Solved

Typically speaking what a Cursor & Handle mean?

Posted on 1998-08-27
5
276 Views
Last Modified: 2010-03-19
1.   I need to know what is a cursor & what for its used>
     Is it same as Handle.Then what's its purpose and list      out the advantages and disadvantages of each of them

2.  What is win16 and win32 api, where's used.Give me where its used exactly.

3. What are the Trigger Types in sql Server.

4. Often I encounter "DB CONNECTION BROKEN"
   when executing left/right and inner join simultaneously in a query. ie In other words if I give left as (*=) and right as (=*) and inner as (=) in WHERE clause I get this error.
Suppose If I give Right join as Right Join , Left as Left Join and Inner as Join I don't get this problem.

Can anyone help me out what might be the problem and what does it mean by "DB BROKEN BROKEN".?

5.  Is SQL SERVER BETTER THAN ORACLE AND IF SO IN WHAT WAY THE FORMER OR LATTER IS BETTER. BRIEF ME WITH DETAILS. DIFFERENCE BETWEEN ORACLE AND SQL SERVER

6. I want to prepare for Microsoft Certification for which I need to Know what books I should look into . Is there any sites where can I find the Questions(previously attended/sample questions, put up by the students).similar to newsgroup like this one.


7. What is meant by SQL PASS THRU QUERY. GIVE AN ILLUSTRATION.

8. WHAT IS THE DIFFERENCE BETWEEN EXECUTE METHOD AND EXECUTE SQL.
.
0
Comment
Question by:Favourites
  • 2
  • 2
5 Comments
 
LVL 4

Expert Comment

by:mcix
ID: 1089805
I don't have time to get into as many details as you requested, but I can tell you an excellent book for SQL Server is:

Inside Microsoft SQL Server 6.5 by Ron Soukup  

ISBN:   1572313315
Publisher:   Microsoft Press
Pub. Date:   November 1997


0
 
LVL 2

Accepted Solution

by:
mkmccreary earned 50 total points
ID: 1089806
That is a fairly long question, I'll see if I can help you out.  I agree with the above comment on the book, it is written by a guy who was on the MSSQL Development Team.

1)A cursor is basically a record-pointer in a subset of records.  If you are use to Recordset objects, the cursor functionality is hidden from you.  It is used to acquire information from a specific record in a subset.  I use cursors in stored procedures and triggers to acquire and set field values on a particular record.  Here is an example of a cursor I used on an insert trigger to acquire information from the 'inserted' records.  (This is the same code I gave Thabo on his question)

CREATE TRIGGER trigDocument_Insert ON dbo.Document
FOR INSERT
AS
      declare @MailReturn int,
            @AMessage varchar(255),
            @TheKey int,
            @ThisBox varchar (255),
            @DocType varchar (255),
            @lDocType int,
            @CaseNumber int,
            @Description varchar (255)

            
      declare curInserted insensitive cursor
            for select Inserted.lPrimaryKey, lDocumentType, lCaseNumber, szDescription, szDocumentDescription, szQueueName
            From Inserted, DocumentType
            Where DocumentType.lPrimaryKey = lDocumentType

      open curInserted

      fetch curInserted into @TheKey, @lDocType, @CaseNumber, @Description, @DocType, @ThisBox

      while @@fetch_status = 0
      begin


            select @AMessage = ('Record Number = ' + convert(varchar(10),@TheKey) + '   Case Number = ' + convert(varchar(10),@CaseNumber) + '   Document Type = ' + @DocType + '   Description = ' + @Description + '    Queue Name = ' + @ThisBox)

            exec @MailReturn = master..xp_sendmail @recipients = @ThisBox,
                  @message = @AMessage,
                  @subject = 'SQL Mail Test'

            if @MailReturn <> 0
            begin
                  insert MailErrors ( lErrorCode, szErrorDescription, szRecipientName, szTableName, lRecordIdentity )
                        values ( @MailReturn, '', @ThisBox, 'Document', @TheKey)
            end

            fetch curInserted into @TheKey, @lDocType, @CaseNumber, @Description, @DocType, @ThisBox
      end

      



      close curInserted
      deallocate curInserted


2) The Win16 (Windows 3.x), Win32s (Windows 3.x), and Win32 (Windows 9x, NT) APIs (Application Program Interface) are a list of function calls located in DLLs that part of the Windows Operating System (except Win32s which is an add on for Windows 3.x).  Before the advent of better visual development tools, you used the APIs to draw windows and controls, to intercept events (mouse clicks, button presses, etc.).  There are still some things that can't be accomplished from these development tools directly, so you try to find an appropriate API call, and there usually is one.  I've used API calls to login to network resources, determine drive types (network, cd, floppy, etc.), and to print specific things to the printer, for a few examples.  There are some great books out there on the APIs, depending on what type of development you are doing.  Some of the best I've seen have been the Dan Appleman's Guides to the Win APIs.  More information on his book is at www.desaware.com.

3)Insert, Delete, and Update.

4) Just need more information

5) Oracle is considered better by most, except in the area of administration, which I read they have fixed in a recent version.  List is to long about the differences between the databases.  Basically Oracle has better performance; it runs on multiple UNIX, Mainframe, Mini, and PC platforms; and has a larger features set; but it is also more expensive.  I have no personal experience with Oracle, these are just things I've heard.

6)I haven't taken the certification test, but the book listed previously covers it all.

7) I'm assuming you are using MS Access.  A pass through query is not preprocessed by the JET engine query optimizer.  This is especially handy when you are using SQL syntax that the preprocessor doesn't understand, but the server does.

8)I'm assuming this is in some form of VBA.  The Execute method is the method of choice.  The ExecuteSQL method is kept for compatibility with previous versions of DAO.  The ExecuteSQL method is considered obsolete.
0
 
LVL 4

Expert Comment

by:mcix
ID: 1089807
Great answer Martin!
0
 

Author Comment

by:Favourites
ID: 1089808
This is reg. the first Question
You have'nt given me the answer for what is an handle and how's its used and in what situations.

I need to have more light on 7th and 8 th Question.
I need more information on Sql Pass thru Query using Visual Basic and not Access. Pl. give an illustration .

Reg. the Execute  and Execute SQL  I need more clarification with respect to how it is being handled from visual basic .



0
 
LVL 2

Expert Comment

by:mkmccreary
ID: 1089809
There are several types of handles, it depends on what you are talking about.  There are environment handles which are used with the ODBC API, there are window handles that are used in the Windows API, etc.  Basically a handle is a long integer that is a reference to a particular area of memory, this memory offset usually has a base address associated with it that the API addresses.

Say you are using DAO to connect to SQL Server and want to execute the stored procedure:
    "exec master..xp_sendmail @recipients = 'MyMailbox'"
Well, DAO does not know how to handle this, and you will get a syntax error, unless you set the dbSQLPassThrough option.  This tells DAO (the JET Engine) to ignore the syntax of the query, that the server will handle it.

Don't use ExecuteSQL, it is obsolete.  This basically means in the near future Microsoft is going to drop support for the function, period.  This becomes an issue when you try to upgrade your code, no matter what environment your in (VB, Access, etc.).  Use the Execute function instead, it has improved functionality, and should be supported for quite a while.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS Dynamics AX 2009 2012 Database/Report Optimization 3 28
C# SQL BULK INSERT CLASS 5 35
SQL JOIN 6 35
sql calculate averages 18 29
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now