Is it a better idea (performance is key) to store in a normalized table and retrieve by JOIN or in a single nvarchar column and retrieve using LIKE? NOTE: The application is not really about websites or keywords, I am using that here only to provide reference points for discussion.

I have a data set that is really just a string of words. Think of at "SEO" keywords for a website. I have a table that contains data that relates to this data. For example, say a list of websites.

The key scenario for this data set is retrieval against the master data, in two-way fashion. That is, while I can get a website with all its associated keywords, I should also be able to input a keyword and get a list of websites attached to that keyword. There is no other metadata attached (or attachable) to the keyword itself.

Now, given that query performance while retrieving the website -> keyword and keyword -> website (significantly more use cases) is paramount, which of these design scenarios makes more sense?

In the websites table, I have a single nvarchar column that contains a string of all the keywords for that website, possibly comma-separated. Retrieval in this case would be using a LIKE operator on that row.
I create a separate keywords table, with two columns (Id, Keyword), put all the keywords in there, and then have a third table WebsiteKeywords that contain the mappings between the websites and keywords tables. To retrieve, we do a three-way join between websites, websitekeywords and keywords tables.

The retrieval is designed to happen via web service infrastructure, so there will be multiple look ups from the middle-tier layer before it thinks it has all the data. So a single "search" fired as a call will result in multiple such look ups, all results will be aggregated by that middle tier before being returned to the caller.

What are your suggestions?

Definitely you want to use option 2. Not only will your queries be faster (= is always faster than like) but you can also index on that keyword field for even faster queries AND your storage space will be significantly reduced since you are not storing the long keyword strings for each website.


table design to track significant events associated with an item An Orders table I'm dealing with at present in design phase has about 10 or more columns.

Each OrderID in the Orders table will have certain significant events or milestones. The number of Orders could go into the millions. At the moment, I'm aware of about 20 important events to track for each Order, but that number may increase or decrease.

Of these 20 or so events, only a handful will always take place, while the rest are just potential events. Also, many - but not all - of these events have at least two points which need storage:

the Date the event is supposed to happen (if it is no longer just a "potential" event)
the Date the event did happen

The events data will be updated in the database from external sources in the form of .xls or .csv files. For all events, it is only possible for the given event to happen once.

What would be the best design for storing this milestone event data?
Would you try to use a somewhat normalized design, where there is an Events table designed similar to this...

CREATE TABLE dbo.Events (
    EventID             INT
    , OrderID           INT
    , EventExpectedDate DATEIME2(7)
    , EventActualDate   DATETIME2(7)
    , EventTypeID       INT
    , EventSkip         BIT
    ...
    )

Or would you try a less normalized approach and flatten all the possible events out into one  very wide events table that mimics what the .xls/.csv update files will look like?

CREATE TABLE dbo.Events (
    EventID             INT
    , OrderID           INT
    , Event_1_ExpDate   DATEIME2(7)  /* No, the actual names would not include 1, 2, 3 */
    , Event_1_ActDate   DATETIME2(7) /* The actual names would name type of Event */
    , Event_2_ExpDate   DATETIME2(7)
    , Event_2_ActDate   DATETIME2(7)
    ...
    , Event_20_ExpDate   DATETIME2(7)
    , Event_20_ActDate   DATETIME2(7)
    )

Or is there another pattern you would use?

Normalize always unless you have a very good reason not to. It should not be flipping a coin.

Neither of your proposed designs seems optimal to me for a variable / unknown number of events, since when "that number may increase or decrease," in both cases, you have to change the schema and the code.

I also don't believe that you have to base your core table design around the format of the input files. This is why we have staging tables, ETL processes, SSIS, C#, etc. There are many ways to transform data from flat files into a format that is more appropriate for a relational database. Don't design your database because of what your flat files look like.

So you could have staging tables that look like the input files, but then you would load the data into real tables that look perhaps like this:

CREATE TABLE dbo.Events -- lookup table describing events
(
  EventID INT PRIMARY KEY,
  Description VARCHAR(32) NOT NULL UNIQUE
);

CREATE TABLE dbo.OrderEventLog -- actual event data
(
  EventID INT FOREIGN KEY REFERENCES dbo.Events(EventID),
  OrderID INT FOREIGN KEY REFERENCES dbo.Orders(OrderID), -- guess
  ExpectedDate DATETIME2(7),
  ActualDate DATETIME2(7)
);

Now when you have a new type of event, you just insert it into the dbo.Events table, and then you can insert related events into dbo.OrderEventLog without having to change the schema of either table, and without having to change the interface to your stored procedures or bulk inserts into the staging tables either. This also keeps the history table (and all of its indexes) nice and narrow, and allows you to build indexes that favor searches for specific types of events.

You can always flatten the data later - which is typically what you do when you get to a point where you are building a data warehouse around the data.


Optimizing PostgreSQL hstore for lots of small updates I have a situation where I will have one web session per row, but this session will generate dozens of updates to an hstore field in that row, one k/v at a time.  At the end, I will have a completed structure.  The keys will be more or less the same per session, but will evolve over time.  The values will be mixed, with some being unique per session (e.g. email) and some having lots of repetition (e.g. male/female).

I am running under the assumption that lock contention will only be present at the row level, which is fine.  What other gotchas should I look out for?  I have zero experience with hstore, so I really want to make sure I understand what I am in for.  Any feedback is appreciated.

With lots of UPDATEs, each one, no matter how small, will cause the entire row to be rewritten into a new version, as a consequence of the MVCC mechanism.
Then the old version of the row will be picked up at some point by autovacuum when it's certain that no transaction may need it, and its space will be flagged as reusable .
The constant turnover of disk space for old and new rows leads to fragmentation, especially if the rows are large in size.
Additionally, the whole set of operations are logged into the WAL files (unless the table is unlogged).

For these reasons, high frequency UPDATEs of large columns is a worst case scenario for postgres.

So, if that session data doesn't really require durable storage in the first place, a mixed disk-memory specialized key/store engine like Redis is likely to perform way better.

Otherwise, this other question: What fillfactor for caching table? has good information and advice on how to mitigate the difficulties.


db2 express c mac osx I'm experiencing some troubles with the execution of DB2 express c on Mac OSX 10.6 Snow Leopard.

I have installed the db system, creating two users, db2 and db2-user. The latter is the instance owner. I have sourced db2profile and the env variables seem to be set correctly.

Db2 starts successfully, but I'm not able to run db2sampl, the error is:

Attempt to start DB2 instance failed.
SQL1019N  The node name "db2-user" specified in the command is not valid.

I have googled the error and it looks like I have to change the DB2INSTANCE variable, but I do not know what value should be.

You do not want to change DBINSTANCE. All that does is change what instance is the "main" one for your OS. And you only have one instance from what I understand (named db2 I believe). I'm betting this has more to do with permissions as you are attempting to run db2sampl, which I believe creates the sample database if I remember correctly. Instance owner should be able to run this without any issues.

If I were you, I would run db2sampl as instance owner and then perform your necessary GRANTs for db2-user once the database is there.


DataGrip shows a time substantially slower than EXPLAIN ANALYZE I searched alot for the answer to my question.

I have a table with 134 rows and 4 rows. My EXPLAIN ANALYZE says the execution time was: 0.038ms. But the actual execution time (using DataGrip) was: 123ms

A complex query with multiple joins shows these stats:
EXPLAIN ANALYZE execution time: 38.605ms
DataGrip execution time: 177ms

My VPS has 2GB ram and 2 vCores.

Is it more likely a problem with my server or my config, or is this behavior normal?

Here is the EXPLAIN ANALAYZE of the simple select:

Seq Scan on champions  (cost=0.00..4.34 rows=134 width=37) (actual time=0.007..0.024 rows=134 loops=1)
Planning time: 0.053 ms
Execution time: 0.044 ms

Here is the log of DataGrip:

[2017-02-10 21:38:30] 134 rows retrieved starting from 1 in 135ms (execution: 127ms, fetching: 8ms)

I am really confused!

EDIT:

These are my only changes to my config:

max_connections = 200
shared_buffers = 512MB
effective_cache_size = 1536MB
work_mem = 32MB
maintenance_work_mem = 128MB
min_wal_size = 1GB
max_wal_size = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
listen_addresses = '*'

EXPLAIN ANALYZE is showing you the planning and execution time.
DataGrip is showing you the completion time. This may include,

Network transit
Drawing in the GUI, etc.

Just think, if you have a one-row table with one bytea column that contains 2GB, transferring it across the network or even using TCP/IP will be a substantial transit compared to the planning and execution of the query.

  [2017-02-10 21:38:30] 134 rows retrieved starting from 1 in 135ms (execution: 127ms, fetching: 8ms)

That's 134 rows you didn't have to retrieve using EXPLAIN ANALYZE.


Postgres database deadlock issue I am using Cloudera Manager to monitor and configure Hadoop. I choose PostgreSQL as metastore of HUE - a web portal to submit your SQL to Hadoop.

When I submit a SQL, the HUE says the database is locked. I don't have any database management experience.

So, how to find the root cause and diagnose?

I try to connect to the database postgres which I believe this is the meta-database of PostgreSQL itself. I expect I can find the processes, events and lock information in this database.

postgres=> \c postgres
Password for user hue:
You are now connected to database "postgres" as user "hue".
postgres=> \d
No relations found.
postgres=>

I connect to this database - 'postgres' as user - 'HUE'. But I didn't find any tables under it. Am I using the wrong user?

How can I access this database successfully? How can I discover why the database is locked? Where is it locked? On which table and which database?

Your software is most likely not using the default postgres database (that is a database that is normally reserved only to store the list of databases, users, and other globals).

From psql, run \l to list the available databases. Your database will be something other than postgres, template0, or template1. Try connecting to one that is not one of the above (\c dbname) and then run \d again to list tables.


How to get data from three different tables This question is continuing of this issue: How to get data from two different tables?

Now I need to connect the table Sessions with 2 tables:

Input
Downloads

When I use this query with real data:

SELECT sessions.ip
    , COUNT(sessions.id)
    , COUNT(input.input) as TotalInputs
    , COUNT(DISTINCT input.input) as UniqInputs
    , COUNT(downloads.shasum) as files
    , COUNT(DISTINCT download.shasum) as Uniqfiles
FROM sessions, input, downloads
WHERE sessions.id = input.session
    AND sessions.session = downloads.session
    AND date_format(sessions.starttime, '%Y-%m-%d') > "2015-01-01"
GROUP BY sessions.ip
ORDER BY COUNT(sessions.id) DESC LIMIT 5;

I get this output:

ip  | COUNT(sessions.id)    | TotalInputs   | UniqInputs    | files | Uniqfiles
IP1 |              11145    |       11145   | 15            | 11145 |         8
IP2 |               9125    |        9125   | 71            |     0 |         0
IP3 |               7882    |        7882   | 56            |  7882 |        19

But numbers for count(sessions.id), TotalInputs and Files are not accurate. For example if I use this query:

SELECT downloads.shasum
FROM sessions, downloads
WHERE sessions.id = downloads.session
    AND date_format(sessions.starttime, '%Y-%m-%d') > "2015-01-01"
    AND sessions.ip = "IP3";

I find that Files count for IP3 have proper value of 752 (not 7882). Real value for TotalInputs is less than COUNT(sessions.id).

How can I fix my query?

Sample data is available on this SQL Fiddle.

Using the above query and below sample data, I get this output:

ip  | COUNT(sessions.id)    | TotalInputs   | UniqInputs    | files | uniq_files
IP2 | 3                     | 3             | 2             | 3     | 1
IP3 | 8                     | 8             | 4             | 8     | 2

I need this output:

ip  | COUNT(sessions.id)    | TotalInputs   | UniqInputs    | files | uniq_files
IP2 | 1                     | 3             | 2             | 1     | 1
IP3 | 3                     | 5             | 4             | 3     | 2

How can I update my query ?

Sample Sessions data:

id      | starttime             | endtime               | sensor    | ip    | termsize  | client
id1     | 2015-05-07 11:01:20   | 2015-05-07 18:01:32   | 10        | IP3   | 80x50     | 3
id2     | 2015-05-07 18:03:20   | 2015-03-07 18:11:32   | 2         | IP2   | 80x50     | 1
id3     | 2015-05-07 23:05:20   | 2015-06-07 18:10:32   | 10        | IP3   | 80x70     | 3
id4     | 2015-05-07 13:05:20   | 2015-05-09 20:05:32   | 7         | IP3   | 60x30     | 5

Sample Input data:

id  | session   | timestamp             | realm | success   | input
1   | id1       | 2015-07-13 10:29:18   | NULL  | 1         | date
2   | id3       | 2015-08-13 10:11:18   | NULL  | 0         | aaa
3   | id1       | 2015-03-13 10:11:18   | NULL  | 0         | aaa
4   | id1       | 2015-07-14 10:33:15   | NULL  | 1         | uname
5   | id3       | 2015-05-19 20:33:11   | NULL  | 1         | netstat
6   | id2       | 2015-09-22 10:53:21   | NULL  | 1         | pwd
7   | id2       | 2015-09-22 10:58:11   | NULL  | 1         | pwd
8   | id2       | 2015-11-03 09:53:07   | NULL  | 0         | bbb

Sample Downloads data:

id  | session   | timestamp             | url           | outfile   | shasum
1   | id1       | 2014-07-13 12:15:47   | http://xxx    | xxx       | SHA1
2   | id2       | 2014-09-13 12:18:50   | http://xxx2   | xxx2      | SHA2
3   | id1       | 2015-09-11 13:20:50   | http://xxx3   | xxx3      | SHA1
4   | id3       | 2016-01-19 18:21:30   | http://xxx4   | xxx4      | SHA3

This query:

Uses ANSI JOIN (LEFT, INNER, ...)
Uses LEFT JOIN for each table in order to count Sessions IPs without Input or Downloads
Uses DISTINCT for each COUNT in order to remove duplicates added by the JOIN between tables
Counts values for totals and counts ids for unique counts

Query:

SELECT s.ip
    , COUNT(DISTINCT s.id)
    , COUNT(DISTINCT i.id) as TotalInputs
    , COUNT(DISTINCT i.input) as UniqInputs
    , COUNT(DISTINCT d.id) as files
    , COUNT(DISTINCT d.shasum) as Uniqfiles
FROM sessions s
LEFT JOIN input i
    ON s.id = i.session
LEFT JOIN downloads d
    ON s.id = d.session
GROUP BY s.ip;

SQL Fiddle

Output:

ip  | COUNT(DISTINCT s.id)  | TotalInputs   | UniqInputs    | files | Uniqfiles
IP2 |       1               |       3       |   2           | 1     |   1
IP3 |       3               |       5       |   4           | 3     |   2


How can I set a column to NOT NULL without locking the table during a table scan? (former question was: Will Postgres use a multi-column index when setting multiple colums not null?)

Typically, when I set a column not null, if it doesn't have an index then I add it first, so that postgres can (hopefully) use the index while doing the table scan while locking the table, so that the table is locked for a shorter amount of time.

I want to set several columns not null, like so:

alter table foos
  alter column bar1 set not null
  alter column bar2 set not null
  alter column bar3 set not null
  alter column bar4 set not null;

If I make a multicolumn index for these columns, will postgres use it when scanning the locked table before making this alteration?

CREATE INDEX CONCURRENTLY my_index on foos (bar1, bar2, bar3, bar4);

What if I made a partial index on IS NULL (or, IS NOT NULL)

CREATE INDEX CONCURRENTLY my_index on foos (bar1, bar2, bar3, bar4) where bar1 is null and bar2 is null and bar3 is null and bar4 is null;

Another answer from another postgresql contributor.

PostgreSQL will not even try to use any indexes during execution of "alter table set not null". It is just not implemented.

Proper implementation of index scan is difficult part. We cannot just do something like this query

select exists(select from foos where bar1 is null)

from alter table command for various reasons. Such feature will require big piece of code (and, probably, fragile code under some edge cases), much work, only for limited use cases. Something what developers do not like. Actually, pgsql-hackers community does not like how NOT NULL is stored in system catalog. It would be cleaner with a redesign of this part of the catalog. After that, it would be possible to do SET NOT NULL NOT VALID with a short lock and table validation without an exclusive lock. Similar to alter table .. add constraint ... not valid + alter table .. validate constraint for check constraint or foreign key. But such redesign is much more work, and there is no one who wants to do it.

But I have good news: in PostgreSQL 12 (and above) scan the entire table is not the only option. alter table set not null can prove correctness of NOT NULL by existing check constraints. So, one can do:

-- short-time exclusive lock
alter table foos
  add constraint foos_not_null
  check (bar1 is not null) not valid;

-- seqscan, but without exclusive lock, concurrent sessions can read/write
alter table foos validate constraint foos_not_null;

-- exclusive lock, but foos_not_null proves: there is no NULL in this column
-- so this alter table would be fast
alter table foos alter column bar1 set not null;
-- not needed anymore
alter table foos drop constraint foos_not_null;

That was my patch. Yeah, this looks like a workaround. But, fortunately, it was merged and now it is possible to set not null without a long exclusive lock.


Pivot values to columns I'm trying to create a view that groups score data for each week and displays it for each user but I don't know which commands to use.

Sample data:

// scorecards table
week | user | score    |
1    | 1    | 100      |
1    | 2    | 50       |
1    | 3    | 75       |
2    | 1    | 20       |
2    | 2    | 30       |
2    | 3    | 40       |

Desired output:

week | user1 | user2 | user3
1    | 100   | 50    | 75
2    | 20    | 30    | 40

Since MySQL doesn't allow FULL OUTER JOIN this is a bit trickier, but doable taking the week as main key for the left joins.

select distinct s.week, user1, user2, user3 from scorecards s left outer join
(select week, score as user1 from scorecards where user = 1) a
on s.week = a.week left join
(select week, score as user2 from scorecards where user = 2) b
on s.week = b.week left join
(select week, score as user3 from scorecards where user = 3) c
on s.week = c.week
;


Lock mysql table to prevent select and insert at the same time I know this question has been asked earlier and I have also read the documentation. But I guess I'm stupid not understand.

I have a code that inserts a row and the row should be unique in several field values. But to make this new data really live delivered to the visitors it updates (if new data, inserting it) via ajax and every 5th second. This means it sometimes insert two or more rows at the same time.

I have a SELECT query before the INSERT to check if the row (a row with the exactly same data) already exist. And I also use BEGIN and COMMIT.
Yet duplicate rows appearing.

I'm using innoDB.

I have googled and found LOCK functions but not figured out how to use it. And does it even work for SELECT? The best would be to LOCK the SELECT query checking if the row already exist.

With LOCK you probably mean locking entire table, that should not be needed in InnoDB. Check SELECT .. FOR UPDATE.

But duplicate values should be forbidden with the use of UNIQUE indexes, that way the worst thing you get is an "duplicate key" error and not a duplicated row.

If you have proper indexes in place, you may even use INSERT .. ON DUPLICATE KEY UPDATE.


Best practices for handling language I am a coder, not a database admin, so my question may be a little obvious to some. I just cannot find the correct search term to find the answer.

I'm trying to create a database structure for both currency and language management.
I'm thinking of following structure:

Language

LanguageId int not null primary key
iso2 char(2) not null

For example:

1, EN

2, SV

Then I need to store the name of the language as well, which should be translatable into other languages. For this, I have a table:

Language_Text

LanguageId int not null
LanguageCulture char(5) not null
LanguageName varchar(20) not null
Primary Key(LanguageId, LanguageCulture)

For example:

1, en-US, English

1, sv-SE, Engelska

2, en-US, Swedish

2, sv-SE, Svenska

The issue comes when setting up keys and relationships. In Language there is only 1 key, while in Language_Texts there is an aggregate key. I would like a relation between the two tables, but having problems with this due to difference in keys.

I want to be able to get all various texts for a specific language, as well as query to get a specific culture if need be.

What is the best practice to use in this case?

Thanks in advance for any help in this matter!

Best regards

Peter Kindberg

If I understand your requirements correctly....

I would just use the natural key, LanguageCode-CultureCode ("en-US," for example).  It's small enough.  (I'm using the entire "en-US" as the primary key to differentiate it from "en-GB," for example.)

CREATE TABLE [dbo].[Language](
    [Language] [char](2) NOT NULL,
    [Culture] [char](2) NOT NULL,
    [LanguageCode]  AS (([Language]+'-')+[Culture]) PERSISTED NOT NULL,
 CONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED
(
    [Language] ASC,
    [Culture] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [LanguageCode] UNIQUE NONCLUSTERED
(
    [LanguageCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Language_Text](
    [LanguageID] [varchar](5) NOT NULL,
    [LanguageCode] [varchar](5) NOT NULL,
    [LanguageName] [nvarchar](20) NULL,
 CONSTRAINT [PK_Language_Text] PRIMARY KEY CLUSTERED
(
    [LanguageID] ASC,
    [LanguageCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Language_Text]  WITH CHECK ADD  CONSTRAINT [FK_Language_Text_Language] FOREIGN KEY([LanguageCode])
REFERENCES [dbo].[Language] ([LanguageCode])
GO

ALTER TABLE [dbo].[Language_Text] CHECK CONSTRAINT [FK_Language_Text_Language]
GO

This should allow you to get all articles in en or sv, and you can also query for en-US or sv-SE.  Presumably, although it wasn't in your example, you could also query for en-CA, fr-CA, en, fr, or CA.

Edit--I'm sorry, you're right, no search by Culture in my old code.  Revamped above, sorry.  Here's a sample of content:

Language:

Language-Culture-LanguageCode
en  US  en-US
sv  SE  sv-SE

Language_Text:

LanguageID-LanguageCode-LanguageName
en-US   en-US   English
en-US   sv-SE   Engelska
sv-SE   en-US   Swedish
sv-SE   sv-SE   Svenska

Searching by Culture (Canada):

SELECT        test.dbo.Language_Text.LanguageID, test.dbo.Language_Text.LanguageCode, test.dbo.Language_Text.LanguageName
FROM            test.dbo.Language_Text INNER JOIN
                         test.dbo.Language ON test.dbo.Language_Text.LanguageID = test.dbo.Language.LanguageCode
WHERE        (test.dbo.Language.Culture = 'CA')

Searching by Language (French):

SELECT        test.dbo.Language_Text.LanguageID, test.dbo.Language_Text.LanguageCode, test.dbo.Language_Text.LanguageName
FROM            test.dbo.Language_Text INNER JOIN
                         test.dbo.Language ON test.dbo.Language_Text.LanguageID = test.dbo.Language.LanguageCode
WHERE        (test.dbo.Language.Language = 'fr')

Searching by LanguageCode (Swedish):

SELECT LanguageName
  FROM [test].[dbo].[Language_Text]
  where (LanguageID = 'sv-SE')


How to allow connections to SQL Server with 2 differents ip I have a small issue here: I have a SQL Server instance 2012 (let's call it SQLINSTANCE) on a cluster. I also have 2 network interfaces for this instance:

- SQLINSTANCE.my.network
- SQLINSTANCE.mgt.my.network.

My instance listens on a specific port, 1550.

If I want to connect to it here are my results:

- SQLINSTANCE.my.network\INSTANCE: working
- SQLINSTANCE.mgt.my.network\INSTANCE: NOT working.

My guess is that it involves the SQL Browser, but I can't find how to tell it that it needs to listen on both addresses.

I'm working on a Windows Server 2008r2 without firewall. Both addresses are set as resources for the SQL Server in the cluster manager.

This is the type of setup we use at the place I work for.

I'll assume for the benefit of giving a better example, that your Aliases (CNAMEs) are configured like this:

SQLINSTANCE.my.network -> 192.168.1.30
SQLINSTANCE.mgt.my.network -> 10.0.0.1

You then start the SQL Server Configuration Manager, navigate to the SQL Server Network Configuration branch and open up to the Protocols for SQLINSTANCE (my picture shows MSSQLSERVER as the instance name) and then you open the TCP/IP properties with a right-click and you will get a screen similar to the below screen shot:

In the dialog you ensure the TCP/IP properties are set Enabled : Yes and that the Listen All : No option is set, so that it looks likes the following screen shot:

You then switch to the IP Addresses register and find your first IP address. In this example the settings for the first IP (192.168.0.30) are found at the IP2 section. It will look like this:

Ensure that the option Enabled : Yes is set and the IP address is correct and that the TCP Port : 1550 is set to you reserved port.

Do the same for the other IP address. Locate the IP section (e.g. IP4 in this screen shot) and set accordingly:

And last but not least we have to ensure that Dynamic Ports are disabled. To accomplish this, scroll down to the bottom of the IP Addresses register and set the following settings:

It is essential that both the TCP Dynamic Ports and the TCP Port settings are empty!

When done click OK and restart your SQL Server Instance SQLINSTANCE. Your SQL Server instance SQLINSTANCE will now pick up on both IP addresses.

Good luck!

I wrote a Q & A style article What are valid connection strings for SSMS login box? which explains some of the settings you might encounter when connecting to SQL Servers and their instances. It also briefly explains that the SQL Server Browser service is only required if you don't know the actual port you are connecting to. Added security anybody?


ORA-04031: unable to allocate 4708660 bytes of shared memory I am running Oracle 11g R1 on my local development machine.

I have been trying to run an anonymous block to test the FORALL statement in Oracle to insert 25,000 records.

I get the following error:

ORA-04031: unable to allocate 4708660 bytes of shared memory ("shared pool","DECLARE
  -- temp_rec temp%R...","PL/SQL SOURCE","PLD: Source Heap")
04031. 00000 -  "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
*Cause:    More shared memory is needed than was allocated in the shared
           pool.
*Action:   If the shared pool is out of memory, either use the
           dbms_shared_pool package to pin large packages,
           reduce your use of shared memory, or increase the amount of
           available shared memory by increasing the value of the
           INIT.ORA parameters "shared_pool_reserved_size" and
           "shared_pool_size".
           If the large pool is out of memory, increase the INIT.ORA
           parameter "large_pool_size".

I tried looking here and here but wasn't able to resolve this issue.

I have tried the following:

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM SET cursor_sharing = 'SIMILAR' SCOPE=BOTH;

Please help.

EDIT

The code I am using is as follows:

DECLARE
  TYPE t_varchar IS TABLE OF varchar(512 char);
  biz_hierarchy t_varchar := t_varchar();
  project_team t_varchar := t_varchar();
  user_roles t_varchar := t_varchar();
  username t_varchar := t_varchar();
  sso t_varchar := t_varchar();
BEGIN
  SELECT *
  BULK COLLECT INTO biz_hierarchy, project_team, user_roles, username, sso
  FROM ... -- Query fetches 25000 records

  FORALL i IN biz_hierarchy.FIRST..biz_hierarchy.LAST
    INSERT INTO temp VALUES (biz_hierarchy(i), project_team(i),
                              user_roles(i), username(i), sso(i));

END;
/

The error returned by Oracle does not mention the line number. If I try loading 5000 records, the code block runs successfully. Fails when I try with 25000.

I'd recommend limiting the number of rows you fetch in your BULK COLLECT and wrapping the whole thing inside a loop:

 -- create our output table
 create table temp as
 select
   object_type as biz_hierarchy,
   object_name as project_team,
   object_name as user_roles,
   object_name as username,
   object_name as sso
 from all_objects where 1=0;
/

DECLARE
  TYPE t_varchar IS TABLE OF varchar(512 char);
  biz_hierarchy t_varchar := t_varchar();
  project_team  t_varchar := t_varchar();
  user_roles    t_varchar := t_varchar();
  username      t_varchar := t_varchar();
  sso           t_varchar := t_varchar();
  cursor cur is
    select object_type as biz_hierarchy,
           object_name as project_team,
           object_name as user_roles,
           object_name as username,
           object_name as sso
      from all_objects
     where rownum <= 25000;
BEGIN
  open cur;
  loop
    fetch cur BULK COLLECT
      INTO biz_hierarchy,
           project_team,
           user_roles,
           username,
           sso limit 1000;
    FORALL i IN biz_hierarchy.FIRST .. biz_hierarchy.LAST
      INSERT INTO temp
      VALUES
        (biz_hierarchy(i),
         project_team(i),
         user_roles(i),
         username(i),
         sso(i));
    exit when cur%notfound;
  end loop;

END;

see Asktom on large bulk collect for further info


Check if a database table has been renamed My app has a list of an external db's tables and it needs to be regularly updated. How do I check if a table has been renamed? i.e. how to differentiate between a new table and an old table that's been renamed? Looking for sql/python/sqlalchemy solutions that'll work in as many engines as possible.
I'm aware of OID in postgres but as far as I could tell it isn't very reliable and there's no equivalent in mysql. I suppose I could do something with the create time and modify time but I'm trying to see if there's an easier way.
Edit: The app is a data visualisation tool to which external dbs connect. I do not have control over the design of dbs that connect to the app which is why I need to account for tables getting renamed.

There is no other way to establish the identity of a database table in PostgreSQL than through the OID (system column tableoid). Creation time is not stored in PostgreSQL.
Relying on the table OID is not only proprietary to PostgreSQL, but also somewhat unsafe, since OIDs can get reused. I recommend that you keep an inventory of your table modifications in a separate database table.


SQL Server fsync Options PostgreSQL has the option to turn off fsync and synchronous commits (http://www.postgresql.org/docs/9.1/static/runtime-config-wal.html). Does SQL Server has this option somewhere?

No, Sql server doesn't let you turn off synchronous writes to the log file.  If you were able to do so, then you would lose ACID compliance.

If this is a "make it go as fast as possible" scenario, then a better approach is to analyze wait statistics to identify what the bottleneck actually is.  Risking data corruption is never a good idea.


MySQL Single Left Join Very Slow SELECT
    contacts.email, emails_stats.id FROM contacts
LEFT JOIN
    emails_stats ON contacts.id = emails_stats.contact_id
WHERE
    contacts.client_code = 121212121212
GROUP BY
    contacts.email;

This query taken 10.2s

Table contacts:

Rows: ~74k; Indexes: primary and client_code

Table emails_stats

Rows: ~20k; Indexes: primary and contact_id

The query explain:

1   SIMPLE  contacts    ref client_code client_code 9   const   2104    Using temporary; Using filesort
1   SIMPLE  emails_stats    index   NULL    contact_id  36  NULL    19394   Using where; Using index; Using join buffer (Block Nested Loop)

SHOW CREATE TABLE contacts:

CREATE TABLE `contacts` (
  `id` char(36) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `client_code` bigint(20) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

SHOW CREATE TABLE emails_stats:

CREATE TABLE `emails_stats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email_event_id` int(11) NOT NULL,
  `contact_id` char(36) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `email_event_id` (`email_event_id`),
  KEY `contact_id` (`contact_id`),
) ENGINE=InnoDB AUTO_INCREMENT=19194 DEFAULT CHARSET=latin1

Expected Result:

email                       id
aaaaaaaaa99@yahoo.com.br    136
bbbbbbbbb99@yahoo.com.br    365
ccccccccc99@yahoo.com.br    765

Because you are grouping by contracts.email, there needs to be an aggregate expression over emails_stats.id, which I've chosen as GROUP_CONCAT:

SELECT
    contacts.email, GROUP_CONCAT(emails_stats.id) FROM contacts
LEFT JOIN
    emails_stats ON contacts.id = emails_stats.contact_id
WHERE
    contacts.client_code = 121212121212
GROUP BY
    contacts.email;

As this lookup is by client_code, to enable this to perform, creating an index on client_code needs to be at the first part of the index. As contacts.email is used by the GROUP BY and SELECT results, putting email at the end of the index will save a secondary lookup of the email, and being in the index, its already sorted and hence grouped together.

CREATE INDEX client_code_email ON contacts(client_code,email)

This will enable the WHERE clause to be fast. In Innodb which is used, the contacts.id is the PK at the end of the key, this is use the JOIN which is ideal.

The JOIN to email_stats is on contract_id which already has an index. email_stats.id is the PK already at the end of this index which gets used in the result set.


I want to change oracle "homedir" how to do? A good link as answer is ok.
I want to change "homedir" or "basedir" of oracle db from /oracle to
/var/oracle.
How to do?
I have set the oracle unix variables($ORACLE_HOME,$ORACLE_BASE)
with the new path,but db doesn't start.
So i reset them to old dir,and make a symlink for now,it work
but how to tell to oracle the new path?

Solution found,following this link

I wrote so a dirty and quick solution
In my case olddir was /oracle and new /var/oracle on aix 71
oracle was working,user created,etc..

#for safe shutdown oracle,is not necessary,but better

#mkdir
mkdir -p /var/oracle/app/oracle/product/12.2.0/dbhome_1;chown -R oracle:oradba  /var/oracle

#clone with tar
tar -zcvf  /var/oracle/Oracle_Home11g_clone.tar.gz . -X ~/exclude.txt
or better  use  rsync
rsync -avP --delete $HOMEOLD/ $HOMENEW

#edit /etc/ora* and set new home

#clone with runinstaller
/oracle/app/oracle/product/12.2.0/dbhome_1/oui/bin/runInstaller -clone -silent -defaultHomeName ORACLE_HOME=/var/oracle/app/oracle/product/12.2.0/dbhome_1 ORACLE_BASE=/var/oracle/app -waitForCompletion

# script from root
su root -
/var/oracle/app/oracle/product/12.2.0/dbhome_1/root.sh

#rsync remaining dir
rsync -avP --exclude app /oracle/* .

#edit all variables on oracle homedir(.profile,.bashrc,etc)

#edit  /etc/filesystems or fstab

#edit orafiles
find . -iname *.ora|xargs grep -lw "/oracle"
vim file.ora..

#test running db with olddir unmounted


Handling differences between MS-SQL and PostgreSQL money datatypes This is a follow-up/ to my previous question Is it possible to insert un-formatted data for money data type in PostgreSQL

I could read money data by typecasting it into numeric
 SELECT '52093.89'::money::numeric

Will it give inconsistent data, rounded etc.. ?

As per ISO standard, the money datatype size is (19,4) but why PostgreSQL returns size as 2147483647? Is this size locale dependent?

Is it a good idea to use the money datatype to store/retrieve locale specific values? If not, when is the money datatype best suitable?

I have to use the money datatype for certain cases. My worry is will I get any problems if I treat money datatype values as decimal values?
I don't bother about locale specific values such as symbol, digit, and decimal grouping etc.. Can I store money datatype values as per lc_monetary settings and reading by typecasting it to numeric?

In Postgres, the cast from money to numeric is exact, immutable and does not round.
In SQL Server the data type MONEY uses 8 bytes to store a values in any currency in the numeric range from -922337203685477.5808 to 922337203685477.5807.
Matches numeric(19,4).
In PostgreSQL the data type money also occupies 8 bytes but the allowed range is -92233720368547758.08 to +92233720368547758.07. Same precision, but different scale by default (without LC_MONETARY setting other than 'C') - only two fractional digits.
Matches numeric(19,2).
But, quoting the manual:

The fractional precision is determined by the database's lc_monetary setting.

I updated the tag info for money accordingly.
As @a_horse commented (repeatedly), money is not defined by the SQL standard.
And it's pretty much consensus around here to stay away from the money data type if you can.


how to select random and unique records from mysql (in php language) I have a question table in my database which consists the following columns

question number which ranges from 1 to 150.
question text
text for 4 options as A,B,C,and D
answer option(either a,b,c or d)

Now i want to make a quiz where there will be 15 questions from these 150 questions and each question will be randomly selected from the pool of these 150 questions and also all these 15 questions must be unique i have used the followig approach but with this approach i am getting random question but there is a possibility of 2 same questions.

$n1=mt_rand(1,150);

$q=mysqli_query($mysqli,"select * from quiz WHERE qno = '".$n1."'");

I have also try to use DISTINCT but with this also I get two or more same questions:-

$n1=mt_rand(1,150);

$q=mysqli_query($mysqli,"select DISTINCT qno,ques,a,b,c,d,ans from quiz where qno='".$n1."' " );

i am using php language

Preliminary: I assume this is your table:

CREATE TABLE questions
(
    question_number INTEGER NOT NULL PRIMARY KEY,
    question VARCHAR(255) NOT NULL,
    answer_a VARCHAR(255) NOT NULL,
    answer_b VARCHAR(255) NOT NULL,
    answer_c VARCHAR(255) NOT NULL,
    answer_d VARCHAR(255) NOT NULL,
    right_answer ENUM ('a', 'b', 'c', 'd') NOT NULL
) ;

You can let MySQL do the hard work for you, and just execute the following SELECT statement, that will sort randomly all your table (i.e.: it is actually shuffling the rows), and just choose the top 5 rows (NOTE: I've used 5 instead of 15 for the sake of simplicity).

The key point: instead of generating a random number from PHP, let the database deal with the random numbers...

SELECT
     question_number, question, answer_a, answer_b, answer_c, answer_d, right_answer
FROM
     questions
ORDER BY
     rand()
LIMIT
     5 ;

(Converting this to PHP code is quite straightforward)

You will get something like:

question_number | question                                             | answer_a     | answer_b     | answer_c           | answer_d                | right_answer
--------------: | :--------------------------------------------------- | :----------- | :----------- | :----------------- | :---------------------- | :-----------
              2 | Which is the colour of the White HOuse?              | Black        | Green        | Blue               | White                   | d
              3 | What's the tallest building on Earth as of 2017?     | Empire State | Burj Khalifa | Eiffel Tower       | Tokyo Skytree           | b
              8 | Does 1 kg of lead weight more than 1 kg of feathers? | Yes          | No           | Depends on gravity | Depends on kind of bird | b
              6 | Which is the third letter of the alphabet?           | a            | b            | c                  | d                       | c
              7 | Which is the first vowel?                            | a            | e            | i                  | o                       | a

Repeating the same query will give you a different set of questions (although some might have already be asked):

question_number | question                                             | answer_a | answer_b | answer_c           | answer_d                | right_answer
--------------: | :--------------------------------------------------- | :------- | :------- | :----------------- | :---------------------- | :-----------
              9 | How many primary colours are there (for a human)?    | 3        | 2        | 6                  | 4                       | a
              5 | How many wheels does a (most common) car have?       | 4        | 6        | 8                  | 10                      | b
              8 | Does 1 kg of lead weight more than 1 kg of feathers? | Yes      | No       | Depends on gravity | Depends on kind of bird | b
              2 | Which is the colour of the White HOuse?              | Black    | Green    | Blue               | White                   | d
              6 | Which is the third letter of the alphabet?           | a        | b        | c                  | d                       | c

When you work with a database, it is normally a good idea to let the database give you the complete answer to your problem, instead of looping in your code.

You can see all the setup and some more executions at dbfiddle here

References:

RAND()
LIMIT


Order BY Customized - Char by Char My target is to sort text columns by a custom sorting.
e.g., ADBC instead of the usual ABCD.

With this custom ordering, rows would be ordered by in this way:

ADAGIO
ABACUS
DICE
BEE

Because D comes before B.

If I'd need to sort texts of one letter that would be easy:

SELECT * FROM my_db
ORDER BY case when text_plain = 'A' then 1
              when text_plain = 'D' then 2
              when text_plain = 'B' then 3
              when text_plain = 'C' then 4
              else 5
         end asc

But text contains multiple characters.
Is this feasible?

I am using Sqlite in this case.

SQLite's C API has functions to create custom collations.

SQLiteStudio allows you to create such collations in any of its supported scripting languages.


Explicitly update a table after sp_tableoption "text in row" advices wanted I have an extremely slow sql server 2000 DB with 8GB, and the cause of this is the 3 ntext columns which are in the more queried table. Table has indexes set and also the full-text ssearch index set for these ntext columns, though when "select ntextcol1, ntextcol2 from table" the query takes up to 5min...crazy! But ofc when one knows which row to fecth, by its id which is indexed its fast!

To try to overcome this, I was trying to use sp_tableoption "text in row", 4000 but according to some Msft Technet reading, this will only be performed on existing records only when they're updated. So I was thinking in force an update in the rows so it would trigger the usage of the new option and hopefully have better performance.

This table as 100000 rows and seems scary stuff to do, so I was looking for some input from you and know if you have done it before, if it's a smooth process if any special advice...

Sounds to me you're missing indexes for the queries your application actually uses. select ntextcol1, ntextcol2 from table takes 5 min is not the point: this is a table scan that retrieves all LOBs. Your application (hopefully...) never issues this query. Pulling ntext (you should be using `NVARCHAR(MAX) btw) in row or pushing them out of row is not what you should be looking at.

Read General Index Design Guidelines and all the related articles in the section (clustered index design guidelines, non-clustered index guidelines etc etc). And learn how to identify the problem queries in your app, the indexes should target those queries: Troubleshooting Performance Problems in SQL Server 2005, Waits and Queues


sqlite database table with 1000's of columns I am using sqlite3 in an application to conduct an alalysis of distances between sets of points.  Distances between these points have been precomputed using dijkstra's algorithim and left me with a table like:

    point | p0    p1    p2  ...  p1500
    ------|--------------------------
    p0    | 0     100   15  ...  X
    p1    | 100   0     10  ...  Y
    p2    | 15    10    0   ...  Z
    p150  | X     Y     Z   ...  0

I will be doing lots of I/O on the table.  For 10s of thousands of records I need to look up a set of points.  Combine with other factors and determine which point is best for that record.  Then repeat.

My question is...  in terms of performance for a database (and sqlite in particular) would it better to leave the table as is or transform the table into a long format?

    aPoint bPoint | dist
    --------------|-----
    p0     p0     | 0
    p0     p1     | 100
    p0     p2     | 15
    p0     p1500  | X
    p1     p0     | 100
    p1     p1     | 0
    p1     p2     | 10
    p1     p1500  | Y
    p2     p0     | 15
    p2     p1     | 10
    p2     p2     | 0
    p2     p1500  | Z
    p1500  p0     | X
    p1500  p1     | Y
    p1500  p2     | Z
    p1500  p1500  | 0

Despite of the database you are using, normally you don't add thousands of fields to a table. Your second model is more 'normalized' ( and it's easy to index ), and should work better ( I'm not an expert, anyway ). My opinion is based on normal limits of databases, that are not meant to work that way, so you're going to hit some inner limit of the engine, or misusing the engine as it was designed for.

You can explore functions and datatypes of more modern versions of mysql, MariaDB and others, that have defined types for points and similar.


Importing Oracle12c database i am using Oracle 12c in debian Jessie
i want to import an old database exported with expdp on oracle 12c
when i try to launch the commande 'impdp' i got those errors :
'ORA-39002: invalid operation'
'ORA-39070: Unable to open the log file'
'ORA-39087: directory name DUMPS is invalid'
Can you help me please?

Looks like your oracle directory doesn't exists. You will need to log into the database, create a directory, and retry your import

export ORACLE_HOME=/path/to/oracle/home
export ORACLE_SID=YOUR_SID
sqlplus / as sysdba
create directory DUMPS as '/path/to/your/dump/files';
exit;

And retry your import. Pending on the user you are using to do the import, you may need to grant read/write access.


Dimension Help - Deciding Fact or Dimension We have the following Dim and Facts:

Customer Dim:  SCD Type 2, Info about the customer ie first purchase date, name, address, etc
Product Dim:  SCD Type 2, about our products

Customer Snapshot Fact:  Monthly Fin facts about the Customer
Product Sales Fact:  Sales by Customer

and will have many more facts that involve the customer Dimension.

We have a legacy DB that was collecting 100's of data fields about a customer and have been asked to DW this data.   There are 100 plus fields related to a customer and may of them are flags that indicate if the customer qualifies for something or not.    The majority of the queries the users will want against any of our fact table may include the filtering and or grouping of these indicators.

The question is should we add 100 plus indicators to our Customer Dim and if not how should the data be structured so this information can be joined with all of our other facts.

Thanks for your help.

Put the indicators on the customer dimension.
This means that any fact table that joins against the customer dimension has access to all of the indicators.  If they are on the dimension then you can trivially make them available to any fact table that links to the customer dimension.
If you just need a fact table with counts of customers that roll up by the dimension attributes, then you can create a 'factless fact table' that just has a single fact - a 'QTY' column with a value of 1.  This allows counts of customers to be grouped by any of the attributes.
If Customer is a slowly changing dimension, consider putting an additional row with -1 in the QTY column into the fact table every time a type 2 change is made.  This should link to the previous version of the dimension, with the current version having an additional row with a 'QTY' of 1.  This allows you to track statistics on changes in customer attributes over time.
A cube can consume this change over time for the counts by implementing a calculated measure that does a running sum in 'QTY' from beginning up to the selected date.  If necessary you could also build a snapshot table.
If the number of attributes and volume of changes becomes unwieldy then you have what Kimball calls a 'Rapidly Changing Monster Dimension' in his first book.  In this case, consider pulling the attributes out into a separate junk dimension that has a row for each distinct combination of values.  You will still want some scaffolding to link this to the actual dimension rows so you can copy the junk dimension key onto any fact tables you have the customer dimension key on.
A cube can consume either structure, so it is less likely to be an issue for the cube so much as an issue for the ETL processing.


Do SQL Server or MYSQL contain JSON supporting functions? Do SQL Server or MYSQL contain JSON supporting functions? If so, then please tell me the name of the functions or where else I can find it?

I am new in this stuff and I want to implement JSON with the databases mentioned above.

SQL Server 2016 will support JSON strings.

Currently with the CTP2 version you can only export a table to JSON string.
But with the release of CTP3 version you can also read and store JSON string in SQL Server tables.

You can check all about how JSON will work with SQL Server on my blog. SQL Server will store JSON in NVARCHAR datatype columns only, unlike XML datatype for XML data.

Edit: With CTP 3.x you can create a Table with NVARCHAR(max) data type column to store JSON text, but make sure to add a CHECK constraint to validate the JSON, like:

CREATE TABLE CustomerOrder (
    BusinessEntityID INT,
    FirstName NVARCHAR(50),
    MiddleName NVARCHAR(50),
    LastName NVARCHAR(50),
    EmailPromotion INT,

    OrderDetailsJSON NVARCHAR(MAX) -- normal column with NVARCHAR datatype
        CHECK ( IsJSON ( OrderDetailsJSON ) = 1 ) -- CHECK Constraint to validate JSON string
)

The official SQL Server team blog also has a series of articles on this topic.


SQL Server connection from iis website: "error: 26 - Error Locating Server/Instance Specified" I've just moved all my databases from one server to another clustered server.  The new SQL Server 2008 R2 (enterprise edition) install is using named instances on a non-standard port.  SQL is running on a Windows Server 2008 R2 Enterprise server.  My web server is running on Windows Server 2008 Enterprise.  The databases are used by three different websites, all hosted on the one server.  After moving the databases I changed the database connection strings in my website, and then checked that they were working ok.

Only one of the sites was working. I changed the connection string on the non-working sites to specify the port number that SQL is listening on.  This got one more site working.  The third site still didn't work.

I tried an iisreset (out of desperation really), and now the first site that was originally working did not work.  Only the second site, that worked with the port number specified, was working.

I disabled the firewall rules between the web server and the database server, and now all three sites work.  But the firewall is disabled, so I can't leave it like that.

I'm at a loss to figure out why the firewall would be preventing one site on a server from connecting to a database, but not another, and why a third site would only connect if the port number was explicitly added to the database connection string.  I'm also confused as to how an iisreset had any effect (to stop the first site from working).

Windows Firewall is turned off
The SQL Browser service is running
SQL is definitely running on the port I think it is running on
TCP and UDP port 1434 and the port number SQL is running on are both opened on the firewall
The old databases were all taken offline, so there's no possibility that the sites were still connecting to the original location somehow
nslookup of the sql cluster from the website server works ok
The sites all work ok when the firewall is disabled, so it has (I assume) got to be firewall related

Any ideas?

Some of the mystery is solved - The reason that some sites could connect and some couldn't is that there was an additional connection string stored in the machine.config file that two of the sites were using.  The site that worked when a port number was specified did not use this connection string, so worked.  The other sites didn't work when port number was specified because I hadn't added the port number to this connection string in machine.config.  Adding the port number to that connection string as well as the ones stored in web.config allowed all the sites to work.

Additionally, the reason that iisreset had an effect is that once IIS knows the port number that SQL is using, it seems to 'remember' the port number, and so it no longer has to rely on using port 1434.  Once I did an iisreset it 'forgot' what port number was used, so had to make a request on port 1434, which for some reason doesn't work.

So my question has gone from a very confusing 'works sometimes' situation to a basic 'cannot communicate on port 1434' situation.


MariaDB is Forgetting Previous Auto Increment IDs I'm using MariaDB 5.5.39, and it seems like it is forgetting the max id of the auto-incremented primary key if I delete all rows from the table and then too much time passes before another row gets inserted. Here's the schema according to HeidiSQL:

CREATE TABLE `submissionqueue` (
    `SubmissionID` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `JSON` TEXT NULL,
    PRIMARY KEY (`SubmissionID`),
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=354;

The original AUTO_INCREMENT was probably 1, HeidiSQL is probably just indicating what the code would be to create the table right now.

I'm using this table as a queue to hold submissions until approval, where the id column is auto incremented.

Later, the queue is processed leaving this table empty. When a new submission occurs, if the table is functioning correctly, a new row is inserted into this table and it receives a new auto increment ID that is higher than the IDs of all previously inserted rows (even though most "previous rows" were deleted long ago).

Over time, more than 600,000 rows have been processed from this queue. So you'd expect the auto increment id to be in the 600,000s.

However, for some time now, the auto increment IDs keep becoming reset. And when I go to process this queue, the auto increment IDs have started over at 1.

It seems like, when I empty that table, and too much time passes before another row is added to it, MariaDB is forgetting the max auto increment ID that was previously in that table and instead the IDs start over at 1.

This is a problem because during processing, any rows deleted are first moved to another table with the exact same schema. Because the auto increment IDs have started over, the program errors out because the ids are no longer unique in respect to all previously processed rows.

For years this program worked flawlessly, but at some point the database started forgetting the correct auto id, starting over at 1 instead.

This table, I'm experiencing this with, has never been dropped. The only thing I do, is remove all the rows that are in the table each time the queue is processed. After this, if I immediately go insert a row, it will received the proper Auto Increment ID (respecting all the previous ids that use to be in the table). Again, however, if too much time passes before an insert occurs, the increment starts over instead of respecting previously existing row's auto IDs.

I'm just using plain old delete statements like:

delete from tableName where id < 600000

No, that is not the guarantee of AUTO_INCREMENT.  It only assures uniqueness.  It does not assure deleted number won't recur.

Old numbers will be resurrected in this scenario (and perhaps others):

DELETE the highest sequence number(s).  (Or TRUNCATE or ROLLBACK, ...)
Restart mysqld (or power failure, ...)
InnoDB, as it opens the table, computes the next id to use as MAX(id)

You need another mechanism, such as

CREATE TABLE seq (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    code CHAR(1) NOT NULL,
    PRIMARY KEY(id),
    UNIQUE(code)
) ENGINE=InnoDB;

INSERT INTO seq (code) VALUE ('a');   -- initialize only

To get next number:
INSERT INTO seq (code) VALUE ('a')
    ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id+1);
then use LAST_INSERT_ID() to retrieve the new number: SELECT LAST_INSERT_ID();

Notes:

This allows multiple sequences, based on code.
The nums will be monotonically increasing (never dup, never decrease).
It will provide consecutive numbers (no gaps).
It will not back-fill.
It will survive crashes; deletes from your table; etc.
If you get a new number inside a transaction, replication could generate the ids not in chronological order.
If you get a new number outside a transaction (autocommit), you could 'burn' the number (create a gap) if certain failures occur.

(There may be something simpler.)


How to set "On Update" for uuid() The syntax to define an update value for timestamp is the following:

CREATE TABLE Example1 (
    ...
    `RowVersion` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ...
);

I can change the datatype from timestamp to GUID colum by:

`RowVersion` CHAR(36) NOT NULL DEFAULT 'uuid()'

But I can not write:

 `RowVersion` CHAR(36) NOT NULL DEFAULT 'uuid()' ON UPDATE 'uuid()',

How am I able to set a new uuid() when the row is updated without create a trigger?

The specification really makes very little sense. Having a "rowversion" is fine and ok. But what makes sense to use for a column that is a "rowversion" is either a timestamp (so we can compare the 2 values in 2 rows to see which is earlier and which is later) or an incrementing (integer or not) value. GUIDs are not incrementing and makes zero sense to be used in this way.

Further, not even the first code sample will work. First because you would need to use the function uuid(), not the string 'uuid()':

`RowVersion` CHAR(36) NOT NULL DEFAULT uuid()

Even in that case though, it wouldn't work because MySQL does not allow anything but constants in the DEFAULT value for column (with one exception, see below). From the documentation, MySQL, CREATE TABLE Syntax:

  The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP or DATETIME column. See ...

Check the answers in the questions: MySQL - Alter table to automatically put in a UUID and How can I get MySQL to use the value returned by a function as a default?, for some ways to put the UUID values in the column.

But in any case, this does not solve the "rowversion" problem. Whether you use triggers, procedures or modify your statements to update the UUID values, I don't see how those can be used as rowversions.


SQL Server Leaked Transactions I have a database accessed by around 50 clients via TDS over TCP which does not seem to be releasing log space.  The number of processes stays around the expected 50, and some of them are quite long lived (>120 days).

The database now has 40 gb in log space (it only has 14 gb data), 39 gb free.  Due to space limitations on the drive, I would like to shrink to something more reasonable (10gb-ish).  When I execute DBCC SHRINKFILE('db_log', 10000), it returns an error that the end of the log is in use.

In order to free access to the end of the log, I attempted to place the database in single user mode with the following:

ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE db SET MULTI_USER
GO

but the script is returning the following message repeated hundreds of times:

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

Which leads me to believe that somewhere, I am leaving some transactions uncommitted.  I am not aware of any process that would intentionally open this many transactions at one time, so I think they must accumulate over time, never being closed.

Question: How do I locate the offending process or script or why is the log not being released?

sys.dm_tran_active_transactions is showing a reasonable 18 transactions with understandable purposes.  sp_who shows only the processes I am aware of.

SQL Server Version:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Apr  2 2010 15:48:46
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Server Version:

Windows Server 2008 R2 x64 - Datacenter
4 vCPUs, 16GB memory, Pass through disk for data and log, OS disk is VHD

on Hyper-V (Windows Server 2008 R2 SP1 x64 Datacenter)
Dual Intel X5650 (6 core, 12 thread at 2.67GHz)
72 GB memory

Hypervisor only has three VMs and does not show high resource use.  SQL Server VM shows ~40% CPU under load and 99% cache hits.

There is a SQL command that will show OPEN transactions. (DBCC OPENTRAN)

http://msdn.microsoft.com/en-us/library/ms182792.aspx

Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database. Results are displayed only if there is an active transaction or if the database contains replication information


Getting previous year values If I had a table structure like this :

id | product name | sales dollars | projection dollars | year

And had data like this :

1 | tv | 334600 | 452000 | 2015
2 | pc | 929492 | 779564 | 2015
3 | tv | 222232 | 432134 | 2014

How can I get results into single row like this :

product name | current year sales dollars | previous year sales dollars

And resulting in this :

tv | 334600 | 222232
pc | 929492 | 0

this is a simple case of a conditional aggregate:

select product_name,
       sum(case when year = 2014 then sales_dollars end) as previous_year_sales,
       sum(case when year = 2015 then sales_dollars end) as this_year_sales
from the_table
group by product_name;


How do SSIS connection strings send passwords? I have a SQL server that pulls data from another SQL server via SSIS. We recently converted the server targeted by SSIS to VM. Since the VM conversion I have been getting "login failed for user '(username)'.". errors and the SSIS package fails to run.
The connection string of the SSIS package indicates the username (which is correct for the target DB), but I do not see how it passes the password to connect to that DB and pull that data. So how does SSIS send passwords if not in the connection string?
The Package Protection Level is set to EncryptAllWithPassword

Windows Authorization only option to run.

The Package protection level matters here as a setting of DontSaveSensitive will remove any sensitive data, like a password, when you save the package. You might enter the password, click validate connection and it's good but as soon as you save, it's wiped out and when you re-open, connections are broken because the password is incorrect (blank)

There are challenges with Package Protection Levels other than DontSaveSensitive. You can encrypt all or sensitive data with user key. That is, the author of the package's Active Directory bits are used to transparently encrypt data. Which is great until that person leaves the organization and the AD account is removed. Hard to unlock something when you're a ghost. Encrypt all or sensitive with password is overkill for a single user and a shared secret among all the developers is no secret at all. So, the advice back in the day was Don' Save Sensitive and use some other means of populating sensitive values. Thus...
Check your expressions. If I have an expression on the Password and the expression is the string "1SecretPassword!" don't save sensitive can't wipe the password out because it's an expression, not a stored value. Semantics, but it's true. I advise people to do the expression stuff in SSIS variables and assign them in properties so instead of seeing "1SecretPassword!", in my packages you would see @[User::Password] which has the password in clear text. Yeah, it's a terrible thing for security, so that's why we had the next thing

Configurations! This allowed us to configure a single element per config entity (Environment Variable or Registry key) or point to a cookie jar of settings (SQL Server table or xml based package configuration file). We had a two phase configuration method in place - the Environment Variable would bootstrap the Config connection string which then pointed at a SQL Server table. As long as the xml/table entity exists, the application of configuration values will just consume them all. Which was nice because you could then add/remove config values without changing code.

But sometimes you would get a package through testing and ready for production and realized you forgot to do any of that. You do not want to go back through the whole testing cycle and approval by the high holy SOX compliance review panel that only meets every other week so what do you do, command line overrides when the package executes. This is why I want to see how the job is defined because what you see in the designer/visual studio might be stomped over at run-time by the parameters defined in the job.

There's a fifth package protection level: Server encryption. I honestly forgot how this works but I think when you deploy a package to the MSDB it strips the other protections and replaces it with its own but it's been nearly a decade since I've dealt with package deployment model in capacity.


GROUP BY on two large joined tables I have two tables:

Person
------
PersonID (PK)
GroupID
DeletedDate

PersonData
----------
PersonID (PK)
FieldID (PK)
Value

The fields are custom-created; hence the schema. I am trying to run a histogram-type query to discover the range of values for a given FieldID together with a count of the times each value occurs. Here is the query:

SELECT pd.Value, count(*)
FROM Person p
INNER JOIN PersonData pd ON p.PersonID=cd.PersonID
WHERE p.DeletedDate IS NULL AND p.GroupID=‘cAPHSWovx9d2yaN’
GROUP BY pd.Value

We use GUIDs for the GroupIDs.

Performance is fine for small groups but degrades rapidly. The group in question has 5 million records in table Person and an order of magnitude beyond that in PersonData. The above query takes an hour to run. It would be acceptable to get it down to a few minutes.

I created an index on PersonData on (PersonID, FieldID, Value) in that order. MySql generally chooses not to use it when I run explain. (Actual results of explain vary based on FieldID.) I also have an index on Person on (GroupID, DeletedDate).

I have two questions:

On the explain I always get a “Using where; Using index; Using temporary; Using filesort” on the Person table. Why does MySql need to sort the Person table before joining to PersonData?
Why does MySql generally decline to use the (PersonID, FieldID, Value) index? Is there a better index I could use? I assumed I needed the PersonID in the index to help with the join.

Edit Here is the relevant portion of the explain:

id    select_type    table    type    key              key_len    ref          rows       Extra
1     SIMPLE         p        ref     deleted_person   67         const,const  3476105    Using where; Using index; Using temporary; Using filesort
1     SIMPLE         pd       ref     person_field_val 62         p.PersonID   10         Using index

Edit 2 Forgot the GROUP BY in the query above.

Sorry, but you have designed yourself into a corner.

Strike 1:  EAV (key-value) schema sucks when the tables get large.  Further discussion and possible workarounds:  http://mysql.rjweb.org/doc.php/eav

Strike 2:  GUIDs (and UUIDs, MD5s, etc) suck as KEYs because they are so random.  Further discussion and a possible (but unlikely) workaround:  http://mysql.rjweb.org/doc.php/uuid

Your best choice may be to keep buying RAM so that everything can be cached.  And/or switch from spinning drives to SSDs.  Of course, neither hardware solution scales well.

Software solution involves a major rework of the schema and application code.  That is beyond the scope of this forum, other than to point you at those two blogs.

As for your specific questions:

  On the explain I always get a “Using where; Using index; Using temporary; Using filesort” on the Person table.  Why does MySql need to sort the Person table before joining to PersonData?

What you are seeing is bogus.  EXPLAIN likes to put those flags on the first line, even though they might apply to some other line.  If you have a new enough version of MySQL, do EXPLAIN FORMAT=JSON SELECT ...; it should spell out things correctly.

  Why does MySql generally decline to use the PersonID/FieldID/Value index? Is there a better index I could use? I assumed I needed the PersonID in the index to help with the join.

I need to see SHOW CREATE TABLE.  But here is a guess...  If the WHERE clause matches more than ~20%, it will choose to do a table scan because it is likely to be faster.  Another guess:  You have PRIMARY KEY(person, field) and INDEX(person, field, value)??  And you are using InnoDB??  The PK includes all columns and is sorted by (person, field).  That's identical to the secondary key!  So, the optimizer may as well flip a coin to decide between them.

Anyway, it says "Using index"; that about as efficient as it gets.


will table partitioning and compression help heavily used OLTP database? We have a very big table which is over 300gb with over half billion rows in it. This table is used for audit purpose for all critical applications. A lot of inserts are happening per seconds. Due to heavy blocking, we can't purge data while the database is online.
My boss is saying by partitioning and table compression will help the disk space and overall performance. He is from OLAP DW area. But I highly doubt this will work in OLTP. How do I convince him this will not work? Any articles?

Sorry to disappoint, but your boss is right on target. Some of us BI folks know about DB optimization, at least a little. =) As with any major architectural change you need to test and adapt appropriately to your unique environment, workload, servers etc.

  Due to heavy blocking, we can't purge data while the database is
  online.

Table partitioning is exceptionally helpful in many environments, especially those with large data sets. Not only can you avoid locking issues and improve query performance, but you can reduce log impact by using Truncate after swapping out your partitions when purging old records.

See this free training by Kendra Little with Brent Ozar Unlimited for a detailed guide about table partitioning in SQL Server which, incidentally, is focused on OLTP environments.

As for data compression, see this SQL Server Customer Advisory Team white paper for data compression implementation. You will save disk space, and reduce I/O at the cost of CPU usage... This trade-off is something that you need to plan and test for before blindly implementing.


Database Design for Preserving Values and Additional columns My colleague and I are having some trouble coming up with a database design, and I am doing my best to stay away from EAV for now.

We have an entity called Preparation that stores configuration data for machines. There are two types of processes per machine, let's call these process1 and process2, and a machine can only have one process. Both processes share a lot of attributes so setting up one table seemed logical.

However process2 has a lot of extra data, and even sub-categorizes itself with process2a, processes2b that is specific to those sub categories (process1 only has one extra attribute for now). So that one table will have nulls in columns that are not specific to its process.

Splitting the table for each process was another option. However, we need to preserve any data change and splitting the tables will bring more work (most all transactions will be INSERTS with a timestamp into a history table, followed by an UPDATE on the live table if they are updating a record). For example, if a machine changes from process1 to process2, then a record will have to be inserted in the history table from process1, deleted from process1 and then inserted into process2. We are using the standard edition of SQL Server, so no CDC to help with logging changes.

I am trying to keep these pretty relational and I would hate to create one table with expected NULLS (maybe create SPARSE columns if the NULL percentage is high?).

I am hoping someone has experience with this type of situation. Thank you.

Which fields will you need to do any of the following on?

sorting
filtering
counting
grouping
type checking

If there are some fields that you simply want to store for viewing or what-not, then you don't need to put them in their own columns. You could store them as JSON or XML or some other serialized form.

But if you need to do the above on your fields, then you almost certainly want real database columns for them. And by real database columns I mean not EAV or LOBs.

If it sounds like a lot of work, then consider using Hibernate (with inheritance) to generate your database schema for you. And use Hibernate Envers to handle change capturing.


Does SSRS lock the table when querying? My senior DBA told me that SQL Query execution by default doesn't lock the table.

I was having some issues with my SQL Server Reporting Services (SSRS) report which seems to be getting some issues with locking and getting some errors.

I did some Googling but fell short of finding anything.

Do SSRS reports lock the tables that are being queried?

Is there any MSDN documentation that document this behavior specifically?

Short answer: No

Longer...

SQL Server doesn't know that it is SSRS sending it a query. So the query from SSRS will run like any other query.

It's more likely be that the query optimiser decides to use a table lock for the SSRS query. of course, it could be a different problem, but that's a different question


Does this break normalization? I have an order table, and in the order table I have a contact_id.

Now, each contact is linked to an account. For my order table, should I have both contact_id and account_id?

If I wanted to say, list all orders for account ABC, I could just look through all orders where account_id = "ABC's id"

If I had just the contact_id I would have to go through all the contacts and then pull orders based on each contact_id... this seems kind of like a pain in the neck.

I don't know if this is really a normalization question, but the easy way seems a little redundant, but I'm not 100% sure. What is the accepted best practices for doing something like this?

Thanks in advance.

There are different levels of normalization.  If your business logic is set up where each contact has an associated account (but more than one contact can be in an account), then there is no reason to put the account ID in the order table because you can get it by going back to the contact table.  In other words, account ID in the contact table is a foreign key.


How does PostgreSQL physically order new records on disk (after a cluster on primary key)? Need to know how PostgreSQL orders records on disk.  In this case, I would like to take advantage of index combination as stated in the docs, which as I understand uses bitmaps to get matching rows and returns them according to their physical location.  The table in question has been clustered by its primary key.

As I understand it, PostgreSQL does not automatically continue doing clustering after a clustering is finished (although it does remember that it clustered according to a certain index).  Now, since this is the primary key, I wonder if the physical storage order would be according to that (which if true I would like to use to our advantage for a specific query).

In summary, how does PostgreSQL order its new records, especially after clustering?

Thanks very much!

Rows in postgresql have no fixed order. Not only are records placed where ever there is free space records can also move around. This is because when a row is updated a new version of the row is created in a new location while the old version continues to live in it's old location until it's removed by vacuum.

A CLUSTER operations sorts all rows but does indeed not affect how postgresql add's rows. So the data won't stay sorted. However postgresql keeps statistics among which is the correlation for each column between the order of the rows in the tables and the sorted order of that column. So the planner can still optimize it's plan based on the statistics that tell it the table is still mostly sorted even if some rows have been added after a cluster operation (or have been moved by updates).


Best Isolation Level to avoid deadlocks in Sql Server I need execute un update statement over an sql server table, this table is used by another process at the same time. because that sometimes deadlocks ocurs.
wich Isolation Level do you recomend for L1 and L2 to avoid deadlocks?

p1:

set transaction level L1
    delete from t1 where id=2
    select * from t1 where id=3

p1:

set transaction level L2
    delete from t1 where id=3
    select * from t1 where id=2

I would recommend to rewrite this code to avoid the deadlocks.  In your case:

p1:

set transaction level L1
    delete from t1 where id=2
    select * from t1 where id=3

p2:

set transaction level L2
    select * from t1 where id=2
    delete from t1 where id=3


Foreign key not working This is a very basic foreign Key issue, I presume. Unfortunately I am not a dba so it is a bit baffling to me.

I have two tables, Report and Request.

The Report table has a primary key of [RP_ID], and Request has a foreign key for field [RQ_RP_ID]. I can insert a record into the Report Table and it's primary key will have a value of 1. When I then try to enter a record into Request Table and enter the number 1 into field [RQ_RP_ID] I get an error message:

  The INSERT statement conflicted with the FOREIGN KEY constraint
  "FK_REQUEST_REPORT". The conflict occurred in database "THEDATABASE", table "dbo.REPORT", column 'RP_ID'. The statement has been terminated.

From my naive knowledge of what I know about Foreign Keys and what I am using it for is simply to keep my data clean by not allowing a report request to come through my app without a valid report id. if this is not what I thought it was please explain otherwise please explain why it is that this is not working as I expected.

Request Table

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[REQUEST](
    [RQ_ID] [int] IDENTITY(1,1) NOT NULL,
    [RQ_NOTE] [nvarchar](max) NULL,
    [RQ_DATE] [datetime] NOT NULL,
    [RQ_BY] [nvarchar](50) NOT NULL,
    [RQ_TYPE] [nvarchar](50) NOT NULL,
    [RQ_RECORD_ID] [nvarchar](50) NULL,
    [RQ_RP_ID] [int] NOT NULL,
    [RQ_MANAGER] [nvarchar](50) NOT NULL,
    [RQ_RESPONSE_DATE] [datetime] NULL,
    [RQ_RESPONSE_ACTION] [nvarchar](50) NULL,
    [RQ_REPORTING_NAME] [nvarchar](50) NULL,
    [RQ_REPORTING_ACTION] [nvarchar](50) NULL,
    [RQ_IS_CLOSED] [bit] NULL,
    [RQ_CLOSED_DATE] [datetime] NULL,
 CONSTRAINT [PK_REQUEST] PRIMARY KEY CLUSTERED
(
    [RQ_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[REQUEST]  WITH CHECK ADD  CONSTRAINT [FK_REQUEST_REPORT] FOREIGN KEY([RQ_ID])
REFERENCES [dbo].[REPORT] ([RP_ID])
GO

ALTER TABLE [dbo].[REQUEST] CHECK CONSTRAINT [FK_REQUEST_REPORT]
GO

ALTER TABLE [dbo].[REQUEST] ADD  CONSTRAINT [DF_REQUEST_RQ_DATE]  DEFAULT (getdate()) FOR [RQ_DATE]
GO

ALTER TABLE [dbo].[REQUEST] ADD  CONSTRAINT [DF_REQUEST_RQ_TYPE]  DEFAULT (N'OMISSION') FOR [RQ_TYPE]
GO

ALTER TABLE [dbo].[REQUEST] ADD  CONSTRAINT [DF_REQUEST_RQ_MANAGER]  DEFAULT (N'MANAGER_NOT_FOUND') FOR [RQ_MANAGER]
GO

ALTER TABLE [dbo].[REQUEST] ADD  CONSTRAINT [DF_REQUEST_RQ_IS_CLOSED]  DEFAULT ((0)) FOR [RQ_IS_CLOSED]
GO

Report Table

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[REPORT](
    [RP_ID] [int] IDENTITY(1,1) NOT NULL,
    [RP_NAME] [nvarchar](50) NOT NULL,
    [RP_SHORT_DESCRIPTION] [nvarchar](200) NOT NULL,
    [RP_FULL_DESCRIPTION] [nvarchar](max) NOT NULL,
    [RP_IS_ACTIVE] [bit] NOT NULL,
    [RP_DATE_CREATED] [datetime] NOT NULL,
    [RP_DATE_DISCONTINUED] [datetime] NULL,
    [RP_REQUESTED_BY] [nvarchar](50) NOT NULL,
    [RP_IS_PDP_SPECIFIC] [bit] NOT NULL,
 CONSTRAINT [PK_REPORT] PRIMARY KEY CLUSTERED
(
    [RP_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[REPORT] ADD  CONSTRAINT [DF_REPORT_RP_IS_ACTIVE]  DEFAULT ((1)) FOR [RP_IS_ACTIVE]
GO

ALTER TABLE [dbo].[REPORT] ADD  CONSTRAINT [DF_REPORT_RP_DATE_CREATED]  DEFAULT (getdate()) FOR [RP_DATE_CREATED]
GO

ALTER TABLE [dbo].[REPORT] ADD  CONSTRAINT [DF_REPORT_RP_REQUESTED_BY]  DEFAULT (N'SOMENAME') FOR [RP_REQUESTED_BY]
GO

ALTER TABLE [dbo].[REPORT] ADD  CONSTRAINT [DF_Table_3_RP_PDP_SPECIFIC]  DEFAULT ((0)) FOR [RP_IS_PDP_SPECIFIC]
GO

Fixed it I just noticed the SQL Management Studio did something crazy and assigned the foreign key to itself

I corrected with this

ALTER TABLE [dbo].[REQUEST]  WITH CHECK ADD  CONSTRAINT [FK_REQUEST_REPORT] FOREIGN KEY([RQ_RP_ID])
REFERENCES [dbo].[REPORT] ([RP_ID])
GO


How does security context propagate between schemas within a database? We have a SQL Server 2008 database that contains two schema's.  The owners of these schema's have been granted full SELECT permissions to all tables within each others ownership.

I have created a user ID and have granted it access to only SQL Views created in both of these schema's but not the underlying tables.  In the example below I've created two views, one in each schema, that select data from one table in one of the schema's.

If I login as the schema owner (either testdata or testcntrl) it works regardless of the schema I execute the view from.

If I execute the View as my new user in the schema that the same table exists in (in the example testdata) the select is successful, but if I try from the other schema (testctrl) the select fails giving me the following permission error: The SELECT permission was denied on the object 'UserData', database 'DEV_DATABASE', schema 'TESTDATA'..

How does security propagate between schema's within a single database?  Why is this successful from one schema but not the other?

Here is a rough example of what I am trying to do, please let me know if I can explain this better, as for Im not remotely close to a dba.

USE [DEV_DATABASE]

-- Create tables and insert dummy data
CREATE TABLE [DEV_DATABASE].[TESTDATA].[UserData]
    (
        COL1 CHAR(10),
        COL2 CHAR(10),
        COL3 CHAR(10),
        COL4 CHAR(10),
        COL5 CHAR(10)
    );

 INSERT INTO [DEV_DATABASE].[TESTDATA].[UserData] VALUES ('1','2','3','4',5');
 INSERT INTO [DEV_DATABASE].[TESTDATA].[UserData] VALUES ('11','22','33','44',55');

CREATE TABLE [DEV_DATABASE].[TESTCTRL].[ControlData]
 (
        CTRL1 CHAR(10),
        CTRL2 CHAR(10),
        CTRL3 CHAR(10),
        CTRL4 CHAR(10),
        CTRL5 CHAR(10)
    );

 INSERT INTO [DEV_DATABASE].[TESTCTRL].[ControlData] VALUES ('a','b','c','d',e');
 INSERT INTO [DEV_DATABASE].[TESTCTRL].[ControlData] VALUES ('aa','bb','cc','dd',ee');

-- Create views
CREATE VIEW [TESTDATA].[DATA_TEST_VIEW] AS
SELECT
 COL1, COL2, COL3
FROM  [TESTDATA].[UserData]

CREATE VIEW [TESTCTRL].[CTRL_TEST_VIEW] AS
SELECT
 COL1, COL2, COL3
FROM  [TESTDATA].[UserData]

-- permissions
DENY SELECT ON [TESTDATA].[UserData] to JOEUSER;
GRANT SELECT ON [TESTDATA].[DATA_TEST_VIEW] to JOEUSER;
GRANT SELECT ON [TESTCRL].[CTRL_TEST_VIEW]  to JOEUSER;

EXECUTE AS USER = 'JOEUSER';
-- tests
SELECT * FROM [TESTDATA].[UserData];
-- fails as expected

SELECT * FROM  [TESTDATA].[DATA_TEST_VIEW];
-- passes as expected

SELECT * FROM  [TESTCRL].[CTRL_TEST_VIEW];
-- fails, and should pass?

REVERT;

Error message received:

SELECT * FROM  [TESTCRL].[CTRL_TEST_VIEW];
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'UserData', database 'DEV_DATABASE', schema 'TESTDATA'.

Thanks to the OP's clarification of the error, I'm going to post my comment as a suggested answer.

In SQL Server, permissions are checked only when ownership changes.

Consider the following tables:

Table SchemaA.Table1, owned by Alice.
View SchemaA.View1, owned by Alice, depends on Schema1.Table1.
View SchemaB.View1, owned by Bob, depends on Schema1.View1.

Consider these situations:

Alice attempts to select from SchemaA.Table1:

No change in ownership so permissions are not checked on the table. Alice is allowed access.
Alice attempts to select from SchemaA.View1:

No change in ownership so permissions are not checked on the view or on the table. Alice is allowed access.
Bob attempts to select from SchemaA.Table1:

There is a change in ownership on SchemaA.Table1 (Bob is attempting an operation on Alice's object) so Bob's permissions are checked on SchemaA.Table1.
Bob attempts to select from SchemaA.View1:

This is a change in ownership on SchemaA.View1 (Bob is attempting an operation on Alice's object) so Bob's permissions are checked on SchemaA.View1. There is no change in ownership from SchemaA.View1 to SchemaA.Table1 so Bob's permissions are not checked on SchemaA.Table1.
Bob attempts to select from SchemaB.View1.

There is no change in ownership on SchemaB.View1 (Bob is attempting an operation on Bob's object) so permissions are not checked on SchemaB.View1. There is a change in ownership from SchemaB.View1 to SchemaA.View1 so Bob's permissions (not Alice's) are checked on SchemaA.View1. There is no change in ownership from SchemaA.View1 to SchemaA.Table1 so Bob's permissions are not checked on SchemaA.Table1.

So, in the OP's sitation, the view [TESTCTRL].[CTRL_TEST_VIEW] depends upon the table [TESTDATA].[UserData]. The view and the table have different owners so there is a change in ownership (something some SQL Server books call a "break in the ownership chain"). To query the view, JoeUser needs to be granted the appropriate permissions to both the view and the table.


How to read an excel file from a network machine using SQL? I am trying to import some excel file data into a SQL table. I thought of using OPENROWSET for this. The issue is that the csv file is location in a different machine in the same network. How do I read this excel file? If this cannot be done using OPENROWSET, are there any other alternatives? Please advice.Please note that I cannot go with Linked servers as I don't have the privileges to client's machine to install relevant drivers.

The SQL Server Import and Export Wizard offers the simplest method to create a Integration Services package that copies data from a source to a destination.

This will create a package for you, which you can schedule via SQL agent job as explained in Scheduling SSIS Package Execution in SQL Server Agent, as per the desired refresh frequency.

Also, refer to this 10 steps package creation for importing data from excel to SQL table as described by Andy Moving Data From Excel to SQL Server - 10 Steps to Follow

This article uses SSIS 2012, the latest version at the time of writing, but you won’t notice many differences if you’re using 2008 or 2005.


Best practice try/catch throw and XACT_ABORT sql server I've previously worked with software that generated ETL scripts for us. The scripts have the following structure, but this appears to run contrary to what I've read in msdn. Can anyone offer any thoughts on the following structure?

SET XACT_ABORT OFF

SET NOCOUNT ON

BEGIN TRY

'DO STUFF'

BEGIN CATCH

SET @return_msg = 'procedure name FAILED'

  + '. Step ' + CONVERT(VARCHAR,ISNULL(@v_step,0))

  + '. Error Num: ' + CONVERT(VARCHAR,ISNULL(ERROR_NUMBER(),0))

  + '. Error Msg: ' + ERROR_MESSAGE()

  SELECT @return_msg

END CATCH

IF XACT_STATE() <> 0

  BEGIN

   ROLLBACK TRANSACTION

  END

  RETURN 0

END

To implement effective try catch with xact_state kindly follow this msdn link

SET XACT_ABORT on

SET NOCOUNT ON

BEGIN TRY

'DO STUFF'
End Try
BEGIN CATCH

SET @return_msg = 'procedure name FAILED'

  + '. Step ' + CONVERT(VARCHAR,ISNULL(@v_step,0))

  + '. Error Num: ' + CONVERT(VARCHAR,ISNULL(ERROR_NUMBER(),0))

  + '. Error Msg: ' + ERROR_MESSAGE()

  SELECT @return_msg

-- Test XACT_STATE for 0, 1, or -1.
    -- If 1, the transaction is committable.
    -- If -1, the transaction is uncommittable and should
    --     be rolled back.
    -- XACT_STATE = 0 means there is no transaction and
    --     a commit or rollback operation would generate an error.

    -- Test whether the transaction is uncommittable.
    IF (XACT_STATE()) = -1
    BEGIN
        PRINT 'The transaction is in an uncommittable state.' +
              ' Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;

    -- Test whether the transaction is active and valid.
    IF (XACT_STATE()) = 1
    BEGIN
        PRINT 'The transaction is committable.' +
              ' Committing transaction.'
        COMMIT TRANSACTION;
    END;

END CATCH

  RETURN 0


Redundancy of information and algorithm to do lossless decompositions Let be a relation R(NameFile, Size, Directory, DateCreation,HourCreation,Login,AutAccess,DateAccess,TypeAcces

F={NF,D→S;
   NF, D →DC;
   NF, D→HC;
   L,NF,D→A;
   L,NF,D,DA,HA→TA;
}

  Part of the instances are given in the following array:

Does the relation create information redundancy?
What would a decomposition of R without loss of information and without loss of dependency be?

I don't think so because it seems that the information given by the dependencies seem to be unique.

I know there is algorithm to decompose R without loss of information or without loss of dependency but I don't know them

Does the relation create information redundancy?

Yes, for instance you have the information on the file (name, size, data creation, etc.) repeated for different accesses to the same file.

  What would a decomposition of R without loss of information and without loss of dependency be?

The following decomposition is both in Third Normal Form and in Boyce-Codd Normal Form and is without loss of data and loss of dependencies. It can be obtained by applying both the “analysis algorithm” to produce the BCNF, and the “synthesis algorithm” to produce the 3NF, but note that in general only the latter is guaranteed to avoid any loss of dependencies:

R1 < (D DC HC NF S),
{ D NF → DC
D NF → HC
D NF → S } >

R2 < (A D L NF),
{ D L NF → A } >

R3 < (D DA HA L NF TA),
{ D DA HA L NF → TA } >

Any good database book will describe both the algorithms.


Can SQL Express be used as a Log Shipping monitor server? I'm keen to setup a "Monitor Server".

We have SQL 2008 R2 Enterprise edition on both primary and secondary servers.

Can we use SQL Express in monitoring server setup for licencing purposes?

No, log shipping is not supported on SQL Express 2008 R2, even as the monitor.

The official MSDN page does not state this explicitly, but I found this case where someone tried to use Express and got an error.

If you have either a Workgroup or Web edition licence lying around unused, those support what you need, as well as Standard (and higher).


Move result of a query from one server to another table on another server What are my options in moving result of a query in one server to another table on another server? Data contains ~400.000 rows so it won't be a quick one. (Both of them are SQL Server 2008 R2)

Thanks all.

The simplest method  which  we use is something like below

Create a table on destination server using the scripts generated from the table on the source server by right click and going through option of Tasks/Generate scripts.
Then run the below T-SQL to upload the data from source table to destination.

INSERT INTO dbo.YourTableNameHere
SELECT *
FROM [SourceServer].[SourceDatabase].dbo.YourTableNameHere

For large data movement, refer to below discussion and answer for same:

Copy a large data from sql query result


SSRS Subscription Error: "Failure sending mail: Report processing has been canceled by the user. Mail will not be resent." On the report Subscriptions page, under "Status" for Last Run, it has the following error:

  Failure sending mail: Report processing has been canceled by the user. Mail will not be resent.

It's a subscription, no person canceled this. Maybe there is a problem with a service.

I can run the report fine right now.
The only log I can find is here:

  C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles

There are two lines for the report:

  notification!WindowsService_508!d24!02/10/2014-05:45:05:: i INFO: Handling subscription 75b94483-618d-4463-b6c0-90916b92cb6 to report ReportName, owner: domain\user, delivery extension: Report Server Email.
  library!WindowsService_508!d24!02/10/2014-05:45:05:: i INFO: RenderForNewSession('/Reports/ReportName')

These lines don't indicate an error occured.
I've been looking through all of the logs I could find and I can't find any sort of description as to what would have caused this.

I would like to find out where this information is logged, so I can create a job that looks for messages like this to alert users of a failed report.

I would also like to know where the logs are in case I do need to troubleshoot something vague like this.

I really would appreciate any help :)

You sometimes see this error when the report is cancelled due to a timeout. You can check the current timeout setting in Report Manager > Site Settings.


Allow access to enter data through forms, but not full database access Using Access 2013 or Sharepoint 2013 is it possible to give someone access to data entry forms that work with a back-end database whether Access database or Sharepoint SQL.

But from everything I've looked at I can't find a way to separate someone from entering data and viewing all data?

For example in Access even if I split the database to a back end, it still has all the tables linked, and fully accessible from the "front end" this seems to be needed to allow Write access, it seems that you can have no access, read-only access and Read/Write access but no write-only access (this might be where I am wrong).

I don't want the people entering data to see the back end data (regardless if they are on Sharepoint or in access) and certainly do not want them to be able to modify the database directly (they should only be able to enter data via a form that can be validated before entered.)

Now I come from a Windows Forms and ASP.NET MVC background. I am used to hosting a SQL server and simply not writing code that pulls data from the database, and if I did I could set parameters on what data they could access. I could also set up server-side validation based on users and groups.

I can't seem to find any documentation on how to do this in either Access or on Sharepoint. I have read every article I could find on these topics via google and Support.Office.com for starters:

Introduction to integrating data between Access and a SharePoint site

Ways to share an Access desktop database

SharePoint Online environment

and a multitude of other articles both on TechNet and various third-party sites. While some do touch base on user access rights, they seem to allow only access to a specific site or database, not parts of, as my needs are allowed access to a way to enter data but not access to the data.

The client I am working with is addiment on using Access and Sharepoint. And before telling them I can't limit a user to data entry only, I wanted to check here first.

I did read a site that stated an issue with Access 2013 and Sharepoint is

  Security setup is basic and somewhat limited. Item (or row) level security is not possible and edit access appears to allow users to edit information across all tables – not just one.

this was found here: http://blogs.perficient.com

but this seems to state that when you give someone table access they have all tables, and NOT that you can't restrict table access.

It does seem to state however that the only true options are HTML or developing something stand alone in Visual Studios.

Use stored procedures, which can validate input before issuing the SQL commands. The user can be given rights to the stored procedure without any other access.