Reports & Microsoft SQL Server 2012 Express LocalDB support policy

[Former Member]

Hello,

Willing to move some databases into Microsoft SQL Server 2012 (2014) Express LocalDB, I have tryed to map some reports on that database.

Using OLE DB SQL Native Client (v11) and SAP CR RunTime SP10 (>SP9), I still have trouble when using this data source:

After drawing some simple reports (standard ones with 2-3 fields from the database):

- VS designer:

+ when "checking database" or "getting a preview" of the report, it always complains about missing tables.

+ when trying to "re-map" tables from one SQL Server 2012 Express LocalDB to another one it fails, complaining about not findind resources.

- Running application (.NET 4.0, 32 or 64-bits):

+ without any change to the report configuration: CR Viewer complains with multiple error codes (17, ...) that it cannot find the tables

+ trying to change the configured database from the report: CR VIewer complains with some more error codes

=> Is Microsoft SQL Server 2012 Express LocalDB now officially supported by CR ?

=> Is changing datasource/database configuraton during execution now officially supported by CR ?

Feel free to provide an any links / howto's / white paper about how managing Microsoft SQL Server 2012 Express LocalDB with SAP CR !

Many thanks in advance.

Regards.

[former_member11696 · Accepted solution]

Hi JP,

What is the first error code you get?

Those errors are being reported back directly from SQL Server client/DB. So need details of the error to determine the issue.

To connect you must use the MS SQL Native 11 client dll.

Don

[Former Member · Accepted solution]

Hello and thanks for your time.

As requested, please find hereafter additional informations requested (codes for each step producing error).

- Yes, using (OLE DB)SQL Native Client v11

- VS designer:

+ when "checking database" or "getting a preview" of the report: "Drop table" "Database table "xxx" not found. Continue to drop this table from the report" (then it shows the report results ...)

+ when trying to "re-map" tables from one SQL Server 2012 Express LocalDB to another one: "Cannot open or access data object" (translated from "Impossible d'ouvrir l'objet de données ou d'y accéder") and then "Unknown database connector error" (translated from "Erreur Database Connector inconnue"). Those two errors messages repeat for each table contained in the report

- Running application (.NET 4.0, 32 or 64-bits)

Done with VS2013 SAP CR libraries VS project in Debug mode.

I have the same issue when running on another computer only having the runtime SP10.

Both can execute queries from c# on the database.

+ without any change to the report configuration: "Table "xxx" not found. Error in file xxxx{53312761-372A-42CB-882B-D9C4A1695350}.rpt. Cannot find table."

+ trying to change the configured database from the report: "Cannot start connection. Details: [Database provider code: 18456], Cannot start connection. Details: [Database provider code: 4060], Cannot start connection xxxx{4D826462-CD30-44FD-8E2B-629F69F60596}.rpt. Details: [Database provider code: 18456]"

=> 18456 refers to a bad login. Currently use the integrated security connection, I report the .net sqldatabase parameters from the existing/connected application ConnectionString.

>>> For reference, here is what is done in the code to change configured database:

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(GpmiDb.ConnectionString);

            CrystalDecisions.Shared.ConnectionInfo ci = new CrystalDecisions.Shared.ConnectionInfo();
            ci.ServerName = builder.DataSource; // = (LocalDB)\v11.0
   ci.DatabaseName = builder.AttachDBFilename; // = D:\TEST\TEST.MDF
   ci.IntegratedSecurity = builder.IntegratedSecurity; // = true
   ci.UserID = builder.UserID; // = ""
   ci.Password = builder.Password; // = ""
   ci.Type = ConnectionInfoType.SQL; // I have also tryed with "CRQE" (what is used inside the report before modification) but have the same issue.

foreach (CrystalDecisions.CrystalReports.Engine.Table tbl in rd.Database.Tables)
            {
                TableLogOnInfo logon = tbl.LogOnInfo;
                logon.ConnectionInfo = ci;
                tbl.ApplyLogOnInfo(logon);
                // tbl.Location = tbl.Location;
            }

            Sections sections = rd.ReportDefinition.Sections;
            foreach (Section section in sections)
            {
                ReportObjects reportObjects = section.ReportObjects;
                foreach (ReportObject reportObject in reportObjects)
                {
                    if (reportObject.Kind == ReportObjectKind.SubreportObject)
                    {
                        SubreportObject subreportObject = (SubreportObject)reportObject;
                        ReportDocument subReportDocument = subreportObject.OpenSubreport(subreportObject.SubreportName);
                        SetConnection(subReportDocument, ci);
                    }
                }
            }

<<<

[former_member183750 · Accepted solution]

I think  - scratch that - I know, that as long as this does not work in the designer, you are wasting your time trying to make it work from code. Thus my suggestion re the stand-alone designer. E.g.; we need to resolve this 1st:

- VS designer:
 + when "checking database" or "getting a preview" of the report, it always complains about missing tables.
 + when trying to "re-map" tables from one SQL Server 2012 Express LocalDB to another one it fails, complaining about not findind resources.

and it is quite possible that the stand-alone designer will be quite helpful.

- Ludek

[former_member11696 · Accepted solution]

Hi JP,

Problem is you do not specify the MDF as the database files:

ci.DatabaseName = builder.AttachDBFilename; // = D:\TEST\TEST.MDF

You specify the Server and log on info only:

//loop through all the tables and pass in the connection info
foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)
{
    mainSecureDB = rpt.Database.Tables[tableIndex].LogOnInfo.ConnectionInfo.IntegratedSecurity;
    string mainTableName = crTable.Name.ToString();
    tableIndex++;

    //btrDataFile - Server
    //btrSearchPath - Database
    //btrFileLocation - User Name
    //btrPassword - password

    //pass the necessary parameters to the connectionInfo object
    //crConnectioninfo.ServerName = btrDataFile.Text.ToString();
    crConnectioninfo.ServerName = "10.161.12.206a";
    if (!mainSecureDB)
    {
        crConnectioninfo.UserID = "sa";
        crConnectioninfo.Password = "Password";
        crConnectioninfo.DatabaseName = "TEST";
    }
    else
    {   // display the ocnnect info
        crConnectioninfo.IntegratedSecurity = true;
        crConnectioninfo.UserID = btrFileLocation.Text.ToString();
        crConnectioninfo.Password = btrPassword.Text.ToString();
        crConnectioninfo.DatabaseName = btrSearchPath.Text.ToString();
    }

    crTableLogOnInfo = crTable.LogOnInfo;
    crTableLogOnInfo.ConnectionInfo = crConnectioninfo;
    crTable.ApplyLogOnInfo(crTableLogOnInfo);

    // SQL Server
    crTable.Location = crDatabase + ".dbo." + "orders"; // crTable.Location;
...

Don

[Former Member · Accepted solution]

Hello Don,

Not really sure to understand your point.

SQL Server 2012 Express LocalDB is working that way:

- You specify the server name like (LocalDB)\<LocalDB instance name>

LocalDB defaut instance is "v11.0" for 2012 : (LocalDB)\v11.0

- You provide the path to the database file (MDF)

In my case, my two databases to test are D:\TEST\TEST.MDF and D:\TEST\TEST2.MDF

>>> extract from my code dump ("builder" (SqlConnectionStringBuilder) is only extracting required informations:from the current ConnexionString used by the c# application)

ci.ServerName = builder.DataSource; // = (LocalDB)\v11.0

ci.DatabaseName = builder.AttachDBFilename; // = D:\TEST\TEST.MDF

<<<

=> So yes, I am providing the "right" database name (at least as I can see it is filled by CR for VS13 when building report from a LocalDB connection).

I also tryed to download CR2013 (Ludek Uher proposal) but SCN site link was not working last days, now ok (SAP Crystal Reports 2013 30-Day Evaluation); hope to be able to update on that test quickly.

[former_member11696 · Accepted solution]

That is the problem. When connecting to a MS SQL Database YOU NEVER select the MDF file in Crystal or any other program that I know of, it's always the Server name, Database Name, User and Password.

Server name is either LocalHost or IP address of your PC or your PC name where ever the Server is running from.

But if you are not willing to change your code then good luck, it will never work in CR Designer either where you select OLE DB or ODBC, you cannot use the Database driver option.

Don

[Former Member · Accepted solution]

Don,

I did not want you to think that I am not willing to change anything...

The issue is that LocalDB specs (http://msdn.microsoft.com/en-us/library/ms130822(v=sql.110).aspx) are not (seems not) fulfiled by what SAP APIs provide.

Following specs, I need to provide ADO DB/SQL Native Client v11 an "AttachDBFileName" property which, for LocalDB instance, is equal to the DatabaseName property (check below screenshot from MS Mgmt Studio connected to a SQL Server 2012 LocalDB instance).

Moreover, OLEDB discovery provide in CR 2013 UI this same list of "DatabaseName":

When connected, this same CR 2013 UI got the right "view" of connected "MDF-labelled" names:

Moreover, CR connected datasource properties also provide an "initial catalog" (databasename) value with that "MDF-labelled" name:

That is what I saw previously in API dumped objects and what I tryed to reproduce in my code:

As previously explained, after adding tables, I am also beeing able to add fields from those tables in my fresh-new report document:

It fikrst shows results in preview but as soon as I try to refresh / add some more fields / ask CR to "check database" / "change database source", the interface always complain about having "lost" objects:

Site note for Ludek Uher: CR 2013 StandaAlone fresh-new builded reports are not working neither using CR runtime SP10 when opening/updating that report..

So YES, I am OK to change whatever is required but please explain me what to use/put into the "crConnectioninfo.DatabaseName" property for a SQL Express 2012 LocalDB case.

Or leave it as is, LocalDB not being supported by CR ...

(all my reports & c# code are working well while using SQL Express 2012 "classic" (not localdb)).

Thanks again for your time.

Regards.

[former_member11696 · Accepted solution]

In CR Designer here how to:

Create a new report and the Database Wizard pop's up:

Select the SQL Native 11 client:

Fill in the info:

DO NOT select the MDF file, you should see your database name:

Or check on the Integrated Security, SQL Express must be configure to accept your local Domain name or pre-configured user name.

Then drill down to your Table:

And add it.

Now when you look at the connection properties you see this:

So this is the ONLY way CR will work. Doesn't matter what MS says on how to connect, this is how CR must connect. You can't specify the MDF file.

As you can see the initial catalog is the table name and not the MDF file.

Looking at your report the table name is Articles, it's not listed in your connection so check permissions, the user you are connecting with does not have rights to access that table.

Log in using the SQL Administrator account, that should work and don't use trusted for now.

Issue appears to be a permission problem since the User name and PW are grayed out: and you can't see Articles listed.

Don

[Former Member · Accepted solution]

Don,

This screenshot relates to your "Connection Information"

ID & Password are greyed because "Integrated Security" is checked (there is no user security by default when using LocalDB (only NTFS security)).

"Articles" is a table, not a database. That is why it does not appear in that list. As for your SQL Server (standard, express or other but NOT LocalDB), Crystal, CrystalTest, ... are not tables but databases.

Your initial catalog "xtreme" is not a table name but your database name (which contains a "Credit" table in dbo schema tables list.

However, I think that while you don't give a try to use a LocalDB instance (which is not the same than the express "standalone running" edition), you won't be able to understand what is going on here.

It seems I am not the only one to report issues on LocalDB integration with SAP CR and I still did not find any "happy end" in scn forums threads till now (even if some reports to get the "support" of LocalDB in SP6+). Seems not the case for me with SP10 and (now) CR2013.

- http://scn.sap.com/thread/3575152

- http://scn.sap.com/thread/3382586

- http://scn.sap.com/thread/3342222

- ...

For your information

>>> http://msdn.microsoft.com/en-us/library/hh510202(v=sql.110).aspx

Microsoft SQL Server 2012 Express LocalDB is an execution mode of SQL Server Express targeted to program developers. LocalDB installation copies a minimal set of files necessary to start the SQL Server Database Engine. Once LocalDB is installed, developers initiate a connection by using a special connection string. When connecting, the necessary SQL Server infrastructure is automatically created and started, enabling the application to use the database without complex or time consuming configuration tasks. Developer Tools can provide developers with a SQL Server Database Engine that lets them write and test Transact-SQL code without having to manage a full server instance of SQL Server. An instance of SQL Server Express LocalDB is managed by using the SqlLocalDB.exe utility. SQL Server Express LocalDB should be used in place of the SQL Server Express user instance feature which is deprecated.

<<<

It takes 5 minutes to download and install LocalDB librairies, 30Mo image. Good for time-to-market applications !

Regards.

[former_member11696 · Accepted solution]

Great info.

The way you are connecting then is not supported... CR requires access to the DB engine through the client with a Database and Table name specified, security is DB or Enterprise integrated.

Like All Versions from Microsoft, Express versions are typically not supported.

If you would like this to be added to the supported platform list add your request to Idea Place

For development purposes installing and configuring the Client to not use LocalDB as the source is simple to do also.

Don

[Former Member · Accepted solution]

Ok, now with that rule in mind, I finally get it to work.

What I understood so far is that LocalDB access through CR does not support the default catalog naming convention of LocalDB (which is the path to the file as we saw it).

It must be done at least with a named catalog which reference a supported string for the "DatabaseName" in CR. I've also tryed to pass a "slashed" named in order to protect windows separators in the path but CR Viewer still complains with that kind of label.

This can be done using an alias for the catalog while requesting the attachement of the MDF file (setting an initial catalog name in place of the default one).

Issue we have when having that kind of restriction is that the catalog name is unique to the instance; an MDF file cannot either be attached to another instance at the same time.

Therefore, with help of the SQL LocalDB Wrapper - Home, database changes can be done during application run and datasource changes can be then simply propagated to the Crystal Report document (which is now running fine in both scenarios).

In case of MDF location change, and because of the instance restart requirement to do it, we should also use a named instance in order to isolate the impact only to the application related databases and not dropping other applications / windows system mounted database at the same time.

Drawback: something/application must be run before the report (or designer) in order to start the LocalDB instance with right parameters (instance name / catalog alias / right MDF file).

After restarting the user Windows session and/or computer, ServerName=(LocalDB)\xxxx | DBName=zzzz is no more existing. At that step, CR Designer / Viewer will not be able to gain access to the database.

To resume, there is a solution to make it working ... but this is still not a supported scenario from CR.

Thank you again for your time and patience.

Regards,

Jean-Philippe.

[former_member183750]

Looking at the SAP Crystal Reports, Developer Version for Microsoft Visual Studio - Supported Platforms:

Not sure if that explicitly includes Microsoft SQL Server 2012 (2014) Express LocalDB, but I don't see why not. My suggestion would be to download CR 2013 and see what happens there:

SME Free Trials | SME Software | SAP

Being a stand-alone version of CR, it may give us a better error, etc.

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

[Former Member]

Hello Ludek,

I've installed the CR2013 standalone designer and get same issues.

Screenshots & evidences provided on Don thread.

Regards,

Jean-Philippe.