[dba-SQLServer] SP read only

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...



More information about the dba-SQLServer mailing list