SQL Server Management Data Warehouse (MDW)

The SQL Server Management Data Warehouse (MDW) is one of the most underappreciated features of SQL Server. But it’s a really handy feature which logs WaitStats and more, with almost no overhead (< 1% CPU). And…it does generate some nice reports which you could use to monitor your server (out of the box):

SQL Server 2012 Master DWH






























How to enable it
For more info (and how to enable it), please visit:
http://www.johnsansom.com/sql-server-management-data-warehouse-and-why-it-rocks/








Advertisements

OPENQUERY() – Query timeout expired, really?

Did you receive this error while using an OPENQUERY() statement?:

OLE DB provider “SQLNCLI10” for linked server “[LinkedServerName]” returned message “Query timeout expired”.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “SQLNCLI10” for linked server “[LinkedServerName]” reported an error. Execution terminated by the provider because a resource limit was reached.
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query ”

You might want to try helping the SQL Engine a little bit, because it’s confused by your query 😀
Try adding a TOP-clause, like for example:
SELECT TOP 3000000 *

If that didn’t help, you can always increase the timeout settings in the Server Properties window or Linked Server Properties window:

Server-Properties-Remote-Query-Timeout


Linked-Server-Properties-TimeOuts








Yahoo! 24TB SSAS Cube – Big Data Case Study + Slides

Thanks Denny Glee for sharing this.

It’s old news, but still it’s very cool 🙂
I always thought that Yahoo! had a 7TB SSAS Cube, but it has been growing of course.
As of dec 2012, it reached 24TB!!

Microsoft Case Study:
Yahoo! Improves Campaign Effectiveness, Boosts Ad Revenue with Big Data Solution

Some key numbers from this case study include:

24TB Analysis Services MOLAP cube
2PB source data of a 14PB Hadoop cluster
700M unique users, 47% of the global online population
3.5B ad impressions/day

Yahoo-24TB-Cube-Platform-Architecture

Yahoo-24TB-Cube-Querying-the-Platform-Architecture

More slides can be found here.

Source:
http://dennyglee.com/2012/12/08/yahoo-24tb-ssas-big-data-case-study-slides/








Having ‘INSERT EXEC nested issues’?

Without manual intervention, one level of INSERT…EXECUTE is the limit

An INSERT EXEC statement cannot be nested.

It is telling you is that you can’t have a proc that does an INSERT-EXEC operation call another proc that also does an INSERT-EXEC operation.

Two basic steps to solve this:

1) Create a #Temp table or @Table var
2) OPENROWSET your stored procedure data into it


INSERT INTO #YOUR_TEMP_TABLE
SELECT * FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off EXEC [ServerName].dbo.[StoredProcedureName] 1,2,3')



Or check out one of these links:
http://sqlserverplanet.com/sql/insert-stored-procedure-results-into-table/
http://blog.coryfoy.com/2005/07/inserting-the-results-of-a-stored-procedure-to-a-temp-table/
http://barry-king.com/2008/05/06/insert-into-temporary-table-from-stored-procedure/
http://www.informit.com/articles/article.aspx?p=25288&seqNum=6








OPENROWSET – The object has no columns or the current user does not have permissions on that object

Today I stumbled upon this little annoying issue.
If you are trying to run a stored procedure using OPENROWSET, for example:

SELECT *
FROM OPENROWSET('SQLNCLI','Server=(LOCAL);Trusted_Connection=Yes;Database=DB_Name','EXEC [DB_Name].dbo.SP_Name')

and you’ve received the following error:

Msg 50000, Level 16, State 1, Line 231
Cannot process the object [T-SQL statement]. The OLE DB provider "SQLNCLI10" for linked server [LinkedServerName] indicates that either the object has no columns or the current user does not have permissions on that object.

The thing is that I wasn’t able to solve it properly, just found a work-around thanks to Google:

By adding ‘SET FMTONLY OFF; SET NOCOUNT ON;‘ it will ignore the metadata as it seems, it will simply return the resultset without validating:

SELECT *
FROM OPENROWSET('SQLNCLI','Server=(LOCAL);Trusted_Connection=Yes;Database=DB_Name','SET FMTONLY OFF; SET NOCOUNT ON; EXEC [DB_Name].dbo.SP_Name')



More info about it:

The OLE DB provider “SQLNCLI10” for linked server indicates that either the object has no columns or the current user does not have permissions on that object.
When one of the columns has an invallid name this can occurs. This can be fixed by altering the sys.spt_columns_view by replacing the c.name column in quotename (c.name).

To avoid this error we need to check if our SP returns a result set that has two or more columns with the same name or does not have any column name. Next we need to set NOCOUNT ON at the beginning of the procedure (just after the begin statement). If these two things are correct then the statement will execute successfully.








From DATETIME(OFFSET) to VARCHAR(x), a world of differences :)

Always use the smallest data type, but in some cases this can really bite you.
In my example, I’ve both DATETIME and DATETIMEOFFSET types in a single table.
‘Simply’ converting/casting both to strings and expecting similar results, isn’t an option.

Instead of the ‘Jan 2 2012 9:15AM’, it would be nice to just have it like ‘2012-01-02 09:15:48’
But…you’ll have to do that yourself 🙂

datetime(offset)-to-(n)varchar-print

datetime(offset)-to-(n)varchar-select

Code

PRINT CAST('2012-01-02 09:15:48' AS datetime)
PRINT CAST('2012-01-02 09:15:48.0000000' AS datetimeoffset)
PRINT CAST('2012-01-02 09:15:48.7529001 +01:00' AS datetimeoffset)
PRINT ''
PRINT CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48' AS datetime),127)
PRINT CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48.0000000' AS datetimeoffset),127)
PRINT CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48.7529001 +01:00' AS datetimeoffset),127)

SELECT '1| -- '+CAST(CAST('2012-01-02 09:15:48' AS datetime) AS VARCHAR(100)) AS Results
UNION
SELECT '2| -- '+CAST(CAST('2012-01-02 09:15:48.0000000' AS datetimeoffset) AS VARCHAR(100))
UNION
SELECT '3| -- '+CAST(CAST('2012-01-02 09:15:48.7529001 +01:00' AS datetimeoffset) AS VARCHAR(100))
UNION
SELECT '4| -- '+CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48' AS datetime),127)
UNION
SELECT '5| -- '+CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48.0000000' AS datetimeoffset),127)
UNION
SELECT '6| -- '+CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48.7529001 +01:00' AS datetimeoffset),127)









T-SQL: How to generate a set of dates (time dimension) in a View itself?

Today I wanted to add all Saturdays and Sundays to our so called NonBusinessDays-table.
An easy join with a Time dimension/table would be fairly simple, but it also creates a dependency between the View and the Table itself. I didn’t want that, what I wanted to accomplish is to generate that mini Time dimension in the View itself (so without any other dependencies except for the actual so called NonBusinessDays-table).

So imagine something like:

SELECT * FROM dbo.tbl_NonBusinessDays
UNION
(
SELECT 'all Saturdays and Sundays between 2010 and 2020'
)

Normally a simple query like this would do the trick (inspired by this thread):

WITH AllWeekendDates(WeekendDate)
AS
(
SELECT CAST('2010-01-01 00:00:00.000' AS DATETIME) --From the year 2010
UNION ALL
SELECT DATEADD(DAY,1,WeekendDate) FROM AllWeekendDates
WHERE DATEPART(YEAR,DATEADD(DAY,1,WeekendDate)) < 2020 --Until the year 2020
)

SELECT
WeekendDate
,DATENAME(WEEKDAY,WeekendDate) AS DayName
FROM AllWeekendDates
WHERE DATENAME(WEEKDAY,WeekendDate) IN ('Saturday','Sunday')
OPTION (MAXRECURSION 0)

The results:

CTE-WeekendDays-Results

But! You’re not allowed to use the OPTION-statement in a View 🙂

OPTION (MAXRECURSION limit) is not allowed in view and UDF definitions

An easy thing to do is to move the ‘OPTION (MAXRECURSION 0)’ from inside the View to the select query that’s using the View:
http://benchmarkitconsulting.com/colin-stasiuk/2010/04/12/maxrecursion-with-a-cte-in-a-view/

Like this:

SELECT [WeekendDate]
FROM [dbo].[vw_WeekendDates]
OPTION (MAXRECURSION 0)

A trick that I found while Googling is to use numbers (like for example the surrogate key of a large table or by using ROW_NUMBER()):
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139267 (bottom post)

SELECT DATEADD(d,number,'9/11/2009')AS Date
FROM master..spt_values
WHERE number BETWEEN 1 AND
DATEDIFF(d,'9/11/2009',GETDATE())
AND type = 'P'

Now, let’s combine the original query (that included the OPTION (MAXRECURSION 0) statement) with this fancy trick.
The ideal query to use in a View will be this one:


SELECT DATEADD(d, [Row],'2010-01-01') AS Date --From the year 2010
FROM (
SELECT TOP 10000 [Row] = ROW_NUMBER() OVER(ORDER BY v1.Number ASC)
FROM master.dbo.spt_values v1
CROSS JOIN master.dbo.spt_values v2
) d
WHERE DATENAME(WEEKDAY, DATEADD(d, [Row],'2010-01-01')) IN ('Saturday','Sunday')
AND DATEPART(YEAR, DATEADD(DAY, 1, DATEADD(d, [Row],'2010-01-01'))) < 2020 --Until the year 2020

The results:
WeekendDays-from-View-Results