Questions tagged [scripting]

For questions about generating or using language scripts, for example scripting a database object definition.

Filter by
Sorted by
Tagged with
0
votes
0answers
29 views

MS SQL Server 2012 - How To Find Last Executed Queries

I have 120+ databases and I want to find last executed queries (actually I want to find last executed queries and last executed select queries) in last 10 days. I have below scripts for this job: 1-) ...
2
votes
1answer
27 views

How to change user running audits mssql

I've been tasked with setting up our SQL server audits - despite having never done this before. So please forgive a really basic question. I setup a specific user, sqlAudits, that I would like to own ...
0
votes
1answer
15 views

PostgreSQL - How to list all foreign key link to Primary key of a table

I have a table res_users with primary key id I would like to list all the foreign key link to id from all the tables in my database.
0
votes
1answer
31 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=...
0
votes
1answer
19 views

Shell script - psql run multiple statements from a variable one by one in one transaction

Im preparing a shell script to generate analyze command and all the commands will go to a variable. echo $analyze_query analyze tbl1; analyze tbl2; analyze tbl3; Now I want to run one by one, and ...
1
vote
1answer
29 views

how to script out proxy accounts?

I have servers where I use proxy accounts to run ETL processes or even to backup SSAS databases using powershell through a job. I can see what AD Accounts are related to my proxies: use msdb go ...
0
votes
1answer
25 views

v$ tables not recognized in a sql statement of a shell script

I have .sh script with this block: sqlplus -S / as sysdba <<EOFILE set feedback off set lines 2000 set pages 20000 col db_size for a15 alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:...
0
votes
1answer
26 views

script to find all bigint that can be converted to int fields in postgresql performance

I want to compact a postgresql 11 db and my idea is to convert lots of bigint to int data types if and only if there is a reasonable lag to the limit of int data type considering the data inside. So, ...
-2
votes
1answer
41 views

How to resolve error “SQL0104N” when running “db2 prune logfile prior to” command?

I wrote an script to delete transaction log files in db2. I need to delete all the files prior to the active log file.I wrote this code as .sh file db2 connect to DWHDB db2 get db cfg | grep '/...
0
votes
0answers
52 views

Loading 260GB worth of CSV into MySQL in Linux

I'm bulk loading a few thousand CSVs totaling ~260GB and ~4.5 billion rows to MySQL using mysqlimport calls on each CSV executed within a bash script. The MySQL installation and the data itself are ...
1
vote
2answers
70 views

How to pass Oracle Wallet auth to sh script?

I am running an Oracle DB update script that requires password. I have Oracle wallet installed. How to pass Oracle wallet to sh script so that I don't have to enter password when running the script? ...
4
votes
0answers
63 views

how to remove all permissions in a database?

every time I need to either restore a database or reinitialize a replication publication I first (on the restored to be or subscription database) I script all the permissions, triggers, indexes, ...
0
votes
0answers
17 views

Is it possible to replace trigger with a job with postgres?

I am using Postgres XL 10R1.1, even tough it utilises PostgreSQL 10.6 .. It is still impossible to use triggers. I want to use the inherence partitioning in order to partition my data using my some ...
0
votes
1answer
85 views

MySQL ERROR 1064 (42000) insert via bash script (RHEL)

MySQL Server: 8.0.16 Commercial OS: RHEL 7.5 I have written a simple script, to insert certain information into a database table. Following is my script # cat ~mysqldba/bin/connections.sh #!/...
0
votes
4answers
162 views

oracle oraenv script bash

I want to do same commande shell CLi into a script Command shell without script (CLI) su - oracle export ORAENV_ASK=NO export ORACLE_SID=HRPRD . oraenv env | grep ora output: USER=oracle **...
2
votes
0answers
56 views

Generate scripts wizard hangs when loading database information

I am trying to script out a database from an instance of SQL Server 2017, so I can load it into an instance of SQL Server 2014. I right-clicked on the database, chose Tasks -> Generate Scripts, and ...
0
votes
1answer
110 views

Syntax Error in Create Table Statement

I'm using Oracle 12c, I want to create a table with partition by year and subpartition by month like this. create table par.Log_his ( ID NUMBER(20) not null, Year number(4,0), -- ...
0
votes
1answer
277 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 ...
0
votes
3answers
67 views

How do you generate a build script for all views [duplicate]

I would like to create a build script that can run through and recreate all views (and stored procedures too, but one step at a time). I am trying to create a baseline of all of our user databases (...
0
votes
0answers
38 views

How to generate queries for Star Schema Benchmark SSB?

After generating 1 Gigabit of the Star Schema Benchmark available via https://github.com/Kyligence/ssb-kylin How to generate for exemple 1000 sql query to execute them on postgreSQL and sql server ...
2
votes
2answers
313 views

how to add data compression to a primary key of a table?

We work with tables with over 300GB of size, a small percentage of them tables use sparse columns although that doesn't always agree with CPU utilization as this question shows. For the tables that ...
1
vote
2answers
79 views

Find jobs that succeeded, but had steps that failed

Is it possible to find jobs that succeeded, but that had steps that failed like the desired output shows? I'm new to this side of development and I'm not sure about all of the system tables, etc. If ...
3
votes
2answers
265 views

Suppress database change message in SQLCMD.exe

I am writing a script generation script. The script is generating list of DDL statments. The util_generator.sql generation starts with database connection: USE [TARGET_DATABASE] GO ... Many queries ...
2
votes
1answer
146 views

How to insert multiple IP addresses in a pg_hba.conf file via unix shell script?

I need to write some IP addresses to the pg_hba.conf file of my Postgresql, in order to allow remote access from those IP to my machine. I have the following code in shell script to do that for a ...
0
votes
2answers
24 views

Fetching wrong results on comparing a variable with 0 in unix

I have the following code in unix #!/bin/bash user=$1 username=$user'%' DB_name=$2 userpwd=$4 export PGPASSWORD=$3 psql -h localhost -U postgres -d $DB_name -At -c "SELECT COUNT(*) AS ...
1
vote
1answer
31 views

Access .sql script files on other partition or hdd [closed]

I'm running a Linux Mint 19.1 - Tessa OS, which for you who might not know it is based on Ubuntu 18.04 - Bionic Beaver. On this I have installed postgresql 11 which work well. There is one problem ...
1
vote
1answer
89 views

How to find out if an index is aligned

I'm struggling to find any confirmation within sql server or sql management studio that my index is in fact aligned with my partition. I'm creating a primary key constraint on a partitioned table: ...
1
vote
1answer
43 views

How to create a job that uses Powershell script? [duplicate]

I have a powershell script which deletes old backups from NAS storage. I have already tested it using Windows Powershell and it works but I couldn't perform the same thing in SQL Server. I have ...
-3
votes
2answers
114 views

Need a shell script that can run a oracle query and produce output in .csv format

Can you please help- need a shell script that will have a oracle query inside it and produce a .csv format output in specific location, that script should be a scheduled in a cron job which runs, the ...
0
votes
0answers
530 views

Show Collections in MongoDB throwing

I am connecting to MongoDB hosted on EC2 instance of AWS. I am connecting through ssh command. Upon entering the command "show collections", I am getting uncaught exceptions. Please see the sequence ...
0
votes
2answers
68 views

TSQL script returning wrong result

I'm trying to find out if a particular index name "Test" exists in all databases using below code. The result I'm getting is always "NOT EXIST" for all databases in an instance. However, the index ...
4
votes
2answers
886 views

how to script out the user defined table types?

I can Get name and definition of all table types using either of the following scripts: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED IF OBJECT_ID('TEMPDB..#RADHE') IS NOT NULL DROP TABLE #...
2
votes
0answers
38 views

the mystery of the table showing up in the subscription, but not in the publication

TL;DR: This question refer to sql server 2016, transactional replication I've got a table that shows up in the subscription but not in the publication. Here are the details: In one of my servers ...
4
votes
1answer
688 views

Scripting a Maintenance Plan in SQL Server Express

How would I run each aspect of Ola Halengren's sql maintenance plan on SQL Express? It's my understanding that each job should be run in a specific order. I'm sure that a .bat or cmd file can do ...
2
votes
4answers
751 views

Copy table from one server to another including keys and indexes

I have been working on a small project for about two weeks. We want to consolidate a few tables from several remote server databases in order to run queries and reports on separate from the live ...
0
votes
1answer
88 views

how to script server side permissions - specifically EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER?

I could not script the following server side permissions: GRANT VIEW DEFINITION ON LOGIN::[site_helpdesk] TO [Radhe] but thanks to the question below I found a solution to that: How to script out ...
0
votes
0answers
54 views

Using SQL script in Toad, how do I suppress the echo of the SQL statements

I'm using a script (Toad, Oracle) so that I can use variables and enter two values that get repeated in multiple SQL statements. That's working great, but the SQL statements echo into the console ...
1
vote
2answers
200 views

Handling data encoding issues while loading data to SQL from Script (Notepad++)

I'm pretty sure this is not a SQL Server problem. I already asked a question HERE with an awesome explanation, BUT I still can't explain to the guys where I work that it has nothing to do with SQL ...
0
votes
1answer
33 views

How to get my result from Procedure

How can i get my out parameters from my procedure. My stl_mbc_cdr table has over 100 records and i cant see my results. declare call_charge VARCHAR(10); MSG_OUT VARCHAR(50); BEGIN FOR ...
2
votes
1answer
208 views

How to automate the changes of parameters in ssis package run by a job?

I have a job that runs a package with specific parameters as you can see on the picture below: I want to set up those parameters (the 2 dates in yellow) to 1900-01-01 instead of 2019-02-27. I need ...
2
votes
2answers
346 views

Moving all Heap Tables to another Filegroup in SQL Server

In SQL Server 2017, I want to move ALL Heaps in a DB to a secondary filegroup. What's the easiest way to do this? This question is only for Nonclustered Indexes. https://stackoverflow.com/questions/...
0
votes
2answers
1k views

PostgreSQL everyday trigger?

I want to add a function like trigger to my Postgres database. The problem is that this function should executed one time each day automatically and change values in the specified table. Is it ...
1
vote
1answer
206 views

Only SQL Server Enterprise Edition supports compression - how to work around that?

I have a script that needs to create a temp table and in that temp table I create an index. I noticed in some of my servers, the script would go on forever, and when checking from a different session ...
2
votes
1answer
566 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
vote
1answer
202 views

passing the parameters of sp_executesql to sp_ineachdb or sp_foreachdb

I am often in need to run the sp_executesql in the context of different databases I would like to add the same parameters as the sp_executesql into the sp_ineachdb But when I look at this: -- Syntax ...
1
vote
2answers
199 views

sys.dm_db_stats_properties seems to be misbehaving for a small table - how to get the number of records of a table in a different way?

I have this table in one of my databases: IF OBJECT_ID('[dbo].[repl_Gender_Type]') IS NOT NULL DROP TABLE [dbo].[repl_Gender_Type] GO CREATE TABLE [dbo].[repl_Gender_Type] ( [Gender_TypeID] CHAR(...
1
vote
2answers
108 views

is it possible to trim the table output for a specific job?

I have a routine to save the logins and apply them on a different server , and I run this routine from a job that I have scheduled to run everyday. On that job I have specified these 3 things: Log ...
2
votes
1answer
34 views

Overlap of group of scripts

I have a group of scripts set to run in a certain order, but sometimes I find the group running long and overlapping into the next group run. Is there a way to prevent that? group.sh: script1.sh &...
2
votes
2answers
250 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 @...
1
vote
2answers
674 views

Run shell script on Oracle 11g

I have an oracle database: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production "CORE 11.2.0.1.0 Production" TNS for Linux: Version 11.2.0.1.0 - ...

1
2 3 4 5
7