August 6, 2013 at 11:27 pm
Hi Professionals
I am running html with php which passed two variables in based on the column names within the database from a dynamically created table. At the backend I have a stored procedure which renames the two column names passed in.
USE [TestData]
GO
/****** Object: StoredProcedure [dbo].[updatethecolumnsSMPV] Script Date: 08/07/2013 15:19:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[updatethecolumnsSMPV]
@column1 nvarchar(1000), /* Needs to be renamed to software manufacturer */
@column2 nvarchar(1000) /* Needs to be renamed to product version */
as
/*****************************************************************************************************
** This procedure passes in a combination choice of two variables **
**software manufacturer and product version **
** we need to rename the columns consecutively **
*****************************************************************************************************/
begin
declare @tablename1 varchar(100), @tablename2 varchar(100);
begin
begin transaction
--SET @tablename1 = '''' + 'newtable.[' + @column1 + ']' + '''';
SET @tablename1 = 'newtable.[' + @column1 + ']'; /* Revised 07-08-2013 */
SET @tablename2 = 'newtable.[' + @column2 + ']';
exec sp_rename @tablename1,'softwaremanufacturer','column'
exec sp_rename @tablename2 ,'productversion','column'
commit transaction
end
--Now go on to cleanse the database Software Manufacturers and the Product names to how they should be
exec cleanseSMPV;
END
The problem is on the front end explorer I am recieving this error, even though everything works fine. Is there a way to not show this error or get rid of it entirely
Array ( [0] => Array ( [0] => 01000 [SQLSTATE] => 01000 [1] => 15477 => 15477 [2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Caution: Changing any part of an object name could break scripts and stored procedures. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Caution: Changing any part of an object name could break scripts and stored procedures. ) )
thanks
August 7, 2013 at 7:55 am
That isn't an error. It is a warning message. AFAIK there is no way to prevent SQL from returning that message when calling sp_rename. Can't you just prevent that in the front end?
I have to ask, why do you need a proc that renames columns frequently enough that you had to create a stored proc for it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 7, 2013 at 12:46 pm
Hi Sean
The reason they change frequentl is that they are imported from a spreadsheet and the user selects the column name from a drop down box which matches the software manufacturer etc so i need to rename it to that to run updates and cleanse etc.
the imported spreadsheet column could be named anything so I pass this in as a variable after I have dynamically created the table it matches the spreadsheet column names so they need to be changed
hope this makes sense.
is there another way to manually rename the columns with an alter statement rather than use the sp_rename
thanks
Alan
August 7, 2013 at 1:05 pm
Oracle765 (8/7/2013)
Hi SeanThe reason they change frequentl is that they are imported from a spreadsheet and the user selects the column name from a drop down box which matches the software manufacturer etc so i need to rename it to that to run updates and cleanse etc.
the imported spreadsheet column could be named anything so I pass this in as a variable after I have dynamically created the table it matches the spreadsheet column names so they need to be changed
hope this makes sense.
is there another way to manually rename the columns with an alter statement rather than use the sp_rename
thanks
Alan
You can achieve that manually with some trickery.
Keep in mind that this is not as efficient as sp_rename but if you have reasonably small datasets this shouldn't be too bad.
Here is an example of one way this can be done.
create table #MyTable
(
MyID int identity primary key clustered,
Column1 varchar(25)
)
insert #MyTable
select 'Theodore' union all
select 'Robert' union all
select 'Robert' union all
select 'Timothy' union all
select 'Janice'
--Now we want to "rename" Column1 to MyNewColumn
--First we have to add a new column
alter table #MyTable
add MyNewColumn varchar(25);
--Now we populate the new column with the original data
update #MyTable
set MyNewColumn = Column1
--Now we drop the original column
alter table #MyTable
drop column Column1
--It is effectively renamed
select * from #MyTable
drop table #MyTable
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 7, 2013 at 3:10 pm
Hi Sean
that works until i try and put it into a procedure and I get the error
Msg 102, Level 15, State 1, Procedure updatethecolumnsSMPV, Line 22
Incorrect syntax near '@column1'.
USE [TestData]
GO
/****** Object: StoredProcedure [dbo].[updatethecolumnsSMPV] Script Date: 08/07/2013 15:19:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[updatethecolumnsSMPV]
@column1 nvarchar(1000), /* Needs to be renamed to software manufacturer */
@column2 nvarchar(1000) /* Needs to be renamed to product version */
as
/*****************************************************************************************************
** This procedure passes in a combination choice of two variables **
**software manufacturer and product version **
** we need to rename the columns consecutively **
*****************************************************************************************************/
begin
--First we have to add a new column
alter table dbo.newtable
add softwaremanufacturer varchar(1000)
--Now we populate the new column with the original data
update dbo.newtable
set softwaremanufacturer = @column1
--Now we drop the original column
alter table dbo.newtable
drop column @column1
exec cleanseSMPV;
END
any ideas
August 7, 2013 at 3:37 pm
Hi again Sean
everything works fine its just the drop column part it does not seem to like
--Now we drop the original column
alter table dbo.newtable
drop column @column1
regards
Alan
August 8, 2013 at 7:24 am
Oracle765 (8/7/2013)
Hi again Seaneverything works fine its just the drop column part it does not seem to like
--Now we drop the original column
alter table dbo.newtable
drop column @column1
regards
Alan
This is because you can't use variable names like that for ddl statements. You would have to change that to dynamic sql.
declare @sql nvarchar(2000)
set @sql = 'alter table dbo.newtable drop column ' + @column1
sp_executesql @sql
Be careful here though. This is vulnerable to sql injection. I don't know of any way to do this with parameterized dynamic sql to prevent this vulnerability.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 8, 2013 at 6:00 pm
thankyou that works, not sure how to close this post though or mark it as solved
August 9, 2013 at 7:14 am
Oracle765 (8/8/2013)
thankyou that works, not sure how to close this post though or mark it as solved
Glad that worked for you.
We don't close threads or mark them as solved around here. It may be that somebody else will stumble on this thread and find an answer. Or even better, somebody may come by here and post a better solution to the same problem.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply