run scripts in one script

Question

run scripts in one script

CooLDBA

Hall of Fame

Points: 3301
More actions
September 12, 2006 at 3:11 pm

#75057

I have 10 scripts like 1.sql, 2.sql, etc...
I want to put in one script to call all the script.
Any ideas how to do it in SQL2000?
This is possible in SQL Plus, no idea in SQL 2000...
maybe in command prompt?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

StormNorm SSCommitted Points: 1654 More actions · Answer 1

Here is a little ActiveX script that you can put into a DTS package that can do it, assuming you place all the files to combine in a directory named c:\test, and the output file is a file named c:\Output.sql.

'**********************************************************************

' Visual Basic ActiveX Script

'************************************************************************

Function Main()

'Specify addl location inf about the file for inclusion in the header...

myLocation = ""

Set fso1 = CreateObject("Scripting.FileSystemObject")

Set f1 = fso1.CreateTextFile("c:\Output.sql", True)

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFolder = objFSO.GetFolder("c:\test")

For Each file in objFolder.Files

FileName=file.name

FileFullName = "c:\test\" & FileName

f1.WriteBlankLines 1

f1.Write "GO"

f1.WriteBlankLines 1

f1.Write "RAISERROR ('Running Script """ & FileName & """', 10, 1) WITH NOWAIT"

f1.WriteBlankLines 1

f1.Write "GO"

f1.WriteBlankLines 1

f1.write "----------------------------------------------------------------" & vbCrLf

f1.write "--" & vbCrLf

f1.write "-- ~~~ " & myLocation & FileName & vbCrLf

f1.write "--" & vbCrLf

f1.write "----------------------------------------------------------------" & vbCrLf

f1.WriteBlankLines 1

Set fso2 = CreateObject("Scripting.FileSystemObject")

set f2 = fso2.OpenTextFile(FileFullName, 1)

do until f2.AtEndOfStream

strLine = f2.Readline

f1.write strLine & vbCrLf

loop

f1.WriteBlankLines 1

f1.Write "GO"

f1.WriteBlankLines 1

f1.Write "RAISERROR ('Finish Script """ & FileName & """', 10, 1) WITH NOWAIT"

f1.WriteBlankLines 1

f1.Write "GO"

f1.WriteBlankLines 1

f2.close

Set fso1 = Nothing

Set fso2 = Nothing

Main = DTSTaskExecResult_Success

End Function

CooLDBA Hall of Fame Points: 3301 More actions · Answer 2

CooLDBA

Hall of Fame

Points: 3301

September 12, 2006 at 5:15 pm

#659991

Thanks!

I will try this!

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 3

you can also use xp_cmdshell to run isql scripts :

declare @myISQLString varchar(5000)

set @myISQLString = 'isqlw -E -S yourserver -d yourdb -i "yourscriptpathandfile" -o "yourresultpathandfile"'

exec master.dbo.xp_cmdshell @myISQLString

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

CooLDBA Hall of Fame Points: 3301 More actions · Answer 4

CooLDBA

Hall of Fame

Points: 3301

September 13, 2006 at 9:02 pm

#660286

Thanks for the inputs guys!