[dba-SQLServer] SP read only

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