Category: SQL-General

TSEQUAL and JOIN syntax as *= or =* not supported in SQL Server 2008

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, Varinder Sandhu

I have faced an issue during SQL Server Migration. Here sharing with you. As I need to move some databases from SQL Server 2000 to SQL Server 2008 then I faced a problem because TSEQUAL and JOIN syntax such as *= or =* not supported in SQL Server 2008. Some of scripts are using the function and syntax as Example: TSEQUAL (TxnStatus, @ciTxnSts) JOIN syntax which has *= and =* to specify the OUTER JOIN. Solution: TxnStatus = @ciTxnSts instead of 

» Read more
, , ,

SQL Server – Table-Valued Parameters

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Stored Procedure, SQL-Tips and Tricks, Varinder Sandhu

Table-valued parameters are a new parameter type introduced in SQL Server 2008. With the help of table-valued parameters, you can send multiple rows of data to a stored procedure or function, without creating a temporary table or many parameters. Steps to create Table-Valued Parameters Create a table type and define the table structure. Declare a stored procedure or function that has a parameter of the table type. Declare a variable of the table type, and reference the table type. Fill

» Read more
, , ,

SQL Server – Inside of Next Generation SQL Server – Denali

SQL, SQL-Advanced, SQL-General, SQL-Server 2011, SQL-Tips and Tricks, Varinder Sandhu

Today I have attended the webcast topic “Inside of Next Generation SQL Server – Denali” at Zeollar.com. This session presented by Pinal Dave (http://blog.sqlauthority.com) It was really a nice presentation about features of SQL Server 2011 – Denali.  You can get the details of this session at here You can watch or download the video at http://zeollar.cloudapp.net/Session/234

» Read more
, , ,

SQL Server – Local temporary table vs. Global temporary table

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Temporary tables are a useful feature provided by SQL Server. Temporary tables created at runtime and can do all kinds of operations that one normal table can do. Because this is available at runtime, that’s why the scope of temporary tables is limited. These tables are created inside the tempdb database. There are two types of Temporary tables. Local Temporary table Global Temporary table Local temporary table: Local temporary tables are available to the current connection or sessions to the

» Read more
, , , , ,

SQL Server – User Defined Functions vs. Stored Procedures

SQL, SQL-Functions(UDF), SQL-General, SQL-Stored Procedure, SQL-Tips and Tricks, Varinder Sandhu

SQL Server user-defined functions and stored procedures have almost similar functionality. Both allow you to create a batch of SQL statements to execute on SQL server. Benefits: We can reuse the code from one program to another, this way we can increase the productivity. Centralize control, to make business logic changes in a single place that automatically affect all dependent applications. Difference between User Defined Functions and Stored Procedures   Stored procedures are called independently, using the EXEC command. Example: EXEC dbo.procedure_name ‘Parameter1’Functions are

» Read more
, , , , ,

SQL Server – Database Read Only

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Here is simple script with you can set your database in Read-Only Mode Once you specify database is in read-only mode then users can read data from the database, not modify it. Here is Script : Mark Database Read Only USE [master] GO ALTER DATABASE database_name SET READ_ONLY WITH NO_WAIT Similarly you can change database Read-Only Mode to Read-Write Here is script : Mark Data Read- Write Mode USE [master] GO ALTER DATABASE database_name SET READ_WRITE WITH NO_WAIT GO Alternative

» Read more
, , , ,

SQL Server – Management Studio – Add an External Tool to the Tools Menu

SQL, SQL-Advanced, SQL-General, SQL-Tips and Tricks, Varinder Sandhu

We can launch any Microsoft Windows or Microsoft .NET Framework application from SQL Server Management Studio. External applications can be added to, and run from, the Tools menu. To add an external tool to the Tools menu On the Tools menu, click External Tools. In the Title text box, type the name you want to appear in the Menu contents list. In the Command text box, type the program name. Include the path to the executable file if necessary. In the Arguments text

» Read more
, , , ,

SQL Server – Use of Cursors

SQL, SQL-Advanced, SQL-Cursor, SQL-General, SQL-Tips and Tricks, Varinder Sandhu

I have seen many different reasons and point of view on the use of cursors. Some people never used, some used at last resort and some used regularly. In each of these, they must have different reasons for their stand on cursor usage. There must have a reason or place where we can use cursors in an efficient manner. Cursors are a bad choice, unless you understand enough about them to justify their use in limited circumstances. Cursors have their

» Read more
, , , ,

SQL Server – Import CSV file to SQL table – Bulk-Insert

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Here is an example –  Import CSV file to SQL table using Bulk-Insert CSV file means  Comma Separated Values file. USE testdb GO CREATE TABLE table1 ( id INT, name VARCHAR(255), address VARCHAR(255) ) GO Create CSV file in Shared folder on any remote location with name csv.txt Suppose location of the file is \\dcc-566\Share\CSV.txt Content of CSV file as : Now let‘s Import this CSV file into Table1. BULK INSERT table1 FROM '\\dcc-566\Share\CSV.txt' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR

» Read more
, , , , ,

SQL Server – Comma Separated INSERT Option

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

  Here is new feature of SQL Server 2008 – “Comma Separated INSERT Option” In this feature using a single INSERT statement you can pass multiple value for the table using comma separated option. USE testdb GO CREATE TABLE table1 ( id INT IDENTITY(1,1), name VARCHAR(255), address VARCHAR(255) ) SELECT * FROM testdb..table1 INSERT INTO testdb..table1 VALUES (‘Varinder’,’ABC Street’), (‘Amit’, ‘CDE Street’), (‘Dinesh’,’XYZ Street’) Result :    

» Read more
, , , , ,

SQL Server – Insert same value multiple time in SQL table

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

  Here is an example:  Inserting same value multiple time in SQL table USE testdb GO CREATE TABLE table1 ( id INT IDENTITY(1,1), name VARCHAR(255), address VARCHAR(255) ) SELECT * FROM testdb..table1 INSERT INTO testdb..table1 VALUES (‘Varinder’,’ABC Street’) go 5 Result :  

» Read more
, , , ,

SQL Server – Policy Based Management

SQL, SQL-Advanced, SQL-General, Varinder Sandhu

SQL Server 2008 has a new feature called Policy Based Management, also know as Declarative Management Framework (DMF), to tackle the problem of standardizing your SQL server instance. Although Policy Based Management can be used just to alert an administrator when an object is out of compliance, depending upon the type of policy, you can also enforce compliance by preventing changes that would violate a policy. Policy Based Management introduces the following new objects that are used to design and

» Read more
, ,
1 2 3 4 5 6