Togli dbo_ da tabelle collegate

Rinomina le tabelle, togliendo il prefisso "dbo_".

Public Sub Togli_DBO()
    
    Dim db As DAO.Database
    Dim sq As String
    Dim rs As DAO.Recordset
    Dim oldName As String
    Dim newName As String
    
    
    Set db = CurrentDb
        
        
    sq = ""
    sq = sq & "SELECT   Name "
    sq = sq & "FROM     MSysObjects "
    sq = sq & "WHERE    Type = 4 "
    sq = sq & "     AND Name LIKE 'dbo_*' "
    
    Set rs = db.OpenRecordset(sq, dbOpenDynaset)
    
    While Not rs.EOF
        oldName = rs!Name
        newName = Replace(oldName, "dbo_", "")
    
        Debug.Print oldName & " --> " & newName
        DoCmd.Rename newName, acTable, oldName
        
        rs.MoveNext
    Wend
    
    Set rs = Nothing
    Set db = Nothing
    
End Sub

How to eliminate XML Escape Characters in "FOR XML PATH"?

How to eliminate XML Escape Characters (" ",' '< <> >& &) after using "FOR XML PATH", With out using "REPLACE" Command.

-- Tables Creation
IF OBJECT_ID('TempDB..#Test') IS NOT NULL DROP TABLE #Test
CREATE TABLE #Test (ID INT, Name VARCHAR(30))
-- Sample Data
INSERT INTO #Test(ID, Name) VALUES(1,'Test'),(2,'&Test'), (3,'')
-- Actual Data
SELECT ID, Name FROM #test

Required Output

Test,&Test,

Se eseguo questo codice:

SELECT STUFF((SELECT ','+Name AS [text()] FROM #Test FOR XML PATH('')),1,1,'')

Current Output

Test,&Test,

Solution:

SELECT STUFF((SELECT ','+Name AS [text()] FROM #Test FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'') AS 'NameList'

L'alias [text()] pare si possa omettere:

SELECT STUFF((SELECT ','+Name FROM #Test FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'') AS 'NameList'

Da approfondire, ma la magia è fatta dal "value() Method":
MSDN page for "value() Method (xml Data Type)"



Trovato qui.

---

Se dovesse dare questo errore:

Impossibile eseguire SELECT perché le impostazioni delle opzioni SET seguenti non sono corrette: 'ARITHABORT'. Verificare che tali opzioni SET siano corrette per l'utilizzo con viste indicizzate e/o indici su colonne calcolate e/o indici filtrati e/o notifiche delle query e/o metodi per tipi di dati XML e/o operazioni sugli indici spaziali.

Prima della SELECT bisogna impostare SET ARITHABORT ON:

SET ARITHABORT ON

SELECT STUFF((SELECT ','+Name AS [text()] FROM #Test FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'') AS 'NameList'

docs.microsoft.com: set-arithabort-transact-sql

DataTable to Excel

Non è testato al 100%!!
Bisogna parametrizzare (quantomeno il path di output)!

Private Shared Sub toExcel(ByVal dt As DataTable)

        Dim application As New Excel.Application()
        Dim workbook As Excel.Workbook = application.Workbooks.Add()
        Dim worksheet As Excel.Worksheet = CType(workbook.Sheets(1), Excel.Worksheet)

        For c As Integer = 0 To dt.Columns.Count - 1
            For r As Integer = 0 To dt.Columns.Count - 1
                worksheet.Cells(r + 1, c + 1) = dt.Rows(r).Item(c).ToString
            Next
        Next

        workbook.SaveAs("C:\whatever123.xlsx")
        workbook.Close()

        Marshal.ReleaseComObject(application)
    End Sub

    Private Shared Function GetExcelColumnName(ByVal columnNumber As Integer) As String

        Dim dividend As Integer = columnNumber
        Dim columnName As String = [String].Empty
        Dim modulo As Integer

        While dividend > 0
            modulo = (dividend - 1) Mod 26
            columnName = Convert.ToChar(65 + modulo).ToString() & columnName
            dividend = CInt((dividend - modulo) / 26)
        End While

        Return columnName

    End Function



    '=======================================================
    'Service provided by Telerik (www.telerik.com)
    'Conversion powered by NRefactory.
    'Twitter: @telerik
    'Facebook: facebook.com/telerik
    '=======================================================

Trovato qui.

Relinking tables and views between Access and SQL Server

Update: prima di riallegare, testa la connessione.

Nota: fare una copia del file di Access, prima di lanciare questa funzione: elimina e ricrea le tabelle!

Con questa sub riallego le tabelle, senza cambiare il nome del db, mantenendo le primary key delle viste logiche, ed evitando che l'MDB ingrassi.

Private Function getDbName(stringaDiConnessione As String) As String
    
    Dim res As String
    Dim x As Long
    Dim arr
    
    arr = Split(stringaDiConnessione, ";")
    
    For x = 0 To UBound(arr)
        If arr(x) Like "DATABASE=*" Then
            res = Replace(arr(x), "DATABASE=", "")
            Exit For
        End If
    Next
    
    getDbName = res
    
End Function

Public Function RelinkSQLTablesAndViewsAlternativeBoso(ServerName As String, _
                                                       Optional UID As String, _
                                                       Optional PWD As String) As Boolean



'    ServerName As String, _
'    DatabaseName As String, _
'    Optional UID As String, _
'    Optional PWD As String _
')


' Inputs:   ServerName:     Name of the SQL Server server (string)
'           DatabaseName:   Name of the database on that server (string)
'           UID:            User ID if using SQL Server Security (string)
'           PWD:            Password if using SQL Server Security (string)
'
    
    
    ' This example re-links any tables and views
    Dim db As Database
    Set db = CurrentDb
    Dim tdef As TableDef
    Dim indexSQL As String
    Dim fld As Field
    Dim constr As Variant
    Dim HasIndex As Boolean
    Dim rst As Recordset
    Dim Tablename As String
    Dim SourceTableName As String
    Dim tablecounter As Integer
    Dim i As Integer
    Dim oldConnectionString As String
    Dim sq As String

    'constr = DLookup("ConnectionString", "tblConnections", "Active = True")
        
    
    ' Start by checking whether using Trusted Connection or SQL Server Security
    
      If (Len(UID) > 0 And Len(PWD) = 0) Or (Len(UID) = 0 And Len(PWD) > 0) Then
        MsgBox "Must supply both User ID and Password to use SQL Server Security.", _
          vbCritical + vbOKOnly, "Security Information Incorrect."
        Exit Function
      Else
        If Len(UID) > 0 And Len(PWD) > 0 Then
    
    
'    ' Use SQL Server Security
'
'          constr = "ODBC;DRIVER={sql server};" & _
'            "DATABASE=" & DatabaseName & ";" & _
'            "SERVER=" & ServerName & ";" & _
'            "UID=" & UID & ";" & _
'            "PWD=" & PWD & ";"
'        Else
'
'    ' Use Trusted Connection
'
'          constr = "ODBC;DRIVER={sql server};" & _
'            "DATABASE=" & DatabaseName & ";" & _
'            "SERVER=" & ServerName & ";" & _
'            "Trusted_Connection=YES;"
'        End If
'      End If
    
    
    ' Use SQL Server Security
    
          constr = "ODBC;DRIVER={sql server};" & _
            "SERVER=" & ServerName & ";" & _
            "UID=" & UID & ";" & _
            "PWD=" & PWD & ";"
        Else
    
    ' Use Trusted Connection
    
          constr = "ODBC;DRIVER={sql server};" & _
            "SERVER=" & ServerName & ";" & _
            "Trusted_Connection=YES;"
        End If
      End If
    
    
    
    '-- TESTA LA CONNESSIONE --------------------------------------------------
    Dim cnA As New ADODB.Connection
    Dim wStringaDiConnessione As String
    Dim allOK As Boolean
    
    allOK = False
        
    If UID <> "" Then
        wStringaDiConnessione = "Provider=SQLOLEDB.1;Password=" & PWD & ";Persist Security Info=True;User ID=" & UID & ";Data Source=" & ServerName
    Else
        wStringaDiConnessione = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Data Source=" & ServerName
    End If
    
    With cnA
        .Open wStringaDiConnessione
        
        If .State = adStateOpen Then
            allOK = True
            .Close
        End If
    End With
    
    Set cnA = Nothing
    
    If Not allOK Then Exit Function
    '--------------------------------------------------------------------------
    
    
    ' find all the tables to be relinked
    sq = ""
    sq = sq & "SELECT MSysObjects.Connect, MSysObjects.ForeignName, MSysObjects.Type, MSysObjects.Name "
    sq = sq & "FROM MSysObjects "
    sq = sq & "WHERE (((MSysObjects.Connect) Like '*DSN=*' Or (MSysObjects.Connect) Like '*sql server*')); "
    
    Set rst = db.OpenRecordset(sq, dbOpenDynaset)
    
    If rst.EOF Then
        MsgBox "Not fonud!"
        Exit Function
    End If
    
    
    Debug.Print
    Debug.Print "**** Relinking on server " & ServerName
    Debug.Print
    
    rst.MoveLast
    ' get a count of how many records to process
    tablecounter = rst.RecordCount
    rst.MoveFirst
    
    For i = 1 To tablecounter
        Set tdef = db.TableDefs(rst!Name)
        
        oldConnectionString = tdef.Connect
        
        HasIndex = False
        If tdef.Indexes.Count = 1 Then
            ' only interested in objects with 1 index
            indexSQL = "CREATE INDEX " & tdef.Indexes(0).Name & " ON [" & tdef.Name & "](" & tdef.Indexes(0).Fields & ")"
            ' convert field list from (+fld1;+fld2) to (fld1,fld2)
            indexSQL = Replace(indexSQL, "+", "")
            indexSQL = Replace(indexSQL, ";", ",")
            HasIndex = True
        End If
        
        Tablename = tdef.Name
        SourceTableName = tdef.SourceTableName
        
        Set tdef = Nothing
        db.TableDefs.Delete Tablename
        
        Set tdef = New TableDef
        tdef.Name = Tablename
        tdef.SourceTableName = SourceTableName
        
        'tdef.Connect = constr
        
        tdef.Connect = constr & "DATABASE=" & getDbName(oldConnectionString)
        
        'Debug.Print tdef.Name, tdef.Connect
        Const lung As Long = 30
        Debug.Print Left(tdef.Name & Space(lung), lung) & Space(4) & tdef.Connect
        
        db.TableDefs.Append tdef
        
        ' if index now removed then re-create it
        If HasIndex And tdef.Indexes.Count = 0 Then
            CurrentDb.Execute indexSQL
        End If
        
        rst.MoveNext
    Next
    
    Debug.Print
    Debug.Print "**** Re-linked " & tablecounter & " tables."
    Debug.Print "**** DONE."
    
    RelinkSQLTablesAndViewsAlternativeBoso = True

End Function

Esempio di utilizzo, con username e password:

RelinkSQLTablesAndViewsAlternativeBoso "ITSRV01\SQL2008", "sa", "xxxx"

Utilizzando la connessione trusted:

RelinkSQLTablesAndViewsAlternativeBoso "ITSRV01\SQL2008"

Mashup delle funzioni trovate qui e qui.

Turn Off the Uppercase Menu in Visual Studio 2012

For all those people who can’t stand the ALL CAPS menus in Visual Studio 2012 there’s a way to switch them to normal casing.

Open your registry editor and create the following registry key and value:

Key:

HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\11.0\General\
SuppressUppercaseConversion 

Value:

REG_DWORD value: 1

For Windows 8 Express go to:

HKEY_CURRENT_USER\Software\Microsoft\VSWinExpress\11.0\General

For Web Express go to:

HKEY_CURRENT_USER\Software\Microsoft\VSWDExpress\11.0\General

Here’s what it looks like before:



And here it is after the change:





Testato su Win 7 + VS2012 Ultimate
Trovato qui e qui.

Aplicazioni a 32 bit su IIS 7 a 64 bit

Per default, IIS 7 e IIS 7.5 installati su un sistema 64 bit non permettono l'esecuzione di codice 32 bit. Per cui, se state cercando di avviare applicazioni non studiate e compilate nativamente a 64 bit, potreste ricevere il messaggio di errore:

Exception information: 
Exception type: ConfigurationErrorsException 
Exception message: Impossibile caricare il file o l'assembly 'xxxxxxx' o una delle relative dipendenze. Tentativo di caricare un programma con un formato non corretto. 

Abilitate il supporto all'esecuzione del codice 32 Bit con il comando DOS:

%windir%\system32\inetsrv\appcmd set config -section:applicationPools -applicationPoolDefaults.enable32BitAppOnWin64:true

Sostituite se necessario "applicationPoolDefaults" con la vostra application pool legata all'applicazione che state cercando di eseguire (questa non l'ho capita, né provata --ndBoso).


Testato su Windows Server 2008 R2 + IIS7.
Trovato qui.

Changing SQL Server language

select @@LANGUAGE

select name, language 
from master.dbo.syslogins
order by language

exec sp_configure 'default language'

EXEC sp_configure 'default language', 6 
RECONFIGURE

Trovato qui.
Testato su SQL 2008 R2

Determining the Control that Caused a PostBack

Public Shared Function GetPostBackControl(ByVal page As Page) As Control

    Dim res As Control = Nothing
    Dim ctrlName As String = Page.Request.Params.Get("__EVENTTARGET")

    If ctrlName IsNot Nothing And ctrlName <> "" Then
        res = page.FindControl(ctrlName)

    Else

        Dim c As Control = Nothing

        For Each ctl As String In page.Request.Form
            c = page.FindControl(ctl)
            If TypeOf (c) Is WebControls.Button Then
                res = c
                Exit For
            End If
        Next

    End If

    Return res

End Function

Si usa così:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Dim coso As Control = GetPostBackControl(Me)

    If coso.ID = "xxx" Then
        ' fai qualcosa...
    End If

End Sub

Testato sui Button.
Trovato qui.

Show/Hide All MS Access Objects

Vari modi per visualizzare/nascondere tutti gli oggetti di MS Access.



Basato su un'idea trovata qui. Non ancora testata al 100%!!:

Public Function Boso()
    
    Dim o As AccessObject
    
    For Each o In CurrentProject.AllForms
        Debug.Print o.Name
        Application.SetHiddenAttribute o.Type, o.Name, False
    Next
    
End Function

---

Public Sub HideObjects(Optional booHide As Boolean)
    
    'Created by Speakers_86
    'You are free to use, modify, and distribute
    'this as long as you leave this comment
    '
    'Purpose:   loops through all of you access objects, and
    '           hides them, the same as using the Access Gui to
    '           hide an object
    'Argument:  booHide
    '           an optional argument.  True hides objects,
    '           false unhides objects
    

    Dim db     As Database
    Dim tbl    As TableDef
    Dim qry    As QueryDef
    Dim str    As String
    Dim i      As Integer

    On Error Resume Next
    
    Set db = CurrentDb()


    For Each tbl In db.TableDefs
            Call SetHiddenAttribute(acTable, tbl.Name, booHide)
    Next tbl


    For Each qry In db.QueryDefs
        Call SetHiddenAttribute(acQuery, qry.Name, booHide)
    Next qry


    For i = 0 To db.Containers("Forms").Documents.Count - 1
        str = db.Containers("Forms").Documents(i).Name
        Call SetHiddenAttribute(acForm, str, booHide)
    Next



    For i = 0 To db.Containers("Reports").Documents.Count - 1
        str = db.Containers("Reports").Documents(i).Name
        Call SetHiddenAttribute(acReport, str, booHide)
    Next i


    For i = 0 To db.Containers("Modules").Documents.Count - 1
        str = db.Containers("Modules").Documents(i).Name
        Call SetHiddenAttribute(acModule, str, booHide)
    Next i



    For i = 0 To db.Containers("Scripts").Documents.Count - 1
        str = db.Containers("Scripts").Documents(i).Name
        Call SetHiddenAttribute(acMacro, str, booHide)
    Next i

    Set db = Nothing

End Sub

Trovato qui.

---

Boso's version:
- aggiunto un po' di `print`
- il parametro booHide è `TRUE` di default

Public Sub HideObjects(Optional booHide As Boolean = False)
    
    'Created by Speakers_86
    'You are free to use, modify, and distribute
    'this as long as you leave this comment
    '
    'Purpose:   loops through all of you access objects, and
    '           hides them, the same as using the Access Gui to
    '           hide an object
    'Argument:  booHide
    '           an optional argument.  True hides objects,
    '           false unhides objects
    

    Dim db As Database
    Dim tbl As TableDef
    Dim qry As QueryDef
    Dim str As String
    Dim i As Integer
    
    On Error Resume Next
    
    Set db = CurrentDb()

    Debug.Print
    Debug.Print "**** TABLES"
    
    For Each tbl In db.TableDefs
        str = tbl.Name
        Debug.Print str
        Call SetHiddenAttribute(acTable, str, booHide)
    Next tbl


    Debug.Print
    Debug.Print "**** QUERIES"
    
    For Each qry In db.QueryDefs
        str = qry.Name
        Debug.Print str
        Call SetHiddenAttribute(acQuery, str, booHide)
    Next qry


    Debug.Print
    Debug.Print "**** FORMS"
    
    For i = 0 To db.Containers("Forms").Documents.Count - 1
        str = db.Containers("Forms").Documents(i).Name
        Debug.Print str
        Call SetHiddenAttribute(acForm, str, booHide)
    Next


    Debug.Print
    Debug.Print "**** REPORTS"
    
    For i = 0 To db.Containers("Reports").Documents.Count - 1
        str = db.Containers("Reports").Documents(i).Name
        Debug.Print str
        Call SetHiddenAttribute(acReport, str, booHide)
    Next i

    
    Debug.Print
    Debug.Print "**** MODULES"
    
    For i = 0 To db.Containers("Modules").Documents.Count - 1
        str = db.Containers("Modules").Documents(i).Name
        Debug.Print str
        Call SetHiddenAttribute(acModule, str, booHide)
    Next i


    Debug.Print
    Debug.Print "**** SCRIPTS"

    For i = 0 To db.Containers("Scripts").Documents.Count - 1
        str = db.Containers("Scripts").Documents(i).Name
        Debug.Print str
        Call SetHiddenAttribute(acMacro, str, booHide)
    Next i

    Set db = Nothing
    
    Debug.Print
    Debug.Print "**** END"

End Sub

---

Boso's version 2 - fatta da zero:

Public Sub ut_NascondiTuttiGliOggetti(Optional hidden As Boolean = True)
    
    Dim ob As Variant
    Dim name As String
    
    
    Debug.Print
    Debug.Print "**** Tables"
    
    For Each ob In CurrentData.AllTables
        name = ob.name
        Debug.Print name
        If Not LCase(name) Like "msys*" Then
            Application.SetHiddenAttribute acTable, name, fhidden:=hidden
        End If
    Next
    
    
    Debug.Print
    Debug.Print "**** Forms"
    
    For Each ob In CurrentProject.AllForms
        name = ob.name
        Debug.Print name
        Application.SetHiddenAttribute acForm, name, fhidden:=hidden
    Next
    
    
    Debug.Print
    Debug.Print "**** Macros"
    
    For Each ob In CurrentProject.AllMacros
        name = ob.name
        Debug.Print name
        Application.SetHiddenAttribute acMacro, name, fhidden:=hidden
    Next
    
    
    Debug.Print
    Debug.Print "**** Modules"
    
    For Each ob In CurrentProject.AllModules
        name = ob.name
        Debug.Print name
        Application.SetHiddenAttribute acModule, name, fhidden:=hidden
    Next
    
    
    Debug.Print
    Debug.Print "**** Reports"
    
    For Each ob In CurrentProject.AllReports
        name = ob.name
        Debug.Print name
        Application.SetHiddenAttribute acReport, name, fhidden:=hidden
    Next
    
    
    Debug.Print
    Debug.Print "**** Querys"
    
    For Each ob In CurrentData.AllQueries
        name = ob.name
        Debug.Print name
        Application.SetHiddenAttribute acQuery, name, fhidden:=hidden
    Next
    
    
    Debug.Print
    Debug.Print "**** DONE ****"
    
End Sub

Send rendered control in Mail

Ritorna l'HTML dei controlli renderizzati

    Private Function getBody() As String

        Dim mSw As New IO.StringWriter()
        Dim mHtw As New HtmlTextWriter(mSw)
        Me.nome_del_controllo_server.RenderControl(mHtw)
        Return mSw.GetStringBuilder.ToString

    End Function

Bisogna aggiungere anche questa sub: serve per far funzionare la Function getBody()

    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As System.Web.UI.Control)

        ' Confirms that an HtmlForm control is rendered for the specified ASP.NET server control at run time.

    End Sub

Trovato qui.

How to: Disable constraints on a table

Sometimes it's useful to disable one or more constraints on a table, do something significant, and then re-enable the constaint(s) after you're done. This is most often done to improve performance during a bulk load operation.

According to SQL Server Books Online, we can disable constraints using the ALTER TABLE statement. Here's an excerpt from SQL Server Books Online that describes it.

{ CHECK | NOCHECK } CONSTRAINT

Specifies that constraint_name is enabled or disabled. This option can only be used with FOREIGN KEY and CHECK constraints. When NOCHECK is specified, the constraint is disabled and future inserts or updates to the column are not validated against the constraint conditions. DEFAULT, PRIMARY KEY, and UNIQUE constraints cannot be disabled.

ALL

    Specifies that all constraints are either disabled with the NOCHECK option or enabled with the CHECK option.

For more information, see the "Alter Table (Transact-SQL)" topic in SQL Server Books Online.

Let's consider an example.The following script disables a single constraint, does something, and then re-enables the constraint using the ALTER TABLE statement.

--disable the CK_Customer_CustomerType constraint 
ALTER TABLE Sales.Customer NOCHECK CONSTRAINT CK_Customer_CustomerType

--do something

--enable the CK_Customer_CustomerType constraint 
ALTER TABLE Sales.Customer CHECK CONSTRAINT CK_Customer_CustomerType

The following example shows how to disable all constraints on a table.

--disable all constraints for the Sales.SalesOrderHeader table 
ALTER TABLE Sales.SalesOrderHeader NOCHECK CONSTRAINT ALL  

--do something  
-- UPDATE...

--enable all constraints for the Sales.SalesOrderHeader table 
ALTER TABLE Sales.SalesOrderHeader CHECK CONSTRAINT ALL  

Testato il comando "CONSTRAINT ALL" su Sql 2008 R2.
Trovato qui.

Pass One Stored Procedure’s Result as Another Stored Procedure’s Parameter

This is one of the most asked questions in recent time and the answer is even simpler.

Here is the question – How to Pass One Stored Procedure’s Result as Another Stored Procedure’s Parameter. Stored Procedures are very old concepts and every day I see more and more adoption to Stored Procedure over dynamic code. When we have almost all of our code in Stored Procedure it is very common requirement that we have need of one stored procedure’s result to be passed as another stored procedure’s parameter.

Let us try to understand this with a simple example. Please note that this is a simple example, the matter of the fact, we can do the task of these two stored procedure in a single SP but our goal of this blog post is to understand how we can pass the result of one SP to another SP as a parameter.

Let us first create one Stored Procedure which gives us square of the passed parameter.

-- First Stored Procedure
CREATE PROCEDURE SquareSP
@MyFirstParam INT
AS
DECLARE @MyFirstParamSquare INT
SELECT @MyFirstParamSquare = @MyFirstParam*@MyFirstParam
-- Additional Code
RETURN (@MyFirstParamSquare)
GO

Now let us create second Stored Procedure which gives us area of the circle.

-- Second Stored Procedure
CREATE PROCEDURE FindArea
@SquaredParam INT
AS
DECLARE @AreaofCircle FLOAT
SELECT @AreaofCircle = @SquaredParam * PI()
RETURN (@AreaofCircle)
GO

You can clearly see that we need to pass the result of the first stored procedure (SquareSP) to second stored procedure (FindArea). We can do that by using following method:

-- Pass One Stored Procedure's Result as Another Stored Procedure's Parameter
DECLARE @ParamtoPass INT, @CircleArea FLOAT
-- First SP
EXEC @ParamtoPass = SquareSP 5
-- Second SP
EXEC @CircleArea = FindArea @ParamtoPass
SELECT @CircleArea FinalArea
GO

You can see that it is extremely simple to pass the result of the first stored procedure to second procedure.

You can clean up the code by running the following code.

-- Clean up
DROP PROCEDURE SquareSP
DROP PROCEDURE FindArea
GO

Trovato qui.

Group by Rows and Columns using XML PATH – Efficient CONCAT Trick

vedi anche qui.

I have a table of students and the courses they are enrolled with the name of the professor besides it. I would like to group the result with course and instructor name.
For example here is my table:

How can I generate result as following?

We can use XML PATH and come up with the solution where we combine two or more columns together and display desired result.

Here is the quick script:

-- Create table
CREATE TABLE #TestTable (StudentName VARCHAR(100), Course VARCHAR(100), Instructor VARCHAR(100), RoomNo VARCHAR(100))
GO

-- Populate table
INSERT INTO #TestTable (StudentName, Course, Instructor, RoomNo)
SELECT 'Mark', 'Algebra', 'Dr. James', '101'
UNION ALL
SELECT 'Mark', 'Maths', 'Dr. Jones', '201'
UNION ALL
SELECT 'Joe', 'Algebra', 'Dr. James', '101'
UNION ALL
SELECT 'Joe', 'Science', 'Dr. Ross', '301'
UNION ALL
SELECT 'Joe', 'Geography', 'Dr. Lisa', '401'
UNION ALL
SELECT 'Jenny', 'Algebra', 'Dr. James', '101'
GO

-- Check orginal data
SELECT *
FROM #TestTable
GO

-- Group by Data using column and XML PATH
SELECT
StudentName,
STUFF((
SELECT ', ' + Course + ' by ' + CAST(Instructor AS VARCHAR(MAX)) + ' in Room No ' + CAST(RoomNo AS VARCHAR(MAX))
FROM #TestTable
WHERE (StudentName = StudentCourses.StudentName)
FOR XML PATH (''))
,1,2,'') AS NameValues
FROM #TestTable StudentCourses
GROUP BY StudentName
GO

-- Clean up
DROP TABLE #TestTable
GO

Trovato qui.

Quickly Open a Command Prompt from the Windows Explorer Address Bar

Windows: Want to quickly run a command from within Windows Explorer? It turns out Windows has a built-in way to do this. Simply type in "cmd" in the address bar and it'll open the command prompt with the path to your current folder already set.


Testato su Win7, pare che su XP non funzioni. -- invece su XP ha funzionato!

Trovato qui.

Leopard Won't Eject External USB HDD

Sembra sia colpa di Spotlight/Finder che lasciano un file aperto sul disco.

Provare a:
1) riavviare Finder
2) provare da Disk Utility / Tasto destro / Eject
3) altrimenti, da Terminal:

hdiutil eject -force /Volumes/Backup\ OSX/

Il trucco è il "-force".


Trovato qui e qui.

Generating UTF-8 with System.Xml.XmlWriter

Today i decided to experiment with XmlWriter. The first i wanted to do was set the Encoding to UTF-8.:

StringBuilder stringBuilder = new StringBuilder();
XmlWriter xmlWriter = XmlWriter.Create(stringBuilder);
xmlWriter.Settings.Encoding = Encoding.UTF8;

When i ran this code i recieved the following exception: XmlException was unhandled: The "XmlWriterSettings.Encoding" property is read only and cannot be set. The documentation for the Settings property clearly says:

The XmlWriterSettings object returned by the Settings property cannot be modified. Any attempt to change individual settings results in an exception being thrown.

So i wrote the following:

StringBuilder stringBuilder = new StringBuilder();
XmlWriterSettings xmlWriterSettings = new XmlWriterSettings();
xmlWriterSettings.Encoding = Encoding.UTF8;
 
XmlWriter xmlWriter = XmlWriter.Create(stringBuilder, xmlWriterSettings);
xmlWriter.WriteStartDocument();
xmlWriter.WriteStartElement("root", "http://www.timvw.be/ns");
xmlWriter.WriteEndElement();
xmlWriter.WriteEndDocument();
xmlWriter.Flush();
xmlWriter.Close();
 
string xmlString = stringBuilder.ToString();

As you can see: is still not what i want. Apparently is the Encoding property ignored if the XmlWriter is not using a Stream. So here is my next attempt:

MemoryStream memoryStream = new MemoryStream();
// initialize xmlWriterSettings as above...
 
XmlWriter xmlWriter = XmlWriter.Create(memoryStream, xmlWriterSettings);
// call the same operations on the xmlWriter as above...
 
string xmlString = Encoding.UTF8.GetString(memoryStream.ToArray());

Ok, i'm getting close:

?

Luckily enough i knew that the ? (byte with value 239) at the beginning is the BOM (Byte Order Mark). In order to get rid of that byte i had to create my own instance of UTF8Encoding. Finally, i can present some working code:

MemoryStream memoryStream = new MemoryStream();
XmlWriterSettings xmlWriterSettings = new XmlWriterSettings();
xmlWriterSettings.Encoding = new UTF8Encoding(false);
xmlWriterSettings.ConformanceLevel = ConformanceLevel.Document;
xmlWriterSettings.Indent = true;
 
XmlWriter xmlWriter = XmlWriter.Create(memoryStream, xmlWriterSettings);
xmlWriter.WriteStartDocument();
xmlWriter.WriteStartElement("root", "http://www.timvw.be/ns");
xmlWriter.WriteEndElement();
xmlWriter.WriteEndDocument();
xmlWriter.Flush();
xmlWriter.Close();
 
string xmlString = Encoding.UTF8.GetString(memoryStream.ToArray());

Trovato qui.

ls con esclusione

ls con esclusione (e scrittura in append dell'output):

ls | grep -v .srt >> /Users/Boso/Desktop/film.txt

Get NBA scores RSS from ESPN

Ad esempio, l'URL http://sports.espn.go.com/nba/bottomline/scores ritorna questa stringa:

&nba_s_delay=120&nba_s_stamp=0306085718&nba_s_left1=^Boston%20109%20%20%20Philadelphia%20101%20(FINAL)&nba_s_right1_1=P.%20Pierce%2018pts,%207ast,%2011reb&nba_s_right1_2=J.%20Holiday%2018pts,%2010ast,%205reb&nba_s_right1_count=2&nba_s_url1=http://sports.espn.go.com/nba/boxscore?gameId=400278616&nba_s_left2=LA%20Lakers%20105%20%20%20^Oklahoma%20City%20122%20(FINAL)&nba_s_right2_1=K.%20Bryant%2030pts,%202ast,%203reb&nba_s_right2_2=R.%20Westbrook%2037pts,%205ast,%2010reb&nba_s_right2_count=2&nba_s_url2=http://sports.espn.go.com/nba/boxscore?gameId=400278617&nba_s_left3=^Denver%20120%20%20%20Sacramento%20113%20(FINAL)&nba_s_right3_1=T.%20Lawson%2024pts,%207ast,%203reb&nba_s_right3_2=I.%20Thomas%2023pts,%208ast,%203reb&nba_s_right3_3=D.%20Cousins%205%20blocks&nba_s_right3_4=M.%20Thornton%205-11%20three%20pointers&nba_s_right3_count=4&nba_s_url3=http://sports.espn.go.com/nba/boxscore?gameId=400278618&nba_s_count=3&nba_s_loaded=true

Il parametro nell'URL è la "/nba/".

Questo programma prende la stringa di output, e la splitta:

Debug delle coppie nome/valore:

nba_s_delay=120
nba_s_stamp=0306072047

nba_s_left1=Boston 109 Philadelphia 101 (FINAL)
nba_s_right1_1=P. Pierce 18pts, 7ast, 11reb
nba_s_right1_2=J. Holiday 18pts, 10ast, 5reb
nba_s_right1_count=2
nba_s_url1=http://sports.espn.go.com/nba/boxscore?gameId=400278616

nba_s_left2=LA Lakers 105 Oklahoma City 122 (FINAL)
nba_s_right2_1=K. Bryant 30pts, 2ast, 3reb
nba_s_right2_2=R. Westbrook 37pts, 5ast, 10reb
nba_s_right2_count=2
nba_s_url2=http://sports.espn.go.com/nba/boxscore?gameId=400278617

nba_s_left3=Denver 120 Sacramento 113 (FINAL)
nba_s_right3_1=T. Lawson 24pts, 7ast, 3reb
nba_s_right3_2=I. Thomas 23pts, 8ast, 3reb
nba_s_right3_3=D. Cousins 5 blocks
nba_s_right3_4=M. Thornton 5-11 three pointers
nba_s_right3_count=4

nba_s_url3=http://sports.espn.go.com/nba/boxscore?gameId=400278618
nba_s_count=3
nba_s_loaded=true

Versione PHP originale, che crea un RSS:

\n\n";
echo "\n\n";
echo "\n\n";
echo "\n";

echo "\n";
echo "http://www.nba.com\n";
echo "NBA Scores\n";
echo "en-us\n";
echo "\n";
echo " \n";
echo " http://www.mpiii.com/scores/nba.gif\n";
echo " http://www.nba.com\n";
echo "\n";
echo "info@nba.com\n";

$content = get_content ("http://sports.espn.go.com/nba/bottomline/scores");

$content_array=explode("&", $content);
$scorearray = array();
$i=0;
foreach($content_array as $content) {
	if (strpos($content, "_left")) {
		$equalpos = strpos($content, "=");
		$end = strlen($content);
		$title = substr($content, ($equalpos+1), $end);
		$title = str_replace("^", "", $title);
		$title = str_replace("%20", " ", $title);
		$scorearray[$i]["title"] = $title;

	}
	if (strpos($content, "_url")) {
		$equalpos = strpos($content, "=");
		$end = strlen($content);
		$url = substr($content, ($equalpos+1), $end);
		$url = str_replace("^", "", $url);
		$url = str_replace("%20", " ", $url);
		$scorearray[$i]["url"] = $url;
				$i++;

	}
}
foreach($scorearray as $score) {
	echo "\n";
	echo "\n";
	echo "".$score["url"]."\n";
	echo "\n";
}

echo "\n";
echo "\n";
?>

Trovato qui e qui.

Elenco di tutti i parametri/feed di espn.

Sono i dati utilizzati dall'applicazione ESPN Bottomline.

Chiudere tutte le connessioni ad un DB

Ad esempio per forzare una RESTORE del DB.

USE master
GO

DECLARE @kill varchar(8000) ; SET @kill = ''

SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE dbid IN 
  (
     db_id('databaseName')
   , db_id('anotherDataBase')
  )

IF @kill = ''
 PRINT 'No connection found.'
ELSE
 EXEC (@kill)   -- le parentesi servono!

Testato su SQL2005. Basato sullo script trovato qui.

Delete Windows Update Cache.bat

Stoppa il servizio di Windows Update, cancella la directory e fa ripartire il servizio.

net stop wuauserv       
cd /d %windir%       
rd /s SoftwareDistribution
net start wuauserv       

pause

Testato su Windows XP.

Responsive web design & HTML5 ToRead

• Flexibility: A Foundation for Responsive Design
• Responsive Web Design
• HTML5 Data Input Validation
• CSS Units of Measure: Going Beyond the Pixel

Access 2007: hide or show Ribbon

Private Sub showRibbon()

    DoCmd.ShowToolbar "Ribbon", acToolbarYes

End Sub

Private Sub hideRibbon()

    DoCmd.ShowToolbar "Ribbon", acToolbarNo

End Sub

SQL SERVER – An Interesting Case of Redundant Indexes – Index on Col1, Col2 and Index on Col1, Col2, Col3 – Part 2

Before you start reading this blog post, I strongly suggest you to read the part 1 of this series. It talks about What is Redundant Index. The story is a conversation between two individuals – Jon and Mike. They are different but have single goal learn and explore SQL Server. Their initial conversation sets the ground for this blog post. They earlier discussed what is a Redundant Index as well, discussed what are the special cases for the same. It is a general assumption (or common best practices) is to drop Redundant Indexes. Later Mike asks for special case where even though the index is clearly a Redundant Index, why it should not be removed. Jon promises to explain with a demo where a Redundant Index is useful and should not be dropped. Here is their conversation continued from earlier.

Mike – Today is Monday. You promised me a demo today where a Redundant Index is useful Index.

Jon - Absolutely. We will create two tables and will create absolutely same table. We will notice that on the first table Redundant Index will be useless and needs to be dropped whereas on the second table Redundant Index will useful and should not be dropped for performance.

Let us start with a demo.

Let us create two tables. One with all the column INT and second with a wider column CHAR (800).

USE tempdb
GO-- Create tableCREATE TABLE SampleTable1 (ID INT, Col1 INT, Col2 INT, Col3 INT)GOCREATE TABLE SampleTable2 (ID INT, Col1 INT, Col2 INT, Col3 CHAR(800))GO

Now let us create indexes on both the tables. We will make sure that created indexes are same on both the table.

Clustered Indexes are just created for reference – the results of this test will not be affected by its presence or absence of it. I have just created to rule out few doubts I anticipate by their absence. The important part is non clustered indexes. One of the non-clustered index is created on Col1 & Col2 and another one is created on Col1, Col2, and Col3.

Table1: SampleTable1 
Index on SampleTable1: IX_ST_Col1_Col2
Index on SampleTable1: IX_ST_Col1_Col2_Col3

Table2: SampleTable2
Index on SampleTable2: IX_ST_Col1_Col2
Index on SampleTable2: IX_ST_Col1_Col2_Col3

-- Create Indexes on Sample Table1CREATE CLUSTERED INDEX [CX_ST]ON SampleTable1 (ID)GOCREATE NONCLUSTERED INDEX [IX_ST_Col1_Col2]ON SampleTable1 (Col1, Col2)GOCREATE NONCLUSTERED INDEX [IX_ST_Col1_Col2_Col3]ON SampleTable1 (Col1, Col2, Col3)GO-- Create Indexes on Sample Table2CREATE CLUSTERED INDEX [CX_ST]ON SampleTable2 (ID)GOCREATE NONCLUSTERED INDEX [IX_ST_Col1_Col2]ON SampleTable2 (Col1, Col2)GOCREATE NONCLUSTERED INDEX [IX_ST_Col1_Col2_Col3]ON SampleTable2 (Col1, Col2, Col3)GO

Now let us populate both the tables. Both the tables have absolutely same data.

-- Populate tablesINSERT INTO SampleTable1 (ID, Col1, Col2, Col3)SELECT RAND()*10000, RAND()*1000, RAND()*100, RAND()*10
GO 100000INSERT INTO SampleTable2 (ID, Col1, Col2, Col3)SELECT *FROM SampleTable1
GO

Now is the most interesting part. For this first enable the execution plan in SSMS (shortcut key – CTRL + M).

We will be doing two tests. Let describe our first test.

Test 1: Select a smaller set of the data

In this test we will run a same query on both the tables. Both the times we will apply our path of the index on the each table. As there are 2 tables and each have 2 indexes we will have a total of 4 indexes.

Table1: SampleTable1 
Index on SampleTable1: IX_ST_Col1_Col2
Index on SampleTable1: IX_ST_Col1_Col2_Col3

Table2: SampleTable2
Index on SampleTable2: IX_ST_Col1_Col2
Index on SampleTable2: IX_ST_Col1_Col2_Col3

Now let us run following script with keeping the Actual Execution Plan on (shortcut key CTRM: + M)

Let us first run two scripts for SampleTable1

-- Select from SampleTable1SELECT Col1, Col2FROM SampleTable1 st WITH(INDEX([IX_ST_Col1_Col2]))WHERE st.Col1 = 10
GO-- Select from SampleTable1SELECT Col1, Col2FROM SampleTable1 st WITH(INDEX(IX_ST_Col1_Col2_Col3))WHERE st.Col1 = 10
GO

Let us check the execution plan:

You can notice from the execution plan that in the case of the SampleTable1 it does not matter if we use either of the index the performance of the both the query is same. Both the queries are using the same amount of the resources. In this case, Col3 is an integer and for SQL Server the width of the column does not make much difference. I can clearly say in this case Indexes are redundant as they are giving the same performance.

(Note: If you are going to ask me to change the SELECT statement to also include Col3, it will become totally different scenario as it will require to do a key lookup for IX_ST_Col1_Col2. If your SELECT statement has Col1, Col2, Col3 – the optimal index here is IX_ST_Col1_Col2_Col3, there is no further discussion in that case).

(A Quick Tip: When we compare execution plan – the higher cost compared to the batch explains higher usage of the resources and expensive query.)

Now looking at both the indexes indexes IX_ST_Col1_Col2 is subset of IX_ST_Col1_Col2_Col3 and as mentioned in an earlier note if there is an additional column (col3) is in the SELECT statement that index will be more suitable. We can easily remove IX_ST_Col1_Col2 index in this particular special case (note this does not apply all the time).

Now let us run similar script for SampleTable2

-- Select from SampleTable2SELECT Col1, Col2FROM SampleTable2 st WITH(INDEX([IX_ST_Col1_Col2]))WHERE st.Col1 = 10
GO-- Select from SampleTable2SELECT Col1, Col2FROM SampleTable2 st WITH(INDEX([IX_ST_Col1_Col2_Col3]))WHERE st.Col1 = 10
GO

Let us check the execution plan:

You can notice from the execution plan that in the case of the SampleTable2 it matters a lot about which index is used for the query as the performance differences between those queries is huge.  One of the query is using very little resources and another one is taking a huge amount of the resources. In this case, Col3 is a CHAR (800) datatype which is fixed length string datatype. In this case, for SQL Server the width of the column does make a big difference. We can clearly say that here in our SELECT statement IX_ST_Col1_Col2 is the most optimal indexes.

If that is the case, what is the use of IX_ST_Col1_Col2_Col3. Well, the answer of this question is also interesting. If you change the SELECT statement to also include Col3, it will become totally different scenario as it will require to do a key lookup for IX_ST_Col1_Col2. If your SELECT statement has Col1, Col2, Col3 – the optimal index here is IX_ST_Col1_Col2_Col3. The need of the both the indexes is different and they achieve a specific task. If you think IX_ST_Col1_Col2 is redundant as a more inclusive index IX_ST_Col1_Col2_Col3 exists, it will be not the optimal thinking. Even though, IX_ST_Col1_Col2_Col3 includes all the columns, when SQL Server only needs Col1 and Col2 it finds IX_ST_Col1_Col2 more suitable for performance.

I hope this is now clear to you that how to identify if the redundant index is useful or useless now.

Mike - Thanks Jon, this is a great explanation. Let me quickly summarize it.

Even though Indexes look redundant there may be some queries which may find them useful. This usually happens when the data type of the of any column is much wider than other columns. Before dropping the indexes one should properly validate the usage patterns of the indexes and query workloads. One should properly test everything before taking any actions. 

Jon – Good summary. Test before you Act! However, you should notice that this is not the only case when redundant indexes are useful. There may be other cases too!

Mike – I understand. However, before you continue further, I see that you called this as a Test 1. Is there any test 2 with the larger dataset? Does it also validate my earlier summary.

Jon – Another good question – Let us see the Test 2 in Friday’s blog post. You can clean up your database by dropping your test tables.

-- Clean UpDROP TABLE SampleTable1
GODROP TABLE SampleTable2
GO

Stay tuned for part 3 of this series.

Trovato qui.

An Interesting Case of Redundant Indexes – Index on Col1, Col2 and Index on Col1, Col2, Col3 – Part 1

Index never stops amazing me, there are so much to learn about Index that I never feel that there is enough knowledge out about this subject. If you are interested you can watch my Indexing Course on Pluralsight for further learning on this subject.

Instead of going on the theory overload – let us start with this blog post as a conversation between two individuals – Jon and Mike. These are just random names. Jon is senior and experienced SQL Server Expert and Mike is beginner with SQL Server.

Mike – What is Redundant Index?

Jon – Indexes are redundant when they have similar columns as a part of a definition. Additionally, the indexes are considered redundant when their first few columns are in the same position with same order by direction are also considered as a redundant.

Mike – Would you please explain it with examples?

Jon – Sure, Let us assume we have two indexes:

Index 1: Col1, Col2, Col3
Index 2: Col1, Col2, Col3

Now if you look at them – they have the same columns as a part of their definition, so they are indeed redundant indexes. However, look at the following scenario:

Index 3: Col1, Col2
Index 4: Col1, Col2, Col3

In this case they are also considered as a redundant because the position of the Col1, Col2 are same in both of the index. It is quite commonly considered that if the initial positions of the columns are the same, they are redundant.

However, there is one more concept here to be looked at as well before we make certain about their redundancy. Look at the following indexes:

Index 5: Col1 ASC, Col2 DESC
Index 6: Col1 DESC, Col2 DESC, Col3 ASC

In this case if the initial positions are the same, they are not redundant as the order of the column is not the same.

There are lot more to discuss but this is just to give you an initial idea. There is one more concept we should consider before calling any index redundant is Included Index. Here is the simple scenario for it.

Index 7: Col1 ASC Included (col2)
Index 8: Col1 ASC Included (col3)

You can notice they have same initial column but the Included columns are totally different.

Mike – Thanks, I got it. It seems that Redundant Indexes are not good and they should be dropped correct.

In case of Index 1 and Index 2 I think we should drop either of the one.

In case of Index 3 and Index 4 I believe Index 4 has more columns and covering, so we should keep it and drop the other one.

In case of Index 5 and Index 6, they are both different indexes so we should keep both.

In case of Index 7 and Index 8, again they are both different index in this case. They can be redundant if the included columns are overlapping to each other.

Am I correct to say this?

Jon – Very good analysis. You are very close to the understanding. Generally, redundant indexes are not good and they should be absolutely addressed. In most cases, redundant cases should be dropped.

Mike – Ahha, so in the most cases indexes should be dropped. Ok, so is there any script or guidance to detect redundant indexes for the most cases.

Jon – Sure, here is the script which does that – however, this query just addresses the scenario of the Index 3 and Index 4. It does not talk about Included Columns or Index Order (ASC or DESC). Just use that for a start but do your analysis on this subject before you drop your indexes. You still have to check for order of the index and included columns as well.

Mike – Perfect, I understand that the script is just for a quick start and not the complete solution. Now you mentioned “In Most Cases” – what are the special cases. What are the cases when an Index which absolutely qualify for the redundant index but should not be dropped. Would you please explain the special cases?

Jon – Absolutely – there are always special cases. For example the width of the column matters.

Mike – Okey I would love to learn more about this – would you please explain.

Jon – Absolutely – I have a working example of it – Checkout Monday’s blog post. I will explain you in detail.



Trovato qui.