September 15, 2011 at 6:51 pm
Any ideas? I have tried many combinations of queries but all seem to fail when I add a where clause.
This works in a query window in SQL Management Studio;
Select v.EntryValue, FirstName, GroupID From VC..UserDetails LEFT JOIN VC..UserGroup ON VC..UserDetails.UserID = VC..UserGroup.UserID
CROSS JOIN
(Select EntryValue From VC..VCSystemData WHERE EntryName = 'CurDBSchemaVersion') v
EntryValueFirstNameGroupID
107.00Admin3
107.00Guest3
107.00VideoWall3
107.00VideoCentralGold3
107.00SystemNULL
107.00a3
107.00o4
107.00Priority Control features not enabled7
107.00Vista4
While the same query fails in bcp;
When @import_type = 'UserDetails' Then
'exec master..xp_cmdshell ''bcp "Select v.EntryValue, FirstName, GroupID From VC..UserDetails LEFT JOIN VC..UserGroup ON VC..UserDetails.UserID = VC..UserGroup.UserID
CROSS JOIN
(Select EntryValue From VC..VCSystemData WHERE EntryName = ''CurDBSchemaVersion'') v" queryout "'+@file_name+'" -c -t"|" -T -S "'+@MachineName+'"'''
Result;
C:\VCP\VCPDataRoot\VCPRS\Exports\UserDetails_20110916_100126.txt
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
exec master..xp_cmdshell 'bcp "Select v.EntryValue, FirstName, GroupID From VC..UserDetails LEFT JOIN VC..UserGroup ON VC..UserDetails.UserID = VC..UserGroup.UserID
CROSS JOIN
(Select EntryValue From VC..VCSystemData WHERE EntryName = 'CurDBSchemaVersion') v" queryout "C:\VCP\VCPDataRoot\VCPRS\Exports\UserDetails_20110916_100126.txt" -c -t"|" -T -S "ADE-LT-6WLK52S\XTRALIS_CYCLOPS"'
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'CurDBSchemaVersion'.
(1 row(s) affected)
September 16, 2011 at 6:19 am
Howdy,
It looks as though your issue is the number of single quotes around CurDBSchemaVersion
Try:
When @import_type = 'UserDetails' Then
'exec master..xp_cmdshell ''bcp "Select v.EntryValue, FirstName, GroupID From VC..UserDetails LEFT JOIN VC..UserGroup ON VC..UserDetails.UserID = VC..UserGroup.UserID
CROSS JOIN
(Select EntryValue From VC..VCSystemData WHERE EntryName = ''''CurDBSchemaVersion'''') v" queryout "'+@file_name+'" -c -t"|" -T -S "'+@MachineName
September 18, 2011 at 5:59 pm
Thanks for your response however I don't think that is the issue. I changed to this and received the following output.
When @import_type = 'UserDetails' Then
'exec master..xp_cmdshell ''bcp
"Select v.EntryValue, FirstName, GroupID From VC..UserDetails LEFT JOIN VC..UserGroup ON VC..UserDetails.UserID = VC..UserGroup.UserID
CROSS JOIN
(Select EntryValue From VC..VCSystemData WHERE EntryName = ''''CurDBSchemaVersion'''') v"
queryout "'+@file_name+'" -c -t"|" -T -S "'+@MachineName+'"'''
Results;
output
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL
Messages;
C:\VCP\VCPDataRoot\VCPRS\Exports\UserDetails_20110919_092658.txt
(1 row(s) affected)
(1 row(s) affected)
exec master..xp_cmdshell 'bcp
"Select v.EntryValue, FirstName, GroupID From VC..UserDetails LEFT JOIN VC..UserGroup ON VC..UserDetails.UserID = VC..UserGroup.UserID
CROSS JOIN
(Select EntryValue From VC..VCSystemData WHERE EntryName = ''CurDBSchemaVersion'') v"
queryout "C:\VCP\VCPDataRoot\VCPRS\Exports\UserDetails_20110919_092658.txt" -c -t"|" -T -S "ADE-LT-6WLK52S\XTRALIS_CYCLOPS"'
(12 row(s) affected)
(1 row(s) affected)
September 18, 2011 at 6:59 pm
Thanks mr_opto that was the issue with needing four single quotes. The second problem came about because you can not put this code on multiple lines. It has to be all on one line as it turns out 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply