Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pioniereelfico
Starting Member
2 Posts |
 Posted - 2011-06-06 : 04:10:25
|
| Hi guys, I'm new to the forum :) I work in a small company that have 2 different database server (one in Brescia ([url]http://en.wikipedia.org/wiki/Brescia[/url]) and other in Pavia [url]http://en.wikipedia.org/wiki/Pavia[/url]). My boss, a very very crazy man, ask me hourly pivot (with excel) from data of two server. I've implemented thus 2 sheets in excel with a mssql connection each one, and an internal UNION ALL query that merge the two dataset (that have same fileds), but the file, cannot be moved, touched, refreshed within looose the excel msquery connection. So, cause I've my stuff to do and is not good to have boss entering in my box every 30 minutes, I need your help. There is a method to get in automagically the data from the server, union all and free me from this boss-asking-data fate? Please don't tell me to use sp_addlinkserver, it work one day yes, one day no.thank you in advance ;) |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-06 : 04:33:22
|
| Try using bcp to connect to the two servers and copy the data to a local server as files then import to a database (could be client machine sql server express or access or even excel if you don't have a local server).I would keep the two databases seperate and use views to merge the data.You can then convert to any method to copy the data - like replication, ssis, backup restore, ...This isolates the transport from the presentation and can easily be scheduled.If you work on the files and a manula excel import make a copy for the import so that they are not locked.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
pioniereelfico
Starting Member
2 Posts |
Posted - 2011-06-06 : 05:04:37
|
| Good solution, but not optimal, I think. I can implement this method, putting a database server on an additional machine, bcp the two database set and have a 30 minutes refreshed image of the database.. But, during night and weekend we don't need this method, and probably this will overload our server and cause disk failure I think. So, there is another problem. My boss, after look at the data, need to do some modification (for example, changing a value in a report table) and ask to another person (Elisa, a very beautiful girl) to open the Financial Program, modify the data and then he call her in his office to view if the modification was good. So, the right way to do this is refresh pivot table data without copy the data, but do a query that recall everything everytime my boss need.Here's the structure of the flow of data:(1)DATABASE_BRESCIA--BILL_VIEW--...................................................--- (3) UNION VIEW---> (4)EXCEL PIVOT(2)DATABASE_PAVIA----BILL_VIEW--'When refresh from excel, the entire flow must be re-executed, and the data fully refreshed. Thus, the 1,2,3 part of the graph should be accomplished by a black box machine, and the 4 should be done by user command. 1,2 could be on different machines and also 3 could be a different machine (a sort of local-view-merger-server that anybody could access). There is any method to accomplish this?Thank you in advance expecially for nigelrivett :) |
|
|
|
|
|
|
|
|
Database unon