Database

alter PROCEDURE [GL].[PopulateStageRpt129_New] (
  @ReportId Varchar(5) = '1-085'
, @filepath nvarchar(max)=N'Database=\bsc\cscm\IM_PHI\Facets_Team\User.Developed.Reports\HPXR\1-085 Cancelled with Cash-Report 129_2017-10-09.xls'
, @sheetname nvarchar(max)=N'[Sheet1$]'
, @providername nvarchar(max)=N'Microsoft.ACE.OLEDB.12.0'
, @providerversion nvarchar(max)=N'Excel 12.0'
) 
AS
BEGIN
    --declare @filepath nvarchar(max)='', @sheetname nvarchar(max)='', @providername nvarchar(max)='', @providerversion nvarchar(max)=''
    --, @tempString nvarchar(max)='', @selectquery nvarchar(max)='';
    --set @filepath=N'Database=\bsc\cscm\IM_PHI\Facets_Team\User.Developed.Reports\HPXR\1-085 Cancelled with Cash-Report 129_2017-10-09.xls;'

<pre>
select @ReportId= REPLACE(@ReportId,';','')
select @filepath= REPLACE(@filepath,';','')
select @sheetname= REPLACE(@sheetname,';','')
select @providername= REPLACE(@providername,';','')
select @providerversion= REPLACE(@providerversion,';','')   

declare @tempString nvarchar(max)='', @selectquery nvarchar(max)=''

if (@sheetname is null) or (@sheetname='')
 set @sheetname=N'[Sheet1$]'

if (@providername is null) or (@providername='')
    set @providername=N'Microsoft.ACE.OLEDB.12.0'

if (@providerversion is null) or (@providerversion='')
    set @providerversion=N'Excel 12.0' + N';'
else
    set @providerversion=@providerversion + N';'

    Insert into saw_raw.[GL].[HpxrStage]
    exec
    Saw_Raw.[GL].[PopulateStageRpt129_New]

    Insert into saw_raw.[GL].[HpxrStage]
        (([Category]
,[Line of Business ID]
,[Line Of Business]
,[Delinquent Definition]
,[Subscriber ID]
,[Last Name]
,[First Name]
,[Group ID]
,[Subgroup ID]
,[Class ID]
,[Paid within Tolerance]
,[Elig Thru Dt]
,[Created End Date]
,[Outstanding Balance]
,[Receipt Payment Type]
,[Date Posted]
,[UNREC Cash]
,[Payment Due Date]
,[PNC Generated]
,[Cancel Reason Code]
)
    exec <Related Column names here or some where>
    Saw_Raw.[GL].[PopulateStageRpt129_New]

Select * from OPENQUERY([WSQL123S,50101], 
    'EXEC xxxx.[GL].[PopulateStageRpt129_New] @ReportId=''1-085''
    , @filepath=''Database=\xxxxx\1-085 Cancelled with Cash-Report 129_2017-10-09.xls''
    , @sheetname=''[Sheet1$]''
    , @providername=''Microsoft.ACE.OLEDB.12.0''
    , @providerversion=''Excel 12.0''')

Could not find server 'WSQL123S,50101' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

USE master 
GO 
sp_configure 'show advanced options', 1 
GO 
RECONFIGURE WITH OverRide 
GO 
sp_configure 'Ad Hoc Distributed Queries', 1 
GO 
RECONFIGURE WITH OverRide 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1 
GO 
EXEC sp_configure 'show advanced options', 1 
GO 
-- To update the currently configured value for advanced options. 
RECONFIGURE 
GO 
-- To enable the feature. 
EXEC sp_configure 'xp_cmdshell', 1 
GO 
-- To update the currently configured value for this feature. 
RECONFIGURE 
GO 

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0;Database=C:\TestFiles\1-085 Cancelled with Cash-Report 129_2017-10-03.xls;', 
'SELECT * FROM [Sheet1$]')

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 32-bit driver is not installed, run the package in 64-bit mode

The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode

DECLARE @Name NVARCHAR(50)
    DECLARE @sqlcmd NVARCHAR(MAX);
    DECLARE @params NVARCHAR(MAX);
    SET @Name ='like ''%ra%''';
    SET @sqlcmd = N'SELECT * FROM [TestDB].[dbo].emp1 WHERE Name '+@Name;
    SET @params = N'@Name NVARCHAR(50)';
    EXECUTE sp_executesql @sqlcmd, @params, @Name;

FileFullName: \\xxxxx\\DataExchange\ShieldLink\Test\32 Bit and 64 Bit Testing\Thirty Two Bit.csv, SSISPackageName: ImportPipeDelimitedCSV.dtsx, Source : Microsoft.SqlServer.Dts.Runtime.TaskHost, ErrorCode : -1071607767, Description : SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Data Conversion.Outputs[Data Conversion Output].Columns[Copy of Column1]" failed because error code 0xC020907F occurred, and the error row disposition on "Data Conversion.Outputs[Data Conversion Output].Columns[Copy of Column1]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
, SubComponent: Data Conversion [2], HelpFiledtsmsg110.rll, HelpContext0, IdofInterfaceWithError{B6F6D221-FC27-4F71-B5A0-597583986C28}

Column1|Column2|Column3|
"1"|"Column 2 Value 1"|"Column 3 Value 1"

FileFullName: \\xxxxx\ShieldLink\Test\32 Bit and 64 Bit Testing\Thirty Two Bit.csv, SSISPackageName: ImportPipeDelimitedCSV.dtsx, Source : Microsoft.SqlServer.Dts.Runtime.TaskHost, WarningCode : -2147183868, Description : Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available.  To resolve, run this package as an administrator, or on the system's console.
, SubComponent: SSIS.Pipeline, HelpFiledtsmsg110.rll, HelpContext0, IdofInterfaceWithError{B6F6D221-FC27-4F71-B5A0-597583986C28}