Dropping Temporary Tables

Is it better to drop temporary tables at the end of a stored procedure?
I have heard somewhere that there can be stored proc recompilation issues which affect performance when you explicitly drop temp tables?

Will SQL Server "Clean Up" once the stored proc is out of scope or should I "do it myself".
Which method has known performance benefits?

CheersHi,
If resources arent an issue on your sqlserver you dont need to drop temps yourself. sqlserver will drop them when the sp is done.

Be careful though when working with temps, more than five updates to a temp table could force sqlserver to recomple. Any Set statements in the sp will also force sqlserver to recompile(SET NOCOUNT ON for example).

Dropping Extended Stored Proc

How do you delete a extended stored procedure in SQL 2005?
sp_dropextendedproc
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:2605012C-28F4-4822-ACA6-EC63C94A7C78@.microsoft.com...
> How do you delete a extended stored procedure in SQL 2005?
|||use
sp_dropextendedproc
see following link for more detail
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
vinu
"John Oberlin" wrote:

> How do you delete a extended stored procedure in SQL 2005?
|||I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try it
in SQL 2005 because of this comment in
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
"In SQL Server 2005, sp_dropextendedproc does not drop system extended
stored procedures. Instead, the system administrator should deny EXECUTE
permission on the extended stored procedure to the public role. In SQL Server
2000, sp_dropextendedproc could be used to drop any extended stored
procedure. "
John
|||The documentation is pretty clear on the subject. Don't use this proc to drop *system* extended
procs. For this, DENY execute permissions instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:B90D7CC5-D4CC-4D2A-B75A-445E40AF6074@.microsoft.com...
>I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try it
> in SQL 2005 because of this comment in
> http://msdn2.microsoft.com/en-us/library/ms164755.aspx
> "In SQL Server 2005, sp_dropextendedproc does not drop system extended
> stored procedures. Instead, the system administrator should deny EXECUTE
> permission on the extended stored procedure to the public role. In SQL Server
> 2000, sp_dropextendedproc could be used to drop any extended stored
> procedure. "
> John
>
|||plus...sp_dropextendedproc can be run only in the master database and the
extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
Any help would be greatly appreciated.
Thanks,
John
|||Extended procedures can only live in master. I checked my 2005 installation, and I have an
xp_sendmail in master and none in msdb. If you have something called xp_sendmail in msdb, then it is
a regular stored procedure, not an extended stored procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:EE66AD24-622F-4DE8-971C-DAFD43C286D0@.microsoft.com...
> plus...sp_dropextendedproc can be run only in the master database and the
> extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
> Any help would be greatly appreciated.
> Thanks,
> John
>
|||xp_sendmail is a system extended stored proc in master. Is there a way to
delete it? Or alternatively, is there a way to alter it?
Thanks,
John
P.S. my apologies, it is sp_send_dbmail that is in msdb
sql

Dropping Extended Stored Proc

How do you delete a extended stored procedure in SQL 2005?sp_dropextendedproc
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:2605012C-28F4-4822-ACA6-EC63C94A7C78@.microsoft.com...
> How do you delete a extended stored procedure in SQL 2005?|||use
sp_dropextendedproc
see following link for more detail
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
vinu
"John Oberlin" wrote:
> How do you delete a extended stored procedure in SQL 2005?|||I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try it
in SQL 2005 because of this comment in
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
"In SQL Server 2005, sp_dropextendedproc does not drop system extended
stored procedures. Instead, the system administrator should deny EXECUTE
permission on the extended stored procedure to the public role. In SQL Server
2000, sp_dropextendedproc could be used to drop any extended stored
procedure. "
John|||The documentation is pretty clear on the subject. Don't use this proc to drop *system* extended
procs. For this, DENY execute permissions instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:B90D7CC5-D4CC-4D2A-B75A-445E40AF6074@.microsoft.com...
>I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try it
> in SQL 2005 because of this comment in
> http://msdn2.microsoft.com/en-us/library/ms164755.aspx
> "In SQL Server 2005, sp_dropextendedproc does not drop system extended
> stored procedures. Instead, the system administrator should deny EXECUTE
> permission on the extended stored procedure to the public role. In SQL Server
> 2000, sp_dropextendedproc could be used to drop any extended stored
> procedure. "
> John
>|||plus...sp_dropextendedproc can be run only in the master database and the
extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
Any help would be greatly appreciated.
Thanks,
John|||Extended procedures can only live in master. I checked my 2005 installation, and I have an
xp_sendmail in master and none in msdb. If you have something called xp_sendmail in msdb, then it is
a regular stored procedure, not an extended stored procedure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:EE66AD24-622F-4DE8-971C-DAFD43C286D0@.microsoft.com...
> plus...sp_dropextendedproc can be run only in the master database and the
> extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
> Any help would be greatly appreciated.
> Thanks,
> John
>|||xp_sendmail is a system extended stored proc in master. Is there a way to
delete it? Or alternatively, is there a way to alter it?
Thanks,
John
P.S. my apologies, it is sp_send_dbmail that is in msdb

Dropping Extended Stored Proc

How do you delete a extended stored procedure in SQL 2005?sp_dropextendedproc
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:2605012C-28F4-4822-ACA6-EC63C94A7C78@.microsoft.com...
> How do you delete a extended stored procedure in SQL 2005?|||use
sp_dropextendedproc
see following link for more detail
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
vinu
"John Oberlin" wrote:

> How do you delete a extended stored procedure in SQL 2005?|||I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try i
t
in SQL 2005 because of this comment in
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
"In SQL Server 2005, sp_dropextendedproc does not drop system extended
stored procedures. Instead, the system administrator should deny EXECUTE
permission on the extended stored procedure to the public role. In SQL Serve
r
2000, sp_dropextendedproc could be used to drop any extended stored
procedure. "
John|||The documentation is pretty clear on the subject. Don't use this proc to dro
p *system* extended
procs. For this, DENY execute permissions instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:B90D7CC5-D4CC-4D2A-B75A-445E40AF6074@.microsoft.com...
>I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try
it
> in SQL 2005 because of this comment in
> http://msdn2.microsoft.com/en-us/library/ms164755.aspx
> "In SQL Server 2005, sp_dropextendedproc does not drop system extended
> stored procedures. Instead, the system administrator should deny EXECUTE
> permission on the extended stored procedure to the public role. In SQL Ser
ver
> 2000, sp_dropextendedproc could be used to drop any extended stored
> procedure. "
> John
>|||plus...sp_dropextendedproc can be run only in the master database and the
extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
Any help would be greatly appreciated.
Thanks,
John|||Extended procedures can only live in master. I checked my 2005 installation,
and I have an
xp_sendmail in master and none in msdb. If you have something called xp_send
mail in msdb, then it is
a regular stored procedure, not an extended stored procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:EE66AD24-622F-4DE8-971C-DAFD43C286D0@.microsoft.com...
> plus...sp_dropextendedproc can be run only in the master database and the
> extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
> Any help would be greatly appreciated.
> Thanks,
> John
>|||xp_sendmail is a system extended stored proc in master. Is there a way to
delete it? Or alternatively, is there a way to alter it?
Thanks,
John
P.S. my apologies, it is sp_send_dbmail that is in msdb

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
> --
>

drop. temp. table proc.

CREATE PROCEDURE DT @.TEMP_TABLE_NAME SYSNAME
AS
DECLARE @.STATEMENT VARCHAR(8000)
SET @.STATEMENT ='DROP TABLE '+@.TEMP_TABLE_NAME
IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME= @.TEMP_TABLE_NAME)
BEGIN
EXEC(@.STATEMENT)
END
SELECT *
INTO #AA
FROM a_table
DT '#AA'
SELECT * FROM #AA--the table #AA is still existing.
How can I change the procedure to enable dropping.I think that this line is where your proc is going wrong:
IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME= @.TEMP_TABLE_NAME)
The object name in tempdbs sysobjects table will be
#AA_________________somethinghere
Therefore, you'd have to change your = to LIKE, something like this:
@.TEMP_TABLE_NAME + '___%'
To check for an object's existence, I always try to retrieve the object's ID
using OBJECT_ID('objectname') function. If a non-null value is returned,
delete the object.
IF OBJECT_ID('tempdb..' + @.TEMP_TABLE_NAME) IS NOT NULL
I wouldn't normally recommend dynamic SQL due to the risk of a SQL injection
attack, but if it's only for your own use?
Dan.
"Alur" <Alur@.discussions.microsoft.com> wrote in message
news:8A70D6C8-790A-4EA9-9D61-DDE408345A3E@.microsoft.com...
> CREATE PROCEDURE DT @.TEMP_TABLE_NAME SYSNAME
> AS
> DECLARE @.STATEMENT VARCHAR(8000)
> SET @.STATEMENT ='DROP TABLE '+@.TEMP_TABLE_NAME
> IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME=
@.TEMP_TABLE_NAME)
> BEGIN
> EXEC(@.STATEMENT)
> END
> SELECT *
> INTO #AA
> FROM a_table
> DT '#AA'
> SELECT * FROM #AA--the table #AA is still existing.
> How can I change the procedure to enable dropping.
>|||On Mon, 15 Aug 2005 12:52:41 +0100, Daniel Doyle wrote:

>The object name in tempdbs sysobjects table will be
>#AA_________________somethinghere
>Therefore, you'd have to change your = to LIKE, something like this:
>@.TEMP_TABLE_NAME + '___%'
Hi Daniel,
I think that you meant to write
LIKE @.TEMP_TABLE_NAME + '[_][_][_]%'
or
LIKE @.TEMP_TABLE_NAME + '\_\_\_%' ESCAPE ''
The _ character in a LIKE pattern will match any single character.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you.
"Daniel Doyle" wrote:

> I think that this line is where your proc is going wrong:
> IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME= @.TEMP_TABLE_NAME
)
> The object name in tempdbs sysobjects table will be
> #AA_________________somethinghere
> Therefore, you'd have to change your = to LIKE, something like this:
> @.TEMP_TABLE_NAME + '___%'
> To check for an object's existence, I always try to retrieve the object's
ID
> using OBJECT_ID('objectname') function. If a non-null value is returned,
> delete the object.
> IF OBJECT_ID('tempdb..' + @.TEMP_TABLE_NAME) IS NOT NULL
> I wouldn't normally recommend dynamic SQL due to the risk of a SQL injecti
on
> attack, but if it's only for your own use?
> Dan.
> "Alur" <Alur@.discussions.microsoft.com> wrote in message
> news:8A70D6C8-790A-4EA9-9D61-DDE408345A3E@.microsoft.com...
> @.TEMP_TABLE_NAME)
>
>|||Yes, of course you are correct Hugo, it slippled my mind that _ is a
wildcard character.
Thanks. Dan.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:vbt1g1tbdoovaq2tt15vahkm5d0qhevn4n@.
4ax.com...
> On Mon, 15 Aug 2005 12:52:41 +0100, Daniel Doyle wrote:
>
> Hi Daniel,
> I think that you meant to write
> LIKE @.TEMP_TABLE_NAME + '[_][_][_]%'
> or
> LIKE @.TEMP_TABLE_NAME + '\_\_\_%' ESCAPE ''
> The _ character in a LIKE pattern will match any single character.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Drop Stored Procedure causing Dropped Tables

Hey guys, has anyone ever seen this happen:

Try to move stored proc from one DB to another using DTS, errors on create proc. Create proc manually.

Three tables referenced by that stored proc have been dropped and re-created with the same table structure.

I'm not 100% certain that it happened at exactly the same time, but it seems to be around the same time. Any ideas? Anyone seen this happen before?You will have to check the options you picked for the Transfer task in your DTS package. Did you ask it to move dependent objects also? For more help on the DTS package/tasks, please post in the SQL Server Integration Services forum.|||It was selected for dependent objects, but those tables are not dependent on the stored proc. As far as I know, the DTS method I used was the same as using DROP PROCEDURE, since it failed after the drop.|||I don't know how the DTS task determines dependencies. If it uses say sp_depends SP then you can check by running the SP for your SP to see the dependencies. Note, that this SP only gives immediate dependencies. If this doesn't help find how DTS determines dependencies then ask in the SSIS forum or run your package and trace the calls to SQL Server.

Drop index in a stored proc in a different db

Hi,
I want to drop an index in a stored procedure. The index is in a
different database though so I am having lots of problems.
I have a Database called Db1. The table is called table1 and the
index is called myindex. The stored procedure is in Db2. I want to
drop the index in Db1 from the stored procedure is in Db2.
USE statements cannot be used in a stored procedure so I am not able
to have DB2 used for the DROP command. I tried to drop a fully
qualified index name such as
DROP INDEX Db1.table1.myindex
but I get the error..
Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
Cannot drop the index 'Db1.Table1.myindex', because it does not exist
in the system catalog.
I want to have the stored procedures in a different db becaue Db1 gets
replaced often and I want to have all the stored procedures in a
separate database (Db2).
What am I doing wrong?
Thanks!
Jim
Use sp_executesql, with a DB prefix:
exec Db1.dbo.sp_executesql N'drop index table1.myindex'
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jim" <jshain@.datamann.com> wrote in message
news:1182957304.026319.146270@.k29g2000hsd.googlegr oups.com...
Hi,
I want to drop an index in a stored procedure. The index is in a
different database though so I am having lots of problems.
I have a Database called Db1. The table is called table1 and the
index is called myindex. The stored procedure is in Db2. I want to
drop the index in Db1 from the stored procedure is in Db2.
USE statements cannot be used in a stored procedure so I am not able
to have DB2 used for the DROP command. I tried to drop a fully
qualified index name such as
DROP INDEX Db1.table1.myindex
but I get the error..
Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
Cannot drop the index 'Db1.Table1.myindex', because it does not exist
in the system catalog.
I want to have the stored procedures in a different db becaue Db1 gets
replaced often and I want to have all the stored procedures in a
separate database (Db2).
What am I doing wrong?
Thanks!
Jim
|||Fantastic!
Thanks,
Jim
On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> Use sp_executesql, with a DB prefix:
> exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "Jim" <jsh...@.datamann.com> wrote in message
> news:1182957304.026319.146270@.k29g2000hsd.googlegr oups.com...
> Hi,
> I want to drop an index in a stored procedure. The index is in a
> different database though so I am having lots of problems.
> I have a Database called Db1. The table is called table1 and the
> index is called myindex. The stored procedure is in Db2. I want to
> drop the index in Db1 from the stored procedure is in Db2.
> USE statements cannot be used in a stored procedure so I am not able
> to have DB2 used for the DROP command. I tried to drop a fully
> qualified index name such as
> DROP INDEX Db1.table1.myindex
> but I get the error..
> Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> in the system catalog.
> I want to have the stored procedures in a different db becaue Db1 gets
> replaced often and I want to have all the stored procedures in a
> separate database (Db2).
> What am I doing wrong?
> Thanks!
> Jim
|||That really works well. Thanks again!
I also have another problem...
Before I drop the index I want to test to see if it is there. How do
I do that?
I am also dropping tables. I can drop the table without a problem,
but I want to test to see if the table exists befopre I drop it.
Thanks!
Jim
On Jun 27, 12:00 pm, Jim <jsh...@.datamann.com> wrote:
> Fantastic!
> Thanks,
> Jim
> On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
>
>
>
>
>
>
>
>
>
> - Show quoted text -
|||Try:
exec Db1.dbo.sp_executesql N'
if indexproperty (object_id (''table1''), ''myindex'', ''IndexID'') is not
null
drop index table1.myindex
'
Not the use of double single-quotes. Just cut and paste and you'll see what
I mean.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jim" <jshain@.datamann.com> wrote in message
news:1182960998.331625.271390@.k29g2000hsd.googlegr oups.com...
That really works well. Thanks again!
I also have another problem...
Before I drop the index I want to test to see if it is there. How do
I do that?
I am also dropping tables. I can drop the table without a problem,
but I want to test to see if the table exists befopre I drop it.
Thanks!
Jim
On Jun 27, 12:00 pm, Jim <jsh...@.datamann.com> wrote:
> Fantastic!
> Thanks,
> Jim
> On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
>
>
>
>
>
>
>
>
>
> - Show quoted text -
|||Typo:
"*Note* the use of double single-quotes. Just cut and paste and you'll see
what
I mean."
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:Oop7vlOuHHA.4612@.TK2MSFTNGP04.phx.gbl...
Try:
exec Db1.dbo.sp_executesql N'
if indexproperty (object_id (''table1''), ''myindex'', ''IndexID'') is not
null
drop index table1.myindex
'
Not the use of double single-quotes. Just cut and paste and you'll see what
I mean.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jim" <jshain@.datamann.com> wrote in message
news:1182960998.331625.271390@.k29g2000hsd.googlegr oups.com...
That really works well. Thanks again!
I also have another problem...
Before I drop the index I want to test to see if it is there. How do
I do that?
I am also dropping tables. I can drop the table without a problem,
but I want to test to see if the table exists befopre I drop it.
Thanks!
Jim
On Jun 27, 12:00 pm, Jim <jsh...@.datamann.com> wrote:
> Fantastic!
> Thanks,
> Jim
> On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
>
>
>
>
>
>
>
>
>
> - Show quoted text -
|||Thanks!
On Jun 27, 2:34 pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> Typo:
> "*Note* the use of double single-quotes. Just cut and paste and you'll see
> what
> I mean."
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote in message
> news:Oop7vlOuHHA.4612@.TK2MSFTNGP04.phx.gbl...
> Try:
> exec Db1.dbo.sp_executesql N'
> if indexproperty (object_id (''table1''), ''myindex'', ''IndexID'') is not
> null
> drop index table1.myindex
> '
> Not the use of double single-quotes. Just cut and paste and you'll see what
> I mean.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "Jim" <jsh...@.datamann.com> wrote in message
> news:1182960998.331625.271390@.k29g2000hsd.googlegr oups.com...
> That really works well. Thanks again!
> I also have another problem...
> Before I drop the index I want to test to see if it is there. How do
> I do that?
> I am also dropping tables. I can drop the table without a problem,
> but I want to test to see if the table exists befopre I drop it.
> Thanks!
> Jim
> On Jun 27, 12:00 pm, Jim <jsh...@.datamann.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
|||I haven't ever seen that particular syntax for sp_executesql. Yet another
useful tidbit from the forums!!
TheSQLGuru
President
Indicium Resources, Inc.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uV4vU9MuHHA.4412@.TK2MSFTNGP02.phx.gbl...
> Use sp_executesql, with a DB prefix:
> exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Jim" <jshain@.datamann.com> wrote in message
> news:1182957304.026319.146270@.k29g2000hsd.googlegr oups.com...
> Hi,
> I want to drop an index in a stored procedure. The index is in a
> different database though so I am having lots of problems.
> I have a Database called Db1. The table is called table1 and the
> index is called myindex. The stored procedure is in Db2. I want to
> drop the index in Db1 from the stored procedure is in Db2.
> USE statements cannot be used in a stored procedure so I am not able
> to have DB2 used for the DROP command. I tried to drop a fully
> qualified index name such as
> DROP INDEX Db1.table1.myindex
> but I get the error..
> Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> in the system catalog.
> I want to have the stored procedures in a different db becaue Db1 gets
> replaced often and I want to have all the stored procedures in a
> separate database (Db2).
> What am I doing wrong?
> Thanks!
> Jim
>
|||It's helped me out in my more lucid moments. ;-)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:u2esZrZuHHA.2272@.TK2MSFTNGP04.phx.gbl...
I haven't ever seen that particular syntax for sp_executesql. Yet another
useful tidbit from the forums!!
TheSQLGuru
President
Indicium Resources, Inc.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uV4vU9MuHHA.4412@.TK2MSFTNGP02.phx.gbl...
> Use sp_executesql, with a DB prefix:
> exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Jim" <jshain@.datamann.com> wrote in message
> news:1182957304.026319.146270@.k29g2000hsd.googlegr oups.com...
> Hi,
> I want to drop an index in a stored procedure. The index is in a
> different database though so I am having lots of problems.
> I have a Database called Db1. The table is called table1 and the
> index is called myindex. The stored procedure is in Db2. I want to
> drop the index in Db1 from the stored procedure is in Db2.
> USE statements cannot be used in a stored procedure so I am not able
> to have DB2 used for the DROP command. I tried to drop a fully
> qualified index name such as
> DROP INDEX Db1.table1.myindex
> but I get the error..
> Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> in the system catalog.
> I want to have the stored procedures in a different db becaue Db1 gets
> replaced often and I want to have all the stored procedures in a
> separate database (Db2).
> What am I doing wrong?
> Thanks!
> Jim
>

Drop index in a stored proc in a different db

Hi,
I want to drop an index in a stored procedure. The index is in a
different database though so I am having lots of problems.
I have a Database called Db1. The table is called table1 and the
index is called myindex. The stored procedure is in Db2. I want to
drop the index in Db1 from the stored procedure is in Db2.
USE statements cannot be used in a stored procedure so I am not able
to have DB2 used for the DROP command. I tried to drop a fully
qualified index name such as
DROP INDEX Db1.table1.myindex
but I get the error..
Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
Cannot drop the index 'Db1.Table1.myindex', because it does not exist
in the system catalog.
I want to have the stored procedures in a different db becaue Db1 gets
replaced often and I want to have all the stored procedures in a
separate database (Db2).
What am I doing wrong?
Thanks!
JimUse sp_executesql, with a DB prefix:
exec Db1.dbo.sp_executesql N'drop index table1.myindex'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jim" <jshain@.datamann.com> wrote in message
news:1182957304.026319.146270@.k29g2000hsd.googlegroups.com...
Hi,
I want to drop an index in a stored procedure. The index is in a
different database though so I am having lots of problems.
I have a Database called Db1. The table is called table1 and the
index is called myindex. The stored procedure is in Db2. I want to
drop the index in Db1 from the stored procedure is in Db2.
USE statements cannot be used in a stored procedure so I am not able
to have DB2 used for the DROP command. I tried to drop a fully
qualified index name such as
DROP INDEX Db1.table1.myindex
but I get the error..
Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
Cannot drop the index 'Db1.Table1.myindex', because it does not exist
in the system catalog.
I want to have the stored procedures in a different db becaue Db1 gets
replaced often and I want to have all the stored procedures in a
separate database (Db2).
What am I doing wrong?
Thanks!
Jim|||Fantastic!
Thanks,
Jim
On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> Use sp_executesql, with a DB prefix:
> exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "Jim" <jsh...@.datamann.com> wrote in message
> news:1182957304.026319.146270@.k29g2000hsd.googlegroups.com...
> Hi,
> I want to drop an index in a stored procedure. The index is in a
> different database though so I am having lots of problems.
> I have a Database called Db1. The table is called table1 and the
> index is called myindex. The stored procedure is in Db2. I want to
> drop the index in Db1 from the stored procedure is in Db2.
> USE statements cannot be used in a stored procedure so I am not able
> to have DB2 used for the DROP command. I tried to drop a fully
> qualified index name such as
> DROP INDEX Db1.table1.myindex
> but I get the error..
> Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> in the system catalog.
> I want to have the stored procedures in a different db becaue Db1 gets
> replaced often and I want to have all the stored procedures in a
> separate database (Db2).
> What am I doing wrong?
> Thanks!
> Jim|||That really works well. Thanks again!
I also have another problem...
Before I drop the index I want to test to see if it is there. How do
I do that?
I am also dropping tables. I can drop the table without a problem,
but I want to test to see if the table exists befopre I drop it.
Thanks!
Jim
On Jun 27, 12:00 pm, Jim <jsh...@.datamann.com> wrote:
> Fantastic!
> Thanks,
> Jim
> On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -|||Try:
exec Db1.dbo.sp_executesql N'
if indexproperty (object_id (''table1''), ''myindex'', ''IndexID'') is not
null
drop index table1.myindex
'
Not the use of double single-quotes. Just cut and paste and you'll see what
I mean.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jim" <jshain@.datamann.com> wrote in message
news:1182960998.331625.271390@.k29g2000hsd.googlegroups.com...
That really works well. Thanks again!
I also have another problem...
Before I drop the index I want to test to see if it is there. How do
I do that?
I am also dropping tables. I can drop the table without a problem,
but I want to test to see if the table exists befopre I drop it.
Thanks!
Jim
On Jun 27, 12:00 pm, Jim <jsh...@.datamann.com> wrote:
> Fantastic!
> Thanks,
> Jim
> On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -|||Typo:
"*Note* the use of double single-quotes. Just cut and paste and you'll see
what
I mean."
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:Oop7vlOuHHA.4612@.TK2MSFTNGP04.phx.gbl...
Try:
exec Db1.dbo.sp_executesql N'
if indexproperty (object_id (''table1''), ''myindex'', ''IndexID'') is not
null
drop index table1.myindex
'
Not the use of double single-quotes. Just cut and paste and you'll see what
I mean.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jim" <jshain@.datamann.com> wrote in message
news:1182960998.331625.271390@.k29g2000hsd.googlegroups.com...
That really works well. Thanks again!
I also have another problem...
Before I drop the index I want to test to see if it is there. How do
I do that?
I am also dropping tables. I can drop the table without a problem,
but I want to test to see if the table exists befopre I drop it.
Thanks!
Jim
On Jun 27, 12:00 pm, Jim <jsh...@.datamann.com> wrote:
> Fantastic!
> Thanks,
> Jim
> On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -|||Thanks!
On Jun 27, 2:34 pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> Typo:
> "*Note* the use of double single-quotes. Just cut and paste and you'll se
e
> what
> I mean."
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote in message
> news:Oop7vlOuHHA.4612@.TK2MSFTNGP04.phx.gbl...
> Try:
> exec Db1.dbo.sp_executesql N'
> if indexproperty (object_id (''table1''), ''myindex'', ''IndexID'') is not
> null
> drop index table1.myindex
> '
> Not the use of double single-quotes. Just cut and paste and you'll see wh
at
> I mean.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "Jim" <jsh...@.datamann.com> wrote in message
> news:1182960998.331625.271390@.k29g2000hsd.googlegroups.com...
> That really works well. Thanks again!
> I also have another problem...
> Before I drop the index I want to test to see if it is there. How do
> I do that?
> I am also dropping tables. I can drop the table without a problem,
> but I want to test to see if the table exists befopre I drop it.
> Thanks!
> Jim
> On Jun 27, 12:00 pm, Jim <jsh...@.datamann.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -|||I haven't ever seen that particular syntax for sp_executesql. Yet another
useful tidbit from the forums!!
TheSQLGuru
President
Indicium Resources, Inc.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uV4vU9MuHHA.4412@.TK2MSFTNGP02.phx.gbl...
> Use sp_executesql, with a DB prefix:
> exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Jim" <jshain@.datamann.com> wrote in message
> news:1182957304.026319.146270@.k29g2000hsd.googlegroups.com...
> Hi,
> I want to drop an index in a stored procedure. The index is in a
> different database though so I am having lots of problems.
> I have a Database called Db1. The table is called table1 and the
> index is called myindex. The stored procedure is in Db2. I want to
> drop the index in Db1 from the stored procedure is in Db2.
> USE statements cannot be used in a stored procedure so I am not able
> to have DB2 used for the DROP command. I tried to drop a fully
> qualified index name such as
> DROP INDEX Db1.table1.myindex
> but I get the error..
> Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> in the system catalog.
> I want to have the stored procedures in a different db becaue Db1 gets
> replaced often and I want to have all the stored procedures in a
> separate database (Db2).
> What am I doing wrong?
> Thanks!
> Jim
>|||It's helped me out in my more lucid moments. ;-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:u2esZrZuHHA.2272@.TK2MSFTNGP04.phx.gbl...
I haven't ever seen that particular syntax for sp_executesql. Yet another
useful tidbit from the forums!!
TheSQLGuru
President
Indicium Resources, Inc.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uV4vU9MuHHA.4412@.TK2MSFTNGP02.phx.gbl...
> Use sp_executesql, with a DB prefix:
> exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Jim" <jshain@.datamann.com> wrote in message
> news:1182957304.026319.146270@.k29g2000hsd.googlegroups.com...
> Hi,
> I want to drop an index in a stored procedure. The index is in a
> different database though so I am having lots of problems.
> I have a Database called Db1. The table is called table1 and the
> index is called myindex. The stored procedure is in Db2. I want to
> drop the index in Db1 from the stored procedure is in Db2.
> USE statements cannot be used in a stored procedure so I am not able
> to have DB2 used for the DROP command. I tried to drop a fully
> qualified index name such as
> DROP INDEX Db1.table1.myindex
> but I get the error..
> Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> in the system catalog.
> I want to have the stored procedures in a different db becaue Db1 gets
> replaced often and I want to have all the stored procedures in a
> separate database (Db2).
> What am I doing wrong?
> Thanks!
> Jim
>

Drop index in a stored proc in a different db

Hi,
I want to drop an index in a stored procedure. The index is in a
different database though so I am having lots of problems.
I have a Database called Db1. The table is called table1 and the
index is called myindex. The stored procedure is in Db2. I want to
drop the index in Db1 from the stored procedure is in Db2.
USE statements cannot be used in a stored procedure so I am not able
to have DB2 used for the DROP command. I tried to drop a fully
qualified index name such as
DROP INDEX Db1.table1.myindex
but I get the error..
Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
Cannot drop the index 'Db1.Table1.myindex', because it does not exist
in the system catalog.
I want to have the stored procedures in a different db becaue Db1 gets
replaced often and I want to have all the stored procedures in a
separate database (Db2).
What am I doing wrong?
Thanks!
JimUse sp_executesql, with a DB prefix:
exec Db1.dbo.sp_executesql N'drop index table1.myindex'
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jim" <jshain@.datamann.com> wrote in message
news:1182957304.026319.146270@.k29g2000hsd.googlegroups.com...
Hi,
I want to drop an index in a stored procedure. The index is in a
different database though so I am having lots of problems.
I have a Database called Db1. The table is called table1 and the
index is called myindex. The stored procedure is in Db2. I want to
drop the index in Db1 from the stored procedure is in Db2.
USE statements cannot be used in a stored procedure so I am not able
to have DB2 used for the DROP command. I tried to drop a fully
qualified index name such as
DROP INDEX Db1.table1.myindex
but I get the error..
Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
Cannot drop the index 'Db1.Table1.myindex', because it does not exist
in the system catalog.
I want to have the stored procedures in a different db becaue Db1 gets
replaced often and I want to have all the stored procedures in a
separate database (Db2).
What am I doing wrong?
Thanks!
Jim|||Fantastic!
Thanks,
Jim
On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> Use sp_executesql, with a DB prefix:
> exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "Jim" <jsh...@.datamann.com> wrote in message
> news:1182957304.026319.146270@.k29g2000hsd.googlegroups.com...
> Hi,
> I want to drop an index in a stored procedure. The index is in a
> different database though so I am having lots of problems.
> I have a Database called Db1. The table is called table1 and the
> index is called myindex. The stored procedure is in Db2. I want to
> drop the index in Db1 from the stored procedure is in Db2.
> USE statements cannot be used in a stored procedure so I am not able
> to have DB2 used for the DROP command. I tried to drop a fully
> qualified index name such as
> DROP INDEX Db1.table1.myindex
> but I get the error..
> Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> in the system catalog.
> I want to have the stored procedures in a different db becaue Db1 gets
> replaced often and I want to have all the stored procedures in a
> separate database (Db2).
> What am I doing wrong?
> Thanks!
> Jim|||That really works well. Thanks again!
I also have another problem...
Before I drop the index I want to test to see if it is there. How do
I do that?
I am also dropping tables. I can drop the table without a problem,
but I want to test to see if the table exists befopre I drop it.
Thanks!
Jim
On Jun 27, 12:00 pm, Jim <jsh...@.datamann.com> wrote:
> Fantastic!
> Thanks,
> Jim
> On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
>
> > Use sp_executesql, with a DB prefix:
> > exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> > --
> > Tom
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> > "Jim" <jsh...@.datamann.com> wrote in message
> >news:1182957304.026319.146270@.k29g2000hsd.googlegroups.com...
> > Hi,
> > I want to drop an index in a stored procedure. The index is in a
> > different database though so I am having lots of problems.
> > I have a Database called Db1. The table is called table1 and the
> > index is called myindex. The stored procedure is in Db2. I want to
> > drop the index in Db1 from the stored procedure is in Db2.
> > USE statements cannot be used in a stored procedure so I am not able
> > to have DB2 used for the DROP command. I tried to drop a fully
> > qualified index name such as
> > DROP INDEX Db1.table1.myindex
> > but I get the error..
> > Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> > Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> > in the system catalog.
> > I want to have the stored procedures in a different db becaue Db1 gets
> > replaced often and I want to have all the stored procedures in a
> > separate database (Db2).
> > What am I doing wrong?
> > Thanks!
> > Jim- Hide quoted text -
> - Show quoted text -|||Try:
exec Db1.dbo.sp_executesql N'
if indexproperty (object_id (''table1''), ''myindex'', ''IndexID'') is not
null
drop index table1.myindex
'
Not the use of double single-quotes. Just cut and paste and you'll see what
I mean.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jim" <jshain@.datamann.com> wrote in message
news:1182960998.331625.271390@.k29g2000hsd.googlegroups.com...
That really works well. Thanks again!
I also have another problem...
Before I drop the index I want to test to see if it is there. How do
I do that?
I am also dropping tables. I can drop the table without a problem,
but I want to test to see if the table exists befopre I drop it.
Thanks!
Jim
On Jun 27, 12:00 pm, Jim <jsh...@.datamann.com> wrote:
> Fantastic!
> Thanks,
> Jim
> On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
>
> > Use sp_executesql, with a DB prefix:
> > exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> > --
> > Tom
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> > "Jim" <jsh...@.datamann.com> wrote in message
> >news:1182957304.026319.146270@.k29g2000hsd.googlegroups.com...
> > Hi,
> > I want to drop an index in a stored procedure. The index is in a
> > different database though so I am having lots of problems.
> > I have a Database called Db1. The table is called table1 and the
> > index is called myindex. The stored procedure is in Db2. I want to
> > drop the index in Db1 from the stored procedure is in Db2.
> > USE statements cannot be used in a stored procedure so I am not able
> > to have DB2 used for the DROP command. I tried to drop a fully
> > qualified index name such as
> > DROP INDEX Db1.table1.myindex
> > but I get the error..
> > Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> > Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> > in the system catalog.
> > I want to have the stored procedures in a different db becaue Db1 gets
> > replaced often and I want to have all the stored procedures in a
> > separate database (Db2).
> > What am I doing wrong?
> > Thanks!
> > Jim- Hide quoted text -
> - Show quoted text -|||Typo:
"*Note* the use of double single-quotes. Just cut and paste and you'll see
what
I mean."
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:Oop7vlOuHHA.4612@.TK2MSFTNGP04.phx.gbl...
Try:
exec Db1.dbo.sp_executesql N'
if indexproperty (object_id (''table1''), ''myindex'', ''IndexID'') is not
null
drop index table1.myindex
'
Not the use of double single-quotes. Just cut and paste and you'll see what
I mean.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jim" <jshain@.datamann.com> wrote in message
news:1182960998.331625.271390@.k29g2000hsd.googlegroups.com...
That really works well. Thanks again!
I also have another problem...
Before I drop the index I want to test to see if it is there. How do
I do that?
I am also dropping tables. I can drop the table without a problem,
but I want to test to see if the table exists befopre I drop it.
Thanks!
Jim
On Jun 27, 12:00 pm, Jim <jsh...@.datamann.com> wrote:
> Fantastic!
> Thanks,
> Jim
> On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
>
> > Use sp_executesql, with a DB prefix:
> > exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> > --
> > Tom
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> > "Jim" <jsh...@.datamann.com> wrote in message
> >news:1182957304.026319.146270@.k29g2000hsd.googlegroups.com...
> > Hi,
> > I want to drop an index in a stored procedure. The index is in a
> > different database though so I am having lots of problems.
> > I have a Database called Db1. The table is called table1 and the
> > index is called myindex. The stored procedure is in Db2. I want to
> > drop the index in Db1 from the stored procedure is in Db2.
> > USE statements cannot be used in a stored procedure so I am not able
> > to have DB2 used for the DROP command. I tried to drop a fully
> > qualified index name such as
> > DROP INDEX Db1.table1.myindex
> > but I get the error..
> > Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> > Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> > in the system catalog.
> > I want to have the stored procedures in a different db becaue Db1 gets
> > replaced often and I want to have all the stored procedures in a
> > separate database (Db2).
> > What am I doing wrong?
> > Thanks!
> > Jim- Hide quoted text -
> - Show quoted text -|||Thanks!
On Jun 27, 2:34 pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> Typo:
> "*Note* the use of double single-quotes. Just cut and paste and you'll see
> what
> I mean."
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote in message
> news:Oop7vlOuHHA.4612@.TK2MSFTNGP04.phx.gbl...
> Try:
> exec Db1.dbo.sp_executesql N'
> if indexproperty (object_id (''table1''), ''myindex'', ''IndexID'') is not
> null
> drop index table1.myindex
> '
> Not the use of double single-quotes. Just cut and paste and you'll see what
> I mean.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "Jim" <jsh...@.datamann.com> wrote in message
> news:1182960998.331625.271390@.k29g2000hsd.googlegroups.com...
> That really works well. Thanks again!
> I also have another problem...
> Before I drop the index I want to test to see if it is there. How do
> I do that?
> I am also dropping tables. I can drop the table without a problem,
> but I want to test to see if the table exists befopre I drop it.
> Thanks!
> Jim
> On Jun 27, 12:00 pm, Jim <jsh...@.datamann.com> wrote:
>
> > Fantastic!
> > Thanks,
> > Jim
> > On Jun 27, 11:24 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> > > Use sp_executesql, with a DB prefix:
> > > exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> > > --
> > > Tom
> > > ----
> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > > SQL Server MVP
> > > Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> > > "Jim" <jsh...@.datamann.com> wrote in message
> > >news:1182957304.026319.146270@.k29g2000hsd.googlegroups.com...
> > > Hi,
> > > I want to drop an index in a stored procedure. The index is in a
> > > different database though so I am having lots of problems.
> > > I have a Database called Db1. The table is called table1 and the
> > > index is called myindex. The stored procedure is in Db2. I want to
> > > drop the index in Db1 from the stored procedure is in Db2.
> > > USE statements cannot be used in a stored procedure so I am not able
> > > to have DB2 used for the DROP command. I tried to drop a fully
> > > qualified index name such as
> > > DROP INDEX Db1.table1.myindex
> > > but I get the error..
> > > Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> > > Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> > > in the system catalog.
> > > I want to have the stored procedures in a different db becaue Db1 gets
> > > replaced often and I want to have all the stored procedures in a
> > > separate database (Db2).
> > > What am I doing wrong?
> > > Thanks!
> > > Jim- Hide quoted text -
> > - Show quoted text -- Hide quoted text -
> - Show quoted text -|||I haven't ever seen that particular syntax for sp_executesql. Yet another
useful tidbit from the forums!!
--
TheSQLGuru
President
Indicium Resources, Inc.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uV4vU9MuHHA.4412@.TK2MSFTNGP02.phx.gbl...
> Use sp_executesql, with a DB prefix:
> exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Jim" <jshain@.datamann.com> wrote in message
> news:1182957304.026319.146270@.k29g2000hsd.googlegroups.com...
> Hi,
> I want to drop an index in a stored procedure. The index is in a
> different database though so I am having lots of problems.
> I have a Database called Db1. The table is called table1 and the
> index is called myindex. The stored procedure is in Db2. I want to
> drop the index in Db1 from the stored procedure is in Db2.
> USE statements cannot be used in a stored procedure so I am not able
> to have DB2 used for the DROP command. I tried to drop a fully
> qualified index name such as
> DROP INDEX Db1.table1.myindex
> but I get the error..
> Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> in the system catalog.
> I want to have the stored procedures in a different db becaue Db1 gets
> replaced often and I want to have all the stored procedures in a
> separate database (Db2).
> What am I doing wrong?
> Thanks!
> Jim
>|||It's helped me out in my more lucid moments. ;-)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:u2esZrZuHHA.2272@.TK2MSFTNGP04.phx.gbl...
I haven't ever seen that particular syntax for sp_executesql. Yet another
useful tidbit from the forums!!
--
TheSQLGuru
President
Indicium Resources, Inc.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uV4vU9MuHHA.4412@.TK2MSFTNGP02.phx.gbl...
> Use sp_executesql, with a DB prefix:
> exec Db1.dbo.sp_executesql N'drop index table1.myindex'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Jim" <jshain@.datamann.com> wrote in message
> news:1182957304.026319.146270@.k29g2000hsd.googlegroups.com...
> Hi,
> I want to drop an index in a stored procedure. The index is in a
> different database though so I am having lots of problems.
> I have a Database called Db1. The table is called table1 and the
> index is called myindex. The stored procedure is in Db2. I want to
> drop the index in Db1 from the stored procedure is in Db2.
> USE statements cannot be used in a stored procedure so I am not able
> to have DB2 used for the DROP command. I tried to drop a fully
> qualified index name such as
> DROP INDEX Db1.table1.myindex
> but I get the error..
> Server: Msg 3703, Level 11, State 6, Procedure sp_Post_DM_Ids, Line 25
> Cannot drop the index 'Db1.Table1.myindex', because it does not exist
> in the system catalog.
> I want to have the stored procedures in a different db becaue Db1 gets
> replaced often and I want to have all the stored procedures in a
> separate database (Db2).
> What am I doing wrong?
> Thanks!
> Jim
>

drop an extended proc that doesn't exist

the latest hotfix for sql2000 gets an error when it tries
to replace an extended proc named xp_mergexpusage in the
replsys.sql file. Here is a small test case to show the
issue. Any ideas on how to resolve this?
Script:--
select * from sysobjects
where name = 'xp_mergexpusage'
go
execute dbo.sp_dropextendedproc 'xp_mergexpusage'
go
execute sp_addextendedproc 'xp_mergexpusage', 'xprepl.dll'
go
result:---
(0 row(s) affected)
Server: Msg 3701, Level 11, State 5, Procedure
sp_dropextendedproc, Line 18
Cannot drop the procedure 'xp_mergexpusage', because it
does not exist in the system catalog.
Server: Msg 2714, Level 16, State 7, Procedure
sp_addextendedproc, Line 26
There is already an object named 'xp_mergexpusage' in the
database.If you are in this state all sp_addextendedproc calls should fail.
Try running this:
select max(id) from master.dbo.sysobjects
dbcc traceon(3604)
dbcc dbinfo('master')
dbcc traceoff(3604)
What is the value returned by the first query and the value of dbi_nextid =returned by the dbcc dbinfo ?
If they are indentical, try doing:
create table master.dbo.t1(c1 int)
drop table master.dbo.t1
And run the query again, see if the values are no longer identical.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.
"Gerald Bowers" <gerald.bowers@.wachovia.com> wrote in message
news:018201c356cb$5a3294c0$a501280a@.phx.gbl...
> the latest hotfix for sql2000 gets an error when it tries
> to replace an extended proc named xp_mergexpusage in the
> replsys.sql file. Here is a small test case to show the
> issue. Any ideas on how to resolve this?
> Script:--
> select * from sysobjects
> where name = 'xp_mergexpusage'
> go
> execute dbo.sp_dropextendedproc 'xp_mergexpusage'
> go
> execute sp_addextendedproc 'xp_mergexpusage', 'xprepl.dll'
> go
> result:---
> (0 row(s) affected)
> Server: Msg 3701, Level 11, State 5, Procedure
> sp_dropextendedproc, Line 18
> Cannot drop the procedure 'xp_mergexpusage', because it
> does not exist in the system catalog.
> Server: Msg 2714, Level 16, State 7, Procedure
> sp_addextendedproc, Line 26
> There is already an object named 'xp_mergexpusage' in the
> database.
>|||You are correct, upon working with it further, later sp_addextendedproc calls did in fact fail.
You solution did rectify the situation. Do you know what series of events gets it into that state?
Thanks,
Gerald
>--Original Message--
>If you are in this state all sp_addextendedproc calls should fail.
>Try running this:
>select max(id) from master.dbo.sysobjects
>dbcc traceon(3604)
>dbcc dbinfo('master')
>dbcc traceoff(3604)
>What is the value returned by the first query and the value of dbi_nextid =3D
>returned by the dbcc dbinfo ?
>If they are indentical, try doing:
>create table master.dbo.t1(c1 int)
>drop table master.dbo.t1
>And run the query again, see if the values are no longer identical.
>GertD@.SQLDev.Net
>Please reply only to the newsgroups.
>This posting is provided "AS IS" with no warranties, and confers no rights.
>You assume all risk for your use.
>Copyright =A9 SQLDev.Net 1991-2003 All rights reserved.
>"Gerald Bowers" <gerald.bowers@.wachovia.com> wrote in message
>news:018201c356cb$5a3294c0$a501280a@.phx.gbl...
>> the latest hotfix for sql2000 gets an error when it tries
>> to replace an extended proc named xp_mergexpusage in the
>> replsys.sql file. Here is a small test case to show the
>> issue. Any ideas on how to resolve this?
>> Script:--
>> select * from sysobjects
>> where name =3D 'xp_mergexpusage'
>> go
>> execute dbo.sp_dropextendedproc 'xp_mergexpusage'
>> go
>> execute sp_addextendedproc 'xp_mergexpusage', 'xprepl.dll'
>> go
>> result:---
>> (0 row(s) affected)
>> Server: Msg 3701, Level 11, State 5, Procedure
>> sp_dropextendedproc, Line 18
>> Cannot drop the procedure 'xp_mergexpusage', because it
>> does not exist in the system catalog.
>> Server: Msg 2714, Level 16, State 7, Procedure
>> sp_addextendedproc, Line 26
>> There is already an object named 'xp_mergexpusage' in the
>> database.
>
>.
>