logo
down
shadow

Why does sp_refreshview raise an exception on views that are not schema bound?


Why does sp_refreshview raise an exception on views that are not schema bound?

By : user2949534
Date : November 17 2020, 01:00 AM
seems to work fine It looks to me like sp_refreshview should be run from the database that houses the view. So change the execute line to:
code :
 EXEC dbo.sp_refreshview @ViewName;


Share : facebook icon twitter icon
How do I query schema bound views?

How do I query schema bound views?


By : Charles lee
Date : March 29 2020, 07:55 AM
will be helpful for those in need I can list all views in SQL Server 2008 by using
code :
SELECT * 
FROM sys.views 
WHERE OBJECTPROPERTY(object_id, 'IsSchemaBound') = 1
SQL Server 2008 - sp_refreshview bombing out on some views

SQL Server 2008 - sp_refreshview bombing out on some views


By : user3222752
Date : March 29 2020, 07:55 AM
fixed the issue. Will look into that further I noticed in the comments you mention it has SCHEMABINDING. I can almost guarantee that is the issue. Books online specifically says this is for use on non-schema bound views.
A scheme-bound view wouldn't allow a breaking change to occur so updating the meta-data is un-necessary. You can safely skip it.
code :
SELECT * FROM sys.views WHERE OBJECTPROPERTY(object_id, 'IsSchemaBound')=1
How could I script all schema-bound views (and other objects that prevent table truncate like "filtered indexes&quo

How could I script all schema-bound views (and other objects that prevent table truncate like "filtered indexes&quo


By : Rustico Guitarte III
Date : March 29 2020, 07:55 AM
around this issue How could I script all schema-bound views? , SMO / powershell solution:
code :
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')|Out-Null; 
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended')|Out-Null; 
$sr = '<server>'
$ln = '<login>'
$pw = '<password>'
$db = '<database>'

$u = 'Microsoft.SqlServer.Management.Smo'
$n = [Environment]::NewLine;
$s = new-object ($u+'.Server') $sr;
$c = $s.ConnectionContext;$c.LoginSecure=$false;$c.Login=$ln;$c.Password=$pw;
$s.SetDefaultInitFields((new-object ($u+'.View')).getType(), "IsSchemaBound");
$v=$s.Databases[$db].Views|Where-Object{$_.IsSchemaBound -eq $true}
if ($v | where $_.Triggers.Count -gt 0) {throw new-object System.ArgumentException 'Views with triggers are not supported'}
$_createScript = ('SET ARITHABORT ON','SET CONCAT_NULL_YIELDS_NULL ON','SET QUOTED_IDENTIFIER ON','SET ANSI_NULLS ON','SET ANSI_PADDING ON','SET ANSI_WARNINGS ON','SET NUMERIC_ROUNDABORT OFF')+
     ($v | %{$i=$_;(@()+'GO'+($i.Script()|where {$_ -notlike "SET * ON"})+'GO'+ ($i.Indexes|sort IsClustered –desc|%{$_.Script()}| where {$_ -notlike "SET *"}))})
$_dropScript =  $v | %{$_.Script(( new-object ($u+'.ScriptingOptions') -prop @{ScriptDrops =$true}))}
Write-Host (@()+'/*'+$_dropScript+'*/'+('-' * 40)+ $_createScript -join $n)
ManageObjectsThatPreventTruncate(@drop=1) -- drop all indexed views and filtered indexes
ManageObjectsThatPreventTruncate() -- crate all indexed views and filtered indexes

ManageObjectsThatPreventTruncate(@drop=1, @schema='x') -- drop all indexed views and filtered indexes from schema X
ManageObjectsThatPreventTruncate(@schema='x') -- crate all indexed views and filtered indexes from schema X

ManageObjectsThatPreventTruncate(@drop=1, @schema='x', @name='y') -- drop concreate indexed view X.Y or drop all filtered indexes from table X.Y
ManageObjectsThatPreventTruncate(@schema='x', @name='y') -- crate concreate indexed view X,Y or drop all filtered indexes from table X.Y

ManageObjectsThatPreventTruncate(@drop=1, @schema='x', @name='y', @indexName='z') -- drop concreate filtered index X.Y
ManageObjectsThatPreventTruncate(@schema='x', @name='y', @indexName='z') -- crate concreate filtered index X.Y
DECLARE @OUTPUT TABLE (id int identity(1,1), line nvarchar(max))
DECLARE @cmd VARCHAR(8000), @psAlterSP VARCHAR(8000),  @psGetDatabase VARCHAR(8000), @psGetViews VARCHAR(8000), @psGetFilteredIndexes VARCHAR(8000), @psLoadAssemblies VARCHAR(8000), @script nvarchar(max) ='',
    @psCompose1 VARCHAR(8000), @psCompose2 VARCHAR(8000), @psCompose3 VARCHAR(8000), @psCompose4 VARCHAR(8000), @psCompose5 VARCHAR(8000), @psComposeAll VARCHAR(8000)
DECLARE @sr nvarchar(max)='<server>', 
        @ln nvarchar(max)='<loginname>', 
        @pw nvarchar(max)='<password>', 
        @db nvarchar(max)=DB_NAME(),
        @sp sysname = 'ManageObjectsThatPreventTruncate',
        @ss sysname = 'dbo'
SET @psLoadAssemblies  = '[System.Reflection.Assembly]::LoadWithPartialName(''Microsoft.SqlServer.SMO'')|Out-Null;$ErrorActionPreference = ''Stop'';'
SET @psGetDatabase='$u =''Microsoft.SqlServer.Management.Smo'';$n = [Environment]::NewLine;$a = ''''+[char]39;$s = new-object ($u+''.Server'') $sr;$c = $s.ConnectionContext;$c.LoginSecure=$false;$c.Login=$ln;$c.Password=$pw;$s.SetDefaultInitFields((new-object ($u+''.View'')).getType(),''IsSchemaBound'');$d = $s.Databases[$db];'
SET @psGetDatabase=REPLACE(@psGetDatabase,'$sr',''''+@sr+'''')
SET @psGetDatabase=REPLACE(@psGetDatabase,'$ln',''''+@ln+'''')
SET @psGetDatabase=REPLACE(@psGetDatabase,'$pw',''''+@pw+'''')
SET @psGetDatabase=REPLACE(@psGetDatabase,'$db',''''+@db+'''')
SET @psGetViews = '$v=$d.Views|where {$_.IsSchemaBound -eq $true};if ($v | where {$_.Triggers.Count -gt 0}) {throw new-object System.ArgumentException ''Views with triggers are not supported''};'
SET @psGetFilteredIndexes = '$r = $d.ExecuteWithResults(''SELECT object_id, Name FROM sys.indexes WHERE has_filter=1 ORDER BY object_id, Name'');$x = @();foreach ($i in $r.Tables[0].Rows) {$x+=$d.Tables.ItemById($i[0]).Indexes[$i[1]]};'
SET @psCompose1='$o1=(''BEGIN TRY'',''IF @drop=1'',''BEGIN'')+($v | %{$_s=$_.Schema;$_n=$_.Name; \"IF (@schema is null or @schema=''$_s'') and (@name is null or @name=''$_n'') DROP VIEW [$_s].[$_n]\"})+($x | %{$_s=$_.Parent.Schema;$_tn=$_.Parent.Name;$_in=$_.Name; \"IF (@schema is null or @schema=''$_s'') and (@name is null or @name=''$_tn'') and (@indexName is null or @indexName=''$_in'') DROP INDEX [$_in] ON [$_s].[$_tn]\"})+''END'';'
SET @psCompose2='$o2=(''ELSE'',''BEGIN'',''SET ARITHABORT ON'',''SET CONCAT_NULL_YIELDS_NULL ON'',''SET QUOTED_IDENTIFIER ON'',''SET ANSI_NULLS ON'',''SET ANSI_PADDING ON'',''SET ANSI_WARNINGS ON'',''SET NUMERIC_ROUNDABORT OFF'',''DECLARE @sql varchar(max)'');'
SET @psCompose3='$o3= ($v | %{$i=$_;$_s=$_.Schema;$_n=$_.Name; (@()+(\"IF (@schema is null or @schema=''$_s'') and (@name is null or @name=''$_n'')\")+''BEGIN''+(''SET @sql =''+$a+(($i.Script()|where {$_ -notlike ''SET * ON''}) -join '''').Replace($a,$a+$a)+$a)  +''exec (@sql)''+( $i.Indexes| sort IsClustered –desc| %{ @()+$n+(''SET @sql =''+$a+(($_.Script()| where {$_ -notlike ''SET *''}) -join '''').Replace($a,$a+$a) +$a) +''exec (@sql)''})+''END'' )});'
SET @psCompose4='$o4= ($x | %{$i=$_;$_s=$_.Parent.Schema;$_tn=$_.Parent.Name;$_in=$_.Name;(@()+(\"IF (@schema is null or @schema=''$_s'') and (@name is null or @name=''$_tn'') and (@indexName is null or @indexName=''$_in'')\")+''BEGIN''+(''SET @sql =''+$a+(($i.Script()|where {$_ -notlike ''SET * ON''}) -join '''').Replace($a,$a+$a)+$a)+''exec (@sql)''+''END'')});'
SET @psCompose5='$o5=(''END'',''END TRY'',''BEGIN CATCH'',''THROW'',''END CATCH'');'
SET @psComposeAll= '$o=($o1+$o2+$o3+$o4+$o5) -join $n;'

SET @psAlterSP = '$p = $d.StoredProcedures[\"$sp\",\"$ss\"]; if($p -eq $null){$p = new-object ($u+''.StoredProcedure'') $d, \"$sp\",\"$ss\";$p.TextHeader=\"CREATE PROCEDURE [$ss].[$sp]$n @drop bit = 0,$n @schema sysname = NULL,$n @name sysname = NULL,$n @indexName sysname=NULL $n AS$n\";$p.TextBody=$o;$p.Create()}else{$p[0].TextBody=$o; $p[0].Alter()}'
SET @psAlterSP=REPLACE(@psAlterSP,'$sp',@sp)
SET @psAlterSP=REPLACE(@psAlterSP,'$ss',@ss)

SET @cmd = 'powershell -Command "'+@psLoadAssemblies+@psGetDatabase+@psGetViews+@psGetFilteredIndexes+@psCompose1+@psCompose2+@psCompose3+@psCompose4++@psCompose5+@psComposeAll+@psAlterSP+'"'
PRINT LEN(@cmd)
DECLARE @result int
INSERT INTO @OUTPUT (line)
exec @result =xp_cmdshell @cmd

IF @result=0 AND (SELECT count(*) FROM @OUTPUT WHERE line is not null)=0
BEGIN
    PRINT 'OK'
END
ELSE
BEGIN
    PRINT 'FAILURE'
    SELECT @script+line FROM @OUTPUT
    WHERE line is not null
    ORDER BY id;
    THROW 50000,'Error during xp_cmdshell execution',1;
END
.ToString() does not raise an exception on double? or long? while it will raise an exception on null string

.ToString() does not raise an exception on double? or long? while it will raise an exception on null string


By : David Gamboa
Date : March 29 2020, 07:55 AM
Hope that helps I have three properties as follow:- , To simplify it:
code :
int? x = null;
string result = x.ToString(); // No exception
return HasValue ? Value.ToString() : "";
object y = x;               // Oh noes, now y is a null reference...
string bang = y.ToString(); // so this throws!
Django: How to raise Http401 and Http403 exceptions like Http404, RAISE EXCEPTION not RESPONSE

Django: How to raise Http401 and Http403 exceptions like Http404, RAISE EXCEPTION not RESPONSE


By : user3412813
Date : March 29 2020, 07:55 AM
I wish this helpful for you I'm trying to make an api using Django, and i'm verifying the request header if it contains an api key and raise an exception according to that, like so: , This is the piece of Django code handling your exceptions:
code :
def response_for_exception(request, exc):
    if isinstance(exc, Http404):
        if settings.DEBUG:
            response = debug.technical_404_response(request, exc)
        else:
            response = get_exception_response(request, get_resolver(get_urlconf()), 404, exc)

    elif isinstance(exc, PermissionDenied):
        logger.warning(
            'Forbidden (Permission denied): %s', request.path,
            extra={'status_code': 403, 'request': request},
        )
        response = get_exception_response(request, get_resolver(get_urlconf()), 403, exc)

    elif isinstance(exc, MultiPartParserError):
        logger.warning(
            'Bad request (Unable to parse request body): %s', request.path,
            extra={'status_code': 400, 'request': request},
        )
        response = get_exception_response(request, get_resolver(get_urlconf()), 400, exc)

    elif isinstance(exc, SuspiciousOperation):
        if isinstance(exc, (RequestDataTooBig, TooManyFieldsSent)):
            # POST data can't be accessed again, otherwise the original
            # exception would be raised.
            request._mark_post_parse_error()

        # The request logger receives events for any problematic request
        # The security logger receives events for all SuspiciousOperations
        security_logger = logging.getLogger('django.security.%s' % exc.__class__.__name__)
        security_logger.error(
            force_text(exc),
            extra={'status_code': 400, 'request': request},
        )
        if settings.DEBUG:
            response = debug.technical_500_response(request, *sys.exc_info(), status_code=400)
        else:
            response = get_exception_response(request, get_resolver(get_urlconf()), 400, exc)

    elif isinstance(exc, SystemExit):
        # Allow sys.exit() to actually exit. See tickets #1023 and #4701
        raise

    else:
        signals.got_request_exception.send(sender=None, request=request)
        response = handle_uncaught_exception(request, get_resolver(get_urlconf()), sys.exc_info())

    # Force a TemplateResponse to be rendered.
    if not getattr(response, 'is_rendered', True) and callable(getattr(response, 'render', None)):
        response = response.render()

    return response
class PermissionDenied(Exception):
    """The user did not have permission to do that"""
    pass
Related Posts Related Posts :
  • How to change datetime format of varchar datatype column
  • Trigger on extended properties? SQL SERVER
  • Database Schema guidance for an app store
  • sql trigger to update based on specific column is updated and if value is equal to 'x'
  • How to descope a local variable in t-sql
  • SQL Server Compact stored procedure with multiple values in 1 input
  • How to validate the ICCID number of a SIM card in TSQL?
  • how to calculate all previous datas sum of a sql column on a date range report
  • How to create view listing all friendship for "friend1,friend2" table in T-SQL and prevent inserting duplicate
  • Sum of multiple column in SQL Server
  • Check month and day lies between two dates
  • How would I configure Effort Testing Tool to mock Entity Framework's DbContext withOut the actual SQL Server Database up
  • While looping through a function, Error: Maximum stored procedure, function, trigger, or view nesting level exceeded (li
  • Collapsing records with adjacent start and end dates
  • SQL Server Management Studio 2008 - Timeout Expired error
  • Insert date from VB to SQL Server
  • How to find what table foreign key is from?
  • Sql Server Nested Inserts possible
  • Deterministic way of getting WeekEnding (Sun) for given date column (for use in computed column)
  • SQL Server, why does one order by query work with a string, but not with an integer?
  • How do I install a SQL Server on the hosted build agent in Visual Studio Online?
  • EXECUTE AS/REVERT within a transaction in SQL Server
  • Distinct 2 field AS one column SQL SERVER
  • how to create a new database after a trigger fired from a table
  • How to sort string alphabetically
  • Checking data in one column in a temp table to a column in another table
  • What's wrong with my IF/ELSE? "ELSE: Incorrect syntax near 'ELSE'."
  • CASE CLAUSE IN WHERE CONDITION
  • TSQL - Creating a running total of a column
  • Why am I getting not null here?
  • EXEC parameters from a SELECT
  • SQL Server Compact connection
  • Star Schema Structure - To many Dimensions
  • sort utf string in sql server with join clause
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org