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
>