Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

BCP Error On New Server Expand / Collapse
Author
Message
Posted Tuesday, September 18, 2012 9:10 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:59 AM
Points: 886, Visits: 1,544
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!
Post #1360851
Posted Tuesday, September 18, 2012 11:58 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:50 PM
Points: 23,045, Visits: 31,570
Have you tried this on the new server?


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





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)
Post #1360926
Posted Tuesday, September 18, 2012 3:51 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:59 AM
Points: 886, Visits: 1,544
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!
Post #1361044
Posted Tuesday, September 18, 2012 3:53 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:50 PM
Points: 23,045, Visits: 31,570
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'





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)
Post #1361045
Posted Tuesday, September 18, 2012 4:01 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:50 PM
Points: 23,045, Visits: 31,570
Bumping thread due to editting issues.


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)
Post #1361048
Posted Tuesday, September 18, 2012 4:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:56 PM
Points: 36,775, Visits: 31,230
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1361054
Posted Tuesday, September 18, 2012 5:53 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:50 PM
Points: 23,045, Visits: 31,570
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 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)
Post #1361066
Posted Wednesday, September 19, 2012 8:36 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:59 AM
Points: 886, Visits: 1,544
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!
Post #1361400
Posted Wednesday, September 19, 2012 8:56 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:50 PM
Points: 23,045, Visits: 31,570
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'





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)
Post #1361413
Posted Wednesday, September 19, 2012 8:58 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:59 AM
Points: 886, Visits: 1,544
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!
Post #1361416
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse