querie with parameters: syntax error

[khikyni]

<table border="0" cellpadding="0" cellspacing="0" width="142"><colgroup><col style="mso-width-source:userset;mso-width-alt:5193;width:107pt" width="142"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:107pt" height="20" width="142">Hi all,
I´m using parameters in several SQL Server queries.</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">In the first one all is ok but in the second one i receive a syntax error.</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Sucedeed querie:</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">--------------------------</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">select SourceMonitoringObjectDisplayName as 'Group Name',</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">TargetMonitoringObjectDisplayName as 'Group Members'</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">from RelationshipGenericView</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">where isDeleted=0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">AND SourceMonitoringObjectDisplayName like 'Group Name'</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ORDER BY TargetMonitoringObjectDisplayName</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">---------------------------</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">The steps i followed:</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">- excel 2007, add external data --> from SQL Server.</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">- i select the correct data source</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">- after creating the conection, i edit it and i select SQL "command type" and in "command text" I paste the query.</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">- I change the 'Group Name' with a ?</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">- I´m asked for the parameter and a select a cell in the book for updating it automatically</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">- all is fine, the group members are shown (this is a Querie to SCOM database) and when i change the cell, the new group members are also displayed.</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">------------------------</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">This is the unsucessful querie:</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">------------------------------------</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">SELECT vManagedEntity.DisplayName AS Computer</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">FROM vManagedEntity INNER JOIN</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20"> vManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">WHERE (vManagedEntity.TopLevelHostManagedEntityRowId IN</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20"> (SELECT DISTINCT ME1.TopLevelHostManagedEntityRowId</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20"> FROM vManagedEntity AS ME2 INNER JOIN</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20"> vRelationship ON ME2.ManagedEntityRowId = vRelationship.SourceManagedEntityRowId INNER JOIN</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20"> vManagedEntity AS ME1 ON vRelationship.TargetManagedEntityRowId = ME1.ManagedEntityRowId</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20"> WHERE (ME2.DisplayName like 'Group Name'))) AND (vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.SystemCenter.HealthService')</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">ORDER BY Computer</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">----------------------------------------</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">In this case, i receive a syntax error.</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">In both queries, if i fill 'group name' with text, the querie suceed.</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">But with parameter (?), the second querie fail with "syntax error".</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Maybe excel has problems with parameters and this kind of syntax?</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">- A workaround i have in mind but i don´t know how to: i could use the first querie, this querie ask database A. But this querie goes into another query (derived table), and the principal query connects to database B. </td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">I don´t know if /how can i add two datasources in the same excel conection.</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Thanks in advance.</td> </tr> </tbody></table>