Exporting data between databases

Just starting out with MSSQL 2000 and have a bit of a problem.
I have a remote database that is of the same design of a local database. What I want to do is update one particular field of each record in my local database with data from the remote one. Of course the primary key of each of the records from the remote dbase and the local dbase must match (as even though the remote database has the most up-to-date information, it has more records in it than my local one (but I do not require the additional records).

How on earth do I do this? I could use the DTS based on a query, but how can I ensure that the data goes in the correct records' field?

Any help would be appreciated!

Thanks.If you have ACCESS 2000 or 2002 you can create two linked tables.

One to each table and then use access to run your update, then you can run you insert of the additional fields at the same time.|||Originally posted by Odin_the_Celt
How on earth do I do this? I could use the DTS based on a query, but how can I ensure that the data goes in the correct records' field?


What about using the primary keys for unique identifing the records? And using Inner Join in the update statement.

Best regards!|||Originally posted by Odin_the_Celt
How on earth do I do this? I could use the DTS based on a query, but how can I ensure that the data goes in the correct records' field?


What about using the primary keys for unique identifing the records?

Best regards!

Export to XML

Hi,

I have been researching the new features of Server 2005 and its handling of XML; there are some impressive features.

I am hoping to design a reusable process for extracting data as XML and am having trouble, I am hoping that someone can help me brainstorm some of my ideas.

Conceptually I would like a procedure or service that I can pass a Stored Procedure and any necessary information needed to execute it as well as an XML Schema and have returned an XML stream containing the results of executing the Stored Procedure formatted according to the provided schema.

The closest feature to this type of functionality that I have found is the HTML/SOAP Endpoints. But with these I see no way to format the out put to a provided schema.

The motivation for this seems like it should be a common one. We have multiple sources of data and many clients who want regular data extracts. We already have Stored Procedures that extract the same data for application use and we also know the schema of the XML we would like to produce. So a reusable tool to produce these XML extracts would be ideal.

Does anyone have experience with this type of thing?

Thanks,

Denis

Denis,

I'm not aware of any SQL Server built-in capability to auto generate an arbitrary XML result based on a XML schema. You can create a stored procedure (potentially using in-proc CLR SP) that will take a XML schema and generate the XML format you need, but this will be code that you will need to write. That same SP can then be exposed through SOAP endpoints if web services is of interest in your scenario.

Alternatively, you can write an ASP.net function to retrieve the data from SQL Server and then generate the XML format you need.

Jimmy Wu

export to excel

Hi:
I have some percentage value in my report. I set these to 0 decimal point
in design layout.
It works fine when display on the web. But when I export them to excel,
these value is set to 1 decimal point.
Is there a way to round to no decimal point when exporting to excel?Ken,
Indeed the standard percentage format without decimals (P0) doesn't export
correctly to Excel. You can use #,##0% as a custom format.
Jan Pieter Posthuma
"ken" wrote:
> Hi:
> I have some percentage value in my report. I set these to 0 decimal point
> in design layout.
> It works fine when display on the web. But when I export them to excel,
> these value is set to 1 decimal point.
> Is there a way to round to no decimal point when exporting to excel?
>|||Thanks Jan, it's very helpful
"Jan Pieter Posthuma" wrote:
> Ken,
> Indeed the standard percentage format without decimals (P0) doesn't export
> correctly to Excel. You can use #,##0% as a custom format.
> Jan Pieter Posthuma
> "ken" wrote:
> > Hi:
> > I have some percentage value in my report. I set these to 0 decimal point
> > in design layout.
> > It works fine when display on the web. But when I export them to excel,
> > these value is set to 1 decimal point.
> > Is there a way to round to no decimal point when exporting to excel?
> >