Executing N procedures in 1 Round trip

w/ SqlServer, is there anyway to pack a number of calls to the same stored procedure into a single round-trip to the DB short of dynamically writing a T-SQL block? For example, if I'm calling a procedure "Update Contact" which takes 2 params @.Campaign, @.Contact 20 times how would I pass in the values for those 20 diffrent versions?

You could pass all the parameters to another stored procedure as a delimited list and parse them over there, create a loop, and call the stored proc in the loop.

|||I'm sure I could. I just thought I'd heard of something similar to ODP.Net's ArrayBinding syntax for SqlServer. I'm fairly sure I wasnt thinking about bulkcopy.|||

To my knowledge I dont think there is any standardized way to make multiple calls in one trip. Perhaps someone else here knows if there's any new feature in 2005. I havent been doing much development in 2005.

|||hrm...

Will the SQL Server provider let me execute blocks of t-sql?

eg something like:

AddContact(@.C1, @.U1);
AddContact(@.C2, @.U2);
AddContact(@.C3, @.U3);
...
AddContact(@.CN, @.UN);
go;|||

Yes, however, you must specify that the call type is text, not stored procedure, and you must properly format your calls like:

EXECUTE AddContact @.C1,@.U1
EXECUTE AddContact @.C2,@.U2
...
GO

|||So what is the "proper" format for sp calls in a 'anonymous block'.

ExecuteNonQuery - Add working/Update not working

I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...

This was my test:

Dim cmd As New SqlCommand("pContact_Update", cn)
 'Dim cmd As New SqlCommand("pContact_Add", cn)
 Try
 cmd.CommandType = CommandType.StoredProcedure
 cmd.Parameters.Add("@.UserId", SqlDbType.VarChar).Value = UserId
 cmd.Parameters.Add("@.FirstName", SqlDbType.VarChar).Value = TextBox1.Text
 [...etc more parameters...]
 cmd.Parameters.Add("@.Id", SqlDbType.VarChar).Value = ContactId
 cn.Open()
 cmd.ExecuteNonQuery()
 Label1.Text = "done"
 cn.Close()
 Catch ex As Exception
 Label1.Text = ex.Message
 End Try

When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.

I have looked at the stored procedures and the syntax is correct according to SQL Server.

Please I would appreciate any advice...Before you do your executeNonQuery - - make sure (do a response.write or a Trace.Write) to make sure your USERID and textbox1.text values are actually populated.

Many times, if the update statement has a where clause, and it continues through, the WHERE arguments are not being fulfilled.|||Thanks for your reply...
I followed the code and the Id field is getting a value. I also added:

Dim NbrRows As Integer = cmd.ExecuteNonQuery()
Label1.Text = NbrRows

And I do receive a message that 1 row has been affected. However, the value I entered changes back to the original value and the record is not updated.

Execute Statement..

Dear Friens,
I am writing a SP.But storeing a query in a variable.But at the time
of execution generating error.Exam
===================
Declare @.query varchar{500)
Set @.query = 'Select * from table'

if exists (exec (@.query))
print 'Hi'
====================
But "if exists" line giving error.How do I solve this.Please help me
out.
Reagrds
Arijit ChatterjeeHi

EXISTS requires a sub-query as the test, and EXEC does not do that.

If you have to do this dynamically then you may want to look at
sp_executesql to return a count or move everything into @.query.

If you posted more precise detail it may be easier to offer advice.

John

arijitchatterjee123@.yahoo.co.in (Arijit Chatterjee) wrote in message news:<ea01504d.0310302205.2ca98658@.posting.google.com>...
> Dear Friens,
> I am writing a SP.But storeing a query in a variable.But at the time
> of execution generating error.Exam
> ===================
> Declare @.query varchar{500)
> Set @.query = 'Select * from table'
> if exists (exec (@.query))
> print 'Hi'
> ====================
> But "if exists" line giving error.How do I solve this.Please help me
> out.
> Reagrds
> Arijit Chatterjee|||Create proc sp_test
as
Declare @.query varchar{500)
Declare @.var varchar(10)
set @.var='Test'
Set @.query = 'Select * from table' + ' Where ' + 'Colname = ' + @.var
exec (@.query)--> Working fine
if exists (exec (@.query))--> Sending error
print 'Hi'
Now tell me how to solve this.
Regards
Arijit Chatterjee|||See inline

"Arijit Chatterjee" <arijitchatterjee123@.yahoo.co.in> wrote in message
news:ea01504d.0311012028.19b506fb@.posting.google.c om...
> Create proc sp_test
> as
> Declare @.query varchar{500)
This will not compile

> Declare @.var varchar(10)
> set @.var='Test'
> Set @.query = 'Select * from table' + ' Where ' + 'Colname = ' + @.var
If you were doing this correctly the value in @.var would be enquoted

> exec (@.query)--> Working fine
> if exists (exec (@.query))--> Sending error
> print 'Hi'
> Now tell me how to solve this.

Reading books online would be the first place to look.

Then read http://www.algonet.se/~sommar/dynamic_sql.html on why you should
justify the use of dynamic SQL.

You should then be able to work out how to return the count using
sp_executesql to get what is require.

> Regards
> Arijit Chatterjee

John|||What you need to do in this case is create a sql server temporary
table (such as create table #temp (column1 nvarchar(10),column2
nvarchar(10))) and then build your @.query string so that it inserts
the results of your dynamic select statement into the temp table. You
can then play the whole "exists" game on the results of a query
against your #temp table.

Make sure that you do not build your temp table dynamically. And
remember, you can't insert results from a dynamically built select
statement into a normal variable, but you can insert them into a temp
table.

Good Luck!

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<bo2oar$89l$1@.hercules.btinternet.com>...
> See inline
> "Arijit Chatterjee" <arijitchatterjee123@.yahoo.co.in> wrote in message
> news:ea01504d.0311012028.19b506fb@.posting.google.c om...
> > Create proc sp_test
> > as
> > Declare @.query varchar{500)
> This will not compile
> > Declare @.var varchar(10)
> > set @.var='Test'
> > Set @.query = 'Select * from table' + ' Where ' + 'Colname = ' + @.var
> If you were doing this correctly the value in @.var would be enquoted
> > exec (@.query)--> Working fine
> > if exists (exec (@.query))--> Sending error
> > print 'Hi'
> > Now tell me how to solve this.
> Reading books online would be the first place to look.
> Then read http://www.algonet.se/~sommar/dynamic_sql.html on why you should
> justify the use of dynamic SQL.
> You should then be able to work out how to return the count using
> sp_executesql to get what is require.
> > Regards
> > Arijit Chatterjee
> John

execute query later

Hi NG
Im writing a web-interface that enables the user to update a DB. The
(non)queries takes heaps of time, and since my page "waits" for the query to
finish, wich is a pain. So basically I was wondering if there is kinda a
"update mytable set myrow=... LATER" kinda keyword or something, to make the
query execute whenever the sqlserver feels like it, but return "ok" right
away.
- Kasper"Kasper Birch Olsen" <kasper@.nospam.com> wrote in message
news:eYDSkU2ZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi NG
> Im writing a web-interface that enables the user to update a DB. The
> (non)queries takes heaps of time, and since my page "waits" for the query
to
> finish, wich is a pain. So basically I was wondering if there is kinda a
> "update mytable set myrow=... LATER" kinda keyword or something, to make
the
> query execute whenever the sqlserver feels like it, but return "ok" right
> away.
> - Kasper
>
As far as I know, there is no direct way of doing this.
One thing comes to mind... Have a simple table where you can drop the
information quickly. A SQL Server job can come along and check the table
every few minutes and then perform the work.
There are some caveats however. What if the data is bad, or SQL Server has
an error attempting to process the data. You have already told the web-user
that everything is ok, but in reality it is not. How do you notify the
user that there were problems.
You may need to do some architecturual work to get this to work the way you
want it to.
Rick Sawtell
MCT, MCSD, MCDBA|||Implement the sql update as a stored procedure. If you are using an ADO /
ADO.NET connection, then look executing the SP asynchronously. The SP can
send an email notification back to the user when it completes.
"Kasper Birch Olsen" <kasper@.nospam.com> wrote in message
news:eYDSkU2ZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi NG
> Im writing a web-interface that enables the user to update a DB. The
> (non)queries takes heaps of time, and since my page "waits" for the query
to
> finish, wich is a pain. So basically I was wondering if there is kinda a
> "update mytable set myrow=... LATER" kinda keyword or something, to make
the
> query execute whenever the sqlserver feels like it, but return "ok" right
> away.
> - Kasper
>|||Service Broker queue(s) come to mind. Need 2005 however or come up with
your own queue table.
William Stacey [MVP]
"Kasper Birch Olsen" <kasper@.nospam.com> wrote in message
news:eYDSkU2ZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi NG
> Im writing a web-interface that enables the user to update a DB. The
> (non)queries takes heaps of time, and since my page "waits" for the query
> to finish, wich is a pain. So basically I was wondering if there is kinda
> a "update mytable set myrow=... LATER" kinda keyword or something, to make
> the query execute whenever the sqlserver feels like it, but return "ok"
> right away.
> - Kasper
>

Execute Integration Package by SP

Hi,
I'm writing a ASP.NET application, and I would like to export data from SQL
Server 2005 to MS-Excel. I know that the Integration Service in SQL 2005
has replaced the DTS in SQL2K. I'm wondering how I can execute the
Integration package from either ASP.NET or by means of Stored Procedures.
Thank you.
Regards,
JanetWell, I have not pkayed with this issue in SQL Server 2005 ,however , can
you create a linked server to EXCEL or using OPENDATSOURCE
"Janet >" <<unknown> wrote in message
news:OoNzgaYfGHA.2032@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm writing a ASP.NET application, and I would like to export data from
> SQL Server 2005 to MS-Excel. I know that the Integration Service in SQL
> 2005 has replaced the DTS in SQL2K. I'm wondering how I can execute the
> Integration package from either ASP.NET or by means of Stored Procedures.
> Thank you.
> Regards,
> Janet
>

Execute dynamic generate SQL with length > 8000

I am now writing a stored procedure that will dynamically generate a
trigger base on a specific table structure. The generated trigger
script will have variable lenght, depends on how many columns are
defined inside the specific table.
I plan to generate the CREATE TIRGGER script on-the-fly and execute it,
but I comes with a problem that, if the script generated is longer than
8000, VARCHAR is simply cannot handle it. While the length of the
script is undeterministic, I cannot split the trigger script into a
constant number of VARCHAR variable. May I know if there is any
workaround on it ?
Thx~>I am now writing a stored procedure that will dynamically generate a
> trigger base on a specific table structure. The generated trigger
> script will have variable lenght, depends on how many columns are
> defined inside the specific table.
And this will really exceed 8000 characters? I think you will find that
this will be impossible to manage. Before you go down this route, I
strongly recommend reading http://www.sommarskog.se/dynamic_sql.html, and
consider generating new triggers in your application as opposed to within a
stored procedure.
However note that you can execute longer strings quite simply.
DECLARE @.sql_1 VARCHAR(8000), @.sql_2 VARCHAR(8000)
SET @.sql_1 = '....'
SET @.sql_2 = '....'
EXEC(@.sql1 + @.sql2)|||John Shum (eurostar@.gmail.com) writes:
> I am now writing a stored procedure that will dynamically generate a
> trigger base on a specific table structure. The generated trigger
> script will have variable lenght, depends on how many columns are
> defined inside the specific table.
> I plan to generate the CREATE TIRGGER script on-the-fly and execute it,
> but I comes with a problem that, if the script generated is longer than
> 8000, VARCHAR is simply cannot handle it. While the length of the
> script is undeterministic, I cannot split the trigger script into a
> constant number of VARCHAR variable. May I know if there is any
> workaround on it ?
I don't know what the purpose is, but to me this sounds like something
I would prefer to do in Perl or Visual Basic. Least of all I would
like to do it in T-SQL.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp