BCP with image, ntext datatype

Hi, I have a table with ntext and image datatype. I did BCP out succesfully but when I try to to BCP in, I am getting error.
----
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
------
I read somewhere that I can use BULK INSERT with a format file.
Can someone suggest how to BCP in siccesfully or How does the format file looks like for this kind of task?. I am using SQL Server 2000.
---
Here is my table structure..
----
CREATE TABLE [dbo].[NOTIFY_TEMPLATE] (
[ID] [numeric](28, 0) NOT NULL ,
[SENDER] [varchar] (128) NOT NULL ,
[SUBJECT] [varchar] (512) NOT NULL ,
[BODY] [ntext] NOT NULL ,
[PRIORITY] [numeric](28, 0) NULL DEFAULT (2),
[ENABLED] [numeric](28, 0) NULL DEFAULT (1),
[LANGID] [numeric](28, 0) NOT NULL DEFAULT (0),
[NOTIFY_TYPE] [numeric](28, 0) NULL DEFAULT (0),
[REQUEST_TYPE] [numeric](28, 0) NULL ,
[CUSTOMIZED] [numeric](28, 0) NOT NULL DEFAULT (0)
) ON [DATA1] TEXTIMAGE_ON [DATA1]in order to perform a successful bcp in/bulk insert (same thing, essentially) you need to have a staging table which will hold only 1 record that'll contain the file you're bcping/bulk inserting. the format file will have to have a field width that will correspond to the file size in bytes.|||Hi, Thanks for reply..
Here is my situation..
With a fmt file, I am able to load the data succesfully, but, the data is going as garbage and some random numbers...
My format file looks like this..Body column has ntext datatype.
I gave the table DDL below..
----
8.0
10
1 SQLNUMERIC 0 13 "" 1 ID ""
2 SQLCHAR 0 128 "" 2 SENDER SQL_Latin1_General_CP1_CS_AS
3 SQLCHAR 0 512 "" 3 SUBJECT SQL_Latin1_General_CP1_CS_AS
4 SQLNTEXT 0 40000 "" 4 BODY SQL_Latin1_General_CP1_CI_AS
5 SQLNUMERIC 0 13 "" 5 PRIORITY ""
6 SQLNUMERIC 0 13 "" 6 ENABLED ""
7 SQLNUMERIC 0 13 "" 7 LANGID ""
8 SQLNUMERIC 0 13 "" 8 NOTIFY_TYPE ""
9 SQLNUMERIC 0 13 "" 9 REQUEST_TYPE ""
10 SQLNUMERIC 0 13 "" 10 CUSTOMIZED ""|||sqlntext column should have the following syntax:

4 SQLNTEXT 0 <file_size_in_bytes> "" 4 BODY SQL_Latin1_General_CP1_CI_AS|||Hi, Thank you for the reply.
How do I get the <file_size_in_bytes> parameter?
Because, there are multiple records in the DB, I gave an approximate size of 40000. But, still it fails..
Also, I see wrong data in almost every column.|||see my previous post about the staging table and 1 document per process|||The problem that I see is that the NTEXT column doesn't have well defined delimiters. Because of that, there isn't any clear way to find the end of it, which implies that there isn't a clear way to find the beginning of the "priority" column after the NTEXT column, other than to know the exact length of the NTEXT column.

My first suggestion would be to limit the data file to just one row and move the NTEXT column to the end of the row. Those two (?) changes would allow the NTEXT column to be treated as though it had a variable length (which would be set by the end-of-file condition). The only other way that I know to import this file using BCP would be to know the exact length (to the byte) of the NTEXT column, and specify that length in the FMT file.

-PatP|||Here is my situation.
----
I already have the data in SQL Server 2000 DB.
I want to export that data using BCP or DTS into flat file or files.
And import the data back into an empty schema using BCP or DTS, any commandline utility...
So, that I can create the empty schema first, import the data, then do some post processing, everything need to be run with commandline...
That is the reason I am exploring BCP, I read somewhere that we can use DTS also for this purpose. Can you elaborate more on this if you have any idea or experience in this..
Thanks a lot|||In a PM, Kishore said:Hi Pat, I see your reply to my message BCP...
This table has about 270 records. How would I know the length of one particular record of that column. That's the problem, it is UGLY to have to do this.

A short and sweet way to create an "offline" copy of a database is to do a SQL BACKUP (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp) followed by a SQL RESTORE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_25rm.asp) command. Both of these can be scripted using the OSQL (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_osql_1wxl.asp) utility.

If that won't work for you, you can create a DTS package using either the DTS Wizard (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_tools_wiz_9e5g.asp) or the DTS Designer (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_tools_wiz_9e5g.asp). Once you save the DTS package, it can be run via the SQL Agent (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_cs_6x0l.asp) or DTSRun (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_tools_other_8reb.asp).

-PatP|||Hi, Thanks for the reply...
I am trying to create a package, when executed should be able to create the DB on any machine...
Loading data is a part of this package run.
Here is my question...
Can I use DTS to create a package to export all tables in flat file format...
Use the cmd line utility to run that package pretty much on any machine to import that data back.
When I selected the DTS export utility to create a package, it is allowing me to select one table at a time. If it is the case, I may have to create 300 packages..:-(|||Originally posted by Pat Phelan
The problem that I see is that the NTEXT column doesn't have well defined delimiters. Because of that, there isn't any clear way to find the end of it, which implies that there isn't a clear way to find the beginning of the "priority" column after the NTEXT column, other than to know the exact length of the NTEXT column.

My first suggestion would be to limit the data file to just one row and move the NTEXT column to the end of the row. Those two (?) changes would allow the NTEXT column to be treated as though it had a variable length (which would be set by the end-of-file condition). The only other way that I know to import this file using BCP would be to know the exact length (to the byte) of the NTEXT column, and specify that length in the FMT file.

-PatP
it all sounds very scientific, but i already posted that answer. also, end-of-file is an end-of-file is an end-of-file. and you need to bcp in specifying only 1(one, uno, ein, odin, etc.) field to be imported into a staging table, knowing exactly what the size of the file is, in bytes (xp_getfiledetails 'full_path_and_full_file_name')
once you know the size, you need to write your format file for bcp operation into a...staging table... i use sp_oaxxx procedures to do that. once the format file is ready, i use bulk insert using the format file into the ...staging table... once bulk insert is completed, i writetext from staging table into the actual table using all other fields i need.|||Hi, I used -n or -N switch with bcp and it solved my problem..
Thats guys for your input. it was helpful and more knowledgeful.
-Kingno1|||Originally posted by kingno1
Hi, Thanks for the reply...
I am trying to create a package, when executed should be able to create the DB on any machine...
Loading data is a part of this package run.
Here is my question...
Can I use DTS to create a package to export all tables in flat file format...
Use the cmd line utility to run that package pretty much on any machine to import that data back.
When I selected the DTS export utility to create a package, it is allowing me to select one table at a time. If it is the case, I may have to create 300 packages..:-(

i don't think dts or bcp are your answers. if you have such datatypes as n/text and image you will be able to save those tables in files, but you'll never be able to import them back unless the size of the field of n/text or image datatype is exactly the same for all rows, and if you do not run into a situation where the contents of the field contain the sequence of field or row terminator.

backup and restore is your only viable solution, but you can make it pretier than just that by ensuring that all users from the restored image have corresponding logins on the target server, and rectify the sid mismatches via sp_change_users_login. i'd recommend to take the guts out of that procedure and do it yourself, because you will not be able to run it within a script against multiple mismatched users, unless you take it outside with xp_cmdshell 'osql ... -Q"exec master.dbo.sp_change_users_login ..." -n'|||Originally posted by kingno1
Hi, I used -n or -N switch with bcp and it solved my problem..
Thats guys for your input. it was helpful and more knowledgeful.
-Kingno1

hey, good for you ;)