:::: MENU ::::

MSSQL – Execution Plan Caching and Recompiling

Hi,

Have you every experience that you have a stored procedure(SP) that have plenty of parameters that when 1 set of parameter is executed for the very first time the said SPwas created, its fast. Then after you have a new set of parameter passed to the same SP you’ll end up a long execution process?

Then that is because of a so called Execution Plan Caching that MSSQL is doing on your query. In an overview MSSQL check if there is an existing execution plan for the SP. If there is none, it will create an optimized execution plan, compile, and then cache it on memory. So when the same SP is executed with the same number of parameter it will be reused.

So in cases that you have the same number of parameter every time you use the said stored procedure and you know that you have a logic that will change the query result then this optimization may not be on our side.

When does this caching is cleared? It is only cleared when either there are no memory allocation for MSSQL or you force MSSQL to recompile the SP.

How to Recompile your specific SP? Just add OPTION (RECOMPILE) at the end of your query, before the END statement, and you are ready to go. In some cases you may want to force all cache to be removed, you need to use another statement to do that and that is DBCC FREEPROCCACHE.

That’s it. Hope it helped you as it helped me on my project.

God Bless!
Thanks,
Thomie


MSSQL – Drop all SP and Table on a Database

Execute this with Precaution

USE YourDataBaseName
declare @procName varchar(500)
declare cur cursor
for select [name] from sys.objects where type = ‘p’
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
exec(‘drop procedure [‘ + @procName+’]’)
fetch next from cur into @procName
end
close cur
deallocate cur
EXEC sp_MSforeachtable @command1 = “DROP TABLE [?]”

 

 


MSSQL – Count Number of Records on Each Table

Here is the script to count all the number of records on each table on a MSSQL Server:

 

SELECT sc.name +’.’+ ta.name TableName
,SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC

Hope it helps.

God Bless!
Thanks,
Thomie


TCP and C# Connecting via Lan

Have you ever thought that you want to connect to create an application that can talk via LAN? Then TCP is one of the mode that you can choose one. Here are the codes from MSDN that can help you:

TCPListen – This is used when you want to create a server like application.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Sockets;
using System.Text;
using System.Threading.Tasks;

namespace TCPListen
{
class Program
{
static void Main(string[] args)
{
TcpListener server = null;
try
{
// Set the TcpListener on port 13000.
Int32 port = 13000;
IPAddress localAddr = IPAddress.Parse("127.0.0.1");

// TcpListener server = new TcpListener(port);
server = new TcpListener(localAddr, port);

// Start listening for client requests.
server.Start();

// Buffer for reading data
Byte[] bytes = new Byte[256];
String data = null;

// Enter the listening loop.
while (true)
{
Console.Write("Waiting for a connection... ");

// Perform a blocking call to accept requests.
// You could also user server.AcceptSocket() here.
TcpClient client = server.AcceptTcpClient();
Console.WriteLine("Connected!");

data = null;

// Get a stream object for reading and writing
NetworkStream stream = client.GetStream();

int i;

// Loop to receive all the data sent by the client.
while ((i = stream.Read(bytes, 0, bytes.Length)) != 0)
{
// Translate data bytes to a ASCII string.
data = System.Text.Encoding.ASCII.GetString(bytes, 0, i);
Console.WriteLine("Received: {0}", data);

// Process the data sent by the client.
data = data.ToUpper();

byte[] msg = System.Text.Encoding.ASCII.GetBytes(data);

// Send back a response.
stream.Write(msg, 0, msg.Length);
Console.WriteLine("Sent: {0}", data);
}

// Shutdown and end connection
client.Close();
}
}
catch (SocketException e)
{
Console.WriteLine("SocketException: {0}", e);
}
finally
{
// Stop listening for new clients.
server.Stop();
}

Console.WriteLine("\nHit enter to continue...");
Console.Read();
}
}
}

TCPClient – As the name suggest it will be used to create the client that will connect to our server.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Sockets;
using System.Text;
using System.Threading.Tasks;

namespace TCPListen
{
class Program
{
static void Main(string[] args)
{
TcpListener server = null;
try
{
// Set the TcpListener on port 13000.
Int32 port = 13000;
IPAddress localAddr = IPAddress.Parse("127.0.0.1");

// TcpListener server = new TcpListener(port);
server = new TcpListener(localAddr, port);

// Start listening for client requests.
server.Start();

// Buffer for reading data
Byte[] bytes = new Byte[256];
String data = null;

// Enter the listening loop.
while (true)
{
Console.Write("Waiting for a connection... ");

// Perform a blocking call to accept requests.
// You could also user server.AcceptSocket() here.
TcpClient client = server.AcceptTcpClient();
Console.WriteLine("Connected!");

data = null;

// Get a stream object for reading and writing
NetworkStream stream = client.GetStream();

int i;

// Loop to receive all the data sent by the client.
while ((i = stream.Read(bytes, 0, bytes.Length)) != 0)
{
// Translate data bytes to a ASCII string.
data = System.Text.Encoding.ASCII.GetString(bytes, 0, i);
Console.WriteLine("Received: {0}", data);

// Process the data sent by the client.
data = data.ToUpper();

byte[] msg = System.Text.Encoding.ASCII.GetBytes(data);

// Send back a response.
stream.Write(msg, 0, msg.Length);
Console.WriteLine("Sent: {0}", data);
}

// Shutdown and end connection
client.Close();
}
}
catch (SocketException e)
{
Console.WriteLine("SocketException: {0}", e);
}
finally
{
// Stop listening for new clients.
server.Stop();
}

Console.WriteLine("\nHit enter to continue...");
Console.Read();
}
}
}

God Bless!

Thanks,
Thomie



Get your Location Height

Have you ever wonder how can you get your location height above sea level? We’ll I have been one of those who wonders. Having the knowledge on how high your location is have advantages. One of the most common advantages is the probability of having a flood on your area. So I’ve search the net and found this website. http://www.daftlogic.com/sandbox-google-maps-find-altitude.htm

You just need to find your location using the embeded google map and click. And you will get the height on feet. Thats easy.

Try it now!

 


Get To Be Executed MSSQL Script like in PHPMyAdmin

If you want to know what is the query corresponding to the change you’ve done to a table (or other object) in designer mode, use Generate Change Script (Table Designer → Generate Change Script… or the very first icon, with a diskette and a script, in Table Designer toolbar) command.


Auto SMS Sender, finally found the best for me.

I have been searching for a perfect Auto SMS Sending application that will fit my very need.

Basic Needs

  • Send SMS Regularly
  • Sent Notification
  • Wide Selection of Intervals
  • Auto Response

Added Features

  • Calendar Notification
  • Backup of Created Scheduled SMS and even SMS in localdisk and Dropbox
  • Auto Forward

 

Therefore, if your looking for such app I will be recommending Auto SMS Sender by DRC InfoTech. Its free but with adds. The adds may annoy some of you but thinking of the features that it offers, I’ll say that its worthit.


What would they call it if your using ASP?

In PHP they are calling coders differently:

Baker when they are using CakePHP. An

How about in ASP .Net? What would they call people who are using WebForms, MVC, or Other MS Products? Would they call MVCaretakers  if your using MVC because your taking carefully all the coding so that it is coded separately so that it can be easily tested via Unit Test?

How about you? What would they call it?



Binding SSL Cerificate to a Machine

Prequisites

  • SSL Certificate (For Testing this can be self-signed)
  • Administrator Rights
  • Command Prompt
  • IIS 7 or above

Instructions

  1. Open the Certificate details and find its ThumbPrint in IIS
  2. Identify the port and IP to which the certificate to be binded
  3. Open the Command Prompt with Administrator Rights
  4. Type in the following
    netsh http add sslcert ipport=<IPtoBind>:<PortToBind> appid={12345678-db90-4b66-8b01-88f7af2e36bf} certhash=<CertificateThumbPrint>
  5. Done
  6. To remove that binding just type the following
    netsh http delete sslcert ipport=<IPtoBinded>:<PortToBinded>

[VMWARE] Exception 0xc0000006 (disk error while paging) has occurred

Hi,

This error, Exception 0xc0000006 (disk error while paging) has occurred, occurred when some improper turning off of your VMWare. In order to fix this you will need to delete the Suspended Session you need to delete or rename the *.vmss file – this is the virtual equivalent of a power-outage in a real machine.

via Source

Thanks,

Thomie


[MSSQL] Truncate all Table

Truncating all tables is not easy by default on MSSQL so I have search the net and found one. Just open up a new query and use the following codes:

Use databaseName
GO

EXEC sp_MSForEachTable ‘TRUNCATE TABLE ?’
GO

It will truncate all the tables under the databaseName DB.

God Bless!

Thanks,
Thomie


I Can Only Image – Mercy Me

I Can Only Image – Mercy Me

c/o http://www.elyricsworld.com/i_can_only_imagine_lyrics_mercy_me.html

What it will be like 
When I walk 
By your side 

I can only imagine 
What my eyes will see 
When your face 

Is before me 
I can only imagine 

[Chorus:]
Surrounded by Your glory, what will my heart feel 
Will I dance for you Jesus or in awe of you be still 
Will I stand in your presence or to my knees will I fall 
Will I sing hallelujah, will I be able to speak at all 

I can only imagine 
I can only imagine 
When that day comes 

And I find myself 
Standing in the Son 

I can only imagine 
When all I will do 
Is forever 
Forever worship You 

I can only imagine

[Chorus]

I can only imagine [x2]

I can only imagine
When all I will do 
Is forever, forever worship you



Who am I – Casting Crowns

Who Am I

c/o https://castingcrowns.com/node/643

Who am I, that the Lord of all the earth
Would care to know my name
Would care to feel my hurt?
Who am I, that the bright and morning star
Would choose to light the way
For my ever wondering heart?

Not because of who I am
But because of what You’ve done
Not because of what I’ve done
But because of who You are

I am a flower quickly fading
Here today and gone tomorrow
A wave tossed in the ocean
A vapor in the wind
Still You hear me when I’m calling
Lord You catch me when I’m falling
And You told me who I am
I am Yours, I am Yours

Who am I, that the eyes that see our sin
Would look on me with love
And watch me rise again?
Who am I, that the voice that calmed the sea
Would call out through the rain
And calm the storm in me?

Not because of who I am

But because what of You’ve done
Not because of what I’ve done
But because of who You are

I am a flower quickly fading
Here today and gone tomorrow
A wave tossed in the ocean
A vapor in the wind
Still You hear me when I’m calling
Lord You catch me when I’m falling
And You told me who I am
I am Yours

Not because of who I am
But because of what You’ve done
Not because of what I’ve done
But because of who You are

I am a flower quickly fading
Here today and gone tomorrow
A wave tossed in the ocean
A vapor in the wind
Still You hear me when I’m calling
Lord You catch me when I’m falling
You told me who I am
I am Yours, I am Yours, I am Yours

Whom shall I fear?
Whom shall I fear?
‘Cause I am Yours, I am Yours


jQuery: FullCalendar

Name: FullCalendar

Description: Most users find developing their own calendar UI not that quite easy but with jQuery FullCalendar you make it easy and fast!

 

Some of Specifications:

Demo: http://arshaw.com/fullcalendar/

Download: http://arshaw.com/fullcalendar/download/