• 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!