That's the common string literal limitation and there is not setting to change the behaviour.
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
3 answers
Sort by: Oldest
-
-
MelissaMa-MSFT 24,176 Reputation points
2021-09-21T06:43:45.877+00:00 Hi @rajesh yadav ,
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. -
Erland Sommarskog 102.3K Reputation points
2021-09-21T21:54:27.08+00:00 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.