Click here to Skip to main content
14,879,971 members
Home / Discussions / Database
   

Database

 
QuestionGUID or long for MVC site Pin
#realJSOP17-Oct-17 6:37
mva#realJSOP17-Oct-17 6:37 
AnswerRe: GUID or long for MVC site Pin
Wendelius17-Oct-17 7:01
mveWendelius17-Oct-17 7:01 
AnswerRe: GUID or long for MVC site Pin
jschell17-Oct-17 7:43
Memberjschell17-Oct-17 7:43 
GeneralRe: GUID or long for MVC site Pin
k505418-Oct-17 5:30
mvek505418-Oct-17 5:30 
GeneralRe: GUID or long for MVC site Pin
jschell20-Oct-17 8:34
Memberjschell20-Oct-17 8:34 
AnswerRe: GUID or long for MVC site Pin
Eddy Vluggen17-Oct-17 23:38
professionalEddy Vluggen17-Oct-17 23:38 
AnswerRe: GUID or long for MVC site Pin
Gerry Schmitz18-Oct-17 18:32
mveGerry Schmitz18-Oct-17 18:32 
QuestionInserting the values of a Stored Procedure directly into a Table by using the Column names Pin
indian14313-Oct-17 12:59
Memberindian14313-Oct-17 12:59 
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;'

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

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]
,[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]

When I am trying with the OpenQuery as below its giving me the error:

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


I am getting the following error if I am using the OpeQuery
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.

Anybody can help me please, any help would be very very helpful - thanks in advance.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."
AnswerRe: Inserting the values of a Stored Procedure directly into a Table by using the Column names Pin
jschell15-Oct-17 8:15
Memberjschell15-Oct-17 8:15 
GeneralRe: Inserting the values of a Stored Procedure directly into a Table by using the Column names Pin
indian14315-Oct-17 15:03
Memberindian14315-Oct-17 15:03 
GeneralRe: Inserting the values of a Stored Procedure directly into a Table by using the Column names Pin
indian14316-Oct-17 7:25
Memberindian14316-Oct-17 7:25 
GeneralRe: Inserting the values of a Stored Procedure directly into a Table by using the Column names Pin
Eddy Vluggen17-Oct-17 2:14
professionalEddy Vluggen17-Oct-17 2:14 
QuestionOpenRowSet is giving error: Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". Pin
indian14312-Oct-17 7:38
Memberindian14312-Oct-17 7:38 
AnswerRe: OpenRowSet is giving error: Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". Pin
Victor Nijegorodov12-Oct-17 9:00
MemberVictor Nijegorodov12-Oct-17 9:00 
GeneralRe: OpenRowSet is giving error: Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". Pin
indian14312-Oct-17 11:30
Memberindian14312-Oct-17 11:30 
GeneralRe: OpenRowSet is giving error: Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". Pin
Richard MacCutchan12-Oct-17 22:11
mveRichard MacCutchan12-Oct-17 22:11 
AnswerRe: OpenRowSet is giving error: Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". Pin
Richard Deeming13-Oct-17 2:10
mveRichard Deeming13-Oct-17 2:10 
QuestionSSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0209302 Pin
indian14311-Oct-17 7:36
Memberindian14311-Oct-17 7:36 
QuestionSQL Server store procedure and prevent sql injection Pin
Mou_kol6-Oct-17 8:45
MemberMou_kol6-Oct-17 8:45 
AnswerRe: SQL Server store procedure and prevent sql injection Pin
Richard Deeming6-Oct-17 9:27
mveRichard Deeming6-Oct-17 9:27 
GeneralRe: SQL Server store procedure and prevent sql injection Pin
Mou_kol6-Oct-17 11:30
MemberMou_kol6-Oct-17 11:30 
GeneralRe: SQL Server store procedure and prevent sql injection Pin
Eddy Vluggen7-Oct-17 2:22
professionalEddy Vluggen7-Oct-17 2:22 
GeneralRe: SQL Server store procedure and prevent sql injection Pin
Richard Deeming9-Oct-17 8:59
mveRichard Deeming9-Oct-17 8:59 
GeneralRe: SQL Server store procedure and prevent sql injection Pin
MadMyche22-Nov-17 4:13
mveMadMyche22-Nov-17 4:13 
QuestionImporting the integer values that are coming within " Pin
indian1435-Oct-17 14:14
Memberindian1435-Oct-17 14:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.