InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 12:41:54 GMT20http://www.sqlservercentral.com/Forums/Topic551557-60-1.aspx[quote][b]stevensonk (8/13/2008)[/b][hr]I have created many queries using Excel as my tool. Instead of stored procedures I use Views because they allow parameters. create a view in SQL and in the SQL box in EXcel you would put all the fields that you will be selecting from your View and then you can paramaterize. It works great![/quote]Excuse my ignorance please but how can I use parameters in a View. I have tried but it does not work. I remember in Access queries you could use "=[parameter1]" but that doe not work in Views. I have tried to use the "@parameter1" like we do in stored procs and it does not work. I would like to use views rather than stored procedures for ad hoc queries. You see, I have a client of whom I taught some managers to use views as adhoc queries.The next thing, David, I get a "subscript out of range" error when I use your code in Office 2003 and 2007. Canyou or anyone else help me on this. I think that using Excel this way is cool because I have to either copy the data from SQL Server after I ran the sp, view or just a plain query and paste it into excel or I have to export the data to Excel.Tue, 07 Sep 2010 01:13:57 GMTManie Versterhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxIf a procedure takes for example 3 parameters, then {CALL uspThreeParm (?,?,?)}The procedure might look like this :ALTER PROCEDURE uspThreeParm ( Parm1 int , Parm2 int , Parm3 datetime )AS....etcWHERE int is the datatype of the parameters 1 and 3, datetime is for the 3rd one.So the parametes will have to be supplied by entry via dialog boxes or by reference to cell locations in the sheetHope this helpsMon, 06 Sep 2010 05:22:31 GMTsgleesonhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxI tried various combinations of the above with one of our stored procs but each time I get an error message 'Invalid parameter number'. Any one got any ideas on how to resolve? ThanksMon, 06 Sep 2010 04:31:19 GMTsjb500-529772http://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxDid you try putting the parameter marker in the full format of the stored procedure escape clause?{Call uspGetEmployeeManagers (?) }Fri, 13 Aug 2010 21:59:51 GMTrhoubenhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxExcel is the analytical tool of choice for analysts everywhere. Anything we can do to help them use the tools they are familiar with is in everyone's best interest. The newer versions of Excel make this even easier whether you are looking at a view, SP, or a cube. Nice article, thanks.Fri, 13 Aug 2010 10:13:30 GMTDaniel Bowlinhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxPowerPivot is a free add in for Excel 2010. For those doing data analysis out of SQL databases or SSAS it's a game changer. The ability to handle datasets with millions of rows, to create linked tables from Excel to use in joins and the replacement of the vlookup to avoid huge amounts of recalculation all make it worthwhile.In general, as much as I grimaced over the ribbon interface I realized that it was only because I was one of the few nerds who had actually figured out where all the cool stuff was buried many layers down in menus, submenus and dialogs. The ribbon interface actually *does* help users I work with get at some of the more arcane functions and capabilities of the sprawling program Excel has become.Fri, 13 Aug 2010 08:43:05 GMTRichard Ray-222778http://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxTo the poster who mentioned Excel 2010...please share your observations. We're about to move up to this "because it's there". :/Fri, 13 Aug 2010 08:02:57 GMTOld SQL Newbiehttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxWhy not just create a view in the database?Assuming, of course, that a single SELECT statement can do the job.Fri, 13 Aug 2010 07:59:05 GMTStephen Hirschhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxI echo the idea that {Call <some stored proc(?,?)} works great for parameters. I have numerous sheets that use this idea to produce sophisticated interactive reports for users. They love it.As to why I would use stored procs over views--answer is mostly about performance and efficiency. There are times when a parameterized stored proc can return needed data a lot more efficiently than with a view. (For example, views that involve linked servers.) Also, security is better with a stored proc--the user has access to precisely what the stored proc returns, not other columns or rows that may be available in a somewhat generic view. I realize that the security aspect is relative--specific views can limit access as well, but stored procs offer greater flexibility.Fri, 13 Aug 2010 07:53:58 GMTbrichardsonhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxWarning - Accessing a SQL Server DB in this way from excel can be prone to causing blocks, maybe not as bad as from Access but something to watch out for!!!Fri, 13 Aug 2010 04:45:23 GMTleeahttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxMan, you should try this on Excel 2010...Fri, 13 Aug 2010 03:25:46 GMTricardo.ferreirahttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxhi,"excel with stored procedures" that opens the title of friend, thank you. I do not know English very well may have problems that I'm sorry for my expression. The topics discussed in this topic useful and easy. But foreigners like me (with little ability to speak and understand English) users will be happy if you add videos to my issues. Thanks again. I have written a Turkish follows. Bye.merhaba, "excel with stored procedures" başlığını açan arkadaşa teşekkür ederim. İngilizceyi çok iyi bilmediğim için anlatımım da sorunlar olabilir özür dilerim. Bu başlıkta anlatılan konu oldukça kullanışlı ve kolay. Ama benim gibi yabancı (ingilizce konuşma ve anlama kabiliyeti az olan) kullanıcılar için konularla ilgili videolar eklerseniz mutlu olacağım. Tekrar teşekkür ederim. Yazdıklarımın bir de Türkçesi aşağıdadır. Hoşçakalın.Fri, 20 Nov 2009 02:14:24 GMTkadirozgul80http://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxMuch joy. Really, you've no idea how much. It's like an epiphany.Thanks, David; you rule!Mon, 12 Oct 2009 08:30:57 GMTMichael Lysonshttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxHi Gosta,well spotted on the errors in the code, I agree the connection should just be opened once.@Ron, the Param1, 2, etc would in that code be variants so as to be able to handle any type of data since I didn't know what type of information the cells would hold. Obviously they'd need to be quoted or formatted correctly if they were strings or dates, etc, when constructing the query (or use ADODB.Parameter objects if you like which will take care of it for you!)LadyReader, did you get a chance to try it out?Mon, 12 Oct 2009 01:54:16 GMTStephen Byrnehttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxFor my purposes, all params will be strings.Sat, 10 Oct 2009 10:23:31 GMTLadyReaderhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxRon Do you ask Stephen and me?As Stephen says it depends.Dim param1 etc as string or as date dependant on what content to be expected in the cell.Look at the format in Excel.//GostaSat, 10 Oct 2009 02:39:31 GMTGosta Munktellhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxCan you post what you declared the variables as?Thanks,RonFri, 09 Oct 2009 14:20:27 GMTroncashhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxGosta, thank you for your comments.I will try Stephen's solution with your variation as soon as I can. I'm in the middle of something else right now, but first thing on Monday...Thanks to both of you!Fri, 09 Oct 2009 14:04:20 GMTLadyReaderhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxStephen great job.I saw your example of code which is an approach i like and have one comment:You open and close the ADODB.connection inside the loop which will cause extra overhead.Also I think you forgott the loop in loop wihileMy suggestion is included, however not tested as written.LadyReaderI am sorry I am bussy I cannot do the whole solution for you at this moment but hopefullyyou have something to work on.//Gosta___dim r as integer dim conn as new ADODB.Connection dim rs as adodb.connection dim comm as new ADODB.Command conn.ConnectionString='server=[your server name];initial catalog=[name of database];integrated security=sspi;' //or uid=xxx, password=yyyy if thats the way it's setup, I recommend you use integrated authentication if possible though. set comm.ActiveConnection = conn conn.Open r = 2 do Param1 = sheet.cells(r,1).value //value from column A Param2 = sheet.cells(r,1).value //B Param3 = sheet.cells(r,3).value //C query = '[Name of your Stored Procedure] @Param1=" & Param1 & ",@Param2=" & Param2 & ",@Param3=" @Param3' //note you have to make sure to quote string, format dates, etc yourself here. comm.CommandText=query set rs = comm.Execute result = rs('[name of field returned by sproc]') sheet.cells(r,6).Value - result //column F rs.Closer=r+1 loop while sheet.cells(r,1).value <>'' //or whatever you want to signify as a 'Stop' value; either a blank column A or something else. set rs = nothingcomm.Closeset comm=nothing conn.Closeset conn = nothingend subpublic sub TimerProc()//disable the timer until we are done.Application.OnTime Now+timeValue('00:01:00'),'TimerProc',false//run the updateDoQuery Sheet1 //or whatever sheet you use/re-set the timerApplication.OnTime Now+timeValue('00:01:00'),'TimerProc',trueend sub______Fri, 09 Oct 2009 11:33:35 GMTGosta Munktellhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxThanks, Stephen. I will try that as soon as I can. I appreciate the time you spent thinking about this!Fri, 09 Oct 2009 09:38:49 GMTLadyReaderhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxI use Excel to give users access to data all the time without having to use VB code. Instead of using Stored Procedures you can use Views and then Excel will let you use all kinds of parameters in the query, the only difference is instead of doing an exec stored procedure you have to do a select * from your view or identify the fields that you want like you would with a sql query but it works great and the users love it.Fri, 09 Oct 2009 09:37:06 GMTstevensonkhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxOk, although I would begin with a caveat that A)this maybe could be done on the server side mostly by storing the results in a table before putting them in the Excel sheet, whereby you could then just issue one query every x seconds to get your updates values, and B)that without doing this you are issuing a lot of queries to the server (one per row to be specific) that could cause performance issues and finally C) that there is probably a much better solution to be had from someone with more experience!First off, you'll need to modify the template file you are using to create the initial sheet to have a reference to an ADO library (ActiveX Data Objects).Also add a module to the Template like so (apologies for lack of formatting and also the // comments needs to be replaced with an apostrophe for VBA, had to put in // or else the code would not display right here. Also, you must replace all of the existing apostrohpes with double quotes for VBA.[code="other"]Public Sub DoQuery (sheet as WorkSheet) dim r as integer r = 2 do Param1 = sheet.cells(r,1).value //value from column A Param2 = sheet.cells(r,1).value //B Param3 = sheet.cells(r,3).value //C query = '[Name of your Stored Procedure] @Param1=" & Param1 & ",@Param2=" & Param2 & ",@Param3=" @Param3' //note you have to make sure to quote string, format dates, etc yourself here. dim conn as new ADODB.Connection conn.ConnectionString='server=[your server name];initial catalog=[name of database];integrated security=sspi;' //or uid=xxx, password=yyyy if thats the way it's setup, I recommend you use integrated authentication if possible though. dim comm as new ADODB.Command set comm.ActiveConnection = conn comm.CommandText=query conn.Open dim rs as adodb.connection set rs = comm.Execute result = rs('[name of field returned by sproc]') sheet.cells(r,6).Value - result //column F rs.Close set rs = nothingcomm.Closeset comm=nothing conn.Closeset conn = nothingr=r+1 while sheet.cells(r,1).value <>'' //or whatever you want to signify as a 'Stop' value; either a blank column A or something else.end subpublic sub TimerProc()//disable the timer until we are done.Application.OnTime Now+timeValue('00:01:00'),'TimerProc',false//run the updateDoQuery Sheet1 //or whatever sheet you use/re-set the timerApplication.OnTime Now+timeValue('00:01:00'),'TimerProc',trueend sub[/code]finally in 'ThisWorkBook' object add code for WorkBook.Open()[code="plain"]private sub WorkBook_Open()Application.OnTime Now+timeValue('00:01:00'),'TimerProc',true //every 1 hourend sub I have left out all error handling code and whatnot, also you will probably want to test this..cos I didn't, I'm running out the door in a few minutes to get nicely toasted for the weekend :)anyway maybe it will keep you going until someone gives you the "real" solution!Fri, 09 Oct 2009 09:31:16 GMTStephen Byrnehttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxsgleeson,How do I ensure that the rows retrieved with the 2nd sproc match up with the rows retrieved from the first sproc? Even if I assume the user won't add any new rows, I still feel I need to ensure that the date returned is the correct date for that particular row. Can one attach a qry to a particular row or cell, isntead of to the spreadsheet itself, like with a function, where, for example, every cell in a column can run the same function but use as input data from its own row?Fri, 09 Oct 2009 09:24:53 GMTLadyReaderhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspx[quote][b]LadyReader (10/9/2009)[/b][hr]I have a spreadsheet, created via .Net automation, and populated from a sproc, with one column left blank (the sproc supplies the column header). Now I need to continuously update that column with the result set of another sproc, using as input parameters the values in 3 columns returned by the first sproc. That is, the values in A2, B2 and C2 will be used as parameters to populate F2, and A3, B3 and C3 will be used to call the same sproc to populate F3, etc...The spreadsheet is re-populated via the first sproc once each morning, but the 2nd needs to be run every x minutes. How can I accomplish this? Thank you for all suggestions and explanations, in advance.[/quote]Suggest creating a proc that selects the data that populates ABC colums and then joins to get the results required in Column F and that is then Data External into a new sheet with data range property set to refresh every x minutes.Fri, 09 Oct 2009 09:17:12 GMTsgleesonhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxAnd well played also. Not one to give up easily however! You can set refresh every x minutes in Data Range properties (agreed can't do seconds). If it is a fixed or formula determinable parameter, and the refresh is in minues, then I think it could be done without VBA, but I am not surei uderstand the requirement other then the periodic refresh.Fri, 09 Oct 2009 09:08:08 GMTsgleesonhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxStephen,Mostly no - that is, except for the initially blank column, all the other columns are populated by the first sproc, and are not changed by the user. The exception (there's always an exception, right?) is that the user may want to add their own entire rows. I would be happy to initially ignore this complication and just get my first coding challenge solved.So, how do I approach this?Thanks again.Fri, 09 Oct 2009 08:58:16 GMTLadyReaderhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxI'm pretty sure you could avoid the VBA altogether if you replace the Stored Procedure with a parameterized table-valued User Defined Function.Fri, 09 Oct 2009 08:50:58 GMTTom Garthhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspx@LadyReader - will the values in the first 3 columns be changed by the user? I presume then you know what they say about assumptions...Fri, 09 Oct 2009 08:49:19 GMTStephen Byrnehttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxFascinating thread -- I do have one note of caution If you plan to use the ODBC connection for anything else, you should know that this approach will introduce minor corruption in that connection.The problem is that when you start at the beginning of the wizard -- selecting your owner and database, that information gets stored as default in the user registry. If you go to the ODBC adminstration you will not see the local user modifications, but if you use the ODBC from another program then you will connect with the new default owner/db. MOst programs try and account for that and they do a 97% job. SO the other program will work 97% of the time, and exhibit sporadic odd problems.There are 2 work-arounds. 1) Use an ODBC created solely for Excel. THis has an additional advantage in that you can repoint the ODBC without change if anything in Excel2) Accept defaults in the wizard, picking a random table from master to start, then remove that table in Query Analyzer and either add your tables their or simply reference them in the SQL pane.(also -- while I have used the underlying technique you mention in excel 2003 for years with no issues, I find that excel 2007 has a strong tendency to corrupt the excel files -- strong enough that I always maintain a formal backup).Fri, 09 Oct 2009 08:46:23 GMTVrosenberghttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspx[quote][b]sgleeson (10/9/2009)[/b][hr]Indeed, I guess I just prefer to keep VBA out of the picture for security compliance where possible. You can wrap the view with a patameterized proc and call the proc from excel using parameters without VBA[/quote]Well played sir, well played...I agree 100% that your method is the simplest way of doing it, but I think VBA has its place - case in point, I reckon it will be necessary for LadyReader's problem - then again, I would genuinely love to see a solution that didn't require any code on the sheet.As regards getting the sheet to execute a refresh at a specific time, I would do this-Add a Module to the Workbook.-Define a procedure that refreshes the queries.-In the WorkBook_Open() procedure, add in[code="plain"]Application.OnTime Now+TimeValue("00:00:05"), "nameofyourprocinmodule"[/code] (to execute every 5 seconds)then in the module procedure, after your work is finished, re-set Application.OnTime in the same way.Fri, 09 Oct 2009 08:42:07 GMTStephen Byrnehttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxGosta,Thank you for your offer. Any method is acceptable, as long as it works. We are not talking about a huge amount of data, 1 date to be returned for each of maybe 100 rows.Thank you.Fri, 09 Oct 2009 08:30:37 GMTLadyReaderhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxMaybe better to do the work on the SQL side? If not maybe you could establish a zone on the sheet that combines the results of the ,Net automation one and the more frequent query, but I don't think an call to external data can update an area that is feed by another external query.Fri, 09 Oct 2009 08:28:08 GMTsgleesonhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxLadyReaderIs it OK if I do it in VBA for you? I am bussy at the moment so please let me know if my helpis accepted.//GostaFri, 09 Oct 2009 08:27:01 GMTGosta Munktellhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxIndeed, I guess I just prefer to keep VBA out of the picture for security compliance where possible. You can wrap the view with a patameterized proc and call the proc from excel using parameters without VBAFri, 09 Oct 2009 08:24:26 GMTsgleesonhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxI have a spreadsheet, created via .Net automation, and populated from a sproc, with one column left blank (the sproc supplies the column header). Now I need to continuously update that column with the result set of another sproc, using as input parameters the values in 3 columns returned by the first sproc. That is, the values in A2, B2 and C2 will be used as parameters to populate F2, and A3, B3 and C3 will be used to call the same sproc to populate F3, etc...The spreadsheet is re-populated via the first sproc once each morning, but the 2nd needs to be run every x minutes. How can I accomplish this? Thank you for all suggestions and explanations, in advance.Fri, 09 Oct 2009 08:15:39 GMTLadyReaderhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspx...and that's where VBA helps you out because you can dynamically construct a WHERE clause for your Select from the view, with "Parameters"Fri, 09 Oct 2009 07:54:20 GMTStephen Byrnehttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxYes, but without parameters. A View works just like a table. so the SQL could be as simple as SELECT * FROM [ViewName] in the MS query, or simpler still just select the View name from the pick list.Fri, 09 Oct 2009 07:32:29 GMTsgleesonhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxdoes anybody know if excel can work with views? Or will sps be the only recourse in that situation?Fri, 09 Oct 2009 07:27:49 GMTdb042188http://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxBy using {Call [Proc Name] (?)} you can still use the native parameter handling without VBA code.Fri, 09 Oct 2009 05:38:55 GMTsgleesonhttp://www.sqlservercentral.com/Forums/Topic551557-60-1.aspxI've been using Excel as the presentation layer of my reporting for ages now, I just found it to be quick and versatile, plus people can play around with the data as much as they want.As regards parameterised queries, I always use an SP and then manually set the parameters in the Before and After Refresh methods of the WorkSheeti.e in the "ThisWorkbook" code:[code="plain"]dim withevents qry as QuerytablesPrivate Sub Workbook_Open()Set qry = Sheet1.QueryTables(1)End SubPrivate Sub qry_BeforeRefresh(Cancel As Boolean)'get your parameters here e.gx = Sheet1.Cells(1,2).Valuey=Sheet1.Cells(1,3).Valueqry.CommandText="ReportServer..MyProc @x=" & X " ,@y=" & yEnd SubPrivate Sub qry_AfterRefresh(ByVal Success As Boolean)'put formatting and post-processing code hereEnd Sub[/code]I find this gives you total control over how the query refreshes and where the params come from - for example you can display a custom dialog box when the user clicks "Refresh" whereby the parameters for the query might be selected from a list generated by another query. Also allows you to properly save the formats of the sheet (code required to do this) and so onPlus is allows me in the formatting and processing code to do things you can't easily do when you're using an auto-formatted report, like maybe doing summing or averaging by some weird method which your users insist you do. Sure, you have to manually handle any grouping code, but it's not that hard once you get into it.Fri, 09 Oct 2009 03:26:08 GMTStephen Byrne