BCP to txt file

I am trying the following but it only works on a local database I want to ru
n
this on a remote server and have the file sent to my PC.
EXEC master..xp_cmdshell
'bcp "SELECT * FROM TestDB_data..titles" queryout "c:\test_08-25-05.txt" -S
server1 -U sa -P sapwd -c'You have to create a shared folder in your pc and give write access to sql
agent services account. Then use that shared folder in the bcp command.
EXEC master..xp_cmdshell
'bcp "SELECT * FROM TestDB_data..titles" queryout
"\\your_pc_name\shared_folder\test_08-25-05.txt" -S
server1 -U sa -P sapwd -c'
AMB
"Lontae Jones" wrote:

> I am trying the following but it only works on a local database I want to
run
> this on a remote server and have the file sent to my PC.
> EXEC master..xp_cmdshell
> 'bcp "SELECT * FROM TestDB_data..titles" queryout "c:\test_08-25-05.txt" -
S
> server1 -U sa -P sapwd -c'
>|||It works only if your servers are linked.
Try using tabe name directly (instead of using query) it should work
bcp "TestDB_data..titles" OUT c:\test_08-25-05.txt ....
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:80C1C9DA-2C3F-4048-9B55-7F849FD6B662@.microsoft.com...
>I am trying the following but it only works on a local database I want to
>run
> this on a remote server and have the file sent to my PC.
> EXEC master..xp_cmdshell
> 'bcp "SELECT * FROM TestDB_data..titles" queryout
> "c:\test_08-25-05.txt" -S
> server1 -U sa -P sapwd -c'
>

bcp error handling

Hello
I am using bcp.exe to transfer data between remote servers and need a way to
do the error handling. What I need is simple - just to know whether the copy
was successful or not, because it needs to be in transaction with other
operations. There are situations when the return value of the exe is always
0 and nothing gets written into the error log ( for example copying from
file to table that violates a constraint ). All that came to me was to parse
the log file ( option -o ) for strings like 'error' and 'failed' but this
doesn't look like a great idea. And yes, I am using bcp in both directions,
so bulk insert will not do.
Any ideas of how to find whether the copy was successful?
(sql server 2000 sp3, in case it matters)
Thanks
Plamen
"Plamen Doykov" <p_doykov@.code.bg> schrieb im Newsbeitrag
news:uoZ1ear1EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hello
> I am using bcp.exe to transfer data between remote servers and need a
way to
> do the error handling. What I need is simple - just to know whether the
copy
> was successful or not, because it needs to be in transaction with other
> operations. There are situations when the return value of the exe is
always
> 0 and nothing gets written into the error log ( for example copying from
> file to table that violates a constraint ). All that came to me was to
parse
> the log file ( option -o ) for strings like 'error' and 'failed' but
this
> doesn't look like a great idea. And yes, I am using bcp in both
directions,
> so bulk insert will not do.
> Any ideas of how to find whether the copy was successful?
> (sql server 2000 sp3, in case it matters)
We settled with the output parsing also. That's what we use in Java:
private static final Pattern ERROR_PATTERN =
Pattern.compile(
"^\\s*(?:SQLState\\s*=\\s*([^,]+?)\\s*,\\s*NativeError\\s*=\\s*([+-]?\\d+)
\\s*|" +
"Error\\s+=\\s*+(.*?))$", Pattern.MULTILINE );
/**
* Extract error BCP messages from the given process output.
*
* @.param bcpOutput the output to extract the messages from.
* @.return a List of {@.link String} with the messages.
*/
protected static List extractErrorMessages( String bcpOutput ) {
List messages = new LinkedList();
Matcher m = ERROR_PATTERN.matcher( bcpOutput );
SqlServerError err = null;
while ( m.find() ) {
String message = m.group( 3 );
if ( message == null ) {
/*
* no message => first line:
* SQLState = 23000, NativeError = 3604
*/
err = new SqlServerError();
err.setSqlState( m.group( 1 ) );
err.setNativeError( StringParsing.string2int( m.group(
2 ) ) );
}
else {
/*
* second line:
* Error = [Microsoft][ODBC SQL Server Driver][SQL
Server]Doppelter Schl?ssel wurde ignoriert.
*/
err.setMessage( message );
messages.add( err );
err = null;
}
}
return messages;
}
Kind regards
robert

BCN service questions

Hi

some questions about BCN service :

- WE have several remote clients and we don’t know their addresses.

- We create a BCN Service for dynamic routes configuration.

- Each time we send a message to a specific user message is sent to BCN asking for a route

- Messages for a specific user can have specific conversation group id

( Question : if we send several message to the client with no route does it mean that there will be several messages to BCN ?.

My guess that “missing route” message is sent to BCN for every new conversation but not for several messages inside one conversation. )

- BCN service doesn’t answer immediately because it doesn’t know the address.

- There will be an event in the system ( client is connected and provide an address ) when we know his address and only then BCN will answer to the message asking for the route for this client.

( Question : if we have several messages ( probably several conversations ) asking route for a specific client does it mean we need to answer to all these messages/conversations? ).

Will this work?

Leonid.

When the service broker service begins a dialog with the BCN service and sends a missing route message, it will not send any more messages until the BCN service ends the dialog. When the dialog is ended, broker will try to classify dialogs targetting the missing service again. If no routes are found, the messages remain in DELAYED state for 10 minutes. Then service broker service will begin a new conversation with the BCN service to re-request the missing route.

|||

So if I have several clients with no routes information then BCN is not a solution ...

I mean the scenario I have presented in my question won;t work?

|||

What do you mean by several client? If you mean target services, Service Broker will send a request to BCN for each service with a missing route. So I don't see why this shouldn't work.

Basic Security Question for remote app

I'm designing a C# winforms app that will be used by 500 customers/companies. The customers will use the app to edit data for their company on the remote SQL Server. What's the best way to manage security in terms of logins/passwords? Should each user have a different login? (probably not) Or should the application have it's own login/password that is encrypted and the user never knows what the login/password is?

Also, is it necessary to use Web Services for this application? Any other thoughts/comments are very welcome.

If there are 500 DIFFERENT companies, would you want a user for companyA to be able to change information for CompanyB?

If not, then using a common login/permission set is NOT a good idea and individual logins seem necessary. It seems like the question then becomes "How to mangage a large number of logins/permissions?"

Perhaps more details are would be useful to fully understand your scenario...

|||Here's some more detail:

Company Table
CompanyId
CompanyName

ProductTable
ProductId
CompanyId
ProductName
Price

Then, we ship out applications to all the different companies.
The application should know what their CompanyId is. In the C# application, there will be a grid that displays all of the Products for their CompanyId and allows the user to update those products (for example, change the Price for a given product).

At this point, I'm just kind of at a loss for the best way to design this in terms of SQL Server Security and logins/passwords, etc. Hope this is not too vague.
|||

This will prove to be an interesting challenge. I can already imagine some interprising soul for a company 'exploring' the products for other companies -and who knows, even changing prices...

The 'safest' way to control data access (keeping each user to his/her own company) is by knowing what user is attempting data access on each query execution. Without a company specific login (or user specific login), you will have a difficult (perhaps even impossible) time keeping users out of each others data.

Yes, on the surface, the application can appear to take care of it. But are you willing to 'guarantee' all companies that their data is safe from prying eyes and safe from accidental or intentional alteration.

The database 'should' know, with certainity, who (user or company) is accessing data. I think that you have no option but to assign individual logins to each company/user (all users for a company 'could' use the same login).

You will need a process to manage Logins, adding users, resetting lost passwords, disabling or removing users. And a way to map a login to a CompanyID. Once someone logins in, the CompanyID value is supplied to be used in WHERE criteria. I wouldn't accept a CompanyID from the application.

Personally, from the data professional perspective, I wouldn't trust my career to expecting the application to control all access.

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)