DB Backup - Single User Mode

Tx for your responses.
I completely understand and accept that there is no need to do a
backup in single user mode, but for one case if the server is already
in single user mode ..
Here is what I found, in SQL 2000 it was possible to do the
backups(in single user mode) while the SQL 2005 server fails to accept
the connection for backup (the same code/binary SQL-DMO statements are
used for both).
Are there any major changes between SQL server 2000 and SQL server
2005 ?
On Oct 31, 3:02 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> If you mean regular backups (usingBACKUPDATABASE andBACKUPLOG commands), then no, no need to set
> the database tosingleusermode...
> --
> Tibor Karaszi,SQLServer MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "velu5" <thirumalaiv...@.gmail.com> wrote in message
> news:1193743910.012534.3610@.v29g2000prd.googlegrou ps.com...
>
What's New in SQL Server 2005:
http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx
http://technet.microsoft.com/tr-tr/library/ms170363(en-us).aspx
Ekrem nsoy
"velu5" <thirumalaivelu@.gmail.com> wrote in message
news:eb680ec9-d7c5-40c2-bec6-bbbdeada23af@.e23g2000prf.googlegroups.com...
> Tx for your responses.
> I completely understand and accept that there is no need to do a
> backup in single user mode, but for one case if the server is already
> in single user mode ..
> Here is what I found, in SQL 2000 it was possible to do the
> backups(in single user mode) while the SQL 2005 server fails to accept
> the connection for backup (the same code/binary SQL-DMO statements are
> used for both).
> Are there any major changes between SQL server 2000 and SQL server
> 2005 ?
>
> On Oct 31, 3:02 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>

DB Backup - Single User Mode

Hello ,
Is it possible/recommended to do SQL server instance backups in Single
user mode ?
Thanks in advance,
atvHi
No need. BACKUP database command actually does nnot blok others
"velu5" <thirumalaivelu@.gmail.com> wrote in message
news:1193743670.860831.196700@.k35g2000prh.googlegroups.com...
> Hello ,
> Is it possible/recommended to do SQL server instance backups in Single
> user mode ?
> Thanks in advance,
> atv
>|||Dear Velu,
Can I know why you want to do the database backups in single user mode?
Regards
Balaji
"velu5" wrote:
> Hello ,
> Is it possible/recommended to do SQL server instance backups in Single
> user mode ?
> Thanks in advance,
> atv
>|||The SQL Backup command is non-blocking and provides a transactionally
consistent backup without any additional intervention such as single-user
mode.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"velu5" <thirumalaivelu@.gmail.com> wrote in message
news:1193743670.860831.196700@.k35g2000prh.googlegroups.com...
> Hello ,
> Is it possible/recommended to do SQL server instance backups in Single
> user mode ?
> Thanks in advance,
> atv
>

DB Backup - Single User Mode

Hello ,
Is it possible/recommended to do SQL server instance backups in Single
user mode ?
Thanks in advance,
atvIf you mean regular backups (using BACKUP DATABASE and BACKUP LOG commands), then no, no need to set
the database to single user mode...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"velu5" <thirumalaivelu@.gmail.com> wrote in message
news:1193743910.012534.3610@.v29g2000prd.googlegroups.com...
> Hello ,
> Is it possible/recommended to do SQL server instance backups in Single
> user mode ?
> Thanks in advance,
> atv
>|||Tx for your responses.
I completely understand and accept that there is no need to do a
backup in single user mode, but for one case if the server is already
in single user mode ..
Here is what I found, in SQL 2000 it was possible to do the
backups(in single user mode) while the SQL 2005 server fails to accept
the connection for backup (the same code/binary SQL-DMO statements are
used for both).
Are there any major changes between SQL server 2000 and SQL server
2005 ?
On Oct 31, 3:02 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> If you mean regular backups (usingBACKUPDATABASE andBACKUPLOG commands), then no, no need to set
> the database tosingleusermode...
> --
> Tibor Karaszi,SQLServer MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "velu5" <thirumalaiv...@.gmail.com> wrote in message
> news:1193743910.012534.3610@.v29g2000prd.googlegroups.com...
> > Hello ,
> > Is it possible/recommended to doSQLserver instance backups inSingle
> >usermode?
> > Thanks in advance,
> >atv|||What's New in SQL Server 2005:
http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx
http://technet.microsoft.com/tr-tr/library/ms170363(en-us).aspx
--
Ekrem Önsoy
"velu5" <thirumalaivelu@.gmail.com> wrote in message
news:eb680ec9-d7c5-40c2-bec6-bbbdeada23af@.e23g2000prf.googlegroups.com...
> Tx for your responses.
> I completely understand and accept that there is no need to do a
> backup in single user mode, but for one case if the server is already
> in single user mode ..
> Here is what I found, in SQL 2000 it was possible to do the
> backups(in single user mode) while the SQL 2005 server fails to accept
> the connection for backup (the same code/binary SQL-DMO statements are
> used for both).
> Are there any major changes between SQL server 2000 and SQL server
> 2005 ?
>
> On Oct 31, 3:02 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> If you mean regular backups (usingBACKUPDATABASE andBACKUPLOG commands),
>> then no, no need to set
>> the database tosingleusermode...
>> --
>> Tibor Karaszi,SQLServer
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "velu5" <thirumalaiv...@.gmail.com> wrote in message
>> news:1193743910.012534.3610@.v29g2000prd.googlegroups.com...
>> > Hello ,
>> > Is it possible/recommended to doSQLserver instance backups inSingle
>> >usermode?
>> > Thanks in advance,
>> >atv
>|||Hmm, below worked just fine on my machine (2005 with sp2):
USE master
ALTER DATABASE pubs SET SINGLE_USER
BACKUP DATABASE pubs TO DISK = 'C:\pubs.bak'
I can only assume that your DMO code for some reason tries to open a connection to the database on
2005 and which causes the failure. I'd run a Profiler trace to verify what TSQL is submitted.
Assuming this is your own code (using DMO - which is how I read your post), then it might be
difficult to do something... Except for working with the code to see if can stay away from the
database. In the end you might have to construct the backup command and execute is using
.ExecuteImmediately or something similar.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"velu5" <thirumalaivelu@.gmail.com> wrote in message
news:eb680ec9-d7c5-40c2-bec6-bbbdeada23af@.e23g2000prf.googlegroups.com...
> Tx for your responses.
> I completely understand and accept that there is no need to do a
> backup in single user mode, but for one case if the server is already
> in single user mode ..
> Here is what I found, in SQL 2000 it was possible to do the
> backups(in single user mode) while the SQL 2005 server fails to accept
> the connection for backup (the same code/binary SQL-DMO statements are
> used for both).
> Are there any major changes between SQL server 2000 and SQL server
> 2005 ?
>
> On Oct 31, 3:02 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> If you mean regular backups (usingBACKUPDATABASE andBACKUPLOG commands), then no, no need to set
>> the database tosingleusermode...
>> --
>> Tibor Karaszi,SQLServer
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "velu5" <thirumalaiv...@.gmail.com> wrote in message
>> news:1193743910.012534.3610@.v29g2000prd.googlegroups.com...
>> > Hello ,
>> > Is it possible/recommended to doSQLserver instance backups inSingle
>> >usermode?
>> > Thanks in advance,
>> >atv
>

DB Backup - Single User Mode

Hello ,

Is it possible/recommended to do SQL server instance backups in Single
user mode ?

Thanks in advance,
atv"velu5" <thirumalaivelu@.gmail.comwrote in message
news:1193743908.840076.37120@.t8g2000prg.googlegrou ps.com...

Quote:

Originally Posted by

Hello ,
>
Is it possible/recommended to do SQL server instance backups in Single
user mode ?
>
Thanks in advance,
atv
>

It's possible. I can't think of a reason to recommend it unless you
especially needed to prevent any changes (for example if you were backing up
in advance of an upgrade or planning to decommission the original database).

--
David Portas|||velu5 (thirumalaivelu@.gmail.com) writes:

Quote:

Originally Posted by

Is it possible/recommended to do SQL server instance backups in Single
user mode ?

Just to emphasize what David said: Yes, it's possible, but the only reason
you would do it, is because you have already put the database in single-
user mode. That is, it's works perfectly well to have backups running with
users active.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Tx for your response.
I completely understand and accept that there is no need to do a
backup in single user mode, but for one case if the server is already
in single user mode ..
Here is what I found, in SQL 2000 it was possible to do the
backups(in single user mode) while the SQL 2005 server fails to accept
the connection for backup (the same code/binary SQL-DMO statements are
used for both).

Are there any major changes between SQL server 2000 and SQL server
2005 ?

On Oct 31, 3:00 am, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

velu5 (thirumalaiv...@.gmail.com) writes:

Quote:

Originally Posted by

Is it possible/recommended to doSQLserver instance backups inSingle
usermode?

>
Just to emphasize what David said: Yes, it's possible, but the only reason
you would do it, is because you have already put the database insingle-usermode. That is, it's works perfectly well to have backups running with
users active.
>
--
Erland Sommarskog,SQLServer MVP, esq...@.sommarskog.se
>
Books Online forSQLServer 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online forSQLServer 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||"velu5" <thirumalaivelu@.gmail.comwrote in message
news:3686490e-167c-4062-9ecf-2c043615e22a@.s19g2000prg.googlegroups.com...

Quote:

Originally Posted by

Tx for your response.
I completely understand and accept that there is no need to do a
backup in single user mode, but for one case if the server is already
in single user mode ..
Here is what I found, in SQL 2000 it was possible to do the
backups(in single user mode) while the SQL 2005 server fails to accept
the connection for backup (the same code/binary SQL-DMO statements are
used for both).
>

Hmm, you sure you don't something else already making a connection?

Quote:

Originally Posted by

Are there any major changes between SQL server 2000 and SQL server
2005 ?
>

Yes. Many changes. But I don't know any specifically that would cause this
particular issue.

Quote:

Originally Posted by

On Oct 31, 3:00 am, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

>velu5 (thirumalaiv...@.gmail.com) writes:

Quote:

Originally Posted by

Is it possible/recommended to doSQLserver instance backups inSingle
>usermode?

>>
>Just to emphasize what David said: Yes, it's possible, but the only
>reason
>you would do it, is because you have already put the database
>insingle-usermode. That is, it's works perfectly well to have backups
>running with
>users active.
>>
>--
>Erland Sommarskog,SQLServer MVP, esq...@.sommarskog.se
>>
>Books Online forSQLServer 2005
>athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
>Books Online forSQLServer 2000
>athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||velu5 (thirumalaivelu@.gmail.com) writes:

Quote:

Originally Posted by

Tx for your response.
I completely understand and accept that there is no need to do a
backup in single user mode, but for one case if the server is already
in single user mode ..
Here is what I found, in SQL 2000 it was possible to do the
backups(in single user mode) while the SQL 2005 server fails to accept
the connection for backup (the same code/binary SQL-DMO statements are
used for both).
>
Are there any major changes between SQL server 2000 and SQL server
2005 ?

Well, DMO became dusty and old with SQL 2005, and it's possible
that DMO somehow manages to cause double connections.

I've always stayed away from DMO (and its successor SMO), so I cannot
really say much more.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Db Backup

I need to creat SQL Database backup that I can have a user with no SQL serve
r
installed on his/her PC to be able to execute the Backup Job how can I use i
n
exe.
thanks,
OmranHi
You can write a program in ADO or ADO.NET that will execute the backup
command for you.
Alternatively you can set up a SQL Agent job on the server that will
automatically backup the database at a given time/date. Check out the backup
database wizard or maintenance plans. This would require no intervention fro
m
the user.
John
"Omran Abdurrahman" wrote:

> I need to creat SQL Database backup that I can have a user with no SQL ser
ver
> installed on his/her PC to be able to execute the Backup Job how can I use
in
> exe.
> thanks,
> Omransql

Db Backup

I need to creat SQL Database backup that I can have a user with no SQL server
installed on his/her PC to be able to execute the Backup Job how can I use in
exe.
thanks,
OmranHi
You can write a program in ADO or ADO.NET that will execute the backup
command for you.
Alternatively you can set up a SQL Agent job on the server that will
automatically backup the database at a given time/date. Check out the backup
database wizard or maintenance plans. This would require no intervention from
the user.
John
"Omran Abdurrahman" wrote:
> I need to creat SQL Database backup that I can have a user with no SQL server
> installed on his/her PC to be able to execute the Backup Job how can I use in
> exe.
> thanks,
> Omran

DB access revoked...

I have two servers (A & B) with 7 DBs and have created a
user on each server with DB_OWNER role.
When I restore a DB backup from one server A to B, the
user on server B, loses all its access to these 7 DBs from
a server level, though I can see that this user exists
from a DB perspective.
Any ideas why and how this can be corrected? Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_0094_01C3810D.6E860260
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Check out sp_change_users_login in the BOL.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Rob" <rhchin@.hotmail.com> wrote in message =news:0dfc01c3812e$29ce71e0$a001280a@.phx.gbl...
I have two servers (A & B) with 7 DBs and have created a user on each server with DB_OWNER role.
When I restore a DB backup from one server A to B, the user on server B, loses all its access to these 7 DBs from a server level, though I can see that this user exists from a DB perspective.
Any ideas why and how this can be corrected? Thanks.
--=_NextPart_000_0094_01C3810D.6E860260
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Check out sp_change_users_login in the =BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Rob" wrote in message news:0dfc01c3812e$29=ce71e0$a001280a@.phx.gbl...I have two servers (A & B) with 7 DBs and have created a user on =each server with DB_OWNER role.When I restore a DB backup from one =server A to B, the user on server B, loses all its access to these 7 DBs from =a server level, though I can see that this user exists from a DB perspective.Any ideas why and how this can be corrected? Thanks.

---

--=_NextPart_000_0094_01C3810D.6E860260--

DB Access from different m/c

when i access the database from different DB server getting the following error

Msg 18452, Sev 14, State 1, Line 1 : Login failed for user ''. The user is not associated with a trusted SQL Server connection. [SQLSTATE 28000]

Thanks,
Ravinder

Your SQL Server is set to Windows Authentication only. You need to logon with a user which has granted access to the server. You can′t provide a userId and passwort in the connecting string because this will be ognored in the case of Windows Auth. only. Another option would be, if feasable for your to switch the authentication mode to mixed.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

DateTime Query

Hi, am trying to build a scheduling system within my SQL Server application. Can someone point me in a good direction please?

OK, A user can select that they want something to happen Weekly, and on each Tuesday of every week. They of course can select any day from Monday through to Sunday. I would like to know how to take this data, and through a stored procedure update a table to set the "next execution date".

I have sorted the Daily timetable for each time, and the Monthly on a certain date seems easy enough, but I cant get the Weekly on a certain Day sorted. Any advice would be great!

Maybe you could post some code of your table and query...?

Though I'm not sure why you have multiple tables; monthly, weekly, daily.

You should just need one

NextExecutionMgr( DueDate datetime, FreqIntvl varchar(2), FreqAmt int, RecordKey varchar(200) )

index on DueDate, most likely a second index on RecordKey

The first item on your DueDate index is the next one to be processed.

When its time comes and once it is processesed you just adjust the date:

Code Snippet

case FreqIntvl when 'dy' then DueDate = DateAdd(dy, FreqAmt, DueDate)

when 'wk' then DueDate = DateAdd(wk, FreqAmt, DueDate)

etc.

end

(doesn't it suck that dateadd doesn't accept a variable for parameter one?)

|||

Why re-invent the wheel?

I would recommend exploring the SQL Agent Service, since it has full features calendaring and scheduling already built-in.

And if you are using SQL 2005 Express, which doens't include SQL Agent, you could explore a combination of using the Windows Scheduler service and SQLCmd.exe.

|||

Arnie, quite true.

I guess it just depends on what it is he's trying to schedule.

Agent is perfect for scheduled system level events and tasks.

But if he's trying to kick off application events with 1,000's of users, that a different thing.

Lotsa cats...

|||

And the skin just regrows...

I suspect that the solution will evolve into a combination of efforts -your outline about how to manage a 'queue' table, and some form of a scheduled process to 'POP' the queue.

There just isn't enough information to point the OP in the 'best' direction. SQL Agent, Notification Service, Service Broker Queues, some 'homegrown' hybrid, ...

DateTime Parameter Validation

Hello Group,
I am having a report parameter which is of the type DateTime. I get a simple
textbox for that and if the user types in some invalide date an exception is
thrown on the UI. I want to handle this parameter as soon as user types it
in and clicks on the 'View Report' button. Can i some how pass this typed-in
value to the VB.Net code that i will write in the rdl and validate it there
itself? I can pass it to my stored proc by making the type of the parameter
as 'String' and do the validation there. But don't want to pass this value
to the stored proc.
Thanks in advance
AtulI have an example on www.msbicentral of a method to use a string as a data
parameter, but format it using functions... ( the name is something like
formatted date...)
I wonder if you couldn't do the same as in the example, except instead of
calling the format method in VB.net, call Code.whatever, and use a code
function which you have written...I've never done exactly that but it might
be worth a few minutes to try...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Atul" <atul.anjankar@.approva.net> wrote in message
news:evPcP$jGFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hello Group,
> I am having a report parameter which is of the type DateTime. I get a
> simple textbox for that and if the user types in some invalide date an
> exception is thrown on the UI. I want to handle this parameter as soon as
> user types it in and clicks on the 'View Report' button. Can i some how
> pass this typed-in value to the VB.Net code that i will write in the rdl
> and validate it there itself? I can pass it to my stored proc by making
> the type of the parameter as 'String' and do the validation there. But
> don't want to pass this value to the stored proc.
> Thanks in advance
> Atul
>|||Is there a reason you are using text for the datetime parameter? You can
have it be datetime. Menu Report, Parameters
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Atul" <atul.anjankar@.approva.net> wrote in message
news:evPcP$jGFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hello Group,
> I am having a report parameter which is of the type DateTime. I get a
simple
> textbox for that and if the user types in some invalide date an exception
is
> thrown on the UI. I want to handle this parameter as soon as user types it
> in and clicks on the 'View Report' button. Can i some how pass this
typed-in
> value to the VB.Net code that i will write in the rdl and validate it
there
> itself? I can pass it to my stored proc by making the type of the
parameter
> as 'String' and do the validation there. But don't want to pass this value
> to the stored proc.
> Thanks in advance
> Atul
>|||Hello Bruce,
If i keep the parameter as datetime, and if the user types in some invalid
date, MSSSRS throws and exception on the UI. I using MSSSRS's UI for the
parameters' input and not any aspx page. If i want to avoid the exception i
will have to accept it as a string and pass it to the stored proc and chekc
the validity of the user input there.
Atul
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:e0$ga%23nGFHA.3076@.tk2msftngp13.phx.gbl...
> Is there a reason you are using text for the datetime parameter? You can
> have it be datetime. Menu Report, Parameters
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Atul" <atul.anjankar@.approva.net> wrote in message
> news:evPcP$jGFHA.576@.TK2MSFTNGP15.phx.gbl...
>> Hello Group,
>> I am having a report parameter which is of the type DateTime. I get a
> simple
>> textbox for that and if the user types in some invalide date an exception
> is
>> thrown on the UI. I want to handle this parameter as soon as user types
>> it
>> in and clicks on the 'View Report' button. Can i some how pass this
> typed-in
>> value to the VB.Net code that i will write in the rdl and validate it
> there
>> itself? I can pass it to my stored proc by making the type of the
> parameter
>> as 'String' and do the validation there. But don't want to pass this
>> value
>> to the stored proc.
>> Thanks in advance
>> Atul
>>
>|||Your choice is to accept the UI from the Report Manager (which in most cases
what I do) or pass it to the SP for validation.
True, this "The value provided for the report parameter 'StartDate' is not
valid for its type. " is not the most user friendly message.
Still, I haven't really had a problem with the users. I'm afraid that we
don't have a whole lot of options with this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Atul" <atul.anjankar@.approva.net> wrote in message
news:uugSVIzHFHA.3612@.TK2MSFTNGP09.phx.gbl...
> Hello Bruce,
> If i keep the parameter as datetime, and if the user types in some invalid
> date, MSSSRS throws and exception on the UI. I using MSSSRS's UI for the
> parameters' input and not any aspx page. If i want to avoid the exception
i
> will have to accept it as a string and pass it to the stored proc and
chekc
> the validity of the user input there.
>
> Atul
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:e0$ga%23nGFHA.3076@.tk2msftngp13.phx.gbl...
> > Is there a reason you are using text for the datetime parameter? You can
> > have it be datetime. Menu Report, Parameters
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Atul" <atul.anjankar@.approva.net> wrote in message
> > news:evPcP$jGFHA.576@.TK2MSFTNGP15.phx.gbl...
> >> Hello Group,
> >> I am having a report parameter which is of the type DateTime. I get a
> > simple
> >> textbox for that and if the user types in some invalide date an
exception
> > is
> >> thrown on the UI. I want to handle this parameter as soon as user types
> >> it
> >> in and clicks on the 'View Report' button. Can i some how pass this
> > typed-in
> >> value to the VB.Net code that i will write in the rdl and validate it
> > there
> >> itself? I can pass it to my stored proc by making the type of the
> > parameter
> >> as 'String' and do the validation there. But don't want to pass this
> >> value
> >> to the stored proc.
> >>
> >> Thanks in advance
> >> Atul
> >>
> >>
> >
> >
>sql

Datetime in Where clause

I have a simple stored procedure that updates the loggedout field, which is
a
datetime field, in a table based on the user id and login date (which is als
o
a datetime field). Since a user can login multiple times throughout the day,
I need the Where clause to include the time, which is part of the datetime
being passed in.
When I run the following sp and pass in a datetime field, for example
5/15/2005 1:12:22 PM, the table never updates, even though there is a field
with that date/time in it.
PROCEDURE dbo.UpdateLoginActivity
(
@.fldLogoutDate datetime,
@.fldUserId int,
@.fldLoginDate datetime
)
AS
UPDATE tblLoginActivity
SET fldLogoutDate = @.fldLogoutDate
WHERE (fldUserId = @.fldUserId) AND (fldLoginDate = @.fldLoginDate)
How can I use a datetime field effectively in a Where clause, so that it
finds the exact row based on the date & time?datetime stores milliseconds:
select getdate()
---
2006-05-15 16:31:33.750
(1 row(s) affected)
YOu must either provide milliseconds in your parameter or modify your
update
UPDATE tblLoginActivity
SET fldLogoutDate = @.fldLogoutDate
WHERE (fldUserId = @.fldUserId) AND (fldLoginDate >= @.fldLoginDate)
AND (fldLoginDate < dateadd(ms,1000,@.fldLoginDate) )|||It worked fine for me.
Did you mean to use 5/15/2006 1:12:22 PM instead of 5/15/2005 1:12:22
PM ?
Richard wrote:
> I have a simple stored procedure that updates the loggedout field, which i
s a
> datetime field, in a table based on the user id and login date (which is a
lso
> a datetime field). Since a user can login multiple times throughout the da
y,
> I need the Where clause to include the time, which is part of the datetime
> being passed in.
> When I run the following sp and pass in a datetime field, for example
> 5/15/2005 1:12:22 PM, the table never updates, even though there is a fiel
d
> with that date/time in it.
> PROCEDURE dbo.UpdateLoginActivity
> (
> @.fldLogoutDate datetime,
> @.fldUserId int,
> @.fldLoginDate datetime
> )
> AS
> UPDATE tblLoginActivity
> SET fldLogoutDate = @.fldLogoutDate
> WHERE (fldUserId = @.fldUserId) AND (fldLoginDate = @.fldLoginDate)
> How can I use a datetime field effectively in a Where clause, so that it
> finds the exact row based on the date & time?|||That worked! Thanks Alexander.
"Alexander Kuznetsov" wrote:

> datetime stores milliseconds:
> select getdate()
> ---
> 2006-05-15 16:31:33.750
> (1 row(s) affected)
> YOu must either provide milliseconds in your parameter or modify your
> update
> UPDATE tblLoginActivity
> SET fldLogoutDate = @.fldLogoutDate
> WHERE (fldUserId = @.fldUserId) AND (fldLoginDate >= @.fldLoginDate)
> AND (fldLoginDate < dateadd(ms,1000,@.fldLoginDate) )
>|||Yes, that is the correct date. See Alexander's post for the syntax, which
worked well for me. Thanks.
"tjolliffe@.hotmail.com" wrote:

> It worked fine for me.
> Did you mean to use 5/15/2006 1:12:22 PM instead of 5/15/2005 1:12:22
> PM ?
>
> Richard wrote:
>

datetime fields and constarints

I've created a user defined datatype called t_date. It's a datetime datatype. I would like to bind a rule to the udt that constrains the time to 12:00am. Can anyone recommend the best way to do this.What about this?

create rule [myrule] as @.date=convert(varchar(10),@.date,101)+' 12:00'
GO|||Yep that works, in fact that's what I've been doing. I was hoping there was a more efficient way to check instead of having to cast to a string and back to a date again. It's not in your example but I cast back to a datetime to do the compare. Not sure if I have to or not.

Do you know how costly casting data in SQL server is?

Thanks for the help :-)|||It doesn't matter how costly it is, 'cause its the only way! You wants it, you pays for it!

blindman|||Thanx blindman, damn good thing my company has deep pockets because weez gonna have to pay|||Pay for processors...pay for memory...make sure they pay you good too!

Seriously, casting/converting datetime and string values is probably not a fast process, but for the string lengths you are dealing with it should not have a big impact on performance. And there is no way around it. MS Access, MS Excel, and other Microsquash products allow you to take the integer portion of the datetime value (a vast operation), but SQL Server uses a different logic for storing datetime values and this method won't work.

blindman

Datetime calculation help

Hi Everyone,
I have got a problem with date calculation. I have a procedure that all
me to insert date into a Table based on user input. The input is a Event
Date and Reminder
Example: if the user Enter an Event Date and choose to a reminder for a
certain event... I need to calculate a date that will be a w prior to the
event date as the reminder
My question is how do I calculate prior w of a certain Date.. e.g Event
Date = 01/14/2005 I want the reminder to be calculate has
Reminder=01/07/2005
Below is my procedure:
CREATE PROCEDURE EventReminder
@.DocketID int,
@.EventName varchar(50),
@.Reminder int,
@.EventNumber int,
@.EventDate varchar(50)
AS
--Declare variables
Declare @.EventStartNum int,
@.EventReminderNum int,
@.EventDate1 datetime,
@.EventNum int
--Initialize the Variables
set @.EventStartNum = 0
set @.EventReminderNum = 0
set @.EventNum = -1
--Delete the Reminder if the DocketID already exist
delete from reminder where DocketID = @.DocketID
--Start the loop
while @.EventStartNum < @.EventNumber
Begin --Start Begin
set @.EventStartNum = @.EventStartNum + 1
--Wly Reminder
if @.EventNumber = 1
begin
while @.Reminder >
@.EventReminderNum
begin
--Increment of the w
set @.EventReminderNum =
@.EventReminderNum + 1
set @.EventDate1 = DATEADD(w,
@.EventReminderNum, @.EventDate)
insert into Reminder
(DocketID, EventDate, EventName, Reminder)
Values
(@.DocketID,convert(varchar(50),@.EventDat
e1,101), @.EventName, @.Reminder)
set @.EventNum = @.EventNum - 1
end
end
--print 'The counter is ' +
convert(varchar(50),@.EventDate1,101)
end --End Begin
GOUse function DATEADD.
Example:
select dateadd(ww, -1, '20050114')
go
AMB
"Roplab" wrote:

> Hi Everyone,
> I have got a problem with date calculation. I have a procedure that al
l
> me to insert date into a Table based on user input. The input is a Event
> Date and Reminder
> Example: if the user Enter an Event Date and choose to a reminder for a
> certain event... I need to calculate a date that will be a w prior to t
he
> event date as the reminder
> My question is how do I calculate prior w of a certain Date.. e.g Even
t
> Date = 01/14/2005 I want the reminder to be calculate has
> Reminder=01/07/2005
> Below is my procedure:
> CREATE PROCEDURE EventReminder
> @.DocketID int,
> @.EventName varchar(50),
> @.Reminder int,
> @.EventNumber int,
> @.EventDate varchar(50)
> AS
> --Declare variables
> Declare @.EventStartNum int,
> @.EventReminderNum int,
> @.EventDate1 datetime,
> @.EventNum int
> --Initialize the Variables
> set @.EventStartNum = 0
> set @.EventReminderNum = 0
> set @.EventNum = -1
>
> --Delete the Reminder if the DocketID already exist
> delete from reminder where DocketID = @.DocketID
> --Start the loop
> while @.EventStartNum < @.EventNumber
> Begin --Start Begin
> set @.EventStartNum = @.EventStartNum + 1
> --Wly Reminder
> if @.EventNumber = 1
> begin
> while @.Reminder >
> @.EventReminderNum
> begin
> --Increment of the w
> set @.EventReminderNum =
> @.EventReminderNum + 1
> set @.EventDate1 = DATEADD(wee
k,
> @.EventReminderNum, @.EventDate)
> insert into Reminder
> (DocketID, EventDate, EventName, Reminder)
> Values
> (@.DocketID,convert(varchar(50),@.EventDat
e1,101), @.EventName, @.Reminder)
> set @.EventNum = @.EventNum -
1
> end
> end
> --print 'The counter is ' +
> convert(varchar(50),@.EventDate1,101)
> end --End Begin
> GO
>
>

DateTime Calculation

'I am writing code that deactivates a user's account if there are 3 failed
login tries within a 10 minute period. I need to take data from a
smallDatetime field in a recordset with only 1 record and subtract 10
minutes. Can someone give me the correct syntax?
Thank you.SELECT DATEADD(MINUTE, -10, GETDATE())
"Jinjer" <Jinjer@.discussions.microsoft.com> wrote in message
news:129C22B7-2B0F-4968-9DCF-5FA99B4244B8@.microsoft.com...
> 'I am writing code that deactivates a user's account if there are 3 failed
> login tries within a 10 minute period. I need to take data from a
> smallDatetime field in a recordset with only 1 record and subtract 10
> minutes. Can someone give me the correct syntax?
> Thank you.|||Thanks. I'll give it a go tomorrow.
--
Jinjer
"Aaron Bertrand [SQL Server MVP]" wrote:

> SELECT DATEADD(MINUTE, -10, GETDATE())
>
> "Jinjer" <Jinjer@.discussions.microsoft.com> wrote in message
> news:129C22B7-2B0F-4968-9DCF-5FA99B4244B8@.microsoft.com...
>
>

Dates, stored procedures and asp.net

Hi,
I am having problems with dates and timestamps...

I have a textbox that allows the user to enter a date - the format is ccyymmdd. I have validation on this to ensure it is in the correct format.

I pass this as a parameter to a sql server stored procedure. In the stored procedure that parameter is defined as datetime and the field in the database it is also datetime.

To cut a long story short...an exception is being thrown! I have convinced myself it is due to the date - everything else I have used before. I am not sure whether I need to do a conversion in SQL perhaps prior to insert but not sure how to do this in any case, or, whether I have to do something in the vb code before loading the parameter??

CREATE PROCEDURE [dateexample]
(@.SomeDate [datetime])

AS INSERT INTO [testdate]
([SomeDate])

VALUES
(@.SomeDate)
GO

Anyone had similar problems / suggestions ? If this is not posted in an appropriate forum I apologise like I say I'm not sure which side the solution lies...

Thanks to all who take the time to read thistry changing your SP to this one..

CREATE PROCEDURE dateexample (@.SomeDate datetime)
AS
begin
INSERT INTO testdate VALUES (@.SomeDate)
end
GO

|||Is the exception being thrown by SQL Server, or by your vb code?|||Just to make sure, since you mention timestamps in your original post. I hope your problem isn't that you're trying to use a timestamp field to store a date/time.
A timestamp is not a date or time field. It's a serial number. The name is somewhat misleading. It's one of the unfortunate legacy things from back in the Sybase days.

From SQL Books-on-line...

timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.

Remarks
The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.

A future release of Microsoft® SQL Server? may modify the behavior of the Transact-SQL timestamp data type to align it with the behavior defined in the standard. At that time, the current timestamp data type will be replaced with a rowversion data type.

Microsoft® SQL Server? 2000 introduces a rowversion synonym for the timestamp data type. Use rowversion instead of timestamp wherever possible in DDL statements.

Datediff formula on insert returning null value

I have a form with two date fields that the user will submit their requested vacation time off with. When they insert it, I am trying to say find the difference between the request_start_date and request_end_date in days MINUS any of the days they would already have off like weekends or holidays that are included in another table. Everything inserts okay, but I am getting null for the request_duration. If I put dates in quotes and run the query it comes back with the right results. If I put the dates in the form and submit it, I get Null for the request_duration.

Thank you in advnace for any help on this!

INSERTrequest(emp_id,request_submit_date, request_start_date,request_end_date,request_duration,request_notes,time_off_id)Select@.emp_id,GETDATE(),@.request_start_date,@.request_end_date, 1 +DATEDIFF(day, @.request_start_date, @.request_end_date) - (selectcount(*)from WeekEndsAndHolidayswhere DayOfWeekDatebetween @.request_start_dateand @.request_end_date),@.request_notes,@.time_off_id

Either request_start_date or request_end_date is coming across as null. Are you sure that request_duration is the only null field?|||

Motley,

Thanks for the response. It took a while to get posted and I figured it out way before it got posted. I had worked on it for about an hour before I posted this and figure it out 5 minutes after I posted it. I didn't have one of my text boxes bound correctly.

datediff (today - date)

Hello

I want to return the number of days between a date in the database and today

something like

SELECT user.fName,
user.lName & " (" & (datediff(now - user.lastVisit)) & " )"
FROM user


I must return

John Turner (38)

where 38 are the days between last visit and now

thank yousee this,now I think you can do it easily

declare @.datevar datetime
select @.datevar = getdate()


*Example for datediff : getting no of days passed since 01-01-2006*/

select datediff(dd,'20060101',@.datevar) [No of days since 01-01-2006]|||it works !

thanks a lot

DateAdd function in Stored Procedure

I need to be able to store a value in a datefield that is 8 hours greater
than another datefield (SchedOpen) which the user updates. What happens is
the user enters local time and I need to also store that time as GMT. I trie
d
this as an alias in a stored procedure: "DATEADD(hh, 8, SchedOpen)" and it
works fine until there is a time when adding 8 hours goes beyond midnight. I
then get a date added to the field which is the year 1900. I suspect that
since the field only contains time the date is defaulted to 1900. There is a
date field in each row with the current date so I need to somehow add that t
o
the GMT field before adding the 8 hours but I just can't figure out how to
do this. Any Ideas? Thanks.Why you need to store it.
you still can access the same field and add the extra 8 hours.
I don't see a problem in adding the hours.
your problem is how to format the date field.
I tried this one
select getdate(),dateadd(hh,8,getdate())
the result was:-
2005-12-07 16:55:13.197 2005-12-08 00:55:13.197
So you can see that I passed the midnight and the and became in a new day.
I still can access the time or the date portions by formating the date.
like in this example:-
select convert(char(8),getdate(),8),convert(cha
r(8),dateadd(hh,8,getdate()),
8)
the result was
17:06:27 01:06:27
I got what I need from the date and i added the hours too.
So all about formating the date.
Thanks,
Nader Shahin
"AkAlan" wrote:

> I need to be able to store a value in a datefield that is 8 hours greater
> than another datefield (SchedOpen) which the user updates. What happens is
> the user enters local time and I need to also store that time as GMT. I tr
ied
> this as an alias in a stored procedure: "DATEADD(hh, 8, SchedOpen)" and i
t
> works fine until there is a time when adding 8 hours goes beyond midnight.
I
> then get a date added to the field which is the year 1900. I suspect that
> since the field only contains time the date is defaulted to 1900. There is
a
> date field in each row with the current date so I need to somehow add that
to
> the GMT field before adding the 8 hours but I just can't figure out how t
o
> do this. Any Ideas? Thanks.
>|||AkAlan (AkAlan@.discussions.microsoft.com) writes:
> I need to be able to store a value in a datefield that is 8 hours
> greater than another datefield (SchedOpen) which the user updates. What
> happens is the user enters local time and I need to also store that time
> as GMT. I tried this as an alias in a stored procedure: "DATEADD(hh, 8,
> SchedOpen)" and it works fine until there is a time when adding 8 hours
> goes beyond midnight. I then get a date added to the field which is the
> year 1900. I suspect that since the field only contains time the date is
> defaulted to 1900. There is a date field in each row with the current
> date so I need to somehow add that to the GMT field before adding the 8
> hours but I just can't figure out how to do this. Any Ideas? Thanks.
There is no time data type in SQL Server (not even in SQL 2005). When you
do not specify a date, the default date is 1900-01-01.
If you want to keep all times within the same date, you will have to
something like:
SELECT convert(datetime, convert(char(8), dateadd(hh, 8, SchedOpen), 108))
or
SELECT dateadd(hh, CASE WHEN SchedOpen < '16:00:00' THEN 8 ELSE -16 END,
SchedOpen)
By the way, in most interfaces you will have to explicitly remove that
date part of 1900-01-01. You may be looking in Enterprise Manager, but
EM is not an application.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks, I did figure out how to add the date field with the time field like
this:
DATEADD(hh, 9, dbo.tblMissions.DateFlown + dbo.tblMtr.StartTime)
This allows me to format the field and present it the way the customer
wants. My real problem is storing the data without have to make design
changes every time we change daylight savings time. I'm using MS Access as
a
front end. Here is the situation...I have a form which the user enters a
local date and time value. I need the report to display the local values as
well as the corresponding GMT based on the local entry. I need to store the
GMT value with each row since I never know on which day the data is being
viewed. I think I'll have to ceate a table and add the daylight savings days
(beging and end) then check the entry date to the table and update the field
with code on the after update event of the local time field. Thanks for your
reply.
"Nader Shahin" wrote:
> Why you need to store it.
> you still can access the same field and add the extra 8 hours.
> I don't see a problem in adding the hours.
> your problem is how to format the date field.
> I tried this one
> select getdate(),dateadd(hh,8,getdate())
> the result was:-
> 2005-12-07 16:55:13.197 2005-12-08 00:55:13.197
> So you can see that I passed the midnight and the and became in a new day.
> I still can access the time or the date portions by formating the date.
> like in this example:-
> select convert(char(8),getdate(),8),convert(cha
r(8),dateadd(hh,8,getdate()
),8)
> the result was
> 17:06:27 01:06:27
> I got what I need from the date and i added the hours too.
> So all about formating the date.
> Thanks,
> Nader Shahin
>
> "AkAlan" wrote:
>