I have a huge application built in sometime 2009-10 for managing SQL Server stored procedures as well as submitting standard daily statements to the variety of servers and instances I manage.

I don't know if you are going to be able to create all of the container objects that are necessary to work with this.

You have to define the server object you then can pass in SQL code to execute, or go through the many aspects of objects within a server, database, backups, stored procedures, UDF, email, etc.

I generate a string for my "code" that I want executed from my WinForms client.

This is the deadlock count query string:

case "Deadlock Count": SQL.Append("select resource_database_id, d.name, count(*) from sys.dm_tran_locks tl "); SQL.Append("left join sys.databases d on tl.resource_database_id = d.database_id "); SQL.Append("group by resource_database_id, d.name order by 3 desc"); break;

I have a control with a LOT of different code I want to operate. User clicks on Deadlock Count and uses this case statement to render the code that will be run on the instance|database.

This is the code that does the actual run query and show results: try { DataSet ds = db.ExecuteWithResults(sql); dgResult.DataSource = ds.Tables[0];

} catch (Exception ex) { MessageBox.Show(ex.Message, "This was the error you experienced");

}

There are many different types of queries that the db can perform, in this case I want the result set that I am placing in my grid on the form.

On Wed, Apr 29, 2020 at 10:58 AM Tracy Pearson tracy@powerchurch.com wrote:

The COM object you are creating is named "sqlSMO", are you sure this isn't already the object you are looking for?

Tracy

-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Paul Newton Sent: Wednesday, April 29, 2020 11:56 AM To: profoxtech@leafe.com Subject: SQL Server Shared Management Objects (SMO)

Hi all

We have been using the following to get a list of available servers loDMO = CreateObject("sqlSMO.Application") loServerList = loDMO.ListAvailableSQLServers

However SQL-DMO was apparently deprecated with SQL Server 2005 and succeeded by SQL-SMO. Has anybody used SQL Server Shared Management Objects in VFP and, if so, can they please give me some pointers on what I need to install and how to implement code to replace that above. Many thanks

Paul Newton

--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html

---

[excessive quoting removed by server]

Alan

Thanks for the reply - I suspected that's what I might have to do.

Paul

-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Alan Bourke Sent: 30 April 2020 08:57 To: profoxtech@leafe.com Subject: Re: SQL Server Shared Management Objects (SMO)

Sent by an external sender ------------------------------

Paul

There is no COM interface to SQL-SMO, so you would have to create a .NET assembly with a COM-callable wrapper exposing the functions you want I think. Or even just a .NET EXE which dumps any results to a text file that the vfp SIDE CAN READ.

What SMO does for you is also available via powershell. Because I have the app written I don't work much in powershell for SQL Server analysis.

On Thu, Apr 30, 2020 at 2:58 AM Alan Bourke alanpbourke@fastmail.fm wrote:

Paul

There is no COM interface to SQL-SMO, so you would have to create a .NET assembly with a COM-callable wrapper exposing the functions you want I think. Or even just a .NET EXE which dumps any results to a text file that the vfp SIDE CAN READ.

-- Alan Bourke alanpbourke (at) fastmail (dot) fm

[excessive quoting removed by server]

What do you want to do through SMO?

On Thu, Apr 30, 2020 at 10:38 AM Alan Bourke alanpbourke@fastmail.fm wrote:

On Thu, 30 Apr 2020, at 4:16 PM, Stephen Russell wrote:

...

What SMO does for you is also available via powershell. Because I have

the

...

app written I don't work much in powershell for SQL Server analysis.

Steven

Yes indeed although leveraging Powershell as part of an EXE distributed to end users brings its own challenges - you would need to #Require SqlServer probably and things like that.

-- Alan Bourke alanpbourke (at) fastmail (dot) fm

[excessive quoting removed by server]

On Thu, 30 Apr 2020, at 5:11 PM, Stephen Russell wrote:

What do you want to do through SMO?

It's actually Paul Newton who has the requirement, which is to discover SQL Server instances on the network.

I don't know that this is fully in-line with the subject of this thread, but, just I want share this in case it helps anyone:

A few years ago I made a blog post about using PowerShell to execute commands against Sql Server.

http://mattslay.com/sql-server-queries-using-powershell-lesson-1/

On 4/30/2020 10:16 AM, Stephen Russell wrote:

What SMO does for you is also available via powershell. Because I have the app written I don't work much in powershell for SQL Server analysis.

On Thu, Apr 30, 2020 at 2:58 AM Alan Bourke alanpbourke@fastmail.fm wrote:

...

Paul

There is no COM interface to SQL-SMO, so you would have to create a .NET assembly with a COM-callable wrapper exposing the functions you want I think. Or even just a .NET EXE which dumps any results to a text file that the vfp SIDE CAN READ.

-- Alan Bourke alanpbourke (at) fastmail (dot) fm

[excessive quoting removed by server]