Avatar of Mark Klein
Mark Klein
Flag for United States of America asked on

output from Sql Server is being truncated

I have a stored proc that queries a table. One field is nvarchar, of length 400.  The proc is below. Despite my declarations the field is always truncated to 50 characters, which I suspect is a default value.  where is this happening.  How can I stop it?
ALTER PROCEDURE dbo.uspGetMailFields @msgName NVARCHAR (50), @msgbody nvarchar (400) OUTPUT, @msgSubject nvarchar (50) OUTPUT, @Recipient nvarchar (50) OUTPUT
AS
BEGIN
       SELECT    @msgSubject=msgSubject, @msgbody=msgBody, @Recipient=recipient
       FROM eMailMessages
       WHERE msgName = @msgName
END

GO

Open in new window


The parameters in the code-behind are
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@msgName", SqlDbType.NVarChar, 50)).Value = msgName
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@msgSubject", SqlDbType.NVarChar, 400))
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@msgbody", SqlDbType.NVarChar, 50))
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@recipient", SqlDbType.NVarChar, 50))

        cmd.Parameters("@msgSubject").Direction = ParameterDirection.Output
        cmd.Parameters("@msgBody").Direction = ParameterDirection.Output
        cmd.Parameters("@recipient").Direction = ParameterDirection.Output

        'Execute the Stored Procedure to collect the email message body,subject line, and recipient type 
        con.Open()
        cmd.ExecuteNonQuery()

        Dim subject As String = CType(cmd.Parameters("@msgSubject").Value, String)
        Dim body As String = CType(cmd.Parameters("@msgBody").Value, String)
        Dim recipientType As String = CType(cmd.Parameters("@recipient").Value, String)

Open in new window


Is it the Dim body line?
ASP.NETMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
Mark Klein

8/22/2022 - Mon
QPR

what is the length of msgBody in your emailMessages table?
Where is it being truncated? In your application or when you are running the stored proc in a query window?
ASKER CERTIFIED SOLUTION
QPR

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Anthony Perkins

Yes, it should be (no points pleasem QPR has already given you the answer):
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@msgbody", SqlDbType.NVarChar, 400))
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@msgSubject", SqlDbType.NVarChar, 50))
mimran18

cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@msgName", SqlDbType.NVarChar, 50)).Value = msgName
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@msgbody", SqlDbType.NVarChar, 50))
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@msgSubject", SqlDbType.NVarChar, 400))
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@recipient", SqlDbType.NVarChar, 50))

cmd.Parameters("@msgBody").Direction = ParameterDirection.Output        
cmd.Parameters("@msgSubject").Direction = ParameterDirection.Output
cmd.Parameters("@recipient").Direction = ParameterDirection.Output

        --Execute the Stored Procedure to collect the email message body,subject line, and recipient type
        con.Open()
        cmd.ExecuteNonQuery()

        Dim body As String = CType(cmd.Parameters("@msgBody").Value, String)
        Dim subject As String = CType(cmd.Parameters("@msgSubject").Value, String)
        Dim recipientType As String = CType(cmd.Parameters("@recipient").Value, Strin
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Mark Klein

ASKER
appreciate the fresh eyes. Stared at the code too long and missed the obvious.Thanks all