Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2006-06-23
7
Medium Priority
?
1,636 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

705 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