Week Number of a month

=IF(MONTH(F2)=1,WEEKNUM(F2),IF(WEEKDAY(EOMONTH(F2, -1)+1) = 1, WEEKNUM(F2)-WEEKNUM(EOMONTH(F2,-1)), WEEKNUM(F2)-WEEKNUM(EOMONTH(F2,-1))+1))

Assumption:

1. Sunday is the first day of a week
2. Week days are represented by Sun = 1 ... Sat = 7

If Sunday is not the first day of a week, change the number 1 in red to desired day number.

I tried this in MSExcel 2013.

Listing columns in SQL tables

Here is the SQL script to list columns in tables:
 
SELECT
  tab.TABLE_TYPE
, tab.TABLE_NAME
, col.COLUMN_NAME
, DATA_TYPE
, IS_NULLABLE
, CHARACTER_MAXIMUM_LENGTH
FROM
INFORMATION_SCHEMA.TABLES tab
INNER JOIN INFORMATION_SCHEMA.COLUMNS col
ON tab.TABLE_NAME = col.TABLE_NAME
WHERE
tab.TABLE_CATALOG = col.TABLE_CATALOG
AND tab.TABLE_SCHEMA = col.TABLE_SCHEMA
AND COLUMN_NAME like 'COLNAME%'
ORDER
BY
TABLE_TYPE, tab.TABLE_NAME
, COLUMN_NAME

Opereating System returned 5(Access is denied) while attempting to RestoreContainer::ValidateTargetForCreate on

I got the following error while creating a database:

Opereating System returned 5(Access is denied) while attempting to RestoreContainer::ValidateTargetForCreate on

Cause: The user running the MSSQLSERVER service does not have proper access to the path.

Solution: Give proper access to the 'user' running the MSSQLSERVER service.

SQL Server backups in network location

I had to take backup off-server to a network location. So, created a job tried running manually; it ran just fine. I scheduled the job check during the run time, it fail! Job history
(

Expand SQL Server Agent under Instance

Expand Jobs

Right click the job and choose View History

)

showed the \ did not have permission on the location. Gave the domain user full access to the location, still did not work. I check the user under which the SQL Server (InstanceName) service was running, it was a different domain user; once this domain user had FULL CONTROL to the network location, the job started backing up in a network location specified by UNC.

Here is a reference: Microsoft Article

Hope this help someone.

astu...

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...