Visual LANSA Forum

Posted: **Tue May 16, 2017 1:33 am**

Anybody hit the SQL Server Express size limitation? What did you do about it?

I just did. I have never done it before, but while trying to pull some data down from Production into my local database it errored out.
Checked the size and sure enough I am out of space.

: capture-database-sanitized.png (27.47 KiB) Viewed 28010 times

We are looking to see if we have a standard license that I can upgrade to.

It made me wonder if anyone else has hit this limit. In all my years of web development I have never hit it. Although since doing more "enterprise" development where databases are massive I can easily see this being a problem for other LANSA developers.

Not only do we have the size of our data to deal with, we also have all the LANSA data in the same database.

Having never really thought much of it before, I think there should be an option when installing LANSA to choose Sql Server Express OR MySql.
I have never used LANSA with MySql, but it looks like its possible from the docs I have read. At least then we won't have to worry about hitting the Sql Server Express limitations.

Posted: **Tue May 16, 2017 6:17 am**

Well, they won't give me a standard license and won't let me install MySql so my only other option was to truncate data down and be very selective about what I am importing.

Unfortunately, this means that I can't use LANSA's "Copy data from IBM i" function.
Once you get a linked server setup its not to bad to do a "insert into select" command.

So every File I load from the IBM i, I have to write a custom script to pull data down.

Of course, since the IBM i is case sensitive and Sql Server is not (unless you have another collation defined) then I run into duplicate data errors a lot.

Still curious what others are doing with this problem, if anything.

Joe

Posted: **Wed May 17, 2017 11:51 am**

I've always tested against a development database on the same setup as production. Some guy Dingo showed me how to copy data using connect bifs before Lansa had the copy iseries data function but it just never was that easy to do.

Posted: **Thu May 18, 2017 1:31 am**

I will say I've had a *heck* of a time getting a linked server to connect properly. So if anybody else tries to do this, this is how I got it work.

Create a new linked server with the IBMDASQL OLE DB Provider.
The linked server name is how you will reference the server in SQL.
You have to put something in the "Product Name", I don't think it matters what it is just something has to be there.
The data source should be your DNS or IP to your iSeries.
The provider string, is essentially a connection string. Its my understanding your can put libraries and other stuff here. I just set the Default Collection to the library where the Files are.
The catalog is the database on the iSeries. In System i Navigator its under the "Databases" node.

: linked-server.PNG (23.77 KiB) Viewed 27956 times

The other thing to do is set the security context. Use credentials to the iSeries that has access to the Files.

: linked-server-security.PNG (24.14 KiB) Viewed 27956 times

Now you can import data with it using something like this:

Code: Select all

USE LANSA;
GO

-- dump any existing data
TRUNCATE TABLE MYSCHEMA.MYTABLE;

INSERT INTO MYSCHEMA.MYTABLE(TBOFID, TBOFD, TBECN, TBOD, TBTTC, TBELVL, TBDXMT, TBDLE, TBDLN, TBTTT, TBCC, TBTCTC, TBTN, TBULU, TBTLU, TBAH, [@@UPID])
SELECT TBOFID, TBOFD, TBECN, TBOD, TBTTC, TBELVL, TBDXMT, TBDLE, TBDLN, TBTTT, TBCC, TBTCTC, TBTN, TBULU, TBTLU, TBAH, 1 
	FROM OPENQUERY(DEV, 'select * from MYLIBRARY.MYTABLE where TBOFID = 1002');

MYSCHEMA.MYTABLE should be your schema and table in SQL Server, and MYLIBRARY.MYTABLE should be the library and file on the iSeries.
The first parameter in the OPENQUERY statement is the name of linked server you are querying against (note it is NOT in quotes!).

This has been working out pretty well for me so far, just have to watch the constraint violations in SQL Server due to the casing in the keys.

Posted: **Fri May 19, 2017 4:44 am**

If Lansa could add a where clause when using the copy file data from iSeries that would solve your problem.

Posted: **Fri May 19, 2017 5:46 am**

The catalog can be found on green screen using WRKRDBDIRE, look for an entry with *LOCAL as the remote location.

Posted: **Fri May 26, 2017 5:30 am**

Please check what tables in your database are the biggest i.e. take up the most space. I suspect the 10GB space is not taken up by your LANSA source code, but rather by other things that may be expendable.

Posted: **Mon Jun 05, 2017 8:46 am**

All the SQL Server Express limitations are removed if you install SQL Server Developer Edition. It is free and is fully licensed for development, testing and demonstrations though it is not licensed for production. It is has all the capabilities of SQL Server Enterprise Edition.

https://www.microsoft.com/en-us/sql-ser ... evelopment

Download and install this before installing Visual LANSA.

Note that you need to sign up for Visual Studio Dev Essentials in order to download it.

Visual LANSA Forum

SQL Server Express Limit

SQL Server Express Limit

Re: SQL Server Express Limit

Re: SQL Server Express Limit

Re: SQL Server Express Limit

Re: SQL Server Express Limit

Re: SQL Server Express Limit

Re: SQL Server Express Limit

Re: SQL Server Express Limit