Problem Truncated fields when passing stings to SQL

[aidanh]

Havinga a problem when I execute an ADO command from vb which writes to my SQL Database. I execute a stored procedure which updates a field. I pass from VB a string through an ADO command however append paramter converts it to Null-terminated Unicode character string. The stored procedure recognises it as a SQL-variant. It works but the String is always truncated at 30 characters. The table column has data type nvarchar and length 100. Can anyone give me any ideas?? Thnks in advance

 

[SQLBill]

Why do you think the string is truncated at 30 characters? How are you retrieving the data? Is it via Query Analyzer?

If it's via QA, go to TOOLS>OPTIONS and select the Results tab. What is "Maximum characters per column" set for?

-SQLBill

 

[SQLBill]

Also, you can check how many characters are stored by running this:

SELECT MAX(LEN(yourcolumnname))
FROM yourtable

That will return the number of characters in the largest entry for yourcolumnname.

Or run:
SELECT DISTINCT LEN(yourcolumnname)
FROM yourtable

Are there any rows over 30 characters? If so, the data isn't truncated...it's just the way you are displaying it.

-SQLBill

 

[SQLSister]

I don't know if this will help your problem as we don't use that methodology to execute stored procedures. But our web programmers always are careful to specifically type their variables and to use types which are exact matches for the data type specified inthe stored procedure for the parameters. So that is the first thing I would check. Since you say that SQl is recognizing it as type variant, it would be my guess that the programmers are not using strict typing of variables.