–Configuring SQL Server Surface Area before using OPENROWSET
USE
[master]
GO
sp_configure
‘show advanced options’,1
GO
reconfigure
withoverride
GO
sp_configure
‘Ad Hoc Distributed Queries’,1
GO
reconfigure
withoverride
GO
Bulk insert In SQL Server
/*
Bulk insert And OPENROWEST
*/
http://msdn.microsoft.com/en-us/library/aa225968(v=sql.80).aspx
—————————*******************Bulk Insert******************—————————–
–Step 1 Create Table [CSVTest]
CREATE
TABLE [dbo].[CSVTest]
(
[ID] [int]
NULL,
[FirstName] [varchar]
(40)COLLATE Arabic_CI_AS NULL,
[LastName] [varchar]
(40)COLLATE Arabic_CI_AS NULL,
[nvarchar]
(50)COLLATE Arabic_CI_AS NULL
—Step 2 Create Text File on D:\ Location
—Step 3 Try Bulk Insert
go
BULK
INSERT
[CSVTest]
FROM
‘D:\TxtFile2.txt’
WITH
(
BATCHSIZE
= 2,—- Show The Rows Affected
FIRSTROW
= 2,—– Start the insert from Row number 2
FIELDTERMINATOR
=‘,’,
ROWTERMINATOR
=‘\n’,
LASTROW
= 4,—- End the insert when you Arraive to row number 4
MAXERRORS
= 1 — Number of errors After this Number The SQL not complete the insert the defult value is 10
)
GO
–Step No 4 Check the Data inserted
Select
*from [CSVTest]
Delete
from [CSVTest]
Drop
Table [CSVTest]
Using DBCC UPDATEUSAGE to correct inaccuracies(Copy from another blog)
The DBCC command can be run on a database to correct inaccuracies of all objects in that database or you can also correct the inaccuracies of a single table by including that table name. If you would like to run the command for a particular database, then you can run the following command
DBCC UPDATEUSAGE (‘DATABASENAME’)
You can also run the below command to correct the inaccuracies for the current database
DBCC UPDATEUSAGE (0)
Remarks : I take this Information Copy from this Blog : http://learnsqlwithbru.com/2012/02/02/using-dbcc-updateusage-to-correct-inaccuracies/
Delete file on your PC by SQL Server
you must be in the first Configure SQl server to allow you to make this operation so Run this script on your server (take it from this link)
[https://mostafaelmasry.wordpress.com/2012/01/20/ole-automation-procedures-to-delete-file-from-pc/]
then run this Script
DECLARE @Result int
declare @File_Location as nvarchar(300) = ‘F:\fff.txt’
DECLARE @FSO_Token int
EXEC @Result = sp_OACreate ‘Scripting.FileSystemObject’, @FSO_Token OUTPUT
EXEC @Result = sp_OAMethod @FSO_Token, ‘DeleteFile’, NULL, @File_Location
EXEC @Result = sp_OADestroy @FSO_Token
Ole Automation Procedures to delete file from PC
if you want to write Script to delete file from your pc by SQL Server must be Configure your SQL server to allow this option :
exec sp_configure ‘Ole Automation Procedures’, 1
go
reconfigure
go
DB Cloud TECH 