显示标签为“commands”的博文。显示所有博文
显示标签为“commands”的博文。显示所有博文

2012年3月25日星期日

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.)

2012年2月16日星期四

Batch SQL

QuestionsI need to batch a set of update commands. Can that be done and if so what are the possible ramfications? Can one mix / match Deletes/Inserts and Updates into a SQL command via the semi-colon in a batch set? (Is this the appropriate forum?)

WHYI am unable to use the

dynamic sql building found in the dataset due to a multiple table

contstraints. I have built classes that will extract the differences

from two datasets and report on the updates/deletes and inserts

required. Since I have multiple SQL statements, it would be nice to combine them into one statement.


Platform.Net 2C#IIS6SQL Server 2000 is the eventual target database for the generated SQL

advTHANKSance

Regarding Transact-SQL it is possible to combine multiple statement into one batch. Separate the statements with semi-colon.

If possible I would look into using stored procedures instead. It will help keeping more logic on the backend and allowing re-usal if you want to build a client for another platform.