SQL Server 2008 Alias

SQL Server Alias setup in the client setup and NOT the server setup. It took sometime to understand the fact. The other thing that tricked me was 32-bit and 64-bit version of the tool, cliconfg.exe. It depends on the application you are trying to setup the alias for - meaning, if the application is 32-bit use c:\Windows\SysWOW64\cliconfg.exe and if it is 64-bit application just use normal cliconfg.exe (assuming that you are in 64-bit machine).

cliconfg.exe is a Windows OS tool and therefore does not need additional installs.

1. Run the application, either from c:\Windows\SysWOW64\cliconfg.exe (for 32-bit)
    or c:\Windows\System32\cliconfg.exe or just press windows + r and type cliconfg and hit enter (for 64-    bit)
2. In SQL Server Client Network Utility window > General tab, enable TCP/IP (or desired protocol)
3. In Alias tab, click Add
4. In Edit Network Library Configuration window, give in Server alias, Server Name, and Server name
5. Click OK
6. Click OK

astu...

LogParser to count number of emails

The task was to count emails sent in and sent out from the organization. LogParser 2.2 presented as an easy tool to parse logs in exchange server for counting the emails. For some reason, COUNT DISTINCT was not working so, I had take a round about.

1. Parse log files and count all MSGID and save the output to as a CSV file.
2. Parse the CSV file created above and count the MSGID
3. Here, I created a graphs as output for few and for others I used the result in excel for further manipulation.

Here are few queries I used:

internal email

SELECT DATE, MSGID, COUNT(MSGID) FROM

WHERE Recipient-Address NOT LIKE '%excludeThis@my.org'

AND Sender-Address NOT LIKE '%excludeThis@my.org%'

AND Sender-Address LIKE '%@my.org%'

For external Emails

LogParser -i:W3C "SELECT Date, MSGID, COUNT(MSGID) FROM \\LogFileLocation\2012*.log WHERE Sender-Address NOT LIKE '%@my.org%' AND Recipient-Address NOT LIKE '%excludeThis@my.org%' AND STRLEN(Sender-Address) > 3 GROUP BY Date, MSGID ORDER BY Date" -o:CSV > X:\ExternalEmail2012.csv

Then used above file to create user usable output file

LogParser -i:CSV "SELECT Year, Month, COUNT(MSGID) FROM X:\ExternalEmail2012.csv GROUP BY Year, Month ORDER BY Month" -o:CSV > H:\MonthlyBreakDown2012.CSV

Few commands for ref:

LogParser /?

LogParser -h -o:chart

LogParser -h -i:CSV

Links for ref: (was working at the time of writing)

http://www.logql.com/documentation/functions/

http://logparserplus.com/Functions

astu...

D.

T-SQL update one table from another

When you want to update one table values from another table using following code might help:

UPDATE t1

  SET t1.col2 = t2.col5

  FROM Table1 AS t1

  INNER JOIN Table2 AS t2

  ON t1.Indentifier = t2.Ref_2_t1_Identifier

  WHERE

Read more on Microsoft explanation of UPDATE or stackoverflow.com forum

astu...

Memory Access Violation

Lesson learnt today!

The old application designed for 16 bit or 32-bit may not run properly in new terminal server setting. Believe me we had a big one!! The application was originally designed in/or 16-bit architecture and we tried to run it in new MS Terminal Server will all bell and whistle but it did not work and gave us memory access violation.
To solve this following steps were followed in the MS Terminal Server:
1. Right click My Computer
2. Select Properties
3. Click on Advance System Settings
4. Click on Settings under Advance > Performance
5. Select Data Execution Prevention tab
6. Select Turn on DEP for all programs and services except those I select: option
7. Click Add
8. Browse to the exe of the file, it will show up in the "selected" box
9. Click OK


After this the application ran fine.

astu...

Padding zeros (0) in front of number or numerical string in T-SQL

To skip story start from the START:
I had to create a Alternate ID from the Employee ID in the following format
ABCD00000
4 characters followed by 5 digits. Employee ID was 4 or 5 digit so would have to pad 0 if 4 digits. I used STR combined with REPLACE function to do this.

START:
Syntax
STR (expression [, length [, decimal] ] )

Return type: varchar

expression: numerical vaue (numerical string would work too)
length: Length of resulting string including - + sign, decimal and digits
decimal: digits after decimal

REPLACE(string, pattern, replacement)

Return type: nvarchar or varchar
string: where you want to replace
pattern: what you want to replace
replacement: string to replace with

Example:

SELECT REPLACE(STR(123.45,10,4), ' ', '0')
SELECT REPLACE(STR('123.45',10,4), ' ', '0')
Result:
00123.4500


SELECT REPLACE(STR(123.45,10,0), ' ', '0')
SELECT REPLACE(STR('123.45',10,0), ' ', '0')
Result:
0000000123


SELECT REPLACE(STR(12345,10,0), ' ', '0')
SELECT REPLACE(STR('12345',10,0), ' ', '0')
Result:
0000012345


Astu...

Converting Number to Text in Crystal Reports

When changing numer to text, it puts comma and decimal, for eg

var = 1234

"aa" + CSTR(var)

would give: aa1,234.0

to get rid of , and . use following syntax

TOTEXT({variable}, 0, "")

Arguments: Variable is the one you are trying to convert into text

0: number of decimal places you want

"": thousand separator (here its nothing)

(Reference)

astu...