Dynamic Query With Variable Greater Than 8000 Bytes
Hello Experts,
Im working on a Procedure that will execute a dynamic query, however, the variable which I'm using to execute the query is greater than 8000 bytes.
I've tried to change the variable type to "TEXT", but I cannot assign a TEXT to a variable.
How can I overcome the greater than 8000 byte limitation when trying to use dynamic SQL?
Thank you.
Im working on a Procedure that will execute a dynamic query, however, the variable which I'm using to execute the query is greater than 8000 bytes.
I've tried to change the variable type to "TEXT", but I cannot assign a TEXT to a variable.
How can I overcome the greater than 8000 byte limitation when trying to use dynamic SQL?
Thank you.
ASKER
Wouldn't VARCHAR(MAX) truncate anything greater than 8000 bytes?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
BartVx,
Here's the error that I'm getting now after changing the variable to VARCHAR(MAX):
Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 1
Procedure expects parameter 'command_string' of type 'varchar'.
I'm running the following EXEC master..xp_cmdshell @sCommand
@sCommand is varchar(max)
Here's the error that I'm getting now after changing the variable to VARCHAR(MAX):
Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 1
Procedure expects parameter 'command_string' of type 'varchar'.
I'm running the following EXEC master..xp_cmdshell @sCommand
@sCommand is varchar(max)
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
xp_cmdshell indeed cannot do that ...
so, you need to solve the initial problem differently.
what is the process you try to do?
so, you need to solve the initial problem differently.
what is the process you try to do?
ASKER
In a nutshell, the user enters a query and a bcp command is used to export the query into a text file.
However, if the query is over 8000 bytes, then when we use the xp_cmdshell command, it bombs out. This happends if we run a select to a table that's has over 200 columns, in our case.
However, if the query is over 8000 bytes, then when we use the xp_cmdshell command, it bombs out. This happends if we run a select to a table that's has over 200 columns, in our case.
I would do it this way:
store the "sql" as a view, and run the bcp with the view name, and drop the view afterwards.
=> this will also protect you more against sql injection attempts :)
to ensure you don't get "bombed out" by simultaneous users, your view name based on the end-users id/name, and eventually some date/time information ...
store the "sql" as a view, and run the bcp with the view name, and drop the view afterwards.
=> this will also protect you more against sql injection attempts :)
to ensure you don't get "bombed out" by simultaneous users, your view name based on the end-users id/name, and eventually some date/time information ...
ASKER
Thank you.
you can use VARCHAR(MAX).
Text is deprecated.