Professional Web Applications Themes

connection variable in Function of SQL server - Microsoft SQL / MS SQL Server

my thank to 1.Greg Low \(MVP\) --MSDE Manager SQL Tools 2.Ron Talmage -- SQL Server MVP for giving me there presuise time to solve my question. but, I have solved the problem so I will: 1.State The Problem as Much as I can 2.detail why I wont use the answers of the two friendly men 3.give the detailed sulotion to all so it might be of any benifit to any one. Problem-Stated: we are making an accounting program using Access XP as Front-End and SQL Server 2000 (MSDE) as the DB, Info: know Access make three connection when it connects ...

  1. #1

    Default Re: connection variable in Function of SQL server




    my thank to
    1.Greg Low \(MVP\) --MSDE Manager SQL Tools
    2.Ron Talmage -- SQL Server MVP


    for giving me there presuise time to solve my question.

    but,
    I have solved the problem so I will:
    1.State The Problem as Much as I can
    2.detail why I wont use the answers of the two friendly men
    3.give the detailed sulotion to all so it might be of any benifit to
    any one.


    Problem-Stated:
    we are making an accounting program using Access XP as Front-End and SQL
    Server 2000 (MSDE) as the DB,
    Info: know Access make three connection when it connects to SQL Server
    1.connection you can get its connection string by
    [CurrentProject.BaseConnection]
    2.Connection that you can get by executing a query in the [.RowSource]
    of a comboBox of a form I call it [Forms Connection]
    3.Connection that I do not know how to get it's connection String or
    any handle to it.

    know the one that I am intrested in is (2) becuase:
    I want to make a transact-sql function that returns all the accounts
    that a user is allowed to see in a combobox when he loggOn to the
    program
    and opens a billing form(window) so either I make a string that uses
    the function and passes the userID and the logged
    (User Privelge Group )
    and that will mean to make a function and execute it every time the
    user uses the comboBox or opens a window that has an Account comboBox
    in its [.RowSource] and that is to much

    replyes and their issues:
    1.know to use the [set Context_Info]
    a. first it does not have enugh space for what I need to save for
    every connection to the DB
    b. I must have a way to save the [SPID] and if I do the I must pass
    it to every function and that means a programming language other than
    sql and that I do not want
    c. I do not know how to convert to binary and backward return it
    to its Ansii readable format.

    2. to use [OpenRowSet]:
    a. same as 1.b
    b. I tried to use it in a function but I did not successed so I do not
    now if it can be used in Transact-SQL Functions.

    the solution that I have used and
    I would want a better solution If any one has please E-Mail it to me.
    I made a Transact-SQL Procedure that uses a table that stores in it
    the [SPID] and what ever I want and its source:
    ALTER PROCEDURE dbo.stp_ConnectionCont
    (
    buildingNo varChar(40),
    people_ID bigint,
    privelegeGroupNo varChar(40),
    storeNo varChar(40),
    projectNo varChar(40),
    accountNo varChar(40)
    )

    AS

    declare intError int
    set intError=0

    --Connection Context Options
    exec stp_ConnectionContSetOptions

    delete ConnectionContext where spid not in(SELECT spid FROM
    master.dbo.sysprocesses)

    insert into Connectioncontext (spid, people_ID, BuildingNo,
    PrivelegeGroupNo, storeNo, ProjectNo, accountNo)
    select spid, people_ID, BuildingNo, privelegeGroupNo, storeNo,
    projectNo, accountNo
    where spid not in (select spid from Connectioncontext)

    Update Connectioncontext
    set people_ID= people_ID,
    BuildingNo= BuildingNo,
    PrivelegeGroupNo= privelegeGroupNo,
    storeNo= storeNo,
    ProjectNo= projectNo,
    accountNo= accountNo
    where spid=spid

    select spid, people_ID, BuildingNo, privelegeGroupNo, storeNo,
    projectNo, accountNo

    that is Executed in the .RowSource of a comboBox when the user loggOn
    and passing to it the logging parameters.
    (again I use the .RowSource of the Combo] becuase we can take from it
    the [SPID] of the [Forms Connection] )
    so know I have the info of the looggOn saved in a table whith the SPID
    as its Primary and its source is:
    CREATE TABLE [ConnectionContext] (
    [spid] [TypeInteger_Dom] NOT NULL ,
    [people_ID] [TypePeople_ID_Dom] NULL ,
    [buildingNo] [BuildingNo_Dom] NULL ,
    [PrivelegeGroupNo] [TypeVarChar12_Dom] NULL ,
    [storeNo] [BuildingNo_Dom] NULL ,
    [projectNo] [ProjectNo_Dom] NULL ,
    [accountNo] [AccountNo_Dom] NULL ,
    CONSTRAINT [PK_CONNECTIONCONTEXT] PRIMARY KEY CLUSTERED
    (
    [spid]
    ) ON [PRIMARY]
    ) ON [PRIMARY]

    and every time the user loggOn or exits the Program I execute this
    procedure becuase it will delete any non used spid (connection that has
    closed)
    so by this procedure I can execute any SQL-Function that can query the
    table whith filtering it by (SPID) and then I would have the loggOn
    data and uses it to return what ever records(in our case Accounts) that
    are allowd for that user to see with out having to pass any params to
    it.

    and Again I than those ho helped me in there Ideas.



    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    reader man Guest

  2. #2

    Default Re: connection variable in Function of SQL server




    my thank to
    1.Greg Low \(MVP\) --MSDE Manager SQL Tools
    2.Ron Talmage -- SQL Server MVP


    for giving me there presuise time to solve my question.

    but,
    I have solved the problem so I will:
    1.State The Problem as Much as I can
    2.detail why I wont use the answers of the two friendly men
    3.give the detailed sulotion to all so it might be of any benifit to
    any one.


    Problem-Stated:
    we are making an accounting program using Access XP as Front-End and SQL
    Server 2000 (MSDE) as the DB,
    Info: know Access make three connection when it connects to SQL Server
    1.connection you can get its connection string by
    [CurrentProject.BaseConnection]
    2.Connection that you can get by executing a query in the [.RowSource]
    of a comboBox of a form I call it [Forms Connection]
    3.Connection that I do not know how to get it's connection String or
    any handle to it.

    know the one that I am intrested in is (2) becuase:
    I want to make a transact-sql function that returns all the accounts
    that a user is allowed to see in a combobox when he loggOn to the
    program
    and opens a billing form(window) so either I make a string that uses
    the function and passes the userID and the logged
    (User Privelge Group )
    and that will mean to make a function and execute it every time the
    user uses the comboBox or opens a window that has an Account comboBox
    in its [.RowSource] and that is to much

    replyes and their issues:
    1.know to use the [set Context_Info]
    a. first it does not have enugh space for what I need to save for
    every connection to the DB
    b. I must have a way to save the [SPID] and if I do the I must pass
    it to every function and that means a programming language other than
    sql and that I do not want
    c. I do not know how to convert to binary and backward return it
    to its Ansii readable format.

    2. to use [OpenRowSet]:
    a. same as 1.b
    b. I tried to use it in a function but I did not successed so I do not
    now if it can be used in Transact-SQL Functions.

    the solution that I have used and
    I would want a better solution If any one has please E-Mail it to me.
    I made a Transact-SQL Procedure that uses a table that stores in it
    the [SPID] and what ever I want and its source:
    ALTER PROCEDURE dbo.stp_ConnectionCont
    (
    buildingNo varChar(40),
    people_ID bigint,
    privelegeGroupNo varChar(40),
    storeNo varChar(40),
    projectNo varChar(40),
    accountNo varChar(40)
    )

    AS

    declare intError int
    set intError=0

    --Connection Context Options
    exec stp_ConnectionContSetOptions

    delete ConnectionContext where spid not in(SELECT spid FROM
    master.dbo.sysprocesses)

    insert into Connectioncontext (spid, people_ID, BuildingNo,
    PrivelegeGroupNo, storeNo, ProjectNo, accountNo)
    select spid, people_ID, BuildingNo, privelegeGroupNo, storeNo,
    projectNo, accountNo
    where spid not in (select spid from Connectioncontext)

    Update Connectioncontext
    set people_ID= people_ID,
    BuildingNo= BuildingNo,
    PrivelegeGroupNo= privelegeGroupNo,
    storeNo= storeNo,
    ProjectNo= projectNo,
    accountNo= accountNo
    where spid=spid

    select spid, people_ID, BuildingNo, privelegeGroupNo, storeNo,
    projectNo, accountNo

    that is Executed in the .RowSource of a comboBox when the user loggOn
    and passing to it the logging parameters.
    (again I use the .RowSource of the Combo] becuase we can take from it
    the [SPID] of the [Forms Connection] )
    so know I have the info of the looggOn saved in a table whith the SPID
    as its Primary and its source is:
    CREATE TABLE [ConnectionContext] (
    [spid] [TypeInteger_Dom] NOT NULL ,
    [people_ID] [TypePeople_ID_Dom] NULL ,
    [buildingNo] [BuildingNo_Dom] NULL ,
    [PrivelegeGroupNo] [TypeVarChar12_Dom] NULL ,
    [storeNo] [BuildingNo_Dom] NULL ,
    [projectNo] [ProjectNo_Dom] NULL ,
    [accountNo] [AccountNo_Dom] NULL ,
    CONSTRAINT [PK_CONNECTIONCONTEXT] PRIMARY KEY CLUSTERED
    (
    [spid]
    ) ON [PRIMARY]
    ) ON [PRIMARY]

    and every time the user loggOn or exits the Program I execute this
    procedure becuase it will delete any non used spid (connection that has
    closed)
    so by this procedure I can execute any SQL-Function that can query the
    table whith filtering it by (SPID) and then I would have the loggOn
    data and uses it to return what ever records(in our case Accounts) that
    are allowd for that user to see with out having to pass any params to
    it.

    and Again I than those ho helped me in there Ideas.



    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    reader man Guest

Similar Threads

  1. [Q] Variable function
    By Jon in forum PHP Development
    Replies: 0
    Last Post: September 27th, 11:28 AM
  2. Replies: 2
    Last Post: September 29th, 07:11 PM
  3. #25393 [Bgs->Opn]: Session Variable is cleared with every connection
    By zoop at lone dot ath dot cx in forum PHP Development
    Replies: 0
    Last Post: September 6th, 02:36 PM
  4. Replies: 0
    Last Post: September 6th, 09:07 AM
  5. #25393 [NEW]: Session Variable is cleared with every connection
    By zoop at lone dot ath dot cx in forum PHP Development
    Replies: 0
    Last Post: September 6th, 06:46 AM

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