Monday, December 29, 2008

Database Publishing Service used with Database Publishing Wizard

In the previous post (Useful SQL Database Transfer Tool) I mentioned Service that can be installed on a a server and receive requests directly from the Publishing Wizard.

This is an open source project. The compiled version can be downloaded from Microsoft SQL Server Database Publishing Services 1.1

I installed it using the defaults.

I had to change the IIS as follows to get it to run:

  1. Change the Virtual directory properties so that it is an application
    image
  2. Change to basic authentication
    image
    You can ignore the warning about passwords sent in free form because the service only accepts https: connections

Browse to https://localhost/SqlPublish/Publish/Service.asmx and you see these WEB Service methods:

  • BeginPublish
  • CancelPublish
  • EndPublish
  • GetServiceOptions
  • PublishData
  • PublishScript

I tested a transfer using a command line like:
SqlPubWiz publish -S QWERTY\SQLEXPRESS -d Northwind -RW https://localhost/SqlPublish/Publish/Service.asmx -RWU John -RWP hidden -RS QWERTY\SQLEXPRESS -RD Northwind2 -RU John -RP hidden

This failed because the certificate has an external name:

Error: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.

I changed the URL in the command above to: https://testing.company.com/SqlPublish/Publish/Service.asmx

I now get a .NET error

System.Data.SqlClient.SqlException: Login failed for user 'John'. The user is not associated with a trusted SQL Server connection.

I create a nw login with server authentication (a no-no normally)
image

I also changed the server properties do that it also allowed SQL Server authentication and restarted the SQL Server
image

I then had to give 'myself' permission to do stuffimage

Then it couldn't find Nortwind2 so I foolishly made "master" the DB and it created the tables in master.

It seems you have to create the DB first (who knows how you do this remotely?)

In summary:
SqlPubWiz publish -S KENTSTREET09\SQLEXPRESS -d Northwind -RW https://direct.148kentstreet.com/SqlPublish/Publish/Service.asmx -RWU John -RWP hidden -RS KENTSTREET09\SQLEXPRESS -RD master -RU myself -RP hidden

Useful SQL Database Transfer Tool

Microsoft SQL Server Database Publishing Wizard 1.1 is a great tool for transferring databases between sites.

It generates a single SQL script file which can be used to recreate a database (both schema and/or data) on a remote location. This is often used in a shared hosting environment where the only connectivity to a server is through a web-based control panel with a script execution window.

Developers and support staff can also use it collect troublesome databases from remote sites via email and zip files. This will generally use less bandwidth than a zipped backup because it will have no indexes or logs.

There is a command line option which would allow users on a remote site to run a batch file and email the resulting ZIP back to the support centre:
      sqlpubwiz script ( -C connection_string | -d local_database_name )
                       (output_file) [switches]

At 2.1 MB, it has a small enough footprint to be included in the installation package of most software products.

There is a corresponding WEB Service that can be installed on a a server and receive requests directly from this tool via the following command: 
sqlpubwiz publish ( -C connection_string | -d  local_database_name ) 
                       ( web_service_options ) [switches]

This WEB Service is probably only useful in an operational environment if you are an ISP, but it could be useful in a development environment. See: Database Publishing Service used with Database Publishing Wizard

The readme.txt file (below) includes some simple command line examples:

SQL Server Database Publishing Wizard
(c) Copyright Microsoft Corporation, 2006. All rights reserved.
Website: http://www.codeplex.com/sqlhost

=======================================
What is the Database Publishing Wizard?
=======================================

The Database Publishing Wizard enables the deployment of SQL Server databases
(both schema and data) into a shared hosting environment. The tool supports
both SQL Server 2005 and 2000 and does not require that source and target
servers are the same version.

The tool provides two modes of deployment:

1) It generates a single SQL script file which can be used to recreate
a database when the only connectivity to a server is through a
web-based control panel with a script execution window.

2) It connects to a web service provided by your hoster and directly
creates objects on a specified hosted database

The tool may also be used by hosters to script out databases for backup or
transfer purposes.

=====
Usage
=====

The Database Publishing Wizard provide both a graphical and a command-line
interface. To use the graphical interface, simply execute "sqlpubwiz.exe"
without any arguments.

To retrieve details on the arguments supported by the command-line
interface, execute the following command:

sqlpubwiz help


The tool also integrates directly into Visual Web Developer 2005 Express
Edition and all non-Express SKUs of Visual Studio. Right click on any
SQL Server database connecton and select "Publish to provider..." to launch
the wizard.

For further details on usage please see:

http://www.codeplex.com/Wiki/View.aspx?ProjectName=sqlhost&title=Database%20Publishing%20Wizard

======================================
Simple Command Line Scripting Examples
======================================

The following command will script the FooDB database existing on the local
machine and default instance using the Windows credentials of the executing
user to C:\FooDB.sql:


sqlpubwiz script -d FooDB C:\FooDB.sql


The following command will script the FooDB database from the default
instance on a machine named MYSERVER using SQL Server authentication with
the username "Alice" and the password "7h92-v6k3" to the file C:\FooDB.sql:


sqlpubwiz script -d FooDB -S MYSERVER -U Alice -P 7h92-v6k3 C:\FooDB.sql

============
Known Issues
============

For a list of known issues, please see:
http://www.codeplex.com/Wiki/View.aspx?ProjectName=sqlhost&title=DPW%20Known%20Issues

================================================
Support, Feedback, Bug Reports, Feature Requests
================================================

For support and any feedback on the tool, please use the following forum:
http://www.codeplex.com/Project/ListThreads.aspx?ProjectName=sqlhost&ForumId=1807

Friday, December 5, 2008

VB6 Developers - Can't use WebBrowser Control since IE7 installed

This should not happen for run time users.

When you open a project that is using the class SHDocVwCtl.WebBrowser (SHDOCVW.dll), you might get this error message:

File Not Found ieframe.dll\1

If you didn't save your project file after you got the error message:

  1. Exit VB6
  2. Run regedit
  3. Edit | Find
    image
    ieframe.dll\1
  4. You should find:
  5. Change the default value by removing "\1"
    image
  6. Open the project in VB6 and everything should be fine

If you saved the project after the error message, the vbp file will be damaged

FIRST FIX THE REGISTRY AS ABOVE

Option 1:    go to your backup vbp

Option 2:   

  1. edit the vbp file with notepad
  2. insert the following line in the top section
    Object={EAB22AC0-30C1-11CF-A7EB-0000C05BAE0B}#1.1#0; shdocvw.dll

Good Luck.

Thursday, December 4, 2008

Google v Microsoft "Chart" Components

 

Microsoft is offering Chart Controls "free". See: New ASP.NET Charting Control: <asp:chart runat="server"/>

In the mean time Google provides the same thing via their online API: See Google Chart API. The 3d piechart on the right is built dynamically using the is url: http://chart.apis.google.com/chart?cht=p3&chd=t:60,40&chs=250x100&chl=Hello|World

Change to a Two dimensional pie chart (cht=p): http://chart.apis.google.com/chart?cht=p&chd=t:60,40&chs=250x100&chl=Hello|World

Or a barchart (cht=bvs): http://chart.apis.google.com/chart?cht=bvs&chd=t:60,40&chs=250x100&chl=Hello|World

Or a QR code (cht=qr): http://chart.apis.google.com/chart?chs=150x150&cht=qr&chl=Hello%20world&choe=UTF-8

QR code

And if you're building a dashboard, what about a Google-o-meter (cht=gom): http://chart.apis.google.com/chart?chs=225x125&cht=gom&chd=t:70&chl=Hello

Google-o-meter with default red to green coloring