Numbers of DB in SQL

Is there a limitation of how many db you can have in SQL Standard Edition?
Will it cause problem if we have too much? somewhere around 45 DBs.
Thanks.
The documented limit is 32767 databases per server instance, so you have
some way to go yet! With the appropriate hardware 45 databases is certainly
no problem.
David Portas
SQL Server MVP
|||Thanks for replied my post. My server is a Dell 8450 Quad-processors with 2
GB of RAM.
"David Portas" wrote:

> The documented limit is 32767 databases per server instance, so you have
> some way to go yet! With the appropriate hardware 45 databases is certainly
> no problem.
> --
> David Portas
> SQL Server MVP
> --
>
>
|||David,
I think that when David Portas mentioned "appropriate hardware", he didn't
mean that there's some magic formula for 45 databases. It depends on what
those databases are doing -- how much data they contain, how many users
concurrently use each of them, what those users are doing, etc. If each
database has 100 concurrent users doing million-row queries, you'll quickly
run into a problem. On the other hand, if each database has 1 table with 10
rows of data and has a query run against it once a week, you'll be able to
support thousands of databases
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"David" <David@.discussions.microsoft.com> wrote in message
news:668C567C-E459-4ABB-A1F7-129BBADD6EB8@.microsoft.com...
> Thanks for replied my post. My server is a Dell 8450 Quad-processors with
2[vbcol=seagreen]
> GB of RAM.
> "David Portas" wrote:
certainly[vbcol=seagreen]
|||David,
In addition to David's and Adam's excellent responses, you may find
limitations with SQL-DMO which will manifest through Enterprise Manager.
After a certain number of databases, you will find that enumeration of
databases will take a long time when you expand the databases node in
the tree. At some point you will find that it takes longer than you are
willing to wait for it, and you will have to administer your databases
using T-SQL.
SMO in SQL Server 2005 addresses this issue by using partial
instantiation (aka delayed instantiation) and caching.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
David wrote:
> Is there a limitation of how many db you can have in SQL Standard Edition?
> Will it cause problem if we have too much? somewhere around 45 DBs.
> Thanks.
>
sql

Numbers of DB in SQL

Is there a limitation of how many db you can have in SQL Standard Edition?
Will it cause problem if we have too much? somewhere around 45 DBs.
Thanks.The documented limit is 32767 databases per server instance, so you have
some way to go yet! With the appropriate hardware 45 databases is certainly
no problem.
--
David Portas
SQL Server MVP
--|||Thanks for replied my post. My server is a Dell 8450 Quad-processors with 2
GB of RAM.
"David Portas" wrote:
> The documented limit is 32767 databases per server instance, so you have
> some way to go yet! With the appropriate hardware 45 databases is certainly
> no problem.
> --
> David Portas
> SQL Server MVP
> --
>
>|||David,
I think that when David Portas mentioned "appropriate hardware", he didn't
mean that there's some magic formula for 45 databases. It depends on what
those databases are doing -- how much data they contain, how many users
concurrently use each of them, what those users are doing, etc. If each
database has 100 concurrent users doing million-row queries, you'll quickly
run into a problem. On the other hand, if each database has 1 table with 10
rows of data and has a query run against it once a week, you'll be able to
support thousands of databases :)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"David" <David@.discussions.microsoft.com> wrote in message
news:668C567C-E459-4ABB-A1F7-129BBADD6EB8@.microsoft.com...
> Thanks for replied my post. My server is a Dell 8450 Quad-processors with
2
> GB of RAM.
> "David Portas" wrote:
> > The documented limit is 32767 databases per server instance, so you have
> > some way to go yet! With the appropriate hardware 45 databases is
certainly
> > no problem.
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
> >
> >|||David,
In addition to David's and Adam's excellent responses, you may find
limitations with SQL-DMO which will manifest through Enterprise Manager.
After a certain number of databases, you will find that enumeration of
databases will take a long time when you expand the databases node in
the tree. At some point you will find that it takes longer than you are
willing to wait for it, and you will have to administer your databases
using T-SQL.
SMO in SQL Server 2005 addresses this issue by using partial
instantiation (aka delayed instantiation) and caching.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
David wrote:
> Is there a limitation of how many db you can have in SQL Standard Edition?
> Will it cause problem if we have too much? somewhere around 45 DBs.
> Thanks.
>

Numbers of DB in SQL

Is there a limitation of how many db you can have in SQL Standard Edition?
Will it cause problem if we have too much? somewhere around 45 DBs.
Thanks.The documented limit is 32767 databases per server instance, so you have
some way to go yet! With the appropriate hardware 45 databases is certainly
no problem.
David Portas
SQL Server MVP
--|||Thanks for replied my post. My server is a Dell 8450 Quad-processors with 2
GB of RAM.
"David Portas" wrote:

> The documented limit is 32767 databases per server instance, so you have
> some way to go yet! With the appropriate hardware 45 databases is certainl
y
> no problem.
> --
> David Portas
> SQL Server MVP
> --
>
>|||David,
I think that when David Portas mentioned "appropriate hardware", he didn't
mean that there's some magic formula for 45 databases. It depends on what
those databases are doing -- how much data they contain, how many users
concurrently use each of them, what those users are doing, etc. If each
database has 100 concurrent users doing million-row queries, you'll quickly
run into a problem. On the other hand, if each database has 1 table with 10
rows of data and has a query run against it once a week, you'll be able to
support thousands of databases
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"David" <David@.discussions.microsoft.com> wrote in message
news:668C567C-E459-4ABB-A1F7-129BBADD6EB8@.microsoft.com...
> Thanks for replied my post. My server is a Dell 8450 Quad-processors with
2[vbcol=seagreen]
> GB of RAM.
> "David Portas" wrote:
>
certainly[vbcol=seagreen]|||David,
In addition to David's and Adam's excellent responses, you may find
limitations with SQL-DMO which will manifest through Enterprise Manager.
After a certain number of databases, you will find that enumeration of
databases will take a long time when you expand the databases node in
the tree. At some point you will find that it takes longer than you are
willing to wait for it, and you will have to administer your databases
using T-SQL.
SMO in SQL Server 2005 addresses this issue by using partial
instantiation (aka delayed instantiation) and caching.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
David wrote:
> Is there a limitation of how many db you can have in SQL Standard Edition?
> Will it cause problem if we have too much? somewhere around 45 DBs.
> Thanks.
>

Numbers less than zero

In a pretty standard select statement (as shown), i want to return 0 when "dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total - dbo.v_AgentCommClean.total AS amount_outstanding_commission" is less than 0.

SELECT dbo.t_Agents.agent_code, dbo.v_CurrentParamPaymentTotal.ammount AS weekley_payment_total,
dbo.v_AgentNumberOfCustomers.count AS number_of_cust, dbo.v_AgentAmmountPaid.total AS total_paid,
dbo.v_AgentOrderTotals.Total AS ytd_order_total, dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total AS amount_outstanding,
ISNULL(dbo.v_AgentAmmountPaid.total / dbo.v_AgentOrderTotals.Total, 0) * 100 AS ytd_percentage,
dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total - dbo.v_AgentCommClean.total AS amount_outstanding_commission,
ISNULL(dbo.v_AgentOrderChange.amount, 0) AS net_weekly_order
FROM dbo.t_Agents LEFT OUTER JOIN
dbo.v_AgentOrderChange ON dbo.t_Agents.AGENT_ID = dbo.v_AgentOrderChange.AGENT_ID LEFT OUTER JOIN
dbo.v_AgentCommClean ON dbo.t_Agents.AGENT_ID = dbo.v_AgentCommClean.AGENT_ID LEFT OUTER JOIN
dbo.v_AgentNumberOfCustomers ON dbo.t_Agents.AGENT_ID = dbo.v_AgentNumberOfCustomers.AGENT_ID LEFT OUTER JOIN
dbo.v_AgentOrderTotals ON dbo.t_Agents.AGENT_ID = dbo.v_AgentOrderTotals.AGENT_ID LEFT OUTER JOIN
dbo.v_AgentAmmountPaid ON dbo.t_Agents.AGENT_ID = dbo.v_AgentAmmountPaid.AGENT_ID LEFT OUTER JOIN
dbo.v_CurrentParamPaymentTotal ON dbo.t_Agents.AGENT_ID = dbo.v_CurrentParamPaymentTotal.AGENT_ID

Any ideas how i do this?

Cheers
Anthony Swift

CASE

WHEN (dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total - dbo.v_AgentCommClean.total) < THEN 0

ELSE dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total - dbo.v_AgentCommClean.total

END AS amount_outstanding_commission

|||Cheers :)

Number of licences & Calls

My Problem is that I don’t know really what does a call means.

We got a SQL Standard Edition.

Can I install as many instances I want in a Server?

How many users can access the Server at the same time?

If I use SQL authentication, and two users use this logging information, how many calls do they made? 2 one per each user (PC) or just 1 one per logging.

If I have a client / server application, and the server access the SQL Server using SQL authentication. Can I have unlimited number of clients?

SQL Server is generally speaking licensed per processor or per Client Access Licenses (CALs). CALs, are merely the maximum number of CONCURRENT USERS you may have. Thus, if you had 10 CALs, up to 10 Clients could access your SQL Server at the same time. In my experience, most companies purchase the product on the CPU model though it is usually not cheap.

Can I install as many instances I want in a Server? Not sure license wise on this, but my hunch is yes

How many users can access the Server at the same time? See above, CPU model is unlimited clients, CALs are not unlimited.

If I use SQL authentication, and two users use this logging information, how many calls do they made? If at the same time, 2 CALs, else 1.

Hope this Helps,

Derek

|||

Standard Edition is limited to a maximum of 16 Instances.

There is no limit to the number of users. However, each user MUST have a Client Access License (CAL) UNLESS the server is operating under a 'Processor' license.

Effective End Users count as a user, not the number of Logins, or servers accessing data, etc.

For a Public Web application, where client licenses cannot be determined, requires a 'Processor' license. A private web application may operate with each client having a CAL and the server only a 'server' license.

So, to clarify, each end user of data served by SQL Server MUST covered with a CAL or the server MUST have a 'Processor' license.

For the definitie answers about licensing, call the licensing folks at:

Licensing –VL Contact
(800) 426-9400

For Other licensing questions/answers:

Licensing -FAQ
http://www.microsoft.com/sql/howtobuy/faq.mspx

Licensing -SQL 2000
http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part2/c0461.mspx?mfr=true

Licensing -SQL 2005
http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc

Number of allowed connections for SQL Server 2005 Standard on XP P

I assume that the number of connections that SQL Server 2005 Standard can
concurrently have will be limited by the operating system it is running on.
If that OS is XP Pro, how many concurrent connections can SQL Server 2005
Standard have?
Michael
I believe that is a wrong assumption. There is no limit based on the OS that
I know of other than the fact that 64 bit can support more memory for
connections. But if you are worried about lots of connections then why would
you want to use XP Pro? The # of connections will ultimately be limited by
the amount of memory and the hardware.
Andrew J. Kelly SQL MVP
"michael" <michael@.discussions.microsoft.com> wrote in message
news:0EA3B57E-6583-4A59-B38B-FB4C89AB5D74@.microsoft.com...
>I assume that the number of connections that SQL Server 2005 Standard can
> concurrently have will be limited by the operating system it is running
> on.
> If that OS is XP Pro, how many concurrent connections can SQL Server 2005
> Standard have?
> --
> Michael
|||Yup... and the amount of CPU time you can dedicate to the threads SQL Server
will generate to handle the operations.
Let's hear more about your connection strategy and application architecture.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OSgPZMRiHHA.4596@.TK2MSFTNGP05.phx.gbl...
>I believe that is a wrong assumption. There is no limit based on the OS
>that I know of other than the fact that 64 bit can support more memory for
>connections. But if you are worried about lots of connections then why
>would you want to use XP Pro? The # of connections will ultimately be
>limited by the amount of memory and the hardware.
> --
> Andrew J. Kelly SQL MVP
> "michael" <michael@.discussions.microsoft.com> wrote in message
> news:0EA3B57E-6583-4A59-B38B-FB4C89AB5D74@.microsoft.com...
>
|||I did think about this for a bit, so, my answer will probably expose some of
my ignorance on the subject. My apologies in advance.
The reason I want to use XP Pro is that I already own a copy running on a
spare machine. The machine belongs to a domain managed on another box.
Nothing else running on the XP box. Nothing about the operation of the
database is really mission critical. I may have 10 - 30 concurrent users,
but, that's about it. I can put as much memory and storage space as I want.
What would I really get by running SQL Server 2005 on an MS server platform
in such a situation?
Respectfully,
Michael
"William (Bill) Vaughn" wrote:

> Yup... and the amount of CPU time you can dedicate to the threads SQL Server
> will generate to handle the operations.
> Let's hear more about your connection strategy and application architecture.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ------
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OSgPZMRiHHA.4596@.TK2MSFTNGP05.phx.gbl...
>
>
|||The other thing to keep in mind is that regardless of any SQL Server
limitations, Windows XP only has 10 TCP/IP connections so supporting 30
concurrent users might not be possible. XP is tuned to give preference to
UI foreground application and not IO and memory intensive applications like
SQL Server so performance often isn't what you expect.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"michael" <michael@.discussions.microsoft.com> wrote in message
news:212C8757-2AE9-4D70-B57C-986F384BB85C@.microsoft.com...[vbcol=seagreen]
>I did think about this for a bit, so, my answer will probably expose some
>of
> my ignorance on the subject. My apologies in advance.
> The reason I want to use XP Pro is that I already own a copy running on a
> spare machine. The machine belongs to a domain managed on another box.
> Nothing else running on the XP box. Nothing about the operation of the
> database is really mission critical. I may have 10 - 30 concurrent users,
> but, that's about it. I can put as much memory and storage space as I
> want.
> What would I really get by running SQL Server 2005 on an MS server
> platform
> in such a situation?
> Respectfully,
>
> --
> Michael
>
> "William (Bill) Vaughn" wrote:
|||Roger brings up several very good points that you should take into
consideration. XP just isn't geared for the types of work that SQL Server
may demand. If you have 30 concurrent connections I would not call that
trivial and if you want to do it right I would look at a server OS.
Andrew J. Kelly SQL MVP
"michael" <michael@.discussions.microsoft.com> wrote in message
news:212C8757-2AE9-4D70-B57C-986F384BB85C@.microsoft.com...[vbcol=seagreen]
>I did think about this for a bit, so, my answer will probably expose some
>of
> my ignorance on the subject. My apologies in advance.
> The reason I want to use XP Pro is that I already own a copy running on a
> spare machine. The machine belongs to a domain managed on another box.
> Nothing else running on the XP box. Nothing about the operation of the
> database is really mission critical. I may have 10 - 30 concurrent users,
> but, that's about it. I can put as much memory and storage space as I
> want.
> What would I really get by running SQL Server 2005 on an MS server
> platform
> in such a situation?
> Respectfully,
>
> --
> Michael
>
> "William (Bill) Vaughn" wrote:
|||Roger brings up a good point. However, it's possible (and legal) to alter
the number of connections by using the metadata editor to bump the number of
connections to (a maximum of) 40.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:9C5831CD-59BC-4948-95BA-D2513BCDF3A8@.microsoft.com...
> The other thing to keep in mind is that regardless of any SQL Server
> limitations, Windows XP only has 10 TCP/IP connections so supporting 30
> concurrent users might not be possible. XP is tuned to give preference to
> UI foreground application and not IO and memory intensive applications
> like SQL Server so performance often isn't what you expect.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "michael" <michael@.discussions.microsoft.com> wrote in message
> news:212C8757-2AE9-4D70-B57C-986F384BB85C@.microsoft.com...
>

Number of allowed connections for SQL Server 2005 Standard on XP P

I assume that the number of connections that SQL Server 2005 Standard can
concurrently have will be limited by the operating system it is running on.
If that OS is XP Pro, how many concurrent connections can SQL Server 2005
Standard have?
MichaelI believe that is a wrong assumption. There is no limit based on the OS that
I know of other than the fact that 64 bit can support more memory for
connections. But if you are worried about lots of connections then why would
you want to use XP Pro? The # of connections will ultimately be limited by
the amount of memory and the hardware.
Andrew J. Kelly SQL MVP
"michael" <michael@.discussions.microsoft.com> wrote in message
news:0EA3B57E-6583-4A59-B38B-FB4C89AB5D74@.microsoft.com...
>I assume that the number of connections that SQL Server 2005 Standard can
> concurrently have will be limited by the operating system it is running
> on.
> If that OS is XP Pro, how many concurrent connections can SQL Server 2005
> Standard have?
> --
> Michael|||Yup... and the amount of CPU time you can dedicate to the threads SQL Server
will generate to handle the operations.
Let's hear more about your connection strategy and application architecture.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OSgPZMRiHHA.4596@.TK2MSFTNGP05.phx.gbl...
>I believe that is a wrong assumption. There is no limit based on the OS
>that I know of other than the fact that 64 bit can support more memory for
>connections. But if you are worried about lots of connections then why
>would you want to use XP Pro? The # of connections will ultimately be
>limited by the amount of memory and the hardware.
> --
> Andrew J. Kelly SQL MVP
> "michael" <michael@.discussions.microsoft.com> wrote in message
> news:0EA3B57E-6583-4A59-B38B-FB4C89AB5D74@.microsoft.com...
>

number of affected records

Okay..i have this problem ...i am using SQL server 2005 standard ,C#,VS2005

--i am inserting some record in DB .. using ExecuteNonQuery...i want to know how many records are getting inserted..so in my DB class i did something like this : numRecords = commandObject.ExecuteNonQuery() ,assuming that ExecuteNonQuery returns the number of affected records.i am retriving this numOfRecords in my code behind and printing it but it always prints 1,even though more then one records are inserted.What is wrong here?

-i also have returnValue defiend like this.Could this tell me anything about how many records are inserted or affected during update,select ?if so,how?

cmd.Parameters.Add(newSqlParameter("@.returnVal",SqlDbType.Int));

cmd.Parameters["@.returnVal"].Direction =ParameterDirection.ReturnValue;

Please help me out with this.Thanks

Are you positive your INSERT statement isn't inserting records 1 at a time, like in a loop or something to that effect? Can you post your query?

You can retrieve values back using the method you wrote above.|||

oh...now that you pointed out...yes..my insert is inserting 1 record at a time in a loop...i am inserting it using the stored procedure...but i want to know how many got inserted at the end of looping successfully...in fact i know 'how many part' as well..b/c my dropdownList gets updated instantly with the inserted values,so i can look at it<but i don't want to rely on that to decipher out the num of recs>.Instead i wanted to display the num of recs affected ,as soon as i click on 'add' button,some parameters-something which comes out from DB as a result of executing of query.Do u think thats possible?or do i have to check on successful returnValue in a loop and increment the counter,to tell me how many times it inserted successfully?

|||

The proc you are calling to do the INSERT can keep track of number of records affected and increment a counter and send back the value at the end through an OUTPUT parameter.

|||

Thanks a million...Can you please tell me what adjustment i need to make in stored procedure,to account this record tracking?Currently it has just the simple insert based on some parameter,i send.

Also for the output parameter,will this suffice or what i need to change here.

cmd.Parameters.Add(

newSqlParameter("@.outputVal",SqlDbType.Int));

cmd.Parameters[

"@.outputVal"].Direction =ParameterDirection.Output;

cmd.ExecuteNonQuery();

return cmd.Parameters["@.outputVal"].value;

|||The code you have to retrieve the OUTPUT value looks good. If you can post the proc I can look at it.|||

Thanks ..

This is one of the stored proc,i have which gets executed in the loop

ALTER PROCEDURE

dbo.IS_spInsertToHardware

@.insertItem

nvarchar(50)

AS

insert intotblProducts (Product)values(@.insertItem)RETURN|||Oh. So you are calling the proc for each insert?|||

Yes...inside the function,this is how it is : value1, value2, value3, value4 are the values which user can enter in text boxes..at a time user can enter max 4 values and on click of update,it gets added to the dropdownlist.i don't know any better way to do it..

string

[] textValues =newstring[] { value1, value2, value3, value4 };if ((value1 !=string.Empty) || (value2 !=string.Empty) ||

(value3 !=

string.Empty) || (value4 !=string.Empty))

{

for (int i = 0; i < textValues.Length; i++)

{

if (textValues[i] !=string.Empty)

{//some additional condition which i need to check

if ((id1 > 0) && (id2 == 0))

{ retVal = invInsert.insertData(id1, textValues[i], sproc);}

|||

So you are making up to 4 trips for each INSERT? There is a better way of doing it all in one trip. Concatenate all the values into one string and send it to the proc. In your proc, parse the string and strip out each value and do the INSERT. Check for @.@.ERRPR after the INSERT and if its 0, increment a counter. After all the INSERTs are done, return the counter value and it should have the number of successfull INSERTs.

Search this forum for some code on parsing the string and extracting the values. I think I posted some code here.

|||

Thanks Dinaker,,i will look into it.....yes...i don't like making up to 4 trips to DB either for each insert..but i couldn't find any better way of doing it when i was coding..so did it this way...

|||

Hi,

In my opinion, I would put a counter in my C# or VB code and accumulate the return value from ExecuteNonQuery.

It would be more easy than calculating in the stored procedure.

HTH.

|||You are right...thats what i have done at this point..inside my db class i haveint n = cmd.ExecuteNonQuery().. in my C# code i check if n>0,if it is i increment the counter ...But i also would like to learn how it can be done inside the stored procedure...i haven't found yet,.how to parse the string in stored procedure and loop through each value and get the success count,as dinaker suggested...thanks|||

Kevin Yu - MSFT:

In my opinion, I would put a counter in my C# or VB code and accumulate the return value from ExecuteNonQuery. It would be more easy than calculating in the stored procedure.

Kevin

Do you really think making 4 trips do the DB is better than one? The increment can be done either at the application level or in the proc.

|||

Hi,

Of course, this is not a good practice. I would put the validation for these 4 strings in the stored procedure and make the 4 strings as parameters. Then the stored procedure will return rows affected at once.