Does Linked Server for MS Access support transaction?

Dear all,

We have connected a Access to a MS SQL Server 2005 as a Linked Server with the followoing settings:

1) Provider: Microsoft Jet 4.0 OLE DB Provider

2) Product Name: Access

3) Data source: X:\XXXXX.mdb

4) Provider string: ;pwd=YYYYYY;

5) Collation Compatible: False

6) Data Access: True

7) Rpc: False

8) Rpc Out: False

9) Use Remote Collation: True

10) Collation Name:

11) Connection Timeout: 0

12) Query Timeout: 0

We found that when cannot have any insert/update/delete statement for this linked server if transaction is began. Otherwise, we will have the following exception.

============

Msg 7390, Level 16, State 2, Line 1
The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ZZZZZZ" does not support the required transaction interface.

===========

We wonder whether the problem is because Access does not support distributed transaction. If so, do you know any workaround will work for us?

Thanks and regards,
William

Hi William,

I have come across your post related to Transaction Interface. Now i had a similar problem while connectiong the the Paradox database using linked server. Can you please suggest me on this Please. Thanks in advance.

Rakesh

|||

You can use pass-through openquery().

e.g.

update openquery(your_access_linkedserver,'select * from tb')

set col1 = 123

...

|||

Access does not provide any transactional support. No Rollbacks or Commits. Period.

Other than eliminating Access, or resort to not making transaction calls in ANY transaction involving Access, I can think of no workaround.

Does Linked Server for MS Access support transaction?

Dear all,

We have connected a Access to a MS SQL Server 2005 as a Linked Server with the followoing settings:

1) Provider: Microsoft Jet 4.0 OLE DB Provider

2) Product Name: Access

3) Data source: X:\XXXXX.mdb

4) Provider string: ;pwd=YYYYYY;

5) Collation Compatible: False

6) Data Access: True

7) Rpc: False

8) Rpc Out: False

9) Use Remote Collation: True

10) Collation Name:

11) Connection Timeout: 0

12) Query Timeout: 0

We found that when cannot have any insert/update/delete statement for this linked server if transaction is began. Otherwise, we will have the following exception.

============

Msg 7390, Level 16, State 2, Line 1
The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ZZZZZZ" does not support the required transaction interface.

===========

We wonder whether the problem is because Access does not support distributed transaction. If so, do you know any workaround will work for us?

Thanks and regards,
William

Hi William,

I have come across your post related to Transaction Interface. Now i had a similar problem while connectiong the the Paradox database using linked server. Can you please suggest me on this Please. Thanks in advance.

Rakesh

|||

You can use pass-through openquery().

e.g.

update openquery(your_access_linkedserver,'select * from tb')

set col1 = 123

...

|||

Access does not provide any transactional support. No Rollbacks or Commits. Period.

Other than eliminating Access, or resort to not making transaction calls in ANY transaction involving Access, I can think of no workaround.

Does Linked Server for MS Access support transaction?

Dear all,

We have connected a Access to a MS SQL Server 2005 as a Linked Server with the followoing settings:

1) Provider: Microsoft Jet 4.0 OLE DB Provider

2) Product Name: Access

3) Data source: X:\XXXXX.mdb

4) Provider string: ;pwd=YYYYYY;

5) Collation Compatible: False

6) Data Access: True

7) Rpc: False

8) Rpc Out: False

9) Use Remote Collation: True

10) Collation Name:

11) Connection Timeout: 0

12) Query Timeout: 0

We found that when cannot have any insert/update/delete statement for this linked server if transaction is began. Otherwise, we will have the following exception.

============

Msg 7390, Level 16, State 2, Line 1
The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ZZZZZZ" does not support the required transaction interface.

===========

We wonder whether the problem is because Access does not support distributed transaction. If so, do you know any workaround will work for us?

Thanks and regards,
William

Hi William,

I have come across your post related to Transaction Interface. Now i had a similar problem while connectiong the the Paradox database using linked server. Can you please suggest me on this Please. Thanks in advance.

Rakesh

|||

You can use pass-through openquery().

e.g.

update openquery(your_access_linkedserver,'select * from tb')

set col1 = 123

...

|||

Access does not provide any transactional support. No Rollbacks or Commits. Period.

Other than eliminating Access, or resort to not making transaction calls in ANY transaction involving Access, I can think of no workaround.

documentation on SQL OLE methods...Where to find?

I've seen a few examples in BOL and various websites where sp_OAMethod is
used to generates scripts, return rows, etc. For example:
SET @.exec_str = 'Databases("'+@.dbname+'").Tables("'+RTRIM(UPPER(@.tbname))+'").Script(74077,"
'+ @.filename +'")'
EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
I can't seem to find any documentation that explains the possible methods
(like databases().Tables().Script).
Can someone point me to where I could find a list of these methods?
Thanks in advancesp_OACreate invokes an extermal program that has a COM OO interface.
The documentation you're looking for will reside with whichever external COM
application you're referring to.
You haevn't given us the name of the particular application you're looking
for in your post, so it's a bit hard to answer this. Your post only gives us
information on a method - perhaps if you go back to whever you got that code
snippet from & give us the part that has "sp_OACreate" in it, we may be able
to help you further.
Regards,
Greg Linwood
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:#$OwJjrsDHA.1680@.TK2MSFTNGP12.phx.gbl...
> I've seen a few examples in BOL and various websites where sp_OAMethod is
> used to generates scripts, return rows, etc. For example:
> SET @.exec_str =>
'Databases("'+@.dbname+'").Tables("'+RTRIM(UPPER(@.tbname))+'").Script(74077,"
> '+ @.filename +'")'
> EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
> I can't seem to find any documentation that explains the possible methods
> (like databases().Tables().Script).
> Can someone point me to where I could find a list of these methods?
> Thanks in advance
>|||Hi Greg,
Thanks for the reply. Here's the sp_OACreate statement:
EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
Thanks,
Tom
"Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote in
message news:uIDLvwrsDHA.2416@.TK2MSFTNGP10.phx.gbl...
> sp_OACreate invokes an extermal program that has a COM OO interface.
> The documentation you're looking for will reside with whichever external
COM
> application you're referring to.
> You haevn't given us the name of the particular application you're looking
> for in your post, so it's a bit hard to answer this. Your post only gives
us
> information on a method - perhaps if you go back to whever you got that
code
> snippet from & give us the part that has "sp_OACreate" in it, we may be
able
> to help you further.
> Regards,
> Greg Linwood
> SQL Server MVP
> "TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
> news:#$OwJjrsDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > I've seen a few examples in BOL and various websites where sp_OAMethod
is
> > used to generates scripts, return rows, etc. For example:
> > SET @.exec_str => >
>
'Databases("'+@.dbname+'").Tables("'+RTRIM(UPPER(@.tbname))+'").Script(74077,"
> > '+ @.filename +'")'
> > EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
> >
> > I can't seem to find any documentation that explains the possible
methods
> > (like databases().Tables().Script).
> >
> > Can someone point me to where I could find a list of these methods?
> >
> > Thanks in advance
> >
> >
>|||Most COM (or Automation) objects come with their own
documentation. For instance, the object hierarchy of SQL-
DMO can be found in SQL Server Books Online. If you need
to interact with an object hierarchy that doesn't seem to
have online/printed documentation, you can try tools such
as OLE-COM Object Viewer that comes with NT Resource kit.
The object viewer allows you browse the object hierarchy
and see all the objects, methods, and proerties.
By the way, if you can stay away from sp_OAxxx stuff, stay
away from it. It's pretty ugly and you can't import the
symbolic constants.
Linchi
>--Original Message--
>I've seen a few examples in BOL and various websites
where sp_OAMethod is
>used to generates scripts, return rows, etc. For example:
>SET @.exec_str =>'Databases("'+@.dbname+'").Tables("'+RTRIM(UPPER(@.tbname))
+'").Script(74077,"
>'+ @.filename +'")'
>EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
>I can't seem to find any documentation that explains the
possible methods
>(like databases().Tables().Script).
>Can someone point me to where I could find a list of
these methods?
>Thanks in advance
>
>.
>

Do you need to be a programmer to use SSIS?

Hello

I've been looking for SSIS examples where you need to import data from a OLE DB datasource and export it to a OLE DB destination source whilst the data in a colum is being transformed with a simple SQL statement.

For example:

SELECT (CONVERT(datetime, Left(Counterdatetime, 23))) datum
FROM CounterData

It seems to me that you have to use the Script Task Component ans start coding in Visual Basic.Net (or whatever)

Am I missing something here or is it normal you have to be a programmer to do simple thins like that?

Many thanks!

If i understand your question correctly, the Script Component may be overkill. You could just drag over the OLE DB Dest and Source. Between the two items in the data flow, you could use a Derrived Column transform or Data Conversion based on what the nuts and bolts of your data conversion. It looks like you just want to do a conversion by your example and you could do that in the Data Conversion transform.

-- Brian

|||

Worf wrote:

Hello

I've been looking for SSIS examples where you need to import data from a OLE DB datasource and export it to a OLE DB destination source whilst the data in a colum is being transformed with a simple SQL statement.

For example:

SELECT (CONVERT(datetime, Left(Counterdatetime, 23))) datum
FROM CounterData

It seems to me that you have to use the Script Task Component ans start coding in Visual Basic.Net (or whatever)

Am I missing something here or is it normal you have to be a programmer to do simple thins like that?

Many thanks!

Absolutely not. You have already done the required transformation in your SQL statement above. If you can do your required transforms in the source then invariably that is what you should do - there's no point in having unnecassary transformations in your data-flow. If you wanted to carry out those same transformations in the data-flow then you would require a Data Conversion component (or possibly the Derived Column component) and there is no "programming" (in the common sense of the word) involved in using those.

-Jamie

|||Good call Jaimie.