Jane's Technical Stuff
Archives
May 2002March 2003
May 2003
July 2003
August 2003
June 2005
July 2005
August 2005
December 2005
January 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
May 2008
June 2008
July 2008
August 2008
September 2008
Wednesday, August 20, 2008
Generating insert statements from table data - Updated
Yesterday Christian left me a comment containing an enhancement for the spu_generateinsert SQL. His suggestion was adding a new parameter @GenerateOneLinePerColumn to allow more "pretty" SQL to be produced - producing all the columns on separate lines which makes it easier for file comparisons to spot differences when comparing data values. I thought it made sense, so this morning I used an hour of my Madgex ILP time to make this change.
By calling EXEC spu_GenerateInsert @table = 'Detail',@GenerateOneLinePerColumn = 0 it produces
-- ** Start of Inserts
INSERT INTO [Detail] ([ID], [Age]) VALUES (1,36)
INSERT INTO [Detail] ([ID], [Age]) VALUES (2,40)
-- ** End of Inserts
but by calling EXEC spu_GenerateInsert @table = 'Detail',@GenerateOneLinePerColumn = 1 it produces
-- ** Start of Inserts
INSERT INTO [Detail]
(
[ID],
[Age]
)
VALUES
(
1,
36
)
INSERT INTO [Detail]
(
[ID],
[Age]
)
VALUES
(
2,
40
)
-- ** End of Inserts
Thanks to Christian for this suggestion. The updated script can be found here and related blog posts can be found here.
Note: this is now only working on SQL 2000 as during my testing I found that VARCHAR(8000) just wasn't long enough so I've made it VARCHAR(max). This is the only SQL 2005 specific piece of SQL in this procedure and I've put comments in the code to indicate -- change this to be (8000) for SQL Server 2000.
By calling EXEC spu_GenerateInsert @table = 'Detail',@GenerateOneLinePerColumn = 0 it produces
-- ** Start of Inserts
INSERT INTO [Detail] ([ID], [Age]) VALUES (1,36)
INSERT INTO [Detail] ([ID], [Age]) VALUES (2,40)
-- ** End of Inserts
but by calling EXEC spu_GenerateInsert @table = 'Detail',@GenerateOneLinePerColumn = 1 it produces
-- ** Start of Inserts
INSERT INTO [Detail]
(
[ID],
[Age]
)
VALUES
(
1,
36
)
INSERT INTO [Detail]
(
[ID],
[Age]
)
VALUES
(
2,
40
)
-- ** End of Inserts
Thanks to Christian for this suggestion. The updated script can be found here and related blog posts can be found here.
Note: this is now only working on SQL 2000 as during my testing I found that VARCHAR(8000) just wasn't long enough so I've made it VARCHAR(max). This is the only SQL 2005 specific piece of SQL in this procedure and I've put comments in the code to indicate -- change this to be (8000) for SQL Server 2000.
Labels: spu_generateinsert, SQLServer, SQLServer2005
// posted by Jane @ 6:36 PM 
Comments:
Thursday, August 14, 2008
TSQL: sp_executesql vs exec
There are 2 methods of executing dynamic SQL :
As you can see sp_executesql demands a parameter to be sent rather than just a string. This leads to improved security and improved performance because the SQL statement itself remains constant and only the parameter values change which means that the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.
In addition, when writing a parameterized query, the additional parameters can be specified separately which again adds to the security, see the following which is based on the example on MSDN:
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @TitleDefinition NVARCHAR(50)
/* Build the SQL string once.*/
SET @SQLString =N'SELECT * FROM ItemsWHERE Title like ''%'' + @title + ''%'''
SET @ParmDefinition = N'@title nvarchar(50)'
* Execute the string with the first parameter value. */
SET @TitleDefinition = 'S'
EXECUTE sp_executesql @SQLString, @ParmDefinition, @title = @TitleDefinition
/* Execute the same string with the second parameter value. */
SET @TitleDefinition = 'P'
EXECUTE sp_executesql @SQLString, @ParmDefinition, @title = @TitleDefinition
So, if you have an option, use sp_executesql instead of exec
- Exec which executes a scalar-valued, user-defined function, a system procedure, a user-defined stored procedure, or an extended stored procedure
- sp_executesql which executes a SQL statement or batch that can be reused many times, or that has been built dynamically
- Exec ('Select * from Items')
- DECLARE @strQuery AS NVARCHAR(50)
SET @strQuery = 'Select * from Items'
EXEC sp_executesql @strQuery
As you can see sp_executesql demands a parameter to be sent rather than just a string. This leads to improved security and improved performance because the SQL statement itself remains constant and only the parameter values change which means that the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.
In addition, when writing a parameterized query, the additional parameters can be specified separately which again adds to the security, see the following which is based on the example on MSDN:
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @TitleDefinition NVARCHAR(50)
/* Build the SQL string once.*/
SET @SQLString =N'SELECT * FROM ItemsWHERE Title like ''%'' + @title + ''%'''
SET @ParmDefinition = N'@title nvarchar(50)'
* Execute the string with the first parameter value. */
SET @TitleDefinition = 'S'
EXECUTE sp_executesql @SQLString, @ParmDefinition, @title = @TitleDefinition
/* Execute the same string with the second parameter value. */
SET @TitleDefinition = 'P'
EXECUTE sp_executesql @SQLString, @ParmDefinition, @title = @TitleDefinition
So, if you have an option, use sp_executesql instead of exec
Labels: SQLServer
// posted by Jane @ 5:19 PM
Comments:
Monday, June 30, 2008
Find a string in a stored procedure - Revisited
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 @%'
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:
Tuesday, June 24, 2008
SQL Server User Group Review
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):
Update: Simon has posted a follow-up containing the answers to questions he didn't get time to answer.
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.
- 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)
Update: Simon has posted a follow-up containing the answers to questions he didn't get time to answer.
// posted by Jane @ 6:55 PM
Comments:
Thursday, May 01, 2008
TSQL - How to get the date element of a datetime object
In SQL Server 2000 and 2005 there is no concept of a DATE data type, only a DATETIME. So, how do you get the DATE element only, i.e. with a 00:00 time element. One of my colleagues needed to do this, so after a bit of google searching he discovered the following.
-- Get today's date without the time element
SELECT CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())))
I would probably have done it via a CONVERT/CAST operation, converting to a VARCHAR and then back to a DATETIME, but this is a much more efficient method.
In SQL Server 2008 there is a DATE data type so this will no longer be an issue.
-- Get today's date without the time element
SELECT CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())))
I would probably have done it via a CONVERT/CAST operation, converting to a VARCHAR and then back to a DATETIME, but this is a much more efficient method.
In SQL Server 2008 there is a DATE data type so this will no longer be an issue.
// posted by Jane @ 5:38 PM
Comments:
Wednesday, April 30, 2008
And yet more about transactions
Further transaction work revolved around the investigation of naming transactions, and naming savepoints and understanding the differences. Again I worked with the theory of nested transactions to try and understand what was going on.
So, stage one - Labelling the two transactions and committing explicit transactions works fine and as expected
DELETE FROM Count -- outside of transaction - a known starting point
IF @@TRANCOUNT > 0
ROLLBACK TRAN -- in case there are any transactions still hanging around
BEGIN TRAN OuterTransaction
INSERT INTO COUNT VALUES (108)
BEGIN TRAN InnerTransaction
UPDATE COUNT SET Count = -108 WHERE Count = 108
COMMIT TRAN InnerTransaction -- can commit transaction InnerTransaction - result will be -108
IF @@TRANCOUNT > 0
COMMIT TRAN OuterTransaction
IF @@TRANCOUNT > 0
SELECT 'Transactions left hanging.'
SELECT * FROM COUNT
This results in one row, with the value -108 and no transactions left hanging.
However, if we don't explicitly commit the InnerTransaction, when we commit the OuterTransaction the InnerTransaction is left hanging, so the last SELECT @@TRANCOUNT returns 1 and not 0
DELETE FROM Count -- outside of transaction - a known starting point
IF @@TRANCOUNT > 0
ROLLBACK TRAN -- in case there are any transactions still hanging around
BEGIN TRAN OuterTransaction
INSERT INTO COUNT VALUES (108)
BEGIN TRAN InnerTransaction
UPDATE COUNT SET Count = -108 WHERE Count = 108
-- No inner commit / rollback
IF @@TRANCOUNT > 0
COMMIT TRAN OuterTransaction
IF @@TRANCOUNT > 0
SELECT 'Transactions left hanging.'
SELECT * FROM COUNT
this results in one row, with the value of -108, but with a transaction left hanging - as we only issued an explicit COMMIT TRAN against the OuterTransaction
so we would probably be better to have the OuterTransaction statement which is currently
IF @@TRANCOUNT > 0
COMMIT TRAN OuterTransaction
as
IF @@TRANCOUNT > 0
COMMIT TRAN
i.e. with no explicit reference to the transaction
Stage Two, attempting to rollback to a named transaction
DELETE FROM Count -- outside of transaction - a known starting point
IF @@TRANCOUNT > 0
ROLLBACK TRAN -- in case there are any transactions still hanging around
BEGIN TRAN OuterTransaction
INSERT INTO COUNT VALUES (108)
BEGIN TRAN InnerTransaction
UPDATE COUNT SET Count = -108 WHERE Count = 108
ROLLBACK TRAN InnerTransaction
IF @@TRANCOUNT > 0
COMMIT TRAN OuterTransaction
IF @@TRANCOUNT > 0
SELECT 'Transactions left hanging.'
SELECT * FROM COUNT
which results in the following error:
Msg 6401, Level 16, State 1, Line 77
Cannot roll back InnerTransaction. No transaction or savepoint of that name was found.
as well a single row, with the value of -108, and a transaction left open.
This behaviour confused me for quite a while until, after a bit of research and reading, I found the following definition for ROLLBACK TRAN [transaction_name]
transaction_name - Is the name assigned to the transaction on BEGIN TRANSACTION. transaction_name must conform to the rules for identifiers, but only the first 32 characters of the transaction name are used. When nesting transactions, transaction_name must be the name from the outermost BEGIN TRANSACTION statement.
So, instead, we can either rollback to the OuterTransaction explicitly
DELETE FROM Count -- outside of transaction - a known starting point
IF @@TRANCOUNT > 0
ROLLBACK TRAN -- in case there are any transactions still hanging around
BEGIN TRAN OuterTransaction
INSERT INTO COUNT VALUES (108)
BEGIN TRAN InnerTransaction
UPDATE COUNT SET Count = -108 WHERE Count = 108
ROLLBACK TRAN OuterTransaction
IF @@TRANCOUNT > 0
COMMIT TRAN OuterTransaction
IF @@TRANCOUNT > 0
SELECT 'Transactions left hanging.'
SELECT * FROM COUNT
which now results in no rows - because everything is rolled back in the inner statement - and no hanging transactions
or, in this case, the same effect can be gained from issuing a simple ROLLBACK TRAN :
DELETE FROM Count -- outside of transaction - a known starting point
IF @@TRANCOUNT > 0
ROLLBACK TRAN -- in case there are any transactions still hanging around
BEGIN TRAN OuterTransaction
INSERT INTO COUNT VALUES (108)
BEGIN TRAN InnerTransaction
UPDATE COUNT SET Count = -108 WHERE Count = 108
ROLLBACK TRAN
IF @@TRANCOUNT > 0
COMMIT TRAN OuterTransaction
IF @@TRANCOUNT > 0
SELECT 'Transactions left hanging.'
SELECT * FROM COUNT
which again results in no rows, and no hanging transactions
Step three, use a save point to restrict the amount of data to be rolled back instead of an inner transaction
DELETE FROM Count -- outside of transaction - a known starting point
IF @@TRANCOUNT > 0
ROLLBACK TRAN -- in case there are any transactions still hanging around
BEGIN TRAN OuterTransaction
INSERT INTO COUNT VALUES (108)
SAVE TRAN SavePoint
UPDATE COUNT SET Count = -108 WHERE Count = 108
ROLLBACK TRAN SavePoint
IF @@TRANCOUNT > 0
COMMIT TRAN
IF @@TRANCOUNT > 0
SELECT 'Transactions left hanging.'
SELECT * FROM COUNT
This results in one row, with a value of 108, showing that the UPDATE statement has been rolled back leaving just the results of the INSERT statement
So, stage one - Labelling the two transactions and committing explicit transactions works fine and as expected
DELETE FROM Count -- outside of transaction - a known starting point
IF @@TRANCOUNT > 0
ROLLBACK TRAN -- in case there are any transactions still hanging around
BEGIN TRAN OuterTransaction
INSERT INTO COUNT VALUES (108)
BEGIN TRAN InnerTransaction
UPDATE COUNT SET Count = -108 WHERE Count = 108
COMMIT TRAN InnerTransaction -- can commit transaction InnerTransaction - result will be -108
IF @@TRANCOUNT > 0
COMMIT TRAN OuterTransaction
IF @@TRANCOUNT > 0
SELECT 'Transactions left hanging.'
SELECT * FROM COUNT
This results in one row, with the value -108 and no transactions left hanging.
However, if we don't explicitly commit the InnerTransaction, when we commit the OuterTransaction the InnerTransaction is left hanging, so the last SELECT @@TRANCOUNT returns 1 and not 0
DELETE FROM Count -- outside of transaction - a known starting point
IF @@TRANCOUNT > 0
ROLLBACK TRAN -- in case there are any transactions still hanging around
BEGIN TRAN OuterTransaction
INSERT INTO COUNT VALUES (108)
BEGIN TRAN InnerTransaction
UPDATE COUNT SET Count = -108 WHERE Count = 108
-- No inner commit / rollback
IF @@TRANCOUNT > 0
COMMIT TRAN OuterTransaction
IF @@TRANCOUNT > 0
SELECT 'Transactions left hanging.'
SELECT * FROM COUNT
this results in one row, with the value of -108, but with a transaction left hanging - as we only issued an explicit COMMIT TRAN against the OuterTransaction
so we would probably be better to have the OuterTransaction statement which is currently
IF @@TRANCOUNT > 0
COMMIT TRAN OuterTransaction
as
IF @@TRANCOUNT > 0
COMMIT TRAN
i.e. with no explicit reference to the transaction
Stage Two, attempting to rollback to a named transaction
DELETE FROM Count -- outside of transaction - a known starting point
IF @@TRANCOUNT > 0
ROLLBACK TRAN -- in case there are any transactions still hanging around
BEGIN TRAN OuterTransaction
INSERT INTO COUNT VALUES (108)
BEGIN TRAN InnerTransaction
UPDATE COUNT SET Count = -108 WHERE Count = 108
ROLLBACK TRAN InnerTransaction
IF @@TRANCOUNT > 0
COMMIT TRAN OuterTransaction
IF @@TRANCOUNT > 0
SELECT 'Transactions left hanging.'
SELECT * FROM COUNT
which results in the following error:
Msg 6401, Level 16, State 1, Line 77
Cannot roll back InnerTransaction. No transaction or savepoint of that name was found.
as well a single row, with the value of -108, and a transaction left open.
This behaviour confused me for quite a while until, after a bit of research and reading, I found the following definition for ROLLBACK TRAN [transaction_name]
transaction_name - Is the name assigned to the transaction on BEGIN TRANSACTION. transaction_name must conform to the rules for identifiers, but only the first 32 characters of the transaction name are used. When nesting transactions, transaction_name must be the name from the outermost BEGIN TRANSACTION statement.
So, instead, we can either rollback to the OuterTransaction explicitly
DELETE FROM Count -- outside of transaction - a known starting point
IF @@TRANCOUNT > 0
ROLLBACK TRAN -- in case there are any transactions still hanging around
BEGIN TRAN OuterTransaction
INSERT INTO COUNT VALUES (108)
BEGIN TRAN InnerTransaction
UPDATE COUNT SET Count = -108 WHERE Count = 108
ROLLBACK TRAN OuterTransaction
IF @@TRANCOUNT > 0
COMMIT TRAN OuterTransaction
IF @@TRANCOUNT > 0
SELECT 'Transactions left hanging.'
SELECT * FROM COUNT
which now results in no rows - because everything is rolled back in the inner statement - and no hanging transactions
or, in this case, the same effect can be gained from issuing a simple ROLLBACK TRAN :
DELETE FROM Count -- outside of transaction - a known starting point
IF @@TRANCOUNT > 0
ROLLBACK TRAN -- in case there are any transactions still hanging around
BEGIN TRAN OuterTransaction
INSERT INTO COUNT VALUES (108)
BEGIN TRAN InnerTransaction
UPDATE COUNT SET Count = -108 WHERE Count = 108
ROLLBACK TRAN
IF @@TRANCOUNT > 0
COMMIT TRAN OuterTransaction
IF @@TRANCOUNT > 0
SELECT 'Transactions left hanging.'
SELECT * FROM COUNT
which again results in no rows, and no hanging transactions
Step three, use a save point to restrict the amount of data to be rolled back instead of an inner transaction
DELETE FROM Count -- outside of transaction - a known starting point
IF @@TRANCOUNT > 0
ROLLBACK TRAN -- in case there are any transactions still hanging around
BEGIN TRAN OuterTransaction
INSERT INTO COUNT VALUES (108)
SAVE TRAN SavePoint
UPDATE COUNT SET Count = -108 WHERE Count = 108
ROLLBACK TRAN SavePoint
IF @@TRANCOUNT > 0
COMMIT TRAN
IF @@TRANCOUNT > 0
SELECT 'Transactions left hanging.'
SELECT * FROM COUNT
This results in one row, with a value of 108, showing that the UPDATE statement has been rolled back leaving just the results of the INSERT statement
Labels: SQLServer
// posted by Jane @ 5:09 PM
Comments:
Friday, April 25, 2008
Database change management: DBVerse review
A few weeks ago I found a reference to DBVerse in The Morning Brew #58 which seemed like a great starting point for a problem I've had time and time again - keeping databases in sync from a structure perspective making sure that all the dependencies are in place and there is little or no risk when running these changes into a live server.
Bruce and I spent an hour or so today looking into this tool as part of our Madgex ILP time, and the following are our observations.
DBVerse is a C# project, which you install as a template on your machine. In my case, I copied the entire DbVerseDatabaseProjectTemplate.zip file, still as a zip, into my My Documents\Visual Studio 2005\Templates\ProjectTemplates folder.
To add a new DBVerse project to a solution, it is pretty simple, just a case of Adding a new project of type DbVerse Database Project. This will then create a new project, consisting of a DbVerse folder (which keeps all the required DLL and EXE files), a Scripts folder (in which any SQL scripts can be kept) and three cs files - Methods.cs, Methods.Initialize.cs and Methods.Updates.cs
The cs file we were most interested in was Methods.Updates.cs as that is our prime requirement - record and manage updates. In DBVerse updates can be scripted as SQL and put into the Scripts folder and then executed via
Db.ExecuteScript("UpdateJaneTestTable.sql");
or by using the DB objects that are exposed from the Lunaverse.DbVerse.Core such as Db.AddTable("JaneTest");
Db.Table("JaneTest").AddColumn("ID", DataType.Int);
Db.Table("JaneTest").AddColumn("Name", DataType.NVarChar(255));. I'm a big believer in scripting out changes in SQL, as these can be tested against the database and written in a none destructive manner (i.e. checking for existance of an object before updating it etc).
Each update method is given an attribute similar to
[Update(2, "Jane Dallaway", "25/04/2008")]
where 2 is the update number, "Jane Dallaway" is the author and "25/04/2008" is the date that the update was written.
The DBVerse project produces a dll, which is run via either a GUI or a console application. To run the updates via the console application I ran
Lunaverse.DbVerse.Console.exe ApplyAllUpdates
A new table is created - Database Updates - which records all the updates scripts which have run, storing the ID, Method Name, Author, Authored data and Application Date.
On subsequent runs I expected this table to be checked before any updates were applied, and to test this I created the following Update
[Update(3, "Jane Dallaway", "25/04/2008")]
public void InsertData()
{
Db.ExecuteScript(("InsertData.sql"));
}
I commented out all previous Updates to make it easy to check and ran
Lunaverse.DbVerse.Console.exe ApplyAllUpdates
The output was
Starting...
------------------------------------------------------------
Server=localhost Database=JaneTest Method count=-1
------------------------------------------------------------
Method starting [ApplyAllUpdates]
------------------------------------------------------------
Script [InsertData.sql] was run
Inserted row into table [DatabaseUpdates] ('InsertData', 'Jane Dallaway', '25/04/2008 00:00:00', '25/04/2008')
------------------------------------------------------------
Method finished [ApplyAllUpdates]
------------------------------------------------------------
Done: all methods completed
------------------------------------------------------------
When I checked the database I got the following output:
ID Name Description
1 Test Test via DBVerse
(1 row(s) affected)
so all as I expected.
I then ran the console application again, and got the following output
Starting...
------------------------------------------------------------
Server=localhost Database=NewDesignPartners Method count=-1
------------------------------------------------------------
Method starting [ApplyAllUpdates]
------------------------------------------------------------
Script [InsertData.sql] was run
------------------------------------------------------------
Method finished [ApplyAllUpdates]
------------------------------------------------------------
Done: all methods completed
------------------------------------------------------------
which has differing output to the previous time in the Script [InsertData.sql] line as last time we were notified what was inserted and this time we weren't.
On examining the database, the insert was run again resulting in the following rowsID Name Description
1 Test Test via DBVerse
1 Test Test via DBVerse
(2 row(s) affected)
This indicates that the check that the update hasn't already been run is causing an issue and cannot be relied upon. This invalidates the tool for our use. All of our other observations were minor - such as not being able to work out how to run all the updates in one go in the GUI, I could only seem to work out how to run one update at a time.
This project is a work in progress, and is probably worth keeping an eye on. Maybe I'll revisit it at a later stage, but I think it might be time to write something that does exactly what we need for the Madgex environment.
Bruce and I spent an hour or so today looking into this tool as part of our Madgex ILP time, and the following are our observations.
DBVerse is a C# project, which you install as a template on your machine. In my case, I copied the entire DbVerseDatabaseProjectTemplate.zip file, still as a zip, into my My Documents\Visual Studio 2005\Templates\ProjectTemplates folder.
To add a new DBVerse project to a solution, it is pretty simple, just a case of Adding a new project of type DbVerse Database Project. This will then create a new project, consisting of a DbVerse folder (which keeps all the required DLL and EXE files), a Scripts folder (in which any SQL scripts can be kept) and three cs files - Methods.cs, Methods.Initialize.cs and Methods.Updates.cs
The cs file we were most interested in was Methods.Updates.cs as that is our prime requirement - record and manage updates. In DBVerse updates can be scripted as SQL and put into the Scripts folder and then executed via
Db.ExecuteScript("UpdateJaneTestTable.sql");
or by using the DB objects that are exposed from the Lunaverse.DbVerse.Core such as Db.AddTable("JaneTest");
Db.Table("JaneTest").AddColumn("ID", DataType.Int);
Db.Table("JaneTest").AddColumn("Name", DataType.NVarChar(255));. I'm a big believer in scripting out changes in SQL, as these can be tested against the database and written in a none destructive manner (i.e. checking for existance of an object before updating it etc).
Each update method is given an attribute similar to
[Update(2, "Jane Dallaway", "25/04/2008")]
where 2 is the update number, "Jane Dallaway" is the author and "25/04/2008" is the date that the update was written.
The DBVerse project produces a dll, which is run via either a GUI or a console application. To run the updates via the console application I ran
Lunaverse.DbVerse.Console.exe ApplyAllUpdates
A new table is created - Database Updates - which records all the updates scripts which have run, storing the ID, Method Name, Author, Authored data and Application Date.
On subsequent runs I expected this table to be checked before any updates were applied, and to test this I created the following Update
[Update(3, "Jane Dallaway", "25/04/2008")]
public void InsertData()
{
Db.ExecuteScript(("InsertData.sql"));
}
I commented out all previous Updates to make it easy to check and ran
Lunaverse.DbVerse.Console.exe ApplyAllUpdates
The output was
Starting...
------------------------------------------------------------
Server=localhost Database=JaneTest Method count=-1
------------------------------------------------------------
Method starting [ApplyAllUpdates]
------------------------------------------------------------
Script [InsertData.sql] was run
Inserted row into table [DatabaseUpdates] ('InsertData', 'Jane Dallaway', '25/04/2008 00:00:00', '25/04/2008')
------------------------------------------------------------
Method finished [ApplyAllUpdates]
------------------------------------------------------------
Done: all methods completed
------------------------------------------------------------
When I checked the database I got the following output:
ID Name Description
1 Test Test via DBVerse
(1 row(s) affected)
so all as I expected.
I then ran the console application again, and got the following output
Starting...
------------------------------------------------------------
Server=localhost Database=NewDesignPartners Method count=-1
------------------------------------------------------------
Method starting [ApplyAllUpdates]
------------------------------------------------------------
Script [InsertData.sql] was run
------------------------------------------------------------
Method finished [ApplyAllUpdates]
------------------------------------------------------------
Done: all methods completed
------------------------------------------------------------
which has differing output to the previous time in the Script [InsertData.sql] line as last time we were notified what was inserted and this time we weren't.
On examining the database, the insert was run again resulting in the following rowsID Name Description
1 Test Test via DBVerse
1 Test Test via DBVerse
(2 row(s) affected)
This indicates that the check that the update hasn't already been run is causing an issue and cannot be relied upon. This invalidates the tool for our use. All of our other observations were minor - such as not being able to work out how to run all the updates in one go in the GUI, I could only seem to work out how to run one update at a time.
This project is a work in progress, and is probably worth keeping an eye on. Maybe I'll revisit it at a later stage, but I think it might be time to write something that does exactly what we need for the Madgex environment.
Labels: database change management, ilp, SQLServer
// posted by Jane @ 1:09 PM
Comments:
Friday, April 18, 2008
SQL Server User Group
Last night I went along to the SQL Server User Group meeting in Microsoft's London offices in Victoria (5 minutes walk from the station) to see what it was all about. The topics for the evening were:
• SQL Server 64 bit – advantages and choices
• Cursors – good or evil
The event was held at Microsoft London in one of the auditorium and was very professionally hosted.
It started at 6pm with an update on latest SQL Server related news – including the fact that SQL Server 2008 wouldn’t start on the 29th February 2008 - oops!
The next session was about SQL Server 64 bit, and when you should choose it – this was a bit too DBA focussed for me and was quite low level. But it was well presented and the presenter, Christian Bolton, obviously knew his stuff. His summary was that the question shouldn’t be "Why should I choose 64 bit?" but "Why wouldn’t I?".
There was then a break for pizza and drinks – no beer this time but apparently there normally is. I chatted to a few people and it would appear that LINQ has divided a community - with DBAs being suspicious of the load it was going to place on their databases without, necessarily, the ability to tune it as effectively as they feel they currently do. The developers liked the idea though.
The final session was about cursors and was presented by Eric Alsop – this talk’s summary was "Nothing divides the SQL community like a cursor, so why are they still around? In this session we are going to look at how to use cursors and when they are most applicable." This was an interesting talk, furnished with plenty of examples, to indicate at which points cursors will save you processing cost over using set based logic (mainly around aggregation, periodic optimisation (whatever that is) and matching issues.
All in all a good evening, and I'm planning to attend the one in June which is billed as a SQL Server 2008 UK Usergroup London Launch event. More details are available on the UK SQL Server Community website.
Update
I asked our Madgex DBA about his opinion on the 32 bit vs 64 bit question and his response was
"I think that the speaker was right to ask 'Why wouldn’t I?'. I think that the advantages for database applications far outweigh any additional cost. Considering that 64 bit is the future (or is that the present?), I think that rather than having to justify using a 64 bit system you would need to justify using a 32 bit system. Why would you want to use outdated technology? Obviously there is no point in upgrading to a 64 bit system just for the sake of it, but if your system has outgrown the capability of the current hardware and performance is suffering, or if the hardware has come to the end of it’s life and you were going to upgrade anyway, then I think that you would need a good reason not to upgrade to a 64 bit system.
The basic advantages are:
There is a good white paper that explains the issues."
• SQL Server 64 bit – advantages and choices
• Cursors – good or evil
The event was held at Microsoft London in one of the auditorium and was very professionally hosted.
It started at 6pm with an update on latest SQL Server related news – including the fact that SQL Server 2008 wouldn’t start on the 29th February 2008 - oops!
The next session was about SQL Server 64 bit, and when you should choose it – this was a bit too DBA focussed for me and was quite low level. But it was well presented and the presenter, Christian Bolton, obviously knew his stuff. His summary was that the question shouldn’t be "Why should I choose 64 bit?" but "Why wouldn’t I?".
There was then a break for pizza and drinks – no beer this time but apparently there normally is. I chatted to a few people and it would appear that LINQ has divided a community - with DBAs being suspicious of the load it was going to place on their databases without, necessarily, the ability to tune it as effectively as they feel they currently do. The developers liked the idea though.
The final session was about cursors and was presented by Eric Alsop – this talk’s summary was "Nothing divides the SQL community like a cursor, so why are they still around? In this session we are going to look at how to use cursors and when they are most applicable." This was an interesting talk, furnished with plenty of examples, to indicate at which points cursors will save you processing cost over using set based logic (mainly around aggregation, periodic optimisation (whatever that is) and matching issues.
All in all a good evening, and I'm planning to attend the one in June which is billed as a SQL Server 2008 UK Usergroup London Launch event. More details are available on the UK SQL Server Community website.
Update
I asked our Madgex DBA about his opinion on the 32 bit vs 64 bit question and his response was
"I think that the speaker was right to ask 'Why wouldn’t I?'. I think that the advantages for database applications far outweigh any additional cost. Considering that 64 bit is the future (or is that the present?), I think that rather than having to justify using a 64 bit system you would need to justify using a 32 bit system. Why would you want to use outdated technology? Obviously there is no point in upgrading to a 64 bit system just for the sake of it, but if your system has outgrown the capability of the current hardware and performance is suffering, or if the hardware has come to the end of it’s life and you were going to upgrade anyway, then I think that you would need a good reason not to upgrade to a 64 bit system.
The basic advantages are:
- Improved scalability through support of larger amounts of RAM and more processors.
- Improved performance through the ability to properly use all the available RAM (In a 32 bit system using AWE to increase the amount of memory, SQL only use the AWE memory for caching data pages. It cannot be used for other processes such as caching execution plans, sorting, hash tables, index creation etc.)
There is a good white paper that explains the issues."
// posted by Jane @ 10:02 AM
Comments:
Tuesday, January 15, 2008
More on spu_generateinsert
Dave posted a comment on the original post the other day seeking for help with resolving an error he was having running the script. The problem transpired to be with dealing with columns of data type image. On investigation, I discovered that there were quite a few column types which wouldn't work as they hadn't been catered for.
I've updated the script to work with all the data types I can get it to work with. For images, binary and varbinary columns, it attempts to insert NULL and puts a warning at the bottom of the generated script saying:
-- ** WARNING: There is an image column in your table which has not been migrated - this has been replaced with NULL. You will need to do this by hand. Images are not supported by this script at this time. or
-- ** WARNING: There is a binary or varbinary column in your table which has not been migrated - this has been replaced with NULL. You will need to do this by hand. Binary and VarBinary are not supported by this script at this time. as appropriate
This isn't great, but is at least informative and explains what has happened.
I will try and think about a way of scripting out data from columns of type image, binary and varbinary, but at least, in the meantime the script no longer errors (at least to my knowledge - if you know otherwise, then please let me know and I'll make more fixes).
Download updated spu_generateinsert.
I've updated the script to work with all the data types I can get it to work with. For images, binary and varbinary columns, it attempts to insert NULL and puts a warning at the bottom of the generated script saying:
-- ** WARNING: There is an image column in your table which has not been migrated - this has been replaced with NULL. You will need to do this by hand. Images are not supported by this script at this time. or
-- ** WARNING: There is a binary or varbinary column in your table which has not been migrated - this has been replaced with NULL. You will need to do this by hand. Binary and VarBinary are not supported by this script at this time. as appropriate
This isn't great, but is at least informative and explains what has happened.
I will try and think about a way of scripting out data from columns of type image, binary and varbinary, but at least, in the meantime the script no longer errors (at least to my knowledge - if you know otherwise, then please let me know and I'll make more fixes).
Download updated spu_generateinsert.
Labels: spu_generateinsert, SQL, SQLServer, SQLServer2005
// posted by Jane @ 6:32 PM
Comments:
Monday, January 07, 2008
Another update - Generate Insert statement from table for SQL Server
Bruce used my spu_generateinsert script today and identified a further issue, it didn't handle GUIDs properly. I fixed that and did a spot of further testing on one of my test databases and discovered a collation issue, so I fixed that as well whilst I was at it. The result is that it runs slower now, but specifies COLLATE database_default on each column to ensure that there are no "Cannot resolve collation conflict for column 1 in SELECT statement" errors raised.
As before, download it here and feel free to feed back any issues/improvements etc.
As before, download it here and feel free to feed back any issues/improvements etc.
Labels: spu_generateinsert, SQLServer, SQLServer2005
// posted by Jane @ 6:35 PM
Comments:
Thursday, January 03, 2008
Update - Generate Insert statement from table for SQL Server
I had a bit of spare time today, so I've updated spu_generateInsert.sql to fix a couple of annoyances:
I've tested this on SQL Server 2005 but I'd recommend testing it and checking it yourself before relying on it.
Download it here
- it now handles text and ntext appropriately - wrapping the data in single quotes, and escaping any single quotes as appropriate - it also displays a warning indicating that text data will be converted into varchar(8000)
- it now handles NULL values appropriately, and so the step in my previous post on this procedure about "replace of 'NULL' with NULL" can now be safely ignored.
I've tested this on SQL Server 2005 but I'd recommend testing it and checking it yourself before relying on it.
Download it here
Labels: spu_generateinsert, SQL, SQLServer, SQLServer2005
// posted by Jane @ 6:32 PM
Comments:
Tuesday, December 04, 2007
Getting the ID from a concatenated string
A colleague is doing some data migration work, and had a string he needed to manipulate to get at the id. The string had been put together as a title, with spaces replaced with -, and an ID with the title and ID separated by a -, i.e. Software-Developer-123 where title is Software Developer and the ID is 123.
He'd written some code to manipulate this, but it was taking quite a while and wanted to know if there was a one line TSQL statement to do it.
After a bit of head scratching we came up with the following:
DECLARE @strTemp VARCHAR(100)
SET @strTemp = 'Software-Developer-123'
SELECT SUBSTRING(@strTemp, LEN(@strTemp)-(CHARINDEX('-',REVERSE(@strTemp)))+2, CHARINDEX('-',REVERSE(@strTemp))-1)
This returns 123, and so is a (long) one line fix to his problem. As there is no LastIndexOf or similar function, I've used the REVERSE function to enable us to find the last instance of - and then its just some manipulation using CHARINDEX and SUBSTRING to get at the ID. The use of the variable @strTemp was just so we could easily swap in different test data and so isn't of any real importance to the solution.
I really enjoy challenges like this and have emailed this on as a brainteaser to the rest of the development team.
Update:
@DECLARE @strTemp VARCHAR(100)
SET @strTemp = 'Software-Developer-123'
SELECT RIGHT(@strTemp, CHARINDEX('-',REVERSE(@strTemp))-1)
A lot cleaner :-) Thanks Alex.
He'd written some code to manipulate this, but it was taking quite a while and wanted to know if there was a one line TSQL statement to do it.
After a bit of head scratching we came up with the following:
DECLARE @strTemp VARCHAR(100)
SET @strTemp = 'Software-Developer-123'
SELECT SUBSTRING(@strTemp, LEN(@strTemp)-(CHARINDEX('-',REVERSE(@strTemp)))+2, CHARINDEX('-',REVERSE(@strTemp))-1)
This returns 123, and so is a (long) one line fix to his problem. As there is no LastIndexOf or similar function, I've used the REVERSE function to enable us to find the last instance of - and then its just some manipulation using CHARINDEX and SUBSTRING to get at the ID. The use of the variable @strTemp was just so we could easily swap in different test data and so isn't of any real importance to the solution.
I really enjoy challenges like this and have emailed this on as a brainteaser to the rest of the development team.
Update:
@DECLARE @strTemp VARCHAR(100)
SET @strTemp = 'Software-Developer-123'
SELECT RIGHT(@strTemp, CHARINDEX('-',REVERSE(@strTemp))-1)
A lot cleaner :-) Thanks Alex.
Labels: SQL, SQLServer, SQLServer2005
// posted by Jane @ 5:33 PM
Comments:
Tuesday, November 13, 2007
Generate SQL Insert statement from table data
Years ago I stumbled upon a helpful SQL script (SQL Server 2000 and 2005) written by a chap called Keith E Kratochvil which you could point at a table in your database and which would then generate a series of Insert scripts.
I made some amendments at the time and over the next few years to make a bit more useful to me for some specific requirements, including the ability to take bits of a where clause to restrict the data coming back, allowing me to script subsets of data. I've carried this script with me from company to company, and decided it was time to share the updated script.
One word of advice, before running the generated inserts, do a replace of 'NULL' with NULL as I failed to get NULLs appended to a string without causing the whole string to become NULL.
The procedure is available here so feel free to take a look and use it if you find it helpful.
I made some amendments at the time and over the next few years to make a bit more useful to me for some specific requirements, including the ability to take bits of a where clause to restrict the data coming back, allowing me to script subsets of data. I've carried this script with me from company to company, and decided it was time to share the updated script.
One word of advice, before running the generated inserts, do a replace of 'NULL' with NULL as I failed to get NULLs appended to a string without causing the whole string to become NULL.
The procedure is available here so feel free to take a look and use it if you find it helpful.
Labels: spu_generateinsert, SQL, SQLServer, SQLServer2005
// posted by Jane @ 5:39 PM
Comments:
Hi Jane.
Ran the sp and got the following errors:
-- ** Start of Inserts
Msg 536, Level 16, State 5, Procedure sup_GenerateInsert, Line 151
Invalid length parameter passed to the SUBSTRING function.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FROM'.
-- ** End of Inserts
Probably "user error" but I would appreciate any insights you might be able to offer.
Thanks.
Ran the sp and got the following errors:
-- ** Start of Inserts
Msg 536, Level 16, State 5, Procedure sup_GenerateInsert, Line 151
Invalid length parameter passed to the SUBSTRING function.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FROM'.
-- ** End of Inserts
Probably "user error" but I would appreciate any insights you might be able to offer.
Thanks.
Hi Dave
You haven't left me an email address or any other way to contact you, and your blogger profile is switched off or similar, so I'm hoping you're going to head back here and take a look.
There is a section in the script which looks like the following:
SELECT @SelList = SUBSTRING(@SelList, 1,(len(@SelList)-1))
SELECT @SelList = @SelList + ' FROM ' + @table
--SELECT @SelList AS TheSelList
I recommend uncommenting the "SELECT @SelList AS TheSelList" line to help enable debugging.
I'd be interested in seeing what is being generated, and the structure of the table you're running it against if you have the time. I'll then try and sort out the problem and update the stored procedure and hopefully fix the problem. Drop me a line at jane(@)dallaway.com - without the ()s of course :-) with any information you have to help.
Thanks
Jane
You haven't left me an email address or any other way to contact you, and your blogger profile is switched off or similar, so I'm hoping you're going to head back here and take a look.
There is a section in the script which looks like the following:
SELECT @SelList = SUBSTRING(@SelList, 1,(len(@SelList)-1))
SELECT @SelList = @SelList + ' FROM ' + @table
--SELECT @SelList AS TheSelList
I recommend uncommenting the "SELECT @SelList AS TheSelList" line to help enable debugging.
I'd be interested in seeing what is being generated, and the structure of the table you're running it against if you have the time. I'll then try and sort out the problem and update the stored procedure and hopefully fix the problem. Drop me a line at jane(@)dallaway.com - without the ()s of course :-) with any information you have to help.
Thanks
Jane
Hi Jane,
A possible enhancement or improvement would be to use the UNION operator instead of Values. You would then have one SQL statement to execute instead of multiple. In addition, the script would generate much less text (only one INSERT INTO line) and make it easier to scan the data.
I modified the print out section of script to do this. I haven't tested it thoroughly, but it seems like a pretty straightforward modification. I removed one of the carriage returns so there's no space between UNION lines. Thanks for the script, it was helpful.
Jon Green - 4R Systems Inc.
Here's the code I modified.
--Cursor through the data to generate the INSERT statement / VALUES clause
DECLARE CR_Data CURSOR FAST_FORWARD FOR SELECT TableData FROM #TheData FOR
READ ONLY
OPEN CR_Data
FETCH NEXT FROM CR_Data INTO @TableData
if (@@fetch_status <> -1)
Print @InsertStmt + char(13) +
'SELECT ' + @TableData
FETCH NEXT FROM CR_Data INTO @TableData
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT 'UNION SELECT ' + @TableData
IF @generateGo = 1
BEGIN
PRINT 'GO'
END
END
FETCH NEXT FROM CR_Data INTO @TableData
END
CLOSE CR_Data
DEALLOCATE CR_Data
A possible enhancement or improvement would be to use the UNION operator instead of Values. You would then have one SQL statement to execute instead of multiple. In addition, the script would generate much less text (only one INSERT INTO line) and make it easier to scan the data.
I modified the print out section of script to do this. I haven't tested it thoroughly, but it seems like a pretty straightforward modification. I removed one of the carriage returns so there's no space between UNION lines. Thanks for the script, it was helpful.
Jon Green - 4R Systems Inc.
Here's the code I modified.
--Cursor through the data to generate the INSERT statement / VALUES clause
DECLARE CR_Data CURSOR FAST_FORWARD FOR SELECT TableData FROM #TheData FOR
READ ONLY
OPEN CR_Data
FETCH NEXT FROM CR_Data INTO @TableData
if (@@fetch_status <> -1)
Print @InsertStmt + char(13) +
'SELECT ' + @TableData
FETCH NEXT FROM CR_Data INTO @TableData
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT 'UNION SELECT ' + @TableData
IF @generateGo = 1
BEGIN
PRINT 'GO'
END
END
FETCH NEXT FROM CR_Data INTO @TableData
END
CLOSE CR_Data
DEALLOCATE CR_Data
# posted by 
: 5:38 PM
: 5:38 PM
Thanks for your suggestion Jon. I've implemented a change based on what you've suggested. I've blogged a reasonable description of what I've changed, and why - but basically I've added a new optional parameter @producesingleinsert to control whether multiple INSERT statements are generated, or a single statement using INSERT, SELECT and UNION SELECT.
New script available here
New script available here
Hi, very usefull script, I added one more parameter to it:
@GenerateOneLinePerColumn BIT = 0
If you want each columns being generated in a separate line, this is usefull to easily compare the generated file with a source control system.
I have all the code available if somebody wants it, most of it consist of statement like :
+ CASE WHEN @GenerateOneLinePerColumn = 1 THEN ' + CHAR(13)+' ELSE '' END +
@GenerateOneLinePerColumn BIT = 0
If you want each columns being generated in a separate line, this is usefull to easily compare the generated file with a source control system.
I have all the code available if somebody wants it, most of it consist of statement like :
+ CASE WHEN @GenerateOneLinePerColumn = 1 THEN ' + CHAR(13)+' ELSE '' END +
Wednesday, November 07, 2007
Copying a database on SQL Server 2005 using Backup and Restore
I always forget to do this, so this is a reminder for me. Hope it comes in handy for others too.
One of the things I do quite often, but obviously not enough, is to create a new database based on an existing one. I've tried various methods over the years, but backup and restore seems to work reliably and without too many headaches, and it doesn't involve taking the database offline either.
So, for instance to create a new database named JaneDallawayTest from and existing database called JaneTest I would do the following:
Backup JaneTest and store the resultant file somewhere I can get hold of it from
Then restore that backup to a new database named JaneDallawayTest by executing the following SQL:
RESTORE DATABASE [JaneDallawayTest]
FROM disk = N'JaneTestBackup.bak'
WITH FILE = 1 ,
move N'JaneTest'
TO N'G:\SQLData\JaneDallawayTest.mdf' ,
move N'JaneTest_log' TO N'G:\SQLData\JaneDallawayTest_log.ldf' ,
nounload , stats = 10 , replace
GO
All that is left to do is to update the logical name for the new database in the Database Properties dialog.
One of the things I do quite often, but obviously not enough, is to create a new database based on an existing one. I've tried various methods over the years, but backup and restore seems to work reliably and without too many headaches, and it doesn't involve taking the database offline either.
So, for instance to create a new database named JaneDallawayTest from and existing database called JaneTest I would do the following:
Backup JaneTest and store the resultant file somewhere I can get hold of it from
Then restore that backup to a new database named JaneDallawayTest by executing the following SQL:
RESTORE DATABASE [JaneDallawayTest]
FROM disk = N'JaneTestBackup.bak'
WITH FILE = 1 ,
move N'JaneTest'
TO N'G:\SQLData\JaneDallawayTest.mdf' ,
move N'JaneTest_log' TO N'G:\SQLData\JaneDallawayTest_log.ldf' ,
nounload , stats = 10 , replace
GO
All that is left to do is to update the logical name for the new database in the Database Properties dialog.
Labels: SQL, SQLServer, SQLServer2005
// posted by Jane @ 3:29 PM
Comments:
Saturday, October 13, 2007
SQLBits Podcast
The interview that Jim and I recorded with Craig Murphy is now up on his site.
The photo I took of Craig was truly awful and so didn't make it into the public domain. Sorry Craig!
The photo I took of Craig was truly awful and so didn't make it into the public domain. Sorry Craig!
Labels: conference, event, interview, SQL, SQLServer, SQLServer2005
// posted by Jane @ 11:14 PM
Comments:
Monday, October 08, 2007
SQLBits conference
On Saturday I attended the SQLBits conference held at Microsoft Campus in Reading. It was another free conference aimed at SQL Server developers and DBAs and consisting of 4 streams of 5 1-hour-long presentations. I teamed up with Jim, friend and former boss, and headed off to Reading ready for a day of learning.
SQL Server is probably my top skill, I've been working with it since version 7, and have designed databases, developed them, tested them and supported them. So, this conference really had my name written all over it. And it didn't disappoint.
I started off with Transactions and Exception Handling, presented by Eric Allsopp which was an in-depth exploration of the locking mechanics within SQL Server, isolation levels as well as exception handling and the advantages of the SQL Server 2005 BEGIN TRY... BEGIN CATCH syntax over the old @@ERROR syntax. Eric obviously knew his stuff, but I have to admit that following a highly technical (in fact, the most technical session I attended) presentation at 9:30 on a Saturday morning was a bit of a struggle.
Next up we headed to the SQL Server 2008 Beyond Relational presentation by Keith Burns and found out about some the cool new features to be presented in SQL Server 2008 - the most interesting being spatial - using both geography and geometry aspects to allow for manipulation of location based data. A great presentation giving a glance into the new version - not that I've got to grips with SQL Server 2005 yet...
The final presentation of the morning was Simon Sabin with 77 SQL Server Myths (although we only got through about 16 or so). This was a really useful session, challenging some of the beliefs I've held over the years (mainly that were true at one point, but weren't any longer) and making me re-evaluate some of the queries I write.
During lunch there were a couple of talks from sponsors, and some Grok talks going on. I managed to attend neither but did do an interview about the day so far with Craig Murphy.
After lunch we headed to Dave McMahon's talk Daves Top 10 SQL Keywords which was truly excellent. He went through his 10 favourite keywords, and explained, giving examples, why they were in the list, with a final countdown in true Top of the Pops style.
We missed the final session altogether as the 2 sessions we'd identified as being useful had either changed, or after discussion turned out to be not what we expected, so we headed off home. A great day, and really refreshing to have a SQL based day like this. Thanks SQLBits.
SQL Server is probably my top skill, I've been working with it since version 7, and have designed databases, developed them, tested them and supported them. So, this conference really had my name written all over it. And it didn't disappoint.
I started off with Transactions and Exception Handling, presented by Eric Allsopp which was an in-depth exploration of the locking mechanics within SQL Server, isolation levels as well as exception handling and the advantages of the SQL Server 2005 BEGIN TRY... BEGIN CATCH syntax over the old @@ERROR syntax. Eric obviously knew his stuff, but I have to admit that following a highly technical (in fact, the most technical session I attended) presentation at 9:30 on a Saturday morning was a bit of a struggle.
Next up we headed to the SQL Server 2008 Beyond Relational presentation by Keith Burns and found out about some the cool new features to be presented in SQL Server 2008 - the most interesting being spatial - using both geography and geometry aspects to allow for manipulation of location based data. A great presentation giving a glance into the new version - not that I've got to grips with SQL Server 2005 yet...
The final presentation of the morning was Simon Sabin with 77 SQL Server Myths (although we only got through about 16 or so). This was a really useful session, challenging some of the beliefs I've held over the years (mainly that were true at one point, but weren't any longer) and making me re-evaluate some of the queries I write.
During lunch there were a couple of talks from sponsors, and some Grok talks going on. I managed to attend neither but did do an interview about the day so far with Craig Murphy.
After lunch we headed to Dave McMahon's talk Daves Top 10 SQL Keywords which was truly excellent. He went through his 10 favourite keywords, and explained, giving examples, why they were in the list, with a final countdown in true Top of the Pops style.
We missed the final session altogether as the 2 sessions we'd identified as being useful had either changed, or after discussion turned out to be not what we expected, so we headed off home. A great day, and really refreshing to have a SQL based day like this. Thanks SQLBits.
Labels: conference, event, SQL, SQLServer, SQLServer2005
// posted by Jane @ 7:05 PM
Comments:
Tuesday, August 14, 2007
TSQL: Get random rows
We needed to return a random subset of rows from a database, and someone pointed out the newid() function.
SELECT *
FROM Company
ORDER BY newid()
For the application concerned it works well, but as it says in Random Sampling in T-SQL "However, it's evaluated by the operating system, not T-SQL. In addition, while unique identifiers can be ordered, the comparison functions aren't documented. Unlike Rand(), NewID() has not been designed for use as a statistically valid random number generator." so it obviously must be used with caution.
SELECT *
FROM Company
ORDER BY newid()
For the application concerned it works well, but as it says in Random Sampling in T-SQL "However, it's evaluated by the operating system, not T-SQL. In addition, while unique identifiers can be ordered, the comparison functions aren't documented. Unlike Rand(), NewID() has not been designed for use as a statistically valid random number generator." so it obviously must be used with caution.
Labels: SQLServer
// posted by Jane @ 4:30 PM
Comments:
Wednesday, June 20, 2007
Can't logon to SQL Server 2000/2005 after password change
We have a strict password change policy here, and having changed my password yesterday I could no longer start either of my two local instances of SQL Server.
On an attempted start I got an error message:
"The MSSQLSERVER service was unable to log on as with the currently configured password due to the following error:
Logon failure: unknown user name or bad password." error.
To resolve this I headed to Control Panel -> Services and selected MSSQLSERVER. I right clicked, selected Properties and opened the Log On tab. I updated the password and confirm password and then skipped back onto the General tab and started the service. It worked :-). I did the same with SQL Server (SQL2005), also successfully.
On an attempted start I got an error message:
"The MSSQLSERVER service was unable to log on as
Logon failure: unknown user name or bad password." error.
To resolve this I headed to Control Panel -> Services and selected MSSQLSERVER. I right clicked, selected Properties and opened the Log On tab. I updated the password and confirm password and then skipped back onto the General tab and started the service. It worked :-). I did the same with SQL Server (SQL2005), also successfully.
Labels: SQLServer, SQLServer2005
// posted by Jane @ 10:05 AM
Comments:
Thursday, February 01, 2007
.NET and SQL Server - retrieving blobs
The database for my current project stores pdf documents as images within the database. I needed to write a quick rough and ready program to retrieve all pdfs which meet a certain set of criteria.
The main key is remembering that a binary, or image comes back as an array of bytes. This enables us to make use of System.IO.File.WriteAllBytes which takes in a string parameter filePath representing the path to the file, and a byte[] parameter representing the file.
I set up a data set, and defined a TableAdapter MyQueryTableAdapter to have a parameterised query as MyQuery to extract the relevant fields, and then used:
MyProjectTableAdapters.MyQueryTableAdapter tableAdapter = new MyProject.MyProjectTableAdapters.MyQueryTableAdapter();
MyProject.MyQueryDataTable table;
to define table and tableAdapter variables.
These were then used to populate the table with data based upon myParameter.
table = tableAdapter.GetData(myParameter);
All I did then was loop through the table.Rows in the table and populate a byte[] myPdfDocument variable for each row. This myPdfDocument was then written using the above code.
Job done.
The main key is remembering that a binary, or image comes back as an array of bytes. This enables us to make use of System.IO.File.WriteAllBytes which takes in a string parameter filePath representing the path to the file, and a byte[] parameter representing the file.
I set up a data set, and defined a TableAdapter MyQueryTableAdapter to have a parameterised query as MyQuery to extract the relevant fields, and then used:
MyProjectTableAdapters.MyQueryTableAdapter tableAdapter = new MyProject.MyProjectTableAdapters.MyQueryTableAdapter();
MyProject.MyQueryDataTable table;
to define table and tableAdapter variables.
These were then used to populate the table with data based upon myParameter.
table = tableAdapter.GetData(myParameter);
All I did then was loop through the table.Rows in the table and populate a byte[] myPdfDocument variable for each row. This myPdfDocument was then written using the above code.
Job done.
Labels: .NET, Development, SQLServer
// posted by Jane @ 3:33 PM
Comments:
Wednesday, August 02, 2006
TSQL: LEN vs DATALENGTH
I encountered a TSQL issue this morning that I knew looked familiar. When doing SELECT LEN(@strName) it was returning less characters than I expected. So, when doing SELECT LEN(' ') I got a result of 0 instead of 1, when I did SELECT LEN('Jane ') I got 4 rather than 5. I'd forgotten about the fact that it trims off white space before returning data. Thanks to Adam Machanic I refound DATALENGTH which does what I'd expect.
LEN defined in SQL Server Books online as Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.
DATALENGTH defined as Returns the number of bytes used to represent any expression. DATALENGTH is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data. The DATALENGTH of NULL is NULL.
LEN defined in SQL Server Books online as Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.
DATALENGTH defined as Returns the number of bytes used to represent any expression. DATALENGTH is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data. The DATALENGTH of NULL is NULL.
Labels: Development, SQLServer, SQLServer2000
// posted by Jane @ 3:04 PM
Comments:
Thursday, July 20, 2006
SQL Server 2000 - find a string in a stored procedure
I've been debugging and unit testing some involved stored procedures, and I've left a trail of PRINT statements all over the place. I was having real difficulties tracking the last 2 down, so I wrote the following SQL to help me track them down.
SELECT o.name
FROM syscomments c
INNER JOIN sysobjects o
ON c.id = o.id
WHERE o.xtype = 'P'
AND text LIKE 'PRINT @'
SELECT o.name
FROM syscomments c
INNER JOIN sysobjects o
ON c.id = o.id
WHERE o.xtype = 'P'
AND text LIKE 'PRINT @'
Labels: Development, SQLServer, SQLServer2000
// posted by Jane @ 10:28 AM
Comments:
Thursday, July 13, 2006
SQL Server Collations
Tony Rogerson has an interesting article about Collations in SQL Server, examples and restrictions, explaining how and why they happen and what they mean. This is a problem I've faced again and again, especially when bringing back customer's data. One of the slight annoyances I have with Visual Studio 2003 is that when generating create scripts for tables it puts the collation into the table creation scripts, so I often do a search of the database for COLLATE and remove them, taking the code from
CREATE TABLE [dbo].[Person]
( [PersonID] [int] NOT NULL ,
[Name] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
to
CREATE TABLE [dbo].[Person]
( [PersonID] [int] NOT NULL ,
[Name] [varchar] (10) NOT NULL
) ON [PRIMARY]
We let our databases take the default collation from the server, rather than forcing our own. This can still lead to issues and I've had the
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
error when doing casts rather than joins. To get around this I've used COLLATE database_default to fix it, for example
SELECT @strName = "THE" + ISNULL(CAST(@intID AS VARCHAR(10)),'NULL') COLLATE database_default
CREATE TABLE [dbo].[Person]
( [PersonID] [int] NOT NULL ,
[Name] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
to
CREATE TABLE [dbo].[Person]
( [PersonID] [int] NOT NULL ,
[Name] [varchar] (10) NOT NULL
) ON [PRIMARY]
We let our databases take the default collation from the server, rather than forcing our own. This can still lead to issues and I've had the
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
error when doing casts rather than joins. To get around this I've used COLLATE database_default to fix it, for example
SELECT @strName = "THE" + ISNULL(CAST(@intID AS VARCHAR(10)),'NULL') COLLATE database_default
Labels: Development, SQLServer, SQLServer2000
// posted by Jane @ 11:09 AM
Comments:
Tuesday, May 23, 2006
Is the database in single user mode?
One of the products I help to look after has recently gained its own upgrade process - so you put in the CD and "stuff just happens". As part of this the database gets upgraded, and whilst doing so it is put into single user mode. Unfortunately, one customer has had a problem, and the upgrade has failed. We thought it might be in single user mode, but weren't convinced, so to check we ran
sp_dboption [databasename], 'single user'
which resulted in
OptionName CurrentSetting
----------- --------------
single user on
to change this we simple issued
sp_dboption [databasename], 'single user', false
and checked again and this time it said
OptionName CurrentSetting
----------- --------------
single user off
Success! Now to fix the process...
sp_dboption [databasename], 'single user'
which resulted in
OptionName CurrentSetting
----------- --------------
single user on
to change this we simple issued
sp_dboption [databasename], 'single user', false
and checked again and this time it said
OptionName CurrentSetting
----------- --------------
single user off
Success! Now to fix the process...
Labe