Francisco Tapia
fhtapia at gmail.com
Thu Jan 26 20:57:57 CST 2006
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...