T-SQL system stored procedures 1

EXEC sp_spaceused

EXEC sp_spaceused dbo.table_name

database_name                                                                                                                    database_size      unallocated space

-------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------

master                                                                                                                           5.25 MB            1.52 MB

 

reserved           data               index_size         unused

------------------ ------------------ ------------------ ------------------

2536 KB            1160 KB            1040 KB            336 KB

Shrink log file

 

USE [master]

GO

ALTER DATABASE [REPORT] SET RECOVERY SIMPLE WITH NO_WAIT

DBCC SHRINKFILE(ANGEL_log, 1)

ALTER DATABASE [REPORT] SET RECOVERY FULL WITH NO_WAIT

GO

Change Server Name –SQL Server Instance adını değiştirme

 

SELECT @@SERVERNAME AS 'Server Name'

 

sp_dropserver 'OLD_NAME'

GO

sp_addserver 'NEW_NAME', local

GO

Restart sql server instance

Adding Custom error message

Exec sp_addmessage

     @msgnum=50002,

     @severity=12,

     @msgtext='error while %s description ;',

     @with_log='False',

       @replace='replace'

       

 

select * from sys.messages

send mail to operator

EXECUTE

      msdb.dbo.sp_notify_operator

      @name=N'operator name',@subject=N'subject of mail',

      @body=N'content of mail message'

Requests of sql server **

            SELECT

            r.session_id,

            r.blocking_session_id,

            s.program_name,

            s.host_name, 

            t.text

      FROM

            sys.dm_exec_requests r

            INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id

            CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t

      WHERE

            s.is_user_process = 1

 

 

report server subscription- agent list

    SELECT    

Schedule.ScheduleID as SubscriptionID,

Subscriptions.*,

Schedule.ScheduleID AS SQLAgent_Job_Name,

                  Subscriptions.Description AS sub_desc, Subscriptions.DeliveryExtension AS sub_delExt,

             [Catalog].Name AS ReportName, [Catalog].Path AS ReportPath

FROM         ReportServer.dbo.ReportSchedule

INNER JOIN ReportServer.dbo.Schedule ON ReportServer.dbo.ReportSchedule.ScheduleID = Schedule.ScheduleID

INNER JOIN ReportServer.dbo.Subscriptions ON ReportServer.dbo.ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID

INNER JOIN ReportServer.dbo.[Catalog] ON ReportServer.dbo.ReportSchedule.ReportID = [Catalog].ItemID AND Subscriptions.Report_OID = [Catalog].ItemID

                WHERE          

         Subscriptions.DeliveryExtension = 'Report Server Email'

 

 

compare two tables dynamic- tablo sütunları karşılaştırma

DECLARE @dbname1 nvarchar(48) =N'ERP'

DECLARE @dbname2 nvarchar(48) =N'AERP'

 

DECLARE @TableName nvarchar(48) =N'FATURA'

 

 

SELECT * FROM (

      SELECT

      @dbname1 as DBNAME,

      clmns.name AS [Name],

      usrt.name AS [DataType],

      ISNULL(baset.name, N'') AS [SystemType],

      CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1 THEN

      clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length],

      CAST(clmns.precision AS int) AS [NumericPrecision]

      FROM

      ERP.sys.tables AS tbl

      INNER JOIN ERP.sys.all_columns AS clmns ON clmns.object_id=tbl.object_id

      LEFT OUTER JOIN ERP.sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id

      LEFT OUTER JOIN ERP.sys.types AS baset ON baset.user_type_id = clmns.system_type_id and

      baset.user_type_id = baset.system_type_id

      WHERE

      (tbl.name=@TableName and SCHEMA_NAME(tbl.schema_id)=N'dbo') )as ERP

LEFT JOIN (

      SELECT

      @dbname2 as DBNAME,

      clmns.name AS [Name],

      usrt.name AS [DataType],

      ISNULL(baset.name, N'') AS [SystemType],

      CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1 THEN

      clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length],

      CAST(clmns.precision AS int) AS [NumericPrecision]

      FROM

      AERP.sys.tables AS tbl

      INNER JOIN AERP.sys.all_columns AS clmns ON clmns.object_id=tbl.object_id

      LEFT OUTER JOIN AERP.sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id

      LEFT OUTER JOIN AERP.sys.types AS baset ON baset.user_type_id = clmns.system_type_id and

      baset.user_type_id = baset.system_type_id

      WHERE

      (tbl.name=@TableName and SCHEMA_NAME(tbl.schema_id)=N'dbo') ) AERP

      ON ERP.Name = AERP.Name

            WHERE

                  ERP.[Length] <> AERP.[Length]

SQL SERVER DBCC

exec sp_who2
kill 54

DBCC PAGE (db, 1, 26839, 1)
DBCC CHECKDB(db, REPAIR_ALLOW_DATA_LOSS)
alter database db set MULTI_USER

;--------------

The error is pretty benign - it's not an actual corruption. It's saying that the PFS (Page Free Space) entry for that page has the wrong amount of free space noted - 100% instead of 0%. All this means is that the page may be picked up be a free-space scanner looking to insert data into a text page and it will find the page is actually full and move on to the next page.

All repair will do is set the correct free space setting, but you'll need to take the database into single-user mode (essentially offline) to do it.

These errors occured reasonably frequently on SQL 2000 as the algorithm to keep track of free space (only tracked for heap and text pages) had some holes in it. They should be fixed in 2005. Is this database recently upgraded from 2000, or is this the first time CHECKDB has been run since it was upgraded?

To use DBCC PAGE, you need to specify the database name too:

DBCC PAGE ('DATABASENAME', 1, 26839, 1)

should do it for you.

Thanks

-------------------------------------------------------

 

alter database db set SINGLE_USER;

DBCC CHECKDB('db', REPAIR_REBUILD);

alter database db set MULTI_USER;

http://msdn.microsoft.com/en-us/library/aa226363%28v=sql.80%29.aspx

http://msdn.microsoft.com/en-us/library/ms188236.aspx

http://msdn.microsoft.com/en-us/library/ms345598.aspx

http://www.cryer.co.uk/brian/sqlserver/howto_start_single_user_mode.htm

http://publib.boulder.ibm.com/tividd/td/BSM/SC32-9084-00/en_US/HTML/bsmd242.htm

http://www.sql-server-pro.com/dbcc-checkdb.html

http://msdn.microsoft.com/en-us/library/ms176064.aspx

http://msdn.microsoft.com/en-us/library/aa258278%28v=sql.80%29.aspx

Aero Style Forms

        #region ----- Aero Form

 

        [System.Runtime.InteropServices.StructLayout(System.Runtime.InteropServices.LayoutKind.Sequential)]

        public struct MARGINS

        {

            public int Left;

            public int Right;

            public int Top;

            public int Bottom;

        }

 

        private MARGINS margins;

        [System.Runtime.InteropServices.DllImport("dwmapi.dll", PreserveSig = false)]

        public static extern void DwmExtendFrameIntoClientArea

                        (IntPtr hwnd, ref MARGINS margins);

 

        [System.Runtime.InteropServices.DllImport("dwmapi.dll", PreserveSig = false)]

        public static extern bool DwmIsCompositionEnabled();

 

        protected override void OnLoad(EventArgs e)

        {

            base.OnLoad(e);

            if (DwmIsCompositionEnabled())

            {

                // Paint the glass effect.

                margins = new MARGINS();

                margins.Top = 50;

                DwmExtendFrameIntoClientArea(this.Handle, ref margins);

            }

        }

 

        protected override void OnPaintBackground(PaintEventArgs e)

        {

            base.OnPaint(e);

            if (DwmIsCompositionEnabled())

            {

                // paint background black to enable include glass regions

 

                e.Graphics.Clear(Color.Black);

                // revert the non-glass rectangle back to it's original colour

 

                Rectangle clientArea = new Rectangle(

                        margins.Left,

                        margins.Top,

                        this.ClientRectangle.Width - margins.Left - margins.Right,

                        this.ClientRectangle.Height - margins.Top - margins.Bottom

                    );

                Brush b = new SolidBrush(this.BackColor);

                e.Graphics.FillRectangle(b, clientArea);

            }

        }

 

        #endregion

 

Service Broker kullanımı

SQL Server 2005 veya 2008 üzerinde service broker nesnesinin kullanımı.

ornekdb isimli bir database oluşturulması

ornekdb service broker erişimi ve service broker’ın aktifleştirilmesi

Select is_broker_enabled From sys.databases Where database_id=db_id()

ALTER DATABASE ornekdb SET ENABLE_BROKER

tanımların yapılması



CREATE MESSAGE TYPE mesaj VALIDATION = WELL_FORMED_XML ;

CREATE CONTRACT kontrat ( mesaj SENT BY INITIATOR);

CREATE QUEUE [dbo].[gondermeKuyrugu];
CREATE QUEUE [dbo].[almaKuyrugu];

CREATE SERVICE gondermeServisi ON QUEUE [dbo].[gondermeKuyrugu];
CREATE SERVICE almaServisi ON QUEUE [dbo].[almaKuyrugu] (kontrat);
mesaj gönderme

BEGIN TRANSACTION ; 

    -- mesaj oluşturulması

    DECLARE @mesaj XML ;
    SET @mesaj = N'<mesaj>Merhaba, Dünya!</mesaj>' ;

    -- baslatılan karsilasmanın referansının
    -- tutulması

    DECLARE @karsilasmaReferansi UNIQUEIDENTIFIER ;

    -- Karşılaşma.

    BEGIN DIALOG CONVERSATION @karsilasmaReferansi
        FROM SERVICE gondermeServisi
        TO SERVICE 'almaServisi'
        ON CONTRACT kontrat 
        WITH ENCRYPTION = OFF;

    -- Send the message on the dialog.

    SEND ON CONVERSATION @karsilasmaReferansi
      MESSAGE TYPE mesaj
      (@mesaj) ;

-- işlem tamalanıyor. Service Broker
-- mesaj hedefe ulaştırılır

COMMIT TRANSACTION ;
GO
mesaj alma

-- Grup bazında işlem
WHILE (1 = 1)
BEGIN

DECLARE @karsilasmaReferansi UNIQUEIDENTIFIER,
        @karsilasmaGrup  UNIQUEIDENTIFIER,
        @mesajIcerigi XML,
        @mesajTipAdi NVARCHAR(128);

BEGIN TRANSACTION ;

-- grubun tutulması

WAITFOR(
   GET CONVERSATION GROUP @karsilasmaGrup FROM [dbo].[almaKuyrugu]),
   TIMEOUT 500 ;

-- eger karşılaşma grubu kalmadıysa işlemin
-- dış döngünün dışına çıkarılması

IF @karsilasmaGrup IS NULL
BEGIN
    ROLLBACK TRANSACTION ;
    BREAK ;
END ;

    -- grup içerisindeki mesajların işleme alınması. 

    WHILE 1 = 1
    BEGIN

        -- grup için mesajın alınması 'where' yazımı ile
        -- aynı gruba ait mesajın işleme alınması

        RECEIVE
           TOP(1)
           @karsilasmaReferansi = conversation_handle,
           @mesajTipAdi = message_type_name,
           @mesajIcerigi =
           CASE
              WHEN validation = 'X' THEN CAST(message_body AS XML)
              ELSE CAST(N'<none/>' AS XML)
          END
        FROM [dbo].[almaKuyrugu]
        WHERE conversation_group_id = @karsilasmaGrup ;

        -- eger mesaj yoksa veya hata olustuysa işlemi durdur,

        IF @@ROWCOUNT = 0 OR @@ERROR <> 0 BREAK;

        -- gelen mesajın gösterilmesi / seçilmesi

        SELECT 'Conversation Group Id' = @karsilasmaGrup,
               'Conversation Handle' = @karsilasmaReferansi,
               'Message Type Name' = @mesajTipAdi,
               'Message Body' = @mesajIcerigi ;

        -- karşılaşmanın sonlandırılması

        END CONVERSATION @karsilasmaReferansi ;

    END;

   -- işlemlerin onaylanması

   COMMIT TRANSACTION ;

END ; -- bütün gruplar
GO

MVC Page URL Relative

<link href="<%= 
        Page.ResolveUrl("~/Content/core/css/smoothness/jquery-ui-1.8.1.custom.css") %>" 
            rel="stylesheet" 
            type="text/css" />  
<link href='<%= 
        Url.Content("~/Plugins/Views/Webilan/Content/Main.css") %>' 
            rel="stylesheet" 
            type="text/css">
<link rel="stylesheet" type="text/css" media="screen" 
        href='<%= VirtualPathUtility.ToAbsolute(
                    string.Format("~/Content/{0}/css/Site.css", 
                    PostSystem.ThemeName )) %>' />

LINQ to SQL query with dynamic OrderBy

public static class DynamicOrderBy
{

    public static IQueryable<TEntity> OrderBy<TEntity>(this IQueryable<TEntity> source, string orderByProperty,
                  bool desc) where TEntity : class
    {

        string command = desc ? "OrderByDescending" : "OrderBy";

        var type = typeof(TEntity);
        var property = type.GetProperty(orderByProperty);
        var parameter = System.Linq.Expressions.Expression.Parameter(type, "p");
        var propertyAccess = System.Linq.Expressions.Expression.MakeMemberAccess(parameter, property);
        var orderByExpression = System.Linq.Expressions.Expression.Lambda(propertyAccess, parameter);

        var resultExpression = 
            System.Linq.Expressions.Expression.Call(typeof(Queryable), command, new Type[] { type, property.PropertyType },
                               source.Expression, System.Linq.Expressions.Expression.Quote(orderByExpression));

        return source.Provider.CreateQuery<TEntity>(resultExpression);

    }

 

reference :

http://blogs.msdn.com/b/swiss_dpe_team/archive/2008/06/05/composable-linq-to-sql-query-with-dynamic-orderby.aspx

http://stackoverflow.com/questions/307512/how-do-i-apply-orderby-on-an-iqueryable-using-a-string-column-name-within-a-gener

C#: Equivalent of JavaScript escape function

Uri.EscapeDataString("KC's trick /Hello") gives:
"KC's%20trick%20%2FHello"
Uri.EscapeUriString("KC's trick /Hello") gives:
"KC's%20trick%20/Hello"
System.Web.HttpUtility.UrlEncode("KC's trick /Hello") gives:
"KC's+trick+%2fHello"
System.Web.HttpUtility.UrlPathEncode("KC's trick /Hello") gives:
"KC's%20trick%20/Hello"

 

reference :http://kseesharp.blogspot.com/2008/01/c-equivalent-of-javascript-escape.html