Executing multiple .sql file using execute sql task

Question

Executing multiple .sql file using execute sql task

Ahmad Osama

SSCertifiable

Points: 7676
More actions
December 21, 2008 at 11:01 pm

#126669

Hi All,
I have to execute multiple Sql Files. To do this, I created a FOR EACH loop container to get file names from a folder and then passed them in a variable to an Execute SQL Task....I am getting an error
Executing the query "D:\WFX_BASE_DB_5.1.1\Stored Procedures\XspAutoCloseOrder.sql" failed with the following error: "Incorrect syntax near 'D:'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
[\code]
Regards,
[font="Verdana"]Sqlfrenzy[/font]

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Um.... I don't know much about IIS, but I'm pretty sure that you're going to have to make a trip to OSQL via a command shell call.

The best thing to do would be to turn all those loose scripts into stored procedures and then use your execute SQL task thingy.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 2

Jeff Moden (12/22/2008)
Um.... I don't know much about IIS, but I'm pretty sure that you're going to have to make a trip to OSQL via a command shell call.
The best thing to do would be to turn all those loose scripts into stored procedures and then use your execute SQL task thingy.

Those scripts are stored procedures to deployed on client server. Currently I am using a for loop in dos prompt to iterate thru a folder and execute all sql files...but It doesn't executes the scripts in the order they are kept in folder...though the need is to execute them in order ....

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Maybe you could create a single file containing all the SQL first:

If Exist All_Files.sql Del All_Files.sql

Copy *.sql All_Files.sql /y

and then just execute All_Files.sql to save iterating round - not sure about the order in which the files will be appended though.

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Ahmad Osama (12/22/2008)
Jeff Moden (12/22/2008)
Um.... I don't know much about IIS, but I'm pretty sure that you're going to have to make a trip to OSQL via a command shell call.
The best thing to do would be to turn all those loose scripts into stored procedures and then use your execute SQL task thingy.
Those scripts are stored procedures to deployed on client server. Currently I am using a for loop in dos prompt to iterate thru a folder and execute all sql files...but It doesn't executes the scripts in the order they are kept in folder...though the need is to execute them in order ....

And, what order might that be? Order by name, date, extension, 8.3 name, or ???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

And, what order might that be? Order by name, date, extension, 8.3 name, or ???

haha, I was wondering the same and was hoping to draw out the answer gently, whereas you went straight for the throat 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 6

Jeff Moden (12/23/2008)
Ahmad Osama (12/22/2008)
Jeff Moden (12/22/2008)
Um.... I don't know much about IIS, but I'm pretty sure that you're going to have to make a trip to OSQL via a command shell call.
The best thing to do would be to turn all those loose scripts into stored procedures and then use your execute SQL task thingy.
Those scripts are stored procedures to deployed on client server. Currently I am using a for loop in dos prompt to iterate thru a folder and execute all sql files...but It doesn't executes the scripts in the order they are kept in folder...though the need is to execute them in order ....
And, what order might that be? Order by name, date, extension, 8.3 name, or ???

order by date....

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

Can you post the DOS command you're using... might be able to tweek it for ya.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

Phil Parkin (12/23/2008)
And, what order might that be? Order by name, date, extension, 8.3 name, or ???
haha, I was wondering the same and was hoping to draw out the answer gently, whereas you went straight for the throat 🙂

BWAA-HAAA! It's because I don't have time to "monkey" around. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)