Category：MsSql
-
Use row_ Number() implements paging instances
Copy codeThe code is as follows: create procedure p_News @pageSize int,@pageIndex int as begin select * from( select *,Row_Number() over(order by Id) as [$Row_Num] from News –Where [$row_num] < = 3 the line number cannot be used at this time. It will be generated only after all execution is completed)as _temp where [$Row_Num] between (@pageIndex*pageSize+1) […]
-
IP connection to SQL server failed (configuration as string failed). Solution
Failed to connect to SQL server using IP or configured as connection string Scenario 1: whenwebconfigUsed in documents < add key = “connectionstring” value = “server = 127.0.0.1; database = database name; uid = Sa; PWD = 1” / > connect to the database. It is found that normal login cannot be performed during login, […]
-
SQL Server database upgrade script steps
Only remote assistance. I specially made a script to instruct the customer to execute the script in SSMS by phone The difference between the 1.0 database and the 1.1 database is that one [ct_outercard] table in the 1.1 database has six more fields than that in 1.0, and all other tables are the same In […]
-
SQL Server 2008 CDC function to realize data change capture script
CDC:Change Data Capture Copy codeThe code is as follows: –Step: This article takes gposdb as an example –Step 1: explicitly enable CDC for the target library:–Use sys. In the current library sp_ cdc_ enable_ db。 Returns 0 (success) or 1 (failure).–Note that this feature cannot be enabled for system and distribution databases. And the performer […]
-
Method for SQL server to execute multiple scripts in sequence (using method of sqlcmd utility)
resolvent: There are several ways to deal with this situation: 1. Purchase third-party software (it is generally estimated that few people buy it) 2. Program a small software to execute, but the logic requirement is relatively high, and the programming ability must have a certain level. I don’t have this for the time being. 3. […]
-
Solution to “device activation error” in SQL Server 2000
The operation of data in database is an important skill, among which the recovery and restoration of data are also common. Do you encounter an error message such as “device activation error, please use the with move option to mark the valid location of the file” during database recovery? I don’t know if you still […]
-
Teach you to easily learn the skills of SQL server recording shifts
For example, the company’s employees adopt three shift systems: the first shift is from 0:00 a.m. to 8:00 a.m., the second shift is from 8:00 a.m. to 4:00 p.m., and the third shift is from 4:00 p.m. to 12:00 p.m. Employees use an electronic clock for signature, which can automatically add records to the SQL […]
-
Teach you to easily recover / repair the MDF files of SQL server
First of all: if the backup database has two files, they are LDF and MDF, open the enterprise manager, right-click on the instance – all tasks – attach database, and then select that MDF file, you can. Or enter in the query analyzer: Copy codeThe code is as follows: sp_ attach_ DB “database name”, “path […]
-
The default encoding problem of importing MSSQL data into Mongo (the correct method of importing Mongo)
Let’s talk about the steps of my operation first.1. Query 50000 pieces of data from the Management Studio of sql2008 and right-click to export them to CSV2. Since there is no column name in the default export, the column name is added after manual editing3. Use mongoimport to import data. If there is an error, […]
-
Script instance of 2-division paging stored procedure
It should be noted that there are many parameters of this stored procedure. In actual use, I wrote a separate class outside. The page call directly calls the encapsulated class. There are many methods, mainly ideas, which you can refer to. Code modifications focus on similar Copy codeThe code is as follows: if @Sort=0set @strTmp […]
-
SQL script for implementing split function
Copy codeThe code is as follows: –Create a function with two parametersCreate FUNCTION [dbo].[SplitToTable](@String of NVAR — input string of [email protected] nvarchar (10) = ‘– segmentation condition)–The returned data is a tableRETURNS @SplitStringsTable TABLE([id] int identity(1,1),[value] nvarchar(max))ASBEGINDECLARE @CurrentIndex int;– Current indexDECLARE @NextIndex int;– Next indexDECLARE @ReturnText nvarchar(max);– Return contentSELECT @CurrentIndex=1;– Set the initial value of […]
-
Solution for sqlserver not starting (error 9003)
Let’s talk about the environment first Customer environment: Windows 2003 enterprise SP2 32-bit SQL2005 enterprise 32-bit SP4Own laptop environment: windows7 SP1 32-bit SQL 2005 personal Developer Edition 32-bitThe computer name of my laptop: JoeThe computer name of the client computer: HS The size of the master database on the customer side: how many MBThe business […]