Create Trigger from within ActiveX Script
-
I've got an ActiveX script (inside a DTS package) that is dynamically creating triggers on my table based off of the table schema (it selects a list of columns, and then does stuff with that).
My main problem so far (besides the fact that my trigger worked before, when I had it typed out manually) is that I can't seem to embed Carriage returns (or line feeds) in my trigger. Then if I go to look at my trigger it shows all on one line. This makes it very difficult to read / edit / debug. (I am including CRLF's in my query statement.)
Anybody seen this before / have any ideas ??
Here's what my activeX script looks like:
strConnect = "Provider=SQLOLEDB;Data Source=UNIMOG;Initial Catalog=Product;User Id=sa;Password=; "
strQuery = "SELECT name FROM syscolumns WHERE OBJECT_NAME (id) = 'products' ORDER BY colid"
Set rstColumns = CreateObject("ADODB.Recordset")
rstColumns.LockType = 3 'adLockOptimistic
rstColumns.Open strQuery, strConnect, , , 1 'adCmdText
strCreateTrigger = "/* automatically generated by a vbscript in DTS Package: Trigger_Test_2. Rerun this if you want to update. */ " & CRLF _
& "CREATE TRIGGER [TR_products_update] ON [dbo].[Products] " & CRLF _
& "FOR UPDATE " & CRLF _
& "AS " & CRLF
strQuery = "IF EXISTS (SELECT name FROM sysobjects WHERE name = 'TR_products_update' AND type = 'TR') DROP TRIGGER TR_products_update"
Set rstTrigger = CreateObject("ADODB.Recordset")
rstTrigger.LockType = 3 'adLockOptimistic
rstTrigger.Open strQuery, strConnect, , , 1 'adCmdText
Set rstTrigger2 = CreateObject("ADODB.Recordset")
rstTrigger2.LockType = 3 'adLockOptimistic
rstTrigger2.Open strCreateTrigger, strConnect, , , 1
-
CRLF is not a known variable and activex will create an empty variable.
Try putting
CRLF = chr(13) & chr(10)
at the beginning of your script
Far away is close at hand in the images of elsewhere.
Anon. -
David.
You are a genius. Thank you!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply