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

Basic SSRS Question

I am trying to get a count of all the reports that do not use shared
datasource. We are finding a bunch of reports that do not use shared
datasource we would like to get a list of all the reports that do not
use shared datsource.
Also we would like to get a list of all the reports that are set for
subscriptions. There are way too many reports that are on our server
it would be really hard to go in manually to find this information.
I have been quering the dbo.catalog table in the report server to
extract this information however I have had no luck. Are there any
dyanamic views that will givbe me that info? I am sure other have run
into this requirement as well and already figured it out.
Could one of you please help me or point me in the right direction?
ThanksOn Apr 30, 8:59=A0am, shub <shubt...@.gmail.com> wrote:
> I am trying to get a count of all the reports that do not use shared
> datasource. We are finding a bunch of reports that do not use shared
> datasource we would like to get a list of all the reports that do not
> use shared datsource.
> Also we would like to get a list of all the reports that are set for
> subscriptions. =A0There are way too many reports that are on our server
> it would be really hard to go in manually to find this information.
> I have been quering the dbo.catalog table in the report server to
> extract this information however I have had no luck. Are there any
> dyanamic views that will givbe me that info? I am sure other have run
> into this requirement as well and already figured it out.
> Could one of you please help me or point me in the right direction?
> Thanks
Not sure where to look for the datasource info as all of our reports
run from shared data sources i.e. all look the same in that regard.
For the subscription stuff, try this query against the reportserver
database on server you run SSRS on to get a list of all reports with
subscriptions.
SELECT DISTINCT c.name AS RptName, c.Description AS RptDescription,
c.path AS Path, sc.name AS SchedName,
su.Description AS SubsDescription
FROM dbo.Catalog c JOIN
dbo.ReportSchedule rs ON c.ItemID =3D rs.ReportID
JOIN
dbo.Schedule sc ON rs.ScheduleID =3D sc.ScheduleID
JOIN
dbo.Subscriptions su ON rs.SubscriptionID =3D
su.SubscriptionID|||On May 1, 3:30=A0pm, toolman <t...@.infocision.com> wrote:
> On Apr 30, 8:59=A0am, shub <shubt...@.gmail.com> wrote:
>
> > I am trying to get a count of all the reports that do not use shared
> > datasource. We are finding a bunch of reports that do not use shared
> > datasource we would like to get a list of all the reports that do not
> > use shared datsource.
> > Also we would like to get a list of all the reports that are set for
> > subscriptions. =A0There are way too many reports that are on our server
> > it would be really hard to go in manually to find this information.
> > I have been quering the dbo.catalog table in the report server to
> > extract this information however I have had no luck. Are there any
> > dyanamic views that will givbe me that info? I am sure other have run
> > into this requirement as well and already figured it out.
> > Could one of you please help me or point me in the right direction?
> > Thanks
> Not sure where to look for the datasource info as all of our reports
> run from shared data sources i.e. all look the same in that regard.
> For the subscription stuff, try this query against the reportserver
> database on server you run SSRS on to get a list of all reports with
> subscriptions.
> SELECT DISTINCT =A0c.name AS RptName, c.Description AS RptDescription,
> c.path AS Path, sc.name AS SchedName,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 su.Description AS SubsDescript=ion
> FROM =A0 =A0 =A0 =A0 dbo.Catalog c JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 dbo.ReportSchedule rs ON c.Ite=mID =3D rs.ReportID
> JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 dbo.Schedule sc ON rs.Schedule=ID =3D sc.ScheduleID
> JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 dbo.Subscriptions su ON rs.Sub=scriptionID =3D
> su.SubscriptionID- Hide quoted text -
> - Show quoted text -
Thank you so much. This query certainly helps a lot. I will keep
poking around for the other solution to find all reports that do not
use shared datasource. Thanks again

Basic Replication Question

I'm really new to this concept, so i would appreciate a little guidance.
I have a database that is stored on a remote server (shared hosting
environment). Of course the hosting company says they do daily backups, but
I don't want to trust that for myself and our clients.
Is there a way for me to set up something locally in enterprise manager that
replicates those databases to my local copy? I would like for it to sync
whenever there are changes, but it could be a batch process that runs hourly
or something.
thanks,
You could use transactional replication or log-shipping for this. Have a
look at this article for some pros and cons:
http://www.replicationanswers.com/Standby.asp
Probably the main issue will be making sure that there is connectivity and
that it is secure.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)