Restrict The Number Of Records From Query

Hi,
ive a table of over 90,000 records . is ther any method for restricting the number of records returned by a SELECT query
i want to implement a query system which will give me the first 50 records, next 50 ,... and so on
can ne body help ?Originally posted by baburajv
Hi,

ive a table of over 90,000 records . is ther any method for restricting the number of records returned by a SELECT query

i want to implement a query system which will give me the first 50 records, next 50 ,... and so on

can ne body help ?

select top 50 from table_name|||SELECT TOP 50 FROM TABLE_NAME

WILL RETURN THE TOP 50 ROWS FROM THE TABLE

HOW CAN I RETRIEVE THE NEXT 50 ROWS|||Originally posted by baburajv
SELECT TOP 50 FROM TABLE_NAME

WILL RETURN THE TOP 50 ROWS FROM THE TABLE

HOW CAN I RETRIEVE THE NEXT 50 ROWS

I think then u need to use temperory tables for it.|||select top NUMBER_OF_ROWS *
from table
where column NOT IN
(select top BEGIN_AT column
from table)

Restrict DTS execution

Hi,
Does anyone know how to restrict the exceution of DTS packages?
The case is I've got some users which want to see the packages that are
currently created but I do not want them to run those packages. I already
know how to restrict the creation via sp_add_dtspackage.
Hi!

> Does anyone know how to restrict the exceution of DTS packages?
> The case is I've got some users which want to see the packages that are
> currently created but I do not want them to run those packages. I already
> know how to restrict the creation via sp_add_dtspackage.
I find this article very helpful:
http://www.windowsitpro.com/Article/...11/27111.html.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message

restoring system databases

I've been testing a new 2005 server. After the install, I backed up the
system databases. I've done a lot of junk testing stuff since then, and
now I'm wondering if I should do a restore of the system databases before I
put the machine into production. If so, which ones?
If you want a clean start, consider running rebuildmaster - rerun setup with
rebuild flag.
e.g.
setup.exe REBUILDDATABASE=1
-oj
"HK" <replywithingroup@.notreal.com> wrote in message
news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
> I've been testing a new 2005 server. After the install, I backed up the
> system databases. I've done a lot of junk testing stuff since then, and
> now I'm wondering if I should do a restore of the system databases before
> I
> put the machine into production. If so, which ones?
>
|||Does that put it into the same state as just after an install?
If I had a test database still showing, for example, would that disappear?
"oj" <nospam_ojngo@.home.com> wrote in message
news:e2GjwiUVGHA.5364@.tk2msftngp13.phx.gbl...
> If you want a clean start, consider running rebuildmaster - rerun setup
with[vbcol=seagreen]
> rebuild flag.
> e.g.
> setup.exe REBUILDDATABASE=1
>
> --
> -oj
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
the[vbcol=seagreen]
and[vbcol=seagreen]
before
>
|||Correct. The system would look as if it's just installed.
Since the setup does not touch your user database, you can just reattach it
after the rebuild.
-oj
"HK" <replywithingroup@.notreal.com> wrote in message
news:0SnXf.18403$WK1.7806@.tornado.socal.rr.com...
> Does that put it into the same state as just after an install?
> If I had a test database still showing, for example, would that disappear?
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e2GjwiUVGHA.5364@.tk2msftngp13.phx.gbl...
> with
> the
> and
> before
>
|||That's a neat trick. Thanks.
"oj" <nospam_ojngo@.home.com> wrote in message
news:OgXKirUVGHA.5900@.tk2msftngp13.phx.gbl...
> Correct. The system would look as if it's just installed.
> Since the setup does not touch your user database, you can just reattach
it[vbcol=seagreen]
> after the rebuild.
> --
> -oj
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:0SnXf.18403$WK1.7806@.tornado.socal.rr.com...
disappear?
>

restoring replicated database

Hi,
I've restored a merge replicated database (publisher) on a new server
without replication.
Now i observe that there are some default replication procedures named
*_pal in my restored database. What is the way of deleting these
replication procedures. What will be the impact of the same.
Regds,
amit
Amit,
these procedures will need cleaning up by hand. sp_removedbreplication will
remove many system objects but these procs sometimes remain. You can
hand-craft a script to delete them (using information_schema.routines) or
just do it manually.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul thanks for you reply. We have configured this restored database
for replication and it has created new *pal. procedures. Is it ok if we
delete the earlier *pal procedure now?
|||Yes
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||thanks, but can you just let me know if there would be any impact if i
keep the old procedures instead of deleting them as i would not like to
change the system configuration if there is not impact.
|||If you mean problems caused by name conflicts and the like, I think the
complexity of the name ensures this isn't the case. I have seen systems that
have accumulated orphaned procs like this over several years and the DBAs
didn't even notice they were there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Restoring Master question

Ive done some reading and it appears as though restoring master to a box
with another name is frowned upon. Does anyone know if its supported or not?
If so, do you have step by step instructions? If not, what about another box
name but a matching named instance?
sql2k sp3
TIA, ChrisR
Hi
Check out:
http://support.microsoft.com/default...-us;Q314546#10
and
http://support.microsoft.com/default...en-us%3b224071
John
"ChrisR" <ChrisR@.NoEmails.com> wrote in message
news:u$x%23y2quEHA.2144@.tk2msftngp13.phx.gbl...
> Ive done some reading and it appears as though restoring master to a box
> with another name is frowned upon. Does anyone know if its supported or
not?
> If so, do you have step by step instructions? If not, what about another
box
> name but a matching named instance?
> --
> sql2k sp3
> TIA, ChrisR
>

Restoring Master question

Ive done some reading and it appears as though restoring master to a box
with another name is frowned upon. Does anyone know if its supported or not?
If so, do you have step by step instructions? If not, what about another box
name but a matching named instance?
--
sql2k sp3
TIA, ChrisRHi
Check out:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#10
and
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b224071
John
"ChrisR" <ChrisR@.NoEmails.com> wrote in message
news:u$x%23y2quEHA.2144@.tk2msftngp13.phx.gbl...
> Ive done some reading and it appears as though restoring master to a box
> with another name is frowned upon. Does anyone know if its supported or
not?
> If so, do you have step by step instructions? If not, what about another
box
> name but a matching named instance?
> --
> sql2k sp3
> TIA, ChrisR
>

Restoring Master question

Ive done some reading and it appears as though restoring master to a box
with another name is frowned upon. Does anyone know if its supported or not?
If so, do you have step by step instructions? If not, what about another box
name but a matching named instance?
sql2k sp3
TIA, ChrisRHi
Check out:
http://support.microsoft.com/defaul...n-us;Q314546#10
and
http://support.microsoft.com/defaul...ben-us%3b224071
John
"ChrisR" <ChrisR@.NoEmails.com> wrote in message
news:u$x%23y2quEHA.2144@.tk2msftngp13.phx.gbl...
> Ive done some reading and it appears as though restoring master to a box
> with another name is frowned upon. Does anyone know if its supported or
not?
> If so, do you have step by step instructions? If not, what about another
box
> name but a matching named instance?
> --
> sql2k sp3
> TIA, ChrisR
>

Restoring master database

Hi All
Im testing our disaster recovery plan by rebuilding our production server
onto a new server box with different hardware and partitions.
Ive started the SQL server in single user mode and restored the master
database to a success message but when it tries to restart it fails to start.
On checking the error log there are some VDN errors, I assume that this is
because the server Im restoring it to has different drive setup to the live
server.
Is there anyway around this problem?
Message posted via http://www.droptable.com
Hi
Does this occur on subsequent restarts?
John
"Tony S via droptable.com" <forum@.droptable.com> wrote in message
news:5410926593904@.droptable.com...
> Hi All
> I'm testing our disaster recovery plan by rebuilding our production server
> onto a new server box with different hardware and partitions.
> I've started the SQL server in single user mode and restored the master
> database to a success message but when it tries to restart it fails to
> start.
>
> On checking the error log there are some VDN errors, I assume that this is
> because the server I'm restoring it to has different drive setup to the
> live
> server.
> Is there anyway around this problem?
>
> --
> Message posted via http://www.droptable.com
|||Hi
I get the VDN error reported after everytime I try and restart the SQL Server,
regardless of how I try to restart it.
Tony
|||Hi Tony
Even with the -f flag?
Have you followed
http://support.microsoft.com/default...22120121120120
John
"Tony S via droptable.com" <forum@.droptable.com> wrote in message
news:54196F15F1D57@.droptable.com...
> Hi
> I get the VDN error reported after everytime I try and restart the SQL
> Server,
> regardless of how I try to restart it.
> Tony
|||Hi John
Yes it fails to start even with the -f flag
I've tried changing the startup parameters but because the SQL server isn't
starting I can not get into the properties through Enterprise Manager.
Im not sure how Attach/Detach can help me as Im trying to restore from
backup?
The common error Im getting is when the SQL server is trying to start it is
looking for the other system databases from a drive that doesnt exist
producing VDN errors
Tony
John Bell wrote:[vbcol=seagreen]
>Hi Tony
>Even with the -f flag?
>Have you followed
>http://support.microsoft.com/default...22120121120120
>John
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com
|||Hi
Test this out by calling sqlservr from a command prompt, see "sqlservr
Application" in Books online for parameters. Also check out the trace flag
3608 which will skip recovery on all databases except master, you can then
detach each database and re-attach with the new files as descibed for msdb
and model in
http://support.microsoft.com/default...22120121120120
To move tempdb use the alter database command as described.
John
"Tony S via droptable.com" <forum@.droptable.com> wrote in message
news:544195E6FFA0B@.droptable.com...
> Hi John
> Yes it fails to start even with the -f flag
> I've tried changing the startup parameters but because the SQL server
> isn't
> starting I can not get into the properties through Enterprise Manager.
> I'm not sure how Attach/Detach can help me as I'm trying to restore from
> backup?
> The common error I'm getting is when the SQL server is trying to start it
> is
> looking for the other system databases from a drive that doesn't exist
> producing VDN errors
> Tony
> John Bell wrote:
>
> --
> Message posted via http://www.droptable.com

Restoring master database

Hi All
I?m testing our disaster recovery plan by rebuilding our production server
onto a new server box with different hardware and partitions.
I?ve started the SQL server in single user mode and restored the master
database to a success message but when it tries to restart it fails to start.
On checking the error log there are some VDN errors, I assume that this is
because the server I?m restoring it to has different drive setup to the live
server.
Is there anyway around this problem?
--
Message posted via http://www.sqlmonster.comHi
Does this occur on subsequent restarts?
John
"Tony S via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:5410926593904@.SQLMonster.com...
> Hi All
> I'm testing our disaster recovery plan by rebuilding our production server
> onto a new server box with different hardware and partitions.
> I've started the SQL server in single user mode and restored the master
> database to a success message but when it tries to restart it fails to
> start.
>
> On checking the error log there are some VDN errors, I assume that this is
> because the server I'm restoring it to has different drive setup to the
> live
> server.
> Is there anyway around this problem?
>
> --
> Message posted via http://www.sqlmonster.com|||Hi
I get the VDN error reported after everytime I try and restart the SQL Server,
regardless of how I try to restart it.
Tony|||Hi Tony
Even with the -f flag?
Have you followed
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b224071#XSLTH3188121122120121120120
John
"Tony S via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:54196F15F1D57@.SQLMonster.com...
> Hi
> I get the VDN error reported after everytime I try and restart the SQL
> Server,
> regardless of how I try to restart it.
> Tony|||Hi John
Yes it fails to start even with the -f flag
I've tried changing the startup parameters but because the SQL server isn't
starting I can not get into the properties through Enterprise Manager.
I?m not sure how Attach/Detach can help me as I?m trying to restore from
backup?
The common error I?m getting is when the SQL server is trying to start it is
looking for the other system databases from a drive that doesn?t exist
producing VDN errors
Tony
John Bell wrote:
>Hi Tony
>Even with the -f flag?
>Have you followed
>http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b224071#XSLTH3188121122120121120120
>John
>> Hi
>[quoted text clipped - 3 lines]
>> Tony
Message posted via http://www.sqlmonster.com|||Hi
Test this out by calling sqlservr from a command prompt, see "sqlservr
Application" in Books online for parameters. Also check out the trace flag
3608 which will skip recovery on all databases except master, you can then
detach each database and re-attach with the new files as descibed for msdb
and model in
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b224071#XSLTH3188121122120121120120
To move tempdb use the alter database command as described.
John
"Tony S via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:544195E6FFA0B@.SQLMonster.com...
> Hi John
> Yes it fails to start even with the -f flag
> I've tried changing the startup parameters but because the SQL server
> isn't
> starting I can not get into the properties through Enterprise Manager.
> I'm not sure how Attach/Detach can help me as I'm trying to restore from
> backup?
> The common error I'm getting is when the SQL server is trying to start it
> is
> looking for the other system databases from a drive that doesn't exist
> producing VDN errors
> Tony
> John Bell wrote:
>>Hi Tony
>>Even with the -f flag?
>>Have you followed
>>http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b224071#XSLTH3188121122120121120120
>>John
>> Hi
>>[quoted text clipped - 3 lines]
>> Tony
>
> --
> Message posted via http://www.sqlmonster.com

Restoring master database

Hi All
Im testing our disaster recovery plan by rebuilding our production server
onto a new server box with different hardware and partitions.
Ive started the SQL server in single user mode and restored the master
database to a success message but when it tries to restart it fails to start
.
On checking the error log there are some VDN errors, I assume that this is
because the server Im restoring it to has different drive setup to the live
server.
Is there anyway around this problem?
Message posted via http://www.droptable.comHi
Does this occur on subsequent restarts?
John
"Tony S via droptable.com" <forum@.droptable.com> wrote in message
news:5410926593904@.droptable.com...
> Hi All
> I'm testing our disaster recovery plan by rebuilding our production server
> onto a new server box with different hardware and partitions.
> I've started the SQL server in single user mode and restored the master
> database to a success message but when it tries to restart it fails to
> start.
>
> On checking the error log there are some VDN errors, I assume that this is
> because the server I'm restoring it to has different drive setup to the
> live
> server.
> Is there anyway around this problem?
>
> --
> Message posted via http://www.droptable.com|||Hi
I get the VDN error reported after everytime I try and restart the SQL Serve
r,
regardless of how I try to restart it.
Tony|||Hi Tony
Even with the -f flag?
Have you followed
120121120120" target="_blank">http://support.microsoft.com/defaul...>
120121120120
John
"Tony S via droptable.com" <forum@.droptable.com> wrote in message
news:54196F15F1D57@.droptable.com...
> Hi
> I get the VDN error reported after everytime I try and restart the SQL
> Server,
> regardless of how I try to restart it.
> Tony|||Hi John
Yes it fails to start even with the -f flag
I've tried changing the startup parameters but because the SQL server isn't
starting I can not get into the properties through Enterprise Manager.
Im not sure how Attach/Detach can help me as Im trying to restore from
backup?
The common error Im getting is when the SQL server is trying to start it is
looking for the other system databases from a drive that doesnt exist
producing VDN errors
Tony
John Bell wrote:[vbcol=seagreen]
>Hi Tony
>Even with the -f flag?
>Have you followed
>2120121120120" target="_blank">http://support.microsoft.com/defaul...
2120121120120
>John
>
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com|||Hi
Test this out by calling sqlservr from a command prompt, see "sqlservr
Application" in Books online for parameters. Also check out the trace flag
3608 which will skip recovery on all databases except master, you can then
detach each database and re-attach with the new files as descibed for msdb
and model in
120121120120" target="_blank">http://support.microsoft.com/defaul...>
120121120120
To move tempdb use the alter database command as described.
John
"Tony S via droptable.com" <forum@.droptable.com> wrote in message
news:544195E6FFA0B@.droptable.com...
> Hi John
> Yes it fails to start even with the -f flag
> I've tried changing the startup parameters but because the SQL server
> isn't
> starting I can not get into the properties through Enterprise Manager.
> I'm not sure how Attach/Detach can help me as I'm trying to restore from
> backup?
> The common error I'm getting is when the SQL server is trying to start it
> is
> looking for the other system databases from a drive that doesn't exist
> producing VDN errors
> Tony
> John Bell wrote:
>
> --
> Message posted via http://www.droptable.com

Restoring Database using Scripts

I have and MSDE environment and I use "OSQL" to administer the database. I've written a backup script and restore script. My problem/question as to do with the restore. Both scripts run just fine.
However, I want my restore to use the lastest and greatest backup set within the backup device. I never really know what the lastest backup set is most of the time. But when ever my restore process runs I want it to use the lastest backup within the back
up device. How do I specify in my script to use the lastest backup set when a restore is done?
Thanks
Hi,
If you are going to restore in the same server see the MSDB..BACKUPSET table
for position. See the below query.
select database_name,backup_finish_date,position from msdb..backupset where
database_name='msdb'
If you are doing it in a different machne use the below command.
restore headeronly from disk='c:\msdb.bak'
You can restore the latest backup based on position
Thanks
Hari
MCDBA
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:C6B6DEC9-1CA4-4624-A6F9-2A7004037BC1@.microsoft.com...
> I have and MSDE environment and I use "OSQL" to administer the database.
I've written a backup script and restore script. My problem/question as to
do with the restore. Both scripts run just fine.
> However, I want my restore to use the lastest and greatest backup set
within the backup device. I never really know what the lastest backup set
is most of the time. But when ever my restore process runs I want it to use
the lastest backup within the backup device. How do I specify in my script
to use the lastest backup set when a restore is done?
> Thanks
|||hi Larry,
"Larry Bird" <LarryBird@.discussions.microsoft.com> ha scritto nel messaggio
news:C6B6DEC9-1CA4-4624-A6F9-2A7004037BC1@.microsoft.com...
> I have and MSDE environment and I use "OSQL" to administer the database.
>I've written a backup script and restore script. My problem/question as to
do with
>the restore. Both scripts run just fine.
> However, I want my restore to use the lastest and greatest backup set
within the
> backup device. I never really know what the lastest backup set is most of
the time.
>But when ever my restore process runs I want it to use the lastest backup
within the
>backup device. How do I specify in my script to use the lastest backup set
when a
>restore is done?
Im'm late, and Hari already pointed out what needed, but, anyway =;-D
I woul'd not automatically scritp this... restore shoul'd be better a manual
operation as it's not a dayly house keeping activity...
anyway... you can start from getting the max file position...
SET NOCOUNT ON
DECLARE @.db VARCHAR(128)
DECLARE @.file VARCHAR(128)
DECLARE @.cmd VARCHAR(1024)
SELECT @.file = 'D:\ADOvb6\VBH_FILE\vbhotel.bak' , @.db = 'vbhotel'
SET @.cmd = 'RESTORE HEADERONLY FROM DISK = '''+ @.file + ''''
CREATE TABLE #restore (
BackupName VARCHAR(128) ,
BackupDescription VARCHAR(128) ,
BackupType INT ,
ExpirationDate DATETIME ,
Compressed INT ,
Position INT ,
DeviceType INT ,
UserName VARCHAR(128) ,
ServerName VARCHAR(128) ,
DatabaseName VARCHAR(128) ,
DatabaseVersion INT ,
DatabaseCreationDate DATETIME ,
BackupSize INT ,
FirstLsn VARCHAR(128) ,
LastLsn VARCHAR(128) ,
CheckPointLsn VARCHAR(128) ,
DifferentialBaseLsn VARCHAR(128) ,
BackupStartDate DATETIME ,
BackupFinishDate DATETIME ,
SortOrder SMALLINT ,
CodePage SMALLINT ,
UnicodeLocaleId INT ,
UnicodeComparisonStyle INT ,
CompatibilityLevel TINYINT ,
SoftwareVendorId INT ,
SoftwareVersionMajor INT ,
SoftwareVersionMinor INT ,
SoftwareVersionBuild INT ,
MachineName NVARCHAR(128) ,
Flags INT ,
BindingId uniqueidentifier ,
RecoveryForkId uniqueidentifier ,
Collation NVARCHAR(128)
)
INSERT INTO #restore EXEC(@.cmd)
SELECT MAX(r.Position) AS [MaxFile]
FROM #restore r
WHERE r.BackupType = 1
AND r.DatabaseName = @.db
AND r.MachineName = @.@.SERVERNAME
GO
DROP TABLE #restore
--<--
MaxFile
1
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply