I'd like to know if there is a way to catch the error messages when a tasks fails, that's because i's like to store every message on a user variable, so i could log all of them later, I was thinking that it may be possible with the event handlers, could it be?
regards
Yes. When an executable errors the error message is in the @.[System::ErrorDescription] variable scoped to the OnError eventhandler.
-Jamie
|||Thanks Jammie, i'll try to do so.
best regards
thankssUse SqlCommand like this:
YourCommandName = New SQLCommand("YourStoredProcedureHere",YourConnection)
YourCommandName.CommandType = CommandType.StoredProcedureYou can also add parameters to the SqlCommand.|||got it.thankss
I have to transfer data from source to destination using stored procedures result set. There might be some more transformation needed to store the final result in the destination table.
Appreciate an early feedback.
Qadir Syed
Just create a new instance of "OLE DB Command".
Pick and choose your connection, and call the command as exec <procedure name>
|||"Oledb command" Source executes stored procedures.But it does not recognize the output of the stored procedure.Is there any advice for executing such stored procedure?
|||Use a no-op select statement to "declare" metadata to the pipeline. Since stored procedures don't publish rowset meta-data like tables,views and table-valued functions, the first select statement of a stored procedure is used by the SQLClient OLEDB provider to determine column metadata.
Code Snippet
CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON
IF 1 = 0
BEGIN
SELECT CAST(1 as smallint) as Fake
-- Publish metadata
END
-- do real work starting here
DECLARE @.x char(1)
SET @.x = (SELECT '1')
SELECT cast(@.x as smallint)
RETURN
|||jaegd - thanks!!!|||It does not work If there are more than one rows are coming out of stored procedure.
|||I got it solved on my end by replacing all temporary tables by temporary variables.
|||
Please give an example of what doesn't work for you.
|||In the above post it should be table variable not temporary variable.Conclusion:
It was table variable that Used instead of temporary table.
|||Hey try with the example below
CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON
CREATE TABLE #test(
[id] [int] NULL,
[Name] [nchar](10) NULL,
[SirName] [nchar](10) NULL
) ON [PRIMARY]
INSERT INTO #test
SELECT '1','A','Z' union all select '2','b','y'
select id,name,SirName
from #test
drop table #test
RETURN
Please let me know the result.
|||
With a local temp table created in the stored procedure, use a no-op select statement to "declare" metadata to the pipeline.
Code Snippet
IF OBJECT_ID('[dbo].[GenMetadata]', 'P') IS NOT NULL
DROP PROCEDURE [dbo].[GenMetadata]
GO
CREATE PROCEDURE [dbo].[GenMetadata]
AS
SET NOCOUNT ON
IF 1 = 0
BEGIN
-- Publish metadata
SELECT CAST(NULL AS INT) AS id,
CAST(NULL AS NCHAR(10)) AS [Name],
CAST(NULL AS NCHAR(10)) AS SirName
END
-- Do real work starting here
CREATE TABLE #test
(
[id] [int] NULL,
[Name] [nchar](10) NULL,
[SirName] [nchar](10) NULL
)
INSERT INTO #test
SELECT '1',
'A',
'Z'
UNION ALL
SELECT '2',
'b',
'y'
SELECTid,
[Name],
SirName
FROM#test
DROP TABLE #test
RETURN
GO
|||Hi Thank you very much..
It is now working fine for the changes you suggested..
|||Hello,
With the procedure you have told,SSIS package able to detect output of the stored procedure.
But there another problem introduced bcz of this procedure.Where According to your procedure the SP returns two datasets.
First dataset having 1 row and this is as a result of First select statement.
Second dataset bcz of our actual select query.
So SSIS chooses first dataset,So returns only one row having Null values for all columns.
How to overcome from this?
|||
Post a sproc, or usage of the above sproc, which demonstrates the problem (e.g. OPENQUERY, EXEC, INSERT EXEC, so on...)
I have to transfer data from source to destination using stored procedures result set. There might be some more transformation needed to store the final result in the destination table.
Appreciate an early feedback.
Qadir Syed
Just create a new instance of "OLE DB Command".
Pick and choose your connection, and call the command as exec <procedure name>
|||"Oledb command" Source executes stored procedures.But it does not recognize the output of the stored procedure.Is there any advice for executing such stored procedure?
|||Use a no-op select statement to "declare" metadata to the pipeline. Since stored procedures don't publish rowset meta-data like tables,views and table-valued functions, the first select statement of a stored procedure is used by the SQLClient OLEDB provider to determine column metadata.
Code Snippet
CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON
IF 1 = 0
BEGIN
SELECT CAST(1 as smallint) as Fake
-- Publish metadata
END
-- do real work starting here
DECLARE @.x char(1)
SET @.x = (SELECT '1')
SELECT cast(@.x as smallint)
RETURN
|||jaegd - thanks!!!|||It does not work If there are more than one rows are coming out of stored procedure.
|||I got it solved on my end by replacing all temporary tables by temporary variables.
|||
Please give an example of what doesn't work for you.
|||In the above post it should be table variable not temporary variable.Conclusion:
It was table variable that Used instead of temporary table.
|||Hey try with the example below
CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON
CREATE TABLE #test(
[id] [int] NULL,
[Name] [nchar](10) NULL,
[SirName] [nchar](10) NULL
) ON [PRIMARY]
INSERT INTO #test
SELECT '1','A','Z' union all select '2','b','y'
select id,name,SirName
from #test
drop table #test
RETURN
Please let me know the result.
|||
With a local temp table created in the stored procedure, use a no-op select statement to "declare" metadata to the pipeline.
Code Snippet
IF OBJECT_ID('[dbo].[GenMetadata]', 'P') IS NOT NULL
DROP PROCEDURE [dbo].[GenMetadata]
GO
CREATE PROCEDURE [dbo].[GenMetadata]
AS
SET NOCOUNT ON
IF 1 = 0
BEGIN
-- Publish metadata
SELECT CAST(NULL AS INT) AS id,
CAST(NULL AS NCHAR(10)) AS [Name],
CAST(NULL AS NCHAR(10)) AS SirName
END
-- Do real work starting here
CREATE TABLE #test
(
[id] [int] NULL,
[Name] [nchar](10) NULL,
[SirName] [nchar](10) NULL
)
INSERT INTO #test
SELECT '1',
'A',
'Z'
UNION ALL
SELECT '2',
'b',
'y'
SELECTid,
[Name],
SirName
FROM#test
DROP TABLE #test
RETURN
GO
|||Hi Thank you very much..
It is now working fine for the changes you suggested..
|||Hello,
With the procedure you have told,SSIS package able to detect output of the stored procedure.
But there another problem introduced bcz of this procedure.Where According to your procedure the SP returns two datasets.
First dataset having 1 row and this is as a result of First select statement.
Second dataset bcz of our actual select query.
So SSIS chooses first dataset,So returns only one row having Null values for all columns.
How to overcome from this?
|||
Post a sproc, or usage of the above sproc, which demonstrates the problem (e.g. OPENQUERY, EXEC, INSERT EXEC, so on...)
I have to transfer data from source to destination using stored procedures result set. There might be some more transformation needed to store the final result in the destination table.
Appreciate an early feedback.
Qadir Syed
Just create a new instance of "OLE DB Command".
Pick and choose your connection, and call the command as exec <procedure name>
|||"Oledb command" Source executes stored procedures.But it does not recognize the output of the stored procedure.Is there any advice for executing such stored procedure?
|||Use a no-op select statement to "declare" metadata to the pipeline. Since stored procedures don't publish rowset meta-data like tables,views and table-valued functions, the first select statement of a stored procedure is used by the SQLClient OLEDB provider to determine column metadata.
Code Snippet
CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON
IF 1 = 0
BEGIN
SELECT CAST(1 as smallint) as Fake
-- Publish metadata
END
-- do real work starting here
DECLARE @.x char(1)
SET @.x = (SELECT '1')
SELECT cast(@.x as smallint)
RETURN
|||jaegd - thanks!!!|||It does not work If there are more than one rows are coming out of stored procedure.
|||I got it solved on my end by replacing all temporary tables by temporary variables.
|||
Please give an example of what doesn't work for you.
|||In the above post it should be table variable not temporary variable.Conclusion:
It was table variable that Used instead of temporary table.
|||Hey try with the example below
CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON
CREATE TABLE #test(
[id] [int] NULL,
[Name] [nchar](10) NULL,
[SirName] [nchar](10) NULL
) ON [PRIMARY]
INSERT INTO #test
SELECT '1','A','Z' union all select '2','b','y'
select id,name,SirName
from #test
drop table #test
RETURN
Please let me know the result.
|||
With a local temp table created in the stored procedure, use a no-op select statement to "declare" metadata to the pipeline.
Code Snippet
IF OBJECT_ID('[dbo].[GenMetadata]', 'P') IS NOT NULL
DROP PROCEDURE [dbo].[GenMetadata]
GO
CREATE PROCEDURE [dbo].[GenMetadata]
AS
SET NOCOUNT ON
IF 1 = 0
BEGIN
-- Publish metadata
SELECT CAST(NULL AS INT) AS id,
CAST(NULL AS NCHAR(10)) AS [Name],
CAST(NULL AS NCHAR(10)) AS SirName
END
-- Do real work starting here
CREATE TABLE #test
(
[id] [int] NULL,
[Name] [nchar](10) NULL,
[SirName] [nchar](10) NULL
)
INSERT INTO #test
SELECT '1',
'A',
'Z'
UNION ALL
SELECT '2',
'b',
'y'
SELECTid,
[Name],
SirName
FROM#test
DROP TABLE #test
RETURN
GO
|||Hi Thank you very much..
It is now working fine for the changes you suggested..
|||Hello,
With the procedure you have told,SSIS package able to detect output of the stored procedure.
But there another problem introduced bcz of this procedure.Where According to your procedure the SP returns two datasets.
First dataset having 1 row and this is as a result of First select statement.
Second dataset bcz of our actual select query.
So SSIS chooses first dataset,So returns only one row having Null values for all columns.
How to overcome from this?
|||
Post a sproc, or usage of the above sproc, which demonstrates the problem (e.g. OPENQUERY, EXEC, INSERT EXEC, so on...)
I have to transfer data from source to destination using stored procedures result set. There might be some more transformation needed to store the final result in the destination table.
Appreciate an early feedback.
Qadir Syed
Just create a new instance of "OLE DB Command".
Pick and choose your connection, and call the command as exec <procedure name>
|||"Oledb command" Source executes stored procedures.But it does not recognize the output of the stored procedure.Is there any advice for executing such stored procedure?
|||Use a no-op select statement to "declare" metadata to the pipeline. Since stored procedures don't publish rowset meta-data like tables,views and table-valued functions, the first select statement of a stored procedure is used by the SQLClient OLEDB provider to determine column metadata.
Code Snippet
CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON
IF 1 = 0
BEGIN
SELECT CAST(1 as smallint) as Fake
-- Publish metadata
END
-- do real work starting here
DECLARE @.x char(1)
SET @.x = (SELECT '1')
SELECT cast(@.x as smallint)
RETURN
|||jaegd - thanks!!!|||It does not work If there are more than one rows are coming out of stored procedure.
|||I got it solved on my end by replacing all temporary tables by temporary variables.
|||
Please give an example of what doesn't work for you.
|||In the above post it should be table variable not temporary variable.Conclusion:
It was table variable that Used instead of temporary table.
|||Hey try with the example below
CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON
CREATE TABLE #test(
[id] [int] NULL,
[Name] [nchar](10) NULL,
[SirName] [nchar](10) NULL
) ON [PRIMARY]
INSERT INTO #test
SELECT '1','A','Z' union all select '2','b','y'
select id,name,SirName
from #test
drop table #test
RETURN
Please let me know the result.
|||
With a local temp table created in the stored procedure, use a no-op select statement to "declare" metadata to the pipeline.
Code Snippet
IF OBJECT_ID('[dbo].[GenMetadata]', 'P') IS NOT NULL
DROP PROCEDURE [dbo].[GenMetadata]
GO
CREATE PROCEDURE [dbo].[GenMetadata]
AS
SET NOCOUNT ON
IF 1 = 0
BEGIN
-- Publish metadata
SELECT CAST(NULL AS INT) AS id,
CAST(NULL AS NCHAR(10)) AS [Name],
CAST(NULL AS NCHAR(10)) AS SirName
END
-- Do real work starting here
CREATE TABLE #test
(
[id] [int] NULL,
[Name] [nchar](10) NULL,
[SirName] [nchar](10) NULL
)
INSERT INTO #test
SELECT '1',
'A',
'Z'
UNION ALL
SELECT '2',
'b',
'y'
SELECTid,
[Name],
SirName
FROM#test
DROP TABLE #test
RETURN
GO
|||Hi Thank you very much..
It is now working fine for the changes you suggested..
|||Hello,
With the procedure you have told,SSIS package able to detect output of the stored procedure.
But there another problem introduced bcz of this procedure.Where According to your procedure the SP returns two datasets.
First dataset having 1 row and this is as a result of First select statement.
Second dataset bcz of our actual select query.
So SSIS chooses first dataset,So returns only one row having Null values for all columns.
How to overcome from this?
|||
Post a sproc, or usage of the above sproc, which demonstrates the problem (e.g. OPENQUERY, EXEC, INSERT EXEC, so on...)
I have to transfer data from source to destination using stored procedures result set. There might be some more transformation needed to store the final result in the destination table.
Appreciate an early feedback.
Qadir Syed
Just create a new instance of "OLE DB Command".
Pick and choose your connection, and call the command as exec <procedure name>
|||"Oledb command" Source executes stored procedures.But it does not recognize the output of the stored procedure.Is there any advice for executing such stored procedure?|||Use a no-op select statement to "declare" metadata to the pipeline. Since stored procedures don't publish rowset meta-data like tables,views and table-valued functions, the first select statement of a stored procedure is used by the SQLClient OLEDB provider to determine column metadata.
Code Snippet
CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON
IF 1 = 0
BEGIN
SELECT CAST(1 as smallint) as Fake
-- Publish metadata
END
-- do real work starting here
DECLARE @.x char(1)
SET @.x = (SELECT '1')
SELECT cast(@.x as smallint)
RETURN
|||jaegd - thanks!!!|||It does not work If there are more than one rows are coming out of stored procedure.|||I got it solved on my end by replacing all temporary tables by temporary variables.|||Please give an example of what doesn't work for you.
|||In the above post it should be table variable not temporary variable.Conclusion:
It was table variable that Used instead of temporary table.|||Hey try with the example below
CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON
CREATE TABLE #test(
[id] [int] NULL,
[Name] [nchar](10) NULL,
[SirName] [nchar](10) NULL
) ON [PRIMARY]
INSERT INTO #test
SELECT '1','A','Z' union all select '2','b','y'
select id,name,SirName
from #test
drop table #test
RETURN
Please let me know the result.|||
With a local temp table created in the stored procedure, use a no-op select statement to "declare" metadata to the pipeline.
Code Snippet
IF OBJECT_ID('[dbo].[GenMetadata]', 'P') IS NOT NULL
DROP PROCEDURE [dbo].[GenMetadata]
GO
CREATE PROCEDURE [dbo].[GenMetadata]
AS
SET NOCOUNT ON
IF 1 = 0
BEGIN
-- Publish metadata
SELECT CAST(NULL AS INT) AS id,
CAST(NULL AS NCHAR(10)) AS [Name],
CAST(NULL AS NCHAR(10)) AS SirName
END
-- Do real work starting here
CREATE TABLE #test
(
[id] [int] NULL,
[Name] [nchar](10) NULL,
[SirName] [nchar](10) NULL
)
INSERT INTO #test
SELECT '1',
'A',
'Z'
UNION ALL
SELECT '2',
'b',
'y'
SELECTid,
[Name],
SirName
FROM#test
DROP TABLE #test
RETURN
GO
|||Hi Thank you very much..
It is now working fine for the changes you suggested..|||Hello,
With the procedure you have told,SSIS package able to detect output of the stored procedure.
But there another problem introduced bcz of this procedure.Where According to your procedure the SP returns two datasets.
First dataset having 1 row and this is as a result of First select statement.
Second dataset bcz of our actual select query.
So SSIS chooses first dataset,So returns only one row having Null values for all columns.
How to overcome from this?|||
Post a sproc, or usage of the above sproc, which demonstrates the problem (e.g. OPENQUERY, EXEC, INSERT EXEC, so on...)
I have to transfer data from source to destination using stored procedures result set. There might be some more transformation needed to store the final result in the destination table.
Appreciate an early feedback.
Qadir Syed
Just create a new instance of "OLE DB Command".
Pick and choose your connection, and call the command as exec <procedure name>
|||"Oledb command" Source executes stored procedures.But it does not recognize the output of the stored procedure.Is there any advice for executing such stored procedure?
|||Use a no-op select statement to "declare" metadata to the pipeline. Since stored procedures don't publish rowset meta-data like tables,views and table-valued functions, the first select statement of a stored procedure is used by the SQLClient OLEDB provider to determine column metadata.
Code Snippet
CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON
IF 1 = 0
BEGIN
SELECT CAST(1 as smallint) as Fake
-- Publish metadata
END
-- do real work starting here
DECLARE @.x char(1)
SET @.x = (SELECT '1')
SELECT cast(@.x as smallint)
RETURN
|||jaegd - thanks!!!|||It does not work If there are more than one rows are coming out of stored procedure.
|||I got it solved on my end by replacing all temporary tables by temporary variables.
|||
Please give an example of what doesn't work for you.
|||In the above post it should be table variable not temporary variable.Conclusion:
It was table variable that Used instead of temporary table.
|||Hey try with the example below
CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON
CREATE TABLE #test(
[id] [int] NULL,
[Name] [nchar](10) NULL,
[SirName] [nchar](10) NULL
) ON [PRIMARY]
INSERT INTO #test
SELECT '1','A','Z' union all select '2','b','y'
select id,name,SirName
from #test
drop table #test
RETURN
Please let me know the result.
|||
With a local temp table created in the stored procedure, use a no-op select statement to "declare" metadata to the pipeline.
Code Snippet
IF OBJECT_ID('[dbo].[GenMetadata]', 'P') IS NOT NULL
DROP PROCEDURE [dbo].[GenMetadata]
GO
CREATE PROCEDURE [dbo].[GenMetadata]
AS
SET NOCOUNT ON
IF 1 = 0
BEGIN
-- Publish metadata
SELECT CAST(NULL AS INT) AS id,
CAST(NULL AS NCHAR(10)) AS [Name],
CAST(NULL AS NCHAR(10)) AS SirName
END
-- Do real work starting here
CREATE TABLE #test
(
[id] [int] NULL,
[Name] [nchar](10) NULL,
[SirName] [nchar](10) NULL
)
INSERT INTO #test
SELECT '1',
'A',
'Z'
UNION ALL
SELECT '2',
'b',
'y'
SELECTid,
[Name],
SirName
FROM#test
DROP TABLE #test
RETURN
GO
|||Hi Thank you very much..
It is now working fine for the changes you suggested..
|||Hello,
With the procedure you have told,SSIS package able to detect output of the stored procedure.
But there another problem introduced bcz of this procedure.Where According to your procedure the SP returns two datasets.
First dataset having 1 row and this is as a result of First select statement.
Second dataset bcz of our actual select query.
So SSIS chooses first dataset,So returns only one row having Null values for all columns.
How to overcome from this?
|||
Post a sproc, or usage of the above sproc, which demonstrates the problem (e.g. OPENQUERY, EXEC, INSERT EXEC, so on...)
I have a stored procedure that creates a normalized table from an existing denorm table. So I just need a simple way to call this SP from an aspx page. It would be good though to know how many records were effected, but this is not a requirement.
Pass the name of the stored procedure to a command object and set the CommandType to StoredProcedure. Then use ExecuteNonQuery to run it.
string query = "nameofsp";
string connect = "connectionstring";
using (SqlConnection conn = new SqlConnection(connect))
{
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
cmd.ExecuteNonQuery();
}
}
|||
Thanks for the quick reply.
I should have mentioned I'm building the apsx page with vb. How different will that look from the above?
Also, I have a DTS package that I have scheduled to run every week. But it appears I need to allow the user to also kick that off as needed. Can I use the above to call that? Or do I need to first create a SP that calls the DTS package?
Thanks!
|||Dim dbobj as new SQLConnection(connection_string_here)
dbobj.open()
Dim objCmd as new SQLCommand(sp_name_here, dbobj)
objCmd.CommandType = CommandType.StoredProcedure
objCmd.Parameters.Add("@.Param_Name_Here", Param_Value_Here) 'if any
objCmd.ExecuteNonQuery() 'Use ExecuteScalar if you want to return a single value
objCmd.Dispose()
dbobj.Close()
I would create the procedure first to run the DTS and call it as above.
|||
tomhirt:
I should have mentioned I'm building the apsx page with vb.
That would have been helpful, but not particularly necessary if you just copy and paste the C# code I gave into www.codechanger.com
How different will that look from the above?
Almost the same:
Dim query As String = "nameofsp"
Dim connect As String = "connectionstring"
Using conn As New SqlConnection(connect)
Using cmd As New SqlCommand(query, conn)
cmd.CommandType = CommandType.StoredProcedure
conn.Open()
cmd.ExecuteNonQuery()
End Using
End Using
I tend to use Using statements for objects that need to be closed and disposed. Saves me remembering to have to do it explicitly each time.
Also, I have a DTS package that I have scheduled to run every week. But it appears I need to allow the user to also kick that off as needed. Can I use the above to call that? Or do I need to first create a SP that calls the DTS package?
Thanks!
Like Rich said.
|||Excellent. I did not know I could use something like Code changer. Great refernce.