Endless subqueries

I have a table with two columns: OID and Cumulative (witch is the same type as OID)

Each OID can have one or more Cumulatives.

Example of data:

OID Cumulative

167 292

167 294

167 296

168 292

169 302

169 304

The cumulation of each OID don't stop at one cumulation, but can be endless (theoretical).

Example: 167->292->590

So the table would have on more row:

OID Cumulative

295 505

I would like to represent this strucuture in a tree view and I'm looking for a query that could give me a table with this structure:

OID Cumul1 Cumul2 Cuml3 Cuml4 .... Cumuln

in the way I can read the row and have as many child nodes as I have values in the columns. The number of columns depends on the row with most cumulations.

How can I do the query?

Is there a better way as my table with n columns?

Thanks for suggestions

Your sample data is confusing..Can you fix it. Let us know the sample output from the input (sample data) you provided.

Encryption ?

Does SQL Server use any type of encryption internally ?
eg. when I take backup of a database is it encrypted if yes then how many bit encryption does it use ?

When a client makes a connection to SQL 2000 server, does it use encryption ? if yes then how many bit encryption is it ?

Any help would be appreciated.

ThanksHi ,

sql server allows for encryption of data for both 16-bit and 32-bit clients with the encryption option of the Multi-Procotol Network Library.

to enable encryption you can select the "Enable multi-protocol encryption" checkbox in the sql server setup program.

hope this clarifies your doubt.

thanks

Encryption

We need to be able to encrypt very sensitive columns of data in a
quasi-warehouse type environment and, from what I've read, SS 2005 should
work quite well for this. But I need examples of how to implement this. I
know very little about encryption and so I need some easy-to-read examples of
how to encrypt/decrypt using stored procs, scripts and code in 2005. Where
can I find examples like this? Any sites, books, articles, links would be
much appreciated.CLM wrote:
> We need to be able to encrypt very sensitive columns of data in a
> quasi-warehouse type environment and, from what I've read, SS 2005 should
> work quite well for this. But I need examples of how to implement this. I
> know very little about encryption and so I need some easy-to-read examples of
> how to encrypt/decrypt using stored procs, scripts and code in 2005. Where
> can I find examples like this? Any sites, books, articles, links would be
> much appreciated.
There are some examples in Books Online...|||See Laurentiu's blog:
http://blogs.msdn.com/lcris/archive/category/10357.aspx
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e3lV0PUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> CLM wrote:
>> We need to be able to encrypt very sensitive columns of data in a
>> quasi-warehouse type environment and, from what I've read, SS 2005 should
>> work quite well for this. But I need examples of how to implement this.
>> I know very little about encryption and so I need some easy-to-read
>> examples of how to encrypt/decrypt using stored procs, scripts and code
>> in 2005. Where can I find examples like this? Any sites, books,
>> articles, links would be much appreciated.
> There are some examples in Books Online...

Encryption

CLM wrote:
> We need to be able to encrypt very sensitive columns of data in a
> quasi-warehouse type environment and, from what I've read, SS 2005 should
> work quite well for this. But I need examples of how to implement this.
I
> know very little about encryption and so I need some easy-to-read examples
of
> how to encrypt/decrypt using stored procs, scripts and code in 2005. Wher
e
> can I find examples like this? Any sites, books, articles, links would be
> much appreciated.
There are some examples in Books Online...See Laurentiu's blog:
http://blogs.msdn.com/lcris/archive/category/10357.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e3lV0PUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> CLM wrote:
> There are some examples in Books Online...|||We need to be able to encrypt very sensitive columns of data in a
quasi-warehouse type environment and, from what I've read, SS 2005 should
work quite well for this. But I need examples of how to implement this. I
know very little about encryption and so I need some easy-to-read examples o
f
how to encrypt/decrypt using stored procs, scripts and code in 2005. Where
can I find examples like this? Any sites, books, articles, links would be
much appreciated.|||CLM wrote:
> We need to be able to encrypt very sensitive columns of data in a
> quasi-warehouse type environment and, from what I've read, SS 2005 should
> work quite well for this. But I need examples of how to implement this.
I
> know very little about encryption and so I need some easy-to-read examples
of
> how to encrypt/decrypt using stored procs, scripts and code in 2005. Wher
e
> can I find examples like this? Any sites, books, articles, links would be
> much appreciated.
There are some examples in Books Online...|||See Laurentiu's blog:
http://blogs.msdn.com/lcris/archive/category/10357.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e3lV0PUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> CLM wrote:
> There are some examples in Books Online...

Encrypting SQL Server table columns

I have never used any type of encryption for SqL Server 2000, but I have recently been asked to research what would be involved to encrypt data like social security numbers.

Since I have no idea where to begin, I was wondering if someone could point in the right direction? Do I need to buy a third party tool to perform this, are security certificates involved? Overall, I need information on everything that I need to encrypt data using SQL Server 2000, and How to perform the tasks!!!

Thank You!

There is no built in data level encryption in SQL Server 2000 (there is in 2005) so you either need a 3rd party or to handle it in the App layer. The folks in the security group will likely have some pointers on 3rd party stuff.|||

As Euan already mentioned, I always liked the way doing encryption on the application / business layer in my application. You will have full control over your encryption and can easily hook some extra modules within if you want to. Otherwise, if you don′t have budget to built your own encryption layer or extend your current one, you can use thrid party tools. Those are normally presented by extended procedures which can be called for de-/encryption. Searching at Google for SQL Server +encryption will get you many hits for vendors of encryption solutions (Also use the adWords links on the right site)

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

Encrypting a data

Hi,

I have creating a stored procedure in sql server 2005 to encrypt a parameter value that comes from an asp.net page. this value is of type varchar.

Now i have declared a paramater as varbinary to accept this value in the stored procedure. And encrypting it using symmetric key which is encrypted by certificate.

Now when i enter a value into the textbox in asp.net page. it shows

"implicit conversion from varchar to varbinary not possible" pls tell me what is the solution? do i have to convert the value in vb code while accessing the value of the textbox or do i have to convert the value while passing the value when call stored procedure.

pls tell me the solution so that i don't jave to change any coding in vb.

some solution in sql server itselt.

Thank you

Gaurav

If you're passing a varchar to the stored procedure, then rewrite the stored procedure to accept a varchar.

Thanks
Laurentiu

|||

But i have to encrypt that value - the value that is coming from a varchar parameter.

And encryption works on varbinary data type. so do i need to convert that parameter to varbinary in the stored procedure.

Infact i even tried that but it is showing the same result implicit conversion not allowed.

Pls give me a solution. where i could pass a varchar data type value to a stored procedure and encrypt it in the stored procdure.

Thanks

Gaurav

|||

You can encrypt varchar values; it is not true that encryption works only on varbinary data. See the BOL article for EncryptByKey:

http://msdn2.microsoft.com/en-us/library/ms174361(SQL.90).aspx

varchar is explicitly listed as an accepted type for the cleartext data, so you just need to rewrite your procedure to accept a varchar value. Converting the varchar data to varbinary in the procedure does not help, because the problem happens earlier when you pass the varchar value to the procedure that expects a varbinary. If you don't want to change the procedure code to expect a varchar, then you have to explicitly cast the varchar to varbinary before you pass it to the procedure.

For an example that encrypts varchar data, see http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx. There are other examples on my blog that encrypt varchar data besides that one.

Thanks
Laurentiu

Encrypted value shown as '?' in a column of type varchar

Dear All,

I inserted a record in table on DB created on SQLServer 2005 and found out that the one of the column values is shown as '?' instead of showing the encrypted value that I sent with the insert statement.3

............................ Can anyone tell me how to get rid of this?

Thanks and regards,

Z Z.

How are you encrypting the data and how are you retrieving it?|||Thanks for your reply. Actually I'm using only one-way encryption and seen these '?' through SQL Server Management Studio by directly viewing the table contents.|||So what are you expecting to see returned if you are using one way encryption?|||

I was expecting to see encrypted value when I opened the database table directly from within the SQL Server Management Studio. Instead I found '?' only. Anyway, I am done with it and used two encryption mechanism. Thanks a lot.

Enable logging for merge agent

Hi,
how can I enable output logging for merge agent if subscriptions type is
anonymous (merge replication between sql server 2000 and sql server ce 2.0)?
Marius,
logging is a bit different with CE. Have a look at the section entitled SQL Server CE Server Agent Logging on this MSDN page:http://msdn.microsoft.com/library/de...bleconnect.asp
HTH,
Paul Ibison