I have a stored procedure that creates a temporary table, populates it
with one record, and then returns that record to an ASP. I've read
that by prefixing the the tablename with a #, that only the connection
that created it can access it.
An IIS server will be making the connection and uses the same
connection string for all users. Even though the connection string is
the same, and the temporary table name is the same, will the temporary
table created only be available to the specific connection that created
it? I'm concerned that if multiple users make a request to that stored
procedure, they may get someone else's data.
thanks,
Joe
The #tablename temp table exists only for the connection that calls the
proc. You are safe to call the same proc and the same table name as many
times as necessary without fear. Do not however use ##{tablename}. This will
cause a global temp table to be created and is referenceable by other
connections until the connection that created it is dropped.
--Rick Butler
"joseph.fanelli@.vba.va.gov" wrote:
> I have a stored procedure that creates a temporary table, populates it
> with one record, and then returns that record to an ASP. I've read
> that by prefixing the the tablename with a #, that only the connection
> that created it can access it.
> An IIS server will be making the connection and uses the same
> connection string for all users. Even though the connection string is
> the same, and the temporary table name is the same, will the temporary
> table created only be available to the specific connection that created
> it? I'm concerned that if multiple users make a request to that stored
> procedure, they may get someone else's data.
> thanks,
> Joe
>
|||Why not just select that one row and avoid creating a temp table? Improper
use of temporary objects in stored procedures can cause recompiles and
affect SQL performance. See
http://support.microsoft.com/default...b;en-us;243586 for details.
Adrian
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:BC656DA7-7BB2-4527-94D5-BCD53D702DE4@.microsoft.com...[vbcol=seagreen]
> The #tablename temp table exists only for the connection that calls the
> proc. You are safe to call the same proc and the same table name as many
> times as necessary without fear. Do not however use ##{tablename}. This
> will
> cause a global temp table to be created and is referenceable by other
> connections until the connection that created it is dropped.
> --Rick Butler
> "joseph.fanelli@.vba.va.gov" wrote:
Showing posts with label ive. Show all posts
Showing posts with label ive. Show all posts
Friday, March 23, 2012
question on temporary tables
I have a stored procedure that creates a temporary table, populates it
with one record, and then returns that record to an ASP. I've read
that by prefixing the the tablename with a #, that only the connection
that created it can access it.
An IIS server will be making the connection and uses the same
connection string for all users. Even though the connection string is
the same, and the temporary table name is the same, will the temporary
table created only be available to the specific connection that created
it? I'm concerned that if multiple users make a request to that stored
procedure, they may get someone else's data.
thanks,
Joe
Connections are always separate from each other, regardless of whether the
client connects using the same login information or not. Your temporary
tables will not collide or be visible to other connections.
Geoff N. Hiten
Microsoft SQL Server MVP
<joseph.fanelli@.vba.va.gov> wrote in message
news:1113844860.949837.190280@.z14g2000cwz.googlegr oups.com...
>I have a stored procedure that creates a temporary table, populates it
> with one record, and then returns that record to an ASP. I've read
> that by prefixing the the tablename with a #, that only the connection
> that created it can access it.
> An IIS server will be making the connection and uses the same
> connection string for all users. Even though the connection string is
> the same, and the temporary table name is the same, will the temporary
> table created only be available to the specific connection that created
> it? I'm concerned that if multiple users make a request to that stored
> procedure, they may get someone else's data.
> thanks,
> Joe
>
sql
with one record, and then returns that record to an ASP. I've read
that by prefixing the the tablename with a #, that only the connection
that created it can access it.
An IIS server will be making the connection and uses the same
connection string for all users. Even though the connection string is
the same, and the temporary table name is the same, will the temporary
table created only be available to the specific connection that created
it? I'm concerned that if multiple users make a request to that stored
procedure, they may get someone else's data.
thanks,
Joe
Connections are always separate from each other, regardless of whether the
client connects using the same login information or not. Your temporary
tables will not collide or be visible to other connections.
Geoff N. Hiten
Microsoft SQL Server MVP
<joseph.fanelli@.vba.va.gov> wrote in message
news:1113844860.949837.190280@.z14g2000cwz.googlegr oups.com...
>I have a stored procedure that creates a temporary table, populates it
> with one record, and then returns that record to an ASP. I've read
> that by prefixing the the tablename with a #, that only the connection
> that created it can access it.
> An IIS server will be making the connection and uses the same
> connection string for all users. Even though the connection string is
> the same, and the temporary table name is the same, will the temporary
> table created only be available to the specific connection that created
> it? I'm concerned that if multiple users make a request to that stored
> procedure, they may get someone else's data.
> thanks,
> Joe
>
sql
Wednesday, March 21, 2012
Question on SQL 2k transactional replication
I have a CRM package that uses SQL Server 2k as the back end. I've got
this same software package being used by two different offices (two
separate implementations of the package, the database structure is
identical). I need to get both offices up an running on the same set of
data.
I plan to merge the two databases into one, and mirror this database in
the two offices (over a dedicated T1) and use transactional replication
(using the publisher/updating subscriber model - since both offices will
be updating/inserting) to keep these databases synced. The rationale for
the two databases is simply that everything will occur on the database
that is local to the respective office, so there will be little to no
performance price. In this scenario, if a transaction doesn't go through
on both databases it is either dropped or queued, right?
A G,
You might want to post this in the .replication group and reside guru Hilary Cotter will give some sage advice.
Before you do that, check that the vendor will support SQL Server replication running underneath the application? You may also wish to consider using Merge replication as that has a conflict resolver built in.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
this same software package being used by two different offices (two
separate implementations of the package, the database structure is
identical). I need to get both offices up an running on the same set of
data.
I plan to merge the two databases into one, and mirror this database in
the two offices (over a dedicated T1) and use transactional replication
(using the publisher/updating subscriber model - since both offices will
be updating/inserting) to keep these databases synced. The rationale for
the two databases is simply that everything will occur on the database
that is local to the respective office, so there will be little to no
performance price. In this scenario, if a transaction doesn't go through
on both databases it is either dropped or queued, right?
A G,
You might want to post this in the .replication group and reside guru Hilary Cotter will give some sage advice.
Before you do that, check that the vendor will support SQL Server replication running underneath the application? You may also wish to consider using Merge replication as that has a conflict resolver built in.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Monday, March 12, 2012
Question on Plan Cache
I've got something awry in one of our servers. I'm getting SP:CacheMiss
like crazy in Profiler. The same procs repeatedly. The strange thing is
that these procs are in sys.syscacheobjects and the usagecount is growing
regularly so the cached plans are being used....why would I still be
getting CacheMiss? Some of these procs are only called from one specific
webservice the same way every time. The usage counts on some of these are
over 20k so the plan doesn't seem to be clearing it just doesn't seem to be
getting used every time...
Is it because the are being executed as "exec procname parm1, parm2,..."
from the .NET application instead of as a paramterized query? We are trying
to convert everything to parameterized as quickly as we can. But if this is
the cause, why is there a plan that *IS* getting used?
I'm really a bit stuck here and seeing a high number of SQL:Compiles that I
don't believe should be happening.
Thanks in advance!!
Possible reason:
EXEC procname
Above is a batch submitted to SQL Server. This is parsed and a plan is generated, but since it is a
dirt cheap plan it isn't cached. Note, I'm not talking about the proc plan, I'm referring to the
text "EXEC procname". Since it isn't cached, this happens every time you call the proc this way. I'm
tempted to test the difference between execution text and RPC, but since you are about to do it,
just let us know. I wouldn't be surprised if cache handling is different with RPC calls...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
> I've got something awry in one of our servers. I'm getting SP:CacheMiss like crazy in Profiler.
> The same procs repeatedly. The strange thing is that these procs are in sys.syscacheobjects and
> the usagecount is growing regularly so the cached plans are being used....why would I still be
> getting CacheMiss? Some of these procs are only called from one specific webservice the same way
> every time. The usage counts on some of these are over 20k so the plan doesn't seem to be
> clearing it just doesn't seem to be getting used every time...
> Is it because the are being executed as "exec procname parm1, parm2,..." from the .NET
> application instead of as a paramterized query? We are trying to convert everything to
> parameterized as quickly as we can. But if this is the cause, why is there a plan that *IS*
> getting used?
> I'm really a bit stuck here and seeing a high number of SQL:Compiles that I don't believe should
> be happening.
> Thanks in advance!!
>
|||Oh shoot...that is a simple explanation and makes total sense...
Thanks...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:704FC8B8-C95F-489A-A467-ED71F4223BD3@.microsoft.com...
> Possible reason:
> EXEC procname
> Above is a batch submitted to SQL Server. This is parsed and a plan is
> generated, but since it is a dirt cheap plan it isn't cached. Note, I'm
> not talking about the proc plan, I'm referring to the text "EXEC
> procname". Since it isn't cached, this happens every time you call the
> proc this way. I'm tempted to test the difference between execution text
> and RPC, but since you are about to do it, just let us know. I wouldn't be
> surprised if cache handling is different with RPC calls...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>
like crazy in Profiler. The same procs repeatedly. The strange thing is
that these procs are in sys.syscacheobjects and the usagecount is growing
regularly so the cached plans are being used....why would I still be
getting CacheMiss? Some of these procs are only called from one specific
webservice the same way every time. The usage counts on some of these are
over 20k so the plan doesn't seem to be clearing it just doesn't seem to be
getting used every time...
Is it because the are being executed as "exec procname parm1, parm2,..."
from the .NET application instead of as a paramterized query? We are trying
to convert everything to parameterized as quickly as we can. But if this is
the cause, why is there a plan that *IS* getting used?
I'm really a bit stuck here and seeing a high number of SQL:Compiles that I
don't believe should be happening.
Thanks in advance!!
Possible reason:
EXEC procname
Above is a batch submitted to SQL Server. This is parsed and a plan is generated, but since it is a
dirt cheap plan it isn't cached. Note, I'm not talking about the proc plan, I'm referring to the
text "EXEC procname". Since it isn't cached, this happens every time you call the proc this way. I'm
tempted to test the difference between execution text and RPC, but since you are about to do it,
just let us know. I wouldn't be surprised if cache handling is different with RPC calls...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
> I've got something awry in one of our servers. I'm getting SP:CacheMiss like crazy in Profiler.
> The same procs repeatedly. The strange thing is that these procs are in sys.syscacheobjects and
> the usagecount is growing regularly so the cached plans are being used....why would I still be
> getting CacheMiss? Some of these procs are only called from one specific webservice the same way
> every time. The usage counts on some of these are over 20k so the plan doesn't seem to be
> clearing it just doesn't seem to be getting used every time...
> Is it because the are being executed as "exec procname parm1, parm2,..." from the .NET
> application instead of as a paramterized query? We are trying to convert everything to
> parameterized as quickly as we can. But if this is the cause, why is there a plan that *IS*
> getting used?
> I'm really a bit stuck here and seeing a high number of SQL:Compiles that I don't believe should
> be happening.
> Thanks in advance!!
>
|||Oh shoot...that is a simple explanation and makes total sense...
Thanks...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:704FC8B8-C95F-489A-A467-ED71F4223BD3@.microsoft.com...
> Possible reason:
> EXEC procname
> Above is a batch submitted to SQL Server. This is parsed and a plan is
> generated, but since it is a dirt cheap plan it isn't cached. Note, I'm
> not talking about the proc plan, I'm referring to the text "EXEC
> procname". Since it isn't cached, this happens every time you call the
> proc this way. I'm tempted to test the difference between execution text
> and RPC, but since you are about to do it, just let us know. I wouldn't be
> surprised if cache handling is different with RPC calls...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>
Saturday, February 25, 2012
question on changing recovery model on DB
Hi,
I've got several production DBs that I want to change the recovery model
on from simple to full. Are there any caveats I need to be aware of in
making this change? Or is it just a matter of selecting the 'Full' recovery
model under the 'Options' tab of the DBs Properties and hitting the 'OK'
button? Thanks.
Make sure you set up log backup jobs for the databases to prevent the log
file sizes getting out of hand.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"mb" <mb@.discussions.microsoft.com> wrote in message
news:2547FAF9-8BB6-407C-AFCA-E5D2F9082EB0@.microsoft.com...
> Hi,
> I've got several production DBs that I want to change the recovery model
> on from simple to full. Are there any caveats I need to be aware of in
> making this change? Or is it just a matter of selecting the 'Full'
> recovery
> model under the 'Options' tab of the DBs Properties and hitting the 'OK'
> button? Thanks.
|||mb wrote:
> Hi,
> I've got several production DBs that I want to change the recovery
> model on from simple to full. Are there any caveats I need to be
> aware of in making this change? Or is it just a matter of selecting
> the 'Full' recovery model under the 'Options' tab of the DBs
> Properties and hitting the 'OK' button? Thanks.
You must immediately perform a Full Backup or your t-logs will still get
truncated every checkpoint.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Hi,
Once you change your database from SIMPLE to FULL recovery model, you need
to recreate your backup chain by doing:-
1. A Full databaase backup
2. Schedule a transaction log backup frequently
In FULL recovery you need to perform the log backup, this will be helpful to
recover the database point-in-time as
well as help you to keep your LDF file size in control.
Have a look into Recovery model topic in books online for more detail.
Thanks
Hari
SQL SERVER MVP
"mb" <mb@.discussions.microsoft.com> wrote in message
news:2547FAF9-8BB6-407C-AFCA-E5D2F9082EB0@.microsoft.com...
> Hi,
> I've got several production DBs that I want to change the recovery model
> on from simple to full. Are there any caveats I need to be aware of in
> making this change? Or is it just a matter of selecting the 'Full'
> recovery
> model under the 'Options' tab of the DBs Properties and hitting the 'OK'
> button? Thanks.
I've got several production DBs that I want to change the recovery model
on from simple to full. Are there any caveats I need to be aware of in
making this change? Or is it just a matter of selecting the 'Full' recovery
model under the 'Options' tab of the DBs Properties and hitting the 'OK'
button? Thanks.
Make sure you set up log backup jobs for the databases to prevent the log
file sizes getting out of hand.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"mb" <mb@.discussions.microsoft.com> wrote in message
news:2547FAF9-8BB6-407C-AFCA-E5D2F9082EB0@.microsoft.com...
> Hi,
> I've got several production DBs that I want to change the recovery model
> on from simple to full. Are there any caveats I need to be aware of in
> making this change? Or is it just a matter of selecting the 'Full'
> recovery
> model under the 'Options' tab of the DBs Properties and hitting the 'OK'
> button? Thanks.
|||mb wrote:
> Hi,
> I've got several production DBs that I want to change the recovery
> model on from simple to full. Are there any caveats I need to be
> aware of in making this change? Or is it just a matter of selecting
> the 'Full' recovery model under the 'Options' tab of the DBs
> Properties and hitting the 'OK' button? Thanks.
You must immediately perform a Full Backup or your t-logs will still get
truncated every checkpoint.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Hi,
Once you change your database from SIMPLE to FULL recovery model, you need
to recreate your backup chain by doing:-
1. A Full databaase backup
2. Schedule a transaction log backup frequently
In FULL recovery you need to perform the log backup, this will be helpful to
recover the database point-in-time as
well as help you to keep your LDF file size in control.
Have a look into Recovery model topic in books online for more detail.
Thanks
Hari
SQL SERVER MVP
"mb" <mb@.discussions.microsoft.com> wrote in message
news:2547FAF9-8BB6-407C-AFCA-E5D2F9082EB0@.microsoft.com...
> Hi,
> I've got several production DBs that I want to change the recovery model
> on from simple to full. Are there any caveats I need to be aware of in
> making this change? Or is it just a matter of selecting the 'Full'
> recovery
> model under the 'Options' tab of the DBs Properties and hitting the 'OK'
> button? Thanks.
Monday, February 20, 2012
Question on CAL
I would like to know that I've upgraded SQL 7.0 server to 2000. some of CAL
licenses are still 7.0, Does it need to upgrading to 2000 as well? Where
could I find information about these at microsoft?
Thanks
Kcid
http://www.microsoft.com/sql/howtobuy/default.mspx
Ekrem ?nsoy
"kcid169" <kcid169@.discussions.microsoft.com> wrote in message
news:0AFE35B2-1502-466D-BBA7-C46B59276134@.microsoft.com...
>I would like to know that I've upgraded SQL 7.0 server to 2000. some of CAL
> licenses are still 7.0, Does it need to upgrading to 2000 as well? Where
> could I find information about these at microsoft?
> Thanks
> Kcid
licenses are still 7.0, Does it need to upgrading to 2000 as well? Where
could I find information about these at microsoft?
Thanks
Kcid
http://www.microsoft.com/sql/howtobuy/default.mspx
Ekrem ?nsoy
"kcid169" <kcid169@.discussions.microsoft.com> wrote in message
news:0AFE35B2-1502-466D-BBA7-C46B59276134@.microsoft.com...
>I would like to know that I've upgraded SQL 7.0 server to 2000. some of CAL
> licenses are still 7.0, Does it need to upgrading to 2000 as well? Where
> could I find information about these at microsoft?
> Thanks
> Kcid
Subscribe to:
Posts (Atom)