1

Yesterday, I noticed something odd when returning a varchar(100) output parameter from my stored procedure in my asp.net application. It appears that the returned value is now including the extra white spaces to return a full 100 characters. This behavior is new. The only thing I have changed recently is migrating the project from VS 2015 to 2017. I am using System.Data.SQLClient to connect to the database. Select statements for varchar columns return just the values in the columns and no extra white spaces. Ansi_padding is off for the database and is not set any where in the code.

set @Message = 'Project Updated!' --where @Message is varchar(100)

This will return to the client.

_UpdateStatus = oData.outputParams.Item("@Message") 
_UpdateStatus = "Project Updated!                                                                                    "

Any ideas as to what is happening?

Kevin Hicks
  • 21
  • 7

1 Answers1

0

Lazy solution:

_UpdateStatus = ((string)oData.outputParams.Item("@Message")).Trim()
mortb
  • 9,361
  • 3
  • 26
  • 44
  • To debug you can add the value of the property ANSI_PADDING in the stored procedure to the output to see what value it has when executing. For example like this: `set @Message = 'Project Updated ANSI_PADDING value used was=' + SESSIONPROPERTY('ANSI_PADDING')` I know that the .NET SqlClient sometimes sends its own default values for some of these properties (ANSI_NULLS, ANSI_PADDING, ARITHABORT etc) when setting up a connection – mortb Oct 12 '17 at 10:49
  • I will try this later. I was curious if .net was changing something. The database ansi padding is set to off. – Kevin Hicks Oct 12 '17 at 18:19
  • "Project Updated ANSI_PADDING value used was=1 " So does that mean it is ON? – Kevin Hicks Oct 14 '17 at 15:43
  • Yes 1 means that `ANSI_PADDING` is `ON`. Try generating a script for the stored procedure by right clicking it in SQL Server Management Studio it will output the `ANSI_PADDING` that the stored procedure was generated with, it is a value that may be set individually per stored procedure and override the database default (which might "not" be a source of confusion ;) ) . – mortb Oct 16 '17 at 07:50
  • I tried setting ansi_padding to off, but it now gives me the following error: [link](https://stackoverflow.com/questions/9235527/incorrect-set-options-error-when-building-database-project) – Kevin Hicks Oct 19 '17 at 14:50
  • A column may be created with or without `ANSI_PADDING` it will affect how the column is stored. https://stackoverflow.com/questions/28668954/set-ansi-padding-on-a-specific-column-ansi-padding-on-and-off-in-different-colu – mortb Oct 20 '17 at 09:51
  • In what context are you trying to `SET ANSI_PADDING OFF`? The stored procedure? – mortb Oct 20 '17 at 09:52