30

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


21

Best is to use Powershell - if you are going to use it frequently. You can refer to Automated Script-generation with Powershell and SMO. Also, SQL Server PowerShell Extensions (SQLPSX) are of great value when working with Powershell. All the modules are having help files e.g. Get-SqlScripter. Get-SqlDatabase -dbname database1 -sqlserver server | Get-...


14

Thankfully there is support for line-continuation in T-SQL via the \ (backslash) character. Just place that at the end of of a line, right before the newline / carriage-return + line-feed, and the newline will be ignored. For text strings, this behaves as follows: PRINT 'Line1\ Line2'; returns (in the "Messages" tab): Line1Line2 For binary / hex strings,...


14

I'll share how I have done this in the past. It is designed to solve the specific limitation of pre-deployment scripts that you call out in your second point: Adding the column in a pre-deployment script will fail the publish when it automatically tries to create the same column, a second time (even if the pre- deployment script is written to be ...


13

Your friend is sys.database_permissions to check/script out permisssions. Below is the script that I am using when doing a refresh of PROD on DEV or UAT server. I will script out before hand all the permissions and after the restore I will just run the script. /* Script DB Level Permissions v2.1 Source: http://www.sqlservercentral.com/scripts/Security/...


13

Tara Raj from Microsoft recently announced that the Microsoft SQL team has released a set of command line tools to generate T-SQL scripts that appear to do exactly what you've asked: mssql-scripter Mssql-scripter is the multiplatform command line equivalent of the widely used Generate Scripts Wizard experience in SSMS. You can use mssql-scripter on Linux, ...


13

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


13

Is there a way to have variable interpolation like this? Kinda sorta, in a way. Just not directly. You need to keep in mind a few things: The value that you set a variable to via :setvar is a simple, literal value. You can see this by running just the following two lines: :setvar var1 $(var2) PRINT '$(var1)'; which returns: $(var2) If SQLCMD / SQLCMD-...


12

COLLATE operates per predicate or per expression, depending on context. COLLATE is mainly used to control how the string values are being compared or sorted. Hence it is most commonly used in JOIN, WHERE, and HAVING predicates, as well as GROUP BY and ORDER BY clauses (in which case it can be used per column / expression). It needs to be applied to string ...


11

I, personally, use sp_restorecriptgenie by Paul Brewer. Here's the link to the SCC article that has it. Under 'The Procedure' http://www.sqlservercentral.com/articles/Restore+database/95839/ Basically, once you build the stored procedure, just run exec sp_restorescriptgenie and the script will go out and get all backups for every DB (even transaction logs) ...


10

PROBLEM NUMERO ONE GO is the default query batch separator. It is a feature of certain client utilities (mainly SQLCMD and SSMS) and is unknown to the SQL Server engine itself. GO needs to be on a line by itself (optionally followed by only an INT value to cause repetition of that batch) as it is parsed by these client utilities to indicate where to split ...


9

From the documentation: createdb is a wrapper around the SQL command CREATE DATABASE. There is no effective difference between creating databases via this utility and via other methods for accessing the server. This means that you can write a script which issues a command like psql -f createdb.sql -v passwd=$passwd -v user=$user Where createdb.sql ...


9

From http://msdn.microsoft.com/en-us/library/ms175502(v=sql.105).aspx Rules for Using Batches The following rules apply to using batches: CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch. The CREATE statement must ...


9

I wrote an open source command line utility named SchemaZen that does this. It's much faster than scripting from management studio and it's output is more version control friendly. It supports scripting both schema and data. To generate scripts run: schemazen.exe script --server localhost --database db --scriptDir c:\somedir Then to recreate the database ...


9

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


9

Discontinued Database Engine Functionality in SQL Server 2016 indicates ActiveX subsytem is discontinued. Use command line or PowerShell scripts instead.


8

You will need to script permissions in two steps, roles and objects. As Kin alludes to, you can use sys.database_permissions for the objects, but you will want to use sys.database_principals and sys.database_role_members for the role membership. The following SQL will work for SQL 2012 only (previous versions should use sp_addrolemember for roles) and ...


8

Why not? It allows you to see when the script was created if you save it & reload another time. The others you mention (created/last modified/last ran) aren't so useful as they're subject to frequent change. And can be grabbed from system views when/if you do need them. You can turn it off in Options here if you really want to:


8

I searched the Internet for an answer last night and found nothing. Of course, today after posting this question I try again--and find an adequate response almost immediately. The gist of it? Put your commands in a text file and direct it to SQLite using the input file descriptor, or just script everything in a bash script. First Method: sqlite3 database.db &...


8

The "X" is an alias for the following portion: Select distinct roleid from UserAccounts WHERE CustID = @custID So you'd be able to use it as: SELECT x.roleid FROM ( Select distinct roleid from UserAccounts WHERE CustID = @custID) x where x.roleid >100 In the grand scheme of things, your query would look like: (select @invtid = coalesce(@...


8

Yes, you bet. Let's start with the open source First Responder Kit (disclaimer: I'm one of the script authors) - install sp_Blitz, and then: sp_Blitz @CheckServerInfo = 1 And at the bottom, you get a bunch of information about your system: I've taken the liberty of rearranging the columns a little in that screenshot because I want to show you the finding, ...


7

Yup, this is Bug 2391334 which has been around for long time, and probably will not be fixed in the near future. One way of working around this is "know" the path for scripts without actually hard coding that path. To do this in SQLPlus requires a trick - if you try to run a non-existent file, then you'll get an error message that includes the path name. ...


7

I wrote an open source command line utility named SchemaZen that does this. It's much faster than scripting from management studio and it's output is more version control friendly. It supports scripting both schema and data. To generate scripts run: schemazen.exe script --server localhost --database db --scriptDir c:\somedir Then to recreate the database ...


7

Your error message says: psql:cobertura.sql:29: ERROR: "sql " is not a known variable LINE 14: sql := format('insert into cobertura_tmp select count(*) as ... Look closely: "sql ", not "sql" That means you have a sneaky, invisible character right after "sql" instead of an innocent space character. It's not in your question, probably got lost in ...


7

You've pretty much self answered here - you won't be able to rely on SQL Server to do the scheduling for you. The most common method is to use Windows Task Scheduler, but if you have any other job scheduling application already that might be a better choice. Either way, you'll need to get comfortable with the sqlcmd utility if you aren't yet. If I were ...


6

The end of your post is really the important bit: information regarding what best suits the needs of database administrators. So let's start there. What are your needs as a DBA? You generally have two realms of operation: system level maintenance, and database level maintenance (whereby all actions are merely maintaining the system for better ...


6

If I understand correctly, you want to restart the numbering with 0 for every table. Use the window function row_number() in an UPDATE: UPDATE tbl t SET xid = n.xid FROM (SELECT ctid, row_number() OVER (ORDER BY aid, bid, cid) - 1 AS xid FROM tbl) n WHERE t.ctid = n.ctid; Using ctid as poor man's surrogate for a primary key, since you neglected to ...


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


6

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


6

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


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