I recently wrote an article on owners vs schemas for SQLTeam thinking that I had this all figured out. So now I am studying for 70-431 and ran across an item on broken ownership chains. This is the problem when a view has a different owner than a table or view used in the view definition. How can this be since we have schemas now, not owners? The book I am using recommended looking at BOL for more information and, sure enough, BOL describes the familiar concept iike nothing thas changed. If the article mentioned that there could be a problem if the objects are in different schemas, I would be satisfied. Hmmm, a user can own a schema, but I don't see where it can own a table anymore. A search of "table owner" brings up an article on sp_column_privileges. This proc uses schema as if it was an owner. In AdventureWorks, there is a HumanResources schema. If I run this

sp_column_privileges @table_name='employee',@table_owner='HumanResources'

I get back the results. But, "HumanResources" is not a user so how can it be an owner? It's a schema that is owned by dbo.

Is the documentation just not consistant? Am I just confused?

There was a time that I avoided bcp utility like the plague. A few years ago, we needed to set up a once a month job to transfer about 100 GB of data from one server to another. Since this involved two SQL Server 2000 instances, the first attempt was using DTS, but the performance was just not good enough. We considered setting up a job to copy a backup file from one server to another, do a restore and then import the data. I decided to give bcp a try, exporting the data to files and then importing the data into the required tables. The performance was acceptable and the bcp solution has been in place without any issues since then.

I think that the reason I avoided using bcp for so long is that it has a dizzying number of options. Luckily, I found some bcp scripts written by a vendor using just a handful of arguments that got me started. Since then I have found several opportunities to use bcp and now consider it a very useful tool.

The bcp utility is a command line tool. You can also use it from the Query Window if xp_cmdshell is enabled. One thing to keep in mind is that when it is used from the command window or batch file, the file path is in the context of the box where the command is run. When running from the Query Window or a stored proc, the file path will be in the context of the server.

Below is an example script that I ran from my laptop at home in the Query Window. It is running SQLExpress. There are many, many more options available for bcp, but this minimal number of arguments has worked for just about everything I have wanted to do. There is also a handy “queryout” argument you can use instead of “out” to use a select statement instead of a table or view name.

You will have to modify the server name and file path for your environment. Make sure that the bcp command ends up all on one line.

/*
Export from the AdventureWorks database to a file
	out = export to a file
	-S = Server\Instance
	-T = trusted authentication
	-c = use character datatypes, tab delimited
	-t = override the delimiter
*/

exec master.dbo.xp_cmdshell 'bcp AdventureWorks.HumanResources.Employee out c:\temp\employee.txt -S localhost\SQLExpress -T -c -t "|"'



--Create a blank table
use AdventureWorks
go
if object_id('dbo.test_import') is not null
	drop table dbo.test_import

select * into dbo.test_import from HumanResources.Employee where 1 = 2

/*
Import from a file into a table. 
	in = import from a file
	-S = Server\Instance
	-T = trusted authentication
	-c = character datatypes, tab delimited
	-t = override the delimiter
*/

exec master.dbo.xp_cmdshell 'bcp AdventureWorks.dbo.test_import in c:\temp\employee.txt -S localhost\SQLExpress -T -c -t "|"'

select * from dbo.test_import