Sunday, September 27, 2009

SQL Azure and MS SQL Server Integration Services

Enterprise data can be of very different kinds ranging from flat files to data stored in relational databases and XML datasources. The extraordinary number of database related products, and their historic evolution, makes this task exacting. The entry of cloud computing has brought area into the forefront as SSIS has been one of the methods indicated for bringing ground based data to cloud storage in SQL Azure, the next milestone in Microsoft Data Management. Presently the following methods are indicated:
  • Run Insert statements SQL Manager to populate tables schema created using SQL Azure MW*
  • Configure SSIS to bring in data
  • BCP [perhaps in next CTP of SQL Azure]
The first method works and can be very fast (only a small table was tested )

It may be noted that SSIS is another tool to bring in tables (so far only tried this object in the initial tests) to SQL Azure although only table schema is migrated and not the data. If the table were to have a clustered index, data porting may also be possible. In the test conducted the table did not even have a primary key column.

* It appears that the recent tweaking of SQL Azure MW can bring in the entire database according to a posting on the Oakleaf Systems blog site.



Saturday, September 26, 2009

Designing a data access page with EnterpriseDB Data

Microsoft Data Access Pages (DAP) was a great hit when suddenly data could be web enabled dynamically and user could say good bye to static HTML. There are still many DAPs fans who continue to use DAPs. With Office 2007 that ability was lost and Microsoft showed that ability was available in MS SharePoint Services. However, there are many who cannot move over to Office 2007 as they have their resources (programs) tied to older technologies.

The following article brought to you by EggHeadCafe.com is addressed to those folks and DAC aficionados and shows step by step how you may develop a DAP with data on a Postgres Database.

Please review the article at eggheadcafe.com

Web access to EnterpriseDB with Data Access Pages

Thursday, September 24, 2009

Two great tools to work with SQL Azure

You got an invitation, and you figured out how to create a database. Now you want to see some action. You want to look at database objects and populate the databases with data you can work with. Here are two great tools that you can use.


SQL Azure Manager and SQL Azure Migration Wizard are two great tools to work with SQL Azure. SQL Azure Manager can be downloaded from <http://hanssens.org/tools/sqlazuremanager/>here. SQL Azure Migration Wizard can be downloaded from <http://sqlazuremw.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=32334>here.

SQL Azure Manager takes your credentials and access your database on the SQL Azure. Presently I could see only tables and views in this version. No stored procedures. I tried to delete a table I had created earlier with SSIS, but somehow it could not drop that table. Perhaps table names with white spaces are 'no,no'. It is a very easy to use tool.

SQL Azure Migration Wizard is a nice tool. It can connect to (local)Server as well as it supports running scripts. I tried running a script to create 'pubs' on SQL Azure. It did manage to bring in some tables and not all. It does not like 'USE' in SQL statements(to know what is allowed and what is not you must go to MSDN). For running the script I need to be in Master(but how?, I could not fathom). I went through lots of "encountered some problem, searching for a solution" messages. On the whole it is very easy to use tool.

If these tools can be attached to IIS 7 Database Manager there will be one great tool to work with SQL Azure.

Monday, September 21, 2009

My experience connecting to an SQL Azure database

I got my invitation code to a space on the SQL Azure. After accepting and validating the same, a master was created for me. I created my first cloud based database HodentekModa.

I tried connecting to my database using SQLCMD but got a strange syntax error.

Pursued another avenue. Created a UDL file to test the connection. This was a success. Here are the steps if you want to test.

Step 1: Create a text file and change its name and extension to create a UDL file. Herein it was named: AzureSQL.UDL


Step2: Double click the file to open the Data Link Properties page

Step3: Fill the details as detailed here:

Provider: SQL Server Native Client 10.0

Select or enter servername: tcp:xxxx.ctp.database.windows.net

Use a specific user name and passord:

User Name: yourUserName@xxxx

Password: Your Password (1 number, 1 special character and few letters)

Select Database : Type in the name of database you created in SQL Azure

Your test connection should be successful with the above settings.

Twitter and Pornography

Recently I have noticed a number of people trying to follow me (http://twitter/subbagiri). I was naturally flattered. My elation subsided when I clicked on the URLs to find they just had Pornographic content.

Saturday, September 19, 2009

My articles on Data Access Pages

Dynamic content is at the heart of successful presentation of enterprise information on the Internet. Without the ability to present data with user interaction, Internet based commerce will be ineffective. It is possible to create dynamic web pages with data from the enterprise by using several technologies, including MS Access. Why MS Access in these days and times? Well, many businesses still use them.

Data Access Pages, a client side technology, depends on DynamicHTML. It is DHTML data binding with ADO that makes Data Access Pages possible. If you are using MS Access and attempting to place your data on the web do yourself a favor by reviewing the following articles:

Data Access Pages: What they are and how to create one ?

Using Microsoft Script Editor for Data Access Pages

Scripting a Data Access Page: a Simple Example

Creating Data Access Pages from Scratch

Creating Data Access Pages with Charts using Office Web Components

 [compare these charts to those created by MS Chart Control in ASP.NET 3.5]

Saturday, September 05, 2009

Teacher's Day (September 5) in India

"The aim of education is not the acquisition of information, although important, or acquistion of technical skills, though essential in modern society, but the development of that bent of mind, that attitude of reason, that spirit of democracy which will make us responsible citizens"
Dr. S. RadhaKrishnan

President and philosopher who figured in the early years of India whose birthday is celebrated as Teacher's day

Friday, September 04, 2009

On USING SSIS to transfer data between SQL Server 2008 and MySQL

There are a large number of posts on various difficulties experienced while transferring data from MySQL using Microsoft SQL Server Integration Services. While the transfer of data from MySQL to Microsoft SQL Server 2008 is not fraught with any blocking issues, transfer of data from SQL Server 2008 to MySQL has presented various problems.


For data interchange with MySQL there are two options one of which can be accessed in the connection wizards of SQL Server Integration Services assuming you have installed the programs. The other can be used to set up a ODBC DSN as described further down. The two connection options are:

  • MySQL Connector/ODBC 5.1
  • Connector/Net 5.2 New versions 6.0 & 6.1
In this article we will be using the ODBC connector for MySQL which can be downloaded from the MySQL Site. The connector will be used to create an ODBC DSN. The article describes in detail how the transfer is carried out. Read on ....
this article brought to you by PACKT PUBLISHING.


MySQL Data Transfer using Sql Server Integration Services (SSIS)


Pick up the mechanics of working with SQL Server Integration 2005 under 10 days reading my no brainer book on SQL Server Integration Serivces.

Do you want to generate a Microsoft Chart chart from your Linq Data?

LINQ, short for Language Integrated Query, provides an object oriented approach to not only querying relational databases but also any kind of source such as XML, Collection of objects, etc. LINQ to SQL provides (O/R) object-relational mapping and Visual Studio 2008 IDE provides a (O/R) designer. Visual Studio 2008 also has a web server control called LinqDataSource control. This control requires a DataContext which is provided by the LINQ-to-SQL classes, a class generator that maps SQL objects to the model. Without this control one may have to generate the classes from scratch or by using the SQLMetal.exe utility which generates tables and columns.


This article brought to you by PACKT PUBLISHING shows how a table on your SQL Server 2008 can be bound to a LinqDataSource Control and then use it as the source of data for the chart. Read on...


Binding MS Chart Control to LINQ Data Source Control
DMCA.com Protection Status