Simplest way to calculate first and last day of month and week for specific date in MS Sql Server

I have come up with following few lines that illustrate a way to find first and last day(date) for specified date.

And then given same thing for week.

Declare @SpecifiedDate DateTime;
Set @SpecifiedDate=GETDATE();
Declare @XStart int;
Set @XStart=0;

SELECT @XStart=DATEDIFF(mm,0,@SpecifiedDate)
SELECT DATEADD(mm,@XStart,0) ‘First Day of Specific Month’
SELECT DATEADD(d,-1,DATEADD(mm,@XStart+1,0)) ‘Last Day of Specific Month’

SELECT DATEADD(wk,DATEDIFF(wk,0,@SpecifiedDate ),0) ‘First Day of Week’
SELECT DATEADD(wk,DATEDIFF(wk,0,@SpecifiedDate ),6) ‘Last Day of Week’

Advertisements

Display SQL SERVER Message,Using InfoMessage Event from SQL SERVER, to troubleshoot stored procedure

Big stored procedures which are building QUERY dynamically according to parameters are used sometime for large web applications.

Bad luck starts when such procedure gives error, and as query itself build at run time, user may frustrates catching the right error.

Also,Debugging becomes challenging with it.

One way is to use PRINT statement at end of procedure, so when stored procedure executed it displays DYNAMIC QUERY.

In SQL Server Management studio we can easily view the printed query.

But what if you want it in ASP.NET environemt?

SqlConneciton class has
SqlConnection.InfoMessage event, which occurs, when SQL Servers returns a warning or informational message.

First create ‘SqlConnection’ object.

string connectionString = “Your Connection String…”;
SqlConnection myConn = new SqlConnection(connectionString);
Then, Relate method to event.

myConn.InfoMessage += new SqlInfoMessageEventHandler(MyConnection _InfoMessage);

 

Your method may look like following:
System.Text.StringBuilder sbLog = new System.Text.StringBuilder();

void MyConnection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
sbLog.AppendLine(e.Message);
}

Execute following code and test output with ‘sbLog.ToString()’.

SqlCommand cmd = new SqlCommand();

if (cn.State != ConnectionState.Open)
cn.Open();

cmd.Connection = myConn;
cmd.CommandText = “SP_That_Prints_Query”;

cmd.CommandType = CommandType.StoredProcedure;

cmd.ExecuteNonQuery();

 

‘sbLog.ToString()’ will give you dynamic query generated in your stored procedure.

 

retrive specific number of records from table in sql server database

Using ROW_NUMBER() in sql, you can have unique record numbers in your query result.

it returns the sequential number of a row within a your query result set.

Syntax of it:

ROW_NUMBER () OVER ( [ ] )

here, order by clause, determines the order in which the ROW_NUMBER value is assigned to the rows

Following example demonstrates the use of it:


select * from
(
select p.keyid, p.name, p.addeddate, ROW_NUMBER() OVER (order by p.name) as [ROWID]
from products p
) as [mytmptable]
where ROWID between 2 and 5
ORDER BY ROWID ASC

Above technique become useful especially in case of  you need to retrieving limited number of records from your query result(like while implementing paging functionality).

SQL – compare number of records in each tables of different database at remote server

Some time during development you need to compare tables(same tables) of different different database , and things becomes tough if both database are on remote server.

At such time you need to query to separate database of remote server.

Using ‘Linked server’ you can query remote database.

To establish linked server, first you have to create two linked server in order to access them, you can add it using following command:

exec sp_addlinkedserver @server=[server1]
exec sp_addlinkedserver @server=[server2]

(Assuming you are connected with same database locally,  and you have to also give authentication information in case of any there)

Then by running ‘cursor’ on all tables you can achieve result.

declare tmpCUR cursor for

select name from sysobjects where xtype=’U’

/*   to scan each tables in database   */

open tmpCUR

declare @tmpName nvarchar(400)

fetch next from tmpCUR into @tmpName While @@fetch_Status = 0
BEGIN

declare @query nvarchar(1000)

set @query=’select ”’ + @tmpName+”’,count(*) from [server1].[database1].dbo.’ + @tmpName
exec sp_executesql @query

set @query=’select ”’ + @tmpName+”’,count(*) from [server2].[database2].dbo.’ +@tmpName
exec sp_executesql @query

fetch next from tmpCUR
into @tmpName

END

CLOSE tmpCUR
DEALLOCATE tmpCUR