texts: Merge two XML outputs to one from SQL query

texts:
I have following code developing 2 XML files but I would like them to be in one file itself with multiple Organization tag.

SELECT
LTRIM(RTRIM(c1.cptname)) as "orgHeader/orgCode",
LTRIM(RTRIM(c1.cptname)) as "orgHeader/longName",
LTRIM(RTRIM(c1.cptname)) as "orgHeader/shortName",
'Branch' as "orgRole",
(
SELECT system, extCode from
(
    Select 'a' AS 'system', LTRIM(RTRIM(c1.cptname)) AS
   'extCode'
    UNION ALL
    Select 'b' AS 'system', LTRIM(RTRIM(c1.cptname)) AS
   'extCode'
    UNION ALL
    Select 'Manual' AS 'system', LTRIM(RTRIM(c1.cptname)) AS
   'extCode'
) a
FOR XML PATH('externalCode'), TYPE, ELEMENTS,Root('externalCodes')
)
from  cpt c1
where cptleagname like '%abc%'
and cptcod IN (select cptcod from pf_map
where pf IN ('abc','abc-jp')
and stat = 'a')

FOR XML PATH('organisation'), root ('collection')

SELECT
LTRIM(RTRIM(c1.cptname))+'.' as "orgHeader/orgCode",
LTRIM(RTRIM(c1.cptname))+'.' as "orgHeader/longName",
LTRIM(RTRIM(c1.cptname))+'.' as "orgHeader/shortName",
(
SELECT orgRole from
(
    Select 'Coll' AS 'orgRole'
    UNION ALL
    Select 'Lega' AS 'orgRole'
) a
FOR XML PATH(''), TYPE, ELEMENTS
)
from  cpt c1
where cptleagname like '%abc%'
and cptcod IN (select cptcod from pf_map
where pf IN ('abc','abc-jp')
and stat = 'a')

FOR XML PATH('organisation'), root ('collection')

When I run this It produces 2 XML files as output as below.

<collection>
 <organisation>
   <orgHeader>
    <orgCode>abc</orgCode>
    <longName>abc</longName>
    <shortName>abc</shortName>
   </orgHeader>
   <orgRole>Branch</orgRole>
   <externalCodes>
    <externalCode>
      <system>a</system>
      <extCode>abc</extCode>
    </externalCode>
    <externalCode>
      <system>b</system>
      <extCode>abc</extCode>
    </externalCode>
    <externalCode>
      <system>Manual</system>
      <extCode>abc</extCode>
    </externalCode>
  </externalCodes>
</organisation>

And

<collection>
 <organisation>
   <orgHeader>
     <orgCode>abc.</orgCode>
     <longName>abc.</longName>
     <shortName>abc.</shortName>
   </orgHeader>
   <orgRole>Coll</orgRole>
   <orgRole>Leg</orgRole>
 </organisation>
</collection>

While I want to add it under single file to produce results as Collection as root and merge these files in organization tag as follows.

 <collection>
  <organisation>
   <orgHeader>
    <orgCode>abc</orgCode>
    <longName>abc</longName>
    <shortName>abc</shortName>
   </orgHeader>
   <orgRole>Branch</orgRole>
   <externalCodes>
    <externalCode>
      <system>a</system>
      <extCode>abc</extCode>
    </externalCode>
    <externalCode>
      <system>b</system>
      <extCode>abc</extCode>
    </externalCode>
    <externalCode>
      <system>Manual</system>
      <extCode>abc</extCode>
    </externalCode>
   </externalCodes>
  </organisation>
  <organisation>
   <orgHeader>
     <orgCode>abc.</orgCode>
     <longName>abc.</longName>
     <shortName>abc.</shortName>
   </orgHeader>
   <orgRole>Coll</orgRole>
   <orgRole>Leg</orgRole>
 </organisation>
</collection>

Please comment below if any other information is required. Thanks.


texts: SQL query result table to an HTML table for email

texts:
please help me to figure this out. I need to send sql query results as html table. The challenge is that columns in this table are dynamic (most recent 5 dates). How can I replace literal column names in CAST function with actual dynamic column names? Just replacing with variable name (ex. Activity -> @header1) does not do the magic.

DECLARE @tableHTML  NVARCHAR(MAX),
                  @header1 NVARCHAR(MAX), @header2 NVARCHAR(MAX), @header3 NVARCHAR(MAX),
                  @header4 NVARCHAR(MAX), @header5 NVARCHAR(MAX), @header6 NVARCHAR(MAX), @test NVARCHAR(MAX);
set @header1 = (SELECT column_name FROM [sample_db].[INFORMATION_SCHEMA].[COLUMNS] where TABLE_NAME = 'sample_table' AND ORDINAL_POSITION =1);
set @header2 = (SELECT column_name FROM [sample_db].[INFORMATION_SCHEMA].[COLUMNS] where TABLE_NAME = 'sample_table' AND ORDINAL_POSITION =2);
set @header3 = (SELECT column_name FROM [sample_db].[INFORMATION_SCHEMA].[COLUMNS] where TABLE_NAME = 'sample_table' AND ORDINAL_POSITION =3);
set @header4 = (SELECT column_name FROM [sample_db].[INFORMATION_SCHEMA].[COLUMNS] where TABLE_NAME = 'sample_table' AND ORDINAL_POSITION =4);
set @header5 = (SELECT column_name FROM [sample_db].[INFORMATION_SCHEMA].[COLUMNS] where TABLE_NAME = 'sample_table' AND ORDINAL_POSITION =5);
set @header6 = (SELECT column_name FROM [sample_db].[INFORMATION_SCHEMA].[COLUMNS] where TABLE_NAME = 'sample_table' AND ORDINAL_POSITION =6);
set @test = "sample_table" + '.' + @header1;
SET @tableHTML =
    N'
Sample Report
' +
    N'
' +
    N'
'  + @header1 + '	' +
    N'' + @header2 + '	' +
    N'' + @header3 + '	' +
    N'' + @header4 + '	' +
    N'' + @header5 + '	'+
    N'' + @header6 + '
' +
    CAST ( ( SELECT td = Activity,       '',
                    td = December_3, '',
                    td = December_2, '',
                    td = December_1, '',
                    td = December_30, '',
                    td = December_29
              FROM [sample_db].[dbo].[sample_table]
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) )  ;
EXEC msdb.dbo.sp_send_dbmail
  @profile_name='111C SQL DBA',
  @recipients='tester@mail.com',
  @subject='Sample Subject',
  @body = @tableHTML,
   @body_format = 'HTML';


texts: DBMS_REDEFINITION vs EXCHANGE PARTITION in oracle

texts: In order to partition an existing non-partitioned table it is possible to use either the EXCHANGE PARTITION or DBMS_REDEFINITION. How to decide on which of those possibilities to choose for table partitioning. Does this depend on how many data reside in the table? Is one operation safer than the other?


texts: Turn on multi-threading in Oracle for ETL purposes

texts:
As you can see from this question, I was able to configure multi-threading in SQL Server and build a workflow in Informatica to multi-thread a job to move data faster into SQL Server.

Multi-thread Informatica connections to use different processors on target database server

Now I'd like to do the same thing in Oracle.  Does anyone know the feature in Oracle that's comparable to the "Max Degree of Parallelism" setting in SQL Server?  I'd like to move data on each core of the server in parallel.

EDIT:

I'm strictly dealing with INSERT statements.  So DML.


texts: How could one monitor MySQL replication?

texts:
I am not a DBA, but I am interested in knowing how replication monitoring is done on MySQL severs.

In order to know if a slave is in sync with its master, one would typically check Seconds_Behind_Master, Slave_SQL_Running, Slave_IO_Running.

There are tools available like mk-table-checksum to verify replication integrity.

My question is, how else would a DBA like to monitor replication?


texts: Best practice for testing database disaster recovery

texts: This is regarding database disaster recovery testing. The production server has 20+ databases. I'm developing procedures for regular testing of disaster recovery and am wondering what is the best practice, testing ALL databases or is one sufficient?


texts: How to convert decimal number to IP address?

texts:
I need a function or query to convert a decimal number to ann IP address.

Presently my ip_address column looks like this:

select  ip_address from test_ip;

    ip_address

     173015430
     173015318
     927815779

I need the result like this:

         ip_address
        10.80.1.134
        10.80.1.22
        55.77.88.99

I can do this in MySQL by using INET_NTOA function. How can I accomplish this in Oracle?


texts: Defining a primary key: Customer numbers are unique only within a country but may be duplicated across different countries

texts:
I am facing the following problem:

I have a table with customer information like the customer number, i.e. customerID set up as the primary key, and, besides a number of other data, also a country value in the form of a country code, countryID.

However, customer data from other countries should also be imported into this table. Although the customer numbers are unique within a country, they are not cross-country. Customer numbers should not be changed for organizational reasons.

How can a key still be ensured for each customer in this table?


texts: Updating a column in one table based on matches with column in another table

texts: I have 2 tables say table1 containing says 3 columns col1, col2 and col3 and table2 which contains col1 and col2. I want to set col3 = 'some value' where table1.col1 = table2.col1 and table1.col2 = table2.col2. I found out that SQLite3 does not support join in updates like postgresql (with which I am familiar). How to achieve this?


texts:
I'm having trouble with a trigger which I can't get my head around.

I have a table called TABLE_A with an AFTER INSERT trigger that executes a procedure. The procedure basically does the following three steps….

Process the data in the TABLE_A row to create a new row in TABLE_B.
Updates the TABLE_A row to indicate it’s been processed.
Updates a summary TABLE_C to increment a record count.

It implements this with three sequential SQL statements. Although I've simplified the first statement, there are no intermediate steps here, just three bits of SQL one after the other:-

--Insert new record that includes the record ID of the source.
--NOTE - recIdB is an autogenerated ID from a database sequence.
INSERT INTO TABLE_B(idA, lots_of_other_fields….
VALUES (new.recordIdA, lots_of_other_values…..
RETURNING recIdB INTO recentRecIdB;

--Update original record in TABLE_A to confirm it’s been processed.
UPDATE TABLE_A SET processed = true WHERE recIdA = new.recIdA;

--Update the summary table with the new record identifier.
UPDATE TABLE_C SET newRecords = (newRecords +1), recentId = recentRecIdB;

The trigger normally runs happily for hours on end processing millions of newly inserted records. Occasionally it will go squiffy and simply not execute the 2nd two update statements.

I know that the insert into TABLE_B has worked, because I can see the record with the unique ID from TABLE_A.
I know that UPDATE TABLE_A hasn't executed (not successfully anyway) because the 'processed' flag isn't set.
I know that the trigger can't have failed between the INSERT and the 1st UPDATE because there is no code between the two statements i.e. nothing to fail.
I can't see how UPDATE TABLE_A can possibly fail because it's so damn simple! there are no calculations to go wrong, a single row is being updated based on a unique identifier provided by the database itself - not a value I've calculated.

My only explanation for UPDATE TABLE_A not being executed is that the code failed before it got there. However I don't see how this can be the case because if the preceding INSERT INTO TABLE_B had failed then there would be no new record in TABLE_B.

My final guess is that first part of INSERT INTO TABLE_B is working (hence the new record I can see afterward) but the final part (RETURNING recIdB INTO recentRecIdB;) has failed. Is this possible? If the trigger bombs-out at this point it would explain what I'm seeing - a new record in TABLE_B but no updated record in TABLE_A

The following may also be relevant.

The code has been working (unchanged) for years without problem. The
code still works 90% of the time but now experiences periods of a few
hours at a time when it never gets past the 1st statement
TABLE_A and TABLE_B both use a record ID generated from the database
as follows:-

.

recida integer NOT NULL DEFAULT nextval('table_a_recida_seq'::regclass)
recidb integer NOT NULL DEFAULT nextval('table_b_recidb_seq'::regclass)

There are no other processes running on the database. The process adding records to TABLE_A is doing nothing else, this trigger is the only running code.
The two updates UPDATE TABLE_B and UPDATE TABLE_C either both work or
both fail together - hence my assumption that the error occurs
between INSERT TABLE_B and the 1st update.

The final thing that may be relevant is that the record identifiers used by TABLE_A and TABLE_B (recIdA and recIdB shown above) are both INTEGERS and are both over 2 billion. However, they are both still several million records away from the maximum INTEGER value of 2,147,483,647. (this is a current issue and I'll be updating to BIGINT asap)

Any suggestions? I'm already looking into how to raise messages to help de-bug triggers but any ideas would be appreciated, could the INSERT statement be inserting a record correctly and then failing at RETURNING X INTO Y? Could the problem be down to the sequences approaching maximum size?

UPDATE - The source records are being inserted in blocks of INSERTS involving a couple of hundred records at a time. (several hundred INSERTS per transaction)


texts: How to convert UTF8 general in UTF8 unicode?

texts:
Short:
My tables have the collation utf8mb4_unicode_ci, but the data itself is utf8mb4_general_ci and now I like to know how I can convert it into unicode?

Long:
I used this query to convert all my latin tables to utf8:

ALTER TABLE ' . $table_name . ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci

Then I reminded a security hole in wordpress regarding truncated blog comments through bypassing the 3 bytes limit and I decided to convert all tables to utf8mb4:

ALTER TABLE ' . $table_name . ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

Everything seemed to work, but after a while I received through my own security check, that some content has been truncated:

Before:
Also mit Nummer/Abe?
Was wird�&euro;�s denn?
ISO-8859-1

After:
Also mit Nummer/Abe?
Was wird
UTF-8

As you can see someone was able to send a latin/iso quotation mark and mysql truncated the string.

So I checked my mysql collaction settings:

SHOW variables WHERE variable_name LIKE '%coll%' OR variable_name LIKE '%char%'

Result:

character_set_client: utf8mb4
character_set_connection: utf8mb4
character_set_database: utf8mb4
character_set_filesystem: binary
character_set_results: utf8mb4
character_set_server: latin1
character_set_system: utf8
character_sets_dir: /usr/share/mysql/charsets/
collation_connection: utf8mb4_general_ci
collation_database: utf8mb4_unicode_ci
collation_server: latin1_swedish_ci

As I'm not able to change server collations I asked my hosting company and they said:

  Settings on the server will be overwritten by your database settings,
  so no change is necessary. But you should set
  collation_connection and collation_database to the same value.

I tested a little bit around and found out that this sets collation_connection to utf8mb4_general_ci:

mysqli_set_charset('utf8mb4', $this->db_connect_id);

And if I use this instead, it sets it to utf8mb4_unicode_ci:

mysql_query('SET collation_connection = @@collation_database;');

Now I could use this setting, but then all existing special chars are scrambled. Only new posts will be saved and displayed correctly. This means my tables have the correct utf8mb4_unicode_ci collation, but the data itself seems to be formatted as utf8mb4_general_ci. I do not know why, maybe because the connection itself while calling CONVERT TO CHARACTER SET was set to utf8mb4_general_ci?!

Anyway... So how can I solve this issue? How can I convert the utf8mb4_general_ci data into utf8mb4_unicode_ci so I'm able to use utf8mb4_unicode_ci as collation_connection?


texts: Error While restoring DB2 db from AIX to AIX server?

texts:
I am required to migrate 1 database  One AIX server to another AIX server.
To achieve, I took backup from 1 machine and followed below steps

1st Command I tried is as  following.

db2 restore db DBNAME FROM /home/LOCATION taken at 20130912184605 into DBNAME1 REDIRECT generate script DBNAME1.sql

Which generated 1 sql file. Then I tired to run below command.

db2 -tvf DBNAME1.sql

I am getting following error

UPDATE COMMAND OPTIONS USING S ON Z ON DBNAME_NODE0000.out V ON
DB21005E  An error occurred while accessing the file "DBNAME_NODE0000.out".


texts: I can´t change innodb_buffer_pool_instances

texts:
I recently updated my mariadb database to 10.5.5 and I can't set innodb_buffer_pool_instances
innodb_log_file_size = 3G
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 12

But when
    MariaDB [(none)]> SHOW VARIABLES LIKE "%innodb_buffer_pool_%";
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 12884901888    |
+-------------------------------------+----------------+

Do I need other vars to configure pool instances? Which?
Thank you very much for any ideas.


texts:
I am looking into automating my SSAS cube builds using SSIS and/or scripts and have a few questions.

I have a cube with yearly partitions and a Date Dim that has attribute indicators by day for "last 30 days", "last 60 days", etc. Obviously as the current date changes, the indicators at the day level will change as well. I realize I will have to "process full" the Date Dim daily, but will I have to process a previous year partition whose date range falls into the "last 60 days"? And then keep on processing that partition until "day 61" of the current year?
I have a Postal Codes Dim with over 2 million entries that I intend to update only once a year. After I load the new postal codes and fully process the cube, can I remove the Postal Code Dim from the dimension update step once I start to process daily again (i.e., processing only the last partition)?
I have a cube with 4 measure groups, but yearly partitions on just one measure group as the 3 others do not have nearly the number of records. If I process the last yearly measure group in a script, do I then need to "process full" on the 3 other measure groups or on the 3 other single partitions in those associated measure groups? I plan to use the Analysis Services Processing Task in SSIS for that step, but not sure whether to include partitions or measure groups.


texts: Browse for Servers does not display local instance

texts:
I just installed SQL Server and SQL Server Management Studio but it doesn't show anything in servers list and I don't know how to connect to it.

This shows that I have installed SQL Server:

But in this image you can see that my Browse for Servers dialog is empty:

Network servers is empty too.


texts: How can I reduce the Tuple Mover's disk I/O impact for mergeouts?

texts:
I have a cluster with a constant trickle load of new data fed by a stream of large batch COPY statements and also simultaneously support interactive read-queries against the most recently added data, i.e. time series for chart data.

How can I control the Tuple Mover so that it spreads out its work over a longer period of time, to reduce the insane disk I/O it periodically causes which slows down read-query performance to horrible levels.  This data is partitioned on a date column, so every 24 hours precisely (GMT) there is a flurry of disk I/O due to partition mini-ROS to ROS consolidation.  I've set ActivePartitions to 2, long ago, which helped reduce the problem but it's still a significant slowdown while the mergout is hogging the spindles.

It seems like the resource pool settings ought to help, but I can't find a setting that has an effect.  Any suggestions?


texts: Should I use Transactional Replication here?

texts: I have a database on SQL Server A & one on SQL Server B. (SQL 2017 w/ enterprise) They share similar objects and need to be kept fairly up to date. Both will require changes to be passed to each of them, however these changes will be different. Should I setup transactional replication to go from server A to server B and then setup another publication to go back from server B to server A with the different criteria? I've never had to replicate different items from the replica. So that is why I am unsure here. Any comments are appreciated. I don't think Peer-to-Peer is the solution here as some objects will be different and/or not needed to be replicated between the 2 db's.


texts:
I have a table with two main columns, id1 and id2:

drop table if exists pairs;
create table pairs (id1 int, id2 int);
insert into pairs values
    (1,2), (1,3), (3,1), (2,4), (5,4), (3,6), (7,8);

I want to select rows which have these pairs [(1,2),(5,4),(3,1)]. This array could have more than 500 items, so how I can write a select query to select those rows?
I am using 'Netezza' database but I need to find the logic to do this, I can figure out how to implement this logic in my database engine.


texts: Creating a case-insensitive and accent/diacritics insensitive search on a field

texts:
I would like to know if it makes sense to create an index combining two functions while using Full Text Search: lower(name) and f_unaccent(name) Where f_unaccent is just my wrapper to make unaccent function immutable.

I do have an index working on: f_unaccent(name) using varchar_pattern_ops. My question is: would an index that combines lower and unaccent functions be triggered by full_text_search lower(f_unaccent(name)).

I don't know if the lower function would be useful working with Full Text Search algorithms.


texts:
I have a configuration table:

Table: refdatecol

id           int(11)
startdatecol varchar(100)
enddatecol   varchar(100)
datecolTable varchar(100)

This table holds the user configured column names and table names.
I am building a query run time using the data from refdatecol and updating another table project_wbs. I am using dynamic SQL.
This works fine inside the procedure.

Problem is: I want to fetch the current date values into the project_wbs on insert OR update. Naturally, I thought of using trigger. But triggers do not support dynamic SQL in MySQL.

So I thought of calling the procedure from the trigger. Then I learnt it is a strictly no-no.

Is there any way I can achieve this?

I am also open to considering a complete redesign of this user-configurable dates

Update

Here is the stored procedure:

create  procedure sp_project_date (_id int)
begin

set @_startdate ='';
set @_enddate ='';
set @_projectId = (select project_wbs.proj_projectId
                   from   project_wbs project_wbs
                   where  id = _id);

set @q = (select concat('select ', startdatecol, ' , ', enddatecol ,
                        ' into @_startdate, @_enddate  from ' , datecolTable ,
                        ' where project_ProjectId = ' , @_projectId) as 'query'
          from refdatecol
          where id = (select p.cogsDateId
                      from   project p
                      where  p.projectId = @_projectId)
          );

prepare stmt from @q;
execute stmt;

UPDATE project_wbs
SET    project_startdate = @_startdate,
       project_enddate = @_enddate
WHERE  id = _id;

DEALLOCATE PREPARE stmt;


texts:
We have a task to copy 2TB of DB files/backups over LAN in a most efficient and speedy way. We are brainstorming amongst our team to come up with a best solution.

These files are multiple backups and compression is already used.

Can someone advise the best way to do this?


texts: Why do Statistic recommendations disappear from DB Tuning Advisor after implementing recommended indexes?

texts:
Why is it, when analyzing a query through Database Engine Tuning Advisor, statistic recommendations disappear after some or all of the suggested indexes are implemented?

For example, Query X suggests 5 indexes, and 5 sets of stats.  After I implement the indexes I rerun the analysis (just curiously seeing what the efficiency improvement comes from, indexes or stats) and notice that not only are the indexes gone from the recommendations, but the statistics are also.

Obviously, these aren't the stats that are inherently a part of the index that was built.


texts: Backing up Master-Master replicated MySQL databases

texts: I have two MySQL 5.5 databases which replicate each other. What's the best way to back these machines up? I would like to prioritise speed/ease of restoration.  Should I add extra slaves to both machines and also replicate there, and then periodically stop those slaves and copy the data somehow? Is a mysqldump enough, or should I be copying the whole data directory somehow?


texts: SQL Server Reporting Service 2014 installation Fail?

texts:
I have an issue while trying to install SQL Server 2014 (Reporting Service only).

During the Installation process i get the following error and the installation ends with Failed to install the followings:

Reporting Service - Native

Error:

TITLE: Microsoft SQL Server 2014 Setup

The following error has occurred:

Error writing to file: Microsoft.SqlServer.Rmo.dll.   Verify that you have access to that directory.

Click 'Retry' to retry the failed action, or click 'Cancel' to cancel this action and continue setup.

I have Administrative  privilege on the server with full control of all folders\paths.

Please if you have an answer for this ,share it with me !

Thanks
Osama Waly


texts: Oracle Materialized View - Is it Safe to Use Only ROWID as Record Identifier?

texts:
this is a question related to Oracle 12c.

I'm currently trying to create MVs as data sources for Golden Gate replication. We need fast refresh because We have to replicate 60,000 rows + at one time and deleting and reinserting all rows seem to be inefficient.

The base tables have unique columns which act as composite primary keys but there's no primary key constraint defined in them. They're peoplesoft tables so We prefer not to add pk constraints if possible, as They're used in so many applications.

I have built the MVs using only WITH ROWID clause for the materialized view logs and They seem to be working fine. But I don't know if it's a good practice to do so, should I add PK constraints on all the master tables instead? or maybe there's another way?

Feedbacks are appreciated. Thanks.


texts: Only SQL Server Enterprise Edition supports compression - how to work around that?

texts:
I have a script that needs to create a temp table and in that temp table I create an index.

I noticed in some of my servers, the script would go on forever, and when checking from a different session what is currently running there was no clear message available.

after cancelling the query I get this message:

Message:
Cannot enable compression for object '#RADHE_sp_getsubscriptions__________________________________________________________________________________________0000000A923A'.
Only SQL Server Enterprise Edition supports compression.
Error: 7738
Severity: 16

this is the part of the script that is generating this error message:

IF object_id('TEMPDB..#RADHE_sp_getsubscriptions') IS NOT NULL
    DROP TABLE #RADHE_sp_getsubscriptions

    create table #RADHE_sp_getsubscriptions (
        publisher sysname NOT NULL,
        publisher_db sysname NOT NULL,
        publication sysname NOT NULL,
        replication_type int,
        subscription_type int,
        last_updated datetime,
        subscriberd_db sysname,
        update_mode int,
        last_sync_status int,
        last_sync_summary nvarchar(4000),
        last_sync_time datetime)

   CREATE CLUSTERED INDEX I_RADHE_sp_getsubscriptions
                       ON #RADHE_sp_getsubscriptions (publisher,Publisher_db,publication)
                     WITH (   PAD_INDEX = OFF
                            , FILLFACTOR = 100
                            , SORT_IN_TEMPDB = ON
                            , IGNORE_DUP_KEY = OFF
                            , STATISTICS_NORECOMPUTE = OFF
                            , ONLINE = OFF
                            , DATA_COMPRESSION=PAGE
                            , ALLOW_ROW_LOCKS = ON
                            , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]

Later on this is what I store in this table:

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

TRUNCATE TABLE #RADHE_sp_getsubscriptions

insert into #RADHE_sp_getsubscriptions
exec sp_MSenumsubscriptions

select * from #RADHE_sp_getsubscriptions

which gives this valuable info:


texts: Distribution Agent is failing in Transaction Replication

texts:
I am replicating large tables using transaction replication.

The problem I am facing while applying snapshot at subscriber.
Because of large table, active distribution transaction fill-up transaction log which cause to fail Distribution agent.

Action I have taken:

-CommitBatchSize -- 10000
-SubscriptionStreams -- 10000
-BCPBatchSize -- 10000

Yet, I am facing same issue.

Thanks!


texts:
I'm getting the below error across multiple servers (SQL Server 2005, 2008, 2008r2, 2012).  The SQL Servers are up to date with service packs as is the OS.  These are all SQL Server Standard edition.

The server's OS was patched this weekend and all the servers were rebooted. So I'm assuming this what they have in common.

Researching this has led me in too many directions and due to the large number of servers impacted (19), nothing is really making sense to me.

Any suggestions what is causing this?  And how do I deal with it?

The relevant error message:

The prelogin packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library. [CLIENT: 10.0.9.9]
Details:

- <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
- <System>
  <Provider Name="MSSQL$myserver" />
  <EventID Qualifiers="49152">17828</EventID>
  <Level>2</Level>
  <Task>4</Task>
  <Keywords>0x80000000000000</Keywords>
  <TimeCreated SystemTime="2018-10-29T06:14:58.000000000Z" />
  <EventRecordID>7435646</EventRecordID>
  <Channel>Application</Channel>
  <Computer>xxx.com</Computer>
  <Security />
  </System>
- <EventData>
  <Data>[CLIENT: 10.0.9.9]</Data>
  <Binary>A44500001400000014000000560049005400530051004C00300031005C004C0041004E005300570045004500500045005200000000000000</Binary>
  </EventData>
  </Event>


texts: My question is in regards to the scope of a transaction when using SSIS transactions.  If I have a sequence container and it is marked as "transaction required" and I have tasks in that sequence container that are marked as "transaction supported", if all the tasks in the container are finished successfully does SSIS commit the transaction when it is done with the sequence container or does the transaction live past the end of the sequence container?


texts:
CREATE PROCEDURE [sp_Test] (
     @param nvarchar(Max)
) AS BEGIN

DECLARE @Output nvarchar(Max) =
N’Select ‘ + @param

Select @output
Return

Intended Use
exec sp_test ‘5’
Returns “select 5”
Malicious Use
exec sp_test ‘5; drop database’
Returns(would be safe):
“select 5; drop database“
—-OR—-
Returns(not safe):
“select 5”
...but also actually dropping the database
MS SQL Server


texts: Okcupid.com (can find matches without accessing a database!?)

texts:
I'm doing a research project on a popular dating site called: OkCupid
I would like to talk about how the database is used.
After reading this explanation from the Co-Founder of the site, I became very confused.
The statements:

  when a user performs a match search on OkCupid, we have to do the
  following:

  -Retrieve (from somewhere not the DB) their question answers, their ideal match's answers, and their question importances. On average,
  each user on OkCupid has 250 questions answered in 3 parts.
  -Figure out who qualifies for their search, typically a very complicated query across a few million users. On average, tens of
  thousands of people qualify, and we need to figure out who they are
  without hitting the DB.

How do they do all this without consulting a database??
I'm utterly confused.
Here is a link to the post
I appreciate any explanations as to how they do things there


texts: What's the fastest way to migrate an Oracle database from Unix to Linux?

texts:
We have an Oracle database installed on AIX 7.1, and now we want to migrate our database instance to RedHat 6.2.

The RedHat server has Oracle 11.2 installed already.

What's the fastest way to migrate my database to our new Linux server?

I tried create a template (structure and data) on the AIX server, and then upload those template files to RedHat server, but dbca failed to create new database with this template, the error message says "bad file header".


texts:
I am using SQL server, and I have been looking closely at the concept of key lookups,

http://blog.sqlauthority.com/2009/10/07/sql-server-query-optimization-remove-bookmark-lookup-remove-rid-lookup-remove-key-lookup/

So if you have a key lookup you can create an index with the 'include' columns to cover the non index columns you have in the select statement.

For instance,

SELECT ID, FirstName FROM OneIndex WHERE City = 'Las Vegas'
GO

This index will include a key lookup,

CREATE NONCLUSTERED INDEX [IX_OneIndex_City] ON [dbo].[OneIndex]
(
[City] ASC
) ON [PRIMARY]
GO

But this one will remove the key lookup,

CREATE NONCLUSTERED INDEX [IX_OneIndex_Include] ON [dbo].[OneIndex]
(
City
) INCLUDE (FirstName,ID) ON [PRIMARY]
GO

I mean how much of an impact will this have on performance? The key lookup has an operator cost of 0.295969 (99%), but what does that really mean?

How do you know that you need the second index there, and at what point does it become the case that you are trying to add too many indexes and it is not worth it?

It seems to me that some queries can include index scans, key lookups, and still seem to perform very fast.


texts: Query to find all dates between two specified dates using plsql

texts:
I have a table with below structure
create table test_table
(
  customer_num      NUMBER,
  card_number       char(2),
  card_issue_date   DATE,
  card_expire_date  DATE
)

Example data is:
customer_num    |  card_number   |  card_issue_date | card_expire_date
--------------------------------------------------------------------------
  1             |  x             |  1/1/2019        | 1/4/2020
  1             |  y             |  1/1/2020        | 1/4/2020
  1             |  z             |  1/11/2020       | 1/15/2020
  1             |  b             |  1/11/2020       | 1/20/2020
  1             |  w             |  1/12/2020       | 1/17/2020
  1             |  a             |  1/18/2020       | 2/12/2020

For each customer's card, I need to have all dates between card_issue_date and card_expire_date.
So the output for the first record would be like this :
customer_num    |  card_number   |  card_issue_date
----------------------------------------------------
  1             |  x             |  1/1/2019
  1             |  x             |  1/2/2020
  1             |  x             |  1/3/2020
  1             |  x             |  1/4/2020

IS there any function or something else that can help me out with this?
Thanks in advance


texts: Is Primary Key Non Clustered recommended?

texts:
I am designing a database table.
Columns are RouteId ,Origin , Destination.

RouteId is a string, it is always unique but length will be more than 60 characters.

Since RouteId is unique, I am thinking to make it  Primary key and since the size is large (nvarchar(1000)) I am thinking to make it non clustered index.

My Question: Is it a best practice to make Primary Key (nVarchar(1000)) non clustered index? Will it have effect on performance? What is the best practice in this situation


texts:
While optimizing a report, I discovered that the time reported by TimeDataRetrieval in ExecutionLog2 was a couple of seconds longer than the query times shown in SQL Server Profiler for that report execution.

After doing a few more things to rule out that the queries were really not running slow, I set Component tracing to Verbose in ReportingServicesrService.exe.config:

<add name="Components" value="all:4" />

The SRRS logs now show an entry like this every time a report is run:

processing!ReportServer_0-2!18d0!01/30/2014-12:11:30:: v VERBOSE: Opening a connection for DataSource: PHData took 2044 ms.

The times are always over 2000 ms. I've tried running a report and then running it again a few seconds later, but it still logs a long delay.

SSRS and the SQL Server instance referenced by PHData are both on the same box.

I can't find the root cause of this. I even went so far as to create a console app to time how long it takes to open a connection to the SQL Server instance. The first run was about 1000 ms but subsequent runs were much quicker (presumably due to connection pooling in SQL server).

Are there any logs / tables I can look at to find out why this is happening?

New information:

I ran a report that gets its data from our other SQL Server box. Opening a connection took about ~ 2055 ms all three times I ran the report. This seems to eliminate the DB engine instance as the problem. I'm wondering if something is timing out in SSRS after 2000 ms.

New information #2:

I setup a shared data source for test purposes and played around with it. This setting seems to be causing my slowness:

I did some testing and was able to get to 2 ms or less using either of the following settings. I can jump through the necessary hoops to start using one of these in production, but it would be really nice to just find the underlying cause.


texts: Dynamic Select with error in where conditon

texts:
@P_SqLCommand NVARCHAR(100) OUTPUT, --output which will be return
    @P_SqlList NVARCHAR(30),-- columns that needs to be filtered out
    @P_Table NVARCHAR(30),--Table Name needs to be use in the select statment
    @P_Input NVARCHAR(50),--the filter variable
    @P_Num varchar(5)
SET @P_Input='@P_Input'

    SET @P_SqLCommand='SELECT Top '+ @P_Num +' '+@P_SqlList+'  FROM '+
    @P_TABLE +' where '+ @P_SqlList + '=' + @P_Input;

give me an error:

  Incorrect syntax near '='.

Any suggestions ?

I am using SQL Server. What am trying to do is to use stored procedure to for dynamic select statement and pass through by parameters, it is work fine till the where statement.

How I can treat @P_Input as character?


texts: How to prepend an element to a JSON array?

texts:
I have a plpgsql function in PostgreSQL 9.6, which at some point has a text variable _x (guaranteed to not be NULL) and another variable _y which is a JSONB array of text (which might be empty).

How can I prepend the value of _x to the array _y?

I was trying to do it like this:

SELECT jsonb_build_array(_x, VARIADIC array_agg(value))
FROM jsonb_array_elements_text(_y)
INTO _y;

which is wrong. I was trying to use info about VARIADIC from this answer.


texts: Do I need to enumerate the columns used in a materialized view when creating the MV log?

texts:
I'm currently creating materialized view logs on a whole bunch of tables in order to support materialized views with FAST REFRESH. The materialized views we're creating contain approx 30 columns from various tables. One of our fact tables contains about 15 columns that will be used in a materialized view.

Is it necessary to enumerate all of the columns in the base table that will be required in the MV when creating the MV Log?

CREATE MATERIALIZED VIEW LOG ON SCHEMA.TABLE_A
  WITH ROWID, PRIMARY KEY, SEQUENCE (COL1, COL2, COL3, ..., COL48)
  INCLUDING NEW VALUES;

The above is an example of how I'm currently creating the MV Log. I'll admit that the above is a result of trial and error, without a thorough understanding of how each component in the statement works (I understand most).

Under what circumstances is it necessary for me to define which columns should be included in the MV log?

Some materialized views will just be a join across multiple tables. Others will include aggregations, groupings, sums, etc.


texts: How do you stop Postgres from performing writes inside base/ directory during pg_dump?

texts:
We are dumping a 4TB postgres database using pg_dump. The source is on a copy-on-write snapshot filesystem, and the target is an external drive.

While the pg_dump is running we get about 4-5MB/sec writes on the filesystem where postgresql data is (iostat), and specifically inside the base/ directory.

Since the only query running on this DB is the COPY initiated from pg_dump which only reads data, who is writing data to postgres datafiles?

We need to find a way to make postgres stop writing because the source copy-on-write filesystem gets filled up before the dump is finished.

Postgresql version is 8.4, vacuum is not running (autovacuum = off).

UPDATE:
I recompiled pg_dump to use READ UNCOMMITTED transactions, but that didn't change anything.


texts: Getting the part for string using sql server query

texts:
I have rows like below

10470_AMK-4799__OFS_BROWSERTC
6038_DMUSER_DM.READ.REPLACEMENT
6043_DMUSER2__OFS_DM.OFS.SRC.VAL
10194_AMK-TC037__OFS_BROWSERTC
64948_AMK-TABLETUSER__OFS_TWS
10010_AMK-0357

I want to write a query which can produce the part of string like below (from the first underscore up to the second, both not included):

AMK-4799
DMUSER
DMUSER2
AMK-TC037
AMK-TABLETUSER
AMK-0357

I am using Microsoft SQL Server 2014.


texts:
I would like to see information on how many times has a table been fully scanned.
I was hoping for something like sys.dm_db_index_usage_stats but for table scans.
There is a method described at the blog keepitsimleandfast, but I would rather get all scans since server started.

What I am looking for is also similiar to pg_stat_all_tables.seq_scan in PostgreSQL.


texts: Is there a behavior change between SQL Server 2000 and SQL Server 2008 R2 OPTION (FAST 1)?

texts:
This is question 2 of 2 related to the FAST query hint. Background Information (as an aside, I'm not trying to garner more rep, I'm trying to be true to only asking one question per question).

Our legacy ERP was running on SQL Server 2000 EE and now it's on 2008 R2 EE. We're noticing many more blocked commands in the 2008R2 environment. The 2008R2 environment is running with trace flag 4199 and 1119.

I noticed a difference between the MSDN documentation on hints in 2000) vs. 2008R2 where the FAST hint is expanded to note that the result will continue to be processed once the specified n rows had been returned. Is this just a more robust document, is this a change in behavior for this hint through the years, or something different?


texts: ORA--07445 error during online Re-org process in 10.2.0.4 database

texts:
I was doing re-org of fragmented tables in an Oracle 10.2.0.4 production database:

sys@CLARITY> ALTER TABLE SOP_QUEUE ENABLE ROW MOVEMENT;
Table altered

sys@CLARITY> ALTER TABLE SOP_QUEUE SHRINK SPACE CASCADE;
ERROR at line 20:
ORA-03113: end-of-file on communication channel

When I checked the alert log, I found the following error:

ORA-07445: exception encountered: core dump [ktsk_get_prevl2()+217]
[SIGSEGV] [Address not mapped to object] [0x2E97317530] [] []

What could be the reason for this error, as I am unable to find a proper solution?


texts: List of database users: can you run C# Code in SQL Server Management Studio?

texts:
I'm trying to obtain a list of users associated with the AD Groups represented by the logons in my database. I understand from my research that the best way to do it is using this code:

{
Server srv = new Server();
//Iterate through each database and display.

foreach ( Database db in srv.Databases) {
   Console.WriteLine("========");
   Console.WriteLine("Login Mappings for the database: " + db.Name);
   Console.WriteLine(" ");
   //Run the EnumLoginMappings method and return details of database user-login mappings to a DataTable object variable.
   DataTable d;
   d = db.EnumLoginMappings();
   //Display the mapping information.
   foreach (DataRow r in d.Rows) {
      foreach (DataColumn c in r.Table.Columns) {
         Console.WriteLine(c.ColumnName + " = " + r[c]);
      }
      Console.WriteLine(" ");
   }
}
}

is this the best approach? Also, this code is in C#, how will I be able to run it in my SSMS?


texts: How long takes populating catalog in SQL Server 2008 R2 when update a row?

texts:
I have installed SQL Server 2008 R2 with FullText search feature installed.

I created a catalog and fulltext index on a table (let's say Table1). I used the following commands (sql queries):

USE MyDatabase
EXEC sp_fulltext_database 'enable'

CREATE FULLTEXT CATALOG [MY_CATALOG_] WITH ACCENT_SENSITIVITY = ON

CREATE FULLTEXT INDEX ON Table1 (Column1)
    KEY INDEX PK_KEY_TABLE1_
    ON MY_CATALOG_ WITH CHANGE_TRACKING AUTO;

WITH CHANGE_TRACKING AUTO; means auto-populate, right ?

When I use a stored procedure to update a row from Table1 and I use search query like:

SELECT * FROM Table1 WHERE CONTAINS(Column1,'modifiedName')

but no results were found.

I've checked the populate status is 0 (idle). I have to go in Sql Management studio and choose rebuild catalog. And then works (mean I found results what I expected)

My question: how long takes populating catalog in sql server ? My table (Table1) has 2000 rows.


texts:
We are troubleshooting a server that has high CPU utilization. After finding that the queries weren't really causing it, we started looking into compilations.

Performance Monitor is showing less than 50 Compilations/sec and less than 15 Recompilations/sec.

After running an XE session looking for compilations, we are seeing thousands of compilations per second.

This system is using triggers to audit changes. Most of the compilations are due to triggers. The triggers reference sys.dm_tran_active_transactions.

Our first thought was that maybe referencing a DMV in a trigger would cause it to compile each time, or maybe just this specific DMV would cause it. So I started testing that theory. It does compile each time, but I hadn't checked if a trigger compiles each time it is triggered when it doesn't reference the DMV and instead hardcodes a value. It was still compiling each time it got triggered. Dropping the trigger stops the compiles.

We are using sqlserver.query_pre_execution_showplan in an XE session to track the compilations. Why is there a discrepancy between that and the PerfMon counter?
Is it normal that you get a compilation event each time a trigger runs?

Repro script:

CREATE TABLE t1 (transaction_id int, Column2 varchar(100));
CREATE TABLE t2 (Column1 varchar(max), Column2 varchar(100));
GO

CREATE TRIGGER t2_ins
ON t2
AFTER INSERT
AS

INSERT INTO t1
SELECT (SELECT TOP 1 transaction_id FROM sys.dm_tran_active_transactions), Column2
FROM inserted;
GO

--Both of these show compilation events
INSERT INTO t2 VALUES ('row1', 'value1');
INSERT INTO t2 VALUES ('row2', 'value2');
GO

ALTER TRIGGER t2_ins
ON t2
AFTER INSERT
AS

INSERT INTO t1
SELECT 1000, Column2
FROM inserted;
GO

--Both of these show compilation events
INSERT INTO t2 VALUES ('row3', 'value3');
INSERT INTO t2 VALUES ('row4', 'value4');

DROP TRIGGER t2_ins;

--These do not show compilation events
INSERT INTO t2 VALUES ('row5', 'value5');
INSERT INTO t2 VALUES ('row6', 'value6');

DROP TABLE t1, t2;


texts: SQL Server Availability Groups between virtual server and a bare metal sql server

texts:
I currently have Mirroring set up between two production SQL Servers. One of these servers is a hardware SQL server (principal) and the mirror is a VM (on different server). I need to migrate these servers to Availability Groups (HA cluster NOT using shared storage).

None of this is my choice. I would personally prefer two VMs, or completely identical systems.

My question is: has anyone come across issues for AG/clustering between a hardware SQL Server and a VM SQL Server? Or is there any issues that you can imagine with that setup?

I have had no issues with Mirroring between a hardware & VM. As long as the servers are very similar (ie: same version of SQL Server, same RAM given, data files are named & stored in the same locations, etc). My concern is AG utilizes windows failover clustering - an unfamiliar territory for me - so I'd like to know if there's anything additional to look out for.


texts:
I am getting an "invalid object" warning error in the Management Studio query. Any query against the table works and it links correctly to another table. I am investigating an error that I'm getting in C#. See: https://stackoverflow.com/questions/14409788/is-not-a-foreign-key-column-and-cannot-be-used-here
This is the latest attempt to ferret out what may be going wrong. My linq queries against this table fail, but when I go to classic ADO.NET using SQL strings they work perfectly. Is it possible that this "invalid object" is causing C#/linq to kill the connection?
How do I get the table to stop the "invalid object" error. There are two tables, the following is the simplest one:

USE [Report]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblHoliDate](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Holiday] [date] NOT NULL,
    [Description] [varchar](50) NOT NULL,
    [DOW] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

It is nothing more than a table of holidays used to calculate the business days between two dates.
edit
Any reference to the table will do (which is why I didn't add a query).

SELECT * FROM tblHoliDate

Mouse over the table name to see the error.
The exact error is "invalid object name tblHolidate". This only occurs on mouse-over and the results of any SQL query against the table are complete and correct.


texts: log_min_duration_statement setting is ignored

texts:
I am running Postgresql 9.1 on Ubuntu. Exact Postgresql version is 9.1+129ubuntu1 as my package manager shows.

I have 2 databases that are actively in use and they are used from a remote server.

I wish to log queries that have a long execution time. So I set following parameters in /etc/postgresql/9.1/main/postgresql.conf file

log_min_duration_statement = 10000
log_statement = 'mod'

so Postgresql will log queries that takes longer than 10 seconds.

But when I reload the postgres configuration, Postgresql begins to log every query that fits log_statement value. That I set  duration to 100 seconds to be sure

log_min_duration_statement = 100000

But Postgresql keep logging every query that fits log_statement value, regardless of log_min_duration_statement value.

Setting log_statement to none seemed to stop logging.

Is there something that I missed about the configuration?


texts: MySQL #1054 - Unknown column 'masterdata_hyung_event.eventId' in 'on clause' on deep join tables

texts:
So, I have this query
SELECT * FROM hyung

LEFT JOIN masterdata_hyung_description ON hyung.descriptionId = masterdata_hyung_description.hyungDescriptionId

CROSS JOIN masterdata_hyung_description ON masterdata_hyung_description.eventId = masterdata_hyung_event.eventId

which return #1054 - Unknown column 'masterdata_hyung_event.eventId' in 'on clause'.
I have those three tables, they are (i think) is already related with foreign key. More less like:
table hyung fk with masterdata_hyung_description, and masterdata_hyung_description fk with masterdata_hyung_event. What I want is how can I relate all of them so I can grab data from masterdata_hyung_event from table hyung?. Thank you
EDIT: here's the link for dump sql. Thank you.


texts: Possible to tell what a query was in session I just killed?

texts:
Our website was briefly down and we determined it was due to SQL Server not responding. I opened the Activity Monitor in SSMS and killed sessions belonging to a couple of devs.

The site then came back but now I have no idea what the were doing, or whether it was just coincidental that the site came up the same time I killed their processes. I have talked to the devs but didn't find out anything useful.

I don't suppose SQL makes any record of the query that was running in a session that's just been killed?


texts: Best away to keep monthly records of loans

texts:
I'm trying to make a database to keep records of the amounts payed on a loan on a monthly basis.

I already have tables for the clients, accounts, products and loans:

create table product(
    id serial not null primary key,
    name varchar(50) not null,
    internal_code int not null
);

create table client(
    id serial not null primary key,
    name varchar(100) not null,
    number bigint not null

);

create table account(
    id serial not null primary key,
    number bigint not null,
    id_cliente int references client(id) not null
);

create table loan(
    id serial not null primary key,
    id_product int references product(id) not null,
    id_account int references account(id) not null,
    date_registry date not null,
    date_due date,
    amount_debt decimal not null
);

Every mouth a new record will be inserted in the loan table with a new amount_debt and a new date_registry (the date the client had payed he loan in that month), does it make sense to have a table with just those records having loan.id as FK on that new table?


texts:
I have a query which looks like:-

select ad.STUDENTMASTERID,ad.ADVERTISEMENTCODE, cod.POSTNAME,ad.CANDIDATENAMEINNEPALI,
ad.PAYMENTSTATUS, cd.TOTALFEE, cod.CLASSNAME
from CANDIDATEAPPEARAGAINSTADVTCODE ad
inner join CANDIDATEAPPEARTOADVTNUMBER cd on ad.STUDENTMASTERID = cd.STUDENTMASTERID
inner join ADVERTISEMENTCODE cod on cd.ADVERTISEMENTCODE = cod.ADVERTISEMENTCODE
WHERE ad.STUDENTMASTERID='415562' and ad.PAYMENTSTATUS='Pending' and
cod.STATUSOFLASTDATEFORMSUBMISSION='Live';

this query returns two rows

415562  15695-15698/2073-74 अमिन वा सो सरह  पुस्मिता कुमारी एेडी    Pending 300 रा.प.अनं द्वितीय
415562  15695-15698/2073-74 अमिन वा सो सरह  पुस्मिता कुमारी एेडी    Pending 150 रा.प.अनं द्वितीय

now i need to return only one row but the values of 6th column should be sum of each 6th columns of the each row.

for eg here:-

415562  15695-15698/2073-74 अमिन वा सो सरह  पुस्मिता कुमारी एेडी    Pending 450 रा.प.अनं द्वितीय


texts:
I want to create a synonyms for the user in my database. These are my user and my schema owner

User : Scoot
Schema Owner : kitty
I would like to create a synonmys "Scoot" for accessing all the table under my schema owner "kitty". User "Scoot" have assigned the correct access right to the table. This is my script but do it accept any wildcard since I have plenty of the table and don't want to make my script too long

 CREATE SYNONYM Scoot.* FOR Kitty.*;


texts: Should I use the deprecated MD5 function in SQL Server?

texts:
We'd like to use MD5 for our hashing function instead of SHA_256 but as of SQL Server 2016, MD5 is deprecated. We're using this for hashing (comparing which records have changed). We now have this dilemma of using of risking it by using this function or incurring storage and performance overhead of using SHA_256.  It's frustrating Microsoft decided to deprecate these functions even though they are still useful in certain scenarios.

This project isn't a critical component of the business. We'll likely go with SHA_256 but is this the right choice? Should new development always avoid deprecated functions?

For context - daily will be about 1-2 million upserting into a 400 million row table comparing hashbytes on the fly. about 30 columns wide

https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-2017

Choosing the right algorithm in HashBytes function


texts: How to choose a column of data from 2 days ago?

texts:
Currently, I am running the following SQL (in MySQL) query to return the column for records recorded at 12am on the current day, up to the current time.  So, if today is 2/3, and I run the query at 8pm, I will return all data recorded from 12:01am up to 8pm.

My question is; how do I retrieve yesterday's data?  I want the same column, except instead of today's date, I want yesterday.

SELECT column1
FROM db.table
WHERE TS >= CURDATE() AND TS < CURDATE() + INTERVAL 1 DAY;

My second question is, how would I take the same column, and find the records for the entire month, up to the CURDATE()?


texts: When no index is faster than having a clustered index?

texts:
I ran the same query, which involves joining two tables A and B, when B had no index at all and then added a PK clustered index on the ID column used for joining to it. The time elapsed when there was no index was actually faster?
No index result:
Table 'Table_A'. Scan count 5, logical reads 1764, ...
Table 'Table_B'. Scan count 10, logical reads 16976, ...
 SQL Server Execution Times:
   CPU time = 859 ms,  elapsed time = 269 ms.

Added PK clustered index to Table_B
Table 'Table_A'. Scan count 1, logical reads 445, ...
Table 'Table_B'. Scan count 2, logical reads 17026, ...
 SQL Server Execution Times:
   CPU time = 1046 ms,  elapsed time = 1053 ms.

Even though the IO numbers are higher when Table_B had no index, how could it be faster still? And how could a table with index slower than without? Any logical explanation?


texts: SQL Query; Admin Log System; Foreign Keys; Retriving multiple rows form different tables at once

texts:
Table A (users)

id     | name   | ip
52433    Yashas   1.1.1.1
512      Admin    5.6.7.8

Table B (admin_record)

id    | admin | time    | date    | reason | action
52433   512     2312352   1213213   Test     WARN

id is FOREIGN key

Table B keeps a log of all actions an administrator takes on his users. And the table A stores the account information of the users & administrators.

I am trying to display a list of actions taken by a administrators on a user.

I want to write a query which gets me all the rows of table B where id = fixed number.

Here is what I want to display:

"Administrator1 [Name]" took action on "User Name" for "[Note]" on XX/XX/XXXX.
"Administrator2 [Name]" took action on "User Name" for "[Note]" on XX/XX/XXXX.
"Administrator3 [Name]" took action on "User Name" for "[Note]" on XX/XX/XXXX.

All I have is the user's id using which I need to obtain all the entries in the log along with the administrator names. I need to obtain the admin's name, users name and all the contents of table B related to a particular user in one single query. If possible, I want the to change the field name(not literally change, but want the SQL server to send the data with new field names to separate admin name & user name) of the admin's name to admin_name and user's name as . I mean something like this

SELECT users.name AS admin_name WHERE users.id = %i
AND SELECT users.name AS user_name WHERE users.id = %i

(the query isn't correct,it us just an example to show what I am trying)

Progress so far:

 SELECT player.name, player.ip, admin.name, record.date, record.time, record.action, record.reason
FROM admin_record AS record
JOIN users AS player ON player.id = record.id
JOIN users AS admin ON admin.id = record.admin
WHERE player.id=%i

where admin_record is table B & users is table A.


texts: How to design relationships for variant data?

texts:
Help me define the table relationships for data with multiple structures. I need to track leads. Leads come from multiple sources. Each lead source has its own data structure. I need advice representing this in a relational database.

Each record in the TABLE "Products Sold List" (defined below) has a lead source. The leads can come from Organizations, Doctors, Company Office, and Company Employees. Here are the different types of data that constitute a lead.

TABLE Organization:
   - Name -- TEXT

TABLE Doctor:
   - NPI# -- INTEGER (unique for each doctor. I planned on using this as primary key)
   - FirstName -- TEXT
   - LastName -- TEXT

TABLE Company Office:
   - Location -- TEXT

TABLE Company Employee:
   - Department
      -- one of {Sales, Technician, QA}. Note that the employee is not tied to an office.
         Office leads are leads that come from misc office staff at the various locations.
   - FirstName
   - LastName

Here is TABLE Products Sold List:
   - [misc fields]
   - PrescribingPhysicianNPI > many-to-one > Doctor.NPI
      -- "doctor that prescribed the product for the patient"
   - SalesRep > many-to-one > [Company Employee].PrimaryKey
      -- "Sales Rep credited for the sale. Needed for commissions calculation"
   - LeadSource > ???

Misc information:

The company offers incentives to leads (affiliate commission) and incentives to sales staff (sales commission). Leads coming from Organizations and Company Offices don't get paid affiliate commission. I am considering merging TABLE Company Office into TABLE organization and using "Office -- " as the data to track different offices (i.e.: Organization.Name = "Office -- Cincinnati"). Bad idea? Issues?


texts:
So, this is now my third question on this topic.  I just keep finding more and more than i want to know and understand about how FULL recovery works.

We transitioned a database to FULL yesterday by taking a full backup at 7am.  Since then we take T-Log backups every hour and differentials nightly at 12:01am.  I'm generating some reports that can be automatically emailed to me (hooray automation!) and im noticing something funny with the LSN numbers.  Maybe I'm just not understanding how they work, but here goes.

Picture of query result displaying files, dates, sizes, LSNs, etc since we initiated full recovery yesterday:

I've highlighted the LSNs that are confusing to me and hopefully it makes it easier for you all to read.  Here are my questions:

The high LSN for the full backup is '308000000583700001', shouldn't the differential backups [Row 13 in picture] low LSN be that also in order for the log to chain correctly? Am I misunderstanding how the full and differential log files are synced to each other?
You'll notice that the differential, taken at 12:01am has a high LSN of '311000000469900001' but the T-Log backup taken at 1:00am has the exact same high LSN. Is this simply because there was no activity and the T-Log simply 'caught up' to match the LSN of the differential?

I'm racking my brain trying to make sure not only do i know how to implement a recovery strategy like this, but also that I completely understand exactly how the LSNs all sync up when generated.

Thanks again for all your help, as usual, on this one!

Here's the SQL used to pull this result, in case you would like to investigate using your system:

    SELECT TOP 48 CAST(s.database_name as char(10)) AS DB
    ,CAST(' ' as char(1)) + CAST(SUBSTRING(m.physical_device_name, 46, 40) as char(25)) AS FileName
    ,CAST(CAST(s.backup_size / 1000000 AS INT) AS CHAR(4)) AS MBSize
    ,CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS CHAR(4)) AS SecsTaken
    ,s.backup_start_date
    ,CAST(s.first_lsn AS CHAR(20)) AS first_lsn
    ,CAST(s.last_lsn AS CHAR(20)) AS last_lsn
    ,CASE s.[type]
        WHEN 'D'
            THEN 'Full'
        WHEN 'I'
            THEN 'Diff'
        WHEN 'L'
            THEN 'TLog'
        END AS BackupType
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
    AND backup_start_date > '2014-07-24 07:00:00'
ORDER BY backup_start_date DESC

From @Shankys response, here is what the result set looks like with database_backup_lsn and chockpoint_lsn added to the query.  As you can see, the backup_lsn is the same for all, referencing that they are ties to the full backup created yesterday.  I hope this information helps anyone else trying to understand how LSNs work!


texts: Connecting to SQL Server 2008 on windows server 2008

texts:
Hell all

I am new to windows server 2008 and we are trying to transfer our website from hosting to virtual server.

I installed SQL Server 2008 Express edition and I did the things like opening port and allowing them through the firewall but I can't log into to the remote server from my local machine.

I am not quite sure which IP address to use to connect to SQL Server from local machine whether is it servers IP address or will SQL give us a different IP address.

Please help.

Thanks


texts: Forgot mysql owner/root username and password

texts:
Recently, I changed my mysql root user to someother username (say kevin). But, now both the accounts don't login with the old password. It says access denied.

I'm also not sure if the username is that (kevin). How can I retrieve both the root username and password?

I also tried resetting my mysql root password but I cannot, it says Access denied. Even if I use the root username, it logs in but I cannot see any database like mysql (Clearly, I don't have any privilege). Just the two basic databases are accessible. I am using Ubuntu 13.10, if that helps.

PS - I cannot reinstall mysql as I have important data stored in the databases.


texts: Queries from different tables locking each other (LCK_M_X)

texts:
Background: Was hired to work with JMeter for performance testing but I'm also getting more and more into SQL performance (because of time, and because I like it).  I still suck, and will for a long time.  I will try to explain the best I can.

I have a table (NUMERATION) that is updated by many transactions.  It contains most unique identifiers, so it gets a lot of updates.

This query

UPDATE Numeration WITH (ROWLOCK) SET NumCounter = NumCounter + 1 WHERE Tid = @p0

...is sometimes being locked by queries who have no relationship to this one, and I have no idea why.

There are no triggers in the tables. There is a Foreign Key that references a Unique identifer in another table, I don't know how to map all relationships but I think both queries have FKs on this Identifier

I used sp_WhoIsActive and sp_BlitzFirst to see the session IDs and type of lock (LCK_M_X).  The query plans are below:

https://www.brentozar.com/pastetheplan/?id=r1ebGh7Lr

Hope you can help me with not my homework.


texts: Inserting into Microsoft SQL Server Table

texts:
I have been using SQL for a bit now but have only recently tried out Microsoft SQL server Express. I have created a database called test and a table called idk. then I have tried to run the following query

USE test;
INSERT INTO idk(Id, Name) VALUES ("1","Name")

But that just says

  Msg 207, Level 16, State 1, Line 2
  Invalid column name '1'.
  Msg 207, Level 16, State 1, Line 2
  Invalid column name 'Name'.

Any help for using SQL with Microsoft SQL server will be appreciated.


texts: Add a suffix to duplicates on PostgreSQL

texts:
Not sure about the term "undouble", I am just trying to add a unique index on a column but it contains doubloons, so I need to update data first.

Figure this data:

BEFORE update

1;foo
2;foo
3;foo
4;bar
5;bar
6;anyother

AFTER update

1;foo0
2;foo1
3;foo2
4;bar0
5;bar1
6;anyother

Note than values without duplicates are unchanged.

My DB is Postgres 9.5.6


texts: Adding column into MySQL table size 150GB

texts:
I am trying to add column into MySQL table
Table size 150GB
Table storage engine= innodb
MySQL version=5.6.34
Table have already created primary key(cluster index).

More than 2 hours completed and tmp directory taken 207 GB space for this operation and still not completed
I tried algorithm =inplace and lock=none then I got syntax error and I was trying to type correct syntax but no luck so I decided to do only write add column query .

My question is why MySQL uses more than 150GB tmp size and still consuming size and not completed yet. I have no idea when it will complete . I checked .sql file size which is created while add column query running The size 1.1TB how?

Table have 4 indexes and aut_increment already on table
Please help me...


texts: MySQL Replication Master Slave Architecture - Web app that is based on the SLAVE DB

texts:
We are developing a web application (Spring mvc based app) that uses a MySQL DB. (let's call it the "original app")

The DB architecture uses a MASTER-SLAVE configuration with replication.
The web app uses the MASTER for the functionality.

We are now developing a Back-office application for our "original app".
This back office is in itself a web app (spring mvc based).

In order to reduce the performance impact on the "original app" we would like to use the SLAVE replica as the DB for the back-office.

This replica DB will include unique tables (that will exists only on the SLAVE, to serve the back-office app).

Non of us ever worked in such architecture, and we are pretty intimidated by this, therefore we have some questions:

Is this architecture even viable?
In case of a replication-error , How hard will it be to recover? remember, that the SLAVE will consist of tables that are not in the MASTER
Any other tips \ information we can use?
We cannot reconstruct the slave-only tables from the replicated tables. No That is one of my biggest concerns. Is that a show stopper?


texts:
I run below query to fetch data from a Oracle DB -
SELECT T1.Col1, T2.Col1 AS Tab2_Col1
    FROM Tab1 AS T1
        INNER JOIN Tab2 AS T2
        ON Tab1.Col3 = Tab2.Col4

With that, I am getting below error -
ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:
*Action:
Error at Line: 2 Column: 32

Can someone help me to understand why my query is failing?
Thanks for your time


texts: Does the compatibility level for sql server work reliably for scripts?

texts:
We have some DB scripts that need to be migrated from SQL Server 2008 R2 back to 2005, often scripts created in SQL Server 2008 / 2008 R2 won't run on a SQL Server 2005 installation (which some customers still use).

So my question: from your experience/knowledge, is this really necessary, or does setting the compatibility level back to 90 on SQL Server 2008 (2008 R2) fix the issue of unnoticed breaking scripts in SQL Server 2005? MSDN says "compatibility level provides only partial backward compatibility with earlier versions of SQL Server ", so I'm unsure here.

Thanks...

I would like a concrete list of “features will pass compatibility level 90 but break on SQL Server 2005" or link. If the list is short list, we could convert that to an internal "don't do that!" list and save a lot of work.


texts:
I'm trying to run some queries in Sublime Text3.

I downloaded this plugin, and I set up the settings:

{
    "connections": {
        "Connection 1": {
            "type"    : "oracle",
            "host"    : "127.0.0.1",
            "port"    :  1521,
            "username": "myuser",
            "password": "mypass",
            "database": "myschema",
            "service" : "What should I put here??"
         }
    }
}

The problem is: when I try to run (Ctrl+E+Ctrl+E) anything it says: No active connection.

My connection in SqlDeveloper:

What should I add to make it work? Thanks in advance.

Edit

Take into account it what they said:
"Some directories have to be defined in the PATH environment variable, according to the SGBD that you want to use.", I made this:

{
    "cmd": ["C:\\oraclexe\\app\\oracle\\product\\11.2.0\\server\\bin\\sqlplus.exe", "-s", "myuser/mypass@myschema", "@$file"],
    "selector": "source.sql",
    "encoding": "cp1252"
}


texts:
Trying to setup log shipping between two SQL Server 2016 servers using the built-in wizard (right click database > Tasks > Ship Transaction Logs). Wizard succeeds, backup job runs fine, copy/restore jobs fail.

Wizard setup:

Configured primary db to backup at a network location. gave SQL Server Agent service full control on that folder. SQL Server service is running under a domain account which also has full control on that folder.
Added secondary server, configured copy job to copy to a local folder on that server. Gave service accounts full control on that local folder.
Configured restore job with no recovery mode

Wizard completes with no issues and creates jobs on both servers. On the secondary server, when I expand the databases, the database appears in "Restoring" mode. This mode does not go away. When I check the error logs I see the message

  Error: 18456, Severity: 14, State: 38.

  Login failed for user 'EAGLE\my_username'. Reason: Failed to open the
  explicitly specified database 'My_Db'. [CLIENT: ]

my_username is the username I used to log into the primary database and run the wizard and this user is a sysadmin on both servers. Why and where is this account even coming in picture? Why not the SQL Job agent service account?

Anyway if I try to manually run the copy job on secondary server. It fails and gives no specific error message except that this step failed. Event viewer or DB error log have no entries for this fail.

How can I make this work?


texts: How to find cores and logical processors in SQL Server

texts:
I can see 4 processors when i right click on SQL server instance -> properties->General->Processors,

does it mean is the server had 2 core processor or 4 core processor? what is CORE? where do i check how many cores sql server has?

Very much appreciate your replies


texts: Understanding multiple table join with aggregation

texts:
I have a basic question on how JOIN works on multiple tables. I want to count occurrences of Foreign Key in link1 & link2

CREATE TABLE main (
   id SERIAL PRIMARY KEY,
   name text NOT NULL
);

CREATE TABLE link1 (
   id SERIAL PRIMARY KEY,
   main_id integer NOT NULL,
   CONSTRAINT main_id_fk FOREIGN KEY (main_id) REFERENCES main (id)
);

-- link2 is similar to link1

SQL Fiddle

Why does the query below give a product of counts (rather than sum) when the count is non-zero in both columns.

SELECT main.id, COUNT(link1.main_id) + COUNT(link2.main_id)
FROM main
LEFT JOIN link1 ON main.id=link1.main_id
LEFT JOIN link2 ON main.id=link2.main_id
GROUP BY main.id


texts: SSIS how to parse column with erratic data

texts:
I have a question on how to parse out a column and hoping a SSIS/Database guru can help out :)

Here is the problem.  I have a column saved as a string/varchar format in SQL Server, and need to parse out data from it.  I have Business Intelligence Development Studio(BIDS) and SSIS support.  I am familiar with using the tool to use derived columns and splitting the data like that using BIDS.

The problem is the data, and here's an example of it.

Easy to parse with derived columns
24 Year
2 Month
3 Month
2 Month
8 Year
7 Year

No clue how to approach to parse
1x Month
3x per Month
1-2 x per month
6 days per month

So what i want to essentially do is, if data is standard I want to split the column into two columns, Number(int) and DayType(string), and if it falls into the 2nd category (erratic data), put it in a 3rd column, Other.  Any suggestions on how to approach this problem?

Added Clarification:  I want to put the non standard data into another 3rd column, so I can look at the data, and run commands on that particular column, to edit out some of the fluff, then make the data workable in the application.

For example, 3x per month, take out the "x per" and then parse that into the columns Number, and Daytype.

Also where there are cases of 1-2, I would like to take the higher number, and drop the lower number, so for example 4-6 days a week, keep 6, drop "a week" and keep days


texts:
Is it possible to concatenate the rows from two/more different columns into one new column ?
Example:

 C1     C2      C3
---- + ---- => ----
 a      d       a
 b      e       b
 c      f       c
                d
                e
                f


texts: Replacing a member of a replica set with the same host name

texts:
I am replacing a member of a replica set (the new member has a different IP address).

What I am looking to do is as follows:

RSYNC the data directory of the existing secondary node to the new node.
Stop the existing node.
Map the existing hostname to the IP of the new node.
Start MongoDB on this new node with the replica set.

Mongo Version 3.2 and storage engine MMapV1

it is 3 member replica set.

Primary
Secondary
Arbiter
My plan is to first replace arbiter with a new secondary node then. Do the procedure with on existing secondary node to replace this also.

Could someone please verify if the above steps make sense?


texts: Log statements from within triggers in Postgresql

texts:
I have several sql statements running in some of my plpgsql triggers. I've enabled logging with the log_statement = 'all'. It logs the 'triggering statement', but not the statements within the query itself.

Is there a way to display statements that were executed within trigger in standard postgres log?


texts: How to do a point in time recovery in postgresql?

texts:
I want to do a point in time recovery in postgresql. This is my recovery.conf

standby_mode = 'off'
primary_conninfo = 'host=localhost user=postgres port=5432 krbsrvname=postgres'
restore_command = 'cp /home/duc/backup/database/postgre_backupdb/%f %p'
recovery_target_time = '2017-06-16 9:00:00'

But when I start the server I always get this error in log

2017-06-17 08:01:53.679 ICT [6984] LOG:  database system was shut down at 2017-06-16 16:27:53 ICT
2017-06-17 08:01:53.679 ICT [6984] LOG:  starting point-in-time recovery to 2017-06-16 09:00:00+07
cp: cannot stat '/home/duc/backup/database/postgre_backupdb/00000002.history': No such file or directory
cp: cannot stat '/home/duc/backup/database/postgre_backupdb/000000020000000C00000042': No such file or directory
2017-06-17 08:01:53.682 ICT [6984] LOG:  invalid checkpoint record
2017-06-17 08:01:53.682 ICT [6984] FATAL:  could not locate required checkpoint record
2017-06-17 08:01:53.682 ICT [6984] HINT:  If you are not restoring from a backup, try removing the file "/var/lib/postgresql/9.6/main/backup_label".
2017-06-17 08:01:53.683 ICT [6983] LOG:  startup process (PID 6984) exited with exit code 1
2017-06-17 08:01:53.683 ICT [6983] LOG:  aborting startup due to startup process failure
2017-06-17 08:01:53.684 ICT [6983] LOG:  database system is shut down

I have try to change the recovery_target_time to other time but still get that error.

How do I do a point in time recovery ?


texts: Receiving error "invalid identifier" while trying to update a table in Oracle

texts:
I'm trying to transform a Merge query into an Update query for getting better performance but I could not succeed.
Tables structure:
create table TEST_FILE_MERGE
(
  customer_num    NUMBER,
  cust_first_name VARCHAR2(180),
  cust_last_name  VARCHAR2(180),
  r_id            NVARCHAR2(1000)
)
create table REQUEST
(
  req_id              NVARCHAR2(1000),
  cust_no             VARCHAR2(50),
  customer_first_name VARCHAR2(50),
  customer_last_name  VARCHAR2(50),
  error_code          NUMBER,
  is_checked          NUMBER(1)
)

Here is my Merge query:
 MERGE INTO (SELECT * FROM REQUEST WHERE REQ_ID = REQUEST_ID) R
  USING TEST_FILE_MERGE A
  ON (A.CUSTOMER_NUM = R.CUST_NO)
  WHEN MATCHED THEN
    UPDATE
       SET R.CUSTOMER_FIRST_NAME = A.CUST_FIRST_NAME,
           R.CUSTOMER_LAST_NAME  = A.CUST_LAST_NAME,
           R.ERROR_CODE          = 0,
           R.IS_CHECKED          = 1;

And Here is my Update Query which is not correct:
UPDATE (SELECT A.CUST_FIRST_NAME, A.CUST_LAST_NAME,
          FROM TEST_FILE_MERGE A
         INNER JOIN REQUEST B
            ON A.CUSTOMER_NUM = B.CUST_NO)
   SET B.CUSTOMER_FIRST_NAME  = A.CUST_FIRST_NAME,
       B.CUSTOMER_LAST_NAME   = A.CUST_LAST_NAME,
       B.ERROR_CODE           = 0,
       B.IS_CHECKED           = 1;

COMMIT;

I receive error ORA-00904:"B"."IS_CHECKED" invalid identifier but column IS_CHECKED  exists in REQUEST  table .
What is wrong with my update query?And how am I suppose to put this part of my merge SELECT * FROM REQUEST WHERE REQ_ID = REQUEST_ID into my update statement?
Thanks in advance


texts: Refreshing a stored generated column in Postgres

texts:
How in PostgreSQL can I refresh the values of a stored generated column.

Example

CREATE TABLE people (
    first_name TEXT,
    last_name TEXT

);

CREATE OR REPLACE FUNCTION name(people) RETURNS text AS
$$
SELECT $1.first_name || ' ' || $1.last_name
$$ LANGUAGE SQL STRICT
                IMMUTABLE
        ;

alter table people add column   full_name text GENERATED ALWAYS AS (name(people)) STORED;

insert into people(first_name, last_name) values('John', 'Smith');

select full_name, full_name='John Smith' as pass from people;

If one then needs to change the function used to generate a stored column value, in this case name how can you ask postgres to recalculate these fields.

eg.

CREATE OR REPLACE FUNCTION name(people) RETURNS text AS
$$
SELECT $1.first_name || ' ' || $1.last_name  ||  ' function change'
$$ LANGUAGE SQL STRICT
                IMMUTABLE
        ;

 -- refresh generated column here

select full_name, full_name='John Smith function change' as pass from people;

Sorry this is a bit of an odd example.

Fiddle


texts:
I am trying to write code for this query:

  Find the names of the actors that played at least in one movie since 2000 and where not directors of any movie at this period of time.

My thought was to split this query into two, then to merge the two parts. The first part will be code that finds the names of the actors that played in at least one movie since 2000. The second code will be a query that finds the actors who were not directors of any movie at this period of time.

I have this two queries:

Code for part one:

SELECT DISTINCT Actors.FirstName, Actors.LastName
FROM (Actors INNER JOIN ActorsMovies ON Actors.ActorID=   ActorsMovies.ActorID)
INNER JOIN Movies ON ActorsMovies.MovieID=Movies.MovieID
GROUP BY ActorsMovies.MovieID, Actors.FirstName, Actors.LastName, Movies.Year
HAVING COUNT(ActorsMovies.MovieID)>0 AND Movies.Year>=2000;

Code for part two:

WHERE SELECT DISTINCT Directors.FirstName, Directors.LastName
FROM( (Directors
INNER JOIN DirectorsMovies ON    Directors.DirectorID=DirectorsMovies.DirectorID)
INNER JOIN Movies ON  DirectorsMovies.MovieID=Movies.MovieID)
GROUP BY Movies.Year, Directors.FirstName, Directors.LastName
HAVING Movies.Year>=2000);

These two queries work.My difficulty is in merging them. I tried using NOT EXISTS here is my attempt:

 SELECT DISTINCT Actors.FirstName, Actors.LastName
 FROM (Actors INNER JOIN ActorsMovies ON Actors.ActorID= ActorsMovies.ActorID) INNER JOIN Movies ON ActorsMovies.MovieID=Movies.MovieID
 GROUP BY ActorsMovies.MovieID, Actors.FirstName, Actors.LastName, Movies.Year
 HAVING COUNT(ActorsMovies.MovieID)>0 AND Movies.Year>=2000
 WHERE NOT EXISTS(
 SELECT DISTINCT Directors.FirstName, Directors.LastName
 FROM( (Directors INNER JOIN DirectorsMovies ON   Directors.DirectorID=DirectorsMovies.DirectorID)
 INNER JOIN Movies ON  DirectorsMovies.MovieID=Movies.MovieID)
 GROUP BY Movies.Year, Directors.FirstName, Directors.LastName
 HAVING Movies.Year>=2000);


texts: Oracle 10g: Is it possible to designate a undo point?

texts:
I was wondering if it is possible to designate a point in time, then later "roll-it-back" to that state (undo DDL and DML changes). Maybe using a record of changes and then executing the reverse? Does Oracle have any features like this built in 10g or 11g?

Thanks in advance,

Kyle


texts: SQL Server uses only PRIMARY partition by default

texts:
My database has 2 partitions files: PRIMARY, DATABASE_ARCHIVED. A lot of tables have a column "archived". When archived is 1 row is in the DATABASE_ARCHIVED partition. When archived is 0 row is in the PRIMARY partition.

To improve performance I started to modify requests in the application by adding archive=0 in WHERE and JOIN.

select *
from myTable1 t1
join myTable2 t2
on t1.id = t2.t1id
and t2.archive=0
where t1.archive=0

But there are a lot of requests, and I might make mistake when typing and won't be able to test everything.

Is there a way to force all requests on the PRIMARY partition and use the ARCHIVED partition on demand? In the application there is a checkbox to see archived data or not.

Not checked : look for data in PRIMARY ONLY, checked : look in both partition.

Can I do this?


texts:
How could I change the SQL Server 2008 R2 Express Default Collation for the whole server and a particular database?

Is there a way to do it using visual interface of SQL Server Management Studio? In the Server Properties window (and in the corresponding Database Properties window), this property is not available for editing.


texts: Transactional Replication : Issues with Identity Column in Subscriber

texts:
I have an Transactional replication set up form Server A to Server B.
I have a table which has an Identity column as a primary key.
In my publisher ,for the table tb1,the the identity column,Id1 was set as "Not for Replication" as Yes.

I used to get the error message "Cannot insert explicit value for idntity column in table tb1 when IDENTITY_INSERT is set to OFF"
But when i checked my subscriber ,i saw that the same column as set as as "Not for Replication" as No.
So i used the below script to change the setting in my subscriber tb1.

alter table [dbo].[tb1] alter column [Id1] add not for replication;

And the "Not for Replicaiton" is set as "Yes" in my subscriber too.

So what ever Id1 value in the source should be replicated to the subscriber table as well.

But now i get the below error message
"Explicit value must be specified for identity column in table tb1 when IDENTOTY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identtity column"

Seems like the explcit identity value in Id1 is not being passed from publisher.

Any suggestions on this.


texts: Interpolate values on Sorting in PSQL query

texts:
In the database, I have a field as status which can have a value of

1, 2, 11, 12, 21, 22

1, 11, 21 Stands for processing

2, 12, 22 Stands for completed.

I am doing this query to get all data with sorting..

select se.*, cam.name, cam.exid, u.api_key, u.api_id from snapshot_extractors se
            left join cameras as cam on se.camera_id = cam.id
            left join users as u on cam.owner_id = u.id
            where lower(cam.name) like lower('%%') or lower(se.requestor) like lower('%%')
            order by se.status desc []

But when It sorts through status.. sorts works but in table formation where I am displaying the data, 22 and 2 means the same thing. but on sorting. either 2 will be up the list or 22 will be down or either way.

I am not getting any clue how I can make it work with sorting, so 22 and 2 can be at the top when I am sorting for completed ones.


texts: Pgpool II and Postgresql upgrade scenario using different major versions

texts:
Pgpool FAQ states that:

  You cannot mix different major versions of PostgreSQL, for example 8.4.x and 9.0.x

And the reason that I understand very well is:

  Pgpool-II assumes messages from PostgreSQL to pgpool-II are identical anytime. Different major version of PostgreSQL may send out different messages and this would cause trouble for Pgpool-II.

As I am a bit adventurous, I tried to install last pgpool 3.6.x with pgsql 9.5.x and everything is working (or seams to work) like a charm.

Question:
When doing this this way (more recent pgpool than pgsql), what is the real risk ? For instance if I have a scenario where I want to upgrade from pgsql 9.5 to 9.6 and I want to reduce downtime, can I first upgrade pgpool and then go on with pgsql ?

In a master/slave stream replication with one separate pgpool server, this could allow me to keep the same entry point for app servers during the upgrade process beginning with standby server upgrade and then promoting it when ready.


texts: How to back out of mass update in SQL Replication

texts:
We have a database which is set up in Merge Replication.

We accidentally updated hundreds of thousands of records in a large published table on the publisher database. We did something like this:

update LargeTable
set Field = Field

So basically, every record got updated to be the same - no data was actually changed. However, every record in the table is now being delivered to every subscriber and the whole system has come to a halt. We are very worried this will take days and days to complete.

How can we safely remove data from the msmerge_contents and msmerge_genhistory table? Are there other system replication tables to remove records from? I can see the exact records in these tables that refer to the mass update. I just don't want to remove them without knowing it is safe to do so.


texts:
I'm trying to setup streaming replication but I'm stuck, please help!

I followed the steps in https://wiki.postgresql.org/wiki/Streaming_Replication

On Master I have
(postgresql.conf)

archive_mode = on
wal_level = archive
max_wal_senders = 5
wal_keep_senders = 150

(pg_hba.conf)

host replication user-with-rep IP/32 md5

On slave I have
(recovery.conf)
primary_conninfo = ' host=IP port=5432 user=USER password=PWD '
standby_mode = 'on'
restore_command = 'copy "\\path\%f" "%p" '

I start services on primary and then do a backup with

psql -c "SELECT pg_start_backup('label', true)"

Then I copy all files from primary to standby with

xcopy from\path\* to\path /s /i /e

finally

psql -c "SELECT pg_stop_backup()"

When I execute pg_ctl start on my standby I get the following

LOG:  database system was interrupted; last known up at 2015-05-27 14:16:41 EDT
LOG:  entering standby mode
LOG:  restored log file "000000010000000000000028" from archive
LOG:  redo starts at 0/28000090
LOG:  consistent recovery state reached at 0/280000B8
LOG:  unexpected pageaddr 0/24000000 in log segment 000000010000000000000029, offset 0
LOG:  started streaming WAL from primary at 0/29000000 on timeline 1

And on my master I have

LOG:  database system was shut down at 2015-05-27 16:00:14 EDT
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

And then nothing else happens, please I really need help with this, I appreciate all comments. Any questions, please feel free to ask.

I would like to have this setup first and then do replication slots.


texts: Diffrence in result from PostgreSQL (PostGIS) and SQL Server (Spatial)

texts:
I have two points and I have to calculate difference between them.

I using below query in SQL Server.

SELECT geography::Point(27.185425, 88.124582, 4326)
       .STDistance(geography::Point(27.1854258, 88.124500, 4326));

Its running fine and result is :

8.12599260290125

Now with same point when I am running below query in PostgreSQL.

SELECT ST_Distance(ST_GeomFromText('POINT(27.185425 88.124582)',4326),
                   ST_GeomFromText('POINT(27.1854258 88.124500)', 4326));

and Now result is :

8.20039023523232

Can anyone tell me why there is difference in results when calculating distance between two points?


texts: I would like to run the pg_dump command from a remote host off my database server that only allows fully verified certificate based connections. How should I go about doing this? The man pages for 9.1 state the instructions for connecting via password from what I could see.


texts:
I ran sp_Blitz that pointed me to a memory issue saying:

  Memory Dangerously Low  The server has
  32755 megabytes of physical memory, but only 235 megabytes are
  available.  As the server runs out of memory, there is danger of
  swapping to disk, which will kill performance.

The Server has 32 GB RAM total, 0-2 MB Free RAM, 200 - 2376 Mb Available.

  @@Version:
      Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)   Jun 28 2012 08:36:30    Copyright (c) Microsoft Corporation     Enterprise
  Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

I watched Performance Counters for a while and see the following values there:

Page Faults/s _Total ~ 1.200 Average, Max 18.000, regular Peeks up to 1500.
Page Faults/s sqlserver ~ 380 Average, Max 1600, regular Peeks up to 1000.
Target Server Memory 30006
Total Server Memory 30006
Buffer cache hit ratio  AVG 99,9
Target Pages 3.750 stable
Total Pages 3.750 stable
Free Pages Avg 8300, Max 13.000
Memory Grants Pending 0
% Processor Time _Total Average 15%

No. of User Connections is constantly around 300.

From my perspective some of these values indicate that the server is doing well, e.g. Memory Grants Pending or Buffer cache hit ratio.
Page Faults/sec look as if there is some caching in place. Resource Monitor shows that Hard Faults /sec (is this the same value) also has regularly high peeks.

So I wondered why sp_blitz indicated this issue. Am I facing a serious I NEED MORE MEMORY issue or is it just pointing to that 30GB assigned to sql of 32Gb physical memory leaves not enough memory left for windows?


texts:
I have a couple questions for those more familiar.  Most of my instances have been running Antelope despite having support for Barracuda.

I was looking to play around with some compresses innodb tables.  My understanding is this is only available under the Barracuda format.

I see innodb_file_format is dynamic so I can just switch over with out a bounce.  Are there any implications of doing this I should be aware of.  All I can tell is that means new tables or subsequently altered will be created with that format.  Is this all correct?
I was hoping to have to not go through and convert all my tables.  Is is kosher to have antelope and barracude tables coexisting in the same tablespace? Even if it works are there any gotcha's to look out for?

From what I've read and gathered from my tests the answers are: Yes. Yes. I'm not sure.

Update

I've been running w/ some Dynamic and some Compressed tables in various instances since this post with out issue.  Further I neglected to read http://dev.mysql.com/doc/refman/5.5/en/innodb-file-format-identifying.html  at the time.

  After you enable a given innodb_file_format, this change applies only
  to newly created tables rather than existing ones. If you do create a
  new table, the tablespace containing the table is tagged with the
  “earliest” or “simplest” file format that is required for the table's
  features. For example, if you enable file format Barracuda, and create
  a new table that is not compressed and does not use
  ROW_FORMAT=DYNAMIC, the new tablespace that contains the table is
  tagged as using file format Antelope.

So tables will be created as Antelope even if you allow Barracuda.  The mixing is unavoidable unless you specify every table as row_format dynamic or a compressed table.

There is no indication you should do a complete dump and reload when introducing your first Barracuda table (such as is recommended when upgrading major versions of mysql)


texts:
I have wamp installed in my system and sql server 2014, and configure so i can access sql server db through php.. with this code:

<?php
$serverName = "MY-PC\sqlexpress";

$connectionInfo = array("Database"=>"master");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
 echo "Connection established.
";
}else{
 echo "Connection could not be established.
";
 die( print_r( sqlsrv_errors(), true));
}
?>

that actually works. But when i change "master" with some "customdb" its giving me this error.

  Error: 18456, Severity: 14, State: 38. Login failed for user 'NT
  AUTHORITY\SYSTEM'.  Reason: Failed to open the explicitly specified
  database 'customdb'. [CLIENT: ]


texts: How to find usage of each object in other objects from a database?

texts:
I am with the task of taking an old SQL Server database and identify all of it's objects and their relationships, because the one who made it did not bother to use any foreign key, he just made all relations inside the code.

So my mission is to identify usages, relationships and make a graphic model, which I think I will use ERwin7 to do it.

I stated today taking every table name and searching it in the source-code and sql metadata. I open all those files in separated instances of Notepad++ and search to see if anyone really uses that table in the source-code or in any stored procedure or function. But there are 1500+ c# files and 857 objects in the database.

Is there a better way to do that?
Is there a tool to tell me where each table is used in the code and in the database without the need of Ctrl+F for each table name or other objects I will also check?
Is ER-win a reasonable choice for doing the re-modeling?


texts: SSRS report slow on initial query of the day

texts:
I am trying to fix an issue where an SSRS report is slow for the initial query of the day. I have tried using the solution described here:

Solving issue with first long starting report on SSRS 2008

...to no avail. I know that it seems to be an SSRS issue, but I am not sure if there is another workaround for this behavior. It uses Microsoft's ServerReport.Render method to render the report and dump it to a PDF.

Clarifications from comments:

I created a script like the one in the blog. I had that script run during off hours prior to the start of the business day. I also manually ran the script line by line in PowerShell and that did seem to alleviate the issue; however, that could have just been a placebo as the slowness was reported again by the QA assigned to this issue.

I have manually run the report and experienced the same level of slowness for the initial report query.

I do have a task that runs just before the start of business every day. I created the scheduler with the following line:

schtasks /create /tn "SSRS Recycle" /ru UserName /rl highest /np /sc daily
    /sd 08/01/2011 /st 02:00 /tr "powershell.exe -noprofile
    -executionpolicy RemoteSigned -file c:scriptsSSRSRecycle.ps1"

Is the path case sensitive? My Scripts file was named Scripts. Also, do I need slashes in the path name?


texts: How do I order the columns of a SQL statement (not the rows)?

texts:
I'm an ERP analyst, and when I find something I haven't seen in a long time, I need to jog my memory to know which column contains the data I want. I need to examine the data to do this, so a query against USER_TAB_COLS isn't sufficient. I normally just do a SELECT * and start scrolling.

The tables often have over 100 columns, and more than a handful have over 200. It's mentally exhausting to scroll to the right in my GUI tools to scan manually. If I could alphabetize the columns, I'd have an easier time finding what I need.


texts: why there is no incremental backup in sql server

texts: How i can change my database backup type from full backup to incremental backup? Is that possible? Why there is no option for incremental backup type.