Erro -> Error %d, Level %d, State %d, Procedure %s, Line %d, Message: Error converting data type nvarchar to bigint in EXEC(@sql1)

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  April 13, 2015 at 4:38 pm

  #296880

  Hi Guys

  Getting conversion error...When I use " SELECT @sql1", I get the correct syntax, buit while inserting records to table it breaks:-

  DBCC CHECKDB('DemoRestoreOrRepair') WITH TABLERESULTS, ALL_ERRORMSGS

  DECLARE database_cursor CURSOR FOR

  SELECT NAME

  FROM sys.databases db

  WHERE NAME NOT IN ( 'tempdb' )

  AND db.state_desc = 'ONLINE'

  AND source_database_id IS NULL

  -- REAL DBS ONLY (Not Snapshots)

  AND is_read_only = 0

  OPEN database_cursor

  FETCH next FROM database_cursor INTO @database_name

  WHILE @@FETCH_STATUS = 0

  BEGIN

  DECLARE @sql1 NVARCHAR(max)

  PRINT @database_name

  SET @sql1 = '';

  SET @sql1 = 'DBCC CHECKDB(''' + @database_name + ''') WITH TABLERESULTS';

  IF @PHYSICAL_ONLY = 'Y'

  SET @sql1 = @sql1 + ', PHYSICAL_ONLY '

  IF @tablock = 'Y'

  SET @sql1 = @sql1 + ', TABLOCK '

  IF @allMessages = 'Y'

  SET @sql1 = @sql1 + ', ALL_ERRORMSGS '

  IF @PHYSICAL_ONLY = 'Y' and @tablock = 'Y'

  BEGIN

  PRINT 'WRONG OPTION. You can''t choose both @PHYSICAL_ONLY and @tablock Options..'

  --SET @sql1 = 'DBCC CHECKDB(''' + @database_name + ''') WITH TABLERESULTS, ALL_ERRORMSGS';

  --SET @sql1 = 'DBCC CHECKDB(''' + @database_name + ''') WITH TABLERESULTS, ALL_ERRORMSGS';

  return 1;

  END

  SELECT @sql1

  INSERT INTO dbcc_history ([Error], [Level], [State], MessageText, RepairLevel, [Status],

  [DbId], [DbFragId],[ObjectId], [IndexId], PartitionId, AllocUnitId,[RidDbid], [RidPruid],[File],

  Page, Slot, [RefDbId], [RefPruId], RefFile, RefPage,

  RefSlot,Allocation)

  EXEC(@sql1)

  FETCH next FROM database_cursor INTO @database_name

  END

  -- Check data in staging table.

  --SELECT error,

  -- level,

  -- Db_name(dbid) AS DBName,

  -- Object_name(objectid, dbid) AS ObjectName,

  -- messagetext,

  -- timestamp

  --FROM dbcc_history

  CLOSE database_cursor

  DEALLOCATE database_cursor

  CREATE TABLE [dbo].[dbcc_history]

  (

  [error] [BIGINT] NULL,

  [level] [BIGINT] NULL,

  [state] [BIGINT] NULL,

  [messagetext] [VARCHAR](7000) NULL,

  [repairlevel] [BIGINT] NULL,

  [status] [BIGINT] NULL,

  [dbid] [BIGINT] NULL,

  [dbfragid] [BIGINT] NULL,

  [objectid] [BIGINT] NULL,

  [indexid] [BIGINT] NULL,

  [partitionid] [BIGINT] NULL,

  [allocunitid] [BIGINT] NULL,

  [riddbid] [BIGINT] NULL,

  [ridpruid] [BIGINT] NULL,

  [file] [BIGINT] NULL,

  [page] [BIGINT] NULL,

  [slot] [BIGINT] NULL,

  [refdbid] [BIGINT] NULL,

  [refpruid] [BIGINT] NULL,

  [reffile] [BIGINT] NULL,

  [refpage] [BIGINT] NULL,

  [refslot] [BIGINT] NULL,

  [allocation] [BIGINT] NULL,

  [timestamp] [DATETIME] NULL CONSTRAINT [DF1_dbcc_history_TimeStamp] DEFAULT (Getdate())

  )

  ON [PRIMARY]

  Thanks.

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  April 13, 2015 at 4:51 pm

  #1790647

  I get repairlevel as nvarchar(22), not BIGINT? but that is on 2012, not 2008.

  For completeness, my table create for this command on 2012 is

  CREATE TABLE <table_name> (

  [Error] [int] NULL

  , [Level] [int] NULL

  , [State] [int] NULL

  , [MessageText] [nvarchar](2048) NULL

  , [RepairLevel] [nvarchar](22) NULL

  , [Status] [int] NULL

  , [DbId] [int] NULL

  , [DbFragId] [int] NULL

  , [ObjectId] [int] NULL

  , [IndexId] [int] NULL

  , [PartitionId] [bigint] NULL

  , [AllocUnitId] [bigint] NULL

  , [RidDbId] [smallint] NULL

  , [RidPruId] [smallint] NULL

  , [File] [smallint] NULL

  , [Page] [int] NULL

  , [Slot] [int] NULL

  , [RefDbId] [smallint] NULL

  , [RefPruId] [smallint] NULL

  , [RefFile] [smallint] NULL

  , [RefPage] [int] NULL

  , [RefSlot] [int] NULL

  , [Allocation] [smallint] NULL

  );

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]