texts: How do I extend the character limit of a string in management studio

texts:
The SQL below works to an extend however when I view the output in SSMS tables with a lot of columns appear to be getting cut of in that the string that is generated by the cursor only appears to go to a maximum limit.

    USE [HealthBI]
GO

/*** ENTER VIEW SCHEMA ***/
DECLARE @schema_name AS varchar(MAX) = 'trust'

/*** ENTER CDO TABLE TO REPLICATE ***/
DECLARE @table_name_value  AS varchar(MAX) = 'CDO[_]%'
DECLARE @table_name  AS varchar(MAX)
DECLARE @column_name AS varchar(MAX)
DECLARE @viewsql AS varchar(MAX)

DECLARE csr_view CURSOR FOR
SELECT TABLE_NAME ,
STUFF(( SELECT  ',' +QUOTENAME(TABLE_NAME)+'.'+QUOTENAME(COLUMN_NAME)
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME = a.TABLE_NAME
ORDER BY COLUMN_NAME
FOR XML PATH(''),Type)
    .value('text()[1]','nvarchar(max)' ), 1, 1, '') AS value
FROM    INFORMATION_SCHEMA.COLUMNS a
where  TABLE_SCHEMA = 'dbo'
    AND TABLE_NAME like @table_name_value
    and TABLE_NAME NOT LIKE 'CDO_CDS%'
GROUP BY a.TABLE_NAME

    OPEN csr_view
        FETCH NEXT FROM csr_View INTO @TABLE_NAME, @column_name
    SET @viewsql = ''
--  PRINT @TABLE_NAME PRINT @column_name

        WHILE @@fetch_status = 0
        BEGIN

        IF OBJECT_ID('['+ @schema_name + '].[' + @table_name + ']') IS NULL
        SET @viewsql = @viewsql + '
        CREATE '
        ELSE
        SET @viewsql = @viewsql + '
        ALTER '

        SET @viewsql = @viewsql + 'VIEW [' + @schema_name + '].[' + @table_name + ']
        AS SELECT ' + @column_name + '
        FROM [dbo].[' + @table_name + ']'

        IF @schema_name = 'trust' AND @table_name <> 'CDO_MPI'
        SET @viewsql = @viewsql + '
        INNER JOIN [CDO_MPI]
        ON [CDO_MPI].[UNIQUE_ID] = [' + @table_name + '].[CDO_MPI_UNIQUE_ID]
        AND [CDO_MPI].[IS_DUMMY_PATIENT] = ''N''   GO'

        IF @schema_name = 'trust' AND @table_name = 'CDO_MPI'
        SET @viewsql = @viewsql + '
        WHERE [CDO_MPI].[IS_DUMMY_PATIENT] = ''N'''
        PRINT (@viewsql)
--       EXEC (@viewsql)

        FETCH NEXT FROM csr_View INTO @TABLE_NAME, @column_name
        SET @viewsql = ''

        END
    CLOSE csr_view
DEALLOCATE csr_View


texts: Azure Backup SQL VM - Not shrinking log file after transaction log backup

texts:
I am using Azure Backup to take a transaction log backup from my SQL 2016 Enterprise VM every 15 mins. It also takes one full backup daily. The db is using Full recovery model.

Problem is that the Azure Backup solution is not shrinking the log file after the transaction log backups. Is there any way to automate this process or ensure that the log file doesn't grow out of control?

I'm using this Azure Backup solution: https://docs.microsoft.com/en-us/azure/backup/backup-sql-server-database-azure-vms#create-a-backup-policy


texts: How much will computer hardware affect the speed of a "load data local infile" query?

texts:
I have a VBA program that separates and saves an excel file into 48 different .csv files, then loads those files into 48 separate tables. I use "load data local infile" to accomplish this, and it has worked very well, and very speedily, up until about 2 weeks ago.

Suddenly, every "rs.open..." line, where I actually send the query to MySQL, took about 4-5 seconds, as opposed to the ~1 second it used to take, which drastically increased the time it took to go through each full file.

I still haven't pinpointed the real problem, but it DID get me thinking: Would better computer hardware appreciably speed up the process? I've got 4gigs of ram and a 3.2 Ghz processor, so my computer isn't terrible, but it COULD be better.

Questions

Can the speed of MySQL queries be appreciably increased with better hardware, or is it somehow limited by MySQL itself?


texts:
I have looked through many of the questions asked and answered but can't seem to find my answer, even kludging the provided answers into my queries - most usually fail, the remainder provide garbage results.

NOTE THAT THIS IS FOR IBM DB2 v9.7!  NOT SQL Server or anything newer.

I have a single table that contains quasi-distinct rows, but has no key.  The odds of finding a duplicate are next to none based on ALL the fields, and the probability of duplication increases with the fewer fields selected in the query.  I'm mostly ok with that at this point.  There are more fields than listed below, and I have added a key just for reference...

PSEUDO-QUERY

select sum(max(NumImages)) from table where Source in ('A','B') and Event='190'

TABLE

Key   ID    NumImages   Source    Event
1     1     4           A         190
2     1     -           -         247
3     1     2           B         190
4     1     -           -         134

5     2     7           C         190
6     2     2           A         190
7     2     1           B         190
8     2     5           A         190

9     3     5           A         190
10    3     -           -         247
11    3     -           -         134
12    3     -           -         815

13    4     2           A         190
14    4     9           A         247
15    4     5           B         190
16    4     4           B         134

PARAMETERS

Source in ('A','B')
Event = '190'
NumImages is not null

(*I don't think that will ever happen with the preceding parameters)

OUTPUT

ID    Sum
1     6
2     6
3     5
4     7

Because:

1 = 6 ; keys 1 and 3 are both max for event 190 and source A or B
2 = 6 ; key 8 (5 - max for Event 190 Source A) + key 7 (1 - max for Event 190 Source B).  Source C (key 5) is ignored.
3 = 5 ; key 9 is the only value matching the parameters
4 = 7 ; keys 13 and 15 are the only ones that match Event 190, even though Source and NumImages have values that match against the other parameters.


texts: archiver stuck after set log_archive_dest_2 to fast recovery area oracle

texts:
All data guard expert,

I am currently trying to configure a primary database for creating physical standby database with an oracle 11g stand alone database.

Oracle version: 11.2.0.4

It's on archivelog mode with automatic archiving using fast recovery area.

db_recovery_file_dest is set to /u01/app/oracle/oraarch

db_recovery_file_deset_size is set to 4G.

archiving is working normally before starting configure this database for physical standby database creation.

here is what's done so far on this primary database:

force logging on primary database is turned on:

alter database force logging;
standby redo logs are created. While this primary database has two redo log file groups, so I created 3 standby redo log file as Oracle recommeneded.

SQL> SELECT * FROM V$STANDBY_LOG;

GROUP# DBID THREAD#  SEQUENCE#

BYTES BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIM

NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME

4 UNASSIGNED 0          0
 52428800 512          0 YES UNASSIGNED

5 UNASSIGNED 0          0
 52428800 512          0 YES UNASSIGNED

6 UNASSIGNED 0          0
 52428800 512          0 YES UNASSIGNED

then

Set archive_log_dest_2 parameter to us DB_RECOVERY_AREA.

alter system set log_archive_dest_2 = 'location=user_db_recovery_file_dest valid_for=(standby_logfile,standby_role)'
scope=both;

and turn log_archive_deset_state_2 be 'enable', while it's was set to enable as default.

alter system set log_archive_dest_state_2=enable scope=both;

Then the problem I am stuck begins. When I issue SQL> alter system switch logfile; 3 times, then it's hung up.

I check the /oraarch directory, the redo log file are not even archived at the first two switch logfile command.

and when the third time switch logfile is issued, then it's hung up.

Please help/advise while you expertise is really appreciated. thank you so much.

regards


texts: Cannot perform alter on 'FunctionName' because it is an incompatible object type

texts:
I know the same question has been asked before, but the situation is slightly different and I'd like more explanation as to why it fails in my context please, when (AFAIK) the return type is the same.

Historically in my database I've used a CLR-based table valued function to split comma-separated values into a table containing an integer column.

Because SQL2016 introduces STRING_SPLIT, I need to swap out the CLR part of this function, and replace with the native TSQL function (as I have hundreds of stored procedures that rely on this function).

If I modify this function in SSMS, I see this:

ALTER FUNCTION [dbo].[e4fn_CreateIdTable](
     @Ids [nvarchar](max),
     @separator [nchar](1))
RETURNS TABLE (
    [IdVal] [int] NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [ValueSplitter].[MyCompany.ValueSplitter].[SplitToIntegers]

I hoped I be able to alter it like so:

ALTER FUNCTION [dbo].[e4fn_CreateIdTable] (
    @Ids [nvarchar](max),
    @separator [nchar](1)
)
RETURNS TABLE
AS RETURN
(
    SELECT
        CAST(NULLIF(LTRIM(RTRIM(value)), '') AS int) AS IdVal
    FROM
        STRING_SPLIT(@Ids, @separator)
)
GO

This returned the error:

  Cannot perform alter on 'dbo.e4fn_CreateIdTable' because it is an
  incompatible object type.

I assumed that was because the table definition of the output parameter was not explicitly defined.  So, I retried like this:

ALTER FUNCTION [dbo].[e4fn_CreateIdTable] (
    @Ids [nvarchar](max),
    @separator [nchar](1)
)
RETURNS @tbl TABLE  (
    IdVal int
)
AS
BEGIN
    INSERT INTO @tbl (
        Idval
    )
    SELECT
        CAST(NULLIF(LTRIM(RTRIM(value)), '') AS int)
    FROM
        STRING_SPLIT(@Ids, @separator);

    RETURN;
END
GO

Again, the same error appears.

Why does the error appear, when the return types appear to be the same?
If I drop/recreate the function, do all the stored procedures need to be recompiled?


texts: SSIS 2017 - Parse flat file with multiple columns and headers on multiple lines

texts:
I receive a daily CSV file that contains 600+ company employee positions, each of which are formatted as follows:

Position,Description
SUP1015,Shipping Supervisor Day
Work UOM:,Hours,Active:,Yes,Permanent:,Yes,,
Default Rate Level:,0,Default Rate Source:,Master,Default GL Source:,Master,,
Effective Date:,,Expiry Date:,,Created Date:,29-Apr-2014,Revised Date:,06-Jun-2019
Job Class:,,,,,Location:,,1004 - Shipping,,
Union Code:,,,,,Reports To:,,MGR1056 - Delivery & Shipping Manager,,
Position FTE:,,1.0000,,,,,,

My goal is to transform all 600+ records into one table:

Position | Description                     | Work UOM | Active | Permanent | Default Rate Level | Default Rate Source | Default GL Code | Effective Date | Expiry Date | Created Date | Revised Date | Job Class | Location                 | Union Code | Reports to                                    | Position FTE |
=========================================================================================================================================================================================================================================================================================================================
SUP1015  | Shipping Supervisor Day         | Hours    | Yes    | Yes       | 0                  | Master              | Master          |                |             | 29-Apr-2014  | 06-Jun-2019  |           | 1004 - Shipping          |            | MGR1056 - Delivery & Shipping Manager         | 1.0000       |

I have no idea how to parse this, given the connection managers in SSIS.  Any help and guidance is greatly appreciated.


texts:
I have a table with some sensor data in it which grows every minute with a new measurement for that specific sensor. Something like:

entry_id (pk), sensor_id (fk), measurement_value, created_at

I need to perform some analytics on this data, at hour intervals. Basically the query will look something like:

SELECT measurement_value FROM above_table
WHERE sensor_id = x
AND created_at BETWEEN time_a AND (time_a + 1 hour)

1) First of all, I'm wondering if this design is reasonable long term. Each sensor will rack up 500k readings x year. If I have enough sensors, the table will get real sizeable real fast. I'd like analytics to be pretty snappy and not take longer than a second, so perhaps I'll need to be pre-computing these results every few insertions somehow. Alternatively I perhaps be looking for NoSQL solutions, or would that not really address the core issue?

2) What's the best performance I can get for querying that table? I'm thinking that an index on (sensor_id, created_at) would be hard to beat.

Thanks!


texts: How to recreate a SQL Server audit without an audit gap?

texts:
I have some audits and audit specifications on a SQL Server 2012 instance and I want to reapply the audit and audit spec definition every evening in case the DBAs change it. I have measured the time taken to do this on one of my Dev boxes, and it's quite short, but there's still a window there where some audit data could get lost. From a quick test I did to measure the time taken:

AuditRecreate_ms
----------------
23

(1 row(s) affected)

AuditSpecRecreate_ms
--------------------
16

(1 row(s) affected)

It's possible with SQLTrace because you can create a new trace and then delete the old one, and no audit data gets lost. I'm trying to move away from SQL Trace but retain an uninterrupted audit but at the same time make sure the audit definitions do not change. Any ideas here?


texts: Is there a way to find out how many times a stored procedure was invoked since last DB start

texts: I am currently in a fairly mismanaged environment, where stored procs were introduced to the database over a few years and some versions became obsolete. Is there an easy way to figure out (preferably in Sybase) how many times a give stored proc was invoked so that we can gain some confidence in removing procs .that were never called recently.


texts: Tutorial for installing and setting up 'streaming replication' with PostgreSQL on Windows XP

texts:
Please refer to the diagram in the link below.  While looking at my goals.

http://i.stack.imgur.com/GkVWb.png

GOAL:

Get PostgreSQL 9.1 running on 3 terminals on the 'on site' location.
'on site' have 'streaming replication' running between these DB's 'on-site' locations.
'on site' have fail-over to one of the 'on-site' 'slaves'.
between 'master' 'on site' and 'slave data warehouse' I'll just pull log files.

I've read a bunch about Linux but haven't found much about Windows XP. All the machines 'on site' have the same hardware.  Unfortunately I can't use linux in the 'on site' environment.

Again been googling but not finding much anyone know of a good tutorial(s) to get this type of setup pieced together!

I'm going to keep looking and if I find anything I'll make sure to post it back here.

Thanks!


texts: Get last result of query and set it to variable

texts:
I am trying to get the last (most recent) record from a query and set it to the value of a declared variable, but it doesn't seem to be working.

DECLARE @PriceID int
SELECT TOP 1 log_id = @PriceID
  FROM price_logs
 WHERE part_id = 45
 ORDER BY log_id DESC

In this instance, the part_id value doesn't matter, it just need to be a value that exists within the database.  When I run this code I am getting a blank value for @PriceID, yet when I check the values in the price_logs table, there is a data row to be collected.  So what is it I am doing wrong here?


texts: What is the fastest way to clone/copy oracle databases on the same machine?

texts:
I want to clone oracle 11g databases on the same machine and I need to change user etc. from e.g. PROD to DEV.

For that I use expdp/impdp, but this tends to be slow with increasing database size. I need to do this on linux and windows machines.

Is using expdp/impdp the fastest way to do this?

Or should I use a completly different way of doing this?
i.e. do not use the same machine,

use two machines i.e. two separate oracle servers

or install multiple oracle servers on the same machine?


texts:
I am tuning SQL backups using compression, writing to multiple files, and changing the BufferCount and MaxTransferSize options. I have SQL instance with a 4 TB database and several smaller databases from a few MB to 150 GB.

My question is can I start tuning with the 150 GB DB and then apply those settings to the 4 TB backup with minimal changes? Or does the 4 TB require it's own "tuning session" from scratch? Since it usually takes several backup attempts to dial in the settings I'm trying to get a jump-start on tuning the 4 TB database if possible.


texts: What tools are there to generate test data for SQL Server?

texts:
As you can see from another question of mine, generating test data is my theme right now.

At this point, I'm still generating my test data by hand.  However, this process always generates small amounts of data (usually five-ten rows) since it is a manual process.

Are there any tools to automate this process?  Particularly, I'd like to be able to generate 1mil+ rows.


texts: Can't run Alter Table command due to locks

texts:
I'm unable to drop, delete or alter a database in my Local SQL Server instance.

I have run sp_Who2 and there don't appear to be any processes using the database apart from a process which gets spawned by my issuing the Alter Database command (I do this against the Master db). This process has a Wait type of LCK_M_X and a value of -2 for the Blocked By attribute.

I have issued KILL commands against them and I've restarted all the related system services and my machine a few times but to no avail. Is there anything else I can try?


texts: Shared locks taken under Read Committed Snapshot Isolation (RCSI)

texts:
I am seeing waits for shared locks (LCK_M_S) when using RCSI. My understanding is that this isn't supposed to happen since SELECTs do not require shared locks when using RCSI.

How can I be seeing shared locks? Is it because of foreign keys?


texts: How to change the output encoding of Oracle command line tools to UTF8?

texts:
I am using a german oracle 11.2.0g install, which has some command line tools communicating with some non-ascii characters (ü, ö, etc.). These are written out in iso8859-1 encoding, but I need them as utf-8 for some scripting. Is it possible?

Additional info:

OS: german windows 7 x64
Tools: command line tools in the bin/ directory of the oracle install, f.e. lsnrctl and like
Scripting language: mostly cmd.exe

Language-specific output is okay, it gives the german output without any problem. The only problem is that it comes in iso8859-1 and not in utf-8.

It were converted by an iconv command from a cygwin, but I used this only as a last resort. The best were if I were able to set somehow the utf-8.

Another tools (f.e. tools from cygwin) work without any problem.


texts: SQL Server 2008R2 Database refresh in isolated environment

texts:
I would greatly appreciate your help on my database "refresh" issue.

The scenario:

I have 12 databases on a QA server that were restored there from a Production server approx. 2 years ago.
Now QAs need to sync those 12 databases with databases on a Prod server; all account logins (and corresponding permissions) have to remain unchanged in QA environment - QAs only need to "refresh" databases so that data is as current as it is in Prod environment.

For security reasons there's no (and cannot be) connection between two servers (hence it is not possible to import data with append option), so I had to request DBAs on that Prod server to backup databases and to place backup files in a shared folder (already there).

My question is - what is the best way to "refresh" 12 databases in QA environment - is it to delete/drop old databases and restore them from backup files (then what would happen to the current QA server logins?) or is it to try to restore databases from backups without dropping 12 old databases and is this possible, would be data just appended to existing data and current logins stay unchanged ?

Thank you in advance for any input.


texts: Lost Connection to mysql server at 'reading authorization packet', system error 0"

texts:
OK, this has been bugging me all afternoon now,

I have a new MySQL Database '5.5.40-0+wheezy1-log' running on Debian Linux. It is to all extents and purposes a clean install.

I have created two users:

GRANT ALL ON *.* TO 'test'@'%' IDENTIFIED BY 'test';
GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'a_secure_password';

I am trying to connect from my PC (windows 7)

If I connect to the physical server (via Putty) I can log into MySQL fine using both usernames.
If I use (the now obsolete) MySQL Query Browser, I can log into MySQL fine using both usernames.
If I use the new MySQL Workbench (6.2)

I can only log in using the 'test' user.
If I try the 'root' user I get : Your Connection attempt failed for user 'root' from your host to server at '123.456.789.123:3306' Lost Connection to mysql server at 'reading authorization packet', system error 0

Why? it's driving me round the bend.


texts: How can MySQL be optimized for fast reads?

texts:
From this Flickr article, they state:

  MySQL is optimized for fast random reads.

Also, one of the answers to this Stack Overflow question states:

  MySQL is typically built for 80/20 read/write ratio

I used those two sentences as keywords to search a lot but cannot find any useful resouces to actually prove/explain that opinion.

Can you explain to me in which way MySQL is optimized for fast random reads with some materials as references, please (or just offer some keyword to help to find out)?


texts: Actual Execution plan does not have memory grant info

texts:
I manually ran query in SSMS and captured actual execution plan of the query by enabling its icon in SSMS. I am not getting "Memory Grant" when I keep my mouse on SELECT OPERATOR. I didn't get in properties window as well. I checked the XML data of plan as well and I am getting below Information which is not useful:
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="629094" EstimatedPagesCached="78636" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="3836688" />

I tried mulitple versions of SSMS but no luck.
Anyone else has faced this wierd issue? Any help would be appreciated.


texts: Single MySQL DB contains both push and pull data. Recommended path to replication?

texts:
We have a single deployed (MySQL) database that contains both user tables and product tables (currently in the same DB).

The product tables are "pushed to production" data -- they are generated in our internal development environment and need to make their way to the production database. We'd like to set up replication because our product DB is growing quite large (>40M items). Its no longer feasible to push snapshots of this data - we want incremental updates.
The user tables are "pulled from production" data -- they generated in our production system and copies of this data need to make their way back into our development. Our devs want near real-time analytics and don't want to run these queries on the production environment.
There are (currently) some join queries between these set of tables (e.g. lookup user's cart product). However its not particularly problematic separate these as separate queries to independent stores.

I'd like to set up replication of both sets of data. Pull the user data, push the product data.

What would be the best practice to set up this kind of bi-directional way replication?

Is it possible to setup replication on a table basis? Or should the data be split into separate databases, or even separate servers?


texts: Verify if user has read access to a table

texts:
I'm developing an ETL, whose extract step must read data from a table.

There are times that my user's access to that table is revoked. So, to avoid the ETL failing, before reading the table I must verify if I have permission to do it. If not, I'll just jump the extract step and run the rest of the ETL.

What's the proper way of verifying if my user has read permission over a table?


texts: Postgres configuration suitable for a single 300 GB dataset (5 billion rows)

texts:
Our purpose is to use up every bit of resources on 1 machine dedicated to just serve one dataset to 2 data scientists and make it available for slicing, grouping, etc.

Here is our current setup and configuration

Data: 5 billion rows (~300GB) of AWS cloudwatch 5-minute data
Hardware: AWS EC2 t2.2xlarge (8 cores, 32GB RAM, 500GB gp2 disk)
Postgresql version 10
Modified sections from the defaults of /etc/postgresql/10/main/postgresql.conf

work_mem = 25GB
maintenance_work_mem = 25GB

max_worker_processes = 8
max_parallel_workers = 8
max_parallel_workers_per_gather = 4

With the current configuration, select count(1) from resource_usage8 takes forever to execute.

enel_cloudwatch=# explain select count(1) from resource_usage8;
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=19774628.42..19774628.43 rows=1 width=8)
   ->  Gather  (cost=19774628.00..19774628.41 rows=4 width=8)
         Workers Planned: 4
         ->  Partial Aggregate  (cost=19773628.00..19773628.01 rows=1 width=8)
               ->  Parallel Seq Scan on resource_usage8  (cost=0.00..18904390.40 rows=347695040 width=0)
(5 rows)

Here's the table's schema

enel_cloudwatch=# \d resource_usage8
                        Table "public.resource_usage8"
     Column     |            Type             | Collation | Nullable | Default
----------------+-----------------------------+-----------+----------+---------
 mtr_resourceid | text                        |           | not null |
 mtr_project    | character varying(30)       |           |          |
 mtr_timestamp  | timestamp without time zone |           |          |
 mtr_metrictype | character varying(30)       |           |          |
 mtr_max        | real                        |           |          |
 mtr_sum        | real                        |           |          |
 mtr_average    | real                        |           |          |

and here is the index I'm creating (which takes several hours to complete)

enel_cloudwatch=# CREATE INDEX ON resource_usage8 USING btree (mtr_resourceid, mtr_metrictype, mtr_timestamp);

Are there any other suggestions that I'm overlooking? Should I just give up on postgresql and resort to another database?

PS: This question was prompted from this SO.


texts: Error on rename for a column on a temporal table

texts:
I've come across an issue when renaming a column in a temporal table. I'm using entity framework to generate migrations, so most of the original SQL is generated, but that doesn't affect my issue.

To reproduce the problem first create a table (I've done a blank database, with just this table).

CREATE TABLE [TestTable1]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [uniqueidentifier] NULL,
    [Description] [nvarchar(2000)] NULL,
    CONSTRAINT [PK_TestTable1] PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
) ON [PRIMARY]

Then turn on system versioning

ALTER TABLE [TestTable1]
ADD
  SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_SysStartTestTable1 DEFAULT DATEADD(d, -1, SYSUTCDATETIME()),
  SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_SysEndTestTable1 DEFAULT CONVERT(datetime2(0), '9999-12-31 23:59:59.9999999'),
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
ALTER TABLE [TestTable1] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TestTable1History))

Now rename the column (no issues with this one, it doesn't matter if you enter data in the table or not)

EXECUTE sp_rename @objname = N'dbo.TestTable1.UserId', @newname = N'OldUserId', @objtype = N'COLUMN'

Next I needed to add a new column.

ALTER TABLE [dbo].[TestTable1] ADD [UserId] [int]

No problems so far, next I update the data using the old user to populate the new user.

Now I make my new column not nullable and I add an index on my new column (these might not be necessary to create the issue) .

ALTER TABLE [dbo].[TestTable1] ALTER COLUMN [UserId] [int] NOT NULL
CREATE INDEX [IX_UserId] ON [dbo].[TestTable1]([UserId])

Now I get rid of my original column.

ALTER TABLE [dbo].[TestTable1] DROP COLUMN [OldUserId]

Because I'm using EF migrations, I need to have a reverse script to roll the database back to the earlier version.

ALTER TABLE [dbo].[TestTable1] ADD [OldUserId] [uniqueidentifier]
DROP INDEX [IX_UserId] ON [dbo].[TestTable1]
ALTER TABLE [dbo].[TestTable1] DROP COLUMN [UserId]
EXECUTE sp_rename @objname = N'dbo.TestTable1.OldUserId', @newname = N'UserId', @objtype = N'COLUMN'

The rename fails with the error Invalid object name ' '.

If I don't drop the column then I can rename it back, so it must be something to do with dropping the column. If temporal tables are not enabled then this also works just fine.


texts: Cycles or multiple cascade paths with on delete set null: really?

texts:
I'm asking this question to check if my reasoning about cascaded delete is correct and if I'm not overlooking anything. I understand the behavior and I'm not asking why it is implemented with the current restrictions.

When we try to create a table containing a self reference like this:

CREATE TABLE [BlogComments] (
    [Id] int NOT NULL IDENTITY,
    [AuthorName] nvarchar(100) NULL,
    [Content] nvarchar(max) NULL,
    [CreatedTime] datetime2 NOT NULL,
    [ReplyToId] int NULL,
    CONSTRAINT [PK_BlogComments] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_BlogComments_BlogComments_ReplyToId] FOREIGN KEY ([ReplyToId])
         REFERENCES [BlogComments] ([Id]) ON DELETE SET NULL -- Not: CASCADE
);

We get the infamous exception

  Introducing FOREIGN KEY constraint 'FK_BlogComments_BlogComments_ReplyToId' on table 'BlogComments' may cause cycles or multiple cascade paths.

I understand that the setting ON DELETE SET CASCADE may actually cause a recursive cascade of deletes and, hence, may be dangerous, or time consuming at best. But ON DELETE SET NULL is different: it will only nullify ReplyToId of direct child records, not of their child records.

So am I right in concluding that SQL Server is overly restrictive when the foreign key is defined with ON DELETE SET NULL? Or am I overlooking some good reasons for this restriction in this particular case?

By the way, I'm aware of a school of thought that contends that there shouldn't be any cascade actions anyway. Let's not get into that.


texts:
I am trying to generate a report from this procedure
create procedure [dbo].[hstatement]
(
    @fromdate date,
    @todate date,
    @prd varchar(8)
)
as
begin
    create table #tempdata
    (
        LBrCode         int,
        prdCode         varchar(8),
        bal_date        datetime,
        bal_amt         numeric(18,2)
    )

    while @fromdate<@todate
    begin
        insert into #tempdata
        select  LBrCode,
                substring(PrdAcctId,1,8) prdcd,
                convert(varchar(12),@fromdate,106) tdt,
                Balance4
        from
        (
            select LBrCode,
                   PrdAcctId,
                   CblDate dt,
                   Balance4,
                   rank() over (partition by LBrCode,PrdAcctId order by CblDate desc ) rnk
            from cl_balance
            where rtrim(substring(PrdAcctId,1,8)) in (@prd)
                  and floor(cast(substring(PrdAcctId,17,8) as int))=0
                  and CblDate<=@fromdate
        ) x
        where rnk= 1
              and LBrCode=1;

        set @fromdate=dateadd(day,1,@fromdate)
end;

The procedure is compiled successfully.
However, I am expecting a result as each date is between fromdate and todate, but I am getting only the first record.


texts: Migrate Data and/or Schema from DBv1 to DBv2

texts:
I have a production database which has been plugging away for several months. I have been making modifications to a duplicate copy on a test/dev server. I've added some additional fields (null allowed) to several tables, as well as I've added some new tables and indexes to both new and existing tables.

My question is this: I am ready to take my test/dev database and push it into production. The problem is that I need to migrate all the data from my production database to my test/dev database. My first thought was to use SSMS to "IMPORT" data from production to test/dev, the problem I'm having is that even with "enable identity insert" I still get all types of foreign key constraint errors. This is a bit of a two-parter

Is there a better way to migrate schema changes from test/dev to production than the process I just outlined?
If not, is there a way to temporarly disable all insert constraints during the import?

I would be very happy to only migrate my test/dev schema to my production server, but I have not found any way to do this without manually going table by table, index by index, view by view, stored procedure by stored procedure... etc. I've seen some tools that claim to be able to do it, but I'm looking for a free/open source solution and haven't found anything.

note: database is small < 10gb, the test/dev database is fully truncated with zero records in every table.


texts: Is it possible to restore a database with only database roles membership?

texts:
I have an existing database, that i want to restore from a backup by overriding the existing database.

at the moment I have

db_owner
db_backupoperator

roles

I thought that would be enough to restore the db. However when i try i get the error

User does not have permission to RESTORE database 'XY'

Is this achievable just with granting database roles (and without granting server role and fine tuning permissions?)


texts:
Good morning, everyone!
I am using SQL SERVER 2008, and a needed to use the database of our client. When I was going to do a backup in case something went wrong I misclicked to do a restore and didn't notice that I was in the restore screen and clicked the "OK" button (without changing any configurantion on the screen). Right when it began doing I hit the "Stop Action Now". It stoped and a message appears saying that the user had canceled the action. So far so good, but when I was going to do the backup for real the SQL didn't allow saying that already had a restore going on. When I saw the database state was "Restoring". I searched on internet and saw that the query:
DATABASE NomeDatabase WITH RECOVERY

would resolve my problem but before I execute I asked my boss if there was any problem executing this query and he said to don't run the query because it would restore to the most recent backup (and the most recent backup was on 08/23). So we only have the MDF e LDF files, and when we try to do an attach with this query
EXEC sp_attach_db @dbname = N'DBName',
    @filename1 = N'C:\PATH\DBName.mdf',
    @filename2 = N'C:\PATH\DBName_log.ldf'

it gives this answer:

Msg 1813, Level 16, State 2, Line 1 Could not open new database
'DBName'. CREATE DATABASE is aborted. Msg 9004, Level 21, State 1,
Line 1 An error occurred while processing the log for database
'DBName'. If possible, restore from backup. If a backup is not
available, it might be necessary to rebuild the log.

I also search this error message and saw that is necessary rebuild the log but nobody here knows how to do it. I would like to know if you could help me or if anyone already had been through that.
Here is the screen that I did the restore, just clicked the "ok" not changing any configuration: Image
(My boss detached and deleted the database to try to do the attachment query, so we don't have the DB on the SQL anymore only MDF and LDF files)
I saw some posts on the site but none of them worked with me.
Thanks and sorry if there is any gramatical mistake!

Thanks for the answer. I also tried this query, but got no lucky.

The log cannot be rebuilt because there were open transactions/users
when the database was shutdown, no checkpoint occurred to the
database, or the database was read-only. This error could occur if the
transaction log file was manually deleted or lost due to a hardware or
environment failure.
Message 1813: Could not open new database 'dbname'. CREATE DATABASE is aborted.

So I guess there is nothing to do but restore the August 23rd now? All the things done after it are completely lost, that's it?


texts: Looking up which record(s) correspond to a specific resource description in dm_tran_locks

texts:
I've spent the better part of two days trying to figure out what exactly is being locked in one of my tables and why I'm getting deadlocks that seem to make no sense at face value.

I've found numerous blogs explaining how to use the undocumented %%lockres%% function to get the hash for a specific row in a table. But each of these guides only give the example where the lock in question is on the primary key for the table. I have a strange situation where there is a lock on the primary key and a unique key as well.

For context: My primary key is a clustered index on a UUID string. The only other index in this table is a composite unique key on two columns (not including the pk). When I do an INSERT into this table, I can see in sys.dm_tran_locks that there are two X KEY locks on this table: one for the pk and one for the unique constraint.

My deadlock report seems to imply (unless I'm reading it wrong -- you can see my other question here) that the deadlock is caused by a second query also locking on the unique index.

I've been experimenting on a different database using the same schema to try and figure out how I can determine if the cause of the deadlock is avoidable or not. I did an INSERT in an open transaction and compared the resource description to the %%lockres%% of all records in the locked table, only to find out that the lock on the primary key maps to the row that I added, but the lock on the unique index does not match anything in the table at all.

Does anybody here know what %%lockres%% is for this unique index? It clearly isn't a specific record in my table.

For context, here is the queries I ran to see this information:

This query lists the locks on my current database. Output below.

SELECT dm_tran_locks.request_session_id,
       dm_tran_locks.resource_database_id,
       CASE
           WHEN resource_type = 'object'
               THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
           ELSE OBJECT_NAME(partitions.OBJECT_ID)
           END                                     AS ObjectName,
       partitions.index_id,
       indexes.name                                AS index_name,
       dm_tran_locks.resource_type,
       dm_tran_locks.resource_description,
       dm_tran_locks.resource_associated_entity_id,
       dm_tran_locks.request_mode,
       dm_tran_locks.request_status,
       cleanlockrs

FROM sys.dm_tran_locks
         LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
         JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id
         CROSS APPLY(
    SELECT LEFT(SUBSTRING(resource_description, 2, LEN(resource_description)), LEN(resource_description) - 2)
) clean(cleanlockrs)
WHERE resource_associated_entity_id > 0
  AND resource_database_id = DB_ID()
  and resource_type = 'KEY'
ORDER BY request_session_id, resource_associated_entity_id

When I then execute the following query, I only get back a result for the resource description for the first index.

-- This returns the row I just added.
select * from entities where %%lockres%% like '%27f49aa9c0ac%'

-- This does not return anything.
select * from entities where %%lockres%% like '%7e24236fccb8%'


texts: SQL Server tempdb log file isn't autogrowing, instead getting error “The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'”

texts:
FYI: moved from Stack Overflow https://stackoverflow.com/questions/56350491/sql-server-tempdb-log-file-isnt-autogrowing-instead-getting-error-the-transac

I'm using SQL Server 2016 and am encountering this error intermittently:

  The transaction log for database 'tempdb' is full due to
  'ACTIVE_TRANSACTION'

It appears that this happens when a transaction is opened and then an expensive tempdb operation is done within the transaction. It seems to me that the tempdb log file should be growing, but it isn't, and instead I"m getting the error above.

I'm able to reproduce this by using the code below. The error comes up everytime I run the code below on the server having this issue. I tried reproducing this on a different SQL Server and couldn't.

BEGIN TRAN

    SELECT * INTO #temp
    FROM sys.all_objects

    DECLARE @iLoops INT = 1000
    DECLARE @i INT = 0
    WHILE @i < @iLoops
    BEGIN
        INSERT INTO #temp
        SELECT * FROM #temp

        SET @i = @i + 1
        DECLARE @rowCount INT = (SELECT COUNT(*) FROM #temp)
        PRINT CONVERT(VARCHAR(16), @i) + ' of ' + CONVERT(VARCHAR(16), @iLoops) + ', rows in #temp: ' + CONVERT(VARCHAR(16), @rowCount)
    END

    DROP TABLE #temp
ROLLBACK

As the reproduction code is running, I'm monitoring the size of the tempdb log file by using this query (which I got from https://www.sqlshack.com/monitor-sql-server-tempdb-database/):

SELECT instance_name AS 'Database',
[Data File(s) Size (KB)]/1024 AS [Data file (MB)],
[Log File(s) Size (KB)]/1024 AS [Log file (MB)],
[Log File(s) Used Size (KB)]/1024 AS [Log file space used (MB)]
FROM (SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name IN
('Data File(s) Size (KB)',
'Log File(s) Size (KB)',
'Log File(s) Used Size (KB)')
AND instance_name = 'tempdb'
) AS A
PIVOT
(MAX(cntr_value) FOR counter_name IN
([Data File(s) Size (KB)],
[LOG File(s) Size (KB)],
[Log File(s) Used Size (KB)])) AS B

The error appears to happen when the tempdb's log file's used size reaches the allocated size. Why isn't the log file autogrowing? Looking in SSMS, the tempdb log file is set to autogrow and has no maximum size. The drive on which the log file lives has over 20GB of free space. The tempdb database is using the simple recovery model.


texts:
I had a situation yesterday where my indexes were rebuilt and database size doubled in size (50 % of the new size was unused). Sort in tempdb is set to off and I get the impression that this rebuild caused it (index rebuild not in tempdb).

Is there a reason for the rebuild procedure to hold onto disk space after completion/potential failure?


texts: Running queries against multiple servers

texts:
I want to use a Central Management Server, with a server group underneith, to run management queries against multiple server.

I know how to run a query against the whole group through SSMS, but I want to schedule queries to run through server agent.

How do I do this?


texts: How to customize constraint violation messages?

texts: I want define my own message for each constraint. Is it possible to customize constraint violation messages in PostgreSQL?


texts:
This query is working good.

select  DATENAME(DW,getdate()) AS [Weekday]

My Problem is suppose I give input values like

select  DATENAME(DW,InvoiceDate) AS [Weekday] from store;

is not working in SQL-Server 2008 R2.

Column datatype is BIGINT, column values below

InvoiceDate

20130925
20130926
20130927
20130928
20130929
20130930


texts: MongoDB Sharding and Replica Set Allocation

texts:
I am going to setup MongoDB cluster:

3 config servers, 3 routing servers, 2 shards (replica set per shard).

A replica set is just simply 1 primary, 1 secondary and 1 arbiter.

Therefore, in my concept, totally 12 (3 + 3 + 2*3) servers will be setup.

However, it is limited only 3 servers physically. Is there any good way to allocate those instances into 3 servers?

My idea is that:

ServerA - 1 config instance, 1 routing instance, 1 primary (shard I), 1 secondary (shard II)

ServerB - 1 config instance, 1 routing instance, 1 secondary (shard I), 1 arbiter (shard II)

ServerC - 1 config instance, 1 routing instance, 1 arbiter (shard I), 1 primary (shard II)

In a result, if one server is down, the whole MongoDB cluster still works. Since I lack in experience of database cluster and distributed system, I would like to obtain professional suggestion from all of you.

In addiction, I would like to know is it possible to allocate MongoDB data to other server storage (i.e. SAN)?

Thank you for any advices.


texts: How to setup Linked Server connect with different Domain Account with Windows Authentication

texts:
we have two SQL Server 2017 "ServerA" and "ServerB" on Windows Server 2016 in the same domain.
Both SQL Server have a Login defined "mydomain\dbaccount".
On ServerA we have a Login defined "mydomain\testaccount" which can access the Database.
We want to connect with the "mydomain\testaccount" to ServerB via Linked Server. The linked server should connect with the "mydomain\dbaccount" via Windows Authentication.
We cannot provide password in linked server of "mydomain\dbaccount" as we want to connect via Windows Authentication. And the Login on ServerB is defined as Windows Authentication.
We tried to setup the Linked Server with the local login as "mydomain\testaccount" and impersonate=true and we are getting the following error:

The OLE DB provider "SQLNCLI11" for linked server "ServerB" reported
an error. Authentication failed. Cannot initialize the data source
object of OLE DB provider "SQLNCLI11" for linked server "ServerB". OLE
DB provider "SQLNCLI11" for linked server "ServerB" returned message
"Invalid authorization specification". (Microsoft SQL Server, Error:
7399)

We tried to setup the Linked Server with the local login as "mydomain\testaccount" and impersonate=false and remote user as "mydomain\dbaccount" and we are getting the following error:

Login failed for user 'mydomain\dbaccount'. (Microsoft SQL Server,
Error: 18456)

We tried to setup the Linked Server with the local login as "mydomain\testaccount" and impersonate=true and without remote user and in options we checked "Be made using this security context" and defined remote login as "mydomain\dbaccount" without password and we are getting the following error:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL
Server, Error: 18456)

How can we setup Linked Server with remote Windows Authentication?


texts: Upgrading SQL Server 2008 Standard to Enterprise

texts:
I'm trying to do the upgrade but when it asks me for the Product Key for the current version, I can't find it. When I go on the MSDN website for it, it says that a key is not necessary for this product. So I'm at a standstill. Do I need to do the upgrade directly from the Enterprise ISO instead of from the Standard SQL Server Installation Center Maintenance Edition Upgrade option? How else can I get the product key?

Or when it asks from what version I'm upgrading, can I select Express or Evaluation where it doesn't require me to put in the current product key?  Will that affect the upgrade?

Will I definitely need the ISO?  The same deal with that is that it says the product key is not necessary on the MSDN website since we have an EA.


texts:
I have a table with statistic of games which has next rows (I only write about rows that has interest in this context):

id        BIGINT PRIMARY KEY
date      TIMESTAMP WITH TIME ZONE
status    VARCHAR  -- 'WON' or 'LOSE'
player_id BIGINT   -- FOREIGN KEY

I need to write a query that select number of consecutive win games (status = 'WON') by some user (user_id = %some id%).


texts: MySQL FULLTEXT search not returning any results

texts:
I'm wondering why my fulltext search isn't returning any results.  To me this seems pretty straightforward, but alas it's not working.  I'm far from a DBA, but looking at the EXPLAINed query, I see key_len is zero, which seems odd.

I'm running MySQL 5.5.41

CREATE TABLE

CREATE TABLE `searchable_content` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `page_id` int(11) DEFAULT NULL,
  `menu_title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `content` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `menu_title` (`menu_title`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `content` (`content`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Table content

+----+---------+------------+-------+------------------------------------------------+
| id | page_id | menu_title | title | content                                        |
+----+---------+------------+-------+------------------------------------------------+
|  8 |      42 | Apps       | Apps  |
blah blah blah blah blabbity blah blah
  |
+----+---------+------------+-------+------------------------------------------------+

Query

SELECT
    page_id,
    MATCH(`content`) AGAINST ('blabbity') AS 'relevance'
FROM
    `searchable_content`
WHERE
    MATCH(`content`) AGAINST ('blabbity');

That query EXPLAINed

+----+-------------+--------------------+----------+---------------+---------+---------+------+------+-------------+
| id | select_type | table              | type     | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------------------+----------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | searchable_content | fulltext | content       | content | 0       |      |    1 | Using where |
+----+-------------+--------------------+----------+---------------+---------+---------+------+------+-------------+


texts: Table is marked as crashed and should be repaired

texts:
I have MySQL and Nagios installed Linux PC (Centos5.5)
I have wrote some script to fetch the data from Nagios to Mysql DB (Exactly fetch data in Table)
The data fetching working properly (confirmed with 'COUNT' cmd)
The problem when i run the following command its through error as below,

mysql> select * from nagios_servicechecks;
ERROR 1194 (HY000): Table 'nagios_servicechecks' is marked as crashed and should be repaired

Except this command, all other commands working fine.


texts:
_ Hello,

my setup is a slow SSH to some linux machine. From there I start a mysql terminal client and execute about 200k inserts (in a while loop inside a stored procedure). This takes about 3 hours to complete. However, my inner sense is telling me that 200k inserts can't take that long (the DB is empty btw), so I am guessing that DB's feedback (line prints to terminal's stdin file descriptor) gets printed onscreen too slow; first on the local terminal on the linux machine, and then even slower on my screen I am SSH-ing from.

I would surely like to turn off all feedback from mysqld so that I would know when the inserts finished only when the mysql's client prompt got back. Probably much sooner than 3 hours. How could I do that ?

/etc/my.cnf follows:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

follows the stored procedures:

    select 'Initializing database..';
call TigInitdb();

-- Possible encodings are:
-- - 'MD5-USERID-PASSWORD'
-- - 'MD5-PASSWORD'
-- - 'PLAIN'
-- More can be added if needed.
call TigPutDBProperty('password-encoding', 'PLAIN');
call TigPutDBProperty('schema-version', '4.0');

select 'Adding new users with PlainPw: ', '+391xxxxxx', 'pwd_xxxxxx';
drop function if exists TigUserGen;

delimiter //

create procedure TigAddUser(_user_id varchar(2049) CHARSET utf8, _user_pw varchar(255) CHARSET utf8)
begin
    declare res_uid bigint unsigned;

    insert into tig_users (user_id, sha1_user_id, user_pw)
        values (_user_id, sha1(lower(_user_id)), _user_pw);

    select LAST_INSERT_ID() into res_uid;

    insert into tig_nodes (parent_nid, uid, node)
        values (NULL, res_uid, 'root');

    if _user_pw is NULL then
        update tig_users set account_status = -1 where uid = res_uid;
    end if;

    select res_uid as uid;
end //

create procedure TigUserGen(usr_count INT)
begin
    DECLARE cnt,len INT;
    DECLARE usr CHAR(6);
    SET cnt = 0;
    SET len = 0;
    SET usr = '';

    WHILE cnt < usr_count DO
        SET cnt = cnt + 1;
        SET usr = CAST(cnt AS CHAR(6));
        SET len = CHAR_LENGTH(usr);
        CASE len
            WHEN 1 THEN
                call TigTestAddUser(CONCAT('+39100000', usr), CONCAT('pwd_00000', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            WHEN 2 THEN
                call TigTestAddUser(CONCAT('+3910000', usr), CONCAT('pwd_0000', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            WHEN 3 THEN
                call TigTestAddUser(CONCAT('+391000', usr), CONCAT('pwd_000', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            WHEN 4 THEN
                call TigTestAddUser(CONCAT('+39100', usr), CONCAT('pwd_00', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            WHEN 5 THEN
                call TigTestAddUser(CONCAT('+3910', usr), CONCAT('pwd_0', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            ELSE
                call TigTestAddUser(CONCAT('+391', usr), CONCAT('pwd_', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
        END CASE;
    END WHILE;
end //

delimiter ;

call TigUserGen(200000);

Thanks


texts:
Does anyone know of a setting or trigger for MS SQL 2014 standard edition that would trigger SQL to start using CPU% when system memory hits over 90% usage. I have a small application that is not accessing SQL but does utilize large amounts of memory. When the memory on the system hits 90%+ utilization SQL Server started showing CPU usage on a continual basis until the toolset is stopped.

I've run multiple analysis such as profiler, queries to see the last statements processed, the connections to the database, etc... Nothing is standing out as being the trigger for SQL Server to spike. What is even more odd is that this particular machine has both SQL Server 2014 standard and SQL Server express on it and both of the versions will trigger the spike (this is not my server but one that I am having to work on).


texts: Are there any special characters that are never allowed in SQL?

texts:
I am a Java programmer. We always make database-related things configurable so that they can be easily modified by the DB team as per their requirements.

I want to make some queries as configurable, the problem is I am not able to find a separator for separating queries.

I will later use this separator in the program to separately execute these queries.

Can anyone tell me any separator or a special symbol that is never used in SQL?

Note: As of now I am using comma (,) as the separator but this has a high possibility that my program may fail in future if any query contains comma (,) in it.


texts:
I am trying to browse online but can not get enough info on how to measure performance of scalar UDF's in SQL server 2014.

Newer version yes i do see from plans we can get the same and newer dmv's

But i have on my SQL 2014 where bunch of scalar uDF's exist including the one which includes function involving calculation and functions calling base or user tables as well.

I am seeing blocking and SP performance and seems few calls using those scalar functions. But could not find a way to prove how bad those functions might be when calling from so many effected SP's

please help


texts: ORA-32000: write to SPFILE requested but SPFILE is not modifiable

texts: I'm not able to alter the SORT_AREA_SIZE in my database.


texts: SQL Server query across several linked servers - abysmal performance

texts:
There are three different databases on a three separate linked servers SQL0, SQL1 (hosting 2 dbs), SQL2 (hosting 1 db). SQL0 acts as a reporting server and will host all the databases in question (method TBD - maybe replication maybe sth else), but as the data will be old for some audit purposes (<24H) it will still be necessary to run some queries on live data.

Two setups:

Setup 1. Localhost\SQL0 - linked to SQL1 and SQL2 (each using their respective sa credentials to connect)

Setup 2. SQL1 linked to SQL2 (again, using sa credentials)

Query in question joins (for now) 8 tables across those three databases. In the future there will be another server, another database and several more joined tables in the query, ran regularly by agent and results emailed. It usually returns less than 100 rows, but is limited using TOP 10 while in development.

Why this query ran on SQL2 takes 3s or less to execute, but 105s when ran on SQL0? As long as it's just network traffic I'm willing to disregard the 30x longer execution, but if it's impacting linked servers performance I need to find the solution stat.

All servers are on same LAN, but SQL2 is in another building connected via VPN.

Everything is in SSMS, on a machine connected to same network as all others, no views, no linked servers to linked servers or other "complications"; I have access to all servers as sa.

Query plans (actual), SQL2 and SQL0 respectively:

and


texts:
I'm sure my title is not clear enough...

Let's say I have a "chat" table, storing individual messages like this:

message_id, from_user_id, to_user_id, timestamp

For a given user_id, I want a list of "conversations" for this user. A conversation would be all messages involving the same 2 users. Kinda of like Facebook.

What would be the best way to achieve this? I was starting to write a simple query with GROUP BY but then realized the pair of user ids could be both ways - but to me it's still the same conversation.

SELECT * FROM message WHERE (from_id = 1 OR to_id = 1) GROUP BY ... ??


texts: Store categories in database or use hard coded integers?

texts:
An address can belong to either the mailing or billing categories.

When a user adds an address to a table, should the address category be an independent integer value set by a constant somewhere in the source code, or should it be a foreign key to a table that lists the categories available?

I'm concerned about performance and best practice.

The chances of adding or removing categories are slim to none.


texts: Is a clustered index on column A the same as creating a table ordered by A?

texts:
Reading the official PostgreSQL documentation for version 9.0 I read an interesting escamotage that performs better than CLUSTER for big tables:

  The CLUSTER command reorders the original table by scanning it using the index you specify. This can be slow on large tables because the rows are fetched from the table in index order, and if the table is disordered, the entries are on random pages, so there is one disk page retrieved for every row moved. (PostgreSQL has a cache, but the majority of a big table will not fit in the cache.) The other way to cluster a table is to use:

  CREATE TABLE newtable AS
    SELECT * FROM table ORDER BY columnlist;

  which uses the PostgreSQL sorting code to produce the desired order; this is usually much faster than an index scan for disordered data. Then you drop the old table, use ALTER TABLE ... RENAME to rename newtable to the old name, and recreate the table's indexes. The big disadvantage of this approach is that it does not preserve OIDs, constraints, foreign key relationships, granted privileges, and other ancillary properties of the table — all such items must be manually recreated. Another disadvantage is that this way requires a sort temporary file about the same size as the table itself, so peak disk usage is about three times the table size instead of twice the table size.

The problem is that this suggestion doesn't appear in > 9.0 versions of the official documentation.

My question is if this escamotage is still valid for 9.1, 9.2, 9.3 and 9.4 because I'm stuck with a CLUSTER operation over two big tables (one has ~750M rows and the other one has ~1650M rows) and average disk write/read speed is 3MB/s due to the CLUSTER algorithm explained in the official doc. It's a slow process over big tables, so I'd like to avoid it doing the "create ordered table over index-associated-column" trick. This will save me days of DB processing.


texts: How does a subquery work in sql server 2012?

texts:
I have created one temp table and inserted 10 records into it. The query against the table gets the 3rd highest salary. This query has a sub query. I want to know which query executes first: the inner query or outer query? Kindly check the query which is written below.

CREATE TABLE #tmpTbl
(
id int,
sal money
)

insert into #tmpTbl values('1','12000')
insert into #tmpTbl values('1','22000')
insert into #tmpTbl values('1','50000')
insert into #tmpTbl values('1','100000')
insert into #tmpTbl values('1','100')
insert into #tmpTbl values('1','5000')
insert into #tmpTbl values('1','15000')
insert into #tmpTbl values('1','18000')
insert into #tmpTbl values('1','19000')
insert into #tmpTbl values('1','21000')

SELECT *
FROM #tmpTbl
order by sal asc

SELECT DISTINCT sal
FROM #tmpTbl t1
WHERE 3 = (SELECT COUNT(DISTINCT SAL)
           FROM #tmpTbl t2
           WHERE t1.SAL <= t2.SAL)

Kindly run both queires. The first query will give you salary in ascending order and second will give you 22000.00.

Which runs first: the outer query or the inner query?


texts:
I have installed fresh ubuntu 16.04 with apache2.4 and php7.0 on it and then installed mariadb. Here is version: mysql  Ver 15.1 Distrib 10.0.35-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2.

After this fresh installation I noticed that I can't create normal mysql dump of some bases from any sources including phpmyadmin export, MysqlWorkbench and mysqldump command in shell. The file that is created is very small and most of tables does not contain INSERT query although there are data in tables.

As the result I got 600kb large dump file of 300mb database. I have tried to change max_allowed_packet is set to 2gb so it cannot be the case. As for --no-data argument then it is set to false and event if it would not be - it's very strange that only some tables contain INSERT with data and other are not. User privileges are also fully set for database i'm trying to dump.

I'm trying to fix this for almost 3 days with no result and i would really appreciate some help.

Thanks.


texts: How do I get mysql to start for the first time?

texts:
I want to get mysql to start for the first time.  I installed it on RHEL version 6.  All of the commands posted were executed as root.

As a test, I ran this:

# yum install mysql

This is the output:

  Loaded plugins: amazon-id, rhui-lb, search-disabled-repos Package
  1:mariadb-5.5.47-1.el7_2.x86_64 already installed and latest version
  Nothing to do

The directory /etc/init.d/ has no mysql or mariadb file inside it.

I tried these five commands (and posted the output after them):

# service mysql start

  Redirecting to /bin/systemctl start  mysql.service Failed to start
  mysql.service: Unit mysql.service failed to load: No such file or
  directory.

# systemctl start mysql.service

  Failed to start mysql.service: Unit mysql.service failed to load: No
  such file or directory.

# systemctl start mysql.service

  Failed to start mysql.service: Unit mysql.service failed to load: No
  such file or directory.

# systemctl start mariadb

  Failed to start mariadb.service: Unit mariadb.service failed to load:
  No such file or directory.

# systemctl start mysql

  Failed to start mysql.service: Unit mysql.service failed to load: No
  such file or directory.

I used

  whereis mysql

The resulting outputs gave me no leads of files to start to get the service to start.

What should I do to get MySQL running?


texts: Does assigning variable in SELECT do RBAR operations?

texts:
So, I discovered something interesting.  I was playing around with the TOP command and assigning variables in the select statement.  I issued the below query and found that the variable (@a) value was the last value returned from the Top Command.  It almost seems like for each value returned, that value is assigned to the variable, then each row in the result set is subsequently assigned, almost like a loop.

declare @a nvarchar(max)

select top 10 DisplayName
from dbo.Users

select top 10 @a = DisplayName
from dbo.Users

select @a as Results

Results:

This made me curious.  I modified the query to test again and executed the following:

declare @a nvarchar(max)
declare @b int = 0

select TOP 10 @a = DisplayName, @b = @b + 1
from dbo.Users

select @a as DisplayName, @b as Number

Results:

This is really interesting.  From these two queries, it almost seems like the TOP command is doing some sort of loop because of the cumulative sum that is happening on @b, as well as the assignments happening above.  Can someone explain what is going on?  Does SQL do a RBAR operation on when using the TOP command?

EDIT:

I also did this without the TOP commands, and see the results, so it's not just the TOP command.  It almost seems like assigning a variable in the SELECT statement does a RBAR command?

declare @a nvarchar(max)
declare @b int = 0

select @a = DisplayName, @b = @b + 1
from dbo.Users

select @a as DisplayName, @b as Number

Results:


texts: Why indexes are not speeding up multiple join query

texts:
OK so I have 3 tables: contacts, notes and lastContact.

Questions are:
1) Whenever I want to do a double join of contacts to the other 2 tables the queries explode in time going to up to 15-60 mins.
2) Why with the current indexes just the count of the the primary key in the main table to the other 2 tables take that much time?

They both have a key in common, unique except for notes.

(Structure simplified removing all unnecessary columns)

    CREATE TABLE `contacts` (
    `contactID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `firstName` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `lastName` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `phone` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
    `mobilePhone` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
    `altPhone` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
    `street` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
    `city` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
    `state` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL,
    `postalCode` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
    `email` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `source` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `owner` varchar(64) unsigned DEFAULT NULL,
    PRIMARY KEY (`contactID`),
    KEY `phone` (`phone`),
    KEY `phones` (`phone`,`mobilePhone`,`altPhone`),
    KEY `owner` (`owner`),
    KEY `source` (`contactID`,`source`,`owner`)
    ) ENGINE=InnoDB AUTO_INCREMENT=119138 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE `contacts_notes` (
    `noteID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `contactID` int(10) unsigned DEFAULT NULL,
    `notes` text,
    `dateline` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`noteID`),
    KEY `contactID` (`contactID`)
    ) ENGINE=Aria AUTO_INCREMENT=1486 DEFAULT CHARSET=utf8 PAGE_CHECKSUM=1;

    CREATE TABLE `lastContacts` (
    `contactID` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
    `ultimo` datetime DEFAULT NULL,
    `callID` int(10) unsigned DEFAULT NULL,
    `userName` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
    `lastContact` datetime DEFAULT NULL,
    `callCount` tinyint(3) unsigned DEFAULT NULL,
    PRIMARY KEY (`contactID`),
    KEY `callid` (`callID`),
    KEY `ultimo` (`ultimo`),
    KEY `username` (`userName`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

There are ~50k records in contacts and lastContacts, contact_notes has ~1k.

The query is:

SELECT COUNT(*) total FROM (
    SELECT l.contactID FROM contacts l
    LEFT JOIN lastContacts lc ON l.contactID = lc.contactID
    WHERE lc.contactID IS NULL
) x
LEFT JOIN contacts_notes ln ON x.contactID = ln.contactID
WHERE  ln.contactID IS NULL


texts:
Can I do better than this query?

 select func_returns_str('key1') ||
        nvl2( func_returns_str('key2'),
              ',' || func_returns_str('key2'),
              null)
   from dual;

I would like to avoid calling func_returns_str('key2') twice.
func_returns_str returns a varchar.

Elaborating further:
 func_returns_str could be a generic function like substr() in Oracle which you can't change the definition.


texts: Is there a way to figure out an executing query's plan handle by the query text?

texts:
Is there a way to determine the plan handle for the query plan of an executing query by that query's text?

Perhaps in any of the DMVs?

(I'm trying to find the query stats on an ad-hoc query.)


texts:
I administer a datawarehouse running on SQL Server 2016. This datawarehouse has most of its ETL processing done during a nightly load, except for a few SSIS packages that run throughout the day, then SSAS processing done afterward, so when I arrive in the morning, there's no active monitoring I can perform.

The processing of this nightly load fluctuates, sometimes more than expected, but with the current monitoring setup, there is none other than a sp_whoisactive loaded into a table every 30 minutes, it makes it difficult to try and tune this process. I can obviously look to the plan cache but with the nightly ETL taking about 6 hours, the plan cache does not hold all of the plans used for the night.

Has anyone come across any performance monitoring/logging tools, the cheaper the better, that have been useful when trying to investigate and tune nightly loads like this? I'm most interested in looking for information to help tune indexes or see when/where I have CPU or memory bottlenecks.


texts: Rebuild system databases to a different location

texts:
Firstly, forgive my poor question title. I couldn't think of the best way to summarise this.

Essentially I'm putting together some scenario based DR documentation, with this specific scenario being a SAN issue where we have lost disks which hold either log or data files.

I'm assuming that MASTER has been affected (ie. the drive where it's files exist isn't accessible) and SQL services won't therefore start.

My specific questions are as follows:

If I run a rebuild of sys databases via command prompt using the install media, am I able to specify an alternative drive for the files? My understanding is on a rebuild, the pre-existing default instance location is used, which in this scenario wouldn't be accessible.
Assuming I can get a copy of the mdf and ldf files (by restoring a backup to another instance, for example) is there a way to start SQL Server by pointing it to a different location for the MASTER database? Like a service parameter?

Now I know that it would be possible to mount a new disk with the same drive letter, but I'm looking for a way where I could safely bring the instance up sooner than that, given in a critical situation such as this, the datacenter may have a list of priorities as long as their arm and it could be several hours or worse before the new volume could be ready.

Thanks all...


texts: Blocked Process Report -two updates causing a long block

texts:
I have a .NET application which is getting SQL update timeouts and I believe that some sort of blocking/locking is taking place which is causing this. I have ran a blocking process report on the database and found some potential culprits: 17 seconds worth of blocking.

Below is such log ( there are many similar ):

<blocked-process-report>
 <blocked-process>
  <process id="process308898748" taskpriority="0" logused="0" waitresource="OBJECT: 99:774293818:0 " waittime="17146" ownerId="66317995993" transactionname="UPDATE" lasttranstarted="2015-06-15T12:59:05.817" XDES="0x3bc9cd970" lockMode="IX" schedulerid="7" kpid="11204" status="suspended" spid="161" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-06-15T12:59:05.817" lastbatchcompleted="2015-06-15T12:59:05.817" clientapp=".Net SqlClient Data Provider" hostname="WORKFLOWG10" hostpid="6832" loginname="WorkflowStateUpdaterSP" isolationlevel="read committed (2)" xactid="66317995993" currentdb="99" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame line="1" stmtstart="16" sqlhandle="0x02000000cb1bb914fe051a308bd33cb2b873948749c2a96d"/>
    <frame line="1" sqlhandle="0x0200000075c8f5236facd1f18bca0258f9d4babad99091d6"/>
   </executionStack>
   <inputbuf>
UPDATE [ScheduDB].[dbo].[SP_ScheduleEvent] set DateLastProcessed = GETDATE() where ScheduleEventID = 3111573   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="suspended" waitresource="OBJECT: 99:774293818:0 " waittime="17182" spid="278" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-06-15T12:59:05.780" lastbatchcompleted="2015-06-15T12:59:05.780" clientapp=".Net SqlClient Data Provider" hostname="WORKFLOWG12" hostpid="9944" loginname="WorkflowStateUpdaterSP" isolationlevel="read committed (2)" xactid="66317995589" currentdb="99" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame line="1" stmtstart="16" sqlhandle="0x02000000cb1bb914fe051a308bd33cb2b873948749c2a96d"/>
    <frame line="1" sqlhandle="0x0200000001c6452aee248fd8f9f2be0e27c7038cfa4e334f"/>
   </executionStack>
   <inputbuf>
UPDATE [ScheduDB].[dbo].[SP_ScheduleEvent] set DateLastProcessed = GETDATE() where ScheduleEventID = 3807096   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

As you can see it appears that two updates are blocking each other and I'm struggling to see why.

Here are the database specifics that might help you help me:

TABLE: SP_ScheduleEvent
------
|ScheduleEventID| PK| bigint| not null|
...
|DateLastProcessed| | datetime| not null|
...

INDEX:
ScheduleEventID, "use page locks when accessing the index is set to" FALSE ( row locks is on )

Typically I have many processes running on different machines that would trigger this UPDATE, I run the update from a DBDataContext within a .NET application ( Linq to SQL) like so:

string sqlUpdateString = "UPDATE [ScheduDB].[dbo].[SP_ScheduleEvent] set DateLastProcessed = GETDATE() where ScheduleEventID = " + scheduleEvent.ScheduleEventID;
db.ExecuteCommand(sqlUpdateString);

Thanks


texts: how to publish user defined table type as an article in Transactional replication

texts:
I am new to replication and trying to use transactional replication. I am trying to publish all data and schema. My stored procedure takes user defined table type as input.

CREATE type TableBParam as table
(
    Id Bigint,
    TableAId Bigint not null,
    FieldB1 nvarchar(50)
)

--Deadlock was observed on the save query
go
CREATE PROC SaveTableB
(
 @val [dbo].[TableBParam] READONLY
)
AS
BEGIN
SET NOCOUNT ON;

MERGE [dbo].[TableB] AS T
USING (SELECT * FROM @val) AS S
  ON ( T.Id = S.Id)
WHEN MATCHED THEN
    update set FieldB1 = S.FieldB1
WHEN NOT MATCHED THEN
    insert(TableAId, FieldB1) Values(S.TableAId, S.FieldB1);

END
go

when the snapshot agent runs it gives me an error "Script failed for user defined table type TableBParam"

I couldn't find an option to specify user defined table types in the article dialog when we setup Local publish. I have also explore the article properties to which didn't help me.

Appreciate your suggestions.


texts: How to fix an odd LSBackup job error in Log Shipping?

texts:
Configuration:

LSPrimary01 - Log Shipping Primary
LSSecondary01 - Log Shipping Secondary
OddServer03 - This server is not part of the LS configuration, the odd man out
Sean\LSServiceAccount - The domain user service account for log shipping on all servers

A colleague of mine and I were configuring Log Shipping in a test environment.  We took a full COPY_ONLY backup of a database on LSPrimary01, a log backup, copied to LSSecondary01 and restored the backup and log WITH NORECOVERY.  Then used the GUI on LSPrimary01 to configure Log Shipping.  We already configured file sharing and proper permissions for Sean\LSServiceAccount.

The following error was the result:

START OF TRANSACTION LOG BACKUP
Error: Could not retrieve backup settings for primary ID'cb1564b4-4ffd-a42d'.(Microsoft.SqlServer.Management.LogShipping)
Error: Failed to connect to server OddServer03.(Microsoft.SqlServer.ConnectionInfo)
Error: Login failed for user 'Sean\LSServiceAccount'.(Net SqlClient Data Provider)
END OF TRANSACTION LOG BACKUP

I ran the following query to verify my Log Shipping configuration:

SELECT
      [primary_database]
      ,[backup_directory]
      ,[backup_share]
      ,[backup_retention_period]
      ,[monitor_server]
      ,[user_specified_monitor]
      ,[monitor_server_security_mode]
      ,[last_backup_file]
      ,[last_backup_date]
  FROM [msdb].[dbo].[log_shipping_primary_databases]

Everything in the result set was correct, save monitor_server, it had the value of OddServer03!  As many of you know, when configuring Log Shipping through the GUI, it is damn near impossible to "accidentally" configure this option.

We never intended having a monitor server in the mix.  We use Quest Spotlight to monitor the aforementioned servers which will yield alerts when jobs fail or when log shipping gets behind.

One other perplexing matter, the job that is getting created is LSAlert_OddServer03 instead of LSAlert_LSPrimary01.

Why is the service account trying to authenticate to a server that isn't part of the configuration?


texts: Why is Mysqlcheck on 5.1.41 still showing as Innodb table corrupted after a table dump and restore?

texts:
I had a very large corrupt innodb table. mysqld would crash whenever I did a check table on it. I did a innodb-force-recovery=1 and did a dump of the table. No errors showed up.

I dropped the table and attempted to restore, it completed fine.  I did another mysqlcheck on the table, part way through Mysqd crashed with this error:

Version: '5.1.41-3ubuntu12.10'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 266102.
InnoDB: You may have to recover from a backup.
120203 23:56:24  InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex bc702d5400040f760004
....
...
120203 23:56:24  InnoDB: Page checksum 1745088809, prior-to-4.0.14-form checksum   2260539380
InnoDB: stored checksum 3161468244, prior-to-4.0.14-form stored checksum 2260539380
InnoDB: Page lsn 230 3433899790, low 4 bytes of lsn at page end 3433899790
InnoDB: Page number (if stored to page already) 266102,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 994
InnoDB: Page may be an index page where index id is 0 4668
InnoDB: (index "PRIMARY" of table "mycompany"."Fact")
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 266102.

I thought dumping a table successfully then restoring from it should clear the corruption? why is it still corrupted and I cannot restore it?

This is on an Ubuntu 10.04 release mysql 5.1.41.

This is on a VPS host so a memory check would not work.  Is there anything else that could be the problem?

Any help would be appreciated.


texts: SSRS Report - 'Go to URL' not working. Link does nothing

texts:
I have a report that I moved from one location to another.  The report has several 'Go to URL' links and the location of the files has changed from the original report.

The files I am trying to link to are here: D:\Mobility\BuildManifests\Scooter.

In SSRS, the Go to URL function is:

="javascript:void(window.open('"
    + "file:\\D:\Mobility\BuildManifests\Scooter"
    + "\"
    + Fields!BuildManifestPDFFileName.Value
    + "','_blank'))"

I don't have much experience with this. Please help! Thanks!


texts: PostgreSQL upper() and lower() functions don't understand UNICODE?

texts:
I just discovered something very strange! My PostgreSQL database is set up to use UTF-8 internally and locale C and all very straight forward. Yet look at this:
select upper('Lösungsmittel') = upper('LÖSUNGSMITTEL');

 ?column?
----------
 f
(1 row)

select upper('Lösungsmittel') union all
select upper('LÖSUNGSMITTEL');

     upper
---------------
 LöSUNGSMITTEL
 LÖSUNGSMITTEL
(2 rows)

isn't that strange?! The upper() function was unable to realize upper('ö') = 'Ö'!
The Java Character class can do that without a problem. I am surprised to see that, and we are on PostgreSQL server 13.1, not something super old.
Is there some configuration parameter to configure this character set stuff beyond selection of UTF-8?


texts: Performance issues moving 1 million rows between tables

texts:
I want to replicate 1 million rows from table1 to table2. I have written a job with a query like this:

delete from table1 OUTPUT *.delete into table2

I am executing this job every hour, but it takes a long time.

Another problem is that my log file size is continuously increasing. How can I fix this?


texts: Is it good sign? If SQL Server Buffer and Procedure cache hit ratio always showing 100%

texts:
If 100% Buffer and Procedure cache hit ratio is considered not good in general?

I check Buffer Cache hit ratio with below query and it showing 100% (100.0000000000000)

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio
FROM sys.dm_os_performance_counters  a
JOIN  (SELECT cntr_value,OBJECT_NAME
    FROM sys.dm_os_performance_counters
    WHERE counter_name = 'Buffer cache hit ratio base'
        AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON  a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'
AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'

Are there instances that it is actually bad and can contribute to server performance degradation? I want to know how much of Buffer and Procedure Cache hit ratio is reasonable to have normal SQL server?


texts: DB2 10.5 - When inserting, the database is trying to reuse index keys on GENERATE ALWAYS

texts:
I am trying to insert a row into a database table which has a GENERATED ALWAYS primary key column. The table has over 1000 rows, and the primary keys start at 1. When I try to do an insert, the error message indicates that DB2 is autogenerating the id 1 and trying to insert, which causes an error since that primary key already exists.

This is the error message:

  One or more values in the INSERT statement, UPDATE statement, or
  foreign key update caused by a DELETE statement are not valid because
  the primary key, unique constraint or unique index identified by "1"
  constrains table "PROJECTMGMT.TIMESHEET" from having duplicate
  values for the index key.

This is my primary key:

"TIMESHEET_ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (
   START WITH 1 INCREMENT BY 1 MINVALUE -2147483648 MAXVALUE 2147483647
   NO CYCLE CACHE 20 NO ORDER
),

To try to troubleshoot the issue, I changed the TIMESHEET_ID column to GENERATED BY DEFAULT so that I could manually enter a primary key value and the query worked.

I am fairly new to DB2, and this has me stumped. I haven't been able to find any fix for this.


texts: Benefits of using prepared statements in stored procedures?

texts:
Except from helping avoid SQL injection, what are the benefits - if any - of using prepared statements inside a stored procedure (assume query is only executed once in the procedure)?

Does the engine have any way to keep the prepared statement in memory or is it a wash?

If the answer is depending upon environment, I interested in mysql with innodb.


texts: How to build a table for a private messaging system that supports replies?

texts:
I'm writing a private messaging web application in PHP, the application allows replies such that when you view a message, you also get to see to what was that a reply to, and to what was that a reply to and to what was that a reply to and so on and so on.

I'm trying to find a right database structure that would avoid redundancy, so I'm wondering how can I link a single message to all other messages that it is replying to?

I thought about basically having a field called reply_id which would be a serialized array holding the IDs of all messages to be presented as replies

Does anyone have a suggestion on how to do this efficiently? Is my thought a good practice?


texts:
We recently switched to Ola Hallengren's maintenance script and automated the deployment of MaintenanceSolution.sql to our customers's SQL Server instance.

We need to set these parameters for the job IndexOptimize - USER_DATABASES:

@UpdateStatistics = 'ALL'
@OnlyModifiedStatistics = 'Y'

I see that these parameters are present in MaintenanceSolution.sql:

After I change the values of the above-mentioned parameters in MaintenanceSolution.sql to

@UpdateStatistics nvarchar(max) = 'ALL'

@OnlyModifiedStatistics nvarchar(max) = 'Y'

then execute, I don't see either @UpdateStatistics = 'ALL' or @OnlyModifiedStatistics = 'Y' added in Job Step properties - IndexOptimize - USER_DATABASES:

My questions are:

Why don't "statistics" options appear in the job's command?
Is it wrong to edit MaintenanceSolution.sql directly for this?
Is there a way to add theses parameters to the job using a query?


texts: SSIS create SSISDB catalog certificate error

texts: I am trying to create SSISDB catalog to publish my SSIS packages. While I am following the steps needed to create the catalog I am stuck with this weird error, I googled but I did not found any satisfying answer to this issue. I am getting the following error. I am working on SQL server 2017. Do anyone has encountered such error. Really appreciate the solution.


texts: 100+ DBs in one instance or divide and conquer : MariaDb Ubuntu 64bits

texts: I have a desktop app (100+ customers and more to come) running at customer's small local server, all of them have same structure in tables (different info). I'm migrating that app to web ( almost finished ) and I need to store all those DBs into a new server : Xeon 4cores 4 ghz, 64 Gb ram, 1 Tb sss hd. What could be best approach to correctly handle the information : 1 instance for all DBs or several instances with few DBs ? each DB has 150 tables many with few 1000- records and just some with 100,000- records. Right now I have an instance with 13 DBs working pretty well, but not sure about 100+, any advice is appreciated.


texts:
I have a relation schema R = {A, B, C} and the following functional dependencies:

A → B
A → C
B → A
B → C
C → A
C → B

How many different minimal covers can I derive from this relation schema? I have found the following, but I am not really sure if those are all:

A → C
B → C
C → A
C → B

Also, I am not sure if there is some rule on how to know if one has found all possible minimal covers.

Thanks a lot for any help!


texts: Exporting mysql query result to .txt file

texts:
I want to export query result into .txt file

The path of .txt file and query is dynamic

 DELIMITER //
 create procedure test(filepath text , _query text)
 BEGIN
 SET @sql = CONCAT(_query, ' into OUTFILE ' ,filepath );
  prepare s from @sql;
   EXECUTE s;
  DEALLOCATE PREPARE s;
  END //
  DELIMITER ;

When call procedure test it shows me:

CALL  test1('C:/Users/Tajamul Hussain/Desktop/file database/test.txt ','select firstname from t1 where id =1');

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'C:/Users/Tajamul Hussain/Desktop/file database/test.txt' at line 1


texts: Saved state for SQL Server Always On on Hyper-V

texts:
For our SQL DEV/QA environment our system-engineers did setup an Hyper-V enviroment
- 3 hosts
- 2 VM on each host (1 for DEV an 1 for QA)

Patching for the hosts is scheduled once a month but I noticed the VM's on these hosts are put in a saved mode and not shut down gracefully during the patching.

We are running multiple SQL Server Always On AG's accross these VM.
Is it the best practice to shut the VM's down gracefully before rebooting the hosts or can SQL handle the saved state without problems?

Thanks for your help!


texts:
I have a MySQL database that is about 17GB in size. Right now we don't have a backup for this and I have been tasked to look into backing it up automatically.

I am a software developer myself, not an expert database engineer.

After some research I found out that MySQL has something called MySQL Replication, which will automatically create a backup database on different server.

My concern about this is that if someone accidentally drops a table, or deletes a record, the same will happen on the slave database as well - so that would not serve as a backup.

I need suggestions on what can be done with our MySQL database so that it can be backed up automatically.


texts: When do I have to use archive_command and when not

texts:
I have some doubts about PostgreSQL replication command. I am doing some research and I found severals configurations.

first one is:

archive_mode = on
archive_command = 'cd .'

second one is :

cp -i %p /var/lib/postgresql/9.1/archive/%f'

Then my questions are:

What is the different?
The first one option ( command ) is really strange to me. What do it
do?


texts: How to skip already created table while importing dump file in MySql

texts:
Is there any way to skip already created tables while importing ? I am trying to import 2GB of database using command prompt but the operation is aborted by mistake. Now if i will do the import again it will drop each table and create it again, That will take very long time.

I want to skip those tables from import which is already created or can i start it from where it was aborted ? I am using this command

mysql -u root -p my_database_name < db_dump.sql


texts: MYSQL:[Note] InnoDB: page_cleaner: 1000ms intended loop took 4303ms. The settings might not be optimal. (flushed=396 and evicted=0, during the time.)

texts:
In  MySQL5.7.11,the error log display the

  "[Note] InnoDB: page_cleaner: 1000ms intended loop took 4303ms. The
  settings might not be optimal. (flushed=396 and evicted=0, during the
  time.)"

frequently,and I set the  parameter innodb_page_cleaners=8 and innodb_buffer_pool_instances=8 and reboot the MYSQL service,but the error also show frequently,what should I do ?
Please help me!Thanks a lot!


texts: Oracle: How can I add ROWDEPENDENCIES to an existing table?

texts:
If I have a table t

create table t (x var number);

How can I modify the table so that it appears to have been created with rowdependencies?

create table t (x var number) rowdependencies;


texts: What makes using an Oracle Wallet to connect to a data source more secure than using a username and password to do the same? If someone knows the connection string for using an Oracle Wallet to connect to a data source, isn't this just as good as having the username and password? If not, why not?


texts:
Often law or organization policies require an application to track changes to certain table rows in a database. The changed data is often also viewable through some application page. Some common strategies exist to implementing these, such as database triggers, DAO query injects, SQL Server Change Data Capture...

Is there anything new related to this in SQL Server 2016?


texts: Need an efficient routine to pass object name, with or without "[" and "]" brackets, to get ObjectID

texts:
Someone in their wisdom created a table called user:

select schema_name(schema_id)
,*
from sys.tables
where type = 'U'
 and name like '%user%'

 select top 100 *
 from usr.user

  Msg 156, Level 15, State 1, Line 14 Incorrect syntax near the keyword
  'user'.

I have many scripts where I pass the object name and I need to check where the name passed is valid (exists in the current database) or not.

some are failing when I pass the tablename usr.user as a parameter.

so I had a look as how sql server does this, using sp_help as an example

and I came out with something like this:

--=======================================================
-- check for the existence of object name or data type (system or user)
-- within the current database
-- works on sql server 2016 but probably lower versions too
-- marcelo miorelli
-- 18-july-2017
--=======================================================
SET NOCOUNT ON

declare @objname varchar(108) = NULL  -- object name we're after
declare @dbname varchar(108) = NULL  -- object name we're after

select @objname = 'ipv4' -- this is the parameter to be passed

-- Make sure the @objname is local to the current database.
 select @dbname = parsename(@objname,3)
 if @dbname is null
  select @dbname = db_name()
 else if @dbname <> db_name()
  begin
   raiserror(15250,-1,-1)
   --return(1)
  end

 -- @objname must be either sysobjects or systypes: first look in sysobjects

 declare @objid int
 declare @sysobj_type char(2)

 select @objid = object_id,
        @sysobj_type = type
   from sys.all_objects
  where object_id = object_id(@objname)

 -- IF NOT IN SYSOBJECTS, TRY SYSTYPES --
 if @objid is null
 begin
  -- UNDONE: SHOULD CHECK FOR AND DISALLOW MULTI-PART NAME
  select @objid = type_id(@objname)

  -- IF NOT IN SYSTYPES, GIVE UP
  -- in SQL Server the system tables are deprecated (i.e. syscolumns, sysobjects)
  -- and it's recommended as a best practice to use the views instead,
  -- sys.columns, sys.objects, sys.types, etc

  if @objid is null
  begin
   raiserror(15009,-1,-1,@objname,@dbname)
   --return(1)
  end
end

select [@objname] = @objname
      ,[@objid] = @objid
      ,[@sysobj_type]=@sysobj_type
      ,[@dbname]=@dbname

example with usr.[user] as parameter

usr.user as parameter

and I even went off and created a user defined data type just to test
ipv4

question:
is there a better(performance wise)\more standard way to get this done?


texts: Where to look for possible deadlock causes?

texts:
We've been having several problems the past days in our production environment, and some of them seem to boil down to database issues.

Just realized we are having deadlocks (68 actually), on database "waypoint":

Can anyone please provide hints on where / how to search for possible deadlock causes?

select version();
=> PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

Found this evidence in log:

2016-03-06 06:58:39 UTC [11807-1] waypointtx@waypoint ERROR:  deadlock detected
2016-03-06 06:58:39 UTC [11807-2] waypointtx@waypoint DETAIL:  Process 11807 waits for ShareLock on transaction 370917997; blocked by process 11788.
    Process 11788 waits for ShareLock on transaction 370917865; blocked by process 11807.
    Process 11807: select * from telemetria_data.insert_tabla_instantaneo($1,$2,$3,$4,$5,$6 ) as result
    Process 11788: select * from telemetria_data.insert_tabla_instantaneo($1,$2,$3,$4,$5,$6 ) as result
2016-03-06 06:58:39 UTC [11807-3] waypointtx@waypoint HINT:  See server log for query details.
2016-03-06 06:58:39 UTC [11807-4] waypointtx@waypoint CONTEXT:  while updating tuple (2836,88) in relation "instantaneo"
    SQL statement "UPDATE telemetria_data.instantaneo SET utc = '2016-03-06 06:58:34', registro=now(), power=0, voltaje = 0, valor ='{2147}'::double precision [] WHERE imei=354676055362536 and clase=40;"
    PL/pgSQL function telemetria_data.insert_tabla_instantaneo(bigint,timestamp without time zone,integer,double precision[],integer,double precision) line 13 at EXECUTE statement
2016-03-06 06:58:39 UTC [11807-5] waypointtx@waypoint STATEMENT:  select * from telemetria_data.insert_tabla_instantaneo($1,$2,$3,$4,$5,$6 ) as result

So it seems that the following part is the problem:

UPDATE telemetria_data.instantaneo ....... WHERE imei=354676055362536 and clase=40;

Am I right?

Possible offending function:

CREATE OR REPLACE FUNCTION telemetria_data.insert_tabla_instantaneo(
    bigint,
    timestamp without time zone,
    integer,
    double precision[],
    integer,
    double precision)
  RETURNS boolean AS
$BODY$
DECLARE
    imei ALIAS FOR $1;
    utc ALIAS FOR $2;
    clase ALIAS FOR $3;
    valor ALIAS FOR $4;
    power ALIAS FOR $5;
    voltaje ALIAS FOR $6;
    num_rows int;

BEGIN

    EXECUTE 'UPDATE telemetria_data.instantaneo SET utc = ''' || utc || ''', registro=now(), power='|| power ||', voltaje = '|| voltaje ||', valor =''{' || array_to_string(valor,',')  ||'}''::double precision [] WHERE imei='|| imei ||' and clase=' || clase || ';';

    GET DIAGNOSTICS num_rows = ROW_COUNT;
    IF num_rows > 0 THEN
    RETURN TRUE;
    ELSE

    EXECUTE 'insert into telemetria_data.enabled_units (imei,clase) values ('|| imei ||','|| clase ||');';
    EXECUTE 'insert into telemetria_data.instantaneo (imei,utc,clase,valor,registro,power,voltaje) values ('|| imei ||',''' || utc || ''','|| clase ||',''{' || array_to_string(valor,',')  ||'}''::double precision [],now(),'|| power ||','|| voltaje ||');';

    RETURN TRUE;
    END IF;

    EXCEPTION
    WHEN UNIQUE_VIOLATION THEN
        EXECUTE 'insert into telemetria_data.instantaneo (imei,utc,clase,valor,registro,power,voltaje) values ('|| imei ||',''' || utc || ''','|| clase ||',''{' || array_to_string(valor,',')  ||'}''::double precision [],now(),'|| power ||','|| voltaje ||');';
        RETURN TRUE;

    WHEN foreign_key_violation THEN
        RETURN FALSE;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Related table structure:

CREATE TABLE telemetria_data.instantaneo
(
  imei bigint NOT NULL,
  utc timestamp without time zone NOT NULL,
  clase integer NOT NULL,
  valor double precision[],
  registro timestamp without time zone DEFAULT now(),
  power integer DEFAULT (-1),
  voltaje double precision DEFAULT 0.0,
  CONSTRAINT pk_telemetria_data_instantaeo PRIMARY KEY (imei, clase),
  CONSTRAINT fk_telemetria_data_clase_id FOREIGN KEY (clase)
      REFERENCES telemetria_data.clase (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);


texts: Find out what queries are causing the biggest amount of network traffic

texts: On a production SQL server I am seeing intermittent enormous spikes in data traffic. Up to 200Mbit/s which is causing NETWORK IO waits which in turn cause query timeouts. How can I find out what queries are returning big result sets?