November 13, 2013 at 3:24 pm
Hi Professionals
I am trying to extract a particular set of columnheadings from the importedquery variable that I am passing in as you can see from the last 3 lines of code at the bottom of my procedure.
However I cannot seem to get the coalesce query right before I can even think of the correct way to extract certain column headings.
I am stuck with a incorrect syntax problem and I have tried everything.
Any ideas where I am going wrong
Alter procedure [dbo].[extractcolheadings]
@importedquery nvarchar(1000)
as
begin
DECLARE @var VARCHAR(4000)
Set @var = 'newtable'
BEGIN
DECLARE @Exec2 NVARCHAR(4000),@sql1 VARCHAR(1000), @sql VARCHAR(1000), @colnames VARCHAR(1000)
-- Need to see where I am going wrong here with the incorrect syntax near ',' --
SET @sql = '"SELECT @colnames = COALESCE(@colnames + ',', '') + column_name from my_db_name.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='+@var+'; select @colnames;" queryout HeadersOnly.csv -c -T -Smy_server_name'
--------------------------------------------------------------------------------
SET @sql1 ='"SELECT column_name from TestData.INFORMATION_SCHEMA.columns where TABLE_NAME='+@var+'; "'
SET @Exec2 = 'exec master.dbo.xp_cmdshell ''bcp '+@sql1+' queryout "C:\inetpub\wwwroot\cleansed\HeadersOnly'+@var+'.csv" -c -k -T -t'''
PRINT @Exec2
EXECUTE sp_executesql @Exec2
END
end
--SELECT column_name from TestData.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='newtable'
--and column_name in ('softwaremanufacturer','productname','licensable');
--exec extractcolheadings 'softwaremanufacturer,productname'
thanks in advance
November 13, 2013 at 3:47 pm
November 13, 2013 at 4:03 pm
Hi Keith
I am now faced with a new error
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'newtable'.
even though that is the name of the table and not a column
???
November 13, 2013 at 10:10 pm
Hi All
ok if the variable @importedquery I pass in equals 'softwaremanufacturer,productname,productversion' then that's the only column headings I will want
I have got this far but its still showing an "Msg 102, Level 15, State 1 Error = Incorrect syntax near ','.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
alter procedure [dbo].[extractnewheadings]
@importedquery nvarchar(1000)
as
begin
DECLARE @colnames VARCHAR(max), @importedquery1 VARCHAR(MAX),@sql1 VARCHAR(1000)
SET @importedquery1 = @importedquery;
SET @sql1 = '"SELECT @colnames = COALESCE(@colnames + ',', '') + column_name from TestData.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='newtable' and column_name in (' + @importedquery1 + '); select @colnames;" queryout C:\inetpub\wwwroot\cleansed\HeadersOnly.csv -c -T -SD3LICSQL02'
EXECUTE sp_executesql @sql1
end
any ideas anyone
November 14, 2013 at 8:35 am
Oracle765 (11/13/2013)
Hi Allok if the variable @importedquery I pass in equals 'softwaremanufacturer,productname,productversion' then that's the only column headings I will want
I have got this far but its still showing an "Msg 102, Level 15, State 1 Error = Incorrect syntax near ','.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
alter procedure [dbo].[extractnewheadings]
@importedquery nvarchar(1000)
as
begin
DECLARE @colnames VARCHAR(max), @importedquery1 VARCHAR(MAX),@sql1 VARCHAR(1000)
SET @importedquery1 = @importedquery;
SET @sql1 = '"SELECT @colnames = COALESCE(@colnames + ',', '') + column_name from TestData.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='newtable' and column_name in (' + @importedquery1 + '); select @colnames;" queryout C:\inetpub\wwwroot\cleansed\HeadersOnly.csv -c -T -SD3LICSQL02'
EXECUTE sp_executesql @sql1
end
any ideas anyone
See my previous post. You need to enclose the comma with 2 single quotes on each side '',''
November 14, 2013 at 3:48 pm
I did do that as shown above and it producted
Msg 102, Level 15, State 1, Procedure extractnewheadings, Line 10
Incorrect syntax near 'newtable'.
here is my amended code
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
alter procedure [dbo].[extractnewheadings]
@importedquery nvarchar(1000)
as
begin
DECLARE @colnames VARCHAR(max), @importedquery1 VARCHAR(MAX),@sql1 VARCHAR(1000)
SET @importedquery1 = @importedquery;
SET @sql1 = '"SELECT @colnames =COALESCE(@colnames + '','', '') + column_name from TestData.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='newtable' and column_name in (' + @importedquery1 + '); select @colnames;" queryout C:\inetpub\wwwroot\cleansed\HeadersOnly.csv -c -T -SD3LICSQL02'
EXECUTE sp_executesql @sql1
end
November 14, 2013 at 3:56 pm
Same issue.
When ever something needs to be enclosed in single quotes make sure you use two when creating the dynamic string i.e.
where TABLE_NAME = 'newTable' and column...<- regular SQL
string = 'where TABLE_NAME = ''newTable'' and column... ' <- dynamic sql
November 14, 2013 at 4:21 pm
Hi Keith
I never knew that you learn something new every day hey.
anyway I have amended it to the following
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
alter procedure [dbo].[extractnewheadings]
@importedquery nvarchar(1000)
as
begin
DECLARE @colnames NVARCHAR(max), @importedquery1 NVARCHAR(MAX),@sql1 NVARCHAR(1000)
SET @importedquery1 = @importedquery;
SET @sql1 = '"SELECT @colnames =COALESCE(@colnames + '','', '') + column_name from TestData.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME="newtable" and column_name in (' + @importedquery1 + '); select @colnames;" queryout C:\inetpub\wwwroot\cleansed\HeadersOnly.csv -c -T -SD3LICSQL02'
EXECUTE sp_executesql @sql1
end
exec extractnewheadings 'softwaremanufacturer,productname'
and it still produces an error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'SELECT @colnames =COALESCE(@colnames + ',', ') + column_name from TestData.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='.
November 14, 2013 at 6:25 pm
Try this:
'"SELECT @colnames =COALESCE(@colnames + '','', '''') + column_name from TestData.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=''newtable'' and column_name in (' + @importedquery1 + '); select @colnames;" queryout C:\inetpub\wwwroot\cleansed\HeadersOnly.csv -c -T -SD3LICSQL02'
November 14, 2013 at 7:02 pm
Hi again Keith
SET @sql1 = '"SELECT @colnames =COALESCE(@colnames + '','', '''') + column_name from TestData.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=''newtable'' and column_name in (' + @importedquery1 + '); select @colnames;" queryout C:\inetpub\wwwroot\cleansed\HeadersOnly.csv -c -T -SD3LICSQL02'
shows
Msg 156, Level 15, State 1, Procedure extractnewheadings, Line 21
Incorrect syntax near the keyword 'COALESCE'.
FYI here is my full code with prints to try and check what the query is doing I am replacing
'softwaremanufacturer,productname,productversion,licensable with 'softwaremanufacturer','productname','productversion','licensable'
for the where column_name IN() part of the statement
alter procedure [dbo].[extractnewheadings]
@importedquery nvarchar(1000)
as
begin
print @importedquery
set @importedquery = REPLACE(@importedquery,',', ''',''' );
print @importedquery
DECLARE @colnames NVARCHAR(max), @importedquery1 NVARCHAR(MAX),@sql1 NVARCHAR(1000)
SET @importedquery1 = @importedquery;
SET @sql1 = '"SELECT @colnames =COALESCE(@colnames + '','', '''') + column_name from TestData.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=''newtable'' and column_name in (' + @importedquery1 + '); select @colnames;" queryout C:\inetpub\wwwroot\cleansed\HeadersOnly.csv -c -T -SD3LICSQL02'
PRINT @sql1
EXECUTE sp_executesql @sql1
end
exec extractnewheadings 'softwaremanufacturer,productname,productversion,licensable'
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply