Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Fixed Help for as Fixed


1
0
Sign in
to vote
Type: Bug
ID: 468946
Opened: 6/22/2009 7:39:02 AM
Access Restriction: Public
1
Workaround(s)
view
0
User(s) can reproduce this bug

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
Details
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.
Sign in to post a workaround.
Posted by Alex Volok on 6/22/2009 at 7:46 AM
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