February 16, 2006 at 9:13 pm
I need to dynamically create a view based on connection parameters supplied.
But before I run 'Create View ...' want to check if Sever and Database mentioned in parameters are accessible.
Problem is it must UNION view accessing multiple databases on multiple servers, so I don't see the way to do it with error handling.
Can anybody help me to check if SELECT statement with those parameters is valid before I include it into UNION construction?
Thanks
_____________
Code for TallyGenerator
February 16, 2006 at 9:49 pm
You are going to have alot of problems trying to do this.
Most users will not have ddladmin permissions, you cannot grant users just create view permissions.
So the security chain will give you all kinds of issues.
you'll have to Pre-Construct all user views. and grant the user permission.
Maybe someone has a clever way, but I tried something like this, no where near as complex as you have, and I couldn't find a satisfactory way to do it. so I had to change gears and do it a different way.
February 17, 2006 at 7:28 am
sorry ??
You can't create a view which accept parameter's
because a view can't accept parameters?
February 17, 2006 at 9:38 am
Sergiy doesn't want to create a view that takes parameters, he wants to dynamically create views based on parameters passed into a stored procedure. The problem is that errors in creating views where the DDL is incorrect (tables or servers don't exists) causes fatal errors and aren't trappable.
I am not sure but you may be able to do this more easily with SQL 2005 as I believe it has better error handling and the ability to write code in .NET languages instead of SQL. Another option would be to do some of the checking via a frontend application with more robust error handling capabilites.
February 17, 2006 at 10:20 am
check for the existence of the server and the db from from the application and if succesfull exec the proc...
-Krishnan
February 17, 2006 at 7:28 pm
That's a problem.
Server may exist, and be accessible from application, but not accessible from the server where SP is trying to create the view. For example, because of Firewall settings.
That's why I need to check exactly from the same server and from real operational sequrity contents.
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply