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?










share|improve this question



















  • 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 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
    yesterday















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?










share|improve this question



















  • 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 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
    yesterday













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?










share|improve this question















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 13 '18 at 5:52









marc_s

571k12811031251




571k12811031251










asked Nov 12 '18 at 23:42









MickB

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 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
    yesterday












  • 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 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
    yesterday







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
yesterday




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
yesterday












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


















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














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





















































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







Popular posts from this blog

Top Tejano songwriter Luis Silva dead of heart attack at 64

政党

天津地下鉄3号線