Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2006-06-23
7
Medium Priority
?
1,653 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 4

Accepted Solution

by:
Winsen earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

571 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