When I run linked server queries from SQL2005 it fails.
Query Example :
SELECT * FROM [servername].master.dbo.sysobjects
Error:
OLE DB provider "SQLNCLI" for linked server " servername" returned
message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote
host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.
Notes:
The linked server is configured to use the current logins security
context. It does not matter whether I connect to SQL2000 or SQL2005.
I have a case with MS (6 hours on the phone yesterday), but no help
yet. All spn settings are as recommended by Microsoft. The MS engineer
states that SQL is not passing the login as indicated in a capture file
from netcap.
Any help is appreciated.
TerryHi
Is the remote server SQL2005? Have you tried using a SQL Server login? Can
you use openquery to return the result?
John
"Terry" wrote:
> When I run linked server queries from SQL2005 it fails.
> Query Example :
> SELECT * FROM [servername].master.dbo.sysobjects
> Error:
> OLE DB provider "SQLNCLI" for linked server " servername" returned
> message "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote
> host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> Notes:
> The linked server is configured to use the current logins security
> context. It does not matter whether I connect to SQL2000 or SQL2005.
> I have a case with MS (6 hours on the phone yesterday), but no help
> yet. All spn settings are as recommended by Microsoft. The MS engineer
> states that SQL is not passing the login as indicated in a capture file
> from netcap.
> Any help is appreciated.
> Terry
>|||Terry wrote:
> When I run linked server queries from SQL2005 it fails.
> Query Example :
> SELECT * FROM [servername].master.dbo.sysobjects
> Error:
> OLE DB provider "SQLNCLI" for linked server " servername" returned
> message "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote
> host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> Notes:
> The linked server is configured to use the current logins security
> context. It does not matter whether I connect to SQL2000 or SQL2005.
> I have a case with MS (6 hours on the phone yesterday), but no help
> yet. All spn settings are as recommended by Microsoft. The MS engineer
> states that SQL is not passing the login as indicated in a capture file
> from netcap.
> Any help is appreciated.
> Terry
>
You are connecting to ServerA using Windows Authentication, not a SQL
login. The linked server connection is attempting to pass your SQL
login over to ServerB. You'll have to login to ServerA using a true SQL
login, or setup an explicit user mapping in the linked server definition.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I should be able to use Windows Authenitication, and have always in the
past with SQL2000. Auditing concerns require I know who is connecting.
Mapped logins do work but this is not a solution.
The problem occurs when I connect to SQL2000 or SQL2005 from SQL2005.
Terry
Tracy McKibben wrote:
> Terry wrote:
> You are connecting to ServerA using Windows Authentication, not a SQL
> login. The linked server connection is attempting to pass your SQL
> login over to ServerB. You'll have to login to ServerA using a true SQL
> login, or setup an explicit user mapping in the linked server definition.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||On the 2000 server, run the instcat.sql from the latest service pack you
have installed. This fixes the connect strings. You have to edit the
instcat.sql to put in the correct SQL version. This is documented in the
instcat.sql file. This fixed my 2005->2000 linked server problems. Same as
your problem.
Jackie
"Terry" <tduffy@.calamos.com> wrote in message
news:1152881495.272827.29100@.h48g2000cwc.googlegroups.com...
>I should be able to use Windows Authenitication, and have always in the
> past with SQL2000. Auditing concerns require I know who is connecting.
> Mapped logins do work but this is not a solution.
> The problem occurs when I connect to SQL2000 or SQL2005 from SQL2005.
> Terry
> Tracy McKibben wrote:
>|||Thank you for the responses...
instcat.sql fixes the schema binding errors on 2005->2000 servers. It
does not resolve my issues. instcat has already been run on my 2000
boxes.
Terry
Jackie Brophy wrote:[vbcol=seagreen]
> On the 2000 server, run the instcat.sql from the latest service pack you
> have installed. This fixes the connect strings. You have to edit the
> instcat.sql to put in the correct SQL version. This is documented in the
> instcat.sql file. This fixed my 2005->2000 linked server problems. Same as
> your problem.
> Jackie
> "Terry" <tduffy@.calamos.com> wrote in message
> news:1152881495.272827.29100@.h48g2000cwc.googlegroups.com...|||Hi Terry
I assume that you are using the latest service packs on both instances?
Previously this error has been the result of a virus checker or firewall
blocking communications or possibly a faulty network. Do you get any command
s
running on the remote system?
Windows authentication should not require the user to map a linked server
login, but you can run it with the remote server name parameter only to make
sure these are mapped.
You may want to try and check what protocols you are using and try a
different one!
John
"Terry" wrote:
> Thank you for the responses...
> instcat.sql fixes the schema binding errors on 2005->2000 servers. It
> does not resolve my issues. instcat has already been run on my 2000
> boxes.
> Terry
> Jackie Brophy wrote:
>|||We are still on RTM SQL05, but similar issues (double hops only) exist
on a dev box with sp1. SQL 2000 is a mix of SP3 and SP4.
Firewall and anti-virus are not any issue. I am not sure what you mean
by "commands
running on the remote system." Also, any ideas what might be "faulty"
in the network.
Linked servers with mapped logins works.
Thanks
John Bell wrote:[vbcol=seagreen]
> Hi Terry
> I assume that you are using the latest service packs on both instances?
> Previously this error has been the result of a virus checker or firewall
> blocking communications or possibly a faulty network. Do you get any comma
nds
> running on the remote system?
> Windows authentication should not require the user to map a linked server
> login, but you can run it with the remote server name parameter only to ma
ke
> sure these are mapped.
> You may want to try and check what protocols you are using and try a
> different one!
> John
> "Terry" wrote:
>|||Hi Terry
Use SQL profiler on both machines to try and see what is happening. Did you
check the protocols being used?
http://msdn2.microsoft.com/en-us/library/ms175496.aspx
You may want to make sure that this still occurs on the latest hotfixes for
each version. If that fails you may want to raise an incident with PSS for
this.
John
"Terry" wrote:
> We are still on RTM SQL05, but similar issues (double hops only) exist
> on a dev box with sp1. SQL 2000 is a mix of SP3 and SP4.
> Firewall and anti-virus are not any issue. I am not sure what you mean
> by "commands
> running on the remote system." Also, any ideas what might be "faulty"
> in the network.
> Linked servers with mapped logins works.
> Thanks
>
> John Bell wrote:
>
Showing posts with label fails. Show all posts
Showing posts with label fails. Show all posts
Friday, March 30, 2012
Linked Server problem, apparently with double-hop problem
Ok, I have read several posts about this problem, and even an explanation on how NTLM fails in double-hop scenarios. I understand it.
I, however, cannot understand why it is failing, as I am working in a AD environment with domains in Windows 2000 native mode which should be using Kerberos and not NTLM.
My setup is this: Server A is my own server, a Windows Server 2003 Standard setup with SQL Server 2000. We also have Server B, same OS and same SQL Server; I am NOT admin for Server B, but I am for Server A. Both SQL Servers are setup for Windows Authentication only.
Now, I have a web application (ASP.NET 2.0) running on Server A that connects to SQL Server in Server A. I have learned that the website connects to SQL server as the user NT AUTHORITY\NETWORK SERVICE (as opposed to classic ASP where the user was impersonated), so I created a SQL server login for NT AUTHORITY\NETWORK SERVICE. The website successfully connects to SQL Server A.
The next step is to create a stored procedure on Server A that retrieves data from Server B in the form of a linked server. This way the ASP.NET application has access to the desired data.
This is where the problem presents itself:
Msg 18456, Level 14, State 1, Line 2
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
The SQL Server login in Server B is a local server security group that contains two AD accounts: domainA\myownlogin, and domainA\ServerA, where the latter is the computer object for Server A. Server B is in domainB, and two-way trusts are in place through transivity via the forest root domain. My own account is there for easy testing of scripts in my workstation, but the idea is to remove it and have the website use the computer credentials once it hits production.
Any ideas? Should I check something in the domains? How can I verify that kerberos is being used so I can discard this as a possibility? Any and all feedback is welcome.
Look into using an 'Application Pool' for login security on the web server (create an appropriate domain login account for the application). In AD, give that login account Delegation permission. Then in ServerB, give that login account permissions to access the data.
Monday, March 26, 2012
Linked Server Openquery returns -1 on 2000, works on 2005
We have a query that works fine on SQLServer 2005, but when we run the exact
same query on SQLServer 2000 it fails with a -1. We're not able to get any
other error info at the moment. We're using Windows 2003 Server for both
databases and connecting to Oracle.
KevinAnyone? Buehler?
> We have a query that works fine on SQLServer 2005, but when we run the
> exact same query on SQLServer 2000 it fails with a -1. We're not able to
> get any other error info at the moment. We're using Windows 2003 Server
> for both databases and connecting to Oracle.
> Kevin
>
same query on SQLServer 2000 it fails with a -1. We're not able to get any
other error info at the moment. We're using Windows 2003 Server for both
databases and connecting to Oracle.
KevinAnyone? Buehler?
> We have a query that works fine on SQLServer 2005, but when we run the
> exact same query on SQLServer 2000 it fails with a -1. We're not able to
> get any other error info at the moment. We're using Windows 2003 Server
> for both databases and connecting to Oracle.
> Kevin
>
Linked Server Openquery returns -1 on 2000, works on 2005
We have a query that works fine on SQLServer 2005, but when we run the exact
same query on SQLServer 2000 it fails with a -1. We're not able to get any
other error info at the moment. We're using Windows 2003 Server for both
databases and connecting to Oracle.
KevinAnyone? Buehler?
> We have a query that works fine on SQLServer 2005, but when we run the
> exact same query on SQLServer 2000 it fails with a -1. We're not able to
> get any other error info at the moment. We're using Windows 2003 Server
> for both databases and connecting to Oracle.
> Kevin
>sql
same query on SQLServer 2000 it fails with a -1. We're not able to get any
other error info at the moment. We're using Windows 2003 Server for both
databases and connecting to Oracle.
KevinAnyone? Buehler?
> We have a query that works fine on SQLServer 2005, but when we run the
> exact same query on SQLServer 2000 it fails with a -1. We're not able to
> get any other error info at the moment. We're using Windows 2003 Server
> for both databases and connecting to Oracle.
> Kevin
>sql
Linked Server Openquery returns -1 on 2000, works on 2005
We have a query that works fine on SQLServer 2005, but when we run the exact
same query on SQLServer 2000 it fails with a -1. We're not able to get any
other error info at the moment. We're using Windows 2003 Server for both
databases and connecting to Oracle.
Kevin
Anyone? Buehler?
> We have a query that works fine on SQLServer 2005, but when we run the
> exact same query on SQLServer 2000 it fails with a -1. We're not able to
> get any other error info at the moment. We're using Windows 2003 Server
> for both databases and connecting to Oracle.
> Kevin
>
same query on SQLServer 2000 it fails with a -1. We're not able to get any
other error info at the moment. We're using Windows 2003 Server for both
databases and connecting to Oracle.
Kevin
Anyone? Buehler?
> We have a query that works fine on SQLServer 2005, but when we run the
> exact same query on SQLServer 2000 it fails with a -1. We're not able to
> get any other error info at the moment. We're using Windows 2003 Server
> for both databases and connecting to Oracle.
> Kevin
>
Friday, March 23, 2012
linked server msdb stored procedures
exec [ltd-gln-db2].msdb..sp_help_job
exec [ltd-gln-db2].msdb..sp_help_alert
Neither line fails.
The first returns 0 rows.
The second returns > 0 rows.
Both stored procedures allow 'public' to 'exec'.
Question: Why does sp_help_job return no rows?
Note: SQLServer 2000 SP2 on all db servers.Hi
Are there any jobs on the remote system?
As sp_help_job does not call sp_help_alert therefore I am not sure why you
should think sp_help_job would return anything if sp_help_alert does?
John
"Larry Storm" <larry.storm@.ltd.org> wrote in message
news:0c1901c5362c$251ac720$a401280a@.phx.gbl...
> exec [ltd-gln-db2].msdb..sp_help_job
> exec [ltd-gln-db2].msdb..sp_help_alert
> Neither line fails.
> The first returns 0 rows.
> The second returns > 0 rows.
> Both stored procedures allow 'public' to 'exec'.
> Question: Why does sp_help_job return no rows?
> Note: SQLServer 2000 SP2 on all db servers.
>
>|||
>--Original Message--
>Hi
>Are there any jobs on the remote system?
>As sp_help_job does not call sp_help_alert therefore I am
not sure why you
>should think sp_help_job would return anything if
sp_help_alert does?
>John
>"Larry Storm" <larry.storm@.ltd.org> wrote in message
>news:0c1901c5362c$251ac720$a401280a@.phx.gbl...
>
>.
>When I am connected to the remote system, sp_help_job
displays info for multiple jobs.
The reason I included information for sp_help_alert was to
show the remote server is accessable as a linked server and
permissions on stored procedures allow sp_help_alert to
return results.
My question remains:
Why does sp_help_job return zero rows?|||Hi
Try profiling the procedure and then see what the steps do when you log in
locally as that user.
John
"Larry Storm" <larry.storm@.ltd.org> wrote in message
news:1a0001c5392e$f9f0e4c0$a401280a@.phx.gbl...
>
> not sure why you
> sp_help_alert does?
> displays info for multiple jobs.
> The reason I included information for sp_help_alert was to
> show the remote server is accessable as a linked server and
> permissions on stored procedures allow sp_help_alert to
> return results.
> My question remains:
> Why does sp_help_job return zero rows?
>
>|||Larry Storm wrote:
> exec [ltd-gln-db2].msdb..sp_help_job
> exec [ltd-gln-db2].msdb..sp_help_alert
> Neither line fails.
> The first returns 0 rows.
> The second returns > 0 rows.
> Both stored procedures allow 'public' to 'exec'.
> Question: Why does sp_help_job return no rows?
> Note: SQLServer 2000 SP2 on all db servers.
>
From BOL: "A user who is not a member of the sy
min fixed role can use sp_
help_job to view only the jobs he/she owns."
HTH,
Igor
exec [ltd-gln-db2].msdb..sp_help_alert
Neither line fails.
The first returns 0 rows.
The second returns > 0 rows.
Both stored procedures allow 'public' to 'exec'.
Question: Why does sp_help_job return no rows?
Note: SQLServer 2000 SP2 on all db servers.Hi
Are there any jobs on the remote system?
As sp_help_job does not call sp_help_alert therefore I am not sure why you
should think sp_help_job would return anything if sp_help_alert does?
John
"Larry Storm" <larry.storm@.ltd.org> wrote in message
news:0c1901c5362c$251ac720$a401280a@.phx.gbl...
> exec [ltd-gln-db2].msdb..sp_help_job
> exec [ltd-gln-db2].msdb..sp_help_alert
> Neither line fails.
> The first returns 0 rows.
> The second returns > 0 rows.
> Both stored procedures allow 'public' to 'exec'.
> Question: Why does sp_help_job return no rows?
> Note: SQLServer 2000 SP2 on all db servers.
>
>|||
>--Original Message--
>Hi
>Are there any jobs on the remote system?
>As sp_help_job does not call sp_help_alert therefore I am
not sure why you
>should think sp_help_job would return anything if
sp_help_alert does?
>John
>"Larry Storm" <larry.storm@.ltd.org> wrote in message
>news:0c1901c5362c$251ac720$a401280a@.phx.gbl...
>
>.
>When I am connected to the remote system, sp_help_job
displays info for multiple jobs.
The reason I included information for sp_help_alert was to
show the remote server is accessable as a linked server and
permissions on stored procedures allow sp_help_alert to
return results.
My question remains:
Why does sp_help_job return zero rows?|||Hi
Try profiling the procedure and then see what the steps do when you log in
locally as that user.
John
"Larry Storm" <larry.storm@.ltd.org> wrote in message
news:1a0001c5392e$f9f0e4c0$a401280a@.phx.gbl...
>
> not sure why you
> sp_help_alert does?
> displays info for multiple jobs.
> The reason I included information for sp_help_alert was to
> show the remote server is accessable as a linked server and
> permissions on stored procedures allow sp_help_alert to
> return results.
> My question remains:
> Why does sp_help_job return zero rows?
>
>|||Larry Storm wrote:
> exec [ltd-gln-db2].msdb..sp_help_job
> exec [ltd-gln-db2].msdb..sp_help_alert
> Neither line fails.
> The first returns 0 rows.
> The second returns > 0 rows.
> Both stored procedures allow 'public' to 'exec'.
> Question: Why does sp_help_job return no rows?
> Note: SQLServer 2000 SP2 on all db servers.
>
From BOL: "A user who is not a member of the sy
min fixed role can use sp_help_job to view only the jobs he/she owns."
HTH,
Igor
Monday, March 19, 2012
Linked Server Fails w/Error: 7399
I'm trying to connnect from a SQL Server 7.0 db to an
Oracle 8.i instance. Connection is failing with the
following error message: Error 7399 OLE DB
Provider 'MSDAOR' reported an error.
I used the following ddl to create my linked server &
linked server logon:
exec
sp_addlinkedserver 'CNRDAPRD', 'ORACLE', 'MSADORA', 'cnrdap
rd'
exec sp_addlinkedsrvlogin 'CNRDAPRD', 'false', 'local SQL
Server logon', 'Oracle Server Logon', 'Oracle Server logon
password'
I can connect to the Oracle database from the box where
I'm running SQL Server.
When I issue: ping oracle server name from box running SQL
Server I can ping server.
When I issue tnsping oracle database name from box running
SQL server I can ping Oracle database.
When I issue sqlplus ORA logon/ORA password@.ORA dbname I
can logon to database so I know I can connect to the box
and I'm using the right logon & password.
Any help greatly appreciated. Thanks in advance.Check if these articles help:
http://support.microsoft.com/defaul...kb;en-us;280106
http://support.microsoft.com/defaul...kb;en-us;220915
http://support.microsoft.com/defaul...kb;en-us;277002
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"walter.williams@.rd.com" <anonymous@.discussions.microsoft.com> wrote in
message news:6f5901c4061d$a857ef90$a601280a@.phx.gbl...
> I'm trying to connnect from a SQL Server 7.0 db to an
> Oracle 8.i instance. Connection is failing with the
> following error message: Error 7399 OLE DB
> Provider 'MSDAOR' reported an error.
> I used the following ddl to create my linked server &
> linked server logon:
> exec
> sp_addlinkedserver 'CNRDAPRD', 'ORACLE', 'MSADORA', 'cnrdap
> rd'
> exec sp_addlinkedsrvlogin 'CNRDAPRD', 'false', 'local SQL
> Server logon', 'Oracle Server Logon', 'Oracle Server logon
> password'
> I can connect to the Oracle database from the box where
> I'm running SQL Server.
> When I issue: ping oracle server name from box running SQL
> Server I can ping server.
> When I issue tnsping oracle database name from box running
> SQL server I can ping Oracle database.
> When I issue sqlplus ORA logon/ORA password@.ORA dbname I
> can logon to database so I know I can connect to the box
> and I'm using the right logon & password.
> Any help greatly appreciated. Thanks in advance.|||I reviewed the Knowledge Base articles you suggested. This was a pre-existi
ng linked server definition that was working and has just failed. I've atte
mpted to redefine it. I'm not getting the message about the Oracle client a
nd networking comonents mis
sing so I'm ruling out 277002 & 220915. Article 280106 suggests that the ve
rsion of MDAC might be wrong (you need MDAC 2.5, or later, installed on SQL
Server computer). How do I check my version of MDAC to see if I'm at 2.5 or
later? TIA|||> How do I check my version of MDAC to see if I'm at 2.5 or later?
You can determine the installed MDAC version with the component checker
tool:
<http://www.microsoft.com/downloads/...a8df6-4a21-4b43
-bf53-14332ef092c9&displaylang=en>
Hope this helps.
Dan Guzman
SQL Server MVP
"walter.williams@.rd.com" <anonymous@.discussions.microsoft.com> wrote in
message news:89454DC5-D415-4284-95FD-760FA7B0C8EC@.microsoft.com...
> I reviewed the Knowledge Base articles you suggested. This was a
pre-existing linked server definition that was working and has just failed.
I've attempted to redefine it. I'm not getting the message about the Oracle
client and networking comonents missing so I'm ruling out 277002 & 220915.
Article 280106 suggests that the version of MDAC might be wrong (you need
MDAC 2.5, or later, installed on SQL Server computer). How do I check my
version of MDAC to see if I'm at 2.5 or later? TIA|||I downloaded the component checker and verified I have MDAC 2.5 sp3 installe
d on my client (SQL Server) computer. I took another look at one of the Kno
wledge Base Articles, #280106, that Dejan suggested. We checked the registr
y entries for Oracle client
software. We found that the registry entries were pointing to Oracle Client
versions for Oracle Client 7.3 event though the dlls were not present on th
e machine, Oracle Client 8.i is loaded onto the machine. That's the version
we expected to see in the
registry. We suspected that the registry entries were somehow corrupted or
overlaid. When we checked the event log for the server it was full of error
s, the server had lost it's connection to the domain server. We scheduled a
n emergecy reboot of the s
erver (server hadn't been stopped & restarted in over 100 days) and when the
server came back up we were able to query our linked server.
Oracle 8.i instance. Connection is failing with the
following error message: Error 7399 OLE DB
Provider 'MSDAOR' reported an error.
I used the following ddl to create my linked server &
linked server logon:
exec
sp_addlinkedserver 'CNRDAPRD', 'ORACLE', 'MSADORA', 'cnrdap
rd'
exec sp_addlinkedsrvlogin 'CNRDAPRD', 'false', 'local SQL
Server logon', 'Oracle Server Logon', 'Oracle Server logon
password'
I can connect to the Oracle database from the box where
I'm running SQL Server.
When I issue: ping oracle server name from box running SQL
Server I can ping server.
When I issue tnsping oracle database name from box running
SQL server I can ping Oracle database.
When I issue sqlplus ORA logon/ORA password@.ORA dbname I
can logon to database so I know I can connect to the box
and I'm using the right logon & password.
Any help greatly appreciated. Thanks in advance.Check if these articles help:
http://support.microsoft.com/defaul...kb;en-us;280106
http://support.microsoft.com/defaul...kb;en-us;220915
http://support.microsoft.com/defaul...kb;en-us;277002
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"walter.williams@.rd.com" <anonymous@.discussions.microsoft.com> wrote in
message news:6f5901c4061d$a857ef90$a601280a@.phx.gbl...
> I'm trying to connnect from a SQL Server 7.0 db to an
> Oracle 8.i instance. Connection is failing with the
> following error message: Error 7399 OLE DB
> Provider 'MSDAOR' reported an error.
> I used the following ddl to create my linked server &
> linked server logon:
> exec
> sp_addlinkedserver 'CNRDAPRD', 'ORACLE', 'MSADORA', 'cnrdap
> rd'
> exec sp_addlinkedsrvlogin 'CNRDAPRD', 'false', 'local SQL
> Server logon', 'Oracle Server Logon', 'Oracle Server logon
> password'
> I can connect to the Oracle database from the box where
> I'm running SQL Server.
> When I issue: ping oracle server name from box running SQL
> Server I can ping server.
> When I issue tnsping oracle database name from box running
> SQL server I can ping Oracle database.
> When I issue sqlplus ORA logon/ORA password@.ORA dbname I
> can logon to database so I know I can connect to the box
> and I'm using the right logon & password.
> Any help greatly appreciated. Thanks in advance.|||I reviewed the Knowledge Base articles you suggested. This was a pre-existi
ng linked server definition that was working and has just failed. I've atte
mpted to redefine it. I'm not getting the message about the Oracle client a
nd networking comonents mis
sing so I'm ruling out 277002 & 220915. Article 280106 suggests that the ve
rsion of MDAC might be wrong (you need MDAC 2.5, or later, installed on SQL
Server computer). How do I check my version of MDAC to see if I'm at 2.5 or
later? TIA|||> How do I check my version of MDAC to see if I'm at 2.5 or later?
You can determine the installed MDAC version with the component checker
tool:
<http://www.microsoft.com/downloads/...a8df6-4a21-4b43
-bf53-14332ef092c9&displaylang=en>
Hope this helps.
Dan Guzman
SQL Server MVP
"walter.williams@.rd.com" <anonymous@.discussions.microsoft.com> wrote in
message news:89454DC5-D415-4284-95FD-760FA7B0C8EC@.microsoft.com...
> I reviewed the Knowledge Base articles you suggested. This was a
pre-existing linked server definition that was working and has just failed.
I've attempted to redefine it. I'm not getting the message about the Oracle
client and networking comonents missing so I'm ruling out 277002 & 220915.
Article 280106 suggests that the version of MDAC might be wrong (you need
MDAC 2.5, or later, installed on SQL Server computer). How do I check my
version of MDAC to see if I'm at 2.5 or later? TIA|||I downloaded the component checker and verified I have MDAC 2.5 sp3 installe
d on my client (SQL Server) computer. I took another look at one of the Kno
wledge Base Articles, #280106, that Dejan suggested. We checked the registr
y entries for Oracle client
software. We found that the registry entries were pointing to Oracle Client
versions for Oracle Client 7.3 event though the dlls were not present on th
e machine, Oracle Client 8.i is loaded onto the machine. That's the version
we expected to see in the
registry. We suspected that the registry entries were somehow corrupted or
overlaid. When we checked the event log for the server it was full of error
s, the server had lost it's connection to the domain server. We scheduled a
n emergecy reboot of the s
erver (server hadn't been stopped & restarted in over 100 days) and when the
server came back up we were able to query our linked server.
Linked Server Fails after MDAC 2.8 upgrade
We installed the MDAC 2.8 upgrade and our existing
Microsoft OLE DB Provider for Oracle stopped working.
Other providers were fine, i.e. SQL Server (linking to
another mssql server).
Has anyone had this issue? Or does anyone have any
ideas as to what changed in 2.8 that would affect this
linked server?
Thanks in advance,
MarkI think that may be something related with distributed transactions.
I have some applications that stopped to work when I upgrade to MDAC 2.8.
Trying to solve this I discover that just returning the sql server odbc
driver files to version 2.7 the applications could work correctly... anyway
I think that is a bug in MDAC 2.8
Agrinei
"Mark D" <anonymous@.discussions.microsoft.com> wrote in message
news:816301c4076c$80de9630$a601280a@.phx.gbl...
> We installed the MDAC 2.8 upgrade and our existing
> Microsoft OLE DB Provider for Oracle stopped working.
> Other providers were fine, i.e. SQL Server (linking to
> another mssql server).
> Has anyone had this issue? Or does anyone have any
> ideas as to what changed in 2.8 that would affect this
> linked server?
> Thanks in advance,
> Mark
Microsoft OLE DB Provider for Oracle stopped working.
Other providers were fine, i.e. SQL Server (linking to
another mssql server).
Has anyone had this issue? Or does anyone have any
ideas as to what changed in 2.8 that would affect this
linked server?
Thanks in advance,
MarkI think that may be something related with distributed transactions.
I have some applications that stopped to work when I upgrade to MDAC 2.8.
Trying to solve this I discover that just returning the sql server odbc
driver files to version 2.7 the applications could work correctly... anyway
I think that is a bug in MDAC 2.8
Agrinei
"Mark D" <anonymous@.discussions.microsoft.com> wrote in message
news:816301c4076c$80de9630$a601280a@.phx.gbl...
> We installed the MDAC 2.8 upgrade and our existing
> Microsoft OLE DB Provider for Oracle stopped working.
> Other providers were fine, i.e. SQL Server (linking to
> another mssql server).
> Has anyone had this issue? Or does anyone have any
> ideas as to what changed in 2.8 that would affect this
> linked server?
> Thanks in advance,
> Mark
linked server failed
hi,
i have added a link server but it fails when i try to retrieve data.
SQL server in on the same machine
i have created the another SQL server instance
Main Server: SERVER1
Instance: SERVER1/NewServer
EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
Server'
EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
when i try to run the above query it gives me the error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'.
anyone have suggestion?
Adi
Adi
Try
select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
"Adi" <Adi@.discussions.microsoft.com> wrote in message
news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
> hi,
> i have added a link server but it fails when i try to retrieve data.
> SQL server in on the same machine
> i have created the another SQL server instance
> Main Server: SERVER1
> Instance: SERVER1/NewServer
> EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
> Server'
> EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
> select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
> when i try to run the above query it gives me the error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '/'.
>
> anyone have suggestion?
> Adi
>
|||yes i tried this too but it gives me the message:
SQL Server Does not exists or access denied.
i have also checked the sysservers the linked server is exist in it.
when i try to run retrieve data from main server its working fine but when i
try to retrieve data from SQL Server instance it failed.
"Uri Dimant" wrote:
> Adi
> Try
> select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
> "Adi" <Adi@.discussions.microsoft.com> wrote in message
> news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
>
>
|||Try looking up the CORRECT syntax in Books Online.
You are using the wrong slash.
Try [Server1\NewServer].
Sincerely,
Anthony Thomas
"Adi" <Adi@.discussions.microsoft.com> wrote in message news:2C6DA00D-ACDE-44D0-AC42-F6F3DC34D58C@.microsoft.com...
yes i tried this too but it gives me the message:
SQL Server Does not exists or access denied.
i have also checked the sysservers the linked server is exist in it.
when i try to run retrieve data from main server its working fine but when i
try to retrieve data from SQL Server instance it failed.
"Uri Dimant" wrote:
> Adi
> Try
> select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
>
> "Adi" <Adi@.discussions.microsoft.com> wrote in message
> news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
>
>
>|||I Anthony.
I have the same problem of Adi (SQL Server Does not exists or access
denied), but my server name is nsv, so the problem is not the slash.
The likend server it seems to be created correctly.
Any suggestion?
"AnthonyThomas" wrote:
[vbcol=seagreen]
> Try looking up the CORRECT syntax in Books Online.
> You are using the wrong slash.
> Try [Server1\NewServer].
> Sincerely,
>
> Anthony Thomas
>
> --
> "Adi" <Adi@.discussions.microsoft.com> wrote in message news:2C6DA00D-ACDE-44D0-AC42-F6F3DC34D58C@.microsoft.com...
> yes i tried this too but it gives me the message:
> SQL Server Does not exists or access denied.
> i have also checked the sysservers the linked server is exist in it.
> when i try to run retrieve data from main server its working fine but when i
> try to retrieve data from SQL Server instance it failed.
>
> "Uri Dimant" wrote:
i have added a link server but it fails when i try to retrieve data.
SQL server in on the same machine
i have created the another SQL server instance
Main Server: SERVER1
Instance: SERVER1/NewServer
EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
Server'
EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
when i try to run the above query it gives me the error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'.
anyone have suggestion?
Adi
Adi
Try
select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
"Adi" <Adi@.discussions.microsoft.com> wrote in message
news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
> hi,
> i have added a link server but it fails when i try to retrieve data.
> SQL server in on the same machine
> i have created the another SQL server instance
> Main Server: SERVER1
> Instance: SERVER1/NewServer
> EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
> Server'
> EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
> select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
> when i try to run the above query it gives me the error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '/'.
>
> anyone have suggestion?
> Adi
>
|||yes i tried this too but it gives me the message:
SQL Server Does not exists or access denied.
i have also checked the sysservers the linked server is exist in it.
when i try to run retrieve data from main server its working fine but when i
try to retrieve data from SQL Server instance it failed.
"Uri Dimant" wrote:
> Adi
> Try
> select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
> "Adi" <Adi@.discussions.microsoft.com> wrote in message
> news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
>
>
|||Try looking up the CORRECT syntax in Books Online.
You are using the wrong slash.
Try [Server1\NewServer].
Sincerely,
Anthony Thomas
"Adi" <Adi@.discussions.microsoft.com> wrote in message news:2C6DA00D-ACDE-44D0-AC42-F6F3DC34D58C@.microsoft.com...
yes i tried this too but it gives me the message:
SQL Server Does not exists or access denied.
i have also checked the sysservers the linked server is exist in it.
when i try to run retrieve data from main server its working fine but when i
try to retrieve data from SQL Server instance it failed.
"Uri Dimant" wrote:
> Adi
> Try
> select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
>
> "Adi" <Adi@.discussions.microsoft.com> wrote in message
> news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
>
>
>|||I Anthony.
I have the same problem of Adi (SQL Server Does not exists or access
denied), but my server name is nsv, so the problem is not the slash.
The likend server it seems to be created correctly.
Any suggestion?
"AnthonyThomas" wrote:
[vbcol=seagreen]
> Try looking up the CORRECT syntax in Books Online.
> You are using the wrong slash.
> Try [Server1\NewServer].
> Sincerely,
>
> Anthony Thomas
>
> --
> "Adi" <Adi@.discussions.microsoft.com> wrote in message news:2C6DA00D-ACDE-44D0-AC42-F6F3DC34D58C@.microsoft.com...
> yes i tried this too but it gives me the message:
> SQL Server Does not exists or access denied.
> i have also checked the sysservers the linked server is exist in it.
> when i try to run retrieve data from main server its working fine but when i
> try to retrieve data from SQL Server instance it failed.
>
> "Uri Dimant" wrote:
linked server failed
hi,
i have added a link server but it fails when i try to retrieve data.
SQL server in on the same machine
i have created the another SQL server instance
Main Server: SERVER1
Instance: SERVER1/NewServer
EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
Server'
EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
when i try to run the above query it gives me the error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'.
anyone have suggestion'
AdiAdi
Try
select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
"Adi" <Adi@.discussions.microsoft.com> wrote in message
news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
> hi,
> i have added a link server but it fails when i try to retrieve data.
> SQL server in on the same machine
> i have created the another SQL server instance
> Main Server: SERVER1
> Instance: SERVER1/NewServer
> EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
> Server'
> EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
> select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
> when i try to run the above query it gives me the error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '/'.
>
> anyone have suggestion'
> Adi
>|||yes i tried this too but it gives me the message:
SQL Server Does not exists or access denied.
i have also checked the sysservers the linked server is exist in it.
when i try to run retrieve data from main server its working fine but when i
try to retrieve data from SQL Server instance it failed.
"Uri Dimant" wrote:
> Adi
> Try
> select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
> "Adi" <Adi@.discussions.microsoft.com> wrote in message
> news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
> > hi,
> > i have added a link server but it fails when i try to retrieve data.
> >
> > SQL server in on the same machine
> > i have created the another SQL server instance
> >
> > Main Server: SERVER1
> > Instance: SERVER1/NewServer
> >
> > EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
> > Server'
> > EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
> >
> > select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
> >
> > when i try to run the above query it gives me the error
> > Server: Msg 170, Level 15, State 1, Line 1
> > Line 1: Incorrect syntax near '/'.
> >
> >
> > anyone have suggestion'
> > Adi
> >
>
>|||I Anthony.
I have the same problem of Adi (SQL Server Does not exists or access
denied), but my server name is nsv, so the problem is not the slash.
The likend server it seems to be created correctly.
Any suggestion?
"AnthonyThomas" wrote:
> Try looking up the CORRECT syntax in Books Online.
> You are using the wrong slash.
> Try [Server1\NewServer].
> Sincerely,
>
> Anthony Thomas
>
> --
> "Adi" <Adi@.discussions.microsoft.com> wrote in message news:2C6DA00D-ACDE-44D0-AC42-F6F3DC34D58C@.microsoft.com...
> yes i tried this too but it gives me the message:
> SQL Server Does not exists or access denied.
> i have also checked the sysservers the linked server is exist in it.
> when i try to run retrieve data from main server its working fine but when i
> try to retrieve data from SQL Server instance it failed.
>
> "Uri Dimant" wrote:
> > Adi
> > Try
> > select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
> >
> > "Adi" <Adi@.discussions.microsoft.com> wrote in message
> > news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
> > > hi,
> > > i have added a link server but it fails when i try to retrieve data.
> > >
> > > SQL server in on the same machine
> > > i have created the another SQL server instance
> > >
> > > Main Server: SERVER1
> > > Instance: SERVER1/NewServer
> > >
> > > EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
> > > Server'
> > > EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
> > >
> > > select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
> > >
> > > when i try to run the above query it gives me the error
> > > Server: Msg 170, Level 15, State 1, Line 1
> > > Line 1: Incorrect syntax near '/'.
> > >
> > >
> > > anyone have suggestion'
> > > Adi
> > >
> >
> >
> >
i have added a link server but it fails when i try to retrieve data.
SQL server in on the same machine
i have created the another SQL server instance
Main Server: SERVER1
Instance: SERVER1/NewServer
EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
Server'
EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
when i try to run the above query it gives me the error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'.
anyone have suggestion'
AdiAdi
Try
select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
"Adi" <Adi@.discussions.microsoft.com> wrote in message
news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
> hi,
> i have added a link server but it fails when i try to retrieve data.
> SQL server in on the same machine
> i have created the another SQL server instance
> Main Server: SERVER1
> Instance: SERVER1/NewServer
> EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
> Server'
> EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
> select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
> when i try to run the above query it gives me the error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '/'.
>
> anyone have suggestion'
> Adi
>|||yes i tried this too but it gives me the message:
SQL Server Does not exists or access denied.
i have also checked the sysservers the linked server is exist in it.
when i try to run retrieve data from main server its working fine but when i
try to retrieve data from SQL Server instance it failed.
"Uri Dimant" wrote:
> Adi
> Try
> select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
> "Adi" <Adi@.discussions.microsoft.com> wrote in message
> news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
> > hi,
> > i have added a link server but it fails when i try to retrieve data.
> >
> > SQL server in on the same machine
> > i have created the another SQL server instance
> >
> > Main Server: SERVER1
> > Instance: SERVER1/NewServer
> >
> > EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
> > Server'
> > EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
> >
> > select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
> >
> > when i try to run the above query it gives me the error
> > Server: Msg 170, Level 15, State 1, Line 1
> > Line 1: Incorrect syntax near '/'.
> >
> >
> > anyone have suggestion'
> > Adi
> >
>
>|||I Anthony.
I have the same problem of Adi (SQL Server Does not exists or access
denied), but my server name is nsv, so the problem is not the slash.
The likend server it seems to be created correctly.
Any suggestion?
"AnthonyThomas" wrote:
> Try looking up the CORRECT syntax in Books Online.
> You are using the wrong slash.
> Try [Server1\NewServer].
> Sincerely,
>
> Anthony Thomas
>
> --
> "Adi" <Adi@.discussions.microsoft.com> wrote in message news:2C6DA00D-ACDE-44D0-AC42-F6F3DC34D58C@.microsoft.com...
> yes i tried this too but it gives me the message:
> SQL Server Does not exists or access denied.
> i have also checked the sysservers the linked server is exist in it.
> when i try to run retrieve data from main server its working fine but when i
> try to retrieve data from SQL Server instance it failed.
>
> "Uri Dimant" wrote:
> > Adi
> > Try
> > select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
> >
> > "Adi" <Adi@.discussions.microsoft.com> wrote in message
> > news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
> > > hi,
> > > i have added a link server but it fails when i try to retrieve data.
> > >
> > > SQL server in on the same machine
> > > i have created the another SQL server instance
> > >
> > > Main Server: SERVER1
> > > Instance: SERVER1/NewServer
> > >
> > > EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
> > > Server'
> > > EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
> > >
> > > select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
> > >
> > > when i try to run the above query it gives me the error
> > > Server: Msg 170, Level 15, State 1, Line 1
> > > Line 1: Incorrect syntax near '/'.
> > >
> > >
> > > anyone have suggestion'
> > > Adi
> > >
> >
> >
> >
linked server failed
hi,
i have added a link server but it fails when i try to retrieve data.
SQL server in on the same machine
i have created the another SQL server instance
Main Server: SERVER1
Instance: SERVER1/NewServer
EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
Server'
EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
when i try to run the above query it gives me the error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'.
anyone have suggestion'
AdiAdi
Try
select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
"Adi" <Adi@.discussions.microsoft.com> wrote in message
news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
> hi,
> i have added a link server but it fails when i try to retrieve data.
> SQL server in on the same machine
> i have created the another SQL server instance
> Main Server: SERVER1
> Instance: SERVER1/NewServer
> EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
> Server'
> EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
> select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
> when i try to run the above query it gives me the error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '/'.
>
> anyone have suggestion'
> Adi
>|||yes i tried this too but it gives me the message:
SQL Server Does not exists or access denied.
i have also checked the sysservers the linked server is exist in it.
when i try to run retrieve data from main server its working fine but when i
try to retrieve data from SQL Server instance it failed.
"Uri Dimant" wrote:
> Adi
> Try
> select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
> "Adi" <Adi@.discussions.microsoft.com> wrote in message
> news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
>
>|||Try looking up the CORRECT syntax in Books Online.
You are using the wrong slash.
Try [Server1\NewServer].
Sincerely,
Anthony Thomas
--
"Adi" <Adi@.discussions.microsoft.com> wrote in message news:2C6DA00D-ACDE-
44D0-AC42-F6F3DC34D58C@.microsoft.com...
yes i tried this too but it gives me the message:
SQL Server Does not exists or access denied.
i have also checked the sysservers the linked server is exist in it.
when i try to run retrieve data from main server its working fine but when
i
try to retrieve data from SQL Server instance it failed.
"Uri Dimant" wrote:
> Adi
> Try
> select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
>
> "Adi" <Adi@.discussions.microsoft.com> wrote in message
> news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
>
>
>|||I Anthony.
I have the same problem of Adi (SQL Server Does not exists or access
denied), but my server name is nsv, so the problem is not the slash.
The likend server it seems to be created correctly.
Any suggestion?
"AnthonyThomas" wrote:
[vbcol=seagreen]
> Try looking up the CORRECT syntax in Books Online.
> You are using the wrong slash.
> Try [Server1\NewServer].
> Sincerely,
>
> Anthony Thomas
>
> --
> "Adi" <Adi@.discussions.microsoft.com> wrote in message news:2C6DA00D-ACD
E-44D0-AC42-F6F3DC34D58C@.microsoft.com...
> yes i tried this too but it gives me the message:
> SQL Server Does not exists or access denied.
> i have also checked the sysservers the linked server is exist in it.
> when i try to run retrieve data from main server its working fine but wh
en i
> try to retrieve data from SQL Server instance it failed.
>
> "Uri Dimant" wrote:
>
i have added a link server but it fails when i try to retrieve data.
SQL server in on the same machine
i have created the another SQL server instance
Main Server: SERVER1
Instance: SERVER1/NewServer
EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
Server'
EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
when i try to run the above query it gives me the error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'.
anyone have suggestion'
AdiAdi
Try
select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
"Adi" <Adi@.discussions.microsoft.com> wrote in message
news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
> hi,
> i have added a link server but it fails when i try to retrieve data.
> SQL server in on the same machine
> i have created the another SQL server instance
> Main Server: SERVER1
> Instance: SERVER1/NewServer
> EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
> Server'
> EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
> select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
> when i try to run the above query it gives me the error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '/'.
>
> anyone have suggestion'
> Adi
>|||yes i tried this too but it gives me the message:
SQL Server Does not exists or access denied.
i have also checked the sysservers the linked server is exist in it.
when i try to run retrieve data from main server its working fine but when i
try to retrieve data from SQL Server instance it failed.
"Uri Dimant" wrote:
> Adi
> Try
> select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
> "Adi" <Adi@.discussions.microsoft.com> wrote in message
> news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
>
>|||Try looking up the CORRECT syntax in Books Online.
You are using the wrong slash.
Try [Server1\NewServer].
Sincerely,
Anthony Thomas
--
"Adi" <Adi@.discussions.microsoft.com> wrote in message news:2C6DA00D-ACDE-
44D0-AC42-F6F3DC34D58C@.microsoft.com...
yes i tried this too but it gives me the message:
SQL Server Does not exists or access denied.
i have also checked the sysservers the linked server is exist in it.
when i try to run retrieve data from main server its working fine but when
i
try to retrieve data from SQL Server instance it failed.
"Uri Dimant" wrote:
> Adi
> Try
> select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
>
> "Adi" <Adi@.discussions.microsoft.com> wrote in message
> news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
>
>
>|||I Anthony.
I have the same problem of Adi (SQL Server Does not exists or access
denied), but my server name is nsv, so the problem is not the slash.
The likend server it seems to be created correctly.
Any suggestion?
"AnthonyThomas" wrote:
[vbcol=seagreen]
> Try looking up the CORRECT syntax in Books Online.
> You are using the wrong slash.
> Try [Server1\NewServer].
> Sincerely,
>
> Anthony Thomas
>
> --
> "Adi" <Adi@.discussions.microsoft.com> wrote in message news:2C6DA00D-ACD
E-44D0-AC42-F6F3DC34D58C@.microsoft.com...
> yes i tried this too but it gives me the message:
> SQL Server Does not exists or access denied.
> i have also checked the sysservers the linked server is exist in it.
> when i try to run retrieve data from main server its working fine but wh
en i
> try to retrieve data from SQL Server instance it failed.
>
> "Uri Dimant" wrote:
>
Friday, February 24, 2012
Linked Server - On Connection Error Resume
I have a stored procedure which connects to multiple linked servers. If one connection fails, the stored procedure ends with error.
Is it possible to have the stored proc continue instead of quit?
I have tried the following and none work:
- connect in a different sp
- use sp_executesql
Thanks
Oh yeah...I am using SQL SERVER 2000 SP4 and I know SQL SERVER 2005 has a sp to test a linked server, but I cannot upgrade.
I once had the problem to check for the availbility of linked server. The solution (in our case) was to ping the servers on a regular basis, by using a SQL Server Agent job, which did something like--INSERT INTO PingTable
--SomeSelectonTheServer, mainly something like SELECT 1, or SELECT * FROM dual (in this case for Oracle)
--UPDATE PingTable, update the status of the ping record to successfull.
Therefore we could query the table for the successfull connectivity, if the column Successfull was not filed or updated properly we threw a manual error.
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
Subscribe to:
Posts (Atom)