Post reply

suppress warnings in ADO

  • August 11, 2005 at 4:04 pm

    #87094

    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

    #581738

    This is just a normal warning. You can ignore it and continue the script.

  • August 12, 2005 at 8:48 am

    #581784

    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

    #584420

    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