Return number of rows based on column value We are administering a sweepstakes for a client where we have a GUID tied to a contestant and the number of entries the contestant has acquired. I want to be able to draw winners based on the chance of winning corresponding to the number of entries.

GUID | Entries
--------------
1    | 1
2    | 5
3    | 2
4    | 7

So the results would be:

GUID
----
1
2
2
2
2
2
3
3
4
4
4
4
4
4
4

Basically I plan put the results in a spreadsheet and then use a random number generator to pick a row number between 1 and n, where n is the total number of entries. But of course if there is a way to easily select the winner programmatically, I'm all ears (or eyes as the case may be).

Thanks in advance.

For Postgres you can use:

select guid, g.i
from the_table
  cross join lateral generate_series(1, entries) as g(i);

Not exact as it will miss missing entries but it probably does what you want

set nocount on
Declare @GuidEntries table (Guid int, Entries int)
insert into @GuidEntries values(1, 1)
insert into @GuidEntries values(2, 5)
insert into @GuidEntries values(3, 2)
insert into @GuidEntries values(4, 7)
insert into @GuidEntries values(5, 1)
insert into @GuidEntries values(6, 2)

select g.Guid
from @GuidEntries g
join (select distinct entries from @GuidEntries) ge
      on g.Entries >= ge.Entries
order by g.Guid

Could even get a random

with cte as
(
select g.Guid, ROW_NUMBER() over(order by g.GUID) as rn
from @GuidEntries g
join (select distinct entries from @GuidEntries) ge
      on g.Entries >= ge.Entries
)
--select select C1.Guid FROM CTE C1
select C1.Guid, C1.rn
FROM CTE C1
where C1.rn = (select cast(rand()*count(*) as int) + 1  from cte)

If you have small max on entries
But I like the answer from Scott better

select GUID from talble where Entries = 1
union all
select GUID from talble where Entries = 2
union all
select GUID from talble where Entries = 3
union all
select GUID from talble where Entries = 4
union all
select GUID from talble where Entries = 5
union all
select GUID from talble where Entries = 6
union all
select GUID from talble where Entries = 7
union all
select GUID from talble where Entries = 8
union all
select GUID from talble where Entries = 9


Activity Monitor % Processor Time grayed out on SQL Server The % Processor Time graph at Activity Monitor for remote connections stopped working and it grayed out after the physical SQL Server restart (image below), but when I get in the server with the same Domain User it works fine. It was also working fine on remote connections before the restart and I have noticed that it takes significantly more time to load the Processes than before.

The problem is happening to all users that try to see the Processor Time remotely including sa and server admins so I would think it´s not a permission issue. The server runs Windows Server 2012 and SQL Server 2012.

What could be happening? or How can I solve this?

Thanks in advance! and sorry about my English.

I know it is not a solution but we had a programmed restart and after that everything works fine, it is a production server so we couldn't restart before but that solved it.


SQL server not listing records with NULL values I faced a strange phenomenon today. I ran the below query to Find all requests with waits other than BROKER_RECEIVE_WAITFOR

select * from sys.dm_exec_requests where session_id > 55
and wait_type not like ('BROKER_RECEIVE_WAITFOR')

The query ran fine, But it failed to list any request with No waits, ie with wait_type as NULL(there were quite a lot of running queries without any waits at this time).

Why is this happening, Isn't it supposed to list all records with wait_type not like 'BROKER_RECEIVE_WAITFOR' including Nulls?

I'm running SQL server 2008R2 SP3.

The reason for this is that a NULL represents an unknown value.

NULL is not LIKE anything and not NOT LIKE anything either.

Try running the following 2 statements on your system:

SELECT CASE WHEN null like '%a%' THEN 'true' ELSE 'false' END;
SELECT CASE WHEN null not like '%a%' THEN 'true' ELSE 'false' END;

That should return false for both statements.

So your query should look like this:

select * from sys.dm_exec_requests where session_id > 55
and (wait_type not like ('BROKER_RECEIVE_WAITFOR') or wait_type is null)

If you want to include nulls I would write it like this:

select * from sys.dm_exec_requests
where session_id > 55
  and COALESCE(wait_type,'') not like ('BROKER_RECEIVE_WAITFOR')

a number of times I've found optimizers handle COALESCE faster than or xxx is null


ora 20001 error in oracle dbms scheduler i have written a test job like

begin
DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'test_job'
,job_type =>'plsql_block'
,job_action => 'null'
,enabled => true
);
end;
/

after a while when the check the status

select * from user_scheduler_job_run_details where job_name='TEST_JOB';

the status is failed ,i see these two lines

ORA-20001: You are not allowed to logon from this terminal!!!
ORA-06512: at line 39

how can i resolve this error.
and
how to find the job id of a scheduled job(created using dbms_scheduler)

Your question contain 2 items that must have been looked at before question itself arose:

Have you looked at API of DBMS_SCHEDULER (any programmer should do that before knowing how to use built-in [package]) ; another words have you RTFM?
Oracle (most of the time) is in-fact very good with "responses" - error signaling back to "client". Have you googled ora - 20001 ?

To your question:

Your job action is NULL; you need to have some procedure that does something AND also is not named as Oracle keyword or PLSQl block that must have at bare minimum begin and end
- docs
Oracle error 20001 is custom error and if you want to see the code you need to contact DBA's.
Based on the message , seems that your schema also missing some grants (you obviously logged in to the database , so message itself is deceiving , but the exact error can only be identified by going thru the code or checking with DBA's directly - see above )
why do you need job id? your job name is providing your with information already! check API's!!


SQL select query order by on where in How Can I make the order by based on what I input on where?

example query

select * from student where stud_id in (
'5',
'3',
'4'
)

the result would be

id|   name  |
5 |  John   |
3 |  Erik   |
4 | Michael |

Kindly help me thanks.

There is no such direct language construct. If you want to do an "unnatural order" (based on the data type), then you can either join to some other table that maps the values and has another column to define the order for each value, or you can use CASE in your ORDER BY, such as

CREATE TABLE t(col int IDENTITY)
INSERT INTO t DEFAULT VALUES
GO 8

SELECT *
FROM t
WHERE col IN(5, 3, 4)
ORDER BY
 CASE col
  WHEN 5 THEN 1
  WHEN 3 THEN 2
  WHEN 4 THEN 3
 END

I'm using int above, but that doesn't change the concept. If you don't like repeating the "list of values" in the ORDER BY as well as the WHERE you can skip the WHERE and do a TOP for as many rows that you want. In any case, not neat, but since there isn't such language construct...

select * from student where stud_id in (
'5',
'3',
'4'
)

Just add at the end of the statement ORDER BY CAST(stud_id AS INT) DESC


What is the most effective way to discover all running instances of SQL Server using PowerShell? I've been tasked with discovering all the instances of SQL Server that are running within our domain. In several cases there are multiple instances per server. I've seen two different PowerShell methods of finding these instances, but neither seem to find all the instances.

1) Use WMI

        $srvr = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $computerName
    $instances = $srvr | ForEach-Object {$_.ServerInstances} | Select @{Name="fullName";Expression={$computerName +"\"+ $_.Name}}
    return $instances

2) Use remote registry (as with Get-SQLInstance1)

The biggest problem I'm running into is that not all of the servers that I know about are running with the SQL Server WMI provider nor are all of them allowing remote registry. Is there a third method? I can use Remote Desktop to access all the servers but I'm looking at approximately 30 machines and would like to avoid manual steps if possible. This only needs to work for SQL Server 2008 and higher and while it would be nice to know about the other SQL Server services (SSIS/SSAS/SSRS) my main focus is on SQL Server itself.

If you want something that will be useful for the future I would probably steer clear of trying to search the registry. The hives for SQL Server have changed a bit over the years and it can be troublesome to keep up with.

The method with the SqlDataSourceEnumerator is flaky at times and although I will use it, not concrete evidence that instances are on the network. I believe it depends on SQL Browser Service as well, which most of the time I find disabled.

I will utilize the WMI class win32_Service. I use this because it offers up more information about the service than Get-Service cmdlet does.

I write everything as functions generally because you can use this to actually just do daily check or verification of the service for troubleshooting.

function Get-ServiceStatus ([string[]]$server)
{
 foreach ($s in $server)
 {
   if(Test-Connection $s -Count 2 -Quiet)
   {
    Get-WmiObject win32_Service -Computer $s |
     where {$_.DisplayName -match "SQL Server"} |
     select SystemName, DisplayName, Name, State, Status, StartMode, StartName
   }
 }
}

This is a bit more than what I usually use but in case someone else comes across and wants to use it. The Test-Connection equates to ping myserver in a DOS prompt and the -Quiet flag simply just has it return true or false. This will default to 4 pings so setting -Count 2 just makes it do it twice instead.

The variable [string[]]$server is a method used to state that $server will accept an array of server names. So an example call of this function could look something like:

Get-ServiceStatus -server (Get-Content C:\temp\MyServerList.txt)

or

$servers = 'MyServer1','MyServer2','MyServer3'
Get-ServiceStatus -server $servers

EDIT

A noted comment is the above does depend on a list of servers being provided. In cases where I am not provided that list you do have a few other options.

If I am in an Active Directory environment I can use the ActiveDirectory module in PowerShell to pull a list of all the servers on the domain with Get-ADComputer cmdlet. A word of warning though make sure you use a good -Filter on large domains.
I have also simply done an IP scan (with approval) of a network that gives me the IP addresses where port 1433 was found open. I will take that IP list and utilize Get-ADComputer to find the domain computer names, then pass that into the function above

Example:

Import-Module ActiveDirectory
$sList = $ipList | Select -ExpandProperty IP
$results = foreach ($i in $sList) {
 Get-ADComputer -Filter 'IPv4Address -eq $i' -Properties * | Select Name}
Get-ServiceStatus -server $results

EDIT

The suggested edit to utilize Write-Verbose and also add in try/catch block, while that may be useful, and in most cases a code practice, I will leave that up to the person that wants to use this function to add that additional code or functionality. Just trying to provide a basic example to go on. I did add the SystemName property to the output to include the actual server name returning information, do this on other functions just generally don't use this for more than one server at a time so it slipped my mind.

Just tried this :[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

Not a lot of data returned but it did detect all the sql servers I have running in a VM environment.


PowerShell command 'New-Item' fails when invoked through SQL Server Agent job I have the following PowerShell script to create a folder at a share location. The share has full control permissions for everyone. I have granted all permissions for testing.

The problem is the script works fine when I manually execute. It also executes fine when I execute it as SQL Server Agent service account. But the same script fails when I try to execute as part of SQL Server Agent job. Am I missing something here?

$path = "\\server\shared\path\01222020"
New-Item -path $path -ItemType Directory -Force

Exception:
Date        1/22/2020 5:25:10 PM
Log     Job History (test)

Step ID     1
Server      serverA
Job Name        Test
Step Name       Test
Duration        00:00:01
Sql Severity    0
Sql Message ID  0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted   0

Message
Executed as user: domain\agent_service_account. A job step received an error at
line 9 in a PowerShell script. The corresponding line is 'New-Item -path $path
-ItemType Directory '. Correct the script and reschedule the job. The error
information returned by PowerShell is: 'Invalid Path:
'\\server\shared\path\01222020'.  '.  Process Exit Code -1.  The step failed.

When accessing UNC paths, or any 'location' that isn't a local drive (i.e. registry), from a PowerShell job step, you need prefix the path with Microsoft.PowerShell.Core\FileSystem::. This tells SQLPS which provider to use, which isn't required in normal PowerShell but is required in the SQL Server implementation.

Alternatively, you can change directory beforehand to a local drive (cd C:) and it should then work without prefixing the provider name, but you may want to stay in the default SQLSERVER:\SQL\SERVERNAME\INSTANCENAME path depending on what your script requires.

More info:

https://dbatools.io/agent/

https://social.technet.microsoft.com/Forums/windowsserver/en-US/ec7f3ae8-d196-459e-b9dc-e6ed0df93004/running-powershell-from-sql-server-using-unc-paths

sqlcheckpoint,

Please take a look at the link below, which describes which Powershell modules are used by SQL Agent. Its either SQLPS or SqlServer depending upon your version of SQL Server.

https://docs.microsoft.com/en-us/sql/powershell/run-windows-powershell-steps-in-sql-server-agent?view=sql-server-ver15

It quite likely the New-Item cmdlet is not found in the powershell module used by SQLAgent.

You could create a SQLAgent job with a CmdExec step, which in turn calls powershell.exe.

powershell.exe -ExecutionPolicy ByPass -NoProfile -File PATH_TO_YOUR_SCRIPT

See the link below for all the options/parameters for powershell.exe

https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_powershell_exe?view=powershell-5.1


Copy Database wizard fails but leaves no text file log I've been using the copy database wizard to create a development copy of my database.

Unfortunately, the copy database wizard has repeatedly failed.  I suspect the problem is that the sql server agent service account does not have the necessary permissions.  I can't verify this because the log file isn't actually being saved.  I've tried having it save to the default directory where I know the account running the copy database operations must have permissions and to another location on the server.

Any thoughts about how I can resolve this problem?  The new database is showing up but without any tables...

Please use backup / restore. The copy database wizard is a disappointing sack of, well, let's leave it at that.

As I encountered this problem I suspect it is likely due to an incorrect user id and/or password in the credentials.


How to check the status of SQL Server? I need to check the status of SQL Server. When I say status its not the status for each database, but rather for the whole SQL Server.

I need to know what are the queries running in the server and no. of connections, which query is taking too long.

Can anyone please help me with this..

You'd have more options here:

Perfmon (or ResourceMonitor on W7+) for general resource information;
Activity Monitor in Management Studio;
Inside SQL Server:

Glenn Berry's DMV queries;
Adam Machanic's stored procedure sp_WhoIsActive.

There are also many other external monitoring tools (some free, some paid) that you can use, but these ones are free and worth mentioning as a first step in your work.

SELECT DB_NAME() AS DatabaseName, DATABASEPROPERTYEX('master', 'Status') AS DBStatus

The DATABASEPROPERTYX function only allows you to see one element at a time, but this maybe helpful if that is all you need.

Here we can see the Status for the master database by issuing the above query.


SQL Server 2008 R2 SP1 - Tempdb "Available free space" showing negative value Please help as this issue is driving me nuts...

Tempdb has four data files and one log file. They are all located on the same dedicated drive.

When I right click on tempdb in SQL Server Management Studio (SSMS) to view Properties --> General...I'm seeing "Size" as ~65000 MB with "Space Available" as ~64000 MB. However, when I try to shrink the files via the GUI, I see that the "currently allocated space" hasn't changed and is still showing the initial size.

This is also where the "Available free space" is shown as a negative number. The only workaround I have found so far is to restart SQL Server to flush out tempdb, but I can't keep on doing this.

Has anyone ever come across this and knows the root cause/permanent solution for it please?

Open Query Analyzer
Run DBCC UPDATEUSAGE('tempdb')
After it is done, check temdb value and it should show the true values.

Also, this might help you.

Also, check if there are any open transactions using dbcc opentran or sp_whoisactive (Adam Mechanic's SP)

This was a bug in SQL Server 2008 SP1 described here.

Out of curiosity, is it causing any performance problems ?

You can simply change the initial size of the files and accordingly shrink the file after that. The issue is that updated size is not reflecting in the file size and hence the available space is in negative. Hope this helps.


How can I optimize a mysqldump of a large database? I have a symfony application with an InnoDB database that is ~2GB with 57 tables.  The majority of the size of the database resides in a single table (~1.2GB).  I am currently using mysqldump to backup the database nightly.

Due to my comcast connection, oftentimes if I am running a dump manually my connection to the server will timeout before the dump is complete causing me to have to rerun the dump.  [I currently run a cron that does the dump nightly, this is just for dumps that I run manually.]

Is there a way to speed up the dumps for the connection timeout issue, but also to limit the time the server is occupied with this process?

BTW, I am currently working on reducing the size of the overall database to resolve this issue.

The main bottleneck in the dump like this is drive I/O. You are reading a load of data and writing it again. You can speed this up in a number of ways:

Make sure your output is going to a different drive(s) than the one(s) the database files are stored on - this will make a massive difference with spinning disks as the drive heads will not be constantly flicking between the location being read from and the location being written to.
The output of mysqldump will be very compressible, so if you can not separate the output from the input as mentioned above pipe the output through gzip or similar. This will reduce the amount of writing being done (so reduce the overall IO load, and the amount of head movement) at the expense of some CPU time (which you may have a lot of spare at these times anyway).
Also, (as well or instead of compression) pass the output through a pipe utility (like pv) that supports large write buffers to group blocks written to the drives together more, again to reduce the effect of head-movement latency - this will make quite a difference if using the --quick option to reduce the RAM impact of backing up large tables).
Only run your backup process when IO load is otherwise low.

You may be fixing the wrong issue though: it might be easier to address the connection drops instead (though reducing the I/O load imposed by your backups will help reduce the effect you have on other users so is worth trying anyway). Could you run your manual backups through screen (or similar tools like tmux)? That way if your connection to the server drops you can just reconnect and reattach to the screen session without any processes getting interrupted.

If you are sending the data directly over the connection (i.e. you are running mysqldump on your local machine against a remote database, so the dump appears locally) you might be better off running the dump on the server first, compressing as needed, then transferring the data over the network using a tool (such as rsync) which supports partial transfers so you can resume the transfer (instead of restarting) if a connection drop interrupts it.

As part of your "reducing the size of the overall database to resolve this issue" I would guess that a large chunk of your data does not change. You might be able to move a large chunk of the 1.2Gb from that main table off into another and remove that from those that are copied by the mysqldump call. You don't need to backup this data every time if it never changes. Splitting data between tables and databases this way is usually referred to as data partitioning and can also allow you to spread the data and I/O load over multiple drives. High-end database have built in support for automatic partitioning, though in mysql you will probably have to do it manually and alter your data access layer to account for it.

Straying off-topic for this site (so you should probably nip over to ServerFault or SuperUser to ask if you need more detail): If you seem to be losing connections due to inactivity, check the options in your SSH server and SSH client to make sure keep-alive packets are enabled and being sent often enough. If seeing drops even if the connection is active you could also try using OpenVPN or similar to wrap the connection - it should handle a short drop, even a complete drop if your entire connection is down for a few seconds, such that the SSH client and server don't notice.

I think the question is about how to restore faster from mysqldump's created dump files, not a different backup solution.

One of the ways, you can do this is by creating groups of tables in your schema, and create a separate DB user for each group then finally use MySQL permissions to not allow tables to be inserted to using all but one DB user.

This is a proven, fast, almost parallel technique but not 100% sure, how long it will take to restore from large dumps like 500G or so. But in my humble opinion, you need something parallel. Checkout the link below for an example.

[Fast, parallel restore from SQL dumps (mysqldump) for MySQL][1]

http://geeksww.com/tutorials/database_management_systems/mysql/tips_and_tricks/fast_parallel_restore_from_sql_dumps_mysqldump_for_mysql.php

"Fast, parallel restore from SQL dumps (mysqldump) for MySQL"


creating view on "oracle view" I am using oracle 12c. We have an oracle view ALL_SCHEUDLER_JOBS. As we will loosing the access to this table, I create a view on it as below :

the below function is placed inside a package called PCK_SUP which is in SCH1

function read_scheduler_jobs return t_scheduler_jobs pipelined
    as
    l_scheduler_jobs all_scheduler_jobs%ROWTYPE;
    cursor c
    is select * from all_scheduler_jobs where owner in ('SCH1', 'SCH2');
    begin
      open c ;
      loop
        fetch c into l_scheduler_jobs;
        exit when c%NOTFOUND;
        pipe row(l_scheduler_jobs);
      end loop;
      close c;
      return;
    end read_scheduler_jobs;

create or replace view vis_scheduler_jobs as
select "OWNER","JOB_NAME","JOB_SUBNAME","JOB_STYLE","JOB_CREATOR","CLIENT_ID","GLOBAL_UID","PROGRAM_OWNER","PROGRAM_NAME","JOB_TYPE","JOB_ACTION","NUMBER_OF_ARGUMENTS","SCHEDULE_OWNER","SCHEDULE_NAME","SCHEDULE_TYPE","START_DATE","REPEAT_INTERVAL","EVENT_QUEUE_OWNER","EVENT_QUEUE_NAME","EVENT_QUEUE_AGENT","EVENT_CONDITION","EVENT_RULE","FILE_WATCHER_OWNER","FILE_WATCHER_NAME","END_DATE","JOB_CLASS","ENABLED","AUTO_DROP","RESTART_ON_RECOVERY","RESTART_ON_FAILURE","STATE","JOB_PRIORITY","RUN_COUNT","MAX_RUNS","FAILURE_COUNT","MAX_FAILURES","RETRY_COUNT","LAST_START_DATE","LAST_RUN_DURATION","NEXT_RUN_DATE","SCHEDULE_LIMIT","MAX_RUN_DURATION","LOGGING_LEVEL","STORE_OUTPUT","STOP_ON_WINDOW_CLOSE","INSTANCE_STICKINESS","RAISE_EVENTS","SYSTEM","JOB_WEIGHT","NLS_ENV","SOURCE","NUMBER_OF_DESTINATIONS","DESTINATION_OWNER","DESTINATION","CREDENTIAL_OWNER","CREDENTIAL_NAME","INSTANCE_ID","DEFERRED_DROP","ALLOW_RUNS_IN_RESTRICTED_MODE","COMMENTS","FLAGS","RESTARTABLE","CONNECT_CREDENTIAL_OWNER","CONNECT_CREDENTIAL_NAME" from table(PCK_SUP.read_scheduler_jobs)
readonly;

We have two schemas SCH1 and SCH2. I created the above function and view in SCH1.
If I run "select distinct owner from ALL_SCHEDULER_JOBS", I can see both SCH1 and SCH2.
But, when I do the same from VIS_SCHEDULER_JOBS, I cannot see SCH2. I only see SCH1.

Thanks for your help

Creating this custom view makes no sense. The view ALL_SCHEDULER_JOBS is accessible to everyone. You will not lose access to it, so creating VIS_SCHEDULER_JOBS is just pointless. ALL_SCHEDULER_JOBS lists the jobs the current user can access. You may lose access to the jobs, but not to ALL_SCHEDULER_JOBS view.

ALL_SCHEDULER_JOBS

If the Permission for the ALL_SCHEUDLER_JOBS is removed, i guess you will not be able to access inside the function as well.
So make sure that the function is created in a schema that has the access to ALL_SCHEUDLER_JOBS and provide an execute privilege to SCH1 schema.


SQL Server 2014 Concurrent input issue In table Orders, I store the orders we receive from all shops. Since an order can have multiple lines, among the columns there are OrderID and OrderLineID
where OrderID can be duplicated but OrderLineID has to be unique within an order.

Since orders can be amended, the stored procedure first checks if the received order's OrderLineID is already present in the table, and then decides to insert or to update. To do this we:

build the insert and update statements dynamically from the XML input
insert into customers table
insert into shippingAddresses table

and then the main table:

IF NOT EXISTS (Select 1 from Orders where OrderLineID=@OrderLineID ......)
INSERT INTO Orders () VALUES ()
ELSE UPDATE Orders SET ... WHERE OrderLineID=@OrderLineID

Or does the MERGE function offer better performances/control?

But the question is the following:

Due to line issue/server busy and so on, the Order message (or the modification), could be sent more than once, and we do not know in which sequence. Therefore, to avoid the Order arriving after the amendment, and therefore overwriting the amendment, we added a time column:

IF NOT EXISTS (Select 1 from Orders where OrderLineID=@OrderLineID)
INSERT INTO Orders () VALUES ()
ELSE UPDATE Orders SET ... WHERE OrderLineID=@OrderLineID AND LastModified<@CreatedTime

In this way if the later message is older than the previous, it has no effect on the table.

But, it could happen that the message and its modification are sent twice (or more) in so little timeframe that the latter message arrives before the former has been saved. Therefore the IF NOT EXISTS (Select 1 from Orders where OrderLineID=@OrderLineID) is TRUE for both executions of the stored procedure, and both times it generates an INSERT and we find duplicated rows.

Perhaps this can be avoided by simply setting OrderLineID as unique key?

I also read something about using:

set transaction isolation level serializable

but I'm not sure how this is handled. I not only want to avoid the duplicate rows but to be sure the later message executes the UPDATE query and not throw a unique key violation.

Community wiki answer:

MERGE has some issues you should be aware of, see Use Caution with SQL Server's MERGE Statement by Aaron Bertrand.

More specifically to your present situation, MERGE provides no additional protection against concurrency issues beyond an automatic containing transaction. You would need to add a SERIALIZABLE hint (or its synonym, HOLDLOCK) to the MERGE statement as well, see “UPSERT” Race Condition With MERGE by Dan Guzman. Note that an UPDLOCK hint is not necessary with MERGE.

For a more general overview of the issues and options, see the following by Michael J. Swart:

SQL Server UPSERT Patterns and Antipatterns
Mythbusting: Concurrent Update/Insert Solutions

A popular and robust solution is to add a WITH (UPDLOCK, SERIALIZABLE) hint to table access in the NOT EXISTS check, with a containing transaction around the whole UPSERT operation.

Using your example:

BEGIN TRANSACTION;

IF NOT EXISTS
(
    SELECT *
    FROM Orders WITH (UPDLOCK, SERIALIZABLE)
    WHERE OrderLineID=@OrderLineID
)
    INSERT INTO Orders () VALUES ()
ELSE
    UPDATE Orders
    SET ...
    WHERE OrderLineID=@OrderLineID
    AND LastModified<@CreatedTime;

COMMIT TRANSACTION;

Or equally:

BEGIN TRANSACTION;

INSERT INTO Orders ()
SELECT ...
WHERE NOT EXISTS
(
    SELECT *
    FROM Orders WITH (UPDLOCK, SERIALIZABLE)
    WHERE OrderLineID=@OrderLineID
);

IF @@ROWCOUNT = 0
BEGIN
    UPDATE Orders
    SET ...
    WHERE OrderLineID=@OrderLineID
    AND LastModified<@CreatedTime;
END;

COMMIT TRANSACTION;

The order of the INSERT and UPDATE operations can be switched so that whichever you expect to succeed most often is tried first. The hints must always be applied to the first operation.

For example, if updates are expected to be most numerous:

BEGIN TRANSACTION;

UPDATE Orders WITH (UPDLOCK, SERIALIZABLE)
SET ...
WHERE OrderLineID=@OrderLineID
AND LastModified<@CreatedTime;

IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO Orders ()
    SELECT ...
END;

COMMIT TRANSACTION;

The uniqueness constraint mentioned in the question is a good idea for consistency in general, but not strictly needed for the methods noted here. If you were to use Michael Swart's Just Do It pattern, a uniqueness guarantee would be required for correct operation.

As a reminder, if you prefer MERGE for whatever reason, you must use a SERIALIZABLE hint against the target table.

While it is true in general that locking hints should be avoided as far as possible, this is one example of a valid use case.

The serializable transaction guarantees that all your transactions that are running concurrently will make the same effect as they run one after another.

This means that if you have 2 transactions that wants to query the same rows in the same table, the one which starts later will wait until the first one commit/roolback.

For example we have 2 rows in table orderLabel where orderID = 1

12:00 Serializable Transaction 1 starts with query - select count(*) from orderLable where orderID = 1

12:01 Serializable Transaction 2 starts with query - insert into orderLabel(orderID) values(1) -- this will wait until Transaction 1 commit/roolback

12:02 T1 commits and returs 2

12:03 T2 inserts 1 row with orderID = 1 and commits.

In your case Serializable will do the work, because when it it reaches the line IF NOT EXISTS(Select 1 from Orders where OrderLineID=@OrderLineID ......)
It will put lock on that rows and no other transaction will be able to insert data with the same @orderLineID.


SQL Server 2016 out of disk space I'm running SQL Server 2016. I have a database that has run, apparently, out of space on the D: drive. The daily index rebuild started producing the error shown below.

  Msg 1101, Level 17, State 12, Line 3
  Could not allocate a new page for database 'MarketDataAnalysisDB' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

First thing I did was remove the logs from the D: drive onto the C: drive so that I could (hopefully) delete some older historic data and free up space.  I was able to move the log files but the indexes still did not run (same error as above).

So I set out to delete rows of data with the intent of freeing up space so that future rows could grow back into the database. Following the delete statement, and 4 1/2 hours later, I got a similar error

  Msg 1105, Level 17, State 2, Line 2
  Could not allocate space for object '' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

While the error information is very informative I can do none of the suggested items. There is nothing on the drive but the database file. When I started reading about shrinking the database I realized I don't really understand why I'm getting this error. I've attached an image of the used space information in hopes that someone who understands this better than I, can provide a safe way to reduce the database size. The goal is to allow new records to come in while I figure out a rolling partition implementation that is currently beyond my SQL skills to implement on my own.

The other option I might consider is purchasing a larger hard drive for the data. But, from what I see, there appears to be space while at the same time SQL Server thinks I'm out of space.

What is the best way to reduce the size of my database?

DB Usage/space details

[EDIT]: I didn't realize it right away, but, my attempt to delete rows that took 4 hours and the log file located on the c: drive which had nearly 200GB free filled the log file so now the c: drive is out of disk space! What to do?

What to check in SSMS:

Right click on the database in question, click "Properties", then choose the "Files" tab.

Note all the files for the database, and the drives they are on. Note which files have auto-growth turned on, and (of those) which have a maximum file size.

Switch to the "Options" tab.

Check the recovery model (upper right corner). Is it "Full", "Simple", or "Bulk-logged"?

Repeat the first step for the system database tempdb.

Using Windows/File Explorer, check how much space is available on each of the drives you noted above.

Why you're having problems:

The index rebuild process takes up extra space in either your database itself, or in tempdb (depending on the settings for the index when it was created). In your case, it seems save to say the rebuild uses your database, not tempdb. Once the index is completely rebuilt, this space is released as available again; that's why it looks like you have free space in your data file, but you run out during the rebuild process.

Note that the database rebuild writes data to the transaction log, tracking the changes it's making, in case they have to be rolled back. The precise behavior of the transaction log depends on the recovery model of your database.

Databases using the "Simple" recovery model need to keep the details of every transaction around until the transaction is completed. Once the transaction is completed, the log space can be released to be reused. So, you can run out of space in your transaction log during a transaction, but have almost no space in use when you look at the log ten minutes later.

Databases using the "Full" recovery model (or "Bulk-logged") keep transaction information around until the transaction log is backed up. This means that a database in "Full", even with a regular full database backup, but with no transaction log backups scheduled, will eventually fill up, as transactions that completed months or even years ago must be kept in the log.

The main reason to use the "Full" recovery model is to allow for "point-in-time" recovery. You can combine your full backups with the string of transaction log backups to recover the database up to any point in time from when the full backup was taken, up to when the most recent transaction log backup was taken.

If you want the option to restore to a point more recent than your last full backup, then use the "Full" recovery model, and schedule transaction log backups to happen often enough that you don't mind losing the data that wasn't backed up. If you don't need transaction log backups (and you're sure about it), then put your DB into the "Simple" model.

A bit of advice: if you're using "Full" but haven't been backing up your transaction logs, you have the option of taking that first transaction log backup, if you have enough space to hold it. If you don't, you can basically "start over" by:

Shifting the recovery model to "Simple". This will mark all completed transactions as being OK to release. You can use the T-SQL CHECKPOINT command to ensure that the space is released immediately.
Switch back to "Full"
Immediately take a full backup of the database
Immediately after that, make sure your regular transaction log backup is set up.

This is one of the few scenarios where it actually makes sense to shrink your transaction log. If it's very large, try cutting it in half. Ideally, you don't want to shrink the file so small that it has to grow for normal operations. If you're looking for a good target size, I've heard the recommendation that you size the transaction log to twice the size of your largest index.

As noted, an index rebuild does write data to the transaction log. So, if you're using the "Full" model, and you do have transaction log backups running, you may want to run them more often when the rebuild is happening. I did this on one of my DBs a few years back - my usual schedule for transaction log backups was every 15 minutes, but I bumped that up to every 5 minutes during the rebuild process.

So, if all of that's set up fine, check to see if your drives are actually full, or if your files have an upper growth limit (or don't grow). If you still have drive space, then you can:

Manually increase the size of the file (on the afore-mentioned "Files" tab);
If auto-growth is not allowed, allow it (you can set a maximum if you like); or
If auto-growth is allowed, but a maximum is set, then increase the maximum (or remove it).

If you don't have any free disk space, and your transaction log is properly sized - then you need more disk space.

There's a way to put a max size on files in SQL Server. You should check those to see if they are artificially restricting growth.

You also need to go to your network admin and ask nicely for a network drive. You may need to switch your SQL services to use an active directory account, then give them permissions to the network drive and move your data there.

If you've got multiple file groups (for example, 1 per CPU core) you can move them one at a time.


Effect of an index on update statements where update column is not in an index I constantly see people say that indexes slow down update, delete and insert. This is used as a blanket statement, as if it is an absolute.

While tuning my database to improve performance, I keep coming across this situation that seems to contradict that rule logically for me, and nowhere can I find anyone say or explain in any way otherwise.

In SQL Server, and I believe/presume most other DBMS, your indexes are created based on specific columns you specify. Inserts and deletes will always affect an entire row, so there is no way they will not affect the index, but updates seem a bit more unique, they can specifically affect only certain columns.

If I have columns that are not included on any index and I update them, are they slowed down just because I have an index on other columns in that table?

As an example, say in my User table I have one or two indexes, the primary key which is an Identity/Auto Increment column, and possibly another on some foreign key column.
If I update a column without the index directly on it, like say their phone number or address, is this update slowed down because I have indexes on this table on other columns in either situation? The columns I am updating are not in indexes, so logically, the indexes should not be updated, shouldn't they? If anything, I would think they are sped up if i use the indexes in the WHERE clause.

For a relatively fast modern system the addition of a single index to an OLTP table will probably be virtually undetectable from a performance standpoint for the vast majority of systems.  That said, you should not create needless indexes, and you probably should not create single-column indexes for every column in a table.

You are correct in the assumption that for many queries the presence of useful indexes will result in a very noticeable speed improvement.

Although your question appears to be around performance, there are several other potential issues around adding indexes, including but not limited to:

Time required to create the index may result in blocking while the index is added to the table.  The lock is very short lived, and most likely won't create a big problem.
Index changes result in execution plans being invalidated for any plans that reference the underlying table.  When those execution plans are recompiled, performance may change negatively for some queries.
Index modifications may result in queries returning errors where none were previously returned.  Take the case of a filtered index that was used to return dates contained in a varchar field; if the filter eliminated any rows that weren't dates, and that filter is subsequently changed, queries that relied upon that index may now fail when attempting to convert non-date data.
A new index may cause the order of execution to change resulting in possible deadlocks occurring where non occurred before.

If the update operation targets a non-indexed column of fixed size (like an integer), it shouldn't be slow generally speaking, but compared to a select statement, the update has to eventually be written on the slow disk as well.


How do others know what is delivery address and invoice address if they are in the same table? When I searched around, the recommended way is to use the invoice address and delivery address in the same table and use a field "type" to show what it is. For example
Addresses(Company, address, city, type)

Type = 1: The address is a delivery address
Type = 2: The address is a invoice address
Type = 3: The address both delivery address and invoce address (same address)
If someone does not look at my code that uses the database, they can not know what Type 2 is. Is it delivery, invoice or both? Do you usually clarify this and how? For example, if someone wants to search the database (outside my code that calls the database) for all billing addresses, they do not know if they should use type 1, 2 or 3 to get all invoice addresses from a database client.
Compared to if you had two tables, then the name of the table says what it is and whoever can easily pick out all the invoice addresses.
Delivery(Company, address, city)
Invoice(Company, address, city)

An address is an address. There’s no need to store the same address details twice because it’s used for a delivery address and also invoicing.
In your orders table you would have columns like delivery_address_id and invoice_address_id, they could have the same value in them.
If you wanted to list all addresses that were used as a delivery address you can do a simple semi-join
Select *
from address
where address_id in (select delivery_address_id from orders)

would have used two boolean fields "delivery" and "invoice", makes it logical and easy to query. also you can keep entries for later use that are neither type right now. putting this in one integer field just asks for trouble later as you realized, good instincts!


Merge 2 SQL statements into 1? Can anyone help me transforming these 2 SQL statements into 1?

$query2 = "SELECT DISTINCT `UID` FROM `ratings`";
$query3 = "SELECT `RID`,`rating` FROM `ratings` WHERE `UID` = $uid";

original php code:

$arrayAux=array();
$query2 = "SELECT DISTINCT `UID` FROM `ratings`";

$result2 = mysql_query($query2);

if(!$result2) return $arrayAux;

    while ($row2=mysql_fetch_array($result2,MYSQL_ASSOC))
    {
    $array2=array();
    $uid=$row2['UID'];

            $query3 = "SELECT `RID`,`rating` FROM `ratings` WHERE `UID` = $uid";
            $result3 = mysql_query($query3);
            if(!$result3) return $arrayAux;
            while ($row3=mysql_fetch_array($result3,MYSQL_ASSOC))
            {
                $rid=$row3['RID'];
                $rating=$row3['rating'];

                $array2[$rid] = $rating;
                }

    array_push($arrayAux, $array2);
        }
return $arrayAux;

Thank you!

select rid,rating,uid from ratings group by uid;


Case insensitive search of CLOBs in Oracle 12 Question: What is the most performant way to do a case-insensitive search  in a CLOB column?

I've simplified the query to easily reproduce the problem. The filedata table can have millions of rows. Many rows can have the same fileid. The fileid column is indexed. In cases where a particular fileid matches many rows, the proc is really slow.

select * from filedata where fileid=2148102100 AND REGEXP_LIKE (dataClobfield, 'Some Text Here', 'i');

This query completes in 130 seconds. 16 rows are returned by this query, but there 400k rows matching the fileid value (in other words, if I removed the regexp_like where clause, I would get 400k with a fileid of 2148102100).

If I change this to

select * from filedata where fileid=2148102100 AND dataClobfield like '%Some Text Here%';

It completes in 32 seconds but the search is case-sensitive, which does not meet the requirements.

I've also tried

select * from filedata where fileid=2148102100 AND LOWER(dataClobfield) like '%some text here%';

This returns in 83 seconds... better than the regexp_like, but still not great.

Other things I've read about haven't worked: setting the session NLS_SORT=BINARY_CI (doesn't work with CLOBs), using the DBMS_LOB.instr function (case sensitive). Ideally I'd like to get this down to 30 seconds. Any ideas?

Searching without any kind of index seems to be not a good idea. And an analysis without further details is difficult. But you can add resources to improve your speed.
It seems that the bottleneck of your query is CPU and not the disks. If disk speed is the limitation there would not be a difference between regexp and LIKE.

You can try to parallelize your query. If the work is done by 4 processes instead of one, it is possible that the query response tim is a quarter of the original time.
you can add an additional clob field that contains the content of dataClobfield  in uppercase. this field is used for searching the records you are interested in

What about using or to match both lower- and uppercase?

select *
from filedata
where fileid = 2148102100 and
      (dataClobfield like '%some text here%' or dataClobfield like '%SOME TEXT HERE%');

I might perform better than the 83-second case, as it avoids operation on column values.


mysql need help optimizing query I have a table containing servers logs, and another table containing rules to match certain and drop them.

The logs tables contains around 1 million rows, and there are about 20-30 rules.

The query runs very slowly, I wonder is there any way I can make it run faster. I tried adding indexes to logs.message, but it does not help, I also read that you cannot index a "LIKE" column.

I am a total newbie to database, so please forgive me if I am missing any important concepts. Thanks in advance.

CREATE TABLE `logs` (
  `log_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `criticality` varchar(255) NOT NULL,
  `hostname` varchar(255) NOT NULL,
  `source` varchar(255) NOT NULL,
  `message` varchar(4096) NOT NULL,
  `record_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `record_by` varchar(255) NOT NULL,
  PRIMARY KEY (`log_id`),
  KEY `idx_message` (`message`(255))
) ENGINE=InnoDB AUTO_INCREMENT=233523 DEFAULT CHARSET=utf8$$

CREATE TABLE `rules` (
  `rule_id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(255) NOT NULL,
  `type` enum('MATCH','DROP') NOT NULL DEFAULT 'DROP',
  PRIMARY KEY (`rule_id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8$$

select hostname, criticality, source, message, record_date
from eventlog.logs l1
where not exists (
    SELECT l.message, r.rule_id
    FROM eventlog.logs l,
         eventlog.rules r
    where l.message like r.content
      and l.log_id = l1.log_id
      and r.type = 'DROP'
) and (criticality = 'High'
        or criticality = 'Medium')
  and record_date > sysdate() - Interval 2 Day
order by l1.message;

UPDATE 1
explain results, it took around 10 seconds to finish the query.

+----+--------------------+-------+--------+---------------+---------+---------+--------------------+---------+-----------------------------+
| id | select_type        | table | type   | possible_keys | key     | key_len | ref                |  rows   | Extra                       |
+----+--------------------+-------+--------+---------------+---------+---------+--------------------+---------+-----------------------------+
|  1 | PRIMARY            | l1    | ALL    | NULL          | NULL    | NULL    | NULL               | 2101642 | Using where; Using filesort |
|  2 | DEPENDENT SUBQUERY | r     | ALL    | NULL          | NULL    | NULL    | NULL               |      16 | Using where                 |
|  2 | DEPENDENT SUBQUERY | l     | eq_ref | PRIMARY       | PRIMARY | 8       | eventlog.l1.log_id |       1 | Using where                 |
+----+--------------------+-------+--------+---------------+---------+---------+--------------------+---------+-----------------------------+

I agree with @ypercubeᵀᴹ, the comma join in the exists clause can be removed:

select hostname, criticality, source, message, record_date
from eventlog.logs l1
where not exists (
    SELECT 1
    FROM eventlog.rules r
    where l1.message like r.content
      and r.type = 'DROP'
)
and criticality in ('High', 'Medium')
and record_date > sysdate() - Interval 2 Day
order by l1.message;

I also replaced the selected columns in the subquery with 1 since it really does not matter what you select, and replaced the OR predicate with IN.

This query should do the trick:

SELECT hostname, criticality, source, message, record_date
FROM logs l
LEFT JOIN rules r ON l.message like r.content AND r.type = 'DROP'
WHERE (criticality = 'High'
        or criticality = 'Medium')
        and record_date > sysdate() - Interval 2 Day
        AND r.rule_id IS NULL
order by l.message;

but do not expect it to run fast, as long as you have a JOIN constraint using a like clause referencing two varchar columns, which do not even have an index on them.

Nevertheless it should be faster (in MySQL) than the exists subquery.


Oracle to PostgreSQL conversion In oracle: employees table contain more than 10 million data. For update I'm using rownum for faster update.

CREATE OR REPLACE PROCEDURE emp_up AS
begin
  loop
      update employees set commission_pct=0 where commission_pct is null and rownum <=10000;
      exit when sql%notfound;
      commit;
  end loop;
end;

In postgresql: postgresql does not support rownum. Is there any way to update employees table in postgres with the similar way in oracle?

In PostgreSQL a function is always be executed in one transaction. It does not matter if you loop over batches of 10.000 rows or just UPDATE the whole table.

Thanks to MVCC there should also no need to split the UPDATE in order to allow other processes to do something on the table, as the locks are very lightweight in this scenario.

So, the correct replacement would be:

CREATE OR REPLACE FUNCTION emp_up() AS
$func$
  update employees set commission_pct=0 where commission_pct is null
$func$
LANGUAGE plpgsql;

You can try this query:

UPDATE employees
    SET commission_pct=0
FROM
(
    SELECT *, Row_Number() OVER (ORDER BY employeeid) as rownum
    FROM employees
) SubQuery
where SubQuery.commission_pct is null
  and SubQuery.rownum <=10000


Theoretical writes/sec limit If I have solarwinds which has an alert set whenever we have more than 300 writes per second, and I know 300 writes per second is fine, and the writes per second often goes over 6500, how can I work out what the theoretical limit of writes per second is, so I can alter the alert thus making it actionable?

If "The number of writes should not exceed 85% of the disk capacity" how can I work out the number of writes that are possible with my current disk configuration?

One way to do this will be to monitor the writes/sec for weeks or a month and then come up with a number based on what the max values that you are seeing.  Obviously this is a rough guide, and as your workload changes you would have to adjust the threshold as well.

You could also use disk benchmarking tools to test the throughput of your disk subsystem.  diskspd is something which we use.  Please also note that writes/sec is dependant on the block size that is used.  Based on what the drive is used for(data or transaction log)  and also based on the type of workload, SQL Server could use different.  You can read about SQL IO sizes here.  Obviously if you run this on a production system, you might have a performance impact.

A better performance counter to alert on would be sec/Write or sec/Read.  You can read about it here and here

imho,

var jsonHDDiops = [
    { "n": '[HDD-15K]', "v":      175 },
    { "n": '[HDD-10K]', "v":      125 },
    { "n": '[HDD-7K2]', "v":      90 },
    { "n": '[HDD-5K9]', "v":      75 }
  ];
var jsonRAIDpenalty = [
    { "n": '[RAID 10]', "v":      2 },
    { "n": '[RAID DP]', "v":      2 },
    { "n": '[RAID 1]',  "v":      2 },
    { "n": '[RAID 0]',  "v":      1 },
    { "n": '[RAID 5]',  "v":      4 },
    { "n": '[RAID 6]',  "v":      6 } //?
  ];

 function dvFiops(RAIDtype,numberHDD,HDDtype,percentR,jsonHDDiops,jsonRAIDpenalty){
   if (percentR>100 || percentR<0) { return 0;}
   var percentW =100-percentR;
   var iops=0; var RAWiops=0; var Fiops=0;
   var RAIDpenalty=1;
   for (var ic = 0; ic < jsonHDDiops.length; ++ic) {
     if(jsonHDDiops[ic].n === HDDtype) {iops = jsonHDDiops[ic].v;}
   }
   for (var ic = 0; ic < jsonRAIDpenalty.length; ++ic) {
     if(jsonRAIDpenalty[ic].n === RAIDtype) {RAIDpenalty = jsonRAIDpenalty[ic].v;}
   }
   RAWiops=iops*numberHDD;
   Fiops= ((RAWiops*percentW/100)/RAIDpenalty)+(RAWiops*percentR/100);
   return Fiops;
   }

https://en.wikipedia.org/wiki/IOPS => edit jsonHDDiops value

85% write => percentR=15

P.S. Example:


What does "collation" mean? I am learning sqlite from a book which has mentioned collation and collating  sequence multiple times. What does it mean exactly in the database world?

Basically, how to sort and compare strings.

Examples:

Comparing:

Accent: à vs ä vs a
Case: A vs a

Sorting:

Swedish: z -> å -> ä -> ö
Danish: z -> æ -> ø -> å

Special character equivalence

German ß = ss

Wikipedia

Some SQL (SQL Server 2008+)

DECLARE @foo TABLE (bar varchar(2))
INSERT @foo VALUES ('z'), ('æ'), ('ø'), ('å'), ('ss'), ('ß'), ('a'), ('ä')

SELECT * FROM @foo ORDER BY bar COLLATE Finnish_Swedish_100_CI_AS
SELECT * FROM @foo ORDER BY bar COLLATE Norwegian_100_CI_AS
SELECT * FROM @foo ORDER BY bar COLLATE Latin1_General_100_CS_AS --german (western europe) and default
SELECT * FROM @foo ORDER BY bar COLLATE German_PhoneBook_100_CS_AS

Sort order. Punched cards were collated. Flat file records are collated. This is not unique to databases.

It is self explanatory for text, but for binary data stored as characters it can be trickier.


how to JOIN on not null Im trying to create referals system. I am doing that by passing into during database every registaration all referals (first, second and third "stage").

to clarify, if user ID 3 registers from ref. of user ID 2, and user ID 2 was registered from ref of ID 1, the values for user ID 3 would look like:

`r1` = 2, `r2` = 1, `r3` = NULL

To read from database who is registered using some id's ref link with whole path, I figured out this query:

SELECT `sub`.`id` AS first,`sub2`.`id` AS `second`,`sub3`.`id` AS `third`

FROM `users` AS `usr`

JOIN `users` AS `sub` ON `sub`.`r1` = `usr`.`id`
JOIN `users` AS `sub2` ON `sub2`.`r2` = `usr`.`id`
JOIN `users` AS `sub3` ON `sub3`.`r3` = `usr`.`id`

WHERE `usr`.`id` = :CurrentUserId ;

(:CurrentUserId is PDO placeholder for PHP for int value)

Although, its problematic, as it works only for accounts with all 3 refers into past, so people with only first line or first and second line, but without third will not be listed. In other words, that would mean, if there is user, that has following values

r1 = 2, r2 = 1, r3 = NULL

is not listed. It works if r3 is equal to something.

I could work it around by sending 3 queries to database, but I am sure, that there is better way.

(Im not very expirienced with databases)

Use left joins instead of inner joins so that records from users will still display even if r1, r2, and r3 are null:

SELECT sub.id AS first,sub2.id AS second,sub3.id AS third

FROM users AS usr

LEFT JOIN users AS sub ON sub.r1 = usr.id
LEFT JOIN users AS sub2 ON sub2.r2 = usr.id
LEFT JOIN users AS sub3 ON sub3.r3 = usr.id

WHERE usr.id = :CurrentUserId ;

However, based on your example I think you may need the following query instead, changing the join conditions so that sub, sub2, and sub3 represent referred users:

SELECT sub.id AS first,sub2.id AS second,sub3.id AS third

FROM users AS usr

LEFT JOIN users AS sub ON sub.id = usr.r1
LEFT JOIN users AS sub2 ON sub2.id = usr.r2
LEFT JOIN users AS sub3 ON sub3.id = usr.r3

WHERE usr.id = :CurrentUserId ;

Simply add the condition you expressed in English:

JOIN `users` AS `sub` ON `sub`.`r1` = `usr`.`id`
JOIN `users` AS `sub2` ON `sub2`.`r2` = `usr`.`id` OR `sub2`.`r2` IS NULL
JOIN `users` AS `sub3` ON `sub3`.`r3` = `usr`.`id` OR `sub3`.`r3` IS NULL


Restoring a single database from mysql enterprise full backup I am having command to restore all databases from full dump taken by enterprise full backup .Can anyone help me how to restore single database from full dump .

Try this:

mysql -u root -p --one-database destdbname < alldatabases.sql

In the above code substitute destdbname with the database name you want to restore, and alldatabases.sql with the name of your full DB backup.

If you would rather just extract the database dump of the single database from the --all-databases dump file, you can do this with sed using this command:

sed -n '/^-- Current Database: `dbname`/,/^-- Current Database: `/p' alldatabases.sql > output.sql

Where dbname is replaced with the database name of the database to extract, and alldatabases.sql is the name of your dump file. The result will be saved into the file output.sql.


Connection to local SQL Server 2012 can be established from SSMS 2008 but not from SSMS 2012 I have two local SQL Server instances running on my local machine. The first is SQL Server 2008 R2 Enterprise Edition (named MSSQLSERVER) and the 2nd is SQL Server 2012 Business Intelligence Edition.

My problem is with SSMS 2012 which can connect to distant servers but not the local 2012 instance; I can however connect to this instance from SSMS 2008.

The error message I get when trying to login is

  Login Failed. The login is from an untrusted domain and cannot be used with Windows Authentication. (Microsoft SQL Server, Error: 18452)

I must point out that I don't have the necessary privileges to access SQL Server Configuration Manager (blocked by group policy).

Any help would be appreciated.

I saw an item on Pinal Dave's blog recently that may answer your question.  He suggests adding the line
127.0.0.1 localhost
to your hosts file

The full article can be found here

I do note, however, the situation he refers to is a machine that is not part of a domain - if your machine is, then this may not solve it


Sql Server 2016 Counter Reporting Zero I utilize the resource governor on my 2016 Sql Server.  In prior 2016 sql server  versions, this query:

select *
from master.sys.dm_os_performance_counters opc
where opc.object_name = 'SQLServer:Resource Pool Stats'
and counter_name = 'CPU usage target %'

Would yield the target value of CPU usage % for the resource pool based on the resource pool configuration settings and system load.  However, now they are always zero.  I also used perfmon to confirm this (along with running legit queries).  Does anyone know why or if this counter changed?  According to the msdn article, it should still work.

You posted a bug on this at Microsoft Connect:

SQL Server 2016 SQLServer:Resource Pool Stats:CPU usage target % Always Returns Zero - by HunterX3

Given that Microsoft didn't respond there either, it's probably a bug. Open a support case with Microsoft, and if it turns out to be a bug, they refund your support fee.

Try this and see if this gives a value other than zero:

SELECT  perfCount.object_name ,
    perfCount.counter_name ,
    CASE WHEN perfBase.cntr_value = 0 THEN 0
         ELSE ( CAST(perfCount.cntr_value AS float) / perfBase.cntr_value ) * 100
    END AS cntr_Value
FROM    ( SELECT    *
          FROM      sys.dm_os_performance_counters
          WHERE     object_name = 'SQLServer:Resource Pool Stats'
                    AND counter_name = 'CPU usage %' ) AS perfCount
        INNER JOIN ( SELECT *
                     FROM   sys.dm_os_performance_counters
                     WHERE  object_name = 'SQLServer:Resource Pool Stats'
                            AND counter_name = 'CPU usage % base' ) AS perfBase
            ON perfCount.object_name = perfBase.object_name;


Google Big Query Invalid time zone: +0000 Getting the error

Query Failed
Error: Invalid time zone: +0000

Is there a way to ignore time zones or default everything to UCT
This is my where condition

WHERE TIMESTAMP(FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%E6S%z", PARSE_TIMESTAMP("%Y%m%d", date )),"UCT") = TIMESTAMP('2018-03-05 00:00',"UCT")

date column is string in this format 20180307

Community wiki answer originally left by the question asker as a comment:

The fix was removing the timestamp formatting from FORMAT_TIMESTAMP

TIMESTAMP(FORMAT_TIMESTAMP("%Y-%m-%d", PARSE_TIMESTAMP("%Y%m%d", ga.date )))

You're specifying UCT, an invalid time zone as it is a misspelling. The time zone you are looking for is UTC.

Incorrect:

... TIMESTAMP('2018-03-05 00:00',"UCT")

Correct:

... TIMESTAMP('2018-03-05 00:00',"UTC")


Not able to start mysqld as windows service as well as from command line in windows 8 I recently downloaded mysql-5.7.9-winx64.zip and tried to run mysqld from command line but it does not starts gives me following error.

ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

I have updated system path variable to have "D:\MySQL\mysql-5.7.9-winx64\bin"
and added mysql as system service

"D:\MySQL\mysql-5.7.9-winx64\bin" --install

It's still does not start when I restart my system.

Also when I tries to run from services.msc ==> MySQL ==> Start It gives me following error:

MySQL Services on Local Computer started and then stopped. Some services stops automatically if they are not in use by other service or programs.

Connecting from the localhost shouldn't be an issue, but edit your "C:\ProgramData\MySQL\MySQL Server 5.7\my.ini" file and change the bind-adress from 127.0.0.1 to the external IP address. Also, set mysql skip-networking to off. Finally, set a rule on windows firewall to allow connections on port 3306.

Goto "services.msc" from the run dialog. Then search for the MySQL server and try starting the server manually.


Run multiple stored procedures in sequence I have a stored procedure written in T-SQL (SQL Server 2008 R2). It is a master procedure that essentially call multiple other subprocedures in sequence. The call and error handling is identical for each one except for the name of the procedure.

In an OO language I would use an abstraction such as an interface or functor and loop over a bunch of objects. That does not work in SQL, but I want to find some way to make this code more concise with less copy and paste repetition. Yes, I know that fundamentally SQL is about set operations and does not support what I want to do very well, but if there is a way, it will make the code much more concise. I also need to capture the result of each stored procedure invocation and do something with it which is not relevant to this question.

Here is what I have so far:

CREATE PROCEDURE dbo.testproc
AS BEGIN
  DECLARE @step INT, @result INT
  DECLARE @tbl TABLE([step] INT, [pname] NVARCHAR(40))

  INSERT INTO @tbl ([step], [pname]) VALUES (1, N'proc1')
  INSERT INTO @tbl ([step], [pname]) VALUES (2, N'proc2')
  INSERT INTO @tbl ([step], [pname]) VALUES (3, N'proc3')
  -- Potentially many more procedures here

  SET @step = 1
  WHILE @step <= (SELECT MAX([step]) FROM @tbl)
  BEGIN
    DECLARE @sql NVARCHAR(60)
    SET @sql = N'EXEC @result = dbo.' + (SELECT [pname] FROM @tbl WHERE [step] = @step)
    EXEC (@sql)
    IF @result <> 0
    BEGIN
      INSERT INTO SomeTable error code and step number
      RETURN
    END
   SET @step = @step + 1
  END
END
GO

When I run the procedure, SQL Server gives me an error because the @result variable that is part of the dynamic SQL is not defined as part of the batch that is contained in the @sql variable. If I modify it like this:

    SET @sql = N'EXEC dbo.' + (SELECT [pname] FROM @tbl WHERE [step] = @step)
    EXEC @result = (@sql)

I get a syntax error.

This works fine except for retrieving the return value of the subprocedures. Is there a way to accomplish my stated goal, and if so, how?

NOTE: based on what I asked here, a cursor would sound like a better implementation than a WHILE loop especially given the table variable. Part of the code that is not essential to this question involves knowing the iteration number, hence the use of a loop control variable.

If you don't need the result values later you can do it shorter this way:

-- procedures to test with
create proc proc1 as print '1' return 0
GO
create proc proc2 as print '2' return 1
GO
create proc proc3 as print '3' return 0
GO

if object_id('dbo.testproc') is null exec('create procedure dbo.testproc as return(0)')
GO
alter PROCEDURE dbo.testproc
AS
  DECLARE @result INT
        , @sql nvarchar(max) = N''
  DECLARE @tbl TABLE([step] INT, [pname] nvarchar(513))

  INSERT INTO @tbl ([step], [pname])
  VALUES (1, N'proc1'),
         (2, N'proc2'),
         (3, N'proc3')
  -- Potentially many more procedures here

  select @sql = @sql + 'exec @result = ' + QUOTENAME(pname) + ' if @result <> 0 return;'
  from @tbl order by step

  exec sp_executesql @sql, N'@result int output', @result output

  if @result <> 0
    begin
      print 'do your cleanup'
    end
GO

exec testproc

The variable engine will produce the following query batch

exec @result = [proc1] if @result <> 0 return;
exec @result = [proc2] if @result <> 0 return;
exec @result = [proc3] if @result <> 0 return;

When the batch is executed it will stop executing when there is a @result not zero and keep that value in the output parameter.

More traditional looping

If you want to loop over the procedures. Since there are no parameters (or the parameters are all the same) you can simply call exec @result = @proc

if object_id('dbo.testproc') is null exec('create procedure dbo.testproc as return(0)')
GO
alter PROCEDURE dbo.testproc
AS
  DECLARE @result INT
        , @proc sysname
  DECLARE @tbl TABLE([step] INT, [pname] nvarchar(513))

  INSERT INTO @tbl ([step], [pname])
  VALUES (1, N'proc1'),
         (2, N'proc2'),
         (3, N'proc3')
  -- Potentially many more procedures here

  declare c cursor fast_forward local
  for select pname from @tbl order by step

  open c
  fetch next from c into @proc
  while @@FETCH_STATUS = 0
    begin
      exec @result = @proc
      if @result <> 0
          BREAK

      fetch next from c into @proc
    end
  close c
  deallocate c

  if @result <> 0
    begin
      print 'do your cleanup'
    end

GO

exec testproc

Try using exec sp_executesql @sql,'@result int output', @result=@result output.
This way you can pass variables in and out to your dynamic query.


Pass parameters to a procedure with SQLcmd I use this SQL command:

 sqlcmd -v first_date="30/09/2015" last_date="15/09/2016"

And I would like to pass that parameters to a stored procedure:

ALTER PROCEDURE [dbo].[_testProcedure]
    WITH EXECUTE AS CALLER
AS
BEGIN

    DECLARE @F_FIRST_DATE_DT VARCHAR(50)
    DECLARE @F_LAST_DATE_DT VARCHAR(50)

    SET @F_FIRST_DATE_DT = N'$(first_date)'
    SET @F_LAST_DATE_DT = N'$(last_date)'

But it return me an error:

  Incorrect syntax near 'first_date'

What is the correct way to pass variables to stored procedure?

There is no special way to pass SQLCMD variables to a Stored Procedure. Stored Procedures are encapsulated code and cannot be passed SQLCMD variables as you are attempting to do in the code posted in the Question.

Stored Procedures take input parameters to pass in values. And those input parameters should be of the correct datatype for the data being passed in; you do not need to accept VARCHAR for date values simply because SQLCMD variables do not specify their type.

CREATE PROCEDURE [dbo].[_testProcedure]
    WITH EXECUTE AS CALLER
(
  @FirstDate DATETIME,
  @LastDate DATETIME
)
AS
SET NOCOUNT ON;

SELECT DATEDIFF(DAY, @FirstDate, @LastDate) AS [NumberOfDays];

Then you call it in one of the following ways:

EXEC [dbo].[_testProcedure] first_date, last_date;
EXEC [dbo].[_testProcedure] @FirstDate = first_date, @LastDate = last_date;

Now, IF you are executing the above T-SQL from SQLCMD, then you can pass in SQLCMD variable into that T-SQL, which is the current query:

-Q "EXEC [dbo].[_testProcedure] $(first_date), $(last_date);"

or:

-Q "EXEC [dbo].[_testProcedure] @FirstDate = $(first_date), @LastDate = $(last_date);"

Also, you need to pass in SQLCMD variable values using single-quotes, not double quotes. Try the following to see why:

SQLCMD -Q "PRINT $(FirstDate);" -v FirstDate='12/05/2006'

Returns:

12/05/2006

But:

SQLCMD -Q "PRINT $(FirstDate);" -v FirstDate="12/05/2006"

Returns:

0

My example sqlcmd -i C:\CFDIxml\SAT3.3\UUID_QAD\sqljob.sql -o W:\einvoicemx\folios.csv

if errorlevel 1

and explain in Spanish in
https://www.programacionparatodos.com/2019/09/ejecutar-script-sql-desde-cmd.html


Find Max Value for each month for the last 3 months, properly I have a query going that gets data for an ID for the last 3 months.  I need to tweak it so I get the highest value for each of the three months.  I've tried a couple of things with the aggregate function MAX, but I'm not getting anywhere.

I'm trying to get the max value for each of the past months ....

Here's the data from the query, currently sorted by date (asc):

ID      Date               Value
12410   01/03/2017 12:17    0.000178
12410   01/10/2017 11:36    0.000186
12410   01/17/2017 11:27    0.000189
12410   01/24/2017 13:09    0.000182
12410   01/31/2017 10:37    0.000169
12410   02/07/2017 11:03    0.000214
12410   02/14/2017 11:52    0.000176
12410   02/21/2017 10:51    0.000200
12410   02/28/2017 12:29    0.000194
12410   03/07/2017 08:39    0.000206

Here's the query:

select AnalysisID as "ID" , AnalysisDateTime as "Date", AnalysisValue as "Value" from AnalysisValueTbl
where
AnalysisID = 12410 and DatePart(m, AnalysisDateTime) = DatePart(m, DateAdd(m, -3, getdate()))
and DatePart(yyyy, AnalysisDateTime) = DatePart(yyyy, DateAdd(m,-3, getdate()))
or
AnalysisID = 12410 and DatePart(m, AnalysisDateTime) = DatePart(m, DateAdd(m, -2, getdate()))
and DatePart(yyyy, AnalysisDateTime) = DatePart(yyyy, DateAdd(m,-2, getdate()))
or
AnalysisID = 12410 and DatePart(m, AnalysisDateTime) = DatePart(m, DateAdd(m, -1, getdate()))
and DatePart(yyyy, AnalysisDateTime) = DatePart(yyyy, DateAdd(m,-1, getdate()))
order by AnalysisValue desc

For SQL Server, you could do something like this.

DECLARE @t TABLE (Id INT, DateVal DATETIME, ValueVal DECIMAL(18, 9));

INSERT @t ( Id, DateVal, ValueVal )
SELECT Id, DateVal, ValueVal
FROM (
    VALUES
    (12410,   '01/03/2017 12:17',    0.000178),
    (12410,   '01/10/2017 11:36',    0.000186),
    (12410,   '01/17/2017 11:27',    0.000189),
    (12410,   '01/24/2017 13:09',    0.000182),
    (12410,   '01/31/2017 10:37',    0.000169),
    (12410,   '02/07/2017 11:03',    0.000214),
    (12410,   '02/14/2017 11:52',    0.000176),
    (12410,   '02/21/2017 10:51',    0.000200),
    (12410,   '02/28/2017 12:29',    0.000194),
    (12410,   '03/07/2017 08:39',    0.000206)
) x (Id, DateVal, ValueVal);

SELECT  DATEPART(MONTH, t.DateVal) AS [DateVal],
        MAX(t.ValueVal) AS MaxVal
FROM @t AS t
WHERE t.DateVal >= DATEADD(MONTH, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
GROUP BY DATEPART(MONTH, t.DateVal);

Updated WHERE clause:
Flattening dates is weird, and my previous query got you today's date minus three months. To get back to the first of three months ago, you have to turn some tricks.

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS [First Of This Month],
       DATEADD(MONTH, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AS [First Of Three Months Ago]

You can either take some time trying to understand this, or keep a cheat sheet of how to do it handy so you don't have to remember awful date math ;)

With PostgreSQL this is pretty easy using date_trunc

SELECT date_trunc('month', date), max(value)
FROM table
GROUP BY date_trunc('month', date)
WHERE date >= date_trunc('month', CURRENT_DATE - '3 months'::interval);


Oracle 10g slow performance on first query We have an application which we recently upgraded and is having bad performance issues. Any ideas would be much appreciated.

The main issue is windows taking ages (minutes) to open because the initial query to fill their list boxes is taking so long. The second time a user opens the window its fine.

We would expect this to be application cacheing but we don't think so. We see similar problems using Crystal Reports and using command line SQL queries.

We have increased the RAM available to the database.

Another part of our business has a slightly different version of the same application and database and doesn't get this problem.

The time to complete a query can be 30 minutes for the first time in a session and 30 seconds for subsequent executions.

The usual reason for such issues is that the first execution of the query requires physical reads to fetch the required blocks from disk, and the second and subsequent executions find that they are already cached.

You can test this by running the queries from SQL*Plus (and timing them) prior to first opening these windows of which you speak, to see if that affects performance.

If you can come back with comparison of the timings when running the queries through SQL*Plus and then through the application it would help further analysis.

The reason why its take a more times in first because oracle caches the results. If you change the SQL then Oracle considers it a different query and won't serve the results from the cache but executes the new query.

These kinds of effects can happen even on index scans, but are especially exacerbated on expensive operations such as full table scans (because of a large number of pages touched).

Check you query plan and see if there are any expensive operations that might touch large number of pages...

and It is a hard question how to speed up first execution. You'll need to post explain plan and probably you'll have to answer further questions if you want to get help on that.

Thanks.


Trying to use MySQL Workbench with TCP/IP over SSH - failed to connect I can't connect using TCP/IP over SSH connection in MySQL Workbench from a PC. What's going on?

I created a MySQL 5.1 database on an Ubuntu server mysql.myhost.com. I can access it locally. MySQL Workbench (PC) offers to make a connection via TCP over ssh. It runs on port 3306 on the remote server where command-line mysql works fine.

I used the following session details:

Connection Method: TCP/IP over SSH.
SSH Hostname: mysql.myhost.com:3306
SSH username: my linux login
SSH public key file: my local public key file
MySQL hostname: 127.0.0.1 MySQL
Server Port: 3306
Username: root

I get an error message when I try to connect:
"Failed to connect to MySQL at 127.0.0.1:3306 through SSH tunnel at mysql.myhost.com with user root"

"Can't connect to MySQL server on '127.0.0.1' (10061)"

As another test - I set up a SSH tunnel with port 3306 using Putty and I can connect OK using MySQL Workbench through that tunnel which forwards connections to my local 3306 to the remote server as described above. But I can't get "TCP/IP over SSH" working in Workbench.

Secondary question: when Workbench asks for "Path to SSH public key file" doesn't it really need my private key file?

I stumbled upon this question when I myself had encountered this error. I could finally figure out the configuration.

I didn't touch anything in /etc/mysql/my.cnf which already has bind_address = 127.0.0.1. So only localhost can connect.
I use OpenSSH server. So in its config file /etc/ssh/sshd_config I changed from no to yes the param responsible for TCP forwarding, thus AllowTcpForwarding yes.
Finally I have the following entered in MySQL WorkBench.

SSH Hostname: 192.168.0.8:22 (my SSH server listens to port 22)
SSH Username: sshuser
SSH Key File: *C:\Users\windowsuser\.ssh\id_rsa* (should be private key, even though it says public)
MySQL Hostname: 127.0.0.1 (this should not be changed, since MySQL server by default is bound to localhost only which I didn't change)
MySQL Server Port: 3306 (also default)
Username: root

The only remaining thing for you is to configure correctly your SSH server to work with keys rather than passwords. Hope this will help someone.

Only what I found... often I'm creating users on SSH server without shell (such as /sbin/nologin) to prevent them be able to login to a server and create files and etc there... (for production systems we're doing it on firewalls).

In a regular Linux environment after that, you still can forward ports after that such as:

ssh -Nf -L 3306:%mysql_ip%:%mysql_port% %ssh_host%

and after that connect to it from local workstation as:

mysql -h localhost:3306 -u %mysql_user% -p

But workbench giving an error that it can't connect to MySQL... If you will change the shell for that user to, let say, /bin/bash - everything works fine after that.

No idea why Workbench is requiring local shell on the remote SSH server.


MySQL is insanely slow on fresh installed Ubuntu 17.10 Yesterday I installed Ubuntu 17.10 on my desktop and today I wanted to migrate a MySQL datababase (php artisan migrate:fresh with laravel), but my database was very slow. The migrations only, took around 30 seconds.

On my macbook these migrations take only a few seconds (under 5 seconds).

I tried google and got a lot of settings I could change in my.cnf.
For example the following settings:

[mysqld]
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64

This didn't made it any faster. I also found the option for slow query logging, but the output file is empty after the migrations.

Does someone have any other solutions?

Ps. I removed the settings from my.cnf because it didn't made it faster and I wasn't sure whether to keep the settings or not.

I have mysql-server-5.7.

long_query_time = 1 (or possibly a lower value); this will help get something useful in the slowlog.

With rare exceptions, "You can't tune you way out of a performance problem."

How much RAM?

Find some slow SQL so we can discuss it.  Please provide the corresponding SHOW CREATE TABLE so we know what the table(s) look like.

# Set innodb_buffer_pool_size to 70-80 % of RAM
innodb_buffer_pool_size = 4G
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 1G


Full text catalog does not allow me to hit on singular forms I have created a full text catalog for a column under database name -> "Storage" -> Full Text Catalogs. It is accent sensitive and "default catalog" is set to false. The language for the column "SearchData" is set to swedish.

I am using a stored procedure that calls the contains function like this:

CREATE PROCEDURE [dbo].[SearchPages]
    @Term varchar(200)
AS
BEGIN
    SELECT pc.SearchData from PageContent pc
    where contains(pc.SearchData, @Term)
END

When I search för "smörgås" (swedish för "sandwich"), I hit on "smörgåsbord", but not on "smörgåsar" (swedish for "sandwishes").

Apparently, it only hits on words that are compositions containing the search term, but not on plural forms.

Our customers want to search putting a singular form in the search box.

Is that possible to obtain?
It would also be nice if you could search for "goose" and find "geese".

You could use either CONTAINS with FORMSOF(INFLECTIONAL, smörgås) or FREETEXT

CONTAINS:

SELECT pc.SearchData from PageContent pc
WHERE CONTAINS(pc.SearchData, 'FORMSOF(INFLECTIONAL, smörgås)', LANGUAGE 'Swedish')

FREETEXT (includes INFLECTIONAL and some other stuff automatically):

SELECT pc.SearchData from PageContent pc
WHERE FREETEXT(pc.SearchData, 'smörgås', LANGUAGE 'Swedish')

Try to set @Term variable this way (double quotes and star are important):

SET @Term = '"smörgås*"'

Or you may try to set thesaurus for synonyms like "goose" and "geese": http://msdn.microsoft.com/en-us/library/ms142491.aspx


How to find index organized table I'm working on Oracle 11g database and I need to list all index-organized tables.
Is there a dba's view to query for that ?

Thanks

Here is the query you require:

SELECT TABLE_NAME
FROM DBA_TABLES
WHERE IOT_TYPE IS NOT NULL;

The documentation states:

  If this is an index-organized table, then IOT_TYPE is IOT,
  IOT_OVERFLOW, or IOT_MAPPING. If this is not an index-organized table,
  then IOT_TYPE is NULL.

SELECT table_name FROM dba_tables WHERE iot_type IS NOT NULL;


Help installing SQL Server 2017 - VS Shell installation has failed with exit code 1638 Any suggestions on how to deal with this error:

TITLE: Microsoft SQL Server 2017 Setup
------------------------------

The following error has occurred:

VS Shell installation has failed with exit code 1638.

For help, click: https://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=14.0.1000.169&EvtType=0x5B39C8B9%25401434%25403

------------------------------
BUTTONS:

OK
------------------------------

This is a new laptop running

SQL Server 2016 express
Visual Studio 2017
SSMS 2017

I tried uninstalling anything related to SQL Server or Visual Studio.

Log: [3500:3970][2017-11-03T16:25:20]e000: Error 0x80070666: Cannot install a product when a newer version is installed.

Detailed results:
  Feature:                       Full-Text and Semantic Extractions for Search
  Status:                        Failed
  Reason for failure:            An error occurred for a dependency of the feature causing the setup process for the feature to fail.
  Next Step:                     Use the following information to resolve the error, and then try the setup process again.
  Component name:                Microsoft Visual C++ 2015 Redistributable
  Component error code:          1638
  Component log file:            C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\Log\20171103_162358\VCRuntime140_x64_Cpu64_1.log
  Error description:             VS Shell installation has failed with exit code 1638.
  Error help link:               https://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=14.0.1000.169&EvtType=VCRuntime140_x64%40Install%400x1638

  Feature:                       Database Engine Services
  Status:                        Failed
  Reason for failure:            An error occurred for a dependency of the feature causing the setup process for the feature to fail.
  Next Step:                     Use the following information to resolve the error, and then try the setup process again.
  Component name:                Microsoft Visual C++ 2015 Redistributable
  Component error code:          1638
  Component log file:            C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\Log\20171103_162358\VCRuntime140_x64_Cpu64_1.log
  Error description:             VS Shell installation has failed with exit code 1638.
  Error help link:               https://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=14.0.1000.169&EvtType=VCRuntime140_x64%40Install%400x1638

I experienced this problem as well with SQL Server 2017 Developer and it appears to be just bad planning on the part of the SQL Server installation package people.  The problem is that Visual Studio 2017 installs the Microsoft Visual C++ 2017 Redistributable (x86) and (x64) and the SQL Server installation tries to install the Microsoft Visual C++ 2015 Redistributables, which it can't because the SQL Server installation doesn't allow you to install an older version with the newer version installed.

This Microsoft Support article presents their explanation of the problem, and their recommended workarounds.

Solution 1: Install SQL Server first before installing Visual Studio 2017.

Solution 2 (what I did):

Uninstall the Microsoft Visual C++ 2017 Redistributable (x86) and (x64),
install SQL Server,
then reinstall the Microsoft Visual C++ 2017 Redistributable (x86) and (x64).

They are a little hard to find, so here are the direct links:

X64: https://go.microsoft.com/fwlink/?LinkId=746572

x86: https://go.microsoft.com/fwlink/?LinkId=746571

Note: If you don't have Microsoft Visual C++ 2017 Redistributable installed at all, just install it from one of the above links, and retry installing SQL Server. It should work now.

You may have to repeat these steps when installed additional named instances of SQL Server 2017.

In Windows' list of apps try to run 'repair' for MS Visual C++ Redistributable


Maintaining SQL Server 2008 R2 on Windows 10 I have to run SQL Server 2008 R2 on Windows 10 but it is not supported and so I am not getting any updates. Installing a later version of SQL Server is not an option. I can find all the service packs and cumulative updates online.

Questions:

Is there a way to get updates automatically?
If not, do I have to install all the service packs in sequence, or can I install only the most recent.

Is there a way to get updates automatically?

SQL Server 2008 R2 is not supported on Windows 10. This means the SQL Server team will not be releasing any security updates or patches if they find any specific issues related to SQL Server 2008 R2 code. There is no way to get updates automatically as none would be released related to Windows 10.

Having said that, SQL Server 2008 R2 installation is not blocked when you try to install it on Windows 10. Of course, it is always better and advisable to install the latest Service Pack (Service Pack 3 is the final SP for 2008 R2).

If you are trying to install SQL Server 2008 R2 for testing purpose on Windows 10, you can go ahead; but since it's not supported it would be of no use to carry forward this idea.

  If not, do I have to install all the service packs in sequence, or can I install only the most recent?

Service Packs are cumulative, so you can directly install SQL Server 2008 R2 SP3 even on RTM bits. There are cumulative updates (CU) released after 2008 R2 SP3 as well, but my advice is don't install CU unless you face specific issues fixed in the CU.

it is better to install the latest SP as it is supposed to include all previous SPs and the CU from the previous SP till this last one

Some thoigh, SPs do not include ALL the CUs.
If a specific one is fixing an issue you face, then install it.

here is a list you may find useful
http://sqlserverbuilds.blogspot.ie/


Duplicate key violates unique constraint When I use this query everything is fine:

UPDATE users
   SET f_name='Mike',
       l_name='MyLastName',
       username='blabla',
       "password"='asdfsdaf',
       "class"=12,
       lang='en'
 WHERE id=50;

I created a view which combines two tables:

CREATE OR REPLACE VIEW students_data AS
SELECT students.id,
       users.f_name,
       users.l_name,
       users.username,
       users."password",
       users."class",
       users.permission,
       users.sessionid,
       users.lastlogin,
       users.lang
  FROM students
  LEFT JOIN users ON students.id = users.id;

And I also created this rule:

CREATE OR REPLACE RULE students_data_update AS
    ON UPDATE TO students_data DO INSTEAD
       UPDATE users
          SET f_name = new.f_name,
              l_name = new.l_name,
              username = new.username,
              "password" = new."password",
              "class" = new."class",
              permission = new.permission,
              sessionid = new.sessionid,
              lastlogin = new.lastlogin,
              lang = new.lang;

When I execute this query I get an error:

UPDATE students_data
   SET f_name='Mikaa',
       l_name='MikeL',
       username='blabla',
       "password"='asdfsdaf',
       "class"=12,
       lang='en'
 WHERE id=50;

  ERROR: duplicate key violates unique constraint "username" SQL
  status:23505

I have no idea why I get this error, username is a unique column but I shouldn't give any problems when I update this column.

-- Table: users

-- DROP TABLE users;

CREATE TABLE users
(
  id serial NOT NULL,
  f_name character varying,
  l_name character varying,
  username character varying NOT NULL,
  "password" character varying NOT NULL,
  "class" integer NOT NULL,
  permission integer NOT NULL,
  sessionid character varying,
  lastlogin integer,
  lang character varying(5),
  CONSTRAINT users_pkey PRIMARY KEY (id),
  CONSTRAINT "class" FOREIGN KEY ("class")
      REFERENCES "class" (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT username UNIQUE (username)
)
WITHOUT OIDS;

Your rule does not have a WHERE clause and any update is trying to modify the whole users table.

You can/should add WHERE id = old.id:

CREATE OR REPLACE RULE students_data_update AS
    ON UPDATE TO students_data DO INSTEAD
UPDATE users
SET f_name = new.f_name,
    l_name = new.l_name,
    --- ...
WHERE id = old.id ;               --- added

Note: I had never used the RULE system before in Postgres. But the above suggestion worked when tested at SQL-Fiddle.

SELECT setval(pg_get_serial_sequence('users', 'id'), coalesce(max(id)+1, 1), false) FROM users;


Escape a quote in SSIS Flat File Conn Manager I am trying to import a simple text file, that has a few "extra" quotation marks in it, and I am trying to see if there is a simple way to accommodate this.

To reproduce the issue, I have a simple three line file I want to import:

"FirstName","LastName","ZIPCODE"
"Tim","Harkin","11111"
"Tim","Harkin"","11111"
"George","Washington","12345"

The extra quote in the third line is throwing off the import.  Looking at the file connection manager, I have the following settings:

However, looking at the preview, the extra quote then ends up "hiding" the column delimiter:

Any suggestions to get the connection manager to ignore that extra quote, and interpret each line as they exist in the flat file?

First, I'll assume you can't simply ask for a correctly formatted csv file. If the last name should really be Harkin", then the entry in the csv file should have escaped the double quote character by doubling it (i.e., "Harkin"""). If this is a recurring process, then the best solution will definitely be to require a correctly formatted file.

I don't know of a good way to force SSIS to read in an incorrectly formatted file and figure out how it should be formatted.

If I were in your shoes, I'd try one of two approaches:

Repair the file manually: Open the file up with a text editor, preferably one that can handle regular expressions. Replace the string "," in the file with a unique token (say, --COLBREAK-- - anything that won't be in your data will work [check first if in doubt]). Then search for double quotes between two characters (i.e., any remaining double quotes not at the start and end of the line). Check these, if appropriate replacing " with "". you might also want to confirm that all lines have the expected number of column breaks, to make sure that you don't have the ","embedded in an existing string, or rows that are missing columns altogether. When done, change your token back to "," and try importing the file again.
Load the data in two phases: If there too much data to manipulate in an editor, rewrite your initial load process to import the entire row as one column. You can process this data in SSIS or load it to a SQL table. Next, check for valid rows (start with ", have "," twice, end with "; and, does not have seven or more instances of " in it), and write those rows to a "valid" file. Write the rest of the rows to an "invalid" file. Process the "valid" file as you originally planned; again, manually resolve the rows in the "invalid" file so they are valid, then process them as well.

I have faced this issue and managed tolve it by setting Configuration as follows and all should go fine.


MySQL 5.7 GetDiagnostics is always returning null for SQLSTATE What am I doing wrong? I have the following Handler and the sqlexception is being caught, but GET DIAGNOSTICS is not returning any information - they are all NULL.

I have the following code:

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN

      GET DIAGNOSTICS @cno = NUMBER;
      GET DIAGNOSTICS CONDITION @cno @errno = MYSQL_ERRNO;

      GET DIAGNOSTICS CONDITION 1
        currentcode = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;

    GET CURRENT DIAGNOSTICS CONDITION 1 @current = RETURNED_SQLSTATE;
    GET STACKED DIAGNOSTICS CONDITION 1 @stacked = RETURNED_SQLSTATE;

  SELECT @current, @stacked, @errno INTO currentcode,stackedcode,errorcode;

GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
SELECT @full_error;

CALL debug_msg(1,@full_error);

    SET _rollback = 1;
    SET ResultCode = CONCAT('currentcode: ' , currentcode, ' , stackedcode: ' , stackedcode);
  END;

I admit that the condition_number element - I do not understand if it represents the index of conditions that were caught or if it is the error number to look at and the documentation on the MySQL site says condition_number (ok I get that it is a condition_number.)

Replace GET DIAGNOSTICS CONDITION 1

with GET STACKED DIAGNOSTICS CONDITION 1

In my case this helped me.


User Defined Function Return value Hello all I have a function:

ALTER FUNCTION [dbo].[getContentURL]
(
    @VID    int,
    @accountID int
)
RETURNS VARCHAR(500)
AS
BEGIN

DECLARE @cTable varchar(50) = '[CONTENT_CONNECT].[dbo].CONTENT_' + CONVERT(varchar(5),@accountID)
    DECLARE @vTable varchar(50) = '[CONTENT_CONNECT].[dbo].VERSION_' + CONVERT(varchar(5),@accountID)
DECLARE @sql VARCHAR(max) =  ''
DECLARE @RETURN VARCHAR(500)
DECLARE @ParmDefinition VARCHAR(500)
 SET @sql = '
    DECLARE @CompanyID int
    DECLARE @RETURN VARCHAR(500)
    DECLARE @ProjectNumber int
    DECLARE @ContentID int
    DECLARE @VersionNumber int
    DECLARE @FileName varchar(400)
  select @VersionNumber= V.Number,@ContentID= V.ContentID,@ProjectNumber=P.Number,@FileName=C.Name,@CompanyID=P.CompanyID
    from ' + @vTable + ' V
    inner join' + @cTable + ' C on V.ContentID=C.ID
    inner join Project P on C.ProjectID=P.ID where V.VID='+convert(varchar,@VID)+'

 select ''/''+Convert(varchar,@CompanyID)+''/''+Convert(varchar,@ProjectNumber)+''/''+Convert(varchar,@ContentID)+''/''+Convert(varchar,@VersionNumber)+''/''+@FileName'

 SET @ParmDefinition = N'@RETURN VARCHAR(500) OUTPUT'
    exec sp_executesql @sql, @ParmDefinition,  @RETURN OUTPUT
  set @RETURN=(select @RETURN)
 RETURN @RETURN
END

but when I execute this function

select  dbo.getContentURL(177,1)

this gives error:

The name 'select --------------------' is not a valid identifier.

how to return this executed value? can any one  help me please?

I can't say that I understand what it is that You are trying to do...
The thing is, just like You have already been told, that You can't use Dynamic SQL in a Function.

Using a Stored Procedure will solve the problem You encountered:
(I did not try to find another logic to retrieving the same data, which is possible, I just converted Your Function to a Stored Procedure)

CREATE PROC [dbo].[usp_getContentURL]
(@VID INT, @accountID INT)
AS
DECLARE @cTable varchar(50) = '[CONTENT_CONNECT].[dbo].CONTENT_' + CONVERT(varchar(5),@accountID)
DECLARE @vTable varchar(50) = '[CONTENT_CONNECT].[dbo].VERSION_' + CONVERT(varchar(5),@accountID)
DECLARE @sql VARCHAR(max) =  ''
SET @sql = '
DECLARE @CompanyID int
DECLARE @ProjectNumber int
DECLARE @ContentID int
DECLARE @VersionNumber int
DECLARE @FileName varchar(400)
select @VersionNumber=V.Number, @ContentID=V.ContentID, @ProjectNumber=P.Number, @FileName=C.Name, @CompanyID=P.CompanyID
from ' + @vTable + ' V
inner join' + @cTable + ' C on V.ContentID=C.ID
inner join Project P on C.ProjectID=P.ID
where V.VID='+convert(varchar,@VID)+'
select ''/''+Convert(varchar,@CompanyID)
+''/''+Convert(varchar,@ProjectNumber)
+''/''+Convert(varchar,@ContentID)
+''/''+Convert(varchar,@VersionNumber)
+''/''+@FileName'

EXEC (@sql)
GO

If this does not meet Your needs please post what exactly You are trying to do.
Good Luck,
Roi


Why does COUNT() aggregate return 0 for 'NULL'? A SELECT statement returns several rows:

SELECT
  ColA
FROM
  TableA
WHERE
  ColA IS NULL

I get 47 rows that have 'NULL' for ColA within TableA.

ColA
NULL
NULL
NULL
etc...

If I add an aggregate to this query:

SELECT
  ColA,
  COUNT(ColA) AS theCount
FROM
  TableA
WHERE
  ColA IS NULL
GROUP BY ColA

I get

ColA | theCount
NULL | 0

Why is this happening, and what can I do to avoid this?

Aggregate functions ignore null values.

So

SELECT COUNT(cola) AS thecount
FROM tablea

is equivalent to

SELECT count(*) AS thecount
FROM tablea
WHERE cola IS NOT NULL;

As all of your values are null, count(cola) has to return zero.

If you want to count the rows that are null, you need count(*)

SELECT cola,
       count(*) AS theCount
FROM tablea
WHERE cola is null
GROUP BY cola;

Or simpler:

SELECT count(*) AS theCount
FROM tablea
WHERE cola is null;

If you want to count NULL and NOT NULL values in a single query, use:

SELECT count(cola) as not_null_count,
       count(case when cola is null then 1 end) as null_count
FROM tablea;

NULL is not empty, it's UNKNOWN, NULL != NULL, ANYTHING != NULL ,so count(NULL) =0