Query to linked server failed if columns have special characters in their names - by Alex Volok

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
0
Sign in
to vote
ID 468946 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 6/22/2009 7:39:02 AM
Access Restriction Public

Description

In case scenario all servers have version

Microsoft SQL Server 2008 (SP1) - 10.0.2714.0 (Intel X86)   May 14 2009 16:02:37   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2) 

Server1:
use master
create table test ([col1.] int)

Server2:
select * from Server1.master.dbo.test

Cannot process the object ""master"."dbo"."test"". The OLE DB provider "SQLNCLI10" for linked server "Server1" indicates that either the object has no columns or the current user does not have permissions on that object.

To fix this issue system view sys.spt_columns_view must be altered: 

has_perms_by_name(quotename(schema_name(o.schema_id)) + '.' + quotename(o.name),
                                                                    'OBJECT',
                                                                    'UPDATE',
                                                                    c.name,
                                                                    'COLUMN') = 1

must be replaced with:

has_perms_by_name(quotename(schema_name(o.schema_id)) + '.' + quotename(o.name),
                                                                    'OBJECT',
                                                                    'UPDATE',
                                                                    quotename(c.name),
                                                                    'COLUMN') = 1
Sign in to post a comment.
Posted by ap2834342 on 2/22/2010 at 5:05 AM
I have a similar problem and wonder if the root cause is the same. On SQL Server 2008 (SP1), when a column name contains the dot character, it is not included in the schema rowset of the respective table when requested via both the SQLOLEDB and SQLNCLI10 providers using ADO's OpenSchema(adSchemaColumns) method. The SQL Server Profiler shows that a command similar to the following is issued on the server:

[testdb].[sys].sp_columns_100_rowset N'tab',N'dbo',NULL

When executed, this command returns rows for all columns except those which contain the dot character in their name. On SQL Server 2005, it works as expected.

Is this the same issue or should I submit a new ticket?

Ales

-------
TEST CASE:

create database testdb
go
use testdb
create table tab ([col1] int, [col2.] int, [col3] int)
exec [testdb].[sys].sp_columns_100_rowset N'tab',N'dbo',NULL
go
use master
drop database testdb
Posted by Microsoft on 7/8/2009 at 10:49 AM
Hi Alexandr,

Thank-you for your feedback regarding this problem you are seeing. We have identified the root cause and will be fixing this issue in an future release of the product.

Sincerely,
Jimmy Wu
Microsoft SQL Server
Posted by Microsoft on 6/24/2009 at 9:26 AM
Thanks for reporting this issue. We will triage this issue for upcoming releases.