Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Invoking VB Script from a trigger
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 06/17/2003 :  08:08:11  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Rothers writes "Can a trigger invoke a VB script? I need to update some text files whenever an insert happen to the source table. The destination are text files, and not SQL tables. I can create a audit table and insert values on source table insert, but was looking for the alternate options.. Is it possible? I am using MS SQL Server 2000

Thannks in advance."

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 06/21/2003 :  16:04:29  Show Profile  Visit Stoad's Homepage  Reply with Quote
Maybe this help.

declare @vbtext varchar(3000)
select @vbtext=
'Set fso = CreateObject("Scripting.FileSystemObject")'
+char(13)+char(10)+
'Set f = fso.OpenTextFile("c:\text.txt", 1)'
+char(13)+char(10)+
'f.Close'

exec spRunScript @vbtext
======================================
!! Check your registry for MSScriptControl being installed !!

CREATE PROCEDURE spRunScript @script_text varchar(8000), @language varchar(8)='VBScript' AS

DECLARE @object int, @hr int

EXEC @hr = sp_OACreate 'MSScriptControl.ScriptControl', @object OUTPUT
IF @hr <> 0 GOTO SCR_ERROR
EXEC @hr = sp_OASetProperty @object, 'Language', @language
IF @hr <> 0 GOTO SCR_ERROR
EXEC @hr = sp_OASetProperty @object, 'Timeout', 60000 -- 1 min
IF @hr <> 0 GOTO SCR_ERROR
EXEC @hr = sp_OASetProperty @object, 'AllowUI', 0
IF @hr <> 0 GOTO SCR_ERROR
EXEC @hr = sp_OAMethod @object, 'ExecuteStatement', NULL, @script_text
IF @hr <> 0 GOTO SCR_ERROR
EXEC @hr = sp_OADestroy @object
IF @hr <> 0 GOTO SCR_ERROR

RETURN 0

SCR_ERROR:
-- print 'Oops!'
RETURN -101



Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/21/2003 :  17:41:23  Show Profile  Visit nr's Homepage  Reply with Quote
There are many ways to write to a file from t-sql. I tend to use bcp for tables but can also use redirection and osql if you wish.

I wouldn't advise doing this from a trigger as the transaction will hold locks until the trigger completes.
Beter to insert into a table an have a scheduled job to output the data.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 06/21/2003 :  18:47:55  Show Profile  Visit Stoad's Homepage  Reply with Quote
nr,

I totally agree with you and would like to add that a combining spRunScript procedure
with spAsyncSQL procedure http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=26889
will solve the locking problem mentioned by you. Maybe I am wrong. I am a SQL Server 7.0 beginner.

BTW, MSScriptControl can be downloaded from Microsoft site (sct10en.exe 204 kB).

Go to Top of Page

kelleyb
Yak Posting Veteran

USA
61 Posts

Posted - 06/23/2003 :  18:41:00  Show Profile  Visit kelleyb's Homepage  Reply with Quote
Would using an extended stored procedure be easier than creating and executing a stored procedure programatically?
SELECT @cmdstr = 'C:\scripts\MyVBScript.vbs'
EXEC master..xp_cmdshell @cmdstr
Just my $0.02

-Brian




Edited by - kelleyb on 06/23/2003 18:54:05
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 06/24/2003 :  05:02:24  Show Profile  Visit Stoad's Homepage  Reply with Quote
Certainly it's the simplest way - if your scripts are static.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000