SQL script as scheduled task

[denniswb]

I have a lengthy SQL script that creates several tables in several databases and culminates in a single table for use in a Crystal report. It runs nicely in isql. Is there a way that I can run this script as a scheduled task? It is way larger than the 255 character limit for TSQL in Scheduled Tasks.

Thanks

Dennis

 

[foxdev]

Can't you make a stored procedure of it, then EXECute it from the scheduled task?

Robert Bradley

http://www.foxdev.com/

 

[denniswb]

I thought of that but I believe that would require that I break it up into a stored procedure for each database as USE cannot be used in a stored procedure. Then I need to merge data from 4 databases into 1 after processing in each.

Dennis

 

[Scott2S]

Schedule a task as a type CmdExec and use this as the command, populated your required data like password and database name:

c:\mssql\binn\isql /U sa /P PASSWORD /d DATABASENAME /i c:\mssql\binn\yourfile.sql /o c:\mssql\binn\yourfile.out

You can also use a batch file and have the batch file called by the NT schedule service.

 

[denniswb]

I used your first method on a test script (I had been trying it this way but didn't get it quite right) and it worked. Thanks a lot for your help.

Dennis