sql server programming: I needed a complex view of data to be created, and ended up hiring a
contract company. They created a stored procedure that I can run from
within SQL Server Management Studio (SQL 2005), that populates a temp table.
My challenge is that my users do not have access to SQL, nor do they have
the skills.

This data needs to be accessed from within Microsoft Dynamics GP using a
tool called Smartlist Builder. Smartlist can grab data from only a view or
a table.

Ideally, whenever a user makes a change to the data, I would like the table
to be updated, but without running this stored procedure, that will not
happen. I know I can probably schedule this stored procedure to update the
table on a regular basis (although I am totally clueless), but no matter how
frequently I do so, the data will never be real time.

I tried creating a view with the exec statement, but got an error that "the
EXEC SQL construct or statement is not supported"

Is there anyway to run a stored procedure immediately before data in a table
is accessed?

Without seeing your DDL, the stored proc code and the business spec, it is
difficult to day. What is possible, though, is creating a trigger that
updates the appropriate table.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


[quoted text, click to view]
I needed a complex view of data to be created, and ended up hiring a
contract company. They created a stored procedure that I can run from
within SQL Server Management Studio (SQL 2005), that populates a temp table.
My challenge is that my users do not have access to SQL, nor do they have
the skills.

This data needs to be accessed from within Microsoft Dynamics GP using a
tool called Smartlist Builder. Smartlist can grab data from only a view or
a table.

Ideally, whenever a user makes a change to the data, I would like the table
to be updated, but without running this stored procedure, that will not
happen. I know I can probably schedule this stored procedure to update the
table on a regular basis (although I am totally clueless), but no matter how
frequently I do so, the data will never be real time.

I tried creating a view with the exec statement, but got an error that "the
EXEC SQL construct or statement is not supported"

Is there anyway to run a stored procedure immediately before data in a table
is accessed?

Cindy Mikeworth (cindy@tas-in.com) writes:
[quoted text, click to view]

You could add triggers on the tables involved and have the triggers
to fire the stored procedure. That would make it real time. It may
however have a backlash on performance´, if there are plenty of updates
to this table and the procedure takes a long time to run.

Depending on what the procedure does, it may be possible to transform it
to a multi-statement table-valued function and that you can package into
a view. But there are quite some limitations what you can do in a function,
so it's far from certain that you can take this track.

By the way, what is the nature of this temp table? Are the columns always
the same, or can they be different in different executions?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

By the way, the triggers Tom and Erland are talking about can only be run
when you do insert, update or delete. If you are thinking of running the
proc triggered by a select from a table (it appears to me that this is what
you are thinking), you are out of luck.

Quentin

[quoted text, click to view]

The temp table does have a static structure.

The trigger sounds like the way to go -- but I've never worked with SQL
triggers before. Can someone point me in a direction for a reference
resource on triggers?

I was able to convert the stored procedure to a table function.

Pretty impressed with myself, actually, since I had no clue as to what I was
doing. I only an example to mimic.

Thanks for the help!

Hopefully, your trigger can handle multi-row update/insert/delete scenarios,
or you will end up with incorrect data.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


[quoted text, click to view]
I was able to convert the stored procedure to a table function.

Pretty impressed with myself, actually, since I had no clue as to what I was
doing. I only an example to mimic.

Thanks for the help!