Undefined

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

2009-07-16T11:45:00.003+12:00

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

2009-07-09T12:56:00.003+12:00

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

2009-05-06T14:38:00.005+12:00

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].