5/2/2024 4:29:44 PM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss ADO specific questions for use with SalesLogix. View the code of conduct for posting guidelines.
|
|
|
|
Run Stored Procedures
Posted: 26 Nov 07 7:50 AM
|
I want to run SP through my code which takes a parameter.and the Sp contains only select statements.
can anyone give the syntax to run the SP.i have no other option and will it give any problem for Remote DB's |
|
|
|
Re: Run Stored Procedures
Posted: 26 Nov 07 9:04 AM
|
Yes it will give a problem for the remote unless you have created the sp on the remote database as well. There are threads that have talked about doing this.
As far as the code to execute the sp here is an example of what we have used:
dim conSQL
set conSQL = Application.GetNewConnection
sSQL = "usp_MKT_GetCustomersEmail 'MA' "
conSQL.CursorLocation = 3
Set oRS = conSQL.Execute(sSQL)
This will return the results of the sp in a Record Set.
Ted |
|
|
|
Re: Run Stored Procedures
Posted: 27 Nov 07 12:22 AM
|
hi Ted !! Can you solve the problem.i used the below code
dim conSQL,oRS,sSQL
set conSQL = Application.GetNewConnection
sSQL = "sp_getsubordinatesof '"&vUser&"' "
conSQL.CursorLocation = 3
Set oRS = objSLXDB.GetNewRecordSet
Set oRS = conSQL.Execute(sSQL)
msgbox oRS.RecordCount'
its giving me error operation is not allowed when obj is closed if i remove Set oRS = objSLXDB.GetNewRecordSet then its saying operation is not allowed when obj is open
can you help me n correct the code please!!!!!!!!!!!!! |
|
|
|
Re: Run Stored Procedures
Posted: 27 Nov 07 6:48 AM
|
I am not sure. Definately you do not want to have the line Set oRS = objSLXDB.GetNewRecordSet. Side rant here - I personally avoid using the SalesLogix "built in" database script because I do not believe it always closes things properly and I think it causes memory leaks. I like my code to be tighter and have better controls. I have written my own variation of their script in which I do the same things but I do not rely on the objSLXDB.
But back to your question. As a test I put together a quick button event with this code:
Sub Button1Click(Sender)
Dim conSQL
Dim oRS
Dim sSQL
'
Set conSQL = Application.GetNewConnection
sSQL = "usp_SLX_GetMktg_Leadsource_Report 'LPMCPA000001'"
conSQL.CursorLocation = 3
Set oRS = conSQL.Execute(sSQL)
msgbox oRS.RecordCount'
End Sub
and I did not get an error, it did return the correct record number. Just to clarify though. You did say your sp was returning a recordset, it is not trying to do any updates, correct?
Ted |
|
|
|
Re: Run Stored Procedures
Posted: 27 Nov 07 7:05 AM
|
Hi Ted!! Thx a tonn for your reply. I had tried this but could not suceed. I gave the cursorlocation some thing different which might have caused the problem. Just to make you clear, i had used a table type variable in the stored procedure and used some insert statements into that table type variable. Does this cause any problem. Absolutely there are no update statements in the stored procedure. Awaiting your reply.
Thanks in advance. |
|
|
|
Re: Run Stored Procedures
Posted: 27 Nov 07 8:02 AM
|
Since you are using the SLX provider it might be balking (not happy) with the insert statements in the sp. The sp I have run do not need to do this, they are just pulling together data and returning the select statements so I have not tried this, but you might try creating an ADO connection instead of using the SLX OleDB Provider connection.
Ted |
|
|
|
Re: Run Stored Procedures
Posted: 28 Nov 07 12:35 AM
|
Hi Tedd,Look at the following query i used its giving me error rs doesnt support this property or method...
can you please help me out
Dim con,rs,cmd,str_empid, strconnect
set con=CreateObject("ADODB.Connection")
set rs=CreateObject("ADODB.Recordset")
set cmd=CreateObject("ADODB.Command")
strconnect = "Provider=SQLOLEDB;Data Source=hcs-srv-crm;Initial Catalog=saleslogix_test"
con.Open strconnect, "sysdba", "satyam$123"
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_GetSubordinatesOf"
cmd.Parameters.Append cmd.CreateParameter("ManagerId", adVarChar, adParamInput, 20, "U6UJ9A00000P")
cmd.Parameters.Append cmd.CreateParameter("DeptName", adVarChar, adParamInput, 20, "sh-parts")
rs = cmd.Execute
If Not rs.EOF Then
msgbox rs.RecordCount
End If |
|
|
|
Re: Run Stored Procedures
Posted: 28 Nov 07 12:36 AM
|
Hi Tedd,Look at the following query i used its giving me error rs doesnt support this property or method...
can you please help me out
Dim con,rs,cmd,str_empid, strconnect
set con=CreateObject("ADODB.Connection")
set rs=CreateObject("ADODB.Recordset")
set cmd=CreateObject("ADODB.Command")
strconnect = "Provider=SQLOLEDB;Data Source=hcs-srv-crm;Initial Catalog=saleslogix_test"
con.Open strconnect, "sysdba", "satyam$123"
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_GetSubordinatesOf"
cmd.Parameters.Append cmd.CreateParameter("ManagerId", adVarChar, adParamInput, 20, "U6UJ9A00000P")
cmd.Parameters.Append cmd.CreateParameter("DeptName", adVarChar, adParamInput, 20, "sh-parts")
rs = cmd.Execute
If Not rs.EOF Then
msgbox rs.RecordCount
End If |
|
|
|
Re: Run Stored Procedures
Posted: 28 Nov 07 12:37 AM
|
Hi Tedd,Look at the following query i used its giving me error rs doesnt support this property or method...
can you please help me out
Dim con,rs,cmd,str_empid, strconnect
set con=CreateObject("ADODB.Connection")
set rs=CreateObject("ADODB.Recordset")
set cmd=CreateObject("ADODB.Command")
strconnect = "Provider=SQLOLEDB;Data Source=hcs-srv-crm;Initial Catalog=saleslogix_test"
con.Open strconnect, "sysdba", "satyam$123"
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_GetSubordinatesOf"
cmd.Parameters.Append cmd.CreateParameter("ManagerId", adVarChar, adParamInput, 20, "U6UJ9A00000P")
cmd.Parameters.Append cmd.CreateParameter("DeptName", adVarChar, adParamInput, 20, "sh-parts")
rs = cmd.Execute
If Not rs.EOF Then
msgbox rs.RecordCount
End If |
|
|
|
Re: Run Stored Procedures
Posted: 28 Nov 07 12:42 AM
|
Hi Tedd,Look at the following query i used its giving me error rs doesnt support this property or method...
can you please help me out
Dim con,rs,cmd,str_empid, strconnect
set con=CreateObject("ADODB.Connection")
set rs=CreateObject("ADODB.Recordset")
set cmd=CreateObject("ADODB.Command")
strconnect = "Provider=SQLOLEDB;Data Source=hcs-srv-crm;Initial Catalog=saleslogix_test"
con.Open strconnect, "sysdba", "satyam$123"
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_GetSubordinatesOf"
cmd.Parameters.Append cmd.CreateParameter("ManagerId", adVarChar, adParamInput, 20, "U6UJ9A00000P")
cmd.Parameters.Append cmd.CreateParameter("DeptName", adVarChar, adParamInput, 20, "sh-parts")
rs = cmd.Execute
If Not rs.EOF Then
msgbox rs.RecordCount
End If |
|
|
|
Re: Run Stored Procedures
Posted: 28 Nov 07 4:33 PM
|
Try running the routine in debug mode and working out exactly which line is giving you the error. That may help you solve it by yourself, otherwise, it will definitely help us to troubleshoot.
BTW, posting the same message 4 times will not lessen the time it takes for people to respond  |
|
|
|
Re: Run Stored Procedures
Posted: 29 Nov 07 7:38 AM
|
I am not sure of your specific sp. But a few points:
you need to SET a rs. So the line should be:
SET rs = cmd.Execute
Here is my code that I used to test this out:
Dim con,rs,cmd,str_empid, strconnect
set con=CreateObject("ADODB.Connection")
set rs=CreateObject("ADODB.Recordset")
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
set cmd=CreateObject("ADODB.Command")
strconnect = "Provider=SQLOLEDB;Data Source=pmt-c-esi;Initial Catalog=slx_development"
con.Open strconnect, "sysdba", "*** MY PASSWORD ***"
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "usp_Test"
cmd.Parameters.Append cmd.CreateParameter("leadsourceid", adChar, adParamInput, 12, "LPMCPA0000J8")
Stop
Set rs = cmd.Execute()
If Not rs.EOF Then
msgbox rs.RecordCount & " - " & rs.fields(0).value
End If |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|