Access SQL Server using Public IP

If we have Public IP (100.200.105.205) we can access sql server over internet.

First we need to run the sql browser services, then, enable TCP/IP in sql configuration tool.

Images from
http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx



then we need to give the port number 1433


we need to add 1433 port in external firewall (if we using) and in windows firewall












After this ,
we can access sql server using public ip without instance name.

if we using OLEDB connection type, we need to give the port number also.
 it looks like xxx.xxx.xxx.xxx,1433




 

Sort Control in Crystal Reports 2008?

Source:
http://www.maximumimpactsolutions.co.uk/blog/comments.asp?bd=149

In Crystal Reports 2008, a new feature, the Sort Control, has been added
The Sort Control, not only allows the end-user to sort the report by a number of fields, but also in ascending and descending order.

There are two ways that the sort control can be added to a report:

• Directly added to the report, or
• Assigned to a field heading of the report

Solutions:

To add a sort control directly to the report:

1. Create the required report
2. From the report menu, select the Record Sort Expert option
3. In the Record Sort Expert dialog box, add the required fields for the sort controls:
   
   
4. Press the OK button
5. From the Insert menu, select the Sort Control option
6. In the Sort Control dialog box, select the required field:
   
   
7. Press the OK button
8. Click and drag on the in the required report section, to draw the Sort Control
9. Double Click on the sort conrtol text box, to enter the label for the conrtol
10. Click anywhere on the report to exit
11. Press the Up or Down sort control button to sort the report

To attach a Sort Control to a Report Field:

1. Create the required report
2. From the report menu, select the Record Sort Expert option
3. In the Record Sort Expert dialog box, add the required fields for the sort controls
4. Press the OK button
5. Right click on the field heading object, select the Bind Sort Control option
6. In the Sort Control dialog box, select the required field:
   
   
   
   
7. Press the OK button
8. Press the Up or Down sort control button to sort the report

Update in same table

update tempadd
  set ContactName =  x.ContactNAme
  from ( SELECT AddressID , ContactNAme From tempadd )x
  WHERE tempadd.AddressID=1 and x.AddressID=14

       
       
        UPDATE ra
SET ra.ContactName = rb.AddressLine1
FROM dbo.tempadd ra
INNER JOIN  dbo.tempadd rb
ON ra.AddressID = rb.AddressID
WHERE  rb.AddressID = 15

Save and Retrieve files from SQL SERVER 2008 with ASP.Net

Create a Table with columns look like ID,FileName,Extension,BinaryData(VarBinary(MAX))

CREATE PROCEDURE [dbo].[InsertDocument]
      (@FileName varchar(50),
      @Extension varchar(5),
      @FileContent varbinary(max))
AS
BEGIN
      DECLARE @ID INT
      SET @ID=(SELECT ISNULL(MAX(ID),0)+1 FROM File_T)
      INSERT INTO File_T (ID,FileName, Extension, BinaryData)
      Values (@ID,@FileName,@Extension,@FileContent);
END

in ASP.net coding,

<tr>
      <td>
               <asp:FileUpload ID="fileUploadDocument" runat="server" />
       </td>
       <td>
               <asp:Button ID="btnUpLoad" OnClick="btnUpLoad_Click" runat="server" Text="Upload"     CssClass="ButtonClass" />
               <asp:Button ID="btnDownLoad" OnClick="btnDownLoad_Click" runat="server" Text="Download" CssClass="ButtonClass" />
         </td>
 </tr>

protected void btnUpLoad_Click(object sender, EventArgs e)
    {
        try
        {

            if (fileUploadDocument.HasFile)
            {
                // Get the File name and Extension
                strFileName = Path.GetFileName(fileUploadDocument.PostedFile.FileName);
                strFileExtension = Path.GetExtension(fileUploadDocument.PostedFile.FileName);
                //
                // Extract the content of the Document into a Byte array
                int intlength = fileUploadDocument.PostedFile.ContentLength;
                Byte[] byteData = new Byte[intlength];
                fileUploadDocument.PostedFile.InputStream.Read(byteData, 0, intlength);
                //
                // Save the file to the DB
               // Call the stored procedure and pass the values.
                int i = PURBLL.Save(byteData, strFileExtension, strFileName);
                //
                //lblMsg.Text = "Document Uploaded Succesfully";
            }
        }
        catch (Exception ex)
        {
            //lblMsg.Text = " Error uploading Document: " + ex.Message.ToString();
        }
    }

File Retrieve


Create procedure as your wish to read binary data.
CREATE PROCEDURE FileDownload
AS
 BEGIN
   SELECT ID,FileName,Extension,BinaryData  FROM File_T  WHERE ID=4
 END
 

protected void btnDownLoad_Click(object sender, EventArgs e)
    {
        try
        {
            DataTable dt = PURBLL.FileDownload();
            DataRow DR = dt.Rows[0];
            Byte[] byteDoc ;
            byteDoc=(byte[])DR["BinaryData"];
            // Response.ContentType = "application/vnd.ms-word";
            //Response.ContentType = "application/vnd.ms-excel";
            //Response.ContentType = "application/pdf";
           Response.AddHeader("content-disposition", "attachment;filename=" + DR["FileName"].ToString() );
           Response.Cache.SetCacheability(HttpCacheability.NoCache);
           Response.BinaryWrite(byteDoc);
           Response.End();
        }
        catch (Exception ex)
        {
        }
    }

The upload file size limit is set to 4MB by default in ASP.NET

If we want to upload more than 4MB file we need to include the following line in web.config file under 
 <system.web>

<httpRuntime maxRequestLength="2097152" executionTimeout="9999999" />

For reference

http://www.codeproject.com/Tips/576395/Uploading-large-files-using-ASP-NET-and-IIS6-0 

  

Convert number to string in Crystal Report Formula

 ToText({Vend.Pin_Code},0,"")
Here
  Vend.PinCode is value from table row.

Insert excel data into MySql Table

1. First save the data in excel file in the .csv format.
2. For example execute a select query that returns more than 100 rows. And there is export option in result panel. Using that export as CSV file format.

From that file execute the below line to insert.
 
 LOAD DATA LOCAL INFILE 'C:\\temp\\dlyhdr.csv' INTO TABLE tt.dailystockheader
 FIELDS TERMINATED BY '\t'
 ENCLOSED BY '' LINES TERMINATED BY '\n' ;
 
Then select that table whether data has inserted or not.

Fail to access iis metabase

You need to install the .net framework on you system Open visual studio command prompt and type this command aspnet_regiis -ga <UserName> example aspnet_regiis -ga \aspnet if this doesn'twork than use this command aspnet_regiis -i   alternatively you can copy and paste this command into windows command prompt %windir%\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i   this will fix your problem