If you connect to a stored procedure which returns a result set, I don't think this should be a problem. So rather than using 'custom sql', just create a stored procedure and select it from the list of objects.
The stored procedure should 'do all your stuff' then return a result set.
You'll need to share the stored procedure code, it's probably an issue with you not specifying SET NOCOUNT ON at the beginning or you aren't returning the result set correctly. Or as the error says, the data in one of your return types isn't valid.
Dynamic / changing result sets should be fine. I just tested this procedure and it works OK:
create procedure dbo.test (@param1 int)
set nocount on;
SELECT 1 as Field1, 2 as Field2
SELECT 1 as Field1, 2 as Field2, 'Test' as Field3
With that being said, Tableau isn't going to like the fact Field3 was there, then it isn't there. You're going to see errors in the report. I wouldn't suggest serving up a dataset where the columns change.
So this is the stored procedure.
Declare @DBNameTable table (DatabaseName varchar (max))
insert into @DBNameTable
select distinct [database_name]
Declare @DBName varchar (max) =''
Select @DBName += QUOTENAME(DatabaseName)+','
Set @DBName = left(@DBName, len(@DBName)-1)
Declare @SQL Nvarchar (max)
(select convert(date,pkdate) as PKDate
,convert(date,[backup_finish_date]) as DBBackupFinishDate
from [PERSISTENT].[dbo_backupset] a
inner join [INFO].[Calendar] b on convert(date,a.[backup_finish_date]
) = b.PKDate) a
in (' +@DBName+
execute sp_executesql @sql
and I agree with you, but the databases in our environment are not static. We can add them and delete them at any given time. This is the original problem I am trying to solve.
1 of 1 people found this helpful
You aren't using SET NOCOUNT ON; at the start of the stored procedure. That should do it, but I can't guarantee it will wotk as the sp_executesql might be tripping Tableau out. I also can't tell from any of that what the datatypes are etc.
Changing columns in your result set is going to make it extremely difficult to create reports in Tableau. One minute you have a 'Customer' column and you build off of that, next minute it's gone - the report is broken until you remove that column from the report.
If I were you, I would create one master database with a table (or set of tables) which you populate using a stored procedure to connect to your other databases.
That way you can connect Tableau to one set of tables where the structure doesn't change.
Well you were right about the no count on! the problem is that I now have too many columns for tableau . oh well! thanks for your help!
Yeah, that was my original plan, but after much trial and error I dont see how I can keep it deep. I'll keep exploring and trying though. Thanks so much for your help!
Continuing my thoughts in your other thread.