Error in my SQL Statement

Question

Post reply

Error in my SQL Statement

Faye Fouladi

SSCrazy

Points: 2770
More actions
April 27, 2011 at 11:05 am

#237217

The following sql statement gives this error:
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near 'AHLTA'.
Msg 319, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Insert Gen
Here is the code:
Declare@Type VARCHAR(5000);
set @Type = NULL;
Declare@Make VARCHAR(5000);
Set @Make = NULL;
Declare@Model VARCHAR(5000);
Set @Model = NULL;
Declare@os VARCHAR(5000);
Set @os = NULL;
Declare@CPU_Speed VARCHAR(5000);
Set @CPU_Speed = NULL;
Declare@Memory VARCHAR(5000);
Set @Memory = NULL;
Declare@hostNameIP VARCHAR(1000);
Declare@softwares VARCHAR(Max);
SET NOCOUNT ON
DECLARE @ColumnValues Varchar(max),@HostNameCheck Varchar(1000),@SerialNumCheck Varchar(1000),@SWNameCheck Varchar(1000)
DECLARE @HostName Varchar(1000),@SerialNum Varchar(1000),@SWName Varchar(1000),@Version Varchar(1000)
DECLARE @VersionValue Varchar(max),@FirstTimeFlag BIT,@Insertquery VARCHAR(1000),@LastValueFlag BIT
DECLARE @WhereClause Varchar(max),@hardwarequery VARCHAR(MAX),@DefaultSoftwares Varchar(8000),@SoftwareQuery Varchar(max)
CREATE TABLE #SoftwareInfo
(
HostName Varchar(1000),
SerialNum Varchar(100),
SWName Varchar(1000),
Version Varchar(1000)
)
CREATE TABLE #TableWithSoftwareValues
(
HOSTNAME VARCHAR(1000),
SerialNum VARCHAR(1000),
AHLTA VARCHAR(1000),
CHCS VARCHAR(1000),
Essentris VARCHAR(1000),
[Dragon NaturallySpeaking] VARCHAR(1000),
[Nuance PDF Professional] VARCHAR(1000),
[As-U-Type] VARCHAR(1000),
[Microsoft OneNote] VARCHAR(1000),
Texter VARCHAR(1000),
MEDCIN VARCHAR(1000),
PDFCreator VARCHAR(1000)
)
CREATE TABLE #HardwareDetails
(
HostName Varchar(600),
SerialNum Varchar(200),
IP_Address Varchar(800),
OS Varchar(800),
[Type] Varchar(800),
Make Varchar(500),
Model Varchar(800),
CPU_Speed Integer,
[Memory_Size(GB)] float,
[Physical_DiskSize(GB)] float,
Image_Date Varchar(800)
)
IF @softwares IS NULL
BEGIN
SET @DefaultSoftwares = '''AHLTA'',''Attachmate Reflection CHCS Companion with Public Keys'',
''Essentris'',''Dragon NaturallySpeaking'',''Nuance PDF Professional'',''as-u-type'',
''Adapx Capturx is Built for Microsoft OneNote 2007'',''Texter'',''MEDCIN'',''PDFCreator'''
SET @softwares = @DefaultSoftwares
END
IF @hostNameIP IS NULL
BEGIN
SET @hostNameIP = 'ca_h.host_name'
END
ELSE
BEGIN
SET @hostNameIP =''+''''+ @hostNameIP+''''+''
END
IF @Make IS NULL
BEGIN
SET @Make = 'ca_h.vendor_name'
END
ELSE
BEGIN
SET @Make =''+''''+ @Make+''''+''
END
IF @Type IS NOT NULL
BEGIN
SET @WhereClause = 'inv_generalinventory_item_1.item_value_text='+ ''''+@Type+''''
END
ELSE
BEGIN
SET @WhereClause = '1=1'
END
IF @Make IS NOT NULL
BEGIN
SET @WhereClause = @WhereClause +' AND ca_h.vendor_name ='+@Make + ''
END
IF @Model IS NOT NULL
BEGIN
SET @WhereClause = @WhereClause +' AND inv_generalinventory_item_5.item_value_text='+ @Model +''
END
IF@os IS NOT NULL
BEGIN
SET @WhereClause = @WhereClause +' AND inv_generalinventory_item.item_value_text='+ ''''+@OS+''''
END
BEGIN
SET @WhereClause = @WhereClause +'AND inv_generalinventory_item_3.item_value_long'+@CPU_Speed+''
END
IF@Memory IS NOT NULL
BEGIN
SET @WhereClause = @WhereClause +' AND ROUND(inv_generalinventory_item_2.item_value_double/1073741824,0)'+@Memory +''
END
IF@hostNameIP IS NOT NULL
BEGIN
SET @WhereClause = @WhereClause +' AND ca_h.host_name ='+ @hostNameIP+ ''
END
IF@softwares IS NOT NULL
BEGIN
SET @WhereClause = @WhereClause +' AND ca_d.NAME IN('+''''+@Softwares+''')'
END
ELSE
BEGIN
SET @WhereClause = @WhereClause +'AND ca_d.NAME IN('+ @DEFAULTSOFTWARES+')'
END
PRINT @WhereClause
SET @SoftwareQuery = 'INSERT INTO #SoftwareInfo
SELECT DISTINCT
ca_h.host_name AS HostName,
ca_h.serial_number AS SerialNum,
ISNULL(ca_d.name,'''') AS SWName,
ISNULL(ca_d.sw_version_label,'''') AS Version
FROM
ca_discovered_hardware ca_h
INNER JOIN
ca_discovered_software ca_s
ON
ca_h.asset_source_uuid = ca_s.asset_source_uuid
INNER JOIN
ca_software_def ca_d
ON
ca_s.sw_def_uuid = ca_d.sw_def_uuid
WHERE
ca_h.host_name = '+ @hostNameIP+' AND
(ca_d.sw_version_label !='''')
AND ca_d.name in ('+''''+@Softwares+''')
AND ca_h.vendor_name = '+@Make+''
EXEC(@SoftwareQuery)
any idea what is wring?
Thank you

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

Faye Fouladi (4/27/2011)
The following sql statement gives this error:...
any idea what is wring?
Thank you

Yep, it took about 4 seconds.

Put a PRINT statement right before the EXEC:

PRINT @SoftwareQuery

and have a look at the query which all that string concatenation generates. One of the strings is bound by an extra quote

AND ca_d.name in (''AHLTA','Attachmate Reflection CHCS Companion with Public Keys',

'Essentris','Dragon NaturallySpeaking','Nuance PDF Professional','as-u-type',

'Adapx Capturx is Built for Microsoft OneNote 2007','Texter','MEDCIN','PDFCreator'')

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Faye Fouladi SSCrazy Points: 2770 More actions · Answer 2

thank you. Can you please tell me which string is bound by an extra quote?

Faye Fouladi SSCrazy Points: 2770 More actions · Answer 3

Faye Fouladi

SSCrazy

Points: 2770

April 27, 2011 at 11:36 am

#1316788

What is the string supposed to be please?

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 4

Faye Fouladi (4/27/2011)
thank you. Can you please tell me which string is bound by an extra quote?