BCP table export and Shared Locks

Hi,
We use BCP commands like this:
BCP ACC.Dbo.[TableName] out CSV\ TableName.csv /c /k /t "|" /r
"\n" -Sservername -Uname -Ppass
to extract data from SQL Server into CSV files.
I want BCP don't put any lock, including shared lock, on table records. How
can I supply locking hint (NOLOCK) along with the table name?
We try not to use the actual query and just put the table name on the
command line.
Thank you,
AlanTry append -hnolock to the command.
Lucas
"Maxwell2006" wrote:
> Hi,
>
> We use BCP commands like this:
>
> BCP ACC.Dbo.[TableName] out CSV\ TableName.csv /c /k /t "|" /r
> "\n" -Sservername -Uname -Ppass
>
> to extract data from SQL Server into CSV files.
>
> I want BCP don't put any lock, including shared lock, on table records. How
> can I supply locking hint (NOLOCK) along with the table name?
>
> We try not to use the actual query and just put the table name on the
> command line.
>
> Thank you,
> Alan
>
>
>|||Hi Max,
Thank you for posting.
As for the SQL server bcp utility, currently there is only a "TABLOCK" hint
option whch can help switch the lock (when performing bulk
importing/exporting) between row level lock and table level lock.
Therefore, if we need to completely disable any lock when performing the
bulk exporting, we still have to use explicit T-SQL script to do it.
#bcp Utility
http://msdn2.microsoft.com/en-us/library/ms162802.aspx
BTW, if you do not want to pass the T-SQL directly in command prompt, do
you think it possible that we use a batch/script file to programmatically
load such T-SQL script and launch the bcp utility command?
Anyway, please feel free to let me know if you have any other consideration.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Thank you Steven.
I changed our export scripts to use the query "select * from tableName
(NOLOCK)".
Max
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:t8tbYX3jGHA.4528@.TK2MSFTNGXA01.phx.gbl...
> Hi Max,
> Thank you for posting.
> As for the SQL server bcp utility, currently there is only a "TABLOCK"
> hint
> option whch can help switch the lock (when performing bulk
> importing/exporting) between row level lock and table level lock.
> Therefore, if we need to completely disable any lock when performing the
> bulk exporting, we still have to use explicit T-SQL script to do it.
> #bcp Utility
> http://msdn2.microsoft.com/en-us/library/ms162802.aspx
> BTW, if you do not want to pass the T-SQL directly in command prompt, do
> you think it possible that we use a batch/script file to programmatically
> load such T-SQL script and launch the bcp utility command?
> Anyway, please feel free to let me know if you have any other
> consideration.
> Regards,
> Steven Cheng
> Microsoft MSDN Online Support Lead
>
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Thanks for your followup Max,
Glad that you've got a solution to work on it. If there is anything else we
can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

bcp copying,but no file

DECLARE @.query NVARCHAR(2000)
SELECT @.query = 'bcp "SELECT * from dbname..tablename" queryout c:\test.txt -c -Slocalhost -Usa -Ppassword'
EXEC master.dbo.xp_cmdshell @.query

I have tried doing this with a test table and this is the output
NULL
Starting copy...
NULL
3 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 16
NULL

and I did have 3 row,but file is still not created, I have put the server name,login,pasword inside "" and without it,no error,no file.

Could somebody please help meWhere are you looking for the file?

bcp command

if i use:
--code--
DECLARE @.CMD VARCHAR(8000)
SET @.CMD = 'bcp "select * from TABLENAME" queryout C:\testfile.xls -c -S"' +
@.@.servername + '" -T'
exec master..xp_cmdshell @.CMD
--end code--
how could i put the column headers in this spreadsheet?
or
is there a better way of getting a sql statement to save directly to excel?Have a look here:
76c9997" target="_blank">http://groups.google.de/group/micro...
76c9997
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"UNOTech" <UNOTech@.discussions.microsoft.com> schrieb im Newsbeitrag
news:CDA8B4C3-3CB1-418A-A649-B928CB8FB275@.microsoft.com...
> if i use:
> --code--
> DECLARE @.CMD VARCHAR(8000)
> SET @.CMD = 'bcp "select * from TABLENAME" queryout C:\testfile.xls -c -S"'
> +
> @.@.servername + '" -T'
> exec master..xp_cmdshell @.CMD
> --end code--
> how could i put the column headers in this spreadsheet?
> or
> is there a better way of getting a sql statement to save directly to
> excel?|||I'd advise to do it directly from Excel using the menu choices in Excel. go
to Data...Get Extranal Data...New Database Query... It is very clean. You
will need to set up an ODBC connection to do this.
"UNOTech" wrote:
> if i use:
> --code--
> DECLARE @.CMD VARCHAR(8000)
> SET @.CMD = 'bcp "select * from TABLENAME" queryout C:\testfile.xls -c -S"'
+
> @.@.servername + '" -T'
> exec master..xp_cmdshell @.CMD
> --end code--
> how could i put the column headers in this spreadsheet?
> or
> is there a better way of getting a sql statement to save directly to excel?[/color
]