Can you share your Initial SQL statement, perhaps? Or at least outline that is it doing?
It it calling a stored procedure? Executing more than one statement? Uses any variables?
1 of 1 people found this helpful
No worries, here is the statement:
DECLARE @MeasureClientID int
DECLARE @MeasureSQL VARCHAR(MAX)
DECLARE @ClientServer nvarchar(255)
DECLARE @ClientDatabase nvarchar(255)
DECLARE @ClientID int
DECLARE myCursor CURSOR FAST_FORWARD FOR
FETCH NEXT FROM myCursor INTO @MeasureClientID
WHILE (@@FETCH_STATUS = 0)
SET @ClientID = (SELECT ClientID FROM tblMeasureClient WHERE MeasureClientID = @MeasureClientID)
SET @ClientServer = (SELECT [Server] FROM tblClient WHERE ClientID = @ClientID)
SET @ClientDatabase = (SELECT DatabaseName FROM tblClient WHERE ClientID = @ClientID)
SET @MeasureSQL =
WHERE MeasureGroupID =
WHERE MeasureClientID = @MeasureClientID)
FETCH NEXT FROM myCursor INTO @MeasureClientID
Essentially I am creating a dashboard that checks to ensure the data flow coming from our clients is stable. I have a table that contains 'Measures' which are essentially just 'checks'. Each check has an SQL query stored in the table tblMeasureSQL.
This cursor just runs through the 'Measures' for each client and runs the SQL stored in tblMeasureSQL.
By the way, each SQL query in tblMeasureSQL is stored as an INSERT statement, which insert into a table called logMeasureCheck.
This table is then referenced in the extract in Tableau.
The weird part is that the cursor query works just find when running in SQL Server...
Brenan Mackas, can you take a look, please?
Luke Beacon, have you looked at log files yet? (log.txt)
Statement looks straightforward enough (i.e. there's nothing to substitute and it'll be blindly passed to connection) so only thing comes to mind is the fact that Tableau creates multiple connections to the database and each one of them will execute Initial SQL statement. Concurrency issues, perhaps?
I have looked in log.txt and tabprotosrv.txt (as I am using a live connection) and I can't see anything obvious.
17 queries are running correctly and it seems to fail on the 18th. That number does not ring any bells to me.
Fail how? Which query? Your Initial SQL statement or some other query from the dashboard?
The Initial SQL query completes and the live connection doesn't give an error. But the Initial SQL runs through a CURSOR and runs INSERT statements. It runs the first 17 insert statements successfully (I can see them in the log table that I insert into), but then the remaining queries don't seem to execute at all.... I don't know if they are failing, or if they are not being started at all.
But this is something that would happen on your database - nothing to do with Tableau as all Tableau does is sending that one big blob of a statement and waits for it to come back...
How long does it take to execute that statement? Milliseconds?
Any way you could execute two of them at the same time?
That is the perplexing part... When I run it through SQL Server Mgmt Studio (with the same Windows Authentication), it works perfectly. It's only failing in Tableau.
It takes about 15 seconds to execute, mainly because the queries it is executing are looking at 100mil row tables.
I tried adding WITH (NOLOCK) to all of the FROM clauses with no luck...
There is some kind of setting difference between the 'user' executing the query from Tableau and when I execute it from SQL Server Mgmt Studio... I will fiddle around with a few user settings to see if it is that.
There seems to be some issue with the number of INSERT statements the Initial SQL (or Tableau in general) can execute in one sitting.
I changed the cursor to execute just one bit sql query as a string... still didn't work.
I changed the cursor to a Loop... still nothing.
I'm going to chalk this up to an issue with Tableau and possibly investigate further by contacting Tableau directly.
If I ever find a solution, I will post it here for you people from the future reading this right now.
If I haven't posted a solution here, then ******!
I am experiencing a similar issue with Initial SQL and MSSQL. I too have a loop that writes one row to a temp table with each loop. The loop should execute 159 times as designed. It runs fine within SSMS but as an Initial SQL statement the loop only executes 14 times. The Custom SQL statement that selects all from the temp table only returns 14 rows. My loop executes a stored procedure into a 3rd party application and the procedure is locked from visibility. I have noticed that if I change one of the options to the stored procedure it can change the number of successful loops up to 27. I am curious if you ever found a solution. My gut is telling me that perhaps I need some sort of option in the initial SQL to suppress any warnings or null behavior warnings that may be being generated from within the stored procedure that I don't have any control of. The behavior I experience is the same from Tableau Desktop and as a published, refreshing Extract on Tableau Server.
I did not find a solution and ended up just running the initial sql code via our ETL scheduling program.
It was a bit of a let down since Initial SQL seemed so promising. There is probably some setting buried deep within Tableau's settings that I am not aware of...