TSQL Latin Collation Error

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

I'm not sure if it's as convenient with the earlier versions but SQL Server 2008 allows you to easily change the collation of your database through the Properties window.

The first thing you would have to do is to drop all connections to the database. This can be easily done by right clicking on you database --> Tasks --> "Detach..." then tick the "Drop all connections" check box.

Then simply restore the database using your back up file (it's usually the same name as your database with a .bak file extension).

Now you're sure no one is initially connected to the database.

Right click on the database --> Properties --> Options then on the top is a drop down for the Collation options. We generally choose "SQL_Latin1_General_CP1_CI_AS".

Run SQL Script from command line

I have a 1GIG update script generated by my SQL Server Management Studio, how would you run it?

Original story: I have a perfectly running database in 2008 that has to be transferred to a 2005 server. I tried back-up and restore from 2008 to 2005 servers but that got all weird so I needed a new strategy.

Solution: Generate Script.

So I generated a script (destination: file instead of a new query window because openning a file and writing a 1Gig worth of text on it would result to a not-enough memory error). As a result, I have a 1GB update script that I can't open on the Management Studio but I have to run on 2005.

Solution: call it from the command prompt then run it.

Here's how:

1. Make sure that your script has USE Database statement at the very top. You don't have to open your file to check (actually you SHOULDN'T or your machine might hang). Just make sure you toggle the option

2. On command line type: OSQL -U [UserLogin] -i [absolutePathOfSQLscript]

For example:

OSQL -U sa -i C:\Users\Oyen\MyDocuments\1Gig_MergeScript.sql

**Note: Case matters (U != u)

You may also opt to display the script ran with the token -e which would look like: OSQL -U [UserLogin] -e -i [absolutePathOfSQLscript]

3. Password will be prompted if it is required by the login, then simply provide the password **Notice that whatever you type will not be displayed for security.

4. Press enter then wait until the script is finished running.

To note: it took me less than an hour to run a 1gigabyte update script. I hope that would help you estimate how long it will take you to run yours :)

Remove Identity Constraint from Column (TSQL)

There's no way to remove an Identity Constraint from a column in a table, except through adding a new column, reassigning the Identity Column data to that new column then recreating the Identity Column (by drop and add) then reassigning back the data to it.

In the following code, I have a PrimaryKey ID that is also set as an Identity Column. What I want to do is to remove the Identity property by creating a new column name 'tempID' then temporarily storing the ID data into it while I recreate ID column so as to remove the Identity property.


ALTER TABLE [TableName]
ADD tempID int NULL
GO
UPDATE [TableName]
SET tempID=[IdentityColumnName]
GO

-- drop all foreign key constraints related to a table
-- see TSQL Script to drop all constraints

GO
ALTER TABLE [TableName]
DROP CONSTRAINT PK_[TableName]
GO
ALTER TABLE [TableName]
DROP COLUMN [IdentityColumnName]
GO
ALTER TABLE [TableName]
ADD [IdentityColumnName] int NULL

GO
UPDATE [TableName]
SET [IdentityColumnName]=tempID

GO
ALTER TABLE [TableName]
ALTER COLUMN [IdentityColumnName] int NOT NULL

GO
ALTER TABLE [TableName]
ADD CONSTRAINT PK_[TableName] PRIMARY KEY CLUSTERED ([IdentityColumnName]) ON [PRIMARY]
--or ADD CONSTRAINT [PrimaryKeyConstraintName]

GO

-- drop all foreign key constraints related to a table
-- see TSQL Script to drop all constraints

GO
ALTER TABLE [TableName]
DROP COLUMN tempID
GO

SQL Server Analysis Services Deployment Error

I was following the MSDN Tutorial on SQL Server Analysis until I got stuck in the Deployment lesson. The error is mostly about being unable to connect to the database or having insufficient permissions.

I've tried everything and finally decided to start from scratch. Then the next attempt succeeded. I searched through whatever difference my first project had with the second and here is what I found out:

• Right click your Project and click Properties.
• Server: localhost
• Database: (e.g., Sample SSAS (attempt 3) should be Sample SSAS attemp 3)

This is where my first Project crashed. I assumed that the server is supposedly the Server in my SQL Server Management Studio (which is MSSQL) and then the Database is naturally the database that I'm designing my Analysis Services Project for, which is AdventureWorksDW. Apparently, I am very much mistaken.

I shall find out more regarding this.