SQL server DAC connection to mssqlsystemresource database I have a problem while connecting to the mssqlsystemresource database.

When I try to connect with DAC connection (without restarting SQL server with -m option) it works, but I can't use the mssqlsystemresource database, because the server is not in Single User mode. The only way to use this database is to restart the SQL server in Single user mode.

But once I restart the server with -m option I can no longer connect with DAC connection. I get this error:

  the server is on Single-user mode, only an admin can connect to the
  server at this moment, SQL error 18461

I'm using SSMS on the same server so no need to verify the firewall
I have SQL Browser service running
I'm using a member of the sysadmin group
I have no other DAC connection to this server

I don't know what to do next. Any help, please?

I just managed to connect to my SQL Server using DAC, where the SQL Server was started in single user mode. I verified single user mode by looking in the errorlog file.

Using SSMS is not a reliable way to use the DAC or single user mode. SSMS has a tendency to open several connections, and you never know which one comes first - even for a single query window. I used SQLCMD. I managed to connect using SSMS single query window as well, but again, sometimes it works sometimes not depending on the orders for the (background) connections you happen to get.

Use SQLCMD.EXE instead of SSMS.


Does my database need to connect to a server? I'm very new to SQL and databases. I'm currently following this tutorial for creating a Magento website: https://www.tutorialspoint.com/magento/magento_installation.htm

and I'm on Step 4. It says to create a new database for my site (which I'm running on localhost/magento right now). I downloaded MySQLWorkbench for this, and when I click "Create new connection", I put localhost/magento into the "hostname" section.

I'm just not sure how I'm supposed to create a database for my specific website, and if it needs to be connected to the URL.

Hope somebody can help me out in simple terms :)
Thanks.

A schema and a connection are two different things. So, creating a new connection does not mean to create a new schema/database.

You can create a database (or schema) in your server by using MySQL Workbench. After you opened a connection to your server, you will see the SQL IDE, a page with the treeview containing your current schemas, an SQL editor and various other stuff. In the schema tree use the context menu on any node to create the schema:

After that you can create a user with only the necessary rights for that single schema. Use the Administration -> Users and Privileges section for that:

Then assign the user access rights (e.g. for SELECT + DELETE) for that new schema by first allowing access to that new schema:

and finally set the required privileges:

Note: the backslash in the priv list is an escape sequence, because the string there is used as a pattern and the underscore ('_') is used as a placeholder in MySQL. Hence it must be escape to be used literally.


How to delete duplicate records in MySQL, in a table without IDs? I need to delete the duplicate records in this table. However, there is no id for each row.
Example Data

product
amount
quantity

table
2000
5

chair
300
25

TV
30000
4

bike
300
25

table
2000
5

chair
300
25

chair
300
25

Expected Results
I need to get this result.

product
amount
quantity

table
2000
5

chair
300
25

TV
30000
4

bike
300
25

Script with ID
If there were an id, I could have used:
DELETE p1 FROM products p1
INNER JOIN products p2
WHERE p1.id < p2.id AND p1.product = p2.product;

There is no any field combination which identifies the record uniqually.

I see at least 2 different solutions.

First solution: move unique records to a copy of table and replace original table.

CREATE TABLE temp LIKE products;
INSERT INTO temp
    SELECT DISTINCT * FROM products;
DROP TABLE products;
RENAME TABLE temp TO products;

Second solution: add temporary autoincrement, delete records using it, and drop temp field.

ALTER TABLE products ADD COLUMN temp SERIAL PRIMARY KEY;
DELETE t1.*
    FROM products t1
    LEFT JOIN ( SELECT MIN(temp) mintemp
                FROM products
                GROUP BY field1,field2 /* , ... */ , fieldN) t2
        ON t1.temp=t2.mintemp
    WHERE t2.mintemp IS NULL;
ALTER TABLE products DROP COLUMN temp;

UPDATE

In second variant: the additional column definition as a primary key is redundant. It is enough to use

ALTER TABLE products ADD COLUMN temp SERIAL;


Alternatives to an INSERT Trigger Looking for an example code in Oracle to do the same. i.e.

Remove an insert trigger on table T1 whose job is to create a row in T2 based on the derived data. How would this PL-SQL api look like?

What would I loose in the real world app by replacing an Insert trigger?

More clarification based on the responses below:
 We do not want Remote client to write to both table T1 and T2. This is a high throughput data and performance will suffer. Hence Remote client would write only to T1. Transfer from T1 to T2 should happen locally.

If T2 is derived data based on the contents of T1, then you may want to replace T2 with a materialized view.  If T2 is rarely accessed, and quick to derive, then you may just want to use a view.

On the application side, if you stop maintaining table T2, then the values it gets will become increasing incorrect.  This can be partially corrected by recalculating the derived values and updating T2 with the new values.


MaxScale login as root or other users I installed MaxScale 2.4.4 with Galera Cluster. I can access to galera with maxscale user but I can't login with other users to maxscale. I created a user in galera and another user with same username with maxctrl but it return Access denied error. How I can create users for applications in maxscale 2.4.4.

maxscale.cnf

[maxscale]
threads=1

[Galera-Monitor]
type=monitor
module=galeramon
servers=server1,server2,server3
user=maxscale
password=qwe123
disable_master_failback=1

[Galera-Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxscale
password=qwe123

[Galera-Listener]
type=listener
#router=readwritesplit
service=Galera-Service
protocol=MySQLClient
port=3306

[Write-Service]
type=service
router=readconnroute
router_options=master
servers=server1, server2, server3
user=maxscale
password=qwe123

[Read-Service]
type=service
router=readconnroute
router_options=slave
servers=server1, server2, server3
user=maxscale
password=qwe123

#[Splitter-Service]
#type=service
#router=readwritesplit
#servers=server1,server2,server3
#user=maxscale
#password=qwe123

#[Splitter-Listener]
#type=listener
#service=Splitter-Service
#protocol=MariaDBClient
#port=3306

[CLI]
type=service
router=cli

[CLI-Listener]
type=listener
service=CLI
protocol=maxscaled
address=0.0.0.0
port=6603

[server1]
type=server
address=192.168.122.93
port=3306
protocol=MySQLBackend

[server2]
type=server
address=192.168.122.17
port=3306
protocol=MySQLBackend

[server3]
type=server
address=192.168.122.13
port=3306
protocol=MySQLBackend

I found enable_root_user=1 for login to maxscale with root user but I don't know where should I add it;

maxctrl list servers
┌─────────┬────────────────┬──────┬─────────────┬─────────────────────────┬──────┐
│ Server  │ Address        │ Port │ Connections │ State                   │ GTID │
├─────────┼────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ server1 │ 192.168.122.93 │ 3306 │ 0           │ Master, Synced, Running │      │
├─────────┼────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ server2 │ 192.168.122.17 │ 3306 │ 0           │ Slave, Synced, Running  │      │
├─────────┼────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ server3 │ 192.168.122.13 │ 3306 │ 0           │ Slave, Synced, Running  │      │
└─────────┴────────────────┴──────┴─────────────┴─────────────────────────┴──────┘

maxscale runs at 192.168.122.222 and I want all applications connect to this address with thier own user;

I asked same question in stackoverflow. It trun out the problem was from maxscale user permissions.

GRANT SELECT ON mysql.user TO 'maxscale'@'maxscalehost';
GRANT SELECT ON mysql.db TO 'maxscale'@'maxscalehost';
GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'maxscalehost';
GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'maxscalehost';
GRANT SHOW DATABASES ON *.* TO 'maxscale'@'maxscalehost';

https://stackoverflow.com/a/59540995/5456910


Executing a very large SQL file I have a 830mb .sql file which is a scripted DB.

I wish to execute this SQL so it will create and insert data into a database on a hosted server. I cannot get it to open in SQL Server Management Studio or EMS SQL Manager.

How can I execute it?

Depends on db flavor, most provide command line tools to load sql files. Looks like you may be using ms sql, reference here http://technet.microsoft.com/en-us/library/ms165702(v=sql.105).aspx, more specific http://technet.microsoft.com/en-us/library/ms170572.aspx

sqlcmd -S myServer\instanceName -i C:\myScript.sql


How to move a table to secondary filegroup without losing data? How to move a table to another filegroup?

How to move a Heap table to another filegroup?

Do we need to move the Non-Clustered indexes to secondary filegroup after moving the table?

Which SQL Server Editions support moving of table to different filegroup?

How to move a table to another filegroup?

You need to rebuild your clustered index to new filegroup

  How to move a Heap table to another filegroup?

You need to organize it as a clustered index to new filegroup

  Do we need to move the Non-Clustered indexes to secondary filegroup after moving the table?

Yes, you can move nonclustered indexes to new filegroup.
Ent Edition Of SQL allows you to even rebuild indexes online.
Some links:
https://msdn.microsoft.com/en-us/library/ms175905(v=sql.105).aspx


Execution Plan Regeneration we have a database server with Sql Server 2008R2 that runs some queries every day; now these queries are fast (I suppose Sql Server has in memory some good execution plans).

If we restart the database server these execution plans in memory will be lost: is it possible that the next time the queries run, Sql Server regenerate some execution plans worst than the previous ones?

If it is so, is it possible to prevent this problem?

Thank you.

If we restart the database server these execution plans in memory will be lost: is it possible that the next time the queries run, Sql Server regenerate some execution plans worst than the previous ones?

Absolutely, it all depends on compile-time differences.  For instance, if a plan is compiled and cached with different values than before the plan cached was wiped (for whatever reason:  Service restart, machine restart, free proc cache, etc.), and those different values give a sub-optimal plan for the typical workload then yes that could be a worse plan for the typical workload than the previous ones.

One way around this is to utilize the OPTIMIZE FOR query hint.  As per BOL:

  Instructs the query optimizer to use a particular value for a local variable when the query is compiled and optimized. The value is used only during query optimization, and not during query execution.

Another method is to use Plan Guides.  Again, as per BOL:

  Plan guides let you optimize the performance of queries when you cannot or do not want to directly change the text of the actual query in SQL Server 2012. Plan guides influence the optimization of queries by attaching query hints or a fixed query plan to them.

Though I will caution you, plan guides can potentially be complicated and I wouldn't personally use them as a first-option to getting the optimal plan(s) you're looking for.  But, nevertheless, they are an option.


Upgrade lock from mongo 2.4 to 2.6 wont go away I have upgrade 3 months ago a mongoDB sharded cluster from 2.4.9 to 2.6.1 . I haven't faced any issues with the upgrade and my cluster working fine, but i still see the initial lock that required to update metadata to v5 on my config servers.

     "_id" : "configUpgrade",
    "process" : "pgdb2:27017:1399361525:1804289383",
    "state" : 0,
    "ts" : ObjectId("53688ff569fb6f34e9a2d398"),
    "when" : ISODate("2014-05-06T07:32:05.343Z"),
    "who" : "pgdb2:27017:1399361525:1804289383:mongosMain:846930886",
    "why" : "upgrading config database to new format v5"

Does anybody knows if this is bug or if it is going to affect any future operation?

The upgrade lock record is not removed post upgrade. A "state" of 0 means the lock is not being held. You can ignore or remove manually.

The lock definitions for MongoDB 2.6 are available here and are:

  0: Unlocked | 1: Locks in contention | 2: Lock held


Index Fragmentation Monitoring: Can I make this query faster? I have a query that is run against each database on two of my servers using a PowerShell script to initiate the script and throw the results into a CSV thus the need to have temp tables.

Is there a way I can limit what I am returning any further then I already have?  I tried adding my WHERE clauses to the temp table I am iterating through, as WHERE clauses on sys.dm_db_index_physical_stats do not actually work as a predicate, but rather just a filter after the fact according to MS docs.

DECLARE @object_id int
DECLARE @index_id int
DECLARE @partition_number int
DECLARE @DatabaseID int
SET @DatabaseID = DB_ID()

SELECT [object_id], [index_id], [partition_number], [rows] INTO #tempTest FROM (
SELECT indexes.[object_id], indexes.[index_id], partitions.[partition_number], partitions.[rows] FROM sys.tables AS tables
INNER JOIN sys.indexes AS indexes on tables.[object_id] = indexes.[object_id]
INNER JOIN sys.schemas AS schemas on tables.[schema_id] = schemas.[schema_id]
INNER JOIN sys.partitions AS partitions ON indexes.[object_id] = partitions.[object_id] AND indexes.[index_id] = partitions.[index_id]
WHERE schemas.[name] <> 'Audit'
AND indexes.[type] IN(1,2,3,4)
AND indexes.is_disabled = 0
AND indexes.is_hypothetical = 0
AND partitions.[rows] > 1000000) AS x

CREATE TABLE #tempTest2 (NK varchar(35), ServerName varchar(35), TimeStamp datetime, DatabaseName varchar(50), SchemaName varchar(50), ObjectName varchar (250), IndexName varchar (250),
ObjectType varchar(30), IndexType varchar (30), PartitionNumber int, PageCount bigint, avg_fragmentation_in_percent float)

DECLARE db_cursor CURSOR FOR

SELECT [object_id], [index_id], [partition_number] FROM #tempTest

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @object_id, @index_id, @partition_number

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO #tempTest2 (NK, ServerName, TimeStamp, DatabaseName, SchemaName, ObjectName, IndexName, ObjectType, IndexType, PartitionNumber, [PageCount], avg_fragmentation_in_percent)

       SELECT CONCAT(objects.[object_id], indexes.[index_id]) AS NK,
       @@SERVERNAME AS ServerName,
       GETDATE() AS TimeStamp,
       DB_NAME(@DatabaseID) AS DatabaseName,
       schemas.[name] AS SchemaName,
       objects.[name] AS ObjectName,
       indexes.[name] AS IndexName,
       objects.type_desc AS ObjectType,
       indexes.type_desc AS IndexType,
       dm_db_index_physical_stats.partition_number AS PartitionNumber,
       dm_db_index_physical_stats.page_count AS [PageCount],
       dm_db_index_physical_stats.avg_fragmentation_in_percent AS AvgFragmentationInPercent
FROM sys.dm_db_index_physical_stats (DB_ID(), @object_id, @index_id, @partition_number, 'LIMITED') dm_db_index_physical_stats
INNER JOIN sys.indexes indexes ON dm_db_index_physical_stats.[object_id] = indexes.[object_id] AND dm_db_index_physical_stats.index_id = indexes.index_id
INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id]
INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]
WHERE objects.[type] IN('U','V')
AND objects.is_ms_shipped = 0
--AND indexes.[type] IN(1,2,3,4)
--AND indexes.is_disabled = 0
--AND indexes.is_hypothetical = 0
AND dm_db_index_physical_stats.alloc_unit_type_desc = 'IN_ROW_DATA'
AND dm_db_index_physical_stats.index_level = 0
AND dm_db_index_physical_stats.page_count >= 1000
--AND schemas.name <> 'Audit'
    FETCH NEXT FROM db_cursor INTO @object_id, @index_id, @partition_number
END

CLOSE db_cursor
DEALLOCATE db_cursor

SELECT * FROM #tempTest2

I think I am going to try moving the WHERE objects.[type] IN('U','V')
AND objects.is_ms_shipped = 0 to the temp table above and comment it out from the below section.

I have already read:

SQL Server Index Maintenance – You’re Doing It Wrong by Sean Gallardy
Stop Worrying About SQL Server Fragmentation by Brent Ozar
How much will it cost or save to rebuild that index? by Jeff Moden

I would suggest reducing the number of joins in your loop.  You are already hitting sys.indexes and sys.schemas in the your first query to build your temp table.  It would be advisable to just grab whatever additional fields you need and throw them into your temp table as well.

You want to make your query that you are looping through in your cursor as efficient as humanly possible.  You might even want to try and get the data you needed from sys.objects in there too.

Speaking of cursor you should consider using a fast_forward cursor as well.  It has a smaller footprint and is a bit more efficient.


Proof that if a relation R is in 3NF and if every key in R is simple, then R is in BCNF? "A key is simple if it consists of only one attribute". Prove that if a relation R is in 3NF and if every key in R is simple, then R is in BCNF. Your proof should be general, e.g., it should not assume that R has a fixed number of, say two or three, attributes.

Solution:

Consider an FD X -> Y that holds in R. Since R is in 3NF, either

X is a superkey or
Y is a member of a key.

In the second case, since every key in R is simple, Y is itself a key, which implies that X is a superkey.
Therefore, X -> Y does not violate BCNF in either case, which implies that R is in BCNF.

I understand everything except the final part; Y being a key implies X being a superkey. Can somebody elaborate on that?

Interesting question (I happen to be reading the Ullman book right now). I think the answer is something like this:

Assume that X is not a superkey. Then, each attribute in Y is a member of a key (is prime). Since each key consists of one attribute, y -> {every other attribute}, where y is a member of Y. Then by transitivity, X -> {every other attribute}, which implies that X is a superkey. We contradicted our original assumption, that X is not a superkey, hence X must be a superkey.

The most important part of the proof is the transitivity part.


how to resolve ELFCLASS32 error in mysql for UDF lib_mysqludf_sys.so I'm trying to install lib_mysqludf_sys.so in mysql to create sys_exec function which will run an external java programm ..

first of all i issued this command :

CREATE FUNCTION sys_exec RETURNS INT SONAME 'lib_mysqludf_sys.so';

and i got this error :

ERROR 1126 (HY000): Can't open shared library 'lib_mysqludf_sys.so' (errno: 0 /usr/lib/mysql/plugin/lib_mysqludf_sys.so: wrong ELF class: ELFCLASS32)

this error is due to the 64 bit os...

i'm using 64 bit Ubuntu and this library file is of 32 bit.

I'm not getting the library of 64 bit..

I have gone trough this :

gcc -Wall -m64 -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o /usr/lib/lib_mysqludf_sys.so -fPIC

this is also giving error that file not present ..how should i resolve this

You didn't say, in your question what the "file not present" message was, but I'm guessing it's this:

ERROR 1126 (HY000) at line 29: Can't open shared library 'lib_mysqludf_sys.so'
(errno: 0 /usr/local/mysql/lib/plugin/lib_mysqludf_sys.so: cannot open shared
object file: No such file or directory)
ERROR: unable to install the UDF

That makes sense, because you're telling gcc to write the file to /usr/lib/lib_mysqludf_sys.so... that's the -o option in your command line.  Give gcc the path MySQL is expecting and the rest of the installation should work.

gcc -m64 -fPIC -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c \
-o /usr/local/mysql/lib/plugin/lib_mysqludf_sys.so \
-L/usr/lib/x86_64-linux-gnu/libstdc++.so.6

Also, whatever you're planning to do with this... don't say I didn't recommend against it.  You're introducing both a potential security vulnerability and potentional performance and stability liability if these tools are deployed.  This is not because there's anything wrong with the utility, but because of the amount of somewhat unorthodox functionality it opens up.

It's pretty cool, I admit.  The lib_mysqludf_sys group of user-defined functions allow some interesting but easily-misappropriated capabilities, letting you spawn system commands and get either their generated output with sys_eval() or their return value with sys_exec() but really, I tend to suspect there's a reason why MySQL doesn't have these capabilities built in.

mysql> select sys_eval('df -k | grep xvda | tr -d "\n"') as cool_function_but_bad_idea;
+-----------------------------------------------------+
| cool_function_but_bad_idea                          |
+-----------------------------------------------------+
| /dev/xvda1       8256952   1216636   6620888  16% / |
+-----------------------------------------------------+
1 row in set (0.81 sec)

mysql> select sys_exec('/bin/false'), sys_exec('/bin/true');
+------------------------+-----------------------+
| sys_exec('/bin/false') | sys_exec('/bin/true') |
+------------------------+-----------------------+
|                    256 |                     0 |
+------------------------+-----------------------+
1 row in set (1.59 sec)

Update to address this error message:

gcc -Wall -m64 -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o
/usr/lib/lib_mysqludf_sys.so -fPIC gcc: error: lib_mysqludf_sys.c: No such
file or directory gcc: fatal error: no input files compilation terminated

The statement in question is directing "gcc" to compile a 64-bit version from the source code, which is found in lib_mysqludf_sys.c.  This is one of the files you downloaded, so, to compile it, you need to be inside the directory where you downloaded the lib_mysqludf_sys files.  This error suggests that you aren't.  The download package isn't limited to 32 bit, it's just that it only builds a 32 bit version unless you use the gcc -m64 -fPIC ... statement.


Filtering a result set on a very large table (using INCLUDE index in SQL Server) Consider a very large table having hundreds of millions of rows defined as

-- Here ObjectIdName and ObjectTypeName together are unique.
-- Due to their size they aren't indexed, but rather they are
-- hashed to ObjectIdName -> ObjectId and ObjectTypeName -> ObjectType
-- which are used in the index instead.
CREATE TABLE VeryLarge
(
    ObjectId INT NOT NULL,
    ObjectIdName NVARCHAR(512) NOT NULL,
    ObjectType INT NOT NULL,
    ObjectTypeName NVARCHAR(512) NOT NULL,
    PayLoad VARBINARY(MAX) NULL,
    IsDeleted BIT NOT NULL
);

-- This was originally in the question. By mistake includes the Payload column.
CREATE NONCLUSTERED INDEX IX_VeryLarge1 ON VeryLarge(ObjectId, ObjectType) INCLUDE(IsDeleted, PayLoad, ObjectIdName, ObjectTypeName);

-- This was the index I intended to have, without the Payload column.
CREATE NONCLUSTERED INDEX IX_VeryLarge2 ON VeryLarge(ObjectId, ObjectType) INCLUDE(IsDeleted, ObjectIdName, ObjectTypeName);

If one assumes the ObjectId and ObjectType parameters locate a single row in 99.99% of the cases, it looks to me there needs to be an interplay between the query and the indexing to be so effective the query will always return predictably in sensible time (considering hardware and the actual amount of rows). However, I'm not sure how should the query be constructed. Basically what I would like to do is query first with ObjectId and ObjectType and filter this small result set with ObjectIdName and ObjectTypeName (if there are more than one row).

Considering this, with the defined indexing, is the following query sensible on SQL Server?

SELECT
    Payload
FROM
    VeryLarge
WHERE
    ObjectId = @objectId
    AND ObjectType = @objectType
    AND IsDeleted = 0
    AND ObjectIdName = @objectIdName
    AND ObjectTypeName = @objectTypeName;

-- Or alternatively, considering the question:
SELECT
    PayLoad
FROM
(
    SELECT
        PayLoad,
        ObjectIdName,
        ObjectTypeName
    FROM
        VeryLarge
    WHERE
        ObjectId = @objectId
        AND ObjectType = @objectType
        AND IsDeleted = 0
) AS x
WHERE x.ObjectIdName = @objectIdName AND x.ObjectTypeName = @objectTypeName;

I don't know how to construct such a query that would prune the results of the first query that will is guaranteed to hit an index and return only one or a few rows. It would look that using INCLUDE has the same effect, forcing and index seek but I don't know if there will be costs associated with this. Also, I know MySQL doesn't have this concept of included index, but it works somehow similarily and I assume filtering the result set of at most a few rows would work on any database that supports indexing regardless of other structures.

This is related to my other question at Hashed and heap indexed object storage table insert and query performance, which has the table and query defined.

<edit: Additional question: Having those repeating ObjectType columns in the same table looks like wasteful. Might be subject for another post, but I believe they ought to be separated to a different table. Then the question is that should the INNER JOIN be avoid with a query that does the join only if there are more rows than one after the first filtering. And how to do that.

From the details you've provided it seems reasonable that the IX_VeryLarge non-clustered index would support both queries you've shown in your question.  You have the Payload column typed as VARBINARY(MAX) - if you expect large objects to be stored in that column, I'd likely not INCLUDE it in the index since that will cause the index to be much much larger than it would otherwise be.  If the vast majority of your queries result in one or two rows, a simple lookup into the clustered index/heap (the table) is preferable.  The pattern you've shown whereby you have a clustered index/heap and supporting non-clustered indexes with included columns (a so-called covering index) is extremely common, and perhaps the single best way to ensure good performance, along with good statistics.

Having said that, I wouldn't expect the 2nd query to outperform the 1st query since the results returned would be exactly the same.  SQL is not a procedural language, it is a declarative language.  With a procedural language, you tell the computer what to do, whereas with a declarative language you describe the desired results.  Since SQL is declarative, and since both queries return the same result set, it is reasonable to assume SQL Server will likely come up with the same plan for both queries.  Since the 2nd query is more difficult for a human to understand, I'd favor the 1st variant.  Try not to outsmart the SQL Server query optimizer, it cannot be done is very hard to do.

At the end of the day, the only way for you to truly understand the best option in your situation, on your data, with your table definition and output requirements, is to test them.


Can't connect to a PG database from MicroOLAP, but can connect from SSH? I'm able to connect to my PGSQL database from SSHv2.

But when I try to do the same thing on MicroOLAP Database Designer, I get this message:

(Note: From my college PC, I can connect. But I can't connect from home.)

My configuration:

Here's me on arka.foi.hr via SSHv2 (accessible from home):

From the error dialog it is clear that something around your connection is wrong.

First be sure that everything you included here matches: you have an error about localhost and port 3082 and you present a connection dialog with a foreign host and port 5432.

Then you have to teach PostgreSQL that it should accept connections from your home.  This can be done via the pg_hba.conf file.  You will need a line added like this (the header is already there) - if you don't have access to it, ask the person in charge of it to do this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    dnizetic        dnizetic        161.53.120.19/32        md5


PostgreSQL: How to get a sum of values and the total rows that went into each value? I have a table, MyData in the following format:

|id|theme_id|priority_id|value|

I am trying to get the following result for all the rows:

theme_id | priority_id | total | sum
------------------------------------
    1    |      1      |   3   |  5
    1    |      2      |   5   |  12
    1    |      3      |   1   |  3
    2    |      1      |   2   |  6

The idea is group all the rows by theme_id and priority_id and then sum up the values for that grouping, as well as, to get the total rows that went into that sum.

When I try the following, I get a total column that equals to the sum column:

WITH breakdown AS (
  SELECT id, theme_id, priority_id, value
  FROM MyData
)
SELECT theme_id, priority_id, sum(value) AS values, count(id) as total
FROM breakdown
GROUP BY theme_id, priority_id

I have also tried:

SELECT theme_id, priority_id, sum(value) AS values, count(id) as total
FROM MyData
GROUP BY theme_id, priority_id

Both of the above give me the same output:

theme_id | priority_id | total | sum
------------------------------------
    1    |      1      |   5   |  5
    1    |      2      |   12  |  12
    1    |      3      |   3   |  3
    2    |      1      |   6   |  6

I have created the following sample DDL structure in order to work the necessary queries:

CREATE TABLE my_data
(
    my_data_id  SERIAL,
    theme_id    INT NOT NULL,
    priority_id INT NOT NULL,
    my_value    INT NOT NULL,
    CONSTRAINT pk_my_data PRIMARY KEY (my_data_id)
);

CREATE TABLE theme
(
    theme_id  SERIAL,
    name      CHAR(6) NOT NULL,
    CONSTRAINT pk_theme PRIMARY KEY (theme_id)
);

CREATE TABLE priority
(
    priority_id  SERIAL,
    name         CHAR(6) NOT NULL,
    CONSTRAINT pk_priority PRIMARY KEY (priority_id)
);

Then, inserted the following test data into the respective tables:

INSERT INTO theme (name)
VALUES ('first');
INSERT INTO theme (name)
VALUES ('second');
INSERT INTO theme (name)
VALUES ('third');

INSERT INTO priority (name)
VALUES ('high');
INSERT INTO priority (name)
VALUES ('medium');
INSERT INTO priority (name)
VALUES ('low');

INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (1, 1, 6);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (1, 2, 5);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (1, 3, 8);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (1, 1, 3);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (1, 2, 4);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (1, 3, 11);

INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (2, 1, 7);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (2, 2, 3);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (2, 3, 9);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (2, 1, 1);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (2, 2, 13);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (2, 3, 10);

INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (3, 1, 1);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (3, 2, 8);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (3, 3, 2);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (3, 1, 6);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (3, 2, 5);
INSERT INTO my_data (theme_id, priority_id, my_value)
VALUES (3, 3, 4);

And, finally the concrete SELECT statement with its corresponding aggregate functions that provide your desired result set:

SELECT theme_id,
       priority_id,
       SUM(my_value) AS values_sum,
       COUNT(*)      AS total_rows
  FROM my_data
GROUP BY theme_id, priority_id
ORDER BY theme_id ASC, priority_id ASC;

In this way, we can see that you almost had it with your second query but, as @a_horse_with_no_name has pointed out, you needed to replace count(id) with count(*), so that you could get your correct data. I have also included the ORDER BY clause for sorting the columns.

By means of this SQL Fiddle sample, you can confirm that the results yielded by SUM and COUNT are correct. As you can see I've added theme and priority, which are relevant tables for this query, along with the required test data.


What does 'sp_configure allow updates' do? Today I got an error ( DTSER_FAILURE  ) but I could fix it by editing the maintenance plan ( this error is due to the maintenance plan searching for a database that was deleted, and I was using "selected databases" in the setup ).

But over the internet, I read some posts saying that I should SP_CONFIGURE 'ALLOW UPDATES',0.

What effects has this option?

I read on the Microsoft page that this option has no effect and it is deprecated.

This is an old option from the SQL Server 2000 days that would allow you to directly make updates to the system catalog tables (sysobjects, syscomments, etc.). Since 2005, this is no longer possible (at least not without jumping through a ton of additional hoops beyond just an sp_configure option - it's not something you ever want to do on a production system), and all of the system catalog is now exposed through read-only views like sys.objects.

I'm not sure how that would be related to the maintenance plan failing, though. That error you saw is pretty typical if you delete or otherwise remove a database that's selected as part of the maintenance plan. Usually, editing the offending step in the maintenance plan and resaving is enough to fix it.


Why does SQL Server Profiler crash when its run against a DB machine from a client machine? I have:

SQL Server 2014 (default instance )is on a Win Server 2012 machine.
SQL Server Management Studio (v17.7) is on a Win 7 machine.
Both machines are on the same domain.
I'm logged into the Win 7 machine as a domain admin.
My domain admin account has SQL Server Security sysadmin
access.

On the Win 7 machine, I bring up SSMS and I connect OK to the DB that is on the Win Server 2012 machine. However, on SSMS, when I do Tools -> SQL Server Profiler, I get a dialog "Profile Application has stopped working". The event log shows the following which seems weird because I would expect Profiler.exe to be run on the remote Win Server 2012 DB machine

Why does SQL Server Profiler crash when its run against a DB machine from a client machine?

Faulting application name: Profiler.exe, version: 2017.140.17254.0, time stamp: 0x5ae98b94
Faulting module name: unknown, version: 0.0.0.0, time stamp: 0x00000000
Exception code: 0xc0000005
Fault offset: 0x75bb48cb
Faulting process id: 0x4e0
Faulting application start time: 0x01d42da026f3a3d9
Faulting application path: C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\Profiler.exe
Faulting module path: unknown
Report Id: 652f0139-9993-11e8-9f5b-327e82b70454

The profiler.exe will run on the local Windows 7 machine, where you have the Management Studio installed. It will connect to remote instance over network to collect the data, but the profiler.exe itself will run locally on your computer. If you want the collection to happen at the same server where the SQL Server is installed, then you need to implement server-side tracing for that.

A good example of how to set it up, can be found here.

Also by starting Profiler the way you described should first prompt for server details (server name, credentials) even if you have server connection open in the Object Explorer. Now I believe it crashed before ever getting there, so the problem is probably not related to connecting to remote server at all. I would recommend re-installing Management Studio to see if it fixes the issue.

Latest stand-alone installers for SSMS can be found from here.


Creating a database under a different owner I'm trying to set up a role in Postgres which can
a) Create a new Role
b) Create a new Database
c) Make that new Role the owner of the database
d) Have no other rights (as far as is possible!)

I have tried this:

sc_1=# CREATE ROLE tenant_admin CREATEDB CREATEROLE;
CREATE ROLE
sc_1=# CREATE ROLE user1 IN ROLE tenant_admin LOGIN NOINHERIT ENCRYPTED PASSWORD 'xyz';
CREATE ROLE
sc_1=#

Followed by (in another session)

tahaan@Komputer:~/projects/acme-project$ psql -U user1 -h localhost -d postgres
Password for user user1:
psql (9.3.6)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=> SET ROLE tenant_admin;
SET
postgres=> CREATE DATABASE "Tenant1" TEMPLATE "tenant_template";
CREATE DATABASE
postgres=> CREATE ROLE "owner1";
CREATE ROLE
postgres=> ALTER DATABASE "Tenant1" OWNER TO "owner1";
ERROR:  must be member of role "owner1"
postgres=>

Background:  The requirement is to have an automated function that can setup separate databases in a multi-tenant system.  My hope is that this function can be performed by a role that does not have too much rights.

I found a solution involving a few extra steps.  The "tenant_admin" role is still created the same way, but it is now used as follow:

postgres=> SET ROLE tenant_admin;
SET
postgres=> CREATE ROLE "owner3";
CREATE ROLE
postgres=> GRANT "owner3" TO "tenant_admin";
GRANT ROLE
postgres=> CREATE DATABASE "Tenant3" OWNER "owner3";
CREATE DATABASE
postgres=> REVOKE "owner3" from "tenant_admin";
REVOKE ROLE


reset mysql root password on ubuntu In short: my goal is to reset mysql root password on ubuntu.

Background: I set up a ubuntu desktop and installed LAMP stack last week. I then went to do something else, and just got back to carry on today. Either I did not set mysql password, or I have forgotten what I typed in.

So I tried to reset it.

I stopped mysql by

sudo stop mysql

I tried to start mysql using command line options, but it seems start only takes service name(??)

So I ran:

sudo /etc/init.d/mysql start --skip-network  --skip-grant-tables

Went into mysql, fine. Then

mysql> UPDATE mysql.user SET Password=PASSWORD('newpasswd') WHERE User='root';
ERROR 1142 (42000): UPDATE command denied to user ''@'localhost' for table 'user'
mysql> show grants for ''@'localhost';
+--------------------------------------+
| Grants for @localhost                |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+
1 row in set (0.00 sec)

If ''@'localhost' has USAGE on all-schemal.all-tables, I'd think I can just update the password like this... Where did I miss?

I used the instruction in mysql official website, the last part. I also tried the unix one but with no luck. And I was unsure when it says 'unix', does it mean all *nix system or just unix???

Anyway, any thoughts are welcome! many thx!!!

Here is a more secure way to reset password without skip-grant-tables
Suppose you want root to have myn3wp@ssw0rd as the password
Step 01 : Create a script to execute when mysqld first start up
Create a file called /var/lib/mysql/init-file.sql with these two lines
GRANT ALL PRIVILEGES ON *.* to root@localhost
IDENTIFIED BY 'myn3wp@ssw0rd' WITH GRANT OPTION;

Step 02 : Run these three(3) lines in the OS
chown mysql:mysql /var/lib/mysql/init-file.sql
service mysql restart --init-file=/var/lib/mysql/init-file.sql
rm -f /var/lib/mysql/init-file.sql

Step 03 : THERE IS NO STEP 03. YOU ARE DONE !!!
Give it a Try !!!
CAVEAT

I used to recommend two restarts of mysql that required editing my.cnf
I learned a better approach from @ShlomiNoach
This answer improves on it more by not editing my.cnf at all

UPDATE 2013-06-17 07:05 EDT
OK Since mysqld for Ubuntu does not like init-file on the command, you must edit the my.cnf. Please do these next two steps
STEP 01) Add these lines to my.cnf under the [mysqld] group header
[mysqld]
init-file=/var/lib/mysql/init-file.sql

STEP 02) Allow mysql to read files from /etc/mysql
sudo vim /etc/apparmor.d/usr.sbin.mysqld

Add the line:
/etc/mysql/*.sql r,

STEP 03) Restart MySQL
This should work for you. Give it a Try !!!


What are sensible/rational character limits in a database e.g. for a persons name This is a general question about database admin.

Is there some kind of standardization for character limits on fields in a DB? It makes sense to specify character limits on the database end for names, phone number etc where you know the sort of data being entered and you want the data entered to be rational so you restrict the character limit. But how do you know what a sensible limit is in this crazy world there could a lot of exceptions.

In my case it is a system for a local company here in the UK with only 10-15 employees. So I could go back and change it if needed but I'd rather enter a rational limit to start with.

Sorry if this question has already been asked. If it has I couldn't find it through Google or on similar Questions.

I think some of it goes back to requirements. How much of a name are you storing? Do you ever want to keep the "names" separate (like first, middle, last)? Do you want to handle multiple middle names?

I'd say 50 characters should be fairly sufficient for each name if you wished to separate them out. (That will be way more than enough for most names, but will also cover names that are larger.) I'd also probably go with VARCHAR than CHAR for names since they vary so much.

If you were going to put the full name in one field, I'd go with 150 - 200 characters then.

Again....it all goes back to requirements...what do you need?


How do I install PostgreSQL in Debian/Ubuntu? I'm following these instructions, however I can only get to step 17.2.

Despite installing postgresql successfully via the

sudo apt-get install postgresql

command, upon running

initdb -D /usr/local/pgsql/data

Ubuntu tells me that it 'initdb' isn't installed. The instructions tell me this command is installed by

sudo apt-get install postgresql

so what's going on?

If you haven't set up the environment variables, then you need to specify the full path to your executables.

What version of Postgresql are you using? You show a link to 8.3 which is many years old. These docs are current: http://www.postgresql.org/docs/current/static/creating-cluster.html

Did you install Postgresql with an up-to-date package, a la http://www.postgresql.org/download/linux/ubuntu/ ?

When installing 9.2 on Ubuntu, I added the following lines to the ~postgres/.bash_profile

export PATH=/usr/lib/postgresql/9.2/bin:$PATH:/usr/sbin
export PGDATA=/var/lib/postgresql/9.2/main
export PGPORT=5432

Where "/usr/lib/postgresql/9.2/bin" is the path to your 9.2 executables. That made the installation a bit smoother for me.


What do the entity-relationship diagram (ERD) symbols used in DBeaver mean? I don't understand the ERDs used by DBeaver. The diamonds and dots at the ends of the line arent the crow's foot I'm used to. I've googled the subject and checked the docs but can't find a straight-forward answer.

Is there a reference for the style used? What do these symbols mean?

Please see the image below:

I found something while putting together this question. It seems the current standard used is IDEF1x, according to this github issue.

I found a PDF cheat sheet with the following content showing all the relationships:


Why Azure site recovery may not meet RPO? While Azure Site Recovery may meet RTO, it may not meet RPO since it
is not accounting for where data is inside SQL Server.

I am looking for an example for what it means by "is not accounting for where data is inside SQL Server".

Looking at Microsoft’s “About Azure Recovery”, we can see this:

RTO and RPO targetsKeep recovery time objectives (RTO) and recovery point objectives (RPO) within organizational limits. Site Recovery provides continuous replication for Azure VMs and VMware VMs, and replication frequency as low as 30 seconds for Hyper-V. You can reduce RTO further by integrating with Azure Traffic Manager.

Data “inside SQL Server” would be data that has not yet been replicated elsewhere, be it Azure, a replication distributor, or a local mirror. Losing 30 seconds of data for most organizations is a tolerable amount of failure, but any company that requires real-time loading and querying will be chomping at the bit about that 30-second gap between the time the primary instance went down and the replicated secondary in Azure picked up


Can an SQL Job Deletes itself after its end date? I have created a job which will sets up its start date and end date automatically with a dynamic value.

For example:

Job will run for eight days and at eighth day it needs to automatically deletes itself irrespective of its status.

Is there a way to code it while creating or scheduling ?

Can an SQL Job Deletes itself after its end date?

  I have created a job which will sets up its start date and end date
  automatically with a dynamic value.

  Is there a way to code it while creating or scheduling ?

You can do this by using sp_delete_job and dynamically creating applicable syntax and controlled logic to execute this for the dynamically created job name when date wise it needs to be deleted.

Since you are building the dynamic SQL Agent jobs and creating them with a start date and end date, you'd just put additional logic in to execute sp_delete_job if the date is equal to day eight at the end of the job WHERE @job_name = '<Dynamic Job Name>'.

You could put the additional logic in at the beginning of the job to execute sp_delete_job if the date is equal to day nine or the day after the eighth day WHERE @job_name = '<Dynamic Job Name>'.

  Syntax

sp_delete_job { [ @job_id = ] job_id | [ @job_name = ] 'job_name' } ,
     [ , [ @originating_server = ] 'server' ]
     [ , [ @delete_history = ] delete_history ]
     [ , [ @delete_unused_schedule = ] delete_unused_schedule ]

  source

Since you control the logic that creates the dynamic SQL Agent jobs you should be able to test with additional logic that will delete the dynamic SQL Agent job and execute sp_delete_job if the date is equal to a particular date (e.g. day eight or day nine).

It may be best to use logic that will execute sp_delete_job if the date is greater than or equal to day eight or nine in case it doesn't run on the exact date due to system issues, etc. with logic such as: WHERE Date >= CONVERT(datetime, 'YYYY-MM-DD' )

  Example

USE msdb ;
GO

EXEC sp_delete_job
    @job_name = N'NightlyBackups' ;
GO

  source


My Procedure isn't working right. It keeps giving me an error create or replace PROCEDURE CHECK_BLACKLIST
(
  P_EMPLOYEE_USERNAME IN VARCHAR2
, P_ROWCOUNT OUT NUMBER
) AS
BEGIN
  SELECT COUNT(*) INTO P_ROWCOUNT FROM boe_safegaurd WHERE UPPER(employee_username) = UPPER(P_EMPLOYEE_USERNAME);
END CHECK_BLACKLIST;

Errors

I entered joe@example.com when I ran the procedure.

ORA-06550: line 5, column 26:
PLS-00352: Unable to access another database 'example.com'
ORA-06550: line 5, column 26:
PLS-00201: identifier 'joe@eample.com' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
Process exited.

when I enter "joe@example.com", I get a similar error

ORA-06550: line 5, column 27:
PLS-00201: identifier 'joe@eample.com' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored

You're right. While the procedure executes correctly from SQL or PL/SQL code, somehow SQLDeveloper raises an exception when you invoke the procedure (via the green arrow) and pass anything as input that contains a "@" sign. It's either a peculiar behavior of SQL Developer or a bug.

But executing it directly works fine, also from SQL Developer:


What do I make of unlinked tables in the same database? Just a basic question here but it's been bothering me for a while. Do all tables have to be related to another? I'm trying to design a database so far I have 15 tables, 13 of them have relationships with each other creating a regular snowflake pattern, but I have two other tables that only relate to each other, meaning they don't have any relationship to the any of the other 13 tables. Does this mean I should review  my design or is this considered OK?

Thank you for your help in advance.

No, there is no requirement for tables to be related to one-another.  The fact that you do have two tables separate from the other thirteen would be a code smell and you should be sure that you have not forced two different business objectives into a single database.  If the two really do support genuine use cases for the others then there's no problem.

A typical example are "staging" tables.  Here data from an external source is loaded into a stand-alone table.  Then that data is validated.  Only then is it copied across to inter-related tables.  Another is to hold system configuration or user options.  These have no foreign keys to the "business" data but support the operation of the system which uses that business data.

Lastly I would point out that the logical and physical designs can be different.  The data model (logical) may document foreign keys but the data base (physical) does not declare foreign key constraints for one reason or another.  This would be a very specific choice for particular circumstances and relatively rare, however.


Building a Trending query Is there a way to build a query that returns a record set consisting of a date and the number of records == that date? For example, I have a DB that is tracking inspections that take place on every day. I want to build a data set to be used for a trending graph to see how many inspections took place on each day streching back 365 days.

Record set desired would look like this:

date | InspectionCount
2013-12-30  265
2013-12-29  123
2013-12-28  425
.
.
.
2013-01-01  15

Is there a way to do that without write code that will simple loop through 365 times and sort of manually generate daily counts like this:

select count(reporting_id) as InspectionCount
from inspection
where close_conf_date >= '2013-12-30'
and close_conf_date < '2013-12-31'

while gradually winding the clock back 365 days? Please let me know if you need more info.

Update

I have just tried this:

WITH RECURSIVE t(n) AS (
VALUES (cast('2013-12-30' as timestamp))
UNION ALL
SELECT n-interval '1 day' FROM t WHERE n >=('2013-12-30'-interval '365 day')
)
SELECT  cast(t.n as date) as date_column, count(reporting_id)
FROM t
INNER JOIN inspection i ON (cast(t.n as date) = cast(i.close_conf_date as date))
GROUP BY cast(t.n as date)

and I am getting this error i do not really understand:
interval field value out of range: "2013-12-30"

You don't need the recursive CTE, just use generate_series():

You also need to use an outer join in order for the days without inspection to show up as zero:

with days as (
  select day::date
  from generate_series(date '2013-12-30', date '2013-12-30' + 365, interval '1' day) day
)
select d.day as date_column,
       count(i.reporting_id) as cnt
from days d
  left join inspection i on i.date = d.day
group by d.day

generate_series(date '2013-12-30', date '2013-12-30' + 365, interval '1' day) generates one row for each date between '2013-12-30' and '2013-12-30' + 365. For details on generate_series() see the manual: http://www.postgresql.org/docs/current/static/functions-srf.html

The ::date notation  is just a shorthand for cast(.. as date)


Database Replication/Mirroring for Reporting Purposes I am pretty green to the administration end of databases, but I was hoping you guys could direct me on the right path.

I have Server 1, 2, and 3. They both identical versions of SQL Server, with an application. Within each instance of MS SQL Server, I have roughly 10 databases in each server. My goal is to replicate/mirror the data to a Server 4 so that reporting and alerts can be run off of the 'live' data without effective performance of the application running on Servers 1,2,3.

To be honest, I don't even know where to start:

Is this at all possible?

Is this a common practice?

Is there any type of recommended reading material?

What you are looking for is called Transactional Replication, which is very commonly used and a good solution for avoiding contention on a transactional database. There are three databases in a transactional replication system, which are the publisher, the distributor and the subscriber. The publisher is the source database, the distributor is where all changes to the publisher are sent for distribution to one or more subscribers. The distribution database can be on the same server as the publisher or a different server. When you create a publication, you choose the articles to be included, such as tables, views, procedures, etc, and whenever a change is made to any of the objects and gets recorded in the transaction log, an agent, which is an executable, reads the transaction log, packages the change and sends it to the  distribution database. Then a separate agent, or executable, takes the packages from the distribution database and applies them to the subscriber.

You will be setting up a publication and subscriber on servers 1,2 and 3 and have all subscriptions point to a database on server 4. There are several articles on MSDN that have good descriptions of transactional replication.


How do I swap tables atomically in Oracle? How to do I swap two tables in an atomic fashion in Oracle?

I would like to perform the equivalent of:

ALTER TABLE foo RENAME foo_tmp;
ALTER TABLE foo_new RENAME foo;

but what happens if a query needs table foo in between those two lines when there is no table foo? Some lock would be required.

P.S. For a MySQL version of the question, see this question.

Create a public synonym that points to the new table.

CREATE PUBLIC SYNONYM foo FOR foo_new;
RENAME foo TO foo_tmp;

-- At this point in time, any DMLs for foo will operate on foo_new, via the synonym

RENAME foo_new TO foo;
DROP PUBLIC SYNONYM foo;

Things will get a bit more complicated if you're accessing the tables involved with a user other than the schema owner, but this works if you're only accessing the table with the schema owner.


SELECT COUNT(*) returns no data SQL Server 2012 I have a query, that as far as I know has failed exactly one time. It's a simple select count(*) from one table, no joins. But at least this once, executing that query resulted in no data read from SqlDataReader. Not even null, just nothing. First call to Read returns false. No exception was raised.

Has any one ever heard of that before? Any scenarios you can think of that would cause it?

I'm not even sure what to ask for to look at beyond SQL server logs. It's not something we can duplicate. I am assuming I'll have to chalk it up to a fluke and move on if/until it becomes a chronic problem. Here's a similar query:

SELECT ISNULL(COUNT(*),0)
FROM dbo.TRANSACTIONS T
WHERE (@TransactionId IS NULL OR (T.TransactionId != @TransactionId))
AND T.ParentId = @ParentId
AND (T.TransactionStatus != 4)

Is there a class of error that will thrown an exception out of the query, but not raise it to the application when executing a command or reading from the resulting SqlDataReader?

UPDATE: Here's the code executing this.

using (SqlConnection conn = /*connection created and opened here*/)
{
    SqlCommand cmd = new SqlCommand("usp_GetChildTransactions");
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@transactionId", SqlDbType.BigInt).Value = transactionId > 0 ? transactionId : (object)DBNull.Value;
    cmd.Parameters.Add("@parentId", SqlDbType.Int).Value = parentId;

    using (SqlDataReader rdr = conn.ExecuteReader(cmd))
    {
        rdr.Read();
        itemCount = rdr.IsDBNull(0) ? 0 : rdr.GetInt32(0); //exception thrown here because there is no data
    }
}

Update 2: I was hoping to not do this, but maybe the exception handling is part of the problem. Here is the create procedure for this, and also another stored procedure being called in the exception handler. Sorry for the length:

CREATE PROCEDURE dbo.usp_GetChildTransactions (
    @transactionId              bigint,
    @parentId                   int,
AS
SET NOCOUNT ON

BEGIN TRY

    SELECT ISNULL(COUNT(*),0)
    FROM dbo.TRANSACTIONS T
    WHERE (@TransactionId IS NULL OR (T.TransactionId != @TransactionId))
    AND T.ParentId = @ParentId
    AND (T.TransactionStatus != 4)

END TRY
BEGIN CATCH
    EXEC usp_RethrowException
END CATCH

CREATE PROCEDURE dbo.usp_RethrowException
AS

SET NOCOUNT ON

DECLARE @ErrorMessage    NVARCHAR(4000),
       @ErrorNumber     INT,
       @ErrorSeverity   INT,
       @ErrorState      INT,
       @ErrorLine       INT,
       @ErrorProcedure  NVARCHAR(200)

SELECT @ErrorNumber = ERROR_NUMBER(),
       @ErrorSeverity = ERROR_SEVERITY(),
       @ErrorState = ERROR_STATE(),
       @ErrorLine = ERROR_LINE(),
       @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')

if (@ErrorState = 0) set @ErrorState = 1

SELECT @ErrorMessage = ERROR_MESSAGE()

IF (LEFT(@ErrorMessage, 15) = N'( Error Number=')
BEGIN

    RAISERROR
          (@ErrorMessage,
           @ErrorSeverity,
           @ErrorState)
END
ELSE
BEGIN
    -- First time - Create the message with all error information in a standard string format
    SELECT @ErrorMessage = N'( Error Number=%d, Severity=%d, State=%d, Procedure=%s, Line=%d )' + CHAR(13) + @ErrorMessage
    RAISERROR
          (@ErrorMessage,
           @ErrorSeverity,
           @ErrorState,
           @ErrorNumber,
           @ErrorSeverity,
           @ErrorState,
           @ErrorProcedure,
           @ErrorLine)
END

P.S. I did edit this a little, changing names of the procedures, the name of the table only, and removed comments only.

Is there a class of error that will thrown an exception out of the
  query, but not raise it to the application when executing a command or
  reading from the resulting SqlDataReader?

If the query errs in the scope of T-SQL structured error handling, the empty or partial result set is returned to the client followed by the error message in the TDS protocol stream. The implications are that client code should not assume SqlDataReader always returns true and that all rows and result sets must be consumed before the exception is raised in the client code. Consider the following example:

USE tempdb;
GO
CREATE PROCEDURE dbo.usp_ExampleError
AS
SET NOCOUNT ON;

BEGIN TRY

    SELECT COUNT(*)
    FROM (VALUES('NotAnInteger')) AS Example(BadData)
    WHERE BadData = 0;

END TRY
BEGIN CATCH
    THROW;
END CATCH
GO

using (var connection = new SqlConnection(@"Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI"))
using (var command = new SqlCommand("dbo.usp_ExampleError", connection))
{
    command.CommandType = CommandType.StoredProcedure;
    connection.Open();
    using (var reader = command.ExecuteReader())
    {
        Console.WriteLine(reader.Read()); //returns false
        Console.WriteLine(reader.NextResult()); //raises the conversion error
    }
}

Note that ExecuteNonQuery implicitly reads and ignores all rows and result sets (which will raise an exception if the batch erred) but ExecuteScaler and ExecuteReader do not. Also, DataAdaper.Fill consumes all results with a DataSet target overload but not with the DataTable overload,

This is one reason I suggest one generally avoid handling errors in T-SQL unless it provides value and specify SET XACT_ABORT ON to avoid continuing after errors and leaving transactions open.


Unable to receive WAL files with barman I've been scratching my head to solve the following error, but haven't made much progress:

$ barman switch-wal --force --archive --archive-timeout 180 pg

The WAL file 0000000100000059000000FF has been closed on server 'pg'
Waiting for the WAL file 0000000100000059000000FF from server 'pg' (max: 180 seconds)
ERROR: The WAL file 0000000100000059000000FF has not been received in 180 seconds

I had earlier configured barman on a throwaway VPS and everything worked fine. But when I tried replicating this on our final EC2 instance, I ran into the issue mentioned above. I have ensured the following (even though I don't remember doing this on the VPS):

SSH from barman => pg is working
SSH from pg => barman is working
All firewall rules on 'pg' have been disabled, temporarily

What could be the issue here?

Edit: Adding the output of barman check pg

Server pg:
2017-12-08 05:56:31,841 [14559] barman.server ERROR: Check 'WAL archive' failed for server 'pg'
    WAL archive: FAILED (please make sure WAL shipping is setup)
    PostgreSQL: OK
    is_superuser: OK
    PostgreSQL streaming: OK
    wal_level: OK
2017-12-08 05:56:34,174 [14559] barman.server ERROR: Check 'replication slot' failed for server 'pg'
    replication slot: FAILED (slot 'pgbackup' not active: is 'receive-wal' running?)
    directories: OK
    retention policy settings: OK
2017-12-08 05:56:34,175 [14559] barman.server ERROR: Check 'backup maximum age' failed for server 'pg'
    backup maximum age: FAILED (interval provided: 14 days, latest backup age: No available backups)
    compression settings: OK
    failed backups: OK (there are 0 failed backups)
2017-12-08 05:56:34,175 [14559] barman.server ERROR: Check 'minimum redundancy requirements' failed for server 'pg'
    minimum redundancy requirements: FAILED (have 0 backups, expected at least 3)
    pg_basebackup: OK
    pg_basebackup compatible: OK
    pg_basebackup supports tablespaces mapping: OK
    pg_receivexlog: OK
    pg_receivexlog compatible: OK
2017-12-08 05:56:34,367 [14559] barman.server ERROR: Check 'receive-wal running' failed for server 'pg'
    receive-wal running: FAILED (See the Barman log file for more details)
    archiver errors: OK

Edit: Added output of barman receive-wal pg

$ sudo barman receive-wal pg
2017-12-10 01:48:01,129 [25410] barman.utils WARNING: Failed opening the requested log file. Using standard error instead.
Starting receive-wal for server pg
2017-12-10 01:48:01,138 [25410] barman.server INFO: Starting receive-wal for server pg
2017-12-10 01:48:03,087 [25410] barman.wal_archiver INFO: Activating WAL archiving through streaming protocol
pg: pg_receivexlog: starting log streaming at 5A/A000000 (timeline 1)
2017-12-10 01:48:03,391 [25410] barman.command_wrappers INFO: pg: pg_receivexlog: starting log streaming at 5A/A000000 (timeline 1)

^CSIGINT received. Terminate gracefully.
2017-12-10 01:50:42,512 [25410] barman.wal_archiver INFO: SIGINT received. Terminate gracefully.

Edit: Configuration files --

; --------------------
; Contents of /etc/barman.conf after removing all commented lines.
; --------------------

[barman]
barman_user = ubuntu
path_prefix = /home/ubuntu/pg/bin
configuration_files_directory = /home/ubuntu/barman.conf.d
barman_home = /home/ubuntu/barman
log_file = /var/log/barman/barman.log
log_level = INFO
compression = bzip2
retention_policy = RECOVERY WINDOW OF 1 MONTH
last_backup_maximum_age = 2 WEEKS
minimum_redundancy = 3
retention_policy = RECOVERY WINDOW OF 4 WEEKS

/home/ubuntu/barman.conf.d/pg.conf:

; --------------------
; Contents of /home/ubuntu/barman.conf.d/pg.conf after removing all commented lines.
; --------------------

[pg]
description =  "Postgres DB"
conninfo = host=REDACTED user=REDACTED dbname=REDACTED
backup_method = postgres
archiver = off
streaming_conninfo =  host=REDACTED user=REDACTED dbname=REDACTED
streaming_archiver = on
slot_name = pgbackup

When you run into a problem like this one, be sure to double-check your logs, the barman check command, and make sure you set a barman cron entry to start barman receive-wal. That needs to be running before you can execute barman switch-wal and finish your setup. =)


CDC with AlwaysOn Availability Groups So I just recently got to enable CDC on an always on AG. While many sources claimed enabling CDC on alwaysON AG is as easy as it is on any standalone server, turns out it really isn't. As MS says here:

  You should create the [CDC capture & cleanup] jobs at all of the possible failover targets
  before failover, and mark them as disabled until the availability
  replica at a host becomes the new primary replica. The CDC jobs
  running at the old primary database should be also disabled when the
  local database becomes a secondary database. To disable and enable
  jobs, use the @enabled option of sp_update_job (Transact-SQL). For
  more information about creating CDC jobs, see sys.sp_cdc_add_job
  (Transact-SQL).

Turns out after we enabled CDC on the primary replica, absolutely nothing happened on the secondary. So as per MS's advice, I did a manual failover, and created the jobs there, then did another failover back to the original primary.

And apparently as hinted at in the quote above, as fully compatible as CDC is supposed to be with alwaysOn AG's, it is entirely unable to determine whether it's currently running on the primary or secondary replica.

So, with the background out of the way, let's go to my question: How do you guarantee that the CDC jobs are always enabled on the current primary replica? And always disabled (so as not to produce errors) on the secondaries?

Since obviously a big point in alwaysOn AG's is that you don't need to manually be there the second the primary fails for whatever reason, because a secondary will pick up the slack and keep the system online, I assume it's then expected to just create a job on each of the replicas, constantly polling their status and changing the states of the CDC jobs as it does?

If this is correct, do you have any specific advice on how to go about this, specific scripts or guides etc? Because for what should obviously be a fairly common dilemma around the globe, it seems odd that I'd have to manually create a new TSQL job on each and every replica just to carry out what is essentially basic bare essentials functionality of CDC with alwaysOn AG.

This seems unlikely, which is why I'm asking. Or did I misunderstand something? Thanks!

The more common and accepted way is that once a job lands on a server in an AG you start editing it:

Add a first job step that checks whether this is the primary.
Exit on success if it isn't.
Otherwise continue onto the normal job step.

This has been explored in a few other SE questions (they don't specifically relate to CDC which is why you didn't find them):

How to run SQL Job only on primary replica in AlwaysOn Availability Groups?
SQL Server Agent Jobs and Availability Groups

The second way is to create a watchdog job using similar methods which will toggle the jobs on and off. This is probably more acceptable seeing as you're fiddling with SQL Server internal jobs. But it's up to you.

I wasn't quite happy with the samples above hardcoding AG names and such. I prefer to just have the job step operate within the database in question, and this way it can work out for itself if it even has a group and is part of it (as your jobs should continue running even if you temporarily remove them from an AG).

Set Nocount On

If  Exists (
    -- Database in an AG
    Select  Top 1 0
    From    sys.availability_databases_cluster adc
    Join    sys.availability_groups ag
    On      adc.group_id = ag.group_id
    Join    sys.dm_hadr_availability_group_states dhags
    On      ag.group_id = dhags.group_id
    Where   adc.database_name = Db_Name()
    )
And Not Exists (
    -- Database in an AG which is Primary on this instance
    Select  Top 1 0
    From    sys.availability_databases_cluster adc
    Join    sys.availability_groups ag
    On      adc.group_id = ag.group_id
    Join    sys.dm_hadr_availability_group_states dhags
    On      ag.group_id = dhags.group_id
    Where   adc.database_name =  Db_Name()
    And     Upper(dhags.primary_replica) = Upper(@@Servername)
    )
Begin
Raiserror('This is not the primary replica.', 16, 1) With Nowait
Return
End

-- Check if the database isn't accessible
If  Not Exists (
    Select  Top 1 0
    From    sys.databases
    Where   databases.name = Db_Name()
    And     state_desc = 'ONLINE'
    )
Begin
    Raiserror('The database doesn''t exist or isn''t ONLINE on this node.', 16, 1) With Nowait
    Return
End

This kind of thing is also useful for SSIS jobs and other bits that pop up over time.

Going one step further you probably want to create a PowerShell script which will go through and check your servers from time to time for AGs which have CDC jobs and which don't have a watchdog, so that you will be notified to create one.


equivalent of oracle sql administration query in SQL Server 2008 I'm new to SQL Server administration, and I would like to get the equivalent of this Oracle 9i administration query :

select object_type,count(*) as number
from all_objects
where owner='ownerSID'
group by object_type;

Any help would be greatly appreciated :)

As in Oracle the owner column denotes the schema, the following should be roughly equivalent:

select type_desc, count(*) as number
from sys.all_objects
where object_name(schema_id) = 'ownerSID'
group by type_desc;

Not sure how to get the real "owner" in SQL Server though  (the user who owns the object is not the same thing as the schema).

In SQL Server foreign keys, primary keys and other constraints are also contained in that view, so you probably might want to exclude them with a where type not in ('F', 'D', 'PK')


How to determine optimal value for pg_stat_ramdisk_size on Amazon RDS? Amazon announced they support a RAM disk for the stats_temp_directory in PostgreSQL on RDS.

This sounds like a good idea so I want to enable it. Now I'm wondering how to determine the optimal size for the RAM disk. Is there a way to show the current size of the directory on disk? The file system of PostgreSQL on RDS is not accessible, isn't it?
Also, what happens if the RAM disk is configured too small?

The pg_stat_ramdisk_size controls the location and size of the stats_temp_directory on RAM. It's easy to determine the size on bare metal or out of the RDS byt issuing a du -h over the directory specified on the variable. There is no way yet to access this folder size at RDS, will try update here if any feature is added for this.

The used space on this folder, will depend strictly on the settings you have for track_* variables (See 27.2.1. Statistics Collection Configuration ). It is not necessary a huge amount of space, although is very write intensive as it holds the counters per global and database basis (counters over database per table/index/functions). So this also means that is more related to the amount of databases and tables+indexes (as pgstat_write_statsfiles at postmaster/pgstat.c iterates, writing the PgStat_StatDBEntry) than the amount of data.

You don't want to have it smaller(as a crash will happen), so you may want to start with a value over the 256MB.

Update:

Per configuration, it won't let you set pg_stat_ramdisk_size higher than 1GB.

Update 2:

  PGSTAT_FILE_FORMAT_ID 1 byte

  PgStat_StatTabEntry  = 164 bytes

  PgStat_StatFuncEntry = 28 bytes

  closingChar = 'E'

  describers is a char (T or F in this case)

First of all, as it'll explained later, not all the tables, indexes and functions are written on the db statsfile. Basically, a basic formula will be _SizeOfDBStatFile = PGSTAT_FILE_FORMAT_ID + describers + (tableCount * PgStat_StatTabEntry) + (funcCount * PgStat_StatFuncEntry) + closingChar_.

A select count(*) * 164 "size in bytes" from pg_class where relkind IN ('r','i','S') will give you the estimate for the tables if all of them were flushed on the file. Also, you need to do the same within pg_proc, but instead the factor will be 28 bytes.

This file is one per database.

  PgStat_StatDBEntry = 180 bytes

  PgStat_GlobalStats = 92 bytes

  PgStat_ArchiverStats = 114 bytes

  describer is a char (D)

The global statfile is smaller, and contains only the global stats and the counters across databases. Should be something close to _PGSTAT_FILE_FORMAT_ID +describer + PgStat_GlobalStats + PgStat_ArchiverStats + (PgStat_StatDBEntry + describer) * numDatabases_.

So, as you can see, the limitation imposed by AWS in regarding is way above the amount of data held on this directory in most of the databases that can run inside RDS expectations.

Reference


SQL Server 2008 R2 - Access Failure in a query that uses Linked server We have 2 SQL Server 2008 R2 machines.

A user is running a query on Server A that joins a table in Server B. In the query he is using linked server to point Server B.

User has access to Server B Database in question. He is a db_owner there. but when he runs the query it fails.

  Msg 18456, Level 14, State 1, Line 1
  Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

The linked server's security is set to "Be made using the login's current security context".
Since the user has db_owner access to Server B database, I would expect this query work from Server A. It doesn't.

How can give this user access to run this query on Server A.

Thanks for your help.

A number of comments asked if Kerberos was configured correctly, but I do not see a direct response to that issue.  Based on the comments that you make I suspect Kerberos is not set up correctly.

Greatly simplified description:

If UserAccount is directly logged onto Server A then a linked server to Server B is one hop away and the UserAccount's authentication will be recognized on Server B.  This works with or without Kerberos delegation.
Is UserAccount is directly logged onto his desktop and accesses Server A then Server A is one hop away from the desktop and the UserAccount's authentication will be recognized on Server A. However, if from the desktop he runs a query against Server A that uses a linked server to access Server B that is two hops away from his desktop. Without Kerberos (or some similar service) the UserAccount's delegation cannot make the second hop and, despite the rights granted on Server B, will not authenticate on Server B.

This is all about how NTLM and Kerberos work. There is a blog from a few years ago that offers some details on Linked Servers and NTLM or Kerberos authentication:

http://blogs.msdn.com/b/sql_protocols/archive/2006/08/10/694657.aspx

One edited quote is: "In a single-hop setting, windows NTLM authentication, which is available if all machines are windows, is sufficient for delegation; while in a double-hop setting, Kerberos authentication is necessary for the user’s credential to cross machine boundaries from the client to the linked server."

Now, suppose that you do indeed have Kerberos configured properly and still are mystified by the behavior.  In that case you might need to check into whether there is a duplicate SPN.  If so that will need to be corrected.

The following blog discusses duplicate SPNs and other Kerberos issues:
http://blogs.technet.com/b/askds/archive/2013/07/01/interesting-findings-on-setspn-x-f.aspx

Note: Some people just fall back to using Named Pipes instead of TCP/IP, but this is not a recommended solution any more.

Bottom line: Resolve the authentication delegation problem and everything should work.


Trouble removing filestream file / filegroup My database was originally using filestreams in one table.  For reasons that I won't go into here, we decided to stop using filestreams.  I therefore wrote a migration to copy the filestreamed table into a new table that doesn't use filestream, I dropped the old table and renamed the new table.  All good so far.

Now I want to drop the filestream file and filegroup.  And SQL server won't let me:

  The file 'MyFilestream' cannot be removed because it is not empty.

I ran the following query:

select * from sys.tables t
join sys.data_spaces ds on t.filestream_data_space_id = ds.data_space_id
-- and ds.name = 'MyFSGroup'

Even commenting out the ds.name filter, I get zero records back.

Like this old, unanswered question, I ran this query:

  exec sp_helpfilegroup 'MyFSGroup'

and got back:

groupname    groupid    filecount
---------    -------    ---------
MyFSGroup    2          0

I've Googled all over for solutions and workarounds.  So far as I can tell, there is no table anywhere in the database that is still using this file - but I still cannot drop it.  I have tried restarting the SQL service, too, but it's not to be fooled.

How do I find out what is still using the filestream file, and how do I drop it?

EDIT: ooh, here's a thought: the database has "Full" recovery model.  Could that have something to do with it?

Indeed, that was the answer.  I set the db recovery model to "Simple", waited a minute for the filestream data to clear up, and then I could remove the filestream file and filegroup.


PostgreSQL roles versus users, grant permissions I'm currently learning the differences between PostgreSQL and MySQL as I've got a new project and I also intend to migrate my existing software from MySQL to PostgreSQL. I've actually started creating an HTML table with a comparison of commands (for users/databases/command prompt, etc) between the two. After reading an answer here I've notice that role seems to be used as a group. With MySQL I have two users, basically public (DELETE, INSERT, SELECT and UPDATE permissions) and an admin user with a couple additional permissions.

So basically in the Windows 7 command prompt (local development only)...

Is a role a user, group or a loosely used term specific to PostgreSQL?
How do I grant only specific permissions to a specific user, for all tables in a database?
How do I grant all permissions to a specific user, for all tables in a database?
How, if at all, does a role compare to a user when using GRANT or REVOKE for user permissions?

A role is an entity that can function as a user and/or as a group. A role WITH LOGIN can be used as a user, i.e. you can log in with it. Any role can function as a group, including roles that you can also log in as. So "user" and "group" are essentially terms that indicate the intended usage of a role, there's no real distinction between them.  Even in the PostgreSQL flavour of SQL the two are used more or less as synonyms.  For example, the documentation on CREATE USER says:

  CREATE USER is now an alias for CREATE ROLE.

Granting all ... see the manual for GRANT. You probably actually want to grant rights to ALL TABLES IN SCHEMA public rather than all tables in the database.

Granting some rights ... same thing, but instead of GRANT ALL use GRANT SELECT, INSERT for example. Again, see the manual.

A role is a user, and/or a group. You can only grant to roles, because roles are all that there is.


Which Oracle directory takes up the most space and why? Does any directory file in Oracle occupy the most space? Why does it takes up the most? e.g. system

In a long-running, stable Oracle database instance, configured the "recommended" way, it's probably going to be the Fast Recovery Area.
That's because it holds all sorts of weird and wonderful things, including (but not limited to):

Archived Redo Logs
Database backups, of which there may be several
Flashback Database logs

Not that you can do much about it.  Like most things about Oracle, it will take up as much space as it wants/needs to.  Your job in Capacity Planning your database instance(s), is basically to make sure it never runs out!


Rotate Postgres log at 5GB How to make postgres 9.1 log to have maximum size 5GB and to be circle when comes to 5GB ?
At the moment my log file is over 500GB.

/var/lib/postgres/

Have you tried setting log_rotation_size as described in http://www.postgresql.org/docs/9.1/static/runtime-config-logging.html#GUC-LOG-ROTATION-SIZE ? That would work if the log is managed directly by postgresql. If you are using syslog then you should look at newsyslog or logrotate.


When to use NULL and when to use an empty string? I'm interested mainly in MySQL and PostgreSQL, but you could answer the following in general:

Is there a logical scenario in which it would be useful to distinguish an empty string from NULL?
What would be the physical storage implications for storing an empty string as...

NULL?
Empty String?
Another field?
Any other way?

Let's say that the record comes from a form to gather name and address information. Line 2 of the address will typically be blank if the user doesn't live in apartment. An empty string in this case is perfectly valid. I tend to prefer to use NULL to mean that the value is unknown or not given.

I don't believe the physical storage difference is worth worrying about in practice. As database administrators, we have much bigger fish to fry!


sql divide by zero error I have an equation

((P.RealisedConsumption / (NULLIF((PO.ActualQty * P.QuantityPO), 0)
    / NULLIF(1000000, 0))) - 1) * 100 AS FibreScrapFactor

This works for the example order that I am looking at.  However if i take out the where clause (so i have all orders) i get divide by zero error.

If I limit the / 1000000 to 10 which i then have

((P.RealisedConsumption / (NULLIF((PO.ActualQty * P.QuantityPO), 0)
    / NULLIF(10, 0))) - 1) * 100 AS FibreScrapFactor

This works for all orders, but is incorrect I need to divide by a million.  How can I get this to work?   The columns are numeric(32, 16).

Since your data types are already decimal types, perhaps you need to remove NULL values with a WHERE clause instead of coalescing them to 0, which would result in the "divide by zero" error.

Something like:

SELECT ((P.RealisedConsumption / (NULLIF((PO.ActualQty * P.QuantityPO), 0)
    / NULLIF(10, 0))) - 1) * 100 AS FibreScrapFactor
FROM  <...>
WHERE PO.ActualQty IS NOT NULL
    AND P.QuantityPO IS NOT NULL


Facing Error in "Execute Sql task" component (The type of the value (DBNull) being assigned to variable) Hope you are doing well
I have a table as you can see below :

I have a variable "ExcelPath"  in "SSIS" in which I keep "FilePath" column .

Then I have "Execute SQL task" and the query I wrote in this task as you can see below

If you compare the sql query with the table you find out that I need to retrieve "DestinationTable" column where the "Filepath" column is equal to what is in my variable "ExcelPath"

the parameter mapping tab is configured like this :

and the result set tab shows I want the output into another variable called "TableName"

I think everything is fine , I have no idea why I'm getting this error :
"The type of the value (DBNull) being assigned to variable "User::TableName" differs from the current variable type (String)"

I was wondering if you could help me up with this issue.
Thanks in advance

Problem Solved .
See, I wanted to retrieve only one column on my table so I set the result set of the general tab to "single row"
as you can see here :

as you can see here

And in result set tab , I configured the resultName to my column name


Method to keep track of large number of sets with high cardinality We're building an analytics service. One of our needs is to maintain ~100MM sets each with anywhere between one member to 1MM members, with each member represented as a long. We need to be able to very quickly:

-Add a member to a set, and know whether it was added

-Update a counter indicating the size of the set

We have a realtime processing loop that ingests new data and needs to be able to update some number of the sets as well as its counter.

At the moment, we have this implemented in PostgreSQL, distributed application side by set name. There's one table with two columns: the set name in the first and the value in the second, as well as an index on set name, value. Is there a better method than this? AFAIK it's not a problem with a standard solution.

Optimal table size would be environment specific. I had an almost exact similar environment.  A billing/tracking/logging system was generating 2-3GB of data per day and was already at 1.5TB in 1 table alone.  An ETL process fed data in so that was the only 'write', while thousands of users 'read' it.

It seems like you need to maximize both writes and reads like I did, so questions that comes to mind are what's your budget and what's the concurrency going to be like, as in how many requests per second on the same table so we can figure out if we also need to address locking.  Also, what are your disk arrays currently configured like and do you have any baselines?

Read this first.
Top 10 Best Practices for Building a Large Scale Relational Data Warehouse

Each point is critical for success, at least in the 1.5 TB nightmare I had inherited.  This article hits most of it on the head.  Remember that you can have 'historic' databases mapped by views, and keep the 'live' databases small by purging the to the historic databases often so backups and performance on most recent data is fast.

Concurrency:
Isolation Levels & Snapshot Level Isolation

This basically makes SQL Servers isolation level the same as Oracle.  Reads don't block writes, writes still block reads.  It comes at the expense of (I believe) 14 bytes added to each row and a copy of it stored in TempDB per update.  This means your tempDB is going to be a big huge bottleneck if it isn't optimized for this so watch the metrics carefully.

Writes:

To optimize writes you would want to optimize your transaction log first, IMO set the 'time to recovery' option to a higher number and let the lazywriter update the data when it can.  This would mean throwing lots of IOPS at the tlog for writes and keeping writes in the Tlog drive under 5ms during full load.  The reason we set the time to recover to a higher number is so the transaction log can update the data less, thus being less dependent on your disk arrays.

Record disk sec/writes on all your data/tlog drives.

Partitioning:
Efficiently delete data by using partitioning.  It's a metadata operation.

Reads:

If you have high cardinality you can vastly improve read performance through the transparent compression option in SQL Server, at the expense of writes... and it's a 'enterprise only' option, not sure if you have that coming from Postgres.

Try to keep base table data in memory.  Record disk sec/reads metrics to see how fast your responses are.

Ensure your queries are using partition elimination.  Kim Tripp has a great video of it here.

This is just a 'brief overview' without knowing more about your environment, the link to the data warehouse article is very good and covers a lot of what I wanted to say.  Feel free to give us more details or ask questions.


Actual Execution plan does not have memory grant info I manually ran query in SSMS and captured actual execution plan of the query by enabling its icon in SSMS. I am not getting "Memory Grant" when I keep my mouse on SELECT OPERATOR. I didn't get in properties window as well. I checked the XML data of plan as well and I am getting below Information which is not useful:
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="629094" EstimatedPagesCached="78636" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="3836688" />

I tried mulitple versions of SSMS but no luck.
Anyone else has faced this wierd issue? Any help would be appreciated.

Your query is probably very simple and does not require any memory grant.
It is trivial to reproduce:

See Understanding SQL Server memory grants for more background.


Update parent id Hierarchy - SQL Server I have a table with a column with the account IDs and I need to create a hierarchy scheme. So I created a column to add the parent account IDs, but I do not know how to update dynamically.

I updated some records manually to find out how it would look, how could I make a script to update the other almost 2,000 lines?

You can use a RECURSIVE query for this job:

CREATE TABLE accounts(account varchar(10), account_parent varchar(10), updated_field int);
INSERT INTO accounts VALUES
('10000', NULL, 0),
('10001','10000', 0),
('10002','10001', 0),
('10003','10002', 0),
('10004','10003', 0),
('10005','10004', 0),
('10006','10005', 0),
('10007', NULL, 0),
('10008', NULL, 0),
('10009', NULL, 0),
('10010', NULL, 0),
('10011', NULL, 0);

GO

12 rows affected

;WITH tree AS
(
    SELECT c1.account, c1.account_parent, Level = 1
    FROM dbo.[accounts] c1
    WHERE c1.account = '10000'
    UNION ALL
    SELECT c2.account, c2.account_parent, Level = Level + 1
    FROM dbo.[accounts] c2 INNER JOIN tree ON tree.account = c2.account_parent
)
UPDATE accounts
SET    updated_field = Level
FROM   tree
INNER  JOIN accounts
ON     accounts.account = tree.account
OPTION (MAXRECURSION 2000)
;
GO

7 rows affected

SELECT * FROM accounts;
GO

account | account_parent | updated_field
:------ | :------------- | ------------:
10000   | null           |             1
10001   | 10000          |             2
10002   | 10001          |             3
10003   | 10002          |             4
10004   | 10003          |             5
10005   | 10004          |             6
10006   | 10005          |             7
10007   | null           |             0
10008   | null           |             0
10009   | null           |             0
10010   | null           |             0
10011   | null           |             0

Keep in mind you must set a MAXRECURSION value greater enough to get all updated records.

Updating records where parent is null

To update the rest of the records where account_parent is null I've used a CTE function with LAG().

Note the CTE uses WHERE    account >= '10006' to flag the last record of the previous tree structure. The following records must have account_parent = null

WITH upd AS
(
    SELECT   account, account_parent,
             LAG(account) OVER (ORDER BY account) AS prev_account
    FROM     accounts
    WHERE    account >= '10006'    -- this is last element of the tree
)
UPDATE     acc
SET        account_parent = upd.prev_account
FROM       accounts acc
INNER JOIN upd
ON         upd.account = acc.account
WHERE      upd.prev_account IS NOT NULL
;
GO

5 rows affected

This is the final result:

SELECT * FROM accounts;
GO

account | account_parent | updated_field
:------ | :------------- | ------------:
10000   | null           |             1
10001   | 10000          |             2
10002   | 10001          |             3
10003   | 10002          |             4
10004   | 10003          |             5
10005   | 10004          |             6
10006   | 10005          |             7
10007   | 10006          |             0
10008   | 10007          |             0
10009   | 10008          |             0
10010   | 10009          |             0
10011   | 10010          |             0

dbfiddle here


Immigrate to file-per-table mode in MySQL I have 20 MySQL databases and in each there are about 20 tables. All the data is saved in a file called ibData1. Now I discovered about the file per table mode but if I change the value in the my.ini file what would happen?

How can I immigrate to file-per-table mode and change all the data structure?

I prefer to have a directory for each Database where each table is saved in a different file.

This is so funny. Yesterday, someone asked how to do the opposite and I answered that question : Migrate from innodb_file_per_table to off in MySQL

Here the steps for what you want

STEP #1

Set innodb_file_per_table to 0 in my.ini

[mysqld]
innodb_file_per_table = 1

STEP #2

Login in Command Prompt in Administrator mode and run

C:\> net stop mysql
C:\> net start mysql

STEP #3

Convert every InnoDB table to InnoDB again

set MYSQLUSER=root
set MYSQLPASS=rootpass
mysql -u%MYSQLUSER% -p%MYSQLPASS% -ANe"SELECT CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' ENGINE=InnoDB;') FROM information_schema.tables WHERE engine='InnoDB';" > C:\ConvertInnoDBToInnoDB.sql
notepad C:\ConvertInnoDBToInnoDB.sql

Once you view the script and are satisfied, login to mysql and run this

mysql> source C:\ConvertInnoDBToInnoDB.sql

Give it a Try !!!


Evaluate a string value as a computed expression in an SQL statement (sthg like EVAL) Is it possible to evaluate a string value as a computed expression in an SQL statement like EVAL in some programming languages ?

>>> EDIT 4 : more precisely : an expression that contains a list of numbers separated by an arithmetic operator... <<<

For example :
Table A :

  CREATE TABLE A (
    id INT NOT NULL PRIMARY KEY,
    note INT,
    detailNote VARCHAR(20)
  );

insert a row :

    INSERT INTO A (id, note, detailNote)
      VALUES (1,20,"2+4+2+2+2+0+2+0+2");

I would like to be able to update 'note' column with the value of the evaluation of 'detailNote' column such as :

    UPDATE A
      SET note = CAST(detailNote AS UNSIGNED);

or :

    UPDATE A
      SET note = 1*(detailNote);

These 2 trials produce an error : "truncated incorrect integer/double value ..."

Many thanks for any help !

>>> EDIT. 1 <<<
I am aware that the DB model is NOT the right one; it should be :

  CREATE TABLE A (
    id INT NOT NULL PRIMARY KEY,
    note INT
  );
  CREATE TABLE B (
    id INT NOT NULL REFERENCES A(id),
    seq INT NOT NULL,
    detNote INT,
    PRIMARY KEY (id, seq)
  );
    INSERT INTO A (id, note)
      VALUES (1,20);
    INSERT INTO B (id, seq, detNote)
      VALUES (1,1,2);
    INSERT INTO B (id, seq, detNote)
      VALUES (1,2,4);
    INSERT INTO B (id, seq, detNote)
      VALUES (1,3,2);

etc.
and the update query could be :

    UPDATE A
      SET note = (SELECT SUM(detNote) FROM B WHERE A.id = B.id);

But, the use of the string is more compact and the content is sure.

>>> EDIT. 2 <<<
1st solution (bad one, but exists...) : (because I am sure of integer (0-9) position :

    SELECT (SUBSTR(detailNote,1,1) +SUBSTR(detailNote,3,1)
      + SUBSTR(detailNote,5,1) +SUBSTR(detailNote,7,1)
      + SUBSTR(detailNote,9,1) +SUBSTR(detailNote,11,1)
      + SUBSTR(detailNote,13,1) +SUBSTR(detailNote,15,1)
      + SUBSTR(detailNote,17,1) +SUBSTR(detailNote,19,1))
    FROM A;

>>> EDIT. 3 <<<
one could tell me to add 10 INT fields in A...but one string is easier to manipulate...

for my specific problem, an answer could be :

    DROP FUNCTION stringExp2Int;
    DELIMITER $$
    CREATE FUNCTION stringExp2Int (expression VARCHAR(20))
        RETURNS INT
        NO SQL
        BEGIN
            DECLARE val INT DEFAULT 0;
            DECLARE ct INT DEFAULT 1;
            WHILE ct <= LENGTH(expression) do
                SET val = val + SUBSTR(expression,ct,1);
                SET ct = ct + 2;
            END WHILE;
             RETURN val;
        END
    $$
    DELIMITER ;

Using LOCATE to find the next separator, could be more secure :

    DROP FUNCTION stringExp2Int;
    DELIMITER $$
    CREATE FUNCTION stringExp2Int (expression VARCHAR(20))
        RETURNS INT
        NO SQL
    BEGIN
      DECLARE val INT DEFAULT 0;
      DECLARE ct INT DEFAULT 1;
      DECLARE pos INT DEFAULT 0;
      DECLARE sep CHAR(1) DEFAULT "+";

      WHILE ct <= LENGTH(expression) DO
         SET pos = LOCATE(sep,SUBSTR(expression,ct,LENGTH(expression)-ct+1));
         IF pos >= 1 THEN
            BEGIN -- when pos = 1 : add a null value
                SET val = val + SUBSTR(expression,ct,pos-1);
                SET ct = ct + pos;
            END;
        ELSE
            BEGIN
                SET val = val + SUBSTR(expression,ct,LENGTH(expression)-ct+1);
                SET ct = ct + LENGTH(expression)-ct+1;
            END;
        END IF;
     END WHILE;
     RETURN val;
    END
    $$
    DELIMITER ;

Trials :

mysql> SELECT stringExp2Int("");
+-------------------+
| stringExp2Int("") |
+-------------------+
|                 0 |
+-------------------+

mysql> SELECT stringExp2Int("+");
+--------------------+
| stringExp2Int("+") |
+--------------------+
|                  0 |
+--------------------+

mysql> SELECT stringExp2Int("1");
+--------------------+
| stringExp2Int("1") |
+--------------------+
|                  1 |
+--------------------+

mysql> SELECT stringExp2Int("+1");
+---------------------+
| stringExp2Int("+1") |
+---------------------+
|                   1 |
+---------------------+

mysql> SELECT stringExp2Int("1+");
+---------------------+
| stringExp2Int("1+") |
+---------------------+
|                   1 |
+---------------------+

mysql> SELECT stringExp2Int("1+1");
+----------------------+
| stringExp2Int("1+1") |
+----------------------+
|                    2 |
+----------------------+

mysql> SELECT stringExp2Int("+1+1+");
+------------------------+
| stringExp2Int("+1+1+") |
+------------------------+
|                      2 |
+------------------------+

mysql> SELECT stringExp2Int("+++1+++1+++");
+------------------------------+
| stringExp2Int("+++1+++1+++") |
+------------------------------+
|                            2 |
+------------------------------+

mysql> SELECT stringExp2Int("+++001+++001+++");
+----------------------------------+
| stringExp2Int("+++001+++001+++") |
+----------------------------------+
|                                2 |
+----------------------------------+

and :

mysql> SELECT stringExp2Int(detailNote) FROM A;
+---------------------------+
| stringExp2Int(detailNote) |
+---------------------------+
|                        16 |
+---------------------------+
1 row in set (0.00 sec)

I close this question...hope it could be useful for someone...


Problem using multiple JOINs I have a list of customer IDs in table newsletter_subscriber. I have an EAV table that contains a lot of information about these customers, table customer_entity_varchar.

For every entry in newsletter_subscriber, I'd like get a specific value from table customer_entity_varchar where the customer ID (named entity_id in this table) is the same as the customer_id in newsletter_subscriber, and the attribute ID is 1.

Here's what I've tried

SELECT newsletter_subscriber.customer_id, customer_entity_varchar.value
FROM newsletter_subscriber
JOIN customer_entity_varchar
    ON newsletter_subscriber.customer_id = customer_entity_varchar.entity_id
    AND customer_entity_varchar.attribute_id = 1

This does not work, and customer_entity_varchar.value contains a wrong value, one that does not have an attribute ID of 1.

You can try both of this:

SELECT a.customer_id, b.value, b.attribute_id
FROM a
JOIN b
    ON a.customer_id = b.customer_id
WHERE b.attribute_id = 2

or:

SELECT a.customer_id, b.value, b.attribute_id
FROM a
JOIN (SELECT value, attribute_id FROM b WHERE attribute_id = 2) as b
    ON a.customer_id = b.customer_id


Migrating MySQL 5.0 to 5.5 - simple databases I have a LAMP server on my LAN running a very old version of RedHat with MySQL 5.0, and I'm trying to migrate it to Ubuntu 12.04 to update it.

I know the advice is to migrate MySQL 5.0 to 5.1 then 5.2, and so on.  However, there are no triggers or stored procedures in these databases, just simple MyISAM and InnoDB tables and indexes.

Can anyone tell me what if I can just export the databases in 5.0 from the RedHat server using phpMyAdmin, and then import them into 5.5 on Ubuntu?

The data is mainly used by PHP to build dynamic web pages.

Make full back (preferably mysqldump)
Follow official guide : http://dev.mysql.com/doc/refman/5.5/en/upgrading.html

and also this one http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-early-5-0.html