We need to import lots of data into some tables. To speed
up the import, we deside to drop the indexes related to
the tables that will receive the data before the import
and then recreate the index after the import. What's the
best practice for the senarios below? When to drop/keep
indexes? What kind of indexes(clustered/non-
clustered/unique)to drop? Many thanks.
1. The import scripts do select first and then insert.
2. The import scripts do insert first and then delete.
3. The import scripts do select first and then update.
It depends on what kind of indexes you have on the tables; sometimes,
indexes can help with data imports (e.g. seqential clustered indexes tend to
speed up data import). For a better answer, post DDL (CREATE TABLE
statements) for your tables. Include all indexes and constraints. Also
describe in more detail what the import will be doing. There are no best
practices; it all depends on the situation.
"Bill" <fei0405@.yahoo.com> wrote in message
news:276601c49cf6$c45725b0$a401280a@.phx.gbl...
> We need to import lots of data into some tables. To speed
> up the import, we deside to drop the indexes related to
> the tables that will receive the data before the import
> and then recreate the index after the import. What's the
> best practice for the senarios below? When to drop/keep
> indexes? What kind of indexes(clustered/non-
> clustered/unique)to drop? Many thanks.
> 1. The import scripts do select first and then insert.
> 2. The import scripts do insert first and then delete.
> 3. The import scripts do select first and then update.
>
|||Thank you very much for the reply.
eg.There is a table we will insert about 150,000 records.
Here is the scripts I got from gernerate scripts utility:
CREATE TABLE [dbo].[PS_RF_ATTR_INSP] (
[SETID] [char] (5) COLLATE Latin1_General_BIN NOT
NULL ,
[INST_PROD_ID] [char] (20) COLLATE
Latin1_General_BIN NOT NULL ,
[MARKET] [char] (3) COLLATE Latin1_General_BIN NOT
NULL ,
[ATTRIBUTE_ID] [char] (15) COLLATE
Latin1_General_BIN NOT NULL ,
[ATTR_ITEM_ID] [char] (15) COLLATE
Latin1_General_BIN NOT NULL ,
[ATTRIBUTE_VALUE] [char] (254) COLLATE
Latin1_General_BIN NOT NULL ,
[ATTR_DATE] [PSDATE] NULL ,
[ATTR_NUMBER] [decimal](28, 6) NOT NULL ,
[ROW_ADDED_DTTM] [PSDATETIME] NULL ,
[ROW_ADDED_OPRID] [char] (30) COLLATE
Latin1_General_BIN NOT NULL ,
[ROW_LASTMANT_DTTM] [PSDATETIME] NULL ,
[ROW_LASTMANT_OPRID] [char] (30) COLLATE
Latin1_General_BIN NOT NULL ,
[SYNCID] [int] NOT NULL ,
[SYNCDTTM] [PSDATETIME] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [PS_RF_ATTR_INSP] ON
[dbo].[PS_RF_ATTR_INSP]([SETID], [INST_PROD_ID], [MARKET],
[ATTRIBUTE_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
But when I issue command "sp_helpindex PS_RF_ATTR_INSP
go" I got more nonclustered index names as below:
index_name index_description
index_keys
PS_RF_ATTR_INSP clustered, unique located on PRIMARY
SETID, INST_PROD_ID, MARKET,ATTRIBUTE_ID
index_name index_description
index_keys
PS0RF_INST_PROD nonclustered located on PRIMARY
BO_ID_CUST, SETID, INST_PROD_ID
PS1RF_INST_PROD nonclustered located on PRIMARY
BO_ID_CONTACT, SETID, INST_PROD_ID
.... ...
Please advise. Many thanks.
>--Original Message--
>It depends on what kind of indexes you have on the
tables; sometimes,
>indexes can help with data imports (e.g. seqential
clustered indexes tend to
>speed up data import). For a better answer, post DDL
(CREATE TABLE
>statements) for your tables. Include all indexes and
constraints. Also
>describe in more detail what the import will be doing.
There are no best[vbcol=seagreen]
>practices; it all depends on the situation.
>
>"Bill" <fei0405@.yahoo.com> wrote in message
>news:276601c49cf6$c45725b0$a401280a@.phx.gbl...
speed
>
>.
>
|||Bill,
I am a bit confused about those nonclustered indexes; they contain columns
that don't appear to be in the table (BO_ID_CUST, etc).
Anyway, with no knowledge of your data it is still quite difficult to answer
this question; 150,000 rows is not a very large insert and if you already
have millions of rows in the table dropping and re-creating the indexes may
take more time than the insert itself, even if there are a lot of page
splits taking place. I think you really need to run a test batch with each
method to determine what's right for you.
"Bill" <fei0405@.yahoo.com> wrote in message
news:3a9301c49f17$d664c910$a401280a@.phx.gbl...
> Thank you very much for the reply.
> eg.There is a table we will insert about 150,000 records.
> Here is the scripts I got from gernerate scripts utility:
> CREATE TABLE [dbo].[PS_RF_ATTR_INSP] (
> [SETID] [char] (5) COLLATE Latin1_General_BIN NOT
> NULL ,
> [INST_PROD_ID] [char] (20) COLLATE
> Latin1_General_BIN NOT NULL ,
> [MARKET] [char] (3) COLLATE Latin1_General_BIN NOT
> NULL ,
> [ATTRIBUTE_ID] [char] (15) COLLATE
> Latin1_General_BIN NOT NULL ,
> [ATTR_ITEM_ID] [char] (15) COLLATE
> Latin1_General_BIN NOT NULL ,
> [ATTRIBUTE_VALUE] [char] (254) COLLATE
> Latin1_General_BIN NOT NULL ,
> [ATTR_DATE] [PSDATE] NULL ,
> [ATTR_NUMBER] [decimal](28, 6) NOT NULL ,
> [ROW_ADDED_DTTM] [PSDATETIME] NULL ,
> [ROW_ADDED_OPRID] [char] (30) COLLATE
> Latin1_General_BIN NOT NULL ,
> [ROW_LASTMANT_DTTM] [PSDATETIME] NULL ,
> [ROW_LASTMANT_OPRID] [char] (30) COLLATE
> Latin1_General_BIN NOT NULL ,
> [SYNCID] [int] NOT NULL ,
> [SYNCDTTM] [PSDATETIME] NULL
> ) ON [PRIMARY]
> GO
> CREATE UNIQUE CLUSTERED INDEX [PS_RF_ATTR_INSP] ON
> [dbo].[PS_RF_ATTR_INSP]([SETID], [INST_PROD_ID], [MARKET],
> [ATTRIBUTE_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> But when I issue command "sp_helpindex PS_RF_ATTR_INSP
> go" I got more nonclustered index names as below:
> index_name index_description
> index_keys
> PS_RF_ATTR_INSP clustered, unique located on PRIMARY
> SETID, INST_PROD_ID, MARKET,ATTRIBUTE_ID
> index_name index_description
> index_keys
> PS0RF_INST_PROD nonclustered located on PRIMARY
> BO_ID_CUST, SETID, INST_PROD_ID
> PS1RF_INST_PROD nonclustered located on PRIMARY
> BO_ID_CONTACT, SETID, INST_PROD_ID
> ... ...
>
> Please advise. Many thanks.
Showing posts with label lots. Show all posts
Showing posts with label lots. Show all posts
Sunday, March 11, 2012
Drop indexes for data import
We need to import lots of data into some tables. To speed
up the import, we deside to drop the indexes related to
the tables that will receive the data before the import
and then recreate the index after the import. What's the
best practice for the senarios below? When to drop/keep
indexes? What kind of indexes(clustered/non-
clustered/unique)to drop? Many thanks.
1. The import scripts do select first and then insert.
2. The import scripts do insert first and then delete.
3. The import scripts do select first and then update.It depends on what kind of indexes you have on the tables; sometimes,
indexes can help with data imports (e.g. seqential clustered indexes tend to
speed up data import). For a better answer, post DDL (CREATE TABLE
statements) for your tables. Include all indexes and constraints. Also
describe in more detail what the import will be doing. There are no best
practices; it all depends on the situation.
"Bill" <fei0405@.yahoo.com> wrote in message
news:276601c49cf6$c45725b0$a401280a@.phx.gbl...
> We need to import lots of data into some tables. To speed
> up the import, we deside to drop the indexes related to
> the tables that will receive the data before the import
> and then recreate the index after the import. What's the
> best practice for the senarios below? When to drop/keep
> indexes? What kind of indexes(clustered/non-
> clustered/unique)to drop? Many thanks.
> 1. The import scripts do select first and then insert.
> 2. The import scripts do insert first and then delete.
> 3. The import scripts do select first and then update.
>|||Thank you very much for the reply.
eg.There is a table we will insert about 150,000 records.
Here is the scripts I got from gernerate scripts utility:
CREATE TABLE [dbo].[PS_RF_ATTR_INSP] (
[SETID] [char] (5) COLLATE Latin1_General_BIN NOT
NULL ,
[INST_PROD_ID] [char] (20) COLLATE
Latin1_General_BIN NOT NULL ,
[MARKET] [char] (3) COLLATE Latin1_General_BIN NOT
NULL ,
[ATTRIBUTE_ID] [char] (15) COLLATE
Latin1_General_BIN NOT NULL ,
[ATTR_ITEM_ID] [char] (15) COLLATE
Latin1_General_BIN NOT NULL ,
[ATTRIBUTE_VALUE] [char] (254) COLLATE
Latin1_General_BIN NOT NULL ,
[ATTR_DATE] [PSDATE] NULL ,
[ATTR_NUMBER] [decimal](28, 6) NOT NULL ,
[ROW_ADDED_DTTM] [PSDATETIME] NULL ,
[ROW_ADDED_OPRID] [char] (30) COLLATE
Latin1_General_BIN NOT NULL ,
[ROW_LASTMANT_DTTM] [PSDATETIME] NULL ,
[ROW_LASTMANT_OPRID] [char] (30) COLLATE
Latin1_General_BIN NOT NULL ,
[SYNCID] [int] NOT NULL ,
[SYNCDTTM] [PSDATETIME] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [PS_RF_ATTR_INSP] ON
[dbo].[PS_RF_ATTR_INSP]([SETID], [INST_PROD_ID], [MARKET],
[ATTRIBUTE_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
But when I issue command "sp_helpindex PS_RF_ATTR_INSP
go" I got more nonclustered index names as below:
index_name index_description
index_keys
PS_RF_ATTR_INSP clustered, unique located on PRIMARY
SETID, INST_PROD_ID, MARKET,ATTRIBUTE_ID
index_name index_description
index_keys
PS0RF_INST_PROD nonclustered located on PRIMARY
BO_ID_CUST, SETID, INST_PROD_ID
PS1RF_INST_PROD nonclustered located on PRIMARY
BO_ID_CONTACT, SETID, INST_PROD_ID
... ...
Please advise. Many thanks.
>--Original Message--
>It depends on what kind of indexes you have on the
tables; sometimes,
>indexes can help with data imports (e.g. seqential
clustered indexes tend to
>speed up data import). For a better answer, post DDL
(CREATE TABLE
>statements) for your tables. Include all indexes and
constraints. Also
>describe in more detail what the import will be doing.
There are no best
>practices; it all depends on the situation.
>
>"Bill" <fei0405@.yahoo.com> wrote in message
>news:276601c49cf6$c45725b0$a401280a@.phx.gbl...
>> We need to import lots of data into some tables. To
speed
>> up the import, we deside to drop the indexes related to
>> the tables that will receive the data before the import
>> and then recreate the index after the import. What's the
>> best practice for the senarios below? When to drop/keep
>> indexes? What kind of indexes(clustered/non-
>> clustered/unique)to drop? Many thanks.
>> 1. The import scripts do select first and then insert.
>> 2. The import scripts do insert first and then delete.
>> 3. The import scripts do select first and then update.
>>
>
>.
>|||Bill,
I am a bit confused about those nonclustered indexes; they contain columns
that don't appear to be in the table (BO_ID_CUST, etc).
Anyway, with no knowledge of your data it is still quite difficult to answer
this question; 150,000 rows is not a very large insert and if you already
have millions of rows in the table dropping and re-creating the indexes may
take more time than the insert itself, even if there are a lot of page
splits taking place. I think you really need to run a test batch with each
method to determine what's right for you.
"Bill" <fei0405@.yahoo.com> wrote in message
news:3a9301c49f17$d664c910$a401280a@.phx.gbl...
> Thank you very much for the reply.
> eg.There is a table we will insert about 150,000 records.
> Here is the scripts I got from gernerate scripts utility:
> CREATE TABLE [dbo].[PS_RF_ATTR_INSP] (
> [SETID] [char] (5) COLLATE Latin1_General_BIN NOT
> NULL ,
> [INST_PROD_ID] [char] (20) COLLATE
> Latin1_General_BIN NOT NULL ,
> [MARKET] [char] (3) COLLATE Latin1_General_BIN NOT
> NULL ,
> [ATTRIBUTE_ID] [char] (15) COLLATE
> Latin1_General_BIN NOT NULL ,
> [ATTR_ITEM_ID] [char] (15) COLLATE
> Latin1_General_BIN NOT NULL ,
> [ATTRIBUTE_VALUE] [char] (254) COLLATE
> Latin1_General_BIN NOT NULL ,
> [ATTR_DATE] [PSDATE] NULL ,
> [ATTR_NUMBER] [decimal](28, 6) NOT NULL ,
> [ROW_ADDED_DTTM] [PSDATETIME] NULL ,
> [ROW_ADDED_OPRID] [char] (30) COLLATE
> Latin1_General_BIN NOT NULL ,
> [ROW_LASTMANT_DTTM] [PSDATETIME] NULL ,
> [ROW_LASTMANT_OPRID] [char] (30) COLLATE
> Latin1_General_BIN NOT NULL ,
> [SYNCID] [int] NOT NULL ,
> [SYNCDTTM] [PSDATETIME] NULL
> ) ON [PRIMARY]
> GO
> CREATE UNIQUE CLUSTERED INDEX [PS_RF_ATTR_INSP] ON
> [dbo].[PS_RF_ATTR_INSP]([SETID], [INST_PROD_ID], [MARKET],
> [ATTRIBUTE_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> But when I issue command "sp_helpindex PS_RF_ATTR_INSP
> go" I got more nonclustered index names as below:
> index_name index_description
> index_keys
> PS_RF_ATTR_INSP clustered, unique located on PRIMARY
> SETID, INST_PROD_ID, MARKET,ATTRIBUTE_ID
> index_name index_description
> index_keys
> PS0RF_INST_PROD nonclustered located on PRIMARY
> BO_ID_CUST, SETID, INST_PROD_ID
> PS1RF_INST_PROD nonclustered located on PRIMARY
> BO_ID_CONTACT, SETID, INST_PROD_ID
> ... ...
>
> Please advise. Many thanks.
up the import, we deside to drop the indexes related to
the tables that will receive the data before the import
and then recreate the index after the import. What's the
best practice for the senarios below? When to drop/keep
indexes? What kind of indexes(clustered/non-
clustered/unique)to drop? Many thanks.
1. The import scripts do select first and then insert.
2. The import scripts do insert first and then delete.
3. The import scripts do select first and then update.It depends on what kind of indexes you have on the tables; sometimes,
indexes can help with data imports (e.g. seqential clustered indexes tend to
speed up data import). For a better answer, post DDL (CREATE TABLE
statements) for your tables. Include all indexes and constraints. Also
describe in more detail what the import will be doing. There are no best
practices; it all depends on the situation.
"Bill" <fei0405@.yahoo.com> wrote in message
news:276601c49cf6$c45725b0$a401280a@.phx.gbl...
> We need to import lots of data into some tables. To speed
> up the import, we deside to drop the indexes related to
> the tables that will receive the data before the import
> and then recreate the index after the import. What's the
> best practice for the senarios below? When to drop/keep
> indexes? What kind of indexes(clustered/non-
> clustered/unique)to drop? Many thanks.
> 1. The import scripts do select first and then insert.
> 2. The import scripts do insert first and then delete.
> 3. The import scripts do select first and then update.
>|||Thank you very much for the reply.
eg.There is a table we will insert about 150,000 records.
Here is the scripts I got from gernerate scripts utility:
CREATE TABLE [dbo].[PS_RF_ATTR_INSP] (
[SETID] [char] (5) COLLATE Latin1_General_BIN NOT
NULL ,
[INST_PROD_ID] [char] (20) COLLATE
Latin1_General_BIN NOT NULL ,
[MARKET] [char] (3) COLLATE Latin1_General_BIN NOT
NULL ,
[ATTRIBUTE_ID] [char] (15) COLLATE
Latin1_General_BIN NOT NULL ,
[ATTR_ITEM_ID] [char] (15) COLLATE
Latin1_General_BIN NOT NULL ,
[ATTRIBUTE_VALUE] [char] (254) COLLATE
Latin1_General_BIN NOT NULL ,
[ATTR_DATE] [PSDATE] NULL ,
[ATTR_NUMBER] [decimal](28, 6) NOT NULL ,
[ROW_ADDED_DTTM] [PSDATETIME] NULL ,
[ROW_ADDED_OPRID] [char] (30) COLLATE
Latin1_General_BIN NOT NULL ,
[ROW_LASTMANT_DTTM] [PSDATETIME] NULL ,
[ROW_LASTMANT_OPRID] [char] (30) COLLATE
Latin1_General_BIN NOT NULL ,
[SYNCID] [int] NOT NULL ,
[SYNCDTTM] [PSDATETIME] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [PS_RF_ATTR_INSP] ON
[dbo].[PS_RF_ATTR_INSP]([SETID], [INST_PROD_ID], [MARKET],
[ATTRIBUTE_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
But when I issue command "sp_helpindex PS_RF_ATTR_INSP
go" I got more nonclustered index names as below:
index_name index_description
index_keys
PS_RF_ATTR_INSP clustered, unique located on PRIMARY
SETID, INST_PROD_ID, MARKET,ATTRIBUTE_ID
index_name index_description
index_keys
PS0RF_INST_PROD nonclustered located on PRIMARY
BO_ID_CUST, SETID, INST_PROD_ID
PS1RF_INST_PROD nonclustered located on PRIMARY
BO_ID_CONTACT, SETID, INST_PROD_ID
... ...
Please advise. Many thanks.
>--Original Message--
>It depends on what kind of indexes you have on the
tables; sometimes,
>indexes can help with data imports (e.g. seqential
clustered indexes tend to
>speed up data import). For a better answer, post DDL
(CREATE TABLE
>statements) for your tables. Include all indexes and
constraints. Also
>describe in more detail what the import will be doing.
There are no best
>practices; it all depends on the situation.
>
>"Bill" <fei0405@.yahoo.com> wrote in message
>news:276601c49cf6$c45725b0$a401280a@.phx.gbl...
>> We need to import lots of data into some tables. To
speed
>> up the import, we deside to drop the indexes related to
>> the tables that will receive the data before the import
>> and then recreate the index after the import. What's the
>> best practice for the senarios below? When to drop/keep
>> indexes? What kind of indexes(clustered/non-
>> clustered/unique)to drop? Many thanks.
>> 1. The import scripts do select first and then insert.
>> 2. The import scripts do insert first and then delete.
>> 3. The import scripts do select first and then update.
>>
>
>.
>|||Bill,
I am a bit confused about those nonclustered indexes; they contain columns
that don't appear to be in the table (BO_ID_CUST, etc).
Anyway, with no knowledge of your data it is still quite difficult to answer
this question; 150,000 rows is not a very large insert and if you already
have millions of rows in the table dropping and re-creating the indexes may
take more time than the insert itself, even if there are a lot of page
splits taking place. I think you really need to run a test batch with each
method to determine what's right for you.
"Bill" <fei0405@.yahoo.com> wrote in message
news:3a9301c49f17$d664c910$a401280a@.phx.gbl...
> Thank you very much for the reply.
> eg.There is a table we will insert about 150,000 records.
> Here is the scripts I got from gernerate scripts utility:
> CREATE TABLE [dbo].[PS_RF_ATTR_INSP] (
> [SETID] [char] (5) COLLATE Latin1_General_BIN NOT
> NULL ,
> [INST_PROD_ID] [char] (20) COLLATE
> Latin1_General_BIN NOT NULL ,
> [MARKET] [char] (3) COLLATE Latin1_General_BIN NOT
> NULL ,
> [ATTRIBUTE_ID] [char] (15) COLLATE
> Latin1_General_BIN NOT NULL ,
> [ATTR_ITEM_ID] [char] (15) COLLATE
> Latin1_General_BIN NOT NULL ,
> [ATTRIBUTE_VALUE] [char] (254) COLLATE
> Latin1_General_BIN NOT NULL ,
> [ATTR_DATE] [PSDATE] NULL ,
> [ATTR_NUMBER] [decimal](28, 6) NOT NULL ,
> [ROW_ADDED_DTTM] [PSDATETIME] NULL ,
> [ROW_ADDED_OPRID] [char] (30) COLLATE
> Latin1_General_BIN NOT NULL ,
> [ROW_LASTMANT_DTTM] [PSDATETIME] NULL ,
> [ROW_LASTMANT_OPRID] [char] (30) COLLATE
> Latin1_General_BIN NOT NULL ,
> [SYNCID] [int] NOT NULL ,
> [SYNCDTTM] [PSDATETIME] NULL
> ) ON [PRIMARY]
> GO
> CREATE UNIQUE CLUSTERED INDEX [PS_RF_ATTR_INSP] ON
> [dbo].[PS_RF_ATTR_INSP]([SETID], [INST_PROD_ID], [MARKET],
> [ATTRIBUTE_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> But when I issue command "sp_helpindex PS_RF_ATTR_INSP
> go" I got more nonclustered index names as below:
> index_name index_description
> index_keys
> PS_RF_ATTR_INSP clustered, unique located on PRIMARY
> SETID, INST_PROD_ID, MARKET,ATTRIBUTE_ID
> index_name index_description
> index_keys
> PS0RF_INST_PROD nonclustered located on PRIMARY
> BO_ID_CUST, SETID, INST_PROD_ID
> PS1RF_INST_PROD nonclustered located on PRIMARY
> BO_ID_CONTACT, SETID, INST_PROD_ID
> ... ...
>
> Please advise. Many thanks.
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
>
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
>
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
>
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
>
Subscribe to:
Posts (Atom)