error 0x80004005 when copying tables between servers

hi
I've got a job which copy tables between different servers .
I am feeding the tables one by one and the process of copying is in a loop so I have cotrol over the copying process.
it works fine but sometimes randomly I am getting

Execution failed with the following error: "ERROR : errorCode=-1071636471 description=SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".

and the process fails and this might happen for in any point and on any table and sometimes it runs all the way successfully!!
any idea what the problem might be!
ThanksDo the tables have exactly the same structure? If not then I'd suggest it is as indicated a simple conversion error due to a type mismatch.|||Yes they are exactly the same is if it isn't it shouldn't run successfully sometimes!|||

Kolf wrote:

Yes they are exactly the same is if it isn't it shouldn't run successfully sometimes!

Unless they aren't the same, but normally the data can be explicitly cast to the destination type. Perhaps, though, that sometimes there is data in the source that cannot be cast.

How do you copy the tables?|||using the SSIS Object trasfer task object, and also I have to mention it is from sql 2000 to 2005
thanks|||

Kolf wrote:

using the SSIS Object trasfer task object, and also I have to mention it is from sql 2000 to 2005
thanks

Can you by chance post the create table statement for your table in SQL Server 2000?

ERROR : Host-file columns may be skipped only when copying into the Server

Hi All,

I need to make a query from the SQL Server 2K and save this data as an XML file. What i am trying to do is to execute a bcp utility as follows:

EXEC master..xp_cmdshell 'bcp "SELECT CustID,CustName,CustSurname,CustEmail FROM myOwenDB..T_Customers FOR XML RAW" queryout "c:\customers.xml" -fc:\bcp.fmt -Sservername -Usa -Ppwd -C RAW -r -t'

with bcp.fmt file formatted as

8.0
4
1 SQLCHAR 0 9 "\t" 1 CustID Turkish_CI_AS
2 SQLCHAR 0 100 "\t" 2 CustName Turkish_CI_AS
3 SQLCHAR 0 100 "\t" 3 CustSurname Turkish_CI_AS
4 SQLCHAR 0 100 "\t" 4 CustEmail Turkish_CI_AS

The error is as follows:

Error = [Microsoft][ODBC SQL Server Driver]Host-file columns may be skipped only when copying into the Server

Does anyone have an idea about the problem? Also other techniques to generate XML file are also welcomed:)

Thanks in advance,

Bahtiyar KARANLIKYour select statment only returns one column and you are using a format file that says bcp will be receiving four columns from the select statment.|||Hi,
How can my SQL statement can return only one column? When i execute the statement via Query Analyzer it displays the correct information?

Am i missing a point??

Bahtiyar KARANLIK|||If you execute
SELECT CustID,CustName,CustSurname,CustEmail FROM myOwenDB..T_Customers
In QA you will get four columns, CustID, CustName,CustSurname and CustEmail.

If you execute
SELECT CustID,CustName,CustSurname,CustEmail FROM myOwenDB..T_Customers FOR XML RAW
In QA you will have a resultset with one column. Within the one column you will have an XML string that contains the four above mentioned columns, but they will be contained in one column!

If you execute
select au_id,au_lname,phone,contract from pubs.dbo.authors

you should get something like:

au_id au_lname phone contract
---- ------------ ---- ---
172-32-1176 White 408 496-7223 1

If you execute
select au_id,au_lname,phone,contract from pubsdbo.authors for xml raw

you should get something like:

XML_F52E2B61-18A1-11d1-B105-00805F49916B
------------------------
<row au_id="172-32-1176" au_lname="White" phone="408 496-7223" contract="1"/>

The first has four colums in the result set the second has only one.