cursor usage

hi guys

i have a table that contains a tremendous amount of row. i have written a stored procedure that takes a summary of that information and updates it's master table as well as another table. the problem is it takes very long to do. is what i am doing correct or is there a better way. here is the source

CREATE PROCEDURE update_cvrbatches
AS

declare @.code varchar(25),@.type varchar(3),@.batchno varchar(10),@.batchqty Float,@.issued float,@.returned float,@.transfered float,@.itemcount float,@.totestcost float,@.totactcost float,@.reserved float,@.warehouse varchar(3)

Update cvrwarehouse set BoughtQty =0, IssuedQty = 0, ReservedQty =0 ,Returned = 0 ,Transfered = 0

DECLARE getbatches CURSOR
for
select Code,Type,BatchNo,sum(BoughtQty) as BoughtQty,sum(IssuedQty) as IssuedQty,sum(ReservedQty) as ReservedQty,sum(ReturnedQty) as ReturnedQty,sum(TransferQty) as TransferQty,count(Barcode) as ItemCount,
sum(EstCost) as EstCost,sum(ActCost) as ActCost,Warehouse
from cvrbatches
Group by Code,Type,Colour,Quality,CustomField,BatchNo,Warehouse
OPEN getbatches

FETCH NEXT FROM getbatches into @.code,@.type ,@.batchno ,@.batchqty,@.issued ,@.reserved,@.returned ,@.transfered ,@.itemcount ,@.totestcost ,@.totactcost ,@.warehouse
WHILE @.@.FETCH_STATUS = 0
BEGIN
--doen iets hier

update cvrbatchctrl set BatchQty = @.batchqty, Issued = @.issued, Reserved = @.reserved ,Returned = @.returned ,Transfered = @.transfered, ItemCount = @.itemcount,TotalEstCost = @.totestcost,TotalActCost = @.totactcost
where Code = @.code and Type = @.type and Warehouse = @.warehouse and BatchNo = @.batchno
update cvrwarehouse set BoughtQty =BoughtQty + @.batchqty, IssuedQty = IssuedQty + @.issued, ReservedQty = ReservedQty + @.reserved ,Returned = Returned + @.returned ,Transfered = Transfered + @.transfered
where Code = @.code and Type = @.type and Warehouse = @.warehouse

FETCH NEXT FROM getbatches into @.code,@.type ,@.batchno ,@.batchqty,@.issued ,@.reserved,@.returned ,@.transfered ,@.itemcount ,@.totestcost ,@.totactcost,@.warehouse

END
CLOSE getbatches
DEALLOCATE getbatches

is there a better way?

Hi,

better use setbased solutions rather than cursors, one example would be (*untested*) the below one: (put in a more human readble format)

update cvrbatchctrl
set BatchQty = SUbQuery.BoughtQty,
Issued = SUbQuery.IssuedQty,
Reserved = SUbQuery.ReservedQty,
Returned = SUbQuery.ReservedQty,
Transfered = SUbQuery.TransferQty,
ItemCount = SUbQuery.ItemCount,
TotalEstCost = SUbQuery.EstCost,
TotalActCost = SUbQuery.ActCost
FROM cvrbatchctrl cvr
INNER JOIN
(
select Code,
Type,
BatchNo,
Warehouse,
sum(BoughtQty) as BoughtQty,
sum(IssuedQty) as IssuedQty,
sum(ReservedQty) as ReservedQty,
sum(ReturnedQty) as ReturnedQty,
sum(TransferQty) as TransferQty,
count(Barcode) as ItemCount,
sum(EstCost) as EstCost,
sum(ActCost) as ActCost
from cvrbatches
Group by Code,Type,Colour,Quality,CustomField,BatchNo,Warehouse
) SUbQuery
ON
cvr.Code = SUbQuery.Code and
cvr.Type = SUbQuery.Type and
cvr.Warehouse = SUbQuery.Warehouse and
cvr.BatchNo = SUbQuery.batchno

--Second one, taking the value from the first update (make surethat your condition is complete in the below script)

update cvrwarehouse
set BoughtQty = cvrhouse.BoughtQty + @.batchqty,
IssuedQty = cvrhouse.IssuedQty + @.issued,
ReservedQty = cvrhouse.ReservedQty + @.reserved ,
Returned = cvrhouse.Returned + @.returned ,
Transfered = cvrhouse.Transfered + @.transfered
FROM cvrwarehouse cvrhouse
INNER JOIN cvrbatchctrl cvrbatch
ON
cvrhouse.Code = cvrbatchctrl.Code AND
cvrhouse.Type = cvrbatchctrl.Type
cvrhouse.Warehouse = cvrbatchctrl.Warehouse

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thank you.

haven't tried it yet but it sure makes for an interesting solution.

let you know as soon as i do.

again thanks

|||

i just tested it

works really well and the speed is a lot better

thanks again

Cursor Process Coding Problem

Hi guys,

It's been awhile since I have posted. I have a situation for the group here. I am new to working with cursors. I have a simple one here that I wish to use to update NULL fields in a table called rpt_Scr_B0000_MiniFinancials. I know for a fact that there are NULLs in this table. When I run the select query from the information_schema I get some 60 some odd fields. Anyway, when I run this I get 0 records affected which I know is incorrect. It appears that my cursor is only processing for the first field. I tried changing the @.@.FETCH_STATUS = 0 to @.@.FETCH_STATUS > 0 and that didn't work either. What am I doing wrong? Thx.

DECLARE @.FieldName char (25)

DECLARE cursor_update_rpt_Scr_B0000_MiniFinancials CURSOR For

select column_name
from information_schema.columns
where table_name = 'rpt_Scr_B0000_MiniFinancials'

open cursor_update_rpt_Scr_B0000_MiniFinancials

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

update rpt_Scr_B0000_MiniFinancials
set @.FieldName = 0
where @.FieldName is null

WHILE @.@.FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

END

CLOSE cursor_update_rpt_Scr_B0000_MiniFinancials
DEALLOCATE cursor_update_rpt_Scr_B0000_MiniFinancialsyour update needs to be inside the WHILE Loop

But aren't you worried about datatypes...and what's wrong with nulls annyway

(Here we go again)|||Ok,

I tried that but now I get all zeros. This is a step forward. I have a table with 66 fields and five records. It appears it is now processing each update but there is still something wrong. Say one of the fields, ie 'abc' has 3 records populated leaving two nulls, the two nulls should be turned into zeros. The issue is that the front end programmer creating the view wants me to populate the nulls with zeros. Is it easier if I use a coalesce function in some way instead?

Here is the updated code:

DECLARE @.FieldName char (25)

DECLARE cursor_update_rpt_Scr_B0000_MiniFinancials CURSOR For

select column_name
from information_schema.columns
where table_name = 'rpt_Scr_B0000_MiniFinancials'

open cursor_update_rpt_Scr_B0000_MiniFinancials

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

WHILE @.@.FETCH_STATUS = 0
BEGIN

update rpt_Scr_B0000_MiniFinancials
set @.FieldName = 0
where @.FieldName is null

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

END|||Well I would take a different approach...you do know what happens when you assign a 0 to a datetime column don't you. Anyway, cut and paste this code example into query analyzer...it should run no problem.

USE Northwind
GO

CREATE TABLE myTable99(Col1 int, Col2 char(1), Col3 datetime)
GO

INSERT INTO myTable99(Col1,Col2,Col3)
SELECT 1 , null, '2006-01-01' UNION ALL
SELECT null, 'b' , '2006-01-02' UNION ALL
SELECT 3 , 'c' , null
GO

SELECT * FROM myTable99
GO

DECLARE @.sql varchar(8000), @.collist varchar(8000), @.TABLE_NAME sysname

SET @.TABLE_NAME = 'myTable99'

SELECT @.collist = COALESCE(@.collist+', ','') + COLUMN_NAME + ' = '
+ 'CASE WHEN ' + COLUMN_NAME + ' IS NULL THEN '
+ CASE WHEN DATA_TYPE IN ('char','nchar','varchar','nvarchar','text','ntext ') THEN ''''+'0'+'''' ELSE '0' END
+ ' ELSE ' + COLUMN_NAME + ' END'
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = @.TABLE_NAME

SELECT @.sql = 'UPDATE ' + @.TABLE_NAME + ' SET ' + @.collist

SELECT @.sql

EXEC(@.sql)

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||OK,

I will take a look at your coding. I should have specified that none of the fields is a datetime. They are all money, int, real, or varchars. Thanks again for your time and diligence.

Dave|||We resolved it in-house. Here is the answer! Thanks again.

================================================== =====

DECLARE @.FieldName char (25)
DECLARE cursor_update_rpt_Scr_B0000_MiniFinancials CURSOR For

select column_name
from information_schema.columns
where table_name = 'rpt_Scr_B0000_MiniFinancials'

open cursor_update_rpt_Scr_B0000_MiniFinancials

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

WHILE @.@.FETCH_STATUS = 0
BEGIN

execute('
update rpt_Scr_B0000_MiniFinancials
set '+@.FieldName+' = 0
where '+@.FieldName+' is null
')

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

END

CLOSE cursor_update_rpt_Scr_B0000_MiniFinancials
DEALLOCATE cursor_update_rpt_Scr_B0000_MiniFinancials|||Well use mine anyway and blow their minds...cursors...ech

Besides mine will be faster|||WOuld setting default = 0 on the table structure accomplish what you want.|||I just tried that, one of the other guys here suggested this also. It fails our process due to the fact that there is an update statement we run to perform calculations, ie averaging. If I try to default the table values to zeros it blows up when it reaches this update with a can't divide by zero error. If the process gets to this update with NULLs it is fine as far not erroring out but these NULL fields don't get populated with zeros either. So the way it is set up I must perform a later update to make all NULLs zero. Make sense?

Davesql

cursor + rounding?

Hi Guys,

I've created a cursor inside a function. When I break it down and execute the code piece by piece, no problem. However, try and parse it together and I get an error - 'Mixing old and new syntax is not allowed?' Something to do with the return statements? as I can alter the function to a procedure and it parses fine. Anyone come across this before?

Also, the values are being rounded when putting them into the cursor, even though I've declared the variables the cursor uses specifically as decimal? How can I get around this please?

Cheers,

Michelle

Michelle:

Can you describe what you are doing? This sounds rather vague.

|||

Blast, it didn't post. Umm, I've sorted the first problem, and I think I may have the answer to the second one. Will give it a go and let you know. Thanks for your help!

Cheers,

Michelle

|||

OK, both of those problems fixed. Should have been

DECLARE product_cursor CURSOR local SCROLL

NOT

DECLARE product_cursor SCROLL CURSOR

and Should have been:

decimal(8,2)

NOT

decimal.

Stupid mistakes! However, now I'm able to parse it fine, but am getting:

Msg 443, Level 16, State 15, Procedure CalculateFreight, Line 15

Invalid use of side-effecting or time-dependent operator in 'SELECT INTO' within a function.

Msg 443, Level 16, State 15, Procedure CalculateFreight, Line 25

Invalid use of side-effecting or time-dependent operator in 'UPDATE' within a function.

when I execute it!?

The SELECT INTO and UPDATE statements are fine when executed alone

Michelle

sql

Cube DDL reverse engineer ?

Hi guys

I have built several cubes. However I would need to create local cubes from the built cubes in Analysis Services. As a result I need to work out the DDL for my existing cubes. However the cube contains many mdx calculated members. I was wondering if it would be easier to simply generate the DDL from my existing cube in Analysis Services.

Does anyone know any third party tools that I can use to reverse engineer the DDL from the built cubes in Analysis Services ?

Thanks

Tom

Why not just load it in Server Manager and tell it to Script Database as Create. That should give you the full DDL I believe.|||I want to script the cube not the sql server database.

If there is a way could you please give a more detailed description of the process.

I really appreciate it ! thankyou !

Tom|||The cube is contained in an Analysis Services database, not a SQL Server database. Or are you saying that your AS database contains multiple cubes and you only want to script out one?

Start SQL Server Management Studio
When asked to connect to server drop the Server type down and pick Analysis Services
Expand the databases node.
Right click on a database
Select Script Database as Create To

Alternatively if you want just one cube expadn the cubes node
Right click on the cube you are interested in
Select Script Cube as Create To|||Start SQL Server Management Studio
When asked to connect to server drop the Server type down and pick Analysis Services
Expand the databases node.
Right click on a database
Select Script Database as Create To
__

I've started sql server enterprise manager. When I tried to make a new registration under a sql server group it DOES NOT pick up analysis services. I know they're running on different ports why would sql server manager pickup analysis services ?

can you confirm the process please. I really can't see it working. As there's no possible way for sql server manager to connect to analysis services.

Thankyou
Tom

|||Tom,

I think I see the problem. I'm betting you are using Analysis Services 2000, not Analysis Services 2005...

With SQL Server 2005, the new management tool (called SQL Server Management Studio) replaces Enteprise Manager in SQL Server 2000. SQL Server Management Studio does indeed have the ability to connect to Analysis Services as well as SQL Server. And it has the ability to script the DDL for an entire Analysis Services 2005 database as well as any portion thereof.

However, since you are likely using Analysis Services 2000, this won't work for you. If you need to generate the DDL to create a cube, you'll have to look to some third-party tool options for this as Analysis Manager does not have the ability to generate DDL. Take a look at the short list of options mentioned on Mosha's website at http://www.mosha.com/msolap/util.htm#Metadata. This should at least give you some ideas and options...

Dave Fackler
|||thanks for that at least now I have a good idea what the other person's talking about

Thanks
Tom

CSV string as a SQL In Parameter

Hi Guys,

I am having SQL query whith "IN"

SELECT * FROM Table1 Where ID in ( 1, 2,3)

how can i pass '1,2,3' as a SP parameter.

Thanks

There is a simple way to resolve this
exec ('select *from tblwhere id in (' + @.csv + ')' )
 
where @.csv is your parameter with comma sparated value.
might this helps you.
 
Thanks
 

|||

Hi, 

There is a simple way to resolve this

exec ('select * from tbl where id in ( ' + @.csv + ')' )

where @.csv is your parameter with comma sparated value.

might this helps you.

Thanks

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.