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, how much recordsets returns Query?

Hi all!

Is there any chanse to determine in Transact-SQL, how much recordsets/rows already returned by currently executing query? I don't need count of rows affected by last statement (that @.@.ROWCOUNT returns), but ones, really returned to SQL-Client.

To understand, what I need it for, please see: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1707794&SiteID=1#1715230

Solution found!

See: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1716062&SiteID=1&mode=1#1716062

How to deserialize matchData?

Hi,
I am having problems deserializing the matchData variable returned by the
GetSubscriptionProperties() method.
My matchData looks like this:
"<ScheduleDefinition><StartDateTime>2005-04-27T06:00:00.000-05:00</StartDateTime><WeeklyRecurrence><WeeksInterval>1</WeeksInterval><DaysOfWeek><Wednesday>True</Wednesday></DaysOfWeek></WeeklyRecurrence></ScheduleDefinition>"
I found several code snippets from this newsgroup to figure out how to
convert this to a ScheduleDefinition object but everytime, it returns a
startdatetime of 1/1/1 and the "Item" part of the ScheduleDefinition object
is null...
code snippet I am currently using:
private ScheduleDefinition DeserializeObject (string sMatchData)
{
sMatchData MemoryStream vStream = new
MemoryStream(System.Text.Encoding.Default.GetBytes(sMatchData));
XmlAttributes attrs=new XmlAttributes();
attrs.XmlElements.Add(new
XmlElementAttribute("MinuteRecurrence",typeof(MinuteRecurrence)));
attrs.XmlElements.Add(new
XmlElementAttribute("DailyRecurrence",typeof(DailyRecurrence)));
attrs.XmlElements.Add(new
XmlElementAttribute("WeeklyRecurrence",typeof(WeeklyRecurrence)));
attrs.XmlElements.Add(new
XmlElementAttribute("MonthlyRecurrence",typeof(MonthlyRecurrence)));
attrs.XmlElements.Add(new
XmlElementAttribute("MonthlyDOWRecurrence",typeof(MonthlyDOWRecurrence)));
XmlAttributeOverrides attrOver=new XmlAttributeOverrides();
attrOver.Add(typeof(ScheduleDefinition),"ScheduleDefinition",attrs);
XmlSerializer newSr=new XmlSerializer(typeof(ScheduleDefinition),attrOver);
return (ScheduleDefinition)newSr.Deserialize(vStream);
}
Any help would be greatly appreciated... I am using this to display a
schedule on a web page. I can set subscriptions fine but I am having a hard
time reading them back.
I used the following code to set subscriptions and it works fine:
http://www.odetocode.com/Articles/114.aspx
Thanks in advance for any help!Never mind. I got it.
For those who are interested, call DeserializeObject() first.
private XmlAttributeOverrides GetScheduleOverrides ()
{
XmlAttributeOverrides overrides = new XmlAttributeOverrides();
XmlAttributes attrs = new XmlAttributes();
attrs.Xmlns = false;
overrides.Add(typeof(ScheduleDefinition), attrs);
overrides.Add(typeof(MinuteRecurrence), attrs);
overrides.Add(typeof(WeeklyRecurrence), attrs);
overrides.Add(typeof(MonthlyRecurrence), attrs);
overrides.Add(typeof(MonthlyDOWRecurrence), attrs);
overrides.Add(typeof(DaysOfWeekSelector), attrs);
overrides.Add(typeof(MonthsOfYearSelector), attrs);
return overrides;
}
private ScheduleDefinition DeserializeObject (string sMatchData)
{
sMatchData = sMatchData.Replace("True", "true");
Stream stream = new
MemoryStream(System.Text.Encoding.Default.GetBytes(sMatchData));
XmlAttributeOverrides overrides = GetScheduleOverrides();
XmlSerializer ser = new XmlSerializer(typeof(ScheduleDefinition),
overrides);
stream.Position = 0;
return (ScheduleDefinition)ser.Deserialize(stream);
}
"Pierrick" <email@.nospam.com> wrote in message
news:426f60d7$0$1254$8fcfb975@.news.wanadoo.fr...
> Hi,
> I am having problems deserializing the matchData variable returned by the
> GetSubscriptionProperties() method.
> My matchData looks like this:
> "<ScheduleDefinition><StartDateTime>2005-04-27T06:00:00.000-05:00</StartDateTime><WeeklyRecurrence><WeeksInterval>1</WeeksInterval><DaysOfWeek><Wednesday>True</Wednesday></DaysOfWeek></WeeklyRecurrence></ScheduleDefinition>"
> I found several code snippets from this newsgroup to figure out how to
> convert this to a ScheduleDefinition object but everytime, it returns a
> startdatetime of 1/1/1 and the "Item" part of the ScheduleDefinition
> object is null...
> code snippet I am currently using:
> private ScheduleDefinition DeserializeObject (string sMatchData)
> {
> sMatchData MemoryStream vStream = new
> MemoryStream(System.Text.Encoding.Default.GetBytes(sMatchData));
> XmlAttributes attrs=new XmlAttributes();
> attrs.XmlElements.Add(new
> XmlElementAttribute("MinuteRecurrence",typeof(MinuteRecurrence)));
> attrs.XmlElements.Add(new
> XmlElementAttribute("DailyRecurrence",typeof(DailyRecurrence)));
> attrs.XmlElements.Add(new
> XmlElementAttribute("WeeklyRecurrence",typeof(WeeklyRecurrence)));
> attrs.XmlElements.Add(new
> XmlElementAttribute("MonthlyRecurrence",typeof(MonthlyRecurrence)));
> attrs.XmlElements.Add(new
> XmlElementAttribute("MonthlyDOWRecurrence",typeof(MonthlyDOWRecurrence)));
> XmlAttributeOverrides attrOver=new XmlAttributeOverrides();
> attrOver.Add(typeof(ScheduleDefinition),"ScheduleDefinition",attrs);
> XmlSerializer newSr=new
> XmlSerializer(typeof(ScheduleDefinition),attrOver);
> return (ScheduleDefinition)newSr.Deserialize(vStream);
> }
> Any help would be greatly appreciated... I am using this to display a
> schedule on a web page. I can set subscriptions fine but I am having a
> hard time reading them back.
> I used the following code to set subscriptions and it works fine:
> http://www.odetocode.com/Articles/114.aspx
> Thanks in advance for any help!
>