Executing A No. of queries thru another query

Dear Sir,

My database have a no. of tables. I have created separate sql files for different tables. Now i want to create all the tables by creating another sql file which will contain the individual sql files.

for example

USE DealSoft

EXEC("c:\SQLAccountTypes.sql")

EXEC("c:\\SQLAccounts.sql")

EXEC("c:\\SQLParties.sql")

EXEC command doesn't work this way. can u suggest the proper syntax.

with regards

wilfi

You could use xp_cmdshell for execute any external program and you could run osql script.sql from command line.

As result you could run:

Code Snippet

xp_cmdshell 'osql C:\yourScript.sql'

or (only in SQL Server 2005)

Code Snippet

xp_cmdshell 'sqlcmd C:\youScript.sql'

May be you need to configure account for executing external apps. You could use sp_xp_cmdshell_proxy_account stored procedure

|||

Wilfi,

If using SQL Server 2005, you might want to consider creating an SSIS package to perform such an action. With SQL Server 2000 that solution becomes "using a DTS package."

Another aspect of this matter would be to put your SQL scripts into stored procedures. You can have one stored procedure invoke multiple other stored procedures, similar to the BATCH action you have described. This plan works for any version of SQL Server, as far as I know.

Dan

|||

Dear Dan,

I would like to invoke my individual Stored Procedures thru a Master S.P. as suggested by you.

Can u tell me the syntax for the same with a small example(of Master S.P.).

thanking U.

With Regards,

wilfi

|||

Code Snippet

--Create procedures

CREATE PROCEDURE mySp1
as
BEGIN
PRINT 'Call To MySP1'
END
go

CREATE PROCEDURE mySp2
as
BEGIN
PRINT 'Call to MySP2'
END
go

CREATE PROCEDURE myMasterSP
as
BEGIN
EXEC mySP1
EXEC mySP2
END
go

-- Execute Master SP, you could do it any time after creating

myMasterSP

|||

Dear Sir,

Hearty Thanx for the immediate response. I could do as suggested by u.

with regards,

wilfi

|||

Konstantin,

Thanks! You beat me to it! ;-)

Dan

|||

Hey Konstantin, i was lookin for something related to inline store procs and saw your post..

do you know if those 2 last procs : mySP1 and mySP2 run async.

meaning, does the mySP2 proc waits for the mySP1 to be completed?.

It will be very helpful if you know!

anyways thanks in advance

Dave.

|||

Dave,

Sorry for butting in. All my experiences are that they run sequentially, in the order listed in the SP.

I would have all kinds of wrong answers in my computations were that not so.

Dan

|||

mySp1 and mySp2 run sync. Meaning the mySp2 wait for the mySp1 to be completed.

If you need async call, you could emulate this approach by using SQL Server Broker