Can Personal Edition connect to SQL server?

[#1]

We have Microsoft Dynamics NAV running on a SQL server. Can Personal Edition connect to the SQL server to retrieve data directly without having me having to import into Excel first?

[#2]

Ok, I found it under File > Edit Script > select OLE DB under Database, Connect and select Microsoft OLE DB Provider for SQL Server.

However, when I attempt to create a new document, it prompts for excel file to open! When I insert new object, the fields are blank. What do I do next?

[deepakk]

hi ,

Yes you can connect any Database using Personal Edition. There is no limitation in Personal edition in terms of development.

you can do all the development activities as same as a licenses version can do.

The limitation with personal edition  that you cannot access documents created by other users.

Deepak

[deepakk]

hi,

Use the connect button to create a connection. Once the connection is done a string similar to below will be displayed.

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=123221;Data Source=232;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=LSHKWMTRS01;Use Encryption for Data=False;Tag with column collation when possible=False];

If there is no string , then it mean you have not connected.  if the string appears.

write  Select * from table (specifiy your tablename).  and Press Ctrl+R for relaod.

Or

Select the the Select button below connect to manually select table from the list.

I hope this helps.

Deepak

[#5]

Thanks Deepak. I am able to open and access the SQL Tables. Unfortunately, the tables are not in user-friendly name. Unlike JetReport we are currently using, it doesn't seem able to sort out the table by the friendly name as what we seen directly in the NAV client.

Moreover, as we have multiple companies, it will be great if the tables can be sort according to the table number (functional name) instead of the company name first followed by table number. Also, is there a build-in function to group and merge all companies of the same table so that it can be generated within a single report?

Most of the tutorials I found focus on Excel and the few database examples only focus on a single company database, not multiple companies.

[deepakk · Accepted solution]

hi,

As i know there isn't an sorting method for the table list. Its sorts on the name of the table. As developer we usually takes the table names from the editior provided by the database.

for your second query , YOu can club all the company tables into one and use them in your report.

eg: Sales Table for companies

Select Invoiceno, Date, Amt, 'Company1' as Company from Table1

concatenate  /// used for concatenating tables.

Select Invoiceno, Date, Amt, 'Company2' as Company from Table2

concatenate 

Select Invoiceno, Date, Amt, 'Company3' as Company from Table3

now in your application you will have field called as company. Selecting on the data of the company you will get the repective  result.

[#7]

Thanks. This look like repeative routine if I have 100 companies to add.

[deepakk]

You can use a for loop if required..

[#9]

For loop in SQL? Will be good if Qlikview include plugin for Dynamics NAV like JetReport to make it more accessible.

[deepakk]

For loop in the qlikview Script to concatenate multipile company data .

The code might look in this way

For i = 1 to 10

Load * from table '$(i)'

next