Database

Moving data between servers

18-Jan-06 5:28

kids17 wrote:
What are some of the tables you would include in an ER diagram for creating a database for a encyclopedia using SQL

Gosh! That's a bit vague.

Okay an encyclopaedia is made up of entries. An entry can have cross references (normally, these days it would be hyperlinks within the text), but lets say there is a See Also section at the end of each article rather than through the text.

So an Articles table, and a References table.

I can't think of much else because an encyclopaedia is a free flowing thing.

What do you think needs to be included?

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

-- modified at 11:29 Wednesday 18th January, 2006

Russell Nash18-Jan-06 2:54

Russell Nash

18-Jan-06 2:54

I'm having an issue that I need "best practice" advice on.

I need to collect data from one SQL Server, and insert it into another SQL Server. The problem is that the two databases cannot talk to each other. And, because I access both with different full-tunnel VPN's, I cannot talk to both of them at the same time.

So, my process is:
Connect to source VPN
Connect to source database
Pull data from view down to local PC
Disconnect from source database
Disconnect from source VPN
Connect to destination VPN
Connect to destination database
Upload data from local PC to destination database
Disconnect from destination database
Disconnect from destination VPN

If I leave the data in a dataset:
Will the data persist after the connection is closed?
Can I connect that dataset to either a new data adapter, or change the connection string of the data adapter?
Is it efficient to copy the data into a different dataset?

If I use XML:
How do I save the data from the XMLReader to a file?
Is this efficient?

Is there another option I'm missing, that would be more efficient?

Thanks for your help! I could make this go, but as the datasets are going to be quite large, I want to make sure I'm doing it in the best way.

Re: Moving data between servers

PolarbearDK18-Jan-06 4:57

PolarbearDK

18-Jan-06 4:57

I've been thinking about making a utility for this:

I a previous thread I asked for feedback on it, but got no replies. Frown | :(

Re: Moving data between servers

Farhan Noor Qureshi18-Jan-06 7:34

Farhan Noor Qureshi

18-Jan-06 7:34

Russell Nash wrote:
If I leave the data in a dataset:
Will the data persist after the connection is closed?

Yes, if you use disconnected datasets.

Russell Nash wrote:
Can I connect that dataset to either a new data adapter, or change the connection string of the data adapter?

I am pretty sure it can be done.

Russell Nash wrote:
Is it efficient to copy the data into a different dataset?

By using DataSet.Copy()?

Russell Nash wrote:
How do I save the data from the XMLReader to a file?

To dump contents of Dataset to an XML file use DataSet.WriteXml().

Russell Nash wrote:
Is this efficient?

I guess.

Russell Nash wrote:
Is there another option I'm missing, that would be more efficient?

One option would be the plain old fashion way of doing stuff like that. Generate INSERT statements and using Bulk Insert. Very fast and easy.

Russell Nash wrote:
I could make this go, but as the datasets are going to be quite large, I want to make sure I'm doing it in the best way.

For larger data, I find BULK insert to be the most efficient way.

Farhan Noor Qureshi

^{if (this == this) thow this;}

query for unique customers

imsathy18-Jan-06 2:22

imsathy

18-Jan-06 2:22

i need a query to display unique customers on each of thier last purchase date alone segregating other dates in the same columns for the same customer

thanx in advance

sathy

Colin Angus Mackay18-Jan-06 2:50

18-Jan-06 2:50

If you want help on building a query then you need to tell us what the tables are, what columns are available, what the primary key(s) are on each table and their foreign key counterparts on other tables.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

imsathy18-Jan-06 3:17

imsathy

18-Jan-06 3:17

the table contains customerId(foreign key), dateOfPurchase, totalValue, shopId(primary key) these are the four columns to be dealth

i want to display unique customerId, dateOfPurchase(in specific the last purchase date of a customer), totalValue for the given shopId

select distinct customerId, max(dateOfPurchase)as date, totalValue from business_orders where shopId = '9F5DF' group by customerId

this query works when totalValue is not added in the query

sathy

Colin Angus Mackay18-Jan-06 4:00

18-Jan-06 4:00

imsathy wrote:
this query works when totalValue is not added in the query

That is correct, it must be in the GROUP BY clause or part of an aggregate. If you want the totalValue from the corresponding date you need to create a subquery.

SELECT customerId, [date], totalValue
FROM business_orders AS bo
INNER JOIN (
    SELECT customerId, max(dateOfPurchase) AS [date]
    FROM business_orders 
    WHERE shopId = '9F5DF' 
    GROUP BY customerId) AS ld ON 
        bo.customerId = ld.customerID AND 
        bo.[dateOfPurchase] = ld.[date]

I also removed the DISTINCT keyword as the GROUP BY effectively does that already and so it was redundant.

CAVEAT: If you have two or more orders on the same dateOfPurchace then you will get duplicates for an individual customer. There is nothing you can do about that as there is not enough date to determine which was really the last purchase.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

How to assign dataset in Reporting Services 2005?

imsathy18-Jan-06 18:32

imsathy

18-Jan-06 18:32

thanx a lot Colin

sathy

Diego F.17-Jan-06 22:48

Diego F.

17-Jan-06 22:48

I'm trying to integrate Reporting Services with an existing ASP.NET 2.0 web application. The goal is to click a button to call a report, but instead of just calling an existing report, to pass the dataset and create dinamically the report. If that's possible, users can create their own reports, by selecting the fields that will appear in the report.

Is that possible? Where can I find a way to achieve that?

Regards,

Diego F.

multiple update problem in datagrid

sood_is_in17-Jan-06 21:24

sood_is_in

17-Jan-06 21:24

I have a datagrid in which there are 2 columns
--First one is a template column which is having a textbox in its itemtemplate.
--secound one is a bound column and is picking up some unique values from database

Now on the page load i am providing the datagrid its datasource and binding it then. after i bind the data, the text boxes will have some values which is the first column and the secound column will be having some unique value now what i want is when i click on a button(not in the grid anywhere on the page) i want to update all those records for which the value in the text box(in the datagrid) has been changed and i want to update all the changes in the database in one trip back to the server.

Help Me!!!

Re: multiple update problem in datagrid

Alexandru Serban18-Jan-06 10:59

Alexandru Serban

18-Jan-06 10:59

You are very ambiguous in describing your "problem" but this is a simple matter of getting your hands on decent ASP.NET book that covers databinding in Webforms.

Need Help With Creating an Easy Code.

CoMMyz17-Jan-06 21:19

CoMMyz

17-Jan-06 21:19

Hi,

Im using Win2000 SP4 with SQL Server 2000 SP4

Basically i need some help with the following.

I have 2 Tables: Called Member and MemberTable
In Member table a column in there stores Names (Called Names) of the members and also in MemberTable there is another column called Names (Called Names) Which stores Names also of members.

I want to erase all rows in MemberTable that do not match those in Member (Case insensitive).

Can anyone provide a code? I tryied doing this myself and erased all at the end Frown | :(

Good thing i had a backup!

Please help.
Chris

Re: Need Help With Creating an Easy Code.

Russell Nash18-Jan-06 2:58

Russell Nash

18-Jan-06 2:58

If I'm understanding you correctly...

DELETE FROM [MemberTable]
WHERE [Called Names] NOT IN (SELECT [Called Names] FROM [Called Member])

HTH

NT User List

Bob Zagars17-Jan-06 20:08

17-Jan-06 20:08

Fellow Professionals,

Is there anyway to get the NT users associated with the server in SQL Server 2000? I know xp_enumgroups gets a list of NT Groups but I can not find a stored procedure that returns that information. Does SQL 2005 have it? My other alternative is to write a .NET object and obtain this information from the local network.

Well can anyone help me? Please let me know.

Best Regards,

Bob Zagars

Colin Angus Mackay17-Jan-06 20:54

17-Jan-06 20:54

Bob Zagars wrote:
Is there anyway to get the NT users associated with the server in SQL Server 2000?

SELECT name 
FROM master..syslogins
WHERE IsNTUser = 1

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Bob Zagars17-Jan-06 23:52

17-Jan-06 23:52

That gives me registered users what about user not yet regisered. Stored procedure:

xp_enumgroups
Provides a list of local Microsoft® Windows NT® groups or a list of global groups defined in a specified Windows NT domain.

Syntax
xp_enumgroups ['domain_name']

They do not have to be registered on the database just within a Windows NT domain.

Best Regards,

Bob Zagars

S Douglas18-Jan-06 0:40

S Douglas

18-Jan-06 0:40

Bob Zagars wrote:
They do not have to be registered on the database just within a Windows NT domain.

If they haven’t been registered in SQL Server then SQL server won’t know about them. You will need to interrogate the Domain it self, if it’s an Active Directory Domain (I think it’s called tree now) here is a vb script that lists all disabled users, a minor mod and it could dump out all users.

List All the Disabled User Accounts in Active Directory[^]

ZeePain! wrote:
This seems like one of those programs that started small, grew incrementally, building internal pressure, and finally barfed all over its source code sneakers. Or something.

thedailywtf.com[^]

Bob Zagars18-Jan-06 5:46

18-Jan-06 5:46

S. Douglas,

Thanks I will check it out and let you know if it works.

Bob Zagars

how to access the Sql Server Publicly

S Douglas18-Jan-06 5:53

S Douglas

18-Jan-06 5:53

No problem...

ZeePain! wrote:
This seems like one of those programs that started small, grew incrementally, building internal pressure, and finally barfed all over its source code sneakers. Or something.

thedailywtf.com[^]

raheeli17-Jan-06 18:15

raheeli

17-Jan-06 18:15

how can i insert aur select data to sql Server through public IP, is there any configuration for this...? i have my own physical server do there any different setting for this. or any different connection string . i m very much fed up for this can any one help me out.

Re: how to access the Sql Server Publicly

Bob Zagars17-Jan-06 20:18