Resorting Columns

Here's something I've never run into before, and I hope someone can help.
I have two identical SQL2000 databases (DB1 and DB2). Each has a different
name and is part of a different merge scheme. My developer has an
application that writes data from a table in DB1 to an identical table in
DB2, but it's failing. He says that it's because the columns in one table
aren't in the same order as the columns in the other table. E.g., one table
has column19 in the 19th position, and the other table has column19 in the
15th position.
Is there a way to move a column from one position to another without
removing the databases from replication?
I'd really appreciate some help in this.
Thanks,
John Steen
Developers should never ever rely on the column order. Tell your developer
to stop being lazy and specify the columns in the insert statement. If he
doesn't he will introduce some very subtle bugs over time that will be nearly
impossible to track down. I made the same mistake at my first job while I
was still in college and still regret it.
|||Thank, Scott, I'll definitely pass that along to our developer.
Unfortunately, I don't think it's going to fly this time. We're trying to
piece together a quick solution for a short-term problem that will be going
away by the end of the month. He's not going to want to rewrite all his apps
just for that. If there's a way for me to change the position of the column
in the table so the app will work, I'd rather do that.
Thanks,
John
"Scott S." wrote:

> Developers should never ever rely on the column order. Tell your developer
> to stop being lazy and specify the columns in the insert statement. If he
> doesn't he will introduce some very subtle bugs over time that will be nearly
> impossible to track down. I made the same mistake at my first job while I
> was still in college and still regret it.

Resore .bak to SQL Express

Our vendor has provided us a copy of our data in a .bak files from SQL server. I want to run reports against this data. I have installed Express as well as Server management studio.

I presume I need to restore the .BAK file before I can run any reports? If so how does one do this?

Thanks in advance

In the UI right click on a database and select restore, or review the restore command in Books On Line;

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_25rm.asp

Also here is a short example;

RESTORE DATABASE MyNwind
FROM MyNwind_1

Resolving function USER

If I log in to Query Analyzer using Windows Auth and run "PRINT USER", it returns "dbo". Is there any way to have the USER function actually return <Domain>\<login> for myself?Look up suser_name and suser_sname.

Resetting stats for Performance Dashboard

I have installed the reports for the performance dashboard and really like it!

However, I'd like to be able to clear the stats in order to run specific procedures etc and see the most inefficient parts in the specific procedure. The documentation says like this about it:

The lifetime of the DMV data depends on the lifetime of the plan in cache. You can determine how long the plan has been cached, and thus the time frame over which these resources have been consumed, by looking at the Plan Cached column

What Plan Cashed column? Can someone explain to me how to clear the stats?

Regards Andreas

take a look at 'dbcc freeproccache' in book online.

|||

Thank you!

Think it was exactly what I needed!

Regards Andreas

sql

Resetting DTS password

Hi all,
can anybody please how to reset passwords for an DTS? I need to edit and run
it...but the former DBA left without giving us the passwords...
Thanks,
TudorHi Tudor - I would change the owner of the package.
We run/own all our packages under a 'pretend' domain account reservd for
SQL - this is also the account that SQL and SQL agent run under.
Hope this helps.
"Tudor Sofron" <tsofron@.cluj.astral.rom> wrote in message
news:O0fjbohpEHA.868@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> can anybody please how to reset passwords for an DTS? I need to edit and
run
> it...but the former DBA left without giving us the passwords...
> Thanks,
> Tudor
>|||Doesn't help. I can't do that until i open the package...What I need is to
change the owner password of the dts...
Tudor
"Allen Davidson" <allen.davidson@.europe.mccann.com> wrote in message
news:eoaY3vhpEHA.1296@.TK2MSFTNGP12.phx.gbl...
> Hi Tudor - I would change the owner of the package.
> We run/own all our packages under a 'pretend' domain account reservd for
> SQL - this is also the account that SQL and SQL agent run under.
> Hope this helps.
> "Tudor Sofron" <tsofron@.cluj.astral.rom> wrote in message
> news:O0fjbohpEHA.868@.TK2MSFTNGP10.phx.gbl...
> > Hi all,
> > can anybody please how to reset passwords for an DTS? I need to edit and
> run
> > it...but the former DBA left without giving us the passwords...
> >
> > Thanks,
> > Tudor
> >
> >
>|||Hi Tudor - in this case I think you are stuck.
The pakage will be stored in encrypted form and without the owner password I
don't think you can do anything (anything at all usefull) with it.
Allen
"Tudor Sofron" <tsofron@.cluj.astral.rom> wrote in message
news:eG$U24hpEHA.3520@.TK2MSFTNGP11.phx.gbl...
> Doesn't help. I can't do that until i open the package...What I need is to
> change the owner password of the dts...
> Tudor
>
> "Allen Davidson" <allen.davidson@.europe.mccann.com> wrote in message
> news:eoaY3vhpEHA.1296@.TK2MSFTNGP12.phx.gbl...
> > Hi Tudor - I would change the owner of the package.
> >
> > We run/own all our packages under a 'pretend' domain account reservd for
> > SQL - this is also the account that SQL and SQL agent run under.
> >
> > Hope this helps.
> >
> > "Tudor Sofron" <tsofron@.cluj.astral.rom> wrote in message
> > news:O0fjbohpEHA.868@.TK2MSFTNGP10.phx.gbl...
> > > Hi all,
> > > can anybody please how to reset passwords for an DTS? I need to edit
and
> > run
> > > it...but the former DBA left without giving us the passwords...
> > >
> > > Thanks,
> > > Tudor
> > >
> > >
> >
> >
>

Resetting DB

> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
Below is a script that will drop all objects. If the database is not large
or you are using SQL 2005, you might find it easy to simply drop and
recreate the database.
IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
BEGIN
RAISERROR('Not for use on system databases', 16, 1)
GOTO Done
END
--Drop objects from current the database
SET NOCOUNT ON
DECLARE @.DropStatement nvarchar(4000)
DECLARE @.SequenceNumber int
DECLARE @.LastError int
DECLARE @.TablesDropped int
DECLARE DropStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
--views
SELECT
1 AS SequenceNumber,
N'DROP VIEW ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
UNION ALL
--procedures and functions
SELECT
2 AS SequenceNumber,
N'DROP PROCEDURE ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = N'FUNCTION' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0
UNION ALL
--foreign keys
SELECT
3 AS SequenceNumber,
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
N' DROP CONSTRAINT ' +
CONSTRAINT_NAME AS DropStatement
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = N'FOREIGN KEY'
UNION ALL
--tables
SELECT
4 AS SequenceNumber,
N'DROP TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'BASE TABLE' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
ORDER BY SequenceNumber
OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
--EXECUTE sp_ExecuteSQL @.DropStatement
SET @.LastError = @.@.ERROR
IF @.LastError > 0
BEGIN
RAISERROR('Script terminated due to unexpected error', 16, 1)
GOTO Done
END
END
END
CLOSE DropStatements
DEALLOCATE DropStatements
Done:
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>
Just a thought here, but another way, if you're doing this often, might be to
make a backup of the empty database and restore it as needed or to make a
backup of the database files while the database is empty and detach/attach as
needed - though that second option doesn't sound nearly as fast as just about
any of the others but it, like the first option here, would give you clean
log files and database files.
Dale
Dale Preston
MCAD C#
MCSE, MCDBA
"Dan Guzman" wrote:

> Below is a script that will drop all objects. If the database is not large
> or you are using SQL 2005, you might find it easy to simply drop and
> recreate the database.
>
> IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
> BEGIN
> RAISERROR('Not for use on system databases', 16, 1)
> GOTO Done
> END
> --Drop objects from current the database
> SET NOCOUNT ON
> DECLARE @.DropStatement nvarchar(4000)
> DECLARE @.SequenceNumber int
> DECLARE @.LastError int
> DECLARE @.TablesDropped int
> DECLARE DropStatements CURSOR
> LOCAL FAST_FORWARD READ_ONLY FOR
> --views
> SELECT
> 1 AS SequenceNumber,
> N'DROP VIEW ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'VIEW' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --procedures and functions
> SELECT
> 2 AS SequenceNumber,
> N'DROP PROCEDURE ' +
> QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.ROUTINES
> WHERE
> ROUTINE_TYPE = N'FUNCTION' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --foreign keys
> SELECT
> 3 AS SequenceNumber,
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) +
> N' DROP CONSTRAINT ' +
> CONSTRAINT_NAME AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> WHERE
> CONSTRAINT_TYPE = N'FOREIGN KEY'
> UNION ALL
> --tables
> SELECT
> 4 AS SequenceNumber,
> N'DROP TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'BASE TABLE' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> ORDER BY SequenceNumber
> OPEN DropStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> BEGIN
> RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
> --EXECUTE sp_ExecuteSQL @.DropStatement
> SET @.LastError = @.@.ERROR
> IF @.LastError > 0
> BEGIN
> RAISERROR('Script terminated due to unexpected error', 16, 1)
> GOTO Done
> END
> END
> END
> CLOSE DropStatements
> DEALLOCATE DropStatements
> Done:
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>
sql

Resetting DB

> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects
Is there some reason why you can't add DROP DATABASE <database_name> and
CREATE DATABASE <database_name> statements to your script?
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>
> Is there some reason why you can't add DROP DATABASE <database_name> and
> CREATE DATABASE <database_name> statements to your script?
I can't speak for John but I have had large SQL 2000 databases where I found
it considerably faster to drop all objects rather than dropping and
recreating the entire database. However, in the SQL 2005 world, the
database drop/create method is very fast thanks to instant file
initialization.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:%233GfsyheHHA.3960@.TK2MSFTNGP02.phx.gbl...
> Is there some reason why you can't add DROP DATABASE <database_name> and
> CREATE DATABASE <database_name> statements to your script?
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>
|||> I can't speak for John but I have had large SQL 2000 databases where I
> found it considerably faster to drop all objects rather than dropping and
> recreating the entire database. However, in the SQL 2005 world, the
> database drop/create method is very fast thanks to instant file
> initialization.
Good point. I wasn't thinking along those lines. Thanks for including this
information.
Gail
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:74A9F97B-3095-471B-A3C7-BF6EE085B1D6@.microsoft.com...
> I can't speak for John but I have had large SQL 2000 databases where I
> found it considerably faster to drop all objects rather than dropping and
> recreating the entire database. However, in the SQL 2005 world, the
> database drop/create method is very fast thanks to instant file
> initialization.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:%233GfsyheHHA.3960@.TK2MSFTNGP02.phx.gbl...
>

Resetting DB

Hi
Is there an easy way to drop all object of a db so it is as new? Just so I
can run a script to recreate the objects.
Thanks
RegardsHello,
In the Generate SQL Script, Script all objects with drop objects. While
executing the script on the database will drop and recreate all objects.
FYI, This will clear off all
the data, so if you need data please backup the database.
Thanks
Hari
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>|||> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects
Is there some reason why you can't add DROP DATABASE <database_name> and
CREATE DATABASE <database_name> statements to your script?
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>|||> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
Below is a script that will drop all objects. If the database is not large
or you are using SQL 2005, you might find it easy to simply drop and
recreate the database.
IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
BEGIN
RAISERROR('Not for use on system databases', 16, 1)
GOTO Done
END
--Drop objects from current the database
SET NOCOUNT ON
DECLARE @.DropStatement nvarchar(4000)
DECLARE @.SequenceNumber int
DECLARE @.LastError int
DECLARE @.TablesDropped int
DECLARE DropStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
--views
SELECT
1 AS SequenceNumber,
N'DROP VIEW ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
UNION ALL
--procedures and functions
SELECT
2 AS SequenceNumber,
N'DROP PROCEDURE ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = N'FUNCTION' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0
UNION ALL
--foreign keys
SELECT
3 AS SequenceNumber,
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
N' DROP CONSTRAINT ' +
CONSTRAINT_NAME AS DropStatement
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = N'FOREIGN KEY'
UNION ALL
--tables
SELECT
4 AS SequenceNumber,
N'DROP TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'BASE TABLE' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
ORDER BY SequenceNumber
OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
--EXECUTE sp_ExecuteSQL @.DropStatement
SET @.LastError = @.@.ERROR
IF @.LastError > 0
BEGIN
RAISERROR('Script terminated due to unexpected error', 16, 1)
GOTO Done
END
END
END
CLOSE DropStatements
DEALLOCATE DropStatements
Done:
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>|||> Is there some reason why you can't add DROP DATABASE <database_name> and
> CREATE DATABASE <database_name> statements to your script?
I can't speak for John but I have had large SQL 2000 databases where I found
it considerably faster to drop all objects rather than dropping and
recreating the entire database. However, in the SQL 2005 world, the
database drop/create method is very fast thanks to instant file
initialization.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:%233GfsyheHHA.3960@.TK2MSFTNGP02.phx.gbl...
>> Is there an easy way to drop all object of a db so it is as new? Just so
>> I can run a script to recreate the objects
> Is there some reason why you can't add DROP DATABASE <database_name> and
> CREATE DATABASE <database_name> statements to your script?
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> Is there an easy way to drop all object of a db so it is as new? Just so
>> I can run a script to recreate the objects.
>> Thanks
>> Regards
>>
>|||> I can't speak for John but I have had large SQL 2000 databases where I
> found it considerably faster to drop all objects rather than dropping and
> recreating the entire database. However, in the SQL 2005 world, the
> database drop/create method is very fast thanks to instant file
> initialization.
Good point. I wasn't thinking along those lines. Thanks for including this
information.
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:74A9F97B-3095-471B-A3C7-BF6EE085B1D6@.microsoft.com...
>> Is there some reason why you can't add DROP DATABASE <database_name> and
>> CREATE DATABASE <database_name> statements to your script?
> I can't speak for John but I have had large SQL 2000 databases where I
> found it considerably faster to drop all objects rather than dropping and
> recreating the entire database. However, in the SQL 2005 world, the
> database drop/create method is very fast thanks to instant file
> initialization.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:%233GfsyheHHA.3960@.TK2MSFTNGP02.phx.gbl...
>> Is there an easy way to drop all object of a db so it is as new? Just so
>> I can run a script to recreate the objects
>> Is there some reason why you can't add DROP DATABASE <database_name> and
>> CREATE DATABASE <database_name> statements to your script?
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> Download the latest version of Books Online from
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> "John" <John@.nospam.infovis.co.uk> wrote in message
>> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> Is there an easy way to drop all object of a db so it is as new? Just so
>> I can run a script to recreate the objects.
>> Thanks
>> Regards
>>
>>
>|||Just a thought here, but another way, if you're doing this often, might be to
make a backup of the empty database and restore it as needed or to make a
backup of the database files while the database is empty and detach/attach as
needed - though that second option doesn't sound nearly as fast as just about
any of the others but it, like the first option here, would give you clean
log files and database files.
Dale
--
Dale Preston
MCAD C#
MCSE, MCDBA
"Dan Guzman" wrote:
> > Is there an easy way to drop all object of a db so it is as new? Just so I
> > can run a script to recreate the objects.
> Below is a script that will drop all objects. If the database is not large
> or you are using SQL 2005, you might find it easy to simply drop and
> recreate the database.
>
> IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
> BEGIN
> RAISERROR('Not for use on system databases', 16, 1)
> GOTO Done
> END
> --Drop objects from current the database
> SET NOCOUNT ON
> DECLARE @.DropStatement nvarchar(4000)
> DECLARE @.SequenceNumber int
> DECLARE @.LastError int
> DECLARE @.TablesDropped int
> DECLARE DropStatements CURSOR
> LOCAL FAST_FORWARD READ_ONLY FOR
> --views
> SELECT
> 1 AS SequenceNumber,
> N'DROP VIEW ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'VIEW' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --procedures and functions
> SELECT
> 2 AS SequenceNumber,
> N'DROP PROCEDURE ' +
> QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.ROUTINES
> WHERE
> ROUTINE_TYPE = N'FUNCTION' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --foreign keys
> SELECT
> 3 AS SequenceNumber,
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) +
> N' DROP CONSTRAINT ' +
> CONSTRAINT_NAME AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> WHERE
> CONSTRAINT_TYPE = N'FOREIGN KEY'
> UNION ALL
> --tables
> SELECT
> 4 AS SequenceNumber,
> N'DROP TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'BASE TABLE' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> ORDER BY SequenceNumber
> OPEN DropStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> BEGIN
> RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
> --EXECUTE sp_ExecuteSQL @.DropStatement
> SET @.LastError = @.@.ERROR
> IF @.LastError > 0
> BEGIN
> RAISERROR('Script terminated due to unexpected error', 16, 1)
> GOTO Done
> END
> END
> END
> CLOSE DropStatements
> DEALLOCATE DropStatements
> Done:
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> > Hi
> >
> > Is there an easy way to drop all object of a db so it is as new? Just so I
> > can run a script to recreate the objects.
> >
> > Thanks
> >
> > Regards
> >
> >
>

Resetting DB

> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
Below is a script that will drop all objects. If the database is not large
or you are using SQL 2005, you might find it easy to simply drop and
recreate the database.
IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
BEGIN
RAISERROR('Not for use on system databases', 16, 1)
GOTO Done
END
--Drop objects from current the database
SET NOCOUNT ON
DECLARE @.DropStatement nvarchar(4000)
DECLARE @.SequenceNumber int
DECLARE @.LastError int
DECLARE @.TablesDropped int
DECLARE DropStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
--views
SELECT
1 AS SequenceNumber,
N'DROP VIEW ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
UNION ALL
--procedures and functions
SELECT
2 AS SequenceNumber,
N'DROP PROCEDURE ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = N'FUNCTION' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0
UNION ALL
--foreign keys
SELECT
3 AS SequenceNumber,
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
N' DROP CONSTRAINT ' +
CONSTRAINT_NAME AS DropStatement
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = N'FOREIGN KEY'
UNION ALL
--tables
SELECT
4 AS SequenceNumber,
N'DROP TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'BASE TABLE' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
ORDER BY SequenceNumber
OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
--EXECUTE sp_ExecuteSQL @.DropStatement
SET @.LastError = @.@.ERROR
IF @.LastError > 0
BEGIN
RAISERROR('Script terminated due to unexpected error', 16, 1)
GOTO Done
END
END
END
CLOSE DropStatements
DEALLOCATE DropStatements
Done:
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>
Just a thought here, but another way, if you're doing this often, might be to
make a backup of the empty database and restore it as needed or to make a
backup of the database files while the database is empty and detach/attach as
needed - though that second option doesn't sound nearly as fast as just about
any of the others but it, like the first option here, would give you clean
log files and database files.
Dale
Dale Preston
MCAD C#
MCSE, MCDBA
"Dan Guzman" wrote:

> Below is a script that will drop all objects. If the database is not large
> or you are using SQL 2005, you might find it easy to simply drop and
> recreate the database.
>
> IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
> BEGIN
> RAISERROR('Not for use on system databases', 16, 1)
> GOTO Done
> END
> --Drop objects from current the database
> SET NOCOUNT ON
> DECLARE @.DropStatement nvarchar(4000)
> DECLARE @.SequenceNumber int
> DECLARE @.LastError int
> DECLARE @.TablesDropped int
> DECLARE DropStatements CURSOR
> LOCAL FAST_FORWARD READ_ONLY FOR
> --views
> SELECT
> 1 AS SequenceNumber,
> N'DROP VIEW ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'VIEW' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --procedures and functions
> SELECT
> 2 AS SequenceNumber,
> N'DROP PROCEDURE ' +
> QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.ROUTINES
> WHERE
> ROUTINE_TYPE = N'FUNCTION' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --foreign keys
> SELECT
> 3 AS SequenceNumber,
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) +
> N' DROP CONSTRAINT ' +
> CONSTRAINT_NAME AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> WHERE
> CONSTRAINT_TYPE = N'FOREIGN KEY'
> UNION ALL
> --tables
> SELECT
> 4 AS SequenceNumber,
> N'DROP TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'BASE TABLE' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> ORDER BY SequenceNumber
> OPEN DropStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> BEGIN
> RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
> --EXECUTE sp_ExecuteSQL @.DropStatement
> SET @.LastError = @.@.ERROR
> IF @.LastError > 0
> BEGIN
> RAISERROR('Script terminated due to unexpected error', 16, 1)
> GOTO Done
> END
> END
> END
> CLOSE DropStatements
> DEALLOCATE DropStatements
> Done:
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>

Resetting DB

> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects
Is there some reason why you can't add DROP DATABASE <database_name> and
CREATE DATABASE <database_name> statements to your script?
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>
> Is there some reason why you can't add DROP DATABASE <database_name> and
> CREATE DATABASE <database_name> statements to your script?
I can't speak for John but I have had large SQL 2000 databases where I found
it considerably faster to drop all objects rather than dropping and
recreating the entire database. However, in the SQL 2005 world, the
database drop/create method is very fast thanks to instant file
initialization.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:%233GfsyheHHA.3960@.TK2MSFTNGP02.phx.gbl...
> Is there some reason why you can't add DROP DATABASE <database_name> and
> CREATE DATABASE <database_name> statements to your script?
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>
|||> I can't speak for John but I have had large SQL 2000 databases where I
> found it considerably faster to drop all objects rather than dropping and
> recreating the entire database. However, in the SQL 2005 world, the
> database drop/create method is very fast thanks to instant file
> initialization.
Good point. I wasn't thinking along those lines. Thanks for including this
information.
Gail
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:74A9F97B-3095-471B-A3C7-BF6EE085B1D6@.microsoft.com...
> I can't speak for John but I have had large SQL 2000 databases where I
> found it considerably faster to drop all objects rather than dropping and
> recreating the entire database. However, in the SQL 2005 world, the
> database drop/create method is very fast thanks to instant file
> initialization.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:%233GfsyheHHA.3960@.TK2MSFTNGP02.phx.gbl...
>

Resetting DB

Hi
Is there an easy way to drop all object of a db so it is as new? Just so I
can run a script to recreate the objects.
Thanks
RegardsHello,
In the Generate SQL Script, Script all objects with drop objects. While
executing the script on the database will drop and recreate all objects.
FYI, This will clear off all
the data, so if you need data please backup the database.
Thanks
Hari
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>|||> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects
Is there some reason why you can't add DROP DATABASE <database_name> and
CREATE DATABASE <database_name> statements to your script?
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>|||> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
Below is a script that will drop all objects. If the database is not large
or you are using SQL 2005, you might find it easy to simply drop and
recreate the database.
IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
BEGIN
RAISERROR('Not for use on system databases', 16, 1)
GOTO Done
END
--Drop objects from current the database
SET NOCOUNT ON
DECLARE @.DropStatement nvarchar(4000)
DECLARE @.SequenceNumber int
DECLARE @.LastError int
DECLARE @.TablesDropped int
DECLARE DropStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
--views
SELECT
1 AS SequenceNumber,
N'DROP VIEW ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
UNION ALL
--procedures and functions
SELECT
2 AS SequenceNumber,
N'DROP PROCEDURE ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = N'FUNCTION' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0
UNION ALL
--foreign keys
SELECT
3 AS SequenceNumber,
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
N' DROP CONSTRAINT ' +
CONSTRAINT_NAME AS DropStatement
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = N'FOREIGN KEY'
UNION ALL
--tables
SELECT
4 AS SequenceNumber,
N'DROP TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'BASE TABLE' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
ORDER BY SequenceNumber
OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
--EXECUTE sp_ExecuteSQL @.DropStatement
SET @.LastError = @.@.ERROR
IF @.LastError > 0
BEGIN
RAISERROR('Script terminated due to unexpected error', 16, 1)
GOTO Done
END
END
END
CLOSE DropStatements
DEALLOCATE DropStatements
Done:
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>|||> Is there some reason why you can't add DROP DATABASE <database_name> and
> CREATE DATABASE <database_name> statements to your script?
I can't speak for John but I have had large SQL 2000 databases where I found
it considerably faster to drop all objects rather than dropping and
recreating the entire database. However, in the SQL 2005 world, the
database drop/create method is very fast thanks to instant file
initialization.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:%233GfsyheHHA.3960@.TK2MSFTNGP02.phx.gbl...
> Is there some reason why you can't add DROP DATABASE <database_name> and
> CREATE DATABASE <database_name> statements to your script?
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pr...oads/books.mspx
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>|||> I can't speak for John but I have had large SQL 2000 databases where I
> found it considerably faster to drop all objects rather than dropping and
> recreating the entire database. However, in the SQL 2005 world, the
> database drop/create method is very fast thanks to instant file
> initialization.
Good point. I wasn't thinking along those lines. Thanks for including this
information.
Gail
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:74A9F97B-3095-471B-A3C7-BF6EE085B1D6@.microsoft.com...
> I can't speak for John but I have had large SQL 2000 databases where I
> found it considerably faster to drop all objects rather than dropping and
> recreating the entire database. However, in the SQL 2005 world, the
> database drop/create method is very fast thanks to instant file
> initialization.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:%233GfsyheHHA.3960@.TK2MSFTNGP02.phx.gbl...
>|||Just a thought here, but another way, if you're doing this often, might be t
o
make a backup of the empty database and restore it as needed or to make a
backup of the database files while the database is empty and detach/attach a
s
needed - though that second option doesn't sound nearly as fast as just abou
t
any of the others but it, like the first option here, would give you clean
log files and database files.
Dale
--
Dale Preston
MCAD C#
MCSE, MCDBA
"Dan Guzman" wrote:

> Below is a script that will drop all objects. If the database is not larg
e
> or you are using SQL 2005, you might find it easy to simply drop and
> recreate the database.
>
> IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
> BEGIN
> RAISERROR('Not for use on system databases', 16, 1)
> GOTO Done
> END
> --Drop objects from current the database
> SET NOCOUNT ON
> DECLARE @.DropStatement nvarchar(4000)
> DECLARE @.SequenceNumber int
> DECLARE @.LastError int
> DECLARE @.TablesDropped int
> DECLARE DropStatements CURSOR
> LOCAL FAST_FORWARD READ_ONLY FOR
> --views
> SELECT
> 1 AS SequenceNumber,
> N'DROP VIEW ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'VIEW' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --procedures and functions
> SELECT
> 2 AS SequenceNumber,
> N'DROP PROCEDURE ' +
> QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.ROUTINES
> WHERE
> ROUTINE_TYPE = N'FUNCTION' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --foreign keys
> SELECT
> 3 AS SequenceNumber,
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) +
> N' DROP CONSTRAINT ' +
> CONSTRAINT_NAME AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> WHERE
> CONSTRAINT_TYPE = N'FOREIGN KEY'
> UNION ALL
> --tables
> SELECT
> 4 AS SequenceNumber,
> N'DROP TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'BASE TABLE' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> ORDER BY SequenceNumber
> OPEN DropStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> BEGIN
> RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
> --EXECUTE sp_ExecuteSQL @.DropStatement
> SET @.LastError = @.@.ERROR
> IF @.LastError > 0
> BEGIN
> RAISERROR('Script terminated due to unexpected error', 16, 1)
> GOTO Done
> END
> END
> END
> CLOSE DropStatements
> DEALLOCATE DropStatements
> Done:
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>sql

Resetting @@Fetch_Status

Hi:
if I run a loop like so...
While @.@.FETCH_STATUS = 0
BEGIN
..Some code
END
It will break out of loop when @.@.FETCH_STATUS = -1. The problem is if I try
to run it again, @.@.FETCH_STATUS will remain at -1 and loop in never entered.
I have to close connection to get it to reset. How do I force it to reset
so I can work with it repeatedly in Query Analyzer?
Thanks,
CharlieCharlie@.CBFC wrote:
> Hi:
> if I run a loop like so...
> While @.@.FETCH_STATUS = 0
> BEGIN
> ...Some code
> END
>
> It will break out of loop when @.@.FETCH_STATUS = -1. The problem is
> if I try to run it again, @.@.FETCH_STATUS will remain at -1 and loop
> in never entered. I have to close connection to get it to reset. How
> do I force it to reset so I can work with it repeatedly in Query
> Analyzer?
>
You need to perform a FETCH that does not result in a non-zero status.
Of course, some will say that you should stop using cursors ... ;-)
HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||On Mon, 14 Nov 2005 16:26:26 -0500, Charlie@.CBFC wrote:

>Hi:
>if I run a loop like so...
>While @.@.FETCH_STATUS = 0
> BEGIN
> ...Some code
> END
>
>It will break out of loop when @.@.FETCH_STATUS = -1. The problem is if I tr
y
>to run it again, @.@.FETCH_STATUS will remain at -1 and loop in never entered
.
>I have to close connection to get it to reset. How do I force it to reset
>so I can work with it repeatedly in Query Analyzer?
Hi Charlie,
The generic outline (in pseudo-code) for consuming a trigger is:
-- Declare and open the curor
-- Fetch first row
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Process data from one row
-- Fetch next row
END -- repeats until FETCH_STATUS <> 0
-- Close and deallocate the cursor
Of course, some will say that you should stop using cursors ... ;-)
(and I would agree!!!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> The generic outline (in pseudo-code) for consuming a trigger is:
Consuming what? Haven't your mummy told you not to be up that late
at night? Or least stop consuming? :-)

> -- Declare and open the curor
> -- Fetch first row
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- Process data from one row
> -- Fetch next row
> END -- repeats until FETCH_STATUS <> 0
> -- Close and deallocate the cursor
Nah, this is the way should it, in my opinion:
DECLARE cur INSENSITIVE CURSOR FOR
SELECT ...
SELECT @.err = @.@.error IF @.err <> 0 BEGIN DEALLOCATE cur RETURN END
OPEN cur
WHILE 1 = 1
BEGIN
FETCH cur INTO ...
IF @.@.fetch_status <> 0
BREAK
-- Do stuff
END
DEALLOCATE cur
The point is that with only one FETCH statement, and which is close
to the cursor declaration, you don't run the risk of adding one more
column to the cursor, updating the first FETCH, but forgetting the
second.
And the cursor should be INSENSITIVE to avoid the nasty pitfalls of
keyset-driven cursors (which I still have not understood). FAST_FORWARD
or STATIC are probably OK to, but INSENSITIVE is ANSI.

> Of course, some will say that you should stop using cursors ... ;-)
> (and I would agree!!!)
Me too!
Then again, cursors are perfectly acceptable in some situations. And if
you have decided that you will iterate - a cursor is the best choice.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm curious ... I use cursors all the time. Why would I want to stop using c
ursor? And
what would I use to replace the cursor functionality?
On Mon, 14 Nov 2005 23:40:08 +0000 (UTC), Erland Sommarskog <esquel@.sommarsk
og.se> wrote:

>Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
>Consuming what? Haven't your mummy told you not to be up that late
>at night? Or least stop consuming? :-)
>
>Nah, this is the way should it, in my opinion:
> DECLARE cur INSENSITIVE CURSOR FOR
> SELECT ...
> SELECT @.err = @.@.error IF @.err <> 0 BEGIN DEALLOCATE cur RETURN END
> OPEN cur
> WHILE 1 = 1
> BEGIN
> FETCH cur INTO ...
> IF @.@.fetch_status <> 0
> BREAK
> -- Do stuff
> END
> DEALLOCATE cur
>The point is that with only one FETCH statement, and which is close
>to the cursor declaration, you don't run the risk of adding one more
>column to the cursor, updating the first FETCH, but forgetting the
>second.
>And the cursor should be INSENSITIVE to avoid the nasty pitfalls of
>keyset-driven cursors (which I still have not understood). FAST_FORWARD
>or STATIC are probably OK to, but INSENSITIVE is ANSI.
>
>Me too!
>Then again, cursors are perfectly acceptable in some situations. And if
>you have decided that you will iterate - a cursor is the best choice.|||Gaetan <someone@.somewhere.com> wrote:

>I'm curious ... I use cursors all the time. Why would I want to stop using
cursor? And
>what would I use to replace the cursor functionality?
Cursors allow row-at-a-time processing. SQL Server is not written for
row-at-a-time processing, and so cursors are, in general, inefficient.
SQL works best when used relationally, everything-at-once rather than
row-at-a-time. Cursors are invaluable, but should be considered a
last resort.
Row-at-a-time cursors look familiar to programmers who are used to
conventional programming languages, and often cursors are used by such
programmers as the only apparent (to them) way to get things done.
They are used as a first approach, rather than a last resort - a
substitute for relational thinking.
Often we find that a problem which has been solved by a cursor could
have been solved relationally, without a cursor. It is not uncommon
for the relational approach to outperform the cursor significantly -
we can be talking about an order of magnitude.
All of which is a broad generalization, and of course there are
exceptions. But like many such generalizations, the exceptions are
far fewer than some would have us believe.
Roy|||I understand what you are saying but I fail to understand how else I could u
se a
relational processing to perform a row-at-a-time process. For example, in a
SP I need to
apply some business logic on rows of a table matching specific criterias and
return the
output;
Table XYZ -> CURSOR -> Business processing on each row -> insert into temp t
able -> select
* from temp table
How could this type of processing be performed without the use of a CURSOR?
If I could get
an order of magnitude performance gain, you bet that I will change a few sto
red procedures
right away.
Thanks.
On Mon, 14 Nov 2005 22:43:17 -0500, Roy Harvey <roy_harvey@.snet.net> wrote:

>Gaetan <someone@.somewhere.com> wrote:
>
>Cursors allow row-at-a-time processing. SQL Server is not written for
>row-at-a-time processing, and so cursors are, in general, inefficient.
>SQL works best when used relationally, everything-at-once rather than
>row-at-a-time. Cursors are invaluable, but should be considered a
>last resort.
>Row-at-a-time cursors look familiar to programmers who are used to
>conventional programming languages, and often cursors are used by such
>programmers as the only apparent (to them) way to get things done.
>They are used as a first approach, rather than a last resort - a
>substitute for relational thinking.
>Often we find that a problem which has been solved by a cursor could
>have been solved relationally, without a cursor. It is not uncommon
>for the relational approach to outperform the cursor significantly -
>we can be talking about an order of magnitude.
>All of which is a broad generalization, and of course there are
>exceptions. But like many such generalizations, the exceptions are
>far fewer than some would have us believe.
>Roy|||Well, what is the business processing you are attempting to do? Is it
not something that can be applied to all of the rows at once?
Stu|||Gaetan (someone@.somewhere.com) writes:
> I understand what you are saying but I fail to understand how else I
> could use a relational processing to perform a row-at-a-time process.
> For example, in a SP I need to apply some business logic on rows of a
> table matching specific criterias and return the output;
> Table XYZ -> CURSOR -> Business processing on each row -> insert into
> temp table -> select * from temp table
> How could this type of processing be performed without the use of a
> CURSOR? If I could get an order of magnitude performance gain, you bet
> that I will change a few stored procedures right away.
That depends on the business processing to apply. Admittedly, in some
cases you can only work with it one at time, but say that the business
processing is "increase the salary with 2% for all employees", there's
obviously no need for a cursor here. The rules for the salary increase
may be more complex, but could very well still be implementable in a
single UPDATE statement. But say that for each employee you need to
call some external program to get the salary increase, at this point you
may have to resort to a cursor.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Gaetan <someone@.somewhere.com> wrote:

>I understand what you are saying but I fail to understand how else I could
use a
>relational processing to perform a row-at-a-time process. For example, in a
SP I need to
>apply some business logic on rows of a table matching specific criterias an
d return the
>output;
>Table XYZ -> CURSOR -> Business processing on each row -> insert into temp
table -> select
>* from temp table
>How could this type of processing be performed without the use of a CURSOR?
If I could get
>an order of magnitude performance gain, you bet that I will change a few st
ored procedures
>right away.
>Thanks.
The question is whether "Business processing on each row" can be done
relationally. Perhaps it can't, but I would not make that judgement
without knowing a lot more about that business processing. If you
care to share some of the requirements someone might have some
thoughts to share on whether they can be handled relationally.
One clear-cut case where cursors were required comes to mind, where
transactions had to be applied in strict sequence.
Roy

Reseting Select Permissions for Public Role

Every night, there are some stored procedures that run to recreate tables so that the information in the table is updated. After the tables are droped and recreated I have to go in and check the select box under the permissions for the public role. If i don't do this users will not be able to select from theres tables.

What can I do so that users are able to select from these tables after they are created?

Would you be able to specify the select permissions for the public role in the script that creates the table or run a script that gives all those tables select permissions for the public role?

All help is appreciated.I'd use GRANT SELECT ON (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_8odw.asp) within the script.

-PatP|||That did the trick. Thanks for all the help.sql

Reset permissions

Hello,
I am looking for a script that will work dynamically depending on which
database I am in. I want to run the GRANT or DENY based upon a certain group
and I would prefer not to select each table the EM. Is there a script out
there that can read the tables and generate a permission script off of it?
Thanks in advance.
Jakesp_msForeachtable 'Grant select on ? to Public'
Change What u want on the Statement but ?
"jake" <rondican@.hotmail.com> wrote in message
news:OT%23e5$%239EHA.3260@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I am looking for a script that will work dynamically depending on
which
> database I am in. I want to run the GRANT or DENY based upon a certain
group
> and I would prefer not to select each table the EM. Is there a script out
> there that can read the tables and generate a permission script off of it?
> Thanks in advance.
> Jake
>|||Hi,
I do not recommend 'sp_msForeachtable' because it is undocumented. I
strongly urge all of you to think long and hard before embedding calls to
undocumented APIs in production code. Some of these undocumented APIs are
gone in Yukon.
I recommend building some simple code generators like the one shown below.
FYI, in Yukon you can GRANT/DENY/REVOKE permissions at different scopes. So
you can do this:
-- GRANT EXECUTE on all current and future procs and scalar funcs
-- in schema
--
GRANT EXECUTE ON SCHEMA :: someschema TO someuser
-- GRANT EXECUTE on all current and future procs and scalar funcs
-- in all schemas in the current database
--
GRANT EXECUTE TO someuser
Regards,
Clifford Dibble
Program Manager
SQL Server Engine
create function make_sql(@.sqltemplate nvarchar(2000)
, @.gdr nvarchar(6)
, @.perm nvarchar(128)
, @.object nvarchar(128)
, @.user nvarchar(128)
)
returns nvarchar(4000) as
begin
declare @.sql nvarchar(4000)
select @.sql = replace(replace(replace(replace(@.sqltemp
late, '<gdr>',
@.gdr), '<perm>', @.perm), '<object>', @.object), '<user>', @.user)
return @.sql
end
go
select dbo.make_sql('<gdr> <perm> ON <object> TO <user>', 'GRANT', 'SELECT',
o.name, 'PUBLIC')
from sysobjects as o
where o.type = 'U'
go
select dbo.make_sql('<gdr> <perm> ON <object> TO <user>', 'REVOKE',
'INSERT', o.name, 'PUBLIC')
from sysobjects as o
where o.type = 'U'
go
"Melih SARICA" wrote:

> sp_msForeachtable 'Grant select on ? to Public'
> Change What u want on the Statement but ?
> "jake" <rondican@.hotmail.com> wrote in message
> news:OT%23e5$%239EHA.3260@.TK2MSFTNGP14.phx.gbl...
> which
> group
>sql

Reserved words

Is there a query that i can run to find reserved words in the tables present
in my database?
Thanks!HP wrote on Tue, 3 Jan 2006 09:21:03 -0800:

> Is there a query that i can run to find reserved words in the tables
> present in my database?
> Thanks!
Much easier to just make sure you put [] around all table and column names
in your views/stored procs/queries, that way you don't risk problems in the
future when you migrate to the next SQL Server version and find they've
added more reserved words.
Dan|||HP (HP@.discussions.microsoft.com) writes:
> Is there a query that i can run to find reserved words in the tables
> present in my database?
There is a page in Books Online with all the reserved keywords, and
two more lists of ODBC keywords and possible keywords for the future.
With some cutting and pasting, and then finding and replacing in a
text editor, you can get those words into a table.
Then you can run this query:
SELECT object_name(c.id), c.name
FROM syscolumns c
WHERE EXISTS (SELECT *
FROM keywords k
WHERE c.name = k.keyword)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

rerun subscriptions

Is there any way to rerun all of the subscriptions at once after they
have been run for the day. I had about 35 subscriptions that were set to
run and 8 AM. We had a burp and they failed. I dont want to wai till
tomorrow for them to try again. And would really rather not run them
individually. So is there a way to get them to rerun all at once. Thanks
so much in advance.start them up manually. Drill down in replication monitor into the agents
folder to do this.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:ugyJBnryGHA.996@.TK2MSFTNGP03.phx.gbl...
> Is there any way to rerun all of the subscriptions at once after they
> have been run for the day. I had about 35 subscriptions that were set to
> run and 8 AM. We had a burp and they failed. I dont want to wai till
> tomorrow for them to try again. And would really rather not run them
> individually. So is there a way to get them to rerun all at once. Thanks
> so much in advance.
>|||I dont understand, where is all of that? I need baby steps. LOL
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uEOiqYyyGHA.4408@.TK2MSFTNGP05.phx.gbl...
> start them up manually. Drill down in replication monitor into the agents
> folder to do this.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
> news:ugyJBnryGHA.996@.TK2MSFTNGP03.phx.gbl...
>

rerun subscriptions

Is there any way to rerun all of the subscriptions at once after they
have been run for the day. I had about 35 subscriptions that were set to
run and 8 AM. We had a burp and they failed. I dont want to wai till
tomorrow for them to try again. And would really rather not run them
individually. So is there a way to get them to rerun all at once. Thanks
so much in advance.start them up manually. Drill down in replication monitor into the agents
folder to do this.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:ugyJBnryGHA.996@.TK2MSFTNGP03.phx.gbl...
> Is there any way to rerun all of the subscriptions at once after they
> have been run for the day. I had about 35 subscriptions that were set to
> run and 8 AM. We had a burp and they failed. I dont want to wai till
> tomorrow for them to try again. And would really rather not run them
> individually. So is there a way to get them to rerun all at once. Thanks
> so much in advance.
>|||I dont understand, where is all of that? I need baby steps. LOL
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uEOiqYyyGHA.4408@.TK2MSFTNGP05.phx.gbl...
> start them up manually. Drill down in replication monitor into the agents
> folder to do this.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
> news:ugyJBnryGHA.996@.TK2MSFTNGP03.phx.gbl...
>> Is there any way to rerun all of the subscriptions at once after they
>> have been run for the day. I had about 35 subscriptions that were set to
>> run and 8 AM. We had a burp and they failed. I dont want to wai till
>> tomorrow for them to try again. And would really rather not run them
>> individually. So is there a way to get them to rerun all at once.
>> Thanks so much in advance.
>

re-run snapshot agent for each new subscription?

If one day SubscriberA gets a push subscription, run snapshot agent, and
SubscriberA goes from pending to active one the snapshot agent is done. The
next day SubscriberB gets a push subscription from the same publication. It
doesn't seem to go from pending to active until I re-run the snapshot agent
(it has status of waiting for snapshot). Is this by design or am I doing
something wrong? Do I need to run the snapshot agent immediately everytime
I add a subscriber if I want the subscriber to be active immediately?
Thanks
Mike Jansen
Why is it that when I add a new transactional subscription I must re-run the
snapshot agent before the subscription becomes active, even if the snapshot
agent has been run recently? Do changes to the database "invalidate" the
snapshot or am I doing something wrong?
Thanks,
Mike
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:uk8qPdNVFHA.3184@.TK2MSFTNGP15.phx.gbl...
> If one day SubscriberA gets a push subscription, run snapshot agent, and
> SubscriberA goes from pending to active one the snapshot agent is done.
The
> next day SubscriberB gets a push subscription from the same publication.
It
> doesn't seem to go from pending to active until I re-run the snapshot
agent
> (it has status of waiting for snapshot). Is this by design or am I doing
> something wrong? Do I need to run the snapshot agent immediately
everytime
> I add a subscriber if I want the subscriber to be active immediately?
> Thanks
> Mike Jansen
>
|||Mike,
the snapshot files will be deleted by the distribution cleanup agent once
all the subscribers have read them. If you want this not to occur so
frequently, you can schedule the distribution cleanup agent to run less
frequently eg at 12pm and have the snapshot agent run once the cleanup has
finished.
Rgds,
Paul Ibison
|||OK, that explains it. Thanks you very much.
Since you had such a good answer, I have another question
Is there a way to detect via T-SQL whether a valid snapshot exists?
Obviously I can't base it on job statuses because even if jobs complete, the
distribution cleanup will cleanup the snapshot. Is there another status
table I can check?
I'd rather check before running the snapshot agent _every_ time a new
subscription is created (the whole process is done in T-SQL scripts).
Thanks again,
Mike
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e0To7bXVFHA.3272@.TK2MSFTNGP10.phx.gbl...
> Mike,
> the snapshot files will be deleted by the distribution cleanup agent once
> all the subscribers have read them. If you want this not to occur so
> frequently, you can schedule the distribution cleanup agent to run less
> frequently eg at 12pm and have the snapshot agent run once the cleanup has
> finished.
> Rgds,
> Paul Ibison
>
|||Mike,
take a look at sp_browsesnapshotfolder (sp_browsesnapshotfolder
'testrepxxx'). It'll return a row if there is a publication snapshot
available. If it's been cleaned up there will be an empty recordset.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Excellent! I'll hopefully be testing/incorporating this at some point this
week. That was the last piece (for now!) in the replication part of my
database script framework. Thanks again.
Mike
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ORAfK0hVFHA.3540@.TK2MSFTNGP15.phx.gbl...
> Mike,
> take a look at sp_browsesnapshotfolder (sp_browsesnapshotfolder
> 'testrepxxx'). It'll return a row if there is a publication snapshot
> available. If it's been cleaned up there will be an empty recordset.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Requirements for non-administrative service account for clustered SQL 2000

I understand from Microsoft that a SQL Server 2000 Cluster on Windows
2003 Enterprise can be run with a non-administrative service account
but I have not been able to get this to work- the SQL installation
fails with "This operation returned because the timeout period expired"
and, while sqlstp.log and sqlstpN.log files look OK, the following
appears in sqlclstr.log:
CreateClusterVirtualSQLSvrGroup2: 1381
[sqlclusterSetup.cpp:2549] : 1460 (0x5b4): This operation returned
because the timeout period expired.
Watching the cluster during the install indicates that it never brings
the SQL Cluster IP address online.
Running the installation in exactly the same way but with the SQL
Server service account in the local administrators group on both nodes
works fine.
My hunch is that this has something to do with registering IP, DNS or
SPN resources in Active Directory but I have found nothing specific in
Technet or in the news groups to back this up.
Has anybody got this to work and can offer me any advice?
Thanks.
I have seen similar error a long time ago. When you get the error, do not press OK. Open SQL Server Errorlog and see the messages in it. That may be helpful.
Also, make sure that you do not have any networking/name resolution issues.
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Thanks for your comments but I eventually cracked it with the help of
this article:
http://www.sqlservercentral.com/colu...intversion.asp
I simply followed the steps in here for assigning file and registry
permissions on both nodes and removed the service account from the
local Adminstrators group when I'd finished. Works a treat.
Note that the service account needs read permissions to the whole of
the drive with the master and TempDB devices not just the folder they
are in and full control on the folders containing the data devices.

Required Security for Report Viewer on Windows 2003 SP1 Web Server

When trying to run a local report using the RS Report Viewer for VS
2005 we get the below exception when the User Account for the
Application Pool we are using is not in the Administrators Group of the
web server.
Does anybody have any suggestions as to what specific security is
needed to use the Report Viewer?
Could this be because we built the application as a "Web Project"
instead of a "Web Site"?
Microsoft.Reporting.WebForms.LocalProcessingException: An error
occurred during local report processing. -->
Microsoft.Reporting.DefinitionInvalidException: The definition of the
report 'Main Report' is invalid. -->
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
An unexpected error occurred while compiling expressions. Native
compiler return value: '[BC2001] file 'C:\WINDOWS\TEMP\n_rkco4n.0.vb'
could not be found'. -- End of inner exception stack trace -- at
AMR.AdvertiserClientList.RefreshReport() in C:\Documents and
Settings\god\My Documents\Visual Studio
2005\Projects\AMR\AdvertiserClientList.aspx.vb:line 192 at
AMR.AdvertiserClientList.RUNREPORT_Click(Object sender, EventArgs e) in
C:\Documents and Settings\God\My Documents\Visual Studio
2005\Projects\AMR\AdvertiserClientList.aspx.vb:line 50 ::
InnerException: Microsoft.Reporting.DefinitionInvalidException: The
definition of the report 'Main Report' is invalid. -->
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
An unexpected error occurred while compiling expressions. Native
compiler return value: '[BC2001] file 'C:\WINDOWS\TEMP\n_rkco4n.0.vb'
could not be found'.This error is also logged in the system event viewer, however granting
access NETMAN in the DCOM manager only keeps the errors from showing
and does not fix the problem. The CLSID refers to tthe Network
Connection Manager Class (under the NETMAN application).
The application-specific permission settings do not grant Local
Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user AMI\srvAcctAppAccessDEV SID
(S-1-5-21-2975352776-793034493-3225607600-10632). This security
permission can be modified using the Component Services administrative
tool.
tfelber@.gmail.com wrote:
> When trying to run a local report using the RS Report Viewer for VS
> 2005 we get the below exception when the User Account for the
> Application Pool we are using is not in the Administrators Group of the
> web server.
> Does anybody have any suggestions as to what specific security is
> needed to use the Report Viewer?
> Could this be because we built the application as a "Web Project"
> instead of a "Web Site"?
> Microsoft.Reporting.WebForms.LocalProcessingException: An error
> occurred during local report processing. -->
> Microsoft.Reporting.DefinitionInvalidException: The definition of the
> report 'Main Report' is invalid. -->
> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> An unexpected error occurred while compiling expressions. Native
> compiler return value: '[BC2001] file 'C:\WINDOWS\TEMP\n_rkco4n.0.vb'
> could not be found'. -- End of inner exception stack trace -- at
> AMR.AdvertiserClientList.RefreshReport() in C:\Documents and
> Settings\god\My Documents\Visual Studio
> 2005\Projects\AMR\AdvertiserClientList.aspx.vb:line 192 at
> AMR.AdvertiserClientList.RUNREPORT_Click(Object sender, EventArgs e) in
> C:\Documents and Settings\God\My Documents\Visual Studio
> 2005\Projects\AMR\AdvertiserClientList.aspx.vb:line 50 ::
> InnerException: Microsoft.Reporting.DefinitionInvalidException: The
> definition of the report 'Main Report' is invalid. -->
> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> An unexpected error occurred while compiling expressions. Native
> compiler return value: '[BC2001] file 'C:\WINDOWS\TEMP\n_rkco4n.0.vb'
> could not be found'.|||Finally figured it out.
The configurable identity that our application pool was using under
IIS 6.0 needed read/write access to the c:\Windows\Temp directory.
(Explains why we could get this to work when the account was part of
the Administrators or Power Users Group). I suppose this is to give it
temp space when rendering large reports.
Hopefully this helps somebody else out one day.
tfelber@.gmail.com wrote:
> This error is also logged in the system event viewer, however granting
> access NETMAN in the DCOM manager only keeps the errors from showing
> and does not fix the problem. The CLSID refers to tthe Network
> Connection Manager Class (under the NETMAN application).
> The application-specific permission settings do not grant Local
> Activation permission for the COM Server application with CLSID
> {BA126AD1-2166-11D1-B1D0-00805FC1270E}
> to the user AMI\srvAcctAppAccessDEV SID
> (S-1-5-21-2975352776-793034493-3225607600-10632). This security
> permission can be modified using the Component Services administrative
> tool.
>
> tfelber@.gmail.com wrote:
> > When trying to run a local report using the RS Report Viewer for VS
> > 2005 we get the below exception when the User Account for the
> > Application Pool we are using is not in the Administrators Group of the
> > web server.
> >
> > Does anybody have any suggestions as to what specific security is
> > needed to use the Report Viewer?
> >
> > Could this be because we built the application as a "Web Project"
> > instead of a "Web Site"?
> >
> > Microsoft.Reporting.WebForms.LocalProcessingException: An error
> > occurred during local report processing. -->
> > Microsoft.Reporting.DefinitionInvalidException: The definition of the
> > report 'Main Report' is invalid. -->
> > Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> > An unexpected error occurred while compiling expressions. Native
> > compiler return value: '[BC2001] file 'C:\WINDOWS\TEMP\n_rkco4n.0.vb'
> > could not be found'. -- End of inner exception stack trace -- at
> > AMR.AdvertiserClientList.RefreshReport() in C:\Documents and
> > Settings\god\My Documents\Visual Studio
> > 2005\Projects\AMR\AdvertiserClientList.aspx.vb:line 192 at
> > AMR.AdvertiserClientList.RUNREPORT_Click(Object sender, EventArgs e) in
> > C:\Documents and Settings\God\My Documents\Visual Studio
> > 2005\Projects\AMR\AdvertiserClientList.aspx.vb:line 50 ::
> > InnerException: Microsoft.Reporting.DefinitionInvalidException: The
> > definition of the report 'Main Report' is invalid. -->
> > Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> > An unexpected error occurred while compiling expressions. Native
> > compiler return value: '[BC2001] file 'C:\WINDOWS\TEMP\n_rkco4n.0.vb'
> > could not be found'.