wow it's awesome, however, I got this error when I tried to connect my store proc "Unable to complete action The "[dbo].[Data_Weekly]" stored procedure returned no results. The fields don't have column names, or the data in the fields is not a supported data type. Unable to connect to the Microsoft SQL Server server "Server1". Check that the server is running and that you have access privileges to the requested database."
OMG thank you so so much. I just had a business user ask about turning their live connection to an extract with stored procedures and this is exactly what I was looking for! Thank you thank you
Hi Donabel, I'm using quite complex SQL queries and finding that queries that work in Oracle SQL Developer fail in Tableau Custom SQL, generating various generic and unspecific errors. Other than Tableau not liking a semicolon at the end of a query, do you have any other tips or a syntax comparison?
Hi Tim, I believe we need to use only a single statement in the custom SQL (might need to double check). Usually what I do if I need a custom SQL which has more complicated query, I convert to either Stored Proc, or View, or Table Valued function and use those in the Custom SQL instead. For example my custom SQL might look like: SELECT * dbo.my_new_function_that_does_everything(some params)
@@sqlbelle Thank you for the feedback! Here are my notes re what I've picked up by dumb trying stuff out: - Oracle MS SQL Developer is much more tolerant than Tableau. A query that works here will not necessarily work in Tableau. - Remove the ; at the end of the query - Use the double quote, not single quote for naming fields using the AS command. Should you be building up a long list of fields and renaming them in Excel, use CHAR(34), e.g.: =A2&" AS "&CHAR(34)&F2&CHAR(34)&"," - If you're using Excel like this, don't forget to remove the last comma before "FROM"! - SQL Developer seems to be tolerant of multiple fields receiving the same name using AS. Tableau is not. Make sure that they are all unique. - Tableau wraps further SQL around the Custom SQL, which can cause performance problems. Once I'm finished, I'll be requesting views from the DB manager to match the custom SQL queries that I've built. For further tips on custom SQL and performance impacts, see this article by Emre Oktay at the Information lab: (sorry links get comments deleted by RU-vid)
I feel the use of stored procedures is very limited in Tableau. How do you use a stored procedure in Tableau that creates a temp table inside the procedure and then returns a select statement from the that temp table?? Is it even possible in Tableau??
This awesome. I love the fact that you have created a database for Superstore data. Can you share the database script with us. Thank you. You are amazing!!!
Thank you Eric. I will be putting together some resources I used in videos - I will post them in sqlbelle.com. They're not there yet, but hoping to do so this week. I'll make a post once they're there. Thank you for your comments and support!
Hello Naveen - I'd say it's always it depends. It depends on what the Custom SQL is, and it depends on whether a stored procedure is a sufficient alternative. There are benefits to using stored proc as database engines tend to optimize these more; however they do pose restrictions when used with Tableau (ie can only do live connections)
