Known assembly FileIOPermission error, still no solution

I'm trying to write to a text file from my custom assembly and I keep
getting FileIOPermission. The only way I can get it to work is by
changing the PermissionSetName of the whole CodeGroup from Nothing to
FullTrust. The assembly itself is very simple and all it does is it
writes one line to a text file.
Here is what I did so far:
1. I asserted the permission in my code.
2. I put the text file and my assembly into ReportSevrer bin folder and
changed the text file's security to allow "NETWORK SECURITY" (it
is IIS6) and just in case "Everyone" to write to it.
3. I added CodeGroup just after the code group with Url="$CodeGen$/*"
to the rssrvpolicy.config file with PermissionSetName="FullTrust".
I even installed Visual Studio 2005 to get access to PermCalc tool.
All it showed me was that my dll needs FileIOPermission with
Unrestricted="true" and SecurityPermission with
Flags="Assertion" in the CodeGroup which I also tried by creating a
seperate PermissionSet.
What else can I possibly try?
My Code:
private void WriteLogFile(String msg)
{
FileIOPermission perm1 = new
FileIOPermission(FileIOPermissionAccess.Write, @."C:\Program
Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\ReportLogger.log");
perm1.Assert();
FileStream fs = new FileStream(@."C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\ReportServer\bin\ReportLogger.log",
FileMode.OpenOrCreate, FileAccess.ReadWrite);
StreamWriter w = new StreamWriter(fs);
w.BaseStream.Seek(0, SeekOrigin.End);
w.Write("{0} {1} ", DateTime.Now.ToLongTimeString(),
DateTime.Now.ToLongDateString());
w.Write(msg + "\r\n");
w.Flush();
w.Close();
}
My CodeGroup:
<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="CGReportHelper"
Description="Allow execution of ReportHelper.dll">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="file://C:/Program Files/Microsoft SQL Server/MSSQL/Reporting
Services/ReportServer/bin/ReportHelper.dll"/>
</CodeGroup>
My Error:
w3wp!processing!2aa8!10/05/2005-16:47:17:: e ERROR: Failed to load
expression host assembly. Details: Request for the permission of type
System.Security.Permissions.FileIOPermission, mscorlib,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
failed.
System.Security.SecurityException: Request for the permission of type
System.Security.Permissions.FileIOPermission, mscorlib,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
failed.
at
System.Security.CodeAccessSecurityEngine.CheckHelper(PermissionSet
grantedSet, PermissionSet deniedSet, CodeAccessPermission demand,
PermissionToken permToken)
at System.Security.CodeAccessSecurityEngine.Check(PermissionToken
permToken, CodeAccessPermission demand, StackCrawlMark& stackMark,
Int32 checkFrames, Int32 unrestrictedOverride)
at
System.Security.CodeAccessSecurityEngine.Check(CodeAccessPermission
cap, StackCrawlMark& stackMark)
at System.Security.CodeAccessPermission.Demand()
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess
access, FileShare share, Int32 bufferSize, Boolean useAsync, String
msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess
access)
at ReportHelper.ConfirmationStatement.WriteLogFile(String msg)
at ReportHelper.ConfirmationStatement..ctor(Int32 futureBatchSetId)
at CustomCodeProxy.OnInit()
at Microsoft.ReportingServices.ReportProcessing.ExprHostObjectModel.
CustomCodeProxyBase..ctor(IReportObjectModelProxyForCustomCode
reportObjectModel)
at ReportExprHostImpl..ctor(Boolean parametersOnly, Object
reportObjectModel)
The state of the failed permission was:
<IPermission class="System.Security.Permissions.FileIOPermission,
mscorlib, Version=1.0.5000.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089"
version="1"
Read="C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\ReportServer\bin\ReportLogger.log"
Write="C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\ReportServer\bin\ReportLogger.log"/>
w3wp!processing!2aa8!10/05/2005-16:47:17:: e ERROR: Throwing
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Exception of type
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException
was thrown., ;
Info:
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Exception of type
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException
was thrown.
w3wp!library!2aa8!10/05/2005-16:47:17:: i INFO: Initializing
EnableExecutionLogging to 'True' as specified in Server system
properties.
w3wp!webserver!2aa8!10/05/2005-16:47:17:: e ERROR: Reporting Services
error Microsoft.ReportingServices.Diagnostics.Utilities.RSException:
Failed to load expression host assembly. Details: Request for the
permission of type System.Security.Permissions.FileIOPermission,
mscorlib, Version=1.0.5000.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089 failed. -->
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Failed to load expression host assembly. Details: Request for the
permission of type System.Security.Permissions.FileIOPermission,
mscorlib, Version=1.0.5000.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089 failed.Hi,
See this article....
http://www.c-sharpcorner.com/Code/2005/June/CustomAssemblyinRS.asp
If still you have the issue, write to me bkkrishnan [at] hotmail [dot] com
Balaji
Siwy wrote:
>I'm trying to write to a text file from my custom assembly and I keep
>getting FileIOPermission. The only way I can get it to work is by
>changing the PermissionSetName of the whole CodeGroup from Nothing to
>FullTrust. The assembly itself is very simple and all it does is it
>writes one line to a text file.
>Here is what I did so far:
>1. I asserted the permission in my code.
>2. I put the text file and my assembly into ReportSevrer bin folder and
>changed the text file's security to allow "NETWORK SECURITY" (it
>is IIS6) and just in case "Everyone" to write to it.
>3. I added CodeGroup just after the code group with Url="$CodeGen$/*"
>to the rssrvpolicy.config file with PermissionSetName="FullTrust".
>I even installed Visual Studio 2005 to get access to PermCalc tool.
>All it showed me was that my dll needs FileIOPermission with
>Unrestricted="true" and SecurityPermission with
>Flags="Assertion" in the CodeGroup which I also tried by creating a
>seperate PermissionSet.
>What else can I possibly try?
>My Code:
>private void WriteLogFile(String msg)
>{
> FileIOPermission perm1 = new
>FileIOPermission(FileIOPermissionAccess.Write, @."C:\Program
>Files\Microsoft SQL Server\MSSQL\Reporting
>Services\ReportServer\bin\ReportLogger.log");
> perm1.Assert();
> FileStream fs = new FileStream(@."C:\Program Files\Microsoft SQL
>Server\MSSQL\Reporting Services\ReportServer\bin\ReportLogger.log",
>FileMode.OpenOrCreate, FileAccess.ReadWrite);
> StreamWriter w = new StreamWriter(fs);
> w.BaseStream.Seek(0, SeekOrigin.End);
> w.Write("{0} {1} ", DateTime.Now.ToLongTimeString(),
> DateTime.Now.ToLongDateString());
> w.Write(msg + "\r\n");
> w.Flush();
> w.Close();
>}
>My CodeGroup:
><CodeGroup class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Name="CGReportHelper"
> Description="Allow execution of ReportHelper.dll">
> <IMembershipCondition class="UrlMembershipCondition"
> version="1"
>Url="file://C:/Program Files/Microsoft SQL Server/MSSQL/Reporting
>Services/ReportServer/bin/ReportHelper.dll"/>
></CodeGroup>
>My Error:
>w3wp!processing!2aa8!10/05/2005-16:47:17:: e ERROR: Failed to load
>expression host assembly. Details: Request for the permission of type
>System.Security.Permissions.FileIOPermission, mscorlib,
>Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
>failed.
>System.Security.SecurityException: Request for the permission of type
>System.Security.Permissions.FileIOPermission, mscorlib,
>Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
>failed.
> at
>System.Security.CodeAccessSecurityEngine.CheckHelper(PermissionSet
>grantedSet, PermissionSet deniedSet, CodeAccessPermission demand,
>PermissionToken permToken)
> at System.Security.CodeAccessSecurityEngine.Check(PermissionToken
>permToken, CodeAccessPermission demand, StackCrawlMark& stackMark,
>Int32 checkFrames, Int32 unrestrictedOverride)
> at
>System.Security.CodeAccessSecurityEngine.Check(CodeAccessPermission
>cap, StackCrawlMark& stackMark)
> at System.Security.CodeAccessPermission.Demand()
> at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess
>access, FileShare share, Int32 bufferSize, Boolean useAsync, String
>msgPath, Boolean bFromProxy)
> at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess
>access)
> at ReportHelper.ConfirmationStatement.WriteLogFile(String msg)
> at ReportHelper.ConfirmationStatement..ctor(Int32 futureBatchSetId)
> at CustomCodeProxy.OnInit()
> at Microsoft.ReportingServices.ReportProcessing.ExprHostObjectModel.
>CustomCodeProxyBase..ctor(IReportObjectModelProxyForCustomCode
>reportObjectModel)
> at ReportExprHostImpl..ctor(Boolean parametersOnly, Object
>reportObjectModel)
>The state of the failed permission was:
><IPermission class="System.Security.Permissions.FileIOPermission,
>mscorlib, Version=1.0.5000.0, Culture=neutral,
>PublicKeyToken=b77a5c561934e089"
> version="1"
> Read="C:\Program Files\Microsoft SQL
>Server\MSSQL\Reporting Services\ReportServer\bin\ReportLogger.log"
> Write="C:\Program Files\Microsoft SQL
>Server\MSSQL\Reporting Services\ReportServer\bin\ReportLogger.log"/>
>w3wp!processing!2aa8!10/05/2005-16:47:17:: e ERROR: Throwing
>Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
>Exception of type
>Microsoft.ReportingServices.ReportProcessing.ReportProcessingException
>was thrown., ;
> Info:
>Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
>Exception of type
>Microsoft.ReportingServices.ReportProcessing.ReportProcessingException
>was thrown.
>w3wp!library!2aa8!10/05/2005-16:47:17:: i INFO: Initializing
>EnableExecutionLogging to 'True' as specified in Server system
>properties.
>w3wp!webserver!2aa8!10/05/2005-16:47:17:: e ERROR: Reporting Services
>error Microsoft.ReportingServices.Diagnostics.Utilities.RSException:
>Failed to load expression host assembly. Details: Request for the
>permission of type System.Security.Permissions.FileIOPermission,
>mscorlib, Version=1.0.5000.0, Culture=neutral,
>PublicKeyToken=b77a5c561934e089 failed. -->
>Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
>Failed to load expression host assembly. Details: Request for the
>permission of type System.Security.Permissions.FileIOPermission,
>mscorlib, Version=1.0.5000.0, Culture=neutral,
>PublicKeyToken=b77a5c561934e089 failed.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200510/1|||I finally figured it out.
The problem was with assertion in my code. I changed it from
FileIOPermissionAccess.Write to FileIOPermissionAccess.AllAccess and it
worked.
I guess when you open a file with FileAccess.ReadWrite then assertion
FileIOPermissionAccess.Write is not enough.
Regards,|||Hi,
I'm custom assemblie to access the registry and get some data..
i'm getting a error of "Requested registry access is not allowed"
I followed all the steps that you mentioned but i still get the same error..
but in case of File access it works im not getting any error but for
Registry acccess im getting that error. did you tried using registry. i even
gave "FullTrust" in the the permission policy file.
Please let me know if any one have tried registree.
Thanks
Bava
"BALAJI K via SQLMonster.com" wrote:
> Hi,
> See this article....
> http://www.c-sharpcorner.com/Code/2005/June/CustomAssemblyinRS.asp
> If still you have the issue, write to me bkkrishnan [at] hotmail [dot] com
> Balaji
>
> Siwy wrote:
> >I'm trying to write to a text file from my custom assembly and I keep
> >getting FileIOPermission. The only way I can get it to work is by
> >changing the PermissionSetName of the whole CodeGroup from Nothing to
> >FullTrust. The assembly itself is very simple and all it does is it
> >writes one line to a text file.
> >
> >Here is what I did so far:
> >
> >1. I asserted the permission in my code.
> >2. I put the text file and my assembly into ReportSevrer bin folder and
> >changed the text file's security to allow "NETWORK SECURITY" (it
> >is IIS6) and just in case "Everyone" to write to it.
> >3. I added CodeGroup just after the code group with Url="$CodeGen$/*"
> >to the rssrvpolicy.config file with PermissionSetName="FullTrust".
> >
> >I even installed Visual Studio 2005 to get access to PermCalc tool.
> >All it showed me was that my dll needs FileIOPermission with
> >Unrestricted="true" and SecurityPermission with
> >Flags="Assertion" in the CodeGroup which I also tried by creating a
> >seperate PermissionSet.
> >
> >What else can I possibly try?
> >
> >My Code:
> >
> >private void WriteLogFile(String msg)
> >{
> > FileIOPermission perm1 = new
> >FileIOPermission(FileIOPermissionAccess.Write, @."C:\Program
> >Files\Microsoft SQL Server\MSSQL\Reporting
> >Services\ReportServer\bin\ReportLogger.log");
> > perm1.Assert();
> >
> > FileStream fs = new FileStream(@."C:\Program Files\Microsoft SQL
> >Server\MSSQL\Reporting Services\ReportServer\bin\ReportLogger.log",
> >FileMode.OpenOrCreate, FileAccess.ReadWrite);
> > StreamWriter w = new StreamWriter(fs);
> > w.BaseStream.Seek(0, SeekOrigin.End);
> > w.Write("{0} {1} ", DateTime.Now.ToLongTimeString(),
> > DateTime.Now.ToLongDateString());
> > w.Write(msg + "\r\n");
> > w.Flush();
> >
> > w.Close();
> >}
> >
> >My CodeGroup:
> >
> ><CodeGroup class="UnionCodeGroup"
> > version="1"
> > PermissionSetName="FullTrust"
> > Name="CGReportHelper"
> > Description="Allow execution of ReportHelper.dll">
> > <IMembershipCondition class="UrlMembershipCondition"
> > version="1"
> >Url="file://C:/Program Files/Microsoft SQL Server/MSSQL/Reporting
> >Services/ReportServer/bin/ReportHelper.dll"/>
> ></CodeGroup>
> >
> >My Error:
> >
> >w3wp!processing!2aa8!10/05/2005-16:47:17:: e ERROR: Failed to load
> >expression host assembly. Details: Request for the permission of type
> >System.Security.Permissions.FileIOPermission, mscorlib,
> >Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
> >failed.
> >System.Security.SecurityException: Request for the permission of type
> >System.Security.Permissions.FileIOPermission, mscorlib,
> >Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
> >failed.
> > at
> >System.Security.CodeAccessSecurityEngine.CheckHelper(PermissionSet
> >grantedSet, PermissionSet deniedSet, CodeAccessPermission demand,
> >PermissionToken permToken)
> > at System.Security.CodeAccessSecurityEngine.Check(PermissionToken
> >permToken, CodeAccessPermission demand, StackCrawlMark& stackMark,
> >Int32 checkFrames, Int32 unrestrictedOverride)
> > at
> >System.Security.CodeAccessSecurityEngine.Check(CodeAccessPermission
> >cap, StackCrawlMark& stackMark)
> > at System.Security.CodeAccessPermission.Demand()
> > at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess
> >access, FileShare share, Int32 bufferSize, Boolean useAsync, String
> >msgPath, Boolean bFromProxy)
> > at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess
> >access)
> > at ReportHelper.ConfirmationStatement.WriteLogFile(String msg)
> > at ReportHelper.ConfirmationStatement..ctor(Int32 futureBatchSetId)
> > at CustomCodeProxy.OnInit()
> > at Microsoft.ReportingServices.ReportProcessing.ExprHostObjectModel.
> >CustomCodeProxyBase..ctor(IReportObjectModelProxyForCustomCode
> >reportObjectModel)
> > at ReportExprHostImpl..ctor(Boolean parametersOnly, Object
> >reportObjectModel)
> >
> >The state of the failed permission was:
> ><IPermission class="System.Security.Permissions.FileIOPermission,
> >mscorlib, Version=1.0.5000.0, Culture=neutral,
> >PublicKeyToken=b77a5c561934e089"
> > version="1"
> > Read="C:\Program Files\Microsoft SQL
> >Server\MSSQL\Reporting Services\ReportServer\bin\ReportLogger.log"
> > Write="C:\Program Files\Microsoft SQL
> >Server\MSSQL\Reporting Services\ReportServer\bin\ReportLogger.log"/>
> >
> >w3wp!processing!2aa8!10/05/2005-16:47:17:: e ERROR: Throwing
> >Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> >Exception of type
> >Microsoft.ReportingServices.ReportProcessing.ReportProcessingException
> >was thrown., ;
> > Info:
> >Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> >Exception of type
> >Microsoft.ReportingServices.ReportProcessing.ReportProcessingException
> >was thrown.
> >w3wp!library!2aa8!10/05/2005-16:47:17:: i INFO: Initializing
> >EnableExecutionLogging to 'True' as specified in Server system
> >properties.
> >w3wp!webserver!2aa8!10/05/2005-16:47:17:: e ERROR: Reporting Services
> >error Microsoft.ReportingServices.Diagnostics.Utilities.RSException:
> >Failed to load expression host assembly. Details: Request for the
> >permission of type System.Security.Permissions.FileIOPermission,
> >mscorlib, Version=1.0.5000.0, Culture=neutral,
> >PublicKeyToken=b77a5c561934e089 failed. -->
> >Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> >Failed to load expression host assembly. Details: Request for the
> >permission of type System.Security.Permissions.FileIOPermission,
> >mscorlib, Version=1.0.5000.0, Culture=neutral,
> >PublicKeyToken=b77a5c561934e089 failed.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200510/1
>

kill long running query

I am changing some field types in a large table
by creating the new table and then selecting the
data into it. So no changes are being made to the
existing data.
I started this at 5:00 last night, and by 11:00
it became apparent that it wasn't going to finish
until this weekend.
I can run queries in UNCOMMITTED READ isolation level
and see how many images have been copied. By 11:00
it was only 12% done.
So I cancelled the query at 11:00. 12 hours later,
it is about half way done rolling back. As a result
we can't access the table and get any work done today.
I don't need this transaction rolled back. I could
just drop the temporary table and be done with it.
I've looked at the KILL copmmand, but that just causes
it to rollback, so it doesn't help.
I could reboot the server, but as far as I know, it might
pick up where it left off and continue rolling back the
transaction.
Is there any way out of this hole that I've dug myself into?
Thanks,
Brad.> So I cancelled the query at 11:00. 12 hours later,
> it is about half way done rolling back. As a result
> we can't access the table and get any work done today.
If it is rolling back a SELECT INTO a new table, why would this prevent you
from accessing the existing table? Or did you mean to explain that the
rollback is consuming the server in terms of resources?|||Aaron Bertrand [SQL Server MVP] wrote :
>> So I cancelled the query at 11:00. 12 hours later,
>> it is about half way done rolling back. As a result
>> we can't access the table and get any work done today.
> If it is rolling back a SELECT INTO a new table, why would this prevent you
> from accessing the existing table? Or did you mean to explain that the
> rollback is consuming the server in terms of resources?
I don't know the answer to that.
I assume that it has affected so many rows
(I was copying the entire table to a new table)
that the locks have been promoted to table level.
2G rows.
The script I was using use ISOLATION LEVEL SERIALIZABLE.
The question has been rendered moot, as the server crashed.
Thanks,
Brad.|||Aaron Bertrand [SQL Server MVP] wrote on 4/4/2008 :
>> So I cancelled the query at 11:00. 12 hours later,
>> it is about half way done rolling back. As a result
>> we can't access the table and get any work done today.
> If it is rolling back a SELECT INTO a new table, why would this prevent you
> from accessing the existing table? Or did you mean to explain that the
> rollback is consuming the server in terms of resources?
No, I don't know why it would be adding locks
on the SOURCE table.
What led me to think that was that queries against
any other table would return reasonably, while
queries against either of those tables would run
up to 30 minutes without returning before I killed them.
Thanks,
Brad.|||2 billion rows, yeah that's going to be tough. If you have the space, I
would suggest making a backup of the database, restoring it as a different
database, and then copy the rows from the restored copy. Then you can use a
much lower isolation level because you don't have to worry about people
coming in and changing the source rows while you are copying...
"Brad White" <bwhite_at_inebraska_dot_com@.remove.nul> wrote in message
news:mn.24b57d840b74b3fd.81110@.remove.nul...
> Aaron Bertrand [SQL Server MVP] wrote :
>> So I cancelled the query at 11:00. 12 hours later,
>> it is about half way done rolling back. As a result
>> we can't access the table and get any work done today.
>> If it is rolling back a SELECT INTO a new table, why would this prevent
>> you from accessing the existing table? Or did you mean to explain that
>> the rollback is consuming the server in terms of resources?
> I don't know the answer to that.
> I assume that it has affected so many rows
> (I was copying the entire table to a new table)
> that the locks have been promoted to table level.
> 2G rows.
> The script I was using use ISOLATION LEVEL SERIALIZABLE.
> The question has been rendered moot, as the server crashed.
> Thanks,
> Brad.
>

kill long running query

I am changing some field types in a large table
by creating the new table and then selecting the
data into it. So no changes are being made to the
existing data.
I started this at 5:00 last night, and by 11:00
it became apparent that it wasn't going to finish
until this weekend.
I can run queries in UNCOMMITTED READ isolation level
and see how many images have been copied. By 11:00
it was only 12% done.
So I cancelled the query at 11:00. 12 hours later,
it is about half way done rolling back. As a result
we can't access the table and get any work done today.
I don't need this transaction rolled back. I could
just drop the temporary table and be done with it.
I've looked at the KILL copmmand, but that just causes
it to rollback, so it doesn't help.
I could reboot the server, but as far as I know, it might
pick up where it left off and continue rolling back the
transaction.
Is there any way out of this hole that I've dug myself into?
Thanks,
Brad.
> So I cancelled the query at 11:00. 12 hours later,
> it is about half way done rolling back. As a result
> we can't access the table and get any work done today.
If it is rolling back a SELECT INTO a new table, why would this prevent you
from accessing the existing table? Or did you mean to explain that the
rollback is consuming the server in terms of resources?
|||Aaron Bertrand [SQL Server MVP] wrote :
> If it is rolling back a SELECT INTO a new table, why would this prevent you
> from accessing the existing table? Or did you mean to explain that the
> rollback is consuming the server in terms of resources?
I don't know the answer to that.
I assume that it has affected so many rows
(I was copying the entire table to a new table)
that the locks have been promoted to table level.
2G rows.
The script I was using use ISOLATION LEVEL SERIALIZABLE.
The question has been rendered moot, as the server crashed.
Thanks,
Brad.
|||Aaron Bertrand [SQL Server MVP] wrote on 4/4/2008 :
> If it is rolling back a SELECT INTO a new table, why would this prevent you
> from accessing the existing table? Or did you mean to explain that the
> rollback is consuming the server in terms of resources?
No, I don't know why it would be adding locks
on the SOURCE table.
What led me to think that was that queries against
any other table would return reasonably, while
queries against either of those tables would run
up to 30 minutes without returning before I killed them.
Thanks,
Brad.
|||2 billion rows, yeah that's going to be tough. If you have the space, I
would suggest making a backup of the database, restoring it as a different
database, and then copy the rows from the restored copy. Then you can use a
much lower isolation level because you don't have to worry about people
coming in and changing the source rows while you are copying...
"Brad White" <bwhite_at_inebraska_dot_com@.remove.nul> wrote in message
news:mn.24b57d840b74b3fd.81110@.remove.nul...
> Aaron Bertrand [SQL Server MVP] wrote :
> I don't know the answer to that.
> I assume that it has affected so many rows
> (I was copying the entire table to a new table)
> that the locks have been promoted to table level.
> 2G rows.
> The script I was using use ISOLATION LEVEL SERIALIZABLE.
> The question has been rendered moot, as the server crashed.
> Thanks,
> Brad.
>