Stored Procedure: Truncation of a nvarchar when being added to

Posted on 2004-11-03
Last Modified: 2008-03-06
A quick SQL Server 8.0 question

I have the following test stored procedure :

declare @varSQL   nvarchar(1000)
    SET @varSQL = 'SELECT BranchSortCode, BranchName, Address1, Address2, Address3, Address4, Pcode FROM BranchFile WHERE 1=1'
    SET @varSQL = @varSQL + ' ORDER BY BranchSortCode'

in debug when stopped at execute line

@varSQL = N'SELECT BranchSortCode, BranchName, Address1, Address2, Address3, Address4, Pcode FROM BranchFile WHERE 1=1 ORDER BY BranchSortC'

and has a type of nvarchar(130)

Why is the variable varSQL being truncated when its size is 1000 ????

Question by:Paul__M
    LVL 50

    Accepted Solution

    are you confusing the display amount with the actual length of the contents....

    what does a Length(@varsql) and a datalength(@varsql)


    Author Comment

    having looked again - 130 is the actual length of the string but why does the debug window truncate the variable contents - I'm actually trying to debug a more complex string build and with the truncation cannot see the SQL statement I'm trying to execute.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now