Prev: Paging Size set up for sql 2005 best performance
Next: Backup Behavior
From: Cindy Lee on 14 Jul 2010 13:00
When I'm trying to rename a table in the tempdb table, I'm getting an
error when I run it in a stored procedure or code, but it works ok on
MSSMS. I get the error:
Invalid EXECUTE statement using object "Object", method "LockMatchID".

EXEC sp_rename 'Tempdb.dbo.oldFile' , 'newFile'

Don't ask me why I have to mess with the tempdb files (This is
probably the problem). But I have old legacy code that writes to the
tempdb database.
From: Cindy Lee on 14 Jul 2010 13:18
On Jul 14, 10:00 am, Cindy Lee <danparker...(a)yahoo.com> wrote:
> When I'm trying to rename a table in the tempdb table, I'm getting an
> error when I run it in a stored procedure or code, but it works ok on
> MSSMS.  I get the error:
> Invalid EXECUTE statement using object "Object", method "LockMatchID".
>
> EXEC sp_rename 'Tempdb.dbo.oldFile' , 'newFile'
>
> Don't ask me why I have to mess with the tempdb files (This is
> probably the problem).  But I have old legacy code that writes to the
> tempdb database.

Ah, I think I just needed to change to the tempdb database, I need to
do a 'use tempdb' I had problems cus I was using a stored procedure
and I can't do it with that.
From: Erland Sommarskog on 14 Jul 2010 18:03
Cindy Lee (danparker276(a)yahoo.com) writes:
> On Jul 14, 10:00�am, Cindy Lee <danparker...(a)yahoo.com> wrote:
>> When I'm trying to rename a table in the tempdb table, I'm getting an
>> error when I run it in a stored procedure or code, but it works ok on
>> MSSMS. �I get the error:
>> Invalid EXECUTE statement using object "Object", method "LockMatchID".
>>
>> EXEC sp_rename 'Tempdb.dbo.oldFile' , 'newFile'
>>
>> Don't ask me why I have to mess with the tempdb files (This is
>> probably the problem). �But I have old legacy code that writes to the
>> tempdb database.
>
> Ah, I think I just needed to change to the tempdb database, I need to
> do a 'use tempdb' I had problems cus I was using a stored procedure
> and I can't do it with that.

You can run sp_rename this way:

EXEC tempdb.sys.sp_rename 'dbo.oldFile' , 'newFile'

The same applies to all system procedure. Invoke them with three-
part notation to run them in a different database.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Paging Size set up for sql 2005 best performance
Next: Backup Behavior