sqlcmd-如何在没有空格的情况下绕过列长度限制? [英] sqlcmd - How to get around column length limit without empty spaces?

问题描述

我正在尝试在运行SQL Server 2005的Windows计算机上使用sqlcmd将查询写入csv文件.我们通常使用的命令行选项是:

I'm trying to use sqlcmd on a windows machine running SQL Server 2005 to write a query to a csv file. The command line options we typically use are:

-l 60 -t 300 -r 1 -b -W -h -1

但是，这些列将被截断为256个字节.为了避免这种情况，我尝试使用此命令行选项代替-W:

However, the columns are getting truncated at 256 bytes. In an attempt to circumvent this, I tried using this command line option in place of -W:

-y 8000

这捕获了整个字段，但是这种方法的问题是由于所有额外的空间，文件从1mb膨胀到200mb左右(我意识到8000可能会过大，但是可能必须在至少4000，而我目前仅处理一小部分数据).-W选项通常会消除所有这些多余的空间，但是当我尝试一起使用它们时，它会告诉我它们是互斥的.

This captures the entire fields, but the problem with this method is that the file balloons up from just over 1mb to about 200mb due to all the extra space (I realize 8000 is probably overkill, but it will probably have to be at least 4000 and I'm currently only working with a small subset of data). The -W option typically eliminates all this extra space, but when I try to use them together it tells me they're mutually exclusive.

是否有办法使sqlcmd达到此限制，或者有人知道其他程序(例如bcp或osql)是否会使此操作更容易?

Is there a way to get sqlcmd around this limit, or does anyone know if another program (such as bcp or osql) would make this easier?

这是我们用来获取被截断的字段的代码片段(类似的代码用于一堆字段):

Here are the code snippets we're using to get the field that's being truncated (similar code is used for a bunch of fields):

SELECT ALIASES.AliasList as complianceAliases,    

...

LEFT OUTER JOIN (Select M1.ID, M1.LIST_ID,stuff((SELECT '{|}' + isnull(Content2,'')+' '+isnull(Content3,'')+' '+isnull(Content4,'')+' '+isnull(Content5,'')+' '+isnull(Content6,'')+' '+isnull(Content7,'')
                                  FROM fs_HOST3_TEST_web.ISI_APP_COMP_MULTI M2 with (nolock)
                                  WHERE M1.LIST_ID = M2.LIST_ID and M1.ID = M2.ID and M1.TYPE = M2.TYPE                                      
                                  FOR XML PATH('')
                                  ),1,1,'') as AliasList
             FROM fs_HOST3_TEST_web.ISI_APP_COMP_MULTI M1 with (nolock)
             WHERE M1.LIST_ID = 2001 AND M1.TYPE = 'Aliases'
             GROUP BY m1.list_id,m1.ID,m1.Type) as ALIASES
             ON ALIASES.LIST_ID = PAIR.COMP_LIST_ID AND ALIASES.ID = PAIR.COMP_ID

推荐答案

我最终使用"-y0"参数解决了这个问题.它仍然留下了一堆空白，但看起来好像只到达了每个字段中最长的数据的末尾.

I ended up solving this by using the "-y0" argument. It still left a bunch of whitespace but it looks like it only went to the end of the longest piece of data in each field.

然后，我通过一个删除重复空间并解决所有问题的程序运行输出.

I then ran the output through a program that removed repeating spaces and that solved all of the problems.

这篇关于sqlcmd-如何在没有空格的情况下绕过列长度限制?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！