Monthly Archives: August 2009

Linked Server 64bit Deficiency

First off, I need to give recognition to Joel Mansford for his blog which gave me the answer to my problem.
Connecting SQL Server 2005 x64 to SQL Server 7 (32bit) as a linked server!?

After creating a linked server on a 64bit SQL Server 2005 instance, I then needed to create a view which referenced the linked server. The linked server is a SQL Server 2000 32bit instance. However, when I tried to create the view, I got this error:  Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI”.

This really proved to be a conundrum because I had just recently created the same linked server and view on another SQL 2005 instance. However, I had not yet connected the fact the 2005 instance was 32bit as opposed to the one I was now working on, which is 64bit.

I wrestled with it being a permissions issue for a while but ruled that out when I could not create the view even while using a domain admin account having authority of all the instances. A search on Google, using the above mentioned error message, turned up Joel’s blog. His specific problem was relative to SQL Server 7 but the answer applied to SQL Server 2000 also. He had found the original post of the ultimate solution and was good enough to include it in his blog which I have an excerpt here.

I found an extremely helpful post by Marek Adamczuk at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=62887&SiteID=1. Marek  explains the 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

It would appear the existing sproc does the same thing but just needs to be referenced using the 64bit name. Kudos to both Joel and to Marek for making this solution available!

PowerShell Where Clause – Compare to a list of values

As a databases admin with a few dozen servers and a few hundred SQL Server database, I need to effectively build automated jobs to perform various administrative tasks such as backing up databases. I’m still new to PowerShell but I am beginning to see how immensely helpful it is to me and my work.

I found a good PowerShell script for backing up databases at this blog location. However, I wanted to change it slightly to accommodate backing up ALL, System, User, or a list of databases. Processing ALL, System, or User is straightforward enough but I had to it was the first time I came up against have to compare a value against a list of values. In SQL you simply use an IN operator with a list of values but I was not sure of how to accomplish the same process in PowerShell.

Here was my problem: the user could supply a list of databases to backup and the backup script has a line with captures all the database objects for that SQL Server instance. I need to backup only those which are in the users list.

I found that the PowerShell WHERE clause can use a -contains operator which effectively filters the selection for an array. To test the operation I set up a small script to build two arrays and then I piped one array through a WHERE filter of which the results are piped to a ForEach. The ForEach sees only the results of the WHERE which mimics the SQL IN operator.

Run this code in PowerShell to see what I mean.

$a = @("db1","db2","db3","db4")
$d = @("db1","db2","db3","db4","db5","db6","db7","db8")
$d | where { $a -contains $_ } | foreach { Write-Host $_ }

$a represents the list of database supplied by the user.
$d represents the array of databases returned when accessing the Microsoft.SqlServer.Management.Smo.Server object.
I can now back up the user supplied databases within the foreach script block.