Question Re: Connect to MS SQL in Technology Q&A

Connect to MS SQL

Former Member — Mon, 28 Feb 2005 09:45:52 GMT

Hi,

Can anybody tell me the detailed procedure to connect MS SQL server from VC.

Which system I have to create in EP and what are parameters to that system.

Thanks in Advance.

Srinivas Sandaka

Re: Connect to MS SQL

Former Member — Mon, 28 Feb 2005 10:50:27 GMT

Hi,

you will need to:

1. create a JDBC system in the portal (EP). One of the parameters to that connector is the connection string you're using to connect to that system (it includes the host, port, username and password, and default DB).

2. Make sure the system works (right click on the system in the portal -> test connection).

3. Map the portal user you want to use in VC to a user you can use in that system. (whatever user you use to connect to the JDBC system).

4. Go to VC and log on to the portal. In the discovery pane you should the system you defined.

5. Choose that system and search for queries.

Hope this helped,

Lior

Re: Connect to MS SQL

Former Member — Mon, 28 Feb 2005 13:31:48 GMT

Hi,

1. Created a system from com.sap.portal.systems.BUDI-> SAP_BI_JDBC.

2. Fill the below parameters

Conntection URL: jdbc:sap:sqlserver://srinivas:1433;DatabaseName=Northwind

Driver Class Name: com.sap.portals.jdbc.sqlserver.SQLServerDriver

3. Done the usermapping.

When I test this system, it returns an error:

Retrieval of default alias successful

Connection failed. Make sure user mapping is set correctly and all connection properties are correct.

Can you please guide me on this

Thanks in advance

Srinivas Sandaka

Re: Connect to MS SQL

Former Member — Mon, 28 Feb 2005 13:56:42 GMT

try removing the '//' from the connection string:

jdbc:sap:sqlserver:srinivas:1433;DatabaseName=Northwind

and check again

Re: Connect to MS SQL

Former Member — Mon, 28 Feb 2005 16:09:58 GMT

Hi,

I tried as you sugest but no luck.

Thanks in advance

Srinivas Sandaka

Re: Connect to MS SQL

Former Member — Mon, 28 Feb 2005 16:31:06 GMT

Can you get a connection with this connection string in another place (e.g. any other program using JDBC ?)

Lior

Re: Connect to MS SQL

Former Member — Mon, 28 Feb 2005 16:49:33 GMT

Hi,

I got the connection with EP6 SP4 through VC.

Conntection URL: jdbc:sap:sqlserver://srinivas:1433;DatabaseName=Northwind

Driver Class Name: com.sap.portals.jdbc.sqlserver.SQLServerDriver

But in SP9 it is giving error.

Thanks

Srinivas S

Re: Connect to MS SQL

Former Member — Mon, 28 Feb 2005 18:08:04 GMT

Hi Srinivas,

Try specifying the IP address instead of "srinivas" in the connection URL. As well check ur database name(for case sensitivity)

Connection URL = jdbc:sap:sqlserver://198.XXX.XX.XX:1433;DatabaseName =Northwind

Driver Class Name = com.sap.portals.jdbc.sqlserver.SQLServerDriver

User Mapping Type = user

Create a system alias for this newly created JDBC system
specify the database userid and password in personalization properties of the portal
see that the server you are trying to login is SQL Authenticated or Windows Authenticated.

Its should be SQL Authenticated with the userid and password.

Check whether you can access the tables from SQL Utitlity Manager with the same userid and password you have specified.

Hope this helps,

Thanks,

Praveen

PS. Dont forget to award points if found useful or solved.

Re: Connect to MS SQL

Former Member — Tue, 01 Mar 2005 12:04:52 GMT

Hi,

I did whatever you sugest, but the same error remains.

But I created a direct JDBC system, it is working fine and successfully tested.

It does't work with BIUDI JDBC system.

I found new parameters in BIUDI system:

Fixed Catalog and Fixed Schema

Do we need to specify anything into them.

Re: Connect to MS SQL

Former Member — Tue, 01 Mar 2005 20:33:01 GMT

Go back to your original connection settings. In the system, check and make sure the User Management property is set to admin,user.

Make sure you have defined an System Alias.

Make sure you have inputted User Mapping for that Alias. If you do not have user mapping set it will not work. I also set the Distributed Query Engine UserID and Password to the Database UserID and Password.

Regards,

Peter

Re: Connect to MS SQL

Former Member — Wed, 09 Mar 2005 11:27:22 GMT

Hi Peter

But the problem is that if you make the connection only based on JDBC you won’t have access from VC BI or SQL editor in the VC.

Regards,

Werner

Re: Connect to MS SQL

Former Member — Thu, 24 Mar 2005 11:58:29 GMT

I had the same problem. I solved it by configuring JDBC Connector in the Admin Console.

Check the configuration and checking the BI Java Connectors ( see Post Installation Activities in SAP Business Warehouse 3.5, which can be accessed from SAP Service Marketplace at

service.sap.com/instguides -> SAP NetWeaver -> Release 04 )

You have to configure jdbc drivers and JDBC Connector to use these drivers.

Follow the guide...

Bye, Roberto

Re: Connect to MS SQL

Former Member — Mon, 28 Mar 2005 14:51:08 GMT

Werner,

Yes, you are correct. You will not have access to the VC BI Wizard or SQL Editor. If this is what is desired, go back and create another system (with the same properties) but instead of using the standard JDBC system, create it this way:

System from Par -> com.sap.portal.systems.BIUDI -> SAP_BI_JDBC.

Then in the VC you should be able to run some SQL against that system and use the SQL Editor also. I suggest using the BI Query Wizard when you get into VC.

Regards,

Peter

Re: Connect to MS SQL

Former Member — Thu, 31 Mar 2005 10:21:56 GMT

Hi.

Did you solve this problem ?

I got same problem in EP 6.0 SP 11.

If you solve this problem, let me know.

Thanks in advance.

Regards, Arnold.

Re: Connect to MS SQL

Former Member — Thu, 31 Mar 2005 11:45:29 GMT

Dear Peter

Exactliy this ist the problem. When i definie a system with SAP_BI_JDBC I can't define "Distributed Qeuery Engine" and without that I can't connect. I guess that there is somewhere an access problem.

Regards

Werner

Re: Connect to MS SQL

Former Member — Thu, 31 Mar 2005 15:46:38 GMT

Werner,

We are not using DQE, I was mistaken. You only need to set that property if you have DQE set up and are running queries against multiple systems and combining the data.

If you only want to create a system to a SQL DB and then run SQL against it, instead of just using stored procedures, then follow the steps in my previous post, setting your user mapping as the a DB user with full control.

Regards,

Peter

Re: Connect to MS SQL

Former Member — Fri, 01 Apr 2005 07:18:00 GMT

Dear Peter

I had setup up first a sql System using the jdbc system. Without defining the DQE user a can not test or use the connection. The defined user has administration rights on the DB. When I define additional the DQE user the SQL connection works and I can use the system in the portal. Via VC a can access the stored procedures.

When I define now a similar (same definition) connection using the BI IDBC I can’t test the connection. When I access the connection via VC I receive the error “Error 21003: Cannot retrieve catalogs and shemas. Failed to connect to backend system. ……”

There is an other hint with the configuration in the backend system according to SP BW. I tried to make this configuration as descript in the BW installation guide. But there a have another problem finding the right class. Is this mandatory for using BI JDBC?

Regards

Werner