BCP process failed

[markryan]

Hi all,

We have encoutered an issue where our bulk load works for all our SQL Server environments, except for 1.  It just so happens that this SQL server install is a 2000 install as opposed to our other environments, which are 2005.  But, the ETL code was originally built for a 2000 install, and we just tried porting it to 2005 to see if there would be problems.  I don't think this is actually a problem caused by the two versions.  I think it's a setup issue that I just don't understand yet.  and we can't try the original 2000 environment that the code was built against because that server has gone to a better place.

I wonder if bcp.exe or Data Manager sets the server name with square brackets [], and causes the issue?  In my log from the 2005 run, the call to bcp looks a little different in that it have "DB_NAME"."schema"."TABLE_NAME" and the 2000 run has "[DB_NAME].[schema].[table_NAME]".

SQL Server 2000 run:
"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" "[DB_NAME].[schema].[table_NAME]" in "G:\Cognos Data\Data Manager\SSLOAD_00000ed8_something_1.dat" -SERVER_NAME -USER_NAME -P<password> -f"G:\Cognos Data\Data Manager\SSLOAD_00000ed8_something_2.fmt" -e"G:\Cognos Data\Data Manager\SSLOAD_00000ed8_something_3.bad"

SQL Server 2005 run:
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\bcp.exe" "DB_NAME"."schema"."TABLE_NAME" in "G:\Cognos Data\Data Manager\SSLOAD_00000ed8_something_1.dat" -q -SERVER_NAME -USER_NAME -P<password> -f"G:\Cognos Data\Data Manager\SSLOAD_00000ed8_something_2.fmt" -e"G:\Cognos Data\Data Manager\SSLOAD_00000ed8_something_3.bad"


Here is the error:
SQLState = 37000, NativeError = 4060
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login '[SERVER_NAME]'. Login fails.
-----------------------------------------

DM-SSL-0060 Delivery 'BCP': BCP process failed (status 1)

DM-BLD-0005 The delivery 'BCP' failed.

Does anyone know why this would happen?  and does anyone know what is set differently on the two environments to make it so one ETL run creates the command with the quoted identifiers on, and the other one not?  Is this an SQL Server setting or a Data Manager setting?  since the ETL code is the same, it sounds like this is something that SQL Server might be doing based on it's configuration.

Hope someone can help get us looking in the right area.

Thanks,

Mark

[UseCog]

just a thought ,

Is your user have full acces rights on the database, if not, is it possible to create another user with all sysadmin rights and can you try to load the data again.

[markryan]

Thanks for the post.  We had tried the user access scenario.

It turns out it was due to the SQL Client tools installed on the ETL server not matching the ones on the SQL Server installation.  So bcp.exe on the ETL server was not the same version as bcp.exe (and associated components).

It could be that the older client just didn't translate the parameters the same way as the newer version.  Not sure exactly what the true explanation is, but it something to check if you run into this type of error is making sure that the client and server SQL tools are compatible versions.

Hope this helps someone,

Mark