Hi,
Do sequences of transactions logs are broken when a restore occur ?
Thank you
danny> Do sequences of transactions logs are broken when a restore occur ?
I am not sure if I understand the question. When you do the restore db and
log over an existing database, you overwrite the data files and the log
files. Sequence is constant and not broken in the restored database, but of
course in the original one you could have had different LSN. It is
overwritten, so why care?
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||Thank you Dejan.
<<So why care ?>>
I am going to expose to you a fictive situation to explain why I asked th=is
question:
This is not a problem that encountered. This is a theorical question
Suppose this
> >
> >Sat. June 07 FULL database Backup
> >Sun. June 08 Transaction log backup
> >Mon. June 09 Transaction log backup
> >Tues. June 10 Transaction log backup
> >Wed. June 11 Transaction log backup
> >Thu. June 12 Transaction log backup
> >Fri. June 13 Transaction log backup
> >
> >Sat. June 14 FULL database Backup
> >Sun. June 15 Transaction log backup
> >Mon. June 16 Transaction log backup
> >Tues. June 17 Transaction log backup
> >Wed. June 18 Transaction log backup
> >Thu. June 19 Transaction log backup
> >Fri. June 20 Transaction log backup
> >
> >Sat. June 21 Full Database Backup
> >Sat June 21 The first Restore from June 14 has been executed (with=out
losing data)
> >Sat. June 21 Full Database Backup after the restore
> >Sun. june 22 Transaction log backup
> >Mon. june 23 Transaction log backup
> >Tues. June 24 Transaction log backup
> >Wed. June 25 Transaction log backup
> >Thu. June 27 Transaction log backup
> >Fri. June 27 Transaction log backup
> >
> >Sat June 28 Full Database Backup
> >Sat June 28 Does another Restore from June 07 is possible witout l=osing
data ?
On June 21, suppose the DBA is not at the office (vacancy) and the
operator realize at 10:00 that a table is corrupted. Suppose the
operator don't know if the table was OK when the last FULL backup
occured on June 21 (at 01:00). Suppose the operator decide to restore
the Full Database from June 14 and all transactions log sequentially
until now (June 21) (without losing data). Suppose the table was also cor=rupted
on June 14. Suppose the operator forgot to execute DBCC CheckDB after the=
Restor to check the table.
On June 28, one week later, suppose the DBA is back to the office and he
realize that a table is corrupted (the same table). The DBA knows that
the table has been corrupted on June 08. The DBA also know that the
operator has restored, one week ago, the database from June 14.
The DBA don't want to lose data.
Question 1:
On June 28, does the DBA can still restore FULL backup from June 07 and r=estore
all transactions log (sequentially) from june 07 untill now without losin=g data
?
Question 2
Do the sequences of transactions logs are going to be OK from June 7 to =June
28 even though the operator has restored the database (from June 14) on J=une 21
? In others words, do sequences of transactions logs has been broken on J=une 21
when the operator executed the first restore ?
Thank you
Danny
Dejan Sarka a =E9crit :
> > Do sequences of transactions logs are broken when a restore occur ?
> I am not sure if I understand the question. When you do the restore db =and
> log over an existing database, you overwrite the data files and the log=
> files. Sequence is constant and not broken in the restored database, bu=t of
> course in the original one you could have had different LSN. It is
> overwritten, so why care?
> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org|||Danny, now I see what you mean. When you issue the recovery process (i.e.
put the database in operational mode after last restore), SQL Server
restarts LSN (Log Sequence Number) from different number from the last
backup. Thus your log backups from
Fri. June 20 Transaction log backup
and
Sun. june 22 Transaction log backup
are not connected anymore- you have a hole in LSN's. So it is not possible
to restore the 2nd log backup mentioned after the 1st mentioned.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Danny Presse" <danny.presse@.sympatico.ca> wrote in message
news:3F0037BF.200A1DBD@.sympatico.ca...
Thank you Dejan.
<<So why care ?>>
I am going to expose to you a fictive situation to explain why I asked this
question:
This is not a problem that encountered. This is a theorical question
Suppose this
> >
> >Sat. June 07 FULL database Backup
> >Sun. June 08 Transaction log backup
> >Mon. June 09 Transaction log backup
> >Tues. June 10 Transaction log backup
> >Wed. June 11 Transaction log backup
> >Thu. June 12 Transaction log backup
> >Fri. June 13 Transaction log backup
> >
> >Sat. June 14 FULL database Backup
> >Sun. June 15 Transaction log backup
> >Mon. June 16 Transaction log backup
> >Tues. June 17 Transaction log backup
> >Wed. June 18 Transaction log backup
> >Thu. June 19 Transaction log backup
> >Fri. June 20 Transaction log backup
> >
> >Sat. June 21 Full Database Backup
> >Sat June 21 The first Restore from June 14 has been executed (without
losing data)
> >Sat. June 21 Full Database Backup after the restore
> >Sun. june 22 Transaction log backup
> >Mon. june 23 Transaction log backup
> >Tues. June 24 Transaction log backup
> >Wed. June 25 Transaction log backup
> >Thu. June 27 Transaction log backup
> >Fri. June 27 Transaction log backup
> >
> >Sat June 28 Full Database Backup
> >Sat June 28 Does another Restore from June 07 is possible witout
losing
data ?
On June 21, suppose the DBA is not at the office (vacancy) and the
operator realize at 10:00 that a table is corrupted. Suppose the
operator don't know if the table was OK when the last FULL backup
occured on June 21 (at 01:00). Suppose the operator decide to restore
the Full Database from June 14 and all transactions log sequentially
until now (June 21) (without losing data). Suppose the table was also
corrupted
on June 14. Suppose the operator forgot to execute DBCC CheckDB after the
Restor to check the table.
On June 28, one week later, suppose the DBA is back to the office and he
realize that a table is corrupted (the same table). The DBA knows that
the table has been corrupted on June 08. The DBA also know that the
operator has restored, one week ago, the database from June 14.
The DBA don't want to lose data.
Question 1:
On June 28, does the DBA can still restore FULL backup from June 07 and
restore
all transactions log (sequentially) from june 07 untill now without losing
data
?
Question 2
Do the sequences of transactions logs are going to be OK from June 7 to
June
28 even though the operator has restored the database (from June 14) on June
21
? In others words, do sequences of transactions logs has been broken on June
21
when the operator executed the first restore ?
Thank you
Danny
Dejan Sarka a écrit :
> > Do sequences of transactions logs are broken when a restore occur ?
> I am not sure if I understand the question. When you do the restore db and
> log over an existing database, you overwrite the data files and the log
> files. Sequence is constant and not broken in the restored database, but
of
> course in the original one you could have had different LSN. It is
> overwritten, so why care?
> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.orgsql
Showing posts with label transactions. Show all posts
Showing posts with label transactions. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Do I really need a cursor?
I've built an application to import transactions into the database. Bad transactions go in a separate table and dupe transactions get updated. Currently, it takes about 2 hours to import ~40K records using the code below. Obviously I'd like this to run as fast as possible and since cursors are a real drag I was wondering if there was a more efficient way to accomplish this.
DECLARE
@.contact_id int,
@.product_code char(9),
@.status_date datetime,
@.business_code char(4),
@.expire_date datetime,
@.prod_status char(4),
@.transaction_id int,
@.emailAddress varchar(50),
@.journal_id int
BEGIN TRAN
DECLARE transaction_import_cursor CURSOR
FOR SELECT transaction_id, product_code, emailAddress, status_date, business_code, expire_date, prod_status from transactions_batch_tmp
OPEN transaction_import_cursor
FETCH NEXT FROM transaction_import_cursor INTO @.transaction_id, @.product_code, @.emailAddress, @.status_date, @.business_code, @.expire_date, @.prod_status
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SELECT top 1 contacts.contact_id AS contact_id, transactions_batch_tmp.status_date AS status_date, transactions_batch_tmp.product_code AS product_code,
transactions_batch_tmp.business_code AS business_code, transactions_batch_tmp.expire_date AS expire_date,
transactions_batch_tmp.prod_status AS product_status
FROM transactions_batch_tmp INNER JOIN
journal INNER JOIN
contacts ON journal.contact_id = contacts.contact_id ON transactions_batch_tmp.emailAddress = contacts.emailAddress AND
transactions_batch_tmp.product_code = journal.product_code INNER JOIN
products ON transactions_batch_tmp.product_code = products.product_code
WHERE rtrim(ltrim(contacts.emailAddress)) = @.emailAddress AND journal.product_code = @.product_code
ORDER BY transactions_batch_tmp.status_date desc
IF @.@.ROWCOUNT = 0
BEGIN
print 'NEW transaction! ' + @.product_code + @.emailAddress
insert into journal (contact_id, product_code, status_date, business_code, expire_date, entryTypeID, product_status, date_entered)
SELECT distinct rtrim(ltrim(contacts.contact_id)) as cid, rtrim(ltrim(products.product_code)), transactions_batch_tmp.status_date,
rtrim(ltrim(transactions_batch_tmp.business_code)) , transactions_batch_tmp.expire_date, 21, rtrim(ltrim(transactions_batch_tmp.prod_status)), getDate()
FROM contacts INNER JOIN (transactions_batch_tmp INNER JOIN products ON transactions_batch_tmp.product_code=products.produ ct_code) ON contacts.emailAddress=transactions_batch_tmp.email Address
WHERE transactions_batch_tmp.transaction_id=@.transaction _id
END
ELSE
BEGIN
--print 'UPDATE transaction! ' + @.product_code + @.emailAddress
UPDATE journal
SET status_date =
(SELECT max(tmp.status_date)
FROM transactions_batch_tmp tmp, contacts c, products p, journal j
WHERE tmp.emailaddress = @.emailAddress
AND tmp.emailaddress = rtrim(c.emailaddress)
AND c.contact_id = j.contact_id
AND j.product_code = @.product_code
AND j.product_code = tmp.product_code)
FROM transactions_batch_tmp tmp, contacts c, products p, journal j
WHERE tmp.emailaddress = @.emailAddress
AND tmp.emailaddress = rtrim(c.emailaddress)
AND c.contact_id = j.contact_id
AND j.product_code = @.product_code
AND j.product_code = tmp.product_code
END
FETCH NEXT FROM transaction_import_cursor INTO @.transaction_id, @.product_code, @.emailAddress, @.status_date, @.business_code, @.expire_date, @.prod_status
END
CLOSE transaction_import_cursor
DEALLOCATE transaction_import_cursor
COMMIT TRAN
/** purge data from temp error table before writing bad records for this batch **/
truncate table tran_import_error;
/** write bad records (missing product code or email address) to temp_error table **/
insert into tran_import_error (transaction_id, product_code, emailAddress, date_entered)
SELECT DISTINCT transactions_batch_tmp.transaction_id, transactions_batch_tmp.product_code, transactions_batch_tmp.emailAddress, getDate()
FROM transactions_batch_tmp
where transactions_batch_tmp.emailaddress not in (select emailaddress from contacts)
OR
transactions_batch_tmp.product_code not in (select product_code from products)
TIAI don't see anything in your code that requires a cursor. It would run much faster as set-based INSERT and UPDATE statements.|||Well, how would I handle the update part without a cursor? I need to make sure that *only* unique contact_id-product_code values exist in the journal table.
Thanks.|||Add some bit flag and notes columns to your import table. Then you can run data checks against the records prior to importing them. Flag any duplicates or bad records and add a note as to why they were flagged. Then import only the non-flagged records. Delete the non-flagged records when you are done, and you are left with a list of bad records that you can review or discard.|||blindman - Thanks for your help.
I'm almost there (I hope), but was wondering if there was a more efficient way to delete the dupe records than having to write two separate queries. I need to keep the most recent product_code-status_date transaction for *each* person. This runs after I insert ALL the records in the journal table.
--delete dupe trans with status_date as the flag
DELETE journal FROM journal, contacts
JOIN
(select product_code, contact_id, max(status_date) as max_status_date
from journal
group by product_code, contact_id) AS G
ON G.[contact_id] = contacts.[contact_id]
WHERE journal.[status_date] < G.[max_status_date]
AND G.[product_code] = journal.[product_code];
--delete dupe trans with journal_id as the flag
DELETE journal FROM journal, contacts
JOIN
(select product_code, contact_id, max(journal_id) as maxID
from journal
group by product_code, contact_id) AS G
ON G.[contact_id] = contacts.[contact_id]
WHERE journal.[journal_id] < G.[MaxID]
AND G.[product_code] = journal.[product_code];
Thanks again.|||The contact table has nothing to do with your delete, except to limit the deleted records to those that have a contact_id. I assume that contact_id is part of journal's natural key and that all records have a valid contact_id, so drop if from both your queries. (If you do need it for filtering, join it in the subquery.)
--delete dupe trans with status_date as the flag
DELETE
FROM journal
INNER JOIN
(select product_code, contact_id, max(status_date) as max_status_date
from journal
group by product_code, contact_id) AS G
ON journal.[product_code] = G.[product_code]
and journal.[contact_id] = G.[contact_id]
and journal.[status_date] < G.[max_status_date]
--delete dupe trans with journal_id as the flag
DELETE journal
FROM journal
INNER JOIN
(select product_code, contact_id, max(journal_id) as maxID
from journal
group by product_code, contact_id) AS G
ON journal.[product_code] = G.[product_code]
and journal.[contact_id] = G.[contact_id]
and journal.[journal_id] < G.[MaxID]
It also appears that the first query should handle all product_code/contact_id duplicates except those with that share exactly the same status_date. If status_date stores only whole-date values, then I guess I see the point of the second delete statement, but otherwise I wouldn't expect you to get a high rowcount from it.
Now to your question; can this be done as a single SQL statement? Yes, but it would essentially require two nested subqueries, so I don't think you would get a big performance boost from it, and you would certainly have to sacrifice code clarity. I recommend that you leave it as two separate deletes.
DECLARE
@.contact_id int,
@.product_code char(9),
@.status_date datetime,
@.business_code char(4),
@.expire_date datetime,
@.prod_status char(4),
@.transaction_id int,
@.emailAddress varchar(50),
@.journal_id int
BEGIN TRAN
DECLARE transaction_import_cursor CURSOR
FOR SELECT transaction_id, product_code, emailAddress, status_date, business_code, expire_date, prod_status from transactions_batch_tmp
OPEN transaction_import_cursor
FETCH NEXT FROM transaction_import_cursor INTO @.transaction_id, @.product_code, @.emailAddress, @.status_date, @.business_code, @.expire_date, @.prod_status
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SELECT top 1 contacts.contact_id AS contact_id, transactions_batch_tmp.status_date AS status_date, transactions_batch_tmp.product_code AS product_code,
transactions_batch_tmp.business_code AS business_code, transactions_batch_tmp.expire_date AS expire_date,
transactions_batch_tmp.prod_status AS product_status
FROM transactions_batch_tmp INNER JOIN
journal INNER JOIN
contacts ON journal.contact_id = contacts.contact_id ON transactions_batch_tmp.emailAddress = contacts.emailAddress AND
transactions_batch_tmp.product_code = journal.product_code INNER JOIN
products ON transactions_batch_tmp.product_code = products.product_code
WHERE rtrim(ltrim(contacts.emailAddress)) = @.emailAddress AND journal.product_code = @.product_code
ORDER BY transactions_batch_tmp.status_date desc
IF @.@.ROWCOUNT = 0
BEGIN
print 'NEW transaction! ' + @.product_code + @.emailAddress
insert into journal (contact_id, product_code, status_date, business_code, expire_date, entryTypeID, product_status, date_entered)
SELECT distinct rtrim(ltrim(contacts.contact_id)) as cid, rtrim(ltrim(products.product_code)), transactions_batch_tmp.status_date,
rtrim(ltrim(transactions_batch_tmp.business_code)) , transactions_batch_tmp.expire_date, 21, rtrim(ltrim(transactions_batch_tmp.prod_status)), getDate()
FROM contacts INNER JOIN (transactions_batch_tmp INNER JOIN products ON transactions_batch_tmp.product_code=products.produ ct_code) ON contacts.emailAddress=transactions_batch_tmp.email Address
WHERE transactions_batch_tmp.transaction_id=@.transaction _id
END
ELSE
BEGIN
--print 'UPDATE transaction! ' + @.product_code + @.emailAddress
UPDATE journal
SET status_date =
(SELECT max(tmp.status_date)
FROM transactions_batch_tmp tmp, contacts c, products p, journal j
WHERE tmp.emailaddress = @.emailAddress
AND tmp.emailaddress = rtrim(c.emailaddress)
AND c.contact_id = j.contact_id
AND j.product_code = @.product_code
AND j.product_code = tmp.product_code)
FROM transactions_batch_tmp tmp, contacts c, products p, journal j
WHERE tmp.emailaddress = @.emailAddress
AND tmp.emailaddress = rtrim(c.emailaddress)
AND c.contact_id = j.contact_id
AND j.product_code = @.product_code
AND j.product_code = tmp.product_code
END
FETCH NEXT FROM transaction_import_cursor INTO @.transaction_id, @.product_code, @.emailAddress, @.status_date, @.business_code, @.expire_date, @.prod_status
END
CLOSE transaction_import_cursor
DEALLOCATE transaction_import_cursor
COMMIT TRAN
/** purge data from temp error table before writing bad records for this batch **/
truncate table tran_import_error;
/** write bad records (missing product code or email address) to temp_error table **/
insert into tran_import_error (transaction_id, product_code, emailAddress, date_entered)
SELECT DISTINCT transactions_batch_tmp.transaction_id, transactions_batch_tmp.product_code, transactions_batch_tmp.emailAddress, getDate()
FROM transactions_batch_tmp
where transactions_batch_tmp.emailaddress not in (select emailaddress from contacts)
OR
transactions_batch_tmp.product_code not in (select product_code from products)
TIAI don't see anything in your code that requires a cursor. It would run much faster as set-based INSERT and UPDATE statements.|||Well, how would I handle the update part without a cursor? I need to make sure that *only* unique contact_id-product_code values exist in the journal table.
Thanks.|||Add some bit flag and notes columns to your import table. Then you can run data checks against the records prior to importing them. Flag any duplicates or bad records and add a note as to why they were flagged. Then import only the non-flagged records. Delete the non-flagged records when you are done, and you are left with a list of bad records that you can review or discard.|||blindman - Thanks for your help.
I'm almost there (I hope), but was wondering if there was a more efficient way to delete the dupe records than having to write two separate queries. I need to keep the most recent product_code-status_date transaction for *each* person. This runs after I insert ALL the records in the journal table.
--delete dupe trans with status_date as the flag
DELETE journal FROM journal, contacts
JOIN
(select product_code, contact_id, max(status_date) as max_status_date
from journal
group by product_code, contact_id) AS G
ON G.[contact_id] = contacts.[contact_id]
WHERE journal.[status_date] < G.[max_status_date]
AND G.[product_code] = journal.[product_code];
--delete dupe trans with journal_id as the flag
DELETE journal FROM journal, contacts
JOIN
(select product_code, contact_id, max(journal_id) as maxID
from journal
group by product_code, contact_id) AS G
ON G.[contact_id] = contacts.[contact_id]
WHERE journal.[journal_id] < G.[MaxID]
AND G.[product_code] = journal.[product_code];
Thanks again.|||The contact table has nothing to do with your delete, except to limit the deleted records to those that have a contact_id. I assume that contact_id is part of journal's natural key and that all records have a valid contact_id, so drop if from both your queries. (If you do need it for filtering, join it in the subquery.)
--delete dupe trans with status_date as the flag
DELETE
FROM journal
INNER JOIN
(select product_code, contact_id, max(status_date) as max_status_date
from journal
group by product_code, contact_id) AS G
ON journal.[product_code] = G.[product_code]
and journal.[contact_id] = G.[contact_id]
and journal.[status_date] < G.[max_status_date]
--delete dupe trans with journal_id as the flag
DELETE journal
FROM journal
INNER JOIN
(select product_code, contact_id, max(journal_id) as maxID
from journal
group by product_code, contact_id) AS G
ON journal.[product_code] = G.[product_code]
and journal.[contact_id] = G.[contact_id]
and journal.[journal_id] < G.[MaxID]
It also appears that the first query should handle all product_code/contact_id duplicates except those with that share exactly the same status_date. If status_date stores only whole-date values, then I guess I see the point of the second delete statement, but otherwise I wouldn't expect you to get a high rowcount from it.
Now to your question; can this be done as a single SQL statement? Yes, but it would essentially require two nested subqueries, so I don't think you would get a big performance boost from it, and you would certainly have to sacrifice code clarity. I recommend that you leave it as two separate deletes.
Monday, March 19, 2012
dml without generating log transactions ?
Hi There
I know the answer to this is probably no, but had to ask anyway.
Is there a way to perform a dml statement without generating anything in the transaction log ?
The reason i ask is that i have a database that uses simply recovery model, however i need to move a 1 billion row table to this DB, i know that even though it is in simple recovery it is one transaction, it will be written to the log until committed then the space will be released in the log file.
I am using a simple: insert into DW_DB..table select * from DB..other_table.
I have dropped all indexes before the operation.
However this is a big problem, the log for the db in simple recovery that i am moving the data to grew to 128 Gig and the disk ran out of space, the other drives on the machine do not have much space.
Is there a way i can move the billion row table into the new DB without generating such a huge log ?
Thanx
Hi There
Part 2 for the question:
The transaction has rolled back, however there is still 27 gigs space used in the transaction log, there are no open transactions in the db, the db is in simple recovery, i cannot backup the log as it is simple recovery, what is this 27 gigs in the transaction and how do i clear it ?
Thanx
|||Please ignore my second comment, this problem went away after checkpointing the database, however any feedback ont he original post would be greatly appreciated.
Thanx
|||You can use select into command which is bulk operation and it is minimally logged in the case of simple recovery model.|||Thank you , this worked perfectly.Wednesday, March 7, 2012
Distrubuted Transactions with AS400
Hi All
I have created stored procs that update SQL and AS400 (Linked server)
tables. I want to wrap both updates in a transaction, however when I do, I
get the error
"The operation could not be performed because OLE DB provider "MSDASQL" for
linked server "LinkedServerName" was unable to begin a distributed
transaction".
We are using Client Access ODBC driver (V5R2, SI06631).
All of the documents that I have read talk about MTS and MS DTC.
MS DTC is running on the SQL Server.
Is there any way of using Distributed Transaction processing without
requiring MTS ?
Thanks in advance.not enough info on the error.
here:
http://support.microsoft.com/kb/306212
you may want to look at the full error when you talk to DB2. like so
DBCC TRACEON (3604, 7300)
then run your sp and seewhat it does.
did you try just issue
BEGIN DISTRIBUTED TRAN
and see if the 2 data sources you are working with can be accessed?
and you the same authentication your application is going to call your sp
with.
Thanks, Liliya
"Jane" wrote:
> Hi All
> I have created stored procs that update SQL and AS400 (Linked server)
> tables. I want to wrap both updates in a transaction, however when I do, I
> get the error
> "The operation could not be performed because OLE DB provider "MSDASQL" for
> linked server "LinkedServerName" was unable to begin a distributed
> transaction".
> We are using Client Access ODBC driver (V5R2, SI06631).
> All of the documents that I have read talk about MTS and MS DTC.
> MS DTC is running on the SQL Server.
> Is there any way of using Distributed Transaction processing without
> requiring MTS ?
> Thanks in advance.|||Thanks for the response.
Yes - used the same auth as the app will use (windows auth)
Yes - just used BEGIN DISTRIBUTED TRAN
Used DBCC TRACEON (3604, 7300) & SET XACT_ABORT ON as per the link in a
Query Window then ran the updates again.
Ran DBCC TRACESTATUS (3604,7300) to check that the flags for the session
were set.
Still failing & still no extra error information.
Under "Configuration Issues" the link states ;
"Start the Distributed Transaction Coordinator (DTC or MSDTC) on all servers
that are involved in the distributed transaction".
I am not sure what needs to running on the AS400 for this to work.
"l" wrote:
> not enough info on the error.
> here:
> http://support.microsoft.com/kb/306212
> you may want to look at the full error when you talk to DB2. like so
> DBCC TRACEON (3604, 7300)
> then run your sp and seewhat it does.
> did you try just issue
> BEGIN DISTRIBUTED TRAN
> and see if the 2 data sources you are working with can be accessed?
> and you the same authentication your application is going to call your sp
> with.
> Thanks, Liliya
>
> "Jane" wrote:
> > Hi All
> >
> > I have created stored procs that update SQL and AS400 (Linked server)
> > tables. I want to wrap both updates in a transaction, however when I do, I
> > get the error
> > "The operation could not be performed because OLE DB provider "MSDASQL" for
> > linked server "LinkedServerName" was unable to begin a distributed
> > transaction".
> > We are using Client Access ODBC driver (V5R2, SI06631).
> > All of the documents that I have read talk about MTS and MS DTC.
> > MS DTC is running on the SQL Server.
> >
> > Is there any way of using Distributed Transaction processing without
> > requiring MTS ?
> >
> > Thanks in advance.|||OLEDB Errors in SQL Profiler shows ;
<hresult>-2147168246</hresult>
<inputs>
<punkTransactionCoord>0x624A0060</punkTransactionCoord>
<isoLevel>4096</isoLevel>
<isoFlags>0</isoFlags>
<pOtherOptions>0x00000000</pOtherOptions>
</inputs>|||> I am not sure what needs to running on the AS400 for this to work.
depend on what are you running on AS400. DB2 version. Here. how to set up
mts (0you have mentioned this one earlier in this thread)
http://www-03.ibm.com/servers/enable/site/db2/mts/mts.pdf
as about ms sql server side, it does not hurt to test if your sql side is
ok. Easy enough as long as you have couple ms sql's on the same domain etc.
if not, can install one on your ws, configure it and see if you can issue
distributed transactions. After all, it will give you a 'lab rat' s well to
experiment if you in need of one.
Thanks, Liliya
I have created stored procs that update SQL and AS400 (Linked server)
tables. I want to wrap both updates in a transaction, however when I do, I
get the error
"The operation could not be performed because OLE DB provider "MSDASQL" for
linked server "LinkedServerName" was unable to begin a distributed
transaction".
We are using Client Access ODBC driver (V5R2, SI06631).
All of the documents that I have read talk about MTS and MS DTC.
MS DTC is running on the SQL Server.
Is there any way of using Distributed Transaction processing without
requiring MTS ?
Thanks in advance.not enough info on the error.
here:
http://support.microsoft.com/kb/306212
you may want to look at the full error when you talk to DB2. like so
DBCC TRACEON (3604, 7300)
then run your sp and seewhat it does.
did you try just issue
BEGIN DISTRIBUTED TRAN
and see if the 2 data sources you are working with can be accessed?
and you the same authentication your application is going to call your sp
with.
Thanks, Liliya
"Jane" wrote:
> Hi All
> I have created stored procs that update SQL and AS400 (Linked server)
> tables. I want to wrap both updates in a transaction, however when I do, I
> get the error
> "The operation could not be performed because OLE DB provider "MSDASQL" for
> linked server "LinkedServerName" was unable to begin a distributed
> transaction".
> We are using Client Access ODBC driver (V5R2, SI06631).
> All of the documents that I have read talk about MTS and MS DTC.
> MS DTC is running on the SQL Server.
> Is there any way of using Distributed Transaction processing without
> requiring MTS ?
> Thanks in advance.|||Thanks for the response.
Yes - used the same auth as the app will use (windows auth)
Yes - just used BEGIN DISTRIBUTED TRAN
Used DBCC TRACEON (3604, 7300) & SET XACT_ABORT ON as per the link in a
Query Window then ran the updates again.
Ran DBCC TRACESTATUS (3604,7300) to check that the flags for the session
were set.
Still failing & still no extra error information.
Under "Configuration Issues" the link states ;
"Start the Distributed Transaction Coordinator (DTC or MSDTC) on all servers
that are involved in the distributed transaction".
I am not sure what needs to running on the AS400 for this to work.
"l" wrote:
> not enough info on the error.
> here:
> http://support.microsoft.com/kb/306212
> you may want to look at the full error when you talk to DB2. like so
> DBCC TRACEON (3604, 7300)
> then run your sp and seewhat it does.
> did you try just issue
> BEGIN DISTRIBUTED TRAN
> and see if the 2 data sources you are working with can be accessed?
> and you the same authentication your application is going to call your sp
> with.
> Thanks, Liliya
>
> "Jane" wrote:
> > Hi All
> >
> > I have created stored procs that update SQL and AS400 (Linked server)
> > tables. I want to wrap both updates in a transaction, however when I do, I
> > get the error
> > "The operation could not be performed because OLE DB provider "MSDASQL" for
> > linked server "LinkedServerName" was unable to begin a distributed
> > transaction".
> > We are using Client Access ODBC driver (V5R2, SI06631).
> > All of the documents that I have read talk about MTS and MS DTC.
> > MS DTC is running on the SQL Server.
> >
> > Is there any way of using Distributed Transaction processing without
> > requiring MTS ?
> >
> > Thanks in advance.|||OLEDB Errors in SQL Profiler shows ;
<hresult>-2147168246</hresult>
<inputs>
<punkTransactionCoord>0x624A0060</punkTransactionCoord>
<isoLevel>4096</isoLevel>
<isoFlags>0</isoFlags>
<pOtherOptions>0x00000000</pOtherOptions>
</inputs>|||> I am not sure what needs to running on the AS400 for this to work.
depend on what are you running on AS400. DB2 version. Here. how to set up
mts (0you have mentioned this one earlier in this thread)
http://www-03.ibm.com/servers/enable/site/db2/mts/mts.pdf
as about ms sql server side, it does not hurt to test if your sql side is
ok. Easy enough as long as you have couple ms sql's on the same domain etc.
if not, can install one on your ws, configure it and see if you can issue
distributed transactions. After all, it will give you a 'lab rat' s well to
experiment if you in need of one.
Thanks, Liliya
Saturday, February 25, 2012
Distribution executable running at 100% cpu but doesn't process any transactions
MSSQL Server 2000 SP3a -> MSSQL Server 2000 SP3a
Transactional Replication
Push subscriptions
Transactional replication has been performing well for years between
two servers. I added a new publication to handle some other tables.
Both publications had push subscriptions to our Production and
Development SQL servers. I added an article for a relatively large
table (2,000,000 rows or so) to the second publication and a few days
later the distribution agent seemed to get "stuck" after this new
article was added.
The distribution agent maxes out the CPU, but I don't see any
transactions going through. Nothing appears to be happening in
profiler, and neither the sqlserver nor the distrib.exe processes seem
to be performing any I/O related to replication with the exception of
a very slow incrementing I/O other for distrib.exe.
I killed the push subscription for the second publication and this
fixed the problem for a day or so. However this morning the
publication that has been working fine for months is now creating the
same problem. As a result no transactions are getting through to the
production server.
I've tried running the distrib.exe through the command prompt but it
causes the same issue.
Any advice or guidance?
Thanks!
- Mike
You can bet it will get "stuck" - 2,000,000 rows is a lot of data to push.
I suggest you do DTS it over (create a package for this and use the fast
insert option), and then do a nosync subscription. Ensure you build the
replication stored procedures using sp_scriptcustomprocs.
"Mike" <ngposterMikeBain@.gmail.com> wrote in message
news:ea5d5311.0410210729.264b3427@.posting.google.c om...
> MSSQL Server 2000 SP3a -> MSSQL Server 2000 SP3a
> Transactional Replication
> Push subscriptions
> Transactional replication has been performing well for years between
> two servers. I added a new publication to handle some other tables.
> Both publications had push subscriptions to our Production and
> Development SQL servers. I added an article for a relatively large
> table (2,000,000 rows or so) to the second publication and a few days
> later the distribution agent seemed to get "stuck" after this new
> article was added.
> The distribution agent maxes out the CPU, but I don't see any
> transactions going through. Nothing appears to be happening in
> profiler, and neither the sqlserver nor the distrib.exe processes seem
> to be performing any I/O related to replication with the exception of
> a very slow incrementing I/O other for distrib.exe.
> I killed the push subscription for the second publication and this
> fixed the problem for a day or so. However this morning the
> publication that has been working fine for months is now creating the
> same problem. As a result no transactions are getting through to the
> production server.
> I've tried running the distrib.exe through the command prompt but it
> causes the same issue.
> Any advice or guidance?
> Thanks!
> - Mike
Transactional Replication
Push subscriptions
Transactional replication has been performing well for years between
two servers. I added a new publication to handle some other tables.
Both publications had push subscriptions to our Production and
Development SQL servers. I added an article for a relatively large
table (2,000,000 rows or so) to the second publication and a few days
later the distribution agent seemed to get "stuck" after this new
article was added.
The distribution agent maxes out the CPU, but I don't see any
transactions going through. Nothing appears to be happening in
profiler, and neither the sqlserver nor the distrib.exe processes seem
to be performing any I/O related to replication with the exception of
a very slow incrementing I/O other for distrib.exe.
I killed the push subscription for the second publication and this
fixed the problem for a day or so. However this morning the
publication that has been working fine for months is now creating the
same problem. As a result no transactions are getting through to the
production server.
I've tried running the distrib.exe through the command prompt but it
causes the same issue.
Any advice or guidance?
Thanks!
- Mike
You can bet it will get "stuck" - 2,000,000 rows is a lot of data to push.
I suggest you do DTS it over (create a package for this and use the fast
insert option), and then do a nosync subscription. Ensure you build the
replication stored procedures using sp_scriptcustomprocs.
"Mike" <ngposterMikeBain@.gmail.com> wrote in message
news:ea5d5311.0410210729.264b3427@.posting.google.c om...
> MSSQL Server 2000 SP3a -> MSSQL Server 2000 SP3a
> Transactional Replication
> Push subscriptions
> Transactional replication has been performing well for years between
> two servers. I added a new publication to handle some other tables.
> Both publications had push subscriptions to our Production and
> Development SQL servers. I added an article for a relatively large
> table (2,000,000 rows or so) to the second publication and a few days
> later the distribution agent seemed to get "stuck" after this new
> article was added.
> The distribution agent maxes out the CPU, but I don't see any
> transactions going through. Nothing appears to be happening in
> profiler, and neither the sqlserver nor the distrib.exe processes seem
> to be performing any I/O related to replication with the exception of
> a very slow incrementing I/O other for distrib.exe.
> I killed the push subscription for the second publication and this
> fixed the problem for a day or so. However this morning the
> publication that has been working fine for months is now creating the
> same problem. As a result no transactions are getting through to the
> production server.
> I've tried running the distrib.exe through the command prompt but it
> causes the same issue.
> Any advice or guidance?
> Thanks!
> - Mike
Distribution cleanup
Are transactions that have been distributed to all subscribers always stored the maximum retention period in the distribution database?
I'm using peer-to-peer replication.
No, they are stored the minimum retention period and are cleanup by the distribution clean up task. If a subscriber is offline, the transactions will be stored for the maximum retention period unless the subscriber is expired by this history retenion level.|||But if the minimum retention period is set to 0 shouldn't all delivered transactions be cleaned up in that case.
In my peer-to-peer replication I have a publication with four subscribers and everything works fine all transactions are replicated and I don't have any latency problems.
So why is the MSrepl_commands still growing.
I can see in the output for distribution cleanup job that transactions actually are cleaned up but the MSrepl_commands grows. From time to time a the distribution cleanup job gets selected as a deadlock victim.
The size of the distribution db is now about 50GB with 100'000'000 rows in the MSrepl_commands table.
/Peter
Friday, February 24, 2012
Distribution cleanup
I would love to be able to run the distribution cleanup job with a switch that says cleanup all distributed transactions.
Because when I use peer to peer replication the @.allow_initialize_from_backup publication property is set to true which is good. But it has the down side that transactions are stored the max retention period in the distribution database. I want to use the deafault 72 hours for my retention period so that the subscritions don't get deactivated but in a system with a high transaction rate there wil be a lot of transactions in 72 hours. This means that the cleanup job will have a tough time to figure out which transactions to delete so the cleanup job will run for a long time not a very big problem but the problem is that the cleanup job will keep the log reader agent from delivering trtansactions to the distribution database and the subscribers won't get their data in time.
Could Microsoft please give me a switch so I can choose when I want to save my transaction and when I want to delete them as soon as they have been delivered to all subscribers?
Is this a feature in SQL Server 2008? Could it be released in SP3 for SQL Server 2005. (The SP 2 cleanup job has a bug so I have to use the SP 1 verison of the cleanup job)
just out of curiosity what is the bug in SP2 cleanup job? Do you happen to have a bug number or description, i can look it up and see if there's a workaround for your problem.|||I don't have the bug number My tech lead at Microsoft will come back to me when the dev team has made the correction. The workaround I use is to run the SP1 versions of some cleanup procs.
This workaround atleast lets me delete the records older than max retention.
But I still would lit to be able to give two dates to the cleanup job. One for checking the subscription deactivation and one for transaction cleanup.
distribution clean up not working
Using transactional replication and all my transactions and commands are
replicated to all my subscribers. There is nothing to be delivered from the
msdistributionstatus view , but yet when i run the distribution cleanup, the
commands and transactions are still in the msrepl_commands and trans table.
When would they get deleted ?
Do you have anonymous subscribers enabled? If so, the commands will hand
around until the transaction retention perios is reached.
Rgds,
Paul Ibison, SQL MVP
|||How do I find out ? And if they are enabled, how do i turn them off ? I know
making certain settings to a publication causes the whole publication to
initilaize and resnapshot.
It happened once to me when i changed it to concurrent snapshot while
replication was on and next thing i know it triggered a reinitialisation and
all my objects were being snapshot.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:esOIyMDbFHA.2696@.TK2MSFTNGP09.phx.gbl...
> Do you have anonymous subscribers enabled? If so, the commands will hand
> around until the transaction retention perios is reached.
> Rgds,
> Paul Ibison, SQL MVP
>
|||sp_helppublication and look for allow_anonymous.
sp_changepublication can be used to alter.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Will this reinitialise all subscriptions to the publication ?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OQYacdDbFHA.3840@.tk2msftngp13.phx.gbl...
> sp_helppublication and look for allow_anonymous.
> sp_changepublication can be used to alter.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Also allow_anonymous = 0 .. So why is not cleaning up ?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OQYacdDbFHA.3840@.tk2msftngp13.phx.gbl...
> sp_helppublication and look for allow_anonymous.
> sp_changepublication can be used to alter.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||It's possible the cleanup agent is being blocked.
I have experienced a similar problem but only on databases with really huge
tables (50 million or so), and still have an open PSS on it. The
recommendation was to reduce the transaction retention period, which was not
ideal. Even when I stopped the logreader and distribution agents, the
cleanup still didn't remove the records, for some strange reason, until the
retention period was reached. So, I made sure my subscribers had
synchronized then really reduced the retention period to remove the backlog.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Where do you change the transaction retention period ?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uNdEoJEbFHA.2124@.TK2MSFTNGP14.phx.gbl...
> It's possible the cleanup agent is being blocked.
> I have experienced a similar problem but only on databases with really
huge
> tables (50 million or so), and still have an open PSS on it. The
> recommendation was to reduce the transaction retention period, which was
not
> ideal. Even when I stopped the logreader and distribution agents, the
> cleanup still didn't remove the records, for some strange reason, until
the
> retention period was reached. So, I made sure my subscribers had
> synchronized then really reduced the retention period to remove the
backlog.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||It's a distributor property, available from the replication monitor,
distributor properties, properties button of the distribution database.
Rgds,
Paul Ibison
replicated to all my subscribers. There is nothing to be delivered from the
msdistributionstatus view , but yet when i run the distribution cleanup, the
commands and transactions are still in the msrepl_commands and trans table.
When would they get deleted ?
Do you have anonymous subscribers enabled? If so, the commands will hand
around until the transaction retention perios is reached.
Rgds,
Paul Ibison, SQL MVP
|||How do I find out ? And if they are enabled, how do i turn them off ? I know
making certain settings to a publication causes the whole publication to
initilaize and resnapshot.
It happened once to me when i changed it to concurrent snapshot while
replication was on and next thing i know it triggered a reinitialisation and
all my objects were being snapshot.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:esOIyMDbFHA.2696@.TK2MSFTNGP09.phx.gbl...
> Do you have anonymous subscribers enabled? If so, the commands will hand
> around until the transaction retention perios is reached.
> Rgds,
> Paul Ibison, SQL MVP
>
|||sp_helppublication and look for allow_anonymous.
sp_changepublication can be used to alter.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Will this reinitialise all subscriptions to the publication ?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OQYacdDbFHA.3840@.tk2msftngp13.phx.gbl...
> sp_helppublication and look for allow_anonymous.
> sp_changepublication can be used to alter.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Also allow_anonymous = 0 .. So why is not cleaning up ?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OQYacdDbFHA.3840@.tk2msftngp13.phx.gbl...
> sp_helppublication and look for allow_anonymous.
> sp_changepublication can be used to alter.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||It's possible the cleanup agent is being blocked.
I have experienced a similar problem but only on databases with really huge
tables (50 million or so), and still have an open PSS on it. The
recommendation was to reduce the transaction retention period, which was not
ideal. Even when I stopped the logreader and distribution agents, the
cleanup still didn't remove the records, for some strange reason, until the
retention period was reached. So, I made sure my subscribers had
synchronized then really reduced the retention period to remove the backlog.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Where do you change the transaction retention period ?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uNdEoJEbFHA.2124@.TK2MSFTNGP14.phx.gbl...
> It's possible the cleanup agent is being blocked.
> I have experienced a similar problem but only on databases with really
huge
> tables (50 million or so), and still have an open PSS on it. The
> recommendation was to reduce the transaction retention period, which was
not
> ideal. Even when I stopped the logreader and distribution agents, the
> cleanup still didn't remove the records, for some strange reason, until
the
> retention period was reached. So, I made sure my subscribers had
> synchronized then really reduced the retention period to remove the
backlog.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||It's a distributor property, available from the replication monitor,
distributor properties, properties button of the distribution database.
Rgds,
Paul Ibison
Sunday, February 19, 2012
Distributed transactions: ITransactionJoin error on two 2003 Servers
I'm hoping someone can help me, I am trying to start a distributed
transaction across a linked server. Both servers are Windows 2003 (pre
SP1) servers, and both are running SQL Server 2000 SP3a. I am getting
the following error message:
Server: Msg 7391, Level 16, State 1, Line 4
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 have checked loads of KB articles, and other newsgroup answers and
have come up with the following ideas, all of which I've done on both
machines:
1. Enabled network DTC access on both machines
2. Ensured network and XA transactions are checked in security
configuration
3. Ensured DTC is running on both machines
4. Set the port ranges to 5000-5020 in COM Internet Services Properites
(although firewall is off)
5. Ensured both machines can ping each other by Netbios names
6. Turned off RPC Security on both machines (setting in registry)
7. Set on RPC and RPC Out in Linked server settings
8. Rebooted (many times) both machines
If anyone has any other ideas I can try (apart from changing the code,
as we have to do things this way, it's not all our code) then let me
know as I am pretty much stuck.
Thanks for your help!
Aron Cox,
Hampshire, UK
I am working in the same problem. See KB839279. If you change
Transaction Manager Communication to "No Authentication Required" it
works but your not authenticating. I also read somewhere that changing
the account from Network Service to Local System, it may work.
*** Sent via Developersdex http://www.codecomments.com ***
transaction across a linked server. Both servers are Windows 2003 (pre
SP1) servers, and both are running SQL Server 2000 SP3a. I am getting
the following error message:
Server: Msg 7391, Level 16, State 1, Line 4
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 have checked loads of KB articles, and other newsgroup answers and
have come up with the following ideas, all of which I've done on both
machines:
1. Enabled network DTC access on both machines
2. Ensured network and XA transactions are checked in security
configuration
3. Ensured DTC is running on both machines
4. Set the port ranges to 5000-5020 in COM Internet Services Properites
(although firewall is off)
5. Ensured both machines can ping each other by Netbios names
6. Turned off RPC Security on both machines (setting in registry)
7. Set on RPC and RPC Out in Linked server settings
8. Rebooted (many times) both machines
If anyone has any other ideas I can try (apart from changing the code,
as we have to do things this way, it's not all our code) then let me
know as I am pretty much stuck.
Thanks for your help!
Aron Cox,
Hampshire, UK
I am working in the same problem. See KB839279. If you change
Transaction Manager Communication to "No Authentication Required" it
works but your not authenticating. I also read somewhere that changing
the account from Network Service to Local System, it may work.
*** Sent via Developersdex http://www.codecomments.com ***
Distributed transactions: ITransactionJoin error on two 2003 Servers
I'm hoping someone can help me, I am trying to start a distributed
transaction across a linked server. Both servers are Windows 2003 (pre
SP1) servers, and both are running SQL Server 2000 SP3a. I am getting
the following error message:
Server: Msg 7391, Level 16, State 1, Line 4
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 have checked loads of KB articles, and other newsgroup answers and
have come up with the following ideas, all of which I've done on both
machines:
1. Enabled network DTC access on both machines
2. Ensured network and XA transactions are checked in security
configuration
3. Ensured DTC is running on both machines
4. Set the port ranges to 5000-5020 in COM Internet Services Properites
(although firewall is off)
5. Ensured both machines can ping each other by Netbios names
6. Turned off RPC Security on both machines (setting in registry)
7. Set on RPC and RPC Out in Linked server settings
8. Rebooted (many times) both machines
If anyone has any other ideas I can try (apart from changing the code,
as we have to do things this way, it's not all our code) then let me
know as I am pretty much stuck.
Thanks for your help!
Aron Cox,
Hampshire, UKI am working in the same problem. See KB839279. If you change
Transaction Manager Communication to "No Authentication Required" it
works but your not authenticating. I also read somewhere that changing
the account from Network Service to Local System, it may work.
*** Sent via Developersdex http://www.codecomments.com ***
transaction across a linked server. Both servers are Windows 2003 (pre
SP1) servers, and both are running SQL Server 2000 SP3a. I am getting
the following error message:
Server: Msg 7391, Level 16, State 1, Line 4
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 have checked loads of KB articles, and other newsgroup answers and
have come up with the following ideas, all of which I've done on both
machines:
1. Enabled network DTC access on both machines
2. Ensured network and XA transactions are checked in security
configuration
3. Ensured DTC is running on both machines
4. Set the port ranges to 5000-5020 in COM Internet Services Properites
(although firewall is off)
5. Ensured both machines can ping each other by Netbios names
6. Turned off RPC Security on both machines (setting in registry)
7. Set on RPC and RPC Out in Linked server settings
8. Rebooted (many times) both machines
If anyone has any other ideas I can try (apart from changing the code,
as we have to do things this way, it's not all our code) then let me
know as I am pretty much stuck.
Thanks for your help!
Aron Cox,
Hampshire, UKI am working in the same problem. See KB839279. If you change
Transaction Manager Communication to "No Authentication Required" it
works but your not authenticating. I also read somewhere that changing
the account from Network Service to Local System, it may work.
*** Sent via Developersdex http://www.codecomments.com ***
Distributed transactions: ITransactionJoin error on two 2003 Servers
I'm hoping someone can help me, I am trying to start a distributed
transaction across a linked server. Both servers are Windows 2003 (pre
SP1) servers, and both are running SQL Server 2000 SP3a. I am getting
the following error message:
Server: Msg 7391, Level 16, State 1, Line 4
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 have checked loads of KB articles, and other newsgroup answers and
have come up with the following ideas, all of which I've done on both
machines:
1. Enabled network DTC access on both machines
2. Ensured network and XA transactions are checked in security
configuration
3. Ensured DTC is running on both machines
4. Set the port ranges to 5000-5020 in COM Internet Services Properites
(although firewall is off)
5. Ensured both machines can ping each other by Netbios names
6. Turned off RPC Security on both machines (setting in registry)
7. Set on RPC and RPC Out in Linked server settings
8. Rebooted (many times) both machines
If anyone has any other ideas I can try (apart from changing the code,
as we have to do things this way, it's not all our code) then let me
know as I am pretty much stuck.
Thanks for your help!
Aron Cox,
Hampshire, UKI am working in the same problem. See KB839279. If you change
Transaction Manager Communication to "No Authentication Required" it
works but your not authenticating. I also read somewhere that changing
the account from Network Service to Local System, it may work.
*** Sent via Developersdex http://www.developersdex.com ***
transaction across a linked server. Both servers are Windows 2003 (pre
SP1) servers, and both are running SQL Server 2000 SP3a. I am getting
the following error message:
Server: Msg 7391, Level 16, State 1, Line 4
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 have checked loads of KB articles, and other newsgroup answers and
have come up with the following ideas, all of which I've done on both
machines:
1. Enabled network DTC access on both machines
2. Ensured network and XA transactions are checked in security
configuration
3. Ensured DTC is running on both machines
4. Set the port ranges to 5000-5020 in COM Internet Services Properites
(although firewall is off)
5. Ensured both machines can ping each other by Netbios names
6. Turned off RPC Security on both machines (setting in registry)
7. Set on RPC and RPC Out in Linked server settings
8. Rebooted (many times) both machines
If anyone has any other ideas I can try (apart from changing the code,
as we have to do things this way, it's not all our code) then let me
know as I am pretty much stuck.
Thanks for your help!
Aron Cox,
Hampshire, UKI am working in the same problem. See KB839279. If you change
Transaction Manager Communication to "No Authentication Required" it
works but your not authenticating. I also read somewhere that changing
the account from Network Service to Local System, it may work.
*** Sent via Developersdex http://www.developersdex.com ***
Distributed Transactions with SQL Express, Server 2003, and XP SP2
I have SQL Express installed on a Windows XP SP2 machine and on a Windows
Server 2003 machine. I added the Windows Server 2003 machine as a linked
server on the Windows XP SP2 machine.
I have checked and double checked that the DTC settings are correct on both
machines and that the DTC is running on both machines, but I am unable to
execute a distributed transaction. I have even tried playing around with many
different combinations of settings to try to get this to work. I have
followed the directions in many of the documents that can be found online on
this issue, but without success.
I am using SQL Server security and am able to execute queries if I do not
begin a transaction. But I cannot execute them if I begin a transaction.
When I execute them in a transaction, I get the following error:
OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "linkedserver" was unable to begin a distributed transaction.
Does anyone have any ideas?
--
Corey YoungCan MSDTC get through the XP Firewall?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Young, Corey" <YoungCorey@.discussions.microsoft.com> wrote in message
news:E2E6FDA4-D185-4625-9BEB-D4995254EE70@.microsoft.com...
>I have SQL Express installed on a Windows XP SP2 machine and on a Windows
> Server 2003 machine. I added the Windows Server 2003 machine as a linked
> server on the Windows XP SP2 machine.
> I have checked and double checked that the DTC settings are correct on
> both
> machines and that the DTC is running on both machines, but I am unable to
> execute a distributed transaction. I have even tried playing around with
> many
> different combinations of settings to try to get this to work. I have
> followed the directions in many of the documents that can be found online
> on
> this issue, but without success.
> I am using SQL Server security and am able to execute queries if I do not
> begin a transaction. But I cannot execute them if I begin a transaction.
> When I execute them in a transaction, I get the following error:
> OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> message
> "No transaction is active.".
> Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "linkedserver" was unable to begin a distributed
> transaction.
> Does anyone have any ideas?
> --
> Corey Young
>|||I turned the firewall off on both machines.
--
Corey Young
"Roger Wolter[MSFT]" wrote:
> Can MSDTC get through the XP Firewall?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Young, Corey" <YoungCorey@.discussions.microsoft.com> wrote in message
> news:E2E6FDA4-D185-4625-9BEB-D4995254EE70@.microsoft.com...
> >I have SQL Express installed on a Windows XP SP2 machine and on a Windows
> > Server 2003 machine. I added the Windows Server 2003 machine as a linked
> > server on the Windows XP SP2 machine.
> >
> > I have checked and double checked that the DTC settings are correct on
> > both
> > machines and that the DTC is running on both machines, but I am unable to
> > execute a distributed transaction. I have even tried playing around with
> > many
> > different combinations of settings to try to get this to work. I have
> > followed the directions in many of the documents that can be found online
> > on
> > this issue, but without success.
> >
> > I am using SQL Server security and am able to execute queries if I do not
> > begin a transaction. But I cannot execute them if I begin a transaction.
> >
> > When I execute them in a transaction, I get the following error:
> >
> > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> > message
> > "No transaction is active.".
> >
> > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> >
> > The operation could not be performed because OLE DB provider "SQLNCLI" for
> > linked server "linkedserver" was unable to begin a distributed
> > transaction.
> >
> > Does anyone have any ideas?
> >
> > --
> > Corey Young
> >
>
>
Server 2003 machine. I added the Windows Server 2003 machine as a linked
server on the Windows XP SP2 machine.
I have checked and double checked that the DTC settings are correct on both
machines and that the DTC is running on both machines, but I am unable to
execute a distributed transaction. I have even tried playing around with many
different combinations of settings to try to get this to work. I have
followed the directions in many of the documents that can be found online on
this issue, but without success.
I am using SQL Server security and am able to execute queries if I do not
begin a transaction. But I cannot execute them if I begin a transaction.
When I execute them in a transaction, I get the following error:
OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "linkedserver" was unable to begin a distributed transaction.
Does anyone have any ideas?
--
Corey YoungCan MSDTC get through the XP Firewall?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Young, Corey" <YoungCorey@.discussions.microsoft.com> wrote in message
news:E2E6FDA4-D185-4625-9BEB-D4995254EE70@.microsoft.com...
>I have SQL Express installed on a Windows XP SP2 machine and on a Windows
> Server 2003 machine. I added the Windows Server 2003 machine as a linked
> server on the Windows XP SP2 machine.
> I have checked and double checked that the DTC settings are correct on
> both
> machines and that the DTC is running on both machines, but I am unable to
> execute a distributed transaction. I have even tried playing around with
> many
> different combinations of settings to try to get this to work. I have
> followed the directions in many of the documents that can be found online
> on
> this issue, but without success.
> I am using SQL Server security and am able to execute queries if I do not
> begin a transaction. But I cannot execute them if I begin a transaction.
> When I execute them in a transaction, I get the following error:
> OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> message
> "No transaction is active.".
> Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "linkedserver" was unable to begin a distributed
> transaction.
> Does anyone have any ideas?
> --
> Corey Young
>|||I turned the firewall off on both machines.
--
Corey Young
"Roger Wolter[MSFT]" wrote:
> Can MSDTC get through the XP Firewall?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Young, Corey" <YoungCorey@.discussions.microsoft.com> wrote in message
> news:E2E6FDA4-D185-4625-9BEB-D4995254EE70@.microsoft.com...
> >I have SQL Express installed on a Windows XP SP2 machine and on a Windows
> > Server 2003 machine. I added the Windows Server 2003 machine as a linked
> > server on the Windows XP SP2 machine.
> >
> > I have checked and double checked that the DTC settings are correct on
> > both
> > machines and that the DTC is running on both machines, but I am unable to
> > execute a distributed transaction. I have even tried playing around with
> > many
> > different combinations of settings to try to get this to work. I have
> > followed the directions in many of the documents that can be found online
> > on
> > this issue, but without success.
> >
> > I am using SQL Server security and am able to execute queries if I do not
> > begin a transaction. But I cannot execute them if I begin a transaction.
> >
> > When I execute them in a transaction, I get the following error:
> >
> > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> > message
> > "No transaction is active.".
> >
> > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> >
> > The operation could not be performed because OLE DB provider "SQLNCLI" for
> > linked server "linkedserver" was unable to begin a distributed
> > transaction.
> >
> > Does anyone have any ideas?
> >
> > --
> > Corey Young
> >
>
>
Distributed Transactions with SQL Express, Server 2003, and XP SP2
I have SQL Express installed on a Windows XP SP2 machine and on a Windows
Server 2003 machine. I added the Windows Server 2003 machine as a linked
server on the Windows XP SP2 machine.
I have checked and double checked that the DTC settings are correct on both
machines and that the DTC is running on both machines, but I am unable to
execute a distributed transaction. I have even tried playing around with many
different combinations of settings to try to get this to work. I have
followed the directions in many of the documents that can be found online on
this issue, but without success.
I am using SQL Server security and am able to execute queries if I do not
begin a transaction. But I cannot execute them if I begin a transaction.
When I execute them in a transaction, I get the following error:
OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "linkedserver" was unable to begin a distributed transaction.
Does anyone have any ideas?
Corey Young
Can MSDTC get through the XP Firewall?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Young, Corey" <YoungCorey@.discussions.microsoft.com> wrote in message
news:E2E6FDA4-D185-4625-9BEB-D4995254EE70@.microsoft.com...
>I have SQL Express installed on a Windows XP SP2 machine and on a Windows
> Server 2003 machine. I added the Windows Server 2003 machine as a linked
> server on the Windows XP SP2 machine.
> I have checked and double checked that the DTC settings are correct on
> both
> machines and that the DTC is running on both machines, but I am unable to
> execute a distributed transaction. I have even tried playing around with
> many
> different combinations of settings to try to get this to work. I have
> followed the directions in many of the documents that can be found online
> on
> this issue, but without success.
> I am using SQL Server security and am able to execute queries if I do not
> begin a transaction. But I cannot execute them if I begin a transaction.
> When I execute them in a transaction, I get the following error:
> OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> message
> "No transaction is active.".
> Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "linkedserver" was unable to begin a distributed
> transaction.
> Does anyone have any ideas?
> --
> Corey Young
>
Server 2003 machine. I added the Windows Server 2003 machine as a linked
server on the Windows XP SP2 machine.
I have checked and double checked that the DTC settings are correct on both
machines and that the DTC is running on both machines, but I am unable to
execute a distributed transaction. I have even tried playing around with many
different combinations of settings to try to get this to work. I have
followed the directions in many of the documents that can be found online on
this issue, but without success.
I am using SQL Server security and am able to execute queries if I do not
begin a transaction. But I cannot execute them if I begin a transaction.
When I execute them in a transaction, I get the following error:
OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "linkedserver" was unable to begin a distributed transaction.
Does anyone have any ideas?
Corey Young
Can MSDTC get through the XP Firewall?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Young, Corey" <YoungCorey@.discussions.microsoft.com> wrote in message
news:E2E6FDA4-D185-4625-9BEB-D4995254EE70@.microsoft.com...
>I have SQL Express installed on a Windows XP SP2 machine and on a Windows
> Server 2003 machine. I added the Windows Server 2003 machine as a linked
> server on the Windows XP SP2 machine.
> I have checked and double checked that the DTC settings are correct on
> both
> machines and that the DTC is running on both machines, but I am unable to
> execute a distributed transaction. I have even tried playing around with
> many
> different combinations of settings to try to get this to work. I have
> followed the directions in many of the documents that can be found online
> on
> this issue, but without success.
> I am using SQL Server security and am able to execute queries if I do not
> begin a transaction. But I cannot execute them if I begin a transaction.
> When I execute them in a transaction, I get the following error:
> OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> message
> "No transaction is active.".
> Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "linkedserver" was unable to begin a distributed
> transaction.
> Does anyone have any ideas?
> --
> Corey Young
>
Distributed Transactions with SQL Express, Server 2003, and XP SP2
I have SQL Express installed on a Windows XP SP2 machine and on a Windows
Server 2003 machine. I added the Windows Server 2003 machine as a linked
server on the Windows XP SP2 machine.
I have checked and double checked that the DTC settings are correct on both
machines and that the DTC is running on both machines, but I am unable to
execute a distributed transaction. I have even tried playing around with man
y
different combinations of settings to try to get this to work. I have
followed the directions in many of the documents that can be found online on
this issue, but without success.
I am using SQL Server security and am able to execute queries if I do not
begin a transaction. But I cannot execute them if I begin a transaction.
When I execute them in a transaction, I get the following error:
OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "linkedserver" was unable to begin a distributed transaction.
Does anyone have any ideas?
Corey YoungCan MSDTC get through the XP Firewall?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Young, Corey" <YoungCorey@.discussions.microsoft.com> wrote in message
news:E2E6FDA4-D185-4625-9BEB-D4995254EE70@.microsoft.com...
>I have SQL Express installed on a Windows XP SP2 machine and on a Windows
> Server 2003 machine. I added the Windows Server 2003 machine as a linked
> server on the Windows XP SP2 machine.
> I have checked and double checked that the DTC settings are correct on
> both
> machines and that the DTC is running on both machines, but I am unable to
> execute a distributed transaction. I have even tried playing around with
> many
> different combinations of settings to try to get this to work. I have
> followed the directions in many of the documents that can be found online
> on
> this issue, but without success.
> I am using SQL Server security and am able to execute queries if I do not
> begin a transaction. But I cannot execute them if I begin a transaction.
> When I execute them in a transaction, I get the following error:
> OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> message
> "No transaction is active.".
> Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "linkedserver" was unable to begin a distributed
> transaction.
> Does anyone have any ideas?
> --
> Corey Young
>
Server 2003 machine. I added the Windows Server 2003 machine as a linked
server on the Windows XP SP2 machine.
I have checked and double checked that the DTC settings are correct on both
machines and that the DTC is running on both machines, but I am unable to
execute a distributed transaction. I have even tried playing around with man
y
different combinations of settings to try to get this to work. I have
followed the directions in many of the documents that can be found online on
this issue, but without success.
I am using SQL Server security and am able to execute queries if I do not
begin a transaction. But I cannot execute them if I begin a transaction.
When I execute them in a transaction, I get the following error:
OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "linkedserver" was unable to begin a distributed transaction.
Does anyone have any ideas?
Corey YoungCan MSDTC get through the XP Firewall?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Young, Corey" <YoungCorey@.discussions.microsoft.com> wrote in message
news:E2E6FDA4-D185-4625-9BEB-D4995254EE70@.microsoft.com...
>I have SQL Express installed on a Windows XP SP2 machine and on a Windows
> Server 2003 machine. I added the Windows Server 2003 machine as a linked
> server on the Windows XP SP2 machine.
> I have checked and double checked that the DTC settings are correct on
> both
> machines and that the DTC is running on both machines, but I am unable to
> execute a distributed transaction. I have even tried playing around with
> many
> different combinations of settings to try to get this to work. I have
> followed the directions in many of the documents that can be found online
> on
> this issue, but without success.
> I am using SQL Server security and am able to execute queries if I do not
> begin a transaction. But I cannot execute them if I begin a transaction.
> When I execute them in a transaction, I get the following error:
> OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> message
> "No transaction is active.".
> Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "linkedserver" was unable to begin a distributed
> transaction.
> Does anyone have any ideas?
> --
> Corey Young
>
Distributed transactions with multiple instances of Microsoft SQL Server
Hi,
I'm having a problem running a distributed transaction between two
linked servers that both have multiple instances of SQL Server
installed on them. This is the error message that I receive:
"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]."
The query follows the format:
"BEGIN DISTRIBUTED TRAN
UPDATE [LINKEDSERVER1\INSTANCE_NAME].DB.OWNER.TABLENAME
SET fieldname = alias2.fieldname
FROM tablename alias2
JOIN [LINKEDSERVER1\INSTANCE_NAME].DB.OWNER.TABLENAME alias1
on alias2.urn=alias1,urn"
>From what I can gather from various sources the SQL Server must be
named the same as the computer which it is installed on. However, if I
have two instances of SQL Server, they cannot both be named the same as
the computer. Does anyone know of a way around this or whether I'm
barking up the wrong tree completely?
Many thanks.Assuming that you can query the linked server successfully (ie run a
SELECT query), then the issue may be DTC rather than the instance names
- there are a number of possible reasons for the error:
http://support.microsoft.com/kb/306212
http://support.microsoft.com/kb/816701
http://support.microsoft.com/kb/839279
Simon|||Thanks! This one fixed it: http://support.microsoft.com/kb/816701. I
was using Windows 2003 server which has DTC access disabled by default.
Distributed Transactions fail
We are trying to create SPs that are using linked servers. I have been able
to create the linked server and see the views and tables. The issue is that
after creating a SP the uses the 4 part naming convention all atempt to
actually save the querry have failed. In some cases I can run the SQL code
but I just cant save it as a SP
Server: Msg 7391, Level 16, State 1, Line 1
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].
Now before you say visit the MS knowledge base I have and this did not fix
the problem:
http://support.microsoft.com/defaul...kb;en-us;839279
DTC is turned on and the logon is set as a Network Service.What else could
be the issue. Is it user permission probs. Deprately need help. I cant even
save via QA or .NET designer
JP
.NET Software DevelperJP,
Is the DTC service running on both servers? Might look at the 'data access'
, 'rpc' and 'rpc out' settings...not sure if this has anything to do with
what you're experiencing but LSs can sometimes return some error messages
that may not directly point to the cause of an issue.
HTH
Jerry
"JP" <JP@.discussions.microsoft.com> wrote in message
news:8D2DF49D-0F5F-4470-8F26-C6D34209E6B3@.microsoft.com...
> We are trying to create SPs that are using linked servers. I have been
> able
> to create the linked server and see the views and tables. The issue is
> that
> after creating a SP the uses the 4 part naming convention all atempt to
> actually save the querry have failed. In some cases I can run the SQL code
> but I just cant save it as a SP
> Server: Msg 7391, Level 16, State 1, Line 1
> 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].
> Now before you say visit the MS knowledge base I have and this did not fix
> the problem:
> http://support.microsoft.com/defaul...kb;en-us;839279
> DTC is turned on and the logon is set as a Network Service.What else could
> be the issue. Is it user permission probs. Deprately need help. I cant
> even
> save via QA or .NET designer
>
>
> --
> JP
> .NET Software Develper
to create the linked server and see the views and tables. The issue is that
after creating a SP the uses the 4 part naming convention all atempt to
actually save the querry have failed. In some cases I can run the SQL code
but I just cant save it as a SP
Server: Msg 7391, Level 16, State 1, Line 1
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].
Now before you say visit the MS knowledge base I have and this did not fix
the problem:
http://support.microsoft.com/defaul...kb;en-us;839279
DTC is turned on and the logon is set as a Network Service.What else could
be the issue. Is it user permission probs. Deprately need help. I cant even
save via QA or .NET designer
JP
.NET Software DevelperJP,
Is the DTC service running on both servers? Might look at the 'data access'
, 'rpc' and 'rpc out' settings...not sure if this has anything to do with
what you're experiencing but LSs can sometimes return some error messages
that may not directly point to the cause of an issue.
HTH
Jerry
"JP" <JP@.discussions.microsoft.com> wrote in message
news:8D2DF49D-0F5F-4470-8F26-C6D34209E6B3@.microsoft.com...
> We are trying to create SPs that are using linked servers. I have been
> able
> to create the linked server and see the views and tables. The issue is
> that
> after creating a SP the uses the 4 part naming convention all atempt to
> actually save the querry have failed. In some cases I can run the SQL code
> but I just cant save it as a SP
> Server: Msg 7391, Level 16, State 1, Line 1
> 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].
> Now before you say visit the MS knowledge base I have and this did not fix
> the problem:
> http://support.microsoft.com/defaul...kb;en-us;839279
> DTC is turned on and the logon is set as a Network Service.What else could
> be the issue. Is it user permission probs. Deprately need help. I cant
> even
> save via QA or .NET designer
>
>
> --
> JP
> .NET Software Develper
Distributed Transactions Co-ordinator
We are running MS SQL Server 2000, on Windows 2003, and the environment is
locked down to the nth degree.
We are running Distributed Transactions and Java, and are experiencing
problems. We are being told that the reason is because we have COM+
disabled. And that we are likely to experience many other inexplainable
problems if we don't enable it.
The build we have is a standard build, and we have other SQL Server
implementations running with no issue.
Is COM+ required to run SQL Server, Distributed Transactions or allow
connectivitiy in to the database through JAVA?
Thanks
BevNot COM+, you just need MSDTC a very small piece; so you do need to have:
1) The MSDTC service running (NET START MSDTC)
2) The MSDTC service enable for network transactions, which by default on
Windows Server 2003 is turned off, see
http://support.microsoft.com/default.aspx?scid=kb;en-us;817064 "How to
enable network DTC access in Windows Server 2003", you can set this with
MMC, start mmc %windir%\system32\com\comexp.msc
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"Beverley" <Beverley@.discussions.microsoft.com> wrote in message
news:0D039C49-8F62-44D4-A62B-4EE31BE43862@.microsoft.com...
> We are running MS SQL Server 2000, on Windows 2003, and the environment is
> locked down to the nth degree.
> We are running Distributed Transactions and Java, and are experiencing
> problems. We are being told that the reason is because we have COM+
> disabled. And that we are likely to experience many other inexplainable
> problems if we don't enable it.
> The build we have is a standard build, and we have other SQL Server
> implementations running with no issue.
> Is COM+ required to run SQL Server, Distributed Transactions or allow
> connectivitiy in to the database through JAVA?
> Thanks
> Bev
locked down to the nth degree.
We are running Distributed Transactions and Java, and are experiencing
problems. We are being told that the reason is because we have COM+
disabled. And that we are likely to experience many other inexplainable
problems if we don't enable it.
The build we have is a standard build, and we have other SQL Server
implementations running with no issue.
Is COM+ required to run SQL Server, Distributed Transactions or allow
connectivitiy in to the database through JAVA?
Thanks
BevNot COM+, you just need MSDTC a very small piece; so you do need to have:
1) The MSDTC service running (NET START MSDTC)
2) The MSDTC service enable for network transactions, which by default on
Windows Server 2003 is turned off, see
http://support.microsoft.com/default.aspx?scid=kb;en-us;817064 "How to
enable network DTC access in Windows Server 2003", you can set this with
MMC, start mmc %windir%\system32\com\comexp.msc
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"Beverley" <Beverley@.discussions.microsoft.com> wrote in message
news:0D039C49-8F62-44D4-A62B-4EE31BE43862@.microsoft.com...
> We are running MS SQL Server 2000, on Windows 2003, and the environment is
> locked down to the nth degree.
> We are running Distributed Transactions and Java, and are experiencing
> problems. We are being told that the reason is because we have COM+
> disabled. And that we are likely to experience many other inexplainable
> problems if we don't enable it.
> The build we have is a standard build, and we have other SQL Server
> implementations running with no issue.
> Is COM+ required to run SQL Server, Distributed Transactions or allow
> connectivitiy in to the database through JAVA?
> Thanks
> Bev
Distributed Transactions Co-ordinator
We are running MS SQL Server 2000, on Windows 2003, and the environment is
locked down to the nth degree.
We are running Distributed Transactions and Java, and are experiencing
problems. We are being told that the reason is because we have COM+
disabled. And that we are likely to experience many other inexplainable
problems if we don't enable it.
The build we have is a standard build, and we have other SQL Server
implementations running with no issue.
Is COM+ required to run SQL Server, Distributed Transactions or allow
connectivitiy in to the database through JAVA?
Thanks
Bev
Not COM+, you just need MSDTC a very small piece; so you do need to have:
1) The MSDTC service running (NET START MSDTC)
2) The MSDTC service enable for network transactions, which by default on
Windows Server 2003 is turned off, see
http://support.microsoft.com/default...b;en-us;817064 "How to
enable network DTC access in Windows Server 2003", you can set this with
MMC, start mmc %windir%\system32\com\comexp.msc
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Beverley" <Beverley@.discussions.microsoft.com> wrote in message
news:0D039C49-8F62-44D4-A62B-4EE31BE43862@.microsoft.com...
> We are running MS SQL Server 2000, on Windows 2003, and the environment is
> locked down to the nth degree.
> We are running Distributed Transactions and Java, and are experiencing
> problems. We are being told that the reason is because we have COM+
> disabled. And that we are likely to experience many other inexplainable
> problems if we don't enable it.
> The build we have is a standard build, and we have other SQL Server
> implementations running with no issue.
> Is COM+ required to run SQL Server, Distributed Transactions or allow
> connectivitiy in to the database through JAVA?
> Thanks
> Bev
locked down to the nth degree.
We are running Distributed Transactions and Java, and are experiencing
problems. We are being told that the reason is because we have COM+
disabled. And that we are likely to experience many other inexplainable
problems if we don't enable it.
The build we have is a standard build, and we have other SQL Server
implementations running with no issue.
Is COM+ required to run SQL Server, Distributed Transactions or allow
connectivitiy in to the database through JAVA?
Thanks
Bev
Not COM+, you just need MSDTC a very small piece; so you do need to have:
1) The MSDTC service running (NET START MSDTC)
2) The MSDTC service enable for network transactions, which by default on
Windows Server 2003 is turned off, see
http://support.microsoft.com/default...b;en-us;817064 "How to
enable network DTC access in Windows Server 2003", you can set this with
MMC, start mmc %windir%\system32\com\comexp.msc
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Beverley" <Beverley@.discussions.microsoft.com> wrote in message
news:0D039C49-8F62-44D4-A62B-4EE31BE43862@.microsoft.com...
> We are running MS SQL Server 2000, on Windows 2003, and the environment is
> locked down to the nth degree.
> We are running Distributed Transactions and Java, and are experiencing
> problems. We are being told that the reason is because we have COM+
> disabled. And that we are likely to experience many other inexplainable
> problems if we don't enable it.
> The build we have is a standard build, and we have other SQL Server
> implementations running with no issue.
> Is COM+ required to run SQL Server, Distributed Transactions or allow
> connectivitiy in to the database through JAVA?
> Thanks
> Bev
Distributed Transactions Co-ordinator
We are running MS SQL Server 2000, on Windows 2003, and the environment is
locked down to the nth degree.
We are running Distributed Transactions and Java, and are experiencing
problems. We are being told that the reason is because we have COM+
disabled. And that we are likely to experience many other inexplainable
problems if we don't enable it.
The build we have is a standard build, and we have other SQL Server
implementations running with no issue.
Is COM+ required to run SQL Server, Distributed Transactions or allow
connectivitiy in to the database through JAVA?
Thanks
BevNot COM+, you just need MSDTC a very small piece; so you do need to have:
1) The MSDTC service running (NET START MSDTC)
2) The MSDTC service enable for network transactions, which by default on
Windows Server 2003 is turned off, see
http://support.microsoft.com/defaul...kb;en-us;817064 "How to
enable network DTC access in Windows Server 2003", you can set this with
MMC, start mmc %windir%\system32\com\comexp.msc
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Beverley" <Beverley@.discussions.microsoft.com> wrote in message
news:0D039C49-8F62-44D4-A62B-4EE31BE43862@.microsoft.com...
> We are running MS SQL Server 2000, on Windows 2003, and the environment is
> locked down to the nth degree.
> We are running Distributed Transactions and Java, and are experiencing
> problems. We are being told that the reason is because we have COM+
> disabled. And that we are likely to experience many other inexplainable
> problems if we don't enable it.
> The build we have is a standard build, and we have other SQL Server
> implementations running with no issue.
> Is COM+ required to run SQL Server, Distributed Transactions or allow
> connectivitiy in to the database through JAVA?
> Thanks
> Bev
locked down to the nth degree.
We are running Distributed Transactions and Java, and are experiencing
problems. We are being told that the reason is because we have COM+
disabled. And that we are likely to experience many other inexplainable
problems if we don't enable it.
The build we have is a standard build, and we have other SQL Server
implementations running with no issue.
Is COM+ required to run SQL Server, Distributed Transactions or allow
connectivitiy in to the database through JAVA?
Thanks
BevNot COM+, you just need MSDTC a very small piece; so you do need to have:
1) The MSDTC service running (NET START MSDTC)
2) The MSDTC service enable for network transactions, which by default on
Windows Server 2003 is turned off, see
http://support.microsoft.com/defaul...kb;en-us;817064 "How to
enable network DTC access in Windows Server 2003", you can set this with
MMC, start mmc %windir%\system32\com\comexp.msc
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Beverley" <Beverley@.discussions.microsoft.com> wrote in message
news:0D039C49-8F62-44D4-A62B-4EE31BE43862@.microsoft.com...
> We are running MS SQL Server 2000, on Windows 2003, and the environment is
> locked down to the nth degree.
> We are running Distributed Transactions and Java, and are experiencing
> problems. We are being told that the reason is because we have COM+
> disabled. And that we are likely to experience many other inexplainable
> problems if we don't enable it.
> The build we have is a standard build, and we have other SQL Server
> implementations running with no issue.
> Is COM+ required to run SQL Server, Distributed Transactions or allow
> connectivitiy in to the database through JAVA?
> Thanks
> Bev
Distributed Transactions between SQL2005 and SQL2000
Hi there,
We have two servers, one (we'll call 'SERVERA') has SQL2005 running on it. The second (we'll call 'YELLOWSTEONE') is running both SQL2000 and SQL2005 on it. The SQL instances on YELLOWSTONE are 'YELLOWSTONE\SQL2000' and 'YELLOWSTONE\SQL2005'. As a linked server, I have an entry for YELLOWSTONE which then links to the SQL Server of YELLOWSTONE\SQL2000 on the server network name of YELLOWSTONE. By them selves they seem to run fine. However, if I have trigger that Runs on SERVERA to do a distributed transaction on 'YELLOWSTONE\SQL2000', I get the following error:
OLE DB provider "SQLNCLI" for linked server "YELLOWSTONE" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "YELLOWSTONE" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 2, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [2].
If You can provide me any assistance, I would greatly appreciate it. Thanks! - Eric -
Ok, Figured it out. I needed to define the Server type as 'SQL Server' instead of 'Other' and then picking the SQL Server connector. Ooops!
Subscribe to:
Posts (Atom)