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


BCP Error On New Server


BCP Error On New Server

Author
Message
YSLGuru
YSLGuru
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1850 Visits: 1665
I have a SQL Job that executes a BCP command to create an file of exported data. The job has worked fine for years (literally). We have moved our DB to a new server. I copied the job to the new server and updated the query. The job will not run on the new server using the copy of the DB on the new server but the same job on the old server will work if I update the query in it to point to the DB on the new server. If I change the query from this:


SELECT @sMysql = 'bcp "SELECT sOutput FROM SQLSVR07.MYDATABASE.dbo.VPOSITIVEPAY" queryout ' + @sFilePath + @sFileName + ' -T -c'



To the below it will work when run from the old DB Server named SQLSVR07 but fails if run from the new server named SQLSVR04:

SELECT @sMysql = 'bcp "SELECT sOutput FROM SQLSVR04.MYDATABASE.dbo.VPOSITIVEPAY" queryout ' + @sFilePath + @sFileName + ' -T -c'




So its not the query that is the problem but the location from which the job is run that causes the failure. The error I get is listed below along with the complete DML of the job as it was on the old server point to the old copy of our DB. I have checked the new DB Sever and verified it is set to allow remote connections just like the old sever.

Both jobs, the one on the old server and the one on the new server are using the same SQL Login which has the same permissions. When testing this I am placing the DML from the Job Step into a query window which is logged on using my domain account so the same level of permissions for the domain is being used so I don;t believe its an issue with the credentials of the account the job is running under.

Any ideas on why this call to BCP fails when is run from on the new server but will still work when run form the old server and pointing to the copy of the DB on the new server?


Actual Error:


SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
NULL





DML of Job On old SQL Server which is named SQLSVR07:

DECLARE @sMysql VARCHAR(8000)
DECLARE @sFileName VARCHAR(256)
DECLARE @sFilePath VARCHAR(256)

/*Set the File Path and name to use for export file*/
SELECT @sFilePath = '\\SVR10\exports\Positive_Pay\',
@sFileName = 'PP_MyBank.prn'

/*Build command text to pass to BCP Utility*/
SELECT @sMysql = 'bcp "SELECT sOutput FROM SQLSVR07.MYDATABASE.dbo.VPOSITIVEPAY" queryout ' + @sFilePath + @sFileName + ' -T -c'


EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE
EXECUTE sp_configure 'xp_cmdshell', 1
RECONFIGURE

/*Call the BCP Utility via the Master database's xp_CmdShell XP*/
EXECUTE master..xp_cmdshell @sMysql

EXECUTE sp_configure 'xp_cmdshell', 0
RECONFIGURE
EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE



Kindest Regards,

Just say No to Facebook!
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40328 Visits: 38567
Have you tried this on the new server?



SELECT @sMysql = 'bcp "SELECT sOutput FROM MYDATABASE.dbo.VPOSITIVEPAY" queryout ' + @sFilePath + @sFileName + ' -T -c'




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
YSLGuru
YSLGuru
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1850 Visits: 1665
Lynn Pettis (9/18/2012)
Have you tried this on the new server?



SELECT @sMysql = 'bcp "SELECT sOutput FROM MYDATABASE.dbo.VPOSITIVEPAY" queryout ' + @sFilePath + @sFileName + ' -T -c'





Yes. When I first tried to run the code from the job step It was structured so that only the Database name and schema.view where used (no servername mentioend). I just realized I forgot to mention that the new SQL Server (SQLSVR04) is a named instance (SQLSVR04\SS2005) where as the old DB server was a default instance. I don't know if that makes a difference for this error but when I tested this I diud use the ServerName\Named Instance reference as shown below and it works when I run it from SQLSVR07 connecting to the DB on SQLSVR04\SS2005:





SELECT @sMysql = 'bcp "SELECT sOutput FROM [SQLSVR04\SS2005].MYDATABASE.dbo.VPOSITIVEPAY" queryout ' + @sFilePath + @sFileName + ' -T -c'




Kindest Regards,

Just say No to Facebook!
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40328 Visits: 38567
YSLGuru (9/18/2012)
Lynn Pettis (9/18/2012)
Have you tried this on the new server?



SELECT @sMysql = 'bcp "SELECT sOutput FROM MYDATABASE.dbo.VPOSITIVEPAY" queryout ' + @sFilePath + @sFileName + ' -T -c'





Yes. When I first tried to run the code from the job step It was structured so that only the Database name and schema.view where used (no servername mentioend). I just realized I forgot to mention that the new SQL Server (SQLSVR04) is a named instance (SQLSVR04\SS2005) where as the old DB server was a default instance. I don't know if that makes a difference for this error but when I tested this I diud use the ServerName\Named Instance reference as shown below and it works when I run it from SQLSVR07 connecting to the DB on SQLSVR04\SS2005:





SELECT @sMysql = 'bcp "SELECT sOutput FROM [SQLSVR04\SS2005].MYDATABASE.dbo.VPOSITIVEPAY" queryout ' + @sFilePath + @sFileName + ' -T -c'




Try this:



SELECT @sMysql = 'bcp "SELECT sOutput FROM MYDATABASE.dbo.VPOSITIVEPAY" queryout ' + @sFilePath + @sFileName + ' -S [SQLSVR04\SS2005] -T -c'




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40328 Visits: 38567
Bumping thread due to editting issues.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88372 Visits: 41128
The problem is likely a "simple" matter of permissions. The login for the new server probably doesn't have the same directory-level privs as the old server. It's an easy fix... give the login for the new server the same directly-level privs as the old.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40328 Visits: 38567
Actually, trying to connect to a named instance. Need to specify that in the command line. The reason it worked on the old server is probably two-fold. One, they connected to the default instance with a trusted connection, and two there is probably a linked server defined on the old server pointing to the new server.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
YSLGuru
YSLGuru
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1850 Visits: 1665
Thanks for the suggestion Jeff but I'm using the same account on the new server that I was on the old and both servers on on the same domain. I'm no IT admin type but does anyone know if within a Windows domain its possible for an account run from one server to have different permissions then when the same account is run/used on another server if both servers are in the same domain? Thats the only thing I can think of.

Thanks

Kindest Regards,

Just say No to Facebook!
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40328 Visits: 38567
YSLGuru (9/19/2012)
Thanks for the suggestion Jeff but I'm using the same account on the new server that I was on the old and both servers on on the same domain. I'm no IT admin type but does anyone know if within a Windows domain its possible for an account run from one server to have different permissions then when the same account is run/used on another server if both servers are in the same domain? Thats the only thing I can think of.

Thanks


Did you try my suggestion with the -S parameter included?



SELECT @sMysql = 'bcp "SELECT sOutput FROM MYDATABASE.dbo.VPOSITIVEPAY" queryout ' + @sFilePath + @sFileName + ' -S [SQLSVR04\SS2005] -T -c'




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
YSLGuru
YSLGuru
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1850 Visits: 1665
Lynn Pettis (9/18/2012)
Actually, trying to connect to a named instance. Need to specify that in the command line. The reason it worked on the old server is probably two-fold. One, they connected to the default instance with a trusted connection, and two there is probably a linked server defined on the old server pointing to the new server.


Lynn,

I've taken the SQL Job bit out of this until I can get it working so as to simplify the process/test. If I log onto my new server using SSMS and my domain user account which is a memeber of dmain admins, and I run the below query it fails. The database it is referring to is on the SQL Server my query window is connected to. I woudl think that would eliminate the need to expliclty refernce the server name & alias but maybe with BCP thats not the case? That said I added the server name & alias explcitly to the query and it still failed with the same error and yet if using the exact same query as shown below but logged inot the old server using SSMS and ny domain acct the query works. Between those 2 tests the only difference if teh SQL Server the query is being run from.

Thoughts? Is there anything on the SQL Server Level that need to be set/changed to facilitate this that I may have missed?

UPDATE: In addition to the below I did another test. I changed the destination from '\\SVR10\exports\Positive_Pay\' to '\\SQLSVR04\exports\Positive_Pay\' so as to eliminate any possible permissions issue between the SQL Server and the server that hosts the destination of the file. Using this new destination the test failed again when run from the new server but once again worked when run from the old server. This menas that the destination of the file and the domain acct being used to execute the BCP query are irrelevant. it is the SQL Server the query window is connected to that affects the outcome of the eblow query. Hopefully that will help with figuring out whats going on.

Thanks to all for replying.


TEST T-SQL CODE USED (from both old & new SQL Servers):


DECLARE @sMysql VARCHAR(8000)
DECLARE @sFileName VARCHAR(256)
DECLARE @sFilePath VARCHAR(256)

/*Set the File Path and name to use for export file*/
SELECT @sFilePath = '\\SVR10\exports\Positive_Pay\',
@sFileName = 'PP_MyBank.prn'

/*Build command text to pass to BCP Utility*/
SELECT @sMysql = 'bcp "SELECT sOutput FROM [SQLSVR04\SS2005].MYDATABASE.dbo.VPOSITIVEPAY" queryout ' + @sFilePath + @sFileName + ' -T -c'


EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE
EXECUTE sp_configure 'xp_cmdshell', 1
RECONFIGURE

/*Call the BCP Utility via the Master database's xp_CmdShell XP*/
EXECUTE master..xp_cmdshell @sMysql

EXECUTE sp_configure 'xp_cmdshell', 0
RECONFIGURE
EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE




Kindest Regards,

Just say No to Facebook!
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