date and time diffs: running

Showing posts with label running. Show all posts

Thursday, March 29, 2012

Datatype problem: development vs production servers

This is driving me nuts: On my development machine the code runs finebut generates an error on the production server. Both are running SQLServer 2000 and ASP.NET 1.1

The datatype of the field in question isdatetime.
The webform has a calendar for a user to select and automaticallyinsert the date into the textbox. The update command in the webform is:
cmdInsert.Parameters.Add("@.citation_date", CDate(txtDate.Text))

This works without a hitch on my development system, but on the production server it generates the following error:
Cast from string "19-12-1997" to type 'Date' is not valid.

WHY? Sad [:(]
It has to do with the locale information (country, language, etc.) for the computer. Check to make sure the server is set to whatever you're using on your local development PC. I'm not familiar with setting/changing these since I only use U.S. format and English.|||

jcasp wrote:
It has to do with the locale information (country,language, etc.) for the computer. Check to make sure the serveris set to whatever you're using on your local development PC. I'mnot familiar with setting/changing these since I only use U.S. formatand English.

You are right. I'm inputting U.S format of date for the time being until I've figured a way around it. Thanks!|||Use YYYY-MM-DD format, then it doesn't matter what culture you are in.

Tuesday, March 27, 2012

DataSource uploads as text file type

Hi,
I have a new installation of SQLExpress ADV with RS installed on XP Pro.
When I try to upload an rds file (which has already been running
successfully on a w2k3 server), it uploads as a text file and NOT as a
Datasource.
The associated report does of course not work.
Anyone seen this before - or know how to fix it please '.
Thanks
Regards
GrahamHi Oxns,
Thank you for your posting!
From your description, my understanding of this issue is: you want to
upload the report data source file as a shared data source in the report
manager. If I misunderstood your concern, please feel free to let me know.
You cannot upload a report data source (.rds) file to create a shared data
source. An .rds file is used only in Report Designer. It cannot provide the
content for a shared data source item that you define and manage through
Report Manager or Management Studio. As an alternative to uploading, you
can write a script that creates a shared data source based on a .rds file.
Please refer to the sample script PublishSamplReports.rss file in the
Samples. By default, scripting samples are located in C:\Program
Files\Microsoft SQL Server\90\Samples\Reporting Services\Script Samples.
Here are the articles for your reference:
Script Samples (Reporting Services)
http://msdn2.microsoft.com/en-us/library/ms160854(d=ide).aspx
Uploading Files to a Folder
http://msdn2.microsoft.com/en-us/library/ms156300(d=ide).aspx
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Oxns,
How are you doing on this issue? Have you got any progress or does Wei's
last reply help you some on this? If the problem is not resolved or there
is anything else we can help, please feel free to post here.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.

Sunday, March 25, 2012

Datasource and Catalog

Hi all,

I accidentally deleted the datasource including the root data source and catalog from reporting services running in SQL server 2005. As a result, my reports didn't work. What should I do? Thanks!

lk_spec

Create a new datasource and link the report to the new datasources. What do you mean by catalog ?

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Thanks for you reply!

The data source was created dynamiclly through web services and SOAP. Before I deleted the data source, my reports worked fine. After that, however, it didn't work. I wonder if it's related to the root data source or something. Any help is appreciated!

|||The datasource is saved in the RDL as:

<rd:DataSourceID>e80dd27e-83d3-475c-b4c7-81261ee98f08</rd:DataSourceID>

with an ID, I don′t know if you can recover that. I you have the chance to reploy everything, I would go this way.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Jen,

Where did I find the datasource id. I checked the rdl file, but there wasn't such information. I'm really new at this stuff.

|||Did you have a view on the RDL in VS or the RDL in the Report Manager, you can download the RDL in Report Manager by Opening the report > Properties > Edit Report, you will get the RDL file downloaded which contained (in my case) this GUID.

HTH, Jens Suessmeyer.

http.//www.sqlserver2005.de|||

Jen,

Thanks for your reply!

I was able to create a new data source. The problem now is, however, an error message saying that the item "/" cannot be found. The application failed when it tried to FindCatalogItems:

rsws.FindItems(folderName, BooleanOperatorEnum.Or, conditions)

Any help would be appreciated!

lk_spec

|||

If you take regular backup of database than restore the database.

datasource "on-the-fly"

Hi,
Is it possible to change a datasource (or its connection string) in the
moment of report running?
Thanks.On SQL 2000, the only way is to create an SP which uses linked servers based
on the parameter you pass ie
create proc getdata @.source varchar(10)
as
if @.source = 'a'
select ... from mylinkedserver1.db.dbo.table
else
if @.source
With SQL 2005 you can use a dynamic data source based on a parameter ie. In
the connection string
="data source=" &Parameters!ServerName.Value & ";initial
catalog=AdventureWorks
Hope this helps
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Vladimir Evdokimov" <evesq@.uk2.net> wrote in message
news:OCvqf0kzFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is it possible to change a datasource (or its connection string) in the
> moment of report running?
> Thanks.
>|||Hello
In SQL 2000 is it possible to supply the datasource (database name etc) as
part of the arguments supplied in the URL?
My application runs off multiple databases corresponding to different
customers and I need to switch the datasource based on the customer user
calling the report.
Any ideas on the best way to accomplish this task in SQL 2000?
Thank you|||Although you can do this with RS 2000 it will be easier in 2005.
In 2000 you have to use dynamic SQL. Use generic query designer (button to
switch to this is to the right of the ...)
declare @.SQL varchar(255)
select @.SQL = 'select table_name from ' + @.Database +
'.information_schema.tables order by table_name'
exec (@.SQL)
Note that @.Database should cause a report parameter called this to be
created. Anyway, this is the concept.
In 2005 you will be able to have dynamic datasources. From 2005 help:
Data Source Expressions
You can put an expression into a connection string to allow users to select
the data source at run time. For example, suppose a multinational firm has
data servers in several countries. With an expression-based connection
string, a user who is running a sales report can select a data source for a
particular country before running the report.
The following example illustrates the use of a data source expression in a
SQL Server connection string. The example assumes you have created a report
parameter named ServerName:
Copy Code
="data source=" &Parameters!ServerName.Value & ";initial
catalog=AdventureWorks
Data source expressions are processed at run time or when a report is
previewed. The expression must be written in Visual Basic. Use the following
guidelines when defining a data source expression:
>>>>>>>>
a.. Design the report using a static connection string. A static
connection string refers to a connection string that is not set through an
expression (for example, when you follow the steps for creating a
report-specific or shared data source, you are defining a static connection
string). Using a static connection string allows you to connect to the data
source in Report Designer so that you can get the query results you need to
create the report.
b.. When defining the data source connection, do not use a shared data
source. You cannot use a data source expression in a shared data source. You
must define a report-specific data source for the report.
c.. Specify credentials separately from the connection string. You can use
stored credentials, prompted credentials, or integrated security.
d.. Add a report parameter to specify a data source. For parameter values,
you can either provide a static list of available values (in this case, the
available values should be data sources you can use with the report) or
define a query that retrieves a list of data sources at run time.
e.. Be sure that the list of data sources share the same database schema.
All report design begins with schema information. If there is a mismatch
between the schema used to define the report and the actual schema used by
the report at run time, the report might not run.
f.. Before publishing the report, replace the static connection string
with an expression. Wait until you are finished designing the report before
you replace the static connection string with an expression. Once you use an
expression, you cannot execute the query in Report Designer. Furthermore,
the field list in the Datasets window and the Parameters list will not
update automatically.
>>>>>>>
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GavinMc" <GavinMc@.discussions.microsoft.com> wrote in message
news:F7640227-35C6-44CB-89BD-ED283ED242A4@.microsoft.com...
> Hello
> In SQL 2000 is it possible to supply the datasource (database name etc) as
> part of the arguments supplied in the URL?
> My application runs off multiple databases corresponding to different
> customers and I need to switch the datasource based on the customer user
> calling the report.
> Any ideas on the best way to accomplish this task in SQL 2000?
> Thank you
>|||Thanks Bruce, unfortunately I'm stuck with SQL 2000 for the moment...
Just a further enquiry based on the above. To allow for the switching of
databases on the fly for my dataset based on an entered parameter, I created
the following sql:
USE master
declare @.SQL varchar(255), @.mydb varchar(255)
set @.mydb= (SELECT dbID FROM co WHERE (coID = @.co))
select @.SQL='USE ' + @.mydb + ' SELECT accid, accno, accname FROM acc'
exec (@.SQL)
This reads a user defined 'co' table in master that contains a reference to
my target databases, When running a report I then supply a 'coID' to retrieve
the reference to the correct database and this is then used as a variable in
report's underlying dataset to ensure that the correct database is used. I
think this is along the lines of what you suggested above.
My question is whether the select statement needs to be built up as a string
for execution, this will obviously require rewriting all my existing plain
sql statements and inserting concatenation operators around the parameters
etc.
Is there some way that I can declare a parameter for the database I want to
run the report off, and include this at the start of existing sql statements
in the 'use' statement without explicitly declaring a sql str variable?

Wednesday, March 21, 2012

Dataset dependant on who's running the report...

I've got a parameter that I am populating from sql, but I'd like to be
able to add some functionality that does something like check for
permissions of the logged in user and use one dataset over another.
Is there anwyay I can achieve this or simulated it in any way?
Thanks in advance
Weston WeemsHi Weston
We are handling this by passing a userID as a parameter and based on
the userID passed will show or hide fields. we haven't created
different datasets, rather one large one and only display what is
necessary.
I don't know if this will help you at all.
Jennifer

Monday, March 19, 2012

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.