cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
krypto6969 Regular Visitor
Regular Visitor

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
waltheed
Advisor

Re: Can't use Temp Tables - really?

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

Then it should work.

Cheers, Edgar Walther
Power BI User Group Netherlands
krypto6969 Regular Visitor
Regular Visitor

Re: Can't use Temp Tables - really?

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

rdurkin Member
Member

Re: Can't use Temp Tables - really?

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

Super User
Super User

Re: Can't use Temp Tables - really?

@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)

 

Near SE WI? Join our PUG Milwaukee Brew City PUG
Mandr0id Frequent Visitor
Frequent Visitor

Re: Can't use Temp Tables - really?

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...

 

mattlazarus Frequent Visitor
Frequent Visitor

Re: Can't use Temp Tables - really?

SPROC option worked from me. Thanks :-)

thephotobus Frequent Visitor
Frequent Visitor

Re: Can't use Temp Tables - really?

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 */

stretcharm Senior Member
Senior Member

Re: Can't use Temp Tables - really?

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

Skotko Frequent Visitor
Frequent Visitor

Re: Can't use Temp Tables - really?

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.

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 51 members 1,044 guests
Please welcome our newest community members: