Truncating table on Linked server

I was trying to truncate a table from a linked server with a command like:
Truncate table [Linked_Server].[Database].[Schema].[TableName], but I was getting an error stating: "The object name [Linked_Server].[Database].[Schema].[TableName] contains more than the maximum number of prefixes. The maximum is 2."

The fix for his was to run the sql through sp_executesql stored procedure on the Linked server:

EXEC [Linked_Server].[Database].sys.sp_executesql N'Truncate table dbo.MyTable'

Importing data from one sqlite database to another

After some problems with Jano, a core data application for IOS, I had to take the data from an older database and put it in a new file. For this I started the Terminal application and typed sqlite, then hit Enter.

The first thing to do was to attach the 2 databases using the following command:

attach database "/Users/puiu/Documents/Jano.sqlite" as 'jano';

attach database "/Users/puiu/Documents/JanoNew.sqlite" as 'janonew';

Inserting the data was quite easy, using a simple SQL statement:

insert into janonew.zcounters(z_ent, z_opt, zisspecial, zordinal,zparent,zhiragana,zkanji,zromaji) 

select z_ent, z_opt, zisspecial, zordinal,zparent,zhiragana,zkanji,zromaji from jano.zcounters;

Setting page name in report

One of the advantages of using Reporting Services with SQL Server 2008 R2 or later is that you can get sheet names for Excel when exporting.
In my case I needed to export an excel file with different companies' names on the sheet name.

For this you first need to create a row group on the field you need. Then go to Row Groups, select the group like in the picture below:

Go to the properties of that group. You should see something like this:

Set the BreakLocation as you need it and the field name for the PageName.

Run your report and export it to excel. The sheet names will no longer be Sheet1, Sheet2, etc.

IIS error - Request entity too large

I had been working on a WCF web service lately that received the contents of a file as byte array and saved it in a database.

When calling the web-service I received the error "Request entity too large." Although the file was less than 200kb it was not able to process it.

The solution was to put the following tags inside the <system.serviceModel> tag in web.config.

<system.serviceModel>
                <bindings>
<basicHttpBinding>
<binding maxReceivedMessageSize="2097152"></binding>
</basicHttpBinding>
</bindings>
  </system.serviceModel>

Make field readonly with Javascript

In this post I will show an example of making a textbox that is bound to a model, read-only/editable when a user checks/unchecks a checkbox.

Our controls will start in the following state: the checkbox (chkIsReadOnly) is unchecked and the textbox is editable.

When the user clicks on the checkbox the following javascript function will be launched:

function changeState() {
            var checkValue = document.getElementById("chkIsReadOnly").checked;
            var userName= document.getElementById("userName");

            if (!checkValue) {
               //the field becomes readonly since the checkbox is checked
                userName.readOnly = false;
                userName.removeAttribute('readonly');
                userName.removeAttribute('disabled');
            }
            else {

                userName.readOnly = true;
                userName.setAttribute('readonly', true);
                userName.setAttribute('disabled', true);
                userName.value = '';
            }

        };

The checkbox is defined like this:

<input
                            type="checkbox"
                            name="chkIsReadOnly"
                            id="chkIsReadOnly"
                            onclick="changeState()"
  />

In order for the javascript function to work we must use the HTML helper TextBoxFor instead of EditorFor

@Html.TextBoxFor(model => model.userName, new { disabled = "disabled", @readonly = "readonly" })

Default GETDATE() contraint for column and LINQ in Entity Framework 5.0

If we have a datetime column that we want to set the default to the current date and time we can use

alter table MyTable add constraint DefGetDate DEFAULT GETDATE() FOR myColumn


In edmx, select the column from the entity and set the StoreGeneratedPatern to Computed or Identity.

The available values for StoreGeneratedPatern are:

• None - A value indicating that it is not a server generated property. This is the default value.
• Identity - A value is generated on insert and remains unchanged on update.
• Computed - A value is generated on both insert and update.

The MSDN page regarding this is http://msdn.microsoft.com/en-us/library/system.data.metadata.edm.storegeneratedpattern.aspx

Render file from ReportViewer connected to Reporting Services

In this post I will show how you can use a report viewer control to connect to a Reporting Services server and call the Render function on the ReportViewer.
This is useful when we don't want to show the control, but we need to get the rendered report in different formats.

Step 1: download the Report viewer runtime from here.

Step 2: Install the runtime.

Step 3: Add the reference to your project. The dll should be in \Program Files (x86)\Microsoft Visual Studio 11.0\ReportViewer\Microsoft.ReportViewer.WinForms.dll

First we create a method that will return the report from the report viewer as an array of bytes.

public byte[] RenderFromServer()
{
ReportViewer rv = new ReportViewer();

// Set the processing mode for the ReportViewer to Remote
rv.ProcessingMode = ProcessingMode.Remote;

rv.ServerReport.ReportServerUrl = new Uri("Http://mypc/ReportServer);
rv.ServerReport.DisplayName = "New report.pdf";
rv.ServerReport.ReportPath = "/Reports/MyReport";

//set credentials
ReportServerCredentials rsCredentials = rv.ServerReport.ReportServerCredentials;

NetworkCredential nc = new NetworkCredential();
nc.Domain = "myDomain"; //can also be the computer name for local access
nc.UserName = "myUser";
nc.Password = "12333";

rsCredentials.NetworkCredentials = nc;

//set parameters
List<ReportParameter> paramLst = new List<ReportParameter>();
ReportParameter rp = new ReportParameter(ParamName, ParamValue);
paramLst.Add(rp);
                        rv.ServerReport.SetParameters(paramLst);

//get the bytes from the report viewer
Warning[] warnings;
string[] streamids;
string mimeType;
string encoding;
string extension;

byte[] bytes = rv.ServerReport.Render
("PDF", null, out mimeType, out encoding, out extension, out streamids, out warnings);

rv.Dispose(); //very important for memory management!!
return bytes;
}

Notice that I called the Render function with the "PDF" paramater. The full list can be found at http://msdn.microsoft.com/en-us/library/ms154606.aspx.

The possible values are:

• PDF
• CSV
• IMAGE
• PDF
• EXCEL
• WORD
• HTML 4.0
• MHTML

When exporting to MHTML and you need to embed the report in another html document, or the body of an email, you have to specify the DeviceInfo details:

<DeviceInfo>
   <HTMLFragment>True</HTMLFragment>
</DeviceInfo>

As stated in MSDN, "When a report is rendered as an HTML fragment, the content of the report is contained within a TABLE element without the use of an HTML or BODY element. You can use the HTML fragment to incorporate the report into an existing HTML document."

Link: http://technet.microsoft.com/en-us/library/ms155397.aspx

Then we can write the bytes to a file:

byte[] bytes = RenderFromServer();
File.WriteAllBytes("C:\\MyReport.pdf", bytes);

Or, make a memory stream from them:

byte[] bytes = RenderFromServer();
MemoryStream ms = new MemoryStream(bytes);
ms.Seek(0, SeekOrigin.Begin);