Alter view from a stored procedure RRS feed

  • Question

  • I've got an issue with this in a DTS package, I need to alter a view for the duration of the DTS so that it can be indexed to prevent the package from killing the server with a subtree cost of over 10,000. However we do a bulk load overnight to the tables in the view so I need to turn off the schemabinding so that the bulk load isn't slaughtered by the indexed view. When I try to run the script from the T-SQL step in DTS package (yes I'm working in SQL 2000 BC), I'm getting a message "Create Index failed" and something about Arithabort. Wrapping the indexed view creation in a stored proc would really help, so I'd be interested to know if it's possible? Any ideas anyone? D
    Wednesday, July 6, 2011 8:15 AM


  • Not sure about SQL 2000

    but you can do in SQL 2005 Onwards by dynamic sql ..

    create view kk_V 
    select A = 1
    select * from kk_V
    create proc usp_kk 
    declare @sql varchar(100)
    select @sql = 'ALTER VIEW kk_V AS SELECT A = 999 '
    print @sql
    select * from sysobjects 
    exec usp_kk
    select * from kk_V
    <br/>drop proc usp_kk<br/>drop view kk_V

    Hope that helps ... Kunal
    • Marked as answer by Dan Cambridge Thursday, July 7, 2011 10:28 AM
    Wednesday, July 6, 2011 11:34 AM