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 deploy my Reports to the Internet? Anonymous access

How can I achive this. I need some reports to be on my page; I already know
to use reportviewer. So I will show it inside my page. But I want to
configure some of the reports that the user dont be asked for a DOMAIN user
name.*
What should I do?
--
LUIS ESTEBAN VALENCIA
MICROSOFT DCE 3.
MIEMBRO ACTIVO DE ALIANZADEV
http://spaces.msn.com/members/extremed/What kind of Authentication you are using for website? Form based?
HTH,
ALi-R
"Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
news:Ob62InICFHA.3504@.TK2MSFTNGP12.phx.gbl...
> How can I achive this. I need some reports to be on my page; I already
know
> to use reportviewer. So I will show it inside my page. But I want to
> configure some of the reports that the user dont be asked for a DOMAIN
user
> name.*
> What should I do?
> --
> LUIS ESTEBAN VALENCIA
> MICROSOFT DCE 3.
> MIEMBRO ACTIVO DE ALIANZADEV
> http://spaces.msn.com/members/extremed/
>|||It is not often good to enable anonymous access to the report server - this
hinders your ability to manage the system (everyone is the same user). You
can use a custom security extension to govern this w/o needing to create
DOMIAN accounts. You'd need to author and deploy the custom security
extension.
If this is not viable, an alternative is to deploy two report servers in a
web farm. Make on internet facing and allow anonymous access on it's
virtual directory. Make the other internal facing and require
authentication on it. This allows you to both give anonymous users rights
while ensuring fine grained security control over who can do report server
operations.
You will also want to customize the roles on your report server to limit
what anonymous users can do - anonymous shouldn't be able to subscribe for
example, this might bring your report server to its knees in the hands of an
attacker :-).
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
news:Ob62InICFHA.3504@.TK2MSFTNGP12.phx.gbl...
> How can I achive this. I need some reports to be on my page; I already
> know
> to use reportviewer. So I will show it inside my page. But I want to
> configure some of the reports that the user dont be asked for a DOMAIN
> user
> name.*
> What should I do?
> --
> LUIS ESTEBAN VALENCIA
> MICROSOFT DCE 3.
> MIEMBRO ACTIVO DE ALIANZADEV
> http://spaces.msn.com/members/extremed/
>|||Hi Lukasz,
I know that you always monitor the threads you answer so I'd like to use
this thread to let you know that ,the thread I initiated with the subject of
"Sharepoint and Reporting Services" has been left unanswered for that last
post I did.I would appreciate if you could take a quick look at it too.
Thanks
"Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
news:eNaNyMfDFHA.2824@.tk2msftngp13.phx.gbl...
> It is not often good to enable anonymous access to the report server -
this
> hinders your ability to manage the system (everyone is the same user).
You
> can use a custom security extension to govern this w/o needing to create
> DOMIAN accounts. You'd need to author and deploy the custom security
> extension.
> If this is not viable, an alternative is to deploy two report servers in a
> web farm. Make on internet facing and allow anonymous access on it's
> virtual directory. Make the other internal facing and require
> authentication on it. This allows you to both give anonymous users rights
> while ensuring fine grained security control over who can do report server
> operations.
> You will also want to customize the roles on your report server to limit
> what anonymous users can do - anonymous shouldn't be able to subscribe for
> example, this might bring your report server to its knees in the hands of
an
> attacker :-).
> -Lukasz
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
> news:Ob62InICFHA.3504@.TK2MSFTNGP12.phx.gbl...
> > How can I achive this. I need some reports to be on my page; I already
> > know
> > to use reportviewer. So I will show it inside my page. But I want to
> > configure some of the reports that the user dont be asked for a DOMAIN
> > user
> > name.*
> > What should I do?
> >
> > --
> > LUIS ESTEBAN VALENCIA
> > MICROSOFT DCE 3.
> > MIEMBRO ACTIVO DE ALIANZADEV
> > http://spaces.msn.com/members/extremed/
> >
> >
>|||Sorry, I don't have that thread anymore. What is the issue?
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"ALI-R" <newbie@.microsoft.com> wrote in message
news:OanIKNhDFHA.2508@.TK2MSFTNGP09.phx.gbl...
> Hi Lukasz,
> I know that you always monitor the threads you answer so I'd like to use
> this thread to let you know that ,the thread I initiated with the subject
> of
> "Sharepoint and Reporting Services" has been left unanswered for that last
> post I did.I would appreciate if you could take a quick look at it too.
> Thanks
> "Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
> news:eNaNyMfDFHA.2824@.tk2msftngp13.phx.gbl...
>> It is not often good to enable anonymous access to the report server -
> this
>> hinders your ability to manage the system (everyone is the same user).
> You
>> can use a custom security extension to govern this w/o needing to create
>> DOMIAN accounts. You'd need to author and deploy the custom security
>> extension.
>> If this is not viable, an alternative is to deploy two report servers in
>> a
>> web farm. Make on internet facing and allow anonymous access on it's
>> virtual directory. Make the other internal facing and require
>> authentication on it. This allows you to both give anonymous users
>> rights
>> while ensuring fine grained security control over who can do report
>> server
>> operations.
>> You will also want to customize the roles on your report server to limit
>> what anonymous users can do - anonymous shouldn't be able to subscribe
>> for
>> example, this might bring your report server to its knees in the hands of
> an
>> attacker :-).
>> -Lukasz
>>
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
>> news:Ob62InICFHA.3504@.TK2MSFTNGP12.phx.gbl...
>> > How can I achive this. I need some reports to be on my page; I already
>> > know
>> > to use reportviewer. So I will show it inside my page. But I want to
>> > configure some of the reports that the user dont be asked for a DOMAIN
>> > user
>> > name.*
>> > What should I do?
>> >
>> > --
>> > LUIS ESTEBAN VALENCIA
>> > MICROSOFT DCE 3.
>> > MIEMBRO ACTIVO DE ALIANZADEV
>> > http://spaces.msn.com/members/extremed/
>> >
>> >
>>
>|||The thread subject is "Sharepiont and Reporting services" and from "ALI-R"
,it was about using kerbers for having sps and rs on different computers,I
asked the last question and unfortunately I didn't get any answer from you.
"Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
news:%23rB5wTvDFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Sorry, I don't have that thread anymore. What is the issue?
> -Lukasz
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "ALI-R" <newbie@.microsoft.com> wrote in message
> news:OanIKNhDFHA.2508@.TK2MSFTNGP09.phx.gbl...
> > Hi Lukasz,
> >
> > I know that you always monitor the threads you answer so I'd like to use
> > this thread to let you know that ,the thread I initiated with the
subject
> > of
> > "Sharepoint and Reporting Services" has been left unanswered for that
last
> > post I did.I would appreciate if you could take a quick look at it too.
> > Thanks
> > "Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in
message
> > news:eNaNyMfDFHA.2824@.tk2msftngp13.phx.gbl...
> >> It is not often good to enable anonymous access to the report server -
> > this
> >> hinders your ability to manage the system (everyone is the same user).
> > You
> >> can use a custom security extension to govern this w/o needing to
create
> >> DOMIAN accounts. You'd need to author and deploy the custom security
> >> extension.
> >>
> >> If this is not viable, an alternative is to deploy two report servers
in
> >> a
> >> web farm. Make on internet facing and allow anonymous access on it's
> >> virtual directory. Make the other internal facing and require
> >> authentication on it. This allows you to both give anonymous users
> >> rights
> >> while ensuring fine grained security control over who can do report
> >> server
> >> operations.
> >>
> >> You will also want to customize the roles on your report server to
limit
> >> what anonymous users can do - anonymous shouldn't be able to subscribe
> >> for
> >> example, this might bring your report server to its knees in the hands
of
> > an
> >> attacker :-).
> >>
> >> -Lukasz
> >>
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >>
> >>
> >> "Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
> >> news:Ob62InICFHA.3504@.TK2MSFTNGP12.phx.gbl...
> >> > How can I achive this. I need some reports to be on my page; I
already
> >> > know
> >> > to use reportviewer. So I will show it inside my page. But I want
to
> >> > configure some of the reports that the user dont be asked for a
DOMAIN
> >> > user
> >> > name.*
> >> > What should I do?
> >> >
> >> > --
> >> > LUIS ESTEBAN VALENCIA
> >> > MICROSOFT DCE 3.
> >> > MIEMBRO ACTIVO DE ALIANZADEV
> >> > http://spaces.msn.com/members/extremed/
> >> >
> >> >
> >>
> >>
> >
> >
>|||I'm sorry, could you restate your question? I don't have the other thread
and I don't know which question you're referring to when you say, "the last
question.".
Are you asking how to deploy Kerberos? I couldn't really find a link that
explained how to do this. I know there are books written on this topic.
You can start here:
http://toolbar.search.msn.com/results.aspx?q=kerberos+how+to+site%3Amsdn.microsoft.com&FORM=QBRE
-Lukasz
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"ALI-R" <newbie@.microsoft.com> wrote in message
news:OD9%23NqwDFHA.2232@.TK2MSFTNGP14.phx.gbl...
> The thread subject is "Sharepiont and Reporting services" and from "ALI-R"
> ,it was about using kerbers for having sps and rs on different computers,I
> asked the last question and unfortunately I didn't get any answer from
> you.
> "Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
> news:%23rB5wTvDFHA.2568@.TK2MSFTNGP10.phx.gbl...
>> Sorry, I don't have that thread anymore. What is the issue?
>> -Lukasz
>>
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "ALI-R" <newbie@.microsoft.com> wrote in message
>> news:OanIKNhDFHA.2508@.TK2MSFTNGP09.phx.gbl...
>> > Hi Lukasz,
>> >
>> > I know that you always monitor the threads you answer so I'd like to
>> > use
>> > this thread to let you know that ,the thread I initiated with the
> subject
>> > of
>> > "Sharepoint and Reporting Services" has been left unanswered for that
> last
>> > post I did.I would appreciate if you could take a quick look at it too.
>> > Thanks
>> > "Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in
> message
>> > news:eNaNyMfDFHA.2824@.tk2msftngp13.phx.gbl...
>> >> It is not often good to enable anonymous access to the report server -
>> > this
>> >> hinders your ability to manage the system (everyone is the same user).
>> > You
>> >> can use a custom security extension to govern this w/o needing to
> create
>> >> DOMIAN accounts. You'd need to author and deploy the custom security
>> >> extension.
>> >>
>> >> If this is not viable, an alternative is to deploy two report servers
> in
>> >> a
>> >> web farm. Make on internet facing and allow anonymous access on it's
>> >> virtual directory. Make the other internal facing and require
>> >> authentication on it. This allows you to both give anonymous users
>> >> rights
>> >> while ensuring fine grained security control over who can do report
>> >> server
>> >> operations.
>> >>
>> >> You will also want to customize the roles on your report server to
> limit
>> >> what anonymous users can do - anonymous shouldn't be able to subscribe
>> >> for
>> >> example, this might bring your report server to its knees in the hands
> of
>> > an
>> >> attacker :-).
>> >>
>> >> -Lukasz
>> >>
>> >>
>> >> --
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> >>
>> >>
>> >>
>> >> "Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
>> >> news:Ob62InICFHA.3504@.TK2MSFTNGP12.phx.gbl...
>> >> > How can I achive this. I need some reports to be on my page; I
> already
>> >> > know
>> >> > to use reportviewer. So I will show it inside my page. But I want
> to
>> >> > configure some of the reports that the user dont be asked for a
> DOMAIN
>> >> > user
>> >> > name.*
>> >> > What should I do?
>> >> >
>> >> > --
>> >> > LUIS ESTEBAN VALENCIA
>> >> > MICROSOFT DCE 3.
>> >> > MIEMBRO ACTIVO DE ALIANZADEV
>> >> > http://spaces.msn.com/members/extremed/
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>

How to deploy my reports on the Internet: Anonymous Access?

How can I achive this. I need some reports to be on my page; I already know
to use reportviewer. So I will show it inside my page. But I want to
configure some of the reports that the user dont be asked for a DOMAIN user
name.*
What should I do?
--
LUIS ESTEBAN VALENCIA
MICROSOFT DCE 3.
MIEMBRO ACTIVO DE ALIANZADEV
http://spaces.msn.com/members/extremed/Anonymous access is pretty difficult to set up. With a default
configuration, when you turn on anonymous access on the web site, then
you're not prompted for a user name at all, and then you can't do your other
stuff like get to protected information or publish files using the Report
Manager.
There are ways to do it, but because of the security concerns, my suggestion
would be to call Microsoft and open a support ticket asking them to walk you
through the process.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
news:%23iPZZVrEFHA.1836@.tk2msftngp13.phx.gbl...
> How can I achive this. I need some reports to be on my page; I already
> know
> to use reportviewer. So I will show it inside my page. But I want to
> configure some of the reports that the user dont be asked for a DOMAIN
> user
> name.*
> What should I do?
> --
> LUIS ESTEBAN VALENCIA
> MICROSOFT DCE 3.
> MIEMBRO ACTIVO DE ALIANZADEV
> http://spaces.msn.com/members/extremed/
>

how to delete a specific record inside a DB?

In one of our DB's, we've got a corrupted record. My question to this group,
is there a command to delete a specific record out of the DB? If so, what
all information do i have to provide to the command, so it specifies this
record. Any help is greatly appreciated
Ken Zimmerman
MIS Dept
American Red Cross
The DELETE statement deletes a row identified by its column values. If you
aren't sure what the key columns of the table are then before you delete
anything you'll want to be sure you've found the right row(s). Check by
using a SELECT statement to view the data you are going to delete. For
example:
SELECT *
FROM YourTable
WHERE col1 = 'X'
AND col2 = 'Y'
Once you're happy that you've defined the correct criteria for the row(s)
you want to delete:
DELETE
FROM YourTable
WHERE col1 = 'X'
AND col2 = 'Y'
May be wise also to make sure you have a recent backup before deleting
anything.
David Portas
SQL Server MVP
|||KZimmerman wrote:
> In one of our DB's, we've got a corrupted record. My question to
> this group, is there a command to delete a specific record out of the
> DB? If so, what all information do i have to provide to the command,
> so it specifies this record. Any help is greatly appreciated
> Ken Zimmerman
> MIS Dept
> American Red Cross
I assume by corrupted, you mean that there is strange data in one of the
columns and you believe this to be a "bad" data issue, not a corruption
in the database.
If so, use David's recommendation and locate the primary key for the row
or rows with the problem so you can issue a delete statement to remove
them from the database.
If this is a database corruption issue, try issuing DBC CHECKDB on the
database to check for problems.
David G.
|||If none of that works. you might back up your database and try.
Dbcc checkdb repair_allow_data_loss (read about this in books on line first)
or
export the rows out using bcp ( you'll get the rows you can see)
truncate the table and re-import them (Be careful to get all of the rows ,
you might have to do some tricks to select forward (and it dies when you get
to the bad row, then select backwards to get the rows on the other side.)
Also,
Call MS Tech support, I think they have some tools which might be useful I
think their fee is $250... Not much considering the time you might spend
messing with this ( if the data is important.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"KZimmerman" <KZimmerman@.discussions.microsoft.com> wrote in message
news:1087034D-3D76-4827-9166-EA3AB7D6CE91@.microsoft.com...
> In one of our DB's, we've got a corrupted record. My question to this
group,
> is there a command to delete a specific record out of the DB? If so, what
> all information do i have to provide to the command, so it specifies this
> record. Any help is greatly appreciated
> Ken Zimmerman
> MIS Dept
> American Red Cross