Tag: SQL

..contributing to IT in London since 2002..

Deceiving MS SQL Server: UPDATE Command

First version:

It does NOT complain and it seems all right, and runs without a problem, but since I selected the records with INNER JOIN and know that there are records that must be updated, we must find another way of updating those records. The problem is our table is referring to itself and …

Broken SQL objects: Views, functions, stored procedures, triggers

Designing a perfect entity relationship diagram for our database together with a marvellous table structure is never good enough.. Have look at the following example (for MS SQL Server) creating two tables and a view:

A view, a function or something else, we try to write re-usable code so that we do not have …

Update Query on MS Access

Microsoft programmers are always very particular on their way of approaching problems. Since they are the “biggest” software developers in the world, they have to set the standards!? Problem: Updating field(s) of a table using a complex (sub) query. Solution: 1. In standard SQL, you can this:

It makes sense, right? No, not according …

MS SQL Server: Anomalies 2

We had a database in MS SQL 2000 server. The server/database was configured NOT to use recursive triggers. We backed up and restored the database on another machine with MS SQL 2005. Again the database is NOT using recursive triggers. However, our application started to behave strangely. On one of the tables, we had 2 …

Firebird SQL: The Power of Simplicity

Firebird SQL Server has always been a “proper” database system since it forked from Borland’s famous Interbase. I just love it! In some big database applications, I found that the developers preferred to duplicate a set of tables for probably performance reasons. For example, in most accounting databases, there is a notion of company so …

MS SQL Server: Using Remote Servers (Linked Servers)

Sometimes I need to have a quick way of transferring data from one database to another running on a separate machine, especially when the table structures are identical or similar, it saves time. M$ SQL Server 2005 has a feature called linked servers. Give a name to your server: SERVER123 Choose a provider depending on …

MS SQL Server: Anomalies

Backup/restore databases: After the restore, you must revoke (sp_revokelogin) and grant access (sp_grantlogin) to users again, esp. if you are restoring the database on a different machine. Note: I use mixed mode authentication and prefer not to use Windows or domain users. VARCHAR or TEXT: Pro VARCHAR: They recommend to avoid using fields of type …