SQL Server Express Limit

This Q&A forum allows users to post and respond to "How Do I Do ....." questions. Please do not use to report (suspected) errors - you must use your regional help desk for this. The information contained in this forum has not been validated by LANSA and, as such, LANSA cannot guarantee the accuracy of the information.
Post Reply
jyoung
Posts: 653
Joined: Thu Jan 21, 2016 6:43 am
Location: Oklahoma City, OK USA

SQL Server Express Limit

Post by jyoung » 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
capture-database-sanitized.png (27.47 KiB) Viewed 3071 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.

jyoung
Posts: 653
Joined: Thu Jan 21, 2016 6:43 am
Location: Oklahoma City, OK USA

Re: SQL Server Express Limit

Post by jyoung » 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. :x

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

Joe

atostaine
Posts: 416
Joined: Wed Jan 20, 2016 7:38 am

Re: SQL Server Express Limit

Post by atostaine » 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.
Art Tostaine

jyoung
Posts: 653
Joined: Thu Jan 21, 2016 6:43 am
Location: Oklahoma City, OK USA

Re: SQL Server Express Limit

Post by jyoung » 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
linked-server.PNG (23.77 KiB) Viewed 3017 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
linked-server-security.PNG (24.14 KiB) Viewed 3017 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.

atostaine
Posts: 416
Joined: Wed Jan 20, 2016 7:38 am

Re: SQL Server Express Limit

Post by atostaine » 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.
Art Tostaine

atostaine
Posts: 416
Joined: Wed Jan 20, 2016 7:38 am

Re: SQL Server Express Limit

Post by atostaine » 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.
Art Tostaine

HamadSheikh
Posts: 27
Joined: Fri Mar 18, 2016 3:49 am
Location: USA
Contact:

Re: SQL Server Express Limit

Post by HamadSheikh » 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.

robe070
Posts: 6
Joined: Thu May 26, 2016 9:27 am

Re: SQL Server Express Limit

Post by robe070 » 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.

Post Reply