locked
execute large block of sql using Execute SQL Task RRS feed

  • Question

  • Hi,

    I have a sql string about 5000 lines of code needed to run in the remote server everyday around 4am.

    Seems liked there is a limit on the length of the SQLStatement in Execute SQL Task.  Because more than half of the code is truncated.

    I've tried putting this code using openquery and there are several hundred of ' which break the OPENQUERY.

    What is the fastest way to schedule a job running at 4am to get the result set from this monster query?

    Thanks!


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    • Moved by Tom Phillips Monday, August 19, 2013 5:01 PM SSIS question
    Friday, August 16, 2013 5:08 PM

All replies

  • Seems liked there is a limit on the length of the SQLStatement in Execute SQL Task

    Hello,

    I know "Execute SQL Task" from SSIS and SQL Server Maintenance Plan; which one exactly are you using?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, August 16, 2013 5:17 PM
  • Can this be written as a stored procedure? Then you could execute the stored procedure instead of storing the sql statement.

    If you are trying to run this from an SSIS package, store the sql statement in a file. You can execute the SQL stored in the file from an Execute SQL Task. 


    Joe Fedak

    Friday, August 16, 2013 5:23 PM
  • good pt. :)ssis toolbox -- Execute SQL task

    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Friday, August 16, 2013 6:32 PM
  • remote server will no allow additional object or change permission.  yea, bone heads. :)

    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Friday, August 16, 2013 6:32 PM
  • i like to try the idea of execute SQL from file.  is there tutorial on passing the result from file to ssis env variable then to SQL Statement for remote execution?


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.



    • Edited by light_wt Friday, August 16, 2013 7:01 PM
    Friday, August 16, 2013 6:33 PM
  • Hi light_wt,

    You can refer to the following documents about how to create a mapping between a result set and a variable in an Execute SQL task.

    Map Result Sets to Variables in an Execute SQL Task
    http://technet.microsoft.com/en-us/library/ms141689.aspx

    Passing Variables to and from an SSIS task
    https://www.simple-talk.com/sql/ssis/passing-variables-to-and-from-an-ssis-task/


    Allen Li
    TechNet Community Support

    Monday, August 19, 2013 8:25 AM
  • Thanks, Allen. I will try them.

    One small question, is there a tutorial on how to read the file with SQL command and execute it in Execute SQL Task?

    Thank you!


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Monday, August 19, 2013 2:04 PM
  • Consider using SQLCMD.

    -i option is the input .sql file.

    SQLCMD example:

    http://www.sqlusa.com/bestpractices2005/restoredbwithsqlcmd/

    BOL: "-i input_file[,input_file2...] 
    Identifies the file that contains a batch of SQL statements or stored procedures. Multiple files may be specified that will be read and processed in order. Do not use any spaces between file names. sqlcmd will first check to see whether all the specified files exist. If one or more files do not exist, sqlcmd will exit. The -i and the -Q/-q options are mutually exclusive.

    Path examples:

    -i C:\<filename>

    -i http://technet.microsoft.com/en-us/library/ms162773.aspx


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Monday, August 19, 2013 2:17 PM
  • nice suggestion. 

    the problem here is that file with 5000 lines of SQL command cannot be stored where the instance is installed.

    is there other option?


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Monday, August 19, 2013 6:39 PM
  • just keeping a note to myself.  a variable which can be varchar(max) and 8000 char long only...

    my job stink.


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Tuesday, August 20, 2013 2:06 PM