Sunday, October 26, 2008

SSIS Junkie : T-SQL: Generate a list of dates

 

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)

SSIS Junkie : T-SQL: Generate a list of dates

Wednesday, October 22, 2008

SQL Server Forums - Deleting Large Number of Rows

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

SQL Server Forums - Deleting Large Number of Rows

Sunday, October 19, 2008

How to open a website in Visual Studio 2005 from the command line - BradleyB's WebLog

 

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

Querying and Reporting on Report Execution Log Data

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.

Querying and Reporting on Report Execution Log Data

Tuesday, October 14, 2008

Andrew Clancy's Blog : NHibernate Query Generator in Action

 

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.

Andrew Clancy's Blog : NHibernate Query Generator in Action

MS BI Conference: Monday Keynote - Mike Diehl's WebLog

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".

MS BI Conference: Monday Keynote - Mike Diehl's WebLog

Tuesday, October 07, 2008

The Programmer's Weblog: Inserting Blob into Microsoft Sql Database

 

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

Using Grouping Sets in SQL Server 2008 | Servers and Storage | TechRepublic.com

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

Monday, October 06, 2008

Cumulative update package 1 for SQL Server 2008

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.

Cumulative update package 1 for SQL Server 2008

Thursday, October 02, 2008

Hey Scripting Guy! How Can I Write to Excel Without Using Excel?

The scripting guy answers this question.

How Can I Write to Excel Without Using Excel?

Hey, Scripting Guy! Question

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?

How Can I Read from Excel Without Using Excel?

Hey, Scripting Guy! Question

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

Nick MacKechnie : Microsoft Announces SQL Server 2008 Experience

 

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

Jamie's Junk : Time Series Reporting Stored Procedure - part 3 of 3

 

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

Wednesday, October 01, 2008

Web Development Helper

 

Web Development Helper

Web Development Helper plugs into Internet Explorer and provides tools for Ajax and Web development.

Web Development Helper

PSS SQL Server Engineers : How to fix your SQL Server 2008 Setup before you run setup...

 

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...

XML Workshop XXII - A TSQL RSS Library - SQL Server Central

 

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.

XML Workshop XXII - A TSQL RSS Library - SQL Server Central