1

I created a stored procedure that I got on Erin Stellato's course on Pluralsight to report if there are any configuration changes on the server as a nightly job as follows:

CREATEPROCEDURE dbo.usp_SysConfigReport1 
(
    @RecentDate DATETIME,
    @OlderDate DATETIME
)

AS

BEGIN;
    IF
    @RecentDate IS NULL
    OR @OlderDate IS NULL
    BEGIN;
    RAISERROR('Input parameters cannot be NULL', 16, 1);
RETURN;
END;
SELECT  
        @@SERVERNAME ServerName,
        CaptureDate,
        [O].[Name], 
        [O].[Value] AS "OlderValue", 
        [O].[ValueInUse] AS"OlderValueInUse",
        [R].[Value] AS "RecentValue", 
        [R].[ValueInUse] AS "RecentValueInUse"

    FROM [dbo].[ConfigData] O

    JOIN

        (SELECT [ConfigurationID], [Value], [ValueInUse]

        FROM [dbo].[ConfigData]

        WHERE [CaptureDate] = @RecentDate) R on [O].[ConfigurationID] = [R].[ConfigurationID]

    WHERE [O].[CaptureDate] = @OlderDate

    AND (([R].[Value] <> [O].[Value]) OR ([R].[ValueInUse] <> [O].[ValueInUse]))

END


--------
--Capture the capture dates into #temp table1
SELECT  DISTINCT
[CaptureDate]
INTO #CapturedDates
FROM [dbo].[ConfigData]
ORDER BY [CaptureDate]

    --Create temp table2
CREATE TABLE #CapturedDates2 (
         ServerName NVARCHAR(20)
        ,[CaptureDate] DATETIMEOFFSET
        ,Name NVARCHAR(1000)
        ,[OlderValue] INT
        ,[OlderValueInUse] INT
        ,[RecentValue] INT
        ,RecentValueInUse INT   
)


------Inserting into Temp2 gin=ving error message as below

DECLARE @MINCapturedDate DATETIME
DECLARE @MAXCapturedDate DATETIME

SELECT @MINCapturedDate =(SELECT CAST(MIN([CaptureDate]) AS DATETIME) FROM #CapturedDates)
SELECT @MAXCapturedDate =(SELECT CAST(MAX([CaptureDate]) AS DATETIME) FROM #CapturedDates)

INSERT INTO #CapturedDates2
EXEC SYSDBA.dbo.usp_SysConfigReport1 @MINCapturedDate ,@MAXCapturedDate

But it gives me the following error message:

Msg 257, Level 16, State 3, Procedure usp_SysConfigReport1, Line 19 Implicit conversion from data type sql_variant to int is not allowed. Use the CONVERT function to run this query.

EDIT: Include definition of ConfigData

CREATE TABLE [dbo].[ConfigData] 
   ( [ConfigurationID] [INT] NOT NULL, 
     [Name] [NVARCHAR](35) NOT NULL, 
     [Value] [SQL_VARIANT] NULL, 
     [ValueInUse] [SQL_VARIANT] NULL, 
     [CaptureDate] [DATETIME] NULL ) ON [PRIMARY] 
2
  • Could you add the table definition with datatypes for the dbo.ConfigData table?
    – RLF
    Oct 1, 2015 at 14:22
  • Here is the definition of the config data table:USE [sysdba] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConfigData]') AND type IN (N'U')) BEGIN CREATE TABLE [dbo].[ConfigData]( [ConfigurationID] [INT] NOT NULL, [Name] [NVARCHAR](35) NOT NULL, [Value] [SQL_VARIANT] NULL, [ValueInUse] [SQL_VARIANT] NULL, [CaptureDate] [DATETIME] NULL ) ON [PRIMARY] END GO ALTER AUTHORIZATION ON [dbo].[ConfigData] TO SCHEMA OWNER GO
    – SQLBen
    Oct 1, 2015 at 14:51

1 Answer 1

1

As you can see from the definition you provided for [dbo].[ConfigData], both Value and ValueInUse are defined as SQL_VARIANT data types. The error message you are receiving is:

Msg 257, Level 16, State 3, Procedure usp_SysConfigReport1, Line 19 Implicit conversion from data type sql_variant to int is not allowed. Use the CONVERT function to run this query.

So Value and ValueInUse are SQL_VARIANT (as also are the derived values for OlderValue, OlderValueInUse, RecentValue, and RecentValueInUse) but you want to use them as INT.

The error message is saying that you need to change the SQL_VARIANT into INT by using the CONVERT function. An example of selecting a SQL_VARIANT and turning it into an INT.

SELECT CONVERT (int, Value) FROM [dbo].[ConfigData]

The CONVERT changes the datatype (if possible) from SQL_VARIANT to INT.

Just make sure to CONVERT to the proper data type and all should be well.

1
  • Thanks a million, I spotted the data type, the moment I scripted out the table definition. Thanks for shedding light into this.
    – SQLBen
    Oct 1, 2015 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.