Make your own free website on Tripod.com

Microsoft SQL Server tools FAQ
Last updated: February 10st '02 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

About myself
My technical skills
My favorites
My picture album

Shortcut keys
My code library

VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources

Search my site
Sign my guestbook
Contact information

SQL Server Articles New




Receive & Send money over the net easily!
 

You are here: Home > Microsoft SQL Server FAQ > Tools FAQ

NEW!!! Subscribe to my newsletter:
Want to keep in touch with the latest in SQL Server world? Email vyaskn@hotmail.com with 'subscribe' in the subject line

Output in Query Analyzer seems truncated. I can see only the first 255 characters per line. What's wrong?

How to hide/show system tables and other system objects in SQL Server Enterprise Manager?

How to save database diagrams to gif, jpg or other image formats?

How to register the same SQL Server twice in enterprise manager, probably with different registration properties for testing purposes?

How to transfer database diagrams to a different database?

How to send emails in HTML format from SQL Server using xp_sendmail?

My Enterprise Manager times out while connecting to a server over WAN (Internet). How can I specify timeout values for Enterprise Manager?

In Enterprise Manager, I see a red squiggle or a red zigzag line over my server icon. Is something wrong with my server?

I can change the login audit levels in Enterprise Manager. But how can I do the same programmatically?

How to overwrite DTS error log files everytime the package runs, instead of appending to the log file?

Output in Query Analyzer seems truncated. I can see only the first 255 characters per line. What's wrong? <top>

By default Query Analyzer (ISQLW) displays only 256 characters per line in the results tab. You can configure Query Analyzer to display a maximum of 8192 lines per line. Follow these steps:

If you are using the Query Analyzer of Server 2000, go to Tools -> Options -> Results tab -> Enter the number 8192 in the "Maximum characters per column:" text box.

If you are using the Query Analyzer of Server 7.0, go to Query -> Current Connection Options -> Advanced tab -> Enter the number 8192 in the "Maximum characters per column:" text box.

How to hide/show system tables and other system objects in SQL Server Enterprise Manager? <top>

Open Enterprise Manager. Right click on your server name and choose 'Edit SQL Server Registration properties...' from the popup menu. This presents the 'Registered SQL Server Properties' dialog box. In the bottom half of this dialog box, there is a check box against 'Show system databases and system objects'. Check it to show system databases/tables or uncheck it to hide system databases/tables.

How to save database diagrams to gif, jpg or other image formats? <top>

Unfortunately, there is no direct support in Enterprise Manager for saving the database diagrams as image files (at least until SQL Server 2000). As a workaround, take screenshots by pressing 'Print Screen' button on your keyboard and paste the image into your favorite image editing tool like Windows Paintbrush.

How to register the same SQL Server twice in enterprise manager, probably with different registration properties for testing purposes? <top>

Use "Client Network Utility" to add an alias (a different name) to the SQL Server. Use this alias name in Enterprise Manager to register the SQL Server. So, now you have an SQL Server registered with the actual name, as well as with a different alias name. This is useful for verifying permissions as you could register the SQL Server with logins having different privileges.

"Client Network Utility" can be accessed from Start -> Programs -> Microsoft SQL Server.

How to transfer database diagrams to a different database? <top>

Database diagrams are stored in the 'dtproperties' table within the database. So, database diagrams can be transferred to a different database, by transferring the contents of this table.

For example , run the following query to transfer the diagram named 'MyTableDesign' from 'pubs' database to 'northwind':

INSERT northwind..dtproperties
SELECT objectid, property, value, lvalue, version
FROM pubs..dtproperties
WHERE objectid =
(
SELECT objectid
FROM pubs..dtproperties
WHERE value='MyTableDesign'
)


Make sure, the tables referenced by these diagrams already exist in the target database, or else these diagrams won't show up in the target database.

If the target database already has a diagram with the same 'objectid', you will see unpredictable results, when Enterprise Manager displays the diagrams. In that case, you might want to explicitly specify a unique value to the 'objectid' column while transferring rows from the source 'dtproperties' table.

How to send emails in HTML format from SQL Server using xp_sendmail?<top>

xp_sendmail has no support for HTML formatted emails. So it is not possible to send HTML emails using xp_sendmail. However, there are a couple of alternatives. You could use CDONTS or any other third party mailing component like ASPEmail from within your stored procedures to send HTML emails. Read the following Knowledge Base article for some examples:

HOW TO: Send E-Mail from SQL Server Without Using SQL Mail (Q312839)

My Enterprise Manager times out while connecting to a server over WAN (Internet). How can I specify timeout values for Enterprise Manager?<top>

In SQL Server 7.0 Enterprise Manager, go to Tools -> Options.... This will open up the dialog box "SQL Server Enterprise Manager properties". Go to "Connection" tab. Specigy the login time-out and query time-out values in seconds. Click OK.

Procedure is the same in SQL Server 2000 Enterprise Manager, only difference being, the "Connection" tab is renamed as "Advanced" tab.

In Enterprise Manager, I see a red squiggle or a red zigzag line over my server icon. Is something wrong with my server?<top>

No, there is nothing wrong with your server. That red squiggle is indicating that Enterprise Manager has established a connection to your SQL Server.

You will also see a similar line over the SQL Server Service Manager icon in your taskbar. This line appears when SQL Server Service Manager polls for the server status, and immediately disappears.

I can change the login audit levels in Enterprise Manager. But how can I do the same programmatically?<top>

To change the Audit levels, Enterprise Manager is calling an undocumented extended registry stored procedure that alters the registry entries. You can see that using Profiler.

On a default instance of SQL Server 2000, the following commands can be used to control the audit levels.

To audit both success and failure logins:
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',N'AuditLevel', REG_DWORD,3

To audit successful logins:
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',N'AuditLevel', REG_DWORD,1

To audit failures:
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',N'AuditLevel', REG_DWORD,2

To stop auditing:
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',N'AuditLevel', REG_DWORD,0