At what throughput should I leave a connection open ?

I am used to writing applications that hit the database "every so often" and am happy with opening and closing the connection to SQL Server for each one.

I am now writing an application that monitors a table where rows are written to it by a 3rd party application at possibly several rows per second. My job is to "pickup" those rows, analyse the data and move them to different tables. This will be done with a timer which is currently set to tick every second.

My question is: At what stage should I start to think about keeping open a permanent connection to the database ?

1 row per second ?

100 rows per second ?

Any suggestions appreciated.

Steve.Can you use a trigger? This way you do not need to poll. Presuming SQL can be used for the analysis.|||Thanks Doug,

It's a good idea, however, the analysis is quite complex and I think it needs to be done within my business object. I'll certainly look into moving as much code as possible into a trigger though.

Your suggestion has given me the idea of using an insert trigger to let the business object know when to run its analysis code rather than using a timer. Why poll and hit the database when there are no rows there ?

Steve.

assembly for stored procedure

I was playing around with the CLR in writing assemblies for the sql server 2005 stored procedure. I guess the example i found was for the beta version

This line is from the beta but no longer works. Any ideas what will fix this. There is no longer GetCommand property.

SqlCommand cmd =SqlContext.GetCommand();

Example

publicpartialclassStoredProcedures{[Microsoft.SqlServer.Server.SqlProcedure] publicstaticvoid StoredProcedure1()

{

// Put your code here

SqlCommand cmd =SqlContext.GetCommand();

cmd.CommandText="select firstname + ' ' + lastname + as [name] from person.contact";

SqlDataReader rdr = cmd.ExecuteReader();

SqlPipe sp =SqlContext.GetPipe();

sp.Send(rdr);

}};

Hi,

yes that has changed quite a bit till SQL 2005's RTM. Something like

using(SqlConnection connection = new SqlConnection("context connection=true"))
{

connection.Open();
SqlCommand cmd=new SqlCommand("select firstname + ' ' + lastname + as [name] from person.contact",connection);

SqlContext.Pipe.ExecuteAndSend(cmd);

}

For more information:http://msdn2.microsoft.com/en-us/library/ms190790.aspx

ASP.NET and SQL Server Compact Edition

I am writing an application that is designed for the desktop, or small group (3 or less) desktops. The application is being deployed using the Cassini web server as a complete "embedded" solution. I wanted to use SQL Server Compact because of its small footprint and because of the synchronization capabilities with SQLServer. Also, it is very easy to deploy vs. SQL Server Express. The idea behind this APP is that someone might use it locally and then decide to subscribe to the service and then connect the same application to a web services application.

That being said, and with knowledge of Microsoft's stance on SQL Compact under ASP.NET, I have most everything working and it works great except for one thing. When I add a SQLDataSource to the page and set the connection string to my file and the DataProvider to System.Data.SqlServerCe and then bind a data grid to the SQLDataSource, it runs fine on my development machine, but when I deploy to my clean test machine, I get an error when the page loads saying:

Unable to find the requested .Net Framework Data Provider. It may not be installed.

I can create a dataset in code (in fact,in several other places I do) and it works fine. It is just the SQLDataSource(s) that are throwing this error. I have a copy of System.Data.SqlServerCe.dll in the bin folder and I have all of the compact DLL's in the bin folder as well. I can't figure out what I need to do. I also have a reference to the System.Data.SqlServerCe in the web.config file.

Can anyone help?

what does your connection string look like?

Based on my understanding, connection string for sql server and sql server mobile is different. But however, since you said everything works fine in your dev mchine and only has problem in your test machine, i think most probably there are some installation issues in your test machine. Make sure you have installed .net compact framwork and sql server compact verision successfully.

Hope my suggestion helps

ASP.Net 2.0 Application Connection to SQL Server 2005 - Setup Questions

All:

I am writing an Internet/Extranet based (ASP.Net 2.0) web application that uses SQL server 2005 as the database. I am using forms authentication on my web application. I am also storing the connection string to SQL server in my web config file. The conn string is encrypted using DPAPI with entropy. I currently have created a SQL login account on my SQL server for use by the web application. This is the user ID I am using in my conn string. The reason for this is because all persons using the application will NOT have a windows login.

Here is my question: The login I created currently has defaulted to the "dbo" role and therefore has "dbo" rights to the database. I want to setup up this login accountso that all it can do is execute stored procedures. I dont want this SQL login to be able to do anything else. In my application I am using stored procedures for ALL data access functions, via a data access layer in my application. Can someone guide me step by step as to how to setup this type of access for this SQL login.

Thanks,

Blue.

The dbo user/role rights cannot be changed at all. It is the intrinsic owner of all objects in sql server and has access to all of them, whether you want it or not.

What you need to do, is to create your own sql server user, something like "MyApplicationUser", that only has exec access toyour stored procedures.

Don't go messing around with the dbo user, you can mess up your sql server installation really fast.

|||

Yes that is what I have done. I created a SQL server user/login. The only issue is when I created this I did not assign to any roles except public. Currently I have NOT granted explicit permissions to this login, to any objects, yet it is still able to run the SP's. When I look at the login I created, it states that the default schema is "dbo", therefore it is giving this login way more access then it needs.

|||

Still need help, anyone have any ideas or help? Please!

|||

Hi,

You may create your own role and add your user to that role. Here's the articles for you to refer.

http://msdn2.microsoft.com/en-us/library/ms187936(SQL.90).aspx

http://msdn2.microsoft.com/en-us/library/ms173463(SQL.90).aspx

Thanks.