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 Out each result of a query in a saperate file

hi,

i am trying to BCP out the result of a query to a seperate file. i am trying to create a seperate file for each record. could somebody please help me out?

Pankaj

Do you really mean a seperate file for each row?
Or a separate file for each table?

If it's for each row, then how many rows are there?
..and why?

/Kenneth

|||

yes,

seperate file for each row. just trying to send file as per required by customer. by the way i am trying with CURSORS.

Pankaj

|||

Sorry for the late answer, it's busy times ;o)

It seems a very odd request from your customer...
In a query, how many rows are you expecting to do this on?

/Kenneth

bcp import of decimal data (long)

I am trying to import data from a flat file into a decimal(9,2) column. Here is the decimal data in each record of the flat file:

123456789

There is no decimal point in the field but last two positions represent the right of the decimal point, some fields have leading zeros)

I have discovered the following format file (extract) appears to do the import and place the decimal properly in the table column (1234567.89). Note the table column number and name are the same:

snip

11 SQLCHAR 0 7 "" 11 FieldName ""
12 SQLCHAR 0 2 "" 11 FieldName ""
snip

But bcp throws this error after a few seconds:

snip

C:\>bcp DatabeseName..Tablename in DataFileName.dat -fFormatFile.fmt -S.\SQLExpress -T

Starting copy...

SQLState = HY000, NativeError = 0

Error = [Microsoft][SQL Native Client]Unspecified error occurred

Connection may have been terminated by the server.

SQLState = 08S01, NativeError = 233

Error = [Microsoft][SQL Native Client]Shared Memory Provider: No

he other end of the pipe.

SQLState = 08S01, NativeError = 233

Error = [Microsoft][SQL Native Client]Communication link failure

SQLState = 08S01, NativeError = 0

Error = [Microsoft][SQL Native Client]Communication link failure

... (last two line repeated until abort)

BCP copy in failed

snip

For some reason the error does not occur with format files with different field formats. However other formats do not import properly.

Can someone identify this error or perhaps tell me how the above decimal format can be imported from a flat file as described? Any help is really appreciated as I have wasted several days already...TIA.

Sturdy

sturdy wrote:

I am trying to import data from a flat file into a decimal(9,2) column. Here is the decimal data in each record of the flat file:

123456789

There is no decimal point in the field but last two positions represent the right of the decimal point, some fields have leading zeros)

I have discovered the following format file (extract) appears to do the import and place the decimal properly in the table column (1234567.89). Note the table column number and name are the same:

snip

11 SQLCHAR 0 7 "" 11 FieldName ""
12 SQLCHAR 0 2 "" 11 FieldName ""
snip...

In the format file the destination column ordinal are supposed to be unique, except when the ordinal is 0. Hence the format file like this is wrong and BCP should have complained about it. Apparently, there must be a bug in the BCP format file parser and the parser misses the defect in the format file instead of failing the statement. Since the shape of TDS stream that comes to the SQL Engine from BCP is not what is expected, unltimately the statement still fails, but much later - when, possibly, much more serious damage is done, including silent corruptions of the data in the database. Thanks for discovering this.

Back to your original question - the bulk load format file is unable to provide you with the functionality you need. Essentially what you require is to specify an additional scaling factor for your integer input data. One suggestion that comes to mind - use SSIS (aka DTS). This is exactly the kind of a problem the SSIS/DTS was originally intended for.

|||

Thanks Tengez. At least that explains why I was having so much trouble ;-) The bcp/format file documentation is horrendous.

It is strange though, that the column numbers as I described does place the decimal correctly.

Sturdy

bcp and exporting

Hello,
I'm exporting data from text file which contains 15 lakh records.
Each field is delimited by "," and record is delimited by "$".
Only 163200 records were inserted.
I'm using this option
bcp tablename in
"datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
Any workarounds?Hi
Was there any error message?
John
"Raj" wrote:

> Hello,
> I'm exporting data from text file which contains 15 lakh records.
> Each field is delimited by "," and record is delimited by "$".
> Only 163200 records were inserted.
> I'm using this option
> bcp tablename in
> "datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
> Any workarounds?
>
>

bcp and exporting

Hello,
I'm exporting data from text file which contains 15 lakh records.
Each field is delimited by "," and record is delimited by "$".
Only 163200 records were inserted.
I'm using this option
bcp tablename in
"datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
Any workarounds?Hi
Was there any error message?
John
"Raj" wrote:
> Hello,
> I'm exporting data from text file which contains 15 lakh records.
> Each field is delimited by "," and record is delimited by "$".
> Only 163200 records were inserted.
> I'm using this option
> bcp tablename in
> "datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
> Any workarounds?
>
>

BCP Insert record error (SQL2005-Window 2003)

Hi All,

When we to inset record into the tables through BCP we are getting following errors.

C:\> bcp ACCT.dbo.dtpro out C:\test\dtpro_TA.dat -T-n
C:\>bcp ACCT.dbo.Semiaa out C:\test\Semiaa_TA.dat -T -n

BCP out works fine only BCP in have problems.

Please can you let me know how to resolved this problem.

===========
Exp - 1
===========

C:\> bcp ACCT.dbo.dtpro in C:\test\dtpro_TA.dat -T-n

Starting copy...
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1

===========
Exp - 2
===========

C:\>bcp ACCT.dbo.Semiaa in C:\test\Semiaa_TA.dat -T -n

Starting copy...
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1

============
Exp-3
============

C:\>bcp ACCT.dbo.SemiH in C:\test\SemiH_TA.dat -T -n

Starting copy...SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file
SQLState = 42000, NativeError = 7339
Error = [Microsoft][SQL Native Client][SQL Server]OLE DB provider 'STREAM' for l
inked server '(null)' returned invalid data for column '[!BulkInsert].Created'.

BCP copy in failed

Thanks in Advance

There are many reasons this error can happen. It could be, and often is, that the schemas of the databases are incompatible. Post a reply containing the schemas of these databases if you can. That would greatly help in diagnosing the problem.

Jay

|||

C:\>bcp ACCT.dbo.dtpro in C:\test\dtpro_TA.dat -T-n

Starting copy...
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
******************************************

table dtpro

[id] [int] NULL,
[objectid] [int] NULL,
[property] [varchar](64) NULL,
[value] [varchar](255) NULL,
[uvalue] [nvarchar](510) NULL,
[lvalue] [image] NULL,
[version] [int]NULL,

********************************************

|||The error message typically indicates some sort of corruption in the file due to invalid lines, invalid terminators etc. Can you try to create a dummy file on a different location and try? If you are using the file that you BCPed out then do that again but to a different location and try.|||

The schema seems simple enough. It seems odd that you can't import into the same table you exported from, since your table doesn't contain fields that would preclude that.

I would try what Umachandar suggested, and if that doesn't work, try and isolate which records might be causing the problems by using the -F and -L options of bcp to export subsets.

|||

Were you ever able to get this resolved?

Jay Kint

basics with stored procedures.....please

Hi there, I'm still fairly new to asp.net / c#

I've setup my stored procedure, but now what?
I want to display the information (only one record it will return) on the page / NOT using a datagrid or list, just simple....I guess using an asp:label???
Could someone tell me what I need to do!!!

Also, are there any proper resources out there that just show you right out of the box howto insert / update / delete with sp's?, show / view, just proper simple source code?

THANKS!

my code:
// Creating the database connection
SqlConnection objConn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["MM_CONNECTION_STRING_conn_main"]);

// Creating the stored procedure 4 the webpage
// Instantiate a Sql Command
SqlCommand webpage_view = new SqlCommand("stpr_webpage_view", objConn);
// Declare that the T-SQL statement that will be executed is a stored procedure
// Use a CommandType.Text to execute a T-SQL Query
webpage_view.CommandType = CommandType.StoredProcedure;
SqlParameter webpage_id_parm = webpage_view.Parameters.Add("@.webpage_id", SqlDbType.Int);
webpage_id_parm.Value = "1";
// Create a SqlDataAdapter to manage the connection and retrieve the data
SqlDataAdapter da_1 = new SqlDataAdapter();
// Instantiate a Dataset that will hold the data retrieved from the database using the select command
DataSet ds_webpage = new DataSet();
da_1.SelectCommand = webpage_view;
// Retrieve the data into the dataset using the SqlDataAdapter.Fill command
da_1.Fill( ds_webpage, "webpage_title" );
DataView dv2 = ds_webpage.Tables[0].DefaultView;
if ( !this.IsPostBack ) {
this.DataBind ( );
}

// C#
// To place a single value from a recordset into a label
// drag an ASP:Label onto the Web form and give it an ID of myLabel
myLabel.Text = "No data";
// Be compulsive and make sure there is a datatable in the dataset and that it has at least one row
if( ds_webpage.Tables.Count > 0 && ds_webpage.Tables[0].Rows.Count > ) ){
 myLabel.Text = ds_webpage.Tables[0].Rows[0].ToString();
}

The simplest way to see how stored procedures can be used to select and update data is to let VS.Net create it for you.

1. Go to Design View for a web form.
2. Drag an SqlDataAdapter onto the form. A DataAdapter Configuration Wizard will open
3. Select your database connection and click Next.
4. On the Choose a Query Type screen select the second option "Create a new Stored Procedure". Click Next
5. On the Generate a Stored Procedure screen enter a select statement for the fields you want to use. For example, just something like Select * from SomeTable. Click Next.
6. On the Create the Stored Procedures screen enter appropriate stored procedure names. Be sure to click the "Yes, create them in the database for me" radio button.

Look at the code behind generated for this data adapter to see how it all works. Use it like the one you created above for the select. For the update, after you changed data in the data set just call:
da_1.Update( ds_webpage, "webpage_title" );

Basic Trigger Firing Question

If I insert 20 records into a table which has an INSERT trigger on it, does the trigger fire once for each record, or once at the end of all the inserts?

I seem to be having a problem where the trigger is firing only for some of the inserted records but not others, seemingly randomly (although I know there must be some logical explanation for how it's choosing which record to fire under).

Thanks =) MattHere's how I ended up getting multirow inserts to work properly using an INSTEAD OF trigger rather than a AFTER trigger.

-- INSTEAD OF Trigger That Fires Single-Row AFTER Triggers for a Table with IDENTITY

CREATE TRIGGER trg_T1IOIS ON T1 INSTEAD OF INSERT
AS

SELECT IDENTITY(int, 1, 1) AS key_col, data_col
INTO #t1
FROM inserted

DECLARE @.key AS int
SELECT @.key = MIN(key_col) FROM #t1

WHILE @.key IS NOT NULL
BEGIN

INSERT INTO T1
SELECT data_col FROM #t1 WHERE key_col = @.key

SELECT @.key = MIN(key_col)
FROM #t1
WHERE key_col > @.key

END
GO|||Each Trigger written should be able to deal with either a Single Insert/Delete/update or Bulk Insert/Delete/update

I tend to use the following Format :-

BEGIN
IF @.@.RowCount =1
BEGIN
/*DO SINGLE ROW OPERATION*/
END
ELSE
BEGIN
/*DO MULTI ROW OPERATION*/
END
END

Generally I alter the TSQL to cope with both eventualities
and I believe it is good practice to apply this to ALL Triggers unless of course there are special cicumstances.

Hope this Helps

GW

basic trigger ?

Hi
I want to have a trigger than when record is inserting into table A, the
trigger inserts a record into table B
I want the identity value from Table A to be one of the values inserted into
table B.
How do I get the indentity value from tableA in my trigger so that I can
insert into table b
thanksAussie Rules (someone@.somewhere.com) writes:
> I want to have a trigger than when record is inserting into table A, the
> trigger inserts a record into table B
> I want the identity value from Table A to be one of the values inserted
> into table B.
> How do I get the indentity value from tableA in my trigger so that I can
> insert into table b
CREATE TRIGGER A_tri ON A FOR INSERT AS
INSERT B (some_col, some_other_col, ...)
SELECT i.identity_col, i.other_col, ...
FROM inserted i
JOIN ...
inserted is a virttual table holds the inserted rows, so you find the
identity value right there.
Be aware of that a trigger fires once per *statement*.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Sirs
You might want to consider using 'After Insert' instead of 'For
Insert'. This way you know that the record has been successfully inserted
into Table A, Before you insert into Table B.
Mark
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96C24758FE7FYazorman@.127.0.0.1...
> Aussie Rules (someone@.somewhere.com) writes:
the
> CREATE TRIGGER A_tri ON A FOR INSERT AS
> INSERT B (some_col, some_other_col, ...)
> SELECT i.identity_col, i.other_col, ...
> FROM inserted i
> JOIN ...
> inserted is a virttual table holds the inserted rows, so you find the
> identity value right there.
> Be aware of that a trigger fires once per *statement*.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||I expect you meant Instead Of rather than For.
For Insert triggers are classified as After Insert triggers, and only fire
after the row has been successfully inserted. Instead Of triggers fire in
place of the triggering action. If you want to perform an insert, then the
body of the Instead Of Insert trigger must issue an insert into the
underlying table.
"Mark Moss" <markmoss@.adelphia.net> wrote in message
news:O4hAxQPrFHA.1172@.TK2MSFTNGP11.phx.gbl...
> Sirs
> You might want to consider using 'After Insert' instead of 'For
> Insert'. This way you know that the record has been successfully inserted
> into Table A, Before you insert into Table B.
> Mark
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns96C24758FE7FYazorman@.127.0.0.1...
> the
inserted
can
>

Basic SQL Execution Question

I have a table that contains a history of record changes within another table. It may have rows that look like this:

DML_Type Record_ID Record_Value DateTime
Insert 1 A Today @. 11AM
Update 1 B Today @. 11:15AM
Update 1 C Today @. 11:22AM
Delete 1 C Today @. 11:25AM
Insert 2 A Today @. 11AM

Update 2 B Today @. 11:15AM

Update 2 C Today @. 11:22AM

I've inherited a stored procedure that migrates these changes to another database in another schema. The proc is using a cursor to perform each operation in sequence.

If I perform the migration in 3 steps (inserts, updates, deletes) - Can I be sure that the updates will occur in the correct sequence if ordered by DateTime or is there a possibility that SQL Server will parellize the operation and I'd end up with incorrect data? For example, is there a possibility using the T-SQL below that I'd end up with a record_ID 2 and a value of B?

UPDATE
destination table
SET
field1 = field,
...
FROM
SELECT
Record_ID,
Record_Value
FROM
sourceTable st
WHERE
DML_Type = 'Update'
ORDER BY
DateTime
WHERE
dt.Record_ID = st.Record_ID

Dan:

Is Record_ID unique in the [destination table]?

Dave

|||Yes. Record_ID is unique in the destination table. It's amusing that you ask this because the problem I'm dealing with can be completely avoided by using the state of the record in the original table - the audit history isn't important for our purposes.

I am still interested in the answer to the original question for my own edification however.|||

Dan:

Here are a couple of mock-ups of your update:

set nocount on

update destinationTable
set record_value = 'A',
[dateTime] = '12/13/2006 11:00'
--select * from destinationTable

--
-- This works with the particular data but it is really
-- not supported; this is likely to be faster than
-- the second query:
--
/*
UPDATE destinationTable
SET record_value = st.record_value,
[dateTime] = st.[dateTime]
from destinationTable dt
inner join sourceTable st
on dt.record_Id = st.record_id
and st.DML_Type = 'Update'
*/

--
-- This is "safer":
--

update destinationTable
set record_value = st.record_value,
[dateTime] = st.[dateTime]
from destinationTable dt
inner join
( select record_id,
record_value,
[dateTime],
row_number () over
( partition by record_id
order by [dateTime] desc, record_value
) as seq
from sourceTable
where dml_type = 'Update'
) st
on dt.record_id = st.record_id
and st.seq = 1

select * from destinationTable

-- Output:

-- record_id record_value dateTime
-- -- --
-- 1 C 2006-12-13 11:22:00.000
-- 2 C 2006-12-13 11:22:00.000

|||Thanks for the response. Based on your answer, I'm going to assume that the default behavior is undefined and may produce data loss. Now I'll have to read up on partioning :-)