"Lisa" <Lisa@discussions.microsoft.com> wrote in message
news:B9DE16A3-1346-424B-81C7-AD161EC8A848@microsoft.com...
> There is a very complex query where I'm trying to call a function from a
> linked server with input paramaters from the local database. This is the
> general idea:
>
> select openquery( linkserver, 'database.dbo.function( t1.c1, t2.c2 )'
> from table1 t1
> and table t2
> where ...
>
> I get a syntax error that doesn't recognize t1 and t2. How should I fix
> this?
>
> Thanks,

"Lisa" <Lisa@discussions.microsoft.com> wrote in message
news:B9DE16A3-1346-424B-81C7-AD161EC8A848@microsoft.com...
> There is a very complex query where I'm trying to call a function from a
> linked server with input paramaters from the local database. This is the
> general idea:
>
> select openquery( linkserver, 'database.dbo.function( t1.c1, t2.c2 )'
> from table1 t1
> and table t2
> where ...
>
> I get a syntax error that doesn't recognize t1 and t2. How should I fix
> this?
>
> Thanks,

"Uri Dimant" wrote:

> Lisa
> Is that Scalar UDF? Is that Inline Table-Valued UDF? Is that Multi-Statement
> Table-Valued UDF?
>
> Look at this technique written by Itzik Ben-Gan
>
> CREATE FUNCTION dbo.fn_getinvid1() RETURNS int
> AS
> BEGIN
> RETURN(SELECT newinvid FROM OPENQUERY([server_name],
> 'SET NOCOUNT ON; DECLARE @invid AS INT;
> UPDATE tempdb..Seq SET @invid = val = val + 1; COMMIT;
> SELECT @invid AS newinvid;') AS O)
> END
>
> CREATE FUNCTION dbo.fn_getinvid2() RETURNS int
> AS
> BEGIN
> RETURN(
> SELECT newinvid
> FROM OPENQUERY(
> [server_name],
> 'SET NOCOUNT ON;
> INSERT INTO tempdb..Seq2 DEFAULT VALUES
> ROLLBACK;
> SELECT SCOPE_IDENTITY() AS newinvid;') AS O)
> END
>
> "Lisa" <Lisa@discussions.microsoft.com> wrote in message
> news:B9DE16A3-1346-424B-81C7-AD161EC8A848@microsoft.com...
> > There is a very complex query where I'm trying to call a function from a
> > linked server with input paramaters from the local database. This is the
> > general idea:
> >
> > select openquery( linkserver, 'database.dbo.function( t1.c1, t2.c2 )'
> > from table1 t1
> > and table t2
> > where ...
> >
> > I get a syntax error that doesn't recognize t1 and t2. How should I fix
> > this?
> >
> > Thanks,
>
>