MS SQL Linked Server query using Ad Hoc links

[CSharpUser]

I'm new to MS SQL and trying to solve a problem where I need to extract specific data from a remote SQL Server 2005 DB into a local SQL Server 2008 R2 DB. In a Stored Procedure, I'm trying to use Linked Server via Ad Hoc links in the following manner:

@TestRunId int,
            @Force bit
.
.
.
.
SELECT * INTO PerfData.[DataCache].[TodaysPageLoadTimes] FROM 
	(SELECT ClientServerName.*, TestProperty.PropertyValue as ClientModel FROM 
		(SELECT ClientBuild.*, TestProperty.PropertyValue as ClientServerName FROM 
			(SELECT PageLoads.LogID, PageLoads.TestRunID, Test.Id AS TestID, Test.TestCaseName, Test.Result, PageLoads.[Message], PageLoads.[Category],
				TestProperty.PropertyValue as ClientBuild
					FROM OPENROWSET
					('SQLNCLI', 'RMTSVRNAME'; 'rmtsvrUser'; 'rmtsvrPwd', '(SELECT LogEntry.ID AS LogID, LogEntry.TestRunID, [Message], [Category], TestId FROM LoggingDB.dbo.LogEntry 
						WHERE (Category BETWEEN 281 AND 286 OR Category BETWEEN 289 AND 296 OR 
							Category BETWEEN 298 AND 299 OR Category BETWEEN 304 AND 304)
							AND TestRunID = @TestRunId)')
			AS PageLoads 
			INNER JOIN LoggingDB.dbo.Test ON Test.Id = PageLoads.TestId 
			INNER JOIN LoggingDB.dbo.TestProperty ON Test.Id = TestProperty.TestId 
			WHERE TestProperty.PropertyName = 'ClientBuild') 
		AS ClientBuild
		INNER JOIN LoggingDB.dbo.TestProperty ON ClientBuild.TestID = TestProperty.TestId 
		WHERE TestProperty.PropertyName = 'ClientServerName') 
	AS ClientServerName
	INNER JOIN LoggingDB.dbo.TestProperty ON ClientServerName.TestID = TestProperty.TestId 
	WHERE TestProperty.PropertyName = 'ClientModel') 
AS ClientModel

But, when I try to create/execute the Stored Procedure, I get this error:

OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@TestRunId".

Can anyone suggest what I am doing wrong and point me to a good, solid (and rather easily understood) example of how to do this correctly?

Your help is GREATLY appreciated!! :)

[BitBlt]

I believe you are trying to use @TestRunId in the wrong scope in line 17. You may have to do something like:

...
AND TestRunID = ' & @TestRunId & ')')
...

to include the variable from the proper scope. Not tested, just an observation. sorry about the formatting...not having all the single-quotes from above makes it a little ugly.

As far as working examples, you should probably just go to Technet or Google and check there.