Detect SQL (Server or MSDE)

[Christian Pické]

Hi,

I want to deploy my Windows Application written in C#. I am using SQL
Server and I wonder if there is a way to detect computers that run SQL
Server or MSDE, so I can connect to them 1) to run a script that creates
the tables and stored procedures on it, 2) to connect to the right
database when it already exists.
I am asking this because when I am deploying my application personnaly,
I could set the right connection parameters myself, but when I want to
let it be installed automatically I don't know the name of the computer
in advance, so I would like to be able to automate the whole installing
proces including taking the discission to install MSDE or to connect to
an existing SQL Server.

Thanks in advance.

Christian

 

[Wiktor Zychla]

I want to deploy my Windows Application written in C#. I am using SQL

Server and I wonder if there is a way to detect computers that run SQL
Server or MSDE, so I can connect to them 1) to run a script that creates

you can easily detect SQLServers using the SQLDMO or even Win32
NetServerEnum function.

however, you do not have to install the database during the application
installation process. you can postpone the database installation until the
user tries to login to the selected server for the first time (this is how
we handle this issue in our applications).
then you scan the databases and if there are no valid ones (or the user
wants to create a new database), you run scripts that install the database.
further, you can store the selected server and selected database in a small
configuration file, so the user does not have to make his selection
everytime he uses your application.

Regards,
Wiktor Zychla

 

[MajorTom]

Example for SQLDMO:

http://www.csharphelp.com/archives2/archive342.html

other options:

http://www.codeproject.com/cs/database/LocatingSql.asp

and for installing an instance of MSDE 2000 during their own setup processes

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/distsql/distsql_8yeq.asp

MajorTom

 

[Wiktor Zychla]

http://www.codeproject.com/cs/database/LocatingSql.asp
the SQLDMO is not as such problematic as the author says it is. in fact,
when you use reflection, the code is rather short (snippet from my code
below).
Regards, Wiktor Zychla

// no external references, no COM wrappers!
public static ArrayList ListAvailableSQLServers()

{

try

{

Type t = Type.GetTypeFromProgID( "SQLDMO.Application" );

object sqldmo = Activator.CreateInstance( t );

object namelist = t.InvokeMember( "ListAvailableSQLServers",
BindingFlags.InvokeMethod, null, sqldmo, null );

int count = (int)t.InvokeMember( "Count", BindingFlags.GetProperty,
null, namelist, null );

ArrayList aServers = new ArrayList();

for ( int i=0; i<count; i++ )

{

object sname = t.InvokeMember( "Item", BindingFlags.InvokeMethod,
null, namelist, new object[] { i } );

if ( sname != null ) aServers.Add( sname.ToString() );

}

sqldmo = null;

namelist = null;

return aServers;

}

catch

{

return new ArrayList();

}

}

 

[MajorTom]

Thanks, I will try this code , more later...

MajorTom

http://www.codeproject.com/cs/database/LocatingSql.asp
the SQLDMO is not as such problematic as the author says it is. in fact,
when you use reflection, the code is rather short (snippet from my code
below).
Regards, Wiktor Zychla

// no external references, no COM wrappers!
public static ArrayList ListAvailableSQLServers()

{

try

{

Type t = Type.GetTypeFromProgID( "SQLDMO.Application" );

object sqldmo = Activator.CreateInstance( t );

object namelist = t.InvokeMember( "ListAvailableSQLServers",
BindingFlags.InvokeMethod, null, sqldmo, null );

int count = (int)t.InvokeMember( "Count", BindingFlags.GetProperty,
null, namelist, null );

ArrayList aServers = new ArrayList();

for ( int i=0; i<count; i++ )

{

object sname = t.InvokeMember( "Item", BindingFlags.InvokeMethod,
null, namelist, new object[] { i } );

if ( sname != null ) aServers.Add( sname.ToString() );

}

sqldmo = null;

namelist = null;

return aServers;

}

catch

{

return new ArrayList();

}

}

 

[Christian Pické]

Hi,

I tried the example using SQLDMO but it didn't work. I got an error
message (invalid casting) on the second code line.

SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.NameList sqlServers = sqlApp.ListAvailableServers();

I tried the code you proposed (using the sqlLocator sample) and that
works fine.

Thanks, all of you!

Christian