Dropping tempe tables

My query creates a new temp table every time a report is run. I need to check
if the table exists before I drop it and perform a select into. Since SQL
Server appends an ID (session ID?) to the temp table name, I am unable to
drop the table prior to running the query. Is there a better way to handle
this? Any help would be appreciated.Try this. make sure the temp table does not exist, and you can do it this way.
Drop your new table at the end if your query.
CREATE PROCEDURE [dbo].[Test] AS
Select top 5 * into #temp from Orders
Select * from #temp
Drop Table #temp
GO
"DrM" wrote:
> My query creates a new temp table every time a report is run. I need to check
> if the table exists before I drop it and perform a select into. Since SQL
> Server appends an ID (session ID?) to the temp table name, I am unable to
> drop the table prior to running the query. Is there a better way to handle
> this? Any help would be appreciated.

Dropping tables from a article

Just a quick question , when you have a article is their any way to drop a table from a article.
Don't know if it important but I'm using merge replication.Ok....what's an article?|||Originally posted by Brett Kaiser
Ok....what's an article?

Sorry I suppose that should have been described as how do I drop a article from a publication. When the article is a table, or is a table a group of articles. Not hundered percent on the Publishing Metaphor.

A article is part of a publication that is part use as part of replication.
Any suggestions?|||Sorry...haven't done replication, but looking up in bol, it looks like the correct terminology...

Did you check out BOL?

To delete an article

Note Deleting articles from publications that have subscriptions is not allowed. To delete an article, you must first delete all subscriptions to the publication.

At the Publisher, open SQL Server Enterprise Manager, expand a server group, expand the Replication folder, expand the Publications folder, right-click the publication, and then click Properties.

Click the Articles tab, select an article to delete, and then clear the check box next to the article to delete.
1988-2000 Microsoft Corporation. All Rights Reserved.|||I don't think there is. What I usually do is drop and recreate the article if I want to make a change.|||Originally posted by joejcheng
I don't think there is. What I usually do is drop and recreate the article if I want to make a change.

Brill, Just wanted to be sure. Thankssql

dropping table from publication

I have setup transactional replication and its going fine since some time in
testing. I was trying to remove one of the tables from publication, but it
always says that i have to drop subscription inorder to drop table from
publication.
If I drop subscription and recreate new subscription after the table is
dropped, I will be out of synch. with publisher database as publisher is
still on-line.
Is there any easy way to get arround this...
Regards,
Ravi
Hi Ravi,
Not sure if I fully understand your situation but you can definitely drop
the subscription for just the table that you want to drop using
sp_dropsubscription (See Books Online). The UI (SEM & SMSS) doesn't usually
allow you to perform such article-level operation however.
HTH
-Raymond
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:EED2FB1D-7481-4027-B418-55BD7A97322E@.microsoft.com...
>I have setup transactional replication and its going fine since some time
>in
> testing. I was trying to remove one of the tables from publication, but it
> always says that i have to drop subscription inorder to drop table from
> publication.
> If I drop subscription and recreate new subscription after the table is
> dropped, I will be out of synch. with publisher database as publisher is
> still on-line.
> Is there any easy way to get arround this...
> Regards,
> Ravi
>
|||Raymond thanks for the reply, all i ma trying to do is drop one table from
existing publication and subscription.
and if possible add another table to same publication and subscription...
Regards,
Ravi
"Raymond Mak [MSFT]" wrote:

> Hi Ravi,
> Not sure if I fully understand your situation but you can definitely drop
> the subscription for just the table that you want to drop using
> sp_dropsubscription (See Books Online). The UI (SEM & SMSS) doesn't usually
> allow you to perform such article-level operation however.
> HTH
> -Raymond
> "SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
> message news:EED2FB1D-7481-4027-B418-55BD7A97322E@.microsoft.com...
>
>
|||Ravi,
I used this to change a column on a replicated table. It is almost exactly
the script you'll need:
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
alter table tEmployees alter column Forename varchar(100) null
exec sp_addarticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.source_table = 'tEmployees'
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Ravi,
I keep forgetting that folks may be using Merge replication in which case it
is not possible (read: no supported way) to drop a single article without
reinitializing the entire subscription. But in case you are using
transactional\snapshot replication, I think here is what you need to do:
1) Call sp_dropsubscription to drop subscriptions on the article you want to
drop
2) Call sp_droparticle to drop the article you want to drop
3) Call sp_addarticle to add the new table to your publication
4) Call sp_addsubscrption to add subscription to your new table (this is not
necessary if syspublications.immediate_sync = 1)
5) Run snapshot agent to generate snapshot for the new article, run
distribution agent to apply it
You probably know about this already but dropping subscription on an article
doesn't remove data that has already been replicated to the subscriber. Of
course, you should be able to manually drop the table easily at the
subscriber if that is what you want.
-Raymond
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:CBE581C0-3897-4A40-A740-CB38D33D61C5@.microsoft.com...[vbcol=seagreen]
> Raymond thanks for the reply, all i ma trying to do is drop one table from
> existing publication and subscription.
> and if possible add another table to same publication and subscription...
> Regards,
> Ravi
> "Raymond Mak [MSFT]" wrote:

Dropping table Column in SQL server 6.5

I'm trying to drop a table column in SQL Server 6.5. I used the following
command and got error:
ALTER TABLE tablename
DROP COLUMN columnname
GO
It works in SQL Server 2000 version
Please I need help.
Thanks.
Ebon.
Hi,
You cant delete a column in sql 6.5
Only way is :-
1. put the data into a new table (select * into table_backup from
real_table)
2. script the table and dependant objetcs
3. change the table script with out the unwanted column
4. Insert into table from table_backup
5. create dependant objects , indexes..
Thanks
Hari
MCDBA
"Egbon" <vnjowusi@.gosps.com> wrote in message
news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
> I'm trying to drop a table column in SQL Server 6.5. I used the following
> command and got error:
> ALTER TABLE tablename
> DROP COLUMN columnname
> GO
> It works in SQL Server 2000 version
> Please I need help.
> Thanks.
> Ebon.
>
|||Many thanks! Hari.
Egbon.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OtjNXHFoEHA.2588@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hi,
> You cant delete a column in sql 6.5
> Only way is :-
> 1. put the data into a new table (select * into table_backup from
> real_table)
> 2. script the table and dependant objetcs
> 3. change the table script with out the unwanted column
> 4. Insert into table from table_backup
> 5. create dependant objects , indexes..
> Thanks
> Hari
> MCDBA
> "Egbon" <vnjowusi@.gosps.com> wrote in message
> news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
following
>

Dropping table Column in SQL server 6.5

I'm trying to drop a table column in SQL Server 6.5. I used the following
command and got error:
ALTER TABLE tablename
DROP COLUMN columnname
GO
It works in SQL Server 2000 version
Please I need help.
Thanks.
Ebon.Hi,
You cant delete a column in sql 6.5
Only way is :-
1. put the data into a new table (select * into table_backup from
real_table)
2. script the table and dependant objetcs
3. change the table script with out the unwanted column
4. Insert into table from table_backup
5. create dependant objects , indexes..
Thanks
Hari
MCDBA
"Egbon" <vnjowusi@.gosps.com> wrote in message
news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
> I'm trying to drop a table column in SQL Server 6.5. I used the following
> command and got error:
> ALTER TABLE tablename
> DROP COLUMN columnname
> GO
> It works in SQL Server 2000 version
> Please I need help.
> Thanks.
> Ebon.
>|||Many thanks! Hari.
Egbon.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OtjNXHFoEHA.2588@.TK2MSFTNGP12.phx.gbl...
> Hi,
> You cant delete a column in sql 6.5
> Only way is :-
> 1. put the data into a new table (select * into table_backup from
> real_table)
> 2. script the table and dependant objetcs
> 3. change the table script with out the unwanted column
> 4. Insert into table from table_backup
> 5. create dependant objects , indexes..
> Thanks
> Hari
> MCDBA
> "Egbon" <vnjowusi@.gosps.com> wrote in message
> news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
> > I'm trying to drop a table column in SQL Server 6.5. I used the
following
> > command and got error:
> >
> > ALTER TABLE tablename
> > DROP COLUMN columnname
> > GO
> >
> > It works in SQL Server 2000 version
> > Please I need help.
> >
> > Thanks.
> >
> > Ebon.
> >
> >
>

Dropping indexes before bulk insert

Hi all,

I have a huge table 170 Gb of size. On that table we have 10 indexes
of around 12 GB in size.

The application is designed such that it bulk inserts the file in to
sql server. But , often we are getting time outs and some latching
isssues ( as can be seen in activity monitor).

So, will this be a good idea of dropping those indexes and then
recreating them again for better performance.

1) Its SQL 2005 Standard Edition SP1

2) Databases are in SIMPLE Recovery mode.

3) Database is not OLTP.

Thanks.

//N

Hi Naj,

Do you see any blocking on the server while the bulk insert is running?

How do you mean latches issues ?

Could you please look for waittime and waitypes in the following view and let us know what is there.

select * from sys.dm_os_waiting_tasks where session_id > 50

Jag

|||

you should be better off by dropping indexes and after bulk insert creating them off line. indexes created offline have very small footprint on log and much faster then indexes created online. as a trade off your table will be offline for a while. how long it depends on clustered key as with large table clustered key plays very vital role when rebuilding indexes.

another point is to consider switching off AUTO_UPDATE_STATISTICS while inserting rows into your table. this will kill the performance while inserting data and updating staticstics at the same time. also there is a new option AUTO_UPDATE_STATISTICS_ASYNC which provide background statistics update. use with extra care as they can hinder performance significatly.

see article http://www.mssqltips.com/tip.asp?tip=1193

Dropping constraint on temporary table

I made a constraint on a temporary table in a stored procedure but now i can't delete it.

Here's what happened:
I ran this in a stored procedure

CREATE TABLE #TeFotograferen (RowID int not null identity(1,1) Primary Key,Stamboeknummer char(11) ,Geldigheidsdatum datetime, CONSTRAINT UniqueFields UNIQUE(Stamboeknummer,Geldigheidsdatum)

next time i ran the stored procedure it gave me
There is already an object named 'UniqueFields' in the database.

but since the temporary table is out of scope i cannot delete the constraint
I tried
delete from tempdb..sysobjects where name = 'UniqueFields'
and
declare @.name
set @.name=(SELECT name from sysobjects where id=(Select parent_obj from sysobjects where name='UniqueFields'))
drop table @.name

giving me
Ad hoc updates to system catalogs are not allowed.
or
Cannot drop the table '#TeFotograferen__________________________________ __________________________________________________ _________________000000000135', because it does not exist or you do not have permission.This kind of problem is symptomatic of multiple sub-problems. You need to reconsider how your application works to truly solve the underlying problem or problems.

To solve the specific issue that you see here, the simplest answer is to drop the temp table itself using something like:DROP TABLE #teFotograferen-PatP|||Pat

That's exactly what defines my problem
If i run
DROP TABLE #teFotograferen

i get
Cannot drop the table '#tefotograferen', because it does not exist or you do not have permission

because the table was a temporary table and there's no way to get back in the scope where it was defined.

If i recreate the table and then drop it the constraint still remains in my database.

create table #tefotograferen (rowid int,Stamboeknummer char(11), Geldigheidsdatum datetime)
alter table #tefotograferen drop constraint UniqueFields
drop table #tefotograferen
gives me
Constraint 'UniqueFields' does not belong to table '#tefotograferen'.
because it is not the same table

on the other hand

create table #tefotograferen (rowid int,Stamboeknummer char(11), Geldigheidsdatum datetime, CONSTRAINT UniqueFields UNIQUE(Stamboeknummer,Geldigheidsdatum))
alter table #tefotograferen drop constraint UniqueFields
drop table #tefotograferen
gives me
There is already an object named 'UniqueFields' in the database.

In other words UniqueFields constraint is parentless, and the only way to delete constraint is to alter non-existent parent-table

It is not a design problem in my application, i just put some garbage in that i can't get out|||This kind of problem is symptomatic of multiple sub-problems.That comment wasn't an accident.

One problem is that you are being bitten by concurrent executions of the code that produces your temp table, and possibly by connection pooling too.

You have multiple temp tables, from multiple spids (connections to your database) with a constant constraint name of UniqueFields that is causing subsequent executions of the CREATE TABLE to fail.

I'd be willing to wager that there are other issues too, but these are enough to keep us amused for the moment.

The solution to this problem is to:

a) Stop execution of all running spids (disconnect them) that have a #teFotographen table at the moment.
b) Create the constraint with a default name (which is unique for each execution).

This should get you far enough to find the next problem!

-PatP|||[smacks forehead]

why would you need contraints on a temp table?

[/smacks forehead]|||After a restart of the server the offending constraint was gone.

Brett: Now i know NOT TO USE constraints on temp tables because of these issues. Rather check the data you insert into the temp table before you insert it.

I thought adding a constraint to ensure uniqueness was a good idea, but it seems with temp tables you get these kinds of issues.

But to me this seems like something that should be fixed. The temp table itself isn't visible outside the scope of execution, the constraint on the other hand is... so if you forget to drop the temp table or drop the constraint at the end of your stored procedure the constraint remains in the database until all connections are closed not just those tspids that have a temp table with that name.

Thanks for the advice and input. :beer:

Dropping Clustered index associated with Primary Key.

Hi all,

I have a huge table with million of rows, which has ONE Clustered index associated with the PRIMARY KEY, and there are some NON_Clustered indexes.

So,now i decided that, i dont need any more indexes ( not even one) on that table, but i need to maintain primary key on that table.

(a) So, how can i accomplish this (i.e.) having primay key but not having indexes on the table.

Thanks.

From BOL.

"When you specify a PRIMARY KEY constraint for a table, the SQL Server 2005 Database Engine enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries. Therefore, the primary keys that are chosen must follow the rules for creating unique indexes."

so basically you can't have a primary key without any indexes. Although you can have PK with a non-clustered index.

I am curious what made you to go down this path to remove the indexes on this table. What are you trying to achieve here?

|||

Actually, there is a big file, which bulk inserts into that huge table,and that table is very rarely used for selects statements.

So, i was thinking , if we could remove the indexes before bulk inserting the data and then rebuild them ( as we build , every week), we can improve the performance of bulk inserting the data into that table.

If you/anyone have any other idea about dealing with this kind of scenario, plz let me know.

Your help is greatly appreciatied.

Thanks.

|||In this scenario, whether to drop the indexes and re-create them after bulk insert or bulk insert data without dropping indexes depends on the ratio of data in the table vs the data coming to the table. If there are already millions of rows, and lot more millions to come then I think its best to drop the indexes and recreate them. Also you can pick up some performance, if you can split the one big file into smaller files and importing them concurrently by specifying TABLOCK after dropping the indexes. Make sure 'select into/bulkcopy' option is set to true.
|||

Along with the other suggestions, also consider changing the recovery model of the database to BULK LOGGED or SIMPLE recovery.

HTH!

|||

Thanks sankar and rich for your suggestions.

Actually there are 9 Non-Clustered indexes and a Clustered index associated with the Primary key on that huge table.

I can drop the non-clustered indexes, But, the Clustered index is associated with Primary key. How can i drop the clustered index, by having primary key on the table.

If i drop the Primary key consraint , then clustered index will be removed. But inserting data on a table without primary key , can lead into data inconsistency ( i mean, some duplicates/null can come into the table).

Thanks for your help.

|||

As Sankar mentioned, if you want a primary key or indeed any constraint to enforce uniqueness you will have an index. This is not something you can change.

You are right in saying that having it protects the integrity of your data, so unless you are able to drop the primary key, insert the data, tidy up any dupes and then reapply the primary key you're going to have to live with the fact that you have an index.

Dropping and Recreating Tables

We have a job that runs every 30 minutes dropping and recreating a table with
updated information. Besides the fact that I am trying to get rid of this
needless process, the question I have is:
When this job is running and attempting to drop and recreate the table will
it not be able to complete if someone has a connection to the database and/or
is running a report off the table that SQL Server is attempting to drop and
recreate? If there is a lock on this table from someone using it, is there a
way I can stop this from happening?
Connections to the database itself will not matter however all locks
involving the table would need to be released before the DROP command
executes. In the event that someone is using the table, the drop command
will wait until the locks are released or your connection times out (which
ever comes first).
--Brian
(Please reply to the newsgroups only.)
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
> We have a job that runs every 30 minutes dropping and recreating a table
> with
> updated information. Besides the fact that I am trying to get rid of this
> needless process, the question I have is:
> When this job is running and attempting to drop and recreate the table
> will
> it not be able to complete if someone has a connection to the database
> and/or
> is running a report off the table that SQL Server is attempting to drop
> and
> recreate? If there is a lock on this table from someone using it, is there
> a
> way I can stop this from happening?
|||Thanks for the reply. That should help a lot. Another question to your reply
though. If the job is waiting for the locks to be released, will this cause
the database to become unresponsive through Enterprise Manager, and will this
slow database access (as a whole) through the website?
"Brian Lawton" wrote:

> Connections to the database itself will not matter however all locks
> involving the table would need to be released before the DROP command
> executes. In the event that someone is using the table, the drop command
> will wait until the locks are released or your connection times out (which
> ever comes first).
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
>
>
|||While the DROP command is waiting, unless its connection is holding locks on
other resources, then no, it will not impact any performance of EM until it
actually executes. While executing, it may impact performance depending on
the table size involved and the time it takes to complete its work. This is
especially true if the web site is waiting for the "refreshed" table to be
repopulated.
All that said, if you are issuing the DROP command through EM, then yes, EM
will become "unresponsive" while it waits for the command to complete.
Unfortunately EM operates synchronously so any operation you perform using
EM may cause it to pause while the operation completes.
--Brian
(Please reply to the newsgroups only.)
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:2F58BF53-683A-4E5B-A664-0F418DD019EF@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply. That should help a lot. Another question to your
> reply
> though. If the job is waiting for the locks to be released, will this
> cause
> the database to become unresponsive through Enterprise Manager, and will
> this
> slow database access (as a whole) through the website?
> "Brian Lawton" wrote:

Dropping and Recreating Tables

We have a job that runs every 30 minutes dropping and recreating a table wit
h
updated information. Besides the fact that I am trying to get rid of this
needless process, the question I have is:
When this job is running and attempting to drop and recreate the table will
it not be able to complete if someone has a connection to the database and/o
r
is running a report off the table that SQL Server is attempting to drop and
recreate? If there is a lock on this table from someone using it, is there a
way I can stop this from happening?Connections to the database itself will not matter however all locks
involving the table would need to be released before the DROP command
executes. In the event that someone is using the table, the drop command
will wait until the locks are released or your connection times out (which
ever comes first).
--Brian
(Please reply to the newsgroups only.)
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
> We have a job that runs every 30 minutes dropping and recreating a table
> with
> updated information. Besides the fact that I am trying to get rid of this
> needless process, the question I have is:
> When this job is running and attempting to drop and recreate the table
> will
> it not be able to complete if someone has a connection to the database
> and/or
> is running a report off the table that SQL Server is attempting to drop
> and
> recreate? If there is a lock on this table from someone using it, is there
> a
> way I can stop this from happening?|||Thanks for the reply. That should help a lot. Another question to your reply
though. If the job is waiting for the locks to be released, will this cause
the database to become unresponsive through Enterprise Manager, and will thi
s
slow database access (as a whole) through the website?
"Brian Lawton" wrote:

> Connections to the database itself will not matter however all locks
> involving the table would need to be released before the DROP command
> executes. In the event that someone is using the table, the drop command
> will wait until the locks are released or your connection times out (which
> ever comes first).
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
>
>|||While the DROP command is waiting, unless its connection is holding locks on
other resources, then no, it will not impact any performance of EM until it
actually executes. While executing, it may impact performance depending on
the table size involved and the time it takes to complete its work. This is
especially true if the web site is waiting for the "refreshed" table to be
repopulated.
All that said, if you are issuing the DROP command through EM, then yes, EM
will become "unresponsive" while it waits for the command to complete.
Unfortunately EM operates synchronously so any operation you perform using
EM may cause it to pause while the operation completes.
--Brian
(Please reply to the newsgroups only.)
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:2F58BF53-683A-4E5B-A664-0F418DD019EF@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply. That should help a lot. Another question to your
> reply
> though. If the job is waiting for the locks to be released, will this
> cause
> the database to become unresponsive through Enterprise Manager, and will
> this
> slow database access (as a whole) through the website?
> "Brian Lawton" wrote:
>sql

Dropping and Recreating Tables

We have a job that runs every 30 minutes dropping and recreating a table with
updated information. Besides the fact that I am trying to get rid of this
needless process, the question I have is:
When this job is running and attempting to drop and recreate the table will
it not be able to complete if someone has a connection to the database and/or
is running a report off the table that SQL Server is attempting to drop and
recreate? If there is a lock on this table from someone using it, is there a
way I can stop this from happening?Connections to the database itself will not matter however all locks
involving the table would need to be released before the DROP command
executes. In the event that someone is using the table, the drop command
will wait until the locks are released or your connection times out (which
ever comes first).
--
--Brian
(Please reply to the newsgroups only.)
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
> We have a job that runs every 30 minutes dropping and recreating a table
> with
> updated information. Besides the fact that I am trying to get rid of this
> needless process, the question I have is:
> When this job is running and attempting to drop and recreate the table
> will
> it not be able to complete if someone has a connection to the database
> and/or
> is running a report off the table that SQL Server is attempting to drop
> and
> recreate? If there is a lock on this table from someone using it, is there
> a
> way I can stop this from happening?|||Thanks for the reply. That should help a lot. Another question to your reply
though. If the job is waiting for the locks to be released, will this cause
the database to become unresponsive through Enterprise Manager, and will this
slow database access (as a whole) through the website?
"Brian Lawton" wrote:
> Connections to the database itself will not matter however all locks
> involving the table would need to be released before the DROP command
> executes. In the event that someone is using the table, the drop command
> will wait until the locks are released or your connection times out (which
> ever comes first).
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
> > We have a job that runs every 30 minutes dropping and recreating a table
> > with
> > updated information. Besides the fact that I am trying to get rid of this
> > needless process, the question I have is:
> >
> > When this job is running and attempting to drop and recreate the table
> > will
> > it not be able to complete if someone has a connection to the database
> > and/or
> > is running a report off the table that SQL Server is attempting to drop
> > and
> > recreate? If there is a lock on this table from someone using it, is there
> > a
> > way I can stop this from happening?
>
>|||While the DROP command is waiting, unless its connection is holding locks on
other resources, then no, it will not impact any performance of EM until it
actually executes. While executing, it may impact performance depending on
the table size involved and the time it takes to complete its work. This is
especially true if the web site is waiting for the "refreshed" table to be
repopulated.
All that said, if you are issuing the DROP command through EM, then yes, EM
will become "unresponsive" while it waits for the command to complete.
Unfortunately EM operates synchronously so any operation you perform using
EM may cause it to pause while the operation completes.
--
--Brian
(Please reply to the newsgroups only.)
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:2F58BF53-683A-4E5B-A664-0F418DD019EF@.microsoft.com...
> Thanks for the reply. That should help a lot. Another question to your
> reply
> though. If the job is waiting for the locks to be released, will this
> cause
> the database to become unresponsive through Enterprise Manager, and will
> this
> slow database access (as a whole) through the website?
> "Brian Lawton" wrote:
>> Connections to the database itself will not matter however all locks
>> involving the table would need to be released before the DROP command
>> executes. In the event that someone is using the table, the drop command
>> will wait until the locks are released or your connection times out
>> (which
>> ever comes first).
>> --
>> --Brian
>> (Please reply to the newsgroups only.)
>>
>> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
>> news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
>> > We have a job that runs every 30 minutes dropping and recreating a
>> > table
>> > with
>> > updated information. Besides the fact that I am trying to get rid of
>> > this
>> > needless process, the question I have is:
>> >
>> > When this job is running and attempting to drop and recreate the table
>> > will
>> > it not be able to complete if someone has a connection to the database
>> > and/or
>> > is running a report off the table that SQL Server is attempting to drop
>> > and
>> > recreate? If there is a lock on this table from someone using it, is
>> > there
>> > a
>> > way I can stop this from happening?
>>

Dropping and recreating all statistics

I am having deadlock problems and I have noticed that on one table dropping
the statistics and recreating them helped. Is there a script or an easy way
to do this
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
sp_updatestats is what you're looking for.
On some servers I regularly rebuild all indexes (which also updates the
stats) and then issue DBCC FREEPROCCACHE to completely refresh the system.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||use UPDATE STATISTICS 'tablename', or drop statistics
tablename.statisticsname Followed by a create statistics
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
"ghunter via droptable.com" <u4529@.uwe> wrote in message
news:672d4e349b451@.uwe...
>I am having deadlock problems and I have noticed that on one table dropping
> the statistics and recreating them helped. Is there a script or an easy
> way
> to do this
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200610/1
>
|||I have tried that and then ran exec sp_helpstats and it showed no statistics
where on the table. What I want to do is delete all statistics on all tables
and them create them on all indexes and colums. I have 10,000 tables so I am
looking for a script.
Paul Ibison wrote:
>sp_updatestats is what you're looking for.
>On some servers I regularly rebuild all indexes (which also updates the
>stats) and then issue DBCC FREEPROCCACHE to completely refresh the system.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
|||could you point me to a script that automagically does all that for me?
Hilary Cotter wrote:[vbcol=seagreen]
>use UPDATE STATISTICS 'tablename', or drop statistics
>tablename.statisticsname Followed by a create statistics
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
|||sp_updatestats.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||sp_updatestats will run UPDATE STATISTICS on all tables. It won't create
stats on tables.
The Indexes should all have stats which will get updated using DBCC
DBREINDEX.
If you want to check that the stats have been updated, run DBCC
SHOW_STATISTICS:
eg: DBCC SHOW_STATISTICS ('District','pk_district')
The first column is called 'Updated' and has the date and time of the
update.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Maybe I am not making my question clear enough. Does sp_updatestats delete
and recreate the statistics. And what does sp_updatestats create statistics
on.
I was not aware that update stats would create statistics on something but
that it would only update them. and on top of that it will only update them
if they are out of date.
Paul Ibison wrote:
>sp_updatestats.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
|||sp_updatestats runs UPDATE STATISTICS on all user tables in the current
database - as far as I know this means that the statblob column is updated
in sysindexes. It doesn't create statistics on columns where there weren't
any previously existing. For indexes there'll already be statistics, and
there'll also be stats for those columns which have been explicitly created
(CREATE STATISTICS). If the key values for indexes have changed a lot or if
there is a significant change in the amount of data in the table then
updating the statistics is useful, and also recompiling the associated
stored procedures whose plans will be inaccurate.
HTH,
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
sql

Dropping and recreating all statistics

I am having deadlock problems and I have noticed that on one table dropping
the statistics and recreating them helped. Is there a script or an easy way
to do this
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1sp_updatestats is what you're looking for.
On some servers I regularly rebuild all indexes (which also updates the
stats) and then issue DBCC FREEPROCCACHE to completely refresh the system.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||use UPDATE STATISTICS 'tablename', or drop statistics
tablename.statisticsname Followed by a create statistics
--
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
"ghunter via SQLMonster.com" <u4529@.uwe> wrote in message
news:672d4e349b451@.uwe...
>I am having deadlock problems and I have noticed that on one table dropping
> the statistics and recreating them helped. Is there a script or an easy
> way
> to do this
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1
>|||I have tried that and then ran exec sp_helpstats and it showed no statistics
where on the table. What I want to do is delete all statistics on all tables
and them create them on all indexes and colums. I have 10,000 tables so I am
looking for a script.
Paul Ibison wrote:
>sp_updatestats is what you're looking for.
>On some servers I regularly rebuild all indexes (which also updates the
>stats) and then issue DBCC FREEPROCCACHE to completely refresh the system.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||could you point me to a script that automagically does all that for me?
Hilary Cotter wrote:
>use UPDATE STATISTICS 'tablename', or drop statistics
>tablename.statisticsname Followed by a create statistics
>>I am having deadlock problems and I have noticed that on one table dropping
>> the statistics and recreating them helped. Is there a script or an easy
>> way
>> to do this
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||sp_updatestats.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||sp_updatestats will run UPDATE STATISTICS on all tables. It won't create
stats on tables.
The Indexes should all have stats which will get updated using DBCC
DBREINDEX.
If you want to check that the stats have been updated, run DBCC
SHOW_STATISTICS:
eg: DBCC SHOW_STATISTICS ('District','pk_district')
The first column is called 'Updated' and has the date and time of the
update.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Maybe I am not making my question clear enough. Does sp_updatestats delete
and recreate the statistics. And what does sp_updatestats create statistics
on.
I was not aware that update stats would create statistics on something but
that it would only update them. and on top of that it will only update them
if they are out of date.
Paul Ibison wrote:
>sp_updatestats.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||sp_updatestats runs UPDATE STATISTICS on all user tables in the current
database - as far as I know this means that the statblob column is updated
in sysindexes. It doesn't create statistics on columns where there weren't
any previously existing. For indexes there'll already be statistics, and
there'll also be stats for those columns which have been explicitly created
(CREATE STATISTICS). If the key values for indexes have changed a lot or if
there is a significant change in the amount of data in the table then
updating the statistics is useful, and also recompiling the associated
stored procedures whose plans will be inaccurate.
HTH,
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Dropping and recreating all statistics

I am having deadlock problems and I have noticed that on one table dropping
the statistics and recreating them helped. Is there a script or an easy way
to do this
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1sp_updatestats is what you're looking for.
On some servers I regularly rebuild all indexes (which also updates the
stats) and then issue DBCC FREEPROCCACHE to completely refresh the system.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||use UPDATE STATISTICS 'tablename', or drop statistics
tablename.statisticsname Followed by a create statistics
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
"ghunter via droptable.com" <u4529@.uwe> wrote in message
news:672d4e349b451@.uwe...
>I am having deadlock problems and I have noticed that on one table dropping
> the statistics and recreating them helped. Is there a script or an easy
> way
> to do this
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200610/1
>|||I have tried that and then ran exec sp_helpstats and it showed no statistics
where on the table. What I want to do is delete all statistics on all tables
and them create them on all indexes and colums. I have 10,000 tables so I am
looking for a script.
Paul Ibison wrote:
>sp_updatestats is what you're looking for.
>On some servers I regularly rebuild all indexes (which also updates the
>stats) and then issue DBCC FREEPROCCACHE to completely refresh the system.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1|||could you point me to a script that automagically does all that for me?
Hilary Cotter wrote:[vbcol=seagreen]
>use UPDATE STATISTICS 'tablename', or drop statistics
>tablename.statisticsname Followed by a create statistics
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1|||sp_updatestats.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||sp_updatestats will run UPDATE STATISTICS on all tables. It won't create
stats on tables.
The Indexes should all have stats which will get updated using DBCC
DBREINDEX.
If you want to check that the stats have been updated, run DBCC
SHOW_STATISTICS:
eg: DBCC SHOW_STATISTICS ('District','pk_district')
The first column is called 'Updated' and has the date and time of the
update.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Maybe I am not making my question clear enough. Does sp_updatestats delete
and recreate the statistics. And what does sp_updatestats create statistics
on.
I was not aware that update stats would create statistics on something but
that it would only update them. and on top of that it will only update them
if they are out of date.
Paul Ibison wrote:
>sp_updatestats.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1|||sp_updatestats runs UPDATE STATISTICS on all user tables in the current
database - as far as I know this means that the statblob column is updated
in sysindexes. It doesn't create statistics on columns where there weren't
any previously existing. For indexes there'll already be statistics, and
there'll also be stats for those columns which have been explicitly created
(CREATE STATISTICS). If the key values for indexes have changed a lot or if
there is a significant change in the amount of data in the table then
updating the statistics is useful, and also recompiling the associated
stored procedures whose plans will be inaccurate.
HTH,
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

dropping and recreating a table

I recall reading somewhere that there are benefits to dropping and
recreating the table, then recreating it's indexes. This is as opposed to
just dropping and recreating the indexes.
Thanks
Regards
JTC ^..^I don't know that there are any advantages to this that you couldn't get via
DBCC statements... Maybe some defragmentation benefits, but you should be
able to get those via DBCC w/o dropping the table...
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns9620E1385E1A7daveJTC@.217.32.252.50...
>I recall reading somewhere that there are benefits to dropping and
> recreating the table, then recreating it's indexes. This is as opposed to
> just dropping and recreating the indexes.
> Thanks
> --
> Regards
> JTC ^..^

dropping and recreating a table

I recall reading somewhere that there are benefits to dropping and
recreating the table, then recreating it's indexes. This is as opposed to
just dropping and recreating the indexes.
Thanks
--
Regards
JTC ^..^I don't know that there are any advantages to this that you couldn't get via
DBCC statements... Maybe some defragmentation benefits, but you should be
able to get those via DBCC w/o dropping the table...
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns9620E1385E1A7daveJTC@.217.32.252.50...
>I recall reading somewhere that there are benefits to dropping and
> recreating the table, then recreating it's indexes. This is as opposed to
> just dropping and recreating the indexes.
> Thanks
> --
> Regards
> JTC ^..^

dropping and recreating a table

I recall reading somewhere that there are benefits to dropping and
recreating the table, then recreating it's indexes. This is as opposed to
just dropping and recreating the indexes.
Thanks
Regards
JTC ^..^
I don't know that there are any advantages to this that you couldn't get via
DBCC statements... Maybe some defragmentation benefits, but you should be
able to get those via DBCC w/o dropping the table...
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns9620E1385E1A7daveJTC@.217.32.252.50...
>I recall reading somewhere that there are benefits to dropping and
> recreating the table, then recreating it's indexes. This is as opposed to
> just dropping and recreating the indexes.
> Thanks
> --
> Regards
> JTC ^..^
sql

Dropping and Creating of a Table.

I have what, hopefully, is a simple question. Do any of the methods
of replication for SQL 2000 support the dropping and creating of a
table and have it replicated to the remote system?
Brandon,
I'm assuming you're talking about an existing publication. In transactional
this is possible using sp_addarticle, sp_addsubscription,
sp_refreshsubscriptions and the corresponding drop commands for the
converse. As far as I recall, snapshot also behaves this way but not merge.
I'll test this when I get a moment.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Dropping An Indexed Column

I have inherited a table with dozens of columns that I no longer want. I want to drop these columns.

So I tried
"ALTER TABLE mydata DROP BLOCK_ID"

and it get an error of: cannot delete a field that is part of an index. How do I get around this?

(BLOCK_ID is the field name of my indexed column)
(The non-indexed ones drop fine.)First remove the column you want to drop from all the indexes that refer it. If there are indexes that refer only that column just drop those. Then you can drop the column.

Cheers,
Suren.|||Yes, I get that I have to drop the index(es) -- but how do I find out which indexes this column is in?

I'm building up to write some scripts to automatically drop a long-list of unwanted columns - how does one go about figuring out what index a field is in? And/or is there a sql way of saying "drop this column and it's indexes" ?|||Well to do that the mist easiest way is to use a graphical tool that organise indexes unser each table and to go through the index and remove the coloms.

If you are thinking of writing scripts then you should select from catalog tables such as user_indexes and user_inx_cols. I think I got the names correct.

Dropping an auto numbered primary key and add a new one

The table I am using have a column called Key which is the primary key of the table and a auto number. This primary key is not a foreign key in any other table.

I need to write SQL to drop the current primary key and add a new one Say "RecordId"

as the new primary key and which should be a autonumber too.

any idea.

thanks in advance

droping and adding a primary key on a column wont affest its 'identity' property, so simply drop the primary key and add again with the new name u want...

alter table tablename drop constraint oldpk

alter table tablename add constraint newpk primary key(id)

|||

What's the point? It is easy enough, just drop the column and add another one, but there might be an easier way to do what you are wanting to do.

Here is a script that does it:

set nocount on
drop table test
go
create table test
(
testId int identity constraint PKtest primary key,
value datetime default (getdate())
)
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
go
select *
from test
go
alter table test
drop PKtest
go
alter table test
drop column testId
go
select *
from test
go
alter table test
add testId int identity (2,2) constraint PKtest primary key
go
select *
from test --the identity values will start at 2 and go up by steps of 2 (so you can see the diff)

sql