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

Generating a flat file

Hi ya,

I'm generating a flat file from SSIS package and i'm having some problems.

My package contains this Data Flow which is connecting to a Database and importing the records. I did design a script component since the file I generate has some padding.

Here is the code for the component so that you really understand what i'm trying to achieve over here.

Dim toPadTo As Int32

Dim myDate As String

Dim MultVal As Int64

Dim myMonth As Int16, myMonth2 As String

myMonth = CShort(Row.TransDate.Month)

If myMonth <= 9 Then

myMonth2 = myMonth.ToString.PadLeft(2, CChar("0"))

Else

myMonth2 = myMonth.ToString

End If

myDate = CStr(Row.TransDate.Date.Day) + myMonth2 + CStr(Row.TransDate.Year)

MultVal = CInt(Row.TransAmount * 100)

Dim myChkLength As Int16 = CShort(MultVal.ToString.Length)

Dim myCombine As String = CStr(MultVal) & CStr(myDate)

Dim myCombine2 As String = CStr(MultVal) & CStr(myDate)

Select Case myChkLength

Case 4

toPadTo = 30 - (myCombine.ToString.Length)

Case 5

toPadTo = 30 - (myCombine.ToString.Length - 1)

Case 6

toPadTo = 30 - (myCombine.ToString.Length - 2)

Case 7

toPadTo = 30 - (myCombine.ToString.Length - 3)

End Select

' + myDate.ToString.Length - 1))

Row.myConvert = myCombine2.PadLeft(toPadTo, CChar("0"))

Dim MyNewRow As String = myCombine2.PadLeft(toPadTo, CChar("0"))

Dim ChkSign As Int16

ChkSign = CShort(Math.Sign(CDec(MyNewRow)))

If ChkSign = 1 Then

Row.myConvert = MyNewRow.PadLeft(2, CChar("+"))

ElseIf ChkSign = -1 Then

Row.myConvert = MyNewRow.PadLeft(2, CChar("-"))

Else

Row.myConvert = MyNewRow.PadLeft(2, CChar("+"))

End If

End Sub

Obviously i don't think this is the best approach hence i'm asking? not to mention the sort of problem i'm getting with the + and - insertion part of the code.

To give you an example of how the value would be:

Actual value: 159.23

After transformation it should be like this : +00000000000015923

Depending on the amount x no zeros should be inserted.

Any other way to achieve it? Apart from this I also need to generate a sequence no with some string at the end of the ragged file. How would i go for it ?

Cheers

Rizshe

Read the data in from the SQL database, add a script transformation using the input column, add an output column of type string length 30, transform like: -

Dim Style As String = "+0000000000000000000000000000#;" _
+ "-0000000000000000000000000000#;" _
+ "+0000000000000000000000000000#"
with row
dim n as int = CInt(.myoldcolumn * 100)
.mynewcolumn = Format(n, Style)
end with

then simply output the new columns to the flat file

|||

Hi Paul,

Your code doesn't seem to do anything different then my own above. Perhaps you misunderstood me.

I would need to put the 0 padding and depending on the + or - in the amount column also put the sign.

The problem i'm getting is that the amount varies from 0.94 to 124.78 and i would need to pad it accordingly.

Cheers

Rizwan

|||

Rizwan,

I may have missed something, but I believe that the code does exactly that. it creates 30 character strings with the correct sign...

|||

Hi Paul,

My deepest apologies. I must be blind that i didn't look at your code carefully.

Yes it does work.

Thank you very much and again forgive me for my stupid mistake

Rizwan