SQL 2000 计划作业最大字符
我正在尝试安排一个很长的 T-SQL 脚本。 但是，当粘贴到文本框中时，脚本会被切断。 我用谷歌搜索了这个问题，有 3200 个字符的限制。
你建议我做什么来解决这个问题？ 创建一个存储过程并将其作为计划作业运行？ 有更好的选择吗？ 如果有帮助，这是脚本。
INSERT INTO CheltonCustomizations..SOBacklogAudits (SoNo,
SoRev,
SysItemNo,
UserItemNo,
Release,
OrderDate,
DueDate,
PartNo,
PartRev,
OrderQty,
ShippedQty,
ShippedNotInvoicedQty,
InvoicedQty,
ProdCl,
[Group],
NetAmount,
SorelsIdentityColumn,
BacklogDate)
SELECT SOR.fsono,
SOM.fsorev,
SOR.fenumber,
SOR.finumber,
SOR.frelease,
SOM.[forderdate],
SOR.fduedate,
SOR.fpartno,
SOR.fpartrev,
SOR.forderqty,
SOR.fshipbook + SOR.fshipbuy + SOR.fshipmake,
COALESCE (
DBO.GETSHIPPEDNOTINVOICEDQTY (
SOR.fsono + SOR.finumber + SOR.frelease),
0),
SOR.finvqty,
SOI.fprodcl,
SOI.fgroup,
(SOR.forderqty - SOR.finvqty
- COALESCE (
DBO.GETSHIPPEDNOTINVOICEDQTY (
SOR.fsono + SOR.finumber + SOR.frelease),
0))
* SOR.funetprice,
SOR.identity_column,
getdate ()
--CONVERT (DATETIME, '02/09/2009')
FROM SORELS SOR
INNER JOIN SOITEM SOI
ON SOR.finvqty <
SOR.forderqty
- COALESCE (
DBO.GETSHIPPEDNOTINVOICEDQTY (
SOI.FSONO + SOR.finumber + SOR.frelease),
0)
- 0
AND SOR.finvqty <
SOR.forderqty
- COALESCE (
DBO.GETSHIPPEDNOTINVOICEDQTY (
SOR.fsono + SOI.FINUMBER + SOR.frelease),
0)
- 0
AND SOR.FSONO = COALESCE (SOI.FSONO, SOI.FSONO)
AND SOR.FINUMBER = SOI.FINUMBER
INNER JOIN SOMAST SOM
ON SOR.finvqty <
SOR.forderqty
- COALESCE (
DBO.GETSHIPPEDNOTINVOICEDQTY (
SOM.FSONO + SOR.finumber + SOR.frelease),
0)
- 0
AND SOR.FSONO = COALESCE (SOM.FSONO, SOM.FSONO)
AND SOI.FSONO = SOM.FSONO
WHERE SOM.FSTATUS = 'OPEN' AND FMASTERREL = COALESCE (0, 0)
AND SOM.forderdate >=
CONVERT (DATETIME, '01/01/2002')
AND SOR.forderqty >
0 + SOR.finvqty
+ COALESCE (
DBO.GETSHIPPEDNOTINVOICEDQTY(SOR.fsono
+ SOR.finumber
+ SOR.frelease),
0)
AND SOR.finvqty <
SOR.forderqty
- COALESCE (
DBO.GETSHIPPEDNOTINVOICEDQTY(SOR.fsono
+ SOR.finumber
+ SOR.frelease),
0)
I'm trying to schedule a long T-SQL script. However, the script gets cut off when pasted into the text box. I googled the problem and there is a 3200 character limit.
What do you recommend I do to solve this? Create a stored procedure and run that as a scheduled job? Is there a better option? If it helps, here is the script.
INSERT INTO CheltonCustomizations..SOBacklogAudits (SoNo,
SoRev,
SysItemNo,
UserItemNo,
Release,
OrderDate,
DueDate,
PartNo,
PartRev,
OrderQty,
ShippedQty,
ShippedNotInvoicedQty,
InvoicedQty,
ProdCl,
[Group],
NetAmount,
SorelsIdentityColumn,
BacklogDate)
SELECT SOR.fsono,
SOM.fsorev,
SOR.fenumber,
SOR.finumber,
SOR.frelease,
SOM.[forderdate],
SOR.fduedate,
SOR.fpartno,
SOR.fpartrev,
SOR.forderqty,
SOR.fshipbook + SOR.fshipbuy + SOR.fshipmake,
COALESCE (
DBO.GETSHIPPEDNOTINVOICEDQTY (
SOR.fsono + SOR.finumber + SOR.frelease),
0),
SOR.finvqty,
SOI.fprodcl,
SOI.fgroup,
(SOR.forderqty - SOR.finvqty
- COALESCE (
DBO.GETSHIPPEDNOTINVOICEDQTY (
SOR.fsono + SOR.finumber + SOR.frelease),
0))
* SOR.funetprice,
SOR.identity_column,
getdate ()
--CONVERT (DATETIME, '02/09/2009')
FROM SORELS SOR
INNER JOIN SOITEM SOI
ON SOR.finvqty <
SOR.forderqty
- COALESCE (
DBO.GETSHIPPEDNOTINVOICEDQTY (
SOI.FSONO + SOR.finumber + SOR.frelease),
0)
- 0
AND SOR.finvqty <
SOR.forderqty
- COALESCE (
DBO.GETSHIPPEDNOTINVOICEDQTY (
SOR.fsono + SOI.FINUMBER + SOR.frelease),
0)
- 0
AND SOR.FSONO = COALESCE (SOI.FSONO, SOI.FSONO)
AND SOR.FINUMBER = SOI.FINUMBER
INNER JOIN SOMAST SOM
ON SOR.finvqty <
SOR.forderqty
- COALESCE (
DBO.GETSHIPPEDNOTINVOICEDQTY (
SOM.FSONO + SOR.finumber + SOR.frelease),
0)
- 0
AND SOR.FSONO = COALESCE (SOM.FSONO, SOM.FSONO)
AND SOI.FSONO = SOM.FSONO
WHERE SOM.FSTATUS = 'OPEN' AND FMASTERREL = COALESCE (0, 0)
AND SOM.forderdate >=
CONVERT (DATETIME, '01/01/2002')
AND SOR.forderqty >
0 + SOR.finvqty
+ COALESCE (
DBO.GETSHIPPEDNOTINVOICEDQTY(SOR.fsono
+ SOR.finumber
+ SOR.frelease),
0)
AND SOR.finvqty <
SOR.forderqty
- COALESCE (
DBO.GETSHIPPEDNOTINVOICEDQTY(SOR.fsono
+ SOR.finumber
+ SOR.frelease),
0)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
正如您所提到的，我将使用存储过程，从作业中调用它。
您还具有可以更新代码但保持作业不变的优势
As you mentioned, I'd use a stored procedure, call that from the job.
You also have the advantage that you can update the code but leave the job untouched
使其成为存储过程。 除了存储过程之外，我永远不会让任何作业运行任何其他内容。 您可以继续在程序中添加逻辑等，而根本不会扰乱工作......
make it a stored procedure. I'd never have any job run anything other than a stored procedure. you can keep adding logic etc. to the procedure and not mess with the job at all...
同意上面的观点 - 一定要把它放在存储过程中并从作业中调用它。
将所有代码放入存储过程中通常是一种很好的做法。 这使您以后可以轻松地按名称调用存储过程（例如，在作业或 DTS 包中使用）。
如果您必须创建多个调用同一存储过程的作业或 DTS 包，并且需要对该存储过程进行更改，则只需更改一次。
如果您只是将代码复制到作业/DTS 包中，则如果需要进行任何更改，则需要进入每个作业来更新代码。
Agree with above - definitely put it in a stored proc and call it from the job.
It's generally good practice to put all code in stored procedures. This allows you to easily call a stored procedure by name later on (for use in either a job or DTS package, for example).
If you ever had to create multiple jobs or DTS packages that call the same stored procedure, and you were required to make changes to that stored procedure, you would only need to make the change once.
If you simply copied the code into the job/DTS package, you would need to go into each and every job to update your code if any changes were ever required.