Copy binary table data from diff servers

Today, before we start an internal demo of our project, we realized that, on a particular table there is no data in the QA server. We have every thing ready, but no the sample data. We just can’t create an insert script from the table and execute the script there, because, there is a column which is binary type. When we generate the script, the binary data type is not able to populate. Then we are struck with a big question of how to transfer the data between servers.

Let me explain you much in detail about our infra structure.

We have a dev DB server for our internal development hosted on 192.168.2.10 <<local ip address; for our convenience >>

We have a test bed for our application and IIS installed on it, let’s say this is 192.168.3.10, which is altogether a different network. And this IIS is connecting to a TestDB Server (let’s say it is hosted on 192.168.3.20), where we migrate our DB scripts to create the database / tables / stored procedure / user defined functions  / blah .. blah .. what not.

Now, this IIS server(192.168.3.10) is connecting to the TestDB(192.168.3.20) on secured connectivity. Our network folks implemented a rule that the x.x.2.x series can’t talk to x.x.3.x series, but not the vice versa. [[This is the trick / loop hole here]] Now that we need to populate the binary data from a table, and we don’t have access.

What all the dev’s do in the begin is to a bing on how to access the remote server to copy data from table. We are not exceptional to this, and we did all kinds of searches. We also googled for the same, unfortunately we end up either creating the LinkedServers. As we couldn’t connect to the remote TestDB server from our DevEnvironment, we couldn’t establish the connectivity. And the our application DBs are not accepting to create a LinkedServer from TestDB server.

after doing lots of trails and found some easy way to populate the data from one server to another with out having to create a Linkage between the servers.

   13 Exec sp_configure 'show advanced options', 1

   14 

   15 Exec sp_configure 'Ad Hoc Distributed Queries', 1

   16 Reconfigure

   17 

   18 insert into UserProfile

   19 select * from OpenDataSource(

   20 'SQLNCLI',

   21 'Data Source=192.168.3.20;User ID=sa;Password=sa'

   22 ).GlobalWebContent.dbo.UserProfile

Pretty neat and simple solution, it took about approximately 4hrs to convince our SQL DBs to execute the above query. 

Comments

Post a Comment

Popular posts from this blog

Network Intrusion Detection using Supervised ML technique

Declaimer This is my initial version of the synopsis for PhD studies. There are plenty of things that i have to complete before i prepare the final version. Thus, this would be the alpha version. Due to the length of the blog post, will not be posting the entire draft. 1.Title of the thesis Network Intrusion Detection using Supervised Machine Learning Technique with Feature Selection 2.Introduction Intrusion Detection System, IDS, is a development which improve the network security and defending the information of the association. The IDS encourage the network chairman to recognize any malicious action on the network and alarms the executive to get the information made sure about by taking the appropriate activities against those attacks. An intrusion alludes to any unauthorized access or malicious usage of data assets. An intruder or an attacker is a true element that attempts to discover a way to increase unauthorized access to data, causes hurt or take part in different
Read more

Common mistakes by Interviewer

Now-a-days am conducting few interviews for our organization. While sitting in the other side of the table, I've visualized why most of the interviews fail with me. But when conducting these interviews, most of the candidates are attending with self confidence that they are suitable for the mentioned role and attend without preparation. When people do some real preparation, they tend to fail due to lack of awareness at the interviewer towards interviewing techniques. Having failed in many interviews, I've learned that the interviews fail because of the following... Reflection (or) Mirroring: The interviewer tends to see self within the candidate. The interviewer starts to compare self with the candidate and evaluate. This evaluation is totally personal. The yard stick for measuring the candidature is not generalized, but influenced.   Template (or) Checklist: The interviewer has a template of a questionnaire. The interviewer measures the candidate with respect to the list of st
Read more

Keep the system active, to avoid the auto lock

Most of the corporate offices will have a policy for screen locking. While working with a long running applications at background, like the automation scripts for testing the applications, it would be difficult to maintain the unlock state of the screen. Thus, we found a technique to keep the system active with a cheating technique. Below is the powershall scripts that keeps the system active ' I am clicking on NUMLOCK for every 5 seconds to keep the system awake Set objNetwork = CreateObject("Wscript.Network")'to get user name numOfHours=inputbox("Hi " & objNetwork.UserName & ", How many hours you want me to be awake? :->") 'time variable conatins the time in hours Set WshShell = WScript.CreateObject("WScript.Shell") 'Creating an object for clicking keys from keyboard numOfMilliSecs=numOfHours*3600*1000 'converting hours to milli seconds While numOfMilliSecs>0 'runs until tym becomes zero WshShell.Send
Read more