I have a select statement, but because I needed to create and drop a #temp table I’ve put it all into a stored procedure. It’s fairly simple with no parameters and it works perfectly in SQL Server Management Studio but in the Metabase question, it’s not accurate. It’s not displaying any errors it just seems to be ignoring the part of the where clause where it says:
and NOT IN (select TempTableName.item_no from TempTable)
I know there are multiple other ways to accomplish this in SQL but for some reason it only works with this method. I know I could just tweak the where clause, use CTE, etc etc.
Then in Metabase, the only text I have is: exec my_stored_proc_name.
I have successfully did almost the same thing with another question and it works perfectly. I’ve tried restarting Metabase. If anyone has any suggestions that would be great. I searched and found an old thread on stored procs but it didn’t really go anywhere.
Hi @ChuckD_843
Please post “Diagnostic Info” from Admin > Troubleshooting.
If Metabase only has exec my_stored_proc_name as the query, then all the other things must be errors on the SQL Server, which is then returned to Metabase.
Try looking in the query log of the SQL Server - perhaps you’re going to see more details there.
Could it be something to do with permissions - using one user for Metabase, but a different in Management Studio?
Hey Flamber, thanks for the reply. Running the stored proc in SSMS with logging into the same SQL User as Metabase works as intended, I will say it does take a good 5-10 seconds to run, but on the Metabase side whether it’s a question, dashboard, etc, it’s almost as if it’s just ignoring the NOT IN part of the Where Clause…but at the same time, all that is inside of the stored proc which executes without errors. Here is the Diagnostic Info:
Diagnostic Info
Please include these details in support requests. Thank you!
{
“browser-info”: {
“language”: “en-US”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 6.3; Win64; x64; rv:75.0) Gecko/20100101 Firefox/75.0”,
“vendor”: “”
},
“system-info”: {
“file.encoding”: “Cp1252”,
“java.runtime.name”: “Java™ SE Runtime Environment”,
“java.runtime.version”: “1.8.0_251-b08”,
“java.vendor”: “Oracle Corporation”,
“java.vendor.url”: “http://java.oracle.com/”,
“java.version”: “1.8.0_251”,
“java.vm.name”: “Java HotSpot™ Client VM”,
“java.vm.version”: “25.251-b08”,
“os.name”: “Windows Server 2012 R2”,
“os.version”: “6.3”,
“user.language”: “en”,
“user.timezone”: “America/Los_Angeles”
},
“metabase-info”: {
“databases”: [
“h2”,
“sqlserver”
],
“hosting-env”: “unknown”,
“application-database”: “h2”,
“application-database-details”: {
“database”: {
“name”: “H2”,
“version”: “1.4.197 (2018-03-18)”
},
“jdbc-driver”: {
“name”: “H2 JDBC Driver”,
“version”: “1.4.197 (2018-03-18)”
}
},
“run-mode”: “prod”,
“version”: {
“tag”: “v0.35.2”,
“date”: “2020-04-10”,
“branch”: “release-0.35.x”,
“hash”: “f3b2857”
},
“settings”: {
“report-timezone”: null
}
}
}
@ChuckD_843
Try another program that also uses JDBC connections for queries. Perhaps RazorSQL.
As far as I know, when using a SP, Metabase does not run the contents of that, but SQL Server does.
Which SQL Server version?
Ok, thanks, I will check that. It’s SQL Express, I can check the version in a bit but I believe it’s somewhat new. I believe it might have something to do with the “NOT IN” subquery part of the where clause. Even though you’re right, MB doesn’t really care what the Stored Proc is, that’s the only thing different with the other question that uses a stored proc - it does not have the NOT IN subqery and works fine.