Showing posts with label connection. Show all posts
Showing posts with label connection. Show all posts

Wednesday, March 28, 2012

How do I lengthen the time out for SQL queries?

I'd like to make the connection time unlimited for a certain sql query to SQL Server from ASP.NET, how do I do this? I already tried setting "Connect Timeout=0" in the sql connection string, this doesn't work...

Please help.Have you tried setting the CommandTimeout property for the SqlCommand object?

SqlCommand.CommandTimeout = iSecondsForTimeout
|||Thanks, that was it.sql

How do i know whether the connection is open with the sqlserver database while work in the

How do i know whether the connection is open with the sqlserver database while work in the application?

You should always open a connection explicity so if the Open method does not fail, you can assume it is open. It's best to wrap your connections in using statements like the following.

public int GetUserIdFromUserName(string userName)
{
int userId = -1;
using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["knovoloCMS"].ConnectionString))
{
sqlConnection.Open();
try
{
String SqlDeleteString = @."
SELECT id FROM Users WHERE Username = @.Username
";
using (SqlCommand sqlCommand = new SqlCommand(SqlDeleteString, sqlConnection))
{
sqlCommand.Parameters.Add("@.Username", SqlDbType.VarChar).Value = userName;
userId = (int)sqlCommand.ExecuteScalar();
}
}
catch (Exception ee)
{

}
}
return userId;
}

|||

I generally use it like;

using (SqlConnection sqlConnection = newSqlConnection(ConfigurationManager.ConnectionStrings["knovoloCMS"].ConnectionString))

{

if (sqlConnection.state != open)

sqlConnection.open();

......

}

Wednesday, March 21, 2012

How do I give a user access to SQL Server?

Hi all

I tried using my administrator account to add SQLServer as an ODBC Connection, but for some reason when it tries authenticating it fails and the reason is that the user is not trusted to use that connection. Yet on my account i've created on my domain works perfectly.

I've added a new user in SQL Enterprise manager, but i still get that error.

How do I set the user to be trusted to use the connection?

Any and as many assists on this would be great.

Kr33

[edit] - Hmm, actually, I may be misunderstanding the question. I'm going to move this question into the Data Access forum which hopefully can provide better support:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1

Sung

|||its better here in the Sql server security section|||

there are two types of login in SQL server

1. the NT login which is used when your server is configured

to use "windows Only" authentication

2. and The SQL server login which is used when your server is configured

to used mixed mode authentication. example of which is the SA login. this is usefull

if your accepting user that not authenticated through windows such as linux user

To configure you server to be on mixed mode authentication. open SQL Enterprise manager

expand the treeview until you reach the desired server. Right click the server. clcik on properties

clcik on security. Then choose "sql server and windows" in the authentication option

note that you can use SA only when you turn on this option

|||Whats your connection string ? If you are not sure about changing the authentication type, you can have a look at my screencasts on my site which will show you how to administer your SQL Server for the change of the authentication mode.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
sql

Monday, March 19, 2012

How do I get rid of this problem

Hi, I did a web page for the intranet where I work using ASP and a database in SQL Server, the connection line is the following:
Set oConn=Server.CreateObject("ADODB.Connection")
oConn.Open "Provider=sqloledb;Server=zapopan02;database=data;U ser Id=sa"
The problem comes the first time I try to establish the connection after 5 min. (aprox) of inactivity.
I get the following error:
Microsoft OLE DB Provider for SQL Server error '80004005'
Timeout expired
Then, I refresh the page and it works fine (and keeps working until I stop using the page for a while)
Could someone pleaseee tell me how to get rid of this problem?
Thank in advance
-RichardGenerally some sort of problem finding the DSN. Could be lost in the global.asa file, or perhaps this is not a SYSTEM DSN.

May refer to this KBA (http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q169/4/70.asp&NoWebContent=1) about connection pooling FAQ.|||For those who might have the same problem that I did, here is the answer to my own question:
There is a parameter named Connect Timeout wich is 15 seconds by default, that means that if after 15 sec the connection has not been established, oledb returns an error.
So, I changed the connection line to the following:
oConn.Open " Provider=sqloledb;Server=zapopan02;database=data;U
ser Id=sa;Connection Timeout=30"
And it worked, but anyway, I dont feel so satisfied because the problem is not 100% solved, I dont get the error anymore and the page opens but its still slow the first time.
Thanks anyway
- Richard