Invoking .sql script in Query Analyzer 1

[AtomicChip]

Hello,

I'm wondering if there's any way to invoke a .sql script from code in Query Analyzer. The reason that I'm wanting to do this is to execute multiple scripts at the same time (without having to open and execute them seperately).

I know this could be done via app programming (i'm a c# developer), but I was wondering if it can be done from QA, as it's not something that I'll be running often enough to warrant spending time on an app.

Make sense? I hope so.
Thanks in advance.

-----------------------------------------------

http://www.liquidfuzion.com

"The night sky over the planet Krikkit is the least interesting sight in the entire universe."
-Hitch Hiker's Guide To The Galaxy

 

[ClayG]

There is no Good way.

The best (or least horrible) way to comibine script calls together is with a .bat file, using calls to isql:

isql -i script1.sql
isql -i script2.sql

(You need more parameters to isql, and some error handling is advisable)

But that is not in QueryAnalyzer as you asked.

You could take those isql calls and put them inside xp_cmdshell, that makes it a SQL script you can use in QA. But thats fairly horrible.

Another way would be to read the script files into a, then execute the variable string with "execute" or xp_executesql. A bit better than cmdshell+isql, still not great. Reading text files is clumsy too (MSDAC text driver?, TextCopy command?, sp_OA*?).

 

[collierd]

May or may not help

Within Enterprise Manager you could create a package that
executes multiple queries (Execute SQL Task)
This then runs scripts via a package invoked by VB (slot in where appropriate)

This bit of code can used to execute the package via VB

Sub ExecuteADTSPackage
  Set oPKG = Createobject("DTS.Package")
  oPKG.LoadFromSQLServer "<Server Name>", , , 256, , , , "<Package Name>"
  oPKG.Execute
  oPKG.UnInitialize
  Set oPKG = Nothing
End Sub

Damian.

 

[AtomicChip]

Thanks Clay, although that wasn't the answer that I was hoping for, it was the one I was looking for.

-----------------------------------------------

http://www.liquidfuzion.com

"The night sky over the planet Krikkit is the least interesting sight in the entire universe."
-Hitch Hiker's Guide To The Galaxy

 

[ESquared]

copy a.sql + b.sql + c.sql + d.sql + ... + z.sql all.sql

then run all.sql

 

[ProdAir]

if you want to do it in Query Analyzer, you may want to look into xp_cmdshell