SQLServerCentral.com Community Update for December 23, 2022

Problems displaying this newsletter? View online.

Featured Contents

Calling Procs from PowerShell with Parameters

Frank Dolan from SQLServerCentral

Building on his last article, Frank shows how to add parameters to your stored procedure calls from PoSh.

Flyway Desktop in Database Development Work: An Overview

Additional Articles from Redgate

For those new to Flyway Desktop, this article takes a strategic overview of the components of a Flyway Desktop project, how to set up a project for team-based development work, and how we can use the tool in conjunction with a version control system and CI servers to manage a database development and release process.

Heapq Module in Python: Using Heaps and Priority Queues

Additional Articles from MSSQLTips.com

In this tutorial, learn about Python priority queues and heaps, how do they relate, and how to use the heapq module in Python.

From the SQL Server Central Blogs - T-SQL Tuesday #157–The End of Year

Steve Jones - SSC Editor from The Voice of the DBA

It’s that time of the month, and I’m late. I’ve been on holiday for a week, so this is a quick post for T-SQL Tuesday. This month is hosted...

From the SQL Server Central Blogs - Advent of Code 2022

Zikato from StraightforwardSQL

Foreword
Advent of Code is an annual event in which participants solve a series of coding puzzles. It typically begins on December 1 and runs through the end of the...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	The 12 days of Christmas
I've created a couple of tables that handle the gifts from the Twelve Days of Christmas song. Here's the code: CREATE TABLE TwelveDays (gift VARCHAR(100)) GO INSERT dbo.TwelveDays (gift) VALUES ( 'A partridge in a pear tree '), ( 'Two turtle doves '), ( 'Three French hens '), ( 'Four calling birds '), ( 'Five gold rings '), ( 'Six geese a-laying '), ( 'Seven swans a-swimming '), ( 'Eight maids a-milking '), ( 'Nine ladies dancing '), ( 'Ten lords a-leaping '), ( 'Eleven pipers piping '), ( 'Twelve drummers drumming ') GO CREATE TABLE Numbers (n INT, word VARCHAR(20)) GO INSERT dbo.Numbers (n, word) VALUES (1, 'A '), (2, 'Two'), (3, 'Three'), (4, 'Four'), (5, 'Five'), (6, 'Six'), (7, 'Seven'), (8, 'Eight'), (9, 'Nine'), (10, 'Ten'), (11, 'Eleven'), (12, 'Twelve') GO I want to be sure the rows return in order. I have this query sketched: SELECT gift FROM dbo.TwelveDays AS td INNER JOIN dbo.Numbers AS n ORDER BY n which ON clause should I use?
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)

The SQL Server Upgrade Targets

Using the SQL Server Migration Assistant, when coming from Access, DB2, Oracle, MySQL, or SAP ASE, which versions of SQL Server can I target? I am ignoring Azure Synapse as a separate offering.

Answer: SQL Server 2012 through SQL Server 2019, as well as Azure SQL Database and MI

Explanation: The SSMA actually lets you target 2012 through 2019 with the Azure offerings. Ref: SQL Server Migration Assistant - https://learn.microsoft.com/en-us/sql/ssma/sql-server-migration-assistant?view=sql-server-ver16

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

A question about unique keys - I've been away from SQL Server for a number of years. Years ago what I am asking could not be done. I assume that it still can't be done, but rather than make an assumption I thought that I should ask. Below is a completely fictitious example, but it illustrates my question. Supposing that I […]

SQL Server 2016 - Administration

SPN/KERBEROS issue - Hi everyone. I would like to kerberize SQL connections in the following situation and have not been able to: I have a single server with two SQL instances and only one IP (the server's). I can't configure more IPs due to infrastructure limitation: SERVER1.domain.com --> 10.10.10.1 I start each instance on a different port using […]

SQL Server 2016 - Development and T-SQL

For Each Loop Query (SSIS) - In above screenshot can anyone tell me how can I enter value of "Files" field as expression?

calculate 12 months of data from a given date from a user - I have a query that it seems to be off dont know what wrong but I need to show last 12 months of data based off a user input for a date. my query is this. My results show different in SSRS. /*Declare @Date as Date = '11/1/2022' Declare @prop as int = 8000 --*/SELECT […]

Administration - SQL Server 2014

Error on File - Hi Experts, I have a database with multiple files on multiple drives and one of the drive is restricted to a defined size. Now because the index maintenance job is failing with below error. How can I rectify this without increase the disk space. Could not allocate space for object 'dbo.Table1'.'PK_Table1_ID' in database 'DB1' […]

how to check the table is being used(selecting/inserting/updating/deleting/locki - how to check the table is being used(selecting/inserting/updating/deleting/locking) by some sql statement? many thanks!

SQL 2012 - General

SQL Server 2012 Auditing Question - I am currently trying to set up SQL Server audit specifications, but they are not showing with the following query: select distinct(eventid) from sys.fn_trace_geteventinfo(1) When I run the query it only shows defaults. However prior to installing KB2958429 (SP 2), the audit specifications manually selected showed up with the query. So, I installed KB3045321 (a […]

Azure Data Factory

Upskill Azure Data Factory knowledge - Hey guys, I'm a DE with of 2 experience and I've been working with Azure Data Factory as the main ETL tool for 6 months now. However, I feel I need to gather more knowledge and assume more responsibilities inside my team but most of the senior professionals don't have much time to teach me. […]

SQL Server 2022 - Development

Using SQL - Convert column to rows - not a straight forward pivot - Hello All, I have below sample table. Code is also included. create table Trade_test (portfoliocode nvarchar(10), tradedate varchar(10), tradeamount varchar(10) ); insert into Trade_test values('A','01012021','100') insert into Trade_test values('A','01012022','1000') insert into Trade_test values('B','01012018','100') insert into Trade_test values('C','01012019','100') I need to pivot the data to have below output. I have tried few things but not getting […]

What's the logic of the NOT IN clause? - I thought I understood the "NOT IN" logic very well until I run into a case where the presence of NULL values affects the result in a way I was not expecting. The setup is super simple: CREATE TABLE A1(ID1 INT); CREATE TABLE A2(ID2 INT); INSERT INTO A1(ID1) VALUES (1), (2), (NULL); INSERT INTO A2(ID2) […]

Struggle Bus - Varbinary(max) Performance Suggestions? - I have a very large table I am dealing with that has a varbinary(max) column. I have a Order By that is extremely slow on this field and also I have a Convert as well to a NVARCHAR(MAX) but even without the convert it is slow. How can I somehow increase the performance on this […]

I have to do a price increase on our parts. My question is history records. - For each part there must be a history record of price from to price to. Is there a better way to do this than a cursor? Use M2MDATA01 declare @part char(30) declare @rev char(3) Declare @price numeric(15,5) declare @newprice numeric(15,5) Declare @now datetime = current_timestamp declare @new varchar(max) declare @old varchar(max) ALTER TABLE [dbo].[inmastx] Disable […]

height_cm -> 167.60 centimeter need to convet height_FT column and Height_IN - height_cm -> 167.60 i have data in sql server column that data i need to insert HeightFT column feet and remain Inches should go into Height_In column.

This email has been sent to newsletter@newslettercollector.com. 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.