Here is the video from my SQL Saturday #279 session on developer best practices & good habits to help DBAs keep the server healthy and secure. Please excuse the fact I was dealing with a sinus & chest infection during the presentation, then halfway through the camera died & we had to switch to Lauri’s Windows Phone…such a superior product! You’ll notice halfway through the presentation the resolution changes, and I apologize. Next time I do a speaking engagement I plan to use a tripod & better camera (or her phone again!)
During the presentation a good question came up regarding the effort required to manage SQL code in a source controlled application vs. in a stored procedure or view, and how I suggested keeping the code in source control can be perceived as a pain in the butt. In no way am I indicating source control is a pain. Source control is a necessary tool in the development process, that in my opinion should be used regularly by all developers regardless of platform or language. My goal is to compare the effort required when a simple change to a data type (for example) is needed, to check the project out, hunt for every location in the code where someone has hard-coded queries or statements that need to be changed, change those queries or statements (and hope you got them all), run unit tests, build/compile your application (assuming nothing else was negatively affected), deploy to test, send to QC for testing, deal with any other “issues” they find, then check the final build back into source control and release to prod, then sit in hypercare hoping nothing else happens…or simply have the DBA look in your stored procedure or view for any changes needed and change them immediately. The end point was it’s way easier to keep these in stored procs or views, not to mention it gives a centrally managed place for your database code that encourages communication between the developer and the DBA, making the entire team more effective. Sorry for any confusion.
Anyway, here’s the link to the slide deck for following along, as well as the presentation
If you attended my session on Connecting a SharePoint list to a SQL table for CRUD ops, thank you for coming! If you missed it, or if you wanted to review it again, my girlfriend was kind enough to record it for everyone! The projector can be difficult to see due to room lighting, but you can always follow along with the slide deck. Both are included in this post:
I wanted a way to validate that an email address in a SQL query was well-formed, and found a lot of resources suggesting to create a scalar function that invokes sp_OACreate among others, and return a bit indicating if it was valid or not. That works great, but not for those of us in heightened security environments, where granting execute permissions to system stored procs and functions is not permitted. After searching around I came up with the following function:
CREATE FUNCTION [dbo].[checkEmail]
DECLARE @results bit = 1
IF (SELECT PATINDEX('%_@__%.__%', @source)) = 0
SET @results = 0
This will allow you to pass in a single string, and the return value will be whether or not it’s a good address. For example,
checkEmail(‘firstname.lastname@example.org’) will return a 1
checkEmail(‘someone@someplace’) will return a 0
Of course, this isn’t a solution for validating actual domains or if an email address is truly active. But this will filter out 99% of the mis-keyed data, and ensure it’s well-formed.
Hope this helps someone!
(Originally posted September 2, 2009)
When I need to know the first (or any) occurrence of a character, I’m used to saying something like InStr() for VB or ASP, or String.IndexOf() in .Net, but when I tried to do something like this in SQL for a substring portion of a query I was creating, those obviously aren’t available. In Oracle InStr is available, but we`re talking about SQL.
To get the first occurrence of a character in a TEXT field (emphasis mine, because it must be a text compliant field) you use the PATINDEX() function. PATINDEX looks for the first occurrence of a specified pattern in a text field. Meaning, the field can be a text, nvarchar, char, etc. It cannot be an image or int, for example. PATINDEX, I learned, is typically used in BLOBs (Binary Large OBjects). Let’s say you are querying a table called MYTABLE, looking for the pattern “forest” in the field STORY_DESCRIPTION. Your query would look like this:
SELECT PATINDEX('%,forest%', STORY_DESCRIPTION)
The result would be the char position of the first occurrence of the pattern “forest”. This helped me when I was trying to put together something like this:
SELECT SUBSTRING(CN, 3, PATINDEX('%CN=', CN) - 3)
Where in this case I was querying an Active Directory results table and the CN field always started with “CN=”, but I wanted just the value from each row. This worked perfectly.
Although those of you who are SQL experts know of a much simpler method, I’m sure, this one worked for me!
We have some old SQL 2000 DTS Packages that we still maintain, and I’m not continuing to install SQL Enterprise Manager on future versions of my OS just to edit these things, I should be able to edit them in SSMS right? Well, yes, but it requires a couple of steps, that so far in my Google research is never complete…it’s always a part of the entire solution. (PINTA) Well, here’s the entire solution:
First, go to http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=11988 and download/install the SQLServer2005_BC.msi file (unless you can find it on your SQL 2008 R2 CD, it’s in <Drive>:<lang_code><proc type>Setup<proc type>)
Stay on that site and download/install the SQLServer2005_DTS.msi file.
Finally, (thanks to this MSDN discussion) check the PATH variable in your system settings by clicking the Start button, locate Computer, right-click and choose Properties. Click Advanced System Settings. On the System Properties window, click Environment Variables. In the Environment Variables window, in the System Variables section locate the PATH (may be lowercase) variable, and change it to one of the following:
For a 32 bit machine, make sure both of these paths appear in the list and in this order:
C:Program FilesMicrosoft SQL Server80ToolsBinn
C:Program FilesMicrosoft SQL Server100ToolsBinn
If it’s a 64 bit machine, you only need this one:
C:Program Files (x86)Microsoft SQL Server80ToolsBinn
Restart SSMS and that should do it!