Paul Hartland
Paul.Hartland at orridge.co.uk
Fri Jan 27 06:38:27 CST 2006
Just a sideline to this discussion about read only stored procedures, is it possible to have an updateable stored procedure recordset in visual basic ? If so, anyone know how I go about doing this ? Thanks in advance for any help on this. PAUL HARTLAND Database Designer/Programmer paul.hartland at orridge.co.uk DDI - 01922 472031 Mobile - 07730 523179 -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of John Colby Sent: 27 January 2006 12:30 To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] SP read only >have you tested an ADO recordset to see if it is editable...? Nope. John W. Colby www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Francisco Tapia Sent: Thursday, January 26, 2006 9:58 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] SP read only have you tested an ADO recordset to see if it is editable...? On 1/26/06, John Colby <jwcolby at colbyconsulting.com> wrote: > > I ran the SP from inside of Server Management Studio express. I am > using 2005. I am not using ADPs so there is no way to directly open a > SP from Access. You have to either do an ado recordset or a pass > through query. I had the pass through working but that was RO. I > have yet to get the Recordset working, at least to the point of > setting an access object's Recordset property (I get an "invalid > object"). > > I am using Microsoft SQL Server Management Studio Express. I build > the SPs in SMSE and I execute the SP there as well. I can "open" a > table and the table is RW, but apparently all (even simple) SPs are > RO. The SPs are RO when used via a pass through out in Access. At > this point I have to wonder if SQL Server 2005 Express has a setting > or an intentional limitation causing this behavior. > > I have built two SPs in SMSE: > > SELECT * FROM tlkpState > > As well as > > SELECT ST_ID, ST_Code FROM tlkpState > > This is the smallest, simplest table in the database. Both of these > SPs are RO. > > Going to the db and opening the linked (ODBC) table it is r/w. > Opening the table directly in SMSE the table is r/w. It has to do > with being a SP. > What a waste of time. 8( > > John W. Colby > www.ColbyConsulting.com > > > -----Original Message----- > From: dba-sqlserver-bounces at databaseadvisors.com > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of > Martin Reid > Sent: Thursday, January 26, 2006 5:03 PM > To: dba-sqlserver at databaseadvisors.com > Subject: Re: [dba-SQLServer] SP read only > > John > > How do you execute the SP from Access? > > Martin > > > Martin WP Reid > Training and Assessment Unit > Riddle Hall > Belfast > > tel: 02890 974477 > > > ________________________________ > > From: dba-sqlserver-bounces at databaseadvisors.com on behalf of John > Colby > Sent: Thu 26/01/2006 21:47 > To: dba-sqlserver at databaseadvisors.com > Subject: Re: [dba-SQLServer] SP read only > > > > Nope, didn't help > > > John W. Colby > www.ColbyConsulting.com > > > -----Original Message----- > From: dba-sqlserver-bounces at databaseadvisors.com > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of > Martin Reid > Sent: Thursday, January 26, 2006 4:18 PM > To: dba-sqlserver at databaseadvisors.com > Subject: Re: [dba-SQLServer] SP read only > > John > > Remove the OrderBY and try it > > Martin > > Martin WP Reid > Training and Assessment Unit > Riddle Hall > Belfast > > tel: 02890 974477 > > > ________________________________ > > From: dba-sqlserver-bounces at databaseadvisors.com on behalf of John > Colby > Sent: Thu 26/01/2006 20:59 > To: dba-sqlserver at databaseadvisors.com > Subject: [dba-SQLServer] SP read only > > > > Folks, > > I have the following SP. It selects data from a single table, > filtered on two fields. Pretty simple. It is also read-only inside > the SMS Express tool. The table itself is editable, i.e. if I just > open the table I can modify values. Does anyone know if parameterized > SPs are supposed to be editable? > If so, what can cause the SP result set to be Read Only? > > SET ANSI_NULLS ON > GO > SET QUOTED_IDENTIFIER ON > GO > -- ============================================= > -- Author: <Author,,Name> > -- Create date: <Create Date,,> > -- Description: <Description,,> > -- ============================================= > CREATE PROCEDURE usp_FrmCallListFiltered > -- Add the parameters for the stored procedure here > @STID int, > @DIID int > AS > BEGIN > -- SET NOCOUNT ON added to prevent extra result sets from > -- interfering with SELECT statements. > SET NOCOUNT ON; > > -- Insert statements for procedure here > SELECT tblPeople.PE_ID, tblPeople.PE_IDCRF, > tblPeople.PE_Specialty , tblPeople.PE_LName, tblPeople.PE_FName, > tblPeople.PE_MName, tblPeople.PE_Addr1, tblPeople.PE_Addr2, > tblPeople.PE_City, tblPeople.PE_Zipcode1, tblPeople.PE_Zipcode2, > tblPeople.PE_IDST, tblPeople.PE_PhoneH, tblPeople.PE_PhoneW, > tblPeople.PE_Currempl, tblPeople.PE_Contact1, > tblPeople.PE_Licotherstates, tblPeople.PE_RateInfo, > tblPeople.PE_Worktime, tblPeople.PE_Email, tblPeople.PE_BusAddress, > tblPeople.PE_Certification, tblPeople.PE_LIC_Licno, > tblPeople.PE_DateStampName, tblPeople.PE_DateStampAddress, > tblPeople.PE_DateStampPhoneH, tblPeople.PE_PhoneCell, > tblPeople.PE_IDDNC, tblPeople.PE_IDIWH, tblPeople.PE_MBIF, > tblPeople.PE_MBIFFollowUpDate, tblPeople.PE_HotLead, > tblPeople.PE_HotLeadNotes, tblPeople.PE_Inactive, > tblPeople.PE_NoHomePhoneInfo, tblPeople.PE_FOE, tblPeople.PE_LIC_IDST, tblPeople.PE_LIC_IDDI > FROM tblPeople > WHERE (((tblPeople.PE_LIC_IDST)=@STID) AND > ((tblPeople.PE_LIC_IDDI)=@DIID)) > ORDER BY tblPeople.PE_LName, tblPeople.PE_FName; > > END > GO > > John W. Colby > www.ColbyConsulting.com > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > -- -Francisco http://pcthis.blogspot.com |PC news with out the jargon! http://sqlthis.blogspot.com | Tsql and More... _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ * This message is confidential. * This email, its content and any files transmitted with it are intended solely for the addressee and may be legally privileged and/or confidential. * Access by any other party is unauthorised without the express written permission of the sender. * If you have received this email in error you may not copy or use the contents, attachments or information in any way and any review, use, dissemination, forwarding, disclosure, alteration, printing of this information is strictly prohibited. Please destroy it and notify the sender via return e-mail. * This email has been prepared using information believed by Paul Hartland to be reliable and accurate, but the company makes no warranty as to accuracy or completeness. In particular the author does not accept responsibility for changes made to this email after it was sent. * Any opinions expressed in this document are those of the author and do not necessarily reflect the opinions of the company or its affiliates. The Orridge web site can be found at: http://www.orridge.co.uk