SQL SERVER – Several Readers Questions and Readers Answers

I often get questions on blog and many times I even get answers from readers as well. This article is collection of few of the questions and answers by readers of this blog.

Q. How the records of a table can be scripted in INSERT INTO statements?

A. In SQL Server 2008 : Right click Database > Tasks > Generate Scripts > In the wizard on Choose Script Option page, set Script Data option to True and complete the wizard.For SQL 2005 or earlier versions, use Database Publishing Wizard. For more details about Database Publishing wizard, please visit the blog https://blog.sqlauthority.com/2007/11/16/sql-server-2005-generate-script-with-data-from-database-database-publishing-wizard/

Q. How can I track the changes or identify the latest insert-update-delete from a table?

A. In SQL Server 2005 and earlier versions, there is no inbuilt functionality to know which row was recently changed and what were the changes. However, in SQL Server 2008, a new feature known as Change Data Capture (CDC) has been introduced to capture the changed data. To get more details about CDC, please visit https://blog.sqlauthority.com/2009/08/15/sql-server-introduction-to-change-data-capture-cdc-in-sql-server-2008/

In versions prior to 2008, the best method to track changes at the table level is Triggers and at the database level is SQL Profiler. Another workaround is to add one more column in the table like “ModifiedDate” to keep the current date-time of the change. Timestamp, rowversion and checksum columns are for optimistic concurrency. These are not suitable to track changes later.

Q. I am getting collation conflict error when joining columns of two tables?

A. When we try to compare, join or union two columns of different collations, the following error occurs: “Cannot resolve the collation conflict for equal to operation”.

Collation represents the character set of a data and can be specified at server, database and column levels. Collation of server is default for databases and collation of database is default for all columns. The collation is matched in following cases while executing a SELECT statement:

SELECT col1, col2
FROM Table1.col1 = Table2.col4
UNION
SELECT col5, col6
FROM Table3

Here make sure that collation of following columns should match
col1 = col5
col1 = col4
col2 = col6

To resolve this issue, you can either specify collation with column name in t-sql statement or change the collation of column permanently by altering the table, database or even server. For more details on how to specify collation in t-sql statement, please check following article: https://blog.sqlauthority.com/2007/06/11/sql-server-cannot-resolve-collation-conflict-for-equal-to-operation/

Sometimes, this error occurs while comparing the table with temporary tables. In this case, check the default collation of your working database and tempdb. If they do not match, then apply the above mentioned resolution.

Q. How can I get data from a database on another server?

A. If you want to import data only through t-sql query, then either use OPENDATASOURCE function. To repeatedly get data from another server, create a linked server and then use OPENQUERY function or use 4 part naming. If you are not adhered with T-SQL, then better use import/export wizard, and you can save it as a SSIS package for future use.

Q. My multilanguage data is not inserting correctly and returning as ???. How can I store Multilanguage data in a table?

A. There are two things to keep in mind while storing unicode data. First, the column must be of unicode data type (nchar, nvarchar, ntext). Second, the value must be prefixed with N while insertion. For example,

INSERT INTO table (Hindi_col) values (N’hindi data’)

I will post the second series of readers’ question and answers in future posts.

Reference: Pinal Dave (https://blog.sqlauthority.com)