Facing problem with executing large-value query variable
-
Hi,
I am dynamically constructing the query and putting it in a variable called @Qry and finally executing it using the Execute in the stored procedure.
Now the problem is , if the length of the query variable @Qry(VarChar) exceeds 5000 I am getting the error 'string or binary data would be truncated'.
I have tried with an option ie.. splitting up the query into pieces like @Qry.,,@Qry2... and finally combining it in the Execute statement as Exec(@Qry1+@Qry2+@Qry3)... but then too I am gettting the same error.
My requirement is to dynamically construct the query and execute it in the stored procedure since the column names are dynamic. So there is no alternate other than dyanamic queries.
Can you please help me in this regard?
Regards
Daniel
-
Can you post the query that is erroring out?
I suspect a cast (explicit or implicit) that would truncate the data is throwing the error, and not the dynamic sql itself!
-
>> I am getting the error 'string or binary data would be truncated'. <<
That's an error I've gotten when I had data exceeding the limit I had defined for a parameter I was passing.
The others here are much more experienced than I, but that's just my own experience with that particular error.
Lee...
-
Post your code. Otherwise, all we can do is guess. It saves time for everyone involved if you post the code.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
The alternative is to write a query instead an "on the fly" ad hoc mess. So, how do they do it for click and drag ETL like Business Objects Data Integrator or click and drag reporting tools like Business Objects and Crystal reports? Write a static query for every single permuation? I agree that those are all a mess
but how do they do it without the "on the fly ad hoc mess"?--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) -
Change @Qry from VARCHAR(5000) to VARCHAR(8000).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
That won't do you any good as Exec() will only read the first 4000 characters of a variable.
One option is to use NText and sp_executesql.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Error 'string or binary data would be truncated' has nothing to do with EXEC(@String)
It happens when you are trying to insert character value with length > 50 into varchar(50) column.
Check the data and datatypes.
_____________
Code for TallyGenerator -
Like I said before, post your code!!!
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
--->The alternative is to write a query instead an "on the fly" ad hoc mess.<---
Whilst dynamic SQL is untidy, it is often necessary in the real world. Think of report writing software where the user can define the particular columns and the order they appear in.
Also, sometimes the tables or views you select may be dependant on a data item; for example in our database each client will follow one of a possible 10000 benchmarks, each of which has its own view, specified by some data items on one of the tables. We could code 10000 different versions of our sps, but it is far easier to have one version where the name of the view to select data from is dynamically constructed.
I don't believe that the ability to create dynamic SQL has been included in different database languages just to encourage sloppy programming technique; rather it exists because not all real-world situations can be covered by a beautifully structured design.
-
Actually, you can do that without dynamic SQL or having 10,000 sp's. All you need is a view that performs a Union All of the various views and include a column that indicates which set of data goes with which benchmark and then merely select from the view where the benchmark = the value passed in.
Though it would work much better if the view wasn't refencing 10,000 views but rather was refencing the underlying tables. And with 10,000 views, it would probably perform better using dynamic SQL. So a partitioned view won't be better in every case, like this one, but often times it would be better.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply