Linked Servers Fail

Nov 4, 2004

I have a Windows 2000 active/passive cluster. I have configured 6 linked servers using OpenLink Database Proxy Client.

I have had to repeatedly cycle the cluster (stop/restart SQL services) because the linked servers "fail". By failing, I mean that the linked servers stop responding to user queries. The user query simply spins with no activity.

The linked servers are configured in the following manner:
1. I configure a DSN using the OpenLink client
2. I configure SQL Server linked server to point to the DSN

Each node in the cluster has identical DSN entries (same name, same driver, same settings). We have tested failing the cluster from Node 1 to Node 2 and back again. We have tested the failover process with queries actively running (they fail). We are always able to reconnect and resume normal processing after the backup node recovers the SQL server.

The linked server failures don't appear to have any real pattern to them. There seems to be a weak correlation between a failover and failure in the linked servers (the linked servers fail 1 - 2 days later).

This is not making any sense to me at all. I am NOT a fan of linked servers, but I have no choice here: I inherited these and the developers are NOT interested in alternative solutions.

If you have any thoughts on the matter, please drop a line.

Regards,

hmscott

Additonal info:
Windows 2000 SP4
SQL 2000 SP3 (hotfix 0818)
MDAC 2.7 SP1
OpenLink MultiTier 5.1

ADVERTISEMENT

Linked Servers On MSCS Fail

Dec 21, 1999

Hi I am not having luck creating a linked server on SQL7 running HA on MSCS. We keep getting the "Error 6: Specified SQL server not found"

1. The Linked Server has an advanced entry in the Client Network Utility
2. Setting up the Link to a SQL 7 Server
3. Security is Mapped to a remote user which does exist on the Remote Server.
4. The two machines can ping each other.
5. On the Linking Server the linked Server can be registered as a registered server.

Any Ideas?

Can Linked Servers be set up on M$CS?

-Thanks in advance
-tom

View 2 Replies View Related

DB Engine :: Distributed Transactions Fail On Linked Server

Feb 12, 2009

We get the below error while performing a distributed transaction on linked server. We have several linked servers configured in the source server and all of them succeed with the distributed transaction except on one.

We did all the basic troubleshooting and moreover the distributed transactions work fine if we use a remote server instead.

Error:
OLE DB provider "SQLNCLI10" for linked server "SERVERNAME.REDMOND.CORP.MICROSOFT.COM" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVERNAME.REDMOND.CORP.MICROSOFT.COM" was unable to begin a distributed transaction.

Test code:
begin distributed transaction
select top 10 * from [SERVERNAME.REDMOND.CORP.MICROSOFT.COM].master.sys.objects
ROLLBACK

Source server :
Microsoft SQL Server 2008 (RTM) - 10.0.1779.0 (X64)
       Nov 12 2008 12:10:04
       Copyright (c) 1988-2008 Microsoft Corporation
       Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1) (VM)

Target server :
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
       Jul 9 2008 14:43:34
       Copyright (c) 1988-2008 Microsoft Corporation
       Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

View 30 Replies View Related

LINKED SERVER NESTED OPENQUERY SAMPLE FROM TIPS AND TRICKS DOESN'T APPEARS TO FAIL WITH MSOLAP.

Feb 27, 2007

Is it that I have a syntax error in the nested OPENQUERY or is there another issue? Do I need to specify a different provider in the Server Link such as OLEDB? Non-nested OPENQUERYs work fine.

I'm generally following theTips and Tricks article.

"Executing predictions from the SQL Server relational engine". One problem is the sample doesn't actually complete the example query after the second nested OPENQUERY call.

e.g.

  SELECT * FROM OPENQUERY(DMServer,
'select �� FROM Modell PREDICTION JOIN OPENQUERY��')

The SQL Server server link's provider is configured to allow adhoc access. I appears that the inner OPENQUERY cannot be prepared by Analysis Server or the Server link provider? but I need to return a key value t.[CardTransactionID] for joining to SQL Server data elements.

OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Errors in the back-end database access module. The data provider does not support preparing queries.".

Msg 7321, Level 16, State 2, Line 2 An error occurred while preparing the query
SELECT * FROM OPENQUERY(DMServer,
'SELECT
              t.[CardTransactionID],
              t.[PostingDate],
              [Misuse Abuse Profile].[Even Dollar Purchase],
              PredictProbability([Misuse Abuse Profile].[Even Dollar Purchase]) AS Score,
              PredictSupport([Misuse Abuse Profile].[Even Dollar Purchase]) AS Suppt,
              t.[BillingAmount]
            FROM
              [Misuse Abuse Profile]
            PREDICTION JOIN
              OPENQUERY([Athena Dev],
                ''SELECT
                  [CardTransactionID],
                  [PostingDate],
                  [BillingAmount],
                  [AccountNumber],
                  [SupplierStateProvinceCode],
                  [MerchantCategoryCode],
                  [PurchaseIDFormat],
                  [TransactionTime],
                  [TaxAmountIncludedCode],
                  [Tax2AmountIncludedCode],
                  [OrderTypeCode],
                  [MemoPostFlag],
                  [EvenDollarPurchase]
                FROM
                  [dbo].[vMisuseAbuseProfile]
                '') AS t
            ON
              [Misuse Abuse Profile].[Account Number] = t.[AccountNumber] AND
              [Misuse Abuse Profile].[Supplier State Province Code] = t.[SupplierStateProvinceCode] AND
              [Misuse Abuse Profile].[Merchant Category Code] = t.[MerchantCategoryCode] AND
              [Misuse Abuse Profile].[Purchase ID Format] = t.[PurchaseIDFormat] AND
              [Misuse Abuse Profile].[Transaction Time] = t.[TransactionTime] AND
              [Misuse Abuse Profile].[Tax Amount Included Code] = t.[TaxAmountIncludedCode] AND
              [Misuse Abuse Profile].[Tax2 Amount Included Code] = t.[Tax2AmountIncludedCode] AND
              [Misuse Abuse Profile].[Order Type Code] = t.[OrderTypeCode] AND
              [Misuse Abuse Profile].[Memo Post Flag] = t.[MemoPostFlag] AND
              [Misuse Abuse Profile].[Even Dollar Purchase] = t.[EvenDollarPurchase]
                      ')

In desparation I tried returning the case key (CardTransactionID) and the predictive column elements but I get an error when I try that. I assume this is a no-no?
OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Error (Data mining): Only a predictable column (or a column that is related to a predictable column) can be referenced from the mining model in the context at line 2, column 15.".

View 4 Replies View Related

Linked Servers

Aug 17, 2006

Is it possible to link to an SQL 2005 server db from a SQL 2000 server? Is there a driver for this?

View 1 Replies View Related

What Are Linked Servers And What R Its Uses

Apr 5, 2008

can anybody tell me about Linked Servers and their uses and how to add a linked server to my Sql Server 2005.
any help on this would be highly appreciated.

View 3 Replies View Related

Linked Servers

Jun 10, 2004

Hello All,

I have been trying to Link two sql servers on two different machines over the Internet without any luck. Can someone point me to information about doing this with good examples?

Thanks

View 2 Replies View Related

LINKED SERVERS

Jan 22, 2002

I currently have a main SQL Server which had a column on the majority of the tables. This column also had a check contraint on it. I dropped the column and the constraint and I now get the following message when trying to query the tables through a linked server 'OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.'.

Can anyone help

View 1 Replies View Related

Linked Servers.....

May 10, 2001

Hi All,
I have successfully linked a server and had SQL queries running
OK for a few weeks, but today I get the following message....

"OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time."
(Server: Msg 7353, Level 16, State 1, Line 1)

I've dropped the link and recreated it but I still get the same error
message. Can anyone help???

Thanks
David.

View 2 Replies View Related

Linked Servers In DTS...

Jun 25, 2001

Hi,

I'm using SQL Server 7.0 SP1.

I have a DTS package that requires me to link 2 servers
and query both in order to get my set of records that I
want to Transform. My query runs fine in QA. I copy and
paste it into the SQL Query window of the transformation.
I click Preview and all that happens is a quick flicker of
the DTS window. When I go to Transformations, there are
no source columns.

Here is a copy of my code that I'm trying to use:
-------------------------------------------------------
declare@start_dteas datetime,
@end_dteas datetime

SELECT@start_dte = date_data_range_start_dte,
@END_DTE = date_data_range_END_dte
FROMSIDDEV.SID_DEVELOPMENT.DBO.T901_RUN
WHERERUN_ID = (selectmax(run_id)
from
SIDDEV.SID_DEVELOPMENT.DBO.t901_run
whererun_type_cd = 'M')

SELECTRUN_ID
INTO#RUNS
FROMCRMDEV02.MDCORE.DBO.T901_RUN
WHEREDATE_DATA_RANGE_START_DTE >= @START_DTE
ANDDATE_DATA_RANGE_END_DTE <= @END_DTE

SELECTT70.*
FROM
CRMDEV02.MDCORE.DBO.T70_MD_UNIVERSE_RELATIONSHIP_M A
NGT AS T70 JOIN #RUNS
ONT70.RUN_ID = #RUNS.RUN_ID

DROP TABLE #RUNS
------------------------------------------------------
Is there an issue with Linked Servers and DTS?
Any help would be greatly appreciated.

Thanks in advance,
Darrin

View 1 Replies View Related

LINKED SERVERS

Aug 7, 2001

FOLKS
I GET THE FOLLOWING ERROR WHEN I RUN A SELECT FROM THE LINKED SERVER
THE LINKSERVER EXISTS AND THERE IS A RECORD IN SYSSERVERS TOO.

Could not initialize data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'

I AM NOT SURE WHERE TO GO FROM HERE.

REGARDS

GIRISH

View 1 Replies View Related

Linked Servers

Aug 10, 2001

Hi Gurus,
I am looking for literature to find out Pros and Cons of Using DTS Verses
Linked Servers in SQL 7.0.

My requirement can be done by either DTS or Linked servers. But I would like to know more about resorce utilisation of these tecniques before making a decission.

Can somebody suggest where to look for.

Thanks
sekhar

View 1 Replies View Related

Linked Servers

Sep 18, 2001

When I create linked server using integrated security and <they will be impersonated> option I get this:
Server: Msg 18456, Level 14, State 1, Line 1 Login failed for user ''
MS Technet says that SQL Server 7 doesn't allow double hops and to use mappings to standard security login to work around. @#%%~~@@#@#&^%@#
Impersonating to version 6.5 works fine.
Is there any way to link servers using ONLY integrated security?
Any help would be much appreciated.
Thanks

View 1 Replies View Related

Linked Servers

Jul 18, 2000

I have setup a linked Informix server in SQL7.0 and I am trying to create a
simple View with the following SQL statement

SELECT doc_code
FROM FOURSITE.foursite.informix.watdoc
WHERE (order_no = [PV01963B ])

I am getting the following error:

---------------------------
SQL Server Enterprise Manager
---------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid use of schema and/or
catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but
the provider does not expose the necessary interfaces to use a catalog
and/or schema.
---------------------------
OK Help
---------------------------

Can any one tell me how to correct this problem?

Thanks

Peter
pczurak@bigfoot.com

View 5 Replies View Related

Linked Servers

Apr 17, 2000

Is it possible to add a 6.5 database as a linked server to a 7.0 database and query from it ?

View 6 Replies View Related

About Linked Servers

Nov 3, 2000

Hi.
I'm working with SQL Server 7.0 and I've been reading about the linked server option. My question is, if it's possible to link a server with a SQLBase database 7.01.

I have download some drivers to my computer, the odbc driver for sqlbase and the sqlbase ole db driver as well, but when I try to add a new linked server in the enterprise, in the provider name option I can not see any provider name addressing to Centura or Sql base driver.

Has anybody an idea how to make this connection then? or the right way for doing it??

Thanks for any help can be offered.
Regards, Elvia.
:)

View 2 Replies View Related

LINKED SERVERS

Feb 10, 2000

I have two 7.0 servers and I want to run a distributed query.

I did sp_addlinkedserver to link server B to server A
and I did sp_addlinkedsrvlogin for a specific login.
After adding login when I try to access a table on server B it
says login failed

All the logins are NT authenticated (in both the servers, So
both servers have same logins.)

but it is not working.

View 1 Replies View Related

Linked Servers

Sep 1, 2000

Hi all
I try to configure Linked Servers from the query analyze , and have some troubles.
My steps are :
1. from the enterprise mennenger , add 2 new servers : srv1 , srv2 (both SQL7)
2. from the query analyzer of srv1.master: "sp_addlinkedserver 'srv2'"
3. from the query analyzer of srv1.master:
"sp_addlinkedsrvlogin 'srv2',false,null,'sa','myPass'"
4. select * from srv2.pubs.srv2Table
5. I get the next error :"error 6 : Specified SQL srever not found."

What do I do wrong ?
Eyal

View 2 Replies View Related

Linked Servers

Apr 19, 2000

Anyone out there use linked servers? I just tried this SQL7 feature and am very impressed. Are there any pitfalls to watch out for? Comments?

Thanks in advance.

Steve

View 1 Replies View Related

Linked Servers

Feb 14, 2002

I have created link servers a few time within SQL2000 and pulled data from Oracle. How would an Oracle Server or Application pull / See from SQL?

Anyone have any experience?

Thanks,
David

View 1 Replies View Related

Linked Servers

May 11, 2002

How to determine by means of T-SQL
if a linked server is available(for processing with its tables)?

View 1 Replies View Related

Linked Servers

Jun 26, 2002

This is my problem I have a Server A and Server B. There is a master table in Table A. I link Server A and Server B. When I look in Under Linked Server tables of B I can only C tables in Server A not the Server B 's Tables. Dpn't know what is happening. Could SOmebody help

View 1 Replies View Related

Linked Servers

Nov 1, 2004

We are creating a newServer grabbing data from an extrnal data source.

We need to perform regular audits to verify the record counts (for now) are the same.

We have 2 physically separate SQL servers 1 with old 1 with new.

I have created a linked server but when i do a query on the linked server the performace is pathetic (2mins 12 secs) to do a simple count.

Is there a better way, or can improv the perf?

Thanks
Greg C

View 3 Replies View Related

Linked Servers

Jan 5, 2006

I have two servers and they are linked servers. When I query a table on ON Box2 from Box1
The distributed query works fine but when I tried the oppisite it says 'SQL Server does not exist or access denied.'
What could be the reason.

Thanks.

View 2 Replies View Related

Linked Servers

Feb 27, 2006

Recently, I had Microsoft review why our SQL Db was unexpectedly terminating. They pointed to our Oracle Linked servers. They recommended that we try to uncheck the enable allow in process option on the ODBC driver. We are currently using the Microsoft OLE Db provider for our Oracle connnections as we were unable to get the Oracle ones to work with a linked server. When we unchecked this allow in process option near the end of the day our website began getting out memory errors from ODBC. It appeared to have been because of this change. Does anyone have any insight as to why changing this option could have caused this?

View 1 Replies View Related

Linked Servers

Sep 29, 2006

Hello,

Is there any limit to the number of linked servers i can add via EM?. where can i find more info on adding/configuring linked servers in MSSQL2k and the limitations/drawbacks if any?

Thanks in advance..

View 1 Replies View Related

Linked Servers

Apr 12, 2007

Hello (again!)

We have a new 2005 server, and I am trying to link our old 2000 server so I can use the old databases in new server.

This is what I done:
In 2005:
EXEC sp_addlinkedserver oldServer
--completed fine
then:
EXEC sp_addlinkedsrvlogin oldServer, 'false', NULL, 'Administrator', password
--completed fine
But whenI run the query I get the error:

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

Any ideas????

View 2 Replies View Related

Linked Servers

Aug 28, 2007

I have to link servers using sql server 2000 standard edition....
for anyone who has done this..... upon linking the servers using the other options radio button choice.... is it suppose to show all the databases for that server under the drop down....
its only showing tables and views... which i get an error 7339 or error 17:

can anyone assist...or lead me in the right direction.....
thanks,
jonathan

View 5 Replies View Related

Linked Servers

Sep 12, 2007

Hi,

Disclaimer: I am not a DBA so my attempts may not make sense. :)

I have a win 2003 server running sql server 2005. I am trying to create a 'linked server' with an oracle 8i database at a remote site. I have tried using the 'add linked server' option from sql server enterprise manager and I have also tried to create it using the sp_addlinkedserver command. i tried using a dsn as the datasource and I have also tried using a dsnless connection using a TNS entry as the provider string. The Oracle client is on the box as can be confirmed both by the dropdown in the 'add linked server' dialog in sql server enterprise manager and also by going into the oracle enterprise manager and actually sending oracle data. I don't really have a preference on how to create the 'Linked server', I just need to get it to work.

AAA = server
BBB = DSN
XXX = UID
YYY = pwd

DSNLess Attempt:
EXEC master.dbo.sp_addlinkedserver @server = N'TEST1', @srvproduct=N'OraOLEDB.Oracle', @provider=N'OraOLEDB.Oracle', @provstr=N'SERVER=(DESCRIPTION=(ADDRESS_LIST=(ADDR ESS=(PROTOCOL=TCP)(HOST=AAA)(PORT=1556))) (CONNECT_DATA=(SERVICE_NAME=LPPCPEDB)));uid=XXX;pw d=YYY;'

DSN Attempt:
EXEC master.dbo.sp_addlinkedserver @server = N'TEST2', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=BBB'

Thanks in Advance

View 3 Replies View Related

Linked Servers

Apr 7, 2004

I have a linked server connection on a SQL 2k server connected to a Sybase ASE 12.5 server via Sybase OLE DB connection. To this point everything has been working great for months. Now we're encountering a problem where new tables created on the Sybase db won't show up in the linked server connection. I've checked permissions and the object owners and everything is exactly the same as the pre-existing tables except the table's new.

Anyone have any ideas wht's up???

View 14 Replies View Related

Linked Servers

Nov 14, 2007

Trying to set this up
http://www.databasejournal.com/features/mssql/article.php/3691721

When i select this be made using this security context
and use local account login it keeps erroring.

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)

------------------------------

TCP Provider: An existing connection was forcibly closed by the remote host.

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
OLE DB provider "SQLNCLI" for linked server "10.50.40.157" returned message "Communication link failure". (Microsoft SQL Server, Error: 10054)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476

------------------------------
BUTTONS:

&Yes
&No
------------------------------

User exists on both SERVERS the linked one and the one im setting u the linked server with.

Dont understand.

View 6 Replies View Related

Linked SQL Servers

Jan 18, 2008

Okay, so i have two sql servers on the same network. i want to be able to have them linked. so i went into management studio, and did the New Linked Server Wizard.

i typed in the box "Server2" (name of the server i want to link to) and selected SQL CLIENT bullet

now the sa logins are the same, and so are the windows logins for the server, so security shouldnt really be an issue.

when i run: EXEC sp_tables_ex Server2

i get the following error:
OLE DB provider "SQLNCLI" for linked server "Server2" returned message "Invalid authorization specification".
Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 41
The OLE DB provider "SQLNCLI" for linked server "Server2" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "Server2".

is this a username/password issue? the windows administrator accounts are the same, and so are the sa logins, is there something i can do to get past this?

small update i tried:
USE master;
GO
EXEC sp_addlinkedserver
'Server2',
N'SQL Server'
GO

BEGIN TRY
EXEC sp_testlinkedserver N'Server2';
END TRY
BEGIN CATCH
PRINT 'Linked Server not available';
RETURN;
END CATCH

ang got:
OLE DB provider "SQLNCLI" for linked server "Server2" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "Server2" 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.".
Linked Server not available

View 2 Replies View Related

Linked Servers

Apr 8, 2008

Hello,

I need help creating a linked server between two SQL Server 2005 databases.
What I need to be able to do is query Table A from Database1 on Server1 from Database1 on Server2. Do I create a DSN? Would I create it on Server1 or Server2? I've looked at examples online and the linked server template, but I'm very new at this.

Many thanks,

Mark

View 7 Replies View Related

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