Stored Procedures don;t seem to work

[mark_anderson_us]

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]

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,

[mark_anderson_us]

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

[Molly C]

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

[mark_anderson_us]

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