Great news to all Stored Procedures fans! now in Universe!

system · March 11, 2009, 1:39pm

and now, ladies and gentlemen, another great news!
you can use stored procedures in the universe in XI R2! (of course with dynamic SQL, temp tables and other powerfull sql features)
how to proceed:
just create derived table in the universe with the following SQL:

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes','EXEC sp_myReport')

(oops! forgot to tell you it will work only with MS SQL 2000 or higher, unless your DBMS has something similar to OPENROWSET)
that’s it, guys! now you can use it like a normal table including joining it to other tables or stored procedures

I believe, this time there are no tricks - both derived tables and OPENROWSET are supported and well documented.

just a few further guidlines:

if you get connection error - try to change provider to OLEDB, or Trusted_Connection to uid=login;pwd=P@$$w0rd, or localhost to your server/instance name
if you get error ‘No column or data to fetch’ change 2nd parameter of OPENROWSET to following:

'SET FMTONLY OFF;EXEC sp_myReport'

there are other ways to fix it, but it’s just enough to start with
3. last and the most important: to use prompts change the last parameter of OPENROWSET to following:

'EXEC sp_myReport '@Prompt('prompt','A',,mono,free)''

Well, if you say XI 3.0 supports SPs natively, yes, but you can’t join it to other table or stored procedure and use them together in a single query, so, even in XI 3.0 I’ll stay with my approach.

Enjoy!

Dmitry Biryukov (BOB member since 2006-10-15)

system · April 8, 2009, 10:14am

Hi Dmitry,

It sounds like your post is the answer to my migration prayers, but I am having trouble in getting it working on my side, it’s down to my lack of understanding…

could you please point me in the right direction.

from your above syntax you have entered ‘SQLNCLI’ as the provider name for the OPENROWSET command.

i’m having trouble finding out what my provider name should be.

Business Objects XI3.1 (fp 1.2) connecting to MS SQL2000 datasource, using a standard ODBC secured connection in the universe, i tried using an OLE DB (MS SQL Server 2000) connection also.

any help would be appreciated.

cheers,

Matt

mds (BOB member since 2006-05-17)

system · April 8, 2009, 1:10pm

it’s 2009 now migrate to 2008 or at least 2005
for 2000 you can use SQLOLEDB provider
and first try OPENROWSET in Query Analyzer, then in BO to separate sql-related issues and BO-related

Dmitry Biryukov (BOB member since 2006-10-15)

system · April 8, 2009, 1:31pm

Hey Dmitry,

many thanks for your reply. I’ll do as you suggested, then i’ll bother our dba’s as they should have a good idea.

Have a good day !!!

mds (BOB member since 2006-05-17)

system · April 8, 2009, 6:12pm

Dmitry,

thank you for your contribution on BOB. This is great stuff.

Like MDS, I too am using Windows Server 2003 for the Web Server platform, and on another server running Windows Server 2003 and SQL Server 2000 (I know, this is 2009), but the powers to be haven’t seen fit to “modernize”. Anyhow, after implementing your procedure and using “SQLOLEDB” for the provider, I get the message saying that DTC is not enabled (Distributed Transaction Coordinator), which is true, and due to the Network Security guys have it turned off (always to them, less is more, thus more secure). Instead of having to initialize and turn on DTC, are there any alterntatives? This country bumpkin sees ODBC supporting the machnery quite well, so why not being able to use ODBC versus OLE? Or, for some of our ASP stuff we’re using ADODB.Connection, which I tried but received the following error:

Thanks,
John

jsanzone (BOB member since 2006-09-12)

system · April 9, 2009, 9:27am

sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

Dmitry Biryukov (BOB member since 2006-10-15)

system · April 9, 2009, 12:33pm

FYI: ‘Ad Hoc Distributed Queries’ is a SQL Serve 2005 & 2008 feature

jsanzone (BOB member since 2006-09-12)

system · April 13, 2009, 6:17pm

In BO XI R2, could anyone try calling a stored procedure when the DB is oracle? If so, request you to post the details. It would be of great help.

Thanks for your help!

bobuser9 (BOB member since 2007-04-20)

system · July 1, 2009, 9:48pm

Hello,

Can any one help me please!!.

I am trying to pull stored procedure into universe.

Using XI, SQL Server 2005.

Here are the expressions :

DECLARE @RC int
DECLARE @Date smalldatetime
DECLARE @Code int

– TODO: Set parameter values here.
set @Date=‘7/01/09’
set @Code=3

EXECUTE @RC = [dd_01].[dbo].[storeproc001]
@Date
,@Code
–This retrieved data successfully.

But below expression is not:

select * from OPENROWSET(‘SQLOLEDB’,‘Server=TESTSERVER;uid=user1;pwd=MNBVC’,
‘EXEC [dd_01].dbo.storedproc001 ‘‘07/01/09’’,3’)

and the ERROR:

Exception: DBD, [Microsoft OLE DB Provider for SQL Server] : Cannot process the object “EXEC [dd_01].dbo.storedproc001 ‘07/01/09’,3”. The OLE DB provider “SQLNCLI” for linked server “(null)” indicates that either the object has no columns or the current user does not have permissions on that object.State: 42000.

1.Stored procedure has two parameters-date and numeric in it,
2.I tried ‘set FMTONLY Off’ --> OPENROWET(…’,'set FMTONLY oFF;EXEC [dd_01]…) and it gave me same above error,
3.uid=user1 is a sql user and has full access,

Maybe it doesnt effect anything but there is no native client setup on the server.

Any help would be appreciated.

Thanks,
Chals

dxchalasani (BOB member since 2009-07-01)

system · July 2, 2009, 2:42pm

Hello Dmitry,

Can you help me finding the solution!?

I am trying to pull SP into universe.

Please find complte error description in this post below.

Thanks in advance.

Chals

dxchalasani (BOB member since 2009-07-01)

system · July 2, 2009, 2:49pm

Hi Chals,

Please let me remind you about this rule from BOB rules and guidelines:
https://bobj-board.org/tos#heading--bumping

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

system · July 2, 2009, 2:58pm

Parden me

dxchalasani (BOB member since 2009-07-01)

system · July 6, 2009, 2:19pm

Chals,

I’m not an expert on Dimitry’s method, and because I’m running SQL Server 2000 (not my choice) I can’t get this stuff to work, but poring over your code, I’m wondering if you’ve tried to submit the stored procedure in the conventional way as follows (this is the way I pass it using ASP code at least):

select * from OPENROWSET('SQLOLEDB','Server=TESTSERVER;uid=user1;pwd=MNBVC', 
'EXEC [dd_01].dbo.storedproc001 @date='07/01/09',@Code=3)

Just a shot in the dark and not sure but I thought it was worth mentioning.

Have a good one!
Thanks,
John

jsanzone (BOB member since 2006-09-12)

system · July 6, 2009, 2:43pm

Hello John,

Thanks for your reply

I tried that way, it’s throwing exactly the same error.

Here it is:
Exception: DBD, [Microsoft OLE DB Provider for SQL Server] : Cannot process the object "EXEC [dd_01].dbo.storedproc001 @date=‘9/11/2008’,@Code=1’'. The OLE DB provider “SQLNCLI” for linked server “(null)” indicates that either the object has no columns or the current user does not have permissions on that object.State: 42000.

Chals

dxchalasani (BOB member since 2009-07-01)

system · July 6, 2009, 5:01pm

Chals,
The portion of the response

looks suspect. Do you have (or at one time had) a link to another SQL server? Was a link turned on by accident at sometime in the past, but no longer needed? You are using SQLOLEDB however, SQLNCLI is jumping into the mix for some reason. Maybe you can research this more on google

. I’m quickly getting out of my league on this topic…
Thanks,
John

jsanzone (BOB member since 2006-09-12)

system · July 7, 2009, 4:13pm

My error got solved.

Few SP functions didnt have right privileges

dxchalasani (BOB member since 2009-07-01)

system · July 7, 2009, 11:27pm

But new problem when i try to pass parameters using @prompt.

This is working fine

select * from OPENROWSET('SQLOLEDB','Server=TESTSERVER;uid=user1;pwd=MNBVC', 
'set FMTONLY OFF;set NOCOUNT ON;EXEC [dd_01].dbo.storedproc001 ''9/11/08'',1')

But when i try to use with @prompt

select * from OPENROWSET('SQLOLEDB','Server=TESTSERVER;uid=user1;pwd=MNBVC', 
'set FMTONLY OFF;set NOCOUNT ON;EXEC [dd_01].dbo.storedproc001 '@prompt('Date:','D'',,mono,free)','@prompt('Number:','A',,mono,free)'')

a bigg NOO, its not working

Any help would be appreciated!!

Thanks,
Chals [/code]

dxchalasani (BOB member since 2009-07-01)

system · July 7, 2009, 11:35pm

By the way i am using Designer 11.5.0.0, sql server 2005

Thanks,
Chals

dxchalasani (BOB member since 2009-07-01)

system · July 8, 2009, 1:20am

with prompt code :

select * from OPENROWSET('SQLOLEDB','Server=TESTSERVER;uid=user1;pwd=MNBVC', 
'set FMTONLY OFF;set NOCOUNT ON;EXEC [dd_01].dbo.storedproc001 '@prompt('Date:','D',,mono,free)','@prompt('Number:','A',,mono,free)'')

dxchalasani (BOB member since 2009-07-01)

system · October 16, 2009, 3:03pm

Is it possible to use Stored Procedured in Universe Designer version 6.5 SP4? :?

gurmeet_singh2 (BOB member since 2007-12-14)