Moving on from my previous post, Creating Simple SQL Server test server with Terraform in Azure, I thought it may be fun to change the SQL Server version of an existing SQL Server. This assumes you made a mistake and want to wipe out the old version. It will not perform an upgrade in-place of […]
Terraform is an open source “Infrastructure as Code” tool, created by HashiCorp. Running a Terraform script will ‘build’ you a SQL Server in a few minutes and destroy it just as fast, so it is perfect for the purpose of standing up a SQL Server to test a theory or some code and then drop this server. […]
I was asked to check a clients 2008 r2 SQL Server recently because there were high numbers of the above error in the SQL Server errorlog. I initially suspected a broken Database Mirrored setup but on checking the server, I could see no Database mirroring, no Availability Groups, no Log shipping setup. Checking the Server […]
Deploying the package to a SQL Server Deploy To deploy the newly created package to a SQL Server (SSISDB Catalog), rom within Visual Studio, we right-button clock on the root of the package and select DEPLOY. The wizard opens. in our example, we select a deploy target of ‘SSIS in SQL Server’. fill in the […]
Writing a basic SSIS package Install Visual Studio You need a development environment to create your SSIS packages. Microsoft provide the popular free IDE, Visual Studio. Once you install this you can add support for SQL Server Integrated Services and start creating your packages. Download Visual studio from https://visualstudio.microsoft.com/downloads/Select the free ‘Community’ edition.Once you LAUNCH […]
This article deals with the following Setting up SQL SSISDB catalogue database Writing a basic SSIS package Deploying the package to a SQL Server Setting up SQL SSISDB catalogue database First of all you need to install this feature. Mount the SQL Server installation media, choose install and select the ‘integration services’ from the options. […]
If you use the excellent tools from dbatools.ie to manage your SQL Server, you may worry about what happens if it all goes wround. Maybe the resatore files were corrupted wne copied from another server or not in the directory you specified. Well, I worry about stuff, so I tried to find a solution. This […]
SQL Agent jobs that run a PowerShell script need to run with a proxy account, so I can adhere to Windows permissions constraints. However, the SQL Agent job returned the following error message Message Unable to start execution of step 1 (reason: Error authenticating proxy DOMAIN\account_name, system error: The user name or password is incorrect.). […]
NO ROWS Function ‘rs_get_textptr’ for ‘sometable’ returned no rows, table example:- create table test_tab( cola int not null, colb_txt text null) This occurs with columns containing text columns. The problem is you forgot sp_setrepcol. The default for text/image is always replicate and so not null is assumed. So you need to run sp_setrepcol to set […]
Reasons not to grant db_owner DBO can do some nasty stuff:- CHANGE RECOVERY MODEL BACKUP DATABASE ALTER ANY SYMMETRIC KEY TURN ON/OFF AUTOSHRINK ADD FILE AUTO_UPDATE_STATISTICS SET RECURSIVE_TRIGGERS DROP DATABASE SET OFFLINE If you must create database objects, view showplans/dynamic tables or more but do not want to give away ‘the keys to the […]
