Sql Create Database If Not Exists, Unexpected Behaviour

CREATE DATABASE runs successfully but no DB created

Solution: Run SSMS as Admin.

Despite CREATE DATABASE working fine via an unelevated command line, SSMS requires admin privileges to do the same. The silent failure is...a possible bug?

I'll do further research on this but my working hypothesis is that when executing via a command line, it uses the SQL Server Windows Service instance's credentials (Network Service for older versions, NT Service\MSSQL$SQLEXPRESS for later versions - there's a strong whiff of a permissions issue here), to write to the %programfiles% folder. SSMS uses the currently logged in user (unelevated) if connecting via a Windows account. Without elevation, there is no write access to %programfiles%.

Still though, even if this is the case (to be verified), there should still be an access error when executing CREATE DATABASE in this context.

How - create and use database directly after creation in SQL Server?

Put a GO statement after the CREATE...

...
CREATE DATABASE Arms2;
PRINT 'CREATE DATABASE Arms2'
GO
USE Arms2

Error Code: 1007. Can't create database 'CustomerDataService'; database exists 0.00029 sec

Either remove the CREATE DATABASE line, or replace it with

CREATE DATABASE IF NOT EXISTS `CustomerDataService`;

Chances are that the scriptlet had already been run, and the tables already exist, too, so you may then run into Table already exists errors next.

The other, brute force, alternative would be to:

DROP DATABASE `CustomerDataService`;

But only try that if you are really sure you want to delete what is already there, and have a good backup just in case ...

SQL query using EXIST operator - unexpected records in result

Your query sounds like this:

Get all transaction history entries of product if any of history entry have
Quantity equal to 1000.

EXISTS return true or false, so

EXISTS(
select *
from Production.TransactionHistory t
where t.Quantity = 1000
and t.ProductID=p.ProductID
)

will return true for all TransactionHistory rows of product which have Transaction with Quantity = 1000

In addition:

Query above will be executed for every row of "Main" query and will return True on every row in your case. Thats why you get all rows

SQL Server : unexpected behaviour in while, when using INSERT INTO

You need to use ONE INSERT for each iteration, and specify all three columns and their values in one go:

WHILE @counter <= @maxCounter 
BEGIN
INSERT INTO #Info (DLoc, DCode, Dobs)
VALUES ('Location_' + CAST(@counter AS VARCHAR(16)),
'Code_' + CAST(@counter AS VARCHAR(16)),
'Observation_' + CAST(@counter AS VARCHAR(16))
)

SET @counter = @counter + 1
END

Each INSERT will insert a whole row - the value you provided is inserted into the column you specified, but the other columns will all remain NULL.

unexpected behavior when using SET NOEXEC ON in a script called using sqlcmd

At some point, it occurred to me that if the database existed (as indicated by the line printed to the terminal in the 2nd screenshot) then the dbo.Tally table should also exist. Based on that it didn't make sense that an invalid object name error message was appearing. The root cause: in Simulation.sql use $(db_name); came after the block of code that checks whether or not the database exists.

Therefore, the solution was to rearrange the order of the statements at the beginning of the Simulation.sql script and add another existence check -

print 'database name    $(db_name)';

if not exists (select 1 from sys.databases where [name] = '$(db_name)')
begin
create database $(db_name);
print '--- $(db_name) database created ---';
end;
go

use $(db_name);
go

if exists (select 1 from sys.databases where [name] = '$(db_name)') and exists (select distinct 1 from sys.all_objects where is_ms_shipped = 0)
begin
print '--- Database $(db_name) already exists - script execution aborted ---';
set NOEXEC on; --prevent execution of statements in this batch and batches that follow it
end;
go

--no change to remaining logic in script

Terminal output when sandbox database does not exist yet:
Sample Image

Terminal output when sandbox database does exist:
Sample Image

INSERT IF NOT EXISTS ELSE UPDATE?

Have a look at http://sqlite.org/lang_conflict.html.

You want something like:

insert or replace into Book (ID, Name, TypeID, Level, Seen) values
((select ID from Book where Name = "SearchName"), "SearchName", ...);

Note that any field not in the insert list will be set to NULL if the row already exists in the table. This is why there's a subselect for the ID column: In the replacement case the statement would set it to NULL and then a fresh ID would be allocated.

This approach can also be used if you want to leave particular field values alone if the row in the replacement case but set the field to NULL in the insert case.

For example, assuming you want to leave Seen alone:

insert or replace into Book (ID, Name, TypeID, Level, Seen) values (
(select ID from Book where Name = "SearchName"),
"SearchName",
5,
6,
(select Seen from Book where Name = "SearchName"));


Related Topics



Leave a reply



Submit