export using bcp failing - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

export using bcp failing

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2011-09-14 : 03:01:45

It is something to do with this line which I need.

LEFT JOIN VC..VCSystemData ON EntryName = 'CurDBSchemaVersion'

If I use this code it fails in the bcp export.

When @import_type = 'UserDetails' Then 'exec master..xp_cmdshell ''bcp "Select * From VC..UserDetails LEFT JOIN VC..UserGroup ON VC..UserDetails.UserID = VC..UserGroup.UserID LEFT JOIN VC..GroupDetails ON VC..UserGroup.GroupID = VC..GroupDetails.GroupID LEFT JOIN VC..GroupPermissions ON VC..GroupDetails.GroupID = VC..GroupPermissions.GroupID LEFT JOIN VC..PermissionDetails ON VC..GroupPermissions.PermissionID = VC..PermissionDetails.PermissionID LEFT JOIN VC..GroupFeatures ON VC..GroupDetails.GroupID = VC..GroupFeatures.GroupID LEFT JOIN VC..FeatureDetails ON VC..GroupFeatures.FeatureID = VC..FeatureDetails.FeatureID LEFT JOIN VC..VCSystemData ON EntryName = ''CurDBSchemaVersion''" queryout "'+@file_name+'" -c -t, -T -S "'+@MachineName+'"'''

But if I run this in a query window it works?

Select * From VC..UserDetails LEFT JOIN VC..UserGroup ON VC..UserDetails.UserID = VC..UserGroup.UserID LEFT JOIN VC..GroupDetails ON VC..UserGroup.GroupID = VC..GroupDetails.GroupID LEFT JOIN VC..GroupPermissions ON VC..GroupDetails.GroupID = VC..GroupPermissions.GroupID LEFT JOIN VC..PermissionDetails ON VC..GroupPermissions.PermissionID = VC..PermissionDetails.PermissionID LEFT JOIN VC..GroupFeatures ON VC..GroupDetails.GroupID = VC..GroupFeatures.GroupID LEFT JOIN VC..FeatureDetails ON VC..GroupFeatures.FeatureID = VC..FeatureDetails.FeatureID LEFT JOIN VC..VCSystemData ON EntryName = 'CurDBSchemaVersion'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-14 : 03:38:33

that left join makes no sense as there's no relationship specified so its as good as CROSS JOIN with WHERE condition

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2011-09-14 : 04:04:10

What I am trying to do is add the column EntryName from VCSystemData where EntryName = 'CurDBSchemaVersion' to the query below. VCSystemData will only ever return one row.

Select * From VC..UserDetails LEFT JOIN VC..UserGroup ON VC..UserDetails.UserID = VC..UserGroup.UserID LEFT JOIN VC..GroupDetails ON VC..UserGroup.GroupID = VC..GroupDetails.GroupID LEFT JOIN VC..GroupPermissions ON VC..GroupDetails.GroupID = VC..GroupPermissions.GroupID LEFT JOIN VC..PermissionDetails ON VC..GroupPermissions.PermissionID = VC..PermissionDetails.PermissionID LEFT JOIN VC..GroupFeatures ON VC..GroupDetails.GroupID = VC..GroupFeatures.GroupID LEFT JOIN VC..FeatureDetails ON VC..GroupFeatures.FeatureID = VC..FeatureDetails.FeatureID

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2011-09-14 : 20:48:55

Any help of how to solve would be greatly appreciated. I don't know why this works when run as a seperate query but fails when run from bcp?

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2011-09-14 : 23:01:07

Any ideas??? VC..VCSystemData has no relationship (keys) with the other tables)

This works in a query.
Select EntryValue, FirstName, GroupID From VC..VCSystemData CROSS JOIN VC..UserDetails LEFT JOIN VC..UserGroup ON VC..UserDetails.UserID = VC..UserGroup.UserID WHERE VC..VCSystemData.EntryName = 'CurDBSchemaVersion'

This fails in bcp?
When @import_type = 'UserDetails' Then 'exec master..xp_cmdshell ''bcp "Select EntryValue, FirstName, GroupID From VC..VCSystemData CROSS JOIN VC..UserDetails LEFT JOIN VC..UserGroup ON VC..UserDetails.UserID = VC..UserGroup.UserID WHERE VC..VCSystemData.EntryName = ''CurDBSchemaVersion''" queryout "'+@file_name+'" -c -t"|" -T -S "'+@MachineName+'"'''

This is the output I am after from bcp.
EntryValue FirstName GroupID
107.00 Admin 3
107.00 Guest 3
107.00 VideoWall 3
107.00 VideoCentralGold 3
107.00 System NULL
107.00 a 3
107.00 o 4
107.00 Priority Control features not enabled 7
107.00 Vista 4

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources