Hi, i'm having a problem with my database. When i created it i specifie the collate sql_latin1_general_cp1_ci_as but now that i'm working on my domain server, the server only acepts databases with the collate latin1_general_ci_as so i tried to change my current (on my localhost) collate but i'm always getting this error:
Msg 5030, Level 16, State 2, Line 2
The database could not be exclusively locked to perform the operation.
Msg 5072, Level 16, State 1, Line 2
ALTER DATABASE failed. The default collation of database 'databasexpto' cannot be set to Latin1_General_CI_AS.
We're upgrading to SQL Server 2005, and we've restored databases from SQL Server 2000 to the development server (SQL Server 2005). Part of what we need to do is alter the database collation to SQL_Latin1_General_CP1_CI_AS; our SQL Server 2000 server is case-sensitive.
My initial attempts to alter the database;
alter database RGDWDEV collate SQL_Latin1_General_CP1_CI_AS
failed because of the presence of computed columns and user functions. So, I wrote scripts to save the definitions of the computed columns and functions, and wrote scripts to drop them. Now that they are gone, I expected to be able to successfully execute the above 'alter database' statement, but I am now getting this error;
Msg 1505, Level 16, State 1, Line 1 CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'sys.sysschobjs' and index name 'nc1'. The duplicate key value is (0, 1, DISB_ADJ). Msg 5072, Level 16, State 1, Line 1 ALTER DATABASE failed. The default collation of database 'RGDWDEV' cannot be set to SQL_Latin1_General_CP1_CI_AS.
I do not have a backup of the model database and have had to rebuild SQL Server 2000 once already. All my databases except model and tempdb have the collation SQL_Latin1_General_CP1_CI_AS those two have Latin1_General_CI_AS.
During SQL rebuild I've restored over master and msdb and they work fine. Only came across problem with stored procedure and I'm lost as to how to alter model without a backup of it.
Any easy way to change model collation? The command listed in subject fails as it is a system table.
Is it as simple as going into single user mode and trying that command again?
Have an old DB (6.5) Upgrade to (8.0). And when i make a script from the SQL.8 to the backup server i gett Collate Errors. So i wonder if there are someway to script the Table without the Collate´s or if i can make som settings on the Backup server so it will accept Collate in the script..
I am new to Stored Procedure, so I want to find out what is COLLATE Latin1_General_BIN means?
For example:
Declare @Var_Tbl Table (CUSTID INT NOT NULL, FIRSTNAME VARCHAR(20) COLLATE Latin1_General_BIN NOT NULL, CUSTOMER VARCHAR(9) COLLATE Latin1_General_BIN NOT NULL )
I am attempting an import of an SQL DB from a local MSDE SQL Server to an SQLServer 2000. I used the Web Data Administrator to create an export file, and then I wanted to perform an import to the actual server. I get the error "Syntax error near 'COLLATE'". I don't know much about syntax when it comes to this. Here is a snippet from there. I am new to this, so I hope someone out there can help me out. CREATE DATABASE [NewDB] ON (NAME = N'NewDB', FILENAME = N'CORRECTFILEPATH' , SIZE = 3, FILEGROWTH = 10%) LOG ON (NAME = N'NewDB_log', FILENAME = N'CORRECTFILEPATH' , SIZE = 1, FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS GO All the "N" don't look like they belong, but they have been added throughout the entire SQL document.
i have a sp that when i add to a CS database it doesnt give me an error but when i add to a CI database it errors out. what can i do to it to make it not error on the CI situtation?
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
-- Accounts Payable ALTER PROCEDURE [dbo].[isp_ap_calc_apt_totals] @p_comp char(2), @p_vend char(6), @p_asofdate char(8) as
if (@p_asofdate <= '00000000') begin set @p_asofdate = '99999999' end
delete from XAPAPTTOT where xapt_comp = @p_comp and xapt_vend = @p_vend and xapt_asof_date = @p_asofdate
insert into XAPAPTTOT select apph_comp, apph_vend, apph_type, apph_id, @p_asofdate, sum(apph_paymnts), sum(apph_discts), sum(apph_adjts), count(apph_paymnts), sum(apph_paymnts)+ sum(apph_discts) + sum(apph_adjts) + (select apt_gross from APTRANF where apt_comp = @p_comp and apt_vend = @p_vend and apt_type = apph_type and apt_id = apph_id), 0, max(str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0')) from APPHISTF where apph_comp = @p_comp and apph_vend = @p_vend and str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') <= @p_asofdate group by apph_comp, apph_vend, apph_type, apph_id
update XAPAPTTOT set xapt_last_payck = (select max(apph_payck) from APPHISTF where apph_comp = xapt_comp and apph_vend = xapt_vend and apph_type = xapt_type and apph_id = xapt_id and str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') = xapt_last_paydt ) where xapt_comp = @p_comp and xapt_vend = @p_vend and xapt_asof_date = @p_asofdate
Hi, I have a SQL Server for a SAP database with the collationSQL_Latin1_General_CP850_BIN. When I connect to that server (or, inthis example, to another server with the SQL_Latin1_General_CP850_BINcollation) and execute a select, the accents seem weird:select t.TEXTfrom [GC-SAP02].P01.p01.AGR_TEXTS twhere t.MANDT = '300'and t.SPRAS = 'E'and t.AGR_NAME = 'ZCD_GEST_DEUDA_PATENTE_SD'TEXT-----------------------------------Gesti¾n de deudas patentes en SD UP(1 row(s) affected)But if I connect to a "normal" SQL Server with aSQL_Latin1_General_CP1_CI_AS collation, and execute the same selectthru a linked server:TEXT-----------------------------------Gestión de deudas patentes en SD UP(1 row(s) affected)I need to perform the select connected to theSQL_Latin1_General_CP850_BIN and get the results as I were connected tothe SQL_Latin1_General_CP1_CI_AS server. I tried with cast and collate,but I can't get it to work:select t.TEXT collate SQL_Latin1_General_CP1_CI_ASfrom [GC-SAP02].P01.p01.AGR_TEXTS twhere t.MANDT = '300'and t.SPRAS = 'E'and t.AGR_NAME = 'ZCD_GEST_DEUDA_PATENTE_SD'Any ideas??? Thanks in advance for your help !!!Manuel Daponte
I created DB Generator and want to enable user choose collate which desired. So I would like to retreive all collate in SQL Server to show for user can see all available collate.
Hi,Good morning to All. I have some doubt.I cannot do anything without knowing the reason why we are doing that particular thing. Any way, my doubt is: While creating table, in some places I see COLLATE clause. For example, recently I have seen this. CREATE TABLE dbo.OrderDetails( OrderDetailID INT IDENTITY(1,1) NOT NULL, ItemNumber VARCHAR(20) COLLATE SQL_Latin1_General_CP1_CI_As NULL, Qty INT NULL)ON [PRIMARY] In the above table, why the Collate clause for ItemNumber column? What is the advantage with that clause?I have seen sql server help for this. I have found 1011 results for the query SELECT * FROM fn_helpcollations().Then I searched for SQL_Latin related collations using: SELECT * FROM fn_helpcollations() where name like 'SQL_Latin%'I have found 31 results.How can I know which clause i have to use for which type of query? Can anybody please tell answers for all these doubts? Thanks in advance. Regards,Ashok kumar.
I am getting an error inserting a simple text file into a SQL2000 database table. The text file is one column, comma delimited, and the DTS package to insert it into a table fails citing an error executing a 'COLLATE' statement. Thanks for the help!
Hi, I'm trying to do a search over a table, the problem is that the default collate applied by this transofrmation is case sensitive... i need a non case sensitive collate. If i edit the SQL Statement to set another Case sensitive collate manually inside the statement, the problem is that if a field (wich is involved in search transformation ) has a null value, the transformation doesnt find any record (it doesnt happens if a dont introduce manually the collate in the statement, null values dont rise any error, and the transofrmation works fine).
Tried a few ways and I can ge this to work at the end in then WHEN part. Just struggling to put this together to be accepted as a CASE WHEN statement, probably missing the obvious.
Case when Postcode like '%[abcdefghijklmnopqrstuvwxyz%]' then 'Lowercase Postcode' else 'Postcode OK' end as [DQPostcode]
collate Latin1_General_CS_AS
Simple terms looking for all instances of Lowercase characters in the Postcode field
I build a asp.net web site on my laptop and accessting data with my vb.net code from SQL server 2005 which is installed on windows 2003 R2 server. All works fine.
but when I hosted this website on the IIS server which is physically the same Windows 2003 Server R2 with SQL Server 2005, the sql queries are not executing well and not getting the result. After some R&D What I found is that the Column Names CASE in the SQL Database and in my SQL Query is not matching. I find that SQL server 2005 collate is allready set to SQL_Latin1_General_CP1_CI_AS. I have lots of pages with lots of sql queries and near about 500 SQL Databases, and its quiet tuff to match all columns CASING.
1. How can I change the collate of windows 2003 server R2?
or
2. How can I run my wesite on this Server properly?
3. What is actually going on (website working on Windows XP but not working well on Windows 2003 server).
I have a query however i am getting the following error message “Msg 447, Level 16, State 0, Line 1 Expression type numeric is invalid for COLLATE clause.“
This is my query
SELECT sjo.ID, sjo.MID, sjo.Trade_Association_Name, da1.Account_Name As Trade_Association_Name, substring(do.[MM-CHN-AGENT],2,12) as Mass_Agent_Chain_No,
[Code] ....
And Dan.Stg_Jitter_Opp2 table consists of the following
ColumnNameData Type Idvarchar(50) Mid numeric(18, 0) RecordTypeIDvarchar(50) Trade_Association_Name varchar(50)
And [FDMS].[SalesForce].[DailyAccounts]table consists of the following
ColumnNameData Type Idint Account_Idvarchar(18) account_Name varchar(150) mid_externalvarchar(15) Mid_internalvarchar(15)
What is 'COLLATE SQL_Latin1_General_CP1_CI_AS'? I am new to SQLServer, and couldn't find much information on the Web. Also, I am trying to understand user defined data types. For example,
In the following example, what is '[Price_DT]' data type? and how would it be referenced at the time of 'INSERT'.
CREATE TABLE [dbo].[Dist_Orders_Master_Index] ( [SubTotal] [Price_DT] NOT NULL , [Tax] [Price_DT] NOT NULL ) ON [PRIMARY]
How to maintain Database User Permissions when copying the Database from One SQL Server to another(Either through backups or sp_detach). The reason is the login sid is different in the target server and as a result the database user is not able to map to the login existing in the target server. The only way I can correct this is through dropping and recreating the user's again and assign the permissions, or change the system catalog - sysusers to remap the login to the user in the database. I do not wish to use the sp_addalias as it is available only for backward compatibility. Is there a better way of doing this ?
From the http://msdn.microsoft.com/en-us/library/bb384469.aspx (Walkthrough: Creating Stored Procedures for the Northwind Customers Table, I copied the following sql code:
--UpdateSPforNWcustomersTable.sql--
USE NORTHWIND
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SelectCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.[SelectCustomers]
GO
CREATE PROCEDURE dbo.[SelectCustomers]
AS
SET NOCOUNT ON;
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM dbo.Customers
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertCustomers' AND user_name(uid) = 'dbo')
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)
GO
==================================================================================== I executed the above code in my SQL Server Management Studio Express (SSMSE) and I got the following error messages:
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'NORTHWIND'. No entry found with that name.
Make sure that the name is entered correctly.
=============================================================================================================== I know I recreated the NORTHWIND Database from a different Database before and I did not do anything for the entry in sysdatabases. How can I change the entry in sysdatabases for database 'NORTHWIND' now? Please help and advise.
Hi, i have database in sql server called search, but today, i found it is changed to search (Single user), I don't know who did it. How can I change to mutilple users, also how to change the name back to search. Thanks.
This is my first posting to this forum. I'm trying to I'm trying to change the name of my database from ASPNET.mdf to CulturedStar.mdf. I need to change the physical name and logical name.
I'm working with Microsoft SQL Server Management Studio Express.
It seems the change occurs inside the dynamic portion because if I SELECT a table within that database, it works. For example the following works but it does not change the default database to My_db.
SET @DynSQL = 'USE [' + @db_name + ']'+' SELECT * FROM Mytable'
How to get the ddl of every change in the database Includes changes in the table, column, constraints's That is a new table added, table drop table name changed, column type change column added, primary keys , foreign key, check constraints e.t.c
I am not looking for the changes in triggers. stored procedures, functions.
Hello,I'm using tableadapters in VWD 2005 Express to link our ASP.NET application to a SQL Server 2000 database. Initially, I used Database Explorer to drop tables into the DataSet object, which picks up the owner of the tables from the database.Recently, we had to change the owner of all objects in the database, including tables and stored procedures. When we run our application it chokes on stored procedures because the owner of the SP's has changed. My question is: how can I update tables and tableadapters in the DataSet to reflect the change in owner, without having to recreate everything in DataSet?
Hi,I just transferred my website and database (SQL 2000) to a new host who's SQL Manager doesn't support the previous username I had for the database.The previous owner of database tables etc was Database_master and now I want to change it to just Master.Please advise! Someone else designed the website for me, and honestly I have just some basic knowledge of databases and such.I would appreciate help and if possible with detailed steps.Many thanks in advance,Isje
