MCITP: Database Administrator

I finally took and passed the 070-444 exam, and in my opinion, it far easier than the 070-443 exam. Had I known this before hand, I would have taken it sooner. Now my focus will be switched to Oracle 10g following my jobs requirements, but I am still working in SQL Server as well.

Loading all SQL Servers into SSMS From Network (VBScript)

Previously I posted how to enumerate your SQL Servers into the XML regsrvr file that can be imported into SSMS to automatically register all SQL Servers on your domain in SSMS. Knowing that not everyone would want to install Visual C# or Visual Studio to build the console app, I also worked on the following VBScript which does the same exact job, using the same tools.

Script:

Set fso = CreateObject("Scripting.FilesystemObject")
Set objSQLDMOApp = CreateObject("SQLDMO.Application")

Set objSQLList = objSQLDMOApp.ListAvailableSQLServers()

set a = fso.createtextFile("c:\2005_SSMS.regsrvr")

RepeatTab = 1
Tab = " "

a.Writeline ("<?xml version=""1.0"" encoding=""utf-8""?>")
a.Writeline ("<Export serverType=""8c91a03d-f9b4-46c0-a305-b5dcc79ff907"">")
a.Writeline (RepeatString(Tab,RepeatTab) & "<ServerType id=""8c91a03d-f9b4-46c0-a305-b5dcc79ff907"" name=""Database Engine"">")
a.Writeline (RepeatString(Tab,RepeatTab) & "<Group name=""SQL Locator Import"" description=""Servers Located by SQL Locator and Imported into SSMS"">")

RepeatTab = RepeatTab +1

For i = 1 To objSQLList.Count
servername = objSQLList.Item(i)

a.Writeline (RepeatString(Tab,RepeatTab) & "<Server name=""" & servername & """ description="""">")
RepeatTab = RepeatTab +1
a.Writeline (RepeatString(Tab,RepeatTab) & "<ConnectionInformation>")
RepeatTab = RepeatTab +1
a.Writeline (RepeatString(Tab,RepeatTab) & "<ServerType>8c91a03d-f9b4-46c0-a305-b5dcc79ff907<⁄ServerType>")
a.Writeline (RepeatString(Tab,RepeatTab) & "<ServerName>" & servername & "<⁄ServerName>")
a.Writeline (RepeatString(Tab,RepeatTab) & "<AuthenticationType>0<⁄AuthenticationType>")
a.Writeline (RepeatString(Tab,RepeatTab) & "<UserName ⁄>")
a.Writeline (RepeatString(Tab,RepeatTab) & "<Password ⁄>")
a.Writeline (RepeatString(Tab,RepeatTab) & "<AdvancedOptions>")
RepeatTab = RepeatTab +1
a.Writeline (RepeatString(Tab,RepeatTab) & "<PACKET_SIZE>4096<⁄PACKET_SIZE>")
a.Writeline (RepeatString(Tab,RepeatTab) & "<CONNECTION_TIMEOUT>15<⁄CONNECTION_TIMEOUT>")
a.Writeline (RepeatString(Tab,RepeatTab) & "<EXEC_TIMEOUT>0<⁄EXEC_TIMEOUT>")
a.Writeline (RepeatString(Tab,RepeatTab) & "<ENCRYPT_CONNECTION>False<⁄ENCRYPT_CONNECTION>")
RepeatTab = RepeatTab -1
a.Writeline (RepeatString(Tab,RepeatTab) & "<⁄AdvancedOptions>")
RepeatTab = RepeatTab -1
a.Writeline (RepeatString(Tab,RepeatTab) & "<⁄ConnectionInformation>")
RepeatTab = RepeatTab -1
a.Writeline (RepeatString(Tab,RepeatTab) & "<⁄Server>")

Next
RepeatTab = 2
a.Writeline (RepeatString(Tab,RepeatTab) & "<⁄Group>")
RepeatTab = 1
a.Writeline (RepeatString(Tab,RepeatTab) & "<⁄ServerType>")
RepeatTab = 0
a.Writeline (RepeatString(Tab,RepeatTab) & "<⁄Export>")


Set Args = wScript.Arguments

If Args.Count = 0 then
msgbox "Registry Import File is ready", vbInformation+vbOKonly
end if

a.Close()

Set a = Nothing
Set objSQLList = Nothing
Set objSQLDeeMOApp = Nothing

Function RepeatString(strInput, intCount)
Dim arrTmp()
ReDim arrTmp(intCount)
RepeatString = Join(arrTmp, strInput)
End Function

wscript.quit

Simply copy and paste the above code into a .vbs file and run it. The output will be in your root C directory.

Difference in JOIN Sort with GUID 2000 / 2005

I found this issue while reviewing a suspected code error in a .NET application I am working on. There is a difference in join sort order between SQL 2000 and SQL 2005. Before I get into the details of this, I want to first point out that joining tables in the manner that occurs here is not, generally speaking, a best practice. I generally don't join tables on non-PrimaryKey/ForeignKey columns, but in this case that is what was done in SQL code.

A few tables are required to demonstrate this issue. I keep a general use Sandbox database on all my servers where I can do things like stage data, or create test tables to test issues as I find them, or they get reported on the SQL Forums. You will need a SQL Server 2000 database server and a SQL Server 2005 database server to test this.

Table Scripts:

CREATE TABLE dbo.xr_Table1_Table2
(
id int NOT NULL IDENTITY (1, 1),
table1key int NOT NULL,
table2key int NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.xr_Table1_Table2 ADD CONSTRAINT
PK_xr_Table1_table2 PRIMARY KEY CLUSTERED
(
table1key,
table2key
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table1] (
[table1key] [int] IDENTITY (1, 1) NOT NULL ,
[datacol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[guid] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[table1key]
) ON [PRIMARY]
GO
CREATE TABLE dbo.Table2
(
table2key int NOT NULL IDENTITY (1, 1),
datacol varchar(50) NOT NULL,
datacol2 varchar(50) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table2 ADD CONSTRAINT
PK_Table2 PRIMARY KEY CLUSTERED
(
table2key
) ON [PRIMARY]
GO

Script to generate some test data:

DECLARE @Loopvar int
SET @Loopvar = 1
WHILE @Loopvar < 100
BEGIN
INSERT INTO Table2 (datacol, datacol2)
VALUES ('This is a test #' + convert(varchar, @loopvar), convert(varchar, @loopvar * 3.1459))
SET @Loopvar = @Loopvar +1
END

And finally the script to show the difference:

DECLARE @temp TABLE
(
id int identity primary key,
datacol varchar(50),
guid uniqueidentIFier,
table2key int,
table1key int
)
DECLARE @loop2 int
DECLARE @guid uniqueidentifier

SELECT @loop2 = min(table2key)
FROM table2

WHILE @loop2 IS NOT NULL
BEGIN
IF @loop2 not in (25, 50, 75)
BEGIN
SET @guid = newid()
END
INSERT INTO @temp (datacol, guid, table2key)
SELECT datacol, @guid, @loop2
FROM table2
WHERE table2key = @loop2

SELECT @loop2 = min(table2key)
FROM table2
WHERE table2key > @loop2
END

BEGIN TRANSACTION

INSERT INTO table1 (datacol, guid)
SELECT datacol, guid
FROM @temp

UPDATE t
SET table1key = t1.table1key
FROM @temp t
JOIN table1 t1 ON t.guid = t1.guid

INSERT INTO xr_table1_table2 (table1key, table2key)
SELECT table1key, table2key
FROM @temp

SELECT * FROM @temp
SELECT * FROM table1
SELECT * FROM table2
SELECT * FROM xr_table1_table2

ROLLBACK TRANSACTION

Results in SQL 2000:

@TEMP DATA

id datacol guid table2key table1key
----- -------------------------- ---------------------- ------------------------------------ ----------- -----------
24 This is a test #24 88914477-CC69-4AB8-ACCA-9CD21C51CB2B 24 619
25 This is a test #25 88914477-CC69-4AB8-ACCA-9CD21C51CB2B 25 619


TABLE 1 DATA
table1key datacol guid
----------- ---------------------------- ------------------------------------
618 This is a test #24 88914477-CC69-4AB8-ACCA-9CD21C51CB2B
619 This is a test #25 88914477-CC69-4AB8-ACCA-9CD21C51CB2B

Results in SQL 2005:

@TEMP DATA
id datacol guid table2key table1key
----- -------------------------- ---------------------- ------------------------------------ ----------- -----------
24 This is a test #24 29B0B6F1-E36A-4CC5-BA0A-52A0DD3F6633 24 321
25 This is a test #25 29B0B6F1-E36A-4CC5-BA0A-52A0DD3F6633 25 321


TABLE 1 DATA
table1key datacol guid
----------- ---------------------------- ------------------------------------
321 This is a test #24 29B0B6F1-E36A-4CC5-BA0A-52A0DD3F6633
322 This is a test #25 29B0B6F1-E36A-4CC5-BA0A-52A0DD3F6633

For whatever reason, on this type of join, SQL 2000 sorts descending, and SQL 2005 sorts ascending. I didn't report this issue on Connect because I am not sure that it really is an issue. It was surprising to find however.

Loading all SQL Servers into SSMS From Network (.NET)

Recently a post on the MSDN forums asked how to import all SQL Servers on a Network into SSMS automatically. There actually is not a good implementation of this anywhere that I have found. So I worked out the following in C# as a console app. I am posting full source so that anyone can review what it is doing. I had the SQLLocator class from somewhere and I don't have a reference for it, but will figure out where I got it and provide credit here soon. It is not my work, but it does an amazing job for what I wanted to do, and it was open source.


using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
using System.IO;

namespace SSMSImportGeneration
{
class Program
{
static void Main(string[] args)
{
string[] theAvailableSqlServers = SqlLocator.GetServers();
if (theAvailableSqlServers != null)
{
WriteFile(theAvailableSqlServers);
}
else
{
Console.WriteLine("No SQL servers found.");
}
}

private static void WriteFile(string[] data)
{
FileStream file = new FileStream("c:\\SQLImport.regsrvr", FileMode.OpenOrCreate, FileAccess.Write);
StreamWriter sw = new StreamWriter(file);
string importdata = "";
importdata = "<?xml version=\"1.0\" encoding=\"utf-8\"?>\n";
importdata += " <Export serverType=\"8c91a03d-f9b4-46c0-a305-b5dcc79ff907\">\n";
importdata += " <ServerType id=\"8c91a03d-f9b4-46c0-a305-b5dcc79ff907\" name=\"Database Engine\">\n";
importdata += " <Group name=\"SQL Locator Import\" description=\"Servers Located by SQL Locator and Imported into SSMS\">\n";

foreach (string server in data)
{
importdata += " <Server name=\"" + server + "\" description=\"\">\n";
importdata += " <ConnectionInformation>\n";
importdata += " <ServerType>8c91a03d-f9b4-46c0-a305-b5dcc79ff907</ServerType>\n";
importdata += " <ServerName>" + server + "</ServerName>\n";
importdata += " <AuthenticationType>0</AuthenticationType>\n";
importdata += " <UserName />\n";
importdata += " <Password />\n";
importdata += " <AdvancedOptions>\n";
importdata += " <PACKET_SIZE>4096</PACKET_SIZE>\n";
importdata += " <CONNECTION_TIMEOUT>15</CONNECTION_TIMEOUT>\n";
importdata += " <EXEC_TIMEOUT>0</EXEC_TIMEOUT>\n";
importdata += " <ENCRYPT_CONNECTION>False</ENCRYPT_CONNECTION>\n";
importdata += " </AdvancedOptions>\n";
importdata += " </ConnectionInformation>\n";
importdata += " </Server>\n";
}
importdata += " </Group>\n";
importdata += " </ServerType>\n";
importdata += " </Export>\n";

sw.Write(importdata);
sw.Close();
file.Close();
Console.WriteLine("File Created in Root c:\\SQLImport.regsrvr");
}
}

public class SqlLocator
{
[DllImport("odbc32.dll")]
private static extern short SQLAllocHandle(short hType, IntPtr inputHandle, out IntPtr outputHandle);
[DllImport("odbc32.dll")]
private static extern short SQLSetEnvAttr(IntPtr henv, int attribute, IntPtr valuePtr, int strLength);
[DllImport("odbc32.dll")]
private static extern short SQLFreeHandle(short hType, IntPtr handle);
[DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
private static extern short SQLBrowseConnect(IntPtr hconn, StringBuilder inString,
short inStringLength, StringBuilder outString, short outStringLength,
out short outLengthNeeded);

private const short SQL_HANDLE_ENV = 1;
private const short SQL_HANDLE_DBC = 2;
private const int SQL_ATTR_ODBC_VERSION = 200;
private const int SQL_OV_ODBC3 = 3;
private const short SQL_SUCCESS = 0;

private const short SQL_NEED_DATA = 99;
private const short DEFAULT_RESULT_SIZE = 1024;
private const string SQL_DRIVER_STR = "DRIVER=SQL SERVER";

private SqlLocator() { }

public static string[] GetServers()
{
string[] retval = null;
string txt = string.Empty;
IntPtr henv = IntPtr.Zero;
IntPtr hconn = IntPtr.Zero;
StringBuilder inString = new StringBuilder(SQL_DRIVER_STR);
StringBuilder outString = new StringBuilder(DEFAULT_RESULT_SIZE);
short inStringLength = (short)inString.Length;
short lenNeeded = 0;

try
{
if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_ENV, henv, out henv))
{
if (SQL_SUCCESS == SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (IntPtr)SQL_OV_ODBC3, 0))
{
if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_DBC, henv, out hconn))
{
if (SQL_NEED_DATA == SQLBrowseConnect(hconn, inString, inStringLength, outString,
DEFAULT_RESULT_SIZE, out lenNeeded))
{
if (DEFAULT_RESULT_SIZE < lenNeeded)
{
outString.Capacity = lenNeeded;
if (SQL_NEED_DATA != SQLBrowseConnect(hconn, inString, inStringLength, outString,
lenNeeded, out lenNeeded))
{
throw new ApplicationException("Unabled to aquire SQL Servers from ODBC driver.");
}
}
txt = outString.ToString();
int start = txt.IndexOf("{") + 1;
int len = txt.IndexOf("}") - start;
if ((start > 0) && (len > 0))
{
txt = txt.Substring(start, len);
}
else
{
txt = string.Empty;
}
}
}
}
}
}
catch (Exception ex)
{
//Throw away any error if we are not in debug mode
#if (DEBUG)
Console.WriteLine(ex.Message, "Acquire SQL Servier List Error");
#endif
txt = string.Empty;
}
finally
{
if (hconn != IntPtr.Zero)
{
SQLFreeHandle(SQL_HANDLE_DBC, hconn);
}
if (henv != IntPtr.Zero)
{
SQLFreeHandle(SQL_HANDLE_ENV, hconn);
}
}

if (txt.Length > 0)
{
retval = txt.Split(",".ToCharArray());
}
return retval;
}
}
}