Hello,
I built a trigger that activates another trigger (Nested Trigger).
The First trigger which is located on table A inserts data into table B and in return the second trigger which is located on table B updates data in table A.
The whole process works fine when both tables are on the same server but when the tables are on different servers i get the following error:
"[Microsoft][ODBC SQL Server Driver][SQL Server]The operation could not be performed because the OLE DB Provider 'SQLOLEDB' was unable to begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator.]"
Any ideas?Under the server properties goto connection tab and check option for 'Enforce Distributed Transactions (DTS)", make sure to uncheck the option.|||I tried it and it didn't work - I even got the same error message.
Any more ideas?|||Make sure to check whether MSDTC is running on all involved servers.|||I checked the servers and MS-DTC runs on both of them.
Could it be connected to the settings of MS-DTC? something in the settings of MS-DTC that doesn't allow a trigger to activate another trigger?|||It always helps try to refresh the services and MSDTC to get it worked.|||no, sorry, still doesn't help
I stopped and played MS-DTC on both servers + refreshed services but still no good|||I'm out of ideas now in this regard, BTW what is the SP Level?
You may refer the case of MS Support for a fix or search under KB for any fix.|||Both servers are running MS-SQL 2K, SP3
Thanks for your time,
I'll update on any solution, if will be founded..|||For information refer thru:
run SP_SERVEROPTION from QA and deliver the result.
Try to implement :
SET ANSI_DEFAULTS ON
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET XACT_ABORT ON
.... before the transaction begins...|||all parameters were already set as mentioned.
sp_serveroption enables changing several server parameters, which one did u mean?
Showing posts with label built. Show all posts
Showing posts with label built. Show all posts
Sunday, March 11, 2012
Sunday, February 19, 2012
Accessing SSIS on servers with only Named Instances
Here's my problem:
I've entered a set of database maintenance plans in SQL 2005 and would like to export them to other servers. I've built four database servers on this Windows 2003 server, all named instances. One is SQL 2000, the others are all SQL 2005. SSIS is running on this Windows server using the server name, since it doesn't support multiple instances. The maintenance plans are all functioning properly.
I want to export the maintenance plans to other servers and the research I've done directs me to go into Integration Services, open up the MSDB database and select the packages there, and select export. The problem is that when I attempt to open up MSDB I always get a connection failure. I don't know which of the SQL Servers the maintenance plans are loaded into (well, I have a hunch, but it's very possible I'm wrong) and I can't figure out how to get the SSIS server opened up properly in Management Studio. I've tried changing the MsDtsSrvr.ini.xml file and setting the <ServerName>.</ServerName> value from "."to the name of the server I think contains the data, but that caused Integration Services to not come up after the restart.
The maintenance plans I developed were quite complex due to error handling steps I included, and I'd really rather not have to build them manually for each of my servers here.
Any assistance will be greatly appreciated.
Well, I solved the problem myself, by just slowing down a bit. I stopped Integration Services on the Windows Server, changed the MsDtsSrvr.ini.xml file <ServerName> setting to the name of the server I thought contained the maintenance plans, then restarted Integration Services. After that I was able to successfully connect to the SSIS and export the maintenance plans by opening up the MSDB database, drilling down to the maintenance plan I wanted to export, right-clicking, then selecting Export. I then entered the server name of the destination server (database server, not SSIS) and drilled down to the Maintenance Plans folder, and clicking OK.
The maint plans are now where I wanted them, without having to rebuild them for each server.
Thursday, February 16, 2012
Accessing SQL Server Databases from Networked PC
Hello all. I have just built another PC (New PC)and have it connected to my
Original PC via a LAN. I have my SQL Server 2000 installed on the Original
PC. Both PCs use WinXP Pro OS. I believe I now have to install the Client
Tools on the New PC but after that, how do I access the SQL Server that is
on the Original PC. The SQL Server is setup with mixed (both Windows
Authentication and SQL Server Authentication). Just how do I use the SQL
Query Analyzer that I just installed on the New PC to connect to and
retreive data from the SQL Databases which are on the Original PC? Thanks
in advance for any help, Jim.Hello all. Dissregard this request for assistance as I was able to figure it
out. I can now connect the New PC to the SQL Server in the Original PC,
write my query and the results are displayed on the New PC over the Network.
Have a good day. Jim.
Original PC via a LAN. I have my SQL Server 2000 installed on the Original
PC. Both PCs use WinXP Pro OS. I believe I now have to install the Client
Tools on the New PC but after that, how do I access the SQL Server that is
on the Original PC. The SQL Server is setup with mixed (both Windows
Authentication and SQL Server Authentication). Just how do I use the SQL
Query Analyzer that I just installed on the New PC to connect to and
retreive data from the SQL Databases which are on the Original PC? Thanks
in advance for any help, Jim.Hello all. Dissregard this request for assistance as I was able to figure it
out. I can now connect the New PC to the SQL Server in the Original PC,
write my query and the results are displayed on the New PC over the Network.
Have a good day. Jim.
"Jim Richards" <JWRichards@.satx.rr.com> wrote in message
news:RGoOd.38259$uA.22293@.fe1.texas.rr.com...
> Hello all. I have just built another PC (New PC)and have it connected to
> my Original PC via a LAN. I have my SQL Server 2000 installed on the
> Original PC. Both PCs use WinXP Pro OS. I believe I now have to install
> the Client Tools on the New PC but after that, how do I access the SQL
> Server that is on the Original PC. The SQL Server is setup with mixed
> (both Windows Authentication and SQL Server Authentication). Just how do I
> use the SQL Query Analyzer that I just installed on the New PC to connect
> to and retreive data from the SQL Databases which are on the Original PC?
> Thanks in advance for any help, Jim.
Accessing SQL Server Accross Domain
I have a concept for an application that I would like to
market, and have a question concerning configuration. My
user interface is built in ADO.Net and VB7, and this is
the part I would like to sell. The end user would use
this UI to access a SQL Server database that I would have
at my location, on my own domain. Is this concept
feasible? Is it possible to grant access on SQL Server to
users from different domains? I am using Windows
Integrated Authentication on my server, and have to as it
is being used with BizTalk Server. Thanks.
JTHi JT,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
As my understanding of your question, you want to know if you client
application in their domain could access the SQL Server in another domain,
right. If I misunderstood, please feel free to let me know.
To access the SQL Server across domain is possible.
At first, make sure the connection between this two computers is OK.
Please refer to this article:
http://support.microsoft.com/?id=238949
You can test the application environment by the following steps to test
the connectivity:
To test general connectivity:
PING < SQL_Server ip>
To test we can connect to default SQL Server port 1433:
TELNET < SQL_Server ip> 1433
If we can connect, the screen will go blank. The server is awaiting more
commands. Hit any key to return.
If we CANNOT connect, you will get an error:
"Connecting To < ip > ..Could not open connection to the host, on port 1433
No connection could be made because the target machine actively refused
it."
NOTE:
Port 1433 is the default port number for SQL Server. You can check the
actual port number by Server Network Utility. You can get the actual port
number in use by check the properties of TCP/IP (which should be in the
"Enabled Protocols").
Then, use your Query Analyzer to check if it can access the database from
one domain to another.
For information you could refer to or ADO.NET programming, you can refer
to the following materials or ask questions in related newsgroup:
http://support.microsoft.com/?id=216415
http://msdn.microsoft.com/library/d...-us/dnauth/html
/dnauth_security.asp
http://support.microsoft.com/defaul...microsoft.com:
80/support/kb/articles/Q193/1/35.ASP&NoWebContent=1
http://support.microsoft.com/defaul...b;en-us;q176379
Hope this answered your questions. If you still have questions, please feel
free to post new message here and I am ready to help.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks Baisong,
What type of Authentication does the distant user need to
use? Is it possible for them to use Windows Auth, or do
the need to use SQL Server Auth? Thanks.
JT
assist you with
you client
in another domain,
know.
computers is OK.
following steps to test
is awaiting more
host, on port 1433
actively refused
can check the
the actual port
should be in the
the database from
programming, you can refer
newsgroup:
url=/library/en-us/dnauth/html
scid=http://support.microsoft.com:
us;q176379
questions, please feel
confers no rights.
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
Both Windows authentication and SQL Server authentication could be used
when your application access the SQL Server across domains. For Windows
Authentication Mode, when the domain that the SQL Server runs in trust the
domain of the application, it can access the SQL Server. For SQL
Authentication Mode, no relation with domain conception, you could add the
SQL Server account and your application could access the SQL Server through
IP.
Please refer to this article, which provide you detailed information of
programming:
HOW TO: Set Up SQL Server with Proxy Server
http://support.microsoft.com/defaul...kb;EN-US;216415
INF: TCP Ports Needed for Communication to SQL Server Through a Firewall
http://support.microsoft.com/?id=287932
HOWTO: Use ADO to Connect to a SQL Server That Is Behind a Firewall
http://support.microsoft.com/defaul...kb;EN-US;269882
Connecting to a SQL Server Data Source
http://msdn.microsoft.com/library/d...-us/adosql/adop
rg01_0ahx.asp
INF: Authentication Methods for Connections to SQL Server in Active Server
Pages
http://support.microsoft.com/?id=247931
Hope this information will be useful to your concern. If you still have
question, please feel free to post new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thank you very much.
JT
assist you with
authentication could be used
domains. For Windows
runs in trust the
For SQL
you could add the
SQL Server through
information of
US;216415
Through a Firewall
a Firewall
US;269882
url=/library/en-us/adosql/adop
Server in Active Server
you still have
I am ready to help!
confers no rights.
market, and have a question concerning configuration. My
user interface is built in ADO.Net and VB7, and this is
the part I would like to sell. The end user would use
this UI to access a SQL Server database that I would have
at my location, on my own domain. Is this concept
feasible? Is it possible to grant access on SQL Server to
users from different domains? I am using Windows
Integrated Authentication on my server, and have to as it
is being used with BizTalk Server. Thanks.
JTHi JT,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
As my understanding of your question, you want to know if you client
application in their domain could access the SQL Server in another domain,
right. If I misunderstood, please feel free to let me know.
To access the SQL Server across domain is possible.
At first, make sure the connection between this two computers is OK.
Please refer to this article:
http://support.microsoft.com/?id=238949
You can test the application environment by the following steps to test
the connectivity:
To test general connectivity:
PING < SQL_Server ip>
To test we can connect to default SQL Server port 1433:
TELNET < SQL_Server ip> 1433
If we can connect, the screen will go blank. The server is awaiting more
commands. Hit any key to return.
If we CANNOT connect, you will get an error:
"Connecting To < ip > ..Could not open connection to the host, on port 1433
No connection could be made because the target machine actively refused
it."
NOTE:
Port 1433 is the default port number for SQL Server. You can check the
actual port number by Server Network Utility. You can get the actual port
number in use by check the properties of TCP/IP (which should be in the
"Enabled Protocols").
Then, use your Query Analyzer to check if it can access the database from
one domain to another.
For information you could refer to or ADO.NET programming, you can refer
to the following materials or ask questions in related newsgroup:
http://support.microsoft.com/?id=216415
http://msdn.microsoft.com/library/d...-us/dnauth/html
/dnauth_security.asp
http://support.microsoft.com/defaul...microsoft.com:
80/support/kb/articles/Q193/1/35.ASP&NoWebContent=1
http://support.microsoft.com/defaul...b;en-us;q176379
Hope this answered your questions. If you still have questions, please feel
free to post new message here and I am ready to help.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks Baisong,
What type of Authentication does the distant user need to
use? Is it possible for them to use Windows Auth, or do
the need to use SQL Server Auth? Thanks.
JT
quote:
>--Original Message--
>Hi JT,
>Thank you for using MSDN Newsgroup! It's my pleasure to
assist you with
quote:
>your issue.
>As my understanding of your question, you want to know if
you client
quote:
>application in their domain could access the SQL Server
in another domain,
quote:
>right. If I misunderstood, please feel free to let me
know.
quote:
>To access the SQL Server across domain is possible.
>At first, make sure the connection between this two
computers is OK.
quote:
>Please refer to this article:
>http://support.microsoft.com/?id=238949
>You can test the application environment by the
following steps to test
quote:
>the connectivity:
>To test general connectivity:
> PING < SQL_Server ip>
>To test we can connect to default SQL Server port 1433:
> TELNET < SQL_Server ip> 1433
>If we can connect, the screen will go blank. The server
is awaiting more
quote:
>commands. Hit any key to return.
>If we CANNOT connect, you will get an error:
>"Connecting To < ip > ..Could not open connection to the
host, on port 1433
quote:
> No connection could be made because the target machine
actively refused
quote:
>it."
>NOTE:
>Port 1433 is the default port number for SQL Server. You
can check the
quote:
>actual port number by Server Network Utility. You can get
the actual port
quote:
>number in use by check the properties of TCP/IP (which
should be in the
quote:
>"Enabled Protocols").
>Then, use your Query Analyzer to check if it can access
the database from
quote:
>one domain to another.
>For information you could refer to or ADO.NET
programming, you can refer
quote:
>to the following materials or ask questions in related
newsgroup:
quote:
>http://support.microsoft.com/?id=216415
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnauth/html
quote:
>/dnauth_security.asp
>http://support.microsoft.com/default.aspx?
scid=http://support.microsoft.com:
quote:
>80/support/kb/articles/Q193/1/35.ASP&NoWebContent=1
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;q176379
quote:
>Hope this answered your questions. If you still have
questions, please feel
quote:
>free to post new message here and I am ready to help.
>
>Best regards
>Baisong Wei
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
quote:|||Hi JT,
>Please reply to newsgroups only. Thanks.
>
>
>.
>
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
Both Windows authentication and SQL Server authentication could be used
when your application access the SQL Server across domains. For Windows
Authentication Mode, when the domain that the SQL Server runs in trust the
domain of the application, it can access the SQL Server. For SQL
Authentication Mode, no relation with domain conception, you could add the
SQL Server account and your application could access the SQL Server through
IP.
Please refer to this article, which provide you detailed information of
programming:
HOW TO: Set Up SQL Server with Proxy Server
http://support.microsoft.com/defaul...kb;EN-US;216415
INF: TCP Ports Needed for Communication to SQL Server Through a Firewall
http://support.microsoft.com/?id=287932
HOWTO: Use ADO to Connect to a SQL Server That Is Behind a Firewall
http://support.microsoft.com/defaul...kb;EN-US;269882
Connecting to a SQL Server Data Source
http://msdn.microsoft.com/library/d...-us/adosql/adop
rg01_0ahx.asp
INF: Authentication Methods for Connections to SQL Server in Active Server
Pages
http://support.microsoft.com/?id=247931
Hope this information will be useful to your concern. If you still have
question, please feel free to post new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thank you very much.
JT
quote:
>--Original Message--
>Hi JT,
>Thank you for using MSDN Newsgroup! It's my pleasure to
assist you with
quote:
>your issue.
>Both Windows authentication and SQL Server
authentication could be used
quote:
>when your application access the SQL Server across
domains. For Windows
quote:
>Authentication Mode, when the domain that the SQL Server
runs in trust the
quote:
>domain of the application, it can access the SQL Server.
For SQL
quote:
>Authentication Mode, no relation with domain conception,
you could add the
quote:
>SQL Server account and your application could access the
SQL Server through
quote:
>IP.
>Please refer to this article, which provide you detailed
information of
quote:
>programming:
>HOW TO: Set Up SQL Server with Proxy Server
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;216415
quote:
>INF: TCP Ports Needed for Communication to SQL Server
Through a Firewall
quote:
>http://support.microsoft.com/?id=287932
>HOWTO: Use ADO to Connect to a SQL Server That Is Behind
a Firewall
quote:
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;269882
quote:
>Connecting to a SQL Server Data Source
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adosql/adop
quote:
>rg01_0ahx.asp
>INF: Authentication Methods for Connections to SQL
Server in Active Server
quote:
>Pages
>http://support.microsoft.com/?id=247931
>Hope this information will be useful to your concern. If
you still have
quote:
>question, please feel free to post new message here and
I am ready to help!
quote:
>Best regards
>Baisong Wei
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
quote:
>Please reply to newsgroups only. Thanks.
>.
>
Subscribe to:
Posts (Atom)