Feeds:
Posts
Comments

        In my recent project I need to identify all columns on which full text index is created. You can find that in management studio from Database – Storage – Full Text Catalogs and right click on catalog name and select property. It display all tables and columns as shown below,

Full Text Index

Fig – (1) All columns on which full-text index is created.

      An alternative is to use query to find all columns on which full-text index is created.

SELECT tbl.[Name] TableName,clm.[Name] ColumnName FROM
Sys.Tables tbl INNER JOIN Sys.Columns clm
ON tbl.[object_id] = clm.[object_id]
INNER JOIN sys.fulltext_index_columns fic
ON clm.[column_id] = fic.[column_id]
WHERE tbl.[Type] = ‘U’

Happy Programming !!!!

Chirag Darji

ASP.NET Consultant & Trainer

Advertisements

Today while working in SQL Server 2008, I encounter an interesting problem which does not allow me to change design of any table in particular database. Each time when I try to change table columns or resize the I receive following message,

Saving changes is not permitted. The changes you made require following tables to be dropped and re-created. You have either made changes to a table that can’t be recreated or enabled the option prevent saving changes that require the table to be re-created. 

blog1

Fig – (1) SQL Server 2008 – Saving changes is not permitted

         You will receive this message when you restore database created on other server. SQL Server 2008 by default prevent changes for database which are created on another server by enabling “Prevent saving changes that require table re-creation” from Tools – Options.

blog2

Fig – (2) Solution – SQL Server 2008 – Saving changes is not permitted

    To solve this go to Tools – Options and uncheck “Prevent saving changes that require table re-creation” option.

Happy Programming !!!

Chirag Darji

            While reading MSDN today I came across very interested topic. This is very use full specially in interview. Link shows maximum character allow in NVARCHAR(MAX), NTEXT, VARCHAR(MAX), Maximum cluster index per table, maximum columns per table, Maximum columns per Foreign Key, Maximum Columns in Group By and so on. Here is the link from MSDN OR you can read here copied from MSDN.

The following table specifies the maximum sizes and numbers of various objects defined in SQL Server databases or referenced in Transact-SQL statements.

SQL Server Database Engine object Maximum sizes/numbers SQL Server (32-bit) Maximum sizes/numbers SQL Server (64-bit)

Batch size1

65,536 * Network Packet Size

65,536 * Network Packet Size

Bytes per short string column

8,000

8,000

Bytes per GROUP BY, ORDER BY

8,060

8,060

Bytes per index key2

900

900

Bytes per foreign key

900

900

Bytes per primary key

900

900

Bytes per row8

8,060

8,060

Bytes in source text of a stored procedure

Lesser of batch size or 250 MB

Lesser of batch size or 250 MB

Bytes per varchar(max), varbinary(max), xml, text, or image column

2^31-1

2^31-1

Characters per ntext or nvarchar(max) column

2^30-1

2^30-1

Clustered indexes per table

1

1

Columns in GROUP BY, ORDER BY

Limited only by number of bytes

Limited only by number of bytes

Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement

10

10

Columns per index key7

16

16

Columns per foreign key

16

16

Columns per primary key

16

16

Columns per nonwide table

1,024

1,024

Columns per wide table

30,000

30,000

Columns per SELECT statement

4,096

4,096

Columns per INSERT statement

4096

4096

Connections per client

Maximum value of configured connections

Maximum value of configured connections

Database size

524,272 terabytes

524,272 terabytes

Databases per instance of SQL Server

32,767

32,767

Filegroups per database

32,767

32,767

Files per database

32,767

32,767

File size (data)

16 terabytes

16 terabytes

File size (log)

2 terabytes

2 terabytes

Foreign key table references per table4

253

253

Identifier length (in characters)

128

128

Instances per computer

50 instances on a stand-alone server for all SQL Server editions except for Workgroup. Workgroup supports a maximum of 16 instances per computer.

SQL Server supports 25 instances on a failover cluster.

50 instances on a stand-alone server.

25 instances on a failover cluster.

Length of a string containing SQL statements (batch size)1

65,536 * Network packet size

65,536 * Network packet size

Locks per connection

Maximum locks per server

Maximum locks per server

Locks per instance of SQL Server5

Up to 2,147,483,647

Limited only by memory

Nested stored procedure levels6

32

32

Nested subqueries

32

32

Nested trigger levels

32

32

Nonclustered indexes per table

999

999

Number of distinct expressions in the GROUP BY clause when any of the following are present: CUBE, ROLLUP, GROUPING SETS, WITH CUBE, WITH ROLLUP

32

32

Number of grouping sets generated by operators in the GROUP BY clause

4,096

4,096

Parameters per stored procedure

2,100

2,100

Parameters per user-defined function

2,100

2,100

REFERENCES per table

253

253

Rows per table

Limited by available storage

Limited by available storage

Tables per database3

Limited by number of objects in a database

Limited by number of objects in a database

Partitions per partitioned table or index

1,000

1,000

Statistics on non-indexed columns

30,000

30,000

Tables per SELECT statement

Limited only by available resources

Limited only by available resources

Triggers per table3

Limited by number of objects in a database

Limited by number of objects in a database

Columns per UPDATE statement (Wide Tables)

4096

4096

User connections

32,767

32,767

XML indexes

249

249

1Network Packet Size is the size of the tabular data stream (TDS) packets used to communicate between applications and the relational Database Engine. The default packet size is 4 KB, and is controlled by the network packet size configuration option.

2The maximum number of bytes in any index key cannot exceed 900 in SQL Server. You can define a key using variable-length columns whose maximum sizes add up to more than 900, provided no row is ever inserted with more than 900 bytes of data in those columns. In SQL Server, you can include nonkey columns in a nonclustered index to avoid the maximum index key size of 900 bytes.

3Database objects include objects such as tables, views, stored procedures, user-defined functions, triggers, rules, defaults, and constraints. The sum of the number of all objects in a database cannot exceed 2,147,483,647.

4Although a table can contain an unlimited number of FOREIGN KEY constraints, the recommended maximum is 253. Depending on the hardware configuration hosting SQL Server, specifying additional FOREIGN KEY constraints may be expensive for the query optimizer to process.

5This value is for static lock allocation. Dynamic locks are limited only by memory.

6If a stored procedure accesses more than 8 databases, or more than 2 databases in interleaving, you will receive an error.

7If the table contains one or more XML indexes, the clustering key of the user table is limited to 15 columns because the XML column is added to the clustering key of the primary XML index. In SQL Server, you can include nonkey columns in a nonclustered index to avoid the limitation of a maximum of 16 key columns. For more information, see Index with Included Columns [ http://msdn.microsoft.com/en-us/library/ms190806.aspx ] .

8 SQL Server supports row-overflow storage which enables variable length columns to be pushed off-row. Only a 24-byte root is stored in the main record for variable length columns pushed out of row; because of this, the effective row limit is higher than in previous releases of SQL Server. For more information, see the "Row-Overflow Data Exceeding 8 KB" topic in SQL Server Books Online.

The following table specifies the maximum sizes and numbers of various objects defined in SQL Server Replication.

SQL Server Replication object Maximum sizes/numbers SQL Server (32-bit) Maximum sizes/numbers SQL Server (64-bit)

Articles (merge publication)

256

256

Articles (snapshot or transactional publication)

32,767

32,767

Columns in a table1 (merge publication)

246

246

Columns in a table2 (SQL Server snapshot or transactional publication)

1,000

1,000

Columns in a table2 (Oracle snapshot or transactional publication)

995

995

Bytes for a column used in a row filter (merge publication)

1,024

1,024

Bytes for a column used in a row filter (snapshot or transactional publication)

8,000

8,000

1If row tracking is used for conflict detection (the default), the base table can include a maximum of 1,024 columns, but columns must be filtered from the article so that a maximum of 246 columns is published. If column tracking is used, the base table can include a maximum of 246 columns. For more information on the tracking level, see the "Tracking Level" section of How Merge Replication Detects and Resolves Conflicts [ http://msdn.microsoft.com/en-us/library/ms151749.aspx ] .

2The base table can include the maximum number of columns allowable in the publication database (1,024 for SQL Server), but columns must be filtered from the article if they exceed the maximum specified for the publication type.

Happy Programming !!!

Chirag Darji

        Today while working with JQuery-Ajax, I found an interesting error. I have used $.ajax() function to retrieve data from remote page. While testing I found that the page always show error : XML Parsing Error: no element found. Error does not contain specific information about the source of error. The common reason for XML Parsing Error: no element found is missing closing tag for one or two html element, so I double checked everything to make sure not miss any closing(</td> </tr>) tags.

        After searching for while I found that somehow ASP.NET treat the response of page as XML document and that’s why we receive XML Parsing Error: no element found error.

        To solve this error I added a line Response.ContentType = "text/HTML" to .cs page. This line tells ASP.NET runtime that response is HTML text and not XML.

 

Happy Programming!!!

           We all may have use JQuery in our application to build rich UI. While working with JQuery today I found that JQuery is not working on some pages where I have user mootool scripts. I search on net and found a very descriptive solution at JQuery site.

           According to this article the reason for JQuery conflict is the use of $ function. mootool and prototype.js files have their own $ function. So when we use $() and try to access JQuery (as it is shorten form of jQuery()) browser is not able to resolve which $() function it has to use. To avoid JQuery conflict we have to use jQuery.noConflict() function.

           While you read JQuery site article you may have observe the order of java script files added to web page.  Read carefully and you find that you have to add other libraries first and then add JQuery library to page. If you change the order and add the JQuery library first, either mootool(or other library which you have included) or JQuery will not work.

 

Happy Programming !!!

       We all know what JSON is and how it helps to build rich application. Recently during free time I was playing with JSON. It is easy to pass simple data types between server and client however its bit tricky to work with complex data types.

        Consider that we have Student class with FirstName, LastName and City properties. You can pass array of Student object from client to server using JSON.stringify() method. See the code,

function LoadAjax() 
{
    var t = [
             { FirstName: "Chirag", LastName: "Darji",City:"Ahmedabad" }, 
             { FirstName: "Chirag", LastName: "Darji",City:"Ahmedabad" } 
            ];
    $("#AjaxInfo")
        .load("http://chiragrdarji.blogspot.com", 
                { values: JSON.stringify(t) }
             );
            
}

Fig – (1) Passing complex data type using JSON

          Here we are passing array of two customer object from client to server using JSON. I have used JQUERY load method which use POST method to send the data to requested page. You can convert this parameter to Student class using following code,

var json = new DataContractJsonSerializer(typeof(List<Student>));
var stream = new MemoryStream(Encoding.UTF8.GetBytes(Request.Form["values"]));
List<Student> lstStudents = (List<Student>)json.ReadObject(stream);
stream.Close();

Fig – (2) Generating Lsit<Student> from JSON data type

 

Happy Programming!!!

        We have used FCK editor as an HTML editor for our recent project. It is one the best and easy to integrate HTML editor available free. When I test the editor on local server and it works fine. However when we add image using FCK editor the image path is relative to tour webpage. So if you have created an email template using FCK editor and you sends email to your customers they will not able see the image.

Set Full Image Path in FCK Editor

Fig – (1) Relative Image path in FCK editor

              We need to use full image path in FCK editor to display the image. You can change SetUrl function at following place to set full image path in FCK editor. You can find SetUrl function in fck_image.js fule at “FckEditor\editor\dialog\fck_image”. Below is the original function,

function SetUrl( url, width, height, alt )
{
    if ( sActualBrowser == 'Link' )
    {
        GetE('txtLnkUrl').value = url ;
        UpdatePreview() ;
    }
    else
    {
        GetE('txtUrl').value = url ;
        GetE('txtWidth').value = width ? width : '' ;
        GetE('txtHeight').value = height ? height : '' ;
 
        if ( alt )
            GetE('txtAlt').value = alt;
 
        UpdatePreview() ;
        UpdateOriginal( true ) ;
    }
 
    dialog.SetSelectedTab( 'Info' ) ;
}

Fig – (2) SetUrl function of FCK Editor

          Change GetE(‘txtUrl’).value = url, first line in else condition to GetE(‘txtUrl’).value = “http://www.xyz.com” + url. See the changed line below,

GetE('txtUrl').value = 'http://www.xyz.com' + url ;

Fig – (3) Changed function.

           Save fck_image.js  file and now check the image path. FCK editor will take full image path instead of relative.

Set Full Image Path in FCK Editor 2

Fig – (4) Full Image Path in FCK Editor

 

Set Full path for Flash File

            Same way you have to change fck_flash.js file at FckEditor\editor\dialog\fck_flash folder to set fill flash path in FCK Editor.

 

Happy Programming !!!