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.
(1) Answer
Hello,
Good to know that helped!
There is a preset limit of 10000 rows to prevent large databases from overflowing Spotfire.
Here is a snippet from the Analyst help-
Import data table-Select this option to bring the data in the selected data table into Spotfire's internal data engine (in-memory analysis).
Keep data table external -Select this option if you want the data in the selected data table to remain in-database.
1 Comment
I was aware of that - but what I wanted to do was apply the filter at the database I guess. I did not want to get into scripting though.
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'