August 11, 2005 at 4:04 pm
I am using ADO application to connect to SQL Server and executing the SQL Script. The SQL Script has sp_rename command in one of the statements, the application exists with the message
Caution: Changing any part of an object name could break scripts and stored procedures.
though it is just a warning. Can I suppress this warning from coming up and continuing the script.
August 12, 2005 at 7:04 am
This is just a normal warning. You can ignore it and continue the script.
August 12, 2005 at 8:48 am
You might try adding:
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
to the stored proc.
See BOL for additional SET options
August 23, 2005 at 4:01 pm
I tried add SET ANSI_WARNINGS OFF to the proc and still the get the warning
-2147217871 [Microsoft][ODBC SQL Server Driver][SQL Server]Caution: Changing any part of an object name could break scripts and stored procedures.
Here is my proc content
Create procedure AlterColumnToNTEXT
@owner nvarchar(255),
@tblname sysname,
@colname nvarchar(255),
@nullproperty varchar(100)
AS
set xact_abort on -- will abort the whole transaction if error is raised ??
set nocount on
SET ANSI_WARNINGS OFF
-- print 'inside AlterColumnToNTEXT'
exec( 'Alter table ['+@owner+'].['+@tblname+'] add [tempmgc'+@colname+'] [ntext] ' + @nullproperty + ' CONSTRAINT [DF' + @tblname + 'tempmgc'+ @colname + '] DEFAULT N''tempmgctest''')
exec ( 'Update ['+@owner+'].['+@tblname+'] Set [tempmgc'+@colname+'] = [' + @colname + ']')
exec ( 'Alter table ['+@owner+'].['+@tblname+'] drop Column [' + @colname + ']')
exec ( 'EXEC sp_rename ''['+@owner+'].[' + @tblname+'].[tempmgc'+@colname + ']'', ''' + @colname + ''', ''COLUMN''')
exec('Alter table [' + @owner + '].[' + @tblname + '] Drop constraint [DF' + @tblname + 'tempmgc'+ @colname + ']')
If @nullproperty = 'NULL'
Begin
exec ( 'Update ['+@owner+'].['+@tblname+'] Set [' + @colname + '] = NULL where [' + @colname + '] like ''tempmgctest''')
End
if @@error != 0
goto error_exception
return 0
error_exception:
return 1
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply