Copy Database Wizard Error- SQL Server Agent cannot execute the SSIS package

I am trying to

copy a database from our company's external SQL

Server(production) to our local SQL

Server(development). The Copy Database wizard fails on the step

"Execute SQL Server Agent Job".
Following is the error in the log file.. Please advise

InnerException-->An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Where did you run your package?|||The Copy Database Wizard ran the SSIS package automatically, at the end of the wizard.|||The Copy Database Wizard ran the SSIS package automatically.|||Oops. I thought you were using transfer database task in SSIS. I think you are at the wrong forum. I assume you initiated copy database wizard from sql management studio.Try posting your issue at the right forum:
SQL Management studio: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=597172&SiteID=1
SMO: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=88&SiteID=1

Also, before doing that you may want to check if both the source and destination servers support remote connections.|||Thanks for pointing me to the fourm. Yes I am using Management Studio for the Copy Database Wizard. Could you please tell me how I can check to see if the source and the destination servers allow remote connections? Is that a Windows Server2003 setting or a SQL Server2005 setting?
Thanks!|||1. Open "SQL Server 2005 Surface Area Configuration" (in Programs-> Microsoft SQL Server 2005 -> Configuration Tools)
2. Click on "Surface Area Configuraiton for Services and Connections"
3. Select "Remote Connections" option for the instance you are interested in.
4. Select "Local and Remove connections"

In SQL Server Management studio, right click on the instance, select properties, go to connections page and select "Allow remote connections tot his server".

I do not know what the default settings are. So, check both.|||When I click on Remote Connections, I get a message saying"You cannot configure surface area of clustered services by connecting to a computer name. Connect to the virtual server to configure clustered services"

Do you know how/where I can do that?
Thanks again!

Copy Database from SQL 2000 to SQL 2005

I'm trying to use "Copy Database" to copy a database from a SQL2000 instance
to a SQL2005 instance; everthing looks ok until I execute the operation and
get an error message on the action "Create Package" of "One or more
arguments are invalid (Copy Database Wizard)"
I've gone over it and over it and cannot see where the problem might be ...
TIA for any suggestions ..
LI would use SSIS for this instead of DTS
"Liz" <liz@.tiredofspam.com> wrote in message
news:e82zA%2346HHA.1164@.TK2MSFTNGP02.phx.gbl...
> I'm trying to use "Copy Database" to copy a database from a SQL2000
> instance to a SQL2005 instance; everthing looks ok until I execute the
> operation and get an error message on the action "Create Package" of "One
> or more arguments are invalid (Copy Database Wizard)"
> I've gone over it and over it and cannot see where the problem might be
> ...
> TIA for any suggestions ..
> L
>

Copy Database error on Execute SQL Server Agent Job

Interestingly I can get further with Copy Database than I did in the last beta. It still won't finish successfully though. Here's a log from one of my attempts.

Thanks in advance!

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
OnPreValidate,RATBERT,NT AUTHORITY\SYSTEM,CDW_CSIADRESKO_RATBERT_0,{00F97964-A8B9-4816-9A87-A39C952A31AA},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,(null)
OnPreValidate,RATBERT,NT AUTHORITY\SYSTEM,CSIADRESKO_RATBERT_Transfer Objects Task,{F7EFCB8D-7553-4E4D-9859-7345D3F6D922},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,(null)
OnPostValidate,RATBERT,NT AUTHORITY\SYSTEM,CSIADRESKO_RATBERT_Transfer Objects Task,{F7EFCB8D-7553-4E4D-9859-7345D3F6D922},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,(null)
OnPostValidate,RATBERT,NT AUTHORITY\SYSTEM,CDW_CSIADRESKO_RATBERT_0,{00F97964-A8B9-4816-9A87-A39C952A31AA},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,(null)
PackageStart,RATBERT,NT AUTHORITY\SYSTEM,CDW_CSIADRESKO_RATBERT_0,{00F97964-A8B9-4816-9A87-A39C952A31AA},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,Beginning of package execution.

Diagnostic,RATBERT,NT AUTHORITY\SYSTEM,CDW_CSIADRESKO_RATBERT_0,{00F97964-A8B9-4816-9A87-A39C952A31AA},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,Maximum concurrent executables are set to 1.

OnPreExecute,RATBERT,NT AUTHORITY\SYSTEM,CDW_CSIADRESKO_RATBERT_0,{00F97964-A8B9-4816-9A87-A39C952A31AA},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,(null)
OnPreExecute,RATBERT,NT AUTHORITY\SYSTEM,CSIADRESKO_RATBERT_Transfer Objects Task,{F7EFCB8D-7553-4E4D-9859-7345D3F6D922},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,(null)
OnPreValidate,RATBERT,NT AUTHORITY\SYSTEM,CSIADRESKO_RATBERT_Transfer Objects Task,{F7EFCB8D-7553-4E4D-9859-7345D3F6D922},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,(null)
OnPostValidate,RATBERT,NT AUTHORITY\SYSTEM,CSIADRESKO_RATBERT_Transfer Objects Task,{F7EFCB8D-7553-4E4D-9859-7345D3F6D922},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,(null)
OnProgress,RATBERT,NT AUTHORITY\SYSTEM,CSIADRESKO_RATBERT_Transfer Objects Task,{F7EFCB8D-7553-4E4D-9859-7345D3F6D922},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,Task just started the execution.
OnInformation,RATBERT,NT AUTHORITY\SYSTEM,CSIADRESKO_RATBERT_Transfer Objects Task,{F7EFCB8D-7553-4E4D-9859-7345D3F6D922},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,Connecting to server CSIADRESKO
OnInformation,RATBERT,NT AUTHORITY\SYSTEM,CDW_CSIADRESKO_RATBERT_0,{00F97964-A8B9-4816-9A87-A39C952A31AA},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,Connecting to server CSIADRESKO
OnError,RATBERT,NT AUTHORITY\SYSTEM,CSIADRESKO_RATBERT_Transfer Objects Task,{F7EFCB8D-7553-4E4D-9859-7345D3F6D922},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:56 PM,11/15/2005 1:15:56 PM,0,0x,Failed to connect to server CSIADRESKO.
StackTrace: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server& server, ServerProperty serverProp)
InnerException-->Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
StackTrace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)
at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable)
at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
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()
OnError,RATBERT,NT AUTHORITY\SYSTEM,CDW_CSIADRESKO_RATBERT_0,{00F97964-A8B9-4816-9A87-A39C952A31AA},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:56 PM,11/15/2005 1:15:56 PM,0,0x,Failed to connect to server CSIADRESKO.
StackTrace: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server& server, ServerProperty serverProp)
InnerException-->Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
StackTrace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)
at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable)
at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
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()
OnProgress,RATBERT,NT AUTHORITY\SYSTEM,CSIADRESKO_RATBERT_Transfer Objects Task,{F7EFCB8D-7553-4E4D-9859-7345D3F6D922},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:56 PM,11/15/2005 1:15:56 PM,100,0x,Transfer objects finished execution.
OnTaskFailed,RATBERT,NT AUTHORITY\SYSTEM,CSIADRESKO_RATBERT_Transfer Objects Task,{F7EFCB8D-7553-4E4D-9859-7345D3F6D922},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:56 PM,11/15/2005 1:15:56 PM,0,0x,(null)
OnPostExecute,RATBERT,NT AUTHORITY\SYSTEM,CSIADRESKO_RATBERT_Transfer Objects Task,{F7EFCB8D-7553-4E4D-9859-7345D3F6D922},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:56 PM,11/15/2005 1:15:56 PM,0,0x,(null)
OnWarning,RATBERT,NT AUTHORITY\SYSTEM,CDW_CSIADRESKO_RATBERT_0,{00F97964-A8B9-4816-9A87-A39C952A31AA},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:56 PM,11/15/2005 1:15:56 PM,-2147381246,0x,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.

OnPostExecute,RATBERT,NT AUTHORITY\SYSTEM,CDW_CSIADRESKO_RATBERT_0,{00F97964-A8B9-4816-9A87-A39C952A31AA},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:56 PM,11/15/2005 1:15:56 PM,0,0x,(null)
PackageEnd,RATBERT,NT AUTHORITY\SYSTEM,CDW_CSIADRESKO_RATBERT_0,{00F97964-A8B9-4816-9A87-A39C952A31AA},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:56 PM,11/15/2005 1:15:56 PM,1,0x,End of package execution.

////////////////
HERE'S ANOTHER entry from the event log.

Event Name: OnError

Message: Failed to connect to server CSIADRESKO.

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

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

InnerException-->An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

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

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

at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)

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_CSIADRESKO_RATBERT_1

Source ID: {37C99953-92D4-40ED-B1CA-943B9D08D513}

Execution ID: {45DAB9CD-CEF6-4F40-8FAA-5F79967B9B70}

Start Time: 11/15/2005 1:25:56 PM

End Time: 11/15/2005 1:25:56 PM

Data Code: 0

For more information, see Help and Support Center at

Hello Alex Dresko

The error seems to be a time-out error that happened when the SSIS task was attempting to connect to the SQL server.

This could be a generic time-out that happens for all connections to the remote server. You could try to connect to the remote server using object explorer / query analyzer (or) execute the task [Agent job] manually on the remote server to see if that solves the issue.

If a time-out occurs only when using CDW, please provide details on what version of SQL server your source server is, what trasnfer type you are using (Detach&Attach / SMO transfer) , any other environment specific information that you suspect here.

Thanks,
[Venkat]|||

This appears to be an SSIS configuration issue:

OnError,RATBERT,NT AUTHORITY\SYSTEM,CSIADRESKO_RATBERT_Transfer Objects Task,{F7EFCB8D-7553-4E4D-9859-7345D3F6D922},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:56 PM,11/15/2005 1:15:56 PM,0,0x,Failed to connect to server CSIADRESKO.
StackTrace: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server& server, ServerProperty serverProp)
This error may be related to whether the server is configured to allow Named Pipe connections:

InnerException-->An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Have you configured your server to accept Named Pipe connections? You can use the Surface Area Configuration tool (Start > All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration) to do that.

You might also consider posting this issue in the SSIS forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1) - they might have additional information on how to configure SSIS to do what you need.

|||Sweet! My server was set up to use TCP only, while my workstation was set to Local only. Both were upgrades from betas.

I changed both to tcp/named pipes and everything works!

Thanks SO much! I would have never thought about that.