Store proc called in derived Table giving error for @Prompt

building semantic layer
1

I am trying to call store procedure in the derived table following code works perfectly fine in derived table.


select * from OPENROWSET('SQLOLEDB','Server=ent-sql-dwd;uid=user1;pwd=user1', 
'set FMTONLY OFF;set NOCOUNT ON; EXEC[gtdw].dbo.sp_ClientRankingFirmReport 200901,200901,ad ')

when i try to use prompt instaed of constant value i get error ’ incorrect syntax near StingValue’ any idea?


select * from OPENROWSET('SQLOLEDB','Server=ent-sql-dwd;uid=crguser;pwd=crgu$3r', 
'set FMTONLY OFF;set NOCOUNT ON; EXEC[gtdw].dbo.sp_ClientRankingFirmReport @Prompt('prompt','A',,mono,free) ,  @Prompt('prompt','A',,mono,free), @Prompt('prompt','A',,mono,free) ')

dbo.sp_ClientRankingFirmReport procedure requires 3 Varchar parameters.

Thanks

Baapu (BOB member since 2008-05-16)

2

Really? Because in your first example, there are not strings but numbers as the input parameters of the procedure.

Try changing the 2nd parameter of the @prompt() function from ‘A’ to ‘N’ and see if that helps.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

Thanks Marek,
i tried changing ‘A’ to ‘N’ still same error.

Baapu (BOB member since 2008-05-16)

4

Then I also think that you’re not using the procedure correctly. Your syntax is

procedure_name parameter1, parameter2

Should it not be like this?

procedure_name (parameter1, parameter2)

It means, with the brackets :?: You are missing them in your syntax.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

5

Just an aside, but I’ve always found using database functions tidier. :slight_smile:

6

Marek!! actually that worked before i just changes ‘A’ to ‘N’ and it didn’t worked then i changed the prompt (1st parameter) and this works fine now. Thanks


select * from OPENROWSET('SQLOLEDB','Server=ent-sql-dwd;uid=crguser;pwd=crgu$3r', 
'set FMTONLY OFF;set NOCOUNT ON; EXEC[gtdw].dbo.sp_ClientRankingFirmReport @prompt ('Start Period','N',,mono,free), @prompt ('End period','N',,mono,free) ,ad')

one more thing it works fine in Deski but in Webi i get error
‘The operation could not be performed because the OLE DB provider ‘SQLOLEDB’ was unable to begin a distributed transaction.’

any clue on this?

Baapu (BOB member since 2008-05-16)