DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER - IIS - Could not acquire connection - Are you getting insane with this error?

Hi!

Is Integration information server making you lose your mind because of this error?

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

There are many reasons for this.

First

You named your connection with a fancy name. Example IggyAzaleia.10-78-5_12_WTF.
Solution : Connections must have simple names. No points, no dashes. Example Connection01

Second

You are updating a DTSX package, and suddenly you receive this dammned error. The message says that it is impossible to logging.

In this case there some possibilities
        The user you your using is blocked. There were so many errors that the user you are using in the connection string was blocked.
        When you update the DTSX, the connection you have entered loses the password. Fill the connection string again and don't forget the password. It seems that everything is ok (but IIS IS LYING!)

And that is it.

Good luck!

PS: If you have any doubt, post your comment below.

INCREASE SIZE COLUMN SQL SERVER

ALTER TABLE MY_TABLE  ALTER COLUMN MY_COLUMN VARCHAR(250)

USING REGEX WITH SQLSERVER

STEP 1

sp_configure 'show advanced options', 1

GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE;

STEP 2

Create this function

USE [Nike]
GO
/****** Object:  UserDefinedFunction [dbo].[RegexCodigoProduto]    Script Date: 04/02/2015 15:17:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[RegexCodigoProduto] (@CodigoProduto VARCHAR(8000), @RegexPattern VARCHAR(4000)) RETURNS VARCHAR(20)
AS
BEGIN
Declare @subject varchar(8000) = @CodigoProduto
Declare @pattern varchar(4000) = @RegexPattern --Pattern
DECLARE @objRegexExp INT, @objMatch INT, @Result VARCHAR(8000)

--Creating COM object
EXEC sp_OACreate 'VBScript.RegExp', @objRegexExp OUT
--Assigning Properties to COM object
EXEC sp_OASetProperty @objRegexExp, 'Pattern', @pattern
EXEC sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
EXEC sp_OASetProperty @objRegexExp, 'MultiLine', 1
EXEC sp_OASetProperty @objRegexExp, 'Global', false
EXEC sp_OASetProperty @objRegexExp, 'CultureInvariant', true

--Executing the COM object
EXEC sp_OAMethod @objRegexExp, 'execute', @objMatch OUT, @subject
--Fetching the first matching value
EXEC sp_OAGetProperty @objmatch, 'item(0).Value' , @Result OUT
--Releasing COM object after use
EXEC sp_OADestroy @objMatch
EXEC sp_OADestroy @objRegexExp

RETURN @Result

END

HOW TO - Populate EXCEL using SQL Server 2008 .. SQL2008 -> EXCEL, GOT IT?

This is an example of a store procedure that reads a VIEW and stores the result into a EXCEL sheet.
This example uses EXCEL of OFFICE 2010.
This new drive that Microsoft produced is not good at all, but if you are using EXCEL 2010 or higher, you have no other option.
Microsoft.ACE.OLEDB.12.0

Something that really got on my nerves was the use of the single quotes.

If you want to use INSERT INTO OPENROWSET with parameters you'll have to be carefull with single quotes.

If you want to write this ', you have to write  this ''.

Be careful with the name of the columns of your table or view. If it begins with number, you will have a bad surprise.

EXAMPLE

SELECT 7RUBY FROM MYTABLE

Will produce a lot of 7s instead of the column value.

Don't use columns that begin with numbers.

Have in mind that you will be using linkedserver in background, which has, at least in my experience, not a very good performance.

Maybe is the drive, maybe is the configuration of the server, I don't know. But linked server in my experiment had a bad performance.

Oh, and don't forget to execute these commands



EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

But can't I use a programming language to do this, instead of do it directly on SQLServer?
Excellent question!
I tried, but it seems the driver has a very weird behavior.
It seems to be intrinsically asynchronous, no matter what you do.
I tried C#.
The only I could have a reliable solution using this drive of Microsoft was making the process on SQLServer. I know, it seems extreme, but that's what solved my problem.



USE [Nike]
GO
/****** Object:  StoredProcedure [dbo].[SP_POPULATE_EXCEL_SHEET]    Script Date: 04/01/2015 13:17:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_POPULATE_EXCEL_SHEET]
@FileName varchar(255),
@Date1 varchar(10),
@Date2 varchar(10),
@Date3 varchar(10),
@Date4 varchar(10),
@ShopCode varchar(8)
AS

DECLARE @SQL VARCHAR(MAX)

SET @SQL = 'INSERT INTO OPENROWSET' +
           '(''Microsoft.ACE.OLEDB.12.0'',' +
           '''Excel 12.0;Database=c:\reports\' + @FileName + ';''' + ',' +
           '''SELECT FIELD1,FIELD2 FROM  [SHEET1$]''' + ')' +
  'SELECT CITY, COUNTRY from VIEW_CITY_COUNTRY  WHERE ((ProcessDate >= ''' + @Date1 + ''' AND ProcessDate <= ''' + @Date2 + ''' ) OR (ProcessDate >= ''' + @Date3 + ''' AND ProcessDate <= ''' + @Date4 + ''' )) AND IndicatorXXX =''S'' AND ShopCode = ' + @ShopCode

EXEC (@SQL)

GO

NOLOCK - Como é que é mesmo?

Vez por outra tenho essa dúvida

Sybase
SELECT * FROM [TABELA] NOLOCK

SQLServer
SELECT * FROM [TABELA_NOME] WITH (NOLOCK)

MySQL
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM [TABELA_NOME];
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;

Oracle
No oracle quem lê não bloqueia quem escreve e vice versa. Este bloqueio é que obriga no SQLServer, MySQL e Sybase a utilizar o artifício do NOLOCK ou READ UNCOMMITTED.

Obs: Talvez já existam versões de Sybase, SQLServer e MySQL que não precisam mais de artifício para fazer o que o Oracle faz. Mas no momento desconheço tais versões. OK?

E HÁ!

Integration Services - Sincronização entre duas tabelas de diferentes bancos de dados.

Veja bem,

Se você está tarado para usar o replicatedo SQLServer para sincronizar duas tabelas de bancos diferentes, controle-se!

Ao invés disso, utilize o integration service, é bem mais elegante. Usar replicate para sincronizar tabelas de bases diferentes é o mesmo que assassinar uma mosca usando um asteróide.

Ao invés disso, veja este exemplo

There are situations where a SQL Server DBA needs to keep two different tables in sync. Usually, the DBA needs to keep a copy of a table in a in a data warehouse repository that is used as a solution for archiving and/or reporting.

SQL Server provides a robust method for keeping data synchronized across databases using Replication but there are situations when all we need is just to keep an online copy of a single table for archiving or reporting purposes and we would prefer to do not tinker into SQL Server replication.

This post is divided into two parts: Part I explains how to update a destination table with the information that is added into a source table while Part II explains how to replicate any change that happens in existing records in the source table into destination. Please, take into account this is just another way of doing this, there are many other possibilities but the one listed here is probably one of the fastest to implement and modify on-demand.

This procedure is based on the following scenario: A “table A” on “database A” is periodically replicated using SSIS into “table B” on “database B”. “Table A” is updated with new records and we need to copy those records into “table B” too. Our final implementation will look like this in SQL Server Business Intelligence Development Studio:

http://blogs.msdn.com/b/jorgepc/archive/2010/12/07/synchronize-two-tables-using-sql-server-integration-services-ssis-part-i-of-ii.aspx

That's folks!