Reporting Services Querying with WQL
Hi Everyone,
From time to time we need to "ask" our Operation system questions such as:
1. How are you today?
2. How was your night?
3. Do you need anything?
4. Did somebody do something to you? Who?
Well, the best way to do it is querying the system with WMI queries by WQL language.
Few days ago my customer needed to decide what the best way is.
So, how we can do it? , the following solutions are some examples of ways to do it
The following bit of code uses a common table expression (CTE) to generate a contiguous list of dates in SQL Server.
with mycte as
(
select cast('20080101' as datetime) DateValue
union all
select DateValue + 1
from mycte
where DateValue + 1 < '20080731'
)
select convert(char(8),DateValue,112) DateValue
from mycte
OPTION (MAXRECURSION 0)
SQL 2008 breaking “feature”
I was looking at the ROWCOUNT documentation and BOL states:
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT together with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax.
Something to keep in mind if your scripts will be run against sql 2008
How to open a website in Visual Studio 2005 from the command line
I’ve seen several queries asking how to launch Visual Studio 2005 opening a specific folder as a website from the command line. Unfortunately Visual Studio 2005 does not support this by default but you can enable the scenario by writing a macro.
To do this, create a new macro either in an existing module or in a new module. For this example I’m going to add a module call Website to the MyMacros project.
If you’re new to macros in Visual Studio 2005, select menu Tools/Macros/Macros IDE, then select the MyMacros project and add a module.
Before you can use the new Website extensibility objects within VS you’ll need to add a reference to VsWebSite.Interop.dll.
Once added you can add the following code:
Public Module Website
Sub OpenWebsite(Optional ByVal path As String = "")
If (String.Compare(path, String.Empty) = 0) Then
MsgBox("Must supply a folder path to the OpenWebsite macro", MsgBoxStyle.OkOnly)
Else
Dim webPkg As VsWebSite.VSWebPackage
webPkg = DTE.GetObject("WebPackage")
webPkg.OpenWebSite(path, VsWebSite.OpenWebsiteOptions.OpenWebsiteOption_None, False)
End If
End Sub
End Module
After this code is added you’ll be able to run the macro. You can test it out in the command window.
From the Command Window in VS:
>Macros.MyMacros.Website.OpenWebsite C:\MyProjects\MyCompany\CompanySite
From the Command Line:
devenv /command "Macros.MyMacros.Website.OpenWebsite C:\MyProjects\MyCompany\CompanySite"
After its working you can register a shell command enabling an “Open as Visual Studio Website” command on any folder in windows explorer.
To do this copy the following into a OpenWebsite.reg file and run it.
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Folder\shell\OpenVSWeb]
@="Open as Visual Studio Website"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Folder\shell\OpenVSWeb\command]
@="devenv.exe /command \\\"Macros.MyMacros.Website.OpenWebsite %1\\\""
Now you should be able to right click on a folder in Windows Explorer and select “Open as Website”.
How to open a website in Visual Studio 2005 from the command line - BradleyB's WebLog
SELECT 'http://localhost/reportserver?'+cast(c.path as nvarchar(max))+'&'+cast([Parameters] as nvarchar(max))
FROM [ReportServer].[dbo].[ExecutionLog] e
INNER JOIN catalog c on c.itemid = e.reportid
WHERE ….
This lets you query the execution log and generate a list of URLs to run reports in Reporting Services.
NHibernate querying is great – it gives us the ability to query objects directly without the need to know anything about the underlying relational schema. It has a couple of major flaws though: 1. property names are accessed via strings, so we don’t know until runtime whether they exist, or are mapped 2. it’s ugly and cumbersome – frankly probably one of the major barriers I’ve had to ORM until now.
Ayende’s NHibernate Query Generator fixes both of these things. Mapped properties are accessed via properties as nature intended, and querying is a lot easier to read and write. Not quite as neat as Linq-to-NHibernate will be, but so much better than native NHibernate querying that I wonder why it wasn’t included in the trunk.
Waiting on 2010…
Timeframes: the next major release of SQL will be 24-36 months from release of SQL 2008, but in the meantime, there are a number of releases coming: Madison and Gemini will be coming in the first half of 2010, and CTP's will be available sometime early next year. There are some incremental releases of Analysis Services, Integration Services and Reporting Services coming - the next gen of Reporting Services in particular will become available in a Feature Pack "real soon now".
It took me a while to figure a good way to insert Blob information into a Microsoft Sql database. One of the things you need to do is to insert an empty Blob, and I wasn't finding that on the internet. In Oracle you will use an empty_blob() function. So, I managed to get my code to work and decided to post it here so people can find an easy example. If you need more information, let me know.
The Programmer's Weblog: Inserting Blob into Microsoft Sql Database
Cube-like functionality in SQL 2008.
Today I took a look at the usefulness of the new GROUPING SETS clause in SQL Server 2008. The results of the GROUPING SETS clause can be achived in other ways, but it takes more programming work to do so. Next time I will take a look at SQL Server 2008 enhancements to the CUBE and ROLLUP; two constructs that can produce similar results to the GROUPING SETS clause, but in a different manner.
Using Grouping Sets in SQL Server 2008 | Servers and Storage | TechRepublic.com
Some important fixes here.
FIX: When you query through a view that uses the ORDER BY clause in SQL Server 2008, the result is still returned in random order
FIX: The result for the Sum or Count function returns an empty value when you query a SQL Server 2008 Analysis Services cube
FIX: In SQL Server 2008 or in SQL Server 2005, the session that runs the TRUNCATE TABLE statement may stop responding, and you cannot end the session
FIX: The Processes pane in the Activity Monitor incorrectly shows sessions as head blockers in SQL Server 2008
Plus plenty of Reporting Services updates.
The scripting guy answers this question.
How Can I Write to Excel Without Using Excel?
Hey, Scripting Guy! I need to be able to write information to an Excel file. I do not want to install Microsoft Office Excel on my server, but the spreadsheet format is a nice way to store information. I can then give it to my pointy-headed boss, and he can create pivot tables, charts, or whatever exactly it is he does all day. Basically, I think if I give him lots of data, he will leave me alone and let me do my job. Can you help?
Hey Scripting Guy! How Can I Write to Excel Without Using Excel?
Hey, Scripting Guy! I have an Microsoft Office Excel spreadsheet with server names in it, and I would like to be able to use this in a Windows PowerShell script. The thing is, I do not have Microsoft Office or Excel installed on the server. How can I use this spread sheet without installing Office on my server (which I understand is unsupported anyway)?
http://www.microsoft.com/technet/scriptcenter/resources/qanda/sept08/hey0911.mspx
Microsoft Announces SQL Server 2008 Experience
On Monday, Sept. 29, Microsoft is launching the SQL Server 2008 Experience online at http://www.SQLServerExperience.com. With more than 500 short videos in 11 different languages, the SQL Server 2008 Experience is a Web site that helps Microsoft’s global customers and partners learn more about SQL Server 2008, Microsoft’s recently released data management and business intelligence platform. SQL Server 2008 provides a trusted, productive and intelligent data platform for business-critical applications. The launch of the SQL Server 2008 Experience kicks off a worldwide readiness outreach that will reach over 350,000 customers, partners and community members through in-person events over the next year.
Visitors to the SQL Server 2008 Experience can expect the following:
• Brief interviews with the SQL Server Engineering team, providing a behind-the-scenes view of the many technologies in SQL Server 2008
• Guidance on a wide range of topics such as business intelligence, compliance, upgrades and application development, including videos from customers showing how SQL Server 2008 is helping them be more successful. Customers that will be featured include Xerox Corp., Baltika Breweries and DriveCam Inc.
• Great deals on SQL Server 2008 books, courses and exams offered by Microsoft Learning
When: Sept. 29, 2008, at 8 a.m. (US Pacific Time)
Where: Online at http://www.SQLServerExperience.com
Nick MacKechnie : Microsoft Announces SQL Server 2008 Experience
Time Series Reporting Stored Procedure - part 3 of 3
This post continues part 2 of the series. In this part of the series I will demonstrate how to create a report using the stored procedure and also how to call the stored procedure from Excel using the Data Mining Addins for Office 2007.
In creating a report, the first thing I did was to use BI Dev Studio to create a new Report Server Wizard Project. I then, of course, gave the wizard a connection string to my Analysis Services database that contains my Time Series model.
Jamie's Junk : Time Series Reporting Stored Procedure - part 3 of 3
Web Development Helper
Web Development Helper plugs into Internet Explorer and provides tools for Ajax and Web development.
How to fix your SQL Server 2008 Setup before you run setup...
Huh? This is not meant to be a brain teaser. The purpose of this post is to help you avoid problems with SQL Server 2008 setup before you run..well setup. Why not take advantage of known fixes we have for setup to avoid the problem in the first place? This would include a new installation, upgrade, or a install of a new instance or feature.
In this blog post, I'll give you details on how this works and point you to resources for known setup fixes that might affect you.
PSS SQL Server Engineers : How to fix your SQL Server 2008 Setup before you run setup...
In the last few sessions of XML Workshop we had been looking at ways of generating RSS/ATOM Feeds. You can find the previous sessions here. We have seen how to generate RSS and ATOM feeds in SQL Server 2005 as well as 2000. In the previous sessions, we have seen how to generate RSS and ATOM feeds using FOR XML PATH as well as FOR XML EXPLICIT. If you are working with SQL Server 2005 (and above), you can take advantage of FOR XML PATH and if you are still in SQL Server 2000, you can use FOR XML EXPLICIT.
..
So the focus of this sessions will be writing a function that accepts two XML parameters containing channel and item information and generates an RSS 2.0 feed. We will be able to call the function as in the given example.
Intelligencia Query delivers add-in functionality for Visual Studio that transforms the task of creating OLAP reports with Microsoft SQL Server Reporting Services. State-of-the-art query tools are combined with a custom data extension that understands how OLAP reporting should work. With Intelligencia Query you get:
- Queries are built the way that your report is to be displayed. There are no restrictions on where each dimension can be displayed or how many dimensions can appear on an axis.
- Only the data required to fulfil your report is returned making IQ faster and more efficient than the default Reporting Services data extension.
- Performance is further enhanced by providing techniques for building drilldown reports that only return drilled data as it is required.
- The flexibility of the query builder dramtically reduces the need to "hand-crank" Mdx leading to better maintainability and reduced skill requirements.
- Table and Matrix report items are supported allowing all types of reports to be created without having to resort to SQL queries via the Analysis Services ODBO interface.
- Intelligencia Query support SAP BW and Congons TM1 in addition to all versions of SQL Server Analysis Services.
This post explains how you can build a dynamic MDX query in Reportins Services, customised to the users requirements. This can often bring some quite major performance benefits.
Using PBM Against SQL2K and SQL2K5
We get this question a lot: can I use PBM against a SQL2K & SQL2K5 instances. The quick answer is yes but in a limited fashion. PBM is ultimately based on SMO (SQL Server Management Objects) and SMO supports SQL2K, SQL2K5, and SQL2K8. PBM relies on some changes to the DB engine which are not available in versions below SQL2K8, therefore, not all PBM functionality is available in SQL2K and SQL2K5.
Analyzing Perfmon data with the SQL 2008 data mining tools.
In our particular example, we can see that Category 8 is the highest TempDB load. Innnnteresting - earlier, I’d discarded categories over 5 because they represented a low amount of time, but it looks like we’ll have to go back and revisit category 8. Category 8 was Friday mornings from midnight to 4am, so we might have something going on then.
Using FILESTREAM to Store BLOBs in the NTFS File System in SQL Server 2008
Using FILESTREAM to Store BLOBs in the NTFS File System in SQL Server 2008
Here's a recap of what's new for Data Mining in SQL Server 2008:
The Microsoft_Time_Series algorithm has been enhanced to include ARIMA in addition to the existing ARTxp method, and a blending algorithm is now used to deliver more accurate and stable predictions, both short and long term, from a hybrid model. In addition, a new prediction mode allows you to add new data to time series models. (See below for a neat app that will let you explore these features.)
Built-in support for holdout has been added. You can easily partition your data into training and test sets that are stored in the mining structure and are available to query after processing.
You can now build mining models on filtered subsets of a mining structure's data (e.g. just male customers), which means that you no longer have to create multiple mining structures and re-read the source data for such variations over a dataset.
Drillthrough functionality has been extended to make all mining structure columns available, not just columns included in the model. This allows you to build more compact models without sacrificing the ability to producing actionable output reports like targeted mailing lists.
The much-requested cross-validation feature has been added, allowing users to quickly validate their modeling approach by automatically building temporary models and evaluating accuracy measures across K folds. The feature is available through a new cross-validation tab under Accuracy Charts in Business Intelligence Development Studio, in addition to being accessible programmatically via a stored procedure call.
Project Description
Business Intelligence LAB is a Microsoft Business Intelligence Framework that is developped based on real life needs.
This repository hosts a set of development done upon SQL Server Business Intelligence Stack.
This covers :
- Data Access
- Sample Cube
- Sample OLAP Viewer
- XMLA
- SSIS custom component
Those component comes from my experience and my needs. Some were published on French .net community (http://www.techheadbrothers.com/) under :
http://www.techheadbrothers.com/Auteurs.aspx/renaud-harduinLast edited May 18 at 11:21 AM by rhmBILAB, version 2
Why doesn’t the US use the metric system? That year, Congress went on summer vacation before the bill was supposed to be passed….
Measure conversion has caused numerous (and disastrous problems). Think of litres to gallons conversion in an airplane, and really quickly you see how someone could run out of gas if they aren’t using the correct units of measure.
MS is trying to fix this with typed unit recognition in programming languages.
As recently announced in the September 2008 F# CTP (Community Technical Preview), the F# programming language now has full support for static checking and inference of units-of-measure. In this series of articles I'll gently introduce the feature. (If you're not familiar with F#, look here.) We've already been testing out the units-of-measure feature inside Microsoft and I'm amazed at the diversity of applications that are turning up. Of course, there are the obvious applications to scientific computing, and games (which are all about physics, after all), but we're seeing applications in machine learning, finance, search (think click rates, etc) and others.
Andrew Kennedy's Blog : Units of Measure in F#: Part One, Introducing Units
Mine your Data, Any Place, Any Time
The SQL Server Data Mining team is working to extend the power and ease of use of SQL Server Data Mining to the Cloud. Our goal is provide services that allow you to build rich, predictive applications without worrying about server infrastructure, and showcase these services with cool applications that give you a glimpse of what’s possible. We bring you a technology preview of our work below. Enjoy!
Current Projects
Table Analysis Tools for the Cloud
Build powerful predictive reports on your data with just a few clicks!
- No data mining expertise required
- No server installation required
- All you need is your Internet connection
The answer should be no in my opinion… for large amounts of data Excel is not the right tool by itself, and usually BI is all about analyzing large amounts of data.
However, the comfort of using Excel to analyze numbers isn’t something that’s going away any time soon.
What makes it a BI tool?
• Excel can be (and is often) used for operational reporting, by connecting to databases, and also for analysis and dashboards using pivot tables and charts (plus the power of VBA — Visual Basic for Applications).
• Excel does not necessarily have to be embedded in other applications.
• Excel certainly has sufficient market presence, well exceeding the stipulation above.
• Excel comfortably crosses $40 million in revenues, even when constrained for reporting and analysis usage. (Microsoft Office accounts for some $10 billion annual revenue, give or take a couple.)
Is Excel a Complete BI Solution? | The Intelligent Enterprise Blog
Searching Google for Sheet1 filetype:xls shows us there is a long way to go before getting rid of Excel.
Results 1 - 10 of about 2,000,000 for sheet1 filetype:xls. (0.28 seconds)
SSAS Stored Procedures - connecting to the transactional data source
There are often circumstances where an SSAS stored procedure needs to connect to the transactional database to perform a query. This is certainly the case in a near-real-time OLAP solution where the cube sits directly on top of the application database and dynamic dimension security is implemented by a stored proc. Here the SSAS stored procedure has to query the transactional database in order to get the user's authorisation. Clearly the SSAS database has a data source and it uses this connection to process the cube. So how do we get the connection string?
Well the short code snippet below provides the solution. The code simply connects back to the current database using AMO and obtains the connection string from the data source object. It then removes the redundant "Provider=SQLNCLI.1;" before returning a valid SQL connection string that can be used by SqlConnection().
The Business Intelligence Update - September 2008
HOT TOPICS
Register NOW for the 2nd Annual Microsoft BI Conference - click here!
25% Discount Promotion - Microsoft Office SharePoint ECAL / PerformancePoint Server CAL
*** extended through September 30, 2008
Assessment Tool for Business Intelligence
- compare your infrastructure against industry best practices and the latest IT research
Online Demo: Geospatial Business Intelligence
- by integrating Virtual Earth and PerformancePoint
- Blog: More on Virtual Earth & PPS
Business intelligence basics: case studies and job advice
Five keys to hiring (good) business intelligence professionals
Leveraging business and marketing skills for a business intelligence (BI) career
UPCOMING EVENTS
How does it all stack up?
6 part Seminar Series, sponsored by Inetium & Microsoft
Sept 18
Omaha, NE
Click here to register
Dashboard in a Day - sponsored by Quilogy
Sept 16 - Omaha, NE
Sept 18 - Chicago, IL
Click here to learn more and register
The Next Generation of Business Intelligence
Executive Roundtable Breakfast Series, sponsored by Neudesic & Microsoft
Sept 18 - Denver, CO
Sept 30 - Las Vegas, NV
Microsoft BI Conference
Oct 6-8 - Seattle, WA
Managing Performance with PerformancePoint
Planning, Financial Reporting, Monitoring and Analytics for Dynamics GP 10
Oct 8 - Mountain View, CA
Click here to register
PASS Community Summit 2008
Nov 18-21 - Seattle, WA
Click here to learn more
UPCOMING WEBCASTS
* all times listed below are for Pacific time zone
Microsoft Business Intelligence for Retail - sponsored by Solver
Sept 11, 11am
Click here to register
Using Your SharePoint Portal to Communicate Business Intelligence presented by Quilogy
Sept 11, 1 pm
Click here to register
Achieving Business Intelligence Across the Enterprise presented by Thorogood
Sept 18, 9am - Realize Fast & Effective Planning, Budgeting & Forecasting
Click here to register
NEWS LINKS & PRESS RELEASES
SQL Server 2008: First Impressions
BIScorecard Rates Eight Leading Products
An Effective Business Intelligence Strategy is the Key to Business Success
Next-generation BI at Hand, Forrester Reports
Gartner lists Microsoft India as leading vendor of BI
The Business Intelligence Model of Conflicting Interests
Is Excel a Complete BI Solution?
Pervasive business intelligence: Are organizations really ready?
Seven Steps to Successful BI Competency Centers
Business Intelligence: Distilling Raw Data into Useful Information
The Business Intelligence Education Problem
Full Circle: Decision Intelligence (DSS 2.0)
Contact Me if you'd like to receive monthly emails of Jason Morales' Microsoft BI Update
Jason Morales' Microsoft BI Update : The Business Intelligence Update - September 2008
Remove Extra Spaces when Pasting from Outlook to Sql Server Management Studio
I seem to have this problem that when I paste sql code from my email into Sql Server Management Studio that it doubles all the line breaks and I have to go and remove them all. Most of the time this isn't a big deal because the Sql is only a couple lines. However if you get a ton of Sql emailed to you it can be quite annoying. I finally found a way to quickly get rid of them. Simply bring up the find and replace window and change the find to use Regular Expressions. Then do a find on \n\n and replace that with \n.
Project Description
SQL 2008 Extended Events are a powerful new way of troubleshooting problems with SQL Server. One short coming is that there is no UI support for this new feature. The Extended Events Manager is a C# .NET winforms application that aims to simplify creating and viewing metadata for Extended Events Sessions in SQL 2008.
This project was initially published on the MSDN Code Gallery:
http://code.msdn.microsoft.com/ExtendedEventManager/
The recently released Beta 2 of Internet Explorer 8 contains a lot of improvements which are aimed at making developing web applications on Internet Explorer 8 easier and more productive. One of these improvements is the JScript Profiler in Developer Tools, which provides critical JScript related performance data to a web developer that helps identify and fix performance related issues. We believe the Profiler is going to be a very helpful tool to fine tune the performance of the scripts in a web application. It is lightweight, easy-to-use and provides the following features:
- Provides performance data for JScript functions in two views:
- Functions View – a flat listing of all the functions
- Call Tree view – a hierarchical listing of the functions based on the call flow
- Supports exporting the data to a file
- Provides an inferred name for anonymous functions
- Profiles built-in JScript functions
- Supports multiple profile reports
- Supports profiling across page navigation and refreshes
This post gives an overview of the Profiler and highlights some of its features. We hope you will try it out and give us your feedback.
New Zealand IE8 Taskforce : Introducing the IE8 Developer Tools JScript Profiler
Who says you can’t write C# Code in Excel?
Probably not what you were expecting…
I wrote a quick console application while testing Excel Services which uses the code provided by Robbe Morris to compile a .NET application on the fly, using source code stored as text format in an Excel spreadsheet and published to Excel Services.
So now you can write C# code in Excel and compile without Visual Studio, and host in a central location.
Please comment if you can think of a good (or bad) application for this. I can think of a few.
Or maybe just something to add to the list of stupid Excel tricks.
Here is the code to go with this solution. (Limited to under 50 lines stored in the text).
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Web.Services.Protocols;
using System.Security.Principal;
using SourceCodeManager;
using System.Reflection;
using System.Diagnostics;
namespace HelloWorldConsoleApp
{
class Program
{
private const string Workbook = @"http://bi-vpc/Reports/ReportsLibrary/CodeSnippet.xlsx";
static void Main(string[] args)
{
es.ExcelService s = new es.ExcelService();
s.SoapVersion = SoapProtocolVersion.Soap12;
string sessionId = null;
try
{
s.Credentials = System.Net.CredentialCache.DefaultCredentials;
es.Status[] status;
Console.WriteLine("Opening");
sessionId = s.OpenWorkbook(Workbook, String.Empty, String.Empty, out status);
Console.WriteLine("Session ID: {0}", sessionId);
Debug.WriteLine(WindowsIdentity.GetCurrent().Name.ToString());
Debug.WriteLine(Workbook);
Debug.WriteLine(sessionId);
object cellVal;
StringBuilder sb = new StringBuilder();
for (int i = 1; i < 50; i++)
{
cellVal = s.GetCellA1(sessionId, "Sheet1", "A" + i.ToString(), true, out status);
Debug.WriteLine(cellVal);
sb.Append(cellVal);
}
Debug.Write(sb.ToString());
Console.Write(sb.ToString());
string returnMsg = CompileCode(sb.ToString()); // The magic happens here
Debug.WriteLine(returnMsg);
Console.WriteLine(returnMsg);
}
catch (SoapException ex)
{
Debug.WriteLine("Error: {0} ", ex.SubCode.Code.Name);
Console.WriteLine("Error: {0} ", ex.SubCode.Code.Name);
}
catch (Exception ex)
{
Debug.WriteLine("Unknown Error: " + ex.ToString());
Console.WriteLine("Unknown Error: {0}", ex);
}
finally
{
if (!String.IsNullOrEmpty(sessionId))
s.CloseWorkbook(sessionId);
s = null;
}
}
protected static string CompileCode(string sourceText)
{
SourceCodeManager.Runtime runtime = new SourceCodeManager.Runtime();
SourceCodeManager.CodeContainer codeContainer = null;
string methodName = "Validate";
object[] parameters = null;
try
{
// Simulate pulling code from disk or from database.
codeContainer = new SourceCodeManager.CodeContainer();
codeContainer.ClassName = "MyClassName";
codeContainer.NameSpace = "MyNameSpace";
codeContainer.UniqueKey = "robbe";
codeContainer.SourceCode = sourceText;
// Compare this code with that which is set in
// our static List<CodeContainer> by the UniqueKey.
// If not found, compile and add.
// If found but source code is different, delete, compile, and add.
// If found and source code is the same, grab reference to
// previously compiled assembly.
SourceCodeManager.Registration.Update(codeContainer);
// Let's create some sample parameters to pass into
// our dynamically compiled class/method.
parameters = new object[2];
List<int> parameter1 = new List<int>();
parameter1.Add(5);
parameter1.Add(10);
List<int> parameter2 = new List<int>();
parameter2.Add(50);
parameter2.Add(100);
parameters[0] = parameter1;
parameters[1] = parameter2;
// Execute the desired method and pass in our parameters.
// Our Execute method will always return an object. So,
// we need to know its actual desired return type ahead
// of time if we want to convert it to the property Type.
bool returnValue = (bool)runtime.Execute(codeContainer,
methodName,
parameters);
return "Method result: " + returnValue.ToString();
}
catch (Exception err)
{
return err.ToString();
}
finally {
runtime = null;
}
}
}
}
USE AT YOUR OWN RISK!
ASP.NET 2.0 - Safely Compile And Execute Source Code Dynamically By Robbe Morris
The 4 steps to setting up a network and VPC.
- Add the MS Loopback Adapter to your physical machine, with a static IP Address (this is actually harder to do than it sounds; it took me about half an hour to find how to install it).
- Configure all my virtual machines with 3 network cards: One tied to the wireless card, one tied to the ethernet card, and one tied to the loopback adapter.
- Configure the first two cards with DHCP, and the last one with a static IP different from the host machine's one.
- Add entries in the host files of all machines using the static ip addresses to enable quick name resolution.
Taking advantage of sparse columns and data compression in SQL 2008.
The above result shows that Page Compression has the best compression rate which is 16% of the original size, and the size of sparse column table is 31%, while row compression is 36%. Data Compression has a nice stored procedure sp_estimate_data_compression_savings which can estiamte the space saving by sampling data into tempdb, and compress the sampled data. The result is listed in “Estimated Saving” column in the table. I will also discuss the changing time later in this blog.
After installing SQL2008 as a named instance, you can stop/start from a command line using NET STOP MSSQL$SQL2008 and NET START MSSQL$SQL2008.
Unfortunately mine failed with this error when I tried to start.
The SQL Server (SQL2008) service terminated with service-specific error 1455 (0x5AF).
Then I got this error. The dreaded sysfader.
Application popup: SysFader: iexplore.exe - Application Error : The instruction at "0x30037c61" referenced memory at "0x00000000". The memory could not be "read".
Click on OK to terminate the program
Click on CANCEL to debug the program
Will try a reboot and see what happens.
I put together a quick C# utility that lists all services on a machine and provides searching capabilities called ListService.
From a command line: ListService.exe %1
Where %1 is the service name you are searching for. Leave blank to show all services under the registry key SYSTEM\CurrentControlSet\Services
It’s called ListServiceSetup.msi and requires .NET 2.0 Framework and Windows Installer 3.1.
It’s only(!) 32k and you can delete the .config file. Should be around 2k with the 10 lines of code in it… but that’s .NET for you. The installation package is a whopping 342k.
You can download from here:
ListService 0.0.0.1 beta installer
http://www.wavesmash.com/download/utils/
Description of Typical Services Registry Keys Under Hkey_Local_Machine
Data mining hits the cloud.
Currently the technology demonstration allows you to access the Table Analysis Tools for Excel 2007 by connecting to our hosted data mining service, meaning you can use the Table Analysis Tools anywhere at anytime without connectivity to your local IT infrastructure. Additionally there is a web interface that allows you to upload a limited amount of data and play with the tools without even having Excel! Currently only a few of the tools are implemented in the web interface. If for any reason you haven't had a chance to grab those amazing Table Analysis Tools and see what SQL Server Data Mining can do for you - run, don't walk, over to http://www.sqlserverdatamining.com/cloud/ and try them out right now!
Jamie's Junk : KDD 2008 and Incredibly Awesome SQL 2008 Data Mining Demos
My step-by-step screen shot installation of SQL 2008. Not a best-practice installation by any means.
Mount the ISO using Microsoft Virtual CDROM.
Run Setup
Run System Checker
Install Upgrade Advisor
Install single instance.
Next screen is product key (not shown here)
---------------------------
Rule Check Result
---------------------------
Rule "Windows Firewall" generated a warning.
The Windows Firewall is enabled. Make sure the appropriate ports are open to enable remote access. See the rules documentation at http://go.microsoft.com/fwlink/?LinkId=94001 for information about ports to open for each feature.
---------------------------
OK
---------------------------
![clip_image001[4]](http://lh5.ggpht.com/wavesmash/SMlfndm-N5I/AAAAAAAAAJQ/y21c_DaTCKs/s1600-h/clip_image001%5B4%5D%5B3%5D.png "clip_image001[4]")
![clip_image001[6]](http://lh4.ggpht.com/wavesmash/SMlfoEly8cI/AAAAAAAAAJY/RwMCoHUutUA/s1600-h/clip_image001%5B6%5D%5B3%5D.png "clip_image001[6]")
![clip_image001[8]](http://lh4.ggpht.com/wavesmash/SMlfoloQI3I/AAAAAAAAAJg/BtNxkUDEt0o/s1600-h/clip_image001%5B8%5D%5B3%5D.png "clip_image001[8]")
![clip_image001[10]](http://lh5.ggpht.com/wavesmash/SMlfo_nkvzI/AAAAAAAAAJo/mWwtF13yRn0/s1600-h/clip_image001%5B10%5D%5B3%5D.png "clip_image001[10]")
![clip_image001[12]](http://lh4.ggpht.com/wavesmash/SMlfpfdNtfI/AAAAAAAAAJw/WJ5JYUYiWbM/s1600-h/clip_image001%5B12%5D%5B3%5D.png "clip_image001[12]")
![clip_image001[14]](http://lh4.ggpht.com/wavesmash/SMlfqMNKC4I/AAAAAAAAAJ4/LDCEl_-xfbc/s1600-h/clip_image001%5B14%5D%5B3%5D.png "clip_image001[14]")
Next screen you select Windows or Mixed mode and add admins to SQL (Removed from here)
![clip_image001[18]](http://lh6.ggpht.com/wavesmash/SMlfraCzK4I/AAAAAAAAAKA/iJCJwLlADvM/s1600-h/clip_image001%5B18%5D%5B3%5D.png "clip_image001[18]")
![clip_image001[20]](http://lh3.ggpht.com/wavesmash/SMlfrk3bQcI/AAAAAAAAAKI/gLpnLg7j1os/s1600-h/clip_image001%5B20%5D%5B3%5D.png "clip_image001[20]")
Next screen you add admins to Analysis Services (removed from here)
![clip_image001[24]](http://lh6.ggpht.com/wavesmash/SMlfsMXosHI/AAAAAAAAAKQ/d80ehHiiBTU/s1600-h/clip_image001%5B24%5D%5B3%5D.png "clip_image001[24]")
![clip_image001[26]](http://lh4.ggpht.com/wavesmash/SMlfsxObUxI/AAAAAAAAAKY/k8pgmfUv8a8/s1600-h/clip_image001%5B26%5D%5B3%5D.png "clip_image001[26]")
![clip_image001[28]](http://lh5.ggpht.com/wavesmash/SMlftqThgjI/AAAAAAAAAKg/mhpUR0sgBnM/s1600-h/clip_image001%5B28%5D%5B3%5D.png "clip_image001[28]")
---------------------------
Rule Check Result
---------------------------
Rule "Previous releases of Microsoft Visual Studio 2008" failed.
A previous release of Microsoft Visual Studio 2008 is installed on this computer. Upgrade Microsoft Visual Studio 2008 to the SP1 before installing SQL Server 2008.
---------------------------
OK
---------------------------
![clip_image001[30]](http://lh3.ggpht.com/wavesmash/SMlft1Y-KqI/AAAAAAAAAKo/clKin5BS0Y4/s1600-h/clip_image001%5B30%5D%5B3%5D.png "clip_image001[30]")
![clip_image001[32]](http://lh5.ggpht.com/wavesmash/SMlfuQO6ZlI/AAAAAAAAAKw/BPGQFyhmKi0/s1600-h/clip_image001%5B32%5D%5B4%5D.png "clip_image001[32]")
And wait. And wait. And wait. And wait. About 1 hour later.
![clip_image001[34]](http://lh5.ggpht.com/wavesmash/SMlfu74suQI/AAAAAAAAAK4/q3-ZKjF3WBw/s1600-h/clip_image001%5B34%5D%5B2%5D.png "clip_image001[34]")
And wait
![clip_image001[36]](http://lh3.ggpht.com/wavesmash/SMlfvBBhaSI/AAAAAAAAALA/hMvN10bqzZU/s1600-h/clip_image001%5B36%5D%5B2%5D.png "clip_image001[36]")
The following notes apply to this release of SQL Server only.
Microsoft Update
For information about how to use Microsoft Update to identify updates for SQL Server 2008, see the Microsoft Update Web site <http://go.microsoft.com/fwlink/?LinkId=108409> at http://go.microsoft.com/fwlink/?LinkId=108409.
Reporting Services
The Reporting Services installation options that you specified in Setup determine whether additional configuration is required before you can access the report server. If you installed the default configuration, the report server can be used immediately. If you installed the program files only, you must run the Reporting Services Configuration tool to deploy the report server.
To install the Reporting Services Add-in for SharePoint Technologies, start the rsSharePoint.msi in the Setup folder on the SQL Server 2008 installation media after SQL Server Setup is complete.
Documentation and Samples
To install the .NET Framework SDK, see “Installing the .NET Framework SDK” in SQL Server 2008 Books Online <http://go.microsoft.com/fwlink/?LinkId=114089> at http://go.microsoft.com/fwlink/?LinkId=114089.
By default, sample databases and sample code are not installed as part of SQL Server Setup. To install sample databases and sample code for non-Express editions of SQL Server 2008, see the CodePlex Web site <http://go.microsoft.com/fwlink/?LinkId=87843> at http://go.microsoft.com/fwlink/?LinkId=87843. To read about support for SQL Server sample databases and sample code for SQL Server Express, see Databases and Samples Overview <http://go.microsoft.com/fwlink/?LinkId=110391> on the CodePlex Web site at http://go.microsoft.com/fwlink/?LinkId=110391.
For more information about late-breaking changes in this release of SQL Server, see the latest readme file <http://go.microsoft.com/fwlink/?LinkId=100093> at http://go.microsoft.com/fwlink/?LinkId=100093.
For information about SQL Server 2008 Surface Area Configuration, see the following SQL Server 2008 documentation topics:
In SQL Server 2008 Books Online: “Understanding Surface Area Configuration.”
In SQL Server 2008 Setup Help: “Minimize SQL Server 2008 Surface Area.”
In SQL Server 2008 Books Online on MSDN: Understanding Surface Area Configuration <http://go.microsoft.com/fwlink/?LinkId=106282> at http://go.microsoft.com/fwlink/?LinkId=106282.
SQL Server 2008 Overview, data platform, store data | Microsoft
SQL 2008 Extended Events are a powerful new way of troubleshooting problems with SQL Server. One short coming is that there is no UI support for this new feature. The Extended Events Manager is a C# .NET winforms application that aims to simplify creating and viewing metadata for Extended Events Sessions in SQL 2008.
Planned Functionality:
- View Extended Events Metadata for all Available Objects
- View Event Sessions on a Server
- Start/Stop Events Sessions
- Create new Event Sessions
- Alter existing Event Sessions
- Drop Event Session
- Script all operations
- View information stored in targets for active Event Sessions
Release Notes
This project was placed on CodePlex at the following address per the rules of the SQL 2008 Heros contest:
http://www.codeplex.com/ExtendedEventManager/
all builds of the project, source code, and documentation have been placed there with Issue Tracking enabled,
as well as discussions enabled. If you would like to download the project, report a bug, or request a feature
be added, please visit the above link.
What every DBA should know.
Here are some sample T-SQL scripts for backing up and restoring databases and transaction logs (using AdventureWorks on the C: drive, which would not be a good idea in reality). They also show how to detach and attach a database, and how to do some basic index and statistics maintenance. This was originally for the MCIS-4423 class that I teach at Denver University.
For the jump to Url functionality in a report…
Instead of "localhost/application/page.aspx?id=" + Fields!ID.Value
Use "localhost/application/page.aspx&id=" + Fields!ID.Value
Use a & instead of the + sign just in front of your field value. I can't explain why but that's how I got it to work...
Virtual PC Tips - Using SYSPREP
Virtual PC is useful for creating development and test environments where a number of server products are required. Take for instance setting up a development environment for a BI engagement. You may need SQL Server 2005, Reporting Services, Analysis Services, MOSS and possibly even Performance Point. If your development environment consists of a single laptop, Virtual PC is something you want to check out. As an aside you can get decent performance when running 2-3 Virtual PCs with 4 GB of RAM.
Microsoft has a tool called SYSPREP which you can use to clone an existing VHD file that you've prepared. This allows you to create a Virtual PC image that you can make a copy of and use. SYSPREP can take care of generating a computer name and a unique SID so that you won't "collide" with existing Virtual PC images.
I'll provide an example of using SYSPREP with a Windows 2003 Server Virtual PC Image. Open the Deploy.cab file on the Windows 2003 Server CD from the Support\Tools folder (just double click in Windows Explorer). Extract the contents to a folder (e.g. C:\DEPLOY; highlight the files in Deploy.cab, right click, then select Extract). When you are running a Virtual PC image, you can click the CD menu option, select Capture ISO Image, and point to your Windows 2003 Server .iso image file; then use Windows Explorer to access the Deploy.cab file.
Run Setupmgr.EXE (from the folder where you copied the Deploy.cab contents) to create an "answer" file; you'll proceed through a series of dialogs that record your answers to certain configuration questions to be answered when you launch a VPC that you created by copying a sysprepped VHD file. The dialogs are pretty self-explanatory; make sure to select Sysprep Setup on the Type of Setup screen.
After running Setupmgr.EXE your Virutal PC will shutdown. At this point delete the .VMC file and save the .VHD file. You will no longer launch the Virtual PC image; when you want a new Virtual PC you create a new virtual machine and use a copy of the .VHD file. The first time you launch a new Virtual PC image, SYSPREP will do it's magic and you'll have a working VPC image.
RDA Blogs - Business Intelligence and SQL Server: Virtual PC Tips - Using SYSPREP
A really cool tool out of MS SQL 2008 that didn’t get released is Report Designer, even with some of the Analysis Services limitations it has. Even though it isn’t released yet, you can still get it.
The good news is that you can now download the RC1 version here. The really good news is that it has got some wizards and loads of other stuff in it to make using it a lot more friendly. It’s also a standalone .msi which you can then deploy to your users as needed, although I believe it will eventually be a click once application like Report Builder v1 in SQL Server 2005.
I will be having a play with over the next few days and I will be sharing that in a future post.
Expert Recommendations on Positioning & Selling Business Intelligence
The Business Intelligence space offers one of the hottest growth areas for our partners. With Microsoft’s suite of BI offerings, you are equipped to make the most of this opportunity. Join Joanne Pinter, Senior Marketing Manager for partner Readiness, Business Intelligence and Guy Weismantel, Marketing Director of Business Intelligence as they share recommendations on how to position and successfully sell BI. A must listen for any partner looking to expand their solution base!
WMA | MP3 |
To save to your computer, right click and choose 'save target as…'.
Free e-book offer: Get a free offer of excerpts from Introducing SQL Server 2008 by Peter DeBetta.
E-learning offers:
Collection 6187: What's New in Microsoft SQL Server 2008 (includes three free clinics)
Clinic 6258 AE: New Features of Microsoft SQL Server 2008 Reporting Services
Get a free second chance to take your certification exam if you do not pass it the first time
Brief Description
Download the Microsoft SQL Server 2008 Upgrade Advisor. Upgrade Advisor analyzes instances of SQL Server 2000 and SQL Server 2005 to help you prepare for upgrades to SQL Server 2008.
I have been looking for something like this since Microsoft moved off the newsgroup model. Web-based forum browsing is painful.
Unfortunately it's just that - a reader. No write capabilities? The source code is available - should not be too hard to add a post link to open in a web browser.
Yes, after many months of discussions, testing and so on, the Team with which I had the pleasure to interact with during my internship in Seattle has finally released the MSDN Forum Client (July 08 CTP) !
Be_Lead Blog : MSDN Forums Offline Reader now available! (CTP)
Here is how to do it:
[WebMethod, SoapDocumentMethod(OneWay = true)]
public void LogError(string errorMessage)
{
DoSlowOperation(errorMessage);
}
Probably without as much fanfare or 1st-day box-office revenue as The Dark Night, SQL Server 2008 launched today.
I hope you are sitting down, and don’t have a coffee in your hand when you read this, but SQL Server 2008 released at 17:00 GMT today!
The launch event in Toronto actually happened prematurely in February.
Don’t expect to see it in your nearest Best Buy until they print off some CDs and boxes.
http://sqlblog.com/blogs/aaron_bertrand/archive/2008/08/06/sql-server-2008-has-rtm-d.aspx
Very important SQL Server update
There is a patch available for four elevation of privilege vulnerabilities recently discovered in SQL Server.
From http://www.microsoft.com/technet/security/bulletin/ms08-Jul.mspx:
This security update resolves four privately disclosed vulnerabilities. The more serious of the vulnerabilities could allow an attacker to run code and to take complete control of an affected system. An authenticated attacker could then install programs; view, change, or delete data; or create new accounts with full administrative rights.
Microsoft Baseline Security Analyzer can detect whether your computer system requires this update. The update may require a restart.
Get the update here:http://www.microsoft.com/technet/security/bulletin/ms08-040.mspx
