Solutions For Errors

Gives solution for any Error occurs in any Programming Language, .Net Core 3.1, 3.0, C#,Asp.Net,Sqlserver,MVC,Java,Php,Html,Css,Jquery,errors and solutions,Latest News,Technology

Showing posts with label Sql Server. Show all posts

Thursday, 29 September 2016

What is Temporary Tables and Table Variables Differences in Sql Server

No comments :
Temporary Tables:
Temporary Tables are tables that are temporarily created for a particular session. Once the session is terminated, the temporary tables are automatically deleted. In other words, these are the physical tables, which are created in "tempdb" database in SQL Server.
Table Variables:
Table variables are laid out like tables. They are partially stored both in the memory and in disk.
Array Of Differences Between Temp tables and Table Variables in SQL Server
In this section, we have listed the major differences between Temporary Tables and Table Variables. They are
1. Syntax:
The syntax for creating Temporary Table and Table Variable differs largely.
Temporary Table
-- Create Temporary Table
CREATE TABLE #Student
(Id INT, Name VARCHAR(50))
--Insert Two records
INSERT INTO #Student
VALUES(1,'Max')
INSERT INTO #Student
VALUES(2,'Clark')
--Retrieve the records
SELECT * FROM #Student
--DROP Temporary Table
DROP TABLE #Student
GO
Table Variable
-- Create Table Variable
DECLARE@Student TABLE
(
 Id INT,
 Name VARCHAR(50) 
)
--Insert Two records
INSERT INTO@Student
VALUES(1,'Max')
INSERT INTO@Student
VALUES(2,'Clarks')
--Retrieve the records
SELECT* FROM@Student
GO
2. Types
Temporary Table
There are mainly two types of Temporary Tables-Local & Global Temporary Tables.
•    Local Temporary Table: These tables are only available for the session that has created them. Once the session is terminated, these tables are automatically deleted. They can be also be deleted explicitly.
•    Global Temporary Table: These tables are available for all the sessions and users. They are not deleted until the last session using them is terminated. Similar to local Temporary Table, a user can delete them explicitly.
Table Variable
They can be declared in batch or stored procedure. Unlike Temporary Tables, they cannot be dropped explicitly. Once the batch execution is finished, the Table Variables are dropped automatically.
3. Storage Location of a Temporary Table
The temporary tables are stored in tempdb database of SQL server.
Table Variable
The Table Variables are stored in both the memory and the disk in the tempdb database.
4. Structure Modification
Temporary Table
The structure of Temporary Tables can be created even after its creation. Thus, we can use DDL statements like ALTER, DROP and CREATE as shown in the below-mentioned example. In the example we have created a Temporary Table named as Employee. In this we will add an Address column and then finally drop the table.
--Create Temporary Table
   CREATE TABLE#Student
   (Id INT, Name VARCHAR(50))
   GO
   --Add Address Column
   ALTER TABLE#Student
   ADD Address VARCHAR(400)
   GO
   --DROP Temporary Table
   DROP TABLE#Student
   GO
Table Variable
The structure of Table Variables cannot be changed once they are created. Thus, it means that DDL commands cannot be run in Table Variables.
5. User Defined Functions
Temporary Table
They are not allowed in the user-defined functions.
Table Variable
The table variables can be used in user-defined functions.
6. Transactions
Temporary Table
They support the explicit transactions that are defined by the user.
Table Variable
They do not participate in the transactions that have been explicitly defined by the user.
7. Indexes
Temporary Table
Local and Global Temporary Tables support creation of indexes on them in order to increase the performance.
Table Variable
Table Variables do not allow creation of indexes on them.
8. Locking
Temporary Tables
Since the Temporary Tables are physical tables, while reading from the table, SQL Optimizer puts a read lock on the table.
Table Variable
Since the Table Variables are partially stored in the memory, they cannot be accessed by any other user or process that the current user. Therefore, no read lock is put on the Table Variable.
Conclusion
The Temporary Table and Table Variables are both strong and weak at the same time. Both the Table Variables and Temporary Variables are extremely useful for developers and administrators. The users need to choose the correct solution according to the situation.

Thursday, 30 June 2016

Solution for Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine error in MSSql Software

No comments :
Here is the explanation of  Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine error message appears while import Microsoft Excel data into SqlServer database software.
using operating system Windows 8/7.

Solution:

Install this software Microsoft  2007 office system driver : Data connectivity components

After downloading completed install software and check if error comes or not.

How to copy table structure and data from one table to another table in same database or different in MSSql Server

No comments :
Here is the explanation of copy database table data and structure from one table to another in sql server 2008/2008 R2/2010/2012.

Procedure:
We have list of data in this Country table.
select * from Country

Method 1:
If you want to copy this entire data with structure (field names and datatypes) to another table with the different name using below queries.
Syntax:
Select * into newtablename from oldtablename

Ex: Select * into CountryNames from Country

You want to copy only specific data into other table using below format.

Syntax:
Select  col1,col2 into newtablename from oldtablename

Ex: Select CountryId,CountryName into CountryNames from Country
Method 2:
Syntax:
Insert into newtablename Select * from oldtablename
Ex: Insert into CountryNames Select * from Country

For particular columns

Insert into newtablename (col1,col2,col3) Select col1,col2,col3 from oldtablename

Thursday, 7 April 2016

How to move/transfer database from Sql Server 2012 to 2008 R2,2008,2010

No comments :
I will explained about how to restore/move/transfer entire database from Sql Server 2012 to Sql Server 2008 R2, Sql Server 2008 and Sql Server 2010.
Steps to Transfer:

Right click on your Database
Choose Tasks option. choose scripts
Click on next  -> next choose Advanced tab 

Wednesday, 2 December 2015

How to execute stored procudure With an output Parameter in C# and Sql Server

No comments :
CREATE PROCEDURE SAMLE @input  VARCHAR(20),
                       @output VARCHAR(20) output
AS
  BEGIN
      IF @input >= '1'
        BEGIN
            SET @output = 'HI';

            RETURN;
        END
  END
Executtion
DECLARE @get VARCHAR(20);
EXEC testme-Procedure Name
  '1',
  @get output

SELECT @get 
C# Code:
SqlCommand cmdStoredProcedure = new SqlCommand();
cmdStoredProcedure.CommandText = "procedure name";
cmdStoredProcedure.CommandType = CommandType.StoredProcedure;
cmdStoredProcedure.Parameters.Add("parmater1", SqlDbType.VarChar, 50);
cmdStoredProcedure.Parameters.Add("parmater2", SqlDbType.VarChar, 500).Direction = ParameterDirection.Output;
// set parameter values
cmdStoredProcedure.Parameters["paramter"].Value = "1";
cmdStoredProcedure = SchedulerJobBLL.ExecuteSQLSP(connectionstring, cmdStoredProcedure);
cmd.Parameters["paramter2"].Value.ToString();