Knowledge Summary (SQLServer)

Source: Internet
Author: User
1) The difference between nvarchar () and nchar () in the database is used in the SSIS project to associate with nvarchar () of the database Field Type nchar, but it cannot be associated all the time, and there is no problem when querying in the database. Later I will see that there are many null values after nchar (), and there will still be no space after update, followed by nchar () type changed to nvarch

1) The difference between nvarchar () and nchar () in the database is used in the SSIS project to associate with nvarchar () of the database Field Type nchar, but it cannot be associated all the time, and there is no problem when querying in the database. Later I will see that there are many null values after nchar (), and there will still be no space after update, followed by nchar () type changed to nvarch

1) differences between nvarchar () and nchar () in the database

In the SSIS project, the database field type is nchar () and the type is nvarchar (), but it cannot be associated all the time. It is no problem to query data in the database, at the end, we can see that there are many spaces behind the values of nchar (), and there are still spaces after update. After the nchar () type is changed to nvarchar (), there will be no problem with SSIS, all field types in the future database design must be unified with narchar ()

2) When Sqlserver2005 is installed in xp sp3, an error occurs when the installation program is run:

In Summary.txt, only:

Microsoft SQL Server 2005 9.00.4035.00
====================================
OS Version: Microsoft Windows XP Professional Service Pack 3 (Build 2600)
Time: Tue May 24 23:09:20 2011

Solution:

1) since the Office in the system is in GOST, download the full version of office 2003, find OWC11.MSI and click Install. After repairing the problem, Install SQL Server 2005.

2) the operating system may be cloned, so you need to reinstall the system.

3) it is best to install SQLSERVER2005 on the server system.

3) when executing the following statementSelect * from Dim_FactoryQuestionClass where gid_System_Factory = 'f4a552e9-B151-4098-BA52-F7F7A4EE82E'

Error:

Conversion failed when converting from a character string to uniqueidentifier.

Solution: 'f4a552e9-B151-4098-BA52-F7F7A4EE82E 'is not a standard GUID format

4) Update Problems

Update one column of a table to another table
Update Dim_WongCompareQuestionItem D set D. WongMsg = (select maid from Dim_WongCompareQuestionItem_0615_Back A where D. gid_WongCompareQuestionItem = A. gid_WongCompareQuestionItem)

An error is reported, not supported. Therefore, modify the following code:

Update Dim_WongCompareQuestionItem set WongMsg = (select WongMsg from Dim_WongCompareQuestionItem_0615_Back A where region = A. gid_WongCompareQuestionItem)

Update Dim_WongCompareQuestionItem set WongMsg = (select -- E. *, C. questionNumber,. itemCode,. itemname, D. questionNumber, B. itemCode, B. itemname, case when E. needFlag = '0' then C. questionNumber + 'select "'+. when itemCode + '",' + D. you cannot select "'+ B. itemCode else C. questionNumber + 'select "'+. when itemCode + '",' + D. select "'+ B. itemCode end tt from Dim_WongCompareQuestionItem E left join Dim_FactoryQuestionItem A on. gid_FactoryQuestionItem = E. gid_FactoryQuestionItem left joinDim_FactoryQuestionItem B on B. gid_FactoryQuestionItem = E. gid_FactoryQuestionItemCompare left joinDim_FactoryQuestionList C on C. gid_Factory_QuestionList =. gid_Factory_QuestionList left joinDim_FactoryQuestionList D on D. gid_Factory_QuestionList = B. gid_Factory_QuestionListwhere E. gid_WongCompareQuestionItem = Dim_WongCompareQuestionItem.gid_WongCompareQuestionItem)

5) SQL statement 2011-11-1

Update all fields of the float type (typestat = 0) in the qirui table, and change the value of 99 in the field to the other value of this field.

declare @sql varchar(max)declare @t varchar(255)set @t='qirui'select @sql=isnull(@sql,'')+'update '+@t+' set ' +name+'=(select avg('+ name+') from dbo.qirui where '+ name +'<>99) where '+ name + '=99' from (    select distinct name from syscolumns    where id=object_id(@t) and typestat=0      ) a      exec(@sql)


6) SQL 2005 swap row and column swaps

Select * FROM studentscoreject (SUM (Score) FOR Subject IN ([language], [mathematics], [chemistry], [physics]) AS B

7) The "database relationship diagram" prompt is displayed:

The database does not have a valid owner, so you cannot install the database relational diagram support object. To continue, first set the database owner to a valid login name using the "file" page or the alter authorization statement in the "database properties" dialog box, and then add a database relational diagram support object.

Solution:

Run the following command:

Alter authorization on database: mydbname TO sa

8) the following statement returns the rows in TableA but not in TableB. From the two tables, we can see that only rows with coll 2 in TableA do not exist in TableB, the following table shows the returned results.

SELECT * FROM TableA

EXCEPT

SELECT * FROM TableB;

10) SQL Server blocks access to 'sys. sp_oacreate' In The 'ole Automation Procedures 'process because this component has been disabled as part of the Server's security configuration. The system administrator can use sp_configur

Sys. sp_OAMethod 'of component' Ole Automation Procedures

Execute the following statement:

Sp_configure 'ole Automation Procedures ', 1;
GO
RECONFIGURE;
GO


11) 2008 log shrinking

USE DB_Satisfaction;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
Alter database DB_Satisfaction
Set recovery simple;
GO
-- Shrink the truncated log file to 1 MB.
Dbcc shrinkfile (DB_Satisfaction_Log, 10 );
GO
-- Reset the database recovery model.
Alter database DB_Satisfaction
Set recovery full;
GO


12) the following error occurs when you reinstall sqlserver 2008:

The SQL Server Analysis Services (MSSQL2008) service on the Local Computer stops again after it is started. Some services are automatically stopped if they have nothing to do, such as the performance log and alarm service.

Solution:

Clear the local application logs.


13)In WIN8, SqlServer2008 32-bit, the following error occurs when importing the office2007 Excel file:

Network-related or instance-specific errors occur when you establish a connection with SQL Server. The server is not found or cannot be accessed. Verify that the Instance name is correct and SQL Server is configured to allow remote connection. (Provider: SQL network interface, error: 26-An error occurred while locating the specified server/instance)

Solution:

USE [master]  GO  EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0',N'AllowInProcess',1  GO  EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0',N'DynamicParameters',1  GO 
Link to the server -- get EXCEL Data


13) after 2008 is converted to sqlserver 2005 by importing SQL scripts, Chinese characters are not supported.


Modify the sorting rule to Chinese_PRC_CI_AS

Alter database DB_MystatisInfoMazda collate Chinese_PRC_CI_AS


14) Error 1 The namespace "Microsoft. Practices. EnterpriseLibrary" does not contain a type or namespace name "Data ". Is Assembly reference missing?

The above error occurs when VS2012 is enabled for the VS2008 project.

This error is caused by the target Framework in the project properties. The default option is ". NET Framework 4 Client Profile", and the Client Profile is a subset of the. NET Framework, with the goal of fast release. But now Enterprise Library 5.0 seems to require the OracleClient-related Assembly included in the complete version of the Client Profile, so here we modify the target framework ". NET Framework 4.0 "and then compile it.


-- SQL SERVER2008 log shrinking method

Method 1

-- 1)
Alter database DB_MystatisInfoPA_2014 set recovery simple -- SIMPLE mode
GO
-- 2) Right-click the task and choose "contract"> "File". Then, the database logs are retained for only 1 MB.

-- 3)
Alter database DB_MystatisInfoPA_2014 set recovery full -- restore to FULL Mode
GO


Method 2:

-- Query the log file name
-- Select name, size FROM sys. database_files
USE [master]
GO
Alter database DB_Satisfaction_FAW set recovery simple with NO_WAIT
GO
Alter database DB_Satisfaction_FAW set recovery simple -- SIMPLE mode
GO
USE DB_Satisfaction_FAW
GO
Dbcc shrinkfile (n'db _ Satisfaction_log ', 11, TRUNCATEONLY)
GO
USE [master]
GO
Alter database DB_Satisfaction_FAW set recovery full with NO_WAIT
GO
Alter database DB_Satisfaction_FAW set recovery full -- restore to FULL Mode
GO


15)SQL Server 2008 "Changes to the table to be re-created to Prevent saving

If you force the changes, the following message is displayed: you cannot save the changes. You must delete and recreate the following tables. You have made changes to tags that cannot be re-created or enabled the "change to prevent table re-creation after saving" option.

Solution:

Tool-> Option-> there is a designer on the left-> Table Designer and Database Designer-> to prevent changes to the Save requirement to recreate the table (on the right), remove the hook.


15)Sqlserver2008

The checks field type is float, and the checks value is 0.98.

Select * from wave_revise where checks> = 0.98

Not found

Select * from wave_revise where checks> = 0.979999

Query

Solution:

Select * from wave_revise cast (checks as decimal (0.98)> =

After conversion, You can query it.

16) Database SQL server2008 misoperation database recovery

Third-party database recovery tools:

Recovery for SQL Server

Http://www.cnblogs.com/dudu/archive/2011/10/15/ SQL _server_recover_deleted_records.html


17) the following error occurs when importing an Excel file:

An exception occurs when the "ExcelReader" type initial value is set.

Failed to Load file or assembly MICROSOFT. OFFICE. INTEROP. EXCEL solved

Microsoft. jet. oledb.4.0 provider not registered on the Local Computer

Solution:

Http://blog.fend.cn/archives/173.html

Http://www.mycodes.net/78/6296.htm


18) the following errors are caused by the browser compatibility mode:

0x800a139e-JavaScript runtime error: Sys. ParameterCountException: The parameter count does not match

Solution:

This is because of the problem that occurs after the IE browser upgrade. You need to set the compatibility mode of the IE browser and add the website

Http://jingyan.baidu.com/article/15622f2472c112fdfcbea5e9.html

Http://jingyan.baidu.com/article/466506580d9828f549e5f8d1.html

19) after the Sqlserver2008 database is converted to Sqlserver2005, you need to adjust the character

Alter database DB_Satisfaction_FAW collate Chinese_PRC_CI_AS


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.