T-SQL

T-SQL Tuesday 30 – DBA Ethics

This month’s edition is being hosted by Chris Shaw ( twitter | blog ). This month’s theme is about Ethics. Part of Chris’s posted a few thoughts on the subject and some additional sub-topics. Based on one of those I am throwing my hat into this challenge.

Does a Code of Ethics mean anything to anyone? How do we as a community enforce a Code of Ethics?

“A person who is fundamentally honest doesn’t need a code of ethics. The Ten Commandments and the Sermon on the Mount are all the ethical code anybody needs.” — Harry S. Truman

I agree with Mr. Truman and yes a Code of Ethics does mean something to me. I like to think that an honest person with good values and common sense to tell the difference between right and wrong. I follow the rules of better judgement and usually error on the side of caution.

I am sure there are a lot of others in the SQL Community who would agree with Mr Truman. I don’t think you’ll have a trouble finding the people who do. Where you will be trouble will be on enforcing the Code. If we did have a Code, would we need to have a governing body? Here is an idea. We could model it after the Code of the Brethren set down Morgan and Bartholomew. Then that only begs the question, “Would they be actual rules or more of a guidelines?*

Obviously I am only joking.

What I am not joking about is whether we have a code of Ethics or not people will be people and you either have ethics or you don’t. It is just that simple. Just like the saying goes, “Locks only keep honest people honest” and a Code of Ethics will only apply to those that already have ethics. Or believe having a code is important for the overall good of the order SQL Community.

Let me share with you some quotes that I find inspirational and fit with how I feel about Honesty and Integrity and  Ethics.

“Character is what you do when no one is watching” — JC Watts

“Always do right. This will gratify some people and astonish the rest.” — Mark Twain

“To be persuasive we must be believable; to be believable we must be credible; credible we must be truthful.” — Edward R. Murrow

So to sum things up I guess I am on the fence about having a Code of Ethics. If we had one I would commit to following it as I do today without having one. If it would help the community over all then I would be for it. Just remembering that oversight no matter how small can be cumbersome to the natural order of things.

I want to thank Chris for hosting this month and allowing me to share my thoughts on this subject. I look forward to reading other posts in the coming days.

Cheers!

Quotes were taking from the following site:

*Pirates of the Caribbean: Curse of the Black Pearl

http://www.leadershipnow.com/

Advertisements

WAITFOR (T-SQL)

I don’t write a lot of code. Most of what I do write is little pieces here and there that make my job as an Operational DBA easier. After all I do my very best to not work hard. I try and work smarter. This post falls right into those lines.

I have a few scripts I use when setting up and configuring a new SQL Server. One of the little scripts runs all the SQL Agent Jobs after setup to confirm they all work and should they fail to fix them before I confirm the Change Orders are all complete and the server is setup.

Code
After running all the scripts to create the Jobs this is the last one that runs.

EXEC MSDB.dbo.sp_start_job @Job_Name = ‘AuditFailedLogins’
go
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘CheckDB’
go
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘CleanHistory’
go
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘DailyMonitor’
go
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘DefrageIndexes’
go
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘RemoveOldBU files’
go
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘SysDB’
go
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘UserDBFull’
go
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘trackDBGrowth’
go
EXEC MSDB.dbo.sp_start_job @Job_Name = ‘UserDBLog’
go

So, What’s The Problem?

Well, I am glad you asked. The problem is/was the Job “UserDBLog” backs up the transaction log and this error is produced.

“BACKUP failed to complete the command BACKUP LOG db_stats. Check the backup application log for detailed messages.”

When checking the job history I found the following error.

“Executed as user: user_name. BACKUP LOG cannot be performed because there is no current database backup. [SQLSTATE 42000] (Error 4214)  BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.”

According to this error the database has never been backed up. Actually, that is somewhat correct. As part of my setup and testing I create a small database. In the script above the “UserDBFull” job backs up the database. What is happening is the “UserLog” job is firing before the user database backup has completed.

Wait For It

To fix this minor issue I am just going to add the following line.

WAITFOR DELAY ’00:00:05′;

This will delay the firing of the log backup long enough (5 seconds) for the database backup to complete. Make sure you understand and read more about this little snippet of code before using it. You can read more about the WATIFOR code here: http://msdn.microsoft.com/en-us/library/ms187331.aspx

Conclusion

There are many things within SQL Server to discover. Only yesterday I used the WAITFOR for the first time. Look around, you might just be surprised by what you find.

Cheers!

Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.

When reviewing my daily monitoring information this morning I noticed I did not get any emails from three different servers for one of my projects. You see, we have scripts we use to look at backups and when they were last performed, we look at job failure, we look at, well you get the picture. We monitor our servers.

I started to investigate the issue. Before I looked at the job history to see if there were any errors I looked on the server to see if the backs had actually been done, regardless of what the results I was reviewing. Yes. The databases and logs were/are being done.

When I looked at the history for the monitor Job the following error message is what I saw.

Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050).
The step failed.

I also noticed the job had been failing the last two days. (I was off the last 2 days so I didn’t notice it before today, just in case you were asking why it took so long to discover)

I ran the code in the query window and it executed correctly. Hmm. What has changed since the errors started? “Light bulb!”

I added domain accounts last week to run the services. OK, what could be wrong then?
All the services are running. No errors there. What else? Looking at the error message again the job was executed under the domain account I added, so that is right. How about permissions? Ah ha! Permissions. I forgot to add the SQL Agent domain account to SQL Server and assign the proper permissions. Fixing that by adding the domain account to the server and granting admin privilages worked.

So, hopefully, you learned a valuable lesson from my mistake.

Cheers!

Using LIKE clause in Stored Procedures

I was working on a stored procedure today and had a complete brain fart as it relates to using the like clause in a query. Let me clarify a little more. I know in a simple query when trying to use a LIKE clause you most likely will use the wild card characters like this;

SELECT
Timestamp
, Message
, CategoryName
, MachineName
, ProcessName
FROM dbo.LogDB
WHERE ProcessName LIKE ‘%ProcessName%’

But I could not, for the life of me remember about using this in a stored procedure.
Like so;

ALTER PROCEDURE dbo.getLogs
@ProcessName NVARCHAR(512)

AS

BEGIN

SELECT
l.Timestamp AS ‘Timestamp’
, l.[Message] AS ‘Message’
, c.[CategoryName] AS ‘Category’
, l.[MachineName] AS ‘Machine’
, l.[ProcessName] AS ‘Process Name’
FROM [dbo].[Log] l
INNER JOIN [dbo].[CategoryLog] cl ON cl.LogID = l.LogID
INNER JOIN [dbo].[Category] c ON c.CategoryID = cl.CategoryID
WHERE l.ProcessName LIKE ‘%@ProcessName%’

Luckily, I copies of a lot of code I have done over the years and it only took a few minutes to look through my “bag of tricks” and find the solution. Here is the correct way.

ALTER PROCEDURE dbo.getLogs
@ProcessName NVARCHAR(512)

AS

BEGIN

SELECT
l.Timestamp AS ‘Timestamp’
, l.[Message] AS ‘Message’
, c.[CategoryName] AS ‘Category’
, l.[MachineName] AS ‘Machine’
, l.[ProcessName] AS ‘Process Name’
FROM [dbo].[Log] l
INNER JOIN [dbo].[CategoryLog] cl ON cl.LogID = l.LogID
INNER JOIN [dbo].[Category] c ON c.CategoryID = cl.CategoryID
WHERE l.ProcessName LIKE ‘%’ + @ProcessName + ‘%’

Notice the difference? When working within a stored procedure you must add in the additional code of;
‘%’ + @ProcessName + ‘% after the LIKE clause. Not too complicated.

Hope this helps.

Cheers!

Using CHARINDEX to clean up bad data

Recently I had a small import task as part of a larger project. I needed to import several different Excel Spreadsheets with customer and vendor data. Over all the process was pretty straight forward.

As with most imports from “other” sources they don’t give you good data. There are several reasons this happens but it is also outside the scope of this article, perhaps I will add a post about that in the future.

The issues I had with the data were with the first name and middle initial columns. They were in the same field and to make things a bit more complicated some of the first names had middle initials but not all.

I think it is important to note that I could have done this within the import process, but as with most solutions, there is usually more than one way to accomplish the end result. This is how I solved the problem.

<code>
SELECT
CASE WHEN CHARINDEX(‘ ‘, c.TFirstName) = 0 THEN SUBSTRING(c.TFirstName, 1, LEN(c.TFirstName))
ELSE SUBSTRING(c.TFirstName, 1, (CHARINDEX(‘ ‘, c.TFirstName) -1))
END AS FirstName
, CASE
WHEN CHARINDEX(‘ ‘, c.TFirstName) > 0 THEN (SUBSTRING(c.TFirstName, CHARINDEX(‘ ‘, c.TFirstName), CHARINDEX(‘ ‘, c.TFirstName)))
END AS MiddleInitial
, c.TLastName AS LastName
FROM dbo._stagingCustomer c
</code>

Using the CHARINDEX (you can find detailed information on the usage of CHARINDEX here: http://tinyurl.com/yzjsbg6)

Firstname: What I am doing here is looking for the first occurrence of a  “space” in between the first name and middle initial. If it is equal to zero then it means the first name does not have a middle initial. So I am getting the first name by using the SUBSTRING function starting at the first character in the string and going the length of the string by using the LEN function.

If it is not equal to zero then grab the first name from the field, again using the SUBSTRING function starting at the first character but then using the CHARINDEX function to find the location of the first occurrence of the space and subtracting 1 from it to get the first name.

MiddleInitial: Here I am checking to see if the CHARINDEX is greater than zero. If it is greater than zero, using the SUBSTRING function and starting at the first occurrence of the space by using the CHARINDEX function by the CHARINDEX returned.

LastName: I just out put the last name column since there were no issues with this column.

Let me know if you have questions.

Cheers!

Missing the tree in spite of the forest

Here is a great example of looking at code too long. Taking a break and coming back to look at it again from a different angle. We are porting over an Oracle database to SQL Server. We are using the SQL Server Migration Assistant (nice tool, I’ll write another post about it later) and need to test the functionality designed in Oracle that works differently in SQL Server. Say it ain’t so. 🙂

We have a few more tables than this. I am just scaling this down to a few tables for example sake.

I have two tables. Table one “Site”. Table two “Customer”.
I am using Site.Id as the foreign key in Customer. I added data into my site table with a simple insert to test things out. Note the witty insert data as well. (cough)

INSERT INTO SITE
VALUES(
NEWID()
, ‘Test SQL Site’
, ‘Description of Test SQL Site’
, ‘Tester_001’
, GETDATE()
)
GO

I then tried to use this code to insert data into the customer table. Using one of our stored procedures. They were designed as CRUD methods on the Oracle side and again handled a bit differently on the SQL Server side.

DECLARE @RC int
DECLARE @P_ID uniqueidentifier
DECLARE @P_CUSTOMER_CODE nvarchar(max)
DECLARE @P_NAME nvarchar(max)
DECLARE @P_SITE_ID uniqueidentifier
DECLARE @P_CREATED_BY nvarchar(max)

set @P_ID = NULL –(SELECT top 1 id from matms.CUSTOMERS)
set @P_CUSTOMER_CODE = ‘Test Customer Code’
set @P_NAME = ‘Testing Customer Name’
set @P_SITE_ID = (select top 1 id from [matms].[SITE])
set @P_CREATED_BY = ‘Tester_001’

EXECUTE @RC = [matms].[matms].[CUSTOMERS_PKG$SAVE]
@P_ID OUTPUT
,@P_CUSTOMER_CODE
,@P_NAME
,@P_SITE_ID
,@P_CREATED_BY

I was getting this error:
Msg 515, Level 16, State 2, Procedure CUSTOMERS_PKG$SAVE, Line 28
Cannot insert the value NULL into column ‘ORGANIZATION_ID’, table ‘CUSTOMERS’;
column does not allow nulls. INSERT fails.
The statement has been terminated.

What? I just manually inserted the data. I did a quick select to confirm, I was not insane. (the jury is still out on the insane part)

SELECT * FROM SITE
go

Data returned! Site.Id was there. What is going on here?

I don’t usually use uniqueidentifiers for primary keys. So, in my ignorance I thought maybe, just maybe I was doing something wrong. I looked up the use of Uniqueidentifiers on google and read the information on its use on MSDN.
http://tinyurl.com/yepr8yl

Seems I am not doing anything against the norm or wrong. So, even though the fields are both Uniqueidentifier data types I had to be doing something wrong on the inserts. After all I just confirmed the data was there. I tried explicitly converting to different data types. Still the same error because SQL Server, depending on the data type you are converting is implicitly converts the data types. http://tinyurl.com/d3vvgv

Well, after banging my head against the wall, I moved on to another project to come back to it after lunch.
When I looked at it again. There was the problem! Plain as day!

Anyone see the problem?

I was working on the dev server and calling the stored procedure on the production (or will be) server that had no data in it! What a dumbass I am. At least I caught it before someone else did. I even posted the code on a few forums to try and get help. 30 + people viewed it and no one caught it.

T-SQL Tuesday #002: A Puzzling Situation – The package failed to load due to error 0xC0011008

Background: While working on a small SQL Server Integration Service project I ran into this problem.

I first developed the packages locally on my laptop using 2008 versions of SQL Server and SSIS. After creating two separate packages, one for the import and one to clean out the tables as I was testing the import successfully I knew I was going to have to update some the components in the package after moving it to the remove development box used by the dev team.

After copying the files from my laptop to the remote server, running them, making a few changes here and there and getting them to work like they were designed I reported in our morning standup meeting I was 95% complete. I just need to document the information for turnover and wanted to confirm all was still working. When this error popped up after another change and tried to redeploy.

The package failed to load due to error 0xC0011008 “Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.”. This occurs when CPackage::LoadFromXML fails.

<post picture />

I was confused. What change from yesterday to today?

My code had not changed except for the minor update I just performed. So, I reverted the change and tried to deploy again. Same error. I then went to Google to see what information was out on the web for this error. Surely someone had run into this issue before. After finding several posts in forums by others dating as far back as 2005 I did not find anything to indicate what was wrong or what else to investigate. I sent a Tweet to some SSIS people and to the #ssis list. I also posted the error in several forums still thinking someone has run into this before.

On Tweet mentioned the Security Protection level. It was set to the default of EncryptSensutiveWithUserKey. I had not changed that, (at least not that I remembered). So I changed it to DontSaveSensitive just to see if somehow I had changed it in my sleep. Still no love.

Two people respond with this link;

http://tinyurl.com/yazwg58

No love here. I was working remotely but that was the only similarity.

For some reason I tried to open the dtsx file in the Deployment folder. After all I had nothing to lose. That is when I got these errors;

Error 1: Error loading CleanStagingTables.dtsx: The version number in the package is not valid. The version number cannot be greater than current version number.

Error 2: Error loading CleanStagingTables.dtsx: Package migration from version 3 to version 2 failed with error 0xC001700A “The version number in the package is not valid. The version number cannot be greater than current version number.”.

Error 3: Error loading CleanStagingTables.dtsx: Error loading value “<DTS:Property xmlns:DTS=”www.microsoft.com/SqlServer/Dts” DTS:Name=”PackageFormatVersion”>3</DTS:Property>” from node “DTS:Property”.

Error 4: Error loading ‘CleanStagingTables.dtsx’ : The package failed to load due to error 0xC0010014 “One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.”. This occurs when CPackage::LoadFromXML fails.

Aha! Could these be the underlying issue(s)? At least I had a different path to look down.

After searching again I found this post;

http://tinyurl.com/y8z9bud

According to the blog post the problem is this;

Reason for the error: Old version of the DTEXEC is picked up by SQL Server instead of the new one. That means, the exe shipped with 2005 is picked up when it is expected to use the one shipped with 2008. So, obviously this happens when SQL Server 2008 is running along with SQL Server 2005 on the same machine. As a result of this, we end up with two versions of DTEXEC executables. One residing in SQL Server 2005 path (“C:\Program Files\Microsoft SQL Server\90\DTS\Binn”) and the other in SQL Server 2008 path (“C:\Program Files\Microsoft SQL Server\100\DTS\Binn”).

There are three choices to fix this problem.

1. Hard code the path of SQL Server 2008’s DTEXEC while calling the SSIS package as shown below.

C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTEXEC.exe /F “D:\MyFolder\MyPackage.dtsx”

2. Rename the old exe in the 2005 path to a different name (Example:- C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEXEC_Old.exe)

3. Go to PATH environmental variable and edit it in such a way that “C:\Program Files\Microsoft SQL Server\100\DTS\Binn” path appears well before the “C:\Program Files\Microsoft SQL Server\90\DTS\Binn” path.

I first tried choice #2. Renaming the file. Yup you guessed it. No love here.

I then proceeded to the choice #3. Again, no love. I don’t know why I never tried choice #1. Other than figuring if renaming the file and changing the ordering in the path environmental variable didn’t work why would the first.

My next move will not work for the majority of you. I uninstalled SQL 2005. We were not using it on this server and the only project on it was the one I was working so. After uninstalling I reapplied SP1 to SS 2008 just in case. This SS 2005 gone this of course caused other issues. (say it isn’t so).

The next issue was the dtsx files and the deployment manifest files were no longer associated with any program. Good and bad. Good because I knew I had finally broken the chain linked to SS 2005. Bad because now I couldn’t use my files! AHHH! Well not really. When I clicked on the manifest file the open with dialog box opened and once I associated the files with the SS 2008 versions all worked as designed. Dtsx files should be associated with Integration Services Package and the manifest files associated with DTSInstall.exe.

After getting this all working I went back and looked at the log tables to see if I could find anything to point to why this started after I had deployed the package without error.

Sure enough, I found this note in the logs.

Product: Microsoft SQL Server 2005 – Update ‘Service Pack 3 for SQL Server Database Services 2005 ENU (KB955706)’ installed successfully.

I know I had applied SP1 to SQL Server 2008 before I even started the work.  I actually had that on my laptop because I had recently gotten a new hard drive and had to reinstall everything.

I don’t recall clicking on SP3 to download/install but I have to take the responsibility since I can’t blame it on anyone else. The only thing I can think is that when SP3 got installed it make SQL 2005 the default server again and that is why I was getting the version out of sync error.

I heard a quote last night from comedian Ron White, “You can fix a lot of things, (my injection here) TSQL code, bugs, but you can’t fix stupid. Stupid is for-ever”

Thanks to all that helped from the twitter list #SSIS.

Lesson learned; be careful and be smart. It took me a day and a half to get this figured out. When it would never have happened if I was more aware of what box(es) I was clicking. Oh well at least it gave me something to write about for #TSQL2sDay 🙂