MSSQL - Split A String of Text Separated by Delimiter into A Table

/*

This SP returns a table containing rows of items that were separated by the delimiter in the given string

*/

SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO CREATE FUNCTION [dbo].[SplitMe]
(
    @List varchar(max),
    @SplitOn nvarchar(5)
)
RETURNS @RtnValue table (Line varchar(8000))
AS BEGIN    While (Charindex(@SplitOn,@List)>0)
    BEGIN
        INSERT INTO @RtnValue(Line)
        SELECT skuid = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List) - 1)))
        SET @List = Substring(@List,Charindex(@SplitOn,@List) + len(@SplitOn), len(@List))
    END
     Return
END 

MSSQL - Output SQL Results to A Preformatted Excel File

/*

This SP uses a pre-formatted excel file as template then save the given sql results

into the template and email it to the user.

NOTE: sp_SQLNotify is used to send email; please see detail in

http://www.howtogeek.com/howto/database/sending-automated-job-email-notifications-in-sql-server-with-smtp/

EXAMPLE:

EXEC USP_ExportToExcel

     'C:\Template.xlsx',

     'Sheet1',

     'SELECT * from sys.servers',

     'from@email.com',

     'to@email.com',

     'Your daily missed sales data report'

*/

SET QUOTED_IDENTIFIER OFF

GO

ALTER PROCEDURE USP_ExportToExcel

@OutputTemplate VARCHAR(500),       -- PathName of the Excel containing header and format

@SheetName VARCHAR(100) = 'Sheet1', -- Name of the sheet

@SelectSQL VARCHAR(4000),                 -- Your query SQL

@FromEmail VARCHAR(100)='',               -- Email sender

@ToEmailList VARCHAR(1000)='',            -- Email to

@EmailSubject VARCHAR(1000)='',           -- Email subject

@NewFileName VARCHAR(500)=''        -- Email attachment; the output result

AS

DECLARE @SQL NVARCHAR(4000)

DECLARE @FileName VARCHAR(200)

SELECT @FileName = REVERSE(SUBSTRING(REVERSE(@OutputTemplate), 1, CHARINDEX('\', REVERSE(@OutputTemplate))-1))

PRINT @FileName

-- COPY TEMPLATE TO A TEMP FOLDER

SELECT @SQL = 'COPY /Y "' + @OutputTemplate + '" "C:\Temp"'

EXEC xp_cmdshell @SQL

-- RUN SELECT INTO A TEMP TABLE

SELECT @SQL = SUBSTRING(@SelectSQL, 1, CHARINDEX(' FROM', @SelectSQL)) + 'INTO ##EXPORTTEMP FROM' + SUBSTRING(@SelectSQL, CHARINDEX('FROM', @SelectSQL)+4, 8000)

EXEC sp_executesql @SQL

-- EXPORT DATA TO EXCEL FILE

IF (CHARINDEX('xlsx', @OutputTemplate) > 0)

BEGIN

    SELECT @SQL = 'INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;Database=C:\TEMP\' + @FileName + ';HDR=NO;'',''SELECT * FROM [' + @SheetName + '$]'') SELECT * FROM ##EXPORTTEMP'

END

ELSE

BEGIN

    SELECT @SQL = 'INSERT INTO OPENROWSET(''Microsoft.JET.OLEDB.4.0'', ''Excel 8.0;Database=C:\TEMP\' + @FileName + ';HDR=YES'',''SELECT * FROM [' + @SheetName + '$]'') SELECT * FROM ##EXPORTTEMP'

END

EXEC sp_executesql @SQL

-- RENAME TO NEW FILENAME

IF @NewFileName <> ''

BEGIN

    SELECT @SQL = 'REN "C:\TEMP\' + @FileName + '" "' + @NewFileName + '"'

    EXEC xp_cmdshell @SQL

    SELECT @FileName = @NewFileName

END

-- SEND EMAIL IF REQUESTED

IF @FromEmail <> '' AND @ToEmailList <> ''

BEGIN

    SELECT @SQL = 'sp_SQLNotify ''' + @FromEmail + ''', ''' + @ToEmailList + ''', ''' + @EmailSubject + ''',  ''Automated report'', ''C:\TEMP\' + @FileName + ''''

    EXEC sp_executesql @SQL

END

-- DELETE FILE

SELECT @SQL = 'DEL "C:\Temp\' + @FileName + '"'

EXEC xp_cmdshell @SQL

DROP TABLE ##EXPORTTEMP

Windows - Enable EventLogging for User/Service Accounts That Don't Have Admin Rights

// to display current settings

wevtutil gl application

// if exception during the eventlogging call; add the permission using the command below (reboot required):

wevtutil sl Application /ca:O:BAG:SYD:(A;;0xf0007;;;SY)(A;;0x7;;;BA)(A;;0x7;;;SO)(A;;0x3;;;IU)(A;;0x3;;;SU)(A;;0x3;;;S-1-5-3)(A;;0x3;;;S-1-5-33)(A;;0x1;;;S-1-5-32-573)(A;;0x3;;;AU)

// provide users the read/write permission to the following folder

C:\Windows\System32\config

// grant read access to BTSQLQA or BTSQLUAT

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\eventlog\Security

// to test any account with ability to write to eventlog or not; runas /user:accnt cmd and run the following cmd:

eventcreate /ID 1 /L APPLICATION /T INFORMATION  /SO MYEVENTSOURCE /D "My first log"

MSSQL - Changing Computer Name

SELECT @@SERVERNAME        -- check current servername;

-- restart sql service after name change

EXEC sp_dropserver 'OldComputerName';         -- remove old server name

EXEC sp_addserver 'NewComputerName', local;  -- add new server name

BizTalk - Clean MsgBox

1. Stop all BizTalk services

2. Type “iisreset” at command line to recycle IIS service

3. Execute the stored procedure “bts_CleanupMsgbox” on your message box database

4. Execute the stroed procedure “bts_PurgeSubscriptions” on your message box database

(if the above stored procedures can't be found, go to your BizTalk installation directory, under “Schema” folder, you can find all original scripts of these stored procedures)

5. Clean up the message box log by running the backup statement similar to below

backup log msgBoxDb to disk = “yourbackupdirectory\yourbackupfile.bak' with init, stats = 5

6. Restart BizTalk services

By Edmund Zhao

Windows - A Few Useful Windows Cmmand-Line tools

WMIC process get description,executablepath

To show the current running processes

NET SART/NET STOP

To start or stop services

FINDSTR /M /S "searchstring" *.*

Find a string from the files specified by the file pattern

SET LofFilePath=C:\Test%date:~-4,4%%date:~-10,2%%date:~-7,2%.log

Use this to create a logfile path with date stamp

%~d0

CD "%~d0%~p0"

Use this to set the default folder to the location of batch script

SC query

A util to check status of Windows services

NET localgroup GroupName

Get a list of user accounts assigned into this localgroup

LOGEVENT -s S|T|W|E|F -c 0 -e -1 -r "SourceName" "BodyText"
EVENTCREATE /S ServerName /L Application /T ERROR /SO "SourceName" /ID 1 /D "BodyText"

Use this to create an entry in the EventLog

FOR /d /r . %%d in (bin.) do @if exist "%%d" rd /s/q "%%d"
Remove all file folders with the specified name "bin."

FORFILES /P "C:\Archive" /M *.* /D -30 /C "CMD /c DEL /q @path"
Remove files from the folder "C:\Archive" that are 30 days or older

QWINSTA /SERVER:RemoteServer

Find out all the existing sessions from a remote server

RWINSTA 2 /SERVER:RemoteServer

Close a remote seesion by ID

QUERY USER
Display all connected (active or inactive) users

LOGOFF sessionID /SERVER:RemoteServer
Force a user account to logoff

ECHO %date:~-4,4%-%date:~-10,2%-%date:~-7,2%
Format the date

for %i in (notepad.exe) do @echo %~$PATH:i
Search for the first folder exe is being executed from

for /F %i in ('findstr /M "SynnexB2B" *.dat') DO DEL %i

Delete found files

quser
User this comment to query who is login on a server.  Below is a script to list all users:
FOR /F "tokens=1-6" %%i IN ('quser /SERVER:MyServer') DO (
    IF "%%i" NEQ "USERNAME" (
        IF "%%k" EQU "Disc" (
            ECHO Svr=MyServer UName=%%i ID=%%j %%k Since=%%m
        ) ELSE (
            ECHO Svr=MyServer UName=%%i ID=%%k %%l Since=%%n
        )
    )
)

netstat -an 2 | findstr "15.200.14 15.201.8 15.217.72"

Tracking incoming HTTP Posting

MSSQL - [ODBC SQL Server Driver] invalid parameter number

This is the error when trying to load external data from external source using parameterized SQL into Excel.  To parameterize your SQL you have to use "From Microsoft Query."  This is the only external source that accepts parameters in Excel 2010.

In the example above, I setup a connection properties to call a stored procedure with 3 parameters and I keep getting the "Invalid parameter number" error.  As it turns out, the user doesn't have enough permission to execute the stored procedure but the error certainly misled me and wasted a lot of time on it.