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


6

This is not feasible in T-SQL. Not all object have a definition stored and you would have to reverse engineer CREATE scripts for basic things like tables and indexes. Rather than reinvent the wheel (in T-SQL), do the proper thing and reuse the component dedicated for this purpose the SMO Scripter class. Is already available, works, is up to date with all ...


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


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


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


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

While not disputing that the most comprehensive method is to use SMO as Remus suggested in his answer, this question does request to get the info via T-SQL and that info does exist for some objects. Just keep in mind that this info: covers a small set of object types does not include any GRANT / DENY statements. But you can get that info from ...


3

No, don't use sys.syslogins - this is a deprecated compatibility view that is only there so people have time to change their existing code based on SQL Server 2000. Also, I'm completely unclear how you think a CASE expression will help you. You need to join to sys.server_role_members to find out which logins belong to which roles, and then back to ...


3

Chances are that the reason your script runs from the command line but fails from cron is that your DB2 environment is already initialized (i.e. via your .profile) for the interactive session, where it is not when called by cron. Here's a suggested modification: #!/bin/bash # # Setup DB2 Environment . $HOME/sqllib/db2profile # Back up Database db2 ...


2

Three misunderstandings: You cannot return data from a DO command. You cannot SELECT without target in plpgsql code. That's what the error message tells you. You don't need either for a simple SELECT statement. Just run the statement itself: abc() { $PG_CMD 'select * from customer' }


2

Instead of issuing PRINT @tsql;, you can issue EXEC( @tsql );, if I understand what you're asking. If so, I would suggest declaring a preview parameter ( something like DECLARE @Preview BIT = 1; ) and then use an IF to either print or execute. IF ( @Preview = 1 ) BEGIN PRINT @tsql; END ELSE BEGIN EXEC( @tsql ); END;


2

Use Profiler for a moment (or Extended Events or a server-side trace), and then produce some of the scripts you want via Management Studio. Make sure you've got the scripting options you want turned on - you may prefer to use Tasks | Generate Scripts at the database level. Anyway, the point being that you can have a look at what queries run, and get a feel ...


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

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

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

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


1

All you need to do is JOIN to sysusers SELECT p.spid, p.dbid, p.uid, u.name FROM sys.sysprocesses p JOIN sys.sysusers u ON p.uid = u.uid


1

Logins (on the server) do not automatically correspond to users (in the database). For instance, "sa" does not have its own user in the database, but uses "dbo". This might get you started. SELECT s.session_id, sp.[type], sp.type_desc, sp.[name] AS loginName, dp.[name] AS userName, sp.is_disabled FROM sys.dm_exec_sessions AS s LEFT JOIN ...


1

If you go to the following stack exchange link and scroll down to the second answer where you see Neeraj Prasad Sharma create a stored procedure named INS, if you go into that stored procedure and modify the line Select @CONDITIONS= 'Select '+@CONDITIONS +'FRom ' +@Schema_name+'.'+@Table_name+' With(NOLOCK) ' + ' Where '+@Condition ...


1

Remark: I stumbled upon this problem myself on MS Windows and I already had something to feed into psql using its stdin. I had to combine inputs. And that is where it gets really tricky. I thought to share a somewhat valuable in my opinion example, besides Erwin's answer, so I'm posting it here as an answer in case someone else is also in need to "use ...


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



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