Deferred Name Resolution
Symptom
Deferred Name Resolution turned on
Stored procedure created with dynamic sql which includes a reference to a #temp table
Error 208 is observed
Msg 208, Level 16, State 1:
Server 'myserver', Procedure 'testproc', Line 13:
#tempsysobjects not found. Specify owner.objectname or use sp_help to check
whether the object exists (sp_help may produce lots of output).
Environment
Sybase Adaptive Server Enterprise(ASE) 15.5
SAP Adaptive Server Enterprise(ASE) 15.7
SAP Adaptive Server Enterprise(ASE) 16.0
Reproducing the Issue
Configure deferred name resolution
sp_configure 'deferred name resolution',1
go
OR
set deferred_name_resolution on
go
Create procedure with dynamic sql that creates a #temptable
create proc testproc as
declare @SQLStatement varchar(6000)
select @SQLStatement =
'select * ' +
'into ' +
'#tempsysobjects ' +
'from ' +
'master..sysobjects'
exec (@SQLStatement)
select top 1 * from #tempsysobjects
go
Execute Procedure
exec testproc
go
Turn off deferred name resolutions:
sp_configure 'deferred name resolution',0
go
OR
set deferred_name_resolution off
go
Cause
Symptom
From source server, use ddlgen to generate DDL for the database
On target server, load the DDL that was generated from source server, run sp_depends on object and find missing stored procedure / trigger
Msg 208, Level 16, State 1:
Environment
SAP Adaptive Server Enterprise (ASE)
SAP Adaptive Server Enterprise For Business Suite
SAP Adaptive Server Enterprise Cluster Edition
Sybase Adaptive Server Enterprise (ASE)
Sybase Adaptive Server Enterprise Cluster Edition
Reproducing the Issue
Here are two different scenarios on how this may happen:
Using ddlgen:
From source server, run sp_depends on table "titles", it shows 3 stored procedures, 2 triggers, and 1 view:
isql -Usa -Ppassword -Sase_server
use pubs2
go
sp_depends titles
go
Things inside the current database that reference the object.
object type
-------------------------------------- --------------------------------
dbo.deltitle trigger
dbo.history_proc stored procedure
dbo.title_proc stored procedure
dbo.titleid_proc stored procedure
dbo.titleview view
dbo.totalsales_trig trigger
....
Run ddlgen from source server to generate DDL for this database:
ddlgen -Usa -Ppassword -Sase_server -TDB -Npubs2 -opubs2.ddl
Load the DDL into target server to generate the same in target server:
isql -Usa -Ppassword -Sase_server2 -ipubs2.ddl
On target server, run sp_depends on table "titles", it shows 3 stored procedures, 1 trigger, and 1 view; a trigger named "totalsales_trig" is missing in target server:
isql -Usa -Ppassword -Sase_server2
use pubs2
go
sp_depends titles
go
Things inside the current database that reference the object.
object type
-------------------------------- --------------------------------
dbo.deltitle trigger
dbo.history_proc stored procedure
dbo.title_proc stored procedure
dbo.titleid_proc stored procedure
dbo.titleview view
...
sysdepends loses dependency information when objects are dropped:
Create a table:
create table t1 (x int)
go
create procedure p1 as select * from t1
go
select * from sysdepends where depid = object_id("t1")
go
id number depid depnumber status selall resultobj readobj columns
----------- --------- ------------ --------- ------ ------ --------- ------- -------------------------------------------------------------------------------------------------
431691137 1 415691080 0 1 1 0 0 0x02
(1 row affected)
Drop the table t1:
drop table t1
go
create table t1 (x int)
go
select * from sysdepends where depid = object_id("t1")
go
id number depid depnumber status selall resultobj readobj columns
----------- --------- ------------ --------- ------ ------ --------- ------- -------------------------------------------------------------------------------------------------
(0 rows affected)
Cause
There is an assumption that the dependence is created in right order.
If the objects were created out of order (for example, if a trigger that uses a table is created before the table is created, or if a procedure that uses a view is created before the view is created.), no rows exist in sysdepends for dependencies.
Looking into the output when loading the DDL script to target server, the error is reported that when creating trigger "totalsales_trig", table "titles" is not found (table "titles" is not created yet):
<<<<< CREATING Trigger - "pubs2.dbo.totalsales_trig" >>>>>
Msg 208, Level 16, State 1:
Server 'sp110', Procedure 'totalsales_trig', Line 16:
titles 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:
Server 'sp110', Procedure 'totalsales_trig', Line 23:
titles not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
Resolution
The solution is to:
Recreate the missing object(s) on target server for the ddlgen issue.
Drop and recreate the stored procedure for dropped objects.
Keywords
sp_depends sysdepends sybmigrate ddlgen
## Auto Start/Stop/Sybase server @ OS Level #######
refer this also ...
https://sap-database.blogspot.com/2017/05/startup-script-to-run-sybase-ase.html
-----------------------------------------------------working code----with root previledge-----
cd /etc/systemd/system/
vi sybase.service
[Unit]
Description=Sybase ASE Server 16.0
After=network.target
[Service]
Environment=PW="Password" DS=sybase_ASE
WorkingDirectory=/apps/sybase/server/16.04/ASE-16_0/install
Type=forking
ExecStart=/sbin/runuser sybase -c ". /apps/sybase/server/16.04/SYBASE.sh; /apps/sybase/server/16.04/ASE-16_0/bin/startserver -f RUN_${DS} -f RUN_${DS}_BS"
ExecStop= /sbin/runuser sybase -c ". /apps/sybase/server/16.04/SYBASE.sh; printf 'shutdown\ngo' | isql -Usa -P$PW -S$DS"
[Install]
WantedBy=multi-user.target
~
###########SQL FRAME COMMAND ################# ###################
select 'use',name, char(10) ,'go' , char(10) , 'sp_changedbowner', name ,'true' , char(10) ,'go' from sysdatabases where name not in ('sybsystemdb','sybsystemprocs','master','model','tempdb','sybsecurity') order by suid
go
go
#################OS level COMMAND##################### ###################
sybase> lscpu | grep "cpu(s):"
sybase> cat /proc/cpuinfo | grep "processor" | wc -l
4
isybase> cat /proc/meminfo | grep "MemTotal"
MemTotal: 7878772 kB
sybase>
###############user permission issue ###################
use user_db
go
alter all userID.* modify owner dbo
go
sp_dropuser userID
go
sp_addalias userID,dbo
go
sp_helpuser userID
go
#######Script for DB CREATE LOAD #####can make edit and use as needed##############
#!/bin/bash
# Sybase server details
SERVER_NAME="sybase_ASE"
#USERNAME="sa_id"
#PASSWORD="sa_pwd"
# Get parameters from user
DB_NAME=$1
#DATA_SIZE=$2
#LOG_SIZE=$3
USERNAME=$2
PASSWORD=$3
PATH_NAME=$4
# Create device names
#DEVICE_NAME="${DB_NAME}D1"
#LOG_DEVICE_NAME="${DB_NAME}L1"
# Create physical device names
#PHYSICAL_DATA_DEVICE_NAME="/apps/sybase/data05/userdata/${DEVICE_NAME}.dat"
#PHYSICAL_LOG_DEVICE_NAME="/apps/sybase/data04/userlog/${LOG_DEVICE_NAME}.dat"
# Create device command
#CREATE_DEVICE_CMD="disk init name = '${DEVICE_NAME}', physname = '${PHYSICAL_DATA_DEVICE_NAME}', size = '${DATA_SIZE}'"
#CREATE_LOG_DEVICE_CMD="disk init name = '${LOG_DEVICE_NAME}', physname = '${PHYSICAL_LOG_DEVICE_NAME}', size = '${LOG_SIZE}'"
# Create database command
#CREATE_DB_CMD="create database ${DB_NAME} on ${DEVICE_NAME} = '${DATA_SIZE}' log on ${LOG_DEVICE_NAME} = '${LOG_SIZE}'"
# load database command
LOAD_DB_CMD="load database ${DB_NAME} from '$PATH_NAME/${DB_NAME}.dat'"
# online database command
online_database=" online database ${DB_NAME}"
# Execute commands using isql
isql -U $USERNAME -P $PASSWORD -S $SERVER_NAME -w 1000 << EOF
#$CREATE_DEVICE_CMD
#go
#$CREATE_LOG_DEVICE_CMD
#go
#$CREATE_DB_CMD
#go
$LOAD_DB_CMD
go
$online_database
go
EOF
#############################################################
Display Locks on Tables
This procedure will display the names of the locked tables (shared, update, etc) across databases. create procedure sp_locks @spid1 int = NULL, /* server process id to check for locks */ @spid2 int = NULL /* other process id to c heck for locks */ as declare @length int declare @msg varchar(250) if @@trancount = 0 begin set transaction isolation level 1 set chained off end /* Print warning message about cursor lock info: ** 18052, "The class column will display the cursor name for locks associated ** with a cursor for the current user and the cursor id for other ** users." */ exec sp_getmessage 18052, @msg out print @msg /* ** Show the locks for both parameters. */ if @spid1 is not NULL begin select @length = max(datalength(db_name(dbid))) from master..syslocks where spid in (@spid1, @spid2) if (@length > 15) select spid, locktype = name, table_name = object_name(id, dbid), page, dbname = db_name(dbid), class from master..syslocks l, master..spt_values v where l.type = v.number and v.type = "L" and spid in (@spid1, @spid2) else select spid, locktype = name, table_name = object_name(id, dbid), page, dbname = convert(char(15), db_name(dbid)), class from master..syslocks l, master..spt_values v where l.type = v.number and v.type = "L" and spid in (@spid1, @spid2) end /* ** No paramete rs, so show all the locks. */ else begin select @length = max(datalength(db_name(dbid))) from master..syslocks if (@length > 15) select spid, locktype = name, table_name = object_name(id, dbid), page, dbname = db_name(dbid), class from master..syslocks l, master..spt_values v where l.type = v.number and v.type = "L" order by spid, dbname, table_name, locktype, page else select spid, locktype = name, table_name = object_name(id, dbid), page, dbname = convert(char(15), db_name(dbid)), class from master..syslocks l, master..spt_values v where l.type = v.number and v.type = "L" order by spid, dbname, table_name, locktype, page end return (0) Sybase ASE: Setting up user assigned custom tempdbs
|
No comments:
Post a Comment