5

Supporting an existing VB6 app with a SQL Server 2005 back end and we're migrating our database server to SQL Server 2012. Created a backup of the production database and restored it into a dev database on both servers. Other than the server version the databases are identical and the Compatibility Level is still set to 90 (SQL Server 2005).

When I run a query on SQL Server 2005, similar to the example below, a string is returned with extra spaces for any blank fields. If I run the same query on SQL Server 2012 there are no extra spaces being returned. As far as I can tell it seems to be an issue with doing a UNION ALL against a table with no records and a char data type but I wanted to ask anyway.

For the below example you'd need to create a table with one NOT NULL, Char(2) column named Unit6. Leave the table empty with no records once created.

CREATE TABLE [dbo].[tblTest](
    [Unit6] [char](2) NOT NULL
) ON [PRIMARY]

When I run the following query I receive two different results depending on the server version:

SELECT 
   Unit5, Unit6,
   LEN(Unit6) as Len_Unit6, 
   '--|' + Unit6 + '|--' as Spanner
FROM
   (SELECT      
       '13' as Unit5,
       '' as Unit6
    UNION ALL
    SELECT      
        '',
        Unit6 
    FROM tblTest) as MyData

Results on SQL Server 2005

SQL 2005 Results

Results on SQL Server 2012

SQL 2012 Results

Hopefully you can see the Spanner column shows two spaces when run on 2005 vs zero spaces when run on 2012.

This is a major issue as the app runs queries similar to the above example and then later uses string functions to cut up a single string for database inserts and updates. Without the spaces which are being included on SQL Server 2005 the string functions are carving up the wrong pieces which causes any inserts or updates to fail.

Any information would be most helpful. Thank you!

Server Versions

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
Nov 24 2008 13:01:59 
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) 
Oct 25 2013 19:04:40 
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
Improve this question
10
  • Try to put to spaces at that '' as Unit6. Union ill try to return a varchar(n) if ehe finds a string with n chars
    – jean
    Commented Feb 4, 2014 at 18:31
  • @jean that doesn't reproduce the problem. Making that change would return two spaces in the spanner without adding a UNION.
    – GunnerFan420
    Commented Feb 4, 2014 at 18:44
  • Is there a collation difference between the two servers? There is likely an implicit conversion occurring which casts your first select '' to a char(2) based on the schema of the second select which references tblTest
    – nathan_jr
    Commented Feb 4, 2014 at 18:52
  • @NathanSkerl Checked it out and both servers have a Server Collation of SQL_Latin1_General_CP1_CI_AS but thanks for the thought.
    – GunnerFan420
    Commented Feb 4, 2014 at 19:11
  • It could also be a collation difference at the column level, but in any case I would look for the CONVERT_IMPLICIT operation in the execution plan on the offending server to rule it out
    – nathan_jr
    Commented Feb 4, 2014 at 19:20

1 Answer 1

Reset to default
1

Check the ANSI_PADDING setting:

Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data.

http://msdn.microsoft.com/en-us/library/ms187403.aspx

I just tried on both SQL Server 2005 and 2012 and I don't get those results:

Unit5 Unit6 Len_Unit6   Spanner
----- ----- ----------- --------
13          0           --||--

(1 row(s) affected)


Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) 
    Dec 10 2010 10:56:29 
    Copyright (c) 1988-2005 Microsoft Corporation
    Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

(1 row(s) affected)


Unit5 Unit6 Len_Unit6   Spanner
----- ----- ----------- --------
13          0           --||--

(1 row(s) affected)


Microsoft SQL Server 2012 (SP1) - 11.0.3349.0 (X64) 
    Mar  8 2013 17:33:56 
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

(1 row(s) affected)
Improve this answer
11
  • I'm using two exact copies of the database so the ANSI Padding Status for all columns in all tables are identical.
    – GunnerFan420
    Commented Feb 4, 2014 at 18:41
  • Actually it doesn't matter when the column is NOT NULL anyway, it should always be padded to the length of the column.
    – BlackICE
    Commented Feb 4, 2014 at 18:47
  • I'm not sure how should always be but I'd expect it would be consistent between server versions. No idea why you didn't see the same results.
    – GunnerFan420
    Commented Feb 4, 2014 at 19:00
  • idk either, I haven't found a setting yet that gives me the results you're seeing.
    – BlackICE
    Commented Feb 4, 2014 at 19:01
  • what's really weird is that even your query results say len(unit6) is 0, but something is still inside the spanner
    – BlackICE
    Commented Feb 4, 2014 at 19:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.