Search This Blog

Tuesday, August 7, 2012

DBCC SHRINKDATABASE (Transact-SQL)


Shrinks the size of the data and log files in the specified database.


DBCC SHRINKDATABASE
( database_name | database_id | 0
[ , target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
)
[ WITH NO_INFOMSGS ]




Arguments

database_name | database_id | 0

Is the name or ID of the database to be shrunk. If 0 is specified, the current database is used.

target_percent

Is the percentage of free space that you want left in the database file after the database has been shrunk.

NOTRUNCATE

Compacts the data in data files by moving allocated pages from the end of a file to unallocated pages in the front of the file. target_percent is optional.

The free space at the end of the file is not returned to the operating system, and the physical size of the file does not change. Therefore, when NOTRUNCATE is specified, the database appears not to shrink.

NOTRUNCATE is applicable only to data files. The log files are not affected.

TRUNCATEONLY

Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent. target_percent is ignored if specified with TRUNCATEONLY.

TRUNCATEONLY is applicable only to data files. The log files are not affected.

WITH NO_INFOMSGS
Suppresses all informational messages that have severity levels from 0 through 10.



Thursday, August 2, 2012

IIS Error: This configuration section cannot be used at this path.


IIS Error: This configuration section cannot be used at this path.
This happens when the section is locked at a parent level. Locking is either by default (overrideModeDefault=”Deny”), or set explicitly by a location tag with overrideMode=”Deny” or the legacy allowOverride=”false”.
Note: When you meet this error you must consider where IIS locates your error. For example, in the image above the error relates to handlers tag. I have this error twice when publish WCF Services, one with handlers and one with modules.

Solution:
- Navigate to C:\Windows\System32\inetsrv\config
- Right click on file applicationHost.config and open it with Notepad
- Search and find the tag which IIS has error with it and edit overrideModeDefault = “Allow”. Remember to save file before close it

Wednesday, August 1, 2012

Mouse Cursor C#

C# > Cursors

Cursors provides a collection of Cursor objects for use by a Windows Forms application.
 
Example: 1. Wait cursor
public Form1()
{
    InitializeComponent();             

    this.Cursor = Cursors.WaitCursor; // Wait cursor, an hourglass shape.
     // do something
    this.Cursor = Cursors.Default; //default cursor, usually an arrow cursor.
}

 












While (c#)

C# > Statements > while

While statement executes a statement or a block of statements until a specified expression evaluates to false.
A while loop can be terminated with
  • break
  • goto
  • return
  • throw
Pass control to the next iteration with the continue statement.
   
Example:

int i = 0;
while (i < 10)
{
     if (i == 1)
   {
         i = 2;
     continue; // goto to next loop
   }
   if (i == 2)
     return; // exit while
   if (i==5)
     break; // exit while
  
   i= i + 1;
}

 







MS Excel: Concatenate strings

Excel > Operators> Concatenate

To concatenate multiple strings into a single string in Excel, you can use the "&" operator to separate the string values.
string_1 & string_2 & string_n




Monday, July 30, 2012

SQL SELECT multi-columns INTO multi-variable

SQL Server > Scripts >  SELECT multi columns INTO multi variables

Example:


CREATE TABLE #tmp
(
    id       int PRIMARY KEY,
    name     varchar(20),
    age             int
)
GO


INSERT #tmp(id, name, age) VALUES (1, 'John',20)
INSERT #tmp(id, name, age) VALUES (2, 'Dan',40)
GO

declare @minage int,
        @maxage int
SELECT
   @minage = min(age),
   @maxage = max(age)
FROM
   #tmp
select
   @minage as minage, @maxage as maxage

drop table #tmp






Wednesday, July 25, 2012

While SQL Server

SQL ServerControl of Flow > While

While represents repeated execution of an SQL statement or statement block.
  • as long as the specified condition is true the statements are executed repeatedly 
  • the execution of statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords

Syntax:

WHILE Boolean_expression { statement_block | BREAK | CONTINUE }


Example

declare @t nvarchar(max)
declare @t1 nvarchar(max)
declare @p int


set @t = 'fld1:"val1";fld2:"val2","val3";fld3:"val4";'
set @p = CHARINDEX(';', @t);

while @p > 0
begin
     set @t1 = SUBSTRING (@t, 0, @p)
     set @t = SUBSTRING (@t, @p+1, len(@t) - @p)
     set @p = CHARINDEX(';', @t);
        select @t1
     if @p = 0
      break
    else
      continue
end

Result: