Passing a variable to Openquery in SQL Server 2005

You'd think that passing a variable to Openquery in SQL Server 2005 would be easy. It isn't. The parser won't parse a string like you think it would. Instead you need to do something like this (and those are all single-quotes):

DECLARE @TSQL varchar(8000), @VAR char(50)
SELECT @VAR = 'XXXXX'
SELECT @TSQL = 'SELECT * FROM OPENQUERY(ConnectionName,''SELECT SomeField FROM SomeTable WHERE AnotherField = ''''' + @VAR + ''''''')'
EXEC (@TSQL)

Ugly, but it works.

Creating a free public/private key pair in Windows

I recently had to test something with public and private keys. You don't want to have to pay for certificates for testing. Free is better. These commands came in very handy so I'm documenting them here so I don't forget...

Go to this directory (or the equivalent depending on your VS install):
C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\

makecert -r -pe -n "cn=kevnls" -b 01/01/2008 -e 01/01/2020 -sky exchange -sv kevnls.pvk kevnls.cer

pvk2pfx -pvk kevnls.pvk -spc kevnls.cer -pfx kevnls.pfx -po somepassword

Edit: If you need a java keystore (.jks) instead of a .pvk file you can do this using the keytool in the jre (usually in C:\Program Files\Java\jre6\bin\).


./keytool -genkey -keyalg RSA -alias somealias -keystore kevnls.jks -storepass somepassword -validity 7300 -keysize 2048

./keytool -export -keystore keystore.jks -alias somealias -file kevnls.cer