Having problems exporting sql table using BCP utility

IN SQL 2005 tables can have a different collation to the server.  Are you sure all the tables have the same colation order as the server?

interesting, I found that same description, but for sql 2000:
http://support.microsoft.com/kb/939537

now, if this IS sql 2005, do you have SP2 or higher applied? if not, do so, and try again.

Its SQL 2005 64-bit. Not sure what SP we are on. How can I find out?

All the tables are supposed to have thesame collation. I did notice that the integer fields had null for a collation value.

How to identify your SQL Server version and edition:
http://support.microsoft.com/kb/321185 

64bit sp2 standard edition

well, did you try the workarounds suggested in the sql 2000 link, by any chance?
do you run the bcp from the server itself? can you double-check that you run the sql 2005 bcp version, and not by chance, a sql 2000 bcp version (with a PATH set to include a old sql 2000 folder before the sql 2005 folder?)
I had that once, where a sybase BCP was called instead of the sql 2000 due to the path setting, and me trying to find out where the heck the error message would come from for 2 hours ...

I am running from the query analyzer on a VM SQL 2005 server - 2000 has never been installed. I have not tried the 2000 work -arounfd. Will do so now.

I didn't see a work around in that link. It looked liek a list of post sp2 hot fixes.

sorry, my fault. I had pasted the wrong kb. here we go:
support.microsoft.com/kb/867677

Here is what i received:
Warning: -w overrides -c.
NULL
Starting copy...
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to resolve column level collations
SQLState = 01000, NativeError = 5701
Warning = [Microsoft][SQL Native Client][SQL Server]Changed database context to 'UPIC'.
NULL
BCP copy out failed
NULL

sorry, but I am running short on ideas, now.
I would suggest to contact ms support (if you have a maintenance contract...)

Thanks.

Do you have and PRINT commands in you script? If so, remove them and try agan.

Can you post the CREATE TABLE scripts of the source and destination tables for me to look at. Thanks

Here is the script:

declare  @dbName varchar(100)
declare  @sql varchar(5000)      
declare @fullFileName varchar(100)

set @dbname = 'dbname'
set @sql = 'select * from dbo.demographics'
set @fullfilename = 'c:\test.xls'

if @sql = '' or @fullFileName = ''
begin
    select 0 as ReturnValue -- failure
    return
end
-- if DB isn't passed in set it to master
select    @dbName = 'use ' + @dbName + ';'
if object_id('##TempExportData') is not null
    drop table ##TempExportData
if object_id('##TempExportData2') is not null
    drop table ##TempExportData2
-- insert data into a global temp table
declare @columnNames varchar(8000), @columnConvert varchar(8000), @tempSQL varchar(8000)
select    @tempSQL = left(@sql, charindex('from', @sql)-1) + ' into ##TempExportData ' +
     substring(@sql, charindex('from', @sql)-1, len(@sql))
exec(@dbName + @tempSQL)
 
if @@error > 0
begin
    select 0 as ReturnValue -- failure
    return
end
-- build 2 lists
-- 1. column names
-- 2. columns converted to nvarchar
SELECT    @columnNames = COALESCE( @columnNames  + ',', '') + column_name,
        @columnConvert = COALESCE( @columnConvert  + ',', '') + 'convert(nvarchar(4000) ,'
        + column_name + case when data_type in ('datetime', 'smalldatetime') then ',121'
                             when data_type in ('numeric', 'decimal') then ',128'
                             when data_type in ('float', 'real', 'money', 'smallmoney') then ',2'
                             when data_type in ('datetime', 'smalldatetime') then ',120'
                             else ''
                        end + ') as ' + column_name
FROM    tempdb.INFORMATION_SCHEMA.Columns
WHERE    table_name = '##TempExportData'
 -- execute select query to insert data and column names into new temp table
SELECT    @sql = 'select ' + @columnNames + ' into ##TempExportData2 from (select ' + @columnConvert + ', ''2'' as [temp##SortID]        from ##TempExportData union all select ''' + replace(@columnNames, ',', ''', ''') + ''', ''1'') t order by [temp##SortID]'

exec (@sql)
-- build full BCP query


select    @sql = 'bcp "' + @dbName + ' select * from ##TempExportData2" queryout "' + @fullFileName + '" -c -T -CRAW'
-- execute BCP

Exec master..xp_cmdshell @sql
if @@error > 0
begin
    select 0 as ReturnValue -- failure
    return
end
drop table ##TempExportData
drop table ##TempExportData2
select 1 as ReturnValue -- success
go

Thanks

A couple of things I would try:

1. Did you try to do the non bcp stuff in a query window in Management Studio first to see if it gives any errors?  If not, I suggest you try, it may gve you a clue.
2. Although there are several reasons bugsrelated to this error message, this error message does still actually mean a problem in the collations also.  When you create the temp tables they will take on the default server collation, which may be different from the collation of one of the columns.  Can I suggest you make a new table with the default serv collation, with just a few columns, then run your script against it, see if you get the same messages.

Thanks - will try that. Is the current default ok?

Your default is the same as I always use. Unless you are doing something specific, I don't see a need to change from the default.

ok

Tried this same script on sql 2000 and had no problems. I  am running this on 64 bit sql 2005 on a 64 bit wondows 2003. is it a 64 bit problem?

They have not fixed the 64kb version yet. I am going back to the 32 bit version. Thanks all.