Showing posts with label current. Show all posts
Showing posts with label current. Show all posts

Thursday, March 22, 2012

Delete 1 month old records

I need to set up a job which will run the last day of every month to
keep the data in the table for the current month only and delete
everyhthing else (older than 1 month records would be deleted). see the
sample case:
create table #t (a datetime)
insert into #t values (getdate())
insert into #t values ('9/1/2005')
insert into #t values ('8/20/2005')
insert into #t values ('8/8/2005')
insert into #t values ('7/3/2005')
insert into #t values ('6/1/2005')
insert into #t values ('4/1/2004')
delete from #t
where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
My final result in this case would be only two records (9/2005).
Eeverything else should be deleted. My DELETE stament is not working bc
of the time poartion I guess. Can you correct this SQL?
Thanks for your help.
*** Sent via Developersdex http://www.examnotes.net ***delete from #t
where a < cast(month(current_timestamp) as varchar) + '-1-' +
cast(year(current_timestamp) as varchar)
--Brian
(Please reply to the newsgroups only.)
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:O90arSyrFHA.1204@.TK2MSFTNGP15.phx.gbl...
>I need to set up a job which will run the last day of every month to
> keep the data in the table for the current month only and delete
> everyhthing else (older than 1 month records would be deleted). see the
> sample case:
> create table #t (a datetime)
> insert into #t values (getdate())
> insert into #t values ('9/1/2005')
> insert into #t values ('8/20/2005')
> insert into #t values ('8/8/2005')
> insert into #t values ('7/3/2005')
> insert into #t values ('6/1/2005')
> insert into #t values ('4/1/2004')
>
> delete from #t
> where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
> My final result in this case would be only two records (9/2005).
> Eeverything else should be deleted. My DELETE stament is not working bc
> of the time poartion I guess. Can you correct this SQL?
> Thanks for your help.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Try:
delete from #t
where a < convert (char (8), DATEADD(MONTH,-1,CURRENT_TIMESTAMP) , 112)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:O90arSyrFHA.1204@.TK2MSFTNGP15.phx.gbl...
I need to set up a job which will run the last day of every month to
keep the data in the table for the current month only and delete
everyhthing else (older than 1 month records would be deleted). see the
sample case:
create table #t (a datetime)
insert into #t values (getdate())
insert into #t values ('9/1/2005')
insert into #t values ('8/20/2005')
insert into #t values ('8/8/2005')
insert into #t values ('7/3/2005')
insert into #t values ('6/1/2005')
insert into #t values ('4/1/2004')
delete from #t
where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
My final result in this case would be only two records (9/2005).
Eeverything else should be deleted. My DELETE stament is not working bc
of the time poartion I guess. Can you correct this SQL?
Thanks for your help.
*** Sent via Developersdex http://www.examnotes.net ***|||Try,
delete #t
where a < cast(convert(varchar(6), getdate(), 112) + '01' as datetime)
AMB
"Test Test" wrote:

> I need to set up a job which will run the last day of every month to
> keep the data in the table for the current month only and delete
> everyhthing else (older than 1 month records would be deleted). see the
> sample case:
> create table #t (a datetime)
> insert into #t values (getdate())
> insert into #t values ('9/1/2005')
> insert into #t values ('8/20/2005')
> insert into #t values ('8/8/2005')
> insert into #t values ('7/3/2005')
> insert into #t values ('6/1/2005')
> insert into #t values ('4/1/2004')
>
> delete from #t
where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
> My final result in this case would be only two records (9/2005).
> Eeverything else should be deleted. My DELETE stament is not working bc
> of the time poartion I guess. Can you correct this SQL?
> Thanks for your help.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Oops, gotta chop off that month part:
delete from #t
where a < convert (char (6), DATEADD(MONTH,-1,CURRENT_TIMESTAMP) , 112) +
'01'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23dwJ3cyrFHA.3440@.TK2MSFTNGP10.phx.gbl...
Try:
delete from #t
where a < convert (char (8), DATEADD(MONTH,-1,CURRENT_TIMESTAMP) , 112)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:O90arSyrFHA.1204@.TK2MSFTNGP15.phx.gbl...
I need to set up a job which will run the last day of every month to
keep the data in the table for the current month only and delete
everyhthing else (older than 1 month records would be deleted). see the
sample case:
create table #t (a datetime)
insert into #t values (getdate())
insert into #t values ('9/1/2005')
insert into #t values ('8/20/2005')
insert into #t values ('8/8/2005')
insert into #t values ('7/3/2005')
insert into #t values ('6/1/2005')
insert into #t values ('4/1/2004')
delete from #t
where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
My final result in this case would be only two records (9/2005).
Eeverything else should be deleted. My DELETE stament is not working bc
of the time poartion I guess. Can you correct this SQL?
Thanks for your help.
*** Sent via Developersdex http://www.examnotes.net ***|||It works! Thanks for all your help!
*** Sent via Developersdex http://www.examnotes.net ***

Wednesday, March 21, 2012

Degrading performance. Expert help appreciated

Greetings,
In our current project, we use a combination of SQL Server 2000 and
ADO. Our database is very simple and small: just about 10 tables with
300K records at most (in just one of the tables). We need, however,
very fast responses to our queries. With that in mind, we designed and
optimized all queries and indices in such a way that each query takes
less that 20 milliseconds, as measured using the SQL profiler under a
normal load.
Each client application opens a single connection to the database and
the queries are funneled through that connection. Each query is a
individual transaction, i.e. it is fenced by Begin Tran...End Tran. We
use mostly stored procedures, which are executed via the _Command
object from ADO. In just a couple of cases, we use _Recordset.
Under a *stress* load, one client can submit 20 transactions/sec to
the server.
In this scenario, I noticed that, sometimes, many commands were taking
almost two orders of magnitude more than under the normal load. I used
SQL Profiler to monitor all Statements and SPs taking longer tha 100
msec and, to my surprise, found that, every so often, some command or
SP would take more than 1-2 seconds. What is interesting is that many
of these commands are IF @.@.TRANCOUNT > 0 COMMIT TRAN, which, I think,
ADO implicitly sends to the server. Those usually show with duration 0
(Zero) under normal load.
What I wanted from you, were some ideas on how to go about
troubleshooting this problem, by identifying the underlying cause for
such poor performance. The problem does not seem associated with a
particular command or SP. It also does not seem related to CPU
contention on the server because it is kept really low (about 20%).
Your help is greatly appreciated.
- CDThis is a multi-part message in MIME format.
--=_NextPart_000_0074_01C3EB4D.F4E4CB70
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
How necessary is the Begin Tran/End Tran? If you don't need to make a
series of updates atomic then leave this out.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
<crbd98@.yahoo.com> wrote in message
news:edf41e63.0402041449.724d721b@.posting.google.com...
Greetings,
In our current project, we use a combination of SQL Server 2000 and
ADO. Our database is very simple and small: just about 10 tables with
300K records at most (in just one of the tables). We need, however,
very fast responses to our queries. With that in mind, we designed and
optimized all queries and indices in such a way that each query takes
less that 20 milliseconds, as measured using the SQL profiler under a
normal load.
Each client application opens a single connection to the database and
the queries are funneled through that connection. Each query is a
individual transaction, i.e. it is fenced by Begin Tran...End Tran. We
use mostly stored procedures, which are executed via the _Command
object from ADO. In just a couple of cases, we use _Recordset.
Under a *stress* load, one client can submit 20 transactions/sec to
the server.
In this scenario, I noticed that, sometimes, many commands were taking
almost two orders of magnitude more than under the normal load. I used
SQL Profiler to monitor all Statements and SPs taking longer tha 100
msec and, to my surprise, found that, every so often, some command or
SP would take more than 1-2 seconds. What is interesting is that many
of these commands are IF @.@.TRANCOUNT > 0 COMMIT TRAN, which, I think,
ADO implicitly sends to the server. Those usually show with duration 0
(Zero) under normal load.
What I wanted from you, were some ideas on how to go about
troubleshooting this problem, by identifying the underlying cause for
such poor performance. The problem does not seem associated with a
particular command or SP. It also does not seem related to CPU
contention on the server because it is kept really low (about 20%).
Your help is greatly appreciated.
- CD
--=_NextPart_000_0074_01C3EB4D.F4E4CB70
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

How necessary is the Begin Tran/End =Tran? If you don't need to make a series of updates atomic then leave this out.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
=wrote in message news:edf41e=63.0402041449.724d721b@.posting.google.com...Greetings,I=n our current project, we use a combination of SQL Server 2000 andADO. =Our database is very simple and small: just about 10 tables with300K =records at most (in just one of the tables). We need, however,very fast =responses to our queries. With that in mind, we designed andoptimized all queries =and indices in such a way that each query takesless that 20 =milliseconds, as measured using the SQL profiler under anormal load.Each =client application opens a single connection to the database andthe queries =are funneled through that connection. Each query is aindividual =transaction, i.e. it is fenced by Begin Tran...End Tran. Weuse mostly stored =procedures, which are executed via the _Commandobject from ADO. In just a couple =of cases, we use _Recordset.Under a *stress* load, one client can =submit 20 transactions/sec tothe server.In this scenario, I noticed that, sometimes, many commands were takingalmost two orders of magnitude =more than under the normal load. I usedSQL Profiler to monitor all Statements =and SPs taking longer tha 100msec and, to my surprise, found that, every so =often, some command orSP would take more than 1-2 seconds. What is =interesting is that manyof these commands are IF @.@.TRANCOUNT > 0 COMMIT TRAN, =which, I think,ADO implicitly sends to the server. Those usually show with =duration 0(Zero) under normal load.What I wanted from you, were some =ideas on how to go abouttroubleshooting this problem, by identifying the =underlying cause forsuch poor performance. The problem does not seem associated =with aparticular command or SP. It also does not seem related to CPUcontention on the server because it is kept really low (about 20%).Your help is greatly appreciated.- CD

--=_NextPart_000_0074_01C3EB4D.F4E4CB70--|||sounds like some blocking issues.
when you run your tests, monitor blocks in SQL Server (and\or Deadlocks)
do your sprocs have NOLOCK Hints in them when appropriate ?
Or setting transaction isolation level to READ UNCOMMITTED (Where
Appropriate).
probably worth looking into.
cheers
Greg Jackson
PDX, OR|||Are you also using VB transactions? I found performance
improvement by issuing transactions only from my stored
procedures. I believe VB trans cause SQL to SET
IMPLICIT_TRANSACTIONS ON which can increase lock
contention & network round trips. I also saw
improvements by using disconnected recordsets.
Darren Fuller
SQL Server DBA MCSE
>--Original Message--
>Greetings,
>In our current project, we use a combination of SQL
Server 2000 and
>ADO. Our database is very simple and small: just about
10 tables with
>300K records at most (in just one of the tables). We
need, however,
>very fast responses to our queries. With that in mind,
we designed and
>optimized all queries and indices in such a way that
each query takes
>less that 20 milliseconds, as measured using the SQL
profiler under a
>normal load.
>Each client application opens a single connection to the
database and
>the queries are funneled through that connection. Each
query is a
>individual transaction, i.e. it is fenced by Begin
Tran...End Tran. We
>use mostly stored procedures, which are executed via the
_Command
>object from ADO. In just a couple of cases, we use
_Recordset.
>Under a *stress* load, one client can submit 20
transactions/sec to
>the server.
>In this scenario, I noticed that, sometimes, many
commands were taking
>almost two orders of magnitude more than under the
normal load. I used
>SQL Profiler to monitor all Statements and SPs taking
longer tha 100
>msec and, to my surprise, found that, every so often,
some command or
>SP would take more than 1-2 seconds. What is interesting
is that many
>of these commands are IF @.@.TRANCOUNT > 0 COMMIT TRAN,
which, I think,
>ADO implicitly sends to the server. Those usually show
with duration 0
>(Zero) under normal load.
>What I wanted from you, were some ideas on how to go
about
>troubleshooting this problem, by identifying the
underlying cause for
>such poor performance. The problem does not seem
associated with a
>particular command or SP. It also does not seem related
to CPU
>contention on the server because it is kept really low
(about 20%).
>Your help is greatly appreciated.
>- CD
>.
>

Degrading performance. Expert help appreciated

Greetings,
In our current project, we use a combination of SQL Server 2000 and
ADO. Our database is very simple and small: just about 10 tables with
300K records at most (in just one of the tables). We need, however,
very fast responses to our queries. With that in mind, we designed and
optimized all queries and indices in such a way that each query takes
less that 20 milliseconds, as measured using the SQL profiler under a
normal load.
Each client application opens a single connection to the database and
the queries are funneled through that connection. Each query is a
individual transaction, i.e. it is fenced by Begin Tran...End Tran. We
use mostly stored procedures, which are executed via the _Command
object from ADO. In just a couple of cases, we use _Recordset.
Under a *stress* load, one client can submit 20 transactions/sec to
the server.
In this scenario, I noticed that, sometimes, many commands were taking
almost two orders of magnitude more than under the normal load. I used
SQL Profiler to monitor all Statements and SPs taking longer tha 100
msec and, to my surprise, found that, every so often, some command or
SP would take more than 1-2 seconds. What is interesting is that many
of these commands are IF @.@.TRANCOUNT > 0 COMMIT TRAN, which, I think,
ADO implicitly sends to the server. Those usually show with duration 0
(Zero) under normal load.
What I wanted from you, were some ideas on how to go about
troubleshooting this problem, by identifying the underlying cause for
such poor performance. The problem does not seem associated with a
particular command or SP. It also does not seem related to CPU
contention on the server because it is kept really low (about 20%).
Your help is greatly appreciated.
- CDHow necessary is the Begin Tran/End Tran? If you don't need to make a
series of updates atomic then leave this out.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
<crbd98@.yahoo.com> wrote in message
news:edf41e63.0402041449.724d721b@.posting.google.com...
Greetings,
In our current project, we use a combination of SQL Server 2000 and
ADO. Our database is very simple and small: just about 10 tables with
300K records at most (in just one of the tables). We need, however,
very fast responses to our queries. With that in mind, we designed and
optimized all queries and indices in such a way that each query takes
less that 20 milliseconds, as measured using the SQL profiler under a
normal load.
Each client application opens a single connection to the database and
the queries are funneled through that connection. Each query is a
individual transaction, i.e. it is fenced by Begin Tran...End Tran. We
use mostly stored procedures, which are executed via the _Command
object from ADO. In just a couple of cases, we use _Recordset.
Under a *stress* load, one client can submit 20 transactions/sec to
the server.
In this scenario, I noticed that, sometimes, many commands were taking
almost two orders of magnitude more than under the normal load. I used
SQL Profiler to monitor all Statements and SPs taking longer tha 100
msec and, to my surprise, found that, every so often, some command or
SP would take more than 1-2 seconds. What is interesting is that many
of these commands are IF @.@.TRANCOUNT > 0 COMMIT TRAN, which, I think,
ADO implicitly sends to the server. Those usually show with duration 0
(Zero) under normal load.
What I wanted from you, were some ideas on how to go about
troubleshooting this problem, by identifying the underlying cause for
such poor performance. The problem does not seem associated with a
particular command or SP. It also does not seem related to CPU
contention on the server because it is kept really low (about 20%).
Your help is greatly appreciated.
- CD|||sounds like some blocking issues.
when you run your tests, monitor blocks in SQL Server (and\or Deadlocks)
do your sprocs have NOLOCK Hints in them when appropriate ?
Or setting transaction isolation level to READ UNCOMMITTED (Where
Appropriate).
probably worth looking into.
cheers
Greg Jackson
PDX, ORsql

Monday, March 19, 2012

Defragmentation of table

We are using database having a current size of over 300GB
to store attachment files in an image column of a table.
is the approach of exporting and importing data records
from the table a good way to defragment the table? if so,
is it possible to export the data records as a flat file
to a tape drive.
thanks.
There are commands in SQL Server to defrag and reorg. I strongly suggest you start by reading below
excellent whitepaper:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SY" <anonymous@.discussions.microsoft.com> wrote in message
news:1dd4a01c454d4$11ae92f0$a301280a@.phx.gbl...
> We are using database having a current size of over 300GB
> to store attachment files in an image column of a table.
> is the approach of exporting and importing data records
> from the table a good way to defragment the table? if so,
> is it possible to export the data records as a flat file
> to a tape drive.
> thanks.
>

Defragmentation of table

We are using database having a current size of over 300GB
to store attachment files in an image column of a table.
is the approach of exporting and importing data records
from the table a good way to defragment the table? if so,
is it possible to export the data records as a flat file
to a tape drive.
thanks.There are commands in SQL Server to defrag and reorg. I strongly suggest you
start by reading below
excellent whitepaper:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SY" <anonymous@.discussions.microsoft.com> wrote in message
news:1dd4a01c454d4$11ae92f0$a301280a@.phx
.gbl...
> We are using database having a current size of over 300GB
> to store attachment files in an image column of a table.
> is the approach of exporting and importing data records
> from the table a good way to defragment the table? if so,
> is it possible to export the data records as a flat file
> to a tape drive.
> thanks.
>

Defragmentation of table

We are using database having a current size of over 300GB
to store attachment files in an image column of a table.
is the approach of exporting and importing data records
from the table a good way to defragment the table? if so,
is it possible to export the data records as a flat file
to a tape drive.
thanks.There are commands in SQL Server to defrag and reorg. I strongly suggest you start by reading below
excellent whitepaper:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SY" <anonymous@.discussions.microsoft.com> wrote in message
news:1dd4a01c454d4$11ae92f0$a301280a@.phx.gbl...
> We are using database having a current size of over 300GB
> to store attachment files in an image column of a table.
> is the approach of exporting and importing data records
> from the table a good way to defragment the table? if so,
> is it possible to export the data records as a flat file
> to a tape drive.
> thanks.
>

Saturday, February 25, 2012

Deferred constraints?

Is this on the list for Katmai? (BOL shows only what is available in the current CTP, so it is hard to know).

thanks

This is not planned for Katmai. -- Umachandar Jayachandran Microsoft SQL Server Performance Team SQL Server Engine Team Tips Blog at http://blogs.msdn.com/sqltips/default.aspx This posting is provided "AS IS" with no warranties, and confers no rights. "Lakusha"@.discussions.microsoft.com wrote on Thu, 12 Jul 2007 07:11:08 -0700: L> Is this on the list for Katmai? (BOL shows only what is available in L> the current CTP, so it is hard to know). L> thanks

Friday, February 24, 2012

Default values for StartDate and EndDate

Hi,
I have Start date and End date as parameters. Now is there a way to have the
start date always default to the start date of the current month...i.e
1/CurrentMonth/CurrentYear and EndDate default to 31 or 30th of the current
month & current year?
How can this be done?
Thanks
--
pmudHi,
I have solved half the question reading from other posts. For the start date
I used =DateTime.Now.Addmonths(-1) . So it went back to the previous month.
But how do i set the day to 30 ?
--
pmud
"pmud" wrote:
> Hi,
> I have Start date and End date as parameters. Now is there a way to have the
> start date always default to the start date of the current month...i.e
> 1/CurrentMonth/CurrentYear and EndDate default to 31 or 30th of the current
> month & current year?
> How can this be done?
> Thanks
> --
> pmud|||Enter this for your (Non-Queried) parameter defaults:
StartDate... (this is in the format "m/d/yyyy" which could be changed)
=CDate(Month(Now()).ToString & "/1/" &
Year(Now()).ToString).ToShortDateString
EndDate...(This takes the first day of the next month and subtracts one day.)
=DateAdd(DateInterval.Day, -1, (DateAdd(DateInterval.Month, 1,
CDate(Month(Now()).ToString & "/1/" & Year(Now()).ToString)
))).ToShortDateString
Hoep this helps. If anyone has a better solution I'd be happy to see it.
Fred
"pmud" wrote:
> Hi,
> I have Start date and End date as parameters. Now is there a way to have the
> start date always default to the start date of the current month...i.e
> 1/CurrentMonth/CurrentYear and EndDate default to 31 or 30th of the current
> month & current year?
> How can this be done?
> Thanks
> --
> pmud|||Hi,
I put the logic into a Stored procedure and created a new dataset in the
report designer that references this SP. Then just reference the dataset in
the report paramaters section.
It suited what I needed here as multiple reports have the same default start
and end dates so rather than creating logic in each report the single SP will
do for all the reports. But that may not suit others.
"FredP" wrote:
> Enter this for your (Non-Queried) parameter defaults:
> StartDate... (this is in the format "m/d/yyyy" which could be changed)
> =CDate(Month(Now()).ToString & "/1/" &
> Year(Now()).ToString).ToShortDateString
>
> EndDate...(This takes the first day of the next month and subtracts one day.)
> =DateAdd(DateInterval.Day, -1, (DateAdd(DateInterval.Month, 1,
> CDate(Month(Now()).ToString & "/1/" & Year(Now()).ToString)
> ))).ToShortDateString
> Hoep this helps. If anyone has a better solution I'd be happy to see it.
> Fred
>
> "pmud" wrote:
> > Hi,
> >
> > I have Start date and End date as parameters. Now is there a way to have the
> > start date always default to the start date of the current month...i.e
> > 1/CurrentMonth/CurrentYear and EndDate default to 31 or 30th of the current
> > month & current year?
> >
> > How can this be done?
> >
> > Thanks
> > --
> > pmud

default values

Is it possible to set report parameter of type datetime on current date in case when user don't specify value?
Thx.Yes you can and it's dead easy.
In the Report Parameters dialog, in the Default Values section at the borrom of the form, select Non-queried and use the following expression:
=Globals!ExecutionTime
That'll insert the exact date and time of when the report is run as a default value if one isn't entered.
DF
"AG, NLB d.d." wrote:
> Is it possible to set report parameter of type datetime on current date in case when user don't specify value?
> Thx.|||is it possible to make yesterday as default value?
>--Original Message--
>Yes you can and it's dead easy.
>In the Report Parameters dialog, in the Default Values
section at the borrom of the form, select Non-queried and
use the following expression:
>=Globals!ExecutionTime
>That'll insert the exact date and time of when the report
is run as a default value if one isn't entered.
>DF
>"AG, NLB d.d." wrote:
>> Is it possible to set report parameter of type datetime
on current date in case when user don't specify value?
>> Thx.
>.
>

Sunday, February 19, 2012

default value in datetime column

hi, i was wondering how to set default value in the datetime column of the database so that it will enter current date and time if one is not provided when a row is populated. is there a store procedure to do this? or built-in function?

mp

If you go to Modify view of the table in either Enterprise Manager or SQL Server Management Studio and select that database field, there should be a property where you can specify the default value. Enter GETDATE() as the default value.|||

There are 2 ways to add default value as Today's Date

1) Using QueryAnalyzer

alter table <TableName>
add <ColumnName> DateTime default getdate()

//This will add one column with Default value as Today's date. If you want to add to existing column...2)

2) go to Enterprise Manager, ... , select your table, Right click, go to Design Table and open...

place the mouse cusor in which column you want to put default value, When you place the mouse cursor bottom of this you will see Columns Tab-->Default value TextBox put getdate() and Save . Now insert any row it will automatically enters to days date

|||

thanks, just what i needed!

Happy New Year!!!

mp

Friday, February 17, 2012

Default Value - I see the finish line!

Third Posting on how the heck Report Manager sets the "Has Default" property,
specifically the method it uses. Current, RS drops that property when you
copy a report, hence making Snap Shots impossible when Snapping a copy of
another report. I'm doing this in an application.
Comon someone, I get this down and this baby gets thru QA!!!
My kingdom for the answer!!
thank you in advanceTake a look at
http://msdn.microsoft.com/library/en-us/rsprog/htm/rsp_ref_soapapi_ir_5zec.asp
for working with ReportParameter Objects
and
http://msdn.microsoft.com/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_48jc.asp
for how to call SetReportParameters method.
Some examples for working with report parms can be found on:
http://groups.google.com/groups?q=SetReportParameters&hl=en&lr=&group=microsoft.public.sqlserver.reportingsvcs&selm=OlRQmQDuEHA.3860%40TK2MSFTNGP09.phx.gbl&rnum=8
http://groups.google.com/groups?hl=en&lr=&frame=right&th=3c7cb0c51785eb58&seekm=9F17B95C-B484-46D9-A7F0-FD7BE91DE8EB%40microsoft.com#link7
Suspect you need to 1st call GetReportParameters against old report to
obtain parameters, then SetReportParameters against copy fo the report you
are making.
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:6320E87B-95CA-4DA7-85FB-B2FCF05198E2@.microsoft.com...
> Third Posting on how the heck Report Manager sets the "Has Default"
> property,
> specifically the method it uses. Current, RS drops that property when you
> copy a report, hence making Snap Shots impossible when Snapping a copy of
> another report. I'm doing this in an application.
> Comon someone, I get this down and this baby gets thru QA!!!
> My kingdom for the answer!!
> thank you in advance|||John,
I do as the docs and you speak of. The twist is, you can't set a default
value if the report does not have the "has default" check box ichecked. It
works great if that darn check box is checked. Unfortunately, when you do a
Copy of a report, using CreateReport(), it for some reason loses the "Has
Default" property, meaning you can't progamatically set it, and hence, cannot
create a snapshot from it. I know it's possible, since you can do it in
Report Manager. What I can't figure out is how to progamatically set the "Has
Default" property of the parameter. The "Has Default" property is not listed
as a public of the ReportParameter Class.
"John H. Miller" wrote:
> Take a look at
> http://msdn.microsoft.com/library/en-us/rsprog/htm/rsp_ref_soapapi_ir_5zec.asp
> for working with ReportParameter Objects
> and
> http://msdn.microsoft.com/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_48jc.asp
> for how to call SetReportParameters method.
> Some examples for working with report parms can be found on:
> http://groups.google.com/groups?q=SetReportParameters&hl=en&lr=&group=microsoft.public.sqlserver.reportingsvcs&selm=OlRQmQDuEHA.3860%40TK2MSFTNGP09.phx.gbl&rnum=8
> http://groups.google.com/groups?hl=en&lr=&frame=right&th=3c7cb0c51785eb58&seekm=9F17B95C-B484-46D9-A7F0-FD7BE91DE8EB%40microsoft.com#link7
> Suspect you need to 1st call GetReportParameters against old report to
> obtain parameters, then SetReportParameters against copy fo the report you
> are making.
> --
> -- "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> jhmiller@.online.microsoft.com
> "Eddie" <Eddie@.discussions.microsoft.com> wrote in message
> news:6320E87B-95CA-4DA7-85FB-B2FCF05198E2@.microsoft.com...
> > Third Posting on how the heck Report Manager sets the "Has Default"
> > property,
> > specifically the method it uses. Current, RS drops that property when you
> > copy a report, hence making Snap Shots impossible when Snapping a copy of
> > another report. I'm doing this in an application.
> >
> > Comon someone, I get this down and this baby gets thru QA!!!
> > My kingdom for the answer!!
> >
> > thank you in advance
>
>