*.dannyg

I had inadventenly locked myself out of Sharepoint by removing myself as site administrator. But, because I have access to the SQL database I was able to restore my permissions. First run the following query to find out your tp_ID. (You will have to run this on the correct Content database).

select tp_id, tp_siteadmin, tp_login from userinfo

Find your user name in the query results (you can use a WHERE LIKE clause to simplify this step). Then run the following query to give yourself permissions.

UPDATE userinfo SET tp_siteadmin = 1 WHERE tp_Id = ?

That should give you everything you need.  Log into Sharepoint and you should be a Site Collection Administration

I ran across a pretty interesting requirement.  Be able to sort something off a CSV list of items.  In this case we have a comma separated string we are using for an IN clause

declare @employeeids varchar(50)

set @employeeids = ‘4213,5321,4124’

declare @orderbyClause nvarchar(MAX)
select @orderbyClause = COALESCE(@orderbyClause, '') + ' when employee_id = ' + convert(varchar, number) + ' then ' + convert(varchar, roworder) from
dbo.iter$ordered_intlist_to_tbl(@employeeids)
select ‘ORDER BY CASE ' + @orderbyClause + ' END'   

We are also using a custom table valued function which turns a CSV into a table and maintains the order using a roworder.

/****** Object:  UserDefinedFunction [dbo].[iter$simple_intlist_to_tbl]    Script Date: 12/08/2009 12:16:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[iter$ordered_intlist_to_tbl] (@list nvarchar(MAX))
   RETURNS @tbl TABLE (number int NOT NULL, roworder int NOT NULL) AS
BEGIN
   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int,
           @rowcounter int

   SELECT @pos = 0, @nextpos = 1, @rowcounter = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(',', @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos - 1
      INSERT @tbl (number, roworder)
         VALUES (convert(int, substring(@list, @pos + 1, @valuelen)), @rowcounter)
      SELECT @pos = @nextpos
      SELECT @rowcounter = @rowcounter + 1
   END
  RETURN
END
GO

Very powerful script for determine table sizesm the original blogger http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/121/determing-sql-server-table-size.aspx

DECLARE @TableName VARCHAR(100)    --For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR
select [name]
from dbo.sysobjects
where  OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY

--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)

--Open the cursor
OPEN tableCursor

--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName

--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
    --Dump the results of the sp_spaceused query to the temp table
    INSERT  #TempTable
        EXEC sp_spaceused @TableName

    --Get the next table name
    FETCH NEXT FROM tableCursor INTO @TableName
END

--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor

--Select all records so we can use the reults
SELECT *
FROM #TempTable

--Final cleanup!
DROP TABLE #TempTable

This has proved time and again to be a useful method of creating a datafeed.

DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + 
   CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @EmployeeList

SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name = ‘Column Name’)

There was a interesting requirement recently that came up.  It was to basically modify and existing BIT field and ALTER it to be an INT field.  Essentially it was to really push the limits of an INT field. 

Traditionally in a relational database, you have a one-to-one mapping of PK-to-FK (primary key to foreign key).  The goal here is to combine values that representative of the object in that database row.  In this case we are talking about permissions.

So first we need to create the table that will generate the IDs.  We need a seed which will be used as a basis of our ID.  We want to use binary values, it will be a power of two (2) for the primary key ID.

CREATE TABLE [Permissions]

(

     [Seed] [smallint] IDENTITY(-1, 1) NOT NULL,

     [Id] AS (power((2),[Seed])) PERSISTED NOT NULL,

     [PermissionName] [varchar](255) NOT NULL,

CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED ([Id] ASC),

UNIQUE NONCLUSTERED ([Seed] ASC)

)

Now that we have a table that can generate these IDs, we need to populate.  Below is a sample script that will insert some values into the table.

INSERT INTO [Permissions] (PermissionName) VALUES ('None')

INSERT INTO [Permissions] (PermissionName) VALUES ('Read')

INSERT INTO [Permissions] (PermissionName) VALUES ('Write')

INSERT INTO [Permissions] (PermissionName) VALUES ('Execute')

Let’s run a SELECT * and see what happened.

As you can see we have some nice values to work with.  I’ll show you what I mean.  Traditionally you would do some kind of join and then determine the value.  In this case, we are going to leverage the power of the bitwise operators built into SQL Server.  I’m going to write a stored procedure that sets my permissions.  Let say that I have a user table and in that table I have a field INT called PermissionId.  I can set my PermissionID using the stored proc or in a simple UPDATE statement.

UPDATE Users SET PermissionId = 3 WHERE [UserId] = 1

What I have basically said is for userid = 1, give them Read and Write permissions.  Let’s validate that. using bitwise operators.  We will exclude 0, because None will be included every time.

DECLARE @Permissions varchar(MAX)

DECLARE @PermissionId int 

SET @PermissionId = 3

SELECT @Permissions = COALESCE(@Permissions + '/', '') + PermissionName

FROM Permissions

WHERE Id = Id & @PermissionId

AND Id <> 0

PRINT @Permissions

This returns ‘Read/Write’.  Let try the same for 5.  It returns ‘Read/Execute’.  How about for 7. Yup, just what I thought, ‘Read/Write/Execute’.

declare @searchString varchar(100)
Set @searchString = '%word_to_find%'
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK)
on SO.Id = SC.ID
AND SC.Text LIKE @searchString
ORDER BY SO.Name

Run this query to see if there is an increasing amount on a particular SPID

select spid, cpu, physical_io from sysprocesses order by cpu desc

This is probably one of the most useful pages I’ve ever used on the Internet

http://www.petefreitag.com/item/169.cfm