Fn get sql

From DB Optimizer
Jump to: navigation, search


If fn_get_sql access is denied, first check the compatibility level of the database

Compatibility Levels
The compatibility level has to be set to the same level as the actual version of the database.
For example, SQL Server 2005 is level 90, but if the compatibility is change to 80 the equivalent of windows 2000, then DB Optimizer won't work.
select name,compatibility_level from sys.databases
if the compatibility level is different than the default, one issue that happens is an error running the function fn_get_sql which is required by DB Optimizer
  • 80 = SQL 2000
  • 90 = SQL 2005
  • 100 = SQL 2008
To set compatibility level you can use, for example
exec sp_dbcmptlevel MyOldDB, 90

see link http://social.msdn.microsoft.com/
To verify current levels and and rights
Execute the sql_handle test query, both in SQL Server Management Studio
and in a DB Optimizer SQL editor, with the current database set to the
same as in the data source properties. For reference:
DECLARE @Handle varbinary(64);
SELECT @Handle = sql_handle;
FROM sys.dm_exec_requests;
SELECT * FROM ::fn_get_sql(@Handle);
SELECT name,compatibility_level FROM sys.databases

to verify compatibility levels

To see if your connection has SYSADMIN which is required for access to fn_get_sql run the following query:

SELECT sysadmin
FROM master.dbo.syslogins

The output should be “1”. If not then the user you are connected as is not in sysadmin and had to be in order to profile the database. To grant “sysadmin” you can run the following command (connect as user that has the privileges to grant sysadmin):

EXEC sp_addsrvrolemember N'username', sysadmin

IF using a domain trusted connection make sure that you are connecting to a database user that has the needed privileges. To see the database user you are connected as, you can run the following query

SELECT name,dbname
FROM master..syslogins