module_end extended event enough for stored procedure logging? Using extended events I plan to capture stored procedure executions particular the time of execution, duration and who executed it.

Is the module_end event enough to capture this data? In a test session I have created I don't see what more the module_start event would provide though would a module_end event occur if a stored procedure execution is terminated?

To answer my own question - when a stored procedure execution is aborted then this is logged as an event, in which case I don't see the need to capture both module_start and module_end events for my purpose.


What are some best monitoring tools available for mysql? I have a setup a linux mysql server and would like to monitor it. I found percona plugins for nagios and cacti. Are there any other tools available?

Have you looked into MySQL Enterprise Monitor? Or are you only looking for freeware?

EDIT: You could also use this comparison chart from Wikipedia to pick one based on your needs:

http://en.wikipedia.org/wiki/Comparison_of_network_monitoring_systems


can i backup multiple db2 databases at the same time? in db2 9.5 LUW i have one db instance to which i just added a second database. we don't have TSM or multiple partitions which is what all the documentation seems to be about when i search the web for this.

can i run an online backup db for the first database and immediately start an online backup db for the second database and let them run simultaneously? or would i have to let the first finish before starting the second? thanks.

Yes, you can back up multiple databases at the same time.

Whether you want to do so or not depends on how much impact to database performance you are willing to accept and how long your backups run. You can control this somewhat by throttling your backup(s).


ODBC connection fail when running AD account for SQL Server services I am following up a question from Remote backup to folder path in domain I asked earlier. I could not get my proxy SQL Server Agent user to work to back up to a remote location and have given up for the time being due to pressure to move on. However, I still want to back up to remote folders.

To that end I have created an AD user and put them into the local server which runs the SQL Server instance Admin group and given the AD user full permission to the remote folder and can browse and create a file at that location.

I then tried to change the MS Service from "Local Service" to the new AD user, using SQL Server 2008 R2 Configuration Manager this failed due to a log on error? Even after a number of tries and resetting the User password and then pasting it into the Configuration Manager this would not work.

I resorted to using the Windows Services to change the log on and all was fine. The instance worked ok and the remote backups worked as well. However, we then noticed a ODBC datasource issue from our TS into the SQL Server, it kept failing with "HY000 cannot generate sspi context", we Googled this and found a fix is to put the AD User into the Domain Administrator Group, which we did and the error disappeared. However, I have defeated my objective that was to use a standard user to run SQL Server services to reduce the security risk!

My question: is there another way to set the standard user / give it different permissions so it does not have to be a Domain Administrator so that ODBC datasource connections work? I am also wondering why I could not change the Service via SQL Server Configuration Manager

As you have found out the SQL Server Service runs the backup but not the user giving the backup command.

Changing the service user should be done using the SQL Server configuration manager which in the background sets correct permissions for the service account, all of which are documented on MSDN, giving the user running the service Administrative rights on the computer will make it possible to run the  service but is not a good security practice.

You have to be sure to run the correct version of the Configuration manager so try running MMC.exe and adding the correct snapin for your version of SQLServer so check that first and if it failes reregister the snapin by running in a command prompt the following command

mofcomp "%programfiles(x86)%\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

If this failes, use the information in the msdn article to manuallyl set the correct permissions.

Your SSPI context error is due to incorrect SPN for the sql server, The SQL server creates a SPN automatically when  you start it and the domain admin has permissions to to that which fixes the SSPI error. After you change the service account for the server to a normal user download the Microsoft Kerberos configuration manager for SQL Server and make sure to create the correct SPN


MySQL service stops after trying to grant privileges to a user I am using MySQL 5.6, I was trying to create a super user for specific database but I got

ERROR 1396 (HY000): Operation CREATE USER failed for 'superuser'@'%'

Then I thought maybe there is already a user with the same name and password. I flushed privileges and run the following query:

grant all privileges on db1.* to 'superuser'@'localhost'

After that query I get: ERROR 2013 (HY000): Lost connection to MySQL server during query
and MySQL service stops working.

I think your problem boils down to a misalignment of grants.

When you run this query

SELECT COUNT(1) column_count FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user';

You should the following number

If you get 43, MySQL 5.6
If you get 42, MySQL 5.5
If you get 39, MySQL 5.1
If you get 37, MySQL 5.0

It simply means you forgot one more upgrade step

# mysql_upgrade --upgrade-system-tables

This should fill in the missing columns.

I discussed this before

May 01, 2013 : Can I find out what version of MySQL from the data files?
Apr 24, 2014 : mysql: Restore All privileges to admin user (StackOverflow)

Since you said that mysql.user has 39, than means you did not run mysql_upgrade --upgrade-system-tables when you went from MySQL 5.1 to MySQL 5.5.

To be honest with you, I do not know if running mysql_upgrade --upgrade-system-tables in MySQL 5.6 will successfully realign mysql.user two versions back.

I would suggest making a copy by doing the following

net stop mysql
cd D:\MySQL\mysql
copy user.frm user1.frm
copy user.MYD user1.MYD
copy user.MYI user1.MYI
net start mysql

After making the copy, go ahead and run mysql_upgrade --upgrade-system-tables. Login to mysql and run SELECT COUNT(1) column_count FROM information_schema.columns WHERE table_schema='mysql' AND table_name='user'; again. Hopefully it will read 43.

If it does not, copy the user files back like this

net stop mysql
cd D:\MySQL\mysql
del user.*
move user1.frm user.frm
move user1.MYD user.MYD
move user1.MYI user.MYI
net start mysql

If you want to change the design manually, try the following:

Create mysql.user_new with MySQL 5.6 layout

CREATE TABLE mysql.user_new (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `plugin` char(64) COLLATE utf8_bin DEFAULT '',
  `authentication_string` text COLLATE utf8_bin,
  `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges';

Move the Grants from Old to New

INSERT INTO mysql.user_new
(Host,User,Password,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv,Reload_priv,Shutdown_priv,Process_priv,File_priv,
Grant_priv,References_priv,Index_priv,Alter_priv,Show_db_priv,Super_priv,
Create_tmp_table_priv,Lock_tables_priv,Execute_priv,Repl_slave_priv,
Repl_client_priv,Create_view_priv,Show_view_priv,Create_routine_priv,
Alter_routine_priv,Create_user_priv,Event_priv,Trigger_priv,ssl_type,
ssl_cipher,x509_issuer,x509_subject,max_questions,
max_updates,max_connections,max_user_connections)
SELECT
Host,User,Password,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv,Reload_priv,Shutdown_priv,Process_priv,File_priv,
Grant_priv,References_priv,Index_priv,Alter_priv,Show_db_priv,Super_priv,
Create_tmp_table_priv,Lock_tables_priv,Execute_priv,Repl_slave_priv,
Repl_client_priv,Create_view_priv,Show_view_priv,Create_routine_priv,
Alter_routine_priv,Create_user_priv,Event_priv,Trigger_priv,ssl_type,
ssl_cipher,x509_issuer,x509_subject,max_questions,
max_updates,max_connections,max_user_connections
FROM mysql.user;

Update the Create_tablespace_priv for root

UPDATE mysql.user_new SET Create_tablespace_priv='Y' WHERE user = 'root';

Flip the Switch

ALTER TABLE mysql.user RENAME mysql.user_old;
ALTER TABLE mysql.user_new RENAME mysql.user;
FLUSH PRIVILEGES;

Give it a Try and See if everything works !!!


Database design: how to handle the "archive" problem? I'm pretty sure a lot of applications, critical applications, banks and so on do this on a daily basis.

The idea behind all that is:

all the rows must have a history
all links must stay coherent
it should be easy to make requests to get "current" columns
clients who have bought obsolete things should still see what they've bought even though this product is not part of the catalogue anymore

and so on.

Here's what I want to do, and I'll explain the problems I'm facing.

All my tables will have those columns:

id
id_origin
date of creation
start date of validity
start end of validity

And here are the ideas for CRUD operations:

create = insert new row with id_origin = id, date of creation=now, start date of validity=now, end date of validity=null (= means it's the current active record)
update =

read = read all the records with end date of validity==null
update the "current" record end date of validity=null with end date of validity=now
create a new one with the new values, and end date of validity=null (= means it's the current active record)

delete = update the "current" record end date of validity=null with end date of validity=now

So here's my problem: with many-to-many associations. Let's take an example with values:

Table A (id = 1, id_origin = 1, start=now, end=null)
Table A_B (start=now, end=null, id_A = 1, id_B = 48)
Table B (id = 48, id_origin = 48, start=now, end=null)

Now I want to update table A, record id=1

I mark record id=1 with end=now
I insert a new value into table A and... damn I've lost my relation A_B unless I duplicate the relation, too... this would end to a table:
Table A (id = 1, id_origin = 1, start=now, end=now+8mn)
Table A (id = 2, id_origin = 1, start=now+8mn, end=null)
Table A_B (start=now, end=null, id_A = 1, id_B = 48)
Table A_B (start=now, end=null, id_A = 2, id_B = 48)
Table B (id = 48, id_origin = 48, start=now, end=null)

And... well I have another problem: the relation A_B: shall I mark (id_A = 1, id_B = 48) as obsolete or not (A - id=1 is obsolete, but not B - 48)?

How to deal with this?

I have to design this on a big scale: products, partners, and so on.

What is your experience on this? How would you do (how have you done)?

--
Edit

I've found this very interesting article, but it doesn't deal properly with "cascasding obsolescence" (= what I'm asking actually)

It's not clear to me if these requirements are for auditing purposes or just simple historical reference such as with CRM and shopping carts.

Either way, consider have an main and main_archive table for each major area where this is required.  "Main" will only have current / active entries whereas "main_archive" will have a copy of everything that ever goes into main.  Insert / update into main_archive can be a trigger from insert / update into main.  Deletes against main_archive can then run across a longer period of time, if ever.

For the referential issues such as Cust X bought Product Y, the easiest way to solve your referential concern of cust_archive -> product_archive is to never delete entries from product_archive.  Generally, churn should be much lower in that table so size shouldn't be too bad of a concern.

HTH.


Compare two users permissions on SQL Server database? Is it possible to compare permissions of two users on one SQL Server database?
Is there any simple query to do it?

I am asking this because I want to find out if user B has different or same level of permissions as user A on a database.

Here are two queries I have used to compare permissions between database users.
The first shows the database roles (if any) to which the database user has membership.  The second shows individual GRANT and DENY permissions.

--Database user and role memberships (if any).
SELECT u.name, r.name
FROM sys.database_principals u
LEFT JOIN sys.database_role_members rm
    ON rm.member_principal_id = u.principal_id
LEFT JOIN sys.database_principals r
    ON r.principal_id = rm.role_principal_id
WHERE u.type != 'R'
AND u.[name] = 'DBUserName';
GO

--Individual GRANTs and DENYs.
SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission],
    sec.class_desc Class, object_name(sec.major_id) [Securable],
    sec.major_id [Securible_Id]
FROM [sys].[database_permissions] sec
JOIN [sys].[database_principals] prin
    ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE prin.[name] = 'DBUserName'
ORDER BY [User], [Permission];
GO


View server login permission I've been working on giving a development team the ability to have read-only access to a SQL environment, I'm at the last step. I need them to be able to see users/logins and roles. I noticed that with view defintion granted on any given DB, it allows their login to view the users/roles for each DB, however even granted on master/msdb/model it does not allow the login to view the server wide logins/roles. What would be the best way to accomplish this? I have tried view defintion and I have tried view server state, neither has worked for server logins to be visible to the user.

Note: I don't want them to have any more access beyond that so I don't want them to be assigned a predefined role.

GRANT VIEW ANY DEFINITION will allow you to grant the ability to view any metadata on the server.  This includes security information at the server level.  This means that the user will be able to view other metadata as well, such as object definitions, which might be outside the scope of your goal.


SSIS - Best way to insert large number of rows So here's the scenario: I have an XML file, which is in size of 500GB, and with data of around 600 million rows (once on a database table). I'm using SSIS for the operation, and since it consumes a REALLY large amount of memory if I am to use an SSIS component (i.e.: XML Source), it might cause a timeout (correct me if I'm wrong, but as far as I know, using the components on SSIS loads the content of the XML into memory - with a file that big it will surely cause errors). My approach then is:

Use a Script Task to parse the XML data using XML Reader (XML Reader
by far is the best approach, since it parses the XML on a forward,
non-cached approach)
Insert the data on a DataTable
Every 500,000 rows on the DataTable, insert the contents to the
database using SqlBulkCopy, then clear the contents of the DataTable

My problem is, currently, I tried it to parse another file with the size of 200GB, and it's running on around 13.5M / 1 hour - and I don't know if it's still fine with that run time. It sure solves my problem - but it's not too elegant, I mean, there should be other ways.

I'm looking on other approaches, like:

Dividing the large XML files into small pieces of CSVs (around 20GB)
then use an SSIS Data Flow task
Use INSERT script every new rows

Can you help me do decide which is best? Or suggest any other solutions.

Every answer will be very much appreciated.

EDIT

I forgot to mention - my approach will be dynamic. I mean, there are many tables that will be populated with large sized XML files. So, using a Script Component as source might be not so useful, since I still need to define the output columns. But still, will give it a try.

Have you considered using a Data Flow Task with a Script Component acting as a source? You could just do an AddRow for each row you consume out of the file, and let SSIS handle the batches.


Why can't the schema owner create a new table? I have a DB user called STANMAN which owns the STANMAN schema.  When I try to create a table as STANMAN, it says STANMAN has insufficient privileges.  How could a schema-owner not already have sufficient privileges?  Naturally, when I try to GRANT ALTER or GRANT CREATE, I get "Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself."  What am I doing wrong?

If you are using SQL Server 2005 or later then to create an table in a schema you need both CREATE TABLE at the database level and ALTER at the schema level. Ownership of a schema covers the ALTER permission requirement but not the CREATE TABLE one.

CREATE TABLE is also granted by membership in the db_ddladmin fixed database role. This role is for backwards compatibility - do not use it for new security implementations.


MyISAM InnoDB performance MyISAM vs InnoDB

I keep reading how InnoDB is faster than MyISAM. I wanted to find out myself.

When I was using a forloop in PHP to fill my MySQL test database with 100,000 numbers using PDO driver.

|a|
+-+
|0|
|1|
|2|
|3|
|4|
...
|99999|

I find out that with InnoDB it went 95 records per second.
After a while I stopped my script cause going to 100,000 is just going to take too long.
Changing my table to MyISAM and re-running the script made a whopping 6000 records per second!
Both tables have no indexes.

How is it that the internet is claiming InnoDB is faster than MyISAM. Could it be that PDO is bottlenecking InnoDB?

This is often one of the most mythical subjects of MySQL. To separate fact from fiction, you need to remember the strengths and weaknesses of each Storage Engine. Each Storage Engine and application will govern the workload, read I/O, write I/0, and tuning options.

I have written many posts about how and when to pick one storage engine over the other:

Sep 20, 2011 : Best of MyISAM and InnoDB
May 03, 2012 : Which is faster, InnoDB or MyISAM?
Jul 05, 2012 : InnoDB vs MyISAM with many indexes
Sep 26, 2012 : Choosing MyISAM over InnoDB for these project requirements; and long term options

I have many more posts but you can start with these.


how to Export a document contain tables name, columns names and column description from SQL Server 2008? I created a db in sql server 2008 and I wrote for some columns description to describe them, What I want now is to find a way to export these tables design including the description for each column into a document to be able to discuss with the teams and be printed.

I've created an open source project to create MS SQL Server database documentation.

http://jeremykdev.github.io/SqlServerDatabaseDocumentationGenerator


Configure SSMS intellisense / web browser settings to accept UNC directory path inputs I would like to navigate to open a directory path from an in-line commented UNC path. SSMS integrates with Windows Explorer and Explorer allows UNC navigation from the address bar.

Indeed, when in the integrated browser, it is possible to navigate among UNC paths. HOWEVER, the click-thru functionality provided for http:/ prefaced URLs in the text editor of SSMS does not seem to exist for UNC paths. The Web Browser section of Options / Environment appears to open Internet Explorer options and the Text Editor / Plain Text / General section has the "Enable single-click URL navigation option", but does not appear to allow definition of what is detected as a URL.

Screenshots linked below

(added sample code below)

use [master]
go

-- http://www.google.com
-- \\servername\unc\BackupFolder\

select 'This query needs to be uploaded to the URL' Header
union
select 'and also saved for archiving at the UNC path'
union
select 'so is it possible to clickthrough to both?'

I would say you need to use file URIs:

For example:

You can use UNC paths in a file uri if you convert the \ to / like this:


Problems while building plpgsql_check on Windows I try to build plpgsql_check extension by @PavelStehule (Erwin Brandstetter recommended it to me in this answer) on Windows XP 32bit with Visual Studio 2010 (I used this article as a manual). However, I got following errors:

error LNK2001: unresolved external symbol _exec_get_datum_type
error LNK2001: unresolved external symbol _plpgsql_compile
error LNK2001: unresolved external symbol _plpgsql_build_datatype
error LNK2001: unresolved external symbol _plpgsql_stmt_typename
error LNK2001: unresolved external symbol _plpgsql_parser_setup

In addition, there are two IntelliSense errors: expected a ')' in plpgsql.h (lines 923 and 953 - both in datum-related functions) - I expect these to be false alerts (if they are important, how to solve them?).

After some googling I found that someone had a similar problem on OS X. The problem was that plpgsql.so either wasn't linked or didn't work. I have no plpgsql.so on my computer, but I have plpgsql.dll which should correspond to it. So I linked it and the error changed:

error LNK1107: invalid or corrupt file: cannot read at 0x2A8

This dynamic library is part of the PostgreSQL distribution, so it shouldn't be corrupt. However, I'm afraid that what Pavel wrote in the linked discussion applies here:

  It needs link against plpgsql.so. I didn't test it against clang - I
  remember a talk with Tom Lane, he was surprised so this code works and
  probably on some platforms can be unavailable.

  ...

  Nobody expected so plpgsql will be used from other extensions - and
  environment is not prepared for it :(

The worst case would be that plpgsql checker extension is simply not available under Windows, because it is too low-mana area for such a mighty magic. :-) If I just need a different approach, please point me to it.

You can find Adam Bartoszewicz's instructions here on Google Groups (also published on Pavel Stehule's blog):

  Download and install PostgreSQL 9.3.4 for Win32 from http://www.enterprisedb.com
  Download and install Microsoft Visual C++ 2010 Express
  Lern tutorial http://blog.2ndquadrant.com/compiling-postgresql-extensions-visual-studio-windows
  The plpgsql_check depends on plpgsql and we need to add plpgsql.lib to the library list. Unfortunately PostgreSQL 9.4.3 does not contain this library.
  Create a plpgsql.lib from plpgsql.dll as described in http://adrianhenke.wordpress.com/2008/12/05/create-lib-file-from-dll
  Change plpgsql_check.c file, add PGDLLEXPORT line before evry extension function, as described in http://blog.2ndquadrant.com/compiling-postgresql-extensions-visual-studio-windows
  ...
  PGDLLEXPORT
  Datum plpgsql_check_function_tb(PG_FUNCTION_ARGS);
  PGDLLEXPORT
  Datum plpgsql_check_function(PG_FUNCTION_ARGS);
  ...
  PGDLLEXPORT
  Datum
  plpgsql_check_function(PG_FUNCTION_ARGS)
  {
  Oid            funcoid = PG_GETARG_OID(0);
  ...
  PGDLLEXPORT
  Datum
  plpgsql_check_function_tb(PG_FUNCTION_ARGS)
  {
  Oid            funcoid = PG_GETARG_OID(0);
  ...
  Build plpgsql_check.dll
  Install plugin

  copy plpgsql_check.dll to PostgreSQL\9.3\lib
  copy plpgsql_check.control and plpgsql_check--0.8.sql to PostgreSQL\9.3\share\extension


How can I query on-premise SQL Server database from Azure SQL database using stored procedure? I have a Azure SQL database and it has a stored procedure from where I am trying to join a table which resides in on-premise SQL server database.
Essentially, I am trying to query a table which sits in on-premise SQL server's database.
Are there any options to make cross-server queries from Azure SQL database?
Edit: I looked into the linked servers as mentioned in the comments but it seems linked servers do not work with Azure SQL Database Singleton and Elastic pools. They are enabled for SQL Server Database Engine and Azure SQL Managed Instance.

Azure SQL database does not support linked servers or Polybase so you cannot directly virtualize the data from your on prem to your cloud db.
If the requirement is to access the data in the on-prem instance from the Azure SQL database and some delay on the latest data is acceptable, your options are:

Replicate the data from the on prem instance to the azure db. This is most "automated" way to do it. See https://docs.microsoft.com/en-us/azure/azure-sql/database/replication-to-sql-database

Use DataFactory, SSIS or other ingestion method to copy the data yourself.

Hope this helps.


T-SQL bulk insert fails on specific word "DENY" in string-data I have CSV files structured as string ; string ; string
First string is quoted with "". The other 2 strings are not.
I'm bulk inserting them into a table using nchar(x) for each of the 3 columns with a sufficiently large value for x.

BULK INSERT [table] FROM 'import.csv' WITH (FIELDTERMINATOR = ';')

This has been working for a couple of years.
Since a few days the import is giving issues on some rows. After investigation each of the failing rows gets a conversion/type mismatch error on the 3rd column.
The thing that has changed about the data is that in the middle of this 3rd string the text (DENY) is now present in each of the failing rows. This was previously never the case.

 Msg 4864, Level 16, State 1, Line 1
 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1019, column 3 (Rights).

Any ideas why the presence of the substring (DENY) causes this to happen ?
(I know it is a SQL reserved word, but why is it parsed in this position ?)
How to work around it ? I still need those rows to be imported, including that text.

Sample content of column 3:

(I)(OI)(CI)(F)
(I)(OI)(CI)(DENY)(WDAC,WO,S,DC)
(I)(OI)(CI)(F)

The 2nd line gives the error. The other 2 import fine.
(And in case you are wondering: Yes, it is ICACLS output that I am parsing.)

After some experimentation I found the following:

The word DENY is indeed a red herring. The problem is caused by the (xx,xx,xx,xx) sequence behind it.
For some obscure reason this causes errors if the bulk insert is run from a dynamic SQL string.
(I was using dynamic SQL as I need to iterate through a list of input filenames derived from another table. bulk insert doesn't allow the use of a variable for the input filename. So I had to programmatically generate each bulk insert statement and then EXEC() it.)

The bulk insert with the same input file works fine if run directly. BCP import of the same file is OK too.

If the column containing this sequence is NOT the last column of the input row, it works as expected.

As I have full control of the script that generates these files I could add a 4th dummy column containing just a =. This solved the issues with the bulk insert.

Obviously I still don't understand the root-cause of this. At least I have a workaround.

Microsoft states on MSDN numerous times that

  "bulk insert of CSV files is not supported, but may work in some
  cases"

They NEVER say why or what the exact problem is...
Makes me wonder if I have run into a known problem...


How to track changes between nightly MySQL database dumps I work with three MySQL databases (a, b & c) on a single server, that are nightly dumps of production databases. I have a fourth database (d) on that same server, that I have been given for creating new tables that connect and synthesize data from databases a, b & c.

I need to begin creating reports that reflect changes in databases a, b & c from one nightly dump to the next.

What would be the best way to capture the records from databases a, b & c, into database d, that:

example 1: records all of the unique records in tonight's dump that were not found in the last night's dump. (e.g. new account numbers in a table)

example 2: records all of the changes in value for records in tonight's dump that were different in last night's dump. (e.g. an account_status value that changes from 1 to 0).

I am new to data work and am hoping to find resources and direction on what the best practices are for such things?

Many thanks!

What you're referring to is called a delta, incremental, or differential backup, which is certainly natively supported by MySQL. You might want to consider backing up the deltas to an intermediary database that you can run your reports off of.  That data can then be added to the up-to-date DB.

I would suggest using an administration tool like Navicat for MySQL for setting up the backups.  It makes it easy to schedule and configure the backup rules.  You can compare and sync data between databases/schemas with detailed analytical wizard process.

As a bonus, it can produce really eye-catching reports.

Hope that helps!


Audit Admin rights use on SQL Server 2019 Being security conscious, I want to audit the use of admin rights. I would like to assign specific permissions to a user (following the concept of least privilege) that current has the SysAdmin role and I want to know what rights they are using above DBO to the database. Is there a good way to audit these actions so that I can grant those specific rights?

From the nature of the question, it sounds like you're observing the user discreetly before opting to revoke permissions. Definitely get an understanding of what the business is willing to allow the user to perform on the instance, especially if they aren't wearing the DBA hat in any capacity. If you're the DBA of the instance and you're responsible for its uptime, security and integrity, convey your security expectations with the business and ensure your users' logins are in compliance.
That being said, you may want to use a combination of Audits with both Server level and Database level specifications. The groups of actions that can be audited are vast but this will allow you the most precision if you want to ignore particular action groups. As for the indicating the principal (user/login), you can indicate the login you want to track in particular but it may be advisable to track the public role so you can monitor overall use of the high-level actions.

Another approach you can try is to start by looking at the default trace. It may not capture all events and its retention depends on disk space and the amount of qualifying events being generated but can be checked on the fly.
Both the above actions are passive with output checked periodically using SSMS and T-SQL. You can also interactively monitor with Extended Events sessions.


This simple query should work in your outdated version:

SELECT DISTINCT member_id
FROM   tbl t1
JOIN   tbl t2 USING (member_id)
JOIN   tbl t3 USING (member_id)
JOIN   tbl t4 USING (member_id)
JOIN   tbl t5 USING (member_id)
JOIN   tbl t6 USING (member_id)
JOIN   tbl t7 USING (member_id)
WHERE  t2.data_date = t1.data_date + 1
AND    t3.data_date = t1.data_date + 2
AND    t4.data_date = t1.data_date + 3
AND    t5.data_date = t1.data_date + 4
AND    t6.data_date = t1.data_date + 5
AND    t7.data_date = t1.data_date + 6;

In Postgres, you can just add integer to a date to get the next day.

And probably fast, too - as long as you don't have much longer streaks of entries producing many dupes in the first step.

Or:

SELECT DISTINCT member_id
FROM   tbl t1
JOIN   tbl t2 USING (member_id)
WHERE  t2.data_date BETWEEN t1.data_date + 1
                        AND t1.data_date + 6
GROUP  BY t1.member_id, t1.date
HAVING count(*) = 6;

Update to a current version of Postgres at the earliest opportunity.


How to write a query to get concatenated names of riders with the same weight? I want to retrieve a table with all Riders of the same Weight from a table named Rider, which is defined in this way:

CREATE TABLE Rider(
    name VARCHAR(30),
    weight REAL,
    PRIMARY KEY(name)
) ENGINE=innodb;

The data in it is:

Alan  55
Ana   55
Anne  50
Bill  65
David 60
Julia 55
Peter 65
Stan  60

I want a result like this:

50 Anne
55 Alan Ana Julia
60 David Stan
65 Bill Peter

I can't figure out how to do it. I can only manage to sort them all by weight with:

  SELECT weight, name
    FROM Rider
GROUP BY weight;

All help is greatly appreciated!

You're looking for GROUP_CONCAT:

SELECT weight, GROUP_CONCAT(name SEPARATOR ' ') as name_list
FROM Rider
GROUP BY weight


Update matching keywords from first table to second table in SQL I have two tables where 1st table is Englishgermankwds_tbl and 2nd is Kwd_UploadRecored the data of the tables is as follows

Table Englishgermankwds_tbl

English_Keywords                 German_Keywords
architecture                     Architektur
Arrival                          Ankunft
aspirations                      Lebensziel
attire                           Kleidung
Blueprint                        Technische Zeichnung
Carrying                         Tragen
caucasian appearance             Europäischer Abstammung
cheerful                         fröhlich
clothes                          Kleidung
color image                      Farbbild
day                              Tag
Development                      Entwicklung
differential focus               Geringe Tiefenschärfe
focus on foreground              Fokus auf den Vordergrund
front view                       Vorderansicht
Full Length                      Ganzkörperansicht
growth                           Wachstum
Happiness                        Glücklichsein,Glück
Hardhat                          Bauarbeiterhelm

Table Kwd_UploadRecored

ID  Primary_Kwd                             Sec_Kwd                                         Main_Kwd
1   Man,One Man,architecture,Boy            Arrival,Sigle Man , Business Man ,Male          aspirations,One Person
2   Woman,attire,Girl                       Girl,Girls,Female,Blueprint,Carrying,           Teenage Girl,Only Girls
3   Grand father,Man,caucasian appearance   cheerful, Family,Fatherhood,Family Member,      Male Parent,
4   Baby ,clothes,color image               growth,Babies,Child,Happiness                   Children,Toddlers,differential focus,

I want to update the all English keywords of Kwd_UploadRecored  to German  which is matched from the English keywords of  Table Englishgermankwds_tbl

I want the expected result like-  Kwd_UploadRecored table would be

ID  Primary_Kwd                                 Sec_Kwd                                         Main_Kwd
1   Man,One Man,Architektur,Boy                 Ankunft,Sigle Man , Business Man ,Male          Lebensziel,One Person
2   Woman,Kleidung,Girl                         Girl,Girls,Female,Technische Zeichnung,         Teenage Girl,Only Girls
3   Grand father,Man,Europäischer Abstammung    fröhlich, Family,Fatherhood,Family Member,      Male Parent,
4   Baby ,clothes,Farbbild                      Wachstum,Babies,Child,Glücklichsein             Children,Toddlers,differential focus,

Please help how to achieve this.

Refer the below solution for the Primary Keyword.

create table Kwd_UploadRecored(ID int, Primary_Kwd varchar(200),Sec_Kwd varchar(200))
insert into Kwd_UploadRecored values
(1,'Man,One Man,architecture,Boy','Arrival,Sigle Man , Business Man ,Male'),
(2,'Woman,attire,Girl','Girl,Girls,Female,Blueprint,Carrying,'),
(3,'Grand father,Man,caucasian appearance','cheerful, Family,Fatherhood,Family Member,'),
(4,'Baby ,clothes,color image','growth,Babies,Child,Happiness')

create table Englishgermankwds_tbl([English Keywords] varchar(50), [German Keywords] varchar(50))
insert into Englishgermankwds_tbl values
('architecture','Architektur'),
('Arrival','Ankunft'),
('clothes','Kleidung')

Query

;WITH ctePrimary AS (
    SELECT ID,CAST('<t>' + REPLACE(Primary_Kwd, ',', '</t><t>') + '</t>' AS XML) AS p
    FROM Kwd_UploadRecored
), ctePrimary1 AS (
    SELECT ID, x.value('.','VARCHAR(20)') AS Primary_Kwd
    FROM ctePrimary CROSS APPLY p.nodes('//t') a(x)
), ctePrimary2 AS ( --get German keyword if exist else English/Primary keyword
    SELECT A.ID,CASE
    WHEN B.[German Keywords] IS NULL THEN A.Primary_Kwd ELSE B.[German Keywords]
    END [German Keywords]
    FROM ctePrimary1 A LEFT OUTER JOIN Englishgermankwds_tbl B ON A.Primary_Kwd=B.[English Keywords]
), ctePrimaryGroup AS (
    SELECT ID,
       Stuff((SELECT ',' + [German Keywords]
              FROM   ctePrimary2
              WHERE  ID = a.ID
              FOR xml path('')), 1, 1, '') [German Keywords]
    FROM   ctePrimary2 a
    GROUP  BY  ID

)
Update Kwd_UploadRecored set Primary_Kwd = [German Keywords]
from Kwd_UploadRecored a inner join ctePrimaryGroup b on a.id=b.id


MariaDB special Characters I have a database on a hosting service that was recently move and upgraded without much notice which has caused me many problems. This problem, however, seems to arise from an upgrade from MySQL to MariaDB 5.5.

I have a table called “Gigs” which stores date, time, Location, description etc. of some upcoming gigs/concerts. It’s an old database I created in my youth, and so time is implemented as a string field.

The PHP code of the website generates a time string using a colon to separate hours and minutes, for example 16:40, it then generates an INSERT query like this example:

INSERT INTO Gigs VALUES (NULL, ‘16:40’, ‘Test’, ‘Location’, NULL, ‘2019-01-09’)

I had no problems with this query until the migration and upgrade of the hosting.

My PHP code was silently failing, so I attempted to execute this SQL on the database directly using phpMyAdmin, and this is the message I got back:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':40’, ‘Test’, ‘Location’, NULL, ‘2019-01-09’)' at line 1

It seems as though the colon is causing an issue? Is anybody able to help? I cannot find mention of colon as a special character in MariaDB documentation, nor any way to escape it.

The problem with the query is not to do with the colon, but rather the quote characters you're using. Instead of ‘, use '.


Measuring Availability Group synchronization lag - Clarifications Can I get some help on below

As I have started and learning about the Measuring of Availability Group synchronization lag between replicas inclusive of BCP , I want to understand and learn that does this query or dashboard show the values including FileStream synchronization lag ?

My Databases are with FileStream enabled .
I am using SQL Server 2014 SP2 AOAG.

Please help me understand the above point and kindly advise me with good link or query to measure the Sync lad between replicas.

I learned that the AG Dashboard is made up using the sys.dm_hadr_database_replica_states  results . Thanks for all help.

I got an answer what I am looking for :

filestream_send_rate (bigint )

The rate at which the FILESTREAM files are shipped to the secondary replica, in kilobytes (KB)/second.


CPU and Hard Drive Spike for 20-30 Minutes I'm collecting data on the performance of our Microsoft SQL 2008 server in preparation for moving it to Azure.  It's a very low load server (currently max concurrent transactions max out at 3), however, about once a day there's a CPU and hard drive activity spike for about 20-30 minutes.  Transaction activity during this time is the same as other times, however, the buffer cache hit ratio drops to almost 0 at the beginning of this spike (returns to 99+% quickly).  Free pages fluctuate during the spike and after the spike is done the number of free pages is higher than before.  Any way to find out what might be causing this spike?  Could this be some sort of garbage collection?  Anything specific I could monitor via Performance Monitor to give me a better idea of what's going on?

...once a day there's a CPU and hard drive activity spike for about 20-30 minutes...the buffer cache hit ratio drops to almost 0 at the beginning of this spike (returns to 99+% quickly).

This spike is almost certainly due to daily maintenance tasks - specifically, index rebuilds and DBCC CHECKDB.  These are very resource-intensive operations.  For instance, the buffer cache hit ratio change you mentioned is likely due to index rebuilds reading every page out of various indexes as they are processed.

  Any way to find out what might be causing this spike?

Look for SQL Server Agent jobs that are configured to start during this time period.  You can see an overview of scheduled jobs on the instance by double-clicking "Job Activity Monitor" in SSMS under the SQL Server Agent node.

You can also check for Maintenance Plans under the "Management" -> "Maintenance Plans" node (also in SSMS).

  I'm collecting data...in preparation for moving it to Azure

In terms of cost-saving in the cloud, it's possible you could forego some, or all, of the index maintenance.  One way to do this would be to use Ola Hallengren's Index and Statistics Maintenance procedure, which will skip rebuilds on indexes that are small or not-very-fragmented.


Why DMV does not show index on my table I am using this query to get information on an index on a table. I can see the index in SSMS.
select object_name(object_id), *
from sys.dm_db_index_usage_stats
where database_id =  DB_ID() and  object_name(object_id) = 'MyTableWithoutSchemaName';

But the result set is empty? How come?
Same thing happens if I use some of the queries from here: https://www.sqlshack.com/how-to-identify-and-monitor-unused-indexes-in-sql-server/

The index won't show up if you haven't touched it since you started your SQL Server. By touched I mean seeked, scanned, or modified.


How to re-assign two variables in a conditional UPDATE Consider a simple update as

SET @var1 = 20;
SET @var2 = 26;
UPDATE table1 SET
    col1=IF(@var1>50, @var1 := @var1-col4, @var1 := @var1+col5),
    col2=IF(@var2>50, @var2 := @var2-col6, @var2);

How we can change two variables in one condition. For example, consider that when @var1<50, we want to change not only @var1 := @var1+col5, but also re-assign @var2 := @var2 + 100. In fact, regardless of the condition 2, we want to increase @var2, if the first condition fails (the second part).

Is it possible to re-assign two user-variable in a IF STATEMENT or we need to add another IF STATEMENT?

This will be equivalent to

SET @var1 = 20;
SET @var2 = 26;
UPDATE table1 SET
    col1=IF(@var1>50, @var1 := @var1-col4, @var1 := @var1+col5),
    virtual_col=IF(@var1>50, NULL, @var2 := @var2+100),
    col2=IF(@var2>50, @var2 := @var2-col6, @var2);

Here we have created a useless column, not to collect the data, but to repeat the IF STATEMENT for the @var2. Obviously, it is not good from performance point of view, as we are running a useless UPDATE action.

It is possible to do two assignments in one IF statement.

There are several ways; I present one, using CASE statement:

SET @var1 = 20;
SET @var2 = 26;
UPDATE table1 SET
    col1=IF(
      @var1>50,
      @var1 := @var1-col4,
      CASE
        WHEN @var1 := @var1+col5 IS NULL THEN NULL
        WHEN @var2 := @var2 + 100 IS NULL THEN NULL
        ELSE @var1
      END
    ),
col2=IF(@var2>50, @var2 := @var2-col6, @var2);

The idea is to create a CASE statement where first WHEN clauses fail. They fail because we want them to, but along they way they get evaluated.

But in order for them to get evaluated, they must first computer the assignment expression, such as @var1 := @var1+col5 and @var2 := @var2 + 100.

CASE expressions work from first WHEN clause to the next one, and must work in this order.

Please consider reading more about it in my presentation of last year's Percona Live London (2011) conference. For referece, relevant slides are 13 - 15: Programmatic queries: things you can code with sql


avoid sys user, disable oracle log Hi and thanks for reading,

I'm really new to oracle, I have an oracle database, and it needs to have a permanent log, not even the sys user should be able to modify it or disable it. I read all the docs I could find on google, and from here:

https://docs.oracle.com/cd/B28359_01/server.111/b28337/tdpsg_auditing.htm#CEGIDHAG

I learnt how I can prevent de sys user to modify the log by using AUDIT_TRAIL  OS. Using OS value makes oracle write the log in a os file, in which the sys user does not have access. But it is required to avoid the sys user to disable the log, to prevent frauds even from the sysadmin that has the password for sys or system user. (If there is another way to prevent frauds from the sys admin, it is also welcome)

I have oracle 11g and there is no budget to buy anything else.

There is no way to limit the abilities of the SYS user. It is a super user account, similar to root on a UNIX operating system.

If a user changes the logging method, this act will be logged. The last OS-level log will show the act of changing it. So your DBA will not be able to disable logging without leaving a record.

Note that the auditing features of Oracle are greatly improved in version 12c. If you can upgrade, you probably should.


What data structure is used for MongoDB 3.x geospatial indexes? For the MongoDB 3.x geospatial features what structure is used for the indexes? B-tree? R-tree? X-tree? I can't find any information about the structures in their documentation

The general index format used by MongoDB's included storage engines as at 3.0.x (MMAPv1 and WiredTiger) is B-tree, however there are more nuances in the technical implementation.

MongoDB 3.0 introduced a storage engine API which separates the concerns of storage formats (i.e. data & index representations on disk and in memory) from the core server product. For example, WiredTiger supports index prefix compression, data compression, and more granular concurrency than MMAPv1.

It is expected that alternative storage engine implementations can (and will) differ in their underlying implementation of indexes and data storage to suit different workloads. For example, WiredTiger has support for LSM (Log Structured Merge-Trees) which is expected to be available in the MongoDB 3.2 production release. There are also alternative storage engines such as RocksDB (which uses LSM) and TokuMXse (which uses Tokutek's fractal tree storage).

I'm not aware of any pluggable storage engines for MongoDB that have been specifically optimized for storage of spatial data, but it is conceivable that one may be created.


Select multiple rows from one row based on column values I have a product table where the value of two columns can imply either one or two actual product results.  If column X = 1 AND Y = 0 return one result, if X = 0 and Y = 1 return one result, if X = 1 AND Y = 1 then return two results.

I want to write a query that will generate two row results for a single row based on the above rule. How can this be done with a SQL query?  Is a UNION the only way?

[EDIT based on comment]

TABLE: PRODUCT

ProductId | ABR | UBR
1 | 1 | 1
2 | 1 | 0
3 | 0 | 1
4 | 1 | 1
5 | 1 | 1

I want a SELECT statement that will generate 8 results from this set.  Basically one result for each instance of either ABR or UBR = 1.

So I would like my result to be:

ProductId | Edition
1 | ABR
1 | UBR
2 | ABR
3 | UBR
4 | ABR
4 | UBR
5 | ABR
5 | UBR

I know I can achieve this using a UNION but I was looking for something more elegant.

UNION does make sense.

You can put the UNION in a subquery which will neaten it up some:

DECLARE @prod TABLE (id int, abr int, ubr int)
INSERT INTO @prod
VALUES
(1 , 1 , 1),
(2 , 1 , 0),
(3 , 0 , 1),
(4 , 1 , 1),
(5 , 1 , 1)

SELECT id, 'ABR' as Ed
FROM @prod
WHERE abr = 1
UNION ALL
SELECT id, 'UBR' as Ed
FROM @prod
WHERE ubr = 1
ORDER BY id

You could also do an UNPIVOT I think but for this simple use case a UNION seems most efficient.


Check all login users list from back 3 months There is any way to check all database login users list and their activities from back 3 months in SQL Server?

I tried with SQL server Logs but I need more info about user activities from past 3 months. Can we get those users list? I didn't capture any data 3 months ago.

We are facing some DB status restoring mode for some reason, so we need to capture details of users at the time who login to server.

Unless you set up something (Server side trace, Profiler trace, Extended event trace, Security audit, Modify SQL Error log retention) and retain that information you will not find what you are looking for.

Based on the comments, I assume what you are looking for will be in the default trace. But unfortunately SQL Server only keeps 5 files, each 20MB. It is a fixed size buffer meaning older events get deleted.

This is a great resource to learn about default trace:

https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server-the-power-of-performance-and-security-auditing/

Starting SQL 2008 Extended event health session is running continuously unless someone turn it off.

SQL error log keeps a good deal of information. Again by default keep current and 6 older files. If you do not recycle error log and did not restart more than 6 times you might still get some information from there.

This answer by Aaron Bertrand go in details about what information is collected in these default collections.

If you need anything more than these you can always set up your own trace/audit.


Do I Need Constraints We created a database template that gets executed during the installation of a web application (that we also created).

After the application ran in a live scenario for a while, it inherited CONSTRAINTS without our intervention, meaning if we export the database, there are now constraints below some of the table creation blocks.

We want to give this application to someone else, and it requires the database template so they can install it (which we no longer have), and I would like to know if it is safe to remove these constraints (that we did not physically create), and give them a clean template, with only table creations?

Here is an example of one of them, there are roughly 30 tables and now roughly 10 mysterious constraints.

--
-- Table structure for table `blocked_period_rooms`
--

CREATE TABLE IF NOT EXISTS `blocked_period_rooms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `blocked_period_id` int(11) NOT NULL,
  `room_type_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `blocked_period_id` (`blocked_period_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

--
-- Constraints for table `blocked_period_rooms`
--

ALTER TABLE `blocked_period_rooms`
  ADD CONSTRAINT `blocked_period_rooms_ibfk_1` FOREIGN KEY (`blocked_period_id`) REFERENCES `blocked_periods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Here is another example.

ALTER TABLE `guest_ip_addresses`
  ADD UNIQUE KEY `guest_id` (`guest_id`,`ipaddress`),
  ADD KEY `for_foreignkey_of_guest` (`guest_id`);

ALTER TABLE `guest_ip_addresses`
  ADD CONSTRAINT `foreignkey_guest` FOREIGN KEY (`guest_id`) REFERENCES `guests` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

I'm not a database expert and don't fully understand how/why we're ending up with code in the database like this, any help is VERY appreciated.

You definitely want to keep database constraints.
They guard data integrity at the database level, not allowing for inconsistent data situations.

An example: your database and application are about students and lockers. So you have two tables: Students and Lockers, and the Locker table has a column linking/pointing/referencing/assigning a locker to a student.
Say your application allows to submit a new student AND at the same time he can choose a locker. That means two database actions and an email to the student:
1. Insert a new student with a new ID.
2. Update the Locker table record linking assigning it to the new student.

Logically, we would first insert the new Student and then update the Locker record, but your programmer is not so experienced and codes it the other way round, not very logical but nobody sees the illogical code and hey it works perfectly while he demo's it.

Now in the real world, as Murphey dictates, when a real user is using the app, halfway the process the web server crashes (database timeout, deadlock, etc...). So the locker record has been updated pointing it to the new Student ID (and the email got sent out), but the Student record never got inserted.
Oops, so you've ended up with a locker assigned to a non-existing student.

You want those database constraints to guard database integrity.


mysql error log issue I have mysql server running successfully.
The error log location is as..

mysql> show variables like '%log_erro%';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| log_error     | /var/log/mysql/err.log |
+---------------+------------------------+
1 row in set (0.00 sec)

The issue am facing is that when i am changing the error log location from above to some other location by changing from my.cnf  and then restarting the server but the error log is not getting generated and servers starts successfully.

EDIT

After restarting the server the value of the above variable get changed.
but i didn't found the error log in that location.

You may have placed the log-error option under the wrong header.

Put it here:

[mysqld_safe]
log-error=/var/log/mysql/err.log

Then, service mysql restart

I put it there because the mysql service in /etc/init.d calls mysqld_safe to call mysqld. In addition, /etc/init.d/mysql can pass paramaters to mysqld_safe as shown above.

Just to be safe, run this also

mkdir /var/log/mysql
chown -R mysql:mysql /var/log/mysql

Give it a Try !!!


Single select from template record oracle Due to lack of terminology in my head, let's say I've got 1 record from users table with these attributes :

Name   | Surname | Height | Weight
Person   X         6,2      190

How do I find other users with same height and weight as a person x with single select?

Update:

I have this on me now :

Select *
from users
where height = (select height from users where name = 'x')
and weight = (select weight from user where name = 'x');

Can I somehow fire this inner query once, because I'm reusing the same db record?

Try this

Select *
from your_table
where height = '6,2'
and weight = 190;

or

Select *
from your_table
where height = (select height from your_table where person = x)
and weight = (select weight from your table where person = x);


"Unresolved reference to User" after importing as a VS DB project I just imported an existing SQL Server 2008r2 production database into a VS 2013 database project.

I now get a number of errors along the lines of

Error       SQL71501: User: [mydbuser] has an unresolved reference to Login [mydbuser].

I don't really need my VS DB project to manage users, but I'm concerned that it would try to remove them upon deploy if they weren't there.

The files themselves are generated as

CREATE USER [mydbuser] FOR LOGIN [mydbuser];

or

CREATE USER [mydomainuser] FOR LOGIN [MYDOMAIN\mydomainuser];

The error marker shows that it's specifically for the Login. As that's a system-level object, I can understand it being outside the scope of the db project.

Is it preferred that I change them all to

CREATE USER [mydbuser] WITHOUT LOGIN;

or add the CREATE LOGIN clause to the beginning of each file?

Removing the login reference seems to be simpler and removing the users altogether would be the simplest.

I want to make sure that I'm using the tool the way it was intended. Will there be any issues in re-publishing any of those back to production? What is the proper procedure for adding a user/login via a project?

The easiest way is to not manage users through ssdt (most people don't). So you can just strip them out and don't deploy logins or users.

There are three ways:

the new options to ignore users/logins
write a deployment contributor to pull them out
use my deployment contributor http://agilesqlclub.Codeplex.com

Ed


MySQL: What do we mean by 0 value in in_use column of show open tables statement? If I run show open tables command, system return 73 records. But, in_use column value for every record is 0. What does this mean?

Are these tables available in cache but not used by any thread?

From the online manual

http://dev.mysql.com/doc/refman/5.6/en/show-open-tables.html

The number of table locks or lock requests there are for the table. For example,
if one client acquires a lock for a table using LOCK TABLE t1 WRITE, In_use will be 1.
If another client issues LOCK TABLE t1 WRITE while the table remains locked, the client
will block waiting for the lock, but the lock request causes In_use to be 2.

I have done this simple test:

mysql> show open tables like 'City';
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| world    | City  |      0 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

mysql> lock table world.City write;
Query OK, 0 rows affected (0.00 sec)

mysql> show open tables like 'City';
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| world    | City  |      1 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> show open tables like 'City';
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| world    | City  |      0 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

The number of rows of "show open tables" is about the one returned by "mysqladmin status". Mysql has a table cache that contains file descriptors for the table data file.

mysql@ubu-14:~$ mysqladmin status
Uptime: 1391  Threads: 4  Questions: 81  Slow queries: 0  Opens: 75
Flush tables: 1    Open tables: 68  Queries per second avg: 0.058

mysql> show open tables;
+--------------------+----------------------------------------------------+--------+-------------+
| Database           | Table                                              | In_use | Name_locked |
+--------------------+----------------------------------------------------+--------+-------------+
| performance_schema | events_waits_summary_by_user_by_event_name         |      0   |           0 |
+--------------------+----------------------------------------------------+--------+-------------+
75 rows in set (0.00 sec)


xb_stream_read_chunk(): wrong chunk magic at offset 0x0 I have taken a backup of mysql instance like this

innobackupex \
   --user=$MYUSER \
   --password=$MYPASS \
   --no-timestamp \
   --parallel=$DUMP_THREADS \
   --stream=xbstream \
   --slave-info \
   --extra-lsndir=$LSN_DIR \
   --tmpdir=$TMP_DIR \
   --no-lock \
   --safe-slave-backup

I am trying to restore this backup like this.

zcat backup_file.xbs.gz 2>/dev/null | xbstream -x -C /var/lib/mysql/

I am getting this following error.

xb_stream_read_chunk(): wrong chunk magic at offset 0x0.

I have tried all google suggestions, upgraded xtrabackup to the latest version.

Nothing seems to be working.

Would have preferred to answer using a comment, but I haven't enough points yet.
Disclosure: I work for Percona. I have put your question to our tech team, and they are not sure that you have created a xstream archive.

There are examples here

For xstream the backup would be something like this:

innobackupex --stream=xbstream /root/backup/ > /root/backup/backup.xbstream

and then the extract from backup.xtream would be something like this:

xbstream -x <  backup.xbstream -C /root/backup/

If you need more discussion, that might be easier via the free Percona forum .


Unknown relationship I have a problem with a database design:

I have an EReport. This EReport will have a QAPCategory.
User must select a QAPCategory from an set of QAPCategory.

To know which QAPCategory can be selected I have done this design:

Is this correct? Is this a circular reference?

There's no need for both relationships.

Generally speaking, your category table should have one row per category, not per fact table row.

It's OK if multiple EReport rows refer to the same category record, but having a category record per EReport record defeats the purpose of normalizing in the first place.


Free space after removing ibdata1 file My ibdata1 file was taking up a lot of space so I followed the steps explqined on How to shrink/purge ibdata1 file in MySQL

However after deleting the ibdata file and recreating the DB I'm still not able to see any free space.

df -h shows 98% usage, du -sh shows usage of 774Mb

I've looked at the deleted files that are held open by certain processes using the lsof command and while there are a number of entries as below, restarting mysql makes no difference.

mysqld    5216 5219    mysql    6u      REG              253,1         0   25417110 /tmp/ib4eLfA1 (deleted)
mysqld    5216 5219    mysql    7u      REG              253,1         0   25417243 /tmp/ibcoRi27 (deleted)
mysqld    5216 5219    mysql   11u      REG              253,1         0   25417290 /tmp/ibhr6Thb (deleted)
mysqld    5216 5220    mysql    4u      REG              253,1         0   25417100 /tmp/ibs0uz9U (deleted)

Is there any way to force the system to check / rest free space?

It will not answer your question directly, but I wanted to notice you about this ibdata issue in general. Unfortunately, there is no magic solution to decrease the ibdata file size... It is the central point of the InnoDB engine, removing it (even when database is shutdown properly) will cause data inconsistency/corruption.

The only way to restart from a healthy state (with a small ibdata) is to reinstall a brand new MySQL instance and load the data from your origin, and PLEASE use innodb_file_per_table to avoid having all you data in one single file (MySQL doc here).

To load you current data into your new instance, you can use mysqldump if your dataset is small (few GB) or use "Transporting tablespace" procedures to cleanly import table by table your dataset (example on Percona Blog here).

You can also read this article form Percona Blog which explain many things regarding these ibdata issues.

Max.


SCAN listener redirects connection to another port I have setup RAC. I have two listeners LISTENER and LISTENER_DG.
LISTENER is listening port 1521
LISTENER_DG is listening 1530

When client connects to the database using SHARED connection mode even indicating port 1521 during connection, scan listener sometimes forwards connection to the vip ip on port 1530 and because netadmins just opened 1521 port for client and not 1530, connection fails.

This happens only on shared connection mode. DEDICATED works good.

LISTENER_DG (port 1530) is used for data guard broker  and for it I am using port other than 1521.

Why does scan forward connections to 1530 event client uses 1521 port in tnsnames.ora?

I found PDF about Oracle Single Client Access Name (SCAN)

Using SCAN with Multiple Ports on the Same Subnet

For the purpose of allowing applications to maintain a distinguishable connect string on host / port
level using one SCAN as the host or to enable port-based firewall configurations, SCAN can be set up
to support more than one port for the same subnet.

  **     It needs to be noticed that different ports are only used for an initial connect. Once a connection using
      SCAN on any SCAN port has been established, port assignment is not maintained and routing of
      connections within the Oracle RAC cluster is performed using database services registered with one or
      more local listeners.
      This means that all SCAN ports are treated the same, so that a connection that comes in on any SCAN
      port can be directed to any registered local listener on any port within the same subnet.
  **

Note also that the multi-port assignment differs per version. Oracle Grid Infrastructure 11.2.0.3 would
not let you set up multiple ports on one SCAN anymore, while Oracle Grid Infrastructure 11.2.0.2
would have let you use this configuration. Support for this configuration is resumed with Oracle Grid
Infrastructure 11.2.0.3.6 and higher as well as Oracle Grid Infrastructure 12c.

I have local_listener set to value:
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip-hq)(PORT=1530))(ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip-hq)(PORT=1521)))

So my local listeners are 1530 and 1521. and incoming connection from scan can be redirected to any of them.

I have added another listener(port 1522) statically registered database with this listener and did not indicated this listener in local_listener parameter.

Just connection to this port must be done trough vip address, not by scan. like that:

> MYDB_JOB =   (DESCRIPTION =
>     (ADDRESS_LIST=
>      (FAILOVER=on)
>      (LOAD_BALANCE=on)
>      (ADDRESS=(PROTOCOL=tcp)(HOST= node1-vip-hq)(PORT=1522))
>      (ADDRESS=(PROTOCOL=tcp)(HOST= node1-vip-hq)(PORT=1522))
>      )
>     (CONNECT_DATA =
>       (SERVER = DEDICATED)
>       (SERVICE_NAME = MYDB)
>     )   )


How can coupled databases be kept in sync with selective uni-directional syncing and with connections that can be off for days? I am trying to create a database topology that looks like this:

                  .------------.        Bad Connection.
Bad Connection.   | Central DB |        May go offline
May go offline    '------------'        for days.
for days.               |  |             .
             \          |  |            /
              ' .--------  ------------'
                |                      |
                |                      |
         .-------------.        .-------------.
         | Remote DB1  |        | Remote DB2  |
         '-------------'        '-------------'

Some of the tables are exclusively pushed from Central down to the Remotes

        .------------.
        | Central DB  |
        |-------------|
        | Master Data |
        | Table Foo   |
        '------------'
              |  |
              V  V
      .--------  ------------.
      |                      |
      V                      V
.---------------.     .---------------.
| Remote DB1    |     | Remote DB2    |
|---------------|     |---------------|
| Unmodified    |     | Unmodified    |
| Slave Copy of |     | Slave Copy of |
| Table Foo     |     | Table Foo     |
'---------------'     '---------------'

Some of the tables are exclusively pushed from Remotes up to Central and the
rows the that each individual Remote mutates are exclusively mutated by the individual Remote node.

        .---------------.
        | Central DB    |
        |---------------|
        | Slave         |
        | Table Bar     |
        |---------------|
        | Row a from DB2|
        | Row b from DB2|
        | Row c from DB1|
        | Row d from DB1|
        | Row e from DB1|
        | Row f from DB2|
        '---------------'
              |  |
              ^  ^
      .--------  ------------.
      |                      |
      ^                      ^
.------------.        .------------.
| Remote DB1 |        | Remote DB2 |
|------------|        |------------|
| Table Bar  |        | Table Bar  |
|------------|        |------------|
| Row c      |        | Row a      |
| Row d      |        | Row b      |
| Row e      |        | Row f      |
'------------'        '------------'

Remote DB1 should not get data generated on Remote DB2.  They only sync
up to Central and Central never pushes down other Remotes' Bar table rows.

Lastly, when connectivity is lost to Remote DB1 or Remote DB2 or Central
or any combination, each database should continue to operate locally and when
connection is restored, the updates to each table or sub-section of table that
they own should be pushed in the appropriate direction.

So, given this topology, and the constraints of having complete internet loss
to one or multiple remote databases on occasion with the addition that each DB
should merrily continue without syncing until connection is restored, is there a
cluster configuration that I can use in MariaDB, MySQL, or PostgreSQL that will
enable such a topology?  I understand that the Remotes are doing something
akin to "horizontal sharding", except that I don't want them to get other
Remotes' data.

The "Master Data" tables that are only mutated by Central clearly looks like a
classic Master-Slave relationship with the Remotes, except the the Remotes
should stay online when the master Central goes away.

With regard to the horizontally sharded  Remotes table Bar, I can imagine
assigning PK ID ranges that those Remotes exclusively mutate.

Is there a way to do this topology with clustering technology or do I need to
manually roll something with incremental database dumps like this technology in
MySQL?

The requirement that the setup has to cope with several days of broken network connections effectively rules out "virtually" synchronous multi-master solutions like Galera and MySQL group replication. And you can also forget about MySQL NDB Cluster.

However, I think an asynchronous MariaDB or MySQL solution could do what you need, assuming the binlogs are large enough to hold the data accumulated while the network connections are down.

It seems like asynchronous MariaDB / MySQL master-master replication might be able to do what you need. Master-master replication is basically two instances of master-slave replication, where each server is master in one of the replication connections, and slave in the other. In addition it's common to have auto_increment_increment = 2 on both servers, auto_increment_offset = 1 on the first server and auto_increment_offset = 2 on the other. This way, neither database server causes collisions when using the auto_increment feature of the primary keys in INSERT statements. This will also eliminate any need for sharding in your use case.

So you will need to setup master-master replication between your Central DB and Remote DB1, and separate master-master replication between your Central DB and Remote DB2.

You will further need to set replicate-do-table=foo so that only that table is replicated to Remote DB1 and Remote DB2 from Central DB. And similarly, replicate-do-table=bar so that only that table is replicated from Remote DB1 to Central DB, and from Remote DB2 to Central DB.

There are many more details, such as GTID (which is a feature which was not available initially, but is a good idea now), but here I suggest you look at the available documentation elsewhere, e.g.:

Simple Master-Master replication on MariaDB (tunnelix.com)

How to Setup MySQL Master-Master Replication (howtoforge.com)


Formatting a Measure in SSAS cube I have a measure that show number in millions / billions.

The formatting of the number for now is like this : "$#,##0.0;-$#,##0.0"

I would like to format the number to show something like this:

If the Orginal number is: 117,100,000 $

I want to see it like that: 117.1 M $.

Can we do it on SSAS cube?

Thanks.

Please try this format string:

0,,.0 \M $

This trick is a bit buried in the documentation.


Is it possible to connect to Oracle 9i data source in SSIS? We're doing a migration from Oracle 9i to SQL Server 2008. So I'm thinking of a way to move the data from Oracle and I thought about SSIS. I'm new to this Microsoft's technology and I would like to know whether it's possible to specify Oracle 9i as a data source, and do I need any further installations in case it's possible ?

You can connect to Oracle from SSIS. You have to install Oracle client on your development and also on your production environments. After doing proper tnsnames configurations you can use odbc source or ado.net source.


Does Microsoft Security Bulletin MS11-049 affect SQL Server 2008 SP3? The security bulletin does not list SQL Server 2008 SP3 under affected software but it came out prior to that service pack. We are running SP3 but this issue is still showing up on a vulnerability scan. Does anyone know for sure if this is a false positive? I only found one forum post where someone said SP3 "should" include this security update.

Scroll down to Security Update Deployment and open that up. Then open SQL Server 2008. You will see that SP3 includes this fix.

If your tool is reporting this problem because it doesn't detect the patch then one of the following is true:

The tool is wrong.
The tool has additional information not listed on that page.

If your tool is reporting the problem because it's using an exploit to test the vulnerability, then I would take that up with Microsoft.


Adding time slots through trigger SQL Server I am quite new to SQL. I am now working on something to schedule appointments.
I found an existing answer about time slots, but did not quite understand.

I need to automatically add time slots to the timeslots table according to the availability table. if availability from start at 10:00 and end at 16:00, and duration is 60 minutes, I need to insert 6 time slot rows.

Would you please be kind enough to let me know what I should do?
Do I need to do it through a trigger? Or do I need to redesign my schema?

My trigger is like this, apparently not really working:

    CREATE TRIGGER trigger_addSlots ON availability
    FOR INSERT AS
    BEGIN
        declare @AvailabilityId int
        declare @SlotStart DateTime
        declare @SlotEnd DateTime
        declare @NumberOfSlots int
        declare @Duration int
        declare @SlotDoctorId int
        declare @i int

        Select @AvailabilityId = Id from inserted
        Select @SlotDoctorId =DoctorId from inserted
        Select @SlotStart =AvailableFrom from inserted
        Select @SlotEnd = AvailableTo from inserted
        Select @Duration = AppointmentDuration from inserted

        set @NumberOfSlots = convert (int, (@SlotEnd - @SlotStart))/@Duration

        set @i = 0;
        While @i<@NumberOfSlots
          Begin

            insert into timeslots(AvailabilityId, SlotStart, SlotEnd,
            SlotDoctorId,IsAvailable) values (@AvailabilityId, @SlotStart,
            @SlotStart+@Duration,
            @SlotDoctorId,1)
            Set @SlotStart = @SlotEnd
            Set @SlotEnd = @SlotEnd + @Duration
            set @i=@i+1
          End

    END

schema

Your schema looks fine but you need to calculate the dates in a different way,
In T-SQL you can't just add and subtract.

@NumberOfSlots calculation should be like this:

    set @NumberOfSlots = convert (int, (DATEDIFF(MINUTE, @SlotStart, @SlotEnd)))/@Duration

adding to a datetime is done like this:

    DATEADD(MINUTE, @Duration, @SlotStart)

The order in the loop needs to be changed too:

    set @i = 0;
    While @i<@NumberOfSlots
        Begin
           Set @SlotEnd = DATEADD(MINUTE, @Duration, @SlotStart)
           insert into timeslots
           (AvailabilityId, SlotStart, SlotEnd, SlotDoctorId,IsAvailable)
           values
           (@AvailabilityId, @SlotStart, @SlotEnd, @SlotDoctorId,1)
           Set @SlotStart = @SlotEnd

           set @i=@i+1
        End

With these changes, I think the trigger will work and give correct values, but
that is a lot of work in a trigger.

I think you should consider solving this by building stored procedures doing the same, leaving the tables with data and no logic.

One stored procedure per table, that inserts a row to that table.

For 'Availability' the stored procedure could be createAvailability and the code would be very similar to what you have in the trigger, but in the loop it calls the stored procedure to create a row in Timeslots:

    CREATE PROCEDURE createAvailability
    (
        @AvailabilityId INT,
        @SlotStart DATETIME,
        @SlotEnd DATETIME,
        @SlotDoctorId INT
    ) AS
    BEGIN
        DECLARE @i INT
        DECLARE @NumberOfSlots INT
        DECLARE @Duration INT

        SET @NumberOfSlots = convert(INT, (DATEDIFF(MINUTE, @SlotStart,@SlotEnd))) / @Duration
        SET @i = 0;
        WHILE @i < @NumberOfSlots
        BEGIN
            SET @SlotEnd = DATEADD(MINUTE, @Duration, @SlotStart)
            EXEC createTimeslots(@AvailabilityId, @SlotStart, @SlotEnd, @SlotDoctorId, 1)
            SET @SlotStart = @SlotEnd
            SET @i = @i + 1
        END
    END


How to force oracle accept insufficient swap size? I want to install Oracle 12c on Oracle Linux 6.10 with 5.7803GB swap size.

But one of prerequisites checked failed:

INFO: *********************************************
INFO: Swap Size: This is a prerequisite condition to test whether sufficient total swap space is available on the system.
INFO: Severity:IGNORABLE
INFO: OverallStatus:VERIFICATION_FAILED
INFO: -----------------------------------------------

INFO: ERROR: [Result.addErrorDescription:703]  PRVF-7573 : Sufficient swap size is not available on node "olhost" [Required = 7.8167GB (8196368.0KB) ; Found = 5.7803GB (6061052.0KB)]
INFO: ERROR: [Result.addErrorDescription:714]  PRVF-7573 : Sufficient swap size is not available on node "olhost" [Required = 7.8167GB (8196368.0KB) ; Found = 5.7803GB (6061052.0KB)]
INFO: ERROR: [Result.addErrorDescription:714]  PRVF-7573 : Sufficient swap size is not available on node "olhost" [Required = 7.8167GB (8196368.0KB) ; Found = 5.7803GB (6061052.0KB)]
INFO: INFO: [Task.perform:819]
TaskSwapSize:Swap Size[CHECK_SWAP_SIZE]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [58 Milliseconds]
          ERRORMSG(olhost): PRVF-7573 : Sufficient swap size is not available on node "olhost" [Required = 7.8167GB (8196368.0KB) ; Found = 5.7803GB (6061052.0KB)]

How to force oracle to use smaller swap size instead of required?

You possibly can't, really, unless there is a documented option for that. As the error is marked as "Severity:IGNORABLE" it is not unlikely that there will be an option to ignore "ignorable" warnings and proceed. Check the installation documentation for this information.

If not, and the problem is that you have a fixed swap partition/volume that you cannot increase because there is no space available to grow it into, you could temporarily add more swap by adding a swap file with something like:

dd if=/dev/zero of=/tmp/additionalswap bs=1048576 count=2100
chmod 600 /tmp/additionalswap
mkswap /tmp/additionalswap
swapon /tmp/additionalswap

before the installation process. This assumes you have sufficient space in the filesystem /tmp is found in, choose another location if not.

Once done, turn the extra swap space off with swapoff /tmp/additionalswap and remove the file.


Can a username and password be hard-coded in a system DSN We have a legacy CRM that uses a DSN with Windows Authentication to connect to an instance of SQL Server 2005 (part of SBS 2003 Premium). We will soon be doing a major migration leaving us without a Windows Domain - as I understand things, that will mean we need to use an alternative authentication method. We are also planning to migrate from the SQL Server 2005 to SQL Server Express 2008 R2 in the same timeframe.

Unfortunately no modifications to the CRM are possible.

Leaving aside the obvious security issues, is it possible to hard-code a username and password in a system DSN? Alternatively is that another obvious approach to authentication that I am missing?

--EDIT

The DSN is defined in the ODBC Data Source Administrator in the "Administrative Tools" section of the Control Panel on the Windows clients.

--EDIT 2

I gave up trying to do this in the DSN thanks to Jonathan's answer. Fortunately I discovered an .ini file where I could insert a fixed username/password replacing the placeholders - problem solved.

A System DSN is stored in the registry and does not have the ability to store a password.  If you want to store the password, you'd have to use a File DSN and that would require changing the CRM to use the file DSN which you say is not possible.  Usually applications that use a System DSN, rely on SQL Server authentication of the users for their security.  When the application opens it should prompt the user to login, and this is the username and password that is used with the System DSN information to connect to the database server.  If the DSN uses Windows authentication and you are eliminating your Windows Domain, you are stuck.  You would have to change the CRM to make it work a different way.


SQL 2008, how does it link Full Text Catalog to a filegroup While creating Full Text Catalog, if "on filegroup" is specified - it creates the catalog on this file, but on MSDN I read "On Filegroup" clause has no affect?

In SQL Server 2005 and prior, you could specify the filegroup for full text.

In SQL Server 2008 and newer, full text is completely different, and the ON FILEGROUP stuff doesn't matter.  However, Microsoft doesn't just rip out syntax - they deprecate it, and a few versions later, it'll disappear.  (Backup log with truncate_only is a good example of this.)  This gives you time to clean up your scripts before the syntax disappears.


Select ID from costumers that do not have contacts last 7 days and 2 times in the month I have 2 tables: custumer, c_contact.
c_contact is all the e-mails I send to my custumers.
From now on I need to put a new rule that the costumer can't receive a new e-mail if:
1) It received an e-mail in the last 7 days
2) Have 2 or more e-mails sent in the current month

I thought something like that:

SELECT *
from costumer c inner join c_contact cc on cc.ID = c.ID
WHERE ID not in (select ID
                 from c_contact c1
                 where c1.ID = cc.ID
                   and c1.CONTDATE >= getdate()-7)
  AND ID not in (select count(ID)
                 from c_contact
                 where MONTH(contdate) = MONTH(getdate())
                   and YEAR(contdate) = YEAR(getdate())
                 HAVING count(ID) >= 2)

But the table c_contact is huge and it taking ages to run this.
Is there a way to do these 2 conditions in 1 "ID not in"? I thing it will run a lot faster.

These are the customers that have not received an e-mail in the last 7 days AND have received less than two e-mails this month:

SELECT *
FROM customer AS c
WHERE c.ID NOT IN (
    SELECT ID        --- the customer ID
    FROM c_contact
    WHERE contdate>=DATEADD(day, -31, SYSDATETIME()) -- for optimization
    GROUP BY ID
    HAVING MAX(contdate)>=DATEADD(day, -7, SYSDATETIME()) OR
           SUM((CASE WHEN contdate>=DATEFROMPARTS(
               YEAR(SYSDATETIME()),
               MONTH(SYSDATETIME()),
               1) THEN 1 ELSE 0 END))>=2);

Conversely, here are customers that have receive an e-mail in the last 7 days OR have received two or more e-mails this month:

SELECT *
FROM customer AS c
WHERE c.ID IN (
    SELECT ID        --- the customer ID
    FROM c_contact
    WHERE contdate>=DATEADD(day, -31, SYSDATETIME()) -- for optimization
    GROUP BY ID
    HAVING MAX(contdate)>=DATEADD(day, -7, SYSDATETIME()) OR
           SUM((CASE WHEN contdate>=DATEFROMPARTS(
               YEAR(SYSDATETIME()),
               MONTH(SYSDATETIME()),
               1) THEN 1 ELSE 0 END))>=2);

Because I've put the two conditions in a single HAVING clause, the two queries only scan the c_contact table once, making them much more efficient. In particular, an optimal index for this type of query would probably look something like this:

CREATE INDEX indexName ON c_contact (ID, contdate);


Limiting number of results in a Partition using OVER(PARTITION BY) In the following query, why is it that we have to limit the results returned from each Partition by using the clause WHERE foo.row_num < 3 outside of the subquery foo but not from within the subquery with WHERE row_num < 3?

Query

SELECT pid, land_type, row_num, road_name, round(CAST(dist_km AS numeric), 2) AS dist_km

FROM (
    SELECT ROW_NUMBER()
    OVER (
        PARTITION by loc.pid
        ORDER BY ST_Distance(r.the_geom, loc.the_geom)
    ) as row_num,
    loc.pid, loc.land_type, r.road_name,
    ST_Distance(r.the_geom, loc.the_geom)/1000 as dist_km
    FROM ch05.land AS loc
    LEFT JOIN ch05.road AS r
    ON ST_DWithin(r.the_geom, loc.the_geom, 1000)
    WHERE loc.land_type = 'police station'
) AS foo

WHERE foo.row_num < 3
ORDER BY pid, row_num;

Query that does not work

SELECT pid, land_type, row_num, road_name, round(CAST(dist_km AS numeric), 2) AS dist_km

FROM (
    SELECT ROW_NUMBER()
    OVER (
        PARTITION by loc.pid
        ORDER BY ST_Distance(r.the_geom, loc.the_geom)
    ) as row_num,
    loc.pid, loc.land_type, r.road_name,
    ST_Distance(r.the_geom, loc.the_geom)/1000 as dist_km
    FROM ch05.land AS loc
    LEFT JOIN ch05.road AS r
    ON ST_DWithin(r.the_geom, loc.the_geom, 1000)
    WHERE loc.land_type = 'police station'
    AND row_num < 3
) AS foo

ORDER BY pid, row_num;

Error: ERROR: column "row_num" does not exist

The column "row_num" doesn't exist because the logical order of processing requires the dbms to apply the WHERE clause before it evaluates the SELECT clause. The windowing function is part of the SELECT clause, so its alias isn't accessible in the same statement's WHERE clause.

The FROM clause is the very first part of the statement to be evaluated. That's why the  aliases you declare in the FROM clause are accessible in the WHERE clause of the same statement.

Search this page for "logical processing order". Although this links to SQL Server documentation, the logical order of processing is the same for every dbms that complies with SQL standards.


disable log during migration? We have an older SQL Server Database that is about 1TB.
We now have a new database that has a slightly different schema.  We are going to migrate the 1TB of data to the new db using the export tool in SSMS.
However to make it go faster, I am thinking maybe there is a way to disable unnecessary logging and backup during the migration?
We won't need to recover to a specific time state during the migration anyway and we have the original database.
Could I set the log size to something like 100MB initially, do the migration and then change it back to unlimited again?  Would it be viable to use SIMPLE recovery during the migration and then afterwards change it to FULL? I'm not sure what this tool uses to migrate data internally, probably bulk inserts.
Just for clarity, in SSMS, we will right click the database > Tasks > Export Data.  This allows you to map columns from the source database to the target database.  So, this is not a "restore" in that sense.  We need to copy from one db to another. The problem is that the new db has other column names and in some cases other datatypes. However this tool converts and truncates correctly.

IMO, this type of work should be testable and repetable. I.e., scripted. I would go for backup/restore and then have a script that does your DDL changes (ALTER TABLE, ...). You script everything and test, re-test etc to know everything is fine.
Backup/restore time can be minimized by utilizing log backups.
As for the DDL changes, you will find out how long time they take. Depending on what you need to do, simple recovery model might be useful - but again, that depends on your DDL/DML in the end.


Storing linked server in stored procedure variable I have following setup:

Server 1: CGENMONOSAMP01

Server 2: CGENMONOSAMP02

I have Server 2 linked to Server 1 and link works, I can do queries like this on Server 1:

SELECT * FROM [CGENMONOSAMP02].master.dbo.sysdatabases

And it retrieves list of DB's on Server 2.

I am trying to create the following:

    DECLARE @SourceDatabase varchar(50) = '[CGENMONOSAMP02].Sample_DB'
    DECLARE @SourceHost varchar(50) = (SELECT SUBSTRING(@SourceDatabase, 2, 14))
    DECLARE @SourceDB varchar(50) = (SELECT SUBSTRING(@SourceDatabase, 18, 100))

--@SourceHost is CGENMONOSAMP02

--@SourceDB is Sample_DB

    IF NOT EXISTS (SELECT [name] FROM master.dbo.sysdatabases WHERE [name] = @SourceDatabase)
    BEGIN
        IF NOT EXISTS (SELECT [name] FROM @SourceHost.master.dbo.sysdatabases WHERE [name] = @SourceDB)
        BEGIN
        RAISERROR ('Error: Source Database[%s] is not existing.', 10, 1,@SourceDatabase)
        RETURN
        END
    END

This is being called on Server 1, it's supposed to take DB name which can either be name of DB located

on Server 1 as: "Sample_Table"

or it can be name of DB located

on Server 2 as: "[CGENMONOSAMP02].Sample_Table"

It's supposed to first check if passed name of DB exists on local server, if not - it should check if it exists on linked server.

I've tried the above and it gives me:

  Incorrect syntax near '.'.

I've also tried:

IF NOT EXISTS (SELECT [name] FROM [@SourceHost].master.dbo.sysdatabases WHERE [name] = @SourceDB)

But that gives me:

  Could not find server '@SourceHost' in sys.servers. Verify that the
  correct server name was specified.

Is it possible to make such reference?

You could use dynamic sql to check the existence of databases in linked servers when using parameters.

An example:

DECLARE @SourceHost nvarchar(50) = '[CGENMONOSAMP02]';
DECLARE @SourceDB nvarchar(50) = 'Sample_DB';
--@SourceHost is CGENMONOSAMP02

--@SourceDB is Sample_DB
CREATE TABLE #temp ([name] nvarchar(50));
IF NOT EXISTS (SELECT [name] FROM master.sys.databases WHERE [name] = @SourceDB)
BEGIN
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = 'SELECT [name] FROM '+@SourceHost+'.[master].[sys].[databases] WHERE [name] = @SourceDB ';

    INSERT INTO #temp([name])
    EXEC SP_EXECUTESQL @SQL,N'@SourceDB nvarchar(50)',@SourceDB;

    IF NOT EXISTS(SELECT [name] FROM #temp)
    BEGIN
        RAISERROR ('Error: Source Database[%s] is not existing.', 10, 1,@SourceDB);
    END
END

DROP TABLE #temp;


SQL Server execution plan using a view, but no view is inside the stored procedure I am troubleshooting this slow stored procedure and when checking the actual execution plan I noticed a warning of a possible missing index. Taking a closer look, it's against one of the views, not an actual table. How can that be possible?

Of course, the physical table is being used but the actual view it is not!

The view is something like this:

CREATE VIEW [ws].[MyVIEWTable]
WITH SCHEMABINDING
AS
SELECT col1, col2
  FROM [dbo].[MyTable]

Why does the SQL Server engine use the view to retrieve data and not the actual physical table, which on this case would be dbo.MyTable?

So it's an indexed view - SQL Server can automatically choose to use an indexed view if the base table is referenced and the indexed view can (better) satisfy the query.

From MSDN (emphasis mine):

  A query does not have to explicitly reference an indexed view in the FROM clause for the query optimizer to use the indexed view. If the query contains references to columns in the base tables that are also present in the indexed view, and the query optimizer estimates that using the indexed view provides the lowest cost access mechanism, the query optimizer chooses the indexed view [...]

Also, as you've seen, the missing indexes algorithm might nudge you and say, "Hey Jose, if you added this non-clustered index to the indexed view, I might have been able to use the index on the view instead, which would have been more efficient for this query." Again, this correlation can happen even if the view isn't explicitly mentioned in the query.

Generally, unless you are throwing indexes on all of your views because you believe they are some magic performance silver bullet, accessing the indexed view will be better than accessing the base table, because - again generally - the indexed view should be a lot smaller than the table on which it is based.


Are certain environment variables required for mysql login-path? I created a bash script that backs up my database, db_backup.sh

It basically just runs these commands:

DB=/home/linuxuser/backups/backupfilename.sql.gz
mysqldump --login-path=local db_name | gzip > "$DB"

When I run it from the command line as linuxuser or as root, it works.

But when I add it to cron.d, I get a mysql error and the sql.gz file is empty.

mysqldump: Got error: 1045: Access denied for user 'linuxuser'@'localhost' (using password: NO) when trying to connect

(I get the same type of error when running the cron as root, too)

How can I fix this? Could it be a problem with environment variables?

I tried changing:

HOME=/

to

HOME=/linuxuser/

... but that did not help.

mysqldump needs -u and -p for some mysql "user".


Unable to get any type of connection using MySQL on LAN My question is rather simple, I have a Windows 7 machine that is hosting an Apache server and a MySQL server using the XAMPP control panel. Both of these have been installed as a service on the machine. I am trying to connect to the MySQL server from a CentOS machine that is on the same LAN. When issuing the command:

mysql -h *IPADD* -u *UNAME* -p*PWORD*

I get this error:

ERROR 2003 (HY000): Can't connect to MySQL server on '172.26.96.166' (110)

I also see no indication in any logs on the Windows machine that there was a connection attempt.

I have spent much time trying to figure out a cause for this and I cannot seem to figure out the issue. I have:

Checked the firewall and explicitly made an exception for mysql

Installed MySQL as a service on the machine

Used the netstat command to verify that the ports are open

Checked and re-checked the username and password being used

Checked the host associated with the used credentials (the host is set to the wild card (%)

After doing all of this, I cant seem to find anything wrong or that would be blocking it. To give some sort of context, I am using this on a medium sized network and my account on the Windows machine is not Admin. However, I have had extensive help from an admin who has been the one authenticating me to change the firewall and what have you. That being said, there still may be restrictions on his account due to some grouping policies or the way the computer is imaged.

It may also be important to note that I am not directly in front of the CentOS machine, I am using "Putty" on the Windows machine to ssh into the machine to issue the MySQL commands

Anything I may have missed or any advice would be much appreciated.

Are you using Putty to log in to DB server or some other server?

If its DB server then, error can be caused by following

Incorrect username/password
Incorrect port number
MySQL DB is not up and running

If you are trying from different host to connect as in remote DB host make sure

you have connectivity between the hosts and
there is no IP based restrictions. As you know admin can restrict access to certain IP address.

Hope this helped.


cypher query to find Employee nodes who have all the Skills I'm trying to write a cypher query to find the first name of 'Employee' nodes who 'Has' all the Skills (have relationship with all the nodes with the 'Skill' label). This is my data model:

I managed to get all the employee nodes that have at least one relationship with skill nodes, but I do not want this. This is the cypher query:

:match (a:employee)-[r:Has_Skill]->(b:profession) return a,r,band

I couldn't write it with the ALL() function.
Can anybody help me with this?

Aside: Your Cypher query uses node labels and relationship types that do not match your illustrated data model. So, I will ignore your illustration.

This is a slightly tricky way to get the employee nodes that have Has_Skill relationships to all profession nodes:

MATCH (p:profession)
WITH COUNT(p) AS pCount
MATCH (employee:employee)-[r:Has_Skill]->()
WITH pCount, employee, COUNT(r) AS sCount
WHERE sCount = pCount
RETURN employee;

The query gets the number of profession nodes and returns employees that have that number of skills.

By the way, you will get much better support for neo4j questions at stackoverflow.com.


Installing 32bit SSRS on 64bit Windows/IIS I've inherited a situation where I have SQL Server 2005 Standard 32bit running on Windows Server 2008 R2 64bit.

When I come to install SSRS components I'm warned that "Failed to find the ASP.Net Version Registration with Microsoft Internet Information Services (IIS)."

In IIS 7 and above, each application pool can run in 32bit mode. If I change all application pools into 32bit mode will this solve the problem?

Are the better alternative solutions?

Thanks in advance.

You can fix the problem by following the steps described in the link below:

http://msdn.microsoft.com/en-us/library/ms143293(v=sql.90).aspx

In this link different scenarios including yours have been discussed.
Hope it helps.