How to run an .SQL script from within SSMS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to run an .SQL script from within SSMS

Author

Topic

Racho
Starting Member

13 Posts

Posted - 2009-09-22 : 06:45:22

Example:

I write a one liner test script such as:

PRINT 'Done'

save this script into root directory of my C Drive so that file pathname is "C:\Test.SQL"

How can I now run this script from within another script?

I've tried the obvious of:

DECLARE @FilePathName VARCHAR (250)
SET @FilePathName = 'C:\Test.SQL'
EXECUTE @FilePathName

but get the error...

Msg 2812, Level 16, State 62, Line 3
Could not find stored procedure 'C:\Test.SQL'.

Don't laugh, but I've got 4 books on my desk about SQL server but none of these make it clear as to how to go about doing this, so would appreciate the help

SQL2008 and Visual Studio 2008 (VB Preferred)

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-09-22 : 06:51:41

Try to use xp_cmdshell stored procedure.

Racho
Starting Member

13 Posts

Posted - 2009-09-22 : 07:14:52

Hi Sanoj_av,

Thanks but tried it as in...


DECLARE @FilePathName VARCHAR (250)
SET @FilePathName = 'C:\Test.SQL'
EXEC xp_cmdshell @FilePathName

and it failed with the following output...
'C:\Test.SQL' is not recognized as an internal or external command,
operable program or batch file.
NULL

SQL2008 and Visual Studio 2008 (VB Preferred)

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-09-22 : 07:21:33

quote:
Originally posted by Racho

Hi Sanoj_av,

Thanks but tried it as in...

DECLARE @FilePathName VARCHAR (250)
SET @FilePathName = 'C:\Test.SQL'
EXEC xp_cmdshell @FilePathName

and it failed with the following output...
'C:\Test.SQL' is not recognized as an internal or external command,
operable program or batch file.
NULL

SQL2008 and Visual Studio 2008 (VB Preferred)

Declare
@DBServerName Varchar(100),
@DBName Varchar(100),
@FilePathName Varchar(100),
@strSql varchar(1000)

Set @DBServerName='SANSERVER'--your Server Name
Set @DBName='SANDB' --Your DB Name
Set @FilePathName='C:\Test.SQL'

Set @strSql= 'sqlcmd -S ' + @DBServerName + ' -d ' + @DBName + ' -i ' + @FilePathName

EXEC xp_cmdshell @strSql

Racho
Starting Member

13 Posts

Posted - 2009-09-22 : 09:07:42

Thanks again

Unfortunately this did not work either... got the message...


Sqlcmd: 'C:\Test.SQL': Invalid filename.
NULL

even though the file name is valid and exists (if I double click on it in Explorer it pops up in SSMS query editor, and when I Execute this line it prints the message "Done", no problem)

Below is my code (exactly as you suggested)


Declare
@DBServerName Varchar(100),
@DBName Varchar(100), 
@FilePathName Varchar(100),
@strSql varchar(1000)

Set @DBServerName='MyServerName'-- Server Name
Set @DBName='MyDBName' -- DB Name
Set @FilePathName='C:\Test.SQL'--Path\FileName

Set @strSql= 'sqlcmd -S ' + @DBServerName + ' -d ' + @DBName + ' -i ' + @FilePathName

EXEC xp_cmdshell @strSql

SQL2008 and Visual Studio 2008 (VB Preferred)

Racho
Starting Member

13 Posts

Posted - 2009-09-22 : 09:45:48

Scrub that!

me bad!

Using SSMS I'd saved the script to my local directory, all works great as soon as I realised my file needed to be on the root of the server for it to be found

Many thanks for your help

SQL2008 and Visual Studio 2008 (VB Preferred)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources