How to structure a database to show data for each USA State and Canada Territory on a Per user basis? I need to build a PHP/MySQL application.  I am a bit confused on how to do the database for a project like I am about to explain though.

The end result will be a Map of the USA and Canada.  Each state will have a popup with data for that state.  Below is the data items I need to show for each state.

Contacts (integer)
Pickups (integer)
Interviews (integer)
Guest Posts (integer)
Book Review Opp (integer)

Now to complex it a little more, each map page will show the data from above only for a particular User and then for a particular "Campaign" that the user owns.

Every user will have a map page with data only relevent to the campaign they are viewing.

I am not sure on how to structure a MySQL table for the "campaigns" and have it store data for the above items for each state or region of Canada.

Any ideas how to structure the database?

The image below is just an example, I need to be able to set these items

Contacts (integer)
Pickups (integer)
Interviews (integer)
Guest Posts (integer)
Book Review Opp (integer)

For every State, also each users campaign will mean a separate map where each of these items can be set again for each state

Well, I guess I will have to make assumptions about what your user and campaign data look like.

Maybe a schema like this:

Users
-----
  id (PK)

Campaigns
---------
  id (PK)
  user_id (This it the "user owns a campaign" relation, FK to users.ID)
  (other fields)

territories
-----------
  id (PK)
  name
  country

territories_campaigns
---------------------
  id (PK)
  territory_id (FK to territories.id)
  campaign_id (FK campaigns.id)

You use the territories_campaigns table to determine which campaign is happening in which territory. The structure of this table allows a campaign to span multiple territories, and each territory can host multiple campaigns.

Since the campaign table refers to the user who owns it, to build a map for a specific user, make sure you filter on the user_id


Fill in missing dates for a vacation schedule Have an issue that has been driving me nuts for the past week and can't seem to get my head around it.

I have a stored procedure that grabs a bunch of vacation requests from our scheduling system. I need to now get each day that the individual has requested into a single row to pivot and plot in excel at the request of the user.

This is the end result I am looking for in a SQL Table

Any help is appreciated, already have a datedimension table created, tried using CTE and another attempt using outer apply but at this point I am spinning my wheels.

UPDATE:  Calendar Table

This is the table (Calendar Table) I am using - Populated with 30 years of dates

CREATE TABLE [dbo].[DateDimension](
    [Date] [date] NOT NULL,
    [Day] [tinyint] NOT NULL,
    [DaySuffix] [char](2) NOT NULL,
    [Weekday] [tinyint] NOT NULL,
    [WeekDayName] [varchar](10) NOT NULL,
    [IsWeekend] [bit] NOT NULL,
    [IsHoliday] [bit] NOT NULL,
    [HolidayText] [varchar](64) SPARSE  NULL,
    [DOWInMonth] [tinyint] NOT NULL,
    [DayOfYear] [smallint] NOT NULL,
    [WeekOfMonth] [tinyint] NOT NULL,
    [WeekOfYear] [tinyint] NOT NULL,
    [ISOWeekOfYear] [tinyint] NOT NULL,
    [Month] [tinyint] NOT NULL,
    [MonthName] [varchar](10) NOT NULL,
    [Quarter] [tinyint] NOT NULL,
    [QuarterName] [varchar](6) NOT NULL,
    [Year] [int] NOT NULL,
    [MMYYYY] [char](6) NOT NULL,
    [MonthYear] [char](7) NOT NULL,
    [FirstDayOfMonth] [date] NOT NULL,
    [LastDayOfMonth] [date] NOT NULL,
    [FirstDayOfQuarter] [date] NOT NULL,
    [LastDayOfQuarter] [date] NOT NULL,
    [FirstDayOfYear] [date] NOT NULL,
    [LastDayOfYear] [date] NOT NULL,
    [FirstDayOfNextMonth] [date] NOT NULL,
    [FirstDayOfNextYear] [date] NOT NULL
) ON [PRIMARY]
GO

You can do this with a CROSS APPLY that will generate the dates.

SELECT m.requestor, m.schedcode, o.Date FROM mockup m
CROSS APPLY (
    SELECT  TOP (DATEDIFF(DAY, m.fromdate, m.enddate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, m.fromdate)
        FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b
) AS o

Props to GarethD's answer at Get all dates between two dates in SQL Server for the date generating code.


Installing SQL Server 2016 for Azure development Coming from a dev background, I'm not an expert in SQL Server. When installing SQL Server 2016, I have the option of installing three different components:

New SQL Server stand-alone installation
SQL Server Management Tools
SQL Server Data Tools

I'm only interested in SQL Azure development with Visual Studio. The question is should I only install the last two items on my local machine? Do I need the full-blown SQL Server 2016 from the first item?

If you're not planning on deploying to a local SQL Server database engine, you can skip that section from the installer, yes.


SQL Server 2014 and SQL Server 2014 Management Studio I successfully installed SQL Server 2014, but when I look for SQL Server Management Studio it was not on the CD.

I have just downloaded SQL Server 2014 Management Studio separately and would like to get advice on any issues installing SQL Server Management Studio.

My experience with SQL Server 2005 was that the process installed both the server and the SQL Server Management Studio. Since they appear to be installed independently now. I would appreciate advice or a link to comprehensive documentation.

You have to run the main installer, and choose to add the shared feature. The fact that it's available as a separate download as well is a bonus for people who don't want to have to download such a large ISO, but the SSMS installer is definitely part of the main installer - you just have to choose it from the "shared features" section.


Update MySQL with Solaris 11 IPS Package I'm barely new with Solaris OS and there are still a couple of things I definitely don't get it.

I want to replicate a MySQL installation that someone did in the past in a development server we have. Simple: MySQL 5.6.36 running on Solaris 11.3 installed as a IPS package with its SMF services.

I'm testing a with a fresh new install Solaris 11.3. I did a "pkg install database/mysql-56" (following this simple guide), but the MySQL IPS package installed version is 5.6.25.
How can I upgrade it to 5.6.36? Why the latest MySQL IPS Package available version is so old (in fact, I was expecting a newer version, and then try to figure out how to downgrade it)? What I'm missing?

Regards!

There's a couple things that may be going on here (possibly both):

In a fresh install, the default package repository is set to http://pkg.oracle.com/solaris/release/ which contains the packages from the initial Solaris 11.3 release in 2015.  To get updates you need to switch to the /support repo, which is only available to customers with a current support contract.  See How to Update Oracle Solaris 11 Systems From Oracle Support Repositories for instructions.
The packages provided with Solaris, like MySQL, default to being updated only as part of an OS update, not individually.  You may be able to unlock them to allow changing versions independently when needed - this is described in More Tips for Updating Your Oracle Solaris 11 System from the Oracle Support Repository under "Updating an Individual Package" or in Relaxing Version Constraints Specified by Constraint Packages.


Installing SQL Server Tools In order to set up an SSAS system, I am having to install Visual Studio (SQL Server Data Tools - Business Intelligence) but when I adding this to the instance, it says it has failed the rule "Same Architecture installation", and that I have to match the new installation CPU architecture with the CPU architecture with the instance.

The instance I'm trying to link to is Microsoft SQL Server 2014 - 12.0.2269.0 (X64) Developer Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) running on Windows 7. The SSDT installation is Visual Studio 2013.

I'm not sure how I can achieve this.

g2server wrote in answer to the question 64 bit Microsoft SQL Server Data Tools on Stack Overflow:

As the others have said, there is only a 32 bit version.
The important thing is, if you’re running an x64-based SQL instance (64-bit), make sure to select “New Instance” on the Installation Type page, and NOT “Add features to an existing instance”. Not doing so will cause it to error out.

Community Wiki answer to document the solution on Stack Overflow found by Dave


mysqldump with automatically generated export name When I do database dumps through MySQL WorkBench it picks a filename for me which is the current date (YYYMMDD).

Now, I want to do this in a batch file since I want to make weekly dumps automatically by simply running a simple script (our DB is running on a windows machine)

Do I have to make the filename myself or is there a way to have mysqldump do it for me? I can use date /t and parse it but it would be nice to know if there was some built-in functionality.

Here a script i have running my database backup :
-it create daily zipped bkps  in their onw daily data dirs.
-it may look strange that no passw in the conf ! is because i have configured my user root to run mysqldump without asking for password(this is for security reasons- you don't what your OS admin to sniff the process and see the password :))

To configure
password less access just create a .my.cnf file in your root home dir containing :

[mysqldump]
user = root
password = password

Script

 #!/bin/bash
        #Script backup mysql individual
        USER="root"
        DIR="`date | awk {'print $2"_"$3"_"$6'}`"
        PWDDIR="/mysql/backup_mysql/_mysqldump/"
        FINALDIR="$PWDDIR$DIR"
        GZIP_ENABLED=1
        GZIP="/bin/gzip"
        MYSQLDUMP="/usr/bin/mysqldump"
        MYSQL="/usr/bin/mysql"
        mkdir $FINALDIR
        databases=`$MYSQL --user=$USER -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`

        for db in $databases; do
            echo $db
                if [ $GZIP_ENABLED == 1 ]; then
                        mysqldump --force --opt --user=$USER  --databases $db | gzip > "$FINALDIR/$db.gz"
                else
                    mysqldump --force --opt --user=$USER  --databases $db > "$FINALDIR/$db.sql"
                fi
        done

the script will create data dir for every day:

drwxr-xr-x 2 root root 4096 Mar 13 02:00 Mar_11_2014

Windows version for a nice mysqldump follow this link


Arithmetic overflow for Variable in Openrowset I try to create a table to store the used storage Information for every Database.
Since theese are hosted on different Cluster / Instances, I created a table with all Instance / Server Information.
 _________________________
|________DBInstance_______|
|____ID___|_Instance_______|
|____1____|Server\Instance__|
|____2____|AG-Listener_____|

I then create a cursor and send my query to every server listed in the Instance-Table.

DECLARE @ID nvarchar(2), @Instancename VARCHAR(50), @cmd NVARCHAR(MAX)
DECLARE DBCursor CURSOR FOR
SELECT ID, Instance
from DBInstancelist;

DECLARE @ID nvarchar(2), @Instancename VARCHAR(50), @cmd NVARCHAR(MAX)
DECLARE DBCursor CURSOR FOR
SELECT ID, Instance
from DBInstancelist;

OPEN DBCursor
FETCH NEXT FROM DBCursor into @id , @Instancename
WHILE @@FETCH_STATUS = 0
BEGIN
select @cmd = N'INSERT INTO DBSIZE
select a.*
from OPENROWSET (''SQLNCLI'',''Server=' + @Instancename + ';Trusted_Connection=YES;'',''select
        [ID] = '+@ID+',
        [Database Name] = DB_NAME(database_id),
        [Type] = CASE WHEN Type_Desc = ''''ROWS'''' THEN ''''Data File''''
                      WHEN Type_Desc = ''''LOG''''  THEN ''''Log File''''
                      ELSE ''''SUMME'''' END,
        [Size in MB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL ),
        GETDATE() AS Scantime
FROM   sys.master_files
WHERE      database_id > 4
GROUP BY      GROUPING SETS
              (
                     (DB_NAME(database_id), Type_Desc),
                     (DB_NAME(database_id))
              )
ORDER BY      DB_NAME(database_id), Type_Desc DESC'')
AS a;'
--select @cmd
exec sp_executesql @cmd
--select @cmd
FETCH NEXT FROM DBCursor into @ID, @Instancename
END
CLOSE DBCursor
DEALLOCATE DBCursor

for the first 4 Instances I receive the Information I except, for the 5th Instance however, I reveice following Error.

 "Arithmetic overflow error converting expression to data type int.

This error only Appears, If I add the Line

 "[ID] = '+@ID+',",

but the ID of the Instance is needed for further Reporting.

Why is the converting appearing, and how can I stop it (Where is my mistake)?

Sorry, don't know how to highlight a comment.

Scott Hodgin postet the solution:

[Size in MB] = CAST( ((SUM(cast(Size as bigint))* 8) / 1024.0)as decimal),

 is the correct line, and solution to my problem:)
Thank you very much:) !


Error creating database on new storage in SQL Server 2008 R2 Failover Cluster Via the Failover Cluster Manager, I recently added a new disk to an existing SQL Server 2008 R2 Cluster, running on Windows Server 2012 R2.

When attempting to create a database on this new storage, I get the following error:

Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15105) while attempting to open or create the physical file 'J:\DATA\mydatabase.mdf'.
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

I can manually add a file to J:\data
The new storage can be moved between nodes without any errors
The SQL Server Service Account has full control over the entire J:\ drive
The SQL Server Service Account has full control over the J:\DATA
Here is a layout of the drive, you will notice that DATA is a seperate volume mounted inside J:\

Can someone please help me figure out why I am getting this error?

Update

From James, if I add a subdirectory to data, then the create database command works

CREATE DATABASE [MCO_DB] ON  PRIMARY
( NAME = N'MYDATABASE_DB', FILENAME = N'J:\DATA\MYDATABASE\MYDATABASE_DB.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'MYDATABASE_DB_log', FILENAME = N'J:\TRN\MYDATABASE_DB_1.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

Operating system error 5 is a simple Access is denied you can always check those errors in the command prompt net helpmsg 5.

Now Volume mount points are sometimes a bother. In Windows server 2000 the calcs.exe had an error and could not set the permissions on the underlying drive and the only way to do it was to mount the volume using a drive letter, set permissions on the root folder and then create a mountpoint.

Later versions of windows should be able set the permissions on the mountpoint by using the /M switch so that

  cacls j:\data /g DOMAIN\ACCOUNT:f /M /E

Will grant the domain\account full permissions on the root of the drive mounted at j:\data, I still haven't leard to do this with icacls.exe and a comment how to do so will be well appriciated.

As you have found out creating a subfolder on the mount point makes it possible for you to create an accesslist for that folder but there still seem to be issues with setting permissions on the root folder of the drive using the GUI.

Usually I dont bother with the calcs command and if I need to set permissions on the root I'll add a driveletter, set the permissions using the GUI and then remove the drive letter.


When are COLUMN aliases in FROM clauses needed? FROM provides column_alias, the SQL spec calls these <derived column list> clauses. This is what the postgres docs say about them,

  A substitute name for the FROM item containing the alias. An alias is used for brevity or to eliminate ambiguity for self-joins (where the same table is scanned multiple times). When an alias is provided, it completely hides the actual name of the table or function; for example given FROM foo AS f, the remainder of the SELECT must refer to this FROM item as f not foo. If an alias is written, a column alias list can also be written to provide substitute names for one or more columns of the table.

When are these needed? When can I not otherwise just use a COLUMN alias?

SELECT t.*
FROM table_name AS t (a,b,c);

vs

SELECT t.col1 AS a, t.col2 AS b, t.col3 AS c
FROM table_name AS t;

This example taken from the chosen answer by @ypercubeᵀᴹ which doesn't seem too useful.

FROM aliases in the above context provide no real benefit unless you're

relying on partial aliasing (NOT aliasing the whole table)
that is dependent on ascending column ordering
your table has more than three columns (or you could just write it explicitly in the from clause).

It seems like doing that relies on the hazards of regular t.* stacked with added obscurity. So when is FROM aliasing useful?

If the columns don't exist (VALUES clause)

PostgreSQL gives them default names, but that's besides the point. You can't alias them in a COLUMN list that doesn't exist.

SELECT t.*
FROM (VALUES ('row1',1), ('row2',2)) AS t;

That would be a great example of the VALUES LIST syntax which requires you to alias in FROM list.

SELECT t.*
FROM (VALUES ('row1',1), ('row2',2)) AS t(english,rownum);

From there you can even use the alias in the select list.

SELECT rownum
FROM (VALUES ('row1',1), ('row2',2)) AS t(english,rownum);

And, that brings us to a complex example like this one found on page 190 of PostGIS in Action, Second Edition.

SELECT
  oid --**only possible because of FROM ALIASING.**
  , lowrite(lo_open(oid, 131072), img) As num_bytes
FROM (
  VALUES (
    lo_create(0),
    (
      SELECT
        ST_AsGDALRaster(
          ST_Band(rast,1)
          , 'USGSDEM'
          , ARRAY[
            'PRODUCER=' || quote_literal('postgis_in_action')
            , 'INTERNALNAME=' || quote_literal(rast_name)
          ]
        ) As dem
      FROM ch07.bag_o_rasters
      WHERE rast_name = 'Raster 1 band heatmap'
    )
  )
) As v(oid,img); --**FROM ALIASING**

If that's hard to swallow there is one other awkward construct there and that's a SELECT as a COLUMN.

SELECT ( SELECT 1 ), ( SELECT 2 ); -- one row "1,2"

SELECT lo_create(0), ( SELECT 2 ); -- one row "oid,2"

lo_create returns an oid. That's beyond the scope of this answer though.

For another real world example, see this question I just answered.

In a SELF JOIN

This may be worth mentioning since it is in the docs,

SELECT * FROM temp;
 foo |  bar
-----+--------
   1 | evan
   2 | dba.se
(2 rows)

And you self-join you'll get two bar columns.

SELECT * FROM temp JOIN temp AS t2 USING (foo);
 foo |  bar   |  bar
-----+--------+--------
   1 | evan   | evan
   2 | dba.se | dba.se
(2 rows)

But, with FROM aliasing you can label them separately,

SELECT * FROM temp JOIN temp AS t2(foo,bar2) USING (foo);
 foo |  bar   |  bar2
-----+--------+--------
   1 | evan   | evan
   2 | dba.se | dba.se
(2 rows)

However, that's not really better than the COLUMN-aliasing method,

SELECT foo, temp.bar, t2.bar AS bar2
FROM temp JOIN temp AS t2
  USING (foo);

The only difference is that uniquely in the FROM-aliasing method you refer to all instance of t2.bar as bar2.


Why is my DR AG database in the "recovery pending" state? I am trying to build an architecture where we get automatic failover for both HA and DR for our DBs using SQL Server 2016 AlwaysOn Availability Groups.  When I tested this using only one Availability Group, the DR DB said it was in a 'recovery pending state'.

Do I need to put the same DB on two Availability Groups to avoid the Quorum problem (one for HA and one for DR)?

Is it possible to have one DB on 2 AGs (one AG for a AG Cluster and one only for DR)?

You cannot put one database into multiple availability groups. The likely cause of the recovery pending state is related to your cluster configuration. If you only have two nodes and no file share or disk witness then your cluster can sustain 0 node failures.

If you're testing by shutting down node 1 and you don't have a quorum witness (file share or disk) then node 2's cluster service will stop and the AG will not bring the databases online.

Run a cluster validation to confirm how many node failures the cluster can sustain (needs to be at least 1) and validate your quorum configuration.


Dealing with data stored as arrays in a MySQL DB So I know storing arrays in a DB field is wrong and would never do it myself, however a 3rd party plugin my company is using stores data in an array and I was wondering if you could help me try to deal with it.

It basically seems to link 2 tables and add a view count. Here is an example of the data:

a:4:{i:4;i:196;i:26;i:27;i:5;i:155;i:34;i:4;}

So I think this means there are 4 entries in the array, each with 2 attributes. The first - i.e. 4, 26, 5, 34 are "store codes". The second lot (196, 27, 155, 4) are number of plays. God knows why they are stored like this as there is already another table that links the video with the stores and they could've just stuck another column there for view count.

Anyhow, what I want to do is order by view count based on store id within that array. Do you think this is possible and does anyone have any ideas how to do this? If storing data like this is a standard, do you know the name for it as I could probably take it from there?

Thanks!

Just as I was about to post a comment that this looked like no serialization format I've ever seen, I had a lucky flash of insight.  (What programming language would make it altogether too easy to stash an array in a string like that? Ah...)

I think you may find that what you're looking at is the output of the serialize() function in PHP... the companion function is unserialize().

I'm not aware of a mechanism for deserializing this natively in MySQL (but then again, I wasn't aware of the whole thing a few minutes ago)... but if I had to do it natively in MySQL, I would go to the source code of common_schema for ideas and helper functions like get_num_tokens(), which returns a count of the number of tokens found in a given string of delimited text.  There is genuine outside-the-box genius lurking in common_schema.

Untwizzling scalar values into rows is not something easily done in MySQL but prettify_message() in common_schema provides an example of how it is technically possible... by splitting the strings and writing what you find in to a new table, it seems possible and maybe even borderline practical if you're not dealing with a massive data set.  Figure that part out and you could theoretically even build a trigger to keep your more-properly-structured table syncronized whenever one of those serialized columns is updated.

Or you could write something in php to read from the database, deserialize the arrays, iterate them, and stuff a table, but doing it natively in MySQL would be much more fun and at least offers the potential to keep another table always-current when you need it without an external script.


Changing from utf8 to utf8mb4 MySql database at the moment has:

Character Set: utf8
Default Collation Name: utf8_unicode_ci

I want to change it to:

utf8mb4
utf8mb4_unicode_ci

I would also do the same thing for all tables in the database.

My question is the following:

By doing that change is there a possibility that the content of my columns gets messed up in any way? (Cause I am really scared to do it!!!)
I have millions of rows in the tables, is there ANY way to do a check after I make the switch that the content of all tables is the OK?

Some crazy ideas I am thinking of include:

Running some scripts to compare original DB to changed DB
Running some scripts to calculate column sizes of original DB to changed DB

Any help or ideas which would guide me to the right direction would be appreciated.

THANK YOU

Main thing to watch altering tables is the index length will increase for utf8 indexed columns which might put them over limit. Adjust URL for your mysql version as some differences apply. These index length exceeded errors will show up immediately on ALTER TABLE so this will be a final bit of the migration.

On validation, generally won't be a problem. Try this on a few tables to get confidence in the idea confidence on smaller tables:

   CREATE TABLE tbl_new LIKE tbl
   ALTER TABLE tbl_new ..  {change to utf8mb4 fields}
   INSERT INTO tbl_new SELECT * FROM tbl
   RENAME TABLE tbl_new TO tbl, tbl TO tbl_old

A SELECT ... tbl JOIN tbl_old ON tbl.pk = tbl_old.pk WHERE tbl.utf_field != tbl_old.utf_field can be used to validate values.

Application behavior can be checked, and you can RENAME back if their are problems. (taking to account updates).


Is it bad practice NOT to delete unused database entities? A recent code review by a client revealed that there some database entities (tables, views, stored procedures) were no longer being used in our Oracle 12c database. We have hundreds of stored procedures, views and tables (not thousands), and the database itself is about 40GB in size with a few hundred million records. I don't know the number of unused entities but I'm told it would take 4 hours or so to remediate.

The problem is that regression testing would take a huge amount of time.

I've been told that having unused entities is common and is not necessarily bad as they could be used at some future time. But on the other hand, this can be confusing to others who are working on the system.

I'd like to have a better perspective on this -- can this be considered a fault developers need to remediate, or is it a non-issue?

Thanks

In case of tables, if it's not being updated you are safe to remove then and keep a backup (just in case).

Other objects like SP, Views and Functions can be easily removed.

Of course you can end up messing with relational integrity if you are not cautions in removing data. I recommend full regression tests over the system.

The advantages of removing unused objects is keeping the schema (and the system) clean and simple, this helps maintenance.

The disvantage is the risk of lost of essential data, damaging relational integrity and jeopardizing the system.

Also be aware if you remove a substancial mass of data it smells like trouble.

Edit

This is long comment full of "IMHO".
I'm a developer happened to get some dba tasks by accident. When they say "it's better let things as they are" it means "ok, cleaning the schema is fine but developers and testers will get they hands full of (unnecessary?) work and they are already full"

And that's true, in special if there acumulating garbage from years of "better let things as they are". Developers can need to remove references to that superflous objects. Testers must testify nothing ill blow up, etc.

At the end of the day someone needs to make the hard decision: To clean or not to clean, that's the question. If you can (do you have good documentation? have the time? Can alloc coworkers?) make a "possible impacts" report to your manager where you can pontuate the advantages, disvantages, risks and answer one little question: Are us just postponing a job needs to be done some point in the future?
Btw the short answer for your quastion is Yes!


Could not start the SQL Server Agent (SQLEXPRESS) Possible Duplicate:
  Sql Server agent can not start

I could not start the SQL Server Agent (SQLEXPRESS) instance as it will give me this error:

The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details

I search for some solution and they say that I should remove SQL Server 2008 Express Edition. However, I do not have SQL Server 2008 Express Edition and thus did not install it.

Also, I notice another SQL Server Agent (MSSQLSERVER), in which I am able to start it and let it run. However, I try using my local account and even the sa account, I could not get into this instance. (The SQL Server (MSSQLSERVER) is running too)

So, may I know what am I missing?

SQL Server Agent is not available with Express. It does install but that is just the false hope Microsoft puts in for some reason.

If you are seeing the "(EXPRESS)" within SQL Server Configuration Manager it was likely installed at some point in time and an attempt to remove it left some things behind. Express edition can get installed if you happen to have installed Visual Studio 2010 for instance.

Since you see the "(MSSQSERVER)" it would tell me that the default instance is the full version (Standard or higher) of SQL Server. There are a few ways to "break" into the default instance you have on your machine/server. However it depends on your environment as to what method you want to use.


SELECT from Function that return CURSOR I would like to select from a function that return a  SYS_REFCURSOR Type value.

for example :

CREATE OR REPLACE FUNCTION my_funtion (
        my_param IN VARCHAR2)
     RETURN SYS_REFCURSOR
  IS
     l_return   SYS_REFCURSOR;
  BEGIN
           OPEN l_return FOR
                SELECT last_name, first_name
                  FROM employees
                  WHERE id = my_param
              ORDER BY employee_id;
     RETURN l_return;
END my_funtion;

I would like to do something similar to this :

select * from  my_function('id015');

or even :

select alias.last_name from  my_function('id015') alias;

You could try using
select * from table(xmlsequence( myfunc() )) . In this case you get xml in columns.

Or try this example to extract fields from your refcursor

select extractvalue(column_value,'/ROW/FIRST_NAME') first_name
     , extractvalue(column_value,'/ROW/LAST_NAME') last_name
from table(xmlsequence(f()));

FIRST_NAME | LAST_NAME
:--------- | :--------
Donald     | Duck
Mickey     | Mouse

dbfiddle here

PS. Note about xml overhead.


SQL not engaging parallelism for extremely large query I have a very large query (~630 lines) that involves a lot of nested SELECT statements and pulls from multiple views. Our SQL server has parallelism set to 2 with a threshold of 95 (set that way because our DBA was optimizing it based on some other applications). This query has recently started taking 5-10 minutes to complete, up from usually less than a minute. While investigating the cause, we noticed that it never seems to trigger parallelism, always running serial, and suspect that may have something to do with its performance. What's weird is while experimenting, we even dropped the threshold back down to the default value of 5 seconds, and it still won't run parallel. What could be preventing it?

We've been doing the testing on a non-production environment that nobody else was using at the time, so this was the only query being run. Our DBA also tried things like clearing caches and plans, and even recycling the system, but it had no effect.

Update 1: Per comments, I've verified that stats are updated nightly but the problem persists. We're actually rolling back the code to an earlier version that didn't have such serious performance issues, but will continue to test this code because it was supposed to improve performance over the old code, and actually did in initial testing. Will update here accordingly.

What's weird is while experimenting, we even dropped the threshold back down to the default value of 5 seconds, and it still won't run parallel. What could be preventing it?

There are many reasons that the optimizer will not (or cannot) produce a parallel plan. Broadly:

Obvious causes, such as the configuration setting max degree of parallelism being set to one, running under a Resource Governor workload group with MAX_DOP = 1, or having only one logical processor available to SQL Server
Parallelism-inhibiting operations

There are many operation that prevent parallelism, either because they make no sense in a parallel plan, or because the product just does not support them (yet).  Some examples (not exhaustive!) of things that force the whole plan to be serial:

Modifying the contents of a table variable (reading is fine)
Any T-SQL scalar function (which are evil anyway)
CLR scalar functions marked as performing data access (normal ones are fine)
Some intrinsic functions including OBJECT_NAME, ENCYPTBYCERT, and IDENT_CURRENT
Fast Forward cursors
System table access (e.g. sys.tables)

Any reference to a table (or view) with a computed column that uses a non-inline T-SQL scalar function will result in a serial plan, even if the that column is not referenced in the query.
Cardinality estimation errors

If there is nothing that absolutely prevents parallelism in the target query, the optimizer may still choose a serial alternative if it has a lower estimated cost. This can be caused by incorrect cardinality estimation.
Costing model limitations

SQL Server uses a model to estimate the runtime cost of each operator in a query plan. This model may incorrect cost a serial plan cheaper than the parallel alternative. The optimizer always chooses the cheapest-looking option it considers.

When SQL Server costs a parallel plan, it generally reduces the CPU cost for a parallel iterator by the a factor equal to the expected runtime DOP. Plans with Nested Loops joins can be a special problem, because the inner side almost always runs multiple threads serially. The parallelism icons are still present, but they indicate that there are DOP independent serial threads.

The distinction is perhaps a subtle one, but it (a) explains why operators that normally force a serial zone can run 'in parallel' on the inner side of a loops join; and (b) the optimizer does not reduce the CPU costs on the inner side by the estimated runtime DOP. This puts nested loops at a disadvantage when it comes to parallelism costing, compared with Hash and Merge Joins, and can explain why the optimizer may fail to choose a parallel Nested Loops plan.
Optimizer code path issues

The optimizer may not get as far as evaluating a parallel plan at all.  One way this can occur is if the final plan is found during the Trivial Plan stage.  If a Trivial Plan is possible, and the resulting cost is less than the configured cost threshold for parallelism, the full optimization stages are skipped and a serial plan is returned immediately.

Queries that pass Trivial Plan may still terminate optimization early (before parallelism is considered), either with a Good Enough Plan Found message, or a Time Out. Both of these are heuristics to prevent the optimizer spending more time optimizing than it stands to gain by reducing estimated execution time (cost).

Testing

There is no supported way to require a parallel plan but there are a couple of undocumented tricks (not suitable for production). One is to temporarily set the CPU weighting used during optimization much higher, and the other is to set trace flag 8649. For SQL Server 2016 SP1 CU2 and later, the undocumented query hint OPTION (USE HINT ('ENABLE_PARALLEL_PLAN_PREFERENCE')) performs the same function as TF 8649, but without the need for admin permissions.

The plan produced might not be one the optimizer would normally consider, but you may be able to capture it and use it in a plan guide in production after careful testing and review.

For more information, see my article Forcing a Parallel Query Execution Plan and Non Parallelizable operations in SQL Server by Simon Sabin.


Unable to SQL => Getting content of last 7 days I thought I was good with SQL till I was told to get the content of all 30 days. I do have a column called "connect_time" which has date and time in the following format: 2014-08-28 11:11:42

All my queries have been a fail till now. I tried the following https://stackoverflow.com/questions/13266130/how-to-select-last-one-week-data-from-todays-date

I just tried:

SELECT  *
FROM   Table_Name
WHERE  connect_time >= DATEADD(day,-7, GETDATE())

I cannot understand the reason its wrong and I am trying to find the syntax so I can edit it myself but I am getting confused what part of the code is syntax and what is their column names.

Using DATE_ADD() or DATE_SUB()

SELECT * FROM Table_Name
WHERE connect_time >= DATE_ADD(CURDATE(),INTERVAL -7 DAY);

or

SELECT * FROM Table_Name
WHERE connect_time >= DATE_SUB(CURDATE(),INTERVAL 7 DAY);

Without those functions, you can also do

SELECT * FROM Table_Name
WHERE connect_time >= (CURDATE() + INTERVAL -7 DAY);

or

SELECT * FROM Table_Name
WHERE connect_time >= (CURDATE() - INTERVAL 7 DAY);


Undo Database Restore in SQL Server 2008 I accidentally restored a SQL Server 2008 database with a wrong backup. Is there any way to undo this and restore to previous version?

Unfortunately no. The only solution is to do another restore with the proper backup file(s).


User connections without a login I was looking at SQL Server performance and noticed that CPU utilization goes high when the number of connections go high(I believe it is normal).Usually number of connections  on an average was approximately 120 but from past 1 week the number of connections on an average goes to 200+ and sometimes even peaks to 400+. This is making the CPU utilization go high and in turn memory pressure starts occurring subsequently resulting in plan cache flush.(I might have to turn on LPIM as a long term solution)

This is causing performance issue. So I checked open sessions for the database and below is result:

Those 70 connections/sessions are from actual users accessing the application over network. Few connections are from SSRS, last 4 set of connections are of remote desktop connections from developers/DBAs.

I have no idea as what those 120 connections are, these connections don't even have LoginName.

Captured more details from sys.dm_exec_session.
Seems, those sessions without a loginname doesn't do anything. It uses 0 cpu time,0 logical reads etc.

I am not sure where else I should look at.

Result from sys.syslogins for the sid.
The login is the same login from actual users.

The 70 connections are using the generic sql login for the .Net application.
Other 120 connections with no login name has the same SID.

Does it happen when the user logs into application and switches to another module and afterwards comes back to the same module and a duplicate session is created and leaving the actual session open?

Here is the result of

  SELECT con.*, '--' AS [---], ses.*, '--' AS [---], req.*
  FROM sys.dm_exec_connections con
  INNER JOIN sys.dm_exec_sessions ses ON con.[session_id] = ses.[session_id]
  LEFT JOIN sys.dm_exec_requests req ON req.[session_id] = con.[session_id] WHERE ses.[login_name] = N''

Finally found out what those sessions without a login name is going.
They are running the functions which gets called form the main queries/stored procedures.

Here is the query i used.

SELECT
  con.connection_id,ses.status,ses.host_name,ses.host_process_id,ses.program_name,ses.last_request_start_time,ses.last_request_end_time,req.status,req.command,
  Substring(st.TEXT,(req.statement_start_offset / 2) + 1, ((CASE req.statement_end_offset WHEN -1 THEN Datalength(st.TEXT) ELSE req.statement_end_offset END - req.statement_start_offset) / 2) + 1) AS statement_text

  FROM sys.dm_exec_connections con
  INNER JOIN sys.dm_exec_sessions ses ON con.[session_id] = ses.[session_id]
  LEFT JOIN sys.dm_exec_requests req ON req.[session_id] = con.[session_id]
  CROSS APPLY sys.Dm_exec_sql_text(req.sql_handle) AS st
  WHERE ses.[login_name] = N''

Those sessions runs only functions.
Not sure why this is happening. Is that a normal behavior?

You might want to query relevant tables like:

sys.dm_exec_connections
sys.dm_exec_sessions
sys.dm_os_waiting_tasks
sys.dm_exec_requests
sys.server_principals
sys.databases
sys.dm_exec_sql_text()
sys.dm_exec_query_plan

...to have an overview of what is happening even if nothing is happening.

Query Running Tasks (and Other Stuff)

Run something like this to see what is going on in your system and which ressources are being used.

SELECT des1.session_id AS Session_ID_S,
       --des1.context_info as Session_Contxt_Info,
       --dec1.session_id AS Session_ID_C,
       dowt.session_id AS Session_ID_WT,
       dowt.exec_context_id AS Exec_Contxt_ID,
       sdb.name AS DatabaseName,
       ssp.name AS SQL_Login_Name,
       des1.nt_user_name AS NT_User_Name,
       --'--kill ' + cast(dec1.session_id as nvarchar(20)) as killcommand,
       dowt.wait_duration_ms AS Wait_Duration_ms,
       dowt.wait_type AS Wait_Type,
       dowt.blocking_session_id AS Blocking_Session_ID,
       dowt.resource_description AS Ressource_Description,
       des1.program_name AS Program_Name,
       dest.[text] AS SQL_Text,
       deqp.query_plan AS Query_Plan,
       des1.cpu_time AS CPU_Time,
       des1.memory_usage AS RAM_Usage
FROM   sys.dm_exec_sessions AS des1
       LEFT
       JOIN sys.dm_exec_connections AS dec1
            ON  des1.session_id = dec1.session_id
       LEFT -- comment out LEFT to display only sessions that have gone parallel
       JOIN sys.dm_os_waiting_tasks AS dowt
            ON  des1.session_id = dowt.session_id
       LEFT -- comment out LEFT to display only sessions currently executing statements
       JOIN sys.dm_exec_requests AS der
            ON  des1.session_id = der.session_id
       LEFT
       JOIN sys.server_principals AS ssp
            ON  des1.login_name = ssp.name
       LEFT
       JOIN sys.databases as sdb
            ON des1.database_id = sdb.database_id
       OUTER APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest -- Retrieve Actual SQL Text
       OUTER APPLY sys.dm_exec_query_plan(der.plan_handle) AS deqp -- Retrieve Query Plan (XML)
WHERE  des1.is_user_process = 1
ORDER BY
       des1.session_id, dowt.exec_context_id

Query Output

It will provide you with an overview of all the tasks running and under which account the "root" session was made if you have parallel processes (CXCONSUMER/CXPACKET). An example of an output will look like the following screenshot:

Your output will be different depending on the number of LEFT commands that you comment out. If you leave them all in, then you will have a full list of all sessions (without system sessions session_id < 50) and you should be able to pinpoint the issue.

Answering Your Question

  Does it happen when the user logs into application and switches to another module and afterwards comes back to the same module and a duplicate session is created and leaving the actual session open?

That is something we cannot answer. We don't know how your application is designed and whether or not a module switch will initiate a new connection.

If your application has some form of threading in the background, then you might be just observing the threads for each connection in the database as you can see in my example output. OR you might be observing that the query is running in parallel, because the query optimizer has decided that your query is best processed (partly) in parallel.

If you are still seeing sessions without a login name as in the next example:

...then you at least can see which Application / Program / Statement / Wait Type is causing the issues. Armed with this information you can dig further.


Duplicate key value violates a unique constraint I've been looking at other posts. It seems many people have had this problem, but I'm somewhat new to Postgres and tried other solutions with no luck.

I have ny_stations table:

CREATE TABLE ny_stations (
id integer primary key,
name text,
latitude numeric,
longitude numeric,
nyct2010_gid integer,
boroname text,
ntacode text,
ntaname text
);

I am trying to insert values from another table, ny_raw_trips:

INSERT into ny_stations (id, name, latitude, longitude)
SELECT DISTINCT start_station_id, start_station_name, start_station_latitude, start_station_longitude
FROM ny_raw_trips
WHERE start_station_id NOT IN (SELECT id FROM ny_stations);

Getting the error:

  ERROR:  duplicate key value violates unique constraint "ny_stations_pkey"
  DETAIL:  Key (id)=(151) already exists.

What am I doing wrong? Also,

SELECT pg_get_serial_sequence('ny_stations', 'id');

...returns nothing. Please let me know if any other info is required.

The id field in your ny_stations table does not seem to be defined as a serial, so it is expected that pg_get_serial_sequence will return nothing.

The duplicate you get relates to one of the records in your SELECT DISTINCT ... FROM ny_raw_trips ... is returning two rows with the same id:

SELECT start_station_id, COUNT(*) FROM (
  SELECT DISTINCT start_station_id, start_station_name, start_station_latitude, start_station_longitude
  FROM ny_raw_trips
  WHERE start_station_id NOT IN (SELECT id FROM ny_stations)
  ) a
GROUP BY start_station_id
HAVING COUNT(*) > 1;

You could list the rows that are introducing the duplication like this:

WITH src AS (
  SELECT DISTINCT start_station_id, start_station_name, start_station_latitude, start_station_longitude
  FROM ny_raw_trips
  WHERE start_station_id NOT IN (SELECT id FROM ny_stations)
  )
SELECT *
FROM src
WHERE start_station_id IN (SELECT start_station_id FROM src GROUP BY start_station_id HAVING COUNT(*) > 1)
ORDER BY start_station_id;

Edit

Once you find the offending duplicates, should you consider that the first occurrence of each case is good enough (e.g. trivial differences in description or coordinate fields), you can use DISTINCT ON:

INSERT into ny_stations (id, name, latitude, longitude)
SELECT DISTINCT ON (start_station_id) start_station_id, start_station_name, start_station_latitude, start_station_longitude
FROM ny_raw_trips
WHERE start_station_id NOT IN (SELECT id FROM ny_stations)
ORDER BY start_station_id;


How to achieve federation using JDBC? I want to achieve federation using following commands through JDBC.

connect to SIGCAP_M user db2sigcp using db2sigcp ;

drop wrapper drda;
create wrapper drda ;

UNCATALOG NODE XYZ;
CATALOG TCPIP NODE XYZ REMOTE 10.XXX.XX.XX SERVER 446 ostype OS400;

UNCATALOG DB as4xyz;
CATALOG DCS DATABASE as4xyz as as4xyz;
CATALOG DB as4xyz as as4xyz AT NODE xyz authentication DCS ;

attach to xyz user "kasparov" using "kasparov123";

drop server xyz;
create server xyz type DB2/400 version 7 wrapper DRDA authorization db2ngicp PASSWORD db2ngicp options( add dbname 'S845F85V', DB2_TWO_PHASE_COMMIT  'Y'  ) ;

CREATE USER MAPPING FOR db2sigcp SERVER EQUATION OPTIONS ( ADD REMOTE_AUTHID 'db2ngicp',ADD REMOTE_PASSWORD 'db2ngicp') ;

CREATE nickname E_NEPF FOR as4xyz.KFILLV.NEPF;

As per IBM documentation - https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0012547.html

SYSPROC.ADMIN_CMD doesn't support any of the above commands. How can I make this work?

CREATE WRAPPER, CREATE USER MAPPING and CREATE NICKNAME are all straight-forward SQL statements so you can execute them through JDBC the same as you would, say, a CREATE TABLE statement (same goes for DROP WRAPPER).

However, ATTACH, CATALOG, and UNCATALOG are entirely different beasts. They don't operate on a DB2 database, rather they affect a DB2 instance. I'm not aware of a method of executing these from JDBC (which is largely database-centric and doesn't have any special knowledge of structures "above" the database level). If all this needs to go together you're probably best off shoving it all in a batch file and executing that in a sub-process rather than trying to shoe-horn it into JDBC.


Query to select distinct name based on several factors I have a table with the following data (simplified here):

ID | PolicyName | Setting | State | Value

1 Default DiskUsage Enabled 1
2 Default Memory Enabled 1
3 Default CPU Enabled 0
4 Personal DiskUsage Enabled 0
5 Personal Memory Enabled 1
6 Personal CPU Enabled 0
7 Custom DiskUsage Enabled 1
8 Custom Memory Enabled 0
9 Custom CPU Enabled 1

I need to run a query looking for policy names that have Setting of DiskUsage Enabled and set to 1 along with Memory Enabled and set to 1.  So it should return:

PolicyName

Default

Not sure how I can go about this one.

Assuming the data is constrained to avoid duplicates, how about something like this?  This is assuming Microsoft SQL Server syntax.

SELECT United.PolicyName FROM
(SELECT PolicyName FROM dbo.Policies
WHERE Setting = 'DiskUsage'
  AND State = 'Enabled'
  AND Value = 1
UNION ALL
SELECT PolicyName FROM dbo.Policies
WHERE Setting = 'Memory'
  AND State = 'Enabled'
  AND Value = 1
) AS United
GROUP BY United.PolicyName
HAVING COUNT(*) = 2

RESULTS: Default

This specifically queries for each of the states that you care about.  Since there are two requirements, then the UNION will produce two rows when both selects are find a row.


Size of Postgres database growing Our Postgres database is currently at about 68G of size and growing at a rate of 6G/month. I am investigating ways how to scale the database.

Would be nice if someone can give some advices as I am new about this?

One suggestion I got is to use Amazon RDS. But it has some limitations as we replicate some particular tables between the database instances using Londiste.

Thanks

6 GB a month is only 72 GB a year. so to get to 1 TB of data it is around 15 years. clearl, the amount of data should not be your concern unless you are really doing something fancy.

what kind of queries do you run to require such a small database?


Calculate number of consecutive years (not gapless) I have a request that wants to look at numbers of consecutive year buyers. For example, I have a table like this:

Order_year, Contact
2015, 123
2014, 123
2013, 123
2011, 123
2010, 123
2015, 234
2014, 234
2011, 234

I want to calculate for each year how many consecutive order years each contact_id has. The problem is, there are gaps such as contact_id = 123, it has 2010-2011, then 2013-2015.

The output would be:

report_year, contact, consecutive_years
2015, 123, 3
2014, 123, 2
2013, 123, 1
2012, 123, 0
2011, 123, 2
2010, 123, 1
2015, 234, 3
2014, 234, 2
2013, 234, 1
2012, 234, 0
2011, 234, 1

Or if you just want to calculate the max order year with the max consecutive years, that's fine, such as:

order_year, contact, consecutive_years
2015, 123, 3
2011, 123, 2
2015, 234, 2

So I've built a short SQL script to do this operation, based on the original sample input data:

Order_year, Contact
2015, 123
2014, 123
2013, 123
2011, 123
2010, 123
2015, 234
2014, 234
2011, 234

The script is as follows:

WITH RECURSIVE ranked_order AS(
  SELECT order_year, contact, 1 AS counter FROM orders
  UNION
  SELECT orders.order_year, orders.contact, ranked_order.counter + 1
  FROM orders JOIN ranked_order
  ON orders.order_year-1 = ranked_order.order_year
  WHERE orders.contact = ranked_order.contact
),
max_from_ranked_order AS(
  SELECT order_year AS report_year, contact, max(counter) AS consecutive_year
  FROM ranked_order
  GROUP BY order_year, contact
),
start_and_consecutive_years AS(
  SELECT  contact, max(consecutive_year) AS consecutive_years,
  report_year - consecutive_year AS start_year
  FROM max_from_ranked_order
  GROUP BY contact, start_year)
SELECT *, start_year + consecutive_years AS end_year
FROM start_and_consecutive_years
ORDER BY contact;

Now, for a little explanation:

Recusive Query

First, you need to find all the occasions of consecutive years, so I built the ranked_order table(maybe a bad name... maybe should have called it counted_order?) to count instances where a given contact ordered in back-to-back years.

Filter Results

So, due to the recursive query, I am left with all counted elements, rather than simply the maximum consecutive counts.  So, I built max_from_ranked_order to extract the max counter value per order_year,contact grouping.

A little arithmetic...

This still doesn't quite yield what we need.  Since we grouped over order_year,contact, you still end up with entries like

report_year, contact, consectuive_year
2015, 123, 3
2014, 123, 2
2013, 123, 1
2015, 234, 2
2014, 234, 1
etc...

What you'll notice is that you have unique, information which you can group on by observing the starting year of the consecutive years.  So, I created start_and_consecutive_years to take the maximum number of consecutive purchasing years for any given starting year, on a per customer basis.

Finally, I get my result by adding the consecutive_years to the start_year, and presto, the final SELECT yields:

contact, consecutive_years, start_year, end_year
123, 3, 2012, 2015
123, 2, 2009, 2011
234, 2, 2013, 2015
234, 1, 2010, 2011

Note that it would be very easy to filter out for casesWHERE consecutive years >= 2 to ignore "irrelevant" results.

Now for the caveats: I wrote this series of CTEs for ease of readability and explanation.  There may be a more computationally or programatically efficient way to achieve your results, but on even a moderately sized table this should get you your results pretty quick.


merging many jsonb objects using aggregate functions in PostgreSQL? Is there a standard function in PostgreSQL (as of 12.x) to concatenate or merge many jsonb objects in a database column into a single jsonb object?

I know there is a the || operator since PostgreSQL 9.5 to merge two jsonb objects. But I need to merge many jsonb objects from a column. The linked documentation does not seem to have one unless I am missing something.

I had the same problem and this post solved it:
https://blog.faraday.io/how-to-aggregate-jsonb-in-postgres/
The idea is to create an aggregate:

CREATE AGGREGATE jsonb_object_agg(jsonb) (
  SFUNC = 'jsonb_concat',
  STYPE = jsonb,
  INITCOND = '{}'
);

See the linked article for more details.


MSSQL and ALTER AUTHORIZATION on table has no affect? I have a schema called [test] with an owner X.
I transferred an ownership of one table [test].[table] (ALTER AUTHORIZATION) to owner Y.
Now, if I understand correctly, even though the owner X owns the parent schema [test], he/she should have no access to [test].[table] since an owner of that table is Y, correct?
Or must I explicitly DENY permissions to X on that table?
I tested this and owner X still has an access to [test].[table].
And btw, X is not member of db_datareader or db_owner ;-)
So the question is, what is the purpose of ALTER AUTHORIZATION on an object/table if other users still have access to it (regardless if they are owners of parent schema)?
Best,
Mike

I think the following quote should explain what is going on
"Ownership of schema-contained objects can be transferred to any database-level principal, but the schema owner always retains CONTROL permission on objects within the schema."
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms189462(v=sql.105)


Creating a Trigger to Update Matching Records in Another Table Morning, All -

I'm currently working on some SQL logic, and wanted to get some input on it.

What I'm trying to accomplish is the following (Psuedo-code):

When a record is inserted into Table A -
IF inserted.Field5 = 'ABC'
    Select records from table B, where -
        TableB:Field1 = inserted:Field1
        AND
        TableB:Field2 = inserted:Field2
    Set TableB.Field3 for selected record(s) to 'Hello World'

While I've worked with SQL in the past, I usually use external programs and languages to manipulate data.

Using SQL directly, it looks like the best way to do this is a trigger.

I've come up with a rough approximation (below) of what this might look like, but wanted to get some input before I went too much further with it.

CREATE TRIGGER I_Am_A_Trigger
    ON TableA
        AFTER Insert
            AS
                IF inserted.Field5 = 'ABC'
                    UPDATE [TableB]
                    SET [Field3] = 'Hello World'
                    WHERE
                        [Field1] = [inserted].[Field1]
                        AND
                        [Field2] = [inserted].[Field2]

Does this look like I'm heading down the right track?

Also - how can I best deal with the possibility of multiple records being inserted at once?

Here is an example of joining the virtual inserted table with TABLEB.

--demo setup
Drop table if exists TableA
Drop table if exists TableB

Create table TableA (Field1 varchar(20), Field2 varchar(20), Field5 varchar(20))
Create table TableB (Field1 varchar(20), Field2 varchar(20), Field3 varchar(20))

insert into TableB(Field1,Field2,Field3) values('one','two','three')
insert into TableB(Field1,Field2,Field3) values('four','five','six')
go
--the trigger
CREATE TRIGGER I_Am_A_Trigger ON TableA
AFTER INSERT
AS
UPDATE tb
SET tb.[Field3] = 'Hello World'
FROM inserted i
JOIN TableB tb
    ON i.Field5 = 'ABC'     --make sure to only join where the inserted Field5 was 'ABC'
        AND tb.[Field1] = i.[Field1]
        AND tb.[Field2] = i.[Field2]
GO

insert into TableA(Field1,Field2,Field5) values
('one','two','abc')

select * from TableB

| Field1 | Field2 | Field3      |
|--------|--------|-------------|
| one    | two    | Hello World |
| four   | five   | six         |

Including a check on Inserted.Field5 in the UPDATE statement takes care of multiple inserts; only those inserted rows where Field5 has the correct value will be used to update tableB.


Altering CHAR(n BYTE) Column How to change CHAR (17 byte) to CHAR (16 byte) when there is already a data?

Values are something like this:

012345-000-00001

There is a space at the end.

I tried to trim it first before altering the column size but still gets the error due to column is already bigger than 16 byte (due the space at the end).

Thanks !

Modifying an Existing Column Definition

  You must set the initialization parameter BLANK_TRIMMING=TRUE to decrease the length of a non-empty CHAR column.

SQL> show parameter BLANK_TRIMMING;

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
blank_trimming               boolean     FALSE

SQL> alter system set BLANK_TRIMMING=true scope=spfile;

System altered.

Then bounce the database.

SQL> startup force;

SQL> update tblchar set col1=TRIM(col1);

SQL> alter table tblchar modify col1 char(16 byte);

Table altered.

Documentation:
Altering Tables


Select data with the latest table partition that actually holds data Version: SQL Server 2016

So, I need to be able to select the data within the most recent table partition. I can do this by using the below script but the latest partition doesn't contain any data. What I need is a way to select the latest partition within the table that actually contains data. Does anyone have any ideas.

There's a new partition created daily, but data doesn't always get inserted within the day. There's thousands of partitions that span many years so simply scrolling through each partition isn't ideal at all.

This is how I'm currently selecting the most recent partition (or should I say the max partition):

USE DBName
DECLARE @PartitionValue int
set @PartitionValue = (select max(boundary_id) from sys.partition_range_values)
SELECT * FROM schema.tablename
WHERE $PARTITION.partition_function_name(expression) = @PartitionValue

Try something like this (replace schema.tablename with your object name).  It worked for me in my limited testing.

DECLARE @PartitionValue INT

SET @PartitionValue = (
        SELECT TOP 1 partition_number
        FROM sys.dm_db_partition_stats
        WHERE object_id = object_id('schema.table_name')
            AND row_count > 0
        ORDER BY partition_number DESC
        )

SELECT count(*)
FROM schema.table_name
WHERE $PARTITION.pf_schema_table_name_data(expression) = @PartitionValue


Q1: I don't understand why primary is chosen here?

The index you made helps neither the WHERE or the ORDER BY due to the columns in the wrong order.

Since you selected only the erp_orders_id, the PRIMARY KEY was chosen.

  Q2: Why is this query so slow even with execution plan indicates that it scanned only 1 row?

Since you forced the index, the entire index was scanned until one row was found that matched o.orders_export_time >= '2015-09-20'. If you have rows from earlier months, even earlier years, those rows would be read until '2015-09-20' was finally found.

  Q3 how am I supposed to optimize this query?

As @dnoeth said in the comment, reverse the column order in the index.

ALTER TABLE `erp_orders`
ADD KEY `IDX_ORDERID_EXPORTTIME`(`orders_export_time`,`erp_orders_id`) USING BTREE;


How to re-arrange rows to best fit within groups? I categorized a table with the method introduced here to categorize my items to folders with a limit size of 100.
mixing_order  id     num_items  cat order_in_cat cumulative_sum folder
1             1      4          1   1            4              1
2             2      33         1   2            37             1
3             3      74         2   1            74             2
4             4      41         1   3            41             3
5             5      24         1   4            65             3
6             6      44         2   2            44             4
7             7      16         1   5            57             4
8             8      55         1   6            55             5
9             9      11         1   7            66             5
10            10     37         1   8            37             6

Now, I want to go further and re-arrange the rows to better fit within folders. Consider that the records are of two categories (indicated by column cat). We can make a better fit just by altering the order of mixing. Then, we can produce the following table as each folder has a cumulative_sum close to the folder size limit (i.e. 100 here). As you can see, the order in each category (order_in_cat) has not been changed, only the order that the rows from different categories are mixed.
mixing_order  id     num_items  cat  order_in_cat cumulative_sum folder
1             1      4          1    1            4              1
2             2      33         1    2            37             1
3             4      41         1    3            78             1
4             3      74         2    1            74             2
5             5      24         1    4            98             2
6             7      16         1    5            16             3
7             8      55         1    6            67             3
8             9      11         1    7            78             3
9             6      44         2    2            44             4
10            10     37         1    8            81             4

In other words, the mixing_order was random in order of INSERT, but now we want to re-arrange the mixing_order to best fit within folder.
Probably, we need a loop in which re-calculating SUM to find the best match out of possible choices, but I have no idea how to conduct such loop in SQL.

Here it the code from @DTest's Answer
SET @folder = 1;
SET @items = 0;
SELECT
    id, num_items,
    (SELECT IF(((@items:=@items+num_items)>100), @folder:=@folder+1, @folder)) as folder,
    IF(@items>100,@items:=0,@items) as checkItems
FROM foo;

Staring at this query, I would shudder to even think about writing of a Stored Procedure Technique for fitting items in folders. Then, it hit me ...
You could simply take the same query and ORDER BY num_items. That would actually pack as many items into a folder as possible before switching to another folder.
mysql> use ali
Database changed
mysql> CREATE TABLE foo (
    ->   id tinyint unsigned primary key AUTO_INCREMENT,
    ->   num_items tinyint unsigned
    -> );
Query OK, 0 rows affected (0.24 sec)

mysql>
mysql> INSERT INTO foo VALUES (1,4),(2,33),(3,74),(4,44),(5,24),(6,34),(7,46),(8,55),(9,11);
Query OK, 9 rows affected (0.17 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> SET @folder = 1;
Query OK, 0 rows affected (0.03 sec)

mysql> SET @items = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT id, num_items, (SELECT IF(((@items:=@items+num_items)>100),
    -> @folder:=@folder+1, @folder)) as folder,
    -> IF(@items>100,@items:=0,@items) as checkItems
    -> FROM foo ORDER BY num_items;
+----+-----------+--------+------------+
| id | num_items | folder | checkItems |
+----+-----------+--------+------------+
|  1 |         4 |      1 |          4 |
|  9 |        11 |      1 |         15 |
|  5 |        24 |      1 |         39 |
|  2 |        33 |      1 |         72 |
|  6 |        34 |      2 |          0 |
|  4 |        44 |      2 |         44 |
|  7 |        46 |      2 |         90 |
|  8 |        55 |      3 |          0 |
|  3 |        74 |      3 |         74 |
+----+-----------+--------+------------+
9 rows in set (0.08 sec)

mysql>

This packing would be somewhat lopsided in iterms of the number of entries per folder but the sum of the items per folder are pressed as close to 100 as possible.
Please note that if you want later folder with more items, simply change ORDER BY num_items to ORDER BY num_items DESC.
Give it a Try !!!
UPDATE 2013-01-09 12:52 EDT
What you need are three(3) mechanisms:

MECHANISM #1 : You need to imbed the query in a subquery
MECHANISM #2 : Order the subquery output by folder,num_items
MECHANISM #3 : Use a variable to monitor when you change folders from each row. When such a change occurs, you simply reset the folder counter to 1.

Here is the new code:
SET @order_val = 0;
SET @folder = 1;
SET @curr_folder = 1;
SET @items = 0;
SELECT *,
    @order_val:=IF(folder=@curr_folder,@order_val+1,1) ordervalue,
    @curr_folder:=folder current_folder
FROM
(
    SELECT * FROM
    (
        SELECT
            id, num_items,
            (SELECT IF(((@items:=@items+num_items)>100),
            @folder:=@folder+1, @folder)) as folder,
            IF(@items>100,@items:=0,@items) as checkItems
        FROM foo
    ) AA ORDER BY folder,num_items
) A;

Here is it execution:
mysql> SET @order_val = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @folder = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @curr_folder = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @items = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT *,
    ->     @order_val:=IF(folder=@curr_folder,@order_val+1,1) ordervalue,
    ->     @curr_folder:=folder current_folder
    -> FROM
    -> (
    ->     SELECT * FROM
    ->     (
    ->         SELECT
    ->             id, num_items,
    ->             (SELECT IF(((@items:=@items+num_items)>100),
    ->             @folder:=@folder+1, @folder)) as folder,
    ->             IF(@items>100,@items:=0,@items) as checkItems
    ->         FROM foo
    ->     ) AA ORDER BY folder,num_items
    -> ) A;
+----+-----------+--------+------------+------------+----------------+
| id | num_items | folder | checkItems | ordervalue | current_folder |
+----+-----------+--------+------------+------------+----------------+
|  1 |         4 |      1 |          4 |          1 |              1 |
|  2 |        33 |      1 |         37 |          2 |              1 |
|  5 |        24 |      2 |         68 |          1 |              2 |
|  4 |        44 |      2 |         44 |          2 |              2 |
|  3 |        74 |      2 |          0 |          3 |              2 |
|  6 |        34 |      3 |          0 |          1 |              3 |
|  7 |        46 |      3 |         46 |          2 |              3 |
|  9 |        11 |      4 |         11 |          1 |              4 |
|  8 |        55 |      4 |          0 |          2 |              4 |
+----+-----------+--------+------------+------------+----------------+
9 rows in set (0.00 sec)

mysql>

Give it a Try !!!


NodeJS problem with utf8_general_ci I have a website with a chat system. This chat system has a table 'messages' and one of the columns is 'messagetext' set to utf8_general_ci(MySQL). In my website, I use php to query database. The results are ok.

In my app, i use NodeJS as backend but the results are like that: " Î±ÏƒÎ´Î±ÏƒÎ´  ". There are greek words stored in this column.

I have tried iconv-lite,iconv,utf8,decode Json.

My thought, in the website it's getting the value 'Î±ÏƒÎ´Î±ÏƒÎ' from column and decoding to utf8, that's how can I see the right results. BUT in app this is not happening, the results are the value instead of the actual word.

What can I do?

(from Comment)

CREATE TABLE messages (
    id int(11) NOT NULL AUTO_INCREMENT,
    sender int(11) NOT NULL,
    reciever int(11) NOT NULL,
    seen varchar(3) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no',
    datetime datetime NOT NULL,
    messagetext text NOT NULL,
    userseen varchar(3) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no',
    PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=94 DEFAULT CHARSET=utf8

node.js needs something like this to start with:

var connection = mysql.createConnection({ ... , charset : 'utf8'});

Yeah, ασδασδ looks Greek to me.  What you have is "Mojibake".  See https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored for discussion of what causes Mojibake.

Do not use any form of encode/decode/conv -- it only compounds the problem. If that link does not help you, provide the extra details that it requests.

See also https://www.npmjs.com/package/utf8

The form should mention utf-8:

<form method="post" action="/your/url/" accept-charset="UTF-8">

Analysis...

'Κ' (Greek Kappa) is the 2 bytes, hex 'CE9A', in utf8.
   'Îš' in latin1 is hex 'CE9A' (2 bytes)
   'Îš', if converted to utf8 is hex 'C38E C5A1'
   'ÃŽÅ¡' is that that looks like when interpreted as latin1.

Note: this last hex is what you show in your Comment.

So, you seem to have "double encoding".

SELECT CONVERT(BINARY(CONVERT(CONVERT(BINARY(CONVERT('ÃŽÅ¡' USING latin1)) USING utf8mb4) USING latin1)) USING utf8mb4);  --> 'Κ'

What is missing is where "latin1" is occurring in the flow of the data.


Is it a bad practice to have several mutually exclusive one-to-one relationships? Say, a table car has one-to-one relationship to tables electric_car, gas_car, and hybrid_car. If a car is electric_car, it can no longer appear in gas_car or a hybrid_car, etc.

Is there anything wrong with such design? Some problems that may occur down the road?

The different types of cars are an instance of a general problem that surfaces over and over again in data modeling.  It is called "generalization/specialization" in ER modeling, and "superclass/subclass" in object modeling.

An object modeler uses the inheritance features built into the object model to solve the problem quite easily.  The subclasses simply extend the superclass.

The relational modeler is faced with a problem.  how to design the tables so as to emulate the benefits that one would get from inheritance?

The simplest technique is called single table inheritance.  Data about all types of cars are grouped into a single table for cars.  There is a column, car_type, that groups together all the cars of a single type.  No car can belong to more than one type.  If a column is irrelevant to, say, electric cars,  it will be left NULL in the rows that pertain to electric cars.

This simple solution works well for the smaller and simpler cases.  The presence of a lot of NULLs adds a tiny bit to storage overhead, and a little bit to retrieval overhead.  The developer may have to learn SQL three-valued logic if boolean tests are done on nullable columns.  This can be baffling at first, but one gets used to it.

There is another technique, called class table inheritance.  In this design, there are separate tables for gas_car, electric_car, and hybrid_car, in addition to a combined table, car, for all of them.  When you want all of the data about a specific kind of car, you join the car table with the appropriate specialized table.  There are fewer NULLs in this design, but you do more joining.  This technique works better in the larger and more complex cases.

There is a third technique called shared primary key.  This technique is often used in conjunction with class table inheritance.  The specialized tables for the subclasses have, as their primary key, a copy of the primary key of the corresponding entry in the car table.  This id column can  be declared to be both the primary key and a foreign key.

This involves a little extra programming when new cars are to be added, but it makes the joins simple, easy, and fast.

Superclasses and subclasses happen all the time in the real world.  Don't be afraid.  But do test your initial design for performance. If your first attempt is simple and sound, you'll be able to tweak it to speed it up.


Remove profile from user How do I clear user's profile?
I found how I can drop the profile (also using CASCADE), but I want to keep the profile and just remove it from the user.

All users have a profile assigned. You can alter the user to have the default profile by:

ALTER USER username PROFILE DEFAULT;


On Insert Trigger To Create Email I am wanting to send an email through the SQL Server Agent once an insert has been made to a specific table.  I tried the syntax below, but the email is not being sent, nor queued to send.

ALTER TRIGGER [dbo].[Insert_Trigger] ON [dbo].[ProdTable]

AFTER INSERT
AS

BEGIN

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Test',
    @recipients = 'XXXX@gmail.com',
    @body = 'Test',
    @subject = 'Test' ;

END

If I run the EXEC command by itself in a query window, it executes successfully, so I don't think it's an issue with my general configuration of Database Mail that's causing the problem.

What is the proper way to do this?

Database Mail does not send the mail through SMTP when you call sp_send_dbmail.  The message is put on a service broker queue, and sent by a background process.

So if the transaction in which the trigger executes is rolled back, then the message will be rolled back from the queue, and the mail will never be sent.  So that's one reason why your mail may not be sent.

Otherwise you can check the log table:

SELECT * FROM msdb.dbo.sysmail_log


PostgreSQL: How to create full copy of database schema in same database? How I can copy my public schema into the same database with full table structure, data, functions, fk, pk and etc.
My version of Postgres is 8.4
P.S. I need to copy schema NOT database

There's no simple way to do this in pg_dump/pg_restore itself.  You could try the following if you are able to remove the database temporarily.

Take a dump of your public schema using pg_dump
run "ALTER SCHEMA public RENAME TO public_copy"
Restore your dump of your public schema from step 1 using pg_restore


Is it ok to increase `innodb_stats_sample_pages` up to large number? MySQL sometimes generates bad execution plan irregularly.

2 years ago, I have increased innodb_stats_sample_pages(default:8) up to 64 and recently changed into 128 because Data Volume has been increased for 2 years.

I think 128 is still small. (128 pages * 16KB = Only 2MB. Am I right?)

Is it ok to increase it up to 1024 or higher? What are the points to consider?

How large value of pages are you using?

Changes to the statistics has plagued InnoDB since the beginning.  Fortunately it affects only a small percentage of queries.

Upgrade to a recent 5.6.  There is a significant improvement wherein the stats are no longer recomputed whenever you sneeze.  It is now essentially possible to set them once and forget them.  The number of "random dives" and which algorithm is used is now less important.

As mentioned, MariaDB (and eventually 5.7) has fancier statistics.


System View Full Qualification Ignored? Why does full qualification appear to be getting ignored?

Here, I USE the database I'm querying, and get the expected results:

use [_DBMgt];
GO
select
    s.[name] as [Schema],
    o.[name] as [Table]
    from [_DBMgt].[sys].[objects] o
    inner join [_DBMgt].[sys].[schemas] s
        on s.[schema_id] = o.[schema_id]
    where OBJECTPROPERTY(o.[object_id], N'IsUserTable') = 1
    order by s.[name], o.[name]
;
GO

Schema Table
------ ---------------
dbo    TableAbbrLookup

(1 row(s) affected)

Here, I did not USE the database I intend to query, but fully qualify the database reference; however, I am not getting the expected results.

use [master];
GO
select
    s.[name] as [Schema],
    o.[name] as [Table]
    from [_DBMgt].[sys].[objects] o
    inner join [_DBMgt].[sys].[schemas] s
        on s.[schema_id] = o.[schema_id]
    where OBJECTPROPERTY(o.[object_id], N'IsUserTable') = 1
    order by s.[name], o.[name]
;
GO

Schema Table
------ ----------------------
sys    sqlagent_job_history
sys    sqlagent_jobsteps
sys    sqlagent_jobsteps_logs

(3 row(s) affected)

Either I'm expecting the wrong thing (likely) or I've found a bug (unlikely).

What am I missing?

From the documentation relating to OBJECTPROPERTY:

  Remarks

  The Database Engine assumes that object_id is in the current database
  context. A query that references an object_id in another database will
  return NULL or incorrect results. For example, in the following query
  the current database context is the master database. The Database
  Engine will try to return the property value for the specified
  object_id in that database instead of the database specified in the
  query. The query returns incorrect results because the view vEmployee
  is not in the master database.


Postgres streaming replication to a different schema on another server? We've got a system with about a dozen Postgres 9.3 databases representing a dozen different applications. In order to do reporting across systems, we have a script that dumps each database and loads it back into a single database (on a second server) under a schema with the same name as the original database.

So for example, we've got the following databases on the main server:

A
B
C

and on the reporting server, we'll load those into a single database across multiple schemas, which ends up looking like this:

reporting.A
reporting.B
reporting.C

Anyway, the script that moves the data takes way too long to run and I was wondering if anyone can suggest a way to use Postgres's replication capability in order to make this process a live, continuous operation?

Streaming replication can't do this - it can only replicate the whole database cluster together, as a unit.

I think you want Londiste, Bucardo, or maybe even Slony-I for this. I've used Londiste for similar jobs and it works well.

There's also ongoing work on an in-core feature that meets this need, logical replication / bidirectional replication. It's pre-alpha quality, though, so I suggest waiting until it appears, hopefully in PostgreSQL 9.5. Parts of it have been included in 9.4, so some 3rd party tools may be able to use it down the track.


Remap array columns to single column with value Here is the sample database schema.
| id |  Critical     |  High  |    Low     |
--------------------------------------------
| 1  | {apple, ball} |  {cat} | {dog, egg} |

Now I want to remap these to another table. Critical set to 1, High to 2 and Low to 3 like:
| element | priority |
----------------------
|  apple  |   1      |
|  ball   |   1      |
|  cat    |   2      |
|  dog    |   3      |
|  egg    |   3      |

1,2,3 can be changed to column name too.
I've written a query to do it for 1 column at a time:
insert into new_table (element, priority)
select unnest(critical), 1 from old_table where id=1;

But I want to do all the 3 columns in this single query.
I want it fast performing. Is UNION the most efficient way?

UNION ALL, not UNION:
SELECT id, unnest(critical) AS element, 1 AS prio FROM tbl
UNION ALL SELECT id, unnest(high)     , 2         FROM tbl
UNION ALL SELECT id, unnest(low)      , 3         FROM tbl;

Optionally drop the id (like in your displayed result), but I suspect you need it.
For big tables, a LATERAL subquery may be faster as it only uses a single sequential scan:
SELECT sub.*
FROM   tbl t
CROSS  JOIN LATERAL (
      SELECT id, unnest(t.critical) AS element, 1 AS prio
   UNION ALL SELECT id, unnest(t.high)        , 2
   UNION ALL SELECT id, unnest(t.low)         , 3
   ) sub;

db<>fiddle here
Otherwise, this question seems like a subset of your later more complex question:

Unnest ids from arrays based on priority with no duplication


Use stored procedure to get minimum value from column and decrement it and other values I'm trying to understand how the following algorithm could be completed in SQL 2008 R2.

I have a table that contains production data. The table uses a "rolling" scheduling method, meaning that orders are assigned a value, a sequence number, to indicate the order that they will be completed. As they are completed, the order is removed. This is all well and good, except, you suddenly end up with the lowest value being over 5660. Additionally, there are different machines for different orders, with different order assignments for each machine.

For example, for one machine,

Order No.

1

2

3

4

5

Then, orders 1, 2, 3, and 4 are completed, and new orders are added on, so you have:

5

6

7

8

What I need is the following.

Once I am at the point where the lowest number in the table is NOT 1 (even if it is only 2), take that minimum value and subtract it from every number so that everything is lowered proportionately.
Based on our previous example, the output would be:

5 -> 1

6 -> 2

7 -> 3

8 -> 4

I understand the process that needs to happen, I am just unsure of how to translate that into SQL, considering that there are multiple machines.

I know that I could easily do this with a cursor, but I would prefer not to. Ideally, some type of nested statement would be best.

For example, some type of statement (pseudo code, not correct at all)

UPDATE xTable
SET xNumber = (xNumber - (SELECT MIN(xNumber) FROM xTable))
WHERE xMachine = xTable.xMachine (??)

Any type of insight that you could offer would be appreciated.

This example code should work, but there are some things to watch out for.
Since this is updating the tables while (presumably) another process is using that table to schedule jobs, concurrency could be an issue.

For example, I'd assume there is another key field not mentioned that the scheduler process is using to identify the jobs. If it's the xNumber/xMachine combination, then changing them could cause an issue.

    CREATE TABLE #xTable (xNumber INT , xMachine INT)

    INSERT #xTable ( xNumber, xMachine ) VALUES (5,1),(6,1),(7,1),(8,1),(9,1),(7,2),(8,2),(9,2)
    SELECT * FROM #xTable AS xt;

;
WITH    cte ( xMin, xMachine )
        AS ( SELECT   MIN(xNumber)
            ,       xMachine
            FROM        #xTable
            GROUP BY xMachine
           )
    UPDATE     #xTable
    SET        xNumber -= cte.xMin - 1
    FROM       #xTable
    INNER JOIN cte ON cte.xMachine = #xTable.xMachine;

    SELECT * FROM #xTable AS xt

    DROP TABLE #xTable;

Initial values:

xNumber xMachine
5   1
6   1
7   1
8   1
9   1
7   2
8   2
9   2

Modified Values:

xNumber xMachine
1   1
2   1
3   1
4   1
5   1
1   2
2   2
3   2


Failed to deploy the project. Try again later. (Microsoft SQL Server, Error: 27118) I try to deploy new version of existing SSIS project into Intergration Service

Catalog and getting error:

  "Failed to deploy the project. Try again later. (Microsoft SQL Server,
  Error: 27118)"

New .ispac file I have on file system (drive "D:\").
I tried:

deploy from SSMS
from command line
with Power Shall script

And I got the same error.

(SQL Server 2012)

Problem has been fixed. So I'll try to decribe solution to this problem and maybe for someone it'll be useful and save time. In the beginning we have an error message from wizard:

  "Failed to deploy the project. Try again later. (Microsoft SQL Server,
  Error: 27118)"

Let's see detailed error, to do this just need to click on "Show technical details button". In the section "Additional data" we can find object wich is responsible for this error. In my case it was procedure prepare_deploy. This is an internal store procedure of database SSISDB. To catch an error I used statement RAISERROR in deferent places, because this error appears in different places. Thus my problem was there:

IF EXISTS (SELECT [project_id] FROM [internal].[projects]
            WHERE [folder_id] = @folder_id AND [name] = @project_name)
    BEGIN
        RAISERROR(27118, 16, 1) WITH NOWAIT
        RETURN 1
    END

But to see this error you should have true in the following condition: IF(@project_id IS NULL) See whole statement which appears earlier in the store procedure:

    SET @project_id = (SELECT [project_id] FROM [catalog].[projects]
                   WHERE [folder_id] = @folder_id AND [name] = @project_name)

    IF(@project_id IS NULL)

    BEGIN
    ...

So we have almost identical SELECT statements except schema, but results are different: first statement gives us nothing although second gives some result.
The schema [catalog] says that we have a deal with view. Take a look on the view:

CREATE VIEW [catalog].[projects]
AS
SELECT     ...

FROM       [internal].[object_versions] ver INNER JOIN
           [internal].[projects] proj ON (ver.[object_id] = proj.[project_id]
           AND ver.[object_version_lsn] = proj.[object_version_lsn]) INNER JOIN
           [internal].[folders] ON proj.[folder_id] = [internal].[folders].[folder_id]
WHERE      (ver.[object_status] = 'C')
           AND (ver.[object_type]= 20)
           AND (
                  proj.[project_id] IN (SELECT [id] FROM [internal].[current_user_readable_projects])
                  OR (IS_MEMBER('ssis_admin') = 1)
                  OR (IS_SRVROLEMEMBER('sysadmin') = 1)
                )

Obviously, to see some result from this view user should have the permission either ssis_admin or sysadmin. Let's see who executes procedure internal.prepare_deploy

ALTER PROCEDURE [internal].[prepare_deploy]
    ...
WITH EXECUTE AS 'AllSchemaOwner'
AS

Finally, checking permission for user AllSchemaOwner in database SSISDB:

Bingo, we don't have appropriate permissions to see data from view [catalog].[projects]. Just adding user AllSchemaOwner to role ssis_admin and problem is being gone.


Temp DB space allocated- will it be reclaimed ever? I have observed that the tempdb in the production environment has grown over to 100 GB after initializing it.

I understand that due to server load and some expensive queries have resulted to the growth of tempdb in a span of just two days. I have been observing this over a week now and find this size did not grow further and remains the same.

I have one mdf file configured for tempdb on the server having 8 logical cores. Though I could add multiple mdf files based on the logical cores.

I observe that the free page size and the physical size of temp db data file are the same in my case

SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

Help me understand if this physical space would ever be reclaimed automatically without we explicitly executing script to shrink the space or by restarting the server to reset the temp db allocation??

Like all databases, free (unallocated) space in tempdb is reused for new objects.  No action is needed for this space to be reused.

The physical size of tempdb files will reflect the high-water mark of growth since the instance was last started.  The size will revert back to the initial file size after an instance restart, freeing space on the disk volume if growth occurred.  It is also possible to release unused file space using DBCC SHRINKFILE.  That should not be done routinely but may be desirable if the growth was an anomaly, such as due to a bad query.  It is best to size files to accommodate expected space requirements and provide growth as a safety net for unexpected needs.

Multiple tempdb data files of equal size and growth can improve performance by reducing allocation page contention for some workloads.  However, that is unrelated to space reclamation.


Transposing repeated rows of datetime into columns I am working with a dataset of inpatient data in SQL Server 2017. The fields contain anonymized ID, admission date, discharge date and other relevant fields. Since some of the patients are readmitted, the ID field is not unique. The table looks like this:

ID       AdmDate1      DischDate
10001   2012-10-16    2012-10-26
10001   2014-06-15    2014-06-18
10001   2014-12-21    2014-12-29
10002   2013-02-14    2013-02-20
10003   2013-01-23    2013-01-31
10004   2012-11-15    2012-11-19
10004   2014-09-26    2014-10-06
10005   2014-12-12    2014-12-23
10006   2013-10-23    2013-10-28

Since I want to calculate readmission rate and intervals between readmissions, I want to create a table like this:

ID       AdmDate1     AdmDate2    AdmDate3   DischDate1 DischDate2 DischDate3
10001   2012-10-16   2014-06-15  2014-12-21
10002   2013-02-14   None        None

I have counted the rows in the columns of AdmDate1 and found the
  maximum admission frequency is 35. Since readmissions more than 4 are
  not many, I can remove the rows with rn>4 for AdmDate1 and DischDate.
  by doing this, the number of columns to be created will be reduced to
  manageable numbers

Based on this reply i started looking for a solution which uses 4 of admdates and 4 of Dischdates.

So this works, but I don't dare look at the query plan

Create test data

create table AdmissionDates(id int, AdmDate1 date,  DischDate date)
insert into AdmissionDates(id,AdmDate1,DischDate)
VALUES(  10001 ,  '2012-10-16' ,  '2012-10-26'),
(10001 ,  '2014-06-15'   , '2014-06-18')  ,
(10001 ,  '2014-12-21'   , '2014-12-29')  ,
(10002 ,  '2013-02-14'   , '2013-02-20')  ,
(10003 ,  '2013-01-23'   , '2013-01-31')  ,
(10004 ,  '2012-11-15'   , '2012-11-19')  ,
(10004 ,  '2014-09-26'   , '2014-10-06')  ,
(10005 ,  '2014-12-12'   , '2014-12-23')  ,
(10006 ,  '2013-10-23'   , '2013-10-28')

select id,row_number() over( partition by id order by admDate1 asc) as rownum, AdmDate1,row_number() over( partition by id order by DischDate asc) as rownum2, DischDate
into #temp
from AdmissionDates;

The Query

select DISTINCT id,
                t2.admDate1 as admDate1,
                t3.admDate1 as admDate2,
                t4.admDate1 as admDate3,
                t5.admDate1 as admDate4,
                T6.DischDate as DischDate1,
                T7.DischDate as DischDate2,
                T8.DischDate as DischDate3,
                T9.DischDate as DischDate4
FROM
#temp t1
OUTER APPLY
(select admDate1 from #temp T2 where rownum = 1 and T2.id = T1.id) as T2
OUTER APPLY
(select admDate1 from #temp T2 where rownum = 2 and T2.id = T1.id) as T3
OUTER APPLY
(select admDate1 from #temp T2 where rownum = 3 and T2.id = T1.id) as T4
OUTER APPLY
(select admDate1 from #temp T2 where rownum = 4 and T2.id = T1.id) as T5
OUTER APPLY
(select DischDate from #temp T2 where rownum2 = 1 and T2.id = T1.id) as T6
OUTER APPLY
(select DischDate from #temp T2 where rownum2 = 2 and T2.id = T1.id) as T7
OUTER APPLY
(select DischDate from #temp T2 where rownum2 = 3 and T2.id = T1.id) as T8
OUTER APPLY
(select DischDate from #temp T2 where rownum2 = 4 and T2.id = T1.id) as T9;

DROP TABLE #temp;


Transfer data from MS SQL to PostgreSQL with SSIS ODBC destination I try to sync data from MS SQL to postgres with SSIS. I configure ODBC destination using psqlodbc_12_01 driver downloaded from here

My SSIS package has a single data flow task which just gets data from a single MS SQL table and transfer it to Postgres. Here is MS SQL table

CREATE TABLE [dbo].[DailyGgrNgr](
    [Date] [DATETIME] NOT NULL,
    [PlayerId] [INT] NOT NULL,
    [GameId] [INT] NOT NULL,
    [GGR] [DECIMAL](18, 4) NOT NULL,
    [NGR] [DECIMAL](18, 4) NOT NULL,
    [BetCount] [INT] NOT NULL,
    [WinCount] [INT] NOT NULL,
    [BetAmount] [DECIMAL](18, 4) NOT NULL,
    [WinAmount] [DECIMAL](18, 4) NOT NULL
)

And this the PostgreSQl table

CREATE TABLE public."DailyGgrNgr" (
"Date" timestamp NOT NULL,
"PlayerId" int4 NOT NULL,
"GameId" int4 NOT NULL,
"GGR" numeric(18,4) NOT NULL,
"NGR" numeric(18,4) NOT NULL,
"BetCount" int4 NOT NULL,
"WinCount" int4 NOT NULL,
"BetAmount" numeric(18,4) NOT NULL,
"WinAmount" numeric(18,4) NOT NULL

);

The problem is that data sync is done row by row, instead of bulk as expected. I understand this by profiling my postgres server and there was only a single insert like this

INSERT INTO "public"."DailyGgrNgr" ( "Date","PlayerId","GameId","GGR","NGR","BetCount","WinCount","BetAmount","WinAmount") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)

SSIS Destination is configured to process rows in batches as you can see from the schreenshot below

However it still inserts data row by row. Below you can see my ODBC Data Source configs.

I believe the problem is with driver because when I use other commercial drivers they works fine. Also according to Microsoft  - "If you select Batch and the provider does not support this method, the ODBC destination automatically switches to the Row-by-row mode."

I wonder if there is a possibility to configure the driver to process the rows in batches instead of row by row or is there another free ODBC driver for postgres that allows batch processing.

My Postgres version is PostgreSQL 12.2, SQL Server 2016.

Isn't this a case for a tools rethink ?
How about BCP from SQL Server to write out a csv text file & Bulk Load on the PostgresSQL side to do the load ?


SQL Server 2016 Primary and Backup We have a new production box with two SQL servers,SQL Server 2016 Standard edition.
The current plan is to have:

One primary SQL server
Backup SQL server:only SQL Report server,which points to primary server.

Can you please suggest any good replication strategy to move data from primary to back up server and which does not use Single User mode.
Realtime replication is expected.

In primary server lots of insert/update happens and will be online 24/7.
New into Replication.

Only one database in Primary SQL server,have to replicate the same db tables/views/triggers etc  into Backup server.

Have done some research on replication(Snapshot,Transaction,Merge),but i am not sure about the Single User mode thing in this context.

AlwaysOn Active Secondaries should provide the capabilities you're looking for, including virtually realtime replication of data, and reporting capability on the secondary.

  The Always On availability groups active secondary capabilities include support for read-only access to one or more secondary replicas (readable secondary replicas). A readable secondary replica can be in either synchronous-commit availability mode, or asynchronous-commit availability mode. A readable secondary replica allows read-only access to all its secondary databases. However, readable secondary databases are not set to read-only. They are dynamic. A given secondary database changes as changes on the corresponding primary database are applied to the secondary database. For a typical secondary replica, the data, including durable memory optimized tables, in the secondary databases is in near real time. Furthermore, full-text indexes are synchronized with the secondary databases. In many circumstances, data latency between a primary database and the corresponding secondary database is only a few seconds.

This technology also provides the ability to failover to the secondary in the event of a hardware or software failure on the primary, with minimal or no data loss.

Transactional replication won't prevent access to the data at the subscriber.  Transactional Replication offers a huge amount of customization and requires quite a lot of configuration and management, whereas AlwaysOn provides a simplified way of making an entire database or set of databases highly available.


How to choose suitable naming convention for Dimensional Table Column in a Data Mart? I have done a lot of reading on naming columns for use in a data mart / data warehouse, as well as for general programming usage.

For example, if I had a business branch in a normal application I would have

Table BRANCH
------------
ID
Code
Name
RegionID

Now in the star schema data mart, I want to flatten the region information into this table.
Which would be more appropriate and user friendly as a naming choice?

Table Branch_Dim
------------
BranchID
BranchCode
BranchName
RegionName
RegionShortName

OR

Table Branch_Dim
------------
ID
Code
Name
RegionName
RegionShortName

I just completed a 4 day course with the Kimball group and they had this to say on naming conventions:

Always use full descriptive names ie. Brand Name. and don't use abbreviations. Otherwise you may need to spend time training users.
Don't use Dim or Fact in your table names because you end up spending a lot of time deleting these from the reporting layer.
Don't use camel case or underscores, use spaces. Yeah I thought this was bizarre, but again they said you'll spend a lot of time adding in space at the reporting layer and most databases now handle spaces in column names without a problem.

If you subscribe to the Kimball methodology, then this is their approach. But like the other commenter said, it's all about comprehension. So if it makes sense for the business users, then you're on the right track.


Table with more than 100 columns I have a table in my MySQL database which contains data about XRF analyses.

There are about 50 chemical elements and for each element there are two values to be stored (Area and Count). Users need to perform queries on a specific element. The idea is that when they upload a new record, they provide a csv file and the fields are automatically filled.

My question is: is it ok to have a table with more than 100 columns? Is there a smarter way to implement this?

I would suggest using two tables, one to store the records uploaded and make an id for it, and another to hold the elements for the record.data types are just for illustration purpose only

create table UploadedRecords
(
    id int not null auto_increament,
    uploadtime datetime not null,
    .
    .
    primary key (uploaded_record_id)
);

create table RecordElements
(
    uploaded_record_id int not null,
    element_symbol varchar(2) not null,
    area varchar(20) not null,
    cnt int not null,
    primary key
        (uploaded_record_id, element_symbol),
    foreign key (uploaded_record_id)
        references UploadedRecords (uploaded_record_id)
);