Thursday, May 15, 2008

Undocumented extended stored procedure - xp_fileexist

This post was originally posted ni this blog entry

It's quite shocking to hear the word "undocumented" in SQL Server. This simply means that you can break your neck trying to find these topics in both the Books Online or Microsoft.com and you won't find anything. But there's always Google to take your chances. As I was trying to search for a VBScript to check for an existence of a file in a file system (it would be a lot easier in .NET but I'm limited to what I just have) when I chanced upon this extended stored procedure - xp_fileexist. DBAs do file system checks whenever they do data transformation or data loading from a delimited text file, an Excel spreadsheet or anything similar. If this was done by a batch job you need to check first whether a file exists based on a specific filename format (such as Test_yyyymmdd.xls). You can create a simple stored procedure to check for the formatting of the filename but to check whether the file exists before you to do your task is not something that you can do with SQL Server. Good thing there is such a stored procedure - xp_fileexist. This verifies whether a specified file exist. This stored procedure is located in the master database where you can execute it in the following syntax:

xp_fileexist 'C:\autoexec.bat'


This will return three columns indicating if the file exists, if the file is a directory and if the file's parent directory exists as a result set. You may want to include this in a script to do functional checks such as the example below

DECLARE @result INT
EXEC xp_fileexist 'c:\autoexec.bat', @result OUTPUT
IF (@result = 1)
--@result will return 1 if the specified file exists, if it doesn't, it will return 0
PRINT 'file exists'
ELSE
PRINT 'file does NOT exist'

This is also helpful when you want to check for the existence of a database backup file as it has been recorded in the msdb database. One of the challenges I have is checking if a database transaction log backup still exists if a log shipping job starts failing.

Just remember that when using this stored procedure is a bit risky as Microsoft reserves the right to change or even remove this in future versions or even service packs without having any documentation (well, it really doesn't have any after all) so make sure to test your scripts after applying service packs or upgrading.

Thursday, May 8, 2008

Changing VMWare image settings

And so I found out.

I was deploying an image to a server and wanted to try out VMWare Player instead. I am using version 2.0.3. Since the server has 4 processors, I was thinking of configuring the image to use as much processors as are available. Now there are two ways to do this. First, you can use VMWare Workstation or Server to change the configuration of the VMX file. You can use the graphical user interface for this. In my case, since my laptop does not have multi-processors, I did encounter an error stating that the image will not start if I use more than one CPU. But it doesn't matter since I am not going to start the image on my laptop anyway. I just need to change the settings so I can use those settings on a different machine.

Second, similar to how you do it in Microsoft Virtual PC where you change the VMC file, you can change the VMX file by searching for the numvcpus field and changing the value of the number, let's say from 1 to 2.


Oh, and there's one more thing: VMWare Player does not support more than 2 processors. And so I found out

Tuesday, April 29, 2008

Automating SQL Server 2005 Express Backups and Deletion of Older Backup Files

I've compiled a couple of my scripts - both TSQL and VBScript - to create a tip for MSSQLTips.com on automating backups and deletion of SQL Server 2005 Express databases to mimic that of creating a database maintenance plan. You can check out the article for more details. Feel free to post comments on the MSSQLTips.com forum site and hopefully, you'll find the tip very useful. I'll write a few more tips for them in the long run just for the fun of it.

Monday, April 21, 2008

Multiple connections to a server or shared resource by the same user, using more than one user name, are not allowed

I got this error when I tried to access a remote share from one of our DR servers. Well, apparently, the DR server configuration was not designed properly which is probably the reason why I am getting this error. The production and DR server both have the same configuration (or course, they are on a different network segment which is why this is possible) and I only get to access one or the other using a separate VPN profile. I was trying to copy a database backup from the production server and moving it to the DR server. The resolution: just run a net use * /delete command. It may sound simple but it works and that's what is more important. Having the same configuration (hostname and IP address, in my case) is causing my Windows XP workstation to get confused so a simple purge of the cache of my shared folder information. I would have to do this quite often as I need to connect to both servers on a regular basis
Google