How to ensure that row with same PK inserted replaces old row in MS SQL Server, instead of throwing exception of PK Violation? I'm transferring bulk data from a C# DataTable into a SQL Server DB. The DataTable provides values for all 26 columns except the first column, which is an auto-incremental ID The schema skeleton is as follows:

CREATE TABLE (
ID INT IDENTITY(1,1) PRIMARY KEY
TS_Code INT, -- This value is generated at run-time & is unique for each row
Column2 .....
(Upto 25 columns)
)

I want that if a row is entered with the same TS_Code, it should be replaced with the newly inserted row. I wrote a trigger that would delete the old row & insert new one.

CREATE TRIGGER [Trig_Insert]
ON [dbo].[Input]
INSTEAD OF INSERT
AS
BEGIN
    --To check if unique key already exists in DB

    DECLARE @TS_C BIGINT
    SELECT @TS_C = TS_Code FROM inserted    --TS_Code is unique for each row, but hasn't been set as "UNIQUE"

    --If TS_Code already exists
    IF EXISTS(SELECT * FROM Input WHERE TS_Code = @TS_C)
    BEGIN
        delete from Input
        where TS_Code = @TS_C
    END

    --Insert into [Input] table; get an auto-incremented PK ID assigned to new row
    INSERT INTO Input
    SELECT * FROM inserted
END

However, I get the following error when trying to compile this trigger:
"An explicit value for the identity column in table 'Input' can only be specified when a column list is used and IDENTITY_INSERT is ON."

Why is this, when I'm not even sending it explicit Identity values?

To fix that error just list the fields  you want to insert. This means, all of the fields but ID.

Solution 1:

Replace

--Insert into [Input] table; get an auto-incremented PK ID assigned to new row
INSERT INTO Input
SELECT * FROM inserted

With:

INSERT INTO Input (TS_Code, column2, column3,..., column25)
SELECT TS_Code, column2, column3,..., column25 FROM inserted;

Notice that in this case your trigger will delete records and you will have holes in your table:

ID      TS_Code       Column2 ....
----------------------------------
1       1001          valueX
3       1003          valueY
4       1002          valueZ   <--- your trigger found a repeated 1002, ID = 2 record is deleted.

If this is what you want, go ahead with Solution 1.
Also notice, that your trigger will work only for record by record insert. A bulk insert will call the trigger once and you will need to handle each record using cursors.

Solution2:

If you want to keep the records already found, you probably want to use the MERGE option:

first of all put all the records from C# into a Working table.
Lets say table C_Sharp that should look like this:

CREATE TABLE C_SHARP(
TS_Code INT,
Column2,
...
Column25
)

With that C_SHARP table having all the records, perform a MERGE sentence:

MERGE INTO Input I
   USING (
          SELECT TS_Code, Column2, ... , Column25
            FROM C_SHARP
         ) C
      ON I.TS_Code = C.TS_Code

WHEN MATCHED THEN
        UPDATE
           SET Column2 = C.Column2,
               ...,
               Column25 = C.Column25
WHEN NOT MATCHED THEN
    INSERT (TS_Code, Column2, ..., Column25)
    VALUES (C.TS_Code, C.Column2, ..., C.Column25)
END;

Good luck!


How do I find all roles that grant access to objects under a particular schema? I need to assign a user to any roles that grant access to tables under a particular schema. What would I query to find those roles?

I believe I got it - replying to my own question in case anyone needs the info:

select * from role_tab_privs where owner = 'SCHEMA_NAME';


SQL Server transactional replication distributor If I have a SQL Server 2012, with two databases, both publishers, replicating to a bunch of subscribers.

Can each database go through a different distributor?
Is the publisher the server or the database?

I understood it to be a publisher can only go to one distributor

Is that each db or the whole server?

The Distributor is the server that contains the distribution database.

Configure Distribution at https://msdn.microsoft.com/en-us/library/ms151860.aspx says:

  By default, the Distributor is the same server as the Publisher (a
  local Distributor), but it can also be a separate server from the
  Publisher (a remote Distributor).

SQL Server can support a single Distributor on the local server.  If you are trying to reduce the load on the main server you can instead create the Distributor on a remote server.

If the load is heavy, an additional step that would help manage the distribution is to create a distribution database for each database that is being published.  This could reduce the contention within each database that might be present in a single distribution database.

Both a local server and a remote server can support multiple distribution databases.

David Poole's article from 2010, Scaling Out the Distribution Database. may provide you more insight.  It can be found here: http://www.sqlservercentral.com/articles/Replication/69663/

Both articles are helpful and I have not detailed every step in the setup.


Postgres, `where` condition, sort by first column, but I don't want that I am using PostgreSQL 11.5 on Ubuntu

Issue

I have sql with joining tables and with where condition, which looks like this.

...

where
s_product.product_shop_id in (
'237002',
'230041',
'467173',
'464431',
'318417',

...

When I run sql.

copy(
select distinct on ("HOBIS")
-- s_product.product_id,
product_shop_id as "HOBIS",
product_name as "NAME",
concat('https://eshop.unihobby.cz/',product_url,'/',s_product.product_id,'p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019') as "URL",
concat('https://eshop.unihobby.cz/bin/product/4/',filename) as "IMG URL",
price_tax as "PRICE VAT",
case when price_rec > price_tax then concat(price_rec) end as "PRICE ORIGINAL"
from
s_product
left join s_product_image on s_product.product_id = s_product_image.product_id
left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id
where
s_product.product_shop_id in (
'237002',
'230041',
'467173',
'464431'
)
and image_order = '0'
and s_pricelist_generated_lists.group_id = '1'

--order by "HOBIS"
) to stdout (format csv, quote '"');

Results are sorted by s_product.product_shop_id.

...

230041,"Interiérové dveře Klasik plné dub archico, 3D povrch - 60 cm L",https://eshop.unihobby.cz/stavba-dvere-interierove-dvere-dvere-klasik-plne-dub-archico-3d-povrch/136280p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019,https://eshop.unihobby.cz/bin/product/4/klasik-plne-dub-archico.jpg,990.00,1390.00
237002,Vinylová podlaha Kronostep - True Grit,https://eshop.unihobby.cz/stavba-podlahy-vinylove-podlahy-podlaha-vinylova-kronostep-true-grit-5-0mm/151328p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019,https://eshop.unihobby.cz/bin/product/4/237002-krono-original-krono-xonic-r018-true-grit-3.jpg.big.jpg,1058.00,1323.00
318417,Primalex Malvena na fasády 1 l,https://eshop.unihobby.cz/bydleni-barvy-a-laky-venkovni-barvy-primalex-malvena-na-fasady-1l/153358p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019,https://eshop.unihobby.cz/bin/product/4/318417-malvena-5l.jpg,199.00,
464431,Benzínová řetězová pila CSH46 Scheppach,https://eshop.unihobby.cz/zahrada-zahradni-technika-retezove-pily-benzinove-retezove-pily-pila-retezova-benzinova-csh46-scheppach/150162p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019,https://eshop.unihobby.cz/bin/product/4/464431-csh46-scheppach-diy-garten-titel-neu-na.jpg,3290.00,

...

But I want to have results as they are in where condition. Is possible to have results as they are in where condition? And why PG sort results when I  have no sort condition in my sql?

Thanks.

You can do that if you change the way you pass the values to the WHERE clause. One way is to join to an unnested array and use the array index for sorting.

As you need a different sort order for the distinct on () you have to wrap the query in a derived table. The inner part uses an order by on the "distinct column". And the result can then be sorted by the array index.

select "HOBIS", "NAME", "URL", ... -- all columns but the `idx`
from (
  select distinct on ("HOBIS")
            product_shop_id as "HOBIS",
            ...,
            x.idx
  ...
  from s_product
    join unnest(array['237002','230041','467173','464431']) with ordinality as x(shopid, idx)
      on s_product.product_shop_id = x.shopid
    left join ...
    ...
  order by "HOBIS"
) t
order by t.idx

This behaves a bit different to an IN condition, so you have to make sure that every shop ID only appears once in the array.

  And why PG sort results when I have no sort condition in my sql?

Postgres does not sort your results.

Without an order by clause, the database is free to return the rows in any order it wants. The "order" results from the different steps in the execution plan. If you see some perceived "order", then this probably stems from retrieving rows through an index.

But the "order" you see there is in no way guaranteed. It can change because rows have been changed or inserted into the table, or because the query optimizer chooses to use a different execution plan. Or because some other transaction is running a similar statement.


Selecting a record based off conditions CLOB I have a table with a column storing JSON data as a CLOB. The JSON has a 'miscData' field that is made up of an array of JSON objects. Each object has 2 keys which are always the same, but the values are different.
.
.
.
"otherKeys" : "otherValues",
"miscData": [
        {
            "miscType": "date",
            "miscText": "2020-07-09T10:01:10.450Z"
        },
        {
            "miscType": "Comment",
            "miscText": "Comment body"
        },
        {
            "miscType": "CORRECT_TYPE",
            "miscText": "SELECT_ME"
        }
    ],
"confirmationNumber" : "123456789qwerty",
.
.
.

I need to
SELECT
    CLOB_COL.miscData.miscText
WHERE
    CLOB_COL.miscData.miscType = 'CORRECT_TYPE'
AND
    CLOB_COL.confirmationNumber = 'xxxx';

But I am having trouble since miscData is an array of objects which all contain miscType and miscData keys and I can't guarantee the array will be the same size, or in the same order.

After surviving the ordeal of installing Oracle 12c on Devuan, I came up with this query using json_exists:
select
  t.id,
  t.data.miscData,
  t.data.miscData.miscType,
  t.data.confirmationNumber
  from json_documents t
  where json_exists(
          t.data,
          '$.miscData?(@.miscType=="CORRECT_TYPE")'
        )
    and json_exists(
          t.data,
          '$?(@.confirmationNumber=="123456789qwerty")'
        )

FYI:
CREATE TABLE json_documents (
  id    RAW(16) NOT NULL,
  data  CLOB,
  CONSTRAINT json_documents_pk PRIMARY KEY (id),
  CONSTRAINT json_documents_json CHECK (data IS JSON (STRICT))
)


Implementation Idea for login structure (login id with two different tables) I am creating a sample web application for a business and I am stuck at the login implementation. There is supposed to be two different users (login types?). I will have employees and customers. I have a table for employees and a table for customers set up already. Let me caveat that I understand the following is not SQL, just the notes I've taken so far in a .txt file.

I have my employees

EMPLOYEES
    Employee_ID  CHAR(5) - 'EM###' - NOT NULL //PK
    ...(columns unimportant to the scope of this question)
    Web_Login_ID

I have my customers

CUSTOMERS
    Customer_ID //PK
    ...(more columns)
    Web_Login_ID

And here is where I get stuck at the web login

WEB_USER_LOGIN
    Web_Login_ID
    ???

I've read other methods to create a table for login information and they referenced a LEFT JOIN in SQL, but that seems like it would only be good if I was referencing 1 other table and not two different tables. How can I pull EMPLOYEE information if the Login ID entered is from the EMPLOYEE tables and vice versa for customers?

First of all, every thing depends on business logic you have. If employees and customers it's fine to use 2 diferrent tables, to connect them with the table WEB_USER_LOGIN I would just append and field like 'usertype'.

If your database is Oracle you can have object tables to solve this problem, if its PostgreSQL you can hava inherited tables.

If its mysql I would use just one table with both fields, but if you prefer to use the left join it's ok but I would create a VIEW for it.


How to installation spark standalone mode in ubuntu I am trying to install spark standalone but show error.
How i can solve this problem.

Java version :- 1.8.0_131
Spark:- 2.2.0
Hadoop: 2.7.4
bashrc file setting
Hadoop file location in local System: /usr/lib/hadoop/hadoop-2.7.4
Spark file location: /opt/spark/spark

File:

-----------------------------
#JAVA HOME directory setup
export JAVA_HOME=/usr/lib/jvm/java-8-openjdk-amd64
export PATH=$PATH:$JAVA_HOME/bin

#HBASE HOME setup
export HBASE_HOME=/usr/lib/hbase/hbase-1.3.1
export PATH=$PATH:$HBASE_HOME/bin

#HADOOP Setup
export HADOOP_HOME=/usr/lib/hadoop/hadoop-2.7.4
export HADOOP_CONF_DIR=$HADOOP_HOME/etc/hadoop
export HADOOP_INSTALL=$HADOOP_HOME
export HADOOP_MAPRED_HOME=$HADOOP_HOME
export HADOOP_HDFS_HOME=$HADOOP_HOME
export YARN_HOME=$HADOOP_HOME
export HADOOP_COMMON_LIB_NATIVE_DIR=$HADOOP_HOME/lib/native
export PATH=$PATH:$HADOOP_HOME/sbin:$HADOOP_HOME/bin
export HADOOP_PID_DIR=$HADOOP_HOME/hadoop2_data/hdfs/pid

#spark setup
export SPARK_HOME=/opt/spark/spark
export PATH=$SPARK_HOME/bin:$PATH
#scala
export SCALA_HOME=/usr/local/src/scala/scala-2.11.11
export PATH=$SCALA_HOME/bin:$PATH
------------------------------------------------------------

I'd recommend using pip pyspark, as it's super easy
$ python3 -m venv venv
$ source venv/bin/activate
$ pip install pyspark
$ pyspark

You can modify Spark configuration by adding files to venv/lib/python3.x/site-packages/pyspark/conf/


Two tables, one foreign key, and two primary keys Say there are two tables:
Student:
StudentID(Primary Key)
Name

StudentTeams:
StudentID(Primary Key, Foreign Key)
TeamName

StudentID in StudentTeams table references Student table primary key. What would be the pros and cons of such a structure in terms of relational integrity?

Just to clarify:  Every student can have either 0 or 1 associated TeamName value, with this setup.

Having StudentTeams without StudentID as a foreign key would allow records with invalid student IDs, so that is presumably a pro. Similarly, if StudentID were not the primary key of StudentTeams, then a student could be attached to multiple team names, also presumably a pro (the assumption being that those are your business rules).

However, if this is literally the table structure, then it would actually be better for TeamName to be a field in the Student table, with NULL values allowed. Each student could have 0 or 1 TeamName values, and no separate relationship (or table) is required. So, that's a con.

In my experience, I've only seen a couple of situations where it made sense to have TableA with a primary key of A_ID, and then to have TableB which uses A_ID as both a primary and a foreign key:

Performance: If TableA is used in many places, by many parts of the application, and the data in TableB is only used in a isolated part of the application (a set of commands that only 2% of the user base have access to, or that are only used at year-end), and each row in TableB is large (for instance, if Student.Name is, on average, 40 characters long, and StudentTeams.TeamName is, on average 4000 characters long), then queries that don't need TeamName might perform much more quickly if it's not in the Student table.
Parallel table: If TableA's structure is not under your control, so you cannot add TeamName to it, then maintaining TableB as a "parallel table" (my own term; a separate table that will only have a row if there's a matching row in TableA) might be the only way to do things. I once set up a number of these tables, when I needed to built an application that used data from a third-party vendor's application, but where our app needed to supply additional data that didn't exist in the vendor's application. The foreign key is necessary, as it can keep your information properly tied to the vendor's data (cascading updates and deletes), without modifying the vendor's application.
Logical Separation: If the data in TableA and TableB are related, but are sharply divided in purpose, then it may make sense to store them in separate tables. As with the previous example, Making TableA's primary key be primary key and foreign key in TableB guarantees that you'll only have a TableB entry if the TableA entry exists, and you have to eliminate the TableB row to delete the TableA row.  I've worked with a system with a Task table (with a list of work to be done), and a separate TaskHistory table (with information about the conditions under which the task was done, consumables used by the task, etc.). This can simplify business rules; a field could be NOT NULL in TaskHistory, even if it could not possibly be filled in until the task was done; if the field were in the Task table, then a special business rule would have to exist, guaranteeing that certain fields were not NULL once the task had reached completed status. Note that this will often be a mix of logic and performance: there were a number of queries in the application that only had to hit the Task table.
Further Relational Requirements: As noted by ypercubeᵀᴹ in the comments, one could use this to allow a third table to use the StudentID from the StudentTeams table as a foreign key, guaranteeing that the third table only linked back to students who were on a team. Personally, I'm not sure that I would implement a structure this way. This could lead to confusion as to the meaning of StudentID; it would always link back to a Student record, but it would not always link back to a StudentTeam record (although, admittedly, in the context of this third table, it would always link back to both a Student and a StudentTeam). I would be inclined to create a StudentTeamID value in StudentTeams, and link to that, as its meaning would be immediately clear (it would only link to students on a team).


Replicating only changes made by a specific database user I am performing parallel testing.  We have upgraded the legacy batch jobs to a new framework which we need to test against the existing batch jobs.

In the existing setup, I have web application which creates or updates records based on user input and these batch jobs will process the records.

The plan is to have 2 databases, primary and secondary, with the existing batch job connected to the primary database server and the new batch job connected to the secondary database server.

The web application will populate data to both primary and secondary databases.

I would like to perform replication for all the tables in the database. However, I would only like to replicate any DML changes done by the specific database account used by the web application.

Is that possible?

I agree with Phil+1 that this is not readily possible.  Furthermore, it is very unclear how this could even work.  Imagine the following simple table is being replicated.

 C1   C2
  1    'Apple'
  2    'Orange'
  3    'Pear'

Scenario 1

 batchjobaccount : INSERT INTO T1 VALUES (4,'Peach');
 appserveraccount: UPDATE T1 SET C2='Grape' WHERE C1=4;

What should happen in the replication?  Should the record be created so that the appserveraccount action can take place or should the update be ignored?

Scenario 2

 batchjobaccount : UPDATE T1 SET C1=5 WHERE C1=2;
 appserveraccount: DELETE FROM T1 WHERE C1=5;

What should happen in the replication?  Should the record be deleted or not?

Scenario 3

 batchjobaccount : DELETE FROM T1 WHERE C1=3;
 appserveraccount: UPDATE T1 SET C2='Strawberry' WHERE C1=3;

What should happen?  Should the update fail on the local database but succeed on the replicated database or should it not run on the remote database because it failed on the local database?

If you don't want changes by batchjobaccount to replicate, then why give them permission to make the changes on the local database?  If they need a local playground, then can you just make a local copy of the tables and only give batchjobaccount access to those?


sql server 2014 unable to connect with Microsoft account or sa I am running sql standard 2008 r2 on my box. and  its been working great.
I just installed sql server 2014 on my machine as a second instance.

The service is running fine, but when I try to log into it with my sa password, I get an error about the login failing.

Assuming I maybe messed up the password on install, I uninstalled and reinstalled.
Same issue.

The weird thing is that when I try log in with windows authentication, it also fails

yet I can log into my 2008 R2 instance with windows authentication, no problem

Something is bugging me that its not necessarily the log in, but maybe how the service is logging in, but it is logged in exactly the same way as the 2008r2 service.

Full error message is here

Server Name: CRAIG-PC\MSSQLSERVER2014
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536

Thanks in advance

Full screen grab of services

browser is running from this path
    "C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe"

The following steps should allow you to create a new sysadmin account, you can then connect using the new account and update the sa password as required.

Log in to Windows as a local administrator (must be local admin, not just domain admin, on the machine hosting SQL Server).
Stop the SQL Server Windows service.
Open command prompt (cmd.exe) as an administrator and run the SQL Server exe with the -m flag (minimal config). Leave this running. (For named instances use -s {instancename}, see https://technet.microsoft.com/en-us/library/aa178031)

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe -m
Open another command prompt window as an administrator.
Run SqlCmd.exe and connect to the instance.

sqlcmd -s localhost\sqlexpress
Create a user and assign to the sysadmin role.

create login new_admin with password='new_password';
go

sp_addsrvrolemember new_admin, 'SYSADMIN';
go
Exit sqlcmd using ctrl + c and close the command prompt.
On the other command prompt (where you started sqlservr.exe) press ctrl + c and then Y to stop SQL Server. Restart the SQL Server Windows service.

You should be able to log in using the new username and password.

Edit: If you have difficulties connecting once the server is started in minimal config mode you may need to stop other services to prevent anything opening a connection, particularly SSRS/SSIS etc. if they're running as local administrators.


Difference between mongod and mongodb upstart jobs I just noticed that there are two possible ways to start mongodb:

service mongodb start (uses /etc/mongod.conf)
service mongod start (uses /etc/mongodb.conf)

Can someone tell me what's the difference between the two?

I'm running mongodb 2.6.4 on Ubuntu 14.04 (64 bit)

I suspect you had MongoDB 2.4 installed previously.

In MongoDB 2.4 the service was called mongodb and used /etc/mongodb.conf (ref: Install MongoDB 2.4 on Ubuntu).

With MongoDB 2.6 there was an attempt to have more standard package names across Linux distributions, so the service was renamed to mongod (to reflect the actual daemon being started) and the config was changed to mongod.conf (ref: Install MongoDB 2.6 on Ubuntu).


How to recover deleted rows data table in SQL Server without backup? I accidentally deleted all data rows from a important table. But unfortunately, the last backup of the database was done last week.
I have tried several ways, namely by looking at the transaction id and returning it with a query. but from the review I can also be more dangerous because it can eliminate some data
How can I restore the data that I deleted?

It would seem that the before and after states would be recorded in the transaction log. Here is a step by step article on recovering deleted data from the SQL Server transaction log. You can create a db copy from the transaction log right before the delete and get a copy of the data from that point.
 RESTORE LOG Databasename_COPY FROM DISK = N'D:\Databasename\TranLog.trn' WITH STOPBEFOREMARK = 'lsn:0x0000001'

Recover Deleted Data From SQL Server Table by Transaction Logs
Edit: I just saw this solution on another answer.
It does not require backups and actually converts the data. It may have some issues with max length datatypes and such, so you would have to check the data.
How to recover deleted data from SQL Server
Which is referred to in an answer in the following question.
How do I get back some deleted records?


Is shrink a safe online operation? We are planning to run the following space management command:

alter table XXX shrink space cascade

on a production database (Oracle 10g). The XXX table is a transactional table heavily used  for updates/inserts/deletes by many applications.

I am wondering if running the shrink command during the peak time (online) will cause a severe performance degradation, or may lock the table and so block all the applications?

Can you clarify the impact of the shrink command on the access to the databese table XXX?

As I understand it the resizing of the high water mark requires an exclusive table lock, which in theory might not be a problem as it would be of very short duration. However if it has to wait on other transactions before it gets the lock it will block other transactions.

https://forums.oracle.com/forums/thread.jspa?threadID=2453756

However as the docs point out, you could run a shrink space compact, which does not move the HWM, and run the shrink later on when the system is known to be quiet: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_3001.htm#i2192484


Log shipping restore job failing Created a transaction log shipping configuration between two SQL Servers (different VMs) using the built in wizard. Backup job runs on primary server, copy and restore jobs run on secondary server.

The restore job fails giving the following message (message is in job history):

  Error: The restore operation cannot proceed because the secondary
  database 'My_DB_Name' is not in NORECOVERY/STANDBY
  mode.(Microsoft.SqlServer.Management.LogShipping)

Under my secondary database Properties > Options, I see recovery model is set to Full. What do I need to do to make the restore job run?

As the error indicates you, your secondary database is not ready for log shipping or it is online. Before you start the actual log shipping process, you need to make secondary database ready for the log shipping. Either you can leave the secondary database under restoring mode and then let SQL Server take snapshot of your primary database, or you can take a full and transnational backup of primary database and restore it on your secondary DB using no recovery mode then continue your log shipping process.


Easily give role in one database access to tables in another database on the same server I have a database server (Sql Server 2008 R2 Enterprise) supporting two separate but related vendor products, each with it's own database (call them X and Y)

In database X I a have a lot of users defined, but their access rights come down to a manageable handful of roles. I'd like to take one of these roles and use it to grant select access to a few tables in database Y. I really want to avoid creating a new role in database Y and re-adding all the users, not just because of the busy-work setting up that security info, but also because of needing to keep these roles in sync over time, as users come and go. Really, the role that already exists in database X already does a great job of logically defining the access group I want in database Y from a business standpoint, and I want to keep it that way.

How can I grant access in Y for this role from X?

Database roles are security principals that are wholly contained within their respective database and are not shared or visible to other databases.  So any roles and users that are in database X have no knowledge of database Y.  To accomplish your goal, you'll need to recreate the role in database Y and add all the appropriate users to this database and role.  Fortunately, you can script out this process using the system views:

USE [Y];
CREATE ROLE foo;

--grant all perms on foo

--Use this generate sql to add all your users to the role
select 'CREATE USER '+quotename(u.name)+';' + char(10)+
       'EXEC sp_addrolemember ''foo'','''+u.name+''';'
from (select name,principal_id
        from x.sys.database_principals where type = 'R') r
    join x.sys.database_role_members rm
        on (r.principal_id = rm.role_principal_id)
    join (select name,type_desc,principal_id
        from x.sys.database_principals where type != 'R') u
        on (rm.member_principal_id = u.principal_id )
where r.name = 'foo'

You can then take this sort of script/process to a batch job to keep things synchronized, but you will need some sort of external process to keep these roles in sync.

One piece that might be confusing here is that you have logins and other server level principals that are connected to the database principals, but there is a clear distinction between these.  I provide some explanation about the differences between these principals in this answer


MySQL datatype for username of maximum 15 characters I currently have a database with the "user" table having username as one of the columns.

I allow a maximum of 15 characters for the username in my system. Is it fine to have the username column as a varchar(15) datatype?

The best way to store any short length string in plain text is to use the varchar datatype, setting the maximum length to 15, only for the purpose of integrity. The length of the username should obviously be validated within the application.

The only thing to keep in mind would be the charset of the database, table, and column. Make sure that you are using a charset which covers all of the expected characters in the username, in whatever human language they are being inserted in.


How do I cast varchar to macaddr without changing client code I have a dedicated DB Server with Debian Jessie and Postgresql 9.4

Client: A is a Tomcat Java app which use torque with a JDBC driver connecting to the DB Server.

Issue: on Updating a row with a new value the update fails because the app transfers the wrong datatype.

Error message:

  column "device_macaddr" is of type macaddr but expression is of type character varying at character 159

Update Statement generated by Torque:

UPDATE device SET device_last_change = $1, device_macaddr = $2 WHERE device_ID = $3

Definition of Datatypes in postgresql :

last_change is timestamp (0) without time zone NOT NULL
device_macaddr is macaddr NOT NULL
device_id is bigint NOT NULL

We recently did update both the DB and the jdbc driver from 8.4 to 9.4

I was able to pinpoint the change to the jdbc driver but was unable to find the changelog which exactly showed this change of behaviour.

Since then the above statement seems to not work anymore, while it previously worked despite being possible the wrong datatype back then as well.

Is there a way to "lessen" the strict detection from the Postgres 9.4 DB Server for that specific case?

I did read the enhancement of the jdbc driver to include those additional datatypes, however this hasn't worked for me yet so I try to get a workaround on the other end of the options i have.

Shamelessly stolen from Craig's answer over SO.

You need to create a cast to make the varchar -> macaddr coercion work automatically.  It is slightly tricky, as there is no function that does exactly this, so we have to wrap an internal function into something we can use:

CREATE TABLE mac (addr macaddr);

INSERT INTO mac VALUES ('11:11:11:11:11:11'::varchar);
=> ERROR:  column "addr" is of type macaddr but expression is of type character varying

CREATE OR REPLACE FUNCTION macaddr_invarchar(varchar)
RETURNS macaddr LANGUAGE SQL AS $$
    SELECT macaddr_in($1::cstring);
$$ IMMUTABLE;

CREATE CAST (varchar AS macaddr) WITH FUNCTION macaddr_invarchar(varchar) AS IMPLICIT;

INSERT INTO mac VALUES ('11:11:11:11:11:11'::varchar);
=> INSERT 0 1


MS SQL login periodically doesn't work Instance info: Microsoft SQL Server 2017 (RTM-CU13) Standard Edition (64-bit) on Windows Server 2019 Datacenter
Location: AWS

A Java app on another AWS server logs into the SQL instance using a local SQL account, and 95% of the time, the login connects, and everything is fine. The other 5% I get following error:

"Login failed for user 'Blah.' Reason: Failed to open the explicitly specified database 'Blah_Database.' [CLIENT: xxx.xxx.xxx.xxx]"

I know the 2 IP's that the login request is coming from and the connection string names the database which is present. The permissions are set correctly.
I've captured the event running a Profiler Trace looking for Event Class "Audit Failed Login." But, This doesn't give me any more info than the SQL error log.
Any other event classes I can add to the profiler to gather more info?
I've verified the the database Auto-Close feature is off. I've also increased the remote login timeout to 30.

Perhaps AWS has some stuff similar to Azure Serverles' functionality? I.e., it takes a while to spin up the database. I would increase the connection timeout drastically as a first test and then take it from there.


Resume a suspended task I was applying an index to a database with archived data. I knew this would take quite a while. It was at 14 hours running when I left it last night. I came back to it this morning and enterprise manager told me it had lost connection.

On checking activity monitor on the server, I can see the task. Its state is SUSPENDED with a wait type of IO_COMPLETION.

Is there a way to resume this or kill it off and start again?

I ran sp_who2 but it's not being blocked by any other activity and has a status of ROLLBACK. If I then run kill 61 with statusonly, SQL tells me that;

  Status report cannot be obtained. Rollback operation for Process ID 61
  is not in progress.

Just looking for options on what I can do/try next. Thanks.

kill 61 with statusonly. SQL tells me that,Status report cannot be obtained. Rollback operation for Process ID 61 is not in progress

It seems Kill With statusonly reports ,only when the session is being killed manually

From Paul Randal :(Emphasis mine)

  KILL WITH STATUSONLY generates a report only if the session ID or UOW is currently being rolled back because of a previous KILL session ID|UOW statement.

So in your case,the operation was not killed..So you will not be able to track status

You can use Sys.dm_Exec_requests to get rollback estimation time

select percent_complete,estimated_completion_time,* from
sys.dm_exec_requests
where status like '%rollback%'

further on resuming part,you can resume If you are using Index Reorg not Rebuild

some more info on reorg vs rebuild (I read about this on MSDN,but not able to find now)

  Reorganize works on page level, so if you happen to stop in the middle, it will roll back that particular page which is 8kb and not the whole thing.

So if you are not using Reorg,only option is to restart index rebuild and it starts from beginning again


Is there a way to load a sql script with a specified encoding into a running SQL Server Management Studio (SSMS)? Just as the question says.
I have scripts with 3 different encodings and feel offended by having to manually select of about 200 possibles I was never interested in. ( I didn't count them ).

this might not be the answer you expected/hoped for but take a look at DbVisualizer. This is a nice tool that organizes sql scripts as bookmarks and we can choose in what encoding they are written to disk. The tool is multiplatform and multidatabase.
Using this I - as an oracle dba - can use a SQL server database without having any dedicated SQL server tool installed, from my macbook.
Choosing a smart encoding if you work multiplatform can help.

I hope this helps.


create table from another table I have master categories table like this

category_id    category_name            parent_name           parent_id
     1         animals & pet supplies       null                   0
     2         live animals            animals & pet supplies      1
     3         pet supplies            animals & pet supplies      1
     4         bird supplies           pet supplies                3
     5         clothing                     null                   0
     6         mens clothing           clothing                    5
     7         womens clothing         clothing                    5
     8         shirts                  mens clothing               6
     9         formal shirts           shirts                      8

when the user selects category i need to create new table with all categories and subcategories & sub sub categories (infinity subcategories). for example if user selects animals & pets supplies the new category table should be like this

  category_id    category_name            parent_name           parent_id
     1         animals & pet supplies       null                   0
     2         live animals            animals & pet supplies      1
     3         pet supplies            animals & pet supplies      1
     4         bird supplies           pet supplies                3

if the user selects clothing category then new category table should be like this

 category_id    category_name            parent_name           parent_id
     5         clothing                     null                   0
     6         mens clothing           clothing                    5
     7         womens clothing         clothing                    5
     8         shirts                  mens clothing               6
     9         formal shirts           shirts                      8

anybody please help me in achieving this. thanks in advance. suggestions to change in table designing also welcome to achieve this

Why do you need to create new table on selection?

Can user select any sub category directly ?
i.e. Instead of clothing, Men's Clothing

Regards,
Maulin.


Why am I getting cardinality issues when querying a single table I have the following query:

SELECT
    [iri].[RateItemKey],
    [iri].[RateItemType]
FROM
    [prism72].[dbo].[ispRateItem] AS [iri]
WHERE
    [iri].[Status] = 1
    AND (
        [iri].[RateItemType] IN (3,2)
    )

The ispRateItem table has the following indexes:

CREATE NONCLUSTERED INDEX [COVIX_ispRateItem_RateItemType_Status] ON [dbo].[ispRateItem]
(
    [Status] ASC,
    [RateItemType] ASC
)
INCLUDE ( [RateItemKey]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [CLIX_ispRateItem] ON [dbo].[ispRateItem]
(
    [RateItemKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ispRateItem] ADD  CONSTRAINT [RateItem_PK] PRIMARY KEY NONCLUSTERED
(
    [RateItemKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

I've updated the statistics using:

UPDATE STATISTICS [ispRateItem] WITH FULLSCAN, ALL;

However I index the table I get the same cardinality issue: Est Rows 1565, Actual Rows 4315. I've even tried removing all indexes (resulting in a table scan).

What could be causing this?

Please note that I'm a beginner to query tuning.

Thanks.

Alan

I think your problem is the fact your WHERE clause has multiple predicates on the same table. This article by Paul White explains the issue.

It may be possible to get around this issue by having a computed column that does what your current WHERE clause does. You could then use this column on it's own but this would need a bit of testing to make sure it works OK.

The resolution:

James suggestion got me looking into multiple predicate issues. So I tested using single predicates and noticed that integers were being stored as varchar for the RateItemType field.

The problem was resolved by making the following change:

[iri].[RateItemType] IN (3,2)

to

[iri].[RateItemType] IN ('3','2')


Named pipes connection closed when subjected to load We're hosting a service where we have a  messaging service. When a message arrives, it checks which subscribers it should be sent to by connecting to the database, and querying the subscriptions table.

A few months ago we started seeing the following error when the message service tries to connect:

  An error has occurred while establishing a connection to the server.
  (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)
  An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)(Microsoft SQL Server, Error: 1326)

We were at that point running on virtual servers hosting SQL Server 2008, and we were moving to a clustered SQL Server solution on physical hardware.

After the move to the physical hardware the error dissapeared, so we put it down to having something to do with the virtual servesr. However, now it is back.

It seems that the error occurs when there is high load.

Has anyone experienced why this happens? Does anyone have any experience of solving it?

What do you consider to be high-traffic?

Turn off Named Pipes and turn on TCPIP
It's lighter in it's communication. In my SQL installations, I never use Named Pipes except if the Application is on the same machine as the SQL service, and that's really ... never ... I always separate the application and SQL Server because monitoring and performance measures are more difficult to obtain and decipher.

Separate Applications/services from SQL Server
Also, if you have SQL and your application on the same server, put them on different servers from each other so that the SQL server is dedicated.

Index and stats maintenance
Make sure your indexes and stats are all maintained. Sometimes the activity can be high enough when stats and indexes are stale or fragmented that this in and of itself consumes most resources. Especially with standard installs and with no adjustment to CPU and RAM pinnings (see next item).

Pinning CPU/RAM to settings so OS/Networks/Services are not entirely consumed by SQL

update the CPU Affinity to use all but 1 or 2 cpus (if box has 8 cpus, use 6 or 7 for SQL and leavning 1 or 2 unchecked for the OS/Network/Services to use)
set the MIN and MAX RAM for the SQL Server to be "pinned" at the same RAM setting (if box has 8GB or RAM, set MIN to 6144KB and MAX to 6144KB), or something where MIN=(MAX Machine RAM * 20%) or MAX=(MAX Machine RAM * 10%) rounded to multiples of 1024KB.

These last two settings help to make sure that the resources for the SQL are not entirely consumed by SQL and the OS and Network and other services can respond and perform their standard requests. I've had situations where so much activity was happening (1K of simultaneous users, all indexes, stats, etc were good) that the box resources were completely consumed by SQL and the network services reported that the box could not be reached. Once I made these changes, and have been doing this ever since, many of my resource-consuming problems have gone away.

You could even try these last two with Named Pipes on and it should work because the Network can respond with the high traffic. But I would recommend TCPIP instead.


On Sql Server can you disable an account at the database level? Let's say I have an account at the server level and they have access to database1, database2, and database3 on the server.

If I run ALTER LOGIN "account_name" DISABLE this will disable the account and they can not access any of the databases on the server.

If I want to disable the account from database2, is the only way to do that by DROP USER "account_name"? Is there something similar to the server disable that can be done at the database level?

You can simply deny connect privileges to the specified database. This may be less disruptive than dropping the user (in case you want to give them access again later, you won't have to re-create the user, set up all the permissions again, etc.).

USE database2;
GO
DENY CONNECT TO account_name;

When connect is denied, if they try to establish a new session connected to this database, they will get:

  The database database2 is not accessible.

If they try to select from a table in the denied database, for example, they'll see:

  Msg 916, Level 14, State 1
  The server principal "account_name" is not able to access the database "database2" under the current security context.

To give them access again:

USE database2;
GO
GRANT CONNECT TO account_name;


create new MySQL database and user using OS root credentials I have root permissions on a server (running Redhat):

[root@myserver /]#

... but not on the MySQL instance that's running on the server:

[root@myserver /]# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

I'd like to create a new database on the MySQL instance (version 5.6.26), and an account with GRANT ALL PRIVILEGES ... on the new database.

Is this possible (e.g. using mysqladmin) using only the OS root account?

I believe you will need to install another, separate instance of MySQL, configure it to run on a different port, specify that port in your login screen, and then do as you like.

You can do this by simply installing MySQL to another disk location, and then editing the ini file.  On Windows and Mac installations, this is called my.ini

Alternatively, I think your only choice is to get permissions granted to you on the currently installed MySQL instance.  The root password to the OS and the root password to the MySQL server instance are entirely distinct (as I'm sure you know).


List of all missing order numbers that are not in the table Our workplace has started to scan all documents rather than manually keep them in archives etc.

On average we are scanning 1,000 documents per time (this happens about 30 times per day).

Each time we scan 1,000 documents we get a small amount of errors/warnings on OCR.

Is it possible to provide the lowest and highest order numbers in to a variable and get SQL server to output a list of all missing order numbers that are not in the table between the lowest and highest order numbers?

For example

OCROrderNo
---------------
9001
9002
9006
9007
9008
9009

Lowest number 9001, highest number 9009

MissingOrderNos
--------------------
9003
9094
9005

Additional information

As per the answer, this what I am attempting:

DECLARE @Start INT = 1497389
        , @End INT = 1498264

SELECT @Start + N - 1
FROM   dbo.Numbers
WHERE  N <= ( 1 + @End - @Start )
EXCEPT
SELECT [ORDERNUMBER]
FROM   [dwdata].[dbo].[ORDERS]
ORDER BY [ORDERNUMBER] DESC

Resulting in:

  Msg 207, Level 16, State 1, Line 10
  Invalid column name 'ORDERNUMBER'.

  Msg 104, Level 16, State 1, Line 10
  ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

First create an auxiliary numbers table larger than the maximum range you will ever be interested in.

CREATE TABLE dbo.Numbers
(
N INT primary key
);

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
    E02(N) AS (SELECT 1 FROM E00 a, E00 b),
    E04(N) AS (SELECT 1 FROM E02 a, E02 b),
    E08(N) AS (SELECT 1 FROM E04 a, E04 b),
    E16(N) AS (SELECT 1 FROM E08 a, E08 b),
    E32(N) AS (SELECT 1 FROM E16 a, E16 b),
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
INSERT
INTO  dbo.Numbers
SELECT TOP(100000) N
FROM cteTally
OPTION (MAXDOP 1);

Then the query is simple

DECLARE @Start INT = 9001
        , @End INT = 9009

SELECT @Start + N - 1 AS ORDERNUMBER
FROM   dbo.Numbers
WHERE  N <= ( 1 + @End - @Start )
EXCEPT
SELECT [ORDERNUMBER]
FROM   [dwdata].[dbo].[ORDERS]
ORDER BY [ORDERNUMBER] DESC


Insert trigger function from select query, plus static values pulled from the new. We're having an issue writing a trigger function that involves 3 tables:  CustomerProfile, DropdownStats and OrderDetails. A simplified schema of each is as follows...

CustomerProfile table...this table has a row for all of the column names of the OrderDetails table.

CustID ColumnName  IsDropDown
------ ----------  ----------
1      firstcolumn false
1      ...
1      orderid     false
1      format      false
1      department  false
1      ...
1      lastcolumn  false

DropdownStats table...

OrderID ColumnName ColumnValues CountOfColumnValues
------- ---------- ------------ -------------------
1       format     circle       15
1       format     square       23
1       format     triangle     3
2       format     circle       10
3       format     square       5
3       format     triangle     10

OrderDetails table...

firstcolumn ... orderid format department ... lastcolumn
----------- --- ------- ------ ---------- --- ----------
data        dta 1       circle 123        dta data
data        dta 1       square 134        dta data
...
data        dta 2       circle 354        dta data
...

The trigger is written for the CustomerProfile table as an AFTER UPDATE trigger.

If the 4th row in the CustomerProfile table (where columnname = format) transitions the IsDropDown from false to true, then we have a to ADD rows to the DropdownStats table as they are pulled from the OrderDetails table.

The trigger function as written is producing results in the DropdownStats table like below but we want results like the sample above.

DropdownStats table...

OrderID ColumnName ColumnValues CountOfColumnValues
------- ---------- ------------ -------------------
1       format     format       41
2       format     format       10
3       format     format       15

The problem is having the trigger function insert a static column name and then have a separate row for each of the distinct values found for that column and a count of rows that hold those values. Here is one (of several) attempt at this...the problem is the two new.columnname They should not be the same.

if old.isdropdown <> new.isdropdown then --if there was a change
  if new.isdropdown = true then --and the change was from false to true
    insert into dropdownstats
    (orderid,columnname,columnvalues,countofcolumnvalues)
      select distinct
      orderid
      , new.columnname -- we want this to be the static actual name of the column, repeated for every record inserted (Ex: 'format')
      , new.columnname -- we want a row for each of the values found for the column in the database (Ex: 'circle', 'square', 'triangle', etc.)
      , count(new.columnname) -- the count of the records found for the value (Ex: for 'circle, count was 15)
      from orderdetails
      group by orderid, new.columnname;
  else --the transition was from true to false
    delete from dropdownstats as dd where dd.columnname = new.columnname;
  end if;
end if;

If I just use the query tool and manually create and run the select statement shown below, I get what I want.

select distinct orderid, 'format', format, count(format) from orderdetails group by orderid, format;

But how do we translate the manual query into an equivalent trigger function? How do we distinguish the two "new.columnname" from each other?

The structure of OrderDetails does not change. It is very stable.

Your problem is that your query is using new.columnname to mean different things.

In the SELECT list of the subquery, the first new.columnname is intended to use the actual value stored new.columnname. The second new.columnname, the one in the COUNT() and the one in the GROUP BY are all intended not to be the literal value stored in new.columnname, but a reference to the actual OrderDetails column named in new.columnname. In other words, the first is the string literal "format", while the second is the database column OrderDetails.format.

Unfortunately, the engine will view each of new.columnname in the query as a string value "format", and will never interpret it as OrderDetails.format.

There are at least two potential ways to get around this problem:

Explicit INSERTs for each column

For each of the columns in OrderDetails, write a separate INSERT statement in your trigger:

if old.isdropdown <> new.isdropdown then --if there was a change
  if new.isdropdown = true then --and the change was from false to true
    if new.columnname = 'first_column' then
      insert into dropdownstats
      (orderid,columnname,columnvalues,countofcolumnvalues)
        select
        orderid
        , 'first_column'
        , first_column
        , count(*)
        from orderdetails
        group by orderid, first_column;
    elsif new.columnname = 'format' then
      insert into dropdownstats
      (orderid,columnname,columnvalues,countofcolumnvalues)
        select
        orderid
        , 'format'
        , format
        , count(*)
        from orderdetails
        group by orderid, format;
    elsif ...
    end;
  else --the transition was from true to false
    delete from dropdownstats as dd where dd.columnname = new.columnname;
  end if;
end if;

(If there's special handling you want to do if new.columnname doesn't match any of the values, you can use CASE ... WHEN ... ELSE ... END; instead of IF ... THEN ... ELSIF ... THEN ... END;, if you like.)

Dynamic SQL

Unless PostgreSQL has some restriction against it, you could use dynamic SQL to build the SQL statement you need each time through:

if old.isdropdown <> new.isdropdown then --if there was a change
  if new.isdropdown = true then --and the change was from false to true
    EXECUTE format( 'insert into dropdownstats '
                    '(orderid,columnname,columnvalues,countofcolumnvalues) '
                      'select '
                      'orderid '
                      ', %L '
                      ', %I '
                      ', count(*) '
                      'from orderdetails '
                      'group by orderid, %I '
                   ,new.columnname, new.columnname, new.columnname);
  else --the transition was from true to false
    delete from dropdownstats as dd where dd.columnname = new.columnname;
  end if;
end if;

CAVEATS: Code is untested, and PostgreSQL is not my primary SQL language, so I may have included typos or misunderstood some subtleties.


How to modify MySQL 5.5 settings that were set during the installation? Especially the one that defines how MySQL is gonna use server's CPU. I'd like to boost performance. Also, what are other ways to boost MySQL performance? (Maybe some code optimization hints?) Anyway. Any information will be useful. Thanks in advance.

Use InnoDB tables and configure it properly.  This site is a great resource:

http://www.mysqlperformanceblog.com/


How to force MySQL Workbench to use constraints in my file? I'm currently working on a project that requires I write my own constraints for my database (to show I know how they work). I'm attempting to test them in MySQL Workbench however the programs settings keep kicking in and overriding the SQL in my document. As a test I am setting all DELETE's and UPDATE's to CASCADE however in the settings it is set to NO ACTION and I am getting error messages.

I am under the assumption that the above is the correct settings page to alter but there is no "DO NOTHING I WANT TO USE MY OWN SETTINGS" option. Is there a way around this? I am trying to emulate another environment which honours no constraints at all to ensure that mine as working.

I don't really understand the trouble you have. These settings are just defaults. They are used when you create a relationship. But you can then change the actions to anything you like. MySQL Workbench won't change them automatically to something else.


Try this one

with cte as (
select metric_id, value, datetime, ROW_NUMBER () OVER (PARTITION BY metric_id ORDER BY datetime desc) as rn
from his_metric
)

select n.ip, m.id, m.key, m_h.value, m_h.datetime
from metric as m
join node as n on m.node_id = n.id
join cte as m_h on m.id = m_h.metric_id and m_h.rn=1


in postgres, can one optimize a table across partitions? in a database with many companies, segregating them using schema and search_path

almost all access comes at the company level, that is, limited to a single schema.  some data-warehouse and accounting functions need to scan all schema.

for this problem, per @horse_with_no_name's most excellent suggestion, using the schema themselves as partitions.  for example

create table global.usr(
    usr_id int primary key,  // these will be unique across all partitions
    name varchar );

create table cmp1.usr(
    usr_id int primary key,
) inherits( global.usr );

create table cmp2.usr(
    ....

if we populate

insert into cmp1 usr values( 11, 'eleven' );
insert into cmp1 usr values( 12, 'twelve' );

insert into cmp2 usr values( 21, 'twenty-one' );
insert into cmp2 usr values( 22, 'twenty-two' );

insert into cmp3 usr values( 31, 'thirty-one' );
insert into cmp3 usr values( 32, 'thirty-two' );

this works beautifully at the schema level:

set search_path = 'global';
select count(1) from t;   => 6

set search_path = 'cmp1, global';
select count(1) from t;   => 2

set search_path = 'cmp1, cmp2, global';
select count(1) from t;   => 4

set search_path = 'global, cmp1';
select count(1) from t;   => 6

the question is whether there is any way to optimize at the global level.  for example, would it be possible to create an index on global.usr_id that would span all partitions?

the question is whether there is any way to optimize at the global level. for example, would it be possible to create an index on global.usr_id that would span all partitions?

Sadly the answer is "No" - global indexes are not currently supported


Moving SSRS reports from one Server to other Is there any way to move all SSRS reports from one server to other, other than downloading each .rdl file and uploading them one by one to the new one?

I followed the steps in this forum post on MSDN, but no use.

Just migrate the objects with the "ssrs_migration.rss" script.
Much easier than database keys and restores

rs.exe -i ssrs_migration.rss -e Mgmt2010 -s http://SourceServer/ReportServer -u Domain\User -p password -v ts="http://TargetServer/reportserver" -v tu="Domain\Userser" -v tp="password"

I've used this many times with good results

Script sources ("ssrs_migration.rss")

https://docs.microsoft.com/en-us/sql/reporting-services/tools/sample-reporting-services-rs-exe-script-to-copy-content-between-report-servers
https://msdn.microsoft.com/en-us/library/dn531017(v=sql.120).aspx


BACKUP failed to complete - with spaces, for no reason Every day my database backup fails for some databases and does fine for others. I'm calling the backup through a Stored Procedure that runs scheduled in a job. Tonight I'm going to run the procedure in a query window with a WAITFOR in place just to check. In the \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG the message is:

2011-12-28 07:49:52.43 Backup      Error: 3041, Severity: 16, State: 1.
2011-12-28 07:49:52.43 Backup      BACKUP failed to complete the command BACKUP DATABASE                                                                                . Check the backup application log for detailed messages.

Obs: the log actually contains all these spaces and no further info.

Well, it looks like 3041 is the generic "something went boom with the backup" error.

Have you tried just running the backup SQL from SQL Agent without using the wrapping stored procedure?  I'm assuming the stored procedure you're using for the backup lives in the master DB (or at least not in the DB you're backing up).

Also, you could use this query to check your backup status (last 7 days).  There might be something else going on that you don't know about and that's interfering with your backups.

SELECT
 CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
 msdb.dbo.backupset.database_name,
 msdb.dbo.backupset.backup_start_date,
 msdb.dbo.backupset.backup_finish_date,
 msdb.dbo.backupset.expiration_date,
 CASE msdb..backupset.type
   WHEN 'D' THEN 'Database'
   WHEN 'L' THEN 'Log'
 END AS backup_type,
 msdb.dbo.backupset.backup_size,
 msdb.dbo.backupmediafamily.logical_device_name,
 msdb.dbo.backupmediafamily.physical_device_name,
 msdb.dbo.backupset.name AS backupset_name,
 msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
  msdb.dbo.backupset.database_name,
  msdb.dbo.backupset.backup_finish_date


What is the definition of bad circular dependency? I have been searching for while for a good resource, where the circular dependency is well explained, unfortunately nothing good found. Therefore I tried to understand exactly which kind of circular dependency I should avoid. The problem is I found some resources which explain in contradictory manners. Could someone explain exactly please which types of circular dependency we should avoid (and why)?

Taking these relationship as an example:

Source

This relationship is mentioned here to be bad (I do not understand why).

However, the same relationship is mentioned to be not a problem (and is described as NOT circular) here:

Models <--------------------------- SuperSets
   ^                                    ^
   |                                    |
   |                                    |
Tasks  <---------------------------- Sets

Source

Another example is this:

Source

I also do not understand why is this a circular relationship?

All of the previous relationships seem to me not curcular (the arrows direction is not going back to the same point). I think there is something wrong in my understanding for circular dependency term. Could some one explain that for me please in general, and particularly also maybe on the previous examples?

There is nothing circular in any of these cases. In "circular reference" scenario you have "a chicken and an egg problem" -- can not insert a row into a table because it is always missing a reference to some other table, like in:

user {user_id, address_id, ...}
  PK {user_id}

FOREIGN KEY {address_id} REFERENCES address

--
--
address {address_id, user_id, ...}
     PK {address_id}

FOREIGN KEY {user_id} REFERENCES user

The only problem that the author of that article has is not understanding
database design. The problem in his examples is simply lousy design stemming from insistence on single column PKs (IDs), and not understanding how business logic relates to DB constraints.

In the second example, his design assigns re-sellers commission based on the user and the product, instead on an actual purchase, which  makes no sense business-wise.
In the first example it is possible to assign a user to a task outside his project scope; again nothing to do with "circular", but not knowing how to implement constraints.

For the second example you may consider something like:

Product {ProductID}
     PK {ProductID}

Customer {CustomerID}
     PK {CustomerID}

Reseller {ResellerID}
     PK {ResellerID}

--
--
Purchase {PurchaseID, CustomerID, ProductID, ResellerID, ...}
      PK {PurchaseID}

FOREIGN KEY {CustomerID} REFERENCES Customer
FOREIGN KEY {ProductID}  REFERENCES Product
FOREIGN KEY {ResellerID} REFERENCES Reseller

And if you insist on the Commission table,  say if not every purchase involves re-seller.

Product {ProductID}
     PK {ProductID}

Customer {CustomerID}
      PK {CustomerID}

Reseller {ResellerID}
      PK {ResellerID}

--
--
Purchase {PurchaseID, CustomerID, Product_ID, ...}
      PK {PurchaseID}

FOREIGN KEY {CustomerID} REFERENCES Customer
FOREIGN KEY {ProductID}  REFERENCES Product

--
--
Commission {PurchaseID, ResellerID, ...}
        PK {PurchaseID}

FOREIGN KEY {PurchaseID} REFERENCES Purchase
FOREIGN KEY {ResellerID} REFERENCES Reseller

And for the first example:

Project {ProjectID, ...}
     PK {ProjectID}

User {UserID, ...}
  PK {UserID}

TaskType {TypeID, ...}
      PK {TypeID}

ProjectRole {RoleID, ...}
         PK {RoleID}

--
--
Task {TaskID, ProjectID, TypeID, ...}
  PK {TaskID}
  AK {TaskID, ProjectID}

FOREIGN KEY {ProjectID} REFERENCES Project
FOREIGN KEY {TypeID}    REFERENCES TaskType

--
--
ProjectTeam {UserID, ProjectID, RoleID, ...}
         PK {UserID, ProjectID}

FOREIGN KEY {UserID}    REFERENCES User
FOREIGN KEY {ProjectID} REFERENCES Project
FOREIGN KEY {RoleID}    REFERENCES ProjectRole

--
--
ProjectAssignment {UserID, ProjectID, TaskID, ...}
               PK {UserID, ProjectID, TaskID}

FOREIGN KEY {UserID, ProjectID} REFERENCES ProjectTeam
FOREIGN KEY {TaskID, ProjectID} REFERENCES Task

If you prefer single-column PKs then you can ADD them to ProjectAssignment and ProjectTeam tables, but you must keep the existing ones as AKs (unique) and reference them in foreign keys where applicable.

Note PK = primary key
     AK = alternate key (unique)
     All attributes (columns) NOT NULL


select users who do something on each date in sql server I have a table with 3 columns - user_id, building_id, datetime where each record is an access to a specific building and hence users can accesss a building many times. I need to select each user_id from each day who has accessed a specific building. It would seem like I need to cast the datetime to just a day and do a distinct across user_id, building_id and day. How would I do this in a modern version of SQL Server?

The query to list the users and dates for a given building (defined by @BuildingID):

DECLARE @BuildingID int = 42;

SELECT
    user_id,
    [Date] = CONVERT(date, [datetime])
FROM dbo.tablename
WHERE building_id = @BuildingID
GROUP BY
    user_id,
    CONVERT(date, [datetime])
ORDER BY
    [Date], user_id;

Beyond that, the requirement is not clear. In the title you say "on each date" - if you have a set of dates and you want the users who accessed @BuildingID on all of those dates, one approach could be:

DECLARE @BuildingID int = 42;

DECLARE @dateCount int;
DECLARE @dates TABLE([Date] date UNIQUE);

INSERT @Dates([Date]) VALUES('20190606'),('20190609');
SELECT @dateCount = COUNT(*) FROM @Dates;

;WITH users AS
(
  SELECT
    t.user_id
  FROM dbo.tablename AS t
  INNER JOIN @Dates AS d
     ON t.[datetime] >= d.[Date]
    AND t.[datetime] < DATEADD(DAY, 1, d.[Date])
  WHERE t.building_id = @BuildingID
  GROUP BY
    t.user_id
)
SELECT user_id
  FROM users
  GROUP BY user_id
  HAVING COUNT(*) = @dateCount;

Not sure if you are querying the data more often than you are writing it, but this might be a use case for an indexed view.


Subtract from two MYSQL Queries How can I subtract query result B from query result A so that I can see the difference between the two?

A:

SELECT
  character_name,
  ROUND(minutes_played / 60, 0)
FROM
  outfit_member
WHERE timestamp =
(
  SELECT
    MAX(timestamp)
  FROM
    outfit_member
)

B:

SELECT
  character_name,
  ROUND(minutes_played / 60, 0)
FROM
  outfit_member
WHERE
  timestamp < NOW() - INTERVAL 1 WEEK

What I want is the Result of ROUND(minutes_played / 60, 0) in B minus ROUND(minutes_played / 60, 0) in A. For example if B returns 1000 Hours and A returns 900 I get 100 Hours.

I assume all "characters" in result of second query exists in result from first query:

SELECT a.character_name
     , SUM(ROUND(a.minutes_played / 60, 0))
     - SUM(COALESCE(ROUND(b.minutes_played / 60, 0), 0))
FROM outfit_member a
LEFT JOIN outfit_member b
     ON a.character_name = b.character_name
WHERE a.timestamp =  (
      SELECT MAX(c.timestamp)
      FROM outfit_member c
)
AND b.timestamp < NOW() - INTERVAL 1 WEEK
GROUP BY a.character_name;

I added a sum since I assume that each character has several rows.

EDIT: since this was an accepted answer I won't change it but suspect that it may give the wrong answer under certain conditions. Anyhow, another option is to add the conditions to the aggregate functions like:

SELECT a.character_name
     , SUM(CASE WHEN a.timestamp =  (SELECT MAX(c.timestamp)
                                     FROM outfit_member c)
           THEN ROUND(a.minutes_played / 60, 0)
           ELSE 0
           END)
     - SUM(CASE WHEN a.timestamp < NOW() - INTERVAL 1 WEEK
           THEN ROUND(a.minutes_played / 60, 0)
           ELSE 0
           END)
FROM outfit_member a
GROUP BY a.character_name;

The latter query potentially scans more rows, but avoid a join


Attach database from SQL Express 2008 Edition to SQL Server 2008 Enterprise Edition My friend is stuck on moving a database stored in SQL Express 2008 Edition in a server (called server A) to SQL Server 2008 Enterprise Edition in another one (called server B). There are two SQL 2008 editions installed in the server A.

SQL Server 2008 Standard
SQL Express 2008

SharePoint is being run in server A (WFE/Application/Database role) and there are two content databases stored in SQL Express 2008. My friend just got the administration job. He doesn't know exactly what has happened because the ex-staff resigned without any confirmation. At this very moment, he is responsible for moving these content databases from server A to server B (SQL Server 2008 Enterprise is already installed in server B). After doing many times, he still can't attach these content databases to server B. I'm trying to help him by digging into SQL Server. Some errors often occurs:

Exclusive access could not be obtained because the database is in use.
The backup set holds a backup of a database other than the existing 'xx' database

Does anyone encounter this case? Any advice is greatly appreciated.

Regards,

T.s

I always like to take the cautious approach to administration.

Before making any changes to the environment, I would suggest your friend take database backups of all relevant databases at source.

Then, rather than using attach/detach. Restore the database to the new instance.


Suggest good ERD tool with UML notation I need to design an ERD with UML notation, can anyone recommend a tool that does this.

Sparx Enterprise Architect is a UML tool with a database modelling function.  It's not terribly expensive and pretty UML centric.  It also has some pretty good extensibility features if you're that way inclined.


PostgreSQL - CAST vs :: operator on LATERAL table function While I can
SELECT elem[1], elem[2]
FROM   ( VALUES ('1,2'::TEXT) ) AS q(arr),
       LATERAL CAST(String_To_Array(q.arr, ',') AS INT[]) AS elem
;

using an explicit call to CAST, I can't
SELECT elem[1], elem[2]
FROM   ( VALUES ('1,2'::TEXT) ) AS q(arr),
       LATERAL String_To_Array(q.arr, ',')::INT[] AS elem
;

using the implicitly calling :: operator:

ERROR: syntax error at or near "::"

One other location at which an explicit CAST is required:
CREATE INDEX ON ... ( CAST(<straw> AS <gold>) );

I doubt there is a syntactical reason, e.g. using extra enclosing parenthesis - which is incorrect here.
Is the explicit function call simply needed at this point as part of the low level implementation? Or does it follow any language rules?

It is a bit weird, yes, but the grammar will only accept something syntactically similar to a function call in a function-in-FROM expression.
So that is indeed a trick you can use if you want an arbitrary expression in a FROM clause: surround it with an unnecessary CAST expression.
PostgreSQL will happily treat anything that looks like a function as a table function in such a context.


Am I using the ORDER BY clause incorrectly I am trying to enumerate a database, created specifically for the purpose of learning SQL injections. I am trying to discover the number of columns a particular select statement might be using. I do this by "Ordinal" ordering the result set. So, I start with ORDER BY 1, .. BY 2, and so on until I get an error returning 'Unknown column'. Now, for an injection like so:

localhost/sqli?id=2' order by 7 AND  '1

The resulting SQL statement is:

mysql> SELECT * FROM table_name where id='2' order by 7 and '1';

This returns a result set, but I was expecting it to throw an error returning "unknown column '7' in 'order clause' ". Why does it not throw the error. The table that I am working with has only 3 columns.

Here is a sample in sqlfiddle-

The syntax for the select statment is the following

SELECT
    ...
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    ...

in the statement

SELECT * FROM table_name where id='2' order by 7 and '1';

7 and '1' must be interpreted as expression by the parser: 7 is the
integer 7 and not a row position in the select list. The manual says

  Logical AND. Evaluates to 1 if all operands are nonzero and not NULL,

so the expresion evaluates to 1


MySQL failing and very slow on importing a large file I have a 47 GB MySQL dump of a single table:

http://dumps.wikimedia.org/commonswiki/latest/commonswiki-latest-image.sql.gz

I ultimately want it into PostgreSQL, but since I didn't figure out an easy way to transform the MySQL SQL to PostgreSQL SQL I figured, I will get it into MySQL and then write a small ETL script to do this.

I initially tried to just load it using MySQL WorkBench Data Import/Restore, but it was failing.

Now I've run split -l 2000 commonswiki-latest-image.sql and have 20 files of roughly 2 GB each, but it still fails with:

23:12:28 Restoring C:\temp\commonswiki\xaa.sql

Running: mysql.exe --defaults-extra-file="c:\users\jmurdoch\appdata\local\temp\tmpi_ltz8.cnf"  --host=127.0.0.1 --user=root --port=3306 --default-character-set=utf8 --comments --database=mediawikiimages < "C:\\temp\\commonswiki\\xaa.sql"

ERROR 2013 (HY000) at line 331: Lost connection to MySQL server during query

Operation failed with exitcode 1

00:54:17 Import of C:\temp\commonswiki\xaa.sql has finished with 1 errors

It's also terribly slow as it only imported 209236 rows during the nearly 2 hours, but I think there are about 20M items to import, so at that rate it would take 200 hours to import.

I'm using Windows 7, MySQL 5.6.14 and MySQL Workbench 6.

My main questions:

Is there a script which would feed MySQL with the splitted files and perform automatic error recovery in case it times out?
Is there a conversion tool which would convert MySQL SQL to PostgreSQL SQL without first loading it into a database?

Using

max_allowed_packet = 256M

in MySQL config allowed to import the files without errors.


connect to server with IP address running sql-express instance through sql-management studio I have my VPS server in a remote location with ip address 172.16.11.2 (for example) having two instances of sql-server express running.

one instance is Microsoft SQL Server 2008 R2 (SP2)  where I connect from my local PC using remote desktop with the following credentials:

 servername : 172.16.11.2
 authentication : sql - server authentication
 userid: xx
 password:xx

the other instance is:

Microsoft SQL Server 2008 R2 (SP2) with advanced services. To connect to this machine I remote desktop to the server and type in

 servername : machinename\sql-express
 authentication : sql - server authentication
 userid: xx
 password:xx

now using remote desktop from my local machine how will I connect to this instance of sql-server.

I tried

 172.16.11.2\sqlexpress

but this failed. I also enabled sharedmemory/namedpipes/tcp-ip but still I am not able to connect. When I go to the machine where sql-express is installed and then type machinename\sqlexpress I am able to connect the sql-express instance, but from my local machine using sql-management studio if i type in ipaddress\sqlexpress I am not able to connect. It gives me network related issue error.

Ensure that the secondary instance (in this case SQL Express) has a TCP port set.

Under SQL Server Configuration Manager, go to SQL Server Network Configuration, then Protocols for [INSTANCENAME].
Right click TCP/IP and under the IP Addresses tab make sure the field "TCP Port" has a value (for example 1533).

Then try to connect to the instance with the newly assigned port:

172.16.11.2\sqlexpress,1533

Voila!


Create a user on database A with a procedure from database B (with EXECUTE AS) I have 2 logins :

LoginA: role dbcreator + CONNECT ANY DATABASE
LoginB: role sysadmin

I have 2 databases :

dbA: everyone has all rights on it
dbB: only LoginB has rights on it, LoginA is only the owner of that database but no more

I'm wondering if it is possible to create a user (linked to LoginA) in dbB from a stored procedure of dbA.
Considering this stored procedure has to be executed by LoginA.
Maybe some kind of "EXECUTE AS" mechanism could help ?
Also I have issues about how to use a "USE [dbB]" in a stored procedure.

The solution can have some rights modification,
but these can't give securityadmin nor sysadmin roles.

Here is what is done for now but clearly there is a lot of debug and functionalities to go, but the spirit of what I wanna do is in here :

CREATE PROCEDURE [dbB_UserAfterRestore]
AS
BEGIN
    SET NOCOUNT ON;

    USE [dbB]
    CREATE USER [HOSTNAME\LoginA] FOR LOGIN [HOSTNAME\LoginA]
    ALTER ROLE [db_datareader] ADD MEMBER [HOSTNAME\LoginA]
    ALTER ROLE [db_datawriter] ADD MEMBER [HOSTNAME\LoginA]
END
GO

Since LoginA is not member of sysadmin server role, the login must need IMPERSONATE permissions to use EXECUTE AS i.e.

USE dbB;
CREATE USER LoginA for login LoginA; -- This will create user under "public" database role
GRANT IMPERSONATE ON USER::LoginB to LoginA; -- This is required to use "EXECUTE AS"

Once LoginA got IMPERSONATE access, you can create procedure as follows in dbA where LoginA got full access (db_owner)

use dbA
go

CREATE PROCEDURE [UserAfterRestore]
    @TargetDBName varchar(128),
    @GranteeLogin varchar(128),
    @ExecuteAs varchar(128)
AS
BEGIN
    SET NOCOUNT ON;
    Declare @TSQL varchar(2000);

    SELECT @TSQL = 'USE [' + @DBName + '];
                    Execute as user = '''+ @ExecuteAs +''';
                    IF NOT EXISTS (SELECT name FROM SYS.database_principals WHERE name = '''+ @GranteeLogin +''')
                        BEGIN
                            CREATE USER [' + @GranteeLogin + '] FOR LOGIN ['+ @GranteeLogin + '];
                        END
                        ALTER ROLE [db_datareader] ADD MEMBER ['+ @GranteeLogin +'];
                        ALTER ROLE [db_datawriter] ADD MEMBER ['+ @GranteeLogin +'];'
    Print 'Executing: ' + @TSQL

    EXEC (@TSQL);
END
GO

Call procedure as follows:

exec dbA.dbo.[UserAfterRestore]
        @TargetDBName = dbB,
        @GranteeLogin = 'LoginA',
        @ExecuteAs = 'LoginB';

If the target is multiple databases, then LoginA must be either member of sysadmin server role or must be user of all databases (under public role) and GRANTED for IMPERSONATE on respective database's db_owner


SQL NULL Value to = A Sequential Number, Help! I have upgraded a database at work from a poorly designed Microsoft Access database to a SQL database.

The previous system allowed NULL values and duplicates to be inserted into a field that should not allow NULL values or duplicates.  Since the existing data already has some entries that are NULL, I cannot set NOT NULL constraints.

My solution would be to use a sequential operator.  For instance, NULL data would be changed to a sequential number that system administrators would know was a bogus number starting at something like 999999900, incrementing up from there. There are only 250 records that would require updating.

To make things more interesting this field is not a integer type, its a VARCHAR type as it is a Hardware ID.  The existing data contains both alpha and numeric data.

Any advice on how to update these NULL values using an incremental ID?

Well, starting with a numerical example pulled from the link at the bottom and then just changing the variable type from int to varchar() works because there is implicit conversion due to the math expression I assume. The field "id" is also a varchar().

  declare
      @start varchar(30) = 100000000;

   update t
      set @start = id = @start - 1
     from #TheTable t
    where id is null;

I came up with this mess before I realized the that the varchar() performed auto conversion. It requires a primary key (pk) but doesn't require a local variable.

  update t
     set id = r.NextId
    from #TheTable t
    join (select pk,
                 10000000 - row_number() over (order by rand()) as NextId
            from #TheTable
           where id is null) r
      on r.pk = t.pk
   where t.id is null;

(Above example was edited to fix bug after receiving comment)

Numeric Solution and many replies that may have an better answer

Update - Responding to comments:  To patch them from 9's to sequential.  Please double check the number of 9's, your comment listed the wrong number I think.

declare
   @fromValue varchar(30) = '9999999';  -- 7 nines

UPDATE t
   SET HardwareNumber = r.NextID
  FROM tblAsset t
  JOIN (select Asset_ID,
               10000000 - ROW NUMBER() over (order by RAND()) AS NextID
          FROM tblAsset
         WHERE HardwareNumber = @fromValue) r
    on r.Asset_ID = t.Asset_ID
 WHERE t.HardwareNumber = @fromValue;


Is there a way to have mysql automatically create partitions as needed? We have a large table with columns, date, tag, country. We would like to partition the table by YEAR(date), tag, country, but would like to have mysql create the partitions on the fly. i.e. We don't want to set the number of partitions up front when the partitions are setup and then have to remember to add new partitions in the future.

Why do you want to use partitions?  "Large" is not a reason; I have seen billion-row tables that work just fine without partitioning.  PARTITIONing are unlikely to provide any benefit.

If you will be deleting "old" data based on the YEAR(date), that is a good use case for partitioning.

More discussion shows how to DROP the oldest partition, etc.


XACT_ABORT OFF vs COMMIT in trigger I have a trigger on a table that kicks off several other processes. However, I would like to ensure that even if the other processes fail, the insert is successfully committed to the original table.

I've been doing this by issuing an explicit COMMIT TRAN in the trigger before calling my other procedure. This seems to have been working fine, except for an unsightly error message:

  Msg 3609, Level 16, State 1, Line 3
  The transaction ended in the trigger. The batch has been aborted.

Upon doing a bit more research I changed the procedure, adding SET XACT_ABORT OFF; at the very top, and removed the explicit commit. This seems to have had the same effect.

Which one of these is preferable? Are there any pitfalls I should be aware of with either method?

If you have no other code in the trigger that should be allowed to cancel the INSERT, then try the following:

Keep the XACT_ABORT OFF
Put the COMMIT TRAN back
Add a BEGIN TRAN at the end of the trigger

This should be the same issue as if you have an open transaction, exec a proc, and in that proc do a ROLLBACK (or even just a BEGIN TRAN and no COMMIT or ROLLBACK): it will error saying value of @@TRANCOUNT is different between entering and exiting the proc.


SQL Server Standard edition attach partitioned database I have a little problem. I'm using a SQL Server database version 11. I have to change database edition from enterprise to standard.

I overlooked fact that standard edition doesn't have partitioned function. But I moved all .mdf files to new server. It's about 200Gb of data.

I want to ask you question, if it's possible to attach database without partitioned tables. That tables i can moved over internet. It's about 40Gb, it's ok to moved that file over internet.

Thanks for answer.

You cannot restore a database that contains partitioned tables in Standard Edition. You also cannot attach a database without supplying all the data files. You could restore individual files or filegroups, but it won't achieve what you're after.

Your only option is to get rid of partitioning before you take a backup and move it to Standard Edition.

BTW, if you query sys.dm_db_persisted_sku_features you'll get a list of all the active features in the database that might impact your migration.

EDIT 2017-09-15: not true any more since SQL Server 2016 SP1.


Why does sqlexpress run on .\sqlexpress while mssqlserver runs on .\ I have two computers.

One machine runs sqlexpress and the instance is named SQLEXPRESS

The other machine run mssqlserver and the instance is MSSQLSERVER.

Why is it that sqlexpress runs on .\sqlexpress while mssqlserver just runs on .\?

"Why is it that sqlexpress runs on .\sqlexpress while mssqlserver just runs on .\?"

Because SQL Server version 7 only supported one instance per server.  And when the ability to run multiple instances was introduced in SQL Server 2000 Microsoft has to figure out some way for clients to distinguish between the instances.

And back at that time connecting to SQL Server using protocols other than TCP/IP was still common, especially Named Pipes, so just using different ports wouldn't have worked.


Will stopping my query rollback all my queries I have started a Query like the following in SQL Mgt studio:

Currently running sp_who I can see the Query is running for TBCompany_503
My question is that if I Kill the process for TBCompany_503 will it rollback the changes for TBCompany_500 - TBCompany_502 ?
Reason I want to stop it, and run them separately.

Not unless you wrapped those calls in the same transaction, otherwise those previously ran updates are committed already.


Determine a set of id's that exist in a set of tables: options needed to speed up or parallelize the algorithm I am trying to determine a set of id's that that exist in a set of tables, but the size of the tables are > 10 million records, the database is denormalized, normalization is not an option and the query either takes to long to complete or never completes.

I'm not sure how to partition this sort of job so that it can be run in parallel so that it takes less time and so if a step/subsection fails it can continue from where it left off.

A simplified example would be: find the customer in 3 tables whose Id matches in all tables.

The initial idea was to start looking at how sql engine inner LOOP JOIN actually works to try get ideas on how to partition it so that parts could be run in parallel. But without knowing anything about the various data science algorithms, I suspect there is some standard way to do this.

The rather cryptic original requirement was:

Compare party_id column in the three tables
to identify the customer available in three table i.e. it is AND operation between three.

SAMPLE1.PARTY_ID AND SAMPLE2.PARTY_ID AND SAMPLE3.PARTY_ID

If the operation is OR then pick all the customers available in the three tables.

SAMPLE1.PARTY_ID OR SAMPLE2.PARTY_ID OR SAMPLE3.PARTY_ID

AND / OR are used between tables then performed the comparison as required.

SAMPLE1.PARTY_ID AND SAMPLE2.PARTY_ID OR SAMPLE3.PARTY_ID

I set up some 4 test tables each with with this definition

CREATE TABLE `TABLE1` (
  `CREATED` datetime DEFAULT NULL,
  `PARTY_ID` varchar(45) NOT NULL,
  `GROUP_ID` varchar(45) NOT NULL,
  `SEQUENCE_ID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`SEQUENCE_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=978536 DEFAULT CHARSET=latin1;

Then added 1,000,000 records to each just random numbers in a range that should result in joins.

I used the following test query

SELECT `TABLE1`.`PARTY_ID` AS `pi1`, `TABLE2`.`PARTY_ID` AS `pi2`, `TABLE3`.`PARTY_ID` AS `pi3`, `TABLE4`.`PARTY_ID` AS `pi4` FROM `devt1`.`TABLE2` AS `TABLE2`, `devt1`.`TABLE1` AS `TABLE1`, `devt1`.`TABLE3` AS `TABLE3`, `devt1`.`TABLE4` AS `TABLE4` WHERE `TABLE2`.`PARTY_ID` = `TABLE1`.`PARTY_ID` AND `TABLE3`.`PARTY_ID` = `TABLE2`.`PARTY_ID` AND `TABLE4`.`PARTY_ID` = `TABLE3`.`PARTY_ID`

It's supposed to complete in under 10 min and for table sizes 10x larger.
My test query still hasn't completed and it has been running for 15 min

This belongs on dba.stackexchange.com  but I don't have the rep to comment
(and the reason I don't have the points is I gave out a 100 point bounty)

You don't give the sql an algorithm.  You give it indexes and a good query (some times with query hints).  SQL will parallelize on its own.

PartyID would be better as an int and then have another table for the text
Int or VarChar put and index on it

select table1.partyID
  from table1
  join table2
    on table2.partyID = table1.partyID
  join table3
    on table3.partyID = table1.partyID

for an or just do do a union

select table1.partyID
  from table1
union
select table2.partyID
  from table2
...


Select children of product, with no other active parents Given these two tables in Postgres 9.6:

CREATE TABLE product (
  id bigint PRIMARY KEY,
  status text
);

CREATE TABLE products_m_products (
  parent_product_id bigint REFERENCES product,
  child_product_id  bigint REFERENCES product
);

How to select all active children of a given product that have no other active parent?

As I understand it needs to be something like this:

Get all children of product A.
Get all the parents of the children.
Filter out all non active (status = 'DELETED') parents.
Count the remaining parents for each children.
Keep children whose parent count is 1 or less.

But how do I put this into a query?

I tried this:

select distinct child, parent
from product as child,
     product as parent,
     (select child_product_id
          from products_m_products
          where parent_product_id = 1337) as children,
     products_m_products as parents
where child.id = children.child_product_id
    AND parents.child_product_id = children.child_product_id
    AND child.product_status = 'ACTIVE'
    AND parents.parent_product_id = parent.id
    AND parent.product_status = 'ACTIVE';

But I'm not sure if this is correct.

We can assume that A is active, but it should be excluded from the final result since A will be deactivated.

This query should do it:

SELECT child.child_product_id
FROM   products_m_products child
JOIN   product             c_prod ON c_prod.id = child.child_product_id
WHERE  child.parent_product_id = 1337     -- user input here
AND    c_prod.status = 'ACTIVE'
AND    NOT EXISTS (
   SELECT
   FROM   products_m_products parent
   JOIN   product             p_prod ON p_prod.id = parent.parent_product_id
   WHERE  parent.child_product_id = child.child_product_id
   AND    p_prod.status = 'ACTIVE'
   AND    parent.parent_product_id <> 1337  -- same user input
   );

The anti-semi-join with NOT EXISTS only keeps children with no other active parent than the given A. This query does not care whether the given parent A is active itself.

Should be as fast as possible.

There are several basic techniques to check there is no related row in another table:

Select rows which are not present in other table

Related answer using a LEFT JOIN:

DELETE rows which are not referenced in other table


Combining Two Base Tables with a 1:M relationship Into One View with Distinct Rows I have two tables, Students and Race. Each student in the Students table has one row. Each student in the Race table has multiple rows, one for each race.

I'm trying to combine the tables into a view where I have one row for each student and each of their races are listed in separate columns; for example:

Any help or advice is greatly appreciated!

You will need to use the PIVOT function with a INNER JOIN to accomplish this:

SELECT StudentId
     , FName
     , LName
     , CASE H WHEN 0 THEN NULL ELSE 'H' END AS [Hispanic]
     , CASE W WHEN 0 THEN NULL ELSE 'W' END AS [White]
     , CASE B WHEN 0 THEN NULL ELSE 'B' END AS [Black]
     , CASE A WHEN A THEN NULL ELSE 'A' END AS [Asian]
     , CASE I WHEN 0 THEN NULL ELSE 'I' END AS [Indian]
 FROM (SELECT s.StudentId, FName, LName, r.[Race Code]
         FROM [Students] AS s
         JOIN [Race] AS r ON r.StudentId = s.StudentId)
PIVOT (COUNT([StudentId])
         FOR [Race Code] IN ([H],[W],[B],[A],[I])) AS p

If you are happy with just having the count as the row values then there is no need for the case statements and the query can be simplified to this:

SELECT StudentId
     , FName
     , LName
     , [H] AS [Hispanic]
     , [W] AS [White]
     , [B] AS [Black]
     , [A] AS [Asian]
     , [I] AS [Indian]
 FROM (SELECT s.StudentId, FName, LName, r.[Race Code]
         FROM [Students] AS s
         JOIN [Race] AS r ON r.StudentId = s.StudentId)
PIVOT (COUNT([StudentId])
         FOR [Race Code] IN ([H],[W],[B],[A],[I])) AS p


Does cost of an Operation matter.? I have a query which is slow when i run from SSMS and from application.I am trying to tune it.I took the execution plan and on checking I can see cost of Clustured Index Seeks on a table is 85%.That table has only two columns and one column is set as primary key.So doing Clustered Index Seek on the table is good i guess.Should i be concerned on that cost of the operation?

Yes and no.

Queries showing a high cost are usually a good place to start, when looking to tune a query. They are places where the query planner believed the query would spend a larger amount of its time and effort to get the query results.

However, when you've got the actual query numbers, one of the key things to check is how the estimate and the actual query run matched up. One of the first things to fix, when possible, are places where there's a big difference between the actual and the estimated numbers. For example, if the estimated and the actual rows are way off, then capturing/updating statistics for the table in question can result in significant gains.

Taking this into account, it may well be transactions that the query planner expected not to be significant, but where the estimated numbers are much smaller than the actual numbers, that are the transactions that most need tuning.

When estimated and actual numbers match well, then those high cost operations are again a good place to focus your tuning efforts. However, they shouldn't be the only thing you pay attention to.


Using Management Studio on Linux Is it possible to use Management Studio in Linux? I want to connect to my SQL Server instances with an IDE. How can i do that?

SSMS runs on Windows only. If you're looking for a Microsoft tool, check out Azure Data Studio. There are also third-party tools available for Windows, Linux and Mac.


Non-clustered index with all columns for "select * from"? I have a simple SELECT * FROM MyTable WHERE DataDate = '18-AUG-2013' query on a table that contains 340 columns and 3.4M rows.

Running estimated Execution plan in SSMS (Ctrl-L) suggests I create a non-clustered index on the DataDate and include every other column?

Is that a sensible thing to do (in general terms)?
Seems to me that this would vastly increase the indexing space and the indexing time on inserts etc. ?

Definitely don't do that. The missing index hints can be very useful but the recommendations can be dumb, occasionally outright ridiculous. Creating a copy of the entire table for the benefit of this query fits the later.

If your most common queries use a predicate on DataDate then it may be appropriate to change your tables clustered index to this. Only you can make that call based on your understanding of the workload.

SELECT * on a 340 column table smells suspicious. Do you really need all of those columns, every time?


What's the formula for calculating Key Efficiency, Key Buffer Used, and Query Cache Hitrate in MySql? Now, I have retrieved some dates from table of 'GLOBAL_STATUS' and 'GLOBAL_VARIABLES' by MySQL DB named information_schema .
I can retrieve

key_buffer_size

key_cache_block_size

query_cache_limit

Query_cache_size... and so on...
But, I don't know how to calculate the rate of Key hitrate, Key buffer used,and   query cache hitrate

so, someone can get the formula about those, such as
? / ? * 100% = ?

For your reference i am trying to explain some concepts also.

Key hit rate

There are basically two forms of key hit rate

1.Key Read Efficiency

  Key Reads: The number of physical reads of a key block from disk.

  Key Read Request: The number of requests to read a key block from the
  cache.

Key Read Efficiency = [1 - (Key_reads/Key_read_requests)]*100

  Key Read Efficiency: The ratio of the number of physical reads of a
  key block from the cache to the number of requests to read a key block
  from the cache in percentage. The MySQL performance is good if the
  value of Key Read Efficiency is 90 percent and above. Increasing the
  size of the cache improves the value of Key Read Efficiency and hence
  an improved the performance.

2.Key Write Efficiency

  Key Writes: The number of physical writes of a key block to disk.

  Key Write Request: The number of requests to write a key block to the
  cache.

Key Write Efficiency=(Key_write/Key_write_requests)*100

  Key Write Efficiency: The ratio of the number of physical writes of a
  key block to the cache to the number of requests to write a key block
  to the cache in percentage. For a good performance of the MySQL
  server, the value of Key Write Efficiency must be 90 percent and
  above. If it is found less, then you can increase the size of the
  cache to improve the performance.

Key Buffer Used

Find value of key_buffer_size as show variables like 'key_buffer_size';

convert it into MB.

Find the All MyISAM index Size :

  SELECT SUM(INDEX_LENGTH)/(1024*1024) 'Index Size' FROM
  information_schema.TABLES where ENGINE='MyISAM' AND TABLE_SCHEMA NOT
  IN('mysql','information_schema');

Find

(Index Size)/key_buffer_size(in MB) * 100

  if result is (<=100 ) then your all indexes are cached into key_buffer

  if result is (>100) then your all indexes are not cached into
  key_buffer you may gain performance boost by increasing
  key_buffer_size.

query cache hitrate

  Hit rate = Qcache_hits / (Qcache_hits + Com_select) * 100

  Insert rate = Qcache_inserts / (Qcache_hits + Com_select) * 100

  Prune rate = (Qcache_lowmem_prunes / Qcache_inserts) * 100


Convert postgres array to jsonb How we can convert postgres array :- ARRAY['{"id": "1"}', '{"id": "2"}'] to JSONB '[{"id": "1"}, {"id": "2"}]'

The array_to_json function with a cast to jsonb accomplishes this.

Example: select array_to_json(ARRAY['{"id": "1"}', '{"id": "2"}'])::jsonb;