May 30, 2008 at 10:41 am
Is anything wrong with the below code. Stored procedure is returning -1 rows when executed from command. Please help...
Function Main()
dim myConn
dim Rs1
dim Cmd1
' instantiate the ADO objects
set myConn = CreateObject("ADODB.Connection")
set Cmd1 = CreateObject("ADODB.Command")
set Rs1 = CreateObject("ADODB.Recordset")
myConn.Open = "Provider=SQLOLEDB.1;Data Source= Server; Initial Catalog= NorthWind; user id = 'uid'; password='pwd'"
Cmd1.ActiveConnection = myConn
Cmd1.CommandType = 4
Cmd1.CommandText = "test_sp"
Set Rs1 = Cmd1.Execute
msgbox Rs1.RecordCount
Main = DTSTaskExecResult_Success
End Function
May 31, 2008 at 3:51 am
Does uid.test_sp exist in the database NorthWind? And does uid has execute permissions on uid.test_sp?
May 31, 2008 at 9:18 am
Also - perhaps try setting NOCOUNT on in your stored proc. Messages being returned might be getting misinterpreted too.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 2, 2008 at 7:38 am
Thanks for the inputs...
The sp is in the db and it has access to execute the package. I am using SET NOCOUNT ON while creating the package.
June 4, 2008 at 1:35 pm
The stored procedure calls another package?
Have you added some debugging routines in the package to see if it actually executes?
June 5, 2008 at 3:59 pm
Yes, it does call another package
June 6, 2008 at 2:58 pm
sat.offers (5/30/2008)
Is anything wrong with the below code. Stored procedure is returning -1 rows when executed from command. Please help...Function Main()
dim myConn
dim Rs1
dim Cmd1
' instantiate the ADO objects
set myConn = CreateObject("ADODB.Connection")
set Cmd1 = CreateObject("ADODB.Command")
set Rs1 = CreateObject("ADODB.Recordset")
myConn.Open = "Provider=SQLOLEDB.1;Data Source= Server; Initial Catalog= NorthWind; user id = 'uid'; password='pwd'"
Cmd1.ActiveConnection = myConn
Cmd1.CommandType = 4
Cmd1.CommandText = "test_sp"
Set Rs1 = Cmd1.Execute
msgbox Rs1.RecordCount
Main = DTSTaskExecResult_Success
End Function
How many rows does the test_sp return when you run it from the stored procedure? Maybe you need to tell us more about the stored proc. Your function here looks ok to me.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply