Novick Software SQL Server Consulting • Design • Programming • Training  

  sql server mvp

 

 

Tips and Tricks for: SQL Server, C#, VB, VB.Net, ASP.Net, ASP, COM, COM+, IIS, XML, ActiveX, Windows Server, etc.

These are just a few tips and tricks that I've encountered, usually while supporting a client.

Link to Solution

Description of the Problem

Windows Azure Solution Windows Azure dev storage fails to initialize if you don't have SQLExpress.

The Windows Azure tools for Visual Studio tools allow you to simulate Windows Azure storage with a local instance of SQL Server.  It assumes that you've installed SQL Server Express Edition when you installed Visual Studio.  If you didn't install SQL Express you run into the error message:

Windows Azure Tools: Failed to initialize the Development Storage service. Unable to start Development Storage. Failed to start Development Storage: the SQL Server instance ‘localhost\SQLExpress’ could not be found. Please configure the SQL Server instance for Development Storage using the ‘DSInit’ utility in the Windows Azure SDK.

Windows Solution A Blue Screen of Death (BSOD) followed my upgrade to Vista SP1

Here's how I solved it.

SQL Server Solution

How to load SQL Profiler trace (.trc) files into a table?

Server traces can produce a large amount of trace data that is stored in multiple trace (.trc) files.  They can be loaded into a table by going through SQL Profiler (2005 or 2000) but that's a cumbersome manual process.  It would be great if all the trace files from a profiler run could be loaded at once.
SQL Server Solution A Stalled SQL Server Agent cmdexec job.
After moving a job to a new server I found one of the jobs still running in the morning when I returned to work.  It was at a cmdexe step and hadn't used any CPU.
IpWorks
Solution
The IPWorks FTP.Download method didn't do anything!
I thought it would be simple to use the IPWorks FTP component to do a simple download of the files in a directory but the Download method wasn't doing anything.  I checked the source file (RemoteFile) and the destination directory (LocalDir) and they were fine.
SQL Server Solution The DISTINCT clause is case insensitive by default.

I was looking for names with bad case and they weren't showing up?

VB.Net Solution

SQL Server 2005 Solution

In SQL Server 2005 CLR Programming is disabled by default.

This script does the trick to change the 'CLR Enabled' server option.

Visual Studio .Net Solution

What do you do when a Windows Form (WinForm) won't enter the designer?

You see the standard class icon instead of the form icon and don't even get the chance to design the WinForm

SQL Server Solution

How do you add an extended property over 255 characters?

SQL Query Analyzer's UI limits extended properties to 255 characters.  This tip shows how to add bigger extended properties and the UDF required to get them back from SQL Server's sysproperties table.

Visual Studio Solution

How do you get an strong name for the Web Browser Control?

When you need a strongly named assembly for ShDocVw.Dll, this tip shows how.

SQL Server Solution

A Restore can leave the database in "LOADING" state

This happened to a client when trying a point in time restore but selecting to many transaction log files.  The RESTORE command can be used to change the state.

SQL Server Solution

How to get the GUID for a DTSRUN command line

DTSRUN is the way to automate running a DTS package. The command line requires the DTS package's GUID.  Constructing a correct DTSRUN command line can be akward but the DTSRUNUI utility is there to help.

SQL Server Solution

Error 2801 was recorded in a SQL Agent Job run by SQL Server

A SQL Agent Job has recorded Error 2801 in it's log.  This has to be dealt with to get the job up and running.

SQL Server
Partial Solution

Level 16 Errors Can't always be handled by User Code

Although books-OnLine says that user code can handle errors with Level 16 and below, they sometimes end the connection.

SQL Server Solution

Replication fails with Error 208 "Unable to replicate a view or function..."

This error occurs when a create script for a view or function fails because a database object that the view or function depends on hasn't been defined in the database when the script is run.  .

.Net Solution

Updater Application Block (UAB) is Broken by BITS 2.0

When BITS 2.0 is installed by Windows Update or the installation of Windows XP Serivce pack 2, applications that have used the Updater Application Block (UAB) stop updating themselves.

SQL Server Solution

Testing @@Error and  @@RowCount one after the other doesn't work.

The only thing that works is to capture @@Error and @@RowCount after each T-SQL statement and use the captured variables later.

SQL Server Solution

Constructing a WHERE clause that doesn't retrieve any rows.

Sometimes you don't want any rows.  See how.

Visual Basic .Net Solution

What is the difference between CType and DirectCast

VB.Net gives you two functions to cast from one type to another.  Which one should you use?  Casts are often required when Option Strict On is specified for the file.

Visual Studio .Net Solution

Controls disappear causing seemingly random errors in multi-project WinForms solutions.
The project usually loads then random errors appear in the Task List and seem to tell you that controls are missing from your forms.

Visual Studio
.Net Solution

When deploying the Infragistics Controls for WinForms there's no Merge Module Supplied
Control size adds up when used in multiple projects. Use a merge module (MSM) to install one copy of the controls in the global-assembly-cache (GAC)

Top 10 List

What utilities should be on every developer's computer?

Visual Studio Solution

Visual Studio .Net doesn't have an easy way to find all occurrences of a class or method.

Sometimes searching the text of your program for a string match, particularly for method names, isn't very valuable because there are several methods that match the string.

Visual Studio Solution

Unable to emit assembly: Referenced assembly 'Interop....' does not have a strong name

Visual Studio will give this message as soon as you give your own assembly a strong name even though Visual Studio had created the assembly when you first referenced the COM dll.

SQL Server Solution

Windows 2003 says that you can't install SQL Server 2000 with Service Pack 2 or below!

When you start installing SQL Server 2000 you get a rather ugly message about only installing SQL Server with Service Pack 3   Since you can't install a service pack without first installing SQL Server it looks like there's no way to complete the installation.

Win 2003 Solution

Windows 2003 rebooted in the middle of the day, taking down my database.

SQL Server
Solution

How to get a list of all the DBCC commands both documented and undocumented.

SQL Server
Solution

Length of text, ntext, or image data (x) to be replicated exceeds configured maximum 65536.

List of Object-Relational Mapping Tools

Mapping between Business Objects and a Relational Database is difficult

Creating the Business objects and stored procedures to implement a n-Tier design is a lot of work. A tool that can generate the data tier can cut that down to size.

C# Solution

Opening a Internet Explorer browser window to a specific URL

HTML Solution

Opening a new browser window when the user clicks a link.

Sometimes you don't want the user to leave your site when they click on a link. 

C# or VB.Net Solution

SEHException in System.Windows.Forms.UnsafeNamtiveMethods.DispatchMessageW

IIS Front Page Server Extenstions Solution

The Visual Interdev Web Project Wizard doesn't show all existing virtual directories

Upgrade Extensions or switch to Windows 2003 Server

FrontPage 2002 or 2003 gives the error: "The following pages in your web site contain link bars or navigation bars that use new formatting options."

C# Solution

How does one creating a COM object in .Net based on its ProgID?

Change the Dialog and Tools Font

The Visual Studio.Net Solution Explorer's Font Size is too Small for Presentations

The 9 point font used by default is to small when projected at 1024x768 or larger. This is a problem when demonstrating programs in Visual Studio.Net.

InfoPath
SQL Server
Solution

InfoPath doesn't allow a button to submit to the SQL Server database.

When creating a form that's connected to a SQL Server database InfoPath doesn't always allow a button to have the submit action with the target of the database.   It also doesn't allow Delete and Submit.

Windows Programming Solution

The Windows command prompt doesn't have autocomplete feature.  

The windows command prompt is rather dull.  It would benefit from an command completion feature that is commonly found in other operating systems.  I've also added a change in color.

SQL Server
Solution

The extended stored procedure xp_sql_agent_proxy_account returned the message "Access Denied" when doing either a 'GET' or 'SET' operation.

ASP Solution

ASP pages hang when they reference the Scripting.FileSystemObject.

COM+, VB
Solution

Installation of a COM+ application .MSI file fails.

ActiveX,VB
Solution

When downloading an ActiveX OCX with Internet Explorer all that shows up is a small square or maybe a small square with a red dot.

Visual Basic
Win32 API Solution

Background programs hog the CPU

When a computer is shared between programs that server users, such as IIS or VB forms based programs, and programs that have no user interface, such as background computational programs, the background programs can hog the CPU and reduce the response time to the user.

RSS Reader List

There are a lot of RSS feed readers available.  They're needed to check compatibility.

Anyone producing an RSS feed needs to check compatibility with the most common feed readers.  While they all adhere to a standard, they all seem to use a different version of the standard.  That is, almost no standard at all.


RSS as HTML

information about novick software site

Personal Blog

Photos

New Tips:

Use dsinit to set the SQL Server instance for Windows Azure dev storage

Upcoming
Presentations:

SQL PASS
Nov 7, '12
Biggest
Loser: DB
Edition


Full Schedule

 

 

Available Now

Transact-SQL
User-Defined Functions

Get it wtih the Bonus 100 UDF Library

Latest News

Novick Software Celebrates 10 Years in Business