March 18, 2008 at 4:25 am
This works for me, Ahmad - c:\CambridgePubs.xls is created - and Excel isn't installed on the server.
Either there's a mistake in your sp, there's a problem with the driver (or the declaration of it) or there's a security issue here?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 18, 2008 at 10:12 am
Hi Chris...I've used the same code that I showed.Well....I've to figure out the problem ..wish me luck 🙂
do u have any solution...
Regards,
[font="Verdana"]Sqlfrenzy[/font]
March 18, 2008 at 11:06 am
Good luck, Ahmad!
To be honest, this isn't how I write to Excel using SQL Server, as I mentioned earlier. I use TSQL to copy/rename an Excel template, then server linking to edit it. There's no particular reason for choosing this over the other methods.
Here's the code to copy the template...
-- Make a new file from the template
DECLARE @Today VARCHAR(10),
@OldFile VARCHAR(100),
@NewFile VARCHAR(100),
@CMD VARCHAR (1000),
@return_status int
SET @Today = REPLACE(CONVERT(VARCHAR(10), GETDATE(), 102), '.', '')
SET @OldFile = '\\ixnuflawLRS1\D$\SLA Reporting\Templates\SLA_DashboardTemplate.xls'
SET @NewFile = '\\ixnuflawLRS1\D$\SLA Reporting\' + 'SLA_Dashboard' + @Today + '.xls'
SET @CMD = 'COPY "' + @OldFile + '" "' + @NewFile + '"'
IF OBJECT_ID('tempdb..#Output') IS NOT NULL
DROP TABLE #Output
CREATE TABLE #Output (CopyResult VARCHAR(500))
INSERT INTO #Output EXEC MASTER..XP_CMDSHELL @CMD
IF (SELECT COUNT(*) FROM #Output WHERE CopyResult = ' 1 file(s) copied.') = 0
BEGIN
RAISERROR ('Template file WAS NOT copied.',0,1) WITH NOWAIT
RETURN
END
Here's the sp for creating the server link...
CREATE PROCEDURE [dbo].[CreateExcelLinkedServer]
@ServerName VARCHAR(30),
@TabName VARCHAR(30),
@xlsFileName VARCHAR(100)
AS
DECLARE @Retval int, @MsgString VARCHAR(400)
SET NOCOUNT ON
-- If the link already exists, then drop it first...
IF OBJECT_ID('tempdb..#Worksheets') IS NOT NULL
DROP TABLE #Worksheets1
CREATE TABLE #Worksheets1 (srvname varchar(20), providerstring VARCHAR(50))
INSERT INTO #Worksheets1
EXEC('SELECT srvname, providerstring FROM master.dbo.sysservers WHERE srvname = ''' + @ServerName + ''' AND providerstring = ''Excel 8.0;''')
IF (SELECT COUNT(srvname) FROM #Worksheets1 WHERE srvname = @ServerName) > 0
EXEC sp_dropserver @ServerName, 'droplogins'
DROP TABLE #Worksheets1
-- Attempt to create the link...
EXEC sp_addlinkedserver @ServerName,
@srvproduct = '',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc = @xlsFileName,
@provstr = 'Excel 8.0;'
EXEC sp_addlinkedsrvlogin @ServerName, 'false'
-- Validate the linked server by checking that the tab is available...
IF OBJECT_ID('tempdb..#Worksheets') IS NOT NULL
DROP TABLE #Worksheets
CREATE TABLE #Worksheets (TABLE_CAT varchar(20), TABLE_SCHEM VARCHAR(20), TABLE_NAME VARCHAR(20), TABLE_TYPE VARCHAR(20), REMARKS VARCHAR(20))
INSERT INTO #Worksheets
EXEC sp_tables_ex @ServerName
IF (SELECT COUNT(TABLE_NAME) FROM #Worksheets WHERE TABLE_NAME = @TabName) = 0
BEGIN
SET @MsgString = 'Tab [' + @TabName + '] or Excel file [' + @xlsFileName + '] not found, linked server [' + @ServerName + '] not created.'
SET @Retval = 1
END
ELSE
BEGIN
SET @MsgString = 'Linked server [' + @ServerName + '] created, with tab [' + @TabName + '].'
SET @Retval = 0
END
RAISERROR (@MsgString, 0, 1) WITH NOWAIT
RETURN @Retval
GO
Here's an example of calling the sp:
EXECUTE @return_status = dbo.CreateExcelLinkedServer 'SLA_Dashboard', 'Results$', @NewFile
Here's an example of updating the spreadsheet:
UPDATE [SLA_Dashboard]...[Results$]
SET D = @month6, E = @month5, F = @month4, G = @month3, H = @month2, I = @month1
WHERE [A] = '2.7'
Here's a more interesting example:
UPDATE cl SET Vendor_Code = hv.Lawson_Vendor
FROM [SUPPLIERS_CLEV]...[Sheet1$] cl
INNER JOIN Staging_HospitalVendors hv (NOLOCK)
ON hv.ACCNT_REFNO = cl.Supplier_Refno AND LEFT(hv.Hospital_Code, 2) = cl.[Hosp_Code]
WHERE Vendor_Code is null AND hv.Lawson_Vendor IS NOT NULL -- 659
Finally, I don't have an example to hand right now, but you can use the content of one spreadsheet to update another.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 18, 2008 at 12:05 pm
Just my 2 cents... I'm always pretty much amazed at such requests... I do one of two things in most cases where someone "has to have it" in an Excel file...
I make a CSV file and write a spreadsheet macro to import it... macro is an auto-run macro so when the user opens it, it runs...
... or I write a view in the database and have the spreadsheet do an external datasource query with an auto update on load and then once every couple of minutes depending on what the customer's needs are.
What I really like about the second method is I never have a user calling me up saying "the data is a couple hours old and we need fresh data for a special analysis... could you run the aggregation code for me now?"
It makes it so I don't have to schedule anything, respond to early run calls, don't have to worry about making formatting changes because someone doesn't like a particular shade of blue, and I don't have to worry about giving anyone access to yet another disk repository. If they want a copy of the data to save each week, they can do that wherever they have access and I don't have to worry about it.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 18, 2008 at 12:40 pm
Jeff Moden (3/18/2008)
Just my 2 cents... I'm always pretty much amazed at such requests... I do one of two things in most cases where someone "has to have it" in an Excel file...I make a CSV file and write a spreadsheet macro to import it... macro is an auto-run macro so when the user opens it, it runs...
... or I write a view in the database and have the spreadsheet do an external datasource query with an auto update on load and then once every couple of minutes depending on what the customer's needs are.
What I really like about the second method is I never have a user calling me up saying "the data is a couple hours old and we need fresh data for a special analysis... could you run the aggregation code for me now?"
It makes it so I don't have to schedule anything, respond to early run calls, don't have to worry about making formatting changes because someone doesn't like a particular shade of blue, and I don't have to worry about giving anyone access to yet another disk repository. If they want a copy of the data to save each week, they can do that wherever they have access and I don't have to worry about it.
Color me lazy. [font="Arial Black"]<snooty_moment>Sorry, I dont DO excel macros </snooty_moment>[/font]
When I get a request to "get it in excel"....I give them a CSV. Since the "default handler" for CSV is Excel after they install it, it launches directly into Excel. Anything fancier than that comes in through a database query into Excel (open Excel sheet, and Excel goes and GETS the data).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 18, 2008 at 1:47 pm
It comes down to using a tool you feel comfortable with to do the job really doesn't it?
Jeff, you like playing with macros in Excel - I used to love playing with macros in Supercalc5 so I know where you're coming from.
Matt -
When I get a request to "get it in excel"....I give them a CSV. Since the "default handler" for CSV is Excel after they install it, it launches directly into Excel.
- no question, this is the "best" way for a simple unformatted report.
But...if you have an sp which copies a template file to a working file, and another which turns a tab/worksheet of that file into a virtual table so you can read/write as if it's just another sql server table - well, why bother writing a new macro every time you get a new report request? Just write the query, which you will have to write anyway...'cos (except for 'simple unformatted report') you've already preformatted an Excel file...
Oookaaaay I'm lazy.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 18, 2008 at 1:57 pm
Chris Morris (3/18/2008)
It comes down to using a tool you feel comfortable with to do the job really doesn't it?Jeff, you like playing with macros in Excel - I used to love playing with macros in Supercalc5 so I know where you're coming from.
Matt -
When I get a request to "get it in excel"....I give them a CSV. Since the "default handler" for CSV is Excel after they install it, it launches directly into Excel.
- no question, this is the "best" way for a simple unformatted report.
But...if you have an sp which copies a template file to a working file, and another which turns a tab/worksheet of that file into a virtual table so you can read/write as if it's just another sql server table - well, why bother writing a new macro every time you get a new report request? Just write the query, which you will have to write anyway...'cos (except for 'simple unformatted report') you've already preformatted an Excel file...
Oookaaaay I'm lazy.
I guess it's one of those "many ways to skin a cat": I would just open up excel in a "pre-defined template" like you mentioned, use Data, New database query... and then e-mail the excel file to whoever needs it.
Or - use SSRS.
Not knocking your method. Like I said - I don't "do" Excel macros, and have no intention of figuring out why they crash (like, say - due to "unsafe behavior", or macro security being to high, or AV killing macros, or a brain dead day while in macro-land, etc...). Especially not with a high-distribution spreadsheet.
Another method we've used (while we're on the topic): create it in HTML/ASP/ASPX, and change the HTTP headers to the Application:Ms-Excel content type. You have formatting, and you have a way to "publish it to the world". Send the link out, and they all get prompted for open/save. We actually already have a template for this in ASP, all we have to do is pass it a view, table or SP name....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 18, 2008 at 2:29 pm
Chris Morris (3/18/2008)
Jeff, you like playing with macros in Excel
Heh... no I don't... I hate the darned things... That's why I like the second of my two methods the best. 😛
I agree with Matt and you... writing Excel macros get's me all tangled up in my coffee IV. But, every once in a while, the guy in the corner office comes over to my desk, blocks my wait out (he's threating to put a lid on my cube 😀 ), and says "I know you're the only one here that really knows how to do this right and I need this done or I'm taking YOU out for porkchops..."
His slingshot is a lot bigger than mine... catch my drift? 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
March 19, 2008 at 12:12 am
Hi All,
That's gr8..We now have 4 ways to schedule the data extraction :-
1) DTS
2) Stored Procedure
3) Macros linked with Views
Will you guys help me to identify pros and cons of each of the method.
I think that the last one is the best(Macro linked with views).
🙂
Regards,
Ahmad
Regards,
[font="Verdana"]Sqlfrenzy[/font]
March 19, 2008 at 4:00 am
Hi Ahmad
Your original question arises so frequently that it it would be worth answering your last question in a formal manner, as a permanent reference. But for now, as I see it we now have the following options
1. DTS
2. Reporting services
3.
make a CSV file
4.
make a CSV file and write a spreadsheet macro to import it... macro is an auto-run macro so when the user opens it, it runs...
5.
write a view in the database and have the spreadsheet do an external datasource query with an auto update on load
6. Stored procs
a) Creating Excel spreadsheets via ADODB
b) Manipulating Excel data via a linked server
c) Manipulating Excel data using OPENDATASOURCE and OPENROWSET functions
d) Creating Excel spreadsheets using sp_MakeWebTask
e) OLE Automation
7.
create it in HTML/ASP/ASPX
In the last year I've used the following;
methods 1 and 3 quite a bit, they've been dead useful for passing data around between groups of people involved in a migration project. Less good for end-users because the output is somewhat "raw". Conclusion: quick and dirty.
6b extensively to UPDATE existing spreadsheets with server data based on data already contained within the spreadsheet, and also to write server data to an existing Excel template.
Advantages - very quick (once you have the components in place). Pretty enough for end-users. Powerful - can update or populate a single cell. Disadvantages - updating a single cell requires that row and column coordinates are available, sometimes requiring a data tab separate to the display tab.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 19, 2008 at 11:33 am
Thanks chris.. y dont u write an article for the same....:)
Regards,
[font="Verdana"]Sqlfrenzy[/font]
March 19, 2008 at 1:49 pm
Ahmad Osama (3/19/2008)
Thanks chris.. y dont u write an article for the same....:)
You're welcome, Ahmad. Hope it helps. I'm sure people will chip in with their expertise in the areas uncovered so far. Why don't I write an article? Because I'm a very very small fish, and this is a very big pond!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 25, 2008 at 5:11 am
Karel Walda (3/13/2008)
My guess / suggestion would be DTS Package;
Do you have any logic to support your answer :hehe:
Regards,
[font="Verdana"]Sqlfrenzy[/font]
March 25, 2008 at 7:15 am
Jeff Moden (3/18/2008)
His slingshot is a lot bigger than mine... catch my drift? 😛
LOL! Oh yes! 😀
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 26, 2008 at 6:15 am
Ahmad Osama (3/19/2008)
Hi All,I think that the last one is the best(Macro linked with views).
🙂
Regards,
Ahmad
How are you getting on with this, Ahmad? It would be interesting to hear which method you finally chose and how you fared with it 😀
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply