"Racer-D" <dpl***@gmail.com> wrote in message
news:1131107315.214252.221520@g43g2000cwa.googlegroups.com...
>I have two queries that I have combined using a join between two
> derived tables and I am getting the error, "Syntax error converting
> from a character string to uniqueidentifier."
>
> I have no idea how to get around this, can anyone help?
>
> Thanks,
>
> dp
>
>
>
> Query below:
>
>
> SELECT a.name1,
>       b.name1
>
> FROM
>
> (
> SELECT T1.[Name] AS 'Name', T2.[Exchange Display Name] AS 'Full Name',
> T0.[User] AS 'User'
> FROM [Inv_AeX_AC_Identification] T1 INNER JOIN
> [Inv_AeX_EU_Contact_Detail] T2
> ON T1.[_ResourceGuid] = T2.[_ResourceGuid] INNER JOIN
> [Inv_AeX_AC_Primary_User] T0
> ON T2.[_ResourceGuid] = T0.[_ResourceGuid] WHERE T0.[Month] = 'October'
> ) AS a
>
> INNER JOIN
>
> (
> SELECT DISTINCT
> COUNT(i.[Name]) [Number of Products],
> i.[Name] [Name],
> s1.Domain [Domain],
> s1.[Logon Name] [Logon],
> t1.[IP Address] [IP Address],
> t1.[Default Gateway] [Gateway],
> t1.[Subnet] [Subnet],
> s1.[System Type] [System Type],
> sn1.[System Manufacturer] [Manufacturer],
> sn1.[Computer Model][Computer Model],
> sn1.[Serial Number] [Serial Number],
> cpu.[Speed] [CPU],
> m1.[Total Physical Memory]/(1024 * 1024) + 1 [Memory MB],
> cop.[OS Name] [Operating System],
> cop.Version [Version],
> o1.[Service Pack] [Service Pack],
> SUBSTRING(o1.[Install Path],1,2) [Installed On],
> l1.[File System] [Format],
> l1.[Size in MBytes] [Size(MB)],
> l1.[Free Space in MBytes] [Free(MB)]
>
> FROM    dbo.Item i
> JOIN dbo.Inv_AeX_HW_Logical_Disk l1
>  ON l1.[_ResourceGuid] = i.Guid
> JOIN dbo.Inv_AeX_OS_Operating_System_spt o1
>  ON  o1.[_ResourceGuid] = i.Guid
> JOIN dbo.Cmn_OS_Operating_System_Common cop
>  ON cop.[_KeyHash] = o1.[_KeyHash]
> JOIN dbo.Inv_AeX_SW_Audit_Software_spt as1
>  ON as1.[_ResourceGuid] = i.Guid
> JOIN dbo.Cmn_SW_Common csw
>  ON csw.[_KeyHash] = as1.[_KeyHash]
> JOIN dbo.Inv_AeX_AC_Identification d
>  ON d.[_ResourceGuid] = i.Guid
> JOIN dbo.CollectionMembership cm
>  ON cm.ResourceGuid = d.[_ResourceGuid]
> JOIN dbo.Item it
>  ON it.Guid = cm.CollectionGuid
> LEFT JOIN dbo.Inv_AeX_HW_Memory m1
>  ON m1.[_ResourceGuid] = i.Guid
> LEFT JOIN dbo.Inv_AeX_HW_CPU_spt p1
>  ON  p1.[_ResourceGuid] = i.Guid
> LEFT JOIN dbo.Cmn_HW_CPU_Common cpu
>  ON cpu.[_KeyHash] = p1.[_KeyHash]
> LEFT JOIN dbo.Inv_AeX_OS_System s1
>  ON s1.[_ResourceGuid] = i.Guid
> LEFT JOIN dbo.Inv_AeX_HW_Serial_Number sn1
>  ON sn1.[_ResourceGuid] = i.Guid
> LEFT JOIN dbo.Inv_AeX_AC_TCPIP t1
>  ON t1.[_ResourceGuid] = i.Guid
> WHERE   l1.[Device ID] = SUBSTRING(o1.[Install Path],1,2)
> AND csw.Manufacturer IS NOT NULL
> AND LEN(csw.Manufacturer) > 1
> AND csw.Manufacturer NOT LIKE '(null)'
> AND csw.KnownAs IS NOT NULL
> AND csw.KnownAs <> ''
> AND csw.KnownAs NOT LIKE '(null)'
> AND csw.ProductVersion IS NOT NULL
> AND csw.ProductVersion <> ''
> AND csw.ProductVersion NOT LIKE '(null)'
> AND d.[System Type] = 'Win32'
> AND i.[Name] LIKE '%ComputerName%'
> AND d.Domain LIKE '%Domain%'
> AND it.[Guid] = '%Collection%'
> GROUP BY
> i.[Name],
> s1.Domain,
> s1.[Logon Name],
> t1.[IP Address],
> t1.[Default Gateway],
> t1.[Subnet],
> s1.[System Type],
> sn1.[System Manufacturer],
> sn1.[Computer Model],
> sn1.[Serial Number],
> cpu.[Speed],
> m1.[Total Physical Memory]/(1024 * 1024) + 1,
> cop.[OS Name],
> cop.Version,
> o1.[Service Pack],
> SUBSTRING(o1.[Install Path],1,2),
> l1.[File System],
> l1.[Size in MBytes],
> l1.[Free Space in MBytes]
> ) AS b
> ON a.Name = b.Name
>

"Racer-D" <dpl***@gmail.com> wrote in message
news:1131126348.348002.33160@g43g2000cwa.googlegroups.com...
> If I have a character field that is joining a uniqueidentifier field;
> how do I get around that. I know I need to join on the result of the
> two derived tables.
>
> Thanks,
>
> Racer-D
>