Jim Salasek's SQL Server Blog

Terminate SSIS Jobs: A Step-by-Step Guide

There are many reasons for terminating a long running SSIS Package. Sometimes someone accidentally kicks-off a monthly job instead of a daily job, or you just clicked the wrong item, either way SQL Server performance is affected. Whatever the reasons, you need to stop the job.

The cleanest way I have found to stop a SSIS package is by first running the following query to identify the processes that you want to stop.

You can either use End_Time is null or Status = 2 in the where clause.

select execution_id,folder_name,project_name,status,start_time,end_time from catalog.executions where end_time is null

This shows all of the SSIS jobs that SQL Serve thinks is still running. Some of these above are from 3-4 years ago, so I know they are not active.

Status of 2, indicates that SSIS still thinks that the process is running.

The other possible status results are (from the docs):
“created (1), running (2), canceled (3), failed (4), pending (5), ended unexpectedly (6),
succeeded (7), stopping (8), and completed (9).”

Now using the execution_id from the query above, we now need to use the catalog.stop_operation stored procedure to cancel the job:

exec catalog.stop_operation @operation_id=648469

After re-running the query, we can see that the job is no longer showing up as a running task.

Below I am stopping all of the jobs.

Rerunning the query now shows that no jobs are running.

For unknown reasons, sometimes the SSISDB database thinks old jobs are still running even though you do not see anything running. Now you have an easy way to stop/delete them.

For further reading, feel free to read these selections about: catalog.stop_operation and catalog.executions.

https://learn.microsoft.com/en-us/sql/integration-services/system-views/catalog-executions-ssisdb-database?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/integration-services/system-stored-procedures/catalog-stop-operation-ssisdb-database?view=sql-server-ver16

Copy SQL Server tables between servers easily with dbatools.

Often you need to copy tables between Sql Server database servers. In your environment, you cannot use or setup linked servers. How do you accomplish this without restoring the entire database on the destination server? Read along to see an easy way.

A scenario you will often find as a DBA, is to copy over table data to a different environment. The users do not want the entire database, but just the one table.

Using dbatools, this is very simple. To read more about setting up dbatools, please refer to my earlier blog posts.

DBATOOLS.IO….A DBA timesaver/luxury

Here is my current TEST database. It does not have any tables at this time.

After having dbatools installed, this simple command will copy a table from one database server to another.

Copy-DbaDbTableData -sqlinstance SOURCEINSTANCE -database warehouse -table dbo.aging -destination DESTINATIONINSTANCE -destinationdatabase Test -autocreate

The parameters you will need to change are easy enough:

SOURCEINSTANCE, TABLE, DESTINATIONINSTANCE, and DESTINATIONDATABASE. If you add -Autocreate, it will create the table automatically for you.

(why is this screenshot so small? You will need to trust me)

This is it running and showing progress.

This is the results of the command finishing. It shows (really it does), the table name, servers, number of rows transferred and time it took to complete.

If we look in the test database, you can see that the table has been copied over.

There are always multiple ways to accomplish tasks in Sql Server. Using dbatools is a huge time saver.

Master Filtering in SSMS: Quick Database Object Access

Are you tired of scrolling through all the tables, stored procedures and views to find the correct one? Sometimes you need to modify a stored procedure, and it takes a couple of minutes just to find it in the tree. Is there a way I can filter objects in SSMS? Yes! Read on to see how to do it.

SQL Server Management Studio (SSMS) will let us filter objects like tables, stored procedures and views. It really helps to get to objects quickly in case you
have a lot of objects in your database.

Here is how you can filter the objects. I am going to demonstrate on the stored procedures, but this works for tables and views also.

Go to the appropriate database and drill down to stored procedures and then Right click. Choose Filter->Filter Settings.

In this example I do not want to see any procedures that are in the ORACLE schema. I change the Operator to Does Not Contain and put the schema I do not want to show up.

After pressing OK, the browser it does not show the Oracle schema anymore. You can also see that the Stored Procedures are filtered by the word Filtered showing up next to them.

To remove the filter, just right click Stored Procedures select Filter -> Remove Filter.

Now all stored procedures show up in the listing again. You can see that the Filtered keyword is not showing up next to the Stored Procedures listing.

Now we are going to filter on certain tables. Right click on Tables and choose Filter Settings. This time we will put a value for Name “contains” and we will put in ACCR so we only see tables related to accruals.

Now we only see tables that contain the letters ACCR. Also notice that the Tables option shows they are Filtered.

If you are a programmer and only responsible for certain tables or procedures, using filters can really clean up your work environment.

Find SQL Server Tables with Identity Columns Easily

How would you find all the tables in a SQL Server Database that have identity columns? Read on to find an easy query to identify them (see what I did there 🙂 )

Here is a listing of some tables in my database. I need to find all of the tables that have an identity column set up.

The slow way is to open up each table in design view and look at each column.

After opening up the table you would then need to check each field to see if identify specification is set to YES.

This way is slow.

The easiest way to check your database is to run the query below. This will show every table and the column name that is set to Yes.

SELECT DB_Name() AS DatabaseName ,OBJECT_NAME(c.OBJECT_ID) AS TableName ,c.NAME AS ColumnName FROM sys.columns c INNER JOIN sys.tables t ON c.object_id = t.object_id WHERE is_identity = 1

Let us look at the Dim_City table in design view. After opening it up, we can see that CityId has Identity Specification set to Yes. This matches what the query turned up.

Do not be afraid of the database system views. They can be very helpful in documenting your database and are much quicker than clicking around in the GUI.

How to find all jobs that call a stored procedure.

I need to find all SQL Agent jobs that call a certain stored procedure. You could open every Agent job and inspect each step or you could do it the smart way and use a query! You can also use this method to find other items (tables, views, functions, etc.)

I need to find any job that is using a stored procedure. Using this query below, I just need to modify the WHERE clause for what I am searching for. Also make sure you change the database to MSDB.

use msdb GO SELECT Job.name AS JobName, Job.enabled AS ActiveStatus, JobStep.step_name AS JobStepName, JobStep.command AS JobCommand FROM sysjobs Job INNER JOIN sysjobsteps JobStep ON Job.job_id = JobStep.job_id WHERE JobStep.command LIKE '%spBHTamper%'

The results show two different Jobs that have spBHTamper in it.

I open up the Tamper Export job, and I see that indeed the spBHTTamperExport is being called. Success!

To read more about the MSDB database, please visit this link: https://learn.microsoft.com/en-us/sql/relational-databases/databases/msdb-database?view=sql-server-ver16

Error Msg 2760, Level 16 When trying to create a table in a schema

I was trying to create a table in a schema, but received the following error message: Msg 2760, Level 16, State 1, Line 1

Read on to find out how I solved this problem.

Why create a schema? A schema in SQL Server is a way to organize database objects such as tables, views, and stored procedures into logical groups. By creating separate schemas for different categories of information, we can easily manage and access the data we need, improve security, and simplify database maintenance.

Those are all good points, so I want to create a new table in a schema called HR. I get the following error that the HR schema does not exist. It is correct, I need to create the HR schema first before creating objects in it.

To create the schema, all you have to do is use the CREATE SCHEMA command.

Here is how I created the HR schema.

CREATE SCHEMA [HR]

After the schema is created, the table can now be created in the HR schema.

If I look in SSMS, I can see that the new table is created within the HR schema that just got created.

Below I am trying to drop the HR schema. It errors off with a Msg 3729, Level 16, State 1 error. It is telling us that there is an object that exists and it will not let you drop it until the objects are removed from the schema.

First drop the table.

Now I can successfully drop the schema.

To read more about schemas, I encourage you to follow this link to the documentation. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-schema-transact-sql?view=sql-server-ver16

Find Columns In Your Database

There are many reasons why you would want to find a specific column or data type in your database. Read on to find some ways to accomplish this.

Here is one way. It is slow and involves opening every table in SSMS.

Then you need to expand the table and then the columns to display each field.

One quick way is to use the SP_COLUMNS procedure. The easiest way to run it is to provide the table name that you wish to look for.

EXEC sp_columns @table_name = 'tbl_products'

The quickest way in my opinion is to use the INFORMATION_SCHEMA.COLUMNS view. In the query below, I am searching for the table name: tbl_products. This lists all of the columns and their data type.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'tbl_products'

Also with this view, you can search for only columns of a certain data type. In the example below, I want to see every table and column that has a data type of TEXT. With this query it is very simple.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE='text'

To read more about these Microsoft provided views/stored procedures, please follow the links below.

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-columns-transact-sql?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/system-information-schema-views-transact-sql?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/columns-transact-sql?view=sql-server-ver16

Create a Graph in a SSRS Report

We have created plenty of SSRS reports, but can you add a graph to one to create some visuals? You sure can! Read on.

Vertical Text Selection in SSMS

Often you have a list of values that you need to put into a query, but you only need certain columns of the data. I used to have to edit the data to get it in the format I needed, but now I can easily copy out the values I need using a single ALT key.

SQL Server Agent: WARNING: The following job steps cannot be reached with the current job step flow logic:

I recently got this error when trying to add a step in an agent job. What caused it and how did I fix it?

M	T	W	T	F	S	S
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31