SQL Server

  • SQL Server: how to move tempdb database

    /

    SQL Server doesn’t support moving TempDB Database using backup/restore or by using detach database methods. In this article I explain the steps you must follow to move TempDB database from one drive to another in SQL Server. However, for the changes to come into effect you must restart SQL Server Service. Start SQL Server Management Studio and execute the below query: USE master; GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = ‘C:\yourpath\tempdb.mdf’); GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = ‘T:\yourpath\templog.ldf’); GO Remember to replace yourpath with your preferred path. Once query is executed, SQL Server give you a message to remember you to Stop…

    read more

  • SQL Server drop user failed: “The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)”

    /

    If you try to delete a user who owns a schema, you will 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) Therefore, to delete the user, you have to find the schema to which it owns, and transfer the ownership to another user (or role). Use this query to find the schema the user belongs:   SELECT sc.name FROM sys.schemas sc WHERE sc.principal_id = USER_ID('my_user') Once you find the schema with the previous query (eg “db_datareader”), use it to transfer the ownership with this snippet:   ALTER AUTHORIZATION ON SCHEMA::db_datareader…

    read more

  • How to reduce multiple rows in a single row and column with SQL Server

    /

    In this sample we want to roll up different rows of a table in a single column, with every row value separated by a comma. I have a simple table like this: and I want to obtain a table with a single row for each continent, with a column thath list all state of the continent. This is the result i want to obtain: First concatenate all state in a string with FOR XML command: SELECT sa.Des_Nazione [Nazioni] FROM AreaStato as sa ORDER BY Des_Continente,Des_Nazione FOR XML PATH('') Then add a comma between each state: SELECT ', ' + sa.Des_Nazione FROM AreaStato as sa ORDER BY Des_Continente,Des_Nazione FOR XML PATH('')…

    read more

  • SQL Server: add a linked server to Oracle Database

    /

    A linked server allows us to read, from the instance we are using SQL Server, tables stored on a different instance or even on a different DBMS. To do this you must use OLEDB (Object Linking and Embedding Database), API developed by Microsoft that allow access uniformly to a wide variety of data sources.

    read more