texts: Can Execute trigger in SQL Server 2008 R2

texts:
I'm trying to create a simple trigger to update a table's column after it was inserted.

But when I execute the trigger it produces an error:

  Cannot execute script. Additional information: Unable to start
  Transct-SQL debugger. The Transact-SQL debugger does not support SQL
  Server 2005 or earlier versions of SQL Server. (SQLEditors)

I'm using SQL Server 2008 R2.

Here's my simple trigger just to check if it execute.

CREATE TRIGGER tgrCheckFile
ON CheckFile
FOR INSERT
AS
        PRINT 'New Check has been issued'


texts: Can I sync an existing database using a .dmp file?

texts:
I have two oracle databases (production and lab). When I created the lab database I imported a .dmp file from production to copy the data. Now that the database has been running for a while the data is out of sync and I'd like to import again. What's the easiest way to do this?

I tried using imp again but it shows errors now since the tables are already there. I could delete the data files and re import I suppose.

Edit: This is what I've tried:

$ imp user/password file=MyExportFile.dmp

following statement failed because the object already exists:
CREATE TABLE ...
...
Import terminated successfully with warnings.


texts: How do I avoid excessive zeroes for numeric fields?

texts:
As per recommendations, I always use the numeric type for any column supposed to hold a sum of money.
When inserting such values (or updating them), I always use high precision math functions in my application (PHP "BC Math").
This results in the fields getting values such as:
-1494.744565020000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

I view them later in pgAdmin 4 and it's very annoying to see the long series of pointless zeroes, when it should be just:
-1494.74456502

There is no meaning with storing/representing all those zeroes like that. I'm not fully sure if this is PG or pgAdmin's doing, actually.
Either way, I want to avoid it. How do I prevent numeric values from being stored with pointless zeroes?
(Of course, I don't want less precision -- I'm just talking about zeroes that literally have no meaning and thus should be automatically cut off from the right until they hit a non-zero digit.)
Is PG for some reason storing these numeric numbers like this? If so, why?


texts: Cluster IP's under Access Point for Administering the Cluster

texts: When adding the cluster IPs under Access Point for Administering the Cluster, do we only include IPs for the public subnets, or do we also need to include an IP for the private subnet(s)?  This cluster will be used for a three-node multi-subnet AlwaysOn Availability Group.  As for the listener, I know it needs to include all of the public  subnets, but I'm not sure how the cluster core resources should be configured.


texts:
I'm looking to try and run the same stored procedure multiple times with different parameters but at the same time.

I'm using SQL 2014

The reason for this is that the procedure takes around 7 hours to complete. It actually does the same process many times. So for instance it might build a new database and tables for each branch.

What I want to do is break down the stored procedure so I can run in per branch but then run each query in parallel. I've run tested this by running it in separate query windows and it runs almost 80% quicker.

Can anyone give me a dummies guide into running queries in parallel?


texts: Why is GETUTCDATE earlier than SYSDATETIMEOFFSET?

texts:
Alternatively, how did Microsoft make time travel possible?

Consider this code:

DECLARE @Offset datetimeoffset = sysdatetimeoffset();
DECLARE @UTC datetime = getUTCdate();
DECLARE @UTCFromOffset datetime = CONVERT(datetime,SWITCHOFFSET(@Offset,0));
SELECT
    Offset = @Offset,
    UTC = @UTC,
    UTCFromOffset = @UTCFromOffset,
    TimeTravelPossible = CASE WHEN @UTC < @UTCFromOffset THEN 1 ELSE 0 END;

@Offset is set before @UTC, yet it sometimes has a later value. (I've tried this on SQL Server 2008 R2 and SQL Server 2016. You have to run it a few times to catch the suspect occurrences.)

This does not appear to be simply a matter of rounding or lack of precision. (In fact, I think the rounding is what "fixes" the issue occasionally.) The values for a sample run are as follows:

Offset

2017-06-07 12:01:58.8801139 -05:00

UTC

2017-06-07 17:01:58.877

UTC From Offset:

2017-06-07 17:01:58.880

So datetime precision allows the .880 as a valid value.

Even Microsoft's GETUTCDATE examples show the SYS* values being later than the older methods, despite being SELECTed earlier:

SELECT 'SYSDATETIME()      ', SYSDATETIME();
SELECT 'SYSDATETIMEOFFSET()', SYSDATETIMEOFFSET();
SELECT 'SYSUTCDATETIME()   ', SYSUTCDATETIME();
SELECT 'CURRENT_TIMESTAMP  ', CURRENT_TIMESTAMP;
SELECT 'GETDATE()          ', GETDATE();
SELECT 'GETUTCDATE()       ', GETUTCDATE();
/* Returned:
SYSDATETIME()            2007-05-03 18:34:11.9351421
SYSDATETIMEOFFSET()      2007-05-03 18:34:11.9351421 -07:00
SYSUTCDATETIME()         2007-05-04 01:34:11.9351421
CURRENT_TIMESTAMP        2007-05-03 18:34:11.933
GETDATE()                2007-05-03 18:34:11.933
GETUTCDATE()             2007-05-04 01:34:11.933
*/

I presume this is because they come from different underlying system information. Can anyone confirm and provide details?

Microsoft's SYSDATETIMEOFFSET documentation says "SQL Server obtains the date and time values by using the GetSystemTimeAsFileTime() Windows API" (thanks srutzky), but their GETUTCDATE documentation is much less specific, saying only that the "value is derived from the operating system of the computer on which the instance of SQL Server is running".

(This isn't entirely academic. I ran into a minor issue caused by this. I was upgrading some procedures to use SYSDATETIMEOFFSET instead of GETUTCDATE, in hopes for greater precision in the future, but I started to get odd ordering because other procedures were still using GETUTCDATE and occasionally "jumping ahead" of my converted procedures in the logs.)


texts:
I'm trying to backup the database I made in postgresql but it doesn't seem to work

root@localhost:/home/tests# sudo -s -u postgres
postgres@localhost:/home/tests$ psql
psql (8.4.17)
Type "help" for help.
postgres=# pg_dump test_site > payroll.dump.out
postgres-# ls
postgres@localhost:/home/tests$ exit
root@localhost:/home/tests# ls # Where is the backup?
cars  manage.py  media  staticfiles  test.db  tests  tests.db  test.wsgi

Can someone help me , please :)


texts:
I have four tables Product, Packaging, LabelsForPackaging and Label

I need to query all Products with their Packaging and Labels (should result in multiple rows)

Product has Packaging1Id and LabelsForPackaging has the PackagingId and the LabelId.

I tried this

select * from Product
left join Packaging on Product.Packaging1Id = Packaging.Id
left join
(
    select * from LabelsForPackaging
    inner join Label on LabelsForPackaging.LabelId = Label.Id
    where LabelsForPackaging.PackagingId = -- what to use here?
) as P
on Product.Packaging1Id = P.Id

If I try to use where LabelsForPackaging.PackagingId = Packaging.Id in the subselect, I get this error:

The multi-part identifier "Packaging.Id" could not be bound.


texts:
I have this statement

EXEC [spProject_Update] @ProjectID = 496
,@Name = 'Hangman Bollywood iPhone'
,@Description = ''
,@EstimateTime = 2674800000

I will get milliseconds in @EstimateTime.
How can I convert this to datetime in SQL Server?


texts: Best approach for querying multiple tables

texts:
In SQL Server, I have a table, let's call it Source. The structure is

ID | SourceValue
-----------------
1  |  'value1'
2  |  'value1'
3  |  'value2'

and so on.

ID is a primary key and SourceValue is not unique.

I also have several other tables which have SourceValues in their records. As it's not unique, there is no Foreign Key constraint. This column also isn't UNIQUE in other tables. Furthermore, these columns can hold values both of SourceValue format and other formats. Because of this, I need to include a DISTINCTed SourceValue data in the query.

I need to return COUNT of every occurence of SourceValue in all tables other than Source.

Our approach was to create a view which holds DISTINCT SourceValues from the Source table, and then do the following:

SELECT q.SourceValue, SUM(q.Count) as Count
FROM (
  SELECT dvw.SourceValue, COUNT(dvw.SourceValue) AS Count
  FROM distinctValuesView AS dvw
  JOIN DependantTable1 dt1
  ON dvw.SourceValue = dt1.SourceValueColumn
  GROUP BY dvw.SourceValue

  UNION

  SELECT dvw.SourceValue, COUNT(dvw.SourceValue) AS Count
  FROM distinctValuesView AS dvw
  JOIN DependantTable2 dt2
  ON dvw.SourceValue = dt2.SomeOtherColumn
  GROUP BY dvw.SourceValue
) AS q
GROUP BY q.SourceValue

distinctValuesView is defined as

SELECT DISTINCT SourceValue FROM Source

The problem is there are more than 20 DependantTables (transaction tables) and all these tables have tens of thousands of rows, with the Source table having more than 20.000 rows.

Naturally, this query is unacceptably slow (2 seconds execution time). Is there any other approach, or a way to speed it up?


texts: When to create a new table if functionality is the same?

texts:
I am having trouble making database schema/design decisions. The database I am building is relational.

Consider this example:

I have these tables: orders, products, companies.

I want to create links between two orders, two products, and two companies (to show that they are related). There is no additional data about the relationship which needs to be recorded, just the fact of the relationship itself (i.e. the relationship table just needs the two IDs and not any other fields - What I mean by this, is that there is no meta data or other fields for the relationship, I purely need to know that a relationship link exists, thus only need an object_one_id, and object_two_id).

I can create 1 table (relationships), or I can create 3 tables (order_relationships, product_relationships, company_relationships). If I just use 1 table, I will need a separate column to determine the relationship type (product, order, or company), and will need to look up the related table based on the object type. If I use 3 separate tables, then I can create a direct relationship between the foreign keys and primary keys across the tables (for example product_relationships would have a product_id, and related_product_id, which both link directly to products in the products table).

Here is role information:
For products, a product can have multiple other products to which the primary product is a comparable. This means that these other, related products, are considered the same physical makeup.

For companies, a company can have multiple other companies to which the primary company does business with. This means that these other, related companies, are considered vendors or customers.

For orders, an order can have another order to which the primary order correlates to. For example, if a company creates an invoice/sales order, then another order is created for the customer company as purchase order.

How can you decide which solution is better? What other questions would you ask to make the decision?

For the DBMS, I am looking to use either MySQL or MariaDB.


texts: How to enable NULL values in MySQL timestamp fields?

texts:
I need to restore backup from one MariaDB server (version 10.1.23) to another MariaDB server (version 10.1.37). But this version has a much stricter mode by default and does not allow NULL values in timestamp fields and throws the below error when I restore the backup.

  1067 - Invalid default value for 'dt'

I try to add this values to *.cnf file in /etc/mysql/conf.d/

[mysqld]

sql_mode=ALLOW_INVALID_DATES,IGNORE_SPACE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
explicit_defaults_for_timestamp=OFF

Queries:

show variables like 'sql_mode';
show variables like 'explicit_defaults_for_timestamp';

show changed values of variables but it does not work. Why?


texts: Why is it important for every table to have a primary key?

texts:
So I have some tables in Sql Server that are essentially a list of sales, things like:

ProductID
SalesOrderID
ProductFamilyID
ProductCost
ProductSource

and so on. In this case, none of the columns are necessarily unique, so I can't create a primary key from any combination of them. In fact, the only constraint that I really have on the table is that I need every row in the table to be a unique combination of the columns. So I'm assuming something like a unique index would be the way to go there.

The only primary key I could add is something like an autoincrement primary key. But what would be the actual use of that, database wise? What are the possible problems with not creating a primary key for a table like this?


texts: sp_repladdcolumn - is it possible to supply a list of Publications to add this column on?

texts:
Scenario

We have an existing table in 3 different publications (going to 3 different subscribers), 1 of those publications does not publish all columns.

All of these are transactional pull subscriptions.

I now need to add some more columns onto this table and we want to add them on to just the first two publications.  However from MSDN:

  [ @publication_to_add =] 'publication_to_add' Is the name of the
  publication to which the new column is added. publication_to_add is
  nvarchar(4000), with a default of ALL. If ALL, then all publications
  containing this table are affected. If publication_to_add is
  specified, then only this publication has the new column added.

which implies that it you expects it to specify 1 publication or ALL publications.

A sample table/publication detail to help demonstrate the issue:

Table sample

[MyTable] (
    ID int IDENTITY(1,1) NOT NULL,
    myName varchar(50) NULL,
    myDOB datetime,
    myJob varchar(100),
    mySpouseID int )

Publication/Subscriber sample

[InternalPublication] All fields, sent to InternalAdmin system
[Reporting] All fields, sent to Reporting/Datawarehouse system
[External] sent to External accessable system, fields sent:
    (ID, myName, myDOB)

Question

How do I add on:

[myMarriageDate] datetime NULL,
[myDivorceDate] datetime NULL

and have it only added to the publications [InternalPublication] and [Reporting]?

Note: sample is a made up one to protect the innocent


texts: How can I stop SQL from validating an OPENROWSET query when its not going to be executed?

texts:
I have a problem where a stored procedure is throwing an error because it's trying to run/validate an OPENROWSET query inside it that it doesn't actually have to execute.

The stored procedure that looks something like this:

IF (@flagA = 1)
BEGIN
    INSERT INTO #tmpData(Column1, Column2, Column3)
    SELECT A, B, C
    FROM (
        SELECT *
        FROM OPENROWSET(
            'Microsoft.ACE.OLEDB.12.0',
            'Text; HDR=Yes; Database=\\server\tmpFiles',
            'SELECT * FROM FileA.txt')
    ) as T
END

IF (@flagB = 1)
BEGIN
    INSERT INTO #tmpData(Column1, Column2, Column3)
    SELECT D, E, F
    FROM (
        SELECT *
        FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0',
        'Excel 8.0;Database=\\server\tmpFiles\FileB.xls',
        'SELECT * FROM [Sheet$]')
    ) as T
END

IF (@flagC = 1)
BEGIN
    INSERT INTO #tmpData(Column1, Column2, Column3)
    SELECT G, H, I
    FROM (
        SELECT *
        FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0',
        'Excel 8.0;Database=\\server\tmpFiles\FileC.xls',
        'SELECT * FROM [Sheet1$]')
    ) as T
END

Note that each OPENROWSET references a different file, in a different format, with different column names.

My problem is that if one of the files doesn't exist, or exists in the wrong format (such as someone uploading a FileB as FileC by mistake), I get an error when the procedure tries to run, even though that OPENROWSET query is never meant to get executed.

For example, if FileB doesn't exist and you try to run the procedure with @flagB = 0, it will throw an error saying it can't find the linked server for FileB

Or if a user uploads a FileB as a FileC by mistake, then corrects themselves by removing FileC from the form, it will throw an error saying Cannot process the object "SELECT * FROM [Sheet1$]" next time the procedure runs because FileC won't have a sheet named Sheet1, even though @flagC = 0

How can I prevent SQL from validating the OPENROWSET queries if they're not the ones getting executed?


texts:
I need to copy an Oracle 10g (10.2.0.4.0) database (150GB) to an Oracle 12c (12.1.0.2.0) database. Both are Standard Editions. What method should I use?

Target is on a different server - empty database. File system structure is different. Target is a Testserver which needs a consistent state but does not need to be up-to-date to production.


texts: SQL Server Data types and operators clarification needed

texts:
I would like to confirm the following with respect to the basic SQL Queries:

The float and real data types cannot be used with the equals and not equals operator in sql. If so then what else operators can i use for these data types.
Filtering based on the BLOB and XML columns is not allowed. So shall we disable any search based on these datatypes, also is there any other data type that cannot be filtered from the sql query?

[edit]
3. Also what kind of operators can i use for the timestamp data type.


texts:
I am using SQL Server 2008 and have a Maintenance Plan setup that does a DBCHECK before the nightly backup.

This has been working fine for months. It has started failing. If I run a DBCHECK on the Databases, they are pass fine. I even went into the maintenance plan and clicked 'View SQL' to get the TSQL that is run during the Plan and it again all passed:

USE [ClientPortal]
GO
DBCC CHECKDB(N'ClientPortal')  WITH NO_INFOMSGS
GO
USE [PHJ_Common_Objects]
GO
DBCC CHECKDB(N'PHJ_Common_Objects')  WITH NO_INFOMSGS
GO
USE [PilotWebApp]
GO
DBCC CHECKDB(N'PilotWebApp')  WITH NO_INFOMSGSenter code here

Why would this fail during the plan but work fine when run manually?


texts: Where do you get Example/Sample Databases from?

texts:
I'm looking for some example or sample databases (sales / production / employee / sports / Etc) to test my skills on other systems. So far my skills are limited to one sector education and i want to branch out see if the skill I've learned can be applied easily to other markets.

Ideally they would be large data sets. I can't seem to find any myself that are free or of any use. Format isn't much of an issue as i have used most forms of SQL, etc.

Thanks in advance.


texts: How to obtain the password of a specified user in Oracle? What query to use to obtain that?


texts: Exclusion constraint generates: ERROR: operator &&(anyrange,anyrange) is not a member of operator family "range_ops"

texts:
How come when I try to create an exclusion constraint with GIST,

CREATE TABLE foo (
  a tsrange,
  EXCLUDE (a WITH &&)
);
ERROR:  operator &&(anyrange,anyrange) is not a member of operator family "range_ops"
DETAIL:  The exclusion operator must be related to the index operator class for the constraint.


texts:
I would like to order this table but I can not find a way to make it work. Can you help me?

I have this table:

I need this:

I have a table where documents are listed, the first field is the id of the document and the second field represents the father, so I must show a list where you can see in an orderly manner that 242 is the first document and 252 and 335 were generated from the 242.

The query:

select * from table_name order by col1 ASC, Col2 ASC

...doesn't work for me. I do this query:

SELECT
    FR1.[report_id],
    FR1.[report_parent]
FROM [FARA_reports] FR1
WHERE
    FR1.[report_is_delete] <> 1
    AND FR1.[report_is_tmp] <> 1
ORDER BY
    FR1.[report_id] asc,
    FR1.[report_parent] desc

and this is my first image.


texts: Is it possible to break down query time?

texts:
We have a setup of a few cloud servers that connect to a MySQL cluster on dedicated servers over Rackspace's Rack Connect network. We have noticed recently that the time for database queries (via PHP) have slowed down and as part of investigating we wanted to figure out whether it was an increase in the amount of time to process the query and generate results, or is it the time to transfer the data from the servers to database cluster that's increased?

Is there a way for me to figure this out? So far our timing is just a total overall for each query.


texts: MySQL load: Got timeout reading communication packets

texts:
I am loading a large MySQL dump file (120 GB). I am using MySQL 5.7.

I am getting the following error:

Aborted connection 6 to db: 'unconnected' user: 'root' host: 'localhost' (Got timeout reading communication packets)

I already have

max_allowed_packet = 1GB

Note that it specifically says "timeout" error. I have seen questions asking about "error" reading communication packets.

Any other suggestions?

It failed after about 12 hours so it's not like I can just keep trying stuff. I need to get this DB loaded.


texts: Faster query with pattern-matching on multiple text fields

texts:
I have one Postgres table with more than 20M tuples:

first_name | last_name | email
-------------------------------------------
bat        | man       | batman@wayne.com
arya       | vidal     | foo@email.com
max        | joe       | bar@email.com

To filter the records I'm using:

SELECT *
  FROM people
WHERE (first_name || '' || last_name) ILIKE '%bat%man%' OR
    first_name ILIKE '%bat%man%'  OR
    last_name ILIKE '%bat%man%'   OR
    email ILIKE '%bat%man%'
    LIMIT 25 OFFSET 0

Even with indexes the search is taking almost one minute to return results.
There are indexes for (first_name || '' || last_name), first_name, last_name and email.

What can I do to improve the performance of this query?


texts: Quorum question for SQL Cluster (Always On)

texts:
We are looking at setting up an SQL Always On High Availability environment at our organisation.
Our organisation has two datacenters of with one much smaller one but this one's being created to create some high availability.

At our primary site we are looking at installing three servers and one at the secondary site. These server will then be added to a cluster (and we'll add the DB's in some Availability Groups accross these servers)

But here comes the problem. When the primary site is down due to a power outage for example, the whole cluster will be down because the one server doesn't have a majority even with a default quorum. We did also look at adding a quorum to cloud-storage (disk-only quorum setup) but when the internet connection is down (happens sometimes here in Belgium) we also loose the entire cluster.

Does anyone have any experience or a solution for this?

Thanks in advance!


texts: PostgreSQL postmaster will not start after configuring for SymmetricDS

texts:
I'm trying to configure SymmetricDS for use with PostgreSQL, a database platform widely supported by the former.

When running through the quick-start tutorial (which leads you to some pre-setup documentation), I am instructed to append the following line to postgresql.conf:

custom_variable_classes = 'symmetric'

The next step of the tutorial requires the postmaster to be running (at least that's what I glean from the stack trace...), so I

service postgresql-9.2 start

but this fails.

Without this single line added (or if it's commented out), PostgreSQL starts as expected.

I must be doing something wrong, so what is it?

yum install postgresql92 postgresql92-server
Download and unzip SymmetricDS into ~
service postgresql-9.2 initdb
Edit /var/lib/pgsql/9.2/data/postgresql.conf as above


texts: Connect Oracle database using AD account via OID

texts:
We have configured Oracle Internet Directory. We have integrated OID with AD. We have also registered Oracle database with OID. And we are now trying to configure authentication to the database with AD users.

Any suggestions, what additional steps should be done to connect to the Oracle database using AD accounts.

We have read many documentations about OID but have not found any certain step by step instructions how to configure it.

We are newbies with this product.

Waiting your suggestion..


texts: How to get all documents from a particular chunks in MongoDB?

texts:
From mongos log, I got the following message

 balancer move failed: { chunkTooBig: true, estimatedChunkSize: 408839903, ok: 0.0, errmsg: "chunk too big to move" } from: shard4 to: shard1

I know this issue occurs due to either

documents size to large,
index key not granular enough, and/or
chunkSize too small

While I'm confident that case 2 and 3 are not the cause, I want to see whether someone abuses the database by inserting extra large documents.

The mongos log shows the docs are in the range :

min: { Country:"US", City:"LA" } max: { Country:"US": City:"NY" }

min: { Country:"FR", City:"Paris" } max: { Country:"US": City:"New York" }

I want to aggregate the schema of documents within this range, and see if some documents contain extra fields.

Since the indexes are not monotonically increasing, I can't get all documents within this range (or can i?).

How can I

get the chunkID with the query

  db.coll.find({ Country:"US", City:"LA" })
  get all the docs within this chunk?

  Other suggestions are welcome.
  Thanks in advance


texts: How to store x,y graphs: (a) Using x,y columns for each point, or (b) to store the set of points as comma separated strings?

texts:
We are designing a database to store the X,Y graph data of electronic devices apart from other entities.
Business domain description

We have a collection of objects A, each object A has a collection of objects B.

Each object B has a collection of X,Y points forming a graph.

For example:

Object A has a collection of ~30,000 to 50,000 object B instances.
Each object B instance has X,Y graph data of about 200 to 250 points of double values.

Expected data size:

~ 100,000 A instances
~ 50,000 B Instances in each A
~ 200 graph points

Number of rows:

100,000 * 50,000 * 200 = 1,000,000,000,000 = 1 trillion.

Requirements

The users are interested in fetching the entire data set of an object B instance, not of a single point.

The users are interested in getting the X,Y points of all the B object instances for a given object instance A.

Users rarely fetch, probably like one or two queries in a day for a given object instance of A.

Considerations
Design 1
Standard-approach design in the simpler form to design the database to retain point data:

Pros: The data is stored "directly" so retrieval doesn't need any conversion.

Cons: The table ends up with trillion of rows, we need to do table partition to get quicker access of data.

Design 2
To store as comma separated values:

Pros: Data table size is confined to the instance counts of Object B.

Cons: Involves using string split to convert the string to rows or to give the data in the comma separated format to the user.


texts: Why does the same query run faster the second time I run it?

texts: I noticed when running a SQL query it is faster the second time than the first. So my question is, in Oracle SQL Developer where can you see the statistics that explain this behaviour?


texts:
Which are the correct steps to disable (not removing) TDE on a AlwaysOn database in an availability group?

I found steps for removing TDE but that involves removing the availability group and disabling the AlwaysOn relationship. I hope this is only for removing at all the TDE configuration.


texts: Conditional string concatenation in PostgreSQL

texts:
I have a table parcels which currently contains the columns owner_addr1, owner_addr2, owner_addr3. Sometimes, one or both of the latter two fields is empty. I want to combine them into a single new field, owner_addr where each of the above fields is concatenated with // between each of them.

But if one or more of the original columns is NULL, I don't want to concatenate // to the result column. So for instance, if owner_addr1 is 123 4th Avenue SE and owner_addr2 and owner_addr3 are NULL, then I want the result column to just be 123 4th Avenue SE, not 123 4th Avenue SE // // (which would happen if I just did CONCAT() with // between the NULL strings ... I only want to add // between non-NULL columns, or leave it out altogether if there is only one non-NULL column.

Is there an easy way to do this kind of conditional concatenation in Postgresql, where it leaves out empty lines? Or should I write a python script to do this?


texts: Is there a better way of displaying 'Count' of records beside other columns in a select query?

texts:
I have a table with below structure :
Test1(c_num  number ,c_first_name varchar2(50), c_last_name varchar2(50))

1)There is a normal index on c_num column.
2)The table has nearly 5 million records.
I have a procedure as you can see below. I want to display the Count(*) along with other columns.I want to know if there are better ways of doing this so that we can have better performance.
create or replace procedure get_members(o_result out sys_refcursor)
  is
begin

open o_result for
  select c_num,
         c_first_name,
         c_last_name,
         (select count(*) from test1) members_cnt -->Is there a better way instead of doing this?
  from test1;

end;

Thanks in advance


texts: PostgreSQL: Index Trigger / Hook? For Synonyms

texts:
Is it possible, in PostgreSQL, to add a trigger to modify data prior to it going into a full text index?

PostgreSQL requires using files to add custom dictionaries for things like stop words, synonyms, etc. However, on most managed database hosting file access is not allowed. What would be best is if I could use a table (such as a spaced single or double column) instead of a file dictionary to supply the list of synonyms, stop words, etc. but after a lot of searching it does not appear that is possible.

Another path I tried to look into was to populate a table with synonyms then export it to the file system, however PostgreSQL requires a full path to the file. On managed hosting you cannot provide a full path if you do not know the the paths to begin with.

So now I am down to seeing if there is some way I can "hook" into the pre-index process and modify the data myself prior to it being added to an index. Note that I thought about using a trigger and modifying the data directly but that would also modify the data going into the stored row's field - something I don't want to do. What I am looking for is a way to leave the original data / field intact and being entered into a field while modifying the information prior to it being indexed. I thought about adding an additional column to the table with the modified data, however that can get very messy and may require a lot of extra columns for larger data sets. Another option is using materialized views or inherited/child tables but, once again, would be a messy and difficult to manage outcome.

TLDR: I am asking if there is any viable way to define stop words, synonyms, etc. without having to use files (so that it can be used on managed systems that do not provide file access)?


texts: How to connect to a SQL Server from another computer using SSMS with only hostname?

texts:
I set up a VM server computer on AWS EC2 and got a SQL Express Server running on it on port 1433. I made sure to allow TCP/IP requests and allowed TCP port 1433 through the firewall along with UDP port 1434. I then created a second VM computer on AWS EC2 - same VPC, security group, and subnet - and installed SSMS.

I also made sure that to put the two computers in the same workgroup in windows, and made them discoverable.

I can connect to the SQL server I created via IP and port number (e.g. IPAddress\Hostname,1433) but I can't connect with just the hostname. Any ideas to what I'm missing here? I followed the documentation here to the letter and it doesn't seem to work.

I've also tried having the two EC2 VM computers ping each other by computer name and it doesn't work either.


texts: How can I skip rows that I've already imported?

texts:
I've previously used the SQL Server 2008 R2 "import data" wizard to import an Excel file into my database.

Now I'm trying to import more Excel sheets that may contain the same data that was previously imported.

How can I import these Excel sheets to the same database, while skipping the rows from Excel that already exist in the database?


texts: Analyse MySQL General Query Log in Real-time?

texts:
We want to use mysql general query log to do real-time monitoring and auditing.

Currently our approach is:

set general_log=on;
sleep 15m;
set general_log=off;
scp & rm xxx.log;
set general_log=on;...

But the main problem is when turn on/off general log it'll cause a peak of slow query.

I also thought of another approach: turn on genlog; tail -f it and send out the log; periodically truncate the logfile (with "> xxx.log" or "cat /dev/null >xxx.log").

I'm wondering whether it's practical.

If only mysql would provide some built-in general log message queue stuff...


texts:
I have Postgresql 9.4 database and products table. For each product there must be list of pairs:
price; quantity for which this price is acutal

Products table may contain millions and billions of records. For this products table i must provide filtering by price, filter by quantity, and filter by price+quantity. If there is only price filter than if product has at least one price variant that satisfites filter then this product will be in result list. If there is price+quantity filter then product will be in result list only if there is at least one price variant which has price AND quantity that satisfies filters.

If I create separate tables

create table prod (id integer primary key);
create table optprice (prod integer, price decimal, q integer);

than with millions of products query takes realy long time:

select * from prod where id in (select o.prod from optprice o where price between 10 and 500 ) limit 20;
 Planning time: 0.166 ms
 Execution time: 867.663 ms

select count(*) from prod where id in (select o.prod from optprice o where price between 10 and 500 );
 Planning time: 0.166 ms
 Execution time: 867.663 ms

Even if I replace first query with joins, count query still too slow

select count(*) from prod left join optprice on id=optprice.prod where price between 10 and 500 limit 20;
 Planning time: 0.149 ms
 Execution time: 1478.455 ms

I decided to use postgresql arrays, so each product has field optprice with something like: {{112.3, 33}, {555.12, 66}, {77.8, 88}}

But I can't understand how can I implement filtering, described earlier. I can implement separate price or quantity filters. I can't see how price+query filtering is possible here. I can write some function but, if i not mistaken, i lose indexing ability and again queries become too slow.

Is it possible to do something like this in postgresql, so it will work relatively fast even on large datasets? (Also, sorry for my bad english).


texts: Are there any disadvantages to partitioning on financial year?

texts:
Our current set up has one table per financial year (May 1- April 30). Each table has approx 1.5 million rows. We have about 8 years of data, and will obviously be adding each year.

The majority of queries are within the financial year/one partition. Either select * from sales where date time between '2013-05-01' and '2014-04-30 23:59:59' or some period within that year.

My plan is to have a range partition on an InnoDB table. e.g.

PARTITION BY RANGE COLUMNS(datetime)
(PARTITION p0 VALUES LESS THAN ('1999-05-01') ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN ('2000-05-01') ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN ('2001-05-01') ENGINE = InnoDB,
...

This means that the PK has to become PRIMARY KEY (index,datetime).

Are there any significant disadvantages to partitioning compared to having an unpartitioned table? I know that means the PK is now length 12 and all further indexes will have that prepended to it. Does that make a difference? The table needs to work faster on reads than writes, and there are a fair few indexes on it.

Is there any functional difference between partitioning on RANGE COLUMNS(datetime) and RANGE TO_DAYS(datetime) using MySQL 5.5+?
Is partitioning on datetime OK? One alternative is adding in a column for financial year and indexing/partitioning on that? (And then adding another index on datetime for other queries).

We do sometimes need to query the time across all time or over "the last X months", but this is pretty rare. The main advantages of moving to a single table is to eliminate the logic in the application working out which table to insert/update/select and not needing to calculate unions in those situations where we need more than one table.


texts: wsrep_slave_threads and tmpdir space usage

texts:
After increasing wsrep_slave_threads at Galera nodes I have experienced that the mysql tmpdir, which is a ramdisk, was using much more resources. Especially notable during xtrabackup-v2 STTs.

Also strange was that there was a huge discrepancy between du -s tmpdir and df -h tmpdir. While du -s tmpdir showed the directory as empty or only occassionally occupied by some small files, df -h tmpdir revealed that the actual usage of the ram risk was getting to 100 percent, what caused databases to crash.

With lsof tmpdir you were able to see that there were many deleted files, which has not been freed yet.

~ $ lsof /mysql-tmp/
COMMAND     PID  USER   FD   TYPE DEVICE   SIZE/OFF     NODE NAME
mysqld    19933 mysql   18u   REG   0,18          0 47695107 /mysql-tmp/ibxVdqUz (deleted)
mysqld    19933 mysql   19u   REG   0,18          0 47695108 /mysql-tmp/ibEEplTO (deleted)
mysqld    19933 mysql   20u   REG   0,18          0 47695109 /mysql-tmp/ibPdHgS3 (deleted)
mysqld    19933 mysql   21u   REG   0,18          0 47695111 /mysql-tmp/ibkwNxvB (deleted)
mysqld    19933 mysql   25u   REG   0,18          0 47695114 /mysql-tmp/ibElJ9YU (deleted)
mysqld    19933 mysql  457u   REG   0,18  134788214 47745791 /mysql-tmp/MLtIcWeR (deleted)
mysqld    19933 mysql  584u   REG   0,18  135912217 49131634 /mysql-tmp/ML28bohx (deleted)
bash      24048  root  cwd    DIR   0,18         80      896 /mysql-tmp
sh        25869 mysql   18u   REG   0,18          0 47695107 /mysql-tmp/ibxVdqUz (deleted)
sh        25869 mysql   19u   REG   0,18          0 47695108 /mysql-tmp/ibEEplTO (deleted)
sh        25869 mysql   20u   REG   0,18          0 47695109 /mysql-tmp/ibPdHgS3 (deleted)
sh        25869 mysql   21u   REG   0,18          0 47695111 /mysql-tmp/ibkwNxvB (deleted)
sh        25869 mysql   25u   REG   0,18          0 47695114 /mysql-tmp/ibElJ9YU (deleted)
sh        25869 mysql  321u   REG   0,18       8192 48945572 /mysql-tmp/#sql_4ddd_1.MAI (deleted)
sh        25869 mysql  414u   REG   0,18       8192 48945573 /mysql-tmp/#sql_4ddd_1.MAD (deleted)
sh        25869 mysql  457u   REG   0,18  134788214 47745791 /mysql-tmp/MLtIcWeR (deleted)
[...]

First question: Why does wsrep_slave_threads cause tmpdir to grow?

Second question: Why is the temporary space not freed occassionally? If the data is deleted, I assume that it is not needed for a later time. Especially ugly, because a full tmpdir is causing corrupt databases.


texts: How do I handle multiple rows with variables defined by a query?

texts:
I am trying to write a query that uses variables to feed into a sub query.

Query:

DECLARE @Code VARCHAR(5)
DECLARE @Sys INT
DECLARE @Key INT

SET @Key = 12345

SET @Code = (SELECT Code
FROM LOOKUP.Table1
WHERE Key = @Key)

SET @Sys = (SELECT DISTINCT System
FROM LOOKUP.Table1
WHERE Key = @Key)

SELECT
@Code AS Code,
(SELECT DISTINCT
        COUNT(ID)
    FROM [LINKED-SERVER].[DB].[SCHEMA].[Table_H]
    WHERE [Type] NOT LIKE 'X%'
        AND [Key] = @Key
        AND [Code] IN (@Code)
) AS Total,

(SELECT
        COUNT(Type)
    FROM [LINKED-SERVER].[DB].[SCHEMA].[Table_H]
    WHERE [Type] LIKE 'Y%'
        AND [Key] = @Key
        AND [Code] IN (@Code)
) AS TypeA,

(SELECT
        COUNT(Type)
    FROM [LINKED-SERVER].[DB].[SCHEMA].[Table_H]
    WHERE [Type] LIKE 'N%'
        AND [Key] = @Key
        AND [Code] IN (@Code)
) AS TypeB

The problem comes when [Key] is used for multiple values of [Code]:

  Msg 512, Level 16, State 1, Line 7
  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Some have as many as 5 values. I can't figure out how to make this query work when SET @Code would return more than 1 value.


texts: Using mysqldump to initialize slave database

texts:
I've facing an issue I don't really understand.

I wan't to synchronize a slave MariaDB database to a master one.

I try to use the mysqldump option --master-data=1 which is adding a "CHANGE MASTER TO" in the output file to set the bin log file and position for the slave. I'm assuming (as per the doc) than it should be enough for the slave to know where to start.
When I import the file to the slave and start slave, the slave is desynchronized

If I do a "FLUSH TABLES WITH READ LOCK;", SHOW MASTER STATUS etc then at the end manually do a "CHANGE MASTER TO", all if fine and synchronized

I would assume the --master-data=1 would generate the same bin log information as the manual way, but it is not.

Where am I going wrong ?


texts: Updating Properties of an Excel cell, with Sql statement

texts:
So far, i have constructed a SSIS Package that creates multiple Worksheets in an Excel Speadsheet and populates it with values (Reports).

Then I can Execute a SQL statement, via SSIS, to selectively update values in the Sheet:

"Update [Sheet1] Set F1 = 'TOTAL:' Where F1 like 'Total:%' "

A next step would be, to Bold the Cells that contains the text "TOTAL:", using SQL.

If possible, I do not want to write an entire macro or start OLE Automation and Install EXCEL on the Server, to just do this.

Is there a way (maybe a syntax change to an Update statement) to accomplish this quickly.

"Update [Sheet1] Set F1.Bold = 'True' Where F1 like 'Total:%' "

Would this be Possibe?


texts:
Suppose, I have a table foo, which contains some statistics that are computed every now and then. It is heavily used by other queries.

That's why I want to compute more recent statistics in foo_new and swap them when computation is ready.

I could do

ALTER TABLE foo RENAME foo_tmp;
ALTER TABLE foo_new RENAME foo;

but what happens if a query needs table foo inbetween those two lines when there is no table foo? I guess I have to lock it somehow... or is there another way to do it?


texts: How to get only those docs which has all the match fields in mongoose

texts:
Let say I have the following collection named survey:

{ _id: 1, results: [ { product: "abc", active: true}, { product: "xyz", active: true} ] }
{ _id: 2, results: [ { product: "abc", active: false }, { product: "xyz", active: true } ] }
{ _id: 3, results: [ { product: "abc", active: false }, { product: "xyz", active: false } ] }

by doing:

db.survey.find(
   { results: { $elemMatch: { active:  false } }
)

I will get all the docs where at least one object in the array is false i.e.

{ _id: 2, results: [ { product: "abc", active: false }, { product: "xyz", active: true } ] }

{ _id: 3, results: [ { product: "abc", active: false }, { product: "xyz", active: false } ] }

but how can I get only those docs which has all the key active: false
 i.e:

{ _id: 3, results: [ { product: "abc", active: false }, { product: "xyz", active: false } ] }

TIA..


texts: MySQL Syntax - Searching points within polygons using GeomFromText


texts:
I need to trace and monitor client login connections (successful and failure) to our important SQL servers (20 MSSQL Server). What method do you recommend?

Note: I think enabling "both failed and successful logins" to windows event logs may create large log files so in my mind it is last option. Also i think auditing may affect database performance so this is last option too.


texts:
I want to create queries to remove/extract redundant rows having similar versions. For example 13.0, 13.0.0, 13.0.0.0 are the same for SQL Server. So I want to delete the rows having less refined version of product - 13.0, 13.0.0 - and be left only with versions 13.0.0.0 and 13.0.0.275.

Is there any way to do this using some SQL query?

As versions 13.0, 13, 13.0.0 and 13.0.0.0 are the same in all sense... so I want to keep the most detailed version out of them, that is 13.0.0.0, and delete other versions. But version 13.0.0.725 and 13.0.0.0 are different so I want to keep this record as it is but for particular product say SQL Server.

The output that I want from the graphical example:

SQL Server with 13.0.0.0 version
SQL Server with 13.0.0.275 version
Visual Studio with 13.0.0.0 version
Visual Studio with 13.0.0.275 version

Another example is-- If there are versions like 2, 2.0, 2.1.0, then in this case, the record having version 2 and 2.0 are the same so I want to keep the more detailed version of same version and delete the rest. The record will show having version 2.0 and 2.1.0.

Is there any way to achieve the same using SQL queries?


texts:
I have been looking online for an answer but do not really know how to formulate correctly what I would like to achieve and whether it's possible, sorry if the question sounds dumb.

I have Site data with number of PC and AADTgroup 1,2 and 3 for each site. Data will remain sorted by ROWID and partitioned by AADTGroup.

My existing data is

I want the output with two more columns containing PCsum and NAF_Driver/Vehsum

where, I want the running total of PC in PCsum when it reaches to 5 and corresponding running total of NAF_Driver/Veh in NAF_Driver/Vehsum. Running total would start after reaching the limit 5 and with different AADTGroup.

The desired output is like below

I used SQL code

DECLARE @point INT=5
;
;WITH x(ROWID, AADT_Group, PC, R, PCsum) AS
(
SELECT
    ROWID, AADT_Group, PC, ROW_NUMBER() OVER (ORDER BY ROWID),
    SUM(PC) OVER (PARTITION BY AADT_Group ORDER BY [ROWID], ROWID RANGE UNBOUNDED PRECEDING)
  FROM dbo.CAUrban4LaneDiv$
)
SELECT x.ROWID, x.AADT_Group, x.PC, x.PCsum
FROM x WHERE x.PCsum < @point
ORDER BY x.ROWID;

But it did't give me the desired result
TIA


texts: Truncated Incorrect date value string with comma

texts:
I have problem in date columns to insert from varchar column to date column.
I used str_to_date and Date_Format functions but it doesn't solved. How Can I

Simple Example:

update Uye_Uye_Liste
set    DogumTarihi=str_to_date(DogumTarihi,'%d.%m.%Y')
where  DogumTarihi is not null;

  ERROR 1292 (22007): Truncated incorrect date value: '23.07.2002 00:00:00'

alter table Uye_Uye_Liste modify DogumTarihi datetime;

  ERROR 1292 (22007): Incorrect datetime value: '13-04-1996' for column 'DogumTarihi' at row 2

select DogumTarihi from Uye_Uye_Liste where DogumTarihi = '13.04.1996';

+-------------+
| DogumTarihi |
+-------------+
| 13-04-1996  |
+-------------+


texts: How do I properly design a many-to-many (charges/payments) accounting system?

texts:
This is just as much a db/programming question as an accounting/math question, so I'm actually posting to all relevant boards.  I appreciate your patience in advance.

Basically I'm having trouble wrapping my head around a payments ledger.  My system has three tables:  CHARGES, PAYMENTS and a PAYMENTS_TO_CHARGES.  The issue is that part of the system requirement calls for both the ability to directly associate a payment with a CHARGE as well as allow members to post overpayments (or credits) to their account.  So there's the need to track credits then apply them as needed to future CHARGES.

Perhaps I'm thinking about things from the wrong standpoint, but I'm picturing CHARGES as a DEBIT and PAYMENTS as a CREDIT.  However, it would appear that they can both have DEBIT/CREDIT depending on your accounting approach.

So, my question -- officially -- is what is the best way to go about tracking/reconciling payments, and charges, while still allowing for overpayments, etc.  I'm trying to work out the DB tables and business logic but every time I solve one problem, a reverse (or converse) item presents itself.  Do I allow the many-to-many relationship to track positive and negative numbers per transaction?  Do I create a fake universal charge that all credits apply to?  Do I create a completely separate CREDITS table?  As you can tell, I'm likely overthinking this, but I'm certainly befuddled.

If all transactions were guaranteed 1-to-1, it would obviously be much simpler.  Unfortunately they're not

See also:

https://softwareengineering.stackexchange.com/questions/72178/how-do-i-properly-design-a-many-to-many-charges-payments-accounting-system

and

https://stackoverflow.com/questions/5543025/need-to-reconcile-accounts-on-a-poorly-designed-system


texts: read-only permissions except specific stored procedures

texts: Right now I have two databases behind my asp.net web application.  I'm using a single login to access both databases.  the login has full ownership of one database and read-only rights to the second database.  I'd like to set it up so that specific stored procedures from my main database can do update/delete operations on the second database.  This way I can control the exact update/delete operations that are happening.  I've been having a hard time figuring this one out, any advice would be appreciated.


texts: MySQL - Why specific query doesn't use cache?

texts:
My customer is running MySQL 5.7.16 on Ubuntu16.04 as backend for Wordpress site.
A lot of heavy, bad written SQLs.
I was able to improve situation significant by enabling MySQL cache, slow query log is almost empty now.
But - certain query doesn't use cache and I cannot understand  why.
SQL uses two tables, which are pretty static, so it's not cache invalidation.
Any idea how I can debug that?

TIA, Vitaly

1) SHOW STATUS LIKE "qcache%";
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1700     |
| Qcache_free_memory      | 49555584 |
| Qcache_hits             | 17960465 |
| Qcache_inserts          | 6928571  |
| Qcache_lowmem_prunes    | 1850967  |
| Qcache_not_cached       | 924283   |
| Qcache_queries_in_cache | 13303    |
| Qcache_total_blocks     | 28441    |
+-------------------------+----------+

2)Example of SQL:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID
FROM wp_posts
  LEFT JOIN wp_postmeta
    ON ( wp_posts.ID = wp_postmeta.post_id )
  LEFT JOIN wp_postmeta AS mt1
    ON (wp_posts.ID = mt1.post_id
    AND mt1.meta_key = '_pagefrog_fbia_status' )
WHERE 1=1
  AND (     ( wp_postmeta.meta_key = '_pagefrog_fbia_status'
          AND wp_postmeta.meta_value = '1' )
       OR     mt1.post_id IS NULL
       OR   ( wp_postmeta.meta_key = '_pagefrog_fbia_status'
          AND wp_postmeta.meta_value = '' ) )
  AND wp_posts.post_type = 'post'
  AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 10

Addenda

SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 83886080 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+-----------------------------+----------+


texts:
The Neo4J site states that the community version of Neo4J has a limit in the graph size, but does not state the limit of the community version.

https://neo4j.com/editions/

What is the size limit of the community version?


texts:
I am currently trying to figure out why the site I am working on (Laravel 4.2 framework) is really slow at times, and I think it has to do with my database setup. I am not a pro at all so I would assume that where the problem is

My sessions table has roughly 2.2 million records in it, when I run show processlist all the queries that take the longest relate to that table.

Here is an example screen shot:

Table structure:

Surely I am doing something wrong or it's not indexed properly?
I'm not sure as I'm not fantastic with databases.


texts: Prevent users deleting more than a certain number of rows in MySQL

texts:
The problem:

I have a table with rows, nothing special about it
When a database user removes a row, it should increment a number
A database user is only allowed to remove 3 rows max in the table
A database administrator should be able to add more "remove tokens" to a database user

I know I should have a BEFORE DELETE trigger to see if the user can remove a row, and an AFTER DELETE trigger to increase the number of rows deleted by the user.

How should I store the info about how many rows a database user has removed from that specific table, and how do I allow a database admin to add more "remove tokens" to the user.

I thought of creating a table to store this, but I wonder if there are different ways to do it without a table? This might sound like a weird question, but I'm just trying to figure out how more advanced stuff works.


texts: Cannot create MySQL database with name '5e370227_db'

texts:
Why am I getting error when I try to create database with name '5e370227_db'?

I am getting the following error:

mysql> create database 5e370227_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '5e370227_db' at line 1

When I try to create a database with name '5a370227_db' (e -> a) it is created successfully:

mysql> create database 5a370227_db;
Query OK, 1 row affected (0.00 sec)

Details:

MYSQL: mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
Linux Centos 2.6.32-358.el6.x86_64
logged as root
database admin


texts: Facing error while trying to run dynamic pl/sql

texts:
Hope you're doing well
I'm new to PL/SQL and I'm trying to write a dynamic PL/SQL.
What I've written so far is :

declare

   sql_stmt     varchar2(100);
   v_student_id number := 1;
   v_new_stname varchar2(50);

begin

  sql_stmt := 'update student
               set student_name = ''pantea'' where student_id :1 ' ||
              'returning student_name into :2';
  execute immediate sql_stmt
  using v_student_id
  returning into v_new_stname;

     dbms_output.put_line('New student name is :' || v_new_stname);

end;

when I want to run the code I see this error :

ORA-06502:Numeric or value error:Character string buffer too small.

I'm 80 percent sure that the problem might be in this part of the code

set student_name = ''pantea''

but I don't know how to solve it and I could not also find it on oracle books
I know how to handle this in T/SQL but have no idea how to handle this in PL/SQL.


texts: How to pass application user names to the database server for audit purposes?

texts:
Writing an application that will require a user to login.
I would like to use a table to store my user information; however, the user's ID will not be available to me when I create audits for all my tables.
Conversely, adding the user to the database itself will give me what I need but it's going to be A LOT of users.
What is the best practice here?  I need to be able to create table audits with a column that holds the id of the user that changed it.


texts:
I am building a table that will store 'task records' i.e. who approved a task for a group and period.

I have normalized the table but I am not sure if I have gone beyond 3NF, and if this is overkill?

I have created 6 related tables each represented by a FK in the TaskRecord table. The Model.Period table just contains a date column...

CREATE TABLE [Model].[Period]
(
   [PeriodID] [int] NOT NULL IDENTITY(1,1),
   [Period] [date] NOT NULL
   CONSTRAINT [PK_Period_PeriodID] PRIMARY KEY CLUSTERED([PeriodID] ASC)
);

Is this really necessary or should I store the date value in the TaskRecord table?

Here is the TaskRecord tbl...

CREATE TABLE [Model].[TaskRecord]
(
    [TaskRecordID] [int] NOT NULL IDENTITY(1,1),
    [GroupID] [int] NOT NULL,
    [PeriodID] [int] NOT NULL,
    [TaskID] [int] NOT NULL,
    [StatusFlagID] [int] NOT NULL,
    [ApprovalLevelID] [int] NULL,
    [ApprovedByID] [int] NULL,
    [ApprovedDateTime] [datetimeoffset](0) NULL,
    CONSTRAINT [PK_TaskRecord_TaskRecordID] PRIMARY KEY CLUSTERED  ([TaskRecordID] ASC),
    CONSTRAINT [FK_TaskRecord_GroupID] FOREIGN KEY(GroupID) REFERENCES [Model].[Group]([GroupID]),
    CONSTRAINT [FK_TaskRecord_PeriodID] FOREIGN KEY(PeriodID) REFERENCES [Model].[Period]([PeriodID]),
    CONSTRAINT [FK_TaskRecord_TaskID] FOREIGN KEY(TaskID) REFERENCES [Model].[Task]([TaskID]),
    CONSTRAINT [FX_TaskRecord_StatusFlagID] FOREIGN KEY([StatusFlagID]) REFERENCES [Model].[StatusFlag]([StatusFlagID]),
    CONSTRAINT [FK_TaskRecord_ApprovalLevelID] FOREIGN KEY(ApprovalLevelID) REFERENCES [Model].[ApprovalLevel]([ApprovalLevelID]),
    CONSTRAINT [FK_TaskRecord_ApprovedByID] FOREIGN KEY(ApprovedByID) REFERENCES [Model].[UserDetail]([UserDetailID])
);


texts:
Our production database is hosted by another company on their domain.  I'm able to RDP into the box and login with the credentials they provided me but when I try to login from my local computer on my local domain using SSMS SSMS forces my local credentials.  I've tried but can find no way to change these credentials using the SSMS front end.

I attemtped using a shortcut with a target of

C:\...\runas.exe /netonly /user:DOMAIN\USERNAME "C:\...\ssms.exe"

But when SSMS comes up it appears to still be trying to use my local credentials and does not allow me to connect.  Is there something I'm missing.  How do I tell SSMS that its connecting to a different domain account?


texts: How to remove XFS filesystem warning in mongodb shell?

texts:
Every time I start a mongodb shell, I get this annoying warning:

Server has startup warnings:
2020-02-01T08:54:27.220+0100 I  STORAGE  [initandlisten]
2020-02-01T08:54:27.220+0100 I  STORAGE  [initandlisten] ** WARNING: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine
2020-02-01T08:54:27.220+0100 I  STORAGE  [initandlisten] **          See http://dochub.mongodb.org/core/prodnotes-filesystem

I understand that in some environments, this is useful. But in my particular case, I'm using mongodb on a VPS server that has pre-installed ext4 filesystem. There is no option to change that. And it is not needed anyway - in this particular application, there are no performance problems and there will never be. The server is already overpowered for this application.

I was going through the documentation to find a command line option for suppressing this warning message, but there is none. Or maybe there is, but I couldn't find it?


texts:
We have a Windows 2008 r2 server (on hyper v) and on this we are running a SQL server. This database is approx 300 gb big. The server has 32 GB ram. In the SQL Server properties I saw the following:

I just wondered is this OK or is this too much memory. According to www.sqlservercentral.com/blogs/glennberry/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008/
I read that a server with 32 GB needs max  29000

Can someone advise. Thanks


texts:
One of our user from another domain WATSON received the following error message:

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'WATSON\User1'.

when he is trying to access one of our web application that retrieve information from a database.

The web application and the database that the web application use is located in a Windows Server 2008 R2. The machine name is Jack11 and has join the domain WATSON. The web application is hosted in the IIS wile the database is hosted in the SQL Server 2008 Enterprise.

Do I need to setup my SQL Server 2008 Enterprise in order to allow domain user such as in this case WATSON\User1 (not local user) to access web application that uses any of the database inside the SQL Server 2008?

(Kindly note that there might be more than 50 domain users that may required to access the web application and therefore it would be inefficient to add each domain user to the database's user rights)


texts: Oracle not available error with Oracle XE 11.2.0-1.0

texts:
I have downloaded Oracle XE 11.2.0-1.0 and installed it on a CentOS 6.3 virtual machine with 1.5GB memory and 3GB of swap.

SYS and SYSTEM set to have password 123456. I have set ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe/ and ORACLE_SID=XE.

I can connect to this database with SQL Developer with the SYSTEM user. But when I want to connect with sqlplus it says:

ERROR.
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0

ps -fe | grep pmon shows this:

oracle    1164    1    0    11:26    ?    00:00:00    xe_pmon_XE

I have logged in as root user and I just type sqlplus with no arguments then it asks for user name and password.

Can anyone help?


texts: Collation conflict when running dynamic sql FOR XML PATH

texts:
I am getting the following error message when running the query below:

  Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_BIN" in the equal to operation

This query works fine and basically gives me the scripts to CREATE USER in my current database.

In order to run the query in ALL (or a selection) of my databases in the current server, I have it done through dynamic SQL and for XML PATH.

The problem is,
when I uncomment the XML part of the script below, I get the error above.

It is not in ALL servers that I get this error. Some servers work fine, others are bogged.

I want this query to work in ALL servers, independently of server collations.

How can I achieve this?

DECLARE @sql NVARCHAR(MAX)
DECLARE @ParamDefinition NVARCHAR(MAX)

    DECLARE  @log NVARCHAR(MAX)
            ,@vCrlf CHAR(2);

    SELECT  @log = ''
           ,@vCrlf = CHAR(13)+CHAR(10);

SELECT @SQL = '
--==========================================
-- creating the users
--==========================================

select  db = db_name(),
         _login=[master].[sys].[server_principals].[name],
         role_= null,
         obj=null,
        Permission= null,
        [script]=
''CREATE USER [''
 + [sys].[database_principals].[name] + '']'' + '' FOR LOGIN ['' + [master].[sys].[server_principals].[name] + '']''

from [sys].[database_principals] INNER JOIN [master].[sys].[server_principals]
on [sys].[database_principals].[name]=[master].[sys].[server_principals].[name]
where [master].[sys].[server_principals].[type] in (''U'', ''G'', ''S'')

'

--SET @SQL = (
--          SELECT STUFF(
--  (SELECT N'  ' + ' USE ' + QUOTENAME(name) +';' + @vCrlf + @SQL + @vCrlf
--                              FROM sys.databases SD
--                              --INNER JOIN @DBS D ON SD.NAME = D.DB
--                              WHERE SD.STATE_DESC = 'ONLINE' -->Skips the database if it is not online
--                                AND SD.COMPATIBILITY_LEVEL > 80

--    FOR XML PATH(''),TYPE)
--  .value('text()[1]','nvarchar(max)'),1,2,N'')
--)

EXECUTE MASTER.DBO.sp_executesql @SQL

HERE IS THE WORKING VERSION

DECLARE @sql NVARCHAR(MAX)
DECLARE @ParamDefinition NVARCHAR(MAX)

    DECLARE  @log NVARCHAR(MAX)
            ,@vCrlf CHAR(2);

    SELECT  @log = ''
           ,@vCrlf = CHAR(13)+CHAR(10);

SELECT @SQL = '
--==========================================
-- creating the users
--==========================================

select  db = db_name(),
         _login=[master].[sys].[server_principals].[name],
         role_= null,
         obj=null,
        Permission= null,
        [script]=
''CREATE USER [''
 + [sys].[database_principals].[name] + '']'' + '' FOR LOGIN ['' COLLATE Latin1_General_CI_AS +  [master].[sys].[server_principals].[name] + '']''

from [sys].[database_principals] INNER JOIN [master].[sys].[server_principals]
on [sys].[database_principals].[name] COLLATE Latin1_General_CI_AS = [master].[sys].[server_principals].[name]
where [master].[sys].[server_principals].[type] in (''U'', ''G'', ''S'')

'

SET @SQL = (
            SELECT STUFF(
  (SELECT N'  ' + ' USE ' + QUOTENAME(name) +';' + @vCrlf + @SQL + @vCrlf
                                FROM sys.databases SD
                                --INNER JOIN @DBS D ON SD.NAME = D.DB
                                WHERE SD.STATE_DESC = 'ONLINE' -->Skips the database if it is not online
                                  AND SD.COMPATIBILITY_LEVEL > 80

      FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')
)

EXECUTE MASTER.DBO.sp_executesql @SQL


texts: MongoDB: queue vs. realtime write/update

texts:
On a large mongoDB I have 2 different use-cases for reading and 2 different types for write/update.

read 1: find by index -> no question on this. this works like a charm - as long as the index fits in the memory/ram.
read 2: read line by line, like a batch script which is reading a log-file one by one.
write 1: insert - append a new dataset or document.
write 2: update data, directly addresses or found by index.

Now I wonder if mongoDB is able to "priorize" different write-tasks. I have one collection which does not require the data to be written and available instantly in "real-time". It's enough to use a slow queque and process these writing tasks without hurry.
However, there are also write statements that should be processed asap and be available with a minimum delay (if any).

Does mongoDB allow me to differ between high (asap) and low prio (queque) writing/updating?


texts: Backup VLDB replicated database in SQL Server?

texts:
I have a problem with my backup strategy.

Currently I'm doing a Full Backup every night and transaction log backup every 2 hours to TAPE.

This used to work without problems, but the size of the DB is almost 2TB and the Full Backup takes 8 to 12 hours to complete overnight.

During the night I have several jobs running that are affected by the backup.

I was thinking of a different strategy using DB replication that would let me keep 2 databases synced and then only backup the "replicated" database, leaving the production database without an online backup.

Could this be a problem? Do I have any risk of losing information? What do I do in this case if I need to restore a backup?


texts: Insert top 253 values where value in select distinct tradernames

texts:
I am trying to insert top 253 values for each trader from a temp traders table into temp results table.

I am using following code to do it.

CREATE TABLE #trad ( Trad varchar(50))
IF @trad is not null
INSERT INTO #Trad
SELECT DISTINCT * FROM dbo.Split(@trad,',') -- split will have traders seperated by commas
ELSE INSERT INTO #Traders
Select distinct Trader from TraderMap tm
where traderorgroup = 'trader'

CREATE TABLE #result
(port VARCHAR(50),portdate DATE,P MONEY,V_95 MONEY,
V_99 MONEY,VLimit MONEY,V_90 MONEY,H_99 MONEY,
H_975 MONEY,H_95 MONEY,H_90 MONEY,H_80 MONEY,dCnt
INT,Trad varchar(100))

DECLARE @traderlist AS Varchar(100)
DECLARE traderlist CURSOR FOR
SELECT DISTINCT Traders FROM #traders
OPEN traderlist
FETCH next FROM traderlist INTO @traderlist
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #result (port, portdate, P, V_95, V_99, VLimit,
dCnt,Trad)
SELECT  TOP 253 pl.Port,pl.Portdate,pl.dtd_P
PL,var.V_95,var.V_99, sum(gu.Guid * -1) VLimit,
Row_number() OVER(partition by pl.TradName ORDER BY Portdate DESC)
dCnt,pl.TradName
FROM Trad_PL pl INNER JOIN rep_tab var
ON pl.Portdate = var.V_Date
AND pl.Port = var.Port
AND pl.TradName = var.Trad
INNER JOIN Trad_Guid gui
ON pl.Port = gui.Port
AND pl.TradName = gui.Trad
WHERE pl.Portdate <= @portdate
AND pl.Port = CASE WHEN @port = 'Her' THEN 'Herport' ELSE
@port END
AND pl.ONA = 'Act'
AND pl.TradName = @tradlist
AND var.PType = '0Trad'
group by
pl.TradName,pl.Port,pl.Portdate,pl.dtd_P,var.V_95,var.V_99
ORDER BY Portdate DESC
FETCH next FROM tradlist INTO @tradlist
END
CLOSE tradlist
DEALLOCATE tradlist

This code runs for hours. Is there any efficient way to get top 253 rows for each trad from Trad_PL and rep_tab.


texts: Using EXECUTE IMMEDIATE inside PL\SQL Block

texts:
Using EXECUTE IMMEDIATE inside PL\SQL block makes the whole block commit immediately.

begin

INSERT INTO Customer ( GUID, STATUS, NAME) VALUES (1,1,'xx');

EXECUTE IMMEDIATE 'CREATE TABLE Shop
(
  GUID         NUMBER(16),
  STATUS       NUMBER(1),
  NAME         VARCHAR2(50 BYTE),
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING';

DBMS_OUTPUT.PUT_LINE('DONE:');

EXCEPTION  -- exception handlers begin
  WHEN OTHERS THEN  -- handles all other errors
  DBMS_OUTPUT.PUT_LINE('Error occured, rollback...');

 ROLLBACK;
end;

As you realized I do not even use COMMIT. About above code,

"Insert into" statement works, but "create table" statement throws exception because there is already a table with the same name in the database.

Both I did not have any commit statement and code block fell exception and rolled back when I looked at the database I saw that insert had worked and there was a new row. It was expected that it should not have been there because there is no commit and also rollback worked..

How can I make rollback when exception occurs.


texts: Better option to store latitude and longitude on Postgres

texts:
I'm adding a GIS module to my Proyect, then I need o store latitude and longitude into my Postgres Database(table target has 500,000 registers by now).
For example:
latitude = 40.767504
langitude = -73.974716

I'm not sure what DataType would be better(performance) to use: decimal with 6 decimal points, String or another.
Thank you in advance.


texts: Get distinct category columns with multiple subcategory columns mapped in table

texts:
I have 2 tables, category and subcategory, this query

select C.Description,s.Description
from category C
inner join SubCategory S on C.CategoryID =S.CategoryID
where  C.IsActive=1 and S.IsActive=1

gives the following output:

But my output has to be as follows:


texts: Schema: Table with many rows vs single table with many columns

texts:
I'm torn between two different schemas and would love some of your thoughts.

Essentially, we have a list of items, and each item can have a bunch of extra boolean attributes, currently about 15 however this is very likely to grow in the future probably up to 50 and perhaps more, it would probably change periodically with new columns being added.

We currently have implemented a separate table, where each row belongs to an "item" in the other table. Each row in this table represents an attribute and has an "attribute name", and "flagged" columns to say if the attribute was flagged. This means for every item created, approx 15 rows in this table are created.

The other option is to create extra columns on the first table of items and store the boolean attributes there, however this could result in quite a lot of columns.

What would be the best way forward?


texts: Why is Primary Key not present at the end of Unique Key in MySQL?

texts:
I have a table with following definition.

CREATE TABLE `test` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  `e` int(11) DEFAULT NULL,
  `f` int(11) DEFAULT NULL,
  `g` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `h` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `b_UNIQUE` (`b`),
  KEY `single` (`c`),
  KEY `double` (`d`,`e`),
  KEY `triple` (`f`,`g`,`h`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

For some queries when I check optimizer trace I notice key parts for indices.

For example:

{
  "index": "single",
  "usable": true,
  "key_parts": [
    "c",
    "a"
  ]
},
{
  "index": "double",
  "usable": true,
  "key_parts": [
    "d",
    "e",
    "a"
  ]
}

Notice for indices single and double the key part contains the Primary Key a at the end. That is what InnoDB says. So thats good.

{
  "index": "b_UNIQUE",
  "usable": true,
  "key_parts": [
    "b"
  ]
}

But as seen in above snippet, the b_Unique key does not contain primary key a as the final key part. Is it really not present and pointer to record is present? What implications does this have with respect to query optimization and Page reorganisation?

EDIT 1: It seems to be present in MySQL 8.0 version. It is highly likely to be present in MySQL version 5.7 as well. So require a way to check its presence in this version.


texts: Importing external data with different field format

texts:
I have the source file in Excel as follows:

  Item-------Reference---Description
  -400020--20045-------Ruler 60cms
  8900032--20012-------Eraser 45gr

Notes:
Because I can add many spaces here, the dash"-" denotes a space on the original file
The Item 400020 comes with a space before the 4 from the source.

The following file is the expected with the proper format to be exported to our DB

  Item--------Reference---Description
  0400020--0020045-----Ruler
  60cms    8900032--0020012-----Eraser 45gr


texts: How can I automate sharding in the presence of continuous reads

texts:
I am using a pymongo in a sharded mongo environment.

I have some fairly write-intensive systems, so I split the data up by time, with one database per hour, so the heavy-lifting of index-updates can happen in-memory, and to give us more flexibility to make performance-related tradeoffs.

I have a number of processes writing to the latest hour's database, and another handful of processes reading from the last n hours of data. To keep things simple, I would like these processes to remain independent. (I do not want to have to synchronize them).

The hourly databases are sharded on creation, so that reads and writes are distributed across the cluster. Unfortunately, the (independent) read processes fall over when they try to read from the latest db whilst the sharding operation is still incomplete.

As I see it, I have a small handful of options available to me:

I can make the read process tolerant to errors, so that when it encounters a failure due to in-progress sharding operations, it can carry on regardless. (This seems the neatest and most pythonic option to me, but runs the risk that the throwing/catching process might lead to large delays).
I can make the read process ignore the most recent database until it is at least 10 minutes old. This will give the sharding operation some time to complete. I do not really like this option because of the involvement of a seemingly arbitrary time interval, making it feel less deterministic than I would like. (Perhaps more acceptable, I could pre-create a few hours worth of databases, and ensure that they are sharded & fully configured well in advance)
I can make the write process create each hourly database by calling the copyDatabase command on a pre-sharded empty database. This approach presumes that the sharding state will be copied with the rest of the database, and that the in-progress copy operation will not be vulnerable to the same (or similar) errors as the in-progress sharding operation was.
I can attempt to synchronise the reads and the writes by maintaining a record of the most-recently-created database, populated when the database is fully ready to be written to. I do not like this approach because it couples the read & write processes together, and introduces a level of complexity that I think I can avoid.

Are there any options that I have missed?

Right now, option 2b seems the most attractive.


texts: one table for all blob with a one to one table for metadata which link to one or many tables?

texts:
I'm going to start by saying that I'm not a DBA but i got that role in my current project so I'm doing my best.

The application must be able to store files(attachments, official
documents, etc)
The application is using entity framework 6 (EF6) to query the
database.
Sql server that I'm using (2012) doesn't have the
FileStream/FileTables functionality activated and I have to assume
it will never be activated.
I don't have access to a fileserver.

I'm not the only one that could change the DAL in the application and I know that if you are not careful with how you write the query with EF6, it's by default select * from so while doing the database I made the choice, to be safe, of creating one table for all blob with a identity as primary key and a one to one mapping to another table which contain the metadata (filename, uploaded time, uploaded by, etc).

A file can have relation to many others tables(dataset) and a dataset can have multiple files.

I see two way of design, I'm showing 2 datasets here(notice and noticemaster) but I could have more;

Option 1

Option 2

My question is; which one is a proper implementation? is there an option 3?

Bonus question; did i made a good choice to have 1 table for all files and 1 table for all metadata?


texts:
Is there a SQL Server implementation of the Longest Common Substring problem? A solution that checks with all rows of a column in SQL Server? I have seen solutions that take two strings as input, but no SQL Server solution that looks at all rows of a column in a table.

I did try a few things, but to be honest I think a solution goes over my head at the moment, so any suggestions are welcome.

There is no "real world" problem here, I'm just looking at programming problems and how they could be solved with SQL Server.


texts: Query Plan showing missing index warning on trivial statements

texts:
I have a Stored Procedure (ReportAdvancedUP) which runs fast (~1sec) in MSSMS but takes a long time (~1.8mins) via our Web Application.  I am aware that this usually comes down to bad choice of Execution Plan so I've had a look at both examples.

Running in MSSQS the Actual Execution plan comprises of four Clustered Index Seeks joined by nested loops.  No problems.

I then use the following query to pull out the cached execution plans used by my Stored Procedure (ReportAdvancedUP):

SELECT
    OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
    cp.usecounts AS ExecutionCount,
    st.TEXT AS QueryText,
    qp.query_plan AS QueryPlan
FROM
    sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE
    cp.objtype = 'Proc'
    AND OBJECT_NAME(st.objectid,st.dbid) = 'ReportAdvancedUP';

I get a QueryPlan that is not only much larger but is also reporting "Missing Index (CREATE NONCLUSTERED INDEX)" entries on queries as trivial as SET NOCOUNT ON; and IF(@ToDate IS NOT NULL)!!!

Furthermore the index it is saying is missing doesn't make sense in the context of the procedure in general... e.g.

I have a table called OrganisationLocations with primary key is ID int IDENTITY(1,1) and a foreign key OrganisationID int NOT NULL.
The table is clustered as Organisation, ID as they are always used together and it is better to group entries by Organisation than by ID
I nearly always join to the OrganisationLocations without providing BOTH the ID and OrganisationID, both from a security perspective and also for optimisation.

However, the index it keeps telling me is missing is:
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[OrganisationLocations] ([OrganisationID])

Can anyone suggest what might be going on here, regarding [a] the difference in execution plans, [b] Missing Index warnings on trivial code and [c] why it might want to add an index by OrganisationID alone.

Also, please advise what additional information might be helpful.

Thanks,
Chris


texts: MySQL - Call a View inside a Stored Procedure

texts:
Let's say, I have a table as follows
tbl_1
id             name                 status               usertype                         flag
================================================================================================
1              Name 1                  1                    2                               0
2              Name 2                  1                    3                               0
3              Name 3                  1                    1                               0
4              Name 4                  1                    4                               0
5              Name 5                  1                    5                               0

HTML PAGE
<select id="type" name="type">

  <option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>

</select>

My requirement is that I need to get all the data from table where the usertype is selected from the html page by the user (could be 1, 2, 3,4,5, etc.)
I want to create a view, and call that inside stored procedure
I cannot pass an argument inside of a View. So, I was told to create a stored procedure, and call the view inside it. The problem is, I don't know how to do it.


texts: Transfer all tables from a schema to other schema

texts:
I have two schema. Schema1 and Schema2

How would I transfer all the tables from schema1 to schema2?


texts: SQL Server - How to restore the database except specific tables

texts:
Can someone provide your insights, what is best option to restore the database from SQL Server (say prod to integration). I want to exclude specific tables during restore.

Using Script
Using SSIS
Using 3rd Party Tools.

If am using SSIS Integration Instance, does it involve licensing cost ? Some thoughts on this would help.


texts: Unable to access SQL Server from SSMS 2014

texts: I'm unable to access the server after I installed Visual Studio Isolated Shell. No matter how I try to access the server, the result will always be the same (image below). How can I access it back without reinstalling it or removing the shell?


texts: Occasional Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error

texts:
I know that this issue has been discussed elsewhere and I already read it. Our problem is that this occurs only once a day (roughly), the rest of time everything works fine.

SERVER RS

Windows Server 2008 R2 STD
Microsoft SQL Server Standard Edition (64-bit) 10.0.6241.0
SQL Server runnuing under SQLSVC domain account
Computer account in AD delegation tab has set Do not trust this computer for delegation
It has linked server to DB configured using logins current security context.

SERVER DB

Windows Server 2008 Ent
Microsoft SQL Server Enterprise Edition (64-bit) 9.00.5000.00
SQL Server runnuing under SQLSVC domain account
IP: xx.yy.1.30
Computer account in AD delegation tab has set Trust this computer for delegation to any service (Kerberos only)
It has linked server configured to RS using logins current security context (no problem there).

On RS server there is a job which runs many steps one of which is procedure on DB server.

Roughly once a day, we have this error.

The error is only logged in RS server as

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access
validation failed with an infrastructure error. Check for previous
errors. [CLIENT: xx.yy.1.30] Date       31.05.2017 16:09:02 Log     SQL
Server (Current - 31.05.2017 16:09:00)

and second Source       Logon

Message Error: 18456, Severity: 14, State: 11.

SPNS registred for SQLSVC account is as follows

MSSQLSvc/RS.domain.local:1433
MSSQLSvc/RS.domain.local
MSSQLSvc/DB.domain.local:1433
MSSQLSvc/DB:1433

Delegation on SQLSVC account (in AD) is as follows
Trust this user for delegation to specified services only - Use Kerberos only

MSSQLSvc DB.domain.local 1433
MSSQLSvc RS.domain.local
MSSQLSvc RS.domain.local 1433

** here is missing the DB.domain.local (wo port) but in this scenario we are not using double hop.

When the error happens, there is no word in system logs, only SQL log on RS server.

I went and enabled logging Kerberos messages on RS server, with no log information in the moment of error.

I also asked infrastructure guys for help, they say there is also no error logged on AD controllers in timeframe of error.

I would realy apriciated any suggestion on what to do next, because the error happens randomly roughly once a day.
Also if you can comment on settings concerning delegation tab in AD computer account (I cant make changes to AD so any change must be justified).

Thank you.


texts:
I have discovered that when I run a CTE in a query window it must have the leading ; but if I am going to run a CTE in a stored procedure it does not need the ;

To me, it would be needed in both places or neither place.  Why is this punctuation need in one area and not the other?


texts: How to analyze compression options for Azure SQL Database?

texts:
What is the most efficient way to determine whether row or page or columnstore compression (or none) would be most effective for each index in an Azure SQL Database (standard or premium tier) used for OLTP, where sp_estimate_data_compression_savings is not supported?

I realize that whether doing so is good for performance would depend on the frequency and volume of writes for each index. What other considerations should be taken into account?

(I realize that whether to use columnstore for a partitioned index is a complex issue and I'll consider that out of scope for this request.)


texts: Oracle impdp - Importing file from remote server

texts:
Am I missing something or is it not possible to import a dump (.dmp) file using impdp to a database on another server other than where it was created?  All of my investigations lead me to believe that this cannot be done...that the dump file needs to reside locally on the server where the data is to be imported or NFS mounted so it appears to be local.  This seems to be a capability that the old "exp/imp" utilities used to have, but no longer exist.  I know you can move data using impdp and the REMOTE_LINK option but in order to use this, the data must physically reside in a schema within the database instance on the remote side for it to be copied.  It can't reside in a dump/exported file.

For example.  I have Server "A" and Server "B".  Each of them with an Oracle instance on it.  On "A", I perform an export using expdp of schema "TESTDATA" to a dumpfile named "testdata.dmp" where it is store on "A".  At some point in the future I would like to restore the contents of the "TESTDATA" dump file (testdata.dmp) to a new schema (TESTDATA2) on server "B".

At this point, is it true that my only options are to:

Copy testdata.dmp to server "B" and perform an import directly on server "B"
NFS mount the directory containing testdata.dmp on server "A" from server "B" so the dmp file appears local to server "B" and then perform the import.
Create a temporary schema (TMPSCHEMA) on server "A", import the test.dmp file to the temporary schema using the REMAP_SCHEMA option in impdp, and then perform an impdp on server "B" using the REMOTE_LINK option pulling the data from TMPSCHEMA on "A"

Either I'm missing something here, or Oracle left a huge gap in functionality between impdp and imp.


texts: Is it better to have one instance of a continuously running agent job or to fire off the job multiple times throughout the day?

texts:
I have a SQL query I need to run every second for about 12 hours a day.
It's a pretty simple query that logs some data to an isolated table.
Right now I've been leaving it running in my local SSMS client inside a WHILE loop and I use WAITFOR DELAY '00:00:01' to pause between each iteration for 1 second.

If I want to move this into an agent job, is it better to schedule the job to run every 1 second over those 12 hours, or keep the query as is and have the job fire once in the morning, so that the query manages how often it runs.
(One thing to keep in mind, is usually the query is fast enough such that it's runtime is sub-second, but rarely it can take a few seconds to finish executing.)

I guess the root question is, is it better to have a job that is continuously running for 12 hours, or a job that runs about 43,000 times (once per second for 12 hours)?


texts: Stale Statistics and View performance on SQL Server 2016

texts:
I have a report query which behaves abnormally, but rebuilding the indexes/refreshing statistics seems to sort it momentarily.
So my doubt essentially is -

What could be the reason that even with Auto Update Statistics enabled and level 130, the statistics are stale?

Is it advisable (or a good idea) to have a utility job every night, post nightly ETL batch completion to update the statistics of all tables?

.
SELECT  dim_isc.<cols>,
        dim_cr.<cols>,
        vw_cs.<cols>,
        --- **problem is here, guess** ---
        isnull(vw_cs.col_1,'  '),
        isnull(vw_cs.col_2,'  '),
        isnull(vw_cs.col_3,'  '),
        isnull(vw_cs.col_4,'  '),
        isnull(vw_cs.col_5,'  ')
FROM    dim_isc
        left join dim_ab on (dim_isc = dim_ab)
        join fact_cc on (fact_cc.cola = dim_isc.cola)
        join dim_cr on (fact_cc.colz = dim_cr.colz)
        join (
            select <cols> from dim_m join fcc
        ) rp on (fcs.coly = rp.coly)
        join vw_cs on (vs_cs.colx = dim_isc.colx)
WHERE
        rp = yyy -- input 1
        and dim_isc >= xxxx -- input 2

Few things to note, might help you advising further -

Removing the ISNULL from the SELECT columns helps resolve the query slowness. (I understand this could be stale statistics in the underlying view table(s) or column(s), resulting in a bad plan.)

vw_cs is a nested view with 4 levels i.e. the base table lies in the 4th view.

Updating statistics (fullscan) of the table from which the columns (vw_cs.col_1, col_2, col_3, col_4 col_5) are sourced, resolved the query slowness.

This table gets populated by nightly ETL jobs, as truncate & full load.

Any thoughts please?
(Apologies for not providing the execution plan, reasons - confidentiality with object names and was too huge to be exported. Happy to share information like scans methods chosen, cardinality, etc. Let me know.)
Database config -

Microsoft SQL Server 2016 (SP2-CU15-GDR) (KB4583461) - 13.0.5865.1 (X64)   Oct 31 2020 02:43:57   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0  (Build 14393: ) (Hypervisor)

Thanks
Ranit


texts: Does database systems use basic system calls of local file system?

texts: Suppose database system inserts a row in table, will it use write() system call of host file system to write that particular row to table?


texts:
Scenario

Debugging a failed transaction, I asked the DBA to run a trace on SQL Server. Eventually he send me a .trc file.

Issue

I use the fn_tracegettable() function to access the trace:

select * from sys.fn_trace_gettable('C:\path\to\myTraceFile.trc', default)

But I get the following error:

  Msg 567, Level 16, State 7, Line 1
  File 'C:\path\to\myTraceFile.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file.

Facts

The SQL Management Studio I am executing the fn_tracegettable from runs in my local machine.
The .trc file is in my local machine.
Both the server and the local machine run a SQL Server 2008 R2.


texts: What is the correct term to describe a single "data point" in a database?

texts:
I'm not sure if this question is appropriate, but anyway here goes.

I want to communicate the number of "data points" in my database. That is every single value, including more generic values such as date_created, date_modified, id, etc. (e.g. sum(table_row_count*number_columns) )

Currently, I have been using the word "data point". However, this can sometimes lead to confusion as often people associate "data point" to a unique value representing some kind of measurement. For example if I am storing weather data, and I said my database has 100 million data points, then some people might assume that I have collected 100 million unique measurements of weather.

Is there a better term instead of "data points"?


texts:
I need to export and import  data from a Oracle 9i to Oracle 12.2 database.

This old app doesn't log in as one privileged user - it logs in for each user.  I have hundreds of users with nothing but synonyms.

I need to pre-create the users and their privileges so I can import.

There are scripts all over the internet:
* Ask Tom suggests import and an indexfile (which I find kludgy).
* DBMS_METADATA.GET_DDL scripts, as well.

However, being in a rush, I haven't found one script to cycle through all the users and generate their DDL and grants, etc.

I want to run one 9i script to grab everything and one 12.2 script to create users, but can't find anything useful.

Can you suggest how I should solve this? Or am I making this too complicated?


texts:
I am running a number of queries in psql using for a time interval where a part of the query goes like

 where g2.datetime between g1.datetime+'19 minutes' and g1.datetime+'21 minutes'

i.e. I want an interval around 20 minutes after g1.datetime

If I want to do this several times, both to make it easier and less error prone (I am also refering to 20 minutes a couple of times in the query). I intended to do something like:

\set timelag 20
\set before :timelag-1
\set after :timelag+1

if I do a

select :timelag, :before, :after;

this seems to work nicely, but obiously, I am just concatenating strings (so the query is really 'select 20, 20-1, 20+1'), because if I do try make what I need:

\set beforemin '\'' :before ' minutes\''

it turns out that what I have is '20-1 minutes' - that does not work as intended in the query.

Is there any possibility to make psql variables work as numbers or is that beyond the scope of those so I will have to define the query in some other language or as a function?


texts:
I have to migrate a SQL Server database to  MySQL but i have no idea which fork to use (MySQL, Percona or MariaDB).

We don't have so much experience with MySQL and we haven't used it for high production workload.

Asynchronous replication cross datacenter for disaster recovery failover is required.

The database is used for a read intensive application but we will use cache layers and we are more concerned about new user peak traffic with writes for registrations etc

Is there a fork that fits best our needs?


texts:
I'm experiencing the dreaded Msg 4861 error message when attempting to use Bulk Insert on a 2012 SP4 SQL Server. "Cannot bulk load because the file "\PathToFileShare.d1.xxx.com\folder\file.dat" could not be opened. Operating system error code 5 (Access is denied)

The details:

I have two different domains, D1 and D2. I have three different computers, the SQL Server (in D2), the File Server (in D1), and the Application Server(in D1).

I am trying to call a stored procedure from the Application Server that uses bulk insert to get a file from the File Server and insert into the SQL Server.

Stored procedure is executed as D1\user1 from the application which produces an error. When called from the SQL Server in D2 in SSMS logged in as D1\user1, it works as expected and correct records are inserted into the table. If I try to log into SSMS as D1\user1 on any other machine in D2, execution produces an error.

Additional information:

I cannot provide the SQL Server service account in D2 access to anything in D1.
I can confirm that D1\user1 has access to the file they are trying to read from the file share.
I have seen the information from the following links:
SQL Server Docs
Additional Docs
We are NOT using kerberos authentication.

The bulk insert part of the stored procedure looks like this:

BULK INSERT BulkInsertDB.ExampleDomain.ExampleTable
FROM '\\PathToFileShare.d1.xxx.com\folder\file.dat'
WITH (
     ROWTERMINATOR = '0x0a'
     );

Any help would be much appreciated.

Thank you.