sql permissions: script

Showing posts with label script. Show all posts

Thursday, March 29, 2012

Default name for domain

Hello, I'm creating a script that will create logins for groups of a
windows machine, and I want to know if there's a way for me not to have to
specify the machine domain name.
Something like the '.\sqlexpress' name to identify the sqlexpress instance
in running in the local machine:
CREATE LOGIN [.\O2 - Viewer] FROM WINDOWS
Does something like this exist?
Regards,
Pablo MontillaOne option is to use variables and dynamically build the
SQL statements to create the logins. You can get the server
name using
select @.@.servername
You can use that to build the login names specific for a
machine.
-Sue
On Wed, 21 Mar 2007 18:31:41 -0300, "Pablo Montilla"
<melkor@.odyssey.com.uy> wrote:

>Hello, I'm creating a script that will create logins for groups of a
>windows machine, and I want to know if there's a way for me not to have to
>specify the machine domain name.
>Something like the '.\sqlexpress' name to identify the sqlexpress instance
>in running in the local machine:
>CREATE LOGIN [.\O2 - Viewer] FROM WINDOWS
>Does something like this exist?
>Regards,
>Pablo Montilla
>|||Many thanks, I'll try that.
Pablo
On Wed, 21 Mar 2007 22:00:19 -0300, Sue Hoegemeier <Sue_H@.nomail.please>
wrote:

> One option is to use variables and dynamically build the
> SQL statements to create the logins. You can get the server
> name using
> select @.@.servername
> You can use that to build the login names specific for a
> machine.
> -Sue
> On Wed, 21 Mar 2007 18:31:41 -0300, "Pablo Montilla"
> <melkor@.odyssey.com.uy> wrote:
>
>

Sunday, March 25, 2012

default database for scripts in SSMS

When I write a script in SSMS (I'm using SQL Server 2005), generally the
database that pops up in the pulldown window on the menu, and which the
script runs against (unless/until I change the pulldown selection), is the
master database. I'm working with only one database I've created, and I
rarely run scripts against the master database, so is there an option to have
SSMS default to my database instead of the master database?
Ed
Change the user default database. Go to Security, Logins, right-clik your
login and select default database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ed White" wrote:

> When I write a script in SSMS (I'm using SQL Server 2005), generally the
> database that pops up in the pulldown window on the menu, and which the
> script runs against (unless/until I change the pulldown selection), is the
> master database. I'm working with only one database I've created, and I
> rarely run scripts against the master database, so is there an option to have
> SSMS default to my database instead of the master database?
> --
> Ed

Wednesday, March 21, 2012

Deduping DB

I have an Sql database with several tables. It has about 75,000 records in it but also has a lot of dupes. Can anyone help me with a script to isolate the most possible dupes? One of the most common things I'm seeing is mispelled names.
Thanks.Check out this post:

http://www.dbforums.com/t1001522.html|||A good starting point would be to quickly link an Access front-end to your DB and then run the Access Find Duplicates query wizard to get an idea of how it does it. Unfortunately there is absolutely no way you are ever going to remove all duplicates from a database. I have had quite a lot of experience with this - NEVER promise anyone that you can do it!

One of the problems with removing cutomer/supplier dups is that if the duplicate customer/supplier also has records in other related tables you will then want to link that data to the duplicate you are keeping if u know what I mean...

Cheers|||I've tried access and the number I am getting seems unbelieveable. Do you know of any tools that might work?|||Hi,

There is software in the marketplace that will do this sort of thing but it all depends on how conisitently your data has been entered in the first place e.g. some users might enter 'Mr Matt McDonald' into a name field whilst others might enter 'Matt McDonald' - a standard database de-duping routine obviously wouldn't pick this up.

Whilst working for a mailing house and de-duping customer data I used software developed by QAS (www.qas.com) but it is very expensive and only works well if you have consistent customer address fields as it looks for postcodes etc. - as far as I'm aware most of the de-duping software works like this.

You say that Access returns more records that you think it should - maybe you need to change the criteria to specify what really are duplicates. Have you tried double-checking some of the results to see if they really are duplicates?? Other than that the only way round this is to write your own custom procedure using a combination of code (maybe VBA in access??) and Queries using wildcard characters e.g. Like * *

I don't know if this is any help to you but de-duping is different for each organisation and for each set of data...there is no one template fits all.

Matt|||Sure...you'll get an answer very fast if you post the DDL

AND what you consider a dup to be...it's not always black and white with some people

A dup to me is everything on the row is exactly the same...

Not just the PK

If you have a PK...which you don't because then you wouldn't have a dup

Monday, March 19, 2012

decryptbykey multiple session issue

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

-- 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

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'

-- 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

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

-- 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

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

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 )

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

Sunday, March 11, 2012

declaring ntext, text and image variable

I want to declare ntext, text and image variable to be used in sql script.
How can I do that?
Rohit
Hi,
You cant declare those datatypes in a declare statement.Instead you can pass
those as a parameter of a stored procedure.
create proc test_procedure @.k image,@.k1 text
as
begin
select @.k,@.k1
end
Thanks
Hari
MCDBA
"Rohit" <rohitk@.grapecity.com> wrote in message
news:uUa8CLtGEHA.2576@.TK2MSFTNGP11.phx.gbl...
> I want to declare ntext, text and image variable to be used in sql script.
> How can I do that?
> Rohit
>
|||I am using cursor and fetching all the variables of tables in variables. One
of the variable has image datatype. If I want to fetch it into cursor, how
can I do that?
is there any way thru which we can declare variable that doesnt have local
scope?
Thanks
Rohit
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23$MT5ktGEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi,
> You cant declare those datatypes in a declare statement.Instead you can
pass
> those as a parameter of a stored procedure.
> create proc test_procedure @.k image,@.k1 text
> as
> begin
> select @.k,@.k1
> end
>
> Thanks
> Hari
> MCDBA
>
>
> "Rohit" <rohitk@.grapecity.com> wrote in message
> news:uUa8CLtGEHA.2576@.TK2MSFTNGP11.phx.gbl...
script.
>
|||FYI
Server: Msg 2739, Level 16, State 1, Line 1
The text, ntext, and image data types are invalid for local variables.
JBandi
|||Thats why I have asked the question
"Andras Jakus" <andras.jakus@.vodafone.com> wrote in message
news:1DBB3032-0B77-4486-B413-7DADFA363367@.microsoft.com...
> FYI
> Server: Msg 2739, Level 16, State 1, Line 1
> The text, ntext, and image data types are invalid for local variables.
> JBandi
|||It is by design. There is no way to declare a local variable for blob
datatype (i.e. text/ntext/image) in current version of sqlserver. You will
have to wait for the next version.
-oj
http://www.rac4sql.net
"Rohit" <rohitk@.grapecity.com> wrote in message
news:Oxz6$UvGEHA.3064@.tk2msftngp13.phx.gbl...
> Thats why I have asked the question
> "Andras Jakus" <andras.jakus@.vodafone.com> wrote in message
> news:1DBB3032-0B77-4486-B413-7DADFA363367@.microsoft.com...
>

declaring ntext, text and image variable

I want to declare ntext, text and image variable to be used in sql script.
How can I do that?
RohitHi,
You cant declare those datatypes in a declare statement.Instead you can pass
those as a parameter of a stored procedure.
create proc test_procedure @.k image,@.k1 text
as
begin
select @.k,@.k1
end
Thanks
Hari
MCDBA
"Rohit" <rohitk@.grapecity.com> wrote in message
news:uUa8CLtGEHA.2576@.TK2MSFTNGP11.phx.gbl...
> I want to declare ntext, text and image variable to be used in sql script.
> How can I do that?
> Rohit
>|||I am using cursor and fetching all the variables of tables in variables. One
of the variable has image datatype. If I want to fetch it into cursor, how
can I do that?
is there any way thru which we can declare variable that doesnt have local
scope?
Thanks
Rohit
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23$MT5ktGEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi,
> You cant declare those datatypes in a declare statement.Instead you can
pass
> those as a parameter of a stored procedure.
> create proc test_procedure @.k image,@.k1 text
> as
> begin
> select @.k,@.k1
> end
>
> Thanks
> Hari
> MCDBA
>
>
> "Rohit" <rohitk@.grapecity.com> wrote in message
> news:uUa8CLtGEHA.2576@.TK2MSFTNGP11.phx.gbl...
script.
>|||FYI
Server: Msg 2739, Level 16, State 1, Line 1
The text, ntext, and image data types are invalid for local variables.
JBandi|||Thats why I have asked the question
"Andras Jakus" <andras.jakus@.vodafone.com> wrote in message
news:1DBB3032-0B77-4486-B413-7DADFA363367@.microsoft.com...
> FYI
> Server: Msg 2739, Level 16, State 1, Line 1
> The text, ntext, and image data types are invalid for local variables.
> JBandi|||It is by design. There is no way to declare a local variable for blob
datatype (i.e. text/ntext/image) in current version of sqlserver. You will
have to wait for the next version.
-oj
http://www.rac4sql.net
"Rohit" <rohitk@.grapecity.com> wrote in message
news:Oxz6$UvGEHA.3064@.tk2msftngp13.phx.gbl...
> Thats why I have asked the question
> "Andras Jakus" <andras.jakus@.vodafone.com> wrote in message
> news:1DBB3032-0B77-4486-B413-7DADFA363367@.microsoft.com...
>

Declaring DataTable in script causes error?

As discovered when trying to create a custom transformation for this question (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=899895&SiteID=1&mode=1) I tried creating an object of type DataTable and I get this error

dim myTable as DataTable

Reference required to assembly 'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' containing the implemented interface 'System.Xml.Serialization.IXmlSerializable'. Add one to your project. dts://Scripts/ScriptComponent_3851bc3613714d2d904d79bc006234f9/ScriptMain 19 24 ScriptComponent_3851bc3613714d2d904d79bc006234f9

Even if I add "Imports System.XML" I get the same error. Isn't DataTable part of System.Data (which is imported by default in Script Components)? Everyone else get the same behavior?

Chris,

The Imports directive brings namespaces from referenced assemblies. please see the link below for more details:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vastmimports.asp

You need to add a reference to System.Xml.dll through the project pane in order to be able to use any types from System.Xml namespace.

|||Thanks that works!... so even though its listed as part of System.Data it uses something from System.Xml?

Wednesday, March 7, 2012

Decimal raised to power produces wrong answer

Does anyone know of a fix or patch for SQL 2000 for the crazy results
produced by the following script ...
- or alternately is this just a PRINT problem?
DECLARE @.decimalParameterDECIMAL(38,0)
DECLARE @.decimalPower DECIMAL
DECLARE @.decimalBase DECIMAL
SET @.decimalBase = 10
SET @.decimalPower = 1
WHILE (@.decimalPower < 38)
BEGIN
SET @.decimalParameter = POWER(@.decimalBase, @.decimalPower)
PRINT @.decimalParameter
SET @.decimalPower = @.decimalPower + 1
END
The results are (clearly a problem):
10
100
1000
10000
100000
1000000
10000000
100000000
1000000000
10000000000
100000000000
1000000000000
10000000000000
100000000000000
1000000000000000
10000000000000000
100000000000000000
1000000000000000000
10000000000000000000
100000000000000000000
1000000000000000000000
10000000000000000000000
100000000000000010000000
999999999999999980000000
10000000000000001000000000
100000000000000000000000000
1000000000000000000000000000
9999999999999999600000000000
100000000000000010000000000000
1000000000000000000000000000000
9999999999999999600000000000000
100000000000000010000000000000000
999999999999999950000000000000000
9999999999999999500000000000000000
99999999999999997000000000000000000
1000000000000000000000000000000000000
9999999999999999500000000000000000000
Wozza,
This isn't a print problem. POWER is giving wrong answers.
I reported this a couple of years ago - see this thread for some discussion:
http://groups.google.co.uk/groups?q=...923828125+kass
So while this is a known bug, unfortunately, it has not
"made the bar" for importance and it's possible it will
not be fixed.
If this bug is causing a serious impact to a production
environment for you, it would be helpful to know and
see a repro or summary of the situation. Perhaps there
is a workaround (using bigint in POWER gives exact results, for
example, up to that type's limit, and one can create slow but
accurate UDFs to do what POWER does), and if not, it
might serve to increase the perceived importance of this bug.
As far as I know, when the answers are wrong, the first 15 or 16
places of precision are always correct, since internally, it appears
that the float time is being used. Here's another example:
select power(cast(1.1 as decimal(25,18)),5), 1.1*1.1*1.1*1.1*1.1
Steve Kass
Drew University
Wozza wrote:

>Does anyone know of a fix or patch for SQL 2000 for the crazy results
>produced by the following script ...
>- or alternately is this just a PRINT problem?
>DECLARE @.decimalParameterDECIMAL(38,0)
>DECLARE @.decimalPower DECIMAL
>DECLARE @.decimalBase DECIMAL
>SET @.decimalBase = 10
>SET @.decimalPower = 1
>WHILE (@.decimalPower < 38)
>BEGIN
> SET @.decimalParameter = POWER(@.decimalBase, @.decimalPower)
> PRINT @.decimalParameter
> SET @.decimalPower = @.decimalPower + 1
>END
>The results are (clearly a problem):
>10
>100
>1000
>10000
>100000
>1000000
>10000000
>100000000
>1000000000
>10000000000
>100000000000
>1000000000000
>10000000000000
>100000000000000
>1000000000000000
>10000000000000000
>100000000000000000
>1000000000000000000
>10000000000000000000
>100000000000000000000
>1000000000000000000000
>10000000000000000000000
>100000000000000010000000
>999999999999999980000000
>10000000000000001000000000
>100000000000000000000000000
>1000000000000000000000000000
>9999999999999999600000000000
>100000000000000010000000000000
>1000000000000000000000000000000
>9999999999999999600000000000000
>100000000000000010000000000000000
>999999999999999950000000000000000
>9999999999999999500000000000000000
>99999999999999997000000000000000000
>1000000000000000000000000000000000000
>9999999999999999500000000000000000000
>
>
|||Thanks Steve,
I was basically just wanting to confirm whether there was a patch or not.
The problem occurred in a unit test that I am writing which includes all
datatypes in SQL. Luckily we are not actually using DECIMAL in production (or
dev in this case), I just included it for completeness.
Thanks anyway.
Warren
"Steve Kass" wrote:

> Wozza,
> This isn't a print problem. POWER is giving wrong answers.
> I reported this a couple of years ago - see this thread for some discussion:
> http://groups.google.co.uk/groups?q=...923828125+kass
> So while this is a known bug, unfortunately, it has not
> "made the bar" for importance and it's possible it will
> not be fixed.
> If this bug is causing a serious impact to a production
> environment for you, it would be helpful to know and
> see a repro or summary of the situation. Perhaps there
> is a workaround (using bigint in POWER gives exact results, for
> example, up to that type's limit, and one can create slow but
> accurate UDFs to do what POWER does), and if not, it
> might serve to increase the perceived importance of this bug.
> As far as I know, when the answers are wrong, the first 15 or 16
> places of precision are always correct, since internally, it appears
> that the float time is being used. Here's another example:
> select power(cast(1.1 as decimal(25,18)),5), 1.1*1.1*1.1*1.1*1.1
> Steve Kass
> Drew University
>
> Wozza wrote:
>

Decimal raised to power produces wrong answer

Does anyone know of a fix or patch for SQL 2000 for the crazy results
produced by the following script ...
- or alternately is this just a PRINT problem?
DECLARE @.decimalParameter DECIMAL(38,0)
DECLARE @.decimalPower DECIMAL
DECLARE @.decimalBase DECIMAL
SET @.decimalBase = 10
SET @.decimalPower = 1
WHILE (@.decimalPower < 38)
BEGIN
SET @.decimalParameter = POWER(@.decimalBase, @.decimalPower)
PRINT @.decimalParameter
SET @.decimalPower = @.decimalPower + 1
END
The results are (clearly a problem):
10
100
1000
10000
100000
1000000
10000000
100000000
1000000000
10000000000
100000000000
1000000000000
10000000000000
100000000000000
1000000000000000
10000000000000000
100000000000000000
1000000000000000000
10000000000000000000
100000000000000000000
1000000000000000000000
10000000000000000000000
100000000000000010000000
999999999999999980000000
10000000000000001000000000
100000000000000000000000000
1000000000000000000000000000
9999999999999999600000000000
100000000000000010000000000000
1000000000000000000000000000000
9999999999999999600000000000000
100000000000000010000000000000000
999999999999999950000000000000000
9999999999999999500000000000000000
99999999999999997000000000000000000
1000000000000000000000000000000000000
9999999999999999500000000000000000000Wozza,
This isn't a print problem. POWER is giving wrong answers.
I reported this a couple of years ago - see this thread for some discussion:
http://groups.google.co.uk/groups?q=7450580596923828125+kass
So while this is a known bug, unfortunately, it has not
"made the bar" for importance and it's possible it will
not be fixed.
If this bug is causing a serious impact to a production
environment for you, it would be helpful to know and
see a repro or summary of the situation. Perhaps there
is a workaround (using bigint in POWER gives exact results, for
example, up to that type's limit, and one can create slow but
accurate UDFs to do what POWER does), and if not, it
might serve to increase the perceived importance of this bug.
As far as I know, when the answers are wrong, the first 15 or 16
places of precision are always correct, since internally, it appears
that the float time is being used. Here's another example:
select power(cast(1.1 as decimal(25,18)),5), 1.1*1.1*1.1*1.1*1.1
Steve Kass
Drew University
Wozza wrote:
>Does anyone know of a fix or patch for SQL 2000 for the crazy results
>produced by the following script ...
>- or alternately is this just a PRINT problem?
>DECLARE @.decimalParameter DECIMAL(38,0)
>DECLARE @.decimalPower DECIMAL
>DECLARE @.decimalBase DECIMAL
>SET @.decimalBase = 10
>SET @.decimalPower = 1
>WHILE (@.decimalPower < 38)
>BEGIN
> SET @.decimalParameter = POWER(@.decimalBase, @.decimalPower)
> PRINT @.decimalParameter
> SET @.decimalPower = @.decimalPower + 1
>END
>The results are (clearly a problem):
>10
>100
>1000
>10000
>100000
>1000000
>10000000
>100000000
>1000000000
>10000000000
>100000000000
>1000000000000
>10000000000000
>100000000000000
>1000000000000000
>10000000000000000
>100000000000000000
>1000000000000000000
>10000000000000000000
>100000000000000000000
>1000000000000000000000
>10000000000000000000000
>100000000000000010000000
>999999999999999980000000
>10000000000000001000000000
>100000000000000000000000000
>1000000000000000000000000000
>9999999999999999600000000000
>100000000000000010000000000000
>1000000000000000000000000000000
>9999999999999999600000000000000
>100000000000000010000000000000000
>999999999999999950000000000000000
>9999999999999999500000000000000000
>99999999999999997000000000000000000
>1000000000000000000000000000000000000
>9999999999999999500000000000000000000
>
>|||Thanks Steve,
I was basically just wanting to confirm whether there was a patch or not.
The problem occurred in a unit test that I am writing which includes all
datatypes in SQL. Luckily we are not actually using DECIMAL in production (or
dev in this case), I just included it for completeness.
Thanks anyway.
Warren
"Steve Kass" wrote:
> Wozza,
> This isn't a print problem. POWER is giving wrong answers.
> I reported this a couple of years ago - see this thread for some discussion:
> http://groups.google.co.uk/groups?q=7450580596923828125+kass
> So while this is a known bug, unfortunately, it has not
> "made the bar" for importance and it's possible it will
> not be fixed.
> If this bug is causing a serious impact to a production
> environment for you, it would be helpful to know and
> see a repro or summary of the situation. Perhaps there
> is a workaround (using bigint in POWER gives exact results, for
> example, up to that type's limit, and one can create slow but
> accurate UDFs to do what POWER does), and if not, it
> might serve to increase the perceived importance of this bug.
> As far as I know, when the answers are wrong, the first 15 or 16
> places of precision are always correct, since internally, it appears
> that the float time is being used. Here's another example:
> select power(cast(1.1 as decimal(25,18)),5), 1.1*1.1*1.1*1.1*1.1
> Steve Kass
> Drew University
>
> Wozza wrote:
> >Does anyone know of a fix or patch for SQL 2000 for the crazy results
> >produced by the following script ...
> >- or alternately is this just a PRINT problem?
> >
> >DECLARE @.decimalParameter DECIMAL(38,0)
> >DECLARE @.decimalPower DECIMAL
> >DECLARE @.decimalBase DECIMAL
> >
> >SET @.decimalBase = 10
> >SET @.decimalPower = 1
> >
> >WHILE (@.decimalPower < 38)
> >BEGIN
> > SET @.decimalParameter = POWER(@.decimalBase, @.decimalPower)
> > PRINT @.decimalParameter
> > SET @.decimalPower = @.decimalPower + 1
> >END
> >
> >The results are (clearly a problem):
> >
> >10
> >100
> >1000
> >10000
> >100000
> >1000000
> >10000000
> >100000000
> >1000000000
> >10000000000
> >100000000000
> >1000000000000
> >10000000000000
> >100000000000000
> >1000000000000000
> >10000000000000000
> >100000000000000000
> >1000000000000000000
> >10000000000000000000
> >100000000000000000000
> >1000000000000000000000
> >10000000000000000000000
> >100000000000000010000000
> >999999999999999980000000
> >10000000000000001000000000
> >100000000000000000000000000
> >1000000000000000000000000000
> >9999999999999999600000000000
> >100000000000000010000000000000
> >1000000000000000000000000000000
> >9999999999999999600000000000000
> >100000000000000010000000000000000
> >999999999999999950000000000000000
> >9999999999999999500000000000000000
> >99999999999999997000000000000000000
> >1000000000000000000000000000000000000
> >9999999999999999500000000000000000000
> >
> >
> >
> >
>

Decimal raised to power produces wrong answer

Does anyone know of a fix or patch for SQL 2000 for the crazy results
produced by the following script ...
- or alternately is this just a PRINT problem?
DECLARE @.decimalParameter DECIMAL(38,0)
DECLARE @.decimalPower DECIMAL
DECLARE @.decimalBase DECIMAL
SET @.decimalBase = 10
SET @.decimalPower = 1
WHILE (@.decimalPower < 38)
BEGIN
SET @.decimalParameter = POWER(@.decimalBase, @.decimalPower)
PRINT @.decimalParameter
SET @.decimalPower = @.decimalPower + 1
END
The results are (clearly a problem):
10
100
1000
10000
100000
1000000
10000000
100000000
1000000000
10000000000
100000000000
1000000000000
10000000000000
100000000000000
1000000000000000
10000000000000000
100000000000000000
1000000000000000000
10000000000000000000
100000000000000000000
1000000000000000000000
10000000000000000000000
100000000000000010000000
999999999999999980000000
10000000000000001000000000
100000000000000000000000000
1000000000000000000000000000
9999999999999999600000000000
100000000000000010000000000000
1000000000000000000000000000000
9999999999999999600000000000000
100000000000000010000000000000000
999999999999999950000000000000000
9999999999999999500000000000000000
99999999999999997000000000000000000
1000000000000000000000000000000000000
9999999999999999500000000000000000000Wozza,
This isn't a print problem. POWER is giving wrong answers.
I reported this a couple of years ago - see this thread for some discussion:
http://groups.google.co.uk/groups?q...6923828125+kass
So while this is a known bug, unfortunately, it has not
"made the bar" for importance and it's possible it will
not be fixed.
If this bug is causing a serious impact to a production
environment for you, it would be helpful to know and
see a repro or summary of the situation. Perhaps there
is a workaround (using bigint in POWER gives exact results, for
example, up to that type's limit, and one can create slow but
accurate UDFs to do what POWER does), and if not, it
might serve to increase the perceived importance of this bug.
As far as I know, when the answers are wrong, the first 15 or 16
places of precision are always correct, since internally, it appears
that the float time is being used. Here's another example:
select power(cast(1.1 as decimal(25,18)),5), 1.1*1.1*1.1*1.1*1.1
Steve Kass
Drew University
Wozza wrote:

>Does anyone know of a fix or patch for SQL 2000 for the crazy results
>produced by the following script ...
>- or alternately is this just a PRINT problem?
>DECLARE @.decimalParameter DECIMAL(38,0)
>DECLARE @.decimalPower DECIMAL
>DECLARE @.decimalBase DECIMAL
>SET @.decimalBase = 10
>SET @.decimalPower = 1
>WHILE (@.decimalPower < 38)
>BEGIN
> SET @.decimalParameter = POWER(@.decimalBase, @.decimalPower)
> PRINT @.decimalParameter
> SET @.decimalPower = @.decimalPower + 1
>END
>The results are (clearly a problem):
>10
>100
>1000
>10000
>100000
>1000000
>10000000
>100000000
>1000000000
>10000000000
>100000000000
>1000000000000
>10000000000000
>100000000000000
>1000000000000000
>10000000000000000
>100000000000000000
>1000000000000000000
>10000000000000000000
>100000000000000000000
>1000000000000000000000
>10000000000000000000000
>100000000000000010000000
>999999999999999980000000
>10000000000000001000000000
>100000000000000000000000000
>1000000000000000000000000000
>9999999999999999600000000000
>100000000000000010000000000000
>1000000000000000000000000000000
>9999999999999999600000000000000
>100000000000000010000000000000000
>999999999999999950000000000000000
>9999999999999999500000000000000000
>99999999999999997000000000000000000
>1000000000000000000000000000000000000
>9999999999999999500000000000000000000
>
>|||Thanks Steve,
I was basically just wanting to confirm whether there was a patch or not.
The problem occurred in a unit test that I am writing which includes all
datatypes in SQL. Luckily we are not actually using DECIMAL in production (o
r
dev in this case), I just included it for completeness.
Thanks anyway.
Warren
"Steve Kass" wrote:

> Wozza,
> This isn't a print problem. POWER is giving wrong answers.
> I reported this a couple of years ago - see this thread for some discussio
n:
> http://groups.google.co.uk/groups?q...6923828125+kass
> So while this is a known bug, unfortunately, it has not
> "made the bar" for importance and it's possible it will
> not be fixed.
> If this bug is causing a serious impact to a production
> environment for you, it would be helpful to know and
> see a repro or summary of the situation. Perhaps there
> is a workaround (using bigint in POWER gives exact results, for
> example, up to that type's limit, and one can create slow but
> accurate UDFs to do what POWER does), and if not, it
> might serve to increase the perceived importance of this bug.
> As far as I know, when the answers are wrong, the first 15 or 16
> places of precision are always correct, since internally, it appears
> that the float time is being used. Here's another example:
> select power(cast(1.1 as decimal(25,18)),5), 1.1*1.1*1.1*1.1*1.1
> Steve Kass
> Drew University
>
> Wozza wrote:
>
>

Saturday, February 25, 2012

Debugging Woes

The following issue is happening a lot these days. For all the world it looks like a bug in BIDS:

I set a breakpoint in a Script Task (not a Script Component!) and the code dutifully stops on the breakpoint over and over again, as expected. (Please don't tell me about not being able to debug Script Components!)

At some point, when I run the package and hit the Script Task, the VSA editor opens as if it's going to take me to the breakpoint, but then it displays an error dialog box containing the following message:

Microsoft Visual Studio has lost its link to .
You work will be exported to C:\Documents and Settings\mgroh\My Documents when you quit the application

(Notice the space between "its link to" and the period in the first statement. It goes without saying that nothing is exported to My Documents.)

When I dismiss the dialog, execution resumes and the break point is ignored. In fact, breakpoints in all script tasks in the project are ignored.

Clearly, something is broken in the DTSX file, and is preventing VSA from finding the breakpoint. That's the only interpretation I can come up with for the "lost its link to" part of the message.

I have tried everything I can think of:
- Deleting all breakpoints and re-establishing the breakpoint
- Making a small change to the code to force VSA to re-evaluate the module
- Shutting down and re-opening the project
- Rebooting the computer (!!!!)

And NOTHING works. The module is just broken, and breakpoints it other Script Tasks don't work, either.

This really looks like a bug in SSIS, but I can't find anyone else who's complained of the same thing.

Any ideas? TIA!

- Mike

mike.groh wrote:

The following issue is happening a lot these days. For all the world it looks like a bug in BIDS:
I set a breakpoint in a Script Task (not a Script Component!) and the code dutifully stops on the breakpoint over and over again, as expected. (Please don't tell me about not being able to debug Script Components!)
At some point, when I run the package and hit the Script Task, the VSA editor opens as if it's going to take me to the breakpoint, but then it displays an error dialog box containing the following message:
Microsoft Visual Studio has lost its link to .
You work will be exported to C:\Documents and Settings\mgroh\My Documents when you quit the application
(Notice the space between "its link to" and the period in the first statement. It goes without saying that nothing is exported to My Documents.)
When I dismiss the dialog, execution resumes and the break point is ignored. In fact, breakpoints in all script tasks in the project are ignored.
Clearly, something is broken in the DTSX file, and is preventing VSA from finding the breakpoint. That's the only interpretation I can come up with for the "lost its link to" part of the message.
I have tried everything I can think of:
- Deleting all breakpoints and re-establishing the breakpoint
- Making a small change to the code to force VSA to re-evaluate the module
- Shutting down and re-opening the project
- Rebooting the computer (!!!!)
And NOTHING works. The module is just broken, and breakpoints it other Script Tasks don't work, either.
This really looks like a bug in SSIS, but I can't find anyone else who's complained of the same thing.
Any ideas? TIA!
- Mike

Mike,

No ideas I'm afraid no. I can only say that the use of script components/tasks can be ...errrr... flaky.

I usually try to recreate the script task and hope the problem goes away.

I know that doesn't help much but I wanted you to know that I feel your pain.

-Jamie

|||

Thanks for the confirmation that the Script Task may need re-creating. I was hoping it was something that you'd run into and had a nice little fix for. What a strange one! Flakey, to be sure!

If I detect any kind of pattern, such as actions that preceed this problem, I'll be sure to let you know. I'm thinking I'm doing something wrong surely, if this was a bug in SSIS or the VSA editor, other people would be complaining about it.

Thanks!

- Mike

|||I would sincearly appreciate it if someone with MSFT could assist with this bug. I am having this issue as well and it is very frustration to say the least. You spend a significant amount of time building a package, testing each step, then BAM! it's broken in the way described above and the only thing I can do is to start over, copy and paste? This is bad... I have to create a new project, create all new variables (making sure I don't accidentaly scope one in the wrong place) and so forth. Any more help or any ideas how to avoid this will be appreciated.|||Can you not just delete and re-create the script task, or is it at a package, or project level that you loose debug support?|||

Steven Barden wrote:
I would sincearly appreciate it if someone with MSFT could assist with this bug. I am having this issue as well and it is very frustration to say the least. You spend a significant amount of time building a package, testing each step, then BAM! it's broken in the way described above and the only thing I can do is to start over, copy and paste? This is bad... I have to create a new project, create all new variables (making sure I don't accidentaly scope one in the wrong place) and so forth. Any more help or any ideas how to avoid this will be appreciated.

Open up a Connect ticket.

https://connect.microsoft.com/SQLServer/Feedback|||

I am getting this error as well. I'm finding that I get the error message conditionally, depending on what line in my code I set the breakpoint on. If I set it on my "Try" line, then I DO NOT get the error. Once the debug window comes up, I can then set breakpoints where I really want to stop the code.

It's not ideal, but it is working for me.

If anyone has since found a resolution, I would greatly appreciate a response.

Thanks.

Mitch

|||I am having the same problem. is this still not fixed over a year later!!? Has anyone found out what the issue is. Just downloaded and installed sp1 for Team Suite as I have VS2005 Teamsuite installed, and I still can't debug my SSIS packages. If I put any break points in the Script code when I try to debug I get the message.

"SQL Server Integration Services Script Task has encountered a problem and needs to close. We are sorry for the inconvenience."

I click on close it then comes up with the "Microsoft Visual Studio has lost its link to..." error and it runs the package but doesn't stop at any break point. If I take out all break points it will run without this error. A couple of times I have managed to put in a single break point early in the package within a different script component which causes it to break... but at a completely different point within a completely different script component. It's so weird. Any help would be appreciated.

Hoots.

Debugging Woes

The following issue is happening a lot these days. For all the world it looks like a bug in BIDS:

Microsoft Visual Studio has lost its link to .
You work will be exported to C:\Documents and Settings\mgroh\My Documents when you quit the application

(Notice the space between "its link to" and the period in the first statement. It goes without saying that nothing is exported to My Documents.)

When I dismiss the dialog, execution resumes and the break point is ignored. In fact, breakpoints in all script tasks in the project are ignored.

Clearly, something is broken in the DTSX file, and is preventing VSA from finding the breakpoint. That's the only interpretation I can come up with for the "lost its link to" part of the message.

And NOTHING works. The module is just broken, and breakpoints it other Script Tasks don't work, either.

This really looks like a bug in SSIS, but I can't find anyone else who's complained of the same thing.

Any ideas? TIA!

- Mike

mike.groh wrote:

The following issue is happening a lot these days. For all the world it looks like a bug in BIDS:

I set a breakpoint in a Script Task (not a Script Component!) and the code dutifully stops on the breakpoint over and over again, as expected. (Please don't tell me about not being able to debug Script Components!)

At some point, when I run the package and hit the Script Task, the VSA editor opens as if it's going to take me to the breakpoint, but then it displays an error dialog box containing the following message:

Microsoft Visual Studio has lost its link to .
You work will be exported to C:\Documents and Settings\mgroh\My Documents when you quit the application

(Notice the space between "its link to" and the period in the first statement. It goes without saying that nothing is exported to My Documents.)

When I dismiss the dialog, execution resumes and the break point is ignored. In fact, breakpoints in all script tasks in the project are ignored.

Clearly, something is broken in the DTSX file, and is preventing VSA from finding the breakpoint. That's the only interpretation I can come up with for the "lost its link to" part of the message.

I have tried everything I can think of:
- Deleting all breakpoints and re-establishing the breakpoint
- Making a small change to the code to force VSA to re-evaluate the module
- Shutting down and re-opening the project
- Rebooting the computer (!!!!)

And NOTHING works. The module is just broken, and breakpoints it other Script Tasks don't work, either.

This really looks like a bug in SSIS, but I can't find anyone else who's complained of the same thing.

Any ideas? TIA!

- Mike

Mike,

No ideas I'm afraid no. I can only say that the use of script components/tasks can be ...errrr... flaky.

I usually try to recreate the script task and hope the problem goes away.

I know that doesn't help much but I wanted you to know that I feel your pain.

-Jamie

|||

Thanks for the confirmation that the Script Task may need re-creating. I was hoping it was something that you'd run into and had a nice little fix for. What a strange one! Flakey, to be sure!

Thanks!

- Mike

Steven Barden wrote:
I would sincearly appreciate it if someone with MSFT could assist with this bug. I am having this issue as well and it is very frustration to say the least. You spend a significant amount of time building a package, testing each step, then BAM! it's broken in the way described above and the only thing I can do is to start over, copy and paste? This is bad... I have to create a new project, create all new variables (making sure I don't accidentaly scope one in the wrong place) and so forth. Any more help or any ideas how to avoid this will be appreciated.

Open up a Connect ticket.

https://connect.microsoft.com/SQLServer/Feedback|||

It's not ideal, but it is working for me.

If anyone has since found a resolution, I would greatly appreciate a response.

Thanks.

Mitch

"SQL Server Integration Services Script Task has encountered a problem and needs to close. We are sorry for the inconvenience."

Hoots.
|||

I am running into this problem as well. Is Microsoft listening? It has been over 1 year since it was first posted.

I think this thread shows that enough people have been experiencing this issue. Isn't this time to pay attention to your paying customers?

By the way, dropping and re-creating script task doesn't help. I guess the only work around is to completely re-create the package, which is terrible and so problem-prone.

Thank you!

Debugging Woes

The following issue is happening a lot these days. For all the world it looks like a bug in BIDS:

Microsoft Visual Studio has lost its link to .
You work will be exported to C:\Documents and Settings\mgroh\My Documents when you quit the application

(Notice the space between "its link to" and the period in the first statement. It goes without saying that nothing is exported to My Documents.)

When I dismiss the dialog, execution resumes and the break point is ignored. In fact, breakpoints in all script tasks in the project are ignored.

Clearly, something is broken in the DTSX file, and is preventing VSA from finding the breakpoint. That's the only interpretation I can come up with for the "lost its link to" part of the message.

And NOTHING works. The module is just broken, and breakpoints it other Script Tasks don't work, either.

This really looks like a bug in SSIS, but I can't find anyone else who's complained of the same thing.

Any ideas? TIA!

- Mike

mike.groh wrote:

The following issue is happening a lot these days. For all the world it looks like a bug in BIDS:
I set a breakpoint in a Script Task (not a Script Component!) and the code dutifully stops on the breakpoint over and over again, as expected. (Please don't tell me about not being able to debug Script Components!)
At some point, when I run the package and hit the Script Task, the VSA editor opens as if it's going to take me to the breakpoint, but then it displays an error dialog box containing the following message:
Microsoft Visual Studio has lost its link to .
You work will be exported to C:\Documents and Settings\mgroh\My Documents when you quit the application
(Notice the space between "its link to" and the period in the first statement. It goes without saying that nothing is exported to My Documents.)
When I dismiss the dialog, execution resumes and the break point is ignored. In fact, breakpoints in all script tasks in the project are ignored.
Clearly, something is broken in the DTSX file, and is preventing VSA from finding the breakpoint. That's the only interpretation I can come up with for the "lost its link to" part of the message.
I have tried everything I can think of:
- Deleting all breakpoints and re-establishing the breakpoint
- Making a small change to the code to force VSA to re-evaluate the module
- Shutting down and re-opening the project
- Rebooting the computer (!!!!)
And NOTHING works. The module is just broken, and breakpoints it other Script Tasks don't work, either.
This really looks like a bug in SSIS, but I can't find anyone else who's complained of the same thing.
Any ideas? TIA!
- Mike

Mike,

No ideas I'm afraid no. I can only say that the use of script components/tasks can be ...errrr... flaky.

I usually try to recreate the script task and hope the problem goes away.

I know that doesn't help much but I wanted you to know that I feel your pain.

-Jamie

|||

Thanks for the confirmation that the Script Task may need re-creating. I was hoping it was something that you'd run into and had a nice little fix for. What a strange one! Flakey, to be sure!

Thanks!

- Mike

Steven Barden wrote:
I would sincearly appreciate it if someone with MSFT could assist with this bug. I am having this issue as well and it is very frustration to say the least. You spend a significant amount of time building a package, testing each step, then BAM! it's broken in the way described above and the only thing I can do is to start over, copy and paste? This is bad... I have to create a new project, create all new variables (making sure I don't accidentaly scope one in the wrong place) and so forth. Any more help or any ideas how to avoid this will be appreciated.

Open up a Connect ticket.

https://connect.microsoft.com/SQLServer/Feedback|||

It's not ideal, but it is working for me.

If anyone has since found a resolution, I would greatly appreciate a response.

Thanks.

Mitch

"SQL Server Integration Services Script Task has encountered a problem and needs to close. We are sorry for the inconvenience."

Hoots.
|||

I am running into this problem as well. Is Microsoft listening? It has been over 1 year since it was first posted.

I think this thread shows that enough people have been experiencing this issue. Isn't this time to pay attention to your paying customers?

By the way, dropping and re-creating script task doesn't help. I guess the only work around is to completely re-create the package, which is terrible and so problem-prone.

Thank you!

|||

Hi Michael,

The original issue that started this thread is a hard to reproduce problem in the VSA framework our script task and pipeline component are relying on for designing and executing scripts.

The latest issue with not hitting breakpoints is caused by a bug in our product that in some scenarios is unable to locate the original breakpoint. This only occurs when you have multiple scripts in the package. To workaround this you can try and set a breakpoint in the first script task that gets executed. When the VSA designer loads up all the script tasks in the package and hits that breakpoint you can add the other breakpoints in the other scripts. Unfortunately this is the only reliable workaround if you are unable to set breakpoints for now.

Also make sure you have SQL Server 2005 SP2 installed because there are some changes in the way scripts execute done in that service pack.

Hope this helps,

Silviu Guea [MSFT]

Debugging Woes

The following issue is happening a lot these days. For all the world it looks like a bug in BIDS:

Microsoft Visual Studio has lost its link to .
You work will be exported to C:\Documents and Settings\mgroh\My Documents when you quit the application

(Notice the space between "its link to" and the period in the first statement. It goes without saying that nothing is exported to My Documents.)

When I dismiss the dialog, execution resumes and the break point is ignored. In fact, breakpoints in all script tasks in the project are ignored.

Clearly, something is broken in the DTSX file, and is preventing VSA from finding the breakpoint. That's the only interpretation I can come up with for the "lost its link to" part of the message.

And NOTHING works. The module is just broken, and breakpoints it other Script Tasks don't work, either.

This really looks like a bug in SSIS, but I can't find anyone else who's complained of the same thing.

Any ideas? TIA!

- Mike

mike.groh wrote:

The following issue is happening a lot these days. For all the world it looks like a bug in BIDS:
I set a breakpoint in a Script Task (not a Script Component!) and the code dutifully stops on the breakpoint over and over again, as expected. (Please don't tell me about not being able to debug Script Components!)
At some point, when I run the package and hit the Script Task, the VSA editor opens as if it's going to take me to the breakpoint, but then it displays an error dialog box containing the following message:
Microsoft Visual Studio has lost its link to .
You work will be exported to C:\Documents and Settings\mgroh\My Documents when you quit the application
(Notice the space between "its link to" and the period in the first statement. It goes without saying that nothing is exported to My Documents.)
When I dismiss the dialog, execution resumes and the break point is ignored. In fact, breakpoints in all script tasks in the project are ignored.
Clearly, something is broken in the DTSX file, and is preventing VSA from finding the breakpoint. That's the only interpretation I can come up with for the "lost its link to" part of the message.
I have tried everything I can think of:
- Deleting all breakpoints and re-establishing the breakpoint
- Making a small change to the code to force VSA to re-evaluate the module
- Shutting down and re-opening the project
- Rebooting the computer (!!!!)
And NOTHING works. The module is just broken, and breakpoints it other Script Tasks don't work, either.
This really looks like a bug in SSIS, but I can't find anyone else who's complained of the same thing.
Any ideas? TIA!
- Mike

Mike,

No ideas I'm afraid no. I can only say that the use of script components/tasks can be ...errrr... flaky.

I usually try to recreate the script task and hope the problem goes away.

I know that doesn't help much but I wanted you to know that I feel your pain.

-Jamie

|||

Thanks for the confirmation that the Script Task may need re-creating. I was hoping it was something that you'd run into and had a nice little fix for. What a strange one! Flakey, to be sure!

Thanks!

- Mike

Steven Barden wrote:
I would sincearly appreciate it if someone with MSFT could assist with this bug. I am having this issue as well and it is very frustration to say the least. You spend a significant amount of time building a package, testing each step, then BAM! it's broken in the way described above and the only thing I can do is to start over, copy and paste? This is bad... I have to create a new project, create all new variables (making sure I don't accidentaly scope one in the wrong place) and so forth. Any more help or any ideas how to avoid this will be appreciated.

Open up a Connect ticket.

https://connect.microsoft.com/SQLServer/Feedback|||

It's not ideal, but it is working for me.

If anyone has since found a resolution, I would greatly appreciate a response.

Thanks.

Mitch

"SQL Server Integration Services Script Task has encountered a problem and needs to close. We are sorry for the inconvenience."

Hoots.

Debugging Woes

The following issue is happening a lot these days. For all the world it looks like a bug in BIDS:

Microsoft Visual Studio has lost its link to .
You work will be exported to C:\Documents and Settings\mgroh\My Documents when you quit the application

(Notice the space between "its link to" and the period in the first statement. It goes without saying that nothing is exported to My Documents.)

When I dismiss the dialog, execution resumes and the break point is ignored. In fact, breakpoints in all script tasks in the project are ignored.

Clearly, something is broken in the DTSX file, and is preventing VSA from finding the breakpoint. That's the only interpretation I can come up with for the "lost its link to" part of the message.

And NOTHING works. The module is just broken, and breakpoints it other Script Tasks don't work, either.

This really looks like a bug in SSIS, but I can't find anyone else who's complained of the same thing.

Any ideas? TIA!

- Mike

mike.groh wrote:

The following issue is happening a lot these days. For all the world it looks like a bug in BIDS:
I set a breakpoint in a Script Task (not a Script Component!) and the code dutifully stops on the breakpoint over and over again, as expected. (Please don't tell me about not being able to debug Script Components!)
At some point, when I run the package and hit the Script Task, the VSA editor opens as if it's going to take me to the breakpoint, but then it displays an error dialog box containing the following message:
Microsoft Visual Studio has lost its link to .
You work will be exported to C:\Documents and Settings\mgroh\My Documents when you quit the application
(Notice the space between "its link to" and the period in the first statement. It goes without saying that nothing is exported to My Documents.)
When I dismiss the dialog, execution resumes and the break point is ignored. In fact, breakpoints in all script tasks in the project are ignored.
Clearly, something is broken in the DTSX file, and is preventing VSA from finding the breakpoint. That's the only interpretation I can come up with for the "lost its link to" part of the message.
I have tried everything I can think of:
- Deleting all breakpoints and re-establishing the breakpoint
- Making a small change to the code to force VSA to re-evaluate the module
- Shutting down and re-opening the project
- Rebooting the computer (!!!!)
And NOTHING works. The module is just broken, and breakpoints it other Script Tasks don't work, either.
This really looks like a bug in SSIS, but I can't find anyone else who's complained of the same thing.
Any ideas? TIA!
- Mike

Mike,

No ideas I'm afraid no. I can only say that the use of script components/tasks can be ...errrr... flaky.

I usually try to recreate the script task and hope the problem goes away.

I know that doesn't help much but I wanted you to know that I feel your pain.

-Jamie

|||

Thanks for the confirmation that the Script Task may need re-creating. I was hoping it was something that you'd run into and had a nice little fix for. What a strange one! Flakey, to be sure!

Thanks!

- Mike

Steven Barden wrote:
I would sincearly appreciate it if someone with MSFT could assist with this bug. I am having this issue as well and it is very frustration to say the least. You spend a significant amount of time building a package, testing each step, then BAM! it's broken in the way described above and the only thing I can do is to start over, copy and paste? This is bad... I have to create a new project, create all new variables (making sure I don't accidentaly scope one in the wrong place) and so forth. Any more help or any ideas how to avoid this will be appreciated.

Open up a Connect ticket.

https://connect.microsoft.com/SQLServer/Feedback|||

It's not ideal, but it is working for me.

If anyone has since found a resolution, I would greatly appreciate a response.

Thanks.

Mitch

"SQL Server Integration Services Script Task has encountered a problem and needs to close. We are sorry for the inconvenience."

Hoots.

Thursday, March 29, 2012

Default name for domain

Sunday, March 25, 2012

default database for scripts in SSMS

Wednesday, March 21, 2012

Deduping DB

Monday, March 19, 2012

decryptbykey multiple session issue

Sunday, March 11, 2012

declaring ntext, text and image variable

declaring ntext, text and image variable

Declaring DataTable in script causes error?

Wednesday, March 7, 2012

Decimal raised to power produces wrong answer

Decimal raised to power produces wrong answer

Decimal raised to power produces wrong answer

Saturday, February 25, 2012

Debugging Woes

Debugging Woes

Debugging Woes

Debugging Woes

Debugging Woes

sql permissions

Blog Archive

About Me