Sql Grab the SQL Command/Statement that fired a Trigger

Hi All, here a easy script to Grab the SQL Command/Statement that fired a Trigger:

--LWEBCODE
--Website Building & free script at http://lwebcode.blogspot.com/
CREATE TRIGGER trgUpdate
ON User_Master
FOR UPDATE

AS
--Print ('AFTER Trigger [trgUpdate] – Trigger executed !!')
--DBCC INPUTBUFFER(@@spid)

--Before Check if not exists
CREATE TABLE TMP_CMD ( [eventtype] NVARCHAR(30), [parameters] INT, [eventinfo] NVARCHAR(4000))
DECLARE @sSql nvarchar(4000)
SET @sSql = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'
INSERT INTO TMP_CMD EXEC(@sSql)

This works but has a big problem, it can’t grab more than 4000 characters , to solve this  there are SQL “Magic Table” which stores last row edited by query
To clone this row you can use:

SELECT * INTO TMP_INSERTED FROM INSERTED
SELECT * INTO TMP_DELETED FROM DELETED

and you can inspect affected row.

c# fast traslation of websites, Extract *.resx To *.xls

Hi All, Here a very quick Class which get all Entries to translate from resource *.resx file and make a Excel *.xls file with a list of all Entries.
Program writes entries in “A” column, 
In “B” (“C”,”D”…if you have more languages) Final user have to puts his language translation,
when done, after xls is saved and came back:
make a copy of *.resx file
With advanced text editor such as notepad++ or ultraedit after have make a copy of *.resx file, with “Find and Replace” tool I can easly traslate all the entries.
here the code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using Excel; // Add refer to Excel (in COM object Microsoft Excel Library )
using System.IO;

//#LWEBCODE
//Website Building & free script at http://lwebcode.blogspot.com/

namespace LWEBCODE
{
    public partial class ResxToXls
    {
        public static void MyStart()
        {
        string sPath =@"\\server\folder\" // where resx files are
        string DestFile = "", Title = "", ResxFile = "";
        string[] SourceFiles = Directory.GetFiles(sPath);
        if (SourceFiles.Length > 0)
        {
            for (int i = 0; i < SourceFiles.Length; i++)
            {
                if (SourceFiles[i].EndsWith(".resx"))
                {
                ResxFile = SourceFiles[i];
                Title = PageNameASPX(ResxFile);
                DestFile = ResxFile.Substring(0, ResxFile.LastIndexOf(@"\") + 1) + Title + ".xls";

                ReadResx(ResxFile, DestFile);
                }
            }
        }
        }

        public static void ReadResx(string ResxPath, string XlsPath)
        {
        Excel.ApplicationClass excel = new ApplicationClass();
        XmlTextReader xTr = new XmlTextReader(ResxPath);
        string ris = "";
        bool bwrite = true;
            while (xTr.Read())
            {
                // Do some work here on the data.
                //Console.WriteLine(xTr.Name);
                switch (xTr.NodeType)
                {
                case XmlNodeType.Element: // The node is an element.
                Console.Write("");

                break;
                case XmlNodeType.Text: //Display the text in each element.
                Console.WriteLine(xTr.Value);

                if (bwrite)
                ris += xTr.Value + ";";
                break;
                case XmlNodeType.EndElement: //Display the end of the element.
                Console.Write("");

                if ((xTr.Name.ToLower() == "comment") || (xTr.Name.ToLower() == "resheader"))
                bwrite = true;
                break;
                }
            }
        Console.Write(ris);
        Excel.Workbook workbook = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
        Excel.Sheets sheets = workbook.Worksheets;

        excel.Visible = true;
        Excel.Worksheet mySheet = (Excel.Worksheet)sheets.get_Item(1);

        string[] splRis = ris.Split(Convert.ToChar(";"));
        int ExRow = 0;
        for (int j = 1; j < splRis.Length; j++)
        {
            ExRow = j + 4;
            Excel.Range myCell = (Excel.Range)mySheet.get_Range("A" + ExRow.ToString(), "A" + ExRow.ToString());
            myCell.Value = splRis[j - 1];
        }
        workbook.SaveAs(XlsPath, Excel.XlFileFormat.xlExcel9795, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null);
        excel.Quit();
        }

        public static string PageNameASPX(string ResxPath)
        {
        return ResxPath.Substring(ResxPath.LastIndexOf(@"\") + 1).Replace(".resx","");
        }
    }
}

SQL CURSOR, looping on talbe's or view's rows with Cursor like Recordset or DataReader

Hi All, here an example on how loop on some rows in SQL Stored Procedure, like a visual basic RecordSet or .NET Framework DataReader, simple put your query at line 7 of script:

SET @SQL ='SELECT Field1,Field2 FROM Table_Name '

That's all, We have spent lot of time to find an example which works, finally it comes:








--LWebCode SQL Script
DECLARE @SQL NVARCHAR (4000)
DECLARE @DynamicSQL NVARCHAR(250)
DECLARE @Field1 NVARCHAR(100)
DECLARE @Field2 NVARCHAR(100)
DECLARE @outputCursor CURSOR
SET @SQL ='SELECT Field1,Field2 FROM Table_Name '
SET @DynamicSQL = 'SET @outputCursor = CURSOR FORWARD_ONLY STATIC FOR ' +
@SQL + ' ; OPEN @outputCursor'
exec sp_executesql -- sp_executesql will essentially create a sproc
@DynamicSQL, -- The SQL statement to execute (body of sproc)
N'@outputCursor CURSOR OUTPUT', -- The parameter list for the sproc: OUTPUT CURSOR
@outputCursor OUTPUT
FETCH NEXT FROM @outputCursor INTO @Field1,@IDField2
WHILE @@FETCH_STATUS = 0
BEGIN
/*here is loop put your code here*/
SET @SQL='SELECT * FROM t_name WHERE Field1=''' + @Field1 + ''''
EXEC SP_EXECUTESQL @SQL
FETCH NEXT FROM @outputCursor INTO @Field1,@Field2
END

VBA (Excel) – Add Single Quote, Excel text cells, Zeros 0s Fix

Hill All, happens to copy and paste from SQL tables or web Page to Excel files, if some fields begin with Zero 
example:“0000545LWEBCODE” , 
when paste to Excel Zeros 0s before other numbers were removed 
example:“545LWEBCODE”
To fix Zeros 0 before number and other things , Here is VBA funcion/Script which add a single quote ‘ before each cells: you can copy from any external platform like Internet, SQL Tables, SQL Views etc.., and paste it in your Microsoft Excel sheet,
Just Copy and Paste this code in a Module a run it.


‘** VBA Script Excel Zeros 0s Fix Script by lwebcode.blogspot.com
Public Sub AddQuote()
Dim Start_, End_, Col, i As Integer
Dim SName As String

Start_ = InputBox(“Row Start:”, “Start”, “1”)
End_ = InputBox(“Row End:”, “End”, “10000”)

Col = CInt(InputBox(“Column (in Number) to Add Quote”, “Column”, “1”))

SName = InputBox(“Sheets’s Name:”, “Sheets”, Sheets(1).Name)

For i = Start_ To End_
    If Sheets(SName).Cells(i, Col).Value “” Then
        Sheets(SName).Cells(i, Col).Value = “‘” & Sheets(SName).Cells(i, Col).Value
    End If
Next

MsgBox “Executed”, vbInformation, “Executed”

End Sub

SQL - Fast Table Copy

Hi All, this script is very easy,a simple and fast solution to copy (structure and data) a table :

SELECT * INTO Table_Destination_Name FROM Table_Source_Name

Warning:Using this script Index aren't copied in new table (Table_Destination_Name) for adds it you have to add it manually.