The copy-pastes and explanations blog for SQL code, errors and daily cases!
This blog is a 'list' of actions that always good to have available.
The copy-paste concept here is short and clear explanations and descriptions (no long stories!) and - of course - the code to take (copy) and use (paste).
The blog deals in the database (mostly) and software issues.
What I did? Create a device. Error message: Cannot create device 'DEVICENAME': no available virtual device numbers (vdevnos) Solution: exec sp_configure 'number of devices', 20 -- replace 20 with a number that is bigger than what it set now. Cause: There is a limit of maximum number of devices - you probably get to it. Check the number of devices: exec sp_configure 'number of devices' See the devices in the server: select * from master..sysdevices
For ASE 15.7: alter login sa with password 'OLDPASSWORD' modify password 'NEWPASSWORD' For other ASE: sp_password 'OLDPASSWORD','NEWPASSWORD' ,sa If you forgot the password and can't login: 1. go to <SAPFOLDER>/ASE-XX_X/install/ 2. Edit the RUN_SID file, add new line in the end of it with: (p=password, sa=sa) -psa 3. reset server. if you don't have a login to do that - restart the server machine. 4. start SAP ASE: <SAPFOLDER>/ASE-XX_X/install$ ./startserver -f RUN_SERVERNAME 5. while executing the startserver, a new password will be printed on the terminal screen: New SSO password for sa:ldxsqzfammlsw2 6. login as sa with the new password 7. change password
After running setup.bin for ASE server (I've got it in 15.7 isntallation), error message was raised: $ ./setup.bin Preparing to install... Extracting the JRE from the installer archive... Unpacking the JRE... Extracting the installation resources from the installer archive... Configuring the installer for this system's environment... Launching installer... ./setup.bin: 2506: exec: /tmp/install.dir.48193/Linux/resource/jre/bin/java: not found Cause: This error occurs because the installer is a 32-bit binary and this casue problems on a 64-bit server. Solution: sudo apt-get install lib32ncurses5 After that - rerun setup and it will work.
dbcc logprint('itai test errorlog') -- will be written with severity=0 or: sp_addmessage 20002, 'itai test sp_addmessage with_log' raiserror 20002 -- will be written with severity=0 or: sp_addmessage 20002, 'itai test sp_addmessage with_log', @with_log=TRUE raiserror 20002 -- will be written with severity=16 or with parameters: sp_addmessage 20003, 'itai %1! sp_addmessage with parameters', @with_log=TRUE raiserror 20003, 'parameter' -- will be written with severity=16
Error message: The transaction log in database sales is almost full. Your transaction is being suspended until space is made available in the log. Will be repuduced whan trying to work with a table in the database. Cause: Log space is full. Check it using: sp_helpdb <DBNAME> sp_helpdevice <DEVICENAME> Solution: use master go disk resize name = "<DEVICENAME>", size = "500M" go alter database sales log on <DEVICENAME> = "500M" go
1. Download SAP ASE from SAP site https://support.sap.com/en/my-support/software-downloads.html 2. Create an user for sybase (DBAdmin) create a user for sybase (DBAdmin) create a folder for sybase and give privilage to the user. $ sudo mkdir <SYBASEFOLDEER> $ sudo chown -R DBAdmin: <SYBASEFOLDEER> 3. Copy the install file to the linux server (zip file). 4. Extract the ZIP file regular unzip: unzip 51047759.ZIP unzip to a folder: unzip 51047759.ZIP -d sybase51047888 ( If required: zip installation in the server: sudo apt install zip ). 5. Check the share memory for the host Check: sudo ipcs -lm Set: $ logout root@SERVER# vi /etc/sysctl.conf root@SERVER# sysctl -p kernel.shmmax = 4294967296 6. Check that there is no running Firewall # systemctl status firewalld 7. Go to the SAP ASE folder and run setup (using the user that was created in step 2). ./setup.bin During the installation you'll need to set some definitions and choose which components to install. 8. cat SYBASE.sh >> ~/.profile cd <SAPASEFOLDER> cat SYBASE.sh >> ~/.profile 9.0 Checks and settings: export LANG= source SYBASE.sh * check that sybase process is up: ps -ef | grep -i syb * check that ASE works: isql64 -S SERVERNAME -Uusername -Ppassword 1> select @@version 2> go ------------------------------------------------------------------------------- Adaptive Server Enterprise/16.0/EBF 22383 SMP/P/x86_64/Enterprise Linux/aseceph eus/3530/64-bit/FBO/Sun Feb 16 06:09:40 2014 (1 row affected)
select * from FirstTable cross join LongRunTB --> Incorrect syntax near 'LongRunTB'. In order to perform "cross join": select * from FirstTable, LongRunTB --> works
IF NOT EXISTS (select * from sysobjects where name = 'FK_NAME' and type = 'RI') ALTER TABLE [dbo].[TABLENAME] ADD CONSTRAINT FK_NAME FOREIGN KEY ([COLUMNNAME]) REFERENCES [dbo].[REF_TABLENAME] ([REF_COLUMNNAME]) GO
