sql server programming:

I'm unable to copy my Stored Procedures from one database to another.
I'm using mixed mode authentication. I have set
CopyAllStoredProcedures to True, DropObjectsFirst to True and
CopySchema to True.



Nothing gets copied. I have followed many web sites that say Transfer
SQL Server Objects Task is broken. Is this true and I should give up?



Also, I'm on SQL 2005 SP2 which appears to be the latest and I assume
is the update for SSIS? yes ?



Thanks for any help



[Transfer SQL Server Objects Task] Error: Execution failed with the
following error: "ERROR : errorCode=-1073548784 description=Executing
the query "CREATE PROCEDURE [dbo].
[del_Admin_RemoveContractorFromContract] @ContractID int,
@ContractorID int AS DELETE FROM CONTRACTOR_CONTRACTS WHERE
CONTRACT_ID = @ContractID AND CONTRACTOR_ID = @ContractorID DELETE
FROM CONTRACTOR_USER_CONTRACTS WHERE CONTRACT_ID = @ContractID AND
CONTRACTOR_ID = @ContractorID " failed with the following error:
"There is already an object named
'del_Admin_RemoveContractorFromContract' in the database.". Possible
failure reasons: Problems with the query, "ResultSet" property not set
correctly, parameters not set correctly, or connection not established
correctly. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-
E9D8-4D23-9739-DA807BCDC2AC}".



Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The
Execution method succeeded, but the number of errors raised (1)
reached the maximum allowed (1); resulting in failure. This occurs
when the number of errors reaches the number specified in
MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Hello,

I suggest generating scripts for the SP-s (instead of using SSIS) and
executing the generated scripts on the target database. You can right
click on the database and choose Tasks / Generate Scripts, or you can
use the Database Publishing Wizard from http://www.codeplex.com/sqlhost

Razvan Socol

[quoted text, click to view]

In this case, you can use the following procedure to copy all SP-s from
a database to another:

CREATE PROCEDURE CopyAllProcedures
(@SourceDB sysname, @DestinationDB sysname)
AS
DECLARE @SQL nvarchar(max)
SET @SQL='
USE '+QUOTENAME(@SourceDB)+'
DECLARE Procs CURSOR LOCAL FOR
SELECT o.name, m.definition
FROM sys.objects o INNER JOIN sys.sql_modules m
ON o.object_id=m.object_id WHERE o.type=''P''
AND OBJECTPROPERTY(o.object_id,''IsMSShipped'')=0
ORDER BY name

USE '+QUOTENAME(@DestinationDB)+'
OPEN Procs
DECLARE @Name sysname, @Definition nvarchar(max)

WHILE 1=1 BEGIN
FETCH NEXT FROM Procs INTO @Name, @Definition
IF @@FETCH_STATUS<>0 BREAK

IF OBJECT_ID(@Name) IS NOT NULL BEGIN
DECLARE @SQL nvarchar(max)
SET @SQL=''DROP PROCEDURE ''+QUOTENAME(@Name)
EXEC (@SQL)
END

EXEC (@Definition)
END

CLOSE Procs
DEALLOCATE Procs
'

EXEC (@SQL)
GO

The procedure ignores the uses_ansi_nulls property (and maybe other
similar settings), but can be enhanced to consider them too. The
procedure also assumes that the tables, views and other dependencies
for the SP-s are already updated (otherwise, we can encounter "Invalid
column name" errors when we create the new SP-s).

--
Razvan Socol

But I need these to run on a schedule. Is SSIS a piece of crap or can
it do these basic tasks?

Thanks for the great reply but I"m trying to use SSIS. Can anyone
help with doing this in SSIS ?