Professional Web Applications Themes

Do I have this right? - Microsoft SQL / MS SQL Server

Calling SQL Server 2k from Access97 through ADO using sqloledb.1 Views Sorting= No Parameters= Yes Updatable= Yes Stored Proc Sorting= Yes Parameters= Yes Updatable= No UDF Scalar Sorting= NA Parameters= Yes Updatable= No UDF InlineTable Sorting= Yes Parameters= Yes Updatable= Yes UDF MultiLine Sorting= Yes Parameters= Yes Updatable= No If I want an updatable ADODB.Recordset in Access I must use either Views or an InlineTable UDF. If I want the recordset sorted I should use UDF. If I don't need sorting, calling a view is more efficient. As you can tell, I am new to Sql Server. Any suggestions on ...

  1. #1

    Default Do I have this right?

    Calling SQL Server 2k from Access97 through ADO using sqloledb.1

    Views Sorting= No Parameters= Yes Updatable= Yes
    Stored Proc Sorting= Yes Parameters= Yes Updatable= No
    UDF Scalar Sorting= NA Parameters= Yes Updatable= No
    UDF InlineTable Sorting= Yes Parameters= Yes Updatable= Yes
    UDF MultiLine Sorting= Yes Parameters= Yes Updatable= No

    If I want an updatable ADODB.Recordset in Access I must use either
    Views or an InlineTable UDF. If I want the recordset sorted I should
    use UDF. If I don't need sorting, calling a view is more efficient.

    As you can tell, I am new to Sql Server. Any suggestions on working
    with Sql Server 2k from within Access97 or AccessXP?

    Thanks,
    Brad
    Brad Guest

  2. #2

    Default Re: Do I have this right?

    Brad wrote: 

    Views are treated in SQL as if they were tables, so no parameters.
     

    You can certainly create an updatable recordset from a stored procedure - I
    do it all the time. Where did you get the idea that you couldn't?
     
    UDF's are not really relevant here. You can call a UDF using this technique:
    Dim cn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset

    cn.Open "Provider=sqloledb;Data Source=MyServerName;" & _
    "Initial Catalog=northwind;User Id=sa;Password=;"

    'Prepare the user-defined function statement and execute the command.
    Cmd.ActiveConnection = cn
    Cmd.CommandText = "select * from ::fn_helpcollations()"
    Set rs = Cmd.Execute

    rs.Close

    But as you can see, you're not really calling it directly: you have to use
    it in the FROM clause of a Select statement. You may as well use a stored
    procedure for this.

    SQL Books Online (BOL) is your friend. If you don't have it, you can
    download it from here:
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

    HTH,
    Bob Barrows


    Bob Guest

  3. #3

    Default Re: Do I have this right?

    Thanks for the great help Bob. I have no idea why I thought Stored
    Procedures were not updatable. I guess I am reading too much too fast.
    I really did try to look this up in SQL BOL but I get frustrated when
    I can't find it. I tried BOL again and I finally did find the exact
    example you listed. So, thanks for the encouragement. I will try BOL
    more. I also appreciate hearing that I should just stick to Stored
    Procs. I kept reading how user-defined functions were the cool new way
    to go so I thought I would learn that first.

    I have now successfully called a Stored Proc and passed parameters to
    it so I guess I will do most of my work that way. Just in case, I
    assume you pass parameters to a UDF like this: 
    Is that right? I couldn't find it in BOL.

    I also assume that you suggested I just stick to Stored Procs since
    they will be slightly faster than calling a UDF since the Select
    statement will not have a stored plan.

    Thanks again for your help!
    Brad
    Brad Guest

  4. #4

    Default Re: Do I have this right?

    Brad wrote: 

    They are cool. Prior to SQL2K, there was no way to create UDF's that could
    be used in queries the way you could with VBA functions in Access queries.
     

    There are several ways to do this. I assume you are using an explicit
    Command object. An easier way is this:

    Set rs = new adodb.recordset
    'optionally, set cursor location, type and locktype properties, then
    cn.procedurename parmval1,...parmvalN,rs

    (This will also work with Access saved queries BTW - substitute the query's
    name for "procedurename")
     
    > Is that right? I couldn't find it in BOL.[/ref]

    Yes. But again, you really won't need to do this. Table-type UDF's are
    intended to be used within stored procedures instead of temp tables.
     

    Sort of. Think of UDF's as something to be used within T-SQL. Client
    programs should use stored procedures or dynamic SQL to communicate with the
    server.

    HTH,
    Bob Barrows


    Bob Guest

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139