Data Source issues through migration

We are using SQL Server 2000 Reporting Services. Our data resides in
Teradata. We have a Development/Acceptance Teradata server (TDDEV) and a
Production Teradata server (TDPROD). Our databases are named slightly
differently on each. For example, a database name EDM_D in development is
named EDM_A for acceptance and EDM_P in Production. What is the best way to
manage the need to change the schema name through migration? We want to
avoid opening every dataset, searching and replacing the schema name. I have
found that I can use a report parameter, but that stilll requires opening
multiple reports. Is there a way to create a global parameter for the
project? I am at my wit's end!Here's how I handle this.
The reports are created using a shared data source, let's say EDM in your
example.
Then in the target folder in each environment, I create a data source named
EDM, and enter an appropriate Connection string that connects to the
appropriate database.
"LoveBoxers" wrote:
> We are using SQL Server 2000 Reporting Services. Our data resides in
> Teradata. We have a Development/Acceptance Teradata server (TDDEV) and a
> Production Teradata server (TDPROD). Our databases are named slightly
> differently on each. For example, a database name EDM_D in development is
> named EDM_A for acceptance and EDM_P in Production. What is the best way to
> manage the need to change the schema name through migration? We want to
> avoid opening every dataset, searching and replacing the schema name. I have
> found that I can use a report parameter, but that stilll requires opening
> multiple reports. Is there a way to create a global parameter for the
> project? I am at my wit's end!

Data Read / Write problem with concurrent users on with dll

Hi,

I have a web app, that runs fine, except for one particular section that uses a class called by an event in the code behind. The class resides as a dll in the bin folder.

We had no problems during testing, when only one user was running this dll. Problems soon occurred when multiple users tried running it. Here's the error & stack:

06/02/2007 09:25:26 ==> cburns ==> There is already an open DataReader associated with this Command which must be closed first. at System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command)
at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, 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.ExecuteReader()
at ESP.Validator.Data.DatabaseEvents.DatabaseEventManager.Read(IEventable eventObject, Int16 eventType, DateTime earliestDate, DateTime latestDate) in C:\My Path\Validator\Validator.NET\Data\DatabaseEvents\DatabaseEventManager.cs:line 92
at ESP.Validator.Data.Translink.CATCard.GetDespatchDate() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\CATCard.cs:line 94
at ESP.Validator.Data.Translink.ExistingSchemeEntitlement.ReadCards() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\ExistingSchemeEntitlement.cs:line 215
at ESP.Validator.Data.Translink.ExistingSchemeEntitlement.Read() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\ExistingSchemeEntitlement.cs:line 147
at ESP.Validator.Data.Translink.TranslinkApplicant.ReadEntitlements() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\TranslinkApplicant.cs:line 369
at ESP.Validator.Data.Translink.TranslinkApplicant.Read() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\TranslinkApplicant.cs:line 353
at ESP.Validator.Data.Translink.PrePrintedLetter.Read() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\PrePrintedLetter.cs:line 282
at ESP.Validator.ValidationProcessor.Read(ValidationSubject subject) in C:\My Path\Projects\Validator\Validator.NET\ValidationProcessor.cs:line 82
at clear_applications_scan_applications.ProcessValidation()

It seems the data reader is getting reused. We have ensured after each read the reader is closed. Though all users are using the same connection string. Could it be a connection pool problem, with the connection being overwritten during execution? Should i edit it according to the users logon?

I am really at a loss for ideas, and I don't mind admitting I am a bit out of my depth with this one!!

Any ideas/suggestions would be greatly appreciated.

Thanks

Hi Assimalyst,

Based on the exception message, we can see that this is not because of reusing of the SqlDataReader, but reusing the SqlConnection.

Generally, on a single SqlConnection, there can only be 1 open SqlDataReader exist at the same time. If a using is trying to open another one, this exception is thrown.

In ADO.NET 2.0, a new concept is involved called Multiple Active Result Sets (MARS). This enables you open multiple DataReaders on a single connection. To achieve this, you only need to add "MultipleActiveResultSets=True" in your connection string. That will prevent the exception from being thrown.

Please check the following links for more information:

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

HTH. If anything is unclear, please feel free to mark this post as Not Answered and post your reply. Thanks!

sql

Data Load

SQL 2K
We have a data-warehouse server which loads data from different databases
which resides in a different server, perform business logic and load into a
FACT table.
I created 4 SQL jobs which does the above same thing from different
databases and writing to same table.
This job is scheduled to run at the same time.
My question
1) What all steps to consider ,since different jobs loading data to same
table and also query same table.
Thanks
ShIf each job has to do a look in the target table, it's probably not a good
thing to process them in parallel. Perhaps, you want to load the data
independently into a staging table for each of your jobs. Then do a final
push/pull of all staging tables.
--
-oj
http://www.rac4sql.net
"MS User" <sqlman@.sql.com> wrote in message
news:OdhPaWXIEHA.4052@.TK2MSFTNGP12.phx.gbl...
> SQL 2K
> We have a data-warehouse server which loads data from different databases
> which resides in a different server, perform business logic and load into
a
> FACT table.
> I created 4 SQL jobs which does the above same thing from different
> databases and writing to same table.
> This job is scheduled to run at the same time.
> My question
> 1) What all steps to consider ,since different jobs loading data to same
> table and also query same table.
> Thanks
> Sh
>

Data Load

SQL 2K
We have a data-warehouse server which loads data from different databases
which resides in a different server, perform business logic and load into a
FACT table.
I created 4 SQL jobs which does the above same thing from different
databases and writing to same table.
This job is scheduled to run at the same time.
My question
1) What all steps to consider ,since different jobs loading data to same
table and also query same table.
Thanks
Sh
If each job has to do a look in the target table, it's probably not a good
thing to process them in parallel. Perhaps, you want to load the data
independently into a staging table for each of your jobs. Then do a final
push/pull of all staging tables.
-oj
http://www.rac4sql.net
"MS User" <sqlman@.sql.com> wrote in message
news:OdhPaWXIEHA.4052@.TK2MSFTNGP12.phx.gbl...
> SQL 2K
> We have a data-warehouse server which loads data from different databases
> which resides in a different server, perform business logic and load into
a
> FACT table.
> I created 4 SQL jobs which does the above same thing from different
> databases and writing to same table.
> This job is scheduled to run at the same time.
> My question
> 1) What all steps to consider ,since different jobs loading data to same
> table and also query same table.
> Thanks
> Sh
>

Data Load

SQL 2K
We have a data-warehouse server which loads data from different databases
which resides in a different server, perform business logic and load into a
FACT table.
I created 4 SQL jobs which does the above same thing from different
databases and writing to same table.
This job is scheduled to run at the same time.
My question
1) What all steps to consider ,since different jobs loading data to same
table and also query same table.
Thanks
Sh
If each job has to do a look in the target table, it's probably not a good
thing to process them in parallel. Perhaps, you want to load the data
independently into a staging table for each of your jobs. Then do a final
push/pull of all staging tables.
-oj
http://www.rac4sql.net
"MS User" <sqlman@.sql.com> wrote in message
news:OdhPaWXIEHA.4052@.TK2MSFTNGP12.phx.gbl...
> SQL 2K
> We have a data-warehouse server which loads data from different databases
> which resides in a different server, perform business logic and load into
a
> FACT table.
> I created 4 SQL jobs which does the above same thing from different
> databases and writing to same table.
> This job is scheduled to run at the same time.
> My question
> 1) What all steps to consider ,since different jobs loading data to same
> table and also query same table.
> Thanks
> Sh
>

Data Load

SQL 2K
We have a data-warehouse server which loads data from different databases
which resides in a different server, perform business logic and load into a
FACT table.
I created 4 SQL jobs which does the above same thing from different
databases and writing to same table.
This job is scheduled to run at the same time.
My question
1) What all steps to consider ,since different jobs loading data to same
table and also query same table.
Thanks
ShIf each job has to do a look in the target table, it's probably not a good
thing to process them in parallel. Perhaps, you want to load the data
independently into a staging table for each of your jobs. Then do a final
push/pull of all staging tables.
-oj
http://www.rac4sql.net
"MS User" <sqlman@.sql.com> wrote in message
news:OdhPaWXIEHA.4052@.TK2MSFTNGP12.phx.gbl...
> SQL 2K
> We have a data-warehouse server which loads data from different databases
> which resides in a different server, perform business logic and load into
a
> FACT table.
> I created 4 SQL jobs which does the above same thing from different
> databases and writing to same table.
> This job is scheduled to run at the same time.
> My question
> 1) What all steps to consider ,since different jobs loading data to same
> table and also query same table.
> Thanks
> Sh
>