0 Comments

I have a table filled with numbers. It is a table with the name numbers and there is only one field named ‘num’ which is an integer (so max value is 2.147.483.647 as seen on MSDN) and the integer is  an identity and unique.

2014-03-05 12_10_47-VM530.ssinternational - dbo.numbers - Microsoft SQL Server Management Studio

The table just contains numbers from 1 until 10.000. I use it to inner join, for instance:

You can have a table with a row: productA is 10 times in stock. You can then inner join the numbers table if you need 10 rows of productA.

select * from products 
inner join numbers on num <= stock

But I only had 10.000 rows in it and encountered a bug in my system because 10.000 was not enough. So I had to increase it. I have found several solutions to create a numbers table. But no snippet to update an existing number table.

So here is my snippet to add additional numbers to your number table:

begin transaction
SET IDENTITY_INSERT [numbers] ON

DECLARE @i INT;
SELECT @i = 10001;
SET NOCOUNT ON
WHILE @i <= 1000000
BEGIN
    INSERT INTO [numbers] (num) VALUES (@i);
    select @i= @i + 1;
end;

SET IDENTITY_INSERT [numbers] OFF
commit
Good luck!
Pin on pinterest Plus on Googleplus Post on LinkedIn
9 Comments
  •   Posted in: 
  • SQL

SQL-Server-Management-Studio-2008

If you want to inner join two tables from different databases, you might encounter some collation errors.


When I executed this query:



SELECT ProductCode, ProductResalePrice
  FROM [database1].[dbo].[PRODUCTS] 
  inner join [database2].[dbo].[items] on
  ProductCode = [items].itemCode
where ProductDeleted = 0 

I got this message:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

So when I executed the following query, I found out that I had two different Collations:

SELECT DATABASEPROPERTYEX('database1', 'Collation') SQLCollation;
SELECT DATABASEPROPERTYEX('database2', 'Collation') SQLCollation;

I thought that it was because one database was migrated from SQL Server 2000 to 2005 to 2008 and the other one (database2) was from 2005 to 2008. But apparently the default collation depends on the region settings of your Windows installation and gets set when you install SQL Server.

Now you can change the collation of your database with various solutions which can be found around the internet, but I wanted to simply join two tables and do one update.

I had to Google a lot to find out, how to define collations when using an inner join. But here is the solution for my query:

SELECT ProductCode, ProductResalePrice
  FROM [database1].[dbo].[PRODUCTS] 
  inner join [database2].[dbo].[items] on
  ProductCode collate SQL_Latin1_General_CP1_CI_AS = [items].itemCode
where ProductDeleted = 0 

So you can define collation on the fields which you join. Open-mouthed smile 

By the way: this is a great resource for the different collations: http://msdn.microsoft.com/en-us/library/ms143515%28v=SQL.90%29.aspx

Hope that it helped you, it would have saved me a lot of frustrations and time.

kick it on DotNetKicks.com Shout it

Pin on pinterest Plus on Googleplus Post on LinkedIn
0 Comments
  •   Posted in: 
  • SQL

Our company acquired a brand new database server with 12 gig’s of RAM. It’s really cool, but unfortunately I have to do the migration…

I started with upgrading our test server which is in our LAN. It was running SQL 2005 standard and I wanted to upgrade it to SQL 2008 standard.SQL Server Installation Center

You should expect the worse case scenario when doing stuff like this, but apparently Microsoft did an amazing thing with the installation wizard. If you insert the SQL 2008 DVD it starts to copy the required files for the installation. After that you normally select ‘install new instance’ but there was this ‘Upgrade from 2000 or 2005’ option!

 

[more]It’s under ‘installation’ and then the forth option: ‘upgrade from SQL Server 2000 or SQL Server 2005

The next task was to migrate the SQL 2005 production database to our brand new server which has SQL 2008 installed. All I did was fire up the ‘Microsoft SQL Server Management Studio’ and chose ‘Task –> Backup’ when I right clicked the database. Then i just wrote the whole DB to a *.bak file and rarred it with WinRar to reduce the file size by 80%! Then i moved it to the new server which is accessible by LAN and restored it there.

All there was left for me to do was re-creating users and giving them the correct roles. Now the next task for me is to migrate the Jobs that I had scheduled in the SQL Server Agent…

Pin on pinterest Plus on Googleplus Post on LinkedIn