Re: Help with openquery

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Steve Kass (skass_at_drew.edu)
Date: 10/13/04 

Date: Wed, 13 Oct 2004 01:11:03 -0400

Chris,

  In that case, either find out how to write the query in Progress so
there are no duplicate column names, or create a table in SQL Server for
the results:

create table T(
  ProductName ...,
  CategoryName ...,
  ...
)

insert into T
select * from openquery(...)

select * from T

SK

Chris wrote:

>Hi,
>The problem is I am passing this query from SQL server to a progress
>database and if I use
>
>select ProductName, CategoryName
> from openquery(
> server,
> 'select prod.name as ProductName, categ.name as CategoryName ...
>
>I get the error Unable to understand after -- "DECLARE".
>
>
>
>"Steve Kass" wrote:
>
>
>
>>Chris,
>>
>> I don't see how either query works without FROM, and I don't see why
>>you are getting the particular message you are. But if you are trying
>>to distinguish the first [name] column of the OPENQUERY output from the
>>second [name] column, you will have no luck. You need to rename them
>>within the query string sent to the server.
>>
>>select ProductName, CategoryName
>>from openquery(
>> server,
>> 'select prod.name as ProductName, categ.name as CategoryName ...
>>)
>>
>>Steve Kass
>>Drew University
>>
>>Chris wrote:
>>
>>
>>
>>>Hi,
>>>I have the foll query
>>>
>>>select * from openquery(server, 'select prod.name,categ.name,price
>>>prod,categ
>>>where
>>>prod.state = 'ny'
>>>and
>>>prod.num = categ.num')
>>>
>>>This query works fine, however, if I want to give the columns names and I do
>>>
>>>
>>>select name as ProductName, name as categoryName from openquery(server,
>>>'select prod.name,categ.name,price
>>>prod,categ
>>>where
>>>prod.state = 'ny'
>>>and
>>>prod.num = categ.num')
>>>
>>>I get the foll error:
>>>
>>>Server: Msg 207, Level 16, State 3, Line 1
>>>Invalid column name 'categ'.
>>>
>>>Any ideas?
>>>
>>>
>>>
>>>
>>>

Relevant Pages

  • Re: Discussing 3 different strategies for deleting from multiple tables
    ... I will be using SQL Server but I am riding on top of a third party ... FYI, Account contains around 20K ... >>> This results in one parameterized query followed by two more trips to ...
    (microsoft.public.data.ado)
  • Re: Efficient coordinated queries??
    ... :-) We are distributing processing over a large server farm -- bringing it back onto the SQL server would probably kill our performance. ... Hitchhiker's Guide to Visual Studio and SQL Server ... A large chunk of it can be brought in with a single query and each row represents an item to process. ... For a hypothetical example consider a customer order database where we need to process through all customers and can bring in a row of customer data as our main query but there are 0..n1 rows of customer order data, 0..n2 rows of customer feedback information, 0..n3 rows of customer discounts available, etc. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Calculated field in pass through query
    ... Using the same hardware, with a single user, Jet will be faster than MS SQL ... Server simply because Jet does not use log. ... Even within MS SQL Server, ... same query on the same data but on another MSSQL db, ...
    (microsoft.public.access.queries)
  • Re: MailMerge hangs and crashes with Access on Server
    ... Since I am fairly new to working in this environment, I am not sure what you mean by an "Access group" with help to restructuring the query? ... "Peter Jamieson" wrote: ... All I know is that the performance of queries depends to a large extent on whether, for example, a join is performed on the client side, or on the server side, incurring no network traffic and potentially benefitting from caching on the server. ... There are no dialog boxes, and actually, I have now split the database, and have kept the Word templates and the Access frontend locally on my machine, and moved the backend to the server. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Website Connectivity?
    ... does the website just query our server? ... How do I give it a login to query the SQL Server running on the server? ... It would be a serious security breach to allow a remote website to ...
    (microsoft.public.windows.server.sbs)