T-SQL – split a string value based on comma or another delimiter

Possibly save 1 hour of your time: This shows how you can take a column that has data such as a string value with comma separated value or semi-colon separated value and process each value for other purpose. In my case I want to check for foreign key not exist.

</pre>
DECLARE @MyCursor CURSOR;
SET @MyCursor = CURSOR FOR
SELECT [column_with_delimiter_separated_values]
FROM [dbo].[YourTable]

DECLARE @textString varchar(500);

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @textString

DECLARE @tempSplitText TABLE
(
SplitString nvarchar(500)
)

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @delimiter char = ';'
DECLARE @pos INT = 0
DECLARE @len INT = 0
DECLARE @value varchar(500)

WHILE CHARINDEX(@delimiter, @textString, @pos+1)>0
BEGIN
set @len = CHARINDEX(@delimiter, @textString, @pos+1) - @pos
set @value = SUBSTRING(@textString, @pos, @len)
INSERT INTO @tempSplitText(SplitString) VALUES(RTRIM(LTRIM(@Value)))
set @pos = CHARINDEX(@delimiter, @textString, @pos+@len) +1
END

IF (LEN(@region) > @len) --deals with last element
BEGIN
set @value = SUBSTRING(@textString, @pos, LEN(@textString)-@pos+1)
INSERT INTO @tempSplitText(TextString) VALUES(RTRIM(LTRIM(@Value)))
END

FETCH NEXT FROM @MyCursor
INTO @textString
END;

CLOSE @MyCursor;
DEALLOCATE @MyCursor;

select r. from @tempSplitText r
left join [dbo].[YourTableWithTheForeignKey] c on r.SplitString = c.ForeignKey
where c.ForeignKey is null
<pre>

 

Advertisements

SSIS – no column information was returned by the sql command

Possibly save 1 hour of your time: In SSIS, I have a data flow task that selects the source with some logic using table variable. It causes this compilation error when you try to save.

SSIS doesn’t know like using table variable in the SQL command.

Solution:

I have some complex logic so I need to use variable table. Temp table would most likely have same issue. So I just use a stored procedure instead. In my SQL command I have exec MyStoredProcedureName.

Using T-SQL cursor to loop through table records

Possibly save 1 hour of your time: There may be times you need to loop through each record in a table and process the data in the specific row or column.

DECLARE @MyCursor CURSOR;
SET @MyCursor = CURSOR FOR
  SELECT column1, column2
  FROM table
  WHERE clause

DECLARE @column1 int;
DECLARE @column2 varchar(500);

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @column1 , @column2

WHILE @@FETCH_STATUS = 0
BEGIN
 print Cast(@column1 as varchar(10)) + '-' + @column2
 FETCH NEXT FROM @MyCursor
INTO @column1 , @column2
END

CLOSE @MyCursor;
DEALLOCATE @MyCursor;

SSIS – How to setup parameter for Flat File Connection Manager connection string

Possibly save 1 hour of your time: This shows how you can add an expression to use as parameter for the Flat File Connection Manager or any other ones.

Right click on your connection manager and choose Properties. The properties window will show up. Find the Expressions and then click on the ellipsis on the right.

In the Property Expressions Editor that opens, you can now choose different Property such as ConnectionString. In my case, I set it to a user variable that tells me where the error log file is.

expression parameter

SSIS – Add logging to SQL Server database table sysssislog

Possibly save 1 hour of your time: This shows how you can add logging for your SSIS package to SQL Server database table sysssislog. The table is automatically created and records will be added automatically.

Open your package in Visual Studio or SSDT. On the top menu, select SSIS >> Logging. Check the checkbox at the top so that you want logging at the package level. Then on the right pane, select provider type of SSIS log provider for SQL Server. Then click Add to add the provider. Check the checkbox for the provider and select your database configuration.

configure SSIS Logs part 1

On the Details tab, you can check the checkbox for logging OnError and OnTaskFailed. This will ensure that you will get log in your table when there is a package error or when a task failed.

configure SSIS Logs part 2

After you write to log to your table, you can also setup Send Mail Task on these log data. On the tab Event Handlers in Visual Studio or SSDT, you can set up OnTaskFailed with Data Flow task to get data from this database table sysssislog. Then you can generate the log flat file somewhere. Then use Send Mail Task to email this log.

 

SQL Server database – changes from SIMPLE to FULL recovery mode after dacpac deployment

Possibly save 1 hour of your time: Our SQL Server ran out of disk space and my operations team found out that one of the database is recording huge transaction logs because the database is in FULL recovery mode in our system test environment.

What causes it? We ran the following command to pinpoint which process changes the recovery mode and at what time. Based on the date and time, we figure out that this happens at similar time as our deployment.

Sp_readerrorlog 0, 1, 'recovery'

Then you can run one of the following to determine the user and time when the recovery mode was changed. id below should be replaced with actual id from above query.

sp_who id

or

sp_who2

The cause of this was from a setting I was not aware of when creating the dacpac deployment file. To change this setting, right click on your database project and choose Properties. Under Project Settings, click on Database Settings. Then Operational. Change Recovery to SIMPLE or FULL as desired.

SIMPLE recovery mode

SSIS error – The environment reference id is not associated with the project.

Possibly save 1 hour of your time: This error happens after I deployed a script to create my SQL Server Agent job with SSIS packages as steps.

This error basically means your package is not referencing the proper environment. The configuration on your package needs to be associated with the right environment id.

Solution:

  • Right click on your SQL Server Agent job, and choose Steps. The step in our case represents each SSIS package.
  • Click on a specific step and click edit.
  • Then click on Configuration.
  • At the bottom, check the box Environment and then select the environment from the dropdown you have created for the project (You can verify the environment is created under Integration Services Catalogs >> SSISDB >> Project Name >> Environments). Click OK.
  • That should fix most people’s issues when they manually create the job.
  • In our case, we automate using script to create the job. So we can right click on the job and Script Jobs As >> Create To >> New Query Editor Window.
  • By looking at the script, you can find the @command variable for each step. On the value, you will see ENVREFERENCE and then a number. That number is the id you need to use in your script to tie the job to the environment reference properly.

ENVREFERENCE.png

Rename failed for Database Microsoft SqlServer. The database could not be exclusively locked to perform the operation

Possibly save 1 hour of your time: You want to rename a database that is shared with other developer and you receive this error.

Solution:

</pre>
DECLARE @DbName nvarchar(50)
SET @DbName = N'replace_your_database_name'

DECLARE @EXECSQL varchar(max)
SET @EXECSQL = ''

SELECT @EXECSQL = @EXECSQL + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DbName) AND loginame not like '%replace_your_login_name%'

EXEC(@EXECSQL)
<pre>

The above will remove other people’s connections. Once you close your query window, you can try to rename the database now.

However, I still get message that you cannot kill your own process.  Then you need to run the above SELECT statement without checking for your loginname to determine what other sessions you have. Then you can manually run EXEC(‘Kill 78;’). The number here should be your process id.

T-SQL – Arithmetic overflow error converting numeric to data type numeric.

Possibly save 1 hour of your time: When you have a float data value operation in T-SQL and assign it to a variable with less precision, you may get this error.

The following will give you this error since float cannot be converted to decimal.

Set PercentValue (decimal(5,2)) = Value (float) / AnotherValue (float)

Solution:

Increase the decimal data type to allow more values and precision like decimal (10, 4).

 

 

 

 

 

 

SSIS – import csv with UTF-8 encoding to avoid weird characters

Possibly save 1 hour of your time: After you import a csv data file into your SQL Server database, you realize some data has funny characters.

That is because your csv file is probably encoded in ANSI. Make sure your original file is in UTF-8 encoding and ensure all characters appear as normal.

You can fix this in notepad as follows. I change the xA0 to a space and save as encode in UTF-8.

encode utf-8

Book: Professional Microsoft SQL Server 2012 Integration Services