TSQL OPENQUERY 
Author Message
 TSQL OPENQUERY

I tried the suggestion from MSFT using the statements
below and got the following message from Query Analyzer

Server: Msg 214, Level 16, State 2, Procedure
sp_executesql, Line 43

ntext/nchar/nvarchar data type expected by procedure.
Checking identity information: current identity value '0',
current column value '0'.
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
So I still need help.




      FROM OPENQUERY(IPDMS, "SELECT DISTINCT
        PDM.PDM_PRODUCTITEMVERFILE.PIVFNAME,
        PDM.PDM_PRODUCTITEMVERFILE.PIVFMAPNAME,
        PDM.PDM_PRODUCTITEMVERSION.PIVREV,
        PDM.PDM_PRODUCTITEMVERSION.PIVVER,
        PDM.PDM_PRODUCTITEM.PINAME

   FROM PDM.PDM_DEPENDENCYGRAPH,
       PDM.PDM_PRODUCTITEM,
       PDM.PDM_PRODUCTITEMVERSION,
       PDM.PDM_PRODUCTITEMVERFILE
  WHERE PDM.PDM_DEPENDENCYGRAPH.PIID =
PDM.PDM_PRODUCTITEM.PIID
        AND
       PDM.PDM_DEPENDENCYGRAPH.PIVID =
PDM.PDM_PRODUCTITEMVERSION.PIVID
        AND
       PDM.PDM_PRODUCTITEMVERSION.PIVID =
PDM.PDM_PRODUCTITEMVERFILE.PIVID

  ORDER BY  PDM.PDM_PRODUCTITEMVERFILE.PIVFNAME,  
PDM.PDM_PRODUCTITEM.PINAME,
       PDM.PDM_PRODUCTITEMVERSION.PIVREV,
       PDM.PDM_PRODUCTITEMVERSION.PIVVER")'
BEGIN

END
___________________________________________________________
Subject:  Re: SQL OPENQUERY
 From:  "SQL Development Team [MSFT]"

You can try use sp_executesql. Build the query text using
your variable

Jun Fang

--
This posting is provided "AS IS" with no warranties, and
confers no rights.
Use of included script samples are subject to the terms
specified at
http://www.***.com/


Quote:
> I'm using the following select statement to retrieve data
> from a ORACLE database to merge with information in a SQL
> Server database to create composit reports.

___________________________________________________________
Quote:


> BEGIN
>    INSERT INTO ilink_config

>       FROM OPENQUERY(IPDMS, 'SELECT DISTINCT
>         PDM.PDM_PRODUCTITEMVERFILE.PIVFNAME AS PIVNAME,
>         PDM.PDM_PRODUCTITEMVERFILE.PIVFMAPNAME,
>         PDM.PDM_PRODUCTITEMVERSION.PIVREV,
>         PDM.PDM_PRODUCTITEMVERSION.PIVVER,
>         PDM.PDM_PRODUCTITEM.PINAME

>    FROM PDM.PDM_DEPENDENCYGRAPH,
>        PDM.PDM_PRODUCTITEM,
>        PDM.PDM_PRODUCTITEMVERSION,
>        PDM.PDM_PRODUCTITEMVERFILE
>   WHERE PDM.PDM_DEPENDENCYGRAPH.PIID =
> PDM.PDM_PRODUCTITEM.PIID
>         AND
>        PDM.PDM_DEPENDENCYGRAPH.PIVID =
> PDM.PDM_PRODUCTITEMVERSION.PIVID
>         AND
>        PDM.PDM_PRODUCTITEMVERSION.PIVID =
> PDM.PDM_PRODUCTITEMVERFILE.PIVID

>   ORDER BY  PDM.PDM_PRODUCTITEMVERFILE.PIVFNAME,
> PDM.PDM_PRODUCTITEM.PINAME,
>        PDM.PDM_PRODUCTITEMVERSION.PIVREV,
>        PDM.PDM_PRODUCTITEMVERSION.PIVVER') WHERE  PIVNAME

> END

___________________________________________________________

- Show quoted text -

Quote:

> I would like to include a variable in the WHERE clause
> within the Quoted String, however I continue to get
syntax
> errors. The Where Clause at the end returns 0 records
> because it doesn't have data from the Quoted string. Does
> anyone have any suggestions?



Fri, 06 Jan 2006 19:58:01 GMT
 TSQL OPENQUERY

You complicate things with openquery() and dynamic parameters <G>
This should help...






      FROM OPENQUERY(IPDMS, ''SELECT DISTINCT
        PDM.PDM_PRODUCTITEMVERFILE.PIVFNAME,
        PDM.PDM_PRODUCTITEMVERFILE.PIVFMAPNAME,
        PDM.PDM_PRODUCTITEMVERSION.PIVREV,
        PDM.PDM_PRODUCTITEMVERSION.PIVVER,
        PDM.PDM_PRODUCTITEM.PINAME
   FROM PDM.PDM_DEPENDENCYGRAPH,
       PDM.PDM_PRODUCTITEM,
       PDM.PDM_PRODUCTITEMVERSION,
       PDM.PDM_PRODUCTITEMVERFILE
  WHERE PDM.PDM_DEPENDENCYGRAPH.PIID =PDM.PDM_PRODUCTITEM.PIID
        AND PDM.PDM_DEPENDENCYGRAPH.PIVID = PDM.PDM_PRODUCTITEMVERSION.PIVID
        AND PDM.PDM_PRODUCTITEMVERSION.PIVID =
PDM.PDM_PRODUCTITEMVERFILE.PIVID
        AND PDM.PDM_PRODUCTITEMVERFILE.PIVFNAME =

'
BEGIN

END

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net


Quote:
> I tried the suggestion from MSFT using the statements
> below and got the following message from Query Analyzer

> Server: Msg 214, Level 16, State 2, Procedure
> sp_executesql, Line 43

> ntext/nchar/nvarchar data type expected by procedure.
> Checking identity information: current identity value '0',
> current column value '0'.
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> So I still need help.




>       FROM OPENQUERY(IPDMS, "SELECT DISTINCT
>         PDM.PDM_PRODUCTITEMVERFILE.PIVFNAME,
>         PDM.PDM_PRODUCTITEMVERFILE.PIVFMAPNAME,
>         PDM.PDM_PRODUCTITEMVERSION.PIVREV,
>         PDM.PDM_PRODUCTITEMVERSION.PIVVER,
>         PDM.PDM_PRODUCTITEM.PINAME

>    FROM PDM.PDM_DEPENDENCYGRAPH,
>        PDM.PDM_PRODUCTITEM,
>        PDM.PDM_PRODUCTITEMVERSION,
>        PDM.PDM_PRODUCTITEMVERFILE
>   WHERE PDM.PDM_DEPENDENCYGRAPH.PIID =
> PDM.PDM_PRODUCTITEM.PIID
>         AND
>        PDM.PDM_DEPENDENCYGRAPH.PIVID =
> PDM.PDM_PRODUCTITEMVERSION.PIVID
>         AND
>        PDM.PDM_PRODUCTITEMVERSION.PIVID =
> PDM.PDM_PRODUCTITEMVERFILE.PIVID
>       AND PDM.PDM_PRODUCTITEMVERFILE.PIVFNAME = "'

>   ORDER BY  PDM.PDM_PRODUCTITEMVERFILE.PIVFNAME,
> PDM.PDM_PRODUCTITEM.PINAME,
>        PDM.PDM_PRODUCTITEMVERSION.PIVREV,
>        PDM.PDM_PRODUCTITEMVERSION.PIVVER")'
> BEGIN

> END
> ___________________________________________________________
> Subject:  Re: SQL OPENQUERY
>  From:  "SQL Development Team [MSFT]"

> You can try use sp_executesql. Build the query text using
> your variable

> Jun Fang

> --
> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> Use of included script samples are subject to the terms
> specified at
> http://www.microsoft.com/info/cpyright.htm.



> > I'm using the following select statement to retrieve data
> > from a ORACLE database to merge with information in a SQL
> > Server database to create composit reports.

> ___________________________________________________________


> > BEGIN
> >    INSERT INTO ilink_config

> >       FROM OPENQUERY(IPDMS, 'SELECT DISTINCT
> >         PDM.PDM_PRODUCTITEMVERFILE.PIVFNAME AS PIVNAME,
> >         PDM.PDM_PRODUCTITEMVERFILE.PIVFMAPNAME,
> >         PDM.PDM_PRODUCTITEMVERSION.PIVREV,
> >         PDM.PDM_PRODUCTITEMVERSION.PIVVER,
> >         PDM.PDM_PRODUCTITEM.PINAME

> >    FROM PDM.PDM_DEPENDENCYGRAPH,
> >        PDM.PDM_PRODUCTITEM,
> >        PDM.PDM_PRODUCTITEMVERSION,
> >        PDM.PDM_PRODUCTITEMVERFILE
> >   WHERE PDM.PDM_DEPENDENCYGRAPH.PIID =
> > PDM.PDM_PRODUCTITEM.PIID
> >         AND
> >        PDM.PDM_DEPENDENCYGRAPH.PIVID =
> > PDM.PDM_PRODUCTITEMVERSION.PIVID
> >         AND
> >        PDM.PDM_PRODUCTITEMVERSION.PIVID =
> > PDM.PDM_PRODUCTITEMVERFILE.PIVID

> >   ORDER BY  PDM.PDM_PRODUCTITEMVERFILE.PIVFNAME,
> > PDM.PDM_PRODUCTITEM.PINAME,
> >        PDM.PDM_PRODUCTITEMVERSION.PIVREV,
> >        PDM.PDM_PRODUCTITEMVERSION.PIVVER') WHERE  PIVNAME

> > END

> ___________________________________________________________

> > I would like to include a variable in the WHERE clause
> > within the Quoted String, however I continue to get
> syntax
> > errors. The Where Clause at the end returns 0 records
> > because it doesn't have data from the Quoted string. Does
> > anyone have any suggestions?



Sat, 07 Jan 2006 19:09:22 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. TSQL OR NOT TSQL this is the ?

2. non Stored Procedure TSQL code and Browser TSQL testing

3. Differences between SyBase TSQL, and SQL Server 2000 TSQL

4. OpenQuery Return Value?

5. OPENQUERY

6. MS SQL 7 - OPENQUERY

7. Problem calling db2 linked server SP using OPENQUERY

8. OpenQuery () Function

9. OpenQuery w/ text criteria using @variable

10. Linked Servers and OPENQUERY

11. OpenQuery AND Where Question

12. OPENQUERY


 
Powered by phpBB® Forum Software