Wednesday, March 01, 2017

T-SQL: Merge

MERGE as target
USING (
SELECT ***
FROM tbl g
WHERE @***

) AS source (A_ID, B_ID)
ON ( target.A_IdMembro = @Id )
WHEN MATCHED THEN
UPDATE SET ID = source.ID
WHEN NOT MATCHED THEN
INSERT (colunas)
VALUES ( @Id
, source.ID
, ...
)
;

Wednesday, July 27, 2016

T-SQL transaction control in SP

A very basic usages of transactions in order to ensure that only one transaction is active at a time.

use master
go

declare @trancount INT = @@TRANCOUNT
, @i INT = NULL

begin try
print 'start'
print @trancount

IF @trancount = 0
begin
BEGIN TRANSACTION
print 'trans'
print @trancount
end

print 'working'
print @trancount

-- Uncoment this line to create exception
-- set @i = 1/0

IF @trancount = 0
BEGIN
COMMIT
print 'commit'
print @trancount
END

END TRY
BEGIN CATCH
print 'rolling'
print @trancount
print ERROR_MESSAGE()

IF @trancount = 0
BEGIN
ROLLBACK
print 'roll'
print @trancount
END
END CATCH

Friday, March 04, 2016

One of the best C# tips

I have found this when looking around in the net and I was so amazed I had to post it here.
By experience, most of these tips I already use but there are still a couple that I never seem to memorize. Is it because I don't use it every day or just laziness I really do not know.

http://www.codeproject.com/Articles/8210/Top-tips-and-tricks-to-jazz-up-your-C-business

Hope it helps you code better.

Thursday, February 18, 2016

T-SQL Server tips

-- Validar os contadores de performance
--select *
--from sys.dm_os_performance_counters
--where counter_name like '%Buffer cache hit ratio%'


---- Overview rápido sobre o estado dos processos na BD
--exec sp_who2


---- Se aparecer clean nos três primeiros está tudo bem
-- exec sp_server_diagnostics


--- Verificar se temos transacções pendentes
---- Por cada BD
-- dbcc opentran

---- Para todas as BDs
--exec sp_MSforeachdb 'use ? dbcc opentran'


Monday, October 26, 2015

Another configuration story

After struggling to get the SSIS working, talking to several people a blog entry came to rescue and saved the day:

https://msdn.microsoft.com/en-us/library/ms137789.aspx

During a SSIS instlalation I never had issues with configurations, I usaually install everything using an Administrator account. I had a local account and run the executable as Administrator, the SSIS was installed and the instance created. The problem was when using the local account to access Management Studio the SSIS instance was unavailable, using the installation account and running as administrator did the trick.
The SSIS instance was registered under the adminstrator priviledges and was unable to be accessed outsider the administrative group. The configuration was required to allow the instance to be accessed from Management Studio

Friday, October 23, 2015

Linq to SQL

Since I have been battling to figure how to use linq in the correct form and to speed up my coding, the following link is a life saver (and brain saver as well);

http://www.codeducky.org/sql-queries-in-linq/

Downgrade SSIS 2014 to work on 2012

The answer to all of my complanig was just around the corner :)

The featured blog does not only provides a solution as well a nice simple comand line utility to execute the replacements:

http://vaniecastro.com/2015/02/26/how-to-downgrade-sql-server-integration-services-2014-packages-to-2012/

Here is the list also referenced of replacements:

https://msdn.microsoft.com/en-us/library/dn592136(v=sql.105).aspx



Tuesday, June 30, 2015

Formating a 128 GB flash drive in Windows 8.1

This seems to be a pretty straight forward and basic action for someone who has experience working with windows operating system.

My problem was that I used my 128 GB flash drive as a boot drive and it was formatted in FAT32 with 32 GB capacity.

I started by searching the web for a rapid solution and I found that everyone was pointing out to "Disk Management". I accessed "Disk Management" but could not format beyond 32GB in FAT32 since the partition on the flash drive was made with 32 GB of space only.

Solution:

Remembered the good old CMD console and the Diskpart Comand tool as described in this article for Windows XP:

https://support.microsoft.com/en-us/kb/300415

Note: You are required to have Administrator privileges to format drives.

If the page is no longer available, here are the action steps:

1. Access CMD console by going to the startup menu and write "cmd"
2. On the console write "diskpart" - this will enter the disk partition tool
3. Enter "list disk" command and check wish drive corresponds to your flash drive
4. Use the "select N" where N is the drive number to change for the correct drive
5. Enter 'list partition' to display all partitions.

In my case there was only one partition for the drive and the rest was free space. I need to remove the partition of 32GB and create a new one with the full space capability.

6. Use the "select M" where M is the partition of 32 GB, to ensure we are only using the correct partition
7. Enter "delete partition M" to remove the currently selected partition
8. Use "create partition primary" without any number to create a partition with the full available space.

9. Exit the diskpart tool by writing "exit"
10. Exit the CMD line writing "exit"

11. Back in Windows, go to "My Computer", right click on it and choose "Manage"
12. Under "Disk Management" section you should have the flash drive with the partition you have just created with available size according to full capacity.
13. Right click on the partition and choose format, select FAT32 or NTFS (if you require files over 2GB of size)

Ok, that it, the format should bring the full available space for the partition, in my case 127 GB.

Hope this helps and if required comment and I will reply as soon as possible.