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 ;)

BCP Unexpected EOF

BCP out works dandy. BCP in fails on unexpected EOF encountered. The BCP
data has ntext columns that contain XML code. Any ideas?
Hi Jeffrey,
Try using -n -N -k -E -o parameters for bcp.
Example:
bcp %dbname%..<tablename> in Yourfiledata.dat -U%dbuid% -P%dbpwd% -S%dbserv%
-n -N -k -E -o <tablename>.out
I hope this helps.
Thanks,
bdee
"Jeffrey K. Ericson" wrote:

> BCP out works dandy. BCP in fails on unexpected EOF encountered. The BCP
> data has ntext columns that contain XML code. Any ideas?
|||-n did the trrick, thanks.
"bpdee" wrote:
[vbcol=seagreen]
> Hi Jeffrey,
> Try using -n -N -k -E -o parameters for bcp.
> Example:
> bcp %dbname%..<tablename> in Yourfiledata.dat -U%dbuid% -P%dbpwd% -S%dbserv%
> -n -N -k -E -o <tablename>.out
> I hope this helps.
> Thanks,
> bdee
> "Jeffrey K. Ericson" wrote:
|||You're welcome and I'm glad that I was able to help you.
"Jeffrey K. Ericson" wrote:
[vbcol=seagreen]
> -n did the trrick, thanks.
> "bpdee" wrote:

BCP Unexpected EOF

BCP out works dandy. BCP in fails on unexpected EOF encountered. The BCP
data has ntext columns that contain XML code. Any ideas?Hi Jeffrey,
Try using -n -N -k -E -o parameters for bcp.
Example:
bcp %dbname%..<tablename> in Yourfiledata.dat -U%dbuid% -P%dbpwd% -S%dbserv%
-n -N -k -E -o <tablename>.out
I hope this helps.
Thanks,
bdee
"Jeffrey K. Ericson" wrote:

> BCP out works dandy. BCP in fails on unexpected EOF encountered. The BCP
> data has ntext columns that contain XML code. Any ideas?|||-n did the trrick, thanks.
"bpdee" wrote:
[vbcol=seagreen]
> Hi Jeffrey,
> Try using -n -N -k -E -o parameters for bcp.
> Example:
> bcp %dbname%..<tablename> in Yourfiledata.dat -U%dbuid% -P%dbpwd% -S%dbser
v%
> -n -N -k -E -o <tablename>.out
> I hope this helps.
> Thanks,
> bdee
> "Jeffrey K. Ericson" wrote:
>|||You're welcome and I'm glad that I was able to help you.
"Jeffrey K. Ericson" wrote:
[vbcol=seagreen]
> -n did the trrick, thanks.
> "bpdee" wrote:
>

BCP Unexpected EOF

BCP out works dandy. BCP in fails on unexpected EOF encountered. The BCP
data has ntext columns that contain XML code. Any ideas?Hi Jeffrey,
Try using -n -N -k -E -o parameters for bcp.
Example:
bcp %dbname%..<tablename> in Yourfiledata.dat -U%dbuid% -P%dbpwd% -S%dbserv%
-n -N -k -E -o <tablename>.out
I hope this helps.
Thanks,
bdee
"Jeffrey K. Ericson" wrote:
> BCP out works dandy. BCP in fails on unexpected EOF encountered. The BCP
> data has ntext columns that contain XML code. Any ideas?|||-n did the trrick, thanks.
"bpdee" wrote:
> Hi Jeffrey,
> Try using -n -N -k -E -o parameters for bcp.
> Example:
> bcp %dbname%..<tablename> in Yourfiledata.dat -U%dbuid% -P%dbpwd% -S%dbserv%
> -n -N -k -E -o <tablename>.out
> I hope this helps.
> Thanks,
> bdee
> "Jeffrey K. Ericson" wrote:
> > BCP out works dandy. BCP in fails on unexpected EOF encountered. The BCP
> > data has ntext columns that contain XML code. Any ideas?|||You're welcome and I'm glad that I was able to help you.
"Jeffrey K. Ericson" wrote:
> -n did the trrick, thanks.
> "bpdee" wrote:
> > Hi Jeffrey,
> >
> > Try using -n -N -k -E -o parameters for bcp.
> >
> > Example:
> > bcp %dbname%..<tablename> in Yourfiledata.dat -U%dbuid% -P%dbpwd% -S%dbserv%
> > -n -N -k -E -o <tablename>.out
> >
> > I hope this helps.
> >
> > Thanks,
> > bdee
> >
> > "Jeffrey K. Ericson" wrote:
> >
> > > BCP out works dandy. BCP in fails on unexpected EOF encountered. The BCP
> > > data has ntext columns that contain XML code. Any ideas?

BCP IN with text/ntext column fails! Is this is a bug?

Hi,

We are in process to test SQL Server 2005 migration; everything seems to work very well,
except one job that transfer data between two databases using BCP tool.
In fact the BCP fail only for one table that have ntext column, If I use the table directly in BCP OUT/IN command it works fine, but if I use a View it fail (I'm using view because the ORDER of columns in target database could be different than source database).

This is the error I receive:
SQLState = S1000, NativeError = 606
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Metadata inconsistency. Filegroup id 0 specified for table '' does not exist. Run DBCC CHECKDB or CHECKCATALOG.

Note : If I change the ntext column to nvarchar(max) it works very well

Is this is a known bug ?
Do I have to change my ntext/text columns to nvarchar(max)/varchar(max) types ?

Any advice is welcome
Thank you.

I have repro-ed the proble. However I have a workaround.

Try BCP IN the data from the BCP file into a table with the same column order as the view, and it will work.

Now create a view on this table. This will resolve your problem.

|||

We have the same problem. The problem with the workaround is that it is very inefficient. If you wanted to do this same thing on tables that have millions of rows, it means you now have to create a table, load the new table and then transfer the data from this new table to the one you want, and delete the new table, instead of using a view like you could in SQL 2000, SQL 7.5 and SQL 6.5. Not only is that a lot slower, but it consumes a lot more disk space (assuming your tables with Text or image data contain lots of data)

It seems like an obvious bug to me. The documentation for BCP has always allowed you to specify a view instead of a table. I can't image why anyone would design this to not work for image or text data in SQL 2005.

BCP IN with text/ntext column fails! Is this is a bug?

Hi,

We are in process to test SQL Server 2005 migration; everything seems to work very well,
except one job that transfer data between two databases using BCP tool.
In fact the BCP fail only for one table that have ntext column, If I use the table directly in BCP OUT/IN command it works fine, but if I use a View it fail (I'm using view because the ORDER of columns in target database could be different than source database).

This is the error I receive:
SQLState = S1000, NativeError = 606
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Metadata inconsistency. Filegroup id 0 specified for table '' does not exist. Run DBCC CHECKDB or CHECKCATALOG.

Note : If I change the ntext column to nvarchar(max) it works very well

Is this is a known bug ?
Do I have to change my ntext/text columns to nvarchar(max)/varchar(max) types ?

Any advice is welcome
Thank you.

I have repro-ed the proble. However I have a workaround.

Try BCP IN the data from the BCP file into a table with the same column order as the view, and it will work.

Now create a view on this table. This will resolve your problem.

|||

We have the same problem. The problem with the workaround is that it is very inefficient. If you wanted to do this same thing on tables that have millions of rows, it means you now have to create a table, load the new table and then transfer the data from this new table to the one you want, and delete the new table, instead of using a view like you could in SQL 2000, SQL 7.5 and SQL 6.5. Not only is that a lot slower, but it consumes a lot more disk space (assuming your tables with Text or image data contain lots of data)

It seems like an obvious bug to me. The documentation for BCP has always allowed you to specify a view instead of a table. I can't image why anyone would design this to not work for image or text data in SQL 2005.

BCP IN with text/ntext column fails! Is this is a bug?

Hi,

We are in process to test SQL Server 2005 migration; everything seems to work very well,
except one job that transfer data between two databases using BCP tool.
In fact the BCP fail only for one table that have ntext column, If I use the table directly in BCP OUT/IN command it works fine, but if I use a View it fail (I'm using view because the ORDER of columns in target database could be different than source database).

This is the error I receive:
SQLState = S1000, NativeError = 606
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Metadata inconsistency. Filegroup id 0 specified for table '' does not exist. Run DBCC CHECKDB or CHECKCATALOG.

Note : If I change the ntext column to nvarchar(max) it works very well

Is this is a known bug ?
Do I have to change my ntext/text columns to nvarchar(max)/varchar(max) types ?

Any advice is welcome
Thank you.

I have repro-ed the proble. However I have a workaround.

Try BCP IN the data from the BCP file into a table with the same column order as the view, and it will work.

Now create a view on this table. This will resolve your problem.

|||

We have the same problem. The problem with the workaround is that it is very inefficient. If you wanted to do this same thing on tables that have millions of rows, it means you now have to create a table, load the new table and then transfer the data from this new table to the one you want, and delete the new table, instead of using a view like you could in SQL 2000, SQL 7.5 and SQL 6.5. Not only is that a lot slower, but it consumes a lot more disk space (assuming your tables with Text or image data contain lots of data)

It seems like an obvious bug to me. The documentation for BCP has always allowed you to specify a view instead of a table. I can't image why anyone would design this to not work for image or text data in SQL 2005.

BCP Error with Key Field Name

Hi I have a table with the following structure

ID Int,
Key nVarchar2(50),
Text nText,
upsizeTS TimeStamp

I want to use BCP to import some data,

it always gives me the following error:
SQLSTATE = 37000, native error 156
Incorrect syntax near the keyword 'Key'

There is no way I can change the name of the field to something else.

Could anybody tell me why am I getting this error ?
Thank you in advance.You have chosen two column names, which are also reserved words: key and text. You will have to put [] around any occurence of these two column names in your script, e.g. [key] instead of key, and [text] instead of text.|||How do I do that, what script are you talking about. I am using a BCP command.

Originally posted by DoktorBlue
You have chosen two column names, which are also reserved words: key and text. You will have to put [] around any occurence of these two column names in your script, e.g. [key] instead of key, and [text] instead of text.|||Post your BCP command.

bcp data to a txt file

I am trying to export data to a text file using bcp. The table that I am
trying to export has an ntext column. I keep getting this error, and
have not been able to find a way to get around it. How do I specify to
another provider, or get it around it anyway? Thanks...

C:\Program Files\Microsoft SQL Server>bcp northwind.dbo.categories out
northwind
_categories.txt -n -T -Usa -PPassword-2003
Msg 4004, Level 16, State 1:
Server 'CORPLGROULTI1', Line 1:
Unicode data in a Unicode-only collation or ntext data cannot be sent
to clients using DB-Library (such as ISQL) or ODBC version 3.7 or
earlier.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it![posted and mailed, please reply in news]

tim groulx (timgru@.verizon.net) writes:
> I am trying to export data to a text file using bcp. The table that I am
> trying to export has an ntext column. I keep getting this error, and
> have not been able to find a way to get around it. How do I specify to
> another provider, or get it around it anyway? Thanks...
>
> C:\Program Files\Microsoft SQL Server>bcp northwind.dbo.categories out
> northwind
> _categories.txt -n -T -Usa -PPassword-2003
> Msg 4004, Level 16, State 1:
> Server 'CORPLGROULTI1', Line 1:
> Unicode data in a Unicode-only collation or ntext data cannot be sent
> to clients using DB-Library (such as ISQL) or ODBC version 3.7 or
> earlier.

Apparently you are not using a version of BCP that comes with the version
of SQL Server you are connecting to. Or you are, but for some reason
the ODBC drivers on the machine have been replaced by older ones.

The most likely reason, though, is that you are running BCP from a
machine that has the 6.5 tools installed, or you are using BCP from a
Sybase installation.

Use bcp -v to find out what you are using.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

bcp and ntext SQL 2K

Is it possible to export ntext data to a text file using bcp and preserve all of the text?

Thanks from first time bcp-er.

Yes, bcp will retain your text, at least in 7.0 and 2k. I haven't tried it in 2k5 yet.

|||

Thanks Anget,

What do I do to retain the ntext formatting? If I accept the defaults in the bcp.fmt file, I lose some of the data.

Best Regards,

Elaine

|||Try to use parameter -N to tell bcp to bulk export "Native Data"
ex.

Code Snippet

bcp myTestUniNativeData out C:\myTestUniNativeData-N.Dat -N

|||

Thanks Alex, that gets the whole field as it appears in the original

Thanks Angel, too for your help.

Best Regards,

Elaine