Archive for category SQL Server

Full Text Reindex, scheduled job has no steps!

Posted by shawson in SQL Server on January 12th, 2010

We have a SQL Server 2005 box, on which we have a database which has a full text index. The database gets a massive refresh of its data each night, meaning the full text index needs rebuilding. I added a schedule to my full text index (Databases > [DBName] > Storage > Full Text Catalogs > Right click your index – click properties > Population Schedule) and this created me a new job (SQL Server Agent > Jobs) but when i opened it up and went to steps, nothing was there!  If i tried running the job it would fail because there are no steps!

I found this as a documented bug in the initial release candidate of sql 2005 (since fixed in the service packs) on the microsoft connect site, and added a workaround – basically just add your own job, using the following sql (which i found on msdn) to rebuild your index;

ALTER FULLTEXT CATALOG [catalogue_name] REBUILD;

Of course, this is only a workaround until we can get the latest service pack installed.

, ,

No Comments

How to run SQL Profiler against local SQL Express instances

Posted by shawson in .net, SQL Server on December 9th, 2009

I’m building a project using entity framework and wanted to ensure it wasn’t going mental and spamming the database with more queries than it should- this requires SQL Profiler, but i didn’t know how to connect it to my local instance of sql (I had just added an MDB file to the app’s app_data folder, so it was running via local sql express)

Turns out it’s easy- you just connect to .\SQLEXPRESS database and then execute this query against the master database;

SELECT
owning_principal_name,
instance_pipe_name
FROM
sys.dm_os_child_instances

From the results you will be able fish out the “named pipe” that you can just put into the server name box when connecting with profiler.

Sql Profiler connection dialogue

Sql Profiler connection dialogue

via Brad Wilson: Profiling SQL Server Express User Instances.

, ,

No Comments

Whats the definitive maximum size of an email address?

Posted by shawson in E-Commerce, Email, SQL Server on December 1st, 2009

I’ve written lots of app’s which store email addresses, and usually go for a nvarchar(100) to store them, but thought I would find out once and for all, what the actual maximum size was- just out of curiousity.

Having googled around, I came across a couple of lengthy posts on the matter (here and here), quoting figures from various standard (which seemed to point at 320 character), then corrections to those standards by members of ICANN based on the SMTP spec and so on and so forth…

After much trawling, it seems the official answer is : 254 characters (256 including the standard <> brackets around the address)

, ,

2 Comments

Conditional Order-By clause in SQL 2005

Posted by shawson in SQL Server on July 23rd, 2009

In SQL Server 2005 onwards you can have conditional Order By clauses in your stored proc’s- for example

SELECT EmployeeID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag WHEN 1 THEN EmployeeID END DESC
        ,CASE WHEN SalariedFlag = 0 THEN EmployeeID END;
GO

CASE (Transact-SQL).

No Comments

ASP.Net Role & Membership Providers (Under IIS7) – DOESN’T Work!

Posted by shawson in .net, SQL Server on July 16th, 2009

Ok well, it does, but I’ve had a very stressful afternoon at it’s merciless hands!  Just trying to get the ASP.Net roles & membership working on asp.net 3.5, using IIS7.  I created the project and clicked “ASP.Net Confguration” from the project menu, but no working providers existed.

I defined some custom providers and nothing would get picked up.

It turns out, under IIS you need to define a connection string with a specific name to make this work–

 <connectionStrings>
    <clear/>
    <add name="LocalSqlServer" connectionString="your real connection string goes here!" />
  </connectionStrings>

So we’re clearing out the default inherited connection strings then overriding the default LocalSqlServer connection to have the correct connection to our real database, as this appears to be the connection string it defaults to.

Save the web.config file, the fire up the “ASP.net configuration” site again and you should be away!

Thanks to Mr Ledger for channelling the awesome power of google through himself to yield that nugget of joy and relief!

No Comments

Collation issues setting up ASP.Net membership tables

Posted by shawson in .net, SQL Server on July 16th, 2009

Setup failed.

Exception:
An error occurred during the execution of the SQL file ‘InstallRoles.sql’. The SQL error number is 446 and the SqlException message is: Cannot resolve collation conflict for equal to operation.
Cannot resolve collation conflict for equal to operation.

I recently had this problem while trying to setup the roles & memberships gubbins on a database using the aspnet_regsql tool on the v2 .net framework.

This turned out to be because this was a SQL 2005 database which we had imported from a third party, produced on SQL2005.  The SQL2005 database had a collaction of SQL_Latin1_General_CP1_CI_AS while our sql 2000 box’s default collection was Latin1_General_CI_AS.

The fix was simple; having backed up our database (!) simply run this command;

Alter Database testdatabase Collate Latin1_General_CI_AS

This converted the database to the same Latin1_General_CI_AS collation as the server’s default, and allowed the aspnet_regsql to do it’s job unhindered!

remove_modeNOTE: If you’ve ran aspnet_regsql once already before correcting your collation (which i would imagine you probably did!), make sure you run it again in remove mode before running a final time in install mode, as the first time it runs it will bomb out after creating some of the tables already leaving you with tables in your database still using the old collation!

No Comments

SQL Server truncate log

Posted by shawson in SQL Server on May 11th, 2009

The command line syntax for truncating the SQL log’s once they are full!

BACKUP <db_name> LOG  WITH TRUNCATE_ONLY

No Comments

SQL 2000 Font!

Posted by in Accessibility, SQL Server on January 21st, 2009

Another simple one which i've only just recently found out- changing the font in the stored procedure viewer under enterprise manager for sql 2000- when in the sql server window, right click and select font.  Then update the "Text" property to the font of your choice and click OK.  This make a permanent change to any future stored procedure windows.

No Comments

Single Character Wildcards

Posted by shawson in SQL Server on January 21st, 2009

To my shame, I only recently realised there was a single character wildcard available in SQL ( _ ) as well as the mutliple character wildcard ( % )!  really handy for substituting single quote characters (or other weird ones) to ensure a match when doing a ‘like’ lookup

No Comments

SSIS / DTS packages violating referential integrity

Posted by in SQL Server on December 8th, 2008

I've recently inherited the integration support for a web project being built by a third party which is about to go live.  Amongst the numerous odd's and ends which are being tied up are a bunch of SSIS packages which have been updated, but not run for some time.  When the moment came to finally push the button, as is my luck, nothing happened. 

The package clears a bunch of data from some book and author tables, then replaces it with nice fresh data from our internal data repository- the trouble is, the developer has a "shopping basket" and "shopping basket items" table, which had the "book id" from the books table as a foreign key- so in order to maintain referential integrity the DBMS (Sql 2000) moaned up when we tried to bin everything from the books table. The exact error i got was;

Error: DELETE statement conflicted with COLUMN REFERENCE constraint ‘FK_ShoppingBasketItem_Book’.  The conflict occurred in database ‘BookDB’, table ‘ShoppingBasketItem’, column ‘BookID’.

Error: Executing the query “Delete from Book” failed with the following error: “The statement has been terminated.”  Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

After having googled about for a bit it seems there is no nice way to do this- but the cleanest work around I could find was to disable the CHECK constraint before the package ran, then re-enable it when complete- the syntax was;

ALTER TABLE <Tablename> NOCHECK CONSTRAINT <constraint name>
ALTER TABLE <Tablename> WITH CHECK CHECK CONSTRAINT <constraint name>

For example, in my case;

ALTER TABLE ShoppingBasketItem NOCHECK CONSTRAINT FK_ShoppingBasketItem_Book
ALTER TABLE ShoppingBasketItem WITH CHECK CHECK CONSTRAINT FK_ShoppingBasketItem_Book

No Comments