Sql Basic Security: date

显示标签为“date”的博文。显示所有博文

2012年3月29日星期四

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
>
>

2012年3月27日星期二

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
> > >
> > >
> >
> >
>

2012年3月25日星期日

BCP template using quoted text qualifiers

I'm dumping data from a table via BCP and when BCPing them back in to another table, it errors out on numeric and date fields. I'd like to place quote marks on the text fields. How do I do this using BCP?I'd say the other table does not match your structure of the first table

Where's the bcp command and the DDL of the tables.|||The tables are exactly the same. They are duplicates of one another just in different databases.
When I tried to manually import the file that that was exported using BCP I got the error stating that I was trying to put a string into a numeric field. When I exported the table manually with quotes, I had no problem importing it in to the other table.
Do you know how to export a table with the quotes around the text in the file using bcp?|||You can either:
- create a view or;
- create a procedure.

Within either you can embed any character to be part of the data you're trying to export. If it's a view, - use OUT, else - QUERYOUT.|||So there is no switch with the bcp command to put in quotes?sql

2012年3月19日星期一

bcp invalid date format

Hi,
I have problem in BCP utility while exporting data to sql server.
It works in my system and my friend's system , but it gives the
following when its run in production
server.
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
BCP copy in failed
there are about 365000 records in the data file...
the date values go like below;
5/30/2007 9:27:35 AM
5/30/2007 9:28:37 AM
5/30/2007 5:28:16 PM
5/30/2007 5:28:32 PM
5/30/2007 5:28:16 PM
please help me for this problem...
thanks,
ThiruHi
/*
Kelly,Reynold,kelly@.reynold.com,5/30/2007 9:27:35 AM
John,Smith,bill@.smith.com,5/30/2007 9:27:35 AM
Sara,Parker,sara@.parker.com,5/30/2007 9:27:35 AM
*/
Make a column for dates as VARCHAR(50) and try insert
CREATE TABLE Tmp
(
stFName varchar (50) NOT NULL,
stLName varchar (50) NOT NULL,
stEmail varchar (100) NOT NULL,
dt varchar (100)
)
BULK INSERT tmp FROM 'c:\myfile.txt' WITH (FIELDTERMINATOR = ',')
SELECT * FROM Tmp
Later on you can convert VARCHAR to DATETIME datatype
"Thiru.net" <thirudotnet@.gmail.com> wrote in message
news:1193915642.828573.240090@.q5g2000prf.googlegroups.com...
> Hi,
> I have problem in BCP utility while exporting data to sql server.
> It works in my system and my friend's system , but it gives the
> following when its run in production
> server.
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> BCP copy in failed
> there are about 365000 records in the data file...
> the date values go like below;
> 5/30/2007 9:27:35 AM
> 5/30/2007 9:28:37 AM
> 5/30/2007 5:28:16 PM
> 5/30/2007 5:28:32 PM
> 5/30/2007 5:28:16 PM
> please help me for this problem...
> thanks,
> Thiru
>|||On Nov 1, 4:47 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> /*
> Kelly,Reynold,ke...@.reynold.com,5/30/2007 9:27:35 AM
> John,Smith,b...@.smith.com,5/30/2007 9:27:35 AM
> Sara,Parker,s...@.parker.com,5/30/2007 9:27:35 AM
> */
> Make a column for dates as VARCHAR(50) and try insert
> CREATE TABLE Tmp
> (
> stFName varchar (50) NOT NULL,
> stLName varchar (50) NOT NULL,
> stEmail varchar (100) NOT NULL,
> dt varchar (100)
> )
> BULK INSERT tmp FROM 'c:\myfile.txt' WITH (FIELDTERMINATOR = ',')
> SELECT * FROM Tmp
> Later on you can convert VARCHAR to DATETIME datatype
> "Thiru.net" <thirudot...@.gmail.com> wrote in message
> news:1193915642.828573.240090@.q5g2000prf.googlegroups.com...
>
> > Hi,
> > I have problem in BCP utility while exporting data to sql server.
> > It works in my system and my friend's system , but it gives the
> > following when its run in production
> > server.
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > BCP copy in failed
> > there are about 365000 records in the data file...
> > the date values go like below;
> > 5/30/2007 9:27:35 AM
> > 5/30/2007 9:28:37 AM
> > 5/30/2007 5:28:16 PM
> > 5/30/2007 5:28:32 PM
> > 5/30/2007 5:28:16 PM
> > please help me for this problem...
> > thanks,
> > Thiru- Hide quoted text -
> - Show quoted text -
Hi,
Thanks for your reply, actually I am using bcp command-lind utility
here. I cant change the datatype in the production server..
Thanks,
Thiru

BCP invalid date

2012年3月8日星期四

BCP Exporting Duplicate Rows

Hi everyone.
I am BCP-ing out data from one table into separate files based on a date
range. Each file has anywhere from 15 to 30 million rows. The source table
has a primary key, and I have verified that there is no duplicate rows. I
have also double checked the date ranges and they are good.
I then bcp the data back into another empty table, with the same primary key
as the source table. But I get a primary key violation error. I then bcp-ed
the data into a temporary table with no primary key. Then I ran a query to
check for duplicates. And sure enough, there were over 300K duplicate rows.
Has anybody seen this kind of behavior with BCP?
OS: Window Server 2003 R2, Standard - SP2.
SQL: 2005 Enterprise, SP2
Thank you!
J> Has anybody seen this kind of behavior with BCP?
I haven't run into this. Can you provide more details on the BCP command
you are using (i.e. out, queryout)?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
news:3E192B68-C642-4B34-A89A-9013C4FCC328@.microsoft.com...
> Hi everyone.
> I am BCP-ing out data from one table into separate files based on a date
> range. Each file has anywhere from 15 to 30 million rows. The source table
> has a primary key, and I have verified that there is no duplicate rows. I
> have also double checked the date ranges and they are good.
> I then bcp the data back into another empty table, with the same primary
> key
> as the source table. But I get a primary key violation error. I then
> bcp-ed
> the data into a temporary table with no primary key. Then I ran a query to
> check for duplicates. And sure enough, there were over 300K duplicate
> rows.
> Has anybody seen this kind of behavior with BCP?
> OS: Window Server 2003 R2, Standard - SP2.
> SQL: 2005 Enterprise, SP2
> Thank you!
> J|||My BCP statement is pretty simple. I am using queryout to select data from
date ranges. Here's the string:
bcp "select * from MARS.dbo.CardTransaction (nolock) where TRANSACTIONTIME
between '2006-10-02 05:00:00.001' and '2006-11-02 05:00:00.000'" queryout
"Q:\PartitionBCPFiles\CardTransaction2006_1102.nat" -e
"Q:\PartitionBCPFiles\bcperrors_2006_1102.txt" -n -SS-MARSDBPRD01\PRD01 -T
-a8192
Thanks!
"Dan Guzman" wrote:
> > Has anybody seen this kind of behavior with BCP?
> I haven't run into this. Can you provide more details on the BCP command
> you are using (i.e. out, queryout)?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
> news:3E192B68-C642-4B34-A89A-9013C4FCC328@.microsoft.com...
> > Hi everyone.
> > I am BCP-ing out data from one table into separate files based on a date
> > range. Each file has anywhere from 15 to 30 million rows. The source table
> > has a primary key, and I have verified that there is no duplicate rows. I
> > have also double checked the date ranges and they are good.
> > I then bcp the data back into another empty table, with the same primary
> > key
> > as the source table. But I get a primary key violation error. I then
> > bcp-ed
> > the data into a temporary table with no primary key. Then I ran a query to
> > check for duplicates. And sure enough, there were over 300K duplicate
> > rows.
> > Has anybody seen this kind of behavior with BCP?
> >
> > OS: Window Server 2003 R2, Standard - SP2.
> > SQL: 2005 Enterprise, SP2
> >
> > Thank you!
> >
> > J
>|||If the table is updated during a scan with the NOLOCK hint (or READ
UNCOMMITTED transaction isolation level), rows may be skipped or duplicated
and you might get uncommitted data. This issue applies to any READ
UNCOMMITTED query, not just BCP.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
news:78C37AA0-C36C-4C47-8210-A3069B975382@.microsoft.com...
> My BCP statement is pretty simple. I am using queryout to select data from
> date ranges. Here's the string:
> bcp "select * from MARS.dbo.CardTransaction (nolock) where TRANSACTIONTIME
> between '2006-10-02 05:00:00.001' and '2006-11-02 05:00:00.000'" queryout
> "Q:\PartitionBCPFiles\CardTransaction2006_1102.nat" -e
> "Q:\PartitionBCPFiles\bcperrors_2006_1102.txt" -n -SS-MARSDBPRD01\PRD01 -T
> -a8192
> Thanks!
> "Dan Guzman" wrote:
>> > Has anybody seen this kind of behavior with BCP?
>> I haven't run into this. Can you provide more details on the BCP command
>> you are using (i.e. out, queryout)?
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
>> news:3E192B68-C642-4B34-A89A-9013C4FCC328@.microsoft.com...
>> > Hi everyone.
>> > I am BCP-ing out data from one table into separate files based on a
>> > date
>> > range. Each file has anywhere from 15 to 30 million rows. The source
>> > table
>> > has a primary key, and I have verified that there is no duplicate rows.
>> > I
>> > have also double checked the date ranges and they are good.
>> > I then bcp the data back into another empty table, with the same
>> > primary
>> > key
>> > as the source table. But I get a primary key violation error. I then
>> > bcp-ed
>> > the data into a temporary table with no primary key. Then I ran a query
>> > to
>> > check for duplicates. And sure enough, there were over 300K duplicate
>> > rows.
>> > Has anybody seen this kind of behavior with BCP?
>> >
>> > OS: Window Server 2003 R2, Standard - SP2.
>> > SQL: 2005 Enterprise, SP2
>> >
>> > Thank you!
>> >
>> > J|||Thanks for the reply.
This table is not updated, it's static data. I tried the same query with an
"insert into ... select" command, and it worked fine.
"Dan Guzman" wrote:
> If the table is updated during a scan with the NOLOCK hint (or READ
> UNCOMMITTED transaction isolation level), rows may be skipped or duplicated
> and you might get uncommitted data. This issue applies to any READ
> UNCOMMITTED query, not just BCP.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
> news:78C37AA0-C36C-4C47-8210-A3069B975382@.microsoft.com...
> > My BCP statement is pretty simple. I am using queryout to select data from
> > date ranges. Here's the string:
> > bcp "select * from MARS.dbo.CardTransaction (nolock) where TRANSACTIONTIME
> > between '2006-10-02 05:00:00.001' and '2006-11-02 05:00:00.000'" queryout
> > "Q:\PartitionBCPFiles\CardTransaction2006_1102.nat" -e
> > "Q:\PartitionBCPFiles\bcperrors_2006_1102.txt" -n -SS-MARSDBPRD01\PRD01 -T
> > -a8192
> >
> > Thanks!
> >
> > "Dan Guzman" wrote:
> >
> >> > Has anybody seen this kind of behavior with BCP?
> >>
> >> I haven't run into this. Can you provide more details on the BCP command
> >> you are using (i.e. out, queryout)?
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
> >> news:3E192B68-C642-4B34-A89A-9013C4FCC328@.microsoft.com...
> >> > Hi everyone.
> >> > I am BCP-ing out data from one table into separate files based on a
> >> > date
> >> > range. Each file has anywhere from 15 to 30 million rows. The source
> >> > table
> >> > has a primary key, and I have verified that there is no duplicate rows.
> >> > I
> >> > have also double checked the date ranges and they are good.
> >> > I then bcp the data back into another empty table, with the same
> >> > primary
> >> > key
> >> > as the source table. But I get a primary key violation error. I then
> >> > bcp-ed
> >> > the data into a temporary table with no primary key. Then I ran a query
> >> > to
> >> > check for duplicates. And sure enough, there were over 300K duplicate
> >> > rows.
> >> > Has anybody seen this kind of behavior with BCP?
> >> >
> >> > OS: Window Server 2003 R2, Standard - SP2.
> >> > SQL: 2005 Enterprise, SP2
> >> >
> >> > Thank you!
> >> >
> >> > J
> >>
>|||The only explanation I can think of is that the IAM pages (scanned due to
NOLOCK hint) are out-of-sync with the linked list (used with default READ
COMMITTED). Did you try the BCP queryout without the NOLOCK hint? You
might try running a DBCC CHECKDB.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
news:9875560F-42C7-46F8-ABF4-44C10C76B310@.microsoft.com...
> Thanks for the reply.
> This table is not updated, it's static data. I tried the same query with
> an
> "insert into ... select" command, and it worked fine.
>
> "Dan Guzman" wrote:
>> If the table is updated during a scan with the NOLOCK hint (or READ
>> UNCOMMITTED transaction isolation level), rows may be skipped or
>> duplicated
>> and you might get uncommitted data. This issue applies to any READ
>> UNCOMMITTED query, not just BCP.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
>> news:78C37AA0-C36C-4C47-8210-A3069B975382@.microsoft.com...
>> > My BCP statement is pretty simple. I am using queryout to select data
>> > from
>> > date ranges. Here's the string:
>> > bcp "select * from MARS.dbo.CardTransaction (nolock) where
>> > TRANSACTIONTIME
>> > between '2006-10-02 05:00:00.001' and '2006-11-02 05:00:00.000'"
>> > queryout
>> > "Q:\PartitionBCPFiles\CardTransaction2006_1102.nat" -e
>> > "Q:\PartitionBCPFiles\bcperrors_2006_1102.txt" -n -SS-MARSDBPRD01\PRD01
>> > -T
>> > -a8192
>> >
>> > Thanks!
>> >
>> > "Dan Guzman" wrote:
>> >
>> >> > Has anybody seen this kind of behavior with BCP?
>> >>
>> >> I haven't run into this. Can you provide more details on the BCP
>> >> command
>> >> you are using (i.e. out, queryout)?
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
>> >> news:3E192B68-C642-4B34-A89A-9013C4FCC328@.microsoft.com...
>> >> > Hi everyone.
>> >> > I am BCP-ing out data from one table into separate files based on a
>> >> > date
>> >> > range. Each file has anywhere from 15 to 30 million rows. The source
>> >> > table
>> >> > has a primary key, and I have verified that there is no duplicate
>> >> > rows.
>> >> > I
>> >> > have also double checked the date ranges and they are good.
>> >> > I then bcp the data back into another empty table, with the same
>> >> > primary
>> >> > key
>> >> > as the source table. But I get a primary key violation error. I then
>> >> > bcp-ed
>> >> > the data into a temporary table with no primary key. Then I ran a
>> >> > query
>> >> > to
>> >> > check for duplicates. And sure enough, there were over 300K
>> >> > duplicate
>> >> > rows.
>> >> > Has anybody seen this kind of behavior with BCP?
>> >> >
>> >> > OS: Window Server 2003 R2, Standard - SP2.
>> >> > SQL: 2005 Enterprise, SP2
>> >> >
>> >> > Thank you!
>> >> >
>> >> > J
>> >>

bcp export stored procedure with a date in the statement

Please can someone help me with the statement below. I am trying to export, via bcp a stored procedure which requires two dates and cannot seem to work out the correct way of typing it into the statement. I know that the dates are meant to have an ' around them but cant work out how to get this concatenated correctly.

Any help would be appreciated.

Paul

select @.sql = 'bcp "Exec CHC_Data_V2..TestSP 05/01/07, 01/01/07" queryout "c:\entitytext.txt" -SAJR\SQLEXPRESS -T -c -t'
exec master..xp_cmdshell @.sq

use the following query...

Code Snippet

declare @.sql as varchar(1000)

select @.sql = 'bcp "Exec CHC_Data_V2..TestSP ''05/01/07'', ''01/01/07''" queryout "c:\entitytext.txt" -SAJR\SQLEXPRESS -T -c -t'

exec master..xp_cmdshell @.sql

|||Thanks very much for your help

2012年2月25日星期六

BCP call to stored procedure - broke during upgrade from SQL 7.0 to 2000

I have this stored procedure that takes a few parameters like date and
merchant ID, and basically goes through a set of if-then statements to build
a SQL SELECT string.

When we upgraded from SQL Server 7.0 to 2000, the stored procedure still
worked from Query Analyzer, but not in BCP. It used to work in BCP just
fine with 7.0. The error I get now is:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain
at least one column

What's really strange is, if I instruct the stored procedure to simply print
the SELECT string, then cut-and-paste it into the end of stored procedure
code (assigning it to the variable that already contains the SELECT string),
then it works from BCP.

Any help would be greatly appreciated.

AstonAston (alau@.selera.com) writes:
> I have this stored procedure that takes a few parameters like date and
> merchant ID, and basically goes through a set of if-then statements to
> build a SQL SELECT string.
> When we upgraded from SQL Server 7.0 to 2000, the stored procedure still
> worked from Query Analyzer, but not in BCP. It used to work in BCP just
> fine with 7.0. The error I get now is:
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain
> at least one column
> What's really strange is, if I instruct the stored procedure to simply
> print the SELECT string, then cut-and-paste it into the end of stored
> procedure code (assigning it to the variable that already contains the
> SELECT string), then it works from BCP.

If I understand this right you are doing something like:

bcp "exec some_db..some_sp" queryout datafile.bcp -c -T

To find out what columns there are in the query, bcp first submits the
query with SET FMTONLY ON. This command is causes SQL Server to not execute
the statements in the procedure, but return data about any result sets
it finds. However, if you produces a dynamic SQL string and executes it,
there not be anyting executed with FMTONLY ON, and BCP will not find any
result set.

Why this worked in SQL 7, I don't know. (I never worked much with SQL 7,
jumped direct to SQL 2000 from 6.5.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

2012年2月23日星期四

BCP and Date Problems

Hi,

We have a job that, every night, imports mail logs into SQL Server for processing. We receive these mail logs from the mail group, so we have no say in how the log should be formatted, basically we are given what we are given.

Our problem started occurring on Oct-01, and has occured every day since then.

The log file may contain rows with dates such as follows (don't ask me why there isn't a consistant format)

1) 2003-10-01 00:18:5
2) 2003-9-01 00:8:6
3) 2003-9-1 00:9:6
4) 2003-9-1 0:09:6

5) 2003-10-1 00:18:6
6) 2003-10-01 0:19:6
7) 2003-10-01 00:8:6

Now, 1-4 import. 5-7 don't.

From what I can see.

If the month is a single digit month, i.e. September above, then it will import basically all formats (e.g. 1 digit for the hour, 1 digit for the minute or 1 digit for the day).

If the month is a double digit month, i.e. October above, then it will not import unless the date is perfectly formatted (e.g. 2 digits for the hour AND 2 digits for the minute AND 2 digits for day).

An excerpt from the format file we are using is listed below.

Why does SQL Server allow 'bad' dates with single digit months, but not double digit months? It's really confusing me. I know the obvious solution, tell the Mail guys to properly format their files, but are there any other solutions?

Thanks heaps. Hope I've asked this clearly.

Format File
----
8.0
11
1 SQLCHAR 0 0 "" 0 TBL_MAIL_LOG_ID ""
2 SQLCHAR 0 0 "" 0 LOG_FILE_ID ""
3 SQLCHAR 0 1 " " 3 DATE ""

The last line reads (broken into individual lines below)

3
SQLCHAR
0
1
" " <-- there is a space in there
3
DATE
""Howdy

Sadly it appears its a quirk in SQL - unless a '1' is an '01' it seems to get its knickers in a knot, especially at the start of a column of information.

Try importing the raw data into another table that uses CHAR instead of datetime for the dates, then add the '0' to any dates required, then transfer across to final table. Its time consuming, but in the long run more robust.

Cheers

SG

BCP / Bulk Insert Date Problems,

Hi,
I am having problems getting BCP/Bulk Insert to insert
dates properly.
The dates are being presented to us as,
dd/MM/yyyy hh:mm:ss
however, when importing into SQL Server as a datetime
field via bcp/bulk insert, they are being inserted as
MM/dd/yyyy hh:mm:ss
In Australia, Windows region date setting set correctly
and using Latin1_General_CI_AS collation.
Using the following format file (just the relevant sample
from the file),
8.0
9
1 SQLCHAR 0 17 "," 1 startDateTime Latin1_General_CI_AS
I tried using
1 SQLDATETIME 0 17 "," 1 startDateTime ""
however this did not work.
Please let me know if any additional info needed.To circumvent the problem load all data into a Holding Table, load the date
data into a varchar column. Then INSERT the data into it's destination with
a select statement that CONVERTs the varchar dates into datetime. Specify
the additional STYLE parameter in your CONVERT to ensure you capture the
dates correctly, i beleive dd/mm/yyyy is STYLE 103.
HTH
Ryan Waight, MCDBA, MCSE
"Mallen" <mallen@.nospam.bigpond.net.au> wrote in message
news:04ef01c3a981$07cd3470$a501280a@.phx.gbl...
> Hi,
> I am having problems getting BCP/Bulk Insert to insert
> dates properly.
> The dates are being presented to us as,
> dd/MM/yyyy hh:mm:ss
> however, when importing into SQL Server as a datetime
> field via bcp/bulk insert, they are being inserted as
> MM/dd/yyyy hh:mm:ss
> In Australia, Windows region date setting set correctly
> and using Latin1_General_CI_AS collation.
> Using the following format file (just the relevant sample
> from the file),
> 8.0
> 9
> 1 SQLCHAR 0 17 "," 1 startDateTime Latin1_General_CI_AS
> I tried using
> 1 SQLDATETIME 0 17 "," 1 startDateTime ""
> however this did not work.
> Please let me know if any additional info needed.

2012年2月13日星期一

batch file to copy file and append date

The Sql Server database can only see the local drive.

I would like to set up a batch file that will copy a SQL Server

backup file from the local drive to the network drive. I would

like to append the file date to the end of the copied file. I

assume a batch file can accomplish this but I am new to batch

file writing. Does anyone have code that they already created

for this sort of task??

Thank you!You can easily do this sort of stuff with a VBScript batch file. See the FileSystemObject. It's a great tool for moving and renaming files.|||How do you plan to do this?

From the context of xp_cmdshell it would run under the sql server service account, which would need write to write to the network drive.

I've heard of people using robocopy...what do you want to use? ftp?

Just a plain copy command?

And from where a sproc, SQL Server agent Job?|||How do you plan to do this?

From the context of xp_cmdshell it would run under the sql server service account, which would need write to write to the network drive.

I've heard of people using robocopy...what do you want to use? ftp?

Just a plain copy command?

And from where a sproc, SQL Server agent Job?

I just want a plain copy within a batch file, but one that can append the file date (ie BackupFile_mm_dd_yyyy). Currently this copy has been set up within windows Task scheduler. I want to do the same but run the batch file in the task. Fyi: Because SQL Server cannot see the network, the task was set up with Windows tasks scheduler.

Let me know your thoughts..

Thank you.|||You can easily do this sort of stuff with a VBScript batch file. See the FileSystemObject. It's a great tool for moving and renaming files.

Thank you for your suggestion. I have not worked with VB Script so was hoping to accomplish this within a batch file. But thank you for your response. I will look into this as another option.|||If you are using XP or Windows 2003, and N:\networkpath\ is where you want the files to go, then you might try a batch file like:copy %1 "n:\networkpath\%~1 %~t1"-PatP|||If you are using XP or Windows 2003, and N:\networkpath\ is where you want the files to go, then you might try a batch file like:copy %1 "n:\networkpath\%~1 %~t1"-PatP

Can you please explain the %1 and %~t1, will this append the file date?
Thanks!|||Go to the Windows XP help, and enter "Using batch parameters" (please include the quotation marks). It has a full explaination.

-PatP|||If you are using XP or Windows 2003, and N:\networkpath\ is where you want the files to go, then you might try a batch file like:copy %1 "n:\networkpath\%~1 %~t1"-PatPIf the destination file doesn't exist your trick will not work. If it does exist the copy will result in "The syntax of the command is incorrect" error. The best place to generate the new filename would be in T-SQL.|||I have a job which save databases structure into files. It create directory 'year-month-day' and dump structure there. This is code:
----------
DECLARE @.command varchar(1000);
-- create local directory for dump
SET @.command='mkdir C:\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar);
exec xp_cmdshell @.command;

--create dump
SET @.command='xp_cmdshell ''"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s TESTERS /d ? /P 1 /f C:\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar)+'\?.sql''';
exec sp_MSforeachdb
@.command1 = @.command,
@.replacechar = '?'

--create remote dir
SET @.command='mkdir \\saturn\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar);
exec xp_cmdshell @.command;

--copy databases structure to remote computer
SET @.command='copy '+'C:\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar)+' \\saturn\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar);
exec xp_cmdshell @.command;
----------
I hope this help you|||If the destination file doesn't exist your trick will not work. If it does exist the copy will result in "The syntax of the command is incorrect" error. The best place to generate the new filename would be in T-SQL.
Can you suggest how to append mmddyyyy to end of filename in a backup command within a SQL SERVER job?|||If you are using XP or Windows 2003, and N:\networkpath\ is where you want the files to go, then you might try a batch file like:copy %1 "n:\networkpath\%~1 %~t1"-PatP
The results is close but beacuse of the slashes in the date, the output is going into subdirectories not a single filename. Any suggestions?|||I figured out how to copy a file and append a date to the copy. Here is the code for the batch file:

:: COPY FILE AND DATE
::
@.ECHO OFF

FOR /F "tokens=2,3,4 delims=/ " %%a IN ('DATE /t') DO SET mydate=_%%a_%%b_%%c

::ECHO The value is "%mydate%"
copy "c:\readme" "L:\DATABASES\PROJECT REVIEW\readme%mydate%" /Y|||I am sure you did, and so did I, after reading this (http://www.computerhope.com/batch.htm#5)and similar pages ;)

2012年2月9日星期四

Basic SQL Query Question....

Can someone please tell me how I do the following...
I have a variable @.Date which is a year... EG: 2005 what I want to be able
to do is return all the records found in a table which match the @.Date
variable!
E.G:
IF @.Date = 2005
All the records added to the database which match the year 2005 from the
field DateAdded are returned!
See example below!
Thanks for any help!
CREATE PROCEDURE dbo.GetVVIssue
(
@.FileDescription nvarchar (255),
@.Date Datetime
)
AS
SELECT fileID, FileName, FileDescription, DateAdded
FROM
tblFiles
WHERE FileDescription = @.FileDescription AND DateAdded = @.Date
ORDER BY DateAdded ASC
GOTim
What is a datatype of the Dateadded column?
If you store into a variable @.date only a year why you are defined it as
datetime
declare @.date int --or char(4)
SELECT fileID, FileName, FileDescription, DateAdded
FROM
tblFiles
WHERE FileDescription = @.FileDescription AND DateAdded = @.Date
ORDER BY DateAdded ASC
"Tim::.." <myatix_at_hotmail.com> wrote in message
news:BF72BABE-2F2E-4CE4-87A2-F015CE3C67E9@.microsoft.com...
> Can someone please tell me how I do the following...
> I have a variable @.Date which is a year... EG: 2005 what I want to be able
> to do is return all the records found in a table which match the @.Date
> variable!
> E.G:
> IF @.Date = 2005
> All the records added to the database which match the year 2005 from the
> field DateAdded are returned!
> See example below!
> Thanks for any help!
>
> CREATE PROCEDURE dbo.GetVVIssue
> (
> @.FileDescription nvarchar (255),
> @.Date Datetime
> )
> AS
> SELECT fileID, FileName, FileDescription, DateAdded
> FROM
> tblFiles
> WHERE FileDescription = @.FileDescription AND DateAdded = @.Date
> ORDER BY DateAdded ASC
> GO
>|||DateAdded has a datatype of DateTime!
But how do I return all the records found in a table which match the @.Date
variable in the DateAdded column?
Thanks
"Uri Dimant" wrote:

> Tim
> What is a datatype of the Dateadded column?
> If you store into a variable @.date only a year why you are defined it as
> datetime
> declare @.date int --or char(4)
> SELECT fileID, FileName, FileDescription, DateAdded
> FROM
> tblFiles
> WHERE FileDescription = @.FileDescription AND DateAdded = @.Date
> ORDER BY DateAdded ASC
>
>
> "Tim::.." <myatix_at_hotmail.com> wrote in message
> news:BF72BABE-2F2E-4CE4-87A2-F015CE3C67E9@.microsoft.com...
>
>|||Tim
SELECT fileID, FileName, FileDescription, DateAdded
FROM
tblFiles
WHERE FileDescription = @.FileDescription AND YEAR(DateAdded) = YEAR(@.Date)
ORDER BY DateAdded ASC
Note: An optimizer won't use an index on DateAdded column.
"Tim::.." <myatix_at_hotmail.com> wrote in message
news:66F29AF7-64A0-49BD-AEC2-440F91648DB7@.microsoft.com...
> DateAdded has a datatype of DateTime!
> But how do I return all the records found in a table which match the @.Date
> variable in the DateAdded column?
> Thanks
>
> "Uri Dimant" wrote:
>
able
the|||In your example @.date is a DATETIME not a numeric so it cannot be equal
to the value 2005. It could be 2005-01-01 00:00:00.000 or 2005-12-31
00:00:00.000 or some other date but if you try to pass it the numeric
value 2005 then @.date will actually take the value of a date in 1905
because the numeric is interpreted as X number of days since
1900-01-01.
What you need is a range query, such as:
WHERE dateadded >= '20050101'
AND dateadded < '20060101'
You could parameterize this by passing the date as '20050101':
WHERE dateadded >= @.year_start_date
AND dateadded < DATEADD(YEAR,1,@.year_start_date)
or you could obviously pass both the start and end dates as parameters.
If you really eanted to pass just a numeric value then change the
parameter to a numeric (@.year) and try this:
WHERE dateadded >= DATEADD(YEAR,@.year-2000,'20000101')
AND dateadded < DATEADD(YEAR,@.year-1999,'20000101')
Hope this helps.
David Portas
SQL Server MVP
--

Basic select statement question

If I want to select certain values from a table where date is equal to something and the second field is null,would I go.

select * from table where date = 'something' and Sent = null or something else because if I do it this way I get nothing as a result but if I scroll down I can see that I have null values.

thanksNULL is a tricky beast, because nothing ever equals NULL, not even NULL itself. You need to modify your statement slightly to use a semantically different test, like:SELECT *
FROM table
WHERE date = 'something'
AND Sent IS nullThis seems like a trivial difference, and from a coding perspective it is. From the logical perspective however, the difference is huge.

-PatP|||Ooh,that made all the difference, I always say, it is easy once you know how to do it.
Thanks for you help once again|||Since you're dealing with this, you might want to look up ANSI_NULLS in Book Online and memorize it. :) It will save you a lot of pain down the road. Make sure you use SET ANSI_NULLS ON when creating tables and procedures.|||Will do so,thanks

订阅：博文 (Atom)