SQL Command in .CommandText

[Paella1]

I am using a procedure to generate a list/table using an ODBC link and an SQL query. I do this a lot, generally with no problem.

However in this instance my SQL query is very long, and generates a Type Mismatch error at the .CommandText = Array(sql) line below:

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DRIVER=SQL Server;SERVER=myServer;UID=myUserName;PWD=myPassword;APP=Microsoft Office 2010;WSID=myWSID" Destination:=Range("$B$1")).QueryTable
        .CommandText = Array(sql)
        .Refresh BackgroundQuery:=False
    End With

I have found that this method will work when the string is up to 255 characters long, but as soon as the string length hits 256 characters a Type mismatch error occurs.

Is there a way around this?

 

[Fazza]

Hi.

It isn't clear where the 255 character limit is from. (Normally within Excel/Access SQL can be many thousands of characters long, btw.) If that is an SQL server limit, I can't advise.

If not, just enter the SQL string differently - any way other than the current way.

regards

 

[Paella1]

I can avert the character limit by using .CommandText = sql, but there remains a problem in the query itself. It works fine in SQL Server Management Studio but when I send the exact same query via VBA it won't work.

There must be a difference in the SQL implementations which is beyond the scope of this forum.

 

[Fazza]

OK.

If you need to identify the difference, suggest starting from a simple version of the query and successively add an extra bit until it fails. Once the problem is identified, it might be something that can be done differently.

cheers

 

[Paella1]

Thanks Fazza. It's actually quite a complicated query written for me by a SQL developer. I've reverted to my own less elegant query with lots of hard-coding. It's the hard-coding I had hoped to avoid. I guess I'm stuck with it.

 

[Colin Legg]

Hi, what's the original query?

 

[Paella1]

Thanks for your interest Colin. Here 'tis

declare @group_name varchar(60) 
declare @group_names varchar(MAX) 
declare @delim varchar(1) 
declare @sql nvarchar(MAX)
declare @db_cursor as cursor;
	
set @group_names = ' '
set @delim = ' '

set @db_cursor =  cursor for
	SELECT distinct group_name from ip_group_user;
	
open @db_cursor;
fetch next from @db_cursor into @group_name

while @@FETCH_STATUS = 0
begin
	set @group_names =  @group_names + @delim + quotename(@group_name);
	fetch next from @db_cursor into @group_name;
	set @delim = ','
end
close @db_cursor

--print ltrim(@group_names)

set @sql = N'
SELECT
*
FROM
(
select
		user_name, ip_group_user.user_network_name as unn2, group_name
from
		ip_group_user, eflow_users
where
		eflow_users.user_network_name = ip_group_user.user_network_name
and   active = 1
) as temp
PIVOT
(count(unn2) for group_name in (' + ltrim(@group_names) +')) p'

--print @sql

exec sp_executesql @sql

 

[Colin Legg]

Hi,

Try adding the below line in red to your query in the VBA

set nocount on

declare @group_name varchar(60)
declare @group_names varchar(MAX)
declare @delim varchar(1)
declare @sql nvarchar(MAX)
declare @db_cursor as cursor;

Also, as you noted in post #3, you shouldn't be using Array().

 

[Colin Legg]

By the way, you can build that string without using a cursor.

Here's a simple example which works against the AdventureWorks database:

use adventureworks2012;

DECLARE @persontypes VARCHAR(MAX)
;with ctePersonTypes AS
(
Select DISTINCT PersonType from Person.Person
)

SELECT @persontypes = COALESCE(@persontypes + ',','') + QUOTENAME(PersonType) 
FROM ctePersonTypes

PRINT @persontypes

 

[Paella1]

I appreciate this Colin. I'll let you know how it goes. It may be a few days before I get back to it.