Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
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 '11 at 13:15
add comment

migrated from serverfault.com Oct 6 '11 at 11:47

This question came from our site for professional system and network administrators.

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.