linking two datasets: windows

Showing posts with label windows. Show all posts

Wednesday, March 28, 2012

Linkedservers using windows authentication

I have two database servers with two different databases. I have the same
windows login set up on both database servers with read access to the two
different databases. I have linkedserver between the two database server.
When I query using a SQL login I get a result set. When I use the windows
login, I get an error - all else is equal.
Please help to resolve.
Hi
Next time, please post your error message too.
Look at
http://msdn.microsoft.com/library/de...urity_2gmm.asp
about what you need to setup for Impersonation.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"spoons" <spoons@.discussions.microsoft.com> wrote in message
news:38BF845C-A1E8-4264-8479-CE3C6DE2776B@.microsoft.com...
> I have two database servers with two different databases. I have the same
> windows login set up on both database servers with read access to the two
> different databases. I have linkedserver between the two database server.
> When I query using a SQL login I get a result set. When I use the windows
> login, I get an error - all else is equal.
> Please help to resolve.

LinkedServer Failure to connect Oracle 8.1.7

The Error Occured while connecting to oracle from
SQLServer 2000 in WINDOWS 2003, I have tried with MDAC
2.8, MDAC 2.7 and restarted the Server, nothing is
working. We are always getting the Same Error
Error 7399: OLE DB Provider 'MSDAORA' Reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
IDBInitialize::Initialize returned 0x80004005: ].
But i tried with one of the Windows 2000 machine, its
working fine, Please help...
Thanks in advance, NEED URgent HELP, Whole project gets
stopped because of THIS.
Thanks
Paul
I have the same problem.
If you find the solution, please post the message here.
Thanks.
Vlado
"Paul" <Paulr@.fieldpower.com> wrote in message
news:024501c49781$0fa9fb30$a501280a@.phx.gbl...
> The Error Occured while connecting to oracle from
> SQLServer 2000 in WINDOWS 2003, I have tried with MDAC
> 2.8, MDAC 2.7 and restarted the Server, nothing is
> working. We are always getting the Same Error
> Error 7399: OLE DB Provider 'MSDAORA' Reported an error.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> IDBInitialize::Initialize returned 0x80004005: ].
> But i tried with one of the Windows 2000 machine, its
> working fine, Please help...
> Thanks in advance, NEED URgent HELP, Whole project gets
> stopped because of THIS.
> Thanks
> Paul
|||Try turning on trace flag 7300 to get a more detailed error
message. In Query Analyzer, execute the following:
Dbcc traceon (7300,3604)
and then try executing a query against oracle.
-Sue
On Fri, 10 Sep 2004 14:56:44 -0700, "Paul"
<Paulr@.fieldpower.com> wrote:

>The Error Occured while connecting to oracle from
>SQLServer 2000 in WINDOWS 2003, I have tried with MDAC
>2.8, MDAC 2.7 and restarted the Server, nothing is
>working. We are always getting the Same Error
>Error 7399: OLE DB Provider 'MSDAORA' Reported an error.
>OLE DB error trace [OLE/DB Provider 'MSDAORA'
>IDBInitialize::Initialize returned 0x80004005: ].
>But i tried with one of the Windows 2000 machine, its
>working fine, Please help...
>Thanks in advance, NEED URgent HELP, Whole project gets
>stopped because of THIS.
>Thanks
>Paul
|||I am also having the same problem on a Windows 2003 box and can't find a
resolution ANYWHERE!!!!! Linked Oracle Server on Windows 2000 box has been
working great all along. Come on Microsoft - what is the story? I have
seen hundreds and hundred of posts on this issue with no known resolution.
This is a widely used functionality. Anyone out there overcome it??
"Paul" wrote:

> The Error Occured while connecting to oracle from
> SQLServer 2000 in WINDOWS 2003, I have tried with MDAC
> 2.8, MDAC 2.7 and restarted the Server, nothing is
> working. We are always getting the Same Error
> Error 7399: OLE DB Provider 'MSDAORA' Reported an error.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> IDBInitialize::Initialize returned 0x80004005: ].
> But i tried with one of the Windows 2000 machine, its
> working fine, Please help...
> Thanks in advance, NEED URgent HELP, Whole project gets
> stopped because of THIS.
> Thanks
> Paul
>
|||I just opened a crit sit with MS to get the answer to this. Here is the
fix with Windows 2003, SQL 2000, and Oracle 9i:
This is discussed in Q193893 and according to this the registry key "
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOC I] " should have
following
entries.
"OracleXaLib"="oraclient9.dll"
"OracleSqlLib"="orasql9.dll"
"OracleOciLib"="oci.dll"
I hope this helps.
Carrie
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Friday, March 23, 2012

Linked SQL Server and Windows 2003 Firewall/SCWQL

SQL Experts,
1. I have two Windows 2003 Standard Server boxes.
2. Both host MS/SQL Server Standard 2000.
3. Server#2 is linked to Server#1 using SQL/Enterprised Link Server applet.
4. Both servers have Win2k3 Service Pack#1 installed.
5. Both server enable firewall via SCW (Security Config. Wizard). I locked
everything but SQL Server Port (1433).
6. Both use TCP/IP as top (default) network protocol.
7. All Windows XP clients can read/write to and from the two SQL servers
without problem (Windows and SQL Auth. are used).
Problem: Server #2 cannot read data from Server#1 it links to. It said
Sever#1 is not running or access denied. Even if SQL Ports on both boxes are
widely openned. because clients from outside can get to it. Hopwever, if I
disable firewall on Server# 1, then Server# 2 will be able to read data from
#1. I really like the FW in service pack# 1. But if I have to disable it in
order to use "Link Server" (server-to-server) in MS/SQL then this new thing
(FW) in W2k3 is really useless ! Therefore I assume that I am missing
something very important ?
Thanks
JohnI suspect it's the way you have your firewall setup, check the following:
INF: TCP Ports Needed for Communication to SQL Server Through a Firewall
http://support.microsoft.com/kb/287932/
Steve
"WJ" <JohnWebbs@.HotMail.Com> wrote in message
news:udrXXC%23OFHA.2252@.TK2MSFTNGP15.phx.gbl...
> SQL Experts,
> 1. I have two Windows 2003 Standard Server boxes.
> 2. Both host MS/SQL Server Standard 2000.
> 3. Server#2 is linked to Server#1 using SQL/Enterprised Link Server
applet.
> 4. Both servers have Win2k3 Service Pack#1 installed.
> 5. Both server enable firewall via SCW (Security Config. Wizard). I locked
> everything but SQL Server Port (1433).
> 6. Both use TCP/IP as top (default) network protocol.
> 7. All Windows XP clients can read/write to and from the two SQL servers
> without problem (Windows and SQL Auth. are used).
> Problem: Server #2 cannot read data from Server#1 it links to. It said
> Sever#1 is not running or access denied. Even if SQL Ports on both boxes
are
> widely openned. because clients from outside can get to it. Hopwever, if I
> disable firewall on Server# 1, then Server# 2 will be able to read data
from
> #1. I really like the FW in service pack# 1. But if I have to disable it
in
> order to use "Link Server" (server-to-server) in MS/SQL then this new
thing
> (FW) in W2k3 is really useless ! Therefore I assume that I am missing
> something very important ?
> Thanks
> John
>|||Dear John,
Did you solve your linked server problem ? Now , i am suffer the same
problem. Do you got any solution ?
Thanks a lot
From Agnes
*** Sent via Developersdex http://www.codecomments.com ***

Linked SQL Server and Windows 2003 Firewall/SCWQL

SQL Experts,
1. I have two Windows 2003 Standard Server boxes.
2. Both host MS/SQL Server Standard 2000.
3. Server#2 is linked to Server#1 using SQL/Enterprised Link Server applet.
4. Both servers have Win2k3 Service Pack#1 installed.
5. Both server enable firewall via SCW (Security Config. Wizard). I locked
everything but SQL Server Port (1433).
6. Both use TCP/IP as top (default) network protocol.
7. All Windows XP clients can read/write to and from the two SQL servers
without problem (Windows and SQL Auth. are used).
Problem: Server #2 cannot read data from Server#1 it links to. It said
Sever#1 is not running or access denied. Even if SQL Ports on both boxes are
widely openned. because clients from outside can get to it. Hopwever, if I
disable firewall on Server# 1, then Server# 2 will be able to read data from
#1. I really like the FW in service pack# 1. But if I have to disable it in
order to use "Link Server" (server-to-server) in MS/SQL then this new thing
(FW) in W2k3 is really useless ! Therefore I assume that I am missing
something very important ?
Thanks
John
I suspect it's the way you have your firewall setup, check the following:
INF: TCP Ports Needed for Communication to SQL Server Through a Firewall
http://support.microsoft.com/kb/287932/
Steve
"WJ" <JohnWebbs@.HotMail.Com> wrote in message
news:udrXXC%23OFHA.2252@.TK2MSFTNGP15.phx.gbl...
> SQL Experts,
> 1. I have two Windows 2003 Standard Server boxes.
> 2. Both host MS/SQL Server Standard 2000.
> 3. Server#2 is linked to Server#1 using SQL/Enterprised Link Server
applet.
> 4. Both servers have Win2k3 Service Pack#1 installed.
> 5. Both server enable firewall via SCW (Security Config. Wizard). I locked
> everything but SQL Server Port (1433).
> 6. Both use TCP/IP as top (default) network protocol.
> 7. All Windows XP clients can read/write to and from the two SQL servers
> without problem (Windows and SQL Auth. are used).
> Problem: Server #2 cannot read data from Server#1 it links to. It said
> Sever#1 is not running or access denied. Even if SQL Ports on both boxes
are
> widely openned. because clients from outside can get to it. Hopwever, if I
> disable firewall on Server# 1, then Server# 2 will be able to read data
from
> #1. I really like the FW in service pack# 1. But if I have to disable it
in
> order to use "Link Server" (server-to-server) in MS/SQL then this new
thing
> (FW) in W2k3 is really useless ! Therefore I assume that I am missing
> something very important ?
> Thanks
> John
>
|||Dear John,
Did you solve your linked server problem ? Now , i am suffer the same
problem. Do you got any solution ?
Thanks a lot
From Agnes
*** Sent via Developersdex http://www.codecomments.com ***

servers with SQL2000 Windows 2003

Error 17 SQL Server does not exist or access denied
We have two clusters , one with four nodes, the other with two.
We are encountering difficulties on some with communication going one way,
say between SQL instance 1 to the default instance running on the other node.
The strange thing is it works the opposite way - going from default to
instance 1.
Did you try wiping out the servers than
rebuilding them?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

servers problem

Hi !!!
I have two computers with Windows XP, and each these is a SQL SERVER.
I execute sp_addlinkedserver for link both servers, but when I execute a
statement SELECT using my linked server, the following error is raised:
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(sa)'. Reason: Not associated with a trusted SQL
Server connection.
In both servers the authentication mode is Mixed.
can anybody help me? thanks.My guess is that you mapped the login to a SQL Server login on the other machine, and that the other
machine doesn't allow SQL Server logins (it runs in "Windows Only" mode).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"German" <German@.discussions.microsoft.com> wrote in message
news:926B63EF-95E6-4680-BFD4-53AE09DB77D6@.microsoft.com...
> Hi !!!
> I have two computers with Windows XP, and each these is a SQL SERVER.
> I execute sp_addlinkedserver for link both servers, but when I execute a
> statement SELECT using my linked server, the following error is raised:
> Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(sa)'. Reason: Not associated with a trusted SQL
> Server connection.
>
> In both servers the authentication mode is Mixed.
> can anybody help me? thanks.|||Thanks for you answer.
Both Servers are Mixed authentication.
At moment to create linked servers i did it with login map and without but
the result is the same.
"Tibor Karaszi" wrote:
> My guess is that you mapped the login to a SQL Server login on the other machine, and that the other
> machine doesn't allow SQL Server logins (it runs in "Windows Only" mode).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "German" <German@.discussions.microsoft.com> wrote in message
> news:926B63EF-95E6-4680-BFD4-53AE09DB77D6@.microsoft.com...
> > Hi !!!
> >
> > I have two computers with Windows XP, and each these is a SQL SERVER.
> > I execute sp_addlinkedserver for link both servers, but when I execute a
> > statement SELECT using my linked server, the following error is raised:
> >
> > Msg 18452, Level 14, State 1, Line 1
> > Login failed for user '(sa)'. Reason: Not associated with a trusted SQL
> > Server connection.
> >
> >
> > In both servers the authentication mode is Mixed.
> >
> > can anybody help me? thanks.
>
>

Wednesday, March 21, 2012

servers problem

Hi !!!
I have two computers with Windows XP, and each these is a SQL SERVER.
I execute sp_addlinkedserver for link both servers, but when I execute a
statement SELECT using my linked server, the following error is raised:
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(sa)'. Reason: Not associated with a trusted SQL
Server connection.
In both servers the authentication mode is Mixed.
can anybody help me? thanks.
My guess is that you mapped the login to a SQL Server login on the other machine, and that the other
machine doesn't allow SQL Server logins (it runs in "Windows Only" mode).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"German" <German@.discussions.microsoft.com> wrote in message
news:926B63EF-95E6-4680-BFD4-53AE09DB77D6@.microsoft.com...
> Hi !!!
> I have two computers with Windows XP, and each these is a SQL SERVER.
> I execute sp_addlinkedserver for link both servers, but when I execute a
> statement SELECT using my linked server, the following error is raised:
> Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(sa)'. Reason: Not associated with a trusted SQL
> Server connection.
>
> In both servers the authentication mode is Mixed.
> can anybody help me? thanks.
|||Thanks for you answer.
Both Servers are Mixed authentication.
At moment to create linked servers i did it with login map and without but
the result is the same.
"Tibor Karaszi" wrote:

> My guess is that you mapped the login to a SQL Server login on the other machine, and that the other
> machine doesn't allow SQL Server logins (it runs in "Windows Only" mode).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "German" <German@.discussions.microsoft.com> wrote in message
> news:926B63EF-95E6-4680-BFD4-53AE09DB77D6@.microsoft.com...
>
>

servers problem

Hi !!!
I have two computers with Windows XP, and each these is a SQL SERVER.
I execute sp_addlinkedserver for link both servers, but when I execute a
statement SELECT using my linked server, the following error is raised:
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(sa)'. Reason: Not associated with a trusted SQL
Server connection.
In both servers the authentication mode is Mixed.
can anybody help me? thanks.My guess is that you mapped the login to a SQL Server login on the other mac
hine, and that the other
machine doesn't allow SQL Server logins (it runs in "Windows Only" mode).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"German" <German@.discussions.microsoft.com> wrote in message
news:926B63EF-95E6-4680-BFD4-53AE09DB77D6@.microsoft.com...
> Hi !!!
> I have two computers with Windows XP, and each these is a SQL SERVER.
> I execute sp_addlinkedserver for link both servers, but when I execute a
> statement SELECT using my linked server, the following error is raised:
> Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(sa)'. Reason: Not associated with a trusted SQL
> Server connection.
>
> In both servers the authentication mode is Mixed.
> can anybody help me? thanks.|||Thanks for you answer.
Both Servers are Mixed authentication.
At moment to create linked servers i did it with login map and without but
the result is the same.
"Tibor Karaszi" wrote:

> My guess is that you mapped the login to a SQL Server login on the other m
achine, and that the other
> machine doesn't allow SQL Server logins (it runs in "Windows Only" mode).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "German" <German@.discussions.microsoft.com> wrote in message
> news:926B63EF-95E6-4680-BFD4-53AE09DB77D6@.microsoft.com...
>
>

servers Fail

I have a Windows 2000 active/passive cluster. I have configured 6 linked servers using OpenLink Database Proxy Client.

I have had to repeatedly cycle the cluster (stop/restart SQL services) because the linked servers "fail". By failing, I mean that the linked servers stop responding to user queries. The user query simply spins with no activity.

The linked servers are configured in the following manner:
1. I configure a DSN using the OpenLink client
2. I configure SQL Server linked server to point to the DSN

Each node in the cluster has identical DSN entries (same name, same driver, same settings). We have tested failing the cluster from Node 1 to Node 2 and back again. We have tested the failover process with queries actively running (they fail). We are always able to reconnect and resume normal processing after the backup node recovers the SQL server.

The linked server failures don't appear to have any real pattern to them. There seems to be a weak correlation between a failover and failure in the linked servers (the linked servers fail 1 - 2 days later).

This is not making any sense to me at all. I am NOT a fan of linked servers, but I have no choice here: I inherited these and the developers are NOT interested in alternative solutions.

If you have any thoughts on the matter, please drop a line.

Regards,

hmscott

Additonal info:
Windows 2000 SP4
SQL 2000 SP3 (hotfix 0818)
MDAC 2.7 SP1
OpenLink MultiTier 5.1Contacted MS PSS for answer. Did a stack dump and traced error to a modal messagebox that the client provider raised (!!!).

Back to the provider.

Was not related to cluster server, but it sure "felt" that way. Learned some great things about stack dumps and such.

Regards,

hmscott

servers Fail

I have a Windows 2000 active/passive cluster. I have
configured 6 linked servers using OpenLink Database Proxy
Client. See below for steps I took to create linked
server entries.
In two months of testing, I have had to cycle the SQL
cluster (stop/restart SQL services) three or four times
because the linked servers "fail". By failing, I mean that
the linked servers stop responding to user queries. The
user query simply spins with no activity. I have isolated
the failure to the interaction between SQL and the DSN
entry (ie, the DB proxy works, the DSN works, but SELECT *
FROM OPENQUERY(MyLink, 'SELECT * FROM db2Table.db2Column')
does not work).
The linked servers are configured in the following manner:
1. I configure a DSN using the OpenLink client
(www.openlinksw.com)
2. I configure SQL Server linked server to point to the DSN
Each node in the cluster has identical DSN entries (same
name, same driver, same settings). We have tested failing
the cluster from Node 1 to Node 2 and back again. We have
tested the failover process with queries actively running
(they fail). We are always able to reconnect and resume
normal processing after the backup node recovers the SQL
server.
The linked server failures don't appear to have any real
pattern to them. There seems to be a weak correlation
between a failover and failure in the linked servers (the
linked servers fail 1 - 2 days later).
This is not making any sense to me at all. I am NOT a fan
of linked servers, but I have no choice here: I inherited
these and the developers are NOT interested in alternative
solutions.
If you have any thoughts on the matter, please drop a line.
Regards,
hmscott
Additonal info:
Windows 2000 SP4
SQL 2000 SP3 (hotfix 0818)
MDAC 2.7 SP1
OpenLink MultiTier 5.1Contact MS PSS for answer. Did a stack dump and traced
error to a modal messagebox that the client provider
raised (!!!).
Back to the provider.
Regards,
hmscott
>--Original Message--
>I have a Windows 2000 active/passive cluster. I have
>configured 6 linked servers using OpenLink Database Proxy
>Client. See below for steps I took to create linked
>server entries.
>In two months of testing, I have had to cycle the SQL
>cluster (stop/restart SQL services) three or four times
>because the linked servers "fail". By failing, I mean
that
>the linked servers stop responding to user queries. The
>user query simply spins with no activity. I have
isolated
>the failure to the interaction between SQL and the DSN
>entry (ie, the DB proxy works, the DSN works, but SELECT
*
>FROM OPENQUERY(MyLink, 'SELECT * FROM
db2Table.db2Column')
>does not work).
>The linked servers are configured in the following manner:
>1. I configure a DSN using the OpenLink client
>(www.openlinksw.com)
>2. I configure SQL Server linked server to point to the
DSN
>Each node in the cluster has identical DSN entries (same
>name, same driver, same settings). We have tested failing
>the cluster from Node 1 to Node 2 and back again. We have
>tested the failover process with queries actively running
>(they fail). We are always able to reconnect and resume
>normal processing after the backup node recovers the SQL
>server.
>The linked server failures don't appear to have any real
>pattern to them. There seems to be a weak correlation
>between a failover and failure in the linked servers (the
>linked servers fail 1 - 2 days later).
>This is not making any sense to me at all. I am NOT a fan
>of linked servers, but I have no choice here: I inherited
>these and the developers are NOT interested in
alternative
>solutions.
>If you have any thoughts on the matter, please drop a
line.
>Regards,
>hmscott
>Additonal info:
>Windows 2000 SP4
>SQL 2000 SP3 (hotfix 0818)
>MDAC 2.7 SP1
>OpenLink MultiTier 5.1
>.
>

servers Dialog Error

I freshly installed SQL Server 2005 Developers Edition (with SP1) on Windows XP (with SP2) but every time I right click on “Linked Servers” and choose “New Linked Server…” I get the following error:

TITLE: Microsoft SQL Server Management Studio

Cannot show requested dialog.

ADDITIONAL INFORMATION:

Cannot find table 0. (System.Data)

BUTTONS:

Details:

===================================

Cannot show requested dialog.

===================================

Cannot find table 0. (System.Data)

Program Location:

at System.Data.DataTableCollection.get_Item(Int32 index)

at Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerPropertiesGeneral.PopulateProvidersCombo()

at Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerPropertiesGeneral.Microsoft.SqlServer.Management.SqlMgmt.IPanelForm.OnInitialization()

at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.SetView(Int32 index, TreeNode node)

at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.SelectCurrentNode()

at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.InitializeUI(ViewSwitcherTreeView treeView, ISqlControlCollection viewsHolder, Panel rightPane)

at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.InitializeForm(XmlDocument doc, IServiceProvider provider, ISqlControlCollection control)

at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm..ctor(XmlDocument doc, IServiceProvider provider)

at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ToolsMenuItem.OnCreateAndShowForm(IServiceProvider sp, XmlDocument doc)

at Microsoft.SqlServer.Management.SqlMgmt.RunningFormsTable.RunningFormsTableImpl.ThreadStarter.StartThread()

I tried reinstalling SQL Server 2005 and also tried Service Pack 2 (CTP) but I still get the same error.

PS I know I can use “sp_addlinkedserver” but I still need to know why the “Linked servers Dialog Box” is not showing up.

Here’s my @.@.VERSION

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
Apr 14 2006 01:12:25
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Thank you,

We've recently released SQL Server 2005 Service Pack 2 (RTM). If you could install that, then let us know if you're still having this issue. I know we had worked on the Linked Server dialog in SP2, but I cannot recall the scope of the work.

SQL Server 2005 Service Pack 2:
http://www.microsoft.com/sql/sp2.mspx

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

It works now. I had a feeling it had something to do with “Host Integration Server 2006” so I uninstalled it, rebooted my PC and now “Linked Servers” dialog box in “SQL Server 2005 Developer Edition” works. I have no idea why it does not work with “Host Integration Server 2006” installed. Hopefully you guys have time to look into it and fix it. Also I’m very curious why this happens.

Thank you,

|||

I have this error too.

My @.@.version is

Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1) (i.e I have SP2)

I'm having trouble setting uo a linked server (SQL 2000) to my brand new SQL 2005. When I try to access the Linked Server (SQL2000) from the 2005 Server I get the following error message:

OLE DB provider "SQLNCLI" for linked server "zerver" returned message "Unspecified error".

OLE DB provider "SQLNCLI" for linked server "zerver" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".

Msg 7311, Level 16, State 2, Line 1

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "zerver". The provider supports the interface, but returns a failure code when it is used.

I used sp_addlinkedserver och sp_addlinkedsrvlogin to set up the connection between the servers.

SQL 2000 version is Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Thank you,

Johan Nilsson

|||

You may receive an error message when you try to run distributed queries from a 64-bit SQL Server 2005 client to a linked 32-bit SQL Server 2000 server or to a linked SQL Server 7.0 server

http://support.microsoft.com/kb/906954/en-us

SYMPTOMS
Consider the following scenario. You define a linked 32-bit Microsoft SQL Server 2000 server or a linked SQL Server 7.0 server by using the sp_addlinkedserver stored procedure. Then, you try to run distributed queries from a 64-bit SQL Server 2005 client to the linked server. In this scenario, you may experience one of the following symptoms: ? If the 32-bit SQL Server 2000 server has not been upgraded to SQL Server 2000 Service Pack 3 (SP3) or SQL Server 2000 Service Pack 4 (SP4), you receive the following error message:
The ODBC catalog stored procedures installed on server <LinkedServerName> are version <OldVersionNumber>; version <NewVersionNumber> or later is required to ensure proper operation. Please contact your system administrator.
? You receive an error message if the following conditions are true: ? SQL Server 2000 SP3 or SQL Server 2000 SP4 is installed on the 32-bit SQL Server 2000 server, or you use the linked SQL Server 7.0 server.
? The versions of the system stored procedures on the 32-bit SQL Server 2000 server or on the SQL Server 7.0 server are different from the service pack version that is installed on the server.
The error message is similar to the following:
The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "<LinkedServerName>". The provider supports the interface, but returns a failure code when it is used.
Note If you use the linked SQL Server 2000 server and the server has not been upgraded to SQL Server 2000 SP3 or SQL Server 2000 SP4, you must install SQL Server 2000 SP3 or SQL Server 2000 SP4 first.

sql