Script to find out the number of row in all the table of a database.

HI,
I am using following script to find out the number of row in all the
table of a database is there any simple way out? if so pls mail me
declare @.TAB VARCHAR (20),
@.qu nvarchar (100)
DECLARE TABALE CURSOR FOR
select name from sysobjects where xtype='u' order by name open tabale
FETCH NEXT FROM TABALE INTO @.TAB while @.@.fetch_status = 0 begin --SET
@.TAB = 'SALES1'
--select name from sysobjects where name = @.tab SET @.QU ='SELECT
COUNT(*) FROM '+@.TAB print @.tab EXEC sp_executesql @.QU fetch next from
TABALE INTO @.TAB end close tabale deallocate tabale
Thanks
Sajid ChhapekarYOu could use the undocumented procedure sp_msforeachtable, but keep in
mind that this one is undocumented and might be deprecated in further
versions of SQL Server.
sp_msforeachtable 'SELECT ''?'' as TableName COUNT(*) AS Counted_rows
FROM ?'
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Hello,
Try the following query to get your row counts without having to use a cursor.
SELECT sysobjects.name, rows FROM Sysindexes
INNER JOIN Sysobjects
on Sysindexes.id = sysobjects.id
AND indid < 2
AND sysobjects.xtype = 'u'
AND sysobjects.name <> 'dtproperties'
You should get the same result as your cursor.
Thanks Kllyj64
"csajid@.gmail.com" wrote:
> HI,
> I am using following script to find out the number of row in all the
> table of a database is there any simple way out? if so pls mail me
>
> declare @.TAB VARCHAR (20),
> @.qu nvarchar (100)
> DECLARE TABALE CURSOR FOR
> select name from sysobjects where xtype='u' order by name open tabale
> FETCH NEXT FROM TABALE INTO @.TAB while @.@.fetch_status = 0 begin --SET
> @.TAB = 'SALES1'
> --select name from sysobjects where name = @.tab SET @.QU ='SELECT
> COUNT(*) FROM '+@.TAB print @.tab EXEC sp_executesql @.QU fetch next from
> TABALE INTO @.TAB end close tabale deallocate tabale
>
> Thanks
> Sajid Chhapekar
>|||Hi,
That's great. Thanks for this.
Thanks and regards,
Sajid.
kllyj64 wrote:
> Hello,
> Try the following query to get your row counts without having to use a cursor.
> SELECT sysobjects.name, rows FROM Sysindexes
> INNER JOIN Sysobjects
> on Sysindexes.id = sysobjects.id
> AND indid < 2
> AND sysobjects.xtype = 'u'
> AND sysobjects.name <> 'dtproperties'
> You should get the same result as your cursor.
>
> --
> Thanks Kllyj64
>
> "csajid@.gmail.com" wrote:
> > HI,
> >
> > I am using following script to find out the number of row in all the
> > table of a database is there any simple way out? if so pls mail me
> >
> >
> > declare @.TAB VARCHAR (20),
> > @.qu nvarchar (100)
> >
> > DECLARE TABALE CURSOR FOR
> > select name from sysobjects where xtype='u' order by name open tabale
> > FETCH NEXT FROM TABALE INTO @.TAB while @.@.fetch_status = 0 begin --SET
> > @.TAB = 'SALES1'
> > --select name from sysobjects where name = @.tab SET @.QU ='SELECT
> > COUNT(*) FROM '+@.TAB print @.tab EXEC sp_executesql @.QU fetch next from
> > TABALE INTO @.TAB end close tabale deallocate tabale
> >
> >
> > Thanks
> > Sajid Chhapekar
> >
> >

Script to find out the number of row in all the table of a database.

HI,
I am using following script to find out the number of row in all the
table of a database is there any simple way out? if so pls mail me
declare @.TAB VARCHAR (20),
@.qu nvarchar (100)
DECLARE TABALE CURSOR FOR
select name from sysobjects where xtype='u' order by name open tabale
FETCH NEXT FROM TABALE INTO @.TAB while @.@.fetch_status = 0 begin --SET
@.TAB = 'SALES1'
--select name from sysobjects where name = @.tab SET @.QU ='SELECT
COUNT(*) FROM '+@.TAB print @.tab EXEC sp_executesql @.QU fetch next from
TABALE INTO @.TAB end close tabale deallocate tabale
Thanks
Sajid ChhapekarYOu could use the undocumented procedure sp_msforeachtable, but keep in
mind that this one is undocumented and might be deprecated in further
versions of SQL Server.
sp_msforeachtable 'SELECT ''?'' as TableName COUNT(*) AS Counted_rows
FROM ?'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--sql

script to combine multiple rows into a single row

Hi everyone,

I really appreciate if anyone could help me with this tricky problem
that I'm having. I'm looking for a sample script to combine data in
multiple rows into one row. I'm using sqlserver. This is how data is
stored in the table.

ID Color
111 Blue
111 Yellow
111 Pink
111 Green

This is the result that I would like to have.

ID Color
111 Blue, Yellow, Pink, Green

There is no definite number of colors per ID. I have to use ID to
group these colors into one row. Therefore, ID becomes a unique key
in the table.

Appreciate your help and time. Thank you in advancelie_valerie@.yahoo.com (Valerie) wrote in message news:<f4825946.0308141505.4c29ff89@.posting.google.com>...
> Hi everyone,
> I really appreciate if anyone could help me with this tricky problem
> that I'm having. I'm looking for a sample script to combine data in
> multiple rows into one row. I'm using sqlserver. This is how data is
> stored in the table.
> ID Color
> 111 Blue
> 111 Yellow
> 111 Pink
> 111 Green
> This is the result that I would like to have.
> ID Color
> 111 Blue, Yellow, Pink, Green
> There is no definite number of colors per ID. I have to use ID to
> group these colors into one row. Therefore, ID becomes a unique key
> in the table.
> Appreciate your help and time. Thank you in advance

The short answer is that you should consider doing this in your client
application. For a longer answer, have a look at the thread "newbie
sql query question" in this group from a day or two ago.

Simonsql

Script to combine multiple rows into 1 single row

Hi,

I'm working on a system migration and I need to combine data from multiple
rows (with the same ID) into one comma separated string. This is how the
data is at the moment:

Company_ID Material
0x00C00000000053B86 Lead
0x00C00000000053B86 Sulphur
0x00C00000000053B86 Concrete

I need it in the following format:
Company_ID Material
0x00C00000000053B86 Lead, Sulphur, Concrete

There is no definite number of materials per Company.

I have read the part of
http://www.sommarskog.se/arrays-in-sql.html#iterative that talks about 'The
Iterative Method' but my knowledge of SQL is very limited and I don't know
how to use this code to get what I need.

Can anyone help me?Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

I'm working on a system migration and I need to combine data from multiple
rows (with the same ID) into one comma separated string. This is how the
data is at the moment:
>
Company_ID Material
0x00C00000000053B86 Lead
0x00C00000000053B86 Sulphur
0x00C00000000053B86 Concrete
>
I need it in the following format:
Company_ID Material
0x00C00000000053B86 Lead, Sulphur, Concrete
>
There is no definite number of materials per Company.
>
I have read the part of
http://www.sommarskog.se/arrays-in-sql.html#iterative that talks about
'The Iterative Method' but my knowledge of SQL is very limited and I
don't know how to use this code to get what I need.

And that article covers the opposite process - unpacking the list.

Composing the list is less funny, because it produces a result which
violates basic principles in a relational database: no repeating groups.
That is not to say that it's a stupid thing to ask for; it's not strange
to ask for this format in reporting. I get a little nervous when you
say that you are working with system migraton, because that means that
someone will have to handle the comma-separated list on the other side,
and is not funny at all. But I assume that you don't have control over
that.

Anyway, to give a good answer to the question, I would need to know a
few more things:
o Which version of SQL Server?
o What is a reasonable upper limit of the comma-separated string? You
could determine the current max value with this query:

SELECT MAX(listlen), AVG(listlen)
FROM (SELECT SUM(len(Material) + 2)
FROM tbl
GROUP BY Company_ID) as a

o What is the datatype of Material? That is, is varchar or nvarchar?

--
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,

I hope it's not to late to get help on this one!

Here are the answers you are looking for:

1) I'm using SQL 2000
2) 40
3) nvarchar

To clarify the field names, it is 'material_name' instead of 'material' and
is 'to_company' instead of 'company_id'

Thanks!

Mintyman

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns98A19BE4CF73BYazorman@.127.0.0.1...

Quote:

Originally Posted by

Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

>I'm working on a system migration and I need to combine data from
>multiple
>rows (with the same ID) into one comma separated string. This is how the
>data is at the moment:
>>
>Company_ID Material
>0x00C00000000053B86 Lead
>0x00C00000000053B86 Sulphur
>0x00C00000000053B86 Concrete
>>
>I need it in the following format:
>Company_ID Material
>0x00C00000000053B86 Lead, Sulphur, Concrete
>>
>There is no definite number of materials per Company.
>>
>I have read the part of
>http://www.sommarskog.se/arrays-in-sql.html#iterative that talks about
>'The Iterative Method' but my knowledge of SQL is very limited and I
>don't know how to use this code to get what I need.

>
And that article covers the opposite process - unpacking the list.
>
Composing the list is less funny, because it produces a result which
violates basic principles in a relational database: no repeating groups.
That is not to say that it's a stupid thing to ask for; it's not strange
to ask for this format in reporting. I get a little nervous when you
say that you are working with system migraton, because that means that
someone will have to handle the comma-separated list on the other side,
and is not funny at all. But I assume that you don't have control over
that.
>
Anyway, to give a good answer to the question, I would need to know a
few more things:
o Which version of SQL Server?
o What is a reasonable upper limit of the comma-separated string? You
could determine the current max value with this query:
>
SELECT MAX(listlen), AVG(listlen)
FROM (SELECT SUM(len(Material) + 2)
FROM tbl
GROUP BY Company_ID) as a
>
o What is the datatype of Material? That is, is varchar or nvarchar?
>
--
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

|||Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

I hope it's not to late to get help on this one!
>
Here are the answers you are looking for:
>
1) I'm using SQL 2000
2) 40
3) nvarchar

I the longest list would be 40 characters, this means that there are not
that many materials per company. Since you said no limit, I was afraid
that there was a risk that you could exceed the limit of 4000 for an
nvarchar. In that case, you would have been in real dire straits. Unless
you had been on SQL 2005 where this would have been much simpler.

Here is an example of a query that runs in Northwind. First run:

select max(cnt) from
(select OrderID, cnt = COUNT(*)
from [Order Details]
group by OrderID) s

(but translated to your database). This gives the longest list in number
of elements. In case of Northwind the returned number is 25 which is a tad
many. With a maximum of 40 characters per list, a maximum of seven seems
reasonable. Using that number, here is a query for Northwind that
returns a comma-separated lists per order:

SELECT OrderID,
MAX(CASE OD.rowno WHEN 1 THEN P.ProductName END) +
coalesce(MAX(CASE OD.rowno WHEN 2 THEN ', ' + P.ProductName END), '') +
coalesce(MAX(CASE OD.rowno WHEN 3 THEN ', ' + P.ProductName END), '') +
coalesce(MAX(CASE OD.rowno WHEN 4 THEN ', ' + P.ProductName END), '') +
coalesce(MAX(CASE OD.rowno WHEN 5 THEN ', ' + P.ProductName END), '') +
coalesce(MAX(CASE OD.rowno WHEN 6 THEN ', ' + P.ProductName END), '') +
coalesce(MAX(CASE OD.rowno WHEN 7 THEN ', ' + P.ProductName END), '')
FROM (SELECT a.OrderID, a.ProductID,
rowno = (SELECT COUNT(*)
FROM [Order Details] b
WHERE b.OrderID = a.OrderID
AND b.ProductID <= a.ProductID)
FROM [Order Details] a) AS OD
JOIN Products P ON P.ProductID = OD.ProductID
GROUP BY OD.OrderID
ORDER BY OD.OrderID

If your maximum number is 8, you will need to add one more line.

Caveat: the performance of this is not fantastic. The big culprit is
the SELECT that computes the row number. If you have millions and millions
of rows in that table, you may bave to find a different way to compute
the row number. One way would to be bounce the data over a temp table
with an IDENTITY column. But before you go that route, try a query like
the one above.

If you need to compose many of these queries, I would suggest that you
look into the third-party tool RAC, http://www.rac4sql.net/ which can
help you to generate such queries.

--
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 script. The difference between the Northwind database and
mine is that all the data I want to get access to is in one table (unlike
Northwind where it is spread over [order details[ and [products]. I tried
modifying the script but it doesn't work:

SELECT to_company,
MAX(CASE OD.rowno WHEN 1 THEN Material_Name END) +
coalesce(MAX(CASE OD.rowno WHEN 2 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 3 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 4 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 5 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 6 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 7 THEN ', ' + Material_Name END), '')
FROM Material__Bridge AS OD
GROUP BY OD.to_company
ORDER BY OD.to_company

It says there is an invalid column name 'rowno' - I guess this is right
because there is no column with that name in my database! However, when I
check in Northwind, there isn't one called that there either!

Any ideas?

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns98AED9C3878CYazorman@.127.0.0.1...

Quote:

Originally Posted by

Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

>I hope it's not to late to get help on this one!
>>
>Here are the answers you are looking for:
>>
>1) I'm using SQL 2000
>2) 40
>3) nvarchar

>
I the longest list would be 40 characters, this means that there are not
that many materials per company. Since you said no limit, I was afraid
that there was a risk that you could exceed the limit of 4000 for an
nvarchar. In that case, you would have been in real dire straits. Unless
you had been on SQL 2005 where this would have been much simpler.
>
Here is an example of a query that runs in Northwind. First run:
>
select max(cnt) from
(select OrderID, cnt = COUNT(*)
from [Order Details]
group by OrderID) s
>
(but translated to your database). This gives the longest list in number
of elements. In case of Northwind the returned number is 25 which is a tad
many. With a maximum of 40 characters per list, a maximum of seven seems
reasonable. Using that number, here is a query for Northwind that
returns a comma-separated lists per order:
>
SELECT OrderID,
MAX(CASE OD.rowno WHEN 1 THEN P.ProductName END) +
coalesce(MAX(CASE OD.rowno WHEN 2 THEN ', ' + P.ProductName END), '')
+
coalesce(MAX(CASE OD.rowno WHEN 3 THEN ', ' + P.ProductName END), '')
+
coalesce(MAX(CASE OD.rowno WHEN 4 THEN ', ' + P.ProductName END), '')
+
coalesce(MAX(CASE OD.rowno WHEN 5 THEN ', ' + P.ProductName END), '')
+
coalesce(MAX(CASE OD.rowno WHEN 6 THEN ', ' + P.ProductName END), '')
+
coalesce(MAX(CASE OD.rowno WHEN 7 THEN ', ' + P.ProductName END), '')
FROM (SELECT a.OrderID, a.ProductID,
rowno = (SELECT COUNT(*)
FROM [Order Details] b
WHERE b.OrderID = a.OrderID
AND b.ProductID <= a.ProductID)
FROM [Order Details] a) AS OD
JOIN Products P ON P.ProductID = OD.ProductID
GROUP BY OD.OrderID
ORDER BY OD.OrderID
>
If your maximum number is 8, you will need to add one more line.
>
Caveat: the performance of this is not fantastic. The big culprit is
the SELECT that computes the row number. If you have millions and millions
of rows in that table, you may bave to find a different way to compute
the row number. One way would to be bounce the data over a temp table
with an IDENTITY column. But before you go that route, try a query like
the one above.
>
If you need to compose many of these queries, I would suggest that you
look into the third-party tool RAC, http://www.rac4sql.net/ which can
help you to generate such queries.
>
>
>
--
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

|||Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

Thanks for the script. The difference between the Northwind database and
mine is that all the data I want to get access to is in one table (unlike
Northwind where it is spread over [order details[ and [products].

I could have done the script with product ids instead of product names
but that seemed boring.

Quote:

Originally Posted by

It says there is an invalid column name 'rowno' - I guess this is right
because there is no column with that name in my database! However, when I
check in Northwind, there isn't one called that there either!

The column rowno is defined in the derived table. I suggest that you study
my query a little closer, and try to understand what it's actually doing.

It might be that you want to be spoon-fed a solution, but I have this funny
idea that I like to help people to help themselves. That is, when I post a
solution, I hope that people do not only use it, but also try to understand
how it works, so that the next time they run into a similar problem, they
now have something in their toolbox that they can apply.

--
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,

I totally agree with not being spoon fed! I'm sorry I came across as wanting
to be. I'll try and work out what your script is doing :o) Thanks for your
help!

Mintyman

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns98AF7D09BCD42Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

>Thanks for the script. The difference between the Northwind database and
>mine is that all the data I want to get access to is in one table (unlike
>Northwind where it is spread over [order details[ and [products].

>
I could have done the script with product ids instead of product names
but that seemed boring.
>

Quote:

Originally Posted by

>It says there is an invalid column name 'rowno' - I guess this is right
>because there is no column with that name in my database! However, when I
>check in Northwind, there isn't one called that there either!

>
The column rowno is defined in the derived table. I suggest that you study
my query a little closer, and try to understand what it's actually doing.
>
It might be that you want to be spoon-fed a solution, but I have this
funny
idea that I like to help people to help themselves. That is, when I post a
solution, I hope that people do not only use it, but also try to
understand
how it works, so that the next time they run into a similar problem, they
now have something in their toolbox that they can apply.
>
--
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

|||Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

I totally agree with not being spoon fed! I'm sorry I came across as
wanting to be. I'll try and work out what your script is doing :o)

There is one thing I should have pointed out. In my query there was this
part:

(SELECT a.OrderID, a.ProductID,
rowno = (SELECT COUNT(*)
FROM [Order Details] b
WHERE b.OrderID = a.OrderID
AND b.ProductID <= a.ProductID)
FROM [Order Details] a) AS OD

That is a *derived table*. A derived table is logically a temp table in
the query so to speak, but not materialised, and the actually computation
order can be different as long as the result is the same. Derived tables
is an enormously powerful tool to build complex queries with, and saves
you from using real temp tables.

--
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

Script Question inside a DataFlow

Is it possible to iterate over all of the fields of the Row collection inside of the Script Component of a data flow. Basically, want I want to is to check every incoming column (all are strings) for a particular character sequence, and if found, change it to something else. I am current accessing each field as Row.Field1, Row.Field2, etc. and just thought there must be a better way to do something like:

For each col in Row

if row.col = XXX then do something.

End For

Thanks in advance for your help

gsell wrote:

Is it possible to iterate over all of the fields of the Row collection inside of the Script Component of a data flow. Basically, want I want to is to check every incoming column (all are strings) for a particular character sequence, and if found, change it to something else. I am current accessing each field as Row.Field1, Row.Field2, etc. and just thought there must be a better way to do something like:

For each col in Row

if row.col = XXX then do something.

End For

Thanks in advance for your help

Well, you COULD do that but, trust me, it would be much much slower than what you are already doing. To loop over the data this would need to be an asynchronous (sometimes called a blocking) component and these are slow.

The correct way to do this is what you are already doing.

-Jamie

Script Component: Keyword not supported: ''provider''

Hi,

I have a script component that accesses the database with this codes:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim cn As SqlConnection

Dim SQLCmd As SqlCommand

Dim dr As SqlDataReader

Dim NextKey As Integer = 0

cn = New SqlConnection(Connections.CONNofficetestdb.ConnectionString.ToString)

cn.Open()

SQLCmd = New SqlCommand("select count(*) from accounttrans", cn)

dr = SQLCmd.ExecuteReader

While dr.Read()

NextKey = NextKey + 1

End While

End Sub

But I encounter a problem once I ran it:

Keyword not supported: 'provider'

How do I resolve this?

thanks a lot.

cherriesh

Since you are using the SQL Server connection object, you should not have a Provider defined in your connection string. You only have to supply the Provider in the connection string when you are using OLEDB.

Script component: copy a row into a variable

HI, is it possible to copy a row from the Input0_ProcessInputRow into another variable of a synchronous script component. I have a script component that has three outputs. What I would like to do is the following:

At first iteration, copy entire row into a variable.

For the other rows, is business key cols is different from the previous row (the one copied at previous iteration) or type 2 columns :

1-direct previous row (from the variable) to second.output
2- direct current row to the first output
3- copy the current row into the variable

If the business key cols are the same and type 2 cols are the same:
1-Direct row to the third output

My problem is that when I declare a variable as Input0Buffer and then copy the current row in it, at each subsequent iterations, the variable is updated. What I want to do is to keep the value of the previous row. I know I can do it b are differenty copying the content of all columns into separate variables but since some script components will have 150+ columns, I would like to find a simpler way to do it (like a copy of the structure of the input buffer).

The reason I use a script component is that the lookups are not updated dynamically, the rows I need to process may not exist in the target table and once inserted, they are not retreived subsequently by the buffer.

Thank you,
Cco

ccote wrote:

My problem is that when I declare a variable as Input0Buffer and then copy the current row in it, at each subsequent iterations, the variable is updated. What I want to do is to keep the value of the previous row. I know I can do it b are differenty copying the content of all columns into separate variables but since some script components will have 150+ columns, I would like to find a simpler way to do it (like a copy of the structure of the input buffer).

You should not use the buffer that was passed to you outside of the ProcessInputRow call - the data flow engine manages the buffer lifetime, and by trying to use it outside of this call you are breaking the rules.

Try copying the columns to another data structure, e.g. an array.

|||

HI, thank you for your reply. I knew I could copy the row's column to another structure (array, list, variables) but I was wondering If I could copy the whole row into a kind of "row" structure and be able to later direct it to an output. It would have been great since as I said, some of my tables have 150+ columns. I guess, the only way I can manage this is to actually add type1 and type 2 columns to my output and accumulate previous row type1 and type2 attributes into variables. So when the business key changes, I would copy variables into current row and have a conditionnal split that further filter out if any action have to be taken.

I could also use asynchrounous script but there I need to recreate all my 150+ table structure into output buffers. Actually, the real problem is that the lookup transform cache is not updated as new rows are added to the target table. That is why I need to do some custom code like this.

Thank's again,
Ccote

SCOPE_IDENTITY() vs. @@IDENTITY

I have a basic C# application that is trying to INSERT a row and get the ID. Really simple; there are no triggers, no stored procs or functions were involved, the app is single threaded, there is currently only one user. I have a really basic table with a INTEGER IDENTITY PK column. All very standard.

If I do the INSERT followed by a "SELECT @.@.IDENTITY" on the same connection, it works correctly.
If I use SCOPE_IDENTITY() instead, it returns NULL. I use SCOPE_IDENTITY on a variety of other occassions and it works fine. Why would this be? I thought SCOPE_IDENTITY() was the preferred replacement to @.@.IDENTITY.

I guess what I have is satisfactory but this was frustrating and I want to know why.

This is on SQL Server 2000 Standard edition with version SP3a + hot fixeshai roger,

SCOPE_IDENTITY and @.@.IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope. @.@.IDENTITY is not limited to a specific scope.

This is the example given in BOL

Suppose if you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.

Assuming that both T1 and T2 have IDENTITY columns, @.@.IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.

@.@.IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.

SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.

Hope this relives u of ur frustration|||Yes, I read BOL and understand the documented theoretical and conceptual differences between the two. However, those differences don't apply to my situation.

There is only one thread, one process, one identity value, and one table involved. There are no triggers or functions or stored procs involved. It's a simple INSERT/get ID situation. And @.@.IDENTITY works and SCOPE_IDENTITY doesn't work which just doesn't make any sense according to what I've read.

SCOPE_IDENTITY with ASP

I am seeing a problem with an ASP application, where I have 2 tables.
In the first table, the ASP inserts just 1 row and retrieves the
primary key of the new row using SCOPE_IDENTITY. It then uses that
primary key in the column of a second table (foreign key) to insert
many rows.

What I'm seeing is an intermittent problem where the foreign key in the
second table is not what it should be. I think the problem may be due
to the fact that the insert into the first table and the calling of
SCOPE_IDENTITY are done in 2 separate ASP statements with some ASP code
in between.

Is it possible that 2 users may be calling my ASP page at the same time
and causing a concurrency problem due to the INSERT and the
SCOPE_IDENTITY being done in 2 different SQL statements? I read that
SCOPE_IDENTITY always returns the last identity value generated from
"the current connection", so I thought that would mean that it wouldn't
get messed up by another ASP request. But now I'm thinking that
perhaps ASP uses connection pooling which could mean that 2 users could
be sharing the same connection which would cause this concurrency
issue.

Does anyone know if my theory of what's wrong is plausible?hmm.. you got me here

Cowly the Game player

Please click on my links
http://spacefed.com
http://gc.gamestotal.org
http://uc.gamestotal.org
http://aw.gamestotal.org
http://www.gamestotal.org
http://3700ad.gamestotal.com
http://www.spacefederation.net
http://www.gamestotal.org/news/
http://ballmonster.gamestotal.com
http://www.spacefederation.net/manual/
http://gc.gamestotal.org/i.cfm?p=aboutgc
http://uc.gamestotal.org/i.cfm?p=aboutgc
http://www.gamestotal.org/corp/
http://www.gamestotal.org/strategygames/|||"Larry" <larry_grant_dc@.hotmail.com> wrote in message
news:1114610604.033965.138570@.o13g2000cwo.googlegr oups.com...
>I am seeing a problem with an ASP application, where I have 2 tables.
> In the first table, the ASP inserts just 1 row and retrieves the
> primary key of the new row using SCOPE_IDENTITY. It then uses that
> primary key in the column of a second table (foreign key) to insert
> many rows.
> What I'm seeing is an intermittent problem where the foreign key in the
> second table is not what it should be. I think the problem may be due
> to the fact that the insert into the first table and the calling of
> SCOPE_IDENTITY are done in 2 separate ASP statements with some ASP code
> in between.
> Is it possible that 2 users may be calling my ASP page at the same time
> and causing a concurrency problem due to the INSERT and the
> SCOPE_IDENTITY being done in 2 different SQL statements? I read that
> SCOPE_IDENTITY always returns the last identity value generated from
> "the current connection", so I thought that would mean that it wouldn't
> get messed up by another ASP request. But now I'm thinking that
> perhaps ASP uses connection pooling which could mean that 2 users could
> be sharing the same connection which would cause this concurrency
> issue.
> Does anyone know if my theory of what's wrong is plausible?

You don't mention if you're using stored procedures, but your description
seems to suggest you aren't. SCOPE_IDENTITY() returns the last value
inserted "within the current scope" - if you do your INSERTs in a stored
proc, then the proc itself is the scope, so there is no problem with
concurrency. But if you're executing each SQL statement directly, then there
could be a concurrency issue because all statements using the same
connection would share the same scope.

Apart from this issue, using stored procedures is generally a good idea, for
a number of security and performance reasons:

http://www.sommarskog.se/dynamic_sql.html#Why_SP

Simon|||I am not using stored procs.

I still have a question about your statement "there could be a
concurrency issue because all statements using the same connection
would share the same scope".

The connection is created within my ASP page. If several users call
the ASP page at the same time, is that considered the "same connection"
or different connections?|||"Larry" <larry_grant_dc@.hotmail.com> wrote in message
news:1114627612.035136.42880@.z14g2000cwz.googlegro ups.com...
>I am not using stored procs.
> I still have a question about your statement "there could be a
> concurrency issue because all statements using the same connection
> would share the same scope".
> The connection is created within my ASP page. If several users call
> the ASP page at the same time, is that considered the "same connection"
> or different connections?

If IIS opens only one connection to the server, then yes, that would all be
in the same scope. If each execution of the ASP page opens a new connection
to MSSQL (which seems unlikely to me, but I know almost nothing about ASP),
then they would be in different scopes. You can use sp_who2 to view the
current connections, and also see sysprocesses and @.@.SPID in Books Online.

If you're unsure about how ASP is managing connections, you'll probably get
better feedback in an ASP forum, although from a purely SQL perspective, I
suspect that using a stored proc should solve the issue anyway.

Simon|||Larry (larry_grant_dc@.hotmail.com) writes:
> I am seeing a problem with an ASP application, where I have 2 tables.
> In the first table, the ASP inserts just 1 row and retrieves the
> primary key of the new row using SCOPE_IDENTITY. It then uses that
> primary key in the column of a second table (foreign key) to insert
> many rows.
> What I'm seeing is an intermittent problem where the foreign key in the
> second table is not what it should be. I think the problem may be due
> to the fact that the insert into the first table and the calling of
> SCOPE_IDENTITY are done in 2 separate ASP statements with some ASP code
> in between.
> Is it possible that 2 users may be calling my ASP page at the same time
> and causing a concurrency problem due to the INSERT and the
> SCOPE_IDENTITY being done in 2 different SQL statements? I read that
> SCOPE_IDENTITY always returns the last identity value generated from
> "the current connection", so I thought that would mean that it wouldn't
> get messed up by another ASP request. But now I'm thinking that
> perhaps ASP uses connection pooling which could mean that 2 users could
> be sharing the same connection which would cause this concurrency
> issue.

I don't know ASP, but what is important is that you cannot use a
model where you connect for each query here, but you must use the
same connection for the two queries, so that you retain scope.

But it may be easier to send the SELECT satement as part of the
INSERT batch to save a round trip.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

SCOPE_IDENTITY returns decimal from SQL command

Hi,

I have an sqlCommand return the ID from the last inserted row. I use SELECT SCOPE_IDENTITY within the SQL.

When I call the function it returns a Decimal instead of an integer??

So I want to convert this to an integer, however this is not liked by the compiler since the overloaded method for the decimal conversion is obviously a decimal, but the ExecuteScalar parameter is of type object.

iReturnVal = decimal.ToInt32(_sqlCommand.ExecuteScalar()); <-- compiler complains...

So I changed to this:

iReturnValue = decimal.ToInt32((decimal)_sqlCommand.ExecuteScalar());

Can someone tell me if this is the right way to do it? Seems messy code to me...

swaino:

iReturnVal = decimal.ToInt32(_sqlCommand.ExecuteScalar()); <-- compiler complains...

Decimal.toint32? I thought it was convert.toint32?

|||

It should already be an integer.

|||

Welldecimal. provides loads of conversion functions.

Also ExecuteScalardoes return an object not an integer..