Connect to SQL Server without DSN 3

[Glasgow]

I am currently using the .Connect property of the CrystalReport control (from Crystal 7) in VB to produce a number of different reports interrogating a SQL Server database. This works fine when I specify a DSN in the property. However, I would prefer not to use a DSN at all but all my attempts have failed.

Is it possible to achieve this without a DSN - either using this .Connect property or some other mechanism to print the report?

Thanks in advance

 

[SurfingGecko]

Here are some variations on using the .Connect under different conditions. (you may want to copy and paste in notetab to see the lines properly)

NOTE:

The database qualifier parameters DSQ and DBQ are only needed when the database location is changed at runtime. DSQ is used with a native connection to an SQL type database or when a connection is made through an OLE DB provider. The DBQ parameter is used with an ODBC connection. If your DBMS does not use the database concept (i.e. Oracle), you cannot specify a DSQ or DBQ parameter. Only one of the DSQ or DBQ parameter should be used (they should not be used together).

> When using the DSQ or DBQ parameters for SQL type databases, it is very important to remove any reference to the database name and owner (i.e. "pubs.dbo.table1" should be modified to become "table1&quot from the report. This can be changed in the Designer under the Database | Set Location menu in the Table text box. This will need to be done for all tables in the report.

> Before you can use this property for an ODBC/SQL database, you must install the ODBC/SQL driver for whatever SQL database you are planning to use, and put the Database/BIN location in your path.

......................................

.:Natively to an SQL type database:.
Connecting natively to a Microsoft SQL Server database. Switch to a physical database server named "Production" and a database named "pubs" with a user ID of "sa" and a password of "letmein":
CrystalReport1.Connect = "DSN=Production;UID=sa;PWD=letmein;DSQ=pubs"

......................................

.:OLE DB to a SQL type database through the ODBC Provider:.
Connecting to a SQL Server database through ODBC and OLE DB (ODBC provider). Switch to an ODBC data source name called "Prod" and a database named "pubs" with a user ID's of "sa" and a password of "letmein":
CrystalReport1.Connect = "DSN=Prod;UID=sa;PWD=letmein;DSQ=pubs"

.....................................
.:OLE DB to a SQL Server database through the SQL Server Provider:.
Switch to a SQL Server (physical) name of "Accounting" and a database named "pubs" with a user ID's of "sa" and a password of "letmein":

CrystalReport1.Connect = "DSN=Accounting;UID=sa;PWD=letmein;DSQ=pubs"
......................................

Hope this helps you out.

Cheers,

SurfingGecko

http://www.surfinggeckosoftware.com

Home of Crystal Ease

 

[shokti]

if you don't like to use ODBC in your report you have to use ADO connection. to do that open crystal then logonserver then select Active Data(ADO) on the server type.
then select ado or ole connectionstring & enter your connection string: connection string is like this

Provider=MSDASQL.1;Password=poweruser;Persist Security Info=True;User ID=admin;Extended Properties=DSN=MS Access Database;DBQ=c:\sample\sample.mdb;DefaultDir=c:\sample;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;

this sample is a connection string of an access database sample.mdb in sample folder, the password is poweruser & the username is admin.

after you successfully logon to the server. you can create new report and add ADO tables. the advantage of ado connection is that you can make complex linking in the tables.

i hope this helps...

 

[Glasgow]

Thanks for both your efforts. I tried shokti's suggestion first and it worked - basically I had not twigged to the fact that this problem could be rooted in the properties of the report when it is defined - I had assumed that actions taking when connecting to design the report would have no bearing on connection at run-time. I should have known better though as I have had problems with design time & run time incompatibility before.

Once I had created a completely new report and used a standard ADO style connect, it worked OK. I therefore did not go on to try any of SurfingGecko's suggestions but thank you anyway.

 

[SurfingGecko]

Cheers,

SurfingGecko

http://www.surfinggeckosoftware.com

Home of Crystal Ease

 

[Glasgow]

It would appear that I was a bit premature in my conclusions. I think I unintentionally saved the data with my report and so it appeared to work when attaching to several different servers. I have only just noticed that it produces the same report on each server. Once I save it without data, I get error 20599 - it apparently cannot find the server.

I have now therefore attempted to implement one of SurfingGecko's suggestions and all appears well - so far at least!

Thanks

 

[shokti]

do this in VB:

Private crxAppl As CRAXDRT.Application
Private crxReport As CRAXDRT.Report

Private Sub OpenReport()
Set crxAppl = New CRAXDRT.Application
Set crxReport = crxAppl.OpenReport(App.Path & "\rptSample.rpt&quot
crxReport.DiscardSavedData

For i = 1 To crxReport.Database.Tables.Count
crxReport.Database.Tables.Item(i).SetLogOnInfo "Provider=MSDASQL.1;Password=poweruser;Pers ist Security Info=True;User ID=admin;Extended Properties=DSN=MS Access Database;DBQ=c:\sample\sample.mdb;DefaultDir=c:\sample;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;"
Next i

CRViewer1.ReportSource = crxReport
CRViewer1.ViewReport
end sub


i hope this helps.

 

[shokti]

i am using different server and it works fine, just change the connection string in the .SetLogOnInfo. it works for me

 

[Glasgow]

Thanks for the input - I did get a resolution in the end. The steps were:


1.Fire up Crystal Reports designer
2.Select New/Standard/SQL Server for new report
3.Log in to development server when prompted and select database from dropdown
4.A list of tables should then be presented – select the relevant table and click ADO & Done.
5.Click Next then select/add at least one field from the list to appear on report & click Design Report
6.Click Database/Set Location and remove any prefix from the table name (e.g. Northwind.dbo.) & click Done.
7.From File menu, ensure that save data with report is not set then save.


At run time I am using a Crystal Report control setting connection as follows:

.Connect = "DSN=" & GlbSqlServer & ";" & GlbSecurity & ";DSQ=" & GlbDefaultDb

where GlbSqlServer contains name of SQL Server, GlbSecurity varies for the operating system but might contain, for example, 'UID=sa' and GlbDefaultDb contains the name of the database.