Store And Retrieve Unicode Data
Oct 31, 2007
HI,
I want to store unicode data and retrieve them using C#.
How can I do so?
Data is saved but can not retrive. The data displayed as (??????).
Could Anybody Help me.
ADVERTISEMENT
Asp.net Page Is Unable To Retrieve The Right Data Calling The Store Procedure From The Dataset/data Adapter
Apr 11, 2007I'm trying to figure this out
I have a store procedure that return the userId if a user exists in my table, return 0 otherwise
------------------------------------------------------------------------
Create Procedure spUpdatePasswordByUserId
@userName varchar(20),
@password varchar(20)
AS
Begin
Declare @userId int
Select @userId = (Select userId from userInfo Where userName = @userName and password = @password)
if (@userId > 0)
return @userId
else
return 0
------------------------------------------------------------------
I create a function called UpdatePasswordByUserId in my dataset with the above stored procedure that returns a scalar value. When I preview the data from the table adapter in my dataset, it spits out the right value.
But when I call this UpdatepasswordByUserId from an asp.net page, it returns null/blank/0
passport.UserInfoTableAdapters oUserInfo = new UserInfoTableAdapters();
Response.Write("userId: " + oUserInfo.UpdatePasswordByUserId(txtUserName.text, txtPassword.text) );
Do you guys have any idea why?
Really Urgent - Data Field Retrieve And Store
Jul 2, 2006Basically i make use of FormView in Web Developer Express Edition to create and store information to the database .... what i want to do further is to capture the value within the primary key of the newly created recordset that I set it become auto increament .....this value would be stored within a session string in global condition that allowed any page able to access it .....
View 3 Replies View RelatedIntegration Services :: Column A Cannot Convert Between Unicode And Non-unicode String Data Types
Aug 7, 2012I am following the SSIS overview video- URL...I have a flat file that i want to import the contents onto a SQL database.I created a Dataflow task, source file and oledb destination.I am getting the folliwung error -"column "A" cannot convert between unicode and non-unicode string data types".in the origin file the data type is coming as string[DT_STR] and in the destination object it is coming as "Unicode string [DT_WSTR]"I used a data conversion object in between, dosent works very well
View 5 Replies View RelatedExcel Destination Error: Columnxx Cannot Convert Between Unicode And Non-unicode String Data Types
Jul 28, 2005Good afternoon
View 32 Replies View RelatedSQL 2012 :: (SSIS) - Cannot Convert Between Unicode And Non-unicode Data Types
Sep 9, 2015I have an SSIS package that pulls data from a MYSQL DB (Using RSSBus for Salesforce in SSIS to accomplish this). Most of the columns are loading properly, but I have many columns that I need to convert.
I have been using the Data Conversion dataflow task in SSIS to convert the rows.
I have 2 data conversions that work on most of the columns, but the DESCRIPTION column continues to return an error saying "Cannot convert between unicode and non-unicode types", regardless of what I choose on the Data Conversion task. So, basically I want to dump this column data into a SQL table with NVARCHAR datatypes. Here is what I am doing in my SSIS package...
1) Grab subset of data from SOURCE
2) Converts to TEXTSTREAM. (Data Conversion)
3) Converts to STRING. (Data Conversion)
4) Load Destination table. (OLE DB Destination)
I have also tried to simply convert the values to STRING, but that doesn't work either.
So, I have 2 Data Conversions working here that process most of the data correctly. What I can do to load the DESCRIPTION column?
SSIS: Unicode And Non-unicode String Data Types
May 14, 2008Hi guys and gals,
I've had some great headaches with SSIS this morning, which I have managed to get a workarounds for, but I'm not happy with them so I've come to ask for advice.
Basically, I am exporting data from an SQL Server database into an Excel spreadsheet and hitting issues with unicode and non-unicode data types.
For example, I have a column that is char(6) and have added a data conversion step to the data flow, which converts it to type DT_WSTR and then everything works!
However, this seems like a completely un-neccessary step as I should be able to do the conversion in T-SQL - but no matter what I try I keep getting the same problem.
SELECT Cast(employee_number As nvarchar(255)) As [employee_number]
FROM employee
WHERE forename = 'george'
ErrorValidation error. details: 1 [1123]: Column "employee_number" cannot convert between unicode and non-unicode string data types.
I know I have a solution (read: workaround) but I really don't want to do this everytime!
Any suggestions for what else to try?
Cannot Convert Between Unicode And Non-unicode String Data Types.
Apr 6, 2006I have an Excel Source component hooked to an OLE DB Destination component in my SSIS 2005 Data Flow Task. After I mapped the excel columns to the OLE DB table columns i get these errors below. I noticed that for the first error, the Excel Field format (when you mouse over the column name in the mappings section in OLE DB component) is of type [DTWSTR] and the corresponding SQL field from my SQL table that it's mapping to is of type [DT_STR] when mousing over that field in the mappings in the properties of my OLE DB component. All table fields in SQL Server for the table I'm inserting into are of type varchar.
print screens here:
http://www.webfound.net/excel_ssis.jpg
http://www.webfound.net/excel_to_oledb_mappings.jpg
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Data Flow Task [OLE DB Destination [27]]: Columns "Commission Agency" and "CommissionAgency" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [OLE DB Destination [27]]: Column "Product" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [OLE DB Destination [27]]: Columns "Officer Code" and "OfficerCode" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [OLE DB Destination [27]]: Columns "Agency Name" and "AgencyName" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [OLE DB Destination [27]]: Columns "Agency Id" and "AgencyID" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [OLE DB Destination [27]]: Columns "Tran Code" and "TranCode" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [OLE DB Destination [27]]: Columns "User Id" and "UserID" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [OLE DB Destination [27]]: Columns "Acct Number" and "AccountNumber" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [DTS.Pipeline]: "component "OLE DB Destination" (27)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
Cannot Convert Between Unicode And Non-unicode String Data Type
Jan 15, 2007HI.
I'm having this problem.
I use Visual Studio's, integration project to load XML file into SQL Server. In the XML file, i have defined collumns as string. When i try to load XML file with parts defined in scheme as string, i get an error "cannot convert between unicode and non-unicode string data type.
Destinated collumns in SQL are defined as varchar and char.
Thanks for help
Cannot Convert Between Unicode And Non-unicode String Data Types
Mar 7, 2008Hi,
I have spent countless number of hours trying to solve the issue, but to no vail. My problem is SSIS throws "cannot convert between unicode and non-unicode string data types" when i am try to transform data from DB2 to SQL Server 2005. And please note, i tried all possibilities like changing the destination field which is in SQL Server 2005 to nvarchar and also text. But so far no help. And i also looked at previous posts which did not help me either.
Thank You in advance.
Store Unicode In SQL Server 2005 Express
Apr 2, 2008
I create websit (ASP.NET) using Visual Studio.Net 2005 and SQL Server 2005 express.
I'm working in Thailand I want to store information such as user name in Thai format into SQL Server 2005 Express.
I already try but doesn't work.
Did someone have any idea?
Non-Unicode To Unicode Data Conversion
Jul 23, 2005Hi all, we are now planning to upgrade our application from anon-unicode version to a unicode version. The application's backend isa SQL Server 2000 SP3.The concern is, existing business data are stored using collation"Chinese_PRC_CI_AS", i.e. Simplified Chinese. So I thought we need toextract these data out to the new SQL Server which is using Unicode (Iassume it means converting them to nchar, nvarchar type of fields for Idon't enough information from the application side, or is there ageneral unicode collation that will make even char and varchar types tostore data as Unicode?).The problem is what's the best and most efficient way to do this dataconversion?bcp? DTS? or others?thanks a lot
View 6 Replies View RelatedRetrieve Store Procedure Name
Apr 11, 2008
Is possible to get the name of the store procedure that i'm executing ?
I need to get the name of the procedure inside the procedure being executed, to insert this value on a log file...
How To Store Linefeeds To A Database And Retrieve Them
Oct 11, 2007hello all, I have a multi line textbox and want to store the content of the textbox to a database. I want to preserve the linefeeds so that I display the message as it is typed by retrieving it. Can anyone please explain how to store the linefeeds to the database. Once i store the linefeeds to the database, I can restore them using the Replace("","<br/>) method. But I can't figure out how to store the 's in the database in the first place. Thanx in advance
View 2 Replies View RelatedHow Can I Store And Retrieve Images From A Database
Mar 23, 2008hi,
i would like to store images in my database and retirve them aswell. i have looked at examples on the net but i am finding it hard to follow and undertsand them, can anyone please give me adivse on how i can do this. i have alrady created my table in my database which has 4 fields pictureID, pictureContent, pictureType and pictureSize but i dont know where to go on from here. i want to do it in vb aswell.
please any help and advise would be much appreciated as i am stuck,
thank you
Retrieve Output Value From Store Procedure
Jan 24, 2006I'm trying to use a store procedure to add an item into the database and retrieve the id of such item within and output parameter but this is not happening.
The item is added into the db but the output param is not modified.
Here is the code:
SP
CREATE procedure dbo.AddItem(@Desc nvarchar(100),@intItemID int output)as insert into RR_Item ( desc ) values ( @Desc )
select @intItemID = SCOPE_IDENTITY()GO
I have tried in the last line of the SP
select @intItemID = SCOPE_IDENTITY()
select @intItemID = @@IDENTITY
select @intItemID = max(itemid) from RR_Item
but nothing seems to work.
I'm calling the store procedure as follows from asp.net:
Dim intItemID As New SqlParameter("@intItemID", SqlDbType.Int)
intItemID.Direction = ParameterDirection.Output
SqlHelper.ExecuteDataset(objConn.ConnectionString, "AddItem", desc, intItemID)
MsgBox(intItemID.Value.ToString)
Any help would be appreciated.
Thanks
How To: Store And Retrieve Images In A Database As A Byte Array.
Feb 28, 2008I€™ve inherited a project from one of the guys on our team who will be out sick for a while. He developed two for marshaling data between System.Drawing.Image and System.Byte(). He€™s storing the byte array data in a database image field.
I€™ve retrieved the byte array data from his database image fields and have successfully converted them to images using his ConvertByteArrayToImage method below. I have also converted and image to a byte array with his ConvertImageToByteArray method below and succfully stored the data in a database image field. However, when I retrieve the byte array data that I stored in the database the last line in his ConvertByteArrayToImage method throws an exception (Parameter is not valid). I€™ve not been able to find a working copy of his code that€™s storing the byte array data. Does anyone see anything I€™m overlooking?
Imports System.Drawing
Imports System.IO
Public Sub InsertImage(ByVal pFilename As String)
Try
Dim lImage As Image
Dim lBA() As Byte
Dim lSQL As String
Dim lQuery As Alcon.SQLServer.Database.clsQuery
Dim lParameters As New Alcon.SQLServer.Database.clsParameters
lImage = Image.FromFile(pFilename)
ConvertImageToByteArray(lImage, lBA)
' Initialization
lQuery = New Alcon.SQLServer.Database.clsQuery(mConnection)
lSQL = ""
lSQL += "INSERT INTO [TBL_PCL_LENS_DATA]("
lSQL += "[SerialNumber], "
lSQL += "[ProcessedDate], "
lSQL += "[CartonLabelImage]) "
lSQL += "VALUES ("
lSQL += "@SerialNumber, "
lSQL += "@ProcessedDate, "
lSQL += "@CartonLabelImage "
lSQL += ")"
lParameters.Add("@SerialNumber", SqlDbType.VarChar, ParameterDirection.Input, mSerialNumber)
lParameters.Add("@ProcessedDate", SqlDbType.DateTime, ParameterDirection.Input, Now)
lParameters.Add("@CartonLabelImage", SqlDbType.Image, ParameterDirection.Input, lBA)
' Execute query
lQuery.Execute(lSQL, lParameters)
Catch ex As Exception
Throw
End Try
End Sub
ConvertByteArrayToImage(ByVal pBA() As Byte, ByRef pImage As Image)
Try
' Declaration
Dim lMS As MemoryStream
' Initialization
lMS = New MemoryStream(pBA, 0, pBA.Length)
lMS.Write(pBA, 0, pBA.Length)
lMS.Position = 0
' Create image
pImage = Image.FromStream(lMS, True)
Catch ex As Exception
Throw
End Try
End Sub
ConvertImageToByteArray(ByVal pImage As Image, ByRef pBA() As Byte)
Try
' Declaration
Dim lBM As Bitmap
Dim lBR As BinaryReader
Dim lMS As New MemoryStream
' Initialization
lBM = New Bitmap(pImage)
lBM.Save(lMS, Imaging.ImageFormat.Png)
lBR = New BinaryReader(lMS)
' Create byte array
pBA = lBR.ReadBytes(lMS.Length)
Catch ex As Exception
Throw
End Try
End Sub
Integration Services :: CDC Source Transformation And Converting Non-Unicode To Unicode String SSIS
May 6, 2015In my package , I am used CDC Source transformation and received the Net changes then insert into Destination. But whatever Data coming from CDC source data type Varchar value needs to Converting Non Unicode string to Unicode string SSIS. So used Data conversion transformation to achieved this.  I need to achieve this without data conversion.
View 3 Replies View RelatedProblem Saving/retrieving Unicode Characters NVARCHAR With Unicode Collation (java Jdbc)
Aug 8, 2006I'm connecting to a SQL Server 2005 database using the latest (beta) sql server driver (Microsoft SQL Server 2005 JDBC Driver 1.1 CTP June 2006) from within Java (Rational Application Developer).
The table in SQL Server database has collation Latin1_General_CI_AS and one of the columns is a NVARCHAR with collation Indic_General_90_CI_AS. This should be a Unicode only collation. However when storing for instance the following String:
‚¬_£_ÙÚÜÛùúüû_ÅÆØåæøߣÇçÑñ_¼½¾_ЎўЄєÒ?Ò‘_прÑ?туф_ЂЉЊЋ
... it is saved with ? for all unicode characters as follows (when looking in the database):
‚¬_£_ÙÚÜÛùúüû_ÅÆØåæøߣÇçÑñ_¼½¾_??????_??????_????
The above is not correct, since all unicode characters should still be visible. When inserting the same string directly into the sql server database (without using Java) the result is ok.
Also when trying to retrieve the results again it complains about the following error within Java:
Codepage 0 is not supported by the Java environment.
Hopefully somebody has an answer for this problem. When I alter the collation of the NVARCHAR column to be Latin1_General_CI_AS as well, the data can be stored and retrieved however then of course the unicode specific characters are lost and results into ? So in that case the output is as described above (ie ‚¬_£_ÙÚÜÛùúüû_ÅÆØåæøߣÇçÑñ_¼½¾_??????_??????_????)
We would like to be able to persist and retrieve unicode characters in a SQL Server database using the correct JDBC Driver. We achieved this result already with an Oracle UTF8 database. But we need to be compliant with a SQL Server database as well. Please help.
Thanks in advance for your help.
Integration Services :: Cannot Convert From Unicode To Non-unicode Error
May 7, 2009For packages that I have created to read Oracle 10g tables, that work fine with debugging in 32-bit mode, I get an error message on all string fields when I try to run in 64-bit mode. An example error message is:[OLE DB Source [1]] Error: Column "ACCT_UNIT" cannot convert between unicode and non-unicode string data types.Another interesting warning included is:[OLE DB Source [1]] Warning: The external columns for component "OLE DB Source" (1) are out of synchronization with the data source columns. The external column "ACCT_UNIT" needs to be updated.I cannot even try to convert this data with a Data Conversion item because the (red) error is on the OLE DB Source item and stops there. It doesn't matter what the destination is or even if there is a destination in the package yet.I'm using Oracle Provider for OLE DB, Oracle Client version 10.203 for 32-bit and Oracle Client 10.204 for 64-bit.Oracle is 10g on a UNIX 64-bit server and the data is not unicode.I'm using SQL Server Enterprise 2008 (10.0.1600) on Windows Server 2008 Standard SP1 on a 64-bit server.The packages work fine in 32-bit mode and the data is not unicode data. When I change Run64BitRuntime to True in the Debugging Property Page, I get the error on the OLE DB Source item. I also get the error when I schedule a package to run using the SQL Server Agent.
View 4 Replies View RelatedMS SQL Server - Transfer Database From Non-unicode To Unicode
Jun 29, 2006Any one know the process of transfering the database from non-unicode to unicode. Coz I like to transfer the data from english to hebrew.
View 1 Replies View RelatedUnicode Data
Mar 9, 2005I am getting unicode data into my database table and I am unable to see that data in my quesry analyzer. Can I convert that unicode data into varchar datatype?
AND how can i See that unicode data.
Inserting Unicode Data
Feb 20, 2007Hi.
We have a sql server db that we need to store Unicode text in. The fields are of the type nvarchar, ntext and nchar. Our solution uses both Oracle and SqlServer as a backing database. In Oracle there is a connection string switch "Unicode=True" that fixes the problem. Is there something similar in SqlServer? Since the db layer is generic we'ed like to avoid using a N' prefix on text strings in query statements.
Updating Unicode Data
Sep 28, 2007Hi,
Could someone please tell me how to update data in a column that contains unicode data in SQL 2000?
I know how to write update scripts on my own, but when it comes to Unicode character updates I canÂ?ft manage. In the script that I have pasted below I can update the customerÂ?fs details, but this only works if I update with Romaji characters (non-unicode).
The data that I need to update is in Japanese Kanji, but when I use the 'Update' script below, the data is updated as all question marks (???????????).
Is it very much harder to get the script to where it will handle Unicode characters and insert the correct data?
UPDATE CUSTOMER_LANGUAGE_DETAILS
SET FIRST_NAME = '‰h�s'
WHERE CUSTOMER_ID = 1592
How To Fetch Unicode Data Using C++?
May 22, 2007I am getting problem in fetching data from SQL using C++ program.
How to Fetch SQL Unicode characters?
I am using ODBC driver calls in C++ to fetch SQL data.
It is working fine with character data.
But for Unicode data it displays €œ????€? instead of data.
I am using SQL Server SQLSRV32.DLL for creating datasource.
Following is my code:
#include <windows.h>
#include <sqlext.h>
#include<srv.h>
#include<stdio.h>
#include <iostream.h>
#include <string.h>
int main(void)
{
void print_err2(RETCODE rc, SQLINTEGER type, SQLHANDLE handle);
HENV hEnv = NULL; // Env Handle from SQLAllocEnv()
HDBC hDBC = NULL; // Connection handle
HSTMT hStmt = NULL;// Statement handle
//UCHAR szDSN[SQL_MAX_DSN_LENGTH] = "myDataSource";// Data Source Name buffer
USHORT *szDSN;
szDSN = (USHORT *) malloc (sizeof(USHORT) * 13);
szDSN= (unsigned short *)"myDataSource";
//UCHAR szUID[10] = "sa";// User ID buffer
USHORT *szUID;
szUID= (unsigned short *) "sa";
//UCHAR szPasswd[10] = "bmcAdm1n";// Password buffer
USHORT *szPasswd;
szPasswd= (unsigned short *) "bmcAdm1n";
char buff[9] = "Testing";
// UCHAR szSqlStr[128]= "INSERT into Quali (Colname) Values ('Testing')" ;
//UCHAR szSqlStr[150]= "select name ,default_database_name,default_language_name from sys.sql_logins" ;
USHORT *szSqlStr;
szSqlStr = (USHORT *) malloc (sizeof(USHORT) * 150);
szSqlStr= (unsigned short *) "select name ,default_database_name,default_language_name from sys.sql_logins" ;
RETCODE retcode;
//UCHAR sqlState; // buffer to store SQLSTATE
// UCHAR errMsg[256]; //buffer to store error message
// SWORD count;
USHORT *sqlState;
USHORT *errMsg;
sqlState= (USHORT *) malloc(sizeof(USHORT)*6);
errMsg= (USHORT *) malloc(sizeof(USHORT)*256);
SDWORD retcode3=NULL; // return code
SDWORD nativeErr; // native error code
SWORD realMsgLen; // real length of error message
// sprintf((char*)szSqlStr,"select name ,default_database_name,default_language_name from sys.sql_logins",buff);
// Allocate memory for ODBC Environment handle
cout<<"hEnv before SQLAllocEnv = "<<hEnv<<endl;
retcode= SQLAllocEnv (&hEnv);
cout<<"retcode= "<<retcode<<endl;
cout<<"hEnv after SQLAllocEnv = "<<hEnv<<endl;
cout<<"***********************";
// Allocate memory for the connection handle
cout<<"hDBC before SQLAllocConnect = "<<hDBC<<endl;
retcode= SQLAllocConnect (hEnv, &hDBC);
cout<<"retcode= "<<retcode<<endl;
cout<<"hDBC after SQLAllocConnect = "<<hDBC<<endl;
cout<<"***********************";
SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDBC);
// Connect to the data source using userid and password.
retcode = SQLConnect (hDBC, szDSN, SQL_NTS,szUID, SQL_NTS, szPasswd, SQL_NTS);
cout<<"retcode= "<<retcode<<endl;
if (retcode != SQL_SUCCESS )
{
retcode3 = SQLError(SQL_NULL_HDBC, hDBC, SQL_NULL_HSTMT, sqlState,&nativeErr,errMsg, 256, &realMsgLen);
// print_err(sqlState, nativeErr, errMsg, realMsgLen);
//print_err2(retcode,SQL_HANDLE_DBC,hDBC);
cout<<sqlState<<endl<<nativeErr<<endl<<errMsg<<endl<<realMsgLen;
}
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{
cout<<"Connection established....!";
// Allocate memory for the statement handle
cout<<"hStmt before SQLAllocStmt = "<<hStmt<<endl;
retcode = SQLAllocStmt (hDBC, &hStmt);
cout<<"retcode= "<<retcode<<endl;
cout<<"hStmt after SQLAllocStmt = "<<hStmt<<endl;
cout<<"***********************";
/*
// Prepare the SQL statement by assigning it to the statement handle
retcode = SQLPrepare (hStmt, szSqlStr, sizeof (szSqlStr));
cout<<"Statement szSqlStr: "<<szSqlStr<<endl;
cout<<"Statement hStmt: "<<hStmt<<endl;
// Execute the SQL statement handle
retcode = SQLExecute (hStmt);
*/
retcode = SQLExecDirect(hStmt,szSqlStr, SQL_NTS);
UCHAR name[4000],defDBnm[4000],defLanguage[4000];
SDWORD retcode2, cbname, cbdefDBnm, cbdefLanguage;
retcode2=NULL;
if (retcode2 == SQL_SUCCESS)
{
retcode2 = SQLBindCol(hStmt, 1, SQL_C_CHAR, name, 4000, &cbname);
retcode2 = SQLBindCol(hStmt, 2, SQL_C_CHAR, defDBnm, 4000, &cbdefDBnm);
retcode2 = SQLBindCol(hStmt, 3, SQL_C_CHAR, defLanguage, 4000, &cbdefLanguage);
}
if (retcode2 != SQL_SUCCESS) /* warning or error returned */
{
retcode3 = SQLError(SQL_NULL_HDBC, hDBC, SQL_NULL_HSTMT, sqlState,&nativeErr,errMsg, 256, &realMsgLen);
//print_err(sqlState, nativeErr, errMsg, realMsgLen);
cout<<sqlState<<endl<<nativeErr<<endl<<errMsg<<endl<<realMsgLen;
}
cout <<"****************************************";
while (TRUE)
{
retcode2 = SQLFetch(hStmt);
if (retcode2 == SQL_SUCCESS || retcode2 == SQL_SUCCESS_WITH_INFO)
{
if (cbname == SQL_NULL_DATA) /* check null data */
printf("name: NULL");
else
printf("Name: %s", name);
if (cbdefDBnm == SQL_NULL_DATA)
printf("defDBnm name: NULL");
else
printf("defDBnm name: %s", defDBnm);
if (cbdefLanguage == SQL_NULL_DATA)
printf("defLanguage: NULL");
else
printf("defLanguage : %s", defLanguage);
}
else if (retcode2 == SQL_ERROR ) /* warning or error returned */
{
retcode3 = SQLError(SQL_NULL_HDBC, hDBC, SQL_NULL_HSTMT, sqlState,&nativeErr, errMsg, 256, &realMsgLen);
//print_err(sqlState, nativeErr, errMsg, realMsgLen);
cout<<sqlState<<endl<<nativeErr<<endl<<errMsg<<endl<<realMsgLen;
}
else /* if no more data or errors returned */
break;
}
// Project only column 1 which is the models
// SQLBindCol (hStmt, 1, SQL_C_CHAR, szModel, sizeof(szModel), &cbModel);
// Get row of data from the result set defined above in the statement
// retcode = SQLFetch (hStmt);
// Free the allocated statement handle
SQLFreeStmt (hStmt, SQL_DROP);
// Disconnect from datasource
SQLDisconnect (hDBC);
}
// Free the allocated connection handle
SQLFreeConnect (hDBC);
// Free the allocated ODBC environment handle
SQLFreeEnv (hEnv);
return 0;
}
void print_err2(RETCODE rc, SQLINTEGER type, SQLHANDLE handle)
{
//SQLCHAR SqlState, *sq = &SqlState[0];
SQLUSMALLINT *SqlState;
SqlState=(SQLUSMALLINT *) malloc (sizeof(SQLUSMALLINT)*6);
SQLUSMALLINT *sq = &SqlState[0];
SQLCHAR Msg[SQL_MAX_MESSAGE_LENGTH], *ms = &Msg[0];
SQLWCHAR SqlStateW, *sqw = &SqlStateW[0];
SQLWCHAR MsgW[SQL_MAX_MESSAGE_LENGTH], *msw = &MsgW[0];
SQLINTEGER NativeError;
SQLSMALLINT i=1, MsgLen;
RETCODE ret;
int j;
printf("Error number is %d", rc);
while (SQL_SUCCEEDED(SQLGetDiagRec(type, handle, i++,(unsigned short *) SqlState, &NativeError, (unsigned short *)Msg, sizeof(Msg), &MsgLen)) )
{
printf("State: %s Native Error Code: %d %s (%d)",
SqlState,NativeError,Msg, MsgLen);
}
}
Regards
Ketaki
MS SQL Server Handling Unicode Data
Feb 21, 2007Hi,
I want to fetch Unicode data from MS SQL server 2005.I have created one user with russian language characters.
Following is my code snippet:
------------------------------------
wchar_twchar1[55];
char VALUE[255];
while (rc != SQL_NO_DATA)
{
rc = SQLFetch(hstmt);
if (rc != SQL_NO_DATA)
memset(VALUE,0,255);
int length=wcstombs( NULL,(wchar_t *)&wchar1, wcslen(wchar1));
char* strChar = (char *) malloc((length +1 )* sizeof(char));
if (strChar != NULL)
{
count++;
memset(strChar,0, length + 1 );
int i =wcstombs(strChar, wchar1, wcslen(wchar1) );
printf( "i is %d",i);
}
printf( "The length is %d",length);
wprintf(L" <%s>", wchar1);
printf(" simple char <%s>", strChar);
wprintf(L" simple char <%S>", wchar1);
}
-----------------------------------
CAn anybody help me ?
I think problem is in fetching of name.
Does anybody have working code for fetching unicode data from SQL server 2005.
Also I want query that will tell the locale setting of the server and also client?
Thanks-----
Easier Way To Convert Non-Unicode To Unicode
Nov 8, 2006I have built a large package and due to database changes (varchar to nvarchar) I need to do a data conversion of all the flat file columns I am bringing in, to a unicode data type. The way I know how to do this is via the data conversion component/task. My question is, I am looking for an easy way to "Do All Columns" and "Map all Columns" without doing every column by hand in both spots.
I need to change all the columns, can I do this in mass? More importantly once I convert all these and connect it to my data source it fails to map converted fields by name. Is there a way when using the data conversion task to still get it to map by name when connecting it to the OLE destination?
I know I can use the wizard to create the base package, but I have already built all the other components, renamed and set the data type and size on all the columns (over 300) and so I don't want to have to re-do all that work. What is the best solution?
In general I would be happy if I could get the post data conversion to map automatically to the source. But because its DataConversion.CustomerID it will not map to CustomerID field on destination. Any suggestions on the best way to do this would save me hours of work...
Thanks.
Unicode Character Data Types && Collation
Dec 18, 2006Hi there,Trying to work with SQL2005 with Hebrew characters. If i use the Unicode character data types do i need to change the database collation???. regards
View 5 Replies View RelatedImporting Unicode Data From Oracle Database
Nov 28, 2005Hi everybody,
View 13 Replies View RelatedHow To Display Unicode Data Stored In MSSQL As Shift-jis In ASP
Nov 22, 2006 We are experiencing problems in presenting Unicode data stored in MSSQL as Shift-jis format in ASP application.
In MSSQL we are storing kanji text in database with a field type as nchar. Other application loads these tables using codepage 932. In MSSQL data is stored in Kanji correctly.
In ASP application, when we are trying to read data from these tables, Kanji characters are coming as '?????'. We are using shift-jis character set in ASP.
Templates for field and column header uses shift-jis, but a record set coming from MSSql is Unicode.
Is there a way to convert from Unicode to Shift-jis in MSSql or in ASP .
Please help!!
Insert Unicode Data Into The Database With Typed DataSet
Dec 11, 2006Hi all,I am using a Strongly Typed DataSet (ASP.NET 2.0) to insert new data into a SQL Server 2000 database, types of some fields in db are nvarchar. All thing work fine except I can not insert unicode data(Vietnamese language) into db.I can't find where to put prefix N. Please help me!!!
View 1 Replies View RelatedDB Engine :: Read Unicode Data From Server 2008
Oct 18, 2015way to read data from database already stored as question marks .that because by mistake i insert the data "arabic" from  the VS as sqltext but the field in the database is nvarchar now i want to get the data back.
View 4 Replies View RelatedAS 400 Data Being Interpreted As Unicode Strings (DT_WSTR) Datatype
Jun 23, 2006I am building a data warehouse. Some of the data comes from an AS 400 EPR system. I used the OLEDB connector when first pulling the data into SQL Server doing simple import data from table option. That worked great for getting the initial data load into SQL Server and creating the base SQL Server tables although it was excruciatingly slow (that was probably due to the transport from the AS 400).
Now, I need to get new records that are added to the AS400 side of things on a daily basis. For that, I was trying to use the OLEDB AS400 connector. However, I found that the OLEDB connector wouldn't work when I was trying to specify an SQL Statement for what to get; i.e., a simple query like Select * from TWLDAT.STKT where BYSDAT >= '2005-01-27' would simply not work. Found articles here explaining that it is probably a problem on the AS400 side of things and where people recommended using an ADO ODBC data reader source for this type of thing. So, I'm trying to implement that. However, I have a huge problem with it.
The original tables that got created were mapped to use NVARCHAR fields for character data. When the ADO ODBC data reader source accesses the AS400 data, it insists on interpreting the string type fields as being unicode strings and giving it a data type of DT_WSTR when what I need it to have is a plain old DT_STR data type. When the strings are interpreted as unicode strings, they cannot be converted in a way that allows the NVARCHAR fields to be filled with the data. The exact error message I get for all the fields that should wind up being nvarchar fields is as follows:
Column "BYStOK" cannot convert between unicode and non-unicode string data types.
Okay, so I try to change the data types in the ADO ODBC data reader to be plain DT_STR data types and I cannot do so.
Does anyone have any idea why the ADO ODBC data reader source insists on interpreting the string data coming from the AS 400 as unicode string data or why it refuses to allow that to be changed to DT_STR data type?
Thanks in advance for any info. By the way, if there is a better way than the ADO ODBC data source to get at this data when I need to specify an SQL command, I would love to hear about it. Not wild about using ODBC in the OLEDB age.
Steve Wells