Inserting UNICODE using Stored Procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Inserting UNICODE using Stored Procedures

Author

Topic

kevin.may
Starting Member

3 Posts

Posted - 2006-03-06 : 06:09:40

Hi,

I am having problems inserting UNICODE characters into nvarchar fields in my SQL Server db. I can add these characters if I directly use sql statements. However, if I used stored procedures to perform the inserts, all I get in the database record is ???'s.

Does anybody have any ideas how to overcome this problem?

Thanks,
Kevin

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-06 : 10:06:08

can you show us your sproc code? and the code you're executing it with?

Go with the flow & have fun! Else fight the flow

kevin.may
Starting Member

3 Posts

Posted - 2006-03-06 : 10:27:33

Here's the SQL table:

CREATE TABLE [dbo].[AlertInformationTbl] (
[pkAlertId] [int] IDENTITY (1, 1) NOT NULL ,
[recipientName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[alertType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GSMPhoneNumber] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pagerNumber] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emailAddress] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AlertName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AlertDescription] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AlertEventID] [int] NULL ,
[ErrorLevelID] [int] NULL ,
[MinutesDelayBefore] [int] null,
[MinutesBetweenAlerts] [int] null,
[NumberOfAlerts] [int] null,
[MessageText] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

CONSTRAINT [PK_AlertInformationTbl] PRIMARY KEY CLUSTERED
(
[pkAlertId]
) ON [PRIMARY]
) ON [PRIMARY]

c# web service:

[WebMethod]
[SoapHeader("timeStamp", Direction=SoapHeaderDirection.InOut)]
public void SaveNewAlert(string strEmail , string strAlertNAme, string strAlertDescription, string strAlertMethod,bool blnNewAlert ,string strAlertEvent, string AlertID,string MinutesDelayBefore ,string MinutesBetweenAlerts ,string NumberOfAlerts, string strStatusType ,string strMessageText, string strSMSCountry, string customer)
{
RKM_Net_DataServices.clsDataServices objGetXMldata = new RKM_Net_DataServices.clsDataServices();

string strsql = "exec up_SaveNewAlert '" + strEmail + "','" + strAlertNAme.Replace("'","''") + "','" + strAlertDescription.Replace("'","''") + "','" + strAlertMethod + "','" +blnNewAlert.ToString() + "'," + strAlertEvent + ",''," + MinutesDelayBefore + "," + MinutesBetweenAlerts + "," + NumberOfAlerts + "," + strStatusType + ",'" + strMessageText.Replace("'","''") + "', '" + strSMSCountry.Replace("'","''") + "', '" + customer.Replace("'","''") + "'";

return objGetXMldata.ExecSQL(strsql);
}

public int ExecSQL(string strSQL)
{
SqlConnection sqlconn = new SqlConnection();
sqlconn.ConnectionString = "Initial Catalog=RKM;Data Source=localhost;User id='RKMUser';Password='RKMorKoss2003';";

System.Data.SqlClient.SqlCommand sqlcomm = new System.Data.SqlClient.SqlCommand(strSQL,sqlconn);

sqlconn.Open();
ExecSQLResults = sqlcomm.ExecuteNonQuery();
sqlconn.Close ();

return ExecSQLResults;
}

I've striped out all the irrelvant code in an attempt to make it more readable (so you will have to excuse the rather sparce error handling)

The stored procedure is as follows.

CREATE Procedure up_SaveNewAlert
@strEmail varchar (255),
@strAlertName varchar(255),
@strAlertDescription varchar(255),
@strAlertMethod varchar(255),
@AlertEventId int,
@AlertID int,
@MinutesDelayBefore int,
@MinutesBetweenAlerts int,
@NumberOfAlerts int,
@strStatusType int,
@strMessageText varchar(255),
@strSMSCountry varchar(50),
@Customer varchar(255)
AS
insert into AlertInformationTbl
(alertType,
GSMPhoneNumber,
AlertName,
AlertDescription,
ErrorLevelID,
AlertEventID,
MinutesDelayBefore,
MinutesBetweenAlerts,
NumberOfAlerts,
MessageText,
SMSCountry,
Customer)
values
( @strAlertMethod,
@strEmail,
@strAlertName,
@strAlertDescription,
@strStatusType,
@AlertEventId,
@MinutesDelayBefore,
@MinutesBetweenAlerts,
@NumberOfAlerts,
@strMessageText,
@strSMSCountry,
@Customer)

Hope this spreads some light. Thanks

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-03-06 : 10:41:37

The data types between your table and proc don't match.

kevin.may
Starting Member

3 Posts

Posted - 2006-03-06 : 10:57:13

I've changed the stored procedure so the paramters types match.

. I still get the same problem though. I had this before as well with a different method and sp.

Any other ideas?

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-06 : 11:10:48

try using the paramaters as they are supposed to be used and not like that.
add parameters to the parameter collection, don't bulid a SQL string to execute.
that might be your problem.

Go with the flow & have fun! Else fight the flow

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-03-07 : 03:27:53

I agree with spirit: pass the parameters as parameters.
However, one reason it doesn't work is because the literal string parameters you're building are all varchar. Putting an N before the first single-quote of each of string parameters would make them nvarchar.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources