Questions tagged [scripting]
For questions about generating or using language scripts, for example scripting a database object definition.
354 questions
0
votes
1
answer
410
views
Is wrapping only possible for package bodies?
I am using oracle 11gR2 database in which I was trying to wrap my scripts. I read the documents and tried to wrap a sql script which contains a create table statement. I used below commands:
wrap ...
- 152
14
votes
4
answers
15k
views
Scripting SQLite with dot commands
Is it possible to write scripts that contain SQLite dot commands ( vis. .read file.sql; .separator ,; .import file.csv; )?
I'm building and repeatedly rebuilding an SQLite database and need to type ...
- 341
1
vote
1
answer
2k
views
Existing DB2 connection does not work inside a script
I connected to the DB2 instance, and I can successfully verify that the connection exists:
db2inst1@hostname:/tmp$ db2 connect
Database Connection Information
Database server = DB2/...
1
vote
1
answer
129
views
Script not working after adding try and catch
I have a working script that I modified to work with SQLCMD since I need to invoke this script remotely.
Here is my script:
:ON ERROR EXIT
USE [master]
GO
BEGIN TRY
/****** Declare Database name **...
- 113
1
vote
1
answer
331
views
Automation approach to executing Shell scripts and performing checks on an Oracle DB
I am in the process of producing an automation framework that will:
Check Oracle tables for entries, such as IDs and status numbers
Log onto a UNIX box via Putty and switch to a directory where the ...
- 23
1
vote
1
answer
7k
views
How to preserve sqlplus session in shell script
I am trying to insert queries from a sql file in shell script using sqlplus.
Now, some queries can fail because of incorrect schema.
So I want to commit all transactions only if all queries succeed.
...
- 113
1
vote
1
answer
257
views
How to manipulate bytea data when using backup script as a template
I have a project with a web app that has many configurable options, with the configurations stored in a PostgreSQL database. Most fields are text or ints, but there are a couple configurations stored ...
- 13
1
vote
3
answers
861
views
Is it possible to have a script that double quotes a view or proc
I apparently cannot figure out the key words to google search for what I want, so I need help finding out how to do this. I feel like this may be a duplicate, but I just cannot find this anywhere else....
- 617
1
vote
1
answer
507
views
SQL script Scope in Microsoft SQL Server Management Studio - runs against active DB or all DB's?
I'm not very familiar with SQL Server, more familiar with MySQL.
I'm used to seeing everything in code, whereas SQL Server, some things are done with buttons. In MySQL, I'd execute USE ...
- 1,240
2
votes
1
answer
3k
views
Running queries one after the other in psql/scripting environment?
I am a little bit concerned about the possibility of queries running concurrently. I want to run a set of queries at worst as soon as the previous is ready through psql (PostgreSQL) and a shell script....
- 233
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 ...
- 51
0
votes
1
answer
6k
views
SQL Mail Failing To Send
SQL Sever 2016 with the latest updates. Running on WS2012R2. Following error message is received:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail ...
- 47
6
votes
1
answer
3k
views
Was 'ActiveX Scripting' step type secretly removed from SQL Server 2016?
Migrating our database from SQL Server 2012 to 2016, we can't recreate an SQL Agent job that involves an ActiveX Scripting step written in VBScript.
The error message suggests that 'ACTIVESCRIPTING' ...
- 1,353
2
votes
1
answer
3k
views
Import all csv files into a table oracle
Trying to import all the files into a table but I receive the following error.
#!/bin/bash
. ~/.bash_profile
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/client_1
sqlldr USERID=user/pass@mydb ...
- 21
0
votes
1
answer
1k
views
Credentials for mysql fail for mysqldump in bash script [closed]
I am having a very strange problem in a script to back up MySQL schemas. The script creates a list of schemas and then runs the mysqldump command for each of them. The credentials are stored in a ...
- 101
0
votes
0
answers
112
views
SQL Server 2008 R2 warning when tried to execute the script of size approx 500MB [duplicate]
When I tried executing a SQL script, which was of 500MB approx. Management Studio throws an error
Insufficient memory to continue the execution of the program
My system memory is 8GB and only 3GB ...
- 908
10
votes
2
answers
9k
views
Variable inside another variable in SQLCMD mode
Trying to tidy up my SQL script with SQLCMD mode and I ran into an issue:
:setvar db_suffix "some_suffix"
:setvar some_db "some_db_$(db_suffix)"
print 'some_db: $(some_db)'
The output of that is:
...
- 223
3
votes
1
answer
4k
views
How to clone a table schema (including indices) in SQL Server?
Is there a command that will completely clone the schema of a table? This would include all constraints, foreign keys, and indices on the table.
In SSMS the Script Table as | Create To menu command ...
- 723
1
vote
1
answer
143
views
Batch altering profiles of users
I currently have a list of databases where the profiles of users are out of date. A lot are listed under default where we want them to be either USR_PROFILE or DBA_PROFILE.
I have a list of all the ...
- 15
1
vote
2
answers
239
views
Bundle SQL scripts for Oracle
I have bunch of sql-scripts used to create various tables, procedures, functions and views. Everything is in separate files; is there a good way to bundle these and run each file in a specific order? ...
- 137
0
votes
1
answer
74
views
Access Send Email. Loop Not Building Table
I’m stumped on this one. I have an action item list on a fairly large Project Management DB. This list is datasheet that is embedded as a subform on a PopUp form from the Main Menu. On the Action Item ...
- 88
2
votes
3
answers
956
views
What is the point of the Script Date in a stored procedure script generated by SSMS?
When I generate a stored procedure script, SSMS additionally generates a comment containing the name of the object and a Script Date timestamp:
The timestamp is just the current time at the moment ...
- 21
3
votes
3
answers
5k
views
Use sp_Blitz to gather information on all servers in a centralized server/database
I am using sp_Blitz, and want to use it to execute the checks on all of my SQL Servers and have it write the results to a single table.
Do I need to create linked servers on the central instance to ...
- 511
0
votes
1
answer
114
views
Insert script adds N before each varchar data
I exported results from a query in order to import it into a table on another DB. I chose the xlsx output format and verified that the data had gotten written to the excel file. While trying to import ...
-2
votes
1
answer
716
views
Generate testing data with PL/SQL
I need to fill a partitioned table with 100 000 of records generated by PL/SQL.
I am thinking about creating a procedure with a loop that will iterate 100000 times. To generate the strings I want to ...
- 31
2
votes
3
answers
9k
views
Use psql --set variable in a function
I want to create a script that will create some custom named schema and within it some tables and functions. Like this:
The example.sh file:
#!/bin/bash
# this is the only place I want to set the ...
- 143
0
votes
2
answers
8k
views
Can't read password from config using --defaults-extra-file in MySQL
I am trying to backup all databases automatically using a batch script. The password is read from a config file called config.cnf. Below is the batch script:
@echo off
C:\MySQL\bin\mysqldump.exe --...
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 ...
- 165
1
vote
2
answers
160
views
SQL 2005 find duplicate indexes
I've created the following script to pull up duplicate indexes on my DB but I need to find a way to implement it across all the databases on the SQL server - any suggestions?`
with indexcols as
(
...
- 35
3
votes
0
answers
16k
views
DROP DATABASE IF EXISTS CREATE DATABASE IF NOT EXISTS USE in PostgreSQL
I always was used following header in files that defined database in MySql:
DROP DATABASE IF EXISTS base;
CREATE DATABASE IF NOT EXISTS base;
USE base;
In PostgreSQL there are the following ...
- 209
1
vote
0
answers
244
views
How to investigate non-specific Generate and Publish Scripts Wizard errors
I am trying to use the Generate and Publish Scripts wizard in SSMS 2012 against a 2012 SQL instance and am getting errors. Trying to generate a schema-only script of the tables and schemas. I allowed ...
- 314
2
votes
2
answers
73
views
Syntax check for creating logins in different SQL versions
We are trying to write a script to do the following task:
Create a new login and grant datareader to the master database on all SQL Server 2012 and 2014.
The script we have did what we want but for ...
- 45
3
votes
1
answer
964
views
ORA-12154: TNS: Could not resolve the connect identifier specified
I am running a script that "creates/replaces" a procedure in a database and then runs the procedure. The code is as below:
WHENEVER SQLERROR EXIT
UNDEFINE g_cust_pwd
UNDEFINE g_db_name
SPOOL '...
- 31
3
votes
0
answers
1k
views
execute R script using a postgresql trigger:
This is my first question here, so excuse me if it does not look well organized:
I want to call a R script from a trigger (postgresql) so this script will be executed after an INSERT on my table (...
- 31
4
votes
1
answer
1k
views
Is this way of MySQL shutdown via bat file sufficient to minimise corruption?
I had memory issues with using 32bit Xampp so I have installed 64bit components myself as a new server.
Although, I want to make sure I am stopping the MySQL database server properly.
Is this a ...
1
vote
1
answer
834
views
MySQL root login email notification
Is there a script that I can run to get an email when someone else other than me logs in as root?
I'm new to MySQL. These are my contents of my.cnf:
[mysqld]
*Remove leading # and set to the amount ...
- 31
2
votes
1
answer
118
views
SQL 2012: Executed generated DB script in SQL 2008 R2
I generated a script (using the wizard from SSMS 2012) to produce a SQL 2008 R2 compatible database script. I ran this script in a SSMS 2008 R2 instance and i got a bunch (30) of these possibly "...
- 153
2
votes
1
answer
76
views
How can I optimize/make safe a script that deletes lots of data?
I'm in the process of trying to develop a script that has to delete accidentally duplicated data (due to a bug in some code), from a client's production database.
It's quite a lot of data that I need ...
- 299
1
vote
2
answers
6k
views
Compile a PLSQL package code that was passed to an sqlplus session via a shell script?
I have two files: (1) and (2)
(1) A Shell script - wrapper.sh
#!/bin/sh
echo "start of shell script"
FILE_NAME="XX_PKGb.sql"
sqlplus user/password @sql_deploy.sql $FILE_NAME
echo "end of shell ...
Vineet
1
vote
0
answers
217
views
Loop a script for all DBs in SQL Server
I am trying to run Kendra's script for all DBs but running into issues.I have tried below.
http://www.littlekendra.com/2016/05/05/how-to-script-out-indexes-from-sql-server/
DECLARE @SQLCmd VARCHAR(...
- 1,928
1
vote
0
answers
266
views
SQL Server and Network considerations to transfer backup file to a shared folder
I'm facing with a situation where backups are being transferred manually to a Shared Folder and my goal is to optimize all of this.
What I have to deal with is:
8 Windows 2008 R2 Servers
2 Windows ...
- 155
1
vote
1
answer
1k
views
how to execute unix commands within nzsql?
I'm converting a script from Teradata to Netezza, and I'm stuck at the portion where BTEQ uses the functionality of the OS to check if a file contains any data. If it does, it is deleted. I want to ...
- 11
1
vote
1
answer
3k
views
Oracle csv line size more than 32767
How to generate csv in oracle if it's query result return more than 32767 character per line in linux shell script
- 31
1
vote
1
answer
428
views
PostgreSQL - cannot create RULE from stored procedure
I'm trying to automate boring database work. I would like to have one function which creates views, rules, etc so I don't need to run corresponding SQL queries from file. Currently I faced an issue ...
- 131
6
votes
1
answer
16k
views
Grant sysadmin permissions to 'NT AUTHORITY\SYSTEM'
For SQL Server 2012 and above, as What's New in SQL Server Installation states:
BUILTIN\administrators and Local System (NT AUTHORITY\SYSTEM) are not automatically provisioned in the sysadmin fixed ...
- 163
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 ...
- 223
0
votes
2
answers
858
views
sp_configure: Preserve values of 'xp_cmdshell' and 'Show Advanced Options'
I dont want to disturb the server values of xp_CMDShell & Show Advanced options. The criteria is to use SQL to run a batch file.
This is the script i am trying to solve it with:
DECLARE @...
- 45
1
vote
1
answer
2k
views
Powershell: Does System.Data.SqlClient.SqlDataAdapter usange in powershell need some ADO.Net installation?
In PowerShell, goal is to query some data from Sql Server and put that into a preformatted Excel file. Towards that, the following PowerShell script snippet, throws an error saying "assemblies are ...
- 707
2
votes
0
answers
937
views
How do I create an automated SQL Server restore script for multiple backups? [duplicate]
I basically have 6 daily SQL Server backups (.bak) that need a restore everyday. I want to automate this process by having the SQL Agent run the script daily. I have the following restore script ...
- 21
1
vote
2
answers
2k
views
oracle rman connecting to a database in not mount state
When connected to oracle database through rman directly from the terminal, everything works fine, but when the same connection string is executed through a shell script rman connects to the database ...
