Excel with Stored Procedures

Question

Excel with Stored Procedures

Viewing 15 posts - 31 through 45 (of 120 total)

You must be logged in to reply to this topic. Login to reply

David in .AU SSCommitted Points: 1784 More actions · Answer 1

This my friend, is a thing of beauty...

It always boggled the mind why MSQuery HAD to display things graphically for the parameters to work; anyhows I just gave this a whirl and it is good to see that it works a treat, I will be saving this to my templates folder 🙂

Eric L Hackett (8/13/2008)

I use Excel 2003 with stored procedures without involving VBA. The trick is to manually edit the .dqy file that MSQuery generates, or more accurately, just manually create the file and leave out MSQuery altogether. Its just plain text so Notepad works nicely. I personally use UltraEdit.

The format you want is:

XLODBC

1

DSN=myDSN;DATABASE=myDatabase;Trusted_Connection=Yes

EXEC dbo.myStoredProc ?, ?

prompt1prompt2

Matteo-317581 Old Hand Points: 338 More actions · Answer 2

What a good article. I also liked Eric's trick of manually creating the qry file. Very neat.

For pivot tables, one way to kind-of get parameter-ish functionality when using an external data source is to set the option on the “Page Field" fields to "Query external data source as you select each page field item”. You need to use the advanced button to access this option.

I have used this feature on occasion, usually to reduce the number of records returned.

Also by-the-by, I came across a very cool Excel Add-In Called "Pivot Play".

This tool allows you to see the connection and select text strings used by the pivot table as well as change them. It is the update feature of this tool that is really the great feature.

Here is a link http://www.contextures.com/xlPivotPlayPLUS01.html

Regards

Matteo

David.Poole SSC Guru Points: 76000 More actions · Answer 3

Years ago, sorry decades ago, there used to be a product from a company called Personics called Monarch.

What the product did was to take spool files off a mini-computer print queue and pattern match the report header, group levels and detail lines and extract data to a.n.other data source. The product of choice in those days being Lotus 123 and CA Supercalc.

It could actually perform fairly basic ETL (Extract Transform Load) functions and I must have been one of the products most rabid fans. It was exactly what the users wanted which was why Mr LongPockets in accounts cracked open the worlds dustiest wallet, freeing several moths previously thought to be extinct, to buy a 2nd copy.

The comment about writing web reports only to have users type them into spreadsheets really ramps up the Deja Vu:hehe:

LinkedIn Profile

peter.abramoff Valued Member Points: 71 More actions · Answer 4

I just want to lift my hat off for David and his great article on Excel's capabilities with stored procedures. Kinda mind-blowing, really! 🙂

Haven't had the opportunity to test this in our SQL2000/Excel2003 environment but it would be really cool if it worked.

Best regards,

Regina Beauregard SSChasing Mays Points: 626 More actions · Answer 5

Peter I just want to assure you that it will work in your SQL2000/Excel2003 environement. I've used the Get External data feature of Excel with SQL7, SQL2000 and SQL2005. However, I don't use the stored procedures because I code prompts right into the query behind the spreadsheet.

😀

Swarm_Catcher SSC Enthusiast Points: 101 More actions · Answer 6

How do you eliminate carriage returns? I replaced a query with a stored procedure in MSQuery and get the same results, except for the additional carriage returns in the text fields.

Any pointers?

Ron Strang Valued Member Points: 74 More actions · Answer 7

Just got back off my hols. to see this topic.

I have been using various versions of Excel to provide reports from SQLserver data that is held by our ERP system (efacs by Exel computers if anyone is interested) for about 10 years now. My queries have increased in complexity as time has gone on...

The "Parameters are not allowed in queries that cannot be displayed graphically" is a pain, but you can work round it with views. I have been able to achieve an indented Bill of Material (BOM) query that relies on over half a dozen simple views - and I am continually surprised at how quick it is.

I prefer developing queries by using the SQL Query Analyzer. When things are working OK, I will cut and paste the query into Microsoft Query - test and then add parameters as required. If no parameters are required then the graphical display is not necessary, just OK past the warning. Microsoft Query is verbose, so I prefer to cut and paste back into Query Analyzer if I have to edit or modify. Creating the *.dqy file directly seems an even better idea ... thanks for that

A favourite is to pull some general data onto [sheet 1] and then analyse with several pivot tables: very quick to produce results.

I do prefer including the prompts as cells on the spreadsheet for the simple reason that this shows on the sheet what has been queried.

I have come across a problem with wildcard prompts in the transition from XL97 to XL2003 (I think it occurs in XL2000 too but haven't really worked with this version). Prompts with wildcards (eg %) always worked fine & then in XL2003 they stopped working with strange string truncation errors. I now try and work round it - it seems to be linked to varchar datatype in tables and string prompts. Using RTRIM in the query and keeping the parameter string no longer than the varchar length seems to work. Anyone have any better solutions?

My next stage is one that I am cautious about: updating into the database from a spreadsheet. Maybe the stored procedure approach is one that I will adopt - one that I haven't had to use so far - the views have given me all that I need.

Regards

Ron

nvesic Valued Member Points: 67 More actions · Answer 8

I use Excel 2003 with stored procedures without involving VBA. The trick is to manually edit the .dqy file that MSQuery generates, or more accurately, just manually create the file and leave out MSQuery altogether. Its just plain text so Notepad works nicely. I personally use UltraEdit.
The format you want is:
XLODBC
1
DSN=myDSN;DATABASE=myDatabase;Trusted_Connection=Yes
EXEC dbo.myStoredProc ?, ?
prompt1 prompt2
The first 2
lines are standard.
Line 3 is basic connection string info.
Line 4 is your EXEC statement with parameters listed as '?'s
Line 5 is a tab-separated list of parameter names, or prompts for Excel

Exact format of dqy file to be edited contains at least one more line containing codes of data types of parameters separated by tab character. Some of the code of data types are as follows

[font="Courier New"]

4 int

-5 bigint

11 datetime

1 char

-8 nchar

-9 nvarchar

12 varchar

-7 bit

3 decimal, money

6 float

[/font]

The last line of the dqy file is list of column names separated by tab character.

So in the previous example, assuming that first parameter of the procedure is int and second of type date should read.

XLODBC

1

DSN=myDSN;DATABASE=myDatabase;Trusted_Connection=Yes

EXEC dbo.myStoredProc ?, ?

prompt1 prompt2

4 11

Name Address Salary

Best regards[p]Nebojša Vesić[/p]

Eric L Hackett SSC Eights! Points: 978 More actions · Answer 9

I've found that the datatypes for the parameters are optional, and in my experience, this is with MSSQL Server 2005, the server handles the implicit conversions quite well.

Eric L Hackett SSC Eights! Points: 978 More actions · Answer 10

Swarm_Catcher (8/18/2008)
How do you eliminate carriage returns? I replaced a query with a stored procedure in MSQuery and get the same results, except for the additional carriage returns in the text fields.
Any pointers?

Manually edit the dqy file and remove any carriage returns.

Eric L Hackett SSC Eights! Points: 978 More actions · Answer 11

Matteo (8/14/2008)
...For pivot tables, one way to kind-of get parameter-ish functionality when using an external data source...

I found a satisfactory method of refreshing pivot data based on parameters. Using the methods I mentioned before, create a named query, with parameters. Then define a named list that simply points to the named query. Excel seems to handle adjusting the data range on the named query when the data is refreshed, but will not let you use the named query for a pivot data source. Well it will, but tells you it is invalid when you try to refresh. However, if your pivot datasource is a named list referencing the named query, just hit the PivotTable refresh button after a refresh of the named query has taken place.

skitter Valued Member Points: 59 More actions · Answer 12

Anyone knows how to change the db connection (I use a different database each month) ? Or at least make Excel forget what's in the ODBC ?

Matteo-317581 Old Hand Points: 338 More actions · Answer 13

skitter

take a look at my earlier post on Pivot Play

>>>

Excel Add-In Called "Pivot Play".

This tool allows you to see the connection and select text strings used by the pivot table as well as change them. It is the update feature of this tool that is really the great feature.

Here is a link http://www.contextures.com/xlPivotPlayPLUS01.html

>>>

Old SQL Newbie Ten Centuries Points: 1060 More actions · Answer 14

Thanks for the article, I set it up in Excel 2007, however I can't find a way to send a parameter through to it or link a parameter to a cell in the spreadsheet. When I have a parameter and refresh, it tells me a parameter was not supplied. When I remove the parm from the SP, it refreshes just fine.

david0044 SSC Veteran Points: 206 More actions · Answer 15

Thanks for the interesting discussion on this topic. It's good to know I'm not alone in working these issues out. That said, I have a slightly different twist...

I'm trying to pass a parameter from excel 2007 to a stored procedure in sql server 2000. Instead of starting with MSQuery, I am trying to modify the command text of my already existing data connection. My command type is SQL and my command text is exec proc_exceltest. If I pass the command with no parameter, it works fine. If I pass the command with a hard coded parameter (e.g. '12/01/2008'), it works fine.

However, as soon as I try and pass a cell reference (e.g. Sheet1!A1) it either passes the literal value (if enclosed in quotes, or I get an error. Is there any syntax to tell excel/sql that the value is a reference and not just another text string?

Thanks in advance for your collective wisdom.

David