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

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!

Property IsLocked is not available - La proprietà IsLocked non è disponibile

Sometimes after rebooting the machine with sql server 2005 standars installed I cannot connect using the sa user and I receive an error when I try to manage the user:

"La proprietà IsLocked non è disponibile per Account di accesso 'sa'"

Or, in english:

Property IsLocked is not available for Login '[sa]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

The only solution is to unlock the sa user using the command:

alter login sa

with password = 'yourpwd' unlock,

check_policy = off,

check_expiration = off

After this all works ok.

For details look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1673143&SiteID=1

Hope it helps,

Matteo

HOWTO update or delete on SQL server a limited number of rows, like TOP condition

If you need to update a limited number of rows on a sql table using a "top"  like condition, here is a solution:

SET ROWCOUNT 100

-- update

SET ROWCOUNT 100

update table1 set column1 = getdate()

-- only 100 rows will be updated

-- delete

SET ROWCOUNT 10

delete from table1

-- only 10 rows will be deleted

Allineare due DB via batch file

Ecco uno script batch che utilizzo spesso per mantenere allineato il database locale con il database sul server di sviluppo.
E' necessario avere i permessi per accedere via file system al database remoto ed una user sql amministrativa.
Ecco i passaggi:

1. collego via net use al sql server
2. killo le connessioni attive sul db che deve essere copiato
3. eseguo lo shrink del db e lo metto offline
4. copio i file mdf ed ldf in locale
5. metto online il db
   

[+/-] Mostra i file

copydb.bat:
rem killo in processi in uso sul db che deve essere copiato, lo setto offline, copio i file e lo rimetto online
echo on
echo utilizzo:copydb.bat NOMESERVER NOMEDB SQLUSER SQLPASSWORD DOMINIO USER SQLPATH LOCALPATH
echo esempio:"copydb.bat" sqlserver1 pubs sa password domain01 myusername "\\sqlserver1\e$\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf" "\\sqlserver1\e$\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf" "c:\sqllocal"

set NOMESERVER=%1
set NOMEDB=%2
set SQLUSER=%3
set SQLPASSWORD=%4
set DOMINIO=%5
set USER=%6
set SQLMDFPATH=%7
set SQLLDFPATH=%8
set LOCALPATH=%9

net use \\%NOMESERVER% /user:%DOMINIO%\%USER%

isql -S %NOMESERVER% -U %SQLUSER% -P %SQLPASSWORD% -d master -i "db_kill_connections.sql"
isql -S %NOMESERVER% -U %SQLUSER% -P %SQLPASSWORD% -d master -Q "DBCC SHRINKDATABASE (%NOMEDB%,10)"
isql -S %NOMESERVER% -U %SQLUSER% -P %SQLPASSWORD% -d master -Q "alter database %NOMEDB% set offline"

copy %SQLMDFPATH% %LOCALPATH% /Y
copy %SQLLDFPATH% %LOCALPATH% /Y

isql -S %NOMESERVER% -U %SQLUSER% -P %SQLPASSWORD% -d master -Q "alter database %NOMEDB% set online"

pause


db_kill_connections.sql:

DECLARE @spid int
-- Declare a cursor For process records that concern with MyDatabase.
DECLARE sysprocesses_cursor SCROLL CURSOR FOR
SELECT spid
FROM master..sysprocesses
WHERE dbid = db_id('Pubs')

OPEN sysprocesses_cursor

FETCH NEXT FROM sysprocesses_cursor
INTO @spid
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN -- Clear up processes.
print 'kill @spid'
print @spid
EXEC ('KILL ' + @spid)
FETCH NEXT FROM sysprocesses_cursor INTO @spidEND
CLOSE sysprocesses_cursor
DEALLOCATE sysprocesses_cursor