October 2007 - Posts
Working with linked servers in VSTS Database Pro can be a bit painful, especially when the linked server is a production server that should be (and typically is) inaccessible from developer's machines. If the appropriate linked server settings aren't set up on the design-time validation database server(which is the local database by default), the Database Pro project won't be able to build, which will prevent all sorts of useful things like schema compares for happening.
The solution to the problem is replicate the schema of the production database to another server (local is fine), and then to use sp_addlinkedserver to add the linked server using an alias rather than the actual server name, and then to use this alias in any stored procs that reference the linked server. The syntax for the call is:
EXEC sp_addlinkedserver @server='ServerAlias', @srvproduct='', @provider='SQLNCLI', @datasrc='ActualServerName'
Once sp_addlinkedserver has been called, the appropriate security settings still need to be set up, and this can be done from Management Studio or via sp_addlinkedsrvlogin.
By using an alias rather than the actual server name, the need for developers to have any login details for production systems on their machines is removed.
I've just completed a short,rushed project that involved moving data from one SQL Server 2005 in a DMZ to another SQL Server 2005 server inside the network. The database in the DMZ is deployed on a per-web site basis, and there can be many copies of the same database sending data back into the central database. I initially choose a push model, which would ease the deployment burden, as each copy of the website database could point back to the central server to push its data back. If a new site was deployed, there was no configuration necessary to get the data pushed back to the central location apart from setting some meta-data in a particular table, and scheduling a stored procedure to run.
As is typical in rushed projects, there was no consultation with the infrastructure guys about the feasibility of the design, and we didn't find out a few issues till we where scheduled to deploy. The problems (in the order that they occurred) where:
For someone that has always had network guys to set up DTC, its not as straight forward as I'd assumed. I spent ages setting all the correct DTC configuration values on the remote machine without ever enabling it on the central server. When I tried to issue a BEGIN DISTRIBUTED TRANSACTION from the central server, I got an error saying that the remote server had disabled its support for distributed transactions. The error message was wrong - the local server hadn't been set up for distributed transactions. Turning on DTC on both servers (obviously) fixed the problem.
Getting DTC working through a firewall is hard. This KB article documents how to do it, but as there was no name resolution between the web server and the central server (to quote the KB article 'DTC also requires that you can resolve computer names by using NetBIOS or DNS'), distributed transactions weren't a goer.
@@IDENTITY and scope_identity() don't flow across servers. Using the push model, I needed to set a foreign key in one of the inserted tables, which means I needed to know the primary key on the row inserted earlier in the stored proc. In the end, I had to go to a pull model, which was a pretty easy change.
While it would have been nice to identity and sort out all these issues before we started baning out TSQL, the project scope was small enough that we could resort to heroics to overcome design short-comings.
A couple of months before Vista was release, I purchased a copy of
DiskView to track down where all my hard disk space was being eaten up on a Windows XP install (it turned out to be several multi-gigabyte files that DivX converter had left lying around).
A version of DiskView that support Vista has recently been released, but when I tried it, I got an error with code 2738 during installation. I checked the website support page, and didn't see any resolution for this issue, so I tried filling in the support page without much hope of ever receiving an answer. Only a couple of hours later I got the following email:
Error 2738 may occur in Windows Vista when a custom install action is required by a software installer and VBScript is not registered. I'm not sure why it is occurring on your computer, as it seems to work fineon other Vista installations. I also just installed DiskView with no errors on a fresh Windows Vista Ultimate virtual machine.
Can you try the following?
Start Button > All Programs > Accessories Right-click on "Command Prompt"
Select "Run as Administrator"
Type cd \ and press ENTER
Type cd C:\Windows\System32 and press ENTER Type regsvr32 vbscript.dll and press ENTER You should receive a message that the DLL has been successfully registered.
Try the DiskView installation again. Please let me know how it goes.
Re-registering vbscript.dll fixed the problem
So, not only is DiskView a really good product, there technical support is excellent.