Isql and If Exist syntax error in UNIX script


 
Thread Tools Search this Thread
Top Forums UNIX for Advanced & Expert Users Isql and If Exist syntax error in UNIX script
# 1  
Old 10-09-2018
Isql and If Exist syntax error in UNIX script

Hello Everyone,

Coming again for your help to solve the below error:

In a script, i had created a temp table (Temp_table) and loaded the data in it using bcp command (performed successfully) and I wanted to move it to the preferred table (called Main_table) for further use. hence I have added the following code:

Code:
isql -U$DBLogin -PPassed -S$DBName -e <<!
   use $schema
go
   if exists (select 1 from syscolumns where id = object_id('Main_table') and name = 'Column1')
   begin
   insert into Main_table select * from $db..Temp_table
   end
else
   begin
   create table Main_table
   (
        Column1 char (5),
        Column2 int
   )

   insert into Main_table select * from $db..Temp_table
   end
go
   DROP TABLE $db..Temp_table
go
!

But, I have got the following error message:

Code:
Msg 208, Level 16, State 1:
Main_table not found. Specify owner.objectname or use sp_help to
check whether the object exists (sp_help may produce lots of output).
Msg 208, Level 16, State 1:
Main_table not found. Specify owner.objectname or use sp_help to
check whether the object exists (sp_help may produce lots of output).

Please have a look and advise the error in my query.

Thank you
# 2  
Old 10-09-2018
Looks like your Main_table is not found, just like the error message said.

I assume you are using Sybase and have a created a database in Sybase, identified in $DBName

What is the value of $DBName and please post the data structure of that DB, or at least the structure of Main_table.
# 3  
Old 10-10-2018
Dear Neo,

That's why I have wrote else part, if the table is not found it should create and insert the data from temp table. but, it hasn't worked.
Code:
Table data is as below:
22055;20181009;
21088;20181009;
66666;20181009;
09999;20181009;
40000;20181009;
Table Structure:
create table Main_table
   (
        Stemm char (5),
        Date int,
        Percode char (5) null
   )

Thank you. Let me know if you need anymore information.
# 4  
Old 10-10-2018
Sure you're using the correct table owner?
# 5  
Old 10-10-2018
Yes or that the DB user has create table or even DB access permissions.
# 6  
Old 10-10-2018
Yes, the DB user has all the access permissions.
# 7  
Old 10-10-2018
Code:
Main_table not found. Specify owner.objectname or use sp_help to
check whether the object exists (sp_help may produce lots of output)

Did you use sp_help to check whether the object exists, per the message?

Code:
syntax

sp_help [objname]

Parameters

objname

is the name of any object in sysobjects or any user-defined datatype or system datatype in systypes. You cannot specify database names. objname can include tables, views, stored procedures, logs, rules, defaults, triggers, referential constraints, encryption keys, and check constraints, but refers to tables when you enable optimistic_index_lock. Use owner names if the object owner is not the user running the command and is not the database owner.


Code:
sp_help Main_table

Code:
Chapter 1: System Procedures
sp_help
Description
Reports information about a database object (any object listed in sysobjects) and about system or user-defined datatypes, as well as user-defined functions, computed columns and function-based indexes. Column displays optimistic_index_lock.

Syntax
sp_help [objname]
Parameters
objname
is the name of any object in sysobjects or any user-defined datatype or system datatype in systypes. You cannot specify database names. objname can include tables, views, stored procedures, logs, rules, defaults, triggers, referential constraints, encryption keys, and check constraints, but refers to tables when you enable optimistic_index_lock. Use owner names if the object owner is not the user running the command and is not the database owner.

Examples
Example 1
Displays a list of objects in sysobjects and displays each object's name, owner, and object type. Also displays a list of each user-defined datatype in systypes, indicating the datatype name, storage type, length, null type, default name, and rule name. Null type is 0 (null values not allowed) or 1 (null values allowed):

sp_help
Example 2
Displays information about a partitioned publishers table. sp_help also lists any attributes assigned to the specified table and its indexes, giving the attribute's class, name, integer value, character value, and comments.

sp_help publishersName                  Owner              Object_Type     Create_date
--------------------- ---------------    -----------     -----------------
publishers            dbo                user table      Oct 7 2005 11:14AM
Column_name Type  Length  Prec  Scale  Nulls  Default_name  Rule_name
Access_Rule_name  Computed_Column_object    Identity
----------  ----  ------  ----- -----  -----  ------------  -----------
----------------  -----------------------    --------
pub_id     char       4    NULL    NULL     0  NULL         pub_idrule
            NULL                    NULL          0
pub_name   varchar   40    NULL    NULL      1           NULL
           NULL                    NULL          0
city       varchar   20    NULL    NULL      1           NULL
           NULL                    NULL          0
state       char      2    NULL    NULL      1           NULL
           NULL                    NULL          0

Object does not have any indexes.
keytype  object      related_objs  object_keys           related_keys
-------  ------      ------------  -----------           ---------------
primary  publishers  -- none --    pub_id,*,*,*,*,*,*,*  *,*,*,*,*,*,*,*

name        type        partition_type  partitions  partition_keys
----------  --------    --------------  ----------  --------------
publishers  base table  roundrobin               3  NULL

partition_name          partition_id   pages  segment  create_date
------------------      -------------- -----  -------  -----------------
publishers_608002166    608002166          1  default  Oct 13 2005 11:18AM
publishers_1116527980   1116527980         1  default  Oct 13 2005 11:18AM
publishers_1132528037   1132528037         1  default  Oct 13 2005 11:19AM

Partition_Conditions
--------------------
NULL

Avg_pages  Max_pages  Min_pages  Ratio(Max/Avg)  Ratio(Min/Avg)
---------  ---------  ---------  --------------  --------------
        1          1          1  1.0000000       1.0000000

Lock scheme Allpages
The attribute 'exp_row_size" is not applicable to tables with allpages lock scheme.

exp_row  reservepagegap  fillfactor  max_rows_per_page  identity_gap
-------  --------------  ----------  -----------------  ------------
      0               0           0                  0             0

concurrency_opt_threshold  optimistic_index_lock  dealloc_first_txtpg
-------------------------  ---------------------  -------------------
                        0                      0                    0

Example 3
Displays information about a partitioned titles table:

sp_help titlesName            Owner               Object_Type    Create_date
---------------- ---------------    -----------    ---------------------
titles           db                 user table     Oct 7 2005 11:14AM
(1 row affected)Column_name Type Length Prec Scale Nulls Default_name Rule_name    Access_Rule_name      Identity
----------- ---- ------ ---- ----- ----- ------------ ---------    -----------------
      --------
title_id    tid       6 NULL NULL      0 NULL         title_idrule NULL
       0
title   varchar      80 NULL NULL      0 NULL         NULL         NULL
        0
type       char      12 NULL NULL      0 typedflt     NULL         NULL
        0
pub_id     char       4 NULL NULL      1 NULL         NULL         NULL
        0
price     money       8 NULL NULL      1 NULL         NULL         NULL
        0
advance   money       8 NULL NULL      1 NULL         NULL         NULL
        0
total_sales int       4 NULL NULL      1 NULL         NULL         NULL
        0
notes   varchar     200 NULL NULL      1 NULL         NULL         NULL
        0
pubdate datetime      8 NULL NULL      0 datedflt     NULL         NULL
        0
contract    bit       1 NULL NULL      0 NULL         NULL         NULL
        0
index_name      index_description                       index_keys

Object has the following indexes

index_name  index_keys      index_description  index_max_rows_per_page
index_fillfactor    index_reservepagegap  index_created      index_local
----------  ----------      -----------------  ------------------------
----------------    ---------------------  ------------      ----------
title_idx   total_sales      clustered                                 0
               0                        0  Oct 13 2005 5:20PM  Local Index

index_ptn_name        index_ptn_seg
--------------------  ---------------
p1                     default
p2                     default
p3                     default
title_idx_98505151     default

keytype   object       related_object  object_keys                     
related_keys
--------  ---------  ----------------------------------------------- 
---------------------
foreign   roysched     titles           title_id, *, *, *, *, *, *, * title_id, *, *, *, *, *, *, *
foreign   salesdetail  titles           title_id, *, *, *, *, *, *, * title_id, *, *, *, *, *, *
foreign   titleauthor  titles           title_id, *, *, *, *, *, *, * title_id, *, *, *, *, *, * 
foreign   titles       publishers        pub_id, *, *, *, *, *, *, * 
pub_id, *, *, *, *, *, *, *
primary   titles       -- none --        title_id, *, *, *, *, *, *, *
*, *, *, *, *, *, *, * 

name    type        partition_type  partitions  partition_keys
----    ----------  --------------  ----------  --------------
titles  base table  range                    4  pubdate

partition_name  partition_id  pages  segment  create_date
--------------  ------------  -----  -------  -------------------
q1              937051343         1  default  Oct 13 2005 5:20PM
q2              953051400         1  default  Oct 13 2005 5:20PM
q3              969051457         1  default  Oct 13 2005 5:20PM
q4              985051514         1  default  Oct 13 2005 5:20PM


Partition_Conditions
--------------------
VALUES <= ("3/31/2006")
VALUES <= ("6/30/2006")
VALUES <= ("9/30/2006")
VALUES <= ("12/31/2006")
VALUES <= ("3'31'2006")

Avg_pages  Max_pages  Min_pages  Ratio(Max/Avg)  Ratio(Min/Avg)
---------  ---------  ---------  --------------  --------------
        1          1          1  1.000000        1.000000

Lock scheme Allpages
The attribute 'exp_row_size" is not applicable to tables with allpages lock scheme.

exp_row  reservepagegap  fillfactor  max_rows_per_page  identity_gap
-------  --------------  ----------  -----------------  ------------
      0               0           0                  0             0

concurrency_opt_threshold  optimistic_index_lock  dealloc_first_txtpg
-------------------------  ---------------------  -------------------
                        0                       0                   0
Example 4
Displays information about the trigger marytrig owned by user “mary”. The quotes are needed, because the period is a special character:

sp_help "mary.marytrig"
Name         Owner              Object_type
------------ ------------------ ----------------
marytrig     mary              trigger

Data_located_on_segment When_created
----------------------- --------------------------
not applicable          Mar 20 2002  2:03PM
Example 5
Displays information about the system datatype money:

sp_help money
Type_name  Storage_type  Length  Prec   Scale  Nulls  Defaul_name
---------  ------------  -------  -----  -----  -----  ------------
Rule_name  Access_Rule_name  Identity
---------  ----------------  --------
money       money               8   NULL    NULL      1         NULL
NULL                   NULL          0 
Example 6
Displays information about the user-defined datatype identype. The report indicates the base type from which the datatype was created, whether it allows nulls, the names of any rules and defaults bound to the datatype, and whether it has the IDENTITY property:

sp_help identype
Type_name  Storage_type  Length  Prec   Scale  Nulls  Defaul_name
---------  ------------  -------  -----  -----  -----  ------------
Rule_name  Access_Rule_name  Identity
---------  ----------------  --------
identype        numeric        4   NULL    NULL      1         NULL
NULL                   NULL          1 
Example 7
Shows a new column, indicating whether optimistic index locking is enabled. 1 indicates that the option is enabled; 0 indicates that it is not.

sp_help "mytable"
------------
exp_row_size   reserve   pagegap   fillfactor   max_rows_per_page
------------------------------------------------------
           1         0         0            0                   0
concurrency_opt_threshold    optimistic_index_lock
-------------------------------------------------
                        0                        1

Example 8
Shows a virtual computed column:

alter table authors add fullname as au_fname + ' ' + au_lname
sp_help authors
Object has the following computed columns

Column_Name Property
----------- --------
fullname    virtual

Text
------------------------------
AS au_fname + ' ' + au_lname
Example 9
Shows a virtual computed column to a materialized computed column:

alter table authors modify fullname materialzied
sp_help authors
Object has the following computed columns

Column_Name Property    
----------- ------------
fullname    materialized

Text 
-------------------------------------------
AS  au_fname + ' ' + au_lname
MATERIALIZED
Example 10
The result set for sp_help table_name includes the Decrypt_Default_name column, which indicates the decrypt default name for the column. For example, if you run the following:

create table encr_table(col1 int encrypt decrypt_default 1)
When you run sp_help on encr_table, it shows the following:

Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name     Computed_Column_object Identity Encrypted Decrypt_Default_name
----------- ---- ------ ---- ----- ----- --------------------- -------------- ------------------- --------- -------------------------
c1          int       4 NULL NULL      0 NULL         NULL       NULL
    NULL                          0         1 encr_table_col1_1036527695

Example 11
Displays the Name, Owner, Object_type, Object_status, and Create_date of the predicate object:

grant select on tab1 where col1 = 5 as pred1 to robert
sp_help pred1

Name  Owner  Object_type  Object_status  Create_date
----- ------ ------------- -------------- ------------
pred1 dbo    predicate     -- none --      Feb 9 2010 12:49PM
Usage
For virtually-hashed table, sp_help reports:

That a table is virtually-hashed with this message:

Object is Virtually Hashed
The hash_key_factors for the table with a message using this syntax:

column_1:hash_factor_1,
column_2:hash_factor_2..., 
max_hash_key=max_hash_value
For example:

attribute_class         attribute       int_value
char_value                                comments                            
---------------------   ---------------  ------------------------
--------------------------------------    -----------
hash clustered tables   hash key factors                     NULL
id:10.0, id2:1.0, max_hash_key=1000.0           NULL 
sp_help looks for an object in the current database only.

sp_help follows the Adaptive Server rules for finding objects:

If you do not specify an owner name, and you own an object with the specified name, sp_help reports on that object.

If you do not specify an owner name, and do not own an object of that name, but the database owner does, sp_help reports on the database owner's object.

If neither you nor the database owner owns an object with the specified name, sp_help reports an error condition, even if an object with that name exists in the database for a different owner. Qualify objects that are owned by database users other than yourself and the database owner with the owner's name, as shown in Example 4.

If both you and the database owner own objects with the specified name, and you want to access the database owner's object, specify the name in the format dbo.objectname.

sp_help works on temporary tables if you issue it from tempdb.

Columns with the IDENTITY property have an “Identity” value of 1; others have an “Identity” value of 0. In example 2, there are no IDENTITY columns.

sp_help lists any indexes on a table, including indexes created by defining unique or primary key constraints in the create table or alter table statements. It also lists any attributes associated with those indexes. However, sp_help does not describe any information about the integrity constraints defined for a table. Use sp_helpconstraint for information about any integrity constraints.

sp_help displays the following new settings:

The locking scheme, which can be set with create table and changed with alter table

The expected row size, which can be set with create table and changed with sp_chgattribute

The reserve page gap, which can be set with create table and changed with sp_chgattribute

The row lock promotion settings, which can be set or changed with sp_setpglockpromote and dropped with sp_droprowlockpromote

sp_help includes the report from:

sp_helpindex - showing the order of the keys used to create the index and the space management properties

sp_helpartition - showing the partition information of the table

sp_helpcomputedcolumn - showing the computed column information of the table

When Component Integration Services is enabled, sp_help displays information on the storage location of remote objects.

sp_help displays information about encryption keys. When a key name is specified as the parameter to sp_help, the command lists the key's name, owner, object type, and creation date.

sp_help tablename indicates if a column is encrypted, including the name of the decrypt default on the column, if one exists.

Permissions
Any user can execute sp_help.

Auditing
Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

Roles - Current active roles

Keywords or options - NULL

Previous value - NULL

Current value - NULL

Other information - All input parameters

Proxy information - Original login name, if set proxy in effect

See also
Documents Java in Adaptive Server Enterprise for more information about SQLJ routines.

Commands alter table, create table

System procedures sp_chgattribute, sp_droprowlockpromote, sp_helpartition, sp_helpcomputedcolumn, sp_helpconstraint, sp_helpindex, sp_setpglockpromote

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Isql syntax error in UNIX script

Hello Everyone, Coming again for your help to solve the below error: In a script, i had created a temp table (Temp_table) and loaded the data in it using bcp command (performed successfully) and I wanted to move it to the preferred table (called Main_table) for further use. hence I have added... (1 Reply)
Discussion started by: Suresh
1 Replies

2. Shell Programming and Scripting

Using Isql for SQL SERVER to get the table rows counts in UNIX shell script to

need to create shell script to read the table's name from file and connect SQL SERVER using isql (odbcunix) i 'm able connect to database with below command line syntex but i could not get working in shell script with SQL and storing the row count in variable. isql -v DSN USERNAME PASSWD ... (6 Replies)
Discussion started by: pimmit22043
6 Replies

3. Shell Programming and Scripting

Syntax Error in Unix Shell Script

I am trying to run a unix script in my home directory.Snippet below echo "`date '+%Y%m%d_%H%M%S'` Getting ProductList.dat" if ( -f $DIR/ProductList.dat) then cp $DIR/ProductList.dat MigratedProductList.dat else echo "`date '+%Y%m%d_%H%M%S'`ProductList.dat does not exist; Processing... (4 Replies)
Discussion started by: Mary James
4 Replies

4. Shell Programming and Scripting

Need to capture error of sybase isql in unix

Hi Gurus, I am very new in Unix, I have 1 script, in which I am truncating the table , then BCP the data in Sybase table, and then loading the data from sybase table to sybase table. every thing is working fine, but the problem is with Error. I made some hanges in my insert statement so... (3 Replies)
Discussion started by: aksar
3 Replies

5. Shell Programming and Scripting

ISQL syntax can't read

Hi Everyone, newbie here, please help me i can't read the code i'm confused reading this code. select c.net_svc_id, c.inst_st_dt into ${REO_RECON_USERID_LUZON_TEMP} from ${WO_INST_SITE_COMP_FIELDS} a, ${WO_INST_SITE_COMPONENTS} b, ${WO_INST} c where a.cust_ac_no = b.cust_ac_no and... (3 Replies)
Discussion started by: nikki1200
3 Replies

6. Shell Programming and Scripting

ISQL syntax

Hi All, I'm niks and i'm a newbie here and newbie in shell, i'm just wondering what is the meaning of -U -P -S in the sample script below. "-U iccbs_dbo -P iccbsdbo -S CCB_REO" Thanks, (2 Replies)
Discussion started by: nikki1200
2 Replies

7. Shell Programming and Scripting

AWK syntax /bailing script error when executing in UNIX

Hi I am trying to execute the following awk script in unix but getting the following error awk: syntax error near line 1 awk: bailing out near line 1 for i in `cat search` do grep -i -l $i *.sas | awk -v token=$i '{print token "\t" $0}' done Please let me know what could be the... (4 Replies)
Discussion started by: nandugo1
4 Replies

8. Shell Programming and Scripting

Does not exist or unreadable error in windows ftp script

I have a file like this 07200900.SUP,in a windows directory I need to FTP this file to UNIX , the directory in unix is N:\orgs\Financial Aid\MIIS\0910\FTP I am getting this error miis_ftp.ELM_SUP.shl: =cd orgs/"Financial Aid"/"MIIS"/"0910"/"FTP" : not found IN THE LOG FILE Activities for Mon... (3 Replies)
Discussion started by: rechever
3 Replies

9. UNIX for Dummies Questions & Answers

How to check path exist or not in UNIX shell script

UNIX Shell Script I'm in /home/suneel dirctory in that directory need to check for a path for example com/src/resources If Path exists need to copy the files from one directory If path not exist need to create the folders and copy the files UNIX shell script help required (3 Replies)
Discussion started by: suneelc
3 Replies

10. Shell Programming and Scripting

isql query in unix shell script

Dear all I want to execute some isql command from unix shell script. Kindly suggest me. isql command mention below. isql -U -P use gdb_1 go select count (*) from table_x go (3 Replies)
Discussion started by: jaydeep_sadaria
3 Replies
Login or Register to Ask a Question