SQL Server: Export data to CVS with headers using BCP
Categories: Database; Tagged with: BCP • CSV • ETL • SQL • SQL Server • T-SQL; @ September 25th, 2012 14:04Use this BCP script to generate CSV file:
bcp "SELECT ''ID'', ''Username'' UNION ALL SELECT CAST(u.ID as varchar(10)),u.USERNAME from DavidTest.dbo.DavidUser u" queryout "c:\output.csv" -c -t"," -r"\n" -SServerName -T
If you want to run in SSMS, you can use:
USE MASTER
GO
DECLARE @query varchar(4000)
SET @query = '"SELECT...."';
EXEC xp_cmdshell @bcpOut
GO
for more info:
http://msdn.microsoft.com/en-us/library/aa174646(v=sql.80).aspx
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
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;