How to determine, inside a function, if a linked-server-query returned results

Hi, have configured an ODBC linked server for an Adaptive Server Anywhere (ASA6.0) database.
I have to write a function (not a procedure) that receives a number (@.Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple...
One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this:

SET @.SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @.Code + ''')'
EXEC sp_executesql @.SQL

(CAT_ASA is the linked-server's name)

Then, i would use @.@.ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed).
Does somebody know how to make what i want?? I prefer to avoid using temporary tables.
Thanks!I never worked with an ASA6 db but how about using four-part naming instead of OpenQuery? In a normal query, you can use variables in your where clauses. So, if the column type of CODE is not something out of the ordinary and recognized by SQL Server, everything should run fine. There could be interface problems but usually with a query as simple as yours, it should work.

This is a simple solution that doesn't really answer your question. Consider it as a possible workaround.

Good luck,

Skip.|||Thanks for your answer, Skip. I also tried using a four part name, but SQL server gave me a message saying that the ODBC Interface doesnt support four-part names. I tried with a 3 part name (linkedservername.database.table), but it still doesnt works. The error was diferent (so, I supose that the names with this ODBC interface must have three parts). I read in another thread that the only way to make a query to a linked server was using OPENQUERY or OPENROWSET. Im not really sure about that, but i tried many ways using 3 or 4 part names and it never worked.|||In addition i tried something like this:
SELECT * FROM OPENQUERY(CAT_ASA,'SELECT code FROM COUNTRIES') WHERE code=@.Code

Here i dont have to use an EXEC, so it works in a function, and i can filter the results with a condition. The problem is (sorry for not saying it before) that i wrote a very simple example, but the real query has 4 nested joins, and (because of performance) i should make it in only 1 query.
Thats why I cannot make something like this:

SELECT * FROM OPENQUERY(CAT_ASA,'SELECT * FROM Table1')
INNER JOIN (OPENQUERY(CAT_ASA,'SELECT * FROM Table2') ON ... )

Because i would make 4 OPENQUERY, which results in a very poor performance (10/14 secs per query!!!).

Another solution would be making the join inside the OPENQUERY, and filtering the results in SQL Server, like this:
SELECT * FROM OPENQUERY(CAT_ASA,'SELECT * FROM Table1 inner join (Table2 inner join (Table3 inner join Table 4 on...) on...)....
WHERE ...

Obviously this is worse than using 4 openquerys, because four joins without conditions (except on PKs) would return a very big quantity of records (in the order of 6.000.000.000!!!!!) and, after the conditions, that number would be reduced to 0 or 1 record (remember, i must check only the EXISTENCE of a record). That would be very inefficient.

So, I think in two ways for solving this:
1) Using the right part names (3 or 4), and making a normal query.
2) Find another method to execute a string query (or, more precisely, to determine if a string query has results), that can be used inside a function.
Thanks

How to determine if a linked server is available

Hello all,
I need to write data from one SQL server in to a linked server running on an
other machine. How can i check with T-SQL commands if this server is
available?
If it is not available the stored procedure directly quits with an error.
But this procedure should continue in differt ways, depending on the result
of the linked server transaction.
Both servers are MS SQL 2000, SP3a
Thanks a lot.
Try
if exists(select * from master.dbo.sysservers where srvname =
'OtherServer')
BEGIN
-- my code
END
sql

How to determine if a linked server is available

Hello all,
I need to write data from one SQL server in to a linked server running on an
other machine. How can i check with T-SQL commands if this server is
available?
If it is not available the stored procedure directly quits with an error.
But this procedure should continue in differt ways, depending on the result
of the linked server transaction.
Both servers are MS SQL 2000, SP3a
Thanks a lot.Try
if exists(select * from master.dbo.sysservers where srvname =
'OtherServer')
BEGIN
-- my code
END

How to Delete Records that are Linked with Relationships

Hello,
I am writing to ask if someone can tell me what the
command is to delete rows in an SQL 2000 database that are
linked through a foreign key relationship.
For example, I have a row in a "Persons" table that has a
primary key "Person ID". "Person ID" is then a foreign
key in two other tables. I would like to be able to
delete a person row from the "Persons" table and then
automatically have all associated rows based on
that "Person ID" in the other two tables deleted.
Thanks in advance!
MikeIn the design for the Persons table, open the relationship and make sure
'cascade delete' is on. This should do what you're asking...
Hope this helps...
"Mike Rogan" <mrogan@.carolinawebdev.com> wrote in message
news:046101c35559$bd142450$a401280a@.phx.gbl...
> Hello,
> I am writing to ask if someone can tell me what the
> command is to delete rows in an SQL 2000 database that are
> linked through a foreign key relationship.
> For example, I have a row in a "Persons" table that has a
> primary key "Person ID". "Person ID" is then a foreign
> key in two other tables. I would like to be able to
> delete a person row from the "Persons" table and then
> automatically have all associated rows based on
> that "Person ID" in the other two tables deleted.
> Thanks in advance!
> Mike

How to define an object in common place instead of every SP in SQL Server

hi,

I want to define a linked server object in one common place instead of every SP. (Because the UAT DB or SIT DB maybe the linked server with different name)

For Oracle we can define one common object in Package like following:

CREATE OR REPLACE PACKAGE FMS_PKG IS

TYPE T_CURSOR IS REF CURSOR;

END FMS_PKG;

Then we can use it in SP like FMS_PKG.T_CURSOR. So I wonder whether SQL Server 2005 has corresponding place can do this.

Thanks

Bily Jiang

Assuming that your Linked Server is another instance of SQL Server then you could create a Linked Server, named, for example, 'MyLinkedServer', and reference this in your stored procs / Views etc...

You can then use the CliConfg.exe application to create an Alias on the server hosting your primary SQL Server instance. Name the Alias 'MyLinkedServer' and specify the Server Alias (the instance of SQL Server to which your Linked Server should point) as appropriate to your environment.

This way you do not have to make changes to the code when you progress through each of the environments - all you have to do is update the Server Alias on the server and any Linked Server security settings on the primary SQL Server instance.

Chris

|||

Yes, I have found this tool.

Could you tell me how to use this tool to create one Linked Server Alias?

After I open it, I click Alias tab --> click Add --> should choose which option?

Thanks

Bily Jiang

|||

It depends on which network protocol you are using.

We use TCP/IP, so I would select 'TCP/IP', enter the Linked Server name in the Server Alias text box (e.g. MyLinkedServer), then enter the name of the server (plus instance name if necessary) that you wish to connect to in the 'Server Name' text box.

Chris

|||If you are using SQL Server 2005 you can create Synonyms for each table, view, procedure or function you are accessing via the linked server. Then you will just use the Synonym name to reference the object on the remote server, not the linked server syntax.|||

Thank you so much.

I feel synonym is the simplest resolution especially when we just access several remoting tables.

But I think package is the advantage of Oracle.

Regards

Ren Jie