SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Run Queries Across Many Data Sources at Once with the CData Query Federation Driver

JerodJ from SQLServerCentral

The CData Query Federation Driver, allows you to federate and aggregate data across drivers as a single database connection. You can combine the Query Federation Driver with other CData JDBC Drivers or CData ADO.NET Providers to access all your SaaS, Big Data, and NoSQL sources as if they were one database. You can query or update data from a single […]

Migrating Column Level Encryption from 2008R2 to Azure SQL Database

Gareth Barnes from SQLServerCentral

A migration to Azure is stuck because of an encryption change in SQL Server. Learn how to get around this by using a certificate for encryption.

How to Create a FOR Loop in SQL Server

Additional Articles from MSSQLTips.com

Learn different ways to create FOR Loops using T-SQL such as using WHILE loops, cursors, tally tables and more.

Dealing with Failed SQL Migrations in MariaDB or MySQL

Additional Articles from Redgate

This article explains the fastest ways to restore the previous version of the database, to recover from a failed Flyway migration that leaves the database in an indeterminate state, and then how to adapt your database development process to avoid these problems.

From the SQL Server Central Blogs - The Chili Shirt–Memories of Euan Garden

Steve Jones - SSC Editor from The Voice of the DBA

Early on in the history of SQL Server Central, we were offered the chance to work in a booth a TechEd and promote our little site. Brian Knight, Andy...

From the SQL Server Central Blogs - Three-part virtual SQL Server learning this week

kleegeek from Technobabble by Klee from @kleegeek

I’m thrilled to announce a three-part remote education event in conjunction with the Iowa SQL Server community! The topic is SQL Server IaaS and virtualization performance tuning, and it’s...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Hiding Your New Year's Age
There is a table that has everyone's date of birth stored in it. The column is dob and it is a date type. This column has the default function applied to it to prevent non-privileged users from seeing this information. If a user without UNMASK ones this query, what do they see as the result? SELECT DATEDIFF(YEAR, dob, '2022-01-01') FROM dbo.Guests AS g WHERE g.Guestname = 'Susan'
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)

Variant Math

What does this code return?

DECLARE @i SQL_VARIANT = 4;
SELECT @i + 2

Answer: A conversion error

Explanation: SQL_Variant is not implicitly converted to an integer. You get a conversion error noting that CONVERT is needed (or CAST) to perform this operation. Ref: Data type precedence - https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?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 - Administration

failure - There is a error in the application logs with table name not allowing nulls into column. However, the sql server error log does not show which proc is causing the error. Looked at monitoring tool did not find anything associated. Is there any other place to look into it. Do yo think the better option […]

SQL Server 2017 - Development

Customized Schedule Job through TSQL - Hi All, After a long time posting my query. It would be great help if anyone can assist on this. I would to like to create one schedule for sql job which should run after every 1 week from the active start date. For Example : If active start date of the job is […]

Development - SQL Server 2014

Count based on condition - Hi I have below query . In T1.Sample1,T1.Sample 'Y' & 'N' is stored . I want to display Count of T1.Sample1 which have & and similarly of T1.Sample2 SELECT T1.U_VendCode,T1.U_VendName as "Vendor Name",T1.Sample1,T1.Sample2 FROM [tbl1] T0 inner join [tbl2] T1 on T0.Docentry = T1.DocEntry where (T0.U_DATE BETWEEN '2021-03-01' and '2021-03-31' ) group by T1.U_VendCode,T1.U_VendName,,T1.Sampl1,T1.Sample2 Code […]

Error - Each GROUP BY expression must contain at least one column - Hi Error - Each GROUP BY expression must contain at least one column that is not an outer reference. SELECT T1.U_VendCode,T1.U_CabNo,T1.U_VendName as "Vendor Name",Count(*), (Select Count(*) from [tbl2] where T1.U_APDE <> 'N' group by T1.U_VendCode,T1.U_CabNo,T1.U_VendName,T1.U_Custcode,T1.U_Custname,T1.U_APDE,T1.U_ARDE,T1.U_ItemCode), T1.U_CustCode as "Cust Code",T1.U_CustName as "Cust Name",T1.U_ItemCode FROM [tbl1] T0 inner join [tbl2] T1 on T0.Docentry = T1.DocEntry where (T1.U_ItemCode is […]

Inner select count(*) is not returning any data. - Hi Inner select count(*) is not returning any data. SELECTT1.U_VendCode,T1.U_CabNo,T1.U_VendName as "Vendor Name",Count(*),(Select Count(*) from [tbl1] where U_APDE = 'Y'),T1.U_CustCode as "Cust Code",T1.U_CustName as "Cust Name",T1.U_ItemCodeFROM [tbl0] T0inner join [tbl1] T1 on T0.Docentry = T1.DocEntrywhere NOT(T1.U_APDE = 'N' AND T1.U_ARDE = 'N') and (T1.U_ItemCode is not null) and (T0.U_DATE BETWEEN '2021-03-01' and '2021-03-31' )and T1.U_CustCode […]

SQL 2012 - General

Urgent help needed. MS SQL Server 2012 - Hi, I've just got a call (6pm on a Friday!) from a customer who has had his line-of-business application provider sell him a solution which required SQL Server 2012 Standard, yet the supplier (joyfully) installed the evaluation version of SQL Server 2012 Enterprise. This has now ticked over and expired. My problem is that (a) […]

SQL Server 2012 - T-SQL

how to make comm seprated list with control break. in sql 2012 - hi, col "R" has report no like "R1" "R2" ETC COL "D" AS Direction like "D" "D1". direction can be more than one in one report. --uniqueness of record is col R + D result needed is all directions of the table IN one SCALAR column with all comma seprated and ";" seprated like FOLLOWING. […]

SQL Server 2019 - Administration

Getting error Failed to open connection dialog when trying to open SSMS 18 - I've got SSMS 18.3 installed on my laptop. When I open it, I'm getting an odd error I've never seen before: Failed to open connection dialog Object reference not set to an instance of an object. (Microsoft.SqlServer.Management.SqlStudio) I cannot get past this, and I don't know how to fix it. How do I fix this […]

SQL Server 2019 - Development

SQL Agent Job Use Different Environment Variable - Hi, Using SQL2019, we have multiple projects and packages in our SSISDB. They all reference a single shared Environment which holds our Connection strings. I have a package using a project level Connection. This package by default uses Connection1, so configuring the sql agent job is simple and this job runs weekly. I want to […]

SQL Filestream Retrieve File with VB or SQL - Hello, I want to use SQL Filestream to store files. I am a fairly novice developer and this is new. I have set up my database to store files and have successfully imported files with SQL and using Excel VB to execute the SQL. I would like to use Excel VB to export the file […]

copy rows in the same table based on conditions - I have the table as in data below , now I would like to copy all of the rows in which TEXT1 is 'erledigt' and the DATE2 is within the year 2021 , I would like to copy the rows within the same table and make sure to copy the same number of rows as […]

Passing a column into CONTAINSTABLE/CONTAINS - I am running a search, passing a column with keywords into inner joint. SELECT SentimentCategoriesAncors._pk AncorID, Clip._pk ClipID FROM SentimentCategoriesAncors INNER JOIN Clip ON Clip.title LIKE '%' + SentimentCategoriesAncors.incude + '%' I need get some speed and put some fuzzy logic in play. So, I indexed Clip.title into Full-Text Search and trying to do something […]

3 calculations on different levels - Hello everyone, I am asking something that I am not even sure is even possible therefore I will appreciate any suggestion or advice. I have a SQL code that works just fine. But I have an issue My calculations for Inventory On Hand and On Order are done using LOC column (it is basically a […]

General Cloud Computing Questions

Noob questions about Cloud computing - What made it so prevalent? - We're seeing everything going to the cloud these days, from data storage to phone systems. I don't fully understand what technologies have made cloud computing so prevalent and easier to implement. Is it due to Internet bandwidth? Better software?

Integration Services

Azure Data Studio for managing SSIS packages? - Hello all, I was wondering if anyone knows of a way to manage SSIS packages using Azure Data Studio? For example, I'd like the functionality of deploying packages: ...and checking on the results from packages that have run in the past: I've Googled and searched this forum but can't find any information on it. This […]

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.