Getting the count of rows for 3 tables in Single Execute SQL task

hi frnds,

im very new to SSIS package .my package consists of Single Exceute task.

In Single Exceute SQL task i had 3 seperate queries to get the count of rows of 3 tables.

the Query goes like dis ...

select count(*) AS precheckcount1 from new_main_dts where cust_nbr like '875%'

like dis for another 2 tables i had written with the alias name precheckcount2 and 3

i mapped the variable user::precheckcount1 to precheckcount1 in the resultSet and for other two alias name i did the same.

while executing the package the error is thrown : [Execute SQL Task] Error: An error occurred while assigning a value to variable "precheckcount2": "Unable to find column precheckcount2 in the result set.".

please help me its very urgent

It is simplest if you get all values in a single result set. Set the ResultSet property to single row, then use a query like this to get the combined result sets-

SELECT (SELECT COUNT(*) FROM sysobjects) AS C1, (SELECT COUNT(*) FROM sysobjects WHERE type = 'U') AS C2

Then on the Result Set tab set the mappings using the column indexes, Result Name of 0 maps to the variable name for the first column's value. Result Name 1 maps to the variable name for the second column's value.

|||

thanks a lot.

my problem got resolved.

Getting Return Code from a stored procedure

How do I get the return code of a stored procedure,
Declare @.rtn as integer
set @.rtn = Execute myProcedure
does not work.
Is there an @.@...... value that I can check for the return code
Which is set when I execute a Return 1 in my code
I would like to use it like a function as I am having problems using
getdate() in a function.Declare @.rtn as integer
EXEC @.rtn = myProcedure
SELECT @.rtn
if @.rtn is 0, result is success.
Good luck.
"dpc"?? ??? ??:

> How do I get the return code of a stored procedure,
> Declare @.rtn as integer
> set @.rtn = Execute myProcedure
> does not work.
> Is there an @.@...... value that I can check for the return code
> Which is set when I execute a Return 1 in my code
> I would like to use it like a function as I am having problems using
> getdate() in a function.
>|||
> How do I get the return code of a stored procedure,
> Declare @.rtn as integer
> set @.rtn = Execute myProcedure
Try:
DECLARE @.rtn as integer
EXECUTE @.rtn = myProcedure

> I would like to use it like a function as I am having problems using
> getdate() in a function.
I don't understand this part of your question. Can you elaborate?
Hope this helps.
Dan Guzman
SQL Server MVP
"dpc" <dpc@.discussions.microsoft.com> wrote in message
news:490C4821-598B-4FB6-9C95-62EED799D8D3@.microsoft.com...
> How do I get the return code of a stored procedure,
> Declare @.rtn as integer
> set @.rtn = Execute myProcedure
> does not work.
> Is there an @.@...... value that I can check for the return code
> Which is set when I execute a Return 1 in my code
> I would like to use it like a function as I am having problems using
> getdate() in a function.
>|||DECLARE @.rtn as interger
EXECUTE @.rtn = myProcedure
Martin C K Poon
Senior Analyst Programmer
====================================
"dpc" <dpc@.discussions.microsoft.com> bl
news:490C4821-598B-4FB6-9C95-62EED799D8D3@.microsoft.com g...
> How do I get the return code of a stored procedure,
> Declare @.rtn as integer
> set @.rtn = Execute myProcedure
> does not work.
> Is there an @.@...... value that I can check for the return code
> Which is set when I execute a Return 1 in my code
> I would like to use it like a function as I am having problems using
> getdate() in a function.
>|||Why would you want to use getdate in your function?
UDFs don't allow non-deterministic function in their definition. and
getdate() is one.
If you want to use getdate inside your function, pass it as an input
parameter to your function (and send getdate() when you are calling the
function)
But if really want to use the current date within the function and you don't
want to accept it as parameter, then try this.. (a work around, thats all :)
create view curdate
as
select getdate() as now
and use it in the function..
--example
create function fngetdate()
returns datetime
as
begin
declare @.a datetime
select @.a = now from curdate
return (@.a)
end
--call to function
select dbo.fngetdate()
Hope this helps.

Getting records output from stored procedure into a table

I have a stored procedure that returns two sets of records, and I want to
execute the procedure and insert the second set of records into a temporary
table outside the procedure.
The last lines of the stored procedure look like this:
--Return count of programs first
SELECT COUNT(*)
FROM #Programs
--Return programs
SELECT *
FROM #Programs
ORDER BY
Day1Date
I have tried the following but this only captures the first set of records
(the count):
INSERT INTO #temp EXECUTE StoredProc
I cannot change the stored procedure. Is there a way to get the second set
into a temprary table (or even permanent table, table variable etc) ?This is a multi-part message in MIME format.
--=_NextPart_000_0067_01C3C3BB.8715A830
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Consider using an output parameter to store the count:
-- inside the called proc
SELECT @.count = COUNT(*)
FROM #Programs
-- inside the calling proc
declare @.count int
INSERT INTO #temp
EXECUTE StoredProc @.count output
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OSwOuR#wDHA.3116@.TK2MSFTNGP11.phx.gbl...
I have a stored procedure that returns two sets of records, and I want to
execute the procedure and insert the second set of records into a temporary
table outside the procedure.
The last lines of the stored procedure look like this:
--Return count of programs first
SELECT COUNT(*)
FROM #Programs
--Return programs
SELECT *
FROM #Programs
ORDER BY
Day1Date
I have tried the following but this only captures the first set of records
(the count):
INSERT INTO #temp EXECUTE StoredProc
I cannot change the stored procedure. Is there a way to get the second set
into a temprary table (or even permanent table, table variable etc) ?
--=_NextPart_000_0067_01C3C3BB.8715A830
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Consider using an output parameter to =store the count:
-- inside the called =proc
SELECT @.count =3D =COUNT(*)FROM #Programs
-- inside the calling =proc
declare @.count int
INSERT INTO #temp
EXECUTE StoredProc @.count output
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laurence Neville" wrote in message news:OSwOuR#wDHA.3116=@.TK2MSFTNGP11.phx.gbl...I have a stored procedure that returns two sets of records, and I want toexecute the procedure and insert the second set of records into a temporarytable outside the procedure.The last lines of the =stored procedure look like this:--Return count of programs =firstSELECT COUNT(*)FROM #Programs--Return programsSELECT *FROM #ProgramsORDER BYDay1DateI have tried the following but =this only captures the first set of records(the count):INSERT =INTO #temp EXECUTE StoredProcI cannot change the stored procedure. Is there =a way to get the second setinto a temprary table (or even permanent table, =table variable etc) ?

---

--=_NextPart_000_0067_01C3C3BB.8715A830--

Getting exception on executing ExecuteCellSet on AdomdCommand object

I'm trying to execute an mdx query in C# and getting exception on calling ExecuteCellSet() on AdomdCommand object,

This is the code snippet

// initialize a new adomdcommand object for the specified connection

objCommand = connection.CreateCommand();

// supply the command text for the adomdcommand object

objCommand.CommandText = queryString;

// attempt to run the command on the cell cellset

try

{

objCellSet = objCommand.ExecuteCellSet();

// return the retrieved cellset

return objCellSet;

}

catch (Exception ex)

{

Console.Write("Exception: " + ex);

objCellSet = null;

return objCellSet;

}

Exception: System.Data.DuplicateNameException: A DataTable named 'UOM' already b
elongs to this DataSet.
at System.Data.DataTableCollection.RegisterName(String name)
at System.Data.DataTableCollection.BaseAdd(DataTable table)
at System.Data.DataTableCollection.Add(DataTable table)
at Microsoft.AnalysisServices.AdomdClient.MDDatasetFormatter.ReadAxesInfo(Xml
Reader reader)
at Microsoft.AnalysisServices.AdomdClient.MDDatasetFormatter.ReadMDDataset(Xm
lReader reader)
at Microsoft.AnalysisServices.AdomdClient.SoapFormatter.ReadDataSetResponsePr
ivate(XmlReader reader)
at Microsoft.AnalysisServices.AdomdClient.SoapFormatter.ReadExecuteResponsePr
ivate(XmlReader reader, Boolean throwOnInlineErrors)
at Microsoft.AnalysisServices.AdomdClient.SoapFormatter.ReadResponse(XmlReade
r reader, Boolean throwOnInlineErrors)
at Microsoft.AnalysisServices.AdomdClient.XmlaClientProvider.Microsoft.Analys
isServices.AdomdClient.IExecuteProvider.ExecuteMultidimensional(ICommandContentP
rovider contentProvider, AdomdPropertyCollection commandProperties, IDataParamet
erCollection parameters)
at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.ExecuteCellSet()
at ceat.excel_writer.ceat_writer.ceat_writer_runQuery(AdomdConnection& connec
tion, String queryString)

When I run the query in MDX sample application editor, it is returning values.

Thanks for your help

DRK12345,

I am having a similar problem. I am wondering whether you solved your problem. Please let me know..

Thanks,

Golf

|||

hello,

which version of AS server are you connecting to? If this code works against AS2000, then i think the issue is that an older version of the msolap80 is used. Installing the latest version of msolap80 available should help i think. [can be downloaded for example from http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en (under Microsoft SQL Server 2000 PivotTable Services) or hotfix http://support.microsoft.com/?kbid=819609 could be used as well i think).

hope this helps,

|||

Mary,

Installing latest version of msolap80 fixed the problem. Thanks a lot for the help. I appreciate it.

Getting exception on executing ExecuteCellSet on AdomdCommand object

I'm trying to execute an mdx query in C# and getting exception on calling ExecuteCellSet() on AdomdCommand object,

This is the code snippet

// initialize a new adomdcommand object for the specified connection

objCommand = connection.CreateCommand();

// supply the command text for the adomdcommand object

objCommand.CommandText = queryString;

// attempt to run the command on the cell cellset

try

{

objCellSet = objCommand.ExecuteCellSet();

// return the retrieved cellset

return objCellSet;

}

catch (Exception ex)

{

Console.Write("Exception: " + ex);

objCellSet = null;

return objCellSet;

}

Exception: System.Data.DuplicateNameException: A DataTable named 'UOM' already b
elongs to this DataSet.
at System.Data.DataTableCollection.RegisterName(String name)
at System.Data.DataTableCollection.BaseAdd(DataTable table)
at System.Data.DataTableCollection.Add(DataTable table)
at Microsoft.AnalysisServices.AdomdClient.MDDatasetFormatter.ReadAxesInfo(Xml
Reader reader)
at Microsoft.AnalysisServices.AdomdClient.MDDatasetFormatter.ReadMDDataset(Xm
lReader reader)
at Microsoft.AnalysisServices.AdomdClient.SoapFormatter.ReadDataSetResponsePr
ivate(XmlReader reader)
at Microsoft.AnalysisServices.AdomdClient.SoapFormatter.ReadExecuteResponsePr
ivate(XmlReader reader, Boolean throwOnInlineErrors)
at Microsoft.AnalysisServices.AdomdClient.SoapFormatter.ReadResponse(XmlReade
r reader, Boolean throwOnInlineErrors)
at Microsoft.AnalysisServices.AdomdClient.XmlaClientProvider.Microsoft.Analys
isServices.AdomdClient.IExecuteProvider.ExecuteMultidimensional(ICommandContentP
rovider contentProvider, AdomdPropertyCollection commandProperties, IDataParamet
erCollection parameters)
at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.ExecuteCellSet()
at ceat.excel_writer.ceat_writer.ceat_writer_runQuery(AdomdConnection& connec
tion, String queryString)

When I run the query in MDX sample application editor, it is returning values.

Thanks for your help

DRK12345,

I am having a similar problem. I am wondering whether you solved your problem. Please let me know..

Thanks,

Golf

|||

hello,

which version of AS server are you connecting to? If this code works against AS2000, then i think the issue is that an older version of the msolap80 is used. Installing the latest version of msolap80 available should help i think. [can be downloaded for example from http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en (under Microsoft SQL Server 2000 PivotTable Services) or hotfix http://support.microsoft.com/?kbid=819609 could be used as well i think).

hope this helps,

|||

Mary,

Installing latest version of msolap80 fixed the problem. Thanks a lot for the help. I appreciate it.

Getting error in Execute SQL Task

Hi,

I am trying to execute a Stored Procedure using an Execute SQL Task. my stored proc contains various input paramaters. And the datatype for some of the paramaters are numeric (decimal). So when I am passing values to those paramaters using Exec SQL Task, I have used variables of type Object as it is not working properly even if I declare the variable type as "double". So I have used Object datatype for those variables and it worked properly.

But suddenly from yesterday onwards I am getting an error message (find below).

[Execute SQL Task] Error: Executing the query "USP_InsertData" failed with the following error: "Failed to convert parameter value from a Object to a Double.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

If any one have idea on this, please let me know to rectify this error.

Thanks in advance.

Regards,

Prakash Srinivasan

What's the value that it is trying to convert?

-Jamie

|||

Hi Jamie,

Such a fast reply it is. I am taking numeric value from table and passing that value through an SSIS's Object datatype variable to my stored proc. The datatype of the parameter in my stored proc is numeric(10,2).

Regards,

Prakash Srinivasan.

|||

Yes, but what is the actual value it is failing on? I woudl suggest that if works some of teh time then there are particular values that it doesn't like. Admittedly that seems a very strange thing to happen.

-Jamie

Getting error 0x80010112 while executing stored package

Hello everybody,

I can't find any posts concerning this error. While executing the stored package (using Execute Package Utility). I get error: SSIS Execution Properties "Could not load package "\File System\migration_package_1" because of error 0x80010112." The package is there and it was correctly loaded.

Please can anybody suggest what do do now or some workaround?

Thank you for any suggestions

Patrik Svestka

It seems this error was produced due to the SQL Server 2005 client & windows xp registry error. The only way out of this problem is complete system reinstalation :(.

Patrik Svestka