Connect to Microsoft SQL Server (including Azure instances) from Linux (x86/x86-64) using ODBC and JDBC

Connecting to Microsoft SQL Server from Linux can be done through two different methods: ODBC and JDBC. Unfortunately, FreeTDS doesn’t connect to the newest versions of SQL Server unless you want to enable legacy connections.

The Microsoft JDBC Driver 4.0 for SQL Server, a Type 4 JDBC driver provides database connectivity through the standard JDBC application program interfaces (APIs) available in Java Platform, Enterprise Edition 5 and 6.

The Microsoft ODBC Driver (Linux) For SQL Server provides native connectivity from Windows to Microsoft SQL Server and Windows Azure SQL Database on Linux.

Share Button

Sybase’s Openclient SDK 15.7

It doesn’t work with Microsoft Visual Studio 6. It requires Visual Studio 2005 (minimum). Digging out my 2005 disc. Starting to build the DBD::Sybase ActiveState PPMs for Windows 32bit and 64bit. Should have at least one version out tomorrow

Share Button

HowTo: Comparing two tables and returning the # of rows that match FAST

The most intuitive way to write a comparison of two tables and spit out the # of rows that are the same involves a left join:

select 'Investment' as TableName, count(*) as RowCount
from Investment_A a, Investment_B b
where
  a.col1 = b.col1
  AND a.col2 = b.col2
  AND a.col3 = b.col3
  AND a.col4 = b.col4

This returns the correct answer but is very slow. Is there a better way? Of course!

select 'Investment' as TableName, count(*) as RowCount
from
(
   select 1 as num
   FROM (
      select * from Investment_A
      UNION ALL
      select * from Investment_B
   ) tmp
   GROUP BY col1, col2, col3, col4
   HAVING COUNT(*) > 1
) tmp2

By pushing the comparison off into the GROUP BY, we leverage the DBMS engine far more efficiently. There are two drawbacks:

  1. Readability of the SQL code
  2. Far more temporary storage is used for the GROUP BY. There is a real risk of running out of temporary storage if the tables are large.
Share Button

Wanted: Microsoft SQL Server DBA (Chicago, IL)

Position Responsibilities

Provides DBA services for Production, test, and development databases
Administers and maintains the production, test, and development databases.
Performs SQL code releases
Reviews application designs for compliance with production acceptance requirements.
Complies with IT policies and procedures, especially those for quality and productivity standards
Complies with Information Security policies and procedures. Verifies deliverables meet Information Security requirements.
Participates in special projects and performs other related duties as assigned.
Operations are 24 x 7. May be required to be on call, work beyond normal business hours

Position Requirements

B.S. in computer science
2 to 3 years experience
Strong written and oral communication skills.
Strong analysis and problem solving skills.
Working knowledge of the following DBA practices and concepts: database backup & recovery, database performance & tuning, database monitoring, and maintenance.
Working knowledge of Visual Source Safe
Strong knowledge of database design and administration
Stored procedure and view development and troubleshooting
Database size management (includes volume/disk mgmt., file and filegroup allocations.)
Basic understanding of Microsoft Clustering technology
Proficient with Microsoft Office Suite
Entry level knowledge of networking
Entry level skill set of database performance tuning/indexing
SSRS, SSIS, and scripting experience a plus.

Interested? (say I referred you on the application form)

Share Button

Andrew E. Bruno: Database Design with Dia

Back in December Diaof 2007, Andrew E. Bruno wrote an excellent article on how to use the open source program Dia to design databases.  He used the resultant design to create a MySQL database but the same principles could be used to create just about any relational database from the design.  Dia is available for Linux, *nix, and Windows.

Database Design with Dia

In this post I’m going to give a quick how-to on creating database schemas with a wonderful tool called Dia. I’ve often found having a nice visual representation of a database to be quite helpful but can’t stand keeping it up to date. As soon as you add a new column or change the design around you end up having to sync your visual diagram with your SQL files. It’s tedious having to manage the various SQL for building the database and this can be a larger pain when trying to support different database platforms each with their own SQL syntax. So before you create your next database read on and see how Dia can make your life a bit easier.

Dia is a program for creating diagrams and for this exercise we’ll be creating UML diagrams from within Dia. We’re also going to use a perl script called tedia2sql which will transform our Dia files directly to SQL for our target database. What’s also nice about creating database schemas this way is that you can generate SQL for multiple target databases without the maintenance overhead.

read more….

Share Button

Introducing QweryBuilder, a Developer GUI for Sybase ASE, SQL Anywhere, Microsoft SQL Server and Oracle 10g/11g

qwerybuilder_reflectedimageQweryBuilder uses innovative ideas to provide developers with the ability to easily insert, extract and modify data from a variety of databases.

The goal of QweryBuilder is to increase a database developer’s productivity. It contains many time saving features.

SQL Editor

QweryBuilder - SQL Editor

QweryBuilder - SQL Editor

  • Script auto complete
  • Custom auto complete lists
  • Code Templates
  • Display column list for tables and views in editor
  • Display procedure parameters in editor
  • Keyboard shortcut to open procedures
  • Generate and insert new GUIDs
  • Syntax folding
  • Auto indenting

Criteria Query

QweryBuilder - Criteria Query

QweryBuilder - Criteria Query

  • Form view criteria screen for easy data retrieval
  • Updateable result set
    • Insert nulls, GUID’s, and computed values into results
    • Insert, delete and update data rows
    • Generate insert statements from results
    • Create graphs from result data

Graphic Query

QweryBuilder - Graphic Query

QweryBuilder - Graphic Query

  • Create queries graphically
  • Cross table querying without writing SQL

Other Features

  • Support for Microsoft SQL Server, Sybase ASE, SQL Anywhere and Oracle
  • DDL Browser
  • Database Search
  • Visual Difference
  • Code Formatter
  • Getting Started Window
    • Displays last used connections, files and database objects
    • Easily navigate to the last thing you were working on
  • Clipboard Saver
  • File Browser
  • Object Browser
Share Button

Calling Sybase developers! Please help fix the Sybase and Microsoft driver for the CakePHP framework!

While the CakePHP framework is very good, the dbo “driver” (wrapper) between CakePHP and the PHP Sybase/Microsoft drivers ar not implemented to be actually usable.  Most of the issues are differences of SQL dialect between MySQL and the commercial DBMSs.

The issues tend to be simple ones such as, “select * from my_table limit 10” to retrieve the first 10 rows not working.  Using “select top 10 col1,col2,….coln from my_table” will.

So how can you help?  Very easily! 🙂  The CakePHP website shows us how:

Contributing to Cake

We are always looking for contributions, so don’t be shy. There are two ways to get involved:

1. Documentation:

The Cookbook is ready to accept your input. Everyone can help with the documentation effort by simply logging in with your Bakery account, writing (or editing) a section and submitting it for review. The application is still under development, so if you find a problem, submit a ticket following the recommended procedure. Visit #cakephp-docs on irc.freenode.net.

2. Bugs, Enhancements and Optimizations:

Contributing code to CakePHP is easy: Submit test cases and attach them to tickets. That’s it. Fame, glory, and praise to all those who make the effort.

Thanks to all those who have already contributed to CakePHP. We look forward to everyone’s future contributions.

Take a look at the source code for the drivers.

Share Button

FW: Migration Migraines

Adrian Bridgwater

Adrian Bridgwater

Migration migraines: the top seven DBA data headaches

Posted by Adrian Bridgwater

Once or twice a year I get to work with an excellent DBA pal from Illinois called Jason Froebe who describes himself as a, “Perlmonger capable of speaking fluent munchkin.” His personal blog is called Ramblings of a Geek, but I keep telling him he should rename it “Froebe’s Frontal Lobe”.

read more at ZDnet…

The work that Adrian mentions is a last minute article for the International Sybase User’s Group that I wrote and he edited.  The benchmarking article that many people are waiting for is being held up by several groups giving their approval for the release of the material.

As always, it is a real pleasure to work with Adrian 🙂

Share Button

Borderline Scamming being done by software companies

The latest craze from software vendors to companies is to charge for each and every core a machine has regardless of whether or not you’re going to use it.

Get this, if you want to buy a production license for your database/middleware/web server, the vendor (starts with an “S”) wants you to send them the hardware specs of the box.  If you tell them it is a Dell superduper server with 8 quad core CPUs and 96GBytes of RAM but you only will be using a single core for the database and devoting the rest to the middleware/webserver, you STILL have to pay the vendor for all 32 cores (8 CPUs X 4 cores).  Your software license costs is now 32 times MORE what you should have to pay IMHO.

Lots of software companies are now doing this anti-customer practice just to beef up their short term revenues.

What makes them think that you won’t go to another vendor?

  1. Their competitors are probably doing the same sales tactic
  2. They have you by the family jewels, vendor lock in, and it will cost you far too much $$$ to migrate
  3. They think that you’re too stupid and/or timid to call their bluff
  4. FUD that is spread by well meaning and well known folk that don’t know jack about the open source alternatives

Who the hell do they think they are?

Share Button

Anybody running VMware’s VMotion in production?

Don’t know what VMware VMotion is? VMotion allows you to easily move one virtual machine to another VMware ESX host that is connected to the same storage mechanism (presumably SAN) with no downtime. Looks promising 🙂

Image courtesy of VMware

Image courtesy of VMware

Dell has a demonstration using Microsoft SQL Server where they move the live SQL Server from one blade to another with no outage:

Share Button