Post reply

Executing query string(of Text datatype) stored in a temporary table

  • January 24, 2006 at 8:25 pm

    #167911

    I have a dynamically genarated query string stored in a temporary table in a column of datatype NTEXT.

    I am unable to execute the string using

    EXECUTE sp_executesql <select TextCol from #TextTable>,

    an error message something like, [@stmt] is not provided to the stored procedure sp_executesql, is poped up.

    Is there any way to execute the string,

    Thanks in advance

    Raj

     

  • January 24, 2006 at 9:20 pm

    #616809

    Did you enclose the select string in quotes?

  • January 24, 2006 at 9:40 pm

    #616814

    thx for you reply

    No, i did not, i wish to execute the output of the query - select TextCol from #TextTable,

    i.e., select TextCol from #TextTable might return a text like "Select * from Product", this particular string has to be executed,

    usually the result from the query (select TextCol from #TextTable ) is not simple as mentioned above, it contains a large query of size exceeding 20000 chars

  • January 25, 2006 at 1:11 am

    #616823

    I think the best you are going to do with a 20,000 character string is to save to a .SQL file, and then use OSQL to execute it.

    you can't use sp_executesql, the @stmt parameter cannot be ntext due to the fact that you cannot delare a variable that is a ntext datatype and you cannot concatenate.

    You could break the ntext column into 4,000 nvarchar chunks, then use EXEC (@part1+@part2+@part3+...), you can concatenate variables but this is really best for smaller length strings, re BOL: "For optimum performance, do not use more than 4,000 characters."

    For either, your #TextTable is local to the EXEC point and cannot be seen by the EXEC statement.

    Andy

  • January 25, 2006 at 7:43 am

    #616906

    Thank you very much Andy,

    will solve my problem i guess

Viewing 5 posts - 1 through 5 (of 5 total)

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