FROM OPENROWSET中的变量 [英] Variables in FROM OPENROWSET
问题描述
您好，
我有以下脚本运行正常
< span style ="font-size：0.75em"> SELECT * INTO tmpAsset 来自OPENROWSET（'Microsoft.ACE.OLEDB.12.0'，
'Excel 12.0; Database = D：\\\ .Depreciation.xls; HDR = No'，
'SELECT
F1代码， ; $
F3为[Item_Name]，
F5 AS [Br_Code]，
F6 AS [Purchased_Date]，
F7 AS [费用]，
F6 AS [Openning_Accum_Dep]，
F7 AS [Depre_Value]，
F8 AS [Depre_Current_Month]，
F9 AS [Depre_Branch_Year]，
F10 AS [Depre_YTD]，
F11 AS [Closing_Accum_Dep]，
F12 AS [账面净值]
FROM [ScheduleFixedAssetsDepreciation $]
在哪里LEN（F1）在26和31之间'）
SELECT * FROM tmpAsset
但是当我尝试使用变量时会收到错误：
OLE DB提供程序" Microsoft.ACE.OLEDB.12.0"对于链接服务器"（null）"返回消息"无效的SQL语句;预期'DELETE'，'INSERT'，'PROCEDURE'，'SELECT'或'UPDATE'。"。$
Msg 7321，Level 16，State 2，Line 1
准备查询时出错"F1为代码，
F3为[Item_Name]，
F5 AS [Br_Code]，
F6 AS [Purchased_Date]，
F7 AS [费用]，
F6 AS [Openning_Accum_Dep]，
F7 AS [Depre_Value]，
F8 AS [Depre_Current_Month]，
F9 AS [Depre_Branch_Year]，
F10 AS [Depre_YTD]，
F11 AS [Closing_Accum_Dep]，
F12 AS [Net Book值]¥
FROM [ScheduleFixedAssetsDepreciation $]
在26和31之间的地方（F1）"对于OLE DB提供程序"Microsoft.ACE.OLEDB.12.0"的执行。对于链接服务器"（null）"。
我的变量脚本如下：
DECLARE @FilePath NVARCHAR（255）
DECLARE @SQL NVARCHAR（MAX）
SET @ FilePath ='D：\\\\Depreciation.xls'
$
如果OBJECT_ID（'tmpAsset'，'U'）IS NOT NULL DROP TABLE tmpAsset
设置@ SQL ='SELECT * INTO tmpAsset FROM OPENROWSET（
''Microsoft.ACE。 OLEDB.12.0''，
''Excel 12.0; HDR = YES; Database ='+ @FilePath +'''，
''F1 as Code，
F3为[Item_Name]，
F5 AS [Br_Code]，
F6 AS [Purchased_Date]，
F7 AS [费用]，& n $; b $ b F6 AS [Openning_Accum_Dep]，
F7 AS [Depre_Value]，
F8 AS [Depre_Current_Month]，
F9 AS [Depre_Branch_Year]，
F10 AS [Depre_YTD ]，$
F11 AS [Closing_Accum_Dep]，
F12 AS [账面净值]
FROM [ScheduleFixedAssetsDepreciation $]
在哪里LEN（F1）在26和31之间''''
EXECUTE（@SQL）
SELECT * FROM tmpAsset
Sam
Hi
Sam ，
请检查您的原始脚本，看看您是否错过了'选择'。请尝试以下脚本。
DECLARE @FilePath NVARCHAR（255）
DECLARE @SQL NVARCHAR（MAX）
SET @FilePath ='D：\\\\Depreciation.xls'
IF OBJECT_ID（'tmpAsset'，'U '）IS NOT NULL DROP TABLE tmpAsset
设置@ SQL ='SELECT * INTO tmpAsset FROM OPENROWSET（
''Microsoft.ACE.OLEDB.12.0''，
'' Excel 12.0; HDR = YES;数据库='+ @FilePath +'''，
''选择
F1代码，
F3为[Item_Name]，
F5 AS [Br_Code]，
F6 AS [Purchased_Date]，
F7 AS [成本]，
F6 AS [Openning_Accum_Dep]，
F7 AS [Depre_Value]，
F8 AS [Depre_Current_Month]，
F9 AS [Depre_Branch_Year]，
F10 AS [Depre_YTD]，
F11 AS [Closing_Accum_Dep]，
F12 AS [账面净值]
FROM [ScheduleFixedAssetsDepreciation
在哪里LEN（F1）在26和31之间''''
--- p rint（@SQL）
EXECUTE（@SQL）
SELECT * FROM tmpAsset
希望它可以帮到你。
最好的问候，
拉结
Hello,
I have the follow script which run ok
SELECT * INTO tmpAsset FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=D:\9\Depreciation.xls;HDR=No',
'SELECT
F1 as Code,
F3 as [Item_Name],
F5 AS [Br_Code],
F6 AS [Purchased_Date],
F7 AS [Cost],
F6 AS [Openning_Accum_Dep],
F7 AS [Depre_Value],
F8 AS [Depre_Current_Month],
F9 AS [Depre_Branch_Year],
F10 AS [Depre_YTD],
F11 AS [Closing_Accum_Dep],
F12 AS [Net Book Value]
FROM [ScheduleFixedAssetsDepreciation$]
WHERE LEN(F1) BETWEEN 26 AND 31')
SELECT * FROM tmpAsset
But when I try to use variables it get the error:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.".Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "F1 as Code,
F3 as [Item_Name],
F5 AS [Br_Code],
F6 AS [Purchased_Date],
F7 AS [Cost],
F6 AS [Openning_Accum_Dep],
F7 AS [Depre_Value],
F8 AS [Depre_Current_Month],
F9 AS [Depre_Branch_Year],
F10 AS [Depre_YTD],
F11 AS [Closing_Accum_Dep],
F12 AS [Net Book Value]
FROM [ScheduleFixedAssetsDepreciation$]
WHERE LEN(F1) BETWEEN 26 AND 31" for execution against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
My script with the variable is as follow:
DECLARE @FilePath NVARCHAR(255)
DECLARE @SQL NVARCHAR(MAX)
SET @FilePath = 'D:\9\Depreciation.xls'
IF OBJECT_ID('tmpAsset', 'U') IS NOT NULL DROP TABLE tmpAsset
Set @SQL='SELECT * INTO tmpAsset FROM OPENROWSET(
''Microsoft.ACE.OLEDB.12.0'',
''Excel 12.0;HDR=YES;Database=' + @FilePath + ''',
''F1 as Code,
F3 as [Item_Name],
F5 AS [Br_Code],
F6 AS [Purchased_Date],
F7 AS [Cost],
F6 AS [Openning_Accum_Dep],
F7 AS [Depre_Value],
F8 AS [Depre_Current_Month],
F9 AS [Depre_Branch_Year],
F10 AS [Depre_YTD],
F11 AS [Closing_Accum_Dep],
F12 AS [Net Book Value]
FROM [ScheduleFixedAssetsDepreciation$]
WHERE LEN(F1) BETWEEN 26 AND 31'')'
SELECT * FROM tmpAsset
Sam
Hi Sam,
Please check your original script to see if you missed 'select ' . And please try following script .
DECLARE @FilePath NVARCHAR(255) DECLARE @SQL NVARCHAR(MAX) SET @FilePath = 'D:\9\Depreciation.xls' IF OBJECT_ID('tmpAsset', 'U') IS NOT NULL DROP TABLE tmpAsset Set @SQL='SELECT * INTO tmpAsset FROM OPENROWSET( ''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;HDR=YES;Database=' + @FilePath + ''', ''select F1 as Code, F3 as [Item_Name], F5 AS [Br_Code], F6 AS [Purchased_Date], F7 AS [Cost], F6 AS [Openning_Accum_Dep], F7 AS [Depre_Value], F8 AS [Depre_Current_Month], F9 AS [Depre_Branch_Year], F10 AS [Depre_YTD], F11 AS [Closing_Accum_Dep], F12 AS [Net Book Value] FROM [ScheduleFixedAssetsDepreciation
WHERE LEN(F1) BETWEEN 26 AND 31'')' ---print (@SQL) EXECUTE(@SQL) SELECT * FROM tmpAsset
Hope it can help you.
Best Regards,
Rachel
这篇关于FROM OPENROWSET中的变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
