How can I execute a query stored in a text field

Question

Post reply

How can I execute a query stored in a text field

Mark Bosomworth

Say Hey Kid

Points: 669
More actions
August 25, 2005 at 3:02 am

#102738

I have a query which is automatically generated and stored in a text field. The query is > 8000 bytes long. If it was <4000 I could assign it to a variable and use sp_executesql @sql.
Alternatively I could call a procedure declaring a variable as ntext which is passed in (not declared locally) and still use sp_execute @sql. However the problem is how do I initialise the ntext variable in the stored procedure in the first place.?
Thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

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

peterhe SSChampion Points: 11469 More actions · Answer 1

If you do not care about the context, you can pass the file name as a parameter in your SP and call isql to run it.

CREATE PROCEDURE dbo.usp_RunScript @ScriptFile nvarchar(255)

AS

DECLARE @Command nvarchar(2000)

DECLARE @ServerName nvarchar(255)

SELECT @ServerName=CAST(SERVERPROPERTY('servername') as nvarchar(255))

SET @Command=N'isql -S '+@ServerName+' -d '+DB_NAME()+' -E -i"'+@ScriptFile+'"'

EXEC master..xp_cmdshell @command

You can also sspecify an output file name. Please check with BOL

peterhe SSChampion Points: 11469 More actions · Answer 2

Sorry, I read the question as query stored in text file instead of text field. The above mentioned solution does not work for text field.

If you know the possible maximum length of the text field, you can define several nvarchar(4000) local variables and use substring function to get the chunks from the text field. Then run the dynamic query.

EXEC (@V1+@V2+@v3...)

peterhe SSChampion Points: 11469 More actions · Answer 3

Anotther solution is query the text field to a file and then run it in my previous suggested way.

To query the result to a file:

DECLARE @Command nvarchar(255)

DECLARE @ServerName nvarchar(255)

SELECT @ServerName=CAST(SERVERPROPERTY('servername') as nvarchar(255))

SET @Command=N'isql -S '+@ServerName+' -d your db -E -Q "SET NOCOUNT ON SELECT TOP 1 TextField AS [--Script] FROM YourTable" -o"C:\Test.Sql"'

EXEC master..xp_cmdshell @command

The trick is you have to use [--XXXX] as the alias name. Or use -h-1 switch to specify no headers in the output.

Mark Bosomworth Say Hey Kid Points: 669 More actions · Answer 4

Thanks for the replies Peterhe. I cannot use xp_cmdshell as the use of this is restricted at work. I also cannot just use exec as I need to get a return value and so use sp_executesql @sql, N'@return nvarchar(4000) output'.

It looks like a temporary table solution.

Thanks for your suggestions.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Just so you know, you can use VARCHAR(8000) for this... won't help with things over 8000 characters like Peter's suggestion but you don't have to limit yourself to 4000 characters using NVARCHAR.

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

peterhe SSChampion Points: 11469 More actions · Answer 6

Ok. I don't know xp_cmdshell is disallowed in your environment.

If the query in th text field is >4000chars, sp_execsql does not work. However, you can concatenate multiple 8000bytes string and run it in EXEC. As for return value, you can get it in a tempory table.

CREATE TABLE #Result (RtnVal int NOT NULL)

SET @V3=' INSERT #Result (RtnVal) VALUES (0)'

EXEC (@V1+@V2+@v3)

SELECT RtnVal FROM #Result

You can use the tempory table to return whatever values you want.

Sergiy SSC Guru Points: 110208 More actions · Answer 7

CREATE PROCEDURE dbo.MyProc @Script ntext

AS

......

GO

EXEC dbo.MyProc ''

and assign your SQL string to @Script inside SP.

_____________
Code for TallyGenerator