Excel - Error - .Refresh BackgroundQuery:=False

ramnaidu · Jun 13, 2014

Hi Guys
When I try to Import data from SQL server through VBA, I get below, Can you please help me to eliminate this error.
Error = .Refresh BackgroundQuery:=False

My SQL Script as follows

Code:

DECLARE @Tel_StartDate INTEGER;
DECLARE @Tel_EndDate INTEGER;
SELECT @Tel_StartDate = '20140101';
SELECT @Tel_EndDate =  '20140612';
SELECT
DimDate.[FullDate] as [Date],
DimManpower.[BusinessKey] AS [LAN ID],
DimManpower.[FirstName] + ' ' + DimManpower.[LastName] AS [Agent],
DimManLM.[FirstName] + ' ' + DimManLM.[LastName] AS [LineManager],
DimManOps.[FirstName] + ' ' + DimManOps.[LastName] AS [OpsManager],
DimManpowerDepartment.[DepartmentDescription] AS [Department],
DimManpowerSite.[SiteDescription] AS [Site],
CallGroup.[Name] as [Call Group],
CallType.[Name] as [Call Type],
SUM([Agent_Queue_Day].[Calls_Accepted]) AS [Accepted Calls],
CASE WHEN SUM([Agent_Queue_Day].[Calls_Accepted]) = 0 THEN 0 ELSE CAST(SUM([Agent_Queue_Day].[Time_Seconds_Talk]-[Agent_Queue_Day].[Time_Seconds_Hold]) AS FLOAT) / SUM([Agent_Queue_Day].[Calls_Accepted]) END AS [Avg Engage Time],
CASE WHEN SUM([Agent_Queue_Day].[Calls_Accepted]) = 0 THEN 0 ELSE CAST(SUM([Agent_Queue_Day].[Time_Seconds_Wrap]) AS FLOAT) / SUM([Agent_Queue_Day].[Calls_Accepted]) END AS [Avg Wrap Time],
CASE WHEN SUM([Agent_Queue_Day].[Calls_Accepted]) = 0 THEN 0 ELSE CAST(SUM([Agent_Queue_Day].[Time_Seconds_Handle]) AS FLOAT) / SUM([Agent_Queue_Day].[Calls_Accepted]) END AS [Avg Handle Time]
FROM
[DAVE_PL].[dbo].[FactInboundTelephony_Agent_Queue_Day] [Agent_Queue_Day] WITH(NOLOCK)
INNER JOIN [DAVE_PL].[dbo].[DimDate] ON Agent_Queue_Day.[DimDateKey] = DimDate.[DimDateKey]
INNER JOIN [DAVE_PL].[dbo].[DimSystem] ON Agent_Queue_Day.[DimSystemKey] = DimSystem.[DimSystemKey]
INNER JOIN [DAVE_PL].[dbo].[DimManpower] ON Agent_Queue_Day.[DimManpowerKey] = DimManpower.[DimManpowerKey]
INNER JOIN [DAVE_PL].[dbo].[DimManpowerSite] ON DimManpower.[SiteKey] = DimManpowerSite.[DimManpowerSiteKey]
INNER JOIN [DAVE_PL].[dbo].[DimManpowerDepartment] ON DimManpower.[DepartmentKey] = DimManpowerDepartment.[DimManpowerDepartmentKey]
INNER JOIN [DAVE_PL].[dbo].[DimManpowerActivity] ON DimManpower.[ActivityKey] = DimManpowerActivity.[DimManpowerActivityKey]
INNER JOIN [DAVE_PL].[dbo].[DimManpowerGrade] ON DimManpower.[GradeKey] = DimManpowerGrade.[DimManpowerGradeKey]
INNER JOIN [DAVE_PL].[dbo].[DimManpowerStatus] ON DimManpower.[StatusKey] = DimManpowerStatus.[DimManpowerStatusKey]
INNER JOIN [DAVE_PL].[dbo].[DimManpower] DimManLM ON DimManpower.[LineManagerKey] = DimManLM.[DimManpowerKey]
INNER JOIN [DAVE_PL].[dbo].[DimManpower] DimManOps ON DimManpower.[OpsManagerKey] = DimManOps.[DimManpowerKey]
INNER JOIN [DAVE_PL].[dbo].[DimInboundTelephony_NaturalKeyType] NaturalKeyType ON Agent_Queue_Day.[DimInboundTelephonyNaturalKeyTypeKey] = NaturalKeyType.[DimInboundTelephonyNaturalKeyTypeKey]
INNER JOIN [DAVE_PL].[dbo].[DimInboundTelephony_CallSource] CallSource ON Agent_Queue_Day.[DimInboundTelephony_CallSourceKey] = CallSource.[DimInboundTelephony_CallSourceKey]
INNER JOIN [DAVE_PL].[dbo].[DimInboundTelephony_CallType] CallType ON Agent_Queue_Day.[DimInboundTelephony_CallTypeKey] = CallType.[DimInboundTelephony_CallTypeKey]
INNER JOIN [DAVE_PL].[dbo].[DimInboundTelephony_CallGroup] CallGroup ON Agent_Queue_Day.[DimInboundTelephony_CallGroupKey] = CallGroup.[DimInboundTelephony_CallGroupKey]
INNER JOIN [DAVE_PL].[dbo].[DimInboundTelephony_SubPL] SubPL ON Agent_Queue_Day.[DimInboundTelephony_SubPLKey] = SubPL.[DimInboundTelephony_SubPLKey]
INNER JOIN [DAVE_PL].[dbo].[DimInboundTelephony_PL] PL ON Agent_Queue_Day.[DimInboundTelephony_PLKey] = PL.[DimInboundTelephony_PLKey]
INNER JOIN [DAVE_PL].[dbo].[DimInboundTelephony_BusinessUnit] BusinessUnit ON Agent_Queue_Day.[DimInboundTelephony_BusinessUnitKey] = BusinessUnit.[DimInboundTelephony_BusinessUnitKey]
INNER JOIN [DAVE_PL].[dbo].[DimInboundTelephony_Switch] Switch ON Agent_Queue_Day.[DimInboundTelephony_SwitchKey] = Switch.[DimInboundTelephony_SwitchKey]
WHERE
[Agent_Queue_Day].[DimDateKey] >= @Tel_StartDate
AND [Agent_Queue_Day].[DimDateKey] <= @Tel_EndDate
AND BusinessUnit.[Name] = 'BGS'
AND DimManpowerDepartment.[DepartmentDescription] IN ('Customer Care','Homemove')
AND DimManpowerSite.[SiteDescription] = 'Uddingston'
AND CallGroup.[Name] IN ('BGS_Retention','BGS_HOMEMOVERS')
AND CallType.[Name] IN ('BGS_RETENTION','BGS_RETENTION_ENGINEER','BGS_RETENTION_JCT','BGS_RETENTION_LANDLORDS','BGS_RETENTION_LEG','BGS_RETENTION_ROCI')
AND DimManpowerStatus.[StatusDescription] <> 'Leaver'
GROUP BY
DimDate.[FullDate],
(DimDate.[FullDate] - (datepart(dw, DimDate.[FullDate])-1)),
DimDate.[ISOYear],
DimDate.[ISOWeekOfYear],
DimSystem.[SystemName],
DimManpower.[BusinessKey],
DimManpower.[FirstName] + ' ' + DimManpower.[LastName],
DimManLM.[FirstName] + ' ' + DimManLM.[LastName],
DimManOps.[FirstName] + ' ' + DimManOps.[LastName],
DimManpowerDepartment.[DepartmentDescription],
DimManpowerSite.[SiteDescription],
DimManpowerActivity.[ActivityDescription],
DimManpowerGrade.[GradeDescription],
DimManpowerStatus.[StatusDescription],
BusinessUnit.[Name],
PL.[Name],
SubPL.[Name],
CallGroup.[Name],
CallType.[Name]
ORDER BY
DimDate.[FullDate],
DimSystem.[SystemName],
DimManpower.[BusinessKey],
BusinessUnit.[Name],
PL.[Name],
SubPL.[Name],
CallGroup.[Name],
CallType.[Name]

Colin Legg · Jun 14, 2014

Hi, what is the error number and what is error message?
Does the SQL run without error if you run it directly in management studio?

ramnaidu · Jun 14, 2014

Colin Legg said:
Hi, what is the error number and what is error message?
Does the SQL run without error if you run it directly in management studio?

Hi Colin

Thanks for coming back to me. Error number is 1004, Showing Syntax error.

My sql script is executing perfectly in SSMS. So I assume the error is in VBA code, but I am unable to rectify it as my knowledge is limited in VBA.. Please find my VBA code. Thanks for you help in advance. Please let me know if you need further details.

[
Sub Get_Inbound()

'

Dim theQueryText As String

Dim theQueryTextcomp As String

Dim datestamp As String

Dim current_date As String

Dim i As Long

i = 0

i = i + 1

Application.StatusBar = "Now running query " & i & " "

ThisWorkbook.Activate

Sheets("SQL").Activate

Sheets("SQL").Select

Range("C3").Select

theQueryText = ""

While ActiveCell.Value2 <> ""

theQueryText = theQueryText + ActiveCell.Value2

ActiveCell.Offset(1, 0).Select

Wend

Sheets("DATA").Select

Range("A5").Select

Range("A5:IU65536").ClearContents

With ActiveSheet.QueryTables.Add(Connection:= _

"ODBC;DRIVER=SQL Server;SERVER=CNWSQLH004P01" & ";APP=" & Fname & ";DATABASE=TAMI;Trusted_Connection=yes;" _

, Destination:=Range("A5"))

.Sql = theQueryText

.Refresh BackgroundQuery:=False

End With

'Clear the Status bar

Application.StatusBar = False

End Sub
]

Colin Legg · Jun 14, 2014

A syntax error is a compile time error rather than a runtime error. It means that something you've written doesn't obey the rules the VBA compiler expects.

I don't know if the space between the first two lines of this code just appeared when you copied the code into your post:

Code:

With ActiveSheet.QueryTables.Add(Connection:= _

"ODBC;DRIVER=SQL Server;SERVER=CNWSQLH004P01" & ";APP=" & Fname & ";DATABASE=TAMI;Trusted_Connection=yes;" _
, Destination:=Range("A5"))

but the empty line shouldn't be there. It should be:

Code:

With ActiveSheet.QueryTables.Add(Connection:= _
    "ODBC;DRIVER=SQL Server;SERVER=CNWSQLH004P01" & ";APP=" & fname & ";DATABASE=TAMI;Trusted_Connection=yes;" _
    , Destination:=Range("A5"))

Once you can get your code to run you might find that you also get some runtime errors, but we can deal with those once you've at least got your code up and running.

ramnaidu · Jun 14, 2014

Colin Legg said:
A syntax error is a compile time error rather than a runtime error. It means that something you've written doesn't obey the rules the VBA compiler expects.

I don't know if the space between the first two lines of this code just appeared when you copied the code into your post:

Code:

With ActiveSheet.QueryTables.Add(Connection:= _ "ODBC;DRIVER=SQL Server;SERVER=CNWSQLH004P01" & ";APP=" & Fname & ";DATABASE=TAMI;Trusted_Connection=yes;" _ , Destination:=Range("A5"))

but the empty line shouldn't be there. It should be:

Code:

With ActiveSheet.QueryTables.Add(Connection:= _ "ODBC;DRIVER=SQL Server;SERVER=CNWSQLH004P01" & ";APP=" & fname & ";DATABASE=TAMI;Trusted_Connection=yes;" _ , Destination:=Range("A5"))

Once you can get your code to run you might find that you also get some runtime errors, but we can deal with those once you've at least got your code up and running.

Hi Colin

Space came in middle while I am pasting here. I assume that's not a problem.

My sql scripts states date as Integer where as my VBA states below as a string, will this be a reason for error.

Dim theQueryText As String
Dim theQueryTextcomp As String
Dim datestamp As String
Dim current_date As String

Colin Legg · Jun 14, 2014

I just noticed your connection string is wrong too. I don't see fname declared anywhere so I don't know what it is, but the SQL Server driver needs to be surrounded with braces:

Code:

"ODBC;DRIVER={SQL Server};SERVER=CNWSQLH004P01" etc.....

Colin Legg · Jun 14, 2014

In addition to fixing the connection string by adding the braces around the driver, one other problem might be the way you are reading the range into a string. For example, when you read in the cells which contain these lines:

Code:

WHERE
[Agent_Queue_Day].[DimDateKey] >= @Tel_StartDate
AND [Agent_Queue_Day].[DimDateKey] <= @Tel_EndDate

The resulting string held in the VBA variable will be:

Code:

WHERE[Agent_Queue_Day].[DimDateKey] >= @Tel_StartDateAND [Agent_Queue_Day].[DimDateKey] <= @Tel_EndDate

There are no spaces between the last word and first word of each line which means the SQL isn't valid.

To correct this you could change this bit of VBA code:

Code:

While ActiveCell.Value2 <> ""
     theQueryText = theQueryText + ActiveCell.Value2
     ActiveCell.Offset(1, 0).Select
Wend

to:

Code:

While ActiveCell.Value2 <> ""
     theQueryText = theQueryText & ActiveCell.Value2 & " "
     ActiveCell.Offset(1, 0).Select
Wend

Debug.Print theQueryText

The Debug.Print line will print out the theQueryText into the VBA immediate window so you can check it is valid. You could copy it from there directly into management studio to see if it is what you expect.

ramnaidu · Jun 14, 2014

Colin Legg said:
In addition to fixing the connection string by adding the braces around the driver, one other problem might be the way you are reading the range into a string. For example, when you read in the cells which contain these lines:

Code:

WHERE [Agent_Queue_Day].[DimDateKey] >= @Tel_StartDate AND [Agent_Queue_Day].[DimDateKey] <= @Tel_EndDate

The resulting string held in the VBA variable will be:

Code:

WHERE[Agent_Queue_Day].[DimDateKey] >= @Tel_StartDateAND [Agent_Queue_Day].[DimDateKey] <= @Tel_EndDate

There are no spaces between the last word and first word of each line which means the SQL isn't valid.

To correct this you could change this bit of VBA code:

Code:

While ActiveCell.Value2 <> "" theQueryText = theQueryText + ActiveCell.Value2 ActiveCell.Offset(1, 0).Select Wend

to:

Code:

While ActiveCell.Value2 <> "" theQueryText = theQueryText & ActiveCell.Value2 & " " ActiveCell.Offset(1, 0).Select Wend Debug.Print theQueryText

The Debug.Print line will print out the theQueryText into the VBA immediate window so you can check it is valid. You could copy it from there directly into management studio to see if it is what you expect.

Hi Colin

Thanks for your instant replies Colin, I cant test my code now as my office servers will be restored now. I will test your suggestions as first thing tomorrow and will update to you. Thanks a lot for your help and support. Now I need to go and watch football, hope England will win today.. Have a nice weekend.

Colin Legg · Jun 14, 2014

Me too... 1-1 at half time... a nervous second half ahead!

ramnaidu · Jun 15, 2014

Colin Legg said:
Me too... 1-1 at half time... a nervous second half ahead!

Hi Colin

Hope you are doing good, better luck in our next foorball match.

I am getting the below error mate, an you please help me on below.

Please verify the attached file

ramnaidu · Jun 15, 2014

ramnaidu said:
Hi Colin

Hope you are doing good, better luck in our next foorball match.

I am getting the below error mate, an you please help me on below.

Please verify the attached file

ramnaidu · Jun 15, 2014

ramnaidu said:
Hi Colin

Hope you are doing good, better luck in our next foorball match.

I am getting the below error mate, an you please help me on below.

Please verify the attached file

Hi Colin

Issue has been solved by changing the sql script.

Date has been changed to Datetime from long integer.

Thanks a lot for your help and support mate.

Excel - Error - .Refresh BackgroundQuery:=False

ramnaidu

New Member

Colin Legg

Active Member

ramnaidu

New Member

Colin Legg

Active Member

ramnaidu

New Member

Colin Legg

Active Member

Colin Legg

Active Member

ramnaidu

New Member

Colin Legg

Active Member

Attachments

ramnaidu

New Member

Attachments

ramnaidu

New Member

Attachments

ramnaidu

New Member