Friday, March 23, 2012
hidden parameter
error.if you have come across such error please suggest the way around
"The report parameter 'p1' is read-only and cannot be modified"Your parameter is not hidded, but read only. You need to change its type
using Report Manager.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Satya" <bhatsatya@.gmail.com> wrote in message
news:1102360089.572689.142700@.c13g2000cwb.googlegroups.com...
> When i try to pass the hidden parameter p1 to my report i get the below
> error.if you have come across such error please suggest the way around
> "The report parameter 'p1' is read-only and cannot be modified"
>
Monday, March 12, 2012
Hep needed!
I have two diff tables which stores passwords (don't ask why b'coz it was there).Now to make data in both table consistance what I need to do?plz provide optimized solution.
Thanks in advance.
bye bye.
happy coding!!!
I have two diff tables which stores passwords (don't ask why b'coz it was there).Now to make data in both table consistance what I need to do?plz provide optimized solution.
Since there does not appear to be much detail in your statement, I am assuming that you are asking how you can keep passwords in both tables synchronised.
I would recommend that you create one stored procedure to update passwords and revoke explicit update statements to both tables. Then grant update to the stored procedure which will force everyone update to go thru that. Then you can update both tables in that sp.
hth
Friday, March 9, 2012
HELP--still cannot shrink the transaction log--PLEASE HELP
This is an update to my other posting. I know that it is unorthodox to post
twice but I don't know what else to do.
Below is my original posting. I had already done the items posted by the
other gentleman such as reivewing the sysfiles. I cannot get the logical
file name from anywhere either.
Again, please help! Should I just not shrink the file. The client only has
1.82 GB of free space left and there is nothing else that can be deleted.
Below is my message:
I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
analyzer in SQL 2000 against the master database.
What is the EXACT syntax? You see, the name of the database is JC and the
log file is named GPSJClog.ldf. I have tried to place both names in the
syntax of the statement but with no success. I keep getting a message saying
that the file name is not in sysfiles.
This is what I have tried:
DBCC SHRINKFILE (GPJClog.ldf , 2)
Please help!
childofthe1980sNevermind. I figured it out.
I had to run the following scripts against each of the databases:
BACKUP LOG JC with TRUNCATE_ONLY
DBCC SHRINKFILE (2, 20)
You see, the support documentation on the web did not say that you have to
take the fileID of the database--2 in the syntax above-- in order to make it
work! You get the number 2 from running the following query against the
database:
select * from sysfiles
It would have been nice if this had been in the documentation!
Thanks!
childofthe1980s
"childofthe1980s" wrote:
> Hello:
> This is an update to my other posting. I know that it is unorthodox to post
> twice but I don't know what else to do.
> Below is my original posting. I had already done the items posted by the
> other gentleman such as reivewing the sysfiles. I cannot get the logical
> file name from anywhere either.
> Again, please help! Should I just not shrink the file. The client only has
> 1.82 GB of free space left and there is nothing else that can be deleted.
> Below is my message:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> analyzer in SQL 2000 against the master database.
> What is the EXACT syntax? You see, the name of the database is JC and the
> log file is named GPSJClog.ldf. I have tried to place both names in the
> syntax of the statement but with no success. I keep getting a message saying
> that the file name is not in sysfiles.
> This is what I have tried:
> DBCC SHRINKFILE (GPJClog.ldf , 2)
> Please help!
> childofthe1980s
>|||> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
The documentation clearly states that you can use *either* the logical filename or the file id. The
documentation is correct. The command you posted had 'GPSJCLog.ldf' for the logical filename, and I
guess that you by mistake put the *physical* filename there instead of the *logical* filename. If
you show ut the output from below, we can say for sure:
SELECT name, filename FROM sysfiles
I have shrunk database files occasionally and I always used the logical filename. In addition,
here's a script (executed on my SQL Server 2000) which clearly show the usage of the *logical*
filename:
USE master
CREATE DATABASE myTestShrink ON PRIMARY
(NAME = N'myTestShrink', FILENAME = N'C:\myTestShrink.mdf'
,SIZE = 1024KB , FILEGROWTH = 10%)
LOG ON
(NAME = N'myTestShrink_log', FILENAME = N'C:\myTestShrink_log.ldf'
, SIZE = 1024KB , FILEGROWTH = 10%)
GO
--Increase log file size
BACKUP DATABASE myTestShrink TO DISK = 'nul'
CREATE TABLE myTestShrink..test(c1 int identity, c2 char(2000))
INSERT INTO myTestShrink..test(c2)
SELECT TOP 10000 'hello' FROM sysobjects, syscolumns
GO
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles WHERE groupid = 0
--Empty the log
BACKUP LOG myTestShrink WITH NO_LOG
--Check the logical and physical files names
SELECT name, filename FROM myTestShrink..sysfiles
--Perform the shrink, ***based on logical filename***
USE myTestShrink
GO
DBCC SHRINKFILE('myTestShrink_log', 2)
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles WHERE groupid = 0
USE master
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:868E9C82-C2C4-4151-BBBD-40741201E676@.microsoft.com...
> Nevermind. I figured it out.
> I had to run the following scripts against each of the databases:
> BACKUP LOG JC with TRUNCATE_ONLY
> DBCC SHRINKFILE (2, 20)
> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
> Thanks!
> childofthe1980s
> "childofthe1980s" wrote:
>> Hello:
>> This is an update to my other posting. I know that it is unorthodox to post
>> twice but I don't know what else to do.
>> Below is my original posting. I had already done the items posted by the
>> other gentleman such as reivewing the sysfiles. I cannot get the logical
>> file name from anywhere either.
>> Again, please help! Should I just not shrink the file. The client only has
>> 1.82 GB of free space left and there is nothing else that can be deleted.
>> Below is my message:
>> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
>> analyzer in SQL 2000 against the master database.
>> What is the EXACT syntax? You see, the name of the database is JC and the
>> log file is named GPSJClog.ldf. I have tried to place both names in the
>> syntax of the statement but with no success. I keep getting a message saying
>> that the file name is not in sysfiles.
>> This is what I have tried:
>> DBCC SHRINKFILE (GPJClog.ldf , 2)
>> Please help!
>> childofthe1980s|||you were getting that error because you were running it in the master
database, not the databse you wanted to shrink the log for. the
documentation's correct.
"childofthe1980s" wrote:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> analyzer in SQL 2000 against the master database.
>|||Tibor Karaszi wrote:
>> You see, the support documentation on the web did not say that you
>> have to
>> take the fileID of the database--2 in the syntax above-- in order to
>> make it
>> work! You get the number 2 from running the following query against the
>> database:
>> select * from sysfiles
>> It would have been nice if this had been in the documentation!
> The documentation clearly states that you can use *either* the logical
> filename or the file id. The documentation is correct. The command you
> posted had 'GPSJCLog.ldf' for the logical filename, and I guess that you
> by mistake put the *physical* filename there instead of the *logical*
> filename. If you show ut the output from below, we can say for sure:
> SELECT name, filename FROM sysfiles
> I have shrunk database files occasionally and I always used the logical
> filename. In addition, here's a script (executed on my SQL Server 2000)
> which clearly show the usage of the *logical* filename:
> USE master
> CREATE DATABASE myTestShrink ON PRIMARY
> (NAME = N'myTestShrink', FILENAME = N'C:\myTestShrink.mdf'
> ,SIZE = 1024KB , FILEGROWTH = 10%)
> LOG ON
> (NAME = N'myTestShrink_log', FILENAME = N'C:\myTestShrink_log.ldf'
> , SIZE = 1024KB , FILEGROWTH = 10%)
> GO
> --Increase log file size
> BACKUP DATABASE myTestShrink TO DISK = 'nul'
> CREATE TABLE myTestShrink..test(c1 int identity, c2 char(2000))
> INSERT INTO myTestShrink..test(c2)
> SELECT TOP 10000 'hello' FROM sysobjects, syscolumns
> GO
> --Check log file size
> SELECT name, size*8192/(1024*1024) AS SizeInMb FROM
> myTestShrink..sysfiles WHERE groupid = 0
> --Empty the log
> BACKUP LOG myTestShrink WITH NO_LOG
> --Check the logical and physical files names
> SELECT name, filename FROM myTestShrink..sysfiles
> --Perform the shrink, ***based on logical filename***
> USE myTestShrink
> GO
> DBCC SHRINKFILE('myTestShrink_log', 2)
> --Check log file size
> SELECT name, size*8192/(1024*1024) AS SizeInMb FROM
> myTestShrink..sysfiles WHERE groupid = 0
> USE master
>
...just to add to Tibor's response. You can retreive the logical
filename by running sp_helpdb 'YourDatabaseName'.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||No, I did all different combinations--with ldf, without ldf, against the
master, against the database, and nothing was working until I placed the
database fileID of "2" in the suntax.
So, yes the documentation needs updating!
Thank you to all who helped!
childofthe1980s
"thomarse" wrote:
> you were getting that error because you were running it in the master
> database, not the databse you wanted to shrink the log for. the
> documentation's correct.
> "childofthe1980s" wrote:
> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> > analyzer in SQL 2000 against the master database.
> >
>|||childofthe1980s wrote:
> No, I did all different combinations--with ldf, without ldf, against the
> master, against the database, and nothing was working until I placed the
> database fileID of "2" in the suntax.
> So, yes the documentation needs updating!
>
I think you need to show up the documentation that's wrong. If you look
in BOL, the syntax is :
DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)
where file_name and file_id is explained as
"file_name
Is the logical name of the file shrunk. File names must conform to the
rules for identifiers. For more information, see Using Identifiers. "
"file_id
Is the identification (ID) number of the file to be shrunk. To obtain a
file ID, use the FILE_ID function or search sysfiles in the current
database."
I only see that it says you can use the LOGICAL filename or the fileID.
I don't see it mentions the physical filename anywhere?
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
HELP--still cannot shrink the transaction log--PLEASE HELP
This is an update to my other posting. I know that it is unorthodox to post
twice but I don't know what else to do.
Below is my original posting. I had already done the items posted by the
other gentleman such as reivewing the sysfiles. I cannot get the logical
file name from anywhere either.
Again, please help! Should I just not shrink the file. The client only has
1.82 GB of free space left and there is nothing else that can be deleted.
Below is my message:
I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
analyzer in SQL 2000 against the master database.
What is the EXACT syntax? You see, the name of the database is JC and the
log file is named GPSJClog.ldf. I have tried to place both names in the
syntax of the statement but with no success. I keep getting a message sayin
g
that the file name is not in sysfiles.
This is what I have tried:
DBCC SHRINKFILE (GPJClog.ldf , 2)
Please help!
childofthe1980sNevermind. I figured it out.
I had to run the following scripts against each of the databases:
BACKUP LOG JC with TRUNCATE_ONLY
DBCC SHRINKFILE (2, 20)
You see, the support documentation on the web did not say that you have to
take the fileID of the database--2 in the syntax above-- in order to make it
work! You get the number 2 from running the following query against the
database:
select * from sysfiles
It would have been nice if this had been in the documentation!
Thanks!
childofthe1980s
"childofthe1980s" wrote:
> Hello:
> This is an update to my other posting. I know that it is unorthodox to po
st
> twice but I don't know what else to do.
> Below is my original posting. I had already done the items posted by the
> other gentleman such as reivewing the sysfiles. I cannot get the logical
> file name from anywhere either.
> Again, please help! Should I just not shrink the file. The client only h
as
> 1.82 GB of free space left and there is nothing else that can be deleted.
> Below is my message:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in que
ry
> analyzer in SQL 2000 against the master database.
> What is the EXACT syntax? You see, the name of the database is JC and the
> log file is named GPSJClog.ldf. I have tried to place both names in the
> syntax of the statement but with no success. I keep getting a message say
ing
> that the file name is not in sysfiles.
> This is what I have tried:
> DBCC SHRINKFILE (GPJClog.ldf , 2)
> Please help!
> childofthe1980s
>|||> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make
it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
The documentation clearly states that you can use *either* the logical filen
ame or the file id. The
documentation is correct. The command you posted had 'GPSJCLog.ldf' for the
logical filename, and I
guess that you by mistake put the *physical* filename there instead of the *
logical* filename. If
you show ut the output from below, we can say for sure:
SELECT name, filename FROM sysfiles
I have shrunk database files occasionally and I always used the logical file
name. In addition,
here's a script (executed on my SQL Server 2000) which clearly show the usag
e of the *logical*
filename:
USE master
CREATE DATABASE myTestShrink ON PRIMARY
(NAME = N'myTestShrink', FILENAME = N'C:\myTestShrink.mdf'
,SIZE = 1024KB , FILEGROWTH = 10%)
LOG ON
(NAME = N'myTestShrink_log', FILENAME = N'C:\myTestShrink_log.ldf'
, SIZE = 1024KB , FILEGROWTH = 10%)
GO
--Increase log file size
BACKUP DATABASE myTestShrink TO DISK = 'nul'
CREATE TABLE myTestShrink..test(c1 int identity, c2 char(2000))
INSERT INTO myTestShrink..test(c2)
SELECT TOP 10000 'hello' FROM sysobjects, syscolumns
GO
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles W
HERE groupid = 0
--Empty the log
BACKUP LOG myTestShrink WITH NO_LOG
--Check the logical and physical files names
SELECT name, filename FROM myTestShrink..sysfiles
--Perform the shrink, ***based on logical filename***
USE myTestShrink
GO
DBCC SHRINKFILE('myTestShrink_log', 2)
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles W
HERE groupid = 0
USE master
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in messa
ge
news:868E9C82-C2C4-4151-BBBD-40741201E676@.microsoft.com...[vbcol=seagreen]
> Nevermind. I figured it out.
> I had to run the following scripts against each of the databases:
> BACKUP LOG JC with TRUNCATE_ONLY
> DBCC SHRINKFILE (2, 20)
> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make
it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
> Thanks!
> childofthe1980s
> "childofthe1980s" wrote:
>|||you were getting that error because you were running it in the master
database, not the databse you wanted to shrink the log for. the
documentation's correct.
"childofthe1980s" wrote:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in que
ry
> analyzer in SQL 2000 against the master database.
>|||Tibor Karaszi wrote:
> The documentation clearly states that you can use *either* the logical
> filename or the file id. The documentation is correct. The command you
> posted had 'GPSJCLog.ldf' for the logical filename, and I guess that you
> by mistake put the *physical* filename there instead of the *logical*
> filename. If you show ut the output from below, we can say for sure:
> SELECT name, filename FROM sysfiles
> I have shrunk database files occasionally and I always used the logical
> filename. In addition, here's a script (executed on my SQL Server 2000)
> which clearly show the usage of the *logical* filename:
> USE master
> CREATE DATABASE myTestShrink ON PRIMARY
> (NAME = N'myTestShrink', FILENAME = N'C:\myTestShrink.mdf'
> ,SIZE = 1024KB , FILEGROWTH = 10%)
> LOG ON
> (NAME = N'myTestShrink_log', FILENAME = N'C:\myTestShrink_log.ldf'
> , SIZE = 1024KB , FILEGROWTH = 10%)
> GO
> --Increase log file size
> BACKUP DATABASE myTestShrink TO DISK = 'nul'
> CREATE TABLE myTestShrink..test(c1 int identity, c2 char(2000))
> INSERT INTO myTestShrink..test(c2)
> SELECT TOP 10000 'hello' FROM sysobjects, syscolumns
> GO
> --Check log file size
> SELECT name, size*8192/(1024*1024) AS SizeInMb FROM
> myTestShrink..sysfiles WHERE groupid = 0
> --Empty the log
> BACKUP LOG myTestShrink WITH NO_LOG
> --Check the logical and physical files names
> SELECT name, filename FROM myTestShrink..sysfiles
> --Perform the shrink, ***based on logical filename***
> USE myTestShrink
> GO
> DBCC SHRINKFILE('myTestShrink_log', 2)
> --Check log file size
> SELECT name, size*8192/(1024*1024) AS SizeInMb FROM
> myTestShrink..sysfiles WHERE groupid = 0
> USE master
>
...just to add to Tibor's response. You can retreive the logical
filename by running sp_helpdb 'YourDatabaseName'.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||No, I did all different combinations--with ldf, without ldf, against the
master, against the database, and nothing was working until I placed the
database fileID of "2" in the suntax.
So, yes the documentation needs updating!
Thank you to all who helped!
childofthe1980s
"thomarse" wrote:
> you were getting that error because you were running it in the master
> database, not the databse you wanted to shrink the log for. the
> documentation's correct.
> "childofthe1980s" wrote:
>
>|||childofthe1980s wrote:
> No, I did all different combinations--with ldf, without ldf, against the
> master, against the database, and nothing was working until I placed the
> database fileID of "2" in the suntax.
> So, yes the documentation needs updating!
>
I think you need to show up the documentation that's wrong. If you look
in BOL, the syntax is :
DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)
where file_name and file_id is explained as
"file_name
Is the logical name of the file shrunk. File names must conform to the
rules for identifiers. For more information, see Using Identifiers. "
"file_id
Is the identification (ID) number of the file to be shrunk. To obtain a
file ID, use the FILE_ID function or search sysfiles in the current
database."
I only see that it says you can use the LOGICAL filename or the fileID.
I don't see it mentions the physical filename anywhere?
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
HELP--still cannot shrink the transaction log--PLEASE HELP
This is an update to my other posting. I know that it is unorthodox to post
twice but I don't know what else to do.
Below is my original posting. I had already done the items posted by the
other gentleman such as reivewing the sysfiles. I cannot get the logical
file name from anywhere either.
Again, please help! Should I just not shrink the file. The client only has
1.82 GB of free space left and there is nothing else that can be deleted.
Below is my message:
I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
analyzer in SQL 2000 against the master database.
What is the EXACT syntax? You see, the name of the database is JC and the
log file is named GPSJClog.ldf. I have tried to place both names in the
syntax of the statement but with no success. I keep getting a message saying
that the file name is not in sysfiles.
This is what I have tried:
DBCC SHRINKFILE (GPJClog.ldf , 2)
Please help!
childofthe1980s
Nevermind. I figured it out.
I had to run the following scripts against each of the databases:
BACKUP LOG JC with TRUNCATE_ONLY
DBCC SHRINKFILE (2, 20)
You see, the support documentation on the web did not say that you have to
take the fileID of the database--2 in the syntax above-- in order to make it
work! You get the number 2 from running the following query against the
database:
select * from sysfiles
It would have been nice if this had been in the documentation!
Thanks!
childofthe1980s
"childofthe1980s" wrote:
> Hello:
> This is an update to my other posting. I know that it is unorthodox to post
> twice but I don't know what else to do.
> Below is my original posting. I had already done the items posted by the
> other gentleman such as reivewing the sysfiles. I cannot get the logical
> file name from anywhere either.
> Again, please help! Should I just not shrink the file. The client only has
> 1.82 GB of free space left and there is nothing else that can be deleted.
> Below is my message:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> analyzer in SQL 2000 against the master database.
> What is the EXACT syntax? You see, the name of the database is JC and the
> log file is named GPSJClog.ldf. I have tried to place both names in the
> syntax of the statement but with no success. I keep getting a message saying
> that the file name is not in sysfiles.
> This is what I have tried:
> DBCC SHRINKFILE (GPJClog.ldf , 2)
> Please help!
> childofthe1980s
>
|||you were getting that error because you were running it in the master
database, not the databse you wanted to shrink the log for. the
documentation's correct.
"childofthe1980s" wrote:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> analyzer in SQL 2000 against the master database.
>
|||No, I did all different combinations--with ldf, without ldf, against the
master, against the database, and nothing was working until I placed the
database fileID of "2" in the suntax.
So, yes the documentation needs updating!
Thank you to all who helped!
childofthe1980s
"thomarse" wrote:
> you were getting that error because you were running it in the master
> database, not the databse you wanted to shrink the log for. the
> documentation's correct.
> "childofthe1980s" wrote:
>
Help-Recovering SQL 2000 Database.
Sorry for the late reply,
The error log just says the below and halts at the last step.
2004-09-13 17:09:54.39 spid11 Starting up database 'jennifertest'.
2004-09-13 17:10:02.75 spid3 Recovery complete.
2004-09-13 17:10:02.75 spid3 SQL global counter collection task is
created.
Do you want any other file like SQLDump?
TIA
--
Santosh
Can you check the SQL Server error log and post the error messages here? You
will find the log in MSSQL\Log folder under the installation path.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
news:eHXmsUcmEHA.3352@.TK2MSFTNGP10.phx.gbl...
> Friends,
>
> The SQL server 2000 was running fine until all of a Sudden I got this
> message,
>
> SQL Server is aborting. Fatal exception c0000005 caught.
>
> The Service would not start, and I need help in Recovering it.
>
> Any help is greatly Appreciated.
>
> --
>
> Santosh
>
>The messages you posted indicate that the SQL Server is probably running
fine. Is that the case, or are you still not able to start your SQL Server?
What happens when you try to start your MSSQLServer service from the
services applet?
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
news:%23cqNlEnmEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi Vyas,
> Sorry for the late reply,
> The error log just says the below and halts at the last step.
> 2004-09-13 17:09:54.39 spid11 Starting up database 'jennifertest'.
> 2004-09-13 17:10:02.75 spid3 Recovery complete.
> 2004-09-13 17:10:02.75 spid3 SQL global counter collection task is
> created.
> Do you want any other file like SQLDump?
> TIA
> --
> Santosh
> Can you check the SQL Server error log and post the error messages here?
You
> will find the log in MSSQL\Log folder under the installation path.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
> news:eHXmsUcmEHA.3352@.TK2MSFTNGP10.phx.gbl...
> > Friends,
> >
> > The SQL server 2000 was running fine until all of a Sudden I got this
> > message,
> >
> > SQL Server is aborting. Fatal exception c0000005 caught.
> >
> > The Service would not start, and I need help in Recovering it.
> >
> > Any help is greatly Appreciated.
> >
> > --
> >
> > Santosh
> >
> >
>
>|||When I try from the Services I get an error saying the Services Could not be
started, and the SQL server does not start at all.
Also I am not able to connect to the Database via Enterprise Manager.
Currently I have shut down the server, but I remember getting a message
something like "c000005 Dump".
--
Santosh
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OwbAc3nmEHA.1136@.TK2MSFTNGP10.phx.gbl...
> The messages you posted indicate that the SQL Server is probably running
> fine. Is that the case, or are you still not able to start your SQL
> Server?
> What happens when you try to start your MSSQLServer service from the
> services applet?
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
> news:%23cqNlEnmEHA.1656@.TK2MSFTNGP09.phx.gbl...
>> Hi Vyas,
>> Sorry for the late reply,
>> The error log just says the below and halts at the last step.
>> 2004-09-13 17:09:54.39 spid11 Starting up database 'jennifertest'.
>> 2004-09-13 17:10:02.75 spid3 Recovery complete.
>> 2004-09-13 17:10:02.75 spid3 SQL global counter collection task is
>> created.
>> Do you want any other file like SQLDump?
>> TIA
>> --
>> Santosh
>> Can you check the SQL Server error log and post the error messages here?
> You
>> will find the log in MSSQL\Log folder under the installation path.
>> --
>> Vyas, MVP (SQL Server)
>> http://vyaskn.tripod.com/
>>
>> "Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
>> news:eHXmsUcmEHA.3352@.TK2MSFTNGP10.phx.gbl...
>> > Friends,
>> >
>> > The SQL server 2000 was running fine until all of a Sudden I got this
>> > message,
>> >
>> > SQL Server is aborting. Fatal exception c0000005 caught.
>> >
>> > The Service would not start, and I need help in Recovering it.
>> >
>> > Any help is greatly Appreciated.
>> >
>> > --
>> >
>> > Santosh
>> >
>> >
>>
>>
>|||>> something like "c000005 Dump".
That indicates an Access Violation, and all AVs are bugs. So I suggest you
open a case with Microsoft. They will refund your money, if it turns out to
be a bug.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
news:u20RwAomEHA.3756@.TK2MSFTNGP09.phx.gbl...
> When I try from the Services I get an error saying the Services Could not
be
> started, and the SQL server does not start at all.
> Also I am not able to connect to the Database via Enterprise Manager.
> Currently I have shut down the server, but I remember getting a message
> something like "c000005 Dump".
> --
> Santosh
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:OwbAc3nmEHA.1136@.TK2MSFTNGP10.phx.gbl...
> > The messages you posted indicate that the SQL Server is probably running
> > fine. Is that the case, or are you still not able to start your SQL
> > Server?
> > What happens when you try to start your MSSQLServer service from the
> > services applet?
> > --
> > Vyas, MVP (SQL Server)
> > http://vyaskn.tripod.com/
> >
> >
> > "Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
> > news:%23cqNlEnmEHA.1656@.TK2MSFTNGP09.phx.gbl...
> >> Hi Vyas,
> >>
> >> Sorry for the late reply,
> >> The error log just says the below and halts at the last step.
> >>
> >> 2004-09-13 17:09:54.39 spid11 Starting up database 'jennifertest'.
> >> 2004-09-13 17:10:02.75 spid3 Recovery complete.
> >> 2004-09-13 17:10:02.75 spid3 SQL global counter collection task is
> >> created.
> >>
> >> Do you want any other file like SQLDump?
> >>
> >> TIA
> >>
> >> --
> >>
> >> Santosh
> >>
> >> Can you check the SQL Server error log and post the error messages
here?
> > You
> >> will find the log in MSSQL\Log folder under the installation path.
> >> --
> >> Vyas, MVP (SQL Server)
> >> http://vyaskn.tripod.com/
> >>
> >>
> >> "Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
> >> news:eHXmsUcmEHA.3352@.TK2MSFTNGP10.phx.gbl...
> >> > Friends,
> >> >
> >> > The SQL server 2000 was running fine until all of a Sudden I got this
> >> > message,
> >> >
> >> > SQL Server is aborting. Fatal exception c0000005 caught.
> >> >
> >> > The Service would not start, and I need help in Recovering it.
> >> >
> >> > Any help is greatly Appreciated.
> >> >
> >> > --
> >> >
> >> > Santosh
> >> >
> >> >
> >>
> >>
> >>
> >>
> >
> >
>
Monday, February 27, 2012
Help: Query
I have 2 tables like below
CREATE TABLE Location
(
LocationID int NOT NULL,
LocationName char (30) NOT NULL UNIQUE,
CONSTRAINT PK_Location PRIMARY KEY (LocationID)
)
CREATE TABLE Parts
(
PartID int NOT NULL,
LocationID int NOT NULL,
PartName char (30) NOT NULL,
CONSTRAINT PK_Parts PRIMARY KEY (PartID),
CONSTRAINT FK_PartsLocation FOREIGN KEY (Location ID)
REFERENCES Location (LocationID)
)
I have created a stored procedure to delete all of partid on a certain
location as shown below.
CREATE PROCEDURE sp_DeleteLocation @.LocName char(30) AS
BEGIN
DECLARE @.PartID int
DECLARE crs_Parts CURSOR FOR
SELECT p.PartID
FROM Parts AS p INNER JOIN Location AS 1
ON p.LocationID = @.LocName
WHERE l.LocationName = @.LocName
OPEN crs_Parts
FETCH NEXT FROM crs_Parts INTO @.PartID
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
DELETE Parts WHERE CURRENT OF crs_Parts
FETCH NEXT FROM crs_Parts INTO @.PartID
END
CLOSE crs_Parts
DEALLOCATE crs_Parts
DELETE Location WHERE LocationName = @.LocName
END
My Question is how to replace the cursor operation in the stored
procedure by using single DELETE statement?
Thanks
Robert LieHi Robert
Jusr the query this way,
DELETE Parts
FROM Parts INNER JOIN Location
ON ON Parts.LocationID = Location.LocationID
WHERE Location.LocationName = @.LocName
hope this is the one that you are looking for:
thanks and regards
Chandra
"Robert Lie" wrote:
> Hi,
> I have 2 tables like below
> CREATE TABLE Location
> (
> LocationID int NOT NULL,
> LocationName char (30) NOT NULL UNIQUE,
> CONSTRAINT PK_Location PRIMARY KEY (LocationID)
> )
>
> CREATE TABLE Parts
> (
> PartID int NOT NULL,
> LocationID int NOT NULL,
> PartName char (30) NOT NULL,
> CONSTRAINT PK_Parts PRIMARY KEY (PartID),
> CONSTRAINT FK_PartsLocation FOREIGN KEY (Location ID)
> REFERENCES Location (LocationID)
> )
>
> I have created a stored procedure to delete all of partid on a certain
> location as shown below.
> CREATE PROCEDURE sp_DeleteLocation @.LocName char(30) AS
> BEGIN
> DECLARE @.PartID int
> DECLARE crs_Parts CURSOR FOR
> SELECT p.PartID
> FROM Parts AS p INNER JOIN Location AS 1
> ON p.LocationID = @.LocName
> WHERE l.LocationName = @.LocName
> OPEN crs_Parts
> FETCH NEXT FROM crs_Parts INTO @.PartID
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> DELETE Parts WHERE CURRENT OF crs_Parts
> FETCH NEXT FROM crs_Parts INTO @.PartID
> END
> CLOSE crs_Parts
> DEALLOCATE crs_Parts
> DELETE Location WHERE LocationName = @.LocName
> END
> My Question is how to replace the cursor operation in the stored
> procedure by using single DELETE statement?
> Thanks
> Robert Lie
>
Help: Purge Table Stored Procedure
table. I have two tables (script below):
Schedule
ScheduleHistory
I need to purge records out of ScheduleHistory. The problem is that
the # of records that needs to be "kept" is dynamic, and stored in the
Schedule table. So all records in ScheduleHistory should be purged,
except for the most recent "x" number of records.
For instance, for each Schedule row, you can specify how many records
to "keep" at all times. I want to keep the most recent "x" # of
ScheduleHistory rows for each Schedule.
In the script below, I have two schedules ("test1" and "test2"). One
of them specifies that the most recent "5" records in ScheduleHistory
should be kept, and the other specifies that the most recent "3"
records in ScheduleHistory should be kept.
So, I need a stored procedure that can clean up this table on demand.
I can't figure this one out...it's over my head.
Dan
------
SCRIPT BELOW
------
CREATE TABLE [dbo].[Schedule] (
[ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
[TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[TimeLapse] [int] NOT NULL ,
[TimeLapseMeasurement] [varchar] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RetryTimeLapse] [int] NOT NULL ,
[RetryTimeLapseMeasurement] [varchar] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RetainHistoryNum] [int] NOT NULL ,
[ObjectDependencies] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ScheduleHistory] (
[ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
[ScheduleID] [int] NOT NULL ,
[StartDate] [datetime] NOT NULL ,
[EndDate] [datetime] NULL ,
[Succeeded] [bit] NULL ,
[LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NextStart] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
1:01 PM', 1, '', '1/2/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
1:01 PM', 1, '', '1/2/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
1:01 PM', 1, '', '1/3/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
1:01 PM', 1, '', '1/3/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
1:01 PM', 1, '', '1/4/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
1:01 PM', 1, '', '1/4/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
1:01 PM', 1, '', '1/5/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
1:01 PM', 1, '', '1/5/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
1:01 PM', 1, '', '1/6/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
1:01 PM', 1, '', '1/6/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
1:01 PM', 1, '', '1/7/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
1:01 PM', 1, '', '1/7/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
1:01 PM', 1, '', '1/8/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
1:01 PM', 1, '', '1/8/2000 1:00 PM')Assuming you want the keep the most recent rows based on StartDate, one
method:
DELETE FROM ScheduleHistory
FROM Schedule s
WHERE
(
SELECT COUNT(*)
FROM ScheduleHistory sh
WHERE
sh.ScheduleID = ScheduleHistory.ScheduleID AND
sh.ScheduleID = s.ScheduleID AND
sh.StartDate >= ScheduleHistory.StartDate
) > s.RetainHistoryNum
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Caron" <dancaron-listserv0687@.mailblocks.com> wrote in message
news:4d6e6a6c.0404231047.60cd40d0@.posting.google.c om...
> I have to create a stored procedure to purge "x" # of records from a
> table. I have two tables (script below):
> Schedule
> ScheduleHistory
> I need to purge records out of ScheduleHistory. The problem is that
> the # of records that needs to be "kept" is dynamic, and stored in the
> Schedule table. So all records in ScheduleHistory should be purged,
> except for the most recent "x" number of records.
> For instance, for each Schedule row, you can specify how many records
> to "keep" at all times. I want to keep the most recent "x" # of
> ScheduleHistory rows for each Schedule.
> In the script below, I have two schedules ("test1" and "test2"). One
> of them specifies that the most recent "5" records in ScheduleHistory
> should be kept, and the other specifies that the most recent "3"
> records in ScheduleHistory should be kept.
> So, I need a stored procedure that can clean up this table on demand.
> I can't figure this one out...it's over my head.
> Dan
> ------
> SCRIPT BELOW
> ------
>
> CREATE TABLE [dbo].[Schedule] (
> [ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
> [TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [TimeLapse] [int] NOT NULL ,
> [TimeLapseMeasurement] [varchar] (2) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RetryTimeLapse] [int] NOT NULL ,
> [RetryTimeLapseMeasurement] [varchar] (2) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RetainHistoryNum] [int] NOT NULL ,
> [ObjectDependencies] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ScheduleHistory] (
> [ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
> [ScheduleID] [int] NOT NULL ,
> [StartDate] [datetime] NOT NULL ,
> [EndDate] [datetime] NULL ,
> [Succeeded] [bit] NULL ,
> [LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [NextStart] [datetime] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
> INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
> INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
> 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
> 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
> 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
> 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
> 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
> 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
> 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
> 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
> 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
> 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
> 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
> 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
> 1:01 PM', 1, '', '1/8/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
> 1:01 PM', 1, '', '1/8/2000 1:00 PM')|||That is exactly what I needed. Thank you very much Dan.
-Dan
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<MLvic.10091$e4.6777@.newsread2.news.pas.earthlink.n et>...
> Assuming you want the keep the most recent rows based on StartDate, one
> method:
> DELETE FROM ScheduleHistory
> FROM Schedule s
> WHERE
> (
> SELECT COUNT(*)
> FROM ScheduleHistory sh
> WHERE
> sh.ScheduleID = ScheduleHistory.ScheduleID AND
> sh.ScheduleID = s.ScheduleID AND
> sh.StartDate >= ScheduleHistory.StartDate
> ) > s.RetainHistoryNum
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dan Caron" <dancaron-listserv0687@.mailblocks.com> wrote in message
> news:4d6e6a6c.0404231047.60cd40d0@.posting.google.c om...
> > I have to create a stored procedure to purge "x" # of records from a
> > table. I have two tables (script below):
> > Schedule
> > ScheduleHistory
> > I need to purge records out of ScheduleHistory. The problem is that
> > the # of records that needs to be "kept" is dynamic, and stored in the
> > Schedule table. So all records in ScheduleHistory should be purged,
> > except for the most recent "x" number of records.
> > For instance, for each Schedule row, you can specify how many records
> > to "keep" at all times. I want to keep the most recent "x" # of
> > ScheduleHistory rows for each Schedule.
> > In the script below, I have two schedules ("test1" and "test2"). One
> > of them specifies that the most recent "5" records in ScheduleHistory
> > should be kept, and the other specifies that the most recent "3"
> > records in ScheduleHistory should be kept.
> > So, I need a stored procedure that can clean up this table on demand.
> > I can't figure this one out...it's over my head.
> > Dan
> > ------
> > SCRIPT BELOW
> > ------
> > CREATE TABLE [dbo].[Schedule] (
> > [ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
> > [TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NOT NULL ,
> > [TimeLapse] [int] NOT NULL ,
> > [TimeLapseMeasurement] [varchar] (2) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [RetryTimeLapse] [int] NOT NULL ,
> > [RetryTimeLapseMeasurement] [varchar] (2) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [RetainHistoryNum] [int] NOT NULL ,
> > [ObjectDependencies] [varchar] (300) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL
> > ) ON [PRIMARY]
> > GO
> > CREATE TABLE [dbo].[ScheduleHistory] (
> > [ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
> > [ScheduleID] [int] NOT NULL ,
> > [StartDate] [datetime] NOT NULL ,
> > [EndDate] [datetime] NULL ,
> > [Succeeded] [bit] NULL ,
> > [LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [NextStart] [datetime] NULL
> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> > GO
> > INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
> > INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
> > INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
> > 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
> > 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
> > 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
> > 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
> > 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
> > 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
> > 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
> > 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
> > 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
> > 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
> > 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
> > 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
> > 1:01 PM', 1, '', '1/8/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
> > 1:01 PM', 1, '', '1/8/2000 1:00 PM')|||That is exactly what I needed. Thank you very much Dan.
-Dan
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<MLvic.10091$e4.6777@.newsread2.news.pas.earthlink.n et>...
> Assuming you want the keep the most recent rows based on StartDate, one
> method:
> DELETE FROM ScheduleHistory
> FROM Schedule s
> WHERE
> (
> SELECT COUNT(*)
> FROM ScheduleHistory sh
> WHERE
> sh.ScheduleID = ScheduleHistory.ScheduleID AND
> sh.ScheduleID = s.ScheduleID AND
> sh.StartDate >= ScheduleHistory.StartDate
> ) > s.RetainHistoryNum
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dan Caron" <dancaron-listserv0687@.mailblocks.com> wrote in message
> news:4d6e6a6c.0404231047.60cd40d0@.posting.google.c om...
> > I have to create a stored procedure to purge "x" # of records from a
> > table. I have two tables (script below):
> > Schedule
> > ScheduleHistory
> > I need to purge records out of ScheduleHistory. The problem is that
> > the # of records that needs to be "kept" is dynamic, and stored in the
> > Schedule table. So all records in ScheduleHistory should be purged,
> > except for the most recent "x" number of records.
> > For instance, for each Schedule row, you can specify how many records
> > to "keep" at all times. I want to keep the most recent "x" # of
> > ScheduleHistory rows for each Schedule.
> > In the script below, I have two schedules ("test1" and "test2"). One
> > of them specifies that the most recent "5" records in ScheduleHistory
> > should be kept, and the other specifies that the most recent "3"
> > records in ScheduleHistory should be kept.
> > So, I need a stored procedure that can clean up this table on demand.
> > I can't figure this one out...it's over my head.
> > Dan
> > ------
> > SCRIPT BELOW
> > ------
> > CREATE TABLE [dbo].[Schedule] (
> > [ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
> > [TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NOT NULL ,
> > [TimeLapse] [int] NOT NULL ,
> > [TimeLapseMeasurement] [varchar] (2) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [RetryTimeLapse] [int] NOT NULL ,
> > [RetryTimeLapseMeasurement] [varchar] (2) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [RetainHistoryNum] [int] NOT NULL ,
> > [ObjectDependencies] [varchar] (300) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL
> > ) ON [PRIMARY]
> > GO
> > CREATE TABLE [dbo].[ScheduleHistory] (
> > [ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
> > [ScheduleID] [int] NOT NULL ,
> > [StartDate] [datetime] NOT NULL ,
> > [EndDate] [datetime] NULL ,
> > [Succeeded] [bit] NULL ,
> > [LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [NextStart] [datetime] NULL
> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> > GO
> > INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
> > INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
> > INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
> > 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
> > 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
> > 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
> > 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
> > 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
> > 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
> > 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
> > 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
> > 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
> > 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
> > 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
> > 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
> > 1:01 PM', 1, '', '1/8/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
> > 1:01 PM', 1, '', '1/8/2000 1:00 PM')
Help: Purge Table Stored Procedure
table. I have two tables (script below):
Schedule
ScheduleHistory
I need to purge records out of ScheduleHistory. The problem is that
the # of records that needs to be "kept" is dynamic, and stored in the
Schedule table. So all records in ScheduleHistory should be purged,
except for the most recent "x" number of records.
For instance, for each Schedule row, you can specify how many records
to "keep" at all times. I want to keep the most recent "x" # of
ScheduleHistory rows for each Schedule.
In the script below, I have two schedules ("test1" and "test2"). One
of them specifies that the most recent "5" records in ScheduleHistory
should be kept, and the other specifies that the most recent "3"
records in ScheduleHistory should be kept.
So, I need a stored procedure that can clean up this table on demand.
I can't figure this one out...it's over my head.
Dan
------
SCRIPT BELOW
------
CREATE TABLE [dbo].[Schedule] (
[ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
[TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[TimeLapse] [int] NOT NULL ,
[TimeLapseMeasurement] [varchar] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RetryTimeLapse] [int] NOT NULL ,
[RetryTimeLapseMeasurement] [varchar] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RetainHistoryNum] [int] NOT NULL ,
[ObjectDependencies] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ScheduleHistory] (
[ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
[ScheduleID] [int] NOT NULL ,
[StartDate] [datetime] NOT NULL ,
[EndDate] [datetime] NULL ,
[Succeeded] [bit] NULL ,
[LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NextStart] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
1:01 PM', 1, '', '1/2/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
1:01 PM', 1, '', '1/2/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
1:01 PM', 1, '', '1/3/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
1:01 PM', 1, '', '1/3/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
1:01 PM', 1, '', '1/4/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
1:01 PM', 1, '', '1/4/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
1:01 PM', 1, '', '1/5/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
1:01 PM', 1, '', '1/5/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
1:01 PM', 1, '', '1/6/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
1:01 PM', 1, '', '1/6/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
1:01 PM', 1, '', '1/7/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
1:01 PM', 1, '', '1/7/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
1:01 PM', 1, '', '1/8/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
1:01 PM', 1, '', '1/8/2000 1:00 PM')Assuming you want the keep the most recent rows based on StartDate, one
method:
DELETE FROM ScheduleHistory
FROM Schedule s
WHERE
(
SELECT COUNT(*)
FROM ScheduleHistory sh
WHERE
sh.ScheduleID = ScheduleHistory.ScheduleID AND
sh.ScheduleID = s.ScheduleID AND
sh.StartDate >= ScheduleHistory.StartDate
) > s.RetainHistoryNum
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Caron" <dancaron-listserv0687@.mailblocks.com> wrote in message
news:4d6e6a6c.0404231047.60cd40d0@.posting.google.c om...
> I have to create a stored procedure to purge "x" # of records from a
> table. I have two tables (script below):
> Schedule
> ScheduleHistory
> I need to purge records out of ScheduleHistory. The problem is that
> the # of records that needs to be "kept" is dynamic, and stored in the
> Schedule table. So all records in ScheduleHistory should be purged,
> except for the most recent "x" number of records.
> For instance, for each Schedule row, you can specify how many records
> to "keep" at all times. I want to keep the most recent "x" # of
> ScheduleHistory rows for each Schedule.
> In the script below, I have two schedules ("test1" and "test2"). One
> of them specifies that the most recent "5" records in ScheduleHistory
> should be kept, and the other specifies that the most recent "3"
> records in ScheduleHistory should be kept.
> So, I need a stored procedure that can clean up this table on demand.
> I can't figure this one out...it's over my head.
> Dan
> ------
> SCRIPT BELOW
> ------
>
> CREATE TABLE [dbo].[Schedule] (
> [ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
> [TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [TimeLapse] [int] NOT NULL ,
> [TimeLapseMeasurement] [varchar] (2) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RetryTimeLapse] [int] NOT NULL ,
> [RetryTimeLapseMeasurement] [varchar] (2) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RetainHistoryNum] [int] NOT NULL ,
> [ObjectDependencies] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ScheduleHistory] (
> [ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
> [ScheduleID] [int] NOT NULL ,
> [StartDate] [datetime] NOT NULL ,
> [EndDate] [datetime] NULL ,
> [Succeeded] [bit] NULL ,
> [LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [NextStart] [datetime] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
> INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
> INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
> 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
> 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
> 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
> 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
> 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
> 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
> 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
> 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
> 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
> 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
> 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
> 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
> 1:01 PM', 1, '', '1/8/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
> 1:01 PM', 1, '', '1/8/2000 1:00 PM')
HELP: problem with using xp_sendmail
EXEC Master.dbo.xp_sendmail
@.dbuse = 'DanoneSSS',
@.recipients = 'xxx@.yyy.co.jp',
@.subject = 'mmm nnn',
@.message = @.parMessage,
@.query = 'SELECT CHECK_JS_CODE, CHECK_TYPE FROM T_CHECK_LOG WHERE CHECK_STATUS = 0 ORDER BY CHECK_ID',
@.attachments = 'ResultSet.txt',
@.attach_results = TRUE,
@.no_header = TRUE,
@.separator = '',
@.ansi_attachment = TRUE
as in my case, when i execute this particular stored procedure, SQL Server sends me the email AND with the necessary attachment
however, when i create a Job with "EXEC dbo.SOSOStoredProcedure," though the Job executes successfully, no EMAIL is sent to me.
the funny thing is, when i comment out the following lines
@.query = 'SELECT CHECK_JS_CODE, CHECK_TYPE FROM T_CHECK_LOG WHERE CHECK_STATUS = 0 ORDER BY CHECK_ID',
@.attachments = 'ResultSet.txt',
@.attach_results = TRUE,
@.no_header = TRUE,
@.separator = '',
@.ansi_attachment = TRUE
the Job executes successfully with an EMAIL being sent to me
does SQL Server's job CANNOT handle xp_sendmail that returns a RESULT set? or i may be mistaken somewhere...
thanks!Who is the owner of the job and what is the permission level ?|||Originally posted by rnealejr
Who is the owner of the job and what is the permission level ?
thanks for the reply...
but i did getting it working though...
by carefully reading through BO regarding xp_sendmail, it seems that in using xp_sendmail to send file attachments, the specific procedure must be defined in the master database. this is what i have done.
then, defined a permission level for the procedure's properties, and the specific entry in SQL Agent.
i may have gotten it working... but there still may be a gap in my understanding, and i would appreciate if anyone could check on this - Books Online could get so scarce in regards to explanations and examples.
again, thanks...|||the specific procedure must be defined in the master database
This is only done if the sa wants to conceal the behavior of xp_sendmail.
Which if that were the case you would have been unsuccessful regardless of where you ran the stored procedure.
then, defined a permission level for the procedure's properties, and the specific entry in SQL Agent.
Can you elaborate ?|||Alter the SP.
In the same sp, use exec master..xp_cmdshell 'isql "Your query" -o drive:\filename.txt etc'
Then remove the query portion from xp_sendmail.
Run the job again. It would work.|||I beleive (rightly or wrongly) that you are unable to run an open query within an xp_sendmail.
I am trying to run a stored procedure with an open query within it. wrapping it within an xp_sendmail.
Which fails.
Any sugestions on how to get this running.
Thanks
Sunday, February 19, 2012
Help: Backup Question
Does anyone can help me up to answer the question below:
============= You are the administrator of a SQL Server 2000 computer The server
contains a database named Sales that has two data files and one
transaction log file. Each data file is located on its own hard disk and
exists in its own filegroup. You perform full database, differential,
and transaction log backups on a regular basis. All backups made during
a single week are striped across three disk backup devices. A portion of
the header information for the current week's backups is shown in the
following table
BackupName BackupType BackupFinishDate
Sales_db_20000625 1 2000-06-25 21 57 04.000
Sales_tl_20000626_1 2 2000-06-26 11 04 22.000
Sales_tl_20000626_2 2 2000-06-26 15 06 33.000
Sales_df_20000626 5 2000-06-26 21 15 48.000
Sales_tl_20000627_1 2 2000-06-27 11 03 39.000
Sales_tl_20000627_2 2 2000-06-27 15 04 59.000
Sales_df_20000627 5 2000-06-27 21 31 13.000
Sales_tl_20000628_1 2 2000-06-28 11 05 16.000
On June 28, 2000, at 1:47 P M , the hard disk that contains the PRIMARY
filegroup fails. You want to recover as much data as possible. What
should you do? (Each correct answer presents part of the solution,
Choose all that apply)
A. Backup the transaction log by using the NO_LOG option
B. Backup the transaction log by using the NO_TRUNCATE option
C. Restore the most recent full database backup
D. Restore the most recent differential backup
E. Restore all differential backups in sequence
F. Restore all transaction log backups in sequence
G. Restore all transaction logs since the most recent differential backup
===================
Thanks
Robert LieRobert,
I will say:
B, C, D, G (with no recovery) and then restore the backup done in option B
with RECOVERY.
AMB
"Robert Lie" wrote:
> Dear All,
> Does anyone can help me up to answer the question below:
>
> =============> You are the administrator of a SQL Server 2000 computer The server
> contains a database named Sales that has two data files and one
> transaction log file. Each data file is located on its own hard disk and
> exists in its own filegroup. You perform full database, differential,
> and transaction log backups on a regular basis. All backups made during
> a single week are striped across three disk backup devices. A portion of
> the header information for the current week's backups is shown in the
> following table
> BackupName BackupType BackupFinishDate
> Sales_db_20000625 1 2000-06-25 21 57 04.000
> Sales_tl_20000626_1 2 2000-06-26 11 04 22.000
> Sales_tl_20000626_2 2 2000-06-26 15 06 33.000
> Sales_df_20000626 5 2000-06-26 21 15 48.000
> Sales_tl_20000627_1 2 2000-06-27 11 03 39.000
> Sales_tl_20000627_2 2 2000-06-27 15 04 59.000
> Sales_df_20000627 5 2000-06-27 21 31 13.000
> Sales_tl_20000628_1 2 2000-06-28 11 05 16.000
>
> On June 28, 2000, at 1:47 P M , the hard disk that contains the PRIMARY
> filegroup fails. You want to recover as much data as possible. What
> should you do? (Each correct answer presents part of the solution,
> Choose all that apply)
> A. Backup the transaction log by using the NO_LOG option
> B. Backup the transaction log by using the NO_TRUNCATE option
> C. Restore the most recent full database backup
> D. Restore the most recent differential backup
> E. Restore all differential backups in sequence
> F. Restore all transaction log backups in sequence
> G. Restore all transaction logs since the most recent differential backup
> ===================>
> Thanks
> Robert Lie
>|||Dear Alejandro Mesa,
Can you explain to me what's the meaning of number at the "Backup Type"
column?
Thanks
Robert Lie
Alejandro Mesa wrote:
> Robert,
> I will say:
> B, C, D, G (with no recovery) and then restore the backup done in option B
> with RECOVERY.
>
> AMB
> "Robert Lie" wrote:
>
>>Dear All,
>>Does anyone can help me up to answer the question below:
>>
>>=============>>You are the administrator of a SQL Server 2000 computer The server
>>contains a database named Sales that has two data files and one
>>transaction log file. Each data file is located on its own hard disk and
>>exists in its own filegroup. You perform full database, differential,
>>and transaction log backups on a regular basis. All backups made during
>>a single week are striped across three disk backup devices. A portion of
>>the header information for the current week's backups is shown in the
>>following table
>>BackupName BackupType BackupFinishDate
>>Sales_db_20000625 1 2000-06-25 21 57 04.000
>>Sales_tl_20000626_1 2 2000-06-26 11 04 22.000
>>Sales_tl_20000626_2 2 2000-06-26 15 06 33.000
>>Sales_df_20000626 5 2000-06-26 21 15 48.000
>>Sales_tl_20000627_1 2 2000-06-27 11 03 39.000
>>Sales_tl_20000627_2 2 2000-06-27 15 04 59.000
>>Sales_df_20000627 5 2000-06-27 21 31 13.000
>>Sales_tl_20000628_1 2 2000-06-28 11 05 16.000
>>
>>On June 28, 2000, at 1:47 P M , the hard disk that contains the PRIMARY
>>filegroup fails. You want to recover as much data as possible. What
>>should you do? (Each correct answer presents part of the solution,
>>Choose all that apply)
>>A. Backup the transaction log by using the NO_LOG option
>>B. Backup the transaction log by using the NO_TRUNCATE option
>>C. Restore the most recent full database backup
>>D. Restore the most recent differential backup
>>E. Restore all differential backups in sequence
>>F. Restore all transaction log backups in sequence
>>G. Restore all transaction logs since the most recent differential backup
>>===================>>
>>Thanks
>>Robert Lie|||I guess it is:
1 - db backup
2 - transaction log backup
5 - db differential backup.
Example:
use northwind
go
alter database northwind set recovery bulk_logged
go
backup database northwind to disk = 'c:\temp\nw.bak'
go
backup database northwind to disk = 'c:\temp\nw_diff.bak' with differential
go
backup log northwind to disk = 'c:\temp\nw_log.bak'
go
restore HEADERONLY from disk = 'c:\temp\nw.bak'
go
restore HEADERONLY from disk = 'c:\temp\nw_diff.bak'
go
restore HEADERONLY from disk = 'c:\temp\nw_log.bak'
go
alter database northwind set recovery simple
go
exec master..xp_cmdshell 'del c:\temp\nw*.bak'
go
AMB
"Robert Lie" wrote:
> Dear Alejandro Mesa,
> Can you explain to me what's the meaning of number at the "Backup Type"
> column?
> Thanks
> Robert Lie
> Alejandro Mesa wrote:
> > Robert,
> >
> > I will say:
> >
> > B, C, D, G (with no recovery) and then restore the backup done in option B
> > with RECOVERY.
> >
> >
> > AMB
> >
> > "Robert Lie" wrote:
> >
> >
> >>Dear All,
> >>
> >>Does anyone can help me up to answer the question below:
> >>
> >>
> >>=============> >>You are the administrator of a SQL Server 2000 computer The server
> >>contains a database named Sales that has two data files and one
> >>transaction log file. Each data file is located on its own hard disk and
> >>exists in its own filegroup. You perform full database, differential,
> >>and transaction log backups on a regular basis. All backups made during
> >>a single week are striped across three disk backup devices. A portion of
> >>the header information for the current week's backups is shown in the
> >>following table
> >>
> >>BackupName BackupType BackupFinishDate
> >>Sales_db_20000625 1 2000-06-25 21 57 04.000
> >>Sales_tl_20000626_1 2 2000-06-26 11 04 22.000
> >>Sales_tl_20000626_2 2 2000-06-26 15 06 33.000
> >>Sales_df_20000626 5 2000-06-26 21 15 48.000
> >>Sales_tl_20000627_1 2 2000-06-27 11 03 39.000
> >>Sales_tl_20000627_2 2 2000-06-27 15 04 59.000
> >>Sales_df_20000627 5 2000-06-27 21 31 13.000
> >>Sales_tl_20000628_1 2 2000-06-28 11 05 16.000
> >>
> >>
> >>On June 28, 2000, at 1:47 P M , the hard disk that contains the PRIMARY
> >>filegroup fails. You want to recover as much data as possible. What
> >>should you do? (Each correct answer presents part of the solution,
> >>Choose all that apply)
> >>
> >>A. Backup the transaction log by using the NO_LOG option
> >>B. Backup the transaction log by using the NO_TRUNCATE option
> >>C. Restore the most recent full database backup
> >>D. Restore the most recent differential backup
> >>E. Restore all differential backups in sequence
> >>F. Restore all transaction log backups in sequence
> >>G. Restore all transaction logs since the most recent differential backup
> >>
> >>===================> >>
> >>
> >>Thanks
> >>
> >>Robert Lie
> >>
>|||Hi,
Backup types: -
1 = Database
2 = Transaction Log
4 = File
5 = Differential Database
6 = Differential File
Thanks
Hari
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:eu3vQsfYFHA.4036@.tk2msftngp13.phx.gbl...
> Dear Alejandro Mesa,
> Can you explain to me what's the meaning of number at the "Backup Type"
> column?
> Thanks
> Robert Lie
> Alejandro Mesa wrote:
>> Robert,
>> I will say:
>> B, C, D, G (with no recovery) and then restore the backup done in option
>> B with RECOVERY.
>>
>> AMB
>> "Robert Lie" wrote:
>>
>>Dear All,
>>Does anyone can help me up to answer the question below:
>>
>>=============>>You are the administrator of a SQL Server 2000 computer The server
>>contains a database named Sales that has two data files and one
>>transaction log file. Each data file is located on its own hard disk and
>>exists in its own filegroup. You perform full database, differential, and
>>transaction log backups on a regular basis. All backups made during a
>>single week are striped across three disk backup devices. A portion of
>>the header information for the current week's backups is shown in the
>>following table
>>BackupName BackupType BackupFinishDate
>>Sales_db_20000625 1 2000-06-25 21 57 04.000
>>Sales_tl_20000626_1 2 2000-06-26 11 04 22.000
>>Sales_tl_20000626_2 2 2000-06-26 15 06 33.000
>>Sales_df_20000626 5 2000-06-26 21 15 48.000
>>Sales_tl_20000627_1 2 2000-06-27 11 03 39.000
>>Sales_tl_20000627_2 2 2000-06-27 15 04 59.000
>>Sales_df_20000627 5 2000-06-27 21 31 13.000
>>Sales_tl_20000628_1 2 2000-06-28 11 05 16.000
>>
>>On June 28, 2000, at 1:47 P M , the hard disk that contains the PRIMARY
>>filegroup fails. You want to recover as much data as possible. What
>>should you do? (Each correct answer presents part of the solution, Choose
>>all that apply)
>>A. Backup the transaction log by using the NO_LOG option
>>B. Backup the transaction log by using the NO_TRUNCATE option
>>C. Restore the most recent full database backup
>>D. Restore the most recent differential backup
>>E. Restore all differential backups in sequence
>>F. Restore all transaction log backups in sequence
>>G. Restore all transaction logs since the most recent differential
>>backup
>>===================>>
>>Thanks
>>Robert Lie|||Instead of keep asking all the questions from the tests, it might be better
if you start reading some litterature about MS SQL Server. A good and easy
place to start, would be to read Books On Line - that's a great ressource to
start with....
Regards
Steen
Robert Lie wrote:
> Dear Alejandro Mesa,
> Can you explain to me what's the meaning of number at the "Backup
> Type" column?
> Thanks
> Robert Lie
> Alejandro Mesa wrote:
>> Robert,
>> I will say:
>> B, C, D, G (with no recovery) and then restore the backup done in
>> option B with RECOVERY.
>>
>> AMB
>> "Robert Lie" wrote:
>>
>> Dear All,
>> Does anyone can help me up to answer the question below:
>>
>> =============>> You are the administrator of a SQL Server 2000 computer The server
>> contains a database named Sales that has two data files and one
>> transaction log file. Each data file is located on its own hard
>> disk and exists in its own filegroup. You perform full database,
>> differential, and transaction log backups on a regular basis. All
>> backups made
>> during a single week are striped across three disk backup devices. A
>> portion of the header information for the current week's backups is
>> shown in the following table
>> BackupName BackupType BackupFinishDate
>> Sales_db_20000625 1 2000-06-25 21 57 04.000
>> Sales_tl_20000626_1 2 2000-06-26 11 04 22.000
>> Sales_tl_20000626_2 2 2000-06-26 15 06 33.000
>> Sales_df_20000626 5 2000-06-26 21 15 48.000
>> Sales_tl_20000627_1 2 2000-06-27 11 03 39.000
>> Sales_tl_20000627_2 2 2000-06-27 15 04 59.000
>> Sales_df_20000627 5 2000-06-27 21 31 13.000
>> Sales_tl_20000628_1 2 2000-06-28 11 05 16.000
>>
>> On June 28, 2000, at 1:47 P M , the hard disk that contains the
>> PRIMARY filegroup fails. You want to recover as much data as
>> possible. What should you do? (Each correct answer presents part of
>> the solution, Choose all that apply)
>> A. Backup the transaction log by using the NO_LOG option
>> B. Backup the transaction log by using the NO_TRUNCATE option
>> C. Restore the most recent full database backup
>> D. Restore the most recent differential backup
>> E. Restore all differential backups in sequence
>> F. Restore all transaction log backups in sequence
>> G. Restore all transaction logs since the most recent differential
>> backup ===================>>
>> Thanks
>> Robert Lie