0

Using SQL Manager ver 18.4 on 2019 servers.

Is there an easier way to allow an end user with NO access to anything SQL related to fire off some SQL commands that:

1.)create and update a SQL table

2.)then create a file from that table (csv in my case) that they have access to in a folder share?

Currently I do this using xp_command shell with bcp commands in a cloud hosted environment, hence I am not in control of ANY permission or access, etc. For example:

declare @bcpCommandIH varchar(200)
set @bcpCommandIH = 'bcp "SELECT * from mydb.dbo.mysqltable order by 1 desc" queryout E:\DATA\SHARE\test\testfile.csv -S MYSERVERNAME -T -c -t, '
exec master..xp_cmdshell @bcpCommandIH

So how I achieve this now is allowing the end users to run a Crystal report which fires a SQL STORED PROCEDUE, that runs some code to create and update a SQL table and then it creates a csv file that the end user can access. Create and updating the table is easy. Getting the table in the hands of the end user is nothing but trouble in this hosted environment.

We always end up with permission or other folder share issues and its a complete waste of time. The cloud service Admins tell me "this is a huge security issue and you need to start and stop the xp_command shell with some commands every time you want generate this file to be safe".

Well this is non-sense to me. I wont want to have to touch any of this and it needs to be AUTOMATED for the end user start to finish.

Is there some easier way to AUTOMATE a process for an END USER to create and update a SQL table and simply get the contents of that table exported to a CSV file without all the administration trouble?

Are there other simpler options than xp_command shell and bcp to achieve this?

Thanks,

MP

mrpush
  • 1
  • 1
    An end-user should not be creating a table, so the premise of your question is mis-guided IMO and you need to reimagine your goal and how to achieve it in a **secure** fashion. Why your application or reporting tool cannot accomplish the generation of information and allow the user to export it to a file system that the user has access to eludes me. I am also not certain what "automate" means in that last part. Nor am i certain what "NO access to anything SQL related" means in this context as it seems to contradict much. – SMor Sep 17 '21 at 15:44
  • Hi, thanks for your input. In the real world however, applications changes cannot always be easily or financially accomplished and other options are necessary. I need to run SQL code against several tables to then create and then edit a final table to be exported. What "reporting tools" do you know of that will do that? The last part means "The end users are not going to be given any permissions to use or access SQL server". Thx. – mrpush Sep 17 '21 at 16:35

2 Answers2

0

Since the environment allows you to run a Crystal Report, you can use the report to create a table via ODBC Export. There are 3rd-party tools that allow that to happen even if the table already exists (giving you an option to replace or append records to an existing target table).

But it's not clear why you can't get the data directly into the Crystal report and simply export to csv.

MilletSoftware
  • 3,521
  • 2
  • 12
  • 15
  • Hi, ok so end users need to add a date range for inputs. The Stored procedure takes the date range, and uses multiple temp tables and then edits some of the results by adding records etc to a final table. That table needs exported to the CSV. I suppose they could just get the results in the crystal report then export but then its not fully automated and end users have to perform he extra steps in the export. They are already taxed with work overload, so full automation is desired to eliminate extra steps. – mrpush Sep 20 '21 at 14:33
0

There are free/inexpensive tools that allow you to automate/schedule the exporting/emailing/printing of a Crystal Report. See list here.

MilletSoftware
  • 3,521
  • 2
  • 12
  • 15