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.

Copy a table minimizing log space

Yes, I know this is a silly situation.
I have an application that uploads several tables to a remote SQL Server
that is part of a web site. The records are uploaded into a dedicated
upload table, then the table they belong in is truncated and the records
copied using
INSERT INTO ... (SELECT ...)
My problem is that the web hosting company is very stingy with log space
and this approach will place the entire table into the log during the
copy. This is failing for the larger tables.
Is there are way for a remote app to copy data from one table to
another, minimizing the use of log space. I have no access to the file
system, so bulk copy is not an option.
Thanks,
++PLSHi
How big are your tables?
What is RECOVERY mode of the database?
Can you divide you inserting into a small transaction ?
INSERT INTO Table SELECT * FROM OtherTable WHERE --put here some logic
OR
SET ROWCOUNT 1000
INSERT INTO Table SELECT * FROM OtherTable
SET ROWCOUNT 0
"pls" <elth-rs1p@.spamex.com> wrote in message
news:uqJF8i8JGHA.2304@.TK2MSFTNGP15.phx.gbl...
> Yes, I know this is a silly situation.
> I have an application that uploads several tables to a remote SQL Server
> that is part of a web site. The records are uploaded into a dedicated
> upload table, then the table they belong in is truncated and the records
> copied using
> INSERT INTO ... (SELECT ...)
> My problem is that the web hosting company is very stingy with log space
> and this approach will place the entire table into the log during the
> copy. This is failing for the larger tables.
> Is there are way for a remote app to copy data from one table to another,
> minimizing the use of log space. I have no access to the file system, so
> bulk copy is not an option.
> Thanks,
> ++PLS|||RECOVERY mode is simple.
I can't think of any logic that could be in a WHERE clause to divide the
table anything like evenly. I like your second approach (although I
think you meant ...SELECT TOP 1000). But then how do you get to the
second 1000?
++PLS
Uri Dimant wrote:
> Hi
> How big are your tables?
> What is RECOVERY mode of the database?
> Can you divide you inserting into a small transaction ?
> INSERT INTO Table SELECT * FROM OtherTable WHERE --put here some logic
> OR
> SET ROWCOUNT 1000
> INSERT INTO Table SELECT * FROM OtherTable
> SET ROWCOUNT 0
> "pls" <elth-rs1p@.spamex.com> wrote in message
> news:uqJF8i8JGHA.2304@.TK2MSFTNGP15.phx.gbl...
>
>
>|||pls
> table anything like evenly. I like your second approach (although I think
> you meant ...SELECT TOP 1000). But then how do you get to the second 1000?
No, it is exactly SET ROWCOUNT (See in the BOL)
CREATE TABLE #Test
(
col INT NOT NULL PRIMARY KEY
)
--Insert 10 rows
INSERT INTO #Test VALUES (1)
INSERT INTO #Test VALUES (2)
INSERT INTO #Test VALUES (3)
INSERT INTO #Test VALUES (4)
INSERT INTO #Test VALUES (5)
INSERT INTO #Test VALUES (6)
INSERT INTO #Test VALUES (7)
INSERT INTO #Test VALUES (8)
INSERT INTO #Test VALUES (9)
INSERT INTO #Test VALUES (10)
SELECT * FROM #Test
--Deletion here
SET ROWCOUNT 5 --only 5 rows at time
DELETE FROM #Test
SET ROWCOUNT 0
--(5 row(s) affected)
SELECT * FROM #Test --The first five rows were deleted
--Deletion here
SET ROWCOUNT 5 --only 5 rows at time
DELETE FROM #Test
SET ROWCOUNT 0
--(5 row(s) affected)
SELECT * FROM #Test --The table is empty now
"pls" <elth-rs1p@.spamex.com> wrote in message
news:eTetGT9JGHA.2336@.TK2MSFTNGP12.phx.gbl...
> RECOVERY mode is simple.
> I can't think of any logic that could be in a WHERE clause to divide the
> table anything like evenly. I like your second approach (although I think
> you meant ...SELECT TOP 1000). But then how do you get to the second 1000?
> ++PLS
> Uri Dimant wrote:

Converting MS SQL Log file to SQL

In Sybase, there is a tool called DBTRAN where you can convert the log file to series of SQL (good for tracing problems).
Is there a similar tool for Microsoft SQL Server?
Thanks.look at http://www.lumigent.com./products/le_sql/le_sql.htm

regards,

hmscott|||Thanks a lot!