query equivalent: record

Showing posts with label record. Show all posts

Friday, March 30, 2012

Loss of inserted records during/after an insert

We have a system that records a data record for each cycle of a machine in an MS SQL Server database. These cycles take place approximately once every 10-12 seconds, and there are four stations on the machine, so we are writing approx. 24 records per min
ute. Our database contains four tables, one for each machine station. Each record contains a unique sequential number generated by the machine control software. Data is logged using SQL INSERT scripts in the application (Wonderware) that Operators use
to control the machine. (Wonderware script, BTW is not VBA, but is a proprietary scripting language.)
Everything works fine, UNTIL the one of the stations encounters an operational fault, and stops. This brings up a window on the control screen that requires the Operator to manually enter data, and an UPDATE statement is executed to modify the last recor
d generated. Occasionally when this update is processed, a single record will be lost (never written) in one or more of the data tables.
At first we had all of the records going to one table. Thinking maybe the update for one station was somehow locking an index in the table, we separated the tables so that each station has its own table. Since the station is stopped, no new record is ge
nerated for that station until after the update is processed. The other stations can still be running, so they are generating INSERT commands, which could coincide with the UPDATE command. Both commands use the same connection, which is always open.
We still occasionally lose ONE record in one or more of the other tables when the UPDATE executes.
Any thoughts?
Message posted via http://www.sqlmonster.com
Use the profiler and watch the sql statements - the most likely culprit is a
logic error within the application. Based on your narrative, I would guess
that the problem lies in the error-handling logic.
"Lee Drendall via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:9981fa1e356140a298c4ffa13b629920@.SQLMonster.c om...
> We have a system that records a data record for each cycle of a machine in
an MS SQL Server database. These cycles take place approximately once every
10-12 seconds, and there are four stations on the machine, so we are writing
approx. 24 records per minute. Our database contains four tables, one for
each machine station. Each record contains a unique sequential number
generated by the machine control software. Data is logged using SQL INSERT
scripts in the application (Wonderware) that Operators use to control the
machine. (Wonderware script, BTW is not VBA, but is a proprietary scripting
language.)
> Everything works fine, UNTIL the one of the stations encounters an
operational fault, and stops. This brings up a window on the control screen
that requires the Operator to manually enter data, and an UPDATE statement
is executed to modify the last record generated. Occasionally when this
update is processed, a single record will be lost (never written) in one or
more of the data tables.
> At first we had all of the records going to one table. Thinking maybe the
update for one station was somehow locking an index in the table, we
separated the tables so that each station has its own table. Since the
station is stopped, no new record is generated for that station until after
the update is processed. The other stations can still be running, so they
are generating INSERT commands, which could coincide with the UPDATE
command. Both commands use the same connection, which is always open.
> We still occasionally lose ONE record in one or more of the other tables
when the UPDATE executes.
> Any thoughts?
> --
> Message posted via http://www.sqlmonster.com

Loss of inserted records during/after an insert

We have a system that records a data record for each cycle of a machine in an MS SQL Server database. These cycles take place approximately once every 10-12 seconds, and there are four stations on the machine, so we are writing approx. 24 records per minute. Our database contains four tables, one for each machine station. Each record contains a unique sequential number generated by the machine control software. Data is logged using SQL INSERT scripts in the application (Wonderware) that Operators use to control the machine. (Wonderware script, BTW is not VBA, but is a proprietary scripting language.)
Everything works fine, UNTIL the one of the stations encounters an operational fault, and stops. This brings up a window on the control screen that requires the Operator to manually enter data, and an UPDATE statement is executed to modify the last record generated. Occasionally when this update is processed, a single record will be lost (never written) in one or more of the data tables.
At first we had all of the records going to one table. Thinking maybe the update for one station was somehow locking an index in the table, we separated the tables so that each station has its own table. Since the station is stopped, no new record is generated for that station until after the update is processed. The other stations can still be running, so they are generating INSERT commands, which could coincide with the UPDATE command. Both commands use the same connection, which is always open.
We still occasionally lose ONE record in one or more of the other tables when the UPDATE executes.
Any thoughts?
--
Message posted via http://www.sqlmonster.comUse the profiler and watch the sql statements - the most likely culprit is a
logic error within the application. Based on your narrative, I would guess
that the problem lies in the error-handling logic.
"Lee Drendall via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:9981fa1e356140a298c4ffa13b629920@.SQLMonster.com...
> We have a system that records a data record for each cycle of a machine in
an MS SQL Server database. These cycles take place approximately once every
10-12 seconds, and there are four stations on the machine, so we are writing
approx. 24 records per minute. Our database contains four tables, one for
each machine station. Each record contains a unique sequential number
generated by the machine control software. Data is logged using SQL INSERT
scripts in the application (Wonderware) that Operators use to control the
machine. (Wonderware script, BTW is not VBA, but is a proprietary scripting
language.)
> Everything works fine, UNTIL the one of the stations encounters an
operational fault, and stops. This brings up a window on the control screen
that requires the Operator to manually enter data, and an UPDATE statement
is executed to modify the last record generated. Occasionally when this
update is processed, a single record will be lost (never written) in one or
more of the data tables.
> At first we had all of the records going to one table. Thinking maybe the
update for one station was somehow locking an index in the table, we
separated the tables so that each station has its own table. Since the
station is stopped, no new record is generated for that station until after
the update is processed. The other stations can still be running, so they
are generating INSERT commands, which could coincide with the UPDATE
command. Both commands use the same connection, which is always open.
> We still occasionally lose ONE record in one or more of the other tables
when the UPDATE executes.
> Any thoughts?
> --
> Message posted via http://www.sqlmonster.com

Loss of inserted records during/after an insert

We have a system that records a data record for each cycle of a machine in a
n MS SQL Server database. These cycles take place approximately once every
10-12 seconds, and there are four stations on the machine, so we are writing
approx. 24 records per min
ute. Our database contains four tables, one for each machine station. Each
record contains a unique sequential number generated by the machine control
software. Data is logged using SQL INSERT scripts in the application (Wond
erware) that Operators use
to control the machine. (Wonderware script, BTW is not VBA, but is a proprie
tary scripting language.)
Everything works fine, UNTIL the one of the stations encounters an operation
al fault, and stops. This brings up a window on the control screen that req
uires the Operator to manually enter data, and an UPDATE statement is execut
ed to modify the last recor
d generated. Occasionally when this update is processed, a single record wi
ll be lost (never written) in one or more of the data tables.
At first we had all of the records going to one table. Thinking maybe the u
pdate for one station was somehow locking an index in the table, we separate
d the tables so that each station has its own table. Since the station is s
topped, no new record is ge
nerated for that station until after the update is processed. The other sta
tions can still be running, so they are generating INSERT commands, which co
uld coincide with the UPDATE command. Both commands use the same connection,
which is always open.
We still occasionally lose ONE record in one or more of the other tables whe
n the UPDATE executes.
Any thoughts?
Message posted via http://www.droptable.comUse the profiler and watch the sql statements - the most likely culprit is a
logic error within the application. Based on your narrative, I would guess
that the problem lies in the error-handling logic.
"Lee Drendall via droptable.com" <forum@.droptable.com> wrote in message
news:9981fa1e356140a298c4ffa13b629920@.SQ
droptable.com...
> We have a system that records a data record for each cycle of a machine in
an MS SQL Server database. These cycles take place approximately once every
10-12 seconds, and there are four stations on the machine, so we are writing
approx. 24 records per minute. Our database contains four tables, one for
each machine station. Each record contains a unique sequential number
generated by the machine control software. Data is logged using SQL INSERT
scripts in the application (Wonderware) that Operators use to control the
machine. (Wonderware script, BTW is not VBA, but is a proprietary scripting
language.)
> Everything works fine, UNTIL the one of the stations encounters an
operational fault, and stops. This brings up a window on the control screen
that requires the Operator to manually enter data, and an UPDATE statement
is executed to modify the last record generated. Occasionally when this
update is processed, a single record will be lost (never written) in one or
more of the data tables.
> At first we had all of the records going to one table. Thinking maybe the
update for one station was somehow locking an index in the table, we
separated the tables so that each station has its own table. Since the
station is stopped, no new record is generated for that station until after
the update is processed. The other stations can still be running, so they
are generating INSERT commands, which could coincide with the UPDATE
command. Both commands use the same connection, which is always open.
> We still occasionally lose ONE record in one or more of the other tables
when the UPDATE executes.
> Any thoughts?
> --
> Message posted via http://www.droptable.comsql

Losing record after synchronizing subscriptions

We got a server running SQL server 2000 SP4 with a database that’s being replicated

There are 17 subscribers running MSDE SP4 using merge replication. Replication is started manualy

Initially we tested this with two subscriptions an everything went well, but now, since 3 months, we are facing a weird problem while sync'ing. We have massive data loss on records that where inserted at the subscribers. Records seem to disappear, but only record that have a foreign key constraint. What I mean is that for example a record is inserted at the table that holds our client records with primary key ‘ClientID’ and then a record is inserted in a table with actions for that client with a foreign key ‘ClientID’ referring to the client table. After sync’ing that client record is inserted correctly in database on the publisher but the records in the table with actions are gone.

As far as I know the tables are correctly formed with identity set not for replication and so on.

Shortly, I can’t find any problem, a specially when it doesn’t happen always.

If anyones has faced this and got a solution, please let me know.

Thanks.

Raf

Are there constraint violations that are happening?

Also look at the article property compensate_for_errors. it is turned ON by default. What this does is, say for an insert coming from publisher to subscriber, if there is a constraint violation or some other failure, a delete is sent back to the publisher resulting in data loss.

Set this propery to false and monitor your system for data integrity.

|||

Thanks

I'll check this out

Wednesday, March 28, 2012

Loops and building comma delimited strings

The problem:

I have 2 tables, with a one to many relationship - lets say customers, and order items.

Each order record has a field that is meant to be a comma delimited list (they are reference numbers) that is driven by the quantity field. So, say in the order record, an item has a quantity of 3. The reference number will look like this:

1, 2, 3

And if the next order item for that customer has a quantity of 4, the reference number value is

4, 5, 6, 7

And the final item with quantity of 2:

8, 9

Reference numbers can either be auto assigned (and are in my web application) or manually set. If manually set they will NOT be numeric.

In my web application, it is possible for users to return to a customer's order and edit a line item. My problem is when users changes the quantity of an item, and I have to reset the reference numbers.

If the quantity of line item 2 changes from 4 to 3, I need to reset all the values for that, and any other, order item that comes after it:

4, 5, 6 (2nd)
7,8 (3rd with same quantity of 2).

I felt a cursor would be the best way to handle this. But I am having trouble re-assigning my variable to be the next number in the series when the cursor is running.

This is what I have so far. The print lines and hard coded values are for debugging purposes only.

DECLARE @.NumberingType varchar(10)
DECLARE @.TotalSum int
DECLARE @.DoorLineItemID int
DECLARE @.Quantity int
DECLARE @.SeedInt int

SET @.SeedInt = 1

SELECT @.TotalSum = SUM(Quantity) FROM DoorLineItems WHERE UniversalOrderID = 12345

DECLARE UpdateRefCursor CURSOR FOR
SELECT DoorLineItemID, Quantity FROM DoorLineItems WHERE UniversalOrderID = 12345 AND NumberingType = 1

OPEN UpdateRefCursor

FETCH NEXT FROM UpdateRefCursor INTO @.DoorLineItemID, @.Quantity
DECLARE @.RefNumberLine varchar(1024)
SET @.RefNumberLine = ''

WHILE @.@.FETCH_STATUS = 0
BEGIN

WHILE @.SeedInt <= @.Quantity
BEGIN

SET @.RefNumberLine = @.RefNumberLine + CONVERT(varchar, @.SeedInt, 101) + ', '
SET @.SeedInt = @.SeedInt + 1

END
PRINT @.RefNumberLine

SET @.SeedInt = @.Quantity + @.SeedInt
PRINT 'new seed: ' + CONVERT(varchar, @.SeedInt, 101) + 'Quantity ' + CONVERT(varchar, @.Quantity + @.SeedInt, 101)

FETCH NEXT FROM UpdateRefCursor INTO @.DoorLineItemID, @.Quantity

END

CLOSE UpdateRefCursor
DEALLOCATE UpdateRefCursor

This returns the same delimited string for X number of items. So I'm getting this:

1,2,3
1,2,3
1,2,3

When I really want the results described above.

What am I doing wrong?

Thanks!

You really need to post a table structure and some data for us to use to try this out. That's a lot of variables with no data to reference to try out.|||solved. Thanks for your input.

Monday, March 26, 2012

Looping through rows in flat file

I'd appreciate some guidance on how to solve a problem. I have a flat file of the format

Header1
Header2
Header3
Data Record
Data Record
... etc. ...
Data Record
Trailer1
Trailer2

I want to loop through each data record. I want to take a column value (string) from each row, and call a stored procedure passing in that value. The s/p will return another string which I want to use to update the original column value from the flat file. If I can't update the original column value I'm happy to add a derived column.

Once I've done this "lookup" for all records, I want to re-create the flat file again with the new column values.

I don't need a detailed solution ... just some pointers on how to go about it.

Greg.

On the surface it looks like you should be able to do this (i.e. execute a sproc) using the OLE DB Command transform however I don't think this will work because you want the return value from the sproc put into the pipeline - which you cannot do with the OLE DB Command transform.

Can you not use the LOOKUP transform to get the new values? If all the sproc does is lookup the input value in a mapping table then LOOKUP transform will do the job.

If not - this will be a little harder. But let's eliminate (or otherwise) the option of using the LOOKUP transform first.

-Jamie

|||The sproc parses the incoming string to attempt to extract two distinct values from it. Each of these values are primary key columns in separate tables, both of which are needed to obtain the a pk value from another table, so it's not a straighfoward lookup.

I guess I could do the lookup using custom SQL in the Lookup transform? and re-write the parsing of the input column as a Script Component (I've already done it in C# so should be simple to port to VB.NET)

Apologies as it's a bit of a muddle, but just trying to get my head around a decent solution.

(I've already done this in BizTalk but am looking to use SSIS instead - it's a pity SSIS support for the Web Service tasks is so poor otherwise it would be simpler).

Thanks,

Greg.

|||Did you think about using of staging table? In this scenario you would load the file to a temp (staging table) 1 to 1. Then apply a stored procedure to a temp table that wouold transfer only needed rows to the production table...|||

Custom SQL in the LOOKUP is a good way to go. In fact, best practice states that this is the best thing to do because if you simply select a table you will be selecting unused data into your LOOKUP cache - VERY BAD.

You could probably do the parsing in a derived column component - no need to resort to code (unless you really want to of course). If you need to derive some values in order to do the LOOKUP then there is nothing at all wrong with doing this in the pipeline - that's what the pipeline is for.

Anyway...anything that enables you to use the LOOKUP is definately the way to go otherwise you'll have to hack around it and that isn't nice.

Incidentally, I agree that the Web Services Task is basically useless. I am currently working on a project where we are consuming web services in SSIS left right and centre and we haven't used the Web Services Task once - it is all done in script. Donald Farmer's book contains an example of how to do this.

-Jamie

|||I'd like to avoid a staging table here as these files really only "pass" the database on their way somewhere else, doing a lookup on the way.

Thanks Jamie. I'll have a look at derived columns. Last question for the moment is,

If I have a Script Component and define an InputColumn with Usage Type of Read/Write ... how do I write to it? Do I need to do it in script using Row.ColumnName = "whatever value"?

Greg.

|||

Jamie,

you can get values from a sproc back to the pipeline using OUTPUT parameters... That shouldn't be any problem...

However I aggree that using lookups (perhaps even some lookups behing eachother) will be the better solution...

|||

Thomas Pagel wrote:

Jamie,

you can get values from a sproc back to the pipeline using OUTPUT parameters... That shouldn't be any problem...

However I aggree that using lookups (perhaps even some lookups behing eachother) will be the better solution...

Ah, thanks Thomas. Care to post a demo on your blog? :)

|||

GregAbd wrote:

If I have a Script Component and define an InputColumn with Usage Type of Read/Write ... how do I write to it? Do I need to do it in script using Row.ColumnName = "whatever value"?

Yeah, basically. There's loads of demo code about for doing this. Here's some from my own page - you'll find stuff on Kirk's blog, Ash's blog, SQLIS.com amongst others as well I'm sure:

http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1841.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/04/09/1265.aspx

-Jamie

|||

Jamie,

here you are: http://sqljunkies.com/WebLog/tpagel/archive/2006/01/03/17744.aspx

|||

BTW, in SP1, the Web Services task gets dynamic. :)

|||Thanks for all the help so far folks. In my data flow, I've got my flat file coming in. This goes into a script component which takes one column (string), parses it (complicated) and sets two variables in the PostExecute method. This then goes into a lookup.

How can I use those variables in the custom SQL window? I know I can build a custom SQL query in a variable as an expression and use that in the OLE DB Command, but this doesn't seem possible in the Lookup

FWIW, my lookup is a three table join along the lines of

SELECT FieldB FROM A, B, C
WHERE A.FieldA = Variable1
AND C.FieldC = Variable2
AND A.PK = B.FK1
AND C.PK = B.FK2

Greg.
|||

Greg,

I guess you didn't understand the concept of the Lookup, yet. The lookup takes a query (when the package starts) and caches the result of that query (by default, you can change that...). In your dataflow you define one field (or many) from the pipeline to be matched with the same number of fields in the cache. If there is a match you'll get the other fields of the found record in return.

So you don't have to pass the variables of each record to the lookup. What you have to do is that you store the results of the script not in variables but in new fields in the pipeline. Then you have to match these fields with the result of the complete query in the lookup (so the lookup doesn't have one record but all possible records but you get only the fields of the matching record back to the pipeline).

HTH

|||I was just in the process of realising that when you posted Thomas. It's much clearer now.

SSIS is a pretty steep learning curve in terms of understanding what it's capable of but it's a pretty cool tool to use.

Plenty more questions to come.

Greg.

looping through query result column and PRINT to log file....

i am creating a log file, and at 1 point I record how many rows are deleted after an update process.. I want to also record which productID are actually deleted... how would I write that?!

Basically I need know how I would get the list, and I am using PRINT command... I need to somehow write a loop that works through the list and PRINTS to the file...

Thanks in advanceI got it :)

For any1 who might be interested heres how its done

DECLARE @.NbrList VarChar(300)
SELECT @.NbrList = COALESCE(@.NbrList + ', ', '') + CAST(Invoice AS varchar(30))
from TRANSACTIONS where ProductID = 'CVSDBN'
PRINT @.NbrList

Thanks ;)sql

Looping through one row at a time

Hoping for a little help... I'm attemting to call a stored proc, pass parameters, and display the data 1 record at a time. I need to be able to show the data in a series of lables or text boxes. So the user will see one record, pushed into the lables, click a button and go to the next record...so on and so forth.

I think I have the code to get the data correct, it's the displaying data in lables and looping through the recordset the has me clueless.


Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not Page.IsPostBack Then ' IF This is the first page load
Dim UserID As String = Request.QueryString("UserID")
' parameter for stored procedure
Dim RoleID As String = Request.QueryString("RoleID")Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyCommand As SqlDataAdapter
MyConnection = New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionString"))
MyCommand = New SqlDataAdapter("getdirective", MyConnection)
MyCommand.SelectCommand.CommandType = CommandType.StoredProcedure
MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@.roleID", SqlDbType.NVarChar)).Value = RoleID
Try
DS = New DataSet
MyCommand.Fill(DS)
'Display data in a series of lables or highly formated datagrid
Catch ex As Exception
Response.Write("<font color=red>Error: " & ex.Message & "</font>")
End Try
Else 'IF the page is being reloaded
End If
End Sub
Private Sub cmdAck_Click(...) Handles cmdAck.Click
'This need to loop through the records
End Sub

Thanks for any help!!!For that you need to maintain the total record number in a viewstate. And also for each click (previous, next, ..) you need to add/remove the nos respectively.

Hope it solves your problem.

Looping tables?

Hi, I would like to know if is possible to loop through all tables (via pure
sql statement or SP) on a database and gives me the record count from each
one...
Because I need to know the table wich has more records on it! Can you help
me ?
Thanks!You can use the undocumented sp_foreachtable:
sp_foreachtable ('select ''?'', count (*) from ?')
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
Hi, I would like to know if is possible to loop through all tables (via pure
sql statement or SP) on a database and gives me the record count from each
one...
Because I need to know the table wich has more records on it! Can you help
me ?
Thanks!|||On Dec 27, 5:26=A0pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Hi, I would like to know if is possible to loop through all tables (via pu=re
> sql statement or SP) on a database and gives me the record count from each=
> one...
> Because I need to know the table wich has more records on it! Can you help=
> me ?
> Thanks!
This is a bit of a hack but works for me:
select so.name,rowcnt
from sysindexes si, sysobjects so
where si.id =3D so.id
and so.type in ('U')
and si.status =3D 2066
order by so.name|||On SQL 2005?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escreveu na mensagem
news:e8MQlAISIHA.6036@.TK2MSFTNGP03.phx.gbl...
> You can use the undocumented sp_foreachtable:
> sp_foreachtable ('select ''?'', count (*) from ?')
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>|||Very very good man!
Thanks a lot !
"SB" <othellomy@.yahoo.com> escreveu na mensagem
news:68856583-1664-4038-adfb-ca87e89bbc02@.d4g2000prg.googlegroups.com...
On Dec 27, 5:26 pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
This is a bit of a hack but works for me:
select so.name,rowcnt
from sysindexes si, sysobjects so
where si.id = so.id
and so.type in ('U')
and si.status = 2066
order by so.name|||On Dec 27, 5:00=A0pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> You can use the undocumented sp_foreachtable:
> sp_foreachtable ('select ''?'', count (*) from ?')
> --
> =A0 =A0Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON =A0 Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau=
> "Paulo" <prbs...@.uol.com.br> wrote in message
> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via pu=re
> sql statement or SP) on a database and gives me the record count from each=
> one...
> Because I need to know the table wich has more records on it! Can you help=
> me ?
> Thanks!
That should be
sp_msforeachtable 'select ''?'', count (*) from ?'|||On Dec 27, 5:11=A0pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Very very good man!
> Thanks a lot !
> "SB" <othell...@.yahoo.com> escreveu na mensagemnews:68856583-1664-4038-adf=b-ca87e89bbc02@.d4g2000prg.googlegroups.com...
> On Dec 27, 5:26 pm, "Paulo" <prbs...@.uol.com.br> wrote:
> > Hi, I would like to know if is possible to loop through all tables (via
> > pure
> > sql statement or SP) on a database and gives me the record count from ea=ch
> > one...
> > Because I need to know the table wich has more records on it! Can you he=lp
> > me ?
> > Thanks!
> This is a bit of a hack but works for me:
> select so.name,rowcnt
> from sysindexes si, sysobjects so
> where si.id =3D so.id
> and so.type in ('U')
> and si.status =3D 2066
> order by so.name
Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-t=
o-count-rows-from-a-table.aspx|||It works for 7.0, 2000 and 2005.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:%23RBSYEISIHA.1184@.TK2MSFTNGP04.phx.gbl...
On SQL 2005?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escreveu na mensagem
news:e8MQlAISIHA.6036@.TK2MSFTNGP03.phx.gbl...
> You can use the undocumented sp_foreachtable:
> sp_foreachtable ('select ''?'', count (*) from ?')
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>|||Since I don't like to use undocumented procedures, since they can go away at
any time, here is the script I use for this
/* Start Script */
Create table #tmpRowCounts (
TblName varchar(128),
RowCt int
)
DECLARE crgetrows CURSOR
FOR select name from sysobjects where xtype = 'U' order by name
DECLARE @.tblname varchar(128)
OPEN crgetrows
FETCH NEXT FROM crgetrows INTO @.tblname
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
Execute('
Declare @.rowcount int
select @.rowcount = count(*) from ' + @.tblname + '
Insert into #tmpRowCounts values(''' + @.tblname + ''',@.rowcount)
')
END
FETCH NEXT FROM crgetrows INTO @.tblname
END
CLOSE crgetrows
DEALLOCATE crgetrows
Select * from #tmpRowCounts
Drop table #tmpRowCounts
GO
/* End Script */
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via
> pure sql statement or SP) on a database and gives me the record count from
> each one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>|||> On SQL 2005?
Here's a SQL 2005-specific method:
SELECT
t.name,
SUM(rows) AS Rows
FROM sys.tables t
JOIN sys.partitions p ON
t.object_id = p.object_id
WHERE
p.index_id IN(0,1)
GROUP BY
t.name
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:%23RBSYEISIHA.1184@.TK2MSFTNGP04.phx.gbl...
> On SQL 2005?
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> escreveu na mensagem
> news:e8MQlAISIHA.6036@.TK2MSFTNGP03.phx.gbl...
>> You can use the undocumented sp_foreachtable:
>> sp_foreachtable ('select ''?'', count (*) from ?')
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "Paulo" <prbspfc@.uol.com.br> wrote in message
>> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
>> Hi, I would like to know if is possible to loop through all tables (via
>> pure
>> sql statement or SP) on a database and gives me the record count from
>> each
>> one...
>> Because I need to know the table wich has more records on it! Can you
>> help
>> me ?
>> Thanks!
>>
>|||Take your pick..........
select o.name tablename ,i.rows tblrowcount
from sysobjects o
inner join sysindexes i on (o.id = i.id)
where o.xtype = 'U' and o.name <> 'dtproperties'
and i.indid < 2 Order by tablename
"Paulo" wrote:
> Hi, I would like to know if is possible to loop through all tables (via pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>
>|||sysindexes is not guaranteed to be maintained to actual row count values.
See DBCC UPDATEUSAGE in BOL.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"SB" <othellomy@.yahoo.com> wrote in message
news:68856583-1664-4038-adfb-ca87e89bbc02@.d4g2000prg.googlegroups.com...
On Dec 27, 5:26 pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
This is a bit of a hack but works for me:
select so.name,rowcnt
from sysindexes si, sysobjects so
where si.id = so.id
and so.type in ('U')
and si.status = 2066
order by so.name|||On Dec 27, 8:25=A0pm, DXC <D...@.discussions.microsoft.com> wrote:
> Take your pick..........
> select o.name tablename ,i.rows tblrowcount
> from sysobjects o
> =A0inner join sysindexes i on (o.id =3D i.id)
> where o.xtype =3D 'U' and o.name <> 'dtproperties'
> =A0and i.indid < 2 Order by tablename
>
> "Paulo" wrote:
> > Hi, I would like to know if is possible to loop through all tables (via =pure
> > sql statement or SP) on a database and gives me the record count from ea=ch
> > one...
> > Because I need to know the table wich has more records on it! Can you he=lp
> > me ?
> > Thanks!- Hide quoted text -
> - Show quoted text -
You need to refer this as well
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-t=
o-count-rows-from-a-table.aspx

Looping tables?

Hi, I would like to know if is possible to loop through all tables (via pure
sql statement or SP) on a database and gives me the record count from each
one...
Because I need to know the table wich has more records on it! Can you help
me ?
Thanks!You can use the undocumented sp_foreachtable:
sp_foreachtable ('select ''?'', count (*) from ?')
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
Hi, I would like to know if is possible to loop through all tables (via pure
sql statement or SP) on a database and gives me the record count from each
one...
Because I need to know the table wich has more records on it! Can you help
me ?
Thanks!|||On Dec 27, 5:26=A0pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Hi, I would like to know if is possible to loop through all tables (via pu=[/vbcol
]
re[vbcol=seagreen]
> sql statement or SP) on a database and gives me the record count from each=[/vbcol
]
[vbcol=seagreen]
> one...
> Because I need to know the table wich has more records on it! Can you help=[/vbcol
]
[vbcol=seagreen]
> me ?
> Thanks!
This is a bit of a hack but works for me:
select so.name,rowcnt
from sysindexes si, sysobjects so
where si.id =3D so.id
and so.type in ('U')
and si.status =3D 2066
order by so.name|||On SQL 2005?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escreveu na mensagem
news:e8MQlAISIHA.6036@.TK2MSFTNGP03.phx.gbl...
> You can use the undocumented sp_foreachtable:
> sp_foreachtable ('select ''?'', count (*) from ?')
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>|||Very very good man!
Thanks a lot !
"SB" <othellomy@.yahoo.com> escreveu na mensagem
news:68856583-1664-4038-adfb-ca87e89bbc02@.d4g2000prg.googlegroups.com...
On Dec 27, 5:26 pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
This is a bit of a hack but works for me:
select so.name,rowcnt
from sysindexes si, sysobjects so
where si.id = so.id
and so.type in ('U')
and si.status = 2066
order by so.name|||On Dec 27, 5:00=A0pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> You can use the undocumented sp_foreachtable:
> sp_foreachtable ('select ''?'', count (*) from ?')
> --
> =A0 =A0Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON =A0 Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau=[/vbcol
]
[vbcol=seagreen]
> "Paulo" <prbs...@.uol.com.br> wrote in message
> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via pu=[/vbcol
]
re[vbcol=seagreen]
> sql statement or SP) on a database and gives me the record count from each=[/vbcol
]
[vbcol=seagreen]
> one...
> Because I need to know the table wich has more records on it! Can you help=[/vbcol
]
[vbcol=seagreen]
> me ?
> Thanks!
That should be
sp_msforeachtable 'select ''?'', count (*) from ?'|||It works for 7.0, 2000 and 2005.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:%23RBSYEISIHA.1184@.TK2MSFTNGP04.phx.gbl...
On SQL 2005?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escreveu na mensagem
news:e8MQlAISIHA.6036@.TK2MSFTNGP03.phx.gbl...
> You can use the undocumented sp_foreachtable:
> sp_foreachtable ('select ''?'', count (*) from ?')
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>|||On Dec 27, 5:11=A0pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Very very good man!
> Thanks a lot !
> "SB" <othell...@.yahoo.com> escreveu na mensagemnews:68856583-1664-4038-adf=[/vbcol
]
b-ca87e89bbc02@.d4g2000prg.googlegroups.com...[vbcol=seagreen]
> On Dec 27, 5:26 pm, "Paulo" <prbs...@.uol.com.br> wrote:
>
ch[vbcol=seagreen]
>
lp[vbcol=seagreen]
>
> This is a bit of a hack but works for me:
> select so.name,rowcnt
> from sysindexes si, sysobjects so
> where si.id =3D so.id
> and so.type in ('U')
> and si.status =3D 2066
> order by so.name
Also refer
[url]http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-t=[/url
]
o-count-rows-from-a-table.aspx|||Since I don't like to use undocumented procedures, since they can go away at
any time, here is the script I use for this
/* Start Script */
Create table #tmpRowCounts (
TblName varchar(128),
RowCt int
)
DECLARE crgetrows CURSOR
FOR select name from sysobjects where xtype = 'U' order by name
DECLARE @.tblname varchar(128)
OPEN crgetrows
FETCH NEXT FROM crgetrows INTO @.tblname
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
Execute('
Declare @.rowcount int
select @.rowcount = count(*) from ' + @.tblname + '
Insert into #tmpRowCounts values(''' + @.tblname + ''',@.rowcount)
')
END
FETCH NEXT FROM crgetrows INTO @.tblname
END
CLOSE crgetrows
DEALLOCATE crgetrows
Select * from #tmpRowCounts
Drop table #tmpRowCounts
GO
/* End Script */
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via
> pure sql statement or SP) on a database and gives me the record count from
> each one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>|||> On SQL 2005?
Here's a SQL 2005-specific method:
SELECT
t.name,
SUM(rows) AS Rows
FROM sys.tables t
JOIN sys.partitions p ON
t.object_id = p.object_id
WHERE
p.index_id IN(0,1)
GROUP BY
t.name
Hope this helps.
Dan Guzman
SQL Server MVP
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:%23RBSYEISIHA.1184@.TK2MSFTNGP04.phx.gbl...
> On SQL 2005?
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> escreveu na mensagem
> news:e8MQlAISIHA.6036@.TK2MSFTNGP03.phx.gbl...
>

looping record by record and processing

I have 2 tables they are identical what i need to do is when i make a update on one table the other table needs to reflect that same table. so lets say i insert into table A i need to look in table B if the record exist if it doesnt insert it if it does update it and if it exist in table B and doesnt exist in table A delete the record but i have to do this record by record..can anyone help me with thisUse a trigger...

You'll probably have to do an initial clean up first...|||Please brett can you show me how to do this in code...i have figured out i will need a triger but i have been trying all kinds of stuff and not getting anywhere code wise...

Friday, March 23, 2012

Looping each record

Hi,

My report requirement of customer activity report.

For each customer, my report should show the summary by purchase catgory and then individual transaction details.

Customer Name: Tom Tim

Books: 100

Computers : 2

Others: 23

(then all 125 transactions should be listed).

Same summary and detail should be repeated for each customer in the same single report. (there are aound 200 customers)

How can I achieve this? (other than Sub report). Sub-report seems to be very slow in rendering?

Thanks,

Vasanth

Wouldn't a list work? The list would repeat for each customer.|||Within the list, I am not able to user another table to list the transaction because list allows only group (aggregate) functionalities.|||any ideas pls?

Loopig Each Record in dataset

Hi,
My report requirement of customer activity report.
For each customer, my report should show the summary by purchase
catgory and then individual transaction details.
Customer Name: Tom Tim
Books: 100
Computers : 2
Others: 23
(then all 125 transactions should be listed).
Same summary and detail should be repeated for each customer in the
same single report. (there are aound 200 customers)
How can I achieve this? (other than Sub report). Sub-report seems to be
very slow in rendering? Also If I use List control, it does not allow
table inside list with out aggregate.
So I couldn;t use list control
Thanks,
VasanthSubreport is how you have to solve this. Make sure that you have appropriate
indexing so the subreport query runs quickly.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bvasanth123@.rediffmail.com> wrote in message
news:1137791468.342737.278100@.g47g2000cwa.googlegroups.com...
> Hi,
> My report requirement of customer activity report.
> For each customer, my report should show the summary by purchase
> catgory and then individual transaction details.
> Customer Name: Tom Tim
> Books: 100
> Computers : 2
> Others: 23
> (then all 125 transactions should be listed).
> Same summary and detail should be repeated for each customer in the
> same single report. (there are aound 200 customers)
> How can I achieve this? (other than Sub report). Sub-report seems to be
> very slow in rendering? Also If I use List control, it does not allow
> table inside list with out aggregate.
> So I couldn;t use list control
> Thanks,
> Vasanth
>|||The performance is very poor when we try to render in PDF. We checked
SP (in sub-report) which takes 1-2 seconds for each customer. But
rendering that takes more than 30 mins depending on data.|||Your question was initially about subreports. Based on what you want to do,
subreports are the way to do it. There are differences with rendering
different formats. HTML and CSV are very fast, PDF and Excel are much slower
(order of magnitude slower). Your hypothesis that the problem is the
subreports most likely is not the issue. The issue with PDF is either
complexity or the size of the data (number of rows and size of rows). Also,
do you have any images? If so, what format is the image in. Try the report
without images and see if that helps. If you want to know how much the
optimal time is then render it to html and see how long that takes. That is
the best case.
Is this for a user viewing on the screen or is this destined for printing?
If it is the user then try using drill through. Drill through is quite fast.
Also, does it have to be pdf? As I said, html is much faster.
RS does all rendering in memory. So you can also try adding additional RAM
to see if that would help.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bvasanth123@.rediffmail.com> wrote in message
news:1137851207.701517.281770@.g14g2000cwa.googlegroups.com...
> The performance is very poor when we try to render in PDF. We checked
> SP (in sub-report) which takes 1-2 seconds for each customer. But
> rendering that takes more than 30 mins depending on data.
>|||Thanks Bruces
1) Total records: 20,000 + ( 100 customer * 200 recods average)
2) This is purely for audit record keeping purpose. Not online report
3) We do not have any images
4) HTML report is coming quickly around 10 mins. PDF seems to be the
killer.. Takes for ever. (HTML size is ? 50 MB which we do not prefer)
5) We tried another sample report that just display 20,000+. PDF comes
in 5-10 mins. But if the same 20K+ records have been run with
sub-reports, it goes down in performance.
5) RAM is 4 GB|||RAM is fine. If it takes 10 mins for html and 30 for PDF then you are doing
pretty good. Usually the difference is greater than that. You might be able
to do something to get the data down quicker (playing with indexes) but that
would only affect at most 10 mins of the total time. The extra 20 minutes is
PDF rendering issues. My suggestion is to just schedule it to run at night.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bvasanth123@.rediffmail.com> wrote in message
news:1138029125.861955.154170@.o13g2000cwo.googlegroups.com...
> Thanks Bruces
> 1) Total records: 20,000 + ( 100 customer * 200 recods average)
> 2) This is purely for audit record keeping purpose. Not online report
> 3) We do not have any images
> 4) HTML report is coming quickly around 10 mins. PDF seems to be the
> killer.. Takes for ever. (HTML size is ? 50 MB which we do not prefer)
> 5) We tried another sample report that just display 20,000+. PDF comes
> in 5-10 mins. But if the same 20K+ records have been run with
> sub-reports, it goes down in performance.
> 5) RAM is 4 GB
>

Loop thru a SQL Table in stored proc?

Hello,

Does anyone know of a way to loop thru a SQL table using code in a stored
procedure?

I need to go thru each record in a small table and build a string using
values from the fields associated with a part number, and I can't find any
way to process each record individually. The string needs to be initialized
with the data associated with the 1st record's part number, and I need to
build the string until a new part number is incurred. Once a new part number
is found in the table, the string is written to a different table and reset
for this next part number in the table. Need to repeat until all records in
the table have been processed.

I use ADO in access 2000 to work thru local recordsets, I just can't find
anyway to do this in a stored SQL procedure.

Thanks for any suggestions, Eric.Thanks Erland, that worked in the procedure.

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93BDF3734C7DEYazorman@.127.0.0.1...
> [posted and mailed, please reply in news]
> Eric Martin (ermartin01@.cox.net) writes:
> > Does anyone know of a way to loop thru a SQL table using code in a
stored
> > procedure?
> Most people here know that you should not do this kind of thing, but
> one should always strive for set-based solutions. Then again...
> > I need to go thru each record in a small table and build a string using
> > values from the fields associated with a part number, and I can't find
> > any way to process each record individually. The string needs to be
> > initialized with the data associated with the 1st record's part number,
> > and I need to build the string until a new part number is incurred. Once
> > a new part number is found in the table, the string is written to a
> > different table and reset for this next part number in the table. Need
> > to repeat until all records in the table have been processed.
> This sounds like it be one of the few cases where you need an iterative
> solution. Yet, then again:
> > I use ADO in access 2000 to work thru local recordsets, I just can't
find
> > anyway to do this in a stored SQL procedure.
> Doing this client-side might be just as effecient. So if there is no
> compelling reason for doing this in a stored procedure, you may keep the
> ADO solution - even if it means that the data needs to do some extra
> roundtrips.
> Here is a sample of how such a procedure would look like:
> DECLARE @.partno varchar(10),
> @.lastpartno varchar(10),
> @.otherdata varchar(10),
> @.output varchar(8000),
> @.err int
> DECLARE part_cur CURSOR LOCAL STATIC FOR
> SELECT partno, otherdata FROM tbl ORDER BY partno
> SELECT @.err = @.@.error
> IF @.err <> 0 BEGIN DEALLOCATE part_cur RETURN @.err END
> OPEN part_cur
> WHILE 1 = 1
> BEGIN
> FETCH part_cur INTO @.partno, @.otherdata
> IF @.@.fetch_status <> 0
> BREAK
> IF @.partno <> coalesce(@.lastpartno, '')
> BEGIN
> IF @.lastpartno IS NOT NULL
> BEGIN
> INSERT othertbl (col1) VALUES (@.output)
> SELECT @.err = @.@.error IF @.err <> 0 BREAK
> END
> SELECT @.lastpartno = @.partno, @.output = @.partno
> END
> SELECT @.output = @.output + ', ' + @.otherdata
> END
> DEALLOCATE part_cur
> IF @.err <> 0
> RETURN @.err
> INSERT othertbl (col1) VALUES (@.output)
> SELECT @.err = @.@.error IF @.err <> 0 RETURN @.err
>
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.aspsql

Loop through the dataset

hello All,
This questione pertains to Sql server 2005 reporting services.
Is there a way to browse through each record in the dataset and also make
changes if required before it is rendered on the report?You would have to create a data processing extension which is non-trivial.
Instead, consider basing the value of the cell as an expression. You do not
have to map to a field of the dataset, you can also use expressions (and
expression can call your code too).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Abby" <Abby@.discussions.microsoft.com> wrote in message
news:FAEFF35F-E9C9-4F07-8573-A689C7A12D67@.microsoft.com...
> hello All,
> This questione pertains to Sql server 2005 reporting services.
> Is there a way to browse through each record in the dataset and also make
> changes if required before it is rendered on the report?|||Hi Bruce,
Can you elaborate on the second method u mentioned... In the sense i have
written custom codes and retrived values in the cells using expressions but
how would this work when there is multiple rows of data and i need to do a
comparison between 2 rows of data before deciding what should go in that
field.
Also wouldn't this slow down the process if each cell is calling code to
retrive the value from the db.
"Bruce L-C [MVP]" wrote:
> You would have to create a data processing extension which is non-trivial.
> Instead, consider basing the value of the cell as an expression. You do not
> have to map to a field of the dataset, you can also use expressions (and
> expression can call your code too).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Abby" <Abby@.discussions.microsoft.com> wrote in message
> news:FAEFF35F-E9C9-4F07-8573-A689C7A12D67@.microsoft.com...
> > hello All,
> > This questione pertains to Sql server 2005 reporting services.
> >
> > Is there a way to browse through each record in the dataset and also make
> > changes if required before it is rendered on the report?
>
>|||It won't work for what you want, in expressions you can only look at the
current row of data.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Abby" <Abby@.discussions.microsoft.com> wrote in message
news:6FF60E74-4ADF-428A-A8AD-B07E631A3BDD@.microsoft.com...
> Hi Bruce,
> Can you elaborate on the second method u mentioned... In the sense i have
> written custom codes and retrived values in the cells using expressions
> but
> how would this work when there is multiple rows of data and i need to do a
> comparison between 2 rows of data before deciding what should go in that
> field.
> Also wouldn't this slow down the process if each cell is calling code to
> retrive the value from the db.
>
> "Bruce L-C [MVP]" wrote:
>> You would have to create a data processing extension which is
>> non-trivial.
>> Instead, consider basing the value of the cell as an expression. You do
>> not
>> have to map to a field of the dataset, you can also use expressions (and
>> expression can call your code too).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Abby" <Abby@.discussions.microsoft.com> wrote in message
>> news:FAEFF35F-E9C9-4F07-8573-A689C7A12D67@.microsoft.com...
>> > hello All,
>> > This questione pertains to Sql server 2005 reporting services.
>> >
>> > Is there a way to browse through each record in the dataset and also
>> > make
>> > changes if required before it is rendered on the report?
>>

Wednesday, March 21, 2012

Loop through each record and then each field within each record

I need to essentially do 2 loops. One loops through each record and then inside each record row, I want to perform an insert on each column.

Something like this maybe using a cursor or something else:

For each record in my table (I'll just use the cursor)
For each column in current record for cursor
perform some sql based on the current column value
Next
Next

So below, all I need to do is figure out how to loop through each column for the current record in the cursor

AS

DECLARE Create_Final_Table CURSOR FOR

SELECT FieldName, AcctNumber, Screen, CaseNumber, BKYChapter, FileDate, DispositionCode, BKUDA1, RMSADD2, RMSCHPNAME_1, RMSADDR_1,
RMSCITY_1, RMSSTATECD_1, RMSZIPCODE_1, RMSWORKKPHN, BKYMEETDTE, RMSCMPNAME_2, RMSADDR1_2, RMSCITY_2, RMSSTATECD_2,
RMSZIPCODE_2, RMSHOMEPHN, BARDATE, RMSCMPNAME_3, RMSADD1_2, RMSADD2_3, RMSCITY_3, RMSZIPCODE_3, RMSWORKPHN_2
FROM EBN_TEMP1

OPEN Create_Final_Table

FETCH FROM Create_Final_EBN_Table INTO @.FieldName, @.AcctNumber, @.Screen, @.CaseNumber, @.BKYChapter, @.FileDate, @.DispositionCode, @.BKUDA1, @.RMSADD2, @.RMSCHPNAME_1, @.RMSADDR_1,
@.RMSCITY_1, @.RMSSTATECD_1, @.RMSZIPCODE_1, @.RMSWORKKPHN, @.BKYMEETDTE, @.RMSCMPNAME_2, @.RMSADDR1_2, @.RMSCITY_2, @.RMSSTATECD_2,
@.RMSZIPCODE_2, @.RMSHOMEPHN, @.BARDATE, @.RMSCMPNAME_3, @.RMSADD1_2, @.RMSADD2_3, @.RMSCITY_3, @.RMSZIPCODE_3, @.RMSWORKPHN_2

WHILE @.@.FETCH_STATUS = 0
BEGIN

@.Chapter = chapter for this record

For each column in current record <-- not sure how to code this part is what I'm referring to

do some stuff here using sql for the column I'm on for this row

Next

Case @.Chapter
Case 7

Insert RecoverCodeRecord
Insert Status Code Record
Insert Attorney Code Record

Case 13

Insert Record
Insert Record
Insert Record

Case 11

Insert Record
Insert Record
Insert Record

Case 12

Insert Record
Insert Record
Insert Record

END

close Create_Final_Table
deallocate Create_Final_TableI need to essentially do 2 loops.Light fuse...One loops through each record and then inside each record row...stand back...I want to perform an insert on each column...cover ears...DECLARE Create_Final_Table CURSOR FOR

SELECT FieldName, AcctNumber, Screen, CaseNumber, BKYChapter, FileDate, DispositionCode, BKUDA1, RMSADD2, RMSCHPNAME_1, RMSADDR_1,
RMSCITY_1, RMSSTATECD_1, RMSZIPCODE_1, RMSWORKKPHN, BKYMEETDTE, RMSCMPNAME_2, RMSADDR1_2, RMSCITY_2, RMSSTATECD_2,
RMSZIPCODE_2, RMSHOMEPHN, BARDATE, RMSCMPNAME_3, RMSADD1_2, RMSADD2_3, RMSCITY_3, RMSZIPCODE_3, RMSWORKPHN_2
FROM EBN_TEMP1

OPEN Create_Final_Table

FETCH FROM Create_Final_EBN_Table INTO @.FieldName, @.AcctNumber, @.Screen, @.CaseNumber, @.BKYChapter, @.FileDate, @.DispositionCode, @.BKUDA1, @.RMSADD2, @.RMSCHPNAME_1, @.RMSADDR_1,
@.RMSCITY_1, @.RMSSTATECD_1, @.RMSZIPCODE_1, @.RMSWORKKPHN, @.BKYMEETDTE, @.RMSCMPNAME_2, @.RMSADDR1_2, @.RMSCITY_2, @.RMSSTATECD_2,
@.RMSZIPCODE_2, @.RMSHOMEPHN, @.BARDATE, @.RMSCMPNAME_3, @.RMSADD1_2, @.RMSADD2_3, @.RMSCITY_3, @.RMSZIPCODE_3, @.RMSWORKPHN_2

WHILE @.@.FETCH_STATUS = 0
BEGIN

@.Chapter = chapter for this record

For each column in current record <-- not sure how to code this part is what I'm referring to

do some stuff here using sql for the column I'm on for this row

Next

Case @.Chapter
Case 7

Insert RecoverCodeRecord
Insert Status Code Record
Insert Attorney Code Record

Case 13

Insert Record
Insert Record
Insert Record

Case 11

Insert Record
Insert Record
Insert Record

Case 12

Insert Record
Insert Record
Insert Record

END

close Create_Final_Table
deallocate Create_Final_Table
KABOOM!!!!!!!!!!

Why are you doing this?|||Are you trying to normalize this beast? If so, I'd do one insert operation per column in the original table. Fast, easy, clear, simple... What's not to like?

-PatP

Loop through each record and then each field within each record

Next
Case @.Chapter
Case 7

Insert RecoverCodeRecord
Insert Status Code Record
Insert Attorney Code Record
Case 13
Insert Record
Insert Record
Insert Record
Case 11
Insert Record
Insert Record
Insert Record
Case 12
Insert Record
Insert Record
Insert Record
END
close Create_Final_Table
deallocate Create_Final_Table

Also, if you think there is a better way to do this, let me know.

Are you inserting from EBN_TEMP1 into multiple tables? If so then you can just use series of INSERT...SELECT statements. You need to reference the column you need in each SELECT statement.|||

I have to take every record from my select, cycle through each. So let's say I cycle to the first record in my cursor. I need to then cycle through each field in that row and take that field and do something with it.

Then move on to the next row, cycle through it's fields one by one and so on till I have done this for every row in my cursor. I just don't know how to cycle and reference each column in a unique row after each iteration of my cursor's rows

What I'll be doing wtih each colum is taking the value and inserting it into another table with some other values I'll specify in a select.

|||There must be a way to do a loop to go through each field in a cursor row, but I haven't come up with any and have searched internet forever. This is shocking that nobody has ever brought this up. All they talk about is looping through a cursor's rows or just rows in general, not how to take a row and loop through to do something with every single column (field) in the row. I have a good reason for this need so please don't ask why if you're tempted to.|||

I'm not trying to be rude whatsoever but to me that's inefficient to create multiple inserts and selects. But of course you probably didn't know that those selects and inserts would be inserting the same values, only the field value is changing in the statement at each iteration. So that's why I don't want to basically rewrite the same insert and select. I just need to loop through each and move in the value to a parameter in my insert statement

|||

SQL is not a procedural language so it is best to approach the problem with a set oriented mindset. And this is often hard to do. So if you can perform the operation efficiently using DMLs alone it is much more efficient for the engine and it is also easier for you to maintain the code. Let's take an example. (You have to provide some examples as to what you are doing in the insert. You didn't answer my question about whether you are inserting into multiple tables)

insert into t1 (f1, f2)

select f1, f2 -- any computations on the columns can be done here

from tbl

....

insert into t1 (f3, f4)

select f3, f4 -- any computations on the columns can be done here

from tbl

....

So there is nothing like looping through each column. There simply isn't any construct in TSQL or similar procedural languages in RDBMSes. On the other hand if you want to unpivot the results then you can do that using UNPIVOT operator in SQL Server 2005 or use SQL again. To use SQL to unpivot the operation of converting columns to rows then you can do something like below:

-- traditional SQL way

select f1

from tbl

...

union all

select f2

from tbl

....

-- another less obvious method

select case c.c when 1 then f1 when 2 then f2 end as f

from tbl

cross join (select 1 union all select 2) as c(c)

If you do not want to repeat the query multiple times then you can define a view or inline table-valued function or temporary table or table variables and use it instead. So there are many ways to avoid duplication of code. Best is to describe your problem rather than showing procedural code since there are many ways to perform the same set of operations in SQL much more efficiently and elegantly.

|||

My insert will look lik this and all go into one table because that table will end up being the flat file I create

Insert into table1 'a1', 'b1', @.ColumnName, @.ColumnValue, 'IO'

so for each column in the row, I have to insert it as a separate record into my final table.

Yes, this is inefficient but I have to do this for our stupid ERP system which whose UI only can map updates based on individual field records from a flat file....don't ask me why, it's retarted. they will take my flat file an use it in conjunctiuon with the ERP Import GUI to do so, I just have to create the flat file. Before the process was:

1) receive txt comma delimited file from our vendor

2) Parse it out into an MS Access Table

3) Create an individual record for each column in each row and include the AccountID with it and some other static values

4) save it as a fixed length flat file

Now I'm automating this process for them using SQL Server 2005 Integration Services. My flow is like this:

1) Use Flat File Source to import the comma delimmeted txt file (650,000 records)

2) Use Conditional Split to determine which records to filter out

3) Use OLE DB Destination Editor to move in the records to a table

4) Use a SQL TASK to code the splitting out of each field of each row into a new record in my final table. The final table will be used to create the fixed length flat file in the end.

#4 is what I'm trying to do. I have to include the following fields for each record in my final table:

AccountID, 'a1', 'b1', ColumnName, ColumnValue

So in other words for each row in my table that the OLE DB added my records to, I then have to split out each column for each row into a final table including the account D for every row.

I hope this makes sense, it's not as confusing as it seems.

|||

so expanding on my last post, this may give you a sense:

Let's say the OLE DB moves my records into a table initially for step 3. The table now looks something like this:
Acct # Zip Phone Addr
11223 23232 333-444-5555 6556 Duns Rd.
12345 34343 222-444-3333 1000 Aspire Blvd.
I need to create a record using the Acct # and column for each column as well as append some other values like this into a final table. That final table will be a flat file in the end, I just need to figure out how to get this done first.
11223 23232 othervalue1 othervalue2
11223 333-444-5555 othervalue1 othervalue2
11223 6556 Duns Rd. othervalue1 othervalue2
12345 34343 othervalue1 othervalue2
12345 222-444-3333 othervalue1 othervalue2
12345 1000 Aspire Blvd. othervalue1 othervalue 2

|||If you are using SSIS then there is really no reason to denormalize the data in SQL Server. You can just do it in SSIS. Look at the foreach loop container in SSIS. This should allow you to loop through each column. If you have more questions about SSIS please post in the SQL Server Integration Services forum.|||ok, so then if I use the for each, how do I add my sql statement and have it refer to each column for the row I'm on?|||No. You get the data from the table as is and then perform the transformation on the client side. This is easier to do. For example, if you get a datareader for the results then you can use the columns collection with foreach container and loop through each column. If you post the question in the SSIS forum you will get more solutions.|||thanks so much|||

I am following your advice on an SSIS package I have that must evaluate each record. The issue I am having is that the dataReader destination is far slower then the recordset destination. Problem is I can not figure out how to get data from the record set.

Loop through each record and then each field within each record

Also, if you think there is a better way to do this, let me know.

Are you inserting from EBN_TEMP1 into multiple tables? If so then you can just use series of INSERT...SELECT statements. You need to reference the column you need in each SELECT statement.|||

What I'll be doing wtih each colum is taking the value and inserting it into another table with some other values I'll specify in a select.

|||

insert into t1 (f1, f2)

select f1, f2 -- any computations on the columns can be done here

from tbl

....

insert into t1 (f3, f4)

select f3, f4 -- any computations on the columns can be done here

from tbl

....

-- traditional SQL way

select f1

from tbl

...

union all

select f2

from tbl

....

-- another less obvious method

select case c.c when 1 then f1 when 2 then f2 end as f

from tbl

cross join (select 1 union all select 2) as c(c)

|||

My insert will look lik this and all go into one table because that table will end up being the flat file I create

Insert into table1 'a1', 'b1', @.ColumnName, @.ColumnValue, 'IO'

so for each column in the row, I have to insert it as a separate record into my final table.

1) receive txt comma delimited file from our vendor

2) Parse it out into an MS Access Table

3) Create an individual record for each column in each row and include the AccountID with it and some other static values

4) save it as a fixed length flat file

Now I'm automating this process for them using SQL Server 2005 Integration Services. My flow is like this:

1) Use Flat File Source to import the comma delimmeted txt file (650,000 records)

2) Use Conditional Split to determine which records to filter out

3) Use OLE DB Destination Editor to move in the records to a table

4) Use a SQL TASK to code the splitting out of each field of each row into a new record in my final table. The final table will be used to create the fixed length flat file in the end.

#4 is what I'm trying to do. I have to include the following fields for each record in my final table:

AccountID, 'a1', 'b1', ColumnName, ColumnValue

So in other words for each row in my table that the OLE DB added my records to, I then have to split out each column for each row into a final table including the account D for every row.

I hope this makes sense, it's not as confusing as it seems.

|||

so expanding on my last post, this may give you a sense:

Loop OR dataset in Store procedure

Hello ,
I want in a store procedure in SQL to have a loop.
I asctually want to SELECT something from a table and then
for each record of the results do something (another selection etc ) is
this posible in a store procedure ?
I know how to do this in vb.net but i want it to be really fast so i am
searching for a way to do this in a store procedure
example:
Select distinct(code) from Table1
for each code
select year from table2 where code=code (of the selection
for ...
for ...
next ...
next ...
The above is an example of what i want to do in the store procedure.
Are you sure you can't use a set statemet?
If not take a look at cursors in BOL
http://sqlservercode.blogspot.com/
"savvaschr@.nodalsoft.com.cy" wrote:

> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>
|||You have to lookup CURSORS in the BOL
<savvaschr@.nodalsoft.com.cy> wrote in message
news:1129813884.938532.251580@.g44g2000cwa.googlegr oups.com...
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>
|||If you using SQL Server 2005,
you could do it in SP with CLR code..
<savvaschr@.nodalsoft.com.cy> wrote in message
news:1129813884.938532.251580@.g44g2000cwa.googlegr oups.com...
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>
|||Perhaps. But there's nothing in the original post to indicate that that
would be necessary or desirable. The OP said "for each record ... do
something (another selection etc)". If "do something" means "select or
update some other data" then chances are the simplest and most
efficient solution is to do a join using set-based SQL code. CLR isn't
the natural place to do data retrieval and manipulation.
David Portas
SQL Server MVP
|||savvaschr@.nodalsoft.com.cy wrote:
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc )
> is this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i
> am searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
You can use a temp table and pull information from it one row at a time
in a loop.If you use a cursor, make sure it is read only, forward only,
and local.
David Gugick
Quest Software
www.imceda.com
www.quest.com
sql

Friday, March 30, 2012

Loss of inserted records during/after an insert

Loss of inserted records during/after an insert

Loss of inserted records during/after an insert

Losing record after synchronizing subscriptions

Wednesday, March 28, 2012

Loops and building comma delimited strings

Monday, March 26, 2012

Looping through rows in flat file

looping through query result column and PRINT to log file....

Looping through one row at a time

Looping tables?

Looping tables?

Looping tables?

looping record by record and processing

Friday, March 23, 2012

Looping each record

Loopig Each Record in dataset

Loop thru a SQL Table in stored proc?

Loop through the dataset

Wednesday, March 21, 2012

Loop through each record and then each field within each record

Loop through each record and then each field within each record

Loop through each record and then each field within each record

Loop OR dataset in Store procedure

query equivalent

Blog Archive

About Me