Run native SQL from DBACockPit using a DBCON to external SQL Server

[alex_pegorini]

Hi,

I have set up a connection to an external MS SQL Server using to DBCO and tested the connection works fine.

I would like to be able to execute native SQL statements against this external DBCON connection from the SQL Command Editor within DBACockPit for testing purposes.  However not sure if this is possible as I am the guessing the DBCON mechanism may need the ABAP stack to work ??

If it is possible I would greatly appreciate help with the syntax. ie select * from MYDBCON.MYTABLE etc

Many thanks for any assistance.

Alex

[former_member184473]

Hello Alex,

You need an ABAP stack to use DBACockpit.
Are you able to run transaction DBACockpit?

Regards,

Eduardo Rezende
SAP Support

[alex_pegorini]

Hi Eduardo,

Yes I can run the DBACockpit without problem.

Also I can run native SQL commands and functions from the SQL Command Dialogue within DBACockpit no problem. ie simple example :

select top 100 M.MATNR from MARA M

But what I want to be able to do is run native SQL commands over a DBCON I set up in DBCO transaction to an external SQL DB on another machine.  Is this possible ?

Thanks for any help

[former_member184473]

Hi Alex,

Have you create the DBCON, you can setup a 'System' in DBACockpit and then use 'SQL Command Editor' under 'Diagnostics'.

Regards,
Eduardo

[Sriram2009]

Hi Alex

Could you refer the SAP KBA

1601608 - How to access an external Microsoft SQL Server database

BR

SS

[alex_pegorini]

Hi SS, Thank you for the link. Unfortunately that post doesn't cover my specific issue of executing linked DBCON queries from within the DBACockpit command editor. I am concluding it's not possible.  The same link can also be found at but not behind the MarketPlace walled garden for future reference. Thks

[Sriram2009]

Hi Alex

On the same SAP note have you check the point number 4 ? refer the snapshot

BR

SS

[alex_pegorini]

Yes thank you SS this SAP note in point number 4 does actually include a link to another useful SAP note (Set up remote monitoring for MS SQL server DB) which describes in detail the steps necessary steps to set up a new 'System' in DBACockPit using the DBCON previously created in DBCO, as mentioned by above.  I missed this initially, so thank you for pointing it out!

I believe these steps would allow me to execute native SQL commands against the remote MS SQL server using the DBACockpit SQL Command editor, provided I have selected the necessary system. See below :

However I don't think its possible to write native SQL which will cross join across the two systems. I think you can only execute native SQL against the current system you have selected. Probably a better way to go for my purposes is to create a link server on the SAP SQL server to link to the remote SQL server using sp_addlinkedserver.  Thks for all the help.

[former_member184473]

Hello Alex,

From your description, I thought you need to join two different tables in the same database.
If you need access two different instances a linked server would be a solution.

After you could create a view with this join an access this view in your SAP system.
Remember to create the view accordingly the kernel limitations (uppercase, up to 26 characters in the name, ...). Check note 512739 and document  .

Regards,

Eduardo

[alex_pegorini]

Hi Eduardo,

Great links thks. Yes sorry if my initial description wasn't clear but the challenge was to try and find a method to build native cross database queries quickly and easily from within the DBACockpit for testing data structures before building into a program.  In this case I wanted to join SAP transparent tables with legacy data stored in external SQL database on another machine.

Linked server proved a good solution and easy to set up from DBACockpit! In case it helps anyone :

Execute the following commands from within SQL Command Prompt of DBACockpit to set up the linked server for external SQL DB (note use dbo schema not the SAP schema) :

EXEC HCD.dbo.sp_addlinkedserver @server = N'myLinkedServerName', @srvproduct=N'SQLSERVER', @provider=N'SQLNCLI', @datasrc=N'mySQLServer', @catalog=N'myDB'

Set up the security for linked server :

EXEC HCD.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'myLinkedServerName',@useself=N'False',@locallogin=NULL,@rmtuser=N'myUserName',@rmtpassword='########'

Then execute some native SQL to access the linked server :

SELECT * FROM [myLinkedServerName].myDB.dbo.myTable

and join against local SAP tables if required ...