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

rajesh yadav 171 Reputation points
2021-09-21T05:47:16.393+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,808 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,558 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.8K Reputation points MVP
    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.

    1 person found this answer helpful.
    0 comments
  2. Olaf Helper 41,001 Reputation points
    2021-09-21T06:04:08.593+00:00

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

    0 comments
  3. 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.

    0 comments