September 18, 2012 at 9:10 am
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!September 18, 2012 at 11:58 am
Have you tried this on the new server?
SELECT @sMysql = 'bcp "SELECT sOutput FROM MYDATABASE.dbo.VPOSITIVEPAY" queryout ' + @sFilePath + @sFileName + ' -T -c'
September 18, 2012 at 3:51 pm
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!September 18, 2012 at 3:53 pm
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'
September 18, 2012 at 4:01 pm
Bumping thread due to editting issues.
September 18, 2012 at 4:47 pm
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
Change is inevitable... Change for the better is not.
September 18, 2012 at 5:53 pm
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.
September 19, 2012 at 8:36 am
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!September 19, 2012 at 8:56 am
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'
September 19, 2012 at 8:58 am
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!September 19, 2012 at 9:03 am
Okay, don't know how many ways I can say it. It looks like you need to add the -S parameter to your BCP command line. Without it BCP is trying to connect to the default instance of SQL Server and you have specified that you are using a named instance on the new server. This means you need to tell bcp the name of the instance.
This:
SELECT @sMysql = 'bcp "SELECT sOutput FROM MYDATABASE.dbo.VPOSITIVEPAY" queryout ' + @sFilePath + @sFileName + ' -S [SQLSVR04\SS2005] -T -c'
Notice the -S parameter I have added.
September 19, 2012 at 9:39 am
Lynn Pettis (9/19/2012)
Okay, don't know how many ways I can say it. It looks like you need to add the -S parameter to your BCP command line. Without it BCP is trying to connect to the default instance of SQL Server and you have specified that you are using a named instance on the new server. This means you need to tell bcp the name of the instance.This:
SELECT @sMysql = 'bcp "SELECT sOutput FROM MYDATABASE.dbo.VPOSITIVEPAY" queryout ' + @sFilePath + @sFileName + ' -S [SQLSVR04\SS2005] -T -c'
Notice the -S parameter I have added.
Lynn,
I did not see the -s param n any of the previous posts. It wasn't until this last post when the comments pointed to something not being seen that I realized there was more text then what I was seeing. My browser was only showing up thru to the '+@sFileName' piece and I did not reliaze there was anything more/diferfent after. If I had I certainly would not have continued to post lengthy messages and do tests when there was something to try that I had not done. Thank you.
That said, it still failed and I copied your sample code just to make sure I did not miss anything or cotain any typos. And I did try it like this:
SELECT @sMysql = 'bcp "SELECT sOutput FROM MYDATABASE.dbo.VPOSITIVEPAY" queryout ' + @sFilePath + @sFileName + ' -S [SQLSVR04\SS2005] -T -c'
And like this just in case it made a difference:
SELECT @sMysql = 'bcp "SELECT sOutput FROM [SQLSVR04\SS2005].MYDATABASE.dbo.VPOSITIVEPAY" queryout ' + @sFilePath + @sFileName + ' -S [SQLSVR04\SS2005] -T -c'
What is DIFFERENT using the -S param is that the query fails when run from the old server and the new server. This is the first test where it failed when run from the old server.
Thanks
Kindest Regards,
Just say No to Facebook!September 19, 2012 at 9:43 am
On the new server, what was the error message? (Sorry, but saying it failed doesn't tell us anything.)
September 19, 2012 at 9:53 am
The following worked on my laptop system which uses a named instance for SQL Server 2005:
bcp "select * from master.sys.databases" queryout C:\Users\lynn.pettis\testfile.txt -S ISS203897\ISS2K5 -T -c
The following failed on my laptop system which uses a named instance for SQL Server 2005:
bcp "select * from master.sys.databases" queryout C:\Users\lynn.pettis\testfile.txt -T -c
So, with that, try this:
SELECT @sMysql = 'bcp "SELECT sOutput FROM MYDATABASE.dbo.VPOSITIVEPAY" queryout ' + @sFilePath + @sFileName + ' -S SQLSVR04\SS2005 -T -c'
September 19, 2012 at 4:11 pm
Bump. Have you tried my latest change? You know, not including the [ ] around the server name and instance name with the -S parameter to bcp?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply