SSIS: Execute SQL task fails when trying to call one stored procedure from another
I am running a Execute SQL task in which I am storing a variable which calls a procedure:
exec load_storedproc1 parameter1, parameter2
Now this stored procedure has a statement in the end which calls another stored procedure:
exec load_storedproc2 parameter1, parameter2
The parameters which are supposed to be passed for both the stored procedures are the same.
When I execute the SQL task, it calls the first stored procedure which executes successfully but when it reaches the end statement of it, which is to call the storedproc2, it fails with the error message:
Error: Invalid column name 'column1'.
Error: Invalid column name 'column2'.
Error: Invalid column name 'column3'.
column1, column2, column3 are columns from the table used in storedproc2.
I feel somehow the Execute SQL task is not able to call the storedproc2 and it keeps failing. I am using dbname.schemaname.tablename everywhere in storedproc2.
sql-server ssis edited Nov 26 '18 at 6:55
marc_s
582k13011231269
asked Nov 26 '18 at 6:44
Kuldip.DasKuldip.Das
4719
add a comment |
I am running a Execute SQL task in which I am storing a variable which calls a procedure:
exec load_storedproc1 parameter1, parameter2
Now this stored procedure has a statement in the end which calls another stored procedure:
exec load_storedproc2 parameter1, parameter2
The parameters which are supposed to be passed for both the stored procedures are the same.
When I execute the SQL task, it calls the first stored procedure which executes successfully but when it reaches the end statement of it, which is to call the storedproc2, it fails with the error message:
Error: Invalid column name 'column1'.
Error: Invalid column name 'column2'.
Error: Invalid column name 'column3'.
column1, column2, column3 are columns from the table used in storedproc2.
I feel somehow the Execute SQL task is not able to call the storedproc2 and it keeps failing. I am using dbname.schemaname.tablename everywhere in storedproc2.
sql-server ssis edited Nov 26 '18 at 6:55
marc_s
582k13011231269
asked Nov 26 '18 at 6:44
Kuldip.DasKuldip.Das
4719
Show us some code. Are you using SQL Server?
– jarlh
Nov 26 '18 at 6:46
Yes, I am using SQL server Create Procedure [dbo].[storedproc1] (parameter1 datetime, parameter2 Varchar(10)) as begin --procedure1 code-- exec [DWIKNOW].[dbo].[storedproc2] @parameter1,@parameter2 -- this statement is where the SQL task fails end go
– Kuldip.Das
Nov 26 '18 at 6:53
Please edit your question and put the code in there. You're going to need to include the code inside the second stored procedure. We can't debug code without seeing the code.
– Nick.McDermaid
Nov 26 '18 at 7:48
add a comment |
I am running a Execute SQL task in which I am storing a variable which calls a procedure:
exec load_storedproc1 parameter1, parameter2
Now this stored procedure has a statement in the end which calls another stored procedure:
exec load_storedproc2 parameter1, parameter2
The parameters which are supposed to be passed for both the stored procedures are the same.
When I execute the SQL task, it calls the first stored procedure which executes successfully but when it reaches the end statement of it, which is to call the storedproc2, it fails with the error message:
Error: Invalid column name 'column1'.
Error: Invalid column name 'column2'.
Error: Invalid column name 'column3'.
column1, column2, column3 are columns from the table used in storedproc2.
I feel somehow the Execute SQL task is not able to call the storedproc2 and it keeps failing. I am using dbname.schemaname.tablename everywhere in storedproc2.
sql-server ssis edited Nov 26 '18 at 6:55
marc_s
582k13011231269
asked Nov 26 '18 at 6:44
Kuldip.DasKuldip.Das
4719
I am running a Execute SQL task in which I am storing a variable which calls a procedure:
exec load_storedproc1 parameter1, parameter2
Now this stored procedure has a statement in the end which calls another stored procedure:
exec load_storedproc2 parameter1, parameter2
The parameters which are supposed to be passed for both the stored procedures are the same.
When I execute the SQL task, it calls the first stored procedure which executes successfully but when it reaches the end statement of it, which is to call the storedproc2, it fails with the error message:
Error: Invalid column name 'column1'.
Error: Invalid column name 'column2'.
Error: Invalid column name 'column3'.
column1, column2, column3 are columns from the table used in storedproc2.
I feel somehow the Execute SQL task is not able to call the storedproc2 and it keeps failing. I am using dbname.schemaname.tablename everywhere in storedproc2.
sql-server ssis sql-server ssis edited Nov 26 '18 at 6:55
marc_s
582k13011231269
asked Nov 26 '18 at 6:44
Kuldip.DasKuldip.Das
4719
edited Nov 26 '18 at 6:55
marc_s
582k13011231269
asked Nov 26 '18 at 6:44
Kuldip.DasKuldip.Das
4719
edited Nov 26 '18 at 6:55
marc_s
582k13011231269
edited Nov 26 '18 at 6:55
marc_s
582k13011231269
edited Nov 26 '18 at 6:55
marc_s
582k13011231269
582k13011231269
asked Nov 26 '18 at 6:44
Kuldip.DasKuldip.Das
4719
asked Nov 26 '18 at 6:44
Kuldip.DasKuldip.Das
4719
asked Nov 26 '18 at 6:44
Kuldip.DasKuldip.Das
4719
4719
Show us some code. Are you using SQL Server?
– jarlh
Nov 26 '18 at 6:46
Yes, I am using SQL server Create Procedure [dbo].[storedproc1] (parameter1 datetime, parameter2 Varchar(10)) as begin --procedure1 code-- exec [DWIKNOW].[dbo].[storedproc2] @parameter1,@parameter2 -- this statement is where the SQL task fails end go
– Kuldip.Das
Nov 26 '18 at 6:53
Please edit your question and put the code in there. You're going to need to include the code inside the second stored procedure. We can't debug code without seeing the code.
– Nick.McDermaid
Nov 26 '18 at 7:48
add a comment |
Show us some code. Are you using SQL Server?
– jarlh
Nov 26 '18 at 6:46
Yes, I am using SQL server Create Procedure [dbo].[storedproc1] (parameter1 datetime, parameter2 Varchar(10)) as begin --procedure1 code-- exec [DWIKNOW].[dbo].[storedproc2] @parameter1,@parameter2 -- this statement is where the SQL task fails end go
– Kuldip.Das
Nov 26 '18 at 6:53
Please edit your question and put the code in there. You're going to need to include the code inside the second stored procedure. We can't debug code without seeing the code.
– Nick.McDermaid
Nov 26 '18 at 7:48
Show us some code. Are you using SQL Server?
– jarlh
Nov 26 '18 at 6:46
Show us some code. Are you using SQL Server?
– jarlh
Nov 26 '18 at 6:46
Yes, I am using SQL server Create Procedure [dbo].[storedproc1] (parameter1 datetime, parameter2 Varchar(10)) as begin --procedure1 code-- exec [DWIKNOW].[dbo].[storedproc2] @parameter1,@parameter2 -- this statement is where the SQL task fails end go
– Kuldip.Das
Nov 26 '18 at 6:53
Yes, I am using SQL server Create Procedure [dbo].[storedproc1] (parameter1 datetime, parameter2 Varchar(10)) as begin --procedure1 code-- exec [DWIKNOW].[dbo].[storedproc2] @parameter1,@parameter2 -- this statement is where the SQL task fails end go
– Kuldip.Das
Nov 26 '18 at 6:53
Please edit your question and put the code in there. You're going to need to include the code inside the second stored procedure. We can't debug code without seeing the code.
– Nick.McDermaid
Nov 26 '18 at 7:48
Please edit your question and put the code in there. You're going to need to include the code inside the second stored procedure. We can't debug code without seeing the code.
– Nick.McDermaid
Nov 26 '18 at 7:48
add a comment |
1 Answer
1
active
oldest
votes
I found the answer to my issue. When we are calling one stored procedure from another stored procedure, in case we are using temp tables, the names of all the temp tables in both the stored procedure should be different as the SQL task will treat both as a single session.
I was using temp tables in the stored procedures and few of the names across both the stored procedures were same. After changing them, its working fine now.
edited Nov 26 '18 at 10:14
marc_s
582k13011231269
answered Nov 26 '18 at 9:04
Kuldip.DasKuldip.Das
4719
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53475941%2fssis-execute-sql-task-fails-when-trying-to-call-one-stored-procedure-from-anoth%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I found the answer to my issue. When we are calling one stored procedure from another stored procedure, in case we are using temp tables, the names of all the temp tables in both the stored procedure should be different as the SQL task will treat both as a single session.
I was using temp tables in the stored procedures and few of the names across both the stored procedures were same. After changing them, its working fine now.
edited Nov 26 '18 at 10:14
marc_s
582k13011231269
answered Nov 26 '18 at 9:04
Kuldip.DasKuldip.Das
4719
add a comment |
I found the answer to my issue. When we are calling one stored procedure from another stored procedure, in case we are using temp tables, the names of all the temp tables in both the stored procedure should be different as the SQL task will treat both as a single session.
I was using temp tables in the stored procedures and few of the names across both the stored procedures were same. After changing them, its working fine now.
edited Nov 26 '18 at 10:14
marc_s
582k13011231269
answered Nov 26 '18 at 9:04
Kuldip.DasKuldip.Das
4719
add a comment |
I found the answer to my issue. When we are calling one stored procedure from another stored procedure, in case we are using temp tables, the names of all the temp tables in both the stored procedure should be different as the SQL task will treat both as a single session.
I was using temp tables in the stored procedures and few of the names across both the stored procedures were same. After changing them, its working fine now.
edited Nov 26 '18 at 10:14
marc_s
582k13011231269
answered Nov 26 '18 at 9:04
Kuldip.DasKuldip.Das
4719
I found the answer to my issue. When we are calling one stored procedure from another stored procedure, in case we are using temp tables, the names of all the temp tables in both the stored procedure should be different as the SQL task will treat both as a single session.
I was using temp tables in the stored procedures and few of the names across both the stored procedures were same. After changing them, its working fine now.
edited Nov 26 '18 at 10:14
marc_s
582k13011231269
answered Nov 26 '18 at 9:04
Kuldip.DasKuldip.Das
4719
edited Nov 26 '18 at 10:14
marc_s
582k13011231269
edited Nov 26 '18 at 10:14
marc_s
582k13011231269
edited Nov 26 '18 at 10:14
marc_s
582k13011231269
582k13011231269
answered Nov 26 '18 at 9:04
Kuldip.DasKuldip.Das
4719
answered Nov 26 '18 at 9:04
Kuldip.DasKuldip.Das
4719
answered Nov 26 '18 at 9:04
Kuldip.DasKuldip.Das
4719
4719
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53475941%2fssis-execute-sql-task-fails-when-trying-to-call-one-stored-procedure-from-anoth%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Show us some code. Are you using SQL Server?
– jarlh
Nov 26 '18 at 6:46
Yes, I am using SQL server Create Procedure [dbo].[storedproc1] (parameter1 datetime, parameter2 Varchar(10)) as begin --procedure1 code-- exec [DWIKNOW].[dbo].[storedproc2] @parameter1,@parameter2 -- this statement is where the SQL task fails end go
– Kuldip.Das
Nov 26 '18 at 6:53
Please edit your question and put the code in there. You're going to need to include the code inside the second stored procedure. We can't debug code without seeing the code.
– Nick.McDermaid
Nov 26 '18 at 7:48