Decryption Not Working

Feb 5, 2008

I have been testing two way symmetrical encryption. I have been able to encrypt rows of a spacific column using the following UPDATE statement, however, when I try and decrypt them, the query returns Null for the effected rows.

Can anyone see where I may have gone wrong in the decryption statement?UPDATE tblReceipts SET tblReceipts.CCNumber = EncryptByPassPhrase('Abracadabra!1234$',tblReceipts.CCNumber)
FROM tblReceipts
WHERE tblReceipts.CreditCardType > 0

Decrypt Does Not Work?????

SELECT CONVERT(varchar, DecryptByPassPhrase('Abracadabra!1234$',tblReceipts.CCNumber)) AS CCNumber
FROM tblReceipts
WHERE tblReceipts.CLIENTID = 15000My CCNumber field is nvarchar(20)My PassPhrase is just a test phraseThe data after it is encrypted in just a bunch of binary looking boxes  

View 6 Replies


ADVERTISEMENT

Decryption Not Working

Dec 2, 2007

I'm trying to put together a proof of concept utilizinf encryption with SQL Server and neded to get a little help. I've got the basics down but I'm not seeing the expected results. Here's the script that I'm running against a very simple table. I'm essentially just adding a coulmn to hose encrypted data, encrypting that data, and then selecting that data - in both encrypted and decrypted form:




Code Block
CREATE CERTIFICATE LAND_ENCRYPT_ACCOUNT3
WITH SUBJECT = 'ExternalAccountID';
GO
CREATE SYMMETRIC KEY AccountID3_01
WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE LAND_ENCRYPT_ACCOUNT3;
GO
USE [LAND_ENCRYPT];
GO
-- Create a column in which to store the encrypted data.
ALTER TABLE Account
ADD EncryptedAccountID3 varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY AccountID3_01
DECRYPTION BY CERTIFICATE LAND_ENCRYPT_ACCOUNT3;
-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE Account
SET EncryptedAccountID3 = EncryptByKey(Key_GUID('AccountID3_01'), ExternalAccountID);
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data.
OPEN SYMMETRIC KEY AccountID3_01
DECRYPTION BY CERTIFICATE LAND_ENCRYPT_ACCOUNT3;
GO
-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
SELECT ExternalAccountID, EncryptedAccountID3
AS 'Encrypted ID Number',
CONVERT(nvarchar, DecryptByKey(EncryptedAccountID3))
AS 'Decrypted ID Number'
FROM Account;
GO


But when I run the "decrypt" the data, the only character that appears unencrypted is the very last character of each account number:




ExternalAccountID Encrypted ID Number Decrypted ID Number

-------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------

12345654444 0x007B9CA28582AC42B36EBD8DFF91434701000000E267255EC20E27E5B4AC03E68C508ADCFA2EB815C234116D54FEE639685D7ECF49BB200140952E45A246AFE83BF53444 ㈱�㘵��4

12312312312 0x007B9CA28582AC42B36EBD8DFF91434701000000DB3838A7C459A2F031B1FFECF2499647C7DE10B7A20171EFDEA68320F7996CCC1C6A45011E93361F9A93494F49311CB9 ㈱ㄳ㌲㈱ㄳ2

12345678901 0x007B9CA28582AC42B36EBD8DFF9143470100000057EEEC0B1E223819EBF8186F0CBFEF8FDF4BFF4C0DCE322FAB8735DC8EA144B5937FFCC745A7FC427282C493DFECD901 ㈱�㘵㠷〹1



The first value is the unencrypted account id, the second is the encrypted account id, the last is the decrypted account id. As you can see, the decrypted value isn't quite right.

I've tried the AES_256, DES, and AES_128 algorithms and they all yield the same result.

Am I missing something?

Also, if I'm going to be inserting into an encrypted column, do I need to do it via a view and a trigger, or can I just do a straight forward INSERT / UPDATE?

Thanks!!!!!

View 7 Replies View Related

Decryption

Aug 11, 2001

How to decrypt a Stored Procedure which has been encrypted in SQL Server 7.0

Thanks in Advance

Regards,

Karthik

View 1 Replies View Related

Decryption

Oct 11, 2007

I am using the below function written by PESO to encrypt/Decrypt the data for my SSN Numbers stored as a Clear Text

First I am Encrypting all my SSN's using this function and storing it in the database in a seperate Column,

Some thing like this..

SELECT DBACentral.dbo.fnEncDecRc4( '145678908' , 'ty6578dmp1203' )
OUPPUT
--------
âUÙN_{��

How to Decrypt ( âUÙN_{�� ) using the below function.

CREATE FUNCTION dbo.fnEncDecRc4
(
@Pwd VARCHAR(256),
@Text VARCHAR(8000)
)
RETURNSVARCHAR(8000)
AS

BEGIN
DECLARE@Box TABLE (i TINYINT, v TINYINT)

INSERT@Box
(
i,
v
)
SELECTi,
v
FROMdbo.fnInitRc4(@Pwd)

DECLARE@Index SMALLINT,
@i SMALLINT,
@j SMALLINT,
@t TINYINT,
@k SMALLINT,
@CipherBy TINYINT,
@Cipher VARCHAR(8000)

SELECT@Index = 1,
@i = 0,
@j = 0,
@Cipher = ''

WHILE @Index <= DATALENGTH(@Text)
BEGIN
SELECT@i = (@i + 1) % 256

SELECT@j = (@j + b.v) % 256
FROM@Box b
WHEREb.i = @i

SELECT@t = v
FROM@Box
WHEREi = @i

UPDATEb
SETb.v = (SELECT w.v FROM @Box w WHERE w.i = @j)
FROM@Box b
WHEREb.i = @i

UPDATE@Box
SETv = @t
WHEREi = @j

SELECT@k = v
FROM@Box
WHEREi = @i

SELECT@k = (@k + v) % 256
FROM@Box
WHEREi = @j

SELECT@k = v
FROM@Box
WHEREi = @k

SELECT@CipherBy = ASCII(SUBSTRING(@Text, @Index, 1)) ^ @k,
@Cipher = @Cipher + CHAR(@CipherBy)

SELECT@Index = @Index +1
END

RETURN@Cipher
END

GO

Thanks for any help

View 3 Replies View Related

Password-Decryption

Aug 11, 2000

Hi,
Every one
Can i Encrypt & Decrypt Data store in SQL SERVER table. If field is alredy Encripted, then how I can decrypt in SQL SERVER.

Thanks
Nirmal

View 3 Replies View Related

What Key To Use For DES3 Decryption

Apr 3, 2015

How do I tell SQL Server what key to use for DES3 decryption? I want to encrypt in Oracle and after ETL to SQL Server, be able to decrypt but keep a column's value encrypted in Oracle, SSIS and SQL side - only decrypting on the SQL Server side when needing to use the value.

View 1 Replies View Related

Decryption On Sql2005

Oct 16, 2006

What solutions are available?
Is there a way to read about from server's metadata?

Gus

View 1 Replies View Related

RC4 Encryption - Decryption

Dec 12, 2006

This function is used to initialize the seed for the RC4 algorithmCREATE FUNCTION dbo.fnInitRc4
(
@Pwd VARCHAR(256)
)
RETURNS @Box TABLE (i TINYINT, v TINYINT)
AS

BEGIN
DECLARE@Key TABLE (i TINYINT, v TINYINT)

DECLARE@Index SMALLINT,
@PwdLen TINYINT

SELECT@Index = 0,
@PwdLen = LEN(@Pwd)

WHILE @Index <= 255
BEGIN
INSERT@Key
(
i,
v
)
VALUES(
@Index,
ASCII(SUBSTRING(@Pwd, @Index % @PwdLen + 1, 1))
)

INSERT@Box
(
i,
v
)
VALUES(
@Index,
@Index
)

SELECT@Index = @Index + 1
END


DECLARE@t TINYINT,
@b SMALLINT

SELECT@Index = 0,
@b = 0

WHILE @Index <= 255
BEGIN
SELECT@b = (@b + b.v + k.v) % 256
FROM@Box AS b
INNER JOIN@Key AS k ON k.i = b.i
WHEREb.i = @Index

SELECT@t = v
FROM@Box
WHEREi = @Index

UPDATEb1
SETb1.v = (SELECT b2.v FROM @Box b2 WHERE b2.i = @b)
FROM@Box b1
WHEREb1.i = @Index

UPDATE@Box
SETv = @t
WHEREi = @b

SELECT@Index = @Index + 1
END

RETURN
ENDANd this function does the encrypt/decrypt partCREATE FUNCTION dbo.fnEncDecRc4
(
@Pwd VARCHAR(256),
@Text VARCHAR(8000)
)
RETURNSVARCHAR(8000)
AS

BEGIN
DECLARE@Box TABLE (i TINYINT, v TINYINT)

INSERT@Box
(
i,
v
)
SELECTi,
v
FROMdbo.fnInitRc4(@Pwd)

DECLARE@Index SMALLINT,
@i SMALLINT,
@j SMALLINT,
@t TINYINT,
@k SMALLINT,
@CipherBy TINYINT,
@Cipher VARCHAR(8000)

SELECT@Index = 1,
@i = 0,
@j = 0,
@Cipher = ''

WHILE @Index <= DATALENGTH(@Text)
BEGIN
SELECT@i = (@i + 1) % 256

SELECT@j = (@j + b.v) % 256
FROM@Box b
WHEREb.i = @i

SELECT@t = v
FROM@Box
WHEREi = @i

UPDATEb
SETb.v = (SELECT w.v FROM @Box w WHERE w.i = @j)
FROM@Box b
WHEREb.i = @i

UPDATE@Box
SETv = @t
WHEREi = @j

SELECT@k = v
FROM@Box
WHEREi = @i

SELECT@k = (@k + v) % 256
FROM@Box
WHEREi = @j

SELECT@k = v
FROM@Box
WHEREi = @k

SELECT@CipherBy = ASCII(SUBSTRING(@Text, @Index, 1)) ^ @k,
@Cipher = @Cipher + CHAR(@CipherBy)

SELECT@Index = @Index +1
END

RETURN@Cipher
END

Peter Larsson
Helsingborg, Sweden

View 20 Replies View Related

RC4 Encryption/Decryption

Jan 3, 2008

This is related to post :
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78552

got a issue with this one..im not sure why..

My results are as follows:

Select dbo.fnEncDecRc4('Orange12345', 'Hello123')
Output : ,Mgl
Select dbo.fnEncDecRc4('Orange12345', ',Mgl')
Output : M

i am not able to decrypt it. Any idea why this is hapenning? Does it has to do something with regional settings?

View 10 Replies View Related

Decryption Problem

May 10, 2006

I wanted to configure Report Server and publish my reports. I configured report server 2005 on win 2003 os. I prepared few reports and deployed them. When I tried to view this report thru ReportServer (http://localhost/ReportServer) from my local machine, I got the following error message:-
The encrypted value for the "UnattendedExecutionAccountPassword" configuration setting cannot be decrypted.

The report server has encountered a configuration error. See the report server log files for more information.
Can anyone help me in solving this!
I will highly appreciate it.

Thanks in advance,

hemal

View 11 Replies View Related

Encryption And Decryption

Jan 24, 2008



Hi,
Does any body have a stored procedure or a function I can use? What I need is to encrypt and decrypt a password using Tiny Encryption Algorithm, SO I have an encryption scalar valued function or sproc and similarly decryption function or sproc.Now I need rolling keys to encrypt and decrypt, so I have a table which has keys used for encryption and decryption and depending on the dtae the keys are different.So I alos need a sproc to retrieve the keys.If anybody has done it before or can point me to where can I go let me know?

Thanks

View 5 Replies View Related

Encryption &&amp; Decryption

Oct 30, 2006

Database Security, we are going to use AES 256 Symmetric Encryption. We will be using RSA for Asymmetric Key Encryption, 1024 Bits.

We got the code working for the seond case but for the first, WHEN:

CREATE SYMMETRIC KEY sym_Key WITH ALGORITHM =
AES_256 ENCRYPTION BY ASYMMETRIC KEY asym_Key
GO

THEN:

-- Msg 15314, Level 16, State 1, Line 1
-- Either no algorithm has been specified or the bitlength and the algorithm specified for the key are not available in this installation of Windows.

What can be the way out to be able to create the AES 256 Symmetric key.

View 1 Replies View Related

Decryption Of Stored Procedures

Feb 22, 2001

Is there anyway by which I can decrypt the encrypted Stored Procedures which the Previus DBA had written and its running in Production . I need to make some changes to the Stored Procedure. Is is possible

View 1 Replies View Related

Encryption/decryption Sql2k

Sep 17, 2003

I am planning to use XP_CRYPT for encrypting and decrypting cc#'s, passwords etc., at database level. Any suggestions or experiences on this. More info about this product at
http://www.activecrypt.com/faq.htm

View 12 Replies View Related

Security And Encryption And Decryption

Apr 21, 2007

I found that while using encryption and decryption by keys and certificates thsere is no security at all.

if we uses master key the sysadmin can decrypt



but if we use private key (encryption by password), how do we pass the password so that profiller didn't show it?

View 1 Replies View Related

SQL Error 15466: Decryption

Jun 15, 2007

Hi

I get a strange error on my SQL Server 2005 database when I try to start replication.

Here is the manipulation leading to my problem:
1. Install new Windows server 2003 and SQL Server 2005 STD, service pack 2.
Server is part of a domain.
SQL2k5 is running under the LocalSystem account on each machine.
2. Get a copy of yesterday's full backup and transaction logs from running production SQL2k5 server.
3. Restore master, model, msdb and other user databases.
4. Try to start replication using the wizard.
I get the following error message

Msg 15466 sp_addlinkedsrvlogin
An error occurred during decryption.
Msg 15185 no remote user distributor_admin


I suspect this problem has to do with master service keys but need some advice. I am not sure it is related to the master restore since it also occurs on the production machine.


What should I do with this error message ?



TIA



Stephane

View 1 Replies View Related

RijndaelManaged Decryption From SQL Server

Jan 17, 2008

Subject:RijndaelManaged decryption from SQL Server

Issue: I am trying to move the decryption code from C# to SQL Server 2005.


Web.config
...

<symmetricCryptoProviders>


<add algorithmType="System.Security.Cryptography.RijndaelManaged, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=xxxxxx" protectedKeyFilename="C:Keysfffff.key" protectedKeyProtectionScope="LocalMachine" type="Microsoft.Practices.EnterpriseLibrary.Security.Cryptography.SymmetricAlgorithmProvider, Microsoft.Practices.EnterpriseLibrary.Security.Cryptography, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null" name="RijndaelManaged"/>

</symmetricCryptoProviders>
...

.cs file
....

decString = Cryptographer.DecryptSymmetric("RijndaelManaged", encString);
....

View 6 Replies View Related

SQL Server Data Encryption And Decryption

Feb 19, 2008

Hi.
I have a SQL Server 2000 database that contains information I would like to encrypt. The information is a field inside a table, and I would like to encrypt this information using a key, and decrypt it in my asp.net application using that key and use the decrypted data.
Please tell me how this can be done, or direct me to an article or a link on the subject.
Thanks in advance.

View 2 Replies View Related

Stored Function Encryption And Decryption

Oct 30, 2007



I created stored function with encryption.
after i created i dont able to view the source code from system tables or any tool.
i have get back the original source code

note: i want to stored function not for stored procedure.

View 3 Replies View Related

Passing Key Decryption Password To Stored Procedure

Aug 7, 2006

I try to pass the key used to decrypt symmetric key to a stored procedure as a parameter like this:

OPEN SYMMETRIC KEY MyKey
DECRYPTION BY PASSWORD=@ keypassword;

I get an error message saying "Incorrect syntax near "@keypassword".
Is there something that I am missing?

View 1 Replies View Related

Password Encryption And Decryption Using Stored Procs

Dec 28, 2007

I would like to be able to store user network passwords in a database table and be able to encrypt and decrypt using stored procs. Could anyone give me a pointer on this.

Many thanks

View 1 Replies View Related

SQL Server 2008 :: TDE Enabled DB Stuck In Limbo During Decryption?

Mar 30, 2015

Sql server 2008R2 (SP2) Ent, PROD DB myDB was encrypted. During Release mistakenly (Vendor created script blames some settings in ...- actually does not matter) Decryption started (ALTER .. SET ENCRYPTION OFF) as we got from ErrorLog.

For some reason initial encryption scan was aborted and then mentioned command: ALTER ... OFF was issued again. What we have now (after 60 h of decryption- encryption took only 2.5 h)- is_encrypted = 0 in sys.databases, encryption_state = 5 (decryption in progress) in sys.dm_database_encryption_keys (percent_complete= 0). But it seems myDB is still encrypted- I made a backup of myDB and tried to read it (restore filelistonly) from other server (with no encryption)- failed- asked for key. Seems metadata was changed when initial scan during decryption started but then stuck and (if I am correct) decryption was never completed. Question- any similar experience? How we can fix meta- data, i.e. assuming that myDB is still encrypted we should have is_encrypted = 1 and encryption_state = 3 (encrypted).

View 2 Replies View Related

SQL Server 2008 :: Encryption / Decryption On Mirroring Database

May 4, 2015

I have created mirroring... one of the column is encrypted on mirror database and I can see the decrypted result when I do query when I actually logged into server (through remote connection) but when I use the same query through using SSMS from my laptop the query result come as the column is not decrypted,

View 0 Replies View Related

Removing Service Account From SQLServer2005MSSQLUser Causes Decryption Error

May 2, 2007

I am attempting to implement tighter security on my instances of SQL Server 2005. One of my tasks is to make sure that the service account for the SQL Server service has the minimum privileges necessary to run the service. I thought I had everything configured correctly, but then I realized that the "SQLServer2005MSSQLUser" Windows group was a member of the "sysadmin" fixed server role. I do not want the service account to be a sysadmin, so I removed the service account from this group.



Everything seemed to be working, until I received a call from one of our developers. He was attempting to execute a stored procedure, and he kept getting the following error: "An error occurred during decryption".



I looked up the error, and found out it is related to the service master key. I am using the same service account that I did when I installed SQL Server, so I am baffled as to why I am receiving this error. The error was resolved when I added the service account back to the "SQLServer2005MSSQLUser" Windows group and restarted the SQL Server service.



Do have any idea what might be happening here?

View 3 Replies View Related

Using Symmetric Key Problem With Encryption, Decryption Works Fine

May 4, 2006

Hey I had a table with a column of data encrypted in a format. I was able to decrypt it and then encrypt it using Symmetric keys and then updating the table column with the data. Now, there is a user sp which needs to encrypt the password for the new user and put it in the table. I'm not being able to make it work. I have this so far. Something somewhere is wrong. I dont know where. Please help Thanks. I used the same script to do the encryption initially but that was for the whole column. I need to see the encrypted version of the @inTargetPassword  variable. But it's not working. It doesn't give me an error but gives me wrong data...

 

 

declare @thePassword as varbinary(128)

,@inTargetPassword as varchar(255)

,@pwd3 as varchar(255)

,@theUserId bigint

set @theUserId= 124564

set @inTargetPassword = 'test'

OPEN SYMMETRIC KEY Key1

DECRYPTION BY CERTIFICATE sqlSecurity;

Select @pwd3=EncryptByKey(Key_GUID('Key1')

, @inTargetPassword, 1, HashBytes('SHA1', CONVERT( varbinary, [UserObjectId])))

from table1 where UserObjectId= @theUserId

close symmetric key Key1

View 6 Replies View Related

SQL Server 2008 :: Error Occurred During Service Master Key Decryption

Mar 13, 2015

I have a SP SQL server that uses Handshake for the web parts. I am getting an error on SharePoint about 'An error occurred during Service Master Key Decryption' inside the web parts of the page, everything else comes up, from what I have researched MS says go under SQL Configuration Manager and change the service account. Is this the correct course of action for this type of error? I am just having a hard time believing that changing the engines service account will stop this issue, this account is used on several SQL server with no issues.

MCSA SQL Server 2012

View 1 Replies View Related

SQL Security :: Encryption 2005 - User Defined Function For Encryption And Decryption

Oct 7, 2015

I have created two user defined functions for encryption and decryption using passphrase mechanism. When I call encryption function, each time I am getting the different values for the same input. While I searching a particular value, it takes long time to retrieve due to calling decryption function for each row.

best way to encrypt and decrypt using user defined functions.Below is the query which is taking long time.

SELECT ID FROM table WITH (NOLOCK)
                     WHERE dbo.DecodeFunction(column) = 'value'

When I try to use symetric or asymetric encryption, I am not able to put "OPEN SYMETRIC KEY" code in a function. So, I am using PassPhrase mechanism.

View 3 Replies View Related

Some Things Not Working In 2005 And Working In 2000

Mar 3, 2006

hi

I had a view in which I did something like this
isnull(fld,val) as 'alias'

when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005.
When I change the query to fld as 'alias' then it works ok in sql 2005 .
why ?? I still have sql 2000 (8.0) compatability.

Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???

any clues or answers ?? it is some configuration issue ?

Thanks in advance.

View 5 Replies View Related

Column Level Or Database Level Encryption/decryption....

Jan 16, 2008

I want to perform column level and database level encryption/decryption....
Does any body have that code written in C# or VB.NET for AES-128, AES-192, AES-256  algorithms...
I have got code for single string... but i want to encrypt/decrypt columns and sometimes the whole database...
Can anybody help me out...
If you have Store procedure in SQL for the same then also it ll do...
Thanks in advance

View 1 Replies View Related

Error 15466 An Error Occurred During Decryption

Nov 13, 2007



i cann't create the credential.
when i type the user and password. i get error 15466 An error occurred during decryption.
the first time i use this code
Use master
crate credential outs_cred with identity='DomainNameAdministrator',secret='xxxxkmn'


i will get error 15466 An error occurred during decryption.


but i use this code (no secret)
Use master
crate credential outs_cred with identity='DomainNameAdministrator'
i will create the Credential success.

Why??

Best regards,
Mr.Problem

View 10 Replies View Related

ExecuteNonQuery - Add Working/Update Not Working

Jan 7, 2004

I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...

This was my test:


Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)

Try
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId

cn.Open()
cmd.ExecuteNonQuery()

Label1.Text = "done"
cn.Close()

Catch ex As Exception
Label1.Text = ex.Message
End Try


When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.

I have looked at the stored procedures and the syntax is correct according to SQL Server.

Please I would appreciate any advice...

View 2 Replies View Related

WHERE NOT IN Not Working

Feb 23, 2008

Original suggestion for my problem was -  Select * from TableA where ID not in ( Select ID from TableB)
When I run the query below using the IN operator I get 227 records returned but when I use NOT IN I get zero records when I expect well over 10,000.  What am I missing?
using SQL 2000 server 
 SELECT    LinksInfo.L_ID, LinksInfo.C_ID, Companies.C_CompanyName, Companies.C_Email, Companies.C_CompanyEmailFROM         LinksInfo INNER JOIN Companies ON LinksInfo.C_ID = Companies.C_IDWHERE     (LinksInfo.L_ID IN           (SELECT ZL_ID FROM Location_Zip))
ZL_ID is not a primary key in Location_Zip

View 2 Replies View Related

Why Is This Not Working

Mar 25, 2008

Hello again,
I think im missing something here, i just cant find out what it is.
I have a temp table:
CREATE TABLE #tempSearch(tempID BIGINT IDENTITY(1,1) PRIMARY KEY,username NVARCHAR(20) COLLATE Finnish_Swedish_CI_AS,lastlogin DATETIME,signupdate DATETIME)
Now i am trying to retrieve some data for each user that is inside this tempSearch list and have an id over xxx (xxx = the value of the parameter @first_id):
SELECT @sql = 'SELECT profile_publicinfo.username, profile_publicinfo.gender, profile_publicinfo.signupdate, profile_profilephoto.imageurl, profile_profilephoto.alttext, settings_username.color, profile_publicinfo.lastloginFROM #tempSearch INNER JOIN dbo.profile_publicinfo ON profile_publicinfo.username = #tempSearch.usernameINNER JOIN dbo.settings_privateinfo ON settings_privateinfo.username = profile_publicinfo.usernameFULL OUTER JOIN dbo.profile_coolfacts ON profile_coolfacts.username = profile_publicinfo.username FULL OUTER JOIN dbo.profile_profilephoto ON profile_profilephoto.username = profile_publicinfo.usernameFULL OUTER JOIN dbo.settings_username ON settings_username.username = profile_publicinfo.usernameWHERE (profile_publicinfo.username IN (SELECT username FROM #tempSearch))AND #tempSearch.tempID >= @first_id'
SELECT @paramlist = '@first_id int'EXEC sp_executesql @sql, @paramlist, @first_id
I need to get the tempID from the tempSearch table in order to compare it with @first_id
When i run this i get the same username repeated like 30 times then it moves over to the next, when i debug the #tempSearch it looks fine, just the users that are suppose to be there.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved