Bulk Insert option in sql server

Bulk insert is a very nice option in sql server to load huge data from the external source system.
It needs the data should be delimeted by specific delimeter.

The syntax is,

BULK INSERT TABLENAME
FROM SOURCE FILE
WITH (OPTIONS)

Now, I will create a temporary table to insert the records into the table.
create table testing
(
empname varchar(20),
value1 varchar(10)
)

Am having a text file with comma seperated delimeter and /n for the next line.



Now, am going to insert the data into the temporary table. Below is the bulk insert
syntax to insert the records into the table.

BULK INSERT testing
FROM 'C:\VP Personal\aa.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

On selecting the table, we will get the records inserted into the table.

select * from testing

Thanks and Regards,

Venkatesan Prabu .J

SQL Server errors

While trying to use bulk insert option, I got the below error,

Msg 4861, Level 16, State 1, Line 1

Cannot bulk load because the file "\\ServerName\input\FileName.csv" could not be opened. Operating system error code 5(Access is denied.).

On digging the microsoft URL's I found the problem is due to the settings in sql server. We need to enable TCP/IP with local and remote connections option in Surface area configuration manager.

I have crossed this and next I got another peculiar error,
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other

On looking into this issue, guessed the problem should be with the file. Some how, My sql doesn't have proper access to read that file. I took the file and placed it another location. Luckily, my problem is resolve and am able to do my bulk insert.

Any way, this is not a permanent and apt solution. For more proper solutions, please check the below URL.

http://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx

Thanks and Regards,
Venkatesan Prabu . J

Tempdb is full - Free up the space

Hi All,

Am writing my article after a very long time. I have been caught up with some other works.



Ok, lets see some interesting facts about tempdb.

Background of Tempdb Database:

TempDB is one of the system database which is used to store the temporary tables or temporary data.



A table can be declared as temp table by adding '#' symbol in the front of the table name.



Temp table will hold a session value appended, For each user a temp table can be created with a session id appended to its name - Unique to identify the tables. Internally, SQL Server will take care of this.


Actions for Tempdb full problem:


Considering, My scenario - Tempdb is full. In this case,



1. Check the feasibility to restart the server. If there is no major impact, restart the server. So that, the database will be freed up and it will more room to get other new tables.

Some times, we are familiar with the below error message.


Description: The LOG FILE FOR DATABASE 'tempdb' IS FULL.

Back up the TRANSACTION LOG FOR the DATABASE TO freeup SOME LOG SPACE

select * from sys.database_files

The above query will get the results as below,

Now, since the log file is full and there is no room to extend further. In that case, we

can advise the database to point the file to another location. Below is the query to achieve the same.

The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

Cheers,

Venkatesan Prabu .J