Solved

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

Posted on 2006-06-23
7
1,591 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
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Read about achieving the basic levels of HRIS security in the workplace.
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…

896 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now