Q&A archive
Questions and answers Andy Mallon contributed across the Stack Exchange network.
Stack Overflow
For a while, I have been thinking about using my Stack Overflow answers (and answers on other sites on the Stack Exchange Network), and storing a copy of them here, on my blog as well. All posts on the Stack Exchange network are covered under the Creative …
Read answerStack Overflow
Question I have the following SQL Server table : create table Account( -- column names with their data types and properties Id int not null identity primary key, User varchar(50) not null, Job varchar(200) not null, -- table limitations constraint …
Read answerDBA Stack Exchange
Question We have a 3TB OLTP database running Siemens Opcenter MES software at one of our manufacturing plants in China. This is setup in an Always-On Availability group with one secondary node. As this is a manufacturing plant, we are only provided 11 1/2 …
Read answerDBA Stack Exchange
Question I'm passing a JSON string to a stored procedure in order to insert warehouse inventory. Sometimes, there will be multiple line items with the same product, going to the same shelf, on the same pallet (LPN), with the same date. I would like to …
Read answerDBA Stack Exchange
Question In Javascript, Excel, Python, and a scientific calculator, the modulo operation -34086.1576962834 % 360.0 <-- python =MOD(-34086.1576962834, 360.0) <-- Excel functiongives a value of 113.84230371659942. (Or something very close to that, depending on …
Read answerDBA Stack Exchange
Question I am trying to establish whether SQL Server is affected by the recently discovered OpenSSL vulnerabilities. For example, are SQL Servers that are using TLS/Certificates to encrypt connections between client and server affected to? I believe SQL …
Read answerDBA Stack Exchange
Question I just learned that indexes can be disabled on a table. Even the clustered index can be disabled. After that, users can’t access the index or the data if clustered index. When will we disable an index? I just don’t understand the use case.
Read answerDBA Stack Exchange
Question We are trying to solve connectivity issues for one of the app trying to pull data from very secured sql server behind firewall. I am not that expertise in security but need help on how to proceed in opening ports for that app to pull data from sql …
Read answerDBA Stack Exchange
Question Each night my SQLServer replication failed on a big table with the following error. It is failing on a different table each night but always on a relatively big table ( > 200,000 rows) The process could not bulk copy into table '"dbo"."Table01"'. …
Read answerDBA Stack Exchange
Question I am trying to import a bacpac file to my SQL Server Express 2016. An exception is thrown during the import with GRANT ADMINISTER Database Bulk Operations TO [MyUser];Returns: Incorrect syntax near 'ADMINISTER'. I am running the import as the sa …
Read answerDBA Stack Exchange
Question I'm simulating the situation where I take a backup from instance A and restore it on instance B. I followed the doc to Backup to Disk with Encryption. Since I'm using only my desktop to simulate the whole process, after taking the backup of the …
Read answerDBA Stack Exchange
Question This has probably been asked before, but I couldn't find the answer. I have a complex datamodel in sql-server-20XX (more likely to be something old) in which cascade delete has been turned off (by design). A large chunk of our database has been sold …
Read answerDBA Stack Exchange
Question I'm really confused by this SQL here, I have this sample where I am using the value from the main query in the subquery and then using XML PATH to add a comma between the dates. The setup is 2 tables one with the dates I want to string together. I am …
Read answerDBA Stack Exchange
Question Our team is migrating one of our databases to Amazon RDS MS SQL Server. As part of that, we have to rewrite some of our stored procedures. We have a stored procedure that performs a database backup to a network storage.
Read answerDBA Stack Exchange
Question When I am running: Ctrl+Alt+R will run the web browser in an SSMS tab. However, this now produces the message: I do have MS Edge installed but SSMS is not finding it. What should I do to help SSMS find MS Edge and use it?
Read answerDBA Stack Exchange
Question I'm trying to find a way to allow an application to create tables and insert data into them on a SQL Server 2019 while protecting from injection attacks in case the app credentials would leak. My experience is limited when it comes to writing code …
Read answerDBA Stack Exchange
Question I have a SQL query that looks for values in a table on a range of dates. If no record is found, I would like to generate rows with default values. Example of one of the table existing records:
Read answerDBA Stack Exchange
Question Apologies in advance if I don't use some terminology correctly here. I'm not a DBA but I've been temporarily forced into the role. I've inherited an application that runs on SQL Server 2016 Enterprise - one instance for PROD and another for TEST. …
Read answerDBA Stack Exchange
Question Can anyone help with this error? SQL: SELECT InvoiceDate, BillingAddress, BillingCity, Total, CASE WHEN Total < 2.00 THEN 'Baseline Purchase' WHEN Total BETWEEN 2.00 AND 6.99 THEN 'Low Purchase' WHEN Total BETWEEN 7.00 AND 15.00 THEN 'Target …
Read answerDBA Stack Exchange
Question I recently had to uninstall Visual Studio 2012 and DTS, then install VS 2017 and DTS again. Afterwards, The directory containing sqlcmd.exe was not in my PATH variable. I installed the following. vs_Professional-vs2017-15.9.41.exe …
Read answerDBA Stack Exchange
Question That's a weird scenario. The transaction log is full. It waits on AG. AG is all good. No latency, last commit/hardened time is almost real-time on all the nodes. The Estimated Recovery Time is zero. I checked this from the primary node and …
Read answerDBA Stack Exchange
Question I have an application that uses PostgreSQL as RDBMS. There is a table containing orders placed by customers. The table has an id as primary key, an order_number (integer) and a created_at field (timestamp). id and created_at are assigned …
Read answerDBA Stack Exchange
Question I have a query that pulls information for a database from the following tables sys.master_files sys.databases sys.filegroups Currently, the context set is the specific database which I want to query about. But if the database is in a mode such that …
Read answerDBA Stack Exchange
Question doing some work with the SQL Perfmon counters and banging my head against the wall trying to work out how to translate the instance_name column to a database name. So if I run a query like the following SELECT top 10 left(counter_name, 25), …
Read answer