crystal report and oracle?

I have a little VB.net web application which connects to Oracle using ADO.NET so i make connection

Dim Oraclecon As OleDbConnection = New OleDbConnection("Provider=MSDAORA.1;Data Source=name;Password=pass;User ID=name;")

and all work good till i try to use crystal report. it generate exception(it happens when i do report.export) "CrystalDecisions.CrystalReports.Engine.LogOnException: logon failed".
in msdn i found article "Accessing Secure Databases" with such a code

' Declare require variables.
Dim logOnInfo As New TableLogOnInfo()
Dim i As Integer

' Loop through every table in the report.
For i = 0 To report.Database.Tables.Count - 1
' Set the connection information for current table.
logOnInfo.ConnectionInfo.ServerName = serverNameTxt.Text
logOnInfo.ConnectionInfo.DatabaseName = dbNameTxt.Text
logOnInfo.ConnectionInfo.UserID = userNameTxt.Text
logOnInfo.ConnectionInfo.Password = passwordTxt.Text
report.Database.Tables.Item(i).ApplyLogOnInfo(logOnInfo)
Next i

i suppose servername parameter should be equal Data Source in connection string and DatabaseName should be empty but it still don't work. and frankly speaking i don't understand why crystal report should know logon information if i use in my report ado.net dataset as data source and make connection to oracle through ado.net technology. why should i logon twice?? so if someone had such problems please help any advice will be usefulHi,

Maybe this can help you because I also use Oracle under SCO UNIX and works for me.

1. You need to have a CrystalReport control in your form.
2. Assign to that control a name (myControl, v.g.).
3. Declare two strings variables (strSQL and strCon, v.g.).
4. Make your SQL sentence for criteria that will be throw to Crystal Reports.
Example:

strSQL = "{mytable.cycle} = 2004"

This line will added to the SQL sentence formed by Crystal Reports when you make your report. Note: this can be optional.


5. Connection for Crystal Reports. This example applies to Oracle where server is a service name made with Easy Configuration of your Oracle Client software.
strCon = "DSN =server;UID=user;PWD=password;"

User: user of Oracle
Password: which is necessary to connect to Oracle

6. Finally, properties to connect to Crystal Reports and view your report.


myControl.ReportFileName = app.path & "\Test.rpt"
myControl.Connect = strCon
myControl.SelectionFormula = strSQL
myControl.Action = 1

I hope this can serve you.

Xernai.

cross-database query from ASP.NET

How do you write a SQL SELECT statement for a cross-database query in ASP.NET (ADO.NET). I understand the server.database.owner.table structure, but the command runs under a connection. How do I run a query under two connections?You don't need 2 connections. Are your 2 databases on the same server?

If they are on the same server, the syntax would be like this:

SELECT
 D1.column1,
 D2.column2
FROM
 database1.dbo.Table1 D1
INNER JOIN
 database2.dbo.Table2 D2 ON D1.ID = D2.ForeignKey

If they are on different servers, the syntax would be like this:

SELECT
 D1.column1,
 D2.column2
FROM
 Server1.database1.dbo.Table1 D1
INNER JOIN
 Server2.database2.dbo.Table2 D2 ON D1.ID = D2.ForeignKey

You might encounter this error:Could not find server 'Server2' in sysservers. Execute sp_addlinkedserver to add the server to sysservers. which means that the system stored procedure sp_addlinkedserver would need to be run to allow access to Server2 from Server1. SeeSQL Server Link Server Performance Tips for some information pertaining to linked servers.

Terri

Cross database manipulation

Hi everyone!

I recently found out that using ADO to connect to the SQL server without mentionning the data source; then, by sending a query with the following syntax: dbname.owner.tablename.columnname... an implicit connection to the data source (database) is performed automatically. This way of connecting allowed me to manipulate accross two or more databases thing that is necessary in my project.
My question is:

1- Do anyone have any bad experience and/or negative consequence to such connectivity (memory consumption, unexpected disconnection, ...)? Please consider a very high frequency of manipulation since we are dealing here with a 24/24 hour operational site.

2- Are there any alternative solutions for cross database manipulation (select, insert, update, delete)?

ThanksGot Code?

Personally I don't believe it...

I googled this...

http://www.able-consulting.com/ADO_Conn.htm|||I've found using three part names to work lovely on databases on the same server, and ]url=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_12_5vvp.asp]four part names[/url] work for databases on other servers (assuming that permissions are Ok).

These are 100% supported, and a nice, clean way to get access to remote data.

-PatP