Re: How can a recordset returned by a sproc be updated?

From: Jacco Schalkwijk (jacco.please.reply_at_to.newsgroups.mvps.org.invalid)
Date: 07/28/04 

Date: Wed, 28 Jul 2004 13:21:27 +0100

Hi Richard,

With the resultset metadata is returned to the client that allows ADO and
ODBC etc to create update , insert delete statements etc against the base
tables. This works for both stored procedures and views. For views you can
prevent this metadata being sent by creating the view with the WITH
VIEW_METADATA option. This will return metadata for the view instead of the
underlying tables.

You can revoke or deny permissions on the base tables as well if you want to
prevent updates. 

-- 
Jacco Schalkwijk
SQL Server MVP
"Richard Hayward" <richard@tortoise.demon.co.uk> wrote in message 
news:jj9gg01lvosqnu6kp0ok7m8oiogufj5rud@4ax.com...
>I have an application written in Delphi connecting to MSSQL 2000 via
> ADO.
>
> At the server, using the example pubs database I've created a stored
> procedure:
>
> CREATE  PROCEDURE spAuthor  AS
> select * from authors
>
> My application displays the resulting records in a grid.
> To my surprise, the application is also able, via ADO,  to update the
> table in pubs.
>
> Watching whats going on with SQL profiler,  somehow SQL gets
> generated, to update the correct table on the
> server. How can that be possible? I gave the application no knowledge
> of which table my data was coming from. Can ADO somehow get at the
> internal text of the stored procedure? Or does the returned recordset
> carry with it the names of the tables its fields came from?
>
> Next, I tried making a view and having the stored procedure select
> from the view instead:
>
> CREATE VIEW vAuthors
> AS
> select *
> from authors
>
> CREATE  PROCEDURE spAuthor  AS
> select * from vAuthors
>
>
> but still, SQL is somehow generated to update the underlying table.
>
>
> Could anyone enlighten me as to how this is possible?
>
> regards
> Richard
>
>
> richard@tortoise.demon.co.uk

Relevant Pages