Fn get sql

fn_get_sql

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

Compatibility Levels

http://blog.sqlauthority.com/2007/05/29/sql-server-2005-change-database-compatible-level-backward-compatibility/

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);

GO

run

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

WHERE loginname IN (SELECT SYSTEM_USER) ;

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

Go

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

WHERE loginname IN (SELECT SYSTEM_USER)