PDA

View Full Version : SQL Server setup



mikemeer
2012-08-02, 18:21
I am in the Data Space of the app builder. I want to add a SQL server database - how do I do it?

lianjasupport
2012-08-02, 18:40
You import the SQL server database (click the small cog icon) then build you apps against the copy. In final release the "Data Source" of the app, page or section is used to connect via odbc to your live data.

mikemeer
2012-08-02, 18:45
I do that and after the 3rd table, it stops working. Help...

lianjasupport
2012-08-02, 18:47
Set debug on

Then do it

And let us see the c:\lianja\debug\debug_client.txt file

It will help us identify the issue.

mikemeer
2012-08-02, 22:04
how do I set debug on?

HankFay
2012-08-02, 22:20
Hi Mike,

go to the Console and issue:

set debug on

That affects debug for all parts of Lianja, including the current app.

Hank

mikemeer
2012-08-02, 22:24
Ok. So I go to Console option - Recital/VFP tab (Under Input) - I type "Set step on". Then what? I see no interaction telling me anything has changed.

Thanks,
Mike

lianjasupport
2012-08-03, 06:46
After typing set debug on in the Recital/VFP tab in the Console Workspace, certain debug information will be logged to the file C:\Lianja\debug\debug_client.txt. So, if you rerun the operation that caused the problem, information about it will be logged in debug_client.txt. Send us the file (ideally Submit a Ticket from the website Support menu and attach the file) and we can investigate the problem.

Thanks.

mikemeer
2012-08-03, 09:53
I tried that and it doesn't write anything to the debug file.

lianjasupport
2012-08-03, 10:10
The debug output should be in:

c:\lianja\debug\debug_client.txt

mikemeer
2012-08-03, 10:20
The file has a size of 0. It doesn't change.

lianjasupport
2012-08-03, 10:27
Try selecting the ODBC tab in the console workspace.

Type in the DSN name. and connect. If the connection is not succesful the reason will be displayed in the "Output" panel. If it is successful then issue:

dir

You will see the table names and schemas from the MSSQL database if you have used them.

Now, assuming you have a schema called mike and a table called test issue the following commands:

use mike.test
? reccount()
list structure
list

lianjasupport
2012-08-03, 10:28
The file has a size of 0. It doesn't change.


Hi Mike, do you have a debug.txt in the same directory?

siotti
2012-08-03, 12:05
The file has a size of 0. It doesn't change.

I think, you should close lianja. After leaving lianja, the file is written.

mikemeer
2012-08-03, 13:13
I can't close the program the normal way. See the picture attached.110

Support: I would be willing to work with you if you want to send me a "special" dll that has debugging code.

Mike

lianjasupport
2012-08-03, 13:36
Mike, did you open the odbc connection as we suggested in the ODBC tab of the console workspace?

If so, issue a DIR and let us see the output.

Then issue USE schemaname.tablename on each table until you identify the table causing the problem.

Then let us see the structure of that MSSQL table.

mikemeer
2012-08-03, 13:47
Here is the Dir List. 111
It crashes on the first table.
Here is the structure

USE [Solovue_Welland]
GO

/****** Object: Table [dbo].[account] Script Date: 08/03/2012 10:46:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[account](
[account_id] [dbo].[primary_key_id] NOT NULL,
[company_id] [dbo].[id] NULL,
[account_num] [decimal](6, 1) NULL,
[description] [char](50) NULL,
[account_type] [dbo].[code] NULL,
[ar_adjust_fl] [dbo].[flag] NULL,
[ar_cash_fl] [dbo].[flag] NULL,
[ar_income_fl] [dbo].[flag] NULL,
[bad_debt_fl] [dbo].[flag] NULL,
[system_fl] [dbo].[flag] NULL,
[qb_link_info] [char](30) NULL,
[inactive_fl] [dbo].[flag] NULL,
[primary_fl] [dbo].[flag] NULL,
[commission_fl] [dbo].[flag] NULL,
[created_on_user_id] [dbo].[id] NULL,
[created_on_dt] [dbo].[datetime_both] NULL,
[last_updated_user_id] [dbo].[id] NULL,
[last_updated_dt] [dbo].[datetime_both] NULL,
[locked_by_user_id] [dbo].[id] NULL,
[locked_dt] [dbo].[datetime_both] NULL,
[CreatedBy] [nvarchar](128) NOT NULL,
[ModifiedBy] [nvarchar](128) NOT NULL,
[Created] [datetime] NOT NULL,
[Modified] [datetime] NOT NULL,
[RowVersion] [int] NOT NULL,
[cash_fl] [dbo].[flag] NULL,
[cogs_fl] [dbo].[flag] NULL,
[inventory_fl] [dbo].[flag] NULL,
[Po_wash_fl] [dbo].[flag] NULL,
[Materials_fl] [dbo].[flag] NULL,
[Show_on_ar_fl] [dbo].[flag] NULL,
CONSTRAINT [account_pkey] PRIMARY KEY CLUSTERED
(
[account_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__company__7E6CC920] DEFAULT ((0)) FOR [company_id]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__account__7F60ED59] DEFAULT (space((0))) FOR [account_type]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__ar_adju__00551192] DEFAULT ((0)) FOR [ar_adjust_fl]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__ar_cash__014935CB] DEFAULT ((0)) FOR [ar_cash_fl]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__ar_inco__023D5A04] DEFAULT ((0)) FOR [ar_income_fl]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__system___03317E3D] DEFAULT ((0)) FOR [system_fl]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__inactiv__0425A276] DEFAULT ((0)) FOR [inactive_fl]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__primary__0519C6AF] DEFAULT ((0)) FOR [primary_fl]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__commiss__060DEAE8] DEFAULT ((0)) FOR [commission_fl]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__created__07020F21] DEFAULT ((0)) FOR [created_on_user_id]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__last_up__07F6335A] DEFAULT ((0)) FOR [last_updated_user_id]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__locked___08EA5793] DEFAULT ((0)) FOR [locked_by_user_id]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__locked___09DE7BCC] DEFAULT (NULL) FOR [locked_dt]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [account_CreatedBy_df] DEFAULT (suser_sname()) FOR [CreatedBy]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [account_ModifiedBy_df] DEFAULT (suser_sname()) FOR [ModifiedBy]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [account_Created_df] DEFAULT (getdate()) FOR [Created]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [account_Modified_df] DEFAULT (getdate()) FOR [Modified]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [account_RowVersion_df] DEFAULT ((1)) FOR [RowVersion]
GO

lianjasupport
2012-08-03, 13:59
Mike, can you create that table as a new temporary table without the constraints and see if that can then be opened.

If it can't, drop the temp table then create it starting with the first column, then the second etc until you identify the column causing the problem.

mikemeer
2012-08-03, 14:18
Ok. from the console, I can use "dbo.account" and can select * from dbo.account - however, it crashes immediately when I try to import the database.

lianjasupport
2012-08-03, 14:22
Can you issue

LIST STRU

after the USE and let me see what it created.

Also

GOTO TOP
DISPLAY

and let me see the output. Sounds like a NULLs issue on a column.

mikemeer
2012-08-03, 14:34
Here you go:

112


I don't understand why it works in the console but it wont import it.

Mike

lianjasupport
2012-08-03, 14:52
You and me both. Let me take a look at our code and see what could be causing this.

Obviously the issue with the importer as the table looks fine.

We will take a look and get back to you.

FYI you can open tables like this.

use dbo.account connst "..."

then:

copy to tablename

but that would be tedious.

lianjasupport
2012-08-03, 15:46
Mike, looking at our code, try setting up a DSN with a username and password just for the import and see if that works for you. That is the only difference i can see between import and using tables in the console workspace.

mikemeer
2012-08-03, 15:52
How would I do the import in L with a DSN?

lianjasupport
2012-08-03, 15:57
Just create the DSN using ODBC admin then specify it in the import dialog.

mikemeer
2012-08-03, 16:01
Any chance I can call you or Skype you to see if we can get this resolved?

lianjasupport
2012-08-03, 16:12
Someone will contact you with their skype name.

mikemeer
2012-08-03, 16:27
*
* Recital embedded database engine
* Version 10.0.4
* Compiled on Jul 18 2012 22:23:57
*
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G

mikemeer
2012-08-03, 16:50
Here is the table it seems to hang on:

114

davefoss
2012-08-03, 17:05
Here is the table it seems to hang on:

114

Hi Mike, If we could ask you to do one more thing. If you could SET DEBUG ON, make your ODBC connection from the console, USE this table, then post the resulting debug file here we'd appreciate it. Thanks for your patience!

mikemeer
2012-08-03, 17:25
I can't post anything. I let it "spin" for over 5 min. When I clicked on the window it went white and unresponsive. I had to end it and nothing got written out to either file.

Mike

lianjasupport
2012-08-03, 17:51
Mike, can you turn ODBC tracing on so we can determine if this is in our code or the ODBC driver.

mikemeer
2012-08-03, 17:54
Sorry, but, I dont know how... can you give me instructions?

mikemeer
2012-08-06, 11:59
Hi,

You found that the ODBC driver is not translating user defined field types "properly".... Are you going to adjust for this issue in some future release? Until then, I can create a program that creates the "sql create statements" to reproduce the database. How would I run a group of them? I don't want to have to cut and paste each one.

Also, once the structures are there... how do I get the data into it?

Thanks,
Mike

barrymavin
2012-08-19, 19:34
Mike, odbc import is all fixed up in beta9r6. Give that a whirl and let us know how you get on.

mikemeer
2012-08-28, 00:55
No, it doesn't work in release 7. It stops on the following table:


CREATE TABLE [dbo].[SchemaAudit](
[AuditDate] [datetime] NOT NULL,
[UserName] [sysname] NOT NULL,
[Event] [sysname] NOT NULL,
[Schema] [sysname] NULL,
[Object] [varchar](50) NULL,
[TSQL] [varchar](max) NOT NULL,
[XMLEventData] [xml] NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


Mike

mikemeer
2012-08-28, 01:17
Can you add a field to the import to handle a comma delimited list of tables to exclude from import? That would solve this issue.

Mike

lianjasupport
2012-08-28, 05:42
Hi Mike, we can look into that in another beta refresh as we are expecting to ship beta9r8 this week. Maybe we should have grid of tables that you can select from.