0

Please asssit on this foxpro to sql server migration requirement..

I have this function USED() in foxpro which determines if a table is open in a work area. Is there any such function in SQL Server that does the same job?

Ricardo Altamirano
  • 14,650
  • 21
  • 72
  • 105
rock
  • 585
  • 3
  • 10
  • 26
  • 1
    There's no concept of a "work area" in SQL Server. Unless you mean that you'd like to detect when another session is reading or writing to a table... in which case you need to look at `sp_lock` and `sp_who2`. – bluevector Jun 15 '12 at 13:10
  • You don't have the same considerations in SQL Server. Instead each session should use TRANSACTIONs and LOCKing hints to protect their own activity. If you explain the scenarios you want to protect against, and why, then we can advise how, or even if you need to at all. – MatBailie Jun 15 '12 at 13:12
  • @Dems, here is the scenario in foxpro.. IF USED('tablename')
    SELECT tablename
    SET ORDER TO tablename.name ELSE
    USE tablename IN 0 ORDER name
    ENDIF     – rock Jun 15 '12 at 13:28
  • Well, in SQL same scenario looks like "SELECT data FROM tablename ORDER BY name", whithout any using() or similar. – Arvo Jun 15 '12 at 13:44

2 Answers2

1

I moved from Foxpro to SQL ages ago. Quick rundown of how it works in SQL Server:

  • In SQL Server, tables (and indexes, views, stored procedures, and loads of other stuff) are defined/created within databases.
  • The entire table is stored within a single database; a database can contain a heck of a lot of tables (and other stuff).
  • All the contents of a database are stored within files. Files are not shared across databases. There will be at least two files for each database, 1 data and 1 log. (It can get complex fast.)
  • When a database is "open", all of its contents (all tables, views, etc.) are available for use. Think of it like Word or Excel: if the file's open, you can work with it (and the files are locked and cannot be modified through the OS or any other app); if the files are closed, the contents are not accessible and you can delete or rename them. In SQL, it's much much more efficient to always have your databases open.
  • SQL has lots of access and premissions control. Just because the database is open and available for use does not mean that anyone can access it. Logins allow you to open Connections to SQL; Users are defined within databases for Logins and are used to control which objects (tables etc.) within the database a given login can access.
  • So: a user or application opens a connection to SQL Server via a Login. The login is granted access rights to specified databases, and to specified objects within those databases. Once the connection is open, you/the application can access anything they want to. However:
  • Every connection has a "current database" context, and that can feel very much like the Foxpro USE command. Examples follow.

Say my login has access to two databases, TestA and TestB. Both databases contain table MyData, and each table contains different data (A data and B data). When I log in, my context gets set to one database, lets say TestA. If I run SELECT * from MyData, I will get the contents of that table from database TestA. If I wanted to get the data from TestB, I would either have to run SELECT * from TestB.dbo.MyDta -- specifying the database I'm gettng the data from -- or I'd have to change the context of the database; programatically, this is done with the Use command, e.g. `USE TestB'.

This is a very simplistic description of "how it works". It's all programming, which means there are dozens of way to perform a given action, depending on the tools (applications) you are using and tasks you seek to achive. I strongly recommend reading up on, well, everything in SQL Books Online, the documentation that comes with SQL Server -- Microsoft did a very good job documenting this product.

Philip Kelley
  • 39,426
  • 11
  • 57
  • 92
  • Thanks for your info Philip! I am not new to SQL but new to old FoxPro.. hence finding it doubly difficult to migrate it to SQL! – rock Jun 15 '12 at 15:58
1

Philip is correct about everything being "open" in SQL. However, I think I would express it a little differently. When SQL-Server is running (since it's typically an automated service that starts when the server itself starts), ANY "database" that has been "attached" is available to query from and is basically untouchable from the OS anywhere else because SQL has an open "handle" to the file, thus preventing copying, deleting, moving, etc...

If you did want to copy/move to other location or even another server, you would have to detach the "database" which releases the handle and you can do whatever.

As for what you can do in SQL-Server. You don't even have to open the database explicitly but can qualify your queries by database.table reference... such as

select t1.field
   from YourDatabase.SomeTable t1
   where t1.SomeField = ?whatever

As long as the database is attached, you should be good to go.

DRapp
  • 47,638
  • 12
  • 72
  • 142
  • Thank you DRapp! I just omitted the USED() in foxpro while rewriting that sp to sql.. It worked fine ! – rock Jun 18 '12 at 17:26
  • @ranjith, glad to help... Just an FYI though. Being a newbie, if you don't start marking applicable answers as "check marked" and upvoting those that help, future questions might not get answered. It helps by "thanking" those posting answers, and it also helps others to know a question IS solved... – DRapp Jun 19 '12 at 01:24