[dba-SQLServer] SP read only

John
 
Very simple example I just ran and it recordset was updateable. I just added several records via the results window in Access.
 
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE PROCEDURE dbo.StoredProcedure2(@ID int)
AS SELECT      fldAssetTypeID, fldAssetTypeDescription, fldAssetTypeRecordStatus
FROM          dbo.tblAssetType
WHERE      (fldAssetTypeID = @ID)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

 
 
 
 
Martin WP Reid
Training and Assessment Unit
Riddle Hall
Belfast
 
tel: 02890 974477
 

________________________________

From: dba-sqlserver-bounces at databaseadvisors.com on behalf of Martin Reid
Sent: Thu 26/01/2006 22:03
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