declare @starDate as nvarchar(8)
declare @overdate as nvarchar(8)
declare @Server as nvarchar(200)
declare @dataname as nvarchar(200)
declare @User as nvarchar(200)
declare @PW as nvarchar(200)set @starDate='20060101'
set @overdate='20070101'
set @Server='(local)'
set @dataname='histest'
set @User='sa'
set @PW=''select * from openrowset
('sqloledb' ,'(local)'
; 'sa';'',
' SELECT office_diag,
count(*) as ReC
FROM HISTEST.DBO.register_diag_tb
WHERE ( register_datetime between ''20060101'' and ''20070101'' ) AND
( return_register <> ''1'' ) AND
( return_sick_code = ''''or return_sick_code is null )
GROUP BY office_diag')
将上面相应的变量名字替到下面的SQL语句中,多谢
declare @overdate as nvarchar(8)
declare @Server as nvarchar(200)
declare @dataname as nvarchar(200)
declare @User as nvarchar(200)
declare @PW as nvarchar(200)set @starDate='20060101'
set @overdate='20070101'
set @Server='(local)'
set @dataname='histest'
set @User='sa'
set @PW=''select * from openrowset
('sqloledb' ,'(local)'
; 'sa';'',
' SELECT office_diag,
count(*) as ReC
FROM HISTEST.DBO.register_diag_tb
WHERE ( register_datetime between '''+@starDate+''' and '''+@overdate+''' ) AND
( return_register <> ''1'' ) AND
( return_sick_code = ''''or return_sick_code is null )
GROUP BY office_diag')
提示语法错误..那个@STARDATE加的不对.
declare @overdate as nvarchar(8)
declare @Server as nvarchar(200)
declare @dataname as nvarchar(200)
declare @User as nvarchar(200)
declare @PW as nvarchar(200)set @starDate='20060101'
set @overdate='20070101'
set @Server='(local)'
set @dataname='histest'
set @User='sa'
set @PW=''select * from openrowset
('sqloledb' ,@Server
; @User ;@PW;@dataname,
' SELECT office_diag,
count(*) as ReC
FROM HISTEST.DBO.register_diag_tb
WHERE ( register_datetime between '+@starDate+' and '+@overdate+' ) AND
( return_register <> ''1'' ) AND
( return_sick_code = ''''or return_sick_code is null )
GROUP BY office_diag')
('sqloledb' ,@Server
; @User ; @PW ; @dataname,
' SELECT office_diag,
count(*) as ReC
FROM HISTEST.DBO.register_diag_tb
WHERE ( register_datetime between '+@starDate+' and '+@overdate+' ) AND
( return_register <> ''1'' ) AND
( return_sick_code = ''''or return_sick_code is null )
GROUP BY office_diag')
declare @starDate as nvarchar(8)
declare @overdate as nvarchar(8)
declare @Server as nvarchar(200)
declare @dataname as nvarchar(200)
declare @User as nvarchar(200)
declare @PW as nvarchar(200)set @starDate='20060101'
set @overdate='20070101'
set @Server='(local)'
set @dataname='histest'
set @User='sa'
set @PW=''select * from openrowset
('MSDASQL' ,
'DRIVER={SQL Server};SERVER='+@Server+';UID='+@User+';PWD='+@PW+';database='+@dataname,
' SELECT office_diag,
count(*) as ReC
FROM HISTEST.DBO.register_diag_tb
WHERE ( register_datetime between '+@starDate+' and '+@overdate+' ) AND
( return_register <> ''1'' ) AND
( return_sick_code = ''''or return_sick_code is null )
GROUP BY office_diag')
declare @overdate as nvarchar(8)
declare @Server as nvarchar(200)
declare @dataname as nvarchar(200)
declare @User as nvarchar(200)
declare @PW as nvarchar(200)
declare @str varchar(8000)set @starDate='20060101'
set @overdate='20070101'
set @Server='(local)'
set @dataname='histest'
set @User='sa'
set @PW=''set @str='select * from openrowset(''sqloledb'','''+@Server+'''; '''+@User+''';'''+@PW+''',
SELECT office_diag,
count(*) as ReC
FROM HISTEST.DBO.register_diag_tb
WHERE ( register_datetime between '''+@starDate+''' and '''+@overdate+''' ) AND
( return_register <> ''1'' ) AND
( return_sick_code = ''''or return_sick_code is null )
GROUP BY office_diag)'
--print @str
exec(@str)
declare @overdate as nvarchar(8)
declare @Server as nvarchar(200)
declare @dataname as nvarchar(200)
declare @User as nvarchar(200)
declare @PW as nvarchar(200)set @starDate='20060101'
set @overdate='20070101'
set @Server='(local)'
set @dataname='histest'
set @User='sa'
set @PW=''declare @sql varchar(4000)
select @sql = ' select * from openrowset '
select @sql = @sql + '('' sqloledb'', '' ' + @Server + '; '' ' + @User + '''; ' + '''''' + ', '
+ ''' SELECT office_diag,
count(*) as ReC
FROM HISTEST.DBO.register_diag_tb
WHERE ( register_datetime between ' + @starDate + ' and ' + @overdate + ') AND
( return_register <> ''1'' ) AND
( return_sick_code = '''' or return_sick_code is null )
GROUP BY office_diag'')'print @sql------------Result
select * from openrowset (' sqloledb', ' (local); ' sa'; '', ' SELECT office_diag,
count(*) as ReC
FROM HISTEST.DBO.register_diag_tb
WHERE ( register_datetime between 20060101 and 20070101) AND
( return_register <> '1' ) AND
( return_sick_code = '' or return_sick_code is null )
GROUP BY office_diag')
declare @starDate as nvarchar(8)
declare @overdate as nvarchar(8)
declare @Server as nvarchar(200)
declare @dataname as nvarchar(200)
declare @User as nvarchar(200)
declare @PW as nvarchar(200)set @starDate='20060101'
set @overdate='20070101'
set @Server='(local)'
set @dataname='histest'
set @User='sa'
set @PW=''declare @sql varchar(4000)
select @sql = ' select * from openrowset '
select @sql = @sql + '('' sqloledb'', '' ''''' + @Server + '''''; ''''' + @User + '''''; ' + '''''''''''' + ', '
+ ''' SELECT office_diag,
count(*) as ReC
FROM HISTEST.DBO.register_diag_tb
WHERE ( register_datetime between ' + @starDate + ' and ' + @overdate + ') AND
( return_register <> ''''1'''' ) AND
( return_sick_code = '''''''' or return_sick_code is null )
GROUP BY office_diag'')'print @sql-----Result
select * from openrowset (' sqloledb', ' ''(local)''; ''sa''; ''''', ' SELECT office_diag,
count(*) as ReC
FROM HISTEST.DBO.register_diag_tb
WHERE ( register_datetime between 20060101 and 20070101) AND
( return_register <> ''1'' ) AND
( return_sick_code = '''' or return_sick_code is null )
GROUP BY office_diag')
这样差不多对了
最后
exec(@sql)