Questions tagged [scripting]
For questions about generating or using language scripts, for example scripting a database object definition.
354 questions
1
vote
1
answer
2k
views
Oracle - Delete dmp files from ASM diskgroup
I want to delete my export files daily from ASM diskgroup with a crontab job. I want to prepare a script for it.
ASMCMD> ls
exp1.dmp
exp2.dmp
exp3.dmp
exp4.dmp
ASMCMD> pwd
+DATA/EXP
How can I ...
CommunityBot
- 1
0
votes
1
answer
913
views
Oracle database 12C restore script
i'm trying to create backup restore script .
in short my backup script creating new directory every day from the operating system level and then backup the database using the directory created
i tried ...
CommunityBot
- 1
0
votes
1
answer
683
views
Script server level user securables onto another server
I have restored a backup of the live DB on a test SQL Server VM.
Then I scripted the server level SQL users and restored them on the test SQL Server and fixed the orphaned user situation.
But the user ...
CommunityBot
- 1
1
vote
1
answer
730
views
Numbering rows consecutively for a number of tables
I have a number of tables, all containing attributes aid,bid,cid, and xid of type integer, other attributes may differ. For every (given) table T, I would like to order the rows according to aid,bid,...
- 182k
3
votes
1
answer
2k
views
select two tables and print the columns side by side with matching key in hive
I have the below tables:
table_1
name_1 id_1 value_1
avi 1 21
adi 2 X
table_2
name_2 id_2 value_2
avi 1 21
adi 2 22
metadata_info: can be read from ...
CommunityBot
- 1
0
votes
1
answer
406
views
Realtime Update MySQL on every Update occurred on PostgreSQL
I have a PostgreSQL for my accounting system and a MySQL database for my php website, i want to sync these two database for every update may occurred on PostgreSQL it also updates MySQL database, they ...
CommunityBot
- 1
12
votes
1
answer
5k
views
Why does SQL Server Management Studio not script indexes by default?
Recently I realized by default, scripting the database does not including indexes, and I have to change the option every time.
Could anyone please explain the reason? Is there any insight about this ...
- 316
0
votes
1
answer
311
views
Pentaho does not read sql script (csv)
I was trying to use a different sql script for one job. For doing this i need to pass the script by file csv, so i set the script like a variable and then i make a transformation that "copy to ...
CommunityBot
- 1
2
votes
1
answer
367
views
How to order databases by size? (For use in full backups)
Recently, I've been trying to make our company's full backup process more efficient. In order to do this, my team has decided that instructing the backup job to tackle the full backups starting with ...
CommunityBot
- 1
0
votes
1
answer
37
views
Help with creating postgres databases with - in the name
I'm currently trying to script some migration of databases on a server.
As part of this process I will need to create a new database with a similar name to the existing database.
Whilst I have this ...
- 16.6k
0
votes
1
answer
4k
views
Read and Get Data from ExcelFile using powershell
I'm starting with powershell and this time. I have to read all the values from a excel file
In this case from a specific sheet.
The ultimate goal of this task is to get the values from two columns and ...
CommunityBot
- 1
0
votes
1
answer
688
views
Error with dbatools
Trying to export SQL Objects via DBAtools in Powershell.
Get-DbaAgentJob -SqlInstance 'TestServer01' -Job 'mydatabase-daily-backup' | Export-DbaScript -FilePath C:\temp\mydatabase-daily-backup.sql
...
CommunityBot
- 1
11
votes
7
answers
15k
views
How do I format the output from OBJECT_DEFINITION so that it looks the same as sp_helptext?
object_definition and sp_helptext both return the source code of a stored procedure - but how can I use object_definition and get the source code including the line breaks as the sp_helptext currently ...
8
votes
1
answer
6k
views
postgres create limited user that can select tables and columns of those tables
I have an app that automates the making of a PostgreSQL database, and also automates the creation of a database user. This database user is given limited access for security purposes, as it will be ...
- 31.6k
0
votes
1
answer
96
views
How to execute complete database script without checks on objects existence
I'm working on a customer data archiving.
I created a complete database script to prepare the new database for archiving data with the same DB schema of the production one.
At the moment I'm preparing ...
- 1
0
votes
2
answers
7k
views
Oracle: Simple way to compare all objects of a schema in two databases?
I'm attempting to compare all objects of two schemas located in two different databases. I know there may be ways to do this with TOAD and SQL Developer, but is there another way to do this by script ...
2
votes
1
answer
3k
views
Replicaton - The current transaction cannot be committed and cannot be rolled back to a savepoint
After creating a replication publication, I go ahead and create a subscription using the script below:
-----------------BEGIN: Script to be run at Publisher 'MY_PUBLISHER_SERVER'--------------
use [...
- 1
19
votes
1
answer
10k
views
SQL Server - Adding non-nullable column to existing table - SSDT Publishing
Due to business logic, we need a new column in a table that is critical to ensure is always populated. Therefore it should be added to the table as NOT NULL. Unlike previous questions that explain how ...
- 741
2
votes
1
answer
302
views
My SSMS is bringing wrong object in "Script Stored Procedure as" > "CREATE To" > "New Query Editor Window"
When I go to Object Explorer and expand down to Stored Procedures (or Tables), right-click on one of them and choose "Script Stored Procedure / Table as" > "CREATE To" > "New Query Editor Window", it ...
CommunityBot
- 1
0
votes
1
answer
42
views
Display in asc order from multiple databases under multiple linked server and add two date parameters so that I can integrate it to my app
I have a script below where it display a result from different databases under different linked Server. The query is just connected or linked thru union all and it seems there's no issue on its result....
- 39.5k
0
votes
1
answer
63
views
Extra `CASE WHEN` Removes Text from Dynamically Created String
I am trying to generate a CREATE TABLE statement based on a given table in my SQL-SERVER database using the INFORMATION_SCHEMA tables, but when I try and concatenate the DEFAULT portion of a field ...
- 16.6k
0
votes
2
answers
96
views
Do I need the MDF and LDF files when I run a script to generate my schema? [closed]
I'm migrating a database from SQL Server 2019 to SQL Server 2017. To do so, I am using the "Generate scripts" feature in the source database to generate the schema and data separately. (I ...
- 286
5
votes
3
answers
659
views
How to minimize\plan ahead the effect of running sp_delete_backuphistory? Measure the gains too!
while running the following query:
-- DATEADD (datepart , number , date )
DECLARE @dt DATETIME
SELECT @dt = DATEADD(month,-6,getdate())
select @dt
EXEC msdb.dbo.sp_delete_backuphistory @...
- 761
2
votes
0
answers
264
views
sp_OACreate script returns Null value from API fetch? The API works well in a html file
I am trying to create a script in Microsoft SQL Server Management Studio to fetch the current exchange rate from an API and update a database. I am told it is not posisble and common to do this.
-- ...
- 855
5
votes
2
answers
2k
views
How to schedule PostgreSQL replication?
I was reading postgresql replications solution but, even I just starting understaning how it works, another doubt has arrisen.
I'll be using postgres internal replication solution but as far as I ...
- 101
0
votes
1
answer
48
views
Do SQL Server have the utility same as Oracle Wrap Utility?
i have made a useful scripts in my environment and i want to wrap the script same as Oracle Wrap utility can do. is there any other way to the same in sql server?
- 18.3k
0
votes
1
answer
2k
views
Script to install Oracle client 12c2 on Redhat linux 7.5
Can someone help me to provide a script which I can use to install the Oracle client as part of the server build or AMI. Below script I was able to pull out from the guide in the Oracle site. But ...
CommunityBot
- 1
1
vote
1
answer
275
views
How to upgrade a MySQL database using numbered scripts based on a version field
I have a database that contains a version table with one value stored in it which is the database version number.
I would like to be able to update the database version automatically using numbered ...
CommunityBot
- 1
0
votes
1
answer
59
views
How to rapidly compare definitions of many tables of two PostgreSQL databases?
I have two PostgreSQL databases, the one for producing, and the other for testing.
Both of them have a lot of tables with same table names.
Now I need to upgrade the producing system to the new ...
- 4,386
6
votes
5
answers
34k
views
What does "psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql)" actually mean?
I have a question regarding following output:
[nikola@192 ~] $ sudo su - postgres
[postgres@192 ~] $ psql dvdrental postgres
psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (...
0
votes
2
answers
7k
views
output psql results and errors to a file
I want to display the results of psql queries on a file.In the log just the name DO for the anonymous Bloc is being displayed.
The following is being executed from a shell script file :
psql --host=...
- 11
0
votes
2
answers
604
views
Loading multiple CSV files into mariadb table using LOAD DATA INFILE and shell script
I'm attempting to load CSV data into a mariadb table. MariaDB is running in a docker container and the CSV files have already been copied in the container as well. I'm already able to successfully ...
- 8,009
0
votes
1
answer
35
views
How to migrate a database from 2016 to 2014 if there are encrypted/locked objects(SPs) associated with it?
I am trying to migrate a database from 2016 to 2014 through Task-->Generate Scripts. There are some stored procedures that are locked and seems like they are encrypted as well. They are causing ...
- 984
1
vote
1
answer
494
views
Set 'timing on' for the duration of the current `psql` session using command-line options
What is the equivalent of \timing on command in psql, using the psql command-line options? I am aware that this will enable timing for all psql sessions:
echo '\\timing on' >> ~/.psqlrc
I am ...
- 41.3k
-1
votes
2
answers
301
views
Is there a short script to safely perform a major-release upgrade on a postgreSQL docker container?
The question specifically focuses on the assumed required upgrade of the data structures when stepping up to the next or even latest major release.
I know it usually involves dumping the data before ...
- 26
0
votes
4
answers
17k
views
How to take SQL Server databases backup using .bat script file with time stamp
I am using SQL Server Express edition, so there is no features for backup plan, I want to take databases backup using bat script, and create a scheduler to be run everyday. I have created .bat file ...
- 584
0
votes
2
answers
258
views
Command line command to read Oracle Database export Success Message In log File
We have several oracle 19c servers with daily exports. I would like to find a way to run a command to read the export log file to find the success message for the day. e.g. I would like to putty into ...
- 3,692
3
votes
2
answers
5k
views
How do I get Postgres DO $$ to work from the bash prompt?
Postgres 9.6.9 on Linux RHEL 6.10
If I surround my DO command in double-quotes and the roles in single quotes, then bash interprets the $$, and that obviously makes the command fail. So I switched ...
- 111
2
votes
3
answers
9k
views
Postgresql Backup all databases separate files
I need to create an automatic backup of all databases from PostgreSQL (running on a Windows machine).
I need to know if it is possible to get a backup of all databases but in separate files.
Using ...
- 18.3k
0
votes
1
answer
435
views
Script to get definitions of table types and Sequences
I need to get the definition of some objects in the SQL server. I got the definitions of stored procedures, functions, views and triggers from sys.sql_modules.
But, I don't know how to get that of ...
- 39.5k
2
votes
1
answer
9k
views
Run PSQL command from bash script
I would like to know how to run \du within a script, and output that to a file.
Something like the following:
# some_script.sh
psql <database connection> & \du > output_of_du.txt
I can ...
- 1,590
11
votes
4
answers
86k
views
script to show all the permissions for a table
inside my database I have a procedure called spGen2_tblIE_SchedProcess_Update.
when I run this script
select * from sys.objects
where name = 'spGen2_tblIE_SchedProcess_Update'
Inside the same ...
- 16.6k
0
votes
2
answers
59
views
How to query 30k of entries from an external data source by script
I have a SQL query which works for single checks:
SELECT trans_id from schema.table where trans_id like '%<trans_id>%'
There're might be better approaches to query for, but that's not the point.
...
- 36
1
vote
1
answer
1k
views
Executing Cassandra pod CQL queries from shell script, using shell variables with kubectl exec
I have a repetitive task that I do while testing which entails connecting to a cassandra pod and running a couple of CQL queries.
Here's the "manual" approach:
On cluster controller node, I ...
0
votes
1
answer
4k
views
Run both of "DROP DATABASE" and "CREAT DATABASE" in CLI with "-c" option of psql of Postgresql
When debugging programs, I need to frequently drop a database of Postgresql, and then re-create it again.
To convenient, I want to execute this in a single command line like following:
psql -c "...
- 56.4k
2
votes
4
answers
18k
views
SQL Server - backup / restore schema only
We have a DB on over 100 servers of which we want to backup / restore the structure of just 1 at a time to a specific UAT database. They're all called the same thing but vary in terms of schema - ...
- 3,243
0
votes
0
answers
66
views
Create dadabase with spesific encoding from shell script
I want to create a database from shell script, I add this line to my script
psql -c 'CREATE DATABASE template1 WITH OWNER = postgres ENCODING = 'LATIN1' TABLESPACE = pg_default LC_COLLATE = "...
4
votes
1
answer
3k
views
Script out XMLA for all databases
I have several SSAS databases. I only want to script out the XMLA for them on a nightly basis. This will be a second tier backup in addition to the regular backups we have.
How do I auto-generate ...
- 2,201
4
votes
2
answers
2k
views
How to set the sqlcmd.exe exit code?
The script needs to test to see if a backup table already exists and stop if it does. This is to prevent overwriting a backup already created.
The following will stop further script execution (but not ...
- 5,512
1
vote
2
answers
603
views
How to script server level objects in sql server?
I regularly do sql server installations (mainly sql server 2016), in virtual machines or otherwise, and I have a bunch of things like permissions, and linked servers and endpoints that I have mostly ...
- 70.5k