Linked Server From SQL 2005 64-bit To SQL 7

Nov 13, 2006

I've experienced the problem with creating a linked server from SQL 2005 64-bit to SQL 2000.

"The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".

I have resolved this by upgrading the system stored procs on the 2000 machine by running instcat.sql, documented in http://support.microsoft.com/default.aspx/kb/906954/en-us . Works great.

However, I'm now experiencing the same error when linking from SQL 2005 64-bit to SQL 7. I'm concerned about applying 2000 code onto a SQL 7 machine. Any ideas on how to handle this case?

Thanks

Paul

View 1 Replies


ADVERTISEMENT

Linked Server In SQL(2005) Job

Apr 20, 2006

I have a linked server to Sybase using MSDASQL. I can query the tables from the linked server using SQL Management Studio (windows login). When I put the same query into a job, it failed to work. The query is simple:

SELECT * from MyLinkedSvr.RemotDB.dbo.RemoteTable

The linked server setting include the login mapping, which maps my windows login and sa to the remote sybase login.

I'm using SQL 2005 9.0.1339 on Windows2000 Pro w/ SP4. SQL Agent starts as local system in the Services panel. Shared Memory/TCPIP/Named Pipes are enabled for Server/Client Network.

Thanks!

View 3 Replies View Related

Linked Server Setup - SQL 2005 RTM

Jan 21, 2006

<<cross posted from microsoft.public.sqlserver.server>>Environment: Windows 2003 Standard Edition SP1, SQL 2005 EE RTM (bothservers)I am getting the following messages when I run this query:select * from LINKEDSERVER.databasename.dbo.tablenameOLE DB provider "SQLNCLI" for linked server "LINKEDSERVER" returnedmessage "An error has occurred while establishing a connection to theserver. When connecting to SQL Server 2005, this failure may be causedby the fact that under the default settings SQL Server does not allowremote connections.".Msg 782, Level 16, State 1, Line 0SQL Network Interfaces: No credentials are available in the securitypackageOLE DB provider "SQLNCLI" for linked server "LINKEDSERVER" returnedmessage "Client unable to establish connection".Msg 782, Level 16, State 1, Line 0SQL Network Interfaces: No credentials are available in the securitypackageLINKEDSERVER is set up as a SQL Server linked server., with no loginmapping, and connections will be made using the login's currentsecurity context.Both servers allow local and remote connections via TCP/IP, and NamedPipes is disabled on both servers. Both servers are trusted fordelegation, and the service accounts for the MSSQLSERVER service onboth servers are also trusted for delegation.We are auditing successful and failed logins, and are not seeing anyactivity on the linked server.Any help would be appreciated.Randy

View 3 Replies View Related

SQL 2005 And SQL 7 - Need Linked Server * Nevermind, It Must Not Be Possible

Nov 16, 2007

I have a Win 2003 running SQL 2005 Workgroup on a hosted site. I have full access to all servers. I am trying to create a linked server to my old SQL 7 server on a Win 2000 server in another network. I can ping the Win 2000 server from the Win 2003 server. I can connect via VPN from the Win 2003 server to the other network firewall and create a linked server to an AS400 server at 192.168.1.50. On the internal network behind the firewall the Win 2000 server is at 192.168.2.133 and natted to 10.10.50.133 via a load balancer. I cannot ping the Win 2000 server over the VPN even if I add an entry to the hosts file.

The SQL 7 server is autostarted with agent and MSDTC autostarted under SQL/NT authority under the administrator account. I allow remote servers to connect via RPC.

I know just enough to be dangerous and have researched this to death. I need a little help with this. I keep getting "could not open a connection to SQL Server [53]" and have researched that to no end. On the SQL 2005 management studio to create a linked server I use Microsoft OLE DB provider for SQL Server and SQLNCLI. I can't determine the connection string so I leave that blank.

View 9 Replies View Related

Linked Server 2005 SQL Sent To Teradata

Sep 17, 2007

I am trying to set up the Linked 2005 Server to Teradata v2r5, and have got most of the way there. My problem is that using both methods (OPENQUERY and SELECT with 4-part naming string) seems to submit only the SELECT * part to Teradata with the rest of the SQL waiting until the data is all pulled back to SQL Server. This is a problem when working with big tables! It puts a huge strain on the network, and also runs me out of temp space in Teradata. Is there a way to get ALL the SQL passed to Teradata? I am looking to create a Report Model for Report Builder or Pro-Clarity. I have a dummy SQL Server 2005 database set up with views of the Teradata tables through the Linked Server. Any help would be greatly appreciated!

View 1 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 Between 2x SQL 2005 Instances

Mar 24, 2008



Hi,

I've been able to make a linked server to an access document as follows and it works perfectly:

EXECUTE sp_addlinkedserver 'North', 'OLE DB Provider for Jet',

'Microsoft.Jet.OLEDB.4.0','C:Practice FilesNorthwind.mdb'

SELECT * FROM North...Customers


I have 2x sql 2005 servers at home at different machines. I'm logging on both machines by using windows authentication. How can I make a linked server between them?

Thank you.

View 3 Replies View Related

This Is How You Configure A DB2 Linked Server In MS SQL 2005.

Jul 26, 2006


With Linked server you can access DB2 resources as if it is another object in your SQL Server.

You don€™t have to establish links from all nodes to the DB2 Server.

Connection from SQL server 2005 to DB2 Server is sufficient to all SQL server users to access DB2 resources.



1. Install MicroSoft OLE DB provider For DB2 in SQL server 2005.

2. configure as follows:
Start - - -> all programs - - - > MicroSoft OLE DB provider For DB2 - - - > Data Access Tool.
Right click on data sources - - -> New Data Source
In Data Source Wizard - - > Data Source plat form = DB2/MVS or any other appropriate plat form from drop down
Net work type = TCP/IP - - > Next
Address = IP address:xx.xx.xx.xx

Port Number: xxxxxxx - - -> Next
Initial Catelogue = SAMPLE
Package Collection = DB2INST1
Default schema = DB2INST1
Default Qualifier = Leave it blank - - -> NEXT
HOST CCSID = EBCDIC- U.S./Canada[37] or appropriate one
P.C Code Page = ANSI-Latin I[1252] or appropriate one
Process Binary as character = check the box - - -> NEXT
Interactive sign on - - - > select it
Provide login name and password.
Check "Allow Password saving" - - - -> NEXT
Check appropriate boxes like read only etc. - - -> NEXT
Click Connect tab - - -> make sure "Success ful" message is appearing
Example: - "Successfully connected to data source 'DB2-server'.
Server class: DB2
Server version: 8021.00.0000"
- - - -> NEXT
Provide a data source name.(Example: DB2-Server)
Check initialization string option - - - > NEXT
Review completed task list on the window and
Select "FINISH" to complete the work.
The data source which you have added just now(DB2-Server) will be appearing on the grid now.
right click on the data source name and make a note of the path where it has formed
right click on the data source name and click "Display connection string"
cut and paste this connection string on the lower window to note pad for future use.

Start MS SQL server Management studio
Add a fresh linked server in 'SQL Server - - > Server Objects - - -> Linked Server" as follows:
Click Mouse right button on linked server and select "New Linked Server"
linked server = Provide a meaning ful, fully identifiable name for Linked server(Example"DB2-Linux")
Provider = MicroSoft OLE DB provider For DB2
Product name = DB2OLEDB
Data source = IP address

Provider string = Paste the connection string pasted to note pad earlier.
Location = Leave it blank
Catalog = SAMPLE
Click on security on the left side pane
Provide an SQL Server login from the drop down
Do not select check impersonate
Provide DB2 username and password and remote user name and password.
Click OK to save.
You will find the name of liked server appearing below the object Linked Server(Example "DB2-LINUX")


Open a new query window and execute an open query
Example:
SELECT * FROM OPENQUERY("DB2-LINUX", 'SELECT * FROM SAMPLE.DB2INST1.EMPLOYEE')


In the Example "DB2-LINUX" is the name of the linked server
"SAMPLE.DB2INST1.EMPLOYEE" is the name of the table. Sample = name of the db
DB2INST1= name of schema to which the table belongs to
Employee = name of the table

Another Example would be
SELECT empno,FIRSTNME FROM OPENQUERY("DB2-LINUX", 'SELECT * FROM SAMPLE.DB2INST1.EMPLOYEE')


View 1 Replies View Related

Linked Server Error From SQL 2005 X64 To X32

Nov 13, 2007

I have a problem I have spent hours on already. I have found multiple posts, but not a solution to this problem. This is not a rights issue.

I have two SQL 2005 servers (I cannot upgrade to SP2 yet):
ServerA - ver 9.00.2153.00 (X64)
ServerB - ver 9.00.2153.00 (Intel X86)


I create a linked server on ServerB pointed to ServerA, uses 'sa' for all logins, works perfectly.
I create a linked server on ServerA pointed to ServerB, uses 'sa' for all logins, FAILS.

Run on ServerA:

SELECT *
FROM OPENQUERY([ServerB], 'SELECT [field1] FROM database.dbo.tablea')

Returns:

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "ServerB" supplied inconsistent metadata for a column. The column "field1" (compile-time ordinal 1) of object "SELECT [field1] FROM database.dbo.tablea" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time.



Run on ServerA:

SELECT [field1] FROM [ServerB].database.dbo.tablea

Returns:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "ServerB" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "ServerB". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema


Any bright ideas?

View 3 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

Problem Accessing A SQL Server 2000 Linked Server From SQL Server 2005

May 4, 2007

Hi
I have created a linked server from SQL Server 2005 (SP 1) to SQL Service 2000 (SP 4) with a sql server login that is available on both servers but with different passwords and permissions.



I am getting the following error while accessing the linked server in management studio based on the scenario given below ;



------ Error Message Starts
OLE DB provider "SQLNCLI" for linked server "(SQL Server 2000 instance name)" 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 'abc'.
------ Error Message Ends


Consider login name is abc.
Now this login abc has sysadmin rights on sql server 2005.
The same login abc has only db_datareader rights on sql server 2000 on just one database and is not associated with any fixed server role.

I have configured the linked server using the following options;
1. I have tried impersonating login from SQL Server 2005 to SQL Server 2000 .
2. I have also tried specifying remote login / password option.


Anyone having any idea, would be of great help.
Regards,
Salman Shehbaz.

View 3 Replies View Related

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

Instructions For Setting Up A Sybase ASE Server As A SQL Server 2005 Linked Server?

Dec 28, 2005

I have a Sybase Adaptive Server Enterprise server which I need to set up as a linked server in SQL Server 2005.  The Sybase server is version 12.5.2, and the Sybase ODBC driver version is 4.20.00.67.  I have already installed the Sybase client software on the server.


I also created a SystemDSN on the SQL Server to connect to the Sybase server.  I tested the connection and it was able to connect.

I ran the following code to create the linked server:

<code>

EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName', @srvproduct=N'Sybase', @provider=N'MSDASQL', @datasrc=N'Sybase System DSN', @provstr=N'"Provider=Sybase.ASEOLEDBProvider;Server Name=servername,5000;Initial Catalog=databasename;User Id=username;Password=password"'

</code>

I then ran sp_tables_ex to make sure I could view the tables in the Sybase database.  Here is the error message I get:

<code>

OLE DB provider "MSDASQL" for linked server "LinkedServerName" returned message "[DataDirect][ODBC Sybase Wire Protocol driver]Error parsing connect string at offset 13. ".

Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41

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

</code>

Any ideas what is happening here?

View 10 Replies View Related

Linked Server 2000 To 2005: Error 17 Sql Server Does Not Exist Or Access Denined.

Aug 30, 2006

I'm trying to link SQL Server 2000 sp3 to SQL 2005 64 bit. I keep getting Error 17 sql server does not exist or access denined. I tried adding remote user mapping and chaning the linked server properties to "Be made using this security context" without any success.
Any help is appreciated.

View 4 Replies View Related

Running A Distributed Query Against A Loopback Linked Server In SQL Server 2005 Is Not Supported

Aug 27, 2007

I receive the following error message when I run a distributed query against a loopback linked server in SQL Server 2005:
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.

To resolve this problem, I was told that running a distributed query against a loopback linked server is not supported in SQL Server 2005. And I am suggested to use a remote server definition (sp_addserver) instead of a linked server definition to resolve this problem. (Although this is only a temporary resolution, which will deprecate in Katmai)

However, I run into another problem when I use the remote server definition. I receive the following error message:
Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'ServerNameSQL2005' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name.

Could anyone please help me out?
(I include the reproduce steps for the first error message, followed by my resolution that generates the second error message)
======
Reproduce steps for the first error message
======


On the ComputerAInstanceA instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseA
GO
USE DatabaseA
GO
CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
GO
INSERT INTO TestTable VALUES (1, 'Hello World')
GO

On the ComputerBInstanceB instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseB
GO
USE DatabaseB
GO
CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
GO

On the ComputerAInstanceA instance, create a linked server that links to the ComputerBInstanceB instance. Assume the name of the linked server is LNK_ServerB.

On the ComputerBInstanceB instance, create a linked server that links to the ComputerAInstanceA instance. Assume the name of the linked server is LNK_ServerA.

On the ComputerBInstanceB instance, run the following statement:
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
END
GO

On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXEC LNK_ServerB.DatabaseB.dbo.InsertA
GO
Then I receive the first error message.

=======
My resolution that generates the second error message
=======


On the ComputerBInstanceB instance, run the following statement:
sp_addserver 'ComputerAInstanceA'
GO
sp_serveroption 'ComputerAInstanceA', 'Data Access', 'TRUE'
GO
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * FROM [ComputerAInstanceA].DatabaseA.dbo.TestTable
END
GO

On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXECUTE [ComputerBInstanceB].[DatabaseB].[dbo].[InsertA]
GO
Then I receive the second error message.

View 1 Replies View Related

Problems Reading Data From Linked Server To Excel In SQL Server 2005

Oct 4, 2007

I have an Excel sheet that is dynamically updated (through DDE) and I want to import this data to a table in SQL Server 2005. Using SQL Server Management Studio to configure an Excel data source as a linked server.
(http://support.microsoft.com/kb/306397/EN-US/)

Following the first 5 steps should let me acces the table (but I cannot view the data in SQL Server 2005). However, I could not find how to export the data into an existing table. Does anyone know how or can give a pointer to document describing how to do this?

View 8 Replies View Related

Importing Data From Oracle 10g To Sql Server 2005 Using Linked Server

Jul 30, 2007

Hi,

I am using Windows 2003 server and Sqlserver 2005 by the use of Linked server , I made a connection to Oracle 10g after that I am importing records from Oracle to sqlserver 2005. When I made tnsnames.ora in sql machine , it worked fine but when i am using tnsnames file from oracle server then i fiired importing procedure it returns below maintain error :

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Unspecified error".

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.".

Msg 7311, Level 16, State 2, Line 1

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


Please let me know.

Thanks

View 15 Replies View Related

SQL Server 2000 To SQL 2005 Linked Server - Query Problem

Jan 16, 2008

Hello

SQL 2000: 8.00.2187 x86, 8 way 700mhz, 6GB Ram
SQL 2005: 9.00.3042 IA64 2 Way Dual-Core 1.66Mhz 16 GB ram

Symptoms

Querys to the SQL 2005 box from SQL 2000 work but when the query is parameterised with non-literals (@variables) then the query run on the SQL 2005 box excludes any where clause causing the entire table to be returned to the SQL 200 box. When the query is parameterised using literal values the query is executed on SQL 2005 including the where clause.

At first I thought that the "Collation Compatible" setting was the culprit but setting this to 1 made no difference. Other SQL 2000 boxes work as expected and any queries from these using literal and non-literal parameters.

Please, any ideas?



Working

SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = 'value'

Not Working (correctly anyway!)


DECALRE @Value tinyint
SET @Value = 22
SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = @value

View 5 Replies View Related

Error Query Data Through Linked Server , SQL Server 2005

May 27, 2008

Hello,

I have a development and a production SQL server instance environment set up on 2 independent machines. Each machine is running Windows 2003 for an OS, while each server instance is version SQL Server 2005. On friday, I experienced difficulties querying one environment from the other through linked servers. I would get the error below:
.

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


The linked servers had been previously set up and had been running without any issues. Dropping and recreating the linked servers did not help at all, and all attempts to google the error led to accounts of either SQL Server 2005-SQL Server 2000 procedures compatibility or 64 bit - 32 bit compatibily related errors. Neither of the two were relevant as both my environment have the same technology, both hardware and software.

Mysteriously, the linked server worked this morning without any issue at all. One co-worker suggests gremlins are at work, while another figures that my set up had 'checked out for the long weekend'. Unfortunately, neither explanation is plausible, so my quest to find out what could have gone wrong, and hopefully put preventitive measures in place for the future goes on. Does anybody have any idea what the issue could have been?

Thanks,
Simba

View 1 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

Programmatically Set Linked Server Properties + SQL 2005 + RPC / RPC OUT

May 21, 2008



Hi Folks,

In SQL 2005, the linked servers are not automatically enabled for RPC / RPC OUT like they were in SQL 2000. I have set the properties for my linked server by using SQL Management Studio.

I right click on the Linked Server --> Properties --> Server Options

There are options like: Collation Compatible, Data Access, RPC, RPC OUT, Use Remote Collation

There is a drop down box to set each value to TRUE or FALSE. Setting them through management studio works great, however for the production environment I need to set these properties programmatically. Is there a stored procedure or registry setting that would allow my program to set these values for a given linked server?

Thank you.

View 3 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

Linked Server From SQL 2000 To 2005 Through A Firewall

Oct 16, 2007

Can anyone tell me if there's a recommended way to link from a SQL 2000 SP4 DB through a firewall to SQL 2005? I have tried with SQLOLEDB but this uses NETBios which is not secure. Or is there a way to tell it not to use NETBios?
SET @provdetail = 'DRIVER={SQL SERVER};SERVER=<IP address>;Trusted_connection=Yes'
EXEC Master..sp_addlinkedserver
@server = ,'<IP address>',
@srvproduct = '',
@provider = 'SQLOLEDB',
@provstr = @provdetail ,
@catalog = 'TestDB',
@datasrc ='<IP address>DB1'

View 1 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

SQLServer 2005, Linked Server, Authentication

Jun 20, 2007

Hi all!!!

I have the following problem:

I used JTDS to connect to SQL Server 2005 SP1(with Windows Authentication) from a Tomcat DataSource, using your usefull ntlmauth.dll, so I can use Users from Active Directory.
All is ok until I must call a Stored procedure that excecute queries from a linked server table.
I have the following exception message "Login failed for user 'NT AUTHORITYANONYMOUS LOGON'"

I make a lot of test, I make a Java main program invoking the sotred procedure that excecute queries on linked server using JTDS and then using Microsoft SQL Server 2005 JDBC Driver 1.2(April 2007)sqljdbc.jar and Ihave the same problem, but if we use the bridge JDBC-ODBC all works OK.


Please help!!

thanks

Lore

View 4 Replies View Related

Cannot Connect To 2000 From 2005 Via Linked Server

Dec 29, 2006

Interesting issue.. I think

I have 2 linked servers on my dev 2005 instance (sp1-win 2003). My 2005 instance shares the physical server with a 2000 instance. This linked server works fine. The second linked server is on a separate server running sql 2000 sp3. I cannot connect to this server at all. I can connect the other way -- from the 2000 to the 2005. I have tried all kinds of combinations of security and options but nothing works. The only resolutions I have found referenced installing Management Studio on the 2000 server or creating an ODBC connection from the 2005 instance. Any ideas???

Here is the error I get:

OLE DB provider "SQLNCLI" for linked server "ALLFLDB6" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "ALLFLDB6" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

Msg 10061, Level 16, State 1, Line 0

TCP Provider: No connection could be made because the target machine actively refused it.

View 4 Replies View Related

ODBC Problems In Linked Server In SQL Server 2005

Feb 19, 2008

Actually i have linked server in SQL 2000 with Provider:Microsoft OLEDB for ODBC which is working perfectly fine. I don't know the password for this linked server too.

Now i want to create same linked server with same provider in SQL server 2005 for Microsoft oledb for ODBC but they don't have this provider in 2005. How can i make it to work.ANy expert who knows this.

and how to use same password in linkedserver in 2005

View 12 Replies View Related

ISAM : Excel Linked Server In Sql Server 2005

Jun 7, 2007

Dear Friends,

I am getting error when trying to make excel file as the linked server in my sql server the details are as under:



os: windows xp and also tried on windows server 2003

server: sql server 2005 sp2

excel: office 2003 and also tried with .xls file of office 2007



tried to add the remote as well as file on the same computer as linked server, but error was:



could not find installable ISAM and error no was 7303.



Please help me up.

I got the idea to make it as linked server from microsoft article and followed the neat steps and got the errors.

Thanks,

View 1 Replies View Related

Linked Server Failure On SQL Server 2005 Cluster

Jan 14, 2008

Hi,

I created a linked server from SQL server 2005 (cluster) to SQL server 2000. When i try to execute the query from local server i get this error


OLE DB provider "SQLNCLI" for linked server "Rem_serv" 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 'rem_User'.


Please help



Thanks and regards

Helen

View 3 Replies View Related

Creating A Linked Server To AS400 In SQL Server 2005

May 24, 2006

I am trying to create a linked server in SQL Server 2005 to show tables in our AS400. I made the connection, however, the tables are not showing up under the Linked Server name.

How do you get the linked tables to display in the Linked Server folder?

David



View 4 Replies View Related

SQL Server 2005 Creating Linked Server With DB2 On ISeries

Dec 18, 2006

Hi,

I am using SQL Express 2005 sp1

When I created a linked Server to the DB2 on iseries ( V4R5) , the SELECT statement fetches me only the first record and the following message appears.

OLE DB provider "MSDASQL" for linked server "DB2TEST" returned message "[IBM][Client Access Express ODBC Driver (32-bit)]Driver not capable.".

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "DB2TEST".

I am using 'MSDASQL' which refers to the DSN setup on my machine using the ODBC "Client Access Driver 32-bit"

Strangely when I used the same DSN in SQL 2000 within the DTS package, I was able to connect and import data and worked like a charm.

Can anyone advice me where I am going wrong.

Thanks in advance

View 1 Replies View Related

Linked Server From 2005 To 2000 Trans Failure

Feb 9, 2007

I'm trying to connect to another slq server db through a linked server (and synonyms) but I get the following error.

Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. ,Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "BURT". A nested transaction was required because the XACT_ABORT option was set to OFF. , at Westfalia.TDMN.TxObjects.TxBurtOCIInterface.BurtOCIPalletChangedRecords_Get() , --- End of inner exception stack

I've set up MSDTC and executed the procedures noted in MS's article ID 873160. I am using 2005, but the Burt server is 2000. I can run the stored procedure from my server, which queries theirs. But when I execute the sp from a service, I get the error. Any help would be greatly appreciated.

View 1 Replies View Related

How To Migrate Linked Server Configuration SQL 2000 And 2005?

Mar 14, 2007

I plan to migrate a SQL 2000 instance with everything in it to another SQL2000 server, and eventually in the future to SQL 2005.

I've pretty much figured out how to migrate everything execept the linked server configuration (there're many linked servers configured in the old server with different security credentials). I'm wondering if there's way I can save the linked server configuration and restore it to the other server.

Thanks much.

Ed

View 4 Replies View Related







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