MS SQL Comparison between two date values

I remember at some point I had some problem with this issue and I couldn't find something that suites a newbie.

The point that I was missing was that I couldn't pass the string representation of the date value to a query string. The key is to convert the string date to its equivalent datetime or smalldate .

For example imagine you'd like to select all Employees who where born on a specific date stored in a variable called myDate:

the BirthDay column in our Employee table has type smalldatetime

--------------

string myDate = @."06/06/1978";

...

SqlCommand comm = new SqlCommand("SELECT * FROM EMPLOYEE WHERE [BirthDay] = Convert(smalldatetime,'" + myDate + "', 103))";

...

--------------

Make suer that you don't miss single quotes around value of myDate in your query string.

For more info regrading the Convert function look at:

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

SELECT *
FROM Employee
WHERE Birthday>= DATEADD(DAY, DATEDIFF(DAY, 0, @.myDate), 0)
AND Birthday< DATEADD(DAY, DATEDIFF(DAY, 0, @.myDate), 1)

@.myDate is datetime datatype.

|||

roozbehtk:

For example imagine you'd like to select all Employees who where born on a specific date stored in a variable called myDate:

This will work:

1declare @.MyDateDateTime23set @.MyDate ='2002-02-02'45select *6from MyTable7where DateOfBirth = @.MyDate8GO

Good luck.

|||

ndinakar:

SELECT *
FROM Employee
WHERE Birthday>= DATEADD(DAY, DATEDIFF(DAY, 0, @.myDate), 0)
AND Birthday< DATEADD(DAY, DATEDIFF(DAY, 0, @.myDate), 1)

@.myDate is datetime datatype.

Using such functions (e.g. DateAdd & DateDiff) in the where clause..will decrease query performance.
No need for those two functions for the mentioned case.

|||My problem was that I had to use a specific class that took the WHERE clause as pure string. I couldn't parameterize my query string and yet I need a mechanism to pass my date.

What would be you're suggestion for that case?

|||

roozbehtk:

My problem was that I had to use a specific class that took the WHERE clause as pure string. I couldn't parameterize my query string and yet I need a mechanism to pass my date.

What would be you're suggestion for that case?

I did not understand you, can you please give more details?

|||

Hi roozbehtk,

you can try the following code:

..................................
SqlCommand cmd =new SqlCommand("select name from employee where birthday>=DateAdd(day,DateDiff(day,@.birth,0),0) and birth<DateAdd(day,DateDiff(day,@.birth,0),1)", con);cmd.Parameters.Add("birth", SqlDbType.DateTime, 10); cmd.Parameters["birth"].Value ="06/06/1978";
.............................

I've tested the code in my side, it works fine.

P.S If onlydate infomation is stored in your database (withouttimeinfo.) you can also try the solutionCS4Ever has suggsted . thanks

|||

If you don't want to use parameters, try this:

string cmdtext=test('1978-1-1') // string cmdtext is an excutable sql command. I also tested this solution in my box, it works fineprotected string test(string date) {string str = @."select name from student whre birth='{0}'";string ret =string.Format(str, date);return ret; }

ms sql cluster: should i and how to place tempdb on local drive

Hi all
we are about to set up database on sql server cluster. Up to this point we
had set up ms sql cluster using vm.
My intention was to set up tempdb on local c drive. It turned out, that we
could not mount c drive at all and only drives which we mounted were drives
defined for cluster.
1. Do we miss something? Does clustered software allows to mount local
drives along with cluster defined drives?
2. If yes, do you recommend to use local drive for tempdb.
Thank you for your respond.
You can only place SQL data files on clustered drives where SQL is dependent
on the particular disk resource. This includes data files for tempdb.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:0A7A9EAC-DF71-499C-A615-8B26F99CD15C@.microsoft.com...
> Hi all
> we are about to set up database on sql server cluster. Up to this point we
> had set up ms sql cluster using vm.
> My intention was to set up tempdb on local c drive. It turned out, that we
> could not mount c drive at all and only drives which we mounted were
> drives
> defined for cluster.
> 1. Do we miss something? Does clustered software allows to mount local
> drives along with cluster defined drives?
> 2. If yes, do you recommend to use local drive for tempdb.
> Thank you for your respond.
|||Hi all
I would like to remark myself regarding my post below.
C drive is visable to cluster pc but...
it's not visable to sql server.
When you create new db either using tsql or windows inerface, 1 one produce
error complaining about not visable drive.
Windows interface does not show c drive as one generaly available for
database creation.
"Gene." wrote:

> Hi all
> we are about to set up database on sql server cluster. Up to this point we
> had set up ms sql cluster using vm.
> My intention was to set up tempdb on local c drive. It turned out, that we
> could not mount c drive at all and only drives which we mounted were drives
> defined for cluster.
> 1. Do we miss something? Does clustered software allows to mount local
> drives along with cluster defined drives?
> 2. If yes, do you recommend to use local drive for tempdb.
> Thank you for your respond.
|||Geoff
Do you mind if i ask you:
We can not do it because cluster does not have c drive defined to it (and
likely can not have c drive defined to it)?
Gene.
"Geoff N. Hiten" wrote:

> You can only place SQL data files on clustered drives where SQL is dependent
> on the particular disk resource. This includes data files for tempdb.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> news:0A7A9EAC-DF71-499C-A615-8B26F99CD15C@.microsoft.com...
>
|||C: is local to each node and cannot be defined as a cluster resource even
when it exists on all nodes. You cannot place tempdb for a cluster on local
disks.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:C7F5E403-A592-452C-B5C0-7DCC7D1B437B@.microsoft.com...[vbcol=seagreen]
> Geoff
> Do you mind if i ask you:
> We can not do it because cluster does not have c drive defined to it (and
> likely can not have c drive defined to it)?
> Gene.
> "Geoff N. Hiten" wrote:
|||Thank you Geoff. now I understand.
"Geoff N. Hiten" wrote:

> C: is local to each node and cannot be defined as a cluster resource even
> when it exists on all nodes. You cannot place tempdb for a cluster on local
> disks.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> news:C7F5E403-A592-452C-B5C0-7DCC7D1B437B@.microsoft.com...
>

ms sql cluster: should i and how to place tempdb on local drive

Hi all
we are about to set up database on sql server cluster. Up to this point we
had set up ms sql cluster using vm.
My intention was to set up tempdb on local c drive. It turned out, that we
could not mount c drive at all and only drives which we mounted were drives
defined for cluster.
1. Do we miss something? Does clustered software allows to mount local
drives along with cluster defined drives?
2. If yes, do you recommend to use local drive for tempdb.
Thank you for your respond.You can only place SQL data files on clustered drives where SQL is dependent
on the particular disk resource. This includes data files for tempdb.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:0A7A9EAC-DF71-499C-A615-8B26F99CD15C@.microsoft.com...
> Hi all
> we are about to set up database on sql server cluster. Up to this point we
> had set up ms sql cluster using vm.
> My intention was to set up tempdb on local c drive. It turned out, that we
> could not mount c drive at all and only drives which we mounted were
> drives
> defined for cluster.
> 1. Do we miss something? Does clustered software allows to mount local
> drives along with cluster defined drives?
> 2. If yes, do you recommend to use local drive for tempdb.
> Thank you for your respond.|||Hi all
I would like to remark myself regarding my post below.
C drive is visable to cluster pc but...
it's not visable to sql server.
When you create new db either using tsql or windows inerface, 1 one produce
error complaining about not visable drive.
Windows interface does not show c drive as one generaly available for
database creation.
"Gene." wrote:
> Hi all
> we are about to set up database on sql server cluster. Up to this point we
> had set up ms sql cluster using vm.
> My intention was to set up tempdb on local c drive. It turned out, that we
> could not mount c drive at all and only drives which we mounted were drives
> defined for cluster.
> 1. Do we miss something? Does clustered software allows to mount local
> drives along with cluster defined drives?
> 2. If yes, do you recommend to use local drive for tempdb.
> Thank you for your respond.|||Geoff
Do you mind if i ask you:
We can not do it because cluster does not have c drive defined to it (and
likely can not have c drive defined to it)?
Gene.
"Geoff N. Hiten" wrote:
> You can only place SQL data files on clustered drives where SQL is dependent
> on the particular disk resource. This includes data files for tempdb.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> news:0A7A9EAC-DF71-499C-A615-8B26F99CD15C@.microsoft.com...
> > Hi all
> > we are about to set up database on sql server cluster. Up to this point we
> > had set up ms sql cluster using vm.
> > My intention was to set up tempdb on local c drive. It turned out, that we
> > could not mount c drive at all and only drives which we mounted were
> > drives
> > defined for cluster.
> > 1. Do we miss something? Does clustered software allows to mount local
> > drives along with cluster defined drives?
> > 2. If yes, do you recommend to use local drive for tempdb.
> >
> > Thank you for your respond.
>|||C: is local to each node and cannot be defined as a cluster resource even
when it exists on all nodes. You cannot place tempdb for a cluster on local
disks.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:C7F5E403-A592-452C-B5C0-7DCC7D1B437B@.microsoft.com...
> Geoff
> Do you mind if i ask you:
> We can not do it because cluster does not have c drive defined to it (and
> likely can not have c drive defined to it)?
> Gene.
> "Geoff N. Hiten" wrote:
>> You can only place SQL data files on clustered drives where SQL is
>> dependent
>> on the particular disk resource. This includes data files for tempdb.
>> --
>> Geoff N. Hiten
>> Senior SQL Infrastructure Consultant
>> Microsoft SQL Server MVP
>>
>>
>> "Gene." <Gene@.discussions.microsoft.com> wrote in message
>> news:0A7A9EAC-DF71-499C-A615-8B26F99CD15C@.microsoft.com...
>> > Hi all
>> > we are about to set up database on sql server cluster. Up to this point
>> > we
>> > had set up ms sql cluster using vm.
>> > My intention was to set up tempdb on local c drive. It turned out, that
>> > we
>> > could not mount c drive at all and only drives which we mounted were
>> > drives
>> > defined for cluster.
>> > 1. Do we miss something? Does clustered software allows to mount local
>> > drives along with cluster defined drives?
>> > 2. If yes, do you recommend to use local drive for tempdb.
>> >
>> > Thank you for your respond.
>>|||Thank you Geoff. now I understand.
"Geoff N. Hiten" wrote:
> C: is local to each node and cannot be defined as a cluster resource even
> when it exists on all nodes. You cannot place tempdb for a cluster on local
> disks.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> news:C7F5E403-A592-452C-B5C0-7DCC7D1B437B@.microsoft.com...
> > Geoff
> >
> > Do you mind if i ask you:
> > We can not do it because cluster does not have c drive defined to it (and
> > likely can not have c drive defined to it)?
> >
> > Gene.
> >
> > "Geoff N. Hiten" wrote:
> >
> >> You can only place SQL data files on clustered drives where SQL is
> >> dependent
> >> on the particular disk resource. This includes data files for tempdb.
> >>
> >> --
> >> Geoff N. Hiten
> >> Senior SQL Infrastructure Consultant
> >> Microsoft SQL Server MVP
> >>
> >>
> >>
> >>
> >> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> >> news:0A7A9EAC-DF71-499C-A615-8B26F99CD15C@.microsoft.com...
> >> > Hi all
> >> > we are about to set up database on sql server cluster. Up to this point
> >> > we
> >> > had set up ms sql cluster using vm.
> >> > My intention was to set up tempdb on local c drive. It turned out, that
> >> > we
> >> > could not mount c drive at all and only drives which we mounted were
> >> > drives
> >> > defined for cluster.
> >> > 1. Do we miss something? Does clustered software allows to mount local
> >> > drives along with cluster defined drives?
> >> > 2. If yes, do you recommend to use local drive for tempdb.
> >> >
> >> > Thank you for your respond.
> >>
> >>
>

ms sql cluster: should i and how to place tempdb on local drive

Hi all
we are about to set up database on sql server cluster. Up to this point we
had set up ms sql cluster using vm.
My intention was to set up tempdb on local c drive. It turned out, that we
could not mount c drive at all and only drives which we mounted were drives
defined for cluster.
1. Do we miss something? Does clustered software allows to mount local
drives along with cluster defined drives?
2. If yes, do you recommend to use local drive for tempdb.
Thank you for your respond.You can only place SQL data files on clustered drives where SQL is dependent
on the particular disk resource. This includes data files for tempdb.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:0A7A9EAC-DF71-499C-A615-8B26F99CD15C@.microsoft.com...
> Hi all
> we are about to set up database on sql server cluster. Up to this point we
> had set up ms sql cluster using vm.
> My intention was to set up tempdb on local c drive. It turned out, that we
> could not mount c drive at all and only drives which we mounted were
> drives
> defined for cluster.
> 1. Do we miss something? Does clustered software allows to mount local
> drives along with cluster defined drives?
> 2. If yes, do you recommend to use local drive for tempdb.
> Thank you for your respond.|||Hi all
I would like to remark myself regarding my post below.
C drive is visable to cluster pc but...
it's not visable to sql server.
When you create new db either using tsql or windows inerface, 1 one produce
error complaining about not visable drive.
Windows interface does not show c drive as one generaly available for
database creation.
"Gene." wrote:

> Hi all
> we are about to set up database on sql server cluster. Up to this point we
> had set up ms sql cluster using vm.
> My intention was to set up tempdb on local c drive. It turned out, that we
> could not mount c drive at all and only drives which we mounted were drive
s
> defined for cluster.
> 1. Do we miss something? Does clustered software allows to mount local
> drives along with cluster defined drives?
> 2. If yes, do you recommend to use local drive for tempdb.
> Thank you for your respond.

MS SQL 2000 Server 'point in time' restore

Hello,
We encountered problem concerning MS SQL 2000 point in time restore. Our
backup/restore agent integrates to MS SQL 2000 Server through MS SQL 2000
VDI. We performed the following combined restore session:
[SCENARIO]
a) Database restore from full backup session, by executing the following
Transact-SQL:
RESTORE DATABASE [Northwind] FROM
VIRTUAL_DEVICE = 'Data Protector_(DEFAULT)_Northwind_11_20_49'
WITH NORECOVERY;
b) Transaction log restoration which backup was finished on 7/28/2004
2:00:30 PM.
RESTORE LOG [Northwind] FROM
VIRTUAL_DEVICE = 'Data Protector_(DEFAULT)_Northwind_11_20_49'
WITH STOPAT = '7/28/2004 2:00:20 PM', RECOVERY;
[PROBLEM]
MS SQL backup/restore agent successfully restores full backup version and
leave Nortwind database non-recovered.
When transaction log restore process starts,
IClientVirtualDeviceSet2::GetConfiguration (60000, &vdiConfig) function call
fails with VD_E_TIMEOUT return value and the following message is reported by
MS SQL 2000 Server:
<Microsoft SQL-DMO (ODBC SQLState: 42000):c91>
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid value specified
for STOPAT parameter.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
abnormally.
We started several restore sessions and always got the same error when
session was started with 'STOPAT' parameter. But at some point restore
session happened to work and we were unable to reproduce problem anymore. We
have also seen customers facing the same problem.
Sessions started with no 'STOPAT' parameter specified always went OK.
[QUESTIONS]
We would appreciate if you can help us with the following answers:
1. what can be a reason for IClientVirtualDeviceSet2::GetConfiguration
failure when 'STOPAT' parameter is specified?
2. Is there any workaround available?
[ENVIRONMENT]
Environment:
- MS SQL 2000 Server with SP3a installed
- Windows 2000 SP4
- Regional Settings set to English
Thank You
Robert,
Seems you are using a 3:rd party backup product (judging by the backup device type: VIRTUAL_DEVICE =). The
vendor of this is probably where you have to look for support.
To determine whether that is the case, I suggest that you try the RESTORE using disk (or possibly tape) and
see whether it work fine...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"robert.cokan" <robert.cokan@.discussions.microsoft.com> wrote in message
news:E4B763F8-09FD-4241-9FDF-331E7DE07819@.microsoft.com...
> Hello,
> We encountered problem concerning MS SQL 2000 point in time restore. Our
> backup/restore agent integrates to MS SQL 2000 Server through MS SQL 2000
> VDI. We performed the following combined restore session:
> [SCENARIO]
> a) Database restore from full backup session, by executing the following
> Transact-SQL:
> RESTORE DATABASE [Northwind] FROM
> VIRTUAL_DEVICE = 'Data Protector_(DEFAULT)_Northwind_11_20_49'
> WITH NORECOVERY;
>
> b) Transaction log restoration which backup was finished on 7/28/2004
> 2:00:30 PM.
> RESTORE LOG [Northwind] FROM
> VIRTUAL_DEVICE = 'Data Protector_(DEFAULT)_Northwind_11_20_49'
> WITH STOPAT = '7/28/2004 2:00:20 PM', RECOVERY;
>
> [PROBLEM]
> MS SQL backup/restore agent successfully restores full backup version and
> leave Nortwind database non-recovered.
> When transaction log restore process starts,
> IClientVirtualDeviceSet2::GetConfiguration (60000, &vdiConfig) function call
> fails with VD_E_TIMEOUT return value and the following message is reported by
> MS SQL 2000 Server:
> <Microsoft SQL-DMO (ODBC SQLState: 42000):c91>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid value specified
> for STOPAT parameter.
> [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> abnormally.
>
> We started several restore sessions and always got the same error when
> session was started with 'STOPAT' parameter. But at some point restore
> session happened to work and we were unable to reproduce problem anymore. We
> have also seen customers facing the same problem.
> Sessions started with no 'STOPAT' parameter specified always went OK.
>
> [QUESTIONS]
> We would appreciate if you can help us with the following answers:
> 1. what can be a reason for IClientVirtualDeviceSet2::GetConfiguration
> failure when 'STOPAT' parameter is specified?
> 2. Is there any workaround available?
>
> [ENVIRONMENT]
> Environment:
> - MS SQL 2000 Server with SP3a installed
> - Windows 2000 SP4
> - Regional Settings set to English
> Thank You
>
|||Hello Tibor,
thank you for your help. However, we need to say that we are backup vendor.
We have addressed this question to MS Support due to we were unable to find
explanation for 'Invalid value specified for STOPAT parameter' message
reported by MS SQL Server. Please help us by providing answers to the
following questions:
1. what can be a reason for IClientVirtualDeviceSet2::GetConfiguration
failure when 'STOPAT' parameter is specified? Please also note that restore
session does not always fail (see [PROBLEM] section below).
2. Is there any workaround available?
3. Under What circumstances MS SQL 2000 Server reports 'Invalid value
specified for STOPAT parameter' message?
Thank you,
Robert

MS SQL 2000 Server 'point in time' restore

Hello,
We encountered problem concerning MS SQL 2000 point in time restore. Our
backup/restore agent integrates to MS SQL 2000 Server through MS SQL 2000
VDI. We performed the following combined restore session:
[SCENARIO]
a) Database restore from full backup session, by executing the following
Transact-SQL:
RESTORE DATABASE [Northwind] FROM
VIRTUAL_DEVICE = 'Data Protector_(DEFAULT)_Northwind_11_20_49'
WITH NORECOVERY;
b) Transaction log restoration which backup was finished on 7/28/2004
2:00:30 PM.
RESTORE LOG [Northwind] FROM
VIRTUAL_DEVICE = 'Data Protector_(DEFAULT)_Northwind_11_20_49'
WITH STOPAT = '7/28/2004 2:00:20 PM', RECOVERY;
[PROBLEM]
MS SQL backup/restore agent successfully restores full backup version and
leave Nortwind database non-recovered.
When transaction log restore process starts,
IClientVirtualDeviceSet2::GetConfigurati
on (60000, &vdiConfig) function call
fails with VD_E_TIMEOUT return value and the following message is reported b
y
MS SQL 2000 Server:
<Microsoft SQL-DMO (ODBC SQLState: 42000):c91>
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid value spe
cified
for STOPAT parameter.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is te
rminating
abnormally.
We started several restore sessions and always got the same error when
session was started with 'STOPAT' parameter. But at some point restore
session happened to work and we were unable to reproduce problem anymore. We
have also seen customers facing the same problem.
Sessions started with no 'STOPAT' parameter specified always went OK.
[QUESTIONS]
We would appreciate if you can help us with the following answers:
1. what can be a reason for IClientVirtualDeviceSet2::GetConfigurati
on
failure when 'STOPAT' parameter is specified?
2. Is there any workaround available?
[ENVIRONMENT]
Environment:
- MS SQL 2000 Server with SP3a installed
- Windows 2000 SP4
- Regional Settings set to English
Thank YouRobert,
Seems you are using a 3:rd party backup product (judging by the backup devic
e type: VIRTUAL_DEVICE =). The
vendor of this is probably where you have to look for support.
To determine whether that is the case, I suggest that you try the RESTORE us
ing disk (or possibly tape) and
see whether it work fine...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"robert.cokan" <robert.cokan@.discussions.microsoft.com> wrote in message
news:E4B763F8-09FD-4241-9FDF-331E7DE07819@.microsoft.com...
> Hello,
> We encountered problem concerning MS SQL 2000 point in time restore. Our
> backup/restore agent integrates to MS SQL 2000 Server through MS SQL 2000
> VDI. We performed the following combined restore session:
> [SCENARIO]
> a) Database restore from full backup session, by executing the following
> Transact-SQL:
> RESTORE DATABASE [Northwind] FROM
> VIRTUAL_DEVICE = 'Data Protector_(DEFAULT)_Northwind_11_20_49'
> WITH NORECOVERY;
>
> b) Transaction log restoration which backup was finished on 7/28/2004
> 2:00:30 PM.
> RESTORE LOG [Northwind] FROM
> VIRTUAL_DEVICE = 'Data Protector_(DEFAULT)_Northwind_11_20_49'
> WITH STOPAT = '7/28/2004 2:00:20 PM', RECOVERY;
>
> [PROBLEM]
> MS SQL backup/restore agent successfully restores full backup version and
> leave Nortwind database non-recovered.
> When transaction log restore process starts,
> IClientVirtualDeviceSet2::GetConfigurati
on (60000, &vdiConfig) function ca
ll
> fails with VD_E_TIMEOUT return value and the following message is reported
by
> MS SQL 2000 Server:
> <Microsoft SQL-DMO (ODBC SQLState: 42000):c91>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid valu
e specified
> for STOPAT parameter.
> [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG
is terminating
> abnormally.
>
> We started several restore sessions and always got the same error when
> session was started with 'STOPAT' parameter. But at some point restore
> session happened to work and we were unable to reproduce problem anymore.
We
> have also seen customers facing the same problem.
> Sessions started with no 'STOPAT' parameter specified always went OK.
>
> [QUESTIONS]
> We would appreciate if you can help us with the following answers:
> 1. what can be a reason for IClientVirtualDeviceSet2::GetConfigurati
on
> failure when 'STOPAT' parameter is specified?
> 2. Is there any workaround available?
>
> [ENVIRONMENT]
> Environment:
> - MS SQL 2000 Server with SP3a installed
> - Windows 2000 SP4
> - Regional Settings set to English
> Thank You
>|||Hello Tibor,
thank you for your help. However, we need to say that we are backup vendor.
We have addressed this question to MS Support due to we were unable to find
explanation for 'Invalid value specified for STOPAT parameter' message
reported by MS SQL Server. Please help us by providing answers to the
following questions:
1. what can be a reason for IClientVirtualDeviceSet2::GetConfigurati
on
failure when 'STOPAT' parameter is specified? Please also note that restore
session does not always fail (see [PROBLEM] section below).
2. Is there any workaround available?
3. Under What circumstances MS SQL 2000 Server reports 'Invalid value
specified for STOPAT parameter' message?
Thank you,
Robert

MS SQL 2000 Server 'point in time' restore

Hello,
We encountered problem concerning MS SQL 2000 point in time restore. Our
backup/restore agent integrates to MS SQL 2000 Server through MS SQL 2000
VDI. We performed the following combined restore session:
[SCENARIO]
a) Database restore from full backup session, by executing the following
Transact-SQL:
RESTORE DATABASE [Northwind] FROM
VIRTUAL_DEVICE = 'Data Protector_(DEFAULT)_Northwind_11_20_49'
WITH NORECOVERY;
b) Transaction log restoration which backup was finished on 7/28/2004
2:00:30 PM.
RESTORE LOG [Northwind] FROM
VIRTUAL_DEVICE = 'Data Protector_(DEFAULT)_Northwind_11_20_49'
WITH STOPAT = '7/28/2004 2:00:20 PM', RECOVERY;
[PROBLEM]
MS SQL backup/restore agent successfully restores full backup version and
leave Nortwind database non-recovered.
When transaction log restore process starts,
IClientVirtualDeviceSet2::GetConfiguration (60000, &vdiConfig) function call
fails with VD_E_TIMEOUT return value and the following message is reported by
MS SQL 2000 Server:
<Microsoft SQL-DMO (ODBC SQLState: 42000):c91>
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid value specified
for STOPAT parameter.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
abnormally.
We started several restore sessions and always got the same error when
session was started with 'STOPAT' parameter. But at some point restore
session happened to work and we were unable to reproduce problem anymore. We
have also seen customers facing the same problem.
Sessions started with no 'STOPAT' parameter specified always went OK.
[QUESTIONS]
We would appreciate if you can help us with the following answers:
1. what can be a reason for IClientVirtualDeviceSet2::GetConfiguration
failure when 'STOPAT' parameter is specified?
2. Is there any workaround available?
[ENVIRONMENT]
Environment:
- MS SQL 2000 Server with SP3a installed
- Windows 2000 SP4
- Regional Settings set to English
Thank YouRobert,
Seems you are using a 3:rd party backup product (judging by the backup device type: VIRTUAL_DEVICE =). The
vendor of this is probably where you have to look for support.
To determine whether that is the case, I suggest that you try the RESTORE using disk (or possibly tape) and
see whether it work fine...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"robert.cokan" <robert.cokan@.discussions.microsoft.com> wrote in message
news:E4B763F8-09FD-4241-9FDF-331E7DE07819@.microsoft.com...
> Hello,
> We encountered problem concerning MS SQL 2000 point in time restore. Our
> backup/restore agent integrates to MS SQL 2000 Server through MS SQL 2000
> VDI. We performed the following combined restore session:
> [SCENARIO]
> a) Database restore from full backup session, by executing the following
> Transact-SQL:
> RESTORE DATABASE [Northwind] FROM
> VIRTUAL_DEVICE = 'Data Protector_(DEFAULT)_Northwind_11_20_49'
> WITH NORECOVERY;
>
> b) Transaction log restoration which backup was finished on 7/28/2004
> 2:00:30 PM.
> RESTORE LOG [Northwind] FROM
> VIRTUAL_DEVICE = 'Data Protector_(DEFAULT)_Northwind_11_20_49'
> WITH STOPAT = '7/28/2004 2:00:20 PM', RECOVERY;
>
> [PROBLEM]
> MS SQL backup/restore agent successfully restores full backup version and
> leave Nortwind database non-recovered.
> When transaction log restore process starts,
> IClientVirtualDeviceSet2::GetConfiguration (60000, &vdiConfig) function call
> fails with VD_E_TIMEOUT return value and the following message is reported by
> MS SQL 2000 Server:
> <Microsoft SQL-DMO (ODBC SQLState: 42000):c91>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid value specified
> for STOPAT parameter.
> [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> abnormally.
>
> We started several restore sessions and always got the same error when
> session was started with 'STOPAT' parameter. But at some point restore
> session happened to work and we were unable to reproduce problem anymore. We
> have also seen customers facing the same problem.
> Sessions started with no 'STOPAT' parameter specified always went OK.
>
> [QUESTIONS]
> We would appreciate if you can help us with the following answers:
> 1. what can be a reason for IClientVirtualDeviceSet2::GetConfiguration
> failure when 'STOPAT' parameter is specified?
> 2. Is there any workaround available?
>
> [ENVIRONMENT]
> Environment:
> - MS SQL 2000 Server with SP3a installed
> - Windows 2000 SP4
> - Regional Settings set to English
> Thank You
>|||Hello Tibor,
thank you for your help. However, we need to say that we are backup vendor.
We have addressed this question to MS Support due to we were unable to find
explanation for 'Invalid value specified for STOPAT parameter' message
reported by MS SQL Server. Please help us by providing answers to the
following questions:
1. what can be a reason for IClientVirtualDeviceSet2::GetConfiguration
failure when 'STOPAT' parameter is specified? Please also note that restore
session does not always fail (see [PROBLEM] section below).
2. Is there any workaround available?
3. Under What circumstances MS SQL 2000 Server reports 'Invalid value
specified for STOPAT parameter' message?
Thank you,
Robert