SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Configure 'Allow Updates'


Configure 'Allow Updates'

Author
Message
cms9651
cms9651
SSC-Addicted
SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)

Group: General Forum Members
Points: 468 Visits: 144
hi there, hope in your help.

I tried this code in sql server, but I've this error.
Can you help me?
I would really appreciate any help.
sp_configure 'Allow Updates',
0;

RECONFIGURE WITH override;

EXEC master.dbo.sp_configure 'show advanced options',
1 RECONFIGURE EXEC master.dbo.sp_configure 'xp_cmdshell',
1 RECONFIGURE EXEC xp_cmdshell 'bcp "SELECT * FROM [dbo].[tbl];" queryout "\\XX.XX.XX.XX\output.txt" -T -c -t;'


SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while
establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct
and if SQL Server is config
ured to allow remote connections. For more information see SQL Server Books Online.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expired


Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39216 Visits: 14411
cms9651 (5/15/2013)
hi there, hope in your help.

I tried this code in sql server, but I've this error.
Can you help me?
I would really appreciate any help.
sp_configure 'Allow Updates',
0;

RECONFIGURE WITH override;

EXEC master.dbo.sp_configure 'show advanced options',
1 RECONFIGURE EXEC master.dbo.sp_configure 'xp_cmdshell',
1 RECONFIGURE EXEC xp_cmdshell 'bcp "SELECT * FROM [dbo].[tbl];" queryout "\\XX.XX.XX.XX\output.txt" -T -c -t;'


SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while
establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct
and if SQL Server is config
ured to allow remote connections. For more information see SQL Server Books Online.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expired



A few things:

1. 'Allow Updates' has not done anything since SQL 2005 was released so you can drop that from your script. Reference
2. Do not use RECONFIGURE WITH OVERRIDE; unless you have a good reason. I know SSMS spits it out when you script a change done through the GUI, but I would recommend against ever executing it in a script.
3. Are you sure you want to use xp_cmdshell? Only use it if you have no other options, and I am confident that I can help you find a better option. At a higher level than just "I need to get data from a table into a file", what exactly are you designing?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
cms9651
cms9651
SSC-Addicted
SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)

Group: General Forum Members
Points: 468 Visits: 144
thank you for help.
I need to get data from a table into a file txt.
OTF
OTF
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1232 Visits: 4128
Is this going to be an ongoing process or is it just a one-off?
cms9651
cms9651
SSC-Addicted
SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)

Group: General Forum Members
Points: 468 Visits: 144
This is an ongoing process, planned in the server
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39216 Visits: 14411
Are you on an Edition of SQL Server that has SQL Agent? In other words not SQL Express Edition?

If so, I would recommend you setup a SQL Agent Job to run bcp for you. When you setup the job, the Job Step Type will be "Operating System (CmdExec)" and in the command you will put your call to bcp. Then you can schedule the job to run as needed.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
cms9651
cms9651
SSC-Addicted
SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)

Group: General Forum Members
Points: 468 Visits: 144
opc.three (5/16/2013)
Are you on an Edition of SQL Server that has SQL Agent? In other words not SQL Express Edition?


Not Sir, the db is to remote server and I use the navicat SQL Server for connected to db
kevaburg
kevaburg
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4441 Visits: 1025
OPC has got it. An SQL Server Agent job with BCP is definately the answer.

If however you have an Express version, I would recommend creating an OS-level Batch Job that calls BCP and then running the batch using Windows Scheduler.
cms9651
cms9651
SSC-Addicted
SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)

Group: General Forum Members
Points: 468 Visits: 144
I tried this but the table is not exporting in txt file:
[SQL] DECLARE
@SQL VARCHAR (8000) SELECT
@SQL = 'bcp mytable out \\XX.XX.XX.XX\output.txt -T -c -t'
Affected rows: 0
Time: 0.041ms

Affected rows: 0



Steve Jones
Steve Jones
SSC Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: Administrators
Points: 145333 Visits: 19425
It's possible you are getting an error on the pathing that you don't see. The command will require that the account running SQL Server (service account) has rights to the \\xx.xx.xx.xx\xx share.

What you might try doing is double checking rights or changing to a local path for testing. Then if the bcp works, you can add the specific rights to the service account.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search