Pass Through Query to Sybase SP

[Kryptorngr]

Hello,
I am using the following Pass Through query and need to change the declared
"Nulls" to a table reference to return multiple values.

execute dbo.p_prep_get_return_listing
@fund_or_bmk1=null,
@type1=null,
@return_type1=null,
@fund_or_bmk2=null,
@type2=null,

Can anyone help with the SQL syntax to change "Null" to a table within
Access.

Thanks

 

[Douglas J. Steele]

Pass-through queries are unable to see values in local tables in the MDB (or
MDE)

 

[Kryptorngr]

Thanks for the Response, do you know of a possible solution in VBA. My VBA
isn't very stong, but I can usually get through if I have a jumpstart.
Thanks

 

[Kryptorngr]

Douglas,
Another possible solution would be to hard code a list of the variables that
I need into the declared value (not elegant but whatever works) could you
help with the syntax on how to have a list of values to replace the "null"
value?

Thanks again.

 

[Douglas J. Steele]

To be perfectly honest, I'm not sure what you mean by replacing the "null"
value with a list of values.

Are you wanting to do something like

execute dbo.p_prep_get_return_listing
@fund_or_bmk1=1,
@type1='x',
@return_type1=True,
@fund_or_bmk2=2,
@type2='y'

 

[Kryptorngr]

Douglas,
Yes exactly, but I need to have multiple values instead of just one.

It would be more like:
execute dbo.p_prep_get_return_listing

@fund_or_bmk1="12345", "23456", "34567" and on.

I've tried several combos of commas and brackets but I'm only ever able to
get the 1st record set returned.

 

[Douglas J. Steele]

Will your stored procedure accept multiple values? I suspect not.

 

[Kryptorngr via AccessMonster.com]

Douglas,
1st let me just say thanks so much for the responses. I'm not there yet, but
you've stopped me from continuing to bang my head against brick walls. You're
right about the SP not allowing multiple values and as I'm not the owner of
the SP I can't really change anything in the Proc.

Researching this site I came across this piece of code you wrote in response
to a user back in June '05, and I thought it would work for my purposes as
well.

You Wrote:
"You need to use a pass-through query to run stored procedures. To pass
parameters, you have to dynamically rewrite the SQL each time.

Something like the following untested air-code:

Dim qdfCurr As DAO.QueryDef
DIm strSQL As String

Set qdfCurr = CurrentDb().QueryDefs("MyPassThroughQuery")
strSQL = "CALL MyStoredProcedure @Parm1=" & Me.txtValue & ", @Parm2='" &
Me.txtOtherValue & "'"
qdfCurr.SQL = strSQL
qdfCurr.Execute ' If the stored procedure doesn't return a record set

or

rsCurr = qdfCurr.OpenRecordset ' If the stored procedure returns a
record set"

So now I have 2 questions:

1. Can you parse for me the " & Me.txtValue & " statement? Can this be a
Table.Fieldname from a local table?

2. If yes to the above, how can I get this to pull a value from a table,
insert it into the proc as a parameter, run it, append the results to a table
then move to the next row in the table until all the rows have been run?

Any direction you can give would be greatly appreciated. I've got a book on
Access VBA programming I'll be reading over the weekend, so hopefully I'll be
able to get this piece of my project done soon.

Thanks again and have agreat weekend.

 

[Douglas J. Steele]

Let's assume you've got a table named MyParms, with a single field Parm1.

You could use something like the following untested air-code:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

Set dbCurr = CurrentDb()
Set qdfCurr = dbCurr.QueryDefs("MyPassThroughQuery")
Set rsCurr = dbCurr.OpenRecordset("SELECT Parm1 FROM MyParms")
Do Until rsCurr.EOF
strSQL = "CALL MyStoredProcedure @Parm1='" & rsCurr!Parm1 & "'"
qdfCurr.SQL = strSQL
qdfCurr.Execute dbFailOnError
rsCurr.MoveNext
Loop

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing

 

[Kryptorngr via AccessMonster.com]

Douglas,
I've converted your AirCode to the code below for my criteria. It does write
the SQL into the Pass Through Query, and pulls the 1st parameter from a table,
but it doesn't actually run the SP and it fails on the "qdfCurr.Execute
dbFailOnError" line with the runtime error '3065' Cannot execute a select
query. How can I get it to run the SP and append the results to a table
before moving to the next row?

Private Sub Command67_Click()
Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

Set dbCurr = CurrentDb()
Set qdfCurr = dbCurr.QueryDefs("RDC_Naked")
Set rsCurr = dbCurr.OpenRecordset("SELECT fund_or_bmk1 FROM
Fund_Bench_Combos")
Do Until rsCurr.EOF
strSQL = "execute dbo.p_prep_get_return_listing @fund_or_bmk1='" & rsCurr!
fund_or_bmk1 & "',@type1='F',@return_type1='T',@start_dt='12/31/2006',
@end_dt='2/28/2007'"
qdfCurr.SQL = strSQL
qdfCurr.Execute dbFailOnError ...........'This is where it fails'........
rsCurr.MoveNext
Loop

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing
End Sub

Thanks again.

 

[Douglas J. Steele]

What does the SP do? The error message implies it's just a select query:
that it doesn't actually do anything.

 

[Kryptorngr via AccessMonster.com]

Douglas,

Yes it just returns rows of data for the time period specified based on the
Fund code and Type supplied. The specific fields returned are from_dt, to_dt,
fund_or_bkm1, and p_return1. It acts just like a select query.

I need to store the results of what the SP retrieves.

Hope that helps,
Thanks

 

[dbahooker]

a) Don't use Sybase
b) Don't use MDB
c) DO use Access Data Projects with SQL Server

you could greatly simplify your database architectures by merely
moving to a freeware version of SQL Server

 

[Douglas J. Steele]

The only thing you can do with a SELECT query is open a recordset. You can
then do whatever you want with the data in that recordset.

Private Sub Command67_Click()
Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim rsSP As DAO.Recordset
Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

Set dbCurr = CurrentDb()
Set qdfCurr = dbCurr.QueryDefs("RDC_Naked")
Set rsCurr = dbCurr.OpenRecordset("SELECT fund_or_bmk1 FROM
Fund_Bench_Combos")
Do Until rsCurr.EOF
strSQL = "execute dbo.p_prep_get_return_listing @fund_or_bmk1='" &
rsCurr!
fund_or_bmk1 & "',@type1='F',@return_type1='T',@start_dt='12/31/2006',
@end_dt='2/28/2007'"
qdfCurr.SQL = strSQL

Set rsSP = qdfCurr.OpenRecordset
Do Until rsSP.EOF
' Do whatever you want with the data
rsSP.MoveNext
Loop

rsCurr.MoveNext
Loop

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing
End Sub

 

[Kryptorngr via AccessMonster.com]

Douglas,
I'm very close to getting the results I want, but can't get the right output.
I've used the following following "DoCmd.OpenQuery "qry_testSP", acViewNormal,
acEdit" where "qry_testSP" is an append query. When I run it however, I get
an ODBC call failure that states "Sybase does not allow more than one active
statement when retrieving results without a Cursor". I'm not sure what that
means or how to get around it.

I've also used DoCmd.Beep just to test and it does run through the code and
rewrites the SQL in the Pass Through query, beeping each time until the end
of the table I'm pulling from. Any reason why it would fail to append, but
not not fail to beep?

Thanks again

 

[Douglas J. Steele]

Does the SP work if you run it in Sybase?

I'm afraid I have no experience using Sybase, so I can't offer any
suggestions.

 

[dbahooker]

Sybase is _JUST_ like SQL Server

imagine it's 100% compatible

I've worked on sybase a half dozen times; once it had some really
really old version that didn't include functions such as 'left';
'right', 'year', 'month', etc

but it did have datepart and substring; so I could almost anything I
needed..


I've got to remark.. this sybase box; it was on a RS 6000.. and it was
like a measly 6-processor box.. but it would do a GREAT job at
caching.. i mean.. the first time that it returned results; it would
take a few seconds longer.

but ANY time I ran the same query? It was like instantly done.. it
was in general; the fastest friggin box I've ever worked against.

but the tools sure sucked; we had to use embarcadero

Does the SP work if you run it in Sybase?

I'm afraid I have no experience using Sybase, so I can't offer any
suggestions.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Douglas,
I'm very close to getting the results I want, but can't get the right
output.
I've used the following following "DoCmd.OpenQuery "qry_testSP",
acViewNormal,
acEdit" where "qry_testSP" is an append query. When I run it however, I
get
an ODBC call failure that states "Sybase does not allow more than one
active
statement when retrieving results without a Cursor". I'm not sure what
that
means or how to get around it.

I've also used DoCmd.Beep just to test and it does run through the code
and
rewrites the SQL in the Pass Through query, beeping each time until the
end
of the table I'm pulling from. Any reason why it would fail to append, but
not not fail to beep?

Thanks again

 

[Kryptorngr via AccessMonster.com]

Douglas,
Yes the SP works fine on Sybase. I would also add that the Query I'm opening
also works fine when I open it on its own. It's only when I open it from VBA
that I get the ODBC call failure. I'll keep trying to use different commands
to see if any can get through. Let me know if you have any further
suggestions.

Thanks.

 

[Kryptorngr via AccessMonster.com]

Douglas,
Any chance you know how to write this in ADO? I've talked to the Sybase folks
and they suggested pulling the first part of the query as an array and
reading the variables from there. I don't think that DAO would support this.

Thanks