Solved

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

Posted on 2006-06-23
7
1,630 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
Industry Leaders: 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

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!

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…

617 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