Connections and ##temp tables
in [Programming SQL]
|
Prev: Multiple Items in Listbox
Next: Covering index
From: Jonathan on 10 May 2010 18:57 Hi, using Access 2003 and SQL 2005. I have an Access application that uses a combination of pass-through queries and dao recordsets to load forms and reports. This is not an Access Project. My question is about design. A search populates a table with parent record IDs. I then have a bunch of stored procedures that use an inner join on this table to filter records. In a multiuser environment what is the best practice method to ensure that the table of IDs is not compromised by an user's search results? For example, will using a ##temp table to hold the search result IDs give the required outcome? Any ideas or recommendations appreciated :-) Many thanks, Jonathan
From: Sylvain Lafontaine on 11 May 2010 00:02 ##temp tables are global temporary tables, so the possibility of having its content getting compromised in a multiuser environement is very high. You should either create a local temporary table #temp outside of any stored procedures - otherwise it will be deleted at the end of the SP - or you should use a permanent table but add to it some sort of id to separate the result of each search. You can also add a time value and use it for say deleting anything that is 48 hours old whatever the user is. -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "Jonathan" <Jonathan(a)discussions.microsoft.com> wrote in message news:D1C4DECF-A7E6-4C2E-BB85-A03F83B77B7A(a)microsoft.com... > Hi, using Access 2003 and SQL 2005. I have an Access application that uses > a > combination of pass-through queries and dao recordsets to load forms and > reports. This is not an Access Project. My question is about design. > > A search populates a table with parent record IDs. I then have a bunch of > stored procedures that use an inner join on this table to filter records. > In > a multiuser environment what is the best practice method to ensure that > the > table of IDs is not compromised by an user's search results? For example, > will using a ##temp table to hold the search result IDs give the required > outcome? > > Any ideas or recommendations appreciated :-) > > Many thanks, > Jonathan
From: Jonathan on 11 May 2010 02:00 Thanks Sylvain. Can you tell me how a local temporary table #temp is "linked" to the current user? That is, does every process using the #temp table need to be using the same connection? Jonathan "Sylvain Lafontaine" wrote: > ##temp tables are global temporary tables, so the possibility of having its > content getting compromised in a multiuser environement is very high. > > You should either create a local temporary table #temp outside of any stored > procedures - otherwise it will be deleted at the end of the SP - or you > should use a permanent table but add to it some sort of id to separate the > result of each search. You can also add a time value and use it for say > deleting anything that is 48 hours old whatever the user is. > > -- > Sylvain Lafontaine, ing. > MVP - Windows Live Platform > Blog/web site: http://coding-paparazzi.sylvainlafontaine.com > Independent consultant and remote programming for Access and SQL-Server > (French) > > > "Jonathan" <Jonathan(a)discussions.microsoft.com> wrote in message > news:D1C4DECF-A7E6-4C2E-BB85-A03F83B77B7A(a)microsoft.com... > > Hi, using Access 2003 and SQL 2005. I have an Access application that uses > > a > > combination of pass-through queries and dao recordsets to load forms and > > reports. This is not an Access Project. My question is about design. > > > > A search populates a table with parent record IDs. I then have a bunch of > > stored procedures that use an inner join on this table to filter records. > > In > > a multiuser environment what is the best practice method to ensure that > > the > > table of IDs is not compromised by an user's search results? For example, > > will using a ##temp table to hold the search result IDs give the required > > outcome? > > > > Any ideas or recommendations appreciated :-) > > > > Many thanks, > > Jonathan > > > . >
From: Sylvain Lafontaine on 11 May 2010 02:36 Temporary table #temp are not linked to the current user but to a single connection and the fact that you using a MDB file with ODBC Linked tables and passthrough queries complicate the matter; especially if connections pooling is enabled. I don't know how you are coding your stuff but unless you can directly return the result through a � Select * from #temp � at the end of the batch, I would suggest that you use a permanent table with an identifiant to separate the multiple results from each other. -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "Jonathan" <Jonathan(a)discussions.microsoft.com> wrote in message news:8A40FBC7-7BF3-4EAD-9970-EB8212A6BE17(a)microsoft.com... > Thanks Sylvain. > > Can you tell me how a local temporary table #temp is "linked" to the > current > user? That is, does every process using the #temp table need to be using > the > same connection? > > Jonathan > > "Sylvain Lafontaine" wrote: > >> ##temp tables are global temporary tables, so the possibility of having >> its >> content getting compromised in a multiuser environement is very high. >> >> You should either create a local temporary table #temp outside of any >> stored >> procedures - otherwise it will be deleted at the end of the SP - or you >> should use a permanent table but add to it some sort of id to separate >> the >> result of each search. You can also add a time value and use it for say >> deleting anything that is 48 hours old whatever the user is. >> >> -- >> Sylvain Lafontaine, ing. >> MVP - Windows Live Platform >> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com >> Independent consultant and remote programming for Access and SQL-Server >> (French) >> >> >> "Jonathan" <Jonathan(a)discussions.microsoft.com> wrote in message >> news:D1C4DECF-A7E6-4C2E-BB85-A03F83B77B7A(a)microsoft.com... >> > Hi, using Access 2003 and SQL 2005. I have an Access application that >> > uses >> > a >> > combination of pass-through queries and dao recordsets to load forms >> > and >> > reports. This is not an Access Project. My question is about design. >> > >> > A search populates a table with parent record IDs. I then have a bunch >> > of >> > stored procedures that use an inner join on this table to filter >> > records. >> > In >> > a multiuser environment what is the best practice method to ensure that >> > the >> > table of IDs is not compromised by an user's search results? For >> > example, >> > will using a ##temp table to hold the search result IDs give the >> > required >> > outcome? >> > >> > Any ideas or recommendations appreciated :-) >> > >> > Many thanks, >> > Jonathan >> >> >> . >>
From: Bob McClellan on 11 May 2010 10:53
Jonathan, One method is to use a staging table that is purged and repopulated as per the end user's search criteria. add a column to this table: [CurrentUser] when you fire the sp... first: delete from YourTable where CurrentUser = @cu then, Insert into YourTable ( col1, col2, CurrentUser , etc.. ) select ( col1, col1, @cu, etc... ) hth, ...bob "Jonathan" <Jonathan(a)discussions.microsoft.com> wrote in message news:D1C4DECF-A7E6-4C2E-BB85-A03F83B77B7A(a)microsoft.com... > Hi, using Access 2003 and SQL 2005. I have an Access application that uses > a > combination of pass-through queries and dao recordsets to load forms and > reports. This is not an Access Project. My question is about design. > > A search populates a table with parent record IDs. I then have a bunch of > stored procedures that use an inner join on this table to filter records. > In > a multiuser environment what is the best practice method to ensure that > the > table of IDs is not compromised by an user's search results? For example, > will using a ##temp table to hold the search result IDs give the required > outcome? > > Any ideas or recommendations appreciated :-) > > Many thanks, > Jonathan |