|
Thank you so much,it works!
but maybe a primary question!!:
how can I attach my database permanently to the server?
|
|
|
|
|
You're welcome. Easiest is using the Sql Management Studio, but it could also be done using TSQL;
sp_attach_db [ @dbname= ] 'dbname'
, [ @filename1= ] 'filename_n' [ ,...16 ]Source[^]
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
I am using a windows forms application to write data to a SQL 2008 database over a wireless network. (Scanning serial numbers)
While stepping through the code in debug mode, the Connection.Open seems to take relatively long.
Is it bad practice to open a connection when the application starts, to avoid delays caused by opening the connection each time a query is executed?
Will the connection stay open, or will SQL server drop it if it is unused for some time?
|
|
|
|
|
Richard.Berry100 wrote: Is it bad practice to open a connection when the application starts, to avoid delays caused by opening the connection each time a query is executed?
Yes. Opening/closing a connection takes very little time. There's a connection-pool that manages the connections.
Another advantage of closing your connection is that you'll be able to handle more client-connections. If your server can handle 25 clients simultaneous, how much would it be able to handle if they only connect when required?
Richard.Berry100 wrote: Will the connection stay open, or will SQL server drop it if it is unused for some time?
That'd be easy to test, wouldn't it? Write a console-app and tell us if it indeed stays open as it should; there's only a timeout on creating a connection and on executing statements, not on the lifetime of the connection. That doesn't mean that the server will guarantee an open connection. A single Windows-update could force a reboot and close the connection. Or the cleaning-lady, when she unplugs the server and plugs in her vacuum-cleaner 
--edit
Here's the link I was looking for; SQL Server Connection Pooling (ADO.NET)[^]
To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling. [...] It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
modified 20-Apr-13 6:23am.
|
|
|
|
|
|
Yes.
I instantiate and dispose the connection only once, but I open and close it for each operation.
|
|
|
|
|
Define your connection at the start, then for each operation open then close the connection once the operation is complete.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Richard.Berry100 wrote:
Is it bad practice to open a connection when the application starts, to avoid delays caused by opening the connection each time a query is executed?
Normal MS stuff uses a connection pool. The first time you create the connection it is opened. After that it gets it from the pool. It is already open and it stays open. It never closes while the application runs. That is the default behavior of the connection pool.
Your suggestion is doing nothing more than priming the pump to make it appear later that your application is more responsive.
So
1. There is nothing wrong with doing that.
2. It is unlikely to have any impact on the database server nor the architecture
3. It is a perfectly reasonable thing to do.
Richard.Berry100 wrote: or will SQL server drop it if it is unused for some time?
No SQL Server will not close idle connections. That itself is actually a problem because it means that if a client connection crashes (client fails or network fails) that the connection just remains there forever. One either must set up a process to clean them or at least look for them every once in a while.
|
|
|
|
|
It is still preferable to use Open to get the connection from the pool and Close to return it.
|
|
|
|
|
PIEBALDconsult wrote:
It is still preferable to use Open to get the
connection from the pool and Close to return it.
I would say it is the only way.
|
|
|
|
|
Yip, agreed, see the reply I posted to my own question with speed tests on various approaches, and reference to Eddy's link - Thanks!
|
|
|
|
|
I think the link Eddy posted puts a lot into perspective, in that closing the connection does not really kill it, it leaves it in the pool, and unless there is something in the connection that changes, like accessing a different database, or using a different User ID the same connection from that pool is used again.
I tried inserting 10000 records (on server on local machine) in three different ways.
The difference was only about 100ms
private void cmdInsert_Click(object sender, EventArgs e)
{
Stopwatch sw = new Stopwatch();
string cnStr = "Data Source=" + Server + ";Initial Catalog=" + Database + ";User Id=" + DBUser + "; Password=" + DBPass + ";";
string strSql = "Insert INTO dbo.SpeedTest (Mydata) VALUES ('Hello')";
sw.Start();
using (System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(cnStr))
{
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(strSql, cn);
for (int i = 0; i < 10000; i++)
{
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
}
sw.Stop();
this.txtResults.Text = sw.ElapsedMilliseconds.ToString();
}
private void cmdInsert_Click(object sender, EventArgs e)
{
Stopwatch sw = new Stopwatch();
string cnStr = "Data Source=" + Server + ";Initial Catalog=" + Database + ";User Id=" + DBUser + "; Password=" + DBPass + ";";
string strSql = "Insert INTO dbo.SpeedTest (Mydata) VALUES ('Hello')";
sw.Start();
using (System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(cnStr))
{
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(strSql, cn);
cn.Open();
for (int i = 0; i < 10000; i++)
{
cmd.ExecuteNonQuery();
}
cn.Close();
}
sw.Stop();
this.txtResults.Text = sw.ElapsedMilliseconds.ToString();
}
private void cmdInsert_Click(object sender, EventArgs e)
{
Stopwatch sw = new Stopwatch();
string cnStr = "Data Source=" + Server + ";Initial Catalog=" + Database + ";User Id=" + DBUser + "; Password=" + DBPass + ";";
string strSql = "Insert INTO dbo.SpeedTest (Mydata) VALUES ('Hello')";
sw.Start();
for (int i = 0; i < 10000; i++)
{
using (System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(cnStr))
{
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(strSql, cn);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
}
sw.Stop();
this.txtResults.Text = sw.ElapsedMilliseconds.ToString();
}
|
|
|
|
|
Hi all,
How do you recursively remove the last character from a string in Oracle e.g for string 'HPN12345', I would like to build the following:
HPN12345
HPN1234
HPN123
HPN12
HPN1
HPN
I know how to do this in Oracle 11 using a recursive common table expression (CTE), but I need to implement this in Oracle 10g (without using a user function).
Thanks
|
|
|
|
|
A while loop based on the length would work.
|
|
|
|
|
Try this one... Change value of MIN_DEPTH and CHAR_STR as required.
SELECT SUBSTR(A.CHAR_STR, 0, LENGTH(A.CHAR_STR) - LEVEL + 1) OUTPUT
FROM (SELECT 'HPN12345' CHAR_STR, 3 MIN_DEPTH FROM DUAL) A
CONNECT BY LENGTH(A.CHAR_STR) - LEVEL + 1 >= A.MIN_DEPTH;
Thanks & Regards,
Niral Soni
|
|
|
|
|
Thanks Niral.. Your code was very helpful. It helped point me in the right direction.
Much appreciated!
|
|
|
|
|
i have installed windows application in sql server 2008,it has been installed but when
i click the icon in desktop error getting is [DBNETLIB][CONNECTION OPEN(connect()] SPECIFIED SQL SERVER NOT FOUND.How can i over come this error.
Ramesh
|
|
|
|
|
I doubt you have installed a windows application into SQL Server 2008.
Member 9999112 wrote: How can i over come this error.
By actually providing real information.
First this site is for the people who create applications not those who use them. So if you have an application that you got from somewhere then you need to go where you got it and ask there. This presumes of course that you already read all of the documentation that comes with the application.
But if this is an application that your wrote yourself then you need to provide some more detail about the code that is failing.
|
|
|
|
|
Hi All,
I am getting conversion error when i try to execute the following query... please help i need this scenario to be fixed for another business logic
Conversion failed when converting the varchar value '12r' to data type int.
declare @t table
(
val varchar(10)
)
insert into @t
select '1' union
select '2' union
select '3' union
select '4' union
select 'et' union
select '4' union
select '6' union
select '7' union
select '8' union
select '9' union
select '12r'
select * from @t where cast(val as int) > 3
|
|
|
|
|
12r cannot be converted because it is not a number.
Use the best guess
|
|
|
|
|
Try this:
select *
from @t
where
cast((case when isnumeric(val) = 1 then val else 0 end) as int) > 3
Edit -this will turn the 12r into a zero as it is not a valid integer.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
This question really does ask for a disparaging response.
If you can't work out that declaring a field of type int and trying to stuff text into it is going to fail and then cannot understand the very clear error message you should stop now and get an education.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Perhaps JavaScript could cast '12r' into an int with a value of 12, but many other programming/scripting languages won't do so.
|
|
|
|
|
Bernhard Hiller wrote: JavaScript could cast '12r' into an int
It does, how extraordinary, no wonder I loathe the script language.
There is certain crappy banking software that will allow the traders to enter 12.65m or 328k as a trade values, WTF!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: There is certain crappy banking software that will allow the traders to enter 12.65m or 328k as a trade values, WTF!
Considering how close K and M are on a qwerty keyboard 
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
