BCP/Bulk Insert and Incoming Dates

Ladies and Gentlemen
Is their a way to force BCP to accept a Date Value of
'000000'
instead of giving an error?
I am using BULK INSERT with a FORMAT FILE.
Mark MossHi Mark
The easiest way would be to use a staging table and then you can cater for
this when you update the main table. Another alternative would be to use DTS
an AnctiveX transform or possibly a lookup.
John
"Mark Moss" wrote:

> Ladies and Gentlemen
>
> Is their a way to force BCP to accept a Date Value of
> '000000'
> instead of giving an error?
>
> I am using BULK INSERT with a FORMAT FILE.
>
> Mark Moss
>
>

bcp utilities

Hi
When I used the bcp utilities to output a query to a text file, the date
field become a "10/2/2003 00:00:00" instead of mm/dd/yyyy format. How can I
correct this?
Thanks!
ChrisYou could use :-
bcp with the queryout option and specifiy a select statement that
converts the date to your format
or
create a view that returns the data as you require and then bcp out throught
the view
or
specifiy a format file for the bcp file to use
--
HTH
Ryan Waight, MCDBA, MCSE
"ChrisM" <cma1@.mail.com> wrote in message
news:ezveEJ6pDHA.2740@.TK2MSFTNGP09.phx.gbl...
> Hi
> When I used the bcp utilities to output a query to a text file, the date
> field become a "10/2/2003 00:00:00" instead of mm/dd/yyyy format. How can
I
> correct this?
> Thanks!
> Chris
>|||Ryan
Thanks for your replied. Could you give me some example on how the queryout
option converts the date to the format I need? Below is the bcp command I
use.
bcp pubs..titles out "C:\bcp_test
Output.txt" -c -q -S"sqlserver" -U"sa" -P"xxx"
Thanks!
Chris
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uqZNap6pDHA.392@.TK2MSFTNGP11.phx.gbl...
> You could use :-
> bcp with the queryout option and specifiy a select statement that
> converts the date to your format
> or
> create a view that returns the data as you require and then bcp out
throught
> the view
> or
> specifiy a format file for the bcp file to use
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "ChrisM" <cma1@.mail.com> wrote in message
> news:ezveEJ6pDHA.2740@.TK2MSFTNGP09.phx.gbl...
> > Hi
> >
> > When I used the bcp utilities to output a query to a text file, the date
> > field become a "10/2/2003 00:00:00" instead of mm/dd/yyyy format. How
can
> I
> > correct this?
> >
> > Thanks!
> >
> > Chris
> >
> >
>|||You would have to use the CONVERT statement which converts the dates into a
string, and with which you can define a format for the date (the 3:rd
parameter to the function).
bcp "SELECT CONVERT(char(10), ord_date, 101) + ' ' + CONVERT(char(8),
ord_date, 108) FROM pubs..sales" out
"C:\bcp_test\Output.txt" -c -q -S"sqlserver" -U"sa" -P"xxx"
Alternatively, you can create a view with the query and CONVERTS and then
export from the view.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"ChrisM" <cma1@.mail.com> wrote in message
news:esqf0M%23pDHA.2000@.TK2MSFTNGP12.phx.gbl...
> Ryan
> Thanks for your replied. Could you give me some example on how the
queryout
> option converts the date to the format I need? Below is the bcp command I
> use.
> bcp pubs..titles out "C:\bcp_test
> Output.txt" -c -q -S"sqlserver" -U"sa" -P"xxx"
> Thanks!
> Chris
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:uqZNap6pDHA.392@.TK2MSFTNGP11.phx.gbl...
> > You could use :-
> > bcp with the queryout option and specifiy a select statement that
> > converts the date to your format
> >
> > or
> >
> > create a view that returns the data as you require and then bcp out
> throught
> > the view
> >
> > or
> >
> > specifiy a format file for the bcp file to use
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "ChrisM" <cma1@.mail.com> wrote in message
> > news:ezveEJ6pDHA.2740@.TK2MSFTNGP09.phx.gbl...
> > > Hi
> > >
> > > When I used the bcp utilities to output a query to a text file, the
date
> > > field become a "10/2/2003 00:00:00" instead of mm/dd/yyyy format. How
> can
> > I
> > > correct this?
> > >
> > > Thanks!
> > >
> > > Chris
> > >
> > >
> >
> >
>

bcp into view with derived column

Hi,

I have a view that looks something like this -

CREATE VIEW myview AS SELECT
myudf(col1) as col1, col2, col3
FROM mytable

The view has an 'INSTEAD OF' trigger on it to do the correct thing on
insert. When I run the bcp, it runs successfully, but the value
inserted into col1 is always NULL. I modified my trigger to get a
better idea of what was happening, and when I look at the values of
INSERTED.col1, they are all null. It appears that bcp is setting the
column value to null, presumable because the view definintion for this
column is a derived column.

Does any one know a way around this?

Thanks!On 10 Jan 2006 11:28:32 -0800, bsandell@.gmail.com wrote:

>Hi,
>I have a view that looks something like this -
>CREATE VIEW myview AS SELECT
> myudf(col1) as col1, col2, col3
>FROM mytable
>The view has an 'INSTEAD OF' trigger on it to do the correct thing on
>insert. When I run the bcp, it runs successfully, but the value
>inserted into col1 is always NULL. I modified my trigger to get a
>better idea of what was happening, and when I look at the values of
>INSERTED.col1, they are all null. It appears that bcp is setting the
>column value to null, presumable because the view definintion for this
>column is a derived column.
>Does any one know a way around this?
>Thanks!

Hi bsandell,

By default, triggers are not fired for bulk copy statements.

To override this default, add the option
-h "FIRE_TRIGGERS"

--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,

Thanks, I am using the -h "FIRE TRIGGERS". The trigger is definitely
being executed. I have added the following line to my trigger code -

SELECT inserted.* into pubs..dummy from inserted

My input file looks like this -
aaa,aaa,aaa
bbb,bbb,bbb
ccc,ccc,ccc

but when the code above runs in my trigger, the output is -
NULL,aaa,aaa
NULL,bbb,bbb
NULL,ccc,ccc

so it looks like bcp has somehow determined that the first column in my
view does not have a 1-to-1 mapping with a column on the database
(because it's based on a udf) and set it to null? I'd like to find a
way to work around this if possible, without changing the view
definition or the underlying table.

Any ideas?

Thanks,
Bruce|||(bsandell@.gmail.com) writes:
> Thanks, I am using the -h "FIRE TRIGGERS". The trigger is definitely
> being executed. I have added the following line to my trigger code -
> SELECT inserted.* into pubs..dummy from inserted
> My input file looks like this -
> aaa,aaa,aaa
> bbb,bbb,bbb
> ccc,ccc,ccc
> but when the code above runs in my trigger, the output is -
> NULL,aaa,aaa
> NULL,bbb,bbb
> NULL,ccc,ccc
> so it looks like bcp has somehow determined that the first column in my
> view does not have a 1-to-1 mapping with a column on the database
> (because it's based on a udf) and set it to null? I'd like to find a
> way to work around this if possible, without changing the view
> definition or the underlying table.

Smells bug to me. To wit, it works with BULK INSERT, which uses OLE DB
in difference of BCP which uses ODBC. The behaviour is the same in
SQL 2000 and SQL 2005, so I've field a bug for it,
http://lab.msdn.microsoft.com/Produ...ackId=FDBK43711
You can vote for it, if you like.

The repro below is also in the bug report:

USE tempdb
go
CREATE TABLE sandell (col1 int NULL,
col2 int NULL,
col3 int NULL)
go
CREATE FUNCTION myudf(@.c int) RETURNS int AS
BEGIN
RETURN (SELECT 80 - @.c)
END
go
CREATE VIEW sandell_vy AS
SELECT col1 = dbo.myudf(col1), col2, col3
FROM sandell
go
CREATE TRIGGER sandell_tri ON sandell_vy INSTEAD OF INSERT AS
INSERT sandell(col1, col2, col3)
SELECT 80 - col1, col2 + 20, col3 + 20 FROM inserted
go
INSERT sandell_vy (col1, col2, col3)
SELECT 12, 98, 23
go
SELECT * FROM sandell_vy
go
EXEC master..xp_cmdshell 'ECHO 9, 2, 98 > C:\TEMP\bulk.csv', 'no_output'
EXEC master..xp_cmdshell 'ECHO 76, 23, 87 >> C:\TEMP\bulk.csv', 'no_output'
go
BULK INSERT sandell_vy FROM 'C:\temp\bulk.csv'
WITH (FIELDTERMINATOR = ',',
DATAFILETYPE = 'char',
FIRE_TRIGGERS)
go
DECLARE @.bcp nvarchar(4000)
SELECT @.bcp = 'bcp tempdb..sandell_vy in C:\temp\bulk.csv -T -c -t, -h "FIRE_TRIGGERS" -S ' + @.@.servername
EXEC master..xp_cmdshell @.bcp
go
SELECT * FROM sandell_vy
go
DROP VIEW sandell_vy
DROP FUNCTION myudf
DROP TABLE sandell
EXEC master..xp_cmdshell 'DEL C:\temp\bulk.csv', 'no_output'

--
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|||Hi Erland,

As always, I really appreciate your help. Any guesses on the liklihood
that this would get fixed? I didn't think there was much going on with
bcp support these days. Thanks again for you time and effort.

Bruce|||(bsandell@.gmail.com) writes:
> As always, I really appreciate your help. Any guesses on the liklihood
> that this would get fixed? I didn't think there was much going on with
> bcp support these days. Thanks again for you time and effort.

I would guess that it is more likely that it will be fixed in SQL 2005 than
in SQL 2000. Whether the fix will come in SP1 for SQL 2005, I don't want to
speculate in.

If this is critical for you, and you need a hotfix, you will have to open
a case with Microsoft, and try to convince them.

--
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 in sql server 2005

Hi,

1 Is it Bcp supported in Sql server 2005?

2 Instead of using BCP, Is there any other alternative options in 2005

Thanks

Altaf Nizamuddin

Here is the how to technet article on the T-SQL Bulk Copy Utility for 2005 (which can be used through a execute sql task):

http://msdn2.microsoft.com/en-us/library/ms162802.aspx

Also, check your control flow items for "Bulk Insert Task" item, which will copy the data from flat file to table (although it has been stated that this may be depricated in a future version and is mainly in this version for compatibility between dts and ssis)

Note that using the sql server destination adapter in the data flow you will have comparable performance to the bulk insert task with more flexibility. This is owing to the fact that the sql server destination adapter uses the same interface into sql server as the bulk insert task. You MUST be in the sysadmin or bulkadmin groups to run either SQL Server Destination Adapter or Bulk Insert Task.

|||

Thanks EWishdahl

BCP in sql server 2005

Hi,

1 Is it Bcp supported in Sql server 2005?

2 Instead of using BCP, Is there any other alternative options in 2005

Thanks

Altaf Nizamuddin

Here is the how to technet article on the T-SQL Bulk Copy Utility for 2005 (which can be used through a execute sql task):

http://msdn2.microsoft.com/en-us/library/ms162802.aspx

Also, check your control flow items for "Bulk Insert Task" item, which will copy the data from flat file to table (although it has been stated that this may be depricated in a future version and is mainly in this version for compatibility between dts and ssis)

Note that using the sql server destination adapter in the data flow you will have comparable performance to the bulk insert task with more flexibility. This is owing to the fact that the sql server destination adapter uses the same interface into sql server as the bulk insert task. You MUST be in the sysadmin or bulkadmin groups to run either SQL Server Destination Adapter or Bulk Insert Task.

|||

Thanks EWishdahl

bcp import

I'd like to bcp import a file that sometimes misses the last column/s. There's an EOL character instead. For some reason, bcp wraps around, ignoring the EOL character, and continues reading from the next row of the file. Instead, I'd like to replace the missing columns by null.

I've tried using bcp, bulk insert and the DTS Wizard. So far, I've only been succesfull using the DTS Wizard. I also do some other bcp imports, so I'd like to stick with bcp.

bcp table1 in myFile.csv -SServer1 -T -c -k -t\t -r\n

myFile.csv looks like... (replaced tabs by ;)
Col001;Col002;Col003;Col004
Col001;Col002
Col001;Col002;Col003
Col001;Col002;Col003;Col004
etc

are imported as:
Col001;Col002;Col003;Col004
Col001;Col002;Col001;Col002
Col001;Col002;Col003;Col001
etc

Any suggestions?if you want to insert null for column3 and column 4 then only specify the separator
Col001;Col002;Col003;Col004
Col001;Col002;;
Col001;Col002;Col003
Col001;Col002;Col003;Col004|||Not specifing the row terminator doesn't make a difference. bcp still regards the \n as just another character to import, at least, on my system.|||...it sounds like the terminator in your file is the problem...on what system was the input file generated? (unix?) This might cause new lines to be unreadable on Windows...|||The file has lf as row-terminator, but I guess it wont be a problem changing that into cr/lf. The lf is readable for those rows that match the layout of the table. It's the rows that don't match that are wrapped. As far as I can see now, either the file will have to include empty values instead of nothing or I'll have to divide the proces in smaller processes.