SQL query in batch file problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL query in batch file problem

Author

Topic

JeffT
Posting Yak Master

111 Posts

Posted - 2006-08-11 : 08:47:37

Hi,

I am trying to run this query in a batch file but am getting an "Incorrect syntax near keyword 'and'" error. Any help would be much appreciated. Thanks, Jeff

ECHO OFF
set GatewayDB="SybaseHDR_Prod"
set DBUser="user"
set DBPswd="pswd"
set SQLServer="nnn.nn.nnn.nn"
set HdrctlNbr1 ="20060714000000000000"
set HdrctlNbr2 ="20060727999999999999"

ECHO ========================================================================
ECHO Start of Bulk Copy Process from Production Tables to Output Text Files
ECHO Database is : %GatewayDB%
ECHO Server : %SQLServer%
ECHO File AuthNbr: %AuthNbr%
ECHO ========================================================================

set TableName="hdr_ctl_nbr_status"
set outfile="C:\BCP\hdr_ctl_nbr_status.txt"
If exist %outfile% del %outfile%
bcp "SELECT * FROM %GatewayDB%..%TableName% where hdr_ctl_nbr between %HdrctlNbr1% and %HdrctlNbr2%" queryout %outfile% -b1000 -c -t~ -U %DBUser% -P %DBPswd% -S%SQLServer%
REM pause

:END

ECHO ================================
ECHO Bulk Copy process is completed !
ECHO ================================

pause

Q
Yak Posting Veteran

76 Posts

Posted - 2006-08-11 : 08:51:02

Are hdr_ctl_nbr, HdrctlNbr1 and HdrctlNbr2 of the same datatype???

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-08-11 : 08:53:25

My guess is that since the HDRCTLNBRs seem to be character data you'd need to have them wrapped in a pair of single quotes.

between ''%HdrctlNbr1%'' and ''HdrctlNbr2%'' "

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

JeffT
Posting Yak Master

111 Posts

Posted - 2006-08-11 : 08:54:20

Yes, they are. Thanks !
J.

JeffT
Posting Yak Master

111 Posts

Posted - 2006-08-11 : 08:57:17

Thanks very much Druer, I will try that !
J.

JeffT
Posting Yak Master

111 Posts

Posted - 2006-08-11 : 09:18:25

I tried the pair of single quotes but that didn't work, so I just tried this and it did.
Thanks again to everyone, J.

set TableName="hdr_ctl_nbr_status"
set outfile="C:\BCP\hdr_ctl_nbr_status.txt"
If exist %outfile% del %outfile%
bcp "SELECT * FROM %GatewayDB%..%TableName% where hdr_ctl_nbr between "20060714000000000000" and "20060727999999999999"" queryout %outfile% -b1000 -c -t~ -U %DBUser% -P %DBPswd% -S%SQLServer%
REM pause

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources