How to catch a query from an another database?

Question

How to catch a query from an another database?

salliven

SSCrazy

Points: 2644
More actions
August 22, 2012 at 8:51 am

#263211

Hello!
How to catch a query from an another database (example from profiler)?
-- Example:
USE [master] -- or another database
GO
-- How to catch this query on the SQL server?
SELECT name, database_id FROM tempdb.sys.databases
-- Or this query?
INSERT INTO tempdb.dbo.mytables (columns) values (values)
-- etc...
Thanks!

Viewing 12 posts - 1 through 11 (of 11 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

salliven (8/22/2012)
Hello!
How to catch a query from an another database (example from profiler)?
-- Example:
USE [master] -- or another database
GO
-- How to catch this query on the SQL server?
SELECT name, database_id FROM tempdb.sys.databases
-- Or this query?
INSERT INTO tempdb.dbo.mytables (columns) values (values)
-- etc...
Thanks!

Not really sure what you mean by "catch" but you mentioned profiler. If you just want to audit the queries that would be the tool to use.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

I think you can include "dbid" column on profiler - it should tell you from where the query is being executed.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

salliven SSCrazy Points: 2644 More actions · Answer 3

I think you can include "dbid" column on profiler - it should tell you from where the query is being executed.

I don't know what database is runing the query (This is my problem).

salliven SSCrazy Points: 2644 More actions · Answer 4

I don't know the query is how come. (Another database, linked server, etc).

My aim catch all query in the database.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

salliven (8/22/2012)
I think you can include "dbid" column on profiler - it should tell you from where the query is being executed.
I don't know what database is runing the query (This is my problem).

If you run profiler you can see the SPID (SYS Process ID). From there go to sys.sysprocesses and you can see which dbid is running the query.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

salliven (8/22/2012)
I think you can include "dbid" column on profiler - it should tell you from where the query is being executed.
I don't know what database is runing the query (This is my problem).

Exactly - that's why "dbid" on profiler will tell you from where the offending query comes from 😀

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

salliven SSCrazy Points: 2644 More actions · Answer 7

Sorry, I said wrongly.

I want to catch all request witch refer to my database.

These requests from another database:

select * from mydatabase.schema.tables

execute mydatabase.schema.procedures

etc,

These requests from my database:

All request

Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

salliven (8/22/2012)
Sorry, I said wrongly.
I want to catch all request witch refer to my database.
These requests from another database:
select * from mydatabase.schema.tables
execute mydatabase.schema.procedures
etc,
These requests from my database:
All request
Thanks

There is one way to capture this...profiler!!!

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jared SSC Guru Points: 61793 More actions · Answer 9

DatabaseName and ServerName are options in profiler. Make sure you check the box to display all columns.

Jared
CE - Microsoft

salliven SSCrazy Points: 2644 More actions · Answer 10

Thanks for replay!

I'm using the profiler with this settings (Picture_1.png).

Now I'm catching all request what runing from MyDatabase, but I don't catch those requests what MyDatabase is received.

Exapmle:

I catch:

Use [MyDatabase]

GO

-- Requests

I don't catch (but a want to catch these request):

Use [another_database]

go

select * from MyDatabase.schema.table

select * from table inner join MyDatabase.schema.table ...

execute MyDatabase.schema.procedure

-- etc, what using MyDatabase

How I use the profiler?

Thanks

crazy4sql SSCoach Points: 19590 More actions · Answer 11

two option

1) Make your database offline, and whoever shouts of "connection error" is the source.

2) In profile, put the object id/name which is related to your database and is being used in query.

----------
Ashish