locked
Run file script inside another script RRS feed

  • Question

  • Hi,

    I want to run a script that have SQL code, and then also runs another script that is in a file alocated on a folder.

    Best regards.
    Wednesday, April 22, 2009 10:30 AM

Answers

  • Joao Prata,

    You want to run a script that in turn calls another script which is located in a file? This can be done by putting the following code inside the first sql script:

    declare @FileName  varchar(255)
    declare @SQLLoad   nvarchar(max)

    -- Set the file to load SQL Script from
    set @FileName = 'C:\MyDir\MyScriptFile.sql'

    -- Create command to load and execute the file
    set @SQLLoad =
    N'declare @cmd varchar(max);
    select @cmd = fd.col1
    from openrowset(bulk ' + QUOTENAME(@FileName, '''') + ', SINGLE_CLOB) as FD(col1);
    exec (@cmd)'

    -- Load file data and execute
    exec (@SQLLoad)


    Hope this helps,
    -h
    • Proposed as answer by h0xff Friday, April 24, 2009 6:30 PM
    • Marked as answer by Joao Prata Tuesday, April 28, 2009 8:57 AM
    Wednesday, April 22, 2009 10:38 AM
  • Hi Joao,

    u can execute your script file(.sql) which present in a folder.
    let me explain practically :

    i ve one script file test.sql which have following code

    USE TestDB
    Select * From Employee

    i have put test.sql file on my server at D:\.

    now using OSQL i m  run that file.

    DECLARE @Qry VARCHAR(8000)
    SELECT @Qry = 'osql -E -i D:\test.sql'
    EXECUTE xp_cmdshell @qry

    i hope u get wht u want!

    Thanks,
    Kapil Khalas

    • Marked as answer by Joao Prata Tuesday, April 28, 2009 8:57 AM
    Wednesday, April 22, 2009 12:03 PM

All replies

  • Joao Prata,

    You want to run a script that in turn calls another script which is located in a file? This can be done by putting the following code inside the first sql script:

    declare @FileName  varchar(255)
    declare @SQLLoad   nvarchar(max)

    -- Set the file to load SQL Script from
    set @FileName = 'C:\MyDir\MyScriptFile.sql'

    -- Create command to load and execute the file
    set @SQLLoad =
    N'declare @cmd varchar(max);
    select @cmd = fd.col1
    from openrowset(bulk ' + QUOTENAME(@FileName, '''') + ', SINGLE_CLOB) as FD(col1);
    exec (@cmd)'

    -- Load file data and execute
    exec (@SQLLoad)


    Hope this helps,
    -h
    • Proposed as answer by h0xff Friday, April 24, 2009 6:30 PM
    • Marked as answer by Joao Prata Tuesday, April 28, 2009 8:57 AM
    Wednesday, April 22, 2009 10:38 AM
  • Hi Joao,

    u can execute your script file(.sql) which present in a folder.
    let me explain practically :

    i ve one script file test.sql which have following code

    USE TestDB
    Select * From Employee

    i have put test.sql file on my server at D:\.

    now using OSQL i m  run that file.

    DECLARE @Qry VARCHAR(8000)
    SELECT @Qry = 'osql -E -i D:\test.sql'
    EXECUTE xp_cmdshell @qry

    i hope u get wht u want!

    Thanks,
    Kapil Khalas

    • Marked as answer by Joao Prata Tuesday, April 28, 2009 8:57 AM
    Wednesday, April 22, 2009 12:03 PM
  • Joao,

    If you use Kapil's solution make sure you have an account with the correct permissions to run OS commands, also make sure you have xp_cmdshell enabled in Surface Area Sonfiguration. This may lead to security vulnerabilities.

    Hope this helps,
    -h
    Wednesday, April 22, 2009 12:06 PM
  • Hi Joao,

    h0xff is right if u want to use osql or sqlcmd command then u need to checked xp_cmdshell option. That option u can find :

    Microsoft SQL Server 2005 >> Configuration Tools >> SQL Server Surface Area Configuration >> Surface Area Configuration for Features >> go to xp_cmdshell and checked Enable xp_cmdshell option.

    After  done this change then there is no anything u need to do. Ther is no any other Security vulnerabilities and u don't need any permission to run os command.
    Please goto this url it will helps u lot:
    http://msdn.microsoft.com/en-us/library/aa214012(SQL.80).aspx

    Best of luck!


    Thanks, Kapil Khalas
    Sunday, April 26, 2009 8:41 AM
  • Hi,

    Thank you for the reply. I cannot use that implementation because i dont have permissions to put files on drive of production server, and this server can be moved to another place.
    But that solution is correct, and in another situation i will use it.

    Best regards,
    Joao.

    Tuesday, April 28, 2009 9:01 AM