No Date passed

I want to update a record that has a datetime field in it. But what if there is no parameter given for that field, i.e. what is the correct type/value to pass as parameter?

Example: A DOB field for a user profile, but the user doesn't enter his birthday

This is some example code that I use to update:

 Private Sub UpdateDOB(ByVal dob As Date)
 Dim parameters As SqlParameter() = { _
 New SqlParameter("@.Birthday", SqlDbType.DateTime, 8)}
 parameters(0).Value = dob
 'Run Stored Procedure

This gives me the exception "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM" when I don't pass a valid date. I tried passingNothing but same error (in this case it convert dob to "#12:00:00 AM#")

The only thing I can think of is to use Date.MaxValue and then check in application logic, but there must be a better way!use dbnull.value

hth|||ndinakar, I tried this:

User.DOB = System.DBNull.Value

but then I get an error, DBNull.Value cannot be converted to Type Date
how should i assign DBNull.Value ?|||Instead of passing DOB as date pass it as string

Private Sub UpdateDOB(ByVal strDOB As string)
...
cmd.Parameters.Add(New SqlParameter("@.Birthday", SqlDbType.DateTime))
If (strDOB= "") Then
cmd.Parameters("@.Date").Value = sqldatenull
'cmd.Parameters("@.Date").Value = DBNull.Value
Else
 cmd.Parameters("@.Date").Value = DateTime.Parse(strDOB)
End If

HTH|||or this :

myCommand.Parameters.Add(New SqlParameter("@.cusbday",SqlDbType.datetime))
If len(trim(bday.Text)) = 0 Then
 myCommand.Parameters("@.cusbday").Value = SqlDateTime.null
Else
 myCommand.Parameters("@.cusbday").Value = server.htmlencode(DateTime.Parse(bday.Text))
End If

you need to import system.data.sqldypes namespace.

hth|||Thank you

SqlDateTime.Null worked fine as parameter.sql

No administrator privileges

I've logged into our database server to update some reports and found
out that I go to manage RS I have no admin privileges! This happened
all of a sudden as a week ago with the same login details I could do it
just fine and now I just have vanilla screen for users with no rights.
I've most probably stuffed something up without realising it, please
help!Check the websites. Most likely you have turned anonymous on (this is an IIS
thing). If anonymous then you are anonymous, RS doesn't know you are an
admin.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<ocbka1@.gmail.com> wrote in message
news:1145322356.507391.151480@.v46g2000cwv.googlegroups.com...
> I've logged into our database server to update some reports and found
> out that I go to manage RS I have no admin privileges! This happened
> all of a sudden as a week ago with the same login details I could do it
> just fine and now I just have vanilla screen for users with no rights.
> I've most probably stuffed something up without realising it, please
> help!
>|||Thanks!! I've taken anonymous access off but that also means that
whenever I try to access a report (web server is a different box) it's
asking me for username and password. What should I do?

No "Open Table" In SQL Server 2005?

In Enterprise Manager for SQL Server 2000, I used to choose "Open Table..."
and then "Return All rows" to be able to update data directly into a grid.
This tool was rapid and useful, but i'm not able to find a such tool in SQL
Server 2005 environment... Is there a tool like this? If not, somebody know
why Microsoft has removed a awesome tool like it'
David.
Thanks(a) you must be using an old beta:
http://www.aspfaq.com/sql2005/show.asp?id=5
(b) please post SQL Server 2005 questions to the SQL Server 2005 newsgroups:
http://www.aspfaq.com/sql2005/show.asp?id=1
"David Parenteau" <DavidParenteau@.discussions.microsoft.com> wrote in
message news:2344C393-7600-4041-BE01-5E0A10AE33D4@.microsoft.com...
> In Enterprise Manager for SQL Server 2000, I used to choose "Open
> Table..."
> and then "Return All rows" to be able to update data directly into a grid.
> This tool was rapid and useful, but i'm not able to find a such tool in
> SQL
> Server 2005 environment... Is there a tool like this? If not, somebody
> know
> why Microsoft has removed a awesome tool like it'
> David.
> Thanks

Nightly Update / Fallback protection

I have become involved with an assessment of a client's nightly update
procedures, and I keep running into the following practice.
The client consistently copies databases off to separate locations and
performs updates upon the new copy. After successful update, the
updated database is then copied back to the origin location and
becomes the new version. All of this, obviously, to protect
themselves in case of error. I can't help but suspect that SQL Server
provides an easier way to do it.
In the mainframe universe, this used to be accomplished via
generational datasets which would be promoted as the baseline when the
update completes. I guess what I'm asking is if SQL Server provides
any kind of "checkpoint-fallback" capability that the client could use
instead of this monstrous time-consuming practice. When talking
thousands of updates in a batch, I'm not sure transactioning would be
the best way to do it. That would be one big honking rollback.
Thanks in advance to any of you architects out there!
SQL Server 2005 (Enterprise Edition) has, sort of, this. You can create a "database snapshot"
(CREATE DATABASE Copy AS SNAPSHOT OF Orig). The database files for the Copy database are initially
empty and consume close to 0 disk space, i.e., the snapshot takes only a few seconds to take. Then
modify your Orig database. As you modify Orig database, the pages to be modified are first copied to
the Copy databases' database files. This way, the copy database always has the data as it was when
the snapshot was produced. If you need to "fall-back", then you can RESTORE DATABASE Orig FROM
DATABASE_SNAPSHOT 'Copy', and all pages (in the Copy databases' database files) are copied back to
the Orig database' files and you are back where you began. Concept is called "Copy on write".
Same-same (concept) but different (implementation). :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177351816.251254.221810@.b58g2000hsg.googlegr oups.com...
>I have become involved with an assessment of a client's nightly update
> procedures, and I keep running into the following practice.
> The client consistently copies databases off to separate locations and
> performs updates upon the new copy. After successful update, the
> updated database is then copied back to the origin location and
> becomes the new version. All of this, obviously, to protect
> themselves in case of error. I can't help but suspect that SQL Server
> provides an easier way to do it.
> In the mainframe universe, this used to be accomplished via
> generational datasets which would be promoted as the baseline when the
> update completes. I guess what I'm asking is if SQL Server provides
> any kind of "checkpoint-fallback" capability that the client could use
> instead of this monstrous time-consuming practice. When talking
> thousands of updates in a batch, I'm not sure transactioning would be
> the best way to do it. That would be one big honking rollback.
> Thanks in advance to any of you architects out there!
>
|||It is called a backup before you do any changes. Then if there is an issue
you simply restore.
Andrew J. Kelly SQL MVP
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177351816.251254.221810@.b58g2000hsg.googlegr oups.com...
>I have become involved with an assessment of a client's nightly update
> procedures, and I keep running into the following practice.
> The client consistently copies databases off to separate locations and
> performs updates upon the new copy. After successful update, the
> updated database is then copied back to the origin location and
> becomes the new version. All of this, obviously, to protect
> themselves in case of error. I can't help but suspect that SQL Server
> provides an easier way to do it.
> In the mainframe universe, this used to be accomplished via
> generational datasets which would be promoted as the baseline when the
> update completes. I guess what I'm asking is if SQL Server provides
> any kind of "checkpoint-fallback" capability that the client could use
> instead of this monstrous time-consuming practice. When talking
> thousands of updates in a batch, I'm not sure transactioning would be
> the best way to do it. That would be one big honking rollback.
> Thanks in advance to any of you architects out there!
>
|||On Apr 23, 1:23 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> SQL Server 2005 (Enterprise Edition) has, sort of, this. You can create a "database snapshot"
> (CREATE DATABASE Copy AS SNAPSHOT OF Orig). The database files for the Copy database are initially
> empty and consume close to 0 disk space, i.e., the snapshot takes only a few seconds to take. Then
> modify your Orig database. As you modify Orig database, the pages to be modified are first copied to
> the Copy databases' database files. This way, the copy database always has the data as it was when
> the snapshot was produced. If you need to "fall-back", then you can RESTORE DATABASE Orig FROM
> DATABASE_SNAPSHOT 'Copy', and all pages (in the Copy databases' database files) are copied back to
> the Orig database' files and you are back where you began. Concept is called "Copy on write".
> Same-same (concept) but different (implementation). :-)
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "BizEd" <EdMathia...@.gmail.com> wrote in message
> news:1177351816.251254.221810@.b58g2000hsg.googlegr oups.com...
>
>
>
> - Show quoted text -
Tibor,
Thanks. That's the type of functionality I was looking for. A simple
backup is not a sufficient solution when you are talking about a
multiple stage update process. If you wish to restart a step in the
process, the backup would necessitate going all the way back to the
beginning. Your solution is in essence "a delta file" which provides
functionality much more in line with the concept of mainframe
generational data sets!
Now to get the client on to SQL 2005....harrumph!
|||Note that you can have several "delta-files" (several database snapshots). But if you want to
restore from any of them, you need for first DROP DATABASE for all those snapshots except the one to
restore from.
Another important thing is that when you restore from a database snapshot, you break the log backup
chain sequence for your database. So, if you are doing log backups for that database, you want to do
a BACKUP DATABASE after restoring from a database snapshot.
And, as always, make sure you read all about this in Books Online first. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177365104.403457.180780@.b58g2000hsg.googlegr oups.com...
> On Apr 23, 1:23 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Tibor,
> Thanks. That's the type of functionality I was looking for. A simple
> backup is not a sufficient solution when you are talking about a
> multiple stage update process. If you wish to restart a step in the
> process, the backup would necessitate going all the way back to the
> beginning. Your solution is in essence "a delta file" which provides
> functionality much more in line with the concept of mainframe
> generational data sets!
> Now to get the client on to SQL 2005....harrumph!
>
|||As a standard process, this seems excessive.
You are aware that within the processing, SQLServer (like most major
databases) allows you to define a transaction that holds off
commitment until all the steps have completed successfully, and can be
rolled back if any errors are detected?
Josh
On 23 Apr 2007 11:10:16 -0700, BizEd <EdMathias18@.gmail.com> wrote:

>I have become involved with an assessment of a client's nightly update
>procedures, and I keep running into the following practice.
>The client consistently copies databases off to separate locations and
>performs updates upon the new copy. After successful update, the
>updated database is then copied back to the origin location and
>becomes the new version. All of this, obviously, to protect
>themselves in case of error. I can't help but suspect that SQL Server
>provides an easier way to do it.
>In the mainframe universe, this used to be accomplished via
>generational datasets which would be promoted as the baseline when the
>update completes. I guess what I'm asking is if SQL Server provides
>any kind of "checkpoint-fallback" capability that the client could use
>instead of this monstrous time-consuming practice. When talking
>thousands of updates in a batch, I'm not sure transactioning would be
>the best way to do it. That would be one big honking rollback.
>Thanks in advance to any of you architects out there!

Nightly Update / Fallback protection

I have become involved with an assessment of a client's nightly update
procedures, and I keep running into the following practice.
The client consistently copies databases off to separate locations and
performs updates upon the new copy. After successful update, the
updated database is then copied back to the origin location and
becomes the new version. All of this, obviously, to protect
themselves in case of error. I can't help but suspect that SQL Server
provides an easier way to do it.
In the mainframe universe, this used to be accomplished via
generational datasets which would be promoted as the baseline when the
update completes. I guess what I'm asking is if SQL Server provides
any kind of "checkpoint-fallback" capability that the client could use
instead of this monstrous time-consuming practice. When talking
thousands of updates in a batch, I'm not sure transactioning would be
the best way to do it. That would be one big honking rollback.
Thanks in advance to any of you architects out there!SQL Server 2005 (Enterprise Edition) has, sort of, this. You can create a "d
atabase snapshot"
(CREATE DATABASE Copy AS SNAPSHOT OF Orig). The database files for the Copy
database are initially
empty and consume close to 0 disk space, i.e., the snapshot takes only a few
seconds to take. Then
modify your Orig database. As you modify Orig database, the pages to be modi
fied are first copied to
the Copy databases' database files. This way, the copy database always has t
he data as it was when
the snapshot was produced. If you need to "fall-back", then you can RESTORE
DATABASE Orig FROM
DATABASE_SNAPSHOT 'Copy', and all pages (in the Copy databases' database fil
es) are copied back to
the Orig database' files and you are back where you began. Concept is called
"Copy on write".
Same-same (concept) but different (implementation). :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>I have become involved with an assessment of a client's nightly update
> procedures, and I keep running into the following practice.
> The client consistently copies databases off to separate locations and
> performs updates upon the new copy. After successful update, the
> updated database is then copied back to the origin location and
> becomes the new version. All of this, obviously, to protect
> themselves in case of error. I can't help but suspect that SQL Server
> provides an easier way to do it.
> In the mainframe universe, this used to be accomplished via
> generational datasets which would be promoted as the baseline when the
> update completes. I guess what I'm asking is if SQL Server provides
> any kind of "checkpoint-fallback" capability that the client could use
> instead of this monstrous time-consuming practice. When talking
> thousands of updates in a batch, I'm not sure transactioning would be
> the best way to do it. That would be one big honking rollback.
> Thanks in advance to any of you architects out there!
>|||It is called a backup before you do any changes. Then if there is an issue
you simply restore.
Andrew J. Kelly SQL MVP
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>I have become involved with an assessment of a client's nightly update
> procedures, and I keep running into the following practice.
> The client consistently copies databases off to separate locations and
> performs updates upon the new copy. After successful update, the
> updated database is then copied back to the origin location and
> becomes the new version. All of this, obviously, to protect
> themselves in case of error. I can't help but suspect that SQL Server
> provides an easier way to do it.
> In the mainframe universe, this used to be accomplished via
> generational datasets which would be promoted as the baseline when the
> update completes. I guess what I'm asking is if SQL Server provides
> any kind of "checkpoint-fallback" capability that the client could use
> instead of this monstrous time-consuming practice. When talking
> thousands of updates in a batch, I'm not sure transactioning would be
> the best way to do it. That would be one big honking rollback.
> Thanks in advance to any of you architects out there!
>|||On Apr 23, 1:23 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> SQL Server 2005 (Enterprise Edition) has, sort of, this. You can create a
"database snapshot"
> (CREATE DATABASE Copy AS SNAPSHOT OF Orig). The database files for the Cop
y database are initially
> empty and consume close to 0 disk space, i.e., the snapshot takes only a f
ew seconds to take. Then
> modify your Orig database. As you modify Orig database, the pages to be mo
dified are first copied to
> the Copy databases' database files. This way, the copy database always has
the data as it was when
> the snapshot was produced. If you need to "fall-back", then you can RESTOR
E DATABASE Orig FROM
> DATABASE_SNAPSHOT 'Copy', and all pages (in the Copy databases' database f
iles) are copied back to
> the Orig database' files and you are back where you began. Concept is call
ed "Copy on write".
> Same-same (concept) but different (implementation). :-)
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> "BizEd" <EdMathia...@.gmail.com> wrote in message
> news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>
>
>
>
>
> - Show quoted text -
Tibor,
Thanks. That's the type of functionality I was looking for. A simple
backup is not a sufficient solution when you are talking about a
multiple stage update process. If you wish to restart a step in the
process, the backup would necessitate going all the way back to the
beginning. Your solution is in essence "a delta file" which provides
functionality much more in line with the concept of mainframe
generational data sets!
Now to get the client on to SQL 2005....harrumph!|||Note that you can have several "delta-files" (several database snapshots). B
ut if you want to
restore from any of them, you need for first DROP DATABASE for all those sna
pshots except the one to
restore from.
Another important thing is that when you restore from a database snapshot, y
ou break the log backup
chain sequence for your database. So, if you are doing log backups for that
database, you want to do
a BACKUP DATABASE after restoring from a database snapshot.
And, as always, make sure you read all about this in Books Online first. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177365104.403457.180780@.b58g2000hsg.googlegroups.com...
> On Apr 23, 1:23 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Tibor,
> Thanks. That's the type of functionality I was looking for. A simple
> backup is not a sufficient solution when you are talking about a
> multiple stage update process. If you wish to restart a step in the
> process, the backup would necessitate going all the way back to the
> beginning. Your solution is in essence "a delta file" which provides
> functionality much more in line with the concept of mainframe
> generational data sets!
> Now to get the client on to SQL 2005....harrumph!
>|||As a standard process, this seems excessive.
You are aware that within the processing, SQLServer (like most major
databases) allows you to define a transaction that holds off
commitment until all the steps have completed successfully, and can be
rolled back if any errors are detected?
Josh
On 23 Apr 2007 11:10:16 -0700, BizEd <EdMathias18@.gmail.com> wrote:

>I have become involved with an assessment of a client's nightly update
>procedures, and I keep running into the following practice.
>The client consistently copies databases off to separate locations and
>performs updates upon the new copy. After successful update, the
>updated database is then copied back to the origin location and
>becomes the new version. All of this, obviously, to protect
>themselves in case of error. I can't help but suspect that SQL Server
>provides an easier way to do it.
>In the mainframe universe, this used to be accomplished via
>generational datasets which would be promoted as the baseline when the
>update completes. I guess what I'm asking is if SQL Server provides
>any kind of "checkpoint-fallback" capability that the client could use
>instead of this monstrous time-consuming practice. When talking
>thousands of updates in a batch, I'm not sure transactioning would be
>the best way to do it. That would be one big honking rollback.
>Thanks in advance to any of you architects out there!

Nightly Update / Fallback protection

I have become involved with an assessment of a client's nightly update
procedures, and I keep running into the following practice.
The client consistently copies databases off to separate locations and
performs updates upon the new copy. After successful update, the
updated database is then copied back to the origin location and
becomes the new version. All of this, obviously, to protect
themselves in case of error. I can't help but suspect that SQL Server
provides an easier way to do it.
In the mainframe universe, this used to be accomplished via
generational datasets which would be promoted as the baseline when the
update completes. I guess what I'm asking is if SQL Server provides
any kind of "checkpoint-fallback" capability that the client could use
instead of this monstrous time-consuming practice. When talking
thousands of updates in a batch, I'm not sure transactioning would be
the best way to do it. That would be one big honking rollback.
Thanks in advance to any of you architects out there!SQL Server 2005 (Enterprise Edition) has, sort of, this. You can create a "database snapshot"
(CREATE DATABASE Copy AS SNAPSHOT OF Orig). The database files for the Copy database are initially
empty and consume close to 0 disk space, i.e., the snapshot takes only a few seconds to take. Then
modify your Orig database. As you modify Orig database, the pages to be modified are first copied to
the Copy databases' database files. This way, the copy database always has the data as it was when
the snapshot was produced. If you need to "fall-back", then you can RESTORE DATABASE Orig FROM
DATABASE_SNAPSHOT 'Copy', and all pages (in the Copy databases' database files) are copied back to
the Orig database' files and you are back where you began. Concept is called "Copy on write".
Same-same (concept) but different (implementation). :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>I have become involved with an assessment of a client's nightly update
> procedures, and I keep running into the following practice.
> The client consistently copies databases off to separate locations and
> performs updates upon the new copy. After successful update, the
> updated database is then copied back to the origin location and
> becomes the new version. All of this, obviously, to protect
> themselves in case of error. I can't help but suspect that SQL Server
> provides an easier way to do it.
> In the mainframe universe, this used to be accomplished via
> generational datasets which would be promoted as the baseline when the
> update completes. I guess what I'm asking is if SQL Server provides
> any kind of "checkpoint-fallback" capability that the client could use
> instead of this monstrous time-consuming practice. When talking
> thousands of updates in a batch, I'm not sure transactioning would be
> the best way to do it. That would be one big honking rollback.
> Thanks in advance to any of you architects out there!
>|||It is called a backup before you do any changes. Then if there is an issue
you simply restore.
--
Andrew J. Kelly SQL MVP
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>I have become involved with an assessment of a client's nightly update
> procedures, and I keep running into the following practice.
> The client consistently copies databases off to separate locations and
> performs updates upon the new copy. After successful update, the
> updated database is then copied back to the origin location and
> becomes the new version. All of this, obviously, to protect
> themselves in case of error. I can't help but suspect that SQL Server
> provides an easier way to do it.
> In the mainframe universe, this used to be accomplished via
> generational datasets which would be promoted as the baseline when the
> update completes. I guess what I'm asking is if SQL Server provides
> any kind of "checkpoint-fallback" capability that the client could use
> instead of this monstrous time-consuming practice. When talking
> thousands of updates in a batch, I'm not sure transactioning would be
> the best way to do it. That would be one big honking rollback.
> Thanks in advance to any of you architects out there!
>|||On Apr 23, 1:23 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> SQL Server 2005 (Enterprise Edition) has, sort of, this. You can create a "database snapshot"
> (CREATE DATABASE Copy AS SNAPSHOT OF Orig). The database files for the Copy database are initially
> empty and consume close to 0 disk space, i.e., the snapshot takes only a few seconds to take. Then
> modify your Orig database. As you modify Orig database, the pages to be modified are first copied to
> the Copy databases' database files. This way, the copy database always has the data as it was when
> the snapshot was produced. If you need to "fall-back", then you can RESTORE DATABASE Orig FROM
> DATABASE_SNAPSHOT 'Copy', and all pages (in the Copy databases' database files) are copied back to
> the Orig database' files and you are back where you began. Concept is called "Copy on write".
> Same-same (concept) but different (implementation). :-)
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "BizEd" <EdMathia...@.gmail.com> wrote in message
> news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>
> >I have become involved with an assessment of a client's nightly update
> > procedures, and I keep running into the following practice.
> > The client consistently copies databases off to separate locations and
> > performs updates upon the new copy. After successful update, the
> > updated database is then copied back to the origin location and
> > becomes the new version. All of this, obviously, to protect
> > themselves in case of error. I can't help but suspect that SQL Server
> > provides an easier way to do it.
> > In the mainframe universe, this used to be accomplished via
> > generational datasets which would be promoted as the baseline when the
> > update completes. I guess what I'm asking is if SQL Server provides
> > any kind of "checkpoint-fallback" capability that the client could use
> > instead of this monstrous time-consuming practice. When talking
> > thousands of updates in a batch, I'm not sure transactioning would be
> > the best way to do it. That would be one big honking rollback.
> > Thanks in advance to any of you architects out there!- Hide quoted text -
> - Show quoted text -
Tibor,
Thanks. That's the type of functionality I was looking for. A simple
backup is not a sufficient solution when you are talking about a
multiple stage update process. If you wish to restart a step in the
process, the backup would necessitate going all the way back to the
beginning. Your solution is in essence "a delta file" which provides
functionality much more in line with the concept of mainframe
generational data sets!
Now to get the client on to SQL 2005....harrumph!|||Note that you can have several "delta-files" (several database snapshots). But if you want to
restore from any of them, you need for first DROP DATABASE for all those snapshots except the one to
restore from.
Another important thing is that when you restore from a database snapshot, you break the log backup
chain sequence for your database. So, if you are doing log backups for that database, you want to do
a BACKUP DATABASE after restoring from a database snapshot.
And, as always, make sure you read all about this in Books Online first. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177365104.403457.180780@.b58g2000hsg.googlegroups.com...
> On Apr 23, 1:23 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> SQL Server 2005 (Enterprise Edition) has, sort of, this. You can create a "database snapshot"
>> (CREATE DATABASE Copy AS SNAPSHOT OF Orig). The database files for the Copy database are
>> initially
>> empty and consume close to 0 disk space, i.e., the snapshot takes only a few seconds to take.
>> Then
>> modify your Orig database. As you modify Orig database, the pages to be modified are first copied
>> to
>> the Copy databases' database files. This way, the copy database always has the data as it was
>> when
>> the snapshot was produced. If you need to "fall-back", then you can RESTORE DATABASE Orig FROM
>> DATABASE_SNAPSHOT 'Copy', and all pages (in the Copy databases' database files) are copied back
>> to
>> the Orig database' files and you are back where you began. Concept is called "Copy on write".
>> Same-same (concept) but different (implementation). :-)
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "BizEd" <EdMathia...@.gmail.com> wrote in message
>> news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>>
>> >I have become involved with an assessment of a client's nightly update
>> > procedures, and I keep running into the following practice.
>> > The client consistently copies databases off to separate locations and
>> > performs updates upon the new copy. After successful update, the
>> > updated database is then copied back to the origin location and
>> > becomes the new version. All of this, obviously, to protect
>> > themselves in case of error. I can't help but suspect that SQL Server
>> > provides an easier way to do it.
>> > In the mainframe universe, this used to be accomplished via
>> > generational datasets which would be promoted as the baseline when the
>> > update completes. I guess what I'm asking is if SQL Server provides
>> > any kind of "checkpoint-fallback" capability that the client could use
>> > instead of this monstrous time-consuming practice. When talking
>> > thousands of updates in a batch, I'm not sure transactioning would be
>> > the best way to do it. That would be one big honking rollback.
>> > Thanks in advance to any of you architects out there!- Hide quoted text -
>> - Show quoted text -
> Tibor,
> Thanks. That's the type of functionality I was looking for. A simple
> backup is not a sufficient solution when you are talking about a
> multiple stage update process. If you wish to restart a step in the
> process, the backup would necessitate going all the way back to the
> beginning. Your solution is in essence "a delta file" which provides
> functionality much more in line with the concept of mainframe
> generational data sets!
> Now to get the client on to SQL 2005....harrumph!
>|||As a standard process, this seems excessive.
You are aware that within the processing, SQLServer (like most major
databases) allows you to define a transaction that holds off
commitment until all the steps have completed successfully, and can be
rolled back if any errors are detected?
Josh
On 23 Apr 2007 11:10:16 -0700, BizEd <EdMathias18@.gmail.com> wrote:
>I have become involved with an assessment of a client's nightly update
>procedures, and I keep running into the following practice.
>The client consistently copies databases off to separate locations and
>performs updates upon the new copy. After successful update, the
>updated database is then copied back to the origin location and
>becomes the new version. All of this, obviously, to protect
>themselves in case of error. I can't help but suspect that SQL Server
>provides an easier way to do it.
>In the mainframe universe, this used to be accomplished via
>generational datasets which would be promoted as the baseline when the
>update completes. I guess what I'm asking is if SQL Server provides
>any kind of "checkpoint-fallback" capability that the client could use
>instead of this monstrous time-consuming practice. When talking
>thousands of updates in a batch, I'm not sure transactioning would be
>the best way to do it. That would be one big honking rollback.
>Thanks in advance to any of you architects out there!

Newbie: UPDATE a Table

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:529989
Hi,
I want to insert some new data in a Table. There is a possibility that there
are duplicate rows .
An Insert is obviously not allowed due to PK violatons.
The new data reside on a temporary Table and there are NO duplicates!
Will an Update do?
I simply want to :
"If the row exists replace it, if not insert the new one"
TIA
-steveIf you know you are replacing the old rows when you have a match, then
why not just delete them and then insert this scrubbed data, like this?
BEGIN
DELETE FROM Foo
WHERE Foo.key_col
IN (SELECT key_col FROM Bar);
INSERT INTO Foo
SELECT * FROM Bar;
END;|||Ok, thanx!
But, for info, there is no command that will do this in one step ?
and in my case the PK consists of multiple columns. However i get an error:
DELETE FROM tblMeasQAHor
WHERE
(tblMeasQAHor.MENVid, tblMeasQAHor.date_time, tblMeasQAHor.CodePol,
tblMeasQAHor.Valide, tblMeasQAHor.val)
IN
(SELECT MENVid, date_time, CodePol, Valide, val FROM tempQAHor)
why? i tried a few combinations and it doesn't work. If i only leave one
column then it's OK.
TIA
"--CELKO--" <jcelko212@.earthlink.net> a crit dans le message de news:
1117220035.893367.120570@.g14g2000cwa.googlegroups.com...
> If you know you are replacing the old rows when you have a match, then
> why not just delete them and then insert this scrubbed data, like this?
>
> BEGIN
> DELETE FROM Foo
> WHERE Foo.key_col
> IN (SELECT key_col FROM Bar);
> INSERT INTO Foo
> SELECT * FROM Bar;
> END;
>|||You'll need to do this in 2 steps. Delete then Insert
For the delete, test this out before you execute it on production code.
Replace temp1
with whatever your temp table name is
DELETE FROM tblMeasQAHor
FROM tblMeasQAHor t1
JOIN temp1 t2 on t1.MENVid = t2.MENVid
AND t1.date_time = t2.date_time
AND t1.CodePol = t2.CodePol
AND t1.Valide = t2.Valide
AND t1.val = t2.val
"steve" <steve@.here.com> wrote in message
news:n1Lle.40566$8j3.864446@.weber.videotron.net...
> Ok, thanx!
> But, for info, there is no command that will do this in one step ?
> and in my case the PK consists of multiple columns. However i get an
error:
> DELETE FROM tblMeasQAHor
> WHERE
> (tblMeasQAHor.MENVid, tblMeasQAHor.date_time, tblMeasQAHor.CodePol,
> tblMeasQAHor.Valide, tblMeasQAHor.val)
> IN
> (SELECT MENVid, date_time, CodePol, Valide, val FROM tempQAHor)
>
> why? i tried a few combinations and it doesn't work. If i only leave one
> column then it's OK.
> TIA
> "--CELKO--" <jcelko212@.earthlink.net> a crit dans le message de news:
> 1117220035.893367.120570@.g14g2000cwa.googlegroups.com...
>|||I just want to clarify. If you want to do it the way Joe recommends, you'll
need to do it in 2 steps. You could also do a correlated subquery
(untested)
INSERT INTO tblMeasQAHor
SELECT *
FROM temp1 t1
WHERE NOT EXISTS (
SELECT 'X' FROM tblMeasQAHor t2 WHERE t1.MENVid = t2.MENVid
AND t1.date_time = t2.date_time
AND t1.CodePol = t2.CodePol
AND t1.Valide = t2.Valide
AND t1.val = t2.val)
I'm not sure which you prefer. Also, I generally recommend using column
names in the insert and select. I don't know what the table looks like
structurally so I went with the select * approach.
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:eY77X9vYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> You'll need to do this in 2 steps. Delete then Insert
> For the delete, test this out before you execute it on production code.
> Replace temp1
> with whatever your temp table name is
> DELETE FROM tblMeasQAHor
> FROM tblMeasQAHor t1
> JOIN temp1 t2 on t1.MENVid = t2.MENVid
> AND t1.date_time = t2.date_time
> AND t1.CodePol = t2.CodePol
> AND t1.Valide = t2.Valide
> AND t1.val = t2.val
>
> "steve" <steve@.here.com> wrote in message
> news:n1Lle.40566$8j3.864446@.weber.videotron.net...
> error:
this?
>