Has anyone else had difficulties with BOOLEAN type in MS SQL Server Databases?
We have a Boolean column that has True and False as values when you look at the data.
We use the field to filter the data.
Accessing the table externally results in a large number of the records not filtering correctly according to the field contents.
Importing the table results in the filter working correctly.
We are using Tibco Spotfire 7.6.
2 Comments
Thanks for the tip:
It is due to the External Query automatically limiting the results to the Top 10,000 results in the background.
Imported Table
CAST(CASE WHEN "dbo#InspectionFaultCategory"."Archived" > 0 THEN 1 ELSE 0 END AS BIT) AS "Archived", "dbo#InspectionFaultCategory"."ParentInspectionFaultCategoryID" AS "ParentInspectionFaultCategoryID" FROM "dbo#InspectionFaultCategory" AS "dbo#InspectionFaultCategory"'
External Table
SqlServer query: SELECT TOP(10000) CAST(CASE WHEN [InspectionFaultCategory].[Archived] > 0 THEN 1 ELSE 0 END AS BIT)
Shame, it is much faster using the external link because it does not load the data when the analytic is loaded.
Hello,
Please enable debug logging in the Spotfire client and look for any difference between the queries fired for external vs in-db connections-
eg: When data table is external a query similar to this would be logged.It shows the query ,the parameter values as well as the type of parameters-
2017-10-04 23:19:00,071 [TF LowPriorityThreadPoolIdentifier] DEBUG Spotfire.Dxp.Data.Adapters.SqlServer.SqlServerAdapterConnection [(null)] - Running SqlServer query: SELECT TOP(10000) [q1].[DISPLAY_NAME] AS [Column_1_1], [q1].[LAST_LOGIN] AS [Column_1_2], [q1].[LAST_MODIFIED_MEMBERSHIP] AS [Column_1_3], [q1].[LOCKED_UNTIL] AS [Column_1_4], [q1].[PASSWORD] AS [Column_1_5], [q1].[PRIMARY_GROUP_ID] AS [Column_1_6], [q1].[USER_ID] AS [Column_1_7], [q1].[USER_NAME] AS [Column_1_8], CAST(0 AS BIT) AS [Column_1_9], [q1].[DOMAIN_NAME] AS [Column_1_10], [q1].[EMAIL] AS [Column_1_11], [q1].[ENABLED] AS [Column_1_12], [q1].[EXTERNAL_ID] AS [Column_1_13] FROM (select * from users where enabled=@p1) AS [q1]
2017-10-04 23:19:00,071 [TF LowPriorityThreadPoolIdentifier] DEBUG Spotfire.Dxp.Data.Adapters.SqlServer.SqlServerAdapterConnection [(null)] - Parameters:
2017-10-04 23:19:00,071 [TF LowPriorityThreadPoolIdentifier] DEBUG Spotfire.Dxp.Data.Adapters.SqlServer.SqlServerAdapterConnection [(null)] - Name = 'p1'; Value = '0'
2017-10-04 23:19:00,071 [TF LowPriorityThreadPoolIdentifier] DEBUG Spotfire.Dxp.Data.Adapters.SqlServer.SqlServerAdapterConnection [(null)] - Added prepared statement parameter 'p1' of type 'Int'