Working with external data in SQL Server

usp_adm_CreateLinkView.sql

IF OBJECT_ID('usp_adm_CreateLinkView') IS NOT NULL DROP PROC usp_adm_CreateLinkView
GO

CREATE PROC usp_adm_CreateLinkView (
            @ConnStrOrFile  VARCHAR(1000)
            , @TableName    sysname         = NULL
            , @ViewName     sysname         = NULL
            , @Flags        INT             = NULL -- 1 - CSV header, 2 - CSV unicode
) AS
SET         NOCOUNT ON

CREATE TABLE #TmpTables (
            SeqNo               INT             IDENTITY(1, 1)
            , TABLE_CAT         sysname         COLLATE DATABASE_DEFAULT NULL -- note: sysname e notnullable data-type, zatowa tuk explicitno e NULL-a
            , TABLE_SCHEM       sysname         COLLATE DATABASE_DEFAULT NULL
            , TABLE_NAME        sysname         COLLATE DATABASE_DEFAULT NULL
            , TABLE_TYPE        VARCHAR(32)     COLLATE DATABASE_DEFAULT
            , REMARKS           VARCHAR(254)    COLLATE DATABASE_DEFAULT
            )
CREATE TABLE #TmpOLEDBProv (
            Name                sysname         COLLATE DATABASE_DEFAULT
            , ParseName         sysname         COLLATE DATABASE_DEFAULT
            , Description       sysname         COLLATE DATABASE_DEFAULT
            )
            
DECLARE     @SQL            VARCHAR(8000)
            , @DbName       sysname
            , @RscName      VARCHAR(50)
            , @Type         INT -- 1 - SQLOLEDB, 2 - Excel, 3 - CSV
            , @ProvStr      VARCHAR(1000)
            , @OledbProvider sysname
            , @TypeGuessKey sysname
            , @Cmd          VARCHAR(8000)

--- parse db name
IF          @ConnStrOrFile LIKE '%.csv'
BEGIN
            SET         @Type = 3
            
            INSERT      #TmpOLEDBProv
            EXEC        master.dbo.xp_enum_oledb_providers

            --SELECT        @OledbProvider = Name
            --FROM      #TmpOLEDBProv
            --WHERE     Name = 'Microsoft.Jet.OLEDB.4.0'
            SELECT      TOP 1 @OledbProvider = Name
            FROM        #TmpOLEDBProv
            ORDER BY    CASE    WHEN Name = 'Microsoft.ACE.OLEDB.12.0' THEN 1
                                WHEN Name = 'Microsoft.Jet.OLEDB.4.0' THEN 2
                                ELSE 3
                        END

            IF          @OledbProvider IS NULL
            BEGIN
                        RAISERROR   ('Dreem: Липсва инсталиран OLEDB Provider за достъп до CSV', 16, 1)
                        GOTO QH
            END

            SELECT      @TableName = SUBSTRING(@ConnStrOrFile, LEN(@ConnStrOrFile) - CHARINDEX('\', REVERSE(@ConnStrOrFile)) + 2, 1000)
                        , @ConnStrOrFile = LEFT(@ConnStrOrFile, LEN(@ConnStrOrFile) - LEN(@TableName) - 1)

            IF          IS_SRVROLEMEMBER(N'setupadmin') = 1
                        AND @@MICROSOFTVERSION / POWER(2, 24) > 8
            BEGIN
                        EXEC        ('
                        IF NOT EXISTS ( SELECT  *
                                        FROM    sys.configurations
                                        WHERE   configuration_id = 16390 -- xp_cmdshell
                                                AND value_in_use = 1    ) 
                        BEGIN
                                    EXEC        master.dbo.sp_configure ''show advanced options'', 1
                                    RECONFIGURE WITH OVERRIDE
                                    EXEC        master.dbo.sp_configure ''xp_cmdshell'', 1
                                    RECONFIGURE WITH OVERRIDE
                        END')
            END

            SET         @Cmd = 'echo [' + @TableName + '] > "' + @ConnStrOrFile + '\schema.ini"'
                            + ' && echo Format=Delimited(,) >> "' + @ConnStrOrFile + '\schema.ini"'
                            + ' && echo ColNameHeader=' + CASE WHEN (@Flags & 1) <> 0 THEN 'True' ELSE 'False' END + '>> "' + @ConnStrOrFile + '\schema.ini"'
                            + ' && echo MaxScanRows=0 >> "' + @ConnStrOrFile + '\schema.ini"'
                            + CASE WHEN (@Flags & 2) <> 0 THEN ' && echo CharacterSet=Unicode >> "' + @ConnStrOrFile + '\schema.ini"' ELSE '' END
            EXEC        master.dbo.xp_cmdshell @Cmd, NO_OUTPUT

            SET         @Cmd = 'type "' + @ConnStrOrFile + '\' + @TableName + '.ini" >> "' + @ConnStrOrFile + '\schema.ini"'
            EXEC        master.dbo.xp_cmdshell @Cmd, NO_OUTPUT
END
ELSE IF     @ConnStrOrFile LIKE '%.xls' OR @ConnStrOrFile LIKE '%.xls_'
BEGIN
            SET         @Type = 2
            
            INSERT      #TmpOLEDBProv
            EXEC        master.dbo.xp_enum_oledb_providers
            
            SELECT      TOP 1 @OledbProvider = Name
            FROM        #TmpOLEDBProv
            ORDER BY    CASE    WHEN Name = 'Microsoft.ACE.OLEDB.12.0' THEN 1
                                WHEN Name = 'Microsoft.Jet.OLEDB.4.0' THEN 2
                                ELSE 3
                        END

            IF          @OledbProvider IS NULL
            BEGIN
                        RAISERROR   ('Dreem: Липсва инсталиран OLEDB Provider за достъп до Excel', 16, 1)
                        GOTO QH
            END
END
ELSE BEGIN
            SET         @Type = 1

            SELECT      @DbName = SUBSTRING(s.Code, LEN('Database=%'), LEN(Code))
            FROM        dbo.fn_sys_Split(@ConnStrOrFile, ';') s
            WHERE       s.Code LIKE 'Database=%'

            IF          @DbName IS NULL
            BEGIN
                        RAISERROR ('Dreem: Невалиден параметър @ConnStrOrFile. Липсва указана "Database" в параметъра.', 16, 1)
                        GOTO QH
            END
END

IF          @Type IN (2, 3)
BEGIN
            IF          IS_SRVROLEMEMBER(N'setupadmin') = 1
                        AND @@MICROSOFTVERSION / POWER(2, 24) > 8 --- above sql2000
            BEGIN
                        EXEC        ('EXEC master.dbo.sp_MSset_oledb_prop N''' + @OledbProvider + ''', N''AllowInProcess'', 1')
                        
                        EXEC        ('
                        IF NOT EXISTS ( SELECT  *
                                        FROM    sys.configurations
                                        WHERE   configuration_id = 16391 -- Ad Hoc Distributed Queries
                                                AND value_in_use = 1    ) 
                        BEGIN
                                    EXEC        master.dbo.sp_configure ''show advanced options'', 1
                                    RECONFIGURE WITH OVERRIDE
                                    EXEC        master.dbo.sp_configure ''Ad Hoc Distributed Queries'', 1
                                    RECONFIGURE WITH OVERRIDE
                        END')
            END

            --- for IMEX=1 mode to scan full column (not first 8 rows only)
            SELECT      @TypeGuessKey = CASE WHEN @OledbProvider = 'Microsoft.Jet.OLEDB.4.0' THEN 'SOFTWARE\Microsoft\Jet\4.0\Engines\Excel'
                                            WHEN @OledbProvider = 'Microsoft.ACE.OLEDB.12.0' THEN 'SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel'
                                        END
                                            
            IF          @TypeGuessKey IS NOT NULL
            BEGIN
                        EXEC        master..xp_regwrite 'HKEY_LOCAL_MACHINE', @TypeGuessKey, 'TypeGuessRows', 'REG_DWORD', 0
            END         
END

--- figure out first excel sheet
IF          @Type = 2 AND @TableName IS NULL
BEGIN
            SET         @ProvStr = 'Excel 8.0;Database=' + @ConnStrOrFile

            IF EXISTS ( SELECT * FROM master..sysservers WHERE srvname = '__usp_adm_CreateLinkView_excel__' )
                        EXEC        dbo.sp_dropserver '__usp_adm_CreateLinkView_excel__', 'droplogins'

            EXEC        dbo.sp_addlinkedserver @server = '__usp_adm_CreateLinkView_excel__'
                            , @srvproduct = 'excel'
                            , @provider = @OledbProvider
                            , @provstr = @ProvStr

            EXEC        dbo.sp_addlinkedsrvlogin @rmtsrvname = '__usp_adm_CreateLinkView_excel__'
                            , @useself = 'false'
                            , @rmtuser = 'Admin'

            INSERT      #TmpTables(TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS)
            EXEC        master.dbo.sp_tables_ex '__usp_adm_CreateLinkView_excel__'

            IF          @ViewName IS NULL
            BEGIN
                        SELECT      *
                        FROM        #TmpTables

                        RETURN
            END
            
            --- predpochitame sheet-owete (zawyrshwat na $)
            SELECT      TOP 1 @TableName = TABLE_NAME
            FROM        #TmpTables
            ORDER BY    CASE WHEN TABLE_NAME LIKE '%$' THEN 0 ELSE 1 END
                        , SeqNo

            EXEC        dbo.sp_dropserver '__usp_adm_CreateLinkView_excel__', 'droplogins'
            
            IF          @TableName IS NULL
            BEGIN
                        RAISERROR ('Dreem: Невалиден параметър @ConnStrOrFile. Невъзможно получаване на налични листа на документа', 16, 1)
                        GOTO QH
            END
END

--- acquire application lock on the view
SET         @RscName = CONVERT(VARCHAR(50), CHECKSUM(@ViewName))
EXEC        dbo.sp_getapplock @Resource = @RscName
                , @LockMode = 'Exclusive'
                , @LockOwner = 'Session'

--- d-sql: drop view
SET         @SQL = '
USE         tempdb
IF          OBJECT_ID(''tempdb..' + QUOTENAME(@ViewName) + ''') IS NOT NULL
            DROP VIEW   dbo.' + QUOTENAME(@ViewName)
-- PRINT    @SQL
EXEC        (@SQL)

--- construct create view
SET         @SQL = '
CREATE VIEW dbo.' + QUOTENAME(@ViewName) +  '
AS
/*------------------------------------------------------------------------

    Linking view to ' + CASE WHEN @Type = 1 THEN @DbName + '.dbo.' + @TableName ELSE @ConnStrOrFile END + '

    Modifications: 
      ' + CONVERT(VARCHAR(50), GETDATE(), 112) + '  SYS     Automatically generated

*/------------------------------------------------------------------------
' + CASE WHEN @Type = 1 THEN '
SELECT      *
FROM        OPENROWSET(''SQLOLEDB''
                , ''' + REPLACE(@ConnStrOrFile, '''', '''''') + '''
                , ''SELECT * FROM ' + QUOTENAME(@DbName) + '.dbo.' + QUOTENAME(@TableName) + ''')' WHEN @Type = 2 THEN '
SELECT      *
FROM        OPENROWSET(''' + @OledbProvider + '''
                , ''Excel 8.0;IMEX=1;HDR=' + CASE WHEN @Flags = 1 THEN 'YES' ELSE 'NO' END + ';Database=' + @ConnStrOrFile + '''
                , ' + QUOTENAME(@TableName) + ')' WHEN @Type = 3 THEN '
SELECT      *
FROM        OPENROWSET(''' + @OledbProvider + '''
                , ''Text;Database=' + @ConnStrOrFile + '''
                , ''SELECT * FROM ' + QUOTENAME(@TableName) + ''')' END
-- PRINT        @SQL

--- d-sql: use tempdb; exec ('create view')
SET         @SQL = '
USE         tempdb
EXEC        (''' + REPLACE(@SQL, '''', '''''') + ''')'
-- PRINT        @SQL
EXEC        (@SQL)
QH:
GO

usp_adm_DropLinkView.sql

IF OBJECT_ID('usp_adm_DropLinkView') IS NOT NULL DROP PROC usp_adm_DropLinkView
GO

CREATE PROC usp_adm_DropLinkView (
            @ViewName       sysname
) AS
SET         NOCOUNT ON

DECLARE     @SQL        VARCHAR(8000)
            , @RscName  VARCHAR(50)

SET         @RscName = CONVERT(VARCHAR(50), CHECKSUM(@ViewName))

--- release application lock on the view
IF EXISTS ( SELECT      *
            FROM        master.dbo.syslockinfo l
            JOIN        master.dbo.spt_values v
            ON          l.rsc_type = v.number 
                        and v.type = 'LR'
            WHERE       substring (v.name, 1, 4) = 'APP'
                        AND req_spid = @@SPID
                        AND LEFT(rsc_text, 4) = LEFT(@RscName, 4))
BEGIN
--          PRINT       'sp_releaseapplock ' + @RscName
            EXEC        sp_releaseapplock  @Resource = @RscName
                            , @LockOwner = 'Session'
END

--- d-sql: drop view
SET         @SQL = '
USE         tempdb
IF          OBJECT_ID(''tempdb..' + QUOTENAME(@ViewName) + ''') IS NOT NULL
            DROP VIEW   dbo.' + QUOTENAME(@ViewName)
-- PRINT    @SQL
EXEC        (@SQL)
GO