DTS error when Copying Objects

I am getting the following error when executing a Copy SQL Server Objects Task. If it helps these objects are User Defined functions and also this had worked in the past it is only after changing the destination server to one that is offsite, has a different OS then the source and also runs as a DC. We are running SQL 2000 Server Standard with Spk 3a on both boxes.

Step 'DTSStep_DTSTransferObjectsTask_6' failed

Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 42S02)
Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.GetRightsAbbreviations'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.GetRights'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.GetTerritoryAbbreviations'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.GetTerritories'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.GetShow'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.GetTvEpisodes'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.GetTvSegments'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.GetTvSegmentsString'.
Step Error code: 800400D0
Step Error Help File:SQLDMO80.hlp
Step Error Help Context ID:1131Check object owner. This is the most common reason which is clearly seen from the error message you provided.sql

DTS and stopred procedures

I have a sproc defined many databases. It has the same neme in every databases and does the same in every databases too.

I have a table that lists all the companies where the sproc exists. (BISYMAP)
The field that holds the database name is INTERID.

I need to run every instance of the sproc that exists on my SQL server. I did a cursor that returns the database name into a variable and I use an EXEC statement to run the sprocs. I build the name using the variable filled by the cursor. (@.TgtCoy)

Here it is.
__________________________________________________ __

/*List all companies from system table and put it in @.TgtCoy*/
DECLARE @.TgtCoy AS CHAR(5)

DECLARE Coys CURSOR FOR
SELECT DISTINCT RTRIM(INTERID)
FROM BISYMAP
WHERE INTERID <> ''

OPEN Coys

FETCH NEXT FROM Coys INTO @.TgtCoy

WHILE @.@.FETCH_STATUS = 0
BEGIN
/* Execute biCoMapGlAccount within each company*/
EXEC('EXEC ' + @.TgtCoy + '.dbo.biCoMapGlAccount')
FETCH NEXT FROM Coys INTO @.TgtCoy
END
CLOSE Coys
DEALLOCATE Coys

__________________________________________

This runs fine in SQL Query Analyzer. The issue is when I try to run this from an SQL Task in a DTS package. I can't even save the task. SQL tries to validate the query before saving and returns the following error: ... Could not find stored procedure '.dbo.biCoMapGlAccount'.

I also tried to put this into system stored procedure and call it from my DTS package but I get the same error.

Anybody ever had to deal with such a situation? If so, I would certainly appreciate a hint or two on how you didi it.

Thanks in advance

RomboltI found the answer to my problem... and I'm almost ashamed to tell.

I insisted on pressing the "Parse Query" button after filling the query in the DTS SQL Task, and that's when I would get the error... Well, guess what, all I had to do was NOT to press it. That way the expression is not evaluated and if it is syntaxly correct, it will work fine at runtime.

If you press "parse query", it looks like SQL evaluates the query line by line so, in my case, it failed.

That's all there was to it.

Thanks

Rombolt

DTC (C!) fails on same server

Is it a known issue that DTC will fail using SQL OLE defined links from and
to the same server? If I change servers within the link properties all is
well. If I link to myself, or another instance on myself, this is what I
get:
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].
It makes sense that it fails, in a way, because DTC is being both a target
and a sender.
Thanks,
JohnMSDTC does not work in loopback mode. From BOL, topic "loopback linked
servers":
Loopback linked servers cannot be used in a distributed transaction.
Attempting a distributed query against a loopback linked server from within
a distributed transaction causes an error:
Msg: 3910 Level: 16 State: 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by
another session.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"John Beatty" <jbeatty@.wmsgaming.com> wrote in message
news:%23faMtUzKFHA.2136@.TK2MSFTNGP14.phx.gbl...
> Is it a known issue that DTC will fail using SQL OLE defined links from
> and
> to the same server? If I change servers within the link properties all is
> well. If I link to myself, or another instance on myself, this is what I
> get:
> 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].
> It makes sense that it fails, in a way, because DTC is being both a target
> and a sender.
> Thanks,
> John
>
>