April 28, 2011 at 7:23 am
There are actually many Distinct records that should have been inserted into the temp table. However, the select statement returns no records.
Why the insert does not happen?
Declare@hostNameIP VARCHAR(1000);
set @hostNameIP = 'AMEDNBWR01C003X';
Declare@softwares VARCHAR(Max);
set @softwares = 'Essentris';
Declare@Make VARCHAR(5000);
Set @Make = 'LENOVO';
Declare @SoftwareQuery VARCHAR(5000);
CREATE TABLE #SoftwareInfo
(
HostName Varchar(1000),
SerialNum Varchar(100),
SWName Varchar(1000),
Version Varchar(1000)
)
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'''
Select * From #SoftwareInfo
April 28, 2011 at 7:31 am
I see a number of problems here. You are wrapping your variables with '@variablename'. That will not do what you think. Secondly and most importantly I don't see why you need any dynamic sql at all.
Try something like this
INSERT INTO #SoftwareInfo
SELECT DISTINCT
ca_h.host_name,
ca_h.serial_number,
ISNULL(ca_d.name,''),
ISNULL(ca_d.sw_version_label,'')
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) if this needs to handle multiple values this will not work
AND ca_d.name = @softwares
AND ca_h.vendor_name = @Make
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 28, 2011 at 7:34 am
But the answer to your question is because you have built your INSERT statement but haven't executed it.
By the way, if you must use dynamic SQL, I recommend that you look at sp_executeSQL. It's a bit more complicated than just using EXEC, but it greatly mitigates the risk of SQL injection.
John
April 28, 2011 at 7:34 am
Thank you, I will try your code.
April 28, 2011 at 2:04 pm
Your code only works if it is like this:
Declare@hostNameIP VARCHAR(1000);
set @hostNameIP = 'AMEDNBWR01C003X';
Declare@softwares VARCHAR(Max);
set @softwares = NULL;
Declare@Make VARCHAR(5000);
Set @Make = 'LENOVO';
Declare @SoftwareQuery VARCHAR(5000);
CREATE TABLE #SoftwareInfo
(
HostName Varchar(1000),
SerialNum Varchar(100),
SWName Varchar(1000),
Version Varchar(1000)
)
IF (@hostNameIP = '')
BEGIN
SET @hostNameIP = 'ca_h.host_name'
END
ELSE
BEGIN
SET @hostNameIP =''+''''+ @hostNameIP+''''+''
END
IF (@Make = '')
BEGIN
SET @Make = 'ca_h.vendor_name'
END
ELSE
BEGIN
SET @Make =''+''''+ @Make+''''+''
END
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 (''Essentris'')
AND ca_h.vendor_name = '+@Make
EXEC(@SoftwareQuery)
Select * From #SoftwareInfo
Why do I have to code the @hostname like SET @hostNameIP =''+''''+ @hostNameIP+''''+''
It does not work otherwise.
April 28, 2011 at 2:09 pm
I still say drop the dynamic sql. From the code you have posted there is absolutely no need to build a big old nasty string and execute it. Just perform the insert statement like i showed you above.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 28, 2011 at 2:19 pm
See if this gets you closer
Declare @hostNameIP VARCHAR(1000);
set @hostNameIP = 'AMEDNBWR01C003X';
Declare @softwares VARCHAR(Max);
set @softwares = NULL;
Declare @Make VARCHAR(5000);
Set @Make = 'LENOVO';
Declare @SoftwareQuery VARCHAR(5000);
CREATE TABLE #SoftwareInfo
(
HostName Varchar(1000),
SerialNum Varchar(100),
SWName Varchar(1000),
Version Varchar(1000)
)
INSERT INTO #SoftwareInfo
SELECT DISTINCT
ca_h.host_name, ca_h.serial_number, ISNULL(ca_d.name, ''), ISNULL(ca_d.sw_version_label, '')
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 = case @hostNameIP when '' then ca_h.host_name else @hostNameIP end
AND (ca_d.sw_version_label != '')
AND ca_d.name = isnull(@Softwares, ca_d.name
AND ca_h.vendor_name = case @Make when '' then ca_h.vendor_name else @Make end
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 28, 2011 at 5:49 pm
Thank I will try it and let you know.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply