SQL OPENQUERY 
Author Message
 SQL OPENQUERY

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?



Fri, 06 Jan 2006 18:43:15 GMT
 SQL OPENQUERY

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.


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.
> ___________________________________________________________


> 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?



Fri, 06 Jan 2006 18:58:12 GMT
 SQL OPENQUERY
I tried that and TSQL still doesn't like it. I get syntax
errors
Jim C



        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
   INSERT INTO ilink_config


END

Quote:
>-----Original Message-----
>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.

___________________________________________________________

- Show quoted text -

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:27:50 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Openquery in SQL server

2. Index Server + SQL Server 7 OpenQuery Contains

3. Buidling distributed openquery string using dynamic sql???

4. SQL 7 and openquery

5. selecting text files from SQL using openquery

6. MS SQL 7 - OPENQUERY

7. Passing parameters to OpenQuery Sql string.

8. MSG 7347 Received when openquery from sql 2000 to informix 7.31

9. OpenQuery - SQL Statement to Linked Server

10. Buidling distributed openquery string using dynamic sql???

11. Update AS400 with SQL data through openquery

12. SQL Server OPENQUERY on a remote MS Index Server catalog


 
Powered by phpBB® Forum Software