Contents tagged with SQL Server

  • Where’s the SQL Server Configuration Manager in Windows 8?

    2854 Comments

    The SQL Server Configuration Manager is helpful for stopping/starting the SQL Server Services on your machine and also making sure all of the appropriate connection protocols are enabled.  In particular it really helps when you are running multiple instances of SQL Server since it allows you to view and control all of them from one place. 

    But in Windows 8 and Windows 8.1, the SQL Server Configuration Manager is no longer found in your list of programs and it does not show up when you search (Windows-S) for it by name.  So how do you get to it?

    This article http://technet.microsoft.com/en-us/library/ms174212(v=sql.110).aspx has details on how to get to the SQL Server Configuration Manager:

    To access SQL Server Configuration Manager Using Windows 8, because SQL Server Configuration Manager is a snap-in for the Microsoft Management Console program and not a stand-alone program, SQL Server Configuration Manager not does not appear as an application when running Windows 8. To open SQL Server Configuration Manager, in the Search charm, under Apps, type SQLServerManager11.msc (for SQL Server 2012) or SQLServerManager10.msc for (SQL Server 2008), and then press Enter.

    In SQL Server 2014 the SQL Server Configuration Manager is back in All Programs:

    image

     

    How to get to the SQL Server 2012 Configuration Manager in Windows 8 or Windows 8.1

    Start > Run > SQLServerManager11.msc

    image

  • Find stored procedures that reference a table column

    1 Comment

    We recently moved away from SQL Server replication and the database still has all of the rowguid columns and their associated indexes and constraints in it. We wanted to gain all of that disk space back so we went ahead with scripting out the delete of the indexes, constraints, and columns.

    One thing though is that there are many stored procedures in use and we needed to make sure none of these referenced the rowguid column.  The stored procedures really should not have used the rowguid column at all since it is solely created and used for SQL Replication, but you never know so we needed to confirm.

    The below script is what I created to search all of the stored procedures in the database for the rowguid column.  It could easily be modified to find any information within the stored procedures of a database.  (Note: One thing to watch out for is encrypted stored procedures.  This wouldn’t find anything in those so you would need to handle it through another method.)

    SELECT p.name, c.text FROM syscomments c
    JOIN sys.procedures p ON p.object_id=c.id
    WHERE c.text LIKE '%rowguid%'
    ORDER BY p.name

  • Lock request time out period exceeded

    6 Comments

    I was trying to drop a foreign key for a table I was working on and I ran into a time out exception from SQL Server Management Studio:

    image

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------
    Drop failed for ForeignKey 'fk_MyForeignKey'.  (Microsoft.SqlServer.Smo)
    ------------------------------
    ADDITIONAL INFORMATION:
    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
    ------------------------------
    Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)
    ------------------------------
    BUTTONS: OK
    ------------------------------

    I also tried dropping the foreign key manually using:

    ALTER TABLE MyTable DROP CONSTRAINT fk_MyForeignKey

    But this time the query was just sitting there running and running.  I let it run for some time and then when I checked the currently executing requests I found it was sitting in a suspended state.  What was interesting about the request was that the wait_time equaled the total_elapsed_time, so it was just waiting there for something else before proceeding.

    This is the sql query I used to see the currently executing requests (one of which was mine):

    SELECT r.session_id, r.status, r.start_time, r.command, s.text,
    r.wait_time, r.cpu_time, r.total_elapsed_time, r.reads, r.writes, r.logical_reads, r.transaction_isolation_level
    ,r.*
    FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

    And this in the particular row in question that made me realize my query was waiting on something else:

    image

    Now I needed to find out what is blocking my Alter Table command from running.  For that I used a query I found on this blog post Error 1222 Lock Request Time Out Period Exceeded When Set up Replication by Andrew Chen:

    select distinct object_name(a.rsc_objid), a.req_spid, b.loginame
    from master.dbo.syslockinfo a (nolock) join
    master.dbo.sysprocesses b (nolock) on a.req_spid=b.spid
    where object_name(a.rsc_objid) is not null

    I found that another SPID from SQL Server Management Studio was holding onto the table I was trying to alter.  Using sp_who2 with the SPID showed me the owner and where it was coming from, and also that it had been holding onto the table for 2 hours... and guess what!?!  It was me!  Smile

    I had been looking at the execution plan and client statistics of a query that I was performance tuning and that SQL Server Management window had a hold of the table I was trying to Alter.  As soon as I closed that window (and canceled that transaction) then I could drop the foreign key without a problem.

    Hopefully this will help someone else in the future!

  • SharePoint 2010 – SQL Server has an unsupported version 10.0.2531.0

    9 Comments

    I am trying to perform a database attach upgrade to SharePoint Foundation 2010.

    At this point I am trying to attach the content database to a Web application by using Windows Powershell:

    Mount-SPContentDatabase -Name <DatabaseName> -DatabaseServer <ServerName> -WebApplication <URL> [-Updateuserexperience]

    I am following the directions from this TechNet article: Attach databases and upgrade to SharePoint Foundation 2010.  When I go to mount the content database I am receiving this error:

    Mount-SPContentDatabase : Could not connect to [DATABASE_SERVER] using integrated security: SQL server at [DATABASE_SERVER] has an unsupported version 10.0.2531.0. Please refer to “http://go.microsoft.com/fwlink/?LinkId=165761” for information on the minimum required SQL Server versions and how to download them.

    At first this did not make sense because the default SharePoint Foundation 2010 website was running just fine.  But then I realized that the default SharePoint Foundation site runs off of SQL Server Express and that I had just installed SQL Server Web Edition (since the database is greater than 4GB) and restored the database to this version of SQL Server.

    Checking the documentation link above I see that SharePoint Server 2010 requires a 64-bit edition of SQL Server with the minimum required SQL Server versions as follows:

  • SQL Server 2008 Express Edition Service Pack 1, version number 10.0.2531
  • SQL Server 2005 Service Pack 3 cumulative update package 3, version number 9.00.4220.00
  • SQL Server 2008 Service Pack 1 cumulative update package 2, version number 10.00.2714.00

    The version of SQL Server 2008 Web Edition with Service Pack 1 (the version I installed on this machine) is 10.0.2531.0.

    SELECT @@VERSION:
    Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Web Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (VM)

    But I had to read the article several times since the minimum version number for SQL Server Express is 10.0.2531.0.  At first I thought I was good with the version of SQL Server 2008 Web that I had installed, also 10.0.2531.0.  But then I read further to see that there is a cumulative update (hotfix) for SQL Server 2008 SP1 (NOT the Express edition) that is required for SharePoint 2010 and will bump the version number to 10.0.2714.00.

    So the solution was to install the Cumulative update package 2 for SQL Server 2008 Service Pack 1 on my SQL Server 2008 Web Edition to allow SharePoint 2010 to work with SQL Server 2008 (other than the SQL Server 2008 Express version).

    SELECT @@VERSION (After installing Cumulative update package 2):
    Microsoft SQL Server 2008 (SP1) - 10.0.2714.0 (X64)   May 14 2009 16:08:52   Copyright (c) 1988-2008 Microsoft Corporation  Web Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (VM)

  • SQL Server 2008 Service Pack 1 and the Invoke or BeginInvoke cannot be called error message

    14 Comments

    When trying to install SQL Server 2008 Service Pack 1 to a SQL Server 2008 instance that is running on a virtual machine, the installer will start:

    image 

    But then after about 20 seconds I receive the following error message:

    image

    TITLE: SQL Server Setup failure.
    -----------------------------
    SQL Server Setup has encountered the following error:
    Invoke or BeginInvoke cannot be called on a control until the window handle has been created.
    ------------------------------
    BUTTONS:
    OK
    ------------------------------

    Searching for this issue I found that several people have the same problem and there is no clear solution.  Some had success with closing windows or Internet Explorer but that didn’t work for me; what did work is to make sure the SQL Server 2008 “Please wait while SQL Server 2008 Setup processes the current operation.” dialog is selected and has the focus when it first shows up.  Selected (with the current focus) it looks like this:

    image 

    Without focus the dialog looks like this:

    image

    Add a comment if you find out any information about how to consistently get around this issue or why it is happening in the first place.

  • How to change SQL Server login default database through SQL Script

    2 Comments

    I am moving a SQL Server database from one drive to another by detaching and then reattaching.  I detached the database, moved the mdf and ldf files, and then went to attach it and was presented with this dialog:

    image

    TITLE: Microsoft SQL Server Management Studio
    Cannot show requested dialog.

    ADDITIONAL INFORMATION:

    Parameter name: nColIndex
    Actual value was -1. (Microsoft.SqlServer.GridControl)

    This is because my login had the default database set to the database that I just detached.  This causes all sorts of errors with SQL Server Management Studio but none of them are particularly helpful, they pretty much just keep telling you “access denied” but not why or what to do.

    This is the dialog you would get if you try to click on the properties for your login:

    image

    TITLE: Microsoft SQL Server Management Studio
    Cannot show requested dialog.

    ADDITIONAL INFORMATION:
    Cannot show requested dialog. (SqlMgmt)
    Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
    For help, click:     http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

    Failed to connect to server. (Microsoft.SqlServer.ConnectionInfo)

    Cannot open user default database. Login failed.
    Login failed for user 'login'. (Microsoft SQL Server, Error: 4064)
    For help, click:     http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=4064&LinkId=20476

    And then even if you close and open SQL Server Management Studio you will get this dialog:

    image

    TITLE: Microsoft SQL Server Management Studio
    Failed to connect to server. (Microsoft.SqlServer.ConnectionInfo)

    ADDITIONAL INFORMATION:
    Cannot open user default database. Login failed.
    Login failed for user 'login'. (Microsoft SQL Server, Error: 4064)
    For help, click:     http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=4064&LinkId=20476

    What you can do to fix this is to change your login’s default database through SQL Script:

    ALTER LOGIN  [DOMAIN\login]
    WITH DEFAULT_DATABASE = master

    This will set the default database to your master database and then you will be able to log in and continue from there.



  • SQL Server encountered error 0x80070422 while communicating with full-text filter daemon host (FDHost) process.

    No Comments

    When trying to do a full text search with SQL Server 2008 I received this error:

    SQL Server encountered error 0x80070422 while communicating with full-text filter daemon host (FDHost) process. Make sure that the FDHost process is running. To re-start the FDHost process, run the sp_fulltext_service 'restart_all_fdhosts' command or restart the SQL Server instance.

    I was able to track this down to the SQL Full-text Filter Daemon Launcher service being disabled.  Enabling and starting the service resolved the issue for me.

    image

    It is interesting to note that the service is set to a Startup Type of Manual but after a restart of my machine the service is started, so SQL Server 2008 must be starting it.  Having the service disabled probably prevented SQL Server from starting it the first time I went to run a full-text search.

    See this post (SQL Server encountered error 0x80070422 (FIXED)) for the same error message but I did not do all the steps he did (I just enabled and started the service).



  • ISO 3166-1 Country Data SQL Script

    3 Comments

    I am creating a standard sign up form with one of the fields being a country drop down. So I created my Country database table and then needed to fill it with information. I found the ISO list of countries here, but then the issue was how to get that list into my Country database table. Luckily someone has already done that for me: http://vidmar.net/weblog/archive/2008/05/23/database-of-country-names-numeric-alpha-2-and-alpha-3-iso-codes.aspx

    But I also like to be able to check a script like that into source control to be able to track changes to it. So I updated the insert script to be a rerunnable insert/update script.  The script will check if a country id exists and if it does not exist, then it will insert the country otherwise it will just do an update. The insert sql script from http://vidmar.net/weblog/archive/2008/05/23/database-of-country-names-numeric-alpha-2-and-alpha-3-iso-codes.aspx really got me most of the way there so I need to give a lot of credit to him.

    The link below will get you my updated version of the country data update sql script. I also updated it to 2010 from the ISO 3166 code lists plus any changes that they have posted so it is up to date as of May 15, 2010. I will try (but no promises) to keep it updated on a regular basis. Feel free to contact me if you want to try and keep it updated for me and I will post it here.

    Click here for the country data update sql script (up-to-date as of May 15, 2010).

    You can use this sql to create the Country table:

    CREATE TABLE [dbo].[Country]
        (
            CountryId int NOT NULL,
            Iso2 char(2) NOT NULL,
            Iso3 char(3) NOT NULL,
            Name nvarchar(64) NOT NULL,
            DateCreated datetimeoffset(7) NOT NULL CONSTRAINT [df__Country__DateCreated] DEFAULT (sysdatetimeoffset()),
            DateModified datetimeoffset(7) NOT NULL  CONSTRAINT [df__Country__DateModified] DEFAULT (sysdatetimeoffset())
        ) 

    ALTER TABLE dbo.Country ADD CONSTRAINT
        pk__Country__CountryId PRIMARY KEY CLUSTERED
        (
            CountryId ASC
        ) WITH FILLFACTOR = 100 ON [PRIMARY]



  • osql.exe and unicode files – how to save your sql scripts with encoding

    4 Comments

    osql.exe is a great application for running sql scripts in a batch.  I use a batch file to execute multiple sql scripts that I use to rebuild my current application database from scratch.  When developing a brand new application, deploying a database in this way makes it really easy to recreate the database just like it will be created on Day 1 when you build out the Production environment. This requires scripting out all of your sql objects and then also having a way to execute all of those sql scripts easily.  That is where osql.exe comes in handy.

    But osql.exe does have one issue that I ran into this week where it does not like UTF-8 (codepage 65001) or UTF-7 (codepage 65000) encoded files.  And sometimes you need to include unicode characters in your sql scripts.  At first I thought osql just did not support unicode but that is not the case… it just does not like the UTF-8 or UTF-7 encoding. 

    Trying to run a UTF-8 (codepage 65001) or UTF-7 (codepage 65000) encoded file with osql.exe will give you errors such as:

    Incorrect syntax near '+'.
    Incorrect syntax near ' '.
    Incorrect syntax near 'ï'.

    Saving the same file with Unicode Encoding (codepage 1200) will work just fine.  Here is how to save sql scripts in Microsoft SQL Server Management Studio with a particular encoding (you can also use this method to see what type of encoding the file is saved in in the first place).  One other thing to note is that Visual Studio has this same type of Save As… functionality.

    From the Microsoft SQL Server Management Studio (or Visual Studio) File menu choose Save [FILENAME] As…

    image

    Then when the Save File As dialog appears you will see a little black arrow (inverted triangle) as part of the Save button. 

    image

    Clicking the just the inverted triangle portion of the button will give you a menu.

    image

    Choosing the Save with Encoding… option will then present you with an Advanced Save Options dialog.

    image

    image

    Here is where you can specify the encoding to use for the file.  For osql.exe make sure you specify either Western European (Windows) – Codepage 1252 or Unicode – Codepage 1200.  Do not select UTF-8 (codepage 65001) or UTF-7 (codepage 65000) or osql.exe will give errors when trying to parse the file.



  • How to Reseed a SQL Server Identity Column

    No Comments

    In SQL Server you can specify a column as an Identity column (ColumnName int IDENTITY(1,1) NOT NULL) to have SQL Server automatically set the value of this column upon insert by incrementing a seed value.  This works great but I have run into situations where I wanted to change what identity value is assigned during the next insert. 

    What had happened is that this table in question had the primary key column set as an Identity column.  There were only a couple thousand rows in the table but SQL Server was handing out identity values in the 10-million range.  In most cases this would not be a big deal, but for this table the identity value was actually used by customers so a eight digit number was not as easy to remember and repeat as a 4-digit number. 

    What had happened is that a row had been inserted into the table with a high identity value (over 10 million) and then SQL Server took over from there and handed out new identity values that were incremented from that number – 23000001, 23000002, 23000003, etc...

    To fix this, I was able to change the seed of the Identity field by using a DBCC CHECKIDENT statement.

    You can use this DBCC CHECKIDENT statement to check the current identity value:

    DBCC CHECKIDENT('table_name', NORESEED)

    And then you can use this DBCC CHECKIDENT statement to change the identity value to another value:

    DBCC CHECKIDENT('table_name', RESEED, 2300)

    After making this change new records inserted into the table now were assigned 4-digit values and were much easier for customers to remember and use.

     

    Technorati Tags: ,