By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,534 Members | 1,830 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,534 IT Pros & Developers. It's quick & easy.

error while executing stored procedure

P: 2
hi there
i have written follwing SP
to add two columns in my table (passing tablename as parameter)

create procedure sp_addCol
@tablename varchar (50)
as
DECLARE @tsql_TZ varchar (200)
SET @tsql_TZ = 'ALTER TABLE [ '+@tablename+'] ADD timezone varchar (5)'
DECLARE @tsql_EXP varchar (200)
EXEC(@tsql_TZ)
SET @tsql_EXP = 'ALTER TABLE [ '+@tablename+'] ADD exported varchar (5)'
EXEC(@tsql_EXP)

when i try to execute the SP
using following command

execute sp_addCol 'MGV10SEP_MNPC_MNLD'

it show me the error
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table ' MGV10SEP_MNPC_MNLD' because this table does not exist in database 'SIL_TESTDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table ' MGV10SEP_MNPC_MNLD' because this table does not exist in database 'SIL_TESTDB'.

but table is actually exist in database
please help

thanks & regards
Sep 16 '08 #1
Share this Question
Share on Google+
2 Replies


deepuv04
Expert 100+
P: 227
Hi,
In the following two statements there is an extra space at the tablename.

SET @tsql_TZ = 'ALTER TABLE [ '+@tablename+'] ADD timezone varchar (5)'
SET @tsql_EXP = 'ALTER TABLE [ '+@tablename+'] ADD exported varchar (5)'


When the string is created an extra space is added to the name of the table which is not there. Remove the space and try again.

try this code
Expand|Select|Wrap|Line Numbers
  1. create procedure sp_addCol
  2. @tablename varchar (50)
  3. as
  4. DECLARE @tsql_TZ varchar (200)
  5. SET @tsql_TZ = 'ALTER TABLE ['+@tablename+'] ADD timezone varchar (5)'
  6. DECLARE @tsql_EXP varchar (200)
  7. EXEC(@tsql_TZ)
  8. SET @tsql_EXP = 'ALTER TABLE ['+@tablename+'] ADD exported varchar (5)'
  9. EXEC(@tsql_EXP)
  10.  
  11.  
Thanks
Sep 17 '08 #2

P: 2
Hi,
In the following two statements there is an extra space at the tablename.

SET @tsql_TZ = 'ALTER TABLE [ '+@tablename+'] ADD timezone varchar (5)'
SET @tsql_EXP = 'ALTER TABLE [ '+@tablename+'] ADD exported varchar (5)'


When the string is created an extra space is added to the name of the table which is not there. Remove the space and try again.

try this code
Expand|Select|Wrap|Line Numbers
  1. create procedure sp_addCol
  2. @tablename varchar (50)
  3. as
  4. DECLARE @tsql_TZ varchar (200)
  5. SET @tsql_TZ = 'ALTER TABLE ['+@tablename+'] ADD timezone varchar (5)'
  6. DECLARE @tsql_EXP varchar (200)
  7. EXEC(@tsql_TZ)
  8. SET @tsql_EXP = 'ALTER TABLE ['+@tablename+'] ADD exported varchar (5)'
  9. EXEC(@tsql_EXP)
  10.  
  11.  
Thanks




oh!!!!!
thanks 4 ur reply :-)
Sep 17 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.