函数记录:

  • DateAdd:
    函数在日期中添加或减去指定的时间间隔,DATEADD(day/month..,number,date)

  • getdate():
    系统时间

  • STUFF结合FOR XML PATH拼接字符串
    不是 varchar类型，先转CAST (xx as VARCHAR)

1
2
3
4
5
6
7
SELECT
	id,
	[text] = '[' + stuff((
SELECT
	',{"bizDate":"' + CAST ( business_date AS VARCHAR ) + '","pkgCode":"' + [package_code] + '","guestType":"' + [guest_type] + '","count":' + CAST ( COUNT AS VARCHAR ) + ',"calcDesc":null}' 
FROM
	kpc.dbo.reservation_package_daily t

解决SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的访问的方法

1、开启Ad Hoc Distributed Queries组件，在sql查询编辑器中执行如下语句：

1
2
3
4
exec sp_configure 'show advanced options',1;
reconfigure;
exec sp_configure 'Ad Hoc Distributed Queries',1;
reconfigure;

2、关闭Ad Hoc Distributed Queries组件，在sql查询编辑器中执行如下语句：

1
2
3
4
exec sp_configure 'Ad Hoc Distributed Queries',0;
reconfigure;
exec sp_configure 'show advanced options',0;
reconfigure;

3、远程链接

1
2
3
4
select * from OPENDATASOURCE(
 'SQLOLEDB',
 'Data Source=127.0.0.1;User ID=sa;Password=root'
 ).xxx.dbo.xxx;

不同服务器数据库之间的数据操作--创建链接服务器

1
2
3
exec sp_addlinkedserver   'ITSV', '', 'SQLOLEDB', 'ip';
exec sp_addlinkedsrvlogin 'ITSV', 'false',null, 'user', 'pass';
select * from ITSV.库名.dbo.xxx;