date and time diffs: net

Showing posts with label net. Show all posts

Thursday, March 29, 2012

DataType Money

Please i need to display the money column in DataBase in an asp.net page but i get something like this 786.0000 how can i format it so that i get something like 786.00

Thanx

That kind of formatting is best done at the application level.|||

You could use the ToString("c") to display as currency on your page.

Double TotalCost = 786.0000;

lblCost.Text = TotalCost.ToString("c");

Your label should now be set to $786.00

|||

If you are binding it, you can use <%# Bind("YourColumnName","{0:c}") %> and will display $786.00
If you dont want to display the currency, repace c with your own format like #0.00 at it should display 786.00

Datatype

I am using Visual Studio .NET 2003 with SQL Server 2000. I am trying to
insert the date and time into a SQL database by using hour(now). I am
having a hard time trying to figure out which datatype to use in SQL to
store this value. I have tried using datetime, char, nchar, text and
nothing seems to work. Anyone have any ideas? Thanks!

Regards, :)

Christopher BowenDo you mean that you want to store a time without a date? This isn't
possible in MSSQL, since there is only a single datetime data type.

http://www.aspfaq.com/show.asp?id=2206
http://www.karaszi.com/sqlserver/info_datetime.asp

Simon|||Use a DATETIME or SMALLDATETIME column.

INSERT INTO YourTable (dt_col) VALUES (CURRENT_TIMESTAMP)

--
David Portas
SQL Server MVP
--|||Christopher,

the function call Hour(Now) will return the current hour, which is an
integer. I would expect this information to be of little use. However,
if you want to store this in an SQL-Server database, then a column of
type tinyint would suffice.

HTH,
Gert-Jan

c_bowen@.earthlink.net wrote:
> I am using Visual Studio .NET 2003 with SQL Server 2000. I am trying to
> insert the date and time into a SQL database by using hour(now). I am
> having a hard time trying to figure out which datatype to use in SQL to
> store this value. I have tried using datetime, char, nchar, text and
> nothing seems to work. Anyone have any ideas? Thanks!
> Regards, :)
> Christopher Bowen|||It would probably be helpful to see your code and it's not 100% clear what
you're trying to do. I'm guessing hour(now) is a .NET function? Does it
simply return the number of the current hour? That would be some sort of
INT, which would be a datatype mismatch with the datatypes you say you've
tried. Your note mentioned "insert the date and time", which wouldn't
simply be the current hour, anyway.

When I want to insert the date and time, I usually use SQL Server's
getdate() function to supply the value. A T-SQL example would be:

create table foo (col1 char(10),col2 datetime)
go
insert foo values ('abc',getdate())
go
select * from foo
go

[results]
(1 row(s) affected)

col1 col2
---- ----------------
abc 2005-02-25 15:16:38.367

(1 row(s) affected)

Use the datetime datatype if you can. In my experience, using character or
numeric types for storing dates and times usually ends up in grief.

By the way, I usually set things up so that SQL Server is responsible for
supplying the time, rather than the application. That way, if the various
workstation's or web server's clocks are a little bit off, the time values
of rows inserted/updated will still be synchronized across your
applications.

<c_bowen@.earthlink.net> wrote in message
news:1109141117.808195.36560@.l41g2000cwc.googlegro ups.com...
> I am using Visual Studio .NET 2003 with SQL Server 2000. I am trying to
> insert the date and time into a SQL database by using hour(now). I am
> having a hard time trying to figure out which datatype to use in SQL to
> store this value. I have tried using datetime, char, nchar, text and
> nothing seems to work. Anyone have any ideas? Thanks!
> Regards, :)
> Christopher Bowensql

Tuesday, March 27, 2012

Datasource Webservice

I test Webservices with the SSRS 2005 and do not get ahead.

Connectionstring: http://www.webservicex.net/WeatherForecast.asmx
Query String: <Query><SoapAction>http://www.webservicex.net/GetWeatherByPlaceName</SoapAction></Query>

I get an error. What is wrong?
Additionally I would like to use parameters. For example Houston, Dallas, New York.

Can someone send an example with this Web service to me?

Willfried Tongue Tied

I just tried this using IE and got:
"The underlying connection was closed: The server committed an HTTP protocol violation."

You should contact the author of the web service to request a fix.

-Lukasz

Sunday, March 25, 2012

Datasets, searching, complex queries.

Alright just starting out in ASP.NET and it's making my head spin, but I think I'm getting it.

My schema in brief : Images, Categories, and a table in the middle so we can have a many-to-many relationship between images and categories. My issue is searching. I can search by keyword, and limit it to categories. So, in pseudo sql..

SELECT ... WHERE keyword LIKE '%test%' and category_id in (1,5,20,66);

I made a variable for the keyword no problem. But how can I get this dynamic list? Or, is there another way about going this problem?

Where will you put your query in? StoredProcedure or Web? I think my two posts help you:

http://forums.asp.net/t/1162993.aspx

http://forums.asp.net/t/1158548.aspx

|||

I've been informed by the big shark guy that I recommend D-SQL a lot, but you are asking for it so here goes:

First off... beware SQL injection. Strip out ' and - from your search text. Do without.

CREATE PROCEDURE sp_search @.keyword varchar(50),
@.categorylist varchar(150) as

declare @.sql varchar(max)

set @.sql = 'SELECT ... WHERE keyword LIKE ''%' + @.keyword + '%'' and category_id in (' + @.categorylist + ')' -- BTW those are double single quotes, not double quotes

EXEC sp_executesql @.sql, N'@.keyword varchar(50), @.categorylist varchar(150)'

then call sp_search in your code, adding the keyword and category list strings as parameters. Does that help?

|||

che : i'll read those links now

Charles : Interesting. Will I be able to accomplish something like this with Oracle? That looks like SQL Server to me?

|||

Oh. Most folks here are SQL Server. I'm sure you can do dynamic SQL in oracle though - my boss who taught me D-SQL co-authored the Oracle Designer/2000 Handbook. The sp_executesql is a SQL serber built-in sp, and I don't know what the equivalent is in oracle:

http://lbdwww.epfl.ch/f/teaching/courses/oracle8i/server.815/a68022/dynsql.htm

|||

This is a little disappointing. Dynamic SQL seems really overkill for such a trivial problem.

Perhaps I should look at not using strongly typed Datasets? Build one on the fly? Would that be different/better?

|||

Many of the problems were have these days tend to be ones caused by the complexity of the frameworks we are working in. For example, to write a web page, I have to know C# (or VB), ASP, HTML, SQL, and .Net, and how they all interrelate. For one page. It used to be that all you needed was a front end and a database. Oh well, we get a lot in terms of scalability and functionality out of this stuff too - the complexity is market driven.

All philosophy aside, it seems to me that most searches are done with D-Sql. If you really want to make a mountain out of a molehill, you could create a search object that safely builds your query in C# and then access it from your search front end. The advantage of that would be that you could get a lot of re-use in the future. Searching is a non-trivial issue. The volume of work that has gone into searching makes it difficult to mine for simple one-off solutions.

sql

DataSet Xml DateTime incompatible with Sql 2005

All I'm trying to do is simply write out a DataSet in .net 2.0 with
..WriteXml(), then read it into sql 2005 with OPENXML.
WriteXml() produces dates in the format "2004-07-14T23:50:13-07:00"
Yet it appears sql 2005 doesn't support that format. Is that possible?
Running the below code gives:
"Conversion failed when converting datetime from character string."
declare @.Xml varchar(max)
declare @.iRet int
declare @.hDoc int
set @.Xml = '<ROOT>
<Favorite>
<Directory>\Astronomy\Aurora\</Directory>
<Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
<Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
<SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
</Favorite>
</ROOT>
'
exec @.iRet = sp_xml_preparedocument @.hDoc OUTPUT, @.Xml
select SaveDate
from openxml(@.hDoc, N'/ROOT/Favorite', 2)
with Favorite
thanks-
Mike
You have a datetime value with a timezone which is not recognized with
OpenXML.
Try one of the following instead:
1. do not generate datetime values with timezones.
2. Use the nodes method (needs to explicitly code the table shape):
declare @.Xml xml
set @.Xml = '<ROOT>
<Favorite>
<Directory>\Astronomy\Aurora\</Directory>
<Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
<Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
<SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
</Favorite>
</ROOT>'
select R.Fav.value('xs:dateTime(SaveDate[1])', 'datetime') as SaveDate
from @.Xml.nodes('/ROOT/Favorite') R(Fav)
Note that you need to first cast it to xs:dateTime to normalize the value to
Z time and then cast it to datetime which will drop the timezone
altogether...
Season's Greetings
Michael
"Mike" <nospam@.dontemailme.com> wrote in message
news:esL2%23A4BGHA.3936@.TK2MSFTNGP12.phx.gbl...
> All I'm trying to do is simply write out a DataSet in .net 2.0 with
> .WriteXml(), then read it into sql 2005 with OPENXML.
> WriteXml() produces dates in the format "2004-07-14T23:50:13-07:00"
> Yet it appears sql 2005 doesn't support that format. Is that possible?
> Running the below code gives:
> "Conversion failed when converting datetime from character string."
> --
> declare @.Xml varchar(max)
> declare @.iRet int
> declare @.hDoc int
> set @.Xml = '<ROOT>
> <Favorite>
> <Directory>\Astronomy\Aurora\</Directory>
> <Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
> <Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
> <SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
> </Favorite>
> </ROOT>
> '
> exec @.iRet = sp_xml_preparedocument @.hDoc OUTPUT, @.Xml
> select SaveDate
> from openxml(@.hDoc, N'/ROOT/Favorite', 2)
> with Favorite
>
> thanks-
> Mike
|||Michael-
That still gave me a 'Conversion failed...' error but did get me on the
right track. Extracting as a string first then converting did the
trick.
selectconvert(datetime, R.Fav.value('xs:dateTime(SaveDate[1])',
'char(20)'),127) as SaveDate
from @.Xml.nodes('/ROOT/Favorite') R(Fav)
Thanks much for the quick response - it really helped.
Mike
|||Hmm. What version of SQL Server 2005 are you currently running?
This should work automatically without you having to do the string/datetime
yourself in the RTM version...
Best regards
Michael
<mhardy@.gmail.com> wrote in message
news:1135818951.156103.3160@.g49g2000cwa.googlegrou ps.com...
> Michael-
> That still gave me a 'Conversion failed...' error but did get me on the
> right track. Extracting as a string first then converting did the
> trick.
> select convert(datetime, R.Fav.value('xs:dateTime(SaveDate[1])',
> 'char(20)'),127) as SaveDate
> from @.Xml.nodes('/ROOT/Favorite') R(Fav)
> Thanks much for the quick response - it really helped.
> Mike
>

DataSet Xml DateTime incompatible with Sql 2005

All I'm trying to do is simply write out a DataSet in .net 2.0 with
.WriteXml(), then read it into sql 2005 with OPENXML.
WriteXml() produces dates in the format "2004-07-14T23:50:13-07:00"
Yet it appears sql 2005 doesn't support that format. Is that possible?
Running the below code gives:
"Conversion failed when converting datetime from character string."
declare @.Xml varchar(max)
declare @.iRet int
declare @.hDoc int
set @.Xml = '<ROOT>
<Favorite>
<Directory>\Astronomy\Aurora\</Directory>
<Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
<Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
<SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
</Favorite>
</ROOT>
'
exec @.iRet = sp_xml_preparedocument @.hDoc OUTPUT, @.Xml
select SaveDate
from openxml(@.hDoc, N'/ROOT/Favorite', 2)
with Favorite
thanks-
MikeYou have a datetime value with a timezone which is not recognized with
OpenXML.
Try one of the following instead:
1. do not generate datetime values with timezones.
2. Use the nodes method (needs to explicitly code the table shape):
declare @.Xml xml
set @.Xml = '<ROOT>
<Favorite>
<Directory>\Astronomy\Aurora\</Directory>
<Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
<Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
<SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
</Favorite>
</ROOT>'
select R.Fav.value('xs:dateTime(SaveDate[1])', 'datetime') as SaveDate
from @.Xml.nodes('/ROOT/Favorite') R(Fav)
Note that you need to first cast it to xs:dateTime to normalize the value to
Z time and then cast it to datetime which will drop the timezone
altogether...
Season's Greetings
Michael
"Mike" <nospam@.dontemailme.com> wrote in message
news:esL2%23A4BGHA.3936@.TK2MSFTNGP12.phx.gbl...
> All I'm trying to do is simply write out a DataSet in .net 2.0 with
> .WriteXml(), then read it into sql 2005 with OPENXML.
> WriteXml() produces dates in the format "2004-07-14T23:50:13-07:00"
> Yet it appears sql 2005 doesn't support that format. Is that possible?
> Running the below code gives:
> "Conversion failed when converting datetime from character string."
> --
> declare @.Xml varchar(max)
> declare @.iRet int
> declare @.hDoc int
> set @.Xml = '<ROOT>
> <Favorite>
> <Directory>\Astronomy\Aurora\</Directory>
> <Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
> <Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
> <SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
> </Favorite>
> </ROOT>
> '
> exec @.iRet = sp_xml_preparedocument @.hDoc OUTPUT, @.Xml
> select SaveDate
> from openxml(@.hDoc, N'/ROOT/Favorite', 2)
> with Favorite
>
> thanks-
> Mike|||Michael-
That still gave me a 'Conversion failed...' error but did get me on the
right track. Extracting as a string first then converting did the
trick.
select convert(datetime, R.Fav.value('xs:dateTime(SaveDate[1])',
'char(20)'),127) as SaveDate
from @.Xml.nodes('/ROOT/Favorite') R(Fav)
Thanks much for the quick response - it really helped.
Mike|||Hmm. What version of SQL Server 2005 are you currently running?
This should work automatically without you having to do the string/datetime
yourself in the RTM version...
Best regards
Michael
<mhardy@.gmail.com> wrote in message
news:1135818951.156103.3160@.g49g2000cwa.googlegroups.com...
> Michael-
> That still gave me a 'Conversion failed...' error but did get me on the
> right track. Extracting as a string first then converting did the
> trick.
> select convert(datetime, R.Fav.value('xs:dateTime(SaveDate[1])',
> 'char(20)'),127) as SaveDate
> from @.Xml.nodes('/ROOT/Favorite') R(Fav)
> Thanks much for the quick response - it really helped.
> Mike
>sql

DataSet works... DataTable doesnt... (ODBC)

I have a longstanding problem where Stored Procedures or complex T-SQL called from VB.NET will not populate a DataTable object, but will work fine with a DataSet. For example:

'oConn is defined elsewhere...
Dim sErr as String = ""
Dim dt As New DataTable
If Not oConn Is Nothing Then
Try
Dim sSQL as String = "select 1"
Dim oCommand As New OdbcDataAdapter(sSQL, oConn)
oCommand.Fill(dt)
Catch ex As Exception
sErr = "Database Error: " & ex.Message
Finally
sqlCloseConnection(oConn)
End Try
End If

this works fine and my dt DataTable object gets one row. However using this as the SQL:

Dim sSQL as String = "declare @.foo table(mycol integer);insert @.foo select 1;select mycol from @.foo;"

does not work. It executes with no errors, but the DataTable has no rows. Finally, if I replace the DataTable with:

Dim ds as DataSet

I can then get the data in ds.Tables(0) no problem.

So, if the results of the sql are a single result table being put at index 0 of a DataSet, why are they not being put in a single DataTable?

When a sql is a simple select statement it always works directly to a DataTable. Only when it's a SP or sql with some logic does it require the DataSet approach. This is a reporting utility so I need to standardize the code though the sql will be dynamic.

Any ideas?

Hello my friend,

I tried your code in my application and it worked for me, but I am using the SqlDataAdapter. Why are you using Odbc? Try using the Connection, Command, DataAdapter, etc classes from the System.Data.SqlClient namespace and I think it will work.

Kind regards

Scotty

Wednesday, March 21, 2012

dataset isn't showing

SQL/RS 2K, VS .Net 2003 - several single resultset/dataset reports working
just fine, finally got some multiple-resultset/dataset reports working very
well, too. today, however, i am creating a new one that isn't working, and i
can't yet determine why.
i have 5 different datasets so far, each are command type text, each field
presents the data just fine in the Data tab. I've created tables in the
Layout tab for each dataset, and dragged/dropped the fields into each
accordingly. When I go into the Preview tab, each dataset/table is visible
only intermittently' Meaning, I'm in Preview, and 3 of the five
datasets/tables are showing data, the other two are blank. Now, if i hit
refresh, only two are showing data... AND one of them is one that wasn't
showing data a moment ago.
I'm setting this thing up just like the three i did yesterday, which are
working just fine.
Can anybody provide any direction on this? It's very, very important, and I
do appreciate any assistance.
--
LynnWeird. I haven't seen this before. I suggest trying to deploy and see if
this is something only from the development environment.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:278379C7-06BF-4726-B3DA-18EE50B6EAFA@.microsoft.com...
> SQL/RS 2K, VS .Net 2003 - several single resultset/dataset reports working
> just fine, finally got some multiple-resultset/dataset reports working
> very
> well, too. today, however, i am creating a new one that isn't working,
> and i
> can't yet determine why.
> i have 5 different datasets so far, each are command type text, each field
> presents the data just fine in the Data tab. I've created tables in the
> Layout tab for each dataset, and dragged/dropped the fields into each
> accordingly. When I go into the Preview tab, each dataset/table is
> visible
> only intermittently' Meaning, I'm in Preview, and 3 of the five
> datasets/tables are showing data, the other two are blank. Now, if i hit
> refresh, only two are showing data... AND one of them is one that wasn't
> showing data a moment ago.
> I'm setting this thing up just like the three i did yesterday, which are
> working just fine.
> Can anybody provide any direction on this? It's very, very important, and
> I
> do appreciate any assistance.
> --
> Lynn

Dataset as source

Can we have an ADO.NET dataset as a datasource to a MS SQL Reporting service
report ? If so, how do we configure the report to do so ?
Regards,
Chak.here you are!
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/RSDSetEx3.asp
"Chakra" <r_chakravarthy@.hotmail.com> schrieb im Newsbeitrag
news:u5I51OloFHA.3552@.TK2MSFTNGP10.phx.gbl...
> Can we have an ADO.NET dataset as a datasource to a MS SQL Reporting
> service
> report ? If so, how do we configure the report to do so ?
> Regards,
> Chak.
>

Monday, March 19, 2012

DataReader source output help

I have configured my DataReader to use an ADO.net (ODBC) connectivity (entered Select * from AMPFM) in Sqlcommand and can see my database columns listed in the Advanced Editor / Column mappings window. My process needs to perform a straight column to column population from AMPFM table into my dbo.visitfinancials table. How do I point the output to the above table?

Add an OLE DB destination. Select the DataReader source, connect its green arrow to the OLE DB destination. Double click on the OLE DB destination and configure it for the appropriate connection manager (will have to click "New..." to create a new one) and table name. Be sure to use "Table or view - fast load" in the Data access mode drop down list.

Then select mappings on the left. Map your columns by dragging one to the other. When done, click OK. Run it. If your data types match, this is as easy as it gets.|||

That worked great, thanks Phil. Now of course, you knew my data types would not all match. I am getting the error "Column "ID" cannot convert between unicode and non-unicode string data types. I believe I came across one of your posts that stated to use a derived column to cast the field. My field (ID) is varchar (254) not null. Where would I enter this derived casting procecure?

|||In a derived column, add a new column.

This expression should do the trick.

(DT_STR,254,1252)[your_input_field]|||

Looks like I have ran into a snag. I navigated to DataReader Source / show advanced editor / Input and Output properties / Datareader Output / Output columns / clicked on the add column radio button and received the following error message:

Error at Data Flow task[DataReader Source[1]]: The component does not allow adding columns to this input or output. Additional information: Pipeline component has returned HRESULT error code 0xC0208019 from a method call. (Microsoft.SqlServer.DTSPipelineWrap)

Any ideas?

|||You need to use Derived Column transformation for that...it it an item in the toolbox in the data flow.|||

OK, I have added a derived column object from the toolbox, connected it to the datareader and then tried to connect it to my OLEDB data source but get an error saying there are no available inputs. I deleted the mapping for "ID" field between DataReader source and destination OLEDB and then tried to connect "ID" from derived column to the OLE DB data source adn get the same message. I feel like I'm getting close but not quite there yet. I need additiona assistance please.

|||You have the wrong OLE DB component. You have a source on there and you need the OLE DB DESTINATION component instead. It's toward the bottom of the list in the toolbox.|||

I believe I used the wrong terminology in my previous posts and confused the issue. I do have a DataReader source, Derived column control and OLEDB destination. Thanks.

|||The data reader source should be connected to the Derived Column, which should be connected to the OLEDB destination. Three boxes, two lines.

Datareader Query Timeout

Hello,

I am running a query via a ado.net data flow source. It works great for a small number of rows, but if I try to execute a long running query it times out with a communication error. I have looked through the doc and every property sheet that I can find in my package, but I can't find anywhere that a timeout is specified. Any help on finding this would be appriciated. The query is running against DB2 on z/os and I know it is timing out on the server side because I can watch the query run on z/os and it continues to run after SSIS gets the error.

Thanks!
HarryHave you checked in the connection manager? Specifically, on the "All" page?|||Yes, I have checked and there is nothing there. Please someone help Tongue Tied |||I'm looking at the Advanced Editor of the Datareader Destination and there is a ReadTimeout property. Is this not what you want?|||The error occurs on a datareader source and I can't find any timeout properties on that component?|||DataReaderSrc does not timeout, as long as the ADO.Net connection manager is still receiving data from the server, it will pull out all and pass them to its downstream dataflow components - I tried to read 27million rows (4GB) in DataReaderSrc(using HIS provider for DB2) and I did not see any problems reading out all rows.
The issue does not seem to me like a SSIS problem. What provider you were using? you only got the timeout, no other error info? And, just a wild guess, was deadlock possible when you ran your query -were there any other concurrent transactions accessing the same sources?
Thanks
Wenyang|||The DataReader source can and does time out. I tried to sort and read 26 million rows (8 GB) with no indexes from a SQL Server 2000 database and it failed with a timeout on the DataReader. Changing the timeout on the DataReader destination has no effect.

I can us an OLE DB Source with a command timeout, but then there is no way to tell it that a column is sorted so no merge join later in the process.

There's a hole in the bucket, dear Liza...

pjp|||

Thanks for the post, Preston. I'll appreciate if you can provide us more info on the followings. Thanks in advance.

>The DataReader source can and does time out. I tried to sort and read 26 million rows (8 GB) with no indexes from a SQL Server 2000 database and it failed with a timeout on the DataReader.
When did you get the timeout error - at design time or at execution time? DataReaderSrc adapter itself, based on the current design, has no timeout related property to expose the command timeout control over to the customers. Can you post here the full timeout error info you received so we can look into this?

>Changing the timeout on the DataReader destination has no effect.
Why the DataReaderDest is realted?

>I can us an OLE DB Source with a command timeout, but then there is no way to tell it that a column is sorted so no merge join later in the process.
Actually you can.
1) The easiest is to use Sort transform at OLEDBSrc downstream to sort on certain columns before leading the dataflow to MergeJoin.
2) Or, in advanced UI of OLEDBSrc, change the "IsSorted" property of the output to true, the "SortKeyPosition" of those sorted output columns to 1,2,3...respectively, then you can directly hook OleDbSrcs to merge join.

Wenyang

|||

Sorry for the delay in responding. I did not get notified of a response for some reason. Anyway, the error is at runtime. The command is "SELECT * FROM [BigTable] ORDER BY [SomeColumn]." It is easily fixed by adding an index to the table but the fact remains: a DataReader source will timeout.

BTW, I would not expect the DataReaderDest to be related; I made the comment because someone else had mentioned it.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Error: 0xC0047062 at Data Flow Task, DataReader Source 2 [8143]: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

at System.Data.SqlClient.SqlDataReader.get_MetaData()

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()

at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)

Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "DataReader Source 2" (8143) failed the pre-execute phase and returned error code 0x80131904.

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "DataReaderDest" (8965)" wrote 0 rows.

Task failed: Data Flow Task

Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

|||Preston, thanks for your detailed error information.
I've looked this over, currently it is by design that the customers are not allowed to change the timeout at DataReaderSrc, but you are right, that could cause DataReaderSrc to experience command timeout at certain special circumstances. I have logged a request on your behalf for a design change to expose a command timeout property to the users, hopefully it can be addressed in our SP1 release.
You mentioned about your work around for this in your post already, other work arounds may include relieve the workload at DataReaderSrc to its downstream components e.g. Aggregate,Sort, rather than doing everything in one query at DataReaderSrc
Thank you
Wenyang|||After days of misery I finally found this thread ... Thanks Preston and Wenyang for the accurate and descriptive information contained.

I experience a similar problem with my Source connection - the problem being it is to an Oracle database on the other side of the world. I normally expect poor connectivity and response times to this database and so I thought I'd set up an overnight SSIS process to download the relevant data to a local SQL Server database for analysis rather than trying to analyse remotely.

The problem is that I have a relatively complex query to retrieve only the pertinent information from Oracle, and so it is normallt 1-2 minutes from the time I send the query until I begin to receive results (depending on dataabse utilization and network performance). Since I cannot set the timeout property anywhere for the source (DataReaderSrc) I am not able to persue this solution at the moment. I am using a Config file for my database connections since I have not been able to find any other way of sending my password to an Oracle dataabse. I have not found any way to set the dataabse timeout.

Has there been any update - can I set a Timeout on the data source?

The relevant error from teh Log events is as follows:

System.Data.Odbc.OdbcException: ERROR [HYT00] [Oracle][ODBC][Ora]ORA-01013: user requested cancel of current operation

at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)
|||

You can find CommandTimeOut property on the Advanced Editor page under Component properties tab.

|||

Jia,

That seems to work for OLE DB sources, but I also have no CommandTimeOut property on the Advanced Editor page under Component Properties when using a DataReader source and I have the same timeout problem. Simply trying to pull data from an average sized Teradata data warehouse table (~10M rows) using just select field1, field2... from table

Seems like sql server 2005 can't pull data via odbc if the query takes longer than 30 seconds?

Any help would be appreciated.

|||I have installed SP1 and now have the ability to set the timeout in the data reader. I have been able to resolve most of the issues I was facing. The first was to use an OLE DB data source to read from Oracle, then this one to set an appropriate timeout limit (0 is not 'infinite, it is 30 seconds, so I upepd it to 600 seconds). I had another issue to do with dates - Australian date formats are not handled very well between SQL Server and Oracle. I had to embed oracle "to_date" functions in the selection criteria.