June 2, 2010 at 4:38 am
Hi i am running a script in an stored procedure in database 1 but want to rename a table in database 2 . the following is not working
Running this in db1
EXECUTE sp_rename N'Db2.dbo.Tmp_table', N'Db2.dbo.table', 'OBJECT'
Getting this error
Msg 290, Level 16, State 2, Procedure sp_rename, Line 276
Invalid EXECUTE statement using object "Object", method "LockMatchID".
Any ideas?
June 2, 2010 at 4:48 am
Use this instead
sp_rename 'Db2..Tmp_table', 'Db2..table'
Njoi SQL'ing
~RD
June 2, 2010 at 4:52 am
Tried that with no luck .. same error message.
Can sp_rename used on tables out with the database you are using?
It works fine from with the current database
June 2, 2010 at 5:02 am
SP_Rename only works on the current database
June 2, 2010 at 5:05 am
ok thanks for your help
June 2, 2010 at 5:07 am
no worries, a good way to see what is going on 'under the hood' of these stored procs is to run sp_helptext
sp_helptext sp_rename
quite often the comments at the top can be useful
June 2, 2010 at 5:12 am
I note point 5
[5] If Database is part of the qualified @objname,
then it must match the current database. The @newname parm can
never be qualified.
June 3, 2010 at 5:07 am
Try
USE MyDB1
GO
EXEC MyDB2..sp_rename 'MyTable', 'YourTable'
March 8, 2012 at 9:00 am
Thanks Jan,
I had a same issue. It is working fine with your solution.
Regards,
Nitin
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy