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

  • 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.

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

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

  • 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

  • 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.

  • 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.

  • 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.

  • 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]

  • 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)

  • 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'

  • 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