|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Trigger logic - this okIf I want the row inserted into the table regardless of whether or not the
Stored proc completes successfully, then is there any problem with employing the following technique (placing a "commit transaction" on the first line) ? CREATE TRIGGER testtrigger ON dbo.Orders FOR INSERT, UPDATE, DELETE AS commit transaction DECLARE @OrderID VARCHAR(20) SELECT @OrderID = OrderNumber FROM Inserted BEGIN EXEC prProcessOrder @OrderID END Actually, looks like the commit removes the Inserted memory variable....
thus I am now asking... CREATE TRIGGER testtrigger ON dbo.Orders FOR INSERT, UPDATE, DELETE AS DECLARE @OrderID VARCHAR(20) SELECT @OrderID = OrderNumber FROM Inserted commit transaction BEGIN EXEC prProcessOrder @OrderID END Show quoteHide quote "Rob" <rwch***@comcast.net> wrote in message news:FaCdnTYtv_T6x57YnZ2dnUVZ_vKdnZ2d@comcast.com... > If I want the row inserted into the table regardless of whether or not the > Stored proc completes successfully, then is there any problem with > employing the following technique (placing a "commit transaction" on the > first line) ? > > > CREATE TRIGGER testtrigger ON dbo.Orders > FOR INSERT, UPDATE, DELETE > AS > commit transaction > > DECLARE @OrderID VARCHAR(20) > SELECT @OrderID = OrderNumber > FROM Inserted > > BEGIN > EXEC prProcessOrder @OrderID > END > > > Hi Rob
Commiting the transaction in the trigger may be ok if you only have one stored procedure to update the table, but if the table gets updated in a multitude of situations there may be a situation where you are prematurely commiting a transaction and therefore not obeying the ACID rules. If you want to take it out of the transaction then you could use xp_cmdshell, but this would be relatively slow. You procedure is not coded to cater for multiple rows being updated and is therefore fundamentally flawed. John Show quoteHide quote "Rob" wrote: > Actually, looks like the commit removes the Inserted memory variable.... > thus I am now asking... > CREATE TRIGGER testtrigger ON dbo.Orders > FOR INSERT, UPDATE, DELETE > AS > > DECLARE @OrderID VARCHAR(20) > SELECT @OrderID = OrderNumber > FROM Inserted > > commit transaction > > > BEGIN > EXEC prProcessOrder @OrderID > END > > > "Rob" <rwch***@comcast.net> wrote in message > news:FaCdnTYtv_T6x57YnZ2dnUVZ_vKdnZ2d@comcast.com... > > If I want the row inserted into the table regardless of whether or not the > > Stored proc completes successfully, then is there any problem with > > employing the following technique (placing a "commit transaction" on the > > first line) ? > > > > > > CREATE TRIGGER testtrigger ON dbo.Orders > > FOR INSERT, UPDATE, DELETE > > AS > > commit transaction > > > > DECLARE @OrderID VARCHAR(20) > > SELECT @OrderID = OrderNumber > > FROM Inserted > > > > BEGIN > > EXEC prProcessOrder @OrderID > > END > > > > > > > > > Hi John,
I do not really like doing this. I know it goes against the grain.... but I am told that they want to commit the insert regardless of whether the sp runs ok... Some more info... The insert into the table is an isolated insert (not part of a larger transaction). Also, only 1 row is added at a time (no bulk inserts). The question I have, and I want to be sure on this is.... Does the "commit transaction" in the code below apply soley to committing the Insert into the "Orders" table. Can it cause other issues ? CREATE TRIGGER testtrigger ON dbo.Orders FOR INSERT AS DECLARE @OrderID VARCHAR(20) SELECT @OrderID = OrderNumber FROM Inserted commit transaction BEGIN EXEC prProcessOrder @OrderID END Thanks, Rob Show quoteHide quote "John Bell" <jbellnewspo***@hotmail.com> wrote in message news:455F7EE5-4FFE-46A2-ADD6-B29BD3B4785F@microsoft.com... > Hi Rob > > Commiting the transaction in the trigger may be ok if you only have one > stored procedure to update the table, but if the table gets updated in a > multitude of situations there may be a situation where you are prematurely > commiting a transaction and therefore not obeying the ACID rules. > > If you want to take it out of the transaction then you could use > xp_cmdshell, but this would be relatively slow. > > You procedure is not coded to cater for multiple rows being updated and is > therefore fundamentally flawed. > > John > > "Rob" wrote: > >> Actually, looks like the commit removes the Inserted memory variable.... >> thus I am now asking... >> CREATE TRIGGER testtrigger ON dbo.Orders >> FOR INSERT, UPDATE, DELETE >> AS >> >> DECLARE @OrderID VARCHAR(20) >> SELECT @OrderID = OrderNumber >> FROM Inserted >> >> commit transaction >> >> >> BEGIN >> EXEC prProcessOrder @OrderID >> END >> >> >> "Rob" <rwch***@comcast.net> wrote in message >> news:FaCdnTYtv_T6x57YnZ2dnUVZ_vKdnZ2d@comcast.com... >> > If I want the row inserted into the table regardless of whether or not >> > the >> > Stored proc completes successfully, then is there any problem with >> > employing the following technique (placing a "commit transaction" on >> > the >> > first line) ? >> > >> > >> > CREATE TRIGGER testtrigger ON dbo.Orders >> > FOR INSERT, UPDATE, DELETE >> > AS >> > commit transaction >> > >> > DECLARE @OrderID VARCHAR(20) >> > SELECT @OrderID = OrderNumber >> > FROM Inserted >> > >> > BEGIN >> > EXEC prProcessOrder @OrderID >> > END >> > >> > >> > >> >> >> Hi Rob
As Erland has stated it will fail silently in SQL 2000 therefore it is a non-starter. You should never assume that you will always only insert/delete/update single rows a table; the requirements in the future may change and you may not be there, or may not be aware of the intention to make the change that results in multiple rows being updated, leading to inconsistent/corrupted data. John Show quoteHide quote "Rob" wrote: > Hi John, > > I do not really like doing this. I know it goes against the grain.... but > I am told that they want to commit the insert regardless of whether the sp > runs ok... > > Some more info... > > The insert into the table is an isolated insert (not part of a larger > transaction). Also, only 1 row is added at a time (no bulk inserts). > > The question I have, and I want to be sure on this is.... > > Does the "commit transaction" in the code below apply soley to committing > the Insert into the "Orders" table. Can it cause other issues ? > > CREATE TRIGGER testtrigger ON dbo.Orders > FOR INSERT AS > > DECLARE @OrderID VARCHAR(20) > SELECT @OrderID = OrderNumber > FROM Inserted > > commit transaction > > > BEGIN > EXEC prProcessOrder @OrderID > END > > Thanks, > Rob > > > > > "John Bell" <jbellnewspo***@hotmail.com> wrote in message > news:455F7EE5-4FFE-46A2-ADD6-B29BD3B4785F@microsoft.com... > > Hi Rob > > > > Commiting the transaction in the trigger may be ok if you only have one > > stored procedure to update the table, but if the table gets updated in a > > multitude of situations there may be a situation where you are prematurely > > commiting a transaction and therefore not obeying the ACID rules. > > > > If you want to take it out of the transaction then you could use > > xp_cmdshell, but this would be relatively slow. > > > > You procedure is not coded to cater for multiple rows being updated and is > > therefore fundamentally flawed. > > > > John > > > > "Rob" wrote: > > > >> Actually, looks like the commit removes the Inserted memory variable.... > >> thus I am now asking... > >> CREATE TRIGGER testtrigger ON dbo.Orders > >> FOR INSERT, UPDATE, DELETE > >> AS > >> > >> DECLARE @OrderID VARCHAR(20) > >> SELECT @OrderID = OrderNumber > >> FROM Inserted > >> > >> commit transaction > >> > >> > >> BEGIN > >> EXEC prProcessOrder @OrderID > >> END > >> > >> > >> "Rob" <rwch***@comcast.net> wrote in message > >> news:FaCdnTYtv_T6x57YnZ2dnUVZ_vKdnZ2d@comcast.com... > >> > If I want the row inserted into the table regardless of whether or not > >> > the > >> > Stored proc completes successfully, then is there any problem with > >> > employing the following technique (placing a "commit transaction" on > >> > the > >> > first line) ? > >> > > >> > > >> > CREATE TRIGGER testtrigger ON dbo.Orders > >> > FOR INSERT, UPDATE, DELETE > >> > AS > >> > commit transaction > >> > > >> > DECLARE @OrderID VARCHAR(20) > >> > SELECT @OrderID = OrderNumber > >> > FROM Inserted > >> > > >> > BEGIN > >> > EXEC prProcessOrder @OrderID > >> > END > >> > > >> > > >> > > >> > >> > >> > > > Hi John,
When I tested this it did appear to "work"... I do know it is very ugly. They are wanting to do this on a temporary basis... If you insert an OrderNumber < 6 characters and a valid date, the transactions sticks, even if the sp fails... sample below... insert into Orders select '1111','1/1/2006' Thanks, Rob CREATE TABLE [dbo].[Orders] ( [OrderNumber] [char] (6) COLLATE Latin1_General_BIN NOT NULL , [Shipped] [datetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[OrdersLog] ( [OrderNumber] [char] (5) COLLATE Latin1_General_BIN NULL , [Shipped] [datetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[OrdersRaw] ( [Ord] [char] (6) COLLATE Latin1_General_BIN NULL , [DatShp] [varchar] (50) COLLATE Latin1_General_BIN NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[OrdersSuccessFail] ( [OrderId] [char] (6) COLLATE Latin1_General_BIN NULL , [SuccessFail] [varchar] (50) COLLATE Latin1_General_BIN NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ( [OrderNumber] ) ON [PRIMARY] GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE TRIGGER testtrigger ON dbo.Orders FOR INSERT AS DECLARE @OrderID VARCHAR(20) SELECT @OrderID = OrderNumber FROM Inserted COMMIT TRANSACTION BEGIN TRANSACTION insert into OrdersSuccessFail select @OrderID, 'Fail' COMMIT TRANSACTION BEGIN EXEC prProcessOrder @OrderID END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO CREATE PROCEDURE prProcessOrder @OrderId as char(6) AS declare @ErrorCode int Select @ErrorCode = @@Error -- The following code causes failure due to incorrect datatype If @ErrorCode = 0 Begin Insert into OrdersLog select @OrderId, 'mmm' Select @ErrorCode = @@Error End If @ErrorCode = 0 begin update OrdersSuccessFail set SuccessFail = 'Success' where OrderId = @OrderId end GO Show quoteHide quote "John Bell" <jbellnewspo***@hotmail.com> wrote in message news:5ED03271-A943-4BF3-AC38-4A02FA7A2EEF@microsoft.com... > Hi Rob > > As Erland has stated it will fail silently in SQL 2000 therefore it is a > non-starter. > > You should never assume that you will always only insert/delete/update > single rows a table; the requirements in the future may change and you may > not be there, or may not be aware of the intention to make the change that > results in multiple rows being updated, leading to inconsistent/corrupted > data. > > John > > "Rob" wrote: > >> Hi John, >> >> I do not really like doing this. I know it goes against the grain.... >> but >> I am told that they want to commit the insert regardless of whether the >> sp >> runs ok... >> >> Some more info... >> >> The insert into the table is an isolated insert (not part of a larger >> transaction). Also, only 1 row is added at a time (no bulk inserts). >> >> The question I have, and I want to be sure on this is.... >> >> Does the "commit transaction" in the code below apply soley to >> committing >> the Insert into the "Orders" table. Can it cause other issues ? >> >> CREATE TRIGGER testtrigger ON dbo.Orders >> FOR INSERT AS >> >> DECLARE @OrderID VARCHAR(20) >> SELECT @OrderID = OrderNumber >> FROM Inserted >> >> commit transaction >> >> >> BEGIN >> EXEC prProcessOrder @OrderID >> END >> >> Thanks, >> Rob >> >> >> >> >> "John Bell" <jbellnewspo***@hotmail.com> wrote in message >> news:455F7EE5-4FFE-46A2-ADD6-B29BD3B4785F@microsoft.com... >> > Hi Rob >> > >> > Commiting the transaction in the trigger may be ok if you only have one >> > stored procedure to update the table, but if the table gets updated in >> > a >> > multitude of situations there may be a situation where you are >> > prematurely >> > commiting a transaction and therefore not obeying the ACID rules. >> > >> > If you want to take it out of the transaction then you could use >> > xp_cmdshell, but this would be relatively slow. >> > >> > You procedure is not coded to cater for multiple rows being updated and >> > is >> > therefore fundamentally flawed. >> > >> > John >> > >> > "Rob" wrote: >> > >> >> Actually, looks like the commit removes the Inserted memory >> >> variable.... >> >> thus I am now asking... >> >> CREATE TRIGGER testtrigger ON dbo.Orders >> >> FOR INSERT, UPDATE, DELETE >> >> AS >> >> >> >> DECLARE @OrderID VARCHAR(20) >> >> SELECT @OrderID = OrderNumber >> >> FROM Inserted >> >> >> >> commit transaction >> >> >> >> >> >> BEGIN >> >> EXEC prProcessOrder @OrderID >> >> END >> >> >> >> >> >> "Rob" <rwch***@comcast.net> wrote in message >> >> news:FaCdnTYtv_T6x57YnZ2dnUVZ_vKdnZ2d@comcast.com... >> >> > If I want the row inserted into the table regardless of whether or >> >> > not >> >> > the >> >> > Stored proc completes successfully, then is there any problem with >> >> > employing the following technique (placing a "commit transaction" >> >> > on >> >> > the >> >> > first line) ? >> >> > >> >> > >> >> > CREATE TRIGGER testtrigger ON dbo.Orders >> >> > FOR INSERT, UPDATE, DELETE >> >> > AS >> >> > commit transaction >> >> > >> >> > DECLARE @OrderID VARCHAR(20) >> >> > SELECT @OrderID = OrderNumber >> >> > FROM Inserted >> >> > >> >> > BEGIN >> >> > EXEC prProcessOrder @OrderID >> >> > END >> >> > >> >> > >> >> > >> >> >> >> >> >> >> >> >> Hi Rob
Your error handling is very poor read http://www.sommarskog.se/error-handling-II.html on how/what you should be checking For instance: declare @ErrorCode int Select @ErrorCode = @@Error -- The following code causes failure due to incorrect datatype If @ErrorCode = 0 Seems a total waste of time as @ErrorCode will always be 0 after the declaration. You datatypes are not consistent between tables which leads to errors such as "Server: Msg 8152, Level 16, State 9, Procedure prProcessOrder, Line 9 String or binary data would be truncated." After sorting this out you will not get the entry in the log that you require if you did something like: Insert into Orders (OrderNumber,shipped) select 'ABCDEF', '20060912' SELECT * FROM ORDERS SELECT * FROM ORDERSLOG SELECT * FROM [dbo].[OrdersSuccessFail] Insert into Orders (OrderNumber,shipped) select 'ABCDEF', '20060912' /* Server: Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK_Orders'. Cannot insert duplicate key in object 'Orders'. The statement has been terminated. */ SELECT * FROM ORDERS SELECT * FROM ORDERSLOG SELECT * FROM [dbo].[OrdersSuccessFail] /* Nothing has been logged */ Erlands point about this failing on SQL 2005 may also indicate that the behavour of this on SQL 2000 may not be guaranteed in future patches/hotfixes John Show quoteHide quote "Rob" wrote: > Hi John, > > When I tested this it did appear to "work"... I do know it is very ugly. > They are wanting to do this on a temporary basis... > > If you insert an OrderNumber < 6 characters and a valid date, the > transactions sticks, even if the sp fails... sample below... > > insert into Orders select '1111','1/1/2006' > > Thanks, > Rob > > > CREATE TABLE [dbo].[Orders] ( > [OrderNumber] [char] (6) COLLATE Latin1_General_BIN NOT NULL , > [Shipped] [datetime] NULL > ) ON [PRIMARY] > GO > > CREATE TABLE [dbo].[OrdersLog] ( > [OrderNumber] [char] (5) COLLATE Latin1_General_BIN NULL , > [Shipped] [datetime] NULL > ) ON [PRIMARY] > GO > > CREATE TABLE [dbo].[OrdersRaw] ( > [Ord] [char] (6) COLLATE Latin1_General_BIN NULL , > [DatShp] [varchar] (50) COLLATE Latin1_General_BIN NULL > ) ON [PRIMARY] > GO > > CREATE TABLE [dbo].[OrdersSuccessFail] ( > [OrderId] [char] (6) COLLATE Latin1_General_BIN NULL , > [SuccessFail] [varchar] (50) COLLATE Latin1_General_BIN NULL > ) ON [PRIMARY] > GO > > ALTER TABLE [dbo].[Orders] ADD > CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED > ( > [OrderNumber] > ) ON [PRIMARY] > GO > > SET QUOTED_IDENTIFIER ON > GO > SET ANSI_NULLS ON > GO > > CREATE TRIGGER testtrigger ON dbo.Orders > FOR INSERT > AS > > DECLARE @OrderID VARCHAR(20) > SELECT @OrderID = OrderNumber > FROM Inserted > > COMMIT TRANSACTION > > > BEGIN TRANSACTION > insert into OrdersSuccessFail select @OrderID, 'Fail' > COMMIT TRANSACTION > > BEGIN > EXEC prProcessOrder @OrderID > END > > > GO > SET QUOTED_IDENTIFIER OFF > GO > SET ANSI_NULLS ON > GO > > CREATE PROCEDURE prProcessOrder @OrderId as char(6) AS > > declare @ErrorCode int > Select @ErrorCode = @@Error > > -- The following code causes failure due to incorrect datatype > If @ErrorCode = 0 > Begin > Insert into OrdersLog select @OrderId, 'mmm' > Select @ErrorCode = @@Error > End > > If @ErrorCode = 0 > begin > > update OrdersSuccessFail set SuccessFail = 'Success' where OrderId = > @OrderId > > end > > GO > > > > > "John Bell" <jbellnewspo***@hotmail.com> wrote in message > news:5ED03271-A943-4BF3-AC38-4A02FA7A2EEF@microsoft.com... > > Hi Rob > > > > As Erland has stated it will fail silently in SQL 2000 therefore it is a > > non-starter. > > > > You should never assume that you will always only insert/delete/update > > single rows a table; the requirements in the future may change and you may > > not be there, or may not be aware of the intention to make the change that > > results in multiple rows being updated, leading to inconsistent/corrupted > > data. > > > > John > > > > "Rob" wrote: > > > >> Hi John, > >> > >> I do not really like doing this. I know it goes against the grain.... > >> but > >> I am told that they want to commit the insert regardless of whether the > >> sp > >> runs ok... > >> > >> Some more info... > >> > >> The insert into the table is an isolated insert (not part of a larger > >> transaction). Also, only 1 row is added at a time (no bulk inserts). > >> > >> The question I have, and I want to be sure on this is.... > >> > >> Does the "commit transaction" in the code below apply soley to > >> committing > >> the Insert into the "Orders" table. Can it cause other issues ? > >> > >> CREATE TRIGGER testtrigger ON dbo.Orders > >> FOR INSERT AS > >> > >> DECLARE @OrderID VARCHAR(20) > >> SELECT @OrderID = OrderNumber > >> FROM Inserted > >> > >> commit transaction > >> > >> > >> BEGIN > >> EXEC prProcessOrder @OrderID > >> END > >> > >> Thanks, > >> Rob > >> > >> > >> > >> > >> "John Bell" <jbellnewspo***@hotmail.com> wrote in message > >> news:455F7EE5-4FFE-46A2-ADD6-B29BD3B4785F@microsoft.com... > >> > Hi Rob > >> > > >> > Commiting the transaction in the trigger may be ok if you only have one > >> > stored procedure to update the table, but if the table gets updated in > >> > a > >> > multitude of situations there may be a situation where you are > >> > prematurely > >> > commiting a transaction and therefore not obeying the ACID rules. > >> > > >> > If you want to take it out of the transaction then you could use > >> > xp_cmdshell, but this would be relatively slow. > >> > > >> > You procedure is not coded to cater for multiple rows being updated and > >> > is > >> > therefore fundamentally flawed. > >> > > >> > John > >> > > >> > "Rob" wrote: > >> > > >> >> Actually, looks like the commit removes the Inserted memory > >> >> variable.... > >> >> thus I am now asking... > >> >> CREATE TRIGGER testtrigger ON dbo.Orders > >> >> FOR INSERT, UPDATE, DELETE > >> >> AS > >> >> > >> >> DECLARE @OrderID VARCHAR(20) > >> >> SELECT @OrderID = OrderNumber > >> >> FROM Inserted > >> >> > >> >> commit transaction > >> >> > >> >> > >> >> BEGIN > >> >> EXEC prProcessOrder @OrderID > >> >> END > >> >> > >> >> > >> >> "Rob" <rwch***@comcast.net> wrote in message > >> >> news:FaCdnTYtv_T6x57YnZ2dnUVZ_vKdnZ2d@comcast.com... > >> >> > If I want the row inserted into the table regardless of whether or > >> >> > not > >> >> > the > >> >> > Stored proc completes successfully, then is there any problem with > >> >> > employing the following technique (placing a "commit transaction" > >> >> > on > >> >> > the > >> >> > first line) ? > >> >> > > >> >> > > >> >> > CREATE TRIGGER testtrigger ON dbo.Orders > >> >> > FOR INSERT, UPDATE, DELETE > >> >> > AS > >> >> > commit transaction > >> >> > > >> >> > DECLARE @OrderID VARCHAR(20) > >> >> > SELECT @OrderID = OrderNumber > >> >> > FROM Inserted > >> >> > > >> >> > BEGIN > >> >> > EXEC prProcessOrder @OrderID > >> >> > END > >> >> > > >> >> > > >> >> > > >> >> > >> >> > >> >> > >> > >> > >> > > > Hi John,
> declare @ErrorCode int Agreed, unless of course you made a syntax error in the declaration. (This > Select @ErrorCode = @@Error > Seems a total waste of time as @ErrorCode will always be 0 after the > declaration code was taken directly from a book written by Dejan Sunderic - Sql server 2000 stored procedures - page 305) >You datatypes are not consistent between tables which leads to errors such I guess I did not present this too clearly.... my datatype did not match on >as >"Server: Msg 8152, Level 16, State 9, Procedure prProcessOrder, Line 9 >String or binary data would be truncated." purpose, as I wanted the stored procedure (called from the trigger) to fail on purpose. My point was that I wanted the original insert to work (provided that it would work if no trigger were on the table), even if the stored procedure called from the trigger fails. Thank you for you help on this matter. I do not want to use this method, but I need some ammo to talk them out of it. I also need to come up with a better solution. Rob Show quoteHide quote "John Bell" <jbellnewspo***@hotmail.com> wrote in message news:FBA1544B-95B8-4300-94BE-7C518B6D1BCC@microsoft.com... > Hi Rob > > Your error handling is very poor read > http://www.sommarskog.se/error-handling-II.html on how/what you should be > checking > > For instance: > > declare @ErrorCode int > Select @ErrorCode = @@Error > > -- The following code causes failure due to incorrect datatype > If @ErrorCode = 0 > > Seems a total waste of time as @ErrorCode will always be 0 after the > declaration. > > You datatypes are not consistent between tables which leads to errors such > as > "Server: Msg 8152, Level 16, State 9, Procedure prProcessOrder, Line 9 > String or binary data would be truncated." > > After sorting this out you will not get the entry in the log that you > require if you did something like: > Insert into Orders (OrderNumber,shipped) select 'ABCDEF', '20060912' > SELECT * FROM ORDERS > SELECT * FROM ORDERSLOG > SELECT * FROM [dbo].[OrdersSuccessFail] > > Insert into Orders (OrderNumber,shipped) select 'ABCDEF', '20060912' > /* > Server: Msg 2627, Level 14, State 1, Line 1 > Violation of PRIMARY KEY constraint 'PK_Orders'. Cannot insert duplicate > key > in object 'Orders'. > The statement has been terminated. > */ > SELECT * FROM ORDERS > SELECT * FROM ORDERSLOG > SELECT * FROM [dbo].[OrdersSuccessFail] > /* Nothing has been logged */ > > Erlands point about this failing on SQL 2005 may also indicate that the > behavour of this on SQL 2000 may not be guaranteed in future > patches/hotfixes > > John > > > > > "Rob" wrote: > >> Hi John, >> >> When I tested this it did appear to "work"... I do know it is very ugly. >> They are wanting to do this on a temporary basis... >> >> If you insert an OrderNumber < 6 characters and a valid date, the >> transactions sticks, even if the sp fails... sample below... >> >> insert into Orders select '1111','1/1/2006' >> >> Thanks, >> Rob >> >> >> CREATE TABLE [dbo].[Orders] ( >> [OrderNumber] [char] (6) COLLATE Latin1_General_BIN NOT NULL , >> [Shipped] [datetime] NULL >> ) ON [PRIMARY] >> GO >> >> CREATE TABLE [dbo].[OrdersLog] ( >> [OrderNumber] [char] (5) COLLATE Latin1_General_BIN NULL , >> [Shipped] [datetime] NULL >> ) ON [PRIMARY] >> GO >> >> CREATE TABLE [dbo].[OrdersRaw] ( >> [Ord] [char] (6) COLLATE Latin1_General_BIN NULL , >> [DatShp] [varchar] (50) COLLATE Latin1_General_BIN NULL >> ) ON [PRIMARY] >> GO >> >> CREATE TABLE [dbo].[OrdersSuccessFail] ( >> [OrderId] [char] (6) COLLATE Latin1_General_BIN NULL , >> [SuccessFail] [varchar] (50) COLLATE Latin1_General_BIN NULL >> ) ON [PRIMARY] >> GO >> >> ALTER TABLE [dbo].[Orders] ADD >> CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED >> ( >> [OrderNumber] >> ) ON [PRIMARY] >> GO >> >> SET QUOTED_IDENTIFIER ON >> GO >> SET ANSI_NULLS ON >> GO >> >> CREATE TRIGGER testtrigger ON dbo.Orders >> FOR INSERT >> AS >> >> DECLARE @OrderID VARCHAR(20) >> SELECT @OrderID = OrderNumber >> FROM Inserted >> >> COMMIT TRANSACTION >> >> >> BEGIN TRANSACTION >> insert into OrdersSuccessFail select @OrderID, 'Fail' >> COMMIT TRANSACTION >> >> BEGIN >> EXEC prProcessOrder @OrderID >> END >> >> >> GO >> SET QUOTED_IDENTIFIER OFF >> GO >> SET ANSI_NULLS ON >> GO >> >> CREATE PROCEDURE prProcessOrder @OrderId as char(6) AS >> >> declare @ErrorCode int >> Select @ErrorCode = @@Error >> >> -- The following code causes failure due to incorrect datatype >> If @ErrorCode = 0 >> Begin >> Insert into OrdersLog select @OrderId, 'mmm' >> Select @ErrorCode = @@Error >> End >> >> If @ErrorCode = 0 >> begin >> >> update OrdersSuccessFail set SuccessFail = 'Success' where OrderId = >> @OrderId >> >> end >> >> GO >> >> >> >> >> "John Bell" <jbellnewspo***@hotmail.com> wrote in message >> news:5ED03271-A943-4BF3-AC38-4A02FA7A2EEF@microsoft.com... >> > Hi Rob >> > >> > As Erland has stated it will fail silently in SQL 2000 therefore it is >> > a >> > non-starter. >> > >> > You should never assume that you will always only insert/delete/update >> > single rows a table; the requirements in the future may change and you >> > may >> > not be there, or may not be aware of the intention to make the change >> > that >> > results in multiple rows being updated, leading to >> > inconsistent/corrupted >> > data. >> > >> > John >> > >> > "Rob" wrote: >> > >> >> Hi John, >> >> >> >> I do not really like doing this. I know it goes against the >> >> grain.... >> >> but >> >> I am told that they want to commit the insert regardless of whether >> >> the >> >> sp >> >> runs ok... >> >> >> >> Some more info... >> >> >> >> The insert into the table is an isolated insert (not part of a larger >> >> transaction). Also, only 1 row is added at a time (no bulk inserts). >> >> >> >> The question I have, and I want to be sure on this is.... >> >> >> >> Does the "commit transaction" in the code below apply soley to >> >> committing >> >> the Insert into the "Orders" table. Can it cause other issues ? >> >> >> >> CREATE TRIGGER testtrigger ON dbo.Orders >> >> FOR INSERT AS >> >> >> >> DECLARE @OrderID VARCHAR(20) >> >> SELECT @OrderID = OrderNumber >> >> FROM Inserted >> >> >> >> commit transaction >> >> >> >> >> >> BEGIN >> >> EXEC prProcessOrder @OrderID >> >> END >> >> >> >> Thanks, >> >> Rob >> >> >> >> >> >> >> >> >> >> "John Bell" <jbellnewspo***@hotmail.com> wrote in message >> >> news:455F7EE5-4FFE-46A2-ADD6-B29BD3B4785F@microsoft.com... >> >> > Hi Rob >> >> > >> >> > Commiting the transaction in the trigger may be ok if you only have >> >> > one >> >> > stored procedure to update the table, but if the table gets updated >> >> > in >> >> > a >> >> > multitude of situations there may be a situation where you are >> >> > prematurely >> >> > commiting a transaction and therefore not obeying the ACID rules. >> >> > >> >> > If you want to take it out of the transaction then you could use >> >> > xp_cmdshell, but this would be relatively slow. >> >> > >> >> > You procedure is not coded to cater for multiple rows being updated >> >> > and >> >> > is >> >> > therefore fundamentally flawed. >> >> > >> >> > John >> >> > >> >> > "Rob" wrote: >> >> > >> >> >> Actually, looks like the commit removes the Inserted memory >> >> >> variable.... >> >> >> thus I am now asking... >> >> >> CREATE TRIGGER testtrigger ON dbo.Orders >> >> >> FOR INSERT, UPDATE, DELETE >> >> >> AS >> >> >> >> >> >> DECLARE @OrderID VARCHAR(20) >> >> >> SELECT @OrderID = OrderNumber >> >> >> FROM Inserted >> >> >> >> >> >> commit transaction >> >> >> >> >> >> >> >> >> BEGIN >> >> >> EXEC prProcessOrder @OrderID >> >> >> END >> >> >> >> >> >> >> >> >> "Rob" <rwch***@comcast.net> wrote in message >> >> >> news:FaCdnTYtv_T6x57YnZ2dnUVZ_vKdnZ2d@comcast.com... >> >> >> > If I want the row inserted into the table regardless of whether >> >> >> > or >> >> >> > not >> >> >> > the >> >> >> > Stored proc completes successfully, then is there any problem >> >> >> > with >> >> >> > employing the following technique (placing a "commit >> >> >> > transaction" >> >> >> > on >> >> >> > the >> >> >> > first line) ? >> >> >> > >> >> >> > >> >> >> > CREATE TRIGGER testtrigger ON dbo.Orders >> >> >> > FOR INSERT, UPDATE, DELETE >> >> >> > AS >> >> >> > commit transaction >> >> >> > >> >> >> > DECLARE @OrderID VARCHAR(20) >> >> >> > SELECT @OrderID = OrderNumber >> >> >> > FROM Inserted >> >> >> > >> >> >> > BEGIN >> >> >> > EXEC prProcessOrder @OrderID >> >> >> > END >> >> >> > >> >> >> > >> >> >> > >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> [posted and mailed, please reply in news]
Rob (rwch***@comcast.net) writes: Show quoteHide quote > Actually, looks like the commit removes the Inserted memory variable.... No this is a very bad idea. On SQL 2000 this fails silently, but on > thus I am now asking... > CREATE TRIGGER testtrigger ON dbo.Orders > FOR INSERT, UPDATE, DELETE > AS > > DECLARE @OrderID VARCHAR(20) > SELECT @OrderID = OrderNumber > FROM Inserted > > commit transaction > > > BEGIN > EXEC prProcessOrder @OrderID > END SQL 2005 you will get an error message. As you all you do is to run an INSERT it may seem OK. But say that you run this batch: INSERT tbl(...) SELECT .... PRINT 'I have inserted!' You will find that the PRINT statement is never executed. When SQL Server finds that the transaction count during the execution of a trigger, SQL Server aborts the batch, and if there was an outstanding transaction, it is rolled back. (There can still be a transaction active despite your COMMIT, as transactions can be nested.) The trigger is also flawed since it reads the contents from inserted into a variable. If many rows were affected, there will be many rows in inserted. A trigger is part of the transaction what is defined by the statement that fires the trigger. This means that if the trigger fails, the statement should fail, because statement + trigger should be atomic. If you want the INSERT to happen, no matter if prProcessOrder succeeds or not, you should invoke prProcessOrder in some other way. Maybe set up an Agent job that scans for unprocessed orders periodically. On SQL 2005 I suspect that you would involve Service Broker one way or another. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hi Erland ,
Using sql server 2000... I do not really like doing this. I know it goes against the grain.... but I am told that they want to commit the insert regardless of whether the sp runs ok... Some more info... The insert into the table is an isolated insert (not part of a larger transaction). Also, only 1 row is added at a time (no bulk inserts). The question I have, and I want to be sure on this is.... Does the "commit transaction" in the code below apply soley to committing the Insert into the "Orders" table. Can it cause other issues ? CREATE TRIGGER testtrigger ON dbo.Orders FOR INSERT AS DECLARE @OrderID VARCHAR(20) SELECT @OrderID = OrderNumber FROM Inserted commit transaction BEGIN EXEC prProcessOrder @OrderID END Thanks, Rob Show quoteHide quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns983A760478A8DYazorman@127.0.0.1... > [posted and mailed, please reply in news] > > Rob (rwch***@comcast.net) writes: >> Actually, looks like the commit removes the Inserted memory variable.... >> thus I am now asking... >> CREATE TRIGGER testtrigger ON dbo.Orders >> FOR INSERT, UPDATE, DELETE >> AS >> >> DECLARE @OrderID VARCHAR(20) >> SELECT @OrderID = OrderNumber >> FROM Inserted >> >> commit transaction >> >> >> BEGIN >> EXEC prProcessOrder @OrderID >> END > > No this is a very bad idea. On SQL 2000 this fails silently, but on > SQL 2005 you will get an error message. As you all you do is to run > an INSERT it may seem OK. But say that you run this batch: > > INSERT tbl(...) > SELECT .... > PRINT 'I have inserted!' > > You will find that the PRINT statement is never executed. When SQL > Server finds that the transaction count during the execution of a > trigger, SQL Server aborts the batch, and if there was an outstanding > transaction, it is rolled back. (There can still be a transaction active > despite your COMMIT, as transactions can be nested.) > > The trigger is also flawed since it reads the contents from inserted > into a variable. If many rows were affected, there will be many rows > in inserted. > > A trigger is part of the transaction what is defined by the statement > that fires the trigger. This means that if the trigger fails, the > statement should fail, because statement + trigger should be atomic. > > If you want the INSERT to happen, no matter if prProcessOrder succeeds > or not, you should invoke prProcessOrder in some other way. Maybe set > up an Agent job that scans for unprocessed orders periodically. On SQL > 2005 I suspect that you would involve Service Broker one way or another. > > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Rob,
trigger is no place for such logic, because if your insert fails, your trigger does not fire: create table a(i int check(i>0)) go create trigger a_ins on a for insert as print 'Trigger fired' go insert a values(1) go Trigger fired (1 row(s) affected) insert a values(-1) go Server: Msg 547, Level 16, State 1, Line 1 INSERT statement conflicted with COLUMN CHECK constraint 'CK__a__i__4316F928'. The conflict occurred in database 'DominoV3', table 'a', column 'i'. The statement has been terminated. drop table a I would use savepoints. Rob (rwch***@comcast.net) writes:
> Using sql server 2000... The idea as such is not wrong. There may be also sorts of business> > I do not really like doing this. I know it goes against the grain.... > but I am told that they want to commit the insert regardless of whether > the sp runs ok... rules that mandates. You just have to make a good implementation. > Some more info... But it still fails, and it will not fail silenly, the day you move> > The insert into the table is an isolated insert (not part of a larger > transaction). Also, only 1 row is added at a time (no bulk inserts). to SQL 2005. Is there any reason why you cannot just make put INSERT in a stored procedure that first inserts the row, and then calls the other stored procedure? -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thanks for responding Erland....
> Is there any reason why you cannot just make put INSERT in a stored The program that inserts the row into the table to begin with is owned by > procedure that first inserts the row, and then calls the other stored > procedure? another entity... I believe it is a VB6 app... but I am assured only 1 row is inserted at a time (no bulk inserts) and it is not part of a larger transaction. > But it still fails, and it will not fail silenly, the day you move I did include a test that I believe shows that it will not necessarily > to SQL 2005. fail...as long as the error is related to the sp that is called in the trigger. (see below) They have no plans to move to SQL 2005 any time soon. I also think SQL 2005 transact sql includes Try-Catch logic that would allow you to by pass any of the sp errors. Can placing the Commit Transaction (which I am assuming would only apply to the implicit Begin Transaction related to the trigger) really bugger things up in any way ? Thanks, Rob ***************************************************** When I tested this it did appear to "work"... I do know it is very ugly. They are wanting to do this on a temporary basis... If you insert an OrderNumber < 6 characters and a valid date, the transactions sticks, even if the sp fails... sample below... insert into Orders select '1111','1/1/2006' CREATE TABLE [dbo].[Orders] ( [OrderNumber] [char] (6) COLLATE Latin1_General_BIN NOT NULL , [Shipped] [datetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[OrdersSuccessFail] ( [OrderId] [char] (6) COLLATE Latin1_General_BIN NULL , [SuccessFail] [varchar] (50) COLLATE Latin1_General_BIN NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ( [OrderNumber] ) ON [PRIMARY] GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE TRIGGER testtrigger ON dbo.Orders FOR INSERT AS DECLARE @OrderID VARCHAR(20) SELECT @OrderID = OrderNumber FROM Inserted COMMIT TRANSACTION BEGIN TRANSACTION insert into OrdersSuccessFail select @OrderID, 'Fail' COMMIT TRANSACTION BEGIN EXEC prProcessOrder @OrderID END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO CREATE PROCEDURE prProcessOrder @OrderId as char(6) AS declare @ErrorCode int Select @ErrorCode = @@Error -- The following code causes failure due to incorrect datatype If @ErrorCode = 0 Begin Insert into OrdersLog select @OrderId, 'mmm' Select @ErrorCode = @@Error End If @ErrorCode = 0 begin update OrdersSuccessFail set SuccessFail = 'Success' where OrderId = @OrderId end GO Show quoteHide quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns983BF0F70AB2Yazorman@127.0.0.1... > Rob (rwch***@comcast.net) writes: >> Using sql server 2000... >> >> I do not really like doing this. I know it goes against the grain.... >> but I am told that they want to commit the insert regardless of whether >> the sp runs ok... > > The idea as such is not wrong. There may be also sorts of business > rules that mandates. You just have to make a good implementation. > >> Some more info... >> >> The insert into the table is an isolated insert (not part of a larger >> transaction). Also, only 1 row is added at a time (no bulk inserts). > > But it still fails, and it will not fail silenly, the day you move > to SQL 2005. > > Is there any reason why you cannot just make put INSERT in a stored > procedure that first inserts the row, and then calls the other stored > procedure? > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Rob (rwch***@comcast.net) writes:
Show quoteHide quote > The program that inserts the row into the table to begin with is owned by Yes, but then someone would have to rewrite the trigger. The first thing > another entity... I believe it is a VB6 app... but I am assured only 1 row > is inserted at a time (no bulk inserts) and it is not part of a larger > transaction. > >> But it still fails, and it will not fail silenly, the day you move >> to SQL 2005. > > I did include a test that I believe shows that it will not necessarily > fail...as long as the error is related to the sp that is called in the > trigger. (see below) > > They have no plans to move to SQL 2005 any time soon. I also think SQL > 2005 transact sql includes Try-Catch logic that would allow you to by > pass any of the sp errors. that till happen is that it will explode in the face of someone. > Can placing the Commit Transaction (which I am assuming would only apply This is about robustness. It may work as long as the application looks like> to the implicit Begin Transaction related to the trigger) really bugger > things up in any way ? it does today. But in six months someone else writes another operation against this table, and where the INSERT is part of a longer transaction. His code will not work, and it could take him several days to understand why. If the procedure is to be executed indepently, it should not be part of the trigger. Set up a job that checks the table for new inserts. And if that is not easy to do that, use the trigger to insert rows into a queue table that the processing SP can take care of. But don't implement a solution that can be very expensive for your client once you are out of the door. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Rob,
Does it have to happen immediately? Can you not have the trigger do something like... Insert Into OrderQueue (OrderID) Select OrderID from Inserted Then have a job run every minute that loops through the OrderQueue table and updates a processed flag or Processed Date field, and calls the SP once for each order (Or even better, re-write the SP to just batch process everything in the OrderQueue that has a Processed of 0/null). If you want it even faster than a minute, just have the job loop back on itself. I.E. Step 1 call SP then GotoStep 2. Step 2 call then goto Step 1. On failure, goto Step 3 - email or page that it failed, then goto step 1. This is all a hack, I know, but I have used it successfully to automate near real-time workflows, and it will still happen if something decides to input more than 1 row into the orders table at a time. Ryan Show quoteHide quote "Rob" wrote: > Thanks for responding Erland.... > > > Is there any reason why you cannot just make put INSERT in a stored > > procedure that first inserts the row, and then calls the other stored > > procedure? > > The program that inserts the row into the table to begin with is owned by > another entity... I believe it is a VB6 app... but I am assured only 1 row > is inserted at a time (no bulk inserts) and it is not part of a larger > transaction. > > > But it still fails, and it will not fail silenly, the day you move > > to SQL 2005. > > I did include a test that I believe shows that it will not necessarily > fail...as long as the error is related to the sp that is called in the > trigger. (see below) > > They have no plans to move to SQL 2005 any time soon. I also think SQL 2005 > transact sql includes Try-Catch logic that would allow you to by pass any of > the sp errors. > > Can placing the Commit Transaction (which I am assuming would only apply to > the implicit Begin Transaction related to the trigger) really bugger things > up in any way ? > > Thanks, > Rob > > ***************************************************** > When I tested this it did appear to "work"... I do know it is very ugly. > They are wanting to do this on a temporary basis... > > If you insert an OrderNumber < 6 characters and a valid date, the > transactions sticks, even if the sp fails... sample below... > > insert into Orders select '1111','1/1/2006' > > > CREATE TABLE [dbo].[Orders] ( > [OrderNumber] [char] (6) COLLATE Latin1_General_BIN NOT NULL , > [Shipped] [datetime] NULL > ) ON [PRIMARY] > GO > > CREATE TABLE [dbo].[OrdersSuccessFail] ( > [OrderId] [char] (6) COLLATE Latin1_General_BIN NULL , > [SuccessFail] [varchar] (50) COLLATE Latin1_General_BIN NULL > ) ON [PRIMARY] > GO > > ALTER TABLE [dbo].[Orders] ADD > CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED > ( > [OrderNumber] > ) ON [PRIMARY] > GO > > SET QUOTED_IDENTIFIER ON > GO > SET ANSI_NULLS ON > GO > > CREATE TRIGGER testtrigger ON dbo.Orders > FOR INSERT > AS > > DECLARE @OrderID VARCHAR(20) > SELECT @OrderID = OrderNumber > FROM Inserted > > COMMIT TRANSACTION > > > BEGIN TRANSACTION > insert into OrdersSuccessFail select @OrderID, 'Fail' > COMMIT TRANSACTION > > BEGIN > EXEC prProcessOrder @OrderID > END > > > GO > SET QUOTED_IDENTIFIER OFF > GO > SET ANSI_NULLS ON > GO > > CREATE PROCEDURE prProcessOrder @OrderId as char(6) AS > > declare @ErrorCode int > Select @ErrorCode = @@Error > > -- The following code causes failure due to incorrect datatype > If @ErrorCode = 0 > Begin > Insert into OrdersLog select @OrderId, 'mmm' > Select @ErrorCode = @@Error > End > > If @ErrorCode = 0 > begin > > update OrdersSuccessFail set SuccessFail = 'Success' where OrderId = > @OrderId > > end > > GO > > > > "Erland Sommarskog" <esq***@sommarskog.se> wrote in message > news:Xns983BF0F70AB2Yazorman@127.0.0.1... > > Rob (rwch***@comcast.net) writes: > >> Using sql server 2000... > >> > >> I do not really like doing this. I know it goes against the grain.... > >> but I am told that they want to commit the insert regardless of whether > >> the sp runs ok... > > > > The idea as such is not wrong. There may be also sorts of business > > rules that mandates. You just have to make a good implementation. > > > >> Some more info... > >> > >> The insert into the table is an isolated insert (not part of a larger > >> transaction). Also, only 1 row is added at a time (no bulk inserts). > > > > But it still fails, and it will not fail silenly, the day you move > > to SQL 2005. > > > > Is there any reason why you cannot just make put INSERT in a stored > > procedure that first inserts the row, and then calls the other stored > > procedure? > > > > -- > > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > > > Books Online for SQL Server 2005 at > > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > > Books Online for SQL Server 2000 at > > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > > Erland,
If you decide to try my test... I forgot a table required... CREATE TABLE [dbo].[OrdersLog] ( [OrderNumber] [char] (5) COLLATE Latin1_General_BIN NULL , [Shipped] [datetime] NULL ) ON [PRIMARY] GO Thanks, Rob Show quoteHide quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns983BF0F70AB2Yazorman@127.0.0.1... > Rob (rwch***@comcast.net) writes: >> Using sql server 2000... >> >> I do not really like doing this. I know it goes against the grain.... >> but I am told that they want to commit the insert regardless of whether >> the sp runs ok... > > The idea as such is not wrong. There may be also sorts of business > rules that mandates. You just have to make a good implementation. > >> Some more info... >> >> The insert into the table is an isolated insert (not part of a larger >> transaction). Also, only 1 row is added at a time (no bulk inserts). > > But it still fails, and it will not fail silenly, the day you move > to SQL 2005. > > Is there any reason why you cannot just make put INSERT in a stored > procedure that first inserts the row, and then calls the other stored > procedure? > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  
Other interesting topics
xml file into table
"Order By" Parameter to a Stored Procedure 'System.OutOfMemoryException' ? rows to column sql server 2000 Views Vs Stored Procedures! Even after year of coding I ask... Formatting and returning Time from Text Deferred drop Migrate data from Access to SQL 2005 Migrate data from Access SQL 2005 |
|||||||||||||||||||||||
