Converting problem: Copy Database Wizard needs what?

I am trying to launch Copy database wizard from SS2005 SP2 Developer Ed. (on Windows XP SP2) to get databases from SS2005 SP1 Enterprise Ed. (on Windows 2003 Server Enterprise Ed.)

The same collation, default language, SQL Server Agent running on both

I use Windows Authentication with the same sysadmin, part of Administrators on both machines, with the same password. It is in workgroup

I am getting various errors from various attempts.

Ошибка DCOM "Указанная служба не может быть запущена, поскольку она отключена или все связанные с ней устройства отключены. " при попытке запуска службы MsDtsServer с аргументами "" для запуска сервера:

{ABF05265-635E-44B0-A28F-AEA45247ACA0}
(i.e. service MsDtsServer with arguments is switched off)

Таймаут (30000 мс) ожидания для ответа при транзакции от службы clr_optimization_v2.0.50727_32.

Copy Database Wizard shows "The wizard will create a Integration Services package with the properties yo specify below". And it is not enabled on remote SS2005 SP1

So, my questions are what exactly should I have and where in order to run CopyDatabase Wizard to get things from remote server:

1)
Is it possible to have Integration Services running only at one of the SS2005? from which I run the wizard

Should CLR integration be enabled? only at one of the SS2005 (from which I ran wizard)?

3)MsDts Services? only at one of the SS2005 (from which I run wizard)?

What else? firewall, etc.

You need the Integration Services Service to be running on the destination machine and also SQL Agent if you are scheduling the package to run.

You do not need SQLCLR.

The firewall needs to to support the SQL Server and Integration Services ports (you need to look up IS but SQL Server defaults to 1433)

|||

Are you trying port the SSIS package on any of the client machine.

http://vyaskn.tripod.com/sql_server_2005_making_ssis_packages_portable.htm and ensure all the XML configuration files are copied across teh server & client.

Also http://aspalliance.com/articleviewer.aspx?aId=947a good run on DTS - SSIS understanding.

|||

No, I do not have SSIS packages on source machine and I do not intend to port any.

As I understood, the Copy Database Wizard always ends up with
"The wizard will create a Integration Services package with the properties you specify below
Package name:
"

And why do I get MSDtsServer error?
Can it be connected with the mirroring on source server?

|||Have you attempted Service pack 2 for SQL SErver?|||

Now I have both with SP2 (90.3054) with postSP2 hotfix
http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx
http://support.microsoft.com/?kbid=934458

(BTW it is very confusing that 3159, as I understood, is the same as 3054?)

I manage to run it between different instances on the same machine but not from remote one.

Both machines is in workgroup, I use Windows Auth with the same login fro
I use SQL Management Object method
I mark one db to copy to a new one at destination. It is just to pass forward (CDW doesn’t advance with no databases marked) to screen offering me to copy:
- Stored procedures from master database
- SQL Server Agent jobs
- User-defined error messages
- Endpoints
- SSIS packages
- Logins

I choose to copy only Logins(stored procedures from master db. I tried other options too, but this one is the most innocuous from all)

Integration Services Proxy account: SQL Service Account . Run immediately

I cannot change it or have any other choice

Performing operation…
- Add log for package Success
- Add task for transferring database objects Success
- Create package Success
- Start SQL Server Agent Job Error

TITLE: Copy Database Wizard

The job failed. Check the event log on the destination server for details.

Event log shows:

Package "CDW_<source_server>_<target_server>_<target_instance_name>_6" failed.

Дополнительные сведения можно найти в центре справки и поддержки, в http://go.microsoft.com/fwlink/events.asp.

Eveent Log shows various erros:
1)

Package "target_server>_<target_instance_name>_6" failed.

Дополнительные сведения можно найти в центре справки и поддержки, в http://go.microsoft.com/fwlink/events.asp.
2)

Event Name: OnWarning

Message: 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.

Operator: NT AUTHORITY\SYSTEM

Source Name: CDW_SERVER310_CELLO_C310_4

Source ID: {037E2C87-3F49-432C-8D67-04485FCD0C7D}

Execution ID: {FFDBBCE1-6BA4-46C3-9257-8F5DA91F44E4}

Start Time: 30.05.2007 12:14:54

End Time: 30.05.2007 12:14:54

Data Code: -2147381246

Дополнительные сведения можно найти в центре справки и поддержки, в "http://go.microsoft.com/fwlink/events.asp".

Event Name: OnTaskFailed

Message:

Operator: NT AUTHORITY\SYSTEM

Source Name: SERVER310_CELLO_C310_Transfer Objects Task

Source ID: {DAF81427-B701-4329-BAB2-CFDB1B573E25}

Execution ID: {FFDBBCE1-6BA4-46C3-9257-8F5DA91F44E4}

Start Time: 30.05.2007 12:14:54

End Time: 30.05.2007 12:14:54

Data Code: 0

Дополнительные сведения можно найти в центре справки и поддержки, в "http://go.microsoft.com/fwlink/events.asp".

4)
Event Name: OnError

Message: Failed to connect to server SERVER310.

StackTrace: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server& server, ServerProperty serverProp)

InnerException-->Login failed for user 'NT AUTHORITY\АНОНИМНЫЙ ВХОД'.

StackTrace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)

at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)

at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)

at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

Operator: NT AUTHORITY\SYSTEM

Source Name: CDW_SERVER310_CELLO_C310_4

Source ID: {037E2C87-3F49-432C-8D67-04485FCD0C7D}

Execution ID: {FFDBBCE1-6BA4-46C3-9257-8F5DA91F44E4}

Start Time: 30.05.2007 12:14:54

End Time: 30.05.2007 12:14:54

Data Code: 0

Дополнительные сведения можно найти в центре справки и поддержки, в "http://go.microsoft.com/fwlink/events.asp".

5)
Event Name: OnError