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
ADVERTISEMENT
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
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
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
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
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
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
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
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
May 8, 2006
I have been unable to get my linked server to work when running queries against it in SQL Server 2005.
Example SQL Statement on my SQL 2005 server that I'm trying to run:
select top 100 * from [linkedservername].databsename.dbo.tablename
Error Message:
Msg 7416, Level 16, State 1, Line 1
Access to the remote server is denied because no login-mapping exists.
Print screen of the security properties of my SQL 2005 Linked Server:
http://www.webfound.net/linkedserver.jpg
The user sqladmin definitely has the right permissions to the right databases on the target linked server.
View 1 Replies
View Related
Oct 19, 2007
I am using VS .net 2005 to develop a web application with sql server 2000, but the SQL servrer 2000 is setup on other computer,so, my web server is setup on A machine and SQL servr is setup on B machine.I got a problem when i try to connect to SQL server through the Web form as following error."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. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
I had try to setup the MDAC 2.8 on my Web Server, but i got a setup error sth like "MDAC 2.8 cannot install on your machine because it is not support your windows version.........."
Why i can't connect to SQL 2000 through the Web server???
It is a urgent case, Please help thx a lot.
View 3 Replies
View Related
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
Oct 12, 2007
Hi,Can I use SQL 2005 Management studio to connect to SQL 2000 server?
View 1 Replies
View Related
Sep 6, 2006
I recently upgraded from SQL Server 2000 to SQL Server 2005. I have Oracle databases I need to share data with. I was using the Heterogeneous Services (hsodbc) from Oracle and connected to SS 2000 fine. Since I've upgraded, I can't use hsodbc to connect to my SS 2005 database. I get the following error:
ORACLE GENERIC GATEWAY Log File Started at 2006-09-06T09:40:07
hoainit (3): ; hoainit Exited with retcode = 0.
hoalgon (7): ; hoalgon Entered. name = edw.
DB_ODBC_INTERFACE (2085): ; [Microsoft][SQL Native Client][SQL Server]Login
failed for user 'edw'. (SQL State: 28000; SQL Code: 18456)
DRV_InitTdp: DB_ODBC_INTERFACE (2085): ; [Microsoft][SQL Native Client][SQL
Server]Login failed for user 'edw'. (SQL State: 28000; SQL Code: 18456)
nvRETURN (F:WorkConnect_4_6_3srcdrvDRV_BIND.C 356): -2220
nvRETURN (F:WorkConnect_4_6_3srcavNAV_BIND.C 293): -2220
hoalgon (7): ; hoalgon Exited with retcode = 28500.
View 6 Replies
View Related
Jan 1, 2008
dear all,
in order to install a program i need to connect through SQL 2000 client to SQLserver2005.
it is possible?
View 8 Replies
View Related
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
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
Aug 24, 2006
I am trying to write some admin only procedures which will collect information to one of my development server from other production and development servers.
I have created linked servers to access these other servers on the development server. This development server is SQL Server 2000 EE. Other servers which I want to access are 2000 and 2005 (vaious editions)
E.g I have another development server called PRODTEST which is SQL Server 2005 and on the development server I have created a linked server pointing to PRODTEST called TESTLINKSRV. I want to access new object catalog view (as I do not want to use sysobjects)
When I run the following query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys].[objects]
I get following error,
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName=' TESTLINKSRV ', TableName='" DBNAME "."sys"."objects"'].
Msg 7314, Level 16, State 1, Line 1
OLE DB provider ' TESTLINKSRV ' does not contain table '"DBNAME"."sys"."objects"'. The table either does not exist or the current user does not have permissions on that table.
So I try this query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys.objects]
and I get following error
Msg 208, Level 16, State 1, Line 1
Invalid object name TESTLINKSRV.DBNAME.sys.objects'.
So bottom line is how do I access catalog views on a 2005 server from a 2000 server using linked server?
I hope someone understands what I am trying to achieve. Please let me know what is it that I am doing wrong.
Thank you
View 5 Replies
View Related
Apr 3, 2007
Hi all.I am working on asp.net 2.0 with back end as sqlserver 2000. but when trying to connect to the database i am getting the following error displayed 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.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL
Server) Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.SqlClient.SqlException: 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. (provider: Named Pipes Provider, error: 40 - Could not
open a connection to SQL Server)Source Error:
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of the
exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): 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. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +115 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +1093 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +1083 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +272 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +351 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +82 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +558 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +126 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +651 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +160 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +122 System.Data.SqlClient.SqlConnection.Open() +229 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +114 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +225 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +157 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +68 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +100 System.Web.UI.WebControls.Login.AuthenticateUsingMembershipProvider(AuthenticateEventArgs e) +100 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +113 System.Web.UI.WebControls.Login.AttemptLogin() +178 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +134 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +56 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +107 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +178 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +31 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +72 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3837
Version Information: Microsoft .NET Framework Version:2.0.50727.42;
ASP.NET Version:2.0.50727.42 can anyone help??????????? regardspravallika
View 12 Replies
View Related
May 11, 2007
i Update SQL Server 2005 SP1 to SQL Server 2005 SP2 but cannot connect SQL Server 2000 SP4. Please Help Me Please
===================================
Cannot connect to sia09-cxdb01cxdb01.
===================================
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. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476
------------------------------
Error Number: -1
Severity: 20
State: 0
------------------------------
Program Location:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectorThread()
View 3 Replies
View Related
Dec 8, 2007
Hi All!!!
In my Computer has MS SQL Server 2000(with default Instance) and now I Install MS SQL server 2005(with Instance name: SQL2005 ). Now I want to write Web Application to connect to the both databases (SQL Server 2000 and MS SQL Server 2005), How to Add connection string in my web.config to do it.
Help me ???
Thanks & Regards,
View 3 Replies
View Related
Mar 13, 2006
Hi,i have SQL 2000 and 2005 on same machine(with different intance names,of course), my laptop - XP with SP2. The 2005 works fine but i can'tconnect on SQL 2000. All the the SQL services are started.Any idea? Have i to reinstall 2000?Tks,Lourival
View 1 Replies
View Related
Apr 13, 2008
Dear Friends,
select name As Name, sAMAccountName As UserName, title As Title,
physicalDeliveryOfficeName As Office, telephoneNumber As PhNumber,
mobile As CellPhone, facsimileTelephoneNumber As FaxNumber, mail As Email,employeeid as emp_no from openquery
(
ADSI,'SELECT name, sAMAccountName, title, physicalDeliveryOfficeName, mobile, facsimileTelephoneNumber, telephonenumber, mail ,employeeid
FROM ''LDAP://fp1-srvr.sidf.gov.sa''
WHERE objectCategory = ''Person''AND objectClass = ''user''
')
on sql server 2000 it was working but when migration to sql server2005
I got the following error when trying to excute on query analyzer:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT name, sAMAccountName, title, physicalDeliveryOfficeName, mobile, facsimileTelephoneNumber, telephonenumber, mail ,employeeid
FROM 'LDAP://fp1-srvr.sidf.gov.sa'
WHERE objectCategory = 'Person'AND objectClass = 'user'
" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".
Could you please help me in that
View 4 Replies
View Related
Oct 16, 2007
Hi,
I have a SQL 2005 server (SQLA) on which I have created a linked server to a microsoft Access DB (LinkedAccessDB)
When I login (or remote desktop) directly into SQLA and run the following query, it works just fine:
select * from LinkedAccessDB...Table1
I also have SQL server 2005 installed on my workstation (WKSTA) and when I bring up Management Studio and connect to SQLA and try to run the previous query, I get the following error message:
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LinkedAccessDB".
I've researched articles for the better part of a day, and haven't found anything that would help. Any ideas would be much appreciated.
NOTE: My workstation, the SQL Server (SQLA) and the file server where the AccessDB is located are all in the same Domain.
View 3 Replies
View Related
Jul 26, 2005
I can't define a linked server in SQL Server 2005 x64 edition (to a SQLServer 2000 instance).
View 29 Replies
View Related
Sep 11, 2007
Morning ALL.
I have a utility server that I am running SS2K5 SP2 w/ the latest patches.
It has numerous Linked Server to both SS2K and SS2K5 servera already in place and working great.
I scripted out (numerous times) a Link Server create statement for a SS2K5 server that is working great and then changed the server name in the script to reflect the new server name and executed it.
It DID created the linked server BUT when it finished up it generated the following message:
================ ERROR TEXT BEGIN ======================
TITLE: Microsoft SQL Server Management Studio
------------------------------
"The test connection to the linked server failed."
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21".
OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (PreLoginHandshake()).".
OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.". (Microsoft SQL Server, Error: 7303)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
============= ERROR TEXT END ==============
Now when I try to open the Catalogs object under the newly created Linked Server, I get the following message each time I try to open it:
================ ERROR TEXT BEGIN ======================
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21". (Microsoft SQL Server, Error: 7303)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
============= ERROR TEXT END ==============
Here is the code that I used as a template (and which is from a SS2K5 server that is working fine)
=========== CODE BEGIN ========
/****** Object: LinkedServer [DC:AUS02DB19] Script Date: 09/11/2007 10:30:42 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'DC:AUS02DB19', @srvproduct=N'OLE DB for ODBC', @provider=N'MSDASQL', @provstr=N'DRIVER={SQL Server};SERVER=AUS02DB19.DomainName.com;UID=user;PWD=password;'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DC:AUS02DB19',@useself=N'False',@locallogin=NULL,@rmtuser=N'SQL_',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'use remote collation', @optvalue=N'true'
=========== CODE END ==========
Here is what the code looks like when I replaced the name of DB19 to DB21 globally throughout the script:
=========== CODE BEGIN ========
/****** Object: LinkedServer [DC:AUS02DB21] Script Date: 09/11/2007 10:30:42 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'DC:AUS02DB21', @srvproduct=N'OLE DB for ODBC', @provider=N'MSDASQL', @provstr=N'DRIVER={SQL Server};SERVER=AUS02DB21.DomainName.com;UID=user;PWD=password;'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DC:AUS02DB21',@useself=N'False',@locallogin=NULL,@rmtuser=N'SQL_',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'use remote collation', @optvalue=N'true'
=========== CODE END ==========
Now I have masked the real values in this post of the @provstr string for obvious reasons and the real Linked Server object has all the correct parameters set.
@provstr=N'DRIVER={SQL Server};SERVER=AUS02DB21.DomainName.com;UID=user;PWD=pass;'
SO ... what am I missing?
Thanks ALL
View 5 Replies
View Related
Mar 22, 2007
Hi
I have 2 severs. Server A uses sql 2005 and Server B uses sql 2000. I want to create sql sever registration on Server B connecting to Server A. But it saied 'To connect to this server you must use SQL Server Management Studio or SQL Server Management Objects (SMO)'
Any idea to solve it? thanks!
View 1 Replies
View Related
Dec 6, 2007
I have a dts package on SS2000 which loops through connections to a list of databases using activex:
set srv = createobject("SQLDMO.SQLServer")
srv.LoginSecure = True
set gpkg = DTSGlobalVariables.parent
set tserv = gpkg.GlobalVariables ("Server") -- where "Server" contains the name of an instance.
srv.Connect(tserv.value)
This works fine when connecting to ss2000 instances, but not with ss2005 instances. How can I connect to ss2005 instances too??? There must be a way! I don't have time to rewrite the package to IS.
Thanks,
Michael
View 1 Replies
View Related
Jun 2, 2007
Dear all
I have created linked server object between a sql 2000 and sql 2005 database. i have the latest mdacs installed and all the services enabled on both sides.
But when i try and run a sql statement on the sql 2000 side i get this error:
[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation.
If i try and run a swl statment on the sql server 2005 side the procedure works.
Has anyone experienced the same problem?Any advice?
Thank you so much for the help.
Dan
View 2 Replies
View Related
Jul 25, 2006
We have two sql servers using Windows authentication. One is sql 2000 the other sql 2005. When logged in as "sa", I can link these servers and run a SELECT distributed query without any problem.
But when one of our developers runs the same query (he is "dbo" of each database on each of the servers) he gets the error:
"OLE DB Provider "SQLNCLI" for linked server <Servername> returned message "Communication Link Failure"
...
Login failed for user '(null'). reason: Not associated with a trusted SQL Server connection"
Despite that, I, as "sa" equivalent, have no problem so it appears to be a permisisons issue. Any ideas what is going on here?
TIA,
Barkingdog
View 3 Replies
View Related
Jun 3, 2007
Dear all
I have created linked server object between a sql 2000 and sql 2005 database. i have the latest mdacs installed and all the services enabled on both sides.
But when i try and run a sql statement on the sql 2000 side i get this error:
[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation.
If i try and run a sql statment on the sql server 2005 side the procedure works.
Has anyone experienced the same problem?Any advice?
Thank you so much for the help.
Dan
View 4 Replies
View Related
Sep 17, 2015
Here are some details:
Domain = dm
Windows User = dmTestUser, member of sysadmin on SQLFL and SQLNY
SQL Server 2012 = SQLFL
SQL Server 2012 = SQLNY
Table = Product (exists in SQLNY and SQLFL)
SP = spGetProduct() (resides on SQLFL)
SQL Login = dbuser (on SQLNY, member of dbo on Product table)
I created a linked server on SQLFL pointing to SQLNY. The local server login to remote login mapping is as follows:
Local Login = dmTestUser
Remote Login = dbuser
with "Be made without security context" option selected. Run spGetProduct() to get Product records from SQLNY and populate Product table on SQLFL via the linked server.Here are tests I did:I connected to SQLFL using dmTestUser in SSMS. I manually executed the stored procedure spGetProduct() without any issues. Procedure gets Product records from SQLNY and populates the Product table on SQLFL. Everything is good.
I created a SQL Agent job called "Get NY Product" on SQLFL to execute spGetProduct(). I invoked this agent job and it executed successfully.However no data was loaded to the Product table on SQLFL. My stored procedure caught this error: Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "SQLNY".
1. When sql agent job runs, which account does it use? Is the SQL Server Service or SQL Agent?I believe it's the sql agent.
2. What do I need to do to make this work?
View 4 Replies
View Related
Jun 11, 2007
why can't i connect from 2000 to 2005?
i get this msg.
to connect to this server you must use sql server management studio or sql server management objects
=============================
http://www.sqlserverstudy.com
View 8 Replies
View Related
