a visual basic script to DTS

Hello I am trying to convert a VB script someone wrote for my company into a DTS package that will fire automatically whenever the file is updated on the server. I am using vb.net for the firing of the DTS package, but I am having trouble writting the DTS package itself. This is what the code looks like now

If fso.FileExists(cSource) Then
With cne
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & cSource & ";" _
& "Extended Properties=Excel 8.0"
.Open
cSQL = "SELECT * FROM " & sSheet
Set rse = .Execute(cSQL)
End With

While Not rse.EOF
i = i + 1
Me.Label1.Caption = "Updating Sold: " & i
Me.Refresh
If Not IsNull(rse("VEHICLE-STOCK-NO###")) Then
sSearch = "'" & rse("VEHICLE-STOCK-NO###") & "'"
cSQL = "UPDATE Inventory SET SoldDate = '" & rse("FNLZ-DT") & "', "
cSQL = cSQL & "LastUpdate = '" & Now & "' WHERE StockNo = " & sSearch
cSQL = cSQL & " AND SoldDate IS NULL"
cn.Execute (cSQL)
End If
rse.MoveNext
Wend
rse.Close
cne.Close
End If


So basically I have an excel file that is downloaded once a week and then I run this program, well I am having some toubles with making it a DTS

I need to convert this into an active X transfermation code I think
If Not IsNull(rse("VEHICLE-STOCK-NO###")) Then
sSearch = "'" & rse("VEHICLE-STOCK-NO###") & "'"
cSQL = "UPDATE Inventory SET SoldDate = '" & rse("FNLZ-DT") & "', "
cSQL = cSQL & "LastUpdate = '" & Now & "' WHERE StockNo = " & sSearch
cSQL = cSQL & " AND SoldDate IS NULL"
end if

Thanks a lot

DamianSearch SQLDTS (http://www.sqldts.com) website for code examples and more information.

HTHsql

A useable but non-updating subscriber?

Is there a way in SQL 2005 to be able to use a subscriber to test run data
and not have the updated data sent back to the publisher using any method of
replication? We will have 1 publisher and 2 subscribers (all 2005), and we
would like to use one of the subscribers for running test jobs, without
having the data returned to the publisher.
Thanks in advance
Nancy
Use either transactional or snapshot replication.
You can also use merge replication with the ExchangeType downloadonly
setting.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:eoSdBsreHHA.4560@.TK2MSFTNGP03.phx.gbl...
> Is there a way in SQL 2005 to be able to use a subscriber to test run data
> and not have the updated data sent back to the publisher using any method
> of replication? We will have 1 publisher and 2 subscribers (all 2005),
> and we would like to use one of the subscribers for running test jobs,
> without having the data returned to the publisher.
> Thanks in advance
> Nancy
>
|||So with transactional (in 2005), the subscription database would be usable
for reporting or testing, it woudn't be in a read only state, is that
correct? For some reason we were thinking the subscription database would
be read only.
Thanks,
Nancy
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eudjU0reHHA.1252@.TK2MSFTNGP04.phx.gbl...
> Use either transactional or snapshot replication.
> You can also use merge replication with the ExchangeType downloadonly
> setting.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:eoSdBsreHHA.4560@.TK2MSFTNGP03.phx.gbl...
>
|||Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:ubDFB5reHHA.3632@.TK2MSFTNGP02.phx.gbl...
> So with transactional (in 2005), the subscription database would be usable
> for reporting or testing, it woudn't be in a read only state, is that
> correct? For some reason we were thinking the subscription database would
> be read only.
> Thanks,
> Nancy
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eudjU0reHHA.1252@.TK2MSFTNGP04.phx.gbl...
>
|||Yes it will be readable and you can write to it, in other words put new
indexes on. When you update data you might cause your replication agent to
fail unless you run in the continue on data consistency error profile.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:ubDFB5reHHA.3632@.TK2MSFTNGP02.phx.gbl...
> So with transactional (in 2005), the subscription database would be usable
> for reporting or testing, it woudn't be in a read only state, is that
> correct? For some reason we were thinking the subscription database would
> be read only.
> Thanks,
> Nancy
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eudjU0reHHA.1252@.TK2MSFTNGP04.phx.gbl...
>
|||Thanks for the information,
Nancy
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23qSxO1seHHA.4848@.TK2MSFTNGP06.phx.gbl...
> Yes it will be readable and you can write to it, in other words put new
> indexes on. When you update data you might cause your replication agent to
> fail unless you run in the continue on data consistency error profile.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:ubDFB5reHHA.3632@.TK2MSFTNGP02.phx.gbl...
>
|||As well as Hilary's recommendation of transactional replication in this case,
I'd add that you could look at Read Committed Snapshot as a tool of
maintaining greater concurrency for those reports run during synchronization.
Paul Ibison
sql

a small problem

How to retreive the value of last identity has been updated in a database (SQL Server)?From your Stored procedure return SCOPE_IDENTITY

A simple question regarding SQL 2000 Transactional Replication

We have a database that we would like to replicate to another server.
Am i correct to asume that the database that is updated would be the
publisher and that the subscriber database would be the replicated copy?
Greg, in the case of plain transactional replication, yes, this is the case.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

a simple insert/update trigger

Hi I am looking to create a simple trigger for both UPDATES and INSERTS
where any change to tbl1 will be inserted/updated in tbl2.
The insert works okay, with values added to both tables, however when I
perform an update, it seems to add an extra row in tbl2.
Here's the code:
CREATE TABLE [dbo].[tbl1] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Team] [varchar] (25) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl2] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Team] [varchar] (25) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TRIGGER [tri_DataTrans] ON [dbo].[tbl1]
FOR INSERT, UPDATE
AS
INSERT INTO tbl2([ID], [name], team)
SELECT ID, [name], team FROM inserted
UPDATE tbl2
SET [Name] = Inserted.[Name],
[Team] = Inserted.[Team]
FROM Inserted
WHERE [ID] = Inserted.[ID]
---
What am I doing wrong? do I need to use IF UPDATE()?
Thanks
qh75Why do you want to use a single trigger? You can certainly do that
(modify your INSERT to insert the row only if it doesn't already exist)
but since you want totally different actions in the case of UPDATE and
INSERT it will surely be more efficient to use two triggers instead of
one.
Secondly, your tables as posted have no keys at all. Apparently even
the ID isn't declared as unique (the IDENTITY property doesn't actually
guarantee uniqueness) and the other columns are nullable. Even if that
constrain exists on ID it is definitely not safe to assume that a row
would be assigned the same ID in both tables. On the other hand you
have included the ID in the INSERT, which will fail unless you turn
IDENTITY_INSERT ON, so I'm not clear if you intended to use IDENTITY in
the second table or not.
The solution is to declare natural keys on both tables (presumably Name
and/or Team) and make those column(s) NOT NULL. Then join the two
tables on that key rather than the IDENTITY.
David Portas
SQL Server MVP
--|||David Portas wrote:
> Why do you want to use a single trigger? You can certainly do that
> (modify your INSERT to insert the row only if it doesn't already
exist)
> but since you want totally different actions in the case of UPDATE
and
> INSERT it will surely be more efficient to use two triggers instead
of
> one.
I just thought it could be performed in one trigger, basically (without
getting into the identity stuff) I was looking for some logic I could
use in the one trigger to check for both inserts and both updates.

> Secondly, your tables as posted have no keys at all. Apparently even
> the ID isn't declared as unique (the IDENTITY property doesn't
actually
> guarantee uniqueness) and the other columns are nullable. Even if
that
> constrain exists on ID it is definitely not safe to assume that a row
> would be assigned the same ID in both tables. On the other hand you
> have included the ID in the INSERT, which will fail unless you turn
> IDENTITY_INSERT ON, so I'm not clear if you intended to use IDENTITY
in
> the second table or not.
I'll add a key to the tables and try that.
Thanks for the reply.
qh|||Anyhoo, this is what I came up with:
---
CREATE TRIGGER [tri_Update] ON [dbo].[tbl1]
FOR UPDATE
AS
IF UPDATE ([Team])
BEGIN
UPDATE tbl2
SET [Team] = Inserted.[Team]
FROM tbl2, Inserted
WHERE tbl2.[ID] = Inserted.[ID]
END
IF UPDATE ([Name])
BEGIN
UPDATE tbl2
SET [Name] = Inserted.[Name]
FROM tbl2, Inserted
WHERE tbl2.[ID] = Inserted.[ID]
END
----
CREATE TRIGGER [tri_DataTrans] ON [dbo].[tbl1]
FOR INSERT
AS
INSERT INTO tbl2([ID], [name], team)
SELECT ID, [name], team FROM inserted
----
If you know of a way of combining the two triggers into one (if indeed
it can be done) please let me know.
Cheers
qh

A hashtable that contains millions of records

My database has a hash table that contains millions of records.
Each day thousands of records will be inserted/updated/deleted/read from
this table.
Are there potential problems that I should be awared of?
Is there a better solution not to use just one table?I don't understand your question. A hash table is not a physical database
structure. It's an in-memory structure that is created at the time of query
execution to optimize direct retrieval by key. This is often created when
no appropriate index exists on the table.
SQL Server indexes are b-trees. These are efficient even with tables
containing of billions of rows.
Happy Holidays
Dan Guzman
SQL Server MVP
"Lang" <Lang@.discussions.microsoft.com> wrote in message
news:D3E18EB5-2318-49DC-8178-03FF7980563E@.microsoft.com...
> My database has a hash table that contains millions of records.
> Each day thousands of records will be inserted/updated/deleted/read from
> this table.
> Are there potential problems that I should be awared of?
> Is there a better solution not to use just one table?|||> Is there a better solution not to use just one table?
Just to add to Dan's answer: this one depends on your business problem. Data
model should be done logically correct first, afterwards you can do some
compromises because of performance.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com|||"Lang" <Lang@.discussions.microsoft.com> wrote in message
news:D3E18EB5-2318-49DC-8178-03FF7980563E@.microsoft.com...
> My database has a hash table that contains millions of records.
> Each day thousands of records will be
inserted/updated/deleted/read from
> this table.
> Are there potential problems that I should be awared of?
> Is there a better solution not to use just one table?
Lang,
You have asked a question about the design of your table.
But you have not provided any information about that table (please
understand that, "The database has a hash table," is not a table
description that is useful in answering your question).
The link http://www.aspfaq.com/etiquette.asp?id=5006,
is excellent when it comes to detailing how to provide
the information that will best enable others to answer
your questions.
Sincerely,
Chris O.