Here is their description:

Red Gate Software has purchased SQL Prompt, an addictive add-in that provides Intellisense forQuery Analyzer, Visual Studio 2005, VS 2003 and Management Studio. For the summer months, while SQL Prompt undergoes further development, feature tuning and relentless testing, the tool as it is now will be made available to everyone, for free. You can download it here:
http://www.red-gate.com/products/SQL_Prompt/index.htm.

I gave it a try and while I wasn’t a fan of the program running constantly in my system tray.  It did prove to be quite useful while I as coding.  I definitely won’t be uninstalling it anytime soon and expect it’ll end up on the other machines I use by the end of the day.

The other day, I was asked how to disable export options for one of the reports I am developing at my current client.  Having not fielded this request previously, I checked the around found you are able to disable export options for each reporting services instance

In the rsreportserver.config file you can set Visible="false" on the <Extension> elements in the <Render> element that shouldn’t be accessible.  This also affects the email delivery extension.

<Render>
    <Extension Name="XML" Type="Microsoft.ReportingServices.Rendering.XmlDataRenderer.XmlDataReport,Microsoft.ReportingServices.XmlRendering"/>
    <Extension Name="NULL" Type="Microsoft.ReportingServices.Rendering.NullRenderer.NullReport,Microsoft.ReportingServices.NullRendering" Visible="false"/>
    <Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
    <Extension Name="IMAGE" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.ImageReport,Microsoft.ReportingServices.ImageRendering"/>
    <Extension Name="PDF" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PdfReport,Microsoft.ReportingServices.ImageRendering"/>
    <Extension Name="RGDI" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.RemoteGdiReport,Microsoft.ReportingServices.ImageRendering" Visible="false" LogAllExecutionRequests="false"/>
    <Extension Name="HTML4.0" Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html40RenderingExtension,Microsoft.ReportingServices.HtmlRendering" Visible="false" LogAllExecutionRequests="false"/>
    <Extension Name="HTML3.2" Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html32RenderingExtension,Microsoft.ReportingServices.HtmlRendering" Visible="false"/>
    <Extension Name="MHTML" Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.MHtmlRenderingExtension,Microsoft.ReportingServices.HtmlRendering"/>
    <Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering"/>
</Render>

The problem with this solution though is that it is for the entire reporting services site.  If only one or two reports need a certain extract type removed, you need to either remove the type for all extracts or live with the issues that exporting to that extract type causes.

There should be properties on each report that will disable or enable extract types.  I can’t see how it would be that difficult to store this information and access it rendering the report.  The toolbar is already reading an XML file to determine the extract types to make available, it should be a significant leap to query this information from the database as each report is called.

My undocumented stored procedure of the day is sp_MSForEachDB. This procedure allows SQL statments to loop through all database names on a server with a dynamic SQL string. The procedure accepts the variable @command1 for the dynamic SQL string, and the database name is passed into the string through the use of "?". Unlike using the sp_ExecuteSQL you are able build the SQL string

One thing to note with this stored procedure is that is does not switch contexts while it executes. So all of the SQL statements will run in the context of the master database. Getting around this issue is fairly trivial using either three-part naming of database objects or through the USE command.

For example executing sp_HelpDB for each database can be achieved through three-part naming:

sp_MSForEachDB ‘?..sp_HelpDB’

or the USE command

sp_MSForEachDB ‘Use ? exec sp_HelpDB’

The sp_HelpDB can be quite useful, I recently ran a script similar to the one below to get a list of IDs that appeared in a table in all of our client databases.

Create Table #Foo
(
DBName sysname,
FooType varchar(20),
FooID varchar(20)
)
Exec sp_MSForEachDB ‘Use ? If Object_ID(”Foo”) Is NOT NULL Insert Into #Foo Select ”?”, FooType, FooID From Foo’
Select * From #Foo

Hope this helps the many (or few rather) people that read this.

Well, contrary to the title you can’t…

I felt empty inside the other day and it was all because I kept adding queries to my SQL Server Management Studio (SSMS) project and they weren’t sorting at all.  They just sat there in the order in which I added them to the project.  The continuing problem is that you cannot sort the queries in SSMS.

Now this empty feeling is being shared by a few others, and what is probably an easy enhancement still waits for that full feeling of accomplishment.  Please if you also have the time and the same empty feeling… vote on it!

Until then the guys over at SQLDBATips have created an external tool for sorting project files.  So I guess I’ll only have a half empty feeling now.

Every so often someone will get me on the topic of why "Select *" is an official Bad Practice. There are various arguements against "Select *" but one that a lot of programmers may not be aware of is the relationship between "Select *" and views.

When a view is created with "Select *" it references the tables that are part of the view and creates metadata to translate "Select *" into a listing of the columns that the view needs to retrieve. While this is a way to get around the work of typing out the columns for the result set there are unintended circumstances that can occur.
For an example, create the following table and view:

create table tbl_foo ([roo] int, [too] int, [moo] int)
create view vw_foo as select * from tbl_foo

If select from this view, you’ll get a list back of all of the columns in the table as expected. Now alter the table to include an additional column, such as:

alter table tbl_foo add [BOO] int

Now when you select from the view only the columns roo, too, and moo will return. The column BOO is not included in the select list. As I mentioned before when a view is created, SQL Server obtains metadata about to define what columns "Select *" references. In order to return the columns BOO in the view, the metadata needs to be refreshed. SQL Server does include a stored procedure, the procedure sp_refreshview will refresh the metadata of the view.

exec sp_refreshview ‘vw_foo’

Select from the view now will return all of the columns from the table. 
Being able to fix the metadata issue that table schema changes cause to "Select *" views doesn’t offset the additional step required to go in after the fact table changes and refresh each affected view. Views need to return specific sets of data, be naming the columns that are returned mistakes can be avoided that might sentance you to WTF.
I can imagine the guy who avoids refreshing views to prevent new columns from returning in the views. Then one day someone updates the sales table and refreshes the view, and suddenly the CEO wants to know why there are credit card numbers on the corporate website.

On a few occassions in the past query filters and clauses have bitten me and reviewing how they work every now and then is always a good idea. Itzik’s article for SQL Mag is a great refresher for anything forgotten.

Understanding Query Filters and Clauses -RSS

PASSMN Meeting is coming up and I have my second speaking engagement there.  I’m really excited by the topic and hope that all who attend see the value in Notification Services over custom built notification engines.

Topic Report Builder – Model Creation
Details
Providing non-technical users with secure, user-friendly ad hoc access to relational data can be a challenge. Fortunately, SQL Server 2005 provides the Report Builder to accomplish this task. The Report Builder uses a layer of abstraction, called the Report Model, to hide the complexities of primary keys and foreign key constraints from and to provide additional metadata to Report Builder users. Brian Larson will take the group through the creation of Report Models, including several tips for model clean up and dealing with meta data. The presentation will also include the return of Duct Tape Man and Jello Woman!

Speaker: Brian Larson

About the Speaker
Brian Larson, MCSD, MCDBA, has 20 years of experience in the computer industry and 15 years’ experience as a consultant creating custom database applications. Brian is currently the Chief of Technology for Superior Consulting Services in Minneapolis, Minnesota, a Microsoft Consulting Partner for Reporting Services. Brian served on the Reporting Services development team as a consultant to Microsoft. In that role, he contributed to the original code base of Reporting Services. Brian is the author of the semi-monthly “B.I. Powers” column for SQL Server Magazine as well as Microsoft SQL Server 2005 Reporting Services and Delivering Business Intelligence with Microsoft SQL Server 2005 both from McGraw-Hill/Osborne. He can be contacted at blarson@teamscs.com.

Topic SQL Server Notification Services
Details
An introduction to Notification Services with an overview of its architecture and the development framework. The session will also include a couple real-world sample implementations of Notification Services that can be used as a starting point for other projects.
Speaker: Jason Strate

About the Speaker
Jason Strate has been a DBA for the past 8 years. During that time he’s designed and implemented both OLAP and OLTP solutions. These solutions have involved clustering servers, database monitoring/tuning, analysis services, DTS/Integration Services, Reporting Services, and Notification Services. He is currently a consultant with Digineer, Inc.

Date

5/16/2006 4:30:00 PM
Time 4:30 pm – 7:00 pm
Capacity 120

Agenda
4:30 pm – 5:00 pm Meet, greet and eat; 
5:00 pm – 5:15 pm Local PASS updates
5:15 pm – 6:15 pm Report Builder – Model Creation
6:15 pm – 7:00 pm Notification Services

Register for this Event
Location Microsoft Office, 8300 Normandale Center Drive, 9th Floor

My current client wants to restrict the export options for reports to those that work best with the reports that they are delivering.  When looking into this request I found the following article by Roman Rehak that covers exactly how to accomplish this.

Also discovered SQL Server 2005 Reporting Services has scrolling headers.  Simply open the properties of the table you want the scrolling headers on and select the option "Header should remain visible while scrolling."  Presto-chango and it works.

Geez, it’s been a while since I’ve blogged here.  I gotta get back on the horse.