You cannot use variables in your ORDER BY for your column list or the ASC/DESC expression...I tested in 2005 and 2008.

Try it this way:

DECLARE @NEW_SEARCH_STRING VARCHAR(250),

@SORTFIELD...

Jeff Moden (4/14/2011)

---

I guess I'm confused. I see the XLM method beat all but one or two of the CLR's in most groups in your latest test results (which...

PS for the record I still think this task would be better off done in PowerShell using WMI (or sc) to avoid having to turn on xp_cmdshell.

Very nice, I completely missed that you were using sc 😀

I fixed up your results query to have it give the instance names instead of just rows with MSSQLSERVER which...

Ysaias Portes-483038, your solution implies he knows an instance he can log into...all he has are host names.

I agree with your comment about xp_cmdshell though...avoid it!

I feel your pain man...there is no official "standard" for CSV files...this is the closest thing out there but it's just an RFC: http://www.rfc-editor.org/rfc/rfc4180.txt

Your file conforms to section 2, unfortunately...

Using EXECUTE AS is the approach I have used but only when using a database user with no associated server login as a proxy. This article from Erland should get...

Using only T-SQL? Unfortunately there is no BULK EXPORT command to counter the BULK INSERT command. You're options are to call bcp using xp_cmdshell (yuck!) or move the process into...

Someone may correct me if they know of a better way in SSIS, but I think you're better off doing this work in T-SQL. Please read the article in my...

I ran into this same issue with SSIS but after lots of complaining amongst my team, having several experienced SSIS developers "take a shot" at the problem, as well as...

Please read this article, it explains it all: http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D

In addition to the DDL and DML please post the queries you have tried so far 🙂

Oh well...that's not something bcp can do with built-in functionality. You would have to do something with UNION ALL in your query to make the first row of your resultset...

Jeff Moden (4/13/2011)

---

It looks like the SQLCLRs are getting beat up a bit by straight T-SQL. I believe there may be an optimization on the DISTINCT XML example that...

Have you tried using bcp? bcp may prove to be faster than sqlcmd. bcp was designed to be fast at getting data from a file on disk into SQL Server,...

Ditto.