Kashif Nizam Qureshi


Leave a comment

Log Errors to Event Viewer C#


public static void LogError(string errorText)
        {
            string userName = string.Empty;
            if (HttpContext.Current != null)
            {
                userName = HttpContext.Current.User.Identity.Name;
                userName = userName.Remove(0, userName.IndexOf("\\") + 1);
            }

            StackTrace trace = new StackTrace();


            Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                if (!EventLog.SourceExists("DHA"))
                {
                    EventLog.CreateEventSource("DHA", "WebPart");

                }

                EventLog.WriteEntry("DHA", string.Format("User: {0} \nFile: {1} \nMethod: {2} \nLine #: {3} \nError: {4}", userName, trace.GetFrame(2).GetMethod().DeclaringType.Name, trace.GetFrame(2).GetMethod().Name, trace.GetFrame(2).GetFileLineNumber(), errorText), EventLogEntryType.Error);
            });

        }


Leave a comment

Resetting SharePoint List ID

Use the list name and sub site name to reset SharePoint list ID. Sub site is necessary because same list name could exist in another sub site.


Declare @ListID uniqueidentifier
Declare @ListName nvarchar(500)
Declare @SubSiteName nvarchar(500)


Set @ListName='AppLog'
Set @SubSiteName='Sample Order'

Select @ListID =
--Select
 tp_ID From [dbo].[AllLists]  
Where tp_Title = @ListName --'AppLog' --'ChemicalCustomerClaim'
and tp_WebId = (Select Id from dbo.AllWebs where Title= @SubSiteName )

SELECT * FROM [dbo].[AllListsAux] 
where ListID= @ListID --'A466A9AE-C6A8-4A1A-8DBB-CA3D2122268B'


--UPDATE dbo.AllListsAux set NextAvailableId=1 
--where ListID='A466A9AE-C6A8-4A1A-8DBB-CA3D2122268B'



Leave a comment

To search all columns of all tables for a given search string

To search all columns of all tables for a given search string. Following data types of column will be searched through, you can add more, if needed.

‘char’, ‘varchar’, ‘nchar’, ‘nvarchar’, ‘int’, ‘decimal’,’uniqueidentifier’

Courtesy: http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx


DECLARE @SearchStr nvarchar(100)
--Replace the following string, that need to be searched.
SET @SearchStr = '7DB28A10-580C-441D-B2C9-FFC633868E72' 
 
 
    -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
    -- Purpose: To search all columns of all tables for a given search string
    -- Written by: Narayana Vyas Kondreddi
    -- Site: http://vyaskn.tripod.com
    -- Updated and tested by Tim Gaunt
    -- http://www.thesitedoctor.co.uk
    -- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx
    -- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010
    -- Date modified: 03rd March 2011 19:00 GMT
    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
 
    SET NOCOUNT ON
 
    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
 
    WHILE @TableName IS NOT NULL
     
    BEGIN
        SET @ColumnName = ''
        SET @TableName =
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )
 
        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
             
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal','uniqueidentifier')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
     
            IF @ColumnName IS NOT NULL
             
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END   
    END
 
    SELECT ColumnName, ColumnValue FROM #Results
 
DROP TABLE #Results


Leave a comment

Getting Last Created or Modified SQL Objects

--=================== Tables ============================
-- Created
select name,type,type_desc, create_date From sys.objects where type ='U' order by create_date desc

-- Modified
select name,type,type_desc, modify_date From sys.objects where type ='U' order by modify_date desc


--=================== Procedures ============================
-- Created
select name,type,type_desc, create_date From sys.procedures order by create_date desc

-- Modified
select name,type,type_desc, modify_date From sys.procedures order by modify_date desc


--=================== Functions ============================
-- Created
select name,type,type_desc, create_date From sys.objects where --name like '%getde%'

type ='FN' or type ='TF' or type ='IF'

order by create_date desc

-- Modified
select name,type,type_desc, modify_date From sys.objects where

--name like '%getde%'

type ='FN' or type ='TF' or type ='IF'

order by modify_date desc


Leave a comment

Restore Database Backup Using PL/SQL

Use the following to restore database backup. This is useful when you’re restoring SharePoint content db on the same server.


RESTORE FILELISTONLY FROM DISK='e:\mssql\backup\creditline.bak'

>LogicalName
>--------------
>CreditLine
>CreditLine_log

RESTORE DATABASE MyTempCopy FROM DISK='e:\mssql\backup\creditline.bak'
WITH 
   MOVE 'CreditLine' TO 'e:\mssql\MyTempCopy.mdf',
   MOVE 'CreditLine_log' TO 'e:\mssql\MyTempCopy_log.ldf'


Leave a comment

Search String Through SQL Objects Like Procedures, Functions etc.

Search String Through SQL Objects Like Procedures, Functions etc.



DECLARE @DBCount INT
DECLARE @CNTR INT
DECLARE @SQL VARCHAR(8000)
DECLARE @dbname NVARCHAR(255)
DECLARE @SearchText NVARCHAR(255)

SET @SearchText = 'String to be Searched'

IF ISNULL(@SearchText,'') <> ''
BEGIN

DECLARE @DBs TABLE ([ID] INT IDENTITY(1,1), [NAME] VARCHAR(255))
INSERT INTO @DBs--drop table @DBs
SELECT [NAME] FROM Master.dbo.sysdatabases
WHERE [NAME] NOT IN ('MASTER', 'TEMPDB', 'MSDB', 'MODEL')
ORDER BY [NAME]

IF EXISTS(SELECT [NAME] FROM TEMPDB.DBO.SYSOBJECTS WHERE NAME LIKE '%#Results%')
BEGIN
DROP TABLE #Results
END

CREATE TABLE #Results([SERVERNAME] VARCHAR(255), [DBName] VARCHAR(255), [NAME] VARCHAR(255), XTYPE VARCHAR(255))

SELECT @DBCount = (SELECT MAX([ID]) FROM @DBs)
SET @CNTR = 1
WHILE @CNTR <= @DBCount
BEGIN
SELECT @dbname = (SELECT [NAME] FROM @DBs WHERE [ID] = @CNTR)
SELECT @SQL =
'USE [' + @dbname + '] insert into #Results
SELECT DISTINCT
--substring(sc.Text, charindex(''' + @SearchText + ''', sc.text, 1) - 20, 255) as ''text'',
@@SERVERNAME, ''' + @dbname + ''' AS [DBName], so.[name], so.xtype
FROM
sysobjects so WITH(NOLOCK)
inner join syscomments sc on so.id = sc.id
WHERE
sc.text like ''%'+ @SearchText + '%'''
--PRINT @sql
EXEC (@SQL)
SET @CNTR = @CNTR + 1
END

SELECT *
FROM #Results
ORDER BY DBName, XType, [Name]

END