Professional Web Applications Themes

Accessing Temp Tables - Microsoft SQL / MS SQL Server

Hello Graham, You could try using global temp tables with a "##", in your case it would be - ##UserAccess. K "Graz79" <graham.smithorchard-systems.co.uk> wrote in message news:096c01c347ac$dd8bf5b0$a501280aphx.gbl... > Have the following stored procedure > *** > > CREATE PROCEDURE spUserAccess > UserName varchar(8), > FormName varchar(50) > AS > CREATE TABLE #UserAccess (FormName varchar(50)) > > --check if username is admin if so return all rows > from program security > IF UserName = 'adm' > BEGIN > INSERT INTO #UserAccess > > SELECT DISTINCT FormName > FROM ProgramSecurity > > END > ELSE > BEGIN > --returns all the ...

  1. #1

    Default Re: Accessing Temp Tables

    Hello Graham,

    You could try using global temp tables with a "##", in your case it would
    be - ##UserAccess.

    K

    "Graz79" <graham.smithorchard-systems.co.uk> wrote in message
    news:096c01c347ac$dd8bf5b0$a501280aphx.gbl...
    > Have the following stored procedure
    > ***
    >
    > CREATE PROCEDURE spUserAccess
    > UserName varchar(8),
    > FormName varchar(50)
    > AS
    > CREATE TABLE #UserAccess (FormName varchar(50))
    >
    > --check if username is admin if so return all rows
    > from program security
    > IF UserName = 'adm'
    > BEGIN
    > INSERT INTO #UserAccess
    >
    > SELECT DISTINCT FormName
    > FROM ProgramSecurity
    >
    > END
    > ELSE
    > BEGIN
    > --returns all the forms/controls a
    > user has access to
    > INSERT INTO #UserAccess
    >
    > SELECT DISTINCT FormName
    > FROM OrchardStaffTeams
    > O INNER JOIN ProgramSecurity P ON O.Team = P.Team
    > WHERE UserName =
    > UserName
    > AND P.SecurityLevel <=
    > O.SecurityLevel
    >
    > END
    > GO
    > ***
    >
    > This builds a list of all forms the current user has
    > security on within a vb application. The stored procedure
    > is called from Visual Basic once the user logs in.
    >
    > The problem is when I come to check this table later in
    > the application the table no longer exists even though I
    > have not closed the connection to the server. Has anyone
    > come across this before. Can anyone suggest an
    > alternative? Thanks

    Karthik Nagaraj Guest

  2. #2

    Default Re: Accessing Temp Tables


    Even if I create the table first it is not available when I call the
    stored procedure. The front end is in vb and I am using ADO recordsets.

    If I use a Global temp table will this not cause problems with multiple
    users? Each user who logs in calls the same stored procedure so gets
    there on file list for that session.


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

  3. #3

    Default Re: Accessing Temp Tables

    Create a real table. Doesn't sound like you need a #temp table at all.



    "Graham Smith" <graham.smithorchard-systems.co.uk> wrote in message
    news:euDyiP7RDHA.2480tk2msftngp13.phx.gbl...
    >
    > Even if I create the table first it is not available when I call the
    > stored procedure. The front end is in vb and I am using ADO recordsets.
    >
    > If I use a Global temp table will this not cause problems with multiple
    > users? Each user who logs in calls the same stored procedure so gets
    > there on file list for that session.
    >
    >
    > *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    > Don't just participate in USENET...get rewarded for it!

    Aaron Bertrand - MVP Guest

  4. #4

    Default Re: Accessing Temp Tables

    > When they login into the vb application it runs this sp that creates a
    > table of access rights for that user. If a second user signs in they
    > require there own table (i thought temp tables would be the easiest way
    > as each session is kept individual)
    Why on earth do they each need their own table? How about a table for all
    users, with sessionID as a primary key?


    Aaron Bertrand - MVP Guest

  5. #5

    Default Re: Accessing Temp Tables

    Take the 100+ user load off the server and have the clients
    store the data locally. The application can obtain the access
    rights and store them on the client machine (array, xml etc).


    "Graham Smith" <graham.smithorchard-systems.co.uk> wrote in message
    news:uLOkHx7RDHA.2188TK2MSFTNGP10.phx.gbl...
    > I have 100+ Users
    > When they login into the vb application it runs this sp that creates a
    > table of access rights for that user. If a second user signs in they
    > require there own table (i thought temp tables would be the easiest way
    > as each session is kept individual)
    >
    > How would I go about creating a "proper" table for each user that could
    > be uniquely identified?
    >
    > Thanks
    >
    >
    >
    > *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    > Don't just participate in USENET...get rewarded for it!

    Daniel Johnson Guest

  6. #6

    Default Re: Accessing Temp Tables

    I agree with Aaron, why use a temp table for this. And why recreate this
    each time they log in? How often do their permissions change? Usually not
    very often and it would be easy enough to rebuild that users security data
    when it does. I would opt for a single table for all users with the
    security data pre-calculated.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Graham Smith" <graham.smithorchard-systems.co.uk> wrote in message
    news:uLOkHx7RDHA.2188TK2MSFTNGP10.phx.gbl...
    > I have 100+ Users
    > When they login into the vb application it runs this sp that creates a
    > table of access rights for that user. If a second user signs in they
    > require there own table (i thought temp tables would be the easiest way
    > as each session is kept individual)
    >
    > How would I go about creating a "proper" table for each user that could
    > be uniquely identified?
    >
    > Thanks
    >
    >
    >
    > *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    > Don't just participate in USENET...get rewarded for it!

    Andrew J. Kelly Guest

  7. #7

    Default Re: Accessing Temp Tables --individuals

    It will not create a problem if the user is unique, I am not sure session ID
    buys you anything.

    ....Until they tell you security level is not good enough. That different
    organizations
    delegate different kinds of permissions to different controls.

    If you can stop things mid stride, go look at tempdb and see if the table is
    really gone.
    It may have a different owner.

    You may want to add the user name to the table.

    Which is a way to make the global temp
    idea work (no coding solution) put the user name in the global table
    '##_' + USERNAME + '_UserAccess'
    or
    USERNAME + '.' + '##UserAccess'

    "Graham Smith" <graham.smithorchard-systems.co.uk> wrote in message
    news:euDyiP7RDHA.2480tk2msftngp13.phx.gbl...
    >
    > Even if I create the table first it is not available when I call the
    > stored procedure. The front end is in vb and I am using ADO recordsets.
    >
    > If I use a Global temp table will this not cause problems with multiple
    > users? Each user who logs in calls the same stored procedure so gets
    > there on file list for that session.
    >
    >
    > *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    > Don't just participate in USENET...get rewarded for it!

    Jay Schmitendorf Guest

Similar Threads

  1. SP Temp Tables
    By navdeep virk in forum Informix
    Replies: 6
    Last Post: February 18th, 04:30 PM
  2. Dynamic identification of Informix temp tables
    By ddodgeaz in forum Informix
    Replies: 2
    Last Post: August 29th, 09:31 PM
  3. Temp tables and jdbc 2.21.jc3 on IDS 9.4.UC1
    By Rajesh Kapur in forum Informix
    Replies: 0
    Last Post: August 26th, 09:59 PM
  4. Schedule maitenance or temp tables
    By Shawn in forum ASP Database
    Replies: 2
    Last Post: August 21st, 03:12 PM
  5. Question about optimizer with (big) temp tables
    By Eric Mamet in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 08:44 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