Schema creations must be the only statement in a batch. One way to get around it is like so:

    IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = &#39;acme&#39;)) 
    BEGIN
        EXEC (&#39;CREATE SCHEMA [acme] AUTHORIZATION [dbo]&#39;)
    END

Here is an even simpler solution (simpler check):

    IF (SCHEMA_ID(&#39;acme&#39;) IS NULL) 
    BEGIN
        EXEC (&#39;CREATE SCHEMA [acme] AUTHORIZATION [dbo]&#39;)
    END



It needs to be in its own batch. You can wrap it in `EXEC(&#39;&#39;)`

    EXEC(&#39;CREATE SCHEMA [acme] AUTHORIZATION [dbo]&#39;)

I believe the reason for the requirement is something to do with an older version of the `CREATE SCHEMA` syntax introduced  in version 6.5 ([at least that&#39;s what it says here][1]).


  [1]: http://www.sommarskog.se/grantperm.html#newschcommands

Sometimes (always) you&#39;re not allowed to use Dynamic SQL, so using `EXEC` is not the best approach. Using `GO` statement can do things better:

    USE [MyDB]
    GO
    
    IF (SCHEMA_ID(&#39;MySchema&#39;) IS NOT NULL)
    BEGIN
    	DROP SCHEMA [MySchema];
    END
    
    GO
    
    CREATE SCHEMA [MySchema] AUTHORIZATION [dbo]
    
    GO

`CREATE SCHEMA` must be in it&#39;s own batch, so embed it inside an `EXEC` and you should be OK.

    IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = &#39;acme&#39;)) 
    BEGIN
        EXEC (&#39;CREATE SCHEMA [acme] AUTHORIZATION [dbo]&#39;)
    END



I&#39;m trying to select the first item in a ListView programmatically, but it doesn&#39;t appear to have been selected. I am using the following code:

    if (listView1.Items.Count &gt; 0)
        listView1.Items[0].Selected = true;

Actually I&#39;ve had this problem before but I can&#39;t remember how I managed to solve it!

How to select an item in a ListView programmatically?

When I modify a buffer, Emacs automatically creates a temporary symlink in the same directory as the file being edited (e.g. foo.c):

    .#foo.c -&gt; user@host.12345:1296583136

where &#39;12345&#39; is Emacs&#39; PID (I don&#39;t know what the last number means).

Why does Emacs create these links, and how do I prevent it from doing that?

Note that I have turned off auto save mode (`M-x auto-save-mode`) and disabled backup files (`M-x set-variable -&gt; make-backup-files -&gt; nil`). When I save a modified buffer, or undo the changes to it, the symlink disappears.

In particular, I&#39;m trying to prevent Emacs from creating these links because they cause the directory timestamp to be modified, which causes our build system to rebuild an entire module instead of compiling and linking for one changed file :/

Thanks for any input!

----------
Update: In order to prevent Emacs from creating interlocking files permanently, you can change `src/filelock.c` and build a custom binary:

    void
    lock_file (fn)
         Lisp_Object fn;
    {
         return;
         // Unused code below...
    }

----------
Update 2: Arne&#39;s answer is correct. It&#39;s now possible to disable lock files in the latest Emacs (24.3.1), by adding this to your .emacs file:

    (setq create-lockfiles nil)

Why does emacs create temporary symbolic links for modified files?

I generated a script which creates all users and schemas for this database and when I wrap the CREATE statements with an IF EXISTS check I find that it does not allow the CREATE SCHEMA call to run in the BEGIN/END block. It complains that it is invalid syntax. Yet I can run the command on it&#39;s own. A sample of the code is below. I am using SQL Server 2008 and Management Studio R2. Why is this invalid syntax?

	--DROP SCHEMA [acme]

	IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = &#39;acme&#39;)) 
	BEGIN
		CREATE SCHEMA [acme] AUTHORIZATION [dbo]
	END

Why can&#39;t I use &quot;create schema&quot; in a begin/end block in SQL Management Studio?

I generated a script which creates all users and schemas for this database and when I wrap the CREATE statements with an IF EXISTS check I find that it does not allow the CREATE SCHEMA call to run in the BEGIN/END block. It complains that it is invalid syntax. Yet I can run the command on it's own. A sample of the code is below. I am using SQL Server 2008 and Management Studio R2. Why is this invalid syntax?
<pre><code class="hljs language-sql">--DROP SCHEMA [acme]

IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'acme')) 
BEGIN
	CREATE SCHEMA [acme] AUTHORIZATION [dbo]
END
</code></pre>

Have picked up someone&#39;s code and this is a part of a where clause, anyone know what the double colon indicates?


    b.date_completed &gt;  a.dc::date + INTERVAL &#39;1 DAY 7:20:00&#39;

Double colon (::) notation in SQL

I have a SQL database and tables that I would like to replicate in another SQL Server.  I would like to create a SQL script that creates the database and tables in a single script.

I can create &quot;Create&quot; script using the SQL Management Studio for each case (Database and Tables), but I would like to know if combining the both &quot;Create&quot; scripts into single script would be enough.

Thanks.

Create SQL script that create database and tables

If we can get data from two tables without having primary and foreign key relation, then why we need this rule? Can you please explain me clearly, with suitable example?
It&#39;s a test database, don&#39;t mind the bad structure. 

Tables&#39; structure:

**

    table - &#39;test1&#39;
    columns - id,lname,fname,dob
    no primary and foreign key and also not unique(without any constraints)

**


    **table - &#39;test2&#39;
    columns- id,native_city
    again, no relations and no constraints** 

I can still join these tables with same columns &#39;id&#39;,
so if there&#39;s no primary-foreign key, then what is the use of that?




Why is a primary-foreign key relation required when we can join without it?

If I have a persons date of birth stored in a table in the form `dd-mm-yyyy` and I subtract it from the current date, what format is the date returned in?

How can I use this returned format to calculate someone&#39;s age?


Calculate Age in MySQL (InnoDb)

MySQL combine two columns and add into a new column

What is the maximum character length of object name (e.g. constraint, column) in SQL Server 2008? 

SQL Server: Maximum character length of object names

How can I generate a class from a SQL Server table object?

I&#39;m not talking about using some ORM. I just need to create the entities (simple class). Something like:

```cs
    public class Person 
    {
        public string Name { get;set; }
        public string Phone { get;set; }
    }
```

 Given some table like:

```
+----+-------+----------------+
| ID | Name  |     Phone      |
+----+-------+----------------+
|  1 | Alice | (555) 555-5550 |
|  2 | Bob   | (555) 555-5551 |
|  3 | Cathy | (555) 555-5552 |
+----+-------+----------------+


```

Generate class from database table

I am trying to order by date. I want the most recent dates coming in first. That&#39;s easy enough, but there are many records that are null and those come before any records that have a date.

I have tried a few things with no success:

    ORDER BY ISNULL(Next_Contact_Date, 0)
    
    ORDER BY ISNULL(Next_Contact_Date, 999999999)
    
    ORDER BY coalesce(Next_Contact_Date, 99/99/9999)

How can I order by date and have the nulls come in last? The data type is `smalldatetime`.

SQL Server ORDER BY date and nulls last

I want to move data from one DB to another, while keeping the identity column values in the old DB. When I run the wizard I get a validation error &quot;Failure inserting into the read-only column &quot;ChannelID&quot;.

Where ChannelID is an identity column on that table.
How can I override the identity functionality in order to be able to import the data?

Import and Export Wizard with Identity Column

In trying to improve the speed of an immensely slow query (several _minutes_ on two tables with only ~50,000 rows each, on SQL Server 2008 if it matters), I narrowed down the problem to an `OR` in my inner join, as in:

    SELECT mt.ID, mt.ParentID, ot.MasterID
      FROM dbo.MainTable AS mt
      INNER JOIN dbo.OtherTable AS ot ON ot.ParentID = mt.ID
                                      OR ot.ID = mt.ParentID

I changed this to (what I hope is) an equivalent pair of left joins, shown here:

    SELECT mt.ID, mt.ParentID,
       CASE WHEN ot1.MasterID IS NOT NULL THEN
          ot1.MasterID ELSE
          ot2.MasterID END AS MasterID
      FROM dbo.MainTable AS mt
      LEFT JOIN dbo.OtherTable AS ot1 ON ot1.ParentID = mt.ID
      LEFT JOIN dbo.OtherTable AS ot2 ON ot2.ID = mt.ParentID
      WHERE ot1.MasterID IS NOT NULL OR ot2.MasterID IS NOT NULL

.. and the query now runs in about a second!

Is it generally a bad idea to put an `OR` in a join condition?  Or am I just unlucky somehow in the layout of my tables?

Is having an &#39;OR&#39; in an INNER JOIN condition a bad idea?

I have MS SQL 2008 R2, 500 databases.
What is the most efficient, easiest and &#39;modern&#39; way to query all databases sizes.

The output should have columns:

- DatabaseName
- DataFilesSize
- LogFilesSize

Content Type	Original Author	Original Content on Stackoverflow
Question	Brennan	View Question on Stackoverflow
Solution 1 - Sql	Tom H	View Answer on Stackoverflow
Solution 2 - Sql	Mark Whitfeld	View Answer on Stackoverflow
Solution 3 - Sql	Martin Smith	View Answer on Stackoverflow
Solution 4 - Sql	Raymond Reddington	View Answer on Stackoverflow
Solution 5 - Sql	Joe Stefanelli	View Answer on Stackoverflow

Why can't I use "create schema" in a begin/end block in SQL Management Studio?

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

Solution 2 - Sql

Solution 3 - Sql

Solution 4 - Sql

Solution 5 - Sql

Why does emacs create temporary symbolic links for modified files?

How to select an item in a ListView programmatically?

Attributions