Showing posts with label generated. Show all posts
Showing posts with label generated. Show all posts

Tuesday, March 27, 2012

Cursor Loop

Hi, I wonder can someone help me. I have a cursor that will drop a number of
wly tables if a monthly table is being generated. I have to keep the
previous ws data but can drop the remaining tables. I have tried it as
below but it will only drop the wk214 table as calculated and does not carry
on to drop wk213,wk212 etc.
Does anybody know how I can acheive this?
Begin
DECLARE lcurTables_To_Be_Deleted CURSOR FOR
SELECT substring(NAME,28,3) FROM SNAPS_ARC..SYSOBJECTS(nolock) WHERE NAME
like @.vchArchiveTable_To_Delete and xtype = 'U' order by crdate desc
OPEN lcurTables_To_Be_Deleted
FETCH lcurTables_To_Be_Deleted into @.TabNoToDrop
SELECT @.TableNumToDrop = convert(int, @.TabNoToDrop) -- wk215
PRINT '1'
PRINT @.TableNumToDrop
SELECT @.TableNumToDrop = + convert(varchar,(@.TableNumToDrop - 1))
PRINT '2' -- wk214
PRINT @.TableNumToDrop
while @.@.fetch_status = 0
begin
EXEC('DROP TABLE SNAPS_ARC..' + @.vchArchiveTable_To_Delete1 + '_'
+ @.TableNumToDrop)
PRINT '_______________________________________
_________'
PRINT @.vchArchiveTable_To_Delete1 + '_' + @.TableNumToDrop + '
Dropped.' PRINT ''
FETCH lcurTables_To_Be_Deleted into @.TableToDrop
end
close lcurTables_To_Be_Deleted
deallocate lcurTables_To_Be_Deleted
EndMake sure you declare the cursor insensitive. Also, did you initialize all
your variables?
Seriously though, why would you want to prolong the life of such a poor
design by developing more code like this? This is so abysmal it breaks just
about every rule in the book: cursors; dynamic SQL; use of system tables;
modifying metadata. Maybe you aren't concerned, but this sort of thing
shouldn't get past even the most basic audit or compliance review.
How about:
DELETE FROM SomeTable
WHERE the_date < @.some_date
Simpler, no?
Take care.
David Portas
SQL Server MVP
--|||I have had this debate before David.
My new model design is in the pipeline with management.
Until then I am stuck with this.
The issue I have is why when the part that calculates the w number is run
it goes into the loop. But it only goes in once. Have I missed something
blatent in the code that prevents it from going into the loop for the other
tables returned from the cursor definition
"David Portas" wrote:

> Make sure you declare the cursor insensitive. Also, did you initialize all
> your variables?
> Seriously though, why would you want to prolong the life of such a poor
> design by developing more code like this? This is so abysmal it breaks jus
t
> about every rule in the book: cursors; dynamic SQL; use of system tables;
> modifying metadata. Maybe you aren't concerned, but this sort of thing
> shouldn't get past even the most basic audit or compliance review.
> How about :
> DELETE FROM SomeTable
> WHERE the_date < @.some_date
> Simpler, no?
> Take care.
> --
> David Portas
> SQL Server MVP
> --
>|||> My new model design is in the pipeline with management.
Good news :-)
I haven't tested it out but I suspect the cursor terminates because dropping
the table deletes the current row. That's why I suggested you declare the
cursor INSENSITIVE. Alternatively, select the table names into a table
variable and iterate through that.
Again, note that you really should avoid system tables where you can. For
this I would prefer to use INFORMATION_SCHEMA.TABLES.
Hope this helps.
David Portas
SQL Server MVP
--
"marcmc" wrote:
> I have had this debate before David.
> My new model design is in the pipeline with management.
> Until then I am stuck with this.
> The issue I have is why when the part that calculates the w number is r
un
> it goes into the loop. But it only goes in once. Have I missed something
> blatent in the code that prevents it from going into the loop for the othe
r
> tables returned from the cursor definition
>
> "David Portas" wrote:
>|||I execute ...
exec uspDB_Maint_Archive_Snaps 'FAT_SNAP_PO_RISK_DETAIL_mth', 216, 'DbName'
Then I declare cursor...
Begin
declare lcurTables_To_Be_Deleted cursor for
select substring(NAME,28,3) from SNAPS_ARC..SYSOBJECTS(nolock) where name
like @.vchArchiveTable_To_Delete and xtype = 'U' order by crdate desc
open lcurTables_To_Be_Deleted
fetch lcurTables_To_Be_Deleted into @.TabNoToDrop
This brings the following into the cursor
--
215
214
213
212
212
I want to keep 215 but delete the rest
I want the next few lines of to do this. But I cant seem to find a way.
Please help...
SELECT @.TableNumToDrop = convert(int, @.TabNoToDrop) -- 215
SELECT @.TableNumToDrop = + convert(varchar,(@.TableNumToDrop - 1))
SELECT @.TableToDrop = @.vchArchiveTable_To_Delete1 + '_' + @.TableNumToDrop
while @.@.fetch_status = 0
begin
PRINT @.vchArchiveTable_To_Delete1 + '_' + @.TabNoToDrop
EXEC('DROP TABLE SNAPS_ARC..' + @.vchArchiveTable_To_Delete1 + '_' +
@.TabNoToDrop)
PRINT '_______________________________________
_________'
PRINT @.vchArchiveTable_To_Delete1 + '_' + @.TabNoToDrop + ' Dropped.'
PRINT ''
FETCH lcurTables_To_Be_Deleted into @.TabNoToDrop
end
close lcurTables_To_Be_Deleted
deallocate lcurTab|||On Mon, 9 May 2005 02:54:01 -0700, marcmc wrote:

>Hi, I wonder can someone help me.
(snip)
Hi marcmc,
Since you are already aware of the downsides of this design, and
battling management to get it changed, I won't comment on that...
In your code, you use three almost but not quite equally named
variables: @.TabNoToDrop, @.TableNumToDrop and @.TableToDrop. The first and
third seem to server the same purpose (the first is used in the first
FETCH and the third in the FETCH in the WHILE loop). The second includes
only a part of the table name and is calculated from the first after the
first FETCH. Since it is not recalculated after the FETCH in the WHILE
loop, it's value never changes, and you are just attempting to drop the
same table over and over again.

>Begin
> DECLARE lcurTables_To_Be_Deleted CURSOR FOR
> SELECT substring(NAME,28,3) FROM SNAPS_ARC..SYSOBJECTS(nolock) WHERE NAME
>like @.vchArchiveTable_To_Delete and xtype = 'U' order by crdate desc
> OPEN lcurTables_To_Be_Deleted
> FETCH lcurTables_To_Be_Deleted into @.TabNoToDrop
> SELECT @.TableNumToDrop = convert(int, @.TabNoToDrop) -- wk215
> PRINT '1'
> PRINT @.TableNumToDrop
> SELECT @.TableNumToDrop = + convert(varchar,(@.TableNumToDrop - 1))
> PRINT '2' -- wk214
> PRINT @.TableNumToDrop
> while @.@.fetch_status = 0
> begin
> EXEC('DROP TABLE SNAPS_ARC..' + @.vchArchiveTable_To_Delete1 + '_'
>+ @.TableNumToDrop)
> PRINT '_______________________________________
_________'
> PRINT @.vchArchiveTable_To_Delete1 + '_' + @.TableNumToDrop + '
>Dropped.' PRINT ''
> FETCH lcurTables_To_Be_Deleted into @.TableToDrop
> end
> close lcurTables_To_Be_Deleted
> deallocate lcurTables_To_Be_Deleted
>End
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Friday, February 17, 2012

CSV from SQL

Hi guys,
I need to get a CSV(comma seperated file) generated for a table on MS SQL Server 2000 database.
Can anyone help me! How should I solve this issue!
Thanks a lot in advance
AsthaIn sql query analyzer after getting the result, File -> save and change the following options
Save as type ".CSV"
Column delimiter as "Comma Separated (CSV)"

Hope it solves your problem

Originally posted by astha_raj
Hi guys,

I need to get a CSV(comma seperated file) generated for a table on MS SQL Server 2000 database.

Can anyone help me! How should I solve this issue!

Thanks a lot in advance
Astha|||Originally posted by abidulla
In sql query analyzer after getting the result, File -> save and change the following options
Save as type ".CSV"
Column delimiter as "Comma Separated (CSV)"

Hope it solves your problem

Thanks for your help - But what I need to actually do is to generate the CSV file for the query result dynamically - Something by which I can create a file to run as a job to do the process automatically at regular intervals - as the table values changes frequently

Can it be done programitacally some how - may be in Cold Fusion or something?

Please guide me if you have any idea.

Thanks anyways
Astha|||I know that you can use the BULK INSERT command to get CSV into a SQL Server table.

I believe that it is just an argument in order to change from an import to an export.

This BULK INSERT command is basically the same thing as BCP, if i understand it correctly..

if this doesnt work, then you can just use BCP throgh the CLI and xp_cmdshell

cheers and good luck|||Use OSQL utility with -s option, refer to BOL for more information.|||Originally posted by Satya
Use OSQL utility with -s option, refer to BOL for more information.

Thank you very much - I was able to do my stuff with bcp utility.

Thanks again

astha|||I have used OSQL many times before to do so. Ofcourse BCP also does the thing.