Showing posts with label input. Show all posts
Showing posts with label input. Show all posts

Monday, March 19, 2012

Best practice to pull data from sql server 2000 to sql server 2005 with dynamic queries

Hi There,

I need to pull data using input from one table in sql server 2005. I have to query against the sql server 2000 database and pull data into sql server 2005. I have a list of ids that I have to pass to a query to get the desired data. What is the best practice for this. Can I use SSIS or do I need to build an app in C#? Can somebody please reply back?

Thanks a lot!!

If you need to query sql server 2000 database to migrate data, then sql server 2005 import/export wizard might be a good one. Please take a look at http://msdn2.microsoft.com/en-us/library/ms141209.aspx. If you don't need to do query, bcp utility might be a good candidate. Please take a look at http://msdn2.microsoft.com/en-us/library/ms162802.aspx.

Thanks,

Junfeng

|||

First, set up the SQL 2000 server as a 'Linked Server' for the SQL 2005 server (See Books Online for details about Linked Servers.)

Then, using 'four-part naming conventions', you can just query between the two servers.

This example, when executed on the SQL 2005 server, would take data from the SQL 2000 server and insert it into the SQL 2005 server:


Code Snippet


INSERT INTO MyTable (Col1, Col2, Col3, etc.)
SELECT Col1, Col2, Col3, etc.
FROM MySQL2000Server.MyDatabase.dbo.MyTable
WHERE MyID IN ( 1, 2, 5, 10, 25 )

|||

Hi There,

Here's my requirements...

I have to run a query B against database B with results (list of ids) from a query A run against database A and then push the results back to database A. Query B is constructed dynamically from the results obtained from Query A. So I wouldlike to know what would be the best way to achive this?

Thanks a lot!!

Thursday, March 8, 2012

Best practice about creating partitions dynamically in AS2000 ?

Does anyone have a good article, link or just input concering the dynamic creation and processing of partitions in a cube.

The scenario where a large amount of data is daily loaded into the data warehouse. In the ETL there is some logic creating a new table when data is comming in for a new month (example: the table Fact_Q1_2007 is created when data is being recieved on 1. januar 2007 and Fact_Q2_2007 is created when data is recieved 1.april and so on)

The question is now - how do i set up the logic to create partitions dynamic in the cube and afterwards make sure that the partitions are being processed successfully.

One approach is to use the Decision Support Objects (DSO) API, which can be invoked from tools which support COM (like DTS):

http://msdn2.microsoft.com/en-us/library/aa936638(SQL.80).aspx

>>

Decision Support Objects Programmer's Reference

Microsoft? SQL Server? 2000 Analysis Services offers substantial opportunity for you to create and integrate custom applications. The server object model, Decision Support Objects (DSO), provides interfaces and objects that can be used with any COM automation programming language

...

>>

http://msdn2.microsoft.com/en-us/library/aa177800(SQL.80).aspx

>>

...

Use the following code to create an object of ClassType clsPartition:

'Assume an object (dsoCube) of ClassType clsCube exists

Dim dsoPartition As DSO.MDStore

Set dsoPartition = dsoCube.MDStores.AddNew("MyPartition")

>>

Typically, you might then clone an existing "template" partition, and update relevant properties like SourceTable:

http://msdn2.microsoft.com/en-us/library/aa177699(SQL.80).aspx

>>

Properties, clsPartition

...

SourceTable

The name of the fact table for the partition.

SourceTableFilter

Contains the WHERE clause of the SQL statement used to determine which source table rows are to be included in the partition.

>>

|||

Thanks

I read that there should be some tools in the SQL Server 2000 Ressource Kit, which lays only on MSDN. But the File Transfer Manager isn't able to download that file ("Application validation failed, transfers are not enabled"), there seems nowhere else to get that kit. Don't know how long microsoft will take to fix their File Transfer Manager......