A while back, I wrote about using sp_ procedures in master to create a toolbox, which works fine for me... until recently I hit a strange problem. I developed a procedure that would read the definition of a table and would create a change_log table and trigger to populate that table. On my dev-environment (Windows Vista x64, SQL Server 2005 Dev Ed x64 SP2) this worked like I expected. But when I tested the procedure on a different server, it didn't!?! A very little sample that touches the essence of the problem I witnessed;

use master
go
if object_id ( 'sp_test' , 'p' ) is not null
drop procedure sp_test
go
create procedure sp_test
as
select db_name () + '.' + table_schema +'.'+ table_name
frominformation_schema. tables
order by table_schema, table_name
go
exec sp_test
go
use adventureworks
go
exec sp_test
go
use master
go
drop procedure sp_test
go

Now on my dev machine, this once listed all tables in master and once all tables in AdventureWorks, as intended. But on other servers, it returned the tables from master on both executions of sp_test (the db_name function is executed correctly in both cases; once master, once AdventureWorks). For some reason, when referencing objects, the stored procedure stayed to master. I was puzzled, but before crying BUG (out loud) I tried the newsgroups and a prompt reply from SQL Server MVP Roy Harvey pointed me to the undocumented stored procedure sp_MS_marksystemobject. Simply execute;

exec sp_MS_marksystemobject sp_test

and you won't suffer from the inconsistent behavior I witnessed.

Just checked the Prometric site and the status for my 70-432 (71-432) and 70-448 (71-448) changed from tested to passed ;-).

Yesterday evening I got into a fight with the SSIS XML Source Data Flow Source. It actually was the first time I used this Data Flow Source. I had no expectations other than, point to your XML-file and get the data. Wrong... I did point to an XML-file, generate a schema and... no columns! I ended up with the error:

Validation error. Data Flow Task: XML Source [000]: A component does not have any inputs or outputs.

In cases like this, Google should be your friend... well I read a lot about SSIS in general, some even about the XML Source but nothing to provide me with answers or even help me out. It was after reading a post by Oren Eini that I decided I was on the wrong trouble shooting track and a good night sleep would be the best next step.

This morning I started with clear vision and an open mind. No answers through Google, nothing useful on Connect, so I tried if I could reproduce my problem with an other document. The document I created was of a very simple structure;

<people>
  <person>
    <firstname>Jan</firstname>
    <lastname>Aerts</lastname>
  </person>
  <person>
    <firstname>Anne</firstname>
    <lastname>Mulders</lastname>
  </person>
</people>

It worked! But now I had to find out why the document above worked, and the other one didn't. Again I read about SSIS in general and a little something about the XML Source. In particular (SSIS in general), I stumbled upon a post by Jamie Thomson, sounding familiar and one about the XML Source I wish I came across earlier: Using XML Source by Matt Masson. I could already agree with his opening comment, especially the various degrees of success. While reading Matt's article I had this feeling my XML document might actually be to simple... it occurred to me that the XML Source was not just going to read XML, it was trying to represent the XML as one or more tables.

A very simple representation of my original document is;

<person id="1">     <firstname>Jan</firstname>     <lastname>Aerts</lastname>   </person>

<row column1="value">   <column2>value</column2>   <column3>value</column3> </row>

The simplest representations Matt used, are:

<rootgoo>   <goo>     <subgoo>value</firstname>     <moregoo>1</lastname>   </goo>   <goo>     <subgoo>value</firstname>     <moregoo>2</lastname>   </goo> </rootgoo>

<table>   <row>     <column1>value</column1>     <column2>value</column2>   </row>   <row>     <column1>value</column1>     <column2>value</column2>   </row> </table>

AND

<root>   <row CustomerID="1" TerritoryID="1" AccountNumber="AW00000001" />   <row CustomerID="2" TerritoryID="1" AccountNumber="AW00000002" /> </root>

<table>   <row column1="value" column2="value" column3="value" />   <row column1="value" column2="value" column3="value" /> </table>

So my document could never be translated to a table... to get back to Oren's post: If only SSIS had told me so with a clear error or even a dialog in the XML Source, that would have saved me a couple of hours!

Or better, since XML Source tries to get data from the XML, it could do a best effort as wrapping something that looks a single row into a table (and to take it one step simpler, represent a single value as a table with just one row and one column.). If you'd like to see some improvement here too, take a moment to vote on FeedbackID 361057.

On a version note, it happens with SQL Server 2005 (SP2) and SQL Server 2008 (PreRelease).

REDMOND, Wash. — Aug. 6, 2008 — Microsoft Corp. today announced the release to manufacturing of Microsoft SQL Server 2008 -->

As most will know sp_ does not stand for stored procedure, it stands for system stored procedure. But calling your procedure sp_something doesn't make it a system procedure automatically, it just hints the server how to resolve the procedure.

When a procedure that starts with sp_ is called, first the master database is checked if it is a real system stored procedure. The books online shows this behavior by creating a procedure in AdventureWorks called dbo.sp_who. However, since sp_who is a real system stored procedure, the existence of AdventureWorks.dbo.sp_who is always ignored. If the procedure is not a real system stored procedure, the connected database is checked for the existence of the stored procedure. If it is in the database you're currently connected to, it gets executed. If it isn't in the database you're currently connected to, it is retrieved from master (or you receive an error if it isn't there either). You can verify this behavior based on the following code.

USE AdventureWorks
GO
CREATE PROCEDURE sp_sayhello
AS
SELECT 'Hello from AdventureWorks, you are connected to '+ DB_NAME() +'.'
GO
USE master
GO
CREATE PROCEDURE sp_sayhello
AS
SELECT 'Hello from master, you are connected to '+ DB_NAME() +'.'
GO

Now, when executing sp_sayhello while connected to AdventureWorks, it will return;

Hello from AdventureWorks, you are connected to AdventureWorks

With any other database, say msdb, you get the following result.

Hello from master, you are connected to msdb

So there are two reasons why starting your stored procedure name with sp_ isn't smart;

• Performance; each time the procedure is called, a (futile) lookup is done against the master database.
• Future; if you have a stored procedure in your database called sp_dosomething and Microsoft implements a system stored procedure sp_dosomething in SQL Server, your application is broken.

There is however one scenario where creating stored procedures with sp_ is smart: When you create it in master as part of your own standardized way of working. Creating your own toolbox so to say. With SQL Server 2005 and 2008 there is an automatic separation, your sp_ procedures are created in the dbo schema by default and the real system stored procedures reside in the sys schema (the actual system stored procedures are in the mssqlsystemresource database).

Your own sp_ procedures and schemas: DON'T!!! It does not work if the schema in master isn't dbo.

USE master
GO
CREATE SCHEMA toolbox
GO
CREATE PROCEDURE toolbox.sp_sayhello
AS
SELECT 'Hello from master. You are connected to ' + DB_NAME () + '.'
GO
USE AdventureWorks
GO
EXEC sp_sayhello -- Doesn't work
EXEC toolbox.sp_sayhello -- Doesn't work
EXEC master.toolbox.sp_sayhello -- Executes against master, not AdventureWorks.
GO
USE master
GO
DROP PROCEDURE toolbox.sp_sayhello
GO
DROP SCHEMA toolbox
GO

Your own sp_ procedures and non-privileged users: Make sure the login has permissions to execute the procedure from master and that any needed permissions are held in the target database. To illustrate, a login, mapped to a user in AdventureWorks will execute a stored procedure named sp_maketable. To make this work, public (therefor any login through guest, which is appropriate for master) will receive execute permissions on the procedure and create table and alter schema permissions are granted to the user in AdventureWorks. The table is created in the default schema of the user.

USE master
GO
CREATE PROCEDURE sp_maketable
AS
CREATE TABLE tblTest (col1 int)
GO
GRANT EXECUTE ON dbo.sp_maketable TO public-- Make sure permissions allow the user to execute.
GO
CREATE LOGIN np_user WITH PASSWORD ='secret', DEFAULT_DATABASE =AdventureWorks
GO
USE AdventureWorks
GO
CREATE USER np_user FOR LOGIN np_user WITH DEFAULT_SCHEMA = Sales
GO
GRANT CREATE TABLE TO np_user-- Make sure the user has proper permissions in the database.
GO
GRANT ALTER ON SCHEMA::Sales TO np_user-- Make sure the user has proper permissions in the schema.
GO
EXECUTE AS LOGIN ='np_user'
GO
SELECT SUSER_SNAME(),USER_NAME()-- Verify it is executing as the user.
GO
EXEC sp_maketable
GO
REVERT

Important stuff when writing your own sp_ 's:

• BACKUP DATABASE master just became even more important.
• Double check on which of your own procedures you grant execute permissions.
• Use a proper naming convention, like including your company name, to avoid naming collision with future Microsoft system stored procedures.
• If a procedure exists with the same name in one of your databases and you are connected to that database, the local procedure gets executed, not the central one from master.
• Document.
• Mark your sp_ as system object with sp_MS_marksystemobject

After running through the prep-guide (looking through a pair of SQL Server 2005 glasses), I identified a couple of topics worth giving a closer look. The topics are derived from the prep-guide, my comments about the topic added in blue italics and the bulleted list refers to (mostly) BOL-resources. This post is based on the prep-guide for 70-432 with published date June 11, 2008

Installing and Configuring SQL Server 2008 (10 percent)

Configure additional SQL Server components.
This objective may include but is not limited to: SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), replication. Not that I expect this to be really different from SQL Server 2005, but if your background is just DBA (MCTS/MCITP) it may be your first encounter with the BI-components.

• Considerations for Installing Integration Services
• Considerations for Installing Analysis Services
• Considerations for Installing Reporting Services

Maintaining SQL Server Instances (13 percent)

Implement the declarative management framework (DMF).
This objective may include but is not limited to: create a policy; verify a policy; schedule a policy compliance check; enforce a policy; create a condition.

• Administering Servers by Using Policy-Based Management Entry page, includes tutorial.
• Oh, DMF = Policy-Based Management... stupid MS-Name-Game.

Back up a SQL Server environment.
This objective may include but is not limited to: operating system-level concepts. I don't expect a lot of fireworks, but the operating system-level concepts made me curious.

• Planning for Disaster Recovery Actually, I'm still curious what is meant by operating system-level concepts. This link from BOL is actually my best shot at a document where some broader considerations are presented.

Managing SQL Server Security (15 percent)

Manage transparent data encryption.
This objective may include but is not limited to: impact of transparent data encryption on backups.

• Database Encryption in SQL Server 2008 Enterprise Edition General overview of encryption, how TDE fits in and some remarks on how backup is affected. Since the topic says "not limited to", consider the whole article to be valuable.

Maintaining a SQL Server Database (16 percent)

Back up databases.
This objective may include but is not limited to: full backups; differential backups; transaction log; compressed backups; file and filegroup backups; verifying backup. Only compressed backups is to be classified as new.

• Backup Compression (SQL Server)

Performing Data Management Tasks (14 percent)

Implement data compression.
This objective may include but is not limited to: sparse columns; page/row.

• Using Sparse Columns
• Using Column Sets

Maintain indexes.
This objective may include but is not limited to: create spatial indexes; create partitioned indexes; clustered and non-clustered indexes; XML indexes; disable and enable indexes; filtered index on sparse columns; indexes with included columns; rebuilding/reorganizing indexes; online/offline. Spatial and filtered indexes on sparse columns are of interest here, along with "is not limited to" which could be indexes on hierarchyid columns.

• Working with Spatial Indexes (Database Engine) Entry Page
• Filtered Index Design Guidelines
• Using hierarchyid Data Types (Database Engine) includes Tutorial

Optimizing SQL Server Performance (10 percent)

Implement Resource Governor.

• Managing SQL Server Workloads with Resource Governor Entry page, includes How-To

Use Performance Studio.

• Data Collection Entry page, includes How-To
• Again, Performance Studio, also an MS-Name-Game, what you're really looking for is Data Collection... and trying to get that confirmed, I found this webcast by Bill Ramos (62 minutes).

The rest, well it is all too familiar from SQL Server 2005. Sure, I'll look for some "What's new" resources, but I think the above pretty much covers what I need to familiarize my self with.

Release Candidate 0 is available for download (and downloading) and the MCTS exam 70-432 went into beta testing (and I registered). Since the beta is only running from June 9th through June 30th, I had to go for 27th as it was the only gap in my schedule. Let's see if I can find the time to blog about my preparations...

Exam-stats: 180 minutes¹, 61 question spread over 6 testlets (cases), passing score 700 points, only multiple choice questions, no simulations. I got 752, was lousy on SSAS ...and this actually was the first Microsoft exam where I really needed the time!!!  Compared to the other MCITP (70-443, 70-444 and 71-647) exams I sat, it was a lot more reading and fact-finding in the case-studies.

What surprised me on this exam, were a couple of questions targeted at the database engine. Think towards backup-requirements for filegroups (which are needed for partitioned tables), index optimization and transaction isolation levels (not mentioned in prep-guide). Unfortunately these topics aren't covered² in the courses 2794 to 2797 (or in 2791 to 2793). From the topics that are covered in the prep-guide, I'd say the number of questions was pretty balanced, only four things were really sticking out:

• Which data mining algorithm to apply in a certain scenario.
• Storage strategy for SSAS cubes.
• Slowly changing dimensions.
• Designing dimensions and hierarchies.

Useful resources for preparation.

• First of all, don't count on the Training-Kit for 70-446 from MS Press, it was announced but won't be released (MCITP BI News; some news for MCTS BI and SQL Server 2008). But if you don't take the Microsoft courses, the MCTS Training Kit for 70-445 (ISBN 9780735623415) is a useful resource.
• Prep-guide (don't go there without one).
• Peter Koller's review of 70-446 beta (and 70-445 beta).
• Jorg Klein's passing of 70-446.
• Ronald Kaijesteijns "verslag" (that is Dutch, as well as the rest of his post).

Just a few links where you can find more info about SQL Server 2008 Certification in general and about the separate certification tracks and exams.

In other words, the system could not find the specified path... that was when I tried restoring through a substituted (subst R: G:\project02\SQLBackup) drive, and for sure R:\MyDB_20080521.bak did exist. Nastier was the fact that G:\project02\SQLBackup\MyDB_20080521.bak restored in an instant, so the problem was likely to be found in the interaction between subst and SQL Server. This was rather disappointing, as it would have been nice to separate the environment for different customers/projects and still be able to use generic scripts targeted at a drive-letter.

So a little quest started to find some solutions, if it's a bug?, should be a feature... I was surprised by the small number of pointers I came across on the Internet and only one useful. After establishing net use R: \\myserver\projdata\project02\SQLBackup suffered the same problem when it came to restoring, I widened the search an came across a post from the beta stage of SQL Server 2005. In there Erland Sommarskog hinted it might have something to do with profile setting, which would apply if the setting in question weren't a system setting. Now that made perfect sense, so let's see how the world looks like through xp_cmdshell.

So on my SQL Server 2005 instance I enabled xp_cmdshell and I substituted a path for a drive letter on Windows. Sure enough, I could see the substituted drive W: as pointing to D:\SQLData, but running EXECxp_cmdshell'subst' yielded NULL from the instance running under LocalSystem. Then, after running EXECxp_cmdshell'subst W: D:\SQLData' my SQL Server instance running under LocalSystem got the picture too. From SQL Server Management Studio I could "see" drive W: and also restore from and backup to my "W-drive". Safe to say it isn't a bug for SQL Server and if a feature request is to be made, it has to be addressed to the Windows team. Something like; enable system-wide setting of subst and net use commands by administrators through an extra switch (or make it a policy setting default on for administrators only).

In the end it can easily be solved with a little sqlcmd-script like:

:setvar path "G:\project02\SQLBackup"
:setvar drive "R"
EXEC xp_cmdshell 'subst $(drive): /d'
GO
EXEC xp_cmdshell 'subst $(drive): $(path)'
GO

From my perspective, a very successful event. Congratulations to the organizing committee and the volunteers supporting them. Also may thanks to the speakers, especially Bob Ward (pre-conference and session), Remus Rusana, James Luetkehoelter, Chuck Hawkins, Klaus Aschenbrenner (GEOGRAPHY data type).

Also look out for James' follow ups on "our" questions, guess the audience was paranoid enough.

Finally the results for the 71-646 and 71-647 are being published. I just checked the prometric site after reading Aaron's update on his scores, he passed his, I passed mine. Now all I need to do to haul in the MCITP: Enterprise Administrator is pass either the 70-620 or 70-624... I guess the 70-620 is by far the easier route.

Today I sat the 70-445 exam and completed those 53 questions with the minimal required passing score of 700 points. Now how did I get to that meager (but satisfactory) result?

First of all, I didn't allocate enough time for my preparation, so out of the initial things I mentioned, I only completed the MOC courses 2791 to 2794 (on a hindsight, the time spent with 2794 was wasted towards this exam). I also purchased the MS Press Training Kit for the 70-445 exam, but didn't really get to using it. In total, I only read the chapters 9 and 17 and ran through all 209 MeasureUP questions once. The reason for picking only chapters 9 and 17, besides time constraints, is the amount of Data Mining in the exam versus the (lack of) coverage of the subject in the MOC2791. The reason for running through the MeasureUP questions in study-mode was to get some exam-focus on the subjects and cover them all (see if there were things I missed from the MOC's). I should add that many of MeasureUP tests do not resemble the exam, like providing non-existent options or asking for trivial look-up fact... actual Microsoft exams have better quality.

Having that extra bit of exam-focus really helped, the MOC's tend to strongly focus on the development part of SSIS, SSRS and SSAS. The actual exam is more balanced between development and administration. The training kit too, seems to be more geared towards the administration part and definitely has a more task-based (or hands-on) approach than the MOC's. So in total, I think you need both for a proper preparation (or be able to compensate development or administration with in-depth practical skills).

Luckily the score report includes those scoring bars that indicate a little on your relative score. What is my experience based on the 7 topics tested;

• Managing SSAS
  • My relative score; 5th
  • Impression, 2791 definitely is shallow on this subject. And since I don't have to much real-world experience managing Analysis Services...
• Developing SSAS Solutions by Using BIDS
  • My relative score; 3rd
  • Impression, 2791 gives you all the handles you need, for the hands-on I can recommend taking a look at the tutorials in SQL Server Books On-Line. Again, no real-world experience on for me here either. Some applied MDX, nothing shocking.
• Implementing Data Mining by Using BIDS
  • My relative score; 6th
  • Impression, next to no coverage in 2791, you really need the 70-445 training kit here (mind, I just read it, didn't do the exercises). Very little basic DMX.
• Managing SSRS
  • My relative score; 2nd
  • Impression, some help from real-world experience, though I wasn't prepared for dealing with farms. Also, be prepared to modify the RSReportServer.config.
• Developing Reporting Solutions by Using SSRS
  • My relative score; 7th
  • Impression, 2793 gives you all the handles you need, but you should also to develop reports and care about how they look (questions included some beatifications of reports). Also, take a good look at URLs. If you only have this covered with the training kit, it seems to me (based on a quick glance), it's not going to be enough.
• Developing Business Intelligence Solutions by Using SSIS
  • My relative score; 4th
  • Impression, pretty well covered from the 2792... which was pretty helpful to me, as I'm used to solving a lot of stuff in the database (using views and stored procedures). Especially focus on transactions, checkpoints and logical combinations between expressions and constraints.
• Administering SSIS Packages
  • My relative score; 1st.
  • Impression, my real-world experience helped me out here, not the 2792. Focus on things like supplying configurations on run-time, securing parts of packages and the differences between storing in SQL Server vs. on the file system. Also be prepared for some dtutil and dtexec syntax.

As Ronald Kraijesteijn noted on his blog-entry (in Dutch) on the exam, it's pretty tool-oriented (like how you do something, even in which order). I felt this was particularly true on the developing with SSAS and SSRS. A couple of months experience are definitely going to prove advantageous. This was not the case for me, SSAS real-world is non-existent and SSRS already dates back a year. But hey, a 700-point pass still is a pass.

Fresh from the often delayed "SQL Server 2008 and Your Certifications", first session.

MCDBA will retire march 2009, no direct upgrade from MCDBA to a SQL Server 2008 certification.

70-446 will be superseded by 70-448 (~August 2008), 70-431 will be split in an Administration exam 70-432 (~August 2008) and 70-433 (~September 2008) for Development. So be ready for the following titles:

• 70-432, MCTS: SQL Server 2008, Implementation and Maintenance
• 70-433, MCTS: SQL Server 2008, Database Development
• 70-448, MCTS: SQL Server 2008, Business Intelligence Development and Maintenance

MCITP's can probably upgrade the MCTS and MCITP in a single upgrade exam. No timelines on the professional level exams yet.

MCA Databases is available, targeted at OLTP... Business Intelligence is under consideration.

Blogs to watch for more info on SQL Server 2008 Certifications:

• http://blogs.msdn.com/trika (here you can find the link to the recorded webcast in a day or two after this post, a written transcript will be provided later).
• http://blogs.msdn.com/gerryo