texts:
I'm currently using the following query to get the Page Life Expectancy from SQL Server:

declare @PLE int

select @PLE=cntr_value
from psql1.master.sys.dm_os_performance_counters
where counter_name='Page life expectancy'
and instance_name='';

The issue I'm having is I don't know if this is in milliseconds, seconds, or what.  The value I am currently getting is 88936.  This could be in seconds or milliseconds.

It would make more sense if it was in miliseconds, but this server isn't used that much currently so something staying in memory for 24 hours (88936/3600=24.7) isn't impossible.


texts: SQL Server large transaction log for simple recovery model? help!

texts:
Hi all i'm building a data warehouse and noticed that my tran log is massive (in simple recovery).  MDF file is 2.6 GB.  LDF file is 7.8 GB!!! Why is this??? i don't want a stupid tran log that's why i've put in simple recovery!  (i know the system needs tran log).  so i tried to shrink the DB and get this error message.

DBCC SHRINKDATABASE (< DB >)

DBCC SHRINKDATABASE: File ID 1 of database ID 5 was skipped because the file does not have enough free space to reclaim.
DBCC SHRINKDATABASE: File ID 2 of database ID 5 was skipped because the file does not have enough free space to reclaim.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

have plenty of disk space!  ARGH should change my job title to professional sql wrestler...


texts: SQLPlus Command Line In-line Set Commands

texts:
I have the need to run linux command line direct query, including  SET options.

The query by itself runs fine.

However, when I attempt to add set commands, it fails.

(my alias 'db' connects to SQLPlus successfully)

OK/works fine (when sending single commands, issue seems to be with strining them together):

$db<<<"SELECT * from mydb.mytable;"
$db <<<"show heading;"
$db <<<"SET heading OFF;"

Not OK - Any variation of quotes/not quotes I try results in error:

$db<<<SET HEADING OFF "SELECT * from mydb.mytable;"
$db<<<"SET HEADING OFF, SELECT * from mydb.mytable;"
$db<<<"SET HEADING OFF "SELECT * from mydb.mytable;"

Results in complete error or:

SQL> SP2-0158: unknown SET option ","

Please assist.


texts: There are 2 main formats for pg_dump: custom vs sql. For custom, it's compressed by default, and not readable if you try to open it. But is it faster to dump into this format as opposed to SQL format. Or is the speed the same?


texts: Adding a domain user in SQL Server Management Studio

texts:
Before I ask my question, let me mention that my SQL Server is installed on a member server of a domain (it belongs to the domain but is not an AD server).

I am having trouble adding a network user in SQL Server Management Studio.

First I tried adding user by right-clicking security and clicking new -> login. I wanted to add a network user, so i clicked locations.  But only the name of the local server was showing. I was unable to browse my domain users (at that time I was logged in through a network user on my SQL Server machine).

So I tried another approach.  I tried to add a network user through Transact-SQL as follows:

create login [domain\user] from windows;

But I got an error message saying - Windows NT user or group not found - even though the user existed on my domain server.

What am I doing wrong?


texts:
I have a table with 4 rows of data always in the same order. What I want to do is to limit the query to just get the 2th to 3th row.

for example a table 'food' with:

cake
pizza
chocolate
lettuce

SELECT * from food (what do i put here to only get the rows pizza and chocolate)

This should also work for getting for example the 4th to 8th row in a table with 10 rows.


texts: Can a subscriber be a publisher at same time

texts:
I want to setup a topology for replication.

The scenario is such that a subscriber is subscribed to transaction replication based publisher (distributor running on publisher).

I then want this subscriber to be a publisher to another subscriber. I want the distributor to be on publisher instance (push).

Can some body help me out?


texts: Couldn't install SSMS: A pending restart is blocking setup from completing

texts:
SSMS version : 17.7

Log file: Log_file

I have SQL Server 2016 and Visual Studio 2017 Community Edition.
I tried reinstalling both tools but the error is still the same.
Some links mention registry values and I tried that as well, but the problem remains the same.

I have restarted the system multiple times, but the restart pending is still showing.
I found a couple of other methods on some sites, tried them as well, but still the error message is showing.

It stops at the point shown below--not sure about Visual Studio 2015 as I'm using VS2017. I have tried repairing VS and SQL Server 2017 as well.


texts: I've got 60 million sales of 800 different liquid products whose price change daily

texts:
..and I'm having a bit of a battle getting to customer stats like who's bought the most, and when was their last purchase.

I've no problem writing the query, it's just really slow

Sales
CustID, ProdID, Qty, SaleTimestamp
1,1,10,2000-01-01 01:01:01

ProductPrices
ProdID,Price,PriceDate
1,100,2000-01-01

Sales are recorded as an amount sold at a time/date. All products change price daily, so to find the total sales to all customers we need to join Sales and ProductPrices on productID and date of sale

SELECT
  CustID, SUM(Qty*Price), MAX(s.SaleTimestamp)
FROM
  Sales s
  INNER JOIN
  ProductPrices p
  ON s.ProdID = p.ProdID and CAST(s.SaleTimestamp as date) = p.PriceDate
GROUP BY
  CustID
HAVING SUM(Qty*Price) > 10000 --only big customers

It's a simple enough query, but it's been running for 45 minutes now. I've got 60 million rows in sales, and 800 products * 365 days = 292,000 prices. Sales:ProductPrices is Many:One

I've tried another way round to try and pre-reduce the dataset to just customer-product-day-totalqty:

WITH s AS (
 SELECT
  CustID, ProdId, CAST(SaleTimestamp as date) Dat, SUM(Qty) TotQty
 FROM
  Sales
 GROUP BY CustID, ProdId, CAST(SaleTimestamp as date)
)

SELECT CustId, SUM(s.TotQty * p.Price) Tot
  ProductPrices p
  INNER JOIN
  s
  ON
    p.PriceDate = s.Dat and p.ProdId = s.ProdId
GROUP BY CustID
HAVING SUM(Qty*Price) > 10000 --only big customers

But no luck. I've wrapped the entire thing in a count(*) to test whether it was completing the query quickly but taking ages to farm N million rows back to SSMS - the count is still slow too. I've swapped the cast to date out for a BETWEEN style on the non CTE query (either way round: saletimestamp between pricedate and pricedate+1day / pricedate between saledate-1day and saledate) but it appeared to make it worse, the most costly operation becoming a merge join on the tables, with SQLS stating that it was a many:many

I'm pretty sure the join is the problem, as I can do this:

WITH s AS (
 SELECT
  CustID, ProdId, CAST(SaleTimestamp as date) Dat, SUM(Qty) TotQty
 FROM
  Sales
 GROUP BY CustID, ProdId, CAST(SaleTimestamp as date)
)
SELECT COUNT(*) FROM s

And I get an answer of 13 million rows in 6 seconds.. If I do the same thing with products:

WITH p AS (
 SELECT
  ProdId, PriceDate, AVG(Price) price
 FROM
  ProductPrices
 GROUP BY ProdId, PriceDate
)
SELECT COUNT(*) FROM p

I get 292000 in less than 1 second. If I take these two queries and put them together for my answers:

WITH s AS (
 SELECT
  CustID, ProdId, CAST(SaleTimestamp as date) Dat, SUM(Qty) TotQty
 FROM
  Sales
 GROUP BY CustID, ProdId, CAST(SaleTimestamp as date)
),
p AS (
 SELECT
  ProdId, PriceDate Dat, AVG(Price) price
 FROM
  ProductPrices
 GROUP BY ProdId, PriceDate
)
SELECT COUNT(*) FROM(
  SELECT s.CustID, SUM(TotQty * Price)
  FROM p INNER JOIN s ON p.ProdId = s.ProdId and p.Dat = s.Dat
)

I'm back to 20+ minutes now and no result ..

I realise that it's probably hard to answer without seeing the plan - do I post the XML?

Server hardware is pretty immense - multiple 20 core Xeon Gold CPUs and 2TB ram


texts:
How can I slice up my table and return and evenly distribute the data?

Because I wanted to slice this up and put it into my 3 combo boxes in a windows form.

Example :

Table1

  ID          Name
   1           a
   2           b
   3           c
   4           d
   5           e

Windows Form Output :

combobox1   combobox2  combobox3
  a             c          e
  b             d


texts: Optimal database for large number of columns or rows

texts:
Problem 1:

I have one file with 567 rows and 16,382 columns, most of which are floating point numbers. I have another file with 117,493 rows but only 3 columns. The contents deal with biology and genetics.

I have no idea what the numbers mean in the files mean. This is a college project where I have to be able to query different things given the files without knowing what the contents are. I am assuming we are not supposed to normalize the data because that would require understanding of the contents. We are graded on performance and reason for choosing which databases.

File 1: (567 rows, 16,382 columns)

I have been getting people surprised at the number of columns in this file. The columns are mostly genetic information with a bunch of numbers and cannot be normalized. I initially thought Postgresql would be good for File 1, but I read they are a row-oriented database, so it would be horrible. I read about Cassandra being good for column-oriented look-ups, but the problem is the most of the column and rows contain data and is mostly structured, except for a few areas with no values. Would it still be a better idea to use Cassandra?

Operations for File 1:

simple look-up
find mean
find standard deviation

I am not able to confirm on the general performance between SQL and NoSQL on aggregate functions such as calculating mean and standard deviation. So this is another factor in selecting my database. I would assume Cassandra is faster than Postgresql here since Cassandra is column-oriented.

File 2: (117,493 rows, 3 columns)

Every single row and column contains data. I am guessing that Postgresql is good here since there are only 3 column but 117,493 rows, Postgresql is row-oriented, and there are no missing data. Is there a better NoSQL alternative in this case? Would a key-value store NoSQL be better since this will mostly be used for retrieval?

Operations for File 2:

simple look-up

Problem 2:

Most of the large files I have are related by some kind of ID, such as File 1 and File 2. If I use Postgresql for File 2 and Cassandra for File 1, is there a considerable performance loss? Usually you would store related tables in an RDMS, but does the number of columns for File 1 mean it is better to use a hybrid approach?


texts: Check constraint only one of three columns is non-null

texts:
I have a (SQL Server) table that contains 3 types of results: FLOAT, NVARCHAR(30), or DATETIME (3 separate columns). I want to ensure that for any given row, only one column has a result and the other columns are NULL. What is the simplest check constraint to achieve this?

The context for this is trying to retrofit the ability to capture non-numeric results into an existing system. Adding two new columns to the table with a constraint to prevent more than one result per row was the most economical approach, not necessarily the correct one.

Update: Sorry, data type snafu. Sadly I wasn't intending the result types indicated to be interpreted as SQL Server datatypes, just generic terms, fixed now.


texts: Foreign Key (references) constraint on PostgreSQL Foreign Data Wrapper?

texts:
It seems to be disallowed to add a Foreign Key constraint to a foreign table.  Is there any other way to do this?  My two tables have these constrains on the remote server.

More specific details:

I only really need it as an annotation because some tools look that up for JOINs.

I have two tables on a remote server that I've added to my local using postgresql_fdw's CREATE FOREIGN TABLE.  These I'm using in PostgREST, an automatic API server uses the REFERENCES information in order to make joins.

Actually, I'm further making two additional CREATE MATERIALIZED VIEW to make the lookups fast - but sadly, they too don't have any way I know of to add REFERENCES information?

I'm open to other ways of making (read-only) lookups fast for these tables on the foreign API server and where Foreign Key constraints will work.


texts: Add constraint to ensure flag column is <= sum of column in other table

texts:
I have a column RSEFlag that contains a flag value that is the sum of one or more of 1, 2, 4. I have separate table that contains the description of each of those flag values:

RSECode RSEDescription
------- --------------
   4    Environment
   1    Reliability
   2    Safety

I would like to constrain my RSEFlag column so that it is less than or equal to the sum of values from the RSE table.

Is this a worthwhile exercise and what is the best approach?


texts: could not read symbolic link error while executing pg_start_backup function in Postgresql DB

texts:
In order to take data folder backup in postgres 9.6 database executed the order of functions for backup,
while executing pg_start_backup function produced warning :  could not read symbolic link for pg_tblspace Invalid argument
test=# checkpoint;
       CHECKPOINT

test=# select pg_switch_xlog();
 pg_switch_xlog
----------------
 5/DF000238
(1 row)
**
test=# select pg_start_backup(to_char(now(), 'yyyy-mm-dd hh24:mi:ss'));
WARNING:  could not read symbolic link "pg_tblspc/oth_data_tbs": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/str_index": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/tbs_temp_default": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/oth_indx_tbs": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/str_data_ids": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/tbs_default": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/top_indx_tbs": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/rpt_data_tbs": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/str_data_h_index": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/rpt_indx_tbs": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/str_data": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/str_data_h": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/top_data_tbs": Invalid argument
 pg_start_backup
-----------------
 5/E0000028
(1 row)


texts: Does "allow remote connections to this server" in sql server impact SSIS packages?

texts:
I'm wondering if anyone has experience with this setting "allow remote connections to this server" in sql server. I am partly responsible for a few hundred sql servers which run SSIS in various ways. Some run it with ssis packages deployed through integration services catalog, and others run the SSIS package from a batch server or even manually from their laptop running visual studio, using a package that connects to our sql server.
A security consultant is saying we need to disable this on every server, no questions asked, but I'm hearing conflicting information. Some would imply any SSIS packages deployed on another server or laptop and connecting to this one, would be denied now. Others say it actually means connections FROM this server, so it would impact ssis packages called from batch servers. Others say it is FROM still, but for some reason wouldn't impact SSIS packages.
Any recommendations on what I should do? Rather than test every possible scenario to appease the security consultant, I would like to be able to rule out a few "always true" situations where we would keep this setting checked. Otherwise I will have to test hundreds of environments just to disprove him. Any help appreciated.


texts: Are there any risks to granting users SQL Server SHOWPLAN permission?

texts:
I'm doing some performance tuning on a large SQL server 2008 database, and the IT group is unwilling to give SHOWPLAN permission. In the past, "Show Execution Plan" has been the most effective way to understand the performance of individual queries and procedures.

What risks are inherent on granting this permission? Is there a legitimate justification for this restriction on a development copy of the database?

Note: This SQL IT group has 200+ databases under a single SQL Server instance. Thanks.

Answer: I am taking the lack of response to mean that there are no significant security risks, other than the one noted below. Basically, restricting this on a development database is counterproductive.

I will update this if anyone comes up with a better answer. Thanks for your comments!


texts: How to resolve this SQL Server 2016 master data services authentication error on the web application?

texts:
I just installed MS SQL server, IIS and Master Data services, 2016 version. Everything seems fine, except the web application doesn't work. I am able to go to the home screen, but there are no models/versions listed in the Model: and Version: dropdown, even though I added two models using the MDSModelDeploy utility. The UI doesn't give me any error, but Chrome dev tools shows "401 Unauthorised" error.

So basically, the url "http://server-name:port/api/Models" is unreachable, giving "authorization has been denied for this request" error.

I've checked the usual places (allowing anonymous auth etc), they all seem correct. What else should I try?


texts:
WHILE (@7DaysEarlierPartitionIntegerId <= @CurrentPartitionIntegerId)
    BEGIN
        Set @7DaysEarlierPartitionId
            = CAST(@7DaysEarlierPartitionIntegerId AS char)
        set @sqlCommand
            = 'Select * from '
              + quotename(@RequestUsage_Partition + @7DaysEarlierPartitionId)
              + 'where UserLogin like''r2\rohit.kharade'
        exec(@sqlCommand)
        set @7DaysEarlierPartitionIntegerId
            = @7DaysEarlierPartitionIntegerId + 1
    END

I get the error:

  Unclosed quotation mark after the character string 'r2\rohit.kh'.

How to execute the exec command with UserLogin =%r2\rohit.kharade%


texts: On pgAdmin 4, how to basically copy the content of one or two cells?

texts:
I decided to go on pgAdmin 4 instead of the 3 (I'm on Windows 7).
It seems that this software if somehow still in a alpha state...

I would like to only get the content of one cell. So I select this cell and tap Ctrl+C.

But it seems NOT to copy the content to the clipboard (the previous clipboard content stays when I paste...).

I notice that the copy button in the topbar stays disabled...

On the pgAdmin 4 GUI Tool page, it seems the only proposed way to copy is to click on this button :

  Click the Copy icon to copy the currently selected row.

Do anybody has a better way to do this ?


texts:
I select data from dbf files through OPENROWSET Visual FoxPro OLEDB.
Despite forcing collation on columns (e.g. SELECT name COLLATE Polish_CI_AS FROM Employees) the polish specific characters keep being different characters.

Due to the data model complexity, nested replace statements are not desired.
Any idea how to fix it?


texts:
For the first time ever could not find a solution on internet!
The problem is when I'm trying to (on local server):

CREATE TABLE `DB_1`.`testView`
ENGINE=FEDERATED CONNECTION='remote_server';

I'm getting an error:

  #1939 - Engine FEDERATED failed to discover table `DB_1`.`testView` with 'CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `testView` AS select 'test_line' AS `test_line` CONNECTION='remote_server''

Obviously you can see that VIEW is very simple for testing purposes (executed on remote server):

 CREATE VIEW `DB_1`.`testView` AS SELECT "test_line"

The user in remote_server has all privileges. View was tested with different options and content. Federated tables work fine. Server version on both machines: 10.0.29-MariaDB-0+deb8u1

Am I missing something?


texts:
I have query such as this:

SELECT t0.id
FROM platform_conversations t0
LEFT OUTER JOIN contacts t1 ON t1.id = t0.contact_id
WHERE t0.user_id = 5340
AND (
     t0.participant ILIKE '%baa%'  -- (1)
     OR t1.first_name ILIKE '%baa%' -- (2)
     )
LIMIT 50;

and

CREATE INDEX ix_conversations_participant
  ON platform_conversations USING GIN (participant gin_trgm_ops);

CREATE INDEX ix_trgm_contacts_search
  ON contacts USING GIN (first_name gin_trgm_ops);

And can't figure out why indexes are not used with OR condition. If I use only (1), or only (2), or use AND instead, they are both used.

Here's the plan:

For only (1)

Limit  (cost=12.43..203.68 rows=50 width=37) (actual time=0.037..0.037 rows=0 loops=1)
  ->  Bitmap Heap Scan on platform_conversations t0  (cost=12.43..222.80 rows=55 width=37) (actual time=0.030..0.030 rows=0 loops=1)
        Recheck Cond: ((participant)::text ~~* '%baa%'::text)
        Filter: (user_id = 5340)
        ->  Bitmap Index Scan on ix_conversations_participant  (cost=0.00..12.42 rows=55 width=0) (actual time=0.012..0.012 rows=0 loops=1)
              Index Cond: ((participant)::text ~~* '%baa%'::text)
Planning time: 0.397 ms
Execution time: 0.092 ms

For only (2)

Limit  (cost=16.71..446.06 rows=50 width=37) (actual time=0.034..0.034 rows=0 loops=1)
  ->  Nested Loop  (cost=16.71..471.82 rows=53 width=37) (actual time=0.028..0.028 rows=0 loops=1)
        ->  Bitmap Heap Scan on contacts t1  (cost=16.29..158.99 rows=37 width=37) (actual time=0.022..0.022 rows=0 loops=1)
              Recheck Cond: ((first_name)::text ~~* '%baa%'::text)
              ->  Bitmap Index Scan on ix_trgm_contacts_search  (cost=0.00..16.28 rows=37 width=0) (actual time=0.016..0.016 rows=0 loops=1)
                    Index Cond: ((first_name)::text ~~* '%baa%'::text)
        ->  Index Scan using ix_platform_conversations_contact_id on platform_conversations t0  (cost=0.42..8.45 rows=1 width=74) (never executed)
              Index Cond: ((contact_id)::text = (t1.id)::text)
              Filter: (user_id = 5340)
Planning time: 0.840 ms
Execution time: 0.121 ms

Slow one (with OR):

Limit  (cost=25771.43..47419.63 rows=50 width=37) (actual time=6652.292..6652.292 rows=0 loops=1)
  ->  Hash Left Join  (cost=25771.43..72531.55 rows=108 width=37) (actual time=6652.282..6652.282 rows=0 loops=1)
        Hash Cond: ((t0.contact_id)::text = (t1.id)::text)
        Filter: (((t0.participant)::text ~~* '%baa%'::text) OR ((t1.first_name)::text ~~* '%baa%'::text))
        Rows Removed by Filter: 553477
        ->  Seq Scan on platform_conversations t0  (cost=0.00..20627.29 rows=553512 width=87) (actual time=0.045..1741.899 rows=553477 loops=1)
              Filter: (user_id = 5340)
              Rows Removed by Filter: 386
        ->  Hash  (cost=17578.19..17578.19 rows=384819 width=46) (actual time=2372.508..2372.508 rows=384819 loops=1)
              Buckets: 65536  Batches: 16  Memory Usage: 2359kB
              ->  Seq Scan on contacts t1  (cost=0.00..17578.19 rows=384819 width=46) (actual time=0.014..1168.218 rows=384819 loops=1)
Planning time: 0.741 ms
Execution time: 6652.333 ms

Can't it just do bitmap index scans on t1, t2 and then bitmap ORs on them? Why is OR such a problem?

Postgres 9.6 (can't create tag, not enough rep)


texts: Collecting VLF information on all versions of SQL server

texts:
Based on my monitoring collection, i am having an issue while collecting info regarding how many VLF' i have for # of databases in SQL instance.
1st issue - Is there a single script which can be used from 2012 - 2019 SQL versions as standard to get the VLF info across all databases in an instance
2nd issue - how can i get 1st done with least privilege because login we use to collect info is not sysadmin and cant be. So how can i achieve this?
Much appreciated, thanks!


texts: Where is the best place to leave the Database Backup scripts, currently I let them on the Backup Job itself, however I wonder if it's best to leave on a Database


texts: How to remove a corrupted shard in MongoDB

texts:
I use MongoDB 3.0.3 with Wired Tiger engine on Cent OS 6.4.
I have a sharded cluster of 4 shards each of which has about a quarter of total data.

One of the shard called "shard2" corrupted and can't start.
Without this shard, the cluster can't work.

I can bear losing data on this shard, How can I remove this shard to make the cluster work??
What parameters should I modify in "config" collection?


texts:
Primary DB: 11.2.0.3 Enterprise
  Standby DB: 12.1.0.2 Enterprise

I've found several articles on making changes on a Physical Standby DB, but I've been having difficulty locating how to enable user testing on a Transient Logical Standby DB (then flashing back their changes and re-enabling log transport and SQL Apply after the testing has been completed).

Based on what I know of Logical Standby Databases, I wrote up the following procedures.  However, this is untested since if I botch up the Standby DB, I can't simply re-duplicate the DB through RMAN since the database versions are different.  I'll essentially have to re-clone the Primary DB, reconfigure the Standby for dataguard, upgrade the Standby DB version, etc, etc... basically, a lot of work that I would prefer to avoid, if possible...

If someone who has had experience with this can look over my below procedures and let me know if i'm forgetting anything, or what potential issues/caveats I should watch out for or be aware of, I would greatly appreciate it:

*note: the primary => standby log transport is specified in log_archive_dest_3

PRIMARY
alter system set log_archive_dest_state_3=defer scope=both;

STANDBY
alter database stop logical standby apply;
alter database guard none;
alter database flashback on;
create restore point before_testing guarantee flashback database;

*AFTER TESTING IS DONE*

STANDBY
flashback database to restore point 'before_testing';
drop restore point before_testing;
alter database flashback off;
alter database guard all;
alter database start logical standby apply immediate;

PRIMARY
alter system set log_archive_dest_state_3=enable scope=both;


texts: Linked Server vs. Ad Hoc (OpenRowset / OpenDatasource) Distributed Queries

texts:
I have an application which needs to grab data from different clients' databases that reside at the client's location. So alongside their normal details such as company name, address etc, I also store the name of their DB server and the name of the database I need to interrogate.

The number of clients is currently zero. However, I expect this to grow to to around 200+ in a year's time.

I am a bit confused with which option to go with to run distributed queries:

  Creating linked servers for every single client (up to 200+!)
  Using an OpenDataSource() or OpenRowset() ad-hoc query within an SP that feeds in the DB server and DB name dynamically from the client
  account table

Option 2 sounds the easiest to manage because if a client was to move their server or whatever, they just need to update their details in their account once and everything should work correctly.

But the reason I'm confused is because of this statement on Microsoft's site:

  OPENDATASOURCE should be used only to reference OLE DB data sources
  that are accessed infrequently. For any data sources that will be
  accessed more than several times, define a linked server.

The external DBs will get accessed quite frequently, with the majority of transactions being SELECT statements.

I'm also unsure to the security implications and which option is more tight for security. Does anyone have experience in this area and could give me some tips please?


texts: SQL-Server: sp_spaceused gives zero rows but big dataSize for cleaned table

texts:
After cleaning my table with DELETE FROM MyTable (and executing DBCC shrinkdatabase('MyDB') should that matter) I run the statement EXEC sp_spaceused MyTable. The results confuse me:

tableName   numberOfRows   reservedSize   dataSize   indexSize   unusedSize
---------   ------------   ------------   --------   ---------   ----------
MyTable     0              21664 KB       20672 KB   736 KB      256 KB

As you can see there are zero rows, yet there's almost 21 MB of data. My question is: what are possible causes for this situation and/or how can I further investigate this?


texts:
I have the following tables.

So one product can have one category, one category can have n genres.(Products-> Genre Column data 6 ( 2 + 4), 132 (128 + 4) like that).

So my question is; i want to do find similar products for one product with using bitwise genres. Is that possible with sql query (or stored procedure)?

I never do something like that before, so i need help about it.

Thanks.


texts: Uniquely Append to Array on Conflict PostgreSQL

texts:
I've got two columns in PostgreSQL, hostname and ip. Hostname is the primary key and ip is an array of IPs.
I want to be able to insert IPs for a give hostname, on conflict I want to append to the array with the data I'm trying to insert and the output data should be unique.

e.g

hostname - ip

test.com   {1.1.1.1,2.2.2.2}

Input

I attempt to insert...

test.com {1.1.1.1, 3.3.3.3, 4.4.4.4}

Output

test.com {1.1.1.1, 2.2.2.2 ,3.3.3.3, 4.4.4.4}

insert into auto_scope_pre_proc (hostname,ip) values ('test.com', '{10.10.10.10, 10.10.10.22}'::inet[])
on conflict (hostname) do update
SET ip = array_append(auto_scope_pre_proc.ip,ip);


texts: Is it always safe to use WITH SCHEMABINDING in a UDF?

texts:
SO I have been reading about WITH SCHEMABINDING and how it can improve the performance of queries using a scalar UDF by omitting the table spool operator from the execution plan. I think I understand halloween protection.
My question is: If I add WITH SCHEMABINDING to a UDF used in a SP is it possible that a SP does not give the same results? If yes in what scenario?


texts:
I'm trying to learn RMAN but getting confused everytime I read Oracle docs!

Can someone help me what are the differences and when to use the following?

BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT

BACKUP FULL

Which came from:

run{
backup full tag='INC000000325026'
(database format='/u04/backups/paris/rman/%d_%U_%T' include current controlfile);
}

run{
ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT '/u01/backups/rman/%d_%U_%T';
BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT TAG ='FRISK_DB';
}

I want/need to take a cold full backup of a database in a simple way, so which one is suitable?


texts: Database explorer disabled in SQL Assistant

texts:
I am a new Teradata SQL assistant user. I have used SQL Server Management Studio before.

In the View option, I am unable to toggle the Database Explorer. Please let me know how I can access the databases I add on my SQL assistant.


texts:
Does SentryOne Plan Explorer work as advertised and is it legitimate? Are there any gotchas or something to be concerned about?

It looks like it shows the hot path in color commpared to SSMS's nightmare of a view for the estimated execution plan.

My concerns is - does it modify any data maliciously or otherwise?

edit: I had just heard of it and never heard of the company before.


texts:
I'm trying to find the total size on the hard disk that all of my MySQL Workbench databases are using.

Does anyone know of an easy way to figure this out?

If nothing else, the default location mysql/workbench uses for saving the raw data on a windows machine?

Thanks in advance!
Quintis


texts:
I've uploaded an image of my database design below. This is the first database I have designed and I was hoping that I could get some input into whether or not it is set up correctly or if I just have no idea what I'm doing! I am using MySQL. It is for an rpg game. Thanks so much for your time.

Overview of what it should do:

Each user should be able to own multiple characters,
But each character should be owned by only one user
Each Character should have only one location
But many Characters can exist in one location
Each character should own one container (inventory)
But each container should only belong to one character/NPC (a container cannot be owned by both a character and an NPC)
Each container may or may not have only one location
But one location can have many containers inside of it
Each item/weapon/armor should either exist inside of one container or have one location
But one container or location can have many item/weapon/armor
Each NPC should have one location
But one location can have many NPC


texts: How can I most closely monitor this table in production to find why rows are disappearing?

texts:
Scenario:
We write online banking software and for the time being (due to legacy design decisions) are using Quartz 2.2 right now as a queue system to perform a handful of operatoins long term. [Let's sidebar the discussion on if it's the right tool, it's worked well for many years until we updated to 2.2]
Part of that quartz.net has the following tables (related) for schema: https://gist.github.com/jcolebrand/8695603
So the process is we're inserting records in this table, to the tune of say 80k records. (I have three replications of quartz.net serving three different configurations, one has 80k records, one 50k, one 280k, so it varies). We insert them from some other table that is the table of record, so rebuilding this table isn't a terrible loss. The issue is described below.
I have a tool which can bulk schedule these tasks (most of which are one-time and will happen anywhere from two weeks to twenty years in the future, again, this is about the db and not the architecture choices) and I can debug step through, see the rows get inserted into the table with no concern. I can monitor the table and see that they get inserted. I then come back 30 minutes later (well, this part varies. It's not deterministic on when they disappear) and check and the records are now missing.
Out of 80k on the one instance, I'm missing approximately 2700 records. And when I run my tool, I see them all synced up, and after some matter of time, they have evaporated again.
Here's things I've tried:

checking the all transactions report in SSMS
checking the all blocking transactions report in SSMS
leaving my scheduling application open for long duration (in case, by some freak magic, there was an open, uncommitted transaction)
restarting the Quartz.net windows service application maintaining the database
inserting records with the quartz.net windows service disabled

Things I haven't tried:

restarting SQL Server
my queries don't use "with(nolock)" [I've heard that's bad juju for day-to-day]

Things I think I should do and don't know how:

run SQL Profiler against the instance and monitor for "delete" statements
magic to determine if there is an uncommitted transaction
sacrifice unicorns
telephone a friend
daily double

@@version:

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

EDIT 2014-01-30
It is imperative for internal reasons that I can demonstrate precisely why the records are disappearing, including but not limited to:

deletion by a user
uncommitted transaction
deletion by Quartz itself
some other unexplained phenomena
butterfly effect
trigger effect
unicorns

Where possible, I need the specific statement involved in the delete.
TL;DR
So, basically, the TL;DR is: records are inserted. For some duration of time up to at least 30 minutes record definitively show in queries against the table. After some duration, records no longer show in queries against the table.
What gives? What am I overlooking? How would you profile this IN PRODUCTION to see what's happening here?


texts: Facing error 'invalid SQL statement' while working with cursor variables in PL/SQL

texts:
Hi everyone hope you're doing well.
I'm new to PL/SQL and I'm trying to learn 'cursor variable' . I have made and example for myself and everything looks correct and I have no idea why it does not work.
my first procedure is

  create or replace procedure p_get_student_detail(s_id  number,
     s_cur out sys_refcursor) AS

    Begin

     open s_cur for
     select student_name, age from student where student_id = s_id;

   end;

and my second procedure in which I'm using the output of the first procedure is :

Create or replace procedure s_print_data(s_id number)

   as

    type ref_cur is ref cursor;
    s_refcur       ref_cur;
    s_student_name student.student_name%type;
    s_student_age  student.age%type;

 begin

    p_get_student_detail(s_id, s_refcur);

     loop
        fetch s_refcur into s_student_name, s_student_age;
        exit when s_refcur%notfound;
        dbms_output.put_line(s_student_name || s_student_age);
     end loop;
 end;

when I try to execute the procedure exec s_print_data (1) I see this error :

ORA - 00900:invalid SQL statement

I was wondering if you could help me with that.
Thanks


texts: Dumping Postgres schema from Google cloud SQL

texts:
Now this may look trivial but it is a managed service so I don't have the superuser rights. And various schema/tables/sequences have different roles/users as their owners so I can't just use pg_dump remotely as i expected as a lot of them will fail due to permission issues.

Their export function only allows "Cloud Storage export" which I would have to pay for again plus it dumps everything not just the schema.

Someone kindly let me know if I have missed something?

So I figure their superuser "cloudsqladmin" can do the entire dump but I am unable to login as that user and do the dump. Any advice?


texts:
I'm working with the following script:

create or replace PROCEDURE a_proc
is vi_get_emails varchar2(500);
cursor get_emails is
SELECT DISTINCT purchase_authorizer_api.get_userid('30', pagl.authorize_id)
FROM purch_authorize_group_line pagl join purchase_authorizer pa
ON pagl.authorize_id = pa.authorize_id
WHERE pagl.authorize_group_id = '30-PM-COM'
AND pa.notify_user = 'TRUE';

BEGIN
    OPEN  get_emails;
    FETCH get_emails  INTO vi_get_emails;
    CLOSE get_emails;

  FOR rec_ IN vi_get_emails LOOP
     command_sys.mail(from_user_name_ => 'IFSAPP',
                    to_user_name_ => vi_get_emails,
                    subject_ => 'Test subject',
                    text_ => 'Test message body');
    END LOOP;
END a_proc;

This part of my script returns two values:

SELECT DISTINCT purchase_authorizer_api.get_userid('30', pagl.authorize_id)
FROM purch_authorize_group_line pagl join purchase_authorizer pa
ON pagl.authorize_id = pa.authorize_id
WHERE pagl.authorize_group_id = '30-PM-COM'
AND pa.notify_user = 'TRUE';

Let's call these values Bob and Bill.

I would like the second part of my script to email both Bob and Bill. However, it's only emailing Bob since Bob is the first record returned by my query. It's not emailing the two users. It's only emailing the first user returned by the query.


texts: How do I guarantee using Microsoft ODBC Driver 13 for SQL Server in a linked server?

texts:
On SQL Server 2016, how do I guarantee using Microsoft ODBC Driver 13 for SQL Server in a linked server?  I don't mind there being another layer in there, such as the MSDASQL provider, but I do want the ODBC Driver 13 for SQL Server to be what ends up making the connection to the remove instance.

With testing on SQL Server 2016 RC2 on Windows 2016 Technical Preview 4, both fresh installs on a blank VM, I can use odbcad32 to see the "ODBC Driver 13 for SQL Server", version 2015.130.1300.275, with file name MSODBCSQL13.DLL.

The version and the filename are identical in the 64-bit odbcad32 screen as well as the 32-bit odbcad32 screen from c:\windows\syswow64, so I do not believe it to be a 32 vs 64 bit issue at this time (particularly since the driver was installed by the SQL Server 2016 RC2 install).

On SQL 2014, for instance, to use Native Client 11, I would use

EXEC master.dbo.sp_addlinkedserver @server = N'LinkName', @srvproduct=N'sql_server', @provider=N'SQLNCLI11', @datasrc=N'YourTargetServer'

On SQL 2016 RC2, when I try

EXEC master.dbo.sp_addlinkedserver @server = N'LinkName', @srvproduct=N'sql_server', @provider=N'MSODBCSQL13', @datasrc=N'YourTargetServer'

The linked server creates just fine, but when I try to use it, I get:

Msg 7403, Level 16, State 1, Line 7
The OLE DB provider "MSODBCSQL13" has not been registered.

I didn't have any luck trying provider names of
ODBC Driver 13 for SQL Server
Microsoft ODBC Driver 13 for SQL Server

or even trying combinations of that that the MSDASQL provider name, from

Using Always Encrypted with the Windows ODBC Driver
and
sp_addlinkedserver (Transact-SQL)

And even looking through the registry didn't reveal a provider name I recognized.

Note that using odbcad32 to create a System DSN does, in fact, test successfully when I choose ODBC Driver 13 for SQL Server, so I know it can work.

Ideally, I just want a sample sp_addlinkedserver command that specifies the new ODBC driver in it.


texts: Trying to create a sequence in Oracle that starts with the max value of primary key of a specific table. Why does this not work?

texts:
Trying to create a sequence in Oracle that starts with the max value from a specific table. Why does this not work ?

CREATE SEQUENCE reservation_sequence
MINVALUE 0
START WITH (SELECT MAX(reservation_id)
 FROM reservation)
INCREMENT BY 1
CACHE 20;


texts: How do I figure TOTAL DURATION for specific group

texts: I have a table with CARD # and REDEEMED DATE. I am trying to figure out the total duration (in minutes) for each CARD #. I am using Microsoft SQL Server 2014


texts: What happens when there's no tempdb space for version store operations?

texts: If tempdb runs out of space, do transactions that need version store space fail, or do they wait for space to be freed?


texts:
Running SS2005.  I have a script that calls procedureA which in turn calls procedureB.

procedureA has no explicit transactions.  procedureB is split up into several small blocks of non-nested BEGIN and COMMIT TRANSACTION.

I have peppered procedureB with PRINT statements to show me the value of @@TRANCOUNT.  It's always at 0 or 1.

But when I check master..sysprocesses it shows open_tran at 1 or 2.  Where this extra transaction is coming from?

I am posting the 2 procedures below.  pr_Get12Relatives has been stripped of most of its content for clarity as it contains 1600 lines!  It just repeats the same behavior as it searches for different types of relationships.

procedureA = pr_GetRelatives

procedureB = pr_Get12Relatives


texts: Has the guidance on the setting for 'cost threshold for parallelism' changed with the advent of columnstore indexes?

texts:
First off, what I'm not asking.  I'm not asking what my setting should be.

Many are recommending upping the value past the default, and I certainly understand why that's the case for B-Tree based queries.  But I've been reading about the (almost) linear scalability of in-memory clustered columnstore indexes, and I'm wondering if setting the cost threshold too high could cause SQL Server to starve columnstore-based queries for CPU cores.

So the question is this:  Does SQL Server treat columnstore indexes differently when the 'cost threshold for parallelism' is concerned, and should that cause me to change my decision about what my initial setting should be?


texts:
In Venezuela a timezone change have been undertaken on May 1st 2016. The old offset for the Venezuela timezone was -04:30 and the new one is -04:00.

All database servers have been updated with the new timezones, when I run this command at the Linux prompt I get the expected output:

$ date +'%:z %Z'
-04:00 VET

Most PostgreSQL clusters happily took the new timezone after bouncing them:

Good behaved cluster:

postgres=# show timezone;
TimeZone
-----------
localtime
(1 row)

postgres=# select now();
      now
-------------------------------
2016-05-02 10:08:54.532902-04
(1 row)

postgres=# SELECT utc_offset FROM pg_timezone_names WHERE name = 'America/Caracas';
utc_offset
------------
-04:00:00
(1 row)

In the case of two particular database servers, the clusters refuse to take the new timezone offset no matter how many times we bounce the cluster.

Ill-behaved cluster 1:

postgres=# SELECT utc_offset FROM pg_timezone_names WHERE name = 'America/Caracas';
utc_offset
------------
-04:30:00
(1 row)

postgres=# select now();
       now
----------------------------------
2016-05-02 09:39:03.678385-04:30
(1 row)

postgres=# show timezone;
TimeZone
-----------------
America/Caracas
(1 row)

The servers who took the change as well as the ones which didn't, all have the default value for the timezone parameter in postgresql.conf.

I need advice on how to solve this, since all other clusters in other servers took the change without a hassle.

What else can I check?
What am I missing?

One of the clusters that failed to take the new timezone is 8.2 and the other is 9.3,

To confuse it even more, one of the ill-behaving clusters (the 9.3 one) has the correct offset from the pg_timezone_names view but returns the time from the old offset:

Ill-behaved cluster 2:

postgres=# SELECT utc_offset FROM pg_timezone_names WHERE name = 'America/Caracas';
 utc_offset
------------
 -04:00:00
(1 fila)

postgres=# select current_time;
        timetz
-----------------------
 10:20:16.398867-04:30
(1 fila)

postgres=# show timezone;
 TimeZone
-----------
 localtime
(1 fila)

I'm totally lost here. Any light on this issued would be most appreciated.


texts:
While trying to backup all data from a mysql server with mysqldump I got this error

root@server$ mysqldump -uroot -p --master-data=1 --single-transaction --flush-privileges --routines --triggers --all-databases
mysqldump: Couldn't execute 'SHOW FIELDS FROM `_bare_grantee_grants`': View 'common_schema._bare_grantee_grants' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)

I assume this is not a permission issue as I'm a root. (I've looked to questions related to this on this SE but I did find they relate to my issue).
What could be the source of the problem and the solution ?
mysql version is 5.7.33 which was upgraded a couple of weeks ago from 5.6
thanks


texts: DB STARTUP command active for an online database

texts: We had a maintenance activity done on our SQL Servers and after that, we restarted the services. Now the databases took their sweet time to recover and after recovery, we started some jobs thinking that everything is good. Now one of the job runs was blocked by a spid that has its command as DB STARTUP. Suspiciously, I checked the SQL Server logs and there it was showing that one database was still recovering. However, in the object explorer I am able to see that database as online and also I can query the database. I even did some fake updates and truncated some tables too in that database. So, what recovery is exactly going on here? The databases are in AOAG, if that helps.


texts:
I have a table with below structure

CREATE TABLE [dbo].[ColumnValues](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ColumnId] [int] NOT NULL,
    [Value] [nvarchar](max) NULL,
    [OutputArea] [nvarchar](max) NULL,
 CONSTRAINT [PK_dbo.ColumnValues] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[ColumnValues]  WITH NOCHECK ADD  CONSTRAINT [FK_dbo.ColumnValues_dbo.CensusColumns_ColumnId] FOREIGN KEY([ColumnId])
REFERENCES [dbo].[CensusColumns] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[ColumnValues] CHECK CONSTRAINT [FK_dbo.ColumnValues_dbo.CensusColumns_ColumnId]
GO

It contains almost 250 Million records. I need to filter records based on list of OutputArea codes and ColumnIds and the query which I used is like this:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[OutputArea] AS [OutputArea],
[Extent1].[ColumnId] AS [ColumnId],
[Extent1].[Value] AS [Value]
FROM [dbo].[ColumnValues] AS [Extent1]
WHERE ([Extent1].[OutputArea] IN (N'E00139237', N'E00139238', N'E00139239', N'E00139255', N'E00139257', N'E00139258', N'E00139259', N'E00139260', N'E00139261', N'E00139262', N'E00139263', N'E00139264', N'E00139265', N'E00139266', N'E00139267', N'E00139268', N'E00139689', N'E00139690', N'E00139691', N'E00139694', N'E00139814', N'E00139815', N'E00139816', N'E00139817', N'E00139818', N'E00139835', N'E00139837', N'E00139838', N'E00139847', N'E00139848', N'E00139849', N'E00139850', N'E00139855', N'E00139856', N'E00139857', N'E00139858', N'E00139939', N'E00139940', N'E00139941', N'E00139980', N'E00139981', N'E00139982', N'E00140008', N'E00140010', N'E00140011', N'E00140012', N'E00140013', N'E00140014', N'E00140015', N'E00140016', N'E00140017', N'E00140018', N'E00140019', N'E00140020', N'E00140021', N'E00140022', N'E00140023')) AND ([Extent1].[OutputArea] IS NOT NULL)
AND ([Extent1].[ColumnId] IN (298, 299, 300, 301, 302, 303, 304, 305, 306, 307, 308, 309))

When I have small list of entries like less than 50 members for input the query works fine, not with great performance though. But when I run a query on an instance of SQL Server 2008 R2 which is hosted on a powerful server which has 128 GB memory it mostly fails to execute and I get below error:

  The query processor ran out of internal resources and could not
  produce a query plan. This is a rare event and only expected for
  extremely complex queries or queries that reference a very large
  number of tables or partitions. Please simplify the query. If you
  believe you have received this message in error, contact Customer
  Support Services for more information.

Is it very complex query? because I expect this to execute fast and without problem due to powerful resources on the server.
How do I fix the problem and increase the performance?


texts: Shrink Tempdb log file when reaching a specific size

texts: how I can shrink tempdb log file when reaching a specific size?? I'm scheduling job to shrink tempdb but still I face a problem of disk space due to tempdb is full!


texts: Grouping data into 5 minute intervals within a time range


texts: How to return a dataset only if an unrelated username and password are correct?

texts:
I have two unrelated tables.  One is Reports the other is Users.  My current queries are:

Select * from Reports;
Select userid from Users where username = @username and password = @password;

Note: Yes, I do salt and encrypt the password in reality.

I want to restate the fact that these tables are not related - that is to say there is no user ID, username, etc. in the Reports table.

My question is: Is it possible to combine these so that the first query will only return a result if the username and password had a match?


texts: PostgreSQL: Unable to run repmgr cloned database

texts:
I'm running tests with PostgreSQL hot standby with 1 master, and exactly 1 slave.

I am using the instructions on this guide: http://www.howtoforge.com/how-to-set-up-a-postgresql-9.0-hot-standby-streaming-replication-server-with-repmgr-on-opensuse-11.4

I'm using PostgreSQL version 9.1, repmgr 1.1.0 and Ubuntu 10.04 LTS.

I followed steps upto step-6 in the guide where I ran

repmgr -D /var/lib/postgresql/9.1/main -d pgbench -p 5432 -R postgres --verbose standby clone pgmaster

on pgslave.

Then I did a

/etc/init.d/postgresql start

on it and it the script (seemingly) finished successfully.
However, executing psql throws error:

  psql: FATAL:  the database system is starting up

Any help on proceeding further is welcome.


texts: Select last 2 records each time based on the record previously accessed records from the in Sql

texts:
I have Message table

DDL

CREATE TABLE [dbo].[Message](
    [ID] [int] NOT NULL,
    [Message] [varchar](50) NOT NULL,
    [DateCreated] [datetime] NOT NULL,
 CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

DML

INSERT [dbo].[Message] ([ID], [Message], [DateCreated]) VALUES (1, N'M1', CAST(0x0000A7F400D63BC0 AS DateTime))
GO
INSERT [dbo].[Message] ([ID], [Message], [DateCreated]) VALUES (2, N'M2', CAST(0x0000A7F400E6B680 AS DateTime))
GO
INSERT [dbo].[Message] ([ID], [Message], [DateCreated]) VALUES (3, N'M3', CAST(0x0000A7F500D63BC0 AS DateTime))
GO
INSERT [dbo].[Message] ([ID], [Message], [DateCreated]) VALUES (4, N'M3', CAST(0x0000A7F600D63BC0 AS DateTime))
GO
INSERT [dbo].[Message] ([ID], [Message], [DateCreated]) VALUES (5, N'M3', CAST(0x0000A7F700D63BC0 AS DateTime))
GO
INSERT [dbo].[Message] ([ID], [Message], [DateCreated]) VALUES (6, N'M3', CAST(0x0000A7F800D63BC0 AS DateTime))
GO
INSERT [dbo].[Message] ([ID], [Message], [DateCreated]) VALUES (7, N'M3', CAST(0x0000A7F900D63BC0 AS DateTime))
GO

Table Message:

ID | Message | DateCreated
1  | M1      | 2017-09-20 13:00:00
2  | M2      | 2017-09-20 14:00:00
3  | M3      | 2017-09-21 13:00:00
4  | M3      | 2017-09-22 13:00:00
5  | M3      | 2017-09-23 13:00:00
6  | M3      | 2017-09-24 13:00:00
7  | M3      | 2017-09-25 13:00:00

I have limited no of rows to be accessed , each time 2 rows.But based on number of row accessed , want to get next 2 rows.

For example:

1) 0 row accessed and get 2 rows:

ID | Message | DateCreated
6  | M3      | 2017-09-24 13:00:00
7  | M3      | 2017-09-25 13:00:00

2) 2 rows accessed and now get next 2 rows:

ID | Message | DateCreated
4  | M3      | 2017-09-22 13:00:00
5  | M3      | 2017-09-23 13:00:00

3) 4 rows accessed and now get next 2 rows:

ID | Message | DateCreated
2  | M2      | 2017-09-20 14:00:00
3  | M3      | 2017-09-21 13:00:00

so on.

I may think of left join with itself and then use sub-query. But I m lost somewhere with my idea.

I may in other words as

select top 2 rows
from   message
where  ID not in (select *
                  from   message
                  where  rows already accessed )
order  by ID desc;

Its like pagination where I saw 2 records on each page. Unfortunately I will not provide the page number , instead I may say I have accessed rows with ID 7, 6 . Next two rows will be with ID 5, 4 and so on.


texts: Is a trade-in its own purchase order, or an adjustment on a sales order?

texts:
Let's say you're a car dealer, selling cars.

When a customer buys a new car, he often trades in his existing car.

What data model makes more sense?

A) create a sales order record for the sale, and a purchase order record for the trade-in, and link them together (related_order_id)?

B) create a sales order record for the sale, and a sales order adjustment for the trade-in?

C) something else?


texts: What would cause a table with 80,000 records to use 145GB of space?

texts:
I have a table that gets inserted and deleted from every day.  On average the record count is 80,000.  Roughly 20 fields, 10 of which are VARCHAR(4000) however most of the records have those fields empty.  1 NC Index account for 8MB.

The job that loads this table runs as expected with no performance issues.

This table has been slowly growing 1GB a day with MINIMAL record count increase(sometimes decrease).  I cant seem to pin point what would cause this.  Any help is greatly appreciated!


texts: Database Design - When to use "attributes" rather than additional columns

texts:
I'm wrestling with a schema design issue.

I'm attempting to determine when I should add another column instead of making it an "attribute".

Rather than adding a numerous columns to a table, I could use an attribute table, with supporting tables if needed, to store this information.

When users decide they need to track another field, it just gets added to the attribute table.

I'm doing the same thing for people on the request. Rather than putting ApproverID, ExecutiveApproverID, ManagerID columns in the table, I add them to a contacts table or approvers table so that a record can have as many related items as needed. This also prevents the user interface from having to add new fields.

This definitely makes understanding the schema more difficult, but it also makes it more flexible. Of course, those attributes will be limited on how detailed they can be in terms of a sub-hierarchy (unless I want to get really complicated).

I'm just getting this off the ground for some business users who will maintain it, so I can't make the schema too complicated.

Do you think pursuing this strategy is a good idea, or should I pursue a design the just adds columns to tables?


texts:
Bit of a strange question. Entries in my table have three relevant columns:

drug
date_1
date_2
seq_id

I am trying to make it such that every drug with the lowest seq_id has a date_2 equal to the date_1 of the drug with the second lowest seq_id (the second lowest will always be lowest + 1) with the same drug name.

Does this make sense?

I don't have any real clue how to go about doing this. I've managed to make a query that returns the date_1 and seq_id of every second-lowest record, and now I want to make it such that every row with a seq_id exactly one lower than any seq_id in this set of records has date_2 = date_1 of the record with the 1-higher seq-id.

God this is really confusing, I hope it comes through.


texts:
What do asynch descriptor resize wait events mean on an Oracle 11g database running on Windows 2008 R2?  According to My Oracle Support, this event applies to any platform, but the description provided includes the following information in Doc ID 1081977.1:

  This event is set when the number of
  asynch descriptor reserved inside the
  OS kernel has to be readjusted. It is
  signaled when the number of asynch
  I/O's submitted by a process has to be
  increased. The Linux kernel does not
  allow the limit to be increased when
  there are outstanding I/O's inside the
  kernel. Hence, all outstanding I/O's
  are reaped before the limit is
  increased. The wait to reap all the
  outstanding I/O's when the limit is
  increased uses this event.
  [Emphasis mine]

This isn't causing a significant problem on our systems, but it would be nice to know what it means on a Windows platform.

This seems similar to a comment on this blog post, particularly because it mentions Oracle 11g R2 with Windows 2008 and VMware.  While this indicates that the problem isn't unique to our systems, it does not help identify the cause or possible solutions.  The other comment about a Microsoft fix applies to Windows 2008 R1 rather than R2.


texts: How to isolate that network is issue of bad query performance

texts:
I would like to know how to isolate SQL Server bad query performance is caused by NETWORK and not the SQL Server.

Are there any specific tips and tricks in SSMS, wait types, or specific PerfMon counter permissable values as far as network is concerned? How do I baseline the network related counters and what values should I consider as good/better/poor?


texts: Insert incoming data (and pk from 2nd table) into 1st table if true boolean value from 2nd table

texts:
This question is further to my previous question answered very well by @ypercube here

I have been trying to work out the easiest way to ask the question without ambiguity, here I go...

I have data arriving from a sensor that contains a unique serial number and a data value. The sensor configuration along with its unique serial number is in a table called device which also contains other settings such as enabled or disabled.

I want to store the incoming data value into another table named datain along with the primary key from the device table.

I am currently doing this with several individual MySQL statements and wish to improve the efficiency by reducing it to one MySQL statement.

Steps to complete are:

Retrieve the primary key and boolean enabled state for supplied sensor serial number from device table
Test if primary key exists and boolean enabled value is true, if either is no, then just exit.
If primary key exists and boolean enabled is true, then store the incoming data value and the primary key (identified by the above test) into another table named datain

Maybe below explains a little better what I am trying to do. I know this is not valid MySQL, just an example of what want.

`INSERT INTO mydb.datain(sensorpk,sensorvalue) values (pk from sensor serial in device table, 25.4) WHERE mydb.device.pk =  pk from sensor serial in device table AND mydb.device.enabled = TRUE;`


texts: If two entities are engaged in a many-to-many relationship with both entities participating totally, how many tables are needed?

texts:
I was thinking three tables, one for entity A, second for entity B and third for the relationship and describe the total participation in the third table by importing primary keys from first and second table and terming them as not NULL.
Am I on right track?

Consider a relation between students and courses, each student must be associated with at least one course (can be more than one too) and each course must be taken by at least one student (can be taken by more than one students too). This would result in a many to many relationship with total participation from both sides.

CREATE TABLE student ( sid INTEGER, age INTEGER, PRIMARY KEY (sid) )
CREATE TABLE COURSE ( cid INTEGER, timings DATETIME, PRIMARY KEY (cid) )
CREATE TABLE TAKES ( sid INTEGER, cid INTEGER, FOREIGN KEY (sid),
    FOREIGN KEY (cid), instructor CHAR(12), PRIMARY KEY (sid,cid) )


texts: return first record after sum of specific value

texts:
I have a table where I want to get the first record after a certain value is reached with sum. I'm using PostgreSQL 9.3.

So - for the following table, summing the amount column for a specific user (eg user 1) I want the record where the amount gets to zero or below, in this case record with deposit_id 8.

deposit_id |   user_id            |   amount
-----------------------------------------------------------
1          |   1                  |   +10
2          |   1                  |   +5
3          |   2                  |   +10
4          |   1                  |   +5
5          |   2                  |   +5
6          |   1                  |   -15
7          |   1                  |   -3
8          |   1                  |   -3
9          |   1                  |   -5

What would the best way to structure a query for a specific user to sum the values and stop when the value has reached 0 or below and return the record which pushed it over the edge (the first record where total sum of amounts <= 0)?


texts: Secondary standby server log shipping backup job error

texts:
I am getting the following error while my log shipping job is trying to copy from the primary server:

C:\Program Files\Microsoft SQL Server\110\Tools\Binn>sqllogship.exe -copy C83E29 23-5131-4767-A902-7E06A2572EA8 -server 831873-DB5

Microsoft (R) SQL Server Log Shipping Agent [Assembly Version = 11.0.0.0, File Version = 11.0.2100.60 ((SQL11_RTM).120210-19 17 )] Copyright (c) 2012 Microsoft. All rights reserved.

2016-11-30 04:24:19.53  ----- START OF TRANSACTION LOG COPY   -----
2016-11-30 04:24:19.61  Starting transaction log copy. Secondary ID: 'c83e2923-5 131-4767-a902-7e06a2572ea8'
2016-11-30 04:24:19.61  Retrieving copy settings. Secondary ID: 'c83e2923-5131-4 767-a902-7e06a2572ea8'
2016-11-30 04:24:19.61  Retrieved copy settings. Primary Server: '642165-DB4', P rimary Database: 'TestDB1', Backup Source Directory: '\\642165-DB4\Incoming', Ba ckup Destination Directory: '\\831873-DB5\Outgoing', Last Copied File: ''
2016-11-30 04:24:19.62  Copying log backup files. Primary Server: '642165-DB4', Primary Database: 'TestDB1', Backup Source Directory: '\\642165-DB4\Incoming', B ackup Destination Directory: '\\831873-DB5\Outgoing'
2016-11-30 04:24:19.65  *** Error: Logon failure: unknown user name or bad password. (mscorlib) ***
2016-11-30 04:24:19.67  ----- END OF TRANSACTION LOG COPY     -----

Exit Status: 1 (Error)


texts: Query - Want the count of ID based on the LAST DATE it appears

texts:
This will be in MySQL. Table consists of following fields:

Date, StockNo, blah, blahblah, blahblahblah

I believe the primary key needs to be Date & StockNo.

This is Inventory Data for widgets.
The assumption I am making is that when the StockNo no longer appears in the data, it was sold as of the date it last appeared.

The thing is this...sometimes a StockNo will disappear on Day T+1 (and thus we'd assume it was sold), but then reappear on Day T+4 (due to data error, or a return, etc...), and disappear again on Day T+6 and NEVER reappear.  So Day T+6 is when we'd consider it SOLD.

So what I am trying to know is the following:

1) How many unique StockNo disappear on Day T+1 vs. Day T? (for all Days)

2) Then AS OF THIS MOMENT how many unique StockNo had their LAST day in the database on Day T?

So the output would show those two counts for all Dates in the database.

In addition to the dummy data below, I have sample data in CSV (40,000 entries per day) which i can send.

Thank you!!!

Additional info:

So if the StockNo shows up in the database on 9/9/16 but NOT on 9/10/16 then we would say that it disappeared and therefore add it to the total count for date 9/9/16.

If that same StockNo later reappears on 9/11/16, when we run the query again that ID will no longer be added to the count as of 9/9/16.  If on 9/12/16 that ID disappears again then it would be added to the count as of 9/11/16.

here's some sample data.  I did not put any data in for the "blahs" since it is not relevant for the query (i believe)

Date,   StockNo,    blah,   blahblah,   blahblahblah

9/8/2016,   1
9/8/2016,   2
9/8/2016,   3
9/8/2016,   4
9/8/2016,   5
9/8/2016,   6
9/8/2016,   7
9/8/2016,   8
9/9/2016,   1
9/9/2016,   2
9/9/2016,   3
9/9/2016,   4
9/9/2016,   5
9/9/2016,   9
9/9/2016,   10
9/9/2016,   11
9/10/2016,  2
9/10/2016,  4
9/10/2016,  6
9/10/2016,  11
9/10/2016,  12
9/10/2016,  13

The expected output run on 9/9/16 would be:

Date, Count of StockNo Last Day

9/8/16,3

9/9/16,8

The expected output run on 9/10/16 would be:

Date, Count of ID Last Day

9/8/16,2

9/9/16,4

9/10/16,6

Note that when the query is run on 9/10/16, the total for 9/8/16 is different from when it was run on 9/9/16 because an StockNo(number 6) disappeared but then re-appeared.


texts: Normalization with two potential primary keys

texts:
I have the following STUDENT relation in first normal form, where id has been identified as the primary key (email here is also unique for all tuples):

STUDENT(id, email, first_name, last_name)

I want to normalize this relation/table such that it is in third normal form (3NF).

The dependencies that I've been able to identify from the above STUDENT relation/table are the following

Full:

{ id } → { email }

Transitive:

{ email } → { first_name, last_name }

Here's the part where I'm confused. I've identified email as a transitive dependency as it can be identified by the primary key (id), and the attribute/column email itself can identify and determine the first_name and last_name.

...if I was to continue with this idea, I would eventually get to a 3NF which looks like so:

STUDENT(id, email)
STUDENT_INFO(email, first_name, last_name)

However, the STUDENT relation/table here seems very redundant, as all it does is point to another relation/table. Is my normalization process here correct? Or did I get my dependencies wrong? It seems that I will always run into this problem whenever the initial relation/table has two candidate keys which both could either be the primary key (id or email both could uniquely identify each row).


texts:
Imagine the following tables:

Products (Id, Name, ...)

Used for storing products

ProductVersions (Id, Version, ReleasedOn)

Used for storing a version history of a single product (v1.0, v2.0, v3.0, ...)

Customers (Id, ForeName, ...)

Well, yeah. Used for storing customers.

Now a single customer can have multiple products. That requires another table:

CustomerProducts (Id, CustomerId, ???)

What is the best approach here? Should I reference  to the Products table (ProductId) or the ProductVersions table (ProductVersionId)?

In terms of joining I'd have access to the Product entity when querying over the ProductVersion of a customer (Customer -> ProductVersion -> Product)

I know it is dependant on my very own design (so if a user owns a product or a version of a product), but what's the best way in general? What if a user can own an entire Product (Product1), but only owns Version 3.0 of Product2?


texts:
We use a oracle 11g database, and we have data coming from our app as well as from external resources.

Now we have some tables that are populated from both UI and the external process/sources, and we have a mapping table in between (this is the important part).

Since the data from the external sources loads once a day (while the app users are inactive), we have a stored procedure(stored_procedure_x) in place that synchs the records(by inserting data in the mapping table) and it works fine. We are able to fetch the data in our app from both external sources and from our tables and read them together.

UI updates however happen more frequently and if we set some triggers up to execute this stored_procedure_x after each insert/update to table_x and table_y. Then because triggers would get fired off before the commit had gone through instead of after, which would screw with the data.

We're now thinking about the options, one of which was :

To have the UI(webserver) call this stored procedure in Oracle once they are done inserting or updating data.

I m just brainstorming what to do now, currently I think the above idea is bad, that this might be better solved in the db layer, got any other ideas?


texts: Can Status of NOEXEC be Checked with a DMV?

texts:
On larger DDL scripts for deployments, usage of SET NOEXEC ON as part of error handling is commonplace to prevent execution of downstream code. If coupled with SET NOCOUNT ON, there's a chance code execution may be inadvertently suppressed unless the proper post-deployment checks are done. I checked sys.dm_exec_sessions and there is no attribute for either SET statement. Is there an alternate method of checking the status of either statement for the current session or any user's session from the DBA perspective?

Here's a quick example...

CREATE TABLE dbo.MJZTest
    (Col1 int)
ON [PRIMARY2]
GO
IF @@ERROR<>0
    SET NOEXEC ON

INSERT INTO dbo.MJZTest (Col1)
VALUES (1)
GO

In the above example, if the [PRIMARY2] filegroup doesn't exist, preventing table creation, the @@ERROR check will call the SET NOEXEC ON statement which will prevent actual execution of the subsequent INSERT statement. I've seen this type of code generation with tools such as RedGate SQL Compare.

The catch is, if the person executing the code neglects to run SET NOEXEC OFF to reset the session during debugging, they may get a Command(s) completed successfully. message regardless of the actual outcome.


texts: Two-way replication between SQL Server 2005 and SQL Server 2008

texts:
We currently have a SQL Server 2008 and SQL Server 2005 participating in one-way transactional replication of a single database.

If possible we would like to set this up as a Two-way replication. What would be the preferred method of replication to achieve this? There is a moderately high latency between these servers. However, the growth of the database to be replicated is minimal.


texts:
I have been told to implement SSL in my Master-Master MySQL instances by my security team to have secure traffic only to be received at the database end. I have never implemented SSL in a database instance and I have seen it only in App and Web layer.

Version - Percona 5.6 | Platform - Linux x86

I need to know are there any challenges in implementing SSL in MySQL and challenges if any, in regard to the application connectivity to the MySQL instance if SSL is implemented.

Also, when SSL is implemented, all the users (including the user which I use for Master and Slave) when created in the database should contain the keyword REQUIRE SSL  as given in this link including those application users which connects from the app to DB?

I'm thinking of referring steps mentioned in How to enable SSL for MySQL server and client


texts: What to call a column that references another row in the same table?

texts:
I have inherited a table that contains a column that is used to "chain" between various related rows within the same table.

For instance:
id, bar, reference_id

1, 'foo', 2
2, 'foo', 3
3, 'foo'

I have no idea what to call this structure in order to search for how I should deal with it.   The idea is that the table maintains a sort of living history within itself.   (my problem is that I may have row 1 or 2, but I need to get to row 3 somehow that is agnostic to the number of 'levels' that I need to traverse)


texts: What events cause each run_status code to appear in sysjobhistory?

texts:
We have been asked to report on the health of our SQL jobs. I am having trouble understanding the run_status column in dbo.sysjobhistory. The MSDB page for sysjobhistory gives this description:

  Status of the job execution:

  0 = Failed
  1 = Succeeded
  2 = Retry
  3 = Canceled

What events cause each status code?

Here is what I have done to attempt to produce each status code. I was able to reproduce...

0 (Failed) by writing SQL that throws an error.

For example, step 1 is SELECT SELECT

1 (Succeeded) by writing SQL that succeeds.

For example, step 1 is SELECT 1

3 (Cancelled) by stopping a job while it was running

4 (In progress) by querying sysjobshistory while a job was running

I know a way produce 0's, 1's, 3's, and 4's, but I don't know all of the ways to produce them, and I have no idea how to cause a 2.

The health of our jobs is binary. We are either healthy or not healthy, because the end user does not want to see anything more granular than that on their dashboard. That is why I am asking this question - I have to interpret the status codes and run durations and draw a single, binary conclusion about the health of the SQL jobs. The problem is that MSDN doesn't explain what the status codes mean, or more importantly, what events might cause them.

For example, if a user cancels a job (resulting in a run_status of 3), we might decide that we don't care and just report that we are healthy. However, if a job gets cancelled because of some other event, we might care about that.


texts:
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


texts:
I just started learning PL/SQL an hour ago... I'm trying to set profile settings dynamically... I select distinct profile names from dba_users and I'm trying to run an alter profile command for each profile name. How can I get the alter command to work in a loop like this?

SQL> SET SERVEROUTPUT ON
SQL> declare
  2  begin
  3     for rec in (select distinct Profile from dba_users) loop
  4       EXECUTE IMMEDIATE 'alter profile :1 LIMIT PASSWORD_LIFE_TIME UNLIMITED' using rec.PROFILE;
  5       EXECUTE IMMEDIATE 'alter profile :1 LIMIT PASSWORD_REUSE_MAX UNLIMITED' using rec.PROFILE;
  6     end loop;
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-00931: missing identifier
ORA-06512: at line 4


texts: how to change nls_language in nls_instance_parameters

texts:
Oracle 12c (12.1.0.2.0) database

Issue: I wanted to change NLS_LANGUAGE and NLS_TERRITORY in NLS_INSTANCE_PARAMETERS. I know how to change for session, but I wanted to change it for the instance. The reason why is because stored procedure scripts weren't running correctly due to timestamp. I was getting this error:

start_date=>TO_TIMESTAMP_TZ('12-MAY-2015 02.00.00.000000000 AM +05:30','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
ORA-01855: E necessario AM/A.M. ou PM/P.M.

Reasononing: The NLS_DATE_FORMAT and the NLS_TIMESTAMP_FORMAT were the same across. I even tried unset LANG and unset NLS_LANG. Despite all of this I was still getting date conversion errors, so I decided to update all of the NLS-related parameters to the same values across all views. My database (nls_database_parameters) and session (nls_session_parameters) were showing the same NLS_LANGUAGE, but my instance (nls_instance_parameters) was not.

Solution: To fix this I had to update the init<db>.ora and the spfiles<db>.ora
I didn't really see this explicitly specified anywhere except for maybe on Burleson Consulting's website, so I decided to put my findings here to help others with the same issue.

I updated NLS_LANGUAGE and NLS_TERRITORY in NLS_INSTANCE_PARAMETERS, restarted the database, and it fixed my issue.

I had to force restart because for some reason I couldn't restart normally.


texts: Can an application connect to databases from on-premise SQL Server Instance and Azure SQL together?

texts: Can a single application connect to two different databases each of which is in different Instances. One instance has on-premise deployment, the other one is Azure SQL(not SQL in Azure VM). So basically is it possible for an application to access data from both Instances?


texts: returning set of records in stored procedure

texts:
I would like to return a set of records from a pl/pgsql. Is there a way to do that without using the "for" construct and cursors? For instance when I compared these two stored procedures:

create or replace function sqlProc () returns setof integer as $$
  select * from foo;
$$ language sql;

create or replace function plpgSqlProc () returns setof integer as $$
declare
  c integer;
begin
  for c in select * from foo loop
    return next c;
end loop;

end;
$$ language plpgsql;

The pure SQL version has 2x better time performance than the pl/pgsql one! Unfortunately I have other logic that cannot be expressed in pure SQL so I am wondering how I should write my stored procedure?


texts: check if linked server table column exists

texts:
I have several access databases that I'm copying over to the sql server, connecting to as a linked server, and then pulling data from each night.

I need to be able to identify if the current access db the linked server is pointing too has a particular column.  If not I'll need to create the column before copying the next access db over.

Is it possible to check if an access column exists through a linked server connection?


texts: Chained MySQL replication from Master->Master replicated pair

texts:
This is a pictorial view of what I have:

Master 1<----->Master 2
|                     |
|                     |
V                     V
Slave 1         Slave 2

The masters are never written to at the same time, rather Master 2 is a standby server. Both Masters have log-slave-updates turned on in my.cnf.

If a table is created in a replicated DB on Master 1, it is present on Master 1, Master 2 and Slave 1. It is NOT present on Slave 2.

In the same way, if a table is created in a replicated DB on Master 2, it is present on Master 1, Master 2 and Slave 2. It is NOT present on Slave 1.

Why is this the case? Is it a limitation of MySQL in how it stops master/master replicated servers from creating replication loops if both have log-slave-updates turned on?

Is there a way this can work?


texts: Update query for Minimum absolute difference in Access [SQL]

texts:
I have 3 tables in an Access db. Table_1 and Table_2 are where the source data comes from and I am filling Table_3.

Table_1 has the following fields:

Acct_Name
Amt

Table_2 has the following fields:

Acct_Name
Amt_1
Amt_2

Table_3 has the following fields:

Acct_Name
Final_Value

Taking the values in Table_1.Amt to be valid, I want to know which of the two values, Amt_1 or Amt_2, from Table_2 is a better match to Table_1.Amt. Amt_1 and Amt_2 are arrived at via 2 different summing algorithms (different pieces summed together to arrive at the total value). I want to use the closer match to fill Table_3.Final_Value.

For the sake of simplicity we'll say there's a 1-to-1 match for Table_1.Acct_Name to Table_2.Acct_Name.

Since it is known that for a large fraction of rows in Table_2 (Amt_1) - (Amt_2) = 0, I start with the following append query (Table_3 is empty):

INSERT INTO Table_3 ([Acct_Name],[Final_Value])
SELECT [Table_2].[Acct_Name], [Table_2].[Amt_2]
FROM Table_2
GROUP BY [Table_2].[Acct_Name], [Table_2].[Amt_2]
HAVING ((([Table_2].[Acct_Name])<>"0"));

At this point all Table_3.Final_value fields equal Table_2.Amt_2 with Table_2.Acct_Name equal to Table_3.Acct_Name. Now I just want to update any Table_3.Final_Value to Table_2.Amt_1 if the absolute value of the difference between Table_1.Amt and Table_2.Amt_1 is less than the absolute difference between Table_1.Amt and Table_2.Amt_2 where Table_1.Acct_Name equals Table_2.Acct_Name. So I run the following update query:

UPDATE Table_3,
Table_1 INNER JOIN Table_2 ON [Table_1].[Acct_Name] = [Table_2].[Acct_Name]
SET [Table_3].[Final_Value] = [Table_2].[Amt_1]
WHERE (((Abs([Table_1]![Amt]-[Table_2]![Amt_1]))<(Abs([Table_1]![Amt]-[Table_2]![Amt_2])))

Somehow when I run the above update query all rows in Table_3.Final_Value are equal. What!?

Any help would be appreciated.Thanks in advance.


texts: Is it a better idea (performance is key) to store in a normalized table and retrieve by JOIN or in a single nvarchar column and retrieve using LIKE?

texts:
NOTE: The application is not really about websites or keywords, I am using that here only to provide reference points for discussion.

I have a data set that is really just a string of words. Think of at "SEO" keywords for a website. I have a table that contains data that relates to this data. For example, say a list of websites.

The key scenario for this data set is retrieval against the master data, in two-way fashion. That is, while I can get a website with all its associated keywords, I should also be able to input a keyword and get a list of websites attached to that keyword. There is no other metadata attached (or attachable) to the keyword itself.

Now, given that query performance while retrieving the website -> keyword and keyword -> website (significantly more use cases) is paramount, which of these design scenarios makes more sense?

In the websites table, I have a single nvarchar column that contains a string of all the keywords for that website, possibly comma-separated. Retrieval in this case would be using a LIKE operator on that row.
I create a separate keywords table, with two columns (Id, Keyword), put all the keywords in there, and then have a third table WebsiteKeywords that contain the mappings between the websites and keywords tables. To retrieve, we do a three-way join between websites, websitekeywords and keywords tables.

The retrieval is designed to happen via web service infrastructure, so there will be multiple look ups from the middle-tier layer before it thinks it has all the data. So a single "search" fired as a call will result in multiple such look ups, all results will be aggregated by that middle tier before being returned to the caller.

What are your suggestions?


texts: MySQL: Join one Row in a table with two Rows in other table

texts:
In my MYSQL Database COMPANY. I have two tables, like below in my diagram (arrow shows relations):

  `users`                          `user_login`
+--------------+            +-----------------------+
| column_name  |            | column_name           |
+--------------+            +-----------------------+
| user_id      |<---|       | user_login_id         |
| first_name   |    |-------| user_id               |
| email_id     |            | user_name             |<---|
+--------------+            | created_by            |----|
                            +-----------------------+

Table: user_login with recursive association, such that  an employee's username can be created by his boss  in some web-based application . A self relationship of something like (created_by (1)- user_name (∞)).
Table: users contains personal information for each user(*or say for each username in user_login table*)

All fields are varchar(64) in both tables. And user_name is has uniqueness constraint. (the actual database I am working with is enough big with more columns and many rows, but I am just putting only useful informations)

[Query]

Input to my query is user_name.

I need a query If I give user_name value it return me information from user table (fist_name and email_id) for both user and his immediate boss.

Suppose my user table is:

mysql> SELECT `user_id`, `first_name`, `email_id` FROM `users`;
+----------+------------+---------------------------+
| user_id  | first_name | email_id                  |
+----------+------------+---------------------------+
| 1        | Grijesh    | grijesh.mnit@gmail.com    |
| 8        | Sumit      | sumit@cscape.in           |
| b        | OMD        | grijesh.chauhan@cscape.in |
+----------+------------+---------------------------+
3 rows in set (0.00 sec)

And user_login is

mysql> SELECT user_login_id, user_id,  user_name , created_by FROM `user_login`;
+----------------+-----------+-----------+------------+
| user_login_id  | user_id   | user_name | created_by |
+----------------+-----------+-----------+------------+
| 13             | 1         | grijesh   | omdadmin   |
| 89             | 8         | sumit01   | grijesh    |
| bd             | b         | omdadmin  | SuperAdmin |
+----------------+-----------+-----------+------------+
3 rows in set (0.00 sec)

Then for the input user_name = 'grijesh' output details should be from user table about omd and grijesh.

For this I written a query like below (that is working fine):

mysql> SELECT  `user_login`.`user_name`,
               `users`.`first_name`,
               `users`.`email_id`
       FROM    `user_login`, `users`
       WHERE   `user_login`.`user_id` = `users`.`user_id` AND
                (`user_login`.`user_name` = 'grijesh'
                  OR
                 `user_login`.`user_name` IN ( SELECT `created_by`
                                               FROM `user_login`
                                              WHERE `user_login`.`user_name` = 'grijesh' ));

its output is like:

+-----------+------------+---------------------------+
| user_name | first_name | email_id                  |
+-----------+------------+---------------------------+
| grijesh   | Grijesh    | grijesh.mnit@gmail.com    |
| omdadmin  | OMD        | grijesh.chauhan@cscape.in |
+-----------+------------+---------------------------+
2 rows in set (0.06 sec)

[QUESTION]:

Can we have better efficient query equivalent of my above. I tried to think a solution with Joins but its hard to me for this query because for a row in user_login I need to join with two rows in user table.

I tried with join but I couldn't. I need a solution with Join instead nested query like I did.

Any help or suggestion will be great help.


texts: What can we do in MySQL 5.0 Replication to address bandwidth concerns?

texts:
I am developing an application to run on the client PC (Win) which is configured with a MySQL server 5.1 instance that will act as read-only slave to the remote master. The remote master has dozens of schemas, but I only need one per client so I supply the replication-do-db setting in my.ini to only replicate the schema that the client needs. The replication works, but when our clients get into regions of the world where internet access is only available via 3G wireless, which charge by data usage, they quickly exceed their data plan limits and run into expensive problems.

As I understand it, MySQL writes all transactions for all schemas into a single binlog file which means that each client has to download all of the transactions that are performed on every schema on the master, then once downloaded, apply the database filter per replication-do-db settings in the client's my.ini file.

To minimize this inefficiency I have employed the slave_compressed_protocol = 1 setting, which seems to reduce the transmitted data by 50%, but still causes our client's to quickly exceed their data limit rack up the 3G bill.

I can't imagine I'm the only one facing this, so I'm sure I'll get a ton of answers on how to achieve this by setting x = y. However, I can't find any documentation of such a setting, nor a recommended approach to take.

So far, here's my thought to a possible solution, please provide feedback or alternate routes:

Set up a "proxy" slave for each schema (on different box, or same box with a different MySQL instance/port)
Configure the proxy slave to replicate-do-db only the one database that the clients wish to replicate.
Configure the client's MySQL instance as slaves to the appropriate proxy slave.

This should result in the client only pulling the binlog data for their schema. The downside (as far as I can tell) is that it dramatically increases the complexity of our setup, likely making it more fragile.

Thoughts? Will this approach even work?

Note, we are running the MySQL 5.0 server on RedHat, but we could upgrade to 5.5 if it produces a solution.


texts:
I've been tasked to try to recover a database which suffered from corruption (due to I/O failure, which has been fixed since). I'm not familiar with the database or what it contains.

I've been given an old (~3 weeks) full backup and a series of transaction logs... however there are missing transaction logs, so I can only recover up to a certain date. There's like 2.5 weeks of data missing (and there's a lot of data being added to this database constantly).

I also have been given a copy of the corrupt database (which is accessible, but with a lot of pages corrupt/missing).

I've tried the typical DBCC CHECKDB commands (still no repair_allow_data_loss, that will be my last resort if nothing else works).

After many comes and goes to the database (the db is a 1.5 terabyte little monster and everything I do is slow and takes a while), I've tried to do an online page restore from the last known good backup for the corrupt pages.

To do that, I've done an script that creates many RESTORE DATABASE <foo> PAGE='pages' FROM DISK='<bar.bak>' commands from the DBCC CHECKDB output (básically a regex and a distinct)... so far so good, this worked up to a point where it said I had reached a limit of 1000 pages per file (there are 8 files on this db) per restore command.

So it asks me to "complete the online restore", but I'm at a loss at how to do that... I don't have a tail log or anything more complete than the full backup I'm starting with, so I basically don't know how to complete the restore to keep trying with the rest of pages.

I've tried a RESTORE DATABASE <foo> WITH RECOVERY but that didn't work either, it asks me for a log which I don't have.

Does anyone have any tips on how I could try to recover anything from here? Or how to "complete" the online restore so I can keep trying to recover more pages? Would I have the same problem if I try an offline restore (basically adding WITH NORECOVERY to everything and then try to bring it back at the end?)

Working out the database by hand is basically undoable... there are hundreds of tables with millions of rows and there's no clear meaning of what any of it are. The corrupt DB will fail on SELECT queries after some million of rows but I'm not sure I can work out where. I've tried rebuilding all non-clustered indexes, but there are corrupt pages with row data, so that didn't work either.

Some data loss would be acceptable, but consistency on the DB should at least try to be achieved.

The corrupt database is -still- online and clients are working on it (so it keeps getting new data), so any process I do on the lab bench should be reproducible on the production database afterwards (downtime will be hard for it).

This is SQL Server 2014 Enterprise

PS: I'm no DBA... I'm a programmer, but the client has tried some "expert" sql disaster recovery services and they have given up, so I've been asked to look at it and see if I could do anything.

Update: after many tests, the page by page restoring was a no-go, so we've ditched the idea. We are going for a manual recovery (manually selecting missing records from the corrupt tables and inserting them in the last known good backup), doing some automated tools for it (again, there are hundreds and hundreds of tables).


texts: What is the purpose of adding new file to the database?

texts: I'm fairly new at Microsoft SQL. I've seen many tutorials online on how to add a file to the database. What I still don't get is why?


texts: Incurs_seek_penalty column of sys.dm_os_volume_stats

texts:
A new column, incurs_seek_penalty, was introduced in the SQL Server 2019 sys.dm_os_volume_stats DMF. This tinyint column is not currently documented.
Does anyone have information about the enumerated values returned in this column? I've seen values of 0 (bare metal server) and 2 (VM). I'm curious as to what these values mean and what other values might be returned. My internet search hasn't turned up anything.


texts: I am making a database for my family but they live in other places and I run the database so they are on another network. when I try to download data out of my database on my local network it works but when I try it on someone else his network it does not work because it keeps loading without doing anything. my port for my database in my pc and router are opened. I am not getting error messages in my PHP log file. how can I fix this problem?


texts: SQL Server 'Create Database' statement. How to inherit autogrowth settings?

texts:
I am using SQL Server 2008 R2 and creating databases through continuous deployment.

In our system, the default 1Mb/10% autogrowth settings in SQL Server work badly with our data. Particularly as we have a legacy application that prevents us from changing the schema much. We would like the database settings to be configured at the instance level so we can change it for staged deployments.

I have read in several places that default settings for a new db are based on the settings for 'model', but it appears this only works by clicking new database in SQL Management studio UI and not from a script e.g. CREATE DATABASE [MyDb].

msdn.microsoft.com/en-us/library/ms186388(v=sql.105).aspx

sqlservercentral.com/Forums/Topic1065073-391-1.aspx

https://stackoverflow.com/questions/8828557/possible-to-configure-database-autogrowth-settings-at-the-instance-level/8828604#comment15586568_8828604

Has anyone actually got this to work with a create script? Is there another way I can set autogrowth per server instance?


texts:
I have a little problem. I have to delete all the data from our database. I don't want to drop tables, only delete the data sets.

These are 47 tables in total.

I tried the following:

Delete FROM(select TABLE_NAME from USER_TABLES);
Delete FROM(select * from ALL_ALL_TABLES Where OWNER = 'DB_NAME')

But that is not valid. How can I efficiently remove all the data from all my tables?


texts:
The list of the files on my hard disks has grown up to a million, the more or less, of records, each consisting of a eigth or ten fields, es.
support id - file size -  full path name - md5 hash - sha256 hash

All is stored in plain text files realized with the DOS's DIR command.

I must restructure the archive (suppressing unwanted redundance, organizing backup procedures ...), therefore I need a (free for personal use) database system, running under Windows Xp, able to perform simple queries on  the file resulting from the unification of aforementioned lists (or import it) and exporting the result as plain text or XML.
In the past, I have unsuccessfully tried Microsoft Access.

I have no need to share or publish the dataset, because it is for my personal use only.

Please, can you suggest any suitable software ?

Thanks in advances !!


texts: What SSIS variable/ paramter to use to capture looping server name?

texts:
I am a newbie to SSIS.

BACKGROUND:- We are trying to make Database Inventory of about 100 SQL Instances. First task is to check SQL connectivity for all servers and capture the failed connections. Although the package runs fine by using SELECT @@servername to capture SQLInstanceName, but there is below challenge.

CHALLENGE:- Using @@servername is not helpful in our case because many servers have their alternate DNS names in AD. Due to this, select @@servername returns the different name than that of hostname. Due to this, the final Inventory shows that DNS_Name, whereas the source server list has HOSTNAME. Hence @@servername creates a mismatch and not helpful for us.

REQUIREMENT:- Somehow capture the exact looping SQLInstanceName from the source server list instead of using @@servername. So, that we can have exactly the same name as in source list.

Dynanmic Connection: "Data Source="+ @[User::Srv_Conn]  +";Initial Catalog=master; Provider=SQLNCLI11; Integrated Security=SSPI; Connect Timeout=0; Auto Translate=False"

public void Main()
    {

        SqlConnection conn = new SqlConnection("Data Source=" + Dts.Variables["User::Srv_Conn"].Value.ToString() + ";Initial Catalog=master;Integrated Security=SSPI;");

        //MessageBox.Show(Dts.Variables["User::Srv_Conn"].Value.ToString());

        try
        {
            conn.Open();

            string SQLLoop;
            SQLLoop = Dts.Variables["User::SQLBeingLooped"].Value.ToString();
            Dts.Variables["User::SQLCommand"].Value = "SELECT" + SQLLoop + ",name from sys.databases where name not in ('master','model','tempdb','msdb','distribution','Autodeskreplication','AdminDB')";

            conn.Close();

            Dts.Variables["User::CFlag"].Value = true;
        }
        catch (Exception ex)
        {
            Dts.Variables["User::CFlag"].Value = false;
        }
    }

I know that it is all basics of C# and variables....I should learn these first. I am currently doing this on half knowledge. Appreciate any help.