Stored Procedures don;t seem to work
-
- Trusted Contributor
- Posts: 61
- Joined: Fri Mar 16, 2012 12:21 pm
Stored Procedures don;t seem to work
I have a stored procedure that returns a list of records. i can run it with no problems on the sql sever, but when I put it in the select statement field it doesnt return anything. Anyone know if this should work?
- Trent S
- Noventri Digital Signage Expert
- Posts: 71
- Joined: Thu Dec 10, 2009 1:51 pm
- Location: Maryland, USA
- Contact:
Re: Stored Procedures don;t seem to work
Hello mark_anderson_us,
Would you mind providing your procedure / select statement for us to try in a test project on our end?
Kind Regards,
Would you mind providing your procedure / select statement for us to try in a test project on our end?
Kind Regards,
Trent Semler
Art Director
Specialized Communications Corp. / Noventri
20940 Twin Springs Dr.
Smithsburg, MD 21783-1510 USA
301-790-0103
Digital Information Display
Menu Display
Flight Board
Art Director
Specialized Communications Corp. / Noventri
20940 Twin Springs Dr.
Smithsburg, MD 21783-1510 USA
301-790-0103
Digital Information Display
Menu Display
Flight Board
-
- Trusted Contributor
- Posts: 61
- Joined: Fri Mar 16, 2012 12:21 pm
Re: Stored Procedures don;t seem to work
Hi trent
it's running against my db so you would have same data, but if you did something like this for adventureworks (or any db by just changing the table name). This is for Adventure works
Create the SP
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetTopTenOrders
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT TOP(10) * FROM Sales.SalesOrderHeader
END
GO
Execute it: GetTopTenOrders
I tried with an without exec in front of the SPname
Regards
Mark
it's running against my db so you would have same data, but if you did something like this for adventureworks (or any db by just changing the table name). This is for Adventure works
Create the SP
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetTopTenOrders
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT TOP(10) * FROM Sales.SalesOrderHeader
END
GO
Execute it: GetTopTenOrders
I tried with an without exec in front of the SPname
Regards
Mark
Re: Stored Procedures don;t seem to work
Hello Mark,
Are you trying to create the stored procedure inside Noventri Suite, or is it created in another program (that you are attempting to execute using Suite)?
I'm not sure one would be able to create a stored procedure using the UI in Suite, but you may be able to access one.
I did a little research on the SQL ODBC driver and came up with this link:
http://msdn.microsoft.com/en-us/library ... 80%29.aspx
The reference suggests using a "call" keyword.
Best Regards,
-Mike C
Are you trying to create the stored procedure inside Noventri Suite, or is it created in another program (that you are attempting to execute using Suite)?
I'm not sure one would be able to create a stored procedure using the UI in Suite, but you may be able to access one.
I did a little research on the SQL ODBC driver and came up with this link:
http://msdn.microsoft.com/en-us/library ... 80%29.aspx
The reference suggests using a "call" keyword.
Best Regards,
-Mike C
-
- Trusted Contributor
- Posts: 61
- Joined: Fri Mar 16, 2012 12:21 pm
Re: Stored Procedures don;t seem to work
Hi Trent
thanks for quick reply
The SP already exists in the DB.
I fixed it partially
I'm not using ODBC, I'm using the native SQL driver so don't need the call. What I do need is the DB name (I omitted it because it was already visible at the top of the dialog)
So when I run AdventureWorks.dbo.GetTopTenOrders (my own SP), it worked OK
My original SP still doesn't show any results in the results pane or in the text field when I insert [1:1] (but it executes fine in SQL server).
The result the SP returns in SQL server is (569 chars).
Jose Cuervo\nEdward Sparks\nEnrique Schultz\nDanielle Webster\nHoward Patton\nKathy Adams\nEvelyn West\nPaula Stone\nMatthew Gross\nRene Osborne\nStacey Ryan\nWallace Ramos\nCameron Rivera\nRobert Mccormick\nJenna Pena\nDana Rice\nEverett Cobb\nEmanuel Brady\nDerek Frazier\nRolando Mills\nAllison Hardy\nEdward Sparks\nEnrique Schultz\nDanielle Webster\nHoward Patton\nKathy Adams\nEvelyn West\nPaula Stone\nMatthew Gross\nRene Osborne\nStacey Ryan\nWallace Ramos\nCameron Rivera\nRobert Mccormick\nJenna Pena\nDana Rice\nEverett Cobb\nEmanuel Brady\nDerek Frazier\n
I also tried reducing the length of the returned filed to 100 chars. Still nothing
Regards
mark
thanks for quick reply
The SP already exists in the DB.
I fixed it partially
I'm not using ODBC, I'm using the native SQL driver so don't need the call. What I do need is the DB name (I omitted it because it was already visible at the top of the dialog)
So when I run AdventureWorks.dbo.GetTopTenOrders (my own SP), it worked OK
My original SP still doesn't show any results in the results pane or in the text field when I insert [1:1] (but it executes fine in SQL server).
The result the SP returns in SQL server is (569 chars).
Jose Cuervo\nEdward Sparks\nEnrique Schultz\nDanielle Webster\nHoward Patton\nKathy Adams\nEvelyn West\nPaula Stone\nMatthew Gross\nRene Osborne\nStacey Ryan\nWallace Ramos\nCameron Rivera\nRobert Mccormick\nJenna Pena\nDana Rice\nEverett Cobb\nEmanuel Brady\nDerek Frazier\nRolando Mills\nAllison Hardy\nEdward Sparks\nEnrique Schultz\nDanielle Webster\nHoward Patton\nKathy Adams\nEvelyn West\nPaula Stone\nMatthew Gross\nRene Osborne\nStacey Ryan\nWallace Ramos\nCameron Rivera\nRobert Mccormick\nJenna Pena\nDana Rice\nEverett Cobb\nEmanuel Brady\nDerek Frazier\n
I also tried reducing the length of the returned filed to 100 chars. Still nothing
Regards
mark