0

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
1
  • When you say 'The ASCII equivalent is CHAR(32)' ... are you saying it's a 32 character long string, or that the strange character is ASCII#32 (which, assuming decimal, 32, is a space)? Based on the 'small squares' description, I'd have assumed it's a character that doesn't exist in the font you're using, but space should exist, and I don't think Excel has the equivalent of BBEdit's 'Show Invisibles' feature.
    – Joe
    Nov 5, 2011 at 13:15

1 Answer 1

2

Some checks

  1. Do both your Dev and Prod link to the same Navision DB?
  2. Compare the rows in sys.servers, especially the is_collation_compatible column
  3. Is the linked server via an ODBC/DSN or is it simple "SQL Server"? If ODBC then are settings the same in the DNS?

I suspect number 2 or 3 if the all your servers link to the same box.

Your Answer

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