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…
You are currently browsing all posts tagged with SQL Server
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…
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…
PowerBuilder ‘Gotcha’ – Datawindow retrieval arguments
So I’m working on a scheduling process and I have a datawindow object which takes a number of arguments.I have them set up as al_pro_id (long), adt_end (datetime), adt_start (datetime), al_resource_id[] (long array), and al_dept_id (long). To save from maintaining two separate objects, I used this object for a datawindow as well as for datastores…
PowerBuilder & MS SQLServer datetime methods
If you have scheduling functionality in your application sooner or later you are faced with spanning dates. By this I mean starting something on one day and ending it on another. Powerbuilder is not very good at this, especially if you have something which starts before midnight and ends after midnight. SQL Server provides good…
Msg 7391 in SQL Server
So I’m setting up a stored procedure in SQL Server 2000 to access some data on an Oracle database. The DBAs have set up the linked server in SQL Server to the Oracle source with the name ‘DTEST’. I’m going to need to use the OPENQUERY method since I need the statement to execute in…