texts: Difference between SQL Server Azure and SQL Server 200x

texts:
I'm a SQL Server DBA and I can understand the logic behind logins users roles and etc ( login for server access, user for databases and etc ).

I've asked some questions here but I think I need to choose better how to ask this.

I'm using SQL Server for 5 years+ and now I'm trying to use some azure servers and databases.

I have some doubts about how it works, why I need to use master to create a login, where can I find a list of roles for azure?

AS I can understand now, to create a Login I need to use master and then create a login. then I change to the database I want to use and then I create a user there pointing to the login. Ok.

My question is, how can I create a sysadmin login like we have in SQL Server? I would like to create myself a login that can do everything. what role should I assign my login? I've seen to crate databases and etc but not "do everything you want mr. DBA".

Does azure uses Contained users? That's why I need to create first a login inside Master and then a user inside a database?

Please have in mind that I've read a lot of official microsoft sites related to logins, but I still can't understand what is simple.

I would really like to understand something like "when you create a login, you are creating....then when creating a user, it means..."

With SQL Server I can create a login, then a user inside databases and assign what I want them to do, but there's not visual info about this with azure.

An example of adding a dbowner:

CREATE LOGIN teste123
    WITH PASSWORD = '12e32d2c23dohdas970h9h&(*H&*(H*&(HG*&bhiuosnad22s23sd23'
GO

Use DatabaseIwant
CREATE USER teste123
    FOR LOGIN teste123
    WITH DEFAULT_SCHEMA = dbo
GO

EXEC sp_addrolemember N'db_owner', N'teste123'
GO

and see? I'm creating a DBO. what should I do to create a sysadmin login? to be able to access all databases, create delete and etc? thats the point. Should I create a login and create a user inside all databases as a DBO? because with SQL Server I can simply create a sysadmin and thats it. I have acces to everything.

Thanks all.

Edit: I think I found a usefull page finally:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins


texts: Possible to get comprehensive view on all defined names in PostgreSQL?

texts:
The SQL standard prescribes a rather large vocabulary; on top of this, RDBMSs implement their own constructs. Then they add hundreds of function names, internal and user-facing. On top of that, users go and define schema(ta)s, tables, indexes, custom functions, types, whatnot. Now, when I want to, say, create a function, a table or a column I have to name it. Some names (like all I think) are no-go, some names (for some kinds of objects?) are OK even though they clash (b/c they are not ambiguous). Good names clearly should be not confusing. But how can I get an exhaustive list of all the names that a given PostgreSQL DB instance 'knows' at any given point in time?

FWIW I scanned to Postgres docs for lists, but they don't seem to have any; instead, there's a list of SQL commands that does not list e.g. order and by (b/c that's subsumed under select); I'd have to peruse all the pages of Chapter 9. Functions and Operators to compile a list of all the function names. Not to speak of extensions, which by default put their stuff into the public schema.

Is there a truly comprehensive way to list all these 'defined words' complete with their roles, origins, comments etc. in a single huge list? I know there's psql's \d+ and so on, and I already have a small collection of utility views to provide subsets of what I'm looking for, but nothing resembling a Grand Inclusive List of Everything.


texts: What happens at the data page level when the record length changes

texts:
I've been digging around the net at work when I can for a few days now trying to make sense of how a DBMS (SQL Server 2008 R2 and others) handles adding a column to the end of a huge table so quickly.

At a high level you could think: I can just put a pointer at the end that points to the new column. However, at the page level aren't data pages filled with individual records? Wouldn't adding a column mean that every page that was already full would require a split?

Even pages that weren't full would require a lot of data juggling to add that column to the end of each record, update all the slot arrays, then cascade all the pointer changes through any existing indexes and/or the IAM and GAM pages?

The only thing I can think of is that all new column data is added to new pages, without the rest of the record, and pointers are added throughout the table tree structure to reference the new column pages. However, this seems like it would ruin spatial locality. If this is it, does the DBMS juggle data behind the scenes even when we don't specifically request a REBUILD?

I'm talking about the bit level of DBMS memory management with pages and asking how DBMSs are able to add a column (with or without allowing NULL values) to a set of existing records so quickly, even though the records already exist as a set of bits in a data page.


texts: How to use SQL*plus from standard user in Windows Vista to run sql query to Oracle10g on my server?

texts:
i have a bach file it contain:

@echo off
sqlplus x/y@z @run.sql

for execute this code correctly i have to execute this file from administrator user from my client, for some reason i need to run this file from a standard user without administrator privilege and without any password request.

when i research about my problem, i understood sqlplus can't be run without administrator privilege.

How to connect to SQL*plus from standard user in Windows Vista to Oracle10g on my server?


texts: Create a T-sql script JOB SQL-2012 and scheduled

texts:
I would like to create a T-sql script and scheduled. My goal is create all my databases (4 databases) and when the QA or DEV team need to test something, they can use these databases.
The problem is that I don´t have other server so I have to use the same server, for example I have a database called AUXDB, so I need to create a new database as PRE_AUXDB.
I have been creating all the scripts per each dabatase (just structure) but I need to know, how to create a header script to execute each script.
I hope you can understand my request.

Thank you in advance.
Raúl


texts: How can the sysssislog table to show up in a user database's system tables?  This is for SQL server 2012.


texts: How do I open local files using heidiSQL?

texts:
I'm using HeidiSQL to access my remote MySQL db.

Recently I was sent an .sql file.

Is there a way to open this file using HeidiSQL?
Does anyone know of a better way to graphically view and manipulate a local sql file?

I know that in windows Heidi associates with sql files but when double clicked nothing happens.


texts:
One of the things I need to do on my job is run lines of SQL code.  I need to do each line individually.  Which involves highlighting one line, executing it, then doing the next.  Sometimes it is up to 50 lines, so I'm trying to find an easier way.

Not giving specifics, basically each line reads

UPDATE que SET status = 0 WHERE doc = '001'; EXECUTE [things] '001'

UPDATE que SET status = 0 WHERE doc = '002'; EXECUTE [things] '002'

UPDATE que SET status = 0 WHERE doc = '003'; EXECUTE [things] '003'

etc etc

Is there some kind of command that will do each line one after another?  preferably with like a 10 second timer inbetween?


texts: Match two distinct values from a single column in a joined table

texts:
I have three tables:
class:
class_id
class_name

student:
student_id
student_name

class_schedule:
class_id
student_id

I want to select all the classes where studentA and studentB are in the same class using the student names. I can use a subquery to pull all the classes studentA is in, and then from that subset the classes that studentB is in and that works. That being said, it is terribly inefficient. I have tried a number of solutions including joining the same table twice, once for each value I want to find, but always get an empty result set.
For testing and prototyping purposes I am using sqlite, but will reside on DB2 long term.


texts: Where did new database 'BackupLocDb_###" come from?

texts:
Yesterday I was doing some work on a 3 Node AlwaysOn group of instances, SQL 2014 SP3, and this morning I found there was a database named BackupLocDb_1260a388-153d-4c86-a30b-0fd3feafb116 on the primary.  I did not create the database, but I am listed as the owner.

Where did this database come from?

Initial attemps to google, lead me to a few AlwaysOn posts but did not mention the database clearly.  I did not find it on DBA.SE either.


texts: Auto incrementing a foreign key value the same as the primary key of the parent table in SQL when inserting data?

texts:
I have the following two connected tables as shown below in the Microsoft SQL Server.

When inserting data from visual studio, the foreign key in the Reservations table (Customer_ID) should be auto incremented and have the same value as the Primary key of the parent table Customer_Info (Customer_ID).

Should i do it via the SQL Statements in Visual Studio or it is possible to be done directly in the Database? How can i do it? Thanks

Visual Studio Windows Forms App Design:

The SQL Statement when button is clicked:

 var query = "insert into Customer_info(Customer_Name,Customer_Phone) " +
            "values('" + name + "','" + phone + "');" +
            " insert into Reservations(RV_DAY,Customer_ID,Table_Arrangement,RV_OT,RV_PW,RV_SIOC) " +
            "values('" + day + "',50,'" + tb + "','" + SR1 + "','" + SR2 + "','" + SR3 + "');";

Visual Studio Exception error:


texts:
Whenever we connect to mongo thru shell, we get some logs. I just want the query result not the other logs except the failure message. Is there any way to configure that?
MongoDB shell version v3.6.11
connecting to: mongodb://localhost:27070/test?authMechanism=MONGODB-X509
2020-04-10T21:40:59.305+0000 I NETWORK [thread1] Starting new replica set monitor for
2020-04-10T21:40:59.516+0000 W NETWORK [ReplicaSetMonitor-TaskExecutor-0] SSL peer certificate validation failed: self signed certificate in certificate chain
2020-04-10T21:40:59.519+0000 W NETWORK [thread1] SSL peer certificate validation failed: self signed certificate in certificate chain
2020-04-10T21:40:59.586+0000 I NETWORK [thread1] Successfully connected to localhost:27070 (1 connections now open to localhost:27070  with a 5 second timeout)
2020-04-10T21:40:59.586+0000 I NETWORK [ReplicaSetMonitor-TaskExecutor-0] Successfully connected to localhost:27070  (1 connections now open to localhost:27070  with a 5 second timeout)
2020-04-10T21:40:59.653+0000 I NETWORK [thread1] changing hosts to ....
2020-04-10T21:40:59.865+0000 W NETWORK [ReplicaSetMonitor-TaskExecutor-0] SSL peer certificate validation failed: self signed certificate in certificate chain
2020-04-10T21:40:59.868+0000 W NETWORK [thread1] SSL peer certificate validation failed: self signed certificate in certificate chain
2020-04-10T21:40:59.932+0000 I NETWORK [ReplicaSetMonitor-TaskExecutor-0] Successfully connected to localhost:27070  (1 connections now open to localhost:27070  with a 5 second timeout)
2020-04-10T21:41:00.007+0000 W NETWORK [ReplicaSetMonitor-TaskExecutor-0] Failed to connect to localhost:27070, in(checking socket for error after poll), reason: Connection refused
2020-04-10T21:41:00.019+0000 W NETWORK [ReplicaSetMonitor-TaskExecutor-0] SSL peer certificate validation failed: self signed certificate in certificate chain
2020-04-10T21:41:00.021+0000 I NETWORK [ReplicaSetMonitor-TaskExecutor-0] Successfully connected to localhost:27070  (1 connections now open to localhost:27070  with a 5 second timeout)
2020-04-10T21:41:00.032+0000 W NETWORK [ReplicaSetMonitor-TaskExecutor-0] SSL peer certificate validation failed: self signed certificate in certificate chain
2020-04-10T21:41:00.032+0000 I NETWORK [ReplicaSetMonitor-TaskExecutor-0] Successfully connected to localhost:27070   (1 connections now open to localhost:27070  with a 5 second timeout)
Implicit session: session { "id" : UUID("0a2c342e-6afe-4d1e-zz98-49110d4a1767") }
MongoDB server version: 3.6.8
2020-04-10T21:41:00.471+0000 E QUERY [thread1] Error: don't know how to show [collections()] :
shellHelper.show@src/mongo/shell/utils.js:997:11
shellHelper@src/mongo/shell/utils.js:750:15
@(shellhelp2):1:1
bye


texts: Copy from file: ERROR: invalid input syntax for integer: "\N"

texts:
My table column defined intcol integer; no NOT NULL constraint. In the load  file most values are integers, but a few are null values labeled with the '\N' default as suggested by the copy manual. Can the copy command convert '\N' to null values in the table?  I also tried with null 'NA'. Got similar error message.

ERROR:  invalid input syntax for integer: "\N"
CONTEXT:  COPY table_name, line 111, column position3: "\N"

Any trick?


texts:
I created In-Memory OLTP Non-Durable Tables in 4 different  SQL servers.Lately one of the server performance has degraded and  I see lot of processes (Almost 200-300) running with command XTP THREAD_POOL.I don't see any such processes running on other 3 servers and there isn't any performance issues on them either.

Does anybody have any idea how to resolve it.


texts: dbcc failing with Cannot continue the execution because... alwayson having a change of state event for each db

texts:
I got a job DatabaseIntegrityCheck - USER_DATABASES, failing with the following error:

  Date and time: 2018-10-29 02:34:13 Command: DBCC CHECKDB ([one of my
  databases]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY HResult
  0x254, Level 21, State 1 Cannot continue the execution because the
  session is in the kill state.

I get an alert email with the following error message:

  The client was unable to reuse a session with SPID 193, which had been
  reset for connection pooling. The failure ID is 46. This error may
  have been caused by an earlier operation failing. Check the error logs
  for failed operations immediately before this error message.

On the primary server I get this message:

  The availability group database "all databases" is changing roles from
  "PRIMARY" to "RESOLVING" because the mirroring session or availability
  group failed over due to role synchronization. This is an
  informational message only. No user action is required.

and on the secondary:

  The availability group database "each database" is changing roles from
  "SECONDARY" to "SECONDARY" because the mirroring session or
  availability group failed over due to role synchronization. This is an
  informational message only. No user action is required.

As I could not find out the cause of this error, I have escalated it to the sys admin friends, also because I suspect it is something to do with network.

question:
as nothing about this on the error log, where else can I find any information about this matter?

I have run sp_blitz with markdown

EXEC sp_Blitz @OutputType = ‘markdown’, @CheckServerInfo = 1

and the results are here

I have also run the following command on one of my small databases test1 which is part of the availability group:

DBCC CHECKDB ([test1]) WITH  ALL_ERRORMSGS, DATA_PURITY

and that gave me the results shown here and important to say, on this occasion it did not cause any failover or change of state in the availability group.

Update:

Yesterday I changed the schedule of the job DatabaseIntegrityCheck - USER_DATABASES from 2 am to 3 am and it upset the availability group at 3:43 am
It seems to be caused by the dbcc checkdb


texts:
I have a table Item with many items of many types. Differently typed items are linked to different resources each of whom have a table with primary key.

Currently to achieve this I have two columns in Item table: Resource type and Resource ID.

The question is, what would be propper way to refactor Item table in a way so I can set up foreign keys to Resource ID?

Preferably without creating many new tables just for this reason or without adding many new columns to Item table, where most of them would be empty.

**EDIT**

Additional note: aside from resource id's, all items also have multiple other fields, some of which may be used only by some Resource type.

As far as I have looked it up, I am seeing 4 options:

Have a table Item with columns Resource type and Resource ID as well as all additional columns (case explained above).

(+) smaller amount of unused cells
(-) some unused cells
(-) no foreign keys on Resource ID

Have a table Item with additional column for each Resource type as well as all additional common columns.

(+) availability of foreign keys on Resource ID
(-) a lot of unused cells

Have a table Item with only common columns (or even with only Item ID) + additional table with columns: ID, Item ID, Value type, Value BIGINT, Value INT, Value STRING. Second table will be used for Resource ID's as well as fields that are unique for one or another Resource type.

(+) smaller and constant amount of unused cells
(+/-) more space per unique field, but overall smaller than in other cases.
(-) no foreign keys on Resource ID

New table for each Resource type

(+) availability of foreign keys on Resource ID
(+) more control over each Resource type
(-) a lot of new tables, number of which will increase as new Resource type's will be introduced.

I am now evaluating, which of these cases will be more beneficial to my case.


texts: How to correctly return a query result only if it isn't NULL?

texts:
I'm writing a PL/pgSQL function that creates a cursor for a query that I need to check whether it returns something.

What I'm doing is this:

Run the query
Check whether it returns something.
If it doesn't, double a parameter and run the query again.
Else, return all rows from the query.

I found that checking if the query returned something with a cursor was the best choice since it's a very long query (joining 5 tables and a lot of columns) and a SELECT ... INTO didn't seemed right, because I'd have to create a TYPE since the query has columns from one table and a column for a distance calculation.

The problem is that I'm currently using the cursor only to check if the query returned something inside a loop where I open it and close it. Once the query returns something, I exit the loop and return the query. I can tell right away that this is an ugly workaround for what I need. Maybe someone can help me with this issue.

Here's some code that shows what I'm currently doing.

CREATE FUNCTION store_distance(
    latitude double precision,
    longitude double precision,
    radius double precision,
    tries integer
)
RETURNS TABLE(
    store_id store.id%type,
    store_name store.name%type,
    distance double precision
)
AS
$$
DECLARE
    cur_stores CURSOR FOR
        SELECT
            store.id,
            store.name,
            get_distance(latitude, longitude, store.latitude, store.longitude) distance
        FROM
            store
        WHERE
            store.latitude BETWEEN (latitude - radius) AND (latitude + radius)
            AND store.longitude BETWEEN (longitude - radius) AND (longitude + radius)
        ORDER BY
            distance ASC;
    count int := 0;
    storerow RECORD;
BEGIN
    LOOP
        IF count = tries THEN
            EXIT;
        END IF;
        OPEN cur_stores;
        FETCH cur_stores INTO storerow;
        IF FOUND THEN
            EXIT;
        END IF;
        radius := radius * 2;
        count := count + 1;
        CLOSE cur_stores;
    END LOOP;
    RETURN QUERY
    SELECT
        store.id,
        store.name,
        get_distance(latitude, longitude, store.latitude, store.longitude) distance
    FROM
        store
    WHERE
        store.latitude BETWEEN (latitude - radius) AND (latitude + radius)
        AND store.longitude BETWEEN (longitude - radius) AND (longitude + radius)
    ORDER BY
        distance ASC;
END;
$$ LANGUAGE PLPGSQL;

So my purpose is to give coordinates, radius and the number of tries and to try and find stores within that search box. If no stores are found, I double the radius and try again until something is returned by the query or until the number of tries are reached.
The RETURN TABLE part is basically because I want to return the distance, so RETURN SETOF store was no use.


texts: What's the best way to migrate a database to a server of a lower SQL version?

texts:
I have Database A on SQL Server 1 that is SQL Server version 2016.
I need to migrate it to Server 2 that is SQL Server version 2012.
Database A is pretty small, only about 5 GB total.

I don't think there's a way to migrate it via a Full backup, so what would be the best way to move the database?...BCP?...or is there better methods?


texts:
Infrastructure : I have 3 virtual machines ip in a DMZ on a same lan.
one of them is running mongodb . OS : RHEL

Mongod.conf : I have commented out the bindIP option

But still mongodb is not accpeting connection from other servers on LAN. Telnet is failing from other 2 servers

the netstat output is

Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:27017           0.0.0.0:*               LISTEN      24251/./mongod
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1508/sshd
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      1621/master
tcp6       0      0 :::22                   :::*                    LISTEN      1508/sshd
tcp6       0      0 ::1:25                  :::*                    LISTEN      1621/master

i m guessing the foreign address for 27017 port should also look like ":::*"
I m not able to catch the problem here.Please help!


texts:
I want to migrate  production DBs from one server to a different one.

This is my plan:

Shut down the application
Change the DB to single user mode
Take a full backup for all databases
Restore the backup to the New server and remap logins
Create replication
Point the application to the new server
Start the application

This may take 4 hours to complete. Is there a way I can perform this in less time? It is kind of hard to request 4 hours down time.


texts: MS SQL 2008 Express - Database Tuning Advisor

texts:
I want to use DTA, but on my website I have an Express Server version.
Can I backup my DB on Express server, restore it on my personal computer with Enterprise Version, make some tuning by DTA and then restore it back on webserver Express MS SQL? Are they compatible? Both MS SQL 2008 R2, but one is Express and second not.


texts: What determines the time waited for Postgres Transaction Timeout?

texts:
What parameter would you change to set how long Postgres waits before deciding a transaction has been interrupted and should be rolled back?

I have a client program that is being frequently terminated in the middle of updating a postgres database.  I need to make sure that postgres catches the loss of connection to the client as quickly as possible and immediately rollsback the transaction.

I have control over the timeout settings on the program, if those matter.  I also have control over the postgres database, but I'm not sure what I should be changing on either if not both.


texts: SQL Server performance issue (CXPACKET wait type)

texts:
I have a performance issue with production SQL Server 2012 which has 24 cores and 32 GB of RAM. From wait type I can see CXPACKET is on the top based on the following SQL Server metrics, should I reduce MAXDOP or "cost Threshold for Parallelism" to avoid more parallelism and reduce CXPACKET wait type?

Any help would be appreciated.


texts:
I have the following table:

+-----+---------+-------+
| ID  | Account | Score |
+-----+---------+-------+
|   1 |       1 |  500  |
|   2 |       1 |  200  |
|   3 |       2 |  150  |
|   4 |       2 |  200  |
|   5 |       3 |  100  |
|   6 |       3 |  100  |
|   7 |       4 |  300  |
+-----+---------+-------+

What I want my query to do is check the table to see if any accounts match.  If they do, then check the Score and see which is higher.  Output the record with the higher score.  If the scores are the same, output the ID that is first.  If there are no accounts that match, just output the record.

The output would look like this:

+-----+---------+-------+
| ID  | Account | Score |
+-----+---------+-------+
|   1 |       1 |  500  |
|   4 |       2 |  200  |
|   5 |       3 |  100  |
|   7 |       4 |  300  |
+-----+---------+-------+

Any ideas?

Thanks


texts: SQL Server 2014 complex Stored Procedure

texts:
Sorry for the Title but I haven't found a better explanation.

I need to generate a TrackingID number for shipments using data from 3 different tables:

Sales
ShippingAddress
Carriers

I built a query that increments the appropriate trackingNumber and returns it together with some extra columns. (In the real query data are stored in a temp table and other queries follow, but now the issue is here.)

Here it is

DECLARE @OrderID NVARCHAR(10) = 'B3'

UPDATE cc
SET NextSeed= IIF(nextSeed IS NULL, NewStartSeed + 1,
                  IIF(nextSeed = LastSeed, NewStartSeed, NextSeed + 1)),
    LastSeed = IIF(NextSeed = LastSeed OR NextSeed IS NULL, NewStopSeed, LastSeed),
    NewStartSeed = IIF(NextSeed = LastSeed OR NextSeed IS NULL, NULL, NewStartSeed),
    NewStopSeed = IIF(NextSeed=LastSeed OR NextSeed IS NULL, NULL, NewStopSeed)
OUTPUT
    COALESCE(deleted.NextSeed, inserted.nextSeed-1) CurrentSeed,
    inserted.Prefix, inserted.Suffix,
    inserted.ShippingService, inserted.CarrierCode
FROM
    Sales s
JOIN
    ShippingAddress ss ON s.shippingAddressID = ss.ShippingAddressID
JOIN
    Carriers cc ON cc.ShippingService = s.ShippingService
                AND cc.carriercode = s.carriercode
LEFT JOIN
    SurchargeAreas ssa ON ss.PostalCode = ssa.PostalCode
WHERE
    s.OrderID = @OrderID

Now I have to add an extra table that contains, for the different carriers, the PostalCodes with Surcharge:

SurchargeAreas Table

What I need is:

if Customer's PostalCode is in SurchargeArea for the selected Carrier I need to change carrier/shippingService to default Carrier/shippingService to avoid extra shipping costs.

I added to the above query the following extra condition:

and ssa.postalcode is null

That detects if the customer's postalcode is in a surcharge area, but returns zero result, and therefore does not select the default shipping service, does not increment the Seed and does not return a result.

Here is the sqlfiddle with all tables and some sample data to better understand.

In the example, the PostalCode of OrderID "B4" is in a surcharge area, while B1, B2 and B3 are not.

Therefore setting

@OrderID = 'B1' or 'B2' or 'B3'

returns data, while

@OrderID = 'B4'

does not return anything, while I would expect something like

31001     RS    NL   postnlrm    postnl

since postnlrm has 'default' column set to 1

Can you suggest how to achieve what I'm looking for?

Thanks.


texts: SQL Server Restore Failing with Error Read on "M:\MSSQL\My_FULL_Backup_20190924_010558.bak" failed: 1101(A tape access reached a filemark

texts:
I have been trying to restore one of our db for the past few days without any luck. I keep getting the below error. I tried the restore during peak hours, off peak hours both by accessing the backup via network and then moving the file to local server.

I have also run Restorefilelistonly and restoreheaderonly on the backup and all seems to be fine. There is no permissions issue.

Please see the error below and the screenshot from SQL Logs. I did not find much help googling this issue.

1 percent processed.
2 percent processed.
3 percent processed.
4 percent processed.
5 percent processed.
6 percent processed.
7 percent processed.
8 percent processed.
9 percent processed.
10 percent processed.
11 percent processed.
12 percent processed.
13 percent processed.
14 percent processed.
15 percent processed.
16 percent processed.
17 percent processed.
18 percent processed.
19 percent processed.
20 percent processed.
21 percent processed.
22 percent processed.
23 percent processed.
24 percent processed.
25 percent processed.
26 percent processed.
27 percent processed.
28 percent processed.
29 percent processed.
30 percent processed.
31 percent processed.
32 percent processed.
33 percent processed.
34 percent processed.
35 percent processed.
36 percent processed.
37 percent processed.
38 percent processed.
39 percent processed.
40 percent processed.
41 percent processed.
42 percent processed.
43 percent processed.
44 percent processed.
45 percent processed.
46 percent processed.
47 percent processed.
48 percent processed.
49 percent processed.
50 percent processed.
51 percent processed.
52 percent processed.
53 percent processed.
54 percent processed.
55 percent processed.
56 percent processed.
57 percent processed.
58 percent processed.
59 percent processed.
60 percent processed.
61 percent processed.
62 percent processed.
63 percent processed.
64 percent processed.
65 percent processed.
66 percent processed.
67 percent processed.
68 percent processed.
69 percent processed.
70 percent processed.
71 percent processed.
72 percent processed.
73 percent processed.
74 percent processed.
75 percent processed.
76 percent processed.
77 percent processed.
78 percent processed.
79 percent processed.
80 percent processed.
81 percent processed.
82 percent processed.
83 percent processed.
84 percent processed.
85 percent processed.
86 percent processed.
87 percent processed.
88 percent processed.
89 percent processed.
90 percent processed.
91 percent processed.
92 percent processed.
93 percent processed.
94 percent processed.
95 percent processed.
96 percent processed.
97 percent processed.
98 percent processed.
Msg 3203, Level 16, State 1, Line 2
Read on "M:\MSSQL\My_FULL_Backup_20190924_010558.bak" failed: 1101(A tape access reached a filemark.)
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

Restore command used:

USE [master] RESTORE DATABASE [XXXXX_Testing_20190910] FROM DISK = N'M:\MSSQL\USE1-PRD-CL4$Entreprise_AG2_Testing_FULL_20190924_010558.bak' WITH FILE = 1, MOVE N'Testing' TO N'D:\MSSQL\DATA\Testing.mdf', MOVE N'Testing_log' TO N'L:\MSSQL\LOGS\Testing_log.ldf', NOUNLOAD, REPLACE, STATS = 1, maxtransfersize=65537

Version

  Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64)
  Jul 12 2019 17:43:08 Copyright (C) 2017 Microsoft Corporation
  Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0
   (Build 14393: ) (Hypervisor)


texts: How can I count some rows before inserting?

texts:
Here is my table structure:

// QandA
+----+----------------------------------------+------+---------+-----------+------------+
| Id |                   body                 | type | related | author_id | date_time  |
+----+----------------------------------------+------+---------+-----------+------------+
| 1  | content of question1                   | 0    | null    | 12345     | 1467468795 |
| 2  | content of first answer for question1  | 1    | 1       | 53456     | 1467469311 |
| 3  | content of question2                   | 0    | null    | 43634     | 1467469512 |
| 4  | content of second answer for question1 | 1    | 1       | 43665     | 1467470098 |
| 5  | content of first answer for question2  | 1    | 3       | 43324     | 1467471291 |
+----+----------------------------------------+------+---------+-----------+------------+
/* Columns:
   type: 0 means question, 1 means answer
   related: null means question, else it's the id of its own question
*/

I'm trying to implement this (something exactly like what stackoverflow does). An user can ask:

1 question per 90 minute
6 question per day
50 question per month

So I need to count the number of previous question for an user before letting him to ask. How can I do that?

I have two kind of query:

One:

INSERT INTO QandA (id, body, type, related, author_id, date_time)
    SELECT NULL, :body, 0, NULL, :id, unix_timestamp
    FROM (select count(*) as num_month,
                 count(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 day))) as num_day,
                 count(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 90 minute))) as num_90min
          from QANDA
          where author_id = :id and
               type = 0 and
               date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 50 day))
         ) a
    WHERE num_month < 50 and num_day < 6 and num_90min < 1;

Two:

INSERT INTO QandA (id, body, type, related, author_id, date_time)
SELECT NULL, :body, 0, NULL, :id, unix_timestamp
FROM dual
WHERE user_id = :id
   AND NOT EXISTS (
        SELECT count(*) AS num_month
               count(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 day))) as num_day,
               count(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 90 minute))) as num_90min
        FROM QandA
        WHERE author_id = :id and
              type = 0 and
              date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 50 day))
        HAVING num_90min  > 50
            OR num_day    > 6
            OR num_month > 1
    )

Ok, which one is better? Or is there a third option which is better than them?


texts: How can I get the row causing a conversion failure in SQL Server?

texts:
I have an update statement that is failing with this,

Msg 242, Level 16, State 3, Line 63
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

I understand the error.  I want to know which row I am trying to convert is failing.  If I have 1200 rows, how can I know which one is causing the problem without looking through it by hand?  Is there a way to get more detailed ouput?

I ended up doing the answer to this one,

https://stackoverflow.com/questions/1094483/sql-server-find-out-what-row-caused-the-tsql-to-fail-ssis

But it does not seem right.


texts: Improving the performance of STIntersects

texts:
Table T_PIN has 300,000 pins and T_POLYGON has 36,000 polygons. T_PIN has this index:

CREATE SPATIAL INDEX [T_PIN_COORD] ON [dbo].[T_PIN]
(
[Coord]
)USING  GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 128, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY];

T_POLYGON has:

CREATE SPATIAL INDEX [T_POLYGON_COORD] ON [dbo].[T_POLYGON]
(
[COORD]
)USING  GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 128, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY];

A query to find the intersection of T_PIN and T_POLYGON takes more than 45 minutes to execute:

SELECT COUNT(*)
FROM T_PIN
INNER JOIN T_POLYGON
    ON T_PIN.Coord.STIntersects(T_POLYGON.COORD) = 1;

The result is 4,438,318 rows.

How can I accelerate this query?


texts: Is it possible to have passwords configured per database or per host in .my.cnf

texts:
I have the following in my ~/.my.cnf

[client]
password="somepass"

but this is not the password I use for every user@host/database I connect to. Is there some way to specify in the config different passwords for different things so I don't have to type them in?


texts: Page setup and print layout in SSRS Report

texts:
I'm creating a SSRS report using BID 2008 and have a couple of questions:

Why is the page layout on the report different from print set up? For example I have set the page layout of the report in design mode to Landscape but when it is printed, it is portrait and the report is broken in many pages, the way that the table columns are broken makes the report not usable. How can we ensure what we see on the screen is the same as what will be printed (aligned with what was designed)? I've spent a considerable amount of time to do that, but have not had much success :(
My report length is relatively long, consisting of 3 pages (in design mode). I want to have multiple layouts in the report. For example page 1, 2 and 3 landscape and page 4 portrait. There is no sub report in the report. How can this be achieved?


texts: PostgreSQL splitting one table into three and setting up references between them

texts:
My title is a little misleading, what I am doing is inserting data from a CSV file where each row has information corresponding to three different tables. You can imagine the schema being something like this:

CREATE TABLE runner (id integer PK, name varchar);
CREATE TABLE race (id integer PK, name varchar);
CREATE TABLE race_result (id integer PK, race_id integer, runner_id ineteger, fp integer);

When I receive the data, it is in a CSV file where each row looks like:

runner_name, race_name, finish_position

Each runner can have multiple race results and so can each race. I am trying to figure out an efficient way to insert this data. I can first insert all runners, then insert all races, then finally insert all race_results and loop up all the ids to fill in the FKs, but this would be awfully slow. Another thing I am trying is:

CREATE TEMP TABLE tmp_x (run_name vc, race_name vc, fp int);
COPY tmp_x FROM 'input.csv' (FORMAT csv);

WITH run_ins AS (
  INSERT INTO runner (name)
    SELECT run_name
    FROM tmp_x
  ON CONFLICT ON some_contraint DO NOTHING
  RETURNING id
), rac_ins AS (
  INSERT INTO race (name)
    SELECT race_name
    FROM tmp_x
  ON CONFLICT ON some_constraint DO NOTHING
  RETURNING id)
INSERT INTO race_result (race_id, runner_id, fp)
  SELECT rc.id, ru.id, t.fp
  FROM rac_ins rc, run_ins ru, tmp_x t;

DROP TABLE tmp_x;

The problem is that this joins the tables creating more results then there actually are. First, is this the best way to go about it? If it is, can I remedy this by explicitly JOINing the tables in some way? I think my problem is that I do not fully understand what happens when I use the WITH statement. Does it do all of the inserts first then creates a temp table/view that has all of the ids of the records that were inserted? If so, does it return an id twice if I tried to insert but there was a conflict on a constraint?

Thank you


texts: How To Export-Import Database in Oracle 11g(Application Express Edition)?

texts:
I am beginner in Oracle 11g. I have created a database in oracle 11g(Application Express).

I want to export this database with all data(whole workspace) and import this to another computer where I already have installed Oracle.Is there any way to export-import the whole workspace in oracle 11g application express?


texts: Oracle: Casting timestamp as date and comparing result

texts:
I don‘t really understand what cast(timestamp as date) is doing. I am on Oracle Database 11g Express Edition Release 11.2.0.2.0.

select cast(to_timestamp('20190516 08:00:00', 'yyyymmdd HH24:MI:SS') as date) from dual

Displays the date 20190516 (without time) in the date format I have in my session.

select to_timestamp('20190516 08:00:00', 'yyyymmdd HH24:MI:SS') from dual;

Shows the date with time in my session format. So far everything as I expected.

So casting seems to remove the time part. Now I want to compare the result of the cast with a date which works in SQL Server but not Oracle.

select case when cast(to_timestamp('20190516 08:00:00', 'yyyymmdd HH24:MI:SS') as date) = to_date('20190516', 'yyyymmdd') then 1 else 0 end as match from dual; -- 0 = not matched
select case when trunc(to_timestamp('20190516 08:00:00', 'yyyymmdd HH24:MI:SS')) = to_date('20190516', 'yyyymmdd') then 1 else 0 end as match from dual; -- 1 = matched

What exacty is returned by cast(to_timestamp('20190516 08:00:00', 'yyyymmdd HH24:MI:SS') as date) when it does not seem to be just the date as indicated by the first select? Why can‘t I compare the result?

As shown I luckily have another solution with trunc(). I would like to know why comparing does not work  and if there is a possiblity to get the compare to work with cast. I am porting a view from SQL Server to Oracle and would like to keep as much as possible the same and it is done with cast in SQL Server.


texts: How I can find out which postgresql I am connected into when behind a load balancer?

texts:
I have the following database replication setup:

As you can see I do a master-slave replication and I use aload balancer in order to connect into read-only replicas.

So I want to test this setup, hence I connect into my database into postgresql read-only replicas via Load Labalcer via psql command.

But how I can find out which replica I am connected into? I need that in order to perform a stress test.


texts: Upper or Lower Case Optimisation on MS SQL Server

texts:
Does anyone know if MS SQL Server text queries are optimised for upper or lower case strings?

I read somewhere the it was uppercase, but cant seem to find the reference to it again.  Of course if this is entierly wrong and no optimisation takes place, this would also be useful to know.

Thanks for your help.

CM


texts: Oracle 11g Enterprise downgrade to Standard One

texts:
I'm checking possibilty of downgrade Oracle 11g from Enterpise Edition to Standard One edition. I've cross-checked edition comparison with

select name from dba_feature_usage_statistics where currently_used='TRUE'

Query result from dba_feature_usage_statistics:

Job Scheduler
Oracle Java Virtual Machine (user)
Oracle Java Virtual Machine (system)
Automatic Memory Tuning
Oracle Utility Metadata API
Segment Advisor
Automatic SQL Tuning Advisor
Object
Materialized Views (User)
Audit Options
Automatic Maintenance - Optimizer Statistics Gathering
Automatic Maintenance - Space Advisor
Automatic Maintenance - SQL Tuning Advisor
Automatic Segment Space Management (system)
Automatic Segment Space Management (user)
Automatic Undo Management
Client Identifier
Character Set
Locally Managed Tablespaces (system)
Locally Managed Tablespaces (user)
Parallel SQL Query Execution
Partitioning (system)
Recovery Area
Recovery Manager (RMAN)
RMAN - Disk Backup
Backup BASIC Compression
Logfile Multiplexing
Server Parameter File
Shared Server
Streams (user)
LOB
SecureFiles (system)

I think Parallel SQL Query Execution and Partitioning (system) are only in Enterprise Edition, but I'm not sure about rest, especially:

Automatic Memory Tuning
Segment Advisor
Automatic SQL Tuning Advisor
Automatic Maintenance - SQL Tuning Advisor
Recovery Area

Do you guys know which edition of Oracle is needed to use these features?


texts: appending 2 tables based on common columns in Postgresql 9.3

texts:
I have 2 large tables that I need to combine into a single table--for statistical analysis. The two tables have some variations in column named, otherwise I would just use an INSERT statement. So I have been able to identify the common columns in both tables, but I cannot seem to write a query to extract those common columns from each table and then add them to a new table. Here is what I have so far.

I was able to get the common columns between tables using an INTERSECTquery.

    (SELECT attname
    FROM   pg_attribute
    WHERE  attrelid = 'public.iabr42fl'::regclass
    AND    attnum > 0
    AND    NOT attisdropped
    ORDER  BY attnum)
    INTERSECT
    (SELECT attname
    FROM   pg_attribute
    WHERE  attrelid = 'public.iabr23fl'::regclass
    AND    attnum > 0
    AND    NOT attisdropped
    ORDER  BY attnum);

I incorporated this query into a postgresql function called z_common_columns and then I tried to create a table from it. But whenever I do this, I hit a memory error WARNING: out of shared memory..

CREATE TABLE indiadb AS
SELECT z_common_columns() from iabr23fl;

Is there a good way to select out the set of common columns on both tables and then append them to a single table that is created from those common columns?
Thanks.


texts: (SSMS) How do you prevent an open tab from querying a DB after disconnecting the server

texts:
In SSMS if you have a tab open with a query and then you disconnect the database you can still run the query after disconnecting.

is there a way to prevent SSMS from running a query after server is disconnected?


texts:
I have SQL Server 2000. I want to clean it by dropping obsolete and unused objects. One of the views causes me an error. I try to give more information but I still have no clue what the cause is.

Background: I found that one view can be drop and I know for sure no one uses it. And it links to detach (unused) database. I decided to drop it. Before I drop I check the view to make sure.

CREATE View vw_obsolete_view
AS
SELECT [very long],
,
,
...
FROM [unused_database].dbo.vw_obsolete_view

DROP VIEW vw_obsolete_view

Now, View was dropped. It seem to be OK. However, I got a call from user that Application (ADP) got an error message. It states that view vw_obsolete_view is missing.

Since this view is not my coding and the my Application is call only my objects. So I have no idea why this cause an error. It took me an hour to figure out. I check all dependent objects. Nothing links to this view at all. I checked all ADP VBA code. Nothing relates to this view also.

I can't find what wrong with this. But I know that the problem is from dropping view because I was the only one who changes the database. So I make a new view with the same name.

CREATE View vw_obsolete_view
AS
SELECT 1 AS total

The error at the end user is disappear. End User is happy but I still can't drop view. I still have no clue.

Sidenote: I can't upgrade SQL Server right now.


texts: SQL Server data migrations go through client machine or not?

texts:
I want to move a lot of data from a SQL Server database on one machine to another machine. Both machines are remote. If I run the Copy Database wizard, will it pull all the data across the Internet to my client machine and then push it back out to the destination? Or will the source and destination machines communicate directly?

It's a very big database, so this matters quite a bit.

One complexity is that my login only shows a view of a subset of the data, so backup and restore would not work. I have to copy from a view to a table.

I can set things up so the two remote machines can see each other. I don't think I would be able to run the wizard on one of them, though, because I all I have is a login to the db and no other access to the OS.


texts: Assiging stored procedure results to SSIS variable

texts:
I am trying to get the value from a stored procedure into an SSIS variable, and then testing to see if two SSIS tasks can work if I added an expression. So for an example, I am trying to use this stored procedure:

Maybe I am even setting up the SSIS variable properties entirely wrong, because I am also not sure if I am doing this the right way for the stored proc value to be imported into an SSIS variable. Please do tell me if you need any more screencaps of anything else.

Here is the task example:

And here is Precedence Constraint Editor screencap:

And here is the properties for the first task:

I want it to go forward (or fail) based on that condition. But when I test it, the process flows from first task to second regardless, and only shows me "100% complete" for the first task and nothing about whether it checked this expression to be true or not. How can I do such a thing and what is going wrong?
I do have a variable in SSIS called 'orderCount' to get the value from stored proc.


texts: Can't Connect to MySQL database through PuTTY

texts:
I have a database that runs on pythonanywhere.com, and am trying to connect Tableau to it, and need to set up an SSH Tunnel in order to do so.

The directions on pythonanywhere's website mention PuTTY, and many people on Tableau forums also use it, so this is what I'm going with.  I've been trying for about 2 weeks to get this working, but no luck.

So here's what happens:

1).  I enter ssh.pythonanywhere.com as the host connection on the main page:

2).  I then go to SSH --> Tunnels, and enter the following information:

The information in the Destination field has the form my-username.mysql.pythonanywhere-services.com:3306

Then I hit Open, and am prompted for my sign in credentials to Python Anywhere:

Then I go to Tableau and enter the following information:

In the password field I type in my database password.

The message that always comes up is:

[MySQL][ODBC 8.0(w) Driver]Access denied for user 'myusername'@'localhost' (using password: YES)
Invalid username or password.

Now obviously, there's a natural response to this:  double check my password.

For now just take my word for it that I've double checked this several times and it is correct.

I've tried to find help on PythonAnywhere's forums, and Tableau's forums, but no solutions have been found.

Also:

I've tried switching out the port 127.0.0.1 with localhost, but this did not work.

I have connected to my database externally in a Jupyter Notebook using this same information, so I have some confirmation that this info has been used successfully.

I've tried connecting to MySQL Workbench using the same info and I get the same error message, which makes me think there's something about my database configuration which is not working, but I have no idea where to look.

UPDATE:

In my PuTTY Bash terminal the command telnet 127.0.0.1 3306 returns the message:

Trying 127.0.0.1...
telnet: Unable to connect to remote host: Connection refused

In my DOS terminal I get the following:

In my git BASH terminal I type in netstat -an|grep 3306 and get:

UPDATE 2:

I was on codementor trying to get this resolved, and we had the following issue:

after signing in throuhg PuTTY, I entered the following command:

mysql -h 127.0.0.1 -u jonathanbechtel -p

Which returned the following error message:

ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

We also tried switching out 127.0.0.1 with localhost and got a different error message:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

To be honest I don't understand the significance of this, but was told by him that this is unusual and might signal some security settings on my account with PythonAnywhere.  I started this forum message on their forums to try and troubleshoot it.


texts:
Before I write an update statement, should I check if it exists?

Someone told me to do this

So it avoids uselessly writing to the transaction log for value that already exists,
And only takes a Shared lock allowing other reads versus an Exclusive lock.
They said imagine a transaction running like this multiple times for different values, you would be taking exclusive locks all the time rather than shared locks.
Coworker mentioned, looking at lock compatibility chart, there cannot be two update locks when searching, however there can be two shared locks. So when "searching for rows to update" when they're no values to update, a fake update lock can block a true update. Can someone invalidate this claim?

if not exists
(
    select FavoriteColor
    from dbo.Person
    where Name = 'Bob'
    and FavoriteColor = 'Green'
)
update dbo.Person
set FavoriteColor = 'Green'
where Name = 'Bob'

Same question, except for delete now, should I check if it exists?

if exists
(
    select FavoriteColor
    from dbo.Person
    where Name = 'Bob'
    and FavoriteColor = 'Green'
)
delete dbo.Person
where Name = 'Bob' and FavoriteColor = 'Green'

We use SQL Server 2016.


texts: EXPLAIN EXECUTE completes quickly, but is slow without EXPLAIN

texts:
When I run the following query in pgAdmin3, it runs in like 74ms, and I can see the execution plan but no rows are returned. I guess that's the behavior of using EXPLAIN?

Anyway, when I remove 'EXPLAIN' in that query, it then is taking 3 minutes to run, anyone have a clue what the problem is?

PREPARE statement66 AS
select
    c.car as carId,
    c.date as manufactured_date
from v_car c
where
    c.carId > -1
    and
    (
        c.carId = 5555
        or c.truckId = 5555
        or c.motorcycleId = 5555
    )
order by c.date desc
limit 100;

EXPLAIN EXECUTE statement66;


texts: Database schema design for individual configurations

texts:
I'm trying to design a database schema that allows me to store customer custom built products. As a simplified example:

A user can buy a cupboard, draws, boxes, and dividers.
A cupboard has 2 tiers.
Each tier can house either a draw or two boxes.
The bottom tier can use dividers instead.
Boxes can also have a divider in them.

A customer can set up a cupboard and buy it. They can also just buy a draw setup, or even just a box setup. My question is, how do I best represent this hierarchy of customised items in a relational database?

My current idea is to have the following tables:

User (user_id, name)
Item (item_id, name, price)
System (system_id, parent_id, item_id, user_id)
Content (item_id, system_id, quantity)
Transaction (transaction_id, user_id, time)
Order (transaction_id, item_id, quantity)

The system table represents cupboards, boxes, or draws that users have customised. The item_id helps differentiate which it is.
If a draw is in a cupboard, then the draw's parent_id would match the cupboard's system_id.
The content table holds a list of items within the system that are not customisable, such as the dividers and any future products.
A transaction would be the final purchase, and the order would hold each item sold.

My worry with this is that the content and system tables could become huge and full of duplicates (e.g. users will probably have a lot of boxes, they will just have different content and parent_id). Is there a better way of modelling such relationships?

Thank you for your help.


texts: Are there any reasons why a table wouldn't show in sys.dba_tables? (Oracle)

texts:
I have 2 queries

 SELECT *
 FROM sys.dba_indexes
 WHERE Table_Name = 'XDB$RESOURCE'

and

 SELECT *
 FROM sys.dba_tables
 WHERE Table_Name = 'XDB$RESOURCE'

Are there any reasons why a table wouldn't show in sys.dba_tables? As far as I've read all tables should show here, hence why I'm using this rather than all_tables.

Can an index in Oracle be against a deleted table?

Does sys.dba_tables show data relevant to access you have, and if so is there any table that rather brings back nothing than half the data?

Relatively new to Oracle, any explanation or advice appreciated.


texts: MYSQL dump and problems character encoding

texts:
I have a 2 gb old database sql backup file. The problem is that text is encoded in UTF-8. Actually this isnt the real problem. This database contains greek and english characters.

The problem is that for example greek Π letter is encoded as
C3 90 UTF-8  which is unicode U+00D0 , clearly not Π letter. But in ISO-8859-7 D0 is Π!

This occurs almost everywhere.. Is there a fix i can apply ?Ideally i want to convert it to utf-8..

NOTICE
ISO8859-7 was used


texts: Deselect CPU in affinity setting for OS Only use?

texts:
A memory recommendation is to reduce MaxMemory by 4GB or 10% of RAM (equally divisible by cores) to dedicate that memory for the OS.

Most questions on CPU affinity deal with splitting them out between instances.

My question is should one (or more) cores be deselected for OS only use? One site did this on a Win2003/SQL 2005.

Q1: Was this a beneficial thing to do? As I understand, older Win versions put everything in NUMA 0 so it sort of made sense. But I just recently learned that SQL was aware of that and favored NUMA 1+ to offset. And now, as I understand it, the newer OS (Win2008+) utilize NUMA and spread their CPU usage across the cores.

Q2: So, even if it was a good idea under older Win/SQL versions is it still a good idea under newer Win/SQL versions?


texts: Client times out, while MySQL query remains running?

texts:
We experienced an issue in which a read-only query, run through MySQL workbench, timed out from a user's UI perspective and remained running on the server (and apparently consuming more and more resources) until we had an outage.

Questions

Is there a standard way to deal with this sort of issue in MySQL?
Is there a fundamental cause that we need to avoid?


texts: Finding opening and closing price using sqlite GROUP BY statement (accessing main query alias in subquery)

texts:
I want to preface this post by saying that I am a SQL(ite) newbie. My main goal is to find the opening/closing/high/low/average prices of each 5 minute interval of these stock prices. I can get the high/low working with min/max and the group by query but I am running into problems with the opening/closing. My database looks something like this

SYMBOL | DATETIME           |PRICE
AAPL   | 1999-06-01 09:30:01|45.0
AAPL   | 1999-06-01 09:30:03|44.9375
AAPL   | 1999-06-01 09:30:04|44.9375
AAPL   | 1999-06-01 09:30:05|44.9375
AAPL   | 1999-06-01 09:30:06|44.9375
AAPL   | 1999-06-01 09:30:07|45.0
AAPL   | 1999-06-01 09:30:08|45.0
AAPL   | 1999-06-01 09:30:09|44.9375
AAPL   | 1999-06-01 09:30:11|45.0
AAPL   | 1999-06-01 09:30:12|44.96875

The following query

SELECT SYMBOL, MIN(DATETIME) AS MINDT, MAX(DATETIME) AS MAXDT, AVG(PRICE)
FROM stocks
WHERE DATETIME < '1999-06-02 00:00:00'
GROUP BY strftime('%s', DATETIME)/(60*5) LIMIT 4;

results in

AAPL|1999-06-01 09:30:01|1999-06-01 09:34:41|44.9269724822695
AAPL|1999-06-01 09:35:22|1999-06-01 09:39:58|44.8615196078431
AAPL|1999-06-01 09:40:03|1999-06-01 09:44:58|44.9800531914894
AAPL|1999-06-01 09:45:05|1999-06-01 09:49:52|44.9397321428571

whereas

SELECT SYMBOL, MIN(DATETIME) AS MINDT, MAX(DATETIME) AS MAXDT, AVG(PRICE),
(SELECT PRICE FROM stocks WHERE DATETIME=MINDT),
(SELECT PRICE FROM stocks WHERE DATETIME=MAXDT)
FROM stocks
WHERE DATETIME < '1999-06-02 00:00:00'
GROUP BY strftime('%s', DATETIME)/(60*5);

throws this error in SQL

Error: near "MINDT": syntax error

and in R

Error in sqliteSendQuery(con, statement, bind.data) :
  error in statement: no such column: MINDT

I have googled around and I think I found what is wrong with my query: you can not acces an alias made in main query in a sub query (i.e. reference MINDT in the subquery). But my I lack SQL knowledge to find a fix to this. Is there anyone that could help me out here?


texts:
We currently use a monitoring tool which shows us our top wait stats either by number of waiting tasks or total wait time.  Below are the wait stats by number of tasks waiting and also wait time per task.

We have users complaining of slow down in the system, yet the metrics for server seem fine in terms of Disk IO, memory and CPU. Does anyone know if the PREEMPTIVE waits are an issue?

Number of waiting tasks
SOS_SCHEDULER_YIELD
PAGELATCH_EX
PAGELATCH_SH
PREEMPTIVE_XE_CALLBACKEXECUTE
PREEMPTIVE_XE_GETTARGETSTATE
PREEMPTIVE_XE_SESSIONCOMMIT

Average wait per task
PAGEIOLATCH_SH
PREEMPTIVE_XE_GETTARGETSTATE

Update:
I ran a query from Paul Randal similar to what you posted and got the following:

WaitType    Wait_S  Resource_S  Signal_S    WaitCount   Percentage  AvgWait_S   AvgRes_S    AvgSig_S
PREEMPTIVE_XE_GETTARGETSTATE    9704.81 9704.81 0.00    604647  44.60   0.0161  0.0161  0.0000

I know this doesn't line up very well, but basically this wait type has accounted for %44.60 of all wait types.  Also as there has been no Signal Waits on this type then this indicates no CPU pressure but instead a wait on some other resource.  Not sure how I deduce what that resource is however.

Also this is SQL 2012 SP1

Update2
AS requested here is results from your query.  In regards extended events the only sessions running is the default system_health one and 2 SharePoint ones I have just noticed, they must have been put there by default.  I may switch these off I wonder if these are causing an issue.

Its interesting that my PREEMPTIVE_XE_GETTARGETSTATE doesn't seem to be in this list.

wait_type   wait_time_ms    signal_wait_time_ms resource_wait_time_ms   percent_total_waits percent_total_signal_waits  percent_total_resource_waits
SP_SERVER_DIAGNOSTICS_SLEEP 300014  355508314   0   24.621089361251698  99.883069863550302  0.000000000000000
MSQL_XP 96782   0   4268999 0.295653861591811   0.000000000000000   0.295653861591811
ASYNC_IO_COMPLETION 56193   64  345552  0.023935987107964   0.000017981341700   0.023931554722962
BACKUPTHREAD    41100   6850    265025  0.018828979257262   0.001924565478840   0.018354575549998
LCK_M_U 40500   71  41030   0.002846491499596   0.000019948050948   0.002841574322484
PWAIT_ALL_COMPONENTS_INITIALIZED    31422   0   94205   0.006524262955146   0.000000000000000   0.006524262955146
XE_LIVE_TARGET_TVF  28050   0   33458   0.002317167771915   0.000000000000000   0.002317167771915
LCK_M_X 4027    50  29195   0.002025392177944   0.000014047923203   0.002021929377161
SQLTRACE_INCREMENTAL_FLUSH_SLEEP    4018    613 355390660   24.612983567922965  0.000172227538471   24.612941113985366
CXPACKET    3756    1   14755   0.001021941767062   0.000000280958464   0.001021872511047


texts: Why is random password generation the same when it's inside while loop? TSQL

texts:
I have a random password generator.  It works fine by itself.  When I put it inside of a while loop, I get the same password even though the seed appears to be different.

declare @count int = 0
declare @end int = 500

while (@count < @end)
    BEGIN
    DECLARE @r varchar(6)
    select @r = coalesce(@r, '') +char(
    case when r between 0 and 9 then 48
    when r between 10 and 35 then 55
    else 61 end + r)
    from master..spt_values
    cross join
    (SELECT CAST(RAND(ABS(CHECKSUM(NEWID()))) *61 as int) r) a
    where type = 'P' and number < 6

    print(newId())
    print(@r)

    set @count = @count + 1
END

This produces an output like the following:

As you can see, the guid is different every time, but the password is the same.
How can I make the password different with each iteration?


texts: Concatenate columns uniquely on the same table in MS ACCESS


texts: SQL Server Job failing to delete html files out of a folder

texts:
We have a simple job that runs every Sunday on sql server 2000. Copies files over, and then deletes any .txt, .rpt, .html and .log files.

It looks to me like it isn't finding any .txt and .rpt files and is reporting back no output generated, but those steps are passing.

Then for the del .html files, it reports back that it can't find any html files and fails.

The code is:

   COPY C:\DATA\LOGS\*.* C:\DATA\LOGS\Sunday\*.*    Succeeds
   DEL  C:\DATA\LOGS\*.txt                          Succeeds
   DEL  C:\DATA\LOGS\*.rpt                          Succeeds
   DEL  C:\DATA\LOGS\*.html                     FAILS
   DEL  C:\DATA\LOGS\*.log                      Never gets here

Any ideas as to why it wouldn't like that del *.html ? Why wouldn't it just look for .html files, and when not finding any, just pass the step, and go onto the last step?


texts:
I have two tables:

notes
users

Notes Table:

id || description ||CreatedBy(which is userid)||......other columns
---------------------------------------

Users table:

id || name || ......other columns
---------------------------------

Now I have a scenario, in which a user can create a note and share it with
other users. Each user can create a note and then can share this with other users.

To Implement the above task, I will create a new table 'UserNotes'.

UserNotes Table:

id || userId || NotesID
-----------------------

But suppose I have one note which I want to share with all other users then
my usernotes table will have 100 entries.
Similarly if I have 1000 notes and all notes are shared with all users then my new table will have 1000*1000 entries.

Also a user who has created a note and shared with other users, can remove the sharing also for some users.
For example: if a user creates a notes and shared with 100 users initially, he can later on remove some user associations or add some new users also.

Is my approach correct?

Or is there a better approach to achieve the above scenario considering ease of insert, update and select?

I am using POSTGRESQL as my RDBMS.

Can you please help?


texts: Trying to get DB2 to enumerate databases

texts:
I am trying to use DB2DataSourceEnumerator.GetDataSources("db2.windward.net", null, "username", "password") to return all databases on that server. When I make the call it takes about 10 seconds to return and then throws the following exception:

  A connection attempt failed because the connected party did not
  properly respond after a period of time, or established connection
  failed because connected host has failed to respond 104.42.24.172:523

The documentation says:

  To be visible, the remote database server instances must have a
  running database administration server (DAS), which is configured to
  respond to Search and Known Discovery requests.

I'm assuming that is the problem. How do I set this for the DB2 server? We have version 11 running on Windows.


texts: How to avoid duplicating information in a series of nested SELECT statements?

texts:
I'm working on an SQL query that puts together customer history based on past purchases (the industry is venue ticketing). The report will be LARGE, something like 80-90 columns, with one column per event or category of event per record in the output CSV.

The database structure requires that I use nested select statements to get the necessary data into columns for each record--each column containing a quantity of tickets for that type of event. I know how to do this in theory, but each embedded select statement is so large that the full report would blow through the 8,000 character limit on my DB interface. One of these statements looks like this:

(select count (*)
FROM
guest ig, event2 e, eventseat es, "order" o
WHERE ig.guestid = g.guestid
and ig.guestid = o.guestid
and o.orderid = es.orderid
and e.eventid = es.eventid
and e.incometype = 'T'
and e.eventtype in ('SS', 'BMF')
and es.status in ('2','4')
and es.price <> '0.00'
and es.price <> '5.00'
and e.year = '2010'       <-- THESE LINES ARE THE ONLY ONES THAT
and e.run in ('SS-DANCE') <-- CHANGE PER SELECT STATEMENT
) as 'SS-Dance',

That's about 400 characters, which only leaves enough room for <20 iterations in a single query without going over the character limit.

Is there a way to reduce this or generalize it so that I don't need to waste all those characters on duplicated information in each SELECT?

I've put together an example at the following link which contains a simplified version of my DB structure and some sample data. Objective is to get the same result data without using so many characters per nested SELECT.

http://sqlfiddle.com/#!3/fc316/2


texts: SQL Server Log Shipping restore fail - The Log Is too recent to apply

texts:
The restore is missing a backup log, and the "physical_device_name" of the missing backup log is "NUL". This is a Transaction Log that gets made out of sequence with the log shipping backup job. I can't find another service/job that triggers this job. If I understand log shipping correctly, this missing log causes the restore to fail every day at about midnight.

The bkSize is 0 but the LSN number did change after the missing backup:

I would appreciate advice to resolve this error.

Regards

Niel


texts: How do I generate a time series in PostgreSQL?

texts:
If you're looking to generate a date series, see this question

Let's say I want to generate a series for every 5 minutes for 24 hours. How do I do that in PostgreSQL?

PostgreSQL can generate_series() from a timestamp, but not from time.

Is it better to pick an arbitrary timestamp, or is there another way to generate the series?


texts: verify that mongos server is connected to config servers

texts:
I've been writing a backup script for sharded replica-sets and it's almost done. Except I can't seem to get it to successfully start the balancer backup after everything's all said and done.
Here's the command I'm trying to use to start the balancer back up; keep in mind that this is being run on the actual mongos server via SSH.
sudo -s
mongo -u username -p password --authenticationDatabase db
use config
sh.setBalancerState(true)
exit
exit
exit

I keep getting the following error whenever the scripts hits the startBalancer function, which runs the above code.
SyncClusterConnection::udpate prepare failed:  mongo-conf-0.foo.bar.com:27019:10276
DBClientBase::findN: transport error: mongo-conf-0.foo.bar.com:27019
ns: admin.$cmd query: { resetError: 1 }

I've tried checking against the exit status of the mongo shell process, using something like
if (code != 0) {
  return next('repeat');
} else {
  return next();
}

but regardless of what actually occurs in the mongo-shell, the exit code seems to always be 0.
Any ideas on how I can verify that the mongos process is actually connected to all three configs before I try to re-enable the balancer? I assume the problem is that the mongos server tries to connect to the config server before the mongod process had a chance to finish starting up (part of the backup process for sharded replica-sets is shutting down one of the config servers)


texts: Sql Server Stored procedure exicution taken long time

texts:
I have worked in a web based project, in my code sometimes i want to execute a stored procedure more than 1000 times in a single button click. The button is used for some type of approval process and when user clicks the button we approve many things for him, so as the part of process we want to run a stored procedure continuously more than 1000 times. Each time take data from a view and store as xml document then parse that document then add each document as command text then execute the stored procedure. But the problem is it takes long time. 3-4 minutes to take to complete the process . i am looking for improving the performance. Anybody has any suggestion please tell me .

i had already go through this link but still the execution lags


texts: How can I get the correct offset between UTC and local times for a date that is before or after DST?

texts:
I currently use the following to get a local datetime from a UTC datetime:

SET @offset = DateDiff(minute, GetUTCDate(), GetDate())
SET @localDateTime = DateAdd(minute, @offset, @utcDateTime)

My problem is that if daylight savings time occurs between GetUTCDate() and @utcDateTime, the @localDateTime ends up being an hour off.

Is there an easy way to convert from utc to local time for a date that is not the current date?

I'm using SQL Server 2005


texts:
I have a table with following definition:

create table json_test (
    my_data jsonb);

Inserted to it values like this:

{"details": [
    {
        "f1": "",
        "f2": "free text"
    },
    {
        "f1": "",
        "f2": "free text"
    }
]}

Now I want to update this jsonb column(f1 to null):

{"details": [
    {
        "f1": null,
        "f2": "free text"
    },
    {
        "f1": null,
        "f2": "free text"
    }
]}

I want to change the f1 from string to null.

How to achieve this ?


texts:
I'm trying to achieve a document validator that checks if a user signs up with an avatar to their user profile. If they didn't attach an avatar I want to use something like $ifNull to set the default image:

db.runCommand({
   collMod: "User",
   validator: { $or: [
       { avatar: { $ifNull: [ "$avatar", "default-avatar.png"] } }
    ]},
   validationLevel: "strict"
})

However, it seems like MongoDB separates document validation and it's aggregation methods... Is there a way to do this so that it will stay as a rule on a collection?


texts: Can full drive backup be used instead of MSSQL database backup?

texts: I have a MSSQL Express server running on a VM.  Hosting provider I use has an option to create drive snapshots. The server is running all the time and is not stopped during the drive backup. Backup has no special functionality related to MSSQL. If I keep my data files on such drive and use snapshots in case of disaster recovery, are there any risks that database may not be restored?


texts: PostgreSQL Slave has more files in pg_xlog than /wal_archive

texts:
Background: for various reasons I am switching PostgreSQL Slave from a Streaming replication to a warm standby replication.

The streaming replication was setup using this guide: http://technology.trapeze.com/journal/postgresql-streaming-replication-ubuntu-1004/

To make the switch to a warm standby: I am following instructions for warm standby here (1) https://wiki.postgresql.org/wiki/Warm_Standby and here (2) http://www.postgresql.org/docs/current/static/pgstandby.html

I am finding that on the slave there are more log files in pg_xlog/ than in wal_archive/ (files sent from Master). How is that possible? I think it's hindering my warm setup.

in /var/lib/postgresql/9.3/main/pg_xlog/ of Master I have files from 000000010000000000000001 to 00000001000000000000001A.backup to 00000001000000000000001C
in /var/lib/postgresql/wal_archive/ of Slave I have files from 000000010000000000000001 to 00000001000000000000001A.backup to 00000001000000000000001B
in /var/lib/postgresql/9.3/main/pg_xlog/ of Slave I have files from 00000001000000000000001C to 000000010000000000000023

recovery.conf contains:

restore_command = 'pg_standby -d -s 2 -t /tmp/pgsql.trigger.5442 [/wal_archive] %f %p %r 2>>standby.log'

when I restart the Slave it complains that it is missing the xxx1C log file

First question, how is it possible for the Slave to have more pg_xlog/ log files than the Master?

Second question, to resync the servers in warm standby mode: do I have to do pg_basebackup again (to essentially copy Master's /data and /pg_xlog directory) to the Slave?

Just trying to understand PostgreSQL :) Thanks in advance!!

I found a very similar question here, that seems to have never been answered: http://www.postgresql.org/message-id/CAE7ByhgTJ6xOe4f3aa42Kn0_5gfEzWW1FZ9QQqsC6RP-Fnh_ow@mail.gmail.com


texts:
i have load on mysql 500%

my server info:
Harddisk    512 GB SSD 2.5"
RAM 32GB
CPU Intel Xeon E3-1230v2
Connection 1GBit

my Max Online: 35
Max. concurrent connections: 250

i use Framework Yii2 and Activerecord

mysql Conf:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
default-time-zone='+00:00'
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
max_connections        = 1000
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem


texts: AVG with OVER - invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

texts:
I have what I believe to be a correctly written sql query that calculates the average using an over clause to window the results. However, I'm getting an error when I include the over clause.

SELECT date,
        Day_Name_of_week,
        AVG(SalesItems_Breakfast) OVER (PARTITION BY Day_Name_of_week) AS BreakfastAvg
    FROM day_Sales_total
    GROUP BY date, day_Sales_total.Day_Name_of_week

Which gives me the following error:

  Msg 8120, Level 16, State 1, Line 93
  Column 'day_Sales_total.SalesItems_Breakfast' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Which is bizarre, because as SalesItems_Breakfast is inside the AVG function.

However, the below query runs fine, the only difference being the over clause has been removed:

SELECT date,
        Day_Name_of_week,
        AVG(SalesItems_Breakfast) AS a --OVER (PARTITION BY Day_Name_of_week) AS BreakfastAvg
    FROM day_Sales_total
    GROUP BY date, day_Sales_total.Day_Name_of_week

What's going on?


texts: PostgreSQL : FOR SHARE & FOR UPDATE priority

texts:
Here is my situation:

I have 2 types of actions A & B, each read or update a single ressource.
Actions A should be able to be executed concurrently.
Actions A should wait for Actions B to be finished.
Actions B should wait for Actions A to be finished.
Actions B should wait for Actions B to be finished.

This is solved perfectly using FOR SHARE for Actions A, and FOR UPDATE on Actions B

My problem is the following:

using this model, Actions B can be on hold indefinetely as long as new Actions A are coming through.
I want that once an Action B is trying to acquire its LOCK, new Action A should be on hold.

Is there any way to achieve that?


texts:
I need to upgrade MySQL 5.6 to 8.0 on a Windows community server (install version).

Do I need to dump and import to 5.7 then to 8.0 ? or a simple dump of all databases from 5.6 is enough ?

thanks in advance.


texts: Insert new value into table if it does not exist

texts:
I am just trying to teach myself SQL so appreciate any help or advice on this one.

I have a table for suppliers which has an attribute for payment method.  The payment method includes but is not limited to a number of items. e.g. paypal, visa.  I was thinking of having a small table for payment method containing valid options but I would like to update the table with information entered in the suppliers table if the payment method doesn't exists.  How would I go about doing this?

I am using SQLite.

Thanks


texts:
i am running mysql 5.1.41 that came bundled with xampp on windows. the problem is that the event doesnt run automatically even when the event scheduler is ON. i have a table named ta_table using innodb engine and it has 4 fields one of which is ti_time with a timestamp type with default value of current timestamp. this field ti_time is given the value of timestamp at which the row is inserted. now i want to delete all rows which are 2 hours old from the table ta_table so i created an event
the event looks like this

CREATE EVENT ev ON SCHEDULE EVERY 1 MINUTE STARTS 2011-07-17 14:54:52 ENABLE
    DO
    begin
    delete from ta_table where timestampdiff(minute,ti_time,now())>120;
    end

now this event should delete any rows with ti_time field greater than 2 hours(120 minutes). when i execute this query

delete from ta_table where timestampdiff(minute,ti_time,now())>120;

it works. it deletes the rows older than 2 hours. which means my query is correct but the event is not running. my event scheduler is running which i confirmed by show processlist
and it shows 2 preocesses root and event scheduler.
the state of the event scheduler is waiting for next activation.
when i ran this query

SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE event_name = 'ev'

it gives result as

status = enabled
last executed=2011-07-18 02:36:38

but when i see the table ta_table the records are not deleted? whats wrong with this?

Edit:

As of RolandoMySQLDBA's suggestion i upgraded mysql 5.1.14 to mysql 5.5 but the event still fails


texts: Is replication a suitable method to move a SQL Server database to new hardware?

texts:
I need to move a live database with about 100 GB of data to a new machine. As I understand the easiest way to do that would be to take it offline, move the files and bring it back online.

But: The old server is connected via a 200 mbit uplink only, which means it would take me more than 1 hour to move the whole thing. That's too long to be down for my situation so I'm wondering if it might be suitable to just set up transactional replication and on day x take the old one offline, change my DNS records to the new server and make that one the new writeable master.

Can I be sure that all my table settings, views, etc. are properly copied to the new machine?

Or is there another suitable way to avoid longer downtimes when moving databases?


texts: Weird memory pressure for SQL Server 2016

texts:
We have been battling an issue with weird memory pressure on a specific server for a couple of months now.  Here's what the last incident looked like in SentryOne:

System Memory

SQL Server Memory

Memory Configuration:

Total Server Memory - 96GB
Max Server Memory - 84GB

The reason why this seems weird is that, if it were external memory pressure I'd expect the Other category on System Memory to grow during this, which is doesn't.

Part of what we see happen during this time is that queries end up generating bad plans and end up causing performance issues for the app.  Historically, running DBCC FreeProcCache during this situation has alleviated the pressure, but we still don't know the cause.  I think the plans getting a bad plan is a symptom rather than a cause of this issue, but I may be wrong.

Things that we have done to try and resolve this issue:

Removed a join in what we thought was the problematic sp
Deleted duplicate records in the database
Increased memory on the server (I think we added 16-32 gb)
Enabled Lock Pages In Memory

I am at a complete loss as to what to look at next.  Our architect thinks we might need to fiddle with some VM settings with memory, but we aren't there yet.

What can I look at to potentially fix this weird memory pressure issue?


texts: Connecting to mySQL from 2 different machines

texts:
ALL,

Is it possible to modify the permissions of the MySQL DB or I will need to revoke it and grant it again?

My problem is as follows:

I'm running MySQL server on Gentoo Linux and I'm trying to connect remotely from 2 different Windows machine (all computers are on the same network in my home).

When I had trouble connecting I asked a question on Gentoo forum and got a suggestion to run:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION ;

My guess is that I used a static IP address of one of the machines because I can connect from one Windows machine but not from the other.

So now, my question would be - is it possible to issue something like:

ALTER PRIVILEGES ON.....;

Or maybe I can do some query to fix this?

Thank you.

BTW, a little off-topic: I couldn't find a tag for "remote-access". Could someone please create it? Or its not present for a reason?

EDIT:

I actually want the permissions to be like this:

GRANT ALL ON foo.* TO bar@'192.168.1.x' IDENTIFIED BY 'PASSWORD';

so that I can connect only from 192.168.1.1 - 192.168.1.254. I don't need this server to be publicly available.

EDIT2:

GRANT USAGE ON *.* TO 'root'@'192.168.1.4' IDENTIFIED BY PASSWORD '*DC365D603F605E79DE2F2B8DA969DBB8497BEC89'
GRANT ALL PRIVILEGES ON `draft`.* TO 'root'@'192.168.1.4' WITH GRANT OPTION

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*DC365D603F605E79DE2F2B8DA969DBB8497BEC89' WITH GRANT OPTION
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

EDIT3:

I just tried to execute following command:

GRANT ALL PRIVILEGES ON *.* TO 'root'@192.168.1.% IDENTIFIED BY '<my_password>' WITH GRANT OPTION;

and I got following response:

Error Code: 1064. 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 '% IDENTIFIED BY '<my_password>'' at line 1 0.00041 sec


texts: Setting up DBLink between two Sybase Databases

texts:
I am having dificulty finding anything on the net on this subject.

I am tasked with creating a DBLink between two Sybase Databases. At the moment it's not yet clear if they will be on the same server or on seperate machines in the same network. What I do know is that they are both gonna run on the same Version of Sybase SQL Anywhere 12.

All I can find is how to creat a DBLink from Oracle or MSSQL to Sybase.

I can't seem to find anything about Sybase to Sybase.

I am sorry if my terminology is a little wonky, I am new to Sybase/ASA.


texts: How to query group for increase or decrease in value

texts:
In MySQl I have a table positions and I would like to find out if position of certain app for certain feed in a certain country type has been increasing/decreasing and by how much from the last reading.

This is the table structure:

CREATE TABLE `positions` (
  `application_id` int(11) unsigned NOT NULL,
  `country` varchar(2) NOT NULL,
  `feed_id` int(2) NOT NULL,
  `position` int(4) NOT NULL,
  `created` datetime NOT NULL,
  KEY `application_id` (`application_id`,`store`,`feed_id`,`position`),
  KEY `created` (`created`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

country is a two character ISO country code, every row belongs to one application through application_id and feed_id is another table that is helping to sort out the large amount of data.

What the query should look like? I have gotten to a pont where I group by the country code where application and feed id's are in WHERE clause but can't figure out the values :(


texts: Is ALTER TABLE CHECK CONSTRAINT redundant?

texts:
I created a table with a foreign key constraint on it. When I generate a table creation script through SSMS, it creates the following code:

ALTER TABLE [dbo].[MainTable]  WITH CHECK ADD  CONSTRAINT [FK_MainTable_ForeignKeyTable] FOREIGN KEY([FK_Field])
REFERENCES [dbo].[ForeignKeyTable] ([PK_Field])
GO

ALTER TABLE [dbo].[MainTable] CHECK CONSTRAINT [FK_MainTable_ForeignKeyTable]
GO

Since the first statement creates the constraint WITH CHECK, does this make the second statement redundant? If not, what is the CHECK CONSTRAINT accomplishing that the WITH CHECK does not?

I saw ALTER TABLE CHECK CONSTRAINT, but in that case, the SSMS is generating the first statement WITH NOCHECK, so it makes sense to have both statements.


texts:
I am kind of new around DBA world, i am currently working with JMeter(front/back end performance testing) but when i have spare time (which is a lot lately..) i try to learn about DBA Development, also my company is looking for a DBA dev so there is that.

Also this question comes as a follow up to  Queries from different tables locking each other (LCK_M_X) .

This DB has a lot of Unique Identifiers linked with FKs, (Tid,Did,Cid,id) and a few more not so big, every time i make a transaction its inserted in "DidRegistry" table.

                  DidRegistry

As you can see from "CreationMoment" column, This table gets lots of inserts.. i wanted to change Datetime to Datetime(2)7, what do you think?

Now this part i am not sure about, when i put load on the DB, sp_whoisactive never shows the locks in Didregistry, but in different tables and in different queries (see link on top), those queries are inside transactions, the transactions come from the Developers code, with SQLProfiler i didn't find a way to see what queries belong to what transactions.

I am at a loss if i am chasing the wrong rabbit hole or if this could be a Design problem for future bigger clients with more load profiles.


texts: Performance of selecting strings: substring IN vs regular expression

texts:
I have a large table with 1 billion+ records. These are the important columns:
CREATE TABLE BigTable
(
   id BIGSERIAL PRIMARY KEY NOT NULL,
   hexid VARCHAR(255), -- usually 24 or 48 characters
   type VARCHAR(255),
   <and other columns>
);

I need to find some records matching on a substring of the hexid column as well as the type column. There is an index on the hexid column. I need to search for a list of strings matching the last 24 characters of the hexid (the records I am interested in will always have 48 character hexids). There are two queries that I could run and I'm wondering which will perform better:
Query 1 using WHERE IN:
SELECT * FROM BigTable
WHERE substring(hexid, 25) IN (
    'deadbeef4b00018c09bf5db1',
    '5f478c50deadbeef97039344',
    '5fc0b855a6a8b600deadbeef')
AND type IN ('OneType', 'SomeType', 'AnotherType')
ORDER BY id ASC;

with explain plan:
Sort  (cost=105476938.69..105477029.92 rows=36492 width=638)
  Sort Key: id
  ->  Seq Scan on BigTable  (cost=0.00..105468257.46 rows=36492 width=638)
        Filter: (((type)::text = ANY ('{OneType,SomeType,AnotherType}'::text[])) AND (""substring""((hexid)::text, 25) = ANY ('{deadbeef4b00018c09bf5db1,5f478c50deadbeef97039344,5fc0b855a6a8b600deadbeef}'::text[])))"

Query 2 using regular expressions:
SELECT * FROM BigTable
WHERE hexid ~ '\w{24}(deadbeef4b00018c09bf5db1|5f478c50deadbeef97039344|5fc0b855a6a8b600deadbeef)'
AND type ~ '(One|Some|Another)Type'
ORDER BY id ASC;

with explain plan:
Sort  (cost=100022576.55..100022577.16 rows=243 width=638)
  Sort Key: id
  ->  Seq Scan on BigTable  (cost=0.00..100022566.92 rows=243 width=638)
        Filter: (((hexid)::text ~ '\w{24}(deadbeef4b00018c09bf5db1|5f478c50deadbeef97039344|5fc0b855a6a8b600deadbeef)'::text) AND ((type)::text ~ '(One|Some|Another)Type'::text))

Based on the explain plans the cost will be more or less the same.

Query 1 performs a substring, doesn't have pattern matching [fast], but does have WHERE IN clauses [can negatively affect performance].
Query 2 has pattern matching (that takes care of the substring) [slow], but doesn't have WHERE IN clauses [doesn't negatively affect performance].

Getting the data is a once-off process. I don't have the luxury of time running these queries individually to determine which one performs better. I need to make an informed decision on which one to go with.

Which query will give the best performance?
Why does the first explain plan show rows=36492 and the second rows=243?
Is there perhaps another query that would perform much better? Keep in mind that, since it is a once-off query, adding additional indices would not be helpful.

(I'm interested in knowing the answers for PostgreSQL, but I guess it will be similar for other RDBMSs.)


texts: PostgreSQL - using password/md5 authentication with empty password

texts:
I have the following role in my system:
CREATE ROLE test LOGIN INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

There is no password. Trying to connect to the db from within the same machine using either psql or PgAdmin3 to the above role I'm getting:
psql: fe_sendauth: no password supplied

When the password is set I can connect perfectly.
The pg_hba.conf is default - merely:
host    all             all             127.0.0.1/32            password
host    all             all             ::1/128                 password

Create Role statement help says:

PASSWORD password
Sets the role's password. (A password is only of use for roles having the LOGIN attribute, but you can nonetheless define one for roles without it.) If you do not plan to use password authentication you can omit this option. If no password is specified, the password will be set to null and password authentication will always fail for that user. A null password can optionally be written explicitly as PASSWORD NULL.

Can I deduct from that that having a NULL password password authentication will always fail? Is this also the case for MD5 authentication? Is there anyway to connect using an NULL password role without specifying trust authentication?


texts: Scale out methods with high availability

texts:
We have a two site AlwaysOn Cluster and looking to do a High Available multi-site write solution. We have looked at Replication, Sharding, and SQL Elastic options for having multi-site write for a single application. End goal is to have N number of sites that can host the application closest to the user requesting the service, with high availability. We have scaled up, now we want to scale out.
It looks like sharding might be the best option. Does anyone have solid experience with implementing a solution like this in MS SQL?


texts: One Materialized View in Two Refresh Groups

texts:
I have five materialized views that I want to refresh in two occasions, every Sunday and at the 1st of every month. I created a Refresh Group for the weekly and that works fine. But when I tried to create the second Refresh Group for the monthly I get a "materialized view is already in a refresh group".

You can have a materialized view in only one refresh group?

What options do I have to refresh it in different intervals?


texts: Create table shows ERROR 1005(HY000) errno 150

texts:
i tried to create tables as per given in my textbook in MySQL server as follows:

create table department
(dept_name      varchar(20),
 building       varchar(15),
 budget             numeric(12,2),
 primary key (dept_name)
);

create table course
(course_id      varchar(8),
 title          varchar(50),
 dept_name      varchar(20),
 credits        numeric(2,0),
 primary key (course_id),
 foreign key (dept_name) references department
);

the department table gets created, but an error

ERROR 1005 (HY000): Can't create table 'test.course' (errno: 150)

is displayed.

The queries seem very simple. And i am unable to find any mistake.
What can be the possible error that is preventing me to create the table?


texts:
I'm doing a lot of work with SQL Azure at the moment, trying to improve performance of a web app.  I am using the dm_exec_query_stats view to look for poor performing queries.

Does anyone know a way to clear the query stats in Azure?  DBCC FREEPROCCACHE isn't supported.  I can use sp_recompile 'procedure' to clear stored procedures/functions etc, but the site uses some LINQ-generated queries which can't be cleared in this manner.


texts: mysql_install_db: How to set the root password

texts:
mysql 5.6 seems to have a new --random-passwords option for mysql_install_db, which lets me discover the root password:
http://dev.mysql.com/doc/refman/5.6/en/mysql-install-db.html#option_mysql_install_db_random-passwords

But what can I do with mysql 5.5? The documentation suggests that the root password should be empty, but I cannot seem to connect with no password (or an empty password), so i cannot set a real root password:

For instance:

I initialize the database like so:

$ mysql_install_db --no-defaults --user=murrayc --datadir=/tmp/testmysql/data

I start the server like so:

$ mysqld_safe --no-defaults --user=murrayc --port=3308 --datadir='/tmp/testmysql/data' --socket='/tmp/testmysql/mysqld.sock' --pid-file='/tmp/testmysql/pid'

But when I try to set the root password like so (I believe that --user in this case refers to the mysql user, not the linux user):

$ mysqladmin --no-defaults --port=3308 --user=root --password='' password 'somenewpassword'

I get this error:

mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'


texts: Clustered vs Non Clustered Indexes for a Primary Key

texts:
I have a large table with around 19 million records in it.

The primary key is a uniqueidentifier column called ID and there is a nonclustered index on this column.  An application generates these GUID values.

The table currently has no clustered index on it.

I am considering changing from a nonclustered index to a clustered index, since the table currently has no clustered index. I am wondering what are the pros and cons of doing this or if I should?

Leave things as is
Find another column to create the clustered index on
Change this ID column over from a non clustered index to a clustered index.

We are getting about 50K inserts daily, with no deletes and no updates. Let me know if you need any additional details.

The table has 24 columns total and 5 nonclustered indexes without a clustered index:

Index 1- PK on ID (uniqueidentifier)
Index 2- TimeStamp (datetime)
Index 3- Hash Column (nvarchar(64))
Index 4- Covering index 4 columns
Index 5- Covering index 6 columns

I inherited the database from someone else. The application uses this GUID as a primary key with relationships to matching foreign keys in respective child tables so the queries use the uniqueidentifier id's in joins.

How do I determine if my GUID is generated sequentially? They don't appear similar. Some examples are as follows ordered by createddate:

24A689A0-831E-4670-9204-766256C58E43
ED001C69-11BA-430D-ACF9-3C3CADF3B400
768E5497-4F48-4C65-87D9-E9C27066708E

Doing a little research, it seems like a uniqueidentifier column is not a good candidate for a clustered index, so I'm wondering if I need to:

Look to a few columns to create a composite clustered index on these columns or
Live without a clustered index
Something else?

Let me know if I can provide any additional details. If anyone can give me some guidance as to what are some considerations for making this decision, I would appreciate that greatly.