SMS queries from Front Page

[Guest_imported]

Hi,

Can someone point me to a good resource for querying SMS data from MS Front Page?

I have created a query in SMS with this syntax:

select IPAddresses, Name, OperatingSystemNameandVersion, SystemRoles from SMS_R_System where IPAddresses >= &quot;24.1.21.32&quot; and IPAddresses <= &quot;24.1.21.63&quot; order by IPAddresses

and I get the desired results in SMS.

I created an ODBC connection to the SMS server from my IIS server. When I verify this same query in Front Page, I get the following result:

Server error: Unable to retrieve schema information from the query:

(connection info, yadda yadda , no errors)

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'SMS_R_System'.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)


Thanks for any help,

Chris

 

[Schoenberg]

You might try using a different ODBC driver. I notice you are trying to use the SQL ODBC driver instead of the WMI ODBC driver. Try looking on the SMS 2.0 CD for the WMI ODBC driver, which will allow you to access the schema. (sorry don't have the path for this handy). Try searching for *.exe, since it's most likely an SMS installer file to install the ODBC on your FrontPage server.

 

[harry1234]

I want to know if any one has tried using concatenated SMS. SMS beyond length of 160 chars.

Harry

 

[Guest_imported]

Make sure that you have the Frontpage server extensions installed and that you have allowed scripting on the relevant webpage. Let me know if you have problems....

 

[Schoenberg]

To clarify my post above: The query, as written in your post, will not work (as you have obviously experienced).

It will not work because the query language you cut and pasted from the SMS Admin Console is written in WQL, which wants to access tables through WMI, not SQL. (if you are unaware: WMI is an abstraction layer to the SQL tables themselves)

This means that if you query using the SQL ODBC driver (as your error message describes), but use a query from he SMS Admin Console (WQL), then you will get the error you describe.

The only was to get this query to work is to either use the WMI ODBC driver instead, or to create a SQL view for he data you are querying for, then use the SQL ODBC driver on your web site (this second option is a lot of work, but if you use Microsoft Web Reports, you'll save a lot of effort).