DecryptByPassPhrase not decrypting varchar columns after copying a database

I have an encrypted column of data that is encrypted by a passphrase. The passphrase was encrypted by a symetric key in a key pair. The passphrase also is stored in a table. I can get the passphrase as needed to encrypt/decrypt the columns. I copied the production database to a new database for development. Subsequently I had to create a new symmetric/asymmetic key pair and recreated my passphrase with the new key pair. Now the passphrase will decrypt a text column but it will not decrypt two other columns which are of type varchar in the database. Here is an example:

DECLARE @.pss varchar(30)
EXEC [dbo].[uspPassPhraseGet] @.pss OUTPUT

SELECT DISTINCT contactid, uissueid, createdby, created_dt
,CONVERT(varchar(max),DecryptByPassPhrase(@.pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.title), 1, CONVERT(varbinary, 23))) as title
,CONVERT(varchar(max),DecryptByPassPhrase(@.pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.description), 1, CONVERT(varbinary, 23))) as description
,CONVERT(varchar(max),DecryptByPassPhrase(@.pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.shortdesc), 1, CONVERT(varbinary, 23))) as shortdesc,
closed_dt, confidential, statusid, due_dt, deleted_dt,deletedbyid, highrisk, dbo.tbl_msg_app_legislativeinquiry.designator, dbo.tbl_ref_sys_status.description AS statusdesc
FROM dbo.tbl_msg_app_legislativeinquiry INNER JOIN
dbo.tbl_ref_sys_status ON statusid = dbo.tbl_ref_sys_status.ustatusid INNER JOIN
dbo.tbl_gbl_lkp_security ON uissueid = dbo.tbl_gbl_lkp_security.msgid AND
dbo.tbl_msg_app_legislativeinquiry.designator = dbo.tbl_gbl_lkp_security.designator

Like I said I can execute the uspPassPhraseGet stored procedure and I get my passphrase. It will correctly decrypt the dbo.tbl_msg_app_legislativeinquiry.description field which is great but the other two fields will not decrypt. When i copied the database over the encrypted fields do not display the same on the new database. The old database shows a box character followed by a bunch of junk (as expected). The new copied table on the new database shows only a single box (not the same as the original). Is there a known bug with copying a table with varchar fields that are encrypted to a new database? I tried to run a test and got the same result. I also tried to convert the varchar columns to text to see if that solved the problem and it didn't. The description field however is a text type column and it reads exactly as the original. The problem I think is that the Copy Database didn't actually copy my data correctly. How can I get the original encrypted data from the production into my development. I also tried just dropping the table and reimporting the table but that didnt take either. Scratching my head on this one.

Also this same code works correctly and as expected by decrypting the encrypted fields in production.|||

A couple of observations first:

(1) Why are you converting the columns to varchar(max)? That should not be necessary.
(2) What is the reason you are using the "1, convert(varbinary, 23)" arguments? I don't see how those could be helpful.

From you description, it appears that the data was mangled during the transfer. What is the type of the encrypted columns?

Also, given that this appears to be a copy database problem, your question may be better directed to the SQL Server Tools General forum. If the column contains a different value after the copy, the decryption is expected to fail.

Thanks
Laurentiu

|||

Mr. Cistofor,

1. It made sense at the time.
2. I must have added that to confuse myself and others later on (job security or bad programming - you decide).
3. Data mangled in transfer, yep thats what it be.
4. Wrong msg board. Sorry, I was in a hurry and I used writting the post as a way to think it through instead of prepping more before submission. My bad dog!

Thanks for the time, consideration and consultation,

Mike512

|||

Ok, you are of course free to write code as you wish, but I wanted to point out that you are doing unnecessary operations. (1) may not be costly, but for (2) you are forcing an additional hash computation per value, which is expensive and will degrade the performance of your queries - it also doesn't serve much purpose from a security point of view.

Thanks
Laurentiu

sql

Decreasing the varchar column sizes in a table

I have a table in my database and the table has almost 45 columns and the rowsize is 10468 bytes.in that most of the colums have varchar datatypes and and i think coz of poor knowledge of the data most of the columns with varchar data were given more column length. Now i want to decrease the size of those columns and to see the row size would be around 8k Bytes.If i do this now, does it affect the table performance much...Infact can i do this as there is lot of data (almost 2 million rows) in the table.If it is possible is there anything to be taken care before changing the column lenghts.

Thanks.Before doing anything, identify all NON-character fields, sum their storage up, and subtract the result and 8000 from 10468. What you get is the total number of characters that you would have to shrink your character-based fields by.

Next, do a SELECT [field_name Width]=max(datalength(field_name))... on all character-based fields, sum the result across all those fields, and see if you get 8000 or less after addint the sum of NON-character-based fields storage sizes to it. If the result is higher, - you will have to decide if you want to truncate data in some of your character fields.

If the latter is what you get, - consider normalizing the table. For example, if only some of the records contain values for a specific field, take the record key and that field and create a different table using the key as FK to the original (I hope you have a key).|||Here's a proc that might help you analyze your char columns.

Declare variable problem

DECLARE @.x varchar
Set @.x = (SELECT COUNT(*)FROM #t)
DECLARE '@.date' + @.x datetime
Lets say the count is 5
I want to DECLARE a datetime variable called @.date5
How do I do this?
Thanks in advancedThis would require dynamic sql, which would be either a pointless exercise
or a dynamic coding nightmare. What are you trying to accomplish? Why is
it important that the name also convey information/data?
"Jim Campau" <jim_campau@.bausch.com> wrote in message
news:uL7Y$g$IFHA.3356@.TK2MSFTNGP12.phx.gbl...
> DECLARE @.x varchar
> Set @.x = (SELECT COUNT(*)FROM #t)
> DECLARE '@.date' + @.x datetime
> Lets say the count is 5
> I want to DECLARE a datetime variable called @.date5
> How do I do this?
> Thanks in advanced
>|||I don't know how many dates (rows) are going to be returned and I need to be
able to dynamically decare a variable for each date returned.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:u0Xzn2$IFHA.580@.TK2MSFTNGP15.phx.gbl...
> This would require dynamic sql, which would be either a pointless exercise
> or a dynamic coding nightmare. What are you trying to accomplish? Why
> is
> it important that the name also convey information/data?
> "Jim Campau" <jim_campau@.bausch.com> wrote in message
> news:uL7Y$g$IFHA.3356@.TK2MSFTNGP12.phx.gbl...
>> DECLARE @.x varchar
>> Set @.x = (SELECT COUNT(*)FROM #t)
>> DECLARE '@.date' + @.x datetime
>> Lets say the count is 5
>> I want to DECLARE a datetime variable called @.date5
>> How do I do this?
>> Thanks in advanced
>>
>|||This sounds like a problem with your approach to solving the problem. Since
you used the term "rows", you probably should be thinking in terms of a
table. You can search the newsgroups for the many posts regarding dynamic
sql - if you want to go down that path.
There might be a set-based approach; one that is more appropriate for a
relational dbms. If you post some details about what you are trying to
accomplish, someone might be able to provide a different approach that is
better suited to the environment.
"Jim Campau" <jim_campau@.bausch.com> wrote in message
news:uqnPiWAJFHA.2956@.TK2MSFTNGP12.phx.gbl...
> I don't know how many dates (rows) are going to be returned and I need to
be
> able to dynamically decare a variable for each date returned.
>
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:u0Xzn2$IFHA.580@.TK2MSFTNGP15.phx.gbl...
> > This would require dynamic sql, which would be either a pointless
exercise
> > or a dynamic coding nightmare. What are you trying to accomplish? Why
> > is
> > it important that the name also convey information/data?
> >
> > "Jim Campau" <jim_campau@.bausch.com> wrote in message
> > news:uL7Y$g$IFHA.3356@.TK2MSFTNGP12.phx.gbl...
> >> DECLARE @.x varchar
> >> Set @.x = (SELECT COUNT(*)FROM #t)
> >>
> >> DECLARE '@.date' + @.x datetime
> >>
> >> Lets say the count is 5
> >> I want to DECLARE a datetime variable called @.date5
> >> How do I do this?
> >>
> >> Thanks in advanced
> >>
> >>
> >
> >
>|||Jim Campau wrote:
> I don't know how many dates (rows) are going to be returned and I
> need to be able to dynamically decare a variable for each date
> returned.
Can you explain the problem, the tables involved, a sample query, and
what you expect to be returned. It sounds like what you're trying to do
can be done a lot more easily.
--
David Gugick
Imceda Software
www.imceda.com

declare variable based on existing column

Is it possible to declare a variable based on an existing column?
Instead of:
DECLARE @.myvariable VARCHAR(20)
Use:
DECLARE @.myvariable mytable.mycolumn%type
WHERE
Table MYTABLE has column MYCOLUMN of data type VARCHAR(20)Not without doing the entire operation in dynamic SQL -- in other words, not
easily and probably not a great idea. Why would you want that
functionality?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:0DF1A88B-C787-4BA1-BA24-FFB7F4630E57@.microsoft.com...
> Is it possible to declare a variable based on an existing column?
> Instead of:
> DECLARE @.myvariable VARCHAR(20)
> Use:
> DECLARE @.myvariable mytable.mycolumn%type
> WHERE
> Table MYTABLE has column MYCOLUMN of data type VARCHAR(20)
>|||ORABLE has that functionality. I admit it's a nice thing, especially when
putting together smaller systems where data types change.
What about using a SQLVariant? I've rarely used that ... does that
Internally store the type (likea VB/JScript type deal)? If so, I'd immagine
that'd work in most scenarios ... not sure how it rates on the Best PRactice
scale though ...
-- Alex
"Adam Machanic" wrote:

> Not without doing the entire operation in dynamic SQL -- in other words, n
ot
> easily and probably not a great idea. Why would you want that
> functionality?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:0DF1A88B-C787-4BA1-BA24-FFB7F4630E57@.microsoft.com...
>
>|||"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:ACC91FFB-ADC5-4DD8-9966-05B78F22422C@.microsoft.com...
> What about using a SQLVariant? I've rarely used that ... does that
> Internally store the type (likea VB/JScript type deal)? If so, I'd
immagine
Yes, it's very similar to those languages' variant datatypes -- and just
like using them, it has lots of pitfalls... I wouldn't use it in production
code, personally, although I have found it useful in a few utility
operations -- but I had to work around a lot of problems, especially dealing
with datetime data.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||The larger the system, the more useful this is.
And its not even the data type changing, but more often the size of VARCHAR
fields for example. You design your system with LAST_NAME as VARCHAR(50),
then realize you need VARCHAR(1000). To make this change in SQL Server, it
seems that you would have to go through all your code to change any local
variables that are @.LAST_NAME. If you could base the declaration on the tabl
e
column, you would not have to do this.
In addition, it is good for global standardization of data types and sizes.
"Alex Papadimoulis" wrote:
> ORABLE has that functionality. I admit it's a nice thing, especially when
> putting together smaller systems where data types change.
> What about using a SQLVariant? I've rarely used that ... does that
> Internally store the type (likea VB/JScript type deal)? If so, I'd immagi
ne
> that'd work in most scenarios ... not sure how it rates on the Best PRacti
ce
> scale though ...
> -- Alex
> "Adam Machanic" wrote:
>|||sqlwish@.microsoft.com
Ask for DOMAINs, an ANSI SQL feature (Google for more info on it) -- it's
definitely at the top of my wishlist, too.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:332D49B5-1442-46BF-A821-77C6FF61D4BA@.microsoft.com...
> The larger the system, the more useful this is.
> And its not even the data type changing, but more often the size of
VARCHAR
> fields for example. You design your system with LAST_NAME as VARCHAR(50),
> then realize you need VARCHAR(1000). To make this change in SQL Server, it
> seems that you would have to go through all your code to change any local
> variables that are @.LAST_NAME. If you could base the declaration on the
table
> column, you would not have to do this.
> In addition, it is good for global standardization of data types and
sizes.
> "Alex Papadimoulis" wrote:
>
when
immagine
PRactice
words, not|||You could use sp_addtype to create your own types which are stored in
systypes, then you can define data table columns, sp and udf parameters, and
variables using the user defined types.
in addition, INFORMATION_SCHEMA.COLUMNS contains the columns DOMAIN_CATALOG,
DOMAIN_SCHEMA, and DOMAIN_NAME which can be used to tables that need to be
altered to accomodate the changed type definition. you can also do a search
on syscomments to find any additional objects that need to be recompiled in
addition to the table alterations.
note: sp_rename can be used to rename a user defined type.
"Bevo" wrote:
> The larger the system, the more useful this is.
> And its not even the data type changing, but more often the size of VARCHA
R
> fields for example. You design your system with LAST_NAME as VARCHAR(50),
> then realize you need VARCHAR(1000). To make this change in SQL Server, it
> seems that you would have to go through all your code to change any local
> variables that are @.LAST_NAME. If you could base the declaration on the ta
ble
> column, you would not have to do this.
> In addition, it is good for global standardization of data types and sizes
.
> "Alex Papadimoulis" wrote:
>|||"Brian Selzer" <BrianSelzer@.discussions.microsoft.com> wrote in message
news:23455E9D-7788-4229-80DB-DD46292346BD@.microsoft.com...
> You could use sp_addtype to create your own types which are stored in
> systypes, then you can define data table columns, sp and udf parameters,
and
> variables using the user defined types.
> in addition, INFORMATION_SCHEMA.COLUMNS contains the columns
DOMAIN_CATALOG,
> DOMAIN_SCHEMA, and DOMAIN_NAME which can be used to tables that need to be
> altered to accomodate the changed type definition. you can also do a
search
> on syscomments to find any additional objects that need to be recompiled
in
> addition to the table alterations.
Brian,
sp_addtype is deprecated in the next version of SQL Server. I recommend
that you do not use it. Unfortunately, those DOMAIN columns don't provide
full DOMAIN support -- the ANSI domains, as I understand them, operate
similarly to the types that can be added with sp_addtype, but with much
greater flexibility and bound constraints.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||>> the ANSI domains, as I understand them, operate similarly to the types
Other than offering a syntactic shorthand, what else can ANSI domains do?
Would it offer anything meaningful in terms of simplification, flexibility
and utility of existing ANSI standard built-in types? If T-SQL UDTs support
binding of constraints, wouldn't it obviate the need for ANSI domains, if
that is the lacking provision?
Anith|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OLLBtY1GFHA.3536@.TK2MSFTNGP14.phx.gbl...
> Other than offering a syntactic shorthand, what else can ANSI domains do?
> Would it offer anything meaningful in terms of simplification, flexibility
> and utility of existing ANSI standard built-in types? If T-SQL UDTs
support
> binding of constraints, wouldn't it obviate the need for ANSI domains, if
> that is the lacking provision?
Two things:
A) Correct me if I'm wrong but I know of no way to alter a T-SQL UDT.
The ANSI Standard does provide ALTER DOMAIN syntax. The OP in this
situation, it appears, wants his variables to be able to mimic the same
datatype used in the table -- even if that datatype should change (e.g. if
changing business requirements mean that the datatype needs to support 100
bytes instead of 50). Is that possible with a T-SQL UDT as they are
currently implemented?
B) My understanding is that the constraints bound to ANSI DOMAINs extend
to variables declared of a domain datatype... so if I define a domain
INTBETWEEN1AND10, which is an integer with a constraint that it must be
between 1 and 10, that will be enforced even for local variables of that
type. That's not the case with T-SQL UDTs, is it?
If I'm wrong on both of these counts, then I regret not making much
heavier use of T-SQL UDTs in my work to date -- I've pretty much ignored the
feature as I have been under the impression that it's inflexible and doesn't
add value.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

Declare in a view

hello,
I have a quick question, can you declare a varchar within a view?
the code at the bottom generate error: Incorrect syntax near the keyword
'declare'.
CODE:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*----
--
05/25/06 - RA : starting code to query data from shamrock db
----
--*/
ALTER VIEW vw_customer_usage_bgcolor
AS
declare @.loc_east varchar (20)
declare @.loc_west varchar (20)
set @.loc_west = (select sum(inv_loc.qty_on_hand) from inv_loc INNER
JOIN inv_mast ON ( inv_mast.inv_mast_uid = inv_loc.inv_mast_uid )
WHERE ( inv_mast.item_id not like '0%' and inv_mast.item_id not like
'0%' ) AND
( inv_mast.item_id like '_____-___-___' or inv_mast.item_id like
'_____-___' ) AND
( inv_mast.delete_flag = 'N' ) AND
(inv_loc.location_id='102230' )
)
set @.loc_east = (select sum(inv_loc.qty_on_hand) from inv_loc INNER
JOIN inv_mast ON ( inv_mast.inv_mast_uid = inv_loc.inv_mast_uid )
WHERE ( inv_mast.item_id not like '0%' and inv_mast.item_id not like
'0%' ) AND
( inv_mast.item_id like '_____-___-___' or inv_mast.item_id like
'_____-___' ) AND
( inv_mast.delete_flag = 'N' ) AND
(inv_loc.location_id='100001' )
)
declare @.B_color bit
set @.B_color =
(select case when @.loc_east > @.loc_west
then 0 --EAST
else 1 --WEST
end)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GONo, you cannot declare variables in a view. I think you want a stored
procedure or a table-valued function.
"ITDUDE27" <ITDUDE27@.discussions.microsoft.com> wrote in message
news:F8A6A242-7A31-4C7B-A629-A54C97124604@.microsoft.com...
> hello,
> I have a quick question, can you declare a varchar within a view?
> the code at the bottom generate error: Incorrect syntax near the keyword
> 'declare'.
> CODE:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> /*---
--
> 05/25/06 - RA : starting code to query data from shamrock db
> ----
--*/
> ALTER VIEW vw_customer_usage_bgcolor
> AS
> declare @.loc_east varchar (20)
> declare @.loc_west varchar (20)
> set @.loc_west = (select sum(inv_loc.qty_on_hand) from inv_loc INNER
> JOIN inv_mast ON ( inv_mast.inv_mast_uid = inv_loc.inv_mast_uid )
> WHERE ( inv_mast.item_id not like '0%' and inv_mast.item_id not like
> '0%' ) AND
> ( inv_mast.item_id like '_____-___-___' or inv_mast.item_id like
> '_____-___' ) AND
> ( inv_mast.delete_flag = 'N' ) AND
> (inv_loc.location_id='102230' )
> )
> set @.loc_east = (select sum(inv_loc.qty_on_hand) from inv_loc INNER
> JOIN inv_mast ON ( inv_mast.inv_mast_uid = inv_loc.inv_mast_uid )
> WHERE ( inv_mast.item_id not like '0%' and inv_mast.item_id not like
> '0%' ) AND
> ( inv_mast.item_id like '_____-___-___' or inv_mast.item_id like
> '_____-___' ) AND
> ( inv_mast.delete_flag = 'N' ) AND
> (inv_loc.location_id='100001' )
> )
> declare @.B_color bit
> set @.B_color =
> (select case when @.loc_east > @.loc_west
> then 0 --EAST
> else 1 --WEST
> end)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO|||ITDUDE27 wrote:
> hello,
> I have a quick question, can you declare a varchar within a view?
> the code at the bottom generate error: Incorrect syntax near the keyword
> 'declare'.
> CODE:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> /*---
--
> 05/25/06 - RA : starting code to query data from shamrock db
> ----
--*/
> ALTER VIEW vw_customer_usage_bgcolor
> AS
> declare @.loc_east varchar (20)
> declare @.loc_west varchar (20)
> set @.loc_west = (select sum(inv_loc.qty_on_hand) from inv_loc INNER
> JOIN inv_mast ON ( inv_mast.inv_mast_uid = inv_loc.inv_mast_uid )
> WHERE ( inv_mast.item_id not like '0%' and inv_mast.item_id not like
> '0%' ) AND
> ( inv_mast.item_id like '_____-___-___' or inv_mast.item_id like
> '_____-___' ) AND
> ( inv_mast.delete_flag = 'N' ) AND
> (inv_loc.location_id='102230' )
> )
> set @.loc_east = (select sum(inv_loc.qty_on_hand) from inv_loc INNER
> JOIN inv_mast ON ( inv_mast.inv_mast_uid = inv_loc.inv_mast_uid )
> WHERE ( inv_mast.item_id not like '0%' and inv_mast.item_id not like
> '0%' ) AND
> ( inv_mast.item_id like '_____-___-___' or inv_mast.item_id like
> '_____-___' ) AND
> ( inv_mast.delete_flag = 'N' ) AND
> (inv_loc.location_id='100001' )
> )
> declare @.B_color bit
> set @.B_color =
> (select case when @.loc_east > @.loc_west
> then 0 --EAST
> else 1 --WEST
> end)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
No, you can't declare variables inside a view... Try this instead:
SELECT
CASE WHEN loc_west > loc_east THEN 1 ELSE 0 END
FROM
(
SELECT
SUM(CASE WHEN inv_loc.location_id = '102230' THEN inv_loc.qty_on_hnd
ELSE 0 END) AS loc_west,
SUM(CASE WHEN inv_loc.location_id = '100001' THEN inv_loc.qty_on_hnd
ELSE 0 END) AS loc_east
FROM inv_loc
INNER JOIN inv_mast
ON inv_loc.inv_mast_uid = inv_mast.inv_mast_uid
WHERE inv_mast.item_id NOT LIKE 0%
AND inv_mast.item_id LIKE '_____-___-___'
AND inv_mast.delete_flag = 'N'
) sums_table|||Check out in BOL index the topic "create function"
Under that you have something called
"Multi-statement Table-valued Functions"
That can handle your requirement.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||>> I have a quick question, can you declare a varchar within a view? <<
If you had ever read the first five pages of a chapter on VIEWs in any
SQL, you would know that a VIEW is a virtual table constructed from a
SELECT statement with some options.
So that did not answer your question? If you couldnot be bothered to
read a definition, wouldn't an error message a "strong hint"?
I also see that you write with bits and delete flags, just like
assembly language. Just like we did in the 1960's before RDBMS. You
also put the silly "volkwagen" suffix on view names to violate
ISO-11179 rules. All of those things are signs of really bad DDL and
DML.
Do you notice anything interesting about this predicate? Like it is
redundant?
(Inv_Mast.item_id NOT LIKE '0%'
AND
Inv_Mast.item_id NOT LIKE '0%' )
What you have posted here implies a LOT of serious errors. Stop
programming, do a full data audit and get some help from an SQL
porgrammer.

Declare cursor based on dynamic query

Hi,

I am declaring the cursor based on a query which is generated dynamically. but it is not working

Declare @.tempSQL varchar(1000)

This query will be generated based on my other conditon and will be stored in a variable

set @.tempsql = 'select * from orders'

declare cursor test for @.tempsql

open test

This code is not working.

please suggest

Nitin

Hi

I am writing the code as below

Declare @.testSQl varchar(1000)

set @.testsql = 'select * from orders'

declare test1 cursor for @.testSQl

The declare statement is not working . My @.testsql will be generated at run time.

Help

Nitin

|||You can not use dynamic sql while opening the cursors..

it should be like this

Declare Test1 cursor for
Select * From Orders|||

You could add the cursor creation to your dynamic sql and then just call sp_executesql for the built up string. Something like...

DECLARE @.sql nvarchar(4000)

--Get beginning of cursor

SELECT @.sql = 'DECLARE c CURSOR FOR'

--Decision code for what query is built

SELECT @.sql = @.sql + 'SELECT * FROM orders'

--Remainder of cursor with specific columns from above query

SELECT @.sql = @.sql + 'OPEN c FETCH NEXT FROM c INTO ....'

--Execute the string we just built

EXEC sp_executesql @.sql

|||

I don't like to ever advocate the use of cursors, but you can do this using a global cursor, if you really must:

create procedure test
as
declare @.name nvarchar(128)
exec ('declare bob cursor global for select name from sys.objects')
open bob

fetch next from bob into @.name
select @.name as works
close bob
deallocate bob
go

test

|||

Hi,

I dont know for the moment how to declare a cursor on a query from a string.. I dont think its possible this way. An alternative is to find a solution other than using the cursor, else you'd lose development time in trying to find a solution.

If you cannot find a solution, try to explain the problem, someone will try help out, and also cursors generally tend to be less performant.

|||this is not possible. i agree with waaz|||

Instead of local cursor, you can create a Global cursor with dynamic sql, which is available beyond the scope the dynamic sql

like this

set @.sql='declare test cursor global for '+ @.tempsql

exec sp_executesql @.sql

open test

close test

|||You can use dynamic SQL to create a global cursor as shown in another reply in this thread. But what are you trying to do? Why do you need to use a cursor? And why do you need to use dynamic SQL? Both have performance implications. And dynamic SQL has serious security implications that can compromise your database system and/or network. You will have to use techniques (both in the database and client-side depending on how you call your SP) that avoid SQL injection to protect your database and network from malicious users. Apart from these problems, dynamic SQL requires more maintainence because you have to grant more permissions to end users since checks are deferred to run-time unlike SPs with static SQL statements. So it is easy to create a cursor dynamically but that is not the right thing to do in majority of the cases.|||

Also, try not to ask the same question twice. This question was also answered in another thread. I have merged the threads into one.

|||

hey whitney,

i got the same problem of dynamic query with cursors..

You gave the alternative but i got the big cursor and its difficult for me to put the entire stuff in string.

Because it gets difficult to maintain for me.

Any help or comment regarding this will be appreciated.

Thanks a ton!!

dromyl@.hotmail.com

dec to varchar

Hello,
I need some help with string functions.
I have these statements:
declare @.text varchar(30), @.quantity dec(32,12), @.date datetime
select @.text = 'qwerty', @.quantity = -123.123456, @.date = getdate()
select @.text + ';' + convert(varchar, @.quantity) + ';' + convert(char(10),
@.date, 121) as string
They give this result:
string
---
qwerty;-123.123456000000;2005-04-12
@.quantity is shown as -123.123456000000. Is there an easy way to get rid of
those tailing zeros?
EskoSorry, that does not help me.
My quantities can have any number of decimal digits between 0 and 12 and I
always want to get rid of all tailing zeros.
So 1.000000000000 should be shown as "1", -33.123456789010 as
"-33.12345678901", -0.112233440000 as "-0.11223344" and so on.
"mark baekdal" wrote:

> can you do this?
> declare @.text varchar(30), @.quantity dec(32,6), @.date datetime
> select @.text = 'qwerty', @.quantity = -123.123456, @.date = getdate()
> select @.text + ';' + convert(varchar, @.quantity) + ';' + convert(char(10)
,
> @.date, 121) as string
>|||or maybe this...
declare @.text varchar(30), @.quantity dec(32,12), @.date datetime
select @.text = 'qwerty', @.quantity = -123.123456, @.date = getdate()
select @.text + ';' + replace(rtrim(replace(convert(varchar,@.q
uantity),'0','
')),' ','0') + ';' + convert(char(10),
@.date, 121) as string
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Esko" wrote:

> Hello,
> I need some help with string functions.
> I have these statements:
> declare @.text varchar(30), @.quantity dec(32,12), @.date datetime
> select @.text = 'qwerty', @.quantity = -123.123456, @.date = getdate()
> select @.text + ';' + convert(varchar, @.quantity) + ';' + convert(char(10)
,
> @.date, 121) as string
> They give this result:
> string
> ---
> qwerty;-123.123456000000;2005-04-12
> @.quantity is shown as -123.123456000000. Is there an easy way to get rid
of
> those tailing zeros?
> Esko
>|||the next query should, just watch the formatting as it changed in the post.
The replace function, replaces '0' with a single space ' ' and then trims th
e
result and then fills any gaps with '0', so this (as far as I've tested)
always works?
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Esko" wrote:
> Sorry, that does not help me.
> My quantities can have any number of decimal digits between 0 and 12 and I
> always want to get rid of all tailing zeros.
> So 1.000000000000 should be shown as "1", -33.123456789010 as
> "-33.12345678901", -0.112233440000 as "-0.11223344" and so on.
>
> "mark baekdal" wrote:
>