A little over 2 years ago I posted a couple of methods of finding the instances of a string within stored procedures - at the time I'd left some debug 'Print @' code in at least one stored procedure and needed to find it before releasing the code.

Today, I wanted to do the same thing, and as over the past 2 years I've been weaning myself off accessing the sysobjects tables, instead making use of the INFORMATION_SCHEMA views, I decided it was time to add an update to that earlier post.

So, using INFORMATION_SCHEMA.ROUTINES it is coded as :
  SELECT SPECIFIC_NAME
  FROM INFORMATION_SCHEMA.ROUTINES
  WHERE ROUTINE_DEFINITION LIKE '%PRINT @%'

Labels: SQL, SQLServer, SQLServer2005

// posted by Jane @ 2:53 PM   

Comments:

Hi Jane

Brighton Bloggers Aggregate doesn't seem to be working

hope all OK with you

best wishes

Ian Lawes/Abi Rhodes

# posted by  Abi Rhodes : 9:30 PM

 

Post a Comment

On Thursday myself and Dave (the Madgex DBA) headed off to the Microsoft offices in Victoria to attend the SQL Server User Group meeting which was the launch event for SQL Server 2008.

There were 2 key speakers (blurb taken from an email from SQL Server User Group):

• Jasper Smith did the first stint on Admin stuff. He’s been an MVP for quite a while and has until recently been working at Nationwide. His blog can be found here and his website here. He’s got some great utilities like server manager for vista and Reporting Services scripter.
• Simon Sabin did the last session on dev stuff. He is also an MVP and is a freelance consultant and works as part of SQL Know How and SQL Skills. His blog can be found here.

Bruce posted a link to the Dev Team earlier this week which covers a lot of the same ground, 10 reasons why SQL Server 2008 is going to rock, and so I'm going to use this as a starter and just supplement it with the additional areas covered.

• Database encryption – another enterprise only tool. You can now encrypt a database against a user defined key (stored as a certificate on the filing system). This will also result in tempdb being encrypted. When encrypted the mdf and ldf files are also encrypted, as are backups.
• Debugging has improved in the Management studio, can debug through a series of statements not just stored procedures.
• Using Inline variable assignment you can use a rowset to insert multiple data items in one line – Insert into values (1), (2), (3) – at the back end this gets translated into a union statement (see more here).
• Merge is introduced (finally) – see here for some posts about it. But the short version is the ability to do
  Merge Into
  Using
  When matched then
    Update set
  When target not matched then
    Insert
  Basically doing an insert and update in one statement. Merge is deterministic, and appears to be quicker but mileage may vary.
• CLR now handles larger data types (> 8000 bytes)

Another good evening, this time complete with pizza and beer.
Update: Simon has posted a follow-up containing the answers to questions he didn't get time to answer.

Labels: event, london, SQLServer

// posted by Jane @ 6:55 PM   

Comments:

Post a Comment

In SQL Server 2000 Enterprise Manager provided the ability to output SQL file on an object level for tables, functions, stored procedures etc. In SQL Server 2005 this option disapeared and the only outputting was in a single file. This wasn't great for controlling objects within a source control system.

A while ago I stumbled across Scriptio, a ClickOnce application to do just this. It has a very simple interface, and you can tell that it was designed to solve a problem, rather than as an end solution.

Tab One - Database Objects

To get a list of database objects, enter a Server name, select either Use Windows Authentication or enter a Username and password and press Connect. This should populate the Database drop down. Select a database and the bottom section should be populated with the objects that are scriptable within your database. From here, it is a simple case of selecting the appropriate options from the top right hand section (Include CREATE, Include DROP etc) and selecting the appropriate objects from the bottom section (there is a handy Script All option which selects everything. I have never been able to get the Schema or Type drop downs to populate, which may be something to do with the design of my objects.

Tab Two - More Options

The options tab allows choices of how many files to create (or even if you want to create a file), where to store them and some additional scripting objects (Include collation, Qualify CREATEs with Schema etc).

Tab Three - Generated Script

This tab displays the SQL generated - in an single box, allowing the content to be copied to clipboard and used elsewhere. Just as an observation, I've never seen the "Save As..." button enabled but I've never needed to use it either so that hasn't been a problem.

Sample script - a table

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Book]') AND type in (N'U'))
DROP TABLE [dbo].[Book]
GO

/****** Object: Table [dbo].[Book] Script Date: 06/16/2008 17:30:18 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Book](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[Author] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[PublisherId] [int] NULL,
[ISBN] [varchar](25) COLLATE Latin1_General_CI_AS NULL,
[PublishedYear] [int] NULL,
[BookAgeId] AS (case datepart(year,getdate())-[PublishedYear] when (0) then (1) when (1) then (2) else (3) end)
) ON [PRIMARY]

GO

Sample script - a stored procedure

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetBooks]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[up_GetBooks]
GO


/****** Object: StoredProcedure [dbo].[up_GetBooks] Script Date: 06/16/2008 17:30:19 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Jane Dallaway
-- Create date: 12 June 2008
-- Description: Get all books, in full detail
-- =============================================
CREATE PROCEDURE up_GetBooks
AS
BEGIN
SET NOCOUNT ON;

SELECT Book.Id, Book.Title, Book.Author, Publisher.Name, Book.ISBN, Book.PublishedYear, BookAge.Name BookAge
FROM Book
INNER JOIN BookAge
ON Book.BookAgeId = BookAge.Id
INNER JOIN Publisher
ON Book.PublisherId = Publisher.Id

END

GO

Summary

This is a great utility to allow scripting on an object by object basis. The source code is available too.

Labels: SQL, SQLServer2005

// posted by Jane @ 5:43 PM   

Comments:

Post a Comment

We've done some more work on integrating FxCop into our development process this week, and today Mike and I have sat down and started work on choosing which rules to validate against initially. To do this, we've based our starting set on the rules that Microsoft have switched on internally. Thus far we've been through the Design, Naming (mostly left switched off until the coding standards review is complete) and Performance rules.

Having set these up in a .FxCop project, I then wanted to be able to make use of the Post Build event to get the assembly analysed according to a predefined set of rules. This took a bit of research but I think I've got it sorted now:

Instead of using:
"C:\Program Files\Microsoft FxCop 1.36\FxCopCmd.exe" /c /f:"$(TargetPath)" /r:"C:\Program Files\Microsoft FxCop 1.36\Rules" /consolexsl:"C:\Program Files\Microsoft FxCop 1.36\Xml\VSConsoleOutput.xsl"
I'm using
"C:\Program Files\Microsoft FxCop 1.36\FxCopCmd.exe" /c /f:"$(TargetPath)" /consolexsl:"C:\Program Files\Microsoft FxCop 1.36\Xml\VSConsoleOutput.xsl" /project:"C:\Program Files\Microsoft FxCop 1.36\DefaultRules.FxCop"
and so am making use of the "/project" flag to specify the .FxCop file to run, and no longer specifying the /r (or /rule) tag to indicate where to find the rules. It is important that the .FxCop file doesn't contain any targets of its own, as otherwise both the assembly produced by the build AND the assemblies targetted in the .FxCop file will be analysed, which could lead to a lot of head-scratching when you try to work out why a source file that doesn't exist is causing problems :-)

More information on the options for the FxCop Command Line version is available here.

Labels: static code analysis

// posted by Jane @ 2:00 PM   

Comments:

Post a Comment

Last week, Chris Alcock included a mention of an article .NET Database Migration Tool Roundup in The Morning Brew #109. Both Bruce and I read this and came to the same conclusion, that Tarantino might be worth a look to stop us from continuing writing our own tool to manage the change process reliably. Today we invested some more of our ILP time into examining how it works, and what it does.

Firstly, there isn't much documentation, so we had to fumble around a bit to work out how to use it. I read the wiki page with interest, and found myself agreeing with what was said there. I especially liked the quote:

"Successful database change management requires that a consistent process be applied by all team members. Without a consistent process than the tools provided in this solution will not provide its full value"

We found that this documentation, and the process description validated our aims and thoughts on how the process should work, suggesting:

• a local database for each developer - this works for me on 2 levels, firstly in the same way that a good developer wouldn't check their code into source control until it was complete, the database changes shouldn't affect anyone else until this stage either, and secondly database changes get tested by other team members
• changes are implemented as SQL scripts
• there is a predefined order in which the SQL scripts should run

We aren't, initially, looking at a tool that needs to integrate with an automated build environment, so we didn't need a lot of the features that Tarantino offers. Instead we concentrated on the Tarantino.DatabaseManager.exe application and worked with that. The result of this is that some of our requirements aren't met by the DatabaseManager but may be met by some of the other aspects of Tarantino.



The user interface is fairly self explanatory, and there is a .config file associated with it to pre-fill the default values. There are 4 possible actions: Create, Update, Drop and Rebuild.

• Create - the default. This will create the database identified in the Database field (I didn't find how you specified the settings for database creation during my 1.5 hour investigation, but I'm sure it's there somewhere). It will then update the database identified in the Database field according to scripts found in the Update sub-folder of the folder specified in the Script Folder field, running them in alphabetical order.
• Update - This will update the database identified in the Database field according to scripts found in the Update sub-folder of the folder specified in the Script Folder field, running them in alphabetical order if they haven't already been reported as run in the table of scripts
• Drop - This will drop the database identified in the Database field
• Rebuild - This will combine the Drop and Create tasks above.

DatabaseManager uses file naming as a convention for determining the order in which the update files will run - the recommendation for naming is 0001_Script.sql, 0002_Script.sql etc however during our investigation we discovered that we could leave Copy of 0001_Script.sql hanging around and this would just get appended to the list in alphabetical order. We were somewhat concerned that during the development process amongst a team, more than one person could be working on database updates at a time (and when using a local database, there is no guarantee that anyone else would know about it) which could mean, at best, two files with the same prefix, i.e. 0003_AddTableArticles.sql and 0003_FixBug12122.sql and at worse a source control conflict of 0003_Script.sql. This is where naming conventions would obviously come into play. During our discussions we liked the idea of some form of dependency mapping - i.e. 0003_AddTableArticles.sql is dependent on the change 0001_Script.sql. This would obviously help us to ensure that errors with code vs database versioning could be avoided. This isn't provided within Tarantino.

DatabaseManager records the scripts run in a table named usd_AppliedDatabaseScript in the database that is being worked on. This means that all actions will not be recorded in perpetuity as choosing the action Drop or Rebuild from the user interface would remove this table as well as all others, and the database itself. One of our preferences is to have a central database per server which records the scripts run - this would mean that the dba team would be able to see, at a minimum, what scripts have been run and when. We also feel that having some form of contact details associated with the script is a useful addition - this seems to be covered in the deployment tool that is included in Tarantino, but not on a script by script basis.

One of the process pre-requisites mentioned for Tarantino is:

"Create a change script that wraps all of the database changes into a single transactional change script. A Tool like Red Gate SQL Compare makes this a 30 second operation."

This unfortunately means that error handling is poor and transaction handling is non existant. One of our tests was to create a file containing invalid SQL amongst the (valid and not applied) change scripts. The error was reported in the output window amongst nAnt exception reporting - making it quite hard to track down. When the error occurred, then data relating to the preceding SQL files in that Update batch will remain in the database but the subsequent files will not even be called - this leaves the database in a state of uncertainty. The table usd_AppliedDatabaseScript gets a row added for any previous files successfully completed, but the Version gets left as NULL. When I corrected the SQL and re-ran the process, the Version column gets updated to match the version number for the rest of the files successfully applied in the batch. This results in different date/time stamps on the rows, but the Version number being the same which makes me wonder if tracking the version history of the database is then subject to a certain amount of interpretation. In addition, selecting the action Create from the user interface when the database already exists results in an exception being raised - in my opinion this should be handled via some defensive SQL coding.

Note: All tests were carried out on a Windows XP laptop running a local installation of SQL Server 2005.

We have previously, also looked at DBVerse to meet our requirements. As neither tool satisfies what we're attempting to do, we'll continue writing our own but this was a worthwhile diversion as it really validates our thoughts on process vs program, and how that process should look.

Labels: database change management, ilp, SQLServer2005

// posted by Jane @ 6:56 PM   

Comments:

Post a Comment

It took a while, but we've finally reviewed our static code analysis tools and made a proposal to use FxCop.

Of the tools we evaluated there were 2 main contenders - FxCop and Submain CodeIt.Right which I've covered in more detail in other blog posts.

The other tools, NDepend, Visual Assist and Resharper, didn't get close enough to our criteria on a number of points. NDepend and Resharper look like valuable tools, but not under the mantle of what we wanted from static code analysis. VisualAssist was more of a tool to help whilst you type - adding to the intellisense and providing prompts for syntax violations.

Note: We don't run Visual Studio team edition so there is no built in code analysis tool

Labels: static code analysis

// posted by Jane @ 5:34 PM   

Comments:

Post a Comment

This is a tool I've used in the past with success, but which I found frustrating by the lack of integration into the IDE, instead relying on its own, slightly gawky interface.

Configuration

Rules can be switched off, switched on, and also custom rules can be added - read this tutorial for some useful hints and tips. Rules can also be supressed on a project by project basis, or globally by updating the FxCop.targets file - this will be really handy for how we want to set our chosen tool up.

Error detection

I left the rules as default, so they are based primarily on the Microsoft Design Guidelines. When run through the FxCop IDE a helpful link is provided to online documentation explaining the issue in more detail - for instance AssembliesShouldHaveValidStrongNames - and giving example fixes. The quality of errors returned were much better than CodeIt.Right, referring both to coding standards (naming of variables etc), but also performance and security improvements. There is no automagic correction meaning that developers learn from the warnings.

Automation

There is a command line version of the tool available which can be built into an automated build process. Additionally, this can be added as a post-build event outputting the results into the Warnings tab of the Error window in VS2005 and VS2008.

Labels: static code analysis

// posted by Jane @ 5:34 PM   

Comments:

> but which I found frustrating by the lack of integration into the IDE, instead relying on its own, slightly gawky interface.

FxCop is actually integrated into the IDE in the Team Developer and Team Suite SKUs, under the name Code Analysis.

# posted by  David Kean : 6:27 AM

 

Post a Comment

Initially this looked like it was going to be our best fit tool - it integrated into the IDE really well, it became an option on the tool menu and had a really simple interface for configuring and examing the rules.

Configuration

Configuring rules was simple on a project by project basis - not sure how easy it would be to configure on a wider basis - so that all the rules are shared across many solutions. It is theoretically possible to add a custom rule making use of an SDK and code editing - there is a tutorial on this.

Error detection

The errors returned are grouped by project, making filtering really easy. It even gives an ability to automagically (their word not mine) correct code errors and violations. When I tried this, my project didn't build any more, although I suspect this may have had something to do with dependency issues - any altered code through this process was commented, such as
  //ENCAPSULATE FIELD BY CODEIT.RIGHT
The errors returned for my test project didn't seem to be to as granular a level as I've experienced previously with FxCop - this was a concern.

Automation

There is a command line version available which can be incorporated into an automated build process.

Labels: static code analysis

// posted by Jane @ 5:13 PM   

Comments:

Post a Comment

I've been working on support for a while, and one of my tasks involved checking the set up of a windows scheduled task. I wanted to script out the scheduled tasks, so I could review them in an easier manner than having to use the GUI and navigating through them all.

The dos command schtasks holds the key. So, after opening a command prompt, the command

  schtasks /query

produces a list of all the scheduled tasks, including Task Name, Next Run Time and Status.
I wanted a bit more detail, specifically the path of what was scheduled, so I used the command

  schtasks /query /v /fo LIST

which produces a detailed list of all tasks. This includes additional information such as the task being run, schedule information etc
Finally, to get the output in a CSV format I used

  schtasks /query /v /fo CSV

This produces the same quantity of detailed information as LIST.

So, finally, by piping the output

  schtasks /query /v /fo CSV > tasks.csv

I get a tasks.csv file which I can open in Excel and review for correctness and completeness.

Labels: dos, nts

// posted by Jane @ 5:07 PM   

Comments:

Post a Comment