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
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
Results on SQL Server 2012
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!
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: )