SQL server BCP in a nutshell

BCP is a sql server utility used for importing and exporting huge quantity of data from a sql server table

You can use BCP without installing sql server, just install Microsoft SQL Server 2008 Command Line Utilities at:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=b33d2c78-1059-4ce2-b80d-2343c099bcb4

You can Export data from command line:

bcp mydbname.dbo.largetable  out c:\yourtable.csv /U sa /P mypassw /S servername /c

And Import data:

bcp mydbname.dbo.largetable  in c:\yourtable.csv /U sa /P mypassw /S servername /c

For a table with 60 fields the rate import is 10000 records/sec… but this is only a test value depending on the hardware.

You can also export queries using bcp (from msdn):

bcp "SELECT FirstName, LastName FROM AdventureWorks2008R2.Person.Person ORDER BY LastName, Firstname" queryout Contacts.txt -c –T

Sql server: cannot resolve collation conflict for equal to operation.

Sometimes you may encounter a “collation problem” when you compare two colums from different table or database.

As MSDN says “Collations let users sort and compare strings according to their own conventions”, but they can be a really pain for developers.

The simplest way for comparing two columns with different collation without hard writing specific collation codes is to convert the two columns to the database default collation.

If this sql script gives you the error “cannot resolve collation conflict for equal to operation”

select * from people,city 
where people.citycode = city.citycode

You can transform the select adding collation instructions:

select * from people,city 
where people.citycode COLLATE DATABASE_DEFAULT  = city.citycode COLLATE DATABASE_DEFAULT 

Hope it helps!

 

Hot to recover unused space from SQL server free edition (MSDE,Express)

If you have a SQL server free edition and it isn’t working with the message:

CREATE/ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 2048 MB per database.

Unfortunately the ”dbcc cleantable “ does not help,  it cleans the table unused space BUT the shrink operation does  not decrease the database size

You have another option:  reorganize the indexes and shrink the db: it works giving you some hours of new life, just the time to install SQL2008 express R2 with 8GB limit

Follow this steps:

1. Open Sql Server Management Studio
2. Run sp_helpdb ‘YourDb’ and save the results
3. Analyze the tables with the bigger size, use this script:
   http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/121/determing-sql-server-table-size.aspx
4. Change in the script the final query :
• from:
  SELECT *
  FROM #TempTable
• To :
  SELECT *
  FROM #TempTable
  order by cast(replace(IndexSize,' KB','') as int) desc
5. Run the query and identify the worst tables
6. Open the Tables, Index and click on “Reorganize all”
7. Shrink the DB
8. Run sp_helpdb ‘YourDb’ and compare the results with the old one
9. Install the new SQL server and try to migrate the databases, but this is another story…..
   

Hope it helps!

Free Sql profiler x Sql Express/MSDE

E’ disponibile sul sito  http://code.google.com/p/sqlexpressprofiler/ un profiler sql gratuito che permette di sostituire il sql profiler non incluso in MSDE e SQL Express.

Non richiede setup ed è stato sviluppato in .net.

Enjoy!