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:
Terminal output when sandbox
database does exist:
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
Have Pl/Sql Outputs in Real Time
Insert Large Amount of Data Efficiently with Sql
Sql Select Multiple Rows in One Column
Is Too Many Left Joins a Code Smell
Oracle Text Escaping with Curly Braces and Wildcards
How to Convert Cyrillic Stored as Latin1 ( SQL ) to True Utf8 Cyrillic with Iconv
Elegant Way of Handling Postgresql Exceptions
Sql Field with Multiple Id's of Other Table
Sql: Insert Into...Values..Select
Sql Server Freetext Match - How to Sort by Relevance
Sql Server Delete Is Slower with Indexes
How to Insert N Rows of Default Values into a Table
How to Search for Names with Apostrophe in SQL Server
Sql Server Bug or Feature? Decimal Numbers Conversion
Performance Difference Between Primary Key and Unique Clustered Index in SQL Server