SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Dynamic SQL being inserted in weird spot
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kotonikak
Yak Posting Veteran

81 Posts

Posted - 09/25/2012 :  10:36:52  Show Profile  Reply with Quote
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!
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000