Hot answers tagged

11

Just to get these out of the way: Technically speaking, both of these options are "dynamic" / ad hoc queries that are not parsed / validated until they are submitted. And both are susceptible to SQL Injection since they are not parameterized (though with the SQLCMD scripts, if you are passing in a variable from a CMD script then you do have an opportunity ...


5

A simple test method for SQL connectivity is to create an empty text file, with a file extension of "UDL". You can create it in Notepad. And it can have any name. I use "TestSQL.UDL" Save it on the desktop of a windows PC and double click it. A "Data Link Properties" dialog box will pop up where you can enter the IP address of the SQL server and also a ...


4

You can connect using the DAC (Dedicated Administrator Connection), and pull the password column from sys.sysowners. First, connect using: ADMIN:Server\Instance Then: SELECT password_hash = [password] FROM sys.sysowners WHERE name = N'MyAppRole'; This view is only visible when using the DAC, and the column is not exposed in the parent view that is ...


4

No, absolutely impossible. Each sqlcmd invocation is a different session and transaction cannot span sessions. Your use of a transaction on the script that invokes the sqlcmd is completely useless, it has nothing to do with the scripts being run.


4

Add -e option: · --execute=statement, -e statement Execute the statement and quit. The default output format is like that produced with --batch. See Section 4.2.4, “Using Options on the Command Line”, for some examples. With this option, mysql does not use the history file. mysql -uroot -pPassword1 -e "select column_name from ...


4

There are a number of system command equivalents available: ls() mkdir() hostname() pwd() sleep() (not a system command, but similar functionality) It should be noted that these are all executed on the client (local) machine, not the server. There is no general construct for executing arbitrary system commands that I know of in the shell. To be honest, ...


3

It's worth noting that if you pass in a replicaset string into mongodump it defaults to reading from a secondary from the set (note: in 3.0.5+ this is not the case if you connect to a mongos in a sharded cluster). If your replica set name was repl1 it would be something like: ./mongodump --host repl1/example-1.domain:27200,example-2.domain:27200 For ...


3

This is really a Unix/Linux question, but it does relate to Oracle, so I'll give an answer. In Unix, the system makes great use of what it calls "spawning" (or "giving birth"). When you run a command, a new process is created which takes all of its information from the parent. Essentially, it's a copy of the parent - it's a new bash shell, which then sets ...


3

I believe you can refer to ola.hallengren. SQL server Backup scripts. As stated on how to use for SQL server Express: SQL Server Express has no SQL Server Agent. Therefore, the execution of the stored procedures must be scheduled by using cmd files and Windows Scheduled Tasks. Follow these steps. Download MaintenanceSolution.sql. Execute ...


2

Pre-requisites This article assumes knowledge of SQL Server. In SQL Server the required database has various tables with data. Here we use the "Address-Book" database. STEP 1 Choose the database to create a database script in the "Object Explorer" section of SQL Server. And choose the following option in sequence for generating a script. STEP 2 We ...


2

It mostly depends on your needs, because it is a common fact that standalone tools feature more options and are faster. I couldn't find the feature comparison list between Visual Studio and Redgate, but I ran into a post that compares the performance of 2 SQL schema comparison tools - ApexSQL Diff and Redgate SQL Compare: ...


2

not "ready to use" solution, but may be is what You need: http://www.sqlparser.com description as I explain in comment, this is framework, it not ready to use solution, but it has very good set of code examples, as well as live-demo. Live demo USE [Audit] GO ALTER PROCEDURE [dbo].[ReportData3] -- Add the parameters for the stored procedure here ...


2

If the queries are in stored procedures, or you can get them into a temporary database as stored procedures then the system SP sp_depends and related catalog views will help. At the very least they will narrow the search. If the queries are in files a bit of Powershell will generate SPs in no time. If they're embedded in the application or, heaven ...


2

Why not just run each job as a seperate step? And so on and so forth


2

If you do not specify a collation when creating a table (regular or temporary or variable), then the database's default collation is used. When creating a temporary table (not table variable), it exists in tempdb, so tempdb's default collation is used. TempDB's default collation is derived from the server default since the server default was used to create ...


2

I'm not a PowerShell user, but it seems you might be looking for SQL Power Doc listed on CodePlex and licensed under the Microsoft Public License (Ms-PL), so you are free to create derivative works. It was written by Kendal Van Dyke, a well-known and highly respected SQL Server community member. From the home page: SQL Power Doc is a collection of ...


2

As you were hinted in comments above, your RBAR approach might be very inefficient. Consider the suggestions there. Also, I am not going into details about the different approaches of UPSERT, as it is a very broad topic, especially when one wants to do concurrency-safe. PostgreSQL 9.5 helps a lot in this regard. So, to your actual question: it is not ...


2

It rather depends on what the scripts are intended to do... If you can cope with the occasional dirty read or unrepeatable read issue (or they are truly simple enough that these are unlikely/impossible), also set your transaction handling to the most lenient mode possible with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. This is actually equivalent to ...


2

Converting comment to an answer. This is not my area of expertise, but you might look at Dennes Torres Simple Talk post from January 2015 for any takeaways that help you. https://www.simple-talk.com/sql/database-administration/centralize-your-database-monitoring-process/ Two subheadings of particular interest may be: Configure Data Collector through ...


2

sp_help_revlogin See: How to transfer logins and passwords between instances of SQL Server on MSDN Worked reasonably well for me this past summer.


2

I have a couple of stored procedures that I wrote and use on a regular basis. sp_SrvPermissions sp_DBPermissions They each have three outputs. Principals : A list of the database/server principals and associated info including create/drop scripts. Role membership : A list of what roles each database/server principal belongs to along with add/drop ...


2

You may want to look at this SO question I'm looking for a reliable way to verify T-SQL stored procedures. Anybody got one? which is asking essentially the same thing, with several answers. To build upon the script Alaa Awad posted... this should show the schema and database of the referenced and referencing objects. If you're using many temp tables via ...


2

O.K. lets create a nice example for you and show you how to script the index of your indexed view. first, lets create a temp table called T1 and add some meaningful data into it lets create a procedure that generates random strings first: USE TEMPDB GO CREATE PROCEDURE [dbo].[SpGenerateRandomString] @sLength tinyint = 10, @randomString varchar(50) OUTPUT ...


2

So the main thing is that you can set these up to be dynamic. You wouldn't have to log into each instance to find out the settings. It does make it easier to perform a health check and just keep track of the lot. I used it for Parsing Excel documents to SQL server. This turned out to be very simple instead of converting it to csv documents and importing from ...


1

Something like that could be achieved through dynamic sql, or via using stored procedures instead of views (for selecting) or triggers (for controlling inserts/updates).alter But you can also create the memberview to return values according to the currently logged on user, which is probably more practical: create view memberview as select Dept, Name ...


1

I agree that you could use \\\server\share\Whatever without needing to map a drive at all (so UNC path only) but the security context running the T-SQL will need appropriate permissions to the SHARE and the NTFS of the full path to ensure it can do whatever commands you are running. You could use PUSHD and POPD as: PUSHD \\server\share Command ...


1

The 'big' players don't even put the images on the same machine. Instead they have "image servers" globally distributed and optimized for pumping out readonly images. In their case, the benefits of distributed processing outweighs the occasional inconsistency (link to non-existent image; vice versa). Yet other machines handle virus checking, scaling, ...


1

;WITH theRoles (member_principal_id, role_principal_id) AS ( SELECT r.member_principal_id, r.role_principal_id FROM sys.database_role_members r UNION ALL SELECT tr.member_principal_id, rm.role_principal_id FROM sys.database_role_members rm INNER JOIN theRoles tr ON rm.member_principal_id = ...


1

You specified #!/bin/sh in your script's shabang line. This gives you a POSIX shell. Under POSIX style shells you can only specify numbers for redirection, as noted in the documentation under section 2.7 Redirection. &> is a "bashism", shown here in the bash documentation under io-redirection. This can cause undefined behavior under POSIX compliant ...


1

There is nothing in the syntax that makes this possible CREATE APPLICATION ROLE (Transact-SQL) - Books Online You can get around this with creating the approle in a template database and have the script restore that. One implementation (Dynamics NAV) creates an approle in code and stores the encrypted application role password in a table in the ...



Only top voted, non community-wiki answers of a minimum length are eligible