I am running trigger with update on a linked server in SQL 2005 SP2. It is
running very smooth and when I turn on the firewall, the trigger action is
giving error and failed to take action. Is this a Big NO between firewall
and Linked server? Is there a way to solve this problem? Thanks.You simply need to ensure the appropriate ports are open on the firewall, at
least between the two servers of concern. A simple packet capture while the
trigger is firing should inform you as to what needs to be opened up. At a
minimum it should be TCP 1433 and probably 1434. I don't know if other's
are involved in remote proc executions or not.
TheSQLGuru
President
Indicium Resources, Inc.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>running very smooth and when I turn on the firewall, the trigger action is
>giving error and failed to take action. Is this a Big NO between firewall
>and Linked server? Is there a way to solve this problem? Thanks.
>|||What is the mechanism to capture the packet? Thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:ec5p9z1gHHA.1220@.TK2MSFTNGP03.phx.gbl...
> You simply need to ensure the appropriate ports are open on the firewall,
> at least between the two servers of concern. A simple packet capture
> while the trigger is firing should inform you as to what needs to be
> opened up. At a minimum it should be TCP 1433 and probably 1434. I don't
> know if other's are involved in remote proc executions or not.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>
Showing posts with label turn. Show all posts
Showing posts with label turn. Show all posts
Wednesday, March 28, 2012
linked server problem
I am running trigger with update on a linked server in SQL 2005 SP2. It is
running very smooth and when I turn on the firewall, the trigger action is
giving error and failed to take action. Is this a Big NO between firewall
and Linked server? Is there a way to solve this problem? Thanks.
You simply need to ensure the appropriate ports are open on the firewall, at
least between the two servers of concern. A simple packet capture while the
trigger is firing should inform you as to what needs to be opened up. At a
minimum it should be TCP 1433 and probably 1434. I don't know if other's
are involved in remote proc executions or not.
TheSQLGuru
President
Indicium Resources, Inc.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>running very smooth and when I turn on the firewall, the trigger action is
>giving error and failed to take action. Is this a Big NO between firewall
>and Linked server? Is there a way to solve this problem? Thanks.
>
|||What is the mechanism to capture the packet? Thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:ec5p9z1gHHA.1220@.TK2MSFTNGP03.phx.gbl...
> You simply need to ensure the appropriate ports are open on the firewall,
> at least between the two servers of concern. A simple packet capture
> while the trigger is firing should inform you as to what needs to be
> opened up. At a minimum it should be TCP 1433 and probably 1434. I don't
> know if other's are involved in remote proc executions or not.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>
running very smooth and when I turn on the firewall, the trigger action is
giving error and failed to take action. Is this a Big NO between firewall
and Linked server? Is there a way to solve this problem? Thanks.
You simply need to ensure the appropriate ports are open on the firewall, at
least between the two servers of concern. A simple packet capture while the
trigger is firing should inform you as to what needs to be opened up. At a
minimum it should be TCP 1433 and probably 1434. I don't know if other's
are involved in remote proc executions or not.
TheSQLGuru
President
Indicium Resources, Inc.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>running very smooth and when I turn on the firewall, the trigger action is
>giving error and failed to take action. Is this a Big NO between firewall
>and Linked server? Is there a way to solve this problem? Thanks.
>
|||What is the mechanism to capture the packet? Thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:ec5p9z1gHHA.1220@.TK2MSFTNGP03.phx.gbl...
> You simply need to ensure the appropriate ports are open on the firewall,
> at least between the two servers of concern. A simple packet capture
> while the trigger is firing should inform you as to what needs to be
> opened up. At a minimum it should be TCP 1433 and probably 1434. I don't
> know if other's are involved in remote proc executions or not.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>
linked server problem
I am running trigger with update on a linked server in SQL 2005 SP2. It is
running very smooth and when I turn on the firewall, the trigger action is
giving error and failed to take action. Is this a Big NO between firewall
and Linked server? Is there a way to solve this problem? Thanks.You simply need to ensure the appropriate ports are open on the firewall, at
least between the two servers of concern. A simple packet capture while the
trigger is firing should inform you as to what needs to be opened up. At a
minimum it should be TCP 1433 and probably 1434. I don't know if other's
are involved in remote proc executions or not.
--
TheSQLGuru
President
Indicium Resources, Inc.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>running very smooth and when I turn on the firewall, the trigger action is
>giving error and failed to take action. Is this a Big NO between firewall
>and Linked server? Is there a way to solve this problem? Thanks.
>|||What is the mechanism to capture the packet? Thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:ec5p9z1gHHA.1220@.TK2MSFTNGP03.phx.gbl...
> You simply need to ensure the appropriate ports are open on the firewall,
> at least between the two servers of concern. A simple packet capture
> while the trigger is firing should inform you as to what needs to be
> opened up. At a minimum it should be TCP 1433 and probably 1434. I don't
> know if other's are involved in remote proc executions or not.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>>running very smooth and when I turn on the firewall, the trigger action is
>>giving error and failed to take action. Is this a Big NO between firewall
>>and Linked server? Is there a way to solve this problem? Thanks.
>
running very smooth and when I turn on the firewall, the trigger action is
giving error and failed to take action. Is this a Big NO between firewall
and Linked server? Is there a way to solve this problem? Thanks.You simply need to ensure the appropriate ports are open on the firewall, at
least between the two servers of concern. A simple packet capture while the
trigger is firing should inform you as to what needs to be opened up. At a
minimum it should be TCP 1433 and probably 1434. I don't know if other's
are involved in remote proc executions or not.
--
TheSQLGuru
President
Indicium Resources, Inc.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>running very smooth and when I turn on the firewall, the trigger action is
>giving error and failed to take action. Is this a Big NO between firewall
>and Linked server? Is there a way to solve this problem? Thanks.
>|||What is the mechanism to capture the packet? Thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:ec5p9z1gHHA.1220@.TK2MSFTNGP03.phx.gbl...
> You simply need to ensure the appropriate ports are open on the firewall,
> at least between the two servers of concern. A simple packet capture
> while the trigger is firing should inform you as to what needs to be
> opened up. At a minimum it should be TCP 1433 and probably 1434. I don't
> know if other's are involved in remote proc executions or not.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>>running very smooth and when I turn on the firewall, the trigger action is
>>giving error and failed to take action. Is this a Big NO between firewall
>>and Linked server? Is there a way to solve this problem? Thanks.
>
Monday, March 19, 2012
Linked Server error 7399
I have setup a linked server to a Sybase database using the ODBC driver (which in turn uses the Sybase OLE DB provider). Logged onto the box as the SQL Service account everything is good. Using Enterprise Manager from any machine to the SQL Server box using an account that is a local administrator on the SQL Server box everything is good. However, if I attempt to make a connection via Enterprise Manager with an account that does not have local administrator rights on the server I get an error. I get the same error if I attempt an OPENQUERY against a table in the Linked server database. The error I receive is as follows:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [SYBASE][ODBC Sybase driver]Allocation of a Sybase Open Client Context failed. Sybase normally generates a SYBINIT.ERR file contianing more specific reasons for failing.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
The drives are secured so that only local administrators have rights to them.
I found in the Microsoft KB the follownig article:
http://support.microsoft.com/?kbid=814398
In this article it is suggesting that I need to open up the server from this lockdown. So, just to test this out without having to restart SQL Server I first allowed for full access to the C drive to "Everyone" and everything was good.
My question then is, has anybody else ran into this sort of thing? It doesn't seem right that I have to open up some folder on the database server to everyone in order to get a linked server going.
ThanksI figured it out. Here is what I found out:
Using the FileMon.exe tool from sysinternals.com I noticed that the file referred to in the error called "SYBINIT.ERR" could not be written since regular users did not have write access to the directory that it was trying to be written to. This was C:\WINNT\System32. So, I temporarily gave "Everyone" rights to modify the files within this directory. After doing so I ended up getting a "SYBINIT.ERR" file created which in turn told me that it "Cannot access file c:\sybase\ini\objectid.dat". I then gave Everyone read rights to the C:\sybase\ini folder. I then attempted again and got the following error: "Cannot access file c:\sybase\locales\locales.dat". Again, I gave read rights to this folder and tried again... this time getting the following errors: "Cannot access c:\sybase\charsets\iso_1\binary.srt file
Cannot access c:\sybase\charsets\iso_1\charset.loc file
Cannot access c:\sybase\charsets\iso_1\iso_1.cfg file"
Not wanting to keep this up, I then gave read rights to the C:\sybase folder and tried again. Everything worked. Then, I removed modify rights from "C:\WINNT\System32" and tried again. EVERYTHING WORKED!!! Yeah, this means that I just had to give read rights without opening up the SQL Server too much.|||Appreciate your interest in posting solution too, keep it up.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [SYBASE][ODBC Sybase driver]Allocation of a Sybase Open Client Context failed. Sybase normally generates a SYBINIT.ERR file contianing more specific reasons for failing.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
The drives are secured so that only local administrators have rights to them.
I found in the Microsoft KB the follownig article:
http://support.microsoft.com/?kbid=814398
In this article it is suggesting that I need to open up the server from this lockdown. So, just to test this out without having to restart SQL Server I first allowed for full access to the C drive to "Everyone" and everything was good.
My question then is, has anybody else ran into this sort of thing? It doesn't seem right that I have to open up some folder on the database server to everyone in order to get a linked server going.
ThanksI figured it out. Here is what I found out:
Using the FileMon.exe tool from sysinternals.com I noticed that the file referred to in the error called "SYBINIT.ERR" could not be written since regular users did not have write access to the directory that it was trying to be written to. This was C:\WINNT\System32. So, I temporarily gave "Everyone" rights to modify the files within this directory. After doing so I ended up getting a "SYBINIT.ERR" file created which in turn told me that it "Cannot access file c:\sybase\ini\objectid.dat". I then gave Everyone read rights to the C:\sybase\ini folder. I then attempted again and got the following error: "Cannot access file c:\sybase\locales\locales.dat". Again, I gave read rights to this folder and tried again... this time getting the following errors: "Cannot access c:\sybase\charsets\iso_1\binary.srt file
Cannot access c:\sybase\charsets\iso_1\charset.loc file
Cannot access c:\sybase\charsets\iso_1\iso_1.cfg file"
Not wanting to keep this up, I then gave read rights to the C:\sybase folder and tried again. Everything worked. Then, I removed modify rights from "C:\WINNT\System32" and tried again. EVERYTHING WORKED!!! Yeah, this means that I just had to give read rights without opening up the SQL Server too much.|||Appreciate your interest in posting solution too, keep it up.
Friday, March 9, 2012
Linked Server and Distributed Transactions
We have a developer that is trying to do a distributed
transaction using a View that in turn uses a Linked
Server.
The Linked server is the MSSQL 2000 server, so both
databases reside on the same server.
She is receiving the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB
provider returned message: New transaction cannot enlist
in the specified transaction coordinator.]
Has anyone see this error? Is it an Option in the OLE
DB Provider or a Linked Server Option that is the problem?
I am able to do distributed transactions inside a MSSQL
Trigger that goes out to an Oracle DB and inserts data,
without an error. So, I am not sure why MSSQL cannot
use a Linked Server for a "local" database connection?
Any help is much appreciated,
MarkHi Mark,
"Mark" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:016401c3c668$e315c8e0$a401280a@.phx.gbl...
> We have a developer that is trying to do a distributed
> transaction using a View that in turn uses a Linked
> Server.
> The Linked server is the MSSQL 2000 server, so both
> databases reside on the same server.
> She is receiving the following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB
> provider returned message: New transaction cannot enlist
> in the specified transaction coordinator.]
> Has anyone see this error? Is it an Option in the OLE
> DB Provider or a Linked Server Option that is the problem?
> I am able to do distributed transactions inside a MSSQL
> Trigger that goes out to an Oracle DB and inserts data,
> without an error. So, I am not sure why MSSQL cannot
> use a Linked Server for a "local" database connection?
> Any help is much appreciated,
> Mark
>
Seems to be a Loopback-Situation.
See http://support.microsoft.com/?kbid=306212 and follow the Link "Loopback
Linked Servers"...
Patrick
transaction using a View that in turn uses a Linked
Server.
The Linked server is the MSSQL 2000 server, so both
databases reside on the same server.
She is receiving the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB
provider returned message: New transaction cannot enlist
in the specified transaction coordinator.]
Has anyone see this error? Is it an Option in the OLE
DB Provider or a Linked Server Option that is the problem?
I am able to do distributed transactions inside a MSSQL
Trigger that goes out to an Oracle DB and inserts data,
without an error. So, I am not sure why MSSQL cannot
use a Linked Server for a "local" database connection?
Any help is much appreciated,
MarkHi Mark,
"Mark" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:016401c3c668$e315c8e0$a401280a@.phx.gbl...
> We have a developer that is trying to do a distributed
> transaction using a View that in turn uses a Linked
> Server.
> The Linked server is the MSSQL 2000 server, so both
> databases reside on the same server.
> She is receiving the following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB
> provider returned message: New transaction cannot enlist
> in the specified transaction coordinator.]
> Has anyone see this error? Is it an Option in the OLE
> DB Provider or a Linked Server Option that is the problem?
> I am able to do distributed transactions inside a MSSQL
> Trigger that goes out to an Oracle DB and inserts data,
> without an error. So, I am not sure why MSSQL cannot
> use a Linked Server for a "local" database connection?
> Any help is much appreciated,
> Mark
>
Seems to be a Loopback-Situation.
See http://support.microsoft.com/?kbid=306212 and follow the Link "Loopback
Linked Servers"...
Patrick
Friday, February 24, 2012
Linked Server & XACT_ABORT
Is there anyway to set XACT_ABORT ON for a linked server? We have a linked
server setup between SQL 2000 and DB2. To use transactions we need to turn
XACT_ABORT on in every single stored procedure. Is there a way to turn it on
at the linked server level?
Thanks.
Paula.. If an application has a local transaction and the option
REMOTE_PROC_TRANSACTIONS is set ON, calling a remote stored procedure
escalates the local transaction to a distributed transaction.
This can be set via sp_configure I beleive... I have never tried this, but
perhaps it is worth a few minutes of your time..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:A2488655-A223-4C02-AAEF-33AD82485C53@.microsoft.com...
> Is there anyway to set XACT_ABORT ON for a linked server? We have a
linked
> server setup between SQL 2000 and DB2. To use transactions we need to
turn
> XACT_ABORT on in every single stored procedure. Is there a way to turn it
on
> at the linked server level?
> Thanks.
> Paul
>|||Thank you for the reply. Unfortunately that did not work. I ran the
following command to turn that option on:
EXEC sp_configure 'remote proc trans', 1
I ran the following statements:
BEGIN TRAN
UPDATE D SET
D.Location = 999
FROM LS..DB2TDBM.DEPARTMENT D
WHERE D.ADMRDEPT = 'E01'
COMMIT TRAN
And got the following error, which is the same error when the Remote Proc
Trans is truned off:
"Unable to start a nested transaction for OLE DB provider 'IBMDADB2'. A
nested transaction was required because the XACT_ABORT option was set to OFF."
Any ideas?
Thanks.
Paul
"Wayne Snyder" wrote:
>
> a.. If an application has a local transaction and the option
> REMOTE_PROC_TRANSACTIONS is set ON, calling a remote stored procedure
> escalates the local transaction to a distributed transaction.
> This can be set via sp_configure I beleive... I have never tried this, but
> perhaps it is worth a few minutes of your time..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:A2488655-A223-4C02-AAEF-33AD82485C53@.microsoft.com...
> >
> > Is there anyway to set XACT_ABORT ON for a linked server? We have a
> linked
> > server setup between SQL 2000 and DB2. To use transactions we need to
> turn
> > XACT_ABORT on in every single stored procedure. Is there a way to turn it
> on
> > at the linked server level?
> >
> > Thanks.
> >
> > Paul
> >
> >
>
>
server setup between SQL 2000 and DB2. To use transactions we need to turn
XACT_ABORT on in every single stored procedure. Is there a way to turn it on
at the linked server level?
Thanks.
Paula.. If an application has a local transaction and the option
REMOTE_PROC_TRANSACTIONS is set ON, calling a remote stored procedure
escalates the local transaction to a distributed transaction.
This can be set via sp_configure I beleive... I have never tried this, but
perhaps it is worth a few minutes of your time..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:A2488655-A223-4C02-AAEF-33AD82485C53@.microsoft.com...
> Is there anyway to set XACT_ABORT ON for a linked server? We have a
linked
> server setup between SQL 2000 and DB2. To use transactions we need to
turn
> XACT_ABORT on in every single stored procedure. Is there a way to turn it
on
> at the linked server level?
> Thanks.
> Paul
>|||Thank you for the reply. Unfortunately that did not work. I ran the
following command to turn that option on:
EXEC sp_configure 'remote proc trans', 1
I ran the following statements:
BEGIN TRAN
UPDATE D SET
D.Location = 999
FROM LS..DB2TDBM.DEPARTMENT D
WHERE D.ADMRDEPT = 'E01'
COMMIT TRAN
And got the following error, which is the same error when the Remote Proc
Trans is truned off:
"Unable to start a nested transaction for OLE DB provider 'IBMDADB2'. A
nested transaction was required because the XACT_ABORT option was set to OFF."
Any ideas?
Thanks.
Paul
"Wayne Snyder" wrote:
>
> a.. If an application has a local transaction and the option
> REMOTE_PROC_TRANSACTIONS is set ON, calling a remote stored procedure
> escalates the local transaction to a distributed transaction.
> This can be set via sp_configure I beleive... I have never tried this, but
> perhaps it is worth a few minutes of your time..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:A2488655-A223-4C02-AAEF-33AD82485C53@.microsoft.com...
> >
> > Is there anyway to set XACT_ABORT ON for a linked server? We have a
> linked
> > server setup between SQL 2000 and DB2. To use transactions we need to
> turn
> > XACT_ABORT on in every single stored procedure. Is there a way to turn it
> on
> > at the linked server level?
> >
> > Thanks.
> >
> > Paul
> >
> >
>
>
Linked Server - Turn Off Constraints
I want to turn off constraints on all tables on a linked server using:
"SP_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'"
Should I make a connection to the linked server, and/or use OpenQuery to
pull this off?Derek,
That could very easily cause data integrity issues. Are you really sure you
want to do that?
HTH
Jerry
"Derek Hart" <derekmhart@.yahoo.com> wrote in message
news:OZ%235R8seGHA.4948@.TK2MSFTNGP04.phx.gbl...
>I want to turn off constraints on all tables on a linked server using:
> "SP_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'"
> Should I make a connection to the linked server, and/or use OpenQuery to
> pull this off?
>
"SP_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'"
Should I make a connection to the linked server, and/or use OpenQuery to
pull this off?Derek,
That could very easily cause data integrity issues. Are you really sure you
want to do that?
HTH
Jerry
"Derek Hart" <derekmhart@.yahoo.com> wrote in message
news:OZ%235R8seGHA.4948@.TK2MSFTNGP04.phx.gbl...
>I want to turn off constraints on all tables on a linked server using:
> "SP_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'"
> Should I make a connection to the linked server, and/or use OpenQuery to
> pull this off?
>
Subscribe to:
Posts (Atom)