How to exclude read-only connections from DTC transactions?

Hi
I'm importing from an Access db into SQL 2005 using SSIS, and I want to execute the package inside a transaction so that I can rollback the import if I get an error. However, it seems the Access connection doesn't support DTC as I get the following error:
[Connection manager "MyConnection"] Error: The connection does not support enlisting in distributed transaction. The error goes away if I set the data flow task's TransactionOption to Supported rather than Required, but obviously I don't then get transactions.
Now, all I want to do is to read from the Access connection so I don't require it to be involved in the transaction. Is there any way I can tell SSIS this, so that it doesn't fall over? Or is there another way to achieve what I want?
On a side note - the read-only "SupportsDTCTransactions" property for the Access connection is set to True even though this is clearly not the case - would this be a bug?
[Also posted to microsoft.private.sqlserver2005.dts - I don't know which is the preferred forum - apologies]
Hi,
I got the same error when I tried to exclude from the distributed transaction a connection to an Excel source. So it seems to be a problem of Microsoft.Jet.OLEDB provider. I also inserted "OLE DB Services = -7;" in the connection string, but it didn't work.|||

Were I in your shoes, I think I'd load into a temporary table (outside of the transaction), then, inside a transaction, load the real table with an "insert into select from" Execute SQL task.

|||Yeah, I can see that would work, but it'd be fairly inefficient once the data volumes get high. I suppose with Access as a source there's not much danger of that - but I wonder how many other data sources are affected by this?
|||

There is same error in my projects. I import data from some dbf files to SQL Server 2005. And I must delete the data from Table A first, then import the data to that table. These two steps shoule be included in a transaction. But SSIS give the error like below:

Error: 0xC0014034 at Package, Connection manager "foxpro": The connection does not support enlisting in distributed transaction.

Error: 0xC0202009 at Package, Connection manager "foxpro": An OLE DB error has occurred. Error code: 0x8000FFFF.

Error: 0xC020801C at 导入CDWArea, OLE DB Source [1]: The AcquireConnection method call to the connection manager "foxpro" failed with error code 0xC0202009.

Error: 0xC004701A at 导入CDWArea, DTS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC020801C

At the beginning, I use the conncetion driver of "Microsoft Jet 4.0", then I change it to "Visual Foxpro 9.0 ole db". But they are useless.

Anyone? Thansk!

How to exclude read-only connections from DTC transactions?

Hi
I'm importing from an Access db into SQL 2005 using SSIS, and I want to execute the package inside a transaction so that I can rollback the import if I get an error. However, it seems the Access connection doesn't support DTC as I get the following error:
[Connection manager "MyConnection"] Error: The connection does not support enlisting in distributed transaction. The error goes away if I set the data flow task's TransactionOption to Supported rather than Required, but obviously I don't then get transactions.
Now, all I want to do is to read from the Access connection so I don't require it to be involved in the transaction. Is there any way I can tell SSIS this, so that it doesn't fall over? Or is there another way to achieve what I want?
On a side note - the read-only "SupportsDTCTransactions" property for the Access connection is set to True even though this is clearly not the case - would this be a bug?
[Also posted to microsoft.private.sqlserver2005.dts - I don't know which is the preferred forum - apologies]
Hi,
I got the same error when I tried to exclude from the distributed transaction a connection to an Excel source. So it seems to be a problem of Microsoft.Jet.OLEDB provider. I also inserted "OLE DB Services = -7;" in the connection string, but it didn't work.|||

Were I in your shoes, I think I'd load into a temporary table (outside of the transaction), then, inside a transaction, load the real table with an "insert into select from" Execute SQL task.

|||Yeah, I can see that would work, but it'd be fairly inefficient once the data volumes get high. I suppose with Access as a source there's not much danger of that - but I wonder how many other data sources are affected by this?
|||

There is same error in my projects. I import data from some dbf files to SQL Server 2005. And I must delete the data from Table A first, then import the data to that table. These two steps shoule be included in a transaction. But SSIS give the error like below:

Error: 0xC0014034 at Package, Connection manager "foxpro": The connection does not support enlisting in distributed transaction.

Error: 0xC0202009 at Package, Connection manager "foxpro": An OLE DB error has occurred. Error code: 0x8000FFFF.

Error: 0xC020801C at 导入CDWArea, OLE DB Source [1]: The AcquireConnection method call to the connection manager "foxpro" failed with error code 0xC0202009.

Error: 0xC004701A at 导入CDWArea, DTS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC020801C

At the beginning, I use the conncetion driver of "Microsoft Jet 4.0", then I change it to "Visual Foxpro 9.0 ole db". But they are useless.

Anyone? Thansk!

How to exclude read-only connections from DTC transactions?

Hi
I'm importing from an Access db into SQL 2005 using SSIS, and I want to execute the package inside a transaction so that I can rollback the import if I get an error. However, it seems the Access connection doesn't support DTC as I get the following error:
[Connection manager "MyConnection"] Error: The connection does not support enlisting in distributed transaction. The error goes away if I set the data flow task's TransactionOption to Supported rather than Required, but obviously I don't then get transactions.
Now, all I want to do is to read from the Access connection so I don't require it to be involved in the transaction. Is there any way I can tell SSIS this, so that it doesn't fall over? Or is there another way to achieve what I want?
On a side note - the read-only "SupportsDTCTransactions" property for the Access connection is set to True even though this is clearly not the case - would this be a bug?
[Also posted to microsoft.private.sqlserver2005.dts - I don't know which is the preferred forum - apologies]
Hi,
I got the same error when I tried to exclude from the distributed transaction a connection to an Excel source. So it seems to be a problem of Microsoft.Jet.OLEDB provider. I also inserted "OLE DB Services = -7;" in the connection string, but it didn't work.|||

Were I in your shoes, I think I'd load into a temporary table (outside of the transaction), then, inside a transaction, load the real table with an "insert into select from" Execute SQL task.

|||Yeah, I can see that would work, but it'd be fairly inefficient once the data volumes get high. I suppose with Access as a source there's not much danger of that - but I wonder how many other data sources are affected by this?
|||

There is same error in my projects. I import data from some dbf files to SQL Server 2005. And I must delete the data from Table A first, then import the data to that table. These two steps shoule be included in a transaction. But SSIS give the error like below:

Error: 0xC0014034 at Package, Connection manager "foxpro": The connection does not support enlisting in distributed transaction.

Error: 0xC0202009 at Package, Connection manager "foxpro": An OLE DB error has occurred. Error code: 0x8000FFFF.

Error: 0xC020801C at 导入CDWArea, OLE DB Source [1]: The AcquireConnection method call to the connection manager "foxpro" failed with error code 0xC0202009.

Error: 0xC004701A at 导入CDWArea, DTS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC020801C

At the beginning, I use the conncetion driver of "Microsoft Jet 4.0", then I change it to "Visual Foxpro 9.0 ole db". But they are useless.

Anyone? Thansk!

sql

How to enable TCP/IP connectons to SqlServer

How to enable remote connections on TCP/IP?

I have just installed for first time SqlServer 2000,
and cannot connect from other host via ODBC.
I give hostname or address but it does not connect.
I checked on my firewall that there is no listenning socket
on port 1433 nor 1440 on sqlserver host.

I can only connect using "(local)" server in ODBC on sqlserver host.

Regards,
Mark"netx" <user72wytnij@.go2.pl> wrote in message
news:42302557$0$3224$f69f905@.mamut2.aster.pl...
> How to enable remote connections on TCP/IP?
> I have just installed for first time SqlServer 2000,
> and cannot connect from other host via ODBC.
> I give hostname or address but it does not connect.
> I checked on my firewall that there is no listenning socket
> on port 1433 nor 1440 on sqlserver host.
> I can only connect using "(local)" server in ODBC on sqlserver host.

start->programs->Microsoft SQL Server->Client Network Utility

then click on TCP/IP in right panel and press 'enable'button', click on
'applay', 'ok' then restart SQL server

Regards,
Fibre Optic|||Fibre Optic (fibre_optic@.go2.pl) writes:
> "netx" <user72wytnij@.go2.pl> wrote in message
> news:42302557$0$3224$f69f905@.mamut2.aster.pl...
>> How to enable remote connections on TCP/IP?
>>
>> I have just installed for first time SqlServer 2000,
>> and cannot connect from other host via ODBC.
>> I give hostname or address but it does not connect.
>> I checked on my firewall that there is no listenning socket
>> on port 1433 nor 1440 on sqlserver host.
>>
>> I can only connect using "(local)" server in ODBC on sqlserver host.
>
> start->programs->Microsoft SQL Server->Client Network Utility
> then click on TCP/IP in right panel and press 'enable'button', click on
> 'applay', 'ok' then restart SQL server

You probably mean the Server Network Utility.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to enable remote connections by code?

How to enable remote connections by code without "SQL Server Surface Area Configuration" ,implement by .net framework?

Thats pretty easy using SMO:

instance.ServerProtocols[protocol].IsEnabled = true;

Whereas protocol is the protocol you want to enable (like "tcp" or "np") ans instance is the server instance you want to configure. I wrote an application, the RemoteconnectionsEnabler which does all this using the SMO library.

Jens K. Suessmeyer

http://www.sqlserver2005.de

How to drop a database in SQL Server 2005 when there are some connections on the database

When there is a conections on a database,then we can't drop the database.But SQL Server 2005 can do it,because SQL Server can close existing conections before drop the database.
I wonder how can i do it too using class SQLCommand and SQLConnection?

Hi
you may run the internal stored procedure SP_Who or SP_Who2 which will retrives all the connections on the server then you may filter the results on your database which you want to drop then using the Kill SPID command you can close the connection to the database
then drop it.|||

HI Eisa:
I do it according to what you said, but i can't close the
connection to the database.

|||

msra_ken, Eisa,

perhaps i had a similar problem. I was generating a test database using a connection string to the master database. I would use a second connection string to the test database to perform some table creations, etc. Then I would try to use the master connection string to then drop the test database, but the test database would say it had connections and it couldn't be dropped.

what solved this problem was setting "Pooling=False" in the connection string to the test database. So, apparently the connection was kept alive in the pool. There are, of course, time/resource allocation draw backs to not using pooling, but in non-performance intensive situations it doesn't make a difference.
Best Wishes,

Rana Ian

|||sands_of_time:
Perhaps the solution you provided may be valid at some time, but often
some other people connect to the database which we want to drop, so,
we can't control other person how to use the connection string. On the other
hand,perhaps other people open the database which we want to drop in the
SQL Server envrionment directly.
SQL Server 2005 can solve this problem,but i don't know how it does so.