Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
krypto6969
Helper I
Helper I

Can't use Temp Tables - really?

rr.png

Using direct import to stored proc that uses temp table..get this error? WTF?

9 REPLIES 9
Skotko
Advocate I
Advocate I

Is it possible you're using mixed storage modes (both Import and direct query) in your report?  In my experience using mixed (I use powerapps for direct query), even the tables I am actually just importing have the "direct query" radio button defaulted for some reason.  If so, switch the button back to 'import' and you should no longer have the problem.  I just tried it my issue (which is how i got to this forum) and it worked great.  The reason is that Direct Queries are super limited in what they can pull.

thephotobus
Frequent Visitor

Or the parser hack I previously learned in SSIS...

 

IF 1 = 0

    BEGIN

        SELECT

            'ColumnDefinition1' = CAST(NULL AS DATE), -- columns and data types in your final result set

            'ColumnDefinition2' = CAST(NULL AS NVARCHAR(75)) -- etc.

    END

 

/* Use temp tables until your heart is content */

 

/* Finish with a Select using the same definition as you defined in the top section */

Yep like ssis you have to give it a bit of help.

Anonymous
Not applicable

What about using a CTE instead of a temp table?  I know that works in PowerBI.

 

Please mark it as a solution or give a kudo if it works for you, otherwise let me know if you run into an issue and I'll do my best to assist. Go To bipatterns.com for more techniques and user guides.

 

Thanks,

Ryan Durkin

waltheed
Solution Supplier
Solution Supplier

You have to explicitly define the data types for the temp table.

Then it should work.

Cheers, Edgar Walther
ITsmart BI and Analytics consultant

I'm pretty sure I am doing that?

 

SET NOCOUNT ON
DECLARE	@CLAIMS int
DECLARE @from_date_entered	date
DECLARE @to_date_entered	date
----------------------------
SET @from_date_entered =  GETDATE() 
SET @to_date_entered	= GETDATE() 

--SET @from_date_entered = N'03/16/2016'
--SET @to_date_entered	= N'03/16/2016'
----------------------------
SELECT	@CLAIMS = batchtype_id
FROM	batchtype
WHERE	batchtype_ud = 'CLAIMS'

DECLARE @begin smalldatetime
SELECT @begin = CONVERT(smalldatetime, @from_date_entered)

DECLARE @end smalldatetime
SELECT @end = DATEADD (dd,1,CONVERT(smalldatetime, @to_date_entered))

DECLARE @begind smalldatetime
SELECT @begind = CONVERT(smalldatetime, @from_date_entered)

DECLARE @endd smalldatetime
SELECT @endd = CONVERT(smalldatetime, @to_date_entered)

DECLARE @CompanyName 	varchar(100)
SELECT 	@CompanyName = environment_info.company_name
FROM	environment_info


IF OBJECT_ID('tempdb..#rpt_data') IS NOT NULL
begin
        drop table #rpt_data
end
CREATE TABLE #rpt_data (CompanyName	varchar(100) 	NULL,
			Batch		char(3)		NULL,
			UserName	varchar(35)	NULL,
			SQLUserName	varchar (25)	NULL,
			claim_form_type_id	int	null, 
			Claim_type       varchar (10)   NULL,
			status_type	varchar(15)	NULL,
			each		int		NULL,
			member_last	varchar(35)	NULL,
			fromdate	smalldatetime	NULL,
			todate		smalldatetime	NULL)
			
INSERT INTO #rpt_data (	CompanyName,
			Batch,
			UserName,
			SQLUserName,
			claim_form_type_id,
			Claim_type,
			status_type,
			each,
			member_last,
			fromdate,
			todate )
			--countofentered as (select )


SELECT DISTINCT	@CompanyName,
	substring(batch_user.batch, 1, 3) as batch_init,
	batch_user.batch as user_name,
	batch_user.batch_sql_user_name,
	claim_form_type_id,--altered 11/25/02
	null,--altered 11/25/02
	claim_status.claim_status_ud,
	claim.claim_id,
	claim.member_last_name as member_last,
	@begind,
	@endd

FROM 	batch_user
	inner join batchtype
		on batch_user.batchtype_id = batchtype.batchtype_id,
	claim
	inner join claim_status
		on claim.claim_status_id = claim_status.claim_status_id
	
	
WHERE	len(batch_user.batch) = 3
	and substring(claim.claim_ud, 9, 3) = batch_user.batch
	and (claim.date_created < @end AND claim.date_created >= @begin)


GROUP BY  claim.claim_form_type_id, batch_user.batch_sql_user_name, batch_user.batch, claim_status.claim_status_ud, claim.member_last_name, claim.claim_id


INSERT INTO #rpt_data (	CompanyName,
			Batch,
			UserName, 
			SQLUserName,
			claim_form_type_id,
                       		Claim_type,
			status_type,
			each,
			member_last,
			fromdate,
			todate )

SELECT DISTINCT 	@CompanyName,
	substring(batch_user.batch, 1, 2) as batch_init,
	batch_user.batch as user_name,
	batch_user.batch_sql_user_name,
	claim_form_type_id,
	null,--altered
	claim_status.claim_status_ud,--altered
	claim.claim_id,
	claim.member_last_name as member_last,
	@begind,
	@endd

FROM 	batch_user
	inner join batchtype
		on batch_user.batchtype_id = batchtype.batchtype_id,
	claim
	inner join claim_status
		on claim.claim_status_id = claim_status.claim_status_id
	
	
WHERE	len(batch_user.batch) = 2
	and substring(claim.claim_ud, 9, 2) = batch_user.batch
	and (claim.date_created < @end AND claim.date_created >= @begin)


GROUP BY  claim.claim_form_type_id, batch_user.batch_sql_user_name, batch_user.batch, claim_status.claim_status_ud, claim.member_last_name,claim.claim_id



INSERT INTO #rpt_data (	CompanyName,
			Batch,
			UserName, 
			SQLUserName,
			claim_form_type_id,
                        		Claim_type,
			status_type,
			each,
			member_last,
			fromdate,
			todate )

SELECT DISTINCT	@CompanyName,
	substring(batch_user.batch, 1, 1) as batch_init,
	batch_user.batch as user_name,
	batch_user.batch_sql_user_name,
	null,--altered
	claim_status.claim_status_ud,--altered
	claim_status.claim_status_ud,
	claim.claim_id,
	claim.member_last_name as member_last,
	@begind,
	@endd

FROM 	batch_user
	inner join batchtype
		on batch_user.batchtype_id = batchtype.batchtype_id,
	claim
	inner join claim_status
		on claim.claim_status_id = claim_status.claim_status_id
	
	
WHERE	len(batch_user.batch) = 1
	and substring(claim.claim_ud, 9, 1) = batch_user.batch
	and (claim.date_created < @end AND claim.date_created >= @begin)
	

GROUP BY  claim.claim_form_type_id, batch_user.batch_sql_user_name, batch_user.batch, claim_status.claim_status_ud, claim.member_last_name,claim.claim_id

update #rpt_data
set claim_type = 'HCFA'
where (claim_form_type_id = 2 or claim_form_type_id is null)

update #rpt_data
set claim_type = 'UB92'
where (claim_form_type_id = 1)


  SELECT 
    CompanyName as Company, 
    Batch,
	USERNAME as [User Name],
	SQLUserName as [System User Name],
	--claim_form_type_id as [Claim Form Type],
	CASE WHEN [claim_form_type_id]  IS NULL  THEN ('N/A') END as [Claim Form Type],
	Claim_type as [Claim Type],--altered
	status_type as [Status Type],
	each as [Each],
	member_last as [Member Name],
	fromdate as [From Date],
	todate as [To Date],
  CASE WHEN [Status_Type] = 'Entered'  THEN 1 ELSE 0 END  AS Entered,
  CASE WHEN [Status_Type] = 'Approved' THEN 1 ELSE 0 END AS Approved,
 CASE WHEN [Status_Type] = 'Pending'  THEN 1 ELSE 0 END  AS Pending
  FROM #rpt_data

@krypto6969You could try making your insert logic dynamic. As an example, I've used the script outlined in this blog to pull in SQL metrics, so I know it works. But you would most likely do something utilizing EXEC against a variable or sp_executesql like here

 

Or.. I just figured this out. For some reason if you wrap your Sproc in a variable, it works. So just build a sproc for the below code and you can run it like this. (Import only, Direct Query doesn't work)

 

DECLARE @sqlCommand varchar(1000)

SET @sqlCommand = 'dbo.Testproc'
EXEC (@sqlCommand)

 


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

SPROC option worked from me. Thanks 🙂

What if using SPROC is not an option? where we want to use the data from production however for a temporary period to monitor and make a business case. 

Need to use temp tables to create some metrics and keep running it for few days/weeks.. can't dump all production data to other environments. Don't want to create/run jobs to load the metrics into actual tables. If temp tables can be used no more an issue.

 

Has anyone found a good solution about using temp tables, please share...

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.