August 18, 2008 at 1:46 pm
Hi.
I'm trying to use the ISNULL function within my sql string:
DECLARE @sqlScript1 VARCHAR(8000)
SET @sqlScript1 = 'SELECT * INTO ##tmpOrders FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'' ,
''Excel 8.0;Database=' + @fileName + ''', ''SELECT [Sale Time] AS SaleTime, Ref, [Item Title] AS ItemTitle, [Item Type] AS ItemType, [Item Id] AS ItemId,
Quantity AS Qty, Amount, [Buyer Id] AS BuyerId, [Email Address] AS EmailAddress, [Day Phone] AS DayPhone, [Evening Phone] AS EveningPhone, [Preferred Contact Time] AS PreferredContactTime,
[First Name] AS FirstName, [Last Name] AS LastName, [Address Line 1] AS AddressLine1, [Address Line 2] AS AddressLine2, [Shipping Locality] AS ShippingLocality, [Postal Code] AS PostalCode,
Country, ISNULL([Order Id], 0) AS OrderId, [Order Amount] AS OrderAmount, [Delivery First Name] AS DeliveryFirstName, [Delivery Last Name] AS DeliveryLastName, [Delivery Address Line 1] AS DeliveryAddressLine1,
[Delivery Address Line 2] AS DeliveryAddressLine2, [Delivery Location] AS DeliveryLocation, [Delivery Postal Code] AS DeliveryPostalCode, [Delivery Country] AS DeliveryCountry, [Delivery Telephone 1] AS DeliveryTelephone1,
[Delivery Telephone 2] AS DeliveryTelephone2, [Online Payment Status] AS OnlinePaymentStatus, [You Rating Buyer] AS YouRatingBuyer, [Buyer Rating You] AS BuyerRatingYou, [SNC Status] AS SNCStatus, [View Item] AS ViewItem,
[View Buyer] AS ViewBuyer, [Email Buyer] AS EmailBuyer, [View Order] AS ViewOrder, [Accept or Reject Credit Card Payments] AS CreditCardPayments, [Rate Buyer] AS RateBuyer, [Reply to Buyer Rating] AS ReplyToBuyerRating,
SNC, [Buyers Note] AS BuyersNote FROM [Sheet1$]'')'
EXEC (@sqlScript1)
SELECT * FROM ##tmpOrders
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "SELECT [Sale Time] AS SaleTime, Ref, [Item Title] AS ItemTitle, [Item Type] AS ItemType, [Item Id] AS ItemId,
Quantity AS Qty, Amount, [Buyer Id] AS BuyerId, [Email Address] AS EmailAddress, [Day Phone] AS DayPhone, [Evening Phone] AS EveningPhone, [Preferred Contact Time] AS PreferredContactTime,
[First Name] AS FirstName, [Last Name] AS LastName, [Address Line 1] AS AddressLine1, [Address Line 2] AS AddressLine2, [Shipping Locality] AS ShippingLocality, [Postal Code] AS PostalCode,
Country, ISNULL([Order Id], 0) AS OrderId, [Order Amount] AS OrderAmount, [Delivery First Name] AS DeliveryFirstName, [Delivery Last Name] AS DeliveryLastName, [Delivery Address Line 1] AS DeliveryAddressLine1,
[Delivery Address Line 2] AS DeliveryAddressLine2, [Delivery Location] AS DeliveryLocation, [Delivery Postal Code] AS DeliveryPostalCode, [Delivery Country] AS DeliveryCountry, [Delivery Telephone 1] AS DeliveryTelephone1,
[Delivery Telephone 2] AS DeliveryTelephone2, [Online Payment Status] AS OnlinePaymentStatus, [You Rating Buyer] AS YouRatingBuyer, [Buyer Rating You] AS BuyerRatingYou, [SNC Status] AS SNCStatus, [View Item] AS ViewItem,
[View Buyer] AS ViewBuyer, [Email Buyer] AS EmailBuyer, [View Order] AS ViewOrder, [Accept or Reject Credit Card Payments] AS CreditCardPayments, [Rate Buyer] AS RateBuyer, [Reply to Buyer Rating] AS ReplyToBuyerRating,
SNC, [Buyers Note] AS BuyersNote FROM [Sheet1$]". The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
Is this allowed or where am I missing something?
You help is highly appreciated.
Thanks.
R
August 18, 2008 at 1:48 pm
do a print of your all your variables (especially @fileName)... i'd guess that it's NULL, causing the entire XL reference to be a NULL reference.
August 18, 2008 at 1:54 pm
Thanks for the reply.
That that cut of my code was a bit incomplete.
Below is a more complete cut:
DECLARE @sqlScript1 VARCHAR(8000)
DECLARE @fileName VARCHAR(8000)
SET @fileName = 'C:\Book1.xls'
SET @sqlScript1 = 'SELECT * INTO ##tmpOrders FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'' ,
''Excel 8.0;Database=' + @fileName + ''', ''SELECT [Sale Time] AS SaleTime, Ref, [Item Title] AS ItemTitle, [Item Type] AS ItemType, [Item Id] AS ItemId,
Quantity AS Qty, Amount, [Buyer Id] AS BuyerId, [Email Address] AS EmailAddress, [Day Phone] AS DayPhone, [Evening Phone] AS EveningPhone, [Preferred Contact Time] AS PreferredContactTime,
[First Name] AS FirstName, [Last Name] AS LastName, [Address Line 1] AS AddressLine1, [Address Line 2] AS AddressLine2, [Shipping Locality] AS ShippingLocality, [Postal Code] AS PostalCode,
Country, [Order Id] AS OrderId, [Order Amount] AS OrderAmount, [Delivery First Name] AS DeliveryFirstName, [Delivery Last Name] AS DeliveryLastName, [Delivery Address Line 1] AS DeliveryAddressLine1,
[Delivery Address Line 2] AS DeliveryAddressLine2, [Delivery Location] AS DeliveryLocation, [Delivery Postal Code] AS DeliveryPostalCode, [Delivery Country] AS DeliveryCountry, [Delivery Telephone 1] AS DeliveryTelephone1,
[Delivery Telephone 2] AS DeliveryTelephone2, [Online Payment Status] AS OnlinePaymentStatus, [You Rating Buyer] AS YouRatingBuyer, [Buyer Rating You] AS BuyerRatingYou, [SNC Status] AS SNCStatus, [View Item] AS ViewItem,
[View Buyer] AS ViewBuyer, [Email Buyer] AS EmailBuyer, [View Order] AS ViewOrder, [Accept or Reject Credit Card Payments] AS CreditCardPayments, [Rate Buyer] AS RateBuyer, [Reply to Buyer Rating] AS ReplyToBuyerRating,
SNC, [Buyers Note] AS BuyersNote FROM [Sheet1$]'')'
EXEC (@sqlScript1)
SELECT * FROM ##tmpOrders
August 18, 2008 at 2:08 pm
Perhaps I should just mention that the query executes just fine when I don't use the ISNULL function. Only as soon as I add it I get the error.
August 18, 2008 at 2:14 pm
are there any string values in the orderid column? what happens if you use "ISNULL([Order Id], '0') AS OrderId" (single quotes around the zero)?
August 18, 2008 at 2:20 pm
Hi Lenny.
I've tried that still no change.
Values in the OrderId column is of int data type.
August 18, 2008 at 2:48 pm
if the query works without the ISNULL, why not let it ride, then UPDATE your temp table after it's been populated? it adds an extra step, sure, but if it works...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply