Recent content by RRinTetons

Excel from current Office 365. No Power BI tools in use. Data not added to Data Model. I’ll take a look at the link you suggested. - Richard Ray Developer, Data Analyst Jackson Hole, WY

I created an Excel workbook that queries a SQL Server database that returns around 500,000 to 600,000 rows. He then refreshes a Pivot Table references query data. You can successfully refresh the Pivot Table immediately after the refresh. When he saves the workbook and reopens it, though...

I have a client running a database on SQL Server 2008 R2 Standard. They are very concerned about processing power on the server box, and want to set up long-term auditing of logins. What is the lightest impact method to do that? Can a trace or Profiler session be set up from another server? Does...

PROBLEM SOLUTION Interesting discussions re INDIRECT. To recap my original problem, I had an #VALUE error when attempting to replace cell references with Structured Table References (STR's) in COUNTIFS & SUMIFS. As would be expected, the problem is nothing to do with either STR's or the...

My apologies, my forum posts are done via Dragon Naturally Speaking. I missed editing the COUNTIFS example to include the underscore in the name. As my keyboard skills are pretty badly deteriorated< I certainly use Intellisense, as well as other syntax helpers, when writing code or entering...

Can a Structured Table Reference (STR) be used in place of cell references in COUNTIFS formulas? I've got a coworker's workbook that takes several minutes to recalc and is taking up over a gb of disk. He's got hundreds of COUNTIFS in cells with multiple references that extend to row 1,048,576...

To answer my own question: MS Query as present in Excel 2010 and 2013 doesn't recognize named parameters. In my case, the client's proc accepts up to 12 arguments, only two of which are of interest in this application. As I couldn't use named parameters to pick out the ones I needed in Excel, I...

I'm looking for a tool to recommend to a client to help him visualize job schedules in SQL Server 2008 and 2012. He's got a LOT of overnight jobs that need to run in order to do closes, posting, ETL, backups, optimization, etc. He wants to be able to see what's scheduled when, and where he might...

Re PowerQuery —- Agree, 100%. Unfortunately, this client has an aversion to Microsoft PowerBI tools, so,I’m stuck with MS Query... I’ve made it work in the past, and recall the syntax being a bit arcane, so I’ll persevere. - Richard Ray Developer, Data Analyst Jackson Hole, WY

I tried this over the Office list figuring that MS Query was more an Eccel thing, but was directed over here, so... RRinTetons (IS/IT--Management)(OP)20 Dec 19 01:59 This query works fine in SSMS: EXECUTE [dbo].[myProc] @BeginTransactionDate = '2019-12-01 22:52:00',@EndTransactionDate =...

I don’t know, I figured that more people working with Excel would be likely to have used MS Query than would folks primarily working with SQL query syntax. As I indicated, the *SQL* part is fine, it’s calling it in MS Query to get the days into Excel that causes problems. Where would *you*...

This query works fine in SSMS: EXECUTE [dbo].[myProc] @BeginTransactionDate = '2019-12-01 22:52:00',@EndTransactionDate = '2019-12-5 22:52:00' When I change it to work as a stored procedure call in MS Query to move data into an Excel workbook I *thought it should look like: {CALL...

Yes, I have a dev environment, isolated on its own server, and machines from which to test, even. Can you suggest ways to get a client to replicate both Windows and SQL Server security across those machines such that they mirror the production environment, perfectly? I can do it on a network...

At least it seems like a failure to me. I have a backup job that includes three COPY TO commands to move most recent backups to other servers for archive and isolated processing needs. Two of the statements work perfectly wel, third one doesn't seem to work at all, but doesn't throw an error...

I have a client who, quite properly,has all SQL Server do’s set to Windows Authentication. I have a VPN connection, I can see the database server. I even have a Windiescaccountinntheirsystem. However being on a non-domain computer gives me no obvious (obvious to me, anyway) way to authenticate...