I have created a SQL script in SQL Server 2005 - a query which generates its results in XML format. I want to be able to automate the process of running the script and saving the query results as an XML file. I've seen where others have used BCP to do this, but I'm under the impression that requires saving the sa username and password in the procedure call... I've also read that it is preferable to create the file using Visual Studio to create a CLR stored procedure. I have some (limited) experience with Visual Basic.NET in older versions of Visual Studio, so I figured I'd try my hand at this technique. Unfortunately, the version of Visual Studio I have here is 2005 Standard, and I'm learning that the SQL Server integration support is limited unless you have the Professional edition. For example, System.Data.SqlServer apparently doesn't exist. For that matter, 'SQL Server Project' isn't even among the new project choices...
I found a cool article (http://www.sqldbatips.com/showarticle.asp?ID=23
) that seems to describe what I would want to do, but it doesn't work with 2005 Standard.
My question then is, can I accomplish with Visual Studio 2005 Standard what would normally be done with Visual Studio 2005 Professional?