Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
madhan
Yak Posting Veteran
59 Posts |
 Posted - 2011-09-20 : 13:14:36
|
| Hi,I am trying to add a alter table code in stored procedure and create a same name index in two different databases. When I run the query in query analyzer separately it runs really well and created those new columns and non-clustered Index very ell. But when I run the code in SP, it gives error on non-clusterd index line as Invalid column name 'locus_id'if not exists (select COLUMN_NAME from psdb_1.INFORMATION_SCHEMA.columns where (table_schema='dbo') and (table_name = 'bbshphst') and (column_name ='locus_id' ))BEGIN alter table PSDB_1.dbo.bbshphst add locus_id int , tax_rate NUMERIC(9,8) CREATE NONCLUSTERED INDEX [IX_bbshphst_locus_id] ON [psdb_1].[dbo].[bbshphst] ([locus_id])END if not exists (select COLUMN_NAME from psdb_2.INFORMATION_SCHEMA.columns where (table_schema='dbo') and (table_name = 'bbshphst') and (column_name ='locus_id' )) BEGIN alter table PSDB_2.dbo.bbshphst add locus_id int , tax_rate NUMERIC(9,8) CREATE NONCLUSTERED INDEX [IX_bbshphst_locus_id] ON [psdb_2].[dbo].[bbshphst] ([locus_id]) --error END please help! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-20 : 13:17:59
|
| add a go after alter table statement and check.I didnt understand why you're adding the columns and index inside sp though. it seems like a one time job and i cant find any value add in including it in a sp, unless you drop and recreate column regularly which is not a recommended practice by itself.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
Add two new columns in SP with non-clustered