Wednesday, April 16, 2014

FREE or Open Source or Inexpensive options to compare data (in tables) in SQL Server

Intro

Visual Studio 2012 (an some earlier editions) include Schema comparison for SQL Server. This is NOT what I am writing about here today. Assuming you have used this tool to make tables, etc the same you may have a need like I do when moving data from dev to production databases or the reverse. There are several ways to go about this.

FREE - SQL Server Data Tools 

Probably the best place to start is SQL Server Data Tools which is available from Microsoft. It includes among other things the ability to compare data in SQL Server tables.It is available for Visual Studio 2010 and newer. There was an option in some editions of Visual Studio 2010, but not in Visual Studio 2012. To get the functionality in Visual Studio 2012 you need the SQL Server Data Tools to be installed. Once you have it installed you will have functionality very similar to what was available in VS 2010 or the RedGate product. Here is a direct link for the download of the English ISO. One of the nice things about this option is that it is well integrated into Visual Studio 2012 and uses the same source and destination configurations as the SQL Schema Comparison that is built into VS 2012.You can also select what tables you want to compare, what columns in the tables, if you want source or destination records, etc. It will just to the update for you or you can have it generate the SQL Script that you can manually. It gives you a nice visual representation of the differences and let's you select the rows you want to change. It seems to be pretty fast. The generated SQL script even disables constraints as needed. It also seems to handle nulls properly. This is a very nice option for free!

It appears it can be called from the command line as well, but I have not tried it.

Here is the blog for the SSDT team.



FREE - tablediff.exe

IMHO, this may be the best choice for scripted options. Believe it or not tablediff.exe is a utility that comes with SQL Sever 2005 or greater. I believe this is the tool that SQL Server uses when replicating tables, though that is just what I read from someone else. It will tell you on a row by row and column by column basis what is different. It will even generate the SQL scripts needed to make the destination table look like the source table. As far as I know you cannot download it separately. However, it is installed when you install SQL Sever 2005 or newer and you choose SQL Server Replication feature. In SQL Server 2008 R2 it is included by default, but I'm not sure about the other versions. On my machine it was located at C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe. Once you find it you can type tablediff.exe -? for the options or refer here to the documentation. The parameters are pretty well documented and easy to follow to I won't go into all the options, but here is an example of how you would generate a change script (SQL) and see what the differences are.

C:\Program Files\Microsoft SQL Server\100\COM>tablediff.exe -sourceserver MySrcServer-sourcedatabase MyDevDB -sourceschema dbo -sourcetable Person -sourceuser User1 -sourcepassword User1Pwd -destinationserver MyDestServer -destinationdatabase MyProdDB -destinat
ionschema dbo -destinationtable Person -destinationuser User1 -destinationpassword User1Pwd -c -o c:\temp\diff.txt -f c:\temp\diffgen.sql

This will generate two files. diff.txt which will have the differences, and diffgen.sql which will be the SQL script you can execute to make the destination table the same as the source table.

WARNING:
Be careful, the SQL will also generate delete statements for your destination table. This may or may not be what you want so just be aware. I recommend backing up your destination table before doing this operation.

Also, it doesn't appear to generate correct scripts for null. It put null in single-quotes. This can be changed easily with a search and replace though.

Keep in mind this is per table. If you have lots of tables and you want them all to be updated it could be a done also, but it a bit tedious. However, the nice thing about this tool is since it is command line once you have it setup you can run it again and again with little to no effort.

If you want you can even download a GUI for the command line tool. NOTE: You still need the command line tool. I was not able to get it to work with SQL Server 2008 R2, but you can decompile it and fix it I'm sure. Does it work for anyone else? All it does is exactly what you do on the command line so I'm not sure I really see the benefit if you are going to want to run it more than once.

Inexpensive - SQLDBTools

If I have a little money to spend on the solution. SQLDBTools costs $60 at seems to do pretty much what the RedGate products do, but for a fraction of the cost. It does do schema and data comparisons which is nice to have it all in one tool. I does visually let you see the differences and it also generates the change script. I have not tried this product, but it looks like the best product for the money (if you are going to pay for a solution). Given that SQL Server Data Tools now exists I don't really see the advantage or reason to pay for this tool.

FREE - Linked Server

This is a less desirable option because it doesn't do anything automatically for you. After you have a linked server you can do queries to see the differences between tables. This does nothing for automatically generating the update scripts. This solution is good for analysis only.

Inexpensive - Beyond Compare

Beyond Compare does a nice job of visually showing the differences in two files. It doesn't know anything about SQL or databases, but as long as you use SSMS to export the data to CSV, Excel, or tabular data Beyond Compare will show you the differences in an Excel like manner. Again, this does nothing for generating the change script and could be slow for very large tables.This is best for comparing two adhoc queries in my opinion.

Additional Info

I found this site that has a pretty exhaustive list of tools for SQL Server that may be useful.

Thursday, April 3, 2014

Capriza Review


Overview

Capriza is designed  specifically for non-developers. Capriza rapidly converts, transforms, and optimizes existing web-based desktop applications into secure, lightweight mobile apps (zapps), complete with modern mobile capabilities such as GPS, camera, barcode scanning, click-to-call, etc

Key Features

  • Zero coding required, but can do own coding also
  • Zero APIs
  • Citrix or live-screenscraping like concept
  • Very fast app creation
  • Produces HTML5-based mobile apps  (Capriza call them zapps) from standard web-based applications that were targeted to run on the desktop browser.
  • Mobile experience is different from desktop experience; it is generally simplified
  • Runs on any modern device and platform
  • Mobile extension kit to add custom widgets, native functionality, etc
  • SSO
  • Optimized for SAP and SalesForce.com, but can work on any web application
  • Mobile services such as
    • GPS
    • Camera
    • Bar code scanner
    • Click-to-call
    • Location services

Distribution (MDM)

  • Capriza Native App: available free through either the Apple App Store or Google Play; users login to see apps
  • Capriza HTML App: For enterprises that don't want a native app use any modern browser to access the non-native app.
  • Custom Enterprise URL: Centrally distribute and manage apps through a custom, corporate branded internet or intranet URL/Domain.
  • Enterprise App Store (EAS)for distributing apps that is corporate branded.
  • Homegrown EAS

Monitoring

  • Cloud-based, real-time management dashboard
    • Users
    • Infrastructure
    • Zapp Health monitoring
  • Analytics
    • Usage
    • Adoption
    • Feedback

Security

  • Can run their software behind the corporate firewall in 8 minutes to access apps on the intranet
  • If it is public the cloud can be leveraged

Platforms

  • iOS
  • Android
  • BlackBerry
  • Windows Phone
  • Samsung BADA
  • Firefox OS

Devices

  • Smartphones
  • Tablets
  • Desktops

Browser Support

  • Safari
  • Chrome
  • Android stock browser
  • Others

Professional Services are available

How it Works

Zapps work in a manner similar to Citrix. The Zapps are actually thin clients. These clients don't store anything locally on devices which lowers security concerns. All communication between components use HTTPS. Here is how a typical interaction would look:

  1. User launches a zapp on their mobile device it
  1. The zapp connects to the Capriza Relay Server (on premise or cloud) via HTTPS. It manages the communication between the zapp and the Runtime Agents.
  1. The Capriza Relay Server starts a Runtime Agent which is a headless browser.
  1. The Runtime Agent connects to the legacy web app and is rendered (not visible to anyone) in the Runtime Agent.
  1. The Runtime Agent (or maybe the Runtime Agent not sure) translates the legacy web app to a mobile friendly format and sends it to the Zapp (mobile app) to be displayed to the user.





Developer Experience

  • Capriza Designer (Firefox Plug-in)
  • Walk through web application and drag parts that you are interested in to the mobile app screen to create the screens.
  • Screens are customizable
  • No programming needed
  • Can change layouts, etc.

Changes

  • When the legacy web application changes zapp will need to change also and should be part of change management.

Cost Model

Depends on how want to license
  • Buy Platform for unlimited use
  • Per User per month for smaller uses
  • Prices vary based on specific details of how to deploy, etc.

Conclusions


I have investigated the product.  Below is a summary of what I found. In general please understand that this tool does NOT allow you to add functionality to a mobile application that is not already on the legacy web application. So, it is not a tool for doing new mobile development if there is not an existing web application that it will interact with. The presentation of the user interface is changed to be for a mobile device. This is typically much simplified to be more task specific. It could be very good for bring existing web applications to the mobile device when source code level access to the existing web application is not available. It could also be useful for prototyping changes to web applications that we do have source code level access to as well.

Pros:
Very fast application creation when it plays nicely with the Capriza tooling
Works on any mobile device
Low cost of development due to time savings
Distribution simplified
Minor changes such as layout and cosmetics do not require the mobile application to be modified typically
Cons:
Cannot be any faster than the existing web site and there is some overhead
Some web apps will be work better with this tool than others
Major changes to legacy web application affect the mobile application

Monday, March 17, 2014

How to deploy Iron Speed Designer web application


I love Iron Speed Designer (ISD) for lots of reasons. I also hate certain things about it. Today, I am going to talk about how the deployment is lacking, but how you can work around it.

My experience:

  • If you want pure frustration click the Deploy button or use the menu in Iron Speed Designer. This button will allow you to deploy exactly once. After that it will tell you there is an existing profile. In my current version of Iron Speed Designer (ISD), it tells me that there is no project for some reason. BTW, ISD support says this is by design. OMG. If you want to deploy to anywhere except a local directory it doesn't support that and you have to use Visual Studio anyway. Believe it or not, I get different results between publishing via this and Visual Studio. 
  • Don't use Visual Studio to publish your project either. It seems to cause some weird problems where ISD can't build anymore and can't find stored procedures at runtime when using Active Directory or Windows Authentication authentication.
The workaround:
  • Just do a simple file xcopy deployment of the project directory to the server. No issues this way.

Does Iron Speed Designer (ISD) use username or domain\username in the user table?


When using Windows Authentication with Iron Speed Designer (ISD) you can use a user table to specify who can access your application. I often forget what the username column in Iron Speed Designer (ISD) is supposed to contain.

The confusion in my mind is that Windows authentication requires a domain to be specified. This may not be apparent if using IE, but with other browsers such as Firefox you will be prompted for your credentials and required to put the username in the format: domain\username. In the case of ISD, it appears that Windows authentication is still handled by IIS / Windows / Active Directory as it is in any other ASP.NET application that uses Windows Authentication. Once the user is authenticated though, ISD queries the database tables that you specify. In these queries it uses just the username portion of the domain\username you typed (or IE passed on for you). I don't think this would work very well if your network has multiple domains, but I have not confirmed this.

So, to put it simply, store just the username (no domain) in the column in the user table you specify.

Wednesday, March 5, 2014

Oracle APEX Review

Overview
Oracle APEX = Oracle Application Express
Been around since 2004 under various names
Browser Based development and deployment
Think of it as a replacement for MS Access, but on the web and multi-user with an Oracle backend.
RAD tool for the Oracle Database; think Forms over data
Declaratively build web 2.0 applications
Leverage SQL and PL/SQL skills.
Multi-tenant Hosting
Departmental Solutions is focus
The Oracle store is built with APEX
Lots of options for Authentication or even custom
Built-in support to prevent URL tampering
20 canned themes or can create your own, including one for starting mobile.
Wizards to create forms including master detail, etc, but has basic layout, functionality, etc.
Create web services with wizard. A web service can be created from a region also.
Can add regions to a page and add most anything to the region including forms, etc.

Cost
Fully supported by Oracle
Free with Oracle Database (include Oracle XE)


Skill Set Required
PL/SQL
SQL

Skill Set Required for Customization
HTML
JavaScript
CSS


Advantages
Robust migration path for Oracle Forms application to APEX.
Very easy to generate data-driven CRUD data entry style applications that include simple reporting.
Migrating MS Access, or Excel files to a multi-user web environment
Similar to SharePoint lists such that you can create tables, and UI based on an Excel spreadsheet.
Free with Oracle database license
RAD or demo or POC
Built-in themes
Customization done using standards such as JavaScript, HTML, and CSS.


Disadvantages
Debugging can be painful. No breakpoints,
Business Logic written in PL/SQL. No layers such as a business layer and data access layer. SQL or stored procedures are referenced directly.
Web based development environment  feels a bit clumsy and slow.
Tightly bound to PL/SQL and Oracle.
You must work within the paradigm that is defined by APEX. If the application doesn't fit the paradigm the effort drastically increases. For instance javascript and html/css become the way of working.  This is a much different skillset. Alternatively, plug-ins can be created to extend APEX.
Migration path from MS Access to APEX is limited and works on with simple
The time you save in development time can be quickly lost in debugging, support, and customization.
No version control 


Architecture

  • Web Browser
  • Apache with mod_plsql/EPG web listener
  • Application Express
  • Meta Data



KEY FEATURES according to a Oracle APEX Specialist

FRAMEWORK
The APEX framework uses SQL and PL/SQL on the back-end, and HTML, CSS, and JavaScript for the user interface. SQL and PL/SQL are solid and proven languages and they allow APEX developers to leverage the features of the Oracle database. HTML, CSS, and JavaScript are industry standard components for building web applications.

SUPPORT FOR MULTIPLE AUTHENTICATION SCHEMES
APEX supports various authentication schemes such as LDAP, database, Single Sign on, Oracle Access Manager, custom, etc.  This makes it convenient to integrate with any existing applications and authentication systems.

DECLARATIVE MOBILE APPLICATION DEVELOPMENT
APEX is bundled with the popular jQuery mobile libraries and provides declarative support for building mobile applications. A special mobile theme provides APEX mobile applications with typical mobile features such as page transitions and gestures including swipe, pinch, and tap. Custom mobile themes can easily be created with the jQuery mobile theme roller.

RESPONSIVE DESIGN FOR OPTIMAL VIEWING ON DESKTOP, TABLET AND SMARTPHONES
APEX applications can be rendered on desktops, tablets, and smartphones by choosing a theme based on a responsive design. APEX includes a set of modern themes that are based on CSS3 and HTML5, and supports HTML5 charts, and HTML5 item types such as sliders and toggles.

PACKAGED APPLICATIONS – A FULL DEVELOPMENT SUITE
APEX is bundled with a suite of business productivity applications that can be freely used to assist with the management and control of projects. The suite contains applications for bug tracking, issue logs, checklist management, meeting minutes, group calendar, decision management, document management, and project management.

RESTFUL WEB SERVICES
APEX has built-in support for RESTful web services and allows applications to access data and services over the internet or intranet using standard web APIs. Database web services that implement SQL or PL/SQL can also be created. RESTful web services in APEX requires the APEX Listener, a J2EE based alternative for the Oracle HTTP server and mod_plsql.

DATABASE INTEGRATION
APEX is a component of the Oracle database and applications build with APEX can utilize or benefit from any feature of the database such as advanced security, RAC, Spatial, Analytics, Multimedia, XML DB, Job Scheduler, utility packages, etc.  APEX uses SQL and PLSQL to interact with the Oracle host database.

GLOBALIZATION SUPPORT
Applications build in APEX can run concurrently in different languages. Applications are developed in a primary language and can be mapped to a supported target language. Strings in the primary application are exported to a XML Localization Interchange File (XLIFF) where they are translated and imported and automatically used by the translated application.

TEAM DEVELOPMENT
Team development is a built-in feature that allows a group of developers, working on a single application, manage new features, to dos, bugs, and milestones. Users of an application can provide instant feedback which can then be classified as feature, to do, or bug.

EXTENSIBLE
The APEX development framework supports plugins that allow developers to extend the functionality of their applications with reusable custom or third-party extensions. APEX applications can also be extended with custom HTML, CSS, or JavaScript.

OTHER FEATURES
APEX is also stacked with other utilities and features that greatly improve developer productivity throughout the life of a project.

Version Control Support – Applications can be automatically exported into SQL script files where they can be included in a version control system. APEX provides an application exporter utility as well as an application splitter utility for splitting application into individual page scripts.

Error Handling – Developers can create a single error handling function that handles exception consistently across all pages in an application.

Oracle Forms Migration Tool – If Java/ADF is not for you, APEX is a viable option for converting Oracle Forms applications. APEX includes a forms migration tool to assist with the migration of Oracle Forms applications.

Accessibility – APEX applications have automatic built-in accessibility support. Applications can be rendered in high contrast mode or screen reader mode in order to meet accessibility requirements.       

Utility Reports – APEX has a comprehensive list of reports that provide real-time information on applications. Some of the more useful reports are:

  • Change History – List of changes made by developers
  • Advisor – Quality control review of an application
  • Database Object Dependencies – List of database objects used by an application
  • Debug Messages – List of debug messages generated by an application
  • Recently Updated Pages – List of pages that were recently updated


Features
Reports
Forms
Charts
Calendar
Templates
Navigation
Validations
Processes
Computations
Branches
Web Services
Email Services
Translation Services
Conditional Processing
Authentication
Authorization
Session State Management
Logging & Monitoring
Interactive Reports

Integrating with Other Tools / Services
SQL
PL/SQL
RAC
Spatial
OLAP
Flashback
Web Services
Text
Multimedia
Analytic Functions
Globalization
XML DB
eBusiness Suite


Migration to APEX
Direct Excel Conversion
MS Access Conversion Support
Oracle Forms Conversion Support

Security
Popular authentication supported as well as custom
Can hide columns based on user access level


Screenshots of the development environment





Built-in User Management



List of applications in the workspace




List of all pages in an application




Design a page




Options for creating a new application


Add a page




Create a Form




Sample User Interface
of the application created from a Spreadsheet
This is an interactive report. We can add charts, group by, add aggregate columns, filter, highlight, export to CSV, re-order columns, hide columns, save report, etc.






Conclusion
Oracle APEX may be a good choice depending on your needs. Here are some key criteria for this to be a good choice:

  • Oracle database is your database of choice
  • You know SQL and even better you know PL/SQL
  • Your application is essentially a CRUD or forms over data applications or data-centric application
  • You want to do RAD
  • You don't want to write lots of code
  • For customization you will need JavaScript and HTML/CSS knowledge
  • For writing Business logic and customizing the application you will need PL/SQL knowledge
  • To get the largest savings in time you will want to use the predefined forms and wizards, but this is not required.

It also means you are willing to give up some of the more common development practices

  • Having separate tiers for business logic and data access. Instead you must want your business logic to be in the database in packages.
  • IDE that runs on your pc
  • Robust debugger
  • Unit tests
  • Version control (Yes you can export files and them to version control, but it isn't quite the same thing in my opinion)
  • If you want to do OOP or MVC this is
  • Drag n drop files from your desktop into the IDE
  • Less control over files in your project

If the above criteria is okay with you then Oracle APEX is worth looking at. You get a lot of functionality. Similar to what you would get with SharePoint, MS Access, or meta-data tools, but with different strengths and weaknesses as noted above.


References:



Thursday, February 20, 2014

I keep getting the error  has not been registered" when I try to use the Import Data functionality in SQL Server Management Studio (SSMS) with a .xlsx Excel file as the data source.

The OLE DB provider Microsoft.ACE.OLEDB.12.0 has not been registered.

Here is my environment:
Windows 7 (64-bit)
SQL Server 2008 R2 (64-bit)
MS Access 2010 (64-bit)
MS Office Excel 2010 (64-bit)

I do NOT get the above error when I use the import wizard in MS Access 2010 to import my Excel (.xlsx). It only happens with SSMS. I tried installing the different Data Connectivity Components. Some would say it was already installed, other would complain that it wouldn't work with the 64-bit software I was using. In the end the only one that would install that helped was the following:

2007 Office System Driver: Data Connectivity Components

So frustrating that I had to waste time on something that should just work after installing SQL Server 2008 in my opinion.

Friday, January 31, 2014

Removing duplicate rows in SQL Server

If you are using SQL Server 2005 or greater (we need the CTE (Common Table Expression) functionality) you can in one statement delete duplicate rows from a table. You can look at all columns in the table or you can look at a subset of the columns. The example below uses just one column (EmailAddress), but you can replace that one column with a comma separated list of the columns you want to consider.

Before we go off and start running delete scripts on our data, it is always a good idea to make sure you have a backup of the table so you can check you work after or restore if something goes wrong.

Let's first look at the data

-- see what rows have duplicates and which ones don't
SELECT  EmailAddress
      , row_number() OVER ( PARTITION BY EmailAddress ORDER BY ID) AS NumInstances
FROM    Person

-- show only the rows that are duplicates
with myTable as (
SELECT  EmailAddress
      , row_number() OVER ( PARTITION BY EmailAddress ORDER BY ID) AS NumInstances
FROM    Person
)
select * FROM myTable
    WHERE   NumInstances > 1

The actual delete statement

-- do the actual deleting of the duplicate rows
with myTable as (
SELECT  EmailAddress
      , row_number() OVER ( PARTITION BY EmailAddress ORDER BY ID) AS NumInstances
FROM    Person
)
DELETE  FROM myTable
    WHERE   NumInstances > 1

To modify the code to fit your situation you will typically just need to replace the items in red with the comma separated list of columns you want to consider. Next change the table name in blue to the table you are trying to remove the duplicates from. Finally, change the column in orange to be one or more columns (separated by comma). In this case, I have a primary key so, I used it. You could use all the columns you are considering as well.

For more info on how it works and pre-SQL 2005 solutions see here.