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.

Advertisements

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

SSIS – Script component is executed in wrong order or multiple times

Possibly save 1 hour of your time: This actually happens when I copy the package file.

The Script Component id remains the same. Hence it was executing a previous package code. It is documented here by Microsoft:

https://support.microsoft.com/en-us/help/928323/fix-a-script-task-or-a-script-component-is-executed-multiple-times-in

One resolution mentioned there was to delete the script component and paste it again. It didn’t work for me. The id was still the same as before. You can see the id when you open in edit mode and it is near the top of the window.

The resolution for me is to recreate the script component. I just have to redo it unfortunately.

Book: Professional Microsoft SQL Server 2012 Integration Services

Add nested dependent web.config transformation

Possibly save 1 hour of your time: When you want to do a continuous integration build, you may want to specify different web.config or app.config used for the transformation based on a specific environment configuration.

In my environment, we used web.config for local use. Then we have SYST for system integration environment (team check in), UAT for user acceptance testing environment and finally PROD for production environment.

So you add a web.SYST.config and it is not nested under the web.config. It should look like this.

web.config

Some people suggest right click on the Web.config and find Add Config Transform. That did not show up for me.

The alternative is to go into the csproj file directly and edit the include to have DependentUpon element.

content include.png

You also need to right click on the Solution and choose Configuration Manager. Under Active solution configuration, you can select New to create your own configuration. The name used here should also reflect the name in the config file.

configuration manager.png

Book:Murach’s ASP.NET 4.6 Web Programming with C 2015

SSIS – cannot create null column

Possibly save 1 hour of your time: When you have a data task flow that imports data from a flat file source and into a OLE DB destination database, you may encounter this issue where it fails to create the record because some columns have default values.

On your OLE DB destination, you must use Data Access mode of Table or view instead of Table or view (fast Load). Fast load does not provide ability to create records with default values.

Book – Professional Microsoft SQL Server 2012 Integration Services

SSIS cannot debug breakpoint in Script Component

No solution yet but provide some insights: When I am targeting SSIS 2012, I got this issue.

I have a Data Flow task that includes an OLE DB source and then link to Script Component. In the OLE DB source, I use a SQL command query which uses a WHERE clause. The WHERE clause seems to be the issue that causes the debugger not break into the next Script Component. After I remove it, it seems to debug in the next script component, but then this doesn’t help in my case as I need to filter some data source. I am not sure if this is a bug.

P.S> This issue only happens with Script Component, and not with Script Task.

Book – Professional Microsoft SQL Server 2012 Integration Services

SSIS – import csv with commas in data fields

Possibly save 1 hour of your time: When you have a line in csv file that contains data with comma, the SSIS package may not parse it correctly.

i.e. country name, iso code, numeric code

“Bonaire, Saint Eustatius and Saba”,BES,535

You can use double quote to signify as text qualifier in the flat file connection manager.

text qualifier

This should parse as the fields correctly.

Book – Professional Microsoft SQL Server 2012 Integration Services

npm install error – eperm operation not permitted unlink

Possibly save 1 hour of your time: I am not sure exactly what causes this issue.

My environment:

  • Windows 10
  • node v8.9.1
  • npm v5.6.0

 

Possible Solution:

1. Some has try the following:

  npm install –no-optional

2. Others have downgraded to npm 5.3.

  npm install npm@5.3 -g

3. Remove npm and npm cache

C:\Users\your.name\AppData\Roaming\npm

C:\Users\your.name\AppData\Roaming\npm-cache

But none of the above works for me.

4. The hard way is to rollback and start with a working state.

5. Finally, if none of above works, start a new app and slowly migrate over to see what changes cause the issue. This is a tedious and long process.