Here is a link to a very good article on SQLServerCentral.com 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.
You are currently browsing the MS SQL Server category
Update Columns in Database
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 Table.id = other_table.id WHERE … The structure of this syntax is always something…
PowerBuilder – Next Key Service
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…
Next Key Service for Database tables
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…
PowerBuilder ‘Gotcha’ – Malformed Imbedded SQL
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…
SQL Server Date & Datepart formats
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()))…
PowerBuilder ‘Gotcha’ – Quoted Identifiers
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…
Development tools at Home
Here is a list of some of the software I use at home. avast! Free Antivirus (www.avast.com) Fine antivirus program at no charge. XnView (www.xnview.com) Free image and graphic file viewer/converter. FileZilla Client (filezilla-project.org) Open Source FTP. Microsoft Visual Studio (www.microsoft.com/express) Great set of tools to use (I use Visual Basic/C# and SQL Server mostly). …
Easy ANSI Join Conversion
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…
Tools I use to get my work done.
This is a listing of the tools I use frequently at my job to get things done faster and more effectively. Notepad++ (www.notepad-plus-plus.org) – 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…
