0

I have searched and searched and cannot work out how to resolve my problem. I am actually not sure it is possible but thought I'd post here and check.

The issue:

I have a stored procedure where I make the following call (there are 2 because I was trying different things but both fail):

SET @olddate = '1606'
SET @newdate = '1706'
SET @TableName = 'sometablename'
SET @sql = 'SP_RENAME ''[DBName' + @olddate + '.dbo.' + @TableName + @olddate +'].[ColumnName' + @olddate + 'restofname]'',''[ColumnName' + @newdate + 'restofname]'''
EXECUTE sp_executesql @sql

Other option:

SET @olddate = '1606'
SET @newdate = '1706'
SET @TableName = 'sometablename'
SET @old = '[DBName' + @olddate + '.dbo.' + @TableName + @olddate+']."[ColumnName' + @olddate + 'restofname]"'
SET @new = 'ColumnName' + @newdate + 'restofname'
EXECUTE sp_rename @objname = @old,@newname = @new, @objtype = 'COLUMN'

I saved this in a stored procedure, and then ran it in another query which has a different database context and got the following error for the first:

No item by the name of '[DBName.dbo.TableName.ColumnName]' could be found in the current database '[Other database]', given that @itemtype was input as '(null)'.

and this for the second:

Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

Now, what I'm wondering is: can I even do this? write a stored procedure that runs sp_rename in a table in one database and then call that stored procedure from another database?

I've also tried every permutation of putting open and closed brackets, [ and ] around parts of my old and new column names. Also tried putting N before the string. It's a bit of trial and error though and thus far nothing has worked.

Amy Clark
  • 21
  • 6
  • I think you'll need a dynamic SQL query to qualify `sp_rename` with the database name and execute with `sp_executesql`. – Dan Guzman Nov 07 '17 at 14:02
  • Why not just simple `alter proc...` and run it on all dbs – TheGameiswar Nov 07 '17 at 14:03
  • 1
    you have actually [DBName 1606.dbo.sometablename 1606]. which is not valid sql. It should be something like [DBName] [1606].[dbo].[sometablename] See also the answer of PlaidDK – GuidoG Nov 07 '17 at 14:18

3 Answers3

1

You cannot put your DBName and schemaName and tablename into one bracket [ ] You need to enclose each with a bracket. And dont use brackets around you new name since it will take these brackets into the name. Its a string so you can just put spaces and what you like into your name. 

declare @TableName nvarchar(max)
declare @ColumName nvarchar(max)
declare @NewColumn nvarchar(max)
declare @sql nvarchar(max)

SET @NewColumn = 'newtest'
SET @ColumName = 'test'
SET @TableName = 'trassor'

SET @sql = 'SP_RENAME ''[YOURDBNAME].[YOURSCHEMANAME].['+ @TableName +'].['+@ColumName+']'', '''+@NewColumn+''', ''COLUMN'''

/* SET @sql = 'SP_RENAME ''[LegogSpass].[dbo].['+ @TableName +'].['+@ColumName+']'', '''+@NewColumn+''', ''COLUMN''' */

PRINT @SQL
EXECUTE sp_executesql @sql
SqlKindaGuy
  • 3,501
  • 2
  • 12
  • 29
  • @plaidDK Thanks very much . I still get this error when doing it this way: >Either the parameter objname is ambiguous or the claimed objtype (COLUMN) is wrong. But I actually found a solution in the meantime: execute ('use otherdb exec sp_rename ''thetable.[colname]'', ''newcolname'', ''COLUMN''') I think the issue was the database change.. – Amy Clark Nov 07 '17 at 14:38
  • My code works i tested it myself so either Its your variables or your typing wrong. Look at the sql print statement – SqlKindaGuy Nov 07 '17 at 15:54
  • And basically your doing the same as i am. Your just prefixing without dbname. Please mark it as correct – SqlKindaGuy Nov 07 '17 at 15:56
  • I think my problem was the fact that I called this whole stored procedure from another query sitting in another database. So then SQL didn't know where to look for the table. So this still didn't work for me when trying that. – Amy Clark Nov 10 '17 at 07:38
  • @AmyClark it doesnt matter which DB your on as your define your database before your schemaname. – SqlKindaGuy Nov 10 '17 at 08:02
0

First of all use a select @sql query before the sp_executesql statement to view how the dynamic statement has been formed.

This gives you an understanding of the error.

In your case the brackets have been placed in a wrong way. Either remove them or add individually to dbname,schemaname,tablename and column.

Also, you were missing the 'column' at the end of the sp_rename statement.

declare @sql nvarchar(max),@olddate varchar(10),@newdate varchar(10),@TableName varchar(100)
SET @olddate = '1606'
SET @newdate = '1706'
SET @TableName = 'sometablename'
SET @sql = 'SP_RENAME ''DBName' + @olddate + '.dbo.' + @TableName + @olddate +'.ColumnName' + @olddate + 'restofname'',''ColumnName' + @newdate + 'restofname'',''Column'''
select @sql
EXECUTE sp_executesql @sql
Kapil
  • 987
  • 5
  • 11
0

Thanks for all the help. I actually figured out the issue subsequently. I know the brackets were wrong but that wasn't the issue (as I tried all sorts of different combinations of brackets and none of them worked).

set @old = @TableName +'.[ColumnName' + @olddate + 'restofname]'
set @new = 'ColumnName' + @newdate + 'restofname'
execute ('use DBName exec sp_rename ''' + @old + ''', ''' + @new + ''', ''COLUMN''')

The trick was to include "Use Database" within the execute statement.

Amy Clark
  • 21
  • 6