execute strored procedure through OPENROWSET-SYNTAX ERROR

Question

execute strored procedure through OPENROWSET-SYNTAX ERROR

george.sofroniadis

Old Hand

Points: 351
More actions
July 9, 2018 at 7:12 am

#414336

declare @Tmp as varchar(25)
set @Tmp ='ΠΑΠΑΓΟΥ'
SELECT
*
INTO
#tmpSortedBooks
FROM
OPENROWSET(
'SQLNCLI',
'Server=MNG01\MPISDARIS;Trusted_Connection=yes;Database=Reporting;',
'EXEC dbo.papagos1 '@tmp)
I WOULD LIKE TO EXECUTE A STORED PROCEDURE which has a dynamic parameter ? which is the correct syntax to run this code..?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 1

george.sofroniadis - Monday, July 9, 2018 7:12 AM
declare @Tmp as varchar(25)
set @Tmp ='Î Î‘Î Î‘Î“ÎŸÎ¥'
SELECT
*
INTO
#tmpSortedBooks
FROM
OPENROWSET(
'SQLNCLI',
'Server=MNG01\MPISDARIS;Trusted_Connection=yes;Database=Reporting;',
'EXEC dbo.papagos1 '@tmp)
I WOULD LIKE TO EXECUTE A STORED PROCEDURE which has a dynamic parameter ? which is the correct syntax to run this code..?

OPENROWSET cannot take parameters. You'll have to materialize the command using dynamic SQL observing, of course, the proper methods to avoid SQL Injection.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nicolas.worth Old Hand Points: 311 More actions · Answer 2

Hi George,
One way of achieving this would be to create and define your temp table first and the insert into from the proc, for example -

declare @Tmp as varchar(25)
set @Tmp ='????G??'

CREATE TABLE #tmpSortedBooks
(
ColumnA <DATATYPE>
,ColumnB <DATATYPE>
,ColumnC <DATATYPE>
)
INSERT INTO #tmpSortedBooks
EXEC [Reporting].dbo.papagos1 @Tmp

SELECT *
FROM #tmpSortedBooks