• SQL Server 2012 cannot connect to the WMI provider solution


    Today, a colleague encountered the following error when starting the SQL Server 2012 configuration manager of his working machine: Unable to connect to WMI provider. You do not have permission or the server cannot be accessed. Note that you can only use SQL Server Configuration Manager to manage SQL Server 2005 servers. The specified module […]

  • Summary of SQL server link server technology


    1、 Link MySQL using Microsoft OLE DB provider for ODBC Install the ODBC driver for MySQL 1. Create an ODBC system data source for MySQL. For example, select the database as test and the data source name as myDSN 2. Establish linked database EXEC sp_addlinkedserver @server = ‘MySQLTest’, @srvproduct=’MySQL’, @provider = ‘MSDASQL’, @datasrc = ‘myDSN’ […]

  • Sample code for creating and deleting constraints using SQL statements


    Create and delete constraints using SQL statements Constraint type Primary key constraint: the primary key column data is required to be unique and cannot be empty. Unique constraint: the column is required to be unique and can be null, but only one null value can appear. Check constraint: limit the value range and format of […]

  • SQL server logs in and reports an error that cannot be empty or null


    SQL Server 2012 value cannot be null. Parameter name: viewinfo (Microsoft. Sqlserver. Managemen) This is because a directory named 2 is missing in C: \ users \ your user name \ appdata \ local \ temp \. Just add it.

  • SQL Server clears completely duplicate data and only keeps the first item in the duplicate data


    –Create test table CREATE TABLE [dbo].[testtab]( [id] [nchar](10) NULL, [name] [nchar](10) NULL ) ; –Insert test data into the test table insert into testtab values(‘1′,’1’); insert into testtab values(‘1′,’1’); insert into testtab values(‘2′,’2’); insert into testtab values(‘2′,’2’); insert into testtab values(‘3′,’3’); insert into testtab values(‘3′,’3’); –Create a temporary table, insert the data in the test […]

  • SQL Server paging query general stored procedure (only for paging query)


    It has been used since the beginning of the project. The creator of this stored procedure (sorry, forgot his name) wrote this SQL code very well. On this basis, I adjusted the code according to my habits and mode of thinking, and only used it for paging query. /*———————————————- *procedure name : P_PageResult * author […]

  • SQL server running SQL Server 2000 with the permission of guess


    Due to its own vulnerability in SQL2000 database, hackers can easily obtain system administrator privileges through him. SQL runs with system permission by default, and the permission is super powerful. In order to make the server more secure, it is recommended to set SQL2000 to run with the permissions of ghosts, so that even if […]

  • Sqlserver modifies the storage location of database files and log files


    –View current storage location select database_id,name,physical_name AS CurrentLocation,state_desc,size from sys.master_files where database_ id=db_ ID (n ‘database name’); –Modify the storage location of the file and it will take effect next time –Testdb is the database name, Alter database database name modify file (name = file name (excluding suffix), filename = ‘file storage path’); Alter database […]

  • Steps to obtain connection string from sqlserver and MySQL


    Steps: 1、 Create a new text document xxx.txt and modify the file type to. UDL 2、 Open the file and find the corresponding service in provide data. 3、 Select the required database in the connection and fill in relevant information. 4、 Test the connection. If it succeeds, skip to the next step. If it fails, […]

  • Example of writing SQL instead of cursor


    Update Tb_Garden1 G Set Steward = ( Select Id From Zyq.Tb_ User u where replace (, ‘zone’) = and rownum = 1) Where exists (select 1 from ZYQ. Tb_user u where replace (, ‘zone’) = and rownum = 1)

  • Sql-order by multi field sorting (ascending and descending)


    ORDER BY _ column1, _ column2; /* _ Column1 in ascending order_ Column2 ascending*/ ORDER BY _ column1, _ column2 DESC; /* _ Column1 in ascending order_ Column2 descending*/ ORDER BY _ column1 DESC, _ column2 ; /* _ Column1 in descending order_ Column2 ascending*/ ORDER BY _ column1 DESC, _ column2 DESC; /* _ […]

  • Handling method of MSSQL attached database access denial prompt 5120 error


    MSSQL prompts the following error when attaching the database: Unable to open physical file “* * *. MDF”. Operating system error 5: ‘5 (access denied.)’. (Microsoft SQL server, error: 5120) This experience describes how to handle the error and then successfully attach the database. Add the authenticated users user name. Assign the authenticated users user […]