Question in using EXEC_SP

tonyjamero

OK I am thinking of using the EXEC_SP in AwareIM to run a stored procedure (SP) from an external MS SQL Server. The stored procedure is basically a saved query that would return 8 columns of records.

I really do not need to save the result of the query into any of my BS Objects. I simply want to display the result of the external query/stored procedure every time it is called. So I am thinking of creating a process that would execute the external stored procedure and display the result.

Do I still need to create an object in my business space to return the result of the external query/stored procedure for this?

aware_support

Yes

tonyjamero

What am i doing wrong? I get an error 'Internal error. database environment externalSQLServerName not found'. The externalSQLServerName is a linked server object in my AwareIM SQL Server. Using SQL Management Studio from my AwareIM SQL Server, I can execute that external stored procedure from the linked server.

Here is my process

EXEC_SP 'externaldbname.dbo.external_storedprocedure' from 'externalSQLServerName' RETURN Myresults
DISPLAY 'Query Myresults'

I did create an Object MyResults with 6 text attributes for the 6 column of records that the external stored procedure will return.

tonyjamero

Scratch the EXEC_SP.

After reading the Object Persistence, I decided to create an Object (ExQuery) that has Existing-external database persistence. I was able to create the connection settings to the external daabase server and select the VIEW and it discovered all the attributes.

Then I create a query to display the content of the Object (ExQuery). But sometimes it would return records and sometimes it wont.

Anything i am missing her? I tried selecting a primary key and not selecting any primary key.

tonyjamero

I wonder if it has something to do with displaying NULL records. The external VIEW has NULL values in some of the records.

I could query the VIEW from SQL Management Studio and it shows like

colum1 column2 colum3
NULL NULL 5341
NULL 1243 NULL
123 NULL NULL
341 NULL NULL

The attributes of the Object created are defaulting to Plain text and I cannot modify it being database:existing external Persistence

Any suggestion? All I really need is to be display the result of an external VIEW or even a Stored procedure.

tonyjamero

I just discovered that if I click on the 'Show Print Friendly' icon on the query, it opens another webpage showing all records of the query from Object linked to the external VIEW. I also tried to export it and all the records are being exported.

So it must be the Query itself with the default standard not being able to display all unless its 'Printer-Friendly'?

tonyjamero

after spending quite a few hours figuring out how to display the result of my query of a View from an external SQL database, I tried exec_sp and external db persistence, I ended up with the external db persistence and made some major modification on my View so I can see the query result much better. it works great now.