SQL Server: Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
Categories: Database; Tagged with: SQL • SQL Server • T-SQL; @ September 13th, 2012 18:41I got some Error when I trying to select data from Excel file using Microsoft.Jet.OLEDB.4.0:
The OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.
Solution:Make sure you can access the file from the DB server.
SQL Server: Read,Write data from Excel/CSV/Text file using T-SQL
Categories: Database; Tagged with: SQL • SQL Server • T-SQL; @ September 13th, 2012 18:28
-- Select Data from Excel file
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\DavidTemp\channel.xls', [Sheet1$])
-- Select data from CVS file:
SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0',
'Text;Database=C:\DavidTemp\;','SELECT * FROM [channel.csv]')
-- Select data From text file:
SELECT * FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0',
'TEXT;Database=C:\DavidTemp\','SELECT * from channel_noData.txt')
REF:
Ad Hoc Querying
SSIS: Export data to CSV with column names 导出数据到CSV并包含标题
Categories: Database; Tagged with: ETL • SQL • SQL Server • SSIS; @ September 7th, 2012 15:38When exporting data to “csv” using SSIS, I found there was no column name in the output file. And the solution is quite simple and inelegant,
refer to the following screen capture:
Using function in SQL Server 在SQL Server中使用函数
Categories: Database; Tagged with: SQL • SQL Server • T-SQL; @ September 7th, 2012 15:06Create SQL function:
USE DavidTest;
GO
IF OBJECT_ID ('dbo.fn_isUserActive') IS NOT NULL
DROP FUNCTION dbo.fn_isUserActive;
GO
CREATE FUNCTION dbo.fn_isUserActive (@userID int)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @userStatus int;
DECLARE @isActive int;
SELECT @userStatus = ID FROM DavidTest.dbo.DavidUser WHERE ID=@userID ;
if(@userStatus is not null AND @userStatus = 3)
BEGIN
SET @isActive = 1;
END
ELSE
SET @isActive = 0;
RETURN @isActive;
END;
GO
Using the function:
SELECT dbo.fn_isUserActive(3);
SELECT * FROM dbo.DavidUser u where dbo.fn_isUserActive(u.ID) > 0;
SSIS: Can not execute dataflow in Visual Studio
Categories: Database; Tagged with: SQL • SQL Server • SSIS; @ September 6th, 2012 19:24This happened when we just edit the dtsx file.
If you want execute the flow in VS directly, create one Integration services project first, then you can copy your dtsx and pase it into the “SSIS Packages” in the Solution Explorer:
then you can execute the ssis dataflow directly in VS.