SQL Server

You are currently browsing articles tagged SQL Server.

If you’ve ever encountered the words collation and conflict in the same sentence, or better, error message, then you’re going to love this one.  It’s a Microsoft Connect request that exists since 2008, created by SQL Server MVP Erland Sommarskog, and it suggests that the SQL Server installer should not select a server collation by default.

Ow yeah, sure got my vote!  I don’t think I ever came across a server which was initially installed with the correct collation unless it was installed by myself.  Not selecting a collation by default in the setup means that whoever is installing the server, usually a DBA, needs to think about (and more importantly: understand!) what collations are and how selecting the wrong one might impact the applications using the databases.  No more messed up servers with half of the databases using Latin1_General_CI_AS and the other half using SQL_Latin1_General_CP1_CI_AS, with possibly a couple of French_CI_AS databases thrown in for fun.

Sound familiar?  Then vote this up!

Share

Tags: , ,

Earlier this month I’ve had the pleasure of installing a development PC from scratch.

As always, the first question that then comes to mind is: in what order should I install my software?  SQL Server, Visual Studio, any service packs, add-ons, …

Obviously, the basic requirement is SQL Server 2008 R2.  I also need Visual Studio 2008 for custom development, and Visual Studio 2010 for the Database Projects.  Furthermore we’re using Team Foundation Server 2010 for source control purposes.

The list below represents the order with which I’ve come up and which I’ve applied with success.  This order is based on own experience, plus some web research to make sure I didn’t miss anything crucial.

Tip: when given the chance, always go for Custom Install.

  • Visual Studio 2008 (ensure to not install SQL 2005 Express Edition)
  • VS2008 Team Explorer (link)
  • SP1 for VS2008 (link)
  • VS2008 SP1 Forward Compatibility Update for TFS2010 (KB974558)
  • SQL Server 2008 R2
  • SP1 for SQL2008R2 (link)
  • Visual Studio 2010 (ensure to not install SQL 2008 Express Edition)
  • SP1 for VS2010 (link)

In case you’re wondering why this matters so much, well, let’s just say that getting the order wrong can really cost you some time.  In the past I’ve had the pleasure of witnessing an Intellisense (in SSMS) that’s completely broken and an error in VS2010 that prevented me from using the Database Schema Comparison feature to script out the changes.

Hopefully this saves you some time, and I encourage you to have fun while you’re watching that installer’s progress bar!

Valentino.

Share

Tags: ,

I’ve been a user of the Microsoft Connect site for several years now.  A regular user who now and then casts an Up vote or clicks the “I can reproduce it!” link.  Not as a content poster.  Well, I did post some comments in the past, but I never filed an issue or suggestion.

So, a couple of weeks ago I decided to post my first bug to Microsoft Connect, an issue which I had discovered in SQL Server 2012 RC0.  Because this process took me more time than I’d expected, I‘m now blogging about my experience.

The hardest part about the whole experience was finding the actual page that needs to be used to enter and submit the content.

Submitting Content To Microsoft Connect

Are you in for a ride on the Connect site?  Here we go!

Step 1: Search For Your Product on The Connect Home Page

Enter a search term on Microsoft Connect to locate your favorite product

On the Home page, enter a search term with which you can locate your favorite product.  If you’re not signed in yet, do that first.

Step 2: Click To Participate

On the search results, click the Participate link next to your favorite product.

Once the search engine has produced the output, locate your favorite product and click the Participate action next to it.

Step 3: Submit Product Feedback

Click Submit Product Feedback to submit product feedback.

After clicking Participate, the following overwhelming screen appears.  The link that you’re looking for is called Submit Product Feedback.

Step 4: The Feedback Center

After clicking Submit Product Feedback, you end up at the Feedback Center.

Do not use the Submit link, use Search first to check if your issue already exists.

Looking at the screen above, you’d think that the yellow Submit Feedback button can be used to open the page where you’d need to enter the bug details.  Well, it doesn’t…

You need to search for your issue first, to ensure it doesn’t exist already.

Step 5: The Submit Feedback Button Has Been Found, Eureka!

The Submit Feedback button on the Product Search Results screen.

After you’ve performed the search for your issue, you get to the following page above.  Finally, on that page you can click a real Submit Feedback button that will bring you to the Select Feedback Form page.

Step 6: Select Your Feedback Form Of Choice

Select SQL Server Bug Form to file a SQL Server issue, or SQL Server Suggestion Form for a suggestion.

On the Select Feedback Form page, select SQL Server Bug Form to file a SQL Server issue, or select SQL Server Suggestion Form to enter a suggestion.

Step 7: Fill Out The Textboxes

The "Submit A Bug" Form for SQL Server.

Finally, the page we’ve been trying to locate when we started the adventure, quest accomplished!  With this new knowledge, we can now skip directly to step 6 through the following link: Select Feedback Form

Disclaimer: please ensure that you’re not creating duplicate bugs or suggestions, but use whatever search engine your prefer to achieve that goal!

So, here’s my New Year’s present to all of you!  And remember: have fun!

Valentino.

References

Microsoft Connect

Feedback Center

Select Feedback Form

Share

Tags: , ,

In this post I’m actually just going to repeat what I answered to a question on Experts Exchange.

The OP was having an issue getting a record count of all tables in his database.  It was working through the Management Studio, using the undocumented but well-known sp_MSforeachtable stored procedure.

exec sp_MSforeachtable 'select ''?'' TableName, count(*) Cnt from ?';

However, the OP needed the list of record counts inside an Integration Services package.  And this didn’t work out as expected, because sp_MSforeachtable uses a temporary table, something that SSIS does not like.

So I proposed to use a script similar to the following, possibly in a stored procedure.

/*
DESCRIPTION: Returns a list of record counts for each table in the database
AUTHOR:         Valentino Vranken
CREATED:     2011/05/21
VERSION:     1.0
COPIED FROM: http://blog.hoegaerden.be
*/
declare @tempTable table
(
    TableSchema nvarchar(256),
    TableName nvarchar(256),
    Cnt bigint
);

declare @sql nvarchar(4000);
declare @tableSchema nvarchar(256);
declare @tableName nvarchar(256);
declare @columnName sysname;
declare @cnt bigint;

declare tableCursor cursor for
    select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES
    where TABLE_TYPE = 'BASE TABLE';

open tableCursor;

fetch next from tableCursor into @tableSchema, @tableName;

while @@FETCH_STATUS = 0
begin
    set @sql = 'select @cnt = COUNT(*) from [' + @tableSchema + '].[' + @tableName + ']';

    exec sp_executesql @sql, N'@cnt bigint output', @cnt = @cnt output;

    insert into @tempTable select @tableSchema, @tableName, @cnt;

    fetch next from tableCursor into @tableSchema, @tableName;
end;

close tableCursor;
deallocate tableCursor;

select * from @tempTable;

Yeah, I know, cursors are bad… But hey, at least it works!  Just don’t schedule it for execution every five seconds.

I seem to be repeating myself.  I wrote something similar when I posted the script about finding empty columns many months ago.

The most important part of the script is the following:

select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE';

This is using one of the information schema views to retrieve a list of tables in the database.  The INFORMATION_SCHEMA.TABLES view contains a list of tables and views, so the where clause is needed to filter out the views.  INFORMATION_SCHEMA.TABLES is just one of many interesting views available in SQL Server, and should always be used in favor of the sys tables.

What does the output look like?  Here’s part of the output when executed on the AdventureWorks2008R2 database:

Record count of all tables in the AdventureWorks2008R2 database (although not all are shown, you get the idea)

Have fun!

Valentino.

Share

Tags: , ,

Last month we had the pleasure of enjoying the third edition of our Belgian SQL Server Day.  If you were there and you were unable to attend a particular session, even though you were interested in it, here’s your answer.  The sessions have been recorded, and the videos are now available online!  How’s that for a New Year’s present?

SQL Server Day 2010: The Videos

And if those videos didn’t still your hunger, or you just have plenty of time to kill, check out this blog post on Chris Webb’s BI blog.  He has posted a link to the 152 videos of SQL Bits, a three-day SQL Server event in the UK.

Still not satisfied?  Maybe these SQL Server 2008 Microsoft Certified Master (MCM) Readiness Videos can keep you busy for a little while then. :-)

Have fun!

Valentino.

Share

Tags: , , ,

« Older entries

© 2008-2017 BI: Beer Intelligence? All Rights Reserved