Forum Discussion
sql_variant data type in Lookup activity
Hello
I’ve got a stored procedure that can return a student’s date of birth either as a date or as in bigInt (i.e. number of seconds from Jan 1 1970). The @outputValue in the procedure is a SQL_VARIANT data type
The date/ bigInt return value is based on the input parameter @returnType, see below ...
Trouble is Microsoft Fabric doesn’t seem to like SQL_VARIANT. I’m using the query below in a Lookup activity, but it keeps falling over because of the SQL_VARIANT
DECLARE @outputValue sql_variant;
EXEC [wm].[sp_GetStudentDoB]
@SchemaName = 'dbo',
@TableName = 'tblStudents',
@returnType = 'date', --BigInt
@outputValue = @outputValue OUTPUT;
SELECT CONVERT(DATETIME, @outputValue) AS NewDate;
Is there any work around for this?
Any suggestions much appreciated.
Thanks in advance
Roy
Hi ryand009 , Thank you for reaching out to the Microsoft Community Forum.
Microsoft Fabric doesn't support the sql_variant data type in pipeline activities like Lookup. When your stored procedure returns a sql_variant as an output parameter, Fabric fails to interpret it since it expects outputs to have a clearly defined, supported SQL type, like datetime, bigint, varchar, etc.
To work around this, you need to introduce a wrapper stored procedure. The wrapper should call your original procedure and explicitly convert the sql_variant output to a supported type before returning it. Instead of returning the value as an output parameter, the wrapper should return the final result using a SELECT statement. This way, Fabric can read the output as a standard, well-defined scalar value, which will work smoothly in your Lookup activity.
If you have control over the original stored procedure, the better long-term solution would be to avoid using sql_variant altogether and return the result directly with a SELECT, already cast to the appropriate type.
1 Reply
Hi ryand009 , Thank you for reaching out to the Microsoft Community Forum.
Microsoft Fabric doesn't support the sql_variant data type in pipeline activities like Lookup. When your stored procedure returns a sql_variant as an output parameter, Fabric fails to interpret it since it expects outputs to have a clearly defined, supported SQL type, like datetime, bigint, varchar, etc.
To work around this, you need to introduce a wrapper stored procedure. The wrapper should call your original procedure and explicitly convert the sql_variant output to a supported type before returning it. Instead of returning the value as an output parameter, the wrapper should return the final result using a SELECT statement. This way, Fabric can read the output as a standard, well-defined scalar value, which will work smoothly in your Lookup activity.
If you have control over the original stored procedure, the better long-term solution would be to avoid using sql_variant altogether and return the result directly with a SELECT, already cast to the appropriate type.