How to convert rows values into different columns based on the values in a Column in SQL Server 2012 Scenario:

VendorID | OPTFIELD   |  VALUE  |   VDESC
---------+------------+---------+---------------------
7ELEVEN  | CHANNELAP  |   03    | Independents
7ELEVEN  | CUSTGROUP  | Metcash | Metcash
7ELEVEN  | SUBCHANNEL |   ING   | Independent Grocery

Solution Required:

Vendor_ID | level_2 |  level_2_desc| level_3 |         level_3_dec | level_4 | level_4_desc
----------+---------+--------------+---------+---------------------+---------+--------------
7ELEVEN   |    03   | Independents |   ING   | Independent Grocery | Metcash |      Metcash

So I just want to convert the rows into columns depending on a particular scenario which is based on the value of one of the Columns(OPTFIELD)

Example:
 - When OPTFIELD ='CHANNELAP' then results corresponding to 'CHANNELAP' that is Value and VDESC would appear in different columns as VALUE in level_2 and VDESC in level_2_desc

So, values in OPTFIELD columns is the condition on which VALUE and VDESC should be moved to different Columns.

IF 'OPTFIELD' = 'CHANNELAP'  then level_2(Value) and level_2_desc(VDESC)
IF 'OPTFIELD' = 'CUSTGROUP'  then level_3(Value) and level_3_desc(VDESC)
IF 'OPTFIELD' = 'SUBCHANNEL' then level_4(Value) and level_4_desc(VDESC)

So, the final result will be 1 row for VendorID = '7ELEVEN'

VendorID | level_2 | level_2_desc |level_3 |level_3_desc |level_4 |        level_4_desc
---------+---------+--------------+--------+-------------+--------+--------------------
7ELEVEN  |      03 | Independents |Metcash |     Metcash |    ING | Independent Grocery

This is a typical case of a Pivot Table.

You can do it in standard SQL using the following query:

SELECT
    VendorID,
    max(case when OPTFIELD = 'CHANNELAP'  then VALUE end) AS level_2,
    max(case when OPTFIELD = 'CHANNELAP'  then VDESC end) AS level_2_desc,
    max(case when OPTFIELD = 'CUSTGROUP'  then VALUE end) AS level_3,
    max(case when OPTFIELD = 'CUSTGROUP'  then VDESC end) AS level_3_desc,
    max(case when OPTFIELD = 'SUBCHANNEL' then VALUE end) AS level_4,
    max(case when OPTFIELD = 'SUBCHANNEL' then VDESC end) AS level_4_desc
FROM
    t
GROUP BY
    VendorID
ORDER BY
    VendorID ;

Assumption: (VendorID, OPTFIELD) is UNIQUE (and probably, the PRIMARY KEY of the table). This way, the max function will actually take the only actual value.

MS SQL server also has a specific PIVOT statement, but the syntax to use is a bit awkard when you want to have two different columns from each row:

SELECT
    VendorID,
    [CHANNELAP]   AS level_2,
    [dCHANNELAP]  AS level_2_desc,
    [CUSTGROUP]   AS level_3,
    [dCUSTGROUP]  AS level_3_desc,
    [SUBCHANNEL]  AS level_4,
    [dSUBCHANNEL] AS level_4_desc
FROM
(
   SELECT
       VendorID, OPTFIELD, [VALUE]
   FROM
       t
   UNION
   SELECT
       VendorID, 'd' + OPTFIELD, [VDESC]
   FROM
       t
) AS SourceTable
PIVOT
(
    max(VALUE)
    FOR OPTFIELD IN (CHANNELAP, CUSTGROUP, SUBCHANNEL, dCHANNELAP, dCUSTGROUP, dSUBCHANNEL)
) AS PivotTable;

dbfiddle here

NOTE: I am not sure which of the queries is more efficient. You should experiment yourself with your actual data. The PIVOT syntax of MS SQL Server is probably more efficient when you're working with just one column to pivot. But your case is not exactly the one this statement was intended for (AFAIK).

Check also

Convert Rows to columns using 'Pivot' in SQL Server, for the "typical" PIVOT case;
PIVOT on two or more fields in SQL Server, although the case is not exactly yours.
TSQL PIVOT multiple columns, which is very close to your case, and offers a slightly different alternative


Trying SQL query with subquery in WHERE clause For a message system we would like to know if a conversation allready exists in the database. The user will then be posting to the existing conversation instead of a new one.

Now, we have linked all participants to conversations. So on creating a new one, we count the number of people marked as participants (included current user (:totalUserCount)), en then would like a query to check if a conversation with x participants and all marked user(name)s exist before creating a new conversation.

After a few attempts we have a query that executes, but the result is not correct.

SELECT `app_company_user_cons`.*
FROM (`app_company_user_cons`)
LEFT OUTER JOIN `link_company_user_cons_company_users` link_company_user_cons_company_users ON `app_company_user_cons`.`id` = `link_company_user_cons_company_users`.`company_user_conversation_id`
LEFT OUTER JOIN `app_company_users` app_company_users ON `app_company_users`.`id` = `link_company_user_cons_company_users`.`company_user_id`

WHERE :totalUserCount = (SELECT
    COUNT(*)
    FROM (`app_company_users`)
    LEFT OUTER JOIN `link_company_user_cons_company_users` link_company_user_cons_company_users ON `app_company_users`.`id` = `link_company_user_cons_company_users`.`company_user_id`
    WHERE link_company_user_cons_company_users.company_user_conversation_id = app_company_user_cons.id
)
AND `link_company_user_cons_company_users`.`company_user_id` =  :currentUserID
AND `app_company_users`.`name` =  :otherUserID2
AND `app_company_users`.`name` =  :otherUserID3
AND `app_company_users`.`name` =  :otherUserIDn
LIMIT 1

The EXPLAIN function shows me:

+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+
| id | select_type        | table                                | type   | possible_keys | key     | key_len | ref                                                               | rows | Extra                                               |  |
+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+
| 1  | PRIMARY            | NULL                                 | NULL   | NULL          | NULL    | NULL    | NULL                                                              | NULL | Impossible WHERE noticed after reading const tables |  |
+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+
| 2  | DEPENDENT SUBQUERY | link_company_user_cons_company_users | ALL    | NULL          | NULL    | NULL    | NULL                                                              | 2    | Using where                                         |  |
+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+
| 2  | DEPENDENT SUBQUERY | app_company_users                    | eq_ref | PRIMARY       | PRIMARY | 4       | de1210mo_CRM.link_company_user_cons_company_users.company_user_id | 1    | Using where; Using index                            |  |
+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+

Not sure if we are missing something. Any ideas on how to solve this?
Thanks in advance!

(PS: Our app adds AND 'app_company_users'.'name' = for all participants in an array)

We have a solution, no idea if it is legit ^^

SELECT
    muc . * ,
    (
        SELECT COUNT( * )
        FROM users AS u
        LEFT OUTER JOIN link_users_conversations luc ON u.id = luc.user_id
        WHERE luc.user_conversation_id = muc.id
    ) readers
FROM user_conversations AS muc
LEFT OUTER JOIN link_users_conversations mluc ON muc.id = mluc.user_conversation_id
WHERE mluc.user_id = :currentUserID
/* Check for all 'readers' if a relation exists */
AND EXISTS (
    SELECT *
    FROM link_users_conversations AS c0
    WHERE c0.user_id = :participantID0
    AND c0.user_conversation_id = muc.id
)
AND EXISTS (
    SELECT *
    FROM link_users_conversations AS cn
    WHERE cn.user_id = :participantIDn
    AND cn.user_conversation_id = muc.id
)
HAVING readers = :totalReaders /* Total readers could be higher while all EXISTS pass. */
LIMIT 1

I simplified names in this example.

So it mimics behaviour as seen at Facebook when starting a new Chat conversation. It will search the database if any other conversation has exactly (EXACTLY!) the same participants/readers/followers, if one exists, the new message will be posted to the existing conversation instead of creating a new one.

(FYI: The SQL provided is to check for existing conversations only. Creating messages and new conversations need other functions.)

If someone knows of a better way to do this check, please comment :)


Should I drop MS_DataCollectorInternalUser in msdb? From SQL Server 2008, there is a user in msdb called MS_DataCollectorInternalUser. This user has no login associated with this and can be considered as a orphan user.

It's a security best practice to drop orphan users.

The MS_DataCollectorInternalUser cannot just be dropped using DROP USER TSQL command because it has granted a database permission IMPERSONATEto a user called dc_admin.

We don't use the Data Collector feature of SQL Server.

What do you suggest ?

MS_DataCollectorInternalUser is not an orphaned user. Is a user without a login, a different kind of beast. I suggest you update your security practices, you may have already dropped other valid non-orphaned users. Unless you have a specific security bulletin quoting a reason to drop or modify this user you should leave it in place.


'OraOLEDB.Oracle' provider is not registered on the local machine - Oracle Database Client 12C I have some problem with connect to the Oracle Database by Oracle Client. I installed on laptop oracle client - version 32 bit. I can connect to the database from laptop when i use 64 bit applications, but when i will use 32 bit application i get following error:

 ERROR System.InvalidOperationException: The 'OraOLEDB.Oracle' provider is not registered on the local machine.
   at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at EconLib.Utility.SqlDataAccess.OpenConnection(String host, String database, String user, String pass)

i have seen this question 'https://stackoverflow.com/questions/12903366/oraoledb-oracle-provider-is-not-registered-on-the-local-machine', but i can't find oraOLEDBXX.dll on my laptop. Any idea what can i do ?

From comment by Wernfried Domscheit:

  Have a look at this instruction to install both 32 bit and 64 bit on one machine: stackoverflow.com/questions/24104210/…. Note Oracle OLEDB provider is not included in Oracle Instant client, you have to install it separately or enable the corresponding option in Oracle Universal Installer.


SQL Error log maintenance I have a few questions about the SQL Server error logs.

Are there any performance gains for recycling the SQL Error log?
How often should you recycle it?

Are there any performance gains for recycling the SQL Error log?

Not in terms of sql server's performance. Better for maintenance and when you want to find specific error - the size of the log file will affect any app or even opening the error log.

To recycle error log - use EXEC sp_cycle_errorlog. You can even schedule this using a sql agent job on a weekly or biweekly (depending on the activity of your server).

If you have Logshipping, to avoid getting your log bloat, I use TF 3226

  How often should you recycle it?

It depends ! I have it every week on my prod servers with a maximum of 10 error logs (Limit the number of error log files before they are recycled setting in sql server log --> configure option) .

This is official from Microsoft - How to manage the SQL Server error log ?


SQL Server not Using Index I am absolutely stumped as to why my query is not using what I think is a selective index.
My Model consists of Claims, Contacts, and Phone Numbers. Each Claim has 1 Contact and Each Contact has Many Phone numbers. A Claim can have a Status and a Phone Number has a Type.

I have added an index on the Claim for the Status and it includes the ContactID.
create index Status on tClaim(Status) include (Name,ContactID)

I have added an index on the Phones for the ContactID and Type that includes the Number.
create index ContactID_Type on tContactPhone(ContactID,Type) include (Number)

I am trying to write a query that returns all Claims that have a status of 'Won' and the corresponding 'Home Phone' for the Claim's Contact. I have tried it 2 ways. One including the join to Contacts and one without. Neither generate a plan that I expect.
select
    c.ID,
    c.Name,
    p.Number
from
     tClaim c
     left join tContactPhone p on
        c.ContactID=p.ContactID and p.Type='Home'
where
    c.Status = 'Won'

select
    c.ID,
    c.Name,
    p.Number
from
     tClaim c
     inner join tContact co on
        co.id=c.ContactID
     left join tContactPhone p on
        co.ID=p.ContactID and p.Type='Home'
where
    c.Status = 'Won'

The plan I am getting back, refuses to use the tContactPhone.ContactID_Type. It suggests indexing by Type, which doesn't make sense, because it seems less selective than the ContactId.
Paste The Plan
Here is the script I used to create a sample data set to test. Please note my actual data set is much larger, named better, and has a lot more fields; but this is distilled down to replicate my situation [AKA I don't even like the naming conventions and data generation, but it gets the job done :)]
/*
        Create Tables and Constraints
*/
CREATE TABLE tContact(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](100) NOT NULL,
    CONSTRAINT [pkey_tContact] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )
)
GO

CREATE TABLE tContactPhone(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ContactID] [int] NOT NULL,
    [Type] [nvarchar](25) NOT NULL,
    [Number] [nvarchar](12) NOT NULL,
    CONSTRAINT [pkey_tContactPhone] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )
)
GO

ALTER TABLE tContactPhone  WITH CHECK ADD  CONSTRAINT FK_tContactPhones FOREIGN KEY(ContactID)
REFERENCES tContact ([ID])
GO

ALTER TABLE tContactPhone CHECK CONSTRAINT FK_tContactPhones
GO

CREATE TABLE tClaim(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ContactID] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Status] nvarchar(10) not null,
    CONSTRAINT [pkey_tClaim] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )
)
GO

ALTER TABLE tClaim  WITH CHECK ADD  CONSTRAINT FK_tClaim FOREIGN KEY(ContactID)
REFERENCES tContact ([ID])
GO

/*
        Add Test Data
*/
declare @Count int = 0
declare @ContactID int =0

while(@Count<100000)
begin
    set @Count = @Count+1
    insert into tContact(Name)
    select 'Name' + convert(nvarchar(10),@Count)

    set @ContactID= SCOPE_IDENTITY()

    insert into tContactPhone(ContactID,Number,Type)
    select @ContactID,@Count+1,'Home'
    union select @ContactID,@Count+1,'Cell'

    insert into tClaim(ContactID,Name,Status)
    select @ContactID, convert(nvarchar(10),@ContactID)+'_ClaimName',case @Count % 25 when 0 then 'Won' else 'Closed' end
end

/*
        Add Indexes for Queries
*/
create index Status on tClaim(Status) include (Name,ContactID)
create index ContactID_Type on tContactPhone(ContactID,Type) include (Number)

You can get almost as good a plan with fewer indexes if you cluster tContactPhone by (ContactID,ID) instead of having a clustered index on ID and a seperate non-clustered index on ContactID.  eg
CREATE TABLE tContactPhone(
    [ContactID] [int] NOT NULL,
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Type] [nvarchar](25) NOT NULL,
    [Number] [nvarchar](12) NOT NULL,
    CONSTRAINT [pkey_tContactPhone] PRIMARY KEY CLUSTERED
    (
        [ContactID],[ID]
    )
)

This is generally a better-performing pattern for "child tables" as the clustered index also supports the foreign key.


Symmetric Key Creation Question I am going through the CREATE SYMMETRIC KEY (Transact-SQL) page.

In there it says as a Caution

  When a symmetric key is encrypted with a password instead of the
  public key of the database master key, the TRIPLE DES encryption
  algorithm is used. Because of this, keys that are created with a
  strong encryption algorithm, such as AES, are themselves secured by a
  weaker algorithm.

I do not understand the first part in bold and have a couple of questions.

From my testing I do not see an option to encrypt a symmetric key
with Database Master key.
Isn't the Database master key a Symmetric key.  What do they mean by
    public key of th database master key?

I believe you are correct and the docs need updating.  Should probably read:

  When a symmetric key is encrypted with a password instead of a
  certificate (or another key), the TRIPLE DES encryption algorithm is
  used to encrypt the password. Because of this, keys that are created
  with a strong encryption algorithm, such as AES, are themselves
  secured by a weaker algorithm.

docs.microsoft.com is all on GitHub, so you can propose doc changes.

I've submitted a pull request to update the language.  You can follow it here: https://github.com/MicrosoftDocs/sql-docs/pull/310


How can I select the next available row id of varchar type in MySQL? I have a table with primary key 'Id' of varchar type. I want to select the next row when user clicks next record, in php, for example
1st row id= 'a'
2nd row id= 'b'
3rd row id='h'
4th row id='z'

Im currently on page with get variable  id='a'
I want to get the next which is 'b', then 'h' then 'z'

The first record is easy as I send  page.php?id='a' to display
table records.

The next record,
Select id from table where id > 'a'
But I dont want 'h' or 'z' because 'b' is next.
I dont have a clue, any help will be appreciated

SELECT id AS next_id FROM your_table WHERE id > @current_id ORDER BY id LIMIT 1

Breaking that down:

SELECT id AS next_id FROM your_table

that bit is hopefully obvious

WHERE id > @current_id

you don't want the current value or anything before it

ORDER BY id

in the output, list earlier ID values first

LIMIT 1

we only want one of them so no point going further once one has been ready from the table.

As an aside: is there a any particular reason why you are using a character field as the ID instead of a numeric one which would be more efficient?


Moving Report Server Databases to Another Drive Currently our Reporting Server Databases are in C:
So would like to place them in E:\RS\

Can I know how can I move the ReportServer and ReportServerTempDB Database files from one location to another and things I should keep in mind before this operation.

There will be minimal downtime, so plan this in a maintenance window:

Don't use detach/attach!

Have verified backups!
Use Alter database...modify file... (do below for mdf and ldf files)

use master;
alter database dbname
modify file (
    name = 'logicalName',
    filename = 'E:\RS\db_name.mdf'); --> This is the new file location

Now offline the database:

alter database db_name set offline with rollback immediate;

Physically copy the mdf and ldf files to the new location.
Bring the database online:

alter database db_name set online;

If you script out and test out on a dummy database, you will see how much time it takes and will build your confidence.


Generic way to determine if the table needs index (PostgreSQL) I saw advices like Indexes should not be used on small tables or Index on small table may decrease performance`. Can anybody provide magnitude of table size when creating indices makes sense, besides trial and error?

Background: I want to add indices to several tables in database and looking for a general rule to start with.

You're missing the point of @a_horse_with_no_name's comments. You create indexes to satisfy queries but whether or not they're chosen is a result of statistics. Whether or not choosing them (or not) is correct action is a factor of the accuracy of statistics to model the workload, environment, and indexes.

Statistics take into account things like selectivity to determine block/page fetches. To determine how many blocks you must retrieve you need to have a value for table size (rows), row width (bytes per row), page size, and page fragmentation.

The point is it's complex, to make matters even more complex most of the values are configurable or can be tuned by either the distro, the user, or the person who compiles the database.

So you're working at the problem from the wrong angle, the right question isn't

  Are all of those factors correct to create an index?

But instead,

  For this given query, can I use an index?

That is to say, go ahead and create the index. Then if the index is not used in the query and you think it should be, you can come back here and paste the query, the schema, the result of EXPLAIN ANALYZE, and  your configuration parameters and hope someone will break it down for you as to why your index which could have been used was not chosen.

As an aside, one of the most powerful tools to determine that is set enable_seq_scan = 0;. By disabling sequential table scan, you can usually  force the planner to use an index. If it uses your index and it's slower, you know that PostgreSQL made the right plan. That's often the case. The planner is smarter than 98% of the users; and, this method is the "check that it's powered on" of query optimization.


Checking update value before update First time posting so apologies if this has been asked already.

I have a table in SQL Server that is being updated from many different locations with data that I don't want in the table. I have come to the conclusion I need a trigger that will fire before the update happens to check to see if the email_address is valid.

I have two tables tracked and trackOmit.

Tracked
  id
  email_address
  ip_address
  trackedPage

trackOmit
  id
  trackOmit_email

TrackOmit is a list of email domains "hotmail.com, gmail.co.uk" etc, that for whatever reason we don't want tracking, however due to bad design the update statement doesn't take into account the trackOmit table. I do not know the first place to start to write a trigger to check the updated values against the trackOmit table before update.

Thanks for any help that can be provided.

Here is an example of an INSTEAD OF TRIGGER.

--demo setup
USE [Test]
GO

drop table if exists Tracked;
drop table if exists TrackOmit;
Create table Tracked (id int, email_address varchar(100), ip_address varchar(50), trackedPage varchar(100))
create table TrackOmit (id int, trackOmit_email varchar(100))

insert into Tracked(id, email_address, ip_address, trackedPage) values
(1,'test@gmail.com','10.100.100.100', ' ')

insert into TrackOmit(id,trackOmit_email) values(1,'hotmail.com'),(2,'gmail.co.uk')
-----------------
--create instead of trigger
DROP TRIGGER IF EXISTS [dbo].[TrackedInsteadOfUpdate];
GO
CREATE TRIGGER [dbo].[TrackedInsteadOfUpdate] ON [dbo].[Tracked]
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON

    --only update rows where the updated email_address
    --is not on the TrackOmit table
    UPDATE t
    SET t.email_address = i.email_address
    FROM dbo.tracked t
    JOIN inserted i
        ON i.id = t.id
            AND NOT EXISTS (
                SELECT *
                FROM TrackOmit o
                WHERE i.Email_Address LIKE '%' + o.trackOmit_email + '%'
                )
END
GO

ALTER TABLE [dbo].[Tracked] ENABLE TRIGGER [TrackedInsteadOfUpdate]
GO

--original values in Tracked
select * from Tracked

| id | email_address   | ip_address     | trackedPage |
|----|-----------------|----------------|-------------|
| 1  | test@gmail.com | 10.100.100.100 |             |

--Attempt update for hotmail.com - should not update
update Tracked set email_address = 'test@hotmail.com'
select * from Tracked

| id | email_address   | ip_address     | trackedPage |
|----|-----------------|----------------|-------------|
| 1  | test@gmail.com | 10.100.100.100 |             |

--Attempt update for gmail.com - should update
update Tracked set email_address = 'test1@gmail.com'
select * from Tracked

| id | email_address   | ip_address     | trackedPage |
|----|-----------------|----------------|-------------|
| 1  | test1@gmail.com | 10.100.100.100 |             |

--Attempt update for gmail.co.uk - should not update
update Tracked set email_address = 'test2@gmail.co.uk'
select * from Tracked

| id | email_address   | ip_address     | trackedPage |
|----|-----------------|----------------|-------------|
| 1  | test1@gmail.com | 10.100.100.100 |             |


ORA-04031: unable to allocate X bytes of shared memory Trying to start oracle service, shows me the follow error:

I have the follow SPFILEXE.ORA:

Im using ORACLE XE, but really I don't know which parameter change and what should be the value.

Please help !!! Thanks in advance

Try doubling shared pool size to 1G, or 1000M.

Java pool should be larger if you're using EJB.

Large pool should be much larger if you're using RMAN.

Remove sessions.

Reduce processes by a LOT, like start with 200.  Are you ever going to use close to 10,000???  XE is not meant for that many processes.

Make job_queue about 20.


Spool query to file without output on screen Using spool on Oracle sqlplus correctly
sends the results of my queries to a file.

But I also see output on the screen.

Is it possible to see only the query without output on screen, and result on file? (This feature is the default on PostgreSQL psql client program).

You can use SQL> SET TERMOUT OFF command to get the result.

Here are some useful links collected from comments:

Oracle SqlPlus - saving output in a file but don't show on screen
sqlplus, how to stop output to console
How to make sqlplus output appear in one line?
Remove blank lines from csv using shell script for oracle


Database design for large amount of sensors at very fast intervals I've been tasked with designing a backup solution to store sensor data from about 1500 sensors at 10 second intervals. In the event that the primary transmission method of the sensors goes down, my solution should not have a delay of more than 10 seconds since the actual update from the sensor. Thus making my solution the next source of data for them to consume.

The data that is generated is also stored in a file and I am supposed to read them as reference, rather than taking it directly from the sensors themselves.

I've been playing around with the numbers and I can't seem to reach that 10 second threshold. Considering that each file amounts to about 10 MB at the end of each month, I've got close 15 GB of data to process and import.

Currently I have been testing with 3 tables of 500 sensors each to store about 1 month worth of data and I keep hitting the 2 hours mark for processing using MySQL LOAD DATA INFILE method with temporary tables.

From here I can only assume that my methods and approach to this is wrong and was wondering if anyone could provide any kind of advice? Is it even possible to approach that kind of timing in the first place?

I have read the following questions for reference to get a better idea on how to approach this.
Database redesign opportunity: What table design to use for this sensor data collection?

Edit 1

Currently using MySQL 5.7.28 for testing. No plans to move towards 8.0.
Current structure is as follows:

CREATE TABLE `points0500` (
  `DateTime` datetime NOT NULL,
  `Point0000` double DEFAULT NULL,
  `Point0001` double DEFAULT NULL,
  `Point0002` double DEFAULT NULL,
...
  `Point0497` double DEFAULT NULL,
  `Point0498` double DEFAULT NULL,
  `Point0499` double DEFAULT NULL,
  PRIMARY KEY (`DateTime`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Database Configuration: my.ini

innodb_log_buffer_size=256M
innodb_buffer_pool_size=2G
innodb_log_file_size=256M

Sorry about the lack of details, not sure what else is important.

Edit 2

Current Test Server is running on a Xeon E3-1220, with 16 GB of RAM and two 1 TB HDD on RAID 1.

If the data was used for consolidated report, I am okay with the time taken.
The issue with the timing is that they are using the data for something time-sensitive which currently I am not privy to. To the best of my knowledge, they will query the database for the latest readings every 10 seconds to get the sensor readings.

150 new rows per second?  At least one of these needs to be true:

Using SSD, not HDD.
Batch loading (LOAD DATA, INSERT with multiple rows, etc)

Other notes:

Don't use DOUBLE.  Double takes 8 bytes and has far more precision than any sensor can provide.  This is important because the size of the data, as you noted, is important.  FLOAT gives you about 7 significant digits in 4 bytes, ample for virtually all sensors.  You could even consider DECIMAL or various *INT types, perhaps with scaling, to save even more space.
How much RAM do you have?  (I'll critique your my.ini settings after getting the RAM size.)
What is the problem with taking 2 hours to load 720 hour's worth of data?
What will you do with the data?  There are several directions I will point you, depending on what kinds of queries you will apply to the data.  Some will make a significant improvement in speed and/or space.

Back to your question:

  I've been tasked with designing a backup solution to store sensor data ... in the event that the primary transmission method of the sensors goes down.

If that is the question, the answer is trivial:  A flat file, in the same format as the raw data comes in.  That way, when recovering from the outage, you focus on getting data from a different source, but not a different format.


How to get results in separate rows after joining two tables I have two tables say - Plans and Discounts. Below are the sample data in Plans table.

Below are the sample data in the Discounts table.

These two tables are related using PlanName column. I am looking for a query in the most optimized way to get a resultset in such a way that a discount for a particular plan should come below the corresponding plans. Sample data below.

What you need is not a join which combines tables horizontally, you need a union set operator to join them vertically.
In your images all tables are named 'table 1' so I changed one of them to 'table 2' to make the query valid.

SELECT PlanName AS ProductName, Amount, StartDate, EndDate
FROM Table1
UNION ALL
SELECT DiscountName, Amount, StartDate, EndDate
FROM Table2
ORDER BY ProductName;

HTH


SQL Server OPENQUERY vs Regular Query against AS400 I log into a SQL Server database which has an AS400 server as a linked server.

I've discovered that running queries against this AS400 server is MUCH faster if I run the select in an OPENQUERY...

SELECT  EMEQP#
FROM _
WHERE EMALOC = '467' AND EMDLCD = 'A' AND EMSTAT NOT IN ('S', 'J', 'V')

Execution time: 46 seconds

SELECT EMEQP#
FROM
OPENQUERY(_, '
SELECT  EMEQP#
FROM _
WHERE EMALOC = ''467'' AND EMDLCD = ''A'' AND EMSTAT NOT IN (''S'', ''J'', ''V'')
')

Execution time: 1 second

Can someone explain or link me to why/how this is happening?

When you use openquery the query itself executed on remote server and you receive only results. In case of linked server, you local server does all the job. You may want to check
http://social.msdn.microsoft.com/Forums/eu/transactsql/thread/0e68f512-1e19-4c50-b343-219085d70076


SQL Server Service Broker and Deadlocks I am reading through SQL server books online for the first team whilst learning how to use the service broker and I came across this statement:

  The application that implements the service reads the message, runs the query, and returns the results. All three of these operations take place in the same transaction. If a failure occurs before the transaction commits, the entire transaction rolls back and the message returns to the queue. When the computer recovers, the application restarts and processes the message again.

I am guessing that this will block in the same way as usual transactions? Ie. row/page locks. Am I correct? Is this something that I should worry about or just leave it until I have a problem?

When the service broker pulls messages from the queue using an activation stored procedure (or in any method) all the normal locking rules apply to the queue and the objects which are referenced in the code after the receiving of messages from the service broker queue.

Note that the information in BOL that you quoted is only true is you do the receive within an explicitly defined transaction.  If you receive a message and there is no explicit transaction to rollback then the database can't put the message back in the queue.


Index or View of all UUIDs in Postgres DB? I have a Postgres database with about 35 tables of varying sizes. Every table has an auto incrementing integer primary key, as well as a column of native Postgres uuid data type recording Version 4 UUID values.

The application code logs UUIDs, but...alas...doesn't always log the object type or table.

While we're working on cleaning that up, we sometimes just want to figure out what row a UUID refers to.

Is there a reasonable strategy to create a view, index, or even full on table to efficiently find a row given a UUID, if every row in the database has a UUID? Or am I chasing an antipattern and should solve it at the application level?

Disclaimer: I think the whole idea doesn't really make sense. But anyway here is one way to get a uniform "view" on all UUIDs in the database.

You can create a function that goes through all columns that are defined as UUID and then returns the values for those columns. You can optionally pass a UUID value that should be searched for:

Something like:

create or replace function find_uuids(p_id uuid default null)
  returns table(schema_name text, table_name text, column_name text, uid uuid)
as
$$
declare
  l_row record;
  l_sql text;
begin
  for l_row in select n.nspname, tb.relname, col.attname
               from pg_attribute col
                 join pg_type ty on ty.oid = col.atttypid
                 join pg_class tb on tb.oid = col.attrelid
                 join pg_namespace n on n.oid = tb.relnamespace
               where col.attnum > 0
                 and not col.attisdropped
                 and ty.typname = 'uuid'
                 and n.nspname not in ('pg_catalog', 'information_schema')
                 and n.nspname not like 'temp%'
  loop
    l_sql := format('select %L as schema_name,
                            %L as table_name,
                            %L as column_name,
                            %I as uid
                     from %I.%I', l_row.nspname, l_row.relname, l_row.attname, l_row.attname, l_row.nspname, l_row.relname);
    if p_id is not null then
       l_sql := l_sql || format(' where %I = %L', l_row.attname, p_id);
    end if;
    return query execute l_sql;
  end loop;
end;
$$
language plpgsql;

Then you can use:

select *
from find_uuids('19d2efcc-45c1-4371-addc-45028a054b85');

to find the tables where that UUID value appears, or

select *
from find_uuids();

to see everything.

The above returns something like this:

schema_name | table_name | column_name | uid
------------+------------+-------------+-------------------------------------
public      | uuid_test  | some_id     | 666ce0a8-11c9-11e0-b5e2-3b49c86ffc2c
public      | uuid_test  | some_id     | 7fffda0c-11c9-11e0-967c-a300aec7eb54
public      | some_table | id          | 3aa04664-863c-43e6-9890-1087db021fd8

But this is going to be really slow


truncate table for all db in sql server 2000 i need a script that will go to every db in server and if exsistes a table named "table1" will do

truncate table table1

First run this script to see which tables would be truncated. Then do the obvious modification.

SET NOCOUNT ON

declare @name sysname
declare @sql varchar(255)
declare @sql2 varchar(255)

declare @rc int
declare db_cursor cursor for

select name from master..sysdatabases
open db_cursor

fetch next from db_cursor into @name
while @@FETCH_STATUS = 0
begin
    Set @sql = 'select * into #t1 from ' + @name + '..sysobjects where name = ''table1'' and type = ''U'''
    EXEC(@sql)
    set @rc = @@rowcount
    if @rc > 0
    begin
        set @sql2 = 'use ' + @name + '; ' + 'Truncate table table1'
        print @sql2
        --Exec(@sql2)
    end
    fetch next from db_cursor into @name
end

close db_cursor
deallocate db_cursor


Script to show MySQL health status? Kev suggested this question ( https://stackoverflow.com/questions/10524981/php-script-to-show-mysql-health-status ) is better fitted here.

I know phpMyAdmin can shows the lists of MySQL vars and stuff like that.

But I would love to have a script that shows me if, for example, there is something wrong with MySQL configs or some graphic tools to see current MySQL health status.

Is there something like that? How can I know, for example, if current settings aren't good for my machine?

Let's say I have innodb buffer pool size set to 64MB while one of my INNODB tables is 1GB. That's not good at all. And that's just an example with innodb pool size. I think there could be many other settings to be aware of that a web based script could help to monitor.

The question asked by yes123 is very relevant in the sense that we all want to know what we can do to improve MySQL's performance without touching the application that's consuming that data.  What is that perfect configuration that will just make everything better?  Although no one program can just spit out that perfect configuration, there are ways to dramatically improve your server's efficency without touching one piece of code.  For starters, I begin with the server's health and what kind of data that server was destined to provide.

Knowing the health of your MySQL database server and how to better configure it must include many factors based on a large number of variables.  What is your MySQL server being used for, a blog, e-commerce, reporting?   What are the principal engines you are using, InnoDB, MyISAM?  What is the OS, Windows, Linux, Unix?  Is there replication involved?

This is important to know because the way you configure MySQL depends on all of these factors and also on the health of the machine that MySQL is installed on.  All to often, people are quick to blame MySQL for a problem that is system wide.   If you setup Magento on a server that only has 1 CPU, 1Gb of ram and and you run out of memory because you have 1000 simultaneous competing on Apache, is it MySQL's fault? Obviously not.

So before you start asking yourself, how is MySQL doing, ask yourself, how is the server its installed on doing?

When dealing with a Linux server for example, here are the steps I follow to perform a general health check

Is there enough RAM? Answer: free -m
Is there enough disk space where MySQL is installed, typically /var/lib/mysql. Answer: df -h
What is the load average, is the CPU or CPUs at 100%.  Answer: top or htop
How much traffic is there on the network.  Answer: bwm-ng

For a Windows Server, I'm going to use perfmon or process explorer.

If I don't have any disk space left, there's no ram left and/or your network bandwith is saturated, it may not be MySQL's fault. Since MySQL stopped working or is timing out,
I may think that MySQL is the cause when its really just the victim of another problem

A very good graphical program for linux that's free is called ksar http://sourceforge.net/projects/ksar/.  You can use ksar to produce graphs based on the statistics collected by the sar service.  sar can be easily installed on both debian and centos and collects statistics about memory, cpu, disk, network usage.  All by itself sar provides you with that information in a numerical report.  ksar goes one step further and puts that information into time based graphs that help to visualize the health of your server at any given point in the day or from any given day in the past.

In Windows,
you need something that could do the same thing, like Process Explorer http://technet.microsoft.com/en-us/sysinternals/bb896653 or perfmon.  ksar and process explorer are both free.

Once I've eliminated the server or other applications such as Apache as possible culprits, that's when I start looking at MySQL.

I'll start with the MySQL's error log if there is one, and I'll look for disk space errors, or table corruption errors or other messages that stand out.  If I don't see anything out of the ordinary, I go to my favorite tool Jet Profiler http://www.jetprofiler.com.

Jet Profiler provides you with real time statistics on the state of your MySQL server over a given period of time. This is much more useful than just a simple snapshot of activity at any given moment.  Knowing how your server behaves during low and high peak periods of time will ultimately shed light on the weaker areas of your infrasture and that's where Jet Profiler comes in.  Its analysis and statistics are based on a server's workload over time.

Find problematic queries

As a first step, I try to identify which the problematic queries are. Check top queries and slow queries, and compare the relative thread load of each of them. Probably, a few of them will stand out clearly from the rest (according to the 90/10 rule or so). These are the queries to focus on, because they will give the most performance improvement per fix.

Load Pattern

The load pattern could also be that the server is doing well for the most time, but with recurring peaks from time to time. It will be very helpful to zoom in on the peaks to discover which queries are heavy during that timeframe. I.e. if you have heavy cron jobs which run once per day, they might not show up too high on the top queries for the last 24 hours so you might not think about them. However, they can still cause so much load and congestion that the server gets really slow precisely at midnight. One database I profiled had this pattern - the developers had added daily cron jobs which did various cleanup, archiving and statistical calculations. The problem was that all of the jobs started at 0 0 * * *. While the server could have handled one or two of these jobs and still serving regular web site visitors, 5+ concurrent jobs was too much. So the site went down for a minute or two every midnight. But those queries probably wouldn't have shown up unless we looked at that specific peak.

Going further, I move on to statistics specific to the engine that my MySQL tables are using MyISAM or InnoDB.

Before though, I ask myself one important question.  Is the database mostly InnoDB tables or MyISAM tables?

    use database;
    show table status \G;

*************************** 148. row ***************************
           Name: sometable
         Engine: InnoDB

Many production databases in MySQL become a mixture on InnoDB and MyISAM.  This requires an optimization of MySQL for both engines.

InnoDB Specific Information

Buffer Pool Hit Rate :  This should typically be at 100%.  If it isn't, MySQL is spending a lot of its time reading the disk instead of memory to retrieve data.  Increasing the buffer pool,
may resolve this problem.
InnoDB disk I/O statistics.  Is the server having disk problems, maybe I need to upgrade to something faster, if InnoDB is writing gigabytes of data to the server.

MyISAM specific information

Locked queries: Queries can easily block eachother in MyISAM tables due to locking. Jet Profiler will show you which queries are locked and on what tables.  You
can then easily resolve this probem by switching the table engine from MyISAM to InnoDB.
MyISAM cache: This should also be as close to 100% as possible.  If not, you can increase the key buffer cache to resolve the problem and/or build better indexes.

Next I look at what's going on globally.

Global Factors

States: What is MySQL doing?  Is it copying data to temporary tables, is it sending data across the network or maybe its sorting. In other words what processes in MySQL are using up the most CPU, memory and IO.
Slow queries:  What queries are taking the most of the CPU's time.  You can also get this by activating the slow query log on the server, but this would impact the server,
because it would be writing those queries to a disk.
Number of simultaneous queries/users.  Perhaps there are just too many users on my little server.

Correlating KSAR and Jet Profiler statistics

With Ksar's and Jet Profiler's graphs, you can actually correlate server performance charts with the MySQL's performance charts.   How is the server behaving when MySQL starts running that batch job.  If you notice a large number of major page faults in KSAR right when MySQL is processing that huge import that you see running as a Top Query in Jet Profiler right when MySQL is serving 1000 simultaneous users, you could just simply run your import when there aren't so many incoming connections.

Then there are other factors like, is the skip-name-resolve feature turned on.  If MySQL has to spend its time resolving domain names, its spending less time retrieving data. I also look at the Query Plan Cache to make sure that's also at 100%

As far as web solutions go.  I am beginning to discover the joys of Zabbix http://www.zabbix.com/.  It is geared towards monitoring multiple servers simultaneously but is capable of providing very precise detail about MySQL and all types of servers and technologies.

It is possible to have that perfect configuration.  With a thourough understanding of your OS, an intelligent analysis of your server's health, the queries its processing and the kind of workload your are asking it to handle you can focus on just those areas that need your attention.  Just adding a couple of gigabytes to your buffer pool or getting a faster disk might improve your server's performance, but why spend money on ram and faster disks, when the solution might just be spending a couple of hours on analyzing your server's workload and how its behaving even when your not there to monitor it.


MySQL How to copy all data in tables from one huge database to another in the same server Consider that I have 2 databases with the exact same structure sitting on the same server one with data and the other is empty. The usual way to copy the data from the first one to the second is to export it on my local machine and then import it on the other one but what if the database size is over the server's limit (and I have no control over that limit), the only two options I know of are:

To export bits and pieces of the first database over a number of times so I'd end up with 10 sql files for example each not exceeding the upload size limit and import those one by one.
To write a query to copy a table from the first database to the second (i.e. INSERT INTO database2.table SELECT * FROM database1.table) Which is a better option if you have small number of tables because you'd have to write the same query for each table.

Is there a way to use the second method where I wouldn't have to write the tables' names? In theory I should be able to get the tables names from the information_schema database and work on those, but I'm not sure where they are stored and how that would work in a query.

You can query information schema for list of all tables in your source database and build the import commands.

SELECT
    CONCAT('INSERT INTO target_db.', TABLE_NAME, ' SELECT * FROM source_db.', TABLE_NAME, ';') restore_command
FROM information_schema.TABLES
WHERE
    TABLE_SCHEMA='source_db'
    AND TABLE_TYPE='BASE TABLE'
ORDER BY TABLE_NAME;

Then copy/paste the output in query window.


Filters Joins or Where clauses TSQL Where should I put the filters on a TSQL query?

SELECT a.colum FROM A
JOIN B  ON A.ID =  B.ID AND B.STATUS  = 1

OR

SELECT a.colum FROM A
JOIN B  ON A.ID =  B.ID
WHERE  B.STATUS  = 1

I'm looking for performance issues or good practices.

There is no performance difference
Best practice is explicit JOIN and separate filters (your 2nd example)

Note:

This changes for OUTER JOINs because you'd change it to a inner join

The quick way:

SELECT a.colum
FROM
   A
   LEFT JOIN
   B  ON A.ID =  B.ID AND B.STATUS  = 1
WHERE
   a.foo = 'bar'

With separate filters:

SELECT a.colum
FROM
   A
   LEFT JOIN
   (SELECT * FROM B WHERE STATUS  = 1) B ON A.ID =  B.ID
WHERE
   a.foo = 'bar'


Select N rows before/after specified row Let's say I have a simple table in Postgres (11.3):

create table posts
(
    id serial not null,
    created_at timestamp(0)
    constraint posts_pkey
        primary key (id)
);

If a user requests id=5869, I need to be able to return the N rows before and the N rows after that row in a query that is ordered by the created_at column. If we're able to assume that the larger the id, the larger the created_at, we can do something relatively simple like this:

(select * from posts where id < 5869 order by id limit 10)
union all
(select * from posts where id >= 5869 order by id limit 11);

However, I am unable to assume that the higher id is most recently created and I'm wondering what the best way to retrieve that data is in that case. This method works, but is remarkably slow on a 100k row dataset:

WITH
  boundaries AS (
    SELECT *,
           row_number() OVER (ORDER BY created_at DESC) AS rownum
    FROM posts
  ),
  target_boundary AS (
     SELECT *
     FROM boundaries
     WHERE boundaries.id = 5869
 )
SELECT posts.*, boundaries.rownum
FROM posts
LEFT JOIN boundaries ON posts.id = boundaries.id
JOIN target_boundary ON boundaries.rownum BETWEEN target_boundary.rownum - 10 AND target_boundary.rownum + 10

Running through that was taking upwards of 800 milliseconds which is far too slow on a dataset so small.

I have also tried a variation of the above using lead() and lag(), but that was even less efficient.

Is there a better way to do this query? Is there perhaps a window function I am missing in Postgres that would handle it?

WITH
cte1 AS ( SELECT created_at, 10 N
          FROM posts WHERE id=5869 ), /* parameters */
cte2 AS ( SELECT posts.created_at,
                 LAG(posts.created_at, cte1.N, CAST('2000-01-01' AS DATE)) OVER () lbound,
                 LEAD(posts.created_at, cte1.N, CAST('2000-01-01' AS DATE)) OVER () ubound
          FROM posts, cte1 )
SELECT posts.*
FROM posts, cte1, cte2
WHERE posts.created_at BETWEEN cte2.lbound AND cte2.ubound
  AND cte2.created_at = cte1.created_at
ORDER BY posts.id

sample fiddle (N set to 3, id set to 10).

PS. I understand that window in LAG/LEAD must be specified like ORDER BY posts.created_at ASC ROWS BETWEEN [UNBOUNDED | cte1.N] PRECEIDING AND [UNBOUNDED | cte1.N] FOLLOWING. May be added if needed really.


MySQL update one table from another joined many-to-many I have two tables, events and plays joined many-to-many by a third table plays_in_events.

Where the events.Name field is blank, I want to set it equal to the name of the play that was performed in that linked event.

I have tried

UPDATE `events`
  INNER JOIN `events` ON (`plays_in_events`.`EventID` = `events`.`EventID`)
  INNER JOIN `plays` ON (`plays_in_events`.`PlayID` = `plays`.`PlayID`)
  SET events.Name = plays.Play
WHERE
  events.Name IS NULL and plays_in_events.PlayID = 1

but get a syntax error

SQL Error: Not unique table/alias: 'events'

(I'm trying to limit any damage from accidental multiple updates by only testing it in the first instance for PlayID = 1, but have hundreds of records to update, so I don't want to do it manually).

There is only one table events in the database.

What's going wrong here please? I can't see it, unfortunately.

LATER

Maybe I've got the JOIN wrong altogether.

This SELECT query works to join the two main tables, and was produced by a visual query editor:

SELECT
  `events`.`EventID`, `events`.`Name`, `plays`.`Play`, `plays`.`PlayID`
FROM
  `plays_in_events`
  INNER JOIN `events` ON (`plays_in_events`.`EventID` = `events`.`EventID`)
  INNER JOIN `plays` ON (`plays_in_events`.`PlayID` = `plays`.`PlayID`)
WHERE
  `events`.`Name` IS NULL AND
  `plays`.`PlayID` < 10

How could I make the JOINS in the UPDATE based on this working?

This works - I had the JOIN wrong

UPDATE `events`
  INNER JOIN `plays_in_events` ON (`plays_in_events`.`EventID` = `events`.`EventID`)
  INNER JOIN `plays` ON (`plays_in_events`.`PlayID` = `plays`.`PlayID`)
  SET events.Name = plays.Play
WHERE
  events.Name IS NULL and plays_in_events.PlayID = 1

and the earlier comments were quite right, but it took me a while to 'get it' - thanks for your help. I had just copied the JOIN lines from the working SELECT query, but when I looked again, I could finally see what the commentators meant about events appearing twice, and plays_in_events not appearing properly in the JOINs.


What is the difference between select count(*) and select count(any_non_null_column)? I seem to remember that (on Oracle) there is a difference between uttering select count(*) from any_table and select count(any_non_null_column) from any_table.

What are the differences between these two statements, if any?

COUNT(*) will include NULLS
COUNT(column_or_expression) won't.

This means COUNT(any_non_null_column) will give the same as COUNT(*) of course because there are no NULL values to cause differences.

Generally, COUNT(*) should be better because any index can be used because COUNT(column_or_expression) may not be indexed or SARGable

From ANSI-92 (look for "Scalar expressions   125")

  Case:

  a) If COUNT(*) is specified, then the result is the cardinality
                of T.

  b) Otherwise, let TX be the single-column table that is the
                result of applying the <value expression> to each row of T
                and eliminating null values. If one or more null values are
                eliminated, then a completion condition is raised: warning-
                null value eliminated in set function.

The same rules apply to SQL Server and Sybase too at least

Note: COUNT(1) is the same as COUNT(*) because 1 is a non-nullable expression.


64 bit Machine 32 bit Excel Using SQl Server Management Studion V17 I am using the below sql command to import an excel file (created in excel 2016) into SQL Server Management Studio on a 64bit Machine with 32 Bit Office.

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
                Database=C:\File1.xlsx', 'SELECT * FROM [Sheet1$]');

But I am getting the following error:

  Msg 7403, Level 16, State 1, Line 1 The OLE DB provider
  "Microsoft.ACE.OLEDB.12.0" has not been registered.

I tried downloading 2007 Office System Driver: Data Connectivity Components but it didn't help.

Is it better to upgrade to 64 Bit Office? I only want part of the excel document to upload directly into a sql table.  I can do the whole table via import but want to do it with VB or a Macro..

You'll need to use a more recent driver since the excel file was created in Excel 2016.

Install the following driver on the machine running the SQL Server Database Engine: Microsoft Access Database Engine 2016 Redistributable (64-bit)

https://www.microsoft.com/en-us/download/details.aspx?id=54920

After the driver is installed, open up SSMS and navigate to:

Linked Servers -> Providers -> right-click Microsoft.ACE.OLEDB.16.0: Enable "Allow In-Process"

From here, you can use the Microsoft.ACE.OLEDB.16.0 in your OPENROWSET command.
I prefer to setup linked servers to these files, as follows:

EXEC sp_addlinkedserver @server='YourLinkedServerName',
                        @srvproduct='Excel',
                        @provider='Microsoft.ACE.OLEDB.16.0',
                        @datasrc='C:\File1.xlsx'

Then you can query the Excel file using the 4-part Linked Server syntax (the DB name and schema part are shorthanded here with ...):

SELECT * FROM [YourLinkedServerName]...[Sheet1$]


How do you restore a DB to Multi-User mode when deadlocked by a sys process I have a database that is stuck in Single User mode after a failed restore process. I have checked the sysprocesses table and none of the processes have spid > 50. They are all sa processes. Using the following code returns a deadlock that I can not seem to clear:

ALTER DATABASE myDatabase SET MULTI_USER with NO_WAIT

Besides restarting the SQL Server and then spamming the Alter statement, is there a way for me to pull this out of single user mode? Thanks in advance for any help you can provide.

Apart from what Shawan referenced, another option is to use DAC ADMIN:ServerName
then issue below command (note that I am not using NO_WAIT)
ALTER DATABASE database SET MULTI_USER with rollback immediate

From BOL :

ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE
Specifies whether to roll back after the specified number of seconds or immediately.
NO_WAIT
Specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, the request will fail.


Collation by row, not by column Is it possible in SQL Server to set collation on individual rows in a table (as opposed to by column, database or server)?

If not, perhaps does anyone know if this is a planned feature for SQL Server 2016?

The only problem this solves is curiosity between two colleagues.

No, you cannot tell SQL Server to collate different rows differently. Collation applies to the column, database, or instance.

Within a query, it is possible that you could apply different collation rules, e.g. for comparisons, using a CASE expression. Assuming you can add a column to indicate what collation should be used for that row.

SELECT pk FROM dbo.basetable
  WHERE
  CASE
       WHEN collation = 1 THEN name COLLATE Albanian_BIN
       WHEN collation = 2 THEN name COLLATE Chinese_Taiwan_Bopomofo_90_BIN2
       WHEN collation = 3 THEN name COLLATE Norwegian_100_CS_AS_WS
       ...
  END = @parameter;

But this will not allow you to store data in different collations in the same column. If this is an actual need and not just some bet, you could consider some kind of EAV design, like:

CREATE TABLE dbo.eav
(
  pk   INT PRIMARY KEY FOREIGN KEY REFERENCES dbo.basetable,
  basecolumn SYSNAME,
  -- maybe collation # to use is specified here, or maybe in base table
  alb  NVARCHAR(255) COLLATE Albanian_BIN,
  chtw NVARCHAR(255) COLLATE Chinese_Taiwan_Bopomofo_90_BIN2,
  nor  NVARCHAR(255) COLLATE Norwegian_100_CS_AS_WS,
  ...
);


invalid db driver codeigniter I appear to have encountered an obscure codeigniter error, invalid db driver when I move from my remote server, to a local xampp server with default config! It's just a simple script using some mysql queries, the driver is mysqli, any ideas?

thanks!

Rather inexplicably, a codeigniter upgrade from 3.0.0 to 3.0.1 solved this!


How to reset MariaDB into a "fresh install" state? I had InnoDB corruption and managed to start the server in read only mode and perform a fresh backup using innodb_force_recovery=5.

This way of starting the service puts the databases in read only mode, even deletion is disallowed.

Is there an official procedure to reset the whole server into a fresh installed (or at least "empty") version?

And in case there isn't, then what are the correct uninstall/reinstall steps to make sure there will be no remaining residues of data that could generate problems in the future?

MariaDB can be reverted to the fresh state by removing its data files.

Say if you run MariaDB on a Debian you can do the next:

systemctl stop mysql
rm -rf /var/lib/mysql/*
systemctl start mysql

At the start if no datafiles exists MariaDB will recreate the internal scheme mysql.* with all default values. All leftovers like config and log files you have to clean up by hands.


How to query with the condition with subquery I have two tables table A and table B.

table B has a field called condition (where some sql conditions are stored).

Now i wanted to query like this

select *
from table_A  as a
where a.id = 1
  and (select b.condition from tableB as b)

I would like get the output for which of the condition satisfies.

Your question isn't terribly clear but I think what you are expecting is that if there is a row in TableB where column condition contains the text of a SQL clause like someField = 123 then the query will do:

SELECT * FROM table_A AS a
WHERE  a.id = 1
AND    (SELECT b.condition FROM tableB AS b)

would have the effect of:

SELECT * FROM table_A AS a
WHERE  a.id = 1
AND    (someField = 123)

This is not possible in the SQL standard nor directly possible in any SQL implementation that I am aware of. In MS SQL Server you could achieve this in a stored procedure by putting together ad-hoc SQL and running that via calls like EXEC (@VaraiableContainingSQL) and I presume the same is also possible in postgres' implementation of stored procedures, but it cannot be done in a simple single statement as described in the question.

As a side note: if those conditions are being provided by your users or any third party then this a very bad idea as it is an easy way to introduce SQL injection attacks. See https://www.owasp.org/index.php/SQL_Injection and https://xkcd.com/327/, amongst other references, for details and implications of this.

With regard to:

  table b itself is a foreign key for table A

... that sentence does not really make any sense. A table can not be a foreign key, a column (or group of columns) with a table can.

As suggested by a_horse_with_no_name you need to edit the question to provide much more detail of the schema (table layouts, their columns, keys and indexes), inputs, data, and intended outputs, to improve our ability to help you.


How to re-declare T-SQL variables if it already exist? Background:

I'm building a SQL command to be executed on the server which involves a lot of accessing store procedure and declaring variables which are used as input to other stored procedures. This is all generated on the client side using C# and send as a big package. I have to do it this way since the ping times are very long since the client will be located very remotely with bad connections and I don't want to send many packages and starting building up a backlog due long response times since.

Currently no more than three client can work in parallel or they start to build up a backlog since the transfer speed of one transaction with about 200 store procedures takes about 35 seconds.

Question:

How can I declare a variable which I don't now if it already exists?
I could use a dictionary on the C# side but that sounds like the wrong way to do it to me. I want a declare which accepts re-declaring of that variable if it did exist.

You cannot redeclare variables, nor can you test to see if they have been declared (at least not that I have ever been able to find). BUT, you shouldn't need to do this anyway. If you know the variable names ahead of time, just declare them all at the beginning of the process. Then, use them throughout the script.

For example:

--------------------------------
-- BEGIN script header
--------------------------------
DECLARE @SomeVariable1 INT,
        @SomeVariable2 VARCHAR(50),
        ....;
        SomeVariableN DATETIME;
--------------------------------
-- END script header
--------------------------------

And then add 1 or more script blocks that use these variables:

---- Script Block 1 ---
EXEC dbo.MyProc @InputParam = 1, @OutputParam = @SomeVariable1 OUTPUT;
-----------------------

---- Script Block 2 ---
EXEC dbo.AnotherProc @InputParam = @SomeVariable1;
-----------------------

---- Script Block 3 ---
SET @SomeVariable2 = NULL; -- reset value if you don't want to carry over prior value
..do something here...
-----------------------


What data is causing an NVARCHAR/DATETIME conversion error? Application is getting this issue :
java.sql.SQLException: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value."

Back end SQL Server 2014. How can i resolve this issue?

If this results from you passing data INTO SQL Server, log your parameters before you issue the query.

If this results from you getting data OUT of SQL Server, remove your cast/convert functions and look at the data. Odds are you're casting/converting a field that isn't guaranteed to be a date.


How to properly index hstore tags column to faster search for keys I imported a large area of OpenStreetMap's planet.osm file into a postgresql database. The database contains a table called nodes. Each node has a geometry column called geom and a hstore column called tags.
I need to extract nodes along a line that have certain keys in the tags column. To do that I use the following query:

SELECT id, tags
FROM nodes
WHERE ST_DWithin(nodes.geom, ST_MakeLine('{$geom1}', '{$geom2}'), 0.001)
AND tags ? '{$type}';

$geom1 and $geom2 are geometries for start and end points of my line.
The $type variable contains the key I want to search for. Now, it can have one of the following values: 'historic' or 'tourist'.

The query given above works but it is too slow. I guess searching for a key in tags column takes too much time. I read about GIN and GIST indexes and I generated  a GIN index using the following query:

CREATE INDEX nodes_tags_idx ON nodes USING GIN(tags);

After creating the index I searched again for nodes using the same first query but there is no change in performance.

How can I properly use GIN and GIST to index tags column so I can faster search for nodes that have a certain key in tags column?

Thank you

You could try indexing the keys like in the following examples and see how that works:

CREATE INDEX nodes_tags_key_historic_idx on nodes ( (tags->'historic') );
CREATE INDEX nodes_tags_key_tourist_idx on nodes ( (tags->'tourist') );
ANALYZE nodes;

What does your query plan look like when run with EXPLAIN ANALYZE?

[edit]: Alternative indexes:

CREATE INDEX nodes_tags_key_tourist_idx2 on nodes ( (tags ? 'tourist') );
CREATE INDEX nodes_tags_key_historic_idx2 on nodes ( (tags ? 'historic') );

The latter shouldn't require any changes to your query.


Sql Server Full Backup Duration Increased Dramatically since last week My Full Backup Increased dramatically .before last week the time is about 3 hour and now the time is about 6 hour.in addition some query take long time to run.
(not all the query).I thought the problem is relate to disk I/o.
and I try to copy/past some files from one hard drive to another and it is done very quickly.(3 giga byte in 4-5 sec).and all the query work with temp table slow down.
is it relate to I/o or what..?

i found the solution.the network admin defrag Disk that cause Database fragmentation.by defrag databases the problem is gone


Putting Databases In Single User Mode I used SSMS to put several databases in Single User Mode.  I started doing a full backup on each database and it was working sucessfully until I got to the 3rd database and I'm receiving an error telling me the database is already open.  I stopped all Agent Jobs before I started and the DB is off the network.  I tried putting the database back in Multi-User mode and I'm being told it's already in use.

How do I disconnect whatever is connected to it?  I restarted the SQL Server instance with no luck.

You can run sp_who2 or equivalent to find a connection that's using that database. Assuming you're a sysadmin on the box (and, if you restarted the SQL Server instance, I hope you are), then you should be able to kill the process that is the current single user.

I would enter the kill command in a query window, along with the command to restore multi-user mode:

KILL 123;
ALTER DATABASE <your_db_name> SET MULTI_USER;

It is still possible that someone will manage to grab the connection in between the execution of the two commands (or that the current connection will need to roll back some changes); repeat as necessary.

NOTE: You are risking wiping out someone's changes to the DB in question by killing their connection. Please pay attention to the user in question, how they're connected, and whether it seems safe to kill their connection; you may want to contact them before killing their process.


Prevent a single query from hogging all resources I have a production database server running SQL Server 2008 R2. How can I keep a user query on one database from hogging all the system resources and bogging down all the databases on the server?

The specific issue I'm having is with a query with a long running cursor. Whenever the query is run, CPU usage goes to 100% on all cores and even simple queries on other databases slow way down or time out. Is this normal and what are the best practices to prevent this?

If you're using Enterprise Edition, you can use the Resource Governor (I wrote a whitepaper for Microsoft on this topic a few years ago). This is especially effective if you can identify just this user (by SUSER_SNAME() or HOST_NAME()). Unfortunately this can't be used to place restrictions on just the one query - it is implemented at login time and affects all queries for the life of their session, but you can certainly constrain their CPU in general. Note that in SQL Server 2008 & R2 this constraint is only enforced when there is other contention on the box. In SQL Server 2012 there is a new setting (CAP_CPU_PERCENT) that allows you to constrain CPU for a resource pool even when they're the only one on the box.

Another way (or an additional way) to attack the problem, assuming you have control over the query text itself (e.g. it's not ad hoc being assembled by the user or their app), is to have that specific query always run with OPTION (MAXDOP 1) - it will still cause high CPU, and the query most likely will take longer, but you can use that setting to limit the number of schedulers it affects. So on a 16-core box, you would only see one CPU spiking as a direct result of this specific query.


If you don't rollback a transaction will the change still be made? Say I am running a query

begin tran

update users
set name = 'Jimmy'
where name = 'john'

If I DON'T rollback the transaction, will these changes still be made, will it throw an error or will it act as a rollback anyway?

You have to understand what a transaction is - It is a single unit of work. It is ALL or NOTHING (follows ACID properties) and this guarantees the database consistency.

  If I DON'T rollback the transaction, will these changes still be made

The changes will be made only if you commit. SQL server will record all the changes to transaction log and once you commit, they are hardened to the data file.

If you don't commit, then your transaction will remain OPEN indefinitely - which you can see in sys.dm_tran_active_transactions / sys.dm_tran_database_transactions / sys.dm_tran_session_transactions or using DBCC OPENTRAN.

Also, what you have initiated is an explicit transaction, which should be explicitly ended with a COMMIT or ROLLBACK statement.

Also read - Is it a bad practice to always create a transaction?


Does innodb_buffer_pool_size accept "*GB" values? I thinks innodb_buffer_pool_size is specified in bytes, would typing in values with unit (i.e 1024MB or 1GB) work? Has anyone already tried this?

Specs:

MySQL 5.6

Using GB or MB wont work. However, any variable that you needs to be defined as a numeric value can use the shorthand byte suffixes K, M or G.

From the docs:

  For variables that take a numeric value, the value can be given with a
  suffix of K, M, or G (either uppercase or lowercase) to indicate a
  multiplier of 1024, 1024^2 or 1024^3. (For example, when used to set
  max_allowed_packet, the suffixes indicate units of kilobytes,
  megabytes, or gigabytes.)

http://dev.mysql.com/doc/refman/5.6/en/program-variables.html

So, if you wanted 1024MB then you would use 1024M or 1G


Sending an async notification to another database in PostgreSQL Is there any way I can send a NOTIFY to a database other than the one that my connection has specified?

[CLARIFICATION]

Consider we have two databases within one instance of PostgreSQL. If you are connected to one of them and you are LISTENing and you won't be able to hear the notifications made on the other database (at least to my experience).

My scenario is:

I want to connect to one unique centeral database within each instance of PostgreSQL from my application. Then, having an arbitrary number of databases on the same instance, I'll be sending notifications to my application, no matter which database has triggered it and I won't need to establish a connection to each database separately.

If the other database has established a connection to the database to which you are connected and has issued a LISTEN, then you send a NOTIFY to it the same way you would send one to anything else using the NOTIFY/LISTEN interface.

If the other database has not volunteered to LISTEN, then NOTIFY is not the right tool for the job.  You could use dblink or FDW to insert records into a queue table on the other database.  Or, insert them into a local queue table, and have the other database fetch them when it wants to.


Fastest way to insert data into mysql from csv I have a dataset of 233,902,846 rows in a csv file.
I want to load the data into mysql table. What is the most efficient way of transferring the data so that data insertion is the fastest. actually simple mysqlimport will take around 4-5 days for the complete insertion.

Regards
Mona

I would normally use the following statement:

LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\Users\\Desktop\\nameoffile.csv' REPLACE INTO TABLE `tmp_table` CHARACTER SET latin1 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';

You should take a look at this page too.
http://derwiki.tumblr.com/post/24490758395/loading-half-a-billion-rows-into-mysql


SQL User with only access to maintain indexes and statistics I need to have an AD user account with access to all databases on a specific instance of SQL Server 2008 R2, and the only thing that user will be doing is;

Reorganize indexes
Database integrity
Update stats

The database admin here says for this I need to give dbo access to this user on all databases; is that really true or is there a better / more secure way of giving this specific access to one user?

Create stored procedures that do these tasks for them - these can be set to execute as owner and the user account can just be granted execute rights on the stored procedures. It means they'll have to learn the interface to your stored procedures (so document them well or give them view definition rights to them also).


how to synchronize Oracle data with SQL Server I have an Oracle 11g database in one machine with unique a IP, and a SQL Server 2008 database in another machine.

A table (A) in Oracle has some fields (not all) which are the same as table (B) in SQL Server.

Now I want to synchronize the fields' values in A with the fields' values in B. That is (maybe it is wordy!), when the value changes in A, so it is automatically updated in B without any manual operation in SQL Server!

How to get this done?

Set up a database link between Oracle and SQL server, then set up a trigger in the Oracle database to update the SQL Server table whenever the oracle table changes.


SQL Server Service Account Configuration - one account per Server? Planning to re-create new service accounts for our Sql-Servers, I wondered if - from a security perspective - it maybe better to have one domain account per Service and per Server or to create one domain-account per service only and re-use those on the different servers?

DOMAIN\Service-SSRS
DOMAIN\Service-SSAS
DOMAIN\Service-SSIS

vs.

DOMAIN\Service-SQL01-SSRS
DOMAIN\Service-SQL01-SSAS
DOMAIN\Service-SQL01-SSIS
DOMAIN\Service-SQL02-SSRS
DOMAIN\Service-SQL02-SSAS
DOMAIN\Service-SQL02-SSIS
etc.

We are doing this because we want to have a uniform and consistent configuration on all servers, something that is not yet in place today. I feel like having single accounts for every server might allow more flexibility and so is counterproductive...

Are there specific pros / cons? Is there a lot of usage of having separated service accounts for every server?

Separate service accounts for each server and for each service will restrict any issues to just one server and just one service in the following cases:

Service account getting compromised
Service account getting locked out
Service account password changed
Service account getting disabled/deleted/renamed by mistake

Imagine what would happen with a single service account if you entered the service credentials wrong a handful of times (scripts are great for this): you would lock out the account and tear down the whole SQL Server farm. Not good.


Why does 3NF Normal form allow transitive dependency of prime attributes? I'm a beginner in Relational Design, and I came across the definitions of 3NF and BCNF.

According to 3NF, Any non-trivial functional dependency X-> A shouldn't exist unless X is a super key or A is a prime attribute (Where as the BCNF doesn't allow the latter).

Is there a reason for this? How does this interpret? (i.e What does it mean?)

Normal forms are used to eliminate or at least reduce redundancy in data, as well as to eliminate the so called insertion/deletion anomalies.

The BCNF eliminates redundancies and anomalies, but decomposing a relation in BCNF sometimes has the unpleasant effect of causing the loss of one or more functional dependencies during the process.

For this reason the 3NF is used instead of the BCNF in practice, since a decomposition of a relation in this form always mantains data and dependencies, and reduces anomalies and redundancy (even if to a lesser extent that the BCNF).

Another reason is that the decomposition in 3NF can be obtained with a polynomial-time algorithm, while the decomposition in BCNF requires an exponential algorithm.


What do the mysqlds do in my mysql database? I build a wordpress on my localhost,and local pc can't connect with internet.
To login mysql database with command.

mysql -u root -p
mysql> show processlist;
+-----+------+-----------+------------+---------+------+-------+------------------+
| Id  | User | Host      | db         | Command | Time | State | Info             |
+-----+------+-----------+------------+---------+------+-------+------------------+
| 385 | root | localhost | wpdatabase | Query   |    0 | NULL  | show processlist |
+-----+------+-----------+------------+---------+------+-------+------------------+
1 row in set (0.00 sec)

sudo pstree -pl  795
mysqld_safe(795)───mysqld(1188)─┬─{mysqld}(1286)
                                ├─{mysqld}(1287)
                                ├─{mysqld}(1288)
                                ├─{mysqld}(1289)
                                ├─{mysqld}(1290)
                                ├─{mysqld}(1291)
                                ├─{mysqld}(1292)
                                ├─{mysqld}(1293)
                                ├─{mysqld}(1294)
                                ├─{mysqld}(1295)
                                ├─{mysqld}(1385)
                                ├─{mysqld}(1386)
                                ├─{mysqld}(1387)
                                ├─{mysqld}(1388)
                                ├─{mysqld}(1403)
                                ├─{mysqld}(1420)
                                ├─{mysqld}(3435)
                                └─{mysqld}(22796)

There are so many mysqlds ,why show processlist can't list it?

I see 18 mysqld's there.  I'll bet SHOW VARIABLES LIKE 'thread_cache_size'; gives you at least 18.  And, I'll bet that, at some point since startup, you had 18 clients running.

So why doesn't PROCESSLIST show 18 processes/threads?  Because wait_timeout killed off 17 of them.

Bottom line:  Nothing to worry about.


Can SQL Server R Services be enabled on a readable secondary? To avoid excess load on the primary SQL Server, I'd like to install and enable R Services on a secondary replica of the primary server. Is this possible?

Also, can I push computations from a remote R client onto the secondary server with R Services enabled?

Does this have any impact on the primary at all?

Readable Secondary is a feature of Always On Availability Groups and you can easily create one of these in Azure using the template from the gallery.

You have to do a little bit of extra config, to configure the read-only routing, something like this:

-- Configure the READ_ONLY_ROUTING_URL
ALTER AVAILABILITY GROUP [Contoso-ag] MODIFY REPLICA ON N'sqlserver-1'
WITH ( SECONDARY_ROLE( READ_ONLY_ROUTING_URL = N'TCP://sqlserver-1.contoso.com:1433') )

ALTER AVAILABILITY GROUP [Contoso-ag] MODIFY REPLICA ON N'sqlserver-0'
WITH ( SECONDARY_ROLE( READ_ONLY_ROUTING_URL = N'TCP://sqlserver-0.contoso.com:1433') )

-- Configure the READ_ONLY_ROUTING_LIST
ALTER AVAILABILITY GROUP [Contoso-ag] MODIFY REPLICA ON N'sqlserver-1'
WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(N'sqlserver-0', N'sqlserver-1')))

ALTER AVAILABILITY GROUP [Contoso-ag] MODIFY REPLICA ON N'sqlserver-0'
WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(N'sqlserver-1', N'sqlserver-0')))

Connecting to the readable secondary is then a matter of using the ApplicationIntent=ReadOnly facet in your connection string.  I have not tried doing this from R but imagine you could.  There's an easy way to verify whether or not you are connected to the secondary using sqlcmd which I used recently here.

As far as the push compute goes, this is not yet known.  Looking on the Always On Availability Groups: Interoperability R Services is not listed.

I would personally be surprised if these features worked together in that way as R is so new, but it's a good thought.  If it turns out it doesn't work, you could make it a feature request.  I would also say though, when you connect to an AG listener and request read-only routing, it is only that, a request.  As AOAG is a high-availability feature, if the secondary is down, you would connect to the primary.  So let's say you only enabled R Services on the secondary, you create a situation where these scripts failed when the secondary was not available.

Unfortunately I can't test this out myself as I've burned all my Azure credits this month answering some question for @BrentO : )  Maybe later in the month.


How do I host and separate multiple projects on one Oracle Database? From Difference between database vs user vs schema

I'm trying to understand the concepts of the schema, user and database.

My current understanding is you cannot create a schema separately.
You can create a user and a schema will be automatically created.

Then my question is, what if you want to create 3 users and only use one schema by granting privileges to only one schema? Will there be 2 empty, unused schemata?

For example, in a project called "Killer Web", I have 3 application developers and I am the DBA.

So I created 3 users for each developer, dev1, dev2 and dev3.
And I granted on these three devs, access and other privileges to the actual schema on which the 3 devs will actually work.
Would that create 3 schemata, empty and unused?

If I wanted to create a schema called "KillerWeb", would I have had to create a "user" called "KillerWeb"?

How do I host multiple projects on one Oracle database and separate them?

In MySQL, I would just create a database for each project. (Not sure if this is the correct practice though.)

What if you want to create 3 users and only use one schema by granting privileges to only one schema? Will there be 2 empty, unused schemata?
[...] Would that create 3 schemata, empty and unused?

Essentially yes. You'll use the users to log in to the database, but some of them won't have any objects, i.e. they'll have empty schema. (This is not uncommon at all.)
But none of this "creates empty schemas" in a technical sense. A schema is just a concept in Oracle, it doesn't have a concrete existence. You have users, and they own objects. Users and objects exist in the database in a very concrete sense, "schema" does not. It is just a term used to describe a user and its objects.

If I wanted to create a schema called "KillerWeb", would I have had to create a "user" called "KillerWeb"?

Yes, you need to create that user, even if no-one/nothing ever uses it to log in. (This not uncommon either. And you should actually prevent logins if no-one should.)

How do I host multiple projects on one Oracle database and separate them?

One option is creating a user for each application. By default, they won't be able to access each other's objects (unless you grant them the appropriate privileges).
Another option is Oracle's multitenant (additional cost, >= 12g only) option, a.k.a. pluggable databases, which gives you something in between one database with separate users and multiple, independent databases.
Or you could just give each app its own database.
How you decide between those depends on a lot of factors, including your budget, whether there is any shared data (and how you want/must share it), security considerations, backup/restore considerations, whether you suspect you'll want to split them up later, how you plan to scale, etc.


How Do I Use PowerShell to Get a SQL Server Backup Status? I need a power-shell script to obtain the following information Name of the database , LastBackupDate of the database and this information should be sent via an email converting the results to HTML format :) if we do have chance of getting the script done which will be .ps1 file I need to know how to run as a sql job.

Code gets backup history for all databases on a server and generates HTML report

Step 1:

   DECLARE @Body VARCHAR(MAX),
        @TableHead VARCHAR(MAX),
        @TableTail VARCHAR(MAX)
    SET NoCount ON ;

    SET @TableTail = '' ;

    SET @TableHead = '' + '' + '' + ''

    SELECT  @Body = ''

    SELECT  @Body = @Body + '
'
            + '
Database Name 	'
            + 'Backup Size	'
            + 'Time Taken	'
            + 'Backup Start Date	'
            + 'First LSN	'
            + 'Last LSN	'
            + 'Backup Type	'
            + 'Server Name	'
            + 'Recovery Model	'
            + 'Physical Device Name
'

    -- Backup History script of AAsim Adbullah
    -- http://blog.sqlauthority.com/2010/11/10/sql-server-get-database-backup-history-for-a-single-database/
    SELECT  @Body = @Body
            + ( SELECT  td = s.database_name, '',
                        td = CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14))+ ' ' + 'MB', '',
                        td = CAST(DATEDIFF(second, s.backup_start_date,
                                           s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds', '',
                        td = s.backup_start_date,  '',
                        td = CAST(s.first_lsn AS VARCHAR(50)), '',
                        td = CAST(s.last_lsn AS VARCHAR(50)), '',
                        td = CASE s.[type]
                               WHEN 'D' THEN 'Full'
                               WHEN 'I' THEN 'Differential'
                               WHEN 'L' THEN 'Transaction Log'
                             END,'',
                        td = s.server_name, '',
                        td = s.recovery_model,'',
                        td = m.physical_device_name,''
                FROM    msdb.dbo.backupset s
                        INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
                WHERE   s.backup_start_date >= DATEADD(dd, 0, DATEDIFF(dd, 0,GETDATE() - 1)) -- --- Date check for one day backup history information
                ORDER BY backup_start_date DESC,
                        backup_finish_date
              FOR
                XML PATH('tr')
              ) + '
'

    SET @Body = REPLACE(@Body, '_x0020_', SPACE(1))
    SET @Body = REPLACE(@Body, '_x003D_', '=')
    SET @Body = REPLACE(@Body, '
<TRRow>1</TRRow>', '
')
    SET @Body = REPLACE(@Body, '<TRRow>0</TRRow>', '')

    SELECT  @Body = @TableHead + @Body + @TableTail

    IF ( @Body IS NULL )
        BEGIN
                EXEC msdb.dbo.sp_send_dbmail @recipients='sqlcache@blogspot.com',  -- Add Valid Email Id
                @subject = 'Backup History Information',
                @profile_name = 'AA.SC', -- Change Profile Name
                @body = 'No Backup History Found for past 1 Day',
                @body_format = 'HTML' ;

        END
    ELSE
    Begin
        EXEC msdb.dbo.sp_send_dbmail @recipients='sqlcache@blogspot.com', -- Add Valid Email Id
                @subject = 'Backup History Information',
                @profile_name = 'AA.SC', -- Change Profile Name
                @body = @Body,
                @body_format = 'HTML' ;
    END

Step 2:
create a new job add this code in step and schedule it according to your requirement


Database is slow once in a while I am executing a web service for 100 times. The average response time of this service is 1.5 seconds. But when I look at the response time of each execution individually, there are a few executions (at least 10) which are taking beyond 15 seconds to respond. The data for each request is exactly the same as any previous request (just appending a dynamic number to name...etc).

It certainly looks like a database issue, because my database is responding differently each time we execute a same query again and again. I am not able to understand why my database is behaving like this. My database is oracle 11g. I have experienced this issue with different UI flows as well previously.

Edited to add collected metrics.

Yes, statspack is an easy free way to go. First thing to look at is the top 5 timed events. Unfortunately statspack won't tell you the problem for specific sql statements. To see where and how particular sql statements are spending their time, you need ASH. A graphical interface to ASH along with the collection of ASH (ie no perf pack license needed)  is available with WASH.
http://www.oraclerealworld.com/web-ash-w-ash/
I'd recommend lab128 which has a 2 day free trial
http://www.lab128.com/lab128_download.html
Lab128 can collect ASH itself or optionally use the ASH collection from the performance pack.
One you have on of those running, see where you are spending time - CPU, I/O, Locks or other stuff.
In both the above tools you can see where individual SQL spend time to help you distinguish a problem in one SQL vs loosing that SQLs problems in the overall stats of the database.


How can I conditionally summarize multiple rows in Microsoft Access? I have a table of data, in Microsoft Access 2003, listing multiple rows for each person.

I want to conditionally summarize a subset of these rows for each person, for instance, if entry for columnA = "Hats".

I've made summary tables for each (with the conditional checking) and I want to UNION the master table and the summary tables together, however each table has a different number of columns. Am I going to have to redesign my summary tables so they match the columns in the master, or is there a clever way of doing this?

I've tried to join them first (do I even need to do this?), and also to UNION CORRESPONDING them together, but MS Access doesn't understand that command it seems.

How should I do this?

Edit, here is a mockup of my master table data:

ResID| Name|  Baselocation|  Destination| CustomerID  |   Project ID    p1    p2    p3
---------------------------------------------------------------------------------
001  |  Bob|  London      |  D-London   | Company     |   "BaseWork"    X%    y%    z%
                             N/A        | myOwnCompany|   "Overhead"    x%    y%    z%
                             D-NotLondon| Company     |    Project      x%    y%    z%
                             D-London   | Company     |    Project2     x%    y%    z%

I'd also like to see the ERD (Entity Relationship Diagram) to get a better understanding of your problem.

I'm thinking if you're creating a report to present the data in this fashion, you can setup a grouping based on an expression, either using built-in functions or use a user-defined function in a VBA module.


is it possible to set engine for database (not the tables)? There are about 250 tables in my database and they all have InnoDB engine. I imported them into a server.

They were imported and were displayed in the tree structure of phpmyadmin (under the database name), but when I clicked on them MySQL reported the error that table doesn't exist!!

From this link ,I find out that database engine and tables engine are not the same. is it possible to set engine for database itself (not the tables)? if yes how ?

update :

I also tried to create tables manually (by SQL Query and InnoDB engine) but reported the error that can't create table, I changed InnoDB to MyISAM and problem solved!

There is no "database" engine setting. A database can have tables using different engines.

There are two variables that define which engine will be used when a table is created with a CREATE TABLE that does not include an ENGINE = EngineName specification. The variables are the (global and session) default_storage_engine. The global is configured at startup (from my.cnf or command line) and the session variable can be setup during each session.


Only LIMIT rows with specific number Sorry for the title, As I'm not that experienced I had a real hard time trying to come up with a proper title.

So I have this query to fetch all the comments of a specific topic.
Now for my paging I want to limit it down to 20 rows, but ONLY for posts with pid (parent_id) zero.

SELECT *, c.id AS cid, u.id AS userid
FROM comments c
INNER JOIN users u ON u.id = c.uid
WHERE c.tid = :id ORDER BY c.posted ASC LIMIT 20

How do I go about limiting the posts by 20 WHERE pid = 0 without removing every row with pid = 1?

If I add:

 WHERE c.tid = :id AND c.pid = 0

I'll just get all comments without a pid.

So, for example, it shows 100 comments, but only 20 that has pid = 0 in the database. That's what I want.

Maybe you could try with:

WHERE c.tid = :id AND c.pid IN(0,1)

or

WHERE c.tid = :id AND c.pid <=1

but I am not sure what you want to get. Maybe an UNION is more appropriate.

-- only 20 with c.pid = 0
(SELECT *, c.id AS cid, u.id AS userid
FROM comments c INNER JOIN users u ON u.id = c.uid
WHERE c.tid = :id AND c.pid = 0
LIMIT 20)
UNION
(SELECT *, c.id AS cid, u.id AS userid
FROM comments c INNER JOIN users u ON u.id = c.uid
WHERE c.tid = :id AND c.pid = 1)
ORDER BY c.posted ASC


Select a distinct row I have a MySQL table:

o_id | Salesperson |   fax   | Pincode
1      Sandy         2110271   12011
2      Sandy         2452356   24523
3      Ricky         1023564   45510

I need to get the following result:

Salesperson |   fax   | Pincode
Sandy         2452356   24523
Ricky         1023564   45510

(Note: I don't need o_id column to be displayed)

I have tried:

select distinct salesperson,faxno,pin
from OPPORTUNITY

and:

select salesperson,faxno,pincode
from OPPORTUNITY
Group By (salesperson)

...but still I am getting:

Salesperson |   fax   | Pincode
Sandy         2110271   12011
Ricky         1023564   45510

I have tried a lot of scenarios present on the web but still not able to achieve the expected result.

Is there any property or function that will give "Sandy" -  whose o_id is max.

Example:

Select distinct sales, faxno, pincode
from opportunity where o_id= max(o_id)

Although above code is not working, but anything like that can help?

The row:

Sandy         2452356   24523

is preferred because it has the largest o_id. You can pick the largest o_id per salesperson via:

select max(o_id), salesperson
from T
group by salesperson

An important property of SQL is that it is closed, i.e. a result of a query is a relation (table). So we can simply join this with the original table:

select x.o_id, x.Salesperson, x.fax, x.Pincode
from T as x
join (
    select max(o_id) as o_id, salesperson
    from T
    group by salesperson
) as y
    on x.o_id = y.o_id
   and x.salesperson = y.salesperson

Even if question does not say so explicitly, there is reason to believe that salesperson is functionally dependent of o_id. If that is the case it is sufficient to join by o_id alone:

select x.o_id, x.Salesperson, x.fax, x.Pincode
from T as x
join (
    select max(o_id) as o_id, salesperson
    from T
    group by salesperson
) as y
    on x.o_id = y.o_id

In SQL2003, window functions were introduced. They are a perfect fit for this kind of query. I have heard the next version of MySQL will support them, but I guess you are not using that.

You also mention this construction:

select salesperson,faxno,pincode
from OPPORTUNITY
Group By salesperson

It is probably the construction in MySQL that causes the most confusion. It is invalid in most (all?) other products, because the result is non-deterministic or random. What happens is that MySQL randomly picks a faxno, pincode for each salesperson.