0

Can someone show me the correct way to structure this command?

FYI - I think this boils down to an escape issue. There are several posts close but I have yet to find a solution which will run as a command (example)

This command queries the db to create a file based on the results.

bcp \
"SELECT * FROM [database].[dbo].[table] CAST(timestamp as DATE) = '2015-11-01'" \
queryout /tmp/2015-11-01.txt \
-S server \
-U user \
-P pwd \
-c 

I want to script this for, say, each day in a month.

for i in 01 02 03

do

bcp \
"\"SELECT * FROM [database].[dbo].[table] where CAST(timestamp_field as  DATE) = '2015-11-${i}';"\"
queryout /tmp/2015-11-${i}.txt \
-S server \
-U user \
-P pwd \
-c 

done

The output from bash shows that it is being modified/escaped

bash -x ./command.sh
bcp '"SELECT * FROM [database].[dbo].[table] where CAST(timestamp_field as DATE) = '\''2015-11-01'\'';"' ....

This modified sql is still passed to sql server but is nonsensical and gets no rows. bcp will only accept a double quoted string exactly as shown below (including double and single quotes):

"SELECT * FROM [database].[dbo].[table] where CAST(timestamp_field as DATE) = '2015-11-01';"

Can this be done ?

Thanks for any help you can give. TD

0

You're overthinking the problem. bcp expects a string as its first parameter that describes either a table or a select... statement. As usual the shell strips the outmost quotes away, and because they're double quotes "..." it interpolates the value of any shell variables it finds.

for i in 01 02 03
do
    bcp "SELECT * FROM [database].[dbo].[table] where CAST(timestamp_field as DATE) = '2015-11-${i}'" \
        queryout /tmp/2015-11-${i}.txt \
        -S server -U user -P pwd -c
done

and similarly

test -z "$odbc_dsn" && read -p 'ODBC DSN: ' odbc_dsn
test -z "$username" && read -p 'Username: ' username
test -z "$password" && read -sp 'Password: ' password

for tb in databases all_columns all_views
do
    bcp "master.sys.$tb" out "$tb.txt" -c -D -S "$odbc_dsn" -U "$username" -P "$password"
    echo "Table $tb"
    sed 's/^/|   /' "$tb.txt"
    echo
done

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.