VBScript to Display DBCC INPUTBUFFER

The VBScript code snippet below uses SQLDMO to read the EventInfo column from DBCC INPUTBUFFER for a specified SPID:

ie. Read the EventInfo as illustrated below:

DBCC INPUTBUFFER(53)
EventType      Parameters EventInfo
————– ———- ———————-
Language Event 0          sp_who2
(1 row(s) affected)
~~~
Dim SQLServer
Set SQLServer = CreateObject(“SQLDMO.SQLServer”)
SQLServer.LoginSecure = True
SQLServer.Connect(“127.0.0.1\test”)
‘ Display the InputBuffer EventInfo
MsgBox InputBuffer(53, SQLServer)
SQLServer.DisConnect
Set SQLServer = Nothing

Function InputBuffer(spid, SQLServer)Dim QueryResult

    Set QueryResult = SQLServer.ExecuteWithResults(“dbcc inputbuffer(” & spid & “)”)
If (QueryResult.Rows <> 1) Then
InputBuffer = “”
Else
InputBuffer = QueryResult.GetColumnString(1, 3)
End If
Set QueryResult = Nothing

End Function
~~~

Reporting Services ASP.NET v1.1 Not Installed

I have finally had an opportunity this morning to re-build my laptop with the RTM of Visual Studio 2005 and SQL Server 2005. One of the interesting things that I have found along the way when installing SQL Server 2000 Reporting Services, is during the “Check Prerequisites” stage I received the error “ASP.NET v1.1 Not Installed” after installing Visual studio 2003 and Visual Studio 2005.  This did not seem quite right as I had just tested that VS 2003 and VS 2005 co-existed happily by building and testing an 2003 ASP.Net application I have been working on.

So I decided to fire up RegMon (http://www.sysinternals.com/Utilities/Regmon.html) to see what the Reporting Services install was checking in the Registry  to determine that “ASP.NET v1.1 Not Installed”.  It turns out that the RS install is checking the version of the ASP.Net using the key [HKLM\SOFTWARE\Microsoft\ASP.NET\RootVer].  The Value for this key was 2.0.50727.42 as a result of installing Framework v2 with VS 2005.  I modified the RootVer value temporarily back to 1.1.4322.573 and the “Check Prerequisites” passed without an issue.

New Database default locations

In SQL Server 2000 there are two ways to identify the New Database default locations ie. where the data and log files are created when a database is created without specifying a filespec for the data or log file (CREATE DATABASE blah).  These values are stored in the registry and can be queried using SQL Server Enterprise Manager by Right clicking on the SQL Server instance you wish to check and selectingProperties and selecting the Database Settings tab.

Or by executing the TSQL statements below to read the values directly from the registry:

EXEC master.dbo.xp_instance_regread N’HKEY_LOCAL_MACHINE’,N’Software\Microsoft\MSSQLServer\MSSQLServer’,N’DefaultData’
EXEC master.dbo.xp_instance_regread N’HKEY_LOCAL_MACHINE’,N’Software\Microsoft\MSSQLServer\MSSQLServer’,N’DefaultLog’
Returns:
Value        Data
———— ————-
DefaultData  c:\MSSQL\Data
(1 row(s) affected)
Value        Data                                                                                                                                                                                                                                                             ———— ————-
DefaultLog   c:\MSSQL\Log
(1 row(s) affected)
The values can be updated using Enterprise Manager or using the TSQL example below.  In the example below the New Database default locations directory for both the data and log is being changed to c:\foo.  Note:  The SQL Server service needs to be restarted for these changes to take effect.
EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’,N’Software\Microsoft\MSSQLServer\MSSQLServer’,N’DefaultLog’,REG_SZ,N’c:\foo’
EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’,N’Software\Microsoft\MSSQLServer\MSSQLServer’,N’DefaultData’,REG_SZ,N’c:\foo’

Brisbane Sharepoint User Group (BSPUG)

The Brisbane Sharepoint User Group had their inaugural meeting Yesterday. The group plan to meet between 12:00-13:30 on the 2nd Wednesday of each month at the Brisbane Microsoft office (Level 9, Waterfront Place, 1 Eagle St, Brisbane). If you would like an invite to the next event or are after additional details refer to http://blogs.msdn.com/cjwalker/archive/2005/11/09/490710.aspx.

Microsoft SQL Server Management Studio Express – November 2005 CTP

Microsoft SQL Server Management Studio Express (SSMSE) provides a graphical management tool for SQL Server 2005 Express Edition (SQL Server Express) instances. SSMSE can also manage relational engine instances created by any edition of SQL Server 2005. SSMSE cannot manage Analysis Services, Integration Services, SQL Server 2005 Mobile Edition, Notification Services, Reporting Services, or SQL Server Agent.

http://www.microsoft.com/downloads/details.aspx?FamilyId=82AFBD59-57A4-455E-A2D6-1D4C98D40F6E&displaylang=en

Divide by zero error encountered

The example below shows a workaround for a Divide by zero error encountered. error by using the NULLIF Function. NULLIF returns the first expression if the two expressions are not equivalent otherwise a NULL is returned.

Usage: SELECT NULLIF(col1, 0)

IF col1 = 0 then a NULL will be returned.
CREATE TABLE dbo.Products
(
Product VARCHAR(10),
Price NUMERIC(7, 2),
CostPrice NUMERIC(7, 2)
)

INSERT dbo.Products SELECT ‘Hammer’, 12.00, 6.00
INSERT dbo.Products SELECT ‘Nails’,  0.10, 0.05
INSERT dbo.Products SELECT ‘Screws’, 0, 0.05

SELECT Product, (CostPrice / Price) * 100 AS PercentProfit FROM dbo.Products

Will Produce the following Error:

(3 row(s) affected)

Server: Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

SELECT Product, (CostPrice / NULLIF(Price, 0))* 100 AS PercentProfit FROM dbo.Products

Returns:

Product    PercentProfit
———- ———————–
Hammer     50.0000000000
Nails      50.0000000000
Screws     NULL

(3 row(s) affected)

Date for Monday and Friday of Current Week

The Date for Monday and Friday of the current week can be determined as illustrated below.

SELECT GETDATE() + (2 – DATEPART(WEEKDAY, GETDATE())) AS MONDAY,GETDATE() + (6 – DATEPART(WEEKDAY, GETDATE())) AS FRIDAY

This assumes that you consider the first day of the week to be a Sunday and that the last day of the week is a Saturday.  If this is not the case you will need to use the SET DATEFIRST to set the first day of the week to a number from 1 through 7.  ie. SET DATEFIRST 1 to set MONDAY.  To determine the day that is currently set as the first day of the week run SELECT @@DATEFIRST (by default this is 7 which is Sunday).