T-SQL – SQLMint

T-SQL: How to export image field data to file(s)?

See below code:

CLR – Check if enabled, enable it.. etc – SQL Server

“The common language runtime (CLR) integration feature is off by default, and must be enabled in order to use objects that are implemented using CLR integration. To enable CLR integration, use the clr enabled option of the sp_configure stored procedure in SQL Server Management Studio:” …. Microsoft

Essential commands:

–check
SELECT * FROM sys.configurations
WHERE name = ‘clr enabled’

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
–enable it
sp_configure ‘clr enabled’, 1;
GO
RECONFIGURE;
GO

–disable it
sp_configure ‘clr enabled’, 0;
GO
RECONFIGURE;
GO

SELECT * FROM sys.configurations
WHERE name = ‘clr enabled’

Unable to start execution of step 1 (reason: JobOwner domain\domainuser

On a server running SQL 2014 SSIS with SSISDB configured, one of the SQL Agent jobs configured to run using a proxy account kept failing with the error below.

Message

Unable to start execution of step 1 (reason: JobOwner domain\domainuser

doesn‘t have permissions to use proxy 1 for subsystem SSIS). The step failed.

Solution that worked for me in my situation.

Query your proxys and see if that proxy is disabled. If so, enable it.

SELECT * FROM msdb.dbo.sysproxies

USE msdb ;

EXEC dbo.sp_update_proxy

@proxy_name = ‘MyProxyUSerUser’,

@enabled = 1;

Configuring Firewall Access For Database Copy Wizard

Right clicking on a SQL server database using SQL Server Management Studio, click tasks, copy database and follow the wizard.

Below are major screenshots for opening up the primary firewall ports, using the inbound rules node of the windows firewall with advanced security node, in order to achieving this.

Transferring logins and passwords between separate SQL server Instances

Transferring logins and passwords between separate SQL Server instances eg SVR1\DEV to SVR1\DEV2 can be done by scripting out the encrypted passwords using the stored procedures sp_hexadecimal and sp_help_revlogin which can be found by following this detailed link by clicking here.

Creating A Contained Database – SQL 2012/2014

“A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. SQL Server 2014 helps user to isolate their database from the instance in 4 ways.

Much of the metadata that describes a database is maintained in the database. (In addition to, or instead of, maintaining metadata in the master database.)
All metadata are defined using the same collation.
User authentication can be performed by the database, reducing the databases dependency on the logins of the instance of SQL Server.
The SQL Server environment (DMV’s, XEvents, etc.) reports and can act upon containment information.”

MSDN Source reference – click here

T-SQL Code

EXEC sys.sp_configure N’contained database authentication’, N’1′

RECONFIGURE WITH OVERRIDE

USE [master]

ALTER DATABASE [MyDemoDB] SET CONTAINMENT = PARTIAL WITH NO_WAIT

The database owner SID recorded

Issue:

A restored database from one instance to the other keeps showing below when attempting to say, alter a stored procedure.

The database owner SID recorded in the master database differs from the database owner SID recorded in database

Solution applied: Assuming XYZ is the name of the database and contosoadmin is the name of the sa or sysadmin domain account usually assigned as owner in the destination SQL instance. contoso here represents the domain name.

Run:

ALTER DATABASE XYZ SET TRUSTWORTHY ON

ALTER AUTHORIZATION ON Database::archive TO [contosoadmin]

Microsoft SQL Server Merge Replication Client Status Information

Below is a select statement which can be placed in a view, for providing information on core merge replication status information usually seen using Microsoft SQL Server Replication Monitor GUI tool.

SELECT TOP 1000 [subscriber_server]
,[db_name]
,SubStatus =
CASE [status]
WHEN 0 THEN ‘Inactive’
WHEN 1 THEN ‘Active’
WHEN 2 THEN ‘Deleted’
ELSE ‘N/A’
END
,convert(nvarchar(100), [last_sync_date], 100) as LastSyncedOn
,LastSyncStatus =
CASE [last_sync_status]
WHEN 0 THEN ‘All jobs are waiting to start’
WHEN 1 THEN ‘One or more jobs are starting’
WHEN 2 THEN ‘All jobs have executed successfully’
WHEN 3 THEN ‘At least one job is executing’
WHEN 4 THEN ‘All jobs are scheduled and idle’
WHEN 5 THEN ‘At least one job is attempting to execute after a previous failure’
WHEN 6 THEN ‘At least one job has failed to execute successfully’
ELSE ‘N/A’
END
,[last_sync_summary]
,ReplicationVersion =
CASE [replica_version]
WHEN 90 THEN ‘SQL Server 2005’
WHEN 100 THEN ‘SQL Server 2008’
ELSE ‘N/A’
END

FROM [MyDatabase].[dbo].[sysmergesubscriptions]
where subscriber_type=2
order by [last_sync_date] asc

Below might be needed as well for non-sysadmins

grant select on [sysmergesubscriptions] to public

More details on the system table used can be found by clicking here.

Corporations Data Extract Download into Microsoft SQL Server

In this post l will be detailing the steps l used to ETL – Extract Transform and Load into a Microsoft SQL Server 2012 database from Washington State Corporations Data download. This showcases SQL Server inbuilt SSIS [SQL Server Integration Services] Data import and export wizard. We will be working with the text (tab limited) and not the XML version first.

First download the text form data file from this link, click here.

Unzip the file as shown below.

Create a database bucket and re-size to accomodate a large data import and reasonable file growth by value instead of percentage which is less efficient.

create database CorporationsDataWA;

USE [master]
GO
ALTER DATABASE [CorporationsDataWA]
MODIFY FILE ( NAME = N’CorporationsDataWA’, SIZE = 512000KB , FILEGROWTH = 102400KB )
GO
ALTER DATABASE [CorporationsDataWA]
MODIFY FILE ( NAME = N’CorporationsDataWA_log’, SIZE = 102400KB , FILEGROWTH = 102400KB )
GO

Below show step by step screenshots of the ETL process. I also chose to save the SQL server generated workflow process for this ETL as a separate SSIS package for later review, and execution.

Notice the ETL error message generated. The default column width of 50 characters for the business name and address fields was not enough to handle the input data without truncation, l tried 1100 and finally settled for 200 characters for each of these name, address fields. And that worked. With a clear data dictionary one might have known beforehand the maximum characters for those data fields in the text limited input data file.

Arrows indicate important entry fields or steps. Click on images for a larger version.