Hot answers tagged

15

Use the -v switch to pass in variables. sqlcmd -v varMDF="C:\dev\SAMPLE.mdf" varLDF="C:\dev\SAMPLE_log.ldf" Then in your script file CREATE DATABASE [SAMPLE] ON PRIMARY ( NAME = N'SAMPLE', FILENAME = N'$(varMDF)' , SIZE = 23552KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'SAMPLE_log', FILENAME = N'$(varLDF)' , SIZE = 29504KB , ...


12

You are too modest - your SQL is well and concisely written given the task you are undertaking. A few pointers: t1.name <> t2.name is always true if t1.name = REPLACE(t2.name, 'DUP_', '') - you can drop the former usually you want union all. union means union all then drop duplicates. It might make no difference in this case but always using union ...


11

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


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


9

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


8

As well as using SSMS, you can use the commercial SSMS Tools Pack I prefer it: more options, batching, cleverer all round. Insert statements for the whole database are generated by the order of PK-FK relationships. Top tables with no FK's are scripted first. Binary data is by default fully scripted. If you wish you can also set the scripting data limit ...


8

You'd use the sp_OA% stored procs for this. Or CLR since SQL Server 2005. It can't be done via T-SQL directly because T-SQL is for data manipulation. So you have to use one of the 2 methods above. Unless you want to use xp_cmdshell to run a powershell script. This also brings up one limitation of T-SQL: how to get an object definition to disk? And I guess ...


7

I don't know that anyone has written something along the lines of Ola's script within PowerShell. I know that PowerShell scripts for SQL Server are slowly growing over at the TechnNet Script Center. Then MSSQLTips.com also has started publishing tips working with SQL Server and PowerShell. Then you also have SQL Server PowerShell Extenstions project over on ...


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

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


7

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


6

I tried to use the VS tool yesterday with my production SQL 2000 instance, comparing to my dev 2008 instance, and it refused to work with anything prior to SQL 2005. Red Gate definitely does not have such a restriction. It even works reasonably well (not 100%) with another database we have that runs in 6.5 Compatibility Mode.


6

Oracle has Java server-side, but I'd caution that it is not a replacement for PL/SQL. There is no better language than PL/SQL for manipulating data stored in the database - Java may be appropriate for computationally intensive business logic. For postgres, There are currently four procedural languages available in the standard PostgreSQL distribution: ...


6

Here is an alternative to the test_attribs_unpivot view provided by JackPDouglas (+1) that works in versions before 11g and does fewer full table scans: CREATE OR REPLACE VIEW test_attribs_unpivot AS SELECT ID, Name, MyRow Attr#, CAST( DECODE(MyRow,1,attr1,2,attr2,3,attr3,4,attr4,attr5) AS VARCHAR2(2000)) attr FROM TEST_ATTRIBS CROSS JOIN (...


6

How do I make the DROP wait for the CREATE CLUSTERED to finish? You don't, it already does. The statements are sequential, i.e. synchronous, one after the other. What is heap storage? Paul Randal explains So what you really have is just a stack of paper thrown all over the desk, almost literally. Your operation neatly numbers the pages on the bottom ...


6

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


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


5

I had to do this for NPI (non-public information) reasons at a previous employer. Ended up scrambling fields in a script, since a first name / last name pair isn't NPI unless it's tied to something else. Here's a rough version of the script. Season to taste. Also, this assumes you have first and last names in separate fields. CREATE TABLE #RandomData ( ...


5

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


5

It's been a while since I used Red Gate, but the VS2010 has it matched from what I remember, with options to include or exclude by object types, and generate scripts to match the two schemas; the VS tools takes a while to run, I remember the Redgate to be pretty quick.


5

I started to play with RedGate's tool - Script Manager. You can have them together in a single place with some UI. It may worth a try :).


5

In a shell script: #!/bin/bash sqlplus user/pass@server/DATABASE<<THEEND -- Change "1" to the desired fatal return code whenever sqlerror exit 1; @yoursqlscript.sql quit; THEEND Or you can just run: sqlplus user/pass@server/DATABASE @yoursqlscript ... and put the whenever sqlerror exit 1; at the top of your .sql script(s).


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


5

There's nothing built-in from the command line. If you have Red Gate SQL Compare you can do it: sqlcompare /s1:MySQLInstance /db1:MyDB /mkscr:MyDB_Schema /q SSMS scripting functions are just wrappers for SMO. I know you mention it, but you could write a powershell script to use SMO. This is adapted from code found on this Simple Talk post. [System....


5

In theory you could do something as like wrap the script execution in a transaction. You can execute the .sql batch files from an application library like dbutilssqlcmd or SMO's ServerConnection.ExecuteNonQuery which handles the GO batch separator or sqlcmd extensions in the script. However in practice this is nearly impossible to do. Wrapping a script in a ...


5

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


5

ksh or bash let you script items through SQL*Plus, and you can do quite complex stuff through this. However, shell scripting tends to be a bit 'write-only' and sh and its derivatives aren't really much good for developing complex program logic. For running automated tasks they're OK. For complex client-side data manipulation, not so good (sed/awk ...


5

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


4

I'm personally using only Visual Studio with Database Edition GDR, because it's better for my needs. What I like about it mostly is the fact that I'm able to compare db projects with the actual databases. The tool can also compare databases, not only projects. It's true that the schema comparison tool itself is not very fast and has some quirks, but I ...


4

Use the standard SSMS scripting feature: Right click on database in object explorer->Tasks->Generate Scripts Then choose what tables do you want to script and not forget to check "Script Data"



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