openrowset dient zum öffnen von externen datenquellen, die dann wie eine tabelle in der from Klausel verwendet werden können. Es gibt 2 Syntaxe:
1.) Openrowset(BULK 'filename', SINGLE_BLOB)
Upload Files into Filetable
ALTER PROCEDURE [dbo].[InsertFile]
(
@filename nvarchar(max),
@filepath nvarchar(max)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @sqlStr nvarchar(max)
set @sqlStr='INSERT INTO [dbo].StpFile ([name],[file_stream]) SELECT '''+@filename+''',* FROM OPENROWSET(BULK '''+@filepath+''', SINGLE_BLOB) AS FileData'
Print @sqlStr
execute (@sqlStr)
END
2.) Openrowset('providername', 'connstr', 'query')
Stored Procedures als Views verwenden
select * from openrowset('SQLNCLI', 'Server=.\sqlexpress;Trusted_Connection=yes;','exec sp_databases') order by Database_size desc
dazu müssen adhoc verteilte Queries zugelassen sein:
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
