When to vertically partition a table I have a table with 100 columns and I know there is an 8k limit before a table overflows in SQL Server

I'm using the following query's to have a look at the table stats

dbcc showcontig ('Products') with tableresults

and

SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'tablename'), OBJECT_ID(N'Products'), NULL, NULL , 'DETAILED'

I notice, with the second query as follows:

index_type_desc,    alloc_unit_type_desc, page_count
CLUSTERED INDEX,    LOB_DATA            , 2

If I am reading it correctly there is only 2 pages of overflow?

MinimumRecordSize,  MaximumRecordSize,  AverageRecordSize
628,                7928,               950.048

Is there further metrics to say (in a more concise way)? If I really need to go to the extra effort of vertically partitioning this table?

At the moment, it looks like i am no where near the 8k column limit for native column size, and the majority of my data is all in Row Data

Is it a fair assumption to say query performance should not be impacted all that much?

P.S.: I know this is a long question, however some good concise metrics would be a good start for me

You should consider vertical partitioning of your table if your columns have different access pattern: some columns are often read and (almost) never updated, others are often updated.

Unless you have columnstore indexes or covering indexes for your queries, every time you need only some columns a whole row is retrived, so if you want namy rows but for limited columns vertical partitioning may be your case.

Please also take a look on this: Vertical Partitioning as the way to reduce IO

Note that MinimumRecordSize 628 + MaximumRecordSize 7928 means that your rows have fields of variable length data types such as varchar(max), and this means that you did not reach max row size. If MinimumRecordSize was equal to MaximumRecordSize this could mean that you may will not be able to add othe columns to your table, but since MinimumRecordSize is only 628, many other variable length columns can still be added.

When row-overfloing data does not fit in a row, a newe page is allocated to accomodate it and only a small pointer is left on original row that points to row-overflow page containing the value.


How to filter values from different table inner join and group by There Are 5 Tables BASE_Inventory, BASE_GoodsInward, BASE_GoodsInwardLine, BASE_GoodsOutward, BASE_GoodsOutwardLine

BASE_Inventory Table Inner Join to BASE_GoodsInwardLine and BASE_GoodsOutwardLine
BASE_GoodsInward Inner Join to BASE_GoodsInwardLine Table
BASE_GoodsOutward Inner Join to BASE_GoodsOutwardLine Table

Tables are below,

Inventory Table

CREATE TABLE BASE_Inventory
(
InventoryId BIGINT NOT NULL UNIQUE,
ItemNameCode VARCHAR(45) NOT NULL,
ItemDescription1 VARCHAR(255) NOT NULL,
CurrentQuantity DECIMAL(18, 4) DEFAULT 0.00 NOT NULL
PRIMARY KEY(InventoryId)
)

Goods Inward Table

CREATE TABLE BASE_GoodsInward
(
    GoodsInwardId BIGINT NOT NULL UNIQUE,
    GoodsInwardDate DATE NOT NULL
    PRIMARY KEY(GoodsInwardId)
)

CREATE TABLE BASE_GoodsInwardLine
(
    GoodsInwardLineId BIGINT NOT NULL UNIQUE,
    GoodsInwardId BIGINT NOT NULL,
    InventoryId BIGINT NOT NULL,
    InwardQuantity DECIMAL(18, 4) NOT NULL
    PRIMARY KEY(GoodsInwardLineId)
)

Goods Outward Table

CREATE TABLE BASE_GoodsOutward
(
    GoodsOutwardId BIGINT NOT NULL UNIQUE,
    GoodsOutwardDate DATE NOT NULL
    PRIMARY KEY(GoodsOutwardId)
)

CREATE TABLE BASE_GoodsOutwardLine
(
    GoodsOutwardLineId BIGINT NOT NULL UNIQUE,
    GoodsOutwardId BIGINT NOT NULL,
    InventoryId BIGINT NOT NULL,
    OutwardQuantity DECIMAL(18, 4) NOT NULL
    PRIMARY KEY(GoodsOutwardLineId)
)

Data with table

Inventory

Inward

Outward

I want to retrieve 2 type of result query from above result Inward & Outward tables (Inward and Outward Quantity should be SUM of Inventory ID)

1: Date wise Inventory Inward & Outward

2: Inventory wis Inward & Outward

Probably someone can more optimally write this but it is fast:

  --1.
    SELECT alll.Date, alll.InventoryId, alll.ItemNameCode, SUM(alll.CurrentQuantity) AS CurrentQuantity, SUM(alll.InwardQuantity) AS InwardQuantity, SUM(alll.OutwardQuantity) AS OutwardQuantity FROM (
    SELECT BASE_GoodsInward.GoodsInwardDate AS "Date", BASE_Inventory.InventoryId, BASE_Inventory.ItemNameCode, SUM(BASE_Inventory.CurrentQuantity) AS CurrentQuantity, 0 AS InwardQuantity, 0 AS OutwardQuantity  FROM BASE_Inventory
    INNER JOIN BASE_GoodsInwardLine ON BASE_Inventory.InventoryId = BASE_GoodsInwardLine.InventoryId
    INNER JOIN BASE_GoodsInward ON BASE_GoodsInwardLine.GoodsInwardId = BASE_GoodsInward.GoodsInwardId
    GROUP BY BASE_GoodsInward.GoodsInwardDate, BASE_Inventory.InventoryId, BASE_Inventory.ItemNameCode
    UNION ALL
    SELECT BASE_GoodsOutward.GoodsOutwardDate AS "Date", BASE_Inventory.InventoryId, BASE_Inventory.ItemNameCode, SUM(BASE_Inventory.CurrentQuantity) AS CurrentQuantity, 0 AS InwardQuantity, 0 AS OutwardQuantity  FROM BASE_Inventory
    INNER JOIN BASE_GoodsOutwardLine ON BASE_Inventory.InventoryId = BASE_GoodsOutwardLine.InventoryId
    INNER JOIN BASE_GoodsOutward ON BASE_GoodsOutwardLine.GoodsOutwardId = BASE_GoodsOutward.GoodsOutwardId
    GROUP BY BASE_GoodsOutward.GoodsOutwardDate, BASE_Inventory.InventoryId, BASE_Inventory.ItemNameCode
    UNION ALL
    SELECT BASE_GoodsInward.GoodsInwardDate AS "Date", BASE_GoodsInwardLine.InventoryId, BASE_Inventory.ItemNameCode, 0 AS CurrentQuantity, SUM(BASE_GoodsInwardLine.InwardQuantity) AS InwardQuantity, 0 AS OutwardQuantity
    FROM BASE_GoodsInwardLine
    INNER JOIN BASE_GoodsInward ON BASE_GoodsInwardLine.GoodsInwardId = BASE_GoodsInward.GoodsInwardId
    INNER JOIN BASE_Inventory ON BASE_GoodsInwardLine.InventoryId = BASE_Inventory.InventoryId
    GROUP BY BASE_GoodsInward.GoodsInwardDate, BASE_GoodsInwardLine.InventoryId, BASE_Inventory.ItemNameCode
    UNION ALL
    SELECT BASE_GoodsOutward.GoodsOutwardDate AS "Date", BASE_GoodsOutwardLine.InventoryId, BASE_Inventory.ItemNameCode, 0 AS CurrentQuantity, 0 AS InwardQuantity, SUM(BASE_GoodsOutwardLine.OutwardQuantity) AS OutwardQuantity
    FROM BASE_GoodsOutwardLine
    INNER JOIN BASE_GoodsOutward ON BASE_GoodsOutwardLine.GoodsOutwardId = BASE_GoodsOutward.GoodsOutwardId
    INNER JOIN BASE_Inventory ON BASE_GoodsOutwardLine.InventoryId = BASE_Inventory.InventoryId
    GROUP BY BASE_GoodsOutward.GoodsOutwardDate, BASE_GoodsOutwardLine.InventoryId, BASE_Inventory.ItemNameCode
    ) alll
    GROUP BY alll.Date, alll.InventoryId, alll.ItemNameCode
    ORDER BY alll.Date, alll.InventoryId, alll.ItemNameCode;

    --2.
    SELECT alll.InventoryId, alll.ItemNameCode, SUM(alll.CurrentQuantity) AS CurrentQuantity, SUM(alll.InwardQuantity) AS InwardQuantity, SUM(alll.OutwardQuantity) AS OutwardQuantity FROM (
    SELECT BASE_Inventory.InventoryId, BASE_Inventory.ItemNameCode, SUM(BASE_Inventory.CurrentQuantity) AS CurrentQuantity, 0 AS InwardQuantity, 0 AS OutwardQuantity  FROM BASE_Inventory
    INNER JOIN BASE_GoodsInwardLine ON BASE_Inventory.InventoryId = BASE_GoodsInwardLine.InventoryId
    INNER JOIN BASE_GoodsInward ON BASE_GoodsInwardLine.GoodsInwardId = BASE_GoodsInward.GoodsInwardId
    GROUP BY BASE_Inventory.InventoryId, BASE_Inventory.ItemNameCode
    UNION ALL
    SELECT BASE_GoodsInwardLine.InventoryId, BASE_Inventory.ItemNameCode, 0 AS CurrentQuantity, SUM(BASE_GoodsInwardLine.InwardQuantity) AS InwardQuantity, 0 AS OutwardQuantity
    FROM BASE_GoodsInwardLine
    INNER JOIN BASE_GoodsInward ON BASE_GoodsInwardLine.GoodsInwardId = BASE_GoodsInward.GoodsInwardId
    INNER JOIN BASE_Inventory ON BASE_GoodsInwardLine.InventoryId = BASE_Inventory.InventoryId
    GROUP BY BASE_GoodsInwardLine.InventoryId, BASE_Inventory.ItemNameCode
    UNION ALL
    SELECT BASE_GoodsOutwardLine.InventoryId, BASE_Inventory.ItemNameCode, 0 AS CurrentQuantity, 0 AS InwardQuantity, SUM(BASE_GoodsOutwardLine.OutwardQuantity) AS OutwardQuantity
    FROM BASE_GoodsOutwardLine
    INNER JOIN BASE_GoodsOutward ON BASE_GoodsOutwardLine.GoodsOutwardId = BASE_GoodsOutward.GoodsOutwardId
    INNER JOIN BASE_Inventory ON BASE_GoodsOutwardLine.InventoryId = BASE_Inventory.InventoryId
    GROUP BY BASE_GoodsOutwardLine.InventoryId, BASE_Inventory.ItemNameCode
    ) alll
    GROUP BY alll.InventoryId, alll.ItemNameCode


How is it possible to convert a 3-node replica set to a 1-node replica set during low-usage hours We have a chat application that runs on MongoDB. For the amount of users that we currently have, it is sufficient to have a 2 node+Arbiter set up during peak hours.

However, during off-peak hours, we would like to turn off the secondary node and the arbiter to save on cloud computing costs.

Is this possible? Simply shutting down the secondary+arbiter makes Mongo unable to elect a new primary and the app fails.

If the best/only solution is to remove the secondary and arbiter from the replica set, what are the consequences of doing when adding them back on the next peak hours cycle?

MongoDB replication is to provide automatic fail over during disaster recovery, minimize downtime during maintenance, to use the secondary for analytical use cases and so on. In your scenario, you don't want to avail these benefits during off-peak hours and as far as I know there is no perfect workaround to do these tasks without downtime.

  Simply, shutting down the secondary + arbiter makes Mongo unable to
  elect a new primary and the app fails

Yes, election process will not happen in a replica set with single node. It needs at least two mongod nodes which might not be present in your case if you remove secondary and an arbiter.

  If the best/only solution is to remove the secondary and arbiter from
  the replica set

You can remove the secondary and arbiter from the replica set by following the steps in this link mongo-replica-set-to-single-server.

  what are the consequences of doing when adding them
  back on the next peak hours cycle?

While adding back the replica sets, you will have to restart mongod instances, issue rs.reconfig() method and it can force the current primary to step down which causes an election. During this window time, your app will not read/write anything from/into database which can last up to 10-30 seconds (for re-configuring replicas), another 20-30 seconds (for restarting mongod instances) based on network bandwidth and replica set configuration. Hence, it is not recommended during peak hours.


How to delete large amount of data from a sql table? We ran out of disk space and we have a table with about 10million records.

We'd like to delete the old records which are about 2million.

What are the impact of just deleting these using a simple DELETE command on production?

What's the best way to do this?

The impact will depend on a number of things including your dependencies and indexes on the table. If there are few dependencies (i.e. foreign keys) and few indexes then the delete will go faster. In any case it is best to do this type of operation "off peak" hours when there are the fewest number of users on the system.

If you're in the pinch right now then you'll have to perform the delete now. The system will be a bit slower than usual while the delete is taking place.

You should also understand that due to the way database and log files grow in SQL Server you will not suddenly have a bunch of disk space to work with. To free up more disk space you need to look into other things (in the short term) such as log truncation and db shrinking. Then, figure out a better long-term disk solution.


How to enable group replication again after dissolving a MySQL Innodb Cluster using mysqlsh? When I dissolve a innodb cluster using mysqlsh it automatically disables replication on the instance.
How do I enable replication on the instance again so i can "start group replication" after a cluster dissolve? I'm getting these errors in the log after I dissolve a cluster and try to start group replication again. my group replication was working fine before the dissolve.
[ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Invalid hostname or IP address () assigned to the parameter local_node!'
[ERROR] [MY-011674] [Repl] Plugin group_replication reported: 'Unable to initialize the group communication engine'
[ERROR] [MY-011637] [Repl] Plugin group_replication reported: 'Error on group communication engine initialization'
[ERROR] [MY-011718] [Repl] Plugin group_replication reported: 'Error calling group communication interfaces while trying to leave the group'

creating a new innodb cluster and adding the instances will start the group replication again on those instances but I'm still unable to find how I can enable group replication manually with "start group replication" sql command without a cluster


Moving MySQL to another server without updating connection strings Currently, I have a Windows 2008 R2 Server which runs both all my websites and all my MySQL databases.  I am considering moving MySQL to another seperate server which may also be Windows or may be Linux.  My question is, if I do this, and MySQL is then on a different IP address, is there anyway of aliasing the IP address of the new MySQL server from the current server hosting the websites so I do not have to go through and update every single connection string?

Thanks.

If you use "localhost" in connection string then the following may work:

edit hosts file and change the line

127.0.0.1   localhost

to

X.X.X.X localhost //( your new ip)

and restart the local application machine.


Execute a Stored Procedure Daily I am building a Web Application which is attached to a Database which is in a Web Server(which is at unknown remote location). I want to execute a Stored Procedure daily at a specific time. I searched and found this.

I am able to connect to my Database. But not to Server Agent.

Please help me to solve this problem. Thanks in Advance.

I'm assuming your DBA has not (and will not) give permissions for SQLServerAgent.
If so you will have to use another scheduler.

If you want to execute a stored procedure daily at a specific time, I'd suggest

Call the stored procedure from a page in your web application layer
Set up a scheduler to call the web page at a daily interval.

A free service like this could be set up to call your URL on a daily interval.
http://www.pingjs.com/


Query other database's INFORMATION_SCHEMA tables I have a shared database that stores procedures for all the other databases on the same server. This helps maintain one instance of a procedure rather than copying it across all databases. As long as I prepend DBName to all my tables, i.e. SELECT * FROM DBName.Table, I've been able to successfully have a shared repository for procedures.

However, I've run into an instance where I need to reference INFORMATION_SCHEMA tables, but the shared database only has tables for that database. Is there a concept of querying INFORMATION_SCHEMA on another database?

I'd like to do something like this, but it doesn't work:

SELECT * FROM [DBName].INFORMATION_SCHEMA.TABLES

I think this does what you need:

CREATE
    DEFINER = root
    PROCEDURE ...
    SECURITY INVOKER   -- This limits what the proc can do to what the user is GRANTed
    BEGIN ... END

See http://dev.mysql.com/doc/refman/5.6/en/create-procedure.html


Monitor unusable indexes on oracle I have a lot of database, a ton of partitioned tables and many processes running on them.
In a couple of occasions I hade problems for unusable indexes or partitions of them. A bugged procedure, or a human error of the dba.

I'd like to rise an alarm when an index became unusable.

Oracle is version 11.2, new installations 12. All enterprise.
Grid control is 12c.

I also have nagios on every machine

The only way I found is to use a nagios plugin to run custom query on database. Is this the best way?

The plugin: http://labs.consol.de/lang/en/nagios/check_oracle_health/

You can use both, Nagios and Oracle Cloud Control 12c (also applies for Grid Control 10g and 11g).

I used Nagios about 8 years ago to run custom SQLs but I guess there is much changed since then. Maybe someone else has recent experiences with that.

In Oracle Cloud Control you have 2 options:

Write your own Agent Plugin. I never did that because after some testing I considered it to be to much work.
Use "Metric Extensions" available with "Diagnostic Pack".


Keep default value as 0.0 for 0 or NULL for numeric column I have a numeric column Number(28,3) in which I have to insert value as 0.0 ,
whenever I encounter 0 or NULL/blank at the time of data insertion.

I have tried below :

NVL(col_name,0.0)

but it's just replacing null values with 0

This is just a matter of displaying the data as required:

SQL> create table t1(c1 number(28,3));

Table created.

SQL> insert into t1 values(nvl(null, 0.0));

1 row created.

SQL> select * from t1;

        C1
----------
         0

SQL> col c1 format 999990.0
SQL> select * from t1;

       C1
---------
      0.0


Having trouble with Merge Join with SSIS I am currently using Merge Join to perform an inner join on two sorted sets of data in SSIS. The problem I am having is that no records are being returned from the inner join. To troubleshoot I have performed the following:

I exported both sorted data sets into my local SQL Server, and wrote an inner join manually. This confirmed that I should be receiving 720 rows as a result of the inner join between the two data sets.

I used a lookup, instead of a merge join, to perform the inner join and this actually worked. The reason I do not want to use a lookup is because of the performance I am getting with these sets of data. To give you an idea of the data I'm working with, table A has ~16k rows and table B has ~7.8 million rows. Using the lookup component yielded a run time of over an hour.

So I was wondering if you guys could help me get the Merge Join setup correctly. To help you help me, I've provided screenshots of how I currently have things configured:
Data Flow of Job

Table A Sort

Table B Sort

Merge Join

Normally, I'd say check your data types and or casing but if the lookup works, then that doesn't seem as probable. Safe to assume the lookup would be against the 7.8M row table b? Check your metadata anyway for the 4 keys on both inputs, maybe there's a length discrepancy


Can I exchange partition between two table with partition with same name? I am trying  to restore one partition from a backup table. Both tables have partitions with the same name. My goal is to grab the data from the backup table partition x and put it in original table partition x. x is the name of the partition.

I have tried to do it by using an auxiliary table. Is there a way to exchange them directly or any other solution with better performance?

Have you seen this?  https://community.oracle.com/thread/889338?start=0&tstart=0

It states you can only exchange partitions from a partitioned table to a non-partitioned table, or vice-versa.

You'd need to temporarily exchange the partition into an
interim, non-partitioned, table, then into the target partitioned table.

There are some interesting wrinkles to the broadly stated rule above; see the following excerpt taken from the Oracle documentation:

  exchange_partition_subpart

  Use the EXCHANGE PARTITION or EXCHANGE SUBPARTITION clause to exchange the data and index segments of:

  One nonpartitioned table with:

  one range, list, or hash partition
  one range, list, or hash subpartition

  One range-partitioned table with the range subpartitions of a range-range or list-range composite-partitioned table partition
  One hash-partitioned table with the hash subpartitions of a range-hash or list-hash composite-partitioned table partition
  One list-partitioned table with the list subpartitions of a range-list or hash-list composite-partitioned table partition

  In all cases, the structure of the table and the partition or subpartition being exchanged, including their partitioning keys, must be identical. In the case of list partitions and subpartitions, the corresponding value lists must also match.


Matching multiple rows in a MySQL database A custom built eCommerce store has the following two tables:

orders

| order_id | user_id | amount | shipping_info | date_time           |
|----------|---------|--------|---------------|---------------------|
| 222      | 7       | 999    | XXX           | 2017-07-20 05:54:45 |
| 333      | 5       | 2599   | XXX           | 2017-06-18 22:34:25 |
| 444      | 9       | 1859   | XXX           | 2017-07-01 15:07:54 |

ordered_products

| order_id | product_id | amount | quantity | product_type |
|----------|------------|--------|----------|--------------|
| 222      | 6546       | 53     | 2        | A            |
| 222      | 8979       | 85.99  | 1        | B            |
| 444      | 9879       | 87     | 2        | A            |
| 444      | 4756       | 25     | 2        | A            |
| 333      | 3215       | 129    | 2        | B            |

I want to list order ids which

contain product_type "A" but NOT "B"
contain product_type "B" but NOT "A"
contains both product_type "A" and "B"

I can't find a way to do this without using multiple queries and PHP loops. Is this possible using only SQL queries?

This is usually done using conditional aggregation plus some applied logic:

select order_id,
   case max(case when product_type = 'A' then 1 else 0 end) +
        max(case when product_type = 'B' then 2 else 0 end)
      when 1 then 'A not B'
      when 2 then 'B not A'
      when 3 then 'A and B'
   end
from mytable
where product_type in ('A','B')
group by order_id


how to remove all permissions in a database? every time I need to either restore a database or reinitialize a replication publication I first (on the restored to be or subscription database) I script all the permissions, triggers, indexes, synonyms, sometimes statistics

then I do the restore or (apply the snapshot) and then
re-apply my saved scripts on the restored database.

I have some LIVE servers where the db_denydatawriter have been used profusely and although I have all the permissions as they used to be, because the db_denydatawriter comes with the restore (because it was in live) the new restored database has it now.

and it causes problems.

so much so that now I have this script (uses sp_foreachdb) just to remove that in test:

     declare @db_list NVARCHAR(MAX)

     SELECT @db_list  = STUFF((
                                SELECT ', ' + name FROM sys.databases
                                WHERE name NOT IN ('DBA','TABLEBACKUPS','MASTER','MSDB','MODEL','TEMPDB','distribution')
                                FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')

     SELECT [@db_list]  =@db_list

     exec sp_foreachdb @database_list = @db_list
                     ,@exclude_list = 'dba,tablebackups,master,msdb,model,tempdb'
                     ,@command='use ?;begin try EXEC sp_droprolemember @rolename = [db_denydatawriter], @membername = [mycompany\Developers] end try begin catch end catch '

question:

How can I delete all the permissions in the newly restored database before I apply my scripts containing the permissions before the restore?

In other words, what would be the easiest way to remove all existing permissions (after the restore) before I re-apply the former permissions?

This should help drop all users after restore.
declare @sql nvarchar(max)

set @sql = ''

select @sql = @sql + '
print ''Dropping ' + name + '''drop user ' + name + ''
from dbo.sysusers
where name not in ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'public') and LEFT(name, 3) <> 'db_'
order by name

--print @sql
execute (@sql)


Recovering Postgres database OK. I'm in a bit of trouble.

I've got an Ubuntu, had Postgres 9.1 installed.

There was something wrong with it - I couldn't start it for some reason. Out of panic, I backed up /var/lib/postgres/9.1/main (my data directory) and did

apt-get purge postgres*

Which removed everything.

Then I did a fresh installation of Postgres 9.1

Now I tried just moving the old data directory to the new installation - there is an error.

I heard you can recover from a file in the pg_xlog.

What are my options?

Without knowing your specific errors, I dont know what the easiest approach to recovering your database is.  For example, there could be minor problems like bad settings that cause PostgreSQL to be unable to start.  I would recommend asking a new question about getting PostgreSQL to start before looking at how to recover your data.

However, I can answer what are some aspects of your question about recovering data.  I have actually never seen data corruption in PostgreSQL, and so all my experience with it is second-hand (email list threads and the like).  It is very rare, and usually related to failing hardware.  If you do have data corruption, my recommendation is to restore from backup onto a new server while you run diagnostics and determine the problem on your existing server.

When you talk about recovering from the files in pg_xlog you are talking about using write logs as incremental backups.  You still need a compatible starting point, which means effectively you are going with a base backup plus all pg_xlog segments created from that point onward.

Now, if you have no good backups, and you have a lot of important data, it may be possible to hire an expert for significant expense to recover some or all of your data.   This is not cheap.

I think your immediate steps are to step back and ask why PostgreSQL is not starting and see what you can do about that first before assuming you need to recover your data.  If you need to recover your data, you hopefully have backups.


Speeding up linking Oracle tables in Microsoft Access Many people in my office use Oracle tables by linking them in Access (either 2010 or 2016) databases.

They always complain about the fact that, when Access is about to present them the list of tables they have access to in a specific Oracle instance for selection, they have to wait 10 to 15 minutes for the selection window to pop-up.

The reason seems to be the huge number of accessible tables, in turn related to the huge number of accessible schemas, on that particular instance, so that the resulting list contains tens of thousands of table names.

There was a similar problem with some DB2 databases, but was solved by adding a 'SCHEMALIST' option in the ODBC configuration (db2cli.ini) for those databases, to limit the number of schemas visible to the connection.

I had a look at Oracle ODBC documentation, but couldn't find a similar option, so I'm stuck.

Does somebody have a trick to share about how to filter schemas/tables visible to an ODBC connection to Oracle or otherwise speed-up linking those tables?

What if you create a special Oracle user that Access would use to connect? That user would only have the ability to access the tables that are relevant, plus it would have a synonym for every table it had access. Then when you connect from Access to Oracle, Access would only populate the table list with the tables that you want to see. I'm going to guess that you have at least tens of thousands of tables in your schema if not hundreds of thousands of tables. Are you running Peoplesoft?

You might want to find other tools to work with besides Access. There must be other BI tools that you can work with. Possibly even a few open source tools.


In what order does postgresql handle queries which have conflicting locks? Let's say that I have a long running query SELECT and this query holds a AccessShareLock on a table. While this query is being processed a new query which needs a AccessExclusiveLock on the table REINDEXDB is made. I suppose this query must wait the other query to finish, becuase they have conflicting locks. And while my REINDEXDB query is waiting for the SELECT query to finish, a second SELECT query is made. Now my question is

Which query will be serviced first? REINDEXDB or SELECT? Select query has been made later but it doesn't have a conflicting lock with the ongoing select query. So will they be processed in parallel? Or because REINDEXDB query has been made earlier will the second select query wait for REINDEXDB to finish?

I can't talk to postgresql directly. However, most rdbms in this situation would process the requests in the order they were received. That means a query can only execute if there is no other query that was submitted earlier and is either holding or waiting for a lock that is not compatible with the newly requested lock.

The reason is, that otherwise a query requesting an exclusive lock might never get executed ("starvation") if there is a high enough number/frequency of read requests coming in that keep sneaking by the waiting query.


Database design advice on a marksheet schema I am not sure whether this type of question is allowed here, but seeing other questions, i think it is.

I am doing a database design of processing exam marks. There is a marksheet for each of the answersheet. Marksheet is identified by reference of answer sheet. Each answer sheet is evaluated by 2 or more examiner.

I want to store the marks for each questions. So, if a question paper has 8 questions, there should be 8 entry for marks, some of which can be blank depending on whether the student has answered that question or something.

And there is a paper code associated with each answer sheet, which is unique for a given exam.

This is my AnswerSheet Table

Id (Primary Key)
Exam_ID (foreign key to exam table)
Paper Code (manually given for each paper, unique for a single exam)

Note that, by exam I am referring to a single course exam, not whole set of exams.

Here is my Marksheet table:

Id (Primary key)
Answer_sheet_id (foriegn key to the answer sheet table)
Examiner_ID (to identify the teacher evaluated this sheet)
Mark on Question 1
.
.
Mark on Question 8

Here, you see that I am hardcoding the question 1 to 8 in the database, which I think a bad design. My logic is that, It's very unlikely to change the no of questions in an exam in future. But, It's not absolutely guranteed.

I am seeking other alternative/better design for this problem?

There should be questions table that contain all questions with unique ID to each question. A new table reference AnswerSheet table Id and questions id  should be there.


Postgresql Streaming Replication Error: WAL segment removed I want to set up PostgreSQL streaming replication, but get the following error:

FATAL:  could not receive data from WAL stream:
ERROR:  requested WAL segment 00000001000000000000006A has already been removed.

Master IP : 192.168.0.30

Slave  IP : 192.168.0.36

On Master:

I have created a user rep which is used solely for replication.

The relevant files inside Postgres config directory (/opt/Postgres/9.3/data):

pg_hba.conf:

host    replication     rep     192.168.0.36/32   trust

postgresql.conf:

listen_addresses = 'localhost,192.168.0.30'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on

I've restarted the postgres service.

On Slave:

I've stopped the postgres service, then applied the changes to the two files:

pg_hba.conf:

host    replication     rep     192.168.0.30/32  trust

postgresql.conf:

listen_addresses = 'localhost,192.168.0.36'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on

For replicating the initial database I have done:

On Master:

Internal postgres backup start command to create a backup label:

psql -c "select pg_start_backup('initial_backup');"

... for transferring the database data to slave:

rsync -cva --inplace --exclude=*pg_xlog* /opt/Postgresql/9.3/data/ 192.168.0.36:/opt/Postgresql/9.3/data/

...for internal backup stop to clean up:

psql -c "select pg_stop_backup();"

On Slave:

I've created the following recovery.conf:

standby_mode = 'on'
primary_conninfo = 'host=192.168.0.30 port=5432 user=rep password=yourpassword'
trigger_file = '/tmp/postgresql.trigger.5432'

Starting the postgres service on the slave starts without any errors but is still waiting:

ps -ef | grep -i postgres

postgres 12959     1  0 13:39 ?        00:00:00 /opt/PostgreSQL/9.3/bin/postgres -D /opt/PostgreSQL/9.3/data
postgres 12969 12959  0 13:39 ?        00:00:00 postgres: logger process
postgres 12970 12959  0 13:39 ?        00:00:00 postgres: startup process   waiting 00000001000000000000006A

Meanwhile, on master:

ps -ef | grep -i postgres

postgres  5930     1  0 13:39 ?        00:00:01 /opt/PostgreSQL/9.3/bin/postgres -D /opt/PostgreSQL/9.3/data
postgres  5931  5930  0 13:39 ?        00:00:00 postgres: logger process
postgres  5933  5930  0 13:39 ?        00:00:00 postgres: checkpointer process
postgres  5934  5930  0 13:39 ?        00:00:00 postgres: writer process
postgres  5935  5930  0 13:39 ?        00:00:00 postgres: wal writer process
postgres  5936  5930  0 13:39 ?        00:00:00 postgres: autovacuum launcher process
postgres  5937  5930  0 13:39 ?        00:00:00 postgres: archiver process
postgres  5938  5930  0 13:39 ?        00:00:00 postgres: stats collector process

psql command on the slave gives:

psql.bin: FATAL:  the database system is starting up

--> cd pg_log gives reason for waiting:-

FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment  has already been removed

00000001000000000000006A segment is not in master's pg_xlog but it is in slaves pg_xlog

How can I solve this error?

From Streaming Replication in the PostgreSQL documentation:

  If you use streaming replication without file-based continuous archiving, the server might recycle old WAL segments before the standby has received them. If this occurs, the standby will need to be reinitialized from a new base backup. You can avoid this by setting wal_keep_segments to a value large enough to ensure that WAL segments are not recycled too early, or by configuring a replication slot for the standby. If you set up a WAL archive that's accessible from the standby, these solutions are not required, since the standby can always use the archive to catch up provided it retains enough segments.


Is it possible to make simple postgres dump using replication server? PostgreSQL 9.6, configured backup server via barman tool. Using WAL streaming (pg_receivexlog) and streaming backup (pg_basebackup).

All working good. However for development/debugging purposes I may need to create simple dump and copy it to localhost to inspect.

On master server I can simply use pg_dump tool. But I don't want to use it under heavy production load. So I was thinking - is there a way to create dump from our backup server?

Only one way I can think about: apply barman recover command to different server (not production), then there create simple dump via pg_dump. That will work, but isn't there a way to ditch intermediate server?

No, you can only run pg_dump against a running PostgreSQL server.

You can avoid the necessity to recover the database whenever you need a pg_dump by setting up a streaming replication hot standby server with max_standby_streaming_delay = -1 and run your pg_dump against that.


How to model a relational database for a sales business domain? I'm trying to come up with a solution that essentially boils down to visualising sales data, across many depts, in many stores, in many areas. The most significant challenge is a way to conceptualise/store this data in a meaningful way.

Store breakdown:

8 stores per area
3 areas per region
3 regions nationally

Department breakdown:

50 micro departments per store (think “chocolate”, “confectionary”, “headphones”) that belong to:
8 major departments (e.g. “chocolate” and “confectionary” belong to “grocery”, “headphones” and “CDs” belong to “entertainment”) that, in turn, belong to:
2 department divisions (each division would hold 4 of the major depts)

Sales data is generated on a weekly basis and then we aggregate it how we see fit. Each week a new spreadsheet is created and each store has three columns devoted to their store, which breaks down each micro department, the sales this year, the sales last year (and then we look at % gains/losses).

So what I'm trying to do is conceptualise a way to store the data meaningfully (all weeks, all departments, all stores) and then we'll perform some analysis on it later to extract what we need.

My current thinking is:

each store instance would have a store ID, name, area & region.
each micro department instance would have an ID, name and the major dept it belongs to
each week would have a week ID (we use financial weeks) and alternatively a date
we then create sales instances with a store ID, department ID, sales TY, $ sales LY and a week #

Is there a better way of representing the data or something that I'm missing?

I would establish 2 hierarchies:

region:
    region_id (PK)
    region_name
    ...

area:
    area_id (PK)
    region_id (FK to region)
    area_name
    ...

store:
    store_id (PK)
    area_id (FK to area)
    store_name
    ...

and

division:
    division_id (PK)
    division_name
    ...

department:
    department_id (PK)
    division_id (FK to division)
    department_name
    ...

subdepartment:
    subdepartment_id (PK)
    department_id (FK to department)
    subdepartment_name
    ...

Since store is tied to area and area to region, you don't need to directly tie store to region. Using ID values instead of embedding the area or region names directly simply allows those things to be renamed (and they will be). Same logical holds true for subdepartment.

I would build the sales_history table with the following columns:

store_id
subdepartment_id
sales_year - You may run your sales year from January through December, but this allows other options, without having to calculate the sales year off the date.
week_no - Again, this is something that could probably be calculated off the date you record, but having it explicitly simplifies searching, comparisons, and allows it to remain correct for past years, even if the calculation for a future year is different.
sales_date - Probably the first date in the week.
sales_amount
and, any other fields you need.

This will allow you to compare 2016 weeks 1-13 with 2017 weeks 1-13 at any level (store and department; region and subdepartment; whatever). You can also easily compare 2015, 2016, and 2017 (not as easy if storing this year's and last year's values in the same row).


SQL Stored Procedure with Output Parameter I know how to write a stored procedure with output parameters. But I don't know the reason why I would use it over just using a simple SELECT statement. A normal stored procedure can still return an output in a grid (see below examples).
Can anyone give a real life example where I can use an SP with output parameter over with SP without output parameter?
Examples
-- Using output parameter
SELECT @var = COUNT(*) FROM table1 WHERE gender = @gender...

-- Without output parameter
SELECT COUNT(*) FROM table WHERE gender = @gender...

Output parameters in stored procedures are useful for passing a value back to the calling T-SQL, which can then use that value for other things.

Let's say you have a stored procedure that returns a state given the input of a city, with state as the output parameter:

CREATE PROCEDURE [dbo].[GetStateFromCity] (@City NVARCHAR(30), @State NCHAR(2) OUTPUT)
AS
SELECT @State = [State]
FROM [ExampleTable]
WHERE [City] = @City
GO;

You can now use this output parameter to pass in a value elsewhere.

For example:

DECLARE @State int
EXEC [dbo].[GetStateFromCity] @City = 'Calgary', @State OUTPUT;

-- Do something with this value
SELECT @State;

-- Do something else
EXEC [dbo].[GetInsuranceCompanyByState] @State;

To summarise, if you just want to return a value for a client application, you probably don't need an output parameter.

However, if you want to pass values around in T-SQL between stored procedures, they can be very useful.

For what it's worth, I hardly use output parameters.


SSMS does not open popup wizard to create a new schema When I right click on 'schemas' and then on 'create new schema' my SSMS only opens the query editor but on every tutorial I saw a pop up wizard opens instead. How can I access this popup wizard? I can't find the right setting.
I'm currently following the course Developing SQL Databases on edx but I've also seen the popup wizard on youtube tutorials. I'm using the version 17.9.1 of SSMS

Many thanks in advance!

How can I access this popup wizard?

This popup is disabled for Azure SQL Database and Azure SQL Data Warehouse.  It works for on-prem SQL Server.

Why?  Probably because it didn't work as-is against Azure SQL DB/DW, and it wasn't deemed worth fixing.  This is a really low-value feature, as Schemas don't really belong to Security and creating them in TSQL is so simple.

If you run Profiler you can see that SSMS runs the following batch:

declare @MasterPath nvarchar(512)
declare @LogPath nvarchar(512)
declare @ErrorLog nvarchar(512)
declare @ErrorLogPath nvarchar(512)
declare @Slash varchar = convert(varchar, serverproperty('PathSeparator'))
select @MasterPath=substring(physical_name, 1, len(physical_name) - charindex(@Slash, reverse(physical_name))) from master.sys.database_files where name=N'master'
select @LogPath=substring(physical_name, 1, len(physical_name) - charindex(@Slash, reverse(physical_name))) from master.sys.database_files where name=N'mastlog'
select @ErrorLog=cast(SERVERPROPERTY(N'errorlogfilename') as nvarchar(512))
select @ErrorLogPath=IIF(@ErrorLog IS NULL, N'', substring(@ErrorLog, 1, len(@ErrorLog) - charindex(@Slash, reverse(@ErrorLog))))

declare @SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT

Which wouldn't work on SQL Database or SQL Data Warehouse.


Really slow DISTINCT ON query with multiple joins Originally posted:
https://stackoverflow.com/questions/11173717/expensive-query-on-select-distinct-with-multiple-inner-join-in-postgres

The songs table has only about 4k rows, posts and stations have less.  Running the query without the DISTINCT ON fixes it.

Running Postgres on Mac OS X Lion.

Song Load (7358.2ms)

EXPLAIN (426.2ms)

EXPLAIN for:
SELECT  DISTINCT ON (songs.rank, songs.shared_id) songs.*,
        songs.*,
        posts.url as post_url,
        posts.excerpt as post_excerpt,
        stations.title as station_title,
        stations.slug as station_slug
FROM "songs"
    INNER JOIN "posts" ON "posts"."id" = "songs"."post_id"
    inner join stations on stations.blog_id = songs.blog_id
WHERE "songs"."processed" = 't'
  AND "songs"."working" = 't'
ORDER BY songs.rank desc
LIMIT 24 OFFSET 0

                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Limit  (cost=546147.28..546159.16 rows=24 width=2525)
   ->  Unique  (cost=546147.28..547360.75 rows=2452 width=2525)
         ->  Sort  (cost=546147.28..546551.77 rows=161796 width=2525)
               Sort Key: songs.rank, songs.shared_id
               ->  Hash Join  (cost=466.50..2906.84 rows=161796 width=2525)
                     Hash Cond: (songs.blog_id = stations.blog_id)
                     ->  Hash Join  (cost=249.41..587.52 rows=2452 width=2499)
                           Hash Cond: (songs.post_id = posts.id)
                           ->  Seq Scan on songs  (cost=0.00..304.39 rows=2452 width=2223)
                                 Filter: (processed AND working)
                           ->  Hash  (cost=230.85..230.85 rows=1485 width=280)
                                 ->  Seq Scan on posts  (cost=0.00..230.85 rows=1485 width=280)
                     ->  Hash  (cost=140.93..140.93 rows=6093 width=30)
                           ->  Seq Scan on stations  (cost=0.00..140.93 rows=6093 width=30)

I tried a few things... first index on (rank, shared_id). Then removed that and added an index on rank and shared_id separately, as well as combinations of the three... no luck.

Are the indexes not being used for some reason? Or do I need to do anything after adding an index to ensure they work?

Because the WHERE condition of the query involves only equality checks:
WHERE "songs"."processed" = 't'
  AND "songs"."working" = 't'

and then you have:
SELECT  DISTINCT ON (songs.rank, songs.shared_id) ...

which is similar to GROUP BY songs.rank, songs.shared_id
I would first try adding a compound index on (first the columns in WHERE, then the columns in DISTINCT ON):
(processed, working, rank, shared_id)

The ordering: ORDER BY rank DESC may be better optimized if you have the index as:
(processed, working, rank DESC, shared_id)

Not really sure if this would contribute to efficiency but you can test.

Addition by @Erwin
As per request in comment
In principal (default) b-tree indexes can be scanned forward and backward at the same speed. But sorting can make a difference in multi-column indexes where you combine the sort order of multiple columns. The query starts with:
SELECT  DISTINCT ON (songs.rank, songs.shared_id)

In combination with ORDER BY rank DESC this dictates that the result be ordered by rank DESC, shared_id effectively. After the (simplified) WHERE clause WHERE processed AND working has been applied and before LIMIT can be applied.
I have my doubts if the DISTINCT clause is actually useful. But while it is there, the optimal index for the query should be (just as @ypercube suspected):
CREATE INDEX songs_special_idx
ON songs (processed, working, rank DESC, shared_id);

Looks like one of the rare cases where explicit ordering of index columns would benefit the query. There is an excellent explanation in the chapter Indexes and ORDER BY of the manual.
If the WHERE condition is stable (always WHERE processed AND working), a partial multi-column index would be smaller and faster, yet:
CREATE INDEX songs_special_idx
ON songs (rank DESC, shared_id)
WHERE processed AND working;


Possible to update rows with data only accessible via API call? I'm not good at what I'm working on so apologies if some of my terminology is off. Also not sure if this belongs here, or in a more programming oriented area.

I am looking for options to update rows of a mysql database with data only accessible via REST api.

In other words... I will create a row with some of the data needed. Then I have 4-5 columns I'd like to update with data that I need to gather from the output from an API call

"Columns" look different (dates vs strings vs numbers); the values within a given column all look the same (eg, all are dates)
"Rows" look similar, but with different values.
A "table" is a collection of any number of rows, but of a fixed number of columns.
An API call must go to some application language code (Java, PHP, or ...) which interprets the yucky REST syntax and proceeds to, for example, issue a SELECT (for a GET REST call) or INSERT/UPDATE (for a PUT), etc.  You must write this app code; MySQL does not understand REST.
I suspect REST is so restrictive that a PUT is not allowed to return what a GET would return.  So, perhaps you have to do 2 API calls.  This also happens with talking to MySQL -- an INSERT or UPDATE merely says 'ok' or 'err'; you then need to do a SELECT to fetch stuff.

I hope this helps.  To continue this Q&A, please provide

More details about your application.
Whether you have settled on an app language to code REST in.
Some hints of what column types your table(s) has.

(Otherwise, it will probably be closed as "too broad".)


Cloning a Db2 database within a single instance I need to create an exact copy of a Db2 database (V11.1+) on the same server within the same instance. (This is a development server that does not run production databases.)

This is how I do it now.

Dump tables using a hand-written script
Create a new database
Import service functions specific to my application to the database
Create a schema in the new database
Import data from a dump into the new database
Set database configuration parameters for the new database

I read that it is possible to copy a database scheme with db2move.
Is there a simple approach that could be used to perform a full copy?

This is a minimial example of copying a database (test) to a into a new database (test2)

db2 create database test
db2 backup database test to /tmp
db2 restore database test from /tmp into test2
db2 connect to test2

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.1.3.3
 SQL authorization ID   = DB2INST1
 Local database alias   = TEST2


postgresql sticking "enable_seqscan=False" in a UDF sensible? I have a query which is taking 40 seconds to execute over the 80ms that it should if postgres where to filter off the primary key rather than doing a sequential scan. I have to force postgres to filter off the index by setting the above flag.

Having to stick this into the UDF seems like quite a hack to me, I mean it's unreasonable IMO that the query planner is chosing a seq scan over a indexed filter. Most of my SQL experience is off SQL Server, and I haven't experienced it missplaning such basic optimizations.

Edit:
To anyone interested, the query exeuction time droped from 80ms to 27 ms with 9.2, holly molley.

enable_seqscan=true doesn't do what you think it will.  It only allows the planner to choose that.  It doesn't force it.

Note it should be on generally anyway.  Sequential scans are far less costly in PostgreSQL than in MySQL since they scan in physical order.  There are many times when they are the cheaper plan.


How do I troubleshoot error 26: "Error Locating Server/Instance Specified" I'm trying to configure TCP access to my SQL Server named instance (named SQL2012). I use management studio to connect to the database engine. I type in the server name: "tcp:localhost\SQL2012" and I receive this error message:

  (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

This post by the SQL Server team seems to be really useful, in it they suggest testing out the SqlBrowser service using PortQry. When I do, I get this info:

Querying target system called:

 localhost

Attempting to resolve name to IP address...

Name resolved to 127.0.0.1

querying...

UDP port 1434 (ms-sql-m service): LISTENING or FILTERED

Sending SQL Server query to UDP port 1434...

Server's response:

ServerName MSWART7
InstanceName SQL2012
IsClustered No
Version 11.0.5058.0
tcp 52329

   ♥K   K P☺K 5 - 2 1 qK 1 4  rK 8 7 0☺    1 qK

==== End of SQL Server query response ====

And sure enough, I'm able to connect to the database when I specify the server name "tcp:localhost,52329"

I'm not sure what I'm missing. SQL Browser seems to be functioning and ports and firewalls seem to be open or irrelevant (I'm trying to connect to localhost).

Where do I look next?

When you try to connect to a local instance, SQL Server will always attempt to use Shared Memory. This connection will work fine:

localhost\SQL2012

This works in my scenario, and the connection is indeed using Shared Memory in this case.

SELECT session_id,net_transport,local_net_address,local_tcp_port
FROM sys.dm_exec_connections;

Results:

session_id  net_transport   local_net_address   local_tcp_port
----------  -------------   -----------------   --------------
53          Shared memory   <local machine>     NULL

If you want to try to override Shared Memory (not sure why, on a local machine) and force TCP/IP instead, you can't mix that with specifying the named instance explicitly, at least in all instances I tried (no pun intended). The named instance forces SQL Server to map the instance name to the port number, using SQL Server browser, but this apparently can't be done when you force TCP/IP in the first place (the whole point of using TCP/IP is so you go straight to the port and not bother with the browser). If your named instance happens to be running on its own IP address and forced to port 1433, you may be able to get this to work; you may also be able to get it to work if you are using a Client Network alias (but I could not).

I was able to connect if I looked up the current dynamic port assigned to that instance in Configuration Manager (in my case it was 49538), and then connect using tcp:localhost,49538:

So now:

SELECT session_id,net_transport,local_net_address,local_tcp_port
FROM sys.dm_exec_connections;

Results:

session_id  net_transport   local_net_address   local_tcp_port
----------  -------------   -----------------   --------------
53          Shared memory   <local machine>     NULL
54          TCP             10.211.55.10        49538

All variations I tried - across multiple machines and versions - where I tried to combine a named instance with overriding shared memory and use TCP/IP instead - yielded the same error that you received. This is both when I prefixed the server/instance name with tcp: and when I specified TCP/IP for the network protocol in the connection properties dialog.

Long story short: If you really need to force TCP/IP for a local connection, you're going to need to use a fixed port for that instance, and specify the port. Or use 1433 (which will only work if there's no default instance or if you have a dedicated IP address for that instance), then you won't need to specify the port, but you'll still need to specify the tcp: prefix or use that option in the connection dialog.

I do not suggest trying to disable Shared Memory. FWIW.


extract values from different stored procedures and create one report At the company I work, the third party taking care of the database. So there is  giant complex stored procedure that produces the result for one parameter only. When management needs a desirable report - they run one parameter, grabbing the sum of money for that parameter. Then running next parameter,  grabbing another sum for parameter and so on. After that they inserting all that sums into excel spreadsheet into a table.

So my job is to automate all this process.
Is any way the same stored procedure can run particular parameters - insert result in tempTable, then run next parameters - insert result in same tempTable and so on.
Do you guys know what I mean? The result of different sets of parameters should be like on a picture below

@GLCompanyGuid  varchar(8000)       =   'CpmpanyName_1',
@PostDateFrom datetime              =   '01-01-2016',
@PostDateTo   datetime              =   '01-31-2016',
@CompanyLocationGuids varchar(8000) =   'CompanyLocation_1'
@CarrierLocationGuids varchar(8000) =    Carrier_1,
@LineGuid     varchar(8000)         =   'Line_1'

--Now I need to run same store procedure but with different parameters value and insert the result at the same TempTable

@GLCompanyGuid  varchar(8000)       =   'CpmpanyName_2',
@PostDateFrom datetime              =   '01-01-2016',
@PostDateTo   datetime              =   '01-31-2016',
@CompanyLocationGuids varchar(8000) =   'CompanyLocation_2'
@CarrierLocationGuids varchar(8000) =    Carrier_2,
@LineGuid     varchar(8000)         =   'Line_2'

--So how can I make one stored procedure run through one set of parameters, insert values in temp table, then run through another set of parameters - insert values in temp table and so on?[![enter image description here][1]][1]

If the stored procedure only returns one result set, and you know the column names in advance, then you can create a temp table and Insert #TableName Exec ProcedureName. You would then select the value you want into a variable from the temp table, and run the procedure again.

If it only returns one result set but you don't know the column names in advance, you can use this solution (https://stackoverflow.com/a/1228165/401970) to call the procedure within an Openrowset and put the results into a temp table it creates for itself with the returned column names.

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

If the procedure returns multiple result sets then you're not going to be able to do a solution in T-SQL only. You can write a simple PowerShell script to do it, but that's not really going to help if they need to get the result directly from the database.


Uninstall SQL Patch with SkipRules I've recently installed Cumulative Update 6 for SQL Server 2014 SP2. I installed it on the passive node of a 2 node cluster. To install the patches I paused the role in cluster manager and ran the setup from command line with:

/SkipRules=Cluster_IsOnlineIfClustered /action=patch

I now need to uninstall CU6 as we've come across some issues we need to resolve. However, I am unable to uninstall the patch as I cannot get past the rule checks. If I run the same command to start setup the UI does not give me the uninstall/maintenance options.

Is there anyway to skip rules to uninstall a patch, or am I going to have to remove this node from the cluster before I can proceed?

With confirmation from Pix(from Chat) the correct way to remove cumulative update is to use
/RemovePatch instead of using /uninstall. The way to remove updates is documented in Installing Updates from Command Prompt.

The script would be

"sqlserver2014-kb4032541-x86_080a5c3a38b721a9587c2072375921161896e19d.exe" /qs /Action=RemovePatch /InstanceName=InstanceName /SkipRules=Cluster_IsOnlineIfClustered


Access Macro: ApplyFilter w/values from 2 combo boxes I'm getting started with databases in Access for a work project (technically a work-learn for my non-CompSci degree). I have experience using multiple filters on multiple columns through Access/Excel, but for my work learn I'm trying to design an interface with some "quick filter" functionality built into the UI. Since I'm pretty green, I'm using macros instead of VBA which seems to be doing the job fine... But I've hit a snag, and was hoping y'all might be able to help?

I have a split-view form with top containing record details and quick search options, and the bottom containing the table itself as filtered (which serves as a list of results). What I'm trying to do is filter a column by the selections from two dropdowns on a form.

I was able to do this by doing:

ApplyFilter WHERE Table!Articles!IntCodes LIKE "*"IntCombo1 & "*" & IntCombo2 "*"

But then I realized that while that will find entries where IntCombo1 and IntCombo2 appear in order, (e.g. Dog, Cat) it won't show ones where they appear reversed (Cat, Dog).

I would think that the way to fix that would be one of:

ApplyFilter WHERE Table!Articles!IntCodes LIKE "*"IntCombo1 & "*" & IntCombo2 "*" OR "*"IntCombo2 & "*" & IntCombo1 "*"

or

ApplyFilter WHERE Table!Articles!IntCodes LIKE ("*"IntCombo1 & "*" & IntCombo2 "*") OR ("*"IntCombo2 & "*" & IntCombo1 "*")

or

 ApplyFilter WHERE Table!Articles!IntCodes LIKE ("*"IntCombo1 & "*" & IntCombo2 "*" OR "*"IntCombo2 & "*" & IntCombo1 "*")

But none of these work, and I'm not sure why because they all seem pretty boolean-ly sound. Can someone tell me what I'm doing wrong?

(Other perhaps relevant info is that the entries on the table I'm filtering are short text words/phrases separated by semicolons ("Dog; Cat; Mouse; Horse", "Mouse", "Cat; Dog", etc...). I totally own that this should probably be converted into a table (long term goal), but the dataset was created over 5 years in an Excel spreadsheet, and therefore is a beast so I haven't gotten around to doing that yet.)

Oh.

Well, I figured it out. I didn't realize that when I use OR, the macro parses it like WHERE (X LIKE Y) OR (X Like Z).

The solution is as follows:

ApplyFilter WHERE Table!Articles!IntCodes LIKE "*"IntCombo1 & "*" & IntCombo2 "*" OR Table!Articles!IntCodes LIKE "*"IntCombo2 & "*" & IntCombo1 "*"

I was hoping to be a little more efficient, but apparently macros don't dig that. :( Any other comments still appreciated!


CmdExec Powershell Job Step Escape Double Quotes I have a PowerShell job step I'm trying to execute and it's failing with a syntax error I believe is because I'm trying to escape double-quotes in my code. Can anyone take a look and help me with getting the escape characters right, or tweaking my code.

powershell.exe "Compress-Archive -Path \\NAS\DB-backups\BackupFiles -DestinationPath `"\\NAS\DB-Backups\BackupFiles $(get-date -f yyyy-MM-dd).zip`" -CompressionLevel Optimal"

I've also tried it using \ as the escape character

powershell.exe "Compress-Archive -Path \\NAS\DB-backups\BackupFiles -DestinationPath \"\\NAS\DB-Backups\BackupFiles $(get-date -f yyyy-MM-dd).zip\" -CompressionLevel Optimal"

I tried running this script (with \ escape characters) in the command line and it ran fine. Thanks again for any help.

I believe the problem you're having has to do with SQL Server Agent Tokens as referred to by this Stack Exchange post SQL Agent - PowerShell step “syntax error”

Excerpting from that post

  This is a not very intuitive and I was never able to find anything
  concrete on the explanation [e.g. no exact BOL or white paper was
  found]. The syntax error in the SQL Agent job is a T-SQL syntax error
  based on User Tokens. So that basically means that a PowerShell
  Sub-Expression Operator is treated as a Token to SQL Server Agent. So
  in PowerShell this $( ) appears to be treated as reserved character
  sequence for SQL Server Agent.

Referencing your attempts,

$(get-date -f yyyy-MM-dd).zip

notice the $( where you're trying to get the current date for the zip file.  That syntax is a Powershell sub-expression. This part is attempting to be interpreted by the SQL Server Agent as a token.

My suggestion would be to separate out the sub-expression into another Powershell variable ($ZipDate) first and then use that to finish the Powershell command.

Try this:

powershell.exe "$ZipDate = (get-date -f yyyy-MM-dd); Compress-Archive -Path "C:\TestCompress" -DestinationPath "c:\TestCompressOut\$ZipDate.zip" -CompressionLevel Optimal"


How to access objects created by a different user User A created few objects with schema "UserAPriv", but now he is either leaving the company or changing the department.
Now we need to restrict his access to his previously created objects and make some arrangements so that other team mates from previous project can access his objects and work normally

Now we need to restrict his access to his previously created objects
  and make some arrangements so that other team mates from previous
  project can access his objects and work normally

It may be the case that your user was db_owner, in this case all the objects he created are already owned by dbo, in this case you should do nothing at all and simply drop the user:

drop user A;

There may be a situation when the user owns some schema and in this case database engine will prevent you from dropping user:

  The database principal owns a schema in the database, and cannot be
  dropped.

To be able to drop a user-owner of a schema you should first change the ownership of a schema:

alter authorization on schema::UserAPriv to dbo

Now you can try to drop the user and he will have no access to your database at all.

If the principal was also a grantor of permissions, database engine will prevent you from dropping user again:

  The database principal has granted or denied permissions to objects in
  the database and cannot be dropped.

in this case you should first find all the permissions where he is a grantor:

    select user_name(grantee_principal_id) as user_,
           object_name(major_id),
           permission_name,
           state_desc
    from sys.database_permissions
    where grantor_principal_id = user_id('A');

Then grant all these permissions as dbo, and then revoke them as User A:

grant select on UserAPriv.t1 to SomeUser as dbo;
revoke select on UserAPriv.t1 to SomeUser as A;

At this point you can drop User A.


Why ALTER DATABASE .. MODIFY NAME reports an error while succeeding at the same time? I am running the following query in SSMS:

SET NOCOUNT ON
ALTER DATABASE [xyz] MODIFY NAME = [xyz_]

And get this:

The database name 'xyz_' has been set.
Msg 911, Level 16, State 1, Line 2
Database 'xyz' does not exist. Make sure that the name is entered correctly.

So, the query fails, but the database is still renamed!

What is going on?

One possibility is that there's a DDL trigger that fires for ALTER DATABASE, and that trigger code try to refer to the (now old) database name.


Saving output of \ commands to disk When I try to run the following command:

my_db> \copy \dt *. to /home/john/list.txt

I get an error:

\copy: parse error at "*"

Why? How can I save the output to disk?

Use the \o command as documented in the manual:

http://www.postgresql.org/docs/current/static/app-psql.html

specifically:

> \o <path_to_the_file_where_you_want_the_output>
> [your command]
> \o

the last statement switches the output of psql back to stdout.


MySQL GTID replication: is it possible to make DDL changes to a slave and then catch it up to a master? Here's what I would like to do:
1. hang a slave off my production master.
2. stop replication
3. adjust some data types and create some indexes on the slave (no data mods)
4. restart replication
5. once the slave has caught up, lock the db & swap slave & master

-the goal, obv. is to have no significant downtime while making some changes that will take time (200G table getting modified)
-it should be possible, because the DDL changes I'm making have to do with shrinking data types & adding constraints - nothing that changes the actual values of data.
-since GTID replication is row-based, I don't know if replication will choke trying to insert, for example, 11 digit ids into a 5 digit id column, even if the values are in range.

You have described a common way to make DDL changes.  Yes, it should work.  But...  please describe the datatype change further.

If you are ALTERing a COLUMN that is current INT SIGNED to be a SMALLINT SIGNED, and if none of the values are bigger than 32767, there should be no problem.  If you are changing from SIGNED to UNSIGNED, and none of the numbers are negative, then no problem.  Etc.  In general, if the old value will fit in the new datatype, then it is OK.  If it won't fit, then something will be lost.  1 million into a SMALLINT UNSIGNED will probably become 65535.

As for the application, how does mysql know it is getting an 11-digit id?  It sees a number of some number of digits and shoves it into the target size.

Do SHOW WARNINGS; after the DDL.


For a standby workload of long running analytical queries, is it better to turn on hot_standby_feedback or set max_standby_*_delay settings to -1? When spinning up a hot standby server specifically for BI/Analytics purposes where long running queries may be common, is it better to turn on hot_standby_feedback or set the max_standby_*_delay settings to -1?

My understanding is that the hot_standby_feedback prevents the master from doing things like VACUUM until it is safe to do likewise on the standby where the max_standby_*_delay settings allow VACUUM to commence on the master but the standby, if necessary, waits to apply any vacuum cleanup that could conflict with a long running query.

Furthermore, the docs state for hot_standby_feedback:

  Remedial possibilities exist if the number of standby-query cancellations is found to be unacceptable. The first option is to set the parameter hot_standby_feedback, which prevents VACUUM from removing recently-dead rows and so cleanup conflicts do not occur. If you do this, you should note that this will delay cleanup of dead rows on the primary, which may result in undesirable table bloat. However, the cleanup situation will be no worse than if the standby queries were running directly on the primary server, and you are still getting the benefit of off-loading execution onto the standby.

And for max_standby_*_delay the docs state:

  If the standby server is tasked as an additional server for decision support queries then it might be acceptable to set the maximum delay values to many hours, or even -1 which means wait forever for queries to complete.

It's still unclear to me which is better and what the exact pros and cons of each are?

With hot_standby_feedback on, vacuum can still be done, but it will be less effective as some tuples that would otherwise be vacuumed now have to be delayed to a later vacuum.  The only real downside that I know of is the increased bloat. How much of a downside this is depends entirely on your usage.  The worst case is if your database has small, intensively updated tables, like a work-queue table.  Those could get extremely bloated.  If you don't have that kind of table, you probably won't have a problem.

The problems with max_standby_*_delay are that other queries running on the standby are also having their horizon held back by potentially large amounts, and that if you hold back the stand-by long enough accumulated un-replayed WAL files will fill up your hard drive and you will lose the stand-by.


How to edit publication's generation_leveling_threshold configuration value I dont know where I put this code:

update sysmergepublications set [generation_leveling_threshold] = 0

I only need do it. But I need edited any funcion of agent.

I think what you may be looking for is sp_changemergepublication.

This stored procedure will allow you to modify generation_leveling_threshold value.


Why MongoDB starts to split before 64MB I'm testing the MongoDB Sharding with a Sharded Collection and a forloop to insert arround 1M documents to see how splitting and moving works.

I'm surprise that after few documents MongoDB starts to split chunks (well before 64MB),

At the end of the 1M (and some) inserts i have thoses stats :

mongos> db.users.getShardDistribution()

Shard rs0 at rs0/mongod00.local.net:2000,mongod01.local.net:2001,mongod02.local.net:2002
 data : 84.76MiB docs : 1010010 chunks : 9
 estimated data per chunk : 9.41MiB
 estimated docs per chunk : 112223

Totals
 data : 84.76MiB docs : 1010010 chunks : 9
 Shard rs0 contains 100% data, 100% docs in cluster, avg obj size on shard : 88B

Why MongoDB created 9 chunks of a collection that contains 85MB?

I didn't change the defaut chunk size parameter:

mongos> db.settings.findOne()
{ "_id" : "chunksize", "value" : 64 }

Thanks,

Max.

The mongos processes control when automatic splitting happens (you can also pre-split, and split manually).  The heuristic they use is a bit more complicated than I am about to describe, but you can use it as a rough guide:

Each mongos keeps track of data written to a particular chunk
At ~20% of the max chunk size (default is 64MB), it will try an autosplit
If valid split points are found and returned (by splitVector), it will split
If not, it will try again at 40% (+20%)

Hence if you have inserted enough data for valid split points to be found, you will see splitting before the 32MB mark (number of documents are a valid reason to split, it's not all size related).

If you do not wish for this to happen, you can start the mongos you will be using to write data with the --noAutoSplit option or you could run with a config server down (which means all splits will fail when attempted because the meta data is read only).  Generally, that would not be recommended for production, but can sometimes be needed for testing purposes.


Postgresql assign mutliple row query result to variable and continue execution I'm trying to create a script that calls several stored procedures in PostgreSQL and print the result of some of them:

CREATE OR REPLACE FUNCTION script(
vtoken character varying)
  RETURNS void AS
$BODY$
DECLARE
  aux character varying;
BEGIN

PERFORM (SELECT acquire_table(vtoken));

-- This query gets the dynamically generated token for the client in that codebar
aux := (SELECT token
                FROM clients c JOIN tables t ON c.tablekey = t.tablekey
                WHERE codebar = vtoken);

PERFORM (SELECT order_drinks(aux, '{1}', '{1}'));

--EXECUTE (SELECT issue_ticket(aux));

PERFORM (SELECT order_drinks(aux, '{1}', '{1}'));

END;$BODY$
  LANGUAGE plpgsql VOLATILE

The issue_ticket() function returns a multiple row result, I'd like to print it directly and continue execution of next function (order_drinks()). If I do PERFORM it does not show the results, and if I just try to execute the SELECT query as it is, it asks to assign return value to variable.

The usual approach is to return a rowset using returns table.  The client calling the function can then print it.  The function itself continues to run after the return.  For example:

create or replace function script()
RETURNS TABLE (col1 int)
language plpgsql
as $_$ begin
RETURN QUERY (VALUES (6), (7), (6*7));
RAISE NOTICE 'continuing after return';
end;
$_$;
select * from script();

This prints:

NOTICE:  continuing after return
 col1
------
    6
    7
   42
(3 rows)

You can also store the result of the function in a (temporary) table:

create or replace function script2()
returns table (col1 int)
language plpgsql
as $_$
begin
CREATE TEMPORARY TABLE temp_table (id int) ON COMMIT DROP;
INSERT INTO temp_table SELECT * FROM script();
return query (select * from temp_table);
end;
$_$;
select * from script2();

This prints:

NOTICE:  continuing after return
CONTEXT:  SQL statement "insert into temp_table select * from script() s where s.col1 < 42"
PL/pgSQL function script2() line 4 at SQL statement
 col1
------
    6
    7
(2 rows)


How to backup a large (100gb+) microsoft sql database for occasional "offline" use? I have a Microsoft SQL database that's over 100gb currently in service. It's due to be decommissioned but I want to keep the data for occasional use without paying for a Microsoft SQL licence (currently provided as part of a monthly subscription by my server provider). Unfortunately Microsoft SQL express has a size limit that is much lower than my database size. What options do I have for retaining this data for only occasional use by a single user?

If the primary goal is to save money (i.e. not licensing Standard, let alone Enterprise, edition) you’ll need to be creative and accept a certain amount of inconvenience. Some ideas:

Purge any data you know you don't need
Break it up into smaller database that do fit on Express (by date, by table, by subject, whatever fits)
Export it to an open source system (Postrgres, MySQL)
Save it in on the cloud (Azure, others), using a plan that best supports infrequent access
Store it outside of a database, on a per-table basis (ASCII file, Spreadsheet)


How to take backups on a defined directory instead of creating sub-directories I am using Ola-Hallengren scripts and now I have a situation in which I require to take backups without creating sub-directories for each DB. I need to define a centralized path like C:\Backups for all FULL, DIFF and LOG backups.

Any help would be appreciated.

You just need to change the Directory parameter in the Ola Hellengren script. Go to the job, click the Step --> Edit-->@Directory = 'C:\Backup',

This will be enough.

Adding to your particular requirement, I went into the Store proc and was able to figure out. This took me some time so apologies for that. Please mark this as answer once you are able to complete your task.

Below are the steps to get your required directory :

For getting all backups to just one location, you will need to remove few parameters from the store proc [dbo].[DatabaseBackup]. There will be this part where you set variebles( Look for the comments) -- Set variables
Go ahead and look for the below part :

  SELECT ROW_NUMBER() OVER (ORDER BY ID), DirectoryPath + CASE WHEN
  RIGHT(DirectoryPath,1) = @DirectorySeparator THEN '' ELSE
  @DirectorySeparator END + CASE WHEN @CurrentAvailabilityGroup IS NOT
  NULL THEN @Cluster + '$' + @CurrentAvailabilityGroup ELSE
  REPLACE(CAST(SERVERPROPERTY('servername') AS nvarchar(max)),'\','$')
  END + @DirectorySeparator + @CurrentDatabaseNameFS +
  @DirectorySeparator + UPPER(@CurrentBackupType) + CASE WHEN
  @ReadWriteFileGroups = 'Y' THEN '_PARTIAL' ELSE '' END + CASE WHEN
  @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END, Mirror, ROW_NUMBER()
  OVER (PARTITION BY Mirror ORDER BY ID ASC), 0, 0   FROM @Directories
  ORDER BY ID ASC

Now you need to remove the parameters : @CurrentDatabaseNameFS and UPPER(@CurrentBackupType). This will create all your backups to a single location as below

Go through each of the available parameters in Ola hellengren scripts. Ola Hellengren


Meaning of SQLIO output "counts per second" I realise that my question isn't strictly 'database' but I know that a lot of DBA's will use the SQLIO tool to check I/O performance on their systems so I'm hoping that somebody here will be able to answer this question:

When I run SQLIO the 3rd line of output says something like:

using system counter for latency timings, 14318180 counts per second

What does that mean?  I get different values on different systems, and because I'm comparing different systems I would like to know what that number is telling me (if anything).

It's an informational message, which can be ignored.

This is just the conversion factor between the timer mechanism the program uses internally and actual, real time. See this for a bit of background.

The per-second metrics that the program outputs are the ones you want to focus on. Have a look at this blog post for a tutorial on how to use SQLIO and interpret its output.


Query performance drastically depends on the database in use I'm on SQL-Server 12.0.5203.0
There is a query covering multiple databases. I think It is not useful to post the actual query (as it is rather complex and confidential, just think of a query like
SELECT t1.Column1
      ,t2.Column2
      ,t3.Column3
FROM db1.schema.TableName1 t1 with (nolock)
JOIN db1.schema.TableName2 t2 with (nolock) ON SomeCriteria
JOIN db2.schema.TableName3 t3 with (nolock) ON SomeCriteria
JOIN db3.schema.TableName4 t4 with (nolock) ON SomeCriteria
WHERE SomeCriteria
  AND t4.ColumnA = (SELECT DISTINCT ColumnX
                      FROM db2.schema.TableName5 with (nolock)
                      WHERE ColumnY = 2902)

Please: This is not my query... Do not discuss the usage of WITH(NOLOCK) or GROUP BY against DISTINCT, thx :-D
Users reported time-outs due to run durations of more than a minute. I could not reproduce this, as exactly the same query in exactly the same environment brought back exactly the same result in less than a second.
Then - by setting all possible configurations to the same values - I encountered something strange: The difference depends on the database in use.
This is reproducable: If there is USE master; it is 1 second, with USE db1; (or any other) it is horribly slow.
Some general observations

All used tables are fully qualified with database.schema.table and aliased
All tables are called with WITH(NOLOCK)
The profiler for the fast run shows CPU(30), Reads(20000), Duration(30)
slow (same SSMS, just two windows): CPU(11000), Reads(13M!!!), Duration(12000)
The execution plans are extremely different

slow: Starts with an index seek returning 2.6M rows
Combines this with another index seek pushing this to 27M rows (estimnated 45!)
Filters this down to 1626 rows, which is the count of the final result
fast: starting with tiny sets of some 100 rows, Never more than 8000 rows

My questions

What is going on here? Why is the database, where I'm coming from, so important?

Lessons learned and a solution
What I did not know: Query plans are stored with each database separately, hence the context database can be very important. I cannot really grasp the advantage of this concept... Why not better store the plans in a central place, one per action? But this is a different question...
Using a query against sys.dm_exec_cached_plans and dm_exec-query_plan() with query_plan.exist('//*:StmtSimple[contains(@*:StatementText[1],"Some specific part of the query")]')=1 I found a couple of stored plans. After removal everything was fast and fine.
But the next day the bad behavior was back.
The solution: After a thorough look into the best plan I re-organised all JOINs to this order of execution and use OPTION(FORCE ORDER). This seems to solve the issue.

At a high level, database context is one of the keys that make a cached plan unique (there are other things, like certain session settings, the query text obviously, etc).

Therefore, it is quite possible that the first time this query was run in one database, it compiled (and is now stuck with) a very different plan than when it was compiled for some other database.

There are many reasons why a plan could be different, depending what happened between the first compilation and any subsequent compilations, but now that you have two plans, it is irrelevant that you are issuing the USE commands in the same window. You're still going to use the plan that was compiled for that database. Quick repro:

USE AdventureWorks;
GO
SELECT TOP (1) Monkey = BusinessEntityID
  FROM AdventureWorks2012.Person.Person;
GO
USE OtherDB;
GO
SELECT TOP (1) Monkey = BusinessEntityID
  FROM AdventureWorks2012.Person.Person;
GO
SELECT * FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.[text] LIKE '%Monkey%';

You get three rows - ignore the first, because that is just the query I ran to verify (which is cached before execution, so adds itself to the resultset), and keep in mind if you run a zoo database or work at SurveyMonkey, you may have to filter further:

Clearly you can see we got a plan for AdventureWorks and a plan for OtherDB, even though this is the same instance of SQL Server. Again, I can't go back in history and tell you why or when you got two different plans, but it's obvious to me that this is a possible explanation. Easy for you to verify, of course.

So, one more time, please try issuing the query with OPTION (RECOMPILE) when you USE <slowdatabase>;, or otherwise evicting those specific plans from the cache and trying again:

DBCC FREEPROCCACHE(0x...); -- where 0x... is the plan_handle


Can I have two `psql` sessions use the same database connection? Is it possible to open two psql sessions that use the same database connection?

My goal is to show that a second user on the same connection can see uncommitted INSERTs, wherease a user on a different connection can't see INSERTs until the first connection runs COMMIT on its transaction.

Is it possible to open two psql sessions that use the same database
  connection?

In theory it's possible through connection sharing. Instead of connecting directly to PostgreSQL, psql would connect to a middleware such as pgBouncer, which has the ability to reuse the same backend connection across its multiple clients.

  My goal is to show that a second user on the same connection can see
  uncommitted INSERTs,

TL;DR: forget that, run the SELECT in the same psql session than the INSERT.

From the point of view of PostgreSQL, there is no second user, it's the same user on the same connection, which is seeing the effects of its own not-yet-committed INSERTs.
Otherwise it would be a dirty read, which PostgreSQL documentation states as "Not possible" in any of the isolation levels.

A middleware routing queries from different connections from its clients to the same connection to the backend could produce the effect of a dirty read to its client.
In practice, your goal is out of reach with a normal pooler, because these tools actively forbird situations where a client would see uncommitted changes of another client. It's part of the effort to be transparent to users, to whom the results should be the same whether their connection is shared or not.

For example PgBouncer when using statement pooling, says:

  Statement pooling
      Most aggressive method. This is transaction pooling with a twist - multi-statement transactions are disallowed. This is meant to enforce
  "autocommit" mode on client, mostly targeted for PL/Proxy.

The mentioned demo would need statement pooling in a multi-statement transaction.
So in order to make it work, you'd need to hack pgBouncer to bypass that check. That's technically possible, but in the end it would demonstrate a setup that never exists in practice. In this sense, it has more potential to confuse people rather than to help them understanding how isolation works.


How to retrieve subtotal for order lines (rollup does not exist in sql 2000) Currently in a crystal report I have each order grouped together where the user is displayed the order lines in the orders group. I have made a running total field for a subtotal for all of the order lines prices. But I wan't to make a parameter for the end user to select whether the price is > or < than a specific amount. And what I thought may be the best solution is calculate the subtotal in the stored procedure and pass it to the report to build the parameter off of. But it looks like SQL Server 2000 does not contain the ROLLUP function in later versions.

Figured it out:

select TS.Order_no, part_no, cost_per_line,
       case when Order_no = (select top 1 Order_no from #tempsales
                           where cust_po = TS.cust_po
                          order by Order_no asc)
            then (select sum(cost_per_line)
                     from #tempsales
                     where Order_no <= TS.Order_no
                        and cust_po = TS.cust_po)
            else '0' end as 'Sub Total',
       case when Order_no = (select top 1 Order_no from #tempsales
                           where cust_po = TS.cust_po
                          order by Order_no asc)
            then (select CAST(ROUND(SUM(cost_per_line), 2, 1) AS DECIMAL(18, 2))
                      from #tempsales)
             else '0' end as 'Grand Total'
from #tempsales TS


Understanding linking tables in a many-to-many relationship I am still working on a photo storage app (designing the database for the data for the photos stored), using MySQL, and have been told that a photo may have one or more people in it, and a person may be in many photographs, which makes sense.
My issue is in understanding the properties of the linking table between the photo and person tables. I have set up a linking table with the PK of both tables (Photo and Person) as the composite PK for the linking table. Both sets of PK in this table are set to Not Null (by me) but the project then stipulates that people can be in the database even if they aren't in a photo, and I was wondering how this tallies with the NN setting of the composite PK. Surely if it has to be NN, this means it can't allow for there to be any entries where I just have the PK of the photo and none for the person part of the linking table? Or am I totally misunderstanding how these relationships work? As mentioned previously, I am in the early stages of learning about databases.

Actually you don't need a special value for people that aren't there
When you select a photo andand you don't know if there aren't any people in it, you make
SELECT p.*,pe.*
FROM Photo p
   LEFT JOIN reftable r ON p.id = r.p_id
   LEFT JOIN peope pe ON  r.pe_id = pe.id
WHERE p.id = 123

So even when there is no entry in the Relationship table, you get an photo.
If you have one person and want to find all photos that they are in
SELECT pe.*,p.*
FROM peope pe
   INNER JOIN reftable r ON r.pe_id = pe.id
   INNER JOIN Photo p  ON p.id = r.p_id
WHERE pe.id = 123

You need here a relationship table, because many persons could be on a photo or one person can be in many photos


CREATE DATABASE Permission denied in database 'master' error I'm working with a tool that as one of the automated steps will attach databases from my file system to my local SQL Express 2008 instance. The tool requires a login in the sysadmin server role, for which I've created a new dummy login for testing. When I log into Management Studio with this new login and run the exact query to attach the DBs, the DBs are attached fine. However, when I run the tool (its an executable program) I am able to turn on the tool's debug panel to see the following error:

  CREATE DATABASE Permission denied in database 'master'

The T-SQL being executed via the program is:

USE [master]
GO
CREATE DATABASE [BBB_core] ON
( FILENAME = N'c:\inetpub\wwwroot\BBB\Databases\sitecore.core.Mdf' ),
( FILENAME = N'c:\inetpub\wwwroot\BBB\Databases\sitecore.core.Ldf' )
FOR ATTACH
GO

USE [master]
GO
CREATE DATABASE [BBB_master] ON
( FILENAME = N'c:\inetpub\wwwroot\BBB\Databases\sitecore.master.Mdf' ),
( FILENAME = N'c:\inetpub\wwwroot\BBB\Databases\sitecore.master.Ldf' )
FOR ATTACH
GO

USE [master]
GO
CREATE DATABASE [BBB_web] ON
( FILENAME = N'c:\inetpub\wwwroot\BBB\Databases\sitecore.web.Mdf' ),
( FILENAME = N'c:\inetpub\wwwroot\BBB\Databases\sitecore.web.Ldf' )
FOR ATTACH
GO

My thought here is that my Windows user somehow cannot execute this query on the master database. I do have my local Windows user (a domain user) in the logins on my SQL instance and its a member of sysadmins. I've also tried running the program as administrator (right-click > Run as administrator). I know the query works, it just doesn't seem to work in the context of the program. Am I missing something?

I'm using SQL Express 2008 on Windows 7 x64

Thanks in advance for any help.

If you can run the code you posted successfully in the context of your domain account then it's likely the tool is connecting to your database as a different user.  Are you able to inspect the connection string it uses?  If so, check to see if it's using Integrated Authentication or is using a User ID/Password combination and modifying things accordingly there.  Either change the connection string to use Integrated Authentication or the SQL login that you created as a test.


Estimate transaction log size for single transaction Today I have tried to execute an update operation on 50M+ rows, something like:

UPDATE [table] SET [Column] += 1;

after 30min of waiting I have seen that the log has started to filling itself, after another 30min the entire transaction rolled-back due to the log being full.
DB was in the simple recovery mode but this is meaningless as this was done as a single transaction. SQL Server on it's own couldn't predict that it will use the entire log size (lets say that it was locked size for this matter), but my question to you my dear experienced DBA's - could you predict that size? Is there any way to calculate such an estimation?

  UPDATE:

Yes, I understand the process, I know I can batch, I know how to make it performing well also. My real question is how big the log size is going to be?
How would you calculate update on a simple table like:

CREATE TABLE [TABL] ([ID] int identity(1,1) PRIMARY KEY CLUSTERED, [X] int not null);

INSERT INTO [TABL] VALUES(1)
GO 1000

UPDATE [TABL]
SET [X] = 2;

On my test box, the update part of the operation was 6 pages large = 48KB.
When I repeated the same for 10k rows I received 144 pages of transaction log.
For 100k rows it was 1314 pages, for 1M it was 13970 pages. This shows that we can concider this as linear function (on the we have extra pages which has to be there anyway no matter did we update anything or not --> 2-3 pages).

Going back to the begeining I know that I could run a single batch on 5% of my total operation, check the page chenge content (as blogged by Paul Randal):

    DECLARE @Extent_ID              INT;
    DECLARE @Size_Total             BIGINT = 0;
    DECLARE @File_ID                INT;
    DECLARE @File_Size_Pages        INT;

    DECLARE @Log_Page_ID            INT;
    DECLARE @Log_Total              BIGINT = 0;
    DECLARE @Log_Total_Changed      BIGINT;
    DECLARE @DBCC_PAGE_String_Log   VARCHAR (200);

    DECLARE [files] CURSOR FOR

        SELECT [file_id], [size]
        FROM master.sys.master_files
        WHERE [type_desc] = N'ROWS'
        AND [state_desc] = N'ONLINE'
        AND [database_id] = DB_ID(@DB_Name);

    OPEN files;

    FETCH NEXT FROM [files] INTO @File_ID, @File_Size_Pages;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Extent_ID = 0;
        SET @Size_Total += @File_Size_Pages / 8;    --| Convert size in pages to to extents

        WHILE (@Extent_ID < @File_Size_Pages)
        BEGIN

             SELECT @Log_Page_ID = @Extent_ID + 7;

             SELECT @DBCC_PAGE_String_Log = 'DBCC PAGE ([' + @DB_Name + '], ' + CAST (@File_ID AS VARCHAR) + ', ' + CAST (@Log_Page_ID AS VARCHAR) + ', 3) WITH TABLERESULTS, NO_INFOMSGS';

             TRUNCATE TABLE [msdb].[dbo].[DBCC_Page_Check];
             INSERT INTO [msdb].[dbo].[DBCC_Page_Check] EXEC (@DBCC_PAGE_String_Log);

             SELECT @Log_Total_Changed = SUM ([msdb].[dbo].[Extent_Change_Checks] ([Field]))
             FROM [msdb].[dbo].[DBCC_Page_Check]
             WHERE [VALUE] = '    MIN_LOGGED'
             AND [ParentObject] LIKE 'ML_MAP%';

             SET @Log_Total += @Log_Total_Changed;

             SET @Extent_ID += 511232;
      END
----------------------------------------------------------------------------------------------------------------------------

        FETCH NEXT FROM [files] INTO @File_ID, @File_Size_Pages;
    END;

    DROP TABLE [msdb].[dbo].[DBCC_Page_Check];
    CLOSE [files];
    DEALLOCATE [files];

but this still requires to make a change (even with rollback) before we could estimate anything at all. So again - is there anyone who knows how to estimate it better than the way above - up front?

Is there any way to calculate such an estimation?

No. You cannot predict that. You should always do your updates or deletes in batches.


Database connectivity issue in SSIS I have created a Connection manager in my SSIS project for database connectivity.I choose SQLClient Data Provider and select SQL server authentication mode for login.Test connection succeeded.
In Script Task I'm using following to get the connectionstring:

string strConnection = Dts.Connections["Target_DB"].ConnectionString;

While debug I found following only:

Data Source=ServerName;User ID=myid;Initial Catalog=product;Persist Security Info=True;

As you can see password is missing and that's why I'm not able to connect to database server Even Test connection succeeded.

Does this means it's not saving password if so then how can I connect to database.

---------------UPDATE---------------------

I added App.config file in ScriptTask and added ConnectionString node in it.Then I tried to access connection string in following manner but couldn't succeeded and it's showing some other connection string not the one which I have written:

string strConnection = ConfigurationManager.ConnectionStrings["CRMConnectionString"].ConnectionString;

Thanks

You can't access the password in the connection string. It's a security measure, regardless of your package protection level.

If you need to use an existing connection, then you need to follow the examples laid out in Todd McDermid's post Use Connections Properly in an SSIS Script Task or in Matt Masson's Accessing OLEDB Connection Managers in a Script

Personal note, I never managed to get Matt's version working but since he was the PM on the SSIS team, I'll assume it's my failed implementation and not his.

An alternative for accessing the password, since this is tagged with 2008, is to use Configuration. Whether it be XML or SQL Server, store the connection string in an external repository and assign the value to an SSIS Variable. Then, apply an Expression to the Connection Manager's ConnectionString property. Within your script task, you'll be able to access the complete connection string which contains the password.