Get All Servers and Databases from MSSQL Server 2005 Express

Is there any way to get all available servers and databases from Microsoft SQL Server 2005 Express without connecting to it at all? I am trying to write an application that uses SS05E and I would like to have an option to display all available servers and databases.

check SQLCMD. Form command prompt ...

for all the sql server instance in a network - sqlcmd -L

to get all the database name from a sql server instance -- sqlcmd -q"Select *from sys.databases' -SserverName -Ppassword

Madhu

|||There is no way to pull a list of databases from the servers without connecting to the databases. sqlcmd with the -L switch can output all the SQL Servers on your Windows network. You would need to log into each one and pull the database list from the master.dbo.sysdatabases or sys.databases objects to see what databases are available on each.|||That will work fine. Is there any way I can run sqlcmd -L in a vb.net program and get the results put into a string array? I am writing an application that uses SQL Server and I want the servers/databases to be put into combo boxes or list boxes to make it easier to select which server/database to use.|||

I found something google that will work perfectly for what I need. Here is the code:

Code Snippet

Try

Dim myprocess As New Process

Dim StartInfo As New System.Diagnostics.ProcessStartInfo

StartInfo.FileName = "cmd" 'starts cmd window

StartInfo.RedirectStandardInput = True

StartInfo.RedirectStandardOutput = True

StartInfo.UseShellExecute = False 'required to redirect

StartInfo.CreateNoWindow = True 'creates no cmd window

myprocess.StartInfo = StartInfo

myprocess.Start()

Dim SR As System.IO.StreamReader = myprocess.StandardOutput

Dim SW As System.IO.StreamWriter = myprocess.StandardInput

SW.WriteLine("sqlcmd -L") 'the command you wish to run.....

SW.WriteLine("exit") 'exits command prompt window

Me.txtOutput.Text = SR.ReadToEnd 'returns results of the command window

SW.Close()

SR.Close()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

Thanks for your help in getting me this far! I really appreciate it

Get a SQL Backup Folder Path in asp.net

Hi

Anyone knows , get a SQL Backup Folder Path in asp.net.Some Systems it displayed like D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup and some Systems like E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup and etc. i need to get a path for backup programmatically.Please help me.

Tamil

You can back up the databse to any location you'd like. You specify the path when you back up the DB.

sql

Get @@rowcount data from MSSQL using SqlDataSource with delete command

Hi

I'm using a simple SqlDataSource to connect to a stored proc to delete a number of rows from different tables.

In my SQL im using:

DECLARE @.table1CountintDELETE FROM Table1WHERE id = @.new_idSET @.table1Count=@.@.rowcountSELECT @.table1Count

I'm then using an input box and linking it to the delete control parameter. Then on a button click event i'm running SqlDataSource1.Delete() which all works fine. But how do i get the @.table1Count back into my aspx page?

Thanks

use OUT parameter

CREATE PROC dbo.SP_DeleteID(@.new_id int, @.effRowCnt intOUT)

As

begin

DELETE FROM Table1WHERE id = @.new_id

SET @.effRowCnt=@.@.rowcount

end

|||

ok, but how do i reference the @.effRowCnt in the code behind page?

thanks

|||

If you are just trying to get a count of the number of rows deleted, an easier way to do it is to use the Deleted methodof the DataSource, something like:

ProtectedSub SQLDataSource1_Deleted(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles SQLDataSource1.Deleted

NumberofRowsDeleted = e.AffectedRows

EndSub

HTH

Generate 2 decimal points?? how?

HI All,

I use MSSQL as my database and ASP.NET as my front application. I want to display Price value S$23.68.

The dayatype I used is smallmoney, but it display: S$23.6800. HOw do I control the number of decimals point in the column of MSSQL??

Thanks a lot

Suigion

Well for monetry values i use datatype Decimal(9,2)

where 9 is the number of digits before Decimal and 2 is the number of digits that u can use after decimal Point

hope that might helps....!!

Generalising Rownum for all the databases

Hi

I have as issue with this rownum.This is supported in different forms in different databases like top in mssql ,limit in mysql and all..i want to genaralise this independent of the type of database because i want to run the query as i wont be knowing the type of the DB used by my user.

can anyone suggest a solution for this ? is there anyway of generating a series of numbers with the query by calling the function in that query repetitively ?

Regards
SreenathA UDF won't help you, because not all databases support User Defined Functions.

I think you will need to use something like this, which requires a primary key or unique sort order on your result set:

select a.Pkey, count(*) as RowNum
from YourTable a
inner join YourTable b on a.Pkey >= b.Pkey
group by a.PKey