My problem. I'm running SQL Server 2005 in a Development (Dev) and Production (Prod) environment. I created a view that extracts data from a Navision database to SQL using a pass-through query OPENQUERY command (displayed below).
I ran view A as a SELECT script in a new query window. Assuming the field to be varchar data type but not knowing the length, the LEN(VendorNo) returns 6 to 8 chars on Dev and 21 chars on Prod (with the remaining 13, or so, invisible). When using SQL BIDS or exporting to Excel (on Prod), I see a series of small squares or blocks after the string. The ASCII equivalent is CHAR(32).
The service pack versions on both servers were different. The Dev server: Product Version - 9.00.4229.00, Product Level - SP3 and Edition – Standard. The Prod server: Product Version - 9.00.1547.00, Product Level – RTM and Edition is Standard.
On a side note, I included a GROUP BY clause for “view A” in a project and noticed the characters appeared, but by removing GROUP BY also removed the square blocks. Any help or direction would be appreciated. Maybe updates to the SP on Prod? We also have a second test server that shows the same results as the Prod server.
-- My code --
CREATE view nav.vw_Vendor
AS
SELECT * FROM OPENQUERY(NAVISION,
'SELECT Vendor.No_ AS VendorNo
FROM Vendor Vendor')
-- View A
SELECT VendorNo FROM nav.vw_Vendor
