How can I change the CommandTimeout value?

When I try to execute a query and after 30 seconds the program sends me error :

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Exception Detail: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Source error:

Line 550 Dim myDataSet as Dataset = New DataSet

Line 551 myDataSet = db.ExecuteDataSet(System.Data.CommanType.Text,NewSql) <== Error line

Line 552 i=myDataSet.Tables(0).Rows.Count

In my connection string I set the parameter "Connection Timeout" = 360 but it not works. ( In debug mode the value for db.GetConnection.ConnectionTimeout is the same(360) like the parameter timeout connection.

After many searchs I found the default value for CommandTimeout is 30 secs. Can I change this value ?

Any suggestion will be welcome.

I'm using FrameWork 1.1.

create a SqlCommand object and set the CommandTimeout on that.

Hope it helps

|||

Klaus,

Do you have an example or reference in order to get the code?

Thanks in advance,

Juan Carlos

|||

Ok. I found the example and the solution for my case is :

Dim myDataSet as Dataset = New DataSet

Dim cmd as DbCommandWrapper = db.GetSqlStringCommandWrapper(NewSql)

cmd.CommandTimeout = 180 (seconds) ==> 0 (zero) in order to wait for ever.

myDataSet = db.ExecuteDataSet(cmdl)

i=myDataSet.Tables(0).Rows.Count

If you want to review more of thishttp://msdn.microsoft.com/msdnmag/issues/05/08/DataPoints/

Klaus, I appreciate a lot your help.

Thanks

sql

How can I calculate the disk space which some records used in SQL 2005 database?

I execute the following SQL, and return 3 records, I want to know how many disk space the 3 records use, how can I do? thanks!

select * from myTable where username='Paul'

I'd like to use a temp table:

select * INTO tbl_tmp from myTable where username='Paul'

exec sp_spaceused tbl_tmp

drop table tbl_tmp

|||Many thanks! but is there a better way?|||

Or you have to sum date length for all fields:

SELECT [TotalDataSize(Bytes)]=SUM(DATALENGTH(col1)+DATALENGTH(col2)+DATALENGTH(username))
FROM myTable where username='Paul'

|||Sorry, I'm a beginner, what does '[TotalDataSize(Bytes)]' mean ?|||

And more, ifcol1 is null in some records, doesSELECT [TotalDataSize(Bytes)]=SUM(DATALENGTH(col1)+DATALENGTH(col2)+DATALENGTH(username))
FROM myTable where username='Paul' cause error?

Many thanks!

|||BTW, the datalength method is not very accurate. It will give you a kind of estimate, but it isn't even close to correct in many cases.

How a user can execute SQL code

I am currently using DTS to run a query that spits out a txt file of the
results - works great, BUT... this code will now need to be run on a fairly
regular basis (possibly each Fri or Saturday). Although I am familiar with
coding, I am not familiar with having users interface with this code. My
question is, how can I have a user have the ability from his/her desktop to
click a button in some program and run this code? The Query is to a Project
Server database extracting data to be imported elsewhere. I though of
Access, but I do not believe Access can access mdf. Any ideas of what I can
use (I do not have Oracle).create a DTS package and try to Execute the package
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Diane" wrote:

> I am currently using DTS to run a query that spits out a txt file of the
> results - works great, BUT... this code will now need to be run on a fairl
y
> regular basis (possibly each Fri or Saturday). Although I am familiar wit
h
> coding, I am not familiar with having users interface with this code. My
> question is, how can I have a user have the ability from his/her desktop t
o
> click a button in some program and run this code? The Query is to a Proje
ct
> Server database extracting data to be imported elsewhere. I though of
> Access, but I do not believe Access can access mdf. Any ideas of what I c
an
> use (I do not have Oracle).|||Since your existing DTS package is working fine, you might consider running
it on the client machine. There are a number of methods to do this. One is
to execute the package using DTSRUN or DTSRUNUI and include the command in a
BAT file for easy user execution. Similarly, you can execute the package
directly from a VBScript or Access application.

> I though of Access, but I do not believe Access can access mdf. Any ideas
> of what I can
> use (I do not have Oracle).
You don't access the SQL Server database files directly in Access. You can
either link needed tables from an ODBC data source or query directly using
ADO in VBA code.
If you don't want to use DTS, you can use virtually any scripting or
programming language that allows you to run a SQL query and create a text
file. The best choice depends on your user interface requirements and
expertise. With Access, you could develop a simple form for any needed
parameters that invokes VBA code to perform the export task.
Hope this helps.
Dan Guzman
SQL Server MVP
"Diane" <Diane@.discussions.microsoft.com> wrote in message
news:E799328F-CD41-49F4-AF04-100A1242F70B@.microsoft.com...
>I am currently using DTS to run a query that spits out a txt file of the
> results - works great, BUT... this code will now need to be run on a
> fairly
> regular basis (possibly each Fri or Saturday). Although I am familiar
> with
> coding, I am not familiar with having users interface with this code. My
> question is, how can I have a user have the ability from his/her desktop
> to
> click a button in some program and run this code? The Query is to a
> Project
> Server database extracting data to be imported elsewhere. I though of
> Access, but I do not believe Access can access mdf. Any ideas of what I
> can
> use (I do not have Oracle).|||Yes- I'm a moron ;-( It didn't dawn on me to just load client tools -
thanks!!!!
"Dan Guzman" wrote:

> Since your existing DTS package is working fine, you might consider runnin
g
> it on the client machine. There are a number of methods to do this. One
is
> to execute the package using DTSRUN or DTSRUNUI and include the command in
a
> BAT file for easy user execution. Similarly, you can execute the package
> directly from a VBScript or Access application.
>
> You don't access the SQL Server database files directly in Access. You ca
n
> either link needed tables from an ODBC data source or query directly using
> ADO in VBA code.
> If you don't want to use DTS, you can use virtually any scripting or
> programming language that allows you to run a SQL query and create a text
> file. The best choice depends on your user interface requirements and
> expertise. With Access, you could develop a simple form for any needed
> parameters that invokes VBA code to perform the export task.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Diane" <Diane@.discussions.microsoft.com> wrote in message
> news:E799328F-CD41-49F4-AF04-100A1242F70B@.microsoft.com...
>
>

hosting SSIS packages where?

After i create a SSIS package, where can i put it or store it in the SQL SERVER 2005 so that i can execute it later on?

You can execute dtsx file directly or use dtexec command

It's better using Agent Job,and define schedule

|||

You could stored (I'm not wrong) it in three different places:

In the SSIS File System

In MSDB

In FAT/NTFS

You can execute a SSIS from a job, .net framework 2.0 code, VbScript, with DTEXECUI, etc..

|||Hi,

The option to do this is available once you have built you're project and you have done this whilst the CreateDeploymentUtility is True. All you then have to do is go to the deployment folder (usually in the bin directory) and run the .SSISDeploymentManifest file. This will give you all deployment options including where to store your package.

This link gves some info on how to execute the packages programmatically:

http://msdn2.microsoft.com/en-us/library/ms403355.aspx

Hope this is of use to you.

Cheers,

Grant|||

Grant Swan wrote:

Hi,

The option to do this is available once you have built you're project and you have done this whilst the CreateDeploymentUtility is True. All you then have to do is go to the deployment folder (usually in the bin directory) and run the .SSISDeploymentManifest file. This will give you all deployment options including where to store your package.

This link gves some info on how to execute the packages programmatically:

http://msdn2.microsoft.com/en-us/library/ms403355.aspx

Hope this is of use to you.

Cheers,

Grant

I don't understand what you just said... You can just upload a package to SQL Server (MSDB, SSIS File System, etc...) and execute it there via a number of methods. Some of those methods require that you have Integration Services installed on your local computer.|||He informing the poster that he can use the deployment utility to deploy his packages, and it will show him the different options available.|||

As I'm still coming up to speed on SSIS and have only worked with it for a couple of months, I can only speak of my own first hand experience. Others may take other approaches that are easier/better/simpler, etc.

First, I find that developing SSIS packages in the Business Intelligence/Visual Studio environment seems to work better when using the Visual Studio project/solution approach with the packages stored on a local development machine's hard disk. During this develop/test period I usually select the option to encrypt the package(s) with user credentials.

Then, when I've completed my development and testing and I'm ready to run the packages from SQL Server Agent for instance, I use the Save Copy As functionality to save the individual .dtsx packages to SQL Server. This Save Copy As functionality gets a little tricky to find as the option is only available when the package is open and a designer is actively selected. I do this mainly to remove dependencies on a particular user's credentials or a particular password, etc. from the security mix that is involved in getting these packages to run correctly from SQL Server Agent, etc. While I am using the Save Copy As functionality, to save it to SQL Server rather than the file system, one of the options presented is to change the security model for the package from encrypt with user credentials to that of letting SQL Server manage the security thru roles and permissions functionality inherent is SQL Server.

This works well for me in a design,program, test, deploy environment as it lets me make clear delineations between packages at the various stages, etc.

Hope this helps.

Steve Wells

|||

I got an error

"An OLE DB error 0x80004005 (unable to complete login process due to delay in opening server connection) occurred while enumerating packages. A SQL statement was issued and failed."

The below is my config:

Package location : SQL server

Server : Computer Name

USERNAME: < sql server login >

PASSWORD: <sql server pass>

package path: <I cannot select!! I got the above error>