Database

Ashfield10-Jun-08 21:02

10-Jun-08 21:02

If its a one off:

begin tran

update database1.dbo.table set .....
update database2.dbo.table set .....
update database3.dbo.table set .....

commit tran

You need to put some error checks and rollback, but you get the idea. If its an ongoing requirement you ought to consider replication

Hope this helps

Bob
Ashfield Consultants Ltd

Dushan12310-Jun-08 23:47

Dushan123

10-Jun-08 23:47

Actually I don't no the exactly name of the database. I only know the database name contains the word "User". Plese can u help me

ssss

Ashfield11-Jun-08 3:02

11-Jun-08 3:02

You need to generate some dynamic sql then. You can the update statements from master..sysdatabases

select 'update ' + name + '.dbo.table set ...' from master..sysdatabases
where name like '%user'

All you need to do then is put it in loop and execute it, or something similar - there are loads of ways from this point.

Bob
Ashfield Consultants Ltd

Mixing Select into and Create

Dushan12311-Jun-08 3:30

Dushan123

11-Jun-08 3:30

thanks

ssss

prithaa10-Jun-08 20:03

prithaa

10-Jun-08 20:03

Hello,

I have this following query

SELECT timeperiod.timeperiod,product.product,JobWork.JobWork
INTO DD FROM timeperiod,product,JobWork where Product='Shirt199Light'

But now I want to add one more column to table DD .
Is this possible in one query? Or Will I have to write one more statement after SELECT.

Thanks
Pritha

Re: Mixing Select into and Create

Ashfield10-Jun-08 20:59

Re: Mixing Select into and Create

10-Jun-08 20:59

Yes.

SELECT timeperiod.timeperiod,product.product,JobWork.JobWork, convert(int,0) as NewCol <br />
INTO DD FROM timeperiod,product,JobWork where Product='Shirt199Light'

I have used int as an example, you can use anything - I often use a real column from the select taht is of the right data type, for example:

SELECT timeperiod.timeperiod,product.product,JobWork.JobWork, product.product as OtherColumn INTO DD FROM timeperiod,product,JobWork where Product='Shirt199Light'

Bob
Ashfield Consultants Ltd

prithaa10-Jun-08 21:52

prithaa

10-Jun-08 21:52

Thanks
It worked

Local ADO.NET Performance Issue

I don't want a display name10-Jun-08 11:01

I don't want a display name

10-Jun-08 11:01

I'm having difficulty identifying the best way to process a dataset I've been provided. It's a fairly large dataset (1.76m records) with two columns (pk1,pk2) in the primary key. e.g., {{1,a},{1,b},{1,c},{1,d},{2,a},{2,b},{3,a},{4,a},{4,b}}

What I need to do is read all the records for pk1 in the dataset {{1,1},{1,2},{1,3},{1,4}}, run some calculations on these, write the results to a different table, then move on to the next pk1. I've tried a couple methods, and run into problems in each. I'm wondering if anyone has any ideas for this.

I'm restricted using the local file system, and cannot install anything new locally: e.g., MS Access, text files (connecting through system.data.oledb), cannot install MySQL or SQLServer.

What I've done thus far, is:
- create a DataTable containing a list of distinct pk1 values [MainList]. {1,2,3,4}
- For each unique value in [MainList],
- Execute a sproc via OleDbDataAdapter.Fill method to populate a DataTable containing all records in the original dataset associated with the current pk1 {{1,a},{1,b},{1,c},{1,d}}
- Process this datatable to create the user's output
- Write the results to a separate set of tables in the same database.
This method takes an unacceptable amount of time, with 98% taken up by the .Fill method.

Next, I tried the same process, but using the OleDbDataReader in lieu of the .Fill(DataTable) process. This improves the speed slightly, but still not where we need it.

Running this file through sequentially, using a text file, and checking the current records key against the prior records key, we processed the entire file in about an hour.

We are currently running VB.NET 2005 with ADO.NET 2.0.

Also, are there any other options on databases available, provided we're not allowed to install something locally, and can only use the local system?

Any input would be appreciated.

Re: Local ADO.NET Performance Issue

Mycroft Holmes10-Jun-08 16:19

Mycroft Holmes

10-Jun-08 16:19

Seems to me that it is a simple set of select statements, unless I am missing something.

A few questions come to mind.
Do you want to do this in the database or the client (VB)?
Do you need to store the results or just output them to the client?

Personally I would use a stored proc, this gives you many tools such as table vars, cursors etc.
1.76m records is not that large.

Never underestimate the power of human stupidity
RAH

Re: Local ADO.NET Performance Issue

I don't want a display name11-Jun-08 3:05

I don't want a display name

11-Jun-08 3:05

Thank you, Mycroft,

We would prefer to have all the processing performed in the client, although creating temporary stored procs and deleting them when completed is well within reason, the calculation itself needs to be housed in the client so we want to pass the subsets of data back to the client. The problem (at least as far as I can tell) is getting the data transferred to the client for the calculation.

And yes, the results need to be stored, as the user has a number of options on how s/he can manipulate the results. Although, using an interim step to write it to a different file, then importing it to the database is feasible (since I cannot have an open datareader if I want to write something to the same database).

I hadn't thought about using a cursor for this. Are cursors available using ADO.NET?

Re: Local ADO.NET Performance Issue

Mycroft Holmes11-Jun-08 14:05

Mycroft Holmes

11-Jun-08 14:05

I don't want a display name wrote:
although creating temporary stored procs and deleting them when completed is well within reason

Oh no its not reasonable - this implies you do not "own" the database otherwise why delete the proc?

I don't want a display name wrote:
We would prefer to have all the processing performed in the client

"prefer" is not a strong enough reason to screw up your application design, there needs to be a good reason to inflict this design on yourself. You need to make a decision, where you want the work to be done. I always prefer the server but that does not preclude using the client. What you should not be doing is using both!

Unless there is a calculation method/function you cannot use in the server then I would pass the parameters to get the resultset to the server proc, process the calculation as close to the datastore as possible (SQL) and return the resultset.

If you must use the client I think you should look into linq. I could do this with datatables and views by iterating through the records but linq sounds like it could do the job with sql like statements.

Never underestimate the power of human stupidity
RAH

SQL Server 2008 Release Candidate Available to Public

brucedkyle10-Jun-08 6:00

brucedkyle

10-Jun-08 6:00

You can get the release candidate of SQL Server 2008 from Microsoft Download or through your MSDN subscription.

To download, see SQL Server 2008 RC0 Download[^]. You can also download it through your MSDN subscription[^]. You can also find SQL Server 2008 RC 0 at TechNet Plus[^].

The released version is scheduled for third calendar quarter this year.

This release includes support for upgrading failover clusters from previous versions of SQL Server, as well as several bug fixes and minor improvements that we have made in response to the extensive customer feedback we’ve received from previous CTPs.

More than 250,000 customers and partners have downloaded the previous community technology preview (CTP) releases.

To learn more about SQL Server 2008, see SQL Server 2008, What's New[^]. To get started using the features in SQL Server 2008, see SQL Server 2008 Jump Start[^].

SQL Server 2008 delivers on the data platform vision in four areas: Mission Critical Platform, Dynamic Development, Beyond Relational, and Pervasive Insight.

Learn more about Microsoft emerging techologies[^] on the Microsoft blog for independent software vendors (ISVs) in the United States.

Recover Oracle Connection in a Windows Service.

jamorey10-Jun-08 4:21

jamorey

10-Jun-08 4:21

Hello.

I have some problems with an application developed in C# and Oracle connection, The Application is a Windows Services that process some actions in an Oracle database in real time.

The problem is that I'm using C# 2003 and Framework 1.1 SP1, and I don´t know why the service lost the connection with the database and the object Oracleconnection.State is "Open".

I think that this is my problem:
http://support.microsoft.com/default.aspx?scid=kb;en-us;830173[^]
But I can´t close and open the connection all the time, I´m only need to reopen the connection when and Exception Happended (But my connection class it´s in other .net solution).

I thought that if I put a thread listening a variable that is changed any time I do an action, if this variable doesn´t change I reopend the connection, but my client doesn´t like this solution.

Anyone can help me ?, if you think on any solution please let me know.

Thank's a lot.

Regards, Jose A. Morey.

Re: Recover Oracle Connection in a Windows Service.

Mycroft Holmes10-Jun-08 16:26

Mycroft Holmes

10-Jun-08 16:26

Retain the connection credentials in the web service
retain the sql command object for resubmission
submit command
trap the error when the SQL connection fails
interrogate the error to make sure it is the connection fail error
reestablish the connection
resubmit the SQL action (this being the nasty bit)

Just some ideas!

Never underestimate the power of human stupidity
RAH

Date Between Problem

Babu.R.K10-Jun-08 3:48

Babu.R.K

10-Jun-08 3:48

Hi,

I have a typical scenario. I want to get

My query is

SELECT StarRate, date FROM BasicSearch WHERE date BETWEEN '2008-06-03' AND '2008-06-05';

My Result is as follows

StarRate date
-------- -----
1 2008-06-03 00:00:00.000
1 2008-06-04 00:00:00.000
1 2008-06-05 00:00:00.000
5 2008-06-04 00:00:00.000
5 2008-06-05 00:00:00.000

But I require the result as below

StarRate date
-------- -----
1 2008-06-03 00:00:00.000
1 2008-06-04 00:00:00.000
1 2008-06-05 00:00:00.000

Because StarRate 1 has all 3 dates

Thanks in advance

Ashfield10-Jun-08 3:59

10-Jun-08 3:59

SELECT DISTINCT StarRate, date FROM BasicSearch WHERE date BETWEEN '2008-06-03' AND '2008-06-05';

Bob
Ashfield Consultants Ltd

Kjetil Svendsen11-Jun-08 3:15

Kjetil Svendsen

11-Jun-08 3:15

Hi
Do you want the output to be only the StarRate witch has all the dates in the date interval?

Try

SELECT StarRate, date FROM BasicSearch WHERE date BETWEEN '2008-06-03' AND '2008-06-05'
AND StarRate in (
    SELECT StarRate FROM BasicSearch WHERE date BETWEEN '2008-06-03' AND '2008-06-05'
    GROUP BY StarRate 
    HAVING Count(*) = DATEDIFF("D", '2008-06-03', '2008-06-05') + 1)

This should work if it's uniqueness on StarRate and date

Kjetil

Ashfield11-Jun-08 5:01

11-Jun-08 5:01

Nice one. I think I misread the original post, didn't notive it was just the starrate 1's required.

Bob
Ashfield Consultants Ltd