Use xp_cmdshell and BCP to Export SQL Server data to CSV

This article describes how to use xp_cmdshell and BCP to export data to CSV.  One problem I ran into was the error “Unable to open BCP host data-file”.  This turned out to be a file permission issue.  I had to give NETWORK SERVICE (the local account that runs my SQL Server service) Full Access rights to the queryout directory.  I also had to use the –S flag to specify which SQL Instance (my server has two) to connect to:

 

EXEC xp_cmdshell 'bcp "SELECT * FROM <my database>.dbo.account" queryout "D:\queryout\bcptest.txt" -T -S "<my server>\<my instance>" -c -t,'

 

 

Get a list of all the tables in a SQL 2005/2008 database

I thought this SQL 2005/2008 tip was very cool.  This gets a list of all the tables in my database:

 

SELECT [name] FROM <my database>.sys.tables;

 

Grant Permission for SQL User for Membership Provider using Database Role

The SQL User connecting to your Membershp Provider database (default named aspnetdb) should have the following database role:

aspnet_Membership_FullAccess

 

Otherwise you will get the following error:

EXECUTE permission denied on object 'aspnet_CheckSchemaVersion', database 'aspnetdb', schema 'dbo'.

Connection String for Membership Provider using a Trusted Connection over TCP/IP on a specific Port

<add name="LocalSqlServer"

  connectionString="Data Source=192.0.0.1,2116;

                    Network Library=DBMSSOCN;

                    Initial Catalog=aspnetdb;

                    Integrated Security=SSPI;"/>

 

Using data binding with Eval to set TemplateField button Visible property

I needed to conditionally hide a delete button in a GridView. This data binding expression did the trick:

<asp:ImageButton ID="ImageButton1" runat="server" CausesValidation="False"
    CommandName="Delete" ImageUrl="~/_Images/cross.png" Text="Delete"
    Visible='<%# ((string)Eval("statuscodeLabel") == "Draft") %>' />

I also ran across this little beauty that calls a DAL class to do a check against the database (the data is hopefully cached):

<asp:HyperLink ID="lnkCert" runat="server" 
    Visible='<%# SomeDAL.UserCanDoSomething(CType(Container.DataItem, System.Data.DataRowView).Item("Code").ToString()) %>'
    NavigateUrl='<%# Eval("ID", "~/forms/AgencyForm.aspx?pid={0}&mode=cert") %>'>
    <img src="../images/tick.png" alt="Certify Agency Data" /></asp:HyperLink>

Pride goeth before a fall

I assure you...this did not end well.

Using CRM Advanced Developer Extensions to Add Attachment to Contact

This code sample is straight from MSDN, but it is so often used, I wanted to make sure I kept it handy:

 

// Add a note with a document attachment to the contact's record.

var attachment = File.OpenRead("capture.png");

var data = new byte[attachment.Length];

attachment.Read(data, 0, (int)attachment.Length);

 

var note = new Xrm.annotation()

{

  subject = "Note subject...",

  notetext = "Note Details....",

  Contact_Annotation_id = contact.contactid,

  objecttypecode = "contact",

  documentbody = Convert.ToBase64String(data),

  filename = Path.GetFileName(attachment.Name),

  mimetype = "image/png"

};

crm.AddToannotations(note);

crm.SaveChanges();