Does anyone know if, in an Update or Insert query within a DDQ task,
it's possible to have more than one statement in the query, and have
them both execute?
e.g.
update tablex set field1=1 where id=?
update tablex set field2 = 2 where id=?
Also, on the same subject, can you mix the statements, so let's say
even if it's the Update query you're using, could you tag an insert on
the end as well?
e.g.
update tablex set field1=1 where id=?
insert into tablex (field2) values (?)
Thanks for any help.Just as an afterthought, I suppose if this isn't possible it may be
useful to know whether the other two types of query can be 'hijacked'
and used as an extra insert (or whatever), but I'll wait to see if
anyone can confirm the original question or not before investigating
this one!!|||Quick note for anyone else looking at the same question: I've just
found the following on msdn:
"In order to refer to your SQL statements, you assign each statement a
name, called a query type. A query type, returned by your Microsoft=AE
ActiveX=AE script code, is used to select one of your SQL statements to
execute. Data Transformation Services (DTS) provides the following four
names:
Insert
Update
Delete
User
These query types should be viewed only as unique identifiers assigned
to each statement. It is in fact possible to perform any SQL operation
supported by the connection. It would be possible for example, to
perform four different updates, four different inserts or any mix of
these or stored procedures."
So I could do it by having an extra update as the 'delete' query, for
example. However, I'd feel it would be better 'practice' to have two
update statements within the Update query...I'd be grateful if anyone
with more experience than me could possibly provide an answer as to
whether you can have the two statements in the one query, as I've
trawled the internet and come up with nothing?
Many, many thanks indeed.,sql
Showing posts with label update. Show all posts
Showing posts with label update. Show all posts
Thursday, March 22, 2012
Data driven query - update only CERTAIN fields?
Hi,
I'm trying to set up a Data Driven Query task, to update only certain
fields in a table.
However, even though the update query only contains the fields I'm
wanting to update, when I try and run it I get:
"One or more destination parameter columns had no transform specified"
Thing is, I don't WANT to specify a transform for most of the
destination columns - I want them left alone!!
I would be so, so grateful if anyone who's done something like this
could help, as there are so precious few decent example of this sort of
thing on the net. Any links anyone has to good in-depth tutorials
covering more than just the basic 'update every single field' scenario
would be fantastic too.
Many, many thanks folks.
ChampersJust to quickly illustrate this (I'm not sure I explained this too well
yesterday)
The destination table (which is my binding table) has, let's say, 10
columns
During the update query, I only want maybe 2 fields to be updated
So my code would be something like...
Function Main()
If
IsEmpty(DTSLookups("DoesRecordExist").Execute(DTSSource("PersonID").Value))
Then
DTSDestination("Field3") = DTSSource("SURNAME")
DTSDestination("Field7") = DTSSource("FORENAME")
Main = DTSTransformstat_InsertQuery
Else..
End If
End Function
But I get the feeling that to avoid the error message above, I still
have to specify all the destination columns, even if I don't want to
update them...but, of course, I would have to set them to update to
something...which I don't want to do!
This is driving me crazy. Thanks in advance for any advice!|||I think I may have just cracked this, so I'll post the answer for
anyone else struggling with it. Basically, on the Transformations tab
(the one with the graphical list of all source and destination
columns), you have to select ALL destination columns (and presumably
all source ones too) regardless of whether you're using them in a query
or not. This is REALLY confusing, and I have not been able to find a
tutorial that explains this anywhere. I'm going to press on now with my
DTS task, and I'l post any other useful info I find on this thread, as
I'm sure other people must have been tearing their hair out over this.|||I'm nearly there with this now, but I have to admit it's such a
confusing thing to use.
I just have one more question that someone could maybe answer - I have
2 DDQ's, one to transfer data from some columns of the source table
into table 1, and another to transfer other columns into a separate
table, table 2.
Now, the first DDQ is OK. However, in the second, one of my queries
refers to a source column that doesn't directly transfer to a column in
table 2. Table 2 here is my binding table.
In order to 'reference' the source column, I'm having to basically map
that source column to a column in the binding 'version' of table 2 (one
that I'm not 'using' in this DDQ), so that I can use it in the
Parameters list.
Is this the correct way to do this? i.e. although the binding table
used is originally a real destination table, it's only actually used as
a way of mapping and referencing source colunmns, and in actual fact
bears no relevance to any data transformations (i.e. this 'mapping'
doesn't actually alter data in the destination column - only my QUERY
can does this, in which the destination table itself is used as a real
query destination table, rather than as the binding table).
Sorry to be so verbose...I'd be grateful if someone could set my mind
at ease and clarify that I've got this right in my head!
Thanks so much guys.
I'm trying to set up a Data Driven Query task, to update only certain
fields in a table.
However, even though the update query only contains the fields I'm
wanting to update, when I try and run it I get:
"One or more destination parameter columns had no transform specified"
Thing is, I don't WANT to specify a transform for most of the
destination columns - I want them left alone!!
I would be so, so grateful if anyone who's done something like this
could help, as there are so precious few decent example of this sort of
thing on the net. Any links anyone has to good in-depth tutorials
covering more than just the basic 'update every single field' scenario
would be fantastic too.
Many, many thanks folks.
ChampersJust to quickly illustrate this (I'm not sure I explained this too well
yesterday)
The destination table (which is my binding table) has, let's say, 10
columns
During the update query, I only want maybe 2 fields to be updated
So my code would be something like...
Function Main()
If
IsEmpty(DTSLookups("DoesRecordExist").Execute(DTSSource("PersonID").Value))
Then
DTSDestination("Field3") = DTSSource("SURNAME")
DTSDestination("Field7") = DTSSource("FORENAME")
Main = DTSTransformstat_InsertQuery
Else..
End If
End Function
But I get the feeling that to avoid the error message above, I still
have to specify all the destination columns, even if I don't want to
update them...but, of course, I would have to set them to update to
something...which I don't want to do!
This is driving me crazy. Thanks in advance for any advice!|||I think I may have just cracked this, so I'll post the answer for
anyone else struggling with it. Basically, on the Transformations tab
(the one with the graphical list of all source and destination
columns), you have to select ALL destination columns (and presumably
all source ones too) regardless of whether you're using them in a query
or not. This is REALLY confusing, and I have not been able to find a
tutorial that explains this anywhere. I'm going to press on now with my
DTS task, and I'l post any other useful info I find on this thread, as
I'm sure other people must have been tearing their hair out over this.|||I'm nearly there with this now, but I have to admit it's such a
confusing thing to use.
I just have one more question that someone could maybe answer - I have
2 DDQ's, one to transfer data from some columns of the source table
into table 1, and another to transfer other columns into a separate
table, table 2.
Now, the first DDQ is OK. However, in the second, one of my queries
refers to a source column that doesn't directly transfer to a column in
table 2. Table 2 here is my binding table.
In order to 'reference' the source column, I'm having to basically map
that source column to a column in the binding 'version' of table 2 (one
that I'm not 'using' in this DDQ), so that I can use it in the
Parameters list.
Is this the correct way to do this? i.e. although the binding table
used is originally a real destination table, it's only actually used as
a way of mapping and referencing source colunmns, and in actual fact
bears no relevance to any data transformations (i.e. this 'mapping'
doesn't actually alter data in the destination column - only my QUERY
can does this, in which the destination table itself is used as a real
query destination table, rather than as the binding table).
Sorry to be so verbose...I'd be grateful if someone could set my mind
at ease and clarify that I've got this right in my head!
Thanks so much guys.
Wednesday, March 21, 2012
Data does not fit....
I am doing a simple update statement but am getting an error.
Cannot create a row of size 10675 which is greater than the allowable maximum of 8060.
The statement has been terminated.
I am inserting data that is as big as 7500 characters into a varchar(7500) field. I have made sure that my column is 7500 in length. the only way it fits is if I cut it down to 4950 characters...
Any Ideas?
William,
Total columns length for row is max 8060 (not a single column). Maybe you would want to put your new concatenated string into a text/ntext column.
PS: BTW check another thread about concatenating text fields earlier today.
|||In SQL Server 2000, the entire row's data must be <= 8060 bytes, not just a single column. (this allows it to fit on a single page)
In SQL Server 2005, you can put > 8060 bytes on a row, but it is not advisable in most cases. Any rows that are larger than that spill over into a different page.
|||I know about the limitation and I implement restrictors to make sure my working tables do not go over the given length that I need.
My data has a max length of 7500 because I populated the field with another process that only allows the data to be 7500 in length. So I do not know how the data is growing...
I am just setting a column (varchar 7500) to the value of another column (varchar 7500) and that is what does not make sense to me.
|||Can you post the script of the table?|||this table gets populated by a process that limits the data to 7500
CREATE TABLE [dbo].[Search_hold] (
[id] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[credits] [varchar] (7500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
When I do a simple update to this table is where I get the error... I am only posting the column that is effected because the table is 30+ columns wide....
CREATE TABLE [dbo].[Search] (
[prod_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[credits] [varchar] (7500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[srch_field] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dateadded] [smalldatetime] NULL ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
The first table is to get the data and the main table is the second one. Alot more data is in the table that these columns...
Monday, March 19, 2012
Data Convertion error help
Hi,
I have an error:"*erro while update quantity. Error converting data type nvarchar to int "while i try update data through form page. Does anybody have any idea how can i correct the error??
I didnt try two methods but both given same error and failed update: -
1) Dim sqlcomm As New SqlCommand(sSaveQuote, rConnect)
....
sqlcomm.Parameters.AddWithValue("@.employeeID", sUserID)
sqlcomm.Parameters.AddWithValue("@.quantity", txtquantity.Text)
2)Error converting data type nvarchar to int ??
Dim rConnect As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("CRNS_CustomerConnectionString").ConnectionString)
Dim command As SqlCommand = New SqlCommand("UpdateOrder", rConnect)
command.CommandType = Data.CommandType.StoredProcedure
If OrderInfo.Verified.ToUpper = "True" Or OrderInfo.Verified = 1 Then
command.Parameters.Add("@.Verified", Data.SqlDbType.Bit)
command.Parameters("@.Verified").Value = 1
Else
command.Parameters.Add("@.Verified", Data.SqlDbType.Bit)
command.Parameters("@.Verified").Value = 0
End If
command.Parameters.Add("@.Comment", Data.SqlDbType.VarChar, 100)
command.Parameters("@.Comment").Value = OrderInfo.Comment
command.Parameters.Add("@.ProductID", Data.SqlDbType.Int)
command.Parameters("@.ProductID").Value = OrderInfo.ProductID
command.Parameters.Add("@.OrderDate", Data.SqlDbType.SmallDateTime)
command.Parameters("@.OrderDate").Value = OrderInfo.OrderDate
Cheers:)
Christe
Hi,
Thks for kind replied. Do you have any idea or hint, how can i correct the error?? or how i can convert the nvarchar to int before update in dbs??
cheers:)
|||Hi
You could debug your program to see if the value you pass to parameter is right ,especiallyOrderInfo.ProductID.
Thursday, March 8, 2012
Data comparison and update
Hello All,
I have two tables T1 and T2 with the same data structure. I need to compare T1 with T2 for all columns and update T2 for deleted, inserted and updated rows. How can I do this?
Are you duplicating the T1 data into T2? If so, why not simply delete all T2 rows and insert all T1 rows into T2 (or drop T2 and then recreate it from T1, including data)?|||Hello,
Thanks you very much for the reply. T1 is big and is changing constantly and I am trying to find the discrepancy between T1 and T2 and update T2 based on the discrepancies. SO trying to realize synchronization on a single table. Any idea?
|||I haven't used Triggers in a long time, but it may be a good solution to your situation. Set up the Triggers for UPDATE, INSERT and DELETE operations and have it synchronize your T2 table accordingly. Once the Triggers are defined (and tested), you don't even have to worry about it. Be sure that performance isn't hit by doing this, though.|||
Sorry Jim, wish I could help you, but I'm currently bound by some confidentiality agreements that prohibit me from discussing this in much detail. But here are some choices:
Use a trigger to update t1 whenever t2 is updated (If you need them to be synchronized in realtime, including transaction consistancy). But since they are the same structure, there is usually little reason for implementing it this way.
Replication. You can use this to replicate data from server to server, and probably from table to table as well. There are so many options on how this can be done, it'll take you a while to research and test the possibilities.
Diff-gramming. Write queries to automatically insert, update, and delete those rows which differ from table1 to table2.
Friday, February 24, 2012
DAO, Transactions, SQLServer
Hello,
I have a problem with DAO, Transactions and SQLServer. I want to do a very
simple thing (in VB)!
BeginTrans
Call db.Execute("UPDATE counter SET value = value + 1 WHERE name =
'mycounter'", dbSQLPassThrough)
Set Rec = db.OpenRecordSet("SELECT value FROM counter WHERE name =
'mycounter'", dbOpenSnapshot, dbSQLPassThrough)
value = Rec(0)
CommitTrans
Note how simple this is. I just want to get a new value for a counter,
UPDATing first in order to make sure each value is returned only once, even
in concurrent environment. This is what you learn in school.
Well, this code works in Oracle, Sybase, in SQLServer using OSQL, but not in
SQLServer using DAO/ODBC.
In SQLServer using DAO/ODBC the code just hangs the entire application at
the SELECT line.
I believe the problem comes from the ODBC SQL Server driver
(2000.85.1025.00). From SQLServer OSQL I can see that multiple connections
are opened (by the driver I believe) and I suspect that the driver sends the
UPDATE statement to connection 1 for instance and the SELECT query to ...
connection 2! Of course this will cause a deadlock.
I saw microsoft comments
http://support.microsoft.com/default...b;EN-US;170548 , but I am
not really using JET, since all my database calls use the dbSQLPassThrough
option. I also tried to disable the ODBC connection pool but this is
useless. I believe it really is the Drivers fault since I can get these
statements to work in Sybase and Oracle.
This is the kind of stuff that puzzles me the most. The whole microsoft
architecture tries to be smarter than you are and takes control of
everything, but fails to do the simplest things AND it really seems you
cannot disable it.
Any help would be very much appreciated.
SerGioGio
Hi
What error are you getting?
Have you considered using ADO insterad of DAO?
DAO is a very old technology so I am trying to remember how the stuff worked
10 years ago.
Since you are doing a read, use dbForwardOnly (I think that is what is it)
for the rs.
rs.BeginTran and rs.CommitTran are required.
Regards
Mike
"SerGioGio" wrote:
> Hello,
> I have a problem with DAO, Transactions and SQLServer. I want to do a very
> simple thing (in VB)!
> BeginTrans
> Call db.Execute("UPDATE counter SET value = value + 1 WHERE name =
> 'mycounter'", dbSQLPassThrough)
> Set Rec = db.OpenRecordSet("SELECT value FROM counter WHERE name =
> 'mycounter'", dbOpenSnapshot, dbSQLPassThrough)
> value = Rec(0)
> CommitTrans
> Note how simple this is. I just want to get a new value for a counter,
> UPDATing first in order to make sure each value is returned only once, even
> in concurrent environment. This is what you learn in school.
> Well, this code works in Oracle, Sybase, in SQLServer using OSQL, but not in
> SQLServer using DAO/ODBC.
> In SQLServer using DAO/ODBC the code just hangs the entire application at
> the SELECT line.
> I believe the problem comes from the ODBC SQL Server driver
> (2000.85.1025.00). From SQLServer OSQL I can see that multiple connections
> are opened (by the driver I believe) and I suspect that the driver sends the
> UPDATE statement to connection 1 for instance and the SELECT query to ...
> connection 2! Of course this will cause a deadlock.
> I saw microsoft comments
> http://support.microsoft.com/default...b;EN-US;170548 , but I am
> not really using JET, since all my database calls use the dbSQLPassThrough
> option. I also tried to disable the ODBC connection pool but this is
> useless. I believe it really is the Drivers fault since I can get these
> statements to work in Sybase and Oracle.
> This is the kind of stuff that puzzles me the most. The whole microsoft
> architecture tries to be smarter than you are and takes control of
> everything, but fails to do the simplest things AND it really seems you
> cannot disable it.
> Any help would be very much appreciated.
> SerGioGio
>
>
|||Hello Mike,
Thanks for your quick answer.
I am only getting a "Timeout Expired" Error after 1 minute or so.
I tried your suggestion but with no luck
The whole app is written in DAO so I cannot switch to ADO unfortunately. In
addition I am not even sure ADO will fix that. We always need the most
advanced technology to support the most basic stuff. On the other hand,
client-side cursors, distributed transactions, access database links,
connection pool are available in DAO since early stages...
SerGioGio
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a crit dans le message de
news:D390368A-C890-4B3E-8C62-725D48FC5348@.microsoft.com...
> Hi
> What error are you getting?
> Have you considered using ADO insterad of DAO?
> DAO is a very old technology so I am trying to remember how the stuff
worked[vbcol=seagreen]
> 10 years ago.
> Since you are doing a read, use dbForwardOnly (I think that is what is it)
> for the rs.
> rs.BeginTran and rs.CommitTran are required.
> Regards
> Mike
> "SerGioGio" wrote:
very[vbcol=seagreen]
even[vbcol=seagreen]
not in[vbcol=seagreen]
at[vbcol=seagreen]
connections[vbcol=seagreen]
the[vbcol=seagreen]
...[vbcol=seagreen]
am[vbcol=seagreen]
dbSQLPassThrough[vbcol=seagreen]
|||Hi
Then you need to pass both statements at once (you may have to navigate
through the dataset collection to get to the 2nd executions output) or create
a stored procedure that has the update and select in it. Input parameter of
'mycounter' and output parameter of count.
Regards
Mike
"SerGioGio" wrote:
> Hello Mike,
> Thanks for your quick answer.
> I am only getting a "Timeout Expired" Error after 1 minute or so.
> I tried your suggestion but with no luck
> The whole app is written in DAO so I cannot switch to ADO unfortunately. In
> addition I am not even sure ADO will fix that. We always need the most
> advanced technology to support the most basic stuff. On the other hand,
> client-side cursors, distributed transactions, access database links,
> connection pool are available in DAO since early stages...
> SerGioGio
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a écrit dans le message de
> news:D390368A-C890-4B3E-8C62-725D48FC5348@.microsoft.com...
> worked
> very
> even
> not in
> at
> connections
> the
> ...
> am
> dbSQLPassThrough
>
>
|||Well it is a bit sad to rewrite all the sql just because of one dumb driver
(or whatever it is that fails here).
Using stored proc means writing one version for Oracle, one for Sybase, one
for SQL Server...
But I guess I will have to.
Sometimes I admire MS for their tool/concepts, sometimes I just find they
purposely push us to bloated solutions.
SerGioGio
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a crit dans le message de
news:76FCBB7F-C446-4938-8122-D5B536D06417@.microsoft.com...
> Hi
> Then you need to pass both statements at once (you may have to navigate
> through the dataset collection to get to the 2nd executions output) or
create
> a stored procedure that has the update and select in it. Input parameter
of[vbcol=seagreen]
> 'mycounter' and output parameter of count.
> Regards
> Mike
>
> "SerGioGio" wrote:
In[vbcol=seagreen]
it)[vbcol=seagreen]
a[vbcol=seagreen]
counter,[vbcol=seagreen]
once,[vbcol=seagreen]
but[vbcol=seagreen]
application[vbcol=seagreen]
sends[vbcol=seagreen]
to[vbcol=seagreen]
I[vbcol=seagreen]
these[vbcol=seagreen]
microsoft[vbcol=seagreen]
you[vbcol=seagreen]
|||Hi SerGioGio,
Close all opened recordset before to begin a transaction.
Vctor Koch.
"SerGioGio" <sergiogio@.yahoo.fr> escribi en el mensaje
news:OyCDm4SpEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a problem with DAO, Transactions and SQLServer. I want to do a very
> simple thing (in VB)!
> BeginTrans
> Call db.Execute("UPDATE counter SET value = value + 1 WHERE name =
> 'mycounter'", dbSQLPassThrough)
> Set Rec = db.OpenRecordSet("SELECT value FROM counter WHERE name =
> 'mycounter'", dbOpenSnapshot, dbSQLPassThrough)
> value = Rec(0)
> CommitTrans
> Note how simple this is. I just want to get a new value for a counter,
> UPDATing first in order to make sure each value is returned only once,
even
> in concurrent environment. This is what you learn in school.
> Well, this code works in Oracle, Sybase, in SQLServer using OSQL, but not
in
> SQLServer using DAO/ODBC.
> In SQLServer using DAO/ODBC the code just hangs the entire application at
> the SELECT line.
> I believe the problem comes from the ODBC SQL Server driver
> (2000.85.1025.00). From SQLServer OSQL I can see that multiple connections
> are opened (by the driver I believe) and I suspect that the driver sends
the
> UPDATE statement to connection 1 for instance and the SELECT query to ...
> connection 2! Of course this will cause a deadlock.
> I saw microsoft comments
> http://support.microsoft.com/default...b;EN-US;170548 , but I am
> not really using JET, since all my database calls use the dbSQLPassThrough
> option. I also tried to disable the ODBC connection pool but this is
> useless. I believe it really is the Drivers fault since I can get these
> statements to work in Sybase and Oracle.
> This is the kind of stuff that puzzles me the most. The whole microsoft
> architecture tries to be smarter than you are and takes control of
> everything, but fails to do the simplest things AND it really seems you
> cannot disable it.
> Any help would be very much appreciated.
> SerGioGio
>
|||Hello,
OK I eventually found a solution to this issue, hopefully this may help some
people.
I now believe that I was wrong, the responsible for multiple connections is
not ODBC, it is Jet! I was under the assumption that since I was using
dbSQLPassThrough in all my queries, I was getting rid of Jet, but not
completely actually as it turned out.
To completely get rid of Jet one must use VB's ODBCDirect, it's just a
matter of adding the flag dbUseODBC in the Workspace object creation option.
With this option I no longer get outstanding connections, and I have a great
control over the connections.
Hope this will help people not to struggle for a whole week like I did.
SerGioGio
"SerGioGio" <sergiogio@.yahoo.fr> a crit dans le message de
news:OyCDm4SpEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a problem with DAO, Transactions and SQLServer. I want to do a very
> simple thing (in VB)!
> BeginTrans
> Call db.Execute("UPDATE counter SET value = value + 1 WHERE name =
> 'mycounter'", dbSQLPassThrough)
> Set Rec = db.OpenRecordSet("SELECT value FROM counter WHERE name =
> 'mycounter'", dbOpenSnapshot, dbSQLPassThrough)
> value = Rec(0)
> CommitTrans
> Note how simple this is. I just want to get a new value for a counter,
> UPDATing first in order to make sure each value is returned only once,
even
> in concurrent environment. This is what you learn in school.
> Well, this code works in Oracle, Sybase, in SQLServer using OSQL, but not
in
> SQLServer using DAO/ODBC.
> In SQLServer using DAO/ODBC the code just hangs the entire application at
> the SELECT line.
> I believe the problem comes from the ODBC SQL Server driver
> (2000.85.1025.00). From SQLServer OSQL I can see that multiple connections
> are opened (by the driver I believe) and I suspect that the driver sends
the
> UPDATE statement to connection 1 for instance and the SELECT query to ...
> connection 2! Of course this will cause a deadlock.
> I saw microsoft comments
> http://support.microsoft.com/default...b;EN-US;170548 , but I am
> not really using JET, since all my database calls use the dbSQLPassThrough
> option. I also tried to disable the ODBC connection pool but this is
> useless. I believe it really is the Drivers fault since I can get these
> statements to work in Sybase and Oracle.
> This is the kind of stuff that puzzles me the most. The whole microsoft
> architecture tries to be smarter than you are and takes control of
> everything, but fails to do the simplest things AND it really seems you
> cannot disable it.
> Any help would be very much appreciated.
> SerGioGio
>
I have a problem with DAO, Transactions and SQLServer. I want to do a very
simple thing (in VB)!
BeginTrans
Call db.Execute("UPDATE counter SET value = value + 1 WHERE name =
'mycounter'", dbSQLPassThrough)
Set Rec = db.OpenRecordSet("SELECT value FROM counter WHERE name =
'mycounter'", dbOpenSnapshot, dbSQLPassThrough)
value = Rec(0)
CommitTrans
Note how simple this is. I just want to get a new value for a counter,
UPDATing first in order to make sure each value is returned only once, even
in concurrent environment. This is what you learn in school.
Well, this code works in Oracle, Sybase, in SQLServer using OSQL, but not in
SQLServer using DAO/ODBC.
In SQLServer using DAO/ODBC the code just hangs the entire application at
the SELECT line.
I believe the problem comes from the ODBC SQL Server driver
(2000.85.1025.00). From SQLServer OSQL I can see that multiple connections
are opened (by the driver I believe) and I suspect that the driver sends the
UPDATE statement to connection 1 for instance and the SELECT query to ...
connection 2! Of course this will cause a deadlock.
I saw microsoft comments
http://support.microsoft.com/default...b;EN-US;170548 , but I am
not really using JET, since all my database calls use the dbSQLPassThrough
option. I also tried to disable the ODBC connection pool but this is
useless. I believe it really is the Drivers fault since I can get these
statements to work in Sybase and Oracle.
This is the kind of stuff that puzzles me the most. The whole microsoft
architecture tries to be smarter than you are and takes control of
everything, but fails to do the simplest things AND it really seems you
cannot disable it.
Any help would be very much appreciated.
SerGioGio
Hi
What error are you getting?
Have you considered using ADO insterad of DAO?
DAO is a very old technology so I am trying to remember how the stuff worked
10 years ago.
Since you are doing a read, use dbForwardOnly (I think that is what is it)
for the rs.
rs.BeginTran and rs.CommitTran are required.
Regards
Mike
"SerGioGio" wrote:
> Hello,
> I have a problem with DAO, Transactions and SQLServer. I want to do a very
> simple thing (in VB)!
> BeginTrans
> Call db.Execute("UPDATE counter SET value = value + 1 WHERE name =
> 'mycounter'", dbSQLPassThrough)
> Set Rec = db.OpenRecordSet("SELECT value FROM counter WHERE name =
> 'mycounter'", dbOpenSnapshot, dbSQLPassThrough)
> value = Rec(0)
> CommitTrans
> Note how simple this is. I just want to get a new value for a counter,
> UPDATing first in order to make sure each value is returned only once, even
> in concurrent environment. This is what you learn in school.
> Well, this code works in Oracle, Sybase, in SQLServer using OSQL, but not in
> SQLServer using DAO/ODBC.
> In SQLServer using DAO/ODBC the code just hangs the entire application at
> the SELECT line.
> I believe the problem comes from the ODBC SQL Server driver
> (2000.85.1025.00). From SQLServer OSQL I can see that multiple connections
> are opened (by the driver I believe) and I suspect that the driver sends the
> UPDATE statement to connection 1 for instance and the SELECT query to ...
> connection 2! Of course this will cause a deadlock.
> I saw microsoft comments
> http://support.microsoft.com/default...b;EN-US;170548 , but I am
> not really using JET, since all my database calls use the dbSQLPassThrough
> option. I also tried to disable the ODBC connection pool but this is
> useless. I believe it really is the Drivers fault since I can get these
> statements to work in Sybase and Oracle.
> This is the kind of stuff that puzzles me the most. The whole microsoft
> architecture tries to be smarter than you are and takes control of
> everything, but fails to do the simplest things AND it really seems you
> cannot disable it.
> Any help would be very much appreciated.
> SerGioGio
>
>
|||Hello Mike,
Thanks for your quick answer.
I am only getting a "Timeout Expired" Error after 1 minute or so.
I tried your suggestion but with no luck
The whole app is written in DAO so I cannot switch to ADO unfortunately. In
addition I am not even sure ADO will fix that. We always need the most
advanced technology to support the most basic stuff. On the other hand,
client-side cursors, distributed transactions, access database links,
connection pool are available in DAO since early stages...
SerGioGio
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a crit dans le message de
news:D390368A-C890-4B3E-8C62-725D48FC5348@.microsoft.com...
> Hi
> What error are you getting?
> Have you considered using ADO insterad of DAO?
> DAO is a very old technology so I am trying to remember how the stuff
worked[vbcol=seagreen]
> 10 years ago.
> Since you are doing a read, use dbForwardOnly (I think that is what is it)
> for the rs.
> rs.BeginTran and rs.CommitTran are required.
> Regards
> Mike
> "SerGioGio" wrote:
very[vbcol=seagreen]
even[vbcol=seagreen]
not in[vbcol=seagreen]
at[vbcol=seagreen]
connections[vbcol=seagreen]
the[vbcol=seagreen]
...[vbcol=seagreen]
am[vbcol=seagreen]
dbSQLPassThrough[vbcol=seagreen]
|||Hi
Then you need to pass both statements at once (you may have to navigate
through the dataset collection to get to the 2nd executions output) or create
a stored procedure that has the update and select in it. Input parameter of
'mycounter' and output parameter of count.
Regards
Mike
"SerGioGio" wrote:
> Hello Mike,
> Thanks for your quick answer.
> I am only getting a "Timeout Expired" Error after 1 minute or so.
> I tried your suggestion but with no luck
> The whole app is written in DAO so I cannot switch to ADO unfortunately. In
> addition I am not even sure ADO will fix that. We always need the most
> advanced technology to support the most basic stuff. On the other hand,
> client-side cursors, distributed transactions, access database links,
> connection pool are available in DAO since early stages...
> SerGioGio
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a écrit dans le message de
> news:D390368A-C890-4B3E-8C62-725D48FC5348@.microsoft.com...
> worked
> very
> even
> not in
> at
> connections
> the
> ...
> am
> dbSQLPassThrough
>
>
|||Well it is a bit sad to rewrite all the sql just because of one dumb driver
(or whatever it is that fails here).
Using stored proc means writing one version for Oracle, one for Sybase, one
for SQL Server...
But I guess I will have to.
Sometimes I admire MS for their tool/concepts, sometimes I just find they
purposely push us to bloated solutions.
SerGioGio
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a crit dans le message de
news:76FCBB7F-C446-4938-8122-D5B536D06417@.microsoft.com...
> Hi
> Then you need to pass both statements at once (you may have to navigate
> through the dataset collection to get to the 2nd executions output) or
create
> a stored procedure that has the update and select in it. Input parameter
of[vbcol=seagreen]
> 'mycounter' and output parameter of count.
> Regards
> Mike
>
> "SerGioGio" wrote:
In[vbcol=seagreen]
it)[vbcol=seagreen]
a[vbcol=seagreen]
counter,[vbcol=seagreen]
once,[vbcol=seagreen]
but[vbcol=seagreen]
application[vbcol=seagreen]
sends[vbcol=seagreen]
to[vbcol=seagreen]
I[vbcol=seagreen]
these[vbcol=seagreen]
microsoft[vbcol=seagreen]
you[vbcol=seagreen]
|||Hi SerGioGio,
Close all opened recordset before to begin a transaction.
Vctor Koch.
"SerGioGio" <sergiogio@.yahoo.fr> escribi en el mensaje
news:OyCDm4SpEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a problem with DAO, Transactions and SQLServer. I want to do a very
> simple thing (in VB)!
> BeginTrans
> Call db.Execute("UPDATE counter SET value = value + 1 WHERE name =
> 'mycounter'", dbSQLPassThrough)
> Set Rec = db.OpenRecordSet("SELECT value FROM counter WHERE name =
> 'mycounter'", dbOpenSnapshot, dbSQLPassThrough)
> value = Rec(0)
> CommitTrans
> Note how simple this is. I just want to get a new value for a counter,
> UPDATing first in order to make sure each value is returned only once,
even
> in concurrent environment. This is what you learn in school.
> Well, this code works in Oracle, Sybase, in SQLServer using OSQL, but not
in
> SQLServer using DAO/ODBC.
> In SQLServer using DAO/ODBC the code just hangs the entire application at
> the SELECT line.
> I believe the problem comes from the ODBC SQL Server driver
> (2000.85.1025.00). From SQLServer OSQL I can see that multiple connections
> are opened (by the driver I believe) and I suspect that the driver sends
the
> UPDATE statement to connection 1 for instance and the SELECT query to ...
> connection 2! Of course this will cause a deadlock.
> I saw microsoft comments
> http://support.microsoft.com/default...b;EN-US;170548 , but I am
> not really using JET, since all my database calls use the dbSQLPassThrough
> option. I also tried to disable the ODBC connection pool but this is
> useless. I believe it really is the Drivers fault since I can get these
> statements to work in Sybase and Oracle.
> This is the kind of stuff that puzzles me the most. The whole microsoft
> architecture tries to be smarter than you are and takes control of
> everything, but fails to do the simplest things AND it really seems you
> cannot disable it.
> Any help would be very much appreciated.
> SerGioGio
>
|||Hello,
OK I eventually found a solution to this issue, hopefully this may help some
people.
I now believe that I was wrong, the responsible for multiple connections is
not ODBC, it is Jet! I was under the assumption that since I was using
dbSQLPassThrough in all my queries, I was getting rid of Jet, but not
completely actually as it turned out.
To completely get rid of Jet one must use VB's ODBCDirect, it's just a
matter of adding the flag dbUseODBC in the Workspace object creation option.
With this option I no longer get outstanding connections, and I have a great
control over the connections.
Hope this will help people not to struggle for a whole week like I did.
SerGioGio
"SerGioGio" <sergiogio@.yahoo.fr> a crit dans le message de
news:OyCDm4SpEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a problem with DAO, Transactions and SQLServer. I want to do a very
> simple thing (in VB)!
> BeginTrans
> Call db.Execute("UPDATE counter SET value = value + 1 WHERE name =
> 'mycounter'", dbSQLPassThrough)
> Set Rec = db.OpenRecordSet("SELECT value FROM counter WHERE name =
> 'mycounter'", dbOpenSnapshot, dbSQLPassThrough)
> value = Rec(0)
> CommitTrans
> Note how simple this is. I just want to get a new value for a counter,
> UPDATing first in order to make sure each value is returned only once,
even
> in concurrent environment. This is what you learn in school.
> Well, this code works in Oracle, Sybase, in SQLServer using OSQL, but not
in
> SQLServer using DAO/ODBC.
> In SQLServer using DAO/ODBC the code just hangs the entire application at
> the SELECT line.
> I believe the problem comes from the ODBC SQL Server driver
> (2000.85.1025.00). From SQLServer OSQL I can see that multiple connections
> are opened (by the driver I believe) and I suspect that the driver sends
the
> UPDATE statement to connection 1 for instance and the SELECT query to ...
> connection 2! Of course this will cause a deadlock.
> I saw microsoft comments
> http://support.microsoft.com/default...b;EN-US;170548 , but I am
> not really using JET, since all my database calls use the dbSQLPassThrough
> option. I also tried to disable the ODBC connection pool but this is
> useless. I believe it really is the Drivers fault since I can get these
> statements to work in Sybase and Oracle.
> This is the kind of stuff that puzzles me the most. The whole microsoft
> architecture tries to be smarter than you are and takes control of
> everything, but fails to do the simplest things AND it really seems you
> cannot disable it.
> Any help would be very much appreciated.
> SerGioGio
>
Dangers of update statement
I'm always a bit concerned when doing an update statement in case there is a
bug that causes it to update an entire table or too many records. Out of all
the programming techniques the update statement would have to have close to
the worst concequences for a bug, imo. I'm curious what techniques people
use to avoid this problem (besides the obvious such as testing).
The reason I'm asking is I found an update statement which should have had a
"where ID = @.ID" but I just plain forgot the where clause. This went out to
customers but through some miracle never got called. It was within a couple
of If statement and was only called in unusual circumstance which luckily
never happened.
Cheers,
MichaelMichael C wrote:
> I'm always a bit concerned when doing an update statement in case
> there is a bug that causes it to update an entire table or too many
> records. Out of all the programming techniques the update statement
> would have to have close to the worst concequences for a bug, imo.
> I'm curious what techniques people use to avoid this problem (besides
> the obvious such as testing).
> The reason I'm asking is I found an update statement which should
> have had a "where ID = @.ID" but I just plain forgot the where clause.
> This went out to customers but through some miracle never got called.
> It was within a couple of If statement and was only called in unusual
> circumstance which luckily never happened.
> Cheers,
> Michael
I'd say there was a serious gap in testing that particular procedure :-)
Every procedure should be tested with all possible inputs and the
outputs clearly examined and documented. As a part of the stored
procedure development process, the developer should clearly examine the
procedure code and design a set of test calls that will attack all of
the source. Also make sure that NULL parameter values are tested as well
where allowed. This could result in a lot of test calls, but it's much
easier to set this up during development than it is to design during an
application test. As you saw, it possible an application will never call
a stored procedure with the parameters that trigger a major problem. In
that case, the development side is the only way to catch these problems.
This could be an internal process where developers clearly document the
test cases (call text and expected outpu). You could document these
items along with the stored procedure text right in your version control
system. Another option is to use a framework for testing that can help
you automate unit testing. An open-source project call TSQL Unit is such
an option (although it hasn't been updated in a while):
http://msdn.microsoft.com/library/d...r />
p04i1.asp
http://sourceforge.net/projects/tsqlunit
That's not to say an error can't creep in occasionally, but a documented
development and testing process will go a long way in eliminating these
types of errors.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||My post errored out so I am not sure if my earlies response got posted.
You could put begin tran before you update and commit or rollback tran after
checking your results.
Check out the following example:
set nocount on
go
create table test(
c1 int not null,
c2 int not null)
go
insert test values(1,1)
insert test values(2,1)
insert test values(3,1)
go
select * from test
go
begin tran
update test set c2= 333 where c1=2
select * from test
/*
c1 c2
-- --
1 1
2 333
3 1
*/
rollback tran
select * from test
/*
c1 c2
-- --
1 1
2 1
3 1
*/
go
drop table test
go
HTH...
http://zulfiqar.typepad.com
BSEE, MCP
"Michael C" wrote:
> I'm always a bit concerned when doing an update statement in case there is
a
> bug that causes it to update an entire table or too many records. Out of a
ll
> the programming techniques the update statement would have to have close t
o
> the worst concequences for a bug, imo. I'm curious what techniques people
> use to avoid this problem (besides the obvious such as testing).
> The reason I'm asking is I found an update statement which should have had
a
> "where ID = @.ID" but I just plain forgot the where clause. This went out t
o
> customers but through some miracle never got called. It was within a coupl
e
> of If statement and was only called in unusual circumstance which luckily
> never happened.
> Cheers,
> Michael
>
>|||On Fri, 19 Aug 2005 11:52:02 +1000, Michael C wrote:
>I'm always a bit concerned when doing an update statement in case there is
a
>bug that causes it to update an entire table or too many records. Out of al
l
>the programming techniques the update statement would have to have close to
>the worst concequences for a bug, imo.
Hi Michael,
I'd say that the DELETE is possibly even more dangerous.
In one of my first jobs in the SQL Server world, my boss was writing and
testing a query to remove erroneous rows from the production database
that were introduced by a bug. Here's how he tested his delete statement
to check that he got the where clause just right:
-- DELETE FROM TheTable
select * from TheTable
WHERE ....
AND ....
go
Once he had the wherer clause tweaked to return just the rows that had
to be deleted, he removed the comment in front of the first line and
clicked the execute button...
After a few minutes, he asked my more experienced coworker if he
understood why the query was taking so long. The coworker then
immediately rushed to his own PC and issued a kill command.
I think that this was the only time that we were actually glad that this
table was loaded with trigger code that was slower than a turtle with
two crippled legs.
> I'm curious what techniques people
>use to avoid this problem (besides the obvious such as testing).
For ad-hoc queries that I want to test first, I always START to type
this:
BEGIN TRAN
go
go
ROLLBACK TRAN
go
After that, I position the cursor between the two go's and start typing
the selects that show the result of my statement, and then the update,
insert, or delete statement itself.
(The extra go before the ROLLBACK ensures it gets executed even if the
query has an error that aborts the batch)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:beecg1hp4o6lk251uv01l62c07trnm4rnb@.
4ax.com...
> I'd say that the DELETE is possibly even more dangerous.
> In one of my first jobs in the SQL Server world, my boss was writing and
> testing a query to remove erroneous rows from the production database
> that were introduced by a bug. Here's how he tested his delete statement
> to check that he got the where clause just right:
> -- DELETE FROM TheTable
> select * from TheTable
> WHERE ....
> AND ....
> go
We had a similar problem except much worse. One particular database had no
referential integrity and someone thought they'd issue a delete statement to
delete orphan records. Problem was they forgot the where clause altogether
and deleted all the records in the table. This went out to 300 customers but
I think the problem was found and fixed fairly quickly so not too many
people encountered the problem. I pointed out that the delete would have
failed if we had integrity but it seemed to fall on deaf ears.
Michael|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:erhULaHpFHA.3376@.TK2MSFTNGP10.phx.gbl...
> I'd say there was a serious gap in testing that particular procedure :-)
> Every procedure should be tested with all possible inputs and the outputs
> clearly examined and documented. As a part of the stored procedure
> development process, the developer should clearly examine the procedure
> code and design a set of test calls that will attack all of the source.
> Also make sure that NULL parameter values are tested as well where
> allowed. This could result in a lot of test calls, but it's much easier to
> set this up during development than it is to design during an application
> test. As you saw, it possible an application will never call a stored
> procedure with the parameters that trigger a major problem. In that case,
> the development side is the only way to catch these problems.
You live in a very different world to me :-) Our projects are fairly rushed
and disorganised and testing is fairly poor. I'd love it to be different but
the only way to do that is change jobs.
Michael|||On Mon, 22 Aug 2005 13:57:38 +1000, Michael C wrote:
(snip)
>You live in a very different world to me :-) Our projects are fairly rushed
>and disorganised and testing is fairly poor. I'd love it to be different bu
t
>the only way to do that is change jobs.
Hi Michael,
But isn't the amount of time you have to spend repairing things even
more than the time you would have spent testing?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:nulkg11ab4m7cr5dqiot9re6atbadmnkbo@.
4ax.com...
> But isn't the amount of time you have to spend repairing things even
> more than the time you would have spent testing?
That's a good question and I'd probably say yes but it's just the way it is
here. There's never any time to do it properly but there's always plenty of
time to fix it. There's always someone who needs something urgently and
always a reason to rush it. I keep explaining that if we'd been doing this
three years ago there'd be someone who needed it urgently then but no one
seems to listen to that. Nothing here get approved if it's over 2 months, it
doesn't matter if it runs over time as long the initial estimate is for 2
months or less. I could go on for hours about this. :-)
Michael|||Michael C wrote:
> We had a similar problem except much worse. One particular database had no
> referential integrity and someone thought they'd issue a delete statement
to
> delete orphan records. Problem was they forgot the where clause altogether
> and deleted all the records in the table. This went out to 300 customers b
ut
> I think the problem was found and fixed fairly quickly so not too many
> people encountered the problem. I pointed out that the delete would have
> failed if we had integrity but it seemed to fall on deaf ears.
> Michael
Hows the job hunting going? Seriously - do your customers know your
name, does your name get associated with these problems? Sooner or
later (probably), your customers are going to move to a supplier who
does care to get these things right in the first place. I'd get out
before the going gets bad (but make sure to include a good lengthy
sermon in your notice)
Just my two-penneth.
Damien|||Add this to your approval form:
1. This job must be done quick...
2. This job must be well done...
3. This job must be done cheap...
PICK 2 OUT OF 3.
Too bad a lot of bosses choose 1 and 3.
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:%23DhnabQqFHA.1556@.TK2MSFTNGP12.phx.gbl...
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:nulkg11ab4m7cr5dqiot9re6atbadmnkbo@.
4ax.com...
> That's a good question and I'd probably say yes but it's just the way it
> is here. There's never any time to do it properly but there's always
> plenty of time to fix it. There's always someone who needs something
> urgently and always a reason to rush it. I keep explaining that if we'd
> been doing this three years ago there'd be someone who needed it urgently
> then but no one seems to listen to that. Nothing here get approved if it's
> over 2 months, it doesn't matter if it runs over time as long the initial
> estimate is for 2 months or less. I could go on for hours about this. :-)
> Michael
>
bug that causes it to update an entire table or too many records. Out of all
the programming techniques the update statement would have to have close to
the worst concequences for a bug, imo. I'm curious what techniques people
use to avoid this problem (besides the obvious such as testing).
The reason I'm asking is I found an update statement which should have had a
"where ID = @.ID" but I just plain forgot the where clause. This went out to
customers but through some miracle never got called. It was within a couple
of If statement and was only called in unusual circumstance which luckily
never happened.
Cheers,
MichaelMichael C wrote:
> I'm always a bit concerned when doing an update statement in case
> there is a bug that causes it to update an entire table or too many
> records. Out of all the programming techniques the update statement
> would have to have close to the worst concequences for a bug, imo.
> I'm curious what techniques people use to avoid this problem (besides
> the obvious such as testing).
> The reason I'm asking is I found an update statement which should
> have had a "where ID = @.ID" but I just plain forgot the where clause.
> This went out to customers but through some miracle never got called.
> It was within a couple of If statement and was only called in unusual
> circumstance which luckily never happened.
> Cheers,
> Michael
I'd say there was a serious gap in testing that particular procedure :-)
Every procedure should be tested with all possible inputs and the
outputs clearly examined and documented. As a part of the stored
procedure development process, the developer should clearly examine the
procedure code and design a set of test calls that will attack all of
the source. Also make sure that NULL parameter values are tested as well
where allowed. This could result in a lot of test calls, but it's much
easier to set this up during development than it is to design during an
application test. As you saw, it possible an application will never call
a stored procedure with the parameters that trigger a major problem. In
that case, the development side is the only way to catch these problems.
This could be an internal process where developers clearly document the
test cases (call text and expected outpu). You could document these
items along with the stored procedure text right in your version control
system. Another option is to use a framework for testing that can help
you automate unit testing. An open-source project call TSQL Unit is such
an option (although it hasn't been updated in a while):
http://msdn.microsoft.com/library/d...r />
p04i1.asp
http://sourceforge.net/projects/tsqlunit
That's not to say an error can't creep in occasionally, but a documented
development and testing process will go a long way in eliminating these
types of errors.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||My post errored out so I am not sure if my earlies response got posted.
You could put begin tran before you update and commit or rollback tran after
checking your results.
Check out the following example:
set nocount on
go
create table test(
c1 int not null,
c2 int not null)
go
insert test values(1,1)
insert test values(2,1)
insert test values(3,1)
go
select * from test
go
begin tran
update test set c2= 333 where c1=2
select * from test
/*
c1 c2
-- --
1 1
2 333
3 1
*/
rollback tran
select * from test
/*
c1 c2
-- --
1 1
2 1
3 1
*/
go
drop table test
go
HTH...
http://zulfiqar.typepad.com
BSEE, MCP
"Michael C" wrote:
> I'm always a bit concerned when doing an update statement in case there is
a
> bug that causes it to update an entire table or too many records. Out of a
ll
> the programming techniques the update statement would have to have close t
o
> the worst concequences for a bug, imo. I'm curious what techniques people
> use to avoid this problem (besides the obvious such as testing).
> The reason I'm asking is I found an update statement which should have had
a
> "where ID = @.ID" but I just plain forgot the where clause. This went out t
o
> customers but through some miracle never got called. It was within a coupl
e
> of If statement and was only called in unusual circumstance which luckily
> never happened.
> Cheers,
> Michael
>
>|||On Fri, 19 Aug 2005 11:52:02 +1000, Michael C wrote:
>I'm always a bit concerned when doing an update statement in case there is
a
>bug that causes it to update an entire table or too many records. Out of al
l
>the programming techniques the update statement would have to have close to
>the worst concequences for a bug, imo.
Hi Michael,
I'd say that the DELETE is possibly even more dangerous.
In one of my first jobs in the SQL Server world, my boss was writing and
testing a query to remove erroneous rows from the production database
that were introduced by a bug. Here's how he tested his delete statement
to check that he got the where clause just right:
-- DELETE FROM TheTable
select * from TheTable
WHERE ....
AND ....
go
Once he had the wherer clause tweaked to return just the rows that had
to be deleted, he removed the comment in front of the first line and
clicked the execute button...
After a few minutes, he asked my more experienced coworker if he
understood why the query was taking so long. The coworker then
immediately rushed to his own PC and issued a kill command.
I think that this was the only time that we were actually glad that this
table was loaded with trigger code that was slower than a turtle with
two crippled legs.
> I'm curious what techniques people
>use to avoid this problem (besides the obvious such as testing).
For ad-hoc queries that I want to test first, I always START to type
this:
BEGIN TRAN
go
go
ROLLBACK TRAN
go
After that, I position the cursor between the two go's and start typing
the selects that show the result of my statement, and then the update,
insert, or delete statement itself.
(The extra go before the ROLLBACK ensures it gets executed even if the
query has an error that aborts the batch)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:beecg1hp4o6lk251uv01l62c07trnm4rnb@.
4ax.com...
> I'd say that the DELETE is possibly even more dangerous.
> In one of my first jobs in the SQL Server world, my boss was writing and
> testing a query to remove erroneous rows from the production database
> that were introduced by a bug. Here's how he tested his delete statement
> to check that he got the where clause just right:
> -- DELETE FROM TheTable
> select * from TheTable
> WHERE ....
> AND ....
> go
We had a similar problem except much worse. One particular database had no
referential integrity and someone thought they'd issue a delete statement to
delete orphan records. Problem was they forgot the where clause altogether
and deleted all the records in the table. This went out to 300 customers but
I think the problem was found and fixed fairly quickly so not too many
people encountered the problem. I pointed out that the delete would have
failed if we had integrity but it seemed to fall on deaf ears.
Michael|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:erhULaHpFHA.3376@.TK2MSFTNGP10.phx.gbl...
> I'd say there was a serious gap in testing that particular procedure :-)
> Every procedure should be tested with all possible inputs and the outputs
> clearly examined and documented. As a part of the stored procedure
> development process, the developer should clearly examine the procedure
> code and design a set of test calls that will attack all of the source.
> Also make sure that NULL parameter values are tested as well where
> allowed. This could result in a lot of test calls, but it's much easier to
> set this up during development than it is to design during an application
> test. As you saw, it possible an application will never call a stored
> procedure with the parameters that trigger a major problem. In that case,
> the development side is the only way to catch these problems.
You live in a very different world to me :-) Our projects are fairly rushed
and disorganised and testing is fairly poor. I'd love it to be different but
the only way to do that is change jobs.
Michael|||On Mon, 22 Aug 2005 13:57:38 +1000, Michael C wrote:
(snip)
>You live in a very different world to me :-) Our projects are fairly rushed
>and disorganised and testing is fairly poor. I'd love it to be different bu
t
>the only way to do that is change jobs.
Hi Michael,
But isn't the amount of time you have to spend repairing things even
more than the time you would have spent testing?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:nulkg11ab4m7cr5dqiot9re6atbadmnkbo@.
4ax.com...
> But isn't the amount of time you have to spend repairing things even
> more than the time you would have spent testing?
That's a good question and I'd probably say yes but it's just the way it is
here. There's never any time to do it properly but there's always plenty of
time to fix it. There's always someone who needs something urgently and
always a reason to rush it. I keep explaining that if we'd been doing this
three years ago there'd be someone who needed it urgently then but no one
seems to listen to that. Nothing here get approved if it's over 2 months, it
doesn't matter if it runs over time as long the initial estimate is for 2
months or less. I could go on for hours about this. :-)
Michael|||Michael C wrote:
> We had a similar problem except much worse. One particular database had no
> referential integrity and someone thought they'd issue a delete statement
to
> delete orphan records. Problem was they forgot the where clause altogether
> and deleted all the records in the table. This went out to 300 customers b
ut
> I think the problem was found and fixed fairly quickly so not too many
> people encountered the problem. I pointed out that the delete would have
> failed if we had integrity but it seemed to fall on deaf ears.
> Michael
Hows the job hunting going? Seriously - do your customers know your
name, does your name get associated with these problems? Sooner or
later (probably), your customers are going to move to a supplier who
does care to get these things right in the first place. I'd get out
before the going gets bad (but make sure to include a good lengthy
sermon in your notice)
Just my two-penneth.
Damien|||Add this to your approval form:
1. This job must be done quick...
2. This job must be well done...
3. This job must be done cheap...
PICK 2 OUT OF 3.
Too bad a lot of bosses choose 1 and 3.
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:%23DhnabQqFHA.1556@.TK2MSFTNGP12.phx.gbl...
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:nulkg11ab4m7cr5dqiot9re6atbadmnkbo@.
4ax.com...
> That's a good question and I'd probably say yes but it's just the way it
> is here. There's never any time to do it properly but there's always
> plenty of time to fix it. There's always someone who needs something
> urgently and always a reason to rush it. I keep explaining that if we'd
> been doing this three years ago there'd be someone who needed it urgently
> then but no one seems to listen to that. Nothing here get approved if it's
> over 2 months, it doesn't matter if it runs over time as long the initial
> estimate is for 2 months or less. I could go on for hours about this. :-)
> Michael
>
Sunday, February 19, 2012
daily refresh of data
Hi
I have around 7 tables that get new data or updates in the production envt
and I would like to insert/update these tables on a daily basis in the
testing environment. Can you suggest a script to do this.
Thanks
Bob
=?Utf-8?B?Qm9i?= <Bob@.discussions.microsoft.com> wrote in
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:
> I have around 7 tables that get new data or updates in the production
> envt and I would like to insert/update these tables on a daily basis
> in the testing environment. Can you suggest a script to do this.
To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
"old" SQL Server 2000
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||Bob
--New data
INSERT INTO TableA (<column lists>) SELECT <column lists> FROM TableB WHERE
NOT EXISTS (SELECT * FROM TableA WHERE TableA.PK=TableB.PK)
--Updated data
UPDATE TableA SET col=(SELECT col FROM TableB WHERE TableA.PK=TableB.PK AND
TableA.col<>Table.B.col) WHERE EXISTS (SELECT * FROM TableB WHERE
TableA.PK=TableB.PK AND TableA.col<>Table.B.col)
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com...
> Hi
> I have around 7 tables that get new data or updates in the production envt
> and I would like to insert/update these tables on a daily basis in the
> testing environment. Can you suggest a script to do this.
> Thanks
> Bob
|||Thank you for the reply, but the problem with the inserts and updates is that
each attribute in my table has referential integrity constraints and there
are 7-8 such constraints on the table. What strategy should I use to solve
this problem.
"Ole Kristian Bang?s" wrote:
> =?Utf-8?B?Qm9i?= <Bob@.discussions.microsoft.com> wrote in
> news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:
>
> To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
> "old" SQL Server 2000
> --
> Ole Kristian Bang?s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>
I have around 7 tables that get new data or updates in the production envt
and I would like to insert/update these tables on a daily basis in the
testing environment. Can you suggest a script to do this.
Thanks
Bob
=?Utf-8?B?Qm9i?= <Bob@.discussions.microsoft.com> wrote in
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:
> I have around 7 tables that get new data or updates in the production
> envt and I would like to insert/update these tables on a daily basis
> in the testing environment. Can you suggest a script to do this.
To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
"old" SQL Server 2000
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||Bob
--New data
INSERT INTO TableA (<column lists>) SELECT <column lists> FROM TableB WHERE
NOT EXISTS (SELECT * FROM TableA WHERE TableA.PK=TableB.PK)
--Updated data
UPDATE TableA SET col=(SELECT col FROM TableB WHERE TableA.PK=TableB.PK AND
TableA.col<>Table.B.col) WHERE EXISTS (SELECT * FROM TableB WHERE
TableA.PK=TableB.PK AND TableA.col<>Table.B.col)
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com...
> Hi
> I have around 7 tables that get new data or updates in the production envt
> and I would like to insert/update these tables on a daily basis in the
> testing environment. Can you suggest a script to do this.
> Thanks
> Bob
|||Thank you for the reply, but the problem with the inserts and updates is that
each attribute in my table has referential integrity constraints and there
are 7-8 such constraints on the table. What strategy should I use to solve
this problem.
"Ole Kristian Bang?s" wrote:
> =?Utf-8?B?Qm9i?= <Bob@.discussions.microsoft.com> wrote in
> news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:
>
> To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
> "old" SQL Server 2000
> --
> Ole Kristian Bang?s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>
daily refresh of data
Hi
I have around 7 tables that get new data or updates in the production envt
and I would like to insert/update these tables on a daily basis in the
testing environment. Can you suggest a script to do this.
Thanks
Bob=?Utf-8?B?Qm9i?= <Bob@.discussions.microsoft.com> wrote in
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:
> I have around 7 tables that get new data or updates in the production
> envt and I would like to insert/update these tables on a daily basis
> in the testing environment. Can you suggest a script to do this.
To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
"old" SQL Server 2000 :)
--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Bob
--New data
INSERT INTO TableA (<column lists>) SELECT <column lists> FROM TableB WHERE
NOT EXISTS (SELECT * FROM TableA WHERE TableA.PK=TableB.PK)
--Updated data
UPDATE TableA SET col=(SELECT col FROM TableB WHERE TableA.PK=TableB.PK AND
TableA.col<>Table.B.col) WHERE EXISTS (SELECT * FROM TableB WHERE
TableA.PK=TableB.PK AND TableA.col<>Table.B.col)
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com...
> Hi
> I have around 7 tables that get new data or updates in the production envt
> and I would like to insert/update these tables on a daily basis in the
> testing environment. Can you suggest a script to do this.
> Thanks
> Bob|||Thank you for the reply, but the problem with the inserts and updates is that
each attribute in my table has referential integrity constraints and there
are 7-8 such constraints on the table. What strategy should I use to solve
this problem.
"Ole Kristian Bangås" wrote:
> =?Utf-8?B?Qm9i?= <Bob@.discussions.microsoft.com> wrote in
> news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:
> > I have around 7 tables that get new data or updates in the production
> > envt and I would like to insert/update these tables on a daily basis
> > in the testing environment. Can you suggest a script to do this.
> To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
> "old" SQL Server 2000 :)
> --
> Ole Kristian Bangås
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>
I have around 7 tables that get new data or updates in the production envt
and I would like to insert/update these tables on a daily basis in the
testing environment. Can you suggest a script to do this.
Thanks
Bob=?Utf-8?B?Qm9i?= <Bob@.discussions.microsoft.com> wrote in
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:
> I have around 7 tables that get new data or updates in the production
> envt and I would like to insert/update these tables on a daily basis
> in the testing environment. Can you suggest a script to do this.
To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
"old" SQL Server 2000 :)
--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Bob
--New data
INSERT INTO TableA (<column lists>) SELECT <column lists> FROM TableB WHERE
NOT EXISTS (SELECT * FROM TableA WHERE TableA.PK=TableB.PK)
--Updated data
UPDATE TableA SET col=(SELECT col FROM TableB WHERE TableA.PK=TableB.PK AND
TableA.col<>Table.B.col) WHERE EXISTS (SELECT * FROM TableB WHERE
TableA.PK=TableB.PK AND TableA.col<>Table.B.col)
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com...
> Hi
> I have around 7 tables that get new data or updates in the production envt
> and I would like to insert/update these tables on a daily basis in the
> testing environment. Can you suggest a script to do this.
> Thanks
> Bob|||Thank you for the reply, but the problem with the inserts and updates is that
each attribute in my table has referential integrity constraints and there
are 7-8 such constraints on the table. What strategy should I use to solve
this problem.
"Ole Kristian Bangås" wrote:
> =?Utf-8?B?Qm9i?= <Bob@.discussions.microsoft.com> wrote in
> news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:
> > I have around 7 tables that get new data or updates in the production
> > envt and I would like to insert/update these tables on a daily basis
> > in the testing environment. Can you suggest a script to do this.
> To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
> "old" SQL Server 2000 :)
> --
> Ole Kristian Bangås
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>
daily refresh of data
Hi
I have around 7 tables that get new data or updates in the production envt
and I would like to insert/update these tables on a daily basis in the
testing environment. Can you suggest a script to do this.
Thanks
Bobexamnotes <Bob@.discussions.microsoft.com> wrote in
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:
> I have around 7 tables that get new data or updates in the production
> envt and I would like to insert/update these tables on a daily basis
> in the testing environment. Can you suggest a script to do this.
To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
"old" SQL Server 2000
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Bob
--New data
INSERT INTO TableA (<column lists> ) SELECT <column lists> FROM TableB WHERE
NOT EXISTS (SELECT * FROM TableA WHERE TableA.PK=TableB.PK)
--Updated data
UPDATE TableA SET col=(SELECT col FROM TableB WHERE TableA.PK=TableB.PK AND
TableA.col<>Table.B.col) WHERE EXISTS (SELECT * FROM TableB WHERE
TableA.PK=TableB.PK AND TableA.col<>Table.B.col)
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com...
> Hi
> I have around 7 tables that get new data or updates in the production envt
> and I would like to insert/update these tables on a daily basis in the
> testing environment. Can you suggest a script to do this.
> Thanks
> Bob|||Thank you for the reply, but the problem with the inserts and updates is tha
t
each attribute in my table has referential integrity constraints and there
are 7-8 such constraints on the table. What strategy should I use to solve
this problem.
"Ole Kristian Bang?s" wrote:
> examnotes <Bob@.discussions.microsoft.com> wrote in
> news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:
>
> To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
> "old" SQL Server 2000
> --
> Ole Kristian Bang?s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>
I have around 7 tables that get new data or updates in the production envt
and I would like to insert/update these tables on a daily basis in the
testing environment. Can you suggest a script to do this.
Thanks
Bobexamnotes <Bob@.discussions.microsoft.com> wrote in
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:
> I have around 7 tables that get new data or updates in the production
> envt and I would like to insert/update these tables on a daily basis
> in the testing environment. Can you suggest a script to do this.
To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
"old" SQL Server 2000
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Bob
--New data
INSERT INTO TableA (<column lists> ) SELECT <column lists> FROM TableB WHERE
NOT EXISTS (SELECT * FROM TableA WHERE TableA.PK=TableB.PK)
--Updated data
UPDATE TableA SET col=(SELECT col FROM TableB WHERE TableA.PK=TableB.PK AND
TableA.col<>Table.B.col) WHERE EXISTS (SELECT * FROM TableB WHERE
TableA.PK=TableB.PK AND TableA.col<>Table.B.col)
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com...
> Hi
> I have around 7 tables that get new data or updates in the production envt
> and I would like to insert/update these tables on a daily basis in the
> testing environment. Can you suggest a script to do this.
> Thanks
> Bob|||Thank you for the reply, but the problem with the inserts and updates is tha
t
each attribute in my table has referential integrity constraints and there
are 7-8 such constraints on the table. What strategy should I use to solve
this problem.
"Ole Kristian Bang?s" wrote:
> examnotes <Bob@.discussions.microsoft.com> wrote in
> news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:
>
> To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
> "old" SQL Server 2000
> --
> Ole Kristian Bang?s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>
Daft DTS Data Driven task question
Hi, I'm trying to get a Data Driven DTS task to update a table based on a .csv source file. I've got a numeric ID Identity column (unique) to give me a record number, and I'm using the following code on the update query to try and update the relevant record:
UPDATE [DR-TestDB].dbo.[Test - CustAddress]
SET
County = ?
WHERE (ID = ?)
The problem I'm getting is a conversion error from VarChar to Numeric when I run the task. I've tried using a type conversion in the transformation, but that doesn't appear to help.
Anyone know the answer - I'll bet it's simple :)
Cheers,
MenthosHmmm... ok, I seem to have resolved this - just rebuilt the task from scratch and it appears to have worked fine.
Very strange.
UPDATE [DR-TestDB].dbo.[Test - CustAddress]
SET
County = ?
WHERE (ID = ?)
The problem I'm getting is a conversion error from VarChar to Numeric when I run the task. I've tried using a type conversion in the transformation, but that doesn't appear to help.
Anyone know the answer - I'll bet it's simple :)
Cheers,
MenthosHmmm... ok, I seem to have resolved this - just rebuilt the task from scratch and it appears to have worked fine.
Very strange.
Subscribe to:
Posts (Atom)