I've been moving databases to 2005 and have always received useful error messages regarding failures using the Copy Database wizard... until the database I am working on now - below is the entire error message - not very useful huh? - any ideas on where to look for the problem? (substituted xxxx for servernames etc)
Thanks, Chris
Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date: 1/17/2007
Time: 11:13:02 AM
User: xxxxxxx
Computer: xxxxxxx
Description:
Event Name: OnError
Message: The requested objects failed to transfer.
StackTrace: at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
Operator: xxxxxxxxxxxxxxxxxx
Source Name: xxxx01_xxxxx02_Transfer Objects Task
Source ID: {0BDAA262-18CB-4BC8-928C-607680E0B634}
Execution ID: {83CB3AB4-3AD7-4AFE-BE5D-9EBA89434874}
Start Time: 1/17/2007 11:13:02 AM
End Time: 1/17/2007 11:13:02 AM
Data Code: 0
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Hi Chris,
This error basically says that an error occurred during the transfer of this database from server A to server B, which indeed is not very helpful.
However, Copy Database Wizard has been widely improved and you should try to use the current CTP version available:
http://www.microsoft.com/sql/ctp.mspx
There is a good chance the problem you are facing had already been fixed in this CTP.
Thanks,
Oscar.
Thanks Oscar - Do I only need to install the CTP on my dev box / workstation? Or do I need to install it on the destination server? (which is where the wizard puts the package and executes it)
If I install the CTP on my workstation will SSIS packages I create / edit etc be able to be opened / edited on non SP2 CTP workstations?
(just trying to see if installing SP2 CTP is something I can do.. I can't on any servers that I am working with but might be able to on my workstation)
|||
Hi Chris,
Regarding the Copy Database Wizard, you should always install the latest version on the target machine, precisely for the reason you just described (i.e. the target machine is where the wizard puts the package and executes it). You could then launch the wizard from here and select the source database on the corresponding machine. This is assuming that it is ok for you to upgrade to this version.
Changing the version on the client side won't help much, because most of the logic is executed at the destination.
Hope this helps!
Cheers,
Oscar.
OK now that I have installed SP2 CTP... Database Copy Wizard packages are failing that succeeded yesterday (so its gotten worse).
Errors are similar in all failures - error about data not accessible and then a little further down it will say the AnsiPaddingStatus is not available. I am running this under the SQL Agent account (which is dbo). These databases are going from a 2000 server to a 2005 server. (If I run 2005 to 2005 as a test I do not receive the error - only when using a 2000 box as the source)
Message: The Table '[dbo].[xxxxxxxxxx]' cannot be scripted as its data is not accessible.
...
InnerException-->Property AnsiPaddingStatus is not available for Column '[xxxxxxx]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.
|||
Hi Chris,
Could you please tell me what are the main options you are selecting from the wizard? For example, I believe you are using the SMO Transfer method instead of Detach/Attach, right? Are you selecting any extra objects apart from the whole database as part of the copy? At the end of the CDW wizard and right before you perform the copy, there is summary page. This has useful information that we could look at as well. Please, make sure you are selecting the "Save Transfer Logs" option before clicking "Finish". This will generate a TransferDump*.sql file for each object being copied (e.g. if you are copying one database with one extra object, then there will be two such files). They will be located under:
C:\Documents and Settings\onaim\Local Settings\Application Data\Microsoft\SQL Server\Smo
for that particular user, assuming SQL Server is installed on the "C" drive in this example.
You could also select "Write to a log file" versus to the Windows event viewer.
If you could send me these two files, I would have more information to be able to help you.
Thanks,
Oscar.
I also forgot to mention that you do need to have sysadmin rights on the destination, otherwise you will not be able to successfully run CDW.
Cheers,
Oscar.
Hello Oscar
I have a problem with Copy Database Wizard to.
Have agent account as sysadmin in both source and target database
Target server has CTP Dec 2006 installed. Bat I can’t install it to the source “production” system
Managed to transfer five databases fixing the error found at the error log file
At the sixth database I have an error that I can not understand
The error appears when trying to create a view at the target system.
errorCode=-1073548784 description=Executing the query "create view [dbo].[VShift_Cost]
as
select sf.ShiftID, SFD.UnitID, sum(sfd.charge) as Shift_Charge
from shiftcostdetail sfd inner join shiftcost sf on sfd.shiftcostid = sf.shiftcostid
group by sf.shiftid, SFD.unitID
" failed with the following error: "Invalid object name 'shiftcostdetail'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The shiftcostdetail is an existing table and the view works fine at the source system.
Can you help?
Thanks
|||
Hi Panzin,
I already responded to your message in this other thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=169786&SiteID=1
Thanks,
Oscar.
Yes, SMO (do not have the problem with the detach/attach - it works fine). No extra objects, I remove the logins option. Yes I was using the "Save Transfer Logs" option.
I've looked at those SQL files and saw nothing out of the ordinary in them (actually I do one db at a time becuase there is no way to specify order of dbs in the wizard (hint would be great new feature :) so its only 1 file and it runs on the destination)
The log you mentioned says same thing... hopefully this helps:
Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date: 1/17/2007
Time: 4:43:39 PM
User: xxxxxxxxxxx
Computer: RSSQLK01
Description:
Event Name: OnError
Message: The Table '[dbo].[tblxxxxxxxxxxxx]' cannot be scripted as its data is not accessible.
StackTrace: at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.Smo.Transfer.Microsoft.SqlServer.Management.Common.ITransferMetadataProvider.SaveMetadata()
at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.Configure(ITransferMetadataProvider metadataProvider)
at Microsoft.SqlServer.Management.Smo.Transfer.GetTransferProvider()
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
InnerException-->Property AnsiPaddingStatus is not available for Column '[vcSymbol]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.
StackTrace: at Microsoft.SqlServer.Management.Smo.PropertyCollection.HandleNullValue(Int32 index)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetPropValueOptional(String propName)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetPropValueOptional[T](String propName)
at Microsoft.SqlServer.Management.Smo.Table.GetColumnPadding(Column c)
at Microsoft.SqlServer.Management.Smo.Table.GetTableAnsiPadded()
at Microsoft.SqlServer.Management.Smo.Table.ScriptCreate(StringCollection queries, ScriptingOptions so)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithListWorker(DependencyCollection depList, SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects)
Operator: xxxxxxxxxxxxxxxxxxx
Source Name: xxxxxxxxxxxxxxxxxxxxxxxx
Source ID: {3DE71A4C-00D6-4C2F-B188-EE680B19AFD9}
Execution ID: {EBD7814A-6828-4041-B08D-E8CA73CD75AF}
Start Time: 1/17/2007 4:43:39 PM
End Time: 1/17/2007 4:43:39 PM
Data Code: 0
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Just to make sure this is clear - if I detach + attach method onto the 2005 box its fine, if I then take that and use SMO to another box its fine as well... its only when using SMO directly from 2000 to 2005.
Sorry I can't be more helpful - due to the SMO issues I was more than a day behind on the project so I ended up doing the project using the detach method even though that was not desired (original request called for the source to not go offline - but we comprimised by running only in off hours). Oh and I have full dbo rights on the source and destination.
|||Hi Chris,
Thank you for the information. Let me investigate the issue based on the information that you have sent me so far and I will get back to you as soon as possible. In the meantime, it would be great if you could something for me. Could you please go to that particular table in SQL 2000 and script it to a query window and send me the output?
Thanks,
Oscar.
CREATE TABLE [tblTradeCurrencyHedgeImport] (
[iTradeCurrencyHedgeImportID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[iCurrencyHedgeImportID] [int] NOT NULL ,
[dtDate] [smalldatetime] NULL ,
[vcSymbol] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[iSettlementDate] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vcBuySell] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[iAccountID] [int] NULL ,
[iHedge] [int] NULL ,
[dTradePrice] [decimal](19, 8) NULL ,
[dFxAllInRate] [decimal](19, 8) NULL ,
[iNumberOfContractsPerBreak] [decimal](13, 2) NULL ,
[iContractSize] [int] NULL ,
[dMinimumTick] [decimal](19, 8) NULL ,
[dClosePrice] [decimal](19, 8) NULL ,
[dPricePercentDiff] [decimal](19, 8) NULL ,
[dtSettlementDate] [datetime] NULL ,
[dtInsertTime] [datetime] NULL CONSTRAINT [DF__tblTradeC__dtIns__56757D0D] DEFAULT (getdate()),
[vcInsertUser] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__tblTradeC__vcIns__5769A146] DEFAULT (suser_sname()),
[dtUpdateTime] [datetime] NULL CONSTRAINT [DF__tblTradeC__dtUpd__585DC57F] DEFAULT (getdate()),
[vcUpdateUser] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__tblTradeC__vcUpd__5951E9B8] DEFAULT (suser_sname()),
CONSTRAINT [PK_tblTradeCurrencyHedgeImport] PRIMARY KEY CLUSTERED
(
[iTradeCurrencyHedgeImportID]
) WITH FILLFACTOR = 80 ON [PRIMARY]
) ON [PRIMARY]
GO
FYI this is scripted using 2000... it will not script the table in 2005 client tools - same error about not being able to determine ansi padding
Hi Chris,
This is exactly what I was thinking. However, I haven't been able to reproduce it here at the lab and it would be great if you could help us by filing a bug using Microsoft Connect, including the steps that you had to follow in order to reproduce the problem. The URL for Microsoft Connect is:
http://connect.microsoft.com
I will make sure that this bug gets triaged as soon as possible.
Thanks again for your time and please let me know if I can be of any further assistance. We are here to help!
Cheers,
Oscar.
The following links are also useful:
http://connect.microsoft.com/SQLServer/Feedback/
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=840523&SiteID=1
Cheers,
Oscar.
No comments:
Post a Comment