SELECT * FROM OPENROWSET('SQLNCLI', 'SERVER=.;TRUSTED_CONNECTION=YES','select 1 EX_REQ_ID, * from sys.dm_exec_requests') SELECT * FROM OPENROWSET('SQLOLEDB', 'DATA SOURCE=.;Trusted_Connection=yes; Integrated Security=SSPI;', 'select 1 EX_REQ_ID, * from sys.dm_exec_requests') SELECT * FROM OPENROWSET('SQLOLEDB', '.';'sa';'/''],lp123','select 1 EX_REQ_ID, * from sys.dm_exec_requests') SELECT * FROM OPENROWSET('SQLNCLI','.';'sa';'/''],lp123','SET NOCOUNT ON; EXEC [TUAN_BIZ_SYSTEM].DBO.COLS22;') there should be "set nocount on" if not only one 'select' in SP, ps. only the first result set would be return & in SQL Server 2012, dynamic SQL needs "with result set ((ID INT))" (something like that) in EXEC statement of SP SELECT * FROM OPENROWSET('SQLNCLI', 'Server=.;Uid=sa;Pwd=/''],lp123;Database=msdb', 'SELECT db_name() name') SELECT * FROM OPENROWSET('SQLOLEDB', 'Server=.;Uid=sa;Pwd=/''],lp123;Database=msdb', 'SELECT db_name() name') SELECT * FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 'exec sp_msforeachdb ''if ''''?'''' = ''''msdb'''' begin use [?]; select db_name() name; end'' with result sets ((name nvarchar(max)))') We can use "database" attribute instead of using "initial catalog". (sp_msforeachdb is another...stupid way. XD) metadata: declare @a nvarchar(max) = 'select @@servername' select * from sys.dm_exec_describe_first_result_set(@a, null, 0) select * into ttc from openrowset('sqlncli','SERVER=.\node1;TRUSTED_CONNECTION=YES', 'SET NOCOUNT ON; SET FMTONLY OFF; exec sp_describe_first_result_set @tsql = N''SP_WHO''') Backup & Restore SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time], CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours], CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END) FROM sys.dm_exec_sql_text(sql_handle))) FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE') truncate all table sp_msforeachtable 'print (''truncate table ?'' + char(10) + ''go'' + char(10) + ''print ''''? done'''''' + char(10) + ''go'' + char(10))' Disk Space select (hey... Navjyot, I always think your scripting skill sucks, however, this select is useful...XD) Hekaton http://msdn.microsoft.com/en-us/library/dn133186(v=sql.120).aspx |
sql | SQL Server >