0

I have two separate servers: one with the database (server 1) and one with some files that I want to insert in the database (server 2). Both are using Windows Server OS. I can't figure out how to use Bulk Insert from server 1 on the files from server 2. I can't copy files from one server to another because they are constantly updated on the server.

There is a similar question BULK INSERT from a different server but I need a step by step procedure how to connect to the server 2 from SSMS on server 1.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Alexandr Kapshuk
  • 1,380
  • 2
  • 13
  • 29
  • 1
    " I can't copy files from one server to another because they are constantly updated on the server." - but then how will you lock them in order to insert coherent data? – Mitch Wheat Oct 30 '18 at 09:59
  • 1
    Why don't you want to use bcp or SSIS package? Both make bulk insert – sepupic Oct 30 '18 at 10:38
  • 1
    If you insist and want to use the method mentioned in your link, you should give the access to SQL Server service account to another server's disk (make it shared and give permissions to SQL Server account) – sepupic Oct 30 '18 at 10:40
  • I'll use the ones that are already complete and closed by using try...catch. This is good enough for me – Alexandr Kapshuk Oct 30 '18 at 10:47
  • @sepupic I don't know about bcp and SSIS packages, this is probably what I needed to hear. I'll Google them – Alexandr Kapshuk Oct 30 '18 at 10:49
  • 1
    The advantage of bcp is that it uses **your** credentials (credentials of who launches it), not those of SQL Server. By given permissions to sql server service account you give them implicitely to any sysadmin(I mean sql server admin, not win admin) that now can access another pc even if he has no permissions on it. If it's not a problem for you I can describe how to give permissions to sql server service account – sepupic Oct 30 '18 at 10:53
  • 1
    The bcp is a command line command, right? So I'll use something like `exec master..xp_cmdShel bcp [enter credentials to get to the server with files, query text as string with the bulk insert statement]`? – Alexandr Kapshuk Oct 30 '18 at 11:12
  • The permission to access the server from the query is not a problem, it's an internal query. "If it's not a problem for you I can describe how to give permissions to sql server service account" - sure, I'll be happy to know that! – Alexandr Kapshuk Oct 30 '18 at 11:14
  • I managed to make this work: `bcp "##delete" in "path to the file from which I insert data" -d database_name -S localhost -T -f "file specifying the format"` but when I substitute `-S localhost` with `-S "00.00.00.00" -U "username" -P "password"` I get "Server is not found or not accessible" error. If there's no error in the format (like I shouldn't put server address inside "") then I'll contact the administrator. – Alexandr Kapshuk Oct 30 '18 at 12:12
  • >>>Server is not found<<< This can be firewall but it can be also the NAMED instance that listens on non default port. You should use the result of select @@SERVERNAME as server name – sepupic Oct 30 '18 at 12:40
  • 1
    i have no idea what that is... I tried this: `bcp "##delete" in "path to the file from which I insert data" -d database_name -S "result of select @@servername which is a string of 15 characters" -U "username" -P "password"` with and without -T parameter, and I still get the same error. Maybe I should combine the IP address and the @@servername somehow? – Alexandr Kapshuk Oct 30 '18 at 12:52
  • Is your "string of 15 characters" a default instance (contains no "\" in it) or a named instance that looks like myPCname\myInstanceName (with slash in it)? – sepupic Oct 30 '18 at 13:02
  • Yes it's default instance, so it's IP is sufficient. Try to ping your IP. It can still be firewall or tcp\ip disabled for this server – sepupic Oct 30 '18 at 13:19
  • I used `ping 00.00.00.00` in cmd and i got "Request timed out." four times – Alexandr Kapshuk Oct 30 '18 at 13:22
  • Are you sure you have 2 servers on the same network? – sepupic Oct 30 '18 at 13:23
  • 1
    OK I'll definitely have to talk to someone who knows more about these servers. After I get access to one server from another, I'll be able to run the bcp command (cause it runs OK on localhost). Thank you very much and sorry for depraving you from the joy of finally getting through all the problematic details and reaching the result:) – Alexandr Kapshuk Oct 30 '18 at 13:31

2 Answers2

1

The permission to access the server from the query is not a problem

I was talking about another thing, not about your query accessing another server.

Once your server service account has permissions over network, any sysadmin will be able to use SQL server to access the other server.

So to make your server "see" a fileshare you should first find out the account of SQL Server, you can see it using Services, Configuration Manager or by executing xp_cmdshell 'whoami'.

These are corresponding pictures:

  1. Using Windows Services. Locate the instance you are interested in under Name and the corresponding account under Logon As. In my case it's LocalSystem (as it's my personal pc) but in your case it should be a domain account that will access a fileshare

enter image description here 2. The same thing using CM.

enter image description here

  1. By executing code

enter image description here

Now when you found out the service account you should give it permissions on fileshare same way as you do it for other users.

Community
  • 1
  • 1
sepupic
  • 8,409
  • 1
  • 9
  • 20
0

What you're looking for is called Linked Servers. But let me warn you that bulk insert across servers can be a very expensive due to latency! you can use regular commands with a extended syntax such as INSERT INTO myLinkedServer.myOtherDatabase.dbo.clients blablabla

Leonardo
  • 10,737
  • 10
  • 62
  • 155
  • But I don't have SSMS installed on the server 2, it's only on server 1. Server 2 has just text files. Will I be able to insert these files in the DB on server 1 using Linked Servers? – Alexandr Kapshuk Oct 30 '18 at 12:34
  • yes you should be able to do that... the idea of linked servers is to treat as a single server... you can even perform joins (https://stackoverflow.com/questions/21137592/sql-linked-server-join-query) – Leonardo Oct 30 '18 at 13:53