Importing Errors when using BCP

Alright, I've been getting an interesting error when using BCP between two databases on the same MSSQL Server 2000. I am bulk copying data from an older version of a database into a newer one, which includes new tables and columns. When bcp gets to a certain table, I get this error

SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

If anyone would happen to know exactly what SQL is executed by BCP or where to find such information, that would be great. None of the columns' data types have been changes in the newer version of the database.
-MikeYou migt try running profiler while using BCP but I don't think you will find much. The error seems to indicate a problem converting data.

Could you post the table structures, BCP command(s) and any BCP format files?|||DTS might give you more of a clue to where the actual error is.

I had a problem like this once that turned out to be typos in date fields that were before 1/1/1753 or whatever the magic "start date" in SQL server is (like '12/31/0999' instead of '12/31/1999').|||You should get a nice picture of the problem with the -o(filename) switch on BCP. The output file will just contain the rows that are causing the errors. I had a similar problem with "Right string truncation" errors in BCP, and the -o spotlighted the problem immediately.

Importing Data??

I have a process that calls a proc that BCP's a delimited file into a table. Well the SOX police say a header and footer must be added to the file. Needless to say this screws my BCP process.

Does anyone know how to strip a header and footer record from a text file using transact sql or have any other suggestions to strip the records?Well, -F will allow you to bypass the headers... If you can magically devine the number of rows, then -L will allow you to bypass the footers.

If that doesn't suffice, you can always use brute force! Either specify a format file and a batch size of 1 row (really ugly performance), or BCP into a staging table, then only copy the rows of interest from the staging table to the production table.

The other option that will get the job done, but might infuriate the SOX-meisters would be to use the "Flintstone" method... Write one utility to apply the requisite header and footer, and a separate utility to remove the header and footer before using BCP to import the data!

-PatP|||Yeah, well...I'm sure SOX is gonna ask you to interogate thos headers and trailers as well...

bcp the whole damn thing in to a single column table varchar(8000)

Sounds like a mainframe file, so do you have record identifiers?

Like 'H', 'D', and 'T'?|||Yeah, well...I'm sure SOX is gonna ask you to interogate thos headers and trailers as well...Oh! You're no fun!
bcp the whole damn thing in to a single column table varchar(8000)Nothing quite like brute force! 'Tain't pretty, but it does get the job done.

-PatP|||What's funny is they don't care if the header and footer are interogated. I'm lobbying to remove the footer since I can eliminate the header with -F.

My record/row delimiter is {CR}{LF}. I've decided my worse case senario is to add an extra column to the file and BCP the file into a staging table that populates the target table with all records where the new column is null. You know, populate the new column with the header and footer data and leave them blank for all other records. Still too much damn work for such a small problem.|||Let me ask you...what other tool could you possibly need in your toolbox, when GOD made this wonderful thing called a sledge hammer?

Peter,

The Header and trailer thing has always been a pain...

Add an IDENTITY column to get the last and first row...

(OK Pat, release the hounds...)

Do your intergoation and save the stats...

The use a simple bcp, with your first row = 2 and your last row = COUNT(*)

Then perform the audits...

I also like to do an INSERT with parsing to the final destination...but I like what I sketched out above better...

OK, now the discussion about how the data may not get loaded to the table in the same manner that it's in the file...

Take it away Pat...

(did I piss him off too?)|||Nah, it takes LOTS more than that to irrigate me!

I'm actually fine with that idea... The staging table was actually in my first posting, and an identity column makes processing easy. The only thing that might make it a booger to process would be variable length columns with delimiters, although SQL Server can handle that too (at some performance penalty relative to the way that BCP would handle it).

I'm for whatever works, and the less effort needed to get there, the better I like it! I've only got time for so much schtuff, and I really don't want to do any more than I have to!

-PatP

Importing data into SQL using BCP

Hello,
I have a question regarding importing data into ms sql using BCP. Can
I use BCP to load data into my sql tables, if the database I am pulling
from is at a remote location? How would I do that?
Does the database at the remote location have to be SQL, or can it be
MySQL too?
Thanks for your input.
G-
> I have a question regarding importing data into ms sql using BCP. Can
> I use BCP to load data into my sql tables, if the database I am pulling
> from is at a remote location? How would I do that?
BCP can load data from a file into a SQL Server table or copy data from a
SQL Server table to a file. The table/file can be local or remote but only
Microsoft SQL Server tables are supported. You'll need to use DTS/SSIS for
other types of databases.
Hope this helps.
Dan Guzman
SQL Server MVP
"InterActionCRMGuru" <leadtank@.gmail.com> wrote in message
news:1169084293.977520.191810@.11g2000cwr.googlegro ups.com...
> Hello,
> I have a question regarding importing data into ms sql using BCP. Can
> I use BCP to load data into my sql tables, if the database I am pulling
> from is at a remote location? How would I do that?
> Does the database at the remote location have to be SQL, or can it be
> MySQL too?
> Thanks for your input.
> G-
>
|||Dan Guzman wrote:
> BCP can load data from a file into a SQL Server table or copy data from a
> SQL Server table to a file. The table/file can be local or remote but only
> Microsoft SQL Server tables are supported. You'll need to use DTS/SSIS for
> other types of databases.
>
Thanks, Dan. One last question, Can I use BCP to copy data from a
remote SQL server table, directly to my SQL server table?
|||> Thanks, Dan. One last question, Can I use BCP to copy data from a
> remote SQL server table, directly to my SQL server table?
BCP (and BULK INSERT) requires file in/out. DTS/SSIS can copy directly from
table to table, including non-SQL Server tables.
Hope this helps.
Dan Guzman
SQL Server MVP
"InterActionCRMGuru" <leadtank@.gmail.com> wrote in message
news:1169087975.361441.131140@.38g2000cwa.googlegro ups.com...
> Dan Guzman wrote:
> Thanks, Dan. One last question, Can I use BCP to copy data from a
> remote SQL server table, directly to my SQL server table?
>

Importing data into SQL using BCP

Hello,
I have a question regarding importing data into ms sql using BCP. Can
I use BCP to load data into my sql tables, if the database I am pulling
from is at a remote location? How would I do that?
Does the database at the remote location have to be SQL, or can it be
MySQL too?
Thanks for your input.
G-> I have a question regarding importing data into ms sql using BCP. Can
> I use BCP to load data into my sql tables, if the database I am pulling
> from is at a remote location? How would I do that?
BCP can load data from a file into a SQL Server table or copy data from a
SQL Server table to a file. The table/file can be local or remote but only
Microsoft SQL Server tables are supported. You'll need to use DTS/SSIS for
other types of databases.
Hope this helps.
Dan Guzman
SQL Server MVP
"InterActionCRMGuru" <leadtank@.gmail.com> wrote in message
news:1169084293.977520.191810@.11g2000cwr.googlegroups.com...
> Hello,
> I have a question regarding importing data into ms sql using BCP. Can
> I use BCP to load data into my sql tables, if the database I am pulling
> from is at a remote location? How would I do that?
> Does the database at the remote location have to be SQL, or can it be
> mysql too?
> Thanks for your input.
> G-
>|||Dan Guzman wrote:
> BCP can load data from a file into a SQL Server table or copy data from a
> SQL Server table to a file. The table/file can be local or remote but onl
y
> Microsoft SQL Server tables are supported. You'll need to use DTS/SSIS fo
r
> other types of databases.
>
Thanks, Dan. One last question, Can I use BCP to copy data from a
remote SQL server table, directly to my SQL server table?|||> Thanks, Dan. One last question, Can I use BCP to copy data from a
> remote SQL server table, directly to my SQL server table?
BCP (and BULK INSERT) requires file in/out. DTS/SSIS can copy directly from
table to table, including non-SQL Server tables.
Hope this helps.
Dan Guzman
SQL Server MVP
"InterActionCRMGuru" <leadtank@.gmail.com> wrote in message
news:1169087975.361441.131140@.38g2000cwa.googlegroups.com...
> Dan Guzman wrote:
> Thanks, Dan. One last question, Can I use BCP to copy data from a
> remote SQL server table, directly to my SQL server table?
>

Importing data into SQL using BCP

Hello,
I have a question regarding importing data into ms sql using BCP. Can
I use BCP to load data into my sql tables, if the database I am pulling
from is at a remote location? How would I do that?
Does the database at the remote location have to be SQL, or can it be
MySQL too?
Thanks for your input.
G-> I have a question regarding importing data into ms sql using BCP. Can
> I use BCP to load data into my sql tables, if the database I am pulling
> from is at a remote location? How would I do that?
BCP can load data from a file into a SQL Server table or copy data from a
SQL Server table to a file. The table/file can be local or remote but only
Microsoft SQL Server tables are supported. You'll need to use DTS/SSIS for
other types of databases.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"InterActionCRMGuru" <leadtank@.gmail.com> wrote in message
news:1169084293.977520.191810@.11g2000cwr.googlegroups.com...
> Hello,
> I have a question regarding importing data into ms sql using BCP. Can
> I use BCP to load data into my sql tables, if the database I am pulling
> from is at a remote location? How would I do that?
> Does the database at the remote location have to be SQL, or can it be
> MySQL too?
> Thanks for your input.
> G-
>|||Dan Guzman wrote:
> > I have a question regarding importing data into ms sql using BCP. Can
> > I use BCP to load data into my sql tables, if the database I am pulling
> > from is at a remote location? How would I do that?
> BCP can load data from a file into a SQL Server table or copy data from a
> SQL Server table to a file. The table/file can be local or remote but only
> Microsoft SQL Server tables are supported. You'll need to use DTS/SSIS for
> other types of databases.
>
Thanks, Dan. One last question, Can I use BCP to copy data from a
remote SQL server table, directly to my SQL server table?|||> Thanks, Dan. One last question, Can I use BCP to copy data from a
> remote SQL server table, directly to my SQL server table?
BCP (and BULK INSERT) requires file in/out. DTS/SSIS can copy directly from
table to table, including non-SQL Server tables.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"InterActionCRMGuru" <leadtank@.gmail.com> wrote in message
news:1169087975.361441.131140@.38g2000cwa.googlegroups.com...
> Dan Guzman wrote:
>> > I have a question regarding importing data into ms sql using BCP. Can
>> > I use BCP to load data into my sql tables, if the database I am pulling
>> > from is at a remote location? How would I do that?
>> BCP can load data from a file into a SQL Server table or copy data from a
>> SQL Server table to a file. The table/file can be local or remote but
>> only
>> Microsoft SQL Server tables are supported. You'll need to use DTS/SSIS
>> for
>> other types of databases.
> Thanks, Dan. One last question, Can I use BCP to copy data from a
> remote SQL server table, directly to my SQL server table?
>

Importing csv File into SqlExpress .MDF file.

Hi,
I'm trying to use BCP to import a .csv file into a .mdf file.
At the commandline, I'm using the following command:
bcp commercedb.mdf.crmc_products in test.csv -T
I'm getting errors (below) telling me that I'm not successfully
connecting to the database.
Any help would be appreciated. If you can help me, please understand
I'm new to this so limited g speak would be most appreciated.
ERRORS:
SQLState = 08001, NativeError = 53
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not
open a con
nection to SQL Server [53].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = 53
Error = [Microsoft][SQL Native Client]An error has occurred while
establishing a
connection to the server. When connecting to SQL Server 2005, this
failure may
be caused by the fact that under the default settings SQL Server does
not allow
remote connections.Data access libraries do not directly access (MDF) SQL Server database files
the same way they can an (MDB) MS Access file. All data access must go
through the SQL Server service itself.
The .mdf database file must first be attached to SQL Server.
http://msdn2.microsoft.com/en-us/library/ms165673.aspx
Once done, you bulk copy specifying the server and database name:
http://msdn2.microsoft.com/en-us/library/ms162802.aspx
<tom.herz@.gmail.com> wrote in message
news:1138982708.766097.92140@.f14g2000cwb.googlegroups.com...
> Hi,
> I'm trying to use BCP to import a .csv file into a .mdf file.
> At the commandline, I'm using the following command:
> bcp commercedb.mdf.crmc_products in test.csv -T
> I'm getting errors (below) telling me that I'm not successfully
> connecting to the database.
> Any help would be appreciated. If you can help me, please understand
> I'm new to this so limited g speak would be most appreciated.
> ERRORS:
> SQLState = 08001, NativeError = 53
> Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not
> open a con
> nection to SQL Server [53].
> SQLState = HYT00, NativeError = 0
> Error = [Microsoft][SQL Native Client]Login timeout expired
> SQLState = 08001, NativeError = 53
> Error = [Microsoft][SQL Native Client]An error has occurred while
> establishing a
> connection to the server. When connecting to SQL Server 2005, this
> failure may
> be caused by the fact that under the default settings SQL Server does
> not allow
> remote connections.
>|||Hi,
Thanks. This is what I needed. I hadn't attached the database.
I haven't been successful yet, but that is a lack of experience with
all the switches and BCP overall.
Once I got the database attached, things started to gel.
I really appreciate your time, JT.
Have a good day.