Encrypt sproc still returns NULL's to non DBO's.

If someone would try out my script below I'd really appreciate it. Whenever
I run a decrypt sproc as a non DBO, it doesn't decrypt the data, despite the
fact that I use "with exec as owner" in the sproc and "exec as user = 'dbo'"
in the execution. All ideas are welcomed.
TIA, ChrisR
USE [AdventureWorks];
GO
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'vato'
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
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.
create procedure getDecryptedIDNumber
with exec as owner
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
GO
/*works for me, shows the decrypted data*/
exec getDecryptedIDNumber
USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OF
F
GO
USE [AdventureWorks]
GO
CREATE USER [test] FOR LOGIN [test]
GO
use [AdventureWorks]
GO
GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
GO
GRANT IMPERSONATE ON USER:: dbo TO test;
GO
/*Now, open up a "file/new/DB Engine Query" and login with the test login*/
exec as user = 'dbo'
exec getDecryptedIDNumber
/*This returns NULL values where it should show the decrypted data*/Hi Chris
I could not get you sample to produce the effect you say, but then I changed
the procedure to open/close the keys. You should have the keys open for as
short a time as possible
CREATE PROCEDURE getDecryptedIDNumber
WITH EXEC AS OWBER
AS
OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;
SELECT NationalIDNumber, EncryptedNationalIDNumber AS [Encrypted ID Numb
er],
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS [Decrypted
ID
Number]
FROM HumanResources.Employee;
CLOSE SYMMETRIC KEY SSN_Key_01;
GO
If this does not work you may want to post in
microsoft.public.sqlserver.security
A good source for encryption information is
http://blogs.msdn.com/lcris/archive/category/10357.aspx
http://blogs.msdn.com/lcris/archive.../13/512829.aspx will dop what you
but signs the procedure instead.
John
"ChrisR" wrote:

> If someone would try out my script below I'd really appreciate it. Wheneve
r
> I run a decrypt sproc as a non DBO, it doesn't decrypt the data, despite t
he
> fact that I use "with exec as owner" in the sproc and "exec as user = 'dbo
'"
> in the execution. All ideas are welcomed.
> TIA, ChrisR
>
> USE [AdventureWorks];
> GO
> IF NOT EXISTS
> (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
> CREATE MASTER KEY ENCRYPTION BY
> PASSWORD =
> 'vato'
> GO
> OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'
> CREATE CERTIFICATE HumanResources037
> WITH SUBJECT = 'Employee Social Security Numbers';
> GO
> CREATE SYMMETRIC KEY SSN_Key_01
> WITH ALGORITHM = DES
> ENCRYPTION BY CERTIFICATE HumanResources037;
> GO
> -- Create a column in which to store the encrypted data
> ALTER TABLE HumanResources.Employee
> ADD EncryptedNationalIDNumber varbinary(128);
> GO
> -- Open the symmetric key with which to encrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
> -- Encrypt the value in column NationalIDNumber with symmetric
> -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
> UPDATE HumanResources.Employee
> SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
> NationalIDNumber);
> GO
> -- Verify the encryption.
> -- First, open the symmetric key with which to decrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
> 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.
> create procedure getDecryptedIDNumber
> with exec as owner
> as
> SELECT NationalIDNumber, EncryptedNationalIDNumber
> AS "Encrypted ID Number",
> CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
> AS "Decrypted ID Number"
> FROM HumanResources.Employee;
> GO
> /*works for me, shows the decrypted data*/
> exec getDecryptedIDNumber
> USE [master]
> GO
> CREATE LOGIN [test] WITH PASSWORD=N'test',
> DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=
OFF
> GO
> USE [AdventureWorks]
> GO
> CREATE USER [test] FOR LOGIN [test]
> GO
> use [AdventureWorks]
> GO
> GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
> GO
> GRANT IMPERSONATE ON USER:: dbo TO test;
> GO
> /*Now, open up a "file/new/DB Engine Query" and login with the test login*
/
> exec as user = 'dbo'
> exec getDecryptedIDNumber
> /*This returns NULL values where it should show the decrypted data*/
>
>|||Good enough, thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6BD1F94F-E0F6-44EB-83FE-2C930BF1FF04@.microsoft.com...
> Hi Chris
> I could not get you sample to produce the effect you say, but then I
changed
> the procedure to open/close the keys. You should have the keys open for as
> short a time as possible
>
> CREATE PROCEDURE getDecryptedIDNumber
> WITH EXEC AS OWBER
> AS
> OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;
> SELECT NationalIDNumber, EncryptedNationalIDNumber AS [Encrypted ID
Number],
> CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS [Decrypted[/vbco
l]
ID[vbcol=seagreen]
> Number]
> FROM HumanResources.Employee;
> CLOSE SYMMETRIC KEY SSN_Key_01;
> GO
> If this does not work you may want to post in
> microsoft.public.sqlserver.security
> A good source for encryption information is
> http://blogs.msdn.com/lcris/archive/category/10357.aspx
> http://blogs.msdn.com/lcris/archive.../13/512829.aspx will dop what
you[vbcol=seagreen]
> but signs the procedure instead.
>
> John
> "ChrisR" wrote:
>
Whenever[vbcol=seagreen]
the[vbcol=seagreen]
'dbo'"[vbcol=seagreen]
CHECK_POLICY=OFF[vbcol=seagreen]
login*/[vbcol=seagreen]

Encrypt sproc still returns NULL's to non DBO's.

If someone would try out my script below I'd really appreciate it. Whenever
I run a decrypt sproc as a non DBO, it doesn't decrypt the data, despite the
fact that I use "with exec as owner" in the sproc and "exec as user = 'dbo'"
in the execution. All ideas are welcomed.
TIA, ChrisR
USE [AdventureWorks];
GO
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'vato'
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
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.
create procedure getDecryptedIDNumber
with exec as owner
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
GO
/*works for me, shows the decrypted data*/
exec getDecryptedIDNumber
USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks]
GO
CREATE USER [test] FOR LOGIN [test]
GO
use [AdventureWorks]
GO
GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
GO
GRANT IMPERSONATE ON USER:: dbo TO test;
GO
/*Now, open up a "file/new/DB Engine Query" and login with the test login*/
exec as user = 'dbo'
exec getDecryptedIDNumber
/*This returns NULL values where it should show the decrypted data*/Good enough, thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6BD1F94F-E0F6-44EB-83FE-2C930BF1FF04@.microsoft.com...
> Hi Chris
> I could not get you sample to produce the effect you say, but then I
changed
> the procedure to open/close the keys. You should have the keys open for as
> short a time as possible
>
> CREATE PROCEDURE getDecryptedIDNumber
> WITH EXEC AS OWBER
> AS
> OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;
> SELECT NationalIDNumber, EncryptedNationalIDNumber AS [Encrypted ID
Number],
> CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) AS [Decrypted
ID
> Number]
> FROM HumanResources.Employee;
> CLOSE SYMMETRIC KEY SSN_Key_01;
> GO
> If this does not work you may want to post in
> microsoft.public.sqlserver.security
> A good source for encryption information is
> http://blogs.msdn.com/lcris/archive/category/10357.aspx
> http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx will dop what
you
> but signs the procedure instead.
>
> John
> "ChrisR" wrote:
> > If someone would try out my script below I'd really appreciate it.
Whenever
> > I run a decrypt sproc as a non DBO, it doesn't decrypt the data, despite
the
> > fact that I use "with exec as owner" in the sproc and "exec as user ='dbo'"
> > in the execution. All ideas are welcomed.
> >
> > TIA, ChrisR
> >
> >
> > USE [AdventureWorks];
> > GO
> >
> > IF NOT EXISTS
> > (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
> > CREATE MASTER KEY ENCRYPTION BY
> > PASSWORD => > 'vato'
> > GO
> >
> > OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'
> >
> > CREATE CERTIFICATE HumanResources037
> > WITH SUBJECT = 'Employee Social Security Numbers';
> > GO
> >
> > CREATE SYMMETRIC KEY SSN_Key_01
> > WITH ALGORITHM = DES
> > ENCRYPTION BY CERTIFICATE HumanResources037;
> > GO
> >
> > -- Create a column in which to store the encrypted data
> > ALTER TABLE HumanResources.Employee
> > ADD EncryptedNationalIDNumber varbinary(128);
> > GO
> >
> > -- Open the symmetric key with which to encrypt the data
> > OPEN SYMMETRIC KEY SSN_Key_01
> > DECRYPTION BY CERTIFICATE HumanResources037;
> >
> > -- Encrypt the value in column NationalIDNumber with symmetric
> > -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
> > UPDATE HumanResources.Employee
> > SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
> > NationalIDNumber);
> > GO
> >
> > -- Verify the encryption.
> > -- First, open the symmetric key with which to decrypt the data
> > OPEN SYMMETRIC KEY SSN_Key_01
> > DECRYPTION BY CERTIFICATE HumanResources037;
> > 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.
> >
> > create procedure getDecryptedIDNumber
> > with exec as owner
> > as
> > SELECT NationalIDNumber, EncryptedNationalIDNumber
> > AS "Encrypted ID Number",
> > CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
> > AS "Decrypted ID Number"
> > FROM HumanResources.Employee;
> > GO
> >
> > /*works for me, shows the decrypted data*/
> >
> > exec getDecryptedIDNumber
> >
> > USE [master]
> > GO
> >
> > CREATE LOGIN [test] WITH PASSWORD=N'test',
> > DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
> > GO
> >
> > USE [AdventureWorks]
> > GO
> >
> > CREATE USER [test] FOR LOGIN [test]
> > GO
> >
> > use [AdventureWorks]
> > GO
> >
> > GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
> > GO
> >
> > GRANT IMPERSONATE ON USER:: dbo TO test;
> > GO
> >
> > /*Now, open up a "file/new/DB Engine Query" and login with the test
login*/
> > exec as user = 'dbo'
> > exec getDecryptedIDNumber
> >
> > /*This returns NULL values where it should show the decrypted data*/
> >
> >
> >

Encrypt sproc still returns NULL's to non DBO's.

If someone would try out my script below I'd really appreciate it. Whenever
I run a decrypt sproc as a non DBO, it doesn't decrypt the data, despite the
fact that I use "with exec as owner" in the sproc and "exec as user = 'dbo'"
in the execution. All ideas are welcomed.
TIA, ChrisR
USE [AdventureWorks];
GO
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'vato'
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
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.
create procedure getDecryptedIDNumber
with exec as owner
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
GO
/*works for me, shows the decrypted data*/
exec getDecryptedIDNumber
USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks]
GO
CREATE USER [test] FOR LOGIN [test]
GO
use [AdventureWorks]
GO
GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
GO
GRANT IMPERSONATE ON USER:: dbo TO test;
GO
/*Now, open up a "file/new/DB Engine Query" and login with the test login*/
exec as user = 'dbo'
exec getDecryptedIDNumber
/*This returns NULL values where it should show the decrypted data*/
Hi Chris
I could not get you sample to produce the effect you say, but then I changed
the procedure to open/close the keys. You should have the keys open for as
short a time as possible
CREATE PROCEDURE getDecryptedIDNumber
WITH EXEC AS OWBER
AS
OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;
SELECT NationalIDNumber, EncryptedNationalIDNumber AS [Encrypted ID Number],
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) AS [Decrypted ID
Number]
FROM HumanResources.Employee;
CLOSE SYMMETRIC KEY SSN_Key_01;
GO
If this does not work you may want to post in
microsoft.public.sqlserver.security
A good source for encryption information is
http://blogs.msdn.com/lcris/archive/category/10357.aspx
http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx will dop what you
but signs the procedure instead.
John
"ChrisR" wrote:

> If someone would try out my script below I'd really appreciate it. Whenever
> I run a decrypt sproc as a non DBO, it doesn't decrypt the data, despite the
> fact that I use "with exec as owner" in the sproc and "exec as user = 'dbo'"
> in the execution. All ideas are welcomed.
> TIA, ChrisR
>
> USE [AdventureWorks];
> GO
> IF NOT EXISTS
> (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
> CREATE MASTER KEY ENCRYPTION BY
> PASSWORD =
> 'vato'
> GO
> OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'
> CREATE CERTIFICATE HumanResources037
> WITH SUBJECT = 'Employee Social Security Numbers';
> GO
> CREATE SYMMETRIC KEY SSN_Key_01
> WITH ALGORITHM = DES
> ENCRYPTION BY CERTIFICATE HumanResources037;
> GO
> -- Create a column in which to store the encrypted data
> ALTER TABLE HumanResources.Employee
> ADD EncryptedNationalIDNumber varbinary(128);
> GO
> -- Open the symmetric key with which to encrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
> -- Encrypt the value in column NationalIDNumber with symmetric
> -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
> UPDATE HumanResources.Employee
> SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
> NationalIDNumber);
> GO
> -- Verify the encryption.
> -- First, open the symmetric key with which to decrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
> 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.
> create procedure getDecryptedIDNumber
> with exec as owner
> as
> SELECT NationalIDNumber, EncryptedNationalIDNumber
> AS "Encrypted ID Number",
> CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
> AS "Decrypted ID Number"
> FROM HumanResources.Employee;
> GO
> /*works for me, shows the decrypted data*/
> exec getDecryptedIDNumber
> USE [master]
> GO
> CREATE LOGIN [test] WITH PASSWORD=N'test',
> DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
> GO
> USE [AdventureWorks]
> GO
> CREATE USER [test] FOR LOGIN [test]
> GO
> use [AdventureWorks]
> GO
> GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
> GO
> GRANT IMPERSONATE ON USER:: dbo TO test;
> GO
> /*Now, open up a "file/new/DB Engine Query" and login with the test login*/
> exec as user = 'dbo'
> exec getDecryptedIDNumber
> /*This returns NULL values where it should show the decrypted data*/
>
>
|||Good enough, thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6BD1F94F-E0F6-44EB-83FE-2C930BF1FF04@.microsoft.com...
> Hi Chris
> I could not get you sample to produce the effect you say, but then I
changed
> the procedure to open/close the keys. You should have the keys open for as
> short a time as possible
>
> CREATE PROCEDURE getDecryptedIDNumber
> WITH EXEC AS OWBER
> AS
> OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;
> SELECT NationalIDNumber, EncryptedNationalIDNumber AS [Encrypted ID
Number],
> CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) AS [Decrypted
ID
> Number]
> FROM HumanResources.Employee;
> CLOSE SYMMETRIC KEY SSN_Key_01;
> GO
> If this does not work you may want to post in
> microsoft.public.sqlserver.security
> A good source for encryption information is
> http://blogs.msdn.com/lcris/archive/category/10357.aspx
> http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx will dop what
you[vbcol=seagreen]
> but signs the procedure instead.
>
> John
> "ChrisR" wrote:
Whenever[vbcol=seagreen]
the[vbcol=seagreen]
'dbo'"[vbcol=seagreen]
CHECK_POLICY=OFF[vbcol=seagreen]
login*/[vbcol=seagreen]

Empty string

MyCol is a varchar(1) column.
SELECT MyCol + '.' FROM tbl
returns ' .' (space+dot), when MyCol contains empty string.
Why? How can I make it to return '.' (with no space)?
I use SQL Server 2000, default settings.
Thanks.First of all, why are you using varchar(1)? You're wasting an extra byte
per row for nothing... Use CHAR(1).
Second, how are you determining that the output is ' .'? Is this happening
client-side? I cannot reproduce what you're talking about, using the
following:
declare @.table table(blah varchar(1))
insert @.table values ('')
select len(blah + '.')
from @.table
Can you post code to reproduce your problem?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> MyCol is a varchar(1) column.
> SELECT MyCol + '.' FROM tbl
> returns ' .' (space+dot), when MyCol contains empty string.
> Why? How can I make it to return '.' (with no space)?
> I use SQL Server 2000, default settings.
> Thanks.
>|||You can use any of LTRIM, REPLACE, SUBSTRING, STUFF, CASE, RIGHT or some
other string function to get this done. See the topic String functions in
SQL Server Books Online for details.
--
Anith|||>> Can you post code to reproduce your problem?
INSERT @.table SELECT SPACE(1) ;
--
Anith|||> when MyCol contains empty string.
What is your definition of an empty string? Can you show a repro? Like
Adam, I can't figure out whow you're doing this, unless you have a different
definition of "empty string" than I. I couldn't yield your result unless I
insert a space.
set nocount on
set concat_null_yields_null on
set ansi_padding off
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
insert #t values (NULL)
select blah+'.', len(blah + '.') from #t
drop table #t
go
set concat_null_yields_null off
set ansi_padding off
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
select blah+'.', len(blah + '.') from #t
drop table #t
go
set concat_null_yields_null on
set ansi_padding on
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
insert #t values (NULL)
select blah+'.', len(blah + '.') from #t
drop table #t
go
set concat_null_yields_null off
set ansi_padding on
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
select blah+'.', len(blah + '.') from #t
drop table #t
go
--
http://www.aspfaq.com/
(Reverse address to reply.)|||I believe the OP said it was an empty string?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uoK1udCyEHA.1956@.TK2MSFTNGP14.phx.gbl...
> >> Can you post code to reproduce your problem?
> INSERT @.table SELECT SPACE(1) ;
> --
> Anith
>|||I asked for a definition of empty string. To me, that's SPACE(0), not
SPACE(1).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#KGvagCyEHA.3400@.TK2MSFTNGP10.phx.gbl...
> I believe the OP said it was an empty string?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uoK1udCyEHA.1956@.TK2MSFTNGP14.phx.gbl...
> > >> Can you post code to reproduce your problem?
> >
> > INSERT @.table SELECT SPACE(1) ;
> >
> > --
> > Anith
> >
> >
>|||It appeared that MyCol contained a space instead of empty string. What
confused me was that LEN(MyCol) returned 0, when MyCol=' ' (one space).
So, a question is why LEN(' ') returns 0 and DATALENGTH(' ') returns 1?
Thanks.
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> MyCol is a varchar(1) column.
> SELECT MyCol + '.' FROM tbl
> returns ' .' (space+dot), when MyCol contains empty string.
> Why? How can I make it to return '.' (with no space)?
> I use SQL Server 2000, default settings.
> Thanks.
>|||From BOL:
LEN
Returns the number of characters, rather than the number of bytes, of the
given string expression, excluding trailing blanks.
I agree, that can get confusing!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:OSjkejCyEHA.4064@.TK2MSFTNGP10.phx.gbl...
> It appeared that MyCol contained a space instead of empty string. What
> confused me was that LEN(MyCol) returned 0, when MyCol=' ' (one space).
> So, a question is why LEN(' ') returns 0 and DATALENGTH(' ') returns 1?
> Thanks.
> "Vik" <viktorum@.==hotmail.com==> wrote in message
> news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> > MyCol is a varchar(1) column.
> >
> > SELECT MyCol + '.' FROM tbl
> >
> > returns ' .' (space+dot), when MyCol contains empty string.
> > Why? How can I make it to return '.' (with no space)?
> >
> > I use SQL Server 2000, default settings.
> >
> > Thanks.
> >
> >
>|||Here is a reason to use VARCHAR(1) instead of CHAR(1): when ANSI_PADDING is
set on. CHAR(1) will store SPACE(0) as space, whereas VARCHAR(1) will store
SPACE(0) as an empty string.
Sincerely,
Anthony Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OpZ3DaCyEHA.1452@.TK2MSFTNGP11.phx.gbl...
First of all, why are you using varchar(1)? You're wasting an extra byte
per row for nothing... Use CHAR(1).
Second, how are you determining that the output is ' .'? Is this happening
client-side? I cannot reproduce what you're talking about, using the
following:
declare @.table table(blah varchar(1))
insert @.table values ('')
select len(blah + '.')
from @.table
Can you post code to reproduce your problem?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> MyCol is a varchar(1) column.
> SELECT MyCol + '.' FROM tbl
> returns ' .' (space+dot), when MyCol contains empty string.
> Why? How can I make it to return '.' (with no space)?
> I use SQL Server 2000, default settings.
> Thanks.
>|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OpZ3DaCyEHA.1452@.TK2MSFTNGP11.phx.gbl...
> First of all, why are you using varchar(1)? You're wasting an extra byte
> per row for nothing... Use CHAR(1).
>
Originally MyCol was Char(1) and contained Nulls. Then it appeared that this
column should be used in a join, so I had to get rid of Nulls.
I also have a few Web pages (in ASP.NET) built in assumption that MyCol is
not blank. So, I decided to use Varchar(1) and an empty string for MyCol
instead of using a space and updating the queries or code with a Trim
function.
> Second, how are you determining that the output is ' .'? Is this
happening
> client-side? I cannot reproduce what you're talking about, using the
> following:
>
> declare @.table table(blah varchar(1))
> insert @.table values ('')
> select len(blah + '.')
> from @.table
>
> Can you post code to reproduce your problem?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Vik" <viktorum@.==hotmail.com==> wrote in message
> news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> > MyCol is a varchar(1) column.
> >
> > SELECT MyCol + '.' FROM tbl
> >
> > returns ' .' (space+dot), when MyCol contains empty string.
> > Why? How can I make it to return '.' (with no space)?
> >
> > I use SQL Server 2000, default settings.
> >
> > Thanks.
> >
> >
>|||Is your database set to a compatibility level of 65, perhaps? SQL
Server 6.5 could not store an empty string, if I recall correctly.
Otherwise, how do you know MyCol is empty? This behavior will result if
MyCol contains the value ' '. You can try rtrim(MyCol) to trim any
trailing spaces, but it would be best to find out what is going on.
Steve Kass
Drew University
Vik wrote:
>MyCol is a varchar(1) column.
>SELECT MyCol + '.' FROM tbl
>returns ' .' (space+dot), when MyCol contains empty string.
>Why? How can I make it to return '.' (with no space)?
>I use SQL Server 2000, default settings.
>Thanks.
>
>|||Vik wrote:
> It appeared that MyCol contained a space instead of empty string. What
> confused me was that LEN(MyCol) returned 0, when MyCol=' ' (one
> space).
> So, a question is why LEN(' ') returns 0 and DATALENGTH(' ') returns
> 1?
> Thanks.
> "Vik" <viktorum@.==hotmail.com==> wrote in message
> news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
>> MyCol is a varchar(1) column.
>> SELECT MyCol + '.' FROM tbl
>> returns ' .' (space+dot), when MyCol contains empty string.
>> Why? How can I make it to return '.' (with no space)?
>> I use SQL Server 2000, default settings.
>> Thanks.
Use DATALENGTH() for the real stored length.
create table #testing(col1 varchar(1))
insert into #testing values (space(1))
select len(col1) as 'len', datalength(col1) as 'datalength'
from #testing
len datalength
-- --
0 1
David Gugick
Imceda Software
www.imceda.com

Empty DataSet Problem

I am having problems with a report that has subreports nested 2 deep. If
the query for top level subreport returns an empty data set, a blank page
with the message "An internal error occurred on the report server. See the
error log for more details. (rsInternalError)" is displayed.
If the query in the top level subreport contains no rows, I want to hide it
and stop processing the subreport it contains. I have tried setting the
visibility of the table containing the top level subreport based on
CountRows() and have tried using the NoRows property to display a message
instead of the table. Neither of these works.
It seems like CountRows() does not work on empty data sets. In other
similar situations, I have made sure my data set returned at least one empty
row and tested for the data set containing more than one row but it seems
like there should be a better way.
Can someone please tell me how to hide the table and stop processing the
nested subreport so the error message isn't displayed?Hi Tom,
Welcome to use MSDN Managed Newsgroup Support.
From your description, my understanding of this issue is: You get the
internal error when the top level subreport gets empty dataset. If i
misunderstood your concern, please feel free to point it out.
What's the parameter you pass to the nested subreport?
I don't think hide the top level subreport will resolve the issue because
this error is occured when the report is processing.
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Tom,
No Rows property works great. Dont toggle using visiblity. Just write the
text on the No Rows property. When it doesn't find the records it displays
the text in the No Row property.
Amarnath
"Tom Bean" wrote:
> I am having problems with a report that has subreports nested 2 deep. If
> the query for top level subreport returns an empty data set, a blank page
> with the message "An internal error occurred on the report server. See the
> error log for more details. (rsInternalError)" is displayed.
> If the query in the top level subreport contains no rows, I want to hide it
> and stop processing the subreport it contains. I have tried setting the
> visibility of the table containing the top level subreport based on
> CountRows() and have tried using the NoRows property to display a message
> instead of the table. Neither of these works.
> It seems like CountRows() does not work on empty data sets. In other
> similar situations, I have made sure my data set returned at least one empty
> row and tested for the data set containing more than one row but it seems
> like there should be a better way.
> Can someone please tell me how to hide the table and stop processing the
> nested subreport so the error message isn't displayed?
>
>|||Wei Lu,
Since the top level subreport returns an empty data set, I have no idea what
is being passed to the nested subreport. I suspect that nothing (not NULL
but nothing) is being passed for the parameters. I removed the nested
subreport from the table and put in an expression to display the value
("Parameter: " + Fields!Id.Value) that would be used for the nested
subreport's parameter and the error message is displayed.
I need to find a way to stop processing the report when the empty data set
is returned by the query in the top level subreport. The top level
subreport cannot be displayed because there is no data for it and the nested
subreport's query cannot be run because there are no parameters to pass to
it. But, nothing I've tried, including the NoRows property, stops the
subreports from processing.
Do you have any suggestions?
Thanks,
Tom
"Wei Lu" <t-weilu@.online.microsoft.com> wrote in message
news:UF49vu$QGHA.940@.TK2MSFTNGXA03.phx.gbl...
> Hi Tom,
> Welcome to use MSDN Managed Newsgroup Support.
> From your description, my understanding of this issue is: You get the
> internal error when the top level subreport gets empty dataset. If i
> misunderstood your concern, please feel free to point it out.
> What's the parameter you pass to the nested subreport?
> I don't think hide the top level subreport will resolve the issue because
> this error is occured when the report is processing.
>
> Wei Lu
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> =====================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Amarnath,
Ever since we started running Reporting Service 2005, the NoRows property
seems to have stop working. I had used it sucessfully before converting to
2005 but it doesn't display anything now.
Have you heard anything about NoRows being broken in RS 2005?
Tom
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:A16C5558-32DD-4CF8-A5C4-2B3F1E2913B9@.microsoft.com...
> Hi Tom,
> No Rows property works great. Dont toggle using visiblity. Just write the
> text on the No Rows property. When it doesn't find the records it displays
> the text in the No Row property.
>
> Amarnath
>
> "Tom Bean" wrote:
>> I am having problems with a report that has subreports nested 2 deep. If
>> the query for top level subreport returns an empty data set, a blank page
>> with the message "An internal error occurred on the report server. See
>> the
>> error log for more details. (rsInternalError)" is displayed.
>> If the query in the top level subreport contains no rows, I want to hide
>> it
>> and stop processing the subreport it contains. I have tried setting the
>> visibility of the table containing the top level subreport based on
>> CountRows() and have tried using the NoRows property to display a message
>> instead of the table. Neither of these works.
>> It seems like CountRows() does not work on empty data sets. In other
>> similar situations, I have made sure my data set returned at least one
>> empty
>> row and tested for the data set containing more than one row but it seems
>> like there should be a better way.
>> Can someone please tell me how to hide the table and stop processing the
>> nested subreport so the error message isn't displayed?
>>|||Hi Tom,
Thanks for the update.
From your description, you memtioned that you can use NoRows to resolved
this issue in Reporting Service 2000, but it does not work in SSRS 2005. If
I misunderstood, please feel free to point it out.
Where's the NoRows property you set? For the subreport or the table which
contain the records?
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei Lu,
I have tried setting the NoRows property for both the subreport and the
table but neither works. That's what prompted me to ask if NoRows is broken
in RS2005.
The documentation clearly states "When the dataset for a data region returns
no data, the data region is not rendered. Instead, a text box is rendered
that displays the value of the NoRows property." and just as clearly, it
doesn't work for me.
Tom
"Wei Lu" <t-weilu@.online.microsoft.com> wrote in message
news:aYOB$BoRGHA.5524@.TK2MSFTNGXA03.phx.gbl...
> Hi Tom,
> Thanks for the update.
> From your description, you memtioned that you can use NoRows to resolved
> this issue in Reporting Service 2000, but it does not work in SSRS 2005.
> If
> I misunderstood, please feel free to point it out.
> Where's the NoRows property you set? For the subreport or the table which
> contain the records?
> Wei Lu
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> =====================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||I am having a similar problem and I think is connected to the re-rendering of
a page containing a sub-report with an empty dataset.
In my case I have a report consisting of a top part using a different
datasource with a (+) for more details. The other part has a table with data
from another datasource. This table also has a subreport that uses a field
for input.
The report works great when the second part has a valid dataset. When the
dataset is empty everything is OK until I press the (+) sign and the report
is re-drawn. Then I get the same error message as you. I tried some other
versions where data is split between different pages, the error immideately
comes if I go to page 2 which re-draws the screen.
I have seen several similar problems on this forum so I belive that it is an
annoying bug for which I can not find a workaround. If anyone can offer help
I would be very happy.
"Tom Bean" wrote:
> Wei Lu,
> I have tried setting the NoRows property for both the subreport and the
> table but neither works. That's what prompted me to ask if NoRows is broken
> in RS2005.
> The documentation clearly states "When the dataset for a data region returns
> no data, the data region is not rendered. Instead, a text box is rendered
> that displays the value of the NoRows property." and just as clearly, it
> doesn't work for me.
> Tom
> "Wei Lu" <t-weilu@.online.microsoft.com> wrote in message
> news:aYOB$BoRGHA.5524@.TK2MSFTNGXA03.phx.gbl...
> > Hi Tom,
> >
> > Thanks for the update.
> >
> > From your description, you memtioned that you can use NoRows to resolved
> > this issue in Reporting Service 2000, but it does not work in SSRS 2005.
> > If
> > I misunderstood, please feel free to point it out.
> >
> > Where's the NoRows property you set? For the subreport or the table which
> > contain the records?
> >
> > Wei Lu
> > Microsoft Online Partner Support
> >
> > Get Secure! - www.microsoft.com/security
> > =====================================================> > When responding to posts, please "Reply to Group" via your newsreader so
> > that others may learn and benefit from your issue.
> > =====================================================> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
>
>

Empty <> IS NOT NULL

Hello,

I have a query that returns the appropriate values I need, however there is one field I'd like to add and utilize but my problem is I only want to use it if it contains data.

If I filter it with IS NOT NULL it returns all the records, including the empty records. The field is simply empty, and doesn't come back as NULL. If I filter it with =' ' , it shows all the records with the empty records only.

I need to do the opposite, be able to filter it only if it's not empty.

Any help would be appreciated.

What is your current query? ( And indicate the field you're talking about... including its type, I guess...)

>L<

|||

Figured it out myself.

For someone elses benefit I'll include what I had to do:

Filtered the desired field using LIKE '0%' .

The data the field actually contains is a yearcode-sequence code (eg. 07-555555).

Now the empty fields are gone and it only pulls the records if it has data.

I used this for a guide. http://msdn2.microsoft.com/en-us/library/ms179859.aspx

|||Thanks for replying, I figured it out.