bcp: starting copy...

hello - i have this problem:
i am able to use bulk insert script like
"bulk insert demodb..table1
from 'c:\filename.ext' with (formatfile = 'c:\bcp.fmt')"
to import a binary file into sql server.
table1 has 4 columns, noted column is number 2, type image, here's my format file:
8.0
1
1 SQLIMAGE 0 100184 "" 2 logo ""
i import only the binary file.
however when trying to do the same using bcp with the same format file, it just says
"starting copy..." and goes to sleep !
any idea what i should do?
i created a temp table with just an IMAGE column and then the bcp method works, but i rather need it with my current setup - table with 4 columns

thank youAny error or information on SQL error log for this behaviour.

Can take help of PROFILER to see the activity during this execution.

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 Interactive - from script

I have a script where I'm doing a bcp out - I need to have the script do the format file on export.

Is there a way to run bcp interactively from script, accepting the defaults? THat way it will make a format file for me.

when you use bcp, a format file is not required, most of our scripts just use the native defaults, we never use format files. Is there a reason you need a format file?|||THis is for an archiving process, and I need to automate the BCP back in...is there a way to bcp in without a format file? I've always used one...|||yes, you can bcp in without a format file. We've never ever had to use a format file when bcp'ing in or out. Of course our datatypes always matched, and we always use -n, but I'm sure -c works as well.|||You can generate a format file like so:

bcp pubs..authors2 format nul -fc:\authors.fmt -Sservername -Usa -Ppassword|||

Thanks so much!

I couldn't find anything anywhere.

THis solves a big problem.

BCP Handling

Hi,

I am executing script like this. How to check for the errors if "master..xp_cmdshell @.bcpCommand" fails. Is there any way to verify that BCP is completed successfully

DECLARE @.FileName varchar(50),
@.bcpCommand varchar(2000)

SET @.FileName = 'E:\TestBCPOut.txt'
SET @.bcpCommand = 'bcp "SELECT * FROM pubs1..authors ORDER BY au_lname" queryout "'
SET @.bcpCommand = @.bcpCommand + @.FileName + '" -c -U -P'

EXEC master..xp_cmdshell @.bcpCommand

Thanks in Advance,declare @.ret int
EXEC @.ret=master..xp_cmdshell @.bcpCommand

|||Is is possible...........|||

In addition, you can also specify an error file for your bcp command, and then check it afterwards for any content:

DECLARE @.FileName varchar(50),
@.bcpCommand varchar(2000)
SET @.FileName = 'E:\TestBCPOut.txt'
SET @.bcpCommand = 'bcp "SELECT * FROM pubs1..authors ORDER BY au_lname" queryout "'
SET @.bcpCommand = @.bcpCommand + @.FileName + '" -c -U -P -ee:\myBCPerror.txt'

declare @.ret int
EXEC @.ret=master..xp_cmdshell @.bcpCommand

CREATE TABLE #bcperr(input varchar(255) null)
INSERT #bcperr(input) EXEC master..xp_cmdshell 'type e:\myBCPerror.txt'
IF EXISTS(SELECT * FROM #bcperror WHERE input IS NOT NULL)
RAISERROR('There was an error with the BCP command.', 16, 1)
DROP TABLE #bcperr

You can also just do a SELECT * FROM #bcperr to get the actual error rows.

|||

thanks a lot..

any idea how to trap the number of rows that were transffered during the bcp out process...

|||

If you also specify an output file with the -o parameter, you can 'parse' it and grab the line with the rows total in it.

declare @.rc int
EXEC @.rc = master..xp_cmdshell 'find c:\myBCPoutput.txt "rows copied"'

output
NULL
- C:\MYBCPOUTPUT.TXT
23 rows copied.
NULL

(4 row(s) affected)

=;o)

/Kenneth

|||Hey how do I capture this in a table... betn thanks !!!|||

Here's an example.

create table #bcpResult
( result varchar(50) null )
go

declare @.rc int

insert #bcpResult
EXEC @.rc = master..xp_cmdshell 'find c:\myBCPoutput.txt "rows copied"'
go
select * from #bcpResult where result like '%rows copied%'
go
drop table #bcpResult
go

=;o)
/Kenneth

BCP Entire DB in MYSQL 2000

I have a stored proc that currently BCP 27 tables into a MS SQL 2000 DB. The original script was created to import FIXED-WIDTH. I have made the changes to TAB-DELIMITED. But the source db that outputs the TAB files is enclosing every field within double quotes("). I am getting a 'Invalid character value for cast specification' error on the first row which is an integer. Is there a parameter that I can set for bcp to strip out the "...".

Thanks,
JNunezNYCWhy don't you use DTS where you can specify the required settings for data.

Bcp data with quotes

I have a table with the following data
A_Id A_Name A_Desig
A324 Author1 Script Writer
T533 Tester Test cases
Now I want to export the data to text file with the following format
"A324" "Author1" "Script Writer"
"T533" "Tester" "Test cases"
Declare @.str varchar(1000) ,@.FileName varchar(100), @.table
varchar(100)
set @.table='Author'
set @.FileName='C:\Author.txt'
set @.str='Exec Master..xp_Cmdshell ''bcp "Select * from
'+db_name()+'..'+@.table+'" queryout "'+@.FileName+'" -t """","""" -c"'''
Exec(@.str)
But I get the result with first and last quotes missing
A324" "Author1" "Script Writer
T533" "Tester" "Test cases
How do I get the desired result?
MadhivananHi Madhivanan
You have a couple of choices. You could either specify a format file or add
the additional "" to your select statement. I've created a quick example
against the pubs database table employee for you ...
bcp "select '"""'+emp_id+'"""', '"""'+fname+'"""' from pubs..Employee"
queryout c:\outputfile.txt -c -t, -Ssql-test -T
This result in...
"A-C71970F","Aria"
"A-R89858F","Annette"
"AMD15433F","Ann"
"ARD36773F","Anabela"
"CFH28514M","Carlos"
"CGS88322F","Carine"
"DBT39435M","Daniel"
"DWR65030M","Diego"
HTH. Ryan
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1138178304.808428.247350@.f14g2000cwb.googlegroups.com...
>I have a table with the following data
> A_Id A_Name A_Desig
> A324 Author1 Script Writer
> T533 Tester Test cases
> Now I want to export the data to text file with the following format
> "A324" "Author1" "Script Writer"
> "T533" "Tester" "Test cases"
> Declare @.str varchar(1000) ,@.FileName varchar(100), @.table
> varchar(100)
> set @.table='Author'
> set @.FileName='C:\Author.txt'
> set @.str='Exec Master..xp_Cmdshell ''bcp "Select * from
> '+db_name()+'..'+@.table+'" queryout "'+@.FileName+'" -t """","""" -c"'''
> Exec(@.str)
> But I get the result with first and last quotes missing
> A324" "Author1" "Script Writer
> T533" "Tester" "Test cases
>
> How do I get the desired result?
> Madhivanan
>|||Thanks, Ryan
When I run your query, I get yhis error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '+emp_id+'.
Madhivanan|||Madhivanan (madhivanan2001@.gmail.com) writes:
> Thanks, Ryan
> When I run your query, I get yhis error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '+emp_id+'.
A query? Ryan did not post a query. He posted something run in a command-
line window. (I tested it, and it works, once I had corrected the
table name, and removed the -S option to run on the local server.)
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|||Thanks Erland
I also made some changes to work
bcp "select '"""'+emp_id+'"""', '"""'+fname+'"""' from pubs..Employee"
queryout c:\outputfile.txt -c -q
Madhivanan

BCP & DMO

I can script out a table's data using dmo and the bulkcopy2 object but how
can I get it to script out using ORDER BY?
Thanks
FrankAs a workaround, create a view with a SELECT TOP 100 PERCENT... ORDER BY and
then export from there.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Frank Ashley" <aa@.aa.com> wrote in message
news:exUksDmIFHA.1096@.tk2msftngp13.phx.gbl...
I can script out a table's data using dmo and the bulkcopy2 object but how
can I get it to script out using ORDER BY?
Thanks
Frank|||I could but that would mean creating temporary views for each table that i
want to script out them dropping them at the end. Unless somebody can come
up with the solution that's what i'll do.
Frank
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eTWj63mIFHA.236@.TK2MSFTNGP14.phx.gbl...
> As a workaround, create a view with a SELECT TOP 100 PERCENT... ORDER BY
> and
> then export from there.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Frank Ashley" <aa@.aa.com> wrote in message
> news:exUksDmIFHA.1096@.tk2msftngp13.phx.gbl...
> I can script out a table's data using dmo and the bulkcopy2 object but how
> can I get it to script out using ORDER BY?
>
> Thanks
> Frank
>|||hi Frank,
Frank Ashley wrote:
> I could but that would mean creating temporary views for each table
> that i want to script out them dropping them at the end. Unless
> somebody can come up with the solution that's what i'll do.
AFAIK, the solution proposed by Tom is the only way you can achieve the
desired result..
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Batch T-SQL Scripts

In Horacle (thanks to someone else for the apt pseudonym), I am able to run a PL/SQL script saved as a file by simply typing in @. plus the path and file name. So, if I have a script called E:\CreateTable.sql, I can simply enter @.CreateTable.sql from the SqlPlus command line and Oracle will attempt to run all the commands in the E:\CreateTable.sql file (there may be one or more commands).

Can I do the same thing in Query Analyzer (or even osql)? If so, what is the correct syntax?

Thanks,

hmscottI don't know of a way to do it from a script in either Query Analyzer or OSQL, but you can open a new file in Query Analyzer and execute it that way and you can use the ED command in OSQL to allow you to incorporate the file (via the editor) there too.

-PatP|||Not that I've seen...the closest is

File>Open>filename.sql

[CTRL]+E

And Whore-acle is so much more painful in SQL+...I guess each has it's own pluses...

Did you ever use mask.sql?

Oh, and osql could run in a command line pretty easily...just make sure you redirect the output...

But why bother, unless you're releasing a script to production...|||Thnx guys. Just wishful thinking...

Batch Script

Hello everyone,

I have about 20 dtsx packages to run in a particular order.
Some create tables, other fill/convert data, and others just clean/remove temporary tables.

Everything works ok manually...
But, i need to run them automatically.

A batch script seems the quickest and more simple way to do...
So, I made a batch with something like this:

dtexec.exe file1.dtsx > output1.txt
dtexec.exe file2.dtsx > output2.txt
dtexec.exe file3.dtsx > output3.txt

(the ideia is to save the logs...)

My first question is...
I get a very usual "product level is insufficient" (0xC00470FE) error on some components...
It's wierd because manually works ok. But through the batch it returns lot's of these...
I searched for information and found this...
It's a nice topic, not enought to fix my problem.

My second question is...
A noob batch script question:
Can i make something like this:
dtexec.exe file1.dtsx > output1.txt
IF FIND /C /I "End Error" output1.txt != 0 GOTO EXITSCRIPT
The ideia is to search the log for errors, and if found any run other batch file...

Thank you so much for any help!!!

David

Have you thought about just creating a master package that runs your packages in the desired order? You have a lot more flexibility that way instead of batch files. Just my opinion.

Batch Insert in SQL Server 2000 Database

I have a process that inserts data into a SQL Server 2000 database using a
VB script. Before inserting data into the database there are numerous checks
from data in the database that need to checked to make sure the data is
correct before inserting into the database. This batch process is very slow
inserting data into the database. Since the data is extracted from several
large table to checked.
Are there 3rd party utilities that could help me with this process or better
way to complete this process?
Thank You,Consider using staging tables, whereby you load these tables without the
constraints and then run your validation queries inside the DB. Insert only
the valid data.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:412A4856-B609-42F3-AAB0-F7EBE7F4B56E@.microsoft.com...
> I have a process that inserts data into a SQL Server 2000 database using a
> VB script. Before inserting data into the database there are numerous
> checks
> from data in the database that need to checked to make sure the data is
> correct before inserting into the database. This batch process is very
> slow
> inserting data into the database. Since the data is extracted from several
> large table to checked.
> Are there 3rd party utilities that could help me with this process or
> better
> way to complete this process?
> Thank You,

Batch Insert in SQL Server 2000 Database

I have a process that inserts data into a SQL Server 2000 database using a
VB script. Before inserting data into the database there are numerous check
s
from data in the database that need to checked to make sure the data is
correct before inserting into the database. This batch process is very slow
inserting data into the database. Since the data is extracted from several
large table to checked.
Are there 3rd party utilities that could help me with this process or better
way to complete this process?
Thank You,Consider using staging tables, whereby you load these tables without the
constraints and then run your validation queries inside the DB. Insert only
the valid data.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:412A4856-B609-42F3-AAB0-F7EBE7F4B56E@.microsoft.com...
> I have a process that inserts data into a SQL Server 2000 database using a
> VB script. Before inserting data into the database there are numerous
> checks
> from data in the database that need to checked to make sure the data is
> correct before inserting into the database. This batch process is very
> slow
> inserting data into the database. Since the data is extracted from several
> large table to checked.
> Are there 3rd party utilities that could help me with this process or
> better
> way to complete this process?
> Thank You,

Batch Insert in SQL Server 2000 Database

I have a process that inserts data into a SQL Server 2000 database using a
VB script. Before inserting data into the database there are numerous checks
from data in the database that need to checked to make sure the data is
correct before inserting into the database. This batch process is very slow
inserting data into the database. Since the data is extracted from several
large table to checked.
Are there 3rd party utilities that could help me with this process or better
way to complete this process?
Thank You,
Consider using staging tables, whereby you load these tables without the
constraints and then run your validation queries inside the DB. Insert only
the valid data.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:412A4856-B609-42F3-AAB0-F7EBE7F4B56E@.microsoft.com...
> I have a process that inserts data into a SQL Server 2000 database using a
> VB script. Before inserting data into the database there are numerous
> checks
> from data in the database that need to checked to make sure the data is
> correct before inserting into the database. This batch process is very
> slow
> inserting data into the database. Since the data is extracted from several
> large table to checked.
> Are there 3rd party utilities that could help me with this process or
> better
> way to complete this process?
> Thank You,

Batch insert (OLAP)

Hi can any one help me with the skeleton script (sample one)of running Bulk insert in batches...... I need to do it in batches as the input data is huge....

The logic is I have to insert thru bcp in fact table...

After that batch execution for 50,000 thousand record... wise... if any of the batch failes i need to identify and have to rerun from that point onwards..... this is OLAP thing...what's the data source?

50,000 ain't that much btw|||50,000 record comes in one batch that waht I meant...... so if there is 1 million record 20 batches will be there...

The source come from DB2 or so which we get it as source file... we create staging table for that in Sql Server... Now we need to do the rest porting data to Fact & Dimension tables|||what script?
you can use create a failover process in dts to allow x number of error rows to pass through the ETL and then you can clean them up the next day if you prefer. its part of the error reporting process in dts tasks.|||So the data is already in a table and you're trying to limit the impact to the logs...can you describe your process how are you building your warehouse..|||:eek: I didnt get u I never asked for error reporting stuff!!!!!|||http://www.winnetmag.com/article/articleid/42903/42903.html

I was trying to do the same way how the above article describe to solve such kinda stuff

Batch execute of SQL script from ADO.Net

I have a VB.Net application that needs to create about 5 stored
procedures and a couple of functions on SQL Server 2005 Express.
Currently I can execute all of them in one window of Sql Server
Management Studio, just separate each of them with a "GO" statement. Is
there a way I can accomplish this "one shot" approach via ADO.Net in my
application? If so, I can just put all my ddl SQL code in a text file
as an embedded resource, and then execute it in a couple of lines of
code. However, I suspect that I need to execute each ddl statement
separately and thus will need to parse the text file to break it up, or
break the sql code into multiple files, one for each stored proc.
Thanks for your thoughts,
Marcus[Reposted, as posts from outside msnews.microsoft.com does not seem to make
it in.]
Marcus (holysmokes99@.hotmail.com) writes:
> I have a VB.Net application that needs to create about 5 stored
> procedures and a couple of functions on SQL Server 2005 Express.
> Currently I can execute all of them in one window of Sql Server
> Management Studio, just separate each of them with a "GO" statement. Is
> there a way I can accomplish this "one shot" approach via ADO.Net in my
> application? If so, I can just put all my ddl SQL code in a text file
> as an embedded resource, and then execute it in a couple of lines of
> code. However, I suspect that I need to execute each ddl statement
> separately and thus will need to parse the text file to break it up, or
> break the sql code into multiple files, one for each stored proc.
Yes, if you read this file from your own application, you will need to
parse the file for "go" and send down batch by batch with ExcecuteNonQuery.
Parsing the file for "go" is a trivial matter.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Basic Replication trouble.

I'm trying to run a simple update script on this database which is
created from a replication agent. Several individual databases are
replicated into one consolidated. I've not worked with replication
before and I was hoping someone could tell me what this error means.

UPDATE
CUSTOMER
SET
[TIMESTAMP]='20060920090453'
[IVBTYPE]='M '
WHERE
[ROWID]='9f83bc89-76f8-4140-a0cc-58fa34962638'

yields:

Server: Msg 208, Level 16, State 1, Procedure
upd_0119B5A9AA624A55AF1B73F2E32A7A0C, Line 14
Invalid object name 'dbo.sysmergearticles'.

Do I have to do something to the database before trying to update it?P.S. I notice I'm missing a comma in there after the first set, missed
it while I was copying it over and didn't notice until I posted.