SQL SERVER Tricks
1, How to get the row number of one table:
select Row_Number() over ( order by id ), *
from testTable
2, sp_configure
SP_Configure
3, 创建链接服务器
exec sp_addlinkedserver 'Conn_Name', ' ', 'SQLOLEDB', 'server_name OR IP Address'
go
exec sp_addlinkedsrvlogin 'Conn_Name', 'false',null, 'uid', 'pwd'
go
select * from Conn_Name.dbo.tableName [this is remote database's data table.]
go
--drop the connection.
exec sp_dropserver 'Conn_Name', 'droplogins'
go
4, Convert xml input into table data.
create table tb
(
stor_id int,
stor_nm nvarchar(100)
)
go
create proc sp_test
(
@xml ntext
)
as
begin
declare @xmlHandler int
exec sp_xml_preparedocument @xmlHandler output,@xml
insert into tb
select *
from
openxml(@xmlHandler,'/Stores/Store',2)
with
(
StoreId int, --The name must be equals to the name in xml [StoreId]
StoreName nvarchar(100) --the same.
)as a
end
go
exec sp_test '
<Stores>
<Store><StoreId>1111</StoreId><StoreName>Lyn</StoreName></Store>
<Store><StoreId>2222</StoreId><StoreName>Roy Store</StoreName></Store>
<Store><StoreId>3333</StoreId><StoreName>Re</StoreName></Store>
<Store><StoreId>4444</StoreId><StoreName>Tester</StoreName></Store>
<Store><StoreId>5555</StoreId><StoreName>Software Engineer</StoreName></Store>
</Stores>
'
go
select * from tb
