8000 Limit of varchar.

Question

5.00/5 (1 vote)

See more:

Hello, Folks,
I have one Query,
And the Length of the query Exeeds the limit of varchar(max) which is 8000
and i am not able to put it into EXEC('') Statement,
as EXEC Statement itself supports only length of 8000 max only.
Is there any way to do it ?

Posted 8-Sep-10 23:56pm

Hiren solanki

Add a Solution

Comments

Ankur^\m/ 9-Sep-10 6:40am

[moved from answer]
Hiren Solanki wrote:
thankyou guyz for your answers,
I will have to think another way, i think it is not possible with that,

I m confusion to whom give the best answer from Abhinav,sandeep,Abhijit,

Thanks.

Ankur^\m/ 9-Sep-10 6:41am

Click 'Add Answer' only if you are adding an answer to the question. For all other things, there is a feature called 'Comment'!

Hiren solanki 9-Sep-10 6:43am

Sorry,
Will surely follow

Abhijit Jana 9-Sep-10 7:07am

I marked it as Good question ! As I founds many alternative new answers !! Thanks !

Hiren solanki 9-Sep-10 7:09am

Thanks abhijit,

7 solutions

Solution 2

I really dont think having a query that is 8000 characters is a good idea.
It is just too long.

Think of the guy who has to maintain this query (or make changes to it later).

Posted 9-Sep-10 0:13am

Abhinav S

Comments

Hiren solanki 9-Sep-10 6:16am

i can understand that abhijit,
it is just a simple Query But Just having to many attributes to compare,
and sub-Query,
As you know microsft dynamics crm Tables have 150 columns, so i can not truncate the querym Should be anyway.

Sandeep Mewara 9-Sep-10 6:21am

:O Abhinav, when did you changed your name from Abhinav to Abhijit?
OR
Hiren, are you too obsessed for Abhijit? :P

Abhijit Jana 9-Sep-10 6:23am

I guess that should be "Abhinav" !! Not Abhijit . Even In my office, people sometimes confused with my name and one of my team member name, whose name is Abhinav too :)

Hiren solanki 9-Sep-10 6:26am

sorry guys, by mistake,

Abhinav S 9-Sep-10 6:40am

Funny someone called me Abhishek at office today and now this. Wow.
I thought no one knew me but this is kind of getting too much :).

Sandesh M Patil 9-Sep-10 7:15am

someone called me sandeep today in office ;) what a coincidence

Sandeep not referring to you, one of colleague name is sandeep patel :)

Solution 3

Can you convert some or all of it to a stored procedure?

Posted 9-Sep-10 0:20am

Chris Maunder772.7K

Solution 8

Put the query into a stored proc, and pass the necessary parameters into that stored proc.

Posted 9-Sep-10 2:09am

#realJSOP

Comments

Hiren solanki 9-Sep-10 8:10am

Thanks John, that is also a way of doing.

Solution 7

Always avoid dynamic queries. It should be the last resort. It will hamper the performance considerably.

Also, 8000 lines is way too much. Break it into peices. Try and get rid of dynamic queris.

Posted 9-Sep-10 2:03am

dan!sh

Comments

Hiren solanki 9-Sep-10 8:05am

thanks d@nish, I can understand.
But it was Requirement for SSRS.

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhijit Jana · Accepted Answer · 2010-09-09T00:53:00

I am not agree with the statement that you have mentioned.
"

SQL

though i will break query but at the end when i will put it into EXEC('only supports 8000 chars only').

I can execute the query which having chars more than 8000

SQL

declare @cmd varchar(max)
set @cmd = 'print /*' + replicate ('-', 7990);
set @cmd = @cmd + replicate ('-', 7990) + '*/ getdate()';
print   datalength (@cmd)
exec (@cmd)
print  datalength (@cmd)

Read the complete thread in MSDN forum !

C++

Executing Dynamic SQL larger than 8000 characters

[^]

Hope this helps you. :) :thumbsup:

Sandeep Mewara · Accepted Answer · 2010-09-09T00:00:00

Solution 1

Break the query!
Use SP/Functions/Views...

Well, actually if you have a query of length more than 8000, chances are it's not well formed. You need have a proper DB designed and then various ways to get data out of it like having Views in place.

Posted 9-Sep-10 0:00am

Sandeep Mewara

Comments

Hiren solanki 9-Sep-10 6:05am

you are right at your end but i am facing problem,
1. Break the query : though i will break query but at the end when i will put it into EXEC('only supports 8000 chars only').
2.Proper Formatting : I m just adding two columns in Query designed by Microsoft so i think there may not be any formation problem because microsoft have created it, ( FYI i am editing query of microsoft dynamics crm reports)

Sandesh M Patil 9-Sep-10 6:08am

Good answer

Abhijit Jana · Accepted Answer · 2010-09-09T00:21:00

Solution 4

Try to break the query string and append in EXEC Statement.

Like

EXEC (@SQLQuery1 + @SQLQuery2 + @SQLQueryry3)

Ref: Read the thread
http://www.sqlservercentral.com/Forums/Topic589708-9-1.aspx#bm589762[^]

Posted 9-Sep-10 0:21am

Abhijit Jana

Comments

Hiren solanki 9-Sep-10 6:25am

inside EXEC you can not write more then 8000 chars,
so if you break the query or keep single, doesnt matter

8000 Limit of varchar.

7 solutions

Solution 6

Solution 1

Solution 2

Solution 3

Solution 4

Solution 8

Solution 7

Add your solution here

Preview 0

8000 Limit of varchar.

7 solutions

Solution 6

Solution 1

Solution 2

Solution 3

Solution 4

Solution 8

Solution 7

Add your solution here

Preview 0

Existing Members

...or Join us