How can I execute a SQL Server stored proceedure from SSRS function code
1
I am trying to execute the following stored procedure
ALTER PROCEDURE [dbo].[sp_zJDEAB_SSID_Update]
(@BuyerNo INT, @SSCustID INT, @ReturnText VARCHAR(10) OUT)
AS
SET NOCOUNT ON
BEGIN TRY
UPDATE [dbo].[crp_F0101]
SET [ABURRF] = CAST(@SSCustID AS CHAR(15))
WHERE ABAN8 = @BuyerNo
SELECT
@ReturnText = CASE
WHEN CAST(ab.ABURRF AS INT) = @SSCUSTID
THEN 'Updated'
ELSE 'Update failed'
END
FROM
dbo.crp_F0101 ab
WHERE
ABAN8 = @BuyerNo
END TRY
BEGIN CATCH
SELECT @ReturnText = 'sp Error'
END CATCH
from the following SSRS function:
Public Shared Dim UpdateText as String
Public Function UpdateF0101(JDEBuyerABNo as Integer, SSCustID as Integer) as String
If (JDEBuyerABNo>0 and SSCustID>0)
then
sp_zJDEAB_SSID_Update(Fields!JDE_BuyerABNo.Value, Fields!SS_CustID.Value, UpdateText output)
Else UpdateText = "No"
End If
Return UpdateText
End Function
I have tried several different format but the call to my stored procedure always results in a custom code error BC30451 (not declared).
What is the correct syntax for call a stored procedure that exists in the same database as the data pull for the report's grid?
tsql ssrs-2012
edited Nov 13 '18 at 5:52
marc_s
571k12811031252
asked Nov 12 '18 at 23:42
MickBMickB
61
add a comment |
1
I am trying to execute the following stored procedure
ALTER PROCEDURE [dbo].[sp_zJDEAB_SSID_Update]
(@BuyerNo INT, @SSCustID INT, @ReturnText VARCHAR(10) OUT)
AS
SET NOCOUNT ON
BEGIN TRY
UPDATE [dbo].[crp_F0101]
SET [ABURRF] = CAST(@SSCustID AS CHAR(15))
WHERE ABAN8 = @BuyerNo
SELECT
@ReturnText = CASE
WHEN CAST(ab.ABURRF AS INT) = @SSCUSTID
THEN 'Updated'
ELSE 'Update failed'
END
FROM
dbo.crp_F0101 ab
WHERE
ABAN8 = @BuyerNo
END TRY
BEGIN CATCH
SELECT @ReturnText = 'sp Error'
END CATCH
from the following SSRS function:
Public Shared Dim UpdateText as String
Public Function UpdateF0101(JDEBuyerABNo as Integer, SSCustID as Integer) as String
If (JDEBuyerABNo>0 and SSCustID>0)
then
sp_zJDEAB_SSID_Update(Fields!JDE_BuyerABNo.Value, Fields!SS_CustID.Value, UpdateText output)
Else UpdateText = "No"
End If
Return UpdateText
End Function
I have tried several different format but the call to my stored procedure always results in a custom code error BC30451 (not declared).
What is the correct syntax for call a stored procedure that exists in the same database as the data pull for the report's grid?
tsql ssrs-2012
edited Nov 13 '18 at 5:52
marc_s
571k12811031252
asked Nov 12 '18 at 23:42
MickBMickB
61
1
I've never actually tried this but I suspect you would have to create a connection to the database then a command object just as you would if you where writing the function in VB. I don't think you can use the report's connection. However, if you explain at what point the SP should execute, there might be a much simpler way to d it, such as calling it from the report's dataset.
– Alan Schofield
Nov 13 '18 at 0:04
1
Hey, it's been a while since I wrote an SSRS report, but I don't think it's possible to do this. Just remember that the report is not directly connected to the database and only operates on data that is pulled through the connector. I might be wrong, but it's kind of philosophically incorrect for a report to update data, much as we'd like to on occasion. I'll see if I can dream up a way of doing it though. Good luck with this, interesting question!
– MikeAinOz
Nov 13 '18 at 0:04
I think the update text part should be part of your dataset as a separate UPDATE query before your data query. Updating SQL server from VB in SSRS through an SSMS SP seems overly complicated.
– Hannover Fist
Nov 13 '18 at 0:40
Side note: you should not use thesp_prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoidsp_and use something else as a prefix - or no prefix at all!
– marc_s
Nov 13 '18 at 5:52
Seems it was not possible to call the sp directly in the ssrs custom vb code . From your custom code , obviously , you do not declare the sp_zJDEAB_SSID_Update in your code.(as function name or whatever) You could try to use the sp in the dataset query , call the stored procedure is not recommend in report . check more detailed information at : SSRS call stored procedure using ReportItems! textbox. Maybe custom report code?
– Mitarai Queen
Jan 2 at 2:43
add a comment |
1
1
1
I am trying to execute the following stored procedure
ALTER PROCEDURE [dbo].[sp_zJDEAB_SSID_Update]
(@BuyerNo INT, @SSCustID INT, @ReturnText VARCHAR(10) OUT)
AS
SET NOCOUNT ON
BEGIN TRY
UPDATE [dbo].[crp_F0101]
SET [ABURRF] = CAST(@SSCustID AS CHAR(15))
WHERE ABAN8 = @BuyerNo
SELECT
@ReturnText = CASE
WHEN CAST(ab.ABURRF AS INT) = @SSCUSTID
THEN 'Updated'
ELSE 'Update failed'
END
FROM
dbo.crp_F0101 ab
WHERE
ABAN8 = @BuyerNo
END TRY
BEGIN CATCH
SELECT @ReturnText = 'sp Error'
END CATCH
from the following SSRS function:
Public Shared Dim UpdateText as String
Public Function UpdateF0101(JDEBuyerABNo as Integer, SSCustID as Integer) as String
If (JDEBuyerABNo>0 and SSCustID>0)
then
sp_zJDEAB_SSID_Update(Fields!JDE_BuyerABNo.Value, Fields!SS_CustID.Value, UpdateText output)
Else UpdateText = "No"
End If
Return UpdateText
End Function
I have tried several different format but the call to my stored procedure always results in a custom code error BC30451 (not declared).
What is the correct syntax for call a stored procedure that exists in the same database as the data pull for the report's grid?
tsql ssrs-2012
edited Nov 13 '18 at 5:52
marc_s
571k12811031252
asked Nov 12 '18 at 23:42
MickBMickB
61
I am trying to execute the following stored procedure
ALTER PROCEDURE [dbo].[sp_zJDEAB_SSID_Update]
(@BuyerNo INT, @SSCustID INT, @ReturnText VARCHAR(10) OUT)
AS
SET NOCOUNT ON
BEGIN TRY
UPDATE [dbo].[crp_F0101]
SET [ABURRF] = CAST(@SSCustID AS CHAR(15))
WHERE ABAN8 = @BuyerNo
SELECT
@ReturnText = CASE
WHEN CAST(ab.ABURRF AS INT) = @SSCUSTID
THEN 'Updated'
ELSE 'Update failed'
END
FROM
dbo.crp_F0101 ab
WHERE
ABAN8 = @BuyerNo
END TRY
BEGIN CATCH
SELECT @ReturnText = 'sp Error'
END CATCH
from the following SSRS function:
Public Shared Dim UpdateText as String
Public Function UpdateF0101(JDEBuyerABNo as Integer, SSCustID as Integer) as String
If (JDEBuyerABNo>0 and SSCustID>0)
then
sp_zJDEAB_SSID_Update(Fields!JDE_BuyerABNo.Value, Fields!SS_CustID.Value, UpdateText output)
Else UpdateText = "No"
End If
Return UpdateText
End Function
I have tried several different format but the call to my stored procedure always results in a custom code error BC30451 (not declared).
What is the correct syntax for call a stored procedure that exists in the same database as the data pull for the report's grid?
tsql ssrs-2012
tsql ssrs-2012
edited Nov 13 '18 at 5:52
marc_s
571k12811031252
asked Nov 12 '18 at 23:42
MickBMickB
61
edited Nov 13 '18 at 5:52
marc_s
571k12811031252
asked Nov 12 '18 at 23:42
MickBMickB
61
edited Nov 13 '18 at 5:52
marc_s
571k12811031252
edited Nov 13 '18 at 5:52
marc_s
571k12811031252
edited Nov 13 '18 at 5:52
marc_s
571k12811031252
571k12811031252
asked Nov 12 '18 at 23:42
MickBMickB
61
asked Nov 12 '18 at 23:42
MickBMickB
61
asked Nov 12 '18 at 23:42
MickBMickB
61
61
1
I've never actually tried this but I suspect you would have to create a connection to the database then a command object just as you would if you where writing the function in VB. I don't think you can use the report's connection. However, if you explain at what point the SP should execute, there might be a much simpler way to d it, such as calling it from the report's dataset.
– Alan Schofield
Nov 13 '18 at 0:04
1
Hey, it's been a while since I wrote an SSRS report, but I don't think it's possible to do this. Just remember that the report is not directly connected to the database and only operates on data that is pulled through the connector. I might be wrong, but it's kind of philosophically incorrect for a report to update data, much as we'd like to on occasion. I'll see if I can dream up a way of doing it though. Good luck with this, interesting question!
– MikeAinOz
Nov 13 '18 at 0:04
I think the update text part should be part of your dataset as a separate UPDATE query before your data query. Updating SQL server from VB in SSRS through an SSMS SP seems overly complicated.
– Hannover Fist
Nov 13 '18 at 0:40
Side note: you should not use thesp_prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoidsp_and use something else as a prefix - or no prefix at all!
– marc_s
Nov 13 '18 at 5:52
Seems it was not possible to call the sp directly in the ssrs custom vb code . From your custom code , obviously , you do not declare the sp_zJDEAB_SSID_Update in your code.(as function name or whatever) You could try to use the sp in the dataset query , call the stored procedure is not recommend in report . check more detailed information at : SSRS call stored procedure using ReportItems! textbox. Maybe custom report code?
– Mitarai Queen
Jan 2 at 2:43
add a comment |
1
I've never actually tried this but I suspect you would have to create a connection to the database then a command object just as you would if you where writing the function in VB. I don't think you can use the report's connection. However, if you explain at what point the SP should execute, there might be a much simpler way to d it, such as calling it from the report's dataset.
– Alan Schofield
Nov 13 '18 at 0:04
1
Hey, it's been a while since I wrote an SSRS report, but I don't think it's possible to do this. Just remember that the report is not directly connected to the database and only operates on data that is pulled through the connector. I might be wrong, but it's kind of philosophically incorrect for a report to update data, much as we'd like to on occasion. I'll see if I can dream up a way of doing it though. Good luck with this, interesting question!
– MikeAinOz
Nov 13 '18 at 0:04
I think the update text part should be part of your dataset as a separate UPDATE query before your data query. Updating SQL server from VB in SSRS through an SSMS SP seems overly complicated.
– Hannover Fist
Nov 13 '18 at 0:40
Side note: you should not use thesp_prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoidsp_and use something else as a prefix - or no prefix at all!
– marc_s
Nov 13 '18 at 5:52
Seems it was not possible to call the sp directly in the ssrs custom vb code . From your custom code , obviously , you do not declare the sp_zJDEAB_SSID_Update in your code.(as function name or whatever) You could try to use the sp in the dataset query , call the stored procedure is not recommend in report . check more detailed information at : SSRS call stored procedure using ReportItems! textbox. Maybe custom report code?
– Mitarai Queen
Jan 2 at 2:43
1
1
I've never actually tried this but I suspect you would have to create a connection to the database then a command object just as you would if you where writing the function in VB. I don't think you can use the report's connection. However, if you explain at what point the SP should execute, there might be a much simpler way to d it, such as calling it from the report's dataset.
– Alan Schofield
Nov 13 '18 at 0:04
I've never actually tried this but I suspect you would have to create a connection to the database then a command object just as you would if you where writing the function in VB. I don't think you can use the report's connection. However, if you explain at what point the SP should execute, there might be a much simpler way to d it, such as calling it from the report's dataset.
– Alan Schofield
Nov 13 '18 at 0:04
1
1
Hey, it's been a while since I wrote an SSRS report, but I don't think it's possible to do this. Just remember that the report is not directly connected to the database and only operates on data that is pulled through the connector. I might be wrong, but it's kind of philosophically incorrect for a report to update data, much as we'd like to on occasion. I'll see if I can dream up a way of doing it though. Good luck with this, interesting question!
– MikeAinOz
Nov 13 '18 at 0:04
Hey, it's been a while since I wrote an SSRS report, but I don't think it's possible to do this. Just remember that the report is not directly connected to the database and only operates on data that is pulled through the connector. I might be wrong, but it's kind of philosophically incorrect for a report to update data, much as we'd like to on occasion. I'll see if I can dream up a way of doing it though. Good luck with this, interesting question!
– MikeAinOz
Nov 13 '18 at 0:04
I think the update text part should be part of your dataset as a separate UPDATE query before your data query. Updating SQL server from VB in SSRS through an SSMS SP seems overly complicated.
– Hannover Fist
Nov 13 '18 at 0:40
I think the update text part should be part of your dataset as a separate UPDATE query before your data query. Updating SQL server from VB in SSRS through an SSMS SP seems overly complicated.
– Hannover Fist
Nov 13 '18 at 0:40
Side note: you should not use the
sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all!– marc_s
Nov 13 '18 at 5:52
Side note: you should not use the
sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all!– marc_s
Nov 13 '18 at 5:52
Seems it was not possible to call the sp directly in the ssrs custom vb code . From your custom code , obviously , you do not declare the sp_zJDEAB_SSID_Update in your code.(as function name or whatever) You could try to use the sp in the dataset query , call the stored procedure is not recommend in report . check more detailed information at : SSRS call stored procedure using ReportItems! textbox. Maybe custom report code?
– Mitarai Queen
Jan 2 at 2:43
Seems it was not possible to call the sp directly in the ssrs custom vb code . From your custom code , obviously , you do not declare the sp_zJDEAB_SSID_Update in your code.(as function name or whatever) You could try to use the sp in the dataset query , call the stored procedure is not recommend in report . check more detailed information at : SSRS call stored procedure using ReportItems! textbox. Maybe custom report code?
– Mitarai Queen
Jan 2 at 2:43
add a comment |
0
active
oldest
votes
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
);
);
draft saved
draft discarded
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%2f53271705%2fhow-can-i-execute-a-sql-server-stored-proceedure-from-ssrs-function-code%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
draft saved
draft discarded
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%2f53271705%2fhow-can-i-execute-a-sql-server-stored-proceedure-from-ssrs-function-code%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
1
I've never actually tried this but I suspect you would have to create a connection to the database then a command object just as you would if you where writing the function in VB. I don't think you can use the report's connection. However, if you explain at what point the SP should execute, there might be a much simpler way to d it, such as calling it from the report's dataset.
– Alan Schofield
Nov 13 '18 at 0:04
1
Hey, it's been a while since I wrote an SSRS report, but I don't think it's possible to do this. Just remember that the report is not directly connected to the database and only operates on data that is pulled through the connector. I might be wrong, but it's kind of philosophically incorrect for a report to update data, much as we'd like to on occasion. I'll see if I can dream up a way of doing it though. Good luck with this, interesting question!
– MikeAinOz
Nov 13 '18 at 0:04
I think the update text part should be part of your dataset as a separate UPDATE query before your data query. Updating SQL server from VB in SSRS through an SSMS SP seems overly complicated.
– Hannover Fist
Nov 13 '18 at 0:40
Side note: you should not use the
sp_prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoidsp_and use something else as a prefix - or no prefix at all!– marc_s
Nov 13 '18 at 5:52
Seems it was not possible to call the sp directly in the ssrs custom vb code . From your custom code , obviously , you do not declare the sp_zJDEAB_SSID_Update in your code.(as function name or whatever) You could try to use the sp in the dataset query , call the stored procedure is not recommend in report . check more detailed information at : SSRS call stored procedure using ReportItems! textbox. Maybe custom report code?
– Mitarai Queen
Jan 2 at 2:43