Search results matching tags 'sql server' and '2012'

SQL Server 2008R2 / 2012 Standard & Backup Compression

manowar — Mon, 25 Mar 2013 13:17:06 GMT

This is something that is totally overlooked, in my experience, with SQL Server 2008 R2 and SQL Server 2012. On the contrary to what happens with SQL Server 2008, the Standard version of SQL Server 2008R2 & SQL Server 2012 supports backup compression:

SQL Server 2008 R2 BOL
http://msdn.microsoft.com/en-us/library/cc645993(v=sql.105).aspx
SQL Server 2012 BOL
http://msdn.microsoft.com/en-us/library/cc645993(v=sql.110).aspx

Unfortunately a bug in the documentation said the opposite in the past, but it has been fixed quite a long ago now:

http://social.msdn.microsoft.com/Forums/en-US/sqldocumentation/thread/b4f846e8-a339-422c-bb0b-91751e6c8560/

Just keep it mind

Trapping SQL Server Errors with Extended Events

manowar — Sun, 17 Mar 2013 16:45:06 GMT

One very useful usage of Extended Events is the ability to trap SQL Server error without the need to have a server trace running (which, btw, is deprecated), with the additional feature of being able to query the data as soon as it comes in. This means that we a solution to monitor and trap errors as soon as they happen can be easily created, in order to help developers to fix problems as soon as they are detected. This is really, really, really helpful especially in very big applications, where the code base is quite old and there is no-one really knowing everything of the solution.

To start a Extended Events sessions in order to trap SQL Server errors with severity greater than 10, just run the following script:

CREATE EVENT SESSION [error_trap] ON SERVER
ADD EVENT sqlserver.error_reported
    (
        ACTION    (package0.collect_system_time,package0.last_error,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack,sqlserver.username)
        WHERE    ([severity]>10)
    )
ADD TARGET package0.event_file
    (
        SET filename=N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\XEvents\error_trap.xel'
    )
WITH
    (
        STARTUP_STATE=OFF
    )
GO

ALTER EVENT SESSION [error_trap] ON SERVER
STATE = START;
GO

The problem with Exended Events is that they only talks XML which is surely flexible and extensible, but not at all confortable to be queried. That’s why I prefer to turn it into something relational. Using the xml nodes function and pivoting data can make the trick:

IF (OBJECT_ID('tempdb..#e') IS NOT NULL) DROP TABLE #e
go

WITH cte AS
(
    SELECT
        CAST(event_data AS XML) AS event_data
    FROM
        sys.fn_xe_file_target_read_file('D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\XEvents\error_trap*.xel', NULL, NULL, NULL)
),
cte2 AS
(
    SELECT
        event_number = ROW_NUMBER() OVER (ORDER BY T.x)
    ,    event_name = T.x.value('@name', 'varchar(100)')
    ,    event_timestamp = T.x.value('@timestamp', 'datetimeoffset')
    ,    event_data
    FROM
        cte
    CROSS APPLY
        event_data.nodes('/event') T(x)
)
SELECT * INTO #e FROM cte2
go

WITH cte3 AS
(
    SELECT
        c.event_number,
        c.event_timestamp,
        --data_field = T2.x.value('local-name(.)', 'varchar(100)'),
        data_name = T2.x.value('@name', 'varchar(100)'),
        data_value = T2.x.value('value[1]', 'varchar(max)'),
        data_text = T2.x.value('text[1]', 'varchar(max)')
    FROM
        #e c
    CROSS APPLY
        c.event_data.nodes('event/*') T2(x)
),
cte4 AS
(
    SELECT
        *
    FROM
        cte3
    WHERE
        data_name IN ('error_number', 'severity', 'message', 'database_name', 'database_id', 'client_hostname', 'client_app_name', 'collect_system_time', 'username')
)
SELECT
    *
FROM
    cte4
PIVOT
    (MAX(data_value) FOR data_name IN ([error_number], [severity], [message], database_name, database_id, username, client_hostname, client_app_name, collect_system_time)) T
WHERE
    [severity] > 10
ORDER BY
    event_timestamp DESC
go

and voilà, now all errors can be easily identified:

It’s now very easy to create reports and dashboards to monitor the system in (near) real time. And this is vital when you run a 24x7 online business.

WCF Error when using “Match Data” function in MDS Excel AddIn

manowar — Thu, 29 Mar 2012 07:46:16 GMT

If you’re using MDS and DQS with the Excel Integration you may get an error when trying to use the “Match Data” feature that uses DQS in order to help to identify duplicate data in your data set.

The error is quite obscure and you have to enable WCF error reporting in order to have the error details and you’ll discover that they are related to some missing permission in MDS and DQS_STAGING_DATA database.

To fix the problem you just have to give the needed permession, as the following script does:

use MDS
go

GRANT SELECT ON mdm.tblDataQualityOperationsState TO [VMSRV02\mdsweb]
GRANT INSERT ON mdm.tblDataQualityOperationsState TO [VMSRV02\mdsweb]
GRANT DELETE ON mdm.tblDataQualityOperationsState TO [VMSRV02\mdsweb]
GRANT UPDATE ON mdm.tblDataQualityOperationsState TO [VMSRV02\mdsweb]

USE [DQS_STAGING_DATA]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [VMSRV02\mdsweb]
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [VMSRV02\mdsweb]
ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin] TO [VMSRV02\mdsweb]
GO

Where “VMSRV02\mdsweb” is the user you configured for MDS Service execution. If you don’t remember it, you can just check which account has been assigned to the IIS application pool that your MDS website is using:

SQL Server 2012 RTM Available!

manowar — Tue, 06 Mar 2012 22:34:28 GMT

SQL Server 2012 is available for download!

http://www.microsoft.com/sqlserver/en/us/default.aspx

The Evaluation version is available here:

http://www.microsoft.com/download/en/details.aspx?id=29066

and along with the SQL Server 2012 RTM there’s also the Feature Pack available:

http://www.microsoft.com/download/en/details.aspx?id=29065

The Feature Pack is rich of useful and interesting stuff, something needed by some feature, like the Semantic Language Statistics Database some other a very good (I would say needed) download if you use certain technologies, like MDS or Data Mining. Btw, for Data Mining also the updated Excel Addin has been released and it’s available in the Feature Pack.

As if this would not be enough, also the SQL Server Data Tools IDE has been released in RTM:

http://msdn.microsoft.com/en-us/data/hh297027

Remember that SQL Server Data Tool is completely free and can be used with SQL Server 2005 and after.

Happy downloading!

Execute a SSIS package in Sync or Async mode from SQL Server 2012

manowar — Thu, 24 Nov 2011 16:45:53 GMT

Today I had to schedule a package stored in the shiny new SSIS Catalog store that can be enabled with SQL Server 2012. (http://msdn.microsoft.com/en-us/library/hh479588(v=SQL.110).aspx)

Once your packages are stored here, they will be executed using the new stored procedures created for this purpose. This is the script that will get executed if you try to execute your packages right from management studio or through a SQL Server Agent job, will be similar to the following:

Declare @execution_id bigint

EXEC [SSISDB].[catalog].[create_execution] @package_name='my_package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'BI', @project_name=N'DWH', @use32bitruntime=False, @reference_id=Null

Select @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0

DECLARE @var1 bit = 0
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'DUMP_ON_ERROR', @parameter_value=@var1

EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

The problem here is that the procedure will simply start the execution of the package and will return as soon as the package as been started…thus giving you the opportunity to execute packages asynchrously from your T-SQL code. This is just *great*, but what happens if I what to execute a package and WAIT for it to finish (and thus having a synchronous execution of it)?

You have to be sure that you add the “SYNCHRONIZED” parameter to the package execution. Before the start_execution procedure:

exec [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=1

And that’s it .

From the RC0, the SYNCHRONIZED parameter is automatically added each time you schedule a package execution through the SQL Server Agent. If you’re using an external scheduler, just keep this post in mind .

DAX editor for SQL Server

manowar — Wed, 23 Nov 2011 06:58:10 GMT

One of the major criticism to DAX is the lack of a decent editor and more in general of a dedicated IDE, like the one we have for T-SQL or MDX.

Well, this is no more true. On Codeplex a very interesting an promising Visual Studio 2010 extension has been released by the beginning of November 2011:

http://daxeditor.codeplex.com/

Intellisense, Syntax Highlighting and all the typical features offered by Visual Studio are available also for DAX.

Right now you have to download the source code and compile it, and that’s it!