Inserting the values of a Stored Procedure directly into a Table by using the Column names

indian143

Hi, I am trying to insert values in a table directly from executing a Store Proc, but I want to use the Column names of the resultset of the Stored Procedure and of the table in which the column values are being inserted. My stored Procedure is as below:

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;'

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';'

if (@filepath is not null) or (@filepath <> '')
	set @filepath=@filepath + N';'	

set @tempString=@providerversion+@filepath

--truncate table saw\_raw.\[GL\].\[HpxrStage\]

set
@selectquery=
'select *
,''' + @reportid + '''
from OPENROWSET(''' + @providername + ''',''' +
@tempString+ ''',''' +
N'SELECT * FROM ' + @sheetname + ''')'

--print @selectquery
exec (@selectquery)

END

--truncate table saw_raw.[GL].[HpxrStage]
--exec SAW_raw.[GL].[PopulateStageRpt129_New]

And I calling it as below

Insert into saw\_raw.\[GL\].\[HpxrStage\]
exec
Saw\_Raw.\[GL\].\[PopulateStageRpt129\_New\]

But I want to use as below:

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]

jschell

Error text seems rather obvious to me. Following in google seems to return quite a few suggestions.

TSQL openquery "Could not find server" "sys.servers"

indian143

If that's the case then the below script working fine and giving me the results though.

Insert into xxxxx.[GL].[HpxrStage]
exec
xxxxx.[GL].[PopulateStageRpt129_New]

The only thing is I want to use the Column names in both the Inserting table and for the Stored Procedure, is there anyway to do that? Any help would be greatly helpful. Thanks for the help my friend. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

indian143

Now when I try to use OpenQuery as below, it gives me the error message as below:

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

Error message

The metadata could not be determined because statement 'exec (@selectquery)' in procedure
'PopulateStageRpt129_New' contains dynamic SQL.
Consider using the WITH RESULT SETS clause to explicitly describe the result set.

And when I use the ColumnNames as below in the query

Select * from OPENQUERY([WSQL569S],
'EXEC Saw_Raw.[GL].[PopulateStageRpt129_New] @ReportId=''1-085''
, @filepath=''Database=\\bsc\cscm\IM_PHI\Facets_Team\User.Developed.Reports\HPXR\1-085 Cancelled with Cash-Report 129_2017-10-16.xls''
, @sheetname=''[Sheet1$]''
, @providername=''Microsoft.ACE.OLEDB.12.0''
, @providerversion=''Excel 12.0''
WITH RESULT SETS ([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]
,[ReportId])'
)

It is giving me the error as below:

OLE DB provider "SQLNCLI11" for linked server "WSQL569S" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'Category'.

I want to somehow get the column names of the stored procedures I am trying all the options anything that can help me is going to be very helpful. Thanks in advance my friends. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

Lost User

See sql - Retrieve column names and types of a stored procedure? - Stack Overflow[^], first answer.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)