Noel Pulis

Category: Microsoft SQL

  • How to connect to a MariaDB via the SQL Management Studio SSMS

    How to connect to a MariaDB via the SQL Management Studio SSMS

    From the SQL Management Studio (SSMS) there is a way to connect/create a linked server to a MariaDB. For this you would need the following.

    – Install the ODBC Connector for MariaDB
    – Create an ODBC Connection (System DSN)
    – Add Linked Server.

    Installing the ODBC libraries for MariaDB

    Browse to https://mariadb.com/downloads/connectors/connectors-data-access/odbc-connector/
    Download the required ODBC installer for Windows.

    Install the downloaded “mariadb-connector-odbc-3.2.3-win64.msi” file

    Creation of the ODBC connection

    Open the ODBC Data Source Administrator (64-bit)
    Click on System DSN
    Click on Add
    Select MariaDB ODBC 3.2 Driver
    Give the connection a name
    Enter the server details and click on Test DSN to confirm the connectivity.

    Setting up the connection via the SSMS

    Open the SQL Management Studio
    Connect to the SQL instance
    Click on Server Objects and right-click on Linked Servers
    Click on New Linked Server

    Enter a name in the linked server section
    Change the Provider to Microsoft OLE DB Provider for ODBC Drivers
    Enter the ODBC Connection name you created in the Data Source section
    Click on Security
    Click on Be made using this security context and enter the credentials of the MariaDB server

    Click OK

    This will create the connection and you can browse the data.

  • SQL: Attach a database using a different database name

    SQL: Attach a database using a different database name

    When attaching a database in SQL Server using the interface, it doesn’t let you change the name of the destination database. This can be done using the SQL Commands below.

    USE [master]
    GO
    CREATE DATABASE [myNewSite_db] ON
    ( FILENAME = N'D:\Data\myNewSite_db.mdf' ),
    ( FILENAME = N'E:\Logs\myNewSite_db_log.ldf' )
    FOR ATTACH
    GO

  • SQL How to empty all tables in a database

    SQL How to empty all tables in a database

    You might need to purge all the data in all the tables in a particular database. For this we can use the following script.

    USE <database name>
    DECLARE @TableName AS VARCHAR(MAX)
    DECLARE table_cursor CURSOR
    FOR
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'
    AND TABLE_NAME LIKE '%_Partition%'
    OPEN table_cursor
    FETCH NEXT FROM table_cursor INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    DECLARE @SQLText AS NVARCHAR(4000)
    SET @SQLText = 'TRUNCATE TABLE ' + @TableName
    EXEC sp_executeSQL @SQLText
    FETCH NEXT FROM table_cursor INTO @TableName
    END
    CLOSE table_cursor
    DEALLOCATE table_cursor