HELP!!! BCP Utility Error 3

[kelfuego]

Yesterday my great plains server's HD went out. I have replaced the server and imported my database backup files into SQL. I have also reinstalled the great plains software.

As I start to run the software though I am recieving the following message:

"An error occurred while using the BCP utility--Data was not correctly copied to the server. Please verify your odbc settings and that BCP has been correctly installed. Great Plains Utilities will now exit"

Now I have no idea what BCP is so i would assume I haven't installed it yet. Can someone help me?


Kelly Johnson MCP
Volunters of America Oregon

 

[Luvsql]

I have seen this before where the installation of Great Plains requires that the server by rebooted. If you have rebooted and still get the error, here is a checklist. Mostly likely it could be the servername that is causing it (#4).

1. Verify that your ODBC connection is using SQL server authentication. Only using Windows authentication will cause BCP to fail.

2. Check the TestBCP.bcp document found in the Great Plains directory to determine what is causing BCP to fail.

3. Add AllowBCPTest=FALSE to the Dex.ini file and launch Utilities.

4. On SQL Server, BCP will fail if the Servername is not the same as the Machine Name or if the Servername contains illegal characters. Determine the SQL Server Servername by executing the following statements against the MASTER database in Query Analyzer:

SELECT @@servername
SELECT * from master..sysservers

5. If the scripts in Step 4 do not match, you will want to change the SQL Servername using the following steps:

From the SQL Query Analyzer window, execute the following statements one at a time against the MASTER database. The Servername for the sp_dropserver is the wrong name and the Servername for the sp_addserver is your computer name:

sp_dropserver 'WrongSERVERNAME'
sp_addserver 'RightSERVERNAME', local

Once the above statements are executed, stop and start Microsoft SQL Server for the changes to take effect.

Run SQL Server Setup from the original product CD. Go as far as the Typical, Minimal, and Custom screen. Doing so will not reinstall SQL Server or any components; however, it will update SQL Server internally to reflect the Windows computer name.

6. If the scripts in Step 4 do match, check the SRVID for the Servername. It needs to be 0, which tells the system it is the local server. Step 5 will make the server local if the SRVID is not 0.

7. Verify that the path to BCP.EXE is correct, that there is only one BCP.EXE, and it is the correct version of SQL that you are using. To verify the path to BCP.EXE, go to the DOS prompt and type PATH and you should see the path to the MSSQL7 | Bin directory for SQL 7.0 or Program Files | Microsoft SQL Server | 80 | Tools | Bin directory for SQL 2000 that holds the BCP.EXE file. If there is more than one BCP.EXE and they are different versions, rename the BCP.EXE of the version that you are not using.

8. Check the ODBCBCP.DLL and SQLSRV32.DLL files. The ODBCBCP.DLL and SQLSRV32.DLL files must be the same version. If they are different versions, rename the SQLSRV32.DLL file and reinstall MDAC(ODBC). The ODBCBCP.DLL and SQLSRV32.DLL files can be found in Windows Explorer under the WINNT | System32 folder. They must be exactly the same (ODBCBCP.DLL = 3.70.0623 and SQLSRV32.DLL = 3.70.0820 will not work).

9. Change the 'sa' password if it contains spaces or symbols. Examples: &, *, #, or %.

Using Enterprise Manager, reset the 'sa' password to something that is just alphanumeric characters.

10. Check ODBC connectivity:

Verify that the System DSN tests successfully.
If you are using SQL Server 7.0, verify that you can successfully run odbcping:
(Odbcping - D Datasource -U Username -P Password)
Set up a new System DSN with a name that have not been used before.

11. The Error Messages Master (SY01700) table is the first table populated within DYNAMICS database via BCP during the eEnterprise/Dynamics for SQL Client\Server Installation. Verify that BCP is failing by checking the number of records in the Error Messages Master table. Run the following statement against the DYNAMICS database in Query Analyzer:

SELECT * from DYNAMICS..SY01700

If there are no records in the SY01700 table, the BCP process failed. Proceed with the checklist.

12. Verify that no errors exist in the SYSDATA.BCP file. This file can be found in Windows Explorer under eEnterprise | SQL | System | Data. To open this file, double-click on the SYSDATA.BCP file and check for error messages.

13. BCP will fail if the Global Variables in the SYSDAT.BAT file are not populating correctly. To run the BCP manually:

In Windows Explorer, go to the eEnterprise | SQL | System | Data folder. Locate SYSDAT.BAT, right-click on it and make a Copy of the file in the same directory. Once the copy has been made, right click on the file and select Edit. This file should open in Notepad.

Do a Replace (Find | Replace) and Find - replacing the following values:

Find: %SERVER% Replace with: the SQL Server name
Find: %USER% Replace with: sa
Find: %DATABASE% Replace with: DYNAMICS
Find: %PASSWORD% Replace with: the sa password
(if none, just delete the %PASSWORD% text and leave it blank)

Save the file and close. Double-click on the Copy of SYSDAT.BAT. A DOS window should appear. Once the DOS window closes, run the following query against the DYNAMICS database in Query Analyzer to verify that the BCP was successful:

SELECT * from DYNAMICS..SY01700