Linking SQL 64-bit to SQL 32-bit server
-
Hi there,
We're implementing a new 64-bit server with Win 2003, SQL 2000 64-bit & SQL SP 4. We need to link to a 32-bit SQL server in order to read data for testing purposes. I made sure that MDAC 2.8 is installed on both servers.
Linked servers have been setup correctly, but when I reference a database on the linked server, I get the following 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: ].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error."
Am I missing something & can it be done at all??
Thanks in advance!!
T
-
Try use Profiler to trace which stored procedure is not found.
-
I don't think it's a stored procedure that SQL is looking for...
Even if you do a simple query like
"Select top 1 * from server.Northwind.dbo.table"
You get the exact same error...
-
I have no issue to link my 64 bits server to 32 bits one, try to link to another 32 bits server to see you still have same errors.
-
Nope!! Tried to run the same query on 4 different servers, still got the same error...
In Enterprise Manager the linked servers looks fine & you can view the tables & views on the remote server, but in Query Analyser, I get the previous error.
The only other thing I can think of is SP1 for Win2003 64-bit??
-
When you issued statment to query 32 bit linked server from 64 bits server, the query is looking for stored procedure sp_tables_info_rowset_64 in 32 bit server and it doesn't exist.
What I have done is script the procedure from 64 bit and create it in 32 bit server.
/* Procedure for 8.0 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
-
The fix is to install the file [Instcat.sql] from the SQL64bit CD on the 32bit server.
This file is located in the folder: Setup\IA64\Install (remember IA64 can different depending on your 64bit hardware)
-
I have de same problem, but how can I run the instcat.sql script?
If I try, from query analyzer: "execute ...paht\instcat.sql" I get the following error:
Could not find stored procedure 'C:\Program Files\Microsoft SQL Server\MSSQL\Install\instcat.sql'.
but it exist, and the path is correct.
And I can't copy the code into instcat.sql and paste in query analyzer because I get a lot of errors ("Incorrect syntax near '<'")
Please, somebody can help me?
Thanks in advance.
-
Am,
You have to open the file first in QA, and then press F5. (File|Open|Find the file|Press Open)
You can't execute the script using the DOS path - Which your post seems to indicate you’re trying.
-
When I load this code (instcat.sql) in SQLServer's 2005 Server Management Studio (I'm new to 2005, but I think this is the equivalent of Query Analyzer) and parse it against a SQLServer2000 connection, I get a number of syntax errors like the following:
Msg 170, Level 15, State 31, Procedure sp_catalogs_rowset, Line 9
Line 9: Incorrect syntax near '<'.
This apparently points to this bit of code:
create procedure sp_catalogs_rowset;5
(
@server_name sysname,
@catalog_name sysname = NULL
 
as
select CATALOG_NAME,
DESCRIPTION
from master.dbo.SYSREMOTE_CATALOGS < @server_name, @catalog_name >
order by 1
go
I guess I am unclear about how to run this, but according to this thread (and other documentation I have read), it is required in order to make a workable Linked Server from SQLServer 2005 to SQLServer 2000.
Does the code need to be edited for each server? If that is the case, then I am not sure what the @catalog_name would be.
-
The part must be changed of course to your server and catalog_name. Then run the script again. I think you need to delete the too.
Edit...
I stand corrected by Hobo-Long. Of course you do not have to change that part... Me sleeping!
And of course you do not run this script on SQL Server 2005...
Good mentioned Hobo-Long...
-
The method I described was for SQL Server 2000 only. You DON'T load 2000 scripts into a SQL Server 2005.
Assuming you’re installing the script(s) on a SQL Server 2000 based server:
1. You shouldn’t have to modify the code at all – just execute it – if it’s prompting you to change something – something is not right.
2. If you’re still having problems – find the proc [sp_tables_info_rowset_64] on the 64 bit CD and execute it. (Note it will not be labeled as a system proc when you manually execute it this way.)
-
my understanding of this that that 2000 should be on SP3 or SP3
look at the article below it explains what needs to be done
-
You are correct, you will only want to load this on a SQL Server 2000 that is SP3 or higher.
-
You should replace <@server_name> by (@server_name).
Thanks
Parvind singh
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply