Machine Syntax

This example shows how to delete top N rows in MSSQL, where N is a dynamic number. It could be 1000, 100, or 10. ;WITH CTE AS ( SELECT TOP 1000 * FROM [table_name] ORDER BY a1 ) DELETE FROM CTE 1000 is an example number. [table_name] is the table you want to delete a1 is just an example of sort by column a1 Thanks to stack overflow for this help : http://stackoverflow.com/questions/8955897/how-to-delete-the-top-1000-rows-from-a-table-using-sql-server-2008 If you want to delete all table data in the fastest way, you can use the Truncate Keyword.  The example Table name is: Analytics_Compaign TRUNCATE TABLE Analytics_Campaign; /* Reset ID to 1, normally primary key id is auto increment, so when you truncate table data, and when new data insert into database, the id will continue from what it left. This is how you reset the id to number 1 DBCC CHECKIDENT (<tablename>,RESEED,<number to set>)*/ DBCC CHECKIDENT (Analytics_Campa...

If you want to try to connect to database using sql authentication and the result is failed due to error code "18456" , here are the solution that you might want to try. I already solved the issue (in my case the problem because of the database did not set to allow sql connection to pass. So the solution is to enable the SQL Server authentication. Enable SQL Server Authentication  Login to the SQL Server using windows authentication. Right click on the SQL Server node >> properties. Go to Security tab. Under SQL Authentication choose SQL Server and Windows Authentication mode. Click Ok Button. Go to Services . Control Panel >> Administrative tools >> Services >> Find SQLServer instance. Restart SQL Server instance. Try to login again using your login information. Done. By Mohd Zulkamal NOTE : – If You have Found this post Helpful, I will appreciate if you can Share it on Facebook, Twitter and Other Social Media Sites. Thanks =)

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]View or function 'dbo.myviewname' has more column names specified than columns defined. The error occur because of the original table has changed the column. So that the view definition will affected and whenever you perforl select statement the error will occur. Solution Resave the view table and the problem solve. By Mohd Zulkamal NOTE : – If You have Found this post Helpful, I will appreciate if you can Share it on Facebook, Twitter and Other Social Media Sites. Thanks =)

This is a example to upload image and store into database. The example also show how to retrieve image from database. Note : Assume the web page have their own master page SQL Create Table SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[imageTest](     [ID] [int] IDENTITY(1,1) NOT NULL,     [Image] [image] NULL,  CONSTRAINT [PK_imageTest] PRIMARY KEY CLUSTERED (     [ID] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ASPX page Code     <asp:Label ID="Label1" runat="server" Text="Image Upload :"></asp:Label>     <asp:FileUpload ID="FileUpload1" runat="server" />     <br />     <asp:Button ID="Button1" runat="server" Text="Save" OnClick="Button1_Click" />     ...

One of the most disturbing aspect of most database applications is connection timeouts. Most of us might have faced this at one time or other. It would be helpful if we have complete control over the timeout and execution of our database queries/procedures. There are 3 different places that you need to check if you face a timeout in your application. Let us say, you have an application that runs a lengthy stored procedure and you started getting a timeout error. You tried to optimize the procedure to the maximum possible extend. At this stage, it is desirable to have a way to extend the timeout so that the user will see the results even after waiting for a little longer. Database Timeout Settings The first place that you need to check is your database server, to see if the server accepts long running queries/procedures. You can do this by checking the properties of the database. If you are using enterprise manager right click on the server name and select properties....

SQL Server Reporting Service(SSRS) is a full range of ready-to-use tools and services to help you create, deploy, and manage reports for your organization, as well as programming features that enable you to extend and customize your reporting functionality. The Report Viewer provide functionality to save the report as a pdf, word and also as a excel format. Sometime organization require this feature, sometime not. This is how to disable save as function in Report Viewer(SSRS). The example will invoke methode pre-render in Report Viewer Controller. The ASPX Page <%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %> <%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"     Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %> : : : <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat...

Brief Description The post will show code example how to upload CSV file data into Database and insert bulk record into SQL server database. Before we start the example, create the table in the database : SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[CSVTABLE](     [ID] [int] NOT NULL,     [NAME] [varchar](40) NOT NULL,     [ADDRESS] [varchar](80) NOT NULL,     [POSKOD] [varchar](15) NOT NULL,     [NOTEL] [varchar](15) NOT NULL,  CONSTRAINT [PK_CSVTABLE] PRIMARY KEY CLUSTERED (     [ID] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO After create the table on the database, now we can proceed on the code example. Let say the csv file like this : The CSV File 1,NAMA1,ADDRESS1,POSKOD1,NOTEL1 2,NAMA...

I got a request from a user and he wanted to count the number of tables in a database. It's quiet simple. Just use the information_schema.tables USE <db name> SELECT COUNT(*) from information_schema.tables WHERE table_type = 'base table' Will return you the count of the tables in the database The Tables The sql statement  USE SQLMembershipOptima SELECT COUNT(*) from information_schema.tables WHERE table_type = 'base table' The Output By Mohd Zulkamal NOTE : – If You have Found this post Helpful, I will appreciate if you can Share it on Facebook, Twitter and Other Social Media Sites. Thanks =)

This tutorial show how to read database using SqlDataReader in database using SqlClient driver to connect to database. Read here to read data in database and pass to DataTable Requirement in this tutorial are : using System.Data.SqlClient; using System.Data; The Example Read Data using SqlDataReader :             DataSet dataSetTable = new DataSet();             SqlConnection dbConn = new SqlConnection("<connection string>");             SqlCommand dbComm = new SqlCommand("<sql statement or procedure name>", dbConn);             SqlDataReader dbReader;             //set command type is procedure if you are using store procedure             //else remo...

This tutorial show how to read database and pass the value in DataTable or DataSet.  Requirement in this tutorial are : using System.Data.SqlClient; using System.Data; The sample code should be like this :             DataTable table = new DataTable();             SqlConnection dbConn = new SqlConnection("<connection string>");             SqlCommand dbComm = new SqlCommand("<sql statement or procedure name>", dbConn);                         //set command type is procedure if you are using store procedure             //else remove this line             dbComm.CommandType = CommandType.Stored...

SqlBulkCopy is a powerful tools that allowed you to update/ insert/ or delete table in database with a batch update. SqlBulkCopy class offer you to load data source as long as the data can be loaded as DataTable or read with iDataReader . Using SqlBulkCopy you can perform : Single update / insert /  delete operation into database; Multiple update / insert /  delete operation into database; Bulk Copy operation with transaction.  This tutorial only show single update / insert / delete operation using sql bulk copy . The C# Method Code using SqlBulkCopy         public void InsertUsingSqlBulkCopy(DataTable tableToUpdate, string tableName, ref string err)         {             using (SqlBulkCopy bulkCopy = new SqlBulkCopy(Configuration.LocalDatabaseConnectionString))             {    ...

Type conversion basically involve casting and convert from one type to another. You can try the Type conversion by following this web site . Today i want to share code to convert from Type to SqlDataType. This method can be used for example to add parameters to SqlCommand based on DataTable column DataType, see example below : SqlCommand dbComm = new SqlCommand("<sql statement>",<sql connection>); dbComm.Parameters.Add("<parameter name>", < sql Data Type>]).Value  = "test"; what about if you have DataTable and you want to automatically loop into column of datatable and and automatically assign Sql Data Type based on Column DataType of data table ..?There is nothing method or casting that available to do this, but after i googling about converting / casting DataType to SqlDataType, the result seem like impossible to done in .net . C# Parse SqlDbType Convertion C# data types to SQL Server data types   Convert DataColumn.DataType to Sq...