Tuesday, April 15, 2008

SQL Server Management Studio won't install over SQL Server Management Studio Express

Since re-installing my main development machine recently, I've had a real irritation -I had to resort to SQL Server Management Studio Express as I couldn't get the full version installed. This wasn't a problem until today when I had to re-create some full text catalogs (which is one of the things you can't do using the GUI in SQL Server Management Studio Express).

I did look into getting this working before giving up before due to time constraints but this time I had no choice but this time I must have done something differently.

Just to bring you up-to speed, whenever I tried to install SQL Server Management Studio using the SQL Server 2005 installer, I got an error message something along the lines of "Edition Version Conflict". The installer then suggests you uninstall the express tools (which you do) and you try again just to get the same error message.

Most people on Google are just resorting to a complete re-install of your machine but I've found that's because you're not uninstalling the right thing. First thing I did this time was remove Microsoft SQL Server 2005 completely but near the end of the installer it asks you if you want to remove or modify. This time, I chose modify and set all the components to "Entire Feature will be Unavailable". That then seems to uninstall it fully for you. Now uninstall SQL Server Management Studio Express and you're done :)

Hope that helps someone else (or me in a few months time...)

SQL Server Management Studio won't install over SQL Server Management Studio Express

Useful Links:            Comments [0] 

Tags: SQL Server | The Site Doctor | WebDD

 Friday, November 02, 2007

How to search every table and field in a SQL Server Database

Today I had an issue with Umbraco and a copy of a deleted page appearing in the menu, I'll post how I fix it if I ever do find the answer but while trying to track the issue down I came across a really useful piece of T-SQL from Narayana Vyas Kondreddi (Vyas) that searches each table in a database and then each field in the table. I had to expand it to include integers etc but all credit to him! For reference here's a copy of the code:

Search all tables and fields in a SQL Server Database

CREATE PROC SearchAllTables
(
    @SearchStr nvarchar(100)
)
AS

begin...end

BEGIN
    -- 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
    -- Tested on: SQL Server 7.0 and SQL Server 2000
    -- Date modified: 28th July 2002 22:50 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...end

    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...end    

        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')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
    
            IF @ColumnName IS NOT NULL

            begin...end

            BEGIN
                INSERT INTO #Results
                EXEC
                (

                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + , 3630) + @TableName + ' (NOLOCK) ' +

                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                    FROM ' + @TableName + ' (NOLOCK) ' +

                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END

        END    

    END

    SELECT ColumnName, ColumnValue FROM #Results
END

How to search every table and field in a SQL Server Database

Useful Links:            Comments [0] 

Tags: SQL Server

 Tuesday, May 01, 2007

ASP.Net Membership SQL Roles access

We have recently moved over to SQL Server 2005 and as part of this transfer I decided to aggregate two separate ASP.Net Membership databases that were created purely out of error.

For those of you who don’t already know, you can happily run more than application’s security from a single membership database as long as you correctly configure the web.config’s security settings –for more information on doing that see my post on having dual records in the ASP.Net authentication table (see: Dual Records In The ASPNet Authentication Table). The important attribute/value set to configure if you are planning on running more than one application from the same roles database is “applicationName”. If you do not set “applicationName” you will find that users can log in across all your applications, roles/access levels will get mixed up and a whole bunch of other hullabaloo!

Luckily for me, the only records stored in one of the membership databases were two users, both of which I knew the password to so I decided I would simply update the web.config with the new database connection string and add them manually.

The next thing I wanted to sort however was the specific SQL Login’s access to the membership database, previously I simply added the user to all the various aspnet_ roles that were in the database which worked fine. As I’m looking to use this database for other applications in the future and I don’t like sharing usernames/passwords across applications, adding the roles each time would become a real PITA so I decided to add a new role with all the access required for the database so I could simply add the user to this new role. I called the role IIS_User.

A number of our applications build on the foundation of the ASP.Net Membership database with application specific values and so I tend to have another table for the application’s users within the applications specific database to store these values. The user has the usual UserId (usually an int) and a uniqueidentifier which allows me to link the two database together. With this in mind, I need additional access to the ASP.Net Membership database –SELECT permission on the tables. I don’t like adding more permissions to a role than needed but I needed a method of doing this quickly –assigning EXECUTE and SELECT permissions to the new role on the various tables/stored procedures. In time I’ll revisit this and work out which are needed by the role and remove the permissions not needed but for now this’ll do :)

The quick and dirty T-SQL

DECLARE @SQL nvarchar(4000),
    @Owner sysname,
    @objName sysname,
    @Return int,
    @objType nvarchar(5),
    @rolename nvarchar(255)

SET @rolename = 'IIS_User'

-- Cursor of all the stored procedures in the current database
DECLARE cursStoredProcedures CURSOR FAST_FORWARD
FOR
SELECT 
    USER_NAME(uid) Owner, 
    [name] StoredProcedure,
    xtype
FROM
    sysobjects
WHERE
(
    xtype = 'U'
  OR
    xtype = 'P'
)
  AND
    LEFT([name], 7) = 'aspnet_'

OPEN cursStoredProcedures

-- Get the first row
FETCH NEXT FROM cursStoredProcedures
INTO @Owner, @objName, @objType

-- Set the return code to 0
SET @Return = 0

-- Encapsulate the permissions assignment within a transaction
BEGIN TRAN

-- Cycle through the rows of the cursor
-- And grant permissions
WHILE ((@@FETCH_STATUS = 0) AND (@Return = 0))

  begin...end

  BEGIN

    --Determine the object's type (table/stored procedure) -could 
    --be done using a case too if more objects are added later
    IF @objType = 'P'

    begin...end

    BEGIN
        SET @SQL = 'GRANT EXECUTE ON [' + @Owner + '].[' + @objName  + '] TO ' + @rolename
    END

    IF @objType = 'U'

    begin...end

    BEGIN
        SET @SQL = 'GRANT SELECT ON [' + @Owner + '].[' + @objName  + '] TO ' + @rolename
    END

    -- Execute the SQL statement
    EXEC @Return = sp_executesql @SQL

    -- Get the next row
    FETCH NEXT FROM cursStoredProcedures
    INTO @Owner, @objName, @objType
  END

-- Clean-up after the cursor
CLOSE cursStoredProcedures
DEALLOCATE cursStoredProcedures

-- Check to see if the WHILE loop exited with an error.
IF (@Return = 0)

  begin...end

  BEGIN
    -- Exited fine, commit the permissions
    COMMIT TRAN
  END

ELSE

  begin...end

  BEGIN
    -- Exited with an error, rollback any changes
    ROLLBACK TRAN
    
    -- Report the error
    SET @SQL = 'Error granting permission to ['
    + @Owner + '].[' + @objName + ']'
    RAISERROR(@SQL, 16, 1)
  END

GO

ASP.Net Membership SQL Roles access

Useful Links:            Comments [0] 

Tags: ASP.Net | SQL Server | Web Development

 Tuesday, April 17, 2007

SQL Server Delete all data

I expect many people already know about this technique but I wanted to share it with those that don’t. The other day I needed to remove all data from a database before importing data from another database. I usually use DTS to copy the data across but knew that the database (one test) had conflicting ids so I decided deleting all the data out of the test database would be the best way to ensure all data’s up to date.

I found this useful little set of SQL at: http://sqljunkies.com/WebLog/roman/archive/2006/03/03/18386.aspx, there are two solutions propsed within the post and comments so here they both are:

Delete the data without resetting the identities

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable 'DELETE FROM ?'
GO
-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

Delete the data and reset the identities

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
GO 

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?' 
GO 

-- enable referential integrity again 
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 
GO

SQL Server Delete all data

Useful Links:            Comments [6] 

Tags: SQL Server | Web Development

 Wednesday, February 28, 2007

SQL Server SP2 quirks

Ok, as sad as it sounds I've been looking forward to the latest SQL Server service pack since I heard about some of the issues it fixed as there was two "glitches" in particular that seriously bugged me, namely:

• Loading a SQL file into the query editor on a live connection would ask you to log in again -this bugged the hell out of me because I have a number of routines saved on my disk as SQL files that manage client's servers and so I don't always have the password to easily hand which would just delay development (ok all be it by 30seconds or so but that's not the point)
• It would never remember my password in the initial splash screen, again see why above.

Anyway, it's great to see that these two points were fixed as well as a load of other issues but I couldn't help but chuckle when I saw the new context menus, I don't know about you but when editing this table I didn't know which one I needed to choose:

FWIW you need "Design" to open the design view and "Edit" to generate a SQL CREATE Script. Genius!

SQL Server SP2 quirks

Useful Links:            Comments [0] 

Tags: SQL Server

 Monday, November 13, 2006

Making sense of database responses

We're nearing the end of a large system for the lovely people over at Consolidated Communications Management Ltd and we've just come across a nice idea to make sense of database responses. Typically in the past we've simply returned -1/0 for failure and a positive integer (usually an id) of it was successful. The new method should add a little more conformity to our code. We already use a common Database Access Layer which handles the opening/closing of our connection, adding parameters etc but I’ve just added an enum with the common database responses, thought it may be of use to others:

public enum DatabaseResponse

{...}

{
    Success = 0,
    Init = -1,
    Found = -2,
    NotFound = -3,
    Duplicate = -4
}

This should cover the common responses from our database however to make it a little more futureproof we'll keep -1 through to -10 reserved for this data level and then start more specific responses from -11 (i.e. BadPassword).

Using it is simple (this is not real code before you ask!):

public bool Save()

{...}

{
    DatabaseLayer DBLayer = new DatabaseLayer();

    //Check whether we should be saving or inserting this record
    if (this.Id == (int)DatabaseLayer.DatabaseResponse.Init)

    {...}

    {
        //The Id's set to initialised only so insert
        //The create method simply inserts the values into the database and returns
        //either the Id of the newly inserted record or our database response
        this.Id = DBLayer.Create(this);

        if (this.Id >= (int)DatabaseLayer.DatabaseResponse.Success)

        {...}

        {
            //The insert was a success
            return true; 
        }

        else

        {...}

        {
            //The insert failed for some reason -you could throw an error 
            //to catch or simply bubble the response upto to the code behind layer
            return false; 
        }

    }

    else

    {...}

    {
        //When updating the record don't use the Id to store the response, instead
        //throw it into a temp object and use that so the Id's still stored in the object
        int response = DBLayer.Update(this);

        if (response == (int)DatabaseLayer.DatabaseResponse.Success)
            return true; //All's ok
        else
            return false; //Something went wrong -it could be the item wasn't found etc
    }

}

Making sense of database responses

Useful Links:            Comments [1] 

Tags: ASP | ASP.Net | SQL Server

 Monday, October 09, 2006

Stored Procedure to assign permissions

This is a useful stored procedure for assigning permissions to users quickly and easily. We tend to assign a new login to each application we develop, this way we limit the damage possible in the event of a username/password compromise.

Note 1: In addition, we tend to prefix our database objects with useful prefixes to group relevant tables, i.e. if we had login information stored in the database we may use “Login_” as the prefix, using this method with this Stored Procedure to assign permissions you can easily select the relevant objects. So you could alter the stored procedure a touch:

Stored Procedure to assign permissions

Useful Links:            Comments [0] 

Tags: SQL Server

 Thursday, September 28, 2006

SQL Server a memory hog? No, not at all

Craig found an amusing dity today, he was messing around with the settings in SQL Server Express and noticed these memory settings:

For those of you who don’t want to deliminate it, that’s:

• 2,147,483,647 Megabytes
• 2,147,483 Gigabytes
• 2,147 Terabytes
• Or 2 Petabytes!!

One does have to wonder what sort of super computer Microsoft are expecting to have run the database. It's certainly future-proof that's for sure. I'd love to have 1TB of RAM, let alone 2PB! So the next time you wonder what’s taking up all the memory, best check SQL Server –you might just find that lost Petabyte…

BTW if this blog post is still around when Petabytes are as common as Mega and Gigabytes are now then feel free to lean back in your chair and recollect “the good ol’ days” when computers only had 200-300GB of disk space and a couple of Gig’s RAM.

SQL Server a memory hog? No, not at all

Useful Links:            Comments [0] 

Tags: SQL Server

 Monday, July 03, 2006

Bulk Granting EXECTUTE Permission on SQL Server

Here's some code I regularly find useful when going from a dev server to a production server (or changing the username), I'm sure there's a more automated way but for a quick fix...

DECLARE @username varchar(100)
SET @username = 'xyz'
SELECT
    'GRANT EXECUTE ON ' + name + ' TO ' + @username
FROM
    sysobjects
WHERE
    xtype = 'p'
AND
     LEFT(name, 4) = 'PRE_'

The code is simple, it lists all the stored procedures with a set prefix and generates the T-SQL to grant EXECUTE permissions to a given user.

To use it, set the username, update the last line if you would like to limit it's bounds (we prefix ours for clarity i.e. SProcs with a prefix of CMS_ are used in the Content Management System) and run it, then copy the results and run them in a Query Analyser.

Bulk Granting EXECTUTE Permission on SQL Server

Useful Links:            Comments [0] 

Tags: SQL Server