ADO.NET query not showing in XEvents

Question

I sent a query to SQL Server using an ADO.Net SqlAdapter from PowerShell. The query returns the correct result. When I run an XEvent session with the sqlserver.sql_statement_starting event, the query from ADO.NET does not show up. Queries I sent from SSMS are shown immediately.

Is this a bug, or why do I not see the ADO.NET queries?

The code I am using is

$serverName = 'localhost'
$databaseName = 'Contoso Retail DW'
$schemaName = 'dbo'
$tableName = 'FactSalesFMCG'

$connString = Get-ConnectionString -IntegratedSecurity -Server $serverName -Database $databaseName 
$sqlConn = [System.Data.SqlClient.SqlConnection]::new($connString) 
$sqlConn.Open()
$columnMetadataAdapter = Get-ColumnMetadataAdapter -Conn $sqlConn -SchemaName $schemaName -TableName $tableName
$table = [System.Data.DataTable]::new()
$columnMetadataAdapter.Fill($table)
$sqlConn.Close()

Get-ConnectionString and Get-ColumnMetadataAdapter are PowerShell functions that assist in creating the needed ADO.NET objects. The table gets filled with the column metadata I wanted, but the SELECT statement is not shown in XEvents.

David Browne - Microsoft · Accepted Answer · 2019-03-01T21:16:24.223

1

Can't repro.

CREATE EVENT SESSION [trc] ON SERVER 
ADD EVENT sqlserver.rpc_completed,
ADD EVENT sqlserver.sp_statement_completed,
ADD EVENT sqlserver.sql_batch_completed,
ADD EVENT sqlserver.sql_statement_completed
GO

Start the session and watch live events in SSMS.

Then

PS C:\Users\dbrowne> $da = new-object system.data.sqlclient.sqldataadapter
PS C:\Users\dbrowne> $con = new-object system.data.sqlclient.sqlconnection "server=.;database=tempdb;integrated security=true"
PS C:\Users\dbrowne> $con.open()
PS C:\Users\dbrowne> $cmd = $con.createcommand()
PS C:\Users\dbrowne> $cmd.commandtext = "select * from sys.objects"
PS C:\Users\dbrowne> $da.selectcommand = $cmd
PS C:\Users\dbrowne> $dt = new-object system.data.datatable
PS C:\Users\dbrowne> $da.fill($dt)
106

and see both the sql_statement_completed and the sql_batch_completed (note with different code you might get an rpc_completed instead of a sql_batch_completed).

If you bind parameters into the SqlCommand it will be sent as an RPC call instead of a batch call, and the events will be a bit different rpc/sp instead of batch/sql.

rpc_completed/sp_statement_completed

instead of

sql_batch_completed/sql_statement_completed

edited Mar 01 '19 at 21:16

answered Mar 01 '19 at 20:34

David Browne - Microsoft

80,331
6
39
67

I tried your code, and the query does show up in XEvents. Mine doesn't, though. I've added my code to the question. Maybe the issue is that I am using the SqlAdapter.Fill() method? – Ben Mar 01 '19 at 20:48
1

No idea. You might need to look into `Get-ColumnMetadataAdapter` to see what it's doing, or eliminate that call, replacing it with a variant of the ADO.NET code in my answer. – David Browne - Microsoft Mar 01 '19 at 20:53
The function sets up a SqlDataAdapter that takes the table and the schema as parameters and has a SelectCommand that queries the DMVs for column names and data types of the table. I'll try inlining that code and see if I get an XEvent now. But still, in the end a SQL query gets sent to SQL Server, and so it should show up in the XEvents. Thanks! – Ben Mar 01 '19 at 20:58
1

Yes. See update to answer. Parameterized queries are sent as RPC calls. – David Browne - Microsoft Mar 01 '19 at 21:16

ADO.NET query not showing in XEvents

1 Answers1