OK, so is it the service that executes/needs to execute the command?

If so, then if your service uses a domain account and that domain account uses a "trusted" connection to the...

Take a look at this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemSecurityPrincipalWindowsIdentityClassImpersonateTopic.asp

Describes how tom impersonate another account, programmatically.

Have you profiled the application? You said it's ADO.NET, are you certain that it's the execution of the query that's the problem or is it something else? For example, is...

Do you have any control over the schema? That is, can you change it or redesign it so that you've got multiple tables instead of everything glommed into a single table?...

Yes, you should be able to do that, but with a table of 100 million records I'm not sure how well it'll perform. Check out the ROW_NUMBER() function. In conjunction...

RETURN is supposed to be used with a value of type INT. It appears to misbehave when you use a resultset as the return value, which is what my example...

OK, here's what I was talking about:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=14697&p=2

Try this stored proc:

CREATE PROCEDURE dbo.StoredProcedure1 AS

Print 'Here 1'

RETURN

(Select Count(*) from INFORMATION_SCHEMA.COLUMNS)

Print 'Here 2'

RETURN

(Select Count(*) from INFORMATION_SCHEMA.TABLES...

OK, there's no wink character in SQL Server, you'll have to use a closing paren. Sorry about that.

Isn't there a problem in SQL Server where, in a stored proc, SQL statements that follow a RETURN will get executed anyway? Not sure if this is the case, but...

Check out SQLDTS.COM, they've got a listing of files that need to be installed to get DTS to work on its own.

http://www.sqldts.com/default.aspx?225

Doubt that these files are freely distributable, so...

OK, I'm goign to stop double-teaming now.

WRT your original example, what if I set the value of valueForParam to the following?

NULL; DROP PROCEDURE usp_Test;

?

Naturally, that assumes I have priviliges to do a DROP PROCEDURE, but it...

Whoa, bad idea. You're opening yourself up for a SQL injection attact with that code.

Don't know if it's related to the problem or even if it's a problem, but you appear to be missing the parameter for the proc's return value (which is parameter zero)....

No problem, glad to help. BTW, googling for "XPath tutorial" turns up some pretty good links (but an awful lot of them). This is one I've used that's fairly decent,...