Wednesday, December 8, 2010

Connecting excel from SQL server 2008

--No space in excel filename and sheet name

exec ('sp_configure ''show advanced options'', 1')
exec ('reconfigure')

exec ('sp_configure ''Ad Hoc Distributed Queries'', 1')
exec ('reconfigure')

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

SELECT *
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\file_name.xlsx;Extended Properties=Excel 8.0')...[Report1$]

Thursday, November 25, 2010

Inserting a file into filestream column using OPENROWSET SQL function

INSERT INTO [tblFile]([file] ,[RowGuid])

SELECT CONVERT(varbinary(max) , Document.bulkcolumn), NEWID()
FROM OPENROWSET(BULK N'\\FilePath\File' ,SINGLE_BLOB) AS Document