February 19, 2024 at 11:49 pm
Hello all,
Is it possible to either:
I have a user who uses Excel regularly to manage the setup of a production machine. I have built a browser interface through which he can edit/add the data, but when he needs to print it out (for others to read), he has a nice looking format...
So, I found something called the bcp command using the xp_cmdshell, but:
Any help is appreciated, thanks!
EDIT: I just ran the bcp command but cannot find the file on the server nor my machine (I am mistaken! I was logged onto the wrong server. Doh!). This is the output I got:
NULL
Starting copy...
NULL
28 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (28000.00 rows per sec.)
NULL
And the SQL of course!
DECLARE @FileName varchar(4000) = 'C:\Users\userName\Downloads\Data01.csv'
DECLARE @bcpCommand varchar(4000)
SET @bcpCommand = 'bcp "SELECT * FROM [ServerName].[gen].[DataBlockTest]" queryout ' + @FileName + ' -c -t , -r \n -S . -T'
SELECT @bcpCommand
EXEC master..xp_cmdshell @bcpCommand
And one more question: How do I handle spaces in the file path, or does it matter?
And one more: Will this command work with network shared drives? I have run it twice to put the file on the network, but got the same error both times:
Error = [Microsoft][ODBC Driver 17 for SQL Server]Unable to open BCP host data-file
The only difference is the file location. It worked on the server machine, but not the network.
OK, Update:
From my local machine, I can pull data from the SQL Server to the network or probably to my local machine via the CLI... However, I need an automated option for my users.
February 20, 2024 at 8:26 pm
One way of solving this problem is to create a view or stored procedure (depends on whether you need parameters) and then grant a group rights to execute the query. Then just do all this in Excel. File, Get External Data, SQL Server,.. Then the users would get the most recent data just by refreshing the data.
February 20, 2024 at 8:46 pm
I would lean towards what pietlinden said. separate the data movement from the formatting by querying it
February 21, 2024 at 1:10 am
What are your end users going to do with the file? The one thing that stinks about the Get Data option is that it "assumes" you're going to add it to a data model. that's fine if you're going to use DAX to analyze the data, but that's a big "if".
Other options... I guess use SSIS (shudder) to export a query or table to Excel. (but good because you can schedule it).
I was going to say use Access, but yeah, no. Too much of a hassle. Although I guess you could create a passthrough query to get the data, and then export to Excel from there... and then it's editable.
February 21, 2024 at 1:58 am
The Excel file is going to be used mainly for the users' viewing pleasure during meetings. I believe there are going to be two or three different sheets in total, for the various user roles. The 'data' on the sheet could be editable, I suppose, but it is not meant for saving back to the DB at all. If they want to make some temporary changes and print that, OK, but the real changes to the data are made via another application.
This data is just machine setup, basically placing numbers and letters in various positions on the machine (as in a conveyor, so there is an order to it).
I believe I can use a macro on a button event to refresh the power query, after the user selects the parameters to filter it by.
And as far as I know, that's it for this particular job...until someone wants it to do something else! 🙂
February 21, 2024 at 9:42 pm
It depends on what rights the users have on the database. Say you create a view or stored procedure and grant them execute rights only, you could create a source in Excel to run the stored procedure/view, and bring the data back to Excel. From there they can do whatever they want. then you'd just refresh the data just before giving it to them.
(and then they can go and get frustrated when they attempt to create a data model and some measures...) Yeah, just kidding.
if all they're doing is looking at the raw data, that should work fine, because you can declare/force data types and formats in Excel. And you can call a stored procedure really easily.I have a Word or PPT file somewhere that shows it, but the steps
EXEC dbo.MyStoredProc @param1='a', @param2=43;
and then when you refresh, that stuff will re-execute.
Oh, and do that in your preformatted workbook. (Well, it will dump to a table, not a range).
February 21, 2024 at 9:46 pm
One option I have used for these types of requests is an SSRS report. Then give the users access to the report and they can export to whatever format they want (Excel, PDF, Word). Add parameters to the report and they can generate for specific subsets - and you can even set it up so it creates separate worksheets based on groups.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply