Microsoft Visual Sutdio for Applications has lost the link to .

Hello,

I am struggling with a really ugly problem in SSIS.

I am designing SSIS package where I have a couple of Script tasks. Let's call them Script Task 1 and Script Task 2. Everything was working fine until this morning I added another Script taks (Script Task 3), which was trying to do some rudementary file manipulation. At that point I started getting this message:

"Microsoft Visual Sutdio for Applications has lost the link to .

Your work will be exported to c:\documents and settings\".

This is it! "lost a link to "dot". After this, the VS environment continues to run in debug mode, so it doesn't even crash stirctly speaking. I can stop debugging and continue developing, but nothing would run anymore and I would keep getting the same error. Moreover, after removing Script Task 3 (which seems to be the culprit), I would still keep getting the same error! Only removing all the script tasks completely and then adding them back gets the package to run again.

Any ideas?

Hi Michael,

This is the first time we are seeing this problem. The only thing that I can think that happened is that somehow those scripts got in a bad state.

One way to fix this kind of errors is to try and setting the precompiled property to false on those script tasks, remove all the breakpoints from the scripts, save the package and then reset the precompiled to the desired value and re-add the breakpoints. By doing that the precompiled version of the scripts will be refreshed and hopefully everything will be back in sync.

Also make sure you have SQL Server SP2 installed because it fixes some problems with scripting that might cause these errors.

Another problem might be if you generated script 3 using copy/paste in the package designer from one of script 1 or 2. Some problems regarding that operation were fixed in SP2.

Let me know if that helped.

Silviu Guea [MSFT] SQL Server Integration Services

Microsoft SQL-DMO [SQL-DMO]Out of memory error

Hi,
We have a stored procedure that we call very frequently
that polls servers to see if they are available. This is
used from various other stored procedures that may then
use an available SQL Server or try another one.
We have implemented the stored procedure such that it uses
the SP_OA routines to perform a SQL-DMO connect. This
works very well and allows us to catch the 'server
unavailable' error in stored procedure code and branch.
Unfortunataly, if the SQL Server that issues the connect
has not been restarted for a few says we get the following
message back from the connect method (from
sp_OAGetErrorInfo):
0x80045900 Microsoft SQL-DMO [SQL-DMO]Out of
memory.
I have included the code fragment below we are using. Has
anyone any thoughts, please, on where our memory leak is
happening. All our SQL Servers are SP2 currently.
Many thanks, Ken
---
CREATE PROCEDURE xxx @.ServerName VARCHAR(20),
@.LoginTimeout INT = 10
AS
SET NOCOUNT ON
DECLARE @.iRet int
DECLARE @.object int
DECLARE @.hr int
DECLARE @.src varchar(255), @.desc varchar(255)
-- @.iRet - return value of procedures
-- @.object - object reference
-- @.hr - handle
-- @.src - source of error message
-- @.desc - description of error message
-- Set up parameters/variables
SELECT @.iRet = 0, @.hr = 0, @.object = 0
-- create SQLDMO SQLServer object
EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT,
@.desc OUT
SELECT hr=convert(varbinary(4),@.hr),
Source=@.src, Description=@.desc
RETURN (2)
END
-- Set the properties
-- Set the LoginTimeout property.
EXEC @.hr = sp_OASetProperty @.object, 'LoginTimeout',
@.LoginTimeout
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
Description=@.desc
EXEC @.hr = sp_OADestroy @.object
RETURN (3)
END
-- Set the integrated login property.
EXEC @.hr = sp_OASetProperty @.object,'LoginSecure',1
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
Description=@.desc
EXEC @.hr = sp_OADestroy @.object
RETURN (4)
END
-- attempt to connect to server
EXEC @.hr = sp_OAMethod @.object, 'Connect', NULL,
@.ServerName
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
Description=@.desc
SELECT @.iRet = convert(varbinary(4),@.hr)
EXEC @.hr = sp_OADestroy @.object
RETURN (CASE @.iRet WHEN 0x80020009 THEN 2 ELSE 1
END) -- 2 = 'SQL Server does not exist or access denied',
1 = misc. error
END
-- Disconnect
EXEC @.hr = sp_OAMethod @.object, 'DisConnect'
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
OUT
SELECT hr=convert(varbinary(4),@.hr),
Source=@.src, Description=@.desc
EXEC @.hr = sp_OADestroy @.object
RETURN (5)
END
-- Destroy object
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
Description=@.desc
RETURN (6)
END
-- success
RETURN (0)
GOIt's difficult to trace memory leaks like this. I can't help with the DMO
specific troubleshooting, but... I would encourage to you to review the fix
list for SP3a. I remember reading about some memory leak issues, don't
remember what they were... but you don't want to waste time on a fix in the
latest SP.
Also... do you have an ability to either a) rollout SP3 (which you really
should anyway for security purposes) or b) at least roll it out in a QA
envrionment where you can test this memory leak against Sp3. That might save
you a lot of time...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ken Eng" <keneng23@.hotmail.com> wrote in message
news:0d5001c36e33$069fcfb0$a301280a@.phx.gbl...
> Hi,
> We have a stored procedure that we call very frequently
> that polls servers to see if they are available. This is
> used from various other stored procedures that may then
> use an available SQL Server or try another one.
> We have implemented the stored procedure such that it uses
> the SP_OA routines to perform a SQL-DMO connect. This
> works very well and allows us to catch the 'server
> unavailable' error in stored procedure code and branch.
> Unfortunataly, if the SQL Server that issues the connect
> has not been restarted for a few says we get the following
> message back from the connect method (from
> sp_OAGetErrorInfo):
> 0x80045900 Microsoft SQL-DMO [SQL-DMO]Out of
> memory.
> I have included the code fragment below we are using. Has
> anyone any thoughts, please, on where our memory leak is
> happening. All our SQL Servers are SP2 currently.
>
> Many thanks, Ken
> ---
> CREATE PROCEDURE xxx @.ServerName VARCHAR(20),
> @.LoginTimeout INT = 10
> AS
> SET NOCOUNT ON
>
> DECLARE @.iRet int
> DECLARE @.object int
> DECLARE @.hr int
> DECLARE @.src varchar(255), @.desc varchar(255)
> -- @.iRet - return value of procedures
> -- @.object - object reference
> -- @.hr - handle
> -- @.src - source of error message
> -- @.desc - description of error message
> -- Set up parameters/variables
> SELECT @.iRet = 0, @.hr = 0, @.object = 0
> -- create SQLDMO SQLServer object
> EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT,
> @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr),
> Source=@.src, Description=@.desc
> RETURN (2)
> END
> -- Set the properties
> -- Set the LoginTimeout property.
> EXEC @.hr = sp_OASetProperty @.object, 'LoginTimeout',
> @.LoginTimeout
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
> OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
> Description=@.desc
> EXEC @.hr = sp_OADestroy @.object
> RETURN (3)
> END
>
> -- Set the integrated login property.
> EXEC @.hr = sp_OASetProperty @.object,'LoginSecure',1
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
> OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
> Description=@.desc
> EXEC @.hr = sp_OADestroy @.object
> RETURN (4)
> END
> -- attempt to connect to server
> EXEC @.hr = sp_OAMethod @.object, 'Connect', NULL,
> @.ServerName
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
> OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
> Description=@.desc
> SELECT @.iRet = convert(varbinary(4),@.hr)
> EXEC @.hr = sp_OADestroy @.object
> RETURN (CASE @.iRet WHEN 0x80020009 THEN 2 ELSE 1
> END) -- 2 = 'SQL Server does not exist or access denied',
> 1 = misc. error
> END
> -- Disconnect
> EXEC @.hr = sp_OAMethod @.object, 'DisConnect'
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
> OUT
> SELECT hr=convert(varbinary(4),@.hr),
> Source=@.src, Description=@.desc
> EXEC @.hr = sp_OADestroy @.object
> RETURN (5)
> END
> -- Destroy object
> EXEC @.hr = sp_OADestroy @.object
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
> OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
> Description=@.desc
> RETURN (6)
> END
> -- success
> RETURN (0)
> GO
>
>|||Thanks Brian,
I will move to SP3 to take this out of the equation.
Cheers
Ken
>--Original Message--
>It's difficult to trace memory leaks like this. I can't
help with the DMO
>specific troubleshooting, but... I would encourage to you
to review the fix
>list for SP3a. I remember reading about some memory leak
issues, don't
>remember what they were... but you don't want to waste
time on a fix in the
>latest SP.
>Also... do you have an ability to either a) rollout SP3
(which you really
>should anyway for security purposes) or b) at least roll
it out in a QA
>envrionment where you can test this memory leak against
Sp3. That might save
>you a lot of time...
>--
>Brian Moran
>Principal Mentor
>Solid Quality Learning
>SQL Server MVP
>http://www.solidqualitylearning.com
>
>"Ken Eng" <keneng23@.hotmail.com> wrote in message
>news:0d5001c36e33$069fcfb0$a301280a@.phx.gbl...
>> Hi,
>> We have a stored procedure that we call very frequently
>> that polls servers to see if they are available. This is
>> used from various other stored procedures that may then
>> use an available SQL Server or try another one.
>> We have implemented the stored procedure such that it
uses
>> the SP_OA routines to perform a SQL-DMO connect. This
>> works very well and allows us to catch the 'server
>> unavailable' error in stored procedure code and branch.
>> Unfortunataly, if the SQL Server that issues the connect
>> has not been restarted for a few says we get the
following
>> message back from the connect method (from
>> sp_OAGetErrorInfo):
>> 0x80045900 Microsoft SQL-DMO [SQL-DMO]Out of
>> memory.
>> I have included the code fragment below we are using.
Has
>> anyone any thoughts, please, on where our memory leak is
>> happening. All our SQL Servers are SP2 currently.
>>
>> Many thanks, Ken
>> ---
>> CREATE PROCEDURE xxx @.ServerName VARCHAR(20),
>> @.LoginTimeout INT = 10
>> AS
>> SET NOCOUNT ON
>>
>> DECLARE @.iRet int
>> DECLARE @.object int
>> DECLARE @.hr int
>> DECLARE @.src varchar(255), @.desc varchar(255)
>> -- @.iRet - return value of procedures
>> -- @.object - object reference
>> -- @.hr - handle
>> -- @.src - source of error message
>> -- @.desc - description of error message
>> -- Set up parameters/variables
>> SELECT @.iRet = 0, @.hr = 0, @.object = 0
>> -- create SQLDMO SQLServer object
>> EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
>> IF @.hr <> 0
>> BEGIN
>> EXEC sp_OAGetErrorInfo @.object, @.src OUT,
>> @.desc OUT
>> SELECT hr=convert(varbinary(4),@.hr),
>> Source=@.src, Description=@.desc
>> RETURN (2)
>> END
>> -- Set the properties
>> -- Set the LoginTimeout property.
>> EXEC @.hr = sp_OASetProperty @.object, 'LoginTimeout',
>> @.LoginTimeout
>> IF @.hr <> 0
>> BEGIN
>> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
>> OUT
>> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
>> Description=@.desc
>> EXEC @.hr = sp_OADestroy @.object
>> RETURN (3)
>> END
>>
>> -- Set the integrated login property.
>> EXEC @.hr = sp_OASetProperty @.object,'LoginSecure',1
>> IF @.hr <> 0
>> BEGIN
>> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
>> OUT
>> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
>> Description=@.desc
>> EXEC @.hr = sp_OADestroy @.object
>> RETURN (4)
>> END
>> -- attempt to connect to server
>> EXEC @.hr = sp_OAMethod @.object, 'Connect', NULL,
>> @.ServerName
>> IF @.hr <> 0
>> BEGIN
>> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
>> OUT
>> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
>> Description=@.desc
>> SELECT @.iRet = convert(varbinary(4),@.hr)
>> EXEC @.hr = sp_OADestroy @.object
>> RETURN (CASE @.iRet WHEN 0x80020009 THEN 2 ELSE 1
>> END) -- 2 = 'SQL Server does not exist or access
denied',
>> 1 = misc. error
>> END
>> -- Disconnect
>> EXEC @.hr = sp_OAMethod @.object, 'DisConnect'
>> IF @.hr <> 0
>> BEGIN
>> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
>> OUT
>> SELECT hr=convert(varbinary(4),@.hr),
>> Source=@.src, Description=@.desc
>> EXEC @.hr = sp_OADestroy @.object
>> RETURN (5)
>> END
>> -- Destroy object
>> EXEC @.hr = sp_OADestroy @.object
>> IF @.hr <> 0
>> BEGIN
>> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
>> OUT
>> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
>> Description=@.desc
>> RETURN (6)
>> END
>> -- success
>> RETURN (0)
>> GO
>>
>
>.
>