Aug 132012
 
Imports System.Data
' Refer to these DLLs:
'   microsoft.sqlserver.connectioninfo
'   Microsoft.SqlServer.Management.Sdk.Sfc
'   Microsoft.SqlServer.smo
'   Microsoft.SqlServer.sqlenum
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Sdk.Sfc
Imports Microsoft.SqlServer.Management.Common

Module Module1

    Sub Main()
        GetServers()
        Console.ReadLine()
    End Sub

    Private Function GetServers() As List(Of String)

        Dim servers As New List(Of String)
        Dim dt As DataTable = SmoApplication.EnumAvailableSqlServers(False)

        If dt.Rows.Count > 0 Then

            For Each dr As DataRow In dt.Rows

                Dim servername As String = dr("Name")

                If Not servers.Contains(servername) Then ' Only once per server

                    servers.Add(servername)
                    Console.WriteLine(servername)

                    Dim server As New Server(servername)
                    Try
                        ' Enumerate databases 
                        For Each db In server.Databases

                            If Not db.issystemobject Then
                                Console.WriteLine("  " & db.name)

                                ' Enumerate user tables
                                For Each t As Table In db.tables

                                    If Not t.IsSystemObject Then
                                        Console.WriteLine("    " & t.Name)
                                    End If

                                Next

                            End If

                        Next

                    Catch ex As Exception
                        Console.WriteLine("  " & ex.Message)
                        Console.WriteLine("  Maybe the SQL Server Browser Service isn't started on " & servername & "?")
                    End Try
                End If
            Next
        End If

        Return servers

    End Function


End Module

 Posted by at 10:17 pm  Tagged with:
Jul 012009
 

From an early age I have practised defensive programming. On a recent project, I was loading data entered on an intranet site to an SQL Server table. Wary, I used SQL’s ISNUMERIC function to validate the numbers users had entered. It worked fine for a few weeks, until somone entered a comma as a decimal separator. It turns out that ISNUMERIC accepts this, whereas casting doesn’t. Here’s the proof:

isnumeric

Doubtless, MS will try and wriggle out by saying that it depends on how your international settings are made. My contention is that if ISNUMERIC says it is then you should be able to CAST it to a number.

Period. 

(sorry for the weak pun)

 Posted by at 11:56 am  Tagged with: