DTS Task Marked as Failed Because of sp_rename

[JohnBates]

hi everyone,

I dont like to cross-post, but got no replies in SQL Server Programming forum.

This DTS package is failing because of the Warning message you get when you rename a table.
The Execute SQL Task runs a stored proc which uses 'exec sp_rename... ' to simply rename a user table.

Is there a way of telling it to ignore this error?

Note: the package/job DOES perform the table renames;
it does run all the steps.

(The package runs as a scheduled Job so it gets flagged as Red/failed when the job completes)

Thanks. John


The DTS Package Log shows:
SQLTask_4 failed at 8:41 am “Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:The object was renamed to 'tblNatRegSumM'. (Microsoft OLE DB Provider for SQL Server (80040e37): Caution: Changing any part of an object name could break scripts and stored procedures.) (Microsoft OLE DB Provider for SQL Server (80040e37): The object was renamed to 'tblNatRegSum'.) (Microsoft OLE DB Provider for SQL Server (80040e37): Caution: Changing any part of an object name could break scripts and stored procedures.) (Microsoft OLE DB Provider for SQL Server (80040e37): The object was renamed to 'tblUsage'.) (Microsoft OLE DB Provider for SQL Server (80040e37): Caution: Changing any part of an object name could break scripts and stored procedures.) (Microsoft OLE DB Provider for SQL Server (80040e37): The object was renamed to 'tblMPR'.) (Microsoft OLE DB Provider for SQL Server (80040e37): Caution: Changing any part of an object name could break scripts and stored procedures.) (Microsoft OLE DB Provider for SQL Serv”

 

[JohnBates]

.... i wasn't able to get this to work by doing everthing in the package.

So I removed the last SQL Task which was calling the sp to do the table renames.

Added another step in the Job to execute that sp.

Now the Job completes successfully and everyone is happy.

Still dont understand why it failed when I let the package perform the renames, yet this way it doesnt fail. Wierd

John