Can I Insert Line Numbers?

Hi. We are creating long reports that need to have unique reference numbers
assigned to each row. For example, the values 1-99 below would be computed at
report run time:
1 Transaction-A
2 Transaction-B
:
99 Transaction-x
I thought I could solve this by using a global variable and function within
the report code block such as:
Private Dim itemCount As Integer
Public Function GetCount() As Integer
itemCount += 1
Return itemCount
End Function
And then create a calculated field (Called LineNumber) that calls the
function:
=Code.GetCount()
But what I get is a seemingly random assignment of line numbers; I assume
due to ReptSvcs resolving the rows in a non-linear fashion.
Any thoughts on how I can get ordered line numbers?Try using static variables in the code
>--Original Message--
>Hi. We are creating long reports that need to have unique
reference numbers
>assigned to each row. For example, the values 1-99 below
would be computed at
>report run time:
>1 Transaction-A
>2 Transaction-B
> :
>99 Transaction-x
>I thought I could solve this by using a global variable
and function within
>the report code block such as:
>Private Dim itemCount As Integer
>Public Function GetCount() As Integer
> itemCount += 1
> Return itemCount
>End Function
>And then create a calculated field (Called LineNumber)
that calls the
>function:
>=Code.GetCount()
>But what I get is a seemingly random assignment of line
numbers; I assume
>due to ReptSvcs resolving the rows in a non-linear
fashion.
>Any thoughts on how I can get ordered line numbers?
>
>
>.
>|||The problem with statics in embedded code is that they are shared
among all instances of the report that are running. If two of the
reports using the static execute at the same time the results could
interleave.
--
Scott
http://www.OdeToCode.com
On Sat, 4 Sep 2004 11:51:17 -0700, "Ravi" <ravikantkv@.rediffmail.com>
wrote:
>Try using static variables in the code|||Thanks for the tips, but I think I got it working.
I used the same code as I mentioned below, but added an "ORDER BY"
quailifier to the dataset to sort the data in the same sequence as the report
displayed. This gave me sequentual numbers.
"Scott Allen" wrote:
> The problem with statics in embedded code is that they are shared
> among all instances of the report that are running. If two of the
> reports using the static execute at the same time the results could
> interleave.
> --
> Scott
> http://www.OdeToCode.com
> On Sat, 4 Sep 2004 11:51:17 -0700, "Ravi" <ravikantkv@.rediffmail.com>
> wrote:
> >Try using static variables in the code
>sql

Can I insert into the same table a new row with the "old" row field value?

I'm trying to insert values into the same database from itself.
Essentially, I want to add another row to the table for each row with
reg_cat_id = 3. But in this row, I want the original registration_id
to show up in the new row.
Here is my syntax below - this generates an error:
INSERT INTO Registration_Category
(REG_CAT_ID, REGISTRATION_ID, STAFF_ID,
REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE)
VALUES (90, t1.REGISTRATION_ID, 'test', '05/05/2007', 'Y',
'05/05/2007')
SELECT REGISTRATION_ID, STAFF_ID,
REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE
FROM Registration_Category t1
WHERE (REG_CAT_ID = 3)
ORDER BY REGISTRATION_ID
Any suggestions?On 30 Mar 2006 14:40:12 -0800, Dee wrote:
(snip)
>Here is my syntax below - this generates an error:
>INSERT INTO Registration_Category
> (REG_CAT_ID, REGISTRATION_ID, STAFF_ID,
>REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE)
>VALUES (90, t1.REGISTRATION_ID, 'test', '05/05/2007', 'Y',
>'05/05/2007')
> SELECT REGISTRATION_ID, STAFF_ID,
>REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE
> FROM Registration_Category t1
> WHERE (REG_CAT_ID = 3)
> ORDER BY REGISTRATION_ID
>Any suggestions?
INSERT INTO Registration_Category
(REG_CAT_ID, REGISTRATION_ID, STAFF_ID,
REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE)
SELECT 90, t1.REGISTRATION_ID, 'test',
'20070505', 'Y', '20070505')
FROM Registration_Category AS t1
WHERE REG_CAT_ID = 3
Hugo Kornelis, SQL Server MVP

Can I change row filter without disable the whole republication?

hi guys
Can I change the row filter without disable the whole republication in sql
2000?
You are best to create a new publication or if you are using dynamic
snapshots, a new snapshot.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Alien" <alien2000@.21cn.com> wrote in message
news:eUuR%23EZuFHA.3104@.TK2MSFTNGP10.phx.gbl...
> hi guys
> Can I change the row filter without disable the whole republication in sql
> 2000?
>
>

Can I add an autonumber to a view?

Hi, I wish to build a datagrid, in an ASP.NET web page, in which I can display a row from a table and when I press a button it will move forward one row. The table is not currently sorted and I need it to be alpahbetical by surname so I thought if I created a view I could sort the table alphabetically on the 'Surname' column. I know that ADO.NET when used in disconnected mode doesn't have a cursor which remembers the last row it was on so I thought if I could create an autonumber on the view then I could use this in the 'where' clause to tell SQLserver which line I wished to select by taking the previous number and adding one. Is this the best way to do this and if so could some one give advice on how to write the line of the query that will add in an autonumber column

thanks
Johnwhy don't you just use the standard paging method with a pagesize of one?

(no views, don't have autonumbering unless you feel like generating one from a cursor)|||The table has over 100,000 rows in it so I don't wish to pull all these into a dataset as I thought this would be far too big if lots of users were going to use the website at once. I thought using paging it stepped through a dataset not the actual SQL table. I just want a way that I can find the next row each time.

I will look at the paging method. thanks

John

can I add a row to a data base table with VWD Express

I have a data table I have adabted for my own use and want to add a row to the table. Can I do that with Visual Web developer Express?

I can add update modify the colums and data in the exciting table but I cannot figure out how to add a row.

This is just a simple datalist of names. firstname lastname. there are 18 of them and i want to add more names

jayirvin wrote:

I have a data table I have adabted for my own use and want to add a row to the table. Can I do that with Visual Web developer Express?

I can add update modify the colums and data in the exciting table but I cannot figure out how to add a row.

This is just a simple datalist of names. firstname lastname. there are 18 of them and i want to add more names

I am using ASP.NET 2.0 GridView to show the data. I have found some code to incert a row into an ASP DetailsView that even has an auto generate incert button. the code below may work for my gridview if I can create my own incert button: Can anyone help me add a relative to my list of relatives?

Can anyone help me adapt this code

'

<asp:SqlDataSourceID="SqlDataSource"runat="server"ConnectionString="<%$ ConnectionStrings:MyConnectionString1 %>"

DeleteCommand="DELETE FROM [relatives] WHERE

[RelativesID] = @.original_RelativesID"FilterExpression="RelativesID='{0}'"

InsertCommand="INSERT INTO [Relatives] ([RelativesID], [FirstName],

[LastName]) VALUES (@.RelativesID, @.FirstName,

@.LastName)"SelectCommand="SELECT * FROM [Relatives]">

<FilterParameters>

<asp:ControlParameterControlID="GridView1"Name="RelativesID"PropertyName="SelectedValue"/>

</FilterParameters>

<InsertParameters>

<asp:ParameterName="RelativesID"Type="String"/>

<asp:ParameterName="FirstName"Type="String"/>

<asp:ParameterName="LastName"Type="String"/>

</InsertParameters>

</asp:SqlDataSource>

|||

I have solved my own probelm I can incert a row with ASP:FormView

ASP:GridView does not support incert

|||

jayirvin wrote:

ASP:GridView does not support incert

Hi Jay,

The GridView does support database inserts. Did you have an InsertItemTemplate setup?

Marcie

|||

datagridgirl wrote:

jayirvin wrote:

ASP:GridView does not support incert

Hi Jay,

The GridView does support database inserts. Did you have an InsertItemTemplate setup?

Marcie

I was working with edit delete update and select template. WVD only wrote the SQL statements for the select. No incert new

|||

Hi Marcie,

Can you please tell me where I can find more information on inserting a record using GridView?

I have edit and delete functioning fine.

Thanks,

Doug

|||

Hi Doug,

This article has some good info on inserting with a GridView: http://fredrik.nsquared2.com/viewpost.aspx?PostID=155

Also visit the GridView articles section of my site: http://www.gridviewgirl.com/GridViewGirl/articles.aspx

Marcie

|||I had to use and ASP Details view component and add insert delete and new while configuring its data scorce go to advanced

Can I add a record number as data passes through

Hello.

In SSIS, is it possible to add a record number to each row of data as I copy it from the source to the destination.

An example of my source data is below, For each MemberID want to record the number of times it occurs in the table.

MemberID

2898

2899

2899

What I want it to look like when it gets to the destination is:

MemberID RecordNumber

2898 1

2899 1

2899 2

Like an Identity column I suppose, not for the whole table but for each MemberID.

Thanks

Looks possible to me using a custom script component to compare incoming fields to the last set coming in. Would work nicely if the data is sorted.|||

bobbins wrote:

Hello.

In SSIS, is it possible to add a record number to each row of data as I copy it from the source to the destination.

An example of my source data is below, For each MemberID want to record the number of times it occurs in the table.

MemberID

2898

2899

2899

What I want it to look like when it gets to the destination is:

MemberID RecordNumber

2898 1

2899 1

2899 2

Like an Identity column I suppose, not for the whole table but for each MemberID.

Thanks

The Rank transformation will do this for you:

Rank Transform
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Rank-Transform.aspx

-Jamie

|||

Jamie:

That's a very cool and useful component. Thanks! Great work.

- Mike

|||

mike.groh wrote:

Jamie:

That's a very cool and useful component. Thanks! Great work.

- Mike

Mike,

Its a pleasure. I've been after that sort of feedback for a long time :)

Again I have to reiterate Darren Green's contribution. It was my idea but he realised the idea.

-Jamie

|||

Thanks for the info, I haven't got round to doing this yet but I'm sure it'll work.

Thanks very much, top people!

|||

I've downloaded it and installed it but how do I use it in my packages? Should I now be able to see it in Visual Studio amongst all the other data flow transformation options.

Cheers

|||

bobbins wrote:

I've downloaded it and installed it but how do I use it in my packages? Should I now be able to see it in Visual Studio amongst all the other data flow transformation options.

Cheers

The instructions here: http://msdn2.microsoft.com/fr-fr/library/ms136125.aspx are for custom tasks but its intuitively similar for custom components. Look for the section entitled "How to use the Task in SSIS Designer"

-Jamie

|||If you just want the row number function, then use the Row Number Tx http://www.sqlis.com/default.aspx?93, as it does not require a sorted input.|||

I still can't do it because when I select 'Choose toolbox items' in the data flow designer it just list what's there already, I only have an option to browse on the COM and .NET components, is this where I should be putting it?

Thanks.

|||

You are looking at the Data Flow page in the Choose Toolbox Items?

You have scolled down to find the transform, in alphabetical order?

Double check this, and perhaps compare with some screenshots here-

Konesans - Frequently Asked Questions - How do I install a task or transform component?
(http://www.konesans.com/faq.aspx#installtask)

If this does not help, on the machine you are running Visual Studio, go to C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents, and look for the DLL file. I'm not sure which transform you are trying right now, so I cannot specify the filename for you here. If the file is not there, then it will never show up in Choose Toolbox Items, so did the install fail?

|||

Yes I am.

Yes I have, it's definately not there. I have checked against the link you provided and I cannot see the component in the list.

I am attempting to use the RankTransform component. I think it installed ok as there were no errors. I've searched for the dll ( it's full name is Conchango.SQLServer.SSIS.DataFlow.RankTransform.dll ) found that it is located in C:\Program Files\Microsof SQL Server\90\DTS\CompFldr so this looks like conformation that it has installed ok.

I've copied it to the directory that you specified and opened the 'choose items' again and it was listed, so I've added it.

I'd like to say a big thanks to yourself and Jamie for the help with this, as someone who doesn't know much about Visual Studio and dll's and stuff you've been an massive help.

I'll now try to use it (I'll have to go back to the instructions!) and let you know how it goes.

Thanks again

|||

Ok, the component works great but it is not putting in what I expected it to:

My source data is:

Member Number

2898

2899

2899

I added a sort task to sort it by Member Number ascending.

In the rank transform task I selected Member Number as the sort key and checked the partition box and selected row number as the output.

When the data reached the destination it looked like this:

Member Number Row Number

2898 1

2899 2

2899 3

I'd expected it to look like this:

Member Number Row Number

2898 1

2899 1

2899 2

because I assumed from what I'd selected in the rank transform task that it's essentially running this query:

SELECT [Member Number] , ROW_NUMBER() OVER (PARTITION BY [Member Number] ORDER BY [Member Number]) AS [Row Number]

FROM StatusHistory

which does give me the expected results.

Have I selected the wrong options in the rank transform task or I have I completely got the wrong end of the stick and am trying to use this task for something that it was not designed for?

It's a great thing to have anyway.

Thanks

|||

bobbins wrote:

Ok, the component works great but it is not putting in what I expected it to:

My source data is:

Member Number

2898

2899

2899

I added a sort task to sort it by Member Number ascending.

In the rank transform task I selected Member Number as the sort key and checked the partition box and selected row number as the output.

When the data reached the destination it looked like this:

Member Number Row Number

2898 1

2899 2

2899 3

I'd expected it to look like this:

Member Number Row Number

2898 1

2899 1

2899 2

because I assumed from what I'd selected in the rank transform task that it's essentially running this query:

SELECT [Member Number] , ROW_NUMBER() OVER (PARTITION BY [Member Number] ORDER BY [Member Number]) AS [Row Number]

FROM StatusHistory

which does give me the expected results.

Have I selected the wrong options in the rank transform task or I have I completely got the wrong end of the stick and am trying to use this task for something that it was not designed for?

It's a great thing to have anyway.

Thanks

Bobbins,

Thankyou for making me aware of this. it looks as though the partition functionality might not be working. I'll check it out.

-Jamie

|||

Had any luck with it?

Cheers

Can anyone help with waittype 0x0044?

Hi,

I wonder if anyone can shed any light on the following as i just can't
explain it.

A user is running an update on a 500m+ row table setting a column
value, computing its value from another column in the table. It's now
been running for 23hours.

The server is Itanium 64, enterprise 2005, SAN based storage and it
usually handles anything with this volume quite quickly, probably
about 30 mins or so.

There is nothing else running currently although overnight batches,
backups etc have been running within the last 23 hours.

In sysprocess it showing the following :-

spid kpid blocked waittype waittime
lastwaittype waitresource
52 5236 0 0x0044 30
PAGEIOLATCH_EX 6:13:1754732

the process seems to stay in this waittype for a few secnds and then
goes to a 0x0000 and then back into this one again. I can see from the
IO counter that IO is increasing and also looking at the current IO i
see the following so presume the query is still working :-

select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle

gives results :-

613151115052100x0000000008624080

I just can't explain why it is so slow when nothing else is ruuning.

Anyone have any ideas on what i can check on?

Thanks

Ian.ianwr (ianwrigglesworth@.yahoo.co.uk) writes:

Quote:

Originally Posted by

A user is running an update on a 500m+ row table setting a column
value, computing its value from another column in the table. It's now
been running for 23hours.

Would the update cause the rows to grow? For instance, if this is
a new column that was added as nullable, and is now being populated?
In that case the table will need to grow, and could take some time.
Not the least if the data file has to grow as well.

Quote:

Originally Posted by

There is nothing else running currently although overnight batches,
backups etc have been running within the last 23 hours.
>
In sysprocess it showing the following :-
>
spid kpid blocked waittype waittime
lastwaittype waitresource
52 5236 0 0x0044 30
PAGEIOLATCH_EX 6:13:1754732

In sys.dm_exec_requests there is a wait_type which is likely to be
more informative than 0x0044.

Quote:

Originally Posted by

Anyone have any ideas on what i can check on?

Obviously a

SELECT COUNT(*) FROM tbl (NOLOCK) WHERE col <expected value

will you some progress information.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,

Thanks for the view to use, unfortunately when i arrived this morning
the task had stopped and took about 29 hours to run.

Going to keep an eye on things and check out the san as well today,
Thanks for the info anyway, if it happens again i'll repost

Thanks

Ian,

Can a stored procedure be executed from within a select statement?

Can a stored procedure be executed from within a select statement?

Given a store procedure named: sp_proc

I wish to do something like this:

For each row in the table
execute sp_proc 'parameter1', parameter2'...
end for
...but within a select statement. I know you can do this with stored functions, just not sure what the syntax is for a stored procedure.No, not within a select statment. Well, maybe with OPENQUERY, but even if that did work I would never use it.|||So convert it into a function...

Is there a question here?

Can a DataTable update an SQL Table?

Can you use a DataTable to update an SQL Table and can it be done in a batch UPDATE as opposed to incrementing through every row using a stored procedure similar to UPDATE SQLTable SET Position = @.Position WHERE ID = @.ID?

Yes.. use the dataadapter and dataset

http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson05.aspx

|||

I think I found my misunderstanding with my approach. What I really should be asking is how to I access the Random001 in my DataSet in my Button1_Click event?

public DataTable GetRandLinks()
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringA"].ConnectionString);
SqlCommand cmd = new SqlCommand("RandomizerSelect001", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
try
{
da.Fill(ds, "Random001");
return ds.Tables["Random001"];
}
catch
{ throw new ApplicationException("Data error"); }
}
protected void Button1_Click(object sender, EventArgs e)

{

NEED to know how to call the Random001 table so I can make changes in a loop, then write those changes up using da.Update? When I try I keep getting object not found?

}

|||

Wait a bit - I think it finally may be seeing the breaking of dawn - - - -

Can @@ROWCOUNT = 0 even when a row is inserted into a table?

Does @.@.ROWCOUNT always return an accurate acount of rows affected by last query OR can it be equal to zero when some rows have been affected?

The @.@.ROWCOUNT function returns the number of rows affected by the last statement, no matter what type (insert/update/delete/select) of the statement is. It just like if you execute a statement in Query Analyzer with the NOCOUNT option off, a message will come with result indicates how many rows are affected.|||And to be clear, the availability of @.@.ROWCOUNT is not affected by the SET NOCOUNT setting.

Don