Tag Info

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


10

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


6

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


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


5

As well as using SSMS, you can use 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 between 0 and ...


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

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


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

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


4

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


4

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


3

You can't put CREATE FUNCTION inside IF logic since it needs to be in its own batch. I would do it this way. If the function doesn't exist yet, create an empty stub. Now you can always safely issue an alter. IF OBJECT_ID('dbo.function_name') IS NULL BEGIN DECLARE @sql NVARCHAR(MAX) = N'CREATE FUNCTION dbo.function_name() RETURNS INT AS ...


3

As you describe it, it's not going to work. The T-SQL script needs to run on a SQL Server which means that it needs to be run in an appropriately-authenticated session. One option would be to write the TSQL script, and then provide it with a VBScript or CMD file or PowerShell script that'll launch the TSQL at the correct server with the right credentials.


3

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


3

If you don't want to know what the name is but for whatever reason you want to keep something in the table for that record, you can always just use a hash of the name: UPDATE MyTable SET Name = HASHBYTES('SHA1', Name) This will keep a unique value for each name record, but you will have no way to know what the name is. You can, however, see if a name ...


3

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


3

Well, if sqlplus is screwing your dbms_metadata.get_ddl output, why not select the output in a CLOB and write the CLOB to filesystem. e.g. DECLARE data CLOB; objType varchar2(30) := 'TABLE'; objSchema varchar2(30) := 'SCOTT'; objName varchar2(30) := 'EMP'; fname varchar2(256) := objType || '_' || objSchema || '_' || objName || '.sql'; ...


3

I often encounter the similar problem to compare two versions of a table for new, deleted or changed rows. Some month ago I published a solution for SQL Server using PowerShell here . To adapt it to your problem, I first create two views to separate the original from the duplicate rows CREATE OR REPLACE VIEW V1_TEST_ATTRIBS AS select * from TEST_ATTRIBS ...


3

In SQL*Plus, the Oracle command line too, you can use the syntax: SQL> @http://blah.com/myscript.sql And run all your scripts off your own personal webserver if you like. My good friend Vishal does this. You just need to get a VM or something of your own, these are cheap now, a quick Google shows £10/month. Just be certain it doesn't stipulate ...


3

DROP DATABASE is a very special command that cannot be undone. To my knowledge there is no way to drop a database inside a transaction. I quote the manual: DROP DATABASE cannot be executed inside a transaction block. Whenever you run two commands in a script, they are automatically wrapped into a transaction. You can explicitly begin and commit ...


3

You can't select columns from the view that aren't exposed in the view definition. If your view only includes three columns in the SELECT list, you can only reference those three columns in queries against the view. You can't use table aliases that are defined in the view in queries against the view. You cannot, therefore, reference the e2 alias when you ...


3

Using a batch file and SQLCMD, you can execute the script as below : cls echo off set DbServer=Server_name\InstanceName set MyLogin=sa set MyPassword=StrongPassword set MasterDbName=master set DbName=AdventureWorks set SQLCMDPath=C:\Program Files\Microsoft SQL Server\90\Tools\binn set SpScripFilePath=C:\ScriptFolder set SpScripFileName=test.sql echo ...


3

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


3

No version of SQL Server has this. It would be useful (PostreSQL has the same and I like it). In SQL Server, there are table types (CREATE TYPE), temporary tables (CREATE TABLE #foo) and table variables (DECLARE @var TABLE), but you can't mimic or inherit a table definition using %TYPE or CREATE TABLE .. INHERITS/OF


3

I would personally just do a backup for each database, and then attach them to the new server. Just remember to use EXEC sp_change_users_login 'Auto_Fix', 'username' for fixing user accounts. The Wizard approach sounds doable. I you need another approach for scripting DDL, the scptxfr.exe tool that comes with earlier version of SQL Server still works with ...


2

You have three(3) options to go about looking at what is running in MySQL OPTION #1 : General Log You could simply activate the general log and look for the timestamp between 3:00-3:30 AM. You could either have the general log as a text file or a MyISAM table. Here are my posts on using the general log: How to enable MySQL general log? (Jan 08, 2012) How ...


2

If your destination database has only one additional column, I think the simplest thing to do would be to pg_dump the existing database, load that dump into a completely new database, then add the new column. An alternative might be to use the --column-inserts option (among others) for pg_dump. That will dump data as SQL INSERT statements with explicit ...


2

This is probably not the best way to do what you want to do. The "proper" way would probably be to execute the SQL commands in your file via jdbc. That said, that's a long way from where you are now, and we can probably make what you are trying to do work. The likely problem is that there is a space in the path to psql.exe. When you use that particular ...


2

Part of the reason that you don't get the heap to shrink as much as you expect is that there's 8 bytes in each row for the dummy column. That might be large, depending on the average size of a typical row. Then you drop column dummy, leaving that space unused. Try this without adding and removing dummy and make your clustered index on other columns. They ...


2

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



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