SQL Query from remote server - SQL Server Forums

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

Transact-SQL (2000)

SQL Query from remote server

Author

Topic

geir76@gmail.com
Starting Member

4 Posts

Posted - 2007-10-10 : 09:03:01

Hi!
I am trying to do a query from my MS SQL 2000 server to my Sybase server.
I have configured a linked server connection, which I know work.
What I am trying to accomplish, is to loop threw a Table in my MS Sql server, send a Campaign code to the Sybase server, count the number of occurences of this campaign code in a Sybase table, and update my MS SQL server tabel with the count.

To test the query, I created a test tabel on my MS SQL server with the same fields as my sybase table.
I entered some test value, and everything worked.
This Query looked like this:

UPDATE Tab_CampCost
SET SumInvoices = (select count(Kampanje) from abo
WHERE CampNo IN (select Kampanje from abo)
AND Tab_CampCost.CampNo=abo.Kampanje)

When doing the query to my linked server I get an error.
Could someone take a look at this query for me?

UPDATE Tab_CampCost
SET SumInvoices = (select * from openquery(
BACKUP,'select count(Kampanje) codecnt from abo))
WHERE CampNo IN (Select * from openquery(
BACKUP,'select Kampanje from abo WHERE abo.Kampanje=))Tab_CampCost.CampNo

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-10 : 09:24:10

what error are you getting?

and at a glance it looks like you may want to review your use of brackets in the second query?

Em

geir76@gmail.com
Starting Member

4 Posts

Posted - 2007-10-10 : 09:31:53

I get error message:
Line 6: Incorrect syntax near 'Tab_CampCost'

I know that their is something wrong with the last line, but this is
myfirst time using querys against remote servers.

quote:
Originally posted by elancaster

what error are you getting?

and at a glance it looks like you may want to review your use of brackets in the second query?

Em

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-10 : 23:23:18

Should it like this?

UPDATE Tab_CampCost
SET SumInvoices = (select * from openquery(
BACKUP,'select count(Kampanje) codecnt from abo))
WHERE CampNo IN (Select * from openquery(
BACKUP,'select Kampanje from abo WHERE abo.Kampanje=Tab_CampCost.CampNo))

geir76@gmail.com
Starting Member

4 Posts

Posted - 2007-10-11 : 03:02:59

Thank for your suggestion.
The changed query give me the following errormessage.

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'ASAProv.90' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'ASAProv.90' ICommandPrepare::Prepare returned 0x80004005: The provider did not give any information about the error.].

Kristen
Test

22859 Posts

Posted - 2007-10-11 : 04:27:11

[code]
UPDATE Tab_CampCost
SET SumInvoices = (select * from openquery(
BACKUP,'select count(Kampanje) codecnt from abo))
WHERE CampNo IN (Select * from openquery(
BACKUP,'select Kampanje from abo WHERE abo.Kampanje=))Tab_CampCost.CampNo
[/code]
Has wrongly embedded single-quote and
WHERE abo.Kampanje=))Tab_CampCost.CampNo
isn't going to do anything useful!

You need to first get the individual parts of your inner query debugged.

You are also attempting to nest an OPENQUERY to the same server. I don;t know if that server will be able to use an OPENQUERY back to itself (it probably can, but I don't know if Sybase supports that syntax, and that is the server it is going to be run on). Why not change your original query to aggregate the data it wants, e.g.
[code]
SELECT Kampanje,
COUNT(Kampanje) AS SumInvoices
FROM abo
WHERE CampNo IN
(
SELECT Kampanje
FROM abo
)
GROUP BY Kampanje
[/code]
and then just pass that to Sybase:
[code]
UPDATE U
SET SumInvoices = T.SumInvoices
FROM Tab_CampCost AS U
JOIN OPENQUERY(BACKUP,
'SELECT Kampanje,
COUNT(Kampanje) AS SumInvoices
FROM abo
WHERE CampNo IN
(
SELECT Kampanje
FROM abo
)
GROUP BY Kampanje'
) AS T
ON T.Kampanje = U.
[/code]
Kristen

geir76@gmail.com
Starting Member

4 Posts

Posted - 2007-10-11 : 06:37:34

I guess some times you dont see the forest from all the trees.

Thank you!

Kristen
Test

22859 Posts

Posted - 2007-10-11 : 07:18:09

Nothing like a second pair of eyes!

Which has drawn my attention to the fact that there is a bit missing off the end of my second query

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources