Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Using direct import to stored proc that uses temp table..get this error? WTF?
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.
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.
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,
You have to explicitly define the data types for the temp table.
Then it should work.
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)
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...
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
104 | |
99 | |
98 | |
38 | |
37 |
User | Count |
---|---|
158 | |
124 | |
75 | |
74 | |
63 |