Help! I need Decrypt Stored procedures

To a certain time atraz we having in a previous base you vary SP, and today
I
am needing. To put the same ones they had been encrypyted! Entering in
contact with the boss that in epoca made, it informed me that through the
sources I would consegueria to use the SP again. He happens that where was
the sources, I had a problem with the HD. Soon I need to Decrypt these
procedures that they are in the data base. Some of descriptografia had
success in few cases, others it of the one error message (below), and exclud
e
procedure. I tried this of link below, and follows the error:
http://searchsqlserver.techtarget.c...ket
=ETA
Erro:
Server: Msg 512, Level 16, State 1, Procedure DECRYPTSP2K, Line 22
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
It forgives for my English!hi,
may suggest to read following thread :
http://www.sql-server-performance.c...p?TOPIC_ID=3271
:-0
Regards
--
Andy Davis
Activecrypt Team
---SQL Server Encryption Software
http://www.activecrypt.com
"ygo" wrote:

> To a certain time atraz we having in a previous base you vary SP, and toda
y I
> am needing. To put the same ones they had been encrypyted! Entering in
> contact with the boss that in epoca made, it informed me that through the
> sources I would consegueria to use the SP again. He happens that where was
> the sources, I had a problem with the HD. Soon I need to Decrypt these
> procedures that they are in the data base. Some of descriptografia had
> success in few cases, others it of the one error message (below), and excl
ude
> procedure. I tried this of link below, and follows the error:
>
> http://searchsqlserver.techtarget.c...k
et=ETA
>
> Erro:
> Server: Msg 512, Level 16, State 1, Procedure DECRYPTSP2K, Line 22
> Subquery returned more than 1 value. This is not permitted when the subque
ry
> follows =, !=, <, <= , >, >= or when the subquery is used as an expression
.
>
> It forgives for my English!

HELP! Case insensitive database on case sensitive server

We need to install CI database on CS server, and there are some issues
with stored procedures.

Database works and have CI collation (Polish_CI_AS). Server has
coresponding CS collation (Polish_CS_AS). Most queries and procedures
works but some does not :-(

We have table Customer which contains field CustomerID.

Query "SELECT CUSTOMERID FROM CUSTOMER" works OK regardless of
character case (we have table Customer not CUSTOMER)

Following TSQL generate error message that must declare variable @.id
(in lowercase)

DECLARE @.ID INT (here @.ID in uppercase)
SELECT @.id=CustomerID FROM Customer WHERE ... (here @.id in lowercase)

I know @.ID is not equal to @.id in CS, but database is CI and table
names Customer and CUSTOMER both works. This does not work for
variables.

I suppose it is tempdb collation problem (CS like a server collation
is). I tried a property "Identifier Case Sensitivity" for my
connection, but it is read only and have value 8 (Mixed) by default -
this is OK I think.

DO I MISS SOMETHING ??aki999 (aki999@.wp.pl) writes:
> We need to install CI database on CS server, and there are some issues
> DECLARE @.ID INT (here @.ID in uppercase)
> SELECT @.id=CustomerID FROM Customer WHERE ... (here @.id in lowercase)
> I know @.ID is not equal to @.id in CS, but database is CI and table
> names Customer and CUSTOMER both works. This does not work for
> variables.
> I suppose it is tempdb collation problem (CS like a server collation
> is). I tried a property "Identifier Case Sensitivity" for my
> connection, but it is read only and have value 8 (Mixed) by default -
> this is OK I think.
> DO I MISS SOMETHING ??

I will have to admit that I was under the impression that it was the
database collation that controlled the sensitivity of variables,
but apparently it is the system collation, so it appears that you
will have to install a case-insensitive instance, assuming that the
code you have problems with is in stored procedures.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Database collation do not controll variables name case sensitivity.
Server collation do. Any other object (tables, fields, etc.) names are
case insensitive in described configuration.

Unfortunatelly installing a CI instance is not a good solution, because
that is licensing problem on MS SQL.
It is OK on MSDE, but You have to pay for license for full MS SQL.

I cannot change code of database (stored procedures, functions, etc.)
but I can modify code in client application. I can chenge connection
parameters and database access methods.

May be there are some database or connection parameters that I can
change to force this to work?|||aki999 (aki999@.wp.pl) writes:
> Database collation do not controll variables name case sensitivity.
> Server collation do. Any other object (tables, fields, etc.) names are
> case insensitive in described configuration.
> Unfortunatelly installing a CI instance is not a good solution, because
> that is licensing problem on MS SQL.
> It is OK on MSDE, but You have to pay for license for full MS SQL.

Is that really so? But licensing is definitely nothing I know well, so
I assume that you know what you are talking about.

> May be there are some database or connection parameters that I can
> change to force this to work?

Apparently not. (And a connetion parameter would be bad a bad idea,
since that could permit you stored a procedure that another process
would not be able to compile.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||So second instance (CI collation) is a one and only solution for now.
I will install named instance of MSDE next to SQL Server and will keep
trying to find other way or modify database application...

Help with writing a Stored Procedure

Hi there,
I would be really grateful with some assistance with this, i am 100% new to
SQL & Stored Procedures. I'm building an ASP web application, i need to be
able to update two fields in two related tables & could really do with some
assistance. The tables are tbCompany and tbemployee. tbemployee is related t
o
tbCompany through a field in tbemployee named CompanyId (int). The fields
that i need to update are CompanySuspendedAccount in tbCompany
EmpCompAccoutSuspended in tbemployee. Needless to say there could be multipl
e
records in tbemployee that are related to the one record in tbCompany.
Ideally i will need to write two stored procedures, the first that updates
all records to yes and the second to no.
I would be really grateful for some assitance with this - Many thanks
GarethAssuming some thing that you didn=B4t posted a SP could bve something
like this for you:
CREATE PROCEDURE UPDSuspendCode
(
@.CompanyId INT,
@.Suspend BIT
)
AS
BEGIN
DECLARE @.Error INT
BEGIN TRANSACTION
UPDATE tbCompany
SET CompanySuspendedAccount =3D @.Suspend
Where CompanyId =3D @.CompanyId
SET @.ERROR =3D @.@.Error
UPDATE tbemployee
SET EmpCompAccoutSuspended =3D @.Suspend
Where CompanyId =3D @.CompanyId
SET @.ERROR =3D @.Error + @.@.Error
IF @.Error > 0
BEGIN
RAISERROR('And Error has occured during updating the suspend
status',16,1)
ROLLBACK
END
ELSE
COMMIT
END
HTH, Jens Suessmeyer.|||Thanks Jene, it looks good to me, presumably here i am setting the value to
Suspend? So i could use @.yes BIT for the suspending of the accounts and @.no
BIT to unsuspend. Both the fields CompanySuspendedAccount &
EmpCompAccoutSuspended are nvarchar so presumably it would need to be as
above unless i change the database? Thanks for your help
Gareth
"Jens" wrote:

> Assuming some thing that you didn′t posted a SP could bve something
> like this for you:
> CREATE PROCEDURE UPDSuspendCode
> (
> @.CompanyId INT,
> @.Suspend BIT
> )
> AS
> BEGIN
> DECLARE @.Error INT
> BEGIN TRANSACTION
>
> UPDATE tbCompany
> SET CompanySuspendedAccount = @.Suspend
> Where CompanyId = @.CompanyId
> SET @.ERROR = @.@.Error
>
> UPDATE tbemployee
> SET EmpCompAccoutSuspended = @.Suspend
> Where CompanyId = @.CompanyId
> SET @.ERROR = @.Error + @.@.Error
> IF @.Error > 0
> BEGIN
> RAISERROR('And Error has occured during updating the suspend
> status',16,1)
> ROLLBACK
> END
> ELSE
> COMMIT
> END
>
> HTH, Jens Suessmeyer.
>|||"@.yes BIT " Thats what my @.Suspend is for, you don=B4t need a @.Suspende
AND @.Unsuspense because the twi BITs can never evaluate to true (can be
either switch on AND off, right ).
You don=B4t have to change the database because BIT a Integer data type
with the constraint of being 1, 0, or NULL, so implicit conversion will
be used.
DECLARE @.Suspense BIT
DECLARE @.SomeColumn NVARCHAR(200)
SET @.Suspense =3D 1
SET @.SomeColumn =3D @.Suspense
Print @.SomeColumn
HTH, jens Suessmeyer.