BCP Error On New Server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » BCP Error On New Server

19 posts, Page 1 of 2

1 2 »»

BCP Error On New Server

Rate Topic

Display Mode

Topic Options

Author

Message

YSLGuru

Posted Tuesday, September 18, 2012 9:10 AM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:18 AM
Points: 848, Visits: 1,453

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,

A Democracy works great until the day you find yourself on the sheep side of a vote between 5 wolves and 4 sheep on what’s for dinner when neither have eaten in many days. A free Republic where the rights of the few and the individual are protected is the only one in which Freedom and Prosperity for all have a chance to blossom.

Post #1360851

Lynn Pettis

Posted Tuesday, September 18, 2012 11:58 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:24 PM
Points: 21,649, Visits: 27,506

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

YSLGuru

Posted Tuesday, September 18, 2012 3:51 PM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:18 AM
Points: 848, Visits: 1,453

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'

Post #1361044

Lynn Pettis

Posted Tuesday, September 18, 2012 3:53 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:24 PM
Points: 21,649, Visits: 27,506

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'


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

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1361045

Lynn Pettis

Posted Tuesday, September 18, 2012 4:01 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:24 PM
Points: 21,649, Visits: 27,506

Bumping thread due to editting issues.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1361048

Jeff Moden

Posted Tuesday, September 18, 2012 4:47 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:10 PM
Points: 32,931, Visits: 26,823

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1361054

Lynn Pettis

Posted Tuesday, September 18, 2012 5:53 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:24 PM
Points: 21,649, Visits: 27,506

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

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1361066

YSLGuru

Posted Wednesday, September 19, 2012 8:36 AM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:18 AM
Points: 848, Visits: 1,453

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,

A Democracy works great until the day you find yourself on the sheep side of a vote between 5 wolves and 4 sheep on what’s for dinner when neither have eaten in many days. A free Republic where the rights of the few and the individual are protected is the only one in which Freedom and Prosperity for all have a chance to blossom.

Post #1361400

Lynn Pettis

Posted Wednesday, September 19, 2012 8:56 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:24 PM
Points: 21,649, Visits: 27,506

YSLGuru (9/19/2012)

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

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1361413

YSLGuru

Posted Wednesday, September 19, 2012 8:58 AM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:18 AM
Points: 848, Visits: 1,453

Lynn Pettis (9/18/2012)

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

Post #1361416

« Prev Topic | Next Topic »

19 posts, Page 1 of 2

1 2 »»

Permissions