bcp.exe installation

(This was originally in the sqlserver.programming group but I moved it to a
more appropriate place)
I'm using the bcp utility to create a text file from a view. The text file
is used by a 3rd party program for other processing.
bcp installs on the server machine with sql 2k (or in my case MSDE) just
fine but of course it's not automatically on any of the client machines. But
that's where I need it because those are the users that create and make use
of this text file.
My qustion is how to install bcp on the client machines without installing
MSDE. I thought it would work to just copy the file to an appropriate
location but apparently it needs to be installed. How do I go about that?
Thanks,
Keith
Never mind. I found a very nice inexpensive 3rd party control for use with
Delphi that will export datasources to all sorts of output formats. This is
a better solution since now I have no need at all for bcp.
Keith

BCP, ignore errors problem

Hi,
I am trying to import data from a Text file into a database Table using SQLserver BCP utility. I am able to do that when I have all new records in my Text file. But I am getting primary key violation error when I am trying to import the record which is already existing in the table. This is correct, but I want my program to ignore these errors and import only those records which are fine.
I tried [-m maxerrors] option, but it is not working. My BCP program is getting interrupted at the first error itself, even if I give [-m100] option.
my command looks something like this,
bcp pub..employee in C:\data.txt -b1 -m100 -c -t, \n -Sdatabase -Uuser -Ppassword

here -b1 is, processing 1 row per batch transaction
-m100 is, ignoring first 100 errors

please help.

thanks
madhuWhy not use BULK INSERT where it accept the CHECK_CONSTRAINTS hint and CHECK_CONSTRAINT clause, respectively, which allows the user to specify whether constraints are checked during a bulk load.

bcp with format file

I am trying to copy the data in excel file into a table using the bcp
and this is the code that I have. However the bcp utility does not
seem to create a format file, which I thought it should do. I am
probably going about this all wrong so any help would be useful.

exec master..xp_cmdshell '(FOR %i IN ("E:\WUTemp\*") DO (bcp
#ProspectImportTest in "%i" -fE:\WUTemp\Prospect.fmt)'
bulk insert #ProspectImportTest from 'E:\WUTemp\*."' with (formatfile =
'E:\WUTemp\Prospect.fmt')

Thanks.

KRKR (kraman@.bastyr.edu) writes:
> I am trying to copy the data in excel file into a table using the bcp
> and this is the code that I have. However the bcp utility does not
> seem to create a format file, which I thought it should do. I am
> probably going about this all wrong so any help would be useful.
> exec master..xp_cmdshell '(FOR %i IN ("E:\WUTemp\*") DO (bcp
> #ProspectImportTest in "%i" -fE:\WUTemp\Prospect.fmt)'
> bulk insert #ProspectImportTest from 'E:\WUTemp\*."' with (formatfile =
> 'E:\WUTemp\Prospect.fmt')

To have BCP to create a format file, you should specify "format" for
the direction parameter, not "in".

Furthermore, BCP cannot access the temp table #ProspectImportTest, as it
as local to your connection. You could try a global temp table,
##ProspectImportTest.

And BULK INSERT does not, as far as I know, accept file specifications with
wildcards in them.

As for the format file , I would not expect that format file be very useful.
You said Excel file, but you did not specify what format of Excel. BULK
INSERT cannot read an xls file, as that is a binary file. It can read
a CSV file or a tab-delimited file, but you don't need a format file for
that, just specify FIELDTERMINATOR with the BULK INSERT command. (Unless you
have a CSV file with strings quoted. Then you need a format file.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Guess I have a lot to learn yet - and you have given me good points. I
am new to this kind of thing.

Anyway, by the time I saw your reply, I had tried to go in another
direction where I was trying to do the same thing but I am working with
a csv file this time.

Thanks

BCP Why does it never work?

Hello All,
I have dreaded this day for some time but I knoew it would arrive one
day...and thats where I need to use the BCP utility to bulk upload data to
my web hosting service (telstra - Australia). Unfortunately they do not
allow the use of the Transact SQL statement BULK INSERT, as you guessed it
that works. Here's the problem I have been working on for a couple of days.
I have a data file created from SQL2000 server here in the office, it
contains 4 fields:
PartNumber varcha(15)
Description varchar(25)
QtyOnHand int
Price money
Some sample data cut and pasted from the data file, fixed length no nasties
between fields and a ODOA at the end of each line.
1000FGM FUEL FILTER/WATER SE 0 542.05
1000FGP Fuel Filter, Water S 0 580
1000FH2 Fuel Filter, Water S 8 548.13
1000MA Fuel Filter, Water S 3 594.5
11007 Lid, Bowl & Base Gas 23 3.29
11040 Bowl Drain Fitting 1 16.87
110A Fuel Filter, Water S 2 195.55
11350 T Handle O'Ring 12 2.06
12003 LID GASKET 1 7.83
12014 GASKET LOWER LID 1 5.49
12041 Bowl Plug 1 2.46
120AS Fuel Filter, Water S 2 244.59
122R FUEL FILTER/WATER SE 0 222.61
130R-T-16S Fuel Filter, Water S 0 207.56
15005 Lid Gasket 2 2.27
15009 Bowl Gasket 5 7.15
I have let BCP create the format file, and this doen't work, I have defined
the format file myself and still does not work. Here's a sample of the
format file:
This is the format file I used to run the bcp last time.
7.0
4
1 SQLCHAR 0 15 "" 1
PartNumber Latin1_General_CI_AS
2 SQLCHAR 0 25 "" 2
Description Latin1_General_CI_AS
3 SQLINT 0 4 "" 3
QtyOnHand ""
4 SQLMONEY 1 8 "\r\n" 4 Price
""
When I run the bcp command this is the error that is generated:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
found in BCP format-file
I am becoming extemely frustrated with this horrid little, but necessary
utility (Please Mr Telstra enable BULK INSERT command for me).
I have tried a number of variations, tab delimited files, no format file but
using the -n and -c switches but still no luck... If any body can provide
me with a solution it would be greatly appreciated.
Any suggestions '
Please reply directly to me at andrew.hull@.hcma.com.au
Thanks in advance.
Safe Sailing
AndrewBCP needs a NULL charater for empty fields. I strugled with this too.
Let Excel import it properly, then once you get it in your table, BCP it out
to a different file. THen compare the two files with LIST.EXE. Once you
are in LIST, hit H to go to Hex mode. You will see the NULL characters.
You cannot see them in NOTEPAD.
You would think that BCP is smart enough to move to a new record when it
hits the 0x0D 0x0A, but no, it is not.
"Plato" <andrew.hull@.hcma.com.au> wrote in message
news:OBk8%23OUiDHA.1964@.TK2MSFTNGP10.phx.gbl...
> Hello All,
> I have dreaded this day for some time but I knoew it would arrive one
> day...and thats where I need to use the BCP utility to bulk upload data
to
> my web hosting service (telstra - Australia). Unfortunately they do not
> allow the use of the Transact SQL statement BULK INSERT, as you guessed it
> that works. Here's the problem I have been working on for a couple of
days.
> I have a data file created from SQL2000 server here in the office, it
> contains 4 fields:
> PartNumber varcha(15)
> Description varchar(25)
> QtyOnHand int
> Price money
> Some sample data cut and pasted from the data file, fixed length no
nasties
> between fields and a ODOA at the end of each line.
> 1000FGM FUEL FILTER/WATER SE 0 542.05
> 1000FGP Fuel Filter, Water S 0 580
> 1000FH2 Fuel Filter, Water S 8 548.13
> 1000MA Fuel Filter, Water S 3 594.5
> 11007 Lid, Bowl & Base Gas 23 3.29
> 11040 Bowl Drain Fitting 1 16.87
> 110A Fuel Filter, Water S 2 195.55
> 11350 T Handle O'Ring 12 2.06
> 12003 LID GASKET 1 7.83
> 12014 GASKET LOWER LID 1 5.49
> 12041 Bowl Plug 1 2.46
> 120AS Fuel Filter, Water S 2 244.59
> 122R FUEL FILTER/WATER SE 0 222.61
> 130R-T-16S Fuel Filter, Water S 0 207.56
> 15005 Lid Gasket 2 2.27
> 15009 Bowl Gasket 5 7.15
> I have let BCP create the format file, and this doen't work, I have
defined
> the format file myself and still does not work. Here's a sample of the
> format file:
> This is the format file I used to run the bcp last time.
> 7.0
> 4
> 1 SQLCHAR 0 15 "" 1
> PartNumber Latin1_General_CI_AS
> 2 SQLCHAR 0 25 "" 2
> Description Latin1_General_CI_AS
> 3 SQLINT 0 4 "" 3
> QtyOnHand ""
> 4 SQLMONEY 1 8 "\r\n" 4 Price
> ""
> When I run the bcp command this is the error that is generated:
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
> found in BCP format-file
> I am becoming extemely frustrated with this horrid little, but necessary
> utility (Please Mr Telstra enable BULK INSERT command for me).
> I have tried a number of variations, tab delimited files, no format file
but
> using the -n and -c switches but still no luck... If any body can provide
> me with a solution it would be greatly appreciated.
>
> Any suggestions '
> Please reply directly to me at andrew.hull@.hcma.com.au
> Thanks in advance.
> Safe Sailing
> Andrew
>
>|||Hi Anthony,
The data itself is extracted from our ERP system and is massaged extensively
to produce a file where there are no blank fields and no null values, as you
know SQL and NULL are not good combination.
I have just got it working, using tab delimited file and using the -c switch
with no format file. All loads perfectly.
Over the last couple of days I have tried so many combinations that its not
funny. The simplest of them works but it took me ages to get to this point.
Now I have the process nailed to the wall so I will never forget the
pain...
Thanks again for your suggestion
Andrew
"Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
news:e5I0KOWiDHA.3324@.TK2MSFTNGP11.phx.gbl...
> BCP needs a NULL charater for empty fields. I strugled with this too.
> Let Excel import it properly, then once you get it in your table, BCP it
out
> to a different file. THen compare the two files with LIST.EXE. Once you
> are in LIST, hit H to go to Hex mode. You will see the NULL characters.
> You cannot see them in NOTEPAD.
> You would think that BCP is smart enough to move to a new record when it
> hits the 0x0D 0x0A, but no, it is not.
>
>
> "Plato" <andrew.hull@.hcma.com.au> wrote in message
> news:OBk8%23OUiDHA.1964@.TK2MSFTNGP10.phx.gbl...
> > Hello All,
> >
> > I have dreaded this day for some time but I knoew it would arrive one
> > day...and thats where I need to use the BCP utility to bulk upload data
> to
> > my web hosting service (telstra - Australia). Unfortunately they do not
> > allow the use of the Transact SQL statement BULK INSERT, as you guessed
it
> > that works. Here's the problem I have been working on for a couple of
> days.
> >
> > I have a data file created from SQL2000 server here in the office, it
> > contains 4 fields:
> > PartNumber varcha(15)
> > Description varchar(25)
> > QtyOnHand int
> > Price money
> >
> > Some sample data cut and pasted from the data file, fixed length no
> nasties
> > between fields and a ODOA at the end of each line.
> >
> > 1000FGM FUEL FILTER/WATER SE 0 542.05
> > 1000FGP Fuel Filter, Water S 0 580
> > 1000FH2 Fuel Filter, Water S 8 548.13
> > 1000MA Fuel Filter, Water S 3 594.5
> > 11007 Lid, Bowl & Base Gas 23 3.29
> > 11040 Bowl Drain Fitting 1 16.87
> > 110A Fuel Filter, Water S 2 195.55
> > 11350 T Handle O'Ring 12 2.06
> > 12003 LID GASKET 1 7.83
> > 12014 GASKET LOWER LID 1 5.49
> > 12041 Bowl Plug 1 2.46
> > 120AS Fuel Filter, Water S 2 244.59
> > 122R FUEL FILTER/WATER SE 0 222.61
> > 130R-T-16S Fuel Filter, Water S 0 207.56
> > 15005 Lid Gasket 2 2.27
> > 15009 Bowl Gasket 5 7.15
> >
> > I have let BCP create the format file, and this doen't work, I have
> defined
> > the format file myself and still does not work. Here's a sample of the
> > format file:
> >
> > This is the format file I used to run the bcp last time.
> > 7.0
> > 4
> > 1 SQLCHAR 0 15 "" 1
> > PartNumber Latin1_General_CI_AS
> > 2 SQLCHAR 0 25 "" 2
> > Description Latin1_General_CI_AS
> > 3 SQLINT 0 4 "" 3
> > QtyOnHand ""
> > 4 SQLMONEY 1 8 "\r\n" 4
Price
> > ""
> >
> > When I run the bcp command this is the error that is generated:
> >
> > SQLState = S1000, NativeError = 0
> > Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
> > found in BCP format-file
> >
> > I am becoming extemely frustrated with this horrid little, but necessary
> > utility (Please Mr Telstra enable BULK INSERT command for me).
> >
> > I have tried a number of variations, tab delimited files, no format file
> but
> > using the -n and -c switches but still no luck... If any body can
provide
> > me with a solution it would be greatly appreciated.
> >
> >
> >
> > Any suggestions '
> >
> > Please reply directly to me at andrew.hull@.hcma.com.au
> >
> > Thanks in advance.
> > Safe Sailing
> > Andrew
> >
> >
> >
>

Bcp utility with stored procedure

Guys,

I have stored proc sp_generate_insert which will generate insert scripts for the tables. When I run the stored Proc
from the management studio it runs fine. But when I run through stored proc as part of BCP utility I get this error.

'SQLState = 42000, NativeError = 536
Error = [Microsoft][SQL Native Client][SQL Server]Invalid length parameter passed to the SUBSTRING function.'

Execute dev.dbo.sp_generate_inserts 'auth' runs fine from management studio and generates inserts for auth table.

When I run the same proc as part of the following stored proc with bcp utility I get the error.

alter PROCEDURE INSERTTEST2 ( @.FILEPATH NVARCHAR(50))
AS
DECLARE @.cmd varchar(2000)
BEGIN
set @.cmd = 'bcp.exe "EXEC dev.dbo.SP_GENERATE_INSERTS auth" '
+ 'QUERYOUT' + ' ' +@.filePath+ '.sql ' +'-S ' +
'NV-DEVSQL3' + ' -q ' + ' -c -T -e' + @.filePath+'.log -o '
+ @.filePath+ '_out.log'
select @.cmd -- + '...'
EXEC master.dbo.xp_cmdShell @.cmd
END

Any suggestions or inputs would help.

Thanks

The problem lies within the proc, so we need to see that code.

Though usually, this error comes from statements where the length parameter in SUBSTRING becomes negative.

If you're dynamically trying to set how large chunk substring should take, and that variable becomes negative, then this error happens.

Since the problem seems to occur or not depending on method of connecting, it may suggest that there are different settings that may be the root cause.. (ie ANSI DEFAULTS etc)

Could this be it perhaps?

/Kenneth

|||kenneth,

Thank you for you reply.

I dont know if the problem is setting defaults on the database or the connection, more so since the stored proc - sp_generate_scripts runs fine from the managment studio.

Anyways the code for stored proc is available at the following link

http://vyaskn.tripod.com/code/generate_inserts_2005.txt

Any suggestions/inputs would help

Thanks

|||

I played around a bit with the proc and found some 'interesting' stuff...

I think your problem may be that you don't use the -d parameter in your bcp command, so you're not ending up in the right db.

The reason this matters may be the same that I found, but didn't notice at first...

(I tried it on SQL Server 2000).
First when compiled, there was a msg about not finding sys.sp_MS_marksystemobject, but the proc compiled anyway, so I tried it out.

Got the same message as you a couple of times, but found that only if I was in a db other than master. Made a usertable in master, then it worked. =/

So, fixed the 'sys.sp_MS_marksystemobject' to 'sp_MS_marksystemobject' and recompiled (since the former doesn't exist in 2000, only in 2005) and tried again. Now all is smooth, and it works like it's supposed to.

Apparently, the proc needs to be marked as a systemobject, else you may get these 'db-scope' issues, so check out if this is the problem.

/Kenneth

BCP Utility with null values

Hi all,
I am using BCP to output from a table that has some empty values into a
flat file. I noticed that when I try to use BCP to upload the flat
file into an exact replica of the table in another server I get an
error due to restrictions on that table that does not allow null
values. I think what is happening is that BCP convert empty values
into null during the output process and when trying to upload the value
I get the error.
Is there a way to go around the fact that BCP is converting empty
values to NULL or should I be using a different process to upload the
data from one server into another. Also, I don't have the option to
connect to the remote server from any of them hence the reason why I am
using BCP.
Thank you very much.
Ron.Hi Ron
What options are you specifying on the BCP command? This should be ok and
you should not get conversions.
John
"Ron" wrote:
> Hi all,
> I am using BCP to output from a table that has some empty values into a
> flat file. I noticed that when I try to use BCP to upload the flat
> file into an exact replica of the table in another server I get an
> error due to restrictions on that table that does not allow null
> values. I think what is happening is that BCP convert empty values
> into null during the output process and when trying to upload the value
> I get the error.
> Is there a way to go around the fact that BCP is converting empty
> values to NULL or should I be using a different process to upload the
> data from one server into another. Also, I don't have the option to
> connect to the remote server from any of them hence the reason why I am
> using BCP.
> Thank you very much.
> Ron.
>sql

BCP Utility with null values

Hi all,
I am using BCP to output from a table that has some empty values into a
flat file. I noticed that when I try to use BCP to upload the flat
file into an exact replica of the table in another server I get an
error due to restrictions on that table that does not allow null
values. I think what is happening is that BCP convert empty values
into null during the output process and when trying to upload the value
I get the error.
Is there a way to go around the fact that BCP is converting empty
values to NULL or should I be using a different process to upload the
data from one server into another. Also, I don't have the option to
connect to the remote server from any of them hence the reason why I am
using BCP.
Thank you very much.
Ron.Hi Ron
What options are you specifying on the BCP command? This should be ok and
you should not get conversions.
John
"Ron" wrote:

> Hi all,
> I am using BCP to output from a table that has some empty values into a
> flat file. I noticed that when I try to use BCP to upload the flat
> file into an exact replica of the table in another server I get an
> error due to restrictions on that table that does not allow null
> values. I think what is happening is that BCP convert empty values
> into null during the output process and when trying to upload the value
> I get the error.
> Is there a way to go around the fact that BCP is converting empty
> values to NULL or should I be using a different process to upload the
> data from one server into another. Also, I don't have the option to
> connect to the remote server from any of them hence the reason why I am
> using BCP.
> Thank you very much.
> Ron.
>

BCP Utility with null values

Hi all,
I am using BCP to output from a table that has some empty values into a
flat file. I noticed that when I try to use BCP to upload the flat
file into an exact replica of the table in another server I get an
error due to restrictions on that table that does not allow null
values. I think what is happening is that BCP convert empty values
into null during the output process and when trying to upload the value
I get the error.
Is there a way to go around the fact that BCP is converting empty
values to NULL or should I be using a different process to upload the
data from one server into another. Also, I don't have the option to
connect to the remote server from any of them hence the reason why I am
using BCP.
Thank you very much.
Ron.
Hi Ron
What options are you specifying on the BCP command? This should be ok and
you should not get conversions.
John
"Ron" wrote:

> Hi all,
> I am using BCP to output from a table that has some empty values into a
> flat file. I noticed that when I try to use BCP to upload the flat
> file into an exact replica of the table in another server I get an
> error due to restrictions on that table that does not allow null
> values. I think what is happening is that BCP convert empty values
> into null during the output process and when trying to upload the value
> I get the error.
> Is there a way to go around the fact that BCP is converting empty
> values to NULL or should I be using a different process to upload the
> data from one server into another. Also, I don't have the option to
> connect to the remote server from any of them hence the reason why I am
> using BCP.
> Thank you very much.
> Ron.
>

bcp utility stringing

ok. so, i've gotten the bcp utility to work and now i'd like to string all the prompts together and place them in a job to be scheduled to autorun. i don't have much experience in this arena and appreciate any help or suggestions. I'd love to be able to run (from the command prompt) a file takes care of synchronizing all my tables.

I have an additional concern. I'd like to retain the integrity of my primary keys but I notice that if i delete the information then add new, my primary key starts where it leaves off (in other words, if del. then synch. a table with 6 records, after the synch. my primary key increments starting at7). Is there a way, using the bcp util. to update records based on the primary key, copy the key verbatim, or a way to configure the table to help out with this? I know if i drop and recreate the tables as part of the job, i'm good to go but is there away around that step?

Hi DJ,

You could write all your bcp commands into a script file and then use the Windows Task Scheduler to call the bcp utility and pass in the script file. There should be informaiton on how to pass a file to bcp in Books Online, once you have it working manually, it's pretty straight forward to do the same thing using Task Scheduler.

You use of the work "job" indicates you might be familiar with SQL Agent, which is used in other Editions of SQL to schedule tasks. SQL Agent is not included in SQL Express, so that is not available to you in the Express context.

As far as your question about Primary Keys, the behavior you're seeing is by design. By definition, a Primary Key value is never repeated once used. Under some conditions there is cause to override this behavior. One way to do that is to use SET IDENTITY_INSERS ON, which is documented at http://msdn2.microsoft.com/en-us/library/ms188059(SQL.90).aspx. You can also check out the topic about keeping identity values during a bulk insert at http://msdn2.microsoft.com/en-us/library/ms186335(SQL.90).aspx.

Regards,

Mike Wachal

BCP utility replaces German with junk characters

Hi All,
I am using command line bcp utility of SQL Server to import data from a text
file to database.

I have some german words in the text file and after
import the German characters are lost.

see eg below.

Input : Khner, Klaus -> Text file value
OutPut: Khner, Klaus -> Table data, after import.

I am using unicode in the commad line.
The collation setting is LATIN

can anyone show some light.

Thanks in advance

Manoj.Manoj (mcmanoj_2000@.yahoo.com) writes:
> I am using command line bcp utility of SQL Server to import data from a
> text file to database.
> I have some german words in the text file and after
> import the German characters are lost.
> see eg below.
> Input : Khner, Klaus -> Text file value
> OutPut: K?hner, Klaus -> Table data, after import.
> I am using unicode in the commad line.
> The collation setting is LATIN

Since BCP is a command-line utility it's defaul code page is the OEM
code page. Therefore, if your server uses an ANSI page, and this is
the normal, there is an automatic conversion from OEM to ANSI. Problem
is if the file is in ANSI already - which it often is.

BCP offers the -C option to control this. The simplest is to use
-C RAW to turn off conversion.

See the description of BCP in Books Online for more details.

--
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 Utility login failure

What am I doing wrong to cause this error
C:\>bcp bookshop..bookcondition in bookcondition.txt -c -T -Ujack -Ppasswor
SQLState = 28000, NativeError = 1845
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'J
CK_WACHTLER\Jack Wachtler'
Thanks for your help
JackJack,
you'll need to remove the -T argument which selects trusted security and
therefore ignores the last 2 arguments. Have a look in BOL : bcp utility,
overview for a listing of all the individual arguments.
HTH,
Paul Ibison|||Using the trusted connection it should assume I am already logged in as a authenticated user. So why am I getting this error? I don't get it
C:\>bcp BookShopDB..BookCondition in BookCondition.txt -c -
SQLState = 28000, NativeError = 1845
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'J
CK_WACHTLER\Jack Wachtler'
Jack|||Jack,
this will create a separate connection to sql server and will only work if
'JACK_WACHTLER\Jack Wachtler' exist as a windows login on the default
instance of sql server on the local computer where you are running bcp, or
alternatively if this windows user exists in a group added as a windows
login.
HTH,
Paul Ibison|||Hi Jack,
Dont you require the -S <Server_name> along with your bcp command? Try to
include this option and verify.
THanks
Hari
MCDBA
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OL6HON4OEHA.2356@.TK2MSFTNGP10.phx.gbl...
> Jack,
> this will create a separate connection to sql server and will only work if
> 'JACK_WACHTLER\Jack Wachtler' exist as a windows login on the default
> instance of sql server on the local computer where you are running bcp, or
> alternatively if this windows user exists in a group added as a windows
> login.
> HTH,
> Paul Ibison
>sql

bcp Utility login failure

What am I doing wrong to cause this error?
C:\>bcp bookshop..bookcondition in bookcondition.txt -c -T -Ujack -Ppassword
SQLState = 28000, NativeError = 18456
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login fai
led for user 'JA
CK_WACHTLER\Jack Wachtler'.
Thanks for your help!
JackJack,
you'll need to remove the -T argument which selects trusted security and
therefore ignores the last 2 arguments. Have a look in BOL : bcp utility,
overview for a listing of all the individual arguments.
HTH,
Paul Ibison|||Using the trusted connection it should assume I am already logged in as a au
thenticated user. So why am I getting this error? I don't get it.
C:\>bcp BookShopDB..BookCondition in BookCondition.txt -c -T
SQLState = 28000, NativeError = 18456
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login fai
led for user 'JA
CK_WACHTLER\Jack Wachtler'.
Jack|||Jack,
this will create a separate connection to sql server and will only work if
'JACK_WACHTLER\Jack Wachtler' exist as a windows login on the default
instance of sql server on the local computer where you are running bcp, or
alternatively if this windows user exists in a group added as a windows
login.
HTH,
Paul Ibison|||Hi Jack,
Dont you require the -S <Server_name> along with your bcp command? Try to
include this option and verify.
THanks
Hari
MCDBA
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OL6HON4OEHA.2356@.TK2MSFTNGP10.phx.gbl...
> Jack,
> this will create a separate connection to sql server and will only work if
> 'JACK_WACHTLER\Jack Wachtler' exist as a windows login on the default
> instance of sql server on the local computer where you are running bcp, or
> alternatively if this windows user exists in a group added as a windows
> login.
> HTH,
> Paul Ibison
>

bcp Utility login failure

What am I doing wrong to cause this error?
C:\>bcp bookshop..bookcondition in bookcondition.txt -c -T -Ujack -Ppassword
SQLState = 28000, NativeError = 18456
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'JA
CK_WACHTLER\Jack Wachtler'.
Thanks for your help!
Jack
Jack,
you'll need to remove the -T argument which selects trusted security and
therefore ignores the last 2 arguments. Have a look in BOL : bcp utility,
overview for a listing of all the individual arguments.
HTH,
Paul Ibison
|||Using the trusted connection it should assume I am already logged in as a authenticated user. So why am I getting this error? I don't get it.
C:\>bcp BookShopDB..BookCondition in BookCondition.txt -c -T
SQLState = 28000, NativeError = 18456
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'JA
CK_WACHTLER\Jack Wachtler'.
Jack
|||Jack,
this will create a separate connection to sql server and will only work if
'JACK_WACHTLER\Jack Wachtler' exist as a windows login on the default
instance of sql server on the local computer where you are running bcp, or
alternatively if this windows user exists in a group added as a windows
login.
HTH,
Paul Ibison
|||Hi Jack,
Dont you require the -S <Server_name> along with your bcp command? Try to
include this option and verify.
THanks
Hari
MCDBA
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OL6HON4OEHA.2356@.TK2MSFTNGP10.phx.gbl...
> Jack,
> this will create a separate connection to sql server and will only work if
> 'JACK_WACHTLER\Jack Wachtler' exist as a windows login on the default
> instance of sql server on the local computer where you are running bcp, or
> alternatively if this windows user exists in a group added as a windows
> login.
> HTH,
> Paul Ibison
>

BCP utility help

I want to bcp out the record set in a flat file. I am unable to write the
correct script to do this.
What I am trying to do is build a text comma delimited file by running a
stored procedure say procTest. This bcp command will be executed in a nightl
y
job.
Please help.David (David@.discussions.microsoft.com) writes:
> I want to bcp out the record set in a flat file. I am unable to write the
> correct script to do this.
> What I am trying to do is build a text comma delimited file by running a
> stored procedure say procTest. This bcp command will be executed in a
> nightly job.
BCP db.dbo.tbl out tblout.bcp -T -c -t,
This is a command-line operation. To run it from a stored procedure,
you would have to call xp_cmdshell to spawn out to command-line level.
Now, when you say comma-delimited, do you in fact mean something like:
"value",2,"other value",98
then it gets trickier, particularly if the first column needs a quote.
If the first column needs a quote, you can use a formar file. If the
first column needs a quote, you will need to use the queryout option, or
define a view or possibly use a global temp table. Queryout appears to
give people headache, so I would stay away from that one.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The line to tell BCP to import a comma separated file to SQL table is:
-t,
It's so tricky.
*** Sent via Developersdex http://www.examnotes.net ***

BCP Utility - Skip rows..

Hello All,

Does the BCP utility enable you to selectively import rows from a flat
file to a table ?

For example:

The first column in my flat file contains a record type - 1, 2..7
I only need to import types 1, 2, & 3

Can this be specified in the .fmt file ?

Thanks in advance
hharryhharry (paulquigley@.nyc.com) writes:
> Does the BCP utility enable you to selectively import rows from a flat
> file to a table ?
> For example:
> The first column in my flat file contains a record type - 1, 2..7
> I only need to import types 1, 2, & 3
> Can this be specified in the .fmt file ?

It depends on the format of the records, but I would say that it is
highly unlikely. In fact, if the record types are heterogeneous, you may
not be able to write a format file to describe the file at all.

If they record types are homegeneous to fit into one format, you can bulk
load into a staging table, and the move on to the target table from there
with the interesting rows.

Also, if the records appears evenly in strict order: 12345671234567...
you can handle all as one big record. But I would not really expect
your file be like that...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||There are 7 different record types within the file - all different
lengths.
I am going to parse the file via a dotnet exe.
Thanks for the assistance

Erland Sommarskog wrote:
> hharry (paulquigley@.nyc.com) writes:
> > Does the BCP utility enable you to selectively import rows from a
flat
> > file to a table ?
> > For example:
> > The first column in my flat file contains a record type - 1, 2..7
> > I only need to import types 1, 2, & 3
> > Can this be specified in the .fmt file ?
> It depends on the format of the records, but I would say that it is
> highly unlikely. In fact, if the record types are heterogeneous, you
may
> not be able to write a format file to describe the file at all.
> If they record types are homegeneous to fit into one format, you can
bulk
> load into a staging table, and the move on to the target table from
there
> with the interesting rows.
> Also, if the records appears evenly in strict order:
12345671234567...
> you can handle all as one big record. But I would not really expect
> your file be like that...
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||One of our people created a table that was, essentially:

record_type int
rest_of_record varchar(long_enough)

loaded the table with a bcp format that was just a field for the record type
and a varchar field for the rest.

Then, he created a SP that used a cursor to walk through the table and T-SQL
decoded the records into various fields and did the inserts into destination
tables.

I found out what they'd done after we upgraded from SQL Server 6.5 and the
process stopped working because the rows no longer came out in
First-In/First-Out order. The SP was almost entirely unmaintainable,
anyway.

We wrote something maintainable and understandable in VB6 (with record
types, liberal use of constant declarations) in a few hours. Much better. *

I wonder if you could use DTS to accomplish what you want? If you haven't
already, why not drop the question into microsoft.public.sqlserver.dts?

* If I remember correctly, the emergency workaround was to add an identity
column to the work table and have the cursor pull rows in sorted order on
the identity column.

"hharry" <paulquigley@.nyc.com> wrote in message
news:1109366729.311250.16040@.l41g2000cwc.googlegro ups.com...
> Hello All,
> Does the BCP utility enable you to selectively import rows from a flat
> file to a table ?
> For example:
> The first column in my flat file contains a record type - 1, 2..7
> I only need to import types 1, 2, & 3
> Can this be specified in the .fmt file ?
>
> Thanks in advance
> hharry|||dh (dh@.news.net) writes:
> One of our people created a table that was, essentially:
> record_type int
> rest_of_record varchar(long_enough)
> loaded the table with a bcp format that was just a field for the record
> type and a varchar field for the rest.
> Then, he created a SP that used a cursor to walk through the table and
> T-SQL decoded the records into various fields and did the inserts into
> destination tables.

Yeah, that's a normal approach to do it. You write a program that reads
the file. But T-SQL is a poor choice for the task, since it's not good
on string handling. And while you can add an IDENTITY, you cannot be
really sure that it works anyway.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

bcp utility

I'm using the bcp utility to export data to a network file. Is there a way to
output the field names in addition to the data? According to Books Online,
there isn't a switch for this.
Hi Mike
Look at using the import/export wizard, DTS or SSIS to do this, you can do
it with BCP using something like:
BCP "SELECT au_id, au_lname, au_fname, phone, address, city, state, zip,
contract FROM ( SELECT CAST(au_id as varchar(30)) as au_id, au_lname,
au_fname, phone, address, city, state, zip, CAST(contract as varchar(30)) AS
contract, 1 AS orderby FROM PUBS..Authors UNION ALL SELECT 'au_id',
'au_lname', 'au_fname', 'phone', 'address', 'city', 'state', 'zip',
'contract', 0 ) A ORDER BY orderby" QUERYOUT authors.txt -c -T -S (local)
but it's a bit of a cludge!
John
"mike" wrote:

> I'm using the bcp utility to export data to a network file. Is there a way to
> output the field names in addition to the data? According to Books Online,
> there isn't a switch for this.
sql

BCP utility

In the process of exporting data from SQL data file to text file through BCP utility I am not getting the Column names.How can I get the column names through BCP utility?
I used this script
exec master..xp_cmdShell 'bcp "select * from regulator.dbo.TEMPTBLBRANCHNOTUPLOAD" QueryOUT \\indiadb\ftproot\CLIENT_BRANCH_UPLOAD\branchnotup loaded.csv -S indiadb -U sa -P sasocrates -k -r \n -c -t "," -q'It doesn't...did you look at BOL?

You can however "fake it out"

It would require a view or using QUERYOUT...

Something like

SELECT 'HEADING1','HEADING2',ect
UNION ALL
SELECT Col1, Col2, ect
FROM yourTable

Just make sure you CONVERT everyhting to varchar...

BCP Utility

Hi all,

I want to use the BCP utility to import data from a .dat file into my database. The .dat file contains a table called xv_Appointments containing the following fields:

AppointmentKey
SurgerySlotKey
PatientKey
Cancelled
Continuation
Deleted
TimeArrived

I would like to import only two of these fields into a table called tbl_Appointments e.g.

AppointmentKey
TimeArrived

I can't seem to get the BCP util to do this. It only works if I import all of the fields from xv_Appointments. Does anyone know if this is possible?

ThanksReplace IN with FORMAT and add "-f file_name.fmt" without quotes on the command line when issuing BCP statement.

Open your favorite text editor and modify the format file to look something like this:8.0
7
1 SQLINT 0 0 "\t" 1 AppointmentKey ""
2 SQLCHAR 0 255 "\t" 0 SurgerySlotKey SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 255 "\t" 0 PatientKey SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 255 "\t" 0 Cancelled SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 255 "\t" 0 Continuation SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 255 "\t" 0 Deleted SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 255 "\r\n" 7 TimeArrived SQL_Latin1_General_CP1_CI_AS|||Thanks for replying and you've certainly pointed me in the right direction. I've almost got it working but for a couple of problems. The first problem is that when it imports the Appointment key it seems to be adding a tab character or something to before the key e.g. 341084.80096.Appointment becomes 341084.80096.Appointment when imported to the new table. Also, I can't get it to import the date field 'TimeArrived' (please see attached), SQL server throws up the following error message:

Server: Msg 4829, Level 16, State 1, Line 1
Could not bulk insert. Error reading destination table column name for source column 8 in format file

Even although the column name is correct...I have tried importing the other date fileds and it does the same thing.

I have attached my .fmt file for you to have a look at.

Thanks|||The second token in the format file (where you have SQLCHAR etc) refers to the format of the field IN THE HOST FILE, not on the server. If this a date in ASCII readable string format, e.g. "Jan 1 2004 10:00AM" or "2004-01-01" the this should be set to SQLCHAR

see Books online : bcp utility / format files / Using format files|||Tried setting the date field to SQLCHAR (see attached file) but it's still giving me the same error message:

Server: Msg 4829, Level 16, State 1, Line 1
Could not bulk insert. Error reading destination table column name for source column 8 in format file '\\Henke\Corporate\48hrAccess\48hrDataArchive\8009 6\bcp.fmt'

Here's the SQL I'm using:

BULK INSERT tbl_UpdateTimeArrived FROM '\\Henke\Corporate\48hrAccess\48hrDataArchive\8009 6\xv_Appointment.dat'
WITH (FORMATFILE = '\\Henke\Corporate\48hrAccess\48hrDataArchive\8009 6\bcp.fmt

The only other thing that may affect it is the .dat file in question is from a SQL Server 6.5 database and I'm importing it into a SQL 2000 database.|||maybe your SQLBIT columns should be SQLCHAR as well...|||Still didn't work but I'm slowly getting to the bottom of it. Tried importing a non-date, non-bit field and it imported fine (again see attached) - so it seems to be the date fields that are causing the problem...Any ideas?|||can you attach say top 5 lines of your data file ?|||I've figured it out. I need the same number of fields in the destination table as the table in the .dat file. Even although I only want to import two fileds. In effect, the destination table needs to be an exact replica regardless of whether I want to use all the fields or not!!

If anybodyt knows any different then please let me know.

bcp utility

I'm using SQL 2005 to export data. I would like to use the bcp utility
to export data to an Excel file.
I have to generate quite a few files and the names are dynamic. The
ideal would be to loop through records in a stored procedure to create
a file name to use in the bcp. My question is how can I use the bcp
from a stored procedure? I know how to run it from the command prompt.
Is there a way to control the command prompt from a stored procedure?

Thanks allYou can use the extended stored procedure xp_cmdshell to execute bcp from a
stored procedure. Please read in SQL Server Books Online the security
implications. Assuming you do not want to run it under an account that is
member of sysadmin, you may want to set up a proxy account via
sp_xp_cmdshell_proxy.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||Mike (mckeyes@.gmail.com) writes:

Quote:

Originally Posted by

I'm using SQL 2005 to export data. I would like to use the bcp utility
to export data to an Excel file.
I have to generate quite a few files and the names are dynamic. The
ideal would be to loop through records in a stored procedure to create
a file name to use in the bcp. My question is how can I use the bcp
from a stored procedure? I know how to run it from the command prompt.
Is there a way to control the command prompt from a stored procedure?

As Plamen said, you can use xp_cmdshell, but xp_cmdshell is a security
risk and for this reason it is disabled by default. It may be better
to write a small application VBscript or whatever you fancy to run
the export.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Bcp utility

command :
bcp [ad-ad].ager.article out c:\Article.txt -n -Sserver -Uuser -Ppassword
return error:
SqlState = 37000, NativeError=4060
error= [Microsoft][odbc SQL Server Driver][Sql Server] Cannot open database
requested in login '[ad-ad]. Login fails.
Solutions ?Catalin
exec master..xp_cmdshell 'bcp northwind..orders out c:\cust2.csv -c -t
"," -S<server> -Usa -P'
"Catalin Tudorescu" <catalint@.ager.ro> wrote in message
news:ezP6amhnDHA.744@.tk2msftngp13.phx.gbl...
> command :
> bcp [ad-ad].ager.article out c:\Article.txt -n -Sserver -Uuser -Ppassword
> return error:
> SqlState = 37000, NativeError=4060
> error= [Microsoft][odbc SQL Server Driver][Sql Server] Cannot open
database
> requested in login '[ad-ad]. Login fails.
> Solutions ?
>|||Problem is name of database [ad-ad].
"Catalin Tudorescu" <catalint@.ager.ro> wrote in message
news:ezP6amhnDHA.744@.tk2msftngp13.phx.gbl...
> command :
> bcp [ad-ad].ager.article out c:\Article.txt -n -Sserver -Uuser -Ppassword
> return error:
> SqlState = 37000, NativeError=4060
> error= [Microsoft][odbc SQL Server Driver][Sql Server] Cannot open
database
> requested in login '[ad-ad]. Login fails.
> Solutions ?
>|||Catalin
> requested in login '[ad-ad]. Login fails.
Are you sure? The error message tells something else.
"Catalin Tudorescu" <catalint@.ager.ro> wrote in message
news:#s7WZuhnDHA.1948@.TK2MSFTNGP12.phx.gbl...
> Problem is name of database [ad-ad].
> "Catalin Tudorescu" <catalint@.ager.ro> wrote in message
> news:ezP6amhnDHA.744@.tk2msftngp13.phx.gbl...
> > command :
> > bcp [ad-ad].ager.article out
c:\Article.txt -n -Sserver -Uuser -Ppassword
> > return error:
> > SqlState = 37000, NativeError=4060
> > error= [Microsoft][odbc SQL Server Driver][Sql Server] Cannot open
> database
> > requested in login '[ad-ad]. Login fails.
> > Solutions ?
> >
> >
>|||Catalin;
Use the -q command line switch. There seems to be some problem with square
brackets around the database name for the bcp utility. I've run into the
same error message, which doesn't seem to be relevant at all.
Anyway, here's the bcp command line that works:
bcp ad-ad.ager.article out c:\Article.txt -n -Sserver -Uuser -Ppassword -q
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OwMRkwhnDHA.2364@.TK2MSFTNGP11.phx.gbl...
> Catalin
> > requested in login '[ad-ad]. Login fails.
> Are you sure? The error message tells something else.
>
> "Catalin Tudorescu" <catalint@.ager.ro> wrote in message
> news:#s7WZuhnDHA.1948@.TK2MSFTNGP12.phx.gbl...
> > Problem is name of database [ad-ad].
> >
> > "Catalin Tudorescu" <catalint@.ager.ro> wrote in message
> > news:ezP6amhnDHA.744@.tk2msftngp13.phx.gbl...
> > > command :
> > > bcp [ad-ad].ager.article out
> c:\Article.txt -n -Sserver -Uuser -Ppassword
> > > return error:
> > > SqlState = 37000, NativeError=4060
> > > error= [Microsoft][odbc SQL Server Driver][Sql Server] Cannot open
> > database
> > > requested in login '[ad-ad]. Login fails.
> > > Solutions ?
> > >
> > >
> >
> >
>|||Catalin,
try this:
bcp "ad-ad.ager.article" out c:\Article.txt -n -Sserver -Uuser -Ppassword
"Catalin Tudorescu" <catalint@.ager.ro> wrote in message
news:%23s7WZuhnDHA.1948@.TK2MSFTNGP12.phx.gbl...
> Problem is name of database [ad-ad].
> "Catalin Tudorescu" <catalint@.ager.ro> wrote in message
> news:ezP6amhnDHA.744@.tk2msftngp13.phx.gbl...
> > command :
> > bcp [ad-ad].ager.article out
c:\Article.txt -n -Sserver -Uuser -Ppassword
> > return error:
> > SqlState = 37000, NativeError=4060
> > error= [Microsoft][odbc SQL Server Driver][Sql Server] Cannot open
> database
> > requested in login '[ad-ad]. Login fails.
> > Solutions ?
> >
> >
>