Thursday, May 30, 2019

SQL Server export/import database diagrams

Database diagrams are saved in sysdiagrams system table, hence they cannot be exported or imported directly. However the following steps are one of the alternatives to export and then import them, provided none of the tables involved the diagrams have changed in any way:

These steps assume that name of the database from which we want to export diagrams is 'MyDB' located at 'MyServer' sql instance

  1. Export sysdiagrams table to a temporary sql server database
    • Create a temporary database on the same sql server instance, say 'TempDB'
    • Right click on 'MyDB'
    • Select Tasks -> Export Data
    • In the export data wizard input following values:
      • Choose a Data Source
        • Data Source: SQL Server Native Client
        • Server Name: MyServer
        • Authentication: Use applicable details
        • Database: MyDB
      • Choose a Destination
        • Data Source: SQL Server Native Client
        • Server Name: MyServer
        • Authentication: Use applicable details
        • Database: TempDB
      • Specify Table copy or query: Write a query
      • Provide a source query: select * from sysdiagrams 
      • Map the query to a table name at destination such as 'TempTable'
      • Run the query.
    • The above steps copy data from sysdiagrams table in to a temporary database. While importing them back, use Generate scripts option to generate insert statements from the TempTable to a sql query. 
    • Use these insert statements to import back the diagrams.

SQL Server Error: Could not obtain information about Windows NT group/user

I got this error while trying to create a new database diagram after restoring it from backup. The error indicates that current login does not have authorization necessary to get information about the database owner. This can be easily solved by executing following script

USE MyDB 
GO 
ALTER DATABASE MyDB set TRUSTWORTHY ON; 
GO 
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false 
GO 
sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE; 
GO 
sp_configure 'clr enabled', 1; 
GO 
RECONFIGURE; 
GO


Copied from: https://stackoverflow.com/questions/25845836/could-not-obtain-information-about-windows-nt-group-user

c# httpclient The remote certificate is invalid according to the validation procedure: RemoteCertificateNameMismatch

 If we get this error while trying to get http reponse using HttpClient object, it could mean that certificate validation fails for the remo...