As a database designer, I often create on database tables the primary key as identity autoincrement and sometimes I needed to find missing or deleted IDs thet interrupt the sequence of autoincrement number on the primary key. To do this you can create a sequence from 1 (or 0 based on setted RESEED) to MAX […]
For different needs, often I had needs to send a list of values to a Stored Procedure. To achieve this goal it is possible to use a workaround with the TSQL function nodes(XQuery) on the types of XML data. We’ll see two examples to better understand the proposed solution. Example 1: How to send […]
When you modify database schema on Sql Server with Microsoft SQL Server Management Studio you can simply create script with Generate Change Script function. This is not allowed when you rename a database object, for example when you rename a table (F2 key) or rename Stored Procedure, but also when you rename a table column […]
If you drop a user that owns a schema, you’ll receive the following error: Drop failed for User ‘my_user’. (Microsoft.SqlServer.Smo) The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138) so, to drop user, you have to find which schemas assigned to, and then transfer the ownership […]
Visual Studio Team Edition for Database Professionals (VSDBPro) consente di gestire la definizione del vostro schema di database di SQL Server allo stesso modo di come gestite i vostri progetti per applicazioni. Si parte dal rappresentare lo schema del database all’interno del progetto di database .dbproj (Figura 1), dopodichè viene creata automaticamente una collezione di […]