July 24, 2011 at 7:00 pm
Hello All,
I have a requirement to build a user interface that will allow end users to generate and issue UPDATE statements. I am fairly certain I need dynamic SQL for this purpose. My plan is to pass parameters to a stored procedure.
My UPDATE statements will look like this:
UPDATE LinkedServer.LinkedServerDatabase.dbo.Transactions
SET MyFlag = 1
WHERE
Field1 = 'This' AND
Field2 = 'That' AND
(Field3 = This OR Field4 = 'That' AND....
This seems to be the classic case of the dynamic search problem except I need to update records rather than search for them.
The target database I will be updating is SQL Server 2000. I want to execute the UPDATE statements from a SQL Server 2008 system.
How can I audit these changes? Triggers are not an acceptable solution, neither is using the transaction logs.
A separate table capturing the record before update seems the best solution for me.
I believe I need two statements then.
INSERT INTO Transactions_Audit
SELECT Field1, etc....,getdate() AS AuditTimeStamp
FROM LinkedServer.LinkedServerDatabase.dbo.Transactions
WHERE
Field1 = 'This' AND
Field2 <> 'That' AND
(Field3 = This OR Field4 = 'That' AND....
UPDATE LinkedServer.LinkedServerDatabase.dbo.Transactions
SET MyFlag = 1
WHERE
Field1 = 'This' AND
Field2 <> 'That' AND
(Field3 = This OR Field4 = 'That' AND....
How do I know if dynamic SQL is absolutely necessary? There will be about ten field fields that could possibly be referenced in my WHERE statement. I will have to check equality and inequality. I may have OR statements with a varying number of fields.
Assuming I need to use dynamic SQL then:
How do I insure that both statements get executed together?
How do I insure that my WHERE statements are consistent? Is this just a case of building the WHERE once?
Thanks
July 24, 2011 at 8:07 pm
Hi
Who is your users?
Can they write SQL?
I'd say dynamic SQL is only required if they know nothing about SQL. Maybe depending on how much they know they can write a statement and you can build in the code to encapsulate it onto the linked server. That way you execute it with sp_executesql.
Do you get data back from the query ?
How many tables are you talking here ? , do you generate a dynamic list from all the linked servers.sysobjects ? do you assume the table \ objects exists everywhere ? Which level do you handle the error checking?
So a few questions to this one, unless you pull up the connection into your application via .NET provider or similar you are going to use dynamic SQL.
Cheers
Jannie
July 24, 2011 at 8:18 pm
>>Who is your users?
Business users making a decision on if they want to set MyFlag = 1
>>Can they write SQL?
Not at all. They need a user interface that will allow them to set the criteria.
>>Do you get data back from the query ?
I only need an update statement.
>>How many tables are you talking here ?
Only one table will be updated based on criteria in the same table.
>>do you generate a dynamic list from all the linked servers.sysobjects ?
No. There is only one linked server and I know what it is.
>> do you assume the table \ objects exists everywhere ?
No. It exists in the one place i know it exists.
>>Which level do you handle the error checking?
Server.
July 24, 2011 at 8:57 pm
if it's only 1 table where an update flag is going to be flipped.
I'd say write a proc that either set the flag to 1 or 0.
Apply proc to you databases.
Called proc on the relevant databases using ado.net connection at runtime.
If you call a proc you can do all kinds of auditing in there.
Cheers
Jannie
July 25, 2011 at 1:25 pm
Chrissy321 (7/24/2011)
INSERT INTO Transactions_AuditSELECT Field1, etc....,getdate() AS AuditTimeStamp
FROM LinkedServer.LinkedServerDatabase.dbo.Transactions
WHERE
Field1 = 'This' AND
Field2 <> 'That' AND
(Field3 = This OR Field4 = 'That' AND....
UPDATE LinkedServer.LinkedServerDatabase.dbo.Transactions
SET MyFlag = 1
WHERE
Field1 = 'This' AND
Field2 <> 'That' AND
(Field3 = This OR Field4 = 'That' AND....
I wouldn't do it this way. For 2 reasons. One depending on the size of your table you are doing an awful lot of work in your where clause, and here you are doing it twice. And even if that isn't an issue, there is always the possibility that someone is going to insert a row that matches your where clause, or run an update that changes something, between your 2 statements. You could put a transaction around them, but how about instead adding a "myflagupdate" date column. and do the following:
DECLARE @MyFlagUpdate datetime
SET @MyFlagUpdate = getdate()
UPDATE LinkedServer.LinkedServerDatabase.dbo.Transactions
SET MyFlag = 1, MyFlagUpdate = @MyFlagUpdate
WHERE
Field1 = 'This' AND
Field2 <> 'That' AND
(Field3 = This OR Field4 = 'That' AND....
INSERT INTO Transactions_Audit
SELECT Field1, etc.... -- MyFlagUpdate can be used as your audit time stamp
FROM LinkedServer.LinkedServerDatabase.dbo.Transactions
WHERE MyFlagUpdate = @MyFlagUpdate
or
SELECT * INTO #temp FROM LinkedServer.LinkedServerDatabase.dbo.Transactions
WHERE
Field1 = 'This' AND
Field2 <> 'That' AND
(Field3 = This OR Field4 = 'That' AND....
UPDATE LinkedServer.LinkedServerDatabase.dbo.Transactions
SET MyFlag = 1
WHERE EXISTS (SELECT * FROM #temp WHERE (join using primary key to transactions)
INSERT INTO Transactions_Audit
SELECT *, getdate() FROM #temp
That being said, triggers would be better if there is any way you can work around that restriction.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
July 25, 2011 at 1:54 pm
Chrissy321 (7/24/2011)
Hello All,I have a requirement to build a user interface that will allow end users to generate and issue UPDATE statements. I am fairly certain I need dynamic SQL for this purpose. My plan is to pass parameters to a stored procedure.
...
First, all the users need are EXEC permission on the stored procedure; they don't need INSERT/UPDATE/DELETE permission on the tables.
If you're using SQL Server 2008, then you could implement DML auditing by creating a database audit specification, however, that might be overkill.
What I would suggest, if all update requests of interest come though this single procedure call, then you can create a table for auditing purposes that receives one row insert for each call. It would basically contain columns for UID, date/time, parameter(s) specified, and perhaps the number of rows updated. This table could then be easily queried when needed.
You may even want to implement a threshold feature so that if the number of rows updated exceeds a certain maximum limit, the transaction will be rolled back.
For example:
begin tran;
delete from .. where ..;
if @@rowcount < 1000
commit tran;
else
rollback tran;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply