26

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


15

If the server is using TCP/IP, then the simple way is to just telnet to the SQL Server port and see if it connects. By default, that's port 1433, so this should work: telnet servername 1433 That will probably be appropriate in most cases. If it's using a different port, or dynamic ports (common with a named instance), then you'll need to determine which ...


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


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


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

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


12

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


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

$(...) denotes a variable in SQLCMD, so it's interpreting your data as a variable. Use the -x command-line option to disable variables.


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

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


8

Provided you have the Microsoft.SqlServer.Smo assembly in your GAC on the local machine, this can be easily done with PowerShell: [Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null $server = New-Object Microsoft.SqlServer.Management.Smo.Server("YourSqlServerName") # do a simple operation to see if you can get data Try { $...


8

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


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

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


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


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


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

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

How about you run your your unit, functional, integration and performance tests? If you don't have any tests then is serious time to start considering your database schema as code and treat it as such, including version control and testing. Alex Kuznetsov has an entire book dedicated to this subject: Defensive Database Programming with SQL Server.


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 reason you are having problems with dbms_metadata.get_ddl is that it outputs CLOBs which can be up to 4GB in size. By default, SQL*Plus and Oracle SQL Developer truncate long text so they don't trash the client with large gobs of text. It's very easy to override this behavior in SQL*Plus with a few SET commands and get clean DDL. The script you need ...


6

When executing multiple commands in a script in pgAdmin they are automatically wrapped into a transaction. You can explicitly begin and commit transactions, but not with commands that won't run in a transaction context. The manual: DROP DATABASE cannot be executed inside a transaction block. Similar in psql when called with -c command. The manual: If ...


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