Tuesday, July 17, 2007

SQL Server Agent starting problem

A lot of people(indluding me) had problem starting SQL Agent and saw the following error from Event Viewer:

SQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).

There is a resolution here.

This tip is go to EM->Management->SQL Server Agent->Properties->Connection, change windows authentication to SQL server authentication(you must provide an admin account/password such as sa).

This fault took place because SQLSERVERAGENT service was by default set to started with a 'Local System Account', while local accounts were usually not added into SQL Server logins and granted system admin role.

Thursday, July 12, 2007

My Portrait

My son's work.

A T-SQL script extracting DB schema info

Sometime I need to get a full view of a MS SQL database's schema details, e.g. user table and its columns, constraints, indexes, etc. And I prefer a plain text output instead of others (let's say XML). Since MS doesn't provide this and I can't find one from web, I wrote a script to implement this on my own:-

TSQL script

The output is tab spearated and can be easily imported into Excel/Access for a better look.

In write this I found TSQL in SQL 2000 is really depressing in constrast to PL/SQL. It event doesn't provide a convenient way of exporting output into an external file, as SPOOL command of PL/SQL does, unless COM is used. More terrible is the lack of support for parameterized cursor as well as 'inline' procedures or functions.

Does SQL 2005 provide any significant enhancements?