texts: Tools to migrate from SQLite to PostgreSQL

texts: I need to continuously migrate from SQLite to PostgreSQL. By continuously, I mean that I will reimport the SQLite database into PostgreSQL every day. It would be nice if I could make changes to the tables in SQLite as I please without having to manually make any changes to the PostgreSQL database. I will run the migration from Python, but it could be any external tool, which can be controlled from command line. The tool needs to be available for Linux, and it would be nice if it also ran on Windows.


texts: Does the PCI standard forbid the use of Windows Authentication?

texts:
We currently use Mixed Mode for our SQL Server Authentication. I've tried to convince our DBA to allow us to use Windows Authentication so we can use Team Foundation Server, however he absolutely refuses to allow us to have it.

Accord to him, we cannot have Windows Authentication since we are planning to become PCI compliant eventually and PCI requires Mixed Mode.  From what I see online, it's the opposite: The PCI standard actually prefers Windows Authentication over Mixed Mode.

Can someone give me some more information about this (preferably a URL  that states the correct information) so I can direct it to our department head?


texts:
Here is some made-up data as an example:

Category
Sub-Category
COUNT

Furniture
Chairs
25

Furniture
Null
15

Technology
Null
10

Technology
Phones
35

What I'm trying to do is... Keeping CATEGORY fixed, choose the non-null subcategory.
so this would be what would happen:

Category
Sub-Category
COUNT

Furniture
Chairs
25

Furniture
Chairs
15

Technology
Phones
10

Technology
Phones
35

and this would be the output:

Category
Sub-Category
COUNT

Furniture
Chairs
40

Technology
Phones
45

I figured out how to do this within Tableau, but I need to figure out how to do this in SQL.
Here is the solution inside Tableau:
IFNULL ([Sub-Category],
{FIXED [Category] : MAX([Sub-Category]) } )

Btw:  there is much more to why I'm doing this, and right now it looks like "why would she want to do this?", but there is a reason that makes sense, but can't explain because I'm not allowed to show data from my job because it is confidential. The output would be a result of combining the like terms together.
Thank you!


texts: Get memory usage of mysql query during runtime

texts:
Is possible to obtain used memory in a running query?

Something like:

mysql> SELECT MEMORY_PEAK as memory; #return memory usage by mysql before run query
mysql> SELECT id FROM table WHERE id>2; #run query
mysql> SELECT MEMORY_PEAK as memory; #return memory usage by mysql after run query

and return something like (return 17kb):

+----------+
|  memory  |
+----------+
|  20100   |
+----------+

+------+
|  id  |
+------+
|  3   |
+------+

+----------+
|  memory  |
+----------+
|  2026767 |
+----------+

or

mysql> USAGE SELECT id FROM table WHERE id>2;

and return something like (return 17kb):

+---------+
|  USAGE  |
+---------+
|  16767  |
+---------+

Is this possible?

I need it to do various tests and see which engines queries and performs better (personal experience).


texts: Can't connect to the DB2 database on localhost, using DBeaver 3.1.1

texts:
Solved: I was connecting to the wrong port, according to /etc/services | grep db2

I have a DB2 Express C v.10.5 database running on localhost, but simply I can't connect to it.

Translation of the error message below:
"java.net.ConnectException: An error occured when opening the socket to the server ... on port 50000 with message: Connection refused"

Driver properties:

I am using DBeaver 3.1.1, Ubuntu 14.04 LTS 64 bit.

Can somebody please help?  :|


texts:
We lost our database administrator over a year ago - and even then she was promoted into the job internally and sort of learned on the job. Basically I say this to establish that strong data standards have not been enforced for a long time. We have a company that set up and administers our database - but they are particularly unhelpful and different people give different answers so I am really just looking for some best practices and maybe some key things that I should be asking for so that I get their attention and we are on the same page.

Our biggest problem is, in my opinion, that we have many many duplicate records. People make new records when they donate, they make new records when they sign up for email newsletters, they make new records when they sign up on our new app...etc etc. This causes problems down the line because then our website returns errors when it finds multiple profiles with the same email and won't let people buy things/register etc. - and we waste money mailing to the same people several times. We really need to tackle this problem - especially for our certified tree farm members because they are getting tired of getting multiple emails and mailings plus we need an accurate count for real programmatic reasons. I know I'm asking a big question - but how SHOULD these things be set up? I know there must be a better way because other websites seem capable of handling these issues fine. For instance - if we have a husband and wife - should they each have their own profile or share one? What if they share an email address (our population is a bit older so many do) right now we can create a 'shared email' relationship - is that a real thing or is there another way to do this?

I know it would be impossible to give a really specific answer - I'm just looking for a gold standard of functionality so I know what we should be aiming for and what we should be able to do. I don't really trust this company because they tell us things are impossible, but I see them working well all around me.

Thank you for any advice!


texts: MySql global variable disconnect_on_expired_password

texts:
This is very annoying....
disconnect_on_expired_password is set to ON.
MySql version is:

Server: Localhost via UNIX socket
Server type: MySQL
Server version: 5.7.5-m15 - MySQL Community Server (GPL)

I need to understand which of my MySql users is causing this error: [Note] Your password has expired. To log in you must change it using a client that supports expired passwords.

I tried setting default password lifetime to 0 so:
SET GLOBAL default_password_lifetime = 0; according to
MySQL password expired, and default_password_lifetime now shows 360 and still I get the error.

I tried setting disconnect_on_expired_password but with no luck.
SELECT @@GLOBAL.disconnect_on_expired_password;
Gives:

+-----------------------------------------+
| @@GLOBAL.disconnect_on_expired_password |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+

So I tried:
SET GLOBAL disconnect_on_expired_password=0;
ERROR 1238 (HY000): Variable 'disconnect_on_expired_password' is a read only variable

My questions are:

how can I know which of my MySql users is causing the error mentioned above?
how can I change disconnect_on_expired_password if @all??

Thanx


texts: How do I show zero when I use count by using self left join?

texts:
I am trying to use count() to count the number of the module where the particular student got the average mark below 40.  It also needs to show the number 0 if the student does not have any assessment mark lower than 40.  However, my query does not show any 0 number at all.
CREATE TABLE assessment (
assessmentId text,
mark integer,
studentId text,
moduleId text,
PRIMARY KEY (assessmentId, studentId, moduleId),
FOREIGN KEY(studentId) REFERENCES student(studentId),
FOREIGN KEY(moduleId) REFERENCES module(moduleId)) ";

in each record, it shows the module that the assessment is from, and show which student took the assessment.
this is my query
 "select count(m.moduleId), avg(e.mark), m.studentId
from assessment e
left join assessment m on m.assessmentId = e.assessmentId
and m.studentId = e.studentId
and m.moduleId = e.moduleId
group by m.studentId
having avg(e.mark)<40";

the module could have more than one assessment, therefore the avg(e.mark) is to find out the average mark for each module for each student.  Therefore it is group by studentId
so what i want the query to show is:

studentId|avg(e.mark)|count(m.moduleId)
1        | 50        | 0
2        | 20        | 2

However I am getting the result below

studentId|avg(e.mark)|count(m.moduleId)
1        | 20        | 2

I really don’t know where the problem is


texts: Can Redshift process CTE / with chains without making interim tables?

texts:
If you are careful about structuring a CTE chain, is Redshift capable of analyzing it and determining that interim results are being used in a linear fashion? If so, it could drop any interim tables generated by the CTEs in the chain. Obviously, you would have to be very careful not to use fancy stored functions, write your query in a very certain pattern, etc. This exercise is no different from the classic "explain until it's optimal" methodology that one has to perform, ANYWAY.
In the past, I know it most definitely DID NOT do that, but a recent explain on a chain led me to believe that. I am sorry, I
Furthermore, I strenuously believe that Redshift should be doing this in the first place. (In other words, if not, WHY NOT?!?!?!) If you're doing this on several billion rows, the benefit is quite obvious.
Example:
I have a fairly long CTE chain that is of the form:
WITH join_tables as (
     select a.column1 ... b.column8 ... c.column15 ...
     from a, b, c
     where a.column1 = b.column1
       and a.column1 = c.column1
),
add_new_column as (
   select *,
   column1 + column2 as new_column
   from join_tables
   where column4 in ('val1', 'val2')
),
add_another_column as (
   select *,
   case where column8 = 'foo' then column19 - column6
        else new_column / column14
        end as another_column
   from add_new_column
   where new_column < column12 + column16
)
select column1, column6, column4+column2
from add_another_column
where another_new_column > 52
;

As you can see, I'm being sooooper careful about not referencing any data before the CTE immediately above. I am also avoiding groupings, sums, etc. In just about any procedural programming language, you could implement this as a generator chain.
It would seem totally possible for Redshift to do this too, perform a static analyzer to optimize away any and all intermediate tables. And for this fairly common Redshift use case, I can totally see AWS putting some time into doing this.
If Amazon can figure out which toothpaste I like before I do, then they can figure this out.
Here is the explain, with results sanitized. You will notice that there are no the interim CTEs at all, including any tables they might have generated (which I THINK explains used to show)
XN Subquery Scan join_tables  (...)
    ->  XN Hash Join DS_DIST_NONE  (...)
          Hash Cond: ("outer".id = "inner".id)
          ->  XN Seq Scan on a  ()
          ->  XN Hash  ()
                ->  XN Hash Join DS_DIST_NONE  (...)
                      Hash Cond: ("outer".id = "inner".id)
                      ->  XN Seq Scan on b  (...)
                      ->  XN Hash  (...)
                            ->  XN Seq Scan on c  (...)


texts: I'm confused about tranasaction logs for SQL Server. I was under the impression they can be used to roll the database forward. For example say I made a software release on Sunday, I added three new columns. But four days down the line we discover a bug, could I roll the database back to the Sunday's backup before I did the release and then use the transaction logs to insert any data that's been inserted since Sunday?


texts:
When there are nested impersonations (as in exec as user = 'user1'; exec as user = 'user2'; exec as user = 'user1';), is there a way to revert all of them without having to use the revert command multiple times (e.g. revert all;)?

Or at least, if one must use revert repeatedly, is there a way to check if there is an active impersonation so one could loop the command as many times as needed (e.g. while @@IMPERSONATION_COUNT > 0 revert;)?


texts: Which columns need index in recursive table and soft delete

texts:
I have a Category table like below :

I have a recursive relationship on this Category table. I use soft delete in my project. I have 2000 rows in Category table. When I want to show data in a UI dropdown it takes about 3 minutes.

Which columns need an index to improve query speed?

Table definition:

 CREATE TABLE [dbo].[Category](
    [CategoryId] [uniqueidentifier] NOT NULL,
    [CategoryCode] [int] IDENTITY(1,1) NOT NULL,
    [CategoryName] [nvarchar](400) NOT NULL,
    [ParentId] [uniqueidentifier] NULL,
    [DisplayOrder] [int] NOT NULL,
    [Description] [nvarchar](max) NULL,
    [IsActive] [bit] NOT NULL,
    [IsShowOnMenu] [bit] NOT NULL,
    [AttachmentId] [uniqueidentifier] NULL,
    [Depth] [int] NOT NULL,
    [Path] [nvarchar](max) NULL,
    [IsDeleted] [bit] NOT NULL,

 CONSTRAINT [PK_dbo.Category] PRIMARY KEY CLUSTERED
(
    [CategoryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)

My query is in C#. I used C# statment to generate show all category in dropdown.
My query is:

exec sp_executesql N'SELECT
[Project1].[DisplayOrder] AS [DisplayOrder],
[Project1].[CategoryId] AS [CategoryId],
[Project1].[CategoryName] AS [CategoryName]
FROM ( SELECT
    [Extent1].[CategoryId] AS [CategoryId],
    [Extent1].[CategoryName] AS [CategoryName],
    [Extent1].[DisplayOrder] AS [DisplayOrder]
    FROM [dbo].[Category] AS [Extent1]
    WHERE (([Extent1].[IsDeleted] = @DynamicFilterParam_1) OR (@DynamicFilterParam_2 IS NOT NULL)) AND ([Extent1].[ParentId] = @p__linq__0)
)  AS [Project1]
ORDER BY [Project1].[DisplayOrder] ASC',N'@DynamicFilterParam_1 bit,@DynamicFilterParam_2 bit,@p__linq__0 uniqueidentifier',@DynamicFilterParam_1=0,@DynamicFilterParam_2=NULL,@p__linq__0='8d8dd739-a132-e3ef-5443-159ecf8adc44'

Above query runs for each item, because I need to get all children.


texts: ora 20001 error in oracle dbms scheduler

texts:
i have written a test job like

begin
DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'test_job'
,job_type =>'plsql_block'
,job_action => 'null'
,enabled => true
);
end;
/

after a while when the check the status

select * from user_scheduler_job_run_details where job_name='TEST_JOB';

the status is failed ,i see these two lines

ORA-20001: You are not allowed to logon from this terminal!!!
ORA-06512: at line 39

how can i resolve this error.
and
how to find the job id of a scheduled job(created using dbms_scheduler)


texts: How to estimate REDO phase time during RESTORE

texts: I am restoring database now and get it hang on 100% but still going (doing redo phase). I am thinking how to estimate time of completion for this phase. But because SQL have no build-in way to do it, I suppose is it possible to count all modified by commited transactions pages from transaction log, multiple it by 8 to get exact space needed to be modified during the redo phase in data file, and estimate completion time by dividing it on your disk subsystem throughtput?


texts: Want to perform a function on a returned table on subset of rows

texts:
I have a query that takes 10 minutes to run (1.4 million rows and 15-ish joins), so I think the time isn't terrible.

What I need from this is to effectively INNER JOIN on itself an additional column based on a key of 'n' other columns. This key would be selecting 3 specific rows and performing a mathematical function to produce one result. This result would be joined onto the 3 rows in the final column:

I know I could (and have with smaller queries) just run the query again with a group by and join it on to the original, but this (I believe) would double the query time.

Is there a standard way to do this that I am just not aware of?

Completely stumped by this one, and have had to resort to asking a question on here (after searching). Bear with me as I am self taught!


texts:
We've had to move around the master a bit. It started on server 01 got moved to 02 (which was a slave) We need to move it again so we built 04 and trying to slave it off 02 and getting the following errors.

2018-02-25 17:00:08 UTC FATAL:  highest timeline 3 of the primary is behind recovery timeline 4
2018-02-25 17:00:13 UTC FATAL:  highest timeline 3 of the primary is behind recovery timeline 4
2018-02-25 17:00:18 UTC FATAL:  highest timeline 3 of the primary is behind recovery timeline 4

The initial dump happened like

pg_basebackup --verbose --progress -d "host=10.132.x.x user=backup password=...." -D /var/lib/postgresql/9.4/main/ -l 'instance restore' --xlog-method=stream

recovery file looks like

restore_command = 'if [ -f /srv/postgresql/archive/${DATASET}/%f ]; then cp /srv/postgresql/archive/${DATASET}/%f %p; else aws s3 cp --quiet s3://company-backups/postgresql/${DATASET}/archive/%f %p; fi'
standby_mode = 'on'
primary_conninfo = 'host=10.132.x.x user=backup password=....'
recovery_target_timeline = 'latest'
trigger_file = '/var/lib/postgresql/9.4/main/failover'


texts:
What should one do when one wishes to replace a large object with a new large object. An example would be to update an uploaded file with a new version.

One way would presumably be to create a new large object, update links to the new oid, and then unlink the old object. But is it reasonable to instead replace the existing data, say, by writing over the original (perhaps truncating the contents first)?

FWIW I'd be doing this in JDBC and for PostgreSQL 9.4.


texts:
I'm new to Azure and now trying to migrate the On-premises SQL databases on azure.
I have chosen VM SQL for migration because I just want to lift and shift architecture and need to restore backup file but as I mentioned above I'm new to Azure, So I just want to check with you guys whatever I have choose is it good choice? Which Azure SQL database is good in performance? (azure managed instance or VM SQL) Is VM SQL more costly as compere to other instances ?
Thanks.


texts: MariaDB Galera stop working without network connectivity

texts:
I am running MariaDB Galera cluster whose using mariaDB and 3 nodes(node A, node B, node C). My nodes are placed locally in our office.

My problem is if node A/B/C lost network connection, it also lost it's working ability.

Say a application is connected to node A, then node A lost network connectivity, so when someone tries to read/write data on node A using physical access,say CLI, it doesn't work. It's showing,
 ERROR 1047 WSREP has not yet prepared node for application use

I want to use node A/B/C offline as well. When they get back the network connection, it will automatically replicate offline data to the connected nodes.

Thanks in advance,

Nixon


texts:
I'm new on Oracle and I'm trying to create a new database. I followed tutorials online but the problem seems to be in Windows and not in Oracle.
I'm using the Oracle assistant to build my Database but when I arrive at the last stage and the assistant begin to configure all, there is an error (image attached).

From the message, seems that my user haven't the permission to read, write and execute anything from documents but it is not true. I gave full permission to my user and so I don't know why it stuck again here.

My system configuration is:
 - Oracle 11g ver. 2
 - Windows 7 Pro (64bit)


texts: How do I adopt the PostgreSQL naming convention in legacy database?

texts: I realize I'll need to update the queries, but if I have a database that has camelCase or spaces in it, you have to use double quotes on the identifiers (viz. schema, table, column). How do I migrate away from taking into account that I can't have capital letters, nor spaces in my identifiers. I need to normalize all of them to snake_case.


texts: Keep original column names in SSRS report designer 3.0?

texts:
I have a query that returns over a hundred columns. The column names contain important underscores, >, < and Uppercase/lowercase characters. However, when creating the reports, SSRS designer (3.0) automatically added spaces and convert these special characters to spaces. It's really annoying. Is it possible to disable this SSRS feature?

How much work would it be to fix the headers using the following two methods?

Use a script to update the column header back to the original column name.
Transform the XML reporting rdl file.


texts: Restoring corrupt SQL Server 2005 database

texts:
I am restoring a SQL Server 2005 database onto a SQL Server 2008 R2 instance using this command:

RESTORE DATABASE SampleDB FROM DISK= 'C:\SampleProject\SampleDB.bak' WITH REPLACE,
MOVE 'sampledb' TO
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SampleDB.mdf',
MOVE 'sampledb_log'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SampleDB_log.ldf',
CONTINUE_AFTER_ERROR, STATS = 10;

And the output is:

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Msg 3242, Level 16, State 2, Line 3
The file on device 'C:\SampleProject\SampleDB.bak' is not a valid Microsoft Tape Format backup set.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

Why does the processed percent reach 100% but then terminate abnormally?

How can I restore this database backup even with corrupt data? Continue-after-error does not appear to make any difference.


texts: I want to use hstore in my postgresql server running on CentOS 7 but hstore.control is not in my postgresql extension folder. I searched for that and came up with the solution to install postgresql93-contrib for postgresql version 9.3. But when I want to install the package, there is no package available for that. How can I solve this issue?


texts:
I have a problem creating the table. It says unexpected parenthesis at line 3

CREATE DATABASE scl_teachers;

USE scl_teachers;

CREATE TABLE clteacher (
    'id' INT (10) PRIMARY KEY auto_increment,
    'f_name' VARCHAR(255) NOT NULL,
    'mid_initials' (255) IS NULL,
    'l_name' VARCHAR(255) NOT NULL,
    'saturation' VARCHAR(2)
);


texts: UPDATE SET REPLACE() matches but does not change

texts:
I've seen another post about this exact thing but it's 1-1/2 years old so I thought I'd make my own...besides, the other post did not help me.  What I am trying to do is pretty straight-forward.

To be on the safe side, I tried a simple UPDATE statement on a different table, which was successful.  So, here's my problem:

mysql> UPDATE bugs_fulltext SET short_desc =
REPLACE(short_desc,'I don\'t know why my previous comment means.',
'I don\'t know what my previous comment means.') WHERE bug_id=452;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

I'm not using wildcards nor am I using a LIKE statement.  I am telling it which record to update.  Why isn't my text changed?


texts: remote oracle connection from AIX to Linux Redhat

texts:
I want to connect oracle database from AIX to Linux using user id and password only.  For that I have changed
tnsnames.ora

RATARA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = XX.XX.XX.XX)(PORT = 1521))
(CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = RATARA)
)

listener.ora

LISTENER =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = XX.XX.XX.XX)(PORT = 1521))
  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)

Above code, I have done in my AIX machine.  In the above change I have used IP address in place of HOST for both listener.ora and tnsnames.ora and PORT no has used for remote machine.
I have updated my .profile also in AIX machine:
export ORACLE_SID=RATARA

I have stop and started listener on AIX.  After that not able connect using @RATARA
RATARA oracle sid name is from remote(Linux) machine which I have set in my AIX.
I am able to connect using sqlplus <user_id>/@oracle_sid but I want to connect without using @oracle_sid since my entire code project uses without @oracle_sid.  Means I want to connect only sqlplus userid/pwd
Kindly provide me solution to connect using only user id and pwd for remote connection if it was missed anything for setting


texts: SQL, sum one column where another column is the same and display as the third column

texts:
I have the following data:

a  b
1  23
1  14
1  3
2  45
2  16

and what I would like is to sum column b where column a is the same and have this displayed in a third column, like so:

a  b  c
1  23 40
1  14 40
1  3  40
2  45 61
2  16 61

Thanks.


texts: create multi valued variable inside mysql stored procedure

texts:
I have two IN variables into my MYSQL stored procedure. I pass one into a select statement and the value is taken in a temporary variable which is passed to the second SQL statement.

CREATE PROCEDURE `usp_mystoredproc`(IN sText varchar(25), IN myId int(21))
begin

set @myCid= ( SELECT tabId AS id
FROM            table1
WHERE           IFNULL(tabId , '') != '' and table1_id= myId);

SELECT  searchItem
FROM    table2
WHERE  searchItem LIKE CONCAT(sText , '%') and table2_id =  @myCid
LIMIT                   15 ;
END

Now the issue is, in the above stored procedure, @myCid gets multiple values and it fails.
I need to get all those ids and pass them to the second SQL. How do I handle this?


texts:
Across my problems with the load on the database, I just have found a new, odd one. I understand, that some queries might be in IDLE IN TRANSACTION status, when I did something wrong in my application layer. However, just now I have spotted this query having this status for almost 20 seconds now:

SELECT 1;

This is the internal mechanism of JBoss AS, checking if the connection is still valid. I cannot understand how it be idle in transaction. Any hints welcome.


texts: Database SQL Script very, very slow (string manipulation over 700k rows)

texts:
I have table containing 700,000 rows containing a string id field with values such as rec-232276-dup-0 and rec-354240-org. The rec- is constant but the other parts of the id can change.

I want to split this string up so that I have just the integer part and a boolean value depending if the next part is equal to dup or org (1 for dup, 0 for org) which I will insert back into the table.

I wrote the following loop to do this and functionally it works fine but when I run it over the full 700,000 rows it takes an inordinately long time (+12 hours and counting).

What have I done wrong to cause it to take this amount of time? Is it the string manipulation that causes this? What can I do to improve this?

Thanks for your help.

My script is as follows:

select id
into #ControlTable
from [dbo].[original_test_dataset]

declare @TableID varchar(20)

while exists (select * from #ControlTable)
begin

   select @TableID = (select top 1 id
                    from #ControlTable
                    order by id asc)

   declare @duplicate bit
   declare @id_only varchar(10)

   --1. Find id only
   -- Trim off rec-
   set @id_only = REPLACE(@TableID,'rec-','')
   -- Find position of first - and then take everything before it
   set @id_only = LEFT(@id_only,CHARINDEX('-',@id_only,0))
   set @id_only = REPLACE(@id_only,'-','')
   UPDATE original_test_dataset set id_only = @id_only WHERE id = @TableID;

   --2. Find if duplicate
   IF(PATINDEX('%dup%',@TableID) = 0)
        BEGIN
           -- No duplicate so original file
           UPDATE original_test_dataset set duplicate = 0 WHERE id = @TableID;
        END
   ELSE
       BEGIN
           -- Duplicate
           UPDATE original_test_dataset set duplicate = 1 WHERE id = @TableID;
       END

   delete #ControlTable
   where id = @TableID
END

drop table #ControlTable

Added table definition as requested:

CREATE TABLE [dbo].[original_test_dataset](
    [id] [varchar](50) NULL,
    [ FirstName] [varchar](50) NULL,
    [ LastName] [varchar](50) NULL,
    [ Phone1] [varchar](50) NULL,
    [ Phone2] [varchar](50) NULL,
    [ Phone3] [varchar](50) NULL,
    [ No] [varchar](50) NULL,
    [ Road] [varchar](50) NULL,
    [ Village] [varchar](50) NULL,
    [ Town] [varchar](50) NULL,
    [ PC] [varchar](50) NULL,
    [ County] [varchar](50) NULL,
    [ DOB] [varchar](50) NULL,
    [id_only] [varchar](10) NULL,
    [duplicate] [bit] NULL
) ON [PRIMARY]

GO

/****** Object:  Index [PK_ORIGINAL_TEST_DATASET_ID]    Script Date: 03/09/2014  07:47:19 ******/
CREATE CLUSTERED INDEX [PK_ORIGINAL_TEST_DATASET_ID] ON [dbo].[original_test_dataset]
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON   [PRIMARY]
GO


texts:
I just uninstall SQL Server 2012 (but Microsoft SQL Server 2012 didn't uninstall completely), now I want to install SQL Server 2016 but it causes this error:

  vs shell installation has failed with exit code 1638.

I google it but can't find a true answer.


texts:
For the past few days, I am getting this error every morning when I tried to connect to the MySQL database using the MySQL workbench and also through some applications. So, every day I log onto the server and run the command FLUSH HOSTS. Then I and some machines can connect to the database and do the daily duty. The error is as follows,

  Error Code: 1129 Host 'xxx.xxx.xx.xxx' is blocked because of many
  connection errors; unblock with 'mysqladmin flush-hosts'

The MySQL databases are in a server that running a Linux operating system (CentOS). Among other databases, we have a database that is using frequently.

Database tables are as follows,

|table 1     |     4000 records|
|table 2     |     2000 records|
|table 3     |     1500 records|
|table 4     |    35000 records|

So, the database and other tables are not working until I run the command FLUSH HOSTS And also I am facing some slowness in table 4. When I run SELECT * FROM table 4 query in workbench sometimes it throws this error,

  Error Code: 2013. Lost connection to MySQL server during query.

So I have increased the maximum connections, net_read_timeout and connect_timeout values.
table 4 has 35000 records around 5 MB. But I don't think the number of records is not the issue. Some of our mobile applications also throw time out errors because of this database issue.

And also I see

  waiting for table level lock

in each UPDATE, INSERT and SELECT queries when I run the command SHOW PROCESSLIST. And at that time table 4 is really slow. Table Engine is MyISAM. Does that make any sense?

Note:- I have seen many questions with answers similar to this question. But they didn't help me. So before flag this as duplicate, please read the whole question.

So anybody can help me? I am really grateful for any help.

Thanks in advance.


texts: SQL Server transactional replication - behaviour of a MERGE command on the subscriber(s) vs. publisher

texts:
Background

I have two instances of SQL server running on two different machines:

One is a publisher, the other is a subscriber for a single (large) database. The replication type is transactional.

They both start off with the same snapshot of my replicated database.

I have a utility which runs on the publisher machine which executes the SQL commands against it (all are inserts, updates, deletes). These changes are then pushed out to the subscriber.

I want to know if I am able to TRUNCATE the data in the publisher database so that it has a lower overhead (all I want is the replication ability, I couldn't care less about the actual data in my replication publisher as long as it makes it to the subscribers).

If I do the above and also modify my utility which executes commands to turn INSERTS and UPDATES into a MERGE command, will the data be properly replicated to all subscribers?

Example scenario:

The original command that my utility will execute:

  UPDATE calendar SET active = 0 WHERE id = 1932

The publisher database doesn't have a calendar record with id of 1932, because I truncated it, so an update will affect zero records.
But the subscriber database DOES have a calendar record with id 1932.
If my utility translates the UPDATE above into a MERGE, will the change be pushed out to the subscriber database even though it executes as an INSERT on the publisher, but an UPDATE on the subscriber?

Thanks in advance.


texts: Table structure for validating data using category and subcategory fields

texts:
I am creating a simple budget application, and the primary table will be a transaction table that contains things like the amount of the transaction, date, account, the category and subcategory (the type of transaction it was).

A spending category might be "Entertainment" or "Food" or "Household" and each would be further subdivided into subcategories.  The "Food" category might have "Work Lunch", "Grocery Store", "Fast Food", etc. subcategories, for example.

One way to do this is to have a table Categories:

    id    category     subcategory
----------------------------------
     1        Food      Work Lunch
     2        Food       Fast Food
     3        Food   Grocery Store
     4   Household        Mortgage
     5   Household         Repairs

and so on.  I then reference this id field in my table Transaction, using type as a foreign key:

id    txdate  amount   type   account   ...
-------------------------------------------
 1   6/25/15  15.25       2      cash   ... (a fast food expense)

The problem with this is that I'd like to be able to allow for null subcategories, and that doesn't seem to fit that well with this model.

Another way would be to just put the category and subcategory into the main Transaction table:

id    txdate  amount   category   subcategory   account   ...
--------------------------------------------------------------
 1   6/25/15   15.25       Food     Fast Food      cash                     ...

I have considered using CHECK CONSTRAINTs but this gets too bulky and messy too quickly, and does not allow me an easy way to access the allowable fields from my front-end code.  I will be validating data in the front end but I would like a robust way to ensure data validation on the actual database update as well, so I thought it would be convenient to store the values in a table (or more than one table), and then retrieve these allowable values in the code.

Any solution will work, but this is such a basic thing that I'm sure some best practices exist for this, and I'd like to query you all to find out what those would be.

Thank you for your time and expertise!


texts: du of directories in a diskgroup in Oracle ASM

texts:
In Linux, you could run the below on a directory to find the size of each sub-directory:

du -sh *

When you're in asmcmdin the +FRA disk group for example, how can I retrieve the size of each directory in the disk group in one list, instead of running du +FRA/directory on each and every single directory?


texts:
I'm trying to run some reporting queries on a MySQL database, and I'm running into serious performance issues for some reason. One table, page_views, is extremely large (~70 million records), so some slowness is to be expected, but it's got lots of indexing, and all the queries are restricted to a certain date range on the started_at field. I'm seeing simple joins taking upwards of ten minutes. The query I've been tossing around lately (which I've stripped way down) looks like this:

select count(page_views.id) as views
from playpack_media
join page_views on page_views.itemId=playpack_media.media_Id
where playpack_media.playpack_id = 1
    and page_views.started_at BETWEEN '2014-06-23' and '2014-07-07'

It's taking about 11 minutes 30 seconds to run. DESCRIBE returns this analysis:

| id | select_type | table          | type | possible_keys        | key         | key_len | ref                                 | rows | Extra       |
+----+-------------+----------------+------+----------------------+-------------+---------+-------------------------------------+------+-------------+
|  1 | SIMPLE      | playpack_media | ref  | media_id,playpack_id | playpack_id | 4       | const                               |  594 |             |
|  1 | SIMPLE      | page_views     | ref  | itemId,started_at    | itemId      | 4       | db_playsmrt.playpack_media.media_id | 4236 | Using where |

It seems to bottleneck at the "copying to tmp table" stage. I've tried increasing tmp_table_size and max_heap_table_size to 256M and key_buffer_size to 64M - the latter change dropped the time from ~18 minutes to ~11, which is a big improvement but still terrible, and a further increase to 128M didn't do much.

One thing I noticed while hammmering at this is that page_views uses MyISAM and playpack_media uses InnoDB, which I'm reading can be an issue. However, changing page_views to InnoDB on my (much smaller) development server makes it dramatically slower. It's possible that it becomes worthwhile on larger tables, but just making the experiment would take a long time.

Does anyone have an idea what's going on here? What else should I be looking at?


texts: Selecting different values from different columns

texts:
I need a query to select different values from different columns as shown below:

timestamp          queues           queuesname        groupnumb
23-01-2014         FNG              CGI               15467P
24-02-2014         ACS              GHK               15467P
26-02-2014         ERT              FNG               15467P
01-03-2014         ACS              TYU               15467P
04-05-2014         TYI              GHK               15467P

I need the the row with queues = ACS (latest one has timestamp 01-03-2014) and queuesname = GHK (latest one has timestamp 04-05-2014)

For the above example I need this output:

Min_timestamp      max_timestamp       queues       queuesname    groupn
01-03-2014         04-05-2014          ACS          GHK           15467P

I don't need a hard coded query.


texts:
It looks like there is a (finally) an open source columnar and compression extension for Postgres.

I wonder if anyone had any experience with it yet?

I've worked with Greenplum for years, but I've been looking desperately for the open source alternative.


texts: Extending condition in a join with Postgres

texts:
I have a situation like this:

with
my_data as (
  select A.a1,B.b1,B.b2
  from some_table A
  left join lateral (
     select b1, b2 from X where X.id = A.id limit 1
  ) B ON TRUE
)
select * from my_data where b1 is not null
union all
select A.a1,C.b1,C.b2
from my_data A, lateral (
   select b1, b2 from Y where Y.id = A.id limit 1
) C
where A.b1 is null;

and it returns the expected result but I would like to know if there is a better way of writing it.

For example one iteration like:

select A.a1,B.b1,B.b2
from some_data A, lateral (
   select b1, b2 from X where id = A.id limit 1
   -- and if the query on 'X' fails then try:
   select b1, b2 from Y where id = A.id limit 1
) B

any idea?


texts: Implementing voting system like reddit/StackExchange?

texts:
In creating an application that would require a voting system similar to that of StackExchange or reddit, I want to know how the database tables should optimally be structured.

So far, I have something planned out like this:

  Vote(UserID[PK], ContentID[PK], IsLike)

I had planned that the UserID and ContentID(the entity being "liked") would be a composite primary key. This would make it so that I can tell which users have already "liked" certain content. The IsLike field would determine whether the like/vote was positive or negative(like or dislike).

Also, could the composite primary keys be foreign keys linking to their respective tables (UserID --> User table, ContentID --> Content table) or is there something else I would have to do to make sure this would work?

Would this be an adequate way to store information about which users have liked certain content? Any suggestions welcome.


texts:
I have these entries in a field in one table under a DB running Postgres 9.4:
copy schema_name.table_name to '/path/path2/path3/path4/reports/export"

and would like to replace ONLY the word "reports" by "reports_test"
What would be the proper way to do this?
I understand is a regular expression in an SQL update query but not exactly sure.


texts: How to implement a low resource privileged user on Oracle 10g using Resource Manager?

texts:
In my database I have users and their respective grants using the least amount of privileges principle.

How do I start taking advantage of resource manager to prevent users from hogging all the resources? Can the process of implementing the resource manager be summed up in some short precise steps? I am not looking for a detailed process but instead like a bullet list of steps you have to complete to have successfully implemented this feature.


texts: How to prevent trigger from overwriting values that were explicitly set?

texts:
I tried this but doesn't work because NEW.updated is never NULL (I suppose NEW gets populated by the values currently in the row in addition to the values in the update-statement?):

create temp table test (
    created  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE FUNCTION update_timestamp() RETURNS TRIGGER AS
$$
BEGIN
    IF NEW.updated IS NOT NULL THEN
        NEW.updated := now();
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER asdf BEFORE UPDATE ON test
FOR EACH ROW EXECUTE PROCEDURE update_timestamp();

How can I make it set NEW.updated := now(); only when updated is not explicitly set in an update-statement? So that the trigger doesn't overwrite me when I want to set the updated column manually (eg. updated=updated).


texts: How to sort values starting with a letter followed up by a number?

texts:
I have a series of values, possibilities are : A1, A2, B1, B2, A100, A101, B100, B200, 1, 2, 3, 4 and so on.

How can I sort it so that first the letter is sorted, and after that the number with the numbers from the previous list would be going like this:

A1
A2
A100
A101
B1
B2
B200
1
2
3
4

The list itself is a lot larger and not really predefined so preferably no solutions in which each value needs to get handled separately.

I'm using Microsoft SQL Server 2016 (SP1) (KB3182545).


texts: Can we tell dbcc to ignore subnormal real values?

texts:
Currently dbcc DATA_PURITY complains about subnormal real values in some of my columns, such as 7.561407E-42. I would rather keep them in my tables, and ask dbcc to ignore them.

I am OK with the check on upper limit, and other checks on other column types. So I would rather keep running the utility, but have it stop complaining about legitimate very low values.

Is there a way to accomplish that?


texts: Why a transaction retrieve the same row more than one time?

texts: I understand that nonrepeatable read problem occurs when a transaction t1 retrives a row, transaction t2 then updates that row and transaction t1 then retrieves the same row again. T1 retrieved the same row, but saw two different values. I wonder why a transaction read the same row again. Why it did not use the old value? If it retrieve again to get updated value, getting different values should not be problem. Can you please explain me? When repeatable read isolation level should be used?


texts: Is it good practice to create tables dynamically?

texts:
Let's say that I'm making an IRC bot, and in addition to all the other stuff that should be stored in the DB, I want to log all the messages in each channel in which the bot is present.

I already have a channels table with columns id, channel_name, and some other info.

For logging each message, what seems the most sense to me is to create a messages_channelname table for each channel, and in there store the message along with whatever other information is associated with it.

That elicits a problem, though - the name of the table is now dissociated from the channel's id (although I could alleviate this by making the table name messages_channelid).

I feel like the best practice here, rather than what makes the most sense, would be to have a master messages table that contains the messages from all channels, with columns channel_id, id and message, where id would be the index of the message per channel. But then I'd have (unfounded) concerns about table size and possibly search speed.

What's the best way to go about this?


texts: DISCONNECTION (bad value of SQLNET.EXPIRE_TIME)

texts:
We start a database without sqlnet.ora file.
So as  SQLNET.EXPIRE_TIME is not configured, some process has been disconnected.

We had sqlnet.ora with a SQLNET.EXPIRE_TIME=1, restart the listener but have still the same problem (somme disconnected process after an idle time).
It seems that the database ignore this new parameter.

We use shared server for this db (11.2).

Do you have any suggest (of course, it's impossible to restart the database)?

thanks


texts: SQL Server - Handling localization of strings in nested non-deterministic view stacks

texts:
While profiling a database I came across a view that is referencing some non-deterministic functions that get accessed 1000-2500 times per minute for each connection in this application's pool. A simple SELECT from the view yields the following execution plan:

That seems like a complex plan for a view that has less than a thousand rows that may see a row or two change every few months. But it gets worse with the following other observances:

Nested views are non-deterministic, so we cannot index them
Each view references multiple UDFs to build the strings
Each UDF contains nested UDFs to get the ISO codes for localized languages
Views in the stack are using additional string builders returned from UDFs as JOIN predicates
Each view stack is treated as a table, meaning that there are INSERT / UPDATE / DELETE triggers on each to write to the underlying tables
These triggers on the views use CURSORS that EXEC stored procedures which reference more of these string building UDFs.

This seems pretty rotten to me, but I only have a few years experience with TSQL. It gets better, too!

It appears the developer who decided that this was a great idea, did all this so that the few hundred strings that are stored can have a translation based on a string returned from a UDF that is schema-specific.

Here's one of the views in the stack, but they are all equally bad:

CREATE VIEW [UserWKStringI18N]
AS
SELECT b.WKType, b.WKIndex
    , CASE
       WHEN ISNULL(il.I18NID, N'') = N''
       THEN id.I18NString
       ELSE il.I18nString
       END AS WKString
    ,CASE
       WHEN ISNULL(il.I18NID, N'') = N''
       THEN id.IETFLangCode
       ELSE il.IETFLangCode
       END AS IETFLangCode
    ,dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex, N'WKS') AS I18NID
    ,dbo.UserI18N_Session_Locale_Key()  AS IETFSessionLangCode
    ,dbo.UserI18N_Database_Locale_Key() AS IETFDatabaseLangCode
FROM   UserWKStringBASE b
LEFT OUTER JOIN User3StringI18N il
ON    (
il.I18NID       = dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex, N'WKS')
AND il.IETFLangCode = dbo.UserI18N_Session_Locale_Key()
)
LEFT OUTER JOIN User3StringI18N id
ON    (
id.I18NID       = dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex,N'WKS')
AND id.IETFLangCode = dbo.UserI18N_Database_Locale_Key()
)
GO

Here is why UDFs are being used as JOIN predicates. The I18NID column is formed by concatenating: STRING + [ + ID + | + ID + ]

During testing of these, a simple SELECT from the view returns ~309 rows, and takes 900-1400ms to execute. If I dump the strings into another table and slap an index on it, the same select returns in 20-75ms.

So, long story short (and I hope you appreciated some of this sillyness) I want to be a good Samaritan and re-design and re-write this for the 99% of clients running this product who do not use any localization at all--end users are expected to use the [en-US] locale even when English is a 2nd/3rd language.

Since this is an unofficial hack, I am thinking of the following:

Create a new String table populated with a cleanly joined set of data from the original base tables
Index the table.
Create a replacement set of top-level views in the stack that include NVARCHAR and INT columns for the WKType and WKIndex columns.
Modify a handful of UDFs that reference these views to avoid type conversions in some join predicates (our largest audit table is 500-2,000M rows and stores an INT in a NVARCHAR(4000) column which is used to join against the WKIndex column (INT).)
Schemabind the views
Add a few indexes to the views
Rebuild the triggers on the views using set logic instead of cursors

Now, my actual questions:

Is there a best practice method to handle localized strings via a view?
Which alternatives exist for using a UDF as a stub? (I can write a specific VIEW for each schema owner and hard-code the language instead of relying on a variety of UDF stubs.)
Can these views be simply made deterministic by fully qualifying the nested UDFs and then schemabinding the view stacks?


texts: SSIS Condition that include two connections/data bases

texts:
In a SSIS package , I need some sort of condition that include two connections.

The simplified problem is:
A simple CSV import to SQL Server, DB1, table 1( col1, col2 ).
I need to populate table 1 > col2 = '1'   if col1     is found in    another database , DB2 > table 2 ;      db2 is specified as a different connection in package.

and table 1 > col2 = '2'   if col1 is not found in the lookup database.

any guidance is appreciated,
ta


texts: SQL Server 2008: DoMiniDump () encountered error (0x80004005) - Unspecified error

texts:
We're running a SQL Server 2008 database and are getting the following output(error?) in our logs:

06/23/2015 21:56:41,Server,Unknown,Process 214:0:42 (0x73b0) Worker 0x0000000BB2CC61A0 appears to be non-yielding on Scheduler 39. Thread creation time: 13079558329722. Approx Thread CPU Used: kernel 0 ms<c/> user 0 ms. Process Utilization 8%. System Idle 91%. Interval: 70169 ms.
06/23/2015 21:56:41,Server,Unknown,DoMiniDump () encountered error (0x80004005) - Unspecified error
06/23/2015 21:56:41,Server,Unknown,External dump process return code 0x20000001.<nl/>External dump process returned no errors.
06/23/2015 21:56:38,Server,Unknown,Stack Signature for the dump is 0x000000000000028A
06/23/2015 21:56:38,Server,Unknown,* *******************************************************************************
06/23/2015 21:56:38,Server,Unknown,*
06/23/2015 21:56:38,Server,Unknown,* Non-yielding Scheduler
06/23/2015 21:56:38,Server,Unknown,*
06/23/2015 21:56:38,Server,Unknown,*   06/23/15 21:56:38 spid 8656
06/23/2015 21:56:38,Server,Unknown,* BEGIN STACK DUMP:
06/23/2015 21:56:38,Server,Unknown,*
06/23/2015 21:56:38,Server,Unknown,* *******************************************************************************
06/23/2015 21:56:38,Server,Unknown,***Unable to get thread context for spid 0
06/23/2015 21:56:38,Server,Unknown,Using 'dbghelp.dll' version '4.0.5'

I can't find much about this on google. There a lot of similar errors, which talk about hanging sql servers and that this might be a user trying to execute a dump that has no permission? I can't find anything about this exact error though.

Could anybody point me in the right direction or know immediately what is causing this?

Note: We've been getting the following error as well, which might have something do with it, but I'm not sure:

06/23/2015 22:22:50,spid23s,Unknown,SQL Server failed to prepare DTC transaction. Failure code: 8522.
06/23/2015 22:22:50,spid23s,Unknown,Error: 8565<c/> Severity: 16<c/> State: 1.
06/23/2015 22:22:50,spid23s,Unknown,Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.
06/23/2015 22:22:50,spid23s,Unknown,Error: 8522<c/> Severity: 16<c/> State: 1.

Appreciate your help.


texts: MYSQL search for each name in 2 columns and add values associated for each player

texts:
I have a database "game_results".

It contains the following:

PlayerID (int not null primary key auto increment)
player1 (varchar)
p1destroyed (int)
player2 (varchar)
p2destroyed (int)
winner (varchar)

It's not the most efficient database (multiple repeats of data in several columns), but it's meant to be a simple way of tracking game results.

After each game, the players input their names, the point amount of ships they killed of the opposing player and then who won.

I'm trying to get the data from both players and add up the for and against points destroyed for each player.

I know this is where the poor design has bitten me, but I'd rather try and figure this out for other things I have planned.

I want to be able to get a result set that gets each unique player with both the amount of points they have destroyed and the amount they have had destroyed by their opponents.

I have figured out how to do it in one way:

SELECT
player1, SUM(p1destroyed) AS Fmov, SUM(p2destroyed) AS Amov
FROM game_results
GROUP BY player1;

But I'm stuck getting the unique values from both p1 and p2 to merge.

I'm thinking a union is probably the right place to look, but I just can't seem to make it work.

Any ideas please?


texts:
I need some guidance on normalisation from 1st Normal Form to 3rd Normal form. I have already set up my database which is working fine but needed to show some normalisation to show that I did do some design. MY only issue is that while going through the normal forms, have I actually normalised the database correctly or not?

Can somebody please have a look at my normalisation and show any changes I should make in their replies?

Screenshort of ER schema - For full size image click here:

And where I started. Screenshot of 0NF:

Screenshot of 1NF:

Screenshot of 2NF:

Screenshot of 3NF:


texts: Query to convert the count to million or thousands

texts:
I am running a query on a database to return count(*) for number of rows.

If the count is greater than million say 2,250,304, I want the query to return 2.25M.
If the count is greater than 1000 but less than a million say 65130 I want the query to return '65.1k'.
If the count is less than 1000 then the query should return actual count.

Here is my query:

SELECT DATENAME(mm, CREATEDTS) + ', ' + DATENAME(yyyy, CREATEDTS) AS Month,
       CASE
         WHEN count(*) > 1000
              AND count(*) < 1000000 THEN ( CONVERT (VARCHAR(10), ( count(*) / 1000 )) ) + 'k'
         WHEN count(*) > 1000000 THEN ( CONVERT (VARCHAR(10), ( count(*) / 1000 )) ) + 'm'
         ELSE COUNT(*)
       END
FROM   USAGEDATA
GROUP  BY month(CREATEDTS),
          DATENAME(mm, CREATEDTS) + ', ' + DATENAME(yyyy, CREATEDTS)
ORDER  BY MONTH(CREATEDTS)

When I run it I am getting an error:

  Msg 245, Level 16, State 1, Line 1 Conversion failed when converting
  the varchar value '165k' to data type int.

I am not sure where I am converting varchar to INT and getting this error.


texts:
I need some help! I have a scenario of oracle 10gR2 database replication using data guard. I have selected the data guard command line interface. After my initial setup I can ship and apply logs from the primary database to the standby db. DGMGRL shows that configuration and databases have been set with SUCCESS. But, when I try to switch to the standby instance I take the following errors and nothing happened. I believe that there is a luck of settings on the database parameters.

DGMGRL> switchover to 'INTER';
Performing switchover NOW. Please wait...
Error: ORA-16751: Resource guard encountered errors in switchover to primary
Failed.
Can not proceed to switchover. Primary is still "MILAN".
DGMGRL> show database 'MILAN' 'LogXptStatus';
Error: ORA-16777: unable to find the destination entry of a standby in V$ARCHIV_DEST
DGMGRL> show database 'MILAN' 'StatusReport';
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT
               milan      ERROR ORA-16783: instance milan not open for read and write access
               milan    WARNING ORA-16728: Checking the consistency of property
LogXptMode hits error ORA-16777.
               milan    WARNING ORA-16777: unable to find the destination entry
of a standby in V$ARCHIVE_DEST
               milan    WARNING ORA-16728: Checking the consistency of property
DelayMins hits error ORA-16777.
               milan    WARNING ORA-16777: unable to find the destination entry
of a standby in V$ARCHIVE_DEST
               milan    WARNING ORA-16728: Checking the consistency of property
Binding hits error ORA-16777.
               milan    WARNING ORA-16777: unable to find the destination entry
of a standby in V$ARCHIVE_DEST
               milan    WARNING ORA-16728: Checking the consistency of property
MaxFailure hits error ORA-16777.
               milan    WARNING ORA-16777: unable to find the destination entry
of a standby in V$ARCHIVE_DEST
               milan    WARNING ORA-16728: Checking the consistency of property
ReopenSecs hits error ORA-16777.
               milan    WARNING ORA-16777: unable to find the destination entry
of a standby in V$ARCHIVE_DEST
               milan    WARNING ORA-16728: Checking the consistency of property
AsyncBlocks hits error ORA-16777.
               milan    WARNING ORA-16777: unable to find the destination entry
of a standby in V$ARCHIVE_DEST
               milan    WARNING ORA-16728: Checking the consistency of property
NetTimeout hits error ORA-16777.
               milan    WARNING ORA-16777: unable to find the destination entry
of a standby in V$ARCHIVE_DEST

Could anyone show me the required database parameters that have to be set in order to switch to the standby database and continue shipping and applying logs to the primary db (primary now as standby)?

Thank you in advance for you help!


texts: SSIS Excel to SQL Server - Need to add SSIS variables into the mix

texts:
I have an SSIS job that loads Excel files from a UNC path into a staging SQL table, then executes a stored procedure at the SQL server to load the data into a production SQL table.

What I need to do is to ensure that along with the data from Excel, I am also loading a SSIS variable.

Say that the Excel has 10 columns. My target SQL table has 11 columns. The 11th column is for the name of the Excel file that was loaded, which I need to load alongside the Excel data.

This is an oversimplified example but it is pretty much it.


texts: Best way to move functions from postgresql to another

texts:
I'm not familiar with postgres but I'm sure there has to be a way to source control its functions.
Currently my team adds functions on a postgresql database directly, and when it's time to deploy to production they just move the functions manually, so it's natural to forget to move certain functions.

Is there a tool similar to git that would allow them to branch out, merge and deploy?


texts: This question is about impersonation in Microsoft SQL Server. I want to know that if I have impersonation permissions on a specific login then I can easily impersonate that login whenever I want and I do not need to have a stored procedure with EXECUTE AS OWNER? If that is so then what is the purpose of a stored procedure with EXECUTE AS?


texts: Can someone help me with this PL/SQL block?

texts:
Write a PL/SQL block to display the details of five employees who are getting the highest salary (table name is Employee)
Is this how you do it??

DECLARE
Cursor C is select * from Employee ORDER BY salary DESC;
Edet C%ROWTYPE;

BEGIN
 Open C;
 Loop
 fetch C into Edet;
 EXIT WHEN C%ROWCOUNT == 5;
 DBMS_OUTPUT.PUT_LINE(Edet);
 end loop;
 Close c;
 END;


texts: MySQL InnoDB + high table creation-destruction rate

texts:
We are transferring a microsoft SQL based website (+app + plugins) structure to MySQL.

The website has a high "Content + Comments + Activity Tracking" behaviour.

Mainly because of the activity tracking, InnoDB structure looks like a must (high read, insert, update and truncate rates). All the activity is written intensively in a table for each content unity and it's periodically "compressed" to shrink the table.

To reduce the amount of tables in the DB, only the tables related to often accessed content are kept in the DB. Others are "packed" and deleted.

If any content becomes "active" again then we "unpack" and recreate the tables.

Our total amount of tables is about 16 million a year but, daily, we normally create and delete about 5k of those tables and about 50k remain always "unpacked" in the DB.

What kind of memory and HD space leak problems can this structure give us in MySQL-InnoDB? Is this a good practice or should we consider a structure change?


texts:
As you know union removes duplicated rows. I thought it removes from the latter ones, but it doesn't.

I found out that if there's no order by clause, Oracle sorts the merged dataset by the first column and removes the duplicates.

select 4,5,6 from dual
union
select 1,2,3 from dual
union
select 4,5,6 from dual

It results :

1   2   3
4   5   6

Whereas I expect :

4   5   6
1   2   3

I failed many times with this, and make the problem simple like the example above.

How do I keep the union sequence without order by?

In my real case, the first select is full match(=) result and the second is partial match(like) result.

If I search for roma for example.

Full match result : roma, partial match result : aroma.

And of course full match result should come first. As I said, it seems order by the first column of union result.

But, as you see:

select 'roma' from dual -- result comes out anyway..
union select 'aroma' from dual;

Result:

aroma
roma

aroma is alphabetically ahead of roma, it comes first.

Maybe I should try union all and remove the duplicates?


texts: Concern with CHECK TABLE on large InnoDB 24x7 table,

texts: Will CHECK TABLE on large InnoDb table causing issue with blocking of online transactions? Assuming the table size is 5GB.


texts: Drive holding my database is running out of space. What are my options?

texts:
I am new to database administration. I've been scraping a website for a month now. I have two drives, each 2TB in size. I have PostgreSQL 10 on my first drive (c:) and it has 50GB left. I've only scraped about 75% of the website and have another 25% to go. What are my options?

I can't extend my first drive because my computer is old (hp z600) and the RAID software only supports up to 2TB. I would prefer not to stop my scraping process because I was an idiot and didn't keep track of pages that I've already visited. I thought 2TB would be enough, but I was wrong.

Running SHOW autovacuum; shows that autovacumm is on.


texts: How to improve the performance of this select query?

texts:
I have two tables: TABLE1 and TABLE2.

And I am running this select query to fetch the data. It takes long time to run:

SELECT TABLE1.* , TABLE2.COL1 , TABLE2.COL4 FROM TABLE1, TABLE2
WHERE TABLE1.COL1 IN ('1','TEXT','HO TED')
  AND TABLE1.COL2= 1800
  AND TABLE2.COL1 = 10519198
  AND TABLE1.COL3>= 100
  AND TABLE1.COL4= TABLE2.COL2
  AND TABLE2.COL3=('TEXT')

How could I improve the performance of this select query? Does too many AND affect my performance?


texts: Update linked server table based on local table

texts:
i'm trying to update a remote table using linked server, based on id of my local table.

I'm trying this:

update [HQSQLHOM01\SQLHOM01].dba_teste.dbo.tabela2
                set [HQSQLHOM01\SQLHOM01].dba_teste.dbo.tabela2.cod = tabela1.codigo
                from [HQSQLHOM01\SQLHOM01].dba_teste.dbo.tabela2
                    join tabela1
                    on tabela1.id=[HQSQLHOM01\SQLHOM01].dba_teste.dbo.tabela2.id

  The multi-part identifier
  "HQSQLHOM01\SQLHOM01.dba_teste.dbo.tabela2.id" could not be bound.

i tried to update the local server based on the linked server but I still receive errors like the TABLE_NAME could not be bound.

update tabela1
        set tabela1 .cod=[HQSQLHOM01\SQLHOM01].dba_teste.dbo.tabela2.cod
            from [HQSQLHOM01\SQLHOM01].dba_teste.dbo.tabela2
            join tabela1
            on [HQSQLHOM01\SQLHOM01].dba_teste.dbo.tabela2.id=tabela1.id

  Msg 4104, Level 16, State 1, Line 40 The multi-part identifier
  "HQSQLHOM01\SQLHOM01.dba_teste.dbo.tabela2.cod" could not be bound.

There's a lot of questions like this one on stackoverflow but none of them is working.

tabela1=local


texts:
I was wondering whether there were a list of forbidden (reserved) database names in mysql.

For instance, mysql is obviously one. But is there a determinated list? I don't want to cover 99% in my program (I can figure out a simple list by myself...), but I rather would like to be up to date and reliable at 100%.

Any official source coming up to your mind?


texts: I'm a complete newbie in oracle databases. I'm just curious on what mode is better when you want to migrate your database to another host by using data pumping. Lately, I'm having a problem on import datapump. I used export in schema mode and it seems that some of my packages were not stored correctly or somehow invalid. But when I checked my database if it has some invalid packages(not compiled after impdp) it appears that the packages were missing. I found out that I had an error about "Tablespace $tablespacename does not exist". I really don't have any idea where that tablespace came from because when I checked out my old database, it seems that $tablespacename does not exist. I looked over all the users but I really can't find that $tablespacename. Now I am too confused about where did I got that $tablespacename.


texts: Insert column from other table / merge tables


texts: where clause return empty row in view mysql

texts:
i have two tables with the schema below:

INCOME TABLE:
INCOME_ID, INCOME_NAME, INCOME_DATE

OUTCOME TABLE:
OUTCOME_ID, OUTCOME_NAME, OUTCOME_DATE

EXAMPLE DATA INCOME
INCOME_ID | INCOME_NAME | INCOME_DATE
1         | FIRST EXMPL | 24-07-2016
2         | SEC EXAMPLE | 25-07-2016
ETC
EXAMPLE DATA OUTCOME
OUTCOME_ID | OUTCOME_NAME | OUTCOME_DATE
1          | FIRST        | 24-07-2016
2          | SEC          | 03-08-2016
ETC

so i use union all to combine these table

SELECT INCOME_DATE AS DATE, INCOME_NAME AS NAME FROM INCOME
UNION ALL
SELECT OUTCOME_DATE, OUTCOME NAME FROM OUTCOME

and i use query above to create view

CREATE VIEW V_BALANCE AS SELECT INCOME_DATE AS DATE, INCOME_NAME AS NAME
UNION ALL
SELECT OUTCOME_DATE, OUTCOME_NAME FROM OUTCOME

the view was succesfully created but when i want to add where clause in view, it returns empty row

SELECT * FROM V_BALANCE WHERE DATE BETWEEN = '24-07-2016' AND '03-08-2016'


texts: SQL Server Standard Edition Failover Cluster Installing Windows Updates

texts:
i have a question concerning the installation of sql server updates on a sql server 2012 standard edition failover cluster.

For example a service pack installation. Is it possible to install the service pack at first on one node? And after the sucessfull installtion do the same thing on the other node? So that i have no downtime for the applications during the restart?

Greetings


texts: Windows 2008 R2: Unable to upgrade MySQL 5.5.58

texts:
We have MySQL 5.5.58 sitting on a Windows 2008 R2 box.
I have downloaded the Installer from https://dev.mysql.com/downloads/mysql/ but nothing happens when I try and upgrade.
At the installer screen, I click on Upgrade and it says one of my products have upgrades available.
I click on catalog and it lists a bunch of Samples and Exercises.
Any suggestions?
Thanks


texts: Sort results of a query by the order of OR clause

texts:
I have a string as input, that represents an hostname, and a table that contains suffixes (e.g. co.uk, uk, etc).

I'd like the database to return the records based on the order of the where clause. For example, let's assume the suffixes table contains the following entries:

| id | name   |
|----|--------|
| 1  | co.uk  |
| 2  | uk     |
| 3  | foo.uk |

Assuming the input is example.co.uk, I will produce a query like the following one:

SELECT *
FROM suffixes
WHERE
  name = "example.co.uk" OR
  name = "co.uk" OR
  name = "uk"
LIMIT 1

The result should be:

| 1  | co.uk  |

However, from tests I executed, it is not guaranteed that PG follows the order of the OR conditions by defaults. In some cases, when there are multiple entries matching the OR conditions (e.g. in this case co.uk and uk), it may very well return uk.

In fact, if I take the same query and swap the OR conditions:

SELECT *
FROM suffixes
WHERE
  name = "uk" OR
  name = "co.uk"
LIMIT 1

in most cases it returned the same result of the previous query.

How can I tell PostgreSQL to return the first match of the OR query, in the order specified in the query?


texts:
I am curious if there are any major differences with performance and/or conventionality regarding which binary data types one chooses. Let's say I have a table of metallic objects and I am considering to create a binary field to record whether or not the object is reflective.

reflective BIT NOT NULL DEFAULT 0 -- 0 for nonreflective 1 for reflective
reflective CHAR(1) NOT NULL DEFAULT 'F' -- False for nonreflective, True for reflective
reflective ENUM('N','R') NOT NULL DEFAULT 'N' -- N for nonreflective R for reflective.
reflective ENUM('nonreflective','reflective') DEFAULT 'nonreflective' -- I think this takes up the same amount of storage resources but is clearer

Question: ENUM aside, they all seem more or less equal in terms of readability,  and while I'm not exactly sure which would make for most efficient scaling and querying, I'd venture they would all be comparable. Am I right in this assumption; is the binary data structure largely arbitrary, or are there trade-offs I should be aware of?

Also I'd be interested to know if among these examples (or structures I did not include) there is a clear convention, and why.


texts: SQL Server Management Studio is not available

texts:
When I installed SQLManagementStudio_x64_ENU.exe I am not able to connect to my server. The server name is not visible in either image.

Is there any issue in my installation?


texts: Why Does the SQL Server Query Optimizer Not Convert an OUTER JOIN to an INNER JOIN When a Trusted Foreign Key Is Present?

texts:
It appears that the SQL Server query optimizer does not translate an OUTER JOIN into an INNER JOIN when the join column in the first table is defined as NOT NULL and has a trusted foreign key constraint to the corresponding column in the second table.
It seems that in this scenario, the OUTER JOIN could be translated into an equivalent INNER JOIN, because each row in the first table is:

guaranteed to have a value in the column (NOT NULL constraint), and
guaranteed to have a matching row in the second table (trusted foreign key constraint).

For example, consider the following tables:
 CREATE TABLE dbo.tbl_fk
 (
    fk_val CHAR(1) NOT NULL PRIMARY KEY CLUSTERED,
    junk VARCHAR(100) NOT NULL
 );

 CREATE TABLE dbo.tbl_main
 (
    id INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1),
    fk_val CHAR(1) NOT NULL FOREIGN KEY REFERENCES dbo.tbl_fk(fk_val)
 );

In the following query, why does the optimizer not convert the LEFT OUTER JOIN to an INNER JOIN in the execution plan?
SELECT m.fk_val, f.junk
FROM dbo.tbl_main AS m
LEFT OUTER JOIN dbo.tbl_fk AS f
    ON m.fk_val = f.fk_val;

By adding a predicate to explicitly remove NULL values from the second table, however, the optimizer converts the LEFT OUTER JOIN into an INNER JOIN in the query plan, as expected:
SELECT m.fk_val, f.junk
FROM dbo.tbl_main AS m
LEFT OUTER JOIN dbo.tbl_fk AS f
    ON m.fk_val = f.fk_val
WHERE f.fk_val IS NOT NULL;

Undocumented trace flags show that a rule is applied to change the OUTER JOIN to an INNER JOIN in the query with the predicate:
SELECT m.fk_val, f.junk
FROM dbo.tbl_main AS m
LEFT OUTER JOIN dbo.tbl_fk AS f
    ON m.fk_val = f.fk_val
WHERE f.fk_val IS NOT NULL
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8621);

***** Rule applied: A LOJ B -> A JN B

These examples were tested using SQL Server 2014 and 2019 using a number of different compatibility levels and also using both the legacy and "new" cardinality estimator.  The behavior appeared to be the same in all cases.
Forrest McDaniel pointed out that (as of 2010), Sybase appeared to have such a transformation built into their product (see Example 1): http://dcx.sybase.com/1200/en/dbusage/queryopt-sectb-5356466.html


texts: How to copy one table data to another on different server?

texts:
I have country-wise MySQL servers and one master server.

The master server has all country data.

In that I want to copy data of a particular country from the master MySQL server.

Example: if any data inserted on the master MySQL server is related to India, it will also be inserted into the Mysql server in India.

How can this be achieved?

Table structures in both a country-level server and the master server are the same:

--
-- Table structure for table `vxp_service_types`
--
CREATE TABLE IF NOT EXISTS `vxp_service_types` (
  `st_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `st_name` varchar(25) NOT NULL,
  PRIMARY KEY (`st_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=17 ;


texts:
I want active log file in mariadb for check last query used in php, i find mysql config in internet like as blow:

general_log_file        = /var/log/mysql/mysql.log
general_log             = 1

or

log = /var/log/mysql/mysql.log

I added this config in /etc/mysql/my.cnf and [mysql] section and restart mariadb service.
but i didn't get any log, how to fix this?

i find answer:

[mysqld]
...
log_output = FILE
general_log = ON
general_log_file = mysql.log

mysql.log save in datadir, run SHOW VARIABLES WHERE variable_name = 'datadir' in mariadb to get datadir.


texts: Optimize MySQL InnoDB query for SUM and Order By

texts:
I have an MySQL InnoDB table with 1.3M rows and 670MB size:

+----------------+--------------+------+-----+---------+-------+
|     Field      |     Type     | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| item_name      | varchar(255) | NO   |     | NULL    |       |
| item_link      | varchar(50)  | NO   | PRI | NULL    |       |
| item_price     | varchar(10)  | NO   |     | NULL    |       |
| shop_name      | varchar(40)  | NO   | MUL | NULL    |       |
| sales          | int(11)      | NO   |     | NULL    |       |
| sale_date      | varchar(10)  | NO   | PRI | NULL    |       |
+----------------+--------------+------+-----+---------+-------+

CREATE TABLE `sales`
  (
     `item_name`      VARCHAR(255) NOT NULL,
     `item_link`      VARCHAR(50) NOT NULL,
     `item_price`     VARCHAR(10) NOT NULL,
     `shop_name`      VARCHAR(40) NOT NULL,
     `sales`          INT(11) NOT NULL,
     `sale_date` VARCHAR(10) NOT NULL,
     PRIMARY KEY (`item_link`, `sale_date`),
     KEY `sale_date` (`sale_date`),
     KEY `shop_name` (`shop_name`)
  )
engine=innodb
DEFAULT charset=latin1

Here are my my.ini settings for my 3GB RAM server:

key_buffer = 16M
max_allowed_packet = 16M
sort_buffer_size = 8M
net_buffer_length = 8K
read_buffer_size = 2M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 8M
log_error = "mysql_error.log"
innodb_autoinc_lock_mode=0
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
thread_stack = 192K
tmp_table_size = 64M

innodb_buffer_pool_size = 1.5G
innodb_additional_mem_pool_size = 16M
innodb_log_file_size = 350M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 120
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90

When I run next query it takes over 10-15 seconds to return the results:

SELECT item_name,
       item_link,
       item_price,
       shop_name,
       sales
FROM   `sales`
WHERE  sale_date = '2019-04-18'

and even longer for:

SELECT item_name,
       item_link,
       item_price,
       shop_name,
       Sum(sales) AS sales
FROM   `sales`
WHERE  sale_date BETWEEN '2019-04-17' AND '2019-04-18'
GROUP  BY item_link

I have a composite primary key for item_link and sale_date, I've also added indexes for shop_name and sale_date.

I don't know what else to do and I think this kind of query should perform way faster for such a relatively small table, please help!


texts:
I'm using SQL Server 2008 R2 for real-time location aware service. The service mostly receives location data (lat/lon) from a user and returns locations around the user.

The data is being sent from each user once in a second. For every location being reported there is a query that checks other users around that user and returns data about them.

I'm using Lat/Lon as float fields and run a scalar-function to find the closest users.

I guess that approach is not the best. I know there are spatial data constructs in SQL Server, but don't know if they are good. Should I use them? If so, what is the best approach?

What approach should I take to make this operation as fast and scalable as possible?

Maybe NoSQL is better for spatial data?

P.S - I'm talking about thousands and tens of thousands users at any given time.


texts: Oracle: list user-created tables in the SYS schema

texts: I need to delete all tables (hundreds) in the SYS schema that someone accidentally created with sqlplus. Looking in dba_tables and dba_objects, I don't see a way to distinguish these tables from pre-existing stock system tables. Any help appreciated.


texts: SSIS unpivot returns different results when run via a SQL agent job

texts:
I hope someone can help me. I have an SSIS package which grabs data from an excel (.xls) file, unpivots it from a rubbish format into a 'proper' format, transfers it to a SQL server 2008 R2 db, and runs some stored procedures. I thought I had it working but testing has thrown up some interesting results. The Excel file has years as separate columns, from 2000 - 2050, plus a whole bunch of other fields.

The really annoying thing is this: when I kick off the SSIS package manually it works as expected. If I run it in BIDS debug mode it works as expected. However if I run it via my SQL agent job it omits all years after 2027. These records are simply not appended. This is causing me some frustration!

Some troubleshooting using the logging functionality has led me to the following information:

OnWarning,EDI-SQLINTDEV01,WOODMAC\ServicePFF,DataFlow Append Temp Data,
{18E3EDB7-DBFE-49E3-8187-7B5CFA4684F6},{48A81BE7-AEDB-4313-A88D-537C5F14693F},
14/08/2014 15:56:24,14/08/2014 15:56:24,-2147192632,0x,
The external columns for component "Excel - Gen Capacity" (2121) are out of
synchronization with the data source columns. The external column "2028" needs to be updated.
The external column "2029" needs to be updated.
The external column "2030" needs to be updated.

(etc. all the way to 2050)

If I run the package manually these entries are not included in the output log.

Can anyone help me?

It's probably relevant to mention that the filepath of the excel file is stored in a user variable, and is different every time, and is retrieved from a SQL query.

EDIT:

I've made a bit more progress with troubleshooting. I know why it starts failing at 2028. It's because there were some empty cells beneath the column headers and 2028 was the first year where there were 8 or more of these. So, bizarrely, if there are 7 or fewer null rows beneath the column header, it will simply ignore the nulls and append all the data for that column. However if there are 8 or more nulls, it seems to ignore the entire column regardless of how much data there is further down. No records are unpivoted where there are 8 or more empty cells below the column heading!!!

How do I get round this? There must be a property setting somewhere.


texts:
Following up from my other question, I would like to start thinking about what I should take a look at on daily/weekly/monthly bases in terms of alerts. I am hoping to be able to see problems coming before they happen (that is the plan)...

So far, I have made a start collecting scripts for the following (no order):

Daily

Check system uptime (just in case I need to check anything as a DBA)
Check the last backup
Check the transaction log backups
Check the status of SQL Jobs
Check the average CPU usage for the last 24 hours (or 1140 mins)

Weekly

Check MSDB backup history
Check to see when the last time CheckDB was run
Check index fragmentation
Check index stats (reads vs writes etc)
Check for IO bottlenecks

Monthly

Check missing indexes
Check indexes that are no longer used

Any other suggestions? (I am new to DBA so any help/advice is always welcome)


texts: Mongodb: Possible to enlarge a capped collection?

texts:
If a collection is created as capped, is it possible to increase the cap without losing data?

Or to increase the cap without recreating the whole collection?

Thanks


texts: Generating maintenance database from maintenance tables currently in master and msdb

texts:
I want to create a maintenance database that i can clean up master and msdb, i am trying to collect all the table info from master and msdb that are not Microsoft tables, bearing in mind that some instances might have different structures due to different requirements. I am getting an error on the code

  Msg 102, Level 15, State 1, Line 27
  Incorrect syntax near '@ServerName'

is there maybe a simpler way of doing this?

DECLARE servers_cursor CURSOR
    FOR
    -- select * from environment
    -- select * from DatabaseInfo
        select  distinct LinkedserverName,Environment,ServerUse
        from SQLMonitor.dbo.Environment
        join master..sysservers on srvname COLLATE DATABASE_DEFAULT = LinkedserverName COLLATE DATABASE_DEFAULT
        where ServerUse = 'DEV'
        --and IncludeBackupsStats = 1
        order by LinkedServerName

    OPEN servers_cursor
    DECLARE @Servername varchar(255)
    DECLARE @sql varchar(8000)
    DECLARE @Environment char(4)
    DECLARE @ServerUse varchar(15)

    FETCH NEXT FROM servers_cursor INTO @ServerName,@Environment,@ServerUse
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        PRINT @ServerName
        CREATE TABLE [_TempDetails_]
        (DatabaseName sysname
        ,TableName sysname
        ,ActualCommand nvarchar(max))
        INSERT INTO [TempTableScripts].dbo._TempDetails_
        SELECT * FROM OPENQUERY(@ServerName, '
    declare @TableNames table
    (
        DatabaseName sysname
        , TableName sysname
    )
        insert @TableNames
        EXEC sp_msforeachdb @command1 = ''use ?;
        IF ''''?'''' IN (''''master'''',''''msdb'''')
        BEGIN
        SELECT ''''?'''', name from sys.tables T WHERE is_ms_shipped = 0
        END'+'

    select *, ''''SELECT TOP 1 * INTO ['''' + @ServerName + ''''_-_'''' + Databasename +''''_-_'''' + TableName +''''] FROM ['''' + @ServerName+''''].[''''+ Databasename + ''''].dbo.['''' + TableName + '''']''''
    from @TableNames
    ')
            --exec (@sql)
      FETCH NEXT FROM servers_cursor INTO @ServerName,@Environment,@ServerUse
      --PRINT @ServerName
    END
    CLOSE servers_cursor
    DEALLOCATE servers_cursor


texts: Service Principal as SQL Active Directory Admin, does it use Graph API?

texts:
I'm looking to use a service principal as the server admin, so it can be used in a release pipeline to create further active directory users.

I'm successfully able to make the service principal the server admin* and connect to the database using an Access token, so the service principal authentication works fine, which is great nice and an interesting challenge.

*EDIT: The AAD server admin was creating via the ARM template, notably if you tried creating manually via the portal, you cannot select service principals as admin users. But interestingly you can see the service principal name ok in the portal after creation using ARM instead. And you can login successfully with AAD auth, so it appears a server principal admin works fine.

However when creating further users, they never seem to be found with the error:

"Principal 'name here' could not be found at this time. Please try again later."

However when logged in as myself I am able to create other users fine.

Which leads me to think is SQL Server using some sort of Active Directory impersonation to validate the subsequent users / service principals exist?

If I were to grant my service principal access to the Graph API would that give it enough permissions? If so what is the minimum rights necessary?

I also see that Azure SQL Server has created a managed service identity in the background, so I wonder if that's also at play?

Also I saw on managed instances that they explicitly require a service principal with AAD rights to hook into AAD - which I presume is like an Azure SQL Server's MSI? But Azure SQL Server doesn't mention this with an AAD admin being enough.

I'd love to know how the underlying mechanism for the active directory hookup works as it could help me investigate my challenges further.


texts: SQL Server - Does adding a subscriber to an existing publication generate a new snapshot or create locks on the publisher DB?

texts:
I need to add a new subscriber with snapshot and transactional replication and I'm trying to determine if I need to add the subscriber after-hours or not.
This particular database is already being replicated on a different server, so there is an existing publication.

Does adding a subscriber to an existing publication generate a new snapshot?
If not, will the publisher DB have any locks while the subscriber is being created?

Thank you for your help.


texts:
Given that bit fields are just binary representations of data and need to be queried in slightly "strange" way.

Does it actually provide any benefit using a bit field for a boolean value? From what I can see it seems to suggest that space is the only real benefit.


texts: Can non-domain-connected users use Report Builder

texts:
I'm dealing with a scenario where SSRS is being provided to remote clients on a different domain. We've discovered that they can launch Report Builder (a ClickOnce application) from their Report Manager browser session, but RB then uses their local credentials when trying to do anything (e.g. hit a datasource) in the RB session.

This article (http://technet.microsoft.com/en-us/library/ms365173(v=sql.105).aspx) seems to indicate that it won't be possible to hack our way around this; whereas this one about Azure (http://msdn.microsoft.com/en-us/library/windowsazure/dn189155.aspx) appears to indicate that a local RB session may be able to interact successfully with Azure - does this suggest there's a glimmer of hope?

We are currently running SQL 2008 R2.


texts: Replication Issue - Changing the replication-do-db filter

texts:
I have a server that someone has setup which are acting as a master on both database. Since they have been configured as both master on the same database, changes one on end has caused all sorts of issues on the other.

On one of the master is displaying two databases in the replication-do-db when I do show master status which is rather odd.

Here's the entry in my system that's of concern:

Replicate_Do_DB: db1,db2,d1,db2

MySQL Version is 5.1, I'm looking for a way to change the replication filter. CHANGE REPLICATION FILTER seems to only apply to version 5.5.

An you explain to me what I need to do to recover from this scenario? Thank you.


texts: MySQL - Join on proceedure result - recommendations on what I should do?

texts:
I have already read that this CANNOT be done. I'm looking for an alternative.

Short problem
I have a really long SQL query (with a union) and I want to use it like a view (hide the union and pretend it's a table) so I can join on it. Of course, you cannot pass parameters to views, nor can you join on the result of a stored procedure.

Distilled essence of problem:
I have containers

ContainerId - uint, auto-increment
Parent - uint, may be null, is a root container if not null
RootContainer - uint, may be null (implies is root)
Location - uint, may be null, only roots may have not null values.

I also have items

ItemId - uint, auto-increment
ContainedIn - uint, may be null
Location - uint, may be null (if not null, then ContainedIn must be null)

The query I want to hide is a union with 2 parts:
 The first part is all the items in a given location (with columns defined to be values that imply these items are in a container with ID0 named the location name)

These are then unioned with all the containers in the given location (which through joins contain all the items in those containers)

So I get a large list of items (with some container info too) - I then want to process these items.

    SELECT
      0 AS `ContainerId`,
      NULL AS `ParentContainer`,
      NULL AS `RootContainer`,
      (SELECT `Name` FROM LocationList WHERE LocationId=1) AS `ConName`,
      ITEM.*
    FROM ItemList ITEM

    WHERE
      ITEM.CurrentLocation=1
      AND
      1=1 #WHATEVER

UNION
    SELECT
      CON.ContainerId,
      IF(CON.ParentContainer IS NULL,0,CON.ParentContainer) AS `ParentContainer`,
      IF(CON.`RootContainer` IS NULL, 0, CON.RootContainer) AS `RootContainer`,
      CON.Name AS `ConName`,
      ITEM.*
    FROM ContainerList CON
      LEFT JOIN ItemList ITEM ON(ITEM.ContainedIn = CON.ContainerId)
    WHERE
      CON.CurrentLocation = 1
      OR
      CON.RootContainer = ANY(
            SELECT ContainerId FROM ContainerList WHERE CurrentLocation=1)

This query did work, it's a chopped down version of the actual one I want to hide.

As you can see I create a 'fake' root node of id 0 which has the name of the location, I parent root containers to this node.

The type is unsigned, so I was lucky to have a 0, I cannot have an id for each location Id (I could use CONCAT and some magic to do this, but I risk the optimiser not following along)

I'm not sure what I should do to go ahead. My options are:

Repeat myself a lot
Write a view which has an extra column of the form LocationId:ContainerId - this way I can still derive from our generic tree data reader. I risk the optimiser not following along properly.
Create 2 views and union the result

I've read this a few times, I hope it actually does make sense. I know 'what should I do' questions are quite soft; I doubt I'm the first to encounter this problem, so I ask what others have done.


texts: SQL Server 2008 and constant expressions

texts:
If I have the following scenario, would Replace be evaluated a single time?

DECLARE @text nvarchar(32) = 'This is a test field'

SELECT textField FROM Table
WHERE Id = @idICareAbout AND textField = REPLACE(@text, ' ', '')

I would hope that it would, but I tend to be nervous about it and do this instead:

DECLARE @text nvarchar(32)
Select @text = REPLACE('This is a test field', ' ', '')

SELECT textField FROM Table
WHERE Id = @idICareAbout AND textField = @text

Of course replace here is just an example, and I'm sure this second variant is probably clearer and safer, but occasionally when I'm over optimizing, I think "What if I didn't have to do the replace at all, because the first part of the WHERE expression evaluates to false". Normally this would be such a micro-optimization it's silly to even think about but there have been a few times where the scenario is such that it may make a not miniscule difference. (Many more where clause parameters and maybe a bunch of HASHBYTES of parameters passed in.)

So I'm just curious how SQL server handles it.


texts: Is it possible to have the transaction log file write to a disk on a remote machine?

texts:
For the SQL server transaction log file, it is possible to set its target file to a disk on a remote machine?

I am working on a BI project.  In its current iteration, when the data warehouse loading processes run, the transaction log can balloon (>100 GB) in size before it has a chance to truncate.  There isn't any room to add more disks to this server, and future data loading operations will involve substantially more data and so I don't think there will be enough disk space for the log.  But we have a development server in the same rack with 900+ GB free and I was thinking we could use that free disk space so that bigger loads will work.


texts:
I'd like to ask advice on setting up a reporting server please.

We have a SQL Server (on a VM) that handles operations. (SQLOps)
This primary SQL server instance has a secondary Log Shipping instance (in another city)

We have a second VM now idle, which I have to set up as a Reporting server.

I have set up log shipping before in my life, but I have no idea how to approach the Reporting server now.

How do I set up the new SQL Server instance (SQLReporting).

How do I get to update the data in the 2nd SQL Server instance?
Can I send through updates every 15 minutes, like with Log shipping?

I never want data from SQL Reporting to update the original SQLOps.

Are snapshots the only option?


texts: SQL Server 2016 Install - R Server and R Services Install Failed

texts:
I just installed SQL Server 2016 Developer and included R Server and R Services to get installed as well. Everything installed correctly except the R Server and R Services. The log file contained this:

2016-07-29T17:07:39 INFO    Command invoked: D:\x64\RSetup.exe /install /component SRS /version 8.0.3 /language 1033 /destdir C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES /logfile C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\20160729_125230\RSetup.log /instance MSSQLSERVER
2016-07-29T17:07:39 INFO    Using default cache dir: C:\Users\fs103799\AppData\Local\Temp\
2016-07-29T17:07:39 INFO    Backing up source dir: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\ => C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES_63975911-f850-4ad1-bf7c-21adf0f9ba6d
2016-07-29T17:07:39 ERROR   Error renaming source dir: Access to the path 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\' is denied.
2016-07-29T17:07:39 INFO    Exiting with code 5

I have no idea why the access to the path was denied. The path exists and there are files there. So the install process was able to store files there, but then couldn't access the folder.


texts: Is it possible to only display the largest calculated table column?

texts:
Below is a code to sum all rows in each column where the value of each column c(x) equals 1. It gives me six columns containing each calculated sum. Is it possible to only display the largest calculated column (c4 as given by the image below) in a neat way?

SELECT DISTINCT
       (SELECT SUM(`vote_candidate_1`)
          FROM `votes`
         WHERE `vote_candidate_1` = 1) c1
     , (SELECT SUM(`vote_candidate_2`)
          FROM `votes`
         WHERE `vote_candidate_2` = 1) c2
     , (SELECT SUM(`vote_candidate_3`)
          FROM `votes`
         WHERE `vote_candidate_3` = 1) c3
     , (SELECT SUM(`vote_candidate_4`)
          FROM `votes`
         WHERE `vote_candidate_4` = 1) c4
     , (SELECT SUM(`vote_candidate_5`)
          FROM `votes`
         WHERE `vote_candidate_5` = 1) c5
     , (SELECT SUM(`vote_candidate_6`)
          FROM `votes`
         WHERE `vote_candidate_6` = 1) c6
  FROM `votes`;


texts: How to prevent data loss during intra-site failover

texts:
We are looking to build a MS SQL server (either SQL server 2016 standard or enterprise edition) solution but are having some troubles. This post can be lengthy and if you think it is TLDR, our question is simply as:

Given two physical sites, each site has two SQL servers with requirement that at any given time application is able to access one of its local SQL server instance to write/read data. Then the data need to be synchronized cross site and all four servers will contain synchronized data.  Which SQL server’s HA solution or replication solution or combination of both should we pick to achieve such result?

Starting here I will be trying to explain what we have tinkered with and the issues we are running into. We use SQL server 2016 developer edition for our testing. Note that it is treated as if it is a standard edition as of now.

Set up:

Two SQL servers per physical site are joined together in a Basic High Availability group with one being primary and one being passive secondary. Then we wrote some T-SQL script based on this link to set up bidirectional replications on the database level. For simplicity, let’s say we have Server 1 and Server 2 on physical site A and Server 3 and Server 4 on physical site B. Initially, Server 1 is primary on site A and Server 3 is primary on site B. The two are also syncing with each other which means whatever the change is written to Server 1’s database is replicated over to Server 3’s database and vice versa.

Issue:

So after the set up, we tinkered with intra-site fail over. For example, manually failing over from Server 1 to Server 2 at site A. what we observed is that during this failover, if any data in a table was modified or added from site A or site B. After we re-run our script to clean up orphaned replication and re-setup bidirectional transactional replication between Server 1 and Server 3, the data added/modified during intra-site failover will not be synced. For example, both Server 1 and Server 3 touched a table. Server 1 inserted a new entry Test 2 into the table and Server 3 inserted a new entry Test3 into the table.

Server 1 table view

Aaa    Bbb
Test1  Test1
Test2  Test2

Server 3 table view

Aaa     Bbb
Test1  Test1
Test3  Test3

After the bidirectional replication script is executed on both Server 1 and Server 3, the added data is not synched. However, changing on Test 1 will be reflected on the other server. This makes sense because the script or bidirectional transactional replication doesn’t know how to deal with tables that have different data?

Essentially during intra-site failover, there is a short time window that both database can be accessed without bidirectional transactional replication yet. As a result, when the bidirectional transactional replication is set up again, two database won’t be fully synched with each other. This is not acceptable because requirement needs all four SQL server instance to be fully synched with one another. Pleas advice on which HA solution or combination of different solutions we should deploy our servers with.

Sorry for the lengthy post since I wanted to provide as much information as possible and thank you for your help.