SQLAgentMail: asp

Showing posts with label asp. Show all posts

Wednesday, March 28, 2012

Question regarding Stored Procedures, Views and ASP

Please could someone explain to me the differences between a stored
procedure and a view.

The reason for this question is I have two almost identical ASP pages.
Both get the same results but one uses a stored procedure and one uses
a view. If the query returns no results the 'view page' generates
errors and therefore I have to check for BOF and EOF, whereas the
'stored procedure page' does not generate errors and instead would
appear to return a recordset with 0 entries.

Thanks in advance

Neil.Neil (neil.lloyd@.fdtsolutions.com) writes:
> Please could someone explain to me the differences between a stored
> procedure and a view.
> The reason for this question is I have two almost identical ASP pages.
> Both get the same results but one uses a stored procedure and one uses
> a view. If the query returns no results the 'view page' generates
> errors and therefore I have to check for BOF and EOF, whereas the
> 'stored procedure page' does not generate errors and instead would
> appear to return a recordset with 0 entries.

It might more have to do with who you call them. Could you post the
code, including how you set up cursor type and all? The SP code can
also be useful.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Monday, March 26, 2012

Question re: security issue detailed in KB887459

Is the Report Manager vulnerable to the issue described here:
http://support.microsoft.com/?kbid=887459
I'm not very experienced with ASP.NET - can I assume that any additional
safeguards for the "canonicalization" issues would have to come from
Microsoft in the case of Report Manager as it is a compiled app? TIA.
-BAHI Brian:
There is now an MSI file that will install an HttpModule to protect
all ASP.NET applications.
See: http://www.microsoft.com/security/incident/aspnet.mspx
--
Scott
http://www.OdeToCode.com/
On Thu, 07 Oct 2004 17:00:46 -0700, Brian Almond
<pythonista@.sbcglobal.net> wrote:
>Is the Report Manager vulnerable to the issue described here:
>http://support.microsoft.com/?kbid=887459
>I'm not very experienced with ASP.NET - can I assume that any additional
>safeguards for the "canonicalization" issues would have to come from
>Microsoft in the case of Report Manager as it is a compiled app? TIA.
>-BA|||Scott Allen wrote:
> There is now an MSI file that will install an HttpModule to protect
> all ASP.NET applications.
> See: http://www.microsoft.com/security/incident/aspnet.mspx
Thanks for posting the link Scott. Unfortunately it looks like
something that MSI does has confused Report Manager on my our
development RS box so that now I'm getting a security exception when
browsing to it. Playing with it now to see if I just need to make
simple config. changes or if it's something more involved causing me
trouble.
-BA|||Interesting, I'll give it a try tommorow at home and see what happens.
--
Scott
http://www.OdeToCode.com/
On Fri, 08 Oct 2004 10:02:30 -0700, Brian Almond
<pythonista@.sbcglobal.net> wrote:
>Scott Allen wrote:
>> There is now an MSI file that will install an HttpModule to protect
>> all ASP.NET applications.
>> See: http://www.microsoft.com/security/incident/aspnet.mspx
>Thanks for posting the link Scott. Unfortunately it looks like
>something that MSI does has confused Report Manager on my our
>development RS box so that now I'm getting a security exception when
>browsing to it. Playing with it now to see if I just need to make
>simple config. changes or if it's something more involved causing me
>trouble.
>-BA|||Brian,
I had the exact same problem when I installed - let me know if you get a
resolution on this.
Thanks,
Dan
"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
> Scott Allen wrote:
> > There is now an MSI file that will install an HttpModule to protect
> > all ASP.NET applications.
> >
> > See: http://www.microsoft.com/security/incident/aspnet.mspx
> Thanks for posting the link Scott. Unfortunately it looks like
> something that MSI does has confused Report Manager on my our
> development RS box so that now I'm getting a security exception when
> browsing to it. Playing with it now to see if I just need to make
> simple config. changes or if it's something more involved causing me
> trouble.
> -BA|||Yes, it's a problem, unfortunately.
I have everything working again after adding a new CodeGroup to both
policy config files, see:
http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
Let me know if this gets you up and running again. If anyone from MS
has an official recommendation I'll update the blog.
--
Scott
http://www.OdeToCode.com/
On Fri, 8 Oct 2004 19:57:15 -0400, "Dan Plaskon"
<dplaskon@.sympatico.ca> wrote:
>Brian,
>I had the exact same problem when I installed - let me know if you get a
>resolution on this.
>Thanks,
>Dan
>"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
>news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
>> Scott Allen wrote:
>> > There is now an MSI file that will install an HttpModule to protect
>> > all ASP.NET applications.
>> >
>> > See: http://www.microsoft.com/security/incident/aspnet.mspx
>> Thanks for posting the link Scott. Unfortunately it looks like
>> something that MSI does has confused Report Manager on my our
>> development RS box so that now I'm getting a security exception when
>> browsing to it. Playing with it now to see if I just need to make
>> simple config. changes or if it's something more involved causing me
>> trouble.
>> -BA
>|||That change doesn't work on my server. I get a parse error on the
ValidatePathModule line of
machine.config... Very strange error as it only says "?" as error message.
/Per Salmi
"Scott Allen" <bitmask@.[nospam].fred.net> skrev i meddelandet
news:4viem0dkbh4apfl41f6btmtufl6pq8mio0@.4ax.com...
> Yes, it's a problem, unfortunately.
> I have everything working again after adding a new CodeGroup to both
> policy config files, see:
> http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
> Let me know if this gets you up and running again. If anyone from MS
> has an official recommendation I'll update the blog.
> --
> Scott
> http://www.OdeToCode.com/
> On Fri, 8 Oct 2004 19:57:15 -0400, "Dan Plaskon"
> <dplaskon@.sympatico.ca> wrote:
>>Brian,
>>I had the exact same problem when I installed - let me know if you get a
>>resolution on this.
>>Thanks,
>>Dan
>>"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
>>news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
>> Scott Allen wrote:
>> > There is now an MSI file that will install an HttpModule to protect
>> > all ASP.NET applications.
>> >
>> > See: http://www.microsoft.com/security/incident/aspnet.mspx
>> Thanks for posting the link Scott. Unfortunately it looks like
>> something that MSI does has confused Report Manager on my our
>> development RS box so that now I'm getting a security exception when
>> browsing to it. Playing with it now to see if I just need to make
>> simple config. changes or if it's something more involved causing me
>> trouble.
>> -BA
>|||Tried the same thing on another server and now the parse error on
machine.config says:
Description: An error occurred during the processing of a configuration file
required to service this request. Please review the specific error details
below and modify your configuration file appropriately.
Parser Error Message: Assembly microsoft.web.validatepathmodule.dll security
permission grant set is incompatible between appdomains.
Source Error:
Line 320: <add name="FileAuthorization"
type="System.Web.Security.FileAuthorizationModule"/>
Line 321: <add name="ErrorHandlerModule"
type="System.Web.Mobile.ErrorHandlerModule, System.Web.Mobile,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
Line 322: <add name="ValidatePathModule"
type="Microsoft.Web.ValidatePathModule, Microsoft.Web.ValidatePathModule,
Version=1.0.0.0, Culture=neutral,
PublicKeyToken=eba19824f86fdadd"/></httpModules>
Line 323: <!--
Line 324: processModel Attributes:
/Per Salmi
"Scott Allen" <bitmask@.[nospam].fred.net> skrev i meddelandet
news:4viem0dkbh4apfl41f6btmtufl6pq8mio0@.4ax.com...
> Yes, it's a problem, unfortunately.
> I have everything working again after adding a new CodeGroup to both
> policy config files, see:
> http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
> Let me know if this gets you up and running again. If anyone from MS
> has an official recommendation I'll update the blog.
> --
> Scott
> http://www.OdeToCode.com/
> On Fri, 8 Oct 2004 19:57:15 -0400, "Dan Plaskon"
> <dplaskon@.sympatico.ca> wrote:
>>Brian,
>>I had the exact same problem when I installed - let me know if you get a
>>resolution on this.
>>Thanks,
>>Dan
>>"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
>>news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
>> Scott Allen wrote:
>> > There is now an MSI file that will install an HttpModule to protect
>> > all ASP.NET applications.
>> >
>> > See: http://www.microsoft.com/security/incident/aspnet.mspx
>> Thanks for posting the link Scott. Unfortunately it looks like
>> something that MSI does has confused Report Manager on my our
>> development RS box so that now I'm getting a security exception when
>> browsing to it. Playing with it now to see if I just need to make
>> simple config. changes or if it's something more involved causing me
>> trouble.
>> -BA
>|||If you restart the web server after making the configuration changes
it should all be working then.
--
Scott
http://www.OdeToCode.com/
On Mon, 11 Oct 2004 12:11:14 +0200, "Per Salmi"
<per.salmi@.nospam.nospam> wrote:
>Tried the same thing on another server and now the parse error on
>machine.config says:
>Description: An error occurred during the processing of a configuration file
>required to service this request. Please review the specific error details
>below and modify your configuration file appropriately.
>Parser Error Message: Assembly microsoft.web.validatepathmodule.dll security
>permission grant set is incompatible between appdomains.
>Source Error:
>Line 320: <add name="FileAuthorization"
>type="System.Web.Security.FileAuthorizationModule"/>
>Line 321: <add name="ErrorHandlerModule"
>type="System.Web.Mobile.ErrorHandlerModule, System.Web.Mobile,
>Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
>Line 322: <add name="ValidatePathModule"
>type="Microsoft.Web.ValidatePathModule, Microsoft.Web.ValidatePathModule,
>Version=1.0.0.0, Culture=neutral,
>PublicKeyToken=eba19824f86fdadd"/></httpModules>
>Line 323: <!--
>Line 324: processModel Attributes:
>
>/Per Salmi
>
>"Scott Allen" <bitmask@.[nospam].fred.net> skrev i meddelandet
>news:4viem0dkbh4apfl41f6btmtufl6pq8mio0@.4ax.com...
>> Yes, it's a problem, unfortunately.
>> I have everything working again after adding a new CodeGroup to both
>> policy config files, see:
>> http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
>> Let me know if this gets you up and running again. If anyone from MS
>> has an official recommendation I'll update the blog.
>> --
>> Scott
>> http://www.OdeToCode.com/
>> On Fri, 8 Oct 2004 19:57:15 -0400, "Dan Plaskon"
>> <dplaskon@.sympatico.ca> wrote:
>>Brian,
>>I had the exact same problem when I installed - let me know if you get a
>>resolution on this.
>>Thanks,
>>Dan
>>"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
>>news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
>> Scott Allen wrote:
>> > There is now an MSI file that will install an HttpModule to protect
>> > all ASP.NET applications.
>> >
>> > See: http://www.microsoft.com/security/incident/aspnet.mspx
>> Thanks for posting the link Scott. Unfortunately it looks like
>> something that MSI does has confused Report Manager on my our
>> development RS box so that now I'm getting a security exception when
>> browsing to it. Playing with it now to see if I just need to make
>> simple config. changes or if it's something more involved causing me
>> trouble.
>> -BA
>>
>|||I called Microsoft support services, they were clueless. I'm blogging
about it at http://www.dogcaught.com/dpack/index.php?p=52
Aaron
http://www.hockley.org
"Per Salmi" <per.salmi@.nospam.nospam> wrote in message news:<ebdglq3rEHA.192@.tk2msftngp13.phx.gbl>...
> Tried the same thing on another server and now the parse error on
> machine.config says:
> Description: An error occurred during the processing of a configuration file
> required to service this request. Please review the specific error details
> below and modify your configuration file appropriately.
> Parser Error Message: Assembly microsoft.web.validatepathmodule.dll security
> permission grant set is incompatible between appdomains.
> Source Error:
> Line 320: <add name="FileAuthorization"
> type="System.Web.Security.FileAuthorizationModule"/>
> Line 321: <add name="ErrorHandlerModule"
> type="System.Web.Mobile.ErrorHandlerModule, System.Web.Mobile,
> Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
> Line 322: <add name="ValidatePathModule"
> type="Microsoft.Web.ValidatePathModule, Microsoft.Web.ValidatePathModule,
> Version=1.0.0.0, Culture=neutral,
> PublicKeyToken=eba19824f86fdadd"/></httpModules>
> Line 323: <!--
> Line 324: processModel Attributes:
>
> /Per Salmi
>|||Scott Allen wrote:
> I have everything working again after adding a new CodeGroup to both
> policy config files, see:
> http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
> Let me know if this gets you up and running again. If anyone from MS
> has an official recommendation I'll update the blog.
I have to admit that I'm left wondering why Microsoft released the patch
without getting a green light on their web apps. Coincidentally I
have tried your fix for their patch, but am now getting an error message
complaining about a request for StrongNameIdentityPermission. I guess
I'm going to have to bear down and study materials on configuration
ASP.NET applications if I want to get any semblance of a grip on this.
-BA|||Brian Almond wrote:
> configuration ASP.NET applications
_Configuring_ ASP.NET applications. Bah! I should really review my
messages prior to posting ;)|||Just an FYI for everyone. This doesn't help solve the issue with a system
being messed up but I don't think that RS is vulnerable to this exploit. I
have had some discussions with MS people and the bottom line is that Report
Server stores all of its secure content in the database. Report Manager is
the portal to Report Server and although it could be affected that there
would not be an exploit because it does not have secure content.
Further explanation given to me is that this exploit is seen when using form
authentication and you use the result of authentication to grant
permissions to files inside your application vroot which Report Manager does
not do.
If you think about how Report Server is designed as a service and if you
look on the server you will not see any rdl files. SQL Server db is used to
store all this information. So it is not like Report Manager is just opening
up report files.
I hesitated to jump in but I hate to see people messing around and wasting
time and energy on a patch that isn't needed. Please note that I am not the
official MS voice. At a minimum I would delay working on it. Hopefully we
can get an official MS person to bless what I said above.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
news:%23oJu899rEHA.2684@.TK2MSFTNGP12.phx.gbl...
> Scott Allen wrote:
> > I have everything working again after adding a new CodeGroup to both
> > policy config files, see:
> > http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
> >
> > Let me know if this gets you up and running again. If anyone from MS
> > has an official recommendation I'll update the blog.
> I have to admit that I'm left wondering why Microsoft released the patch
> without getting a green light on their web apps. Coincidentally I
> have tried your fix for their patch, but am now getting an error message
> complaining about a request for StrongNameIdentityPermission. I guess
> I'm going to have to bear down and study materials on configuration
> ASP.NET applications if I want to get any semblance of a grip on this.
> -BA|||Bruce,
Thanks for commenting on this. I hope we do get an official word at
some point on this issue. It would be nice not to have to worry about
this issue in the future.
Unfortunately, at this point I have a 'dead' report server. Luckily
it's my test server, but I would like to get it back up without
reinstalling RS if possible. (Uninstalling the MS patch doesn't seem to
revert all of its changes.) I didn't backup all of the configuration
files before applying the Microsoft patch, so I've given myself a more
difficult restore situation than I could have had otherwise.
It is certainly time better spent elsewhere.
-BA|||Hi Bruce:
I'm inclined to agree with you after some experimenting today.
In the case where someone *has* to install the module on a machine
with SSRS (because there are other ASP.NET applications present), the
fix is to put the following entry in the web.config file (both
ReportManager and ReportServer config files) in the system.web
section:
<httpModules>
<remove name="ValidatePathModule"/>
</httpModules>
This disables the module for just the SSRS applications.
There sure has been some confusion. I've seen a couple reputable
sources say the vulnerability exists for Windows authentication in
addition to forms authentication. I've also heard that Windows 2003 is
affected, even though I haven't been able to exploit the vulnerability
on any of my 2003 machines.
--
Scott
http://www.OdeToCode.com/
On Mon, 11 Oct 2004 17:33:19 -0500, "Bruce L-C [MVP]"
<bruce_lcNOSPAM@.hotmail.com> wrote:
>Just an FYI for everyone. This doesn't help solve the issue with a system
>being messed up but I don't think that RS is vulnerable to this exploit. I
>have had some discussions with MS people and the bottom line is that Report
>Server stores all of its secure content in the database. Report Manager is
>the portal to Report Server and although it could be affected that there
>would not be an exploit because it does not have secure content.
>Further explanation given to me is that this exploit is seen when using form
>authentication and you use the result of authentication to grant
>permissions to files inside your application vroot which Report Manager does
>not do.
>If you think about how Report Server is designed as a service and if you
>look on the server you will not see any rdl files. SQL Server db is used to
>store all this information. So it is not like Report Manager is just opening
>up report files.
>I hesitated to jump in but I hate to see people messing around and wasting
>time and energy on a patch that isn't needed. Please note that I am not the
>official MS voice. At a minimum I would delay working on it. Hopefully we
>can get an official MS person to bless what I said above.|||We are working on a KB article that will have the offical workaround for
this. We hope to have it posted tomorrow.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott Allen" <bitmask@.[nospam].fred.net> wrote in message
news:kobmm0hepkp6gh7voej1d882b56mpbt1en@.4ax.com...
> Hi Bruce:
> I'm inclined to agree with you after some experimenting today.
> In the case where someone *has* to install the module on a machine
> with SSRS (because there are other ASP.NET applications present), the
> fix is to put the following entry in the web.config file (both
> ReportManager and ReportServer config files) in the system.web
> section:
> <httpModules>
> <remove name="ValidatePathModule"/>
> </httpModules>
> This disables the module for just the SSRS applications.
> There sure has been some confusion. I've seen a couple reputable
> sources say the vulnerability exists for Windows authentication in
> addition to forms authentication. I've also heard that Windows 2003 is
> affected, even though I haven't been able to exploit the vulnerability
> on any of my 2003 machines.
> --
> Scott
> http://www.OdeToCode.com/
> On Mon, 11 Oct 2004 17:33:19 -0500, "Bruce L-C [MVP]"
> <bruce_lcNOSPAM@.hotmail.com> wrote:
>>Just an FYI for everyone. This doesn't help solve the issue with a system
>>being messed up but I don't think that RS is vulnerable to this exploit. I
>>have had some discussions with MS people and the bottom line is that
>>Report
>>Server stores all of its secure content in the database. Report Manager is
>>the portal to Report Server and although it could be affected that there
>>would not be an exploit because it does not have secure content.
>>Further explanation given to me is that this exploit is seen when using
>>form
>>authentication and you use the result of authentication to grant
>>permissions to files inside your application vroot which Report Manager
>>does
>>not do.
>>If you think about how Report Server is designed as a service and if you
>>look on the server you will not see any rdl files. SQL Server db is used
>>to
>>store all this information. So it is not like Report Manager is just
>>opening
>>up report files.
>>I hesitated to jump in but I hate to see people messing around and wasting
>>time and energy on a patch that isn't needed. Please note that I am not
>>the
>>official MS voice. At a minimum I would delay working on it. Hopefully we
>>can get an official MS person to bless what I said above.
>|||You are right in this that the RS might not be affected by the vulnerability
but as there might be lots of other asp.net applications running on the same
server that are vulnerable it would feel better to have the patch installed,
and still have a working report server application.
Best regards,
Per Salmi
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> skrev i meddelandet
news:ODmNQJ%23rEHA.2096@.TK2MSFTNGP11.phx.gbl...
> I hesitated to jump in but I hate to see people messing around and wasting
> time and energy on a patch that isn't needed. Please note that I am not
> the
> official MS voice. At a minimum I would delay working on it. Hopefully we
> can get an official MS person to bless what I said above.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services|||There is now a KB article that describes the workaround at
http://support.microsoft.com/?kbid=887787.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Per Salmi" <per.salmi@.nospam.nospam> wrote in message
news:uBzQx2BsEHA.3748@.TK2MSFTNGP09.phx.gbl...
> You are right in this that the RS might not be affected by the
> vulnerability but as there might be lots of other asp.net applications
> running on the same server that are vulnerable it would feel better to
> have the patch installed, and still have a working report server
> application.
> Best regards,
> Per Salmi
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> skrev i meddelandet
> news:ODmNQJ%23rEHA.2096@.TK2MSFTNGP11.phx.gbl...
>> I hesitated to jump in but I hate to see people messing around and
>> wasting
>> time and energy on a patch that isn't needed. Please note that I am not
>> the
>> official MS voice. At a minimum I would delay working on it. Hopefully we
>> can get an official MS person to bless what I said above.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>|||Thanks, Brian.
--
Scott
http://www.OdeToCode.com/
On Tue, 12 Oct 2004 16:09:32 -0700, "Brian Welcker [MSFT]"
<bwelcker@.online.microsoft.com> wrote:
>There is now a KB article that describes the workaround at
>http://support.microsoft.com/?kbid=887787.|||Thanks! That worked perfectly on both of our servers.
/Per Salmi
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> skrev i meddelandet
news:eXlZICLsEHA.2560@.tk2msftngp13.phx.gbl...
> There is now a KB article that describes the workaround at
> http://support.microsoft.com/?kbid=887787.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services

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,
JoeIf you are populating the table with just one row, then use a table variable
instead.
AMB
"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
>|||If you are only populating with one row then chances are you can do this
without a temp table. But to answer your question no others can not see
this. But you might want to do as Alejandro says and use a table variable
instead.
Andrew J. Kelly SQL MVP
<joseph.fanelli@.vba.va.gov> wrote in message
news:1113844980.325249.265010@.l41g2000cwc.googlegroups.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
>|||Why even use a temporary table?
Declare variables, populate them, then return a recorset:
select @.variable1 as ..., @.variable2 as ... @.variable3 as ... etc.
<joseph.fanelli@.vba.va.gov> wrote in message
news:1113844980.325249.265010@.l41g2000cwc.googlegroups.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
>|||Andrew,
I did not think about it, but yes, better to use an output parameter. What
about a connection taken from the pool, if the sp does not drop the temporar
y
table explicitly, and uses this code to create it:
if object_id('tempdb..#temptbl') is null
create table #temptbl ...
...
then next time the sp is executed using the same connection, a row inserted
by previous execution can be selected, correct?
AMB
"Andrew J. Kelly" wrote:

> If you are only populating with one row then chances are you can do this
> without a temp table. But to answer your question no others can not see
> this. But you might want to do as Alejandro says and use a table variable
> instead.
> --
> Andrew J. Kelly SQL MVP
>
> <joseph.fanelli@.vba.va.gov> wrote in message
> news:1113844980.325249.265010@.l41g2000cwc.googlegroups.com...
>
>|||If they are using connection pooling and follow the proper procedures a
sp_resetconnection is called that will clean up any such things and put the
connection settings back to the proper settings. Normally a temp table
created in a sp will go out of scope when the sp is completed as well. It
is only if they created the temp table outside of a sp that it will hang
around.
Andrew J. Kelly SQL MVP
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:8C7B0F8B-12B7-49F0-818B-F39C2415C8FE@.microsoft.com...
> Andrew,
> I did not think about it, but yes, better to use an output parameter. What
> about a connection taken from the pool, if the sp does not drop the
> temporary
> table explicitly, and uses this code to create it:
> if object_id('tempdb..#temptbl') is null
> create table #temptbl ...
> ...
> then next time the sp is executed using the same connection, a row
> inserted
> by previous execution can be selected, correct?
>
> AMB
> "Andrew J. Kelly" wrote:
>|||Andrew,
You are right, the temporary table goes out of scope when the sp has
finished. I wonder what kind of cleaning is done by sp_resetconnection, I ca
n
not find anything about it in the BOL.
Thanks,
AMB
"Andrew J. Kelly" wrote:

> If they are using connection pooling and follow the proper procedures a
> sp_resetconnection is called that will clean up any such things and put th
e
> connection settings back to the proper settings. Normally a temp table
> created in a sp will go out of scope when the sp is completed as well. It
> is only if they created the temp table outside of a sp that it will hang
> around.
> --
> Andrew J. Kelly SQL MVP
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:8C7B0F8B-12B7-49F0-818B-F39C2415C8FE@.microsoft.com...
>
>

question on temporary tables

Tuesday, March 20, 2012

Question on security

Hi,
I have a feeling what I want to do is not possible. I have a reportserver
(everything works fine) and I have an asp.net app which allows users to run
some reports. I dont use the reportviewer control, I just opent the report in
a new IE window by using url access e.g.
http://reportserver/reports/TimeReport&rs:command=render.
This works fine but is there a way of preventing users just typing in
http://reportserver/reports into the url and therefore accessing all the
reports. I know I could use windows authentication for the reports but I cant
do that for various reasons.
Is there any setting I can set on the http://reportserver/reports site to
only allow certain users access it but yet allow url access to reports as
normal using e.g. http://reportserver/reports/TimeReport&rs:command=render.
Its a bit of a contradiction really, I want the reportsite to be secure but
yet be able to run url access reports from it.
Thanks
NYou could deny access to the root from the report manager. Just allow
access to the subfolders/individual reports. Of course, they could then
directly access the subfolder...
Mike G.
"NH" <NH@.discussions.microsoft.com> wrote in message
news:846A037D-582D-498E-A6A1-5BF59AA772AF@.microsoft.com...
> Hi,
> I have a feeling what I want to do is not possible. I have a reportserver
> (everything works fine) and I have an asp.net app which allows users to
> run
> some reports. I dont use the reportviewer control, I just opent the report
> in
> a new IE window by using url access e.g.
> http://reportserver/reports/TimeReport&rs:command=render.
> This works fine but is there a way of preventing users just typing in
> http://reportserver/reports into the url and therefore accessing all the
> reports. I know I could use windows authentication for the reports but I
> cant
> do that for various reasons.
> Is there any setting I can set on the http://reportserver/reports site to
> only allow certain users access it but yet allow url access to reports as
> normal using e.g.
> http://reportserver/reports/TimeReport&rs:command=render.
> Its a bit of a contradiction really, I want the reportsite to be secure
> but
> yet be able to run url access reports from it.
> Thanks
> N
>|||Thanks Mike G, I dont think there is a way to do this.
"Mike G." wrote:
> You could deny access to the root from the report manager. Just allow
> access to the subfolders/individual reports. Of course, they could then
> directly access the subfolder...
> Mike G.
>
> "NH" <NH@.discussions.microsoft.com> wrote in message
> news:846A037D-582D-498E-A6A1-5BF59AA772AF@.microsoft.com...
> > Hi,
> >
> > I have a feeling what I want to do is not possible. I have a reportserver
> > (everything works fine) and I have an asp.net app which allows users to
> > run
> > some reports. I dont use the reportviewer control, I just opent the report
> > in
> > a new IE window by using url access e.g.
> > http://reportserver/reports/TimeReport&rs:command=render.
> >
> > This works fine but is there a way of preventing users just typing in
> > http://reportserver/reports into the url and therefore accessing all the
> > reports. I know I could use windows authentication for the reports but I
> > cant
> > do that for various reasons.
> >
> > Is there any setting I can set on the http://reportserver/reports site to
> > only allow certain users access it but yet allow url access to reports as
> > normal using e.g.
> > http://reportserver/reports/TimeReport&rs:command=render.
> > Its a bit of a contradiction really, I want the reportsite to be secure
> > but
> > yet be able to run url access reports from it.
> >
> > Thanks
> > N
> >
> >
>
>

Question on restoring encrypted databases on different servers

I have run into problems trying to use backed-up encrypted databases. I have an ASP.Net/SqlServer 2005 website for which I have production, test, and development environments, each on its own box. I have set up encryption using Master Keys, Certificates, and Symmetric Keys using the following commands:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'

CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.

All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.

I am running into problems when I back up the development database and try to move it to the test environment. When I do the move, and then try to access the encrypted data, I get an error "An error occurred during decryption", even though they are the "same" database (I originally detached and copied the development database to the test box when I set up the test environment) with the same password.

I then try the following commands, which I used when I brought the development database over to the test box the first time:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

This caused the following error:

"An error occurred while decrypting certificate 'CERTIFICATE_NAME' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable."

So, how do you set up encrypted databases so that you can backup from one database server and restore on another? This problem will be very important when I set up a backup production database server.

From the error message and your description, I am guessing that somehow while moving the DB from one environment to another the metadata for either the master key (DBMK) or the certificate got somehow corrupted, or rather out of sync.

I am assuming that on the test environment the DB and an earlier copy of both the cert and DBMK (or only the DBMK) exist; based on that, this is my guess. On the dev environment you have DBMK_1 protecting cert_pvk_1, but when the DB is moved to the test environment, one of the following situations happened:

1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)

2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0

Can you please verify if my assumption is correct? If so, I can try to reproduce the scenario in my environment to verify. If my assumption is not correct we will need more information. What are the OS versions running in both environments? Is there any significant difference between both environments (i.e. OS language, any third party software that may affect the crypto-API calls, etc)?

Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Also, the commands that you need to execute when moving a database to another server are:

OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

The REGENERATE option should be used only if you want to have a new master key in the database.

Thanks
Laurentiu

|||

This solves my problem. Thank you for your help.

However, it has opened up a new line of thought for me. If you don't use the ALTER MASTER KEY command in the backup database, you have to open the master key every time you want to access the encrypted data. I think I would like that requirement in the original database(it always bothered me that if you can decrypt at will if you know the name of the symmetric key and the certificate).

So, how do you make the original database require the Open Master Key Statement?

|||

You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.

Thanks
Laurentiu

|||

You can use the following statement:

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

Just to verify it:

-- Should fail with error 15581:

-- Please create a master key in the database or open the master key in the session before performing this operation.

create certificate foo with subject = 'foo'

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@.ssw0rD>>'

-- Will succeed

create certificate foo with subject = 'foo'

CLOSE MASTER KEY

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

I am also trying to restore a database which has an encrypted column. I used methods from Larentiu's blog post - column encryption demo.

I execute this command and get:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'

Msg 15313, Level 16, State 1, Line 1

The key is not encrypted using the specified decryptor.

I try this on my original database and I get the same result - what have i done?! I'm guessing my password is wrong, but i do have the script I used to create it right in front of me, and written: and they match.

|||

Hi Sam,

I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?

thanks...

|||

I just answered a similar problem in the following thread of this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1

Hopefully this information will help, but if you still have any problems let us know.

-Raul Garcia

SDE/T

SQL Server Engine

Question on restoring encrypted databases on different servers

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'

CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.

All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.

I then try the following commands, which I used when I brought the development database over to the test box the first time:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

This caused the following error:

1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)

2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0

Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Also, the commands that you need to execute when moving a database to another server are:

OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

The REGENERATE option should be used only if you want to have a new master key in the database.

Thanks
Laurentiu

|||

This solves my problem. Thank you for your help.

So, how do you make the original database require the Open Master Key Statement?

|||

You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.

Thanks
Laurentiu

|||

You can use the following statement:

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

Just to verify it:

-- Should fail with error 15581:

-- Please create a master key in the database or open the master key in the session before performing this operation.

create certificate foo with subject = 'foo'

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@.ssw0rD>>'

-- Will succeed

create certificate foo with subject = 'foo'

CLOSE MASTER KEY

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

I am also trying to restore a database which has an encrypted column. I used methods from Larentiu's blog post - column encryption demo.

I execute this command and get:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'

Msg 15313, Level 16, State 1, Line 1

The key is not encrypted using the specified decryptor.

|||

Hi Sam,

I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?

thanks...

|||

I just answered a similar problem in the following thread of this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1

Hopefully this information will help, but if you still have any problems let us know.

-Raul Garcia

SDE/T

SQL Server Engine

Question on restoring encrypted databases on different servers

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'

CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.

All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.

I then try the following commands, which I used when I brought the development database over to the test box the first time:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

This caused the following error:

1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)

2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0

Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Also, the commands that you need to execute when moving a database to another server are:

OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

The REGENERATE option should be used only if you want to have a new master key in the database.

Thanks
Laurentiu

|||

This solves my problem. Thank you for your help.

So, how do you make the original database require the Open Master Key Statement?

|||

You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.

Thanks
Laurentiu

|||

You can use the following statement:

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

Just to verify it:

-- Should fail with error 15581:

-- Please create a master key in the database or open the master key in the session before performing this operation.

create certificate foo with subject = 'foo'

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@.ssw0rD>>'

-- Will succeed

create certificate foo with subject = 'foo'

CLOSE MASTER KEY

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

I am also trying to restore a database which has an encrypted column. I used methods from Larentiu's blog post - column encryption demo.

I execute this command and get:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'

Msg 15313, Level 16, State 1, Line 1

The key is not encrypted using the specified decryptor.

|||

Hi Sam,

I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?

thanks...

|||

I just answered a similar problem in the following thread of this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1

Hopefully this information will help, but if you still have any problems let us know.

-Raul Garcia

SDE/T

SQL Server Engine

Question on restoring encrypted databases on different servers

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'

CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.

All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.

I then try the following commands, which I used when I brought the development database over to the test box the first time:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

This caused the following error:

1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)

2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0

Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Also, the commands that you need to execute when moving a database to another server are:

OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

The REGENERATE option should be used only if you want to have a new master key in the database.

Thanks
Laurentiu

|||

This solves my problem. Thank you for your help.

So, how do you make the original database require the Open Master Key Statement?

|||

You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.

Thanks
Laurentiu

|||

You can use the following statement:

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

Just to verify it:

-- Should fail with error 15581:

-- Please create a master key in the database or open the master key in the session before performing this operation.

create certificate foo with subject = 'foo'

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@.ssw0rD>>'

-- Will succeed

create certificate foo with subject = 'foo'

CLOSE MASTER KEY

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

I am also trying to restore a database which has an encrypted column. I used methods from Larentiu's blog post - column encryption demo.

I execute this command and get:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'

Msg 15313, Level 16, State 1, Line 1

The key is not encrypted using the specified decryptor.

|||

Hi Sam,

I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?

thanks...

|||

I just answered a similar problem in the following thread of this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1

Hopefully this information will help, but if you still have any problems let us know.

-Raul Garcia

SDE/T

SQL Server Engine

Monday, March 12, 2012

Question on passing multi-value parameter for multiple branches

Hello. We are using asp .net and reporting services, and trying to pass a multi-value parameter into reporting services that will show data for multiple branches.

Dim paramList As New Generic.List(Of Microsoft.Reporting.WebForms.ReportParameter)

paramList.Add(New Microsoft.Reporting.WebForms.ReportParameter("BranchNumber", 1))

ReportViewer1.ServerReport.SetParameters(paramList)

pInfo = ReportViewer1.ServerReport.GetParameters()

Let me know if you have any suggestions!

Thanks.

Did you try passing "1,2,3,4" instead of "1" ?

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||We tried that also, without any luck.|||

We have already figured out how to move between seperate branches, we just want a corporate option that will show all branches as a whole.

Any ideas?

Thanks

|||There is no way to post the "All" option, you will either have to pass all values to display or use an additional (hidden) parametert which uses the "All" option behind the scenes, something like:

Where SomeVar IN (@.TheValues) OR @.TheMagicParameter = 1 (Where TheMagicParameter is the magic hidden parameter)

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Question on MTS and SQL Server

Hi
I have a stored procedure which gets executed from ASP code, the ASP in turn
uses MTS . the problem is, when the stored procedure encounters a rollback
statement, instead of just sending the error message and then rolling back
the transaction, the application aborts with a SQL Server error message and
the transaction gets rolled back. It does not send the user defined error
message
to the application. if i remove the rollback statement from the stored
procedure and then run the application, the proper error message is displaye
d
. Is there any ways i can skip the SQL Server error messages or any idea o
n
how MTS handles the rollbacks and transactions ?
Thanks
RodgerWhat are the error messages? Can you post the procedure?
ML
http://milambda.blogspot.com/|||These are sql server error messages. when the same stored proc is executed
from query analyser the proc works fine and even the rollback is ok.
8519 Current MSDTC transaction must be committed by remote client.
8525 Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction.
her's the code which i execute and the error message which i get. Remember
i get the error message on my front end which is ASP.NET. The stored
procedure gets executed from the ASp code
Let me know if you have any questions
First Step :
If i execute the first piece of code, the table nfi_stage always has a
value, so the commit tran is sucessfull. Now if i change the code and put a
rollback tran (second piece of code ) instead of a commit tran
the application aborts giving the following error
Error : 6401
Cannot roll back %.*ls. No transaction or savepoint of that name was found.
First Code
begin distributed tran tran_outer
if (select count(*) from nfi_stage ) > 0
begin
delete nfi_stage
if @.@.trancount = 2
begin
commit tran tran_outer
--set @.retcode = 10087
return 10087
end
end
Change code with rollback
begin distributed tran tran_outer
if (select count(*) from nfi_stage ) > 0
begin
delete nfi_stage
if @.@.trancount = 2
begin
rollback tran tran_outer
--set @.retcode = 10087
return 10087
end
end
"ML" wrote:

> What are the error messages? Can you post the procedure?
>
> ML
> --
> http://milambda.blogspot.com/|||Nested transactions can be a pain. Look at the example in Books Online on ho
w
to "append" transactions to existing transactions:
http://msdn2.microsoft.com/en-us/library/ms188378.aspx
A few pointers:
1) check in each procedure where you intend to use explicit transactions,
whether an outer transaction has already begun;
2) if already in transaction create a save point (SAVE TRAN <save point
name> );
3) on errors rollback to the save point (ROLLBACK TRAN <save point name> );
4) only commit a transaction if it was started in the current procedure.
ML
http://milambda.blogspot.com/|||Hi
I tried using the code for transaction existence, and it gives me a error
message no
627 which means i cannot save a distributed transaction. the fact is i do
not have a distributed transaction started. maybe MTS treats the transactio
n
as a distributed transaction. any other alternate to saving a transaction
"ML" wrote:

> Nested transactions can be a pain. Look at the example in Books Online on
how
> to "append" transactions to existing transactions:
> http://msdn2.microsoft.com/en-us/library/ms188378.aspx
> A few pointers:
> 1) check in each procedure where you intend to use explicit transactions,
> whether an outer transaction has already begun;
> 2) if already in transaction create a save point (SAVE TRAN <save point
> name> );
> 3) on errors rollback to the save point (ROLLBACK TRAN <save point name> );
> 4) only commit a transaction if it was started in the current procedure.
>
> ML
> --
> http://milambda.blogspot.com/|||In your example you've started a distributed transaction explicitly.
Maybe you should explain a bit more why you think you need nested
transactions.
ML
http://milambda.blogspot.com/|||Here's the problem
I execute a proc from my VB code, the app is a web base application using
VB, IIS and MTS. when the proc is executed from app and if the first
sstatement in the proc is
if @.@.trancount > 0
save transaction firsttran
the @.@.trancount value = 1
and it gives a sql server error message 627
if i replace the save transaction with a commit transaction
it gives a sql error message 8519
so the problem here is the moment i execute a stored proc there is already a
transaction present which is a distributed transaction, started by MTS. so
the counter is always 1. now if i start my transactions and give a rollback
the entire proc fails and just comes out. its a bit complicated to explain
but can send the stored proc its a big proc.
thanks
"ML" wrote:

> In your example you've started a distributed transaction explicitly.
> Maybe you should explain a bit more why you think you need nested
> transactions.
>
> ML
> --
> http://milambda.blogspot.com/|||"Rodger" <Rodger@.discussions.microsoft.com> wrote in message
news:7D6214FF-DBFE-4EE3-B5A9-CBA834A2376B@.microsoft.com...
> Here's the problem
> I execute a proc from my VB code, the app is a web base application using
> VB, IIS and MTS. when the proc is executed from app and if the first
> sstatement in the proc is
> if @.@.trancount > 0
> save transaction firsttran
> the @.@.trancount value = 1
> and it gives a sql server error message 627
> if i replace the save transaction with a commit transaction
> it gives a sql error message 8519
> so the problem here is the moment i execute a stored proc there is already
> a
> transaction present which is a distributed transaction, started by MTS. so
> the counter is always 1. now if i start my transactions and give a
> rollback
> the entire proc fails and just comes out. its a bit complicated to
> explain
> but can send the stored proc its a big proc.
> thanks
>
You do realise that ROLLBACK is not nestable, and ROLLBACK will roll back
all pending nested transactions?
David|||Can you just turn off the MTS transaction and control the transaction in
your stored procedure or do you have a need to coordinate two different
components in the same distributed transaction with MTS.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23812F0LZGHA.4144@.TK2MSFTNGP04.phx.gbl...
> "Rodger" <Rodger@.discussions.microsoft.com> wrote in message
> news:7D6214FF-DBFE-4EE3-B5A9-CBA834A2376B@.microsoft.com...
> You do realise that ROLLBACK is not nestable, and ROLLBACK will roll back
> all pending nested transactions?
> David
>

Wednesday, March 28, 2012

Question regarding Stored Procedures, Views and ASP

Monday, March 26, 2012

Question re: security issue detailed in KB887459

Friday, March 23, 2012

question on temporary tables

question on temporary tables

question on temporary tables

question on temporary tables

question on temporary tables

question on temporary tables

question on temporary tables

Tuesday, March 20, 2012

Question on security

Question on restoring encrypted databases on different servers

Question on restoring encrypted databases on different servers

Question on restoring encrypted databases on different servers

Question on restoring encrypted databases on different servers

Monday, March 12, 2012

Question on passing multi-value parameter for multiple branches

Question on MTS and SQL Server

SQLAgentMail

Blog Archive

About Me