[dba-SQLServer] SP read only

John Colby jwcolby at ColbyConsulting.com
Fri Jan 27 06:30:00 CST 2006 

>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

More information about the dba-SQLServer mailing list