Calling a sql file from Query Analyzer 3

[spirit66]

Hi,
I'm updating a database with a sql file .I was wondering how to call a sql file inside a sql file .
The sql files are used for updating databases .

Thanks!!!

 

[JayKusch]

By using the oSQL command. Take a look at the syntax in Books OnLine(BOL).

Thanks

J. Kusch

 

[donutman]

I think he's asking how to retrieve a query file from within QA, rather than running it from the command prompt. I don't know the answer. :-(
-Karl

 

[JayKusch]

OUCH!

Maybe use xp_cmdshell to load the text of the sql file into a record w/ a 8000 varchar field size ... if the text is small enough to fit. Then dynamically build the query, w/in the query, by pulling that value from the table.

I am not failiar w/ using other "larger" fields to do this if the sql file is greater than 8000 characters ... But I am SURE some of you other gurus are!

Thanks

J. Kusch

 

[spirit66]

Actually I can run a sql file from a query analyzer which can be done by opening from the menu->file ->open . I'm curious how to call a sql file or query file from another sql or query file.
Thanks!

 

[donutman]

There is a way to shell to dos. Could you then run oSQL from the shell?
-Karl

 

[mrdenny]

Yes, you could use xp_CMDShell to then run osql to run the sql query.

Can't you put the sql code from the sql file into a stored procedure and then just call the procedure?

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[spirit66]

Actually all this exercise is for updating a production database in a streamlined manner .
1.creation of new sql stored procedures
2.updates of old stored procedures
3.create/update tables
4.adding indexes


I was planning on having a bat file and then call all the sqlscripts inside that bat file . I did
try to use OSQL and it seem to work when i call a file inside that bat file.

osql /U sa /P password /d my_database /S localhost /i sqlscript.sql

I tried to use xp_cmdshell command but i noticed i didnt have that extened stored procedure in my
database though its present in master database .

So right now OSQL would do it...but would be nice to call xp_cmdshell .

Thanks!!!

 

[mrdenny]

xp_cmdShell will only be located in master. That is the only place that you can put Extended stored procedures.

You can call that from anywhere.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[spirit66]

Thank you all for the tips!!!