.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Linked Server: error connecting using link

Posted By:      Posted Date: October 21, 2010    Points: 0   Category :Sql Server
 

I'm pretty much at wits end because I can't see anything that would cause the problem after searching through several blogs.

So my question is can anyone help me with a SS2008 link service to a SS2005 instance?

The scenario is this:::

I am having trouble with a SS2008 linked server connection to a SS2005 (limited install) server.  The SS2005 server has been running perfectly fine for the past few years and we are upgrading our development environments to SS2008. 

The SS2005 BOUNCEBAK server is linked to by several of our other SS and Oracle databases and continues to work perfectly as a linked server to other servers.

The SS2008 server I am working with has working linked server connections to other SS2005 servers and is not giving any errors.  Named pipes is turned on and TCP is higher in the configuration order for communications protocols.

I have added a synonym name for the target server called BOUNCEBAK which is our company standard and is identical to what other servers have in their configuration.

I am able to connect to the BOUNCEBAK SS2005 server using the synonym in a management studio connection, so I'm pretty sure its not the synonym.

I have setup and verified by login a common user on the two servers (i.e. TestUser) with the same password on both (so as to make the test process simpler).&n


View Complete Post


More Related Resource Links

Error Connecting to Server Using SQL Server Management Studio

  
Hello all, I am trying to connect to a SQL Server 2005 instance using SQL Server Management Studio and I am receiving the following error message. =================================== Cannot connect to VTWIN. =================================== A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider) ------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476 ------------------------------ Error Number: 53 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)  &

Error connecting to Remote report server database in SQL Server 2008 R2 DataCenter Edition

  
I am trying to configure my report server installed on Server A to point to a reporting services DB on Server B. I am able provide the name and test the connection. However, when i click next on the configuration wizard after providing the Server A name I am receiving the below error: The feature: "Remote access to report data sources and/or the report server database" is not supported in this edition of Reporting Services I have SQL Server 2008 R2 DataCenter Edition on both the servers.    

Linked Server To MS Access using Jet Returns "unspecified error"

  
I'm have SQL SERVER 2008 on Win2008 Server. I have several linked servers to Oracle (odbc) and have just created Linked Servers to MS access 2003 databases on network shares. I can 'test connection' and select data from the linked ms access servers using 4 part naming no problem. As soon as I do this, the Oracle db links stop working. I reboot the server, test connection to Oracle dbs , all ok there, run test connection to ms access dbs and receive the following;Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MYAccessLink"".OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MYAccessLink" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303) Reboot the server, test connection to ms access db's, all ok, then test connection to oracle, and receive the following;A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.) (Microsoft SQL Server, Error: 109) Reboot server, perform test again, toggling the order and I get the same results.Have tried opening ms access via openrowset and "execute ('select count(*) from SomeTable') at MYAccessLink" to rule out test connection (sp_testlinkedserver) doing something to crash the links. I have checke

SQL Server 2005 X64 Linked Server error

  
We use Windows 2003 Server (64) on AMD64 and SQL Server 2005 Developer Edition x64 + SP1  Problem: I can not execute any sql on a linked server using the native Provider (SQLCLNI) Example (create a linked server on the same machine, other database (msdb) and try to execute any simple select using OPENQUERY  sp_addlinkedserver @server='ls',@srvproduct='SQLNCLI',@provider='SQLNCLI' ,@provstr='Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=msdb;Data Source=localhost;'  Select * from OPENQUERY(ls,'Select * from Sysobjects')  Msg 7356, Level 16, State 1, Line 1 The OLE DB provider "SQLNCLI" for linked server "ls" supplied inconsistent metadata for a column. The column "name" (compile-time ordinal 1) of object "Select * from Sysobjects" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time.

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access

  
Hi,I am importing Excel 2007 file into SqlServer 2005 (Sqlexpress). The office 2007 is not installed in the machine. I have downloaded and installed "AccessDatabaseEngine". When I execute following statement SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\File1.xlsx', 'SELECT * FROM [Sheet1$]');The following error is comming:Msg 7399, Level 16, State 1, Line 1The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.Msg 7350, Level 16, State 2, Line 1Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".Can any one suggest what should I do to resolve?Thanks,Parmeshthee. Learning .Net 3.5

SQL 2005 Error - Connecting Excel workbook to SQL 2005 server

  
Question: I am trying to connect an Excel workbook to a remote SQL server database and getting the following error below (see code snippet). Any insight would be appreciated. Wondering if there is an easier method to connecting then what I have. I have VBA coding in the Excel workbook (if needed, I can provide it). Connection failed: SQLState: '01000' SQL Server Error: 10054 [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead(recv()). Connection failed: SQLState: '08501' SQL Server Error: 11 [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.

Linked Server Mapping Error "Access to the remote server is denied because no login-mapping exists"

  

SQL Server 2005 SP3 32bit server
Connecting to an Oracle 10g server
Loaded OracleClient 10g

I'm able to ping the Oracle Server, and ODBC connect and able to run an open query with a single remote user id.

Issue: When I use the security context "Be made using this security context" it connects.
When I choose the "Not Be Made" WITH THE SAME USER ID & PASSWORD, I get the mapping error.


Help connecting with Linked Server !?

  

Hello,

I need to set up a linked server.

I connect to a remote desktop, that has JXExplore (Active directory) data store on it.  I connect to the RD with UserName= abc Password=123.

Then I connect to another remote desktop that has SQL 2008 on it. This is where I need to set up the linked server.  I have read a million forums/articles on this but I CANNOT get connected for some reason.  I have tried to query it and I have also tried using SQL Server Business Intelligence method and can't connect.

I created the linked server like so...

sp_addlinkedserver 'ADSI', 'Active Directory Service Interface', 'ADSDSOObject', 'adsdatasource'    <--Should I put my host name in for adsdatasource??

I created the Login like... (Here is where I 'Think' I go wrong)

sp_addlinkedsrvlogin 'ADSI', false, 'ls9', 'summit.local\abc', '123'  <-- ls9 is the login name I use for SQL, summit.local is my host name, abc is the username to connect to the remote desktop the JXExplore is on and 123 is the password

 

I wrote some openquery queries to try

extra token error on linked server query

  

I am writing a query across a linked server in SQL Server 2005 and am getting the error below about an extra token.

 

 

OLE DB provider "MSDASQL" for linked server "Live_Navision" returned message "[Navision a/s][Navision Attain ODBC Driver]Unexpected extra token: .".

Msg 7321, Level 16, State 2, Line 1

An error occurred while preparing the query "select ManitouSerialno from Navision.dbo.InterfaceSecuritySystemsLLC$CustomerTable" for execution against OLE DB provider "MSDASQL" for linked server "Live_NAVISION".


Lee Markum

Help connecting with Linked Server!?

  

Hello,

I need to set up a linked server.

I connect to a remote desktop, that has JXExplore (Active directory) data store on it.  I connect to the RD with UserName= abc Password=123.

Then I connect to another remote desktop that has SQL 2008 on it. This is where I need to set up the linked server.  I have read a million forums/articles on this but I CANNOT get connected for some reason.  I have tried to query it and I have also tried using SQL Server Business Intelligence method and can't connect.

I created the linked server like so...

sp_addlinkedserver 'ADSI', 'Active Directory Service Interface', 'ADSDSOObject', 'adsdatasource'    <--Should I put my host name in for adsdatasource??

I created the Login like... (Here is where I 'Think' I go wrong)

sp_addlinkedsrvlogin 'ADSI', false, 'ls9', 'summit.local\abc', '123'  <-- ls9 is the login name I use for SQL, summit.local is my host name, abc is the username to connect to the remote desktop the JXExplore is on and 123 is the password

 

I wrote some openquery queries to try and pull the data but my "LDAP://...." might have been wrong.

I do have all the Base DN, User DN, password, port number to connect to the JXExplore. I just have no idea where to input it all in my SQL statements


Error: OLE DB provider "SQLNCLI" for linked server "MHTIS01T" returned message "Multiple-step OLE DB

  

Hi All, i am getting the error (in title) while executing the package.

can somebody tell me how do i solve it?


Linked Server Error 7303 for Access 2003 Database

  
I am having difficulty creating a linked server to and Access 2003 database on my SQL 2005 server.  Whenever I create a linked server through the either the “New Linked Server” tool in the SQL Management Studio or using the sp_addlinkedserver method, receive and error indicating “Cannot initialize the data source object of the OLE DB provider…(Microsoft SQL Server Error: 7303)”.  Although my ultimate goal is to link to a secure Access 2003 database file, the current MDB file I am trying to link to is “unsecured”.  I have already verified that the SQL Service account has read and write access to the folder containing the MDB file as well as the temp directories on the SQL server (suggestions from other forums).  Is there any SQL Server configuration that I need to modify in order to use the Jet 4.0 linked server provider (Microsoft.Jet.OLEDB.4.0)?

Error while connecting to SQL Server

  

Hello,

When ever I try to connect to server through Microsoft sql server management studio express then an error is generated saying that

Cannot connect to .//SQLEXPRESS

Additional Information

A network related or instance specfic error occured while connecting to SQL server. The server not found or not accessible verify that instance name is correct and that SQL server is configured to allow remote connections.(Provider: Named Pipes Provider, error 40-could not open connection to SQL server )(Microsoft SQL server, Error:53)

Please help me regarding this as it is critical.

Thank you.

 


Linked server error

  


Hi,

Normally whenever I have to use this Linked Server in my query, it works fine. But the script is errored out when the Linked server is called twice in the query.


DELETE
FROM	LinkedServer.DbName.[dbo].[Table1]
WHERE	dealerid NOT IN (SELECT DealerId FROM Table2)
AND		dealerid NOT IN (SELECT dealerid FROM  LinkedServer.DbName.[dbo].[Table3])
		AND		dealerid NOT LIKE '%@%'
		AND		dealerid NOT LIKE '%-%'
		AND		dealerid != 'seacaptest'


Is anything wrong with this query? It didn't like the use of LinkedServer twice in this query. Please advise!


2008 server Access database connecting win7 workstation with ODBC link works extremely slowly

  

I have an Access database on a 2008 server (64 bit), and notebooks with 32 bit win7. The program using the mdb is written in delphy, and running on my notebook, and working on data extremely slow. What can be the problem?

The database connector is an ODBC linked network drive.

On that same configured PC with XP it works normally.

Pappxla


OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Syntax error

  

Hi there.

I have been sitting hours and hours trying to solved this issue with absolutely no luck.

I have the following query:

Code Snippet

DECLARE @sqlScript1 VARCHAR(8000)

-- Import records fron source file (OPENROWSET)

SET @sqlScript1 = 'SELECT * INTO ##tmpOrders FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'' ,

''Excel 8.0;Database=' + @fileName + ''', ''SELECT [Sale Time] AS SaleTime, Ref, [Item Title] AS ItemTitle, [Item Type] AS ItemType, [Item Id] AS ItemId,

Quantit

Configuring linked server for a service account - error authenticating

  

I am trying to run a report through SSRS 2008 web interface.  The credentials that are being used to execute the report are through a service account.  The point of using a service account was so that the subscriptions feature could be used.  However, two particular reports connect directly though a linked server to an excel workbook.  I get the following error when I try to execute the report:

 

  • An error has occurred during report processing. (rsProcessingAborted)
    • Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand)
      • The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "YTWCHECK" reported an error. Authentication failed. Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "YTWCHECK".

How can I correctly add my service account to the linked server?

 

 


ddd
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend