passing a binary (encrypted ssn) to stored procedure

[DarwinIT]

I just changed an application which was using hard coded t-sql within it to write to a table, including a binary field. I swapped that out for a stored procedure. Now I've just discovered the SSN field is all zeros except for a one at the beginning. How do I get that value to correctly get stored????
The data type in the sproc is specified as binary. I just changed it to binary(154) since that is the size of the field in the table - and that did not help. I now have this code in production and need to fix it fast. I don't want to revert to the hard coded string because that had it's own problem that the sproc fixed.

 

[markros]

What application you're using to send data to SQL Server and how the parameters are defined there? Also, may be you should use varbinary(154) instead of binary ?

Try running SQL Server profiler and capture the command send to SQL Server. Then try executing the same command from SQL Server. The error will be more apparent this way.

PluralSight Learning Library

 

[DarwinIT]

Oops. We're using a Dot Net Dataset. I just realized that although I changed the sproc to indicate there were 154 bytes, I had not changed the Dataset configuration. It was set to a size of 1 byte. I wish it would have thrown a truncation error instead of just passing one byte through. Anyway, I have changed that to 154 and I'm working fine. Thanks for your input!!!
C# by the way.