March 9, 2016 at 8:59 am
I have access to 2 servers. 1 server is 100% access to do anything I want. The other is read and select only.
I need to take data that I query from the read only server and insert it to my server for reporting purposes. The data is very large and I find that querying, exporting to .txt only to import to my server is very time consuming and not efficient. I was reading through some help documentation in the SQL Server that I could link the two servers if I create an sp in the server I have full rights. Would this be the accurate code to perform that sp?
sp_addlinkedserver @server = N'DBP_DWA1\DWA1P'
GO
SELECT A.*, B.*
FROM LinkServer.HEDIS_RPT.DBO.HEDIS_MEASURE_DETAIL A
INNER JOIN LINKSERVER.HEDIS_DM.DBO.DIMMEMBER B ON
A.MEMBERKEY = B.MEMBERKEY
March 9, 2016 at 10:58 am
Hi Tina,
Welcome to the forums.
You don't make it clear from your question whether you NEED to replicate the data from the read-only server to the report server, or just access the data.
If you need to move it I would look at building a proper ETL process. SSIS is good for this but is by no means the only solution. If you just need to access the data then setting up linked servers is one option, however there is probably some work that needs to be done on the Surface Area Configuration of one or both servers. By this we mean that by default a server will normally be isolated and access to the database and instance are only available through management studio or an application making a connection on a specified user ID (Windows or SQL). When you open the server up to remote calls you have (can) to be very specific about what rights the external server has.e.g. Can it just read data tables and views or can it run stored procedures and functions; when you are fetching data and having to join it to other tables, does the join execute on the host server or on your client server etc..
This means that the answer to your question is not straighforward and we would probably need more context around the purpose of each server and what exactly you are trying to do and - more importantly - what you DO NOT EVER EVER EVER want to be able to do (e.g. run a remote procedure call that modifies data in the host server)
March 9, 2016 at 1:29 pm
Ok. What I want to do is extract the data elements I need from this SQL Server series of tables I have access to and put them in my database. The DBA of the SQL Server where I require the data will not open up the ability for me to create tables, even temps in his database, which I totally understand. I will not open mine up either. So at present this DBA just supplies these 612 .txt files and those files will increase as we pull in all lines of business. Right now we just have Medicaid and Medicare but my leaders want us to do the same for Commercial, FEP, and the Marketplace. Then the amount of time to get these .txt files and load, clean (format how I need that is consistent with my backend) is just very time consuming. The DBA of the read SQL Server gave me access because he said this is where I store all those massive .txt files. He said maybe you can use the database and that would be more efficient for what you are trying to achieve, which is automating this whole process. Create a SQL code, kick it off and leave and let it do it's thing. We have tried connecting with SAS but the problem is we go from our computer through VPN to the SAS Grid Server to the SQL DB and then back again. So we got time outs with every attempt because everyone and their mother using SAS has to go through the organizations Grid Server and it is just not fast at all. So now I am thinking since I have SQL Server and use the studio to do whatever in my database and then can do select queries in this other SQL Server database, I could just link with an sp and extract the data I need into a table on my side. Does that further explain? And thank you for the welcome.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply