Product:

Microsoft SQL server 2019

Issue:

How find what SQL Query is it that i is working one?

Solution:

When you have a long list of Query’s you work with, click on the little PIN one each and they will stay at the top. And maybe it is easier for you to find back to the one you want to work with.

 

Other tip is to use WHERE 1=1 in your statements, to easy then remark the lines you need or not need when you adjust your selection.

SELECT TOP (1000) [GeographyKey]
,[City]
,[StateProvinceCode]
,[StateProvinceName]
,[CountryRegionCode]
,[EnglishCountryRegionName]
,[SpanishCountryRegionName]
,[FrenchCountryRegionName]
,[PostalCode]
,[SalesTerritoryKey]
,[IpAddressLocator]
FROM [AdventureRestore].[dbo].[DimGeography]
WHERE 1=1
and CountryRegionCode = 'AU'
--and city = 'Alexandria'
and PostalCode = 2015

 

More Information:

https://www.red-gate.com/simple-talk/wp-content/uploads/imported/1307-keystrokes.pdf

https://learn.microsoft.com/en-us/sql/ssms/sql-server-management-studio-keyboard-shortcuts?view=sql-server-ver16

https://www.mssqltips.com/sqlservertip/2542/display-line-numbers-in-a-sql-server-management-studio-query-window/

https://sqlstudies.com/2022/07/21/ssms-put-pinned-tabs-in-their-own-row/

Product:
Microsoft Windows

Issue:

How rename many files in a folder by change the first letter to a other letter?

Solution:

Go to the command prompt.

Go to the folder where the files are.

Enter below to change all files to start with the letter b

ren  *.* b*.*

 

More Information:

https://www.alphr.com/how-to-batch-rename-files-in-windows-10/

https://www.partitionwizard.com/partitionmagic/batch-rename-files.html

 

Product:
Microsoft SQL Azure database

Issue:
How create a new database from a BACPAC file in SQL Azure?

The Azure portal only supports creating a single database in Azure SQL Database and only from a .bacpac file stored in Azure Blob storage.

Depending how your firewall and network endpoints are setup in the SQL azure, you can get different problems.

https://learn.microsoft.com/en-us/azure/azure-sql/database/database-import?view=azuresql&tabs=azure-powershell 

Error: The ImportExport operation failed because of invalid storage credentials, can be that you need to click in “use private link” for the subscription.

You need to select the backup bacpac file from your blob storage.
You should select the database model you want to get a big and valuable SQL Azure database created.
Enter a name that you can easy find from the other databases.

The ImportExport operation with Request Id 'xxxxx-xxxxx' failed due to 'The server principal "donald" is not able to access the database "sqldatabasename-new" under the current security context.
Cannot open database "sqldatabasename-new" requested by the login. The login failed.

Error like above, can be that you do not have access to the database, the user account is not ADMIN on the SQL Azure server.

Other common issue can be that the BLOB storage is not allowing access from the SQL Azure database.
When doing the import above, you need to monitor both Private Endpoint Connections and SQL azure private access, to Approve the request for access that the above import process will ask for.

You can also may need to add the SQL server as resource type.  But it may still fail, if you have not given access to the SQL server over the network from the BLOB storage.

Solution:

If you know you have access from your computer to the SQL azure server, then you should upload the DACPAC from you c:\temp folder with a powershell script.

.\SqlPackage.exe /Action:Import /SourceFile:"C:\temp\sqldatabasfilesavedname.bacpac" /TargetConnectionString:"Server=tcp:sqlservername.database.windows.net,1433;Initial Catalog=databasename_test_restore;Authentication=Active Directory Password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;User ID=donald.duck@company.com;Password=xxxxxxx" /p:DatabaseEdition=Standard /p:DatabaseServiceObjective=S4

First you need to install the DacFramework.msi from https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver16

Then start powershell as administrator and go to the folder C:\Program Files\Microsoft SQL Server\160\DAC\bin, and run above powershell script after you change the names to match yours.

Parameters used in script:

.\SqlPackage.exe /Action:Import
= start the process and what action we do – in this case import
/SourceFile:”C:\temp\sqldatabasfilesavedname.bacpac”
= tell the location and filename of the file to restore from
/TargetConnectionString:”Server=tcp:sqlservername.database.windows.net,1433;
= tell protocol and name of SQL server and port the script should use
Initial Catalog=databasename_test_restore;
= tell the new name of the database in SQL azure
Authentication=Active Directory Password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
= set parameters that you will login with active directory
User ID=donald.duck@company.com;
= enter the name of a administrator – this account should not be specified in the database
Password=xxxxxxx”
= enter the password for the account
/p:DatabaseEdition=Standard
= set the version of SQL Azure database you want in the subscription
/p:DatabaseServiceObjective=S4
= set the number of DTU you will use first in your new database

When it does the import – it start with this – check the line for servername to ensure it is correct

Importing to database ‘databasename_test_restore’ on server ‘tcp:sqlservername.database.windows.net,1433’.
Creating deployment plan
Initializing deployment
Verifying deployment plan
Analyzing deployment plan
Importing package schema and data into database
Updating database
Importing data
Processing Import.
Disabling indexes.

….

When done it should say something like this:

Successfully imported database.
Changes to connection setting default values were incorporated in a recent release. More information is available at https://aka.ms/dacfx-connection
Time elapsed 0:13:32.42

 

If you get below error, it can be that you are on VPN, and need to be at the office instead, to be allowed to connect to the Azure SQL Server.

The ImportExport operation with Request Id ‘xxxxx-xxxx’ failed due to ‘An error occurred while communicating with the SQL Server using AdPassword-login: AADSTS50076: Due to a configuration change made by your administrator, or because you moved to a new location, you must use multi-factor authentication to access ‘xxxxx-xxxxxx’

If you get below error, the account you gave, already exist in the DACPAC file, you need to remove the user from the database, and do a new extract of data to dacpac.

Error SQL72014: Framework Microsoft SqlClient Data Provider: Msg 15063, Level 16, State 1, Line 1 The login already has an account under a different user name.
Error SQL72045: Script execution error.  The executed script:
CREATE USER xxxxxxx

You may need to remove logins for the user account used at the import, on the database that you see exist at the server level (on the master database).

To see server accounts, run below on master database:

SELECT A.name as userName, B.name as login, B.Type_desc, default_database_name, B.* 
FROM sys.sysusers A 
    FULL OUTER JOIN sys.sql_logins B 
       ON A.sid = B.sid 
WHERE islogin = 1 and A.sid is not null

More Information:

https://learn.microsoft.com/sv-se/azure/azure-sql/database/database-import?view=azuresql&tabs=azure-powershell 

SqlPackage /a:import /tcs:”Data Source=<serverName>.database.windows.net;Initial Catalog=<migratedDatabase>;User Id=<userId>;Password=<password>” /sf:AdventureWorks2008R2.bacpac /p:DatabaseEdition=Premium /p:DatabaseServiceObjective=P6

SqlPackage /a:Import /sf:testExport.bacpac /tdn:NewDacFX /tsn:apptestserver.database.windows.net /ua:True /tid:”apptest.onmicrosoft.com”

https://support.ptc.com/help/windchill/r13.0.0.0/en/index.html#page/Windchill_Help_Center/WCUpgradeGuide/WCUpgrade_ImportingAzureSQL.html 

https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-import?view=sql-server-ver16 

 

Product:

Microsoft Azure SQL database

Issue:

How create a BACPAC, a file that contain both data and table structure (metadata) from the database? That you can import to other database later.

If you are going to import the BACPAC into SQL Azure later, the user account doing the import, can not exist inside the database. Please remove that user from inside the database.

Solution:

From inside SSMS (SQL Server Management Studio) you right click on the database and select task – export data-tier application.

You can enter a filename and save the file to your computer. (downloading database from Azure can take some time)

It is faster to save the file to AZURE BLOB storage (if they are at the same location), then you select “save to Microsoft Azure”.
Select a Storage Account, from your subscription.
Enter a name for the file, or keep the default.
Leave the temporary file as is.
Click next…

Or to save the file to disk, keep the “save to local disk” settings.

Click next and finish to start download the file to your local disk.

A DAC is a logical database management entity that defines all of the SQL Server objects which associates with a user’s database. A BACPAC includes the database schema as well as the data stored in the database.

More Information:

https://learn.microsoft.com/en-us/azure/azure-sql/database/database-export?view=azuresql 

https://www.sqlshack.com/azure-automation-export-azure-sql-database-to-blob-storage-in-a-bacpac-file/ 

SQLPackage utility to export Azure SQL Databases

Product:

Microsoft SQL Azure database

Issue:

Can not delete a user in the database from SSMS.

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

Solution:

Go to inside SSMS, check what schema is with this command (replace donald with the login you want to check):

select * from information_schema.schemata
where schema_owner = 'donald'

Change the schema stage to use DBO instead with command:

 ALTER AUTHORIZATION ON SCHEMA::[Stage] TO [dbo]

 

After the schema is changed to have a different owner, then the user can be dropped in SSMS.

More Information:

https://www.mssqltips.com/sqlservertip/2620/steps-to-drop-an-orphan-sql-server-user-when-it-owns-a-schema-or-role/

To list all user in database enter:

 SELECT DB_NAME(DB_ID()) as DatabaseName, * FROM sys.sysusers

Product:
Microsoft Azure Data Factory
Issue:
How schedule the ADF main pipeline to run every hour?

Solution:

Inside ADF  ( https://adf.azure.com/en/authoring/pipeline/Main ) under Factory Resources select you pipeline named “main”.
Click on the Trigger(1) icon to get the dropdown that show “New/Edit”, select that. (The number show how many triggers you got running)

Click the arrow at choose trigger to get the New icon to select.

Enter the name for your trigger.
Edit the start date to sometime next day – easy to only write the numbers in this field. It will start at the time specified, so set 1:00:00 AM to make it run at every whole hour. If you set it to 1:30:00 will make it run at 1:30 and then 2:30 etc.
Select a time zone that is the one you are in – otherwise it will run at for you unexpected time.

Set the Recurrence to 1 hour, if you want it to run each hour.
Note: that it is marked to Start.
And click OK.

You need to click OK one more time.

You need to click on the Publish All button in ADF, to make the change be uploaded and activated.

To check your trigger the next day, go to Monitor icon and Trigger runs. Here will be a list of the last 24 hours trigger runs and if they where successful.

To edit a existing Trigger – go to the pipeline and select again the Trigger drop-down and New/Edit link.

Click on your trigger name to get it up in Edit dialog. Here you can change the name and time it will run – select a start date and time in the future.

Ensure it is started, before you press OK.
Then you have to Publish your changes again to ADF, to make it happen.

As Azure change the layout all the time, the dialogs may look different when you read this.

More Information:

https://www.mssqltips.com/sqlservertip/6062/create-schedule-trigger-in-azure-data-factory-adf/ 

https://www.mssqltips.com/sqlservertutorial/9402/azure-data-factory-scheduling-and-monitoring/ 

https://www.sqlshack.com/how-to-schedule-azure-data-factory-pipeline-executions-using-triggers/ 

https://www.serverlessnotes.com/docs/schedule-azure-data-factory-pipeline-executions 

https://learn.microsoft.com/en-us/azure/data-factory/how-to-create-schedule-trigger?tabs=data-factory 

Product:
Azure Data Factory (ADF)

Issue:
Can not connect to SQL server from ADF in same subscription.

Cannot connect to SQL Database: ‘databaseservername.database.windows.net‘, Database: ‘databasename‘, Reason: Connection was denied since Deny Public Network Access is set to Yes. To connect to this server,
1. If you persist public network access disabled, please use Managed Virtual Network IR and create private endpoint. https://docs.microsoft.com/en-us/azure/data-factory/managed-virtual-network-private-endpoint; https://docs.microsoft.com/en-us/azure/data-factory/tutorial-copy-data-portal-private;
2. Otherwise you can enable public network access, set “Public network access” option to “Selected networks” on Auzre SQL Networking setting.

Solution:

Prompt yourself to Owner in the subscription and ADF.
Inside ADF you need first ensure that Integration Run-times are using a Managed Virtual Network. Create a new Integration runtime setup,
select Azure. self-hosted.
select Azure
set region to your needs, and click create.

When this use Managed Virtual Network, you can go and create the Linked Service.
Select Azure SQL database.
In connect drop-down select the above created integrationruntime2 that have managed virtual network.
Select your Azure Subscription.
Select your database server name from the drop-down.
Select the database name.
Select the Authentication type to be “System Assigned Managed Identity”
Click Test, and if there is OK, click Create.

Then you may inside SSMS add the ADF managed user to the database with command similar to this:

In master database:

CREATE login [adf_user] FROM EXTERNAL PROVIDER

CREATE USER [adf_user] FROM LOGIN [adf_user] WITH DEFAULT_SCHEMA=[dbo]

In user database:

CREATE USER [adf_user] FROM LOGIN [adf_user]

ALTER ROLE [db_owner] ADD MEMBER [adf_user]

 

 

More Information:

https://learn.microsoft.com/en-us/azure/data-factory/managed-virtual-network-private-endpoint

https://lazyadmin.nl/office-365/how-to-use-azure-managed-identity/ 

https://www.inthecloud247.com/configure-a-user-assigned-managed-identity-the-basics/ 

Product:

Microsoft SQL 2019 server standard
Microsoft Windows 2022 server datacenter

Issue:

How take a backup that does not affect the night differentiation backups etc ?

Solution:

Take a full backup and select copy-only backup in general.

In this example, a copy-only backup of the test database is backed up to disk at the default backup location.

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
  2. Expand Databases, right-click test, point to Tasks, and then select Back Up….
  3. On the General page in the Source section, check the Copy-only backup checkbox.
  4. Select OK.

 

 

More Information:

https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server?view=sql-server-ver16

https://www.mssqltips.com/sqlservertip/1772/copy-only-backup-for-sql-server/ 

https://www.ninjaone.com/blog/copy-only-backup-in-sql-servers-explained/ 

Product:

Microsoft SQL server Azure

Issue:

How to sum the amount in one column?

Solution:

Inside SSMS enter this, to sum column [amount] all rows that have active=”y’ and date = 20221201.

SELECT SUM(amount)
FROM [DM].[table1]
where [Active] = 'Y'
and [sDate] = '20221201'

 

To list all rows from two identical tables, including duplicates enter:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2

 

List number of rows from a table, from a selection:

select count(*)
FROM [DM].[table1] WHERE [Active] = 'Y'

 

How copy a number of rows into a new table:

SELECT *
INTO [DM].[table1]
FROM [DM].[table2]
WHERE [active] = 'N';

 

More information:

https://www.w3schools.com/sql/sql_sum.asp

https://www.w3schools.com/sql/sql_union.asp

https://www.w3schools.com/sql/sql_update.asp

Product:

Microsoft SQL Azure

Issue:

Want a new column on a table, that is needed only for the SP.

Want two column to be a new one, so we do not need to join on the two columns later.

Suggested Solution:

In a separate query , that you run before your SP,  check if the column exist and if not create it with SQL like this:

IF COL_LENGTH('[Stage].[tablename]','cDate') IS NOT NULL
PRINT 'Column Exists';
ELSE
BEGIN;
ALTER TABLE [Stage].[tablename] ADD cDate AS ( Year + '-' + Month + '-01') PERSISTED;
END;

Or even this will work:

IF COL_LENGTH('[Stage].[tablename]','cDate') IS NULL
BEGIN;
ALTER TABLE [Stage].[tablename] ADD cDate AS ( Year + '-' + Month + '-01') PERSISTED;
END;

You can find out better SQL to this problem.

Should give a new column with data:  2023-09-01, if the Year contain 2023 and Month contain 09.

Then you can compare a date formatted column with your cDate column.

PERSISTED will store the value of the calculation in the table, so it does not need to be calculated when asked for later.

More Information:

https://learn.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-ver16 

https://www.sqlshack.com/an-overview-of-computed-columns-in-sql-server/ 

SQL Server Computed Columns

https://database.guide/add-a-computed-column-to-an-existing-table-in-sql-server/ 

https://www.sqlservercentral.com/articles/using-computed-columns 

https://www.tsql.info/ex/sql-check-if-column-exists-in-a-table.php 

https://www.geeksforgeeks.org/how-to-check-if-a-column-exists-in-a-sql-server-table/ 

https://blog.sqlauthority.com/2017/07/29/sql-server-check-column-exists-sql-server-table/ 

https://www.sqlshack.com/sql-if-statement-introduction-and-overview/