I need to create a page that is populated from an external SQL database. The query is as follows:

SELECT * FROM [dbo].[tvfn_TableName] (240, 1, 0)

What I need is to replace the first parameter (240) with Umbraco.Field("programKey"), but I can't make it work. Everything in my brain says the following should work:

var prgminfo = dbconn2.Query(@"USE rover;
DECLARE @cookie varbinary(8000)

EXEC sp_setapprole '*username*', '*password*'
, @fCreateCookie = true, @cookie = @cookie OUTPUT;


SELECT * FROM [dbo].[tvfn_TableName] (@0, 1, 0)

EXEC sp_unsetapprole @cookie;
", pki);

This triggers an 'Application roles can only be activated at the ad hoc level.' error. If that parameter is hard-coded (e.g.240), everything runs just perfectly, but being a template page it will always load the data for that particular programKey.

Looking for any ideas/suggestions. I have been working with my data guy, but he's baffled too.

Thanks, Greg