Activating Nested Trigger with DTC

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?

Accessing Remotely located SQL Server via Access

Hi,

I need the code to create a connection between the Sql server using ACCESS form.

Can I do this. If yes . can you help mw with it.

Currently I am accessing the server with remote desktop connection

Regards,

Jay

There is a fairly useful resource for this process in Access help under "Import or link SQL database tables or data from other ODBC data sources". Just search for that string in Access help, and there will be a whole bunch of information on this topic. Hopefully, this will provide you with all you need to connect to SQL server.

Hope this helps!

John (MSFT)

|||

There is a fairly useful resource for this process in Access help under "Import or link SQL database tables or data from other ODBC data sources". Just search for that string in Access help, and there will be a whole bunch of information on this topic. Hopefully, this will provide you with all you need to connect to SQL server.

Hope this helps!

John (MSFT)

|||

Open the destination database (access database which you want linked to SQL Server)
On the External Data tab, in the Import group, click More.
Click ODBC Database.
Click Link to the data source by creating a linked table, and then click OK.

Click New to create a new data source name (DSN).
The Create New Data Source Wizard starts.

In the wizard, select SQL Server in the list of drivers, and then click Next.

Click Next, review the summary information, and then click Finish to complete the Create New Data Source Wizard.
The Create a New Data Source to SQL Server Wizard starts.

In the wizard, type a description of the data source in the Description box. This step is optional.
Under Which SQL Server do you want to connect to, in the Server box, type or select the name of the SQL Server computer to which you want to connect, and then click Next to continue.
On this page of the wizard, you might need to get information from the SQL Server database administrator, such as whether to use Windows NT authentication or SQL Server authentication. Click Next to continue.
On the next page of the wizard, you might need to get more information from the SQL Server database administrator. If you want to connect to a specific database, ensure that the Change the default database to check box is selected, select the SQL Server database that you want to work with, and then click Next.
Click Finish. Review the summary information, and then click Test Data Source.
Review the test results, and then click OK to close the SQL Server ODBC Data Source Test dialog box.
If the test was successful, click OK again to complete the wizard, or click Cancel to return to the wizard and make changes to your settings.

Click OK.
Access displays the Link Tables dialog box.

Under Tables, click each table or view that you want to link to, and then click OK.

Regards,

Designing-Systems.com

Accessing MSDE from Query Manager

Hey all,
Is it possible to use Query Manager (Sql Server 2000) to
access an instance of MSDE that's located on my Virtual
PC? I'm having trouble logging into it and was just
wondering if this was even possible?
Thanks in advance,
rodchar
If MSDE is configured to listen on named pipes and/or TCP/IP and you can
access the Virtual PC remotely then you should be able to connect.
Go to Start - Run and enter svrnetcn.exe. This will open Server Network
Utility. Verify that named pipes and/or TCP/IP are enabled. If not enable
them and stop and restart MSDE.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Also make sure that your MSDE is set up for the authentication you are
using to connect to it. For more information, refer to
325022 INFO: MSDE Security and Authentication
http://support.microsoft.com/?id=325022
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Also make sure that your MSDE is set up for the authentication you are
using to connect to it. For more information, refer to
325022 INFO: MSDE Security and Authentication
http://support.microsoft.com/?id=325022
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.