Creating Powershell to run a push data set over SQL DB

[DebbieE]

I have created a push dataset for real time data straming in Power BI Service

Using the how to guides Im creating Powershell to run it.

However Im getting errors so if anyone knows Powershell and can see what Im doing wrong....

Its when I create the query variable

$query = '
CREATE @TableName Varchar(200) = ('dim.Realtimedatatestingcustomer');
DECLARE @MaxUpdateDate DateTime = (SELECT MAX(UpdateDate) FROM [dim].[RealTimedatatestingcustomerDE]);
DECLARE @OldUpdateDate DateTime = (SELECT ISNULL([UpdateDate],0) FROM audit.RealTimeDataAudit WHERE TableName = @TableName);

UPDATE audit.RealTimeDataAudit
SET [UpdateDate] = @MaxUpdateDate
WHERE TableName = @TableName;

SELECT [CustKey], [Name], [UpdateDate] FROM [dim].[RealTimedatatestingcustomerDE]
WHERE [UpdateDate] = @MaxUpdateDate; '

Essentially it seems to be the where part thats causing problems

At line:20 char:38
+ ... CREATE @TableName Varchar(200) = ('dim.Realtimedatatestingcustomer');
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Unexpected token 'dim.Realtimedatatestingcustomer');

Ive tried adding it without () and I got the same error

I tried adding it  "dim.Realtimedatatestingcustomer" but that gives you a SQL error

Basically Im not sure how to add a Where = '' in the Powershell

Any advice would be gratefully recieved

[mwegener · Accepted solution]

Hi @DebbieE ,

try this.

$query = @'
CREATE @TableName Varchar(200) = ('dim.Realtimedatatestingcustomer');
DECLARE @MaxUpdateDate DateTime = (SELECT MAX(UpdateDate) FROM [dim].[RealTimedatatestingcustomerDE]);
DECLARE @OldUpdateDate DateTime = (SELECT ISNULL([UpdateDate],0) FROM audit.RealTimeDataAudit WHERE TableName = @TableName);


UPDATE audit.RealTimeDataAudit
SET [UpdateDate] = @MaxUpdateDate
WHERE TableName = @TableName;

SELECT [CustKey], [Name], [UpdateDate] FROM [dim].[RealTimedatatestingcustomerDE]
WHERE [UpdateDate] = @MaxUpdateDate; '@

https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_quoting_rul...