A .cmd file can be run on SQL7, but not on SQL2000

Hi,
I have a command file with the following SQL statement:
bcp "SELECT * FROM [%dbname%].[dbo].[%1] WHERE DATEDIFF(hour,TimeStamp,
DATEADD(n, -DATEPART(n,getdate()), getdate())) <=24" queryout %CurDate%.tmp
-U -P -f %formatfile%
I can run this file on a machine with SQL7 installed, but have error when I
run it on a machine with SQL2000. Here is the error:
SQLState = 08001, Native Error=17
Error=[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not
exist or access denied.
SQLState = 01000, Native Error=2
Warning=[Microsoft][ODBC SQL Server Driver][Shared
Memory]ConnectionOpen(Connect()).
Does anyone know where is the problem? How can I fix it?
Thanks for the support!
On Thu, 30 Mar 2006 09:56:02 -0800, June wrote:

>Hi,
>I have a command file with the following SQL statement:
>bcp "SELECT * FROM [%dbname%].[dbo].[%1] WHERE DATEDIFF(hour,TimeStamp,
>DATEADD(n, -DATEPART(n,getdate()), getdate())) <=24" queryout %CurDate%.tmp
>-U -P -f %formatfile%
>I can run this file on a machine with SQL7 installed, but have error when I
>run it on a machine with SQL2000. Here is the error:
>SQLState = 08001, Native Error=17
>Error=[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not
>exist or access denied.
>SQLState = 01000, Native Error=2
>Warning=[Microsoft][ODBC SQL Server Driver][Shared
>Memory]ConnectionOpen(Connect()).
>Does anyone know where is the problem? How can I fix it?
>Thanks for the support!
Hi June,
Some things to check:
- Are you sure the SQL Server 2000 service is running, and that the
computer that runs this script and the server where SQL Server is
installled can "see" each other?
- Try adding the "-S servername[\instance_name]" argument
- Try using a trusted connection instead of a SQL Server login (ie
replace the "-U -P" arguments with "-T").
- If you can't use a trusted connection, double-check that SQL Server is
configured to allow "Mixed Mode Authentication". The default is "Windows
Authentication", which allows only trusted connections. Also, add userid
and password to the command string (i.e. replace "-U -P" with "-U userid
-P password").
- Check the Client Network Utility on the workstation and the Server
Network Utility on the server. Are the same protocols enabled? Are the
properties the same? If there are any firewalls between the workstation
and the server, have you made sure that the prots required for SQL
Server are not blocked?
Hugo Kornelis, SQL Server MVP
|||Hi Hugo,
Thanks for your info.
1. Both the command file and SQL server are running on the same machine
(Win2000).
2. I have tried to add -S [instance_name] parameter to the command line, but
I got similar error messages:
SQLState = 08001, Native Error=17
Error=[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
exist or access denied.
SQLState = 01000, Native Error=53
Warning=[Microsoft][ODBC SQL Server
Driver][DBNETLIB]ConnectionOpen(Connect()).
3. I also tired to replace -T with -U -P, still got the same error.
What else should I try?
Thanks again!
"Hugo Kornelis" wrote:

> On Thu, 30 Mar 2006 09:56:02 -0800, June wrote:
>
> Hi June,
> Some things to check:
> - Are you sure the SQL Server 2000 service is running, and that the
> computer that runs this script and the server where SQL Server is
> installled can "see" each other?
> - Try adding the "-S servername[\instance_name]" argument
> - Try using a trusted connection instead of a SQL Server login (ie
> replace the "-U -P" arguments with "-T").
> - If you can't use a trusted connection, double-check that SQL Server is
> configured to allow "Mixed Mode Authentication". The default is "Windows
> Authentication", which allows only trusted connections. Also, add userid
> and password to the command string (i.e. replace "-U -P" with "-U userid
> -P password").
> - Check the Client Network Utility on the workstation and the Server
> Network Utility on the server. Are the same protocols enabled? Are the
> properties the same? If there are any firewalls between the workstation
> and the server, have you made sure that the prots required for SQL
> Server are not blocked?
> --
> Hugo Kornelis, SQL Server MVP
>
|||On Thu, 30 Mar 2006 14:55:01 -0800, June wrote:

>2. I have tried to add -S [instance_name] parameter to the command line, but
>I got similar error messages:
Hi June,
The correct usage of -S is either
-S server_name
or
-S server_name\instance_name

>What else should I try?
If none of these option work, try posting to a different group. This
group is actually intended for support to the "Microssoft English Query"
tool. Precious few people visit here.
Since your question is about bcp, one of the tools that come with SQL
Server, microsoft.public.sqlserver.tools would be the best place to ask.
Hugo Kornelis, SQL Server MVP