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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-16 : 11:55:27
|
| Taryn-Vee writes "Hi there I am trying to execute a stored proc on another database using sp_executesql. The reason for this is that the database name is passed into the stored procedure as a parameter.here is the code that I have attempted to get working however i keep getting a stored procedure does not exist message and I know this proc is there!code snippet as follows:/***************************************************************//* create the sql proc string */select @exec_sql = 'exec ' + @database + '..spdba_rename_master @table varchar(50), @uri integer, @field_name varchar(50), @oldval varchar(50), @newval varchar(50)'/* create the parameter variable string */select @parmvar = '@table varchar(50), @uri integer, @field_name varchar(50), @oldval varchar(50), @newval varchar(50)'/* set up the variables */select @parmval = '@table = @table , @uri = @uri , @field_name = @field_name , @oldval =@oldval , @newval =@newval 'execute sp_executesql @exec_sql , @parmvar ,@parmval/***************************************************************/@exec_sql , @parmvar ,@parmval are all defined as nvarchar variables.i have tried various methods of defining @parmval but none seem to work and the online books dont seem to have a clear cut route for this.Much appreciatedTaryn-Vee" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2001-12-16 : 19:45:40
|
| Which SP is it complaining about?Try just execute sp_executesql to check if it is sp_executesql that is not there.next 'exec ' + @database + '..sp_configure'then 'exec ' + @database + '..spdba_rename_master'that should give a missing parameters message. If it gives a SP not found then I suspect that you have misspelt something or the SP really is not there.Put a select @exec_sql in before the call to make sure you are calling what you think you are.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2001-12-16 : 19:51:01
|
| You can also execute sp_executesql in the database in which the SP resides by calling it via dynamic sql.exec ('exec dbname..sp_executesql N''exec spdba_rename_master...'' ..')The name of the SP you are trying to call is a bit worrying too.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
tinks
Starting Member
34 Posts |
Posted - 2001-12-17 : 04:56:58
|
| I finally after loads of hairpulling and threatening to throw my pc out the window figured this one out! it didnt like my stringing in the @parmval variable - so instead of /*****************************************************/select @exec_sql = ('exec ' + @server + '.' + @database + '..spdba_rename_master @table , @uri , @field_name , @oldval , @newval ')select @parmvar = '@table varchar(50), @uri integer, @field_name varchar(50), @oldval varchar(50), @newval varchar(50)'select @parmval = @table = @table , @uri =@uri , @field_name = @field_name , @oldval = @oldval, @newval = @newvalexecute sp_executesql @exec_sql ,@parmvar, @parmval/*****************************************************/this seems to work ... oh well /*****************************************************/select @exec_sql = ('exec ' + @server + '.' + @database + '..spdba_rename_master @table , @uri , @field_name , @oldval , @newval ')select @parmvar = '@table varchar(50), @uri integer, @field_name varchar(50), @oldval varchar(50), @newval varchar(50)'execute sp_executesql @exec_sql ,@parmvar, @table = @table , @uri =@uri , @field_name = @field_name , @oldval = @oldval, @newval = @newval/*****************************************************/Also I know the naming of the stored procedure is not ideal but the product is called 'co.name database administrator' (which I have tried to coerce the company to change as it is a job title and not a product name) and that was (unfortunately) the standard picked by my boss who would not be swayed!Thanks for the help guysTaryn-VeeTaryn-Vee@>-'-,--- |
|
|
|
|
|
|
|
|
using sp_executesql in Stored Procedures to call a stored procedure on another database