Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ?
I can't find the the keyword 'user' from your query.
You could use CTE or Stored procedure as a workaround.
https://community.powerbi.com/t5/Desktop/Custom-query-with-CTE-not-supported-bug/td-p/41395
Stored procedure parameter call with power bi
Execute SQL Server Stored Procedure With User Parameter In Power BI
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
that's precisely my point. i have no 'user' keyword and it spits out this error message..
Im pretyty sure the issue somewhere inside storage procedure
sp_executesql
do not hesitate to kudo useful posts and mark solutions as solution
Linkedin
how? its a built in sql function
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |