SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

XACT_STATE() vs @@TRANCOUNT

The Voice of the DBA

It Just Works

Access is a database application that just won't die, and it's the subject of a short piece that discusses the reasons why the platform is still in use and thriving in some cases. I wonder how many of you still use, or support, databases that are built in Access and used by people in your organization to get work done.

Like many SQL Server pros, I've often dismissed Access as a poor tool for running an important database system. However, that's been based on my experience with larger organizations that need to scale up a system for multiple people to use concurrently. Access doesn't do that well, though it can certainly handle a small number of people easily.

Perhaps my disdain is also born from experience with Access being used to edit data in SQL Server and taking table locks. That usually builds a blocking chain that ends with my phone ringing, something I try to ensure doesn't happen. However, that's a broad brush against Access. I have a SQL Server DBA friend that used to use Access to edit lookup data. This person would even give a form in Access to a few employees to allow them to maintain some data in SQL Server. Without any issues.

Access is easy to get up and running, which isn't something I'd say about SQL Server. I've used most versions of SQL and built applications on the platform. Getting going quickly, without overhead, isn't something I think about. Even security, which I understand well, isn't simple to explain or get right. I see plenty of people struggling with this constantly, which is likely why many vendors end up using the "sa" account.

There are lots of reasons why SQL Server (or some other server) is a better database platform for an application with any widespread usage. There are also reasons why Access is a good platform for small systems. I've run into a few Access MVPs that are still building and maintaining applications for small customers, with no shortage of work and plenty of accolades from their clients. Maybe the last part of the article says it the best why Access is still in use: " the attraction of something that “just works” has turned many ordinary technologies into overnight sensations." I couldn't say it any better myself.

Steve Jones - SSC Editor

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

Featured Contents

Understanding Outer Joins in SQL

jonfrisbee from SQLServerCentral

The basics of outer joins are shown in this short piece that explains how they work.

SQL Prompt 10: What’s New?

Additional Articles from Redgate

Tony Davis reviews the major new features of SQL Prompt 10, included improved 'ranking' of its code auto-completion suggestions, tab history improvements to make it easier to find 'lost' code, and auto-fixing of code that breaks code analysis rules.

Associating Data Directly with SQL Server Database Objects.

Additional Articles from SimpleTalk

It is easy to attach details and documentation to a SQL Server database using extended properties. In fact, you can add a number of items of information to any database objects such as tables, views, procedures or users.

From the SQL Server Central Blogs - Azure Synapse Analytics new features

James Serra from James Serra's Blog

Almost lost in all the announcements from Ignite was a bunch of amazing new features that were added to the Provisioned Resources/SQL Pool section (read SQLDW functionalities) side of...

From the SQL Server Central Blogs - Wrangling data flows in Azure Data Factory: Calculating distance between two Geo-points

Rayis Imayev from Data Adventures

(2019-Nov-10) Microsoft has recently announced a public preview of the Wrangling data flows in Azure Data Factory (ADF). What used to be called Power Query in Excel, Query Editor in Power...

Question of the Day

Today's question (by sergey.gigoyan):
	XACT_STATE() vs @@TRANCOUNT
Let’s assume we have an empty table in the TesDB database with the following structure: USE TestDB GO CREATE TABLE TestTable ( ID INT PRIMARY KEY, Val INT ) GO In a new query window, we start the following transactions: USE TestDB GO BEGIN TRANSACTION INSERT INTO TestTable(ID, Val) VALUES(1, 100) BEGIN TRANSACTION SELECT XACT_STATE() AS 'XACT_STATE' SELECT @@TRANCOUNT AS '@@TRANCOUNT' INSERT INTO TestTable(ID, Val) VALUES(2, 200) COMMIT COMMIT What will be the selected values for the XACT_STATE and @@TRANCOUNT?
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)

Leaving SQLCMD

I type SQLCMD to connect to my server, and use the "q" parameter. I end up with a 1> as shown here.

SQLCMD command prompt

What do I type to exist SQLCMD?

Answer: quit or exit

Explanation: Either quit or exit will work, as will a CTRL+C. Ref: SQLCMD - https://docs.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-use-the-utility?view=sql-server-ver15

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 - Development

how can i see the last 100 select statements that were run by any user - how can i see the last 100 select statements that were run by any user against a particular table?

Identity column as Primary Key - good or bad? - I'm building a web app to maintain some data, which is mostly flat but will benefit from having some static look-up tables. Typically these look-up tables with have at most a dozen rows and are unlikely to change over time. Consider the two different DDL scripts. The first creates a look-up table CustomerType with an […]

SQL Server 2016 - Administration

DB goes into not sync / suspect after a manual failover - After the patching, one of the database goes into Not synchronizing / suspect state. I resumed a data movement manually. It goes into recovery pending to suspect. This is from the log file 'DB has been suspended for the following reason: "system" (Source ID 2; Source string: 'SUSPEND_FROM_REDO'). To resume data movement on the database, […]

SQL Server 2016 - Development and T-SQL

One table or two? - I have an outside company that will be sending me 2 files each month. File 1 has approx. 280 columns, File 2 has around 330. The first 150 column names on each table are exactly the same. The remaining columns on each file are unique to that file(no overlapping column names). There is no one-to-one […]

How to apply rank when fetching data from table using sql query? - Hi Everyone, I need your help to get the required results. I want to get a machine status every 5 minutes. here how to apply rank as the sequence number for given resultset. the Rank should be a mentioned snapshot. Thanks in advance for your support Sample Script select Starttime, Endtime,Status as machineStatus,datediff(minute,starttime,Endtime)as diff […]

list to table, versus delimitedsplit8k - Dear reader The list to table is build around a while loop, one item is taken of the list and put in a table, the list is shortened and the while loop is repeated. On seeing this construction I thought this would be a good candidate to be replaced with the delimitedsplit8k function. So to […]

Development - SQL Server 2014

repeating block of records - I want to look for blocks of records repeating in a table. pattern is unknown...to keep it simple - table X can have up to 100 records in the example below: The block: A,B and C is repeated 2 times I don't care about (A and B) or (B and C) repeated twice...I […]

SQL 2012 - General

Compare two databases - Hi , What are the parameters to compare two databases if migrated from source to destination .

Need of database - In order to structure and layout 50GB of data which includes pdf files, videos, images, etc, which is the better way to implement? Using sql or not? Please can you tell me the pros and cons of it. Thank you. This is for an intranet site development!

SQL Server 2008 - General

Backup file size 3X after shrinking the log file? - Here's a head scratcher - I've not seen anything like this in 20+ years of DBA life! I received a SQL Server 2008 R2 backup file to restore. There wasn't enough disk space on the server so I moved the file to my workstation to look at it. After restoring the database, the transaction log […]

Using crypt_gen_random to generate unique Serial Numbers - Trying to generate unique serial numbers with alphanumerics (A-Z,a-z,0-9) and no special characters.Used below code where sLength was min 10 and max 12 as defined in front end. declare @sLength tinyint declare @randomString varchar(50) declare @counter tinyint declare @nextChar char(1) declare @rnd as float set @sLength = 3 set @counter = 1 set @randomString = […]

OPENQUERY is not inserting identical rows - Hi, Thanks in advance for any help. I am trying to insert into a sql table using data from an oracle server using a linked server. I have noticed it ignores any lines which are identical. However I do not want to ignore these and would like the query to insert the rows. I have […]

SQL Azure - Development

Azure file copy creating a directory to hold the renamed file - Hi All, I'm pretty new to azure and am hitting the following problem. I've got a notebook that creates an output csv file. The file gets named to the default Part-0000-tid and I want to rename it to something more sensible. The code below shows what I've done. The problem is it creates a subfolder […]

SQL Azure - Administration

Metric CPU show 100% , but Query Performance Insight show only 60% same period - Hi, I am investigation DTU spike for period specific period of time In my case time period in question between 08:00 PM and 11:00 UTC Azure portal ->db ->metric -> Cpu % avg show that I have 100% usage of CPU (db DTU = 1750) between 08:15 PM and 08:45 PM Azure portal->db-Query Performance Insight-> […]

Integration Services

Dynamically execute SSIS package. - I am trying to figure out how to dynamically execute SSIS package based on file path that are stored in the table. I found some examples online, it won’t work for me for some reason. The path could be something like this \\ServerName\\SubFolder\Name.DTSX. Most examples I found online has something to do with SQL Server […]

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.