SQL About AMO: linked

Showing posts with label linked. Show all posts

Sunday, March 25, 2012

Access dabase linked to SQL Server

Hi,

I have an Access database (name "Shipping") linked to SQL Server. The Access database is not secured. My access file is located on the same machine as the instance of SQL Server, in a shared folder that can be accessed by any user. The file permissions are set to full control for Everyone. I linked Access with the following procedure:

EXEC sp_addlinkedserver
@.server = 'Shipping',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'C:\Shipping\Shipping BackEnd.mdb'

then modified the linked server login mapping with:

EXEC sp_addlinkedsrvlogin 'Shipping','false',NULL,'Admin',NULL

Now, since I have domain administrator permissions, I can run a stored procedure in SQL Server that does a Select in the Access database without any problem. But when a normal user tries to run the procedure, he gets an error #7399. If I give this user domain administrator rights, he's able to run the procedure. There is something I don't get, with normal user permission, this user can open the Access database through the shared folder...

Thanks

-SteveStraight from BOL:

Error 7399
Severity Level 16
Message Text
OLE DB provider '%ls' reported an error. %ls

Cannot start your application. The workgroup information file is missing or opened exclusively by another user.

Explanation

This error message returned by the Microsoft OLE DB Provider for Jet indicates one of the following:

The Microsoft Access database is not a secured database and the login and password specified was not Admin with no password.

The Access database is secured and the HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Syst emDB registry key is not pointing to the correct Access workgroup file. Secured Access databases have a corresponding workgroup file, including the full path, which should be indicated by the above registry key.

Action

Verify that there is a login mapping for the current Microsoft SQL Server login to Admin with no password.

If the Access database being accessed is secured, make sure that the above registry key points to the full pathname of the Access workgroup file.|||Thanks, but I've already checked the BOL and it didn't help me:

1- The Access database is not secured so I shouldn't have to worry about the workgroup information file
2- I already made the login mapping to Access user 'Admin' with no password and it didn't fix the problem => EXEC sp_addlinkedsrvlogin 'Shipping','false',NULL,'Admin',NULL
3- Everything is working great with Windows users with Administrator rights. So it seems to be a Windows permission problem even though I gave Full control permission to everyone on the Access file. This file is in a Shared Folder on the server and anyone can open it...

Access Crashes Filtering a Linked SQL Table on a date field

I have an MS Access 2002 application that is distributed to a number of PCs around our office. The data for this application is stored on a central SQL Server that is linked in through ODBC.

This application has been in place for two years and working fine. We recently formatted and restored a PC, and now that particular PC has issues with the Access application.

Every time it tries to filter one of the linked SQL tables on a date field, Access goes unresponsive and GPFs out. If it's in a query that is behind a report, I get the old standard 'Catastrophic Failure'. If I open the table and right-click filter or run a query manually, Access GPFs.

I've tried recreating the ODBC, linking the tables through TCP/IP as well as Named Pipes. Nothing fixes it. All Windows and Office updates have been applied. This is not the first time we've reformatted a PC in the office, but we've never had this issue.

Has anyone run across this before?

Thanks!

-BenWhat is the operating system on the new PC?
Is is connecting directly to the network?
What is the SQL Server ODBC driver version #?

The other thing I would do is find the developer that designed the application in Access and smack him in the back of the head for trying to develop a multi-user application in Access.

There are many many other alternatives that would work better and be much much faster.

Access Connection

I have a linked server connection from sql server 2000 to access...once
someone puts a password on an access database does anyone know what the
default username is. I have tried Admin and Administrator with no luck while
using the correct password of course
thanks
Sammy
Did you try 'sa'. If no one else on this discussion alias replies with
other suggestions, perhaps you would get one from the Microsoft Access
alias.
| Thread-Topic: Access Connection
| thread-index: AcWpgVenySe/YO83T+K2XLbswonlNQ==
| X-WBNR-Posting-Host: 212.56.83.30
| From: "=?Utf-8?B?U2FtbXk=?=" <Sammy@.discussions.microsoft.com>
| Subject: Access Connection
| Date: Thu, 25 Aug 2005 07:29:03 -0700
| Lines: 9
| Message-ID: <D3D44E5D-E5B1-474B-8288-CB7436020643@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2615
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
|
| I have a linked server connection from sql server 2000 to access...once
| someone puts a password on an access database does anyone know what the
| default username is. I have tried Admin and Administrator with no luck
while
| using the correct password of course
|
| thanks
|
| Sammy
|

Access Connection

I have a linked server connection from sql server 2000 to access...once
someone puts a password on an access database does anyone know what the
default username is. I have tried Admin and Administrator with no luck while
using the correct password of course
thanks
SammyDid you try 'sa'. If no one else on this discussion alias replies with
other suggestions, perhaps you would get one from the Microsoft Access
alias.
--
| Thread-Topic: Access Connection
| thread-index: AcWpgVenySe/YO83T+K2XLbswonlNQ==
| X-WBNR-Posting-Host: 212.56.83.30
| From: "examnotes" <Sammy@.discussions.microsoft.com>
| Subject: Access Connection
| Date: Thu, 25 Aug 2005 07:29:03 -0700
| Lines: 9
| Message-ID: <D3D44E5D-E5B1-474B-8288-CB7436020643@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2615
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
|
| I have a linked server connection from sql server 2000 to access...once
| someone puts a password on an access database does anyone know what the
| default username is. I have tried Admin and Administrator with no luck
while
| using the correct password of course
|
| thanks
|
| Sammy
|

Access Combo Box Linked to Stored Proc

Does anyone for code code to link a stored procedures to a combo box in Access?A list of available stored procedures to execute or something else?

Thursday, March 22, 2012

Access 97 to SQL Conversion

Access 97 to SQL

I have an Access 97 program that for many years linked to tables in another
database through drive letter access. Recently, because of speed, we
converted the Access tables to a SQL Server 2000 database and linked the
tables via an ODBC connection.
The program can read the tables but there are several problems. The append
queries that are trying to build new records in one of the SQL tables keep
giving a key violation error. Also, a few of the screens used to append
records to some of the tables no longer let me add records. It will show the
records that were originally imported, and let me delete them, but not add.
The first time I converted, many of the Access queries wouldn't work and we
discovered that in the conversion, the Autonumbering in many of the tables
got lost. Since then, I have set the Identity to Yes and when viewing the
design of the ODBC tables through Access, they are now showing as Autonumber.
Is there some incompatibility between Access 97 and SQL server 2000, or do I
have the wrong data types in the SQL tables? Is there something wrong with
the way I set up Autonumbering in SQL? When the data was all in Access, I
had no problem appending records, but now that the data is in SQL.... what
am I missing?
Please help!
Thanks,
DianneDid you set up logons and permissions on the sql tables for your users?
You should check all your indexes and relationships as well. Did you use the
Access Upsize Wizard to convert the tables? If so, you will want to
definately go thru each table to check indexes and relationships, and make
sure you have your identity columns set up properly.
There's good book on this "Microsoft Access Developer's Guide to SQL Server"
which I recommend you read. There's a fair bit of learning required to make
this move. I did it several years ago, but read the above book first.
TomT
"Dianne" wrote:
> I have an Access 97 program that for many years linked to tables in another
> database through drive letter access. Recently, because of speed, we
> converted the Access tables to a SQL Server 2000 database and linked the
> tables via an ODBC connection.
> The program can read the tables but there are several problems. The append
> queries that are trying to build new records in one of the SQL tables keep
> giving a key violation error. Also, a few of the screens used to append
> records to some of the tables no longer let me add records. It will show the
> records that were originally imported, and let me delete them, but not add.
> The first time I converted, many of the Access queries wouldn't work and we
> discovered that in the conversion, the Autonumbering in many of the tables
> got lost. Since then, I have set the Identity to Yes and when viewing the
> design of the ODBC tables through Access, they are now showing as Autonumber.
> Is there some incompatibility between Access 97 and SQL server 2000, or do I
> have the wrong data types in the SQL tables? Is there something wrong with
> the way I set up Autonumbering in SQL? When the data was all in Access, I
> had no problem appending records, but now that the data is in SQL.... what
> am I missing?
> Please help!
> Thanks,
> Dianne
>|||I have set the logons and permissions on the sql tables on the table level.
Do I also have to go into each column and check the boxes, or does it cover
each column on the table level?
I also could not locate the Access Upsize wizard as part of Access 97. Is
that from a newer version of Access?
And, thank you, I have already ordered the book at your suggestion.
Dianne
"TomT" wrote:
> Did you set up logons and permissions on the sql tables for your users?
> You should check all your indexes and relationships as well. Did you use the
> Access Upsize Wizard to convert the tables? If so, you will want to
> definately go thru each table to check indexes and relationships, and make
> sure you have your identity columns set up properly.
> There's good book on this "Microsoft Access Developer's Guide to SQL Server"
> which I recommend you read. There's a fair bit of learning required to make
> this move. I did it several years ago, but read the above book first.
> TomT
> "Dianne" wrote:
> > I have an Access 97 program that for many years linked to tables in another
> > database through drive letter access. Recently, because of speed, we
> > converted the Access tables to a SQL Server 2000 database and linked the
> > tables via an ODBC connection.
> >
> > The program can read the tables but there are several problems. The append
> > queries that are trying to build new records in one of the SQL tables keep
> > giving a key violation error. Also, a few of the screens used to append
> > records to some of the tables no longer let me add records. It will show the
> > records that were originally imported, and let me delete them, but not add.
> >
> > The first time I converted, many of the Access queries wouldn't work and we
> > discovered that in the conversion, the Autonumbering in many of the tables
> > got lost. Since then, I have set the Identity to Yes and when viewing the
> > design of the ODBC tables through Access, they are now showing as Autonumber.
> >
> > Is there some incompatibility between Access 97 and SQL server 2000, or do I
> > have the wrong data types in the SQL tables? Is there something wrong with
> > the way I set up Autonumbering in SQL? When the data was all in Access, I
> > had no problem appending records, but now that the data is in SQL.... what
> > am I missing?
> >
> > Please help!
> >
> > Thanks,
> >
> > Dianne
> >|||Providing permissions at the table level is sufficient, unless you want more
granular control over specific columns within a table.
I gather from your response you did not use the upsize wizard (as I recall
it was a download from Microsoft, and didn't work all that great anyway). Did
you just import the tables and data within SQL Server then?
If so, you definately need to make sure you have indexes and primary keys
set up, and if you used Autonumber fields in Access, you'll need to set those
columns in SQL Server as identity columns.
Access won't let you modify data in attached tables without a primary key on
the table, so that could very well be your problem.
As you learn more about SQL Server, you will likely want to move much of
your programming/data processing over to the server side, as that will be
much faster than going thru Jet. Also, check into using Pass Through queries
on the MS Access side, they are quick and often a good way to provide data
for reports, etc.
"TomT" wrote:
> Did you set up logons and permissions on the sql tables for your users?
> You should check all your indexes and relationships as well. Did you use the
> Access Upsize Wizard to convert the tables? If so, you will want to
> definately go thru each table to check indexes and relationships, and make
> sure you have your identity columns set up properly.
> There's good book on this "Microsoft Access Developer's Guide to SQL Server"
> which I recommend you read. There's a fair bit of learning required to make
> this move. I did it several years ago, but read the above book first.
> TomT
> "Dianne" wrote:
> > I have an Access 97 program that for many years linked to tables in another
> > database through drive letter access. Recently, because of speed, we
> > converted the Access tables to a SQL Server 2000 database and linked the
> > tables via an ODBC connection.
> >
> > The program can read the tables but there are several problems. The append
> > queries that are trying to build new records in one of the SQL tables keep
> > giving a key violation error. Also, a few of the screens used to append
> > records to some of the tables no longer let me add records. It will show the
> > records that were originally imported, and let me delete them, but not add.
> >
> > The first time I converted, many of the Access queries wouldn't work and we
> > discovered that in the conversion, the Autonumbering in many of the tables
> > got lost. Since then, I have set the Identity to Yes and when viewing the
> > design of the ODBC tables through Access, they are now showing as Autonumber.
> >
> > Is there some incompatibility between Access 97 and SQL server 2000, or do I
> > have the wrong data types in the SQL tables? Is there something wrong with
> > the way I set up Autonumbering in SQL? When the data was all in Access, I
> > had no problem appending records, but now that the data is in SQL.... what
> > am I missing?
> >
> > Please help!
> >
> > Thanks,
> >
> > Dianne
> >|||Yes, I imported the tables through Enterprise Manager in SQL Server. And I
learned the hard way about the primary keys and the indexes, not to mention
the Autonumber. Wish I had asked the right questions before I started.
Thank you for the suggestions about moving the processing over to the SQL
side and using pass through queries - I will certainly need to learn how to
do that.
Dianne
"TomT" wrote:
> Providing permissions at the table level is sufficient, unless you want more
> granular control over specific columns within a table.
> I gather from your response you did not use the upsize wizard (as I recall
> it was a download from Microsoft, and didn't work all that great anyway). Did
> you just import the tables and data within SQL Server then?
> If so, you definately need to make sure you have indexes and primary keys
> set up, and if you used Autonumber fields in Access, you'll need to set those
> columns in SQL Server as identity columns.
> Access won't let you modify data in attached tables without a primary key on
> the table, so that could very well be your problem.
> As you learn more about SQL Server, you will likely want to move much of
> your programming/data processing over to the server side, as that will be
> much faster than going thru Jet. Also, check into using Pass Through queries
> on the MS Access side, they are quick and often a good way to provide data
> for reports, etc.
> "TomT" wrote:
> > Did you set up logons and permissions on the sql tables for your users?
> >
> > You should check all your indexes and relationships as well. Did you use the
> > Access Upsize Wizard to convert the tables? If so, you will want to
> > definately go thru each table to check indexes and relationships, and make
> > sure you have your identity columns set up properly.
> >
> > There's good book on this "Microsoft Access Developer's Guide to SQL Server"
> > which I recommend you read. There's a fair bit of learning required to make
> > this move. I did it several years ago, but read the above book first.
> >
> > TomT
> >
> > "Dianne" wrote:
> >
> > > I have an Access 97 program that for many years linked to tables in another
> > > database through drive letter access. Recently, because of speed, we
> > > converted the Access tables to a SQL Server 2000 database and linked the
> > > tables via an ODBC connection.
> > >
> > > The program can read the tables but there are several problems. The append
> > > queries that are trying to build new records in one of the SQL tables keep
> > > giving a key violation error. Also, a few of the screens used to append
> > > records to some of the tables no longer let me add records. It will show the
> > > records that were originally imported, and let me delete them, but not add.
> > >
> > > The first time I converted, many of the Access queries wouldn't work and we
> > > discovered that in the conversion, the Autonumbering in many of the tables
> > > got lost. Since then, I have set the Identity to Yes and when viewing the
> > > design of the ODBC tables through Access, they are now showing as Autonumber.
> > >
> > > Is there some incompatibility between Access 97 and SQL server 2000, or do I
> > > have the wrong data types in the SQL tables? Is there something wrong with
> > > the way I set up Autonumbering in SQL? When the data was all in Access, I
> > > had no problem appending records, but now that the data is in SQL.... what
> > > am I missing?
> > >
> > > Please help!
> > >
> > > Thanks,
> > >
> > > Dianne
> > >

Access 97 to SQL

Access 97 linked to SQL 2000 large fields problems

Hi there,
I've been slogging away at this one for a while and searching on
Google Groups so I thought perhaps if I ask someone can help.
We have an old Access 97 Db, which we have recently moved to SQL 2000
to make it easier to build a .net web interface. However we still
need to use the old Access 97 interface, so we've linked the tables
using an ODBC file DSN.
Unfortunately the memo fields have come across to SQL (using the DTS)
as Text. This means that when the Access forms try to write back to
the SQL tables they attempt to match all the fields like so:
exec sp_executesql N'UPDATE "dbo"."MyTable"
SET "Name"=@.P1 WHERE "Number" = @.P2 AND "Name" = @.P3',
N'@.P1 varchar(255),@.P2 int,@.P3 varchar(255)',
'NewNameVal', 1, 'OldNameVal'
If the "Name" happens to be "Text" (rather than a varchar as in the
example) then I get the error "The text, ntext, and image data types
cannot be compared or sorted, except when using IS NULL or LIKE
operator.". Fair enough, but I can't change the way the Access tries
to match up the data (can I?).
So I've changed all the text fields into (large) varchars and
re-linked the tables. Now the whole thing just collapses in a heap -
"ODBC--Call failed", and I'm at the end of my tether.
If anyone can help I'd be very grateful.
Tim
Well, from further searching it appears that Access 97 linking to SQL
Server 2000 has this problem when there are "spaces in the fieldnames"
(I generally try to avoid this myself but it's not my database
"design"). Not that the fields with the spaces in the names are the
memo fields themselves but they are in the same table and apparently
that's enough to confuse it (from other posts, confirmed by my
testing). Rather than rename the fields and mess up all the Access
front end (forms and code) as well as the existing web interface,
we're going to upgrade to Access XP which seems to have no such
problems. Job done.
Cheers anyway,
Tim
|||The other issue you may not have realized could cause problems is
basic data type incompatibility between A97 and SQLS 2000, which
supports Unicode. Moving to a more recent version of Access means that
a lot of the incompatibilities won't exist --plus, A97 isn't being
supported anymore :-(
--Mary
On 14 Apr 2004 08:13:06 -0700, temp1999@.yahoo.co.uk (Tim) wrote:

>Well, from further searching it appears that Access 97 linking to SQL
>Server 2000 has this problem when there are "spaces in the fieldnames"
>(I generally try to avoid this myself but it's not my database
>"design"). Not that the fields with the spaces in the names are the
>memo fields themselves but they are in the same table and apparently
>that's enough to confuse it (from other posts, confirmed by my
>testing). Rather than rename the fields and mess up all the Access
>front end (forms and code) as well as the existing web interface,
>we're going to upgrade to Access XP which seems to have no such
>problems. Job done.
>Cheers anyway,
>Tim

Access 97 linked to SQL 2000 large fields problems

Hi there,
I've been slogging away at this one for a while and searching on
Google Groups so I thought perhaps if I ask someone can help.
We have an old Access 97 Db, which we have recently moved to SQL 2000
to make it easier to build a .net web interface. However we still
need to use the old Access 97 interface, so we've linked the tables
using an ODBC file DSN.
Unfortunately the memo fields have come across to SQL (using the DTS)
as Text. This means that when the Access forms try to write back to
the SQL tables they attempt to match all the fields like so:
exec sp_executesql N'UPDATE "dbo"."MyTable"
SET "Name"=@.P1 WHERE "Number" = @.P2 AND "Name" = @.P3',
N'@.P1 varchar(255),@.P2 int,@.P3 varchar(255)',
'NewNameVal', 1, 'OldNameVal'
If the "Name" happens to be "Text" (rather than a varchar as in the
example) then I get the error "The text, ntext, and image data types
cannot be compared or sorted, except when using IS NULL or LIKE
operator.". Fair enough, but I can't change the way the Access tries
to match up the data (can I?).
So I've changed all the text fields into (large) varchars and
re-linked the tables. Now the whole thing just collapses in a heap -
"ODBC--Call failed", and I'm at the end of my tether.
If anyone can help I'd be very grateful.
TimWell, from further searching it appears that Access 97 linking to SQL
Server 2000 has this problem when there are "spaces in the fieldnames"
(I generally try to avoid this myself but it's not my database
"design"). Not that the fields with the spaces in the names are the
memo fields themselves but they are in the same table and apparently
that's enough to confuse it (from other posts, confirmed by my
testing). Rather than rename the fields and mess up all the Access
front end (forms and code) as well as the existing web interface,
we're going to upgrade to Access XP which seems to have no such
problems. Job done.
Cheers anyway,
Tim|||The other issue you may not have realized could cause problems is
basic data type incompatibility between A97 and SQLS 2000, which
supports Unicode. Moving to a more recent version of Access means that
a lot of the incompatibilities won't exist --plus, A97 isn't being
supported anymore :-(
--Mary
On 14 Apr 2004 08:13:06 -0700, temp1999@.yahoo.co.uk (Tim) wrote:

>Well, from further searching it appears that Access 97 linking to SQL
>Server 2000 has this problem when there are "spaces in the fieldnames"
>(I generally try to avoid this myself but it's not my database
>"design"). Not that the fields with the spaces in the names are the
>memo fields themselves but they are in the same table and apparently
>that's enough to confuse it (from other posts, confirmed by my
>testing). Rather than rename the fields and mess up all the Access
>front end (forms and code) as well as the existing web interface,
>we're going to upgrade to Access XP which seems to have no such
>problems. Job done.
>Cheers anyway,
>Timsql

Tuesday, March 20, 2012

Access 2007 linked tables (vs Access 2003)

We migrated a MS Access 2003 mdb into MS Access 2007. The mdb has linked tables to SQL Server via a DSN and utilizes a mdw file. In 2003, the username/password is "passed" to SQL Server, so the UID/PWD that is used for opening the mdb, is used in SQL Server.

Opening the same file in 2007 using the same mdw, gives a secondary login on SQL Server.

Is there a way to have MS Access 2007 pass the UID/PWD to SQL Server on linked tables, the same way that 2003 does?

Thanks!

I hope someone answers this. I would really like to see the answer...

Access 2003 with SQL Express 2005

I am a fresher in using SQL with access.

I am using a code in Ms access to link a table that is in Sql express 2005.

I use this linked table name to download records to the local Access table.

The link is then dropped in Access.

Is there any way to write back records into the linked Sql table from Access.

It does not allow me to delete / add / modify records of a linked table.

How can I do this.

Thanks

Sjohn

YOU will need to specify the primary key on the linked server table that exists in SQL Server. This information is automatically created by default.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
sql

Access 2003 Linked Tables update

Hello,

I am trying to update some SQL-linked tables in my Access database by repoiting the existing linked tables to a new datasource. The problem is, when I go to select the machine data source where the table sits, I get an error message saying the MS Jet Database can't find the object. This is because when Access creates the linked table, it replaces the period in the <schema>.<table_name> with an underscore. So when I go to update the links, it is essentially looking for the new table with the wrong file name.

I have about 80 linked tables to update and I haven't been able to figure a work-around. HELP PLEASE!

Cheers,

Josh

According to http://www.microsoft.com/technet/archive/office/office97/reskit/office97/027.mspx

Renaming Linked Tables

When Access links a remote table, it prefixes the default table owner ID of the SQL Server to each table name. The period separator between the owner ID and the table name is replaced by an underscore because periods in table names are not permitted by Access. Thus, the names of linked tables no longer correspond to the original table names in your MDB file. The simplest way to correct this is to rename your tables to their original names after linking.

Monday, March 19, 2012

Access + ODBC + MS SQL on WAN

Hi, there !
Can anybody tell me why the application MS ACCESS 2002
front-end + MS SQL Server 2000 back-end with the tables
linked via ODBC is running perfectly well on LAN, and does
not run on WAN ?
The firewall and security issues excluded.
Many thanks,
Dmitri KalininWhat do you mean by 'Does not run'
Is it slow? Crashes?
"Dmitri" <kalinin@.dblink.co.nz> wrote in message
news:081301c33f95$46100d80$a101280a@.phx.gbl...
> Hi, there !
> Can anybody tell me why the application MS ACCESS 2002
> front-end + MS SQL Server 2000 back-end with the tables
> linked via ODBC is running perfectly well on LAN, and does
> not run on WAN ?
> The firewall and security issues excluded.
> Many thanks,
> Dmitri Kalinin|||"Dmitri" <kalinin@.dblink.co.nz> wrote in message news:<081301c33f95$46100d80$a101280a@.phx.gbl>...
> Hi, there !
> Can anybody tell me why the application MS ACCESS 2002
> front-end + MS SQL Server 2000 back-end with the tables
> linked via ODBC is running perfectly well on LAN, and does
> not run on WAN ?
> The firewall and security issues excluded.
> Many thanks,
> Dmitri Kalinin
I'd guess there are different network protocols involved somehow.
Or maybe DNS.
Perhaps you need a LMhosts file (update)?

Saturday, February 25, 2012

About SQL2000 SP4 and DTC e Linked Server

Hi,
the scenario is :
- cluster windows 2003 SP1 withc SQL2000 SP4 A/P
- DTC on both server was configured with the modified after the service
pack1
In SQL 2000 i have 2 database:
1- DB1
2- DB2
For the DB2 i have created a linked server and it seems work .
Now i would start a distribuited transaction across DB1 & DB2 , the
stantment SQL is like this :
begin tran
insert into table-1
select top 100 convert(varchar(100),idMov) as Chiav , num from
LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
commit tran
if i run it a error was show :
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I check for MSDTC configuration but it seems right.
I check for this error and i found some KB that indicated a isuee with
MSDTC, so i test it with the DTCTester but the tool tell work all fine !
ARGH!!!!
It's important see that the transaction start on the node1 use a "local"
MSDTC contat a "local" linked server that poit to "local" sql server
instance for use a "local" DB2.
The MSDTC is cluster configuration.
There is any problem to use a ditribuited transaction with a "local" linked
server ?
Anybody have any idea ?
Thanks in advance.
Hi,
You don't mention which KB's you've looked at, but try to see if the 2
links below helps you any further?
http://support.microsoft.com/kb/301600/
http://support.microsoft.com/kb/817064/
Regards
Steen Schlter Persson
Database Administrator / System Administrator
alterx@.noemail.noemail wrote:
> Hi,
> the scenario is :
> - cluster windows 2003 SP1 withc SQL2000 SP4 A/P
> - DTC on both server was configured with the modified after the service
> pack1
>
> In SQL 2000 i have 2 database:
> 1- DB1
> 2- DB2
> For the DB2 i have created a linked server and it seems work .
> Now i would start a distribuited transaction across DB1 & DB2 , the
> stantment SQL is like this :
>
> begin tran
> insert into table-1
> select top 100 convert(varchar(100),idMov) as Chiav , num from
> LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
> commit tran
>
> if i run it a error was show :
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>
> I check for MSDTC configuration but it seems right.
> I check for this error and i found some KB that indicated a isuee with
> MSDTC, so i test it with the DTCTester but the tool tell work all fine !
> ARGH!!!!
> It's important see that the transaction start on the node1 use a "local"
> MSDTC contat a "local" linked server that poit to "local" sql server
> instance for use a "local" DB2.
> The MSDTC is cluster configuration.
> There is any problem to use a ditribuited transaction with a "local"
> linked server ?
>
> Anybody have any idea ?
> Thanks in advance.
>
|||Hello,
Thank you for posting here.
From the problem description of the post you submitted, my understanding
is: when you attempt to start a Distributed Transaction across DB 1 and DB
2, the following error message is received:
########################
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction. [OLE/DB provider returned
message: New transaction cannot enlist in the specified transaction
coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
########################
In addition, you have created a linked server for DB2, which seems work
properly. If I have misunderstood about your concern, feel free to let me
know.
Based on my experience, the following error message can normally be caused
by one of the following
1. Microsoft Distributed Transaction Coordinator (MSDTC) is disabled for
network transactions.
2. Windows Firewall is enabled on the computer. By default, Windows
Firewall blocks the MSDTC program.
3. Communication issue between DB1 and DB2
Note This problem may occur even when Windows Firewall is turned off.
In your post, you have indicated that the MSDTC has been verified by the
DTCTester tool but this tool tell work fine.
First of all, I would like to check whether the object on the destination
server (DB2) refers back to the first server (DB1). This is what is known
as a loopback situation. This is not supported, as documented in SQL Server
Books Online. For more information, visit the following Microsoft Web site:
Loopback Linked Servers
http://msdn2.microsoft.com/en-us/library/aa213286(SQL.80).aspx
Next, lets manually verify your network name resolution works. Verify
that the servers can communicate with one another by name, not just by IP
address. Check in both directions (for example, from DB1 to DB2 and from
DB2 to DB1). You must resolve all name resolution problems on the network
before you run your distributed query. This may involve updating WINS, DNS,
or LMHost files.
If Windows Firewall or other third party firewall is used, please also make
sure that your Remote Procedure Call (RPC) ports are opened correctly.
Below are the steps to configure Windows Firewall to include the MSDTC
program and to include port 135 as an exception. To do this, follow these
steps:
==============================
a. Click Start, and then click Run.
b. In the Run dialog box, type Firewall.cpl , and then click OK
c. In Control Panel, double-click Windows Firewall.
d. In the Windows Firewall dialog box, click Add Program on the Exceptions
tab.
e. In the Add a Program dialog box, click the Browse button, and then
locate the Msdtc.exe file. By default, the file is stored in the
<Installation drive>:\Windows\System32 folder.
f. In the Add a Program dialog box, click OK.
g. In the Windows Firewall dialog box, click to select the msdtc option in
the Programs and Services list.
h. Click Add Port on the Exceptions tab.
i. In the Add a Port dialog box, type 135 in the Port number text box, and
then click to select the TCP option.
j. In the Add a Port dialog box, type a name for the exception in the Name
text box, and then click OK.
k. In the Windows Firewall dialog box, select the name that you used for
the exception in step j in the Programs and Services list, and then click
OK.
In addition, although we have used the DTCTester tool to manually verify
the MSDTC service, it is also recommended that we manually make sure that
the Log On As account for the MSDTC service is the Network Service account
and it allow the network transaction.
For the detail steps, please refer to the STEP ONE and STEP TWO in the
following KB article
You receive error 7391 when you run a distributed transaction against a
linked server in SQL Server 2000 on a computer that is running Windows
Server 2003
http://support.microsoft.com/default.aspx?scid=KB;EN-US;329332
If anything is unclear in my post, please don't hesitate to let me know and
I will be glad to help. Thank you for your efforts and time.
Have a nice day!
Best regards,
Adams Qu, MCSE 2000, MCDBA
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: <alterx@.noemail.noemail>
| Subject: About SQL2000 SP4 and DTC e Linked Server
| Date: Wed, 13 Jun 2007 20:50:41 +0200
| Lines: 60
| Message-ID: <8083FF78-EEF1-4280-ADAB-B497217EA3DF@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| format=flowed;
| charset="iso-8859-1";
| reply-type=original
| Content-Transfer-Encoding: 7bit
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Windows Mail 6.0.6000.16386
| X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16386
| X-MS-CommunityGroup-MessageCategory:
{E4FCE0A9-75B4-4168-BFF9-16C22D8747EC}
| X-MS-CommunityGroup-PostID: {8083FF78-EEF1-4280-ADAB-B497217EA3DF}
| Newsgroups: microsoft.public.sqlserver.server
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:17889
| NNTP-Posting-Host: TK2MSFTNGHUB02.phx.gbl 127.0.0.1
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Hi,
|
| the scenario is :
|
| - cluster windows 2003 SP1 withc SQL2000 SP4 A/P
| - DTC on both server was configured with the modified after the service
| pack1
|
|
| In SQL 2000 i have 2 database:
|
| 1- DB1
| 2- DB2
|
| For the DB2 i have created a linked server and it seems work .
|
| Now i would start a distribuited transaction across DB1 & DB2 , the
| stantment SQL is like this :
|
|
| begin tran
| insert into table-1
| select top 100 convert(varchar(100),idMov) as Chiav , num from
| LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
| commit tran
|
|
|
| if i run it a error was show :
|
| Server: Msg 7391, Level 16, State 1, Line 2
| The operation could not be performed because the OLE DB provider
'SQLOLEDB'
| was unable to begin a distributed transaction.
| [OLE/DB provider returned message: New transaction cannot enlist in the
| specified transaction coordinator. ]
| OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
| ITransactionJoin::JoinTransaction returned 0x8004d00a].
|
|
|
| I check for MSDTC configuration but it seems right.
|
| I check for this error and i found some KB that indicated a isuee with
| MSDTC, so i test it with the DTCTester but the tool tell work all fine !
| ARGH!!!!
|
| It's important see that the transaction start on the node1 use a "local"
| MSDTC contat a "local" linked server that poit to "local" sql server
| instance for use a "local" DB2.
|
| The MSDTC is cluster configuration.
|
| There is any problem to use a ditribuited transaction with a "local"
linked
| server ?
|
|
| Anybody have any idea ?
|
| Thanks in advance.
|
|
|||Hello,
We wanted to see if the information provided was helpful. Please keep us
posted on your progress and let us know if you have any additional
questions or concerns.
We are looking forward to your response.
Have a nice day!
Best regards,
Adams Qu, MCSE 2000, MCDBA
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| X-Tomcat-ID: 190683365
| References: <8083FF78-EEF1-4280-ADAB-B497217EA3DF@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
| From: v-adamqu@.online.microsoft.com (Adams Qu [MSFT])
| Organization: Microsoft
| Date: Thu, 14 Jun 2007 10:16:25 GMT
| Subject: RE: About SQL2000 SP4 and DTC e Linked Server
| X-Tomcat-NG: microsoft.public.sqlserver.server
| Message-ID: <QonFV0mrHHA.644@.TK2MSFTNGHUB02.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| Lines: 187
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:17954
| NNTP-Posting-Host: TOMCATIMPORT1 10.201.218.122
|
| Hello,
|
| Thank you for posting here.
|
| From the problem description of the post you submitted, my understanding
| is: when you attempt to start a Distributed Transaction across DB 1 and
DB
| 2, the following error message is received:
|
| ########################
| Server: Msg 7391, Level 16, State 1, Line 2
| The operation could not be performed because the OLE DB provider
'SQLOLEDB'
| was unable to begin a distributed transaction. [OLE/DB provider returned
| message: New transaction cannot enlist in the specified transaction
| coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
| ITransactionJoin::JoinTransaction returned 0x8004d00a].
| ########################
|
| In addition, you have created a linked server for DB2, which seems work
| properly. If I have misunderstood about your concern, feel free to let me
| know.
|
| Based on my experience, the following error message can normally be
caused
| by one of the following
|
| 1. Microsoft Distributed Transaction Coordinator (MSDTC) is disabled for
| network transactions.
| 2. Windows Firewall is enabled on the computer. By default, Windows
| Firewall blocks the MSDTC program.
| 3. Communication issue between DB1 and DB2
|
| Note This problem may occur even when Windows Firewall is turned off.
|
| In your post, you have indicated that the MSDTC has been verified by the
| DTCTester tool but this tool tell work fine.
|
| First of all, I would like to check whether the object on the destination
| server (DB2) refers back to the first server (DB1). This is what is known
| as a loopback situation. This is not supported, as documented in SQL
Server
| Books Online. For more information, visit the following Microsoft Web
site:
|
| Loopback Linked Servers
| http://msdn2.microsoft.com/en-us/library/aa213286(SQL.80).aspx
|
| Next, lets manually verify your network name resolution works. Verify
| that the servers can communicate with one another by name, not just by IP
| address. Check in both directions (for example, from DB1 to DB2 and from
| DB2 to DB1). You must resolve all name resolution problems on the network
| before you run your distributed query. This may involve updating WINS,
DNS,
| or LMHost files.
|
| If Windows Firewall or other third party firewall is used, please also
make
| sure that your Remote Procedure Call (RPC) ports are opened correctly.
|
| Below are the steps to configure Windows Firewall to include the MSDTC
| program and to include port 135 as an exception. To do this, follow these
| steps:
| ==============================
| a. Click Start, and then click Run.
| b. In the Run dialog box, type Firewall.cpl , and then click OK
| c. In Control Panel, double-click Windows Firewall.
| d. In the Windows Firewall dialog box, click Add Program on the
Exceptions
| tab.
| e. In the Add a Program dialog box, click the Browse button, and then
| locate the Msdtc.exe file. By default, the file is stored in the
| <Installation drive>:\Windows\System32 folder.
| f. In the Add a Program dialog box, click OK.
| g. In the Windows Firewall dialog box, click to select the msdtc option
in
| the Programs and Services list.
| h. Click Add Port on the Exceptions tab.
| i. In the Add a Port dialog box, type 135 in the Port number text box,
and
| then click to select the TCP option.
| j. In the Add a Port dialog box, type a name for the exception in the
Name
| text box, and then click OK.
| k. In the Windows Firewall dialog box, select the name that you used for
| the exception in step j in the Programs and Services list, and then click
| OK.
|
| In addition, although we have used the DTCTester tool to manually verify
| the MSDTC service, it is also recommended that we manually make sure that
| the Log On As account for the MSDTC service is the Network Service
account
| and it allow the network transaction.
|
| For the detail steps, please refer to the STEP ONE and STEP TWO in the
| following KB article
|
| You receive error 7391 when you run a distributed transaction against a
| linked server in SQL Server 2000 on a computer that is running Windows
| Server 2003
| http://support.microsoft.com/default.aspx?scid=KB;EN-US;329332
|
| If anything is unclear in my post, please don't hesitate to let me know
and
| I will be glad to help. Thank you for your efforts and time.
|
| Have a nice day!
|
| Best regards,
|
| Adams Qu, MCSE 2000, MCDBA
| Microsoft Online Support
|
| Microsoft Global Technical Support Center
|
| Get Secure! - www.microsoft.com/security
| ================================================== ===
| When responding to posts, please "Reply to Group" via your newsreader so
| that others may learn and benefit from your issue.
| ================================================== ===
| This posting is provided "AS IS" with no warranties, and confers no
rights.
|
|
| --
| | From: <alterx@.noemail.noemail>
| | Subject: About SQL2000 SP4 and DTC e Linked Server
| | Date: Wed, 13 Jun 2007 20:50:41 +0200
| | Lines: 60
| | Message-ID: <8083FF78-EEF1-4280-ADAB-B497217EA3DF@.microsoft.com>
| | MIME-Version: 1.0
| | Content-Type: text/plain;
| | format=flowed;
| | charset="iso-8859-1";
| | reply-type=original
| | Content-Transfer-Encoding: 7bit
| | X-Priority: 3
| | X-MSMail-Priority: Normal
| | X-Newsreader: Microsoft Windows Mail 6.0.6000.16386
| | X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16386
| | X-MS-CommunityGroup-MessageCategory:
| {E4FCE0A9-75B4-4168-BFF9-16C22D8747EC}
| | X-MS-CommunityGroup-PostID: {8083FF78-EEF1-4280-ADAB-B497217EA3DF}
| | Newsgroups: microsoft.public.sqlserver.server
| | Path: TK2MSFTNGHUB02.phx.gbl
| | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:17889
| | NNTP-Posting-Host: TK2MSFTNGHUB02.phx.gbl 127.0.0.1
| | X-Tomcat-NG: microsoft.public.sqlserver.server
| |
| | Hi,
| |
| | the scenario is :
| |
| | - cluster windows 2003 SP1 withc SQL2000 SP4 A/P
| | - DTC on both server was configured with the modified after the service
| | pack1
| |
| |
| | In SQL 2000 i have 2 database:
| |
| | 1- DB1
| | 2- DB2
| |
| | For the DB2 i have created a linked server and it seems work .
| |
| | Now i would start a distribuited transaction across DB1 & DB2 , the
| | stantment SQL is like this :
| |
| |
| | begin tran
| | insert into table-1
| | select top 100 convert(varchar(100),idMov) as Chiav , num from
| | LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
| | commit tran
| |
| |
| |
| | if i run it a error was show :
| |
| | Server: Msg 7391, Level 16, State 1, Line 2
| | The operation could not be performed because the OLE DB provider
| 'SQLOLEDB'
| | was unable to begin a distributed transaction.
| | [OLE/DB provider returned message: New transaction cannot enlist in the
| | specified transaction coordinator. ]
| | OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
| | ITransactionJoin::JoinTransaction returned 0x8004d00a].
| |
| |
| |
| | I check for MSDTC configuration but it seems right.
| |
| | I check for this error and i found some KB that indicated a isuee with
| | MSDTC, so i test it with the DTCTester but the tool tell work all fine
!
| | ARGH!!!!
| |
| | It's important see that the transaction start on the node1 use a
"local"
| | MSDTC contat a "local" linked server that poit to "local" sql server
| | instance for use a "local" DB2.
| |
| | The MSDTC is cluster configuration.
| |
| | There is any problem to use a ditribuited transaction with a "local"
| linked
| | server ?
| |
| |
| | Anybody have any idea ?
| |
| | Thanks in advance.
| |
| |
|
|

About SQL2000 SP4 and DTC e Linked Server

Hi,
the scenario is :
- cluster windows 2003 SP1 withc SQL2000 SP4 A/P
- DTC on both server was configured with the modified after the service
pack1
In SQL 2000 i have 2 database:
1- DB1
2- DB2
For the DB2 i have created a linked server and it seems work .
Now i would start a distribuited transaction across DB1 & DB2 , the
stantment SQL is like this :
begin tran
insert into table-1
select top 100 convert(varchar(100),idMov) as Chiav , num from
LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
commit tran
if i run it a error was show :
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I check for MSDTC configuration but it seems right.
I check for this error and i found some KB that indicated a isuee with
MSDTC, so i test it with the DTCTester but the tool tell work all fine !
ARGH!!!!
It's important see that the transaction start on the node1 use a "local"
MSDTC contat a "local" linked server that poit to "local" sql server
instance for use a "local" DB2.
The MSDTC is cluster configuration.
There is any problem to use a ditribuited transaction with a "local" linked
server ?
Anybody have any idea ?
Thanks in advance.Hi,
You don't mention which KB's you've looked at, but try to see if the 2
links below helps you any further?
http://support.microsoft.com/kb/301600/
http://support.microsoft.com/kb/817064/
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
alterx@.noemail.noemail wrote:
> Hi,
> the scenario is :
> - cluster windows 2003 SP1 withc SQL2000 SP4 A/P
> - DTC on both server was configured with the modified after the service
> pack1
>
> In SQL 2000 i have 2 database:
> 1- DB1
> 2- DB2
> For the DB2 i have created a linked server and it seems work .
> Now i would start a distribuited transaction across DB1 & DB2 , the
> stantment SQL is like this :
>
> begin tran
> insert into table-1
> select top 100 convert(varchar(100),idMov) as Chiav , num from
> LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
> commit tran
>
> if i run it a error was show :
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>
> I check for MSDTC configuration but it seems right.
> I check for this error and i found some KB that indicated a isuee with
> MSDTC, so i test it with the DTCTester but the tool tell work all fine !
> ARGH!!!!
> It's important see that the transaction start on the node1 use a "local"
> MSDTC contat a "local" linked server that poit to "local" sql server
> instance for use a "local" DB2.
> The MSDTC is cluster configuration.
> There is any problem to use a ditribuited transaction with a "local"
> linked server ?
>
> Anybody have any idea ?
> Thanks in advance.
>|||Hello,
Thank you for posting here.
From the problem description of the post you submitted, my understanding
is: when you attempt to start a Distributed Transaction across DB 1 and DB
2, the following error message is received:
########################
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction. [OLE/DB provider returned
message: New transaction cannot enlist in the specified transaction
coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
########################
In addition, you have created a linked server for DB2, which seems work
properly. If I have misunderstood about your concern, feel free to let me
know.
Based on my experience, the following error message can normally be caused
by one of the following
1. Microsoft Distributed Transaction Coordinator (MSDTC) is disabled for
network transactions.
2. Windows Firewall is enabled on the computer. By default, Windows
Firewall blocks the MSDTC program.
3. Communication issue between DB1 and DB2
Note This problem may occur even when Windows Firewall is turned off.
In your post, you have indicated that the MSDTC has been verified by the
DTCTester tool but this tool tell work fine.
First of all, I would like to check whether the object on the destination
server (DB2) refers back to the first server (DB1). This is what is known
as a loopback situation. This is not supported, as documented in SQL Server
Books Online. For more information, visit the following Microsoft Web site:
Loopback Linked Servers
http://msdn2.microsoft.com/en-us/library/aa213286(SQL.80).aspx
Next, let¡¯s manually verify your network name resolution works. Verify
that the servers can communicate with one another by name, not just by IP
address. Check in both directions (for example, from DB1 to DB2 and from
DB2 to DB1). You must resolve all name resolution problems on the network
before you run your distributed query. This may involve updating WINS, DNS,
or LMHost files.
If Windows Firewall or other third party firewall is used, please also make
sure that your Remote Procedure Call (RPC) ports are opened correctly.
Below are the steps to configure Windows Firewall to include the MSDTC
program and to include port 135 as an exception. To do this, follow these
steps:
==============================a. Click Start, and then click Run.
b. In the Run dialog box, type Firewall.cpl , and then click OK
c. In Control Panel, double-click Windows Firewall.
d. In the Windows Firewall dialog box, click Add Program on the Exceptions
tab.
e. In the Add a Program dialog box, click the Browse button, and then
locate the Msdtc.exe file. By default, the file is stored in the
<Installation drive>:\Windows\System32 folder.
f. In the Add a Program dialog box, click OK.
g. In the Windows Firewall dialog box, click to select the msdtc option in
the Programs and Services list.
h. Click Add Port on the Exceptions tab.
i. In the Add a Port dialog box, type 135 in the Port number text box, and
then click to select the TCP option.
j. In the Add a Port dialog box, type a name for the exception in the Name
text box, and then click OK.
k. In the Windows Firewall dialog box, select the name that you used for
the exception in step j in the Programs and Services list, and then click
OK.
In addition, although we have used the DTCTester tool to manually verify
the MSDTC service, it is also recommended that we manually make sure that
the Log On As account for the MSDTC service is the Network Service account
and it allow the network transaction.
For the detail steps, please refer to the STEP ONE and STEP TWO in the
following KB article
You receive error 7391 when you run a distributed transaction against a
linked server in SQL Server 2000 on a computer that is running Windows
Server 2003
http://support.microsoft.com/default.aspx?scid=KB;EN-US;329332
If anything is unclear in my post, please don't hesitate to let me know and
I will be glad to help. Thank you for your efforts and time.
Have a nice day!
Best regards,
Adams Qu, MCSE 2000, MCDBA
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
| From: <alterx@.noemail.noemail>
| Subject: About SQL2000 SP4 and DTC e Linked Server
| Date: Wed, 13 Jun 2007 20:50:41 +0200
| Lines: 60
| Message-ID: <8083FF78-EEF1-4280-ADAB-B497217EA3DF@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| format=flowed;
| charset="iso-8859-1";
| reply-type=original
| Content-Transfer-Encoding: 7bit
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Windows Mail 6.0.6000.16386
| X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16386
| X-MS-CommunityGroup-MessageCategory:
{E4FCE0A9-75B4-4168-BFF9-16C22D8747EC}
| X-MS-CommunityGroup-PostID: {8083FF78-EEF1-4280-ADAB-B497217EA3DF}
| Newsgroups: microsoft.public.sqlserver.server
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:17889
| NNTP-Posting-Host: TK2MSFTNGHUB02.phx.gbl 127.0.0.1
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Hi,
|
| the scenario is :
|
| - cluster windows 2003 SP1 withc SQL2000 SP4 A/P
| - DTC on both server was configured with the modified after the service
| pack1
|
|
| In SQL 2000 i have 2 database:
|
| 1- DB1
| 2- DB2
|
| For the DB2 i have created a linked server and it seems work .
|
| Now i would start a distribuited transaction across DB1 & DB2 , the
| stantment SQL is like this :
|
|
| begin tran
| insert into table-1
| select top 100 convert(varchar(100),idMov) as Chiav , num from
| LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
| commit tran
|
|
|
| if i run it a error was show :
|
| Server: Msg 7391, Level 16, State 1, Line 2
| The operation could not be performed because the OLE DB provider
'SQLOLEDB'
| was unable to begin a distributed transaction.
| [OLE/DB provider returned message: New transaction cannot enlist in the
| specified transaction coordinator. ]
| OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
| ITransactionJoin::JoinTransaction returned 0x8004d00a].
|
|
|
| I check for MSDTC configuration but it seems right.
|
| I check for this error and i found some KB that indicated a isuee with
| MSDTC, so i test it with the DTCTester but the tool tell work all fine !
| ARGH!!!!
|
| It's important see that the transaction start on the node1 use a "local"
| MSDTC contat a "local" linked server that poit to "local" sql server
| instance for use a "local" DB2.
|
| The MSDTC is cluster configuration.
|
| There is any problem to use a ditribuited transaction with a "local"
linked
| server ?
|
|
| Anybody have any idea ?
|
| Thanks in advance.
|
||||Hello,
We wanted to see if the information provided was helpful. Please keep us
posted on your progress and let us know if you have any additional
questions or concerns.
We are looking forward to your response.
Have a nice day!
Best regards,
Adams Qu, MCSE 2000, MCDBA
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
| X-Tomcat-ID: 190683365
| References: <8083FF78-EEF1-4280-ADAB-B497217EA3DF@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
| From: v-adamqu@.online.microsoft.com (Adams Qu [MSFT])
| Organization: Microsoft
| Date: Thu, 14 Jun 2007 10:16:25 GMT
| Subject: RE: About SQL2000 SP4 and DTC e Linked Server
| X-Tomcat-NG: microsoft.public.sqlserver.server
| Message-ID: <QonFV0mrHHA.644@.TK2MSFTNGHUB02.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| Lines: 187
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:17954
| NNTP-Posting-Host: TOMCATIMPORT1 10.201.218.122
|
| Hello,
|
| Thank you for posting here.
|
| From the problem description of the post you submitted, my understanding
| is: when you attempt to start a Distributed Transaction across DB 1 and
DB
| 2, the following error message is received:
|
| ########################
| Server: Msg 7391, Level 16, State 1, Line 2
| The operation could not be performed because the OLE DB provider
'SQLOLEDB'
| was unable to begin a distributed transaction. [OLE/DB provider returned
| message: New transaction cannot enlist in the specified transaction
| coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
| ITransactionJoin::JoinTransaction returned 0x8004d00a].
| ########################
|
| In addition, you have created a linked server for DB2, which seems work
| properly. If I have misunderstood about your concern, feel free to let me
| know.
|
| Based on my experience, the following error message can normally be
caused
| by one of the following
|
| 1. Microsoft Distributed Transaction Coordinator (MSDTC) is disabled for
| network transactions.
| 2. Windows Firewall is enabled on the computer. By default, Windows
| Firewall blocks the MSDTC program.
| 3. Communication issue between DB1 and DB2
|
| Note This problem may occur even when Windows Firewall is turned off.
|
| In your post, you have indicated that the MSDTC has been verified by the
| DTCTester tool but this tool tell work fine.
|
| First of all, I would like to check whether the object on the destination
| server (DB2) refers back to the first server (DB1). This is what is known
| as a loopback situation. This is not supported, as documented in SQL
Server
| Books Online. For more information, visit the following Microsoft Web
site:
|
| Loopback Linked Servers
| http://msdn2.microsoft.com/en-us/library/aa213286(SQL.80).aspx
|
| Next, let¡¯s manually verify your network name resolution works. Verify
| that the servers can communicate with one another by name, not just by IP
| address. Check in both directions (for example, from DB1 to DB2 and from
| DB2 to DB1). You must resolve all name resolution problems on the network
| before you run your distributed query. This may involve updating WINS,
DNS,
| or LMHost files.
|
| If Windows Firewall or other third party firewall is used, please also
make
| sure that your Remote Procedure Call (RPC) ports are opened correctly.
|
| Below are the steps to configure Windows Firewall to include the MSDTC
| program and to include port 135 as an exception. To do this, follow these
| steps:
| ==============================| a. Click Start, and then click Run.
| b. In the Run dialog box, type Firewall.cpl , and then click OK
| c. In Control Panel, double-click Windows Firewall.
| d. In the Windows Firewall dialog box, click Add Program on the
Exceptions
| tab.
| e. In the Add a Program dialog box, click the Browse button, and then
| locate the Msdtc.exe file. By default, the file is stored in the
| <Installation drive>:\Windows\System32 folder.
| f. In the Add a Program dialog box, click OK.
| g. In the Windows Firewall dialog box, click to select the msdtc option
in
| the Programs and Services list.
| h. Click Add Port on the Exceptions tab.
| i. In the Add a Port dialog box, type 135 in the Port number text box,
and
| then click to select the TCP option.
| j. In the Add a Port dialog box, type a name for the exception in the
Name
| text box, and then click OK.
| k. In the Windows Firewall dialog box, select the name that you used for
| the exception in step j in the Programs and Services list, and then click
| OK.
|
| In addition, although we have used the DTCTester tool to manually verify
| the MSDTC service, it is also recommended that we manually make sure that
| the Log On As account for the MSDTC service is the Network Service
account
| and it allow the network transaction.
|
| For the detail steps, please refer to the STEP ONE and STEP TWO in the
| following KB article
|
| You receive error 7391 when you run a distributed transaction against a
| linked server in SQL Server 2000 on a computer that is running Windows
| Server 2003
| http://support.microsoft.com/default.aspx?scid=KB;EN-US;329332
|
| If anything is unclear in my post, please don't hesitate to let me know
and
| I will be glad to help. Thank you for your efforts and time.
|
| Have a nice day!
|
| Best regards,
|
| Adams Qu, MCSE 2000, MCDBA
| Microsoft Online Support
|
| Microsoft Global Technical Support Center
|
| Get Secure! - www.microsoft.com/security
| =====================================================| When responding to posts, please "Reply to Group" via your newsreader so
| that others may learn and benefit from your issue.
| =====================================================| This posting is provided "AS IS" with no warranties, and confers no
rights.
|
|
| --
| | From: <alterx@.noemail.noemail>
| | Subject: About SQL2000 SP4 and DTC e Linked Server
| | Date: Wed, 13 Jun 2007 20:50:41 +0200
| | Lines: 60
| | Message-ID: <8083FF78-EEF1-4280-ADAB-B497217EA3DF@.microsoft.com>
| | MIME-Version: 1.0
| | Content-Type: text/plain;
| | format=flowed;
| | charset="iso-8859-1";
| | reply-type=original
| | Content-Transfer-Encoding: 7bit
| | X-Priority: 3
| | X-MSMail-Priority: Normal
| | X-Newsreader: Microsoft Windows Mail 6.0.6000.16386
| | X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16386
| | X-MS-CommunityGroup-MessageCategory:
| {E4FCE0A9-75B4-4168-BFF9-16C22D8747EC}
| | X-MS-CommunityGroup-PostID: {8083FF78-EEF1-4280-ADAB-B497217EA3DF}
| | Newsgroups: microsoft.public.sqlserver.server
| | Path: TK2MSFTNGHUB02.phx.gbl
| | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:17889
| | NNTP-Posting-Host: TK2MSFTNGHUB02.phx.gbl 127.0.0.1
| | X-Tomcat-NG: microsoft.public.sqlserver.server
| |
| | Hi,
| |
| | the scenario is :
| |
| | - cluster windows 2003 SP1 withc SQL2000 SP4 A/P
| | - DTC on both server was configured with the modified after the service
| | pack1
| |
| |
| | In SQL 2000 i have 2 database:
| |
| | 1- DB1
| | 2- DB2
| |
| | For the DB2 i have created a linked server and it seems work .
| |
| | Now i would start a distribuited transaction across DB1 & DB2 , the
| | stantment SQL is like this :
| |
| |
| | begin tran
| | insert into table-1
| | select top 100 convert(varchar(100),idMov) as Chiav , num from
| | LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
| | commit tran
| |
| |
| |
| | if i run it a error was show :
| |
| | Server: Msg 7391, Level 16, State 1, Line 2
| | The operation could not be performed because the OLE DB provider
| 'SQLOLEDB'
| | was unable to begin a distributed transaction.
| | [OLE/DB provider returned message: New transaction cannot enlist in the
| | specified transaction coordinator. ]
| | OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
| | ITransactionJoin::JoinTransaction returned 0x8004d00a].
| |
| |
| |
| | I check for MSDTC configuration but it seems right.
| |
| | I check for this error and i found some KB that indicated a isuee with
| | MSDTC, so i test it with the DTCTester but the tool tell work all fine
!
| | ARGH!!!!
| |
| | It's important see that the transaction start on the node1 use a
"local"
| | MSDTC contat a "local" linked server that poit to "local" sql server
| | instance for use a "local" DB2.
| |
| | The MSDTC is cluster configuration.
| |
| | There is any problem to use a ditribuited transaction with a "local"
| linked
| | server ?
| |
| |
| | Anybody have any idea ?
| |
| | Thanks in advance.
| |
| |
|
|

Sunday, March 25, 2012

Access dabase linked to SQL Server

Access Crashes Filtering a Linked SQL Table on a date field

Access Connection

Access Connection

Access Combo Box Linked to Stored Proc

Thursday, March 22, 2012

Access 97 to SQL Conversion

Access 97 to SQL Conversion

Access 97 to SQL

Access 97 to SQL

Access 97 to SQL

Access 97 to SQL

Access 97 linked to SQL 2000 large fields problems

Access 97 linked to SQL 2000 large fields problems

Tuesday, March 20, 2012

Access 2007 linked tables (vs Access 2003)

Access 2003 with SQL Express 2005

Access 2003 Linked Tables update

Renaming Linked Tables

Monday, March 19, 2012

Access + ODBC + MS SQL on WAN

Saturday, February 25, 2012

About SQL2000 SP4 and DTC e Linked Server

About SQL2000 SP4 and DTC e Linked Server

SQL About AMO

Blog Archive

About Me