Incorrect syntax in Power BI - but query works fine in SSMS?

[krypto6969]

Hi,

I'm getting this error when trying to use a direct query

But when I run my proc in SSMS it is just fine - I can't figure out why POwer BI doesn't like it.

Here's the statement I am trying to use:

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

[karlwelday]

Power BI is objecting to your query becuase it is using direct query mode.  Try switching to import mode.

[JimmyGibson]

Thank you! 

Somehow, I checked that box and should not have.

You saved me a lot of time!

[Anonymous]

Hi,

I had same problem with this query:

let
UserNameSQL = Sql.Database("Servername","DatabaseName",[Query="EXEC [proj].[TestOrganisation2]"])

in
UserNameSQL

but i solved it by changing it to this:

let
UserNameSQL = () =>
Sql.Database("Servername","DatabaseName",[Query="EXEC [proj].[TestOrganisation2]"])

in
UserNameSQL

Hope it can help you guys 🙂

regards

[vissvess]

But what is this in case of Direct query..!
This converting the query into function and then invoking to a query and loading is converting the query to import mode..

What should I do for Direct query.

[Anonymous]

Brilliant! THank you!

[v-sihou-msft]

@krypto6969

Power BI Desktop has issues on parsing Queries in Direct Query mode. It always resolves the front end input SQL statement as a derived table so that statements like DECLARE, CTE will cause syntax error. We have reported this issue internally. Please refer to similar threads below:

http://community.powerbi.com/t5/Desktop/Why-is-DECLARE-not-supported-Error/m-p/40983#U40983

http://community.powerbi.com/t5/Desktop/Custom-query-with-CTE-not-supported-bug/m-p/41395#U41395

Regards,

[Seth_C_Bauer]

@krypto6969 It is better not to create multiple threads asking the same question, if you really need to, add the link to previous threads you've created so others don't start the same recommendations you may have already gotten.

To link to your other thread with the same question - http://community.powerbi.com/t5/Desktop/Can-t-use-Temp-Tables-really/m-p/45167#M17506

Did you try the recommended solutions in there? Obviously there is a limitation to what you can, or how you can query SQL using Direct Query.  Stored Procedures don't appear to work, nor does creating temporary objects and populating them.

There are other solutions to your problem. 1) import using the recommendations in the other thread 2) Re-structure your query to not use temporary tables (write sub queries, use CTE's, etc)

[Habiber]

Try to break the query into pieces and run it through PowerBI one at a time, in order to identify where the exact issue is occuring