Dynamic SQL being inserted in weird spot

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic SQL being inserted in weird spot

Author

Topic

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-09-25 : 10:36:52

I have a SQL code where I'm fetching data from 3 cursors and eventually dumping all the data to a .csv file. Now I have certain insert statements like so (within the cursors)

insert into AnalyticsV2.dbo.ResultCSVCat values('Generic','','','','','','','','','')
insert into AnalyticsV2.dbo.ResultCSVCat values('Company Inputs','Insurance','Products',@LOB,'"Future Losses, ALAE and SS"','"Catastrophe Losses, ALAE and SS"','Perils',@RegionPeril,'Base Exposure Units','')
insert into AnalyticsV2.dbo.ResultCSVCat values(4,'','','','','','','','','')
insert into AnalyticsV2.dbo.ResultCSVCat values('','','','','','','','','','')

insert into AnalyticsV2.dbo.ResultCSVCat values('Generic','','','','','','','','','')
insert into AnalyticsV2.dbo.ResultCSVCat values('Company Inputs','Insurance','Products',@LOB,'"Future Losses, ALAE and SS"','"Catastrophe Losses, ALAE and SS"','Perils',@RegionPeril,'Event Table',@ModelChoice)
insert into AnalyticsV2.dbo.ResultCSVCat values('Event ID','Poisson frequency','Loss and ALAE','Exposure','Standard Deviation (Independent)','Standard Deviation (Correlated)','','','','')

set @SQL = N'select EVENTID, RATE,' + @LossColumn + N',' + @ExpColumn + N',' + @StdDevIndep + N',' + @StdDevCorr + N', '''','''', '''',''''
from Catastrophe.dbo.' + QUOTENAME(@CatTableName) + N';';
insert into AnalyticsV2.dbo.ResultCSVCat
execute sp_executesql @SQL

where @LOB is being fetched from 1 cursor. The problem I'm having is that if I specify a few LOB's in my cursor like so:

DECLARE c2 CURSOR local READ_ONLY FOR
select RptLOB
from CM.RptLineOfBusiness2
where RptLOB in ('USSIC Aviation','Avemco Aviation')
order by RptLOBPrintOrder

OPEN c2

FETCH NEXT FROM c2 INTO @LOB

WHILE @@FETCH_STATUS = 0
BEGIN

my code works fine. However, if I get rid of the where function and my code runs for all LOB's, the execution of @SQL gets inserted in really weird places and not right after the last insert statement.

There's A LOT A LOT A LOT of data which is why I'm exporting to a .csv file. Any reason to why this problem may happen? Is it due to the amount of data? I'm not sure if anyone has run across this problem.

Also, I'm using the following command to export to a .csv file:

xp_cmdshell 'sqlcmd -S (local) -d AnalyticsV2 -E -s, -W -h-1 -i "E:\Analytics\Data\Database\SQL Server DB\AnalyticsV2\ExportToCSVCat.sql" -o "E:\Analytics\Data\Database\SQL Server DB\AnalyticsV2\Cat.csv" '

where the ExportToCSVCat.sql file is just selecting the 10 columns from the ResultCSVCat table.

If I need to clarify, please let me know.

Any help will be appreciated!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources