Test Linked Server Connection SQL 2000

Feb 27, 2007

I€™m trying to resolve an issue and wonder if anybody has ever run into and possibly had a resolution for.

I wrote a custom conflict resolvers to manage data at the column level. What I needed to do to achieve this was to use a remote query to the mobile subscriber that raised the conflict. I€™m trying to put in a rule that if there are any issues connecting to the subscriber I want to use the default conflict resolver and move forward. Here is my issue, it seems there is no way I can test a connection without getting a critical error that I can€™t seem to trap. Example

I was trying to do

Select * from [linkedserver].[database].[dbo].[incident]
If @@error <> 0
use default conflict resolver

What I did to test this was I changed the linked server authentication to an incorrect password to get the following error that there is no way to trap.

Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'sa'.

Does anybody have an idea how to trap this? In SQL 2005 there is a stored procedure

sp_testlinkedserver to handle this but I need this to work in SQL 2000 also, it seems many people have ran into this issue on the web and I tried to get the one where sqldmo is used to test connection but that will not work since I wont always know the password. I would like to just check the @@error value after trying to run a remote query.

Thank you,
Pauly C

View 3 Replies


ADVERTISEMENT

Connect From A SQL Server 2005 Db To A SQL Server 2000 Db, Without Linked Server Connection

Apr 18, 2007

Can I connect from a SQL Server 2005 database to a SQL Server 2000 database, without establishing a linked server connection.



I need to fire a SELECT query on a SQL Server 2000 database, but don't want to add it as a linked server. Is there any way I can do this or its not possible??

View 1 Replies View Related

Oledbconnection In Vb.net 2005 And Sql Server Express Test Connection

Jan 18, 2008

Hey everyone,

I was had just finished creating the architecture for a test databse using sql express 2005 and have been able to get the browser and sql service started just fine. However, When I try to create an oledbconnection or oledbadapter to the database using the Microsoft SQL Server engines I get the following error: I have tried using a direct path to the database in the server name and the computer's name itself. Everything is on the local machine and I have not started remotely connecting. [DBNETLIB][ConnectionOpen (Connect()).] SQL Server does not exist or access denied. I am using windows authentication and have also specified the direct patht to the database in the attach database file: Thanks for the help.

Shullaymahl

View 1 Replies View Related

SQL 2000 Test Server Database Recovery Bombed

Feb 3, 2008

I am a SQL lightweight and I tried to update the data on the test SQL server with the data on the production server and now the test SQL server won't start.

I was trying to move a sql database from our production server to our test server and things got mucked up. I think where I went wrong is that I tried to backup the database on the Production server and then restore it to the test server. I think I should have backup the production server and then detached the databases from the test server and then attached the backups. I got in a hurry and had an attitude that SQL is so good that if I mess up, I can recover.

These are the databases I recovered to test server:

Builder
CSales
CIB
Master
Model
MSDBdata

Now SQL server won€™t start and I get the error message that the database Model is in the middle of being restored. We are on SQL 2000. I tried to start from a command line with the €“f switch and it still can€™t come up.

Should I reinstall SQL and then attach the databases?

Do I need to bring over the Master, Model and MSDBdata data bases from the production servers.

View 7 Replies View Related

Sql Server 2000 Linked Server To Access 97/2000 Incompatability

Jul 20, 2005

We are experiencing a problem with Sql Server 2000 linking to anAccess 97 file. We have two machines that link to this .mdb file, andwe recently upgraded one to newer hardware, SP3a, MDAC 2.8, etc. Thelink on this upgraded machine no longer works, giving this message:Server: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.[OLE/DB provider returned message: Cannot open a database created witha previous version of your application.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'IDBInitialize::Initialize returned 0x80004005: ].The link on the older machine still works. We decided to tryconverting a copy of the file to Access 2000 to see if the newerpatches/drivers/whatever no longer supported 97. We set up a link onboth machines to this file, and they both work. However, on theupgraded machine, the following error is receievedServer: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.[OLE/DB provider returned message: System resource exceeded.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'ICommandText::Execute returned 0x80004005: ].when making 1-3 connections to the the linked server, while the oldermachine supports at least 7 simultaneous queries connecting to thelinked server and still hasn't produced that error.Does anyone have any idea if there is a known issue with linking toAccess 97/2000 files under MDAC 2.8, Jet 4.0, etc? Any light anyonecan shine on this subject would be greatly appreciated.

View 1 Replies View Related

Login Failed For User 'test'. The User Is Not Associated With A Trusted SQL Server Connection.

Mar 18, 2008

Hi all,
I am new in asp.net. In my web.config file, I wrote the connection strings as below and then I got the above error. I already changed SQL server Authentication mode to "SQL server and Windows". Any idea? Thanks in advance.
<connectionStrings>
<remove name="LocalSqlServer" />
<add name="LocalSqlServer" connectionString="Data Source=xxx;Initial Catalog=mydb;User ID=test;Password=test;Persist Security Info=False;"
providerName="System.Data.SqlClient" />
</connectionStrings>

View 1 Replies View Related

How To Set Linked Server Connection To Db2?

Oct 1, 2007

What is the correct way to set a linked server pointing to a DB2 database?

I've been trying for hours with "Microsoft OLE DB Provider for ODBC Drivers" [MSDASQL] and "IBM OLE DB Provider for DB2" [DB2OLEDB] and I still cant setup a correct connection.

I already have a ODBC connection pointing succesfully to my DB2 database, but I can't make MSSQL to use it.

Should I created the connection using sp_addlinkedserver OR the "new Linked Server" under "linked Servers" on Management Studio?

Any help is welcome.

[]s

View 2 Replies View Related

Test Connection Failed Because Of Error Initializing Provider. The HTTP Server Returned The Following Error : Not Found

May 26, 2008



Hi All,

I am using windows 2003 server and i have installed SSAS 2005 and configured http request for AS 2005 with this below url : http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx. I had tried all the possiblities given in this url. But i am getting like "Test connection failed because of error initializing provider. The HTTP Server returned the following error : Not found" when i create udl file. Moreover i have installed MSOLAP 3.0 and OLAP 9.0 provider and MSXML 6.0 Parser.

Can you anyone please provide solution for this?

Thanks in advance,
Anand Rajagopal

View 1 Replies View Related

Testing Linked Server Connection

Jan 16, 2001

I have a server with several linked servers. Before I execute sql against any of these linked servers I want to check to make sure the connection is active.

I created a small stored procedure that takes the server name. I am trying to get it to run a simple select statement. If the select statement runs without errors (meaning the linked server is active) I want to return a value for success. If the select statement fails I want to return a value for a failure.

The problem is that I am having trouble with the error. When I shut down the linked server and run the select statement I get the following error returned:

Server: Msg 11, Level 16, State 1, Line 1
General network error. Check your network documentation.

The stored procedure I have only returns this message and does not send the return value that I set. How do I get my procedure to return a failure value instead of the following error above? Is there a better way for me to check for this type of error?

Thanks

View 1 Replies View Related

Linked Server Connection To Sybase

Mar 11, 2004

Has anyone had problems using an OLEDB linked server connection to Sybase ASE 12.5? I'm having major performance problems when I use string criteria in the where clause. Queries that return 1 row and execute in less than a second using a native Sybase connection take 40 seconds to run using the linked server OLEDB connection. If I use ints in the where clause performance is almost exactly the same between native and linked connections.

Any ideas???

View 9 Replies View Related

SSMS Linked Server Connection For DB2

Mar 19, 2008

I am trying to establish a OLEDB connection for a db2 server from SSMS adn some one tell me the settings. I haver tried different options but failed.
This is my connection string from SSIS
"Data Source=DB2W;User ID=myid;Provider=IBMDADB2.1;Persist Security Info=True;"

I gave
Linked Server: DB2W
Provider: IBM OLEDB provider for DB2
Product name: ???
DataSource: ???
Provider string:???
Location: ???

View 3 Replies View Related

Is It Possible To Open A Connection To The Linked Server In C#

Jan 17, 2008



Hi Gurus,

In my code, I need to update the table on the linked server according to the XML input. A table variable is used to store the value from XML.

But the collation of the linked database doesn't match with the default collation used by the table variable, so the table join fails.

I am thinking about to open the connection to the linked server directly, and use the target database. So the table variable collation should be as same as the target database collation.

Is it possible to open the connection to the linked server in c# ?


Thanks and best regards,

Jennifer Zhao

View 5 Replies View Related

Linked Server Connection String ??

Apr 22, 2008



What is the connection string for access a database of a linked server in sqlserver

Thanks

I add a linked server (another sqlserver server2) to my sqlserver(server1) , I need make a sqlconnection to the linked server connecting my sqlserver (server1)

View 7 Replies View Related

How To Test Connection In DTS...

Feb 27, 2008



Hi All

I have a Job which having a Step "Clear_Report" and Step having
" DTSRun /S "CrosconnectSrv" /U "dt_Fee" /P "res@1i2" /N "Report_Clear" /A "Our_camp_suit":"7"="1" /W "R" "

and it throwing an Error

DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE)
Error string: Invalid connection string attribute
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0

Error Detail Records:

Error: -2147220482 (800403FE); Provider Error: 0 (0)
Error string: Error Code: 0
Error Source= Microsoft OLE DB Provider for SQL Server
Error Description: Invalid connection string attribute

Error on Line 52

Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500


Error: -2147217843 (80040E4D); Provider Error: 18456 (4818)
Error string: Login failed for user 'dt_Fee'.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0


Error: -2147217843 (80040E4D); Provider Error: 0 (0)
Error string: Invalid connection string attribute
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0

DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun: Package execution complete.


By seeing we came to know this is User problem,But It is working till yester day....
now I want "Test the connection externally from Query Analyzer and I want find out the what was the problem arised.

View 4 Replies View Related

Establishing An Access Linked Server Connection On SS7

May 11, 2000

Hi:

No matter how I configure my link going thru EM I get a 7399 or 7303 error. First I tried to link to an Access 2000 db on a Windows 98 station. Next I installed Access 2000 on the test server with SS7, SP 2 (Windows NT 4, SP 5) and still the same errors. Is it possible to set up Access 2000 as a linked server on SS7? Can you give me the brainless man's walk-thru so I can get over this hump?

Thanks in advance,
Gary

View 3 Replies View Related

Linked Server To Pervasive 8 - Connection Error

Aug 7, 2006

Hello,

I am currently migrating from SQL 7.0 to SQL2005. So far everything is going well except for one thing, my linked server to a Pervasive 8.6 server database. I've done the same configurations as with the SQL 7.0 server to create the linked server. I've created an Pervasive ODBC Client DSN system connection on the server called PervasiveDB pointing to the DataDB catalog, using TCP/IP as transport and the server name "PervasiveDB" as the server address. From this point, on the SQL2005 server, the DSN connection is successfull and I can see the different catalog names of the PervasiveDB server. When I create the Linked server on the SQL2005, I create exactly as done on the SQL7.0 which is :

SQL7.0 server : OS is Windows 2000 all service packs applied for both OS and SQL7.0

SQL2005 server : OS is Windows2003 standard edition all service packs applied for both OS and SQL2005.

Linked server name : TestPervasive

Provider : Microsoft OLE DB Provider for ODBC Drivers

Product name: Pervasive

Data source: PervasiveDB

Location: PervasiveDB

Catalog : DataDB

I get the following error when I try to query the data to test if the linked server I just created is successful:

query : SELECT * FROM testpervasive.DataDB..schedule

error message:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "MSDASQL" for linked server "TestPervasive" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "TestPervasive".

Anybody can help me on this one? I would greatly appreciate it !!

View 3 Replies View Related

SQL 2005 Linked Server Connection Issues

Sep 24, 2007

Hi,


I am trying to connect to a sql 2005 SP2 windows auth server through linked server from a sql 2005 SP2 mixed mode. I am using the "use current users credentials" option for authentication. I am getting the error below. Any thoughts to why this is happening would be appreciated.

TITLE: Microsoft SQL Server Management Studio
------------------------------
"The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?"
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)

View 4 Replies View Related

How Can I Test A Connection String?

Jul 21, 2005

I'm trying to convert an application from MSDE to SQL2K.  I am able execute the MSDE script to populate the SQL database and I've modified the .config file to point to the new db but it appears as though the web app is not connecting.Is there a way I can test the connection string using Visual Studio or WebMatrix?Thanks for helping!Scott 

View 1 Replies View Related

Test Connection Problem

Apr 13, 2007

I click on ADD DATA SOURCE

select the database
then Test connection button, then it hurls at me:

Unable to open the physical file " file path name". Operating system error 32: " the file is being
used by another process)

an attempt to attach an auto-named database for the file d:Microsoft SQL server 2005MSSQL.1MSSQLDatasampleDB.mdf has failed.

but when i try with Adventure works db, Test connection succeeded!

View 1 Replies View Related

Test SQL Connection With Telnet

Aug 23, 2007

I have just been able to configure remote connection on SQL 2005. I can also access the AdventureWorks database remotely via ODBC on port 1433. The install directions of a some software instruct me to do telnet <hostname> 1433 to check for connectivity. It doesn't work! I gave my instance the name RM and I also tried telnet <hostname>RM 1433 but again failed.

When I try just telnet <hostname> it works.

Can anyone help?

View 3 Replies View Related

Linked Server (SQL 2000 To SQL 6.5)

Jan 4, 2004

Hi,

I am trying to setup up a linked server using 'sp_addlinkedserver'. The host server is a SQL 2000 and the remote server is SQL 6.5. Both the servers are on the same domain.

When the system stored procedure is run, enterprise manager shows the name of the linked server but there are no tables.

I get error 'SQL Server does not exist or access denied' when trying to run query against a database on the remote server.

Does SQL server 6.5 support linked servers?

Please help,

DPKA

View 3 Replies View Related

OLE DB Connection Error; Provider Becomes Unavailable To Linked Server

Apr 24, 2008

We have a SQL2005 server install running on a Win2003 Server. It has a linked server to iHistorian using the iHOLEDB provider for iHistorian. Randomly and after a few execution to this provider, we start getting errors

Msg 7303, Level 16, State 1, Line 2

Cannot initialize the data source object of OLE DB provider "IhOLEDB.iHistorian" for linked server "fspheafhs1".I created a data source in VS2005 for BI on the SQL server using the iholedb provider. This was successful. However, all queries or OPENQUERY statements to the linked server fail. This seems to continue until SQL Server is restarted. Does anyone know how to reset a provider in SQL Server or tell if the SQL Server is having trouble with the provider? There are no message in the SQL Error Log. Again the provider continues to work outside of SQL Server, but no longer can be accessed from within SQL Server. Thanks in advance.

View 3 Replies View Related

Test Connection Successful, Yet No Data

May 18, 2007

I run XP Professional on a home machine. I recently installed VB 2003 with MSDE. When I go to the server explorer window, right click to add a connection, the Data Link properties dialog box pops up as it should. I select the name of home server, indicate integrated security, and select a sample database such as model or master from the list. The test connection is successful and clicking OK causes the connection to appear in the Server Explorer window. Yet when I go to open the connection, there is no data. The folders for Tables, Stored Procedures and so forth appear but they are empty. I have another 2000 database that a friend sent me. If I try to indicate in the Data Link propetries dialog box that I would like to attach this database, I get the same symptom. Test connection successful, yet when I create and open this connection, the folders are empty. I am a newbie to ADO .NET and I am not sure where to begin. Can someone help?

View 3 Replies View Related

Sql Server 2000 - Linked Servers

Jan 23, 2001

I'm working with SQL Server 2000 installed in Windows 2000. When i tried to add the 'servername' as a linked server , i got the following message:

" Error: 15038. The Server 'servername' already exists."

When i execute "sp_linkedservers" , i could see the name "servername" in the list. But before doing this i had Restored the "MSDB" database on this server. Should i use "sp_droplinkedservers" and add them again.

Any comments are most welcome.

Thanks, Santha.

View 3 Replies View Related

Linked Server SQL 2005 To 2000

May 7, 2007

I have database in 2000 which needs to be access from SQL 2005 via linked server ? and vice - versa ?

How do we do that ?



we run select statement we get following error message.

Error 4064 : cannot open user defulat database : login failed.



We are using SQL 2000/sp4

SQL 2005/sp2



Please advice.

Thank you

View 1 Replies View Related

Linked Server From Sql 2000 To Sql 2005

Jan 15, 2007

I'm having issue in connecting to sql 2005 from sql 2000 via linked server.

Please advice what I should do to overcome the problem.

Thanks.



-EC

View 4 Replies View Related

Linked Server W/ Text Field Gets Connection Broken Error

Sep 5, 2006

We have a stored proc on Server B called:

my_sp_server_b it takes 1 parameter a text field as a parameter, with default set to NULL

this proc calls:

my_sp_server_a through a linked server (which happens to be the same server, different DB), it has two parameters: my_id int, my_text text w/ my_text having a default set to NULL

This second stored procedure just selects back an ID that is passed to it (to keep things simple).

If we pass any string value to my_sp_server_b we get the appropriate hardcoded ID passed to my_sp_server_a. If we pass NULL to my_sp_server_b we get the following error:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.

Connection Broken

If we remove the linked server, and just reference my_sp_server_a via the scoped DB, we do not get an error. If we change the data type in both procs to varchar(50) we do not get an error. If we change the data type to nText we still get an error. If we put IF logic into stored procedure: my_sp_server_b to check for NULL in the input parameter and if it true then to pass NULL explicitly to my_sp_server_a we do not get an error.

It seems to be a combination of using a linked server and trying to pass a text (or nText variable) with a NULL value to stored procedure. Sometimes the error changes based on which scenario I described above - but we consistantly receive an error unless we do some of the workarounds described above.

Any ideas?

View 2 Replies View Related

Oracle Linked Server Connection Error After Upgrading To SQL 2005

Mar 14, 2007

Hello all.

I upgraded a SQL 2000 installation to SQL 2005 this past weekend. Minimal issues overall, but one that did crop up relates to a linked server to an Oracle database. The linked server worked before the upgrade, and stopped working immediately after.

We're running on Windows 2003 x64, latest service pack. The linked server is set up using the MSDAORA provider. The error I'm getting post-upgrade is:

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

OLE DB provider "MSDAORA" for linked server "PROD" returned message "ORA-06413: Connection not open.".

Msg 7303, Level 16, State 1, Procedure RDM_GET_REP_LIST_SP, Line 12

Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "PROD".

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

A tnsping from the server works fine. I've also tried recreating the linked server. Any help would be appreciated. Let me know what other information would be useful.

Thanks,

Adam

View 3 Replies View Related

Named Pipe Provider/Linked Server Connection Error

Aug 21, 2007

Hello, everyone!

I'm currently having an issue with a linked server. Here's the surrounding information:

A) I have a clustered SQL Server 2005 Instance (A) and a SQL Server 2000 instance (B).
B) There is a linked server on A to B. When I set it up, I did run the fix to ensure A could talk to B (There was an issue with communication between 2005 and 2000 servers). It has been there since I installed A, and has worked fine.. Until last week.
C) This linked server uses static credentials to connect to B.
D) Named Pipes are enabled on both servers to listen to both connections on both A and B in cliconfg. So A has a named pipe listening for B, and B has a named pipe listening for A.
E) A has a view that looks at a table on B - It's a table view, very simple, just pulls in all the data from the table on B.


So, I go to do a select statement from the view, connected as a user other then 'sa'. I then get the error:

Named Pipes Provider: Could not open a connection to SQL Server Linked Server
Error Source: ncli Client (Paraphrasing, didn't copy that down, but it was the ODBC connector)

Well, that's odd. It's been working fine for months now..

I then go and connect as sa on A to query B. It works! Mind you, absolutely NO QUERY from A to B will run again until I run SOME kind of query has been run as sa on A.

Mind you, this is not a credential issue. Every user who connects to the linked server to B uses a stored credential that is DBO to the database on B. The same User Name and Password exists on both A and B.

Also of note, I check the activity monitor. There's a process that is "dormant" every time I run a query against the view. The details of this connection are:

sp_reset_connection;1

My question is, why is it resetting the connection on B when being queried from A? Why is it "all of a sudden" a problem? Are there any changes that coudl ahve been made that would cause this?

Any help with this confusing issue would be appreciated.

Thanks!

View 1 Replies View Related

Test MAPI Connection (Definitive Description)

Feb 9, 2005

(Isn't this fun? Type MAPI and SQL into Google and it almost tilts!)

Here is the situation:
1- When SQL Agent starts, it establishes a MAPI session. For some bizarre design reason, it keeps that session open as long as it is running, rather than re-opening the session when it has mail to send. (this can be days, weeks, or months)
2- Frequently, Exchange servers are clustered. When the virtual server moves to a different physical server in the cluster, - or the Exchange server is restarted for whatever reason - the (above) MAPI session of the SQL Agent gets jammed.
3- SQL Agent tries, and fails to send an E-mail saying (for instance) that a database dump has completed (etc.)
4- User posts a question to some forum, asking for help again.

My solution: I created a job to run on a daily basis to restart the SQL Agent, and re-establish the MAPI session. Voila!

The problem: Now we (sometimes) get the message:
[241] Startup error: Unable to initialize error reporting system (reason: The EventLog service has not been started)
This is incorrect - the service was never stopped!

*sigh* No hits in Microsoft or Google - Back to the drawing board.

SO: Can anyone think of a way to *test* the MAPI connection in a job step (without actually sending a message), so that the agent restart job is only executed if the MAPI connection is failing?

View 2 Replies View Related

Linked Server&txt In SQL 2000 And SQL 2005 Issues

Mar 7, 2008

Hi Guys,

I am trying to Query text file into SQL Server 2005 using linked server.
In SQL Server 2000 here is what I have.
Linked server configured to use following parameters
Product Name : Customer_file
Provider Name: Customer_File ( System DSN)

Here is basic file

0000003700 0000XXXXXX XX14 61J 1154-06-249
0000008600 0000XXXX11 YY17 41F 1144-12-309
0000010700 00023232323233 XX48 9DY 1145-10-299
0000015700 00001212121212 OX1 5RW 1163-08-079
0000026600 00001233333 YY1 31S 1150-12-111
0000027600 00001212121 YY15 8HU 1176-04-0891
0000038700 0000H13344 B120 8RF 1157-05-211

Now when I EXECUTE following command.
select * from openquery (TEST, 'select f1,f2,f3,f4,f5 from t#txt');
I normally get formatted output in columns..

As soon as I setup the same Linked server in SQL Server 2005, and follow same process I get all data in one column with first line as header.

I am basically concerting old import process into new one..so trying to figure out what i am missing?

Anay help?

THanks









SKR

View 1 Replies View Related

SQL 2000 Linked Server Hangs Due To Trigger

Jul 23, 2005

Hi -We have two SQL 2000 Servers. We have the linked server setup and wecan perform updates and inserts between the databases. But when we adda trigger and insert something into a table, the database hangs. Thereare NO processes blocking or being block in either database. This ONLYoccurs when we have one OS as Windows 2000 Server and the other OS asWindows 2003 Server. This problem does not occur when both servers areWindows 2000. Has anyone ran into anything similar to this???*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 2 Replies View Related

Another 2005 X64 To 2000 X32 Linked Server Problem

Aug 31, 2007

Hello,
I have been searching the archives for information on problems that occur with creating a linked server to SQL Server 2000 from SQL Server 2005 x64, but the problem I am having seems to be slightly different.

I was able to create the linked server from the 2005 server this way;

Exec sp_addlinkedserver
@Server = '2kServer',
@srvproduct = 'SQL Server'

I was able to add the login this way;

Exec sp_addlinkedsrvlogin
@rmtsrvname = '2kServer',
@useself='False',
@locallogin='domaincdun2',
@rmtuser='domaincdun2',
@rmtpassword='pswd'

I receive 'Command(s) completed successfully' for both. When I attempt to run either of the following queries from a connection to the 2005 server in Management Studio;

select top 100 * from 2kServer.database.dbo.table
select * from Openquery(2kServer,'select top 10 * from database.dbo.table')

I get the following error

OLE DB provider "SQLNCLI" for linked server "2kServer" 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 18456, Level 14, State 1, Line 0
Login failed for user 'domaincdun2'.

I've seen a couple of ideas on this. One states the following;
***************************************
When running 4 part reference query like this:
select * from sql2000.mybase.dbo.mytable

SQL Server 2005 x64 runs the following query on remote SQL2000 server:
exec [mybase]..sp_tables_info_rowset_64 N'mytable', N'dbo', NULL

Unfortunately there is no such a proc on SQL2k. However, sp_tables_info_rowset exists and does the same thing. The solution is to create wrapper on master database like this:

create procedure sp_tables_info_rowset_64
@table_name sysname,
@table_schema sysname = null,
@table_type nvarchar(255) = null
as
declare @Result int set @Result = 0
exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type

And then everything works fine. If you don't want to create "Microsoft like" objects on master database, use openquery instead of 4 part reference.
**************************************
I put this proc on the 2kServer and tried again, but I got the same error. I've also seen this:

I went into the Sql Server Configuration Manager, Sql Native Client
Configuration -> Protocols and disabled Shared Memory and made the TCP/IP
protocol #1 in order. Then just restarted the Sql Service and it all started
working!

I don't have access to the server to do this, but I wonder if it would solve the problem. I am a sys admin on both servers. The servers are in two different domains. Could this be a firewall issue?

Thank you for your help!

cdun2

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved