VB6 DLL Failed to Load on Windows x64

I ran into a problem today that I struggled with for a couple of hours. I just had my machine upgraded from a Windows XP x86 (32 bit) to a Windows 7 x64 machine. We have some legacy custom applications that require a VB6 dll to be registered. Everytime I ran the regsrv32 command on the DLL to register it, I got the below error:

The module "MyVB6.dll" failed to load.

Make sure the binary is stored at the specified path or debug it to check for problems with the binary or dependent .DLL files.

The specified module could not be found.

I found posts that talk about making sure I run the command with adminstrator rights but it didn't help. I finally found the below URL:

http://social.msdn.microsoft.com/Forums/en-US/sbappdev/thread/91cf3127-70fe-4726-8a27-31b8964430c5/

It says that I need to make sure that the DLL does not sit in the System32 folder like we did on WinXP. Because the DLL is a 32 bit DLL, you need to use the 32 bit version of regsvr32 and that is in the SysWOW64. I even tried to run the command from SysWOW64 but leave the DLL in System32 and still got the same error. The DLL must sit in the SysWOW64 folder and be registered by the regsvr32.exe in the SysWOW64 folder.

Getting Rid of Some SQL Cursors

I had a need to find a way to take a single column result set and put it into a single varchar variable. I did not want to go through the hassle or the overhead of doing a cursor for this. With a little Googling, I found the below article:

http://stackoverflow.com/questions/822615/what-is-the-best-way-to-collapse-the-rows-of-a-select-into-a-string

It shows a very nice way of solving my problem :

 DECLARE @A VARCHAR(max)
SET @A = ''
SELECT @A = @A + ISNULL(mt.ColA, '') + ', '
FROM dbo.MyTable AS mt
ORDER BY mt.ColA
SET @A = SUBSTRING(@A, 1, LEN(@A) - 2)
SELECT @A

I thought this was a pretty nice way to do what I was doing but then I thought of how we are constantly having to do cursors to generate files and realized that we could use this method to generate a file without using a cursor. The below code will add a line at a time to the variable and then the variable can be written to file.

 DECLARE @A VARCHAR(max) 
SET @A = '' 
SELECT @A = @A + ISNULL(mt.ColA, '') + ',' + ISNULL(mt.ColB, '') + CHAR(10) 
FROM dbo.MyTable AS mt 
ORDER BY mt.ColA
SELECT @A 
--Write @A to a file

Using the above method reduces considerably the code required to generate a file and makes it a lot easier to read. Look over it and let me know if you have any comments or recommendations.

Update: Found an issue with the above method. We were using this at work with a process that would work sometimes and not in at other times. After a bunch of playing and searching, I found the below queries:

• http://blog.sqlauthority.com/2009/09/29/sql-server-interesting-observation-execution-plan-and-results-of-aggregate-concatenation-queries/
• http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

The first link essentially says that using the order by statement may cause unexpected results inconsistently.  Meaning that it will work one time but may not the next.

The second link gives the alternative by using a FOR XML PATH query hint. Very helpful