I am building a web app using VS2005 and SQL 2005 I would like to use the Caching Application Block to cache objects from my BLL. I was wondering if there is a way of utilizing the build in SQLDependency in SQL 2005 with the Caching Application Block??? Does anybody have tried this, are there any samples on the web???
Thanks,
Newbie
Hello, I have caching enabled in my application and I am using SqlCacheDependency to cache my tables. Well, the cache works fine but when the table is updated the information in my cache does not update. I DO have the broker service started and running but not sure what else i am missing. I am using sql server 2005.
View 1 Replies View RelatedHey, I can't enable caching on my local computer..I use daspnet_regsql -S "localhostSQLEXPRESS" -E -d books -edand aspnet_regsql -S "AdminSQLEXPRESS" -E -d books -edto try to start but i get a login error...cannot open database "books" requested by login. The login failed. Login failed for user Computer2/Adminunable to connect to SQL database for cache dependency registrationI tested connection with vwd and sql express management and it connects so I don't know what i'm doing wrong since I followed the direction and I googled for answer but not coming up with anything that's helping me..anyone have this problem and know how to resolve so i can cache some data output on my project
View 2 Replies View RelatedProbably a dumb question, but if one is using SQL Server 2000 instead of 'SQL Express' what has to change here, in order to set up caching? What changes in the paths?
http://beta.asp.net/guidedtour2/s22.aspx
Today I was asked a question I couldn't answer [it happens all the time...more than I like to admit...] concerning the performance hit of the SQL LIKE Filter.
The question was, what is the performance hit you take when you, say filter the results of a gridview control, with a LIKE FILTER in a stored procedure in SQL SERVER 2005?
Would it be preferable to create a Dependency Cache of a DataSet or a DataView object and then perform some type of filtering operation on that?
Thanks in advance.
God Bless.
Doug
I needed to do lookup on tables with approx 1 million records (how else do I know if record already exists?).
View 7 Replies View RelatedHi,
Is there anyway I can impliment this in my application without installing it as a COM. My Web Hoster says they won't install it. The Data Access Application Block
Does anybody knows, from where can I download the latest version of DAAB for SQL CE 3.5?
I know that the Moblie Client Softvare Factory was included a DAAB, but it is outdated in some respect.
So any information will be greatly appreciated.
Gabor
I download the DAAB CF version. However, most of them use SQLCEClient to connect to the database. Is there any DAAB that use SQLClient?
One workaround that I made is I replaced all SQLCEClient objects with the SQLClient objects. I only test one method "ExecuteDataset" and it works so far. However, my question is "does my workaround a good solution?"
Thanks,
Hendra
I'm trying to use the Data Access Application block, and am having some issues with configuration. I am using it in a class library, and it seems that with v 3 you need to configure the DAAB first, making changes to the configuration file. However, in a class library, I do not seem to have the web.config or app.config file to change. So where do I need to store the configuration settings?
Thanks,
Paul
This is my code...
public partial class test : System.Web.UI.Page
{protected void Page_Load(object sender, EventArgs e)
{SqlDatabase database = (SqlDatabase)DatabaseFactory.CreateDatabase("ConnectionString");DbCommand command = database.GetSqlStringCommand("SELECT UserName FROM Users WHERE UserID = '6264'");
using (IDataReader reader = database.ExecuteReader(command))
{if (reader.Read())
{lblTest.Text = "test";
}
}
}
}
This is my error:
Server Error in '/' Application.
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.Source Error:
Line 21: //DbCommand command = database.GetStoredProcCommand("CorrUsers_GetByUserId_s");
Line 22: DbCommand command = database.GetSqlStringCommand("SELECT UserName FROM Users WHERE UserID = '6264'");
Line 23: database.ExecuteReader(command);
Line 24: using (IDataReader reader = database.ExecuteReader(command))
Line 25: {Source File: d:webwwwrootchacha078dev.cha.toplingo.com est.aspx.cs Line: 23 Stack Trace:
[InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.]
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +1261381
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
Microsoft.Practices.EnterpriseLibrary.Data.Database.GetNewOpenConnection() +195
Microsoft.Practices.EnterpriseLibrary.Data.Database.GetOpenConnection(Boolean disposeInnerConnection) +106
Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command) +62
test.Page_Load(Object sender, EventArgs e) in d:webwwwrootchacha078dev.cha.toplingo.com est.aspx.cs:23
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +33
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +47
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1436
Any help with this matter would be very much appreciated.
Thank you for your time.
Sincerely,
Jeffrey Pham
I have the following stored procedure to test scope of variables
alter proc updatePrereq
@pcntr int,
@pmax int
as
begin
[Code] ....
In the above script @i is declare in the if block only when the @pcntr value is 1. Assume the above stored procedure is called 5 times from this script
declare @z int
set @z = 1
declare @max int
set @max = 5
while @z <= @max
begin
exec dbo.updatePrereq @z, @max
set @z = @z + 1
end
go
As i said earlier `@i` variable exists only when `@pcntr` is `1`. Therefore when i call the stored procedure for the second time and so forth the control cannot enter the if block therefore @i variable wouldn't even exist. But the script prints the value in `@i` in each iteration, How comes this is possible should it throw an error saying `@i` variable does not exist when `@pcntr` values is greater than `1`?
Hi..I have a large database which powers an old ASP website, until I have time to migrate the old ASP pages over to .NET I was wondering if I am able to use any caching settings in SQL Express 2005??What I mean is, can I long in to SQL using the management interface - And set up some sort of caching for the databases I have in there? I wanted to cache the database for say 10 seconds.. As I get alot of hits on the site and this would help speed things up / save resources.Any tips / help appreciated...ThanksAlso I don't want to get into hacking around with the ASP code... As I am in the middle of migrating the pages, hence I was hoping this would be the quickest way if it was possible
View 2 Replies View RelatedHi,I have over 200 stored procedures and they have dependencies on each other (not necessarily a cyclic dependency).Is there a way in SQL Server 2005 that I can disable a dependency check while I am executing a script that creates those stored procedures, and then re-enable it after?Thanks.
View 2 Replies View RelatedI need to deploy some SSIS packages onto a remote Integration Server machine. Hence
I wrote a VS 2005 Setup with Custom Action (C# Installer class) which uses the
SSIS DTSX Dlls to deploy the Packages onto an Integration Server. I am shipping
all the necessary DLLs in the MSI itself, so that the MSI can run on any client
machine having just .NET 2.0 Framework installed.
When I run the Setup on a Machine having Integration Server, the Setup works
fine and the Packages are deployed.
However, if I try to run the MSI on a machine which just has the .NET 2.0
Framework installed, I get the following error.
SqlServer.DTS.Runtime.Application could not be created. Retrieving the COM
class factory for component with CLSID
{E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error.
80040154
I am not sure whether any of the DLLS are COM components. I believe all are
Managed DLLs.I believe SSIS 2005 looks like a pre-requisite but I don't need that.
Please help. I am posting this query here, as I believe ppl writing SSIS Packages may have faced this issue.
Do anybody know if Analysis Services 2005 allows cashing (storing) calculated members ?
There is a cube in Analysis Services 2000 with a calculated member based on some running sums. The performance of MDXs is too slow because running summs are being recalculated for each member of time dimension on each call.
I think that caching calculated members might help me and hope that this feature exists in Analysis Services 2005 .
Thanks!
I've heard that the Standard version supports the use of CommandNotification and that the Express version does not. What about the Workgroup edition?
The reason I'm asking is because there is a substantial monthly difference in cost between the two versions if I were to have my site hosted. I've been developing with MS SQL 2005 Express so obviously I have not tested this caching feature. Are command notifications reliable? On paper, it sounds impressive and extremely efficient especially if a site is to have heavy traffic and you can't afford the overhead of heavy database processing that is useless if the data is unchanged.
Does the Workgroup edition support automatic change notifications? Have automatic notifications been proven reliable and vastly more efficient than crude polling methods?
Thanks in advance.
Hello our organization has never had any problems getting users to connect to our SQL servers but we have grown a lot over the past year or two. Does SQL Server actually block connections if they go over the license limit? I don't have access to the servers to see the model used but I have been asked to look into this. I couldn't find any information on this so I decided to ask you guys. Thanks in advance.
View 4 Replies View RelatedI'm attempting to use RDA to synchronize a pocket pc emulator with SQL Server 2005 database by following tutorial http://msdn2.microsoft.com/en-us/library/aa454892.aspx, which uses AdvWorksMobile database that comes with SQL Server 2005.
When get to testing of "To test application features " ( step 4 of the merge replication setup lab ), I am receiving SQLCe Exception errors.
ORIGINALLY, I was able to deploy an application and view the local database on the emulator, but once I clicked "InitSinc" button, I received an error.
"Failure to connect to SQL Server with provided connection information. SQL Server does not" exist, access is denied because the SQL user is not a valid user on the SQL Server, or the password is incorrect.
Found this error listed in Server Agent Errors log http://technet.microsoft.com/en-us/library/ms172357.aspx, but it wasn't very helpful since it doesn't provide any suggestions on fixing the problems.
I am brand new to SQL CE Server , mobile programming and .NET framework in general so please bare with me
All the connections / security settings, etc that were set up were taken from a tutorial so I am not sure what SQLCeServer is complaining about. Reading up on the error on the web didn't help, it all pointed to making sure that the snapshot folder had correct permissions set up. I verified that MACHINENAMEUISR_MACHINENAME guest user had Read rights to the database so I don't think that piece is the problem. Otherwise ,Merge publication has been setup in Microsoft SQL Server Management Studio by following the tutorial.
I am trying to run this sync on my home pc so there should be no issues with any user permissions
I have IIS installed
I did NOT have SQL Server Agent running in management studio at this time
I am trying to run this tutorial on an XP machine, which is my regular home PC so I dont think there are any special networking settings to consider
I am able to hit http://localhost/AdvWorksMobile/sqlcesa30.dll from my pc OK, but when I try to hit it from a cradled emulator ( after replacing "localhost" with "MACHINE_NAME" ), Internet Explorer on the emulator gives me a "Cannot Connect With Current Settings" error message. This part wasn't part of tutorial but decided so not sure if I am supposed to be able to hit it from my emulator..so I am not sure if the fact that I can't hit it is related to the problem.
AFTER STARTING SQL SERVER AGENTin Microsoft SQL Server Management Studio, I killed the application, and after relaunching it, it hits "The Specified table does not exist [Vendor]" error. This time it doesn't even launch first - that is I dont even get to pressing "InitSinc" button. Debugger is showing that this error is hit on the following line in AdvWorksMobile.AddWorksDataSetTableAdapters
int returnValue = this.Adapter.Fill(dataTable);
I am not sure why the errors changed on me after starting SQL Server Agent.
What can I do to fix this connection problem?
Thank you so much for your help!
Irina
Hello All,
Greetings!!!
Due to certain constraints on my Table, I am not able to place unique Key constraint on it.
So I have take care about the uniqueness in my T-SQL code. I don't want to use the serializable transaction isolation level as it will result in frequent deadlocks.
I want some means by which one select will block other select.
Can this be done in SQL Server. I tried using TablockX along with holdlock as Table hint, but still the Selects does not block other select.
Will appreciate if you can resolve this issue
Thanks in Advance,
Mitesh Shah
MCTS- SQL Server 2005
I have an asp.net application. This application provide consolidated information to the user using Analysis Services 2005. Within it, one aspx page uses MDX query to retrieve the information from the cube. However, we are unable to access AS 2005 because of authentication issues. What would be the easier to implement and deploy scenario?
How can I access AS 2005 from an ASP.NET application? Is there any feature to use?
We have an an application that was written using OLE DB (ADO) against a SQL 2000 Server that uses an Application role to give rights to the database objects. It connects, calls sp_setapprole and goes on. If the database needs to LOCK a record, it is creating a new ADO Connection and instantiating the Approle again. This model has been working fine up til now.
Now we are installing a SQL 2005 server for the latest version of the product we are working on and are running into an error. The error is
Error: 18059, Severity: 20, State: 1.
The connection has been dropped because the principal that opened it subsequently assumed a new security context, and then tried to reset the connection under its impersonated security context. This scenario is not supported. See "Impersonation Overview" in Books Online.
It's happening when the second ADO Connection for locking a record is being created and the sp_setapprole is being executed.
One of my questions is what is the problem with executing the approle on a different connection? Our code has not changed, so obviously SQL 2005 is doing something different. The other is What can we do to correct this?
Is the resource pooling different? We had problems in the beginning with approles and figured out through research that we needed to add OLE DB Services=-2 to the connection string to turn off resource pooling.
Is there an extra step to using Approles in SQL 2005?
Any help would be greatly appreciated as we need to resolve this ASAP.
Thanks,
David
Hi All,I have an application that reads data from a very slow database link(like 10 seconds per call) though what I am looking for would be ofgeneric use for anyone who has long-running queries that arefrequently repeated.I would like to be able to cache the results of a query so that I donot have to re-execute that query if it is reissued. Ideally Ibelieve that this could be implemented by hiding the query inside aUDF and exposing the UDF through a view. The UDF could then "Checkthe cache" and only run the slow query if there wasn't a match (or ifthe match was too old). From what I understand the best way to dothis would be for the cache to be an extended stored procedure.Has anyone done or seen this? Has someone written a copy that Icould purchase? Does anyone care to offer their opinnion of how or ifthis could work?Thanks in Advance,Steven
View 4 Replies View RelatedImporting CVS to SQLServer 2005 in a web application:
I need to import CSV file to a table. This is a .Net 2.0 web application running in the WebServer which has .Net 2.0 installed. SQL Server 2005 is a database in another machine. What are my options? Would SSIS work in WebServer if no database installed in that machine.
Thanks
I am having a strange problem. I have an application that to connects to SQL Database in SQL Server 2005.
When I run the application with Visual Studio everything works fine but when I deploy the application on my web server and then to access the site .
I get an error : Login failed for user ''. The user is not associated with a trusted SQL Server connection.
For some reason the application is not passing the user name . I have checked the IIS and authentication is set to Integrated Windows Authentication .
My connection string is correct as well
<add name="xxxxx" connectionString="Data Source=xxxxxx;Initial Catalog=xxxx;Integrated Security=SSPI" providerName="System.Data.SqlClient"/>
I will appreciate any kind of assistance in this matter. Thanks
I am trying to call a view that is already in SQL Server 2005 from my ASP.NET 2.0 application. I get the following error message:
The request for procedure 'vwMyView failed because 'vwMyView' is a view object.
I thought I could call a view the same way that I call a stored procedure but I guess I was wrong. How can I fix this?
Here is some sample code too:
Dim Results As DataSetDim Cmd As IDbCommand
Dim Adapter As IDbDataAdapter Results = New DataSet
Cmd = DB.CreateCommandCmd.CommandText = "vwMyView"
Cmd.CommandType = CommandType.StoredProcedure
Adapter = DataSource.CreateDataAdapter
Adapter.SelectCommand = Cmd
DB.Open()
Cmd.Connection = DB
Adapter.Fill(Results)
Cmd.Dispose()
DB.Close()
Return Results
Hi
I can't connect to sql server 2005 from my asp.net web page. I seem to be able to connect to it when i run the application from within visual studio but i can't connect to it when i run the application from IIS.I am new to sql server 2005 express so please bear with me.The error message i get is: Cannot open database "Vets" requested by the login. The login failed.Login failed for user 'OSCAR2K4ASPNET'.I don't know where this user OSCAR2K4ASPNET came from but i looked in sql server 2005 and there he is. In the left hand pane I went to Security and then logins and found oscar2k4asp.net there. I went to the properties for this user and went to User Mappings. There was no tick next to my Vets database so I added one and ran the application from IIS. I also added the user to every single role in the vets database (including dbowner). I also went into the vets database itself and found the oscar/aspnet user and made sure he was added to every role.
I ran the application again. This time i got a lots of different errors. If the page was trying to run a select procedure I got the error user does not have select permission. If the page was running an SP i got the error or user does not execute permission
I have never had this problem before so I don't understand.Where did this user come from? Was he created automatically when i configured my database for membership and roles? How do i set up this user so he can access the databsae from IIS.
The only way I could get the application to run from IIS was to go to Security - Logins for sql server and add the oscar2k4asp.net user to the sysadmin role.
I find this very strange. Naturally i don't want to leave oscar2k4 in sysadmin.
Please help :) I can't upload my website until i fix this
Thanks, andrea
I am not sure if this is the right folder to post, but currently I'm considering using Flash as an interface, the application at www.mobik.com seems impressive, simple and sweet. But I'm wondering if I can build desktop application with flash and SQL2005 as backend for all the processing. at the same time, the flash shall be getting updates from a server. Possible?
View 4 Replies View RelatedHi,
I'd like to get the experts' advice on whether SQL server 2005 Express edition (SSE) is suitable for medium scale web applications.
I have looked through the reviews of SSE. From what I understand, its limitations over the MSSQL 2005 Standard are:
1. SSE limits database size to 4GB and memory to 1GB.
2. Support for only 1 CPU.
3. No analysis or reporting services.
4. No full text indexing
5. No SQL Agent.
I have a web application that is currently running on a shared web host with a shared MSSQL 2000 database. I'm thinking of shifting to a Virtual Private Server, where I can install SSE for free.
Currently my app has about 14,000 page views a month, and each of them pulls out data from the database. I don't use any analysis or reporting services, or full text indexing or SQL Agent (for now).
My questions:
1. Is 1GB memory sufficient for this type of application?
2. Is there any reason I should not be using SSE for my type of application?
Thanks in advance for the help.
Hi all,
My ASP application is running fast in SQL Server 2000. But when we shifted it to SQL Server 2005, it becomes very slow.
I have created the indexes also. There is lot of data in the tables.
Is there any option to build the indexes for previous data.
Please help....
-Shah.
Hello,I have an application that uses SQL Server express. I'd like to be able topackage the SQL Server Express set up file and deploy it with my applicationbut all the searching I've done only tells me how to deploy SQL ServerExpress manually (i.e. how to set it up yourself). I can script my set upto see if an instance of SQL Server Express already exists but if itdoesn't, I'd like to know the best way to deploy this on the target machineautomatically.Any help or direction would be greatly appreciated.Thanks!Rick
View 2 Replies View RelatedI have got a VB6 application where I update local SQL server table from MS Access table with code like that:
----------------------------------------------------------------------------------------------------------------
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM Table1 WHERE [Fld1]=" & v1, Cn, adOpenKeyset, adLockOptimistic
'check if exist if not exist create new
If rst.EOF Then
rst.AddNew
rst("Fld1") = v1
rst("Fld2") = v2
rst.Update
End If
rst.Close
----------------------------------------------------------------------------------------------------------------
On rst.Update I have got an error message that the server has to be added to syservers as linked with stored procedure sp_addlinked srever. Why?
Anyhow I executed the stored procedure then I've got error message 16933, "The cursor does not include the table being modified or the table is not updatable through the cursor."
I had to rewrite the code to "INSERT INTO (Fld1, Fld2..." and it works.
Is something wrong in SQL Server 2005 installation or any other reason?
Please help.
Hello,
Can I confirm whether pooling=false in the connection string is still required for SQL Server 2005 (Express Edition)?
Various google searches say pooling has to be turned off for SQL Server 2000, but I was just wondering whether it is still a limitation for SQL Server 2005
Thanks
John