Connection.CommandTimeout

Running VB6 with SQL Server 7, opening a connection and setting this
parameter by Conn.CommandTimeout does not always seem to force a
command (stored procedure) executed through Conn.Execute to timeout
after the specified period. Is anybody able to suggest what may be
going on here.

Thankspeter.dawson@.kingsch.nhs.uk (Petros) wrote in message news:<478ba33e.0309300533.2e31f9f7@.posting.google.com>...
> Running VB6 with SQL Server 7, opening a connection and setting this
> parameter by Conn.CommandTimeout does not always seem to force a
> command (stored procedure) executed through Conn.Execute to timeout
> after the specified period. Is anybody able to suggest what may be
> going on here.
> Thanks

CommandTimeout starts when the database server has accepted the
command and ends when the database server returns the first record. If
the server or network is busy (or not answering at all), this setting
won't help you to get control again.|||Petros (peter.dawson@.kingsch.nhs.uk) writes:
> Running VB6 with SQL Server 7, opening a connection and setting this
> parameter by Conn.CommandTimeout does not always seem to force a
> command (stored procedure) executed through Conn.Execute to timeout
> after the specified period. Is anybody able to suggest what may be
> going on here.

If you are also running queries from the command object, you need
to see the .CommandTimeout for that object too; the value is not
inherited from the connection object.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Connection string with sa password

My application designed with VB6.0 is connected to MS SQL Server 7.0.
Server has password for sa login. The VB code for connection is next:
Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection
Conn.ConnectionTimeout = 30
Conn.ConnectionString = Provider=SQLOLEDB.1;Persist Security
Info=False;User ID=sa;Initial Catalog=master;Data
Source=server;Password=mypassword;
Conn.Open
Connection performs successfully but after that Conn.ConnectionStrring
value has changed and equals
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial
Catalog=master;Data Source=retiv;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;Workstation ID=USER;Use Encryption for
Data=False;Tag with column collation when possible=False
There is no any information about sa password which is not blank.
Connection object Conn is a global object in my application which is
used after, so I get a error
Login failed for user sa
How can I save my sa password in connection string?First of all, you shouldn't be using 'sa' to connect to SQL Server, from
your applications. This is a security threat, as 'sa' happens to be a
powerful login.
Then, you should be either using a DSN, or store your connection string
somewhere (Global.asa may be?), so that it is there at a specific location,
and that it never changes.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Vadim Bulat" <bulat@.asuxxivek.spb.ru> wrote in message
news:7e1d1f98.0407150731.78249213@.posting.google.com...
> My application designed with VB6.0 is connected to MS SQL Server 7.0.
> Server has password for sa login. The VB code for connection is next:
> Dim Conn As ADODB.Connection
> Set Conn = New ADODB.Connection
> Conn.ConnectionTimeout = 30
> Conn.ConnectionString = Provider=SQLOLEDB.1;Persist Security
> Info=False;User ID=sa;Initial Catalog=master;Data
> Source=server;Password=mypassword;
> Conn.Open
> Connection performs successfully but after that Conn.ConnectionStrring
> value has changed and equals
> Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial
> Catalog=master;Data Source=retiv;Use Procedure for Prepare=1;Auto
> Translate=True;Packet Size=4096;Workstation ID=USER;Use Encryption for
> Data=False;Tag with column collation when possible=False
> There is no any information about sa password which is not blank.
> Connection object Conn is a global object in my application which is
> used after, so I get a error
> Login failed for user sa
> How can I save my sa password in connection string?

Connection string with sa password

My application designed with VB6.0 is connected to MS SQL Server 7.0.
Server has password for sa login. The VB code for connection is next:
Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection
Conn.ConnectionTimeout = 30
Conn.ConnectionString = Provider=SQLOLEDB.1;Persist Security
Info=False;User ID=sa;Initial Catalog=master;Data
Source=server;Password=mypassword;
Conn.Open
Connection performs successfully but after that Conn.ConnectionStrring
value has changed and equals
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial
Catalog=master;Data Source=retiv;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;Workstation ID=USER;Use Encryption for
Data=False;Tag with column collation when possible=False
There is no any information about sa password which is not blank.
Connection object Conn is a global object in my application which is
used after, so I get a error
Login failed for user sa
How can I save my sa password in connection string?
First of all, you shouldn't be using 'sa' to connect to SQL Server, from
your applications. This is a security threat, as 'sa' happens to be a
powerful login.
Then, you should be either using a DSN, or store your connection string
somewhere (Global.asa may be?), so that it is there at a specific location,
and that it never changes.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Vadim Bulat" <bulat@.asuxxivek.spb.ru> wrote in message
news:7e1d1f98.0407150731.78249213@.posting.google.c om...
> My application designed with VB6.0 is connected to MS SQL Server 7.0.
> Server has password for sa login. The VB code for connection is next:
> Dim Conn As ADODB.Connection
> Set Conn = New ADODB.Connection
> Conn.ConnectionTimeout = 30
> Conn.ConnectionString = Provider=SQLOLEDB.1;Persist Security
> Info=False;User ID=sa;Initial Catalog=master;Data
> Source=server;Password=mypassword;
> Conn.Open
> Connection performs successfully but after that Conn.ConnectionStrring
> value has changed and equals
> Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial
> Catalog=master;Data Source=retiv;Use Procedure for Prepare=1;Auto
> Translate=True;Packet Size=4096;Workstation ID=USER;Use Encryption for
> Data=False;Tag with column collation when possible=False
> There is no any information about sa password which is not blank.
> Connection object Conn is a global object in my application which is
> used after, so I get a error
> Login failed for user sa
> How can I save my sa password in connection string?

Connection string Problem

Hi!

I have a very silly problem here.

i am setting some variables here from c# code.

Everything works fine except for Conn string. I understand statements that works fine in SQL does not act same in C# because of string literals.

I need an extra qoute \"" ......."/" that would execute in C#

\""Data Source=SE413695\AASQL2005;Initial Catalog=TestDB;Integrated Security=SSPI;"\"

where as in c#

setting variable value as :

string scriptTaskConnectST = @."Data Source=SE413695\AASQL2005;Initial Catalog=TestDB;Integrated Security=SSPI;";

executing this through c# code

jobCommand = new SqlCommand("xp_cmdshell 'dtexec /f \"" + path + "\" /Set \\package.Variables[User::ArchivePackageName].Properties[Value];\"" + name + "\" /Set \\package.Variables[User::ScriptTaskConnectST].Properties[Value];\"" + scriptTaskConnectST + "\"

When i check i resulting vaue i get this for scriptTaskConnectST as

/"......\"

\"Data Source=SE413695\AASQL2005;Initial Catalog=TestDB;Integrated Security=SSPI;\"

how should i format it to get \""......"\"

Thanks,

Jasm

if i'm not mistaken, you've already asked this question in a previous incarnation. if true, i believe that i've already suggested that you use sql server agent stored procedures to run your package. you can execute any stored procedure from c# provided that your code has the proper permissions.

does my suggestion not work for you? if so, please tell me why that is.

|||

I need to execute the packages through C# UI. Executing Throught the Agent is a secnd case now. But i found a work around method to set the connection string in my main package and set the value into the varaible which is used by all the pther sub packages. and it is working now.

I am already executing Stored proceedure through c# now for other implementations.

Thanks for your Suggestion.

Once i finish working through the UI i will try to execute throught the agent.

Jas

Connection String Problem

Hi guys,
I was trying to access the northwind database by using the following codes:

conn =new SqlConnection("data source=localhost;integrated security=true;initial catalog=Northwind");daCustomers =new SqlDataAdapter("select CustomerID, CompanyName from Customers", conn);
ds =new DataSet();
daCustomers.Fill(ds,"Customers");

When I preview the aspx, I would fail with the following error:

SQL Server does not exist or access denied.

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.Data.SqlClient.SqlException: SQL Server does not exist or access denied.
Source Error:

Line 44: daCustomers = new SqlDataAdapter("select CustomerID, CompanyName from Customers", conn);Line 45: ds = new DataSet();Line 46: daCustomers.Fill(ds, "Customers");Line 47: Line 48: //create the second DataTable

But now the strange thing is when I change the data source to (local):

conn =new SqlConnection("data source=(local);integrated security=true;initial catalog=Northwind");

It works! So my question is why would localhost fail while (local) works? Shouldn't these 2 be the same? Thanks in advance.

Silvertype:

It works! So my question is why would localhost fail while (local) works? Shouldn't these 2 be the same? Thanks in advance.

I always use (local) as the server name. As far as I understand it, ifyou want to use localhost then SQL Server needs to be configured to usea TCP/IP protocol, which isn't always the case.|||

I see. Btw, how do I configure SQL Server to use TCP/IP?

|||

Silvertype:

I see. Btw, how do I configure SQL Server to use TCP/IP?

As far as I know, it's via:Start -> Programs -> Microsoft SQL Server -> Client Network Utility|||Well, it seems like TCP/IP is already enabled for my sql server. However, localhost is still not working. Any idea?|||

Silvertype:

Well, it seems like TCP/IP is already enabled for my sql server. However, localhost is still not working. Any idea?

I do not know for sure. I always use (local).

Check out this blog post from Jon Galloway for some possible further help/explanation:[tip] localhost vs. (local) in SQL|||

Ok, thanks.

Connection String

Can someone please tell me why my connection below is not working?
using System.Data.OleDb;
OleDbConnection conn = new OleDbConnection("Provider=SqlOleDb;Data Source=BLOOD;Initial Catalog=Test;");
//Tried Above as ...;Initial Catalog= Test")
conn.Open();You need to install the database engine software (SQL Server, based on your connection string), and properly configure the server and the database before you can connect to them.

Once you get your server setup you may get error messages. Those would help us a lot in futher diagnosing your problem(s)!

-PatP

Connection problem

I have connection to MSSQL server.
When I read the data from the database, I close the connection (conn.Close() and conn.Dispose()).
After several page loads, it gets an error:
Database error. 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.
I found the way to fix this by compiling the webapp in release, butagain, after several page loads problem still occurs. Then I have tocompile it in debug and over and over again.
This is somehow OK if Iam on local so I can do the compile, but if Iam on the net (my web page) I cant do this.
I realised that when I make a change to a connection string (connectionstring is in appSettings in web.config) I don't get the above error(just like I copiled the project).
Can I some how prevent this error to occur?
Can I use Pooling=false in connection string to solve the problem?
Hi there,
to me it seems that your application is not closing SQL connections properly... I will advie to go over your code again..
In some cases you may have a function which is returning values fromdatabase if you return a value even before you close the connection,your SQL connection will still remain active... this is just a smallpointer for you to get started.
Cheers!
|||Thanks for the reply.
I will take a look at the code, but Iam convinced that I have closed the connection every time I use it.
About Pooling=false in ConnectionString. I tried to load a page inlocalhost many time in a roud, but nothing happens, no error, butstill, I want to use the ConnectionString without Pooling=false.
As I said, I will take a look at the code again.
Thanks mate

|||I have put the Pooling=false in ConnectionString and error doesen't appear anymore until I see where the problem.