'Default' NON_EMPTY_BEHAVIOR behavior...

Hi,

The below query returns 20 rows when run against our production DW. When the NON_EMPTY_BEHAVIOR is removed, it returns 10 rows. Note the lack of a member list on the N_E_B. Anyone else seen this, or have any ideas as to why it's causing me a 'problem'?

WITH MEMBER

[Container].[Container Type].[AllCalc] AS [Container].[Container Type].[All],

NON_EMPTY_BEHAVIOR={}

SELECT

([Measures].[Container Load Count]) ON COLUMNS,

NON EMPTY

[Trade].[Trade].&[EAST_AFRICA] * [Container].[Container Number].[All].Children ON ROWS

FROM [DW_SM]

WHERE (

<various slicers>,

[Container].[Container Type].[AllCalc]

)

Thanks,

Will.

You should remove NON_EMPTY_BEHAVIOR from your calculated member, because it is set incorrectly. Specifying an empty set for NEB is FAAP always wrong. And really to get any benefit out of it in the form you are trying to use it, it should've only been used on calculated measure, not on calculated member.

decryptbykey multiple session issue

Hi

I'm having some issues using the decryptbykey method via multiple connections. When I run the below test script simultaneously on two machines the sum function is always less then the known amount (ie 14945490 and 36382777). Does anyone know of any locking method or alternative way to sum an encrypted column?

Thanks in advance

Waz

open symmetric key HR01 decryption by password='yes'
DECLARE @.Bonus decimal
DECLARE @.Salary decimal
DECLARE @.Errors int
DECLARE @.Success int
DECLARE @.LoopCount int
SET @.Errors = 0
SET @.Success = 0
SET @.LoopCount = 0

WHILE (@.LoopCount < 40)
BEGIN

SELECT
@.Bonus = SUM(convert(float,convert(varchar(80),decryptbykey(Bonus)))),
@.Salary = SUM(convert(float,convert(varchar(80),decryptbykey(Salary))))
FROM ChallengeEmployee
WHERE ChallengeID = 5

IF(@.Bonus <> 14945490 OR @.Salary <> 36382777)
BEGIN
PRINT 'Bonus ' + CAST(@.Bonus AS varchar(80))
PRINT 'Salary ' + CAST(@.Salary AS varchar(80))
SET @.Errors = @.Errors + 1
END
ELSE
SET @.Success = @.Success + 1

SET @.LoopCount = @.LoopCount + 1

END

PRINT 'Finish'
PRINT 'Errors ' + CAST(@.Errors AS varchar(80))
PRINT 'Success ' + CAST(@.Success AS varchar(80))
close symmetric key HR01

Unfortunately binary values cannot be converted/casted back to real/float data types. This limitation was also present in SQL Server 2000 (and from the information I could find, even in earlier versions). For detailed information on allowed cast/convert operations see http://msdn2.microsoft.com/en-us/library/ms187928.aspx.

I would recommend using a different data type if possible. Another workaround would be to cast to an intermediate data type (i.e. to a string), but this will cause data loss, and I would personally recommend against it because of the data loss potential. This seems to be the case in your particular scenario.

We really appreciate your feedback.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul

Thanks for the reply.

I don't think it's a cast issue. I am actually casting to a string in my function. The problem seems to be with running over multiple connections. Running the test script on a single machine works fine. If I run the test scripts below the actual and binary values always return the correct values. Only the Encrypted ones fail when running from two separate machines. (all works fine on one connection). Maybe it's how I'm handling the keys. Could a close statement on a connection effect another connections read?

Cheers

--########################################### Setup Data ###############################################

open symmetric key HR01 decryption by password='yes'
DECLARE @.LoopCount int
DECLARE @.Bonus decimal
DECLARE @.Salary decimal
DECLARE @.EmployeeID int

CREATE TABLE TestData
(
empId int,
BonusActual decimal,
SalaryActual decimal,
BonusBinary varbinary(64),
SalaryBinary varbinary(64),
BonusEncrypt varbinary(64),
SalaryEncrypt varbinary(64)
)

SET @.EmployeeID = 1000
SET @.Bonus = 20000
SET @.Salary = 60000
SET @.LoopCount = 0
WHILE (@.LoopCount < 1000)
BEGIN
INSERT INTO TestData (empId, BonusActual, SalaryActual, BonusEncrypt,SalaryEncrypt)
VALUES (
@.EmployeeID,
@.Bonus,
@.Salary,
EncryptByKey(Key_GUID('HR01'), (CAST(@.Bonus AS varchar(80)))),
EncryptByKey(Key_GUID('HR01'), (CAST(@.Salary AS varchar(80))))
)

SET @.EmployeeID = @.EmployeeID + 1
SET @.Bonus = @.Bonus + 100
SET @.Salary = @.Salary + 500
SET @.LoopCount = @.LoopCount + 1
END

UPDATE TestData SET BonusBinary = decryptbykey(BonusEncrypt), SalaryBinary = decryptbykey(SalaryEncrypt)

close symmetric key HR01

--########################################### Verify Sums ###############################################

open symmetric key HR01 decryption by password='yes'
-- All figures should equal below (and do)
-- Bonus = 69950000
-- Salary = 309750000
SELECT
SUM(BonusActual) as 'BonusActualSum',
SUM(CAST((CAST(BonusBinary AS varchar(80)))AS decimal)) as 'BonusBinarySum',
SUM(CAST((CAST((decryptbykey(BonusEncrypt)) AS varchar(80)))AS decimal)) as 'BonusEncryptSum',
SUM(SalaryActual) as 'SalaryActualSum',
SUM(CAST((CAST(SalaryBinary AS varchar(80)))AS decimal)) as 'SalaryBinarySum',
SUM(CAST((CAST((decryptbykey(SalaryEncrypt)) AS varchar(80)))AS decimal)) as 'SalaryEncryptSum'
FROM TestData

close symmetric key HR01

--########################################### Run Tests ###############################################

open symmetric key HR01 decryption by password='yes'

DECLARE @.BonusActualSum decimal
DECLARE @.SalaryActualSum decimal
DECLARE @.BonusBinarySum decimal
DECLARE @.SalaryBinarySum decimal
DECLARE @.BonusEncryptSum decimal
DECLARE @.SalaryEncryptSum decimal
DECLARE @.ActualErrors int
DECLARE @.BinaryErrors int
DECLARE @.EncryptErrors int
DECLARE @.Success int
DECLARE @.LoopCount int
SET @.ActualErrors = 0
SET @.BinaryErrors = 0
SET @.EncryptErrors = 0
SET @.Success = 0
SET @.LoopCount = 0

WHILE (@.LoopCount < 40)
BEGIN
SELECT
@.BonusActualSum = SUM(BonusActual),
@.BonusBinarySum = SUM(CAST((CAST(BonusBinary AS varchar(80)))AS decimal)),
@.BonusEncryptSum = SUM(CAST((CAST((decryptbykey(BonusEncrypt)) AS varchar(80)))AS decimal)),
@.SalaryActualSum = SUM(SalaryActual),
@.SalaryBinarySum = SUM(CAST((CAST(SalaryBinary AS varchar(80)))AS decimal)),
@.SalaryEncryptSum = SUM(CAST((CAST((decryptbykey(SalaryEncrypt)) AS varchar(80)))AS decimal))
FROM TestData

IF(@.BonusActualSum <> 69950000 OR @.SalaryActualSum <> 309750000) SET @.ActualErrors = @.ActualErrors + 1
IF(@.BonusBinarySum <> 69950000 OR @.SalaryBinarySum <> 309750000) SET @.BinaryErrors = @.BinaryErrors + 1
IF(@.BonusEncryptSum <> 69950000 OR @.SalaryEncryptSum <> 309750000) SET @.EncryptErrors = @.EncryptErrors + 1

IF(@.BonusActualSum <> 69950000 OR @.BonusBinarySum <> 69950000 OR
@.BonusEncryptSum <> 69950000 OR @.SalaryActualSum <> 309750000 OR
@.SalaryBinarySum <> 309750000 OR @.SalaryEncryptSum <> 309750000)
BEGIN
PRINT '@.BonusActualSum ' + CAST(@.BonusActualSum AS varchar(80))
PRINT '@.BonusBinarySum ' + CAST(@.BonusBinarySum AS varchar(80))
PRINT '@.BonusEncryptSum ' + CAST(@.BonusEncryptSum AS varchar(80))
PRINT '@.SalaryActualSum ' + CAST(@.SalaryActualSum AS varchar(80))
PRINT '@.SalaryBinarySum ' + CAST(@.SalaryBinarySum AS varchar(80))
PRINT '@.SalaryEncryptSum ' + CAST(@.SalaryEncryptSum AS varchar(80))
END ELSE
SET @.Success = @.Success + 1

SET @.LoopCount = @.LoopCount + 1
END

PRINT 'Finish'
PRINT 'ActualErrors ' + CAST(@.ActualErrors AS varchar(80))
PRINT 'BinaryErrors ' + CAST(@.BinaryErrors AS varchar(80))
PRINT 'EncryptErrors ' + CAST(@.EncryptErrors AS varchar(80))
PRINT 'Success ' + CAST(@.Success AS varchar(80))

close symmetric key HR01

Results from one machine:

Finish
ActualErrors 0
BinaryErrors 0
EncryptErrors 0
Success 40

Results from two sessions of SQL Server Management Studio run simultaneously (just showing 1 of the 37):

Warning: Null value is eliminated by an aggregate or other SET operation.
@.BonusActualSum 69950000
@.BonusBinarySum 69950000
@.BonusEncryptSum 69404000
@.SalaryActualSum 309750000
@.SalaryBinarySum 309750000
@.SalaryEncryptSum 309750000

Finish
ActualErrors 0
BinaryErrors 0
EncryptErrors 37
Success 3

|||The strange thing is I cannot get this to fail on my local SQL Express. It's just failing on our development servers and production boxes. It also takes around 3 times as long to run on the high spec'd boxes. Could be an install issue....|||

Sounds unlikely it is related to the installation, but let’s not completely discard the possibility yet.I am suspecting it may be either a problem on how the key is being used or even a concurrency bug with the key ring in addition to the way the query is being cached/optimized by the server that we haven’t seen in our tests.

Let’s try to minimize the variables and see if we can get to the root problem. Can you try the following changes on your query and run them on the system that you know you can repro the problem?

--########################################### Run Tests ###############################################

open symmetric key HR01 decryption by password='yes'

-- if possible, separate the OPEN SYMMETRIC KEY from the rest of the batch

go

-- Key HR01 should be opened w/status = 1

if( (SELECT count(*) FROM sys.openkeys) = 0 )

PRINT 'Failed!!! no keys found in the key-ring'

ELSE

SELECT * FROM sys.openkeys

go

DECLARE @.BonusActualSum decimal

DECLARE @.SalaryActualSum decimal

DECLARE @.BonusBinarySum decimal

DECLARE @.SalaryBinarySum decimal

DECLARE @.BonusEncryptSum decimal

DECLARE @.SalaryEncryptSum decimal

DECLARE @.ActualErrors int

DECLARE @.BinaryErrors int

DECLARE @.EncryptErrors int

DECLARE @.Success int

DECLARE @.LoopCount int

SET @.ActualErrors = 0

SET @.BinaryErrors = 0

SET @.EncryptErrors = 0

SET @.Success = 0

SET @.LoopCount = 0

-- RG: Let's just make sure the decryptbykey values we are getting back are not null and seem like valid decimal values (hex)

SELECT decryptbykey(BonusEncrypt) as decrypted_bonus, decryptbykey(SalaryEncrypt) as decrypted_salary FROM TestData

WHILE (@.LoopCount < 40)

BEGIN

SELECT

@.BonusActualSum = SUM(BonusActual),

@.BonusBinarySum = SUM(CAST((CAST(BonusBinary AS varchar(80)))AS decimal)),

@.BonusEncryptSum = SUM(CAST((CAST((decryptbykey(BonusEncrypt)) AS varchar(80)))AS decimal)),

@.SalaryActualSum = SUM(SalaryActual),

@.SalaryBinarySum = SUM(CAST((CAST(SalaryBinary AS varchar(80)))AS decimal)),

@.SalaryEncryptSum = SUM(CAST((CAST((decryptbykey(SalaryEncrypt)) AS varchar(80)))AS decimal))

FROM TestData

-- Let's make sure no decryption call returned null

if( @.BonusEncryptSum is null OR @.SalaryEncryptSum is null )

BEGIN

PRINT 'FAILED!!! Some values are null'

SELECT @.BonusEncryptSum, @.SalaryEncryptSum, @.LoopCount

END

IF(@.BonusActualSum <> 69950000 OR @.SalaryActualSum <> 309750000) SET @.ActualErrors = @.ActualErrors + 1

IF(@.BonusBinarySum <> 69950000 OR @.SalaryBinarySum <> 309750000) SET @.BinaryErrors = @.BinaryErrors + 1

IF(@.BonusEncryptSum <> 69950000 OR @.SalaryEncryptSum <> 309750000) SET @.EncryptErrors = @.EncryptErrors + 1

IF(@.BonusActualSum <> 69950000 OR @.BonusBinarySum <> 69950000 OR

@.BonusEncryptSum <> 69950000 OR @.SalaryActualSum <> 309750000 OR

@.SalaryBinarySum <> 309750000 OR @.SalaryEncryptSum <> 309750000)

BEGIN

PRINT '@.BonusActualSum ' + CAST(@.BonusActualSum AS varchar(80))

PRINT '@.BonusBinarySum ' + CAST(@.BonusBinarySum AS varchar(80))

PRINT '@.BonusEncryptSum ' + CAST(@.BonusEncryptSum AS varchar(80))

PRINT '@.SalaryActualSum ' + CAST(@.SalaryActualSum AS varchar(80))

PRINT '@.SalaryBinarySum ' + CAST(@.SalaryBinarySum AS varchar(80))

PRINT '@.SalaryEncryptSum ' + CAST(@.SalaryEncryptSum AS varchar(80))

END ELSE

SET @.Success = @.Success + 1

SET @.LoopCount = @.LoopCount + 1

END

PRINT 'Finish'

PRINT 'ActualErrors ' + CAST(@.ActualErrors AS varchar(80))

PRINT 'BinaryErrors ' + CAST(@.BinaryErrors AS varchar(80))

PRINT 'EncryptErrors ' + CAST(@.EncryptErrors AS varchar(80))

PRINT 'Success ' + CAST(@.Success AS varchar(80))

close symmetric key HR01

Hopefully we will be able to see if the key-ring and/or decryptbykey values are returning unexpected results. For your own safety, make sure to not post any of the decrypted values from the SELECT statement, just glance through them to see if there is anything that doesn’t seem to be a valid value. You can manually verify them be casting them to a decimal in an ad-hoc query, example:

declare @.x varbinary(100)

set @.x = 0x12000001CBA70800 -- varbinary value copied from decrypted column

print cast(@.x as decimal)

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul

Thanks again for the reply. I amended my scripts (and removed excess variables) and I can still reproduce the error i.e. quite a few of the values returned from your select statement are null. I also showed the issue to the SQL Server team and they have now passed it onto Microsoft as they could not figure it out. I've included the latest script with your changes. I'm almost convinced that it's a install issue as I can run the tests without fail against my local SQL Express.

Thanks

--########################################### Description ###############################################

--The problem seems to be with running the decryptbykey function on the Company Servers
--Running the test script under the 'Run Tests' heading on a single connection will work fine
--most of the time (sometimes 1 or 2 will fail which is a concern). But running with
--the query in two windows of SQL Management Studio simultanteously will error on almost all results.
--The actual and binary values always return the correct values. But the Encrypted values will fail to sum correctly.
--This only seems to be a problem on the servers (Win2000 Server SP4 4GB RAM, 4 CPU)
--ie On local installs of SQL Express (Win XP SP2) we have increased the loop to 4000 and
--we do not have any issues and it runs 3 times as quick.

--########################################### Setup Data ###############################################

IF NOT EXISTS(SELECT * FROM sys.symmetric_keys WHERE [name] = 'TestKey')
BEGIN
CREATE SYMMETRIC KEY TestKey WITH algorithm=DES encryption BY password = 'yes'
END

open symmetric key TestKey decryption by password='yes'

DECLARE @.LoopCount int
DECLARE @.Bonus decimal
DECLARE @.EmployeeID int
CREATE TABLE TestData
(
empId int,
BonusActual decimal,
BonusBinary varbinary(64),
BonusEncrypt varbinary(64),
)

SET @.EmployeeID = 1000
SET @.Bonus = 20000
SET @.LoopCount = 0

WHILE (@.LoopCount < 1000)
BEGIN
INSERT INTO TestData (empId, BonusActual, BonusEncrypt)
VALUES (@.EmployeeID, @.Bonus, EncryptByKey(Key_GUID('TestKey'), (CAST(@.Bonus AS varchar(80)))))

SET @.EmployeeID = @.EmployeeID + 1
SET @.Bonus = @.Bonus + 100
SET @.LoopCount = @.LoopCount + 1
END

UPDATE TestData SET BonusBinary = decryptbykey(BonusEncrypt)

close symmetric key TestKey

--########################################### Verify Sums ###############################################

open symmetric key TestKey decryption by password='yes'

-- All figures should equal below
-- Bonus = 69950000
SELECT
SUM(BonusActual) as 'BonusActualSum',
SUM(CAST((CAST(BonusBinary AS varchar(80)))AS decimal)) as 'BonusBinarySum',
SUM(CAST((CAST((decryptbykey(BonusEncrypt)) AS varchar(80)))AS decimal)) as 'BonusEncryptSum'
FROM TestData

close symmetric key TestKey

--########################################### Run Tests ###############################################

open symmetric key TestKey decryption by password='yes'

go

-- Key HR01 should be opened w/status = 1

if( (SELECT count(*) FROM sys.openkeys) = 0 )
PRINT 'Failed!!! no keys found in the key-ring'
ELSE
SELECT * FROM sys.openkeys

go

DECLARE @.BonusActualSum decimal
DECLARE @.BonusBinarySum decimal
DECLARE @.BonusEncryptSum decimal
DECLARE @.ActualErrors int
DECLARE @.BinaryErrors int
DECLARE @.EncryptErrors int
DECLARE @.Success int
DECLARE @.LoopCount int

SET @.ActualErrors = 0
SET @.BinaryErrors = 0
SET @.EncryptErrors = 0
SET @.Success = 0
SET @.LoopCount = 0

-- RG: Let's just make sure the decryptbykey values we are getting
--back are not null and seem like valid decimal values (hex)

SELECT decryptbykey(BonusEncrypt) as decrypted_bonus
FROM TestData

WHILE (@.LoopCount < 40)
BEGIN
SELECT
@.BonusActualSum = SUM(BonusActual),
@.BonusBinarySum = SUM(CAST((CAST(BonusBinary AS varchar(80)))AS decimal)),
@.BonusEncryptSum = SUM(CAST((CAST((decryptbykey(BonusEncrypt)) AS varchar(80)))AS decimal))
FROM TestData

-- Let's make sure no decryption call returned null
if(@.BonusEncryptSum is null)
BEGIN
PRINT 'FAILED!!! Some values are null'
SELECT @.BonusEncryptSum, @.LoopCount
END

IF(@.BonusActualSum <> 69950000) SET @.ActualErrors = @.ActualErrors + 1
IF(@.BonusBinarySum <> 69950000) SET @.BinaryErrors = @.BinaryErrors + 1
IF(@.BonusEncryptSum <> 69950000) SET @.EncryptErrors = @.EncryptErrors + 1

IF(@.BonusActualSum <> 69950000 OR
@.BonusBinarySum <> 69950000 OR
@.BonusEncryptSum <> 69950000)
BEGIN
PRINT '@.BonusActualSum ' + CAST(@.BonusActualSum AS varchar(80))
PRINT '@.BonusBinarySum ' + CAST(@.BonusBinarySum AS varchar(80))
PRINT '@.BonusEncryptSum ' + CAST(@.BonusEncryptSum AS varchar(80))
END
ELSE
SET @.Success = @.Success + 1
SET @.LoopCount = @.LoopCount + 1
SET @.BonusEncryptSum = null
END

PRINT 'Finish'
PRINT 'ActualErrors ' + CAST(@.ActualErrors AS varchar(80))
PRINT 'BinaryErrors ' + CAST(@.BinaryErrors AS varchar(80))
PRINT 'EncryptErrors ' + CAST(@.EncryptErrors AS varchar(80))
PRINT 'Success ' + CAST(@.Success AS varchar(80))

close symmetric key TestKey

|||

This is really strange. Just to make sure that it is decryptbykey usage the one causing problems, can you try this change in the script on the real servers:

SELECT BonusEncrypt , decryptbykey(BonusEncrypt) as decrypted_bonus
FROM TestData

If it is decryptbykey is the one failing, we should see a valid encrypted value (the encrypted value should start with the same sequence, the key GUID on all rows and be of the same length) in the first column and null in the second one. If the first column shows any null then it also fails during the encryptbykey function.

After this test, can you try to change the script to add encryption by a certificate to TestKey and use decryptByKeyAutoCert? The way the decryptByKeyAutoCert handles the key ring may help on this particular case as a workaround; as I have never seen this behavior before, I am not sure if it will really work but it may be worth to give it a try:

OPEN SYMMETRIC KEY TestKey DECRYPTION BY PASSWORD = 'yes'

ALTER SYMMETRIC KEY TestKey ADD ENCRYPTION BY CERTIFICATE TestCert

CLOSE SYMMETRIC KEY TestKey

go

And on the test script, remove the OPEN SYMMETRIC KEY statement and use decryptbykeyautocert( cert_id('TestCert'), N'yes', BonusEncrypt) instead of the regular decryptbykey function.

I will also need to investigate more on this one, but any additional information I can get will help. I will also appreciate if you can test an algorithm different than DES. For testing purposes only, can you also give it a try with RC4 and RC2 algorithms?

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

I ran the select with the extra column and all of the BonusEncrypt values were returned correctly. Beside them were quite a few nulls in the decrypted_bonus column.

The interesting thing was changing the encryption method to RC4 or RC2 fixed the problem. Using the certificate didn't seem to make a difference.

Thanks
Warren

|||

Based on your observations I am suspecting the problem may be related to how Windows 2000 CAPI works with DES keys (to be more specific, the parity bits). Can you please try the following two tests?

* First, try using TRIPLE_DES instead of DES, I would expect a similar behavior.

* Save some of the rows that return null for the decrypted value, close and reopen the symmetric key and then copy the encrypted value and run the following script:

declare @.x varbinary(1000)

set @.x = -- Copy the encrypted value here

select datalength(@.x), decryptbykey( @.x )

go

Run this select statement a few times (reopen the key), it should always fail the same way (return null), the first column (datalength) is only to see if the encrypted data length seems correct (should always be the same). I would also expect that the select statement above works consistently with the rows that returned as not-null.

In the meantime I will continue investigating on my own environment. Thanks a lot.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul

I tried the TRIPLE_DES and it works fine. Just to make sure I repeated the test several times with both encryption methods and DES always failed and TRIPLE_DES never fails.

Also I couldn't get the script you sent to fail. So it only seems to be when reading from a table. Just to make sure I ran this script simultaneously in two windows without any errors:

OPEN SYMMETRIC KEY TestKey decryption by password='yes'

DECLARE @.LoopCount int
DECLARE @.x varbinary(1000)
DECLARE @.y varbinary(1000)
DECLARE @.z varchar(20)
SET @.LoopCount = 0
WHILE (@.LoopCount < 100000)
BEGIN
SET @.x = 0x0070D1427D69FA4BBC7EE4C3A031DCFE01000000C9E733BEA6BD461A28B9F8522280F0644CB68BE940D5E8AE
SET @.LoopCount = @.LoopCount + 1
SET @.y = decryptbykey( @.x )
SET @.z = CAST(@.y AS varchar(20))
IF @.y IS NULL OR @.z <> '20400' PRINT CAST(datalength(@.x) AS varchar(20)) + ' ' + @.z
SET @.y = NULL
SET @.z = NULL
END

CLOSE SYMMETRIC KEY TestKey

|||

I am working on this case with the premier support group, we will continue via premier support.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

As I mentioned on my last post, the premier support engineers are working on this case with the customer, but they already figured out that the root of the problem is a bug in Windows 2000 CryptoAPI that only happens when using DES keys.

To prevent anyone else to hit the same problem, I decided to update this thread. We strongly suggest updating to Windows 2003 server products, or if you still need to continue using Windows 2000 use a different algorithm, we strongly recommend using TRIPLE_DES keys when using Windows 2000.

Thanks a lot, and kudos to the Premier Support team for their great work and help on this case!

-Raul Garcia
SDE/T
SQL Server Engine

decrypt

Hi,

When I run the package it gives the following error warning.

Not sure how and where to fix this.

P.S. The package runs successfully and loads data but not sure why I get this error.

Thanks

Error: 2007-08-29 06:00:13.70
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error

How is your package protected (do not save sensitive data, encrypt sensitive w/ password, encrypt sensitive w/ key, encrypt all w/ password, encrypt all w/ key, server storage)? This sounds like you are not set up with the specified user key of the user / machine combination which created the package...

See:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1059406&SiteID=1

|||

Changed the protection level to DontSaveSensitive and it seems to work ok now.

Decreased memory usage when the workload is heavy?

I have a specific job that should be run with a decreased memory usage when the workload is heavy on the SQL Server.

This is a heavy job that has no specific requirement when it comes to response time.

It is important that the rest of the application shouldn't be affected with longer response time when this job is running.

How can this job bee handled from the application, without having to create a separate batch job.

You cannot limit the amount of memory to be used for a certain process. If you are worried the production would be affected during peak hours, have a reporting server that you replicate to and run your process off of it.

Decoding SQL Profiler .trc files into text?

Is there a method available to turn a Profiler .trc file into its text
equivalent? We would like to run some parsing tools against Profiler files
but they need to be in some form of delimited text. Doesn't it?
I think you would have to use fn_trace_gettable first to load it into SQL
and then export the table to a text file. In SQL2005 you can use SMO to read
a trace from code (e.g. C#,VB.NET) and do what you want with it. You can
also read running traces using both SMO and fn_trace_gettable (in SQL2000
the trace has to be stopped in order to acces it).
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:035B663B-AA0A-4CAE-A1EA-92D2114DC1FB@.microsoft.com...
> Is there a method available to turn a Profiler .trc file into its text
> equivalent? We would like to run some parsing tools against Profiler
> files
> but they need to be in some form of delimited text. Doesn't it?

DECODE?

I have to run a query to give a column a value based on a time range. Can I
use DECODE?

select decode(trans_date, trans_date>='01-Jul-2002' and
trans_date<='30-Jun-2003','Fiscal2002', ....) as fiscal,
from. . .
where. . .Sherman H. (shung@.earthlink.net) writes:
> I have to run a query to give a column a value based on a time range.
> Can I use DECODE?
> select decode(trans_date, trans_date>='01-Jul-2002' and
> trans_date<='30-Jun-2003','Fiscal2002', ....) as fiscal,
> from. . .
> where. . .

Maybe in some other DBMS, but there is no such function in SQL Server.

I don't know what decode is supposed to achieve, but it seems that
the CASE expression might to the task:

SELECT CASE WHEN transdate BETWEEN '20020701' AND '20030630'
THEN 'Fiscal2002'
...
END

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Declare Scalar Variable??

I've got a report that is pretty simple but for some reason I keep getting
the following error when I try to run it:
An error occured during local report processing.
An error has occured during report processing.
Query execution failed for data set "Dataset 1"
Must declare the scalar variable "@.TABLENAME".
This is the actual dataset I'm trying to run:
EXEC dbo.Report_TSQL_By_ID_Archive @.TABLENAME, @.SQL_ID, @.DB_ID
This is set up as COMMAND TYPE of TEXT.
Here's the actual stored procedure being called:
ALTER proc [dbo].[Report_TSQL_by_ID_Archive]
----
-- Description: Report all transactions from a given trace table by SQL_ID
-- Revision History:
----
@.TABLENAME varchar(128),
@.SQL_ID int,
@.DB_ID int,
@.Sort varchar(20) = 'CPU'
as
set nocount on
--DECLARE @.Table VARCHAR(128)
--Set @.TABLENAME = N'MTGSMNEG034_' + CONVERT(VARCHAR(24), DATEADD(day, -1,
getdate()), 110)
exec ('
select StartTime, Reads, CPU, Duration, spid, [SQL] = convert( varchar(4000), substring( TextData, 1, 4000 ) )
from [' + @.TABLENAME + '] t
join [' + @.TABLENAME + '_id] i on t.RowNumber = i.RowNumber
where i.id = ' + @.SQL_ID + '
and i.databaseID = ' + @.DB_ID)-- + '
--order by ' + @.Sort + 'desc
--')
GO
I've got another report that is literally the exact same thing, except there
is no @.TABLENAME parameter in the stored procedure and it runs just fine. I'm
also able to run the stored procedure by itself just fine.
If anyone has any idea as to what the issue may be, that would be fantastic!!
Thanks!On May 4, 2:53 pm, A. Robinson <ARobin...@.discussions.microsoft.com>
wrote:
> I've got a report that is pretty simple but for some reason I keep getting
> the following error when I try to run it:
> An error occured during local report processing.
> An error has occured during report processing.
> Query execution failed for data set "Dataset 1"
> Must declare the scalar variable "@.TABLENAME".
> This is the actual dataset I'm trying to run:
> EXEC dbo.Report_TSQL_By_ID_Archive @.TABLENAME, @.SQL_ID, @.DB_ID
> This is set up as COMMAND TYPE of TEXT.
> Here's the actual stored procedure being called:
> ALTER proc [dbo].[Report_TSQL_by_ID_Archive]
> ----
> -- Description: Report all transactions from a given trace table by SQL_ID
> -- Revision History:
> ----
> @.TABLENAME varchar(128),
> @.SQL_ID int,
> @.DB_ID int,
> @.Sort varchar(20) = 'CPU'
> as
> set nocount on
> --DECLARE @.Table VARCHAR(128)
> --Set @.TABLENAME = N'MTGSMNEG034_' + CONVERT(VARCHAR(24), DATEADD(day, -1,
> getdate()), 110)
> exec ('
> select StartTime, Reads, CPU, Duration, spid, [SQL] => convert( varchar(4000), substring( TextData, 1, 4000 ) )
> from [' + @.TABLENAME + '] t
> join [' + @.TABLENAME + '_id] i on t.RowNumber = i.RowNumber
> where i.id = ' + @.SQL_ID + '
> and i.databaseID = ' + @.DB_ID)-- + '
> --order by ' + @.Sort + 'desc
> --')
> GO
> I've got another report that is literally the exact same thing, except there
> is no @.TABLENAME parameter in the stored procedure and it runs just fine. I'm
> also able to run the stored procedure by itself just fine.
> If anyone has any idea as to what the issue may be, that would be fantastic!!
> Thanks!
I don't think you have the syntax correct on the Reporting Services
side.
This link outlines it:
http://msdn2.microsoft.com/en-us/library/aa337435.aspx
Let me know if this is what you're looking for. I have some scripts I
use to pass parameters into stored procedures at home. I can take a
look into it if the link isn't clear or if it doesn't work.|||I'm using the exact same syntax throughtout my project and all the reports
work fine. For example, this is the syntax in another report I'm using:
EXEC dbo.Report_TSQL_By_ID @.SQL_ID, @.DB_ID
This report works fine with no problems at all...
"Ayman" wrote:
> On May 4, 2:53 pm, A. Robinson <ARobin...@.discussions.microsoft.com>
> wrote:
> > I've got a report that is pretty simple but for some reason I keep getting
> > the following error when I try to run it:
> >
> > An error occured during local report processing.
> > An error has occured during report processing.
> > Query execution failed for data set "Dataset 1"
> > Must declare the scalar variable "@.TABLENAME".
> >
> > This is the actual dataset I'm trying to run:
> > EXEC dbo.Report_TSQL_By_ID_Archive @.TABLENAME, @.SQL_ID, @.DB_ID
> >
> > This is set up as COMMAND TYPE of TEXT.
> >
> > Here's the actual stored procedure being called:
> >
> > ALTER proc [dbo].[Report_TSQL_by_ID_Archive]
> > ----
> > -- Description: Report all transactions from a given trace table by SQL_ID
> > -- Revision History:
> > ----
> > @.TABLENAME varchar(128),
> > @.SQL_ID int,
> > @.DB_ID int,
> > @.Sort varchar(20) = 'CPU'
> > as
> > set nocount on
> >
> > --DECLARE @.Table VARCHAR(128)
> >
> > --Set @.TABLENAME = N'MTGSMNEG034_' + CONVERT(VARCHAR(24), DATEADD(day, -1,
> > getdate()), 110)
> >
> > exec ('
> > select StartTime, Reads, CPU, Duration, spid, [SQL] => > convert( varchar(4000), substring( TextData, 1, 4000 ) )
> > from [' + @.TABLENAME + '] t
> > join [' + @.TABLENAME + '_id] i on t.RowNumber = i.RowNumber
> > where i.id = ' + @.SQL_ID + '
> > and i.databaseID = ' + @.DB_ID)-- + '
> > --order by ' + @.Sort + 'desc
> > --')
> > GO
> >
> > I've got another report that is literally the exact same thing, except there
> > is no @.TABLENAME parameter in the stored procedure and it runs just fine. I'm
> > also able to run the stored procedure by itself just fine.
> >
> > If anyone has any idea as to what the issue may be, that would be fantastic!!
> >
> > Thanks!
> I don't think you have the syntax correct on the Reporting Services
> side.
> This link outlines it:
> http://msdn2.microsoft.com/en-us/library/aa337435.aspx
> Let me know if this is what you're looking for. I have some scripts I
> use to pass parameters into stored procedures at home. I can take a
> look into it if the link isn't clear or if it doesn't work.
>|||...and the link here is addressing the issue of binding input parameters to
user defined functions...unfirtunately that's not what I'm doing.
"Ayman" wrote:
> On May 4, 2:53 pm, A. Robinson <ARobin...@.discussions.microsoft.com>
> wrote:
> > I've got a report that is pretty simple but for some reason I keep getting
> > the following error when I try to run it:
> >
> > An error occured during local report processing.
> > An error has occured during report processing.
> > Query execution failed for data set "Dataset 1"
> > Must declare the scalar variable "@.TABLENAME".
> >
> > This is the actual dataset I'm trying to run:
> > EXEC dbo.Report_TSQL_By_ID_Archive @.TABLENAME, @.SQL_ID, @.DB_ID
> >
> > This is set up as COMMAND TYPE of TEXT.
> >
> > Here's the actual stored procedure being called:
> >
> > ALTER proc [dbo].[Report_TSQL_by_ID_Archive]
> > ----
> > -- Description: Report all transactions from a given trace table by SQL_ID
> > -- Revision History:
> > ----
> > @.TABLENAME varchar(128),
> > @.SQL_ID int,
> > @.DB_ID int,
> > @.Sort varchar(20) = 'CPU'
> > as
> > set nocount on
> >
> > --DECLARE @.Table VARCHAR(128)
> >
> > --Set @.TABLENAME = N'MTGSMNEG034_' + CONVERT(VARCHAR(24), DATEADD(day, -1,
> > getdate()), 110)
> >
> > exec ('
> > select StartTime, Reads, CPU, Duration, spid, [SQL] => > convert( varchar(4000), substring( TextData, 1, 4000 ) )
> > from [' + @.TABLENAME + '] t
> > join [' + @.TABLENAME + '_id] i on t.RowNumber = i.RowNumber
> > where i.id = ' + @.SQL_ID + '
> > and i.databaseID = ' + @.DB_ID)-- + '
> > --order by ' + @.Sort + 'desc
> > --')
> > GO
> >
> > I've got another report that is literally the exact same thing, except there
> > is no @.TABLENAME parameter in the stored procedure and it runs just fine. I'm
> > also able to run the stored procedure by itself just fine.
> >
> > If anyone has any idea as to what the issue may be, that would be fantastic!!
> >
> > Thanks!
> I don't think you have the syntax correct on the Reporting Services
> side.
> This link outlines it:
> http://msdn2.microsoft.com/en-us/library/aa337435.aspx
> Let me know if this is what you're looking for. I have some scripts I
> use to pass parameters into stored procedures at home. I can take a
> look into it if the link isn't clear or if it doesn't work.
>|||Is there a reason you are not using a command type of stored procedure? If
you do this then RS automatically determines the parameters and the
parameter data type and creates the report parameters for you. That would
solve your problem.
But, given what you have below the issue is that for whatever reason the
query parameter @.TABLENAME is not mapped to your report parameter. On the
dataset tab click on the ..., parameters tab and make sure the @.TABLENAME
parameter is mapped to the report parameter.
This error is what you get when this mapping has not occured.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:974C277C-B4F0-4940-A7E9-E8CDCD33D27F@.microsoft.com...
> I've got a report that is pretty simple but for some reason I keep getting
> the following error when I try to run it:
> An error occured during local report processing.
> An error has occured during report processing.
> Query execution failed for data set "Dataset 1"
> Must declare the scalar variable "@.TABLENAME".
> This is the actual dataset I'm trying to run:
> EXEC dbo.Report_TSQL_By_ID_Archive @.TABLENAME, @.SQL_ID, @.DB_ID
> This is set up as COMMAND TYPE of TEXT.
>
> Here's the actual stored procedure being called:
> ALTER proc [dbo].[Report_TSQL_by_ID_Archive]
> ----
> -- Description: Report all transactions from a given trace table by SQL_ID
> -- Revision History:
> ----
> @.TABLENAME varchar(128),
> @.SQL_ID int,
> @.DB_ID int,
> @.Sort varchar(20) = 'CPU'
> as
> set nocount on
> --DECLARE @.Table VARCHAR(128)
> --Set @.TABLENAME = N'MTGSMNEG034_' + CONVERT(VARCHAR(24), DATEADD(day, -1,
> getdate()), 110)
> exec ('
> select StartTime, Reads, CPU, Duration, spid, [SQL] => convert( varchar(4000), substring( TextData, 1, 4000 ) )
> from [' + @.TABLENAME + '] t
> join [' + @.TABLENAME + '_id] i on t.RowNumber = i.RowNumber
> where i.id = ' + @.SQL_ID + '
> and i.databaseID = ' + @.DB_ID)-- + '
> --order by ' + @.Sort + 'desc
> --')
> GO
> I've got another report that is literally the exact same thing, except
> there
> is no @.TABLENAME parameter in the stored procedure and it runs just fine.
> I'm
> also able to run the stored procedure by itself just fine.
> If anyone has any idea as to what the issue may be, that would be
> fantastic!!
> Thanks!
>
>
>|||Thanks!
I actually discovered the problem about five minutes after I posted my
question!
"Bruce L-C [MVP]" wrote:
> Is there a reason you are not using a command type of stored procedure? If
> you do this then RS automatically determines the parameters and the
> parameter data type and creates the report parameters for you. That would
> solve your problem.
> But, given what you have below the issue is that for whatever reason the
> query parameter @.TABLENAME is not mapped to your report parameter. On the
> dataset tab click on the ..., parameters tab and make sure the @.TABLENAME
> parameter is mapped to the report parameter.
> This error is what you get when this mapping has not occured.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
> news:974C277C-B4F0-4940-A7E9-E8CDCD33D27F@.microsoft.com...
> > I've got a report that is pretty simple but for some reason I keep getting
> > the following error when I try to run it:
> >
> > An error occured during local report processing.
> > An error has occured during report processing.
> > Query execution failed for data set "Dataset 1"
> > Must declare the scalar variable "@.TABLENAME".
> >
> > This is the actual dataset I'm trying to run:
> > EXEC dbo.Report_TSQL_By_ID_Archive @.TABLENAME, @.SQL_ID, @.DB_ID
> >
> > This is set up as COMMAND TYPE of TEXT.
> >
> >
> > Here's the actual stored procedure being called:
> >
> > ALTER proc [dbo].[Report_TSQL_by_ID_Archive]
> > ----
> > -- Description: Report all transactions from a given trace table by SQL_ID
> > -- Revision History:
> > ----
> > @.TABLENAME varchar(128),
> > @.SQL_ID int,
> > @.DB_ID int,
> > @.Sort varchar(20) = 'CPU'
> > as
> > set nocount on
> >
> > --DECLARE @.Table VARCHAR(128)
> >
> > --Set @.TABLENAME = N'MTGSMNEG034_' + CONVERT(VARCHAR(24), DATEADD(day, -1,
> > getdate()), 110)
> >
> > exec ('
> > select StartTime, Reads, CPU, Duration, spid, [SQL] => > convert( varchar(4000), substring( TextData, 1, 4000 ) )
> > from [' + @.TABLENAME + '] t
> > join [' + @.TABLENAME + '_id] i on t.RowNumber = i.RowNumber
> > where i.id = ' + @.SQL_ID + '
> > and i.databaseID = ' + @.DB_ID)-- + '
> > --order by ' + @.Sort + 'desc
> > --')
> > GO
> >
> > I've got another report that is literally the exact same thing, except
> > there
> > is no @.TABLENAME parameter in the stored procedure and it runs just fine.
> > I'm
> > also able to run the stored procedure by itself just fine.
> >
> > If anyone has any idea as to what the issue may be, that would be
> > fantastic!!
> >
> > Thanks!
> >
> >
> >
> >
> >
> >
>
>

debugging the ole db destination?

I have an OLE DB destination which should insert data into a table named in an SSIS variable. When I run the package, I don't get any errors and I have a data viewer which shows that the data is reaching the OLE DB destination. However, the data isn't being inserted into the destination table.

Can someone suggest how I should go about debugging this?

Thanks in advance.

Hi Duane,

you might try SQL Server Profiler and monitor OLEDB and T-SQL events

--
SvenC

|||

SvenC wrote:

Hi Duane,

you might try SQL Server Profiler and monitor OLEDB and T-SQL events

--
SvenC

Thanks for your reply. I figured out what I was doing incorrectly. I was using the refresh function in SQL Server Management Studio. However, it wasn't working. I'm not sure if it's a bug or a problem with my installation.

Debugging stops without messages

Have a task that has 120 tables (components) that I am running in debug mode. Just over half of the components run which takes btrieve db and converts into a sybase db. When it stops running there are a few components that are yellow, the components which completed are green and the rest are still white because they have ran yet. The problem is there is not a message to indicate as to why it stopped. I've broken up the task into two tasks and also tried making two projects. The same situation happens at the same point. Our dbas have checked the database to ensure that's fine and it is. Is there some sort of limitation in how many components can be run in debug mode?

No, but enginethreads may be limiting you here. Read this and see if it helps: http://blogs.conchango.com/jamiethomson/archive/2005/10/02/2227.aspx

-Jamie

|||Thanks for the response. I read the link and tried making a few changes to the enginethreads but no luck. In my original project I had two tasks that were linked and the first one ran without issues and the next task is where it only did the couple of tables. I created a new project and added the package to the new project then deleted the first task. Now that it's a separate project I still have the same issue. Because I copied the original package could there still be some sort of hooks that won't change because of the copy and it will still associate the number of components with the original package? The reason for this question is that I changed the enginethreads to be the max of 60 and when I ran it came back with a message that the required amount of threads in the pipeline were 121 and the max allowed was 64. I was thinking that because the package was copied could the pipeline info still show as 121 instead of the actually component count? Each task originally had about 60 components. Can I delete lines from the xml file that the package creates?|||

I found this log:

04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters: 4 supplied
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID = 3368
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags = 0x0
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr = 0x0100C5D0
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir = <NULL>
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExceptionRecordPtr = 0x00000000
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr = 0x00000000
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile = <NULL>
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName = <NULL>
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName = <NULL>
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11 not used
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7 not used
04/04/06 14:46:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump completed: C:\Program Files\Microsoft SQL Server\90\Shared\ErrorDumps\SQLDmpr0017.mdmp
04/04/06 14:46:43, ACTION, DtsDebugHost.exe, Watson Invoke: No

|||

That looks like it could be a SQL Server issue - that's where SQLDUMPER files come from unless I'm mistaken.

-Jamie

|||

Thanks again Jamie. With my post before the log info just wondered what your opinion was on that? I read the link and tried making a few changes to the enginethreads but no luck. In my original project I had two tasks that were linked and the first one ran without issues and the next task is where it only did the couple of tables. I created a new project and added the package to the new project then deleted the first task. Now that it's a separate project I still have the same issue. Because I copied the original package could there still be some sort of hooks that won't change because of the copy and it will still associate the number of components with the original package? The reason for this question is that I changed the enginethreads to be the max of 60 and when I ran it came back with a message that the required amount of threads in the pipeline were 121 and the max allowed was 64. I was thinking that because the package was copied could the pipeline info still show as 121 instead of the actually component count? Each task originally had about 60 components. Can I delete lines from the xml file that the package creates?

Once again thanks for your responses.

Debugging in VS.NET

I am attempting to debug a few stored procedures I created using the VS.NET
IDE.
After clicking "Run Stored Procedure" the IDE starts debugging, but almost
immediately throws the following error:
"Cannot debug stored procedures because the SQL Server database is not setup
correctly or user does not have permission to execute master.sp_sdidebug. Run
SQL Server setup or contact database administrator."
I found this error in conjunction with a bug in Windows XP SP2. I did have
the service pack installed at one point but uninstalled it earlier this week.
A few of the things I have tried include the following:
Debugging as SA
Setting sp_sdidebug to 'legacy_on"
Disabling my firewall
Changing DCOM settings to look like this (suggested in SQL Books)
DCOMCNFG
|
|__ Application Tab
| |
| |_____ SQLDBREG
| |
| |______ Identity Tab
| |
| |_______ The interactive user
|
|__ Default Security Tab
|
|_____ Default Access Permissions
|
|______ Edit Default Button
|
|_______ Everyone (or domain\account and System)
And, beating my head on the desk.
None of these have worked for me. If anyone has any suggestions they would
be greatly appreciated!
Thanks in advance.
Mike
Any ideas?
|||Come on! You've got to provide more information about your problem then
that.
Debugging what?
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:5635E9E3-8009-4C6D-86DF-AFF2C3067128@.microsoft.com...
> Any ideas?
|||In my post I stated that i was debugging stored procedures that I created in
Visual Studio using the server explorer. What additional information do you
need?
"Jim Young" wrote:

> Come on! You've got to provide more information about your problem then
> that.
> Debugging what?
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:5635E9E3-8009-4C6D-86DF-AFF2C3067128@.microsoft.com...
>
>
|||Sorry, I only saw the short reply you made, not the original post.
All I can say is that I'm able to debug sprocs in VS.Net by stepping into
the stored procedure from the Database menu. I can't find your original
post. What kind of problem are you having again?
Jim
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:9363461F-EE37-44D6-A346-93B2DA35894A@.microsoft.com...
> In my post I stated that i was debugging stored procedures that I created
in
> Visual Studio using the server explorer. What additional information do
you[vbcol=seagreen]
> need?
> "Jim Young" wrote:
|||After clicking "Run Stored Procedure" the IDE starts debugging, but almost
immediately throws the following error:
"Cannot debug stored procedures because the SQL Server database is not setup
correctly or user does not have permission to execute master.sp_sdidebug. Run
SQL Server setup or contact database administrator."
If anyone has any ideas I would really appreciate them. If not I'm just
going to have to get the developers edition and pray that it works.
"Jim Young" wrote:

> Sorry, I only saw the short reply you made, not the original post.
> All I can say is that I'm able to debug sprocs in VS.Net by stepping into
> the stored procedure from the Database menu. I can't find your original
> post. What kind of problem are you having again?
> Jim
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:9363461F-EE37-44D6-A346-93B2DA35894A@.microsoft.com...
> in
> you
>
>
|||By any chance did you install XP Service Pack 2 on your machine?
http://support.microsoft.com/?id=839280
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:8BBC54B3-45A7-45C7-AFC7-B051B96203ED@.microsoft.com...
> After clicking "Run Stored Procedure" the IDE starts debugging, but almost
> immediately throws the following error:
> "Cannot debug stored procedures because the SQL Server database is not
setup
> correctly or user does not have permission to execute master.sp_sdidebug.
Run[vbcol=seagreen]
> SQL Server setup or contact database administrator."
> If anyone has any ideas I would really appreciate them. If not I'm just
> going to have to get the developers edition and pray that it works.
> "Jim Young" wrote:
into[vbcol=seagreen]
created[vbcol=seagreen]
do[vbcol=seagreen]
then[vbcol=seagreen]
|||I did install SP2, but when I found that KB article you mentioned, I
uninstalled it. Is it possible that whatever it does that messes up
debugging in VS is still hanging around? I made a restore point right before
I installed the service pack, so I can restore to it if absolutely necessary.
That was almost 2 weeks ago, so I would like to avoid this solution if
possible.
Thanks for the reply.
Mike
"Jim Young" wrote:

> By any chance did you install XP Service Pack 2 on your machine?
> http://support.microsoft.com/?id=839280
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:8BBC54B3-45A7-45C7-AFC7-B051B96203ED@.microsoft.com...
> setup
> Run
> into
> created
> do
> then
>
>
|||I had the same problem debugging a SP,(not to mention that I have had not
installed WinXP SP2).
I set permissions to the extended SP on master and it did not work also, but
then I went a little bit further.
Now the message is simply:
" Security hasn't been setup correctly for SQL debugging on server xxxxx.
SQL Debugging terminated. See SQL Debugging documentation on how to set it up
correctly"
Any ideas?
LA
"Mike" wrote:
[vbcol=seagreen]
> I did install SP2, but when I found that KB article you mentioned, I
> uninstalled it. Is it possible that whatever it does that messes up
> debugging in VS is still hanging around? I made a restore point right before
> I installed the service pack, so I can restore to it if absolutely necessary.
> That was almost 2 weeks ago, so I would like to avoid this solution if
> possible.
> Thanks for the reply.
> Mike
> "Jim Young" wrote:

Debugging Exception Errors in SQL Server Profiler

I have been having weird problems from my SQL Server, and decided to run a
trace. Well, I found my problem really fast, except I really don't know
what the problem is!
First off I am developing ASP apps for working with the DB, and this is
where the problem has arisen. It used to error me on my client machine, now
it just kind of locks up and acts like it is still running.
When I run trace on a page that I am developing, it gives me the following
error,
EventClass TextData
Exception Error: 207, Severity: 16, State: 3
A few lines above this entry is one that says Attention, but there is no
TextData or anything.
How do I find out where these problems are coming from?
Thanks,
Drew
From SQL BOL:
Error 207
Severity Level 16
Message Text
Invalid column name '%.*ls'.
Explanation
This error occurs when a column referenced in a Transact-SQL statement was
not found in any table specified in the FROM clause of the query
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:OZs5UH$yEHA.4044@.TK2MSFTNGP10.phx.gbl...
> I have been having weird problems from my SQL Server, and decided to run a
> trace. Well, I found my problem really fast, except I really don't know
> what the problem is!
> First off I am developing ASP apps for working with the DB, and this is
> where the problem has arisen. It used to error me on my client machine,
now
> it just kind of locks up and acts like it is still running.
> When I run trace on a page that I am developing, it gives me the following
> error,
> EventClass TextData
> Exception Error: 207, Severity: 16, State: 3
> A few lines above this entry is one that says Attention, but there is no
> TextData or anything.
> How do I find out where these problems are coming from?
> Thanks,
> Drew
>
|||Ok, I guess the question from here is, why doesn't it error out on the page?
Or at least show a generic error page. This error doesn't seem fatal, but
it won't show me a 404 page or anything... the progress bar at the bottom of
IE just keeps going up...
Have I disabled errors or somethign?
Thanks,
Drew
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:e0irCM$yEHA.824@.TK2MSFTNGP11.phx.gbl...
> From SQL BOL:
> Error 207
> Severity Level 16
> Message Text
> Invalid column name '%.*ls'.
> Explanation
> This error occurs when a column referenced in a Transact-SQL statement was
> not found in any table specified in the FROM clause of the query
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:OZs5UH$yEHA.4044@.TK2MSFTNGP10.phx.gbl...
> now
>
|||Also, If I try to run this page, with that error and then go to the server I
get this error,
"Unable to load SQL Server OLEDB Provider resource dll. The application
cannot continue."
Any ideas?
Thanks,
Drew
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:OpJtXQ$yEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Ok, I guess the question from here is, why doesn't it error out on the
> page? Or at least show a generic error page. This error doesn't seem
> fatal, but it won't show me a 404 page or anything... the progress bar at
> the bottom of IE just keeps going up...
> Have I disabled errors or somethign?
> Thanks,
> Drew
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:e0irCM$yEHA.824@.TK2MSFTNGP11.phx.gbl...
>
|||See http://support.microsoft.com/kb/821535
From http://www.developmentnow.com/g/118_2004_11_0_0_479224/Debugging-Exception-Errors-in-SQL-Server-Profiler.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com

Debugging Exception Errors in SQL Server Profiler

I have been having weird problems from my SQL Server, and decided to run a
trace. Well, I found my problem really fast, except I really don't know
what the problem is!
First off I am developing ASP apps for working with the DB, and this is
where the problem has arisen. It used to error me on my client machine, now
it just kind of locks up and acts like it is still running.
When I run trace on a page that I am developing, it gives me the following
error,
EventClass TextData
Exception Error: 207, Severity: 16, State: 3
A few lines above this entry is one that says Attention, but there is no
TextData or anything.
How do I find out where these problems are coming from?
Thanks,
DrewFrom SQL BOL:
Error 207
Severity Level 16
Message Text
Invalid column name '%.*ls'.
Explanation
This error occurs when a column referenced in a Transact-SQL statement was
not found in any table specified in the FROM clause of the query
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:OZs5UH$yEHA.4044@.TK2MSFTNGP10.phx.gbl...
> I have been having weird problems from my SQL Server, and decided to run a
> trace. Well, I found my problem really fast, except I really don't know
> what the problem is!
> First off I am developing ASP apps for working with the DB, and this is
> where the problem has arisen. It used to error me on my client machine,
now
> it just kind of locks up and acts like it is still running.
> When I run trace on a page that I am developing, it gives me the following
> error,
> EventClass TextData
> Exception Error: 207, Severity: 16, State: 3
> A few lines above this entry is one that says Attention, but there is no
> TextData or anything.
> How do I find out where these problems are coming from?
> Thanks,
> Drew
>|||Ok, I guess the question from here is, why doesn't it error out on the page?
Or at least show a generic error page. This error doesn't seem fatal, but
it won't show me a 404 page or anything... the progress bar at the bottom of
IE just keeps going up...
Have I disabled errors or somethign?
Thanks,
Drew
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:e0irCM$yEHA.824@.TK2MSFTNGP11.phx.gbl...
> From SQL BOL:
> Error 207
> Severity Level 16
> Message Text
> Invalid column name '%.*ls'.
> Explanation
> This error occurs when a column referenced in a Transact-SQL statement was
> not found in any table specified in the FROM clause of the query
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:OZs5UH$yEHA.4044@.TK2MSFTNGP10.phx.gbl...
>> I have been having weird problems from my SQL Server, and decided to run
>> a
>> trace. Well, I found my problem really fast, except I really don't know
>> what the problem is!
>> First off I am developing ASP apps for working with the DB, and this is
>> where the problem has arisen. It used to error me on my client machine,
> now
>> it just kind of locks up and acts like it is still running.
>> When I run trace on a page that I am developing, it gives me the
>> following
>> error,
>> EventClass TextData
>> Exception Error: 207, Severity: 16, State: 3
>> A few lines above this entry is one that says Attention, but there is no
>> TextData or anything.
>> How do I find out where these problems are coming from?
>> Thanks,
>> Drew
>>
>|||Also, If I try to run this page, with that error and then go to the server I
get this error,
"Unable to load SQL Server OLEDB Provider resource dll. The application
cannot continue."
Any ideas?
Thanks,
Drew
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:OpJtXQ$yEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Ok, I guess the question from here is, why doesn't it error out on the
> page? Or at least show a generic error page. This error doesn't seem
> fatal, but it won't show me a 404 page or anything... the progress bar at
> the bottom of IE just keeps going up...
> Have I disabled errors or somethign?
> Thanks,
> Drew
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:e0irCM$yEHA.824@.TK2MSFTNGP11.phx.gbl...
>> From SQL BOL:
>> Error 207
>> Severity Level 16
>> Message Text
>> Invalid column name '%.*ls'.
>> Explanation
>> This error occurs when a column referenced in a Transact-SQL statement
>> was
>> not found in any table specified in the FROM clause of the query
>> --
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
>> news:OZs5UH$yEHA.4044@.TK2MSFTNGP10.phx.gbl...
>> I have been having weird problems from my SQL Server, and decided to run
>> a
>> trace. Well, I found my problem really fast, except I really don't know
>> what the problem is!
>> First off I am developing ASP apps for working with the DB, and this is
>> where the problem has arisen. It used to error me on my client machine,
>> now
>> it just kind of locks up and acts like it is still running.
>> When I run trace on a page that I am developing, it gives me the
>> following
>> error,
>> EventClass TextData
>> Exception Error: 207, Severity: 16, State: 3
>> A few lines above this entry is one that says Attention, but there is no
>> TextData or anything.
>> How do I find out where these problems are coming from?
>> Thanks,
>> Drew
>>
>>
>|||See http://support.microsoft.com/kb/821535
From http://www.developmentnow.com/g/118_2004_11_0_0_479224/Debugging-Exception-Errors-in-SQL-Server-Profiler.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com

Debugging Exception Errors in SQL Server Profiler

I have been having weird problems from my SQL Server, and decided to run a
trace. Well, I found my problem really fast, except I really don't know
what the problem is!
First off I am developing ASP apps for working with the DB, and this is
where the problem has arisen. It used to error me on my client machine, now
it just kind of locks up and acts like it is still running.
When I run trace on a page that I am developing, it gives me the following
error,
EventClass TextData
Exception Error: 207, Severity: 16, State: 3
A few lines above this entry is one that says Attention, but there is no
TextData or anything.
How do I find out where these problems are coming from?
Thanks,
DrewFrom SQL BOL:
Error 207
Severity Level 16
Message Text
Invalid column name '%.*ls'.
Explanation
This error occurs when a column referenced in a Transact-SQL statement was
not found in any table specified in the FROM clause of the query
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:OZs5UH$yEHA.4044@.TK2MSFTNGP10.phx.gbl...
> I have been having weird problems from my SQL Server, and decided to run a
> trace. Well, I found my problem really fast, except I really don't know
> what the problem is!
> First off I am developing ASP apps for working with the DB, and this is
> where the problem has arisen. It used to error me on my client machine,
now
> it just kind of locks up and acts like it is still running.
> When I run trace on a page that I am developing, it gives me the following
> error,
> EventClass TextData
> Exception Error: 207, Severity: 16, State: 3
> A few lines above this entry is one that says Attention, but there is no
> TextData or anything.
> How do I find out where these problems are coming from?
> Thanks,
> Drew
>|||Ok, I guess the question from here is, why doesn't it error out on the page?
Or at least show a generic error page. This error doesn't seem fatal, but
it won't show me a 404 page or anything... the progress bar at the bottom of
IE just keeps going up...
Have I disabled errors or somethign?
Thanks,
Drew
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:e0irCM$yEHA.824@.TK2MSFTNGP11.phx.gbl...
> From SQL BOL:
> Error 207
> Severity Level 16
> Message Text
> Invalid column name '%.*ls'.
> Explanation
> This error occurs when a column referenced in a Transact-SQL statement was
> not found in any table specified in the FROM clause of the query
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:OZs5UH$yEHA.4044@.TK2MSFTNGP10.phx.gbl...
> now
>|||Also, If I try to run this page, with that error and then go to the server I
get this error,
"Unable to load SQL Server OLEDB Provider resource dll. The application
cannot continue."
Any ideas?
Thanks,
Drew
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:OpJtXQ$yEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Ok, I guess the question from here is, why doesn't it error out on the
> page? Or at least show a generic error page. This error doesn't seem
> fatal, but it won't show me a 404 page or anything... the progress bar at
> the bottom of IE just keeps going up...
> Have I disabled errors or somethign?
> Thanks,
> Drew
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:e0irCM$yEHA.824@.TK2MSFTNGP11.phx.gbl...
>|||See http://support.microsoft.com/kb/821535
From http://www.developmentnow.com/g/118...er-Profiler.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com

Debugging and ActiveX Script Task in a DTS Package running on SQL Server 2005

I have a DTS package that I brought over from SQL server 2000 in to SQL Server 2005. I have installed all of the legacy components to run the DTS packages but I need to debug an ActiveX script task. In SQL Server 2000 I could turn on Just-In-Time debugging and use the stop operator (in my vbscript) to break the running script and launch the debugger.

I don't see how to do this in SQL Server 2005 Management Studio. Is it possible to debug a script object in a DTS package running in SQL Server 2005?

Jay Abbott

I have not tried, but another way to stop execution is to use a MsgBox. Whilst it is paused, you could attach the debugger to the DTS process.

I would bin the ActiveX Script, or leave it in DTS. The diagnostic information is absolutely non-existent coming out of the SSIS ActiveX Script Task, and for my money that is unacceptable in a system. Leaving it in DTS is quite easy, and perhaps call the DTS from SSIS to allow you to migrate some functions in your process. Just the fact that no error information is forthcomming in the event of a failure is enough for me to avoid using it entirely.

debug in sql server 2000

Hello guys!
I am trying to debug a sql procedure inside query analyzer but I can seem to step into each line.
When I run the procedure it doesn't stop in the breakpoint I set.

By the way I am running in a client pc..

Please help guys!

Can you post the procedure you're talking about? And which version and edition are you using?

debug in sql server 2000

Hello guys!
I am trying to debug a sql procedure inside query analyzer but I can seem to step into each line.
When I run the procedure it doesn't stop in the breakpoint I set.

By the way I am running in a client pc..

Please help guys!

Can you post the procedure you're talking about? And which version and edition are you using?

debug

Hello guys!
I am trying to debug a sql procedure inside Server Explorer but I can seem to step into each line.
When I run the procedure it doesn't stop in the breakpoint I set.

By the way I am running in a client pc..

Please help guys!

Is this a T-SQL proc, or a SQLCLR proc?

If it is a T-SQL proc, make sure you ahve opened the proc in VS before you step into it.

If it is a SQLCLR proc there are a couple of things to check:
1. Have you enabled SQLCLR debugging on the connection: Server explorer, right click the connection you use, Allow SQLCLR debugging
2. Have the debug symbols been uploaded to the database. If you use VS SQL Server Project to deploy the assemblies it happens automatically. If you deplou manually (CREATE ASSEMBLY ...) you have to do it yourself: ALTER ASSEMBLY name ADD FILE FROM path_to_pdb_file

Niels|||

Thanks for your reply nielsb!
It is a T-SQL proc. I have opened the procedure inside the VS Server explorer but still I
can't step into the proc.It ends right away.I have also tried doing it in the query analyzer
but still the same result.

Please help.

|||Hmm - ok so below is a proc script. Run this script in a database from SQL Server Management Studio

create procedure testDbg @.x int
as
declare @.y int
set @.y = 9
set @.x = @.x + @.y
select @.x

After you have run the script:
1. Open VS
2. Open Server Explorer
3. Create a connection against the database where you created the proc (if you already have a connection delete it and recreate).
4. Drill down to Stored Procedures and open the proc
5. Set a breakpoint at: set@.y = 9
6. In server explorer right click on the proc name and choose Step Into Procedure
7. You should now see a dialog, where you can set parameter values. Change the <default> to a value
8. Click OK. You should now hit the break point. If you don't check the Output Window and see if you have any error messages.

Niels|||

Hi!

First of all I don't have SQL Server Management Studio installed in my pc.

Anyway, I run the procedure in the query analyzer. And I opened VS.

I set a breakpoint and I click step into. When I entered the value and click ok nothing happens.(The breakpoint has a question mark inside it.)

There is no error inside the output window.When I point my mouse in the breakpoint I
set it just says that the breakpoint set cannot be read.And i hit F8 or F5 and still nothing happens.

I don't know why this happens..:( Please help.

|||I'm sorry, but I have no clue why it doesn't work.

What version of VS are you running, VS Express, Standard, Pro ... (I assume you are running VS 2005)?

Niels|||

Sorry to hear that...:(

I'm using VS .net 2003 Framework 1.1 though....

Could it be a permission setting or something in the server side?

|||

Are you trying to debug a stored procedure in SQL Server 2005 using VS 2003? That is not possible.

Thanks,

-Vineet.