Fantastic Four Official Trailer #1 (2015) - Miles Teller, Michael B. Jor...

Star Wars The Force Awakens Teaser Trailer 2 Official

SQLSERVER 2008 AND SSIS AND MICROSOFT.ACE.OLEDB.12.0 AND WINDOWS 64 BITS DO NOT WORK TOGETHER!

SSIS AND MICROSOFT.ACE.OLEDB.12.0 AND WINDOWS 64 BITS DO NOT WORK!
AND THAT'S IT.
THE TRUTH IS THAT IT WORKS FINE ON WINDOWS 32 BITS BUT 64 BITS, NO WAY!

THE ONLY THING THAT WORKS WITH MICROSOFT.ACE.OLEDB.12.0, IN SQLSERVER2008 IS LINKEDSERVER.

THE REST, FORGET IT!

Microsoft SQL Server Error 18456 Login Failed for User

AFTER THE MANEUVER DON'T FORGET TO RESTART THE SERVER!!!!!!!!!!!!!!!!!!

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

Pra achar o norte de quem somos e sintonizar.

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