Solved

How to run .sql file directly from query analyzer (MS SQL 2000).

Posted on 2006-06-23
7
1,618 Views
Last Modified: 2012-06-21
I am attempting to run several sql scripts originating in different .sql files. How can I call these files / print files in query analyzer?

Example:

USE DB

Execute all contents in scriptone.sql
Execute all contents in scripttwo.sql
etc.


I have many datatransfer scripts and table populating scripts that I want to run all at once but do not want them to be in the same file for organization.

Thanks in advance.


0
Comment
Question by:tcknudson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16973478
You will have to open each one and execute from SQL Query Analyzer.
0
 

Author Comment

by:tcknudson
ID: 16973482
Not quite the answer I was looking for. What about inserting the textfile contents into a variable and executing that? Stored procedures? Functions? Bribery, anything???
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16973503
From SQL Query Analyzer that is your only realistic choice.  Alternatively consider using osql to run from the command line.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Accepted Solution

by:
Winsen earned 500 total points
ID: 16973812
Umm... Can you create a stored procedure for each of the script, and then run it like your example? You may:

CREATE PROCEDURE proc1
AS
BEGIN
    -- The content of scriptone.sql goes here...
END
GO

CREATE PROCEDURE proc2
AS
BEGIN
    -- The content of scripttwo.sql goes here...END
GO

Then run it with:
EXEC proc1
EXEC proc2
0
 

Expert Comment

by:PaulThomasSilvey
ID: 26550720
DECLARE @strSQL nvarchar(max)
DECLARE @dbname nvarchar(max)

/*  
--- code here to select your specific database
-- example below
SELECT   name
into #tmpDB
from sysdatabases where name not in
 ('master','model','msdb','tempdb','dbadata', 'ReportServer','ReportServerTempDB')
*/

--- build your dynamic sql
SELECT top 1 @dbname = name from #tmpDB
WHILE @dbname is not null
BEGIN

--- build your osql statement and run it in xp_cmdshell (you will need to really clean up my tsql below
SELECT @strSQL = 'xp_cmdshell ''osql  -d' + @dbname + '  -  your other parameters and sql files'''
EXEC (@strSQL)

---after that one runs delete the one you just ran
DELETE FROM #tmpdb where name = @dbname

--- zero db name so when it doesn't pull one it will still be null
SELECT @dbname = null

--- get next db name
SELECT top 1 @dbname = name from #tmpDB

END

--- all done



0
 

Expert Comment

by:PaulThomasSilvey
ID: 26550849
Here is a working version from my system

DECLARE @strSQL nvarchar(max)
DECLARE @dbname nvarchar(max)
DECLARE @server nvarchar(max)

SELECT @server = 'PQA-IVDB'



SELECT   name
into #tmpDB
from master.dbo.sysdatabases where name not in
 ('master','model','msdb','tempdb','dbadata', 'ReportServer','ReportServerTempDB'
 ,'dbdata','RPQ00003_Logging','RPQ00008_Logging')

--- build your dynamic sql
SELECT top 1 @dbname = name from #tmpDB
WHILE @dbname is not null
BEGIN

--- build your osql statement and run it in xp_cmdshell (you will need to really clean up my tsql below
SELECT @strSQL = 'xp_cmdshell ''sqlcmd  -d ' + @dbname + ' -S ' + @server + ' -E -i "\\pqc-nas\engineering\Builds\eHR\ProdSupport\eHR.1.0.0.1\DBDeployment\eHR Conslidated upgrade script 02102010.sql"'''
--EXEC (@strSQL)
select @strSQL

---after that one runs delete the one you just ran
DELETE FROM #tmpdb where name = @dbname

SELECT @dbname = null

SELECT top 1 @dbname = name from #tmpDB


END

drop table #tmpDB

0
 

Expert Comment

by:PaulThomasSilvey
ID: 26550856
Also, use osql since this is sql 2000
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

761 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question