Dynamic Reference to Linked Server

Dear all
I got a problem that I would like to use a dynamic statement to query data
from a linked server (it is not guaranteed that the used database remains on
this server or the Test-Database would be renamed).
I tried to use the OPENQUERY-Functionality or to set up a SP which uses
Parameters
Example (SP with OPENQUERY)I:
ALTER PROCEDURE sp_GetParameterTable
@.LnkSrv NVARCHAR(30), @.LnkTbl NVARCHAR(30)
AS
SELECT
*
FROM
OPENQUERY
(
LTRIM(@.LnkSrv), -- LinkedServer
'
SELECT
*
FROM ' +
LTRIM(@.LnkTbl)
+ '
WHERE
(X_TYPE = ''XYZ'')
ORDER BY
CONVERT(FLOAT,X_KEY) DESC
'
)
->> it won't work !!
the other way to use a a SELECT Statement and a Function to get the Table
won't also not work.
SELECT
TOP 1
CONVERT(FLOAT,X_KEY) AS LastUsedKey
FROM
lokalserver.dbo.fn_Test
(
LinkedServer,
DataBase,
Table
)
WHERE
(X_TYPE = 'XYZ')
ORDER BY
CONVERT(FLOAT,X_KEY) DESC
-->> Function
ALTER FUNCTION fn_Test
(
@.SrvName NVARCHAR(20),
@.DBName NVARCHAR(20),
@.tblName NVARCHAR(35)
)
RETURNS NVARCHAR(80)
AS
BEGIN
DECLARE @.DBRef NVARCHAR(80)
SET @.DBRef = LTRIM(@.SrvName) + '.'
+ LTRIM(@.DBName ) + '.dbo.'
+ LTRIM(@.tblName )
RETURN(LTRIM(@.DBRef))
END
This will also not working.
Could anybody help me in this context? Any Help will be appreciated.
Youry,
BrunoSomething like this should work - I have used it:
DECLARE @.w nvarchar(4000)
SET @.w = 'SELECT.....FROM ['
IF (@.server IS NOT NULL) BEGIN SET @.w = @.w + @.server + '].[' END
SET @.w = @.w + @.database + ']..[' + @.table + ']...(rest of query)'
EXECUTE sp_executesql @.w
Cheers,
Peter.
Peter Hyssett
"Bruno" wrote:

> Dear all
> I got a problem that I would like to use a dynamic statement to query data
> from a linked server (it is not guaranteed that the used database remains
on
> this server or the Test-Database would be renamed).
> I tried to use the OPENQUERY-Functionality or to set up a SP which uses
> Parameters
> Example (SP with OPENQUERY)I:
> ALTER PROCEDURE sp_GetParameterTable
> @.LnkSrv NVARCHAR(30), @.LnkTbl NVARCHAR(30)
> AS
> SELECT
> *
> FROM
> OPENQUERY
> (
> LTRIM(@.LnkSrv), -- LinkedServer
> '
> SELECT
> *
> FROM ' +
> LTRIM(@.LnkTbl)
> + '
> WHERE
> (X_TYPE = ''XYZ'')
> ORDER BY
> CONVERT(FLOAT,X_KEY) DESC
> '
> )
> ->> it won't work !!
> the other way to use a a SELECT Statement and a Function to get the Table
> won't also not work.
>
> SELECT
> TOP 1
> CONVERT(FLOAT,X_KEY) AS LastUsedKey
> FROM
> lokalserver.dbo.fn_Test
> (
> LinkedServer,
> DataBase,
> Table
> )
> WHERE
> (X_TYPE = 'XYZ')
> ORDER BY
> CONVERT(FLOAT,X_KEY) DESC
> -->> Function
> ALTER FUNCTION fn_Test
> (
> @.SrvName NVARCHAR(20),
> @.DBName NVARCHAR(20),
> @.tblName NVARCHAR(35)
> )
> RETURNS NVARCHAR(80)
> AS
> BEGIN
> DECLARE @.DBRef NVARCHAR(80)
> SET @.DBRef = LTRIM(@.SrvName) + '.'
> + LTRIM(@.DBName ) + '.dbo.'
> + LTRIM(@.tblName )
> RETURN(LTRIM(@.DBRef))
> END
> This will also not working.
> Could anybody help me in this context? Any Help will be appreciated.
> Youry,
> Bruno