Error: Failed to load script task

I have an email script that keeps failing to load when I execute my script task. I have the precompileintobinarycode set to true. Anyone know why it won't load the script? I would use the Send Mail task but it can't find my smtp connection or something.

Thanks,

Mike

This error can spontaneously appear for no apparent reason. It seems that if you have the PreCompile property set to True, but you have precompiled script in the package, which is what spontaneously disappears, this error will be raised. To resolve this issue:

a. Open the package

b. Open the Script Task editor

c. Click Design Script

d. Close the VSA Editor

e. Close the Script Task Editor

f. Save and close the package.

|||

didn't work I still get the same error.

sql

Error: Failed to load script task

I have an email script that keeps failing to load when I execute my script task. I have the precompileintobinarycode set to true. Anyone know why it won't load the script? I would use the Send Mail task but it can't find my smtp connection or something.

Thanks,

Mike

This error can spontaneously appear for no apparent reason. It seems that if you have the PreCompile property set to True, but you have precompiled script in the package, which is what spontaneously disappears, this error will be raised. To resolve this issue:

a. Open the package

b. Open the Script Task editor

c. Click Design Script

d. Close the VSA Editor

e. Close the Script Task Editor

f. Save and close the package.

|||

didn't work I still get the same error.

ERROR: A variable may only be added once to either the read lock list or the write lock list.

Hi,
I have set of 2 DTS packages, one of which calls the other by forming a command-line (dtexec) using a Execute Process task.

From the parent package-> Execute Process Task->
dtsexec /F etc... /<pkg variable> = "servername"

Each of the parent and the called package have a variable: "User::DWServerSQLInstance" which is mapped to the SQL server connection manager server name property using an expression. The outer package has the above variable and so does the inner called package (which gets assigned through the command line from the outerpackage call to inner)

I "sometimes" get the following error:

OnError,I4,TESTDOM\Administrator,ACDWAggregation,{A1F8E43F-15F1-4685-8C18-6866AB31E62B},{77B2F3C7-6756-46EB-8C01-D880598FB4B3},5/22/2006 5:10:28 PM,5/22/2006 5:10:28 PM,-1073659822,0x,The variable "User::DWServerSQLInstance" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

Help would be appreciated!

I have seen other posts on this but, not able to relate the solution to my scenario.

Do you have multiple tasks (in the same package) executing in parallel, and using same variable, e.g. in property mappings?

If yes, I think this problem is fixed by this post-SP1 fixes collection:
http://support.microsoft.com/Default.aspx?id=918222

Alternatively, you may change the package so the tasks are executed sequentially by creating precedence constraints.|||

Hi Brian,
The child Package uses the User Variable: DWSQLInstanceName mentioned above to initialize 4 SQL Server connections (2 OLEDB & 2 ADO .NET) -- Only <<Server Name>> property using expressions.

I also checked that this error seems to be only on Multi-proc machine. Don't get the same on my laptop.

I cannot seralize the tasks since, the error is coming @. package level while evaluating the package expressions (i.e. for the connections) when the package starts.

What is the best way to still use expressions for connection server names but, get around this error?
Is this what is fixed in SP1?

Also, it seems that the packages does not fail when it encounters the error which I am getting. I have MaxErrorCount = 1 for Package. The package goes ahead.

|||I believe the issue with connections sharing the variable is fixed in the commulative hotfix package I've mentioned above. It should be installed on top of SP1: first, install SP1 then install all the component packages from the hotfix package in the order they are listed in the article.

http://www.microsoft.com/downloads/details.aspx?FamilyId=CB6C71EA-D649-47FF-9176-E7CAC58FD4BC
and then
http://support.microsoft.com/Default.aspx?id=918222|||This was indeed a bug and the post SP1 hotfix "fixes" it.

However, you can still create the bug. It's harder but stil there...|||

Thanks Michael and Crispin for the help on this!

I think we will need to upgrade to SP1 and install the Hotfix bundle for SP1. I think this should fix the issue which I am facing i.e. use of same variable for setting connection "Server Name" - Property in the expressions.

Thanks,
Gaurav

Error: -2147024770 when i scheduled a package containing a custom task

I create a VB ActiveX DLL custom task, when i execute the package containing the DLL custom task, all is ok
But when i scheduled the package i receive the message "Error: -2147024770 The specified module could not be found"
In the code of the VB custom task i use the sysdtssteplog table from MSDB and it doesn't log anything
What's the problem
Thanks!Whenever I seen that error from VB - SQL it has always
been in the context that the SQL command run from VB has
not worked.
Try a trace to see exactly what SQL was processed.
J
>--Original Message--
>I create a VB ActiveX DLL custom task, when i execute the
package containing the DLL custom task, all is ok.
>But when i scheduled the package i receive the
message "Error: -2147024770 The specified module could
not be found".
>In the code of the VB custom task i use the sysdtssteplog
table from MSDB and it doesn't log anything.
>What's the problem?
>Thanks!
>.
>|||Jay,
When you run it, what context (login, machine, etc) are you running under?
When you schedule it (I assume through SQL Agent), what context (login,
machine, etc) are you running under?
I suspect that the SQL Server account (either the account running the SQL
Server service or the SQL Agent proxy account) does not have rights to the
location where the DLL is stored. Make sure that the DLL can be reached by
the SQL Server.
Russell Fields
"JayF" <anonymous@.discussions.microsoft.com> wrote in message
news:025B1AE0-EDC9-482C-B93E-788775D19D24@.microsoft.com...
> I create a VB ActiveX DLL custom task, when i execute the package
containing the DLL custom task, all is ok.
> But when i scheduled the package i receive the message
Error: -2147024770 The specified module could not be found".
> In the code of the VB custom task i use the sysdtssteplog table from MSDB
and it doesn't log anything.
> What's the problem?
> Thanks!

Error: -2147024770 when i scheduled a package containing a custom task

I create a VB ActiveX DLL custom task, when i execute the package containing
the DLL custom task, all is ok.
But when i scheduled the package i receive the message "Error: -2147024770
The specified module could not be found".
In the code of the VB custom task i use the sysdtssteplog table from MSDB an
d it doesn't log anything.
What's the problem?
Thanks!Jay,
When you run it, what context (login, machine, etc) are you running under?
When you schedule it (I assume through SQL Agent), what context (login,
machine, etc) are you running under?
I suspect that the SQL Server account (either the account running the SQL
Server service or the SQL Agent proxy account) does not have rights to the
location where the DLL is stored. Make sure that the DLL can be reached by
the SQL Server.
Russell Fields
"JayF" <anonymous@.discussions.microsoft.com> wrote in message
news:025B1AE0-EDC9-482C-B93E-788775D19D24@.microsoft.com...

quote:

---

> I create a VB ActiveX DLL custom task, when i execute the package

---

containing the DLL custom task, all is ok.

quote:

---

> But when i scheduled the package i receive the message

---

Error: -2147024770 The specified module could not be found".

quote:

---

> In the code of the VB custom task i use the sysdtssteplog table from MSDB

---

and it doesn't log anything.

quote:

---

> What's the problem?
> Thanks!

---

sql

Error! Must declare the varaible

Hi
Can someone help me with the following problem? I have a report that needs
two parameters at runtime. If I execute this report on the same server as RS
with a data source that is connected to a database on the same server as the
RS server. Then the report execute without any problems.
The setup is as follow:
SQL 2000 running on Windows 2003 Server, with XP Professional
workstations connecting to the RS.There is also three other SQL 2000 servers
running on Windows 2003 Server.
The problem occurs when the Data Source points to a database on a separate
server than the RS server. In this scenario the report executes with an
Error.
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Query execution failed for data set 'SRCC'. (rsErrorExecutingCommand) Get
Online Help
Must declare the variable '@.PCode'.
If the report contains no parameters, it executes without any error. Only if
it contains parameters does it execute with an error. If someone can tell me
why this is happening I will really appreciate it.
Thanx
MVAHi,
Have you done anything like this in thr Data tab.
set @.ecode = 1002
select * from employee where empno = @.ecode
Then you will get the error. If you can share the full query with dummy
values that's well and good. If you get "must declare @.pcode" then it must be
something to do with the query and I hope you didnt give anywhere in the
report "@.pcode". In that case you may get this error.
Amarnath.
"MVA" wrote:
> Hi
> Can someone help me with the following problem? I have a report that needs
> two parameters at runtime. If I execute this report on the same server as RS
> with a data source that is connected to a database on the same server as the
> RS server. Then the report execute without any problems.
> The setup is as follow:
> SQL 2000 running on Windows 2003 Server, with XP Professional
> workstations connecting to the RS.There is also three other SQL 2000 servers
> running on Windows 2003 Server.
> The problem occurs when the Data Source points to a database on a separate
> server than the RS server. In this scenario the report executes with an
> Error.
>
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Query execution failed for data set 'SRCC'. (rsErrorExecutingCommand) Get
> Online Help
> Must declare the variable '@.PCode'.
>
> If the report contains no parameters, it executes without any error. Only if
> it contains parameters does it execute with an error. If someone can tell me
> why this is happening I will really appreciate it.
>
> Thanx
> MVA
>
>

error with SQL statement

hi, i try the below sql code, however when i try to execute the command, it always give me the error 'Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.'

If i remove the "(SELECT [description] = CASE WHEN ([description]) <> '' THEN [description] ELSE [tbl_batch_completed].[status] END)" under the group by clause, it will work

do I need to change something in the sql statement?

Thanks in advance

SELECT TAG_FACE_CON.REQUEST_ID,
(SELECT [description] = CASE WHEN ([description]) <> '' THEN [description] ELSE [tbl_batch_completed].[status] END)AS status
FROM (TAG_FACE_CON RIGHT JOIN tbl_batch_completed ON TAG_FACE_CON.GROUP_ID = tbl_batch_completed.ID)
GROUP BY TAG_FACE_CON.REQUEST_ID,(SELECT [description] = CASE WHEN ([description]) <> '' THEN [description] ELSE [tbl_batch_completed].[status] END)

You are going to have to remove the select staement from the group by. Also in that SQl Staement there is no from clause... It does not know where to get the batch completed information.

I would look into create a temp table to create the first section as raw data, then when you need to do the group by run it as a query from the temp table.|||I think restructuring your query like this will solve your problem:

SELECT
 TAG_FACE_CON.REQUEST_ID,
 CASE
 WHEN [description] <> '' THEN [description]
 ELSE [tbl_batch_completed].[status]
 END AS status
FROM
 TAG_FACE_CON
RIGHT JOIN
 tbl_batch_completed ON TAG_FACE_CON.GROUP_ID = tbl_batch_completed.ID
GROUP BY
 TAG_FACE_CON.REQUEST_ID,
 CASE
 WHEN [description] <> '' THEN [description]
 ELSE [tbl_batch_completed].[status]
 END

Terri

Error with NULL using EXECUTE SCALAR

I keep getting an error message when I try to place the result of an execute scalar command into a text box. The msg is:"conversion from 'DBNull' to type 'String' is not valid"

The code I am using is:

Dim con as OLEDBConnection

con =NewOleDB connection("Provider = MIcrosoft.JetOLEDB.4.0, Data Source = "c:\caps.mdb")

Dim cmd As OLEDBCommand

cmd= NewOLEDBCommand("Select Product from [Inventory Table] Where [Customer ID] = " & grid View1.SelectdValue

texBox1.Text = cmd.ExecuteScalar

The code works fine as long as there is a value for the Product. However if the value in the database is NULL I get an error message: :"conversion from 'DBNull' to type 'String' is not valid". How do I wok around this?

Chas28

You need to first check the returning value of the query that if it is null then you don't need to set the text box text because its text property require an empty or non empty string not the Null value.

Try this:

if not cmd.ExecuteScalar =DBNull.Value then

texBox1.Text = cmd.ExecuteScalar

End If

Don't forget to click "Mark as Answer" on the post that helped you.
This earns you a point.

Zeeshan Malik
http://zeemalik.wordpress.com

|||

Sorry, but "if not cmd.ExecuteScalar =DBNull.Value" does not work. I get the error message :

'= is not defined for system.data.OLEDbCommand' and 'system.DBNull''

but thanks for trying.

Chas28

|||

ok try this:

Dim returnValue as String

returnValue = cmd.ExecuteScalar

if not ( returnValue =DBNull.Value OR returnValue =Nothing)then

texBox1.Text = returnValue

End If

Don't forget to click "Mark as Answer" on the post that helped you.
This earns you a point.

Zeeshan Malik
http://zeemalik.wordpress.com

Error with membersip and roles Cannot resolve the collation conflict between

I'm getting the following errors when trying to execute the following script on the server, its part of the standard asp.net membership and roles, anybody have any ideas how I get get round this?

Msg 468,Level 16, State 9,Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 50Cannot resolve the collation conflictbetween "SQL_Latin1_General_CP1_CI_AS"and "Latin1_General_CI_AS"in the equalto operation.Msg 468,Level 16, State 9,Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 58Cannot resolve the collation conflictbetween "SQL_Latin1_General_CP1_CI_AS"and "Latin1_General_CI_AS"in the equalto operation.Msg 468,Level 16, State 9,Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 84Cannot resolve the collation conflictbetween "SQL_Latin1_General_CP1_CI_AS"and "Latin1_General_CI_AS"in the equalto operation.Msg 468,Level 16, State 9,Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 92Cannot resolve the collation conflictbetween "SQL_Latin1_General_CP1_CI_AS"and "Latin1_General_CI_AS"in the equalto operation.

/****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles] Script Date: 05/20/2007 11:23:33 ******/SET ANSI_NULLSONGOSET QUOTED_IDENTIFIEROFFGOIFNOT EXISTS (SELECT *FROM sys.objectsWHEREobject_id =OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]')AND typein (N'P', N'PC'))BEGINEXEC dbo.sp_executesql @.statement = N'CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]@.ApplicationName nvarchar(256),@.UserNames nvarchar(4000),@.RoleNames nvarchar(4000)ASBEGINDECLARE @.AppId uniqueidentifierSELECT @.AppId = NULLSELECT @.AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@.ApplicationName) = LoweredApplicationNameIF (@.AppId IS NULL)RETURN(2)DECLARE @.TranStarted bitSET @.TranStarted = 0IF( @.@.TRANCOUNT = 0 )BEGINBEGIN TRANSACTIONSET @.TranStarted = 1ENDDECLARE @.tbNames table(Name nvarchar(256) NOT NULL PRIMARY KEY)DECLARE @.tbRoles table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)DECLARE @.tbUsers table(UserId uniqueidentifier NOT NULL PRIMARY KEY)DECLARE @.Num intDECLARE @.Pos intDECLARE @.NextPos intDECLARE @.Name nvarchar(256)DECLARE @.CountAll intDECLARE @.CountU intDECLARE @.CountR intSET @.Num = 0SET @.Pos = 1WHILE(@.Pos <= LEN(@.RoleNames))BEGINSELECT @.NextPos = CHARINDEX(N'','', @.RoleNames, @.Pos)IF (@.NextPos = 0 OR @.NextPos IS NULL)SELECT @.NextPos = LEN(@.RoleNames) + 1SELECT @.Name = RTRIM(LTRIM(SUBSTRING(@.RoleNames, @.Pos, @.NextPos - @.Pos)))SELECT @.Pos = @.NextPos+1INSERT INTO @.tbNames VALUES (@.Name)SET @.Num = @.Num + 1ENDINSERT INTO @.tbRoles SELECT RoleId FROM dbo.aspnet_Roles ar, @.tbNames t WHERE LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @.AppIdSELECT @.CountR = @.@.ROWCOUNTIF (@.CountR <> @.Num)BEGINSELECT TOP 1 N'''', NameFROM @.tbNamesWHERE LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar, @.tbRoles r WHERE r.RoleId = ar.RoleId)IF( @.TranStarted = 1 )ROLLBACK TRANSACTIONRETURN(2)ENDDELETE FROM @.tbNames WHERE 1=1SET @.Num = 0SET @.Pos = 1WHILE(@.Pos <= LEN(@.UserNames))BEGINSELECT @.NextPos = CHARINDEX(N'','', @.UserNames, @.Pos)IF (@.NextPos = 0 OR @.NextPos IS NULL)SELECT @.NextPos = LEN(@.UserNames) + 1SELECT @.Name = RTRIM(LTRIM(SUBSTRING(@.UserNames, @.Pos, @.NextPos - @.Pos)))SELECT @.Pos = @.NextPos+1INSERT INTO @.tbNames VALUES (@.Name)SET @.Num = @.Num + 1ENDINSERT INTO @.tbUsers SELECT UserId FROM dbo.aspnet_Users ar, @.tbNames t WHERE LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @.AppIdSELECT @.CountU = @.@.ROWCOUNTIF (@.CountU <> @.Num)BEGINSELECT TOP 1 Name, N''''FROM @.tbNamesWHERE LOWER(Name) NOT IN (SELECT au.LoweredUserName FROM dbo.aspnet_Users au, @.tbUsers u WHERE u.UserId = au.UserId)IF( @.TranStarted = 1 )ROLLBACK TRANSACTIONRETURN(1)ENDSELECT @.CountAll = COUNT(*)FROMdbo.aspnet_UsersInRoles ur, @.tbUsers u, @.tbRoles rWHERE ur.UserId = u.UserId AND ur.RoleId = r.RoleIdIF (@.CountAll <> @.CountU * @.CountR)BEGINSELECT TOP 1 UserName, RoleNameFROM @.tbUsers tu, @.tbRoles tr, dbo.aspnet_Users u, dbo.aspnet_Roles rWHERE u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND tu.UserId NOT IN (SELECT ur.UserId FROM dbo.aspnet_UsersInRoles ur WHERE ur.RoleId = tr.RoleId) AND tr.RoleId NOT IN (SELECT ur.RoleId FROM dbo.aspnet_UsersInRoles ur WHERE ur.UserId = tu.UserId)IF( @.TranStarted = 1 )ROLLBACK TRANSACTIONRETURN(3)ENDDELETE FROM dbo.aspnet_UsersInRolesWHERE UserId IN (SELECT UserId FROM @.tbUsers) AND RoleId IN (SELECT RoleId FROM @.tbRoles)IF( @.TranStarted = 1 )COMMIT TRANSACTIONRETURN(0)END 'ENDGO

Any help appreciated thanks, 

What is your database collation set to?

You can check this in MicrosoftSQL Server Management by right clicking the database and selecting "properties".

Now check what collation you have on your aspnet tables. Right click on aspnet_Roles, and select "Script table as","Create to","New query window". Look for the collation settings on each field.

|||

Thank you for your response on the DB I have - Latin1_General_CI_AS

On the table I have -

****** Object:Table [dbo].[aspnet_Roles] Script Date: 05/22/2007 21:28:48 ******/SET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOCREATE TABLE [dbo].[aspnet_Roles]([ApplicationId] [uniqueidentifier]NOT NULL,[RoleId] [uniqueidentifier]NOT NULLCONSTRAINT [DF__aspnet_Ro__RoleI__03F0984C]DEFAULT (newid()),[RoleName] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL,[LoweredRoleName] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL,[Description] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,CONSTRAINT [PK__aspnet_Roles__02084FDA]PRIMARY KEY NONCLUSTERED ([RoleId]ASC)WITH (PAD_INDEX =OFF, IGNORE_DUP_KEY =OFF)ON [PRIMARY])ON [PRIMARY]GOALTER TABLE [dbo].[aspnet_Roles]WITH CHECK ADD CONSTRAINT [FK__aspnet_Ro__Appli__02FC7413]FOREIGN KEY([ApplicationId])REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])GOALTER TABLE [dbo].[aspnet_Roles]CHECK CONSTRAINT [FK__aspnet_Ro__Appli__02FC7413]

So what do I do to fix it?|||

Any body tell me how i can change the collation?

|||

ALTER DATABASE {DatabaseName} COLLATE {NewCollationName}

In this case ALTER DATABASE ASPNETDB COLLATE SQL_Latin1_General_CP1_CI_AS

|||

That worked, thank you.

Error with Execute sql task and full result set: " not been initialized before calling 'Fi

This is the first time I've tried creating an "execute sql task" with a "full result set".

I've read in the documentation that I must set the resultname to 0, which is done, and that the variable must be of type object. Also done.

[Execute SQL Task] Error: Executing the query "select * from blah" failed with the following error: "The SelectCommand property has not been initialized before calling 'Fill'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Has anyone else had success with a full result set?

Thanks,
-Lori

Yes. I have an example doing exactly this here:

SSIS Nugget: Execute SQL Task into an object variable - Shred it with a Foreach loop
(http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx)

-Jamie

|||Jamie,

The variable *is* of type Object (i believe i mentioned that).
Since this fails, I cannot move forward with the foreach loop.

I'm using 64bit sql server but debugging in Run64BitRuntime as False.

Any other ideas?

-Lori|||

lorijean44 wrote:

Jamie,

The variable *is* of type Object (i believe i mentioned that).

Yes you did. You also asked if anyone else has had any success which I have and was giving you a downloadable example of it working which I thought might be useful to you.

lorijean44 wrote:

Since this fails, I cannot move forward with the foreach loop.

I'm using 64bit sql server but debugging in Run64BitRuntime as False.

Any other ideas?

-Lori

Afraid not. Without being there its hard to say what the problem might be.

Regards

-Jamie

|||I noticed that you said in your blog, "just update the connection to point to your sql server". I thought I should mention that I'm actually trying to run this against a mysql server, with an ADO.net connection. I don't think that should matter, but who knows?

I'll play around and see if it works against a SQL Server just to try it.|||Yep, works great against a sql server (OLE DB Connection).

I will then update my question to be:
has anyone gotten a full result set to work with an ADO.net connection?|||

lorijean44 wrote:

I noticed that you said in your blog, "just update the connection to point to your sql server". I thought I should mention that I'm actually trying to run this against a mysql server, with an ADO.net connection. I don't think that should matter, but who knows?

I'll play around and see if it works against a SQL Server just to try it.

I'd bevery surprised if that was anything to do with it but who knows - give it a try.

Also note that my example used OLE DB Connection Manager rather than ADO.Net. That could be an avenue of investigation also.

-Jamie

Error with Execute sql task and full result set: " not been initialized before calling

This is the first time I've tried creating an "execute sql task" with a "full result set".

I've read in the documentation that I must set the resultname to 0, which is done, and that the variable must be of type object. Also done.

[Execute SQL Task] Error: Executing the query "select * from blah" failed with the following error: "The SelectCommand property has not been initialized before calling 'Fill'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Has anyone else had success with a full result set?

Thanks,
-Lori

Yes. I have an example doing exactly this here:

SSIS Nugget: Execute SQL Task into an object variable - Shred it with a Foreach loop
(http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx)

-Jamie

|||Jamie,

The variable *is* of type Object (i believe i mentioned that).
Since this fails, I cannot move forward with the foreach loop.

I'm using 64bit sql server but debugging in Run64BitRuntime as False.

Any other ideas?

-Lori|||

lorijean44 wrote:

Jamie,

The variable *is* of type Object (i believe i mentioned that).

Yes you did. You also asked if anyone else has had any success which I have and was giving you a downloadable example of it working which I thought might be useful to you.

lorijean44 wrote:

Since this fails, I cannot move forward with the foreach loop.

I'm using 64bit sql server but debugging in Run64BitRuntime as False.

Any other ideas?

-Lori

Afraid not. Without being there its hard to say what the problem might be.

Regards

-Jamie

|||I noticed that you said in your blog, "just update the connection to point to your sql server". I thought I should mention that I'm actually trying to run this against a mysql server, with an ADO.net connection. I don't think that should matter, but who knows?

I'll play around and see if it works against a SQL Server just to try it.|||Yep, works great against a sql server (OLE DB Connection).

I will then update my question to be:
has anyone gotten a full result set to work with an ADO.net connection?|||

lorijean44 wrote:

I noticed that you said in your blog, "just update the connection to point to your sql server". I thought I should mention that I'm actually trying to run this against a mysql server, with an ADO.net connection. I don't think that should matter, but who knows?

I'll play around and see if it works against a SQL Server just to try it.

I'd bevery surprised if that was anything to do with it but who knows - give it a try.

Also note that my example used OLE DB Connection Manager rather than ADO.Net. That could be an avenue of investigation also.

-Jamie

error with Execute package task

Hi,

We have used an execute package task in our master package to execute a child package and we have set the execute out of process=false. This master package is running fine in 32 bit server but is failing in 64 bit server. is there any settings to be done in the server or is it the problem with the property setting(execute out of process)

Vivek S

sorry for posting in SSRS category. Has been reposted in SSIS

Error while using sp_OAMethod

I get an error with HResult 10077 while using the sp_OAMethod to get an
'Item' from an array returned by VBScript.RegExp's Execute Method. Error
occurs after executing this for 3346 records.
I have seen article # 816937. But, didn't find much help.
My SQL Server version
===================
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows N
T
5.0 (Build 2195: Service Pack 4)
Can someone help me on this?
Kiran
Programmer AnalystCan you post some code to reproduce the issue?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Kiran" <Kiran@.discussions.microsoft.com> wrote in message
news:9913D55F-80D0-4357-A51B-470C5664E079@.microsoft.com...
> I get an error with HResult 10077 while using the sp_OAMethod to get an
> 'Item' from an array returned by VBScript.RegExp's Execute Method. Error
> occurs after executing this for 3346 records.
> I have seen article # 816937. But, didn't find much help.
> My SQL Server version
> ===================
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
NT
> 5.0 (Build 2195: Service Pack 4)
> Can someone help me on this?
> --
> Kiran
> Programmer Analyst
>|||I encountered the memory leak problem (816937) myself recently. After a
while it stopped returning results and would occasionally throw errors. We
finally had to re-boot, and then I found another tool to handle RegEx's in
SQL. Here's the link to the one I'm using currently:
http://www.codeproject.com/database...asp#xx963223xx
"Kiran" <Kiran@.discussions.microsoft.com> wrote in message
news:9913D55F-80D0-4357-A51B-470C5664E079@.microsoft.com...
>I get an error with HResult 10077 while using the sp_OAMethod to get an
> 'Item' from an array returned by VBScript.RegExp's Execute Method. Error
> occurs after executing this for 3346 records.
> I have seen article # 816937. But, didn't find much help.
> My SQL Server version
> ===================
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT
> 5.0 (Build 2195: Service Pack 4)
> Can someone help me on this?
> --
> Kiran
> Programmer Analyst
>

Error while using sp_OAMethod

I get an error with HResult 10077 while using the sp_OAMethod to get an
'Item' from an array returned by VBScript.RegExp's Execute Method. Error
occurs after executing this for 3346 records.
I have seen article # 816937. But, didn't find much help.
My SQL Server version
=================== Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT
5.0 (Build 2195: Service Pack 4)
Can someone help me on this?
--
Kiran
Programmer AnalystCan you post some code to reproduce the issue?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Kiran" <Kiran@.discussions.microsoft.com> wrote in message
news:9913D55F-80D0-4357-A51B-470C5664E079@.microsoft.com...
> I get an error with HResult 10077 while using the sp_OAMethod to get an
> 'Item' from an array returned by VBScript.RegExp's Execute Method. Error
> occurs after executing this for 3346 records.
> I have seen article # 816937. But, didn't find much help.
> My SQL Server version
> ===================> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
NT
> 5.0 (Build 2195: Service Pack 4)
> Can someone help me on this?
> --
> Kiran
> Programmer Analyst
>|||I encountered the memory leak problem (816937) myself recently. After a
while it stopped returning results and would occasionally throw errors. We
finally had to re-boot, and then I found another tool to handle RegEx's in
SQL. Here's the link to the one I'm using currently:
http://www.codeproject.com/database/xp_pcre.asp#xx963223xx
"Kiran" <Kiran@.discussions.microsoft.com> wrote in message
news:9913D55F-80D0-4357-A51B-470C5664E079@.microsoft.com...
>I get an error with HResult 10077 while using the sp_OAMethod to get an
> 'Item' from an array returned by VBScript.RegExp's Execute Method. Error
> occurs after executing this for 3346 records.
> I have seen article # 816937. But, didn't find much help.
> My SQL Server version
> ===================> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT
> 5.0 (Build 2195: Service Pack 4)
> Can someone help me on this?
> --
> Kiran
> Programmer Analyst
>

Error while using sp_OAMethod

I get an error with HResult 10077 while using the sp_OAMethod to get an
'Item' from an array returned by VBScript.RegExp's Execute Method. Error
occurs after executing this for 3346 records.
I have seen article # 816937. But, didn't find much help.
My SQL Server version
===================
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT
5.0 (Build 2195: Service Pack 4)
Can someone help me on this?
Kiran
Programmer Analyst
Can you post some code to reproduce the issue?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Kiran" <Kiran@.discussions.microsoft.com> wrote in message
news:9913D55F-80D0-4357-A51B-470C5664E079@.microsoft.com...
> I get an error with HResult 10077 while using the sp_OAMethod to get an
> 'Item' from an array returned by VBScript.RegExp's Execute Method. Error
> occurs after executing this for 3346 records.
> I have seen article # 816937. But, didn't find much help.
> My SQL Server version
> ===================
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
NT
> 5.0 (Build 2195: Service Pack 4)
> Can someone help me on this?
> --
> Kiran
> Programmer Analyst
>
|||I encountered the memory leak problem (816937) myself recently. After a
while it stopped returning results and would occasionally throw errors. We
finally had to re-boot, and then I found another tool to handle RegEx's in
SQL. Here's the link to the one I'm using currently:
http://www.codeproject.com/database/...asp#xx963223xx
"Kiran" <Kiran@.discussions.microsoft.com> wrote in message
news:9913D55F-80D0-4357-A51B-470C5664E079@.microsoft.com...
>I get an error with HResult 10077 while using the sp_OAMethod to get an
> 'Item' from an array returned by VBScript.RegExp's Execute Method. Error
> occurs after executing this for 3346 records.
> I have seen article # 816937. But, didn't find much help.
> My SQL Server version
> ===================
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT
> 5.0 (Build 2195: Service Pack 4)
> Can someone help me on this?
> --
> Kiran
> Programmer Analyst
>

Error while trying to execute an SP in my CASE STATEMENT

Hi all,
How do i execute a stored procedure in the THEN CLAUSE of my CASE STATEMENT? Av been getting errors since.

Here is my code:

Alter PROCEDURE sp_getTxn (
@.m1 int = Null,
@.txn int = Null,
@.p2 int = Null,
@.amt int = Null,
@.pAccountno varchar(50) = 'Null',
@.DAcct int = Null,
@.Balance Decimal(19,4) = NULL OUTPUT,
@.pBalance Decimal(19,4) = NULL OUTPUT,
@.RowsReturned smallint = NULL OUTPUT )
AS
SET NOCOUNT ON

select CASE
WHEN @.m1 = 200 THEN case
when @.txn = 00 then ('exec dbo.CustOrderHist (@.CrAcct int)')

when @.txn = 01 then ('exec dbo.Sp_withdrawal')

when @.txn = 31 then exec dbo.CheckBalance(@.pAccountno varchar(50), @.pBalance Decimal(19,4) OUTPUT)

when @.txn = 38 then ('exec dbo.Sp_StatementOfAcct')
END
END
WHEN @.m1 = 420 THEN case
when @.txnType = 00 then ('exec dbo.Sp_reversal')

when @.txnType = 01 then ('exec dbo.Sp_reversal2')

when @.txnType = 31 then ('exec dbo.Sp_reversal3')
END
END

SET @.Balance = @.pBalance
Print @.Balance

Or is there an alternative to the above CASE statement that is easier and faster?

ThanksUse IF instead of CASE:
IF @.m1 = 200
BEGIN
IF @.txn = 00
BEGIN
exec dbo.CustOrderHist (@.CrAcct int)
END
ELSE IF @.txn = 01
BEGIN
exec dbo.Sp_withdrawal
END
ELSE IF @.txn = 31
...
END
ELSE IF @.m1 = 420
BEGIN
...|||Thanks for the response, the if--else--if works.
...

Error while trying to execute a user defined function

Hi,

I'm working on SQL SERVER 2005 Standard edition.

I created a user defined function on the dbo schema.

Is it possible to invoke a user defined function in a select clause only with the name of this function without precising the name of the schema.

if my function is called TOTO(), can i execute the command :

SELECT TOTO()

go

Actually when i try to execute this command i have the following error message :

Message 195, level 15, state 10 :

'TOTO' is not a known built-in function name option.

Can someone help me to solve this issue ?

try

select dbo.toto()

|||

I know that it works but i want to know if it's possible to execute this function without specifying the schema just like this :

SELECT toto()

go

|||

Locolito:

The response to your question is no: WIth a scalar function you must supply the "schema" qualifier -- such as dbo; however, the schema qualifier is not required with a table function.

Dave

Error while executing SSIS package from other SSIS package

Hi,

In our project we have two SSIS package.

And there is a task (Execute SSIS package) in First package that calls the execution of second package.

I m continuously receiving an error "Failed to decrypt protected XML node "PackagePassword" 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."

As we are running first package by job, job runs successfully logging above error

The protection level of second package is set to "EncryptSensitiveWithUserKey"

Can anybody please suggest how to handle it?

If these DTSX has been saved on the MSDB you could use 'Server Storage' as protection level.

EncryptSensitive.. is the default protection level for the packages.

|||

If protection level property of the package has EncryptSensitive WithUSerKey means that only the author of the package is allowed to run it. This KB offers diffrent alternatives to solve your issue:

An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step:

http://support.microsoft.com/kb/918760

|||

Admin,

This topic is a good candidate for a "sticky" for the forum.

This question is asked once a day... we could come up with a synopsis on package encryption

|||

The DTSX is not saved in the MSDB,

its at disk only.

Error While DTS Execution from ASP

I ahve created a DTS package & was able to execute it
from backend but while trying to execute DTS Package
created in local Packages in sql client gets error.
Error Number 2147287038T
Error Description :The system cannot find the file
specified.
package Name :FSPETLISASUpload_DevServer1
Code used
set objDTSPackage = Server.CreateObject("DTS.Package")
on error resume next
objDTSPackage.LoadFromSQLServer "Moody", "fspapp", "w4wmm"
,
DTSSQLStgFlag_Default, "", "", "", "FSPETLISASUpload_DevSe
rver1"
Response.Write err.number & err.Description
Thanks in advance
As a test you may want to try the script (change the obvious variables),
contained at the following link:
http://support.microsoft.com/default...b;en-us;252987
Steve
"Aparna" <nandi_aparna@.yahoo.com> wrote in message
news:02d201c49aa1$d1cef530$a401280a@.phx.gbl...
> I ahve created a DTS package & was able to execute it
> from backend but while trying to execute DTS Package
> created in local Packages in sql client gets error.
>
> Error Number 2147287038T
> Error Description :The system cannot find the file
> specified.
> package Name :FSPETLISASUpload_DevServer1
> Code used
> set objDTSPackage = Server.CreateObject("DTS.Package")
> on error resume next
> objDTSPackage.LoadFromSQLServer "Moody", "fspapp", "w4wmm"
> ,
> DTSSQLStgFlag_Default, "", "", "", "FSPETLISASUpload_DevSe
> rver1"
> Response.Write err.number & err.Description
>
> Thanks in advance
>