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

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

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 CMS Configure Collection Sets ...


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, ...


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

You don't have to change the option every time. If you change the selection in Tools > Options > Object Explorer > Scripting, it should persist. As for why not scripting indexes is the default, I have no idea. Not a great question for your peers; if you really want the background, hit up someone on the SSMS team and buy them a beer - maybe they'll be able ...


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

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 ...


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 ...


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

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

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

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: http://blog.apexsql.com/apexsql-...


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

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


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 forfend,...


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

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

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

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 ...


2

Kris Johnston said in a comment: nomount/mount/open are the states of the database... something can't connect to the db one way and connect as nomount, and connect to the same db a different way and connect as mount or open. It doesn't work like that. It sounds like you are connecting to a different database altogether. To check, verify the dbid's are ...


2

Error is at below select statements. If you do ALT+F1 on sys.confiugrations you'll notice that value_in_use column is of type sql_variant and you are trying to assign it to int variables. To fix this, use below statements in your script and it should work fine. SELECT @advanced_options = cast(value_in_use as int) FROM sys.configurations WHERE name = 'show ...


1

You state that "some of the columns and tables may be renamed or disappear completely." I would interpret the corollary to that is: most of the columns and tables remain as they were. Therefore, assuming that assessment is correct, rather than trying to identify everything you should build a test database to make the changes. Restore a copy of your ...


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

;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 = tr....


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