September 4, 2014 at 7:11 am
I tried a bunch a ways and can't figure this out
The FirstName and LastName values being passed in are in cyrillic text.
How do I add the N prefix in the update statement in order to store the text correctly.
I've tried FirstName = N@Firstname
or FirstName = '''N''' + @FirstName + '''
[dbo].[sp_UpdateDealerPeopleInfo](
@PersonId char(9),
@OriginalSID char(9),
@FirstName Varchar(50),
@LastName Varchar(50),
@ShipAddressLine1 Varchar(50),
@ShipAddressLine2 Varchar(50),
@City Varchar(50),
@State Char(2),
@ZipCode Varchar(10),
@Email Varchar(256),
@CanSpin Bit,
@RoleId Varchar(3),
@UpdateUser Varchar(50),
@DealershipId Char(5))
As
Begin
UPDATE Person
SET PersonId = @PersonId,
FirstName = @FirstName,
LastName = @LastName,
ShipAddressLine1 = @ShipAddressLine1,
ShipAddressLine2 = @ShipAddressLine2,
City = @City,
State = @State,
ZipCode = @ZipCode,
Email = @Email,
CanSpin = @CanSpin,
UpdateUser = @PersonId,
RoleId = @RoleId,
UpdateDate = getdate()
WHERE PersonId = @OriginalSID
End
September 4, 2014 at 7:13 am
lefevers (9/4/2014)
I tried a bunch a ways and can't figure this outThe FirstName and LastName values being passed in are in cyrillic text.
How do I add the N prefix in the update statement in order to store the text correctly.
I've tried FirstName = N@Firstname
or FirstName = '''N''' + @FirstName + '''
[dbo].[sp_UpdateDealerPeopleInfo](
@PersonId char(9),
@OriginalSID char(9),
@FirstName Varchar(50),
@LastName Varchar(50),
@ShipAddressLine1 Varchar(50),
@ShipAddressLine2 Varchar(50),
@City Varchar(50),
@State Char(2),
@ZipCode Varchar(10),
@Email Varchar(256),
@CanSpin Bit,
@RoleId Varchar(3),
@UpdateUser Varchar(50),
@DealershipId Char(5))
As
Begin
UPDATE Person
SET PersonId = @PersonId,
FirstName = @FirstName,
LastName = @LastName,
ShipAddressLine1 = @ShipAddressLine1,
ShipAddressLine2 = @ShipAddressLine2,
City = @City,
State = @State,
ZipCode = @ZipCode,
Email = @Email,
CanSpin = @CanSpin,
UpdateUser = @PersonId,
RoleId = @RoleId,
UpdateDate = getdate()
WHERE PersonId = @OriginalSID
End
Change the parameters.
e.g.
ALTER PROCEDURE [dbo].[sp_UpdateDealerPeopleInfo]
(
@PersonId CHAR(9),
@OriginalSID CHAR(9),
@FirstName NVARCHAR(50),
@LastName NVARCHAR(50),
@ShipAddressLine1 VARCHAR(50),
@ShipAddressLine2 VARCHAR(50),
@City VARCHAR(50),
@State CHAR(2),
@ZipCode VARCHAR(10),
@Email VARCHAR(256),
@CanSpin BIT,
@RoleId VARCHAR(3),
@UpdateUser VARCHAR(50),
@DealershipId CHAR(5)
)
AS
BEGIN;
UPDATE Person
SET PersonId = @PersonId,
FirstName = @FirstName,
LastName = @LastName,
ShipAddressLine1 = @ShipAddressLine1,
ShipAddressLine2 = @ShipAddressLine2,
City = @City,
State = @State,
ZipCode = @ZipCode,
Email = @Email,
CanSpin = @CanSpin,
UpdateUser = @PersonId,
RoleId = @RoleId,
UpdateDate = GETDATE()
WHERE PersonId = @OriginalSID;
END;
September 4, 2014 at 7:17 am
That still didn't work
Puts ????? in firstname field
September 4, 2014 at 7:18 am
lefevers (9/4/2014)
That still didn't workPuts ????? in firstname field
What is the DDL of your table?
September 4, 2014 at 7:20 am
CREATE TABLE [dbo].[Person](
[PersonId] [char](9) NOT NULL,
[Password] [varchar](10) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
September 4, 2014 at 7:27 am
Full table DDL, please - we need to be able to see constraints, indexes and so on.
Also, what syntax are you using to call the stored procedure and what application are you calling it from (SSMS, SSIS package, etc)?
Thanks
John
September 4, 2014 at 7:51 am
This is being called from an asp.net application
/****** Object: Table [dbo].[Person] Script Date: 09/04/2014 09:47:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Person](
[PersonId] [char](9) NOT NULL,
[Password] [varchar](10) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[ShipAddressLine1] [varchar](50) NULL,
[ShipAddressLine2] [varchar](50) NULL,
[City] [varchar](50) NULL,
[State] [char](2) NULL,
[ZipCode] [varchar](10) NULL,
[Email] [varchar](256) NULL,
[ReportsTo] [char](9) NULL,
[RoleId] [varchar](10) NULL,
[DealershipId] [char](7) NULL,
[Active] [bit] NULL,
[CanSpin] [bit] NULL,
[CreateUser] [varchar](50) NULL,
[CreateDate] [datetime] NULL,
[UpdateUser] [varchar](50) NULL,
[UpdateDate] [datetime] NULL,
[LastQuestionAnswered] [int] NULL,
[W9StatusCode] [char](3) NOT NULL,
[AreaId] [char](6) NULL,
[PersonIdDBA] [char](9) NULL,
[EnrollStatus] [char](1) NULL,
[ProgramCode] [int] NOT NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[PersonId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Person] WITH CHECK ADD CONSTRAINT [FK_Person_Area] FOREIGN KEY([AreaId])
REFERENCES [dbo].[Area] ([AreaId])
GO
ALTER TABLE [dbo].[Person] CHECK CONSTRAINT [FK_Person_Area]
GO
ALTER TABLE [dbo].[Person] WITH CHECK ADD CONSTRAINT [FK_Person_Dealership] FOREIGN KEY([DealershipId])
REFERENCES [dbo].[Dealership] ([DealershipId])
GO
ALTER TABLE [dbo].[Person] CHECK CONSTRAINT [FK_Person_Dealership]
GO
ALTER TABLE [dbo].[Person] WITH NOCHECK ADD CONSTRAINT [FK_Person_Person] FOREIGN KEY([ReportsTo])
REFERENCES [dbo].[Person] ([PersonId])
GO
ALTER TABLE [dbo].[Person] NOCHECK CONSTRAINT [FK_Person_Person]
GO
ALTER TABLE [dbo].[Person] WITH CHECK ADD CONSTRAINT [FK_Person_Program] FOREIGN KEY([ProgramCode])
REFERENCES [dbo].[Program] ([ProgramCode])
GO
ALTER TABLE [dbo].[Person] CHECK CONSTRAINT [FK_Person_Program]
GO
ALTER TABLE [dbo].[Person] WITH CHECK ADD CONSTRAINT [FK_Person_Role] FOREIGN KEY([RoleId])
REFERENCES [dbo].[Role] ([RoleId])
GO
ALTER TABLE [dbo].[Person] CHECK CONSTRAINT [FK_Person_Role]
GO
ALTER TABLE [dbo].[Person] ADD CONSTRAINT [DF_Person_Active] DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [dbo].[Person] ADD CONSTRAINT [DF_Person_CanSpin] DEFAULT ((1)) FOR [CanSpin]
GO
ALTER TABLE [dbo].[Person] ADD CONSTRAINT [DF_Person_W9StatusCode] DEFAULT ('TBD') FOR [W9StatusCode]
GO
September 4, 2014 at 7:55 am
What is the exact command that is being passed to SQL Server? What happens if you run the exact same command from SSMS?
John
September 4, 2014 at 8:45 am
Also, might be worth adding a "SELECT @FirstName, @LastName;" to the top of the sproc, to see how it gets passed in to SQL Server.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply