question

rajeshyadav-0048 avatar image
0 Votes"
rajeshyadav-0048 asked MelissaMa-msft commented

the sting in tsql is getting truncated is there any connection level setting or stored proc level setting for it.

hi,

  declare @strquery varchar(max) ='there i have very lengy query';

exec (@strquery) --here when it execute it cuts the query. so what i did is seprated /cut the query in two blocks like following.

declare @strquery varchar(max) ='8000 chars';
set @strquery +='next 8000 cahrs';
exec (@strquery) -- this runs

Q1) I would like to know can i set some thing at the top of stored procedure or in connectionstring , so that i do not have to bother while making SP or after wards.

yours sincerly

sql-server-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What version of SQL Server are you using? Please post the results of SELECT @@VERSION.

0 Votes 0 ·

Hi @rajeshyadav-0048,

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

That's the common string literal limitation and there is not setting to change the behaviour.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @rajeshyadav-0048,

Welcome to Microsoft Q&A!

I would like to know can i set some thing at the top of stored procedure or in connectionstring , so that i do not have to bother while making SP or after wards.

Actually there is no such setting. You have to cut the query in two blocks as you already did.

Besides, you could use nvarchar(max), instead of varchar(4000) or varchar(max). This could store up to 2 GB of text. Please refer more details in this forum.

And use sp_executesql if necessary.

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

I think I know what is going on. This is something I discuss in the section A Trap with Long Strings in my article on Dynamic SQL.

I refer you this section, rather than going into details here, so that you can look at the example and see if fits in with what you are doing.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.