Comments
3 comments
-
TJay,
Did this used to work in SQL Compare 5? Can you try to CREATE this proc using SSMS in another database on this server, do you get the same error?
I can only create this proc on a server if the database and table that it refers to exists on a linked server. This is in both SSMS and SQL Compare so I think it is a behaviour of SQL Server.
Is the target a 2000 or 2005 database? What type of linked server is it?
Ta,
Jonathan -
I did not attempt this in sql compare 5. Only via sql compare 6.
It is sql 2000. not 2005.
There is a linked server by that name. I had no idea there were linked servers on that box.
I will investigate more. -
TJ,
If you investigations run you up a dead end, András has suggested that manually executing the script and changing the transaction isolation level to readcommitted "could" work around this issue.
Unfortunately, setting the execution mode is not something that SQL Compare can do automatically.
Regards,
Jonathan
Add comment
Please sign in to leave a comment.
What was being done was a sync of two procs. Both of which had OpenQuery commands within them.
When i execute the procs, they functino fine. I can even alter the procs to the new code, and execute them, and they work. But Sql Compare gave me the following error.
It seems like it was actually attempting to execute the code, not just alter the procedure. It says it couldnt start a distributed transaction... but it shouldnt have had too, to perform an alter...
The following error message was returned from the SQL Server:
[7391] 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 following SQL command caused the error:
ALTER PROCEDURE WSP_REPORT_GET_REMOTE_MR_DATA AS
if (SELECT object_id('T_WF_MR_TRACKING_CACHE')) IS NOT NULL
TRUNCATE TABLE dbo.T_WF_MR_TRACKING_CACHE
INSERT INTO dbo.T_WF_MR_TRACKING_CACHE
(DCN,
BUNDLE_BATCH,
DOC_CLASS,
DOC_TYPE,
DOC_SUBTYPE,
PRIORITY,
LOGGER,
PREPPER,
SCANNER,
LOGDATE,
RECEIVE_DATE,
NOINV,
NOPAGES,
DUPLEX,
MULTIPAGE,
SCANDATE,
RESCANDATE,
BOXNO,
SENT_TO_RESCAN,
FTP_TO_DE,
COORD_COMPLETED,
PROCESS_ID,
INACTIVATED,
RESCAN_DCN,
UPDATED,
MAIL_TYPE,
RESCAN_INV,
APPEND_IMG)
SELECT
DCN,
BUNDLE_BATCH,
DOC_CLASS,
DOC_TYPE,
DOC_SUBTYPE,
PRIORITY,
LOGGER,
PREPPER,
SCANNER,
LOGDATE,
RECEIVE_DATE,
NOINV,
NOPAGES,
DUPLEX,
MULTIPAGE,
SCANDATE,
RESCANDATE,
BOXNO,
SENT_TO_RESCAN,
FTP_TO_DE,
COORD_COMPLETED,
PROCESS_ID,
INACTIVATED,
RESCAN_DCN,
UPDATED,
MAIL_TYPE,
RESCAN_INV,
APPEND_IMG
FROM OPENQUERY(ACS_SLR_MR, 'SELECT DCN,
BUNDLE_BATCH,
DOC_CLASS,
DOC_TYPE,
DOC_SUBTYPE,
PRIORITY,
LOGGER,
PREPPER,
SCANNER,
LOGDATE,
RECEIVE_DATE,
NOINV,
NOPAGES,
DUPLEX,
MULTIPAGE,
SCANDATE,
RESCANDATE,
BOXNO,
SENT_TO_RESCAN,
FTP_TO_DE,
COORD_COMPLETED,
PROCESS_ID,
INACTIVATED,
RESCAN_DCN,
UPDATED,
MAIL_TYPE,
RESCAN_INV,
APPEND_IMG
FROM ACS_SLR_MAILROOM..T_MR_TRACKING (nolock)')
The following messages were returned from the SQL Server:
[0] Altering [dbo].[WSP_REPORT_GET_REMOTE_MR_DATA]