Breaking the Rules - Modifying database

[Former Member]

Hi All,

Sap is very strict about now modifying their databases in Enterprise manager or any other tool for that matter. And I fully agree with that statement, as it could be catastrophic for a company should the wrong data be changed. Thats why they gave us userdefined fields and tables. But I have a problem.

While userdefined tables are a bit tricky to work with, its usable, however, for my client, I am developing an application that uses windows CE and CE SQL2 on a heldheld scanner. I populate a userdefined table onto a screen, and then use the com objects to import the data.

I am using replication to establish the connection, and writing only to userdefined tables. This works great no problem. However, when I modify the data from the Mobile application using SQL statements (eg. INSERT), I cannot write to the table because there is an @ variable in it. I can get past this problem straight in SQL Server Enterprise, but SQL CE I cannot.

May I add my own table onto SAP called 'LAZ_SCANNER_IN' ?

[Former Member]

Hi Leonard,

Could you post the SQL Statement?

The @ is for a tablename, or for a variable?

Regards,

Ibai Peñ

[Former Member]

Hi Ibai,

Thanks for your response. Remember that is application resides out of SAP and SQL 2003, and sits on a windows CE PDA using CE SQL 2

Try 1

SQLCOMMAND.CommandText = "INSERT INTO [dbo][@LAZ_SCANNERIN] (REG) VALUES ('REG')"

Try 2

SQLCOMMAND.CommandText = "INSERT INTO @LAZ_SCANNERIN(REG) VALUES ('REG')"

Try 3

SQLCOMMAND.CommandText = "INSERT INTO [dbo].[@LAZ_SCANNERIN] (REG) VALUES ('REG')"

But adding a Table on SQL Enterprise called LAZ_SCANNERIN

SQLCOMMAND.CommandText = "INSERT INTO [dbo].[LAZ_SCANNERIN] (REG) VALUES ('REG')"

This works,

[Former Member]

Hi Leonard

For all problems to write statement in SQL, you can use the SQL Enterprice Manager: it work for you. Select the table, then open it in data mode. Then press 'Modify query type' and select 'INSERT FROM'. Then fill the value 'REG' in the field Reg. The editor write for you the SQL statement.

Hi, Andrea

[Former Member]

Hi Andrea,

I think what Leonard meant was that if you create a user-defined table through SBO it automatically adds an @ sign in front of the table name. When you try to access this table from within MS SQL Enterprise Manager or code it works fine, but as soon as you access it from a remote device (like a scanner) it seems as if SQL CE does not recognise this table name with the @ sign in front even if you write it as [@Table]. SQL CE thinks it is a variable.

His only solution currently is to create a seperate table (manually) to write and read data from. He does not need to see or access this table from inside SBO, so is it OK and acceptable by SAP to create a table manually inside the company SBO database.

Hope it helps!

Regards,

Adele

[Former Member]

Hi,

Leonard, have you think about creating a view that points to the @Table with no @ on its name? Then you could make your querys against the view.

This way, you would not break the rules, and I think it will work.

I´m not very sure if SQL Server CE ables you to do it, but give it a try.

Regards,

Ibai Peñ

[Former Member]

Hi Ibai,

Thanks for the response, SQL CE does not allow me to setup a view, what I had to do to solve the problem after days of messing around was to create a UDT with the @ in the table name, bound all my datasources to that, create another table with the same structure without the app, setup my merge replication between SQL 2003 and SQL CE, connect to SQL2003 using the standard SQL connect option, and from there, read the entries into a matrix.

The reason for this is, using the DBDATASOURCE option, I could not add a table that was non sap, it just would not execute.

Either way, problem solved. But thanks for your help