read from progress, update linked server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

read from progress, update linked server

Author

Topic

jenam
Starting Member

13 Posts

Posted - 2004-08-16 : 12:53:19

I have an ODBC driver set up for a Progress database (ievaxs). I can read the Progress records just fine from my SQL server using analyzer.

I also have a linked server set up for an SQL database (madmax) (on another server). I can read the SQL records just fine from my SQL server using analyzer.

I need to read the Progress records and update a table on the linked SQL database if the records exist, adding them if they do not exist.

I am struggling as to where to add that piece (not exists) in my code. I am also confused on the syntax for the conditional checking.

One column of data to be updated/added will be a default value of "NOLOT". This column does not exist in the Progress table. There are more than three columns in the table that is getting updated, I'm only dealing with the three for this project.

I need to check for the part number (in madmax.dbo.item). If it exists, then I need to check the description. If the descriptions match, I need to ensure the lot type is not blank. If the descriptions do not match, then I need to update the description. If the lottype is blank, default it to "NOLOT".

Here's the code that I have so far ...

insert into madmax.dbo.item ("itemnum","description","lottype")
(SELECT "part"."part_nbr" ,
"part"."part_desc"
from OPENQUERY(ievAXS, 'SELECT "part_nbr","part_desc","NOLOT"
from PUB."part"
where "part"."part_nbr" = 3433362808 ')part )

The 'where' clause is used to isolate one part for testing purposes.

Any help would be appreciated as I can't find examples of this anywhere! TIA.

Jenam

Kristen
Test

22859 Posts

Posted - 2004-08-17 : 05:32:44

You might be needing:


insert into madmax.dbo.item ([itemnum],[description],[lottype])
(SELECT [part].[part_nbr] ,
[part].[part_desc], 'NOLOT' 
from OPENQUERY(ievAXS, 
    'SELECT "part_nbr","part_desc","NOLOT"
     from PUB."part" 
     where "part"."part_nbr" = 3433362808 '
)part )

If I had understodd correctly NOLOT doesn't exist on ievaxs, so you can't query that there.

I've changed the SQL Server column name wrapper to [ ] instead of double quotes - but that may not be important. I presume Progress uses double quotes

Kristen

jenam
Starting Member

13 Posts

Posted - 2004-08-17 : 08:42:07

Thank you for your response, Kristen.

You are correct, NOLOT does not exist in ievaxs. Thank you for clarifying where that goes.

I changed the SQL Server column name wrappers as you indicated. And yes, using Progress requires double quotes.

Right now, I get the following message ... Invalid object name 'madmax.dbo.item'. Would that be related to the setup of the linked server or is it syntax? Do both servers need to acknowledge each other as linked servers or just the one that's sending data?

How do I do the conditional piece of the puzzle?

Any information would be appreciated!
Jenam

Kristen
Test

22859 Posts

Posted - 2004-08-17 : 12:54:48

You need to run this connected to the database that MADMAX is hosted on, and THAT server needs to have a Linked Server configured for the link to ievAXS.

"How do I do the conditional piece of the puzzle?"

Adding them if they don't already exist?

If so then I do:


SELECT *
INTO #MyTempTable
FROM OPENQUERY(ievAXS, 
    'SELECT "part_nbr","part_desc","NOLOT"
     from PUB."part" 
     where "part"."part_nbr" = 3433362808 '
)

UPDATE U
SET    [itemnum] = T.[part_nbr],
       [description] = T.[part_desc],
       [lottype] = 'NOLOT'
FROM   #MytempTable T
       JOIN madmax.dbo.item U
           ON U.MyPKColumn = T.MyPKColumn

INSERT INTO madmax.dbo.item
(
    [itemnum], [description], [lottype]
)
SELECT T.[part_nbr],
       T.[part_desc],
       'NOLOT'
FROM   #MytempTable T
       LEFT OUTER JOIN madmax.dbo.item U
           ON T.MyPKColumn = U.MyPKColumn
WHERE  U.MyPKColumn IS NULL

DROP TABLE #MytempTable

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources