[Fixed]How to overcome the linq contains limitation.Too many parameters were provided in this RPC request. the maximum is 2100

When we want to filter the data from the Linq to Sql query with the collection every one is trying to use the Contains() operator as follows. Linq to Sql will try to generate a sql query to represent the whole expression. This means that it will try to pass in the collection as parameters to a sql query. If there are too many parameters the query will hit the parameter limit (2100) and it cannot be executed. If there are not that many parameters, then you can use a contains expression which will be converted to an "IN" expression in sql. Sample code as follows

List<int> contactIds=Contact.GetContactIds()
//count of contactIds is morethan 2100
var contacts = Context.Contacts.Where(c => contactIds.Contains(c.ContactId)).ToList();

If the parameter limit exeeds then the following exception throws.

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.

To avoid this many developers will try to join the in memory collection with database table as follows. 

List<int> contactIds=Contact.GetContactIds()
//count of contactIds is morethan 2100
var contacts = Context.Contacts.Join(contactIds, c => c.ContactId, ci => ci, (c, ci) => c).ToList();

When developer trying this the following exception throws.

Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator.

This means that the linq to sql generates sql query and executes on the database server. Our in memory collection will not support to execute on the database server. so will get the above exception.
The easiest resolution is to convert the LINQ to SQL table into an IEnumerable list, which can be done as follows.

List<int> contactIds=Contact.GetContactIds()
//count of contactIds is morethan 2100
var contacts = Context.Contacts.AsEnumerable().Join(contactIds, c => c.ContactId, ci => ci, (c, ci) => c).ToList();

I hope this solution helps you.

Windows User Account Control (UAC) restrictions have been addressed

When you are working with the SQL Server Reporting services on windows server 2008 R2 and Window7 then you may encounter the following exception

User ‘Domain\User’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed


To overcome that issue we nee to set the User Account Control settings. The steps to set the User Access Control as follows

1. Open control panel
2. Click on System and Security
3. Click on User Account Control Settings

    4.  Slide down the User Account Control up to never notify. Then Click on Ok.

   5. Restart the system now.
   6. Open the Report server url in browser(Ex: http://systemname/Reports)

I hope this will helps to you.

Restoring SQL Server Database from .bak files

Restoring MSSQL Database from .bak files

1. Open SQL Server Management Studio on your local machine.
2. Right click the Databases folder. From the pop-up menu, select New Database.
3. Enter a database name, and then click Ok.
4. Right click the new database icon. From the pop-up menu, select Tasks -> Restore -> Database.
5. Select the From Device option, and then click the browse button.
6. Click Add and navigate to the appropriate file. Click Ok.
7. In the Restore Database window, select the checkbox next to your .bak file.
8. Switch to the Options page. Select the Overwrite the existing databasecheckbox.
9. Click Ok.
10. Verify the contents of your database, which is now active on your local machine.

Restoring SQL Server Database from .mdf files

Restoring MSSQL Database from .mdf files

1. Open SQL Server Management Studio on your local machine.
2. Right click the Databases folder. From the pop-up menu, select Attach.
3. Click Add and select the appropriate .mdf file. Click Ok, and then click Ok again.
4. Verify the contents of your database, which is now active on your local machine.

SQL Server 2008 Setup on Windows Fail With “The system cannot find thepath specified”

• Clean SQL 2008 components using the command line given at the top of the Summary.txt file:
  setup /q /action=uninstall /instanceid=MSSQLSERVERfeatures=SQLEngine,Replication,FullText
  OR
  Use Add\Remove Programs to clean the installed components.
• Do not remove 100 folder inside C:\Program Files\Microsoft SQL Server\..
• Now, run rsfx.msi file from ..\Installation folder\[x86] or [x64] or [ia64]\Setup folder. This will create the RsFxInstall folder and .ini files inside ..\100\Shared.
• Then start the slipstream setup again.

Sql Server 2008 Installation failed

If already sql server is installed in your system. if you want to upgrade or reinstalling fails
then follow the given steps

1.  Stop IIS Admin Service
2.  Stop IIS
3. Uninstall SQL Server previously installed.
4.  Start new installation

Query to count total number of stored procedures,tables and Views in database

When you want to count the total number of stored procedures in your database

the solution is

select count(*) as 'total' from sysobjects where xtype='P'

Note: where xtype='p' for stored procedures

xtype='V' for views

xtype='U' for tables

How to Generate the Scripts for Table with data in sql server 2011

Steps requied to generate Script in MS-Sql Server 2011:

1. Click on database. Right click on database and open context menu.Select Tasks > Generate Scripts... from displayed menu.


2. Microsoft SQL Server 2011 Generate and Publish Scripts wizard is displayed. Click on Next buton.


3. We are going to choose database objects that we want to create scripts for. If you want to script data of a single sql table, choose the "Select specific database objects" option. If you want to generate script for all database objects in the target database you can choose "Script entire database and all database objects". Click on Next button


4. Next we will configure scripts in detail and save or publish options.If you want to display the generated data script in the SQL Server Management Studio Query Editor window.


5. The most important configuration here in this screen is configuring the script wizard to script data. So please click Advanced buton. In the Advanced Scripting Options configuration screen, find Types of data to script parameter. The default selection is Schema only script type. In order to script data in table in SQL Server, you can either choose Data only script type or Schema and data script type.


6. After you completed your advanced scripting options selection, click OK buton. Then click Next buton. Click Next buton if everything is correct. Or click Previous buton to reconfigure generate script data options.


7. The Generate and Publish Scripts wizard, executes the task and displays each step and the result of the task execution.


8. Click Finish buton to close the SQL Server 2011 generate script wizard.

How to Generate the Scripts for Table with data in sql server 2008

MS SQL Server 2008 has new Generate Scripts option which enables sql programmers
to script data in SQL Server database tables. SQL developers can script data
from sql tables into a script file, to the clipboard or script data on a new sql
query window. Script data can be used to export and/or import table data from
one database to another database.

The Script Data option creates INSERT
statements foreach row in the table using the column data that the related table
record has. Later than the scripted table data can be used by executing the
generated t-sql scripts, to create a copy of the original table on an other
server or an other database with identical data or identical rows on the
destination database or table.

SQL Server generate script with data is a powerful SQL Server tool in order to
create sql script to move data from one database to another database.

Script Data option is new with Microsoft SQL Server 2008. So on the Tasks
context menu of a database although the Generate Scripts... option exists, we
won't be able to find the Script Data options in the Choose Script Options
screen of the Script Wizard.

In this article, I want to demonstrate with a sample how a sql developer can use
the Generate Scripts task in order to script table data of a SQL Server 2008
database table.

Open the Generate Scripts SubMenu Item from Task Menu

First of all, open the Microsoft SQL Server Management Studio which is installed
from the Client Tools of a MS SQL Server 2008 installation package.

Connect to a MS SQL Server 2008 database instance using the Server Explorer or using the
Connect screen.

Then open the Object Explorer window and expand the Databases
node.

Here I connected to the local SQL Server 2008 instance and clicked over
the Databases node and a list of existing sql server databases are visible in
the object explorer window now. Later, I clicked the sql database MyWorks which
owns the tables that I want to script data, rows/records of the database.

Continue by right clicking on the database name and open the context menu,
chooes Tasks menu and open submenu. Select Generate Script submenu item from the
displated list.

Tasks menu - Generate Scripts...

Generate SQL Server Script Wizards

When you select the Generate Scripts sub menu item the Generate SQL Server
Scripts Wizard starts. SQL administrators and sql programmers can use the Script
Wizard to generate t-sql scripts as a t-sql scripter to create scripts for any
object (tables, views, schemas, etc). You can work in detail on the Script
Wizard and find useful hint that you can benefit in your sql developments.

Generate SQL Server Scripts Wizard

Select Database to Script

The first step in the Script Wizard is detemining the database to work on. You
can choose a sql database among the listed all sql databases existing on the sql
server instance.

Script Wizard - Select Database Option

Choose Script Options

Here is the screen where sql developers can configure the script details, and
where developers can shape the automatic generated script according to the
applications needs and requirements.

For our case, since we want to script table data which exists in the database
that I have selected in the previous steps, we should set the Script Data option
to True. You can see that the Script Data option is listed in the Table/View
Options sections on the Choose Script Options screen. Since default Script Data
option is set to false by default, we should alter this option in order to get
an Insert statement for each row in the database table.

Note : Set Script Data option to True

Script Wizard - Choose Script Options

Choose Object Types

This option in the Script Wizard is for the types of the objects we want the
script generator to build scripts for. Since we deal with database tables, we
will select Tables among the listed options such as Schema and User-defined
table types.

Script Wizard - Choose Object Types

Choose Tables

Since we selected Tables in the previous step, the wizard now displays all the
tables that exists in the selected sql database. Here as a developer, I make a
list of tables that I want to generate table data scripts for. For our sample
case, I only select one table.

Script Wizard - Choose Tables

Output Option

Output Option screen in the Generate Script Wizard is the screen where a sql
administrator or a programmer can make a selection among the existing output
options. The script generator can create the desired scripts in the forms of a
file, also can split the automatic generated script per object basis, or define
the file as a unicode file or in ANSI text. A database developer can also select
the file name and the output file folder for the script engine to create and
place the script file.

Other output options are script to clipboard, just like a Copy-Paste operation
and the last option is displaying the generated script on a new query window in
the Microsoft SQL Server Management Studio.

I selected the New Query Window option in order to display the generated script
by the MS SQL Server 2008.

Script Wizard - Output Options

Script Wizard Summary

Here is the last stop before proceeding to the script generation where database
programmers can see which options they have selected and can go back to previous
screen and make re-selections in the options for a desired script.

Script Wizard Summary

Generate Script Progress

Generate Script Progress screen displays the status of the scripting operation.
If an error occurs sql developers and administrators can find the error details
on this screen. If everything runs without any error and does not fail, you will
see Success status for each scripting action on the progress screen.

Generate Script Progress

Data Script on the Query Window

Since as the output option for the scripting, the New Query Window is selected,
the final script is displayed on the SQL Server Management Studio Query Editor
window as shown below.

This script is formed of a CREATE TABLE script for the source table and
following that, INSERT statements for each row in the selected source table.

As you see, any sql developer or any database administrator can use the below
script to create a copy of any sql database tables with their data on another
sql database or an other sql server instance.

Sample Output to New Query Windows for Scripting Table Data

How to Generate the Scripts for Table with data in sql server 2005

This is the problem I faced when I want to change the schema and adding a new column to table which has lot of records. while adding a column it is showing timeout. I deleted the data from the table and added the column. But i want to insert the same data which has before delete.  Then I get a question that How i can generate scripts along with data in Sql Server 2005?. The sql server 2005 doesnot have generate script along with data facility.

Solution for SQL SERVER – 2005 – Create Script to Copy Database Schema and All The Objects – Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects:

1. First of all install Database Publishing Wizard from here : Download Database Publishing Wizard. It will be installed at following location : C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\


2. Now  goto Command prompt and run following command on any desire database, it will create the script at your specified location. Script will have schema as well as data which can be used to create the same information on new server.

Examples:

Command to run which will create schema and database:
C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz script -d AdventureWorks “C:\AdventureWorks.sql”

Command to run which will create schema:
C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz script -d AdventureWorks “C:\AdventureWorks.sql” -schemaonly

Command to run which will create data:
C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz script -d AdventureWorks “C:\AdventureWorks.sql” -dataonly

Note: I suggest that you try this on smaller database of size around 100MB.

Reference : Database Publishing Wizard