SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Tonie from SQLServerCentral

This article explores the 'shadow copy' and 'disk virtualization' services built into the Windows operating system and explains a basic solution demonstrating how the technology is used to copy the data and log files for a live SQL Server database into an 'image', from which we can create multiple, lightweight copies, or clones, of the original database.

How do you monitor your databases? Take the survey!

Additional Articles from Redgate

Calling data professionals! We want to know how you are monitoring your servers, instances and databases. By taking the 2021 State of Database Monitoring survey, you’ll help us better understand how the community monitor and manage their estates, and the challenges they face. You’ll get exclusive early access to the 2021 State of Database Monitoring report, plus, one lucky winner will receive a $500 Amazon gift card in our prize draw.

Building a SQL Server data dictionary

Additional Articles from SimpleTalk

In this article, Edward Pollack explains the benefits of a SQL Server data dictionary and how to build one.

From the SQL Server Central Blogs - T-SQL Copy & Paste Pattern – Increasing a performance problem

Cláudio Silva from Cláudio Silva

Disclaimer: The title is my assumption because I saw it in the past happening this way. This blog post aims to make you remember something: something that is obvious...

From the SQL Server Central Blogs - Modernizing Your T-SQL: String Aggregation

alevyinroc from FLX SQL

This is another in a group of several posts on modernizing T-SQL code with new features and functionality available in SQL Server. SQL Server 2016 gave us the STRING_SPLIT()...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	The Full OFFSET
I am getting a limited list of numbers with this code on SQL Server 2014: WITH myTally(n) AS (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null)) FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n) CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n) ) SELECT * FROM myTally ORDER BY n OFFSET 7 ROWS FETCH NEXT 6 ROWS This code gives me a syntax error near "ROWS". What is wrong?
Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day (by BTylerWhite)

Python - Pandas and HTML

I'm looking to render results from a Pandas DataFrame in Python to convert the output to an HTML format.

I have the following code so far (working with a Snowflake database) shortened for brevity:

from snowflake import connector
import pandas as pd
import os

cnx = connector.connect(
    account=os.getenv('SNOWFLAKE_ACCOUNT'),
    user=os.getenv('SNOWFLAKE_USER'),
    password=os.getenv('SNOWFLAKE_PASSWORD'),
    warehouse='COMPUTE_WH',
    database='DEMO_DB',
    schema='PUBLIC'
)

cur = cnx.cursor()
cur.execute("SELECT LASTNAME, FIRSTNAME FROM REALLYCOOLTABLE;")
rows = pd.DataFrame(cur.fetchall(), columns=['Last Name', 'First Name'])

Which of the following lines would work to render the query results to HTML?

Answer: rows.to_html()

Explanation: We can use the "to_html" method within the pandas library to render a DataFrame as an HTML table. In this example, we're calling this method on "rows" as that is the variable storing the results from our query in a DataFrame. The output of this would look something like this:

	Last Name	First Name
0	Doe	John
1	Doe	Jane

I'm aware the code used in the sample may not follow best practices, just used as an example. References:

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 2016 - Administration

SQL Server 2016 SP1 to SP2 - We are currently on SQL server SP1. Should we go ahead and apply SP2? What are your recommendations? Do you think it is critical that if we do not apply this updates are we are not having any issues with SP1? Thank you,

SQL Server 2016 - Development and T-SQL

SQL2016 to Access2016 Linked server issue - Hi all We've got an issue with a linked server to an Access database. The general gist is we need the Access database for a file submission that we have to make. The linked server itself is fine but, when I try and write data to it via code, I get the […]

Datawarehouse question - So I have one dimension table and a fact table. dimension table has surrogatekey, fiscalyear and businessplan attributes. (Fiscalyear=2021) fact table has invoice level facts and businessplan surrogate key in there, which works fine. Now, business has additional requirements. They want to come up new business plan for new fiscal year (2022). This new year […]

Getting sum of all transactions by month-end - I have a table that is a list of inventory transactions. For simplicity sake, the table has these columns: Location, Item Number, Trx Date, Trx Amount I'm trying to create a query that gets the accumulation of all transactions for each month in a year. For example, let's assume these transactions: I'm trying to create […]

Administration - SQL Server 2014

Enabling/Configuring Read only routing - I have 2 servers which are part of AG. DB1(Primary) and DB2(Secondary). It has been setup like this for quite some time now but we don't have read routing enabled/configured. I went thru GUI and scripted out what I want to do. Couple of question related to my inquiry. Do I have to failover/reboot when […]

SQL 2012 - General

Implementing TLS In sql server 2012 - Hi Experts, In my project we have 2 servers Webserver and one is database server . We are planning to Implement the TLS in the database server and our DBA can handle the the step to implement TLS on the database server. My query is what all changes we have to do on the Webserver […]

Can we use SSIS dynamically import or export from one table to another table? - Normally, we can use selected databasename--tasks--import data/export data to handle data import and data export rapidly, but this operation is required to be done manully, Can we use SSIS dynamically import or export from one table to another table? such as creating a job in backend to call it as the schedule or call the […]

SQL Server 2019 - Administration

SQL server 2019 reporting need help - Hello sql server reporting 2019, i created temp for 180 days and not sure what to do next, I need to do the following: create a folder for reports for reporting services server. 2.create the report. We want to generate a reporting services web page where you display the contents of the table and create […]

Changing Data Drive Allocation Unit Size on Existing Installation - I have an existing SQL Server 2019 Standard installation that includes SSRS. The setup is fairly typical and the databases are relatively small at this point. There is a C drive and two additional local drives; one for data files and one for backups. The data drive was formatted for 4K and I'd like to […]

Unable to uninstall Sql Server 2019 in my Laptop - Hello, I have installed Sql Server 2019 more than 6 months before and forgot the credentials. So I tried uninstalling manually but it is not allowing me to uninstall. Even the services are not starting in Services.msc and Sql Configuration Manager. Not sure what is the issue. Attached the screenshots of the same. I tried […]

Grant Execute on ALL procs in a Database - I see a lot of old, 10+ year old posts for SQL 2000, 2005 etc on this. For our SQL 2019 environment, what is the easiest most efficient way to: GRANT EXECUTE on ALL StoredProcedures in DB_XYZ to MyDomainUser (eg. MyDomain\User_1) thx for any feedback!

SQL Server 2019 - Development

Cant cast VARCHAR(MAX) To XML Variable - Hi there I would like to cast a NVARCHAR(MAX) variable string to an XML variable as follows: declare @DataSheetXML NVARCHAR(MAX) = 'supports_alarmsTruealarmsTinylikesupports_min_maxFalselocally_latched_alarmsFalsesupports_startFalsesupports_suspend_when_fullFalsertcinternalhas_radioTrueunicode_descriptionsTrueminimum_interval120host_battery_alert_functionbattery_alertmemory_size1146880pt1000_reference_resistor1831.1688311688313device_descriptionTinytag Ultra2 Radio Receiveris_basestationTruememory_size32768config_hz1024delay_hz1max_delay315360000outputs_signed_encodingTruehas_32_bit_addressesTruehas_bc_start_new_sessionTrueserviceserviceambientserviceinstrumentationinstrumentationtesttesttesttesttesttesttesttesttesttesttest01thrhinstrumentationambienttestserviceloaded_vrefswitched120false0vrefswitched120false0bat_chk120twelve_bit_rawfalse0temperature120false0RadioStats_00160false0Radio' declare @xml xml Set @xml = cast(@DataSheetXML as XML) However I am getting the following error: Msg 9400, Level 16, State 1, Line 6 XML parsing: line 1, […]

Powershell

Pipeline Variables - Hi All I am trying to understand why my filtering doesn't work here.... I am new to Powershell Using dbatools This works: $t = get-dbaregserver $t | Where-Object {$_.group -eq "GroupName"} This does not filter the result: $p = get-dbaregserver $p | Where-Object {$p.group -eq "GroupName"}

The Future of Community

Future of SQL Server - I am becoming a little disconcerted by the lack of news about the next version of SQL Server as well as the continual bombardment of all things Azure from Microsoft. Microsoft is pushing Azure hard. The subscription model via the Cloud is the future. For Adobe customers it is already the present. Now, logically, I […]

Administration

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.