CODEWITHSUNDEEP
sqlsql-server
steve.g
steve.g

Reputation: 115

Pulling data across multiple servers

The company i am working for is implementing Share-point with reporting servers that runs on an SQL back end. The information that we need lives on two different servers. The first server being the Manufacturing server that collects data from PLCs and inputs that information into a SQL database, the other server is our erp server which has data for payroll and hours worked on specific projects. The i have is to create a view on a separate database and then from there i can pull the information from both servers. I am having a little bit of trouble with the syntax for connecting the two servers to run the View. We are running ms SQL. If you need any more information or clarification please let me know.

Upvotes: 0

Views: 1662

Answers (2)

Void Ray
Void Ray

Reputation: 10209

It all depends on a project size and complexity, but in many cases it is difficult to aggregate data from multiple sources with Views. The reason is that the source data structure is modeled for the source application and not optimized for reporting.

In that case, I would suggest going with an ETL process, where you would create a set of Extract, Transform and Load jobs to get data from multiple sources (databases) into a target database where data will be stored in the format optimized for reporting.

Ralph Kimball has many great books on the subject, for example: 1) The Data Warehouse ETL Toolkit 2) The Data Warehouse Toolkit

They are truly worth the read if you are dealing with data

Upvotes: 0

SQLMason
SQLMason

Reputation: 3275

Please read this about Linked Servers.

Alternatively you can make a Data Warehouse - which would be a reporting data base. You can feed this by either making procs with linked servers or use SSIS packages if they're not linked.

Upvotes: 2

Related Questions