Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
luisf
Starting Member
23 Posts |
 Posted - 2005-02-15 : 07:29:12
|
| Can anybody tell me if it si posible to define stored procedures (queries) in Access having output parameters? how?Thanx! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-15 : 07:45:26
|
| Not with a query. A macro might work, but I've rarely used them in Access so I'm not certain.Access can run stored procedures in SQL Server, but unless the procedure returns results via a SELECT, Access won't retrieve anything. You *might* be able to do this in a pass-through query:DECLARE @p1 varchar(10);EXECUTE myProcedure @p1 OUTPUT;SELECT @p1 AS Result;I've never tried it, it may not work. |
 |
|
|
luisf
Starting Member
23 Posts |
Posted - 2005-02-15 : 11:15:35
|
| The problem is that the StoredProc is in SQL-Server, while my app is accessing data through an mdb (with linked tables).The app is using dao.It seems I cannot define a CDaoQueryDef for a proc that returns values... if actual implementation of the proc is in the SQL-Server DB. Or I dont find out how to do!Neither using dbPassThrough posibilities.The proc has capabilities, that I think are not in Access queries. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-02-15 : 11:19:26
|
| You can use ADO. that has full support for executing stored procedures and dealing with input and output parameters.- Jeff |
|
|
|
luisf
Starting Member
23 Posts |
Posted - 2005-02-15 : 11:31:38
|
| Im afraid I cannot use ADO... (;P)Legacy application with no oportunity of changing technologies at this moment.An the problem I think is not really to catch the return values -I think I can by DAO-, but Passing the query-ref directly to attached the SQL-server DB (so really passingthrough Access). |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-02-15 : 12:32:09
|
| >>Passing the query-ref directly to attached the SQL-server DB (so really passingthrough Access).???You need to give us specific information or a specific example of what you are trying to accomplish. It is too difficult to try to guess what you are talking about w/o more information.(i.e., For starters, are you working in Access VBA or some other language? )- Jeff |
|
|
|
luisf
Starting Member
23 Posts |
Posted - 2005-02-15 : 12:55:42
|
| Oops, yes it is not quite clear...Let's see:I've a VC++6 app, that uses DAO for accessing a table -call it T- in an MDB file.But, the MDB actually has T defined as a linked or attached (I'm not sure wich term is used in english) to table T in an SQL-Server DB.It works fine for application operations like:a) DaoRecordSets based on CDaoTableDef, whenever they are query operation, ie. non update (nor inserts, AddNew)b) DaoQueryDefs, which are limited to the kind of SQL statements jet can accept.I'm basically trying to insert a record, and have to return the ID (autoincremental/identity in SQL-Server) which is unknown until right after the insertion.It could be done by:-Defining a stored procedure -P-, which has to be in SQL-Server, that inserts the colums in T and returns de IDENTITY (last used). This would be perfect, but I cannot call this stored-proc (it is in SQL-Server, application runs agains MDB), neither using some kind of pass-through (for example I cannot use a QueryDef with SQL sentence "exec P..." and then executing by passthrough).-An other tested posibility is to do it in two stage:--1. execution of "insert into T ..." (by the mean of a DaoQueryDef); OK--2. execution of something like "SELECT IDENT_CURRENT('T') AS ID"; and the getting the result (field ID). This lead to error, beacuse it seems the select sentence is not valid for jet... but I dont want jet to filter that, it actually has to be executed in SQL-Server.I hope it is detailed enough.Thanks for the note.Some help on that please? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-02-15 : 14:07:04
|
| first off, as you probably know, since you are altering the app you should do it right and hit the SQL Server directly, using ADO. I'm sure you can give 100 reasons why you can't do this, but you really should. I guarantee you in the long run you'd be much better off.Now, assuming you WON'T do that, you have a few other options:1) use DAO to direclty access the SQL Server using ODBC. you can use OpenConnection() in the DAO library to open an ODBC (and maybe even OLEDB, not 100% sure) connection directly to the SQL Server. then you should be able to execute T-SQL syntax directly, w/o needing Access as a middleman. Again, though, this may not be 100% efficient or work very well since you should be use ADO (which is very similiar to DAO and takes about 10 minutes to learn the basics).or2) create QueryDef objects in your Access database, and establish them as PassThrough queries (Type = dbQSQLPassThrough). Specify an ODBC connect string (Connect property), specify the SQL statement (SQL property), indicate whether or not they return records (ReturnsRecords = True), and then execute them. you should be able to use any regular T-SQL statement, including executing stored procs. However, I am not sure about output parameters -- you may need to do use a trick similiar to what Rob showed, which I think should work.- Jeff |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-02-15 : 14:18:04
|
quote:
This would be perfect, but I cannot call this stored-proc (it is in SQL-Server, application runs agains MDB), neither using some kind of pass-through (for example I cannot use a QueryDef with SQL sentence "exec P..." and then executing by passthrough).
Don't miss my previous post, but also -- can you explain why you can't use a pass through query? Do you know how ? What have you tried, and what happens? an error? wrong results?- Jeff |
|
|
|
luisf
Starting Member
23 Posts |
Posted - 2005-02-16 : 05:17:18
|
| Ok. Let me change the the query.How can I call (I don't know) a remote procedure that is in SQL-Server?(in the situation described before) |
|
|
|
luisf
Starting Member
23 Posts |
Posted - 2005-02-16 : 05:47:00
|
| In particular: CDaoDatabase daodatabase; daodatabase.Open( SZ_MDB_SPEC_VINCULADA ); CDaoQueryDef daoquerydef( &daodatabase ); string strQueryName = "InsertaRegistroEnTabla2_Tabla"; daoquerydef.Open( strConsulta.c_str() ); COleVariant varParam1( (short) 98) ; COleVariant varParam2( (double) 1.2345 ) ; daoquerydef.SetParamValue( "Param1", varColumna1 ); daoquerydef.SetParamValue( "Param2", varColumna2 ); daoquerydef.Execute(dbSQLPassThrough); daoquerydef.Close();Execution notifies that Ms Jet cannot find "InsertaRegistroEnTabla2_Tabla".Thats true, because it is not in jet. I think pass-through call should not be "filtered" by Jet/MDB requirements, should'nt it?*SQL stored procedure is defined as: CREATE PROCEDURE InsertaRegistroEnTabla2_Tabla @Param1 integer, @Param2 float AS insert into Tabla (Column1,Colum2) values (@Param1, @Param2) select SCOPE_IDENTITY( ) as NuevoID |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-02-16 : 09:39:12
|
Again, you have to set the connect property correctly, specify pass-through, and indicate whether or not it returns records. the queryDef must be created correctly.Now, once that is done, the T-SQL of the querydef is basically what you would type into Query Analyzer -- there is no parsing of parameters by MS Access into the queryDef, you have to do it yourself and concatenate a valid SQL string to execute. Access has no clue that it is SQL Server you are calling in the pass-through -- it has no idea about how to handle stored procs, paramters, etc. it is simply executing a SQL string against the datasource. This is an important concept -- does it make sense? So, you need to make sure that in your SQL string create a valid T-SQL statement. so if you are executing the procedure "sp_Test" with parameters of 1, 'Jeff' and '1/1/2003', then your app must set the SQL property to:exec sp_Test 1, 'Jeff', '1/1/2003'and then you can execute the pass-through. By the way, did you read my other post at all? I explained exactly how to run queries directly off of the SQL Server box using DAO; using that method, JET is aware of the datasource and knows how to handle passing parameters .... Amazing how often people have problems that last over a week and when given help (for free) they often ignore most of the advice!  ... sometimes I just don't get it ....- Jeff |
|
|
|
luisf
Starting Member
23 Posts |
Posted - 2005-02-16 : 13:50:12
|
| I'm afraid I'd missed the post -I read "Don't miss my previous post", I thought you was talking about the very previous-...%PSo I'm reading that -very important- and the new.Briefly... No ADO because the application is based also in information currently in the mdb file (access user properties, or whatever they are called); For sure the application will be rebuild but not at this moment... (time!).The really important cue is what have told about "establish them as PassThrough queries".. I've just seen today that there exist that type of query when defining a query by Access. I've suspect about that, but not tried... (I usually do not use Access with non standard ANSI-SQL operations -like querys-).I suposed (seems to be wrong) that passthrough was just an option while programming, but...So, I DO need to define a query in the mdb (by Access) as PassThrough and also setting the Execute operation with dbPassThrough.Well, some points no so clear..."(...)it is simply executing a SQL string against the datasource"Yes, that was what I though.Schematically:-I've to open the MDB file with access and add a new query; I supose I have to give a name that is just for naming when Open() programatically by DAO-In the program:-- QueryDef open-- QueryDef set SQL (string)-- QueryDef execute (with dbPassThrough)-- How to get output params?-- Could it be used a RecordSet to get results?Thanks so much! |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-02-16 : 13:58:19
|
| First off, do you know what ADO and DAO are? They are just libraries that you use to access databases -- both can access SQL Server or Access or whatever you need. in your app, you can still connect to Access for what is stored locally there, and also make as many OTHER connections as needed, including directly to the SQL Server using ODBC or OLEDB.Before touching another line of code, I would really buy a good book on data access using C++ and also do some research (Google is great) to make sure you understand what you are wokring with. DAO is a library that works best with Access; it is NOT a database, it is just a library. ADO is ALSO a library (NOT a database format), but it is more flexible and works well with Access OR with SQL Server.>> How to get output params?Did you see Rob's Post? >> Could it be used a RecordSet to get results?Yes. Again, you have completely ignored Rob's post and the 3 times or so I've tried to get you to notice it and to see if you can make sense of it.- Jeff |
|
|
|
luisf
Starting Member
23 Posts |
Posted - 2005-02-17 : 10:21:31
|
| >>First off, do you know what ADO and DAO are? They are just libraries that you use to access databases -- both can access SQL Server or Access or whatever you need. in your app, you can still connect to Access for what is stored locally there, and also make as many OTHER connections as needed, including directly to the SQL Server using ODBC or OLEDB.>>Before touching another line of code, I would really buy a good book on data access using C++ and also do some research (Google is great) to make sure you understand what you are wokring with. DAO is a library that works best with Access; it is NOT a database, it is just a library. ADO is ALSO a library (NOT a database format), but it is more flexible and works well with Access OR with SQL Server.Yes, I know all that. Ive been reading a lot around; I made my homework; now, because of practical problems was why I request to the forum.In the future the app will be rebuilt, in particular: 1) for using ADO -as you say, beacuase of its flexibility, and beacuse DAO is "disapearing"-; 2) for avoiding all that information in the mdb which must be in the DB and not in the file; etc. The easiest way for getting the app running agains SQL-Server DB linking tables; but then I got into the limitations (or "no-direct-ways"). The first though was "just to link tables and run", but because the limitations I had to change "some" code; I will tackle the rebuilt later, now Iwant to make the minimum changes.[i/]>> Did you see Rob's Post? >> Yes. Again, you have completely ignored Rob's post and the 3 times or so I've tried to get you to notice it and to see if you can make sense of it.[/i]" Not with a query. A macro might work, but I've rarely used them in Access so I'm not certain."Seems to talk about running under Access, not programatically (C++).And he says no... [well, now I'll interpret that a passthrough query is not a query (?)]So the final reply is that I can run the passthrough query and pass to a DaoRecordSet, where I'll have the result of the select, won't?Thanks again for the time |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-17 : 19:49:56
|
quote:
well, now I'll interpret that a passthrough query is not a query
It certainly is a query. MS Access pass-through queries allow it to hit a data source natively via ODBC connections. No need for DAO or ADO or linked tables. If you're not using Access (which you said you were in your original post) then this feature will not help you. You also asked about stored procedures with output parameters. Access DOES NOT support this on its own, HOWEVER the technique I described would allow you to retrieve such output from a SQL Server stored procedure that used output variables, by using an Access pass-through query. If this is not what you intended, my apologies for mis-reading your question.Lastly, if you are simply trying to get data into a recordset, and not into an Access or SQL Server table, then you do not need to bother with linked tables or pass-through queries (this was not clear until your last post, and I'm still not sure what you're trying to do) You can simply use an ADO Recordset or Command object to query the data you want. I'm not clear about where exactly you're getting the data from (Access? SQL Server? Both?) I would strongly recommend that you NOT use DAO, especially if you are trying to retrieve data from SQL Server. It is simply not designed to do it properly, and ADO will be much easier and faster in this regard. |
|
|
|
luisf
Starting Member
23 Posts |
Posted - 2005-02-18 : 03:46:30
|
| I said that ironically; that was the reason I did not pay so much attention.The proposal for obtaining the result was an option I also though to be valid, but I did not know yes if it was possible to get a result (even as a recordset) when calling queries.Im using this schema:App <-> AccessMDB <-> SQL-ServerBoth for getting records (from a table), so DAO CDaoTableDef is OK, and for inserting. In this case (insertion) CDaoTableDef cannot be used. Moreover in some cases, when inserting a new record, I need the ID that has been used to be returned (I dont specify it because it is autoincremental/identity, so it is generated by the DBMS). So for the insertion I need a querydef, but cannot be direct to de MDB because it cannot do the insertion and obtain the ID. Then, a passthrough query seemed to be the solution (call whatever I want pseudo-directly to SQL-Server).But here I had problems with the tests made (Jet did not find the query -yes, because it is not defined in the mdb, but in SQL-Server-). From the posts by jsmith, I think Is that I need to define de query in the mdb (from Access) setting it as passthrough.PD. Using ADO is not an option at this time (more details in previous posts). |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-18 : 08:07:03
|
| Well, your reasons for not being able to use ADO are not clear. I can tell you from personal exeperience that DAO is NOT the way to go, whatever you might be trying to do. I don't think you need to modify any QueryDefs either. I'm not AT ALL clear if you are trying to program this application via an MS Access front end, or if your code is external and is simply trying to connect to an Access database. I've never seen or heard of a CDaoTableDef object, unless that is the name used when programming DAO in C++. At this point I really don't know what you're trying to do, but I do think you're focusing too much on trying to work with QueryDefs, and that they are not the right way for you to go. I'd suggest that you try to describe WHAT the program is supposed to be doing, NOT how you're trying to program it. And don't assume that you HAVE TO use DAO. Unless you're stuck using an MS Access database as a front end, DAO shouldn't even be considered an option. |
|
|
|
luisf
Starting Member
23 Posts |
Posted - 2005-02-22 : 10:19:56
|
| Thanx to all of you anyway. It has been usefull.I wonder why is so difficult to understand that it is a complex legacy app and changing technology is not a posibility at all. Moreover, I think is not necesary to understand just to help in the problem...Anyway, all the discussion motivates me more to trackle ADO in the near future :) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-22 : 11:28:53
|
quote:
Moreover, I think is not necesary to understand just to help in the problem...
Well, if someone doesn't understand a problem, I don't know how they're supposed to help solve it. Frankly I don't understand why you couldn't answer any of the questions I asked (repeatedly)quote:
I wonder why is so difficult to understand that it is a complex legacy app and changing technology is not a posibility at all
First, I'm pointing out, based on past experience, that the method you're trying to use WILL NOT WORK AT ALL. It has nothing to do with whether you can or can't use new technology. DAO is a dead end, and is only useful IF AND ONLY IF you are using MS Access. Since you never stated definitively that you are, I can't recommend using it at all.Secondly, I'm pointing out that you should NOT dismiss ADO as an option without at least trying it. Again, it's hard to suggest anything since you still have not explained what the code is trying to accomplish; you simply keep repeating that you want to use a DAO QueryDef for something.If you want good advice, you have to help us to help you. |
|
|
|
|
|
|
|
|
Return parameters