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 database is in use

I have a VB application that uses SQL Server 2000. I am adapting it to support both 2000 and 2005. I connect to the database using ODBC. My application has the typical backup/restore functionality. Before I do a backup or restore, I check to see if the database is currently being used by another application. In SQL Server 2000, I did this by connecting to the 'master database' and running the following query:

SELECT COUNT(*) FROM SYSDATABASES WHERE DBID IN (SELECT DBID FROM SYSLOCKS) AND NAME = 'MyDatabase'

If I get a count greater than 0, someone else is using the database. This of course does not work on SQL Server 2005. I have come up with an alternative. For SQL Server 2005, I connect to the database I want to backup or restore and run the following query:

SELECT COUNT(*) FROM sys.dm_tran_locks

If I get a count greater than 1 (1 because I had to connect to the database myself to run the query), someone else is using the database. The problem is, it isn't terribly reliable. I sometimes run the query and get a count greater than one, then try again a few seconds later and get a count of 1. Having the Studio Enterprise manager open to the point that you can see all database in the database tree also has an impact. The problem must be more complicated than my simply solution can handle. Trouble is I am having a hard time finding any docs that discusses the issue. I am probably just no looking in the right place. Does anyone have a better way to determine if another process is using the database you want to backup or restore?

Why worry for backups, the operation is online, i.e. people can be accessing the system whilst the backup is running.

Not the case for restores but they should be few and far between. You can kick everyone off, by using the ALTER DATABASE command in SQL 2005.

If you do need to find connections go to sysprocesses in SQL 2000 and in sql 2005 sys.dm_exec_requests

|||

I figured that might be be someone's response, but I wanted to keep my post short so I did not explain any further. While backups can be done on-line, in my case it would not be appropriate. I am doing a backup and restore as part of my database upgrade process. A backup is done just before the data upgrade (adding and deleting tables, fields, moving data around, etc). The restore is used to recover from any failure during the upgrade process. So, allowing the users to be in the system for a few more moment's just postpones the problem. I cannot have someone in the database while I am making structure changes.

That said, kicking them off unexpectedly isn't very elegant either. I would much prefer to put a check before going through this process to tell the user that someone is connected to the database and I cannot proceed until they are out. I am running this business application in locations across the country. It is used by "normal" users who may have few skills other than a knowledge of how to run the business application.

I do appreciate the suggestion to use the sys.dm_exec_requests stored procedure. I'll look into this now.

Thank you very much!

|||You could issue a

alter database <database> set single_user

And just wait. If it timesout then someone is still in the db.

|||That also stops new users from connecting.|||

This looked like a really good idea. I tried using it by connecting to the database via my application, then going to Management Studio and entering the alter database as you suggested. The result was that it waited indefinitely for the lock to be removed (it never timed out). I searched around for how to set the timeout period. The only thing I could find is 'set lock_timeout=n' but it is documented that alter database ignores this setting (as does create database and drop database). I could not find an alternative.

I'll go back and try to work with your first suggestion.

Thanks!

Andy

|||

I went back and found the problem with my original solution. The SQL Server 2000 approach and the corresponding SQL Server 2005 approach are more similar than I thought. In SQL Server 2000, I ran the following query to see if anyone was using the database called 'MyDatabase':

SELECT COUNT(*) FROM SYSDATABASES WHERE DBID IN (SELECT DBID FROM SYSLOCKS) AND NAME = 'MyDatabase'

In SQL Server 2005, the following seems to work the same:

SELECT COUNT(*) FROM sys.dm_tran_locks WHERE resource_database_id IN (SELECT dbid from sys.sysdatabases where name='MyDatabase')

Thank you for you other suggestions. Setting the access to single user is probably better, because it prevents people using the database part way through the upgrade process. In order to use it, I just need to figure out how to make it timeout in a reasonable period of time.

Andy

|||

You can set the timeout in your application. If using ADO the command object has a command timeout.

As for your second set of code. you can simplify it by using db_id('your database name') to get the database_id of your database

|||Thanks for the info. I am not using ADO, but I'll keep that in mind if I ever switch. The biggest problem that prevents me from using ADO is the lack of support for cursors. Not everything works well with a disconnected recordset. I keep hearing that support will be added back in... but I do not think it has happened so far.|||

Why do you need cursors?

What do you use instead of ADO?

What are you programming in?

|||

I only have one need for cursors (but it is big). In many placed in an application, I have to manage a collection of items where that is very large (several thousand to as many as 10 million). It isn't practical to load that many things in a collection (it takes too long) or display that many things in a listbox or spreadsheet. The way I solve this problem know is by paging the data into and out of the collection.

While getting a page worth of data doesn't require a cursor, navigating from page to page without it has its problems. For example, just bringing up a vertical scrollbar is a problem. There isn't a way to tell with a disconnected recordset what page you are on relatively to the entire collection. Going to the next and previous page is terribly innefficient (especially if you are not near the top of the collection) because ther isn't a good way to get the 'next' or previous page. Disconneted recordsets involve rerunning queries to go from one page to the next (which does not perform well). These are problems I can solve with a cursor.

Of course the applications that I am writing are desktop applications with a relatively small number of users. A lot of folks say cursors are expensive and do not scale well. In my case, that is a good trade off. Most of those folks are writing web pages which is a very different animal. I would rather have a highly function and high performing interface than be concerned about scalability to a number of users because my applications are typically used by less than 100 users at at time.

Anyway, all of my applications are written in VB 6.0. I use the ODBC APIs for database access. Most of them work against more than one database (e.g. SQL Server, Access, Sybase). I will begin converting them to .NET next year (I am working on a prototype of the .NET archicture I will use and a prototype of how to convert them). I will probably leave the data access alone, since the existing data access works will in .NET. This also allows me to focus my efforts on just getting the applications working again.

How to Deploy SQL server Database to another PC while creating SetUP package in .Net VB

How to Deploy SQL server Database to another PC, How to create a
package that craetes Database as well as ODBC driver for accessing data
at enduser PC, using .Net VBhi,
<hitendra15@.gmail.com> ha scritto nel messaggio
news:1102241845.011857.112670@.c13g2000cwb.googlegr oups.com
> How to Deploy SQL server Database to another PC, How to create a
> package that craetes Database as well as ODBC driver for accessing
> data at enduser PC, using .Net VB

John already answered you about drivers...
as regard database installation, personally I do not like backup/restore
practice, nor detach/attach...
and Ialways go for executing the DDL scripts to recreate the dbs and related
objects as long as performing BCP in and/or INSERT INTO scripts in order to
populate pre-loaded tables...
the best "universal" approach I've seen so far explained in a public article
is
http://msdn.microsoft.com/sql/archi...er/default.aspx
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--- remove DMO to reply|||To add on to Andrea's response, we keep all of our DDL scripts under source
control and run these scripts during installation with a .Net custom action.

Depending on your requirements, you can include the scripts in a .Net
project as either content files or as embedded resources. You can then
include that project in your Setup and Deployment project. Your custom
action can read and execute the scripts from either the resource assembly or
from the file system.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<hitendra15@.gmail.com> wrote in message
news:1102241845.011857.112670@.c13g2000cwb.googlegr oups.com...
> How to Deploy SQL server Database to another PC, How to create a
> package that craetes Database as well as ODBC driver for accessing data
> at enduser PC, using .Net VB

How to deny information schema views...

Hello, all. I created a login and granted the user access to my sql 2005
database. well when the user creates his odbc dsn to access the database, I
discovered he can also see the INFORMATION_SCHEMA views. What gives? How
can I deny him access to these objects. He should have access to the db I
granted him.
Help!!!!!!!!
RozThe Information Schema views in SQL Server 2005 should only return for the
user information about the objects the user actually has access to. While
this was a prominent information disclosure issue in SQL Server 2000, it's
not as wide open in SQL Server 2005. They are provided for SQL-92 compliance
so that users can query the metadata/schema of the database without having
to query the system tables. Is there a reason you want to block access to
them?
K. Brian Kelley, brian underscore kelley at sqlpass dot org
http://www.truthsolutions.com/

> Hello, all. I created a login and granted the user access to my sql
> 2005 database. well when the user creates his odbc dsn to access the
> database, I discovered he can also see the INFORMATION_SCHEMA views.
> What gives? How can I deny him access to these objects. He should
> have access to the db I granted him.
> Help!!!!!!!!
> Roz|||Hello Roz,
You can't hide the fact the views exist as far as I can tell, but if you
look at what he see, it won't be much if anything. Basically he has to be
able to the see the metadata's metadata, but he shouldn't be able to see
the metadata itself unless you start granting him rights to do so (e.g.,
VIEW DEFINITION).
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks for reply. I want to block access because as my users create their
ODBC DSNs, they can open these tables and **change** data. I've tried it an
d
it works. Very scary.
Roz
"K. Brian Kelley" wrote:

> The Information Schema views in SQL Server 2005 should only return for the
> user information about the objects the user actually has access to. While
> this was a prominent information disclosure issue in SQL Server 2000, it's
> not as wide open in SQL Server 2005. They are provided for SQL-92 complian
ce
> so that users can query the metadata/schema of the database without having
> to query the system tables. Is there a reason you want to block access to
> them?
>
> K. Brian Kelley, brian underscore kelley at sqlpass dot org
> http://www.truthsolutions.com/
>
>
>|||Kent,
Simply having the "public" role, gets him access to these tables. He (I)
was even able to open these tables say in Access thru ODBC, and potentially
change the data. Scary.
Roz
"Kent Tegels" wrote:

> Hello Roz,
> You can't hide the fact the views exist as far as I can tell, but if you
> look at what he see, it won't be much if anything. Basically he has to be
> able to the see the metadata's metadata, but he shouldn't be able to see
> the metadata itself unless you start granting him rights to do so (e.g.,
> VIEW DEFINITION).
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||I am wondering if you are seeing something else.
Could you please give us the steps you used to open
information schema views and change the underlying data on
SQL Server 2005? Which views, data in what columns?
As far as I know, what you are saying is not possible.
If it is actually other tables you are referring too, I
think you have a permissions issue with how you have
security set up. I think that's likely the issue anyway.
-Sue
On Tue, 20 Mar 2007 16:51:05 -0700, Roz
<Roz@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for reply. I want to block access because as my users create their
>ODBC DSNs, they can open these tables and **change** data. I've tried it a
nd
>it works. Very scary.
>Roz
>"K. Brian Kelley" wrote:
>