I have a SQL Server 2000 database which uses a Cyrillic collation. The database itself is in English, but some of the tables contain text which use the Cyrillic character set. When creating an ODBC connection to the database, ODBC forces me to have the "Translate character data" option set, which means that a query or stored procedure parameter containing Cyrillic characters have the Cyrillic characters translated to plain ascii. Data returned from a query or SP with Cyrillic characters work fine.
So the question is: how can I send Cyrillic (unicode) strings to an English SQL Server, without it translating the characters between Workstation and Server? Ideas appreciated.
Hi,I have a problem regarding the perfomance of a stp in combination withcharacter translation.The following happens.We have an automated installation script (nt command file) forcreating all stored procedures in a database.At first we used osql in this script, but with osql the internationalcharacters (é, ï etc) were not entered correctly into the database.The tip given in some newsgroups was to use isql in stead of osql, andturn off automatic ansi to oem translation.This worked perfectly. However now some stored procedures are executedmuch slower than before (and they take up much cpu). When I drop andrecreate the stored procedure using Query Analyser, the performancegoes up again.When I look through the messages in this group, the advise is to useosql instead of isql. But I just changed to isql because of thecharacter translation!Does anyone have a solution for this?
I wanted to post this message to all of those people whom might have experieinced the same situation that we are currently experiencing.
The architecture is transactional replication from a Unix/Sybase environment to a WINNT/MSSQL environment.
When a developer ran a query utilizing a Sybase SQL Advantage window and a ISQL window from Unix, this data field with these values is being report as such:
col1 (SQL Advant) Nov 17 2000 (ISQL) Nov 17 4336 col2 (SQL Advant) Nov 17 2000 (ISQL) Nov 17 1510
However, when the problem was brought to my attention, I opend a SQL Advantage window and a MSSQL QA window and ran the same query and the dates matched as listed above in the (SQL Advant) col1 and col2. Can anyone explain this wierd phenomena?
Any help with be appreciated.
Thanks in Advance, Daimon daimon.russell@bridge.com
I am importing data where the date is in "797867443" format. I believe this in seconds taken place since 1/1/1980 GMT. How can I translate this to 8am 10/1/1999 EST?
I have a source table with a varchar(1000) field. I want to abbreviate words in source string and store them as a concatenated string in target field which is varchar(100). Translation of source word to abbreviated word can be provided in flat file or database table.
I have a stored procedure which is written in SQL Server and I want to translate it in Oracle. This stored procedure find a value in whole database, all the tables and columns. Any help will be highly appreciated.
CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS BEGIN
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ANDQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName ANDOBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0 )
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA= PARSENAME(@TableName, 2) ANDTABLE_NAME= PARSENAME(@TableName, 1) ANDDATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') ANDQUOTENAME(COLUMN_NAME) > @ColumnName )
IF @ColumnName IS NOT NULL BEGIN INSERT INTO #Results EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 ) END END END
I have deveoped a replacement for some an old bcp based applications in the .Net Framework that uses the SqlBulkCopy class.
I have run into some difficulties with code page translation:
The original BCP client runs with OEM Codepage 437 , thus the data "ëÄÆòÖ" gets loaded as "d-¦=+". DB is SQL_Latin1_General_CP1_CI_AS, column is varchar.
I have been unable to perform any code page Encoding in .Net that yields the same result.
I want to emulate this behaviour in my database loader but as yet have been able to find a way....
I have translated my ReportViewer by setting myReportViewer.Messages to my own implementation. This helps me with the translation of most of the interface. However, errormessages such as "The value provided for the report parameter 'SomeDateParameter' is not valid for it's type" is not translated. Is there any way to fix the translation of such error messages?
Hi,I'm trying to convert MS Access 97 .mdb application to Access 2003 .adpapplication with SQL Server as Backend.I'm having trouble converting Access Query into SQL Query. The Query isgiven below:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~SELECT DISTINCTROW Buildings.BuildingNumber,First(Buildings.BuildingName) AS FirstOfBuildingName,First(OwnershipCodes.OwnershipCode) AS FirstOfOwnershipCode,First(OwnershipCodes.OwnershipDesc) AS FirstOfOwnershipDesc,First(CityCodes.CityName) AS FirstOfCityName,First(CountyCodes.CountyName) AS FirstOfCountyName,First(Buildings.Address) AS FirstOfAddress,First(Buildings.YearConstructed) AS FirstOfYearConstructed,First(Buildings.DateOccupancy) AS FirstOfDateOccupancy,First(Buildings.NumberLevels) AS FirstOfNumberLevels,First(Buildings.BasicGrossArea) AS FirstOfBasicGrossArea,Sum(Rooms.AssignableSquareFeet) AS SumOfAssignableSquareFeet,First(Buildings.UnrelatedGrossArea) AS FirstOfUnrelatedGrossArea,First(Buildings.SpecialArea) AS FirstOfSpecialArea,First(Buildings.CoveredUnenclosedGrossArea) ASFirstOfCoveredUnenclosedGrossAreaFROM CountyCodes INNER JOIN (OwnershipCodes INNER JOIN (ConditionCodesINNER JOIN ((CityCodes INNER JOIN Buildings ON CityCodes.CityCode =Buildings.CityCode) LEFT JOIN Rooms ON Buildings.BuildingNumber =Rooms.BuildingNumber) ON ConditionCodes.ConditionCode =Buildings.ConditionCode) ON OwnershipCodes.OwnershipCode =Buildings.OwnershipCode) ON CountyCodes.CountyCode =CityCodes.CountyCodeGROUP BY Buildings.BuildingNumber;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~Please can any one tell me substitue for First Function in Acess to SQLFunction.Any help is appreciated.Thanks,S
While trying to set up an unpivot transformation to load data from excel (2003) into sql server db, the dates as column headers get lost in the translation.
To simplify the problem I created a very simple package with an excel source and an excel destination.
The test Excel Source looks like
ID 1/1/2008 3/1/2008 5/1/2008 A 5 7 9 B 10 12 24
After running the package The destination looks like this: ID F4 F5 F6 A 5 7 9 B 10 12 24
I need to keep the dates since I am loading a large volume of data often. Any suggestions?
I need to consume a live data feed from a golf tournament. And by consume, I really mean insert (merge) into our own SQL Server database on a regular intervals as a tournament progresses.This site didn't let me upload an XML file, but you can see a sample of the data feed here: URL....
I need to insert this data into 2 tables, Player_Holes and Player_Shots. But while doing the insert, I need to lookup several things such as our player ID match to theirs on an external_id against the players table. The shot types translation, and some other logic about the process overall.
The columns in my player_holes tables are: id, player_id, hole_id, round, shots (this is a total # of strokes) and date_created/date_modified.Shots table is similar: id, player_id, hole_id, round, shot_number, shot_type_id, club, distance, date_created/date_modified.
The only way I know how to do it, is inefficient. I would parse the XML in ColdFusion (please no comments on ColdFusion, that's what we use for webdev), and then loop over it and do inserts for each player, each hole for each round, and the shots would probably be separate for each hole.
It would be so much better and more efficient if I could do it in SQL directly. I've done some research and SQL Server Data Tools looks promising. I've never used it, so would have to learn, but also I'm not sure if that'd work in this application when we want to run is as a scheduled task every few minutes.
Hello,I'm struggling a giant fight with my webprovider (ASP.net 2.0) in the following case:I'm using the default connectionstring which automatic is provided when I create an ASP.net webstarterskit. This connectionstring is working fine when i deployed that kit to a W2003 server testenvironment and is also running on my laptop. But when I tried to deploy the website to my (new) hostingserver, also a W2003 server, I landed in ERROR-land. The last error I received was:Cannot open database "Club" requested by the login. The login failed.> Login failed for user 'NT AUTHORITYNETWORK SERVICE'.NB: I gave that user the right privileges and roles. My provider is not willing in sending me an example of a good working connectionstring and is directing me to their FAQ-site in which the right connectionstring is mentioned. That string looks like this (for a MS SQL database):strConnectie = "Provider=SQLOLEDB;" & _ "Data Source=(local)SQLEXPRESS;" & _ "Initial Catalog=[GEBRUIKERSNAAM];" & _ "User Id=[GEBRUIKERSNAAM];" & _ "Password=[WACHTWOORD]"My connectionstring in web.config looks like this:<connectionStrings><clear/><add name="ClubSiteDB" connectionString="Data Source=.SQLExpress;Integrated Security=true;AttachDBFileName=|DataDirectory|Club.mdf;User Instance=True" providerName="System.Data.SqlClient"/><remove name="LocalSqlServer"/><add name="LocalSqlServer" connectionString="Data Source=.SQLEXPRESS;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|aspnetdb.mdf" /></connectionStrings>Is there anybody in this forum that can help me out and explain to me how to use the string, as provided by the host, in the default connectionstring i use in web.config ??Any help is welcome and appreciated.NB: I'm using VWD 2005 Express Edition and SQL Server Management Studio ExpressHarry Elzinga
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());
Hi, Anytime I BCP data from a table containing characters like ö it gets muddled up when I look at it in the text file. The code page of the NT OS is 850 and the character is included in it. Why is the character changed during the BCP ?
Ok this is going to be a little hard to describe but here goes
I have a table within which one column is used to store a SQL select statement as a string. This means that the whole select statement has to be enclosed within two ' characters. The problem is that the SQL statement itself contains these characters. The specific SQL statement i wish to save as a string is:
select '<A HREF=DisplayOnlyDiscipline?SESSION_ID=' + :SESSION_ID + '&DISP_REF=' + cast(DISP_REF as varchar) + '&EDIT_REF=' + :EDIT_REF + '>' + cast(DISP_REF as varchar) + '</A>' as Reference, V.DESCRIPTION as Discipline_Stage, DISP_DATE as Date from DISCPLIN D left outer join V_DISP V on (V.CODE = D.DISP_CODE) where EMPLOY_REF = :EDIT_REF order by DISP_DATE DESC
I'm using MS SQL intelligence studio's SSIS import wizard to import some Excel files into SQL tables and for certain fields I'm getting this strange character instead of NULL. Does anyone know what this character is, how to avoid it, or a SQL query that will get rid of it? I can't even copy it to my clipboard.
Hi I have got a column having data like RT 12.5R20 (P) OL RT 12.5R244(SP)GRADER
I want only need the value before '('. So, the result will be RT 12.5R20 and RT 12.5R244 Since the length of the character will change. Is there any funtions in T-SQL that I can use to achieve this? Thanks
I have the following:----------------- WHILE PATINDEX('%,%',@Columns)<> 0 BEGINSELECT @Separator_position = PATINDEX('%,%',@Columns)SELECT @array_Value = LEFT(@Columns, @separator_position - 1)SET @FieldTypeID = (SELECT FieldTypeID FROM [Form].[Fields] WHERE FieldID = (CAST(@array_Value AS INT)))SET @FieldName = (SELECT [Name] FROM [Form].[Fields] WHERE FieldID = @array_Value)print 'arry value' + CONVERT(VarChar(500), @array_value)print 'FieldTypeID: ' + CONVERT(VARCHAR(500), @FieldTypeID)PRINT 'FieldName: ' + @FieldNameBEGINIF @FieldTypeID = 1 OR @FieldTypeID = 2 OR @FieldTypeID = 3 OR @FieldTypeID = 9 OR @FieldTypeID = 10 OR @FieldTypeID = 7BEGINSET @InnerItemSelect = ' (SELECT ISNULL(CONVERT(VARCHAR(MAX),[Value]),'''') FROM [Item].[ItemDetailFieldRecords] IDFR WHERE IDFR.ItemDetailID = ID.ItemDetailID AND IDFR.FieldID = ' + @array_Value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT ISNULL(CONVERT(VARCHAR(MAX),[Value]),'''') FROM [Item].[TaskFieldRecords] TFR WHERE TFR.TaskID = T.TaskID AND TFR.FieldID = ' + @array_Value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FieldTypeID = 4 OR @FieldTypeID = 8 --DropDownList/RadioButtonlistBEGINSET @InnerItemSelect = ' (SELECT [Value] FROM [Form].[FieldListValues] FFLV INNER JOIN [Item].[ItemDetailFieldListRecords] IDFLR ON FFLV.FieldListValueID = IDFLR.FieldListValueID WHERE IDFLR.ItemDetailID = ID.ItemDetailID AND FFLV.FIeldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT [Value] FROM [Form].[FieldListValues] FFLV INNER JOIN [Item].[TaskFieldListRecords] TFLR ON FFLV.FieldListValueID = TFLR.FieldListValueID WHERE TFLR.TaskID = T.TaskID AND FFLV.FIeldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FieldTypeiD = 5 --CascadingBEGINSET @InnerItemSelect = ' (SELECT [FCV].[Value] FROM [Form].[FieldCascadingValues] FCV INNER JOIN [Form].[FieldCascadingLookUpTables] LT ON FCV.FIeldCascadingLookupTableID = LT.FieldCascadingLookupTableID INNER JOIN [Item].[ItemDetailFieldCascadingRecords] IDFCR ON IDFCR.FieldCascadingValueID = FCV.FieldCascadingValueID WHERE IDFCR.ItemDetailID = ID.ItemDetailID AND LT.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT [FCV].[Value] FROM [Form].[FieldCascadingValues] FCV INNER JOIN [Form].[FieldCascadingLookUpTables] LT ON FCV.FIeldCascadingLookupTableID = LT.FieldCascadingLookupTableID INNER JOIN [Item].[TaskFieldCascadingRecords] TFCR ON TFCR.FieldCascadingValueID = FCV.FieldCascadingValueID WHERE TFCR.TaskID = T.TaskID AND LT.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FieldTypeiD = 6 --ListBoxBEGINSET @InnerItemSelect = ' (SELECT i.[CSV] FROM @ItemDetailLV i WHERE i.ID = ID.ItemDetailID AND i.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT it.[CSV] FROM @TaskLV it WHERE it.ID = T.TaskID AND it.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FieldTypeID = 11 --UsersBEGINSET @InnerItemSelect = ' (SELECT SU.[UserID] FROM [Security].[Users] SU INNER JOIN [Item].[ItemDetailUserRecords] IDUR ON SU.UserID = IDUR.UserID WHERE IDUR.ItemDetailID = ID.ItemDetailID AND IDUR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT SU.[UserID] FROM [Security].[Users] SU INNER JOIN [Item].[TaskUserRecords] TUR ON SU.UserID = TUR.UserID WHERE TUR.TaskID = T.TaskID AND TUR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FIelDTypeID = 12 --GroupBEGINSET @InnerItemSelect = ' (SELECT SG.[GroupID] FROM [Security].[Groups] SG INNER JOIN [Item].[ItemDetailGroupRecords] IDGR ON SG.GroupID = IDGR.GroupID WHERE IDGR.ItemDetailID = ID.ItemDetailID AND IDGR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT SG.[GroupID] FROM [Security].[Groups] SG INNER JOIN [Item].[TaskGroupRecords] TGR ON SG.GroupID = TGR.GroupID WHERE TGR.TaskID = T.TaskID AND TGR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDENDPRINT 'Inner Item Select:' + @InnerItemSelectPRINT 'Inner Task Select:' + @InnerTaskSelectSET @IDSelect = @IDSelect + @InnerItemSelect + ', 'SET @TSelect = @TSelect + @InnerTaskSelect + ', 'SELECT @Columns = STUFF(@Columns, 1, @separator_position, '')END --------------- That is only part of a large query that writs a SQL Query to a column in a Database. That Query (in the column) is just ran normally so I don't need to compile it each time I want to run it.THe problem I have is @FieldName might be: ryan's field. That apostrophe is killing me because the SQL keeps it as ryan's field, not ryan''s field(note the 2 apostrophes). I cannot do: REPLACE(@FieldName, ''', '''') because it's not closing the apostrophes. Is there an escape character that I can use to say only one: ' ?Would the only solution be to put: ryan''s field into the Database, and just format it properly on the output? Thanks.
I've forgotten the character set that I've chosen when I was installing the SQL Server 7. Is there a way to check?
I'm currently using US English version of Windows NT4 and SQL7. But interestingly, all the data is in Japanes characters. It's actually for a Japanese website, and the front-end application is written in ASP. I remember reading somewhere that it is impossible to do certain type of sorting (by some particular order for the Japanese language) as it is limited by the choice of the language of the NT OS.
The type of sorting that I'm looking at is the grouping of 5-characters. One example is in http://www.forest.impress.co.jp/aiueo.html
Would this be possible with my current setup? Or would it help if I migrate over to Windows 2000? (I'd rather not move to Japanese NT4)
Your feedback and advice would be very much appreciated!
How do I replcae an 'enter' character with a space in a field? This extra spaces entered in the web form while populating the field is causing extra spaces in the field. What is the ASCII value for the 'enter' character? so that we can replace that with a space? Thanks. sa.
Hello, I am from the school of thought that you should in every case have your primary keys as numeric values only. However, where I currently work there is a project leader who is a recent FoxPro convert (I know, they are tough ones to crack). I made the suggestion recently that the keys in the table should be numeric and with him being the project leader and me just a lowely developer he said get lost. I made the point that later joining your tables together in a PK/FK relationship where the keys where character would be slower then with numeric keys. He didn't listen and now we are approaching production with a database that is really just a bunch of text file. He said that with SQL 7 it doesn't matter if the pk is numeric or character. I disagree. But I need solid documentation to take to him and to the managers to convince them. If anyone out there could advise me on this. And if anyone could give me or tell me where I could find documentation on why even in SQL 7 there is a need to use numeric keys that would be a great help and you would be making one more shop in this world a little bit more technically sound :-) Thank you in advance for your help.
