MSSQL Hack: be able to execute sys.sp_addrolemember within a transaction

sys.sp_addrolemember is a MS SQL Server system stored procedure that cannot be executed within a user-defined transaction.
The work around is to terminate/commit any outer transaction (only in scenarios where the caller does not have any change *prior to calling sp_addrolemember*, that may need rolling back if something fails *after calling sp_addrolemember*, as this might affect the caller to lose the capability of rolling back unwanted changes).

SET IMPLICIT_TRANSACTIONS OFF -- make sure server does not start any implicit transaction 
WHILE XACT_STATE() = 1 -- the transaction is committable
  COMMIT TRAN -- Try committing any outer transaction, in order for 

EXEC sp_addrolemember
  @rolename = 'rolename',
  @membername = 'db_user_name'

Comparing between different collations

In MSSQL 2005, when comparing string (nvarchar, varchar, etc..) between different collations e.g.
SELECT * FROM sys.sql_logins
WHERE NAME NOT IN (
SELECT login_name FROM RESOURCE
)

SQL Server would throw an error due to different collations set for the columns compared:
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

So, to be able to compare between different collations, COLLATEE DATABASE_DEFAULT can be used, which would convert the column(s) to the collation the current database is using, and the new query looks like:
SELECT * FROM sys.sql_logins
WHERE NAME COLLATE DATABASE_DEFAULT NOT IN (
SELECT login_name COLLATE DATABASE_DEFAULT FROM RESOURCE
)

In addition, if the collation for one of the columns being compared is known, all the other columns can be converted to the same collation:
SELECT * FROM sys.sql_logins
WHERE NAME COLLATE SQL_Latin1_General_CP1_CS_AS NOT IN (
SELECT login_name FROM RESOURCE
)

Set up PostgreSQL as a linked server in Microsoft SQL Server 32-bit on a Windows Server 2003 64-bit

Environment

• Machine 1
  IP: 10.2.29.183
  OS: Windows Vista Business SP1
  DB: PostgreSQL 8.3, port: 5432
  
  
• Machine 2
  IP: 10.2.29.18
  OS: Windows Server 2003 Enterprise x64 Edition SP2
  DB: Microsoft SQL Server X86 Standard 9.00.1399.06, port: 4900
  

Procedures

• Machine 1
  
  
  
  1. Make sure port 5432 is opened in the firewall.
     
  2. Make sure Machine 2 is granted access to PostgreSQL. The access config file is at C:\Program Files\PostgreSQL\8.3\data\pg_hba.conf in this case. host all all 10.2.29.18/32 md5 is added to the end of the file.
     
  
  
• Machine 2
  
  
  
  1. Install 64-Bit OLEDB Provider for ODBC (MSDASQL), the installer file is WindowsServer2003.WindowsXP-KB948459-v2-x64-ENU.exe in this case.
     
  2. Install 32-bit PostgreSQL ODBC driver, choose the appropriate PostgreSQL version matching the setup (in this case psqlodbc_08_03_xxxx.zip).
     
  3. In SQL Server Management Studio, create a linked server using the following command:
     
EXEC sp_addlinkedserver
@server = '10.2.29.183',
@srvproduct = 'PostgreSQL',
@provider = 'MSDASQL',
@provstr = 'Driver=PostgreSQL ANSI;uid=postgres;Server=10.2.29.183;Port=5432;database=my_db_name;pwd=my_password';

EXEC sp_addlinkedsrvlogin
@rmtsrvname = '10.2.29.183',
@useself = 'false',
@rmtuser = 'postgres',
@rmtpassword = 'my_password';

     
  4. To test the connection:
     
SELECT *
FROM OPENQUERY([10.2.29.183], 'SELECT * FROM information_schema.tables')

     
  
  

References

• "Using 32 bit ODBC driver in x64?". Available: http://forums.techarena.in/windows-x64-edition/698703.htm [Accessed: May 06, 2009].
  
• Postgres OnLine Journal, "SETTING UP POSTGRESQL AS A LINKED SERVER IN MICROSOFT SQL SERVER 64-BIT". Available: http://www.postgresonline.com/journal/index.php?/archives/46-Setting-up-PostgreSQL-as-a-Linked-Server-in-Microsoft-SQL-Server-64-bit.html [Accessed: May 06, 2009].