When running the identical stored procedure via Metabase and direct SQL queries separately, the output returned by Metabase is inaccurate.
{
"browser-info": {
"language": "zh-CN",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36",
"vendor": "Google Inc."
},
"metabase-info": {
"databases": [
"mysql",
"sqlserver"
],
"run-mode": "prod",
"plan-alias": "",
"version": {
"date": "2026-04-21",
"tag": "v0.60.1.6",
"hash": "94d5b9e"
},
"settings": {
"report-timezone": "Asia/Shanghai"
},
"hosting-env": "unknown",
"application-database": "mysql",
"application-database-details": {
"database": {
"name": "MariaDB",
"version": "12.3.1-MariaDB"
},
"jdbc-driver": {
"name": "MariaDB Connector/J",
"version": "2.7.10"
}
}
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "21.0.10+7-LTS",
"java.vendor": "Red Hat, Inc.",
"java.vendor.url": "https://www.redhat.com/",
"java.version": "21.0.10",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "21.0.10+7-LTS",
"os.name": "Linux",
"os.version": "5.14.0-611.36.1.el9_7.x86_64",
"user.language": "en",
"user.timezone": "Asia/Shanghai"
}
}
Are you connecting as the same database user that Metabase is using?
The stored procedure on the external MSSQL server runs correctly in raw SQL, but returns wrong results when executed in Metabase.
we’ve seen this in the past, check the connection strings to the database
How do I check the database connection? What parameters can cause connection issues?Thank you.
I suspect there is a mismatch of timezones, or a difference in users results in different access to data, especially if RLS is in use in the database.
Does any of the data that the stored procedure accesses have columns of type datetime2?
I use same user both them, stored procedure has some type DATE . I looked up some information. Initially, I suspected the issue was related to datetime, so I modified the stored procedure to only output a final result without datetime. However, the output was still incorrect.
Here the stored procedure
CREATE PROCEDURE dbo.Cust_sp_BI_GetPersonWorkHoursByDept
@BeginDate DATE,
@EndDate DATE
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#lzk_person_bi_by_dept') IS NOT NULL DROP TABLE #lzk_person_bi_by_dept;
IF OBJECT_ID('tempdb..#bi_person_all_by_dept') IS NOT NULL DROP TABLE #bi_person_all_by_dept;
IF OBJECT_ID('tempdb..##BIPersonWorkHoursFinalResultByDept') IS NOT NULL DROP TABLE ##BIPersonWorkHoursFinalResultByDept;
SELECT
lzk.per_code,
lzk.per_cdcode,
lzk.per_name,
lzk.per_zhu,
lzk.per_flag
INTO #lzk_person_bi_by_dept
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY per_name, per_iden ORDER BY per_lzdate DESC) AS rowNo,
*
FROM [Rabbit].[kqdata].[dbo].[lzk]
) lzk
LEFT JOIN [Rabbit].[kqdata].[dbo].[personal] personal
ON personal.per_name = lzk.per_name
AND personal.per_iden = lzk.per_iden
WHERE lzk.rowNo = 1
AND personal.per_code IS NULL;
SELECT
per_code,
per_cdcode,
per_name,
per_zhu,
per_flag
INTO #bi_person_all_by_dept
FROM [Rabbit].[kqdata].[dbo].[personal]
UNION ALL
SELECT
per_code,
per_cdcode,
per_name,
per_zhu,
per_flag
FROM #lzk_person_bi_by_dept;
SELECT
p.per_zhu AS dpt,
SUM(ar.att_gzsj) + SUM(ar.att_jbsj) AS WORK_HOURS
INTO ##BIPersonWorkHoursFinalResultByDept
FROM [Rabbit].[kqdata].[dbo].[att_reco] ar
INNER JOIN #bi_person_all_by_dept p
ON ar.att_code = p.per_code
WHERE
CONVERT(DATE, ar.att_date, 23) BETWEEN @BeginDate AND @EndDate
AND p.per_zhu IN ('总装1部','总装2部','五金部','注塑部')
AND p.per_flag NOT IN ('固薪', '月薪')
GROUP BY
p.per_zhu;
SELECT * FROM ##BIPersonWorkHoursFinalResultByDept;
END
What is the data type of [Rabbit].[kqdata].[dbo].[att_reco].[att_date]?
data type of [Rabbit].[kqdata].[dbo].[att_reco].[att_date] is smalldatetime
One thing that could be impacting this is row limits. I don’t know how Metabase applies those to SQL Server, though, and if those apply to things like temp table queries in stored procedures. But if the inner queries in the SP aren’t returning all the rows, that would impact the calculation.
