SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

How do you debug your applications that work with SQL Server?

These could be .NET applications that query the database. You could have ETL processes using SSIS or some other tool that you work on. Perhaps you have a system that runs entirely inside SQL Server and you need to untangle your T-SQL.

Do you use Visual Studio tools? Have you configured the T-SQL debugger? Are you a PRINT statement or temp-table-for-results developer? Perhaps you have logging or some other mechanism that you use?

Let us know this week what works well for you, and if you've found a particular technique to be handy in a situation, we'd love an article that might teach someone else how to debug their code.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

Featured Contents

Understanding Machine Learning

Narendra Bhogavalli from SQLServerCentral.com

This article describes about the understanding Machine Learning and bits of Artificial Intelligence as it’s a superset of Machine Learning.

The Sins of SELECT * (BP005)

Additional Articles from Redgate

If Prompt warns you of use of the asterisk, or 'star' (*), in SELECT statements, consider replacing it with an explicit column list. It will prevent unnecessary network load and query performance problems, and avoid problems if the column order changes, when inserting into a table.

From the SQL Server Central Blogs - How was it done back in the day? T-SQL Tuesday #114

Kenneth.Fisher from SQLServerCentral

It’s TSQL Tuesday and Matthew McGiffen (b/t) is our host with a subject near and dear to my heart. Puzzles! ... Continue reading

From the SQL Server Central Blogs - Maximum Number of Rows for the Table Value Constructor

Solomon Rutzky from SQLServerCentral

What is the maximum number of rows for the Table Value Constructor? And, do different uses of it behave differently?… Continue reading Maximum Number of Rows for the Table...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Memorial Day
Completely unrelated to databases, but since I like sports: which of these takes place on Memorial Day in the US (Since 1986)?
Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Contract Requirements

When creating a Service Broker Contract, what is required?

Answer: At least one message type and at least one direction that includes the initiator (SENT BY INITIATOR or SENT BY ANY)

Explanation: When creating a contract, at least one message type must be specified and the initiator must be allowed to send at least one message. You can specify this in one of two ways

SENT BY INITIATOR
SENT BY ANY

Ref:

Service Broker Basics Part 1 - https://www.sqlservercentral.com/articles/service-broker-part-1-service-broker-basics
CREATE CONTRACT - https://docs.microsoft.com/en-us/sql/t-sql/statements/create-contract-transact-sql?view=sql-server-2017

Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 - Administration

error Output File cleanup does not delete files script d'olla - Hello everyone, I use olla scripts in my backup and maintenance operations there is a step that overwrites the old log file Output File Cleanup this and it comes out with an error Message Run as user: xxxxx \ SQLAGENT2K8QUA. ... 20190214_220307.txt "Access denied." Q: \ Backup \ log "\" DatabaseBackup_FULL_0xBEE0AC49E7982D49842F71412C701125_1_20190228_220234.txt "Access denied." Q: \ […]

SQL Server 2017 - Development

Primary Keys - What is a good way to determine which combinations of fields should make up a primary key?

Reading data from 0x05 - Hi We currently have a table, with a column of a varbinary(MAX). This contains data which precedes with 0x50 – from what I understand means that the data has a ZIP format and I believe it has been compressed. We need to be able to read this data. I know that this is a .docx […]

SQL Server 2016 - Administration

Lock Request Timeout period Exceeded Error 1222 - A long running job is doing a SELECT INTO a table, so the table gets created at the same time as it's being populated. If I try to expand the DB's list of tables, I get the error "Lock Request Timeout period Exceeded Error 1222" Is there a way to get around this ?

Error: Nonqualified transactions are being rolled back - Every morning, the Prod Backup file is copied to the Reporting server, and restored. This morning, the restore step seems to have completed, but the step shows the following error, causing the overall job to fail, so subsequent steps did not run. Thoughts on the cause(s) for this on a just restored DB ? Error: […]

Restore DB - what is the procedure to restore a database of size 2 TB with a minimal down time ?

Migrating from (mostly) 2K8 to 2016 - HI all, About to start this project and would be grateful for any good comprehensive articles rated highly by the good folks here at SSC TIA

SQL Server 2016 - Development and T-SQL

Organizing and cleaning up AS400 DB2 data in SQL server - I've been dealing with data migration out of ancient AS400 piece by piece through linked server connection and I'm having hard time figuring out the best way to structure everything into more or less decent way. I'm joining multiple tables into views to get a piece of data I'm looking for and then creating tables […]

Development - SQL Server 2014

TLS 1.2 configuration with MS SQL 2014 - Dear Gurus, I have MS SQL 2014 Installed with following configurations. Windows 2016 SS. MS SQL 2014 + SP3 64 Bit EE. TLS 1.0 Client : Disable Server Disable TLS 1.1 Client : Disable Server Disable TLS 1.2 Client : Enable Server Enable ODBC Drivers : 64 Bit: 11(2014.120.6024.00) MSODBCSQL11.DLL Below query works fine […]

Fetching unique record within a stored procedure - I have a table "Mytable" containing a list of IDs. There is a web application and the user logging into the web application is assigned an ID from the table mentioned above. So if 2 users are logging in concurrently, an ID from this table is given to each of the user. The ID and […]

SQL Server 2012 - T-SQL

Net Earned in Triangles without a Triangular Join? - Something I've been trying to figure today is can I achieve a figure for net earned in our insurance triangles without using a triangular join. I'm sure I could do this with a Window Function, but I can't seem to think of a way to do so. The way the figures work is to do […]

SQL Server 2008 - General

Encyrption and change field as same time - Hello all. I am doing this to modify a field and encrypt data in that field: --Update EncryptedName field with a new name UPDATE dbo.Tbl_Table SET EncryptedName = 'My Name' Where dbo.Tbl_Table .ID = 1234 Go --Encrypt the field. OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION BY CERTIFICATE MySymmetricCertificate; UPDATE dbo.Tbl_Table SET EncryptedName = EncryptByKey(Key_GUID('MySymmetricKey'), EncryptedName ) […]

Duplicate title - Duplicated post. plz ignore

Amazon AWS and other cloud vendors

increase size instance RDS - Hello everyone I would like to create an RDS instance I will create my instance with an initial size of 20G0 if tomorrow I will need to increase space dedicate will it be possible to thanks

Analysis Services

Need to perform leaf level aggregation - So I have a DW with a table with some pre-aggregated scores (not my choice) and the counts of animals separated by breed and a date. To calculate the average score across breeds, I need to the following calculation: ([AverageScore]*[CountOfAnimalsForBreed])/TotalCountOfAnimals The problem is that when the cube rolls up for a specific date, it is […]

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.