Active Directory - from SQL

Here's one way to get to AD for SQL, to do things like synchronizing your application's security with AD.

I have been able to link the server to query AD.  I use a Cursor to go through and import OU's, Groups, and associated Users.  This works like a charm, but upon further review, not all Group members are coming across. 

Query For OU's:  I only care about those under Departmental Accounts.  This returns accurately.

SELECT  name, distinguishedName
FROM
OPENROWSET('ADSDSOObject',
'adsdatasource;',
'SELECT  name, distinguishedName
   FROM ''LDAP://corp.company.com/OU=Departmental Accounts,OU=company,DC=corp,DC=company,DC=com''
  WHERE objectClass = ''organizationalUnit'' ')

Query For Groups:  This is run within a Cursor from the above result set.  @DN is the distinguishedName for each OU from above.  This returns accurate results.

SELECT mail, displayName, distinguishedName
FROM
OPENROWSET('ADSDSOObject',
'adsdatasource;',
'SELECT  mail, displayName, distinguishedName
   FROM ''LDAP://corp.company.com/" + @DN + "''
  WHERE objectClass = ''Group'' ')

Query For Group Members:  This runs within a Cursor from the Groups result set above.  @DN is the distinguishedName for each Group from above.  This returns some Group members but not others and is where I need help.

SELECT mail, displayName, distinguishedName
FROM
OPENROWSET('ADSDSOObject',
'adsdatasource;',
'SELECT  mail, displayName, distinguishedName
    FROM ''LDAP://corp.company.com/OU=Departmental Accounts,OU=company,DC=corp,DC=company,DC=com''
WHERE memberOf = ''" + @DN + "''

Most examples on the web use OpenQuery.  Anytime I try that method I get an error.  Permissions is not an issue, or so it should not be given that I had Domain Admin rights while trouble-shooting this.  The following query is an example of a Group that does not return any results:

SELECT mail, displayName, distinguishedName
FROM
OPENROWSET('ADSDSOObject',
'adsdatasource;',
'SELECT  mail, displayName, distinguishedName
   FROM ''LDAP://corp.company.com/OU=Departmental Accounts,OU=Company,DC=corp,DC=company,DC=com''
  WHERE memberOf = ''CN=Analysts,OU=Marketing,OU=Departmental Accounts,OU=Company,DC=corp,DC=company,DC=com''
    AND objectClass = ''User'' ')

I'v got pretty far after many trial and error routines.  This has me stumped.  Any help would be appreciated!

Active Directory - Yet Again

Ward Pond's SQL Server blog : The OPENROWSET Trick: Accessing Stored Procedure Output In A SELECT Statement

 

The OPENROWSET Trick: Accessing Stored Procedure Output In A SELECT Statement

This nifty little trick will allow you, with some limitations, to treat the output of a stored procedure as a named SQL record set rather than creating a table and going through an INSERT.. EXEC process. The output of the stored procedure is then available for direct manipulation in SELECT statements, JOINs, etc.

Ward Pond's SQL Server blog : The OPENROWSET Trick: Accessing Stored Procedure Output In A SELECT Statement

Chris Webb's BI Blog: Dimension Security Tips, Tricks and Problems

Some helpful information on dimension-level security in Analysis Services.

I've been doing some work with complex dimension security recently and come across a number of issues which I've either known about but never blogged about, or only just become aware of. It's a bit of a random list but hopefully it'll help someone out there...

Chris Webb's BI Blog: Dimension Security Tips, Tricks and Problems

Monitoring and Recording DDL changes on SQL 2005 (NARC) - SQL Server Central

Poor man's SQL auditor.

The classic rhetorical question when diagnosing a technical issue "what changed on the server?", may now be possibly answered. Since some of my servers do not have Service Broker and Notification Services activated or installed, I had to come up with a solution that would work on my installed base of SQL 2005 as I originally intended to make this a Notification Services running asynchronously just a learning exercise.

Monitoring and Recording DDL changes on SQL 2005 (NARC) - SQL Server Central

How to Summarize Excel Data For Reporting and Analysis, An Excel 2003 Tutorial

 

Learn something new every day... maybe even a couple things.

Business Intelligence with Microsoft Excel Formula Arrays and Named Ranges.

In Excel, you normally enter a formula by typing it into the formula bar and then pressing the Enter key. You array enter a formula by typing it into your formula bar, holding down the Ctrl and Shift keys, and then pressing Enter.

After you array-enter a formula, the formula bar shows that it begins and ends with braces, like this:

{=A1}

To be clear, you do not type in those "{" and "}" characters yourself. Instead, Excel displays them after you array-enter a formula by pressing Ctrl+Shift+Enter.

How to Summarize Excel Data
For Reporting and Analysis

Excel offers several ways to summarize data quickly and easily.
We explain the most powerful and flexible approaches, which
include using Excel arrays.

How to Summarize Excel Data For Reporting and Analysis, An Excel 2003 Tutorial

Microsoft vs. Teradata | Reg Developer

Comparing Microsoft vs. Teradata.  Don't flame me, I'm just the messenger! :)

Given this common wish list, how did Microsoft and Teradata end up with such different strategies?

Microsoft vs. Teradata | Reg Developer

SSIS Junkie : SQL Server Analysis Services - Best Practices

 

As a pointer to best practice I would recommend that you always use named queries in your DSV.

SSIS Junkie : SQL Server Analysis Services

Chris Webb's BI Blog: Using Non_Empty_Behavior With YTD calculations

Chris hacks away at a slow performing MDX query... by creating a new fact table view.

The query now runs in, wait for it, in 7 seconds on a cold cache in SQLMS and if you look in Profiler you'll see that in fact the majority of that time is taken up by SQLMS rendering the resultset - it takes just over 2.5 seconds to actually run on the server.

Chris Webb's BI Blog: Using Non_Empty_Behavior With YTD calculations

SQL Server 2008 CTP6 gives you PowerShell - Rob Farley

 

SQL Server 2008 CTP6 gives you PowerShell

The sixth CTP of SQL Server 2008, made available this past week at http://connect.microsoft.com/sql provides a PowerShell provider for SQL Server, which is backwards compatible with SQL Server 2005 as well. I showed it a little at the User Group in Melbourne a few days ago, and some people seemed to like it.

SQL Server 2008 CTP6 gives you PowerShell - Rob Farley

Free Online CSV to SQL Converter - mySQL Native Support!

 

This tool provides an easy way to convert CSV files to SQL to be inserted into a database. Simply upload your CSV file and the resulting page will display the INSERT statements to turn the CSV file into records in a database table!

Free Online CSV to SQL Converter - mySQL Native Support!

Aggregation design, row counts and the 1/3 rule

 

Mosha and Thierry give us multiple examples of how to provide annotations in SSAS 2005.

The solution revolve around the fact that Analysis Services Storage engine doesn’t know how to handle String for Measure data but does know for Dimension data and also the fact that the Formula engine does know how to handle text data.

So in a nutshell, Comment and Annotation are handled as Dimension members, entered by the user using Dimension writeback, then are retrieved as part of the measure group using a calculated member and thus queried from any front-end using regular MDX.

Handling Comment or Annotation as dimension member can seem scary at first, but since Yukon doesn’t handle the entire Dimension in memory anymore, its scalability capability in term of size of dimension members has greatly increased and can easily go above 50 millions members. For the very large majority of applications it seems reasonable to assume that this type of volume will never be reached. Indeed if we assume that an application has a lifespan of 5 years, then it gives us 10 millions members or Comment per year. Now let’s say that this application has 1000 active users, then every user has a workspace of 10 000 comments per year or 833 comments per month. It is a lot of text to enter every month for a single user…

Aggregation design, row counts and the 1/3 rule

The Road to Know Where: Download Free Office 2007 Software from Microsoft

 

Even though Microsoft Office 2007 has only been release for a short time, there are already some free additions from Microsoft. If you're looking for free Microsoft Office 2003 software -- click here!

The Road to Know Where: Download Free Office 2007 Software from Microsoft

K. Scott Allen : Three Rules for Database Work

 1. Never use a shared database server for development work.

2. Always Have a Single, Authoritative Source For Your Schema

3. Always Version Your Database  

K. Scott Allen : Three Rules for Database Work

Five sqlcmd features to automate SQL Server database tasks

 

Five sqlcmd features to automate SQL Server database tasks

Roman Rehak
02.18.2008
Rating: -4.20- (out of 5)

Five sqlcmd features to automate SQL Server database tasks

Microsoft Gets Gartner's Business Intelligence Top Ranking -- Business Intelligence

Looks like Microsoft's BI Strategy (KPIs for everyone in all of our products) is working out.

Business Objects, Cognos, and Microsoft were placed among the leaders in Gartner's just-released Magic Quadrant for Business Intelligence Platforms, 2008. But the research firm placed Microsoft above the other two in its ability to execute, including the competitiveness and success of its BI goods and services, its viability and investment in BI, and the execution of its sales and pricing. In last year's BI platform report, Gartner put Microsoft in the challenger quadrant.

Microsoft Gets Gartner's Business Intelligence Top Ranking -- Business Intelligence

Cubes and Universes « Stéphane-Robert Langer

A couple years old but still worth a read to better understand differences between BO and SSAS.

Mark Miller recently posted a link to a whitepaper comparing the BI offerings from Business Objects and Microsoft. This is something I had been looking for for quite a while and I think it does a good job of presenting a comparative history of both platforms. The content remain fairly high level, though. What I’d like to do here is to discuss some of the conceptual differences between a Business Objects (BO) universe and a Microsoft SQL Server Analysis Services (SSAS) cube.

Cubes and Universes « Stéphane-Robert Langer

format date excel export reporting services - Google Search

Not a lot of results on this one... so I figured it out myself.

Instead of formatting just the field, you need to format the entire column in order to retain the date format in Reporting Services Excel export.

Hope this helps someone else.

format date excel export reporting services - Google Search

Microsoft Research: MSAGL: Microsoft Automatic Graph Layout

A cool Visio-like graph tool for under $300 that you can drop into C# code.

MSAGL is a .NET tool for graph layout and viewing. It was developed in Microsoft Research by Lev Nachmanson. MSAGL is built on the principle of the Sugiyama scheme; it produces so called layered, or hierarchical layouts. This kind of a layout naturally applies to graphs with some flow of information. The graph could represent a control flow graph of a program, a state machine, a C++ class hierarchy, etc.

Microsoft Research: MSAGL: Microsoft Automatic Graph Layout

JIT Development - Managed Visio

Visio (and office in general) is still in the dark ages with its automation support.  Namely COM+ & VBA.  It works, though I'm a C# kind of guy.

Luckily here's one project that may help to improve the coding experience.  It helps that I just installed VS 2008.

Project Description

VisioAutoExt is a library that simplifies using Visio 2007 from C#

VisioAutoExt - Home

Diagrammatic Reasoning Related Research Sites

Interesting site for those looking at Data Visualization, OLAP and Silverlight application ideas, as I am.

Diagrammatic Reasoning Related Research Sites

Diagrammatic Reasoning Related Research Sites

and this to demonstrate a cube example.

http://www.wideman-one.com/gw/Models/sdsubigpic/DataCube1.htm

This is from '96.  How long has OLAP been around anyway?

According to the OLAP Report, before 1962...

http://www.olapreport.com/origins.htm

What's even more incredible than this tidbit of information?  Cognos Planning is based on APL, the OLAP language designed by IBM... and once again owned by IBM as of last year.  Over 37 years later.

Who has one of the most long-lived software products ever?  Oracle's Express, which has gone through over 35 years of development.

Who had features that are still not widely adopted?  Comshare's System W had these features:

• Full non-procedural rules
• Full screen multidimensional viewing and data editing
• Automatic recalculation
• Batch integration with relational data

#1 is in IBM Cognos Applix TM1.  I can see Silverlight hitting number 2 pretty easily with a writeback cube and a cube browser.  #3 is in Analysis Services.   #4... well... that would be nice.  I have built a few custom solutions around merging MDX & SQL data.  It would be great to see these pulled into a single view without writing complex MDX.

Oracle might offer #4 with its materialized views.

Which OLAP server didn't integrate with Excel when it was first released?  Microsoft OLAP.

What's the most popular BI tool in the world?  Microsoft Excel.

So the killer app to promote Silverlight adoption for BI (other than installing it for the MS downloads site) is to create a Silverlight visualization plugin for Microsoft Excel (Services?) and PerformancePoint.

I guess I've answered my data visualization question.

Visio Guy » Blog Archive » SharePoint Planning Visio Diagrams

 

For your convenience, here's the list of Visio drawing-links:

Models

• Model: Business Intelligence
• Model: Office SharePoint Server Application Security
• Model: Office SharePoint Server Backup and Restore
• Model: Office SharePoint Server Topologies
• Model: Office SharePoint Server Baseline Site Hierarchies
• Model: Office SharePoint Server Customization
• Model: Office SharePoint Server Database Administration
• Model: Office SharePoint Server Enterprise Content Management
• Model: Office SharePoint Server Extranet Topologies
• Model: Office SharePoint Server Inter-Farm Shared Services
• Model: Office SharePoint Server Products Migration
• Model: Office SharePoint Server Search Administration
• Model: Office SharePoint Server Shared Services
• Model: Upgrading Office SharePoint Server Products
• Model: Upgrading SharePoint Portal Server 2003 to Office SharePoint Server (1 of 2)
• Model: Upgrading SharePoint Portal Server Version 2 to Office SharePoint Server (2 of 2)

Scenarios

• Scenario: Enterprise Content Management
• Scenario: InfoPath Forms Services Customer Interaction Form
• Scenario: InfoPath Forms Services Expense Reporting
• Scenario: InfoPath Forms Services Insurance Claims
• Scenario: InfoPath Forms Services Permits
• Scenario: InfoPath Forms Services Supplier Survey
• Scenario: Keeping Connections to Data Sources Current
• Scenario: Large Corporate Internet Site (Infrequent Changes to Web Content)
• Scenario: Office SharePoint Server Call Center
• Scenario: Office SharePoint Server Corporate Intranet Portal Site
• Scenario: Office SharePoint Server Departmental Portal Site
• Scenario: Office SharePoint Server Equities Research
• Scenario: Office SharePoint Server Informational Portal Site
• Scenario: Office SharePoint Server Out-Hosted Portal Sites (ASPs)
• Scenario: Online News Internet Site (Frequent Web Content Updates and Searching)
• Scenario: Preserving "One Version of the Truth" for Distributed Workbooks
• Scenario: Records Management in Office SharePoint Server
• Scenario: Rendering Data from Spreadsheets to Web Parts

Flowcharts

• Office SharePoint Server Planning Flowchart, Part I (application administrators)
• Office SharePoint Server Planning Flowchart, Part II (IT pros)
• Office SharePoint Server Deployment Flowchart

Related Posts:

• Integrating Visio 2007 with Microsoft Technologies
• Shapes
• Microsoft Exchange Server 2007 Stencil
• Visio Links
• Visio Conference 2008 - Awards, Notes, Epilogue

Visio Guy » Blog Archive » SharePoint Planning Visio Diagrams

Check List for Performance Optimization in SQL Server Analysis Service 2005

 

No matter what your platform or the tools that you are using, performance is almost always the number one feature that customers require.  In the case of SQL Server Analysis Service (SSAS), there are four areas where we can improve the performance. They are database design, processing, querying and configuring SSAS.

Check List for Performance Optimisation in SQL Server Analysis Service 2005

Generating Missing Dates and Numbers - SQL Server Central

Here's how to find gaps in data with an extremely fast solution. 

This article presents an approach to generate missing numbers and dates without using a temp table. I do not intend to claim that this approach is better than the others. There is no programming approach that suites all requirements. Based on the specific application scenario a certain approach may be found suitable than the others. I guess some of you may find this approach easier to integrate into your application specific requirements.

Source: Generating Missing Dates and Numbers - SQL Server Central

101 Design Patterns & Tips

Lots of good ones here... 

101 Design Patterns & Tips for Developers

Source: 101 Design Patterns & Tips

Revenge of the BLOB - SQL Server Central

One of my articles has been published to SQL Server Central.  It was an odd scenario that I had never seen put to use in SQL Server before.  To store a table of records records in a table column as a blob sounds like madness to me, or perhaps something retro from the '80s.

I think it was more fun digging up the origin of Blob than finding a solution though...

Link to Revenge of the BLOB - SQL Server Central

The Data Platform Insider : Microsoft SQL Server 2008 Roadmap Clarification

Looks like SQL 2008 is not quite ready for primetime just yet...  

To continue in this spirit of open communication, we want to provide clarification on the roadmap for SQL Server 2008. Over the coming months, customers and partners can look forward to significant product milestones for SQL Server.  Microsoft is excited to deliver a feature complete CTP during the Heroes Happen Here launch wave and a release candidate (RC) in Q2 calendar year 2008, with final Release to manufacturing (RTM) of SQL Server 2008 expected in Q3. Our goal is to deliver the highest quality product possible and we simply want to use the time to meet the high bar that you, our customers, expect.

This does not in any way change our plans for the February 27 launch and we look forward to seeing many of you in Los Angeles and other events around the world. Please keep the great feedback coming and thank you again for your ongoing support of SQL Server!

Source: The Data Platform Insider : Microsoft SQL Server 2008 Roadmap Clarification

A Reporting System Architecture - SQL Server Central

An interesting use of Database Mirroring to create a low-impact reporting solution.

These seemingly simple requirements added more complexity to the project than one might think. We mulled over numerous ideas and varying solutions to satisfy the business needs. Our final solution involved the use of a staging database setup on the reporting server (a seperate box) that is populated via SQL Server mirroring. ETL Scripts are then written in SSIS that utilize a snapshot of the mirror database to denormalize the data and populate two reporting databases (details below). The solution turns out to be elegant, to perform well and has basically zero negative impact on the current OLTP system.

A Reporting System Architecture - SQL Server Central

Comparing Multiple Datasets with the INTERSECT and EXCEPT operators

A manual Diff tool for SQL 2005...

With SQL Server 2005, Microsoft introduced the INTERSECT and EXCEPT operators to further extend what you could already do with the UNION and UNION ALL operators.

• INTERSECT - gives you the final result set where values in both of the tables match
• EXCEPT - gives you the final result set where data exists in the first dataset and not in the second dataset

The advantage of these commands is that it allows you to get a distinct listing across all of the columns such as the UNION and UNION ALL operators do without having to do a group by or do a comparison of every single column.  

Source: Comparing Multiple Datasets with the INTERSECT and EXCEPT operators

Basics of Statistics in SQL Server 2005 - Load testing without any data

For example, you can vastly increase the apparent rowcount and pagecount of the Sales.SalesOrderDetail table by issuing this command:

UPDATE STATISTICS Sales.SalesOrderDetail 
WITH ROWCOUNT=5000000, PAGECOUNT = 5000000

Why would you want to do this? SQL Server's query optimizer does things differently on small tables and on large tables. During development, you likely don't have a whole lot of data in your tables. Rather than spend time generating huge amounts of sample data, you can use the undocumented options of UPDATE STATISTICS to see how the query optimizer will treat your tables after they've been in production for a while. This can be a quick and dirty method to uncover potential performance issues before they arise, and perhaps point out the need for additional indexes or other schema work. I'd recommend confirming any such changes by retesting with realistic amounts of sample data, though

Basics of Statistics in SQL Server 2005

Microsoft PowerPoint - procedurecache-sql.ppt

 

Lots of useful stuff on optimizing queries in SQL 2005.

Becoming a Query Plan

Caching Superhero!

Microsoft PowerPoint - procedurecache-sql.ppt

Very Large Database index optimization

 

We're talking hundreds of GBs or 1 TB or more. These databases are now common on SQL Server 2000 and 2005 - at TechEd IT Forum this week (and at SQL Connections the week before) there are many customers with multi-TB databases. Any experienced DBA knows the value of running consistency checks, even when the system is behaving perfectly and the hardware is rock-solid. The two problems that people have with running a full CHECKDB on their VLDB are:

• It takes a long time to run (based on many factors – see my previous post here for details).
• It uses lots of resources – memory, CPU, IO bandwidth, tempdb space.

So it uses lots of resources for a long time. Even with a decent sized maintenance window, the CHECKDB may run over into normal operations. There's also the case of a system that's already pegged in more or more resource dimensions. Whatever the case, there are a number of options:

• Don't run consistency checks
• Run CHECKDB using the WITH PHYSICAL_ONLY option
• Use SQL Server 2005's partitioning feature and devise a consistency checking plan around that
• Figure out your own scheme to divide up the consistency checking work over several days
• Offload the consistency checks to a separate system

In Recovery... - Paul S. Randal on SQL Server

Howard @ MSFT selling BI: Proclarity tricks

 

Proclarity tricks

There is a cool trick that allows you to use a proclarity analytics server page web part in a performance point dashboard, with the toolbar functionality and supports the passing of filters.

Basically you set up the ProClarity Analytics Server Page report view like you normally would, except that after you select the desired report, you modify the server URL parameter to point to a different web page on the server.

Here are the steps. You’ll need to plug in your PAS server URLs:

#1 – start by using http://pas-server/pas  in the Server URL when configuring your PC Report View; then navigate select the PC view you wish to display

#2 – after having chosen a view, change the Server URL to:  http://pas-server/pas/en/src/proclarity.asp?uiConfig=tb;ht

#3 – Then, if you want to connect up a filter, drag the Member UniqueName property from the filter onto your PC Report View

Bonus – adding the URL parm fs; places an extra button onto the toolbar which comes in handy if you want to let end users open the underlying view in the PC Web pro client:

http://pas-server/pas/en/src/proclarity.asp?uiConfig=tb;ht;fs;

Howard @ MSFT selling BI: Proclarity tricks

Inferred Members & SSAS 2005

How to ignore missing keys in dimensions.

This is about you can handle new fact records that have one or many dimension keys that is not part of your dimension table or a dimension key is null or blank.

Either you can solve this in the ETL process by adding an inferred member in each dimension, like 'N/A' or 'unknown', or you can let SSAS2005 handle this.

This post is about the last scenario.

Thomas.I Microsoft BI & Analytics

Journey to SQL Authority with Pinal Dave

Very useful.

How many times we have wondered what were the last few queries ran on SQL Server? Following quick script demonstrates last ran query along with the time it was executed on SQL Server 2005.

SELECT deqs.last_execution_time AS [Time], dest.text AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

Journey to SQL Authority with Pinal Dave

SQL Nexus Tool - Home

 

What is SQL Nexus?

SQL Nexus is a tool that helps you identify the root cause of SQL Server performance issues. It loads and analyzes performance data collected by SQLDiag and PSSDiag. It can dramatically reduce the amount of time you spend manually analyzing data.

SQL Nexus Tool - Home

PSS SQL Server Engineers : RML Utilities for Microsoft SQL Server Released

 

The Microsoft SQL Server support team uses several internally-written utilities to make it easier to work on a typical customer support case. These utilities may also be useful to database developers and system administrators who work with Microsoft SQL Server 2000 and Microsoft SQL Server 2005.  The utilities are collectively called the RML Utilities for SQL Server.

With the RML Utilities you can answer questions such as the following:

· Which application, database or login is consuming the most resources, and which queries are responsible for that.

· Whether there were any plan changes for a batch during the time when the trace was captured and how each of those plans performed.

· What queries are running slower in today's data as compared to a previous set of data.

PSS SQL Server Engineers : RML Utilities for Microsoft SQL Server Released

SQL Courses

Large list of database resources from Joe Celko..

SQL Courses

Passing the 70-445 BI Exam

Jorg Klein has some great info if you're studying for 70-445, or MS exams in general.

http://sqlblogcasts.com/blogs/jorg/archive/2007/11/15/MCTS-_2D00_-I-passed-the-70_2D00_445-exam_2100_.aspx

And he linked to my article on Sql Server Central.

Thanks Jorg!

SQL Server Engine Tips : SQL Server 2005

 

Updating a large table is a common scenario. This is often encountered in cases of applications that performs series of tasks in the background or data warehousing loading scenarios. To reduce locking and logging resources, such update statements are broken down into smaller batches or units of work. This has been traditionally done using SET ROWCOUNT setting in SQL Server. Now, SQL Server 2005 provides you with a simpler construct that the optimizer can understand and use efficiently. TOP clause has been enhanced in SQL Server 2005 to support expressions and can be used now in all DML operations. Let's look at a quick example on how to use TOP with UPDATE:

SQL Server Engine Tips : SQL Server 2005

Rocket Science: OBJECT_NAME enhancement and OBJECT_SCHEMA_NAME addition in SQL Server 2005 SP2

I think sp_msforeachdb might be a solution that's a bit easier to read, but this works too.

Get the top 10 statements that took the most time for each database on a SQL 2005 server.

select coalesce(QUOTENAME(DB_NAME(t.dbid), '"'), '') /* NULL means resource db*/
+ N'.'
+ QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.dbid), '"')
+ N'.'
+ QUOTENAME(OBJECT_NAME(t.objectid, t.dbid), '"') as full_obj_name
, SUBSTRING(t.text, (t.statement_start_offset/2)+1,
((CASE t.statement_end_offset
WHEN -1 THEN DATALENGTH(t.text)
ELSE t.statement_end_offset
END - t.statement_start_offset)/2) + 1) AS statement_text
, t.*
from (
select *, DENSE_RANK() OVER(PARTITION BY t.dbid ORDER BY qs.total_elapsed_time) as rnk
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as t
where t.objectid is not null
) as t
where t.rnk <= 10
order by t.dbid, t.rnk;

SQL Server Engine Tips : OBJECT_NAME enhancement and OBJECT_SCHEMA_NAME addition in SQL Server 2005 SP2

MDX Outline Formulas

Essbase to MDX translation.

MDX Outline Formulas

Troubleshooting 64 bit COM+ Apps

One interesting behaviour on 64-bit systems I saw today is how applications are run, and the fact that two command prompts are available (32 & 64 bit).

"Windows gets around these issues by offering two command prompts: one 64-bit and one 32-bit. Environment variables are set according to which command environment is being used.
For example, if you open a command prompt by entering the CMD.EXE command at the Run prompt, Windows will open a 64-bit command prompt. In most cases, the %ProgramFiles% environment variable for the command environment will be set to C:\Program Files. If you run a script, the script can interact with 64-bit applications, but not with 32-bit apps.
On the flip side, if you enter the C:\Windows\SysWOW64\cmd.exe command at the run prompt, you'll be running a 32-bit command prompt. In that case, the %ProgramFiles% environment variable will be set to C:\Program Files (x86). "

http://searchwincomputing.techtarget.com/tip/0,289483,sid68_gci1218185,00.html

The key issue to remember here is that c:\program files should not be used for 32-bit apps or 32-bit COM+ components. Use c:\program files (x86) instead.

Tips, Tricks, and Advice from the SQL Server Query Optimization Team : Fun for the day - Automated Auto-Indexing!

 

Effectively, this will periodically determine top index candidates for your workload.  It currently runs in a recommendation mode, but you can also have it run fully automated if you uncomment a line in the file.

Source: Tips, Tricks, and Advice from the SQL Server Query Optimization Team : Fun for the day - Automated Auto-Indexing!

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' : Curious cursor optimization options

I don't like cursors.  Usually there are ways to avoid them, more so in SQL 2005.  But if they exist in your environment and are causing performance problems, take a look at this article.

If you have to optimize a cursor for performance, keep the following considerations in mind:

1. Always try to replace the cursor by a set-based equivalent first. If you fail to see how, do not hesitate to ask in one of the SQL Server newsgroups.
2. If you are really stuck with a cursor, then do NOT rely on the default options. They will result in the slowest of all possible option combinations
3. If you think that the FAST_FORWARD option results in the fastest possible performance, think again. I have not found one single test case where it was faster than, or even as fast as, a STATIC cursor.
4. Do NOT use the WHERE CURRENT OF syntax of the UPDATE command. Using a regular WHERE clause with the primary key values will speed up your performance by a factor of two to three.
5. Do not rely blindly on my performance results. Remember, the one thing that is always true when working with SQL Server is: “it depends”.

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' : Curious cursor optimization options

Craig Freedman's SQL Server Blog : Scans vs. Seeks

 

Scans vs. Seeks

Scans and seeks are the iterators that SQL Server uses to read data from tables and indexes.  These iterators are among the most fundamental ones that we support.  They appear in nearly every query plan.

Craig Freedman's SQL Server Blog : Scans vs. Seeks

Exporting from C#

    /// <summary>
    /// Web Utility Function For Exporting Data Set to Specified Format
    /// </summary>
    /// <param name="dsResults">Result Data Set</param>
    /// <param name="enExport">Export Enum Values</param>
    /// <param name="strColDelim">Column Delimiter value</param>
    /// <param name="strRowDelim"></param>
    /// <param name="strFileName"></param>
    public static void ExportDataSet(DataSet dsResults , ExportFormat enExport,string strColDelim, string strRowDelim, string strFileName)
    {
        DataGrid dgExport = new DataGrid();      
        dgExport.AllowPaging =false;
        dgExport.DataSource =dsResults;
        dsResults.DataSetName ="WebERP";
        dgExport.DataMember =  dsResults.Tables[0].TableName;
        dgExport.DataBind();          
        System.Web.HttpContext.Current.Response.Clear();          
        System.Web.HttpContext.Current.Response.Buffer= true;
        System.Web.HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
        System.Web.HttpContext.Current.Response.Charset = "";
        System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" +strFileName );
        switch(enExport.ToString().ToLower())
        {
            case "xls":
            {
                System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";                                      
                System.IO.StringWriter oStringWriter = new System.IO.StringWriter();                                      
                System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
                dgExport.RenderControl(oHtmlTextWriter);
                System.Web.HttpContext.Current.Response.Write(oStringWriter.ToString());
                break;
            }
            case "custom":
            {
                string strText;                          
                System.Web.HttpContext.Current.Response.ContentType = "text/txt";                                                  
                System.IO.StringWriter oStringWriter = new System.IO.StringWriter();                  
                System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);                  
                dgExport.RenderControl(oHtmlTextWriter);
                strText = oStringWriter.ToString();                  
                strText = ParseToDelim(strText ,strRowDelim,strColDelim);
                System.Web.HttpContext.Current.Response.Write(strText);
                break;
            }
            case "csv":
            {
                string strText;      
                strRowDelim = System.Environment.NewLine;
                strColDelim = ",";
                System.Web.HttpContext.Current.Response.ContentType = "text/txt";
                System.IO.StringWriter oStringWriter = new System.IO.StringWriter();                  
                System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);                                      
                dgExport.RenderControl(oHtmlTextWriter);
                strText = oStringWriter.ToString();                  
                strText = ParseToDelim(strText ,strRowDelim,strColDelim);
                System.Web.HttpContext.Current.Response.Write(strText);
                break;
            }          
            case "tsv":
            {
                string strText;      
                strRowDelim = System.Environment.NewLine;
                strColDelim = "\t";
                System.Web.HttpContext.Current.Response.ContentType = "text/txt";                                                  
                System.IO.StringWriter oStringWriter = new System.IO.StringWriter();                  
                System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);                                      
                dgExport.RenderControl(oHtmlTextWriter);
                strText = oStringWriter.ToString();                  
                strText = ParseToDelim(strText ,strRowDelim,strColDelim);
                System.Web.HttpContext.Current.Response.Write(strText);
                break;
            }
            case "xml":
            {
                System.Web.HttpContext.Current.Response.ContentType = "text/xml";                  
                System.Web.HttpContext.Current.Response.Write(dsResults.GetXml());                                      
                break;
            }
            case "htm":
            {                  
                System.Web.HttpContext.Current.Response.ContentType = "text/html";
                System.IO.StringWriter oStringWriter = new System.IO.StringWriter();                  
                System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
                dgExport.RenderControl(oHtmlTextWriter);                  
                System.Web.HttpContext.Current.Response.Write(oStringWriter.ToString());                  
                break;
            }
        }
    }

}

#region "Export To a Delim Format"
public static string ParseToDelim(string strText, string  strRowDelim , string strColDelim)
{
    Regex objReg = new Regex(@"(>\s+<)",RegexOptions.IgnoreCase);                  
    strText = objReg.Replace(strText,"><");
    strText = strText.Replace(System.Environment.NewLine,"");
    strText = strText.Replace("</td></tr><tr><td>",strRowDelim);
    strText = strText.Replace("</td><td>",strColDelim);
    objReg = new Regex(@"<[^>]*>",RegexOptions.IgnoreCase);                  
    strText = objReg.Replace(strText,"");              
    strText = System.Web.HttpUtility.HtmlDecode(strText);
    return strText;
}
#endregion
      
public enum ExportFormat
{
    XML,
    XLS,
    HTML,
    CSV,
    CUSTOM,
    TSV  
}

Importing Text-based data: Workbench

 

/* Robyn and Phil return with some fresh ideas about how to import text files into SQL Server, without resorting to DTS or SSIS scripting. They go on to show how much can be done in TSQL */

Importing Text-based data: Workbench

htmlSQL - live example

 

htmlSQL is a experimental PHP class which allows you to access HTML values by an SQL like syntax.
This means that you don't have to write complex functions (regular expressions) to extract specific values.

htmlSQL - live example

BOCOG modifies Phase 2 domestic ticketing policy - The Official Website of the Beijing 2008 Olympic Games

Sounds like Beijing Ticketmaster ran out of log space in their database...  damn holes in the optimization processes.  I hate those things...  Should have put in some failover systems or something. though that probably wouldn't have kept up with the onslaught in demand. 

Within the first hour, from 9:00am to 10:00am, the page view (PV) volume of the official ticketing website of the Beijing 2008 Olympic Games (http://tickets.beijing2008.com) soared to eight million, while the number of calls to the ticketing hotline (+8610 952008) exceeded 3.8 million.

That's a lot of hits.

A ticketing official later said that after Phase 2 of the ticket sales began, an excessive volume of page views to the official ticketing website affected the database performance, causing in a "hole" in the optimization of the database processes and slowing the online search speed. In addition, new ticket buyers were unable to register with the system as a result.

After the second phase of the domestic ticket sales was launched on October 30, the public's purchase of tickets surpassed the system processing capability by more than eight times, leading to a "paralysis" of the official Olympic ticketing website. As a result, BOCOG decided to temporarily suspend the Phase 2 domestic ticket sales and issued an explanation for the situation on October 31.

On November 5, BOCOG released a new ticketing policy based on a comprehensive analysis of the situation and system capability.

The new ticketing policy for the Phase 2 domestic ticket sales institutes a unified ticket lottery system, whereby the order of ticket application submission will not matter. The ticket purchase limit will be adjusted accordingly, and the public may submit ticket applications from December 10 through December 30.

According to reports, the BOCOG Ticketing Center immediately began to work with the BOCOG Technology and Security departments and Beijing Gehua Ticketmaster Ticketing Co., Ltd to adopt measures to solve the technical problems as soon as they arose.

Source: BOCOG modifies Phase 2 domestic ticketing policy - The Official Website of the Beijing 2008 Olympic Games

More here. http://blogs.techrepublic.com.com/tech-news/index.php?cat=931

PC World - No Price Hike for SQL Server

Looks like the SQL 2008 price is staying the same as 2005.  Interesting to note that many customers have not even upgraded to SQL 2005 yet.  Launching 2008 should speed adoption of 2005. Customers always seem to want to stay 1-2 versions behind because of the appearance of stability & being a "proven" architecture.  In my opinion there is no reason not to ditch SQL 2000 for SQL 2005, if you have any need whatsoever for better reporting and performance capabililties.

Source: PC World - No Price Hike for SQL Server

Using 64-bit architecture and the increased memory capacities of SQL 2005 Enterprise should net some serious performance improvements for those who upgrade. Not to mention the management views that will make analyzing and boosting performance in systems much easier.

Hopefully they launch the first service pack a month after, so that the early-adopter crowd will go all in on an upgrade.

One feature being hyped is "Pervasive Insight".  This appears to be the external auditing capabilities that wrap around the database.  There also appears to be some integration of the BIDS add-in into the base platform, to troubleshoot issues with dimension relationships and warnings in Analysis Services.

More info below.

http://blogs.msdn.com/ajaiman/archive/2007/06/05/sql-server-2008-june-ctp.aspx