servers Fail

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.1Contacted MS PSS for answer. Did a stack dump and traced error to a modal messagebox that the client provider raised (!!!).

Back to the provider.

Was not related to cluster server, but it sure "felt" that way. Learned some great things about stack dumps and such.

Regards,

hmscott

servers Fail

I have a Windows 2000 active/passive cluster. I have
configured 6 linked servers using OpenLink Database Proxy
Client. See below for steps I took to create linked
server entries.
In two months of testing, I have had to cycle the SQL
cluster (stop/restart SQL services) three or four times
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. I have isolated
the failure to the interaction between SQL and the DSN
entry (ie, the DB proxy works, the DSN works, but SELECT *
FROM OPENQUERY(MyLink, 'SELECT * FROM db2Table.db2Column')
does not work).
The linked servers are configured in the following manner:
1. I configure a DSN using the OpenLink client
(www.openlinksw.com)
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.1Contact MS PSS for answer. Did a stack dump and traced
error to a modal messagebox that the client provider
raised (!!!).
Back to the provider.
Regards,
hmscott
>--Original Message--
>I have a Windows 2000 active/passive cluster. I have
>configured 6 linked servers using OpenLink Database Proxy
>Client. See below for steps I took to create linked
>server entries.
>In two months of testing, I have had to cycle the SQL
>cluster (stop/restart SQL services) three or four times
>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. I have
isolated
>the failure to the interaction between SQL and the DSN
>entry (ie, the DB proxy works, the DSN works, but SELECT
*
>FROM OPENQUERY(MyLink, 'SELECT * FROM
db2Table.db2Column')
>does not work).
>The linked servers are configured in the following manner:
>1. I configure a DSN using the OpenLink client
>(www.openlinksw.com)
>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
>.
>

servers and problem with ANSI_NULLS and ANSI_WARNINGS

Hello everybody,

I've configured linked server from MS SQL 7.0 to Oracle 9.2. Next I've created trigger on table in MS SQL Database. Application which I'm using inserts data to this table and should to run this trigger. But after insertion of data I can see following messages in my logs:

SQL Server User Error: 7405, State 1, Severity: 16, Message: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

...and trigger doesn't do nothing ...

I red that I should use special settings for connection via linked servers.

SET ANSI_NULLS on
SET ANSI_WARNINGS on

...but I have found this in examples about procedures not about triggers. So my question is : is it possible to solve this problem if I use triggers ? And if it's possible, how can I do this ?

Thanks for your help.

Pawel.You should use like following:

set ANSI_Nulls on
go
create /*or alter */Procedure uspRefreshTable
@.TableName sysname
as
set ANSI_Warnings on
declare @.SQL nvarchar(2000)
...

Refer to books online for more information.|||Originally posted by Satya
You should use like following:

set ANSI_Nulls on
go
create /*or alter */Procedure uspRefreshTable
@.TableName sysname
as
set ANSI_Warnings on
declare @.SQL nvarchar(2000)
...

Refer to books online for more information.

Hi,

Yes, it's true but for procedures not for triggers. I've found this in example. I am looking for some example about triggers.

Cheers,

Pawel|||Just insert the code before :
CREATE TRIGGER...|||Originally posted by Satya
Just insert the code before :
CREATE TRIGGER...

It doesn't work. I tried this before I send email to dbforums :-(

Has anyone idea how to do it ?

Pawel