Hi,
I noticed that when I installed SQL2000,with "typical install",
the default, the following will be used:
SQL collation (dictionary order, case-insensitive, assent sensitive)
sp_helpsort will give
SQL_Latin1_General_CP1_CI_AI
Is there any difference if I choose the "custom install", then and choose
the windows locale which gives the result of sp_helpsort:
Latin1_General_CI_AI
SQL_Latin1_General_CP1_CI_AI is supposed for backward compatibilty, so are
they actually equivalent ??
Any impact or difference we have to be aware of ??
Anyone knows..Thx
Hi there. I'm trying to create a copy of a remote database for development purposes. I am useing MySQL, the remote database is in MSSQL. The create table commands contain a collation titled
quote:SQL_Latin1_General_CP1_CI_AS
which MySQL doesn't recognise. The three most similar collations I can find in MySQL are: quote: 'latin1_general_ci', 'latin2_general_ci' 'latin7_general_ci'
Does anyone know the difference between these collations? And whether any of them is equivalent to the MSSQL code I have to implement?
I want to create a database where the table names / column names / SP namesare NOTcase sensitive but where the data in the tables is, so that I canbuild a unique index where 'test' and TEST' is accepted as different.I have tried Installing SQL with a Collation designator with the CaseSensitive option checked- this caused all sp names / column names / table names to be casesensitive - not what I want.I have also tried installing SQL and selecting a SQL Collation and pickingan option from the drop down list. - again this cause everything to be casesensitive - not desiredDo I have to install SQL with a non case sensitive collation, then set eachcolumn in the table to be case sensitive? What if any are the problems I amlikely to come across?ThanksSteve
I have looked thru the forum but have a couple of questions: 1) data base was created with case insensitive collation 2) all the tables were then create (72 tables) and by default got the CI collation on all columns 3) lots of data was added 2GB 4) discovered mistake and altered the database to have case sensitive collation 5)..... how to change all the collations for all the columns without doing them all manually can i backup the database and change some settings and restore it? export all the data, drop and recreate tables and import data? ????
Hi,We have around 150 databases as case sensitive, and we are planning tochange it to case insensitive. Each database has around 180 tables, Ihave changed the collation on DB, but changing collation manually oneach column is a daunting process. Is there any script or tool whichcan assist in doing this.Appreciate your help.ThanksSAI
I have developed a tool to allow project developers to easily re-create the entire schema for our base product. The current issue involves setting the correct collation for the customers' region. Our brother company in Germany uses the same db creation tool and scripts, and we here in the US also have customers in South America. My ultimate question is "what subset of collation names would be necessary to provide the project developer?" I could query the database to get all the collation names, but I think it was around 1000 names. Can I query to get a smaller subset of the most relevant collation names?
Hello. Here a two different problems that occur one and a while when I try to import a textfile to SQL Server 2005.
I have a flat file connection to a csv-file that originally is a export from an AS4000 DB2 database This csv-file is defined as a variable length file Why do SSIS automatically interpret the length of each column as varchar(50)? It does not matter if a define the same file as a fixed lenght file. The problem is that I will get a warning that information in columns will be truncated. I would like to do a direct export to the SQL Server 2005 table with shorter varchar fields. I can solve this by using the task for transforming data types but this only works on the text fields. This task cannot transform a string to a decimal or an integer column in the SQL Server 2005 table. Is there no other way than having a staging table between the text file and the SSIS-data pipe? I also get a lot of collation or code page errors even if we set the receiving columns to nvarchar and nchar. Is there any good article on this subject? Code page errors
Last question. Is there parameter support in the data reader source connection?
When I try to execute a package I get this following error. I have a bunch of similar packages which runs fine on the same source(sybase) and destination(sqlserver 2005), just different tables. Only few of them fails and all of them have the same error of "Unable to resolve column level collations. Bulk-copy cannot continue". I checked for the dtatatype and length between source and destination, both are same. The user have all the required rights on the objects in both source and destination.
Error at Data Flow Task For Test1 - test_tbl_job [OLE DB Destination [16]]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to resolve column level collations. Bulk-copy cannot continue.".
Error at Data Flow Task For Test1 - test_tbl_job [OLE DB Destination [16]]: Failed to open a fastload rowset for "testdb..tbl2". Check that the object exists in the database.
On further trial and error I found that if I remove the fast load option, it works without glitch.
Is there any way to update the system tables directly, to alter the collations of the columns in the user db's?
I've tried the script below:
UPDATE Syscolumns SET collation = 'SQL_Latin1_General_CP1_CS_AS' WHERE name = '<AddrCode>' AND id = object_id('<Compliance>')
but, I get the following error message:
Server: Msg 271, Level 16, State 1, Line 1 Column 'collation' cannot be modified because it is a computed column.
Can you please help me! I need to do thousands of these, and most of them has constraints on, so my script I generated to do the ALTER TABLE.... ALTER COLUMN does not suffice.
I'm recreating many of my DBA scripts that no longer work in 2005 due to the rework of system tables. It's a risk I lived with knowing that someday the system tables would change. I'm now encountering collation problems, which I do not understand. I know how to fix the problem, but I don't know why the collation issues exist in the first place.
Run the following command.
Select * From sys.all_objects a JOIN master..spt_values b on a.type = b.type
You will receive the following error.
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS_KS_WS" in the equal to operation.
Now run sp_help 'sys.all_objects' and look at the collation defintion for columns "type" and "type_desc". In my environment they have a collation of Latin1_General_CI_AS_KS_WS. This is different then the overall default collation of SQL_Latin1_General_CP1_CI_AS, thus causing the error.
My question is why did Microsoft need to make this collation different for these columns?
When I try to execute a package I get this following error. I have a bunch of similar packages which runs fine on the same source(sybase) and destination(sqlserver 2005), just different tables. Only few of them fails and all of them have the same error of "Unable to resolve column level collations. Bulk-copy cannot continue". I checked for the dtatatype and length between source and destination, both are same. The user have all the required rights on the objects in both source and destination.
Error at Data Flow Task For Test1 - test_tbl_job [OLE DB Destination [16]]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to resolve column level collations. Bulk-copy cannot continue.". Error at Data Flow Task For Test1 - test_tbl_job [OLE DB Destination [16]]: Failed to open a fastload rowset for "testdb..tbl2". Check that the object exists in the database.
i have sql2000 & sql2005 on the same machine. I am unable to register my localhost in sql2000, get an access denied error. How can I make my localhost use sql2000 database?
i am in the process of Migrating SQL 2000 to my new SQL2000 server i want to know the what would the best way for me to migrate one SQL server to another SQL server on the same network and rename the new server to the old server and bring it up for use in our ecommerce website.
Hi,I have made a web application using SQL Server 2005 Express with a few admin pages that require login. I used the SQL Server Managment Studio Express to setup users and groups and it works fine. But the web server has SQL Server 2000 and all I get is a user id and password to access the database. It seems I cannot use the Login control I put on my "login.aspx" page becuase it uses the integrated authentication which chokes on 2000. I am afraid I wonlt be able to use some of the 2.0 features such as profiles, ... Is this the way 2.0 works with SQL Server 2000? Should I ditch the ISP if they only give me a single access to database with no provisions to create users. (I was told I had to create a "Users" table and manually assign ids and passwords and that I can only use the user id and password they supplied to setup the connection string in the script.)I appreciate your help.
I am developing a Crystal Reports App in VS2005 (VB). I was originally working on an XP machine with MSDE and stored procedures where it worked fine. I then transferred the development to a Win2003 machine with SQL2000 and am now getting the following error; Failed to open a rowset. Description: 'Get_Calls_By_MLO_Date' expects parameter '@DT1' which was not supplied. Plus some other error detail. Here is the SP. CREATE PROCEDURE Get_Calls_By_MLO_By_Date @DT1 datetime, @DT2 datetimeASSELECT MLO, CallNo, DT, Type FROM ActionsWHERE dt >= @DT1 AND dt <= @DT2 AND ActionID=1 ORDER BY MLO, DTGO If I transfer everything back to the XP machine it works fine. Any ideas? Thanks Terry.
How to lock a Row in SQL2000 so that nobody can select that row. I applied ROWLOCK, but i am not finding the way. My query is "SELECT * FROM tablename WITH (ROWLOCK)" Is this the correct way to write locks. I would be thankful if u help me
I am exporting data to an Excel file via a scheduled DTS package. I need to be able to either overwrite the existing Excel file or delete it. I haven't found a way to do this yet. Any help, comments, or direction will be appreciated. TIA Paul
We're beginning to migrate our 6.5 DB's to SQL2000. A question came up regarding naming conventions for stored procedures. In BOL it says that for SQL2000:
"Stored procedures with the prefix sp_ are first looked up in master. If a user-defined stored procedure has the same name as a system-supplied stored procedure residing in master, SQL Server always finds the system-supplied stored procedure. "
Behavior in 6.5 was the opposite: SQL Server searched the current database followed by a search in master.
We started several months ago renaming user stored procedures to "usp_XXX", but we still have many non-system sp's that still use "sp_". I'm looking for opinions of whether we should bite the bullet now and rename all our non-system sp's at this time; prior to moving our production environment to SQL2000. Will we see a performance gain since SQL will not have to hit master first before going to the current DB?
When bcp'ing in data into an sql 6.5 table that is NOT nullable, records in the text file that contain nulls are ommited, but valid records without nulls are inserted. However, using exactly the same table structure and files on a newly installed sql 2000 server (sp2), as soon as the first invalid record in the text file (i.e. with a null value)is encountered, bcp'ing is terminated and NO records are inserted. I can't find any option in bcp/sql 2000 to allow me to carry out a load as before. The help files state that the default no. of invalid records allowed is 10 (both versions sql), and explicitly setting the -m option of bcp to 10 still doesn't work. Most of the data i receive has at least one duff record Is there an option in sql 2000 that allows me to have x number of duff records inserted before a full rollsback occurs?
I can't find anything in technet, msdn or books online.
Does SQL 2000 standard edition support replication if it would be configured as a subscriber. Is SQL 2000 Enterprise Edition required for a Publisher and Distributor or can you get away with standard edition. In a web site environment how many CALs are needed when users access the database via webservers and middleware.
I am experimenting with the FOR XML AUTO clause in SQL2000, but instead of nicely structured XML I get one continuous string oftags that can't be parsed by IE5.5
The BOL seem to indicate just running the query in Query Analyser will produce nicely formatted XML output - can anybody tell me what is wrong?
I need some strong points for the management to convince them to upgrade to sql2000 now than waiting for 6 months. We are currently in process of developing this new product. All development is on sql7. I want to upgrade our database to sql2000 now rather than waiting after the release which might be in another 3 months.
I crashed my HP portable that has SQL2000 ENT installed and I re-installed XP-Pro +SP1 and when I install SQL2000 ENT it refused to install saying ENT cannot install in this O/S. Did I miss anything ?
I went and checked another XP-Pro W/S and SQL2000 is intalled OK.
