SQL Server: Export data to CVS with headers using BCP

Categories: Database; Tagged with: • • • • • ; @ September 25th, 2012 14:04

Use 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: • • ; @ September 13th, 2012 19:50

We can create a new linked server using Excel:

image

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: • • ; @ September 13th, 2012 18:41

I 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: • • ; @ 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: • • ; @ September 7th, 2012 15:06

Create 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;

Older Posts

ActionScript Adobe AIR ANT Apache ApacheFlex Apache Flex Better Developer BlazeDS Cloud Computing CMD DataGrid DataGridColumn DB2 Derby DTSX Eclipse ETL Event Excel Flash Builder Flex Google Java JDBC JDK JIRA Life@SG|新加坡 Linux MySQL Oracle PHP Python RegEx Servlet SQL SQL Server SSIS SVN T-SQL Tools Tree Ugly WordPress XML dev-notes side-projects

// Proudly powered by Apache, PHP, MySQL, WordPress, Bootstrap, etc,.