April 14, 2011 at 11:19 am
In SSMS I have connected to the remote server (SQL 2000 over a WAN)- it's in the Object Explorer with my native server. I can right-click on a table and Select Top 1000 rows - it produces results. I can view properties of the tables.
However, I can not run a new query against it. Throws this error:
Msg 17, Level 16, State 1, Line 1
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Here is the code:
use master
go
exec sp_addlinkedsrvlogin 'OWDC', 'true', 'sa', 'FishBait'
go
select owc.customernumber
from [owdc].[sfidata].[dbo].[tblcustomer] owc
where not exists (select customernumber from VSQL2.sfidata.dbo.tblcustomer
where customernumber = owc.customernumber)
NOTE: running query piecemeal, the login part works. The error seems to be coming from the Select statement.
Any advice would be appreciated!
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
April 14, 2011 at 12:04 pm
Try to connect to the linked server using the same user and pass.
the first part would run successfully even if you put in a user that did not exist.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 14, 2011 at 12:31 pm
Did you run instcat.sql on the SQL Server 2000 box? This is needed if you are creating a linked server from SQL Server 2005 or higher back to a 2000 system.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 14, 2011 at 12:35 pm
Jeffrey I go t your msg about the time I was going to hit Post on this ....
I realized I had the remote server hilited when I opened and ran the new query. I closed it, hilited native server and opened new query. Pasted code and ran again. got a different message...
OLE DB provider "SQLNCLI10" for linked server "owdc" returned message "Unspecified error".
OLE DB provider "SQLNCLI10" for linked server "owdc" 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 2
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "owdc". The provider supports the interface, but returns a failure code when it is used.
the remote server is SQL2000 so it looks like it won't play with the SQL 10 provider.
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
April 14, 2011 at 12:45 pm
That indicates that you probably have not run the instcat.sql updates on the 2000 box. Again, this is required when creating a linked server from a 2005/2008 system to a 2000 system.
What this procedure does is updates the catalog information so the 2005/2008 systems can read them from the 2000 box.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 14, 2011 at 12:57 pm
this might be a stupid question, but I truly don't know. Can it be run while the DB is being used?
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
April 14, 2011 at 1:00 pm
Yes, it can but you should review the article from microsoft. I don't have the link for the article, but you should be able to find it through google.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 14, 2011 at 1:03 pm
Goin there now. Thanx Jeffrey!
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
April 14, 2011 at 2:57 pm
Does user (sa on 1st server) have public role on master DB on secend server?
-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
April 14, 2011 at 3:03 pm
Yes. sa is sysadmin on 2nd server with db owner and public membership for specified database.
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
April 14, 2011 at 3:07 pm
how can you tell which service packs are installed for SQL Server? I can see the version, but can't find SP info.
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
April 14, 2011 at 3:11 pm
found an interesting workaround for a query here...
but I want to do updates also.
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply