texts: SSMS 2014 Gives an exception when opening the new query window

texts:
I have an installation of SQL Server 2014 on a server on the LAN and on my PC I have SQL Server Management Studio 2014 for few months already. Out of no where when I open SSMS I get

The type initializer for 'system.windows.media.fonts' threw an exception

I have no clue of what might have caused this exception. I tried re-installing SSMS 2014, removing all SQL components on my PC and re-installing SSMS again, I even removed .NET 4.7 (Because I have VS 2017 as well). What is the cause for this exception and how to resolve it?


texts: How to get currently inserted rows and sum them within Mysql trigger before insert

texts:
I have a mysql trigger that fires BEFORE INSERT.
My php application is inserting multiple rows at once like so

INSERT INTO table1(id,name,price) VALUES(1,'LH32400', 1990),(2,LH32401,2000);

The idea is that before the data is inserted i want to sum all of the prices, and insert them into another table. I know after insert would work,  but the rest of my logic requires this to be a before insert trigger.

The main problem is that within the trigger body New.price returns null (or i think it does, because the trigger throws an error: total cannot be null in my final insert)

If i try

CREATE TRIGGER triggerName
BEFORE INSERT
ON table1 FOR EACH ROW

BEGIN
DECLARE vSum DOUBLE(10,2);
SET vSum = vSum + NEW.price; // because of FOR EACH ROW i thought that this would happen multiple times

INSERT INTO table2
(
 id,
 date,
 total,
 status
)
VALUES
(
 1,
 SYSDATE(),
 vSum,
 'closed'
);

END

If  on the other hand i try to use a cursor i get stuck

DECLARE cur1 CURSOR FOR SELECT id,name, price FROM ? (i obviously cant use table1 since this is a before insert trigger and no data has been actually inserted into table1)

There is probably a very simple answer to this but my brain is pretty down with concentration and i desperately need help. Any suggestion will be appreciated, thanks.


texts: Postgres upgrade and converting latin1 to utf8

texts:
I'm pretty new to PostgreSQL, but I've been tasked with doing an upgrade. The current system runs on CentOS 5.11 and is Postgres 8.1.23 with a default encoding of latin1.  The new server is OpenSuSE 42.1 with Postgres 9.4.9 with a default encoding of utf8.

I've been trying to use the pg_dumpall command in order to get the users and their roles moved along with the databases/tables/data. But, I'm unable to restore to the new server because of the difference in encoding.

I see that the pg_dump command has the -E option to specify encoding, but it doesn't seem to give me a way to get users and roles. Would the -E option even accomplish what I'm needing -- the conversion from latin1 to utf8?

What is the best way for me to migrate/upgrade users, roles, data, etc. and at the same time convert from latin1 to utf8?

Thanks!


texts: Does logging a SLOW query impact postgres performance?

texts:
Context...

Our Production system is reporting(occasionally) query timing that is taking longer than usual.

I have already measured EXPLAIN ANALYZE against the SQL statement but nothing seems to explain the longer than usual query timing.

cprdb=# explain ANALYZE select ror from crns where crn='8000440008' limit 1;
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.29..8.30 rows=1 width=6) (actual time=0.092..0.092 rows=1 loops=1)
   ->  Index Scan using crn_idx on crns  (cost=0.29..8.30 rows=1 width=6) (actual time=0.091..0.091 rows=1 loops=1)
         Index Cond: (crn = '8000440008'::bpchar)
 Planning time: 0.200 ms
 Execution time: 0.132 ms

My next bet is to log the PostgreSQL (DML) query time using log_min_duration_statement=0 and get a conclusive evidence as to where the problem is.

For that, I want to cross check ..

By Enabling query logging I would not deteriorate the overall PostgreSQL performance right?


texts:
Its been a few years... but I've run into a problem-set that reminds me of a schema  design pattern and I'm trying to recall the details.

Essentially, the pattern consists of one database which is 3 NF (or something else) that services all Inserts, Updates, and Deletes. On some periodic or event-driven basis it would publish all (or some) of its state to another database which is optimized for high-performance read-only access. Essentially, the publish would generally target a completely different or de-normalized schema.

The question is... Is this a well known pattern? If yes, what is it called, and what are the pitfalls aside from duplicative storage of the same state.


texts: How can I get my linked server working using Windows authentication?

texts:
I'm trying to get a linked server to ServerA created on another server, ServerB using "Be made using the login's current security context" in a domain environment. I read that I'd need to have SPNs created for the service accounts that run SQL Server on each of the servers in order to enable Kerberos. I've done that and both now show the authentication scheme to be Kerberos, however, I'm still facing the error:

"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'".

In Active Directory, I can see that the service account for ServerB is trusted for delegation to MSSQLSvc, but I noticed that the service account for ServerA does not yet have "trust this user for delegation" enabled. Does the target server also need to have that option enabled? Is anything else necessary to be able to use the current Windows login to use a linked server?


texts: Database Entity Design for Enumeration Table

texts:
I have an enumeration of ~10 values - they are Char such as A through K. These are all unique. They are used for two purposes - an application is forced to select acceptable values and also there is a many to many relation between product and this enumeration table.

Is it bad practice to set the Primary Key as simply this unique value [A-K]?
So basically one column of char(1) as the Primary Key. Or should I use an ID field and then set these values in a column and force that column to unique?

The values in this enumeration will never change. They are fixed by the representation. For example, position 1 can only be position 1, like first base will always be first base and never will it become 3rd base (or at least in the last 150 years it has not - my app might not last that long). So that is why I was thinking to simply do it this way - because it truly is permanent, non-changing.


texts: How to send an alert based on SQL Query results

texts:
I currently have a maintenance plan in place that does a backup, re-indexing, etc.  If a task fails, it sends me an email alert.  I would like to add a "T-SQL Statement Task" that will execute a query and send me an email alert based on the results of the query.

For example:

Select Cast(case when count(inventory.items) > 1000 then 1 else 0 End as bit) as SendAlert
From Inventory

The maintenance plan's alert function seems to be triggered whether the Query executed successfully or not -- RATHER THAN from the Query Result.

Can I accomplish what I want from within a maintenance plan or is there a better way to do this?


texts: Identify changes across records for the field ID

texts:
Consider the following table

TabName     ID      Gender    FirstName
Tab1        C1      M         John
Tab2        C1      M         John
Tab3        C1      F         John
Tab1        C2      F         Mary
Tab2        C2      F         Mary
Tab3        C2      F         Mary

I am looking to identify individual IDs where there is inconsistency across the 'tabs'. For example, ID 'C1' is not the same in tabs 'Tab1' and 'Tab2', however, has a change in the Gender field in 'Tab3'. On the other hand, 'Mary' is consistent across all three tabs.

A potential approach may be to find 'ID' where the count of duplicates records (each row is exactly the same) is not equal to the count of occurrences of each 'ID'.

However, I am not sure how I would write the above SQL. Additionally, I think there is probably a better and cleaner approach to this problem.


texts: get data from table having records in hindi


texts: "Unresolved reference to User" after importing as a VS DB project

texts:
I just imported an existing SQL Server 2008r2 production database into a VS 2013 database project.

I now get a number of errors along the lines of

Error       SQL71501: User: [mydbuser] has an unresolved reference to Login [mydbuser].

I don't really need my VS DB project to manage users, but I'm concerned that it would try to remove them upon deploy if they weren't there.

The files themselves are generated as

CREATE USER [mydbuser] FOR LOGIN [mydbuser];

or

CREATE USER [mydomainuser] FOR LOGIN [MYDOMAIN\mydomainuser];

The error marker shows that it's specifically for the Login. As that's a system-level object, I can understand it being outside the scope of the db project.

Is it preferred that I change them all to

CREATE USER [mydbuser] WITHOUT LOGIN;

or add the CREATE LOGIN clause to the beginning of each file?

Removing the login reference seems to be simpler and removing the users altogether would be the simplest.

I want to make sure that I'm using the tool the way it was intended. Will there be any issues in re-publishing any of those back to production? What is the proper procedure for adding a user/login via a project?


texts: SQL Server collecting SQL statements using particular index

texts: I'd like to capture SQL statements that uses a particular index. Is there a way I can utilize SQL Profiler or extended events to do so? I know I can view the plan cache to get a gist of the queries, but I'd like to capture the currently being executed queries real time.


texts:
I want to use the functionality that PROC FREQ provides in SAS. PROC FREQ helps in giving the statistics such as frequency, percent, cumulative frequency etc. of a variable or variables. The link attached explains the function in more detail. I am aware that I can use PIVOT in SQL Server to achieve somewhat the same objective. However, there are certain feature of PROC FREQ that I cannot find SQL equivalent. To be precise:

table x/norow nocol nocum nopercent missing

https://support.sas.com/documentation/cdl/en/statug/63033/HTML/default/viewer.htm#statug_freq_sect010.htm

This link helped me figure out that table is basically a cross tabulation technique, so how can I use pivot in SQL for this? Next, the missing treats missing values as non-missing. How can I do the same in SQL?

Any thoughts/advice/help much appreciated.


texts: SQL Server 2008R2 - How to prevent a role from creating or altering views within a database?

texts:
I have a situation on SQL Server 2008 R2 where I am trying to prevent users of an AD group from being able to create new views, or alter any existing views, within a particular database. This AD group is mapped to a role in the database, so I'm working on tweaking the permissions that role has.

From the documentation and Management Studio it's straightforward to deny the CREATE VIEW permission at the database level. I am also familiar with denying the alter command at the schema level, but that would prevent the altering of tables as well as views. I'm hoping to just deny the ALTER VIEW permission at the database or schema level.

Is this possible?


texts: Use of integer instead of interval (of one type)

texts:
Our DB design presently has a interval column which will only be storing days (no other interval type) so it is making sense to use INT2 (smallint) instead of interval. Reference to documentation.

Advantage: 2 bytes instead of 12 bytes (we have many such columns).

Is this line of thinking ok or am I overlooking something?


texts: INSERT-SELECT Statement occasionally fails silently and works upon re-run

texts:
We have a batch import process that BULK INSERTs raw data into staging tables, does some data mapping, and then pushes it to another database for validation.

The procedure where my issue occurs is very long and is filled with column names so I'll give you the run down:

Raw files are BULK INSERTED into various Import Tables. If this
succeeds, those records begin migration to the validation database.
New GUIDs are generated for the Validation Database (@ValBatchId)
Basic Record information is put into a Records table

INSERT INTO [ValidationDatabase].[dbo].[RecordsTable]([RecordId],[BatchId],[Columns...])
(
    SELECT NewId(), @ValBatchId, Columns...
    FROM [ImportRecordDataTable]
)

Import Tables have their data mapped from provided values to standard values and are inserted into their respective tables in the Validation database

While performing the last step of the process, it will occasionally silently fail and insert 0 records into a specific table:

INSERT INTO [ValidationDatabase].[dbo].[Table]
           ([Columns]...)
     (
        SELECT rec.RecordId, imp.stuff...
        FROM [ValidationDatabase].[dbo].[RecordsTable] as rec
        INNER JOIN [ImportDatabase].[dbo].[ImportTable] as imp ON...
    WHERE rec.BatchId = @ValBatchId AND imp.BatchId = @ImportBatchId
  )

I added some diagnostic code in the stored procedure to makes sure this was a SQL Server error and not a disk issue by adding @@ROWCOUNT into a logging table:

DECLARE @RowsInserted INT = @@ROWCOUNT

      INSERT INTO [ImportDatabase].[dbo].[MigrateLog]([BatchID],[TransactionDate],[Description])
      VALUES(@ImportBatchId,GETDATE(), CAST(@RowsInserted AS VARCHAR(MAX)) + ' records inserted into <table name>')

...where it displayed this for a particular batch:

inserting into <table name>

0 records inserted into <table name>

Here's the kicker: Immediately after the rest of the migration process finishes, if I go and rerun the select statement inside the insert...and it returns the data that should have been inserted. Most disturbingly, If I go and re-import that batch of data (as a completely separate batch, without any reference to the failed batch) it will work without issue.

I can't for the life of me figure out what is going on. It has to be the SELECT statement inside the INSERT that isn't returning records but I can't recreate this problem, even with the same batch it happens on. Is this a bug in SQL Server, or am I missing something here?

Thanks!

Additional info:

SQL Server 2016 Enterprise
Both databases are on the same server, no remote queries or linked
servers
Static SQL
READ COMMITTED Isolation Level
Snapshotting is off


texts: Is there any approach for encrypting mysql column by key?

texts:
I have a scenario for encrypting user information in mysql db. Is there any approach to encrypting column by key. This key information will be there in application configuration file. This key we won't store any where in databases/triggers.

My approach is while connecting to database from application we'll pass key. With that key we'll get the information. If some user directly comes into db he will see the encrypted data in table columns.

Please suggest how can I proceed?


texts: Copy database schema from SQL Server 2008 to SQL Server 2005

texts:
Possible Duplicate:
  Downgrading from SQL Server 2008 to 2005

Is it possible to copy the database schema from SQL Server 2008 to SQL Server 2005,

I don't need data from the tables, just the empty database structure with triggers, stored procedures, ...


texts: How does Postgres know which logs have been archived?

texts: I'm asking this because Postgres was hard-stopped yesterday, and I fear that there could be partial / corrupt data in one of my archived log segments. I'd like to simply delete the logs from my slave server and just have Postgres start again from the beginning (there are only about 90 segments, so it won't take long), but I'm not sure what to do, to get Postgres to do this.


texts: Removing Instances and SQL Server Express edition and going to Standard/Developer/Enterprise

texts:
I would like to move from SQL Server 2008 R2 Express to SQL Server 2008 R2 Developer or Enterprise.

I just noticed that I have the following services running:

SQL Server (MSSQLSERVER)
SQL Server (SQLEXPRESS)
SQL Server (SQLR1)
SQL Server (SQLR1V2)
SQL Server Agent (MSSQLSERVER)
SQL Server Agent (SQLR1)
SQL Server Agent (SQLR1V2)

What I would like to accomplish here is to maintain the databases I have in (local). How can I tell which instance this is running against?

Also, have the Express Edition completely removed and install either the Developer / Enterprise edition of SQL Server with the Management Studio interface. I ultimately want to have SQL Profiler at the end of the day.

I don't mind if I have to remove everything services, instances, management studios, etc.

As long as I can end up with 3 things:

SQL Server Developer / Enterprise
SQL Server Management Studio
SQL Profiler

Edit:
I have downloaded SQL Server 2008 R2 Developer (x86, x64, ia64) - DVD (English) from MSDN. However, according to the installation I already have Management Tools - Basic installed.

Edit2:
I actually created a new instance and I was given additional the 2 Management Tools options in the installation, as mentioned by mrdenny.


texts: Is it possible to override a ReadCommittedSnapshot isolation level with ReadCommitted isolation level for a particular transaction?

texts:
The default isolation level of my database is "read committed snapshot". Please see the below screenshot.

I have the table "Tickets' with the following data in my database.

Now in connection 1, I am starting a new transaction with isolation level as "read committed". And I'm going to update the priority to 'medium' for the #TicketID "2"

From Connection 2, I am trying to access the details of #TicketID "2". And its still taking the value from the version store which is created by the read committed snapshot isolation, which is not expected behavior.

As per documentation, if a transaction using "read committed" isolation level, the row can't be accessed by any other transaction until the transaction performs a commit/Rollback.

Can anyone tell me why it is not happening in the given scenario? I am also curious to know, why a transaction level isolation is not able to override the default isolation level (here read committed snapshot) which is set at the database level?

To give more clarity on the isolation override on the transaction scope, I'm providing the below information.

I have set my database isolation to read committed.

Now in connection 1, I am initiating my transaction to update the Tickets priority.

In connection 2, I am trying to access the record. But this will not complete since the transaction isolation level is in "read committed" mode.

In connection 3, I am trying to access the record with isolation level as "read uncommitted". Here we can see the record as since it will override the default isolation which is set at the database level.

I am expecting the same behavior when I set "Snapshot Isolation" at the database level, and overide it in another transaction scope. For me, it always returns the last committed value from the version store irrespective of the isolation level which is mentioned at the transaction scope. I hope I conveyed my concerns clearly.. :)


texts: Running Oracle Export Utility within a Shell script

texts:
I have wasted a lot of time on this.  Any ideas of what I am doing wrong here?

Script in bash

#!/usr/bin/bash
.
.
.

/export/home/app/oracle/product/10.2.0/bin/exp $uname/$passw \
    FILE="$WORKSPACE"/"$EXP_FILE" \
    LOG="$EXP_LOG" \
    TABLES=SYSTEM_PARAMETERS\
    CONSISTENT=Y \
    ROWS=Y \
    INDEXES=Y \
    CONSTRAINTS=Y \
    GRANTS=Y ;

Error Message

/export/home/app/oracle/admin/ukediuat/exp >./exp_syspar.sh
Username: USER
Enter DPDADMIN Password:

LRM-00116: syntax error at 'TABLES' following '='

EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully

Problem

The issue is with the dollar signs in the export command.  By replacing the variables which use the dollar sign with the full path the script runs.

Is there anyway to parse the shell variables into the export command?


texts: Slow query on large table with 50M plus data (especially with larger offset)

texts:
Scenario

I have a table with 50M plus data.

I need to fetch 4 columns and show on datatable order by 'name_voter' column.

First, I started with basic query such as

 select id,name_voter,home_street_address_1, home_address_city
from base_voter
where deleted_at is null
order by home_street_address_1
offset @pagesize * (@pagenumber - 1) rows
fetch next @pagesize rows only option(recompile)

With the increase in data, it started to become slow. So, I decided to create an index as

create index IX_base_voter_name_voter_asc_deleted_at on base_voter(name_voter asc,deleted_at) include(home_street_address_1, home_address_city) where deleted_at is null

It makes quite faster but not enough, so one of my friends suggest to create additional table as:

create table base_voter_name_voter
(
sr_no bigint not null,
base_voter_id int,
constraint pk_name_voter_sr_no primary key (sr_no)
)
go

and need to run following query on every insertion/deletion in the base_voter table

create procedure procUpdateSortDetails
as
begin
    truncate table base_voter_name_voter
    insert into base_voter_name_voter(sr_no,base_voter_id)
    select row_number() over(order by name_voter asc),id from base_voter with(nolock)
    where deleted_at is null
end
go

After this the execution time is acceptable, but still, I am not convinced to run that query on every update.

My question:

1) Is there any other way to make this faster without updating this additional table on every insertion manually need to be automatically updated something as in index.

2) Isn't index itself should be enough to handle tasks like this? Am I doing it incorrectly or only this much index is capable

3) Is any other way to deal with such problems, to make the query faster even for the last page?

4) If there is also some other way of representing data so that this query might not be needed. Please, mention those.

My data table is something like

name_voter (asc)     |     home_street_address_1    |    home_address_city

Please, suggest some way to tackle this problem.


texts: How to Fetch A Record If Shipping And billing Address are Different

texts: Show New Orders with Address If shipping and billing are different. I am stuck with this query. Can someone guide me a bit. Thanks


texts: How to update where subquery value is null in SQL Server

texts:
I am attempting to update a column if another value from a subquery is less than a certain value or null:

UPDATE Customer
SET PriceClassID = 'A'
FROM (SELECT custid, SUM(curybaldue) as last_sum
FROM SOShipHeader
WHERE OrdDate > @year_ago
AND Status = 'C'
GROUP BY CustID) a
JOIN Customer ON Customer.CustId = a.CustId
WHERE (last_sum < 3000 OR last_sum IS NULL)
AND PriceClassID IN ('CLUB', 'CLUB-E')
AND Customer.User7 <= @year_ago
AND Customer.User7 > @year_and_month_ago

However, when I then check if the PriceClassID was changed, I see it was not changed for any row where last_sum was null. What am I doing wrong?


texts: Select table and count aggregate information

texts:
i have the following DB
CREATE TABLE public.contacts (
    id int4 NOT NULL,
    "name" varchar NULL,
    address varchar NULL,
    CONSTRAINT contacts_pk PRIMARY KEY (id)
);

CREATE TABLE public.customers (
    id int4 NOT NULL,
    "name" varchar NULL,
    active varchar NULL,
    CONSTRAINT customers_pk PRIMARY KEY (id)
);

CREATE TABLE public.producttypes (
    code varchar NOT NULL,
    "name" varchar NULL,
    CONSTRAINT producttype_pk PRIMARY KEY (code)
);

CREATE TABLE public.attachments (
    id int4 NOT NULL,
    "name" varchar NULL,
    "path" varchar NULL,
    customer int4 NULL,
    CONSTRAINT attachment_pk PRIMARY KEY (id),
    CONSTRAINT attachment_fk FOREIGN KEY (customer) REFERENCES public.customers(id)
);

CREATE TABLE public.benefits (
    id int4 NOT NULL,
    "name" varchar NULL,
    customer int4 NULL,
    CONSTRAINT benefits_pk PRIMARY KEY (id),
    CONSTRAINT benefits_fk FOREIGN KEY (customer) REFERENCES public.customers(id)
);

CREATE TABLE public.contactincustomer (
    customer int4 NOT NULL,
    contact int4 NOT NULL,
    CONSTRAINT contactincustomer_pk PRIMARY KEY (customer, contact),
    CONSTRAINT contactincustomer_fk FOREIGN KEY (customer) REFERENCES public.customers(id),
    CONSTRAINT contactincustomer_fk_1 FOREIGN KEY (contact) REFERENCES public.contacts(id)
);

CREATE TABLE public.customerincustomer (
    parent int4 NOT NULL,
    child int4 NOT NULL,
    CONSTRAINT customerincustomer_pk PRIMARY KEY (parent, child),
    CONSTRAINT customerincustomer_fk FOREIGN KEY (parent) REFERENCES public.customers(id),
    CONSTRAINT customerincustomer_fk_1 FOREIGN KEY (child) REFERENCES public.customers(id)
);

CREATE TABLE public.products (
    id int4 NOT NULL,
    "name" varchar NULL,
    "type" varchar NULL,
    customer int4 NULL,
    CONSTRAINT product_pk PRIMARY KEY (id),
    CONSTRAINT product_fk FOREIGN KEY (customer) REFERENCES public.customers(id),
    CONSTRAINT product_fk_1 FOREIGN KEY (type) REFERENCES public.producttypes(code)
);

and i need to extract a table like this

Productid
Numbercontacts
NumberlinkedCustomer
Numberattachments
Numberbenefits
totalProducts
productsTypeA
productsTypeB

1
1
1
1
1
10
4
0

2
2
0
10
3
5
5
0

3
0
0
0
0
3
2
1

4
3
3
3
4
4
2
1

Is possible to do with one query o i have to run multiple query in a procedure?
The DB is postgreSQL
Exampele with data
http://www.sqlfiddle.com/#!15/0aff4
my only idea is create multiple query and full join, but for a lot of record (millions of customer) is really really slow
select c.id as customerID, count(c2.contact) as contactsNumber
from public.customers c
left join public.contactincustomer c2 on c2.customer = c.id
group by c.id
)  as q1
full join
(select c.id as customerID, count(a.id) as attachmentNumber
from public.customers c
left join public.attachments a on a.customer = c.id
group by c.id )   as q2 on q1.customerID = q2.customerID


texts: Using "Generate Scripts" with EMERGENCY/SINGLE USER mode

texts:
I am trying to create an INSERT script to salvage data from a single table in a corrupt database.

I've succesfully put the database online and put it in EMERGENCY / SINGLE USER mode (following this http://www.sqlskills.com/blogs/paul/disaster-recovery-101-hack-attach-a-damaged-database/
)

This allow SELECTs against the data. However, when trying to use the "Generate Scripts" tool in SSMS, I get an error because there already is a connection to the database. I assume the script tool is attempting to create a seconday (sql server local?) connection/open the database to do its work, but my SSMS session is blocking.

Is it possible to ensure that the script tool is using same connection as the already established? Or drop the connection, but keep the database in EMERGENCY mode?


texts: Running two MySQL servers that use the same data directory

texts:
The situation

I have two MySQL instances, deployed on two different remote machines.
Both MySQL instances access the same data directory, which is stored on a persistent volume (this volume is provided by Gluster, but that's not really relevant, I'm only saying this for the sake of completeness).

I'm aware that it is strongly discouraged to run two MySQL servers using the same data directory, but it's not an impossible thing to do, as long as one takes the right precautions. In order to do this, I followed the steps suggested by the official documentation ( https://dev.mysql.com/doc/refman/5.6/en/multiple-data-directories.html ), more specifically in the paragraph named Warning. To sum it up:

I'm using MySQL 5.6.
I'm using MyISAM as default engine, as required.
I specified log file names that are unique to each server.
One server is going to be read-write, while the other server is going to be read-only.

So, I set everything up, I imported a test database and everything worked flawlessly. Both servers could read data from the same data directory.

The problem

Now comes the problem. As soon as one server performs a write (by inserting a row into a table), the other server marks the table as corrupted for itself.
After looking into the problem for a while, I found out why this happened. Basically each server holds its own metadata describing each table. So, as soon as the first server updated a table, it also updated its own metadata, but it didn't update the metadata of the second server. As the second server checked the table, it noticed a mismatch between the current number of rows in the table and the number of rows it had previously saved in its metadata, therefore it marked the table as corrupted.
After some research, it turns out that the metadata is saved inside a default database named information_schema. At first, I thought of making the two server share the same information_schema, but this was nowhere to be found. As I later discovered, it's saved inside the memory of the program and there's no way you can manually update or access it.

The questions

Since the documentation tells you exactly what precautions to take, I was expecting this to actually work...or else, what's the point of that guide in the official documentation? If this couldn't be done, why would it tell you how to do it in the first place?
Most importantly, do you know how to make this work, using MySQL?
If not, do you know of any other DBMSs that allow you to obtain this result (that is, running two servers that use the same data directory)?

Just keep in mind that I don't want to set up master-slave replication.
All other suggestions are welcome, thanks everyone for the help.

EDIT: I ended up not doing it, as everyone discouraged me from doing it. Thanks everyone for your input!


texts: Table-Valued Parameter as Output parameter for stored procedure

texts:
Is it possibile to Table-Valued parameter be used as output param for stored procedure ?

Here is, what I want to do in code

/*First I create MY type */
CREATE TYPE typ_test AS TABLE
(
     id int not null
    ,name varchar(50) not null
    ,value varchar(50) not null
    PRIMARY KEY (id)
)
GO

--Now I want to create stored procedu whic is going to send output type I created,
--But it looks like it is inpossible, at least in SQL2008
create  PROCEDURE [dbo].sp_test
         @od datetime
        ,@do datetime
        ,@poruka varchar(Max) output
        ,@iznos money output
        ,@racun_stavke  dbo.typ_test   READONLY --Can I Change READONLY with OUTPUT ?
AS
BEGIN
    SET NOCOUNT ON;

    /*FILL MY OUTPUT PARAMS AS I LIKE */

    end


texts: InnoDB Data Dictionary Cleanup for a Non-Existent Database Named "tmp"?

texts:
I have a number of error messages like this in my error.log file referencing different files that can't be opened when I start my mariadb 10.4.8 server:

2019-09-30 17:38:19 0 [ERROR] InnoDB: Cannot open datafile for read-only: '/tmp/#sql50b_8808_1.ibd' OS error: 71
2019-09-30 17:38:19 0 [ERROR] InnoDB: Cannot read first page of './tmp/#sql50b_8808_1.ibd' I/O error
2019-09-30 17:38:19 0 [Note] InnoDB: Cannot read first page in datafile: ./tmp/#sql50b_8808_1.ibd, Space ID:18446744073709551615, Flags: 0
2019-09-30 17:38:19 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2019-09-30 17:38:19 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2019-09-30 17:38:19 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2019-09-30 17:38:19 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``tmp`.`#sql50b_8808_1``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2019-09-30 17:38:19 0 [Warning] InnoDB: Ignoring tablespace for `tmp`.`#sql50b_8808_1` because it could not be opened.

I don't have a database named tmp, so the instructions for data dictionary troubleshooting haven't been helpful.

I can't find any files that start with #sql anywhere in my file system, but

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE NAME LIKE '%#sql%';

returns 65 rows, including one associated with the file described above. Is there some way to clean this up so MariaDB doesn't try to open these non-existent files?


texts: SQLite3 column truncation even though column is wide enough

texts:
I have a table with schema
CREATE TABLE test(id INTEGER NOT NULL PRIMARY KEY, numbers TEXT);

and values
INSERT INTO test(numbers) VALUES('1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100');

And if I run these commands
.mode box
.headers on
.width 0 400
.once -e
SELECT * FROM test;

I get output as
┌────┬──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ id │                                                                                numbers                                                                                │
├────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ 1  │ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86 │
└────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

numbers column gets truncated even though the headers are not and the column is wide enough.
I tried increasing width value but still wouldn't work
This happens with box, column, table and markdown modes only. Other modes work fine.
What am I doing wrong?
I am using SQLite version 3.33.0 2020-08-14 13:23:32


texts: Selecting from a view shows the wrong set of columns

texts:
I'm selecting from a view in SQL Server. If I right-click on it and choose "Select Top n Rows", it runs and shows me 30 columns.  If I open the view in ALTER mode (right-click, Script View As, etc...), highlight the code and run it, I get 50 columns.  I should get 50 columns, because I just added 20 new columns to the table feeding the view, and inside the view I'm using a "SELECT * FROM..." statement.

I've refreshed the Views folder, but that didn't help.

Anyone know why the discrepancy, and how to correct it?


texts:
I have the following table (stripped to the bare essentials):
(script is in postgresql)

CREATE TABLE t_version (
  name VARCHAR(64) NOT NULL,
  major INTEGER NOT NULL,
  minor INTEGER NOT NULL,
  attr VARCHAR(64)
);

ALTER TABLE t_version
  ADD CONSTRAINT PK_VERSION
  PRIMARY KEY (name, major, minor);

And some test data:

INSERT INTO t_version (name, major, minor, attr) VALUES ('n1', 1, 0, 'a1');
INSERT INTO t_version (name, major, minor, attr) VALUES ('n1', 1, 1, 'a2');
INSERT INTO t_version (name, major, minor, attr) VALUES ('n1', 2, 0, 'a3');

I am trying to figure out, how to select the highest version from this, i.e. a select which would give me

'n1', 2, 0, 'a3'

as a result.

I know how to do that, when only the highest minor version is required:

SELECT A.name, A.major, A.minor, A.attr
  FROM t_version AS A
    JOIN (
           SELECT name, major,
             max(minor) AS minor
           FROM t_version
           GROUP BY name, major
         ) LATEST
      ON A.name = LATEST.name
         AND A.major = LATEST.major
         AND A.minor = LATEST.minor;

But I am at a loss for the more general problem.

BTW, the solution should not only work for postgresql by for sql server too.


texts: Configure SQL Server to encrypt data traffic in both directions

texts:
How can I ensure that SQL Server data traffic is encrypted over the wire both from server to client and from client to server?

I have set Force Encryption to Yes and that alone seems to hide query information from Profiler and MS Message Analyzer. Is that all I need to do? A lot of documentation states that a certificate is also required.

So I created a p12 certificate, imported it into the certificate store and selected it from the "Certificate" tab in SQL Server Configuration Manager Protocols. Then I was unable to restart the service even after removing the certificate.

I'd like to get some clear instructions on how to achieve wire encryption.


texts:
I want to output data that will display various results depending on Type and/or Quantity values.

Each query calls the same table and returns the same columns. I was thinking of creating a UNION on each one. Or should I just make a single call to database, populate a table variable from this, and then query table variable to get the desired output?

Below a sample of queries.
Thanks.

-- 1) display where type is 'detail'
SELECT Type, Description, Country, Quantity
FROM UserHoldings
WHERE (Type = 'Detail')
ORDER BY Quantity DESC;

-- 2) display where type is 'current' and positive
SELECT Type, Description, Country, Quantity
FROM UserHoldings
WHERE (Type = 'Current')
AND Quantity >= 0
ORDER BY Quantity DESC;

-- sum positive values
SELECT SUM(Quantity)
FROM UserHoldings
WHERE (Type = 'Current')
AND Quantity >= 0
GROUP BY Type;

-- 3) displaywhere type is 'current' and negative
SELECT Type, Description, Country, Quantity
FROM UserHoldings
WHERE (Type = 'Current')
AND Quantity < 0
ORDER BY Quantity DESC;

-- sum negative values
SELECT SUM(Quantity)
FROM UserHoldings
WHERE (Type = 'Current')
AND Quantity < 0
GROUP BY Type;


texts: Don't RESEED tables on Query Store Clean

texts:
We are using Query Store. And now we have solved one problem with it by cleaning the Query Store every night. And when you clean the Query Store, the Query Store tables are being RESEEDed :-(

Do you know how clean the Query Store without RESEEDing the tables?


texts: List of escape sequences for Postgresql string data types

texts:
I'm trying to find the documentation of a complete list of escape sequences for string data types in Postgresql.  So for example, if you need to escape a quote character inside of a quoted string, you would use \".  Different DBMSs use different jargon, so I'm having a hard time finding what to search for.

I found this, but it doesn't look exhaustive to me.  In this link, search for "The following special backslash sequences are recognized by COPY FROM".

http://www.postgresql.org/docs/9.5/static/sql-copy.html

EDIT:

I knew it wasn't exhaustive.  See the \\ character here:

http://www.postgresql.org/docs/8.3/static/sql-syntax-lexical.html

Then look at "4.1.2.2. String Constants with C-style Escapes" in this link.  Also read what's written in CAUTION.  This is making me humble, and I hope it is for others.

http://www.postgresql.org/docs/9.2/static/sql-syntax-lexical.html


texts: MySQLDump issue with a special characters

texts:
Code:
use test;

CREATE TABLE Test_user(Username VARCHAR(15),path VARCHAR(100));

INSERT INTO Test_user VALUES('new1','C:\newfolder\one.xlsx');
INSERT INTO Test_user VALUES('new1','C:/newfolder/one.xlsx');

I have created a .sql file using MySQLDump, which has contents

Filename : Table1.sql

DROP TABLE IF EXISTS test_user;
/*!40101 SET @saved_cs_client     = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE test_user (
  Username varchar(15) DEFAULT NULL,
  path varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/!40101 SET character_set_client = @saved_cs_client */;

LOCK TABLES test_user WRITE;
/*!40000 ALTER TABLE test_user DISABLE KEYS /;
INSERT INTO test_user VALUES ('new1','C:\newfolderone.xlsx'),('new1','C:/newfolder/one.xlsx');
/!40000 ALTER TABLE test_user ENABLE KEYS */;
UNLOCK TABLES;

in the above Table1.sql file ('new1','C:\newfolderone.xlsx') backslash before one.xlsx is not displaying , While trying to execute this file, i am getting output like this

new1    C:ewfolderone.xlsx
new2    C:/newfolder/one.xlsx

Where it is not displaying \ at all , suggest me how to resolve it and also what are special characters like this backslash will create this kind of bug.

Thanks in advance..


texts: Traversing tree-like data in a relational database using SQL

texts:
Is there a way to traverse tree data in SQL? I know about connect by in Oracle, but is there another way to do this in other SQL implementations?  I'm asking because using connect by is easier than writing a loop or recursive function to run the query for each result.

Since some people seem to be confused by the phrase "tree data" I will explain further: What I mean is with regards to tables which have a parent_id or similar field which contains a primary key from another row in the same table.

The question comes from an experience where I was working with data stored in this way in an Oracle database and knew that the connect by isn't implemented in other DBMSs.  If one were to use standard SQL, one would have to create a new table alias for each parent one would want to go up.  This could easily get out of hand.


texts: SSMS Permission Denied, Login with Windows Authentication

texts:
My SSMS(SQL Server Management Studio) version 16.4, Windows 7. First install is normal use, sometimes i reinstalling my Windows and just now i install SSMS again, and got this problem.
I cannot do anything except view.

Object Explorer & Registered Servers Display

BUILTIN\Users (ServerRole: Public) > using windows authentication
sa (ServerRole: Public, Sysadmin) > SQL Server Authentication (with default password)

I cannot create any database, restore or anything that change the content of my server. Also i have try to Add new login, and i don't have permission to create new login. I run the SSMS as Administrator, but i still cannot do anything.

Anyone know how to add new logins from out of application. Maybe i can connect to Server with SQL Server Authentication that have all permission that required. Or any way to solve this permission problem.

Thanks

Attach Database -> Add

Restore Database

Try something to do

Error when add ServerRole to my BUILTIN\Users

Connection Properties if needed.


texts: SQL Server 2016 - “Always on” issue on secondary server , redo thread blocked

texts:
I need help for an always-on issue after upgrading to 2016 from 2014. Redo thread is causing blocking for all the select query on the secondary replica. all long running select queries are getting blocked by redo thread for a specific database. All DBs are already in sync. redo thread is getting blocked once I am running SQL job to fetch the data from secondary database.. redo queue size is also 0 and DB also synchronized before running the job. read routing URL also configured. Has anyone faced such a scenario? Please help with this.

I have tried multiple things to rid of deadlock issue and enabled the Is_Read_Commited_Snapshot option as well but still, that REDO thread ( DB sync) is blocked.


texts: MySQL count time between each in/out and show total

texts:
I am having an issue with my time clock query. Basically users clock in/out several times per day (can clock in/out unlimited times per day), and it is counting the time between the first clock in and last clock out and I need it to count time between each in out and add up to provide result.

SELECT
    user,
    '2020-01-01' as date,
    'Total Hours for Week:' as WeekDay,
    SUM(Minutes) as Minutes
    FROM (SELECT
            tc.user,
            DATE(tc.timeinout) as date,
            CASE
                WHEN WEEKDAY(tc.timeinout)=0 THEN 'Monday'
                WHEN WEEKDAY(tc.timeinout)=1 THEN 'Tuesday'
                WHEN WEEKDAY(tc.timeinout)=2 THEN 'Wednesday'
                WHEN WEEKDAY(tc.timeinout)=3 THEN 'Thursday'
                WHEN WEEKDAY(tc.timeinout)=4 THEN 'Friday'
                WHEN WEEKDAY(tc.timeinout)=5 THEN 'Saturday'
                WHEN WEEKDAY(tc.timeinout)=6 THEN 'Sunday' END AS WeekDay,
            TIMESTAMPDIFF(MINUTE,tcmin.min,tcmax.max) AS Minutes
        FROM timeclock AS tc
        LEFT JOIN (SELECT
                    user,
                    MIN(timeinout) as min
                  FROM timeclock
                  GROUP BY user,DATE(timeinout)) as tcmin
            ON (tcmin.user=tc.user AND DATE(tcmin.min)=DATE(tc.timeinout))
        LEFT JOIN (SELECT
                    user,
                    MAX(timeinout) as max
                  FROM timeclock
                  GROUP BY user,DATE(timeinout)) as tcmax
            ON (tcmax.user=tc.user AND DATE(tcmax.max)=DATE(tc.timeinout))
        WHERE
           tc.timeinout>= :startdate
            AND tc.timeinout< :enddate
            AND tc.user= :user
        GROUP BY tc.user,DATE(tc.timeinout)
        ORDER BY date ASC) AS Total
    UNION ALL
    SELECT
    tc.user,
    DATE(tc.timeinout) as date,
    CASE
        WHEN WEEKDAY(tc.timeinout)=0 THEN 'Monday'
        WHEN WEEKDAY(tc.timeinout)=1 THEN 'Tuesday'
        WHEN WEEKDAY(tc.timeinout)=2 THEN 'Wednesday'
        WHEN WEEKDAY(tc.timeinout)=3 THEN 'Thursday'
        WHEN WEEKDAY(tc.timeinout)=4 THEN 'Friday'
        WHEN WEEKDAY(tc.timeinout)=5 THEN 'Saturday'
        WHEN WEEKDAY(tc.timeinout)=6 THEN 'Sunday' END AS WeekDay,
    TIMESTAMPDIFF(MINUTE,tcmin.min,tcmax.max) AS Minutes
    FROM timeclock AS tc
    LEFT JOIN (SELECT
            user,
            MIN(timeinout) as min
          FROM timeclock
          GROUP BY user,DATE(timeinout)) as tcmin
    ON (tcmin.user=tc.user AND DATE(tcmin.min)=DATE(tc.timeinout))
    LEFT JOIN (SELECT
            user,
            MAX(timeinout) as max
          FROM timeclock
          GROUP BY user,DATE(timeinout)) as tcmax
    ON (tcmax.user=tc.user AND DATE(tcmax.max)=DATE(tc.timeinout))
    WHERE
    tc.timeinout>= :startdate
    AND tc.timeinout< :enddate
    AND tc.user= :user
    GROUP BY tc.user,DATE(tc.timeinout)
    ORDER BY date ASC

user   min                   max                   WeekDay     Minutes
----   -------------------   -------------------   ---------   -------
1      2015-07-27 20:20:32   2015-07-27 20:29:25   Monday      8
1      2015-07-28 20:32:04   2015-07-28 20:36:28   Tuesday     4
1      2015-07-29 20:40:00   2015-07-29 20:45:39   Wednesday   5
1      2015-07-30 20:20:32   2015-07-30 20:20:56   Thursday    0
1      2015-07-31 20:29:25   2015-07-31 20:32:04   Friday      2
1      2015-08-01 20:36:28   2015-08-01 20:40:00   Saturday    3
1      2015-08-02 20:20:32   2015-08-02 20:40:00   Sunday      19


texts:
Bit of a headscratcher here.

I have a db that has less than 1GB of data, but a 40GB log file.
Transaction logs are backed up daily, and there is not a lot of activity on this database; approximately once a week it records new payroll information and then regurgitates this data for reporting purposes.
The database is set to Auto Shrink.

running sp_spaceused  @updateusage = true yields the following information:

database_name   database_size   unallocated space
PayrollImports  39412.06 MB 105.00 MB

reserved    data    index_size  unused
321728 KB   278640 KB   42816 KB    272 KB

running DBCC shrinkfile (N'PayrollImports_log', 1 , notruncate) yields the following:

DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
19  2   4991088 3456    4991088 3456

...the discrepancy between the UsedPages and the EstimatedPages is mystifying, but I continue with DBCC shrinkfile (N'PayrollImports_log', 1 , truncateonly) and get:

DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
19  2   4991088 3456    4991088 3456

Nothing has changed at this point.  The log file is still 40GB.  So I think, maybe I have some open transaction.  Running dbcc opentran should verify:

No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Crap.  Well maybe my indexes are fragmented.  I will defrag them with sp_msForEachTable 'DBCC indexdefrag([PayrollImports], ''?'')' and try to shrink again:

DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
19  2   4991088 3456    4991088 3456

Still nothing has changed.  Ok, how about I reindex with sp_msForEachTable 'DBCC dbreindex([?])'?

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

...and now we get:

DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
19  2   4991088 3456    4991088 3456

no change.  All right, how about sp_msForEachTable 'ALTER INDEX ALL ON [PayrollImports].[?] REBUILD WITH (FILLFACTOR = 10)'?

Immediately, this fails with:

Cannot find the object "(One of my tables)" because it does not exist or you do not have permissions.

Huh?  It's there, all right.  I do a select top 10 * from (My table) and it comes up empty.  Well, that's not right at all.  This is a lookup table that should have over 200 rows.  Is this a data corruption issue maybe?  I collect the data from my development environment, re-insert it in.

But I am out of ideas.  I cannot shrink this thing.  What else can I try?  Why are my UsedPages so incredibly higher than my EstimatedPages?  What is going on here?


texts:
The Douglas and Douglas PostgreSQL book includes the following EXPLAIN result:

The first step of the query, the Seq Scan, shows a width of 1917. However the last step of the query, the Sort, has a width of 1911.

Why/how could these values differ?


texts: SQL to select all siblings with specific child

texts:
I have a simple parent-child hierarchical arrangement using two tables: Assemblies (parents) and Parts (children). An assembly consists of one or more parts.

| AID |     A_NAME |
|-----|------------|
|   1 | Assembly X |
|   2 | Assembly Y |
|   3 | Assembly Z |

| PID | P_NAME | AID |
|-----|--------|-----|
|   1 | Part A |   1 |
|   2 | Part B |   1 |
|   3 | Part C |   1 |
|   4 | Part D |   1 |
|   5 | Part A |   2 |
|   6 | Part B |   2 |
|   7 | Part C |   3 |
|   8 | Part D |   3 |

To show the complete list of Assemblies with their Parts, I can run the following SQL query:

SELECT a.*, p.*
FROM assemblies a
LEFT JOIN parts p USING (AID);

and it returns:

| AID |     A_NAME | PID | P_NAME |
|-----|------------|-----|--------|
|   1 | Assembly X |   1 | Part A |
|   1 | Assembly X |   2 | Part B |
|   1 | Assembly X |   3 | Part C |
|   1 | Assembly X |   4 | Part D |
|   2 | Assembly Y |   5 | Part A |
|   2 | Assembly Y |   6 | Part B |
|   3 | Assembly Z |   7 | Part C |
|   3 | Assembly Z |   8 | Part D |

Now, if I only want to return Assemblies that include a specific Part (say 'Part B'), then I could run this query:

SELECT a.*, p.*
FROM assemblies a
LEFT JOIN parts p USING (AID)
WHERE p.P_NAME='Part B';

which returns:

| AID |     A_NAME | PID | P_NAME |
|-----|------------|-----|--------|
|   1 | Assembly X |   2 | Part B |
|   2 | Assembly Y |   6 | Part B |

However, what I'd really like to see is all the sibling parts* as well:

| AID |     A_NAME | PID | P_NAME |
|-----|------------|-----|--------|
|   1 | Assembly X |   1 | Part A |*
|   1 | Assembly X |   2 | Part B |
|   1 | Assembly X |   3 | Part C |*
|   1 | Assembly X |   4 | Part D |*
|   2 | Assembly Y |   5 | Part A |*
|   2 | Assembly Y |   6 | Part B |

Any ideas on how I can achieve this? That is, for a given part, return the assemblies it belongs to, along with all the other parts in the assembly.

I'm using MySQL, and here's an sqlfiddle if it helps.


texts:
I have InnoDB table with two columns (1) account(int) (2) comment(varchar).

I have index on (account,comment)

Now I want to run query like

select * from table where account = 1111 AND comment NOT LIKE 'abcd%';

How can i make the NOT LIKE null safe?

I mean, include all records whose comment not starts with 'abcd', even if its null value.


texts:
How does running PostgreSQL on a Windows desktop with pgAdmin III compare to running it on a dedicated server? I am trying to develop a database with very large tables (~60 million rows) with the following hardware:

64-bit Windows 7
Dual quad-core Intel Core i7-4770 CPU @ 3.40 GHz
32 GB RAM

I would like to know if it would be worthwhile to invest in a server or whether the desktop should be sufficient. The tables will be static, so I mainly concerned with the time it will take to read the data and perform basic aggregate and join operations.


texts: May ask for the difference of MySQL Audit log plugin and general log? I looking for any activities done in a MySQL server.


texts: How to select only first column dynamically on mysql

texts:
I have to select only first column. Because of i dont want to select all columns like SELECT * FROM table. But my first columns may vary like following:

id
article_id
user_id
product_id
page_id
etc..

Is there any way always getting only first column. For example:

SELECT 0 FROM table;

or

SELECT FIRST_COLUMN FROM table;


texts:
I have 3 tables:

TASK (id_task (PRIMARY KEY), name)
PROJECT (id_project (PRIMARY KEY), name)
PROJECT_HAS_TASK (id_project, task_id)

PROJECT_HAS_TASK is the (n,m) relation between PROJECT and TASK.

I'd like to return all the TASK records and a boolean value indicating if the task_id also exists in PROJECT_HAS_TASK. Is that possible to do with a SQL query? Or should I use a stored procedure?

EDIT :
Yes it is possible to do it with a SQL query. Thanks to both a1ex07 and bummi's help I ended up with this solution :

SELECT a.id_task,
Case WHEN b.id_task IS NULL then 0 else 1 end as HasProject
from TASK a
LEFT join (SELECT DISTINCT id_task FROM PROJECT_HAS_TASK)b on (a.id_task = b.id_task);


texts: Need to sum a field in a row with same it and result needs to be cumulative in a field


texts:
I am running MySql innodb with innodb_file_per_table.

As a daily script, I would like to create a new partition for a set of tables, as well as delete yesterday's partitions. What command would need to be run to delete the old partitions to free up the disk space?  I would like to avoid having a system that runs out of disk space because of the database disk usage.  I would also like to avoid having to shut down Mysql.

Edit: I have read that discarding the tablespace should delete the .ibd files that take up most of the space.

ALTER TABLE tbl_name DISCARD TABLESPACE;

However there is a known bug using this command on partitioned tables.


texts: Syncing data from primary member to secondary after election

texts:
Maybe this is kind a stupid question but, I'm novice in configuring replication and I need to clear something before makeing anything else.
Moment ago I configured replica set with 3 members in it. It's all working well but I think I don't understand the part about copying data from primary to secondary member of replica set after new election.

Let's say I made one collection in my primary member, after that kill instance of primary member, election has been made and now I have new instance as primary member on example --port 27018.
What happend with all data on first instance which I killed? Do I need to make mongodump? Do I need to use some sync method for instant syncing or read the oplog and how to do that? Beacause, I tought that if I made an replSet, and turn on all three instances which are connected, all of them will listening the same oplog and after election update the database, or I'm completly wrong about that and I'm clearly missing some things?

Thank you!


texts: MySQL Workbench can either Dump or Restore - How Do I Configure To Do Both?

texts:
I have previously posted this question on Stack Overflow, but have not solved the problem. Hopefully it is not incorrect to post it here in the hope that it will be seen by a some different individuals who have encountered the problem and can explain what is happening and have a solution.

Having used MySQL on a previous laptop running Linux Mint 13 I recently installed it on a newer laptop (also running Mint 13). Although I could carry out a Data Dump via MySQL Workbench a Data Import/Restore fails when it encounters a larger BLOb (of ~1.9 MB) with Error 2006 (HY000) : MySQL server has gone away. The same error is reported if I try to add the BLOb via my Python 2.7 app. After talking with a former colleague I edited /etc/mysyl/my.cnf. The section I changed was:-

[mysqld]
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
explicit_defaults_for_timestamp

to:-

    [mysqld]
    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
    explicit_defaults_for_timestamp
    max_allowed_packet = 128M
    net_buffer_length = 32M
    wait_timeout    = 6000

   [mysqldump]
   user            = mysql
   max_allowed_packet = 128M
   net_buffer_length = 32M

After a reboot the Data Import/Restore worked (as did the Python app) but a Data Dump fails with cannot parse version from mysqldump. My former colleague was thrown by this and I am now feeling somewhat confused. Changing /etc/mysql/my.cnf and rebooting every time you want to carry out a different action is not really viable. How should I edit /etc/mysql/my.cnf so that Dumps and Restores work consistently.

Thank you...


texts:
I would like to concatenate multiple values and remove duplicates (case-insensitive) in the same field with SQL only.

Without using a unique key or stored procedure.

Ex.

Toto;toto1;titi;TOTO;toto1;tata;

in

Toto;toto1;titi;TOTO;tata;

Here is my SQL model:

UPDATE users SET pseudo = CONCAT_WS(';', pseudo, 'NEWS_VALUE') WHERE id = '1';

I can not handle duplicates

Thank you for your help


texts: PostgreSQL buffer page size limitations - rebuild with larger size?

texts:
We just ran into a limit on indexing due to the 'index size cannot be more than 1/3 of page size (2712)' error.

This indicates the buffer page size is 8192 (8k).  This seems awfully small during the terabyte era.

This does not seem to be a parameter we can reset in a config file.  I can download, build, and install it if needed.

What are the performance considerations?  I would think we might benefit substantially on performance with a larger buffer page size.  Can someone experienced with PostgreSQL database tuning offer an opinion?

Thanks,

Andrew Wolfe


texts: I'm developing a SQL Server table that will store audit trail data.  I know that this table will get large as the system is used over time.  Is it worth partitioning the table by year when the system is first configured?  Or worth waiting a year or so in order to keep the performance as high as possible?


texts:
I have downloaded and started up Oracle's pre-built OTN Developer Day VM. The VM is running on an SSD. I want to move datafiles that can grow to a separate mount point (setup as a vmdk on a HDD. more details here ).

I am looking at moving the following to the HDD mount point:

USERS tablespace
Redo log files

Should datafiles like temp or undo also be moved if I want to minimize the growth of the VM on the SSD ?


texts: T-SQL How to Pivot on the Values of a Table?

texts:
How do you pivot a table on the value in the field? I have to admit that while I can write some pretty nifty queries, have my CTEs down, and enjoy a wide knowledge of T-SQL programming, Pivots get into a wonky area that I am uncomfortable and unfamiliar with.

Let me describe my issue, and see if you can help.

I have the following tables:

CREATE TABLE [dbo].[Itinerary]
(
   [ItineraryID] [int] IDENTITY(1,1) NOT NULL primary key,
   [RoadTripID] [int] not NULL,
   [ItineraryDate] [date] not NULL,
   EstimatedMiles int not null,
   Destination varchar(64) not null,
   DestinationState varchar(2) not null,
   DayTypeID int not null
)

Create Table dbo.DayType
(
   DayTypeID int primary key identity,
   DayTypeCode varchar(2) not null,
   DayTypeDescription varchar(32) not null
)

DayType is a helper table with nine values:

Insert into DayType
values
  ('XX', 'Planning/Prep'),
  ('SE', 'Short Event'),
  ('LE', 'Long Event'),
  ('WD', 'Writing Day'),
  ('TD', 'Tech Day'),
  ('HD', 'Hiking Day'),
  ('PD', 'Photography Day'),
  ('LD', 'Layover Day'),
  ('LH', 'Long Haul')

When I enter a row into Itinerary it looks like this:

insert into itinerary
values(1, '11/27/2014', 361, 'Amarillo', 'TX', 1)

The last piece of the puzzle (and the question itself) is that I want to report out on how many of each type of day I've planned.

I am currently using the tried-and-true-(and awful) method:

Select
   s.ItineraryID, R.ShortName as RoadTripName,
   sum(EstimatedMiles) as TotalMiles,
   count(itineraryid) as TotalDays,
   (select count() from Itinerary i1 with (nolock) where daytypeid = 1 and i1.itineraryid = s.itineraryid) as 'Planning/Prep',
   (select count() from Itinerary i2 with (nolock) where daytypeid = 2 and i2.itineraryid = s.itineraryid) as 'Short Event',
   (select count() from Itinerary i3 with (nolock) where daytypeid = 3 and i3.itineraryid = s.itineraryid) as 'Long Event',
   (select count() from Itinerary i4 with (nolock) where daytypeid = 4 and i4.itineraryid = s.itineraryid) as 'Writing Day',
   (select count() from Itinerary i5 with (nolock) where daytypeid = 5 and i5.itineraryid = s.itineraryid) as 'Tech Day',
   (select count() from Itinerary i6 with (nolock) where daytypeid = 6 and i6.itineraryid = s.itineraryid) as 'Hiking Day',
   (select count() from Itinerary i7 with (nolock) where daytypeid = 7 and i7.itineraryid = s.itineraryid) as 'Photography Day',
   (select count() from Itinerary i8 with (nolock) where daytypeid = 8 and i8.itineraryid = s.itineraryid) as 'Layover Day',
   (select count(*) from Itinerary i9 with (nolock) where daytypeid = 9 and i9.itineraryid = s.itineraryid) as 'Long Haul'
from
   RoadTrip r
inner join
   Itinerary s on r.roadtripid = s.roadtripid
group by
   s.itineraryid, r.shortname

which returns:

Obviously (hopefully!) there needs to be a better way. How can I pivot those nine values, and their total count each into this query?


texts:
I recently came up with this and I am not sure if it is even possible to do in Mysql. The idea is to create a forecast/prediction of expenses based on last 3 months also adding some conditions for the values. Currently I created two working queries, but I am unable to transform my vision into the query for the prediction table. The questions are : Is it possible to achieve this? And is this possible to make it one query. Any hints will be appreciated. I am willing to use this in my PHP script, but I want to reduce the query amount and preferably have it later translated into a SP.

The query logic.

Getting the average expense cost of last 3 month
Display Verified sales per month
Forecast expenses for next year ( and include corrections )

Display Verified sales per month output

+-----+-----+-----+-----+-----+--------+--------+----------+----------+----------+----------+------+
| jan | feb | mar | apr | may |  june  |  july  |   augu   |   sept   |   oct    |   nov    | dece |
+-----+-----+-----+-----+-----+--------+--------+----------+----------+----------+----------+------+
|   0 |   0 |   0 |   0 |   0 | 387.71 | 387.71 | 71026.92 | 43914.10 | 61683.26 | 20898.04 |    0 |
+-----+-----+-----+-----+-----+--------+--------+----------+----------+----------+----------+------+

Forecast logic

We first get the average expense cost of last 3 month, and each forecast month should be increased by a given percentage

set @default_percentage = 1.03;

The percentage may change for a given month based on the table forecast_correction

+--------+---------+-------+------+-------+
| PK_cor | method  | month | year | value |
+--------+---------+-------+------+-------+
|      1 | add     |     2 | 2018 |   150 |
|      2 | percent |     2 | 2018 |     2 |
+--------+---------+-------+------+-------+

We forecast the expenses for next year,

41290.65 : the average expense cost of last 3 month
1.03 : @default_percentage = 1.03 variable

Prediction table - basic ( desired output )

    +----------+---------------+------------+------------+------------+------------+-------------+-------------+-------------+-------------+------------+------------+
    |   jan    |      feb      |    mar     |    apr     |    may     |    june    |    july     |    augu     |    sept     |     oct     |    nov     |    dece    |
    +----------+---------------+------------+------------+------------+------------+-------------+-------------+-------------+-------------+------------+------------+
    | 41290.65 | 41290.65*1.03 | feb * 1.03 | mar * 1.03 | apr * 1.03 | may * 1.03 | june * 1.03 | july * 1.03 | augu * 1.03 | sept * 1.03 | oct * 1.03 | nov * 1.03 |
    +----------+---------------+------------+------------+------------+------------+-------------+-------------+-------------+-------------+------------+------------+

But as we have some entries in the forecast_correction table

The calculations should be made as following for February ( since we have month : 2 in the table )

41290.65 - that's our value from January
+ ( method : add PK_cor 1 ) the value 150
* 1.02 ( the @default_percentage changed from 1.03 to 1.02 )

In other cases it remains the same -1 month * 1.03

MCVE : https://www.db-fiddle.com/f/5FtV24czfAfUiE1dj5EeYt/23


texts: DBForge can't connect with MySQL server, server is running

texts:
I recently installed the latest version of MySQL Server.

I initialized it with --initialize-insecure so I have a "root" user with no password set.

The server is running, with the command mysqld --console I am able to start it.

I can connect to it through MySQL workbench just fine, port 3306, user "root", password not set

However if I try to use DBForge v7.2.34 the connection is refused, how can I solve this or at least debug the problem?


texts: InnoDB AUTO_INCREMENT resetting itself without MySQL restart

texts:
We've recently noticed the following scenario happening in many of our databases:

A record is inserted, and receives an AUTO_INCREMENT primary key.
The record is deleted.
Another record is inserted, and receives the same AUTO_INCREMENT id as the deleted one.

I'm aware that this will happen for InnoDB tables (which is what these are) if MySQL restarts between setps 2 and 3, but it is not restarting. Is there any other event that will cause the AUTO_INCREMENT counter to be re-initialised? The time period between 2 and 3 can be a few minutes or up to a month.

Over the past month we've had some performance issues that we have so far resolved my increasing table_open_cache. The server has up to 8000 databases in use at any one time, and we found that we had a lot of processes in the "Opening tables" and "Closing tables" states. Could this be related?

Edit in response to Rick James' comment regarding FLUSH TABLES

The MySQL documentation (under "InnoDB AUTO_INCREMENT Counter Initialization") states that:

  If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk.

This is the counter that is initialised to MAX(id) + 1 on restart. However, the glossary entry for the data dictionary states that:

  This metadata is physically located in the InnoDB system tablespace.

This in turn links to the glossary entry for the system tablespace, which states that it is stored in "One or more data files (ibdata files)". This is contradictory but  I'm inclined to believe the InnoDB AUTO_INCREMENT documentation. If the counter were stored on disk in the system tablespace, there would be no need to initialise it with MAX(id) + 1.

If this is the case, then FLUSH TABLES would have no effect, but if the counter is persisted to the system tablespace then it should work. Does anybody know which part of the documentation is correct?


texts: SQL Server database design with foreign keys

texts:
See the following link for the (total) databse design:
https://sqldbm.com/Project/SQLServer/Share/_AUedvNutCEV2DGLJleUWA

I am not used to the database design first and then build it. But... for everything there is a first time... and I try to do make a database that is using standards and is using the power of SQL Server the correct way.

I am trying to create a database design where foreign keys are used.

The data for the tables documentsets_metadata, documentset_casenumbers, documentset_costs, documentset_controls & link_id cannot have data for documentset_id if there isnt a row with the same ID in documentsets.

The tables routes, route_details, cotg & planetpress_controls can have data without the documentset_id id being present in documentsets.

All the tables mentioned above (except for the documentsets_metadata table) have a 1:m relationship (Where documentsets is the parent table).

The tables below documentsets are the following:
subsets, subdocuments, subdocuments & documents.

the tables subsets, subdocuments, documents all have an m:m relationship so I thought the best way to create a database to get unique and correct that is to put a link table in between called subdocuments_documents.

Now I thought within this link table I only put foreign key references so that all the data in this table is always present in the subtables and when a row in subset is removed all the data in those subtables are also removed.

Do you guys think I am on the right track or I am totally going the wrong way?

Any help is appropriated!


texts: As a DBA, what is the best way to read the audit records on daily basis? Say, i'm the administrator and I would like to view the audit records made by the DBAs. I have enabled audit sys operatiosn to TRUE and and it creates records for the same but is there any easy readable format to clearly list out the changes made by the DBAs?


texts: Why can't relational databases meet the scales of Big Data?

texts:
It is often repeated that the Big Data problem is that relational databases can not scale to process the massive volumes of data that are now being created.

But what are these scalability limitations that Big Data solutions like Hadoop are not bound to? Why can't Oracle RAC or MySQL sharding or MPP RDBMS like Teradata (etc.) achieve these feats?

I am interested in the technical limitations - I am aware that the financial costs of clustering RDBMS can be prohibitive.


texts: Optimize command issuing "'View is not base table" and "Corrupt" error messages

texts:
I have no experience with MySQL. My boss ran an optimize against a MySQL database. Against a bunch of views we got the message

  "tablexyz" is not BASE TABLE

and the next has message is

  Corrupt

It seems to be only against views, we didn't get any of these error messages against the base tables.

Does this look like an incorrect error message, or do we have issues with our tables?


texts: Replication : Delete from Master Database

texts:
Is it possible to delete certain data from Master Database and
retain that data in the slave ?

For, example I want to delete data of before 5 years from master.
And I'm sure that data is not being used in any current transactions.

Will this cause any synchronization problems ?

Is it not recommended ?

I researched for it, but couldn't find any perfect solution !


texts: Group by WEEK but show a more readable the result?


texts: SQL query to update value based on email address in different table joined by id

texts:
Please could someone help me construct an SQL query for the following...

I need to update the value 'ucr' in an existing table (db_table_1) with the 'ucr' uploaded to a temporary table (dummy_table) by matching the email address up with the email address that actually exists in a different table altogether (db_table_2).

Both db_table_1 and db_table_2 are joined by id.

db_table_1

id     | blah1  | blah2  | ucr
-------+--------+--------+----------
10001  | abc    |   33   |  VALUE1
10002  | def    |   44   |  VALUE2

db_table_2

id     | blah1  | blah2  | email
-------+--------+--------+----------
10001  | tyu    |   33   |  1@1.com
10002  | fgt    |   44   |  2@2.com

dummy_table

ucr    | name  | email
-------+-------+------------
10001  | abc   |   1@1.com
10002  | def   |   2@2.com

So something like:-

Update 'ucr' in db_table_1 with 'ucr' in dummy_table for 'id' which matches 'email' in db_table_2 in dummy_table. Sorry not sure of the correct syntax when having to join two tables and cross reference another column. Apologies if not explained correctly.


texts: List all users on SQL Server for auditing

texts:
I'm using SQL Server Management Studio 17 and I'm trying to list all the users for auditing in an organized way with mentioning their database name.

I tried the query below but it's showing the users in different schedules and some users are duplicated.

exec sp_MSforeachdb 'select * from ?.sys.sysusers'

What could I use/try instead?


texts: Multiple queries in merge into statement

texts:
I have a scenario where i want to insert record if it doesn't exist in DB2.
If it already exists update is_active column to 0 of the existing row and insert the new row with is_active as 1.

I cannot use merge into as i cannot run 2 queries in when matched section.

How can i achieve this in batch.

If i were to run queries one by one i could have run them. But since there are millions of such records i want to do this in batch.

I want to do this using java prepared statement.


texts:
I have a situation:
I have a dmp file and a database with old data
server crashed and the latest data we have ar in the dmp file!
we managed to pull the server back but it has old data!
so we need to restore from the dmp!
how to import back the database from dmp file and replace all data and structure (tables schemes)...

I know it's RMAN's job but we do not have it in place, and it's urgent case!


texts: What is the best practice for backing up a database in Simple mode?

texts:
I am trying to create a SQL Job for backing up my database.  I want to have:

A weekly full backup
A daily differential backup
Something that will clean up backups that are older that 14 days

I started doing this with the Maintenance Plan, but decided managing it through jobs would be more flexible.

My questions are: how do I name the backup files?  Do I name them with a date naming convention and use the same convention to delete old files?  Is there an easier way?

If I'm over thinking this and should be using a Maintenance Plan, I would appreciate that answer as well.


texts: Can we retrieve historical database growth report for MySQL?

texts: Can someone share a method on how can I retrieve the database growth report from the date of creation till current for MySQL? Coming from Oracle background, I understand for Oracle we can do a script to query the creation date or from the snapshot. But I don't now if MySQL has a similar table/view which I can query and check from.


texts: There's a SQL trace file being populated every day. How can I find the routine running the trace file populating it with data?

texts: So I found a strange folder inside my SQL Server. It's called Analysis and most of its files was not updated since 2015. but there are 2 trace files being updated daily. theres some useless information inside it ( only start date and end date of something ) and a TXT file with only errors. how can I find what is starting this trace ? can't find it under traces, or schedules taks on SO.


texts: Easy way to check connectivity to SQL Server from client

texts:
For troubleshooting purposes, I would like to be able to check if a client can connect to a SQL Server instance, independent of the application that possibly can't connect to the SQL Server.

Is there an easy way (that means, not having to install 3rd party software) to do this using the default Windows system tools? Perhaps using scripts or network applications?


texts: How to check if a column in a query can produce NULL?

texts:
I am scheming how to implement a type generator for arbitrary SQL queries.
I found that for any query, the simplest way to get the types of every column is to create a temporary table and then inspect that table. However, this tells nothing about which columns can be NULL.
I am interested to find a solution to identifying which columns can be NULL for any arbitrary query with the goal of generating types for that query.
What’s the way to go about it?


texts: Can't upgrade a SQL Express to SQL Standard

texts:
I have a 64 bit SQL Server 2008 Express R2 installed and I cannot upgrade it to a fully licensed 2014 Standard. When I try to update to SP 4, I get this issue:

If I try to run the upgrade edition, notice there is no "Instance Select" option for me to select my instance:

However, I am able to connect and run to the database, and get the properties:

So my question is, I'm able to use the existing SQL express instance, but I cannot upgrade the service pack or upgrade the edition. I'm at a loss here. Any thoughts? Below is the summary of the install log:

Overall summary:
  Final result:                  Passed
  Exit code (Decimal):           0
  Start time:                    2015-08-11 12:12:19
  End time:                      2015-08-11 12:19:27
  Requested action:              EditionUpgrade

Machine Properties:
  Machine name:                  xxxxxxxx
  Machine processor count:       8
  OS version:                    Windows 8
  OS service pack:
  OS region:                     United States
  OS language:                   English (United States)
  OS architecture:               x64
  Process architecture:          64 Bit
  OS clustered:                  No

Product features discovered:
  Product              Instance             Instance ID                    Feature                                  Language             Edition              Version         Clustered  Configured
  SQL Server 2008 R2   xxxxDBSERVER         MSSQL10_50.xxxxxBSERVER        Database Engine Services                 1033                 Express Edition      10.50.1600.1    No         Yes
  SQL Server 2008 R2                                                       Management Tools - Basic                 1033                 Express Edition      10.50.1600.1    No         Yes

Package properties:
  Description:                   Microsoft SQL Server 2014
  ProductName:                   SQL Server 2014
  Type:                          RTM
  Version:                       12
  SPLevel:                       0
  Installation location:         E:\x64\setup\
  Installation edition:          Standard

User Input Settings:
  ACTION:                        EditionUpgrade
  CONFIGURATIONFILE:             C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\20150811_121219\ConfigurationFile.ini
  ENU:                           true
  FEATURES:                      BROWSER
  HELP:                          false
  IACCEPTSQLSERVERLICENSETERMS:  true
  INDICATEPROGRESS:              false
  INSTANCENAME:                  <empty>
  PID:                           *****
  QUIET:                         false
  QUIETSIMPLE:                   false
  UIMODE:                        Normal
  X86:                           false

  Configuration file:            C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\20150811_121219\ConfigurationFile.ini

Detailed results:
  Feature:                       SQL Browser
  Status:                        Passed

Rules with failures:

Global rules:

There are no scenario-specific rules.

Rules report file:               C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\20150811_121219\SystemConfigurationCheck_Report.htm


texts: SSRS 2016 : HTTP Error 503. The service is unavailable

texts:
We have SSRS 2016 on Windows Server 2016.

Reporting Server configured with virtual directories

http://ServerName:80/Reports
http://ServerName:80/ReportServer

While I'm trying to connect to reports via there URLs I get the following error messages:

For the first URL, I get the one below:

  This error (HTTP 500 Internal Server Error) means that the website you
  are visiting had a server problem which prevented the webpage from
  displaying.

And this for the second URL:

  HTTP Error 503. The service is unavailable.

Possible solutions I tried to fix these issues

Installed latest update for SQL Server 2016.
Tried with different virtual directories 000 Deleted "Encrypted Content" and restarted SSRS service.
Changed SSRS service account to domain account
validated rsreportserver.config

<Add Key="SecureConnectionLevel" Value="0"/>

<UrlString>http://+:80</UrlString>  -------Tried putting server name / IP instead of + sign

<IsWebServiceEnabled>True</IsWebServiceEnabled>
Validate the login connecting to the database is member ofRSExecRole in master, msdb ReportServer & ReportServerTempDB databases.

Please suggest what I'm missing to fix the error.

Thanks!


texts: Unusual issue with localhost:3000 after watching ruby on rails 4 tutorial: Lynda.com

texts:
I'm totally new to programming, and I've started my training by watching the ruby on rails 4 essential training tutorial at lynda.com. I got to the section where I was supposed to create a web server by first opening the code in my database.yml file, then entering my password for mysql, then starting a server by typing "rails server" in the command line. It didn't work at localhost:3000. I tried granting privileges to the databases in mysql to the account, and I even tried changing the password, but it still doesn't work. When I go to localhost:3000, it still says : Access denied for user 'root'@'localhost' (using password: NO).

Also, I have a host name written in the code, but no socket. I don't know if this changes anything, I may just have a newer version of mysql than the guy in the video.

Here is the code:

# MySQL.  Versions 4.1 and 5.0 are recommended.
#
# Install the MYSQL driver
#   gem install mysql2
#
# Ensure the MySQL gem is defined in your Gemfile
#   gem 'mysql2'
#
# And be sure to use new-style password hashing:
#   http://dev.mysql.com/doc/refman/5.0/en/old-client.html
development:
  adapter: mysql2
  encoding: utf8
  database: #my_cms_development
  pool: 5
  username: root
  password: snapple187
  host: localhost
# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  adapter: mysql2
  encoding: utf8
  database: my_cms_test
  pool: 5
  username: root
  password: snapple187
  host: localhost

production:
  adapter: mysql2
  encoding: utf8
  database: #my_cms_production
  pool: 5
  username: root
  password: snapple187
  host: localhost

Thank you for taking the time to help solve this issue.


texts: Having Problems Installing 64-bit MySQL 5.5.8 x86 on OS X 10.6.6

texts:
I'm really new to databases and SQL in general, previously having installed MySQL only to hand it over completely to some CMS or other. Now, I want to get serious about adding SQL and MySQL or PostgreSQL to my skills.

And so, perhaps bearing this in mind, MySQL no longer installs successfully. I'm using the DMG archive for 5.5.8, and everything seems to install just fine. The installers for both MySQL and the startup item give me big green checkmarks, and the preference pane works just fine, in and of itself.

But the MySQL daemon can't seem to come up. If I try to launch MySQL via the preference pane, this gets logged to the console:

2/6/11  3:01:11 AM  1269    System Preferences  about to perform command
2/6/11  3:01:11 AM  1269    [0x0-0x65065].com.apple.systempreferences   /usr/local/mysql/support-files/mysql.server: line 256: my_print_defaults: command not found
2/6/11  3:01:11 AM  1269    System Preferences  status= 0

$ mysqld spits out:

110206  3:04:29 [Warning] Can't create test file /usr/local/mysql-5.5.8-osx10.6-x86/data/Atlas.lower-test
110206  3:04:29 [Warning] Can't create test file /usr/local/mysql-5.5.8-osx10.6-x86/data/Atlas.lower-test
110206  3:04:29 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
110206  3:04:29 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
110206  3:04:29  InnoDB: Initializing buffer pool, size = 128.0M
110206  3:04:29  InnoDB: Completed initialization of buffer pool
110206  3:04:29  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.

Let me know if any other information is needed. And thanks for your help!

Edit:
MySQL is installed in lrwxr-xr-x   _mysql  wheel  /usr/local/mysql -> mysql-5.5.8-osx10.6-x86.


texts: How do I move SQL Server database files?

texts:
I have a database and want to move the .mdf and .ldf files to another location. But I do not want to stop the MSSQLSERVER service, and I do not want to export to another server.

How can I do this?


texts: dynamic sql - assigning variable a value from result of dynamic sql query

texts:
Been struggling with this one for a good while now.  I'm trying to use sp_executesql below:

DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @IntVariable int;
DECLARE @fulldbtablename nvarchar(500);
DECLARE @ID bigint;

SET @fulldbtablename = QUOTENAME(@databasename) + '.[DBO].' + QUOTENAME(@tablename)

SET @SQLString = N'SELECT TOP 1 @IDOut = ID FROM' + @fulldbtablename + 'WHERE LastModified < DATEADD(DAY, @daystokeep, GETDATE()) order by 1 desc';
SET @ParmDefinition = N'@daystokeep int,
@IDOut bigint OUTPUT';
SET @IntVariable = -1;

EXECUTE sp_executesql
@SQLString
,@ParmDefinition
,@daystokeep = @IntVariable
,@IDOut = @ID OUTPUT;

I'm getting the error Must declare the scalar variable "@ID"

I've reviewed the help below, but I can't shake this error.  Has anyone any ideas?  I am declaring @ID in DECLARE @ID bigint;

To help me debug, I split the query into a new window and ran the query below which completes successfully so I am pulling my hair out:

DECLARE @Databasename varchar(500)
DECLARE @Tablename varchar(500)
DECLARE @ID bigint;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @IntVariable int;
DECLARE @fulldbtablename nvarchar(500);

SET @databasename = 'DatabaseName'
Set @Tablename = 'TableName'
SET @fulldbtablename = QUOTENAME(@databasename) + '.[DBO].' + QUOTENAME(@tablename)

SET @SQLString = N'SELECT TOP 1 @IDOut = ID FROM' + @fulldbtablename + 'WHERE LastModified < DATEADD(DAY, @daystokeep, GETDATE()) order by 1 desc';
SET @ParmDefinition = N'@daystokeep int,
@IDOut bigint OUTPUT';
SET @IntVariable = -1;

EXECUTE sp_executesql
@SQLString
,@ParmDefinition
,@daystokeep = @IntVariable
,@IDOut = @ID OUTPUT;

SELECT @ID

Anyone any ideas??????  Thank you!


texts: SQl Server Password Change after installation

texts: I have installed SQL Server 2005, but at the time of installation I forgot to change my password. Now I can access my database only in Windows authentication mode, and I want to access the database in mix mode.  For that I need to know the username and password. Please tell me how I can change the password after installing SQL Server 2005.


texts: Why are certain objects loaded into Buffer?

texts:
I have been experimenting on my test environment with clearing buffer cache via

DBCC DROPCLEANBUFFERS

Before any work is done or operational queries run the buffer is instantly starting to repopulate with data from core tables (data warehouse). It is loading one dimension fully (3 years data) whist others are ignored or only partially loaded so for example

Tab1 1490476 of 1490525 pages loaded (100%) (60m+ row dim)
Tab2 86446 of 12737347 pages loaded (0.68%) (250m row+ fact)
Tab3 14 of 18 pages loaded (77.78%) (800 row dim)

Tab1 would rarely have all rows read and updates/inserts would only occur on approximately the last 20% where as Tab3 would be 100% read pretty much all the time but rarely updated/inserted.

Interestingly when I test in a dev environment no data is reloaded in buffer until queries start to be run at which point I see a far different profile

Tab1 89154 of 512569 pages loaded (17.39%)
Tab2 105652 of 289851 pages loaded (36.45%)
Tab3 18 of 18 pages loaded (100%)

These were just random reports and a couple of builds run against dev to get these figures but still fairly true to typical usage. The test environment did not change other than a higher dirty row count.

I am curious as to whether the buffer is being reloaded from something like the plan cache or is there something else i should be looking at?

The reason for this question is that I have a performance problem in production where there are significant IO waits when loading Tab2 and I am perplexed as to why Tab1 is being fully loaded despite limited use and in my simple mind a higher buffer % for Tab2 would make more sense.

2017-02-24
There were a couple of queries being passed down that were highlighted by BlitzCache on read sort that were reading an unnecessary amount of data from Tab1 but it also reads from Tab4 equally unnecessarily (Both joined from another table on Surrogate key in the read).

Tab4 pages are however 0.34% written to buffer which I would assume would have been higher if this was the cause of the high buffer content of Tab1.

There were some other issues highlighed by BlitzCache which I will investigate more fully but still at a bit of a loss here.


texts: How can I specify the DDL of the index that is implicitly created when creating a materialized view log?

texts: When I create a materialized view log, Oracle implicitly creates an index on that log. For example, when creating a materialized view log on table foo, Oracle creates the view log table mlog$_foo and the index i_mlog$_foo. Oracle creates the index in the default tablespace, and I need it on a different tablespace. I can't change the default tablespace for the user. How can I specify the DDL (or at least the tablespace) for the index?


texts:
The web application I am working with has a huge Database that is a real mess, no foreign keys, no constraints, no indexes, no optimization, no normalization, nothing. Until now, the logic has been: If you need a field, add it, if you need a table, create it as fast as you can. At the beginning, I couldn't understand the severity of these mistakes, but having to deal all the time with obstacles created from a bad database, has made me decide to re-design the entire database. What i would need to know is:

Where should i start, normalization, adding keys and indexes, or anywhere else?
Since the cost of these changes will be huge for my work (I will have to redesign the entire code from scratch to adapt it), is it worth? I mean, what are the cons and pros of such a huge thing (I say huge, because I am a junior level in database matter). If anyone could guide me, would be awesome


texts:
I'm new as MySQL and DBA role.

Our systems started to fail due to timeout established by SLA (a few seconds).

Nothing different happened that day, normal usage, less than a thousand transactions per second.

It turns out, database storage reported a latency up to 390ms!!!!

How will we diagnostic this issue without the report from our provider?

We ask for this report after seeing load average 4 times as usual and I/O waits for over 70%.

What should I have done?


texts: Best way to automate sql server backup & restore from prod to dev?

texts:
I have a development server, and a production server. The dev server is inside our office at work inside our LAN.

The production server is a remotely hosted windows pc, that is connected to our web server, which is a seperate pc.

Now what I want is to have a batch file or some other tool to automatically download a recent backup, for which there are weekly backups done, and then restore that to the current copy of the database on dev server.

I know, I can create a scheduled task in windows to copy n zip a backup that the weekly backup takes, and I would need any suggestions or help for that, since the name is different because it is date/time stamp in the filename. I want to create a task to zip the backup and copy it to a folder that is accessible on the web server, they are both on the same network.

Then, I want the dev server to check for the existance of this latest backup zip file, and if exists, download it, unzip to a temp folder, then restore that database to the current one.

Since I may or may not be hear in the far future the more I can make this easy to use, and as automatic as possible, the better they will be.

Since there is no dba we can use to do this for this manaully.

Any suggestions?

I do not mind using software for this, but it has to be freeware, since there is no budget for this project.


texts: Database is stuck in recovery and thinks it's mirrored and out space. None of this is true

texts:
I have a non-production database that is stuck in recovery.

When I try to restore with recovery, I get an error saying it's mirrored or in an AG group (it isn't).

When I try to Set Partner Off, it says the file is inaccessible or not enough space (there is oodles of space).

I don't have rights to delete the mdf and ldf.

The files aren't set to read only.

Is there any other way to get these online>?


texts: How to store 'n' days of web server logs in Sql Server?

texts:
For faster reporting and performance analysis, we want to insert our web server logs into Sql Server. This will allow us to see traffic patterns, issues, slowdowns in near real-time.

We have a daemon that listens for request/response events from our load balancer and bulk inserts into the database.

However, we get around 1 GB of logs per day and we only need to keep about a week around (at least in this raw form).

What is the best way to store this data and the best way to delete old entries?

We've talked about storing each day's data in its own table, e.g. Log_2011_04_07 would have all the entries for that day, and then dropping the oldest table.  A view could be created to span all the day tables for easy querying. Is the feasible?