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

Dynamic If Update() in Trigger - Urgent!

Hi All

I have a question about generating dynmamicly If Update() statement in a
trigger..

in My db, there is a table that holds some column names of an another table.
for example;

Columns Table-A: Col1, Col2, Col3, Col4,Col5

Table-B: Col2, Col5 (The selected columns of Table A)

Then, in the Trigger of Table-A I use;

Select name from syscolumns where id=object_id('Table-A')

fetch next from TableA_Cursor into @.strColName

then, I used a statement like this..

if UPDATE(' + @.strColName + ')

But it gives "incorrect syntax" error..

How can I write this line?

Thanks alot in advance...

--
Message posted via http://www.sqlmonster.comI don't believe it is possible to use IF UPDATE() dynamically, nor is
it necessary. It also doesn't really make much sense to reference IF
UPDATE() in a cursor since the result will be the same for every row.
Anyway you shouldn't use cursors in triggers - they just turn your
set-based update statements into row-based updates, which is bad in
principle from a design poiunt of view and generally performs very
poorly.

If you want your triggers to take account of table structure changes
then generate the trigger code dynamically at DESIGN time rather than
runtime.

If you need a trigger to act on what data has changed then join the
Inserted and Deleted virtual tables and compare the columns. IF UPDATE
doesn't tell you what changed, only which columns were referenced by
the update statement.

If you need more help, please post a fuller description of your problem
including DDL and sample data.

--
David Portas
SQL Server MVP
--

Dynamic crosstab query in MS SQL Server 2000

Hello all!
I have a problem with creating crosstab query in MS SQL Server 2000. I
spent 8 hours on searching internet to achieve my succes but without
result. I would like to transform such data:
MRPController WK Value
C01 200505 1
C01 200505 1
C02 200505 2
C03 200506 4
C03 200506 7
C04 200505 1
C04 200507 5
into:
MRPController 200505 200506 200507
C01 2
C02 2
C03 4
C04 1 5
The data are updated once a w, that`s why I need a dynamic crosstab
query which let me receive such query in MS SQL Server 2000. I found out
that it is no so easy to create such cross tab query in MS SQL Server
2000, but I am wondering why it is so easy even in MS Access 1997 and
Excel 1997, and it is so tough case in MS SQL Server released in 2000. I
have search newsgroups, but I didn`t find anything whcih could help me.
I found some SQL procedures but they didn`t work. I heard that in MS SQL
Server 2005 there is a special function who let do it, but I have MS SQL
Server 2000 and I need to do this in this version on SQL Server. Is it
possible to do it? Is it some correct method to do it. Please be so kind
and help, but I already don`t know what to do and it is very wanted
query in my company. I didn`t think that I stuck on such query.
Thank you in advance for your help
I really apprieciate it
Marcin from Poland
*** Sent via Developersdex http://www.examnotes.net ***Take a look at this link
[url]http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21205811.html[/ur
l]
I use the transform proc just as you described with great results. You
will have to make a few small modifications so the date is labeled to
your likeing. I have mine labled (Month Year i.e. March 2005).
GL|||Dynamic Crosstab Queries
http://www.windowsitpro.com/SQLServ...5608/15608.html
Dynamic Cross-Tabs/Pivot Tables
http://www.sqlteam.com/item.asp?ItemID=2955
AMB
"Marcin Zmyslowski" wrote:

>
> Hello all!
> I have a problem with creating crosstab query in MS SQL Server 2000. I
> spent 8 hours on searching internet to achieve my succes but without
> result. I would like to transform such data:
> MRPController WK Value
> C01 200505 1
> C01 200505 1
> C02 200505 2
> C03 200506 4
> C03 200506 7
> C04 200505 1
> C04 200507 5
> into:
> MRPController 200505 200506 200507
> C01 2
> C02 2
> C03 4
> C04 1 5
> The data are updated once a w, that`s why I need a dynamic crosstab
> query which let me receive such query in MS SQL Server 2000. I found out
> that it is no so easy to create such cross tab query in MS SQL Server
> 2000, but I am wondering why it is so easy even in MS Access 1997 and
> Excel 1997, and it is so tough case in MS SQL Server released in 2000. I
> have search newsgroups, but I didn`t find anything whcih could help me.
> I found some SQL procedures but they didn`t work. I heard that in MS SQL
> Server 2005 there is a special function who let do it, but I have MS SQL
> Server 2000 and I need to do this in this version on SQL Server. Is it
> possible to do it? Is it some correct method to do it. Please be so kind
> and help, but I already don`t know what to do and it is very wanted
> query in my company. I didn`t think that I stuck on such query.
> Thank you in advance for your help
> I really apprieciate it
> Marcin from Poland
> *** Sent via Developersdex http://www.examnotes.net ***
>