May 22nd, 2008, 10:56 AM
|
|
Contributing User
|
|
Join Date: Jan 2008
Posts: 122
Time spent in forums: 23 h 42 m 25 sec
Reputation Power: 1
|
|
|
Batch file: passing a string variable to sql script results in truncation
Hi, I have a batch file that takes variables from the cmd and passes them by sqlcmd to an SQL script. When I pass the variable 'MikeTestList' the script runs as expected with no problems. When I passed 'BulkTransferTest' I got an error message, 'The string or binary data will be truncated'.
Some testing has shown that I get the error when the string is anything above 12 characters. Is there a limit to the length of a string you can pass using sqlcmd from a batch file? Is there any way around it?
Also, if I have a space in the string, it only passes the string up to the space. I have tried to fill it with a '%' which then gets passed as is, so the % stays in place through out the script. Can you solve this too?!
Any help will be appreciated!
Code:
@REM BulkTransfer.bat
@REM ----------------------------------------------------------
@REM This batch file takes 5 parameters.
@REM 1 The server name
@REM 2 The Old Site Name
@REM 3 The New Site Name
@REM 4 The Termination Date of the old site.
@REM 5 The List containing patients to be transferred
:begin
@ECHO OFF
ECHO.
Sqlcmd -S "%1" -E -h -1 -Q "set nocount on select'You are about to transfer patients from ' +(SELECT rtrim(ltrim(PRACTICE_NAME)) FROM SITE_DETAILS WHERE ID =%2)+' to '+(select rtrim(ltrim(PRACTICE_NAME)) FROM EMISSYSTEM..SITE_DETAILS WHERE ID = %3)"
ECHO.
SET /P runscript="Do you wish to run the transfer script? (Y/N) "
if %runscript%==y goto run
if %runscript%==Y goto run
if %runscript%==n goto end
if %runscript%==N goto end
goto begin
:run
Set FileDate=%date:/=%
ECHO.
ECHO Log has been saved to %CD%
ECHO.
sqlcmd -S "%1" -v oldsite = "%2" -v newsite = "%3" -v terminationdate = "%4" -v oldlist = "%5" -E -i "BulkTransferScript.sql" -o "LogFile_%2_to_%3_%Filedate%.CSV"
:END
Last edited by acidedge2004 : May 22nd, 2008 at 11:13 AM.
|
Dev Shed Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
