Production Version

I looked around microsoft.com and here, but I don't see a target date for the production version. Is that info posted anywhere?

Thanks,

Ned

If you are looking for the date for Release version of SSEv 3.1 then it would be in Nov this Year.

We will soon post message on our Blog http://blogs.msdn.com/sqlservereverywhere

sql

Producing week ending dates for each week of the year

Hi

I am trying to produce a script that will show date for the last day of the week(Sunday). The script will have to show dates for all weeks of the year. Does anyone know how this can be done?

Thanks

Quote:

Originally Posted by rcr69er

Hi

I am trying to produce a script that will show date for the last day of the week(Sunday). The script will have to show dates for all weeks of the year. Does anyone know how this can be done?

Thanks

hai try this-----------
declare
v_date date;
begin
select next_day('01-JAN-07','sunday') into v_date from dual;
for i in 0..51 loop
dbms_output.put_line(to_char(to_date((v_date)+7*i) ));
end loop;
end;sql

producing a date time report in SQL/DTS

I have need to produce a report (excel sheet actually) from SQL that
would run each Tuesday and each Friday of every w.
What needs to be on the Tuesday report is everything that came in from
the Friday midnight time, until the Monday midnight time. The friday
report(sheet) would have everything that came in from Midnight Monday
evening, thru midnight Thursday. The next Tuesday report would have
everything from Midnight Thursday thru midnight Monday, and so on.
I know I can schedule the jobs to run on that interval, but how do I
selectively pick the records I want? There is a datetime field on the
table, "submit date" and what I am basically doing is a select * from
tbl_literature_orders where date > x.
Any ideas?
Thanks,
BC"Blasting Cap" schrieb:
> I have need to produce a report (excel sheet actually) from SQL that
> would run each Tuesday and each Friday of every w.
> What needs to be on the Tuesday report is everything that came in from
> the Friday midnight time, until the Monday midnight time. The friday
> report(sheet) would have everything that came in from Midnight Monday
> evening, thru midnight Thursday. The next Tuesday report would have
> everything from Midnight Thursday thru midnight Monday, and so on.
> I know I can schedule the jobs to run on that interval, but how do I
> selectively pick the records I want? There is a datetime field on the
> table, "submit date" and what I am basically doing is a select * from
> tbl_literature_orders where date > x.
> Any ideas?
> Thanks,
> BC
Try it with two jobs, one for Tuesday, one for Friday, and set the execution
time of the job appropriately. Search for your data by difference:
select * from MyTable where datefield > dateadd(d, -3, GetDate()) -- Friday
and
select * from MyTable where datefield > dateadd(d, -4, GetDate()) -- Tuesday|||Just use the DATEPART() or DATENAME() functions to determine which day it
is. Then use DATEADD() with the appropriate days to get the from and to
that you need for your WHERE clause.
Andrew J. Kelly SQL MVP
"Blasting Cap" <goober@.christian.net> wrote in message
news:eiUP1w9CGHA.4080@.TK2MSFTNGP09.phx.gbl...
>I have need to produce a report (excel sheet actually) from SQL that would
>run each Tuesday and each Friday of every w.
> What needs to be on the Tuesday report is everything that came in from the
> Friday midnight time, until the Monday midnight time. The friday
> report(sheet) would have everything that came in from Midnight Monday
> evening, thru midnight Thursday. The next Tuesday report would have
> everything from Midnight Thursday thru midnight Monday, and so on.
> I know I can schedule the jobs to run on that interval, but how do I
> selectively pick the records I want? There is a datetime field on the
> table, "submit date" and what I am basically doing is a select * from
> tbl_literature_orders where date > x.
> Any ideas?
> Thanks,
> BCsql

Processed Date for a Cube

Hi

I want to be able to display to the users the date/time a cube was last processed. We have SSAS 2005 and presenting the cube through Excel 2003 (with the SSAS 9 OLAP driver).

Thanks

LastProcessed is a property on the object avaliable only through AMO. Dont think Excel will show you this property.

You can possible wirte a AMO code yourself and use Excel programming model to show it.

Another way is to create a dimension with a single memeber based on the column in the relational database where you would keep the current date and during processing you update this dimension to pickup a new value.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks for the suggestions.

I created a table with the current date and use this.

Procedure Stopping

Hi,

Please look at the code at the bottom of this procedure (UPDATE
COVENANT PRINT DATE). When I put it at the tope of this section, it
does the update and fails to return the SELECT results. When I put it
at the bottom, the SELECT returns results and the UPDATE fails.

Can anyone suggest what might be causing my problem?

Thanks

/************ SPECIFIC PROGRAM SEARCH ************************/
IF @.PRG_ID != 0 -- PRG_ID supplied
BEGIN
SET @.strWHERE = 'WHERE PRG.PRG_ID = ' + cast(@.PRG_ID as varchar(15))
EXECUTE (@.strSELECT + ' ' + @.strFROM + ' ' + @.strWHERE + ' ' +
@.strORDERBY)
RETURN(0)
END

/************ NON-SPECIFIC PROGRAM SEARCH ************************/

IF @.ORG_ID != 0 -- Add ORG_ID to where
IF @.intAND = 0
BEGIN
SET @.strWHERE = @.strWHERE + 'ORG.ORG_ID = ' + cast(@.ORG_ID as
varchar(15))
SET @.intAND = 1
END
ELSE
BEGIN
SET @.strWHERE = @.strWHERE + ' AND ORG.ORG_ID = ' + cast(@.ORG_ID as
varchar(15))
END

IF @.PRG_Closed != 2 -- Add PRG_Closed to where
IF @.intAND = 0
BEGIN
SET @.strWHERE = @.strWHERE + 'PRG.PRG_Closed = ' + cast(@.PRG_Closed
as varchar(15))
SET @.intAND = 1
END
ELSE
BEGIN
SET @.strWHERE = @.strWHERE + ' AND PRG.PRG_Closed = ' +
cast(@.PRG_Closed as varchar(15))
END

IF @.strWHERE != 'WHERE '-- if some parameters supplied..
BEGIN-- Execute search
EXECUTE (@.strSELECT + ' ' + @.strFROM + ' ' + @.strWHERE + ' ' +
@.strORDERBY)
RETURN(0)
END

/************ UPDATE COVENANT PRINT DATE ************************/
-- Added by JSHAW 02/25/2004
IF @.PRG_Covenant != 0 -- PRG_Covenant supplied

BEGIN
--SET @.ORD_Date = GETDATE()
UPDATE PROGRAMS
SET Covenant_Printed = convert(char(10),@.ORD_Date,101)
WHEREPRG_ID = cast(@.PRG_ID as varchar(15))
END
GO"RETURN is immediate and complete and can be used at any point to exit from
a procedure, batch, or statement block. Statements following RETURN are not
executed."

HTH
Igro Raytsin

"John Shaw" <jmshaw@.weir.net> wrote in message
news:91422298.0403041138.1059d960@.posting.google.c om...
> Hi,
> Please look at the code at the bottom of this procedure (UPDATE
> COVENANT PRINT DATE). When I put it at the tope of this section, it
> does the update and fails to return the SELECT results. When I put it
> at the bottom, the SELECT returns results and the UPDATE fails.
> Can anyone suggest what might be causing my problem?
> Thanks
> /************ SPECIFIC PROGRAM SEARCH ************************/
> IF @.PRG_ID != 0 -- PRG_ID supplied
> BEGIN
> SET @.strWHERE = 'WHERE PRG.PRG_ID = ' + cast(@.PRG_ID as varchar(15))
> EXECUTE (@.strSELECT + ' ' + @.strFROM + ' ' + @.strWHERE + ' ' +
> @.strORDERBY)
> RETURN(0)
> END
> /************ NON-SPECIFIC PROGRAM SEARCH ************************/
> IF @.ORG_ID != 0 -- Add ORG_ID to where
> IF @.intAND = 0
> BEGIN
> SET @.strWHERE = @.strWHERE + 'ORG.ORG_ID = ' + cast(@.ORG_ID as
> varchar(15))
> SET @.intAND = 1
> END
> ELSE
> BEGIN
> SET @.strWHERE = @.strWHERE + ' AND ORG.ORG_ID = ' + cast(@.ORG_ID as
> varchar(15))
> END
> IF @.PRG_Closed != 2 -- Add PRG_Closed to where
> IF @.intAND = 0
> BEGIN
> SET @.strWHERE = @.strWHERE + 'PRG.PRG_Closed = ' + cast(@.PRG_Closed
> as varchar(15))
> SET @.intAND = 1
> END
> ELSE
> BEGIN
> SET @.strWHERE = @.strWHERE + ' AND PRG.PRG_Closed = ' +
> cast(@.PRG_Closed as varchar(15))
> END
> IF @.strWHERE != 'WHERE ' -- if some parameters supplied..
> BEGIN -- Execute search
> EXECUTE (@.strSELECT + ' ' + @.strFROM + ' ' + @.strWHERE + ' ' +
> @.strORDERBY)
> RETURN(0)
> END
>
> /************ UPDATE COVENANT PRINT DATE ************************/
> -- Added by JSHAW 02/25/2004
> IF @.PRG_Covenant != 0 -- PRG_Covenant supplied
> BEGIN
> --SET @.ORD_Date = GETDATE()
> UPDATE PROGRAMS
> SET Covenant_Printed = convert(char(10),@.ORD_Date,101)
> WHERE PRG_ID = cast(@.PRG_ID as varchar(15))
> END
> GO

procedure runs with an error??

This is my procedure and the error is incorrect syntax near '01'

DECLARE @.returnDay int

--Looking at current date,
SELECT @.returnDay = DatePart(day,GetDate())
--If is the 7th of the current moth then
If @.returnDay = 24

EXEC master.dbo.xp_sendmail
@.query = 'SELECT a.HospitalName,a.HospitalCode, c.ProductName,b.UnitsDiscarded,d.FateOfProducts,b. DateEntered,
b.DateCompleted,b.CompiledBy FROM test.dbo.Units b inner join Hospitals a ON (a.HospitalID = b.HospitalID)
inner join Products c ON (b.ProductID = c.ProductID)
inner join FateOfProducts d ON (d.FateID = b.FateID)
where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01') order by a.HospitalID',
@.recipients=test@.hotmail.com', @.message='Submitting Results for the previous month',
@.subject=' results for previous month', @.attach_results = 'true', @.separator = '/s'

SELECT @.@.ERROR As ErrorNumber

What am I missing here now, I am quite new to stored procedures

REgardsEXEC master.dbo.xp_sendmail
@.query = "SELECT a.HospitalName,a.HospitalCode, c.ProductName,b.UnitsDiscarded,d.FateOfProducts,b. DateEntered,
b.DateCompleted,b.CompiledBy FROM test.dbo.Units b inner join Hospitals a ON (a.HospitalID = b.HospitalID)
inner join Products c ON (b.ProductID = c.ProductID)
inner join FateOfProducts d ON (d.FateID = b.FateID)
where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01') order by a.HospitalID",
@.recipients=test@.hotmail.com', @.message='Submitting Results for the previous month',
@.subject=' results for previous month', @.attach_results = 'true', @.separator = '/s'|||Thank you for your help.
Now I have this error message

The identifier that starts with 'SELECT a.HospitalName,c.ProductName,b.UnitsDiscarded,d.Fa teOfProducts,b.DateEntered,
b.DateCompleted,b.CompiledBy
FROM Test.database' is too long. Maximum length is 128.

I have seen this error but not a solution for it...
Could you help me with that?|||it seems that the allowed lenght of the @.query is 128 only so it is giving an error.|||Yeah I got that too,but I need all the data from the query so how I am suppose to go around it, any suggestions...|||Yeah I got that too,but I need all the data from the query so how I am suppose to go around it, any suggestions...

how about using a staging table (some temp table) to get the results and while sending the mail just select the records from this staging table?|||I hope I am not annoying everybody,but how do I use the temp table,do I create a replica of a table that has all the data or...I see what you mean expect I don't quite know what is my next step...

Any help appreciated|||set quoted_identifier off
EXEC master.dbo.xp_sendmail
@.query = "SELECT a.HospitalName,a.HospitalCode, c.ProductName,b.UnitsDiscarded,d.FateOfProducts,b. DateEntered,
b.DateCompleted,b.CompiledBy FROM test.dbo.Units b inner join Hospitals a ON (a.HospitalID = b.HospitalID)
inner join Products c ON (b.ProductID = c.ProductID)
inner join FateOfProducts d ON (d.FateID = b.FateID)
where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01') order by a.HospitalID",
@.recipients='test@.hotmail.com', @.message='Submitting Results for the previous month',
@.subject=' results for previous month', @.attach_results = 'true', @.separator = '/s'|||I hope I am not annoying everybody,but how do I use the temp table,do I create a replica of a table that has all the data or...I see what you mean expect I don't quite know what is my next step...

Any help appreciated
SELECT a.HospitalName,a.HospitalCode, c.ProductName,b.UnitsDiscarded,d.FateOfProducts,b. DateEntered,
b.DateCompleted,b.CompiledBy into #temp FROM master.dbo.units b inner join Hospitals a ON (a.HospitalID = b.HospitalID)
inner join Products c ON (b.ProductID = c.ProductID)
inner join FateOfProducts d ON (d.FateID = b.FateID)
where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01') order by a.HospitalID

EXEC master.dbo.xp_sendmail
@.query = "SELECT * from #temp",
@.recipients=test@.hotmail.com', @.message='Submitting Results for the previous month',
@.subject=' results for previous month', @.attach_results = 'true', @.separator = '/s'|||set quoted_identifier off
EXEC master.dbo.xp_sendmail
@.query = "SELECT a.HospitalName,a.HospitalCode, c.ProductName,b.UnitsDiscarded,d.FateOfProducts,b. DateEntered,
b.DateCompleted,b.CompiledBy FROM test.dbo.Units b inner join Hospitals a ON (a.HospitalID = b.HospitalID)
inner join Products c ON (b.ProductID = c.ProductID)
inner join FateOfProducts d ON (d.FateID = b.FateID)
where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01') order by a.HospitalID",
@.recipients='test@.hotmail.com', @.message='Submitting Results for the previous month',
@.subject=' results for previous month', @.attach_results = 'true', @.separator = '/s'

:eek:
smart|||Thank you guys heaps...|||By default in SQL Server 2000 ... the entries inside "" (double quotes) are taken as identifiers ... you need to turn them off while using them as a way to avoid this happening.

alternatively you can use the single quote two times to use them within a string

eg .


set quoted_identifier off
EXEC master.dbo.xp_sendmail
@.query = 'SELECT a.HospitalName,a.HospitalCode, c.ProductName,b.UnitsDiscarded,d.FateOfProducts,b. DateEntered,
b.DateCompleted,b.CompiledBy FROM test.dbo.Units b inner join Hospitals a ON (a.HospitalID = b.HospitalID)
inner join Products c ON (b.ProductID = c.ProductID)
inner join FateOfProducts d ON (d.FateID = b.FateID)
where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + ''01'') order by a.HospitalID',
@.recipients='test@.hotmail.com', @.message='Submitting Results for the previous month',
@.subject=' results for previous month', @.attach_results = 'true', @.separator = '/s'


I hope I have been able to explain it clearly ...|||Excellent, many thanks