texts:
I  created a mondrian schema xml with schema workbanch after generating the xmh file successfuly, I uploaded it in Mondrian schemas catalog , then  I created the docuemnt in "documents developper" section, but when clicking on generate schema I get ERROR -Generic Error-, (nothing is added in log file); and when I open the principal menu where fila user should navigate in OLAP, I find this error  :
Template is missing "reference" property, that is the reference to the Mondrian schema
anyone can help me?


texts: Using IDs from multiple tables in a single column

texts:
One of my co-workers created a schema similar to the following.  This is a simplified schema including only the parts necessary to address this question.

The system rules are as follows:

Departments can have 0 to many divisions.
A division must belong to only one department.
An article can be assigned to either a department, or a division of that department.

The schema is:

Department
----------
DepartmentID (PK) int NOT NULL
DepartmentName varchar(50) NOT NULL

Division
--------
DivisionID (PK) int NOT NULL
DepartmentID (FK) int NOT NULL
DivisonName varchar(50) NOT NULL

Article
-------
ArticleID (PK) int NOT NULL
UniqueID int NOT NULL
ArticleName varchar(50) NOT NULL

He defined the schema using an imaginary rule (for lack of a better term), that all DepartmentIDs would be between 1 and 100, and all DivisionIDs would be between 101 and 200.  He states that when querying the Article table, you will know whether the UniqueID is from the Department table or the Division table based on what range it falls into.

I think this is a poor design and proposed the following alternative schema:

Department
----------
DepartmentID (PK) int NOT NULL
ParentDepartmentID (FK) int NULL /* Self-referencing foreign key.  Divisions have parent departments. */
DepartmentName varchar(50) NOT NULL

Article
-------
ArticleID (PK) int NOT NULL
DepartmentID (FK) int NOT NULL
ArticleName varchar(50) NOT NULL

I believe this is a properly normalized schema and properly enforces relationships and data integrity, while honoring the business rules outlined above.

My specific question is this:

I know using one column to contain values from two domains is poor design, and I can argue the benefits of the foreign key in the Article table.  However, can someone provide a reference to a specific database design article / paper that I can use to backup my position.  If I can point to something concrete, it will make it that much easier.


texts:
SELECT * FROM wp_posts WHERE ID IN
(
(SELECT courses  FROM wp_category WHERE CatID =401) OR
(SELECT  meta_value FROM wp_postmeta WHERE  post_id IN (SELECT courses FROM wp_category WHERE CatID =401) AND meta_key ='post_id' )
)
AND
ID IN (SELECT post_id FROM wp_locations_courses WHERE location_id = 1120)

I am having the problem with above query while i am using IN with sub queries gave me the error Subquery returns more than 1 row why it happens when i am using IN clause


texts: How to debug difference in command line and work bench out put for MySQL?

texts:
I am supposed to create an automated CSV report from our MySQL database. For this purpose I have a standard script from here

mysql -u $USER -h $DBSERVER  -p $PASS $DB -B -T -e "source myscript.sql" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' >/home/ec2-user/temp.csv

Problem is, that the created CSV file has some fields as null or erroneous data. However the query does not seem to be the problem since I inserted the same query verbatim in MySQL work bench and got expected results as well as exported correct CSV through workbench out put export.

My first hunch was SED script could be incorrect but after trying other methods and directly looking at the tab separated file that does not seem to be the case. Following is how I obtained the tab separated file

mysql -u $USER -h $DBSERVER  -p $PASS $DB -B -T -e "source myscript.sql" >/home/ec2-user/temp.tsv

So the MySQL script itself on command line is not producing correct data. The tab separated file had the same erroneous fields, like the final report.

The report currently has around 6000 rows and around 40 columns, and is growing. The fields which are erroneous are in minority though important and are repeatable with the entire script.

I tried debugging the issue by limiting the number of columns or only extracting the problem rows along with 5 preceding and succeeding those problematic rows using mysql client on terminal, and each time I could not reproduce those erroneous fields and I got the correct results. In fact after my debugging session when I try to create the report file itself on terminal it renders correctly.

However whenever later, I try to create the report file again in one go, the problem surfaces.

What do I want?
Could any body please provide with debugging methodologies to find out why terminal would produce particular fields consistently wrong while MySQL workbench gets them right every time.

I haven't been able to read the direct terminal output of the script since it is large, however the file to which it is piped to, shows those errors.

Since the data belongs to my employer, and only few particular fields are having these errors which I am unable to create a simple replication case, I am not looking for a solution to my problem, but rather ways to find solution to the problem.


texts: Convert query in words to relational algebra

texts:
I have a follow-up question to a question I have previously asked on SO.

Instead of the query from my initial question, I want to convert the following into relational algebra:

  List the names and phone numbers of the bidders who are not always affected by
  double-bidding.

Note: Double-bidding occurs when two different bidders bid the same price on the same exact item.

I have some ideas as to how to proceed:

Find all bidders that have double bid (1)
Find all bidders who have a bid that isn't double-bidding (2)
Find all bidders who have never placed a double-bidding bid (3)

From here, I can get the intersection of (1) and (2) and add (3) to this intersection to get the final answer. (That is my thought process, please correct me if I am wrong)

I have no issues in finding all bidders that have placed a double bid, but the consequent ideas are confusing me quite a bit. This is what I have for "all bidders that have double bid":

BID⨝ITEM         - (Q1)
Q1 ⨝ ρbid→bid', iid→iid', price→price'(Q1) - (Q2)
πbid(σbid != bid'(Q2) ∧ σprice = price'(Q2) ∧ σiid = iid'(Q2)) - (Q3)

How do I use this to find the bidders who are not always affected by double bidding?

By the way, the bolded text is merely for labeling purposes and is not part of the answer.


texts:
For this query, I'm trying to average the price of all orders for a given restaurant. It works perfectly fine for the sum:

SELECT r.restaurantName, SUM(m.price) AS 'totalPrice w/o Tax', SUM(m.price *1.10) AS 'totalPrice w/ Tax'
FROM Restaurant r
INNER JOIN MenuItem m
ON m.restaurantNo = r.restaurantID
INNER JOIN FoodOrder f
ON m.itemNo = f.itemNo
WHERE r.restaurantName = "Eureka Pizza";

But when I try to average that I get an error:

SELECT r.restaurantName, SUM(m.price) AS 'totalPrice w/o Tax', SUM(m.price *1.10) AS 'totalPrice w/ Tax', AVG(SUM(m.price)) AS 'avg base price'
FROM Restaurant r
INNER JOIN MenuItem m
ON m.restaurantNo = r.restaurantID
INNER JOIN FoodOrder f
ON m.itemNo = f.itemNo
WHERE r.restaurantName = "Eureka Pizza";


texts:
How do I create a table with a formula column in MySQL version 5?

For example: I have a table named Product, which has 5 columns:

id int,
amount int,
sold_out int,
faulty int,
remain_amount is (amount-(sold_out+faulty))

I will not use a trigger or a view.

I am looking for a solution similar to what, in SQL Server, is called a computed column.


texts: Help Writing an UPDATE Statement With JOIN

texts:
I am new to SQL Server and am attempting to write an UPDATE statement that includes a JOIN. Essentially this is what I am trying to write, but obviously this is invalid syntax.  In SQL Server 2008 R2 what would be the appropriate way to write this syntax?

Update mi
Set totalvalue = TV
FROM masterinfo mi
Inner Join xyz
(
  Select accID
  ,SUM(totalval) TV
  from OPENQUERY(EA, 'Select accID, totalval from sv')
  GROUP BY accID
) xyz


texts: Handling Recovery of Disk Space in MySQL and MongoDB

texts:
So I have worked with MySQL all my life (not as a DBAdmin, but a programmer), and I have some limited knowledge about maintaining MySQL databases.

Recently a portion of our database has been moved to MongoDB and we have outsourced MongoDB administration to another company. They informed us that with MongoDB, when data is deleted disk space is NOT recovered and that specific clean up tasks need to be done for this which require downtime.

What I wanted to ask here is that: They also said the same is applicable to MySQL (where if we delete data, disk space is not recovered and we need to run clean up scripts for that purpose). I was never aware about this regarding MySQL.

Can anyone tell me briefly how this works for MySQL or point me to any good resources that I can understand?


texts: Renaming table and putting a view in its place on Postgres

texts:
I have a very central table in our database that is used by a range of applications, it has rules attached to it, triggers and all the dependencies that you can imagine. Now I would like to modify the table without causing any problems with the dependencies. I have previously been successful with doing the following but in a much less complex case:

alter table reconciliations rename to matches;
create view reconciliations as select * from matches;

What this achieves is that I could now modify the new "matches" table and for example add a column or rows, which do not need to get presented in the "reconciliations" view (by adding a where clause to filter them out).

I am on Postgres 9.5 so the view is automatically updatable. Initial tests shows that there are no immediate problems with this, so I am asking this question to know what kind of problems I should be looking for. Performance is not a big issue.


texts: PostgreSQL cascading triggers based on the condition of which other table triggered it or a value given by the other trigger?

texts:
I want to keep (slightly different) tables synced to one another and with only 2 tables, this is fine. Because of pg_trigger_depth() = 0 in the WHEN clause they will not trigger each other.
I currently have the following database triggers defined:

Table From
Trigger
Table To

V1
V1 -> V2
V2

V2
V2 -> V1
V1

But I now want to have a V3 (or even more, V4, V5) table that also has to be synced. I want the triggers to trigger after one another but only in the right direction, e.g. V1 -> V2 -> V3, V3 -> V2 -> V1 or when V2 gets an insert/update/delete from V2 to V3 and from V2 to V1.
So my question is is it possible to come up with a solution that if V1 gets an insert/update/delete it first updates V2 and then only triggers the trigger from V2 -> V3 and not also the one from V2 back to V1.
I was thinking of some kind of 'forwards' and 'backwards' value when pg_trigger_depth() is not 0. Because a trigger is always either forward or backwards it can maybe be built into the trigger that it only triggers other 'forwards' or 'backwards' triggers when pg_trigger_depth() > 0.

Table From
Trigger
Table To

V1
V1 -> V2
V2

V2
V2 -> V1
V1

V2
V2 -> V3
V3

V3
V3 -> V2
V2

-
-
-

V4
V4 -> V5
V5

V5
V5 -> V4
V4

A different solution I can think of is to make a trigger from every table to every other table but this can quickly become too much when a new, for example V5, table gets added. In the above question only 2 triggers will have to be defined, but in this case 8 new triggers with slightly different changes will have to be made.


texts:
I know how to get the name of the current node, but is there a simple T-SQL query to use to get the Windows Server Failover Cluster name that the clustered instance is part of?

The SQL Server version is 2014 and the OS is Windows Server 2012


texts:
I wrote a sql procedure to get the name of the physician by entering the Eid in sql server.

create procedure doca(@eid int,@pname varchar  output)
as
begin
select @pname =p.name
from physician p
where p.eid =@eid
end

I used the procedure by following way

declare @pname varchar
exec doca 1,@pname output
print @pname

when I enter 1 I want to get the name of the physician whose eid is 1.

in this case it should be "John Dorian"
but this procedure prints just "J"

whats wrong with my code?


texts: Is it possible to set PostgreSQL roles with privileges this way?

texts:
I'm trying to develop a system, and for the first time I'm using PostgreSQL. I'm trying to organize and separate roles properly, but I'm losing my mind with it (about a week on this now).

Following the title of this question, is it possible to set the following(?):

A user who can do everything with all tables, except perform CRUD
operations on the records of the same. In addition, this user can not create, delete, modify or insert roles.
Another user who can perform all CRUD operations on table records,
but can not perform any structural change operation in database
objects. This guy cannot create/D/M/I roles as well.
A third user who behaves like DBA, able to do anything.

The first user serves as a limited DBA who can make structural changes, but can not snoop data. The second is a system using the database. The third and last is a DBA with full control.

This question comes from another one previously made by me, similar, but not equal. I do not want to know what's wrong with that code anymore.  Also, I believe that here is the most appropriate place to ask things like that.

I want to know if this is possible. If so, I would like to see how. If not, I want an explanation of why.

Thank you for your attention in advance.


texts: How to Use a Certificate from a Different Database

texts:
Here are the requirements:

  Key-encrypting keys are stored separately from data-encrypting keys.  ( Certificate and the symmetric key should be stored separately – not within the same database)

I am using certificate based encryption so I made the certificate on the database I'll be using.  How do I access a certificate from a different database?


texts: Best practise for copying a database from production

texts:
Currently after releasing a new major or minor version of our application, we take a copy of the production database and perform the below process:

Copy production database to test server
Obfuscate the data and setup the test users
Switch the old test database with the obfuscated new database
Repeat for the development database.

The intention of this is to ensure all our environments are as close to one another as possible.  This originated as a manual process but due to increasing complexity we've started to automate it.

However what is the best way to automate a post deployment process such as this?  Our currently automation is a bespoke c# program - however, would an SSIS package be more appropriate?  Or is there a tool out there that is made for the job?


texts: SQL Server sqlcmd setvar depending of selected database

texts:
I'm using sqlcmd for a moment now to update database on our project.

Before we have use script like this:

:setvar relativepath D:\Project1\Sql
:r $(relativepath)\Tables\CreateTable1.sql
:r $(relativepath)\SP\Table1.sql

This was a good way to update database during developement and publish.

But now we are using different directory depending if we are in a dev branch or a production branch (Subversion).

We wish to avoid error, in case someone comit a script to a wrong directory by setting the path depending of the selected database name. Some thing like this:

:setvar relativepath "D:\Project1\Prod\Sql"
if (DB_name() = 'Project1' OR DB_name() = 'Project1_Dev')
begin
    :setvar relativepath "D:\Project1\Dev\Sql"
end

I can't find a way to do it. Some one have an idea?

[Update] Solution : (Tanks to wBob)

I have a script named : setRelativePath.sql

:out D:\Project1\SQL\Temp\temp.sql
go

declare @bdName varchar(15) = DB_name()
--Here the print got saved to D:\Project1\SQL\Temp\temp.sql
if @bdName = 'Project1' OR @bdName = 'Project1_DEV'
    print ':setvar relativepath D:\Project1\DEV\Sql'
else if @bdName = 'Project1_PROD'
    print ':setvar relativepath D:\Project1\PROD\Sql'
else
    raiserror( 'Unknown value (%s) for sqlcmd variable relativepath.', 16, 1, @bdName )

go
:out stdout
go

!!REM Read the file to execute the setvar statement
:r D:\Project1\SQL\Temp\temp.sql
go

--Here the print is displayed then we execute the script
print 'Relative path set to $(relativepath)'
go

then i use it in my scripts files:

:r D:\Project1\SQL\setRelativePath.sql
:r $(relativepath)\SP\SomeStoredProcedure.sql

D:\Project1\SQL\ is in the trunk .

the relativepath varriable can be used inside of SomeStoredProcedure.sql to.

This script ensure we do not aply a dev script to prod and remove a step when we merge dev to prod.


texts: SSRS Report by URL with parameter-value AND retain full top menu

texts:
TL;DR: How can I link to an SSRS (2016) report by URL, with a parameter in the query-string, AND retain (show) the FULL MENU bar (the top "SSRS chrome" that has the Favorites, Browse, Settings, breadcrumb navigation, etc.).

Why: I'm phasing out an old 2008R2 instance by putting a clickable link on the old RDLs to new versions of similar reports in the 2016 instance. When the user has selected/filled-in a parameter value, I obviously want to pass that forward to the new report when possible. I've followed the dox and built the URL like so:

http://ssrsInstance/ReportServer/Pages/ReportViewer.aspx?%2fFolder+Name%2fOrder+Line+Details&rs%3aCommand=Render&OrderNo=123456

or, the non-encoded more readable version:

http://ssrsInstance/ReportServer/Pages/ReportViewer.aspx?/Folder Name/Order Line Details&rs:Command=Render&OrderNo=123456

This works insofar as it shows the report with the parameter values filled-in, but it does NOT show the top level SSRS menu section, i.e. the branding (logo/title), the Settings/Download/Help buttons in the upper-right, and the Favorites & Browse links in the upper left. I WANT this stuff to show up, because it conveys to them that they're in the new reporting system and they can navigate around and find other reports.

What else I've tried: just using the 'standard' URL of the report (as in browsing to it from the menu in the new instance), and just appending the parameter to the end, like so:

http://ssrsInstance/reports/report/Folder%20Name/Order%20Line%20Details&OrderNo=123456

or, for the non-encoded version:

http://ssrsInstance/reports/report/Folder Name/Order Line Details&OrderNo=123456

That gives an "invalid item path" error:

The path of the item '/Folder Name/Order Line Details&OrderNo=123456' is not valid. The full path must be less than 260 characters long; other restrictions apply. If the report server is in native mode, the path must start with slash. (rsInvalidItemPath)

And obviously the other (less favorable) option is to simply use that URL without the parameter, but that forces the user to have to re-do work (especially in the case of more complex parameter sets and multi-valued params), which would suck.

Hope there is a way! Thanks all. :)


texts: List all tables that a specific login has permissions

texts:
This might be repetitive, but I haven't found a solution that serves my purpose 100%. What I'd like to achieve is to have a script (or SP) that'll list all tables that a user has permissions on. Any insight will be helpful.
PS: I'm not very good with TSQL. :(

Thanks a lot.


texts: Updating column having index causing Deadlock

texts:
My update statement:

Update XyzTable set state ='second' where pgmNumber = 'prog123' and state = 'first'

Both state and pgmNumber columns has individual index.

Deadlock is happening even if above query running concurrently  with different pgmNumber.

There is single row for each pgmNumber

What would be the best way to resolve the issue


texts: MySQL: How to sync on demand beetwen two database in the same server

texts:
I'll like to know if it's possible to have a systeme on synchronisation beetween two database in the same server.
I have a production database in my server , i want a dev database (same database with another name)
And i want to sync on demand.
Is it possible ?


texts: Can I recover CDC tables from transaction logs?

texts:
Is there anyway to restore CDC tables from transaction logs? My basic understanding is that CDC reads these logs, and entries that are specially marked, are grabbed by CDC. Once this has happened though, this data is no longer flagged for harvesting by CDC.

I need this to happen because a DB was restored without any KEEP_CDC options.

MS SMS, 2008 R2, sql server 10.5.16


texts: Can I update an Access table with an Oracle table?

texts:
I have an Access database with a table with a unique key and price field among other fields.  I have been told I have access to an Oracle database with those prices in it and the linking Key.

Can I run an update query in Access to update those price fields with values in the Oracle table or do I need to import the Oracle table into Access first and then run the update query from that access table?

I'm pretty well up on Access but have never had to link it with another SQL database system.


texts: Problem Insert Data to connected tables postgresql

texts:
I have 2 existing tables: ‘projects’ and ‘units’.The two tables are connected through the project id. In the table 'projects' the column is called ‘id’ and generates every time a new project is inserted. In the table 'units' the same id is called ‘projekt_id’.
I now want to add new projects from a third table that is called ‘temp_units’. That table also has information that is relevant for the table ‘units’. So for every row in the ‘temp_units’ table I want to create a new row in the ‘projects’ table and also a new row in the ‘units’ table. Those two rows should be connected through the projects id.
I tried the following query but I get the error: more than one row returned by a subquery used as an expression.
Then I read somewhere about adding Limit = 1, so I added that to the query below (it is shown as a comment there). With Limit = 1 the code worked but all of the inserted data in the ‘units’ table is connected to the first project created in the 'projects' table.
At the moment it is like this:
projects
id
--
1
2
3

Units
Id | projekt_id
---------------
 1 | 1
 2 | 1
 3 | 1

But I need it like this:
projects
id
--
1
2
3

Units
Id | projekt_id
---------------
 1 | 1
 2 | 2
 3 | 3

I am using PostgreSQL 9.5.19. I would really appreciate any help
WITH insert_projekte AS
    (
    INSERT INTO projects (name, projekt_status_id, creator_id, editor_id, inserted_at, updated_at)
    SELECT name, projekt_status_id, creator_id, editor_id, inserted_at, updated_at
    FROM temp_units
    RETURNING ID
    )

INSERT INTO units (kgnr, ez, projekt_id, creator_id, editor_id, inserted_at, updated_at)
SELECT t.kg_ez, t.ez, (SELECT id from insert_projekte/*LIMIT = 1*/), t.creator_id, t.editor_id, t.inserted_at, t.updated_at
FROM temp_units t
RETURNING ID


texts: Percona cluster + Wordpress blogs, low performance

texts:
We currently run ~ 900 WordPress blogs on several machines so was decided to move to load balanced environment with clustered db.

I followed instructions from http://www.percona.com/doc/percona-xtradb-cluster/5.5/howtos/cenots_howto.html, installed on 3 nodes Percona's XtraDB and imported DB dump, total DB's size is around 40 GB.

Now if there are no inserts in DB queries work fast and all is ok but if we start to do some inserts (4 - 10 inserts per second) on nodes all queries begin to slow down very dramatically reads and writes.

Hardware :

Intel(R) Core(TM) i7 CPU 950 3.07GHz with 26 GB RAM

Xeon E5-2670 0 @ 2.60GHz  with 64 GB RAM

my.conf

[mysqld]
datadir=/var/lib/mysql
user=mysql
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.0.93,192.168.0.94,192.168.0.95
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

# Node #2 address
wsrep_node_address=192.168.0.94

# Cluster name
wsrep_cluster_name=my_centos_cluster

# SST method
wsrep_sst_method=xtrabackup

#Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"

innodb_log_file_size = 50331648

# Bellow begins different testing values

wait_timeout=20
interactive_timeout=30

key_buffer_size=256M
query_cache_limit=256M
sort_buffer_size=128M
read_rnd_buffer_size=128M

tmp_table_size=1G
max_heap_table_size=1G

innodb_file_per_table=1

innodb_data_file_path = ibdata1:512M:autoextend

innodb_fast_shutdown = 0

innodb_buffer_pool_size        = 6144M
innodb_buffer_pool_instances   = 4
innodb_read_io_threads         = 64
innodb_write_io_threads        = 64

# CACHES AND LIMITS #
max_connections                = 200
thread_cache_size              = 50
open_files_limit               = 65535
table_definition_cache         = 1024
table_open_cache               = 2048

innodb_flush_log_at_trx_commit = 2
innodb_flush_method=O_DSYNC
#autocommit=OFF

sync_binlog=0

innodb_thread_concurrency = 0
innodb_io_capacity = 5000
bulk_insert_buffer_size = 256M

innodb_locks_unsafe_for_binlog=1
innodb_stats_on_metadata=0

What can be problem ?
Machines run in same LAN with ~ 0.1 ms latency

and cluster is deployed under OpenVZ virtualisation


texts:
I'm looking at AlwaysOn availability groups. The more I look at it the more it appears that the availability listener group is a single point of failure. Where exactly does the listener actually run? A separate a server, the primary SQL server, all of them?

Say I have a whole application stack at my second data center. How do I configure the listener(s) such that they will be running at both sites and that the applications will point to their own local copies?

I'm sure I'm missing something here but I don't know what.


texts: Horrible MySQL database performance after upgrading from 5.5 to 5.6

texts:
I have run into a performance problem that I don't know how to solve with MySQL after Amazon forced us to upgrade our 5.5 MySQL instance to 5.6.
We have a stored procedure which executed in about 10 seconds on version 5.5, but on version 5.6 it takes 5 minutes to complete.  I have narrowed the problem down to some very basic and simple SQL which is executing inside of a loop.  It seems that dropping and re-creating a temporary table in memory takes MUCH longer now and so does simple insert statements.  Here is the basic flow of the procedure:
loop
  drop temporary table if exists
  create temporary table (engine = memory)
  insert into temporary table values (...)...
  insert into temporary table values (...)...
  insert into temporary table values (...)...
  insert into temporary table values (...)...
end loop

The insert statements are very simple - there is no subquery involved.  It's just a simple insert statement.  If I comment out the insert statements and only execute the drop / create statements and nothing else, performance is still horrible (although improved).  I tried creating a regular table and removing the drop / create and executing only the insert statements, and again it was still horrible performance (but improved).
Because the statements executing are so incredibly simple, I don't think it's possible to fix this issue without a significant rewrite of the application (so no looping occurs).
I am hopeful that someone can point me to a configuration setting that I can change which will improve performance.  In my research, I have already found recommendations to modify various settings that have not helped.  The settings I have already checked are:

innodb_stats_persistent
innodb_file_per_table
innodb_buffer_pool_size


texts: Scheduled copy of large table from SQL Server 2000 to 2008

texts:
I have a table on SQL Server 2000 with 10 million records and 20 columns. The table is rebuilt once a day and is essentially a reporting table, not a transactional table. Of the 10 million records, some, but not all, will change on a daily basis.

I have the option to use SQL Server 2008, but only for this table at the time being. The reason for bringing this table to the SQL Server 2008 instance is to have this table leverage the Full Text Search capabilities 2008 offers.

Given this situation, what is the best way to go about dumping this table across servers on a routine, daily basis?

(I suppose I run the risk of this question being closed as a duplicate of How to move data from big table,
but the scenario I am describing seems sufficiently unique from that question.)


texts: SQL Server EXECUTE AS impersonation as RBAC method

texts:
I wrote a web application that is using AD authentication (Windows) and has it's own authorization module (like RBAC). Backend is SQL Server. A DBA in my team is not happy with us using a service account to talk to the database, he would rather have the actual user impersonated using EXECUTE AS at the database level. He would then use actual SQL roles mapped to lock down the access per user.
To me it sounds like a bad idea as I have to manage 100s of users at the SQL Server level and it just makes the attack surface bigger. What other arguments for or against this setup exist?


texts:
I try Get few ip postgresql slave from pg_stat_replication using non-superuser

CREATE OR replace FUNCTION client_addr_pg_stat_replication3
  () returns setof RECORD
AS
  $x$
  DECLARE
    rec RECORD;
  BEGIN
    SELECT client_addr
    FROM   pg_stat_replication
    INTO   rec;

  END;
  $x$ LANGUAGE plpgsql security definer;

GRANT EXECUTE ON FUNCTION client_addr_pg_stat_replication3(SETOF record) TO non-superuser;

select * from client_addr_pg_stat_replication3() as x(a int, b int);
ERROR:  function client_addr_pg_stat_replication3() does not exist
LINE 1: select * from client_addr_pg_stat_replication3() as x(a int,...
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Second function

CREATE OR replace FUNCTION client_addr_pg_stat_replication4
  () returns TABLE (client_addr cidr)
AS
  $func$
BEGIN
  RETURN query
  SELECT client_addr
  FROM   pg_stat_replication;

END $func$ LANGUAGE plpgsql security definer;

GRANT EXECUTE ON FUNCTION client_addr_pg_stat_replication4() TO non-superuser;

select * from client_addr_pg_stat_replication4();
ERROR:  column reference "client_addr" is ambiguous
LINE 1: SELECT client_addr FROM pg_stat_replication
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  SELECT client_addr FROM pg_stat_replication
CONTEXT:  PL/pgSQL function client_addr_pg_stat_replication4() line 1 at RETURN QUERY


texts: Is it imperative that I backup my index files?

texts: I have a few different instances (SQL 2000, 2005) with indexes placed in a separate filegroup. Do i need to ensure that this group is backed up, or will it be rebuilt by a restore of of the database?


texts: Is there any way to limit using RAM for mongodb on Debian? I'm looking for a solution fo about 8 hours, but have no results.


texts: How to backup Oracle Production Database without stopping service?

texts:
I have a Oracle 18XE database

I would like to create a backup for the database.

I did :

expdp sysdba full=y dumpfile=expdat.dmp directory=DatabaseDumps transportable=always logfile=export.log

But this command asks for the tablespaces to be in readonly. Wich I can not do in a production database running 24h/7.

Is there any other way to backup a database whitout stopping it or making it readonly ?

Thanks.
Regards,


texts: SQL Server 2016 SQL Agent Token Not Working

texts:
I have this in scores of SQL Agent Job steps throughout my enterprise and it works as expected:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE MyStoredProc etc..."

But on my new SQL Server 2016 instance, it simply produces a Named Pipes connection error (which is a complete red herring).

This, on the other hand, works just fine on my new server:

sqlcmd -E -S MyExplicitServerName -d master -Q "EXECUTE MyStoredProc etc..."

Why does the SRVR token not work?
If I fire up sqlcmd in a command prompt and tell it to print $(ESCAPE_SQUOTE(SRVR)) it says:

'SRVR' scripting vaiable not defined.

This is a pretty basic, no-frills install with just the default instance.


texts: Scripts to disable constraints and indexes for data warehouse load

texts:
I load data into a data warehouse.

Presently I have a script that I use to drop foreign key constraints and indexes prior to loading the data, for convenience and speed. There is a big window in which I can do the load so I don't need to worry about users accessing the data during the load, but I don't want to impact unrelated data in other tables in the database.

I have done some research here and elsewhere to come up with this script but I wonder if there are some things that I might be overlooking which could either make performance sub-optimal or if I might be missing something important (I don't know ... calculated columns or something?) or maybe I'm doing things in the wrong order etc.

Any advice appreciated to make this robust and performant.

Disable Constraints and Indexes

Edit: I removed the WHILE loop which commenters helped me realise was redundant.

Declare @schema varchar(128) = 'dbo';
Declare @sql nvarchar(max) = N'';

-- 1. Indices
-- Select a list of indexes in the schema and generate statements to disable them.
Select @sql = @sql + 'ALTER INDEX ' + QuoteName(idx.name) + ' ON ' + QuoteName(@schema) + '.' + QuoteName(obj.name) + ' DISABLE;' + CHAR(13)
  From sys.indexes As idx
  Join sys.objects As obj On idx.object_id = obj.object_id
  Where ((obj.type = 'U' And idx.type in (2,6)) -- Non-clustered index/columnstore on a table
    Or obj.type = 'V') -- All indexes on indexed views
    And obj.schema_id = (Select schema_id From sys.schemas Where name = @schema)
  Order By obj.name, idx.name;

Execute sp_executesql @sql;

-- 2. Foreign-key constraints
-- Build a list of foreign keys constraints in the schema and generate statements to disable the constraint checking.
Select @sql = @sql + 'ALTER TABLE ' + QuoteName(@schema) + '.' + QuoteName(obj.name) + ' NOCHECK CONSTRAINT ' + QuoteName(fk.name) + ';' + CHAR(13)
  From sys.foreign_keys As fk
  Join sys.objects As obj On fk.parent_object_id = obj.object_id
  Where obj.schema_id = (Select schema_id From sys.schemas Where name = @schema);

Execute sp_executesql @sql;

Enable constraints, rebuild indexes and update statistics

Declare @schema nvarchar(128) = 'dbo';
Declare @sql nvarchar(max) = N'';

-- 1. Indices
-- Build a list of tables in the schema and generate statements to enable the indices on them.
Select @sql = @sql + 'ALTER INDEX ' + QuoteName(idx.name) + ' ON ' + QuoteName(@schema) + '.' + QuoteName(obj.name) + ' REBUILD' + iif(idx.type = 6, ' WITH (MAXDOP = 1);', ' WITH (FILLFACTOR = 100);') + CHAR(13)
  From sys.indexes idx
  Join sys.objects obj ON obj.object_id = idx.object_id
  Where ((obj.type = 'U' And idx.type in (2,6)) -- Non-clustered index on a table
    Or obj.type = 'V') -- All indexes on indexed views
    And obj.schema_id = (Select schema_id From sys.schemas Where name = @schema)
    And idx.is_disabled = 1 -- Don't rebuild indexes that are already online
    And idx.is_hypothetical = 0 -- Don't rebuild hypothetical indexes!
  Order By iif(idx.type = 6, 1, 2), obj.name, idx.name;

Execute sp_executesql @sql;

-- 2. Foreign-key constraints
-- Build a list of foreign keys constraints in the schema and generate statements to enable them with checking.
Select @sql = @sql + 'ALTER TABLE ' + QuoteName(@schema) + '.' + QuoteName(obj.name) + ' WITH CHECK CHECK CONSTRAINT ' + QuoteName(fk.name) + ';' + CHAR(13)
  From sys.foreign_keys fk
  Join sys.objects obj ON obj.object_id = fk.parent_object_id
  Where obj.schema_id = (Select schema_id From sys.schemas Where name = @schema)
  Order By obj.name, fk.name;

Execute sp_executesql @sql;

-- 3. Statistics
-- Build a list of tables in the schema and generate statements to update the statistics on them.
Select @sql = @sql + 'UPDATE STATISTICS ' + QuoteName(@schema) + '.' + QuoteName(obj.name) + ' WITH COLUMNS;' + CHAR(13)
  From sys.objects obj
  Where obj.type = 'U' -- User defined
    AND obj.schema_id = (Select schema_id From sys.schemas Where name = @schema)
  Order By obj.name;

Execute sp_executesql @sql;


texts: MySQL / MariaDB - Start multi-source replication with mariabackup (xtrabackup)

texts:
Many times I successfully performed procedure described in the https://mariadb.com/kb/en/setting-up-a-replication-slave-with-mariabackup/ - it's fastest and easiest because replica is restored by copying files and binlog coordinates for CHANGE MASTER are saved in xtrabackup_binlog_info file.
With multi-source replication it seems to be not possible, because both backups from masters contain their own ibdata1 file - so I cannot restore them into single instance of MySQL (the future slave).
The only way I can think of is like that:

restore 1st master into slave
enable replication for selected databases from 1st master
restore 2nd master into second slave (temporary)
use mysqldump to move selected databases from temporary slave into main slave

The problem is that databases on both masters are large (~1 TB) and mysqldump/restore takes ages - and chasing few days of stalled replication (between backup and restore) takes additional significant amount of time. I'd really like to avoid this.
I know that with MyISAM tables I could just move table files but it's innodb and it cannot be changed.
Maybe there's some way to merge ibdata1 files? Or maybe export/import ibdata1 dictionaries related to chosen databases?


texts: Query optimization when multiplying by zero

texts:
I'm on SQL Server and I have a query like this:

select * from MyTable where a * expensive_function() > 0

I noticed the server always runs expensive_function(), even for rows where a = 0 (and also if I replace a with a literal 0).

Is there a clear reason for that?

Do other servers optimize queries like this?

Note: This query is trivial, and could be changed to avoid running the function. My question isn't about optimization of this query, but to know if/why/which servers would optimize (or not) queries like this.


texts: How to measure a perfomance of SQL Server database?

texts:
I have the task to improve the performance of a SQL Server 2012 database (one of 4 in an instance) by 60% confirmed by corresponding statistics.

So, I need to measure "performance" of the RDBMS database before performance tuning and optimization and after.

Which metrics are better suited for this?

Trying to answer the obvious questions ahead ...

I/O (hardware) bottlenecks are absent since the SQL Server runs on a virtual rack having plenty of physical resources under it.

The database is used by approx. 60 users (mostly 8 hours a day) with widely varying load (per sec).

This is a company management task, so the results of this task should be easy to grasp.

UPDATE:
the corresponding my question was answered in StackOverflow.com by @Martin von Wittich and, then, deleted by moderator/community:

  Run different kinds of queries (INSERT, UPDATE, DELETE) in a loop
  (e.g. 1000 times), then divide the runtime by the amount of loop
  iterations. Now you now how long a single query of that type takes.

  Then do whatever you want to do to improve the performance, and
  compare the results.

Quite good for me. Also I do not see why it was deleted in SO with the reason: "This question does not appear to be about programming" and closed in this site with: "There are either too many possible answers, or good answers would be too long for this format."


texts: How to allow connection for edb database in Postgres

texts:
By mistake we have executed disallow connections for all databases after that we are not able to connect any of the database, see below for reference:
Executed Query:

edb=# update pg_database set datallowconn=false;

[root@keng02-dev01-ins01-udm12-dbs-1510678526-01 ~]# psql -U tkcsowner
Password for user tkcsowner:
psql.bin: FATAL:  database "edb" is not currently accepting connections

Is there a way we can allow connections to edb or enterpriseDB without using PSQL? Please advise.


texts: Can I replicate a table in PostgreSQL to an unlogged table in another database?

texts:
The documentation is not clear about this specific logical replication scenario. It does mention unlogged tables on the publisher's side:

  Only persistent base tables can be part of a publication. Temporary
  tables, unlogged tables, foreign tables, materialized views, regular
  views, and partitioned tables cannot be part of a publication. To
  replicate a partitioned table, add the individual partitions to the
  publication.

In my scenario, the table in the publisher is normal, while the table in the subscriber is unlogged.

Can I do this?


texts: SQL AlwaysOn Dashboard Not Showing Green Circles

texts:
I have a SQL server 2017 with SSMS 18.1 and SAO dashboard shows gray circles instead of the green check marks. It does not matter if it is secondary or primary it always shows grey circles. The server is healthy and I can failover, this is more like a UI annoyance.

Has anyone experienced this and know how to fix it?


texts: selecting table1 and table2 with single data row and table 3 with multiple data rows

texts:
I want to retrieve data from table1(payments), table2(properties), and table3 (property.assessments). See my visual representation below:

tables screenshot

Snippets:

SELECT a.payment_id, a.payment_arp_number, a.payment_pin, b.property_id, b.property_arp_number,
    b.property_location, c.property_id, group_concat(c.pa_classification separator ', ')
    as pa_classification, sum(c.pa_market_value) as pa_market_value
FROM `property.payments` a, properties b, `property.assessments` c
WHERE a.payment_arp_number = b.property_arp_number
AND b.property_id = c.property_id

The problem with this is it only fetch one row, if not it will fetch all but the classification and market_value are just the same with other rows. I want something like the expected result.

Expected result:
property.assessments can have multiple row of classification and market value with the same property_id.

result screenshots


texts: Encrypt application name in c# and decrypt in SQL Server

texts:
I've encrypted application name in my c# app using MD5, so when constructing the connection string the application name is passed to SQL as encrypted. I would like to decrypt in SQL server the value to see if there is a match?
How can I get the original text in SQL ?


texts: Online text based game design - Am I linking the databases correctly?

texts:
I'm trying to recreate an old game that I used to play that went offline quite some time ago. I'm doing this as a hobby project for myself with the hope that maybe a few hundred people might enjoy it once it's finished. It's obviously a long way off as I'm teaching myself as I go but figured I'd start with the database design.

It's a simple (to play) game. You create an empire with a particular race, you create buildings which influence your population, resources, etc and military. The game updates once an hour and depending on what you've built, you start to progress or gain networth. - I hope this is a brief overview to understand the type of game I'm attempting to make? There's no graphical interface, everythings done in text using tables, etc..

Anyways, so I started to use MySQL Workbench to try and map out the databases and now I've realised how much it's going to require in the background. I've got a screenshot of what I've laid out (which I'll post below) and I'd like to know if, in it's current and basic form, it will work. Of course, there's ALOT more to go into this but I want to know that I'm on the right track before progressing.

Thank you for the help.

Here's the screenshot!

& This is what I'm working with: Web Archive of "races" guide for reference.


texts:
I'm working in a company that currently develops Media Asset Management system and now we want to improve our system for document files. I have to search nearly thousand .doc and .pdf files and index them.

I want to use SQL Server's filestream and full-text search capabilities for this job but my colleagues warn me about accessibility and database size. When you use SQL Server, it saves document files in its share and change the file name so database file size is getting bigger and bigger.

They offered me to use Windows Search Service. In this way, indexing and storing is not my job so my database will be clean and accessing files are relatively easy.

I'm confused. Which way do I prefer or is there any other way?


texts: Availability Group database stuck in Not Synchronizing / Recovery Pending mode

texts:
While upgrading the storage in a SQL Server 2014 SP1 (12.0.4422.0) instance we ran in to an issue where two of the databases would not start on the secondary after restarting SQL Server. The server had been offline for a few hours while we installed new (larger) SSDs and copied the data files over to the new volume. When we restarted SQL Server all but two of the databases started synchronizing again. The other two were displayed in SSMS as Not Synchronizing / Recovery Pending.

Having had a similar Not Synchronizing / In Recovery issue before, I checked the status under the Availability Groups -> Availability Databases section but they displayed a red X:

and even trying to Suspend Data Movement generated an error message:

  Failed to suspend data movement in database 'StackExchange.Bycycles.Meta', which resides on the availability replica 'ny-sql03' in the availability group 'SENetwork_AG'. (Microsoft.SqlServer.Smo)

  Additional Information:
  An exception occurred while executing a transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

  Database 'StackExchange.Bycycles.Meta' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details. (Microsoft Sql Server, Error: 945)

I checked and the files existed and did not have any permission issues. I also checked the SQL Server logs in SSMS under Management, but did not see anything about pending recovery or any issues with the two databases.

Searching for help I found two different articles that said the databases would need to be restored.

Is there any way to resume data replication on a secondary when a database is stuck in Recovery Pending?


texts: Generate data delta between two databases PostgreSQL

texts:
I have a big database with a lot of data ~50GB that spans around 40 tables - running PostgreSQL 9.6.  This database is copied and sits in number of client sites without an Internet connection.  Changes that are made to this database should be delivered to the client sites.

I'm looking for a way to generate a delta script between the current version of the database to the previous and execute this script in each client's site (open source/ freeware solution - RedGate is not an option).

The main assumption is that the schema is the same - so the delta is only in data (insert / update / remove rows)

I have checked a number of proposed tools (here for example) and the one that worked nice was SQLWorkbench-J using its WbDataDiff command. But its very slow - even if the databases resides on the same machine. I assume that its because the delta is calculated externally to support different databases sources.

Does anyone have a suggested way to do that? Is there a way to make it run internally on the PostgreSQL instance to increase the performance (I understand that joins cannot be calculated using different databases according to here - is using the "foreign data wrapper" may help in this situation)?


texts: Database design, 1-to-many or many-to-many?

texts:
Just a little background information pertaining to the question at hand:
I'm developing an application for users to be able to log the work they have been doing hourly, to be compiled into a weekly log. They should log 8 consecutive hours a day.
Only these users can log their work, as they are in a specific table of their own.

My question is how to handle these weekly logs, as a 1-to-many relation or a many-to-many relation? That is, since there will be many users and thus many logs, create an intermediate relation linking both of these tables, or look at it from a more general point of view and think one user can have many reports, so as to label it a one-to-many relation.

In short, would it be a good idea to see this as follows: there is a relation stating the hour and description of the work done or task performed, another relation which would be the actual report (which has user field and an hour field, both foreign keys so this would be an intermediate relation), and then the user relation with all of its pertinent information.

I guess my doubt is more conceptual than anything, but I'm interested in learning this so I can make an optimal design.


texts: PL/pgSQL functions and optimizations fences?

texts:
I am newbie on DB stuff. And my DB knowledge is very old (around MSSQL2000). All what I remember is, the stored procedures are faster than ordinary queries because it avoid query compilation and optimization.

I know PG has two types of query caching options. Prepared statement (temporary cached query) and function (persistent cached query, which is stored procedure in MSSQL).

Anyway, today I heard the concept of optimization fence. Which is the optimizer sees functions as opaque black box, so cannot optimize the inside. Conceptually it could be right.

Is this true even if the functions are fully written only with PL/pgSQL? Or just a limitation only to non-SQL languages? Do I have any chance to get better performance by using PL/pgSQL?


texts: Can I reinstall Wamp on Windows 10 without overwriting my databases?

texts:
I have another open question which at the moment seems to be a bit tricky to answer:

phpMyAdmin - Cannot login even when changed password

I am hoping someone can still answer it. I think one of the config files or ini files is wrong in some way.

So I need to ask this question as a back up.

I have a few databases in my present install of Wamp server on Windows 10. Can I reinstall, or do a repair-install, on this Wamp server without overwriting or deleting my databases?


texts: Implications of removing unnecessary column from primary key

texts:
I have a database in which a number of tables are defined with multiple UUID columns forming the Primary Key when they should instead be defined as Foreign Keys, e.g.
CREATE TABLE [dbo].[SomeTable](
    [SomeTableID] [uniqueidentifier] NOT NULL,
    [WorkEntryID] [uniqueidentifier] NOT NULL,
    [TimeEntryID] [uniqueidentifier] NOT NULL,
    [RateEntryID] [uniqueidentifier] NOT NULL,
    ...

 CONSTRAINT [PK_SomeTable] PRIMARY KEY CLUSTERED
 (
    [SomeTableID] ASC,
    [WorkEntryID] ASC,
    [TimeEntryID] ASC,
    [RateEntryID] ASC
 )
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SomeTable] ADD  CONSTRAINT [DF_SomeTable_SomeTableID]  DEFAULT (newsequentialid()) FOR [SomeTableID]
GO

The additional columns should have been defined as Foreign Keys and in some cases have been. I'm assuming there are performance implications of having the additional columns as part of the PK? Are there any considerations I should be aware of before redefining the PK for the tables to include only the PK column?


texts: How to extract fields of anonymous row type in PostgreSQL

texts:
In PostgreSQL I can create an anonymous row or composite type
postgres=# SELECT ROW(1,'Hello',false) as r;
      r
-------------
 (1,Hello,f)
(1 row)

But how can I get the fields out again?  None of the following work
postgres=# SELECT (r).* from (SELECT ROW(1,'Hello',false) as r) as T;
ERROR:  record type has not been registered

postgres=# SELECT (r).column0 from (SELECT ROW(1,'Hello',false) as r) as T;
ERROR:  record type has not been registered


texts: Using PL/PerlU with a non system perl lib

texts:
psql -1f check_email.pl.sql ccushing postgres
SET
psql:check_email.pl.sql:7: ERROR:  Can't locate Email/Valid.pm in @INC (@INC contains: /usr/lib/perl5/site_perl /usr/share/perl5/site_perl /usr/lib/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib/perl5/core_perl /usr/share/perl5/core_perl .) at line 3.
BEGIN failed--compilation aborted at line 3.
CONTEXT:  compilation of PL/Perl function "check_email"

My current @INC is

@INC:
  /home/ccushing/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2/x86_64-linux
  /home/ccushing/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2
  /home/ccushing/perl5/perlbrew/perls/perl-5.14.2/lib/5.14.2/x86_64-linux
  /home/ccushing/perl5/perlbrew/perls/perl-5.14.2/lib/5.14.2

obviously my problem is that Email::Valid is not in my @INC, can I globally change Postgres @INC Paths? also related is postgres only capable of using the Perl it's built against? or can I make it use my perlbrew.


texts: Postresql, max returns all rows with same ids

texts:
System

PostgreSQL 11.5 (Ubuntu 11.5-1.pgdg19.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 8.3.0-6ubuntu1) 8.3.0, 64-bit

Issue

Need return one max value from table for every product_id (1 product_id has 10 store_ids) based on available_count.

Sql

select
    product_id, store_id, max(available_count)
from
    j_product_store_availability
group by
    product_id,
    store_id,
    available_count
order by
    product_id,
    available_count desc,
    store_id
--limit 1

Result

product_id store_id available_count

130475  4   7
130475  1   4
130475  8   4
130475  2   3
130475  5   3
130475  7   3
130475  10  3
130475  3   2
130475  6   2
130475  9   0
130479  1   0
130479  2   0
130479  3   0
...

It returns rows for every store_id. Need to return just one max value based on available_count.

Expected result

130475  4   7
130479  1   0
(next product_id)
...

Thanks.

Solution (Laurenz solution)

SELECT DISTINCT ON (product_id)
product_id,
store_id,
max(available_count)
from
j_product_store_availability
--where store_id = '7'
group by
product_id,
store_id,
available_count
ORDER by
product_id,
available_count desc;


texts: Table Partition Function Empty Partitions

texts:
I plan to partition my table monthly along a date column, and have read at few places, that it is recommended (in fact a best practice) to keep empty partitions at both "end" of the range.
https://techcommunity.microsoft.com/t5/premier-field-engineering/oops-i-forgot-to-leave-an-empty-sql-table-partition-how-can-i/ba-p/370563

As a naive approach I would create my partition function like this:

CREATE PARTITION FUNCTION MyPf(DATE)
AS RANGE RIGHT FOR VALUES (
'1900-01-01',
'2019-10-01','2019-11-01',...,...,...,'2022-08-01',
'9999-12-31');

CREATE PARTITION SCHEME MyPs AS PARTITION MyPf
ALL TO (MySingleFileGroup)

I can guarantee that no data older than 2019-10-01 will be inserted to the table, and I plan to keep SPLIT partitions once I reached 2022-08-01, and keep doing so up until 9999-12-31.
I also plan to regularly TRUNCATE old partitions and MERGE the old partitions range.

Did I miss anything obvious regarding the best practice with this setup? My only goal is to be able to split and merge without moving data around.

Thank you!


texts:
I'm struggling here. This is my code

CREATE DEFINER=`****`@*****` PROCEDURE `setCountry`(
    IN `countryname` VARCHAR(100) CHARSET utf8mb4,
    IN `nationality` VARCHAR(100) CHARSET utf8mb4,
    IN `countryPrefix` INT(3)
)
    MODIFIES SQL DATA
INSERT INTO country(
    countryName,
    nationality,
    countryPrefix
)
SELECT
    @countryname
FROM
    (
    SELECT
        @countryname AS countryName,
        @nationality AS nationality,
        @countryprefix AS countryPrefix
) AS tmp
WHERE NOT EXISTS
    (
    SELECT
        countryName
    FROM
        country
    WHERE
        countryName = @countryname
)
LIMIT 1

And i'm trying to execute code with this sentence

call ccc.setCountry('Togo', 'Togolese', 228);

The ERROR it displays is this :

    Error Code: 1136. Column count doesn't match value count at row 1


texts:
wanted to ask if my design is on the right track for installing SSRS.
Currently i have 2 nodes in an Always ON AG (2017 Enterprise Edition).
There is an application update coming up that will require the use of SSRS.  No users will access the SSRS portals and the application will display the reports.
If the SSRS server is not available the application will work unless you go to the section that shows the reports, but everything else will work.
My question is, should I stand up SSRS on the two SQL Nodes in a scale out deployment and have them sitting behind my Net Scaler load balancer?
Should I just setup a 3rd box and get SSRS installed on a separate box to make the config simple like a 3rd node (Sync or Async) with manual failover to host the ssrs server and its databases?
Just trying to see what is the best angle to go here to give me the lease amount of headaches in the future.
I think the scale out deployment is a good way to go due to not having yet another server to manage, but i then need to depend on the net scaler admin to make sure they are doing their jobs in the setup, but i would like to get some feedback from you guys.
Thanks,


texts: Is there any way to find the exact Query responsible for replication lag?

texts:
I've tried checking slowquery.log and binlog.

Slow query log kind of helps but not all the time.

Binlog entries are inaccurate. I used the following command to check binlog.

show binlog events in <binlog name> from <log position> limit <n>;


texts: lock and get next n records from table in oracle

texts:
I have a oracle table PHONE_NUMBER which has 2 columns PHONE and STATUS
PHONE_NUMBER
PHONE STATUS
1     U
2     O
3     U

U stands for used , O is open/UnUSED .
in my input I will get n as input number , which signifies the number of phones I want to reserve(update status to U from O )
I want a query that will lock(something like SELECT FOR UPDATE) the any n available row (rows that are not locked yet and having status as O )
Constraints: Table has millions of Used and unsed phones
comments:
problem is i Want n (input) rows to be locked with a particular status . i.e i have to work with rownum
I will be getting multiple concurrent calls in my service with sqli_n as input number of records to select :
so
SELECT * FROM PHONE_NUMBER WHERE STATUS='O' and rownum<=:sqli_n FOR UPDATE ;

this query wont work for me it will select only first n rows and if the first n rows are locked in 1 call the next/concurrent call will be stuck /wait until the first call updates the status to Used .
for update no wait will fail the above query
for update skip lock will also not work with row num


texts: Drop partition and rebuild global indexes "online"

texts:
I am using Oracle 11g.

I have a requirement to drop a partition and rebuild global indexes. The query below does job well but BLOCKS all DML operations on the table until the indexes are rebuilt.

ALTER TABLE ABC DROP PARTITION PART1 UPDATE GLOBAL INDEXES;

But If I break the query into 2 parts and rebuilt indexes with ONLINE option separately, DML queries DOES NOT get blocked while indexes are being rebuilt

ALTER TABLE ABC DROP PARTITION PART1;
ALTER INDEX XYZ REBUILD ONLINE;

The down side of this approach is the time between the execution of drop partition and rebuilding index, those indexes will be unusable that might create performance problems.

So my question is, is there any option where I can drop partition and rebuild index "online" in one query? Currently I dont think we have following option.

ALTER TABLE ABC DROP PARTITION PART1 UPDATE GLOBAL INDEXES ONLINE;


texts: SQL Agent Proxy - writing to logs is only allowed to jobs that are owned by sysadmin

texts:
I have a Windows 2012 RS and SQL Server 2012 Enterprise Edition. I have SQL Server maintenance jobs from ola.hallengren.com and the owner of the jobs were sa.

The security team decided to change that owner to a SQL login called SQLUSERID. It is DB owner for the databases that maintenance job runs and no server level access.

I was asked to create a SQL Agent proxy SQLJobs_Proxy and created a credential DBAAdmin_Creditional using Domain\sql_service_account which is a sysadmin and a memeber of local admin group. I created this proxy under Operating System(cmdExec) and SQLUSERID as the principal. In the job property I Type is selected Operating System(cmdExec and Run As  SQLjobProxy.

When I execute the job I get the following error:

  Executed as user: Domain\sql_service_account.
  Warning: cannot write logfile:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\Indexrebuild_83722.txt.
  Writing to log files is only allowed to jobs that are owned by sysadmin.
  Please consider writing log to table.  The step failed.

Domain\SQL_Service_Account is sysadmin and local admin so it has full access to the folders and should be able to write.

What can I do to run the jobs successfully with this SQL login that is not a domain user?


texts:
Lets assume we have a table as follows:

PK -- nonclustered primary key
Col1 -- unique clustered
Col2
Col3

Is this index:

nonclustered index on (Col2, Col3)

any different from this index:

nonclustered index on (Col2, Col3) include (PK)

What if my PK is clustered instead of nonclustered? Now does it make/not make a difference?

Edit to add:

I suppose this is a round-about way of asking: Is the nonclustered index's pointer back to the table based on the clustered index or the primary key? I assume the clustered index RID is what is used.


texts: Debugging long Oracle queries and identifying offending data for 'Expression type does not match a required type' error

texts:
I have a long running query with a large number of complex XQueries. This queries used to work but more recently it is giving me

  Expression type does not match a required type

error. I suspect new data was entered into the database with XML of a different structure.

What is the best way to narrow down the offending data?

How can I ask Oracle to give me the offending data row?


texts: How to get last truncated time for each tables in SQL Server database

texts:
I have a SQL Server 2012 database. Is it possible to get last truncated time for all tables in the database?

Note: Recovery mode is SIMPLE in my scenario. Is it possible if I change it to FULL ?


texts: Londiste replication fails intermittenly

texts:
I have skytools setup for postrgesql replication. It keeps on failing intermittently. When I checked londiste status I get following error

$ londiste /etc/skytools/mydb_data_0.ini status
Queue: mydb_data   Local node: mydb_data_0

mydb_data (root)
  |                   Tables: 18/0/0
  |                   Lag: 9s, Tick: 2620740
  +--: mydb_data_0 (leaf)
  |                   Tables: 18/0/0
  |                   Lag: 15m8s, Tick: 2620670
  |                   ERR: mydb_data_0: Lost position: batch 620669..2620669, dst has 2620670
  +--: mydb_data_1 (leaf)
                      Tables: 18/0/0
                      Lag: 9s, Tick: 2620740

I really don't understand what's going wrong. I get the same error message in postgres logs also,

Exception: Lost position: batch 2620669..2620669, dst has 2620670

I found this article to solve the error which I'm getting. It says you would have to use --reset option of worker to reset the queue position on remote site and then issue wait-sync to get the table queue moving again.

So I did this,

$ londiste /etc/skytools/mytestdb_data_0.ini worker --reset
Ignoring stale pidfile
2016-12-23 17:00:34,278 15245 INFO Resetting queue tracking on dst side

It resets the queue successfully, but when I check londiste status I get this error,

$ londiste /etc/skytools/mydb_data_0.ini status
Queue: mydb_data   Local node: mydb_data_0

mydb_data (root)
  |                   Tables: 18/0/0
  |                   Lag: 9s, Tick: 2620740
  +--: mydb_data_0 (leaf)
  |                   Tables: 18/0/0
  |                   Lag: 15m8s, Tick: 2620670
  |                   ERR: mydb_data_0: [ev_id=84594950,ev_txid=702851528] duplicate key value violates unique constraint "dmn_pkey"
  +--: mydb_data_1 (leaf)
                      Tables: 18/0/0
                      Lag: 9s, Tick: 2620740

I don't know what is causing this, can you please guide me on this.

Postgresql verion : 9.5, Skytools version : 3.2

Update

I found this skytools logs for master db,

2016-12-27 05:36:23,369 15563 ERROR Job mydb_data_0 crashed: Lost position: batch 2681655..2681655, dst has 2681656
Traceback (most recent call last):
  File "/usr/lib/python2.7/dist-packages/skytools-3.0/skytools/scripting.py", line 578, in run_func_safely
    r = func()
  File "/usr/lib/python2.7/dist-packages/skytools-3.0/pgq/cascade/consumer.py", line 199, in work
    return BaseConsumer.work(self)
  File "/usr/lib/python2.7/dist-packages/skytools-3.0/pgq/baseconsumer.py", line 257, in work
    self._launch_process_batch(db, batch_id, ev_list)
  File "/usr/lib/python2.7/dist-packages/skytools-3.0/pgq/baseconsumer.py", line 286, in _launch_process_batch
    self.process_batch(db, batch_id, list)
  File "/usr/lib/python2.7/dist-packages/skytools-3.0/pgq/cascade/consumer.py", line 168, in process_batch
    if self.is_batch_done(state, self.batch_info, dst_db):
  File "/usr/lib/python2.7/dist-packages/skytools-3.0/pgq/cascade/worker.py", line 185, in is_batch_done
    done = CascadedConsumer.is_batch_done(self, state, batch_info, dst_db)
  File "/usr/lib/python2.7/dist-packages/skytools-3.0/pgq/cascade/consumer.py", line 254, in is_batch_done
    prev_tick, cur_tick, dst_tick))
Exception: Lost position: batch 2681655..2681655, dst has 2681656
2016-12-27 05:37:11,988 18190 INFO Resetting queue tracking on dst side
2016-12-27 05:37:47,776 18578 INFO pgq.maint_operations is installed
2016-12-27 05:37:48,038 18578 INFO {count: 0, duration: 0.295}
2016-12-27 05:37:48,115 18578 ERROR Job mydb_data_0 got error on connection 'db': duplicate key value violates unique constraint "dmn_pkey"
DETAIL:  Key (id)=(31780560) already exists..   Query: update only public.percent_bleed set percent_redir_sid = '43 ...
Traceback (most recent call last):
  File "/usr/lib/python2.7/dist-packages/skytools-3.0/skytools/scripting.py", line 578, in run_func_safely
    r = func()
  File "/usr/lib/python2.7/dist-packages/skytools-3.0/pgq/cascade/consumer.py", line 199, in work
    return BaseConsumer.work(self)


texts:
So I have this application. It runs on a server. There is a portion of the code that is taking around 10-15 seconds to run, it contains some queries, logic, as well as some code to update the interface--nothing too crazy.

At this point, I've gone through the code, simplified it essentially as far as possible at the application level as well as on the backend and it still seems to be taking about 10-15sec to run. Had a go with threading etc

If I just execute the queries outside the application, the time it take to run them is seemingly trivial, with the exception of function which takes about a second.

Thoughts on what to do to optimize the db past indexes and what not? (I ran the exact same code on the application level from another application, really even less, and it seemed to take about 3 times as long (with the only difference really being the db it was executed on (same server)))?


texts: How to automatically fill in column values based on another difference column value

texts:
I have function takes two parameters and I want to make this function takes the parameters automatically from table B and add it to specific column in this table.

For example, if I have a function getSomething(val_1 , val_2); and it returns a value from table A. Table B is empty. Let's assume it contains three column (X , Y , Z)

   column X : contains val_1 values
   column Y : contains val_2 values

And I want:

  column Z : contains the value return from getSomething(col X val_1 ,col Y val_2);

And this function takes parameters from each row in column X and Y for values will be insert.

I will try to edit my question to be cleared why i want to do that

For example if I have three tables (A , B , C) :
Table A contains (col1 , col2 )user changed it dynamically that's mean may be delete all values and insert another values.
Table B contains ID for all values will be in table A.
This ID exist in table C with the another data we want to select it.
The function created to fetch the ID from table B so we can fetch the data from table C.
I know we can create a new table to insert the ID with data from table A by the select with function, then we can fetch the data, but I wonder if we can add this function directly into table A, so we will get the ID automatically without additional table.
Finally, I want to say it's too hard to explain the system in the question because it's more complex , so i tried to make it simple.

I am using Oracle SQL Developer

How can I do this?


texts: SQL Server 2008, database completely locked

texts:
Our database becomes completely locked, and it seems that many tables not related to each other are locked at once. When this happens, it becomes locked for approximately 2 minutes, and when this happens it is not related to the amount of traffic that our application has at that time.

We have tried to replicate the issue on our test servers by

increasing the activity on the database (Inserts, Selects, Updates, Deleting),
increasing the connections to the database simultaneously,
increasing latch time,
generating temporary locks on a particular table

But could not arrive to a conclusion were this is coming from.

Is there any type of configuration, or background running tasks of SQL Server, that may cause this (Backups, Transaction Log related stuff etc)?

Is it possible to trace locks, by a date range (For example : Taking a snapshot of the current locks present in the database for a particular time)?


texts: Postgres: querying jsonb array with an array

texts:
My products table has a jsonb column called 'specs'.

This specs hash will often have an array field, let's call it 'spec_options'.

When querying this table, I need to find any product that has certain spec options.

Let's say we have a product with these spec_options:

['b', 'c', 'd']

And the user wants to find products with these spec_options:

['a', 'b', 'c']

The query I need should return find above product.

This where clause worked just fine when the field wasn't an array:

'["a", "b", "c"]'::jsonb ? (specs->>'spec_options')

But now that it's an array, I think I need to use the |? operator, and I can't work out how to get the right side of this operation into the correct format. (I think it needs to be text[] but I'm not sure.)


texts: Increased disk consumption after upgrading to MySQL 8

texts:
I have a MySQL 8 DB in AWS RDS, and after upgrading from MySQL 5.7 to 8 a couple months ago, we have noticed free disk space being consumed at a very rapid rate, at least double the previous rate.

One particular table shows about 500GB of data_length + index_length when querying against INFORMATION_SCHEMA.TABLES, and yet its .ibd file is allocated to over 1TB on disk when querying INFORMATION_SCHEMA.FILES or INFORMATION_SCHEMA.INNODB_TABLESPACES (we have innodb_per_file_table=1). There is very little fragmentation, judging by the comparison of data_free against the data + index length.

How can I find the cause of the increased disk consumption rate? Is there anything inherent to MySQL 8 that uses more disk? Our application hasn't changed, but it does have rather large JSON columns -- possibly something in MySQL 8 handles JSON on disk in a less efficient way?


texts: creating server with Microsoft Server Management Studio

texts:
I am working with a django project and I want to connect it to local server. I downloaded the Microsoft Server Management Studio but got lost on how to create a server and database?

If I am not wrong the object explorer tab only lets you connect to existing server. Is that right? If not how do I create a new server and database in SMSS?

Also I have imported a .bacpac file from the azure cloud and want to duplicate that cloud database in my local machine so that I don't mess up the actual database on cloud while testing. (I followed this link https://stackoverflow.com/questions/5475306/how-do-i-copy-sql-azure-database-to-my-local-development-server)

So how do I create a server/database on local machine using SMSS and load it with the .bacpac file to backup my django site?

Would appreciate the help


texts: Stuck at Dynamic SQL execution in a procedure

texts:
I'm learning PL/SQL and using Oracle 11g as my Database and HR as my schema for practice. There's one scenario where I'm stuck and not sure why it's not working. The requirement is:

"Create a procedure to extract all employees from employees table that are not managers and put them into new table non_managers".

NOTE: It could be done my many ways and one of the ways is by using composite data structures but I haven't reached that chapter so my solution listed below is based on what I've read till now and would like solutions based on these approaches first. But if there's a better way of doing this please do let me know. Probably I'll cover those topics later on during my reading.

As solution I came up with two approaches, approach 1 doesn't work for me but approach 2 works absolutely fine. I need your help on approach 1:

Approach 1: Doesn't work

Write a procedure

The procedure will have a cursor to hold all employees that are not managers
Create a table NON_MANAGERS to hold the data
INSERT every row of the cursor into the NON_MANAGERS table.

Write an anonymous block to run the above procedure.

Approach 2: Works Absolutely Fine

Create the table to hold the result of the procedure.
Write the procedure with below requirements:

create a cursor to hold all employees that are not managers
INSERT each row of the cursor in the NON_MANAGERS table created in step 1

Write an anonymous block to run the above procedure.

Here's my code for approach 1 that's not working:

-- Step 1
CREATE OR REPLACE PROCEDURE not_manager_procedure
IS
  v_SQL VARCHAR2(3000);
  v_insert_data VARCHAR2(3000);
  CURSOR emp_cur
  IS
    SELECT *
    FROM employees
    WHERE employee_id NOT IN
      (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL
      );
BEGIN
  v_SQL := 'CREATE TABLE non_managers AS (SELECT * FROM employees WHERE 1=2)';
  EXECUTE IMMEDIATE v_SQL;
  --DBMS_OUTPUT.PUT_LINE();
  FOR emp_row IN emp_cur
  LOOP
    v_insert_data := 'INSERT INTO non_manager VALUES
      (
      '||emp_row.employee_id||',
      '''||emp_row.fiRst_name||''',
      '''||emp_row.last_name||''',
      '''||emp_row.email||''',
      '''||emp_row.phone_number||''',
      '||emp_row.hire_date||',
      '''||emp_row.job_id||''',
      '||emp_row.commission_pct||',
      '||emp_row.manager_id||',
      '||emp_row.department_id||'
      )';
    EXECUTE IMMEDIATE v_insert_data;
    DBMS_OUTPUT.PUT_LINE(v_insert_data);
  END LOOP;
  COMMIT;
END not_manager_procedure;
/

-- Step 2
BEGIN
  not_manager_procedure;
END;
/

The procedure gets complied properly and if I comment the "Execute Immediate v_insert_data;" statement and run the anonymous block in step 2, I can see 89 records in the "DBMS Output" pane in SQL Developer. But as soon as I uncomment that statement and try to run it, it gives me the below error:

Error starting at line : 40 in command -

    BEGIN
      not_manager_procedure;
    END;
    Error report -
    ORA-00936: missing expression
    ORA-06512: at "HR.NOT_MANAGER_PROCEDURE", line 31
    ORA-06512: at line 3
    00936. 00000 -  "missing expression"
    *Cause:
    *Action:

Any help would be good.


texts: Oracle 11g Express Edition - missing or invalid schema authorization identifier

texts:
I have a new fresh instance of Oracle Express 11g.
I'm trying to create a new Schema with a Table and a View in the default db (xe).

My script is:

create user XAuthority identified by mysecret;

alter session set current_schema = XAuthority;

create schema authorization XAuthority
create table CUSTOMER (  ID int, CUSTOMER text)
create view CUSTOMER_VIEW as select * from CUSTOMER
grant select on CUSTOMER_VIEW to xareadonly;

but I get an error:

  SQL Error: ORA-02421: missing or invalid schema authorization identifier
  02421. 00000 -  "missing or invalid schema authorization identifier"

  *Cause:    the schema name is missing or is incorrect in an authorization
             clause of a create schema statement.

  *Action:   If the name is present, it must be the same as the current
             schema.

I'm familiar with Postgres and MySql but it seems Oracle is quite different.  Can anyone tell me what's wrong here?


texts: MongoDB Export/Import Failed: error counting

texts:
I am trying to export data from local database using:

mongodump --db ride4you --collection rides

But I receive the next error:

Failed: error counting ride4you.rides: not authorized on ride4you to execute command { count: "rides", query: {} }

What is this and what to do?
Plus, is there a way to export the whole database at once, instead by each table?


texts:
I'm trying to create a trigger that would run twice daily and do updates based on a specific condition.

To be run on Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

We have Table A and Table B with columns

Primary Key
Date 1
Date 2
Date 3
Status

For a specified set of Primary Keys in Table A, until Table A.Status==Completed, I want the Date fields to be copied over from Table B to Table A twice daily. The same primary keys will be already setup in Table B. The number of records to update will decrease over time as more records in the primary keys list reach completed status.

The purpose is to maintain certain static values on the Date fields till status==completed. We are unable to control an external system that keeps over-writing them. As a stopgap we want to rewrite them at frequent intervals to have the notion of maintaining static values. Once all PKs in our list reach completed status, we want to turn off the trigger.


texts:
Hello,
kindly can anybody tell me what is the difference between the 2 options (Recovery State and Leave Source Database in the restoring state (WITH NORECOVERY)), - see the snapshot below please - and When I have to check the option : Leave Source Database in the restoring state (WITH NORECOVERY)?
any help please,
Thanks in advance


texts: Postgresql : How to login as a NOSUPERUSER and create a database?

texts:
After logging in as Superuser "postgres" I created another user "p5" :

testdb=# CREATE USER p5 NOSUPERUSER CREATEDB LOGIN PASSWORD '123';
CREATE ROLE
testdb=# \du

p5        | Create DB
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

To login as postgres(superuser) I used to do :

revoltman@G41:~$ psql testdb postgres
Password for user postgres:
psql (9.5.10)
Type "help" for help.
testdb=#

How to login as user "p5" and create a database "db5" ? p5 already has CREATEDB rights ! Should I create "db5" first as a Superuser and then connect "p5" to "db5" ? If so then what is the use of p5 having CREATEDB rights ?


texts: postgresql multi column unique or check constraint

texts:
First a word of pleading.  To say I am a newbie to postgresql would be an insult to the newbies!  I have been working on this for only a day or so.

I have found several questions on this site, and found references in the postgres 9.1 documentation which allude to this issue, but I am new enough to this that my understanding of the various complex examples is insufficient for me to grasp the concept.  At least I think that is the problem...

I have the following table:

CREATE TABLE cc
(
 idx integer NOT NULL DEFAULT nextval('cc_idex_seq'::regclass),
 card_num character(16), -- credit card number
 sec character(4), -- credit card security number from reverse
 card_holder_fname character(20), -- first name of card holder
 card_holder_lname character(20), -- last name of card holder
 card_holder_mi character(1), -- middle initial (if given)
 card_type character(1), -- Visa, Master Card, or Discover
 CONSTRAINT chk_cc_card_type CHECK
  (card_type = ANY (ARRAY['V'::bpchar,'M'::bpchar, 'D'::bpchar]))
)

In an effort to help us combat fraud, we want to ensure that the combination of data for a given table row is the only way that the card in question is ever entered.  Since a given card can wind up in the table more than once, but should never do so with different card holder data, multiple entries need to be allowed IF THEY ARE EXACTLY THE SAME!  But if a card number appears more than once, with different holder data, it should raise an error.

(Note:  This isn't the full table, but it is enough to demonstrate the issue with, I hope...)

I am as yet unable to find a way to do this with sql, and am new enough to postgresql that the functions and triggers elude me.

Any help would be wonderful.

Edit:  Per ypercube's suggestion I am ading what I should have said in the beginning.

1) The information is being entered from an employee operated form (web based script of some kind, I will look into this further)

2) The table / constraint should refuse to allow the insert if the conditions are detected.  Since a new entry would be the entire transaction involving suspect information the entire transaction should be refused.  Obviously the script engine adding the data will have to be modified as well, but I should be able to handle that part.  Writing the data to another table as suspect would be allowable since no actual financial transaction would have been allowed.

3) This data is stored in no other table (this was my initial idea to place it in another table and use a foreign key lookup on the card number + data and reject it if not the same...I am pushing the boss to do this but he has to confer with some one about legality...apparently there is an issue of having it in more than one location)

4) And just for the record, the boss knows he needs a pro to do this, I am simply a stop gap!  (Hopefully a short stop for a small gap!)


texts: Sybase 8 to Sybase 16 migration in Windows 10 OS

texts:
Currently we are using Sybase 16 db in our project. But there are few Sybase 8 db's (that clients are using in market) that needs to converted to Sybase 16 for any patch testing. I used to use the below query to migrate/convert Sybase 8 to Sybase 16 in Windows 7 OS

dbunload -c "UID=DBuserid; PWD= DBpwd; DBF=c:\PathOfSybase8dbToBeConverted\xxxSybase8.db;DBKEY=encryption key" -an c:\PathOfSybase16ConvertedDB\xxxSybase16.db

The above query used to work perfectly fine in Windows 7 OS and Sybase 8 db was getting migrated/converted Sybase 10 db without any hassle.

Recently, we migrated to Windows 10 OS. I have the same set up of Sybase 8 and 16 db's as it was in Windows 7 OS.

But when I run the above dbunload query, the query stops at some point, pointing to a table of my db as shown below

Adaptive Server Anywhere Unload Utility Version 8.0.2.3601
Unloading "DBA"."OneOfTheTableInMyDb" into OneOfTheTableInMyDb (relative to server)

Am I missing some setting in Windows 10 OS to run the query successfully? Any immediate help is appreciated.


texts: What factors prevent and delay TRUNCATE command?

texts: I had a quite large table, perhaps a 50M rows and I had to truncate it. Usually truncate is really fast but in this case it took hours to complete. What are the factors that can prevent truncate command behaving normally?


texts: Packet and N-SubPacket Msql query ( same as catgeory and N sub catgeory )

texts:
First of i am PHP Developer , and working on huge project alone .i think i have made many mistake in creating table. and below is one of those.

I am making packet type system when in my Packet Table , parent packet ( id which is primary key ) and its N -Sub Packet is under (parent_id) is stored , below is my table structure :

Packet_table

id | packet_name | parent_id |
------------------------------
1  |   01        |  0
2  |   02        |  0
3  |   03        |  1
4  |   04        |  1
5  |   05        |  1
6  |   06        |  4
7  |   07        |  4
8  |   08        |  3
9  |   09        |  5
10 |   010       |  2
........................so on and on with N packets in same table

Below is what i have tried but its not getting id N sub packet detail properly:

SELECT p.`packet_name` AS MAIN, s.`packet_name` AS SUB
FROM packet_table s
LEFT JOIN packet_table p ON s.`parent_id` = p.`id`

thats as per above table : id ( which is primary / auto increment )

id = 1 -> main packet (01) , its sub and  N sub packets are :

01 - > 03,04,05

04 -> 06,07

03 -> 08

05 -> 09

in short

01 -> 03 -> 08
      04 -> 06 , 07
      05 -> 09

its not necessary above design format mysql code .. just simple N sub packet query will do

above is just few but in my case there will be N number of sub packet for each (id).

above packet primary id is used in many other tables , so i will get all information in future ?

Note : it can be same as Category and N sub Category type, also please note as said above i don't want query as above explained ( as tree )  .. i just need only mysql query in any format ..

1 } just need is when i search with id=1 then result will give me it all sub packet and it N sub sub packets,

2 } when i search with any sub packet id then it result should give me its sub packet and its all N sub packet and so on and on.. also this sub packet is of which main packet and vise verse.

UPDATE : please check below format for query i need some with N Packet

Table Format 1 :  thats Main Packet
+------------+------------+----------+
| Main Pkt   | Sub Packet | COUNT(*) |
+------------+------------+----------+
| 01         | 03         |        1 |
| 01         | 04         |        1 |
| 01         | 05         |        1 |
--------------------------------------

Second :

Table Format 2 :  thats Sub and Its N sub Packet
+------------+------------+-----------------+
| Main Pkt   | Sub Packet | N Sub Packet    |
+------------+------------+-----------------+
| 01         | 03         |        08       |
| 01         | 04         |        06       |
| 01         | 04         |        07       |
| 01         | 05         |        09       |
---------------------------------------------

Third :

Table Format 2 :  thats Sub and Its N sub Packet
+------------+------------+-----------------+
| Main Pkt   | Sub Packet | N Sub Packet    |
+------------+------------+-----------------+
| 01         | 03         |        08       |
| 01         | 04         |        06       |
| 01         | 04         |        07       |
| 03         | 011        |        014      | -- *****
---------------------------------------------
above ***** : here  03 is actually sub packet of 01  hecene it query will also help me

So that 01 - 03 - 011 - 014


texts: SQL Profiler - Scripting a trace with HostName filter

texts:
I have a stock of scripted trace definitions that I use for different levels of diagnostics, none of which filter by HostName. I needed to filter traffic by host today so:

Fired up Profiler
Created an empty trace
Added SP:Start/Complete
Set a filter on hostname
Scripted to test.sql file

Open file in SSMS, no sign of the filter. Rinse, repeat, same again. Assuming I was doing something daft I looked up the value to use (harder to find than it should be!) and added to my trace definition manually, which worked.

EXEC sp_trace_setfilter @TraceId, 8, 0, 6, N'MyHostName'

Came back to check on why I couldn't get this to work from Profiler and same result. I found mention of this being a problem when scripting traces for 2000 from SSMS but no mention of this situation with SSMS2005/2008 to 2005/2008 servers. Is this a bug in Profiler?


texts:
I want to estimate how many reads the Partial index of PostgreSQL with the method B-tree requires, since I have not been able directly to alter the block size.
PostgreSQL manual about the index here and here about the block size, which is 100 for aux so having 3 reads.

The default memory taken by the block size is 8 KB i.e. 1 block normally but I am not sure if this can be possible because log_1(2) is infinity.
I got the idea that the number of reads is dynamically calculated possibly also in PostgreSQL here by considering B-tree where the block size determines the number of reads.
I would like to know how much this is in the block size: log_b n where b is the block size and n is the number of events.
I think it cannot be one mathematically.
I think Postgres B-tree is implemented the standard way as described in the Wiki page, also described by Cormen et al.

B-tree index contains only keys in PostgreSQL, based on this answer.
Data is then again in tables which are logical heaps.
The point of indexes is to store keys to begin with. Data lies in tables, which are logical heaps, based on here.
I am interested in how PostgresSQL make the entity called B-tree.
Based on here, the physical storage of B-tree indexes and tables are using the same data pages with mostly the same page-layout.
However, I am interested in how this entity works together.
Probably, the functioning of the index and data can be described by this:

  B-tree grows from root, not from leaves.

but more precisely from Sumathi about Fundamentals of Relational Database Management Systems (Studies in Computational Intelligence):

  In B-tree, nonleaf nodes are larger than leaf nodes.  Pointers to data
  records exist at all level of the tree.

In B+tree, pointers exists only at the leaves.
How can you evalute the pointer system of B-tree?
How can you describe the big-O space taken by PostgreSQL B-tree?
How does Postgres make its B-tree index?


texts: The SQL Scripter library is running a SELECT COUNT(*), how do I stop it?

texts:
I'm using the Microsoft.SqlServer.Management.Smo.Scripter library from within a PowerShell script to pull SQL object creation scripts from a DB as part of an automated process. The user account used for the script has minimum permissions; it is public, can VIEW ANY DATABASE and VIEW ANY DEFINITION, and the user mapped to each DB can explicitly SELECT from sys.sql_expression_dependencies. Using this permission set, I can use the account to generate schema-only scripts from within SSMS.
However, when using the below option set for the Scripter class:
    $scriptr.Options.AppendToFile = $False
    $scriptr.Options.AllowSystemObjects = $False
    $scriptr.Options.ClusteredIndexes = $True
    $scriptr.Options.NonClusteredIndexes = $True
    $scriptr.Options.DriAll = $True
    $scriptr.Options.ScriptDrops = $False
    $scriptr.Options.IncludeHeaders = $False
    $scriptr.Options.ToFileOnly = $True
    $scriptr.Options.Indexes = $True
    $scriptr.Options.Permissions = $True
    $scriptr.Options.WithDependencies = $False
    $scriptr.Options.Triggers = $True
    $scriptr.Options.ScriptData = $False
    $scriptr.Options.EnforceScriptingOptions = $True
    $scriptr.Options.ContinueScriptingOnError = $True

... the script fails on every table, with the error The SELECT permission was denied on the object 'theTable', database 'theDB', schema 'dbo'. Notice I'm explicitly not scripting table data, hence the confusion.
I profiled the scripting process, and found that the error occurs on the following SQL statement executed by Scripter:
use [theDB]
SELECT
(CASE WHEN (tbl.is_memory_optimized=0)
                            THEN ISNULL((SELECT SUM (spart.rows) FROM sys.partitions spart WHERE spart.object_id = tbl.object_id AND spart.index_id < 2), 0)
                            ELSE ISNULL((SELECT COUNT(*) FROM [dbo].[theTable]), 0) END) AS [RowCount]FROM sys.tables tbl WHERE SCHEMA_NAME(tbl.schema_id)=N'dbo' AND tbl.name=N'theTable'

I direct your attention to the SELECT COUNT(*). Without SELECT permissions on the DB for the user, this will obviously fail.
I need to stop the scripter object from trying to run the above command. Granting blanket SELECT access to every DB on the server is a Very Bad Thing™, but it's what would need to happen in order for this SELECT COUNT(*) to work. I don't need a rowcount, I don't know why the scripter would need one, and at this point I don't really care, I just need it to not happen so I can fix this process. There has to be an option I'm missing.
PS: this script works fine as written on two Azure-hosted DB servers with identical account permissions. The Azure DBs are v14 (2016) and I also have a legacy v10 (2008 R2) server that it runs against without error; the failure occurs on a v15 (2019) database server.


texts:
I'm trying to connect to my SQL Server Express and I get this error. This happens both locally and on client.

  Connection Timeout Expired. The timeout period elapsed during the
  post-login phase. The connection could have timed out while waiting
  for server to complete the login process and respond; Or it could have
  timed out while attempting to create multiple active connections. The
  duration spent while attempting to connect to this server was
  [Pre-Login] initialization=216; handshake=7899;
  [Login] initialization=0; authentication=0;
  [Post-Login] complete=5999;  (.Net SqlClient Data Provider)

Can someone help me?


texts: Using listener_name in 4 part naming convention gives sys.servers error

texts:
App uses cross db connections, the dbs are in different AG's, so I need them to use 4 part naming convention - in the place of servername would be listener name. But when I run a simple query like:

select * from Listener_Name.DatabaseName.[schemaname].[objectname]

I get:

  Could not find server 'Listener_Name' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Listener_Name is not in sys.servers table.

My current environment has 2 nodes and 3 AGs, each having their own listeners.  Do I need to add the 3 listener names as linked servers on both nodes? And does anyone have a script on how best to create the linked server using listener name?   I would like it to be similar to the local server which shows up on the sys.servers table with server_id 0.


texts: I want to know which table the value comes from in inner join

texts:
I have a simple query where i do 2 inner joins to combine data from 3 tables. within the table i would like to know which table each value comes from. i attempted the following but the syntax is wrong.

select e.subscriberkey, Source
CASE
    WHEN FROM Currently_in_Welcome THEN Source = "E"
    WHEN FROM Sell_Engage_Unknown THEN Source = "S"
    WHEN FROM At_Risk_NonEngagement THEN Source = "R"
END
from Currently_in_Welcome e
inner join Sell_Engage_Unknown s on e.subscriberkey = s.subscriberkey
inner join At_Risk_NonEngagement r on r.subscriberkey = e.subscriberkey

thanks in advance for all your help.


texts:
I'm doing some high level planning of an application at the moment. When a bcp import finishes, I'm going to send an email using Database Mail.

I'd like the email to have options or filters for the recipient. Their choices will determine which particular SQL scripts kick off.

For example:

  from: database@mail.com

  to: henry@mail.com

  Import has completed, would you like to:

+------------+---------+
|  Update    |   Find  |
+------------+---------+

My intuition was that I could make the choices mailto links, to populate the body of a response email. But I haven't been able to find any resources on triggering an alert when receiving an email. In fact, the diagram I found below suggests to me that you actually can't reply to Database Mail:

Has anyone ever worked on a project like this?


texts: On Load sub which checks records causing problems

texts:
I have a checkbox called 'Added to Schedule' on a form (booking sheet) which is supposed to be ticked if the job appears on another table (schedule). It usually works but sometimes if the job is then unscheduled or moved around, this checkbox does not get unticked.  It can also be vice versa, where the box is not ticked even though the job is on the schedule.

So I wrote an On Load event to check if the job is on the schedule (using the unique job number in a DLookup). If it finds the job on the schedule, it ticks the box. If it doesn't, it unticks.

It works well, but the only problem is, when I look at a booking sheet that has no jobs added to it yet. The booking sheet record has several required fields, such as 'Date' and 'Specific Job No' (between 1 and 99).

When the On Load event runs, it seems to recognise the top empty line as a record to check, and Access then recognises it as an incomplete record. When you then try to close the booking sheet or click somewhere else, it's waiting for you to enter info in the rest of the fields and gives you a validation error ('please enter the date', etc).

How do I get around this? I don't want my Form_Load event to run if there are no lines on the current booking sheet (which is a subform on a form which holds some of the information, such as the account manager).

Private Sub Form_Load()
Dim strJob As String
Dim strSpec As String

strSpec = Format(Me.Specific_Job_No.Value, "00")
strJob = Left(Me.Parent.JobRef.Value, 18) + strSpec
    If DCount("*", "tblWebSchedule", "[JobNo] = " & """" & strJob & """") = 1 Then
        Me![Added to Schedule] = True
        Me![Added to Schedule].Locked = True
    Else
        Me![Added to Schedule] = False
        Me![Added to Schedule].Locked = True
    End If
End Sub


texts: SQL Server with French_CI_AS Collation date conversion

texts:
I have a French installation of SQL Server using French_CI_AS Collation. For legacy reasons, a table column stores data as VARCHAR which is later converted to the right type, akin to variant in C++.
Two rows in this column have data as:
2020-10-12 22:54:40

2020-10-13 04:42:10

The second date fails when converting to DATETIME with default options. The application uses ODBC escape sequences. SQL such as COLMVALUE >= {ts'2020-01-01 00:00:00'} fails with the error:

La conversion d'un type de données nvarchar en type de données datetime a créé une valeur hors limites

I did a DBCC useroptions  and the server has date format set to dmy. Why is the 2nd row failing? It looks like it is trying to interpret "13" as the month. If the format is dmy, shouldn't both dates fail?
Also, it is interesting to note that on this server, the following fails:
DECLARE @datevar DATETIME = '2020-10-13 04:42:10';
SELECT @datevar;

But this works:
DECLARE @datevar DATETIME2 = '2020-10-13 04:42:10';
SELECT @datevar;

I am aware that DATETIME2 is more accurate and has greater range than DATETIME. However, it seems that even the parsing is different for the two here.


texts:
I have a bacpac file from a read-only database hosted on Azure. When imported to a new server the database is also read-only.

I’ve tried looking for answers with no luck.

How can I change it to a writeable?