Default datetime values?

I'm confused. I have two variables:
@.StartDate and @.EndDate. I have to figure out how to
execute this report each day through email grabbing
yesterdays data. I tried just for a test to put in GetDate
() and GetDate()-1 and I got an error. I'm wondering how
do I set two datetime variables for yesterday 12:00 AM to
today 12:00 AM. Please help,
Regards,
BryanBmurtha,
Try using DateAdd function like
=DateAdd(DateInterval.Day, -1, Today())
Regards,
Cem
"bmurtha" <anonymous@.discussions.microsoft.com> wrote in message
news:46c301c47352$60dcc490$a601280a@.phx.gbl...
> I'm confused. I have two variables:
> @.StartDate and @.EndDate. I have to figure out how to
> execute this report each day through email grabbing
> yesterdays data. I tried just for a test to put in GetDate
> () and GetDate()-1 and I got an error. I'm wondering how
> do I set two datetime variables for yesterday 12:00 AM to
> today 12:00 AM. Please help,
> Regards,
> Bryan|||Or try:
=Today.AddDays(-1)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
news:e4cADS1cEHA.3988@.tk2msftngp13.phx.gbl...
>
> Bmurtha,
> Try using DateAdd function like
> =DateAdd(DateInterval.Day, -1, Today())
> Regards,
> Cem
>
> "bmurtha" <anonymous@.discussions.microsoft.com> wrote in message
> news:46c301c47352$60dcc490$a601280a@.phx.gbl...
> > I'm confused. I have two variables:
> > @.StartDate and @.EndDate. I have to figure out how to
> > execute this report each day through email grabbing
> > yesterdays data. I tried just for a test to put in GetDate
> > () and GetDate()-1 and I got an error. I'm wondering how
> > do I set two datetime variables for yesterday 12:00 AM to
> > today 12:00 AM. Please help,
> >
> > Regards,
> > Bryan
>

Decoding a Maintenance Plan

How can I "decode" exactly what is done during a scheduled Maintenance Plan
(or its job/step)?
The job steps only indicate EXECUTE master.dbo.xp_sqlmaint N'-PlanID <uuid>
etc.
I tried sp_help_maintenance_plan, but received the error
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_help_maintenance_plan'.
I am a member of the sysadmin fixed role.
TIA,
Tore.Tore
In EM go to your maint plan (in the management folder) and
look at the properties. This will show you what it is
doing and you can make changes there.
Regards
John|||Thanks. It isn't there, either.
Another post pointed me to the "Database Maintenance Plans" under the
Management folder in EM. (I'm not blind, just focused... :->).
Tore.
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23MUito2WDHA.652@.TK2MSFTNGP10.phx.gbl...
> Hi Tore,
> sp_help_maintenance_plan exists in the msdb database, not in the master
> database, which means that you either have to be in msdb when you run it,
or
> prefix it with msdb.. to run it from any database:
> EXEC msdb..sp_help_maintenance_plan will work.
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
> "Tore Bostrup" <newspost_at_bostrup.us> wrote in message
> news:OUGPWf2WDHA.212@.TK2MSFTNGP12.phx.gbl...
> > How can I "decode" exactly what is done during a scheduled Maintenance
> Plan
> > (or its job/step)?
> >
> > The job steps only indicate EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> <uuid>
> > etc.
> >
> > I tried sp_help_maintenance_plan, but received the error
> >
> > Server: Msg 2812, Level 16, State 62, Line 1
> > Could not find stored procedure 'sp_help_maintenance_plan'.
> >
> > I am a member of the sysadmin fixed role.
> >
> > TIA,
> > Tore.
> >
> >
>|||I'm not blind, just focused... :->
Thanks,
Tore.
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:8ed101c35b6b$305067f0$a001280a@.phx.gbl...
> Tore
> In EM go to your maint plan (in the management folder) and
> look at the properties. This will show you what it is
> doing and you can make changes there.
> Regards
> John

Declare dynamicly variable

I'd like to know, if there is a possibility reference declare SQLServer dynamically?

For example, I've tried execute this query(under), but I received this message (Server: Msg 137, Level 15, State 1, Line 6
Must declare the variable '@.t1'.):

declare @.Frase varchar(50)
set @.Frase = 'declare @.t1 varchar(10), @.t2 int'
exec (@.Frase)
select @.t1 = 'AAAAA'
select @.t2 = 1000
Print @.t1
Print @.t2

ThanksMay be because Dynamic SQL is executed/Parsed last by the query Parser and the variable declared are within the statement which is limited to the "exec" and is considered a seperate stored procedure outside of main query|||The EXEC statement executes within its own scope, outside of the procedure that calls it. Therefore, EXEC cannot share variables with its calling procedure. As soon as EXEC completes, the variables go out of scope and "poof", they disappear. Temporary tables, however, are connection specific and can be referenced within EXEC statements.

blindman

Declare cursor for execute stored_procedure

Hello,

I am using SQL 2005 and i would like to create a cursor from executing a stored procedure (dbo.SP_getDate @.Variable).
Something like this:

DECLARE Cursor1 CURSOR FOR EXECUTE dbo.SP_getDate @.Variable

i get an error saying "incorrect syntax near the keyword 'EXECUTE'."
cannot get rid of the error. what am i doing wrong?
(i am trying to avoid using #tempTbl to store the results of the execute first and then doing a select on the #tempTbl)

Not sure if i am doing this right all together.
any help would be greatly appreciate.See if this helps. It is not good practice to concatenate user entries into a SQL string, so be careful not to risk SQL Injection.

create proc SP_getDate (

@.v int

) as

SET NOCOUNT ON

SELECT TOP (@.v) HireDate

FROM AdventureWorks.HumanResources.Employee

ORDER BY EmployeeID

go

DECLARE @.sql NVARCHAR(1000)

DECLARE @.Variable int

SET @.Variable = 13

SET @.sql = '

DECLARE Cursor1 CURSOR FOR

SELECT HireDate

FROM OPENQUERY([SK8400\YUK], ''exec AdventureWorks.dbo.SP_getDate @.v'')'

SET @.sql = REPLACE(@.sql,'@.v',@.Variable)

exec (@.sql)

go

declare @.d datetime

open Cursor1

toploop:

fetch from Cursor1 into @.d

while @.@.fetch_status = 0 begin

print @.d

goto toploop

end

close Cursor1

deallocate Cursor1

go

drop proc SP_getDate

-- Steve Kass

-- Drew University

-- http://www.stevekass.com

Yassi@.discussions.microsoft.com wrote:

> Hello,

>

> I am using SQL 2005 and i would like to create a cursor from executing a

> stored procedure (dbo.SP_getDate @.Variable).

> Something like this:

>

> DECLARE Cursor1 CURSOR FOR EXECUTE dbo.SP_getDate @.Variable

>

> i get an error saying "incorrect syntax near the keyword 'EXECUTE'."

> cannot get rid of the error. what am i doing wrong?

> (i am trying to avoid using #tempTbl to store the results of the execute

> first and then doing a select on the #tempTbl)

>

> Not sure if i am doing this right all together.

> any help would be greatly appreciate.

>

>

Decimal.MinValue throw OverflowException

I'm using Decimal.MinValue in SqlParameter( SqlDbType.Decimal ) to execute a stored procedure.

But, I receive this stack trace exception:

System.OverflowException: Conversion overflows.
at System.Data.SqlTypes.SqlDecimal.ToDecimal()
at System.Data.SqlTypes.SqlDecimal.get_Value()
at System.Data.SqlClient.TdsParser.AdjustDecimalScale(Decimal value, Int32 newScale)
at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

I have changed Decimal.MinValue to Decimal.Zero to resolve my trouble. But, Why Decimal.MinValue throw OverflowException? Isn't Decimal.MinValue valid SqlDbType.Decimal type?

From the online help:

The value of this constant is negative 79,228,162,514,264,337,593,543,950,335.

How many digits did you set the decimal database field to accept?

|||

Hi,

Please check if the decimal scale and precision are set correctly in both .NET and database.

The should be match, so the data conversion can work properly.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

Debug output no longer displaying?

I must have hit some setting to turn off the debug output, but I can't find how to get it back on. The debug output window displays when I execute a package, I just don't get the execution output I had been getting. Sorry to bother you with such trivia, but any help you can provide is appreciated. Thanks.

If I understand you correctly, then what you are looking for is under 'View - Output'.

Pipo

|||That's what I am looking at, but the output window is empty - nothing is being generated in the window when I execute the package. It had been before - just not anymore.|||Got it. When I right click within the output window the option for program output was not checked. When I clicked on that option to set the check on, I then began to get the output trace again.