How can I execute an external .sql file from a stored procedure?

Question

Post reply

How can I execute an external .sql file from a stored procedure?

Luiz-458831

SSCommitted

Points: 1904
More actions
February 7, 2008 at 3:13 pm

#120250

Hi Guys,
Is it possible to execute an external .sql file from a stored procedure or job?
Something like this: EXEC sp_xyz(c:\myScript.SQL)
Thanks,
Luiz.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

You can't do that way. its better u code that as a SP.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Vivien Xing SSChampion Points: 12297 More actions · Answer 2

Try this:

1. make a sql file "mysql.sql". Inside the sql file, include your sql statement.

2. make batch file "mybatch.bat".

3a. If use stored procedure, do this:

create procedure mysp

as

exec xp_cmdshell 'c:\dbajob\mybatch.bat'

Note: you need to enable xp_cmdshell from surface area configuration.

3b. If use job, make a job step like this:

job type: CmdExec

job step: c:\dbajob\mybatch.bat

example:

--mysql.sql

sp_who2

go

--mybatch.bat

osql -Sservername -E -ic:\dbajob\mysql.sql > c:\dbajob\mysql.out

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

From a stored procedure, you'd have to use xp_cmdshell.

From a job, you could setup an Active X step and use VBScript to hit the command line with sqlcmd or osql.

Luiz-458831 SSCommitted Points: 1904 More actions · Answer 4

Thank you guys. Those are good suggestions.

Is it possible to load an external file to a table field? something like this:

DECLARE @T TABLE (Code VARCHAR(MAX))

UPDATE @T SET Code = sp_LoadFromFile(c:\mysql.sql)

Thanks,

Luiz.

Luiz-458831 SSCommitted Points: 1904 More actions · Answer 5

Ok. sp_LoadFromFile was just a example. I know there is not such a proc.

But Does somebody know if there is a way to load an external file to a variable or table ?

Thanks,

Luiz.

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 6

Here is an article on writing to a file using the filesystem object and sp_oacreate

http://www.sqlservercentral.com/articles/Miscellaneous/writingtoafileusingthesp_oacreatestoredprocedurean/1694/

I've used the filesystem object like this to read from a file as well but can't remember how I did it off the top of my head. This should give you a good starting place though.

Kenneth

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 7

If the file width is less than or equal to 255 characters, this is a very easy way to do what you ask...

[font="Courier New"]CREATE TABLE #MyFile (LineNum INT IDENTITY(1,1), MyText VARCHAR(255), Continuation INT)

DECLARE @MyPath SYSNAME

SET @MyPath = 'C:\temp\PipeTest.txt'

INSERT INTO #MyFile (MyText,Continuation)

EXEC MASTER.dbo.sp_ReadErrorLog -1,@MyPath

SELECT * FROM #MyFile

/*

NOTE TO SELF... Splits input lines to 255 characters with "Continuations" that appear to be limited to 4*255

First parameter can be -1 or 1 to 99... means nothing, though.

Second parameter should be the full path including the file name.

*/[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ksullivan SSCrazy Points: 2346 More actions · Answer 8

bulk insert and bcp will work too. Both have a lot of options. This is about as simple as they get:

CREATE TABLE #MyFile (MyText VARCHAR(500))

BULK INSERT #MyFile

FROM 'L:\temp\PipeTest.txt'

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 9

The name of the extended command shell proc in this post is intentionally misspelled, because if it is present then there is no way to submit the form (or I don't know how to do it). This behavior is the same on other sites, such as hotmail where if I type the email message which includes the word then I can neither save it as draft nor can I send it. I checked many different ways and it appears that so long as the name appears correctly spelled, submitting the form always results in error.

There is another way to do it. The problem with calling zxp_zcmdzshell is that if the server is configured to not allow it to execute then you will have to do something like this first:

sp_configure 'show advanced options', 1

reconfigure with override

go

sp_configure 'zxp_zcmdzshell', 1 -- name is misspelled on purpose

reconfigure with override

go

-- then do the work and then revert the settings back.

In a mean time, you can take advantage of the varchar(max) datatype to dump the guts of the file into the string and then call to execute it. The varchar(max) does not have 8000 characters limitation and therefore, there is no need to worry about the file size. Here is the script to get the file and execute its guts:

declare @the_script varchar(max)

select @the_script = f.BulkColumn

from openrowset

(

bulk '\\Box_Name\Share_Name\script_file.sql',

single_clob

) f

exec (@the_script)

You may have multiple statements in the file, and the script can be as big as you want, but it cannot include the go word in it as the latter is a batch terminator, and while exec can happily execute the bunch, it cannot execute the batch. The account running the script should have the bulk rights (a member of bulk admin server role). This I think is better than messing with server sonfiguration settings.

Oleg