The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

Help, I am brain fried. I have a data base with read only permissions. I have a second db I created so I can create the tables and do the work needed. I am trying very hard to use the "create existing table" to extract the tables from the read only db to my db.

I am failing terribly.

are there a set of permissions I need for "unload" and "create existing table" to be able to work on a db with read only permissions? My db has dba with every thing wide open. I am testing this in isql sybase10. Am I even in the right environment?

I have tried what I am finding online and either really misunderstanding what is going on or I am in the wrong environment to be testing this. I will end up writing code in vb6 once I can see it work.

Suggestions PLEASE...

Thanks George

asked 16 May '12, 13:09

George's gravatar image

George
165101017
accept rate: 0%


Here is an end-to-end demonstration of how to use a proxy table to copy data from one database to another. In this example, ddd2 represents your read-only database, and ddd1 is the database where you can actually mess, er, work with the data.

"%SQLANY10%\win32\dbinit.exe" ddd1.db
"%SQLANY10%\win32\dbinit.exe" ddd2.db

"%SQLANY10%\win32\dbspawn.exe" -f "%SQLANY10%\win32\dbeng10.exe" ddd1.db 
"%SQLANY10%\win32\dbspawn.exe" -f "%SQLANY10%\win32\dbeng10.exe" ddd2.db

"%SQLANY10%\win32\dbisql.exe" -c "ENG=ddd1;DBN=ddd1;UID=dba;PWD=sql;CON=ddd1"
"%SQLANY10%\win32\dbisql.exe" -c "ENG=ddd2;DBN=ddd2;UID=dba;PWD=sql;CON=ddd2"

---------------------------------------------------------------------
-- On ddd2

CREATE TABLE t ( 
   pkey INTEGER NOT NULL PRIMARY KEY,
   data INTEGER NOT NULL );

INSERT t VALUES ( 1, 2 );
INSERT t VALUES ( 2, 2 );
COMMIT;

SELECT *
  FROM t
 ORDER BY t.pkey;

/*

pkey,data
1,2
2,2

*/

---------------------------------------------------------------------
-- On ddd1

CREATE TABLE t ( 
   pkey INTEGER NOT NULL PRIMARY KEY,
   data INTEGER NOT NULL );

CREATE SERVER ddd2_server CLASS 'SAODBC' 
   USING 'DRIVER=SQL Anywhere 10;ENG=ddd2;DBN=ddd2';

/* or...

CREATE SERVER ddd2_server CLASS 'SAODBC' 
   USING 'DSN=ddd2';

*/

CREATE EXTERNLOGIN DBA -- optional for SQL Anywhere
   TO ddd2_server 
   REMOTE LOGIN "DBA" IDENTIFIED BY 'sql';

CREATE EXISTING TABLE proxy_t 
   AT 'ddd2_server...t';

-- Note: ON EXISTING UPDATE does not work (yet?)...

INSERT t SELECT * FROM proxy_t;
COMMIT;

SELECT *
  FROM t
 ORDER BY t.pkey;

/*

pkey,data
1,2
2,2

*/
permanent link

answered 16 May '12, 14:47

Breck%20Carter's gravatar image

Breck Carter
26.6k418575824
accept rate: 21%

edited 16 May '12, 14:48

Can you explain exactly the problem(s) you are encountering?

Perhaps you misunderstand the purpose of the CREATE EXISTING TABLE statement. It is defining a proxy, or reference, to the remote server table which does not mean it is extracting the table to the database against which the statement was run. An operation that affects the proxy table i.e., an update or delete will fail as the remote server is read-only since the statement is passed to the remote server to be processed.

permanent link

answered 16 May '12, 13:35

Chris%20Keating's gravatar image

Chris Keating
2.4k1444
accept rate: 22%

Yes, I am not understanding. I have enterprise.db (read only) and autotiretech.db (wide open) and I need to get table informatin from enterprise to autotiretech.

I was thinking that 'create existing table TABLENAME COLUMNS at autotiretech would copy a tables layout from enterprise.db to autotiretech.db then I could select * from TABLENAME in enterprise.db in to TABLENAME in autotiretech.db.

Where I am lacking in understanding is do I use the ODBC name for the server, or the datafile for the server.

does the table get created in the "AT" database where I have write permissions, that is what I am understanding and needing.

The bottom line is I need information from a put in to b and I AM FAILING.... Such a simple task and I can not figure out the syntax in Sybase. I have done this in SQL, or like this and I am getting old and losing it.. smile Your comments are appreciated. I have looked at so many examples and I just don't or cannot get it. Maybe it is the limited access the owners of enterprise.db allow their customers or I am doing it really wrong. I can do an "unload" on the enterprise.db, if that helps you with what permissions I have.

(16 May '12, 14:13) George
Replies hidden

If you are able to connect ISQL to enterprise.db, and connect ISQL to autotiretech.db as well, you are halfway there... you have working connection parameters.

The problem is, if you create an EXISTING table on autotiretech.db, the data for that table exists on the read-only enterprise.db... so you can only SELECT from it, not INSERT or LOAD. What you also need is to create a real table on autotiretech.db that looks exactly like the table on enterprise.db, into which you can INSERT or LOAD data.

The quickest way to get the CREATE TABLE statements you need, is to run the dbunload -n command and look inside the resulting reload.sql file.

(16 May '12, 14:27) Breck Carter

The SERVER is the name specified in the CREATE SERVER statement. You must first create a remote server before you can define remote tables.

The basic process is:

CREATE SERVER RemoteSA CLASS ' SAODBC' USING'MyDSN';

CREATE EXISTING TABLE t AT 'RemoteSA..dba.t';

(16 May '12, 14:38) Chris Keating

Being frustrated I looked closer at the user the company/owner of enterprise.db made for me.
So here is a link to what the user has for permissions:

http://www.santa-ana.tv/Ext-Permissions.jpg

The user has only "select" permissions at file level. This is probably why I can not get create existing table, unload, etc... to work.

Any suggestions on how to work with "select" permission only? I need to pull data in to a seperate data base so I can actually do some work.

A lot of gray hairs over this and I don't have that many brown left... SMILE

Appreciated. George

(17 May '12, 12:16) George
Replies hidden

Did you look at the code in my reply? It uses SELECT to copy the data.

(17 May '12, 12:30) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×22
×20
×18
×4

question asked: 16 May '12, 13:09

question was seen: 1,748 times

last updated: 17 May '12, 12:30