-1

While generating the XML ,I am using XP-CMDSHELL along with BCP.My query character length is more than the 4000 characters . Bcp only considering 4000 characters. kindly suggest the way. Note : query is not going to convert in sp or view

jarlh
  • 42,561
  • 8
  • 45
  • 63
Vicky J
  • 1
  • 3
  • 2
    Can you please add your `BCP` script – Ven Feb 16 '18 at 09:18
  • If you need to run `bcp` why are you using *xp_cmdshell*? Just execute `bcp` from the command line or a SQL Server agent job. Besides, what kind of query requires 4000 characters? That's no longer bulk copying, that's data generation and exporting. You should probably create a view or stored procedure with that query. Better yet, use SSIS instead of bcp to export the data in any format you like. `BCP` is meant to do what the name says - bulk copying. It has limited formatting and no processing support – Panagiotis Kanavos Feb 16 '18 at 09:20
  • Do something else that doesn't generate queries of more than 4K, then. It's hard to suggest what, since you're light on the details of what you're trying to do, but there's almost certainly a better way. – Jeroen Mostert Feb 16 '18 at 09:20
  • You must show more of your current approach... One suggestion might be to store intermediate results in temporary tables to shorten the BCP query's code. – Shnugo Feb 16 '18 at 09:20
  • the current sql query has too many xml tags thats why my query is 6000 characters long. query is not going to reduces since it has too many validations. – Vicky J Feb 16 '18 at 09:28
  • @VickyJ XML tags in your query??? try to reduce this and poste an example. I'm pretty sure there's a better approach. – Shnugo Feb 16 '18 at 16:29

1 Answers1

0

You said "my query is 6000 characters long"

xp_cmdshell takes at most varchar(8000) or nvarchar(4000) so are already limited in command length.

If you need nvarchar then you have no option but to refactor.

If can use varchar, then you can at least pass the whole string via xp_cmdshell. Whether bcp accepts this, that's a different problem,,,

gbn
  • 422,506
  • 82
  • 585
  • 676