question

herculeon avatar image
0 Votes"
herculeon asked herculeon answered

incorrect syntax near "server name\instance" expecting '(' or select

I am still trying to learn/teach myself sql. I am adjusting a SCOM dashboard but 1 query gives an error. I am not sure why. I asked my DBA and he said for me to ask google. I get incorrect syntax near "server name\instance" expecting '(' or select at sql.Database("OPSMGR2016DW\OPSDBDW" I tried but still cannot figure it out. the full statement is:

let
Source = Sql.Database("OPSMGR2016DW\OPSDBDW", "OperationsManagerDW", [Query="SELECT DISTINCT DB_NAME(dovs.database_id) DBName,#(lf)mf.physical_name PhysicalFileLocation,#(lf)dovs.logical_volume_name AS LogicalName,#(lf)dovs.volume_mount_point AS Drive,#(lf)CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB#(lf)FROM sys.master_files mf#(lf)CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs#(lf)Where DB_NAME(dovs.database_id) LIKE '%OperationsManager%'"]),
#"Renamed Columns" = Table.RenameColumns(Source,{ {"DBName", "DB Name"}, {"PhysicalFileLocation", "Physical File Location"}, {"LogicalName", "Logical Name"}, {"FreeSpaceInMB", "Free Space (MB)"}})
in
#"Renamed Columns"

Please point me in the direction of what the proper format is.

I also get the same thing in the area of Table.RenameColumns(Source,{ of incorrect syntax near 'Source'. Expecting '(' or Select

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You will need to re-tag this to address SCOM people. While SCOM may be using SQL Server, the above is something SCOM-specific, I assume.

However, I followed the advice of your DBA and used Google, and I found https://thedevopshub.com/operations-manager-distributed-application-health-visual-in-power-bi-with-awesome-status-icons/ and scroll down to point 3.

Then again, that appears to be related to Power BI (another component I don't know), and you said nothing about PowerBI.

0 Votes 0 ·
LukasYu-msft avatar image
0 Votes"
LukasYu-msft answered

The M query looks all right to me, I would suspect the #(If) part in the sql query. We must make sure the query works in SQL level. Could you explain what the #(If) intended to do ?

SELECT DISTINCT DB_NAME(dovs.database_id) DBName,#(lf)mf.physical_name PhysicalFileLocation,#(lf)dovs.logical_volume_name AS LogicalName,#(lf)dovs.volume_mount_point AS Drive,#(lf)CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB#(lf)FROM sys.master_files mf#(lf)CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs#(lf)Where DB_NAME(dovs.database_id) LIKE '%OperationsManager%'


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

herculeon avatar image
0 Votes"
herculeon answered

I am sorry to not answer again. I did not get notified about a response. I was going for an end goal of trying to check all of our servers free space and query the scom database using powerbi. I ended up trashing this query and rewriting it and it came out differently, but it worked.
The query I used is let
Source = Sql.Database("OPSMGR2016DW\OPSDBDW", "OperationsManagerDW", [Query="select *, FullName, ObjectName, CounterName, InstanceName, SampleValue, DateTime from (#(lf)#(lf)SELECT Path, FullName, ObjectName, CounterName, InstanceName, SampleValue, DateTime, ROW_Number() Over (Partition by fullname order by datetime desc) rn#(lf)FROM#(lf)Perf.vPerfRaw pvpr #(lf)inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId #(lf)inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId #(lf)inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId #(lf)WHERE CounterName = '% Free Space') tmp#(lf)WHERE rn=1"]),
#"Sorted Rows" = Table.Sort(Source,{ {"SampleValue", Order.Ascending}})

in
#"Sorted Rows"

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.