Posts Tagged ‘SQL Server’

Use sys.sql_modules not INFORMATION_SCHEMA.ROUTINES

Have you ever tried using INFORMATION_SCHEMA.ROUTINES in SQL Server for searching for all procs referencing a specific object? Perhaps something like this; ?View Code TSQLSELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE ‘%table_name%’; Has this ever bitten you? If not then you’ve been lucky so far. The ROUTINE_DEFINITION column is limited to 4000 characters so you […]

The job failed. Unable to determine if the owner (sa) of job has server access.

If you have failing SQL Agent jobs with the following error; The job failed. Unable to determine if the owner (sa) of job has server access. and the following is logged in the SQL Server Log; Message [298] SQLServer Error: 15281, SQL Server blocked access to procedure ‘dbo.sp_sqlagent_has_server_access’ of component ‘Agent XPs’ because this component […]

Moving user databases the TSQL way

Here’s a few queries I built to construct the commands needed to move user database files in SQL Server 2208 R2. The queries are based on the procedure outlined here. As with all scripts on the Internet take care with this. It worked fine for my circumstances but may not in yours. Be careful and […]

Find out members of a database role in SQL Server

Just a quick post with a query to identify members of a specific database role in MSSQL. ?View Code TSQLSELECT dp2.name FROM sys.database_role_members dbrm INNER JOIN sys.database_principals dp ON dp.principal_id = dbrm.role_principal_id INNER JOIN sys.database_principals dp2 ON dp2.principal_id = dbrm.member_principal_id WHERE dp.name = ‘Database Role’; You may want to use this in conjunction with a […]

Using Hints in SQL Server

Query hints are bad right? I confess to using them on odd occasions but only when other attempts to find a solution have failed. Microsoft emphasize this themselves; Because the SQL Server query optimizer typically selects the best execution plan for a query, we recommend that <join_hint>, <query_hint>, and <table_hint> be used only as a […]