Tag Archives: SQL Server

[Thinkphp6] Connect to SQL Server and use subquery to report error: when subquery is not introduced with exists, only one expression can be specified in the selection list

Connect to SQL Server in thinkphp6 and report errors using subquery

sentence

Print SQL statements

Mysql

SELECT `NickName`,`Gender`,`Mobile`,`RealName`,`Birthday` FROM `SiteCustomerSupplement` `s` 

INNER JOIN `WxUser` `u` ON `u`.`OpenId`=`s`.`OpenId` 

WHERE `s`.`OpenId` IN (SELECT DISTINCT `OpenId` FROM `WxPackagesOrder` WHERE `OrderStatus` >= 40 AND `RegionId` = 72) AND `Birthday` BETWEEN '2021-10-26' AND '2021-11-02'

SQL Server

SELECT T1.* FROM (SELECT thinkphp.*, ROW_NUMBER() OVER ( ORDER BY rand()) AS ROW_NUMBER FROM (SELECT [NickName],[Gender],[Mobile],[RealName],[Birthday] FROM [SiteCustomerSupplement] [s] 

INNER JOIN [WxUser] [u] ON [u].[OpenId]=[s].[OpenId]

WHERE [s].[OpenId] IN (SELECT T1.* FROM (SELECT thinkphp.*, ROW_NUMBER() OVER ( ORDER BY rand()) AS ROW_NUMBER FROM (SELECT DISTINCT [OpenId] FROM [WxPackagesOrder] WHERE [OrderStatus] >= 40 AND [RegionId] = 72) AS thinkphp) AS T1) AND [Birthday] BETWEEN '2021-10-26' AND '2021-11-02') AS thinkphp) AS T1

Obviously, it is much longer than the MySQL statement, mainly this paragraph

SELECT thinkphp.*, ROW_NUMBER() OVER ( ORDER BY rand()) AS ROW_NUMBER FROM

So when the problem comes, how to solve it?Just replace it with a native statement. Don’t use TP internal encapsulation.

Error in creating SQL Server database by Navicat premium (5174)

Error message: each file size must be greater than or equal to 512KB

1. Look at the “database log file” configuration of the client. The size is set to 1MB

2. Click “SQL preview” to view the log file configuration in the SQL statement creating the database. The size is set to 1KB

Problem found: the setting of “myandedps_log” file size in the SQL statement is different from that configured by the client.

3. Solutions

Change 1KB in the SQL statement to 1MB, or click the “file” tab, click the “size” input box, trigger an onblur event similar to the input box, and then look at the SQL statement. The size has changed to 1m.

Error in importing excel file from SQL Server

catalogue

The text is truncated, or one or more characters do not match in the target code page

Cause

Solution

The value violates the integrity constraint of the column

Blank line

Not set to allow null

I have imported data before without reporting any errors. When importing data again today, I found two errors, as follows

The text is truncated, or one or more characters do not match in the target code page

It’s easy to understand that your data is too large and the size you set is not enough. There may be questions here

As shown in the figure below, if the size can be set, an error will still be reported after setting the size

Cause

This is because in order to determine the field type of the data table, the import and export of SQL server takes the first 8 lines of the excel file. If the first eight strings are less than 255, they are set to nvarchar (255). However, if the length of the records behind excel exceeds 255, an error will occur during import. Even if the length of this field is adjusted when importing the setting mapping

Solution

I added a row to the first row of Excel data, and each field is filled with long characters, so that SQL server will automatically set the maximum value when detecting the first 8 rows

The value violates the integrity constraint of the column

Blank line

This is because the new column behind you is a space. You can select the column behind the last field and delete it to eliminate the possibility of spaces. As long as there is no f beginning such as F10 when importing

Not set to allow null

There is another reason. This field is set to allow null. I cancelled it and the setting is not allowed to be Mull. Then an error is reported that a column violates data integrity
if it is reset to allow null, there is no problem

[Solved] JDBC connection to SQL Service reported an error: “the driver cannot establish a secure connection with SQL server by using SSL encryption“

1. Environment

IDE : IDEA

JDK : JAVA1.8

SQL Server Version: SQL Server 2008

2. Specific error information

2019-09-16 17:43:11 [Druid-ConnectionPool-Create-847839957] ERROR c.a.d.p.DruidDataSource - create connection SQLException, url: jdbc:sqlserver://localhost;DatabaseName=PT_STORE_HLW, errorCode 0, state 08S01
com.microsoft.sqlserver.jdbc.SQLServerException: The driver was unable to establish a secure connection to SQL Server by using Secure Socket Layer (SSL) encryption. Error: "SQL Server did not return a response. The connection was closed. ClientConnectionId:22dc49b0-221d-4a51-9a84-8d507658df6e”。
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1667)
	at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1668)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1323)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)
	at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:156)
	at com.alibaba.druid.filter.stat.StatFilter.connection_connect(StatFilter.java:218)
	at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:150)
	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1560)
	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1623)
	at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2468)
Caused by: java.io.IOException: SQL Server No response returned. The connection is closed. ClientConnectionId:22dc49b0-221d-4a51-9a84-8d507658df6e
	at com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeInputStream.ensureSSLPayload(IOBuffer.java:651)
	at com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeInputStream.readInternal(IOBuffer.java:708)
	at com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeInputStream.read(IOBuffer.java:700)
	at com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream.readInternal(IOBuffer.java:895)
	at com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream.read(IOBuffer.java:883)
	at sun.security.ssl.InputRecord.readFully(InputRecord.java:465)
	at sun.security.ssl.InputRecord.read(InputRecord.java:503)
	at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:975)
	at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1367)
	at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1395)
	at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1379)
	at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1618)
	... 10 common frames omitted

Solution process:

On the Internet, some Baidu said it was because of the problem of JDK1.6, but I used JDK1.8

It is also said that JRE lacks two jar packages, which is not the reason after trying

Solution:

In JDK 8 and earlier, edit the/lib/security/Java. Security file and create 3DES_ EDE_ CBC is removed from the jdk.tls.legacyalgorithms security attribute

Remove 3DES from Java. Security under JDK_EDE_CBC can connect SQL Server with JDBC

In Java 8, the encryption suite is disabled by default: “SSL”_ RSA_ WITH_ 3DES_ EDE_ CBC_ Sha ”
and then to connect successfully, open SSL_ RSA_ WITH_ 3DES_ EDE_ CBC_ The answer is:
open folder (Java)_ Security policy file in home/JRE/lib/Security: Java. Security
Modify JDK. TLS. Disabledalgorithms option jdk.tls.disabledalgorithms = MD5, SSLv3, DSA, RSA keysize & lt; 2048
jdk.tls.disabledAlgorithms=SSLv3, RC4, MD5withRSA, DH keySize < 1024,
EC keySize < 224, DES40_CBC, RC4_ 40,3DES_ EDE_ CBC

To enable SSL_RSA_WITH_3DES_EDE_CBC_Sha, will 3DES_EDE_CBC notes out:

Default values of jdk.tls.disabledalgorithms in Java 8:
jdk.tls.disabledalgorithms = MD5, SSLv3, DSA, RSA keysize < 2048
jdk.tls.disabledAlgorithms=SSLv3, RC4, MD5withRSA, DH keySize < 1024,
EC keySize < 224, DES40_CBC, RC4_40
#,3DES_EDE_CBC

How to Solve SQL Server: database stuck in “restoring” state

Question:

I backed up a database:

BACKUP DATABASE MyDatabase
TO DISK = 'MyDatabase.bak'
WITH INIT --overwrite existing

And then tried to restore it:

RESTORE DATABASE MyDatabase
   FROM DISK = 'MyDatabase.bak'
   WITH REPLACE --force restore over specified database

And now the database is stuck in the restoring state.

Some people have theorized that it’s because there was no log file in the backup, and it needed to be rolled forward using:

RESTORE DATABASE MyDatabase
WITH RECOVERY

Except that, of course, fails:

Msg 4333, Level 16, State 1, Line 1
The database cannot be recovered because the log was not restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

And exactly what you want in a catastrophic situation is a restore that won’t work.

The backup contains both a data and log file:

RESTORE FILELISTONLY 
FROM DISK = 'MyDatabase.bak'

Logical Name    PhysicalName
=============   ===============
MyDatabase    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDatabase.mdf
MyDatabase_log  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDatabase_log.LDF

 

Solve the problem that SQL Server blocks the state’openrowset/opendatasourc of component ‘ad hoc distributed queries’

1.1 connection

select * from openrowset( 'SQLOLEDB ', 'sql server name '; 'username '; 'password ',database name.dbo.table name)

1.2 error prompt

Message 15281, level 16, status 1, line 3
sql server has blocked access to the state “openrowset/opendatasource” of the component “ad hoc distributed queries” because the component has been shut down as part of the security configuration of this server. System administrators can use sp_ Configure enables “ad hoc distributed queries”. For more information about enabling ad hoc distributed queries, search for ad hoc distributed queries in SQL Server Books Online

2. Solutions

2.1. Open the ad hoc distributed queries component and execute the following statements in the SQL query editor:

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

2.2. Close the ad hoc distributed queries component and execute the following statements in the SQL query editor:

exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

You can’t specify target table for update….

This article mainly introduces the MySQL you can’t specify target table for update in from clause error resolution, need friends can refer to

The error of you can’t specify target table for update in from clause in MySQL means that you can’t select some values in the same table and then update the table (in the same statement). For example, the following SQL:

delete from tbl where id in 
(
        select max(id) from tbl a where EXISTS
        (
            select 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)&>1
        )
        group by tac
)

Just rewrite it as follows:

delete from tbl where id in 
(
    select a.id from 
    (
        select max(id) id from tbl a where EXISTS
        (
            select 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)&>1
        )
        group by tac
    ) a
)

That is to say, select the result through the middle table again, so as to avoid the error. Note that this problem only occurs in mysql, and will not occur in MSSQL and Oracle.