Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
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.
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
5 | |
4 | |
2 | |
2 | |
2 |
User | Count |
---|---|
18 | |
15 | |
4 | |
4 | |
4 |