April 5, 2013 at 12:22 pm
<obligatory newbie double-post above>
April 5, 2013 at 12:31 pm
jjturner (4/5/2013)
Thanks David - that was quick! I'll have a go at translating this into VBA and see what happens.Although it does look like there's no way around firing a bunch of SELECT statements with a cursor (unless I'm misreading it)...
Cheers,
John
Its not a cursor. It was a solution that was presented as an alternative to a cursor. If you're using VBA, I'd guess that you'll have to create the sp on the SQL Server side and then execute the sp from VBA. Using the SELECT @name = @name + [NameLong]... is something that won't be available on the Access side.
However, that being said you could create a recordset and then loop through each record to create the string, however the performance difference between that approach and the one I posted will be quite noticable.
April 5, 2013 at 12:58 pm
Ok, thanks for the confirm - now I'll have my work cut out for me trying to build a solution with ADO slinging around recordsets between Access and SQL Server!
April 5, 2013 at 1:42 pm
jjturner (4/5/2013)
Ok, thanks for the confirm - now I'll have my work cut out for me trying to build a solution with ADO slinging around recordsets between Access and SQL Server!
This code will update a pass through query that relies on a stored procedure that requires parameters. The idea is that you would create the sp in SQL Server, then create the pass through query in Access. Your code would then call this sub to update the pass through query to change the parameters before the pass through query is executed. This might be neccessary if you have form based on the pass through query in which case it would be called on the Form_Load event. Or it might be executed before you open a Select statement on the pass through query via .OpenRecord set.
strQryDefName - Name of the Access object that represents the passthrough query
strStatement - Actual statement to the update the passthrough query to, this must include any parameters such as...
Sample usage...
This code builds the parameters passed to the UpdatePassthroughQuery sub by looking up the RecordSource from a table (eg: view_UsageByDate) and then builds the parameters by looping through the controls on a form to determine if a value has been entered. It is a rather nifty approach to supplying criteria driven reports where the criteria is not known and needs to be flexible </tooHorn>.
If Me.pg1_lstReports.Column(3) = True Then
'Lookup the SQL Server view/recordsource to us
strRecordSourceName = Nz(DLookup("txtRecordSourceName", "qry_sp_getReportDefinitions", "[lngId] = " & [Forms]![frmReports]![pg1_lstReports]), "")
strRecordSourceStatement = Nz(DLookup("txtRecordSourceStatement", "qry_sp_getReportDefinitions", "[lngId] = " & [Forms]![frmReports]![pg1_lstReports]), "")
'How to test if either are invalid
'Loop through the criteria for the report, check if it exists in strRecordSourceStatement and replace
'Not certain how I feel about this over all
CriteriaArray = Split(Me.pg1_lstReports.Column(2), ";")
For i = 0 To UBound(CriteriaArray)
ControlSetting = Split(CriteriaArray(i), "=")
If InStr(1, strRecordSourceStatement, ControlSetting(0)) > 0 Then
strRecordSourceStatement = Replace(strRecordSourceStatement, ControlSetting(0), Me.Controls(ControlSetting(0)))
End If
Next i
Call updatePassThroughQuery(strRecordSourceName, strRecordSourceStatement)
End If
Public Sub updatePassThroughQuery(strQryDefName As String, strStatement As String)
'Updates a pass through query to allow parameters to be passed in
'For ease of development the pass through is created if it doesn't exist
'http://support.microsoft.com/kb/131534
Dim MyDb As Database
Dim MyQ As QueryDef
Dim QryDefExists As Boolean
Dim i As Integer
Set MyDb = CurrentDb()
QryDefExists = False
For i = 0 To MyDb.QueryDefs.Count - 1
Debug.Print MyDb.QueryDefs(i).Name
If MyDb.QueryDefs(i).Name = strQryDefName Then QryDefExists = True
Next i
If QryDefExists Then
Set MyQ = MyDb.QueryDefs(strQryDefName)
MyQ.SQL = strStatement
MyQ.Close
MyDb.Close
Set MyQ = Nothing
Else
Set MyQ = MyDb.CreateQueryDef(strQryDefName)
MyQ.Connect = "ODBC;DSN=TrailerManagementSystem;Description=Connection Trailer Management System SQL Server;Trusted_Connection=Yes"
MyQ.ReturnsRecords = True
MyQ.SQL = strStatement
MyQ.Close
MyDb.Close
Set MyQ = Nothing
End If
Set MyQ = Nothing
Set MyDb = Nothing
End Sub
April 5, 2013 at 2:21 pm
hmm... seeing that my data is in Access and the recordsets are pretty light, I may opt to keep everything local with the VBA cursor on a custom function.
Otherwise I'd have to somehow pass the recordset from Access into the whole sp call. But thanks again for laying the groundwork on the ADO parameter piece!
Cheers,
John
April 5, 2013 at 2:33 pm
jjturner (4/5/2013)
hmm... seeing that my data is in Access and the recordsets are pretty light, I may opt to keep everything local with the VBA cursor on a custom function.Otherwise I'd have to somehow pass the recordset from Access into the whole sp call. But thanks again for laying the groundwork on the ADO parameter piece!
Cheers,
John
Nope. 'Pass Through Query' is only the term. From a developer standpoint, a Pass Through Query is just another recordset.
We should open a new thread as this is off topic.
April 6, 2013 at 6:25 am
david.holley (4/5/2013)
I don't know if you can do that with a TallyTable or if a TT is neccessary. This is a function that should accomplish it. It simply adds the next selected value to the previous. And no, I didn't figure this out myself.
You need to add something to that code to check that you are getting things in the right order and do something else if not. The order of elements in the result string is not guaranteed to be determined by the order by clause, so you need to include code for each row that checks the required order has not yet been violated and force an error if it has. That is actually veru easy to do, only a small modification. But you also have to have code to deal with the error, presumably by using an explicity loop to do the job instead of the single statement.
Whether order the elements of the result string are in depends on how the optimiser decides to execute that query, which potentially changes when the table content changes or when the optimiser is updated (hotfix, service pack, or new release) or when an index on the table is added, altered, or dropped.
If the table is clustered on the id column and there is no covering index for the query, or the table is clustered on a covering index for the table whose first element is id, of the table is an heap with a covering index for the query and that indexes first element ois id, you stand a good chance of getting the behaviour you want (in most other cases you don't) but it's still not guaranteed. Also, if the query works today that doesn't guarantee it will work tomorrow - for example adding some rows to the table (or deleting some) may cause the optimiser to adopt a different query plan. And it may not work after applying an update to SQL Server or in a new release, because the optimiser may have changed a bit and adopt a different query plan.
Tom
April 6, 2013 at 5:17 pm
L' Eomot Inversé (4/6/2013)
The order of elements in the result string is not guaranteed to be determined by the order by clause, so...
I've heard about that but haven't been able to cause it. Do you have a link or some code that demonstrates the problem?
--Jeff Moden
April 6, 2013 at 5:20 pm
jjturner (4/5/2013)
Thanks David - that was quick! I'll have a go at translating this into VBA and see what happens.Although it does look like there's no way around firing a bunch of SELECT statements with a cursor (unless I'm misreading it)...
Cheers,
John
Although the function that David demonstrated is a tried and true method, there's no need for a function at all. It can be done using an XML hack that is quite popular. Please see the following article.
http://qa.sqlservercentral.com/articles/comma+separated+list/71700/
--Jeff Moden
April 6, 2013 at 8:34 pm
Jeff Moden (4/6/2013)
L' Eomot Inversé (4/6/2013)
The order of elements in the result string is not guaranteed to be determined by the order by clause, so...I've heard about that but haven't been able to cause it. Do you have a link or some code that demonstrates the problem?
No, unfortunately I don't have anything that demonstrates it. I think I have experienced it, though.
I was bitten by it, I think, back in 2005 - it started happening with our system on a machine belonging to one of our customers, but not on other customers' machines and not in-house, although the code and the relevant part of the schema was identical in all the machines, both those affected and those not - only the data was different according to (a) what the customer had contracted for and (b) what use the customer had made of his stuff, and the hardware was different according to when it had been installed. We ended up changing that piece of code on all the systems to do it with a loop, since our customers expected 24 X 7 correct operation apart from a monthly break (scheduled ad hoc by the customer to fit his needs, not the same time every month) to apply MS patches and any of our fixes that would need downtime. Unfortunately this was at a time when I was swamped with too much administrative work (we didn't have enough people) so although the exact details were on file in our support record I didn't make a personal copy. Now I no longer have access - I left the company nearly 4 years ago, after transferring responsability for all technical matters from London to Beirut, and since then the company's London and Dubai operations have been reduced even further so the record may no longer exist.
edit: I should probably mention that in the cases where I said there's a good chance, my personal belief is that it will always work in those cases - but people whom I probably ought to believe have told me there's no guarantee even in those cases. And where I've said there's not such a good chance, I suspect that the chance of it working is still quite high, but think I have seen it fail once.
Tom
April 7, 2013 at 6:52 am
Would wrapping the SELECT inside another work if the specific order is a concern? Something like...
SELECT @NewString = @NewString + NameInitials FROM
(SELECT NameInitials FROM Employees WHERE OfficeId = 1001 ORDER BY NameInitials) as s
April 7, 2013 at 7:39 am
L' Eomot Inversé (4/6/2013)
Jeff Moden (4/6/2013)
L' Eomot Inversé (4/6/2013)
The order of elements in the result string is not guaranteed to be determined by the order by clause, so...I've heard about that but haven't been able to cause it. Do you have a link or some code that demonstrates the problem?
No, unfortunately I don't have anything that demonstrates it. I think I have experienced it, though.
I was bitten by it, I think, back in 2005 - it started happening with our system on a machine belonging to one of our customers, but not on other customers' machines and not in-house, although the code and the relevant part of the schema was identical in all the machines, both those affected and those not - only the data was different according to (a) what the customer had contracted for and (b) what use the customer had made of his stuff, and the hardware was different according to when it had been installed. We ended up changing that piece of code on all the systems to do it with a loop, since our customers expected 24 X 7 correct operation apart from a monthly break (scheduled ad hoc by the customer to fit his needs, not the same time every month) to apply MS patches and any of our fixes that would need downtime. Unfortunately this was at a time when I was swamped with too much administrative work (we didn't have enough people) so although the exact details were on file in our support record I didn't make a personal copy. Now I no longer have access - I left the company nearly 4 years ago, after transferring responsability for all technical matters from London to Beirut, and since then the company's London and Dubai operations have been reduced even further so the record may no longer exist.
edit: I should probably mention that in the cases where I said there's a good chance, my personal belief is that it will always work in those cases - but people whom I probably ought to believe have told me there's no guarantee even in those cases. And where I've said there's not such a good chance, I suspect that the chance of it working is still quite high, but think I have seen it fail once.
Thanks, Tom. I've never actually seen the out-of-order problem happen either and it might be because of the precautions I normal take when doing such things. I do suspect (but haven't taken the time to prove) that the problem occurs when the unique and or clustered indexes are just right and parallelism occurs. Because of the possibility of it coming out wrong, I normally add the ol' OPTION(MAXDOP 1) to such a thing when I can. I can imagine that a join within the concatenation overlay in a single variable might still gum up the works. That might also be why I've never seen it. I try to keep my code pretty simple and modular. I don't mix joins with such string tricks except for the Tally Table.
I do wish that MS had two different ORDER BYs... one that occurred as the processing occurred and one after the processing completed.
--Jeff Moden
April 7, 2013 at 7:48 am
david.holley (4/7/2013)
Would wrapping the SELECT inside another work if the specific order is a concern? Something like...SELECT @NewString = @NewString + NameInitials FROM
(SELECT NameInitials FROM Employees WHERE OfficeId = 1001 ORDER BY NameInitials) as s
Damn. I was just looking at an MS white paper last night (for a totally different reason) that said this is no guarantee, either, and didn't save the URL. But I believe I know how to fix even that problem.
First, since you're using ORDER BY in a sub-query (Derived Table, in this case), you must also use TOP. That's where most people end up making the mistake. For simplicity, most will use TOP 100%. I can't prove it but I remember one of the folks over on the SQLTeam forums proving that TOP 100% can sometimes cause the ORDER BY to be ignored in a sub-query. Instead, they recommended the use of the max integer value of 2147483647.
As a sidebar, I kick myself everytime I remember something like this and forget to save the URL from where I found it some years ago.
Of course, the "best" way to do it now is to use the XML hack that I provided a link for.
--Jeff Moden
April 7, 2013 at 10:07 am
Jeff Moden (4/7/2013)
david.holley (4/7/2013)
Would wrapping the SELECT inside another work if the specific order is a concern? Something like...SELECT @NewString = @NewString + NameInitials FROM
(SELECT NameInitials FROM Employees WHERE OfficeId = 1001 ORDER BY NameInitials) as s
Damn. I was just looking at an MS white paper last night (for a totally different reason) that said this is no guarantee, either, and didn't save the URL. But I believe I know how to fix even that problem.
First, since you're using ORDER BY in a sub-query (Derived Table, in this case), you must also use TOP. That's where most people end up making the mistake. For simplicity, most will use TOP 100%. I can't prove it but I remember one of the folks over on the SQLTeam forums proving that TOP 100% can sometimes cause the ORDER BY to be ignored in a sub-query. Instead, they recommended the use of the max integer value of 2147483647.
As a sidebar, I kick myself everytime I remember something like this and forget to save the URL from where I found it some years ago.
Of course, the "best" way to do it now is to use the XML hack that I provided a link for.
Until SQL Server 2000, the plan would include a sort, in order to satisfy the TOP clause. Usually, that sort order would then be retained throughout the rest of the plan - though that was never guaranteed - if the order by is in a subquery, it does not define the order in which rows of the final query are returned.
Then, in SQL Server 2005, the optimizer was improved to recognize a "TOP 100 PERCENT" as superfluous. And that meant that the sort step, which was only needed to execute that TOP clause, could be removed from the plan as well. This broke an enormous amount of code that relied on this trick, in spite of it being undocumented and many experts warning against it. (Some of that code was in use by Microsoft customers with enough influence that MS even released a hotfix that implemented a trace flag you could activate return to the old behaviour, and that trace flag survived into SQL Server 2008 as well - see http://support.microsoft.com/kb/926292).
I've seen the advise to use TOP with an insane high integer value, but I don't support it. First, there are tables that include more rows, and then this TOP clause could cause incorrect results to be returned. But probably more important: peoplpe should consider that a future improvement of the optimizer might also include an optimization to remove the top and associated sort if the optimizer can be sure that the actual number of rows will never come evenn near that amount. At that point, all code using this trick can stop working.
April 7, 2013 at 12:58 pm
Hugo Kornelis (4/7/2013)
Jeff Moden (4/7/2013)
david.holley (4/7/2013)
Would wrapping the SELECT inside another work if the specific order is a concern? Something like...SELECT @NewString = @NewString + NameInitials FROM
(SELECT NameInitials FROM Employees WHERE OfficeId = 1001 ORDER BY NameInitials) as s
Damn. I was just looking at an MS white paper last night (for a totally different reason) that said this is no guarantee, either, and didn't save the URL. But I believe I know how to fix even that problem.
First, since you're using ORDER BY in a sub-query (Derived Table, in this case), you must also use TOP. That's where most people end up making the mistake. For simplicity, most will use TOP 100%. I can't prove it but I remember one of the folks over on the SQLTeam forums proving that TOP 100% can sometimes cause the ORDER BY to be ignored in a sub-query. Instead, they recommended the use of the max integer value of 2147483647.
As a sidebar, I kick myself everytime I remember something like this and forget to save the URL from where I found it some years ago.
Of course, the "best" way to do it now is to use the XML hack that I provided a link for.
Until SQL Server 2000, the plan would include a sort, in order to satisfy the TOP clause. Usually, that sort order would then be retained throughout the rest of the plan - though that was never guaranteed - if the order by is in a subquery, it does not define the order in which rows of the final query are returned.
Then, in SQL Server 2005, the optimizer was improved to recognize a "TOP 100 PERCENT" as superfluous. And that meant that the sort step, which was only needed to execute that TOP clause, could be removed from the plan as well. This broke an enormous amount of code that relied on this trick, in spite of it being undocumented and many experts warning against it. (Some of that code was in use by Microsoft customers with enough influence that MS even released a hotfix that implemented a trace flag you could activate return to the old behaviour, and that trace flag survived into SQL Server 2008 as well - see http://support.microsoft.com/kb/926292).
I've seen the advise to use TOP with an insane high integer value, but I don't support it. First, there are tables that include more rows, and then this TOP clause could cause incorrect results to be returned. But probably more important: peoplpe should consider that a future improvement of the optimizer might also include an optimization to remove the top and associated sort if the optimizer can be sure that the actual number of rows will never come evenn near that amount. At that point, all code using this trick can stop working.
Thanks for the info and the link, as well.
With the understanding that I've not seen nor have I been able to produce such a failure except when an indeterminate formula was used as the target of the ORDER BY, I guess the next question would be, is there a code example somewhere that demonstrates failure of the TOP (MaxIntValue)/ORDER BY? The one that MS published never seems to fail.
Also, is there a work around that will produce the desired behavior other than the obvious method of shifting to FOR XML PATH? Or, is that also subject to ORDER BY failing (which would really screw up some real XML)?
--Jeff Moden
Viewing 15 posts - 376 through 390 (of 497 total)
You must be logged in to reply to this topic. Login to reply