July 21, 2003 at 7:23 am
I am writing an asp.net app that needs to rename column names. The table to be worked on is a temp table, created either from an existing table, or a view. Regardless of the source of the temp table, the same sp is used to create it, as follows:
CREATE PROCEDURE dbo.eMakeTempTable
(@Table[varchar] (50),
@Fields[varchar] (2000),
@user-id[varchar] (50),
@Year[varchar] (50),
@sql[varchar] (2150) = null)
AS
set @sql = 'SELECT ' + @Fields + ' INTO [dbo].[TempTableForUserID' + @user-id + '] FROM ' + @Table + ' WHERE DataYear = ' + @Year
exec (@Sql)
GO
Also, regardless of the temp table source, the same sp is used to rename a column:
CREATE PROCEDURE dbo.eChangeColumnName
(@OldName[varchar] (100),
@NewName[varchar] (100))
AS
EXEC sp_rename @OldName, @NewName, 'COLUMN'
GO
This re-naming approach works via the .dot net code behind, calling the sp, when the source is an existing table, but does not work when the source is a view. SQL Server simply times out, not matter how long the timeout is set.
Interestingly, the renaming ALWAYS works when tested through the SQL Server Query Analyzer, using exactly the same arguments that the app passes to the sp's.
Any insights here would be appreciated.
July 21, 2003 at 7:38 am
Couple of things: How are you passing your @OldName variable? For column change this should be 'tablename.oldcolumnname' for sp_rename to work.
Also, under what security context is your dotnet code connecting to SQL Server? I think sp-rename require sysadmin, db_owner, or db_ddladmin rights.
Joseph
July 21, 2003 at 12:37 pm
I am using 'tablename.oldcolumnname'; I am logged in as db owner; I also have admin priveleges. Renaming works for smaller tables; the one that is not working has about 7,000 records.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply