The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My work

  • My badges

  • Twitter Updates

  • My Flickr Stream




    More Photos
  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,689 other followers

Archive for the ‘Database Development’ Category

Static Code Analyzer for T-SQL – MS SQL Server. Plugs into MS SSMS and can al…

Posted by jpluimers on 2017/01/12

Static Code Analyzer for T-SQL – MS SQL Server.

Plugs into MS SSMS and can also be run from command line.It reports useful clues which you can turn/on off to your liking.

It will spot declared but unused variables, but it appears it doesn’t do code coverage or execution path to spot stuff like variables being used without being initialized. Price: Free

Source: Lars Fosdal on G+: Static Code Analyzer for T-SQL – MS SQL Server. Plugs into MS SSMS and can al…


Posted in Database Development, Development, SQL, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 | Leave a Comment »

Firebird – date and time literals, conversion to ISO-8601 compatible strings

Posted by jpluimers on 2016/12/28

For my reference:

select 'Date literal values' as description
      , (cast('now' as timestamp)) as nowTimeStamp
      , (cast('today' as timestamp)) as todayTimeStamp
      , (cast('tomorrow' as timestamp)) as tomorrowTimeStamp
      , (cast('yesterday' as timestamp)) as yesterdayTimeStamp
      , cast(cast('Now' as date) as varchar(10)) as nowDateCast
      , replace(cast(cast('Now' as date) as varchar(10)), '-', '') nowDateIso8601
      , cast(cast('Now' as time) as varchar(13)) as nowTimeCast
      , replace(cast(cast('Now' as time) as varchar(13)), ':', '') nowTimeIso8601
      , cast(cast('Now' as timestamp) as varchar(24)) as nowTimeStampCast
      , substring(replace(replace(replace(cast(cast('Now' as timestamp) as varchar(24)), '-', '') , ' ', 'T'), ':', '') from 1 for 15) as nowTimeStampIso8601
from rdb$database 

Output and transposed output:

Date literal values 3-8-2016 8:45:12        3-8-2016                4-8-2016                2-8-2016                2016-08-03  20160803       08:45:12.3860 084512.3860    2016-08-03 08:45:12.3860 20160803T084512         

DESCRIPTION             Date literal values
NOWTIMESTAMP            2016-08-03 8:45:12
TODAYTIMESTAMP          2016-08-03
TOMORROWTIMESTAMP       2016-08-04
NOWDATECAST             2016-08-03
NOWDATEISO8601          20160803
NOWTIMECAST             08:45:12.3860
NOWTIMEISO8601          084512.3860
NOWTIMESTAMPCAST        2016-08-03 08:45:12.3860
NOWTIMESTAMPISO8601     20160803T084512



Posted in Database Development, Development, Firebird | Leave a Comment »

Firebird [# CORE-3558] server reports that DB file is used by another application on secondary attachment attempt through a symlink

Posted by jpluimers on 2016/12/15

It took quite a while to find out what happened, but this is the underlying issue: [# CORE-3558] FB server reports that DB file is used by another application on secondary attachment attempt through a symlink – Firebird RDBMS Issue Tracker.

The error you get in Windows is this one: I/O error during “CreateFile (open)” “The process cannot access the file because it is being used by another process.”

When you look with Process Explorer or handle.exe (both from SysInternals) they will show exactly the same filename (the canonical name to which the SymLink points).

But when you look in the config files of the applications, the database connections point to different filenames. Either (or both) are a symlink themselves or have a directory in their path that is a symlink.


In my case it was the latter: one of the directories in the path had an mklink.exe created link to another directory that had a directory contained the final file. There is even an API function that gets the canonical file name: GetFinalPathNameByHandle function (Windows)

Firebird doesn’t use that API on Windows (probably because it’s only available as of Windows Vista and Windows Server 2008) but does use a similar POSIX function (so on Mac OS X and Linux you don’t get this error).

Note that the above error messages can also happen when you use an embedded connection to a database (i.e. a connection string without a network address). There it is normal you get an exclusive file lock.


via: ADO.NET – a problem with multiple connections to the database (Russian) with Google Translate

Posted in Database Development, Development, Firebird, Software Development | Leave a Comment »

SQL Server, Modulo, floats

Posted by jpluimers on 2016/12/08

SQL server % (modulo, not mod) operator doesn’t like floats (with reason).

You should get rid of the floats as they will give inaccurate results.

As a workaround, cast either through an integer or through a decimal: sql server modulo float – Google Search

CAST(CAST(TheInaccurateFloatValue AS decimal(38,19)) % ModuloValue AS float)

The decimal(38,19) is the maximum non-float precision you get.

( cast(dividend as integer) % divisor ) + ( dividend - cast(dividend as integer))


Posted in Algorithms, Database Development, Development, Floating point handling, Software Development, SQL, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 | Leave a Comment »

Common Table Expressions: no nesting, but consecutively usage – via Stack Overflow

Posted by jpluimers on 2016/09/28

Common table expressions are awesome. They work in at least Oracle and SQL Server.

You cannot nest them, but you can use them consecutively. Thanks spender for explaining that:

x AS
y AS


via: sql – Can you create nested WITH clauses for Common Table Expressions? – Stack Overflow.

Posted in Development, Database Development, SQL Server, SQL Server 2008, SQL Server 2005, SQL Server 2008 R2, SQL Server 2012, OracleDB, SQL Server 2014 | Leave a Comment »

%d bloggers like this: