Questions tagged [scripting]

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

Filter by
Sorted by
Tagged with
2
votes
3answers
56 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
28 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
28 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
58 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
28 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
36 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
80 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
44 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
71 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 ...
1
vote
1answer
91 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
56 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
83 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
65 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
33 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
145 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
64 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 - ...
0
votes
1answer
133 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 ...
2
votes
1answer
39 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 ...
0
votes
1answer
23 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 ...
1
vote
0answers
34 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 ...
0
votes
1answer
116 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:...
0
votes
2answers
191 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 ...
0
votes
1answer
417 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 ...
1
vote
0answers
20 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 ---------- -----...
0
votes
1answer
47 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 ...
3
votes
3answers
99 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 ...
0
votes
1answer
59 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 ...
3
votes
3answers
74 views

Script to identify which AD accounts are no longer active [closed]

Trying to get a script that will identify which AD accounts/Windows authentication on a SQL Server are no longer active. Say perhaps an employee was released and the login would be removed from AD, ...
1
vote
1answer
113 views

how to add an extended events tracking session to SSAS?

I would prefer to have a script to achieve this, but as I am not so familiar with SSAS I am using the wizard to create an extended events session in SSAS. The way I am doing it is right clicking on ...
4
votes
1answer
860 views

What are the rules for using COLLATE in a query?

I am putting a script together, that gives me the permissions on database objects. SELECT permission_order=710 ,permission_type = 'Object Level Permissions' ,db = db_name(), login_=...
1
vote
1answer
60 views

is there a way to improve this query by not re calculating functions? cross apply?

when I set up replication I like to take a count of each table in the publisher, then I go to the subscriber I do the same thing in order to compare both pictures. this is the picture from the ...
2
votes
1answer
633 views

How to script out the grant view on login permission?

I create the following login without any permissions. USE [master] GO IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'Radhe') CREATE LOGIN [Radhe] WITH PASSWORD=N'...
1
vote
1answer
119 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 ...
0
votes
1answer
58 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 ...
-2
votes
1answer
30 views

How to write a shell script to disable the database accounts from a list of user IDs

I am new to Unix scripting...I have a list of userids for terminated employees. I want to write a script that will check these userids against my databases and disable/remove the user accounts ...
0
votes
1answer
38 views

How to get a SQL value literal for arbitrary type in PostgreSQL?

I want to be able to get a valid SQL literal for any type and value. If the type is text, varchar, etc... and the value is abc I want to get 'abc'. If the type is timestamp I want quoted and ...
6
votes
1answer
1k 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. Updating existing references that insert/update is difficult but doable, but ensuring future code/...
2
votes
1answer
623 views

I want Powershell's invoke-sqlcmd to ignore SQL execution errors

I've written a Powershell script that scripts out the appropriate SQL scripts for all databases on a server as well as creating BCP files for the contents of each. This is to allow the restoration of ...
7
votes
2answers
1k views

How to back up an entire database to text files

If it’s stupid and it works, it’s still stupid. The hullabaloo about GDPR got me thinking - if you had to go into your backups and modify/delete data, how would you do it? Cracking open a .bak file ...
4
votes
1answer
354 views

Breaking up insert statements with GO [closed]

I'm trying to build off of this answer How do I run a large script with many inserts without running out of memory? and break up a query into smaller ones with BEGIN TRANSACTION and then GO's ...
-1
votes
1answer
34 views

Can scripting be done on DB2 Connect instance?

I need to get the number of connections passing through connect server for 1 week. Can we write a script to get the details? Can we only pull the data manually? Is there any way to script it? I am ...
2
votes
2answers
463 views

SQL Server Reporting Services: Generate Scripts Programmatically

I'm looking to export SSRS reports from SSRS 2005 into SSRS 2017. I have around 100 reports to move but in total there are around 500 subscriptions across all of these reports. The Script to file ...
1
vote
2answers
1k views

script all triggers in a database

I have a few databases involved in replication as subscribers and they have triggers and are used by a different set of applications. Every time I need to reinitialise those database I need to script ...
5
votes
1answer
997 views

Sqlcmd: Invalid argument - when trying to pass a variable to sqlcmd

I'm trying to use a DOS command prompt on Server 2012R2 to run a batch with the following in it. I want to run a SQL command (sqlcmd) and return the results to the command window. This is what I'm ...
1
vote
1answer
67 views

How to make performance tests for DML

I created two multi-column indexes on a big table (~2,5 millions rows 370Mb and 621Mb with indexes). SELECT queries are very fast. Know I want to check if we have a regression for INSERT/UPDATE ...
0
votes
2answers
896 views

MySQL - Why is my BASH Script insert/update query not working correctly? [closed]

Background: I have a MySQL table that stores ip addresses for a very large amount of hosts. It's a simple table (host xyz -> ip1, ip2, ip3). These hosts will write a flat file with it's ip addresses ...
2
votes
1answer
2k views

ERROR: “sql ” is not a known variable

This must one of the dumbest questions I've ever asked here, but there must something really sick hiding in my SQL script that is blocking it from running. I'm invoking the cobertura.sql file using ...
2
votes
1answer
1k 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 ...
1
vote
1answer
63 views

Parsing a manifest.xml of a table schema to script?

I have a manifest.xml sent to me by a vendor that accompanies some extracts they are also sending. I assume there should be a way to parse this into an actual table structure (or a script to create a ...
0
votes
1answer
593 views

Restoring multiple files to multiple database with pg_restore

I have multiple dump files and want to restore them with single command. something like this pg_restore -h localhost -p 5432 -U postgres -d gl_election gl_world gl_tourism -c "gl_election" ...