No disk space on SQL Server 2005 (pt 2)

Continuing on from my previous post here we once again ran out of disk space (this time whilst trying to apply Win2003 SP2 to one of the servers). I used the freeware version of TreeSize to look at where the space was going and noticed that nearly 1Gb was in use in:

C:/Program Files/Microsoft SQL Server/MSSQL.3/Reporting Services/LogFiles/

Upon further investigation I noted that Reporting Services (SSRS) was keeping execution logs for 60 days, this included the exceptions when someone attempted a report which they had no access to. To check go in to SQL Server Management Studio->Connect->Reporting Services…->Connect. Then right-click the SSRS node in the tree and click properties. See the screenshot below for how it looked once I’d set it to 5days which for us is enough time to identify a problem one it is reported:

Clients don’t connect with WSUS 3.0 on non-standard port 8530

I’ve just installed WSUS 3.0 on a new server and decommissioned WSUS 2.0 on our old server. On our 2.0 server I had both WSUS and Sharepoint installed which presented me with a few issues when I tried to upgrade it to 3.0.

For this reason I chose to install WSUS 3.0 on the alternative port of 8530, leaving port 80 for other uses:

However having set the Group Policy to simply http://mercury for both settings and waiting a day, no clients were registered. I trawled through all of the documentation and there was no reference to doing anything different if WSUS was not on port 80. You’ll notice from the IIS setup that it did indeed create virtual directories on the port 80 web implying that this should work.

Well, here’s the answer, YOU HAVE TO SET THE PORT NUMBER on the URL, this would also apply if you were directly fiddling with the settings in the registry at:

HKEY_LOCAL_MACHINE/SOFTWARE/Policies/Microsoft/Windows/WindowsUpdate

Happy WSUS’ing.

Connecting SQL Server 2005 x64 to SQL Server 7 (32bit) as a linked server!?

We were having to use SSIS to pull data back and forth from an old SQL Server 7 server to our main production server which runs SQL Server 2005 SP2 x64 standard.

When I tried to create the linked server using:

 EXEC sp_addlinkedserver   
   @server='MySQLServer7'

Then I try

SELECT * FROM [MySQLServer7].[msdb].[dbo].[sysfiles] -- Or any other SQL

I get the following error

OLE DB provider "SQLNCLI" for linked server "MySQLServer7" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "MySQLServer7" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 9
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "OSLO". The provider supports the interface, but returns a failure code when it is used.

Now the solution for connecting to SQL Server 2000 32-bit is to run instcat.sql as referenced at http://support.microsoft.com/default.aspx?scid=kb;en-us;906954

However, this isn’t really an option on SQL Server 7.0, luckily I found a very helpful post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=62887&SiteID=1 by Marek Adamczuk explaining that it’s simply that 32-bit editions are missing a stored procedure on the master database called sp_tables_info_rowset_64.

Marek’s solution is to create a wrapper on the master database as follows (his code not mine):

create procedure sp_tables_info_rowset_64

      @table_name sysname,

      @table_schema     sysname = null,  

      @table_type nvarchar(255) = null

as

declare @Result int set @Result = 0

exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type

This works a treat – cheers Marek!