- Posts: 110
- Thank you received: 0
Error when running a Sql Server 2000 script using table variable
- brillsupport
- Topic Author
- Offline
- Premium Member
-
10 years 2 months ago #7623
by brillsupport
I created a transform using the following SQL:
It's a little messy since it requires sql sever 2000 and I have to limit the items being sent to the web service. I determine the customer ship to address based on the table variable I initialize. I keep track of the last customer variable in the loop.
Any way the query works fine in sql server management studio, but I get an error in Advanced ETL Processor (assuming due to the table variable.) Is there any way around this or a way to fix it? I have to use rowcount which necessitates the variable/temp table.
Code:
DECLARE @CustomersToSendPerSoap int
DECLARE @LastCustomer varchar(6)
SET @CustomersToSendPerSoap =
CASE WHEN '<CustomersToSendPerSoap>' = '<Customers' + 'ToSendPerSoap>' THEN 2000
ELSE CAST('<CustomersToSendPerSoap>' AS INT)
END
SET @LastCustomer =
CASE WHEN '<LastCustomer>' = '<Last' + 'Customer>' THEN ''
ELSE '<LastCustomer>'
END
DECLARE @CustomerList TABLE ( ID_CUST varchar(6))
SET rowcount @CustomersToSendPerSoap
INSERT INTO @CustomerList
SELECT ID_CUST FROM CUSMAS_SOLDTO
WHERE ID_CUST > @LastCustomer
SET rowcount 0
SELECT t1.[ID_CUST]
,t1.[SEQ_SHIPTO]
,t1.[NAME_CUST]
,t1.[ADDR_CUST_1]
,t1.[ADDR_CUST_2]
,t1.[CITY]
,t1.[Country]
,t1.[ZIP]
FROM [CUSMAS_SHIPTO] t1
JOIN @CustomerList t2 ON t1.ID_CUST = t2.ID_CUST
It's a little messy since it requires sql sever 2000 and I have to limit the items being sent to the web service. I determine the customer ship to address based on the table variable I initialize. I keep track of the last customer variable in the loop.
Any way the query works fine in sql server management studio, but I get an error in Advanced ETL Processor (assuming due to the table variable.) Is there any way around this or a way to fix it? I have to use rowcount which necessitates the variable/temp table.
Please Log in or Create an account to join the conversation.
- brillsupport
- Topic Author
- Offline
- Premium Member
-
- brillsupport
- Topic Author
- Offline
- Premium Member
-
10 years 1 month ago #7802
by brillsupport
Replied by brillsupport on topic Error when running a Sql Server 2000 script using table variable
Hi Mike,
I ran into an issue using this stored procedure in a transformation. I'm not really sure what changed, but I receive "Access iolation at address 00000000. Read of address 00000000." with the following sql:
I found this thread here and tried use '' instead of '0' for @LastCustomer.
The attached error is with a new transformation, using that sql and trying to use data in the validator with nothing mapped. You can see the data on the bottom left.
I ran into an issue using this stored procedure in a transformation. I'm not really sure what changed, but I receive "Access iolation at address 00000000. Read of address 00000000." with the following sql:
Code:
DECLARE @CustomersToSendPerSoap int
DECLARE @LastCustomer varchar(6)
DECLARE @CompanyCode varchar(3)
SET @CustomersToSendPerSoap =
CASE WHEN '<CustomersToSendPerSoap>' = '<Customers' + 'ToSendPerSoap>' THEN 1000
ELSE CAST('<CustomersToSendPerSoap>' AS INT)
END
SET @LastCustomer =
CASE WHEN '<LastCustomer>' = '<Last' + 'Customer>' THEN '0'
ELSE '<LastCustomer>'
END
SET @CompanyCode =
CASE WHEN '<CompanyCode>' ='<Company' + 'Code>' THEN 'PTC'
ELSE '<CompanyCode>'
END
EXEC BBS_GetCustomersShipTo @CustomersToSendPerSoap, @LastCustomer, @CompanyCode
I found this thread here and tried use '' instead of '0' for @LastCustomer.
The attached error is with a new transformation, using that sql and trying to use data in the validator with nothing mapped. You can see the data on the bottom left.
Please Log in or Create an account to join the conversation.
- brillsupport
- Topic Author
- Offline
- Premium Member
-
