February 7, 2008 at 3:13 pm
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.
February 7, 2008 at 5:11 pm
You can't do that way. its better u code that as a SP.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 7, 2008 at 9:58 pm
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
February 8, 2008 at 6:03 am
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.
February 8, 2008 at 9:51 am
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.
February 11, 2008 at 9:22 am
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.
February 11, 2008 at 9:49 am
Here is an article on writing to a file using the filesystem object and sp_oacreate
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]
February 11, 2008 at 7:36 pm
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
Change is inevitable... Change for the better is not.
February 11, 2008 at 8:03 pm
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'
April 30, 2009 at 8:50 am
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply