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 all indexes with a single query?

Would that it were so simple! I tried that method, no go because
there are constraints that have to be deleted first. So, I figure OK,
I'll just kill the constraints first, then the indexes will go away
with ease. NOT! Since there are two ways to do things, using
standard SQL and using the SQLDMO object, I tried both. Here
is where I am with the code right now:
'******************************
Dim server
Dim tbcnt
Dim idcnt
Dim kecnt
Dim i, j, k
Dim tblname
Dim keyname
Dim sql
Set server = CreateObject("SQLDMO.SQLServer")
server.Connect "Server", "sa", ""
i = "Northwind"
tbcnt = server.Databases(i).Tables.Count
For j = 1 to tbcnt
If server.Databases(i).Tables(j).TypeOf = 8 Then
tblname = server.Databases(i).Tables(j).Name
kecnt = server.Databases(i).Tables(j).Keys.Count
idcnt = server.Databases(i).Tables(j).Indexes.Count
For k = 1 to kecnt
keyname = server.Databases(i).Tables(j).Keys(k).Name
' was: server.Databases(i).Tables(j).Keys(k).Remove
sql = "USE " & i & vbCrLf & _
"ALTER TABLE " & tblname & vbCrLf & _
"DROP CONSTRAINT " & keyname
server.ExecuteImmediate(sql)
Next
For k = 1 to idcnt
server.Databases(i).Tables(j).Indexes(k).Remove
Next
End If
Next
'******************************
Now, Keys here are really constraints, so I tried this, using a
mixture of SQL and VB code, and I also tried it with all VB,
and all SQL as well. No matter what I try, I keep getting
'Constrain xxxx is being referenced by Foreign Key uuuu...etc.
and
'Could not drop constraint xxxx'
Does anyone know if this can be automated like this? If so,
How? I'm already past tearing my hair out, I hate to think
what happens next...
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23d0XGCbQEHA.2132@.TK2MSFTNGP11.phx.gbl...
> So, run the first query, bring back the "DROP INDEX ...; GO; DROP INDEX;
> GO;" statements into a variable, and then execute that just like you
> executed the initial query.
> A
>
How about drop the database and re-create it?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Gary Morris" <gwmorris1@.hotpop.com> wrote in message
news:O8YoOieQEHA.3748@.TK2MSFTNGP09.phx.gbl...
> Would that it were so simple! I tried that method, no go because
> there are constraints that have to be deleted first. So, I figure OK,
> I'll just kill the constraints first, then the indexes will go away
> with ease. NOT! Since there are two ways to do things, using
> standard SQL and using the SQLDMO object, I tried both. Here
> is where I am with the code right now:
> '******************************
> Dim server
> Dim tbcnt
> Dim idcnt
> Dim kecnt
> Dim i, j, k
> Dim tblname
> Dim keyname
> Dim sql
> Set server = CreateObject("SQLDMO.SQLServer")
> server.Connect "Server", "sa", ""
> i = "Northwind"
> tbcnt = server.Databases(i).Tables.Count
> For j = 1 to tbcnt
> If server.Databases(i).Tables(j).TypeOf = 8 Then
> tblname = server.Databases(i).Tables(j).Name
> kecnt = server.Databases(i).Tables(j).Keys.Count
> idcnt = server.Databases(i).Tables(j).Indexes.Count
> For k = 1 to kecnt
> keyname = server.Databases(i).Tables(j).Keys(k).Name
> ' was: server.Databases(i).Tables(j).Keys(k).Remove
> sql = "USE " & i & vbCrLf & _
> "ALTER TABLE " & tblname & vbCrLf & _
> "DROP CONSTRAINT " & keyname
> server.ExecuteImmediate(sql)
> Next
> For k = 1 to idcnt
> server.Databases(i).Tables(j).Indexes(k).Remove
> Next
> End If
> Next
> '******************************
> Now, Keys here are really constraints, so I tried this, using a
> mixture of SQL and VB code, and I also tried it with all VB,
> and all SQL as well. No matter what I try, I keep getting
> 'Constrain xxxx is being referenced by Foreign Key uuuu...etc.
> and
> 'Could not drop constraint xxxx'
> Does anyone know if this can be automated like this? If so,
> How? I'm already past tearing my hair out, I hate to think
> what happens next...
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:%23d0XGCbQEHA.2132@.TK2MSFTNGP11.phx.gbl...
>
|||How did I know you would say that
Actually I had thought of that, but was dearly hoping
that I wouldn't have to resort to it. I just cannot
believe that SQL Server will not allow this in an easier
way. It's no problem manually, but programmatically
it just won't work. Eight hours working on this one
problem today..not very productive. Why can't it just
work like the Sybase version?
Well, I'll hunt around some just to satisfy myself that
some nerdy guru hasn't found a way around this
already, then I'll look into the alternative. What I'm
trying to accomplish is a script that will drop just the
indexes of ALL the user tables, OR just the indexes
on specified tables. I won't go into the reasons, that's
just the way it has to work. Now, I have considered
just writing the sql out for each table, parsing it to
remove the constraint/index parts, and then reimport
the table(s) back into the db, which would work, I
just consider it to be a dirty hack, and I should have
a nice clean, acceptable way to do it. This sucks...
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:e%23lGhGgQEHA.252@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> How about drop the database and re-create it?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
> "Gary Morris" <gwmorris1@.hotpop.com> wrote in message
> news:O8YoOieQEHA.3748@.TK2MSFTNGP09.phx.gbl...
INDEX;
>
sql

DropDown width of database selection combo

In Query Analyzer the database dropdown combo is not wide enough to show
the full database name. We have a lot of database with similar starting
names and trying to decide which one to choose can be a matter of trial
and error.
Is there any add on perhaps that provides a wider dropdown to select the
database.
Has this been fixed in a more recent version?
Thanks,
Tom.
v.8.00.760
Have you tried Ctrl-U ?
Andrew J. Kelly SQL MVP
"Tom Corcoran" <tom.corcoran.nospam@.nospam.sportstg.com> wrote in message
news:%23%23MJ4UJqFHA.2696@.TK2MSFTNGP11.phx.gbl...
> In Query Analyzer the database dropdown combo is not wide enough to show
> the full database name. We have a lot of database with similar starting
> names and trying to decide which one to choose can be a matter of trial
> and error.
> Is there any add on perhaps that provides a wider dropdown to select the
> database.
> Has this been fixed in a more recent version?
> Thanks,
> Tom.
> v.8.00.760
|||Andrew J. Kelly wrote:
> Have you tried Ctrl-U ?
Wicked! Great shortcut.
Thanks, Tom.
sql

DROPCLEANBUFFERS/FREEPROCCACHE

I've been doing some stored proc tuning and I want to ensure that after the
changes I make to the query the execution times are accurate. So basically,
if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
users, am I clearing ALL buffers/proccache that SQL is using, or just the
ones tied to my connection?Everything for the server.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@.microsoft.com...
> I've been doing some stored proc tuning and I want to ensure that after
the
> changes I make to the query the execution times are accurate. So
basically,
> if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
> users, am I clearing ALL buffers/proccache that SQL is using, or just the
> ones tied to my connection?|||There is no need to issue FREEPROCCACHE as each time you edit the sp it will
invalidate the plan already in cache anyway. DropCleanBuffers is data
related and has nothing to do with the sp execution plan chosen.
--
Andrew J. Kelly SQL MVP
"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@.microsoft.com...
> I've been doing some stored proc tuning and I want to ensure that after
> the
> changes I make to the query the execution times are accurate. So
> basically,
> if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
> users, am I clearing ALL buffers/proccache that SQL is using, or just the
> ones tied to my connection?|||well, i thought so about DROPCLEANBUFFERS but the proc ran quicker (without
change) every time I ran it after the first time. So SQL must have been
acessing the data from the buffer? Thanks for your quick response.
"Andrew J. Kelly" wrote:
> There is no need to issue FREEPROCCACHE as each time you edit the sp it will
> invalidate the plan already in cache anyway. DropCleanBuffers is data
> related and has nothing to do with the sp execution plan chosen.
> --
> Andrew J. Kelly SQL MVP
>
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@.microsoft.com...
> > I've been doing some stored proc tuning and I want to ensure that after
> > the
> > changes I make to the query the execution times are accurate. So
> > basically,
> > if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
> > users, am I clearing ALL buffers/proccache that SQL is using, or just the
> > ones tied to my connection?
>
>|||"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:43E5BEE0-D6D4-4D78-A6E0-6F128AA7B961@.microsoft.com...
> well, i thought so about DROPCLEANBUFFERS but the proc ran quicker
(without
> change) every time I ran it after the first time. So SQL must have been
> acessing the data from the buffer? Thanks for your quick response.
Andrew may not agree with the method, but I personally always do a
CHECKPOINT then run DROPCLEANBUFFERS and FREEPROCCACHE when I'm testing
stored procedure changes for single-run performance -- I want to work with a
totally clean slate.
Of course, a single-run doesn't tell you much; you might instead want to
try running it 10 or 20 times and taking the average. I've found this gives
much more interesting results. Sometimes a single run can be skewed by
something else on the server running (or not running, thereby making you
think you've done better than you really have). If you run it several times
and take an average, that's less likely to affect your results.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||It is not that I don't agree with doing that but it is important to
understand what happens when you do that and how it may or may not affect
your results. But it's rare any more that I do a single test like that
since it rarely mimics real life conditions on busy systems. I can usually
tell more from the query plan than anything else. But That does not mean
this can not be useful information.
Andrew J. Kelly SQL MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uC9JouXMFHA.2252@.TK2MSFTNGP15.phx.gbl...
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:43E5BEE0-D6D4-4D78-A6E0-6F128AA7B961@.microsoft.com...
>> well, i thought so about DROPCLEANBUFFERS but the proc ran quicker
> (without
>> change) every time I ran it after the first time. So SQL must have been
>> acessing the data from the buffer? Thanks for your quick response.
> Andrew may not agree with the method, but I personally always do a
> CHECKPOINT then run DROPCLEANBUFFERS and FREEPROCCACHE when I'm testing
> stored procedure changes for single-run performance -- I want to work with
> a
> totally clean slate.
> Of course, a single-run doesn't tell you much; you might instead want
> to
> try running it 10 or 20 times and taking the average. I've found this
> gives
> much more interesting results. Sometimes a single run can be skewed by
> something else on the server running (or not running, thereby making you
> think you've done better than you really have). If you run it several
> times
> and take an average, that's less likely to affect your results.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>sql

DROPCLEANBUFFERS/FREEPROCCACHE

I've been doing some stored proc tuning and I want to ensure that after the
changes I make to the query the execution times are accurate. So basically,
if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
users, am I clearing ALL buffers/proccache that SQL is using, or just the
ones tied to my connection?Everything for the server.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@.microsoft.com...
> I've been doing some stored proc tuning and I want to ensure that after
the
> changes I make to the query the execution times are accurate. So
basically,
> if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
> users, am I clearing ALL buffers/proccache that SQL is using, or just the
> ones tied to my connection?|||There is no need to issue FREEPROCCACHE as each time you edit the sp it will
invalidate the plan already in cache anyway. DropCleanBuffers is data
related and has nothing to do with the sp execution plan chosen.
Andrew J. Kelly SQL MVP
"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@.microsoft.com...
> I've been doing some stored proc tuning and I want to ensure that after
> the
> changes I make to the query the execution times are accurate. So
> basically,
> if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
> users, am I clearing ALL buffers/proccache that SQL is using, or just the
> ones tied to my connection?|||well, i thought so about DROPCLEANBUFFERS but the proc ran quicker (without
change) every time I ran it after the first time. So SQL must have been
acessing the data from the buffer? Thanks for your quick response.
"Andrew J. Kelly" wrote:

> There is no need to issue FREEPROCCACHE as each time you edit the sp it wi
ll
> invalidate the plan already in cache anyway. DropCleanBuffers is data
> related and has nothing to do with the sp execution plan chosen.
> --
> Andrew J. Kelly SQL MVP
>
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@.microsoft.com...
>
>|||"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:43E5BEE0-D6D4-4D78-A6E0-6F128AA7B961@.microsoft.com...
> well, i thought so about DROPCLEANBUFFERS but the proc ran quicker
(without
> change) every time I ran it after the first time. So SQL must have been
> acessing the data from the buffer? Thanks for your quick response.
Andrew may not agree with the method, but I personally always do a
CHECKPOINT then run DROPCLEANBUFFERS and FREEPROCCACHE when I'm testing
stored procedure changes for single-run performance -- I want to work with a
totally clean slate.
Of course, a single-run doesn't tell you much; you might instead want to
try running it 10 or 20 times and taking the average. I've found this gives
much more interesting results. Sometimes a single run can be skewed by
something else on the server running (or not running, thereby making you
think you've done better than you really have). If you run it several times
and take an average, that's less likely to affect your results.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||It is not that I don't agree with doing that but it is important to
understand what happens when you do that and how it may or may not affect
your results. But it's rare any more that I do a single test like that
since it rarely mimics real life conditions on busy systems. I can usually
tell more from the query plan than anything else. But That does not mean
this can not be useful information.
Andrew J. Kelly SQL MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uC9JouXMFHA.2252@.TK2MSFTNGP15.phx.gbl...
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:43E5BEE0-D6D4-4D78-A6E0-6F128AA7B961@.microsoft.com...
> (without
> Andrew may not agree with the method, but I personally always do a
> CHECKPOINT then run DROPCLEANBUFFERS and FREEPROCCACHE when I'm testing
> stored procedure changes for single-run performance -- I want to work with
> a
> totally clean slate.
> Of course, a single-run doesn't tell you much; you might instead want
> to
> try running it 10 or 20 times and taking the average. I've found this
> gives
> much more interesting results. Sometimes a single run can be skewed by
> something else on the server running (or not running, thereby making you
> think you've done better than you really have). If you run it several
> times
> and take an average, that's less likely to affect your results.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>

DROPCLEANBUFFERS/FREEPROCCACHE

I've been doing some stored proc tuning and I want to ensure that after the
changes I make to the query the execution times are accurate. So basically,
if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
users, am I clearing ALL buffers/proccache that SQL is using, or just the
ones tied to my connection?
Everything for the server.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@.microsoft.com...
> I've been doing some stored proc tuning and I want to ensure that after
the
> changes I make to the query the execution times are accurate. So
basically,
> if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
> users, am I clearing ALL buffers/proccache that SQL is using, or just the
> ones tied to my connection?
|||There is no need to issue FREEPROCCACHE as each time you edit the sp it will
invalidate the plan already in cache anyway. DropCleanBuffers is data
related and has nothing to do with the sp execution plan chosen.
Andrew J. Kelly SQL MVP
"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@.microsoft.com...
> I've been doing some stored proc tuning and I want to ensure that after
> the
> changes I make to the query the execution times are accurate. So
> basically,
> if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
> users, am I clearing ALL buffers/proccache that SQL is using, or just the
> ones tied to my connection?
|||well, i thought so about DROPCLEANBUFFERS but the proc ran quicker (without
change) every time I ran it after the first time. So SQL must have been
acessing the data from the buffer? Thanks for your quick response.
"Andrew J. Kelly" wrote:

> There is no need to issue FREEPROCCACHE as each time you edit the sp it will
> invalidate the plan already in cache anyway. DropCleanBuffers is data
> related and has nothing to do with the sp execution plan chosen.
> --
> Andrew J. Kelly SQL MVP
>
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@.microsoft.com...
>
>
|||"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:43E5BEE0-D6D4-4D78-A6E0-6F128AA7B961@.microsoft.com...
> well, i thought so about DROPCLEANBUFFERS but the proc ran quicker
(without
> change) every time I ran it after the first time. So SQL must have been
> acessing the data from the buffer? Thanks for your quick response.
Andrew may not agree with the method, but I personally always do a
CHECKPOINT then run DROPCLEANBUFFERS and FREEPROCCACHE when I'm testing
stored procedure changes for single-run performance -- I want to work with a
totally clean slate.
Of course, a single-run doesn't tell you much; you might instead want to
try running it 10 or 20 times and taking the average. I've found this gives
much more interesting results. Sometimes a single run can be skewed by
something else on the server running (or not running, thereby making you
think you've done better than you really have). If you run it several times
and take an average, that's less likely to affect your results.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
|||It is not that I don't agree with doing that but it is important to
understand what happens when you do that and how it may or may not affect
your results. But it's rare any more that I do a single test like that
since it rarely mimics real life conditions on busy systems. I can usually
tell more from the query plan than anything else. But That does not mean
this can not be useful information.
Andrew J. Kelly SQL MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uC9JouXMFHA.2252@.TK2MSFTNGP15.phx.gbl...
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:43E5BEE0-D6D4-4D78-A6E0-6F128AA7B961@.microsoft.com...
> (without
> Andrew may not agree with the method, but I personally always do a
> CHECKPOINT then run DROPCLEANBUFFERS and FREEPROCCACHE when I'm testing
> stored procedure changes for single-run performance -- I want to work with
> a
> totally clean slate.
> Of course, a single-run doesn't tell you much; you might instead want
> to
> try running it 10 or 20 times and taking the average. I've found this
> gives
> much more interesting results. Sometimes a single run can be skewed by
> something else on the server running (or not running, thereby making you
> think you've done better than you really have). If you run it several
> times
> and take an average, that's less likely to affect your results.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>

DROPCLEANBUFFERS doesn't seem to clear cache

I have a query that I'm trying to benchmark. The first time I run it,
it takes 40 seconds to run. The next time I run it, it returns in 0
seconds.
I execute the following:
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Then I run the query again and it takes 40 seconds, as expected.
Re-running the query causes it to go back down to 0 seconds, as
expected. However, from this point forward, even if I run the commands
above, the query stays at 0 seconds! I can't seem to clear the cache
anymore.
Is something being cached in spite of dropping the data and procedure
caches?Hello,
It should work. Can you set the Statistics I/O and Statistics time and see
if there is any change in values after and before clearing the cache.
Thanks
Hari
SQL Server MVP
"element533@.gmail.com" wrote:

> I have a query that I'm trying to benchmark. The first time I run it,
> it takes 40 seconds to run. The next time I run it, it returns in 0
> seconds.
> I execute the following:
> CHECKPOINT
> DBCC DROPCLEANBUFFERS
> DBCC FREEPROCCACHE
> Then I run the query again and it takes 40 seconds, as expected.
> Re-running the query causes it to go back down to 0 seconds, as
> expected. However, from this point forward, even if I run the commands
> above, the query stays at 0 seconds! I can't seem to clear the cache
> anymore.
> Is something being cached in spite of dropping the data and procedure
> caches?
>|||You might compare the query plans to make sure they are the same. It might
be that a large scan takes 40 seconds without cached data but runs
sub-second once data are cached. A plan change (e.g. index seek) could
cause the query to run sub-second with or without cached data.
Hope this helps.
Dan Guzman
SQL Server MVP
<element533@.gmail.com> wrote in message
news:1158086894.924338.242370@.i42g2000cwa.googlegroups.com...
>I have a query that I'm trying to benchmark. The first time I run it,
> it takes 40 seconds to run. The next time I run it, it returns in 0
> seconds.
> I execute the following:
> CHECKPOINT
> DBCC DROPCLEANBUFFERS
> DBCC FREEPROCCACHE
> Then I run the query again and it takes 40 seconds, as expected.
> Re-running the query causes it to go back down to 0 seconds, as
> expected. However, from this point forward, even if I run the commands
> above, the query stays at 0 seconds! I can't seem to clear the cache
> anymore.
> Is something being cached in spite of dropping the data and procedure
> caches?
>|||But why would the same SELECT statement result in two different query
plans on two different runs?
Dan Guzman wrote:[vbcol=seagreen]
> You might compare the query plans to make sure they are the same. It migh
t
> be that a large scan takes 40 seconds without cached data but runs
> sub-second once data are cached. A plan change (e.g. index seek) could
> cause the query to run sub-second with or without cached data.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <element533@.gmail.com> wrote in message
> news:1158086894.924338.242370@.i42g2000cwa.googlegroups.com...|||> But why would the same SELECT statement result in two different query
> plans on two different runs?
You might get different plans due to differences in statistics.
Hope this helps.
Dan Guzman
SQL Server MVP
<element533@.gmail.com> wrote in message
news:1158182579.563636.167870@.i42g2000cwa.googlegroups.com...
> But why would the same SELECT statement result in two different query
> plans on two different runs?
> Dan Guzman wrote:
>|||On 12 Sep 2006 11:48:15 -0700, element533@.gmail.com wrote:
>I have a query that I'm trying to benchmark. The first time I run it,
>it takes 40 seconds to run. The next time I run it, it returns in 0
>seconds.
>I execute the following:
>CHECKPOINT
>DBCC DROPCLEANBUFFERS
>DBCC FREEPROCCACHE
>Then I run the query again and it takes 40 seconds, as expected.
>Re-running the query causes it to go back down to 0 seconds, as
>expected. However, from this point forward, even if I run the commands
>above, the query stays at 0 seconds! I can't seem to clear the cache
>anymore.
Pretty weird.
You haven't left any transactions open, have you?
You might turn on a few perfmon stats to see if you can get more info.
J.

DROPCLEANBUFFERS doesn't seem to clear cache

I have a query that I'm trying to benchmark. The first time I run it,
it takes 40 seconds to run. The next time I run it, it returns in 0
seconds.
I execute the following:
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Then I run the query again and it takes 40 seconds, as expected.
Re-running the query causes it to go back down to 0 seconds, as
expected. However, from this point forward, even if I run the commands
above, the query stays at 0 seconds! I can't seem to clear the cache
anymore.
Is something being cached in spite of dropping the data and procedure
caches?Hello,
It should work. Can you set the Statistics I/O and Statistics time and see
if there is any change in values after and before clearing the cache.
Thanks
Hari
SQL Server MVP
"element533@.gmail.com" wrote:
> I have a query that I'm trying to benchmark. The first time I run it,
> it takes 40 seconds to run. The next time I run it, it returns in 0
> seconds.
> I execute the following:
> CHECKPOINT
> DBCC DROPCLEANBUFFERS
> DBCC FREEPROCCACHE
> Then I run the query again and it takes 40 seconds, as expected.
> Re-running the query causes it to go back down to 0 seconds, as
> expected. However, from this point forward, even if I run the commands
> above, the query stays at 0 seconds! I can't seem to clear the cache
> anymore.
> Is something being cached in spite of dropping the data and procedure
> caches?
>|||You might compare the query plans to make sure they are the same. It might
be that a large scan takes 40 seconds without cached data but runs
sub-second once data are cached. A plan change (e.g. index seek) could
cause the query to run sub-second with or without cached data.
Hope this helps.
Dan Guzman
SQL Server MVP
<element533@.gmail.com> wrote in message
news:1158086894.924338.242370@.i42g2000cwa.googlegroups.com...
>I have a query that I'm trying to benchmark. The first time I run it,
> it takes 40 seconds to run. The next time I run it, it returns in 0
> seconds.
> I execute the following:
> CHECKPOINT
> DBCC DROPCLEANBUFFERS
> DBCC FREEPROCCACHE
> Then I run the query again and it takes 40 seconds, as expected.
> Re-running the query causes it to go back down to 0 seconds, as
> expected. However, from this point forward, even if I run the commands
> above, the query stays at 0 seconds! I can't seem to clear the cache
> anymore.
> Is something being cached in spite of dropping the data and procedure
> caches?
>|||But why would the same SELECT statement result in two different query
plans on two different runs?
Dan Guzman wrote:
> You might compare the query plans to make sure they are the same. It might
> be that a large scan takes 40 seconds without cached data but runs
> sub-second once data are cached. A plan change (e.g. index seek) could
> cause the query to run sub-second with or without cached data.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <element533@.gmail.com> wrote in message
> news:1158086894.924338.242370@.i42g2000cwa.googlegroups.com...
> >I have a query that I'm trying to benchmark. The first time I run it,
> > it takes 40 seconds to run. The next time I run it, it returns in 0
> > seconds.
> >
> > I execute the following:
> >
> > CHECKPOINT
> > DBCC DROPCLEANBUFFERS
> > DBCC FREEPROCCACHE
> >
> > Then I run the query again and it takes 40 seconds, as expected.
> > Re-running the query causes it to go back down to 0 seconds, as
> > expected. However, from this point forward, even if I run the commands
> > above, the query stays at 0 seconds! I can't seem to clear the cache
> > anymore.
> >
> > Is something being cached in spite of dropping the data and procedure
> > caches?
> >|||> But why would the same SELECT statement result in two different query
> plans on two different runs?
You might get different plans due to differences in statistics.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<element533@.gmail.com> wrote in message
news:1158182579.563636.167870@.i42g2000cwa.googlegroups.com...
> But why would the same SELECT statement result in two different query
> plans on two different runs?
> Dan Guzman wrote:
>> You might compare the query plans to make sure they are the same. It
>> might
>> be that a large scan takes 40 seconds without cached data but runs
>> sub-second once data are cached. A plan change (e.g. index seek) could
>> cause the query to run sub-second with or without cached data.
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> <element533@.gmail.com> wrote in message
>> news:1158086894.924338.242370@.i42g2000cwa.googlegroups.com...
>> >I have a query that I'm trying to benchmark. The first time I run it,
>> > it takes 40 seconds to run. The next time I run it, it returns in 0
>> > seconds.
>> >
>> > I execute the following:
>> >
>> > CHECKPOINT
>> > DBCC DROPCLEANBUFFERS
>> > DBCC FREEPROCCACHE
>> >
>> > Then I run the query again and it takes 40 seconds, as expected.
>> > Re-running the query causes it to go back down to 0 seconds, as
>> > expected. However, from this point forward, even if I run the commands
>> > above, the query stays at 0 seconds! I can't seem to clear the cache
>> > anymore.
>> >
>> > Is something being cached in spite of dropping the data and procedure
>> > caches?
>> >
>|||On 12 Sep 2006 11:48:15 -0700, element533@.gmail.com wrote:
>I have a query that I'm trying to benchmark. The first time I run it,
>it takes 40 seconds to run. The next time I run it, it returns in 0
>seconds.
>I execute the following:
>CHECKPOINT
>DBCC DROPCLEANBUFFERS
>DBCC FREEPROCCACHE
>Then I run the query again and it takes 40 seconds, as expected.
>Re-running the query causes it to go back down to 0 seconds, as
>expected. However, from this point forward, even if I run the commands
>above, the query stays at 0 seconds! I can't seem to clear the cache
>anymore.
Pretty weird.
You haven't left any transactions open, have you?
You might turn on a few perfmon stats to see if you can get more info.
J.

Drop temporary tables whilst connected ?

Just a quicky about temporarary tables. If using QA, when you create a
temporary table, it gets dropped if you close the query. Otherwise you
need to state 'DROP TABLE myTable' so that you can re-run the query
without the table being there.

Sometimes, you can have quite lengthy SQL statements (in a series)
with various drop table sections throughout the query. Ideally you
would put these all at the end, but sometimes you will need to drop
some part way through (for ease of reading and max temp tables etc...)

However, what I was wondering is :

Is there any way to quickly drop the temporary tables for the current
connection without specifying all of the tables individually ? When
testing/checking, you have to work your way through and run each drop
table section individually. This can be time consuming, so being
naturally lazy, is there a quick way of doing this ? When working
through the SQL, it's possible to do this quite a lot.

Example

SQL Statement with several parts, each uses a series of temporary
tables to create a result set. At the end of a section, these work
tables are no longer needed, so drop table commands are used. The
final result set brings back the combined results from each section
and then drops those at the end.

TIA

RyanRyan (ryanofford@.hotmail.com) writes:
> Just a quicky about temporarary tables. If using QA, when you create a
> temporary table, it gets dropped if you close the query. Otherwise you
> need to state 'DROP TABLE myTable' so that you can re-run the query
> without the table being there.
> Sometimes, you can have quite lengthy SQL statements (in a series)
> with various drop table sections throughout the query. Ideally you
> would put these all at the end, but sometimes you will need to drop
> some part way through (for ease of reading and max temp tables etc...)
> However, what I was wondering is :
> Is there any way to quickly drop the temporary tables for the current
> connection without specifying all of the tables individually ? When
> testing/checking, you have to work your way through and run each drop
> table section individually. This can be time consuming, so being
> naturally lazy, is there a quick way of doing this ? When working
> through the SQL, it's possible to do this quite a lot.

No, there is no "DROP TABLE #%".

You could write a cursor over tempdb..sysobjects which finds the tables,
but then you would have to mask out the part which is tacked on to the
table name. Kind of messy.

On the other hand, why not pack everything in a stored procedure? A temp
created in a scope is dropped when that scope exits. Thus, with a stored
procedure, this is a non-problem.

If using a stored procedure is problematic for some reason, a RAISERROR
with level 21 is a brutal way if getting rid of the temp tables - in
fact, this kills your connection. Only do this, if you are your own DBA,
because it may ping an alert for an operator on a big server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> On the other hand, why not pack everything in a stored procedure? A temp
> created in a scope is dropped when that scope exits. Thus, with a stored
> procedure, this is a non-problem.

Most of this type of query will be put into a stored procedure once
finished, but we most often need to work through it in stages to check
that we have the maths correct at each stage before we progress this
further into an SP. We do a lot of manipulating financials so need to
check our maths throughout. We have a lot of reports based on our
figures and each needs to use the same logic but slightly different
groups of answers which needs checking.

In a lot of cases the SQL can be over a thousand lines long, so we
tend to break it down as much as possible in order to keep it simple.
Hence grouping the drop table statements so we can work with it.

Thanks

Ryan

DROP Temp Table or any scripts

Hi,

What control can I use to put any scripts like that?

ExecuteSQLTask lets me do just sql query.

I need to have something like this.

USE [DB1]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Lookup]') AND type in (N'U'))

DROP TABLE [dbo].[Lookup]

or Create

CREATE TABLE [Lookup] (
[MD_ID_Old] INTEGER,
[MD_ID_New] uniqueidentifier
)

I need to find the way to use scripts in SSIS.

Thanks.

Have you tried your sql statements in ExecuteSQLTask? This components exists for executing any sql statement. There is a SourceType property that allows you to point to a file [your script] and you can execute the sql statements that way.

Hope this helps...

Senthil

|||Thanks.

drop table not supported

I create an Execute SQL Task and add a connection and the sql
DROP TABLE Table1
Parse Query succeeds. But Build Query gives the error "The DROP TABLE SQL
construt or statement is not supported."
Baffling ...John,
I think you are asking about SSIS related queries, and this group is for
Reporting Services.
Amarnath, MCTS
"John Grandy" wrote:
> I create an Execute SQL Task and add a connection and the sql
> DROP TABLE Table1
> Parse Query succeeds. But Build Query gives the error "The DROP TABLE SQL
> construt or statement is not supported."
> Baffling ...
>
>

Drop Table Gets skipped in a query batch

I have a situation in SQL Svr 2k that I can't explain. I have a query batch that runs in a SQL step in a DTS package. All steps execute except the Drop Table statements. Here is a section of the code;

************************************************** ****
INSERT INTO Oral_VitDhistory ( MeNumber, DrugType, Territory, Quantity, SalesAmt, DataType, RXDate )
SELECT tbl_Oral_VitDHistory.MENumber, tbl_Oral_VitDHistory.DrugType, tbl_Oral_VitDHistory.Territory, tbl_Oral_VitDHistory.Quantity, tbl_Oral_VitDHistory.SalesAmt, tbl_Oral_VitDHistory.DataType, tbl_Oral_VitDHistory.RXDate
FROM Data_Warehouse..tbl_Oral_VitDHistory WHERE tbl_Oral_VitDHistory.Territory Is Not Null

drop table Data_Warehouse..NDC24month_Cost
drop table Data_Warehouse..NDC24month_Count

/* Process New Rx */

select MeNumber, zip, RXTypecode, DrugTYpe, NRX1 into NDC24month_Cost from Data_Warehouse..NDC24month where RxTypecode = '$ B'

Insert into tbl_Oral_VitDHistory(MeNumber, DrugType, Quantity, SalesAmt) select a.MeNumber,
a.DrugType, a.NRX1 as Quantity, b.NRX1 as SalesAmt from Data_Warehouse..NDC24month_Count as a, NDC24month_Cost
as b where a.Menumber = b.MeNumber and a.DrugType = b.DrugType
************************************************** *******

The drop table statements appear to get skipped and the code fails because the column names should have been changed as the dropped tables should have been recreated with a different column name.

This used to work fine. Then suddenly it stopped working. No changes to the SQL installation from when it worked to when it stopped. The batch also fails in the QA if cut and pasted and ran from there.

I ran accross this once before in a Stored Proc with the Drop table statement and had to put the statement in a different procedure.

Anybody ever run accross this? I have searched the MS KB to no avail.

Thanks in advance to the genius who can help a guy out!Howdy,

I have come across this before - usually the best way to get around it is to break the code into separate sprocs & then call each in turn from a main sproc. Even using BEGIN TRAN...COMMIT TRAN wont help....it seems to get a bee in its bonnet and thats it......

Also, if its in DTS package etc , break up the code into logical chunks with end of batch "GO" commands. This will mean separate sprocs if you run it in one sproc currently.

Cheers,

SG.|||Thanks for your help!

Thats about where I thought I would have to go with it.
You confirmed my suspicions!

Have a great one!

Originally posted by sqlguy7777
Howdy,

I have come across this before - usually the best way to get around it is to break the code into separate sprocs & then call each in turn from a main sproc. Even using BEGIN TRAN...COMMIT TRAN wont help....it seems to get a bee in its bonnet and thats it......

Also, if its in DTS package etc , break up the code into logical chunks with end of batch "GO" commands. This will mean separate sprocs if you run it in one sproc currently.

Cheers,

SG.

Drop table causes system to hang

I have a table in SQL 2000 that I cannot drop with either Enterprise Manager
or Query Analyzer (drop table tblName). I don't get an error message, I just
get the hour glass. I got the data out of it - I could ignore it, but I'd
rather get rid of it. I created it with a create table statement, if that
makes any difference.
It must be being used by some process somehow.
You may set its access to Restrict Access: Members of... from the
database's properties and then try dropping it again?
Or detach that database and delete its "mdf" (ndf) and "ldf" files.
Ekrem ?nsoy
"mlwallin" <mlwallin@.discussions.microsoft.com> wrote in message
news:4D3ED88B-5A01-49F5-9210-DE1572BE0DE7@.microsoft.com...
>I have a table in SQL 2000 that I cannot drop with either Enterprise
>Manager
> or Query Analyzer (drop table tblName). I don't get an error message, I
> just
> get the hour glass. I got the data out of it - I could ignore it, but I'd
> rather get rid of it. I created it with a create table statement, if that
> makes any difference.
|||Hi Ekrem,
How detaching and deleting mdf and ldf files can help to drop a table?
Thanks,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ekrem ?nsoy" wrote:

> It must be being used by some process somehow.
> You may set its access to Restrict Access: Members of... from the
> database's properties and then try dropping it again?
> Or detach that database and delete its "mdf" (ndf) and "ldf" files.
> --
> Ekrem ?nsoy
>
> "mlwallin" <mlwallin@.discussions.microsoft.com> wrote in message
> news:4D3ED88B-5A01-49F5-9210-DE1572BE0DE7@.microsoft.com...
>
|||I guess I was kinda drunk while typing that =) I probably assumed he wanted
to delete the database itself.
However it could work in terms of disconnecting processes which use the
mentioned table.
Ekrem ?nsoy
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:EC2F8E1B-A7C0-4147-BEE9-D81CAA9BCC8D@.microsoft.com...[vbcol=seagreen]
> Hi Ekrem,
> How detaching and deleting mdf and ldf files can help to drop a table?
> Thanks,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Ekrem ?nsoy" wrote:
|||How about some constructive advice?
"Ekrem ?nsoy" wrote:

> I guess I was kinda drunk while typing that =) I probably assumed he wanted
> to delete the database itself.
> However it could work in terms of disconnecting processes which use the
> mentioned table.
> --
> Ekrem ?nsoy
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:EC2F8E1B-A7C0-4147-BEE9-D81CAA9BCC8D@.microsoft.com...
>

Drop table causes system to hang

I have a table in SQL 2000 that I cannot drop with either Enterprise Manager
or Query Analyzer (drop table tblName). I don't get an error message, I just
get the hour glass. I got the data out of it - I could ignore it, but I'd
rather get rid of it. I created it with a create table statement, if that
makes any difference.It must be being used by some process somehow.
You may set its access to Restrict Access: Members of... from the
database's properties and then try dropping it again?
Or detach that database and delete its "mdf" (ndf) and "ldf" files.
--
Ekrem Ã?nsoy
"mlwallin" <mlwallin@.discussions.microsoft.com> wrote in message
news:4D3ED88B-5A01-49F5-9210-DE1572BE0DE7@.microsoft.com...
>I have a table in SQL 2000 that I cannot drop with either Enterprise
>Manager
> or Query Analyzer (drop table tblName). I don't get an error message, I
> just
> get the hour glass. I got the data out of it - I could ignore it, but I'd
> rather get rid of it. I created it with a create table statement, if that
> makes any difference.|||Hi Ekrem,
How detaching and deleting mdf and ldf files can help to drop a table?
Thanks,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ekrem Ã?nsoy" wrote:
> It must be being used by some process somehow.
> You may set its access to Restrict Access: Members of... from the
> database's properties and then try dropping it again?
> Or detach that database and delete its "mdf" (ndf) and "ldf" files.
> --
> Ekrem Ã?nsoy
>
> "mlwallin" <mlwallin@.discussions.microsoft.com> wrote in message
> news:4D3ED88B-5A01-49F5-9210-DE1572BE0DE7@.microsoft.com...
> >I have a table in SQL 2000 that I cannot drop with either Enterprise
> >Manager
> > or Query Analyzer (drop table tblName). I don't get an error message, I
> > just
> > get the hour glass. I got the data out of it - I could ignore it, but I'd
> > rather get rid of it. I created it with a create table statement, if that
> > makes any difference.
>|||I guess I was kinda drunk while typing that =) I probably assumed he wanted
to delete the database itself.
However it could work in terms of disconnecting processes which use the
mentioned table.
--
Ekrem Ã?nsoy
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:EC2F8E1B-A7C0-4147-BEE9-D81CAA9BCC8D@.microsoft.com...
> Hi Ekrem,
> How detaching and deleting mdf and ldf files can help to drop a table?
> Thanks,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Ekrem Ã?nsoy" wrote:
>> It must be being used by some process somehow.
>> You may set its access to Restrict Access: Members of... from the
>> database's properties and then try dropping it again?
>> Or detach that database and delete its "mdf" (ndf) and "ldf" files.
>> --
>> Ekrem Ã?nsoy
>>
>> "mlwallin" <mlwallin@.discussions.microsoft.com> wrote in message
>> news:4D3ED88B-5A01-49F5-9210-DE1572BE0DE7@.microsoft.com...
>> >I have a table in SQL 2000 that I cannot drop with either Enterprise
>> >Manager
>> > or Query Analyzer (drop table tblName). I don't get an error message,
>> > I
>> > just
>> > get the hour glass. I got the data out of it - I could ignore it, but
>> > I'd
>> > rather get rid of it. I created it with a create table statement, if
>> > that
>> > makes any difference.|||How about some constructive advice?
"Ekrem Ã?nsoy" wrote:
> I guess I was kinda drunk while typing that =) I probably assumed he wanted
> to delete the database itself.
> However it could work in terms of disconnecting processes which use the
> mentioned table.
> --
> Ekrem Ã?nsoy
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:EC2F8E1B-A7C0-4147-BEE9-D81CAA9BCC8D@.microsoft.com...
> >
> > Hi Ekrem,
> >
> > How detaching and deleting mdf and ldf files can help to drop a table?
> >
> > Thanks,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "Ekrem Ã?nsoy" wrote:
> >
> >> It must be being used by some process somehow.
> >>
> >> You may set its access to Restrict Access: Members of... from the
> >> database's properties and then try dropping it again?
> >>
> >> Or detach that database and delete its "mdf" (ndf) and "ldf" files.
> >>
> >> --
> >> Ekrem Ã?nsoy
> >>
> >>
> >>
> >> "mlwallin" <mlwallin@.discussions.microsoft.com> wrote in message
> >> news:4D3ED88B-5A01-49F5-9210-DE1572BE0DE7@.microsoft.com...
> >> >I have a table in SQL 2000 that I cannot drop with either Enterprise
> >> >Manager
> >> > or Query Analyzer (drop table tblName). I don't get an error message,
> >> > I
> >> > just
> >> > get the hour glass. I got the data out of it - I could ignore it, but
> >> > I'd
> >> > rather get rid of it. I created it with a create table statement, if
> >> > that
> >> > makes any difference.
> >>
>|||I'd check for blocking (sp_who, sp_who2, sp_lock etc).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mlwallin" <mlwallin@.discussions.microsoft.com> wrote in message
news:5704E6AD-6BEE-421A-98BD-2F8BD177ABB0@.microsoft.com...
> How about some constructive advice?
>
> "Ekrem Ã?nsoy" wrote:
>> I guess I was kinda drunk while typing that =) I probably assumed he wanted
>> to delete the database itself.
>> However it could work in terms of disconnecting processes which use the
>> mentioned table.
>> --
>> Ekrem Ã?nsoy
>>
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:EC2F8E1B-A7C0-4147-BEE9-D81CAA9BCC8D@.microsoft.com...
>> >
>> > Hi Ekrem,
>> >
>> > How detaching and deleting mdf and ldf files can help to drop a table?
>> >
>> > Thanks,
>> >
>> > Ben Nevarez
>> > Senior Database Administrator
>> > AIG SunAmerica
>> >
>> >
>> >
>> > "Ekrem Ã?nsoy" wrote:
>> >
>> >> It must be being used by some process somehow.
>> >>
>> >> You may set its access to Restrict Access: Members of... from the
>> >> database's properties and then try dropping it again?
>> >>
>> >> Or detach that database and delete its "mdf" (ndf) and "ldf" files.
>> >>
>> >> --
>> >> Ekrem Ã?nsoy
>> >>
>> >>
>> >>
>> >> "mlwallin" <mlwallin@.discussions.microsoft.com> wrote in message
>> >> news:4D3ED88B-5A01-49F5-9210-DE1572BE0DE7@.microsoft.com...
>> >> >I have a table in SQL 2000 that I cannot drop with either Enterprise
>> >> >Manager
>> >> > or Query Analyzer (drop table tblName). I don't get an error message,
>> >> > I
>> >> > just
>> >> > get the hour glass. I got the data out of it - I could ignore it, but
>> >> > I'd
>> >> > rather get rid of it. I created it with a create table statement, if
>> >> > that
>> >> > makes any difference.
>> >>

Drop table causes system to hang

I have a table in SQL 2000 that I cannot drop with either Enterprise Manager
or Query Analyzer (drop table tblName). I don't get an error message, I jus
t
get the hour glass. I got the data out of it - I could ignore it, but I'd
rather get rid of it. I created it with a create table statement, if that
makes any difference.It must be being used by some process somehow.
You may set its access to Restrict Access: Members of... from the
database's properties and then try dropping it again?
Or detach that database and delete its "mdf" (ndf) and "ldf" files.
Ekrem ?nsoy
"mlwallin" <mlwallin@.discussions.microsoft.com> wrote in message
news:4D3ED88B-5A01-49F5-9210-DE1572BE0DE7@.microsoft.com...
>I have a table in SQL 2000 that I cannot drop with either Enterprise
>Manager
> or Query Analyzer (drop table tblName). I don't get an error message, I
> just
> get the hour glass. I got the data out of it - I could ignore it, but I'd
> rather get rid of it. I created it with a create table statement, if that
> makes any difference.|||Hi Ekrem,
How detaching and deleting mdf and ldf files can help to drop a table?
Thanks,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ekrem ?nsoy" wrote:

> It must be being used by some process somehow.
> You may set its access to Restrict Access: Members of... from the
> database's properties and then try dropping it again?
> Or detach that database and delete its "mdf" (ndf) and "ldf" files.
> --
> Ekrem ?nsoy
>
> "mlwallin" <mlwallin@.discussions.microsoft.com> wrote in message
> news:4D3ED88B-5A01-49F5-9210-DE1572BE0DE7@.microsoft.com...
>|||I guess I was kinda drunk while typing that =) I probably assumed he wanted
to delete the database itself.
However it could work in terms of disconnecting processes which use the
mentioned table.
Ekrem ?nsoy
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:EC2F8E1B-A7C0-4147-BEE9-D81CAA9BCC8D@.microsoft.com...[vbcol=seagreen]
> Hi Ekrem,
> How detaching and deleting mdf and ldf files can help to drop a table?
> Thanks,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Ekrem ?nsoy" wrote:
>|||How about some constructive advice?
"Ekrem ?nsoy" wrote:

> I guess I was kinda drunk while typing that =) I probably assumed he wante
d
> to delete the database itself.
> However it could work in terms of disconnecting processes which use the
> mentioned table.
> --
> Ekrem ?nsoy
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:EC2F8E1B-A7C0-4147-BEE9-D81CAA9BCC8D@.microsoft.com...
>|||I'd check for blocking (sp_who, sp_who2, sp_lock etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mlwallin" <mlwallin@.discussions.microsoft.com> wrote in message
news:5704E6AD-6BEE-421A-98BD-2F8BD177ABB0@.microsoft.com...[vbcol=seagreen]
> How about some constructive advice?
>
> "Ekrem ?nsoy" wrote:
>sql

drop or delete view

hello,

im creating a sp that creates a view from a query then bcp to csv file.

my problem is that when i start the sp, it complains the sp already exists....yes it does, however, i don't if im going about this the wrong way...but i tried the following to no avail

DROP IF EXISTS v_participantTrades

now, does that syntax not work for views?

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'v_participantTrades'))

DROP VIEW v_participantTrades

|||

aah that explains the whole, create view needs to be the first statement!

thank you

|||

exuse my incompetence however,

Invalid Object name 'sys.views'.

blaaa was my ineptitude - sysobjects for my version

cheers

DROP IDENTITY ?

Is there a way to drop the Identity property of a column in a table using an
Alter Table?
I would like to do it in Query Analyzer, not EM, and I do not want to have
to create a new table and copy the data to the new table (This is how EM
performs this operation.)
I'm looking for some kind of ALTER TABLE or a system procedure. Is it
possible?
(BTW - I don't like the idea of changing the STATUS bit in syscolumns with
an UPDATE syscolumns T-SQL either.)
Thanksdrop the column and recreate it without identity property.
Ex:
create table i(i int not null identity, ii varchar(6000))
go
alter table i drop column i
go
alter table i add i int
go
Note: you can explicitly insert the values into identity column of a table by doing session level
setting of IDENTITY_INSERT.
Ex:
SET IDENTITY_INSERT <table> ON
- Vishal|||You can drop the column if you don't care about losing the
data, otherwise you may need to use EM.
Edgardo Valdez
MCSD, MCDBA, MCSE, MCP+I
http://www.edgardovaldez.us/
>--Original Message--
>Is there a way to drop the Identity property of a column
in a table using an
>Alter Table?
>I would like to do it in Query Analyzer, not EM, and I do
not want to have
>to create a new table and copy the data to the new table
(This is how EM
>performs this operation.)
>I'm looking for some kind of ALTER TABLE or a system
procedure. Is it
>possible?
>(BTW - I don't like the idea of changing the STATUS bit
in syscolumns with
>an UPDATE syscolumns T-SQL either.)
>Thanks
>
>.
>|||cw3,
There is no option in the ALTER TABLE command to drop an IDENTITY property.
If you want to preserve the data in the identity column, include a copy
step. Here's a slight modification of Vishal's solution:
create table MyTable (i int not null identity, ii varchar(6000))
go
insert MyTable values ('X')
insert MyTable values ('Y')
go
alter table MyTable add i2 int
go
update MyTable set i2 = i
go
alter table MyTable drop column i
go
alter table MyTable add i int
go
update MyTable set i = i2
go
alter table MyTable drop column i2
The main disadvantage of this approach is that the column order may not be
what you wanted, because ALTER TABLE puts new columns at the end.
To control column order, you need to do something like what Enterprise
Manager does. You can get the EM script and tweak it yourself before
applying it.
Ron
--
Ron Talmage
SQL Server MVP
"cw3" <cw@.3mc.com> wrote in message
news:ugM9gSEjDHA.1964@.TK2MSFTNGP12.phx.gbl...
> Is there a way to drop the Identity property of a column in a table using
an
> Alter Table?
> I would like to do it in Query Analyzer, not EM, and I do not want to have
> to create a new table and copy the data to the new table (This is how EM
> performs this operation.)
> I'm looking for some kind of ALTER TABLE or a system procedure. Is it
> possible?
> (BTW - I don't like the idea of changing the STATUS bit in syscolumns with
> an UPDATE syscolumns T-SQL either.)
> Thanks
>

Drop database

I cannot drop a user defined database in the following situation:
In enterprise manager, I select that database, select tables.
In query analyser, run the statement: use master, go, drop that user
database, an error said that database is currently in use.
Then I back to enterprise manager, collapse the database folder. Run the
statement in query analyser again but got the same error.
The way I get around was go back to enterprise manager, select root level or
upper level database, press refresh.
Back to query analyser and run that SQL again and it worked.
Why do I need to refresh enterprise manager after select the root level
folder or collapse the database folder ?Because EM doesn't want to do unecessary database work unless it has to. So, the connection is still
in the old database until you refresh in EM.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alan" <alanpltse@.yahoo.com.au> wrote in message news:OnzQiTEaDHA.440@.tk2msftngp13.phx.gbl...
> I cannot drop a user defined database in the following situation:
> In enterprise manager, I select that database, select tables.
> In query analyser, run the statement: use master, go, drop that user
> database, an error said that database is currently in use.
> Then I back to enterprise manager, collapse the database folder. Run the
> statement in query analyser again but got the same error.
> The way I get around was go back to enterprise manager, select root level or
> upper level database, press refresh.
> Back to query analyser and run that SQL again and it worked.
> Why do I need to refresh enterprise manager after select the root level
> folder or collapse the database folder ?
>

Drop All tables using one query

Hi all,

How do i drop all tables (with constraints) in a database using one queries?

one easy way is using the ms_foreachtable.

exec sp_msforeachtable 'Drop table ?'

Note: This sp is undocumented sp. You can use it for one time activity, but don't stick on this sp. It may be get removed with out any notification.

|||

Could not drop object 'dbo.TRN' because it is referenced by a FOREIGN KEY constraint.

Msg 3726, Level 16, State 1, Line 1

This is what i get..

|||Execute the same query multiple times|||

Would not it be easier if you drop the database?

AMB

|||

Another possible approach:

if object_id('[dbo].[sp_drop_all_tables]') is not null drop proc [dbo].[sp_drop_all_tables]

go

create proc [dbo].[sp_drop_all_tables]

@.pSchemaName sysname = null

as

set xact_abort on

set nocount on

declare

@.ParentTableSchemaName sysname,

@.ParentTableName sysname,

@.ChildTableSchemaName sysname,

@.ChildTableName sysname,

@.FKName sysname,

@.SQLCommandText nvarchar(max),

@.ErrorMessageText nvarchar(max)

if db_name() = 'master'

begin

raiserror('Cannot be run in the master database.', 16, 1)

return

end

if @.pSchemaName = 'sys'

begin

raiserror('Cannot be run for tables in the [sys] schema.', 16, 1)

return

end

select

@.ParentTableSchemaName = @.pSchemaName

begin tran

select

@.SQLCommandText = '

declare curObject cursor global static for

select

fk_po_s.name as ParentTableSchemaName,

fk_po.name as ParentTableName,

fk_co_s.name as ChildTableSchemaName,

fk_co.name as ChildTableName,

fk.name as FKName

from sys.foreign_keys as fk

join sys.objects as fk_po

on fk_po.object_id = fk.referenced_object_id

join sys.schemas as fk_po_s

on fk_po_s.schema_id = fk_po.schema_id

join sys.objects as fk_co

on fk_co.object_id = fk.parent_object_id

join sys.schemas as fk_co_s

on fk_co_s.schema_id = fk_co.schema_id

where fk_po.type = ''u''

and fk_po_s.name <> ''sys''' +

case when @.ParentTableSchemaName is null then '' else '

and fk_po_s.name = ''' + @.ParentTableSchemaName + '''' end + '

order by

fk_po_s.name,

fk_po.name,

fk_co_s.name,

fk_co.name,

fk.name'

--print @.SQLCommandText

exec(@.SQLCommandText)

open curObject

fetch next from curObject into

@.ParentTableSchemaName,

@.ParentTableName,

@.ChildTableSchemaName,

@.ChildTableName,

@.FKName

while @.@.fetch_status = 0

begin

select

@.SQLCommandText = 'alter table [' + @.ChildTableSchemaName + '].[' + @.ChildTableName +

'] drop constraint [' + @.FKName + ']'

-- print @.SQLCommandText

exec(@.SQLCommandText)

fetch next from curObject into

@.ParentTableSchemaName,

@.ParentTableName,

@.ChildTableSchemaName,

@.ChildTableName,

@.FKName

end

close curObject

deallocate curObject

select

@.ParentTableSchemaName = @.pSchemaName

select

@.SQLCommandText = '

declare curObject cursor for

select

s.name as ParentTableSchemaName,

o.name as ParentTableName

from [' + db_name() + '].sys.objects as o

join [' + db_name() + '].sys.schemas as s

on s.schema_id = o.schema_id

where o.type = ''u''

and left(o.name, 1) <> ''#''

and s.name <> ''sys''' +

case when @.ParentTableSchemaName is null then '' else '

and s.name = ''' + @.ParentTableSchemaName + '''' end + '

order by

o.name'

--print @.SQLCommandText

exec(@.SQLCommandText)

open curObject

fetch next from curObject into

@.ParentTableSchemaName,

@.ParentTableName

if @.@.fetch_status <> 0

begin

print 'No user tables exist.'

end

while @.@.fetch_status = 0

begin

select

@.SQLCommandText = 'drop table [' + @.ParentTableSchemaName + '].[' + @.ParentTableName + ']'

print @.SQLCommandText

exec(@.SQLCommandText)

fetch next from curObject into

@.ParentTableSchemaName,

@.ParentTableName

end

close curObject

deallocate curObject

commit

NormalReturn:

return 0

ErrorReturn:

if @.@.trancount > 0

begin

rollback

end

if cursor_status('global','curObject') = 1

begin

close curObject

end

if cursor_status('global','curObject') = -1

begin

deallocate curObject

end

select

@.ErrorMessageText = coalesce(@.ErrorMessageText, 'The stored procedure sp_drop_all_tables failed.')

raiserror(@.ErrorMessageText, 16, 1)

return 1

go

Ron Rice

drop all tables in database

what is the sql query to drop all tables in a database in sql server 2000

Please excuse the fact that I have not tested this!

DECLARE

@.TABLEVARCHAR(100)
DECLARE @.EXECVARCHAR(100)
DECLARE XCURSORCURSORFORSELECTNAMEFROM SYSOBJECTSWHERE XTYPE='U'
OPEN XCURSOR
FETCH XCURSORINTO @.TABLE
WHILE@.@.FETCH_STATUS= 0
BEGIN
SET @.EXEC='DROP TABLE '+ @.TABLE
PRINT @.EXEC
EXEC(@.EXEC)
FETCH XCURSORINTO @.TABLE
END
CLOSE XCURSOR
DEALLOCATE XCURSOR|||

Only possible way of doing is by droping the entire database using

drop database "Database Name" command

|||>> Only possible way of doing is by droping the entire database using drop database "Database Name" command

The command I gave should work, but it might need to be repeated because of iintertable key relationships.

|||

I ran this one in TEMPDB and it works

DECLARE @.TABLE VARCHAR(100)
DECLARE @.LINK VARCHAR(100)
DECLARE @.EXEC VARCHAR(100)
DECLARE XCURSOR CURSOR FOR SELECT T.NAME TNAME, L.NAME AS LINK
FROM SYSOBJECTS T, SYSOBJECTS L, sysforeignkeys
WHERE L.XTYPE = 'F' AND fkeyid = T.id AND constid = L.id
OPEN XCURSOR
FETCH XCURSOR INTO @.TABLE, @.LINK
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.EXEC = 'ALTER TABLE [dbo].[' + @.TABLE + '] DROP CONSTRAINT ' + @.LINK
PRINT @.EXEC
EXEC (@.EXEC)
FETCH XCURSOR INTO @.TABLE, @.LINK
END
CLOSE XCURSOR
DEALLOCATE XCURSOR
DECLARE XCURSOR CURSOR FOR SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U'
OPEN XCURSOR
FETCH XCURSOR INTO @.TABLE
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.EXEC = 'DROP TABLE ' + @.TABLE
PRINT @.EXEC
EXEC (@.EXEC)
FETCH XCURSOR INTO @.TABLE
END
CLOSE XCURSOR
DEALLOCATE XCURSOR

|||

You can run this script in Query analyzer in (Resuls to Text mode), get the script and run it. I would advise caution though, make sure you are doing what you intend to. there's no way to rollback, except to restore from a previous copy of the Db.

select

'DROP TABLE '+ name+char(10)+char(13)+'Go'

FROM

sysobjectswhere type='u'