You are currently browsing the MS SQL Server category

Sequence Numbers in SQL

Posted on February 7, 2011 at 8:20 pm in

Here is a link to a very good article on showing a technique to re-sequence items via a database stored procedure rather than on the client. It’s pretty straight forward and solves a fairly common issue I’ve addressed several times in PowerBuilder over the years.

Sequence Numbers in SQL - the full story »

Update Columns in Database

Posted on January 28, 2011 at 8:20 pm in

It’s pretty common to want to update a column (or several columns) in one table with the value(s) from another table. Here is how to do it: UPDATE Table SET Table.col1 = other_table.col1, Table.col2 = other_table.col2 FROM Table INNER JOIN other_table ON = WHERE … The structure of this syntax is always something…

Update Columns in Database - the full story »

PowerBuilder – Next Key Service

Posted on January 11, 2011 at 8:20 pm in

As an extension of the next key stored procedure article, here is a pretty easy to implement a next surrogate key service to use with the PFC. If you don’t use the PFC it is still fairly easy to use with some modification. You can use this service anytime you want to get a sequential…

PowerBuilder – Next Key Service - the full story »

Next Key Service for Database tables

Posted on December 30, 2010 at 8:20 pm in

This provides for an easy way to generate key values for tables in a database when you don’t want to use a system generated value (like an IDENTITY column). The example is for MS SQLServer 2000 but could be adapted to any database which supports stored procedures. Note that this service assumes the key value…

Next Key Service for Database tables - the full story »

PowerBuilder ‘Gotcha’ – Malformed Imbedded SQL

Posted on October 7, 2010 at 8:20 pm in

I came across a statement like this while tracking down a separate issue and at first didn’t think much of it since it wasn’t related to the problem at hand. However, when running my process through the debugger I noticed a SQL error which caught my attention. <code>UPDATE dbo.shipperDetail SET contractId = (SELECT max(docId) FROM…

PowerBuilder ‘Gotcha’ – Malformed Imbedded SQL - the full story »

SQL Server Date & Datepart formats

Posted on September 24, 2010 at 8:24 pm in

Here is a handy cheat sheet for dates in SQL Server: — DATEPART SAMPLES select 'Year: ' + CONVERT(NVARCHAR(4),datepart(year, GETDATE())) select 'Quarter: ' + CONVERT(CHAR(1),datepart(quarter , GETDATE())) select 'Month: ' + CONVERT(NVARCHAR(2),datepart(month, GETDATE())) select 'Day of Year: ' + CONVERT(NVARCHAR(3),datepart(dayofyear, GETDATE())) select 'Day of Month: ' + CONVERT(NVARCHAR(2),datepart(day, GETDATE())) select 'Week: ' + CONVERT(NVARCHAR(2),datepart(week, GETDATE()))…

SQL Server Date & Datepart formats - the full story »

PowerBuilder ‘Gotcha’ – Quoted Identifiers

Posted on September 21, 2010 at 8:20 pm in

Be careful when developing against any database which supports quoted identifiers (I know, who doesn’t)? Anyway it is real easy to forget and ‘turn off’ this option on your local db (or your development connection) and end up releasing a bug by a simple SQL alteration like this: –Original SQL SELECT partNo , partDesc FROM…

PowerBuilder ‘Gotcha’ – Quoted Identifiers - the full story »

Development tools at Home

Posted on August 10, 2010 at 8:08 pm in

Here is a list of some of the software I use at home. avast! Free Antivirus ( Fine antivirus program at no charge. XnView ( Free image and graphic file viewer/converter. FileZilla Client ( Open Source FTP. Microsoft Visual Studio ( Great set of tools to use (I use Visual Basic/C# and SQL Server mostly). …

Development tools at Home - the full story »

Easy ANSI Join Conversion

Posted on July 26, 2010 at 8:10 pm in

Like many MS SQL users way back when I learned the standard syntax for selectstatements: SELECT customer.custId , customer.custName , address.addrType , address.addrAddress1 , address.addrSddress2 , csz.cszCity , csz.cszState , csz.cszZip , order.ordOrderNo , order.ordOrderDate , order.partId , part.partDesc FROM customer , address , csz , order , part WHERE address.custId = customer.custId AND csz.cszId…

Easy ANSI Join Conversion - the full story »

Tools I use to get my work done.

Posted on July 20, 2010 at 8:22 pm in

This is a listing of the tools I use frequently at my job to get things done faster and more effectively. Notepad++ ( – editorA great open source editor with great deal of customization and functionality built in.  Might as well get the ‘Poor Mans SQL Formatter’ plug in while you are at it. SSMS…

Tools I use to get my work done. - the full story »