Questions tagged [scripting]
For questions about generating or using language scripts, for example scripting a database object definition.
344
questions
-2
votes
1
answer
726
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
0
answers
73
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 ...
- 181
1
vote
2
answers
3k
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?
...
- 213
5
votes
2
answers
1k
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, ...
- 15.4k
0
votes
1
answer
566
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
4
answers
5k
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
**...
- 17
3
votes
0
answers
2k
views
Generate scripts wizard hangs when loading database information [closed]
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 ...
- 175
-1
votes
1
answer
371
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
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 ...
- 1
0
votes
3
answers
3k
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 (...
3
votes
2
answers
3k
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 ...
- 15.4k
1
vote
2
answers
388
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 ...
- 253
3
votes
2
answers
1k
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
...
- 151
2
votes
1
answer
738
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 ...
- 47
0
votes
2
answers
37
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 ...
- 47
1
vote
1
answer
108
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 ...
- 165
2
votes
1
answer
810
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:
...
- 205
1
vote
1
answer
110
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 ...
- 1,213
-3
votes
2
answers
2k
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 ...
1
vote
0
answers
2k
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 ...
- 107
0
votes
2
answers
97
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 ...
- 195
4
votes
2
answers
4k
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 #...
- 15.4k
2
votes
0
answers
177
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 ...
- 15.4k
4
votes
1
answer
2k
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 ...
- 43
2
votes
4
answers
4k
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 ...
- 21
0
votes
1
answer
227
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 ...
- 15.4k
0
votes
0
answers
227
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 ...
- 109
2
votes
2
answers
2k
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 ...
- 7,248
0
votes
1
answer
36
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 ...
- 1
2
votes
1
answer
1k
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 ...
- 15.4k
2
votes
3
answers
2k
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/...
user180724
1
vote
1
answer
10k
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 ...
2
votes
1
answer
733
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 ...
- 15.4k
2
votes
1
answer
2k
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 [...
- 15.4k
1
vote
1
answer
443
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 ...
- 15.4k
1
vote
2
answers
494
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(...
- 15.4k
2
votes
2
answers
251
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 ...
- 15.4k
2
votes
1
answer
38
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 &...
- 21
3
votes
2
answers
544
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 @...
- 15.4k
1
vote
2
answers
2k
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 - ...
- 113
1
vote
1
answer
3k
views
powershell Write-Output format [closed]
I am finding what seems to be an inconsistency to the way a Write-Output line (which prints out a variable) should be formatted. I can't find any information on this, which makes me guess that I am ...
- 113
2
votes
1
answer
63
views
How can I optimize this MYSQL Script For many Records?
I have This script by Helping @lukstorms ,
-- DROP TABLE IF EXISTS test_words;
CREATE TABLE IF NOT EXISTS test_words (
id int unsigned NOT NULL PRIMARY KEY,
words text NOT NULL
);
-- DROP TABLE ...
- 21
0
votes
1
answer
130
views
Can I ignore queries about extendedproperty?
I want to reuse the structure of a database in a new project. So using SSMS I use Generate Script and in settings I set to generate schema only.
In the result there are too many queries about ...
- 231
1
vote
0
answers
38
views
Checking if schema name is included in every sentence
I got some oracle database scripts for creating the whole database of an application, including sentences for creating tables, sequences, synonyms, inserts and updates of data, etc
I need to ensure ...
- 111
0
votes
1
answer
1k
views
Capturing mongo replica set state from mongo shell
I am trying to capture mongo replica set state from mongo shell. E.g.
[root@server ~]$ mongo
MongoDB shell version v3.6.8
connecting to: mongodb://127.0.0.1:27017
MongoDB server version: 3.6.8
rs0:...
- 989
0
votes
2
answers
2k
views
How to script all the service broker elements for each database it is on, in the current server?
I have service broker running on different servers.
Just recently I had to rebuild one of the test servers and it had a complete service broker solution running there.
For the next time I need to ...
- 15.4k
0
votes
1
answer
2k
views
bind variable and shell scripting with sqlplus
I wish to execute DMLs using bind variable with shell scripts.
For example, something like:
#!/bin/bash
SH_NUM=10
sqlplus -S test_user/test_pass <<EOD
var a number;
a:=${SH_NUM}
insert into ...
- 473
1
vote
0
answers
36
views
Extracting AD subgroups information [duplicate]
I need to extract AD groups and all of it's subgroup into an excel sheet.Basically the script should loop though each of the AD group and get their subgroup.
AD_Group Subgroup
---------- -----...
- 11
0
votes
1
answer
62
views
How to format the output of this query into year...second?
I have this query that given @secs a number of seconds, it returns a table with year,months,weeks,days,hours,minutes and seconds.
take for example:
declare @secs int
select @secs = 19475298
...
- 15.4k
3
votes
3
answers
265
views
How to speed up rebuilding a database from scratch/scripts
Our Database exists in many different scripts within our source control. i.E. every table has it's own scripts for creating the table, the indexes and so on.
When needed, we can rebuild the entire ...
- 31