Martin Poon - Microsoft MVP blog [SQL Server] : SQL Server

SQL Server Configuration Manager – Cannot connect to WMI provider – Invalid class [0x80041010]

Martin Poon — Fri, 27 Nov 2009 15:05:22 GMT

When I am trying to connect to my SQL Server 2008 box using SQL Server Configuration Manager today, the following error dialog pops up…

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid class [0x80041010]

I have never seen this before!… while the following line saves me.

>> Open an elevated Command Prompt, and type the following…

mofcomp.exe "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

===== For more information =====

~ SQL Server 2005 WMI Provider Error

http://blogs.msdn.com/echarran/archive/2006/01/03/509061.aspx

~ April SQLExpress CTP Installation fails with error 29535 (WMI not configured properly)

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124587

SQL Server 2008 SP1 is released. Get your copy here!

Martin Poon — Thu, 09 Apr 2009 04:09:02 GMT

~ SQL Server 2008 SP1

http://www.microsoft.com/downloads/details.aspx?FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19&displaylang=en

~ SQL Server 2008 Express Edition SP1

http://www.microsoft.com/downloads/details.aspx?familyid=01AF61E6-2F63-4291-BCAD-FD500F6027FF&displaylang=en

SSMS - Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft .VisualStudio .OLE .Interop .IServiceProvider'. Exception from HRESULT: 0x80004002 (E_NOINTERFACE))

Martin Poon — Fri, 20 Mar 2009 13:48:38 GMT

I am working on Windows Server 2008 SP1 Datacenter Edition 64-bit, with SQL Server 2008 installed.

After installing Windows IE8, I got the following error upon starting SQL Server Management Studio (SSMS) 2008.

===== Error 0x80004002 =====

SSMS - Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.VisualStudio.OLE.Interop.IServiceProvider'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{6D5140C1-7436-11CE-8034-00AA006009FA}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)). (Microsoft.VisualStudio.OLE.Interop)

===== Solution =====

After a bit of investigation, I found that the registry is somehow corrupted, and the solution is to re-register DLL.

~ Run [ Command Prompt ] (You will need to use the **Run as administrator** option)

~ regsvr32 actxprxy.dll

===== For more information =====

~ SSMS Object Explorer/Server Explorer Error

http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/d5d3e5fc-d8ce-4f42-b7ea-9bbbb7756a20/

~ Windows Internet Explorer 8

http://www.microsoft.com/ie8

SSIS – Converting string / numeric (yyyymmdd and hhmmss) to datetime inside Derived Column Transformation Editor

Martin Poon — Mon, 16 Mar 2009 15:35:18 GMT

It takes me some time to figure out how a string / numeric value can be converted to a datetime value inside the Derived Column Transformation Editor of SSIS, and so, I would like to share my $0.20 here (in order to save others’ $0.20). :)

Here, the original TradeDate (e.g., 20090316) and TradeTime (e.g., 230533) are numeric values.

This one returns a datetime value with TradeDate only.

(DT_DBTIMESTAMP)(SUBSTRING((DT_WSTR,8)TradeDate,1,4) + "-" + SUBSTRING((DT_WSTR,8)TradeDate,5,2) + "-" + SUBSTRING((DT_WSTR,8)TradeDate,7,2))

And here’s how I obtain the datetime value including both TradeDate and TradeTime. I intentionally use the function DATEADD() to add hour, minute, and second to TradeTime.

DATEADD("ss", TradeTime - ROUND(TradeTime / 100, 0) * 100, DATEADD("mi", ROUND(TradeTime / 100, 0) - ROUND(TradeTime / 10000, 0) * 100, DATEADD("Hh", ROUND(TradeTime / 10000, 0), (DT_DBTIMESTAMP)(SUBSTRING((DT_WSTR,8)TradeDate,1,4) + "-" + SUBSTRING((DT_WSTR,8)TradeDate,5,2) + "-" + SUBSTRING((DT_WSTR,8)TradeDate,7,2)))))

You may also concatenate both TradeDate and TradeTime into a string that can be read by DT_DBTIMESTAMP.

===== For more information =====

~ SQL Server Product Samples: SSIS

http://www.codeplex.com/MSFTISProdSamples/Wiki/View.aspx?title=SS2008!Execute%20SQL%20Statements%20in%20a%20Loop%20Sample%20Package&referringTitle=Home

~ Converting Strings to Dates in the Derived Column Component

http://toddmcdermid.blogspot.com/2008/11/converting-strings-to-dates-in-derived.html

Microsoft Certification Exam – 10% discount voucher

Martin Poon — Wed, 18 Feb 2009 09:49:19 GMT

Great news – Microsoft is offering self-paced, eLearning collections to you when you sign up for the Certification Campaign using the code HKEBA99B. You will enjoy a eLearning collection at US$35 for a 90-days use. This is a great resource for those who prefers self-paced learning option. To sign-up, go to www.learnANDcertify.com by March 31, 2009 and key in HKEBA99B (this exam voucher code is valid in Hong Kong only) to obtain a 10% discount on your next Microsoft Certification Exam plus free retake if you fail plus the US$35 offer on eLearning. You will receive an email with the exam voucher code and a eLearning Promotion Code. Both offers will be valid till June 30, 2009. Happy Certifying…

Offer Details:

The certification promotion code ( HKEBA99B ) can only be used in Hong Kong.

The Offer is valid for a Microsoft Certified Technical Specialist (MCTS), Microsoft Certified IT Professional (MCITP) or Microsoft Certified Professional Developer (MCPD) exams. Not valid for Academic Exams.

Refer to the following links for applicable exams :
http://www.microsoft.com/learning/mcp/mcts/default.mspx
http://www.microsoft.com/learning/mcp/mcitp/default.mspx
http://www.microsoft.com/learning/mcp/mcpd/default.mspx

Exam candidates must request an Exam Offer Voucher Code using the unique MVP Certification Promotion Code by March 31, 2009 via www.learnandcertify.com.

To receive a 10% discount on the cost of an applicable Microsoft Certification exam, exam candidates must use the Exam Offer Voucher Code provided by an MVP when they register for an exam and sit for an exam by May 31, 2009.

Limited to one (1) exam at 10% discount with a free retake (if necessary) per individual. Both exams must be taken by May 31, 2009.

The Exam Offer Voucher Code may not be combined with other vouchers or discounts and are void if altered or revised in any way.

Refer to http://learnandcertify.com/Terms.aspx for complete Terms & Conditions of Exam Voucher usage.

Upgrade and migration to SQL Server 2008 webcast

Martin Poon — Fri, 08 Aug 2008 05:30:07 GMT

SQL Server 2008 is released, and you are invited to join this webcast that highlights the tools and procedures to make your move to SQL Server 2008 easy and troublefree.

*** For more information

~ Upgrade and migration to SQL Server 2008 webcast

http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032384669&EventCategory=3&culture=en-US&CountryCode=US

~ SQL Server 2008 is available for download for MSDN subscribers

https://msdn.microsoft.com/en-us/subscriptions

Microsoft SQL Server 2008 { JumpStart }... your data, any time, any place

Martin Poon — Thu, 08 May 2008 07:58:00 GMT

Microsoft SQL Server 2008 { JumpStart } @ http://sqlserver2008jumpstart.microsofttraining.com

This site contains presentations, recordings, hands-on labs and demonstrations for the SQL Server 2008 JumpStart technical training event, while there are 5 tracks in this event: Overview Sessions, Database Infrastructure and Scalability, Business Intelligence Part I & II, Developer & Software + Services, and Application Compatibility & Upgrade. -->Overview Sessions, Database Infrastructure and Scalability, Business Intelligence Part I & II, Developer & Software + Services, and Application Compatibility & Upgrade

Writing the first SQL Server database .NET application

Martin Poon — Sun, 23 Mar 2008 16:15:00 GMT

Just listing a few getting-started links for developing SQL Server .NET application.

~ SQL Server 2005 Express Edition - Sample Rich Client and Web Applications
http://msdn2.microsoft.com/en-us/express/bb403187.aspx

~ SQL Server 2005 Express Edition - Samples and Starter Kits
http://msdn2.microsoft.com/en-us/express/aa718396.aspx

~ "How Do I" Videos - ASP.NET
http://msdn2.microsoft.com/en-us/asp.net/bb498194.aspx

~ "How Do I" Videos - Visual Basic
http://msdn2.microsoft.com/en-us/vbasic/bb466226.aspx

~ Hitchhiker's Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples (7th Edition)
http://www.amazon.com/Hitchhikers-Guide-Visual-Studio-Server/dp/0321243625
http://www.betav.com

~ LearnVisualStudio.NET
http://www.learnvisualstudio.net

~ Free basic training for SQL Server
http://blogs.msdn.com/buckwoody/archive/2008/07/17/free-basic-training-for-sql-server.aspx

Installing Components... Full Installation of Visual Studio 2008 Professional Edition

Martin Poon — Sun, 09 Mar 2008 06:20:00 GMT

Just got the *full* installation of Visual Studio 2008 Professional Edition done on an Vista Ultimate box (x64), and, here is the ...long... list of components that are installed!

- Runtime Pre-requisites
- Microsoft .NET Framework v3.5 (x64)
- Microsoft Visual Studio 2008 64bit Prerequisites (x64)
- Microsoft Document Explorer 2008
- Microsoft Visual Studio Web Authoring Component
- Microsoft Visual Studio 2008
- Microsoft .NET Compact Framework 2.0 SP2
- Microsoft .NET Compact Framework 3.5
- Microsoft Visual Studio Tools for the Microsoft Office system (version 3.0 Runtime)
- Microsoft Visual Studio 2005 Tools for the 2007 Microsoft Office System Runtime
- Microsoft SQL Server Compact 3.5
- Microsoft SQL Server Compact 3.5 Design Tools
- Microsoft SQL Server Compact 3.5 For Devices
- Windows Mobile 5.0 SDK R2 for Pocket PC
- Windows Mobile 5.0 SDK R2 for Smartphone
- Microsoft Device Emulator version 3.0 (x64)
- Microsoft SQL Server 2005 Express Edition
- Microsoft Visual Studio 2008 Remote Debugger (x64)
- Crystal Reports Basic for Visual Studio 2008
- Crystal Reports Basic 64-Bit Runtime for Visual Studio 2008
- Microsoft Windows SDK for Visual Studio 2008 Tools (x64)
- Microsoft Windows SDK for Visual Studio 2008 Headers and Libraries (x64)
- Microsoft Windows SDK for Visual Studio 2008 Win32 Tools (x64)
- Microsoft Windows SDK for Visual Studio 2008 .NET Framework Tools (x64)
- Microsoft Windows SDK for Visual Studio 2008 SDK Reference Assemblies and IntelliSense (x64)
- Microsoft SQL Publishing Wizard

SQL Server 2008 CTP 6 (February 2008) is available for { evaluation }

Martin Poon — Sun, 24 Feb 2008 14:51:00 GMT

This build should be { feature complete }, and is available in the following languages.

>> Chinese (Simplified), Chinese (Traditional), English, French, German, Italian, Japanese, Korean, Russian, and Spanish

=== For more information ===

~ Evaluate Microsoft SQL Server 2008 today

http://www.microsoft.com/downloads/details.aspx?FamilyId=749BD760-F404-4D45-9AC0-D7F1B3ED1053&displaylang=en

.NET Framework 3.5 full package

Martin Poon — Sat, 26 Jan 2008 02:06:00 GMT

Have just got the link to download the full package of .NET Framework 3.5. This is a 197 MB standalone executable file that contains all the components that are required to deploy to 32-bit and 64-bit platforms on all supporting operating systems. (The .NET Framework 3.5 supports IA64 only on Microsoft Windows Server 2008.) This executable does not contain language packs. The .NET Framework version 3.5 language packs are available as separate per-language stand-alone executable files.

.NET Framework 3.5 builds incrementally on the new features added in .NET Framework 3.0. For example, feature sets in Windows Workflow Foundation (WF), Windows Communication Foundation (WCF), Windows Presentation Foundation (WPF) and Windows CardSpace. In addition, .NET Framework 3.5 contains a number of new features in several technology areas which have been added as new assemblies to avoid breaking changes.
They include the following:

~ Deep integration of Language Integrated Query (LINQ) and data awareness. This new feature will let you write code written in LINQ-enabled languages to filter, enumerate, and create projections of several types of SQL data, collections, XML, and DataSets by using the same syntax.

~ ASP.NET AJAX lets you create more efficient, more interactive, and highly-personalized Web experiences that work across all the most popular browsers.

~ New Web protocol support for building WCF services including AJAX, JSON, REST, POX, RSS, ATOM, and several new WS-* standards.

~ Full tooling support in Visual Studio 2008 for WF, WCF, and WPF, including the new workflow-enabled services technology.

~ New classes in .NET Framework 3.5 base class library (BCL) that address many common customer requests.

===== For more information =====

~ .NET Framework 3.5 full package
http://download.microsoft.com/download/6/0/f/60fc5854-3cb8-4892-b6db-bd4f42510f28/dotnetfx35.exe

~ Microsoft .NET Framework 3.5 readme
http://download.microsoft.com/download/9/a/e/9ae0f6cc-7032-408e-9ca7-989f9e4af4ec/dotNetReadMe.htm

===== Other posts that points here =====

~ Microsoft .NET Framework 3.0: Pacote de Idiomas x86 (KB928416) (Resolvido)

http://www.guiadohardware.net/comunidade/microsoft-net/907431/

Free SQL Server 2008 test drive program

Martin Poon — Fri, 25 Jan 2008 03:34:00 GMT

SQLServerBeta is offering a free SQL Server 2008 online testing account for the community.

Sign up at http://www.sqlserverbeta.com , and get the chance to experience the latest SQL Server 2008 technologies online, using your own set of data.

SQL Server Management Studio - standard reports in the Summary Page

Martin Poon — Thu, 01 Nov 2007 04:37:00 GMT

In SQL Server Management Studio, the Summary Page can be displayed at any time by pressing F7 (or, to be chosen from the menu --> View --> Summary). When you select an item in the Object Explorer of SQL Server Management Studio, information about that object is presented in the document window called the Summary Page.

You can opt to display the Summary Page when starting SQL Server Management Studio.

--> Tools --> Options --> Environment --> General --> At startup: Open Object Explorer

Note that the Summary Page will be displayed when the option 'Open Object Explorer' is being chosen. Other options will *not* bring up the Summary Page at startup (which include 'Open new query window', 'Open Object Explorer and new query', 'Open empty environment').

Following is the list of reports that can be found within the Summary Page when you choose a *SQL Server* in the Object Explorer. This list will be changed when you choose different kinds of database objects.

~ server dashboard
~ configuration changes history
~ schema changes history
~ scheduler health
~ memory consumption
~ activity - all blocking transactions
~ activity - all cursors
~ activity - top cursors
~ activity - all sessions
~ activity - top sessions
~ activity - dormant sessions
~ activity - top connections
~ top transactions by age
~ top transactions by blocked transactions count
~ top transactions by locks count
~ performance - batch execution statistics
~ performance - object execution statistics
~ performance - top queries by average CPU time
~ performance - top queries by average IO
~ performance - top queries by total CPU time
~ performance - top queries by total IO
~ service broker statistics
~ transaction log shipping status

Recently, Buck Woody blogged the standard reports that can be found under the Summary Page. More information can be found here.

http://blogs.msdn.com/buckwoody/archive/tags/Standard+Reports/default.aspx

TechNet webcast: Advanced Manageability for SQL Server 2008 Analysis Services on 2007.10.10

Martin Poon — Mon, 08 Oct 2007 03:03:00 GMT

With the release of SQL Server 2008 Analysis Services, Microsoft introduces several advanced features for manageability and one such feature is Dynamic Management Views (DMV). These views enable administrators to query for information regarding connections, sessions, and server performance. This session focuses on such views and will drill down into many examples of their usage. This session will also help you learen on how to build effective queries, reports and even cubes that you can use to understand the behavior and monitor the performance of your SQL Server 2008 Analysis Services.
Presenter: Donald Farmer (Group Program Manager, Microsoft Corporation)

===== For more information =====
~ Advanced Manageability for SQL Server 2008 Analysis Services (recorded session)
http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032352699&EventCategory=4&culture=en-US&CountryCode=US

Microsoft Query - ODBC data source - Error 2571 - User 'xxxx' does not have permission to run DBCC TRACEON

Martin Poon — Fri, 06 Jul 2007 08:39:00 GMT

Upon querying SQL Server 2005 using an ODBC data source in Microsoft Excel 2000...

You may encounter the following error.

Microsoft SQL Server Login
Connection failed:
SQLState: '37000'
SQL Server Error: 2571
[Microsoft][ODBC SQL Server Driver][SQL Server]User 'xxxx' does not have permission to run DBCC TRACEON.

===== Solution =====

To resolve this problem, you will need to change the *application name* for the ODBC connection, such that it is not 'Microsoft(R) Query'!

SQL Server 2005 Books Online (May 2007)

Martin Poon — Tue, 05 Jun 2007 02:17:00 GMT

The latest version of SQL Server 2005 Books Online (May 2007) is now available at http://www.microsoft.com/downloads/details.aspx?familyid=be6a2c5d-00df-4220-b133-29c1e0b6585f&displaylang=en

SQL Server 2008 Community Technology Preview (CTP)

Martin Poon — Mon, 04 Jun 2007 15:27:00 GMT

SQL Server 2008 CTP is now available at Microsoft Connect.

===== For more information =====

~ Connect: SQL Server 2008 CTP

https://connect.microsoft.com/sqlserver

[Hong Kong] Microsoft Business Intelligence Conference on 2007.06.20 (Wed)

Martin Poon — Thu, 31 May 2007 06:56:00 GMT

The first-ever Microsoft BI Conference is going to be held in Hong Kong this month, after the successful launch of the same conference in Seattle, Washington! You will be able to learn more about powerful BI insights using SQL Server 2005 SP2, PerformancePoint Server, and 2007 Microsoft Office System.

Date: 20 June, 2007 (Wednesday)

Time: 10:00am - 6:00pm

Venue: Hong Kong Convention & Exhibition Center

===== For more information =====

~ Microsoft Business Intelligence Conference 2007

http://www.microsoft.com/hk/biconference

~ Microsoft BI and SQL Server 2005

http://www.microsoft.com/bi/products/sqlserver2005-overview.aspx

~ Microsoft Office 2007 System and Business Intelligence

http://www.microsoft.com/bi/products/office2007-overview.aspx

~ Microsoft BI and PerformancePoint Server

http://www.microsoft.com/bi/products/performancepoint-overview.aspx

Remote Keys release candidate (RC1) is now available

Martin Poon — Tue, 22 May 2007 13:54:00 GMT

Remote Keys release candidate (RC1) is now available. Remote Keys allows you to define and enforce cross databases integrity constraint. With Remote Keys, you can create a foreign key between two tables located in distinct SQL Server databases.

===== For more information =====

~ Remote Keys

http://www.remote-keys.com/Product.aspx

~ Remote Keys FAQ

http://www.remote-keys.com/faq.aspx

~ Remote Keys quick start

http://www.remote-keys.com/QuickStart.aspx