SQL Server: New Linked Server from Excel
Categories: Database; Tagged with: SQL • SQL Server • T-SQL; @ September 13th, 2012 19:50We can create a new linked server using Excel:
Select the Provider, Input the data source(excel path), and provider string, keep ‘Excel 8.0’.
Save, and you can see the server .
then you can query from the new server:
SELECT * FROM DAVIDEXCEL...Sheet1$;
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;