SQL Server 2012 - Insert into linked server table using openquery

Question

I have a linked server Remoteserver containing a table that holds file and folder names from a dir

When I am on the remote server I can run a built in procedure (xp_dirtree) and populate the 'files' table but what i need to do is to run a query from the local SQL server that does this:

Delete all records from the [Files] table on Remoteserver

Insert data that comes from the stored procedure:

INSERT [Remoteserver].[dbo].[files] (subdirectory,depth,isfile)
   EXEC master.sys.xp_dirtree '\\Fileserver\DBBackup',1,1;

Select the 'subdirectory' column

I tried some things using openquery and i am able to select existing records but unable to do the insert.

Any help is appreciated.

can you include the error message you're getting? it looks to me like your table name is missing a part. To be a valid remote query it should have four parts: Remoteserver.dbname.dbo.files for example — gordy
– gordy, Commented Jan 9, 2015 at 5:59
To be honest i am not quite sure about the right syntax to be used. I tried some variations of the openquery but i am sure thay are not right cause they didnt work :)...anyways the remote table name is as follows:[Remoteserver].[dbo].[files]....To clarify i dont need to pull the data into an existing table on the localserver...i Just need to display it on a web app so everything that needs to happen should happen remotely...the only thing that happens locally is the openquery that displays the data from the remoteserver 'files' table — Kiran Reddy
– Kiran Reddy, Commented Jan 9, 2015 at 6:06
INSERT OPENQUERY([Remoteserver], ' SELECT id,subdirectory,depth,isfile FROM [Remoteserver].[dbo].[files] ' ) VALUES ('EXEC master.sys.xp_dirtree ''\\fileserver\DBBackup'',1,1') — Kiran Reddy
– Kiran Reddy, Commented Jan 9, 2015 at 6:10

Andrey Morozov · Accepted Answer · 2015-01-09 08:26:25Z

7

Try this

INSERT INTO OPENQUERY([Remoteserver]
    ,'SELECT subdirectory, depth, [file] FROM [RemoteDB].[dbo].[files]')
EXEC master.sys.xp_dirtree '\\fileserver\DBBackup', 1, 1;

OR

INSERT INTO OPENQUERY([Remoteserver]
    ,'SELECT subdirectory,depth, [file] FROM [RemoteDB].[dbo].[files]')
select * from OPENQUERY([another_server_name], 'master.sys.xp_dirtree ''\\fileserver\DBBackup\temp'', 1, 1');

But in general you do not need to use OPENQUERY at all if Fileserver and Remoteserver are accessible from the local machine.

INSERT INTO [Remoteserver].[RemoteDB].[dbo].[files] (subdirectory, depth, isfile)
   EXEC master.sys.xp_dirtree '\\Fileserver\DBBackup',1,1;

edited Jan 9, 2015 at 8:26

answered Jan 9, 2015 at 6:30

Andrey Morozov

8,0175 gold badges59 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kiran Reddy Over a year ago

Thanks Andrey! the first query worked...I have a question about the other 2 methods you mentioned...The localserver and remoteserver are in 2 different networks separated by a firewall and also the fileserver exists in the same network as the remoteserver so if i choose to use either the 2nd or the third method would it be the localserver that is executing the 'dirtree' command against the fileserver?

Andrey Morozov Over a year ago

not at all. if you are going to use OPENQUERY it might be any other server that is configured as linked server, but this may require the MSDTC to be enabled on each server (check this stackoverflow.com/questions/27726567/…)

Kiran Reddy Over a year ago

thank you for the link...I have a followup question related to the above so asking it here.... I have 3 commands in my stored procedure - first one deletes all records from a table , second inserts new records and third pull those records but at the moment when the query runs the delete works but i dont get the records from the 3rd statement which is the "select" ....I am unable to post the code here because it is too long for the comment block.

Andrey Morozov Over a year ago

it's bad. for now I can't help you without any information about the problem details. and for sure discussing your new problem here is definitely off-topic... you can try to figure it out or you could ask a new question...

Collectives™ on Stack Overflow

SQL Server 2012 - Insert into linked server table using openquery

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related