linked server problem four part names dont work SQL2000 (64bit)

Last Post 07 Sep 2004 10:16 AM by hrosch. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Z3
New Member
New Member

--
12 Dec 2003 10:12 AM
Hello Everyone,
I am having a rather strange problem. I have one 64 Bit SQL Server 2000 (named nibbler2) setup that can not use four part names with linked servers or use the opendatasource() function. I get the error listed below. Now the weird part is the openquery() & OPENROWSET() functions both work perfectly well. However this machine is going to be used to replace a 32 bit sql server 2000 machine (named nibbler) and we have to many qrys to rewrite in the short term. All the machines have the latest patches nibbler is sp3a and nibbler2 has the 1 and only patch available for 64bit sql server (no service packs are yet released for 64 bit as far as I can tell).

In a side note I already tried the instcat.sql script and it made no difference.
Plus I can browse to the linked server and see all the tables plus openquery works so as far as I can tell the server is linked correctly.
Nibbler(32 bit) has no problems with 4 part names.


For example the following qry was run on nibbler2 with nibbler as a linked server

select *
from
nibbler.seveneleven.dbo.pip_composed_of_bom

It returns Returns

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Unspecified error]
[OLE/DB provider returned message: The stored procedure required to complete this operation could not be found on the server (they were supplied with the ODBC setup disk for the SQL Server driver). Please contact your system administrator.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBSchemaRowset::GetRowset returned 0x80004005: ].

Z3
New Member
New Member

--
12 Dec 2003 10:51 AM
Trying to avoid the 250 dollar call if I can. Their forums are a joke.
bsalmon
New Member
New Member

--
12 Jul 2004 04:56 AM
I encountered this same type of error. I dug into it and ran profiler while trying to run a query from our 64bit server to a 32bit server. The problem is that behind the scenes, the query executes a system proc - sp_tables_info_rowset_64 - which doesn't exist in 32bit SQL. So, what I did was copy the proc from the 64bit server to the 32bit server and now things work. I don't know what side effects this could cause. I don't believe it will break anything, but I suppose it is possible that the remote queries could encounter a problem in certain scenarios.

Anyway, here's the SQL I ran in the master database on the 32 bit server.

create procedure sp_tables_info_rowset_64
(
@table_name sysname,
@table_schema sysname = null,
@table_type nvarchar(255) = null
)
as
select *
from (select TABLE_CATALOG = db_name(),
TABLE_SCHEMA = user_name(o.uid),
TABLE_NAME = o.name,
TABLE_TYPE = convert(nvarchar(30),
case o.type
when 'U' then
case when ObjectProperty(o.id, 'IsMSShipped') = 0
then N'TABLE' else N'SYSTEM TABLE' end
when 'S' then N'SYSTEM TABLE'
when 'V' then
case when ObjectProperty(o.id, 'IsMSShipped') = 0
then N'VIEW' else N'SYSTEM VIEW' end
end),
TABLE_GUID = convert(uniqueidentifier, null),
BOOKMARKS = convert(bit, 1),
BOOKMARK_TYPE = convert(int, 1 /*DBPROPVAL_BMK_NUMERIC*/),
BOOKMARK_DATATYPE = convert(smallint, 21 /*DBTYPE_UI8 */),
BOOKMARK_MAXIMUM_LENGTH = convert(int, 8),
BOOKMARK_INFORMATION = convert(int, 0),
TABLE_VERSION = convert(bigint, ObjectPropertyEx(o.id, 'objectversion')),
CARDINALITY = x.rows,
DESCRIPTION = convert(nvarchar(1), null),
TABLE_PROPID = convert(int, null)

from sysobjects o left join sysindexes x on o.id = x.id and x.indid in (0,1)
where o.name = @table_name
and o.type in ('U','V','S')
and permissions(o.id) <> 0) as t

where (@table_schema is null or @table_schema = TABLE_SCHEMA)
and (@table_type is null or @table_type = TABLE_TYPE)
order by 4, 2, 3
GO
GRANT EXECUTE ON sp_tables_info_rowset_64 TO PUBLIC GO create procedure sp_tables_info_rowset_64;2
(
@table_schema sysname = null,
@table_type nvarchar(255) = null
)
as
select *
from (select TABLE_CATALOG = db_name(),
TABLE_SCHEMA = user_name(o.uid),
TABLE_NAME = o.name,
TABLE_TYPE = convert(nvarchar(30),
case o.type
when 'U' then
case when ObjectProperty(o.id, 'IsMSShipped') = 0
then N'TABLE' else N'SYSTEM TABLE' end
when 'S' then N'SYSTEM TABLE'
when 'V' then
case when ObjectProperty(o.id, 'IsMSShipped') = 0
then N'VIEW' else N'SYSTEM VIEW' end
end),
TABLE_GUID = convert(uniqueidentifier, null),
BOOKMARKS = convert(bit, 1),
BOOKMARK_TYPE = convert(int, 1 /*DBPROPVAL_BMK_NUMERIC*/),
BOOKMARK_DATATYPE = convert(smallint, 21 /*DBTYPE_UI8 */),
BOOKMARK_MAXIMUM_LENGTH = convert(int, 8),
BOOKMARK_INFORMATION = convert(int, 0),
TABLE_VERSION = convert(bigint, ObjectPropertyEx(o.id, 'objectversion')),
CARDINALITY = x.rows,
DESCRIPTION = convert(nvarchar(1), null),
TABLE_PROPID = convert(int, null)

from sysobjects o left join sysindexes x on o.id = x.id and x.indid in (0,1)
where o.type in ('U','V','S')
and permissions(o.id) <> 0) as t

where (@table_schema is null or @table_schema = TABLE_SCHEMA)
and (@table_type is null or @table_type = TABLE_TYPE)
order by 4, 2, 3
GO
hrosch
New Member
New Member

--
07 Sep 2004 10:16 AM
You should manualy execute the script “instcat.sql” located in the "..\Program Files\Microsoft SQL Server\MSSQL\Install\" folder. Do this for EVERY computer involved (connected) in the Linked Server scenario. Among other things, the mentioned "sp_tables_info_rowset_64" is found in that script.



You are not authorized to post a reply.

Acceptable Use Policy