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

2012年3月27日星期二

bcp within batch file

I have a windows batch file that executes a SQL Server bcp command. I
would like to obtain a return code if the bcp command fails. However,
I cannot seem to find the return code (if any) for bcp. For example,
if the bcp command is improperly formatted, or has a bad password, I
want the batch file to return an error. Right now, my batch file
simply executes and returns success, even when the bcp command fails.
Has anyone run into this before?

Thanks!DBA (kaylisse@.yahoo.com) writes:
> I have a windows batch file that executes a SQL Server bcp command. I
> would like to obtain a return code if the bcp command fails. However,
> I cannot seem to find the return code (if any) for bcp. For example,
> if the bcp command is improperly formatted, or has a bad password, I
> want the batch file to return an error. Right now, my batch file
> simply executes and returns success, even when the bcp command fails.
> Has anyone run into this before?

The return status for a program called from a batch file is in
%ERRORLEVEL%, so this is the variable you should check.

I seem to recall that BCP does not always set this variable as one
may desire. It does set it, if the password is wrong. But I believe
it does not set %errorlevel% if some rows does not load.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Looks like most interesting bcp errors will set %errorlevel% to 1. An empty
input file, however, doesn't set the errorlevel. You can take action in a
..CMD file like this:

bcp <table> [in|out] <filespec> [switches]
if %errorlevel% 1 goto <label>
<normal processing steps here
:<label> echo something BAD happened to your BCP!
<steps to do something about it here
I didn't test it but I believe if you set the maxerrors switch, you won't
get the non-zero errorlevel unless you actually exceed that threshold. You
might want to test this yourself.

FYI - in our .CMD scripts, if I want to simply fail the job after the error,
I usually do this:

bcp <stuff>
if %errorlevel% 1 goto BCP_FAILED

and I don't bother using a BCP_FAILED label anywhere. Searching for it, the
job runs right past the end and aborts. You'll see a message saying "Can't
find label BCP_FAILED" or something similar as part of the job status report
if you run this through SQL Executive and, by convention here, that's the
diagnostic for the job.

"DBA" <kaylisse@.yahoo.com> wrote in message
news:ffe01bb8.0407151237.39fbef2c@.posting.google.c om...
> I have a windows batch file that executes a SQL Server bcp command. I
> would like to obtain a return code if the bcp command fails. However,
> I cannot seem to find the return code (if any) for bcp. For example,
> if the bcp command is improperly formatted, or has a bad password, I
> want the batch file to return an error. Right now, my batch file
> simply executes and returns success, even when the bcp command fails.
> Has anyone run into this before?
> Thanks!|||Hi

%ERRORLEVEL% will be 0 when a succesful import has been performed. If it
fails then it will return 1 (on my tests!).

John

"DBA" <kaylisse@.yahoo.com> wrote in message
news:ffe01bb8.0407151237.39fbef2c@.posting.google.c om...
> I have a windows batch file that executes a SQL Server bcp command. I
> would like to obtain a return code if the bcp command fails. However,
> I cannot seem to find the return code (if any) for bcp. For example,
> if the bcp command is improperly formatted, or has a bad password, I
> want the batch file to return an error. Right now, my batch file
> simply executes and returns success, even when the bcp command fails.
> Has anyone run into this before?
> Thanks!

Bcp utility

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

2012年2月16日星期四

BC30260 Error

I am getting this error that says "Natvie compiler return value [BC30260] 'EH_PID is declared as 'Class EH_PID' in this class."

Does anyone know what this means and how I can fix it?

Nevermind, for some reason when I changed the name of the Dataset it works fine.

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 SQL Query (Select Records Added Today)

Hi,
I feel stupid for posting this question but I cannot find out how to create an SQL statement to return records that have been added today.
My database table has a field called calldate which hold the date and time of the record added to the database i.e. "02/08/2005 16:55:41".
Please can someone let me know how I can search to find all records added today. I thought it would be something like it, but I assume I need to format the calldate so it's just 02/08/2005.
Select *
From Table
Where CallDate = GetDate()
Thanks
BrettYou may try something as:
SELECT
*
FROM
Table
WHERE
(
(DAY(CallDate) = DAY(GETDATE())
AND
(YEAR(CallDate) = YEAR(GETDATE())
)
Hope this works for you
Regards|||

you may want to use the month too , as the day number and year may match but not the month:

SELECT
*
FROM
Table
WHERE
(
(DAY(CallDate) = DAY(GETDATE())
AND
(MONTH(CallDate) = MONTH(GETDATE())
AND
(YEAR(CallDate) = YEAR(GETDATE())
)

|||I missed that, sorry.
Regards
|||I tried the code but it does not work, any other ideas?|||What do you mean it didn't work? the code is very simple and clear, tell us what the main problem is with this code.
regards
|||The error I receive is :-
Incorrect syntax near ')'.
Any idea's?
Thanks
Brett|||Problem now resolved
Select * from tbl
WHERE
(
DAY(CallDate) = DAY(GETDATE())
AND
MONTH(CallDate) = MONTH(GETDATE())
AND
YEAR(CallDate) = YEAR(GETDATE())
)
Thanks for your help,
Brett|||In fact you are right, I just noticed that.
Regards
|||I'd simplify it by using the DATEDIFF function:
SELECT * FROM tbl WHERE DATEDIFF(d,CallDate,GETDATE()) = 0
But for better performance, this would be preferable:
DECLARE @.startDate datetime, @.endDate datetime
SELECT @.startDate = CONVERT(char(8), GETDATE(),112), @.endDate = CONVERT(char(8), DATEADD(d,1,GETDATE()),112)
SELECT * FROM tbl WHERE CallDate >= @.startDate AND CallDate < @.endDate

The second option would perform better because a function is notperformed on each column of the table. Note that I am setting@.startDate equal to midnight of the current date (this is the defaultif a time is not specified), and I am setting @.endDate to midnight oftomorrow.