Error encountered near the keyword 'user'

[RogerSteinberg]

Hi,

The following query ran in SSMS works perfectly. It takes every table name from a database and looks up the most recent  value from the last_update_stamp column of each table. 

My query:

drop table if exists ##productUpdate
drop table if exists ##temp

CREATE TABLE ##productUpdate([table_name] varchar(30),[last_update_stamp] datetime)
   
SELECT [table_name], ROW_NUMBER() OVER (Order by [table_name] asc) as rowid 
INTO ##temp 
FROM (
	select distinct [name] as [table_name]
	from sys.Objects 
	) as a


declare @count int, @rowMax int, @date datetime, @sql nvarchar(max);
set @count = 1
set @rowMax =(SELECT Max(rowid) FROM ##temp);

WHILE @count <= @rowMax
    BEGIN
        set @sql = 'SELECT @x = MAX(last_update_stamp) FROM '+(SELECT [table_name] FROM ##temp WHERE rowid=@count)+';'
        exec sp_executesql @sql, N'@x datetime out', @date out
        INSERT INTO ##productUpdate SELECT (SELECT [table_name] FROM ##temp WHERE rowid=@count), @date
        set @count=@count+1
    END

SELECT * FROM ##productUpdate

I get the following error message: Details: "Microsoft SQL: Incorrect syntax near the keyword 'user'."

I made sure to use Import connection with my SSMS db.

Any way to solve this ?

[parry2k]

@RogerSteinberg reason you posted this question here this in power bi forum is that you are running these sql statements in Power Query? not sure how your question is relevent to Power BI.

[RogerSteinberg]

well its relevant to power bi because it works when running it in ssms but not in power bi 

maybe its in the wrong category. i created the question in the power query forum instead. thank you !

[parry2k]

@RogerSteinberg are you doing all these steps in Power Query?

[RogerSteinberg]

no just when I follow the Get Data steps with SQL Server DB . ANd i insert the query in the query box.

[parry2k]

@RogerSteinberg I don;t think you can have seperate sql statement in the query, probably better to create a stored procedure and execute that in Power BI.