Locating All Checked Out Excel Documents in SharePoint (WSS 3.0)
SELECT DirName, LeafName, tp_Email, CheckoutDate, IsCheckoutToLocal FROM AllDocs d WITH(nolock) INNER JOIN Webs w WITH(nolock) ON d.WebId = w.Id INNER JOIN Sites s WITH(nolock) ON w.SiteId = s.Id INNER JOIN UserInfo u WITH(nolock) ON (s.Id=u.tp_SiteID AND u.tp_ID=CheckoutUserId) WHERE d.Type <> 1 AND (LeafName LIKE '%.xls' OR LeafName LIKE '%.xlsx') AND (LeafName NOT LIKE '%template%') AND DeleteTRansactionId = CAST(0 AS VARBINARY) AND IsCurrentVersion = 1 AND IsCheckoutToLocal = 1 ORDER BY tp_Email ASC
Dynamic MDX queries in TSQL
Say you want to run the same MDX query for each row in a given rowset. I needed to do this for alerting purposes, where there were different alert thresholds for different attribute values in a given dimension attribute. After struggling with passing a variable to the query argument of the OPENROWSET command, I finally found the documentation that clearly stated that the query argument CAN'T be a variable. Or a concatination of a string and a variable. I still don't understand why... but the suggested workaround is to construct a giant TSQL string and run it using the EXEC command.
Ok - but how do we get the results of the query? Basically, the only way to do this is to create a temporary table in the current scope and do an INSERT INTO that temp table in your giant TSQL query. It all ends up looking something like this:
Social Networks
What I'm Doing...
- @joekieffer good luck! in reply to joekieffer 2 weeks ago
- @ben_p bah, i'm proud of him - that's a ridiculous name to have on a resume. give him at least 2 years before you start the peer pressure in reply to ben_p 2010-06-11
- @michaelfransen ha, I was pretty sure u were ignoring me ;-) ty. in reply to michaelfransen 2010-06-11
- Last day at W3i today - moving to Trek Bicycle Corp! 2010-06-11
- Anyone know how to mimic gears.geolocation.getPermission() with the html5 navigator.geolocation object? want to add a message to the dialog 2010-06-07
- More updates...
Recent Posts
- Excel 2007 Hangs When Trying to Edit an OLAP Cube Filter
- Locating Rampant Database Growth
- Script sp_configure Output To Migrate Settings Between SQL Servers
- Script RESTORE DATABASE … WITH MOVE Stub
- VersionOne Nag: Reminding Us To Burn Our Points