Job to be run ever first for the previous calendar month

Hi!

I have a query that has to return bunch of data based on the calendar
month. I have to make sure that it will return data to me for 28 days
if it is February and for 31 if it is August(for example). I need to
be able to execute it every first of every month for the past 30, 31 or
28 days based on the calendar month. Is there a function or a stored
procedure that I can use to do that?

Thank you,
T.tolcis,

There might be a more elegant way, but this should work:

declare @.Now datetime
declare @.StartDate datetime
declare @.EndDate datetime

set @.Now = getdate()
if datepart(d, @.Now) = 1 -- only execute if it is the first day of the month
begin
-- to get start date, subtract one month from the date and remove the
time from the date
set @.StartDate = convert(varchar(15), dateadd (m, -1, @.now), 112)
-- to get end date, remove time from date
set @.EndDate = dateadd(m, 1, @.StartDate)
print cast(@.StartDate as char(25)) + cast(@.EndDate as char(25))
-- execute your code here using >= @.StartDate and < @.EndDate
end

-- Bill

"tolcis" <nytollydba@.gmail.comwrote in message
news:1169073028.098703.235980@.11g2000cwr.googlegro ups.com...

Quote:

Originally Posted by

Hi!
>
I have a query that has to return bunch of data based on the calendar
month. I have to make sure that it will return data to me for 28 days
if it is February and for 31 if it is August(for example). I need to
be able to execute it every first of every month for the past 30, 31 or
28 days based on the calendar month. Is there a function or a stored
procedure that I can use to do that?
>
Thank you,
T.
>

|||WHERE create_date < DATEADD(month,DATEDIFF(month, 0,getdate()),0)
AND create_date >= DATEADD(month,DATEDIFF(month, 0,getdate())-1,0)

Roy Harvey
Beacon Falls, CT

On 17 Jan 2007 14:30:29 -0800, "tolcis" <nytollydba@.gmail.comwrote:

Quote:

Originally Posted by

>Hi!
>
>I have a query that has to return bunch of data based on the calendar
>month. I have to make sure that it will return data to me for 28 days
>if it is February and for 31 if it is August(for example). I need to
>be able to execute it every first of every month for the past 30, 31 or
>28 days based on the calendar month. Is there a function or a stored
>procedure that I can use to do that?
>
>Thank you,
>T.

|||tocis,

Ignore the first post, this is better. Schedule your job to be run on the
first of every month. This will give you the prior month date range for any
date, not just the 1st. This way if the job fails you can also run it on the
2nd, 3rd, etc.

declare @.Now datetime
declare @.StartDate datetime
declare @.EndDate datetime

set @.Now = getdate()

-- to get end date, subtract days to get to get 1st of the month for any
date
set @.EndDate = dateadd(d, 1 - datepart(d,@.Now), @.Now)

-- remove the time portion
set @.EndDate = convert(char(12), @.EndDate, 112)

-- subtract a month for start date
set @.StartDate = dateadd(m, -1, @.EndDate)
print cast(@.StartDate as char(25)) + cast(@.EndDate as char(25))
-- execute your code here using >= @.StartDate and < @.EndDate

-- Bill

"AlterEgo" <alterego55@.dslextreme.comwrote in message
news:12qte0qjqivt0ae@.corp.supernews.com...

Quote:

Originally Posted by

tolcis,
>
There might be a more elegant way, but this should work:
>
declare @.Now datetime
declare @.StartDate datetime
declare @.EndDate datetime
>
set @.Now = getdate()
if datepart(d, @.Now) = 1 -- only execute if it is the first day of the
month
begin
-- to get start date, subtract one month from the date and remove the
time from the date
set @.StartDate = convert(varchar(15), dateadd (m, -1, @.now), 112)
-- to get end date, remove time from date
set @.EndDate = dateadd(m, 1, @.StartDate)
print cast(@.StartDate as char(25)) + cast(@.EndDate as char(25))
-- execute your code here using >= @.StartDate and < @.EndDate
end
>
-- Bill
>
"tolcis" <nytollydba@.gmail.comwrote in message
news:1169073028.098703.235980@.11g2000cwr.googlegro ups.com...

Quote:

Originally Posted by

>Hi!
>>
>I have a query that has to return bunch of data based on the calendar
>month. I have to make sure that it will return data to me for 28 days
>if it is February and for 31 if it is August(for example). I need to
>be able to execute it every first of every month for the past 30, 31 or
>28 days based on the calendar month. Is there a function or a stored
>procedure that I can use to do that?
>>
>Thank you,
>T.
>>

>
>

|||Roy,

Wya cool, did not know this!

-- Bill

"Roy Harvey" <roy_harvey@.snet.netwrote in message
news:vietq2l4344pq52plgrnael566amuu5pfb@.4ax.com...

Quote:

Originally Posted by

WHERE create_date < DATEADD(month,DATEDIFF(month, 0,getdate()),0)
AND create_date >= DATEADD(month,DATEDIFF(month, 0,getdate())-1,0)
>
Roy Harvey
Beacon Falls, CT
>
On 17 Jan 2007 14:30:29 -0800, "tolcis" <nytollydba@.gmail.comwrote:
>

Quote:

Originally Posted by

>>Hi!
>>
>>I have a query that has to return bunch of data based on the calendar
>>month. I have to make sure that it will return data to me for 28 days
>>if it is February and for 31 if it is August(for example). I need to
>>be able to execute it every first of every month for the past 30, 31 or
>>28 days based on the calendar month. Is there a function or a stored
>>procedure that I can use to do that?
>>
>>Thank you,
>>T.

|||Thanks. Works great.

Roy Harvey wrote:

Quote:

Originally Posted by

WHERE create_date < DATEADD(month,DATEDIFF(month, 0,getdate()),0)
AND create_date >= DATEADD(month,DATEDIFF(month, 0,getdate())-1,0)
>
Roy Harvey
Beacon Falls, CT
>
On 17 Jan 2007 14:30:29 -0800, "tolcis" <nytollydba@.gmail.comwrote:
>

Quote:

Originally Posted by

Hi!

I have a query that has to return bunch of data based on the calendar
month. I have to make sure that it will return data to me for 28 days
if it is February and for 31 if it is August(for example). I need to
be able to execute it every first of every month for the past 30, 31 or
28 days based on the calendar month. Is there a function or a stored
procedure that I can use to do that?

Thank you,
T.

sql

job keep running if fail

Dear All

In SQL Server 2000, I schedule a job to run a exe. I expect the return of this result show the success or fail. However, I find the job keep running if fail. Actually, I want to see the failed status in enterprise manager.

Could you give me some suggestions how to return a fail from VB6 program to SQL Server? Maybe give me other directions to solve the problem? Thanks a lot.

More Information
I run the exe by double click. if it fail, it return a prompt message box.

Alex

This is a SSIS not DTS or VB6 forum, so you may get better responses on a more appropriate forum or newsgroup-

http://www.devx.com/vb2themax/Tip/18288

|||

Reading again is see you mention a message box. This will cause the scheduled job to just hang. You should never throw message boxes or any other form of UI when running unattended. I think there may have been an option suppress errors in message boxes, somewhere in project properties, but it has been a long time since I used VB6. Try the newsgroup, or just searching Google Groups.

sql

JDBC: calling a stored procedure with multiple return values.

Using JDBC, is there a way to call a stored procedure with multiple
return values? Thanks.
randy.p.ho@.gmail.com wrote:

> Using JDBC, is there a way to call a stored procedure with multiple
> return values? Thanks.

Absolutely. What do you mean by 'multiple return values'? Multiple output
parameters? Multiple result sets and/or update counts? Multiple mixes of
result sets and update counts?
If you will show the procedure signature and maybe even the text? Tell
us what the body of the procedure returns.

Joe Weinstein at BEA|||Thanks for the reply. I meant "multiple output parameters".

Here is how I execute the stored procedure:

declare @.ErrorID int
declare @.ErrorStr varchar(255)
exec procName
@.customerId = '1234567890',
@.customerName = 'some name',
@.error_code = @.ErrorID,
@.error_state = @.ErrorStr

Here is the procedure:

create procedure uxt1.procName
@.customerId char(15) output,
@.customerName char(64) output,
@.error_code int output,
@.error_state varchar(255) output
... ...
/* all the business logic */
... ...
return (@.error_state)
GO

Here's what SQL server gives me if I do a "Script object as Execute":
DECLARE @.RC int
DECLARE @.customerId char(15)
DECLARE @.customerName char(64)
DECLARE @.error_code int
DECLARE @.error_state varchar(255)
EXEC @.RC = [uxt1].[procName] @.customerId, @.customerName, @.error_code
OUTPUT , @.error_state OUTPUT

The following is what I've tried in a Java program:
...
CallableStatement cs = conn.prepareCall(" {? = call
uxt1.procName(?,?,?,?)}" );
cs.registerOutParameter(1,java.sql.Types.INTEGER);
cs.setString(2,"some ID");
cs.setString(3,"some Name");
cs.registerOutParameter(4,java.sql.Types.INTEGER);
cs.registerOutParameter(5,java.sql.Types.VARCHAR);
ResultSet rs = cs.executeQuery();
...

My code doesn't throw any exception; but the procedure was not executed
correctly (i.e. it's not doing what it's supposed to do, which is to
simply insert some values into a table).
Any help is appreciated. Thanks in advance.|||
randy.p.ho@.gmail.com wrote:

> Thanks for the reply. I meant "multiple output parameters".
> Here is how I execute the stored procedure:
> declare @.ErrorID int
> declare @.ErrorStr varchar(255)
> exec procName
> @.customerId = '1234567890',
> @.customerName = 'some name',
> @.error_code = @.ErrorID,
> @.error_state = @.ErrorStr
>
> Here is the procedure:
> create procedure uxt1.procName
> @.customerId char(15) output,
> @.customerName char(64) output,
> @.error_code int output,
> @.error_state varchar(255) output
> ... ...
> /* all the business logic */
> ... ...
> return (@.error_state)
> GO
>
> Here's what SQL server gives me if I do a "Script object as Execute":
> DECLARE @.RC int
> DECLARE @.customerId char(15)
> DECLARE @.customerName char(64)
> DECLARE @.error_code int
> DECLARE @.error_state varchar(255)
> EXEC @.RC = [uxt1].[procName] @.customerId, @.customerName, @.error_code
> OUTPUT , @.error_state OUTPUT
>
> The following is what I've tried in a Java program:
> ...
> CallableStatement cs = conn.prepareCall(" {? = call
> uxt1.procName(?,?,?,?)}" );
> cs.registerOutParameter(1,java.sql.Types.INTEGER);
> cs.setString(2,"some ID");
> cs.setString(3,"some Name");
> cs.registerOutParameter(4,java.sql.Types.INTEGER);
> cs.registerOutParameter(5,java.sql.Types.VARCHAR);
> ResultSet rs = cs.executeQuery();
> ...
> My code doesn't throw any exception; but the procedure was not executed
> correctly (i.e. it's not doing what it's supposed to do, which is to
> simply insert some values into a table).
> Any help is appreciated. Thanks in advance.

Is the procedure executing at all? You shouldn't be calling executeQuery()
unless the first thing the procedure does is a select. Use execute() and
then loop:

cs.execute();
while (true)
{
int update_count = ps.getUpdateCount();
ResultSet rs = ps.getResultSet();
if ((rs == null && (update_count == -1)) break; // done

if (rs != null) process rs;
ps.getMoreResults();
}
// after processing inline results, call ps.getXXX() to get output parameters.

Whatever jdbc driver you're suing is pretty flakey if it
returns a result set from executeQuery() and the
procedure didn't do a select for data to go to the caller...

Joe Weinstein at BEA|||I have closely the same problem, excepting that my stored procs returns a "TABLE".

In fact, I have a SQL function that must return a TABLE and get 2 parameters. Its signature is as follow:

FUNCTION myFunction (@.codett varchar(40), @.codet varchar(40) )
RETURNS @.Tab TABLE([cp] [varchar] (40), [pourcentage] [int])

I'd like to call it from my DAO (using JDBC). having that 'cs' is a CallableStatement, I tried:

cs = jdbcConnection.prepareCall("{? = call myFunction(?, ?)}");
cs.registerOutParameter( 1, Types.OTHER );
cs.setString(2, codett);//codett contains a String
cs.setString(3, codet);//codet contains a String
ResultSet rset = cs.executeQuery();

However, when a get to the "executeQuery()" method, I get an SQLException saying that "myFunction" is a function Object.

have you met such a problem ?