SQL Server profile name is not valid [SQLSTATE 42000] Error 14607

Created first account and then profile for SQL Server Emailing.

I opened SQL management Studio and ran the test script:

EXEC msdb.dbo.sp_send_dbmail @recipients=’abc@xyz.com’, @profile_name = ‘ProfilenameABC’,    @subject = ‘test email ‘,    @body = ‘test email’,    @body_format = ‘HTML’ ;

It worked fine.

But the developer from my team came back saying the job has failed with error:

profile name is not valid [SQLSTATE 42000] Error 14607.

He too is getting the same error.

So then we realized we have to change it security and make it Public.

So in SQL management Studio, navigate to Database mail => Configure Database mail, and select option=> Manage Profile Security.

Making it public profile allows guest access too.

 

Advertisements

SQL Server Catalog Views Information Schema Views Metadata List of all tables views databases

To get metadata in SQL server such as list of all databases, or tables, views residing in a database we can use:

  • Catalog Views
  • Information Schema Views.
  • OLE DB schema rowsets.
  • ODBC catalog functions.
  • System stored procedures and functions.

In this blog let’s look at the first two approaches of Catalog Views, and Information Schema Views.

Metadata means data about data. 

The core of the SQL Server system catalogs is a set of views that show metadata that describes the objects in an instance of SQL Server. Metadata is data that describes the attributes of objects in a system.

Microsoft recommends using catalog views to access metadata for the following reasons:

  • All metadata is made available as catalog views.
  • Catalog views present metadata in a format that is independent of any catalog table implementation, therefore catalog views are not affected by changes in the underlying catalog tables.
  • Catalog views are the most efficient way to access core server metadata.
  • Catalog views are the general interface to catalog metadata and provide the most direct way to obtain, transform, and present customized forms of this metadata.
  • Catalog view names and the names of their columns are descriptive. Query results match what might be expected by a user who has a moderate knowledge of the feature that corresponds to the metadata that is being queried.

For example, the following query uses the sys.objects catalog view to return all database objects that have been modified in the last 10 days.

SELECT name AS object_name, SCHEMA_NAME(schema_id) AS schema_name, type_desc
,create_date,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() – 10
ORDER BY modify_date;

Information schema views are based on catalog view definitions in the ISO standard. They present the catalog information in a format that is independent of any catalog table implementation and therefore are not affected by changes in the underlying catalog tables. Applications that use these views are portable between heterogeneous ISO compliant database systems.
Following are some of the catalog based queries published on Microsoft site.

Data Types

Tables, Indexes, Views, and Constraints

Modules (Stored Procedures, User-Defined Functions, and Triggers)

Schemas, Users, Roles, and Permissions

Answers

How do I find all the tables that do not have a clustered index in a specified database?

Before you run the following queries, replace <database_name> with a valid database name.

USE AdventureWorks2008;
GO
SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name
FROM sys.tables AS t
WHERE NOT EXISTS 
   (
     SELECT * FROM sys.indexes AS i
     WHERE i.object_id = t.object_id
     AND i.type = 1  -- or type_desc = 'CLUSTERED'
   )
ORDER BY schema_name, table_name;
GO


Or, you can use the OBJECTPROPERTY function as shown in the following example.

USE <database_name>;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name, name AS table_name
FROM sys.tables 
WHERE OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0
ORDER BY schema_id, name;
GO


TOP

How do I find all the owners of entities contained in a specified schema?

Before you run the following query, replace <database_name> and <schema_name> with valid names.

USE <database_name>;
GO
SELECT 'OBJECT' AS entity_type
    ,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name
    ,name 
FROM sys.objects WHERE SCHEMA_NAME(schema_id) = '<schema_name>'
UNION 
SELECT 'TYPE' AS entity_type
    ,USER_NAME(TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId')) AS owner_name
    ,name 
FROM sys.types WHERE SCHEMA_NAME(schema_id) = '<schema_name>' 
UNION
SELECT 'XML SCHEMA COLLECTION' AS entity_type 
    ,COALESCE(USER_NAME(xsc.principal_id),USER_NAME(s.principal_id)) AS owner_name
    ,xsc.name 
FROM sys.xml_schema_collections AS xsc JOIN sys.schemas AS s
    ON s.schema_id = xsc.schema_id
WHERE s.name = '<schema_name>';
GO


How do I find all the tables that do not have a primary key?

Before you run the following queries, replace <database_name> with a valid database name.

USE <database_name>;
GO
SELECT SCHEMA_NAME(t.schema_id) AS schema_name
    ,t.name AS table_name
FROM sys.tables t 
WHERE object_id NOT IN 
   (
    SELECT parent_object_id 
    FROM sys.key_constraints 
    WHERE type_desc = 'PRIMARY_KEY_CONSTRAINT' -- or type = 'PK'
    );
GO


Or, you can run the following query.

USE <database_name>;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
    ,name AS table_name 
FROM sys.tables 
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
ORDER BY schema_name, table_name;
GO


How do I find all the tables that do not have an index?

Before you run the following query, replace <database_name> with a valid database name.

USE <database_name>;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
    ,name AS table_name
FROM sys.tables 
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0
ORDER BY schema_name, table_name;
GO


How do I find all the tables that have an identity column?

Before you run the following query, replace <database_name> with a valid database name.

USE <database_name>;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
    , t.name AS table_name
    , c.name AS column_name
FROM sys.tables AS t
JOIN sys.identity_columns c ON t.object_id = c.object_id
ORDER BY schema_name, table_name;
GO


Or, you can run the following query.

NoteNote
This query does not return the name of the columns.
USE <database_name>;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
    ,name AS table_name 
FROM sys.tables 
WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 1
ORDER BY schema_name, table_name;
GO


How do I find the data types of the columns of a specified table?

Before you run the following query, replace <database_name> and <schema_name.table_name> with valid names.

USE <database_name>;
GO
SELECT c.name AS column_name
    ,c.column_id
    ,SCHEMA_NAME(t.schema_id) AS type_schema
    ,t.name AS type_name
    ,t.is_user_defined
    ,t.is_assembly_type
    ,c.max_length
    ,c.precision
    ,c.scale
FROM sys.columns AS c 
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE c.object_id = OBJECT_ID('<schema_name.table_name>')
ORDER BY c.column_id;
GO


How do I find the dependencies on a specified function?

Before you run the following query, replace <database_name> and <schema_name.function_name> with valid names.

USE <database_name>;
GO
SELECT OBJECT_NAME(object_id) AS referencing_object_name
    ,COALESCE(COL_NAME(object_id, column_id), '(n/a)') AS referencing_column_name
    ,*
FROM sys.sql_dependencies
WHERE referenced_major_id = OBJECT_ID('<schema_name.function_name>')
ORDER BY OBJECT_NAME(object_id), COL_NAME(object_id, column_id);
GO 


 

How do I find all the stored procedures in a database?

Before you run the following query, replace <database_name> with a valid name.

USE <database_name>;
GO
SELECT name AS procedure_name 
    ,SCHEMA_NAME(schema_id) AS schema_name
    ,type_desc
    ,create_date
    ,modify_date
FROM sys.procedures;
GO


 

How do I find the parameters for a specified stored procedure or function?

Before you run the following query, replace <database_name> and <schema_name.object_name> with valid names.

USE <database_name>;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
    ,o.name AS object_name
    ,o.type_desc
    ,p.parameter_id
    ,p.name AS parameter_name
    ,TYPE_NAME(p.user_type_id) AS parameter_type
    ,p.max_length
    ,p.precision
    ,p.scale
    ,p.is_output
FROM sys.objects AS o
INNER JOIN sys.parameters AS p ON o.object_id = p.object_id
WHERE o.object_id = OBJECT_ID('<schema_name.object_name>')
ORDER BY schema_name, object_name, p.parameter_id;
GO


 

How do I find all the user-defined functions in a database?

Before you run the following query, replace <database_name> with a valid database name.

USE <database_name>;
GO
SELECT name AS function_name 
  ,SCHEMA_NAME(schema_id) AS schema_name
  ,type_desc
  ,create_date
  ,modify_date
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
GO


 

How do I find all views in a database?

Before you run the following query, replace <database_name> with a valid database name.

USE <database_name>;
GO
SELECT name AS view_name 
  ,SCHEMA_NAME(schema_id) AS schema_name
  ,OBJECTPROPERTYEX(object_id,'IsIndexed') AS IsIndexed
  ,OBJECTPROPERTYEX(object_id,'IsIndexable') AS IsIndexable
  ,create_date
  ,modify_date
FROM sys.views;
GO


 

How do I find all the entities that have been modified in the last N days?

Before you run the following query, replace <database_name> and <n_days> with valid values.

USE <database_name>;
GO
SELECT name AS object_name 
  ,SCHEMA_NAME(schema_id) AS schema_name
  ,type_desc
  ,create_date
  ,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() - <n_days>
ORDER BY modify_date;
GO


 

How do I find the LOB data types of a specified table?

Before you run the following query, replace <database_name> and <schema_name.table_name> with valid names.

USE <database_name>;
GO
SELECT name AS column_name 
    ,column_id 
    ,TYPE_NAME(user_type_id) AS type_name
    ,max_length
    ,CASE 
       WHEN max_length = -1 AND TYPE_NAME(user_type_id) <> 'xml'
            THEN 1
            ELSE 0
     END AS [(max)]
FROM sys.columns
WHERE object_id=OBJECT_ID('<schema_name.table_name>') 
    AND ( TYPE_NAME(user_type_id) IN ('xml','text', 'ntext','image')
         OR (TYPE_NAME(user_type_id) IN ('varchar','nvarchar','varbinary')
         AND max_length = -1)
        );
GO


 

How do I view the definition of a module?

Before you run the following query, replace <database_name> and <schema_name.object_name> with valid names.

USE <database_name>;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('<schema_name.object_name>');
GO


Or, you can use the OBJECT_DEFINITION function as shown in the following example.

USE <database_name>;
GO
SELECT OBJECT_DEFINITION (OBJECT_ID('<schema_name.object_name>')) AS ObjectDefinition;
GO


 

How do I view the definition of a server-level trigger?

SELECT definition
FROM sys.server_sql_modules;
GO


 

How do I find the columns of a primary key for a specified table?

Before you run the following query, replace <database_name> and <schema_name.table_name> with valid names.

USE <database_name>;
GO
SELECT i.name AS index_name
    ,ic.index_column_id
    ,key_ordinal
    ,c.name AS column_name
    ,TYPE_NAME(c.user_type_id)AS column_type 
    ,is_identity
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic 
    ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns AS c 
    ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE i.is_primary_key = 1 
    AND i.object_id = OBJECT_ID('<schema_name.table_name>');
GO


Or, you can use the COL_NAME function as shown in the following example.

USE <database_name>;
GO
SELECT i.name AS index_name
    ,COL_NAME(ic.object_id,ic.column_id) AS column_name
    ,ic.index_column_id
    ,key_ordinal
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic 
    ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1 
    AND i.object_id = OBJECT_ID('<schema_name.table_name>');
GO


 

How do I find the columns of a foreign key for a specified table?

Before you run the following query, replace <database_name> and <schema_name.table_name> with valid names.

USE <database_name>;
GO
SELECT 
    f.name AS foreign_key_name
   ,OBJECT_NAME(f.parent_object_id) AS table_name
   ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name
   ,OBJECT_NAME (f.referenced_object_id) AS referenced_object
   ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name
   ,is_disabled
   ,delete_referential_action_desc
   ,update_referential_action_desc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc 
   ON f.object_id = fc.constraint_object_id 
WHERE f.parent_object_id = OBJECT_ID('<schema_name.table_name>');


 

How do I find the permissions granted or denied to a specified principal?

The following example creates a function to return the name of the entity on which the permissions are checked. The function is invoked in the queries that follow. The function must be created in every database in which you want to check permissions.

-- Create a function to return the name of the entity on which the permissions are checked.
IF OBJECT_ID (N'dbo.entity_instance_name', N'FN') IS NOT NULL
    DROP FUNCTION dbo.entity_instance_name;
GO
CREATE FUNCTION dbo.entity_instance_name(@class_desc nvarchar(60), @major_id int) 
RETURNS sysname AS
BEGIN
    DECLARE @the_entity_name sysname
    SELECT @the_entity_name = CASE
        WHEN @class_desc = 'DATABASE' THEN DB_NAME()
        WHEN @class_desc = 'SCHEMA' THEN SCHEMA_NAME(@major_id)
        WHEN @class_desc = 'OBJECT_OR_COLUMN' THEN OBJECT_NAME(@major_id)
        WHEN @class_desc = 'DATABASE_PRINCIPAL' THEN USER_NAME(@major_id)
        WHEN @class_desc = 'ASSEMBLY' THEN 
            (SELECT name FROM sys.assemblies WHERE assembly_id=@major_id)
        WHEN @class_desc = 'TYPE' THEN TYPE_NAME(@major_id)
        WHEN @class_desc = 'XML_SCHEMA_COLLECTION' THEN 
            (SELECT name FROM sys.xml_schema_collections
              WHERE xml_collection_id=@major_id)
        WHEN @class_desc = 'MESSAGE_TYPE' THEN 
            (SELECT name FROM sys.service_message_types WHERE message_type_id=@major_id)
        WHEN @class_desc = 'SERVICE_CONTRACT' THEN 
           (SELECT name FROM sys.service_contracts
              WHERE service_contract_id=@major_id)
        WHEN @class_desc = 'SERVICE' THEN
          (SELECT name FROM sys.services WHERE service_id=@major_id)
        WHEN @class_desc = 'REMOTE_SERVICE_BINDING' THEN
          (SELECT name FROM sys.remote_service_bindings
             WHERE remote_service_binding_id=@major_id)
        WHEN @class_desc = 'ROUTE' THEN
          (SELECT name FROM sys.routes WHERE route_id=@major_id)
        WHEN @class_desc = 'FULLTEXT_CATALOG' THEN
          (SELECT name FROM sys.fulltext_catalogs WHERE fulltext_catalog_id=@major_id)
        WHEN @class_desc = 'SYMMETRIC_KEY' THEN
          (SELECT name FROM sys.symmetric_keys WHERE symmetric_key_id=@major_id)
        WHEN @class_desc = 'CERTIFICATE' THEN
          (SELECT name FROM sys.certificates WHERE certificate_id=@major_id)
        WHEN @class_desc = 'ASYMMETRIC_KEY' THEN
          (SELECT name FROM sys.asymmetric_keys WHERE asymmetric_key_id=@major_id)
        WHEN @class_desc = 'SERVER' THEN 
             (SELECT name FROM sys.servers WHERE server_id=@major_id)
        WHEN @class_desc = 'SERVER_PRINCIPAL' THEN SUSER_NAME(@major_id)
        WHEN @class_desc = 'ENDPOINT' THEN 
             (SELECT name FROM sys.endpoints WHERE endpoint_id=@major_id)      
        ELSE '?'
    END
    RETURN @the_entity_name
END;
GO
-- Return server-level permissions for the user.
SELECT class
    ,class_desc
    ,dbo.entity_instance_name(class_desc, major_id) AS entity_name 
    ,minor_id
    ,SUSER_NAME(grantee_principal_id) AS grantee
    ,SUSER_NAME(grantor_principal_id) AS grantor
    ,type
    ,permission_name
    ,state_desc 
FROM sys.server_permissions 
WHERE grantee_principal_id = SUSER_ID('public');
GO
-- Return database-level permissions for the user.
SELECT class
    ,class_desc
    ,dbo.entity_instance_name(class_desc , major_id) AS entity_name 
    ,minor_id
    ,USER_NAME(grantee_principal_id) AS grantee
    ,USER_NAME(grantor_principal_id) AS grantor
    ,type
    ,permission_name
    ,state_desc   
FROM  sys.database_permissions 
WHERE grantee_principal_id = DATABASE_PRINCIPAL_ID('public');
GO


 

How do I determine if a column is used in a computed column expression?

Before you run the following query, replace <database_name><schema_name.table_name>, and <column_name> with valid names.

USE <database_name>;
GO
SELECT OBJECT_NAME(object_id) AS object_name
    ,COL_NAME(object_id, column_id) AS computed_column 
    ,class_desc
    ,is_selected
    ,is_updated
    ,is_select_all
FROM sys.sql_dependencies
WHERE referenced_major_id = OBJECT_ID('<schema_name.table_name>')
    AND referenced_minor_id = COLUMNPROPERTY(referenced_major_id, '<column_name>', 'ColumnId')
    AND class = 1;
GO


TOP

 

How do I find all the columns that are used in a computed column expression?

Before you run the following query, replace <database_name> with a valid name.

USE <database_name>;
GO
SELECT OBJECT_NAME(d.referenced_major_id) AS object_name
    ,COL_NAME(d.referenced_major_id, d.referenced_minor_id) AS column_name
    ,OBJECT_NAME(referenced_major_id) AS dependent_object_name 
    ,COL_NAME(d.object_id, d.column_id) AS dependent_computed_column
    ,cc.definition AS computed_column_definition
FROM sys.sql_dependencies AS d
JOIN sys.computed_columns AS cc 
    ON cc.object_id = d.object_id AND cc.column_id = d.column_id AND d.object_id=d.referenced_major_id     
WHERE d.class = 1
ORDER BY object_name, column_name;
GO


TOP

 

How do I find the columns that depend on a specified CLR user-defined type or alias type?

Before you run the following query, replace <database_name> with a valid name and <schema_name.data_type_name> with a valid, schema-qualified CLR user-defined type, or schema-qualified alias type name. The following query requires membership in the db_owner role or permissions to see all dependent column and computed column metadata in the database.

USE <database_name>;
GO
SELECT OBJECT_NAME(object_id) AS object_name 
    ,c.name AS column_name 
    ,SCHEMA_NAME(t.schema_id) AS schema_name
    ,TYPE_NAME(c.user_type_id) AS user_type_name
    ,c.max_length
    ,c.precision
    ,c.scale
    ,c.is_nullable
    ,c.is_computed
FROM sys.columns AS c
INNER JOIN sys.types AS t ON c.user_type_id = t.user_type_id
WHERE c.user_type_id = TYPE_ID('<schema_name.data_type_name>'); 
GO


The following query returns a restricted and narrow view of columns dependent on a CLR user-defined type or alias, but the result set is visible to the public role. You can use this query if you have granted REFERENCE permissions on your user-defined type to others and you do not have permission to view the metadata of the objects others have created that use the type.

USE <database_name>;
GO
SELECT OBJECT_NAME(object_id) AS object_name 
    ,COL_NAME(object_id, column_id) AS column_name
    ,TYPE_NAME(user_type_id) AS user_type
FROM sys.column_type_usages
WHERE user_type_id = TYPE_ID('<schema_name.data_type_name>');
GO


TOP

 

How do I find the computed columns that depend on a specified CLR user-defined type or alias type?

Before you run the following query, replace <database_name> with a valid name and <schema_name.data_type_name> with a valid, schema-qualified CLR user-defined typealias type name. For more information about computed column dependencies, see Understanding SQL Dependencies.

USE <database_name>;
GO
SELECT OBJECT_NAME(object_id) AS object_name
    ,COL_NAME(object_id, column_id) AS column_name
FROM sys.sql_dependencies
WHERE referenced_major_id = TYPE_ID('<schema_name.data_type_name>')
    AND class = 2 -- schema-bound references to type
    AND OBJECTPROPERTY(object_id, 'IsTable') = 1;   -- exclude non-table dependencies 


TOP

 

How do I find the parameters that depend on a specified CLR user-defined type or alias type?

Before you run the following query, replace <database_name> with a valid name and <schema_name.data_type_name> with a valid, schema-qualified CLR user-defined typealias type name. The following query requires membership in the db_owner role or permissions to see all dependent column and computed column metadata in the database.

USE <database_name>;
GO
SELECT OBJECT_NAME(object_id) AS object_name
    ,NULL AS procedure_number
    ,name AS param_name
    ,parameter_id AS param_num
    ,TYPE_NAME(p.user_TYPE_ID) AS type_name
FROM sys.parameters AS p
WHERE p.user_TYPE_ID = TYPE_ID('<schema_name.data_type_name>')
UNION 
SELECT OBJECT_NAME(object_id) AS object_name
    ,procedure_number
    ,name AS param_name
    ,parameter_id AS param_num
    ,TYPE_NAME(p.user_TYPE_ID) AS type_name
FROM sys.numbered_procedure_parameters AS p
WHERE p.user_TYPE_ID = TYPE_ID('<schema_name.data_type_name>')
ORDER BY object_name, procedure_number, param_num;
GO



The following query returns a restricted and narrow view of parameters that depend on a CLR user-defined type or alias, but the result set is visible to the public role. You can use this query if you have granted REFERENCE permissions on your user-defined type to others and you do not have permission to view the metadata of the objects others have created that use the type.

USE <database_name>;
GO
SELECT OBJECT_NAME(object_id) AS object_name
    ,parameter_id
    ,TYPE_NAME(user_type_id) AS type_name
FROM sys.parameter_type_usages 
WHERE user_type_id = TYPE_ID('<schema_name.data_type_name>');
GO


TOP

 

How do I find the CHECK constraints that depend on a specified CLR user-defined type?

Before you run the following query, replace <database_name> with a valid name and <schema_name.data_type_name> with a valid, schema-qualified CLR user-defined type name.

USE <database_name>;
GO
SELECT SCHEMA_NAME(o.schema_id) AS schema_name
    ,OBJECT_NAME(o.parent_object_id) AS table_name
    ,OBJECT_NAME(o.object_id) AS constraint_name
FROM sys.sql_dependencies AS d
JOIN sys.objects AS o ON o.object_id = d.object_id
WHERE referenced_major_id = TYPE_ID('<schema_name.data_type_name>')
    AND class = 2 -- schema-bound references to type
    AND OBJECTPROPERTY(o.object_id, 'IsCheckCnst') = 1; -- exclude non-CHECK dependencies
GO


TOP

 

How do I find the views, Transact-SQL functions, and Transact-SQL stored procedures that depend on a specified CLR user-defined type or alias type?

Before you run the following query, replace <database_name> with a valid name and <schema_name.data_type_name> with a valid, schema-qualified CLR user-defined typealias type name.

The parameters defined in a function or procedure are implicitly schema bound. Therefore, parameters that depend on a CLR user-defined type or alias type can be viewed by using the sys.sql_dependencies catalog view. Procedures and triggers are not schema bound. This means that dependencies between any expression defined in the body of the procedure or trigger and a CLR user-defined type or alias type is not maintained. Schema bound views and schema bound user-defined functions that have expressions that depend on a CLR user-defined type or alias type are maintained in the sys.sql_dependencies catalog view. Dependencies between types and CLR functions and CLR procedures are not maintained.

The following query returns all schema-bound dependencies in views, Transact-SQL functions, and Transact-SQL stored procedures for a specified CLR user-defined type or alias type.

USE <database_name>;
GO
SELECT SCHEMA_NAME(o.schema_id) AS dependent_object_schema
  ,OBJECT_NAME(o.object_id) AS dependent_object_name
  ,o.type_desc AS dependent_object_type
  ,d.class_desc AS kind_of_dependency
  ,TYPE_NAME (d.referenced_major_id) AS type_name
FROM sys.sql_dependencies AS d 
JOIN sys.objects AS o
  ON d.object_id = o.object_id
  AND o.type IN ('FN','IF','TF', 'V', 'P')
WHERE d.class = 2 -- dependencies on types
  AND d.referenced_major_id = TYPE_ID('<schema_name.data_type_name>')
ORDER BY dependent_object_schema, dependent_object_name;
GO


TOP

 

How do I find all the constraints for a specified table?

Before you run the following query, replace <database_name> and <schema_name.table_name> with valid names.

USE <database_name>;
GO
SELECT OBJECT_NAME(object_id) as constraint_name
    ,SCHEMA_NAME(schema_id) AS schema_name
    ,OBJECT_NAME(parent_object_id) AS table_name
    ,type_desc
    ,create_date
    ,modify_date
    ,is_ms_shipped
    ,is_published
    ,is_schema_published
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT' 
    AND parent_object_id = OBJECT_ID('<schema_name.table_name>');
GO


TOP

 

How do I find all the indexes for a specified table?

Before you run the following query, replace <database_name> and <schema_name.table_name> with valid names.

USE <database_name>;
GO
SELECT i.name AS index_name
    ,i.type_desc
    ,is_unique
    ,ds.type_desc AS filegroup_or_partition_scheme
    ,ds.name AS filegroup_or_partition_scheme_name
    ,ignore_dup_key
    ,is_primary_key
    ,is_unique_constraint
    ,fill_factor
    ,is_padded
    ,is_disabled
    ,allow_row_locks
    ,allow_page_locks
FROM sys.indexes AS i
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
WHERE is_hypothetical = 0 AND i.index_id <> 0 
AND i.object_id = OBJECT_ID('<schema_name.table_name>');
GO


TOP

 

How do I find all the objects that have a specified column name?

Before you run the following query, replace <database_name> and <column_name> with valid names.

USE <database_name>;
GO
SELECT OBJECT_NAME(object_id)
FROM sys.columns
WHERE name = '<column_name>';
GO


Or

USE <database_name>;
GO
SELECT SCHEMA_NAME(o.schema_id) AS schema_name 
    ,o.name AS object_name
    ,type_desc
FROM sys.objects AS o
INNER JOIN sys.columns AS c ON o.object_id = c.object_id
WHERE c.name = '<column_name>';
GO


How do I find all the user-defined tables in a specified database?

Before you run the following query, replace <database_name> with a valid name.

USE <database_name>;
GO
SELECT * 
FROM sys.tables;
GO


How do I find all the tables and indexes that are partitioned?

Before you run the following query, replace <database_name> with a valid name.

USE <database_name>;
GO
SELECT SCHEMA_NAME(o.schema_id) AS schema_name
    ,OBJECT_NAME(p.object_id) AS table_name
    ,i.name AS index_name
    ,p.partition_number
    ,rows 
FROM sys.partitions AS p
INNER JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.partition_schemes ps ON i.data_space_id=ps.data_space_id
INNER JOIN sys.objects AS o ON o.object_id = i.object_id
ORDER BY index_name, partition_number;
GO


How do I find all the statistics on a specified object?

Before you run the following query, replace <database_name> with a valid name and <schema_name.object_name> with a valid table, indexed view, or table-valued function name.

USE <database_name>;
GO
SELECT name AS statistics_name
    ,stats_id
    ,auto_created
    ,user_created
    ,no_recompute
FROM sys.stats
WHERE object_id = OBJECT_ID('<schema_name.object_name>');
GO


How do I find all the statistics and statistics columns on a specified object?

Before you run the following query, replace <database_name> with a valid name and <schema_name.object_name> with a valid table, indexed view, or table-valued function name.

USE <database_name>;
GO
SELECT s.name AS statistics_name
    ,c.name AS column_name
    ,sc.stats_column_id
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc 
    ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
INNER JOIN sys.columns AS c 
    ON sc.object_id = c.object_id AND c.column_id = sc.column_id
WHERE s.object_id = OBJECT_ID('<schema_name.object_name>');
GO


How do I find the definition of a view?

Before you run the following query, replace <database_name> and <schema_name.object_name> with valid names.

USE <database_name>;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('<schema_name.object_name>');
GO


Or, you can use the OBJECT_DEFINITION function as shown in the following example.

USE <database_name>;
GO
SELECT OBJECT_DEFINITION (OBJECT_ID('<schema_name.object_name>')) AS ObjectDefinition;
GO

 

Upgrade Power BI on-premise Report Server

Reference Upgrade Power BI Report Server

 

Before you begin

Before you upgrade a report server, it is recommended that you perform the following steps to back up your report server.

Backing up the encryption keys

You should backup the encryption keys when you configure a report server installation for the first time. You should also backup the keys any time you change the identity of the service accounts or rename the computer. For more information, see Back Up and Restore Reporting Services Encryption Keys.

Backing up the report server databases

Because a report server is a stateless server, all application data is stored in the reportserverand reportservertempdb databases that run on a SQL Server Database Engine instance. You can backup the reportserver and reportservertempdb databases using one of the supported methods for backing up SQL Server databases. Recommendations that are specific to the report server databases include the following:

  • Use the full recovery model to backup the reportserver database.
  • Use the simple recovery model to backup the reportservertempdb database.
  • You can use different backup schedules for each database. The only reason to backup the reportservertempdb is to avoid having to recreate it if there is a hardware failure. In the event of hardware failure, it is not necessary to recover the data in reportservertempdb, but you do need the table structure. If you lose reportservertempdb, the only way to get it back is to recreate the report server database. If you recreate the reportservertempdb, it is important that it have the same name as the primary report server database.

For more information about backup and recovery of SQL Server relational databases, see Back Up and Restore of SQL Server Databases.

Backing up the configuration files

Power BI Report Server uses configuration files to store application settings. You should backup the files when you first configure the server and after you deploy any custom extensions. Files to back up include:

  • config.json
  • RSHostingService.exe.config
  • Rsreportserver.config
  • Rssvrpolicy.config
  • Reportingservicesservice.exe.config
  • Web.config for the Report Server ASP.NET applications
  • Machine.config for ASP.NET

Stop the Power BI Report Server Service.

Upgrade the report server

Upgrading Power BI Report Server is straight forward. There are only a few steps to install the files.

  1. Find the location of PowerBIReportServer.exe and launch the installer.
  2. Select Upgrade Power BI Report Server.

  3. Read and agree to the license terms and conditions and then select Upgrade.

  4. After a successful upgrade, you can select Configure Report Server to launch the Reporting Services Configuration Manager, or select Close to exit the installer.

Upgrade Power BI Desktop

After the report server is upgrade, you will want to make sure that any Power BI report authors upgrade to the version of Power BI Desktop optimized for Power BI Report Server that matches the server.

how to delete empty or blank rows in excel

After selecting the data range, continue as follows:

  1. Press [F5].
  2. In the resulting Go To dialog box, click Special.
  3. Click the Blanks option and click OK. Doing so selects the blank cells (what you might think of as rows) in the selected range. In this case, that’s A9:X9 and A17:X17.
  4. Now you’re ready to delete the selected cells. On the Home tab, click the Delete dropdown in the Cells group and choose Entire row. Excel will display the Delete dialog box, with the Entire row option selected. Click OK.

Reference

 

Power BI Q&A for On-premises Data Gateway datasets

Once we publish the Power BI report to cloud (Power Bi Service) we need to navigate to the dataset created and enable Q&A.

Then you navigate to the dashboard created using this report and enable Q&A.

As of October 28, 2017 there are following limitations:

  • Initially the feature is only available for SQL Server 2016 Analysis Services Tabular data sources. The feature is optimized to work with tabular data. Some functionality is available for multi-dimensional data sources, but the full Q&A experience is not yet supported for multi-dimensional. Additional data sources supported by the On-premises data gateway will be rolled out during the public preview.

  • Full support for row level security defined in SQL Server Analysis Services is not available initially in the public preview. While asking questions in Q&A, the “auto-complete” of questions while typing can show string values a user does not have access to. However, RLS defined in the model is respected for report and chart visuals so no underlying numerical data can be exposed. Options to control this behavior will be released in coming updates.

  • Live connections are only supported with the On-premises data gateway. As a result, this cannot be used with the personal gateway.

Details:

Once enabled, Power BI creates an index of your data source and uploads a subset of that data to Power BI to enable asking questions. It may take several minutes to create the initial index and Power BI maintains and updates the index automatically as your data changes. Using Q&A with these datasets behaves the same as with data published to Power BI. The full set of features available in the Q&A experience is supported in both cases, including using the data source with Cortana.

As you ask questions in Power BI, Q&A determines the best visual to construct or report sheet to use to answer your question using an index of your dataset. After determining the best potential answer, Q&A uses DirectQuery to fetch live data from the data source via the gateway to populate charts and graphs. This ensures Power BI Q&A results always show the most up-to-date data directly from the underlying data source.

Since Power BI Q&A uses the text and schema values from your data source to determine how to query the underlying model for answers, searches for specific new or deleted text values (such as asking for a customer name related to a newly added text record) rely on the index being up-to-date with the latest values. Power BI automatically keeps the text and schema index up to date within a 60 minute window of changes.

 

How does Microsoft Azure or cloud On-Premises Data Gateway works

Reference Sept 6th 2017 On-premises data gateway in-depth

How the gateway works

on-prem-data-gateway-how-it-works

Let’s first look at what happens when a user interacts with an element connected to an on-premises data source.

  1. A query will be created by the cloud service, along with the encrypted credentials for the on-premises data source, and sent to the queue for the gateway to process.

  2. The gateway cloud service will analyze the query and will push the request to the Azure Service Bus.

  3. The on-premises data gateway polls the Azure Service Bus for pending requests.

  4. The gateway gets the query, decrypts the credentials and connects to the data source(s) with those credentials.

  5. The gateway sends the query to the data source for execution.

  6. The results are sent from the data source, back to the gateway, and then onto the cloud service. The service then uses the results.

Note:

For Power BI, you will need to configure a data source for the gateway.

On-Premises Data Gateway FAQ

As of Oct 5th 2017 Reference article

General

Question: What is the actual Windows service called?
Answer: The gateway is called On-premises data gateway service in Services

Question: What are the requirements for the gateway?
Answer: Take a look at the requirements section of the main gateway article.

Question: What data sources are supported with the gateway?
Answer: See the data sources table in the main gateway article.

Question: Do I need a gateway for cloud data sources like Azure SQL Database?
Answer: No! The service will be able to connect to that data source without a gateway.

Question: Are there any inbound connections to the gateway from the cloud?
Answer: No. The gateway uses outbound connections to Azure Service Bus.

Question: What if I block outbound connections? What do I need to open?
Answer: See the list of ports and hosts that the gateway uses.

Question: Does the gateway have to be installed on the same machine as the data source?
Answer: No. The gateway will connect to the data source using the connection information that was provided. Think of the gateway as a client application in this sense. It will just need to be able to connect to the server name that was provided.

Question: What is the latency for running queries to a data source from the gateway? What is the best architecture?
Answer: It is recommended to have the gateway as close to the data source as possible to avoid network latency. If you can install the gateway on the actual data source, it will minimize the latency introduced. Consider the data centers as well. For example, if your service is making use of the West US data center, and you have SQL Server hosted in an Azure VM, you will want to have the Azure VM in West US as well. This will minimize latency and avoid egress charges on the Azure VM.

Question: Are there any requirements for network bandwidth?
Answer: It is recommended to have good throughput for your network connection. Every environment is different and this is also dependent on the amount of data being sent. Using ExpressRoute could help to guarantee a level of throughput between on-premises and the Azure data centers.

You can use the 3rd party Azure Speed Test app to help gauge what your throughput is.

Question: Can the gateway Windows service run with an Azure Active Directory account?
Answer: No. The Windows service needs to have a valid Windows account. By default it will run with the Service SID, NT SERVICE\PBIEgwService.

Question: How are results sent back to the cloud?
Answer: This is done by way of the Azure Service Bus. For more information, see how it works.

Question: Where are my credentials stored?
Answer: The credentials you enter for a data source are stored encrypted in the gateway cloud service. The credentials are decrypted at the gateway on-premises.

Question: Can I place the gateway in a perimeter network (also known as DMZ, demilitarized zone, and screened subnet)?
Answer: The gateway requires connectivity to the data source. If the data source is not accessable in your perimeter network, the gateway may not be able to connect to it. For example, your SQL Server may not be in your perimeter network. And, you cannot connect to your SQL Server from the perimeter network. If you placed the gateway in your perimeter network, it would not be able to reach the SQL Server.

Question: Is it possible to force the gateway to use HTTPS traffic with Azure Service Bus instead of TCP?
Answer: Yes. Although, this will greatly reduce performance. You will want to modify the Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config file. You will want to change the value from AutoDetect to Https. This file is located, by default, at C:\Program Files\On-premises data gateway.

Question: Do I need to whitelist the Azure Datacenter IP list? Where do I get the list?
Answer: If you are blocking outbound IP traffic, you may need to whitelist the Azure Datacenter IP list. Currently, the gateway will communicate with Azure Service Bus using the IP address in addition to the fully qualified domain name. The Azure Datacenter IP list is updated weekly. You can download the Microsoft Azure Datacenter IP list.

<setting name="ServiceBusSystemConnectivityModeString" serializeAs="String">
    <value>Https</value>
</setting>

High Availability/Disaster Recovery

Question: Are there any plans for enabling high availability scenarios with the gateway?
Answer: Yes, this is an area of active investment for the Power BI team. Please stay tuned to the Power BI blog for further updates about this feature.

Question: What options are available for disaster recovery?
Answer: You can use the recovery key to restore or move a gateway. When you install the gateway, supply the recovery key.

Question: What is the benefit of the recovery key?
Answer: It provides a way to migrate or recover your gateway settings. This is also used for disaster recovery.

Troubleshooting

Question: Where are the gateway logs located?
Answer: See the tools section of the troubleshooting article.

Question: How can I see what queries are being sent to the on-premises data source?
Answer: You can enable query tracing. This will include the queries being sent. Remember to change it back to the original value when done troubleshooting. Having query tracing enabled will cause the logs to be larger.

You can also look at tools your data source has for tracing queries. For example, for SQL Server and Analysis Services you can use Extended Events or SQL Profiler.

Analysis Services

Question: Can I use msdmpump.dll to create custom effective username mappings for Analysis Services?
Answer: No. This is not supported at this time.

Question: Can I use the gateway to connect to a multidimensional (OLAP) instance.
Answer: Yes! The On-Premises Data Gateway supports live connections to both Analysis Services Tabular and Multidimensional models.

Question: What if I install the gateway on a computer in a different domain from my on-premises server that uses Windows authentication?
Answer: No guarantees here. It all depends on the trust relationship between the two domains. If the two different domains are in a trusted domain model, then the gateway might be able to connect to the Analysis Services server and the effective user name can be resolved. If not, you may encounter a login failure.

Question: How can I find out what effective username is being passed to my on-premises Analysis Services server?
Answer: We answer this in the troubleshooting article.

Question: I have 25 databases in Analysis Services, is there a way to have them all enabled for the gateway at once?
Answer: No. This is on the roadmap, but we don’t have a timeframe.

Administration

Question: Can I have more than one admin for a gateway?
Answer: Yes! When you manage a gateway, you can go to the administrator’s tab to add additional admins.

Question: Does the gateway admin need to be an admin on the machine where the gateway is installed?
Answer: No. The gateway admin is used to manage the gateway from within the service.

Question: Can I prevent users in my organization from creating a gateway?
Answer: No. This is on the roadmap, but we don’t have a timeframe.

Question: Can I get usage and statistics information of the gateways in my organization?
Answer: No. This is on the roadmap, but we don’t have a timeframe.

Power BI

Question: Do I need to upgrade the personal gateway? Answer: No, you can keep using the personal gateway for Power BI.

Question: How often are tiles in a dashboard, in Power BI, refreshed when connected through the On-Premises Data Gateway?
Answer: About ten minutes. DirectQuery connections are just that. This doesn’t mean that a tile issues a query to your on-premises server, and shows new data, every ten minutes.

Question: Can I upload Excel workbooks with Power Pivot data models that connect to on-premises data sources? Do I need a gateway for this scenario?
Answer: Yes, you can upload the workbook. And, no, you don’t need a gateway. But, because the data will reside in the Excel data model, reports in Power BI based on the Excel workbook will not be live. In order to refresh reports in Power BI, you’d have to re-upload an updated workbook each time. Or, use the gateway with scheduled refresh.

Question: If users share dashboards that has a DirectQuery connection, will those other users be able to see the data even though they might not have the same permissions.
Answer: For a dashboard connected to Analysis Services, users will only see the data they have access to. If the users do not have the same permissions, they will not be able to see any data. For other data sources, all users will share the credentials entered by the admin for that data source.

Question: Why can’t I connect to my Oracle server?
Answer: You may need to install the Oracle client and configure the tnsnames.ora file with the proper server information in order to connect to your Oracle server. This is a separate install outside of the Gateway. For more information, see Installing the Oracle Client.

Question: Will the gateway work with ExpressRoute?
Answer: Yes. For more information about ExpressRoute and Power BI, see Power BI and ExpressRoute.