0

I have an SQL Server query and I am using FOR XML PATH to generate the result as XML. I am trying to save the file result.xml to a particular folder of my computer but I do not want to use the "save as" link of SQL Server to do it, because I need to automate my process.

Unfortunately, I cannot use xp_cmdshell solution provided here because I do not have the permissions to do it. In fact I get an error

The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'

each time I try to execute it.

May anyone help me with that? It would be highly appreciated!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
N.Doe
  • 1
  • Hi, not clear to me. do you want to use xp_cmdshell and need help with the error, or do you want to find other solutions? most of the solutions which based on writing the file directly from sql server will require high/special permission (I can think about at least 5 different solutions probably). – Ronen Ariely Jun 28 '18 at 18:29
  • Hi, thank you for your answer. In fact, I tried to find solutions in order to use use xp_cmdshell but it seems that since I do not have high permission it wont be possible, unless you can help me with that I would say that I other solutions are more than welcomed ! – N.Doe Jun 28 '18 at 18:35
  • The interface in stackoverflow does not really give the option to have a discussion. I can only add one-line comment or final answer, which can come only at the end of the discussion :-(. It might take log time to solve the issue using this interface and one-line messages. Lets me first mention some other options, but as I said most of these need high permission or unique permissions. Check if you think that one of these can fit better and you know to implement: (1) SQLCLR (2) Python (3) xp_cmdshell (4) sp_send_dbmail + Google Apps (5) SSIS... – Ronen Ariely Jun 28 '18 at 18:44
  • Now, at the time that you think about other options which I mentioned we can go back to the xp_cmdshell issue. (1) are you the administrator of the machine or do you have option to ask the admin to do what is needed? (2) what is your level of permission in the SQL Server - are you sysadmin, simple user.. ? – Ronen Ariely Jun 28 '18 at 18:47
  • Thank you again for your help. I would like to emphasize first that I am not an extensive user of SQL. I am the administrator of the machine I think but certainely not on the database on which I am executing my queries. Concerning the options you listed one question what is SQLCLR ? Python is not an option since I never used it before and that I do not have unfortunately the time to do for this special task. I think that i should check first the topic you send for xp_cmdshell and see what I can do concerning my permissions. For google it will not be an option nether. – N.Doe Jun 28 '18 at 19:44
  • SQLCLR is the most powerful feature in my opinion since 2005. It was added to SQL Server 2005. You can read the introduction here: `https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/introduction-to-sql-server-clr-integration`, and if want to watch a lecture I gave at PASS global event SQLSaturday in 2015 then you can find it here: `http://www.sqlsaturday.com/360/Sessions/Details.aspx?sid=9834` (presentation and code available to download from Microsoft gallery). In order to use SQLCLR you need to know one of the Dot.Net languages like C# since CLR is related to .Net – Ronen Ariely Jun 28 '18 at 20:00
  • Check the answer I wrote. It seems in first glance that each solution you chose you will need to learn something and the forum is not the place to learn. You will need to choose your way and then start your learning (using google as first step in order to find good online/offline course). Basically the answer I wrote (and the links) should give the way to solve your needs. – Ronen Ariely Jun 28 '18 at 20:03
  • You're absolutely right! thank you for your help again ! – N.Doe Jun 28 '18 at 20:10
  • *You are most welcome!* It's always nice to speak with someone polite who remember to say thanks :-) I think that you can close the current thread at this time by marking the answer. If you will have any followup question we can discuss these as well – Ronen Ariely Jun 28 '18 at 20:13

2 Answers2

0

Good day,

like always there are lot of solutions which depend on your knowledge and resources. I will try too give you some ideas here and probably others will be able to add more

  1. Using SQLCLR you can simply create a file and save it (this will require using PERMISSION_SETUNSAFE)
  2. Using Python (basically same as SQLCLR we can do everything with Python including external tasks).
  3. You mentioned that you do not want to use xp_cmdshell at first but after the short discussion I understand that you simply do not succeed to use it, but this is your preferred option. Check this thread on the same topic. You should find the solution there on how to configure the permissions.
  4. Using sp_send_dbmail you can send the result of the query to email. Not like other options this is usually open in most servers. The idea is to send simple email, and using Google Apps you can read the content of the email and create file. You can use for example the methods: GmailApp.getInboxThreads, getMessages(), getRawContent()... and in order to write the file in your Google drive you can use DriveApp.createFile
  5. Using SSIS
Ronen Ariely
  • 2,336
  • 12
  • 21
0

Following your advice I took the time to learn how to use C# in order to solve my problem and now it works perfectly! I created a short script that executes my sql query using for xml path and then managed to save the resulting XML file in a specific repertory. Thank you again for the support. So my process is now fully automated. Always nice to learn more!

N.Doe
  • 1