how to identify sql scripts not in stored procs w/o using trace

Question

Post reply

how to identify sql scripts not in stored procs w/o using trace

Snargables

SSCrazy Eights

Points: 8855
More actions
June 28, 2023 at 5:28 pm

#4221793

I'm storing off various stats about procs on a daily basis. On e of them stores each sql statement in each proc w/ runtimes and other information.

My issue is there are power bi reports and sql job step that are not using procs but have embedded the sql scripts directly in them . Is there a way for me to capture these sql scripts that are running on the server that are not is stored procedures without using a trace? I'd prefer not to use a trace because i want it to run daily. I will if i have to but i was hoping to avoid a trace.
July 7, 2023 at 12:22 pm

This was removed by the editor as SPAM
July 18, 2023 at 4:46 pm

This was removed by the editor as SPAM
September 13, 2024 at 11:20 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Emperor100 SSCrazy Points: 2173 More actions · Answer 2

What is your objective or purpose of collecting queries?

If you do not want to use Trace then you may use QUERYSTORE. It is a different animal altogether if that might help to collect more queries (not sure if it helps) and you can disable the feature whenever you want.

=======================================================================

rothj Hall of Fame Points: 3407 More actions · Answer 3

I'm not 100% sure, but check out Erik Darling's sp_HumanEvents.

I believe you can filter by db and username and log to a table.

Priyanka Chouhan SSC Rookie Points: 31 More actions · Answer 4

If you want to capture SQL scripts on the server, you can check on some pointers:

1. Need to work on extended events and provide monitoring. You can create an Extended Events session "sql_statement_completed" event which will capture SQL statements.

2. Need to work on SQL server audit: It tracks all events such as successful or failed SQL statements.

3. Custom Logging: It depends on the nature and use cases of scripts for implementing custom log within SQL server jobs.

3. Need to work on Powershell scripts which depend on use cases and implementing SQL server jobs.