Chaging SSN Format

This is a repost due to know response:
If I use this formula:
SELECT LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum, 4)
as SSN, Clients.First_Name + ' ' + Clients.Last_Name
FROM Clients
It recodes the SSN as I need it. How do I get this result to the UPDATE
function inside the main db?
Possibly:
Update Clients
Set LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum, 4)
I tried that code but get a syntax error. Any ideas?> Possibly:
> Update Clients
> Set LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum,
> 4)
Try:
UPDATE dbo.Clients
SET ssnum = LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' +
RIGHT(ssnum, 4)
However, I generally suggest storing unformatted data and instead format
strings in the application/report during presentation
Hope this helps.
Dan Guzman
SQL Server MVP
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:E9C89F84-1950-4670-A852-49033092BD83@.microsoft.com...
> This is a repost due to know response:
> If I use this formula:
> SELECT LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum,
> 4)
> as SSN, Clients.First_Name + ' ' + Clients.Last_Name
> FROM Clients
> It recodes the SSN as I need it. How do I get this result to the UPDATE
> function inside the main db?
> Possibly:
> Update Clients
> Set LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum,
> 4)
> I tried that code but get a syntax error. Any ideas?

Certificate not found

Hello,

I have two different instances of sql server 2005 but i get

Connection handshake failed. The certificate used by the peer is invalid due to the following reason: Certificate not found. State 89.

This is one of the two instances:

use master

--ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'dsjdkflJ435907NnmM#sX003'

create master key encryption by password = 'hello'

create certificate [Certificato2]

from file = 'c:\certs\TransportCert2.cer'

with private key (FILE='c:\certs\TransportCert2.pvk',

decryption by password='simone')

active for begin_dialog = ON

CREATE LOGIN [M02] WITH PASSWORD = 'wrPqYkr%bm3';

ALTER LOGIN [M02] DISABLE;

CREATE USER [M02] FROM LOGIN [M02];

GO

create certificate [Certificato1]

authorization [M02]

from file = 'c:\certs\TransportCert1.cer'

active for begin_dialog = ON

GO

USE PublisherdDB

--ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'dsjdkflJ435907NnmM#sX003'

create master key encryption by password = 'hello'

create certificate [CertificatoDialogo2]

from file = 'c:\certs\DialogCert2.cer'

with private key (FILE='c:\certs\DialogCert2.pvk',

decryption by password='simone')

active for begin_dialog = ON

CREATE USER [Proxy::IsDbLookupRequestServiceM02] WITHOUT LOGIN;

GO

create certificate [CertificatoDialogo1]

authorization [Proxy::IsDbLookupRequestServiceM02]

from file = 'c:\certs\DialogCert1.cer'

active for begin_dialog = ON

CREATE REMOTE SERVICE BINDING [RSB::IsDbLookupRequestServiceM02]

TO SERVICE 'IsDbLookupRequestServiceM02'

WITH USER = [Proxy::IsDbLookupRequestServiceM02],

ANONYMOUS = OFF;

GO

CREATE ROUTE [Route::IsDbLookupRequestServiceM02,D516E70B-59D6-4BF4-882A-BDA7ACD6EB07] WITH

SERVICE_NAME = 'IsDbLookupRequestServiceM02',

ADDRESS = 'tcp://PORTATILEXP:4022';

GO

GRANT SEND ON SERVICE::[IsDbLookupResponseService] TO [Proxy::IsDbLookupRequestServiceM02]

GO

USE MASTER

CREATE ENDPOINT [BROKER]

AUTHORIZATION [VIDEOSYSTEM\Simone_Farinea]

STATE=STARTED

AS TCP (LISTENER_PORT = 4033, LISTENER_IP = ALL)

FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED

, MESSAGE_FORWARD_SIZE = 10

, AUTHENTICATION = CERTIFICATE [Certificato2]

, ENCRYPTION = REQUIRED ALGORITHM RC4)

GRANT CONNECT ON ENDPOINT::[BROKER] TO [M02];

Here is the second one:

use master

--ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'hello'

create master key encryption by password = 'hello'

create certificate [Certificato1]

from file = 'c:\certs\TransportCert1.cer'

with private key (FILE='c:\certs\TransportCert1.pvk',

decryption by password='simone')

active for begin_dialog = ON

CREATE LOGIN [SIMONEX] WITH PASSWORD = 'wrPqYkr%bm3';

ALTER LOGIN [SIMONEX] DISABLE;

CREATE USER [SIMONEX] FROM LOGIN [SIMONEX];

GO

create certificate [Certificato2]

authorization [SIMONEX]

from file = 'c:\certs\TransportCert2.cer'

active for begin_dialog = ON

GO

USE vsi

--ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'hello'

create master key encryption by password = 'hello'

create certificate [CertificatoDialogo1]

from file = 'c:\certs\DialogCert1.cer'

with private key (FILE='c:\certs\DialogCert1.pvk',

decryption by password='simone')

active for begin_dialog = ON

CREATE USER [Proxy::IsDbLookupResponseService] WITHOUT LOGIN;

GO

create certificate [CertificatoDialogo2]

authorization [Proxy::IsDbLookupResponseService]

from file = 'c:\certs\DialogCert2.cer'

active for begin_dialog = ON

GRANT SEND ON SERVICE::[IsDbLookupRequestServiceM02] TO [Proxy::IsDbLookupResponseService]

GO

CREATE ROUTE [Route::IsDbLookupResponseService,88EB00C4-8CA9-4B45-9899-677AA70818B1] WITH

SERVICE_NAME = 'IsDbLookupResponseService',

ADDRESS = 'tcp://SIMONEX:4033';

GO

USE MASTER

CREATE ENDPOINT [BROKER]

AUTHORIZATION [VIDEOSYSTEM\Simone_Farinea]

STATE=STARTED

AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL)

FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED

, MESSAGE_FORWARD_SIZE = 10

, AUTHENTICATION = CERTIFICATE [Certificato1]

, ENCRYPTION = REQUIRED ALGORITHM RC4)

GRANT CONNECT ON ENDPOINT::[BROKER] TO [SIMONEX];

What's wrong in my code?

Many thanks.

Which server traces the handshake failed error? This error is related to the endpoint authentication, so the problem is from the certificates in [master] ([Certificato1] and [Certificato2]). Please make sure that the right certificates are deployed: look into master.sys.certificates and validate that the certificate thumbprints match between the two instances.

If I'd have to make a wild guess, I'd say: try adding a start/expiration date to the certificates you create. There is a problem with certs created in the eastern UTC time zones that makes the certs unusable for a numbers of hours if a start date is not provided. I believe Italy is GMT-1, so the problem would manifest as the handshake failing for 1 hour after the certs are created, then it would start working.

HTH,
~ Remus