December 3, 2003 at 1:48 am
Hello
Here is my issue. Certain data is held within my production db that is considered dangerous. Output file paths for instance. I have a T-SQL script that I run after restoring a backup to a test environment, but I have to remember to do it 🙂
I want to write an app (using VB) that restores the db, & then runs the script. The problem is I am using the ExecuteWithResultsAndMessages2 method, and it does not like 'GO' command separators in the script.
OK, no problem, I'll break the script down into separate files, and cycle through. This works fine until I come to a script that contains:
CREATE TRIGGER TR_UP_TABLE ON Table_T
FOR UPDATE
AS
UPDATE table_t
SET DateUpdated = getdate()
WHERE columnName = (SELECT o.ID FROM deleted as o)
This fails with the message:
Invalid Object Name 'table_T'
The code works fine in Query Analyser, so can some one tell me whats going on. Or to solve my problem, how to create a trigger defined in an external file in DMO?
ta
Jacko
Edited by - jacko999 on 12/03/2003 03:48:38 AM
December 3, 2003 at 4:35 am
Dont know. Not the best answer! Sure you are in the right db?
I'll experiment, in the interim there are a couple alternatives. One is to execute it using OSQL or ISQL, both understand the batch separator and you know it works. The other is to use more of an object approach, build a trigger object and load from disk, probably easiest way would be to use an ini file to pull the various values you need.
Andy
December 3, 2003 at 6:58 am
Good Questions, I've asked them all of myself. 🙂
I split the query up into separate files to try & get the trigger in, and the other two, which are updates or inserts into tables, work OK, but this has got me baffled.
I think I need to read this in from a file & create the trigger in 'native' DMO, using the trigger.Add method, But I'm not sure how yet...
Jacko
December 5, 2003 at 11:39 am
How about using ExecuteImmediate instead? I have an application that creates a local database based on the SQL created from a ScriptTransfer method. It has the GO statements embedded within. I feed the script into a ExecuteImmediate from the database object, and no problems with the multiple statements/GO's.
Mark
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply