Hello!
My client has a need to be able to store Japanese characters in their
PeopleSoft database. So we need to change the character set from from
Latin1_General (1252) to Japanese character set (932) on SQL Server
2000 Enterprise. I have 2 questions:
1) I would like to know if in SQL Server, the character set is machine
specific or is it defined at the database instance level?
2) Can multiple installs of SQL Server co-exist on a single server with
different default character sets?
Hi,I come from the "dark side" php/mysql and there often problems withcharacter sets (utf-8, latin...) and storing data in datebase.Exists in the world of dot.net and ms-sql-server similiar problems?To precise: I have to store xml-data in database. Maybe its better toencode (like base64) the strings?Perhaps there are some links to read?Thanks.klaus.
I have a need to store records in two different languages. Has anyone done this and what were the requirements. Can you do this within the same database, table?
I have a table like this below and it doesn't only contain English Names but it also contain Chinese Name. CREATE TABLE Names (FirstName NVARCHAR (50), LastName NVARCHAR (50)); I tried to view the column using SQL Query Analyzer, It didn't display Chinese Character. I know that SQL Server 2005 is using UCS-2 Encoding and Chinese Character uses Double Byte Character Set (DBCS) Encoding. I want to read the FirstName and LastName columns and display in Window Form Data Grid and ASP.NET Grid View. I tried to use this code below and it didn't work. It convert some of the English Name to Chinese Character and it display the chinese character and some still in the original unreadable characters. Does anybody know how to read those character from SQL Table and display the correct Chinese Character without converting the English Name into Chinese also? Thanks
int codePage = 950; StringBuilder message = new StringBuilder(); Encoding targetEncoding = Encoding.GetEncoding(codePage); byte[] encodedChars= targetEncoding.GetBytes(str); . message.AppendLine("Byte representation of '" + str + "' in Code Page '" + codePage + "':"); for (int i = 0; i < encodedChars.Length; i++) { message.Append("Byte " + i + ": " + encodedChars); }
message.AppendLine(" RESULT : " + System.Text.Encoding.Unicode.GetString(encodedChars)); Console.Writeline(message.ToString());
Hello, I am using existing code, which I am trying to convert from using MS Access to SQL Server 2005... The data set works fine with MS Access database, however when executing with SQL Server 2005 as data source, it generates the following error: "..The data types ntext and nvarchar are incompatible in the equal to operator..." in this line: count = adapter.Update(dataset); Not sure what should I look for since data sets are new to me.. Where should I check to fix this problem? I have noticed that the table has two columns with nvarchar...
ColA ColB ----- ----- 21 A 22 A 23 A 24 B 25 B 26 D
What I want is to be able to identify a set sequence (1,2,3) based upon ColB such that I'd get the following result:
ColA ColB ColC ----- ----- ----- 21 A 1 22 A 1 23 A 1 24 B 2 25 B 2 26 D 3
I know that I should be able to get it using ROW_NUMBER() OVER (PARTITION BY ColB ORDER BY ColA), but instead of getting the sequence (1,1,1,2,2,3) I get (1,2,3,1,2,1). Using DENSE_RANK gave me the same results.
A DB2 store procedure returns two data sets, when executed from SSMS, using linked server. Do we have any simple way to save the two data sets in two different tables ?
so async cursor population is supposed to create the cursor and return the cursor id quickly, while the server works on async populating the results. For a keyset-driven cursor, SQL Server stores the key sets in tempdb, which it then uses to fetch data for cursor results. Anyway, this works fine for smaller tables, but I'm finding for large result sets, the async cursor population is very slow and indeed seems to approximate synchronous time. The wait stat I get while it is running (supposedly asynchronously) is TRANSACTION_MUTEX.
Example: --enable async cursor exec dbo.sp_configure 'cursor threshold', 0; reconfigure; declare @cursor int, @stmt nvarchar(max), @scrollopt int, @ccopt int, @rowcount int; --example of giant result set set @stmt = 'select * from sys.all_objects o1, sys.all_objects o1';
[code]...
Note that using the SQL "select * from sys.all_objects o1" is much faster than "select * from sys.all_objects o1, sys.all_objects o2". However, if cursor population is async, I'd expect the time to return a cursor id to be similar between the two.
I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:
SetIdItemCode 1A 1B 24 28 26 310 312 410
[code]....
You can see that there are some sets that have the same members:
- 1 and 10 - 2 and 11 - 7, 8 & 9
What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.
I have a database column that stored the weblinks like the one below :
[URL] .....
I want to extract string "airfare" which comes after string "cname= " and before the string "&ccontent" .This is actually the value for cname. The entire string size can be of variable length.
I am using System:ata::Common:bDataAdapter::Fill method in my Visual Studio 2005 project to achieve copy database from an existing database on server. - Server name contains '-' character. I am getting exception on this method about 'Incorrect syntax near '-' ". Does it really matter with server instance name containing '-' character?
Hi, I am using DTS for importing a table from FileMaker Pro (a database application) to SQL Server 2000. The problem is that everything gets imported correctly but in some values, it shows a box type character for a blank space. It does not happen for all the blank spaces. Because of this, that value is cannot be checked in the WHERE clause until I use a LIKE and % for that blank space. I copied the value and pasted in Notepad, it only shows a blank space. What is this character? Can someone help... Thanks, bullpit
Not sure if this is the right forums to ask this question.. I have a website in which i have a text box that has multiline = true... So suppose if if the user enter absbsbcjd and then hits enter and then again enter jfkdjf in my database i been stored as abscbabc twoboxes (new line) and then jfdkf and then sometime later i am taking that plan name and sending it as a subject to an email but since it has a new line character in that field.. i get an error..
so how can escape the new line character.. thanks Karen
I am developing a simple ASP based form, that stores user info in MS SQL server. I have created a table in the SQL server to store the data and defined the body field with this line: `body char(255)`. The problem is that if the user inputs a string longer then 255 characters it gets choped off. How would you suggest solving this problem? Should I use `text` datatype instead?? Any comments are very appreciated !!!
create table data_set (id int primary key, col1 varchar(10)) go insert into data_set values (1,'a'), (2,'b'),(3,'c'),(4,'d'),(5,'a'),(6,'b'),(7,'e'),(8,'f'),(9,'a'),(10,'a') select * from data_set
I tried this below
Declare @child_ids int @col_val varchar @count int
select @child_ids, @col_val, @count, count(col1) as records from data_set group by col1 order by col1
I am getting a tab character at the end of my query fields, been trying various things to fix, such as using the replace function below but still i get the tabs!
select CAST(REPLACE(NAMEALIAS,CHAR(9),'')AS CHAR(40)) + ',' as PRODNAME, CAST(REPLACE(ISNULL(GLOBALTRADEITEMNUMBER,0),CHAR(9),'')AS CHAR(18))as EANNO,LTRIM(cast(ISNULL(GLOBALTRADEITEMNUMBER,0) as char(18))) as KONSEAN ,LTRIM(CAST(I.ITEMID AS CHAR(8))) AS PRODCODE,'00' from INVENTTABLE I LEFT JOIN INVENTITEMGROUPITEM IG ON I.ITEMID = IG.ITEMID
I'm creating a table using a XML string and a stored procedure to the SQL Server. I'm having problems in just one field, it is a nvarchar(10) type. This field's string XML Schema is also nvharchar(10) type. It is called CodCanalPrice.
Today, the only values this field can assume are AA or BB or CC, (...), ZZ or (space)A, (space)B, (...), (space)C. Note that (space) is actually a space character. They all have 2 characters.
The XML data string of one data row is showed below:
When the statement is executed, SQL server removes the first space (so in this case it is updated to A without space in the table). The others values that don't have an space before like AA or BB are updated fine. All others fields are also updated correctly.
So far I tried to replace the first space for but SQL Server doesn't accept this tag.
I have a legacy table which name contains non-printable characters (CHAR(31), to be more specific). The non-printable character is beside a underscore, and I've discovered that the shortcut CTRL+SHIFT+_ shoots the CHAR(31) character (which means "US" - Unit Separator). The previous developer should've hit by mistake this combination, and created the table with this weird character on it.
When we issue a SELECT command against the table, it returns results. But when we try to issue any DDL to it (DROP, sp_rename, etc), it simply doesn't work.
Examples: DROP Table_Name; raises "Msg 15225 - No item by the name of 'Table_Name' could be found in the current database 'MyDB', given that @itemtype was input as '(null)'". sp_rename 'Table_Name', 'NewTableName'; raises "Msg 102 - Incorrect syntax near '_Name'".
I already duplicated the table with the correct name, and have corrected it on the referenced objects (SP's, Views etc). The remaining step is just dropping it from the database. when we copy+paste from SQL Server to Notepad++, it shows the hidden chacacter ("US") on the middle of the table name, beside the underscore.
Hi,I used SQL Server 2000 Personal Edition. I created a stored procedurehaving input parameters as smallint,tinyint,char,varchar andsmalldatetime. When I executed it by Query Analyzer, it's ok. But whenI executed it by ASP code that used ADODB.command, it showed error:Invalid character value for cast specification .Though SQL Server debugger, it showed:[Microsoft][ODBC SQL Server Driver]Invalid character value for castspecificationWhat did I do wrong? How do I fix this problem?TIAKrungPS: One of input parameters was smalldatetime, but I casting it in ASPas date as:Const adDate=7datetxt='2004/6/28'cmd.Parameters.Append cmd.CreateParameter("CoopId",adChar,adParamInput, 8, coopid)cmd.Parameters.Append cmd.CreateParameter("Addr",adVarChar,adParamInput, 100, addr)...cmd.Parameters.Append cmd.CreateParameter("Cdate",adDate,adParamInput, , datetxt)....cmd.execute
I had a situation that required me to set up SQL Server to do full text search against both English content and Chinese content. I am not sure if it's achievable in SQL server environment. Any help is appreciated.
I am trying to figure out how I can find the names of people who have a double occurrence or more of characters either in their first name or last name.
I'm using OpenRowSet to import about 30 columns from a csv file with 190 columns with a format file. Ultimately, I want to put this in an SSIS Package. I am receiving the following error when trying to import date and decimal info.
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 990, column 64 (TOTALSALES). There are several similar errors. I looked at this line and it is 17873.34 so I am not seeing the problem. Every value in the column is either 0 or a 2 digit decimal value. If I change the SQL Column and format file to to NVARCHAR, it imports fine.
The existing format file and SQL Column looks as follows. There are multiple errors referring to different columns and all of them seem to be valid decimals. I am having the same issue with date fields that exist in the csv as 20130521. If I bring it in as text, it is fine.
The SQL Column is defined as Decimal ((15,2), NULL))
I created a small csv file with representative decimal, date, integer and NVarchar fields and it imports into SQL fine as decimal and date info. The SQL Query used is pretty simple. Ultimately, I am planning to create a package that imports this data and joins to a production table based on values in the csv file. It will either update existing values in a Production Table or insert New Values
INSERT INTO Import.dbo.test1 SELECT * FROM OPENROWSET(BULK 'C:ShareImport.csv', FirstRow=2, FORMATFILE='C:ShareImport.xml' ) AS t1;
I am assuming there is bad data in the csv file but I'm not sure how to identify it as my test file seems to bring in date values with a format of 20140923 and 2 digit decimal values and that is what exists in the line numbers being referenced. I've not used OpenRow Set for this purpose before. The only workaround I've found is to bring it all in as text and create additional fields so I can cast or convert the date values which I'd rather not do as this process seems to work in my small sample file.
Create table DBInfo (Path varchar (500)) Insert into DBInfo values('/Data Sources') Insert into DBInfo values('/Data Sources/SALES') Insert into DBInfo values('/PRODUCTION') Insert into DBInfo values('/PRODUCTION/SERVICE') Insert into DBInfo values('/PRODUCTION/SERVICE/MAINTENANCE') Insert into DBInfo values('/PRODUCTION/SERVICE/LOGISTICS')
My Expected Output
Column1,Column2,Column3 Data SourcesNullNull Data SourcesSalesNull PRODUCTIONNullNull PRODUCTIONSERVICENull PRODUCTIONSERVICEMAINTENANCE PRODUCTIONSERVICELOGISTICS
I have a problem where I want to write a function to remove recurring characters from a string and replace them with a single same character.
For instance I have the string '12333345566689' and the result should be '12345689'. In Oracle I could do this with "regexp_replace('12333345566689', '(.)1+', '1')", but in T-SQL the only solution I could think of is something like this:
DECLARE @code NVARCHAR(255) SET @code = '12333345566689'; SET @code = REPLACE(REPLACE(REPLACE(@Code, '1', '~1'), '1~', ''), '~1', '1');
and repeat this for 2 - 9. But I'm sure there is a more elegant version for this in SQL Server 2012.
