asked
error Conversion failed when converting date and/or time from character string.
I have been stuck on this issue and can't figure out how to fix it. I have a stored procedure that is supposed show current tmp tables. I needed to add a create_date column into the table and as soon as I did that is where I get the error. Can you help? ..... SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[CRC_dbaTableInfo] as set nocount on; -- exec [dbo].[CRC_dbaTableInfo] DECLARE @TblNames Table ( COUNTER INT IDENTITY(1,1), tbl_name nvarchar(200) NULL, create_date datetime ) DECLARE @TableSizes AS TABLE ([TblName] VARCHAR(255), [NumRows] INT, [Reserved_Size] VARCHAR(20), [Data_Size] VARCHAR(20), [Index_Size] VARCHAR(20), [Used] VARCHAR(20), create_date datetime) DECLARE @ROWCOUNT INT DECLARE @I INT DECLARE @str nvarchar(100) DEclare @A datetime SET @I = 1 INSERT INTO @TblNames(tbl_name, create_date) SELECT t.create_date, s.NAME +'.'+t.name FROM hmxafo.sys.Tables t JOIN HMXafo.sys.Schemas s ON s.SCHEMA_ID = t.schema_id SET @ROWCOUNT = @@ROWCOUNT WHILE @I <= @ROWCOUNT BEGIN set @str = (select tbl_name FROM @TblNames WHERE COUNTER = @I) set @A =(select create_date from @TblNames where COUNTER =@I) INSERT INTO @TableSizes EXEC sp_spaceused @str, @A SET @I = @I +1 END SELECT *, cast(left(Data_Size, charindex(' ',Data_Size)) as decimal) / 1024 as [Data_Size MB] FROM @TableSizes where TblName like 'tmp%' or TblName like 'temp%' order by Data_Size desc -- exec dbo.crc_dbatableinfo
Is it the line: INSERT INTO @TableSizes EXEC sp_spaceused @str, @A that's causing problems? What I would do is change `@TableSizes` so it doesn't have the `created_date`, and get that information from @TableNames. If you must have the `created_date` field in `@TableSizes`, then create it as a NULLable field, change your `INSERT INTO...` statement to specifically insert into the columns that sp_spaceused returns, and then populate the `created_date` field afterwards.
6 People are following this question.
