.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Kaviya Balasubramanian
Sgraph Infotech
Imran Ghani
Post New Web Links

MSSQL 2008 R2: Change Server Collation Steps, with user DBs already of desired collation

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
 
On the default instance of SQL Server 2008 R2, I want to change the server collation from the usual US default SQL_Latin1_General_CP1_CI_AS to an application vendor's required collation, Latin1_General_BIN.  I understand that changing the server collation is essentially the same as changing the collation of databases master and model -- is that a fair statement? But my main question is, since there are user databases on the server that I would like to preserve, can I not simply detatch the user DBs prior to performing the server collation change, and then reattach?  The database collation of the user databases is already set to Latin1_General_BIN.  This article, Setting and Changing the Server Collation, at http://msdn.microsoft.com/en-us/library/ms179254.aspx says that to preserve the user databases, I should script all DB objects out, and bulk-copy all data out, because the DBs will have to be recreated and repopulated.  Can someone explain why I ought not simply detach and reattach the user DBs, given that they are already of the desired collation (i.e., the same collation that I will be changing the server collation to)? Your help much appreciated. -Tom


View Complete Post


More Related Resource Links

Cannot change the collation sequence of a Database in SQL Server 2008 R2

  

I tried two steps below separately: restricted user ran for 30 minutes then I stopped the execution; They obviously could not run simultaneously. I only want to operate at the database and not the server level. Our other key db is already in the desired collation. We'd like to have equijoins work on tables with the same collation sequences, and not have to use ETL or individual ALTERs for each table involved.

Second point: is CP1 below a 2000 only collation? That appears to be a patently false assumption in another forum posting. It appears to be a valid 2008 R2 collation is the point.

ALTER DATABASE DBNAME

SET RESTRICTED_USER WITH NO_WAIT;

ALTER DATABASE DBNAME

COLLATE SQL_Latin1_General_CP1_CI_AS ;

 

Many Regards,

MM

 

 


what's the right collation for sql server 2008 to ignore french accent?

  
I want to  sql server 2008 collation using english as default(in Canada). when search for text, french accent can be ignore. For example, À,Â,Ä, à, â, ä will be treated same as a or A, but the data still is stored as different(such as for key). so what is the right collation need to be selected when install sql server 2008?. If existing instance collation is not the right one, how to change it to the right one?

SQL Server 2008 collation design techniques

  

Can anyone provide some insight into design techniques when multiple languages are required within SQL Server?

Let me tell you about our situation. We currently have a database that holds French content. New languages are going to be added (Chinese, Arabic etc.). We have to make a decision to determine what would be the best approach to deal with all those languages.

 

Here are the approaches we identified:

 

o   I database per language:

SQL Server 2008 R2 Setup: How to set Collation during interactive setup?

  

Hi!

Did I miss something when running SQL 2008 R2 Setup.exe on Win2008 Server R2?  I did not notice my opportunity to set the server collation to something other than my regional default.  Was it on a tab that I missed?

Are my choices either (a) set collation at the OS level through Control Panel...Regional...Advanced, or (b) run setup.exe at the command prompt while specifying the desired server collation?  Because I get that impression from reading this: http://msdn.microsoft.com/en-us/library/ms144260.aspx

Have I read that correctly?

Thanks!

Tom

 


SQL SERVER 2008 COLLATION ISSUE

  

Hi,

i have 2 data bases source(2005), destination (2008). i took a back up of database from 2005(SQL_Latin1_General_CP1_CI_AS) and restored in 2008(Latin1_General_CI_AS). now while running sp's, or any other queries, i am getting this error.

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

I donot want to re install my server as it is a big task , and i already restored DB's which are also a lot . so can anyone please sugegst a solution apart from the above two(reinstalling SQL, re-restoring DB's).

 

Thanks

santosh


On SQL_Latin1_General_CP1_CI_AS SQL Server 2008 Default Collation

  

Collation is one of the most confusing features of SQL Server. BOL 2008 states that backward compatibility is the only reason to go with SQL collation. I thought previously it is just smart to stay with the default Microsoft offers unless there is a real good reason to choose a different collation.

So is the default SQL_Latin1_General_CP1_CI_AS  (Case Insensitive, Accent Sensitive) a good choice? (Assume the installation is brand new SQL Server 2008)

Related links:

SQL Server offers the SQL_Latin1_General_CP1_CI_AS collation as the default collation for server installations.

For backward compatibility, the default English-language (US) collation is SQL_Latin1_General*.


Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

Should I change my Server Collation ???

  

My server collation is SQL_Latin1... as all my databases were upgrade from earlier version.

Should I plan a change to the collation to a window's based collation?


No ASPNET User in Windows Server 2008?

  

I have been looking everywhere online regarding this, but have not found a clear answer.

 

I have an ASP.Net app running on Windows Server 2008, it's basically a reporting app using Crystal Reports. I have encountered a roadblock in regards to rendering some pages. When I run the app in Visual Studio(on the same 2008 machine) everything works perfectly. Yet when I publish it and run it live the reports will not display correctly, I get a message saying Failed to render page.

 

I looked this error up in the Crystal Reports website and the fixes they have there refer to giving the windows "aspnet" user full access rights to certain folders on the machine. I would be happy to do this, however there is no aspnet user in Windows Server 2008.

 

I installed the server and set its role to be Web Server. I also checked all the options regarding ASP.Net and IIS 6 compatibility. Yet there is no ASPNET username anywhere.

 

Did I set the server up wrong? or do I have to do something within ASP.Net to  create this user and then give it rights? 


Need help with setting up SQL Server 2008 for role/user management

  

I've posted my problem in a few other categories.  Sorry for that.  I didn't see the security category until now.

I'm trying to use ASP.NET's built-in role/user management for a couple ASP.NET MVC 2 sites.  I'm using the MVCMusicStore as my guide.  I click on the Configuration Tool as the tutorial suggests, which launches the site administration page.  Unfortunately when I click on the security tab, I get an error that it cannot connect to the data store.  Attempting to test the AspNetSqlProvider gives me a similar error.

I've run the aspnet_regsql program as the error message suggested, and I have a role/user management database ready to go on the server.  The proper tables are constructed, and all that.  The configuration tool still doesn't see the data store.

As a test, I ran the MVCMusicStore demo in the debugger.  Its role/user management functionality worked.  I was able to add myself as a customer, and add/remove items from my cart with no errors or exceptions.  However, when I attempted to access its security tab in the configuration tool, I received the same error as I get for my own project.  How is it possible that role/user management works, but the configuration tool cannot change its settings?

Any suggestions on how I can get the configuration t

Having problems installing/setting up SQL Server 2008 Express/R2 for ASP.NET user management on deve

  
I'm a bit new to installing and configuring, well, just about anything regarding databases, so please bear with me. I'm currently building a couple of ASP.NET MVC 2 sites.  I'm following along with the MVCMusicStore tutorial, and am trying to set up user management using ASP.NET's configuration tool.  The problem is that ASP.NET can't 'see' the database. I have SQL Server 2008 installed on my laptop.  It's the version which is available on the Web Platform Installer (I believe it's Express).  When I try running the aspnet_sqlreg.exe program to set up my user management database, it can't tell that I'm running any version of SQL Server at all.  I get an exception about 'named pipes', and some other things I don't understand at the moment. Obviously, this is all a bit over my head.  Is there an idiot proof guide or tutorial available that describes, step-by-step, how to: 1. Install SQL Server 2008 on a development PC 2. How to configure it to be used by Visual Studio 2010 Professional 3. How to properly upload/modify the db on the production server with what's on the development PC Essentially, all the things that the ASP.NET tutorials gloss over.  Any suggestions, links to documentation, or other nudges in the right direction would be immensely appreciated.

Server Collation SQL_Latin1_General_CP1_CI_AS versus Latin1_General_CI_AS

  
Hi,Why does SQL Server 2005 sometimes install with a server collation of :Server Collation SQL_Latin1_General_CP1_CI_AS and other times a server collation of :Latin1_General_CI_ASWhat causes this ???What is the difference between the two ?Are there any problems restoring databases from one to the other ? Do the two different collations cause problems with applications ?

Can not connect to user instance of SQL Server 2008 Express with Advance Services in Visual Studio 2

  
I used SQL Server 2008 Express with Advanced Services (not SQL Server 2008 Express) in Visual Studio 2010 because it is the fast and easy way to developed and managed data-driven applications with powerful built-in reporting and full-text search functionality. I received Sql Server 2008 Service Pack 1 with Windows Update. It's ok if I connect to instance of SQL Server 2008 Express with Advance Services (with SP1) through SQL Server Management, but if I try to attach a database in Server Explorer (Visual Studio 2010), I get the following error: "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed." I'm stuck at this point. Is there a fix or a workaround? I've already tried deleting the 'C:\Users\Administrtor\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS' directory as mentioned in other posts, with no luck. The error.log at 'C:\Users\Administrator\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\error.log' has the following relevant entries: 2010-09-01 14:44:10.47 Server      Error: 17156, Severity: 16, State: 1. 2010-09-01 14:44:10.47 Server      initeventlog: Could not initiate the EventLog Service for the key '3677F6D3-17C7-4F', last error code is 2. 2010-09-01 14:44:10.47 Server &

Best practice for user defined error messages in SQL Server 2005/2008 --Need help

  
My requirement is to setup a standard practice for our team in handling DB erros and also  user defined messages. We are handling exceptions using Raiseerror method with in Try and Catch blocks.  For user defined message, we are using Sp_addmessage to add our application specific error/warning messages with error codess>80000.  Ex: 80001 | Company name already exists. please check. Here is the sample code ********************************************************************* CREATE PROCEDURE [dbo].[usp_CompanyProfile_insert]  -- Add the parameters for the stored procedure here  (@company_name VARCHAR(50),  @company_code VARCHAR(3),  @user_id INT  ) AS BEGIN  -- SET NOCOUNT ON added to prevent extra result sets from  -- interfering with SELECT statements.  SET NOCOUNT ON;  BEGIN TRY -- Begin Try Block      -- Validate company name   IF EXISTS (SELECT company_id FROM companyprofile WHERE company_name = @company_name)    RAISERROR(90021, 16, 1)      INSERT INTO companyprofile (company_name,       company_code,       createdby)   VALUES(@company_name,     @company_code,     @user_id)    END TRY  -- End Try Block &

Failed to generate a user instance of SQL Server 2008 Express with Advance Services in Visual Studio

  
I used SQL Server 2008 Express with Advanced Services (not SQL Server 2008 Express) in Visual Studio 2010 because it is the fast and easy way to developed and managed data-driven applications with powerful built-in reporting and full-text search functionality. I received Sql Server 2008 Service Pack 1 with Windows Update. It's ok if I connect to instance of SQL Server 2008 Express with Advance Services (with SP1) through SQL Server Management, but if I try to attach a database in Server Explorer (Visual Studio 2010), I get the following error: "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed." I'm stuck at this point. Is there a fix or a workaround? I've already tried deleting the 'C:\Users\Administrtor\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS' directory as mentioned in other posts, with no luck. The error.log at 'C:\Users\Administrator\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\error.log' has the following relevant entries: 2010-09-01 14:44:10.47 Server      Error: 17156, Severity: 16, State: 1. 2010-09-01 14:44:10.47 Server      initeventlog: Could not initiate the EventLog Service for the key '3677F6D3-17C7-4F', last error code is 2. 2010-09-01 14:44:10.47 Server &

Easy install package vs MsSQL server 2008

  
Is there anything i wont be able to do if i use SQL Server Express Database from the downlod install package offered on asp.net? Ive used MSSQL server 2008 and 2005, but to be honest i think it might be overkill cause im not very familiar with those applications. I just need the database for creating websites. Will i be able to remote connect to my webbhotel host and remotely modify the database on the remote server?Now i have a mess, i have installed both MsSQL 2005 and 2008 and im not even sure wich database is created with what. I would love to just have one simple setup, but im not sure if i can manage to migrate the databases to the new computer with this easy install package.The easy install package contains: ASP.NET and ASP.NET MVC Visual Studio 2010 Express Development Tool SQL Server Express DatabaseThanks for your time!

MSSQL Server 2008 Workgroups install to Win 7 - Compatibility issues

  
I attempted to install MSSQL Server 2008 Workgroups from the DVD to a Win 7 workstation.  Error "This Program has known compatibility issues.  The directions are to download the MSSQL Server Service Pack.  I downloaded SP 1 and SP 2.  Neither SP will install.  The DVD install also has a run option displayed along with the Compatibility message.  I select "Run" and it appeared to install, with Start\Programs\Microsoft SQL Server 2008\Configuration Tools\ SQL Server Installation Center 64bit.  However, running the 64bit installation process results in no change.  My question is how to install the MSSQL Server 2008 Workgroups on the Win 7 machine.  I would appreciate your thoughts.

How to change SQL server 2008 collate after installation

  
Hi, On windows sever 2008 I've installed SQL server 2008 RC0.At the time of installation, I'd specified the collation as LATIN1_GENERAL_BIN, and now I want to change this collation to LATIN1_GENERAL_CI_AS.Please note that I don't want to change the collation of specific db but need to change the sql server 2008 server's settings.How to accomplish this requirement with minimum efforts?Thanks
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend