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

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

0x80070005 Access is Denied

I was creating a scheduled task in Windows XP SP3 and I was getting 0x80070005 Access is Denied error. I looked and tried all I could find in the net. I finally tried the following and it worked!

I made the user local administrator of the machine.

Everything went fine then after.

astu...

Alternating Color for GROUP HEADERS

I am hiding detail row and just showing group header in SSRS and needed alternating color, this is what i used

=IIf (RunningValue (Fields!SomeField.Value, CountDistinct, Nothing) Mod 2 = 0,
"Silver", "White")

it worked !

astu...

Reducing size of MS SQL Server Log File

Couple of days back I realized that my log file has grown out of control. Usually backing up transactions logs and then shrinking the log file should have taken care but I had to take one more step.

I changed the recovery model of the dB from 'Full' to 'Simple' then shrunk the log file and changed the recovery model back to 'Full'. I was able to reduce my .ldf file size from almost 100GB to about 500KB! Quite a resize.

NOTE: Take all cautions while dealing with backend dB. Backups, no users, front end program considerations.

astu. . .

GP Note Index

I have collected this for my reference from following two references and I thank you both for putting this out.

Ref:
	from msdn blog
	From mbsguru, blogspot

The Note Index (NOTEINDX) field in each record of SY_Company_MSTR (SY1500) table store the next Note Index to be used for that company. Every Master and Transaction record is assigned a Note Index when created. This Note Index is used as a foreign key when looking note against SY_Record_Notes_MSTR (SY03900).

To get Note Index use the following:
For Dexterity: call Get_Next_Note_Index, 'Note Index';
For SQL Scripting, call procedure: smGetNextNoteIndex

Yous SQL script as following:
declare @companyId smallint,
@noteIndex numeric (19, 5)
@errorState int;

-- get company id
select @companyId = CMPANYID
from DYNAMICS..SY01500
where INTERID = DB_Name()

--get and increment the next note index
exec DYNAMICS..smGetNextNoteIndex @companyId, 1, @noteIndex output, @errorState output


print @noteIndex
print @errorState

Displaying PPT in SharePoint

Follow this link. Follow the instructions, they are straight forward, but make sure that you choose MULTPLE UPLOAD when uploading the file, the web version of ppt has more than one file so, SP informs you that you are uploading xx files even when you just selected 1 web version of PPT file. Thanks Sherri!

SQL Server login character length

Run sp_server_info procedure to find the limitation of the server with other information.

e.g. EXEC sp_server_info

Ref:
http://awesomesql.wordpress.com/2009/08/21/maximum-length-of-loginuser-name-and-password-in-sql-server-2005/
http://msdn.microsoft.com/en-us/library/ms176094.aspx
http://social.msdn.microsoft.com/Forums/en-US/sqldocumentation/thread/7b28762b-5e7e-40c7-b765-3eb6c2681ba5

Other limitations:
http://weblogs.sqlteam.com/mladenp/archive/2007/07/24/60267.aspx

SQL Server Error: 10061

Check if the SQL Server (MSSQLSERVER) Service is running. This can throw that error.
(Ref)

Relational Database Design

A database is a collection of data. Related data are grouped together in a tabular format so that it makes sense. For instance, salary data would better completement work related data than address data.
A relational database is acollection of tables of such data which can be linked with some common culumns, primary key and foreign key in database term. For instance:

EmpId	FName	LName
1	John	Q
2	Mary	James
3	Henry	Dew

EmpId	Type	Desc
1	Primary	This is primary description
1	Secondary	This is secondary description
1	Tertiary	This is tertiary description
3	Primary	This is primary description

Here, these two tables can be connected with the common field EmpId. Also notice that employee 2 does not has any entry in second table. This way information can be separated without loosing its integrity. There would have been duplication and few blanks if these two tables were combined. This is a form of a relational database.

In above, the first table could be called Employee and the second, Description. In database term it would be written as:

Employee (EmpId, FName, LName)

Description (EmpId, Type, Desc)

Employee and Description are called "entity" and those in parenthesis are called attributes.

As with every design, one needs to decide what needs to be put into the table as shown above. How detailed do you want to be will decide on how many tables and how to group them.


Once this has been decided, linking those tables with common field would produce ERD and the documentation something like (for first table above):

Table	Field	Data Type	Length	Description	Constraints
Employee	EmpId	Integer	Unique number that identifies an employee	Primary Key, Identity
	FName	varchar 50	first name of an employee
	LName	varchar 50	last name of an employee

Would be data dictionary where one defines data: name, type, length etc.

Installing SSMS 2008 in Windows 7

What?
I wanted to install SQL Server Management Studio 2008 in Windows 7 machine

Problem:
Whenever I tried to install it failed in preliminary run saying it could not restart the computer and continued even after restarting computer (not just one or two restarts)

How I solved:
Before running the initial run, I went to registry in:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Mananger
and cleared the value of "PendingFileRenameOperations"
and run the setup. mz! it worked.

Making Contact Us Forms in SharePoint with SharePoint Designer (SPD)

What?
I am creating a "Contact Us" form in SharePoint (SP) without coding (only little in SPD)

What do I need?
i. SPD
ii. Access in SP to add/delete/edit a list

How?
Concept:
1. Create a list in SP with required fields
2. Create a workflow which triggers on create
2.1 will email the content of the item (our Contact Us form content)
2.2 will delete the current item, it will just send email and not save the content
3. Tweak a little so that user fills as if they are filling out normal form

Procedre:
1. Set up a Custom List

2. Give the name "Contact Us"

3. Add column "Body", there already is "Title" and this can be used as 'subject'

4. Go to Settings->Advance Setting and disable attachment

5. Go to SPD and create workflow that triggers automatically on create.

5.1 Build email

5.2 Send email

5.3 Delete the current Item

7. Few tweaks

7.1 Go to Site Actions-> Site Settings

7.2 Click on "Navigation"

7.3 "Edit" navigation of "Contact Us" to /mysite/Lists/Contact Us/NewForm.aspx

This will take users directly to the form instead of list items.

7.4 Now change verbiage in NewForm.aspx in SPD so that it reads "Contact Us: Please fill out the form" instead of "Contact Us: New Item," the default one.

7.5 Also change verbiage in AllItems.aspx, mine says "Thank you for . . . " instead of the default one. This will act as a confirmation page and will appear when "Ok" button is presse in NewForm.aspx.

So,

This is an easy way to do "Contact Us" or similar kind of form. I'd appreciate if someone could add more to this one.

Happy F'day!