when I call ((System.Collections.ICollection)resultSet.ResultSetView).Count multitime I get out of memory exception
does the calling of this statement reserve memory and how can I release it?
Hi I have an issue while attempting to clean/build my Integration Services solution which prevents me creating my deployment SSIS deployment package.
The solution comprises of around 73 packages totally a physical file size of 103 MB. Most packages are ~1/2 MB with a ~1/2 packages around the 4/5/6 MB
I cannot seem to clean or build/rebuild the solution as I keep getting the following exception message
Error 1 Error loading ��PACKAGE_NAME.dtsx' : Exception of type 'System.OutOfMemoryException' was thrown.. C:FOLDER1PACKAGE_NAME.dtsx 1 1
This randomly occurs with different packages but the commonality is it occurs with the package above 4MB.
My workstation has Windows XP Professional with SP2 and 2GB RAM and Visual Studio 2005 with SP1 and SQL Server 2005 with SP2.
A look at task manager in Windows shows the devenv.exe process to be ~675MB when the above exception occurs. There are no other applications running in the background.
Can anybody kindly suggest any possible resolutions or any known fixes for this problem? Maybe the possible to build SSIS packages from a command prompt?
Many Thanks in advance
Hi,
when I�m trying to save a SSIS Package in Visual Studio I�m having the following problem:
===================================
Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft Visual Studio)
------------------------------
Program Location:
at System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity)
at System.Text.StringBuilder.GetNewString(String currentString, Int32 requiredLength)
at System.Text.StringBuilder.Append(Char value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BufferTextWriter.Write(Char ch)
at System.IO.TextWriter.Write(Char[] buffer, Int32 index, Int32 count)
at System.IO.TextWriter.Write(Char[] buffer)
at System.IO.TextWriter.Write(String value)
at Microsoft.DataTransformationServices.Design.Serialization.DtrDesignerSerializer.SerializePackage(IDesignerSerializationManager manager, Package package, TextWriter textWriter)
at Microsoft.DataTransformationServices.Design.Serialization.DtrDesignerSerializer.SerializeComponent(IDesignerSerializationManager manager, IComponent component, Object serializationStream)
at Microsoft.DataWarehouse.Serialization.DesignerComponentSerializer.Serialize(IDesignerSerializationManager manager, Object value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseDesignerLoader.Serialize()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush(Boolean forceful)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseContainerManager.OnBeforeSave(UInt32 docCookie)
The dtsx is around 8MB size, I have installed the service pack, what else could be?
thanks
I am trying to load a XML file into a string variable using a XML Task. The file is 67,936 KB. When I excecute the package in the dev environment I get the following error:
[XML Task] Error: An error occurred with the following error message: "Exception of type 'System.OutOfMemoryException' was thrown.".
Has anybody run into this before? In the next step I pass the XMLData string to a stored procedure which loads it into a table with a XML data type column.
Recently upgraded SQL Server 2005 x64 to SP2 and upto Build 3159. Since then the Maintenance Plan for Index Reorgs has failed with a System.OutOfMemoryException error. No other errors are logged anywhere. The plan report file has no information either.
Has anybody come across a similar issue?
Thank you.
All,
I have a large package (I know the recommendation is to have one package per data flow.) It has 20+ data flows in it. Personally I have found it much too complex to manage dozens of packages just because I want to have more than one data flow in my package.
I have been working on this package in an iterative manner over the past several months. Recently, I noticed I started getting the error message: "Exception of type 'System.OutOfMemoryException' was thrown" when I went to save my package. This is _extremely_ frustrating, because when this happens, all the changes I have made are generally lost. Occasionally, I have noticed if I close some other BIDS windows, or just wait a bit, I will be able to click save again, and it will actually save. Usually, I am forced to just "end-task" Visual Studio.
Other than splitting the package up into 20 separate packages, is there a way around this problem? I would rather put up with the lost changes than switch to dealing with 20 separate packages. It just makes things to difficult to manage when everything is split up into so many packages - particularly when I am passing variables around from parent to child packages.
Please help!
Thanks,
David Baldauff
Hi,
My server configuration is
Intel Xeon(R) X5355 @ 2.66GHz, 3.75 GB RAM
Win 2k3
I have been receiving the above mentioned error when I try to use the out of the box feature of "Export To Excel" provided in the Reporting Services, the reports are hosted on MOSS 2007 using the Reporting Services Add-In. The number of pages generated by the report is 500+, so I don't think exporting a report of around 500 - 1000 should give out a memory exception since we have sufficient RAM on the server.
Please advice a solution to solve the error.
Thanks in Advance.
Vikas Mestry.
This is my error text... how do I get this nonsense to stop?!
===================================
Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft Visual Studio)
------------------------------
Program Location:
at System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity)
at System.Text.StringBuilder.GetNewString(String currentString, Int32 requiredLength)
at System.Text.StringBuilder.Append(Char value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BufferTextWriter.Write(Char ch)
at System.IO.TextWriter.Write(Char[] buffer, Int32 index, Int32 count)
at System.IO.TextWriter.Write(Char[] buffer)
at System.IO.TextWriter.Write(String value)
at Microsoft.DataTransformationServices.Design.Serialization.DtrDesignerSerializer.SerializePackage(IDesignerSerializationManager manager, Package package, TextWriter textWriter)
at Microsoft.DataTransformationServices.Design.Serialization.DtrDesignerSerializer.SerializeComponent(IDesignerSerializationManager manager, IComponent component, Object serializationStream)
at Microsoft.DataWarehouse.Serialization.DesignerComponentSerializer.Serialize(IDesignerSerializationManager manager, Object value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseDesignerLoader.Serialize()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush(Boolean forceful)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseContainerManager.OnBeforeSave(UInt32 docCookie)
I have an SqlDataSource control on my aspx page, this is connected to database by a built in procedure that returns a string dependent upon and ID passed in.
I have the followinbg codewhich is not complet, I woiuld appriciate any help to produce the correct code for the code file
Function GetCategoryName(ByVal ID As Integer) As String sdsCategoriesByID.SelectParameters("ID").Direction = Data.ParameterDirection.Input sdsCategoriesByID.SelectParameters.Item("ID").DefaultValue = 3 sdsCategoriesByID.Select() <<<< THIS LINE COMES UP WITH ERROR 1End Function
ERROR AS FOLLOWS
argument not specified for parameter 'arguments' of public function Select(arguments as System.Web.DatasourceSelect Arguments as Collections ienumerable
Help I have not got much more hair to loose
Thanks Steve
Hi,
I got an strange problem with one of my packages.
When running the package in VisualStudio it runs properly, but if I let this package run as part of an SQL-Server Agent job, I got the message "The script threw an exception: Exception of type 'System.OutOfMemoryException' was thrown." on my log and the package ends up with an error.
Both times it is exactly the same package on the same server, so I don't know how the debug or even if there is anything I need to debug?
Regards,
Jan
I have a select with a few joins producing this results table:
1 Name1 Date1
1 Name1 Date1
1 Name1 Date1
2 Name1 Date2
2 Name1 Date2
2 Name1 Date2
2 Name1 Date2
How can I turn it to this:
1 Name1 Date1 Count3
2 Name1 Date2 Count4
Hi,
How to put a resultset of a System Stored Procedure into a table?.
For Ex,
I want to put the result of sp_Databases into a table
Is there anyway to access the row count of a SQLCEResultSet?
I found this but it is not a public property.
ResultSetView.rowViewList
Hi again, and so soon...Having just solved my previous issue, I am now trying to call this stored proc from my page.aspx.vb code. Firstly the stored proc looks like this:----------- ALTER PROCEDURE dbo.CountTEstAS SET NOCOUNT ON DECLARE @sql nvarchar(100); DECLARE @recCount int; DECLARE @parms nvarchar(100); SET @sql = 'SELECT @recCount1 = COUNT(*) FROM Pool 'SET @parms = '@recCount1 int OUTPUT' exec sp_executesql @sql, @parms, @recCount1 = @recCount OUTPUTRETURN @recCount -------------When tested from the stored proc, the result is: @RETURN_VALUE = 4 My code that calls this stored proc is: Dim connect As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") connect.Open() Dim cmd As New SqlCommand("CountTEst", connect) cmd.CommandType = CommandType.StoredProcedure Dim MyCount As Int32 MyCount = cmd.ExecuteScalar connect.Close() So I expext MyCount = 4 but this code returns MyCount = 0 With the RETURN statement in the stored proc, and then calling it via MyCount = cmd.ExecuteScalar, I didn't think I need to explicitly define any other parameters. Any help please. thanks,
View 5 Replies View RelatedI am creating a simple SSRS table report through Report Builder. My dataset is looking for the stored procedure . When I execute the Stored procedure through SSMS I get resutset for certain parameters. I execute the dataset (Store procedure) through query designer in dataset properties and I get results back. But when I try to run the report and see the preview, I do not get any results displayed. I been looking on the same issue form last 3-4 days and have not found any clue.
Following is the stored procedure I am using. Also I am passing multivalued parameter through report as well, and I am using spilt function to seperate the libraryid I am reading from parameter values. This works fine. I have similar kind of four other reports and with different stored procedure which exactly follow the same method , like multivalue parameters and other criteria are also very similar. All other reports works just fine.. This perticular report has issue for displying results, following is the stored procedure I am using
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[code]....
the Table columns is like this NO ProductNo Area In Out1 0001 US NULL NULL2 0002 UK NULL Y3 0003 FR Y NULL 4 0004 FR Y NULL5 0005 UK Y NULL I have Query get the result belowArea Count In&OutUS 1UK 2FR 2 the Area is Group By by Area and the Count columns is counting how many recoreds Table for each Areathe problem is the column "In & OUt'I have to make sure if the "In" or "Out" is Null , if one of them is Null the plus 1 so the result would like I have Query get the result belowArea Count In&OutUS 1 1
UK 2 1
FR 2 0 which syntax I can use for the problem? I just think maybe I can use IsNULL?but I have no idea how to wirte a query ...can you give me a hint? thank you
Hi All,I have one question about many "or" operation make system chooseincorrect indexThere is one table TT (C1 VARCHAR(15) NOT NULL,C2 VARCHAR(15) NOT NULL,C3 VARCHAR(15) NOT NULL,C4 VARCHAR(15) NOT NULLC5 VARCHAR2(200),)Primary Key TT_PK (C1, C2, C3, C4)SELECT C1, C2, C3, C4 FROM TT WHERE C1 = 'TEST' AND ((C2 ='07RES' AND C3 = '00000' AND C4 = '02383') OR (C2 = '07RES' ANDC3 = '00000' AND C4 = '02382') OR (C2 = '07RES' AND C3 = '00000'AND C4 = '02381') OR (C2 = '07RES' AND C3 = '00000' AND C4 ='02380') OR (C2 = '07RES' AND C3 = '00000' AND C4 = '02379') OR(C2 = '07RES' AND C3 = '00000' AND C4 = '02378') OR (C2 = '07RES'AND C3 = '00000' AND C4 = '02377') OR (C2 = '07RES' AND C3 ='00000' AND C4 = '02376') OR (C2 = '07RES' AND C3 = '00000' ANDC4 = '02375') OR (C2 = '07RES' AND C3 = '00000' AND C4 ='02374') OR (C2 = '07RES' AND C3 = '00000' AND C4 = '02373') OR(C2 = '07RES' AND C3 = '00000' AND C4 = '02372').... about 100 or operationsOR (C2 = '07COM' AND C3 = '00000' AND C4 = '00618') OR (C2 ='07COM' AND C3 = '00000' AND C4 = '00617') OR (C2 = '07COM' ANDC3 = '00000' AND C4 = '00616') OR (C2 = '07COM' AND C3 = '00000'AND C4 = '00608') )The system choose index prefix, and query all index leaf withC1='TEST'Prefix: [dbo].[TT].C1 = 'TEST'After I reduce the OR operators to 50, it use choosePrefix: [dbo].[TT].C1, [dbo].[TT].C2,[dbo].[TT].C3,[dbo].[TT].C4='TEST, '07RES', '00000', '02383'Then Merge Join, it is very quick,Can anyone help on this, do I have to reduce the OR operator to 50?Thanks in advance!
View 5 Replies View RelatedSQL Server 2012 Standard SP 1.
Stored procedure A calls another stored procedure B. Rowcount is set properly in called procedure B, but does not seem to return it to calling procedure A. Otherwise the two stored procedures are working correctly. Here is the relevant code from the calling procedure A:
declare @NumBufferManagerRows int = 0;
exec persist.LoadBufferManager @StartTicks, @EndTicks, @TimeDiff, @NumBufferManagerRows;
print 'BufferManagerRows';
print @NumBufferManagerRows;
Print statement prints @NumBufferManagerRows as 0.
Here is the called stored procedure B:
CREATE PROCEDURE [persist].[LoadBufferManager]
-- Add the parameters for the stored procedure here
@StartTicks bigint,
@EndTicks bigint,
@TimeDiff decimal(9,2),
@NumRows int OUTPUT
[Code] ...
Geoff Davis writes "I am wanting to write a hobbyist catalogue collection database in SQL Server 2005 Express and I am really struggling with its design so I would really appreciate some help or pointers.
Want I want to do is have a single database store items of Music, Movies, Books, etc; what I call Collections but my design should not be limited to these collections, I could have anything really, Electrical goods for example.
There can be many formats within a collection (e.g. Music can have formats of CD, DVD, T-Shirt, etc) but each format within a collection can have it's own set of attributes. (I am calling it an attribute but it might be known as something else) e.g. A Music CD has Artists (Artist Table Lookup) and a Track Listing (Track Listing Table) associated with it but a T-Shirt has completely different attributes such as Artists (Artist Table Lookup), Size (Size Table Lookup) and Colour (Colour Table Lookup) associated with it. So Size and Colour is not applicable to a Music CD but it is to a T-Shirt.
It is fair to say that these attributes could be lookups tables as stated above, single values or even a list of fixed values. e.g. A Music CD has a label which is a lookup but a Photo will not doesn't have a label.
Whats even more confusing for me is that a Movie CD has completely different attributes associated with it, it doesn't have Artists, it has Authors which even though is a person it is a different entity (Well I think it is) An Artist in the Music collection could be a person but it could also be a group of persons under a group name... this is making my head hurt!!!
Regardless of the different collections and the different attributes associated with them I will need to be able to search on any field in the whole database, hmmm How do I do that?
I did think of keeping the collections separate in my database, i.e. MusicArtist, MusicFormat, MusicItem and then have MovieAuthor, MovieFormat, MovieItem.
I could then have a GlobalItem to do the searching on but is this the right way to go or should I work harder to provide a single products table; but if I do that what about the different attributes for each format?
I suppose what I am after is a design like http://www.play.com - If you are looking at Music CD's you get a Track Listing but if you are looking at a DVD, you don't. Or even a store/supermarket type of approach where each product is different and the attributes stored against it needs to be easily retrieved, i.e. lookups.
Can you please help? What is this kind of design called anyway?
Thanks in advance,
Geoff."
I created a CLR function based on following VB code:
Imports Microsoft.SqlServer.Server
Public Partial Class SqlClrVB
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function GetTotalPhysicalMemory() As Integer
GetTotalPhysicalMemory = My.Computer.Info.TotalPhysicalMemory
End Function
End Class
The VB code was complied into a DLL called totalmem.dll and call following TSQL to map it into a SQL function:
create assembly totalmem from '!WORKINGDIR! otalmem.dll'
WITH PERMISSION_SET=UNSAFE
go
create function fnGetTotalMem()
returns int
as external name totalmem.SqlClrVB.GetTotalPhysicalMemory
go
When I call this function, it returned following error:
select dbo.fnGetTotalMem()
Msg 6522, Level 16, State 2, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'fnGetTotalMem':
System.IO.FileNotFoundException: Could not load file or assembly 'System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.
System.IO.FileNotFoundException:
at Microsoft.VisualBasic.MyServices.Internal.ContextValue`1.get_Value()
at My.MyProject.ThreadSafeObjectProvider`1.get_GetInstance()
at SqlClrVB.GetTotalPhysicalMemory()
.
Anyone knows why I'm hitting this error? I didn't reference any System.Web interface why it needs to load System.Web assembly? The same code runs OK if I compile it as a separate VB application out side of SQL Server 2005.
Thanks much,
Zhiqiang
Does anyone know how to get the row count of a user table by using the system tables.
There is no guarantee that these user tables will have any indexes - so I can not use the sysindexes table to count the rows in a clustered index.
Is there another way?
For MS SQL so that I can do something like...
select * from myTable where #system_row_number = 5;
Hi All, I am trying my hand at SQL CLR. I want to know one thing which I'm not able to get from anywhere. Suppose I've a C# class like Student. I create an object of this class and push its values into database. Instead of pushing individual property values to database I want to pass the Student object itself or maybe an array or list etc. Kindly help me if it is possible.
Regards
Anyone knows how to get a Table's Row Count from system tables?
Thanks.
Hi,
My report has multiple datasets, and I want to access the fields of a particular dataset from custom code. The problem is that the name of the dataset to use is only calculated during the execution of the custom code.
Allow me to illustrate:
public function Test(data as DataSets, fieldName as string)dim datasetName as string = CalculateDatasetName()dim ds as Dataset = data(datasetName)' now here I want to get the fieldName out of the dataset.' in the report, I would do something like
' First(Fields!fieldName.Value, datasetName)' but in custom code, this obviously doesn't work.end function
I've been looking for a way to accomplish this, but it seems you cannot get data from a dataset through custom code (there is only the commandtext property). Also it is not possible to pass the Fields collection as a parameter, as I do not know the dataset name when in the report designer.
I hope my problem description is clear.
Does anyone know how to solve this issue?
Thanks,
Phil
I need to count the records in a table with a datetime field equal to system datetime. It looks like it is trying to match time also since time is in field too. I just want to match date only. My sql is below. Does anyone have some suggestions on how to handle this?
Date from code
DateTime todaydt = DateTime.Now;
Table Data format
[cal_str_tm] [datetime] not null,
Data in cal_str_tm field - 3/27/2008 9:43:16 PM
Thanks,
Ron
ALTER PROCEDURE SP_DpCount
(
@todaydt datetime
)
AS
SELECT @total = COUNT(cal_int_id)
from dpcalldtl
where (cal_callstat != 'COMPLETED' and cal_str_tm = @todaydt)
RETURN
Hellois there a variable that is available to me that contains the numberof rows contained in a dataset return from a database call?
have a class that runs a stored proc and returns a dataset/resultsetlooking to simply assign an integer this value if it is possible
i'm using (learning) vb.net and sql server
thanks in advance
I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.
Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.
When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.
I've tried giving administrative permissions to the ASPNET worker process user to no avail.
I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.
I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.
Anybody got any idea on how to go about this?
My application create quite a lot log file to submit to the server under WiFi coverage to monitor the application. (log is just txt file). as time goes on, submitsql method throw OutOfMemoryException. I agree my log file is quite big, 80K each and 6 log files to submit.
My log submitter is an instance object. I also tried static object but it doesn't help either.
I do dispose my rda object when it is out.
Is any way I can fix this?
I have been getting "System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown" errors, that in turn cycles the SSRS server. I have SSRS 2005, SQL 2005, Win2003 STD, and 4GB RAM on box. We discovered this issue by running 5-10 big reports and Report-Builder-Model queries. If we stay on "normal workload" the system runs fine.
I found KB articles and TechNet articles that say increase below settings from default, which above 100% will use PageFile (virtual memory) for possible resolution.
Does anyone know if my "new settings" are right (based on how I read the TechNet articles)?
Original/Default
-=-=-=-=-=-=-=-=-=-=-=-
MemoryLimit = 60
MaximumMemoryLimit = 80
New Value
-=-=-=-=-=-=-=-=-=-=-=-
MemoryLimit = 120
MaximumMemoryLimit = 160
Any other suggestions on what to adjust before I have to invest in Win2003 Advanced and more GBs?
Hi,
I'm getting always an OutOfMemoryException after my program runs for a while. It's a smartphone application running on .NET 2.0 CF and using the SQL Server Mobile 2005. Sometimes it's even not a OutOfMemoryException but a SqlCeException. But it's always when the phone is having little free memory left.
I have also created a few custom controls (I even implemented the Disposable interface) to dispose all resources (Fonts, Bitmaps). I'm using all Pens and Brushes always in "using" blocks, to have them disposed immediately after having used them. The program does not have any static objects or lists of objects that are hold in memory for a long time.
I really don't understand why I get this exception. I see it always happen after doing a lot queries on the SQL Server Mobile. Is this tool using a lot memory? Can I flush somehow the memory of the SQL Server? Should I run also manually sometimes the GarbageCollector? Does this thing not run automatically?
Can anybody, perhaps of MSFT help me? I'm also willing to share the full source code with MSFT, if required.
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False
'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i
' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
I have created a windows library control that accesses a local sql database
I tried the following strings for connecting
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Trusted_Connection = true"
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Integrated Security=SSPI"
I am not running the webpage in a virtual directory but in
C:Inetpubwwwrootusercontrol
and I have a simple index.html that tries to read from an sql db but throws
the error
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection,
etc etc
The action that failed was:
Demand
The type of the first permission that failed was:
System.Data.SqlClient.SqlClientPermission
The Zone of the assembly that failed was:
Trusted
I looked into the .net config utility but it says unrestricted and I tried adding it to the trusted internet zones in ie options security
I think that a windows form connecting to a sql database running in a webpage should be simple
to configure what am I missing?