Need Help with the distributed Query

Question

Hi All ,

Iam new to this area and i am having hard time getting the query run can some on please throw some light 

This is part of the SP which tried to import data from excel sheet to a table in the DB i run this script but this never completes nor gives me an error 

DECLARE @vFileNameWithPath   VARCHAR(100) 
        ,@vSQL               NVARCHAR(3000) 


SET @vFileNameWithPath = 'C:\CLADS\Client List Additions  records.xls'

SET @vSQL = 'INSERT INTO clientlist_temp_testDBA 
            ( account) 
            SELECT  [Account]
                  FROM OPENROWSET 
                  (''Microsoft.ACE.OLEDB.12.0'', 
                   ''Excel 8.0;HDR=YES;Database=' + @vFileNameWithPath + ''', 
                   ''SELECT * FROM [Client List$]'')' 

PRINT @vSQL 

BEGIN TRANSACTION

EXEC sp_executesql @vSQL  

But i do the import from the import export wizard every thing works fine ,Please Help 

Thanks in Advance.....

John

Answer 1

Why do you need BEGIN TRANSACTION before executing the sql? Also, have you tried to execute the script which was displayed in the messages tab? Does it work?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Hi Naomi,

This Script is written by the development teams they wanted us to execute the SP and it was took 7 hrs and never completed .

Thanks,

Jack

Answer 3

Can you post the exact script (the end of the script)? Does it end with BEGIN TRANSACTION and no COMMIT TRANSACTION? 

Also, where did you attempt to execute it? Is the file located relative to the server? Do you have an access to the server box directly?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

USE [PR]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[clasxlsupload]
( 
@Server                VARCHAR(100) 
, @Database             VARCHAR(100) 
, @FileName             VARCHAR(100) 
, @ClientListVersion    VARCHAR(100) 
, @msg                  VARCHAR(100) OUTPUT 
) 
AS 

SET NOCOUNT ON 

BEGIN TRY 

DECLARE @MyError       INT 

DECLARE @vFileNameWithPath   VARCHAR(100) 
        ,@vSQL               NVARCHAR(3000) 


SET @vFileNameWithPath = 'C:\CLASXLS\' + @FileName 

SET @vSQL = 'INSERT INTO clientlist_temp 
            ( account 
             ,client_name 
             ,client_number 
             ,max_area 
             ,max_rev_bu 
             ,lob 
             ,sector 
             ,segment 
             ,db_sic_code 
             ,total_client 
             ,audit_client 
             ,adv_client 
             ,tax_client 
             ,max_partner_name 
             ,partner_empl_id 
             ,partner_email 
             ,security 
             ,Fortune_1000 
             ,Fortune_1000_Rank 
             ,Global_Client 
             ,Account_Executive_Email 
             ,Account_Executive_Name 
             ,Account_Executive_Log_On 
             ,PEClient
,Public_Audit_Client
,Global_Approved) 
            SELECT  [Account]
                    ,[Client Name]
                    ,[Client Number]
                    ,[Max Area]
                    ,[Max Rev BU]
                    ,[LOB]
                    ,[Marketing Sector]
                    ,[Marketing Segment]
                    ,[D&B SIC Code]
                    ,[Total Client_>100K]
                    ,[Audit Client_>100K]
                    ,[Adv Client_>100K]
                    ,[Tax Client_>100K]
                    ,[Max Partner Name]
                    ,[Partner EmpID]
                    ,[Partner Email Address]
                    ,[Security]
,[Fortune 1000 (yes/No)]
                    ,[Fortune 1000 Rank]
                    ,[Requested by Global Markets]
                    ,[Account Executive Email]
                    ,[Account Executive Name]
                    ,[Account Executive Logon]
                    ,[PE Client]
                    ,[Public Audit Client]
,[Global Approved]
              FROM OPENROWSET 
                  (''Microsoft.ACE.OLEDB.12.0'', 
                   ''Excel 8.0;HDR=YES;Database=' + @vFileNameWithPath + ''', 
                   ''SELECT * FROM [Client List$]'')' 

PRINT @vSQL 

BEGIN TRANSACTION

EXEC sp_executesql @vSQL 

               
                IF EXISTS ( SELECT * FROM CLIENTLIST_TEMP WITH ( NOLOCK )) 
                BEGIN 
--      BEGIN TRANSACTION 

                    UPDATE clientlist_temp SET clientlist_version = @ClientListVersion FROM clientlist_temp WITH ( NOLOCK ) 

--                UPDATE clientlist_temp SET total_client='True' FROM clientlist_temp WITH (NOLOCK) where total_client<>'' 
                UPDATE clientlist_temp SET total_client = 
                ( CASE 
                        WHEN total_client = 'YES' THEN 'True' 
                        WHEN total_client = 'NO' THEN 'False' 
                        ELSE '' 
                END ) FROM clientlist_temp WITH ( NOLOCK ) 

--                UPDATE clientlist_temp SET audit_client='True' FROM clientlist_temp WITH (NOLOCK) where audit_client<>'' 
                UPDATE clientlist_temp SET audit_client = 
                ( CASE 
                        WHEN audit_client = 'YES' THEN 'True' 
                        WHEN audit_client = 'NO' THEN 'False' 
                        ELSE '' 
                END ) FROM clientlist_temp WITH ( NOLOCK ) 

--                UPDATE clientlist_temp SET adv_client='True' FROM clientlist_temp WITH (NOLOCK) where adv_client<>'' 
                UPDATE clientlist_temp SET adv_client = 
                ( CASE 
                        WHEN adv_client = 'YES' THEN 'True' 
                        WHEN adv_client = 'NO' THEN 'False' 
                        ELSE '' 
                END ) FROM clientlist_temp WITH ( NOLOCK ) 

--                UPDATE clientlist_temp SET tax_client='True' FROM clientlist_temp WITH (NOLOCK) where tax_client<>'' 
                UPDATE clientlist_temp SET tax_client = 
                ( CASE 
                        WHEN tax_client = 'YES' THEN 'True' 
                        WHEN tax_client = 'NO' THEN 'False' 
                        ELSE '' 
                END ) FROM clientlist_temp WITH ( NOLOCK ) 

                UPDATE clientlist_temp SET total_client =NULL FROM clientlist_temp WITH ( NOLOCK ) where total_client = '' 
                UPDATE clientlist_temp SET audit_client =NULL FROM clientlist_temp WITH ( NOLOCK ) where audit_client = '' 
                UPDATE clientlist_temp SET adv_client =NULL FROM clientlist_temp WITH ( NOLOCK ) where adv_client = '' 
                UPDATE clientlist_temp SET tax_client =NULL   FROM clientlist_temp WITH ( NOLOCK ) where tax_client = '' 
                UPDATE clientlist_temp SET status = 'No Response' FROM clientlist_temp WITH ( NOLOCK ) 
                UPDATE clientlist_temp SET approval_selection = 'No Response' FROM clientlist_temp WITH ( NOLOCK ) 

--                UPDATE clientlist_temp SET Fortune_1000='True' FROM clientlist_temp WITH (NOLOCK) where Fortune_1000<>'' 
                UPDATE clientlist_temp SET Fortune_1000 = 
                ( CASE 
                        WHEN Fortune_1000 = 'YES' THEN 'True' 
                        WHEN Fortune_1000 = 'NO' THEN 'False' 
                        ELSE '' 
                END ) FROM clientlist_temp WITH ( NOLOCK ) 

                UPDATE clientlist_temp SET Fortune_1000 =NULL FROM clientlist_temp WITH ( NOLOCK ) where Fortune_1000 = '' 
--                UPDATE clientlist_temp SET Global_Client='True' FROM clientlist_temp WITH (NOLOCK) where Global_Client<>'' 
                UPDATE clientlist_temp SET Global_Client = 
                ( CASE 
                        WHEN Global_Client = 'YES' THEN 'True' 
                        WHEN Global_Client = 'NO' THEN 'False' 
                        ELSE '' 
                END ) FROM clientlist_temp WITH ( NOLOCK ) 

UPDATE clientlist_temp SET public_Audit_Client = 
( CASE 
WHEN public_Audit_Client = 'YES' THEN 'True' 
WHEN public_Audit_Client = 'NO' THEN 'False' 
ELSE '' 
END ) FROM clientlist_temp WITH ( NOLOCK ) 

UPDATE clientlist_temp SET Global_Approved = 
( CASE 
WHEN Global_Approved = 'YES' THEN 'True' 
WHEN Global_Approved = 'NO' THEN 'False' 
ELSE '' 
END ) FROM clientlist_temp WITH ( NOLOCK )

                UPDATE clientlist_temp SET Global_Client =NULL FROM clientlist_temp WITH ( NOLOCK ) where Global_Client = '' 

                UPDATE clientlist_temp SET PEClient = 
                ( CASE 
                        WHEN PEClient = 'YES' THEN 'True' 
                        WHEN PEClient = 'NO' THEN 'False' 
                        ELSE '' 
                END 
                ) FROM clientlist_temp WITH ( NOLOCK )         

                UPDATE clientlist_temp SET PEClient =NULL FROM clientlist_temp WITH ( NOLOCK ) where PEClient = '' 
UPDATE clientlist_temp SET status = 'Approve & Publish' where public_audit_client='True'
UPDATE clientlist_temp SET approval_selection = 'Approve' where public_audit_client='True' or Global_Approved = 'True'
UPDATE clientlist_temp SET status = 'Approve & Publish' where Global_Approved = 'True'and (notes = '' or notes is null)
UPDATE clientlist_temp SET status = 'Approve (Conditions & Notes)' where Global_Approved = 'True'and notes <> ''
                

                INSERT INTO clientlist 
                ( clientlist_version , 
                status , 
                approval_selection , 
                account , 
                client_name , 
                client_name_alias , 
                client_number , 
                max_area , 
                max_rev_bu , 
                lob , 
                sector , 
                segment , 
                db_sic_code , 
                total_client , 
                audit_client , 
                adv_client , 
                tax_client , 
                max_partner_name , 
                correct_lead_partner , 
                partner_empl_id , 
                partner_email , 
                [security] , 
                Fortune_1000 , 
                Fortune_1000_Rank , 
                Global_Client , 
                Account_Executive_Log_On , 
                Account_Executive_Email , 
                Account_Executive_Name , 
                PEClient, 
                public_audit_client,
Global_Approved
                ) 

                SELECT 
                clientlist_version , 
                status , 
                approval_selection , 
                account , 
                client_name , 
                client_name , 
                client_number , 
                max_area , 
                max_rev_bu , 
                lob , 
                sector , 
                segment , 
                db_sic_code , 
                total_client , 
                audit_client , 
                adv_client , 
                tax_client , 
                max_partner_name , 
                max_partner_name , 
                partner_empl_id , 
                partner_email , 
                [security] , 
                Fortune_1000 , 
                Fortune_1000_Rank , 
                Global_Client , 
                Account_Executive_Log_On , 
                Account_Executive_Email , 
                Account_Executive_Name , 
                PEClient ,
public_audit_client,
Global_Approved
                FROM clientlist_temp 
                where clientlist_version = @ClientListVersion and [security] is not NULL 

                Truncate table clientlist_temp 

  COMMIT TRANSACTION 

                END --IF EXISTS (SELECT * FROM CLIENTLIST_TEMP WITH (NOLOCK)) 

END TRY 
BEGIN CATCH 
    SET @MyError = @@ERROR 

    ROLLBACK TRANSACTION 

    --SET @msg = 'Error while saving data in clientlist table.  Error #:' + CAST ( @MyError AS VARCHAR )
SET @msg = ERROR_MESSAGE()
END CATCH 


The main problem is the data is not getting loaded into the temp table .

Answer 5

I see that you have one unmatched BEGIN TRANSACTION (I think the first BEGIN TRANSACTION should not be there).

Also, the script is supposed to print the SQL command. Can you grab it and try to execute separately to see, if that command can be executed?

Also, looks like the CATCH is not re-throwing the error back. So, the error is caught, but nothing to process it. You need to raise it back to pass back to the client. What is the message? Can you do

set @msg = 'Error occurred ' + COALESCE(ERROR_MESSAGE(),'')

Also, are you checking the @msg parameter?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog