Trying to run below query from Ado.net connection but getting error
-
Hi, I am trying to run below query from C# code using ADO.Net but I am getting an error "System.Data.SqlClient.SqlException: 'Incorrect syntax near '.'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon." This query is working fine with Sql server query window.
SELECT DISTINCT CONVERT(DECIMAL(18, 2) , user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )) AS [index_advantage] ,
migs.last_user_seek ,
mid.[statement] AS [Database.Schema.Table] ,
mid.equality_columns ,
mid.inequality_columns ,
mid.included_columns ,
migs.unique_compiles ,
migs.user_seeks ,
migs.avg_total_user_cost ,
migs.avg_user_impact ,
OBJECT_NAME(mid.[object_id]) AS [Table Name] ,
p.rows AS [Table Rows]
FROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK )
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK ) ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK ) ON mig.index_handle = mid.index_handle
INNER JOIN sys.partitions AS p WITH ( NOLOCK ) ON p.[object_id] = mid.[object_id]
WHERE mid.database_id = DB_ID()
ORDER BY index_advantage DESC
OPTION ( RECOMPILE );So to fix this issue I added ; before With but still it's not working saying some error near '.'. Can someone please help here. I am using below code to call this query:
SqlCommand sqlCommand = new SqlCommand(query, connection);
connection.Open();
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader()Thanks