Executing Multiple Scripts

Hey guys,
What is the best way to run multiple sql scripts against a database.
Vendor provided a DBupdate utility but it isn't working properly....
any suggestions...
thanks,
jonathanOSQL with a batch file. DTS/SSIS. Scheduled job.
Many ways to do this...|||do you know of a site or book where I can read how to do so....
a good search string for google....|||just giving it away today...

DECLARE @.SQLServer VARCHAR(100)
DECLARE @.Database VARCHAR(100)
DECLARE @.UserName VARCHAR(100)
DECLARE @.Password VARCHAR(100)
DECLARE @.UseWindowsAuthentication BIT
DECLARE @.Path VARCHAR(1000)
DECLARE @.FilePathToSQLFiles VARCHAR(200)

/*################################################# ###########################
If you are unsure of your sql server name, you can use the following
SELECT @.@.SERVERNAME. This should be the sql server where the database resides
that you are updating.
################################################## ###########################*/
SET @.SQLServer = 'MyServer'

/*################################################# ###
@.Database is the name of the database you are updating.
################################################## ####*/
SET @.Database = 'MyDB'

/*################################################# ###
Is the path to the sql files that you wish to execute.
Example FilePath : C:\SQL Scripts\
################################################## ###*/
SET @.FilePathToSQLFiles = 'C:\SQL Scripts\Create Scripts\'

/*################################################# ###################################
If you choose to use windows auth, you do not have to fill in a user name or password,
but your network account has to be a sysadmin on the sql server.
1 = use windows auth
0 = sql auth
################################################## ##################################*/

SET @.UseWindowsAuthentication = 1
SET @.UserName = ''
SET @.Password = ''

CREATE TABLE #SQLFiles ( SQLFileName VARCHAR(2000))

SET @.Path = 'dir /b "' + @.FilePathToSQLFiles + '*.sql"'

INSERT INTO #SQLFiles
EXECUTE master.dbo.xp_cmdshell @.Path

DECLARE cFiles CURSOR FOR
SELECT DISTINCT [SQLFileName]
FROM #SQLFiles
WHERE [SQLFileName] IS NOT NULL AND
[SQLFileName] <> 'NULL'
ORDER BY [SQLFileName]

DECLARE @.vFileName VARCHAR(200)
DECLARE @.vSQLStmt VARCHAR(4000)

OPEN cFiles

IF @.UseWindowsAuthentication = 0
BEGIN

FETCH NEXT FROM cFiles INTO @.vFileName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.vSQLStmt = 'master.dbo.xp_cmdshell ''osql -S ' + @.SQLServer + ' -U ' + @.UserName + ' -P ' + @.Password + ' -d ' + @.Database + ' -i "' + @.FilePathToSQLFiles + @.vFileName + '" >>"' + @.FilePathToSQLFiles + 'LogFile_' + CONVERT(VARCHAR,GETDATE(),102) + '_' + @.SQLServer + '_' + @.Database + '.txt"'''
--PRINT @.vSQLStmt
EXECUTE (@.vSQLStmt)
FETCH NEXT FROM cFiles INTO @.vFileName
END

END

IF @.UseWindowsAuthentication = 1
BEGIN

FETCH NEXT FROM cFiles INTO @.vFileName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.vSQLStmt = 'master.dbo.xp_cmdshell ''osql -S ' + @.SQLServer + ' -E -d ' + @.Database + ' -i "' + @.FilePathToSQLFiles + @.vFileName + '" >>"' + @.FilePathToSQLFiles + 'LogFile_' + CONVERT(VARCHAR,GETDATE(),102) + '_' + @.SQLServer + '_' + @.Database + '.txt"'''
--PRINT @.vSQLStmt
EXECUTE (@.vSQLStmt)
FETCH NEXT FROM cFiles INTO @.vFileName
END

END

CLOSE cFiles
DEALLOCATE cFiles

Print '################################################# ################################################'
Print 'Please review the log file located at ' + @.FilePathToSQLFiles + 'LogFile_' + CONVERT(VARCHAR,GETDATE(),102) + '_' + @.SQLServer + '_' + @.Database + '.txt'
Print '################################################# ################################################'
GO
DROP TABLE #SQLFiles
GO|||Much much appreciated...|||just giving it away today...

Slut

This message is to short

Executing multiple reports to form single document

Hi Everyone!
In my project I have lot of small small reports having the same criteria
e.g. Company and Years, the requirement here is to execute all these reports
and create a single document that can be delivered to customer's customer. I
am not able to find any such posting in this group or not able to find any
pointers on this.
Your help on this would be really appreciated.
Thanks in Advance
--
CloudsYou could use subreports as a way of combining your multiple reports. You
would have one "master" report that consisted of simply several subreports.
That would enable you to run the master and export it.
Mike G.
"Clouds" <Clouds@.discussions.microsoft.com> wrote in message
news:56C49443-0E2F-401A-B5B8-5D694B2D8780@.microsoft.com...
> Hi Everyone!
> In my project I have lot of small small reports having the same criteria
> e.g. Company and Years, the requirement here is to execute all these
> reports
> and create a single document that can be delivered to customer's customer.
> I
> am not able to find any such posting in this group or not able to find any
> pointers on this.
> Your help on this would be really appreciated.
> Thanks in Advance
> --
> Clouds

executing multiple query at one time

hi,

i am making a n application which in between deletes the multiple tables from the sql database.

for that i have written the following code:

SqlCommand cmd = newSqlCommand();

cmd.CommandText = "delete from " + dbConstt.DBSchema + ".PicassoSelectivityLog where QTID=" + qtid;

cmd.ExecuteNonQuery();

cmd.CommandText = "delete from " + dbConstt.DBSchema + ".PicassoSelectivityLog where QTID=" + qtid;

cmd.ExecuteNonQuery();

in this way, many more tables are to be deleted.

is there any need to create the new SQLCommand object again an\d agin for each and every query. can iot be done like the given above or can there be some better method?

thanz in advance..

divya

What about creating a static method, passing in the connection and the things that can change in the query, composing query query within the method and executing this thing asynchronously ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
sql

executing multiple procs at one go

Hi,

I would like to execute multiple stored procs by calling a single script say A. I want to A to execute on a nightly basis. The multiple stored procs are bulk inserts into separate tables. I want to be notified of the error if one among the many stored procs fail when I call A. But if one fails, I don't want A to fail. The remaining stored procs should execute. Does anyone have a script wriiten for A? I guess A is just not about -

EXEC SP1
EXEC SP2
EXEC SP3
EXEC SP4
EXEC SP5

Thankscreate proc dbo.A as
declare @.rc int, @.error int, @.cmd varchar(255)
set @.error = 0
EXEC @.rc = SP1
if @.rc != 0 set @.error = 1
EXEC @.rc = SP2
if @.rc != 0 set @.error = 2
EXEC @.rc = SP3
if @.rc != 0 set @.error = 3
EXEC @.rc = SP4
if @.rc != 0 set @.error = 4
EXEC @.rc = SP5
if @.rc != 0 set @.error = 5

if @.error = 1 begin
set @.cmd = 'Failed to execute SP' + cast(@.error as varchar(10))raiserror (@.cmd, 15, 1)
return (1)
end
return (0)|||Thanks. I shall test it soon and let you know if I have any problems.|||If more than one fails this logic will report the last one. In order to capture all failed you'll have to create a temp table (either # or @.) and insert a record with corresponding info after each SP fired. At the end of execution check for presence of rows in that table and if found, - construct the error message based on contents.|||Yeah, but if you have to rollback, your logging would rollback as well, and you'll have no idea...

echo out to a text file

Also some errors just raise and you can't trap them...I wonder what @.rc would be set to in those cases...NULL?

@.@.ERROR might be non zero in that case...

I'll have to test it...

Vivek: Let us know how it goes...

Executing more than one stored procedures in a DataReader

Hey guys,

I have found out that we can execute multiple queries and receive multiple resultsets in a SqlDataReader by executing the queries with ";" separators,

However, what if we wanted to execute two sqlcommand storedprocedures? are there any other way rather than placing "Execute sp1;Execute sp2" in the command text?

I would like to do it in a way whereby I can pass in two storedprocedures with parameters binding capability rather than execute sp1(param1, param2);execute sp2(param1, param2, param3)

Hope to get some suggestions and advice from you guys,

Thank you very much in advance.

Combine the two stored procedures into a third that will execute both.

|||

thanks Mike for your response,

any other better ways?

as there would be too many storedprocs just for combining procedures and it might get messy..

I was thinking to write a custom class and configure the class as I would for Sqlcommand storedprocedure, and then combine the various StoredProc Classes with a ";" and pass into the sqlcommand text.

But would this add-on to more performance defect? as there will be extra objects involved..

Please advice. Thanks.

|||

I don't think by any means or ways its a good idea to execute 2 or more procedures simultaneously from code. If you create such a class which can call and handle 2 or more SPs then also you'll have some questions to answer, such as what about the parameters ? You must be having different parameters for different SPs. Out of them, some may be out put type. What if an error occurs while executing any of the SPs ?.

So, my advice to you is better you execute them one by one if you don't have the compulsion ( which I don't think you'll be having ) to execute them simultaneously.

|||

Hi,

The best way as one user suggested you is to create a stored proc that combines multiple stored procs into one :

For eg:

CREATE PROCEDURE sp_ProcCallMultiple
(
@.var1 varchar(10)
@.var2 varchar(10),
)
AS

EXEC sp_Proc1 @.var1

EXEC sp_Proc2 @.var1

Once done, you can then use sp_ProcCallMultiple using a data reader

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Suprotim Agarwal:

Hi,

The best way as one user suggested you is to create a stored proc that combines multiple stored procs into one :

For eg:

CREATE PROCEDURE sp_ProcCallMultiple
(
@.var1 varchar(10)
@.var2 varchar(10),
)
AS

EXEC sp_Proc1 @.var1

EXEC sp_Proc2 @.var1

Once done, you can then use sp_ProcCallMultiple using a data reader

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

Hi,

Thanks for showing this sample but I already know about this, just wanted to find out if there is an alternative.

To Dhimant: It is possible to handle Output Parameters and so on and it is more effective as it only takes one trip to the server. However, I only need to do this mostly for queries with select statements, other stored procs which require calling two or more procs, i usually execute them in one proc itself. The reason why i didnt want to combine the selection procs is because, there may be too many combinations and things will get messy. Thanks for your advice.

Executing commands in parallel

I have the need to run multiple SQL commands (like osql and bcp) in parallel
from a single bat file. To describe this a little clearer...
While executing a single .BAT file, I want to be able to execute 3 OSQL
commands in parallel. Currently I have to run these 3 serially and it takes
too long.
Is there any way from within a single bat file that I could have 3 OSQL
commands running at the same time?
Thanks in advance."TJT" <T_homas.T_odd@.smed.com> wrote in message
news:OHrmXephDHA.604@.TK2MSFTNGP10.phx.gbl...
> I have the need to run multiple SQL commands (like osql and bcp) in
parallel
> from a single bat file. To describe this a little clearer...
> While executing a single .BAT file, I want to be able to execute 3 OSQL
> commands in parallel. Currently I have to run these 3 serially and it
takes
> too long.
> Is there any way from within a single bat file that I could have 3 OSQL
> commands running at the same time?
>
start osql.exe . . .
start osql.exe . . .
start osql.exe . . .
From a command prompt type
start /?
for help with the start command.
Davidsql

Executing a package multiple times in parallel - can't see it in the GUI

Hi,
I am calling a package 4 times, in parallel, from a parent package. It
would be really nice if I could see 4 instances of this package in the GUI so I can see what each is doing.

Is that possible?

-JamieJamie, thanks for the feedback. The designer experience here is a little rough. I don't think we'll be able to do anything about it for now.
Please open a bug and we'll take a look for V.Next.
One thing you might try is to log progress and open the log whenever you want to see progress.
Thanks,
K|||Done. Track ID 642775426

Incidentally Kirk, did you see my post "Rowcounts don't appear" which is along a similar theme.

|||Yep, saw that Jamie. Thanks a heap. Have already put it into the V.Next "Think hard about this one" bucket.
K|||Cool. Cos I have to be honest, that's a major irritation for me at the moment cos of the huge (and I do mean HUGE) datasets I've been working with.

-Jamiesql

Execute sql on multiple objects (DBs or tables)

Hi,
Because I'me a newbie on this... and I don't want to make a monstrous-query, please some advice on this:

In pseudo-code:

for objectname in (specified list of objects)
do
some sql code (i.e. create table xyz)
done

With 'objects' I mean a database or table name.

I've searched and found the foreachdb option, but I don't want to execute the sql n ALL db's but only the ones specified.

Any help is appreciated!sticking to your '(list of objects)' syntax, you could do this:

declare @.x int
declare @.dbname varchar(500)
set @.x = 1

create table #databases
(
ID int IDENTITY,
name varchar(500)
)

insert #databases
select name
from master..sysdatabases
where name in(<your list of databases separated by comma>)

while @.x <= (select max(id) from #databases)
begin
select @.dbname = name from #databases where id = @.x
--<capture @.dbname for dynamic sql>
print @.dbname
set @.x = @.x + 1
end

drop table #databases

Good luck.

Execute Process Task Arguments

HI,

Is it possible to provide variables ( multiple variables ) in the arguments parameters of the Execute Process Task?

Thanks

Shafiq

You can use multiple command arguements in one task by using spaces to delimit arguements.

Thanks,
Loonysan

|||To use variables in the process arguments, use Expressions tab and define an expression for Arguments property. You can use multiple variables there.

Execute package results different to step by step execution - uses raw file

I have a package that has multiple data flow tasks. At the end of a task, key data is written into a raw file (file name stored in a variable) that is used as a data source for the next task. Each task requires a success from the preceding task.

Here's the rub:

If I execute the entire package, the results of the package (number of records of certain tasks) differs significantly from when I execute each step in the package in turn (many more records e.g. 5 vs 350).

I get the feeling that the Raw file is read into memory before it is flushed by the previous task, or that the next task begins preparation tasks too early.

Any help is greatly appreciated.

I am running on Server 2003 64 (although the same thing happens when deployed on a Server 2003 32 machine)

Thanks

B.

Hi Brian,

Interesting.

A workaround question/suggestion: Would staging the data between tasks in a database staging table work for you?

Just curious,
Andy

|||

Hi Andy,

I was hoping someone has encountered this and has a quick fix - that said....

Short answer - yes - would probably work, but I would prefer not to (I don't really like using temporary tables if I can avoid them).

The data that is being stored in the files are new guids and their relationship to old composite ids, generated during the processing of the records. I guess that storing that information in a staging database is a possibility, but I would hate to have to re-work that entire block of code.

Ideally, if it can't be stored in raw files, then connection specific temporary tables would have been my next option - but those are not really available either.

Thanks,

B.

Execute Mutiple Tasks In Debug Mode

Maybe I'm missing something, but I can't find how to run multiple tasks in sequence while in Visual Studio debug mode. In DTS design mode I grew accustomed to right-clicking tasks one-at-a time, but in SSIS I find the additional step of having to exit Debugging mode after every task gets old after a while.

There must be a way to start execution at a certain task and have the package continue all the way to some other specified task. It would also be nice to have every task in a Group execute in sequence and stop (even if connections continue beyond the group). I could even settle for repeatedly clicking the Continue button in Debug mode, but it's always grayed out when the current task is finished!

Can this be achieved by setting breakpoints?

I have previously requested "Execute from here" functionality in the control flow. You can vote for this request here: http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=a2548612-b602-42ab-9ff9-563d927674f8

Adding a comment would help as well.

In the meantime, you can place all required tasks into a sequence container, right click on the sequence container and select "Execute task". All tasks in the container will be executed.

-Jamie

|||

Sequence container appears to be what I was looking for. Thanks. It seems obvious now that you've pointed it out, but features are easy to miss with so much new functionality.

I added some comments on your request.

Execute multiple statements at once...

I am not even sure if this is possible but I want to be able to excute several statements (or SPs) at once from inside a SPs (or any other method).

What I am doing is a I am taking data from a single column, multiple rows and making it into one row (i.e. data1 + data2 + data3....) But I am doing this to a total of 2.1 million individual rows and the result will be about 204k rows.... what I have written is basically a nested loop and it works fine but very slow... slow as in it has been running for 24 hours now and it is about 60% done... I need this to finish in under 36 hours preferably...

If I could handle more than one set of data at once (there will be no duplicates) I could speed up the process by how ever many I feel like working with...

So is there a way to execute a statement (sp or function) and go to the next statement without waiting for the first to finish?

Thanks Big Time... I hope this is possible.

OH.... SQL 2000 SP4 on Windows 2003.

You could open multiple connections from the client, but something tells me that there may be a better way to process your data.

Could you post the relevent schema and code?

|||

AFAIK within SQL server 2000 you do not have such a chance. On a single CPU, I doubt the case would be different under 2005 (but not an MSSQL expert).

However "24 hours" and even "hours" for 2.1 million rows processing to create a 204K data sounded to be very slow to me. Maybe if you select the data outside of SQL server with an isolation like uncommitted, prepare outside and do a single bulk load, it'd be much faster.

How do you do this? All in a single T-SQL batch?

|||

It is all within a single tsql SP.... It is extremely slow and I can't seem to get it to use more processor or memory...

I am thinking using a DTS package and working with multiple temp tables. Then combining all the temp tables in the end... it should be quicker that way and I might be able to push my server (which is enterprise with 12GB of ram and 2 processors)...

current schema is :

Final Table = ID_NUM (Key, char(10)), data (varchar(6000))

Working table is ID_NUM, date varchar(100), importance (smallint) - key is ID_NUM and DATA

Working temp table data varchar(100), importance smallint -- no key, always less than 200 rows.
Plus one variable @.data that is a varchar(6000)

Loop 1

Grab an ID_NUM

get list of data for ID_NUM into working table

Loop 2

Loop through till all rows have been added together or it goes over 6000 characters

@.data = Data + data + data

end loop 2

insert ID_NUM, @.DATA into final table

end loop 1

That is what I am going... no cursor, just while loops...

|||

William:

Where you say, "... date varchar (100), ... " for your "Working table" do you really mean "... data varchar (100) ... "?

Dave

|||

Yes that is what I mean...

sorry... working on a million things a once and don't always proof read..

|||

It sounds like you're just trying to concatenate all of your rows in the child table into a single row in a third table. If so, the following scenario may help.

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

You'll likely not get much parallelism in looping code that you desribed so if you can find a set-based method of processing then you'll be much better off.

|||

That actually looks good... the problem will be hitting the varchar limit.

I will have to see if I hit it or not...

What fun... lol...

Thanks for all the help.

|||

My apologies - I forgot you're on SQL Server 2000. The XML method of processing won't work in this situation. You still want a process where you can build the comma-separated list in a SET operation rather than one row at a time.

There's a method whereby you can assign a value to a variable in one row and use it in subsequent rows of an UPDATE statement. AFAIK, it's undocumented but it works in SQL 2005 as well so you're safe for a little while.

The pseudo-code would look something like this:

-- Place all of the data into a work table

CREATE TABLE #WorkTable (ID_NUM char(10), data varchar(6000), importance smallint, IdentCol INT IDENTITY, PRIMARY KEY(ID_NUM, IdentCol)

INSERT INTO #WorkTable (ID_NUM, data, importance)

SELECT ID_Num, data, Importance

FROM SourceTable

ORDER BY ID_NUM, Importance

-- Update work table. Each row has the data of itself + the row preceding it when there is a matching ID_NUM

DECLARE @.ID_NUM CHAR(10)

DECLARE @.data VARCHAR(6000)

UPDATE #WorkTable

SET @.data = data = CASE WHEN ID_NUM <> @.ID_NUM THEN data

ELSE @.data + data

END

,@.ID_NUM = ID_NUM

INSERT INTO FinalTable(ID_NUM, Data)

SELECT ID_NUM, MAX(DATA)

FROM #WorkTable

GROUP BY ID_NUM

In case the pseudo-code doesn't work, here's a working sample for Adventureworks database:

CREATE TABLE #JobTitles (job_id INT, job_desc VARCHAR(100), employees VARCHAR(1000), EmployeeName VARCHAR(100), PRIMARY KEY (job_id,

EmployeeName))

DECLARE @.job_id INT, @.EmployeeNames VARCHAR(1000)

INSERT INTO #JobTitles(job_id, job_desc, EmployeeName) SELECT J.job_id, J.job_desc, E.lname +', ' + E.fname AS EmployeeName FROM jobs J INNER JOIN employee E ON J.job_id = E.job_id

SET @.job_id = -1 -- Initialize this to a number that does not exist

-- in the list of values.

UPDATE #JobTitles

SET @.EmployeeNames = employees = CASE

WHEN @.job_id <> job_id THEN EmployeeName

ELSE @.EmployeeNames + '; ' + EmployeeName

END

, @.job_id = job_id

-- Grab the largest row of each grouping.

SELECT job_id, job_desc, MAX(employees) AS EmployeeList FROM #JobTitles GROUP BY job_id, job_desc ORDER BY job_id

DROP TABLE #JobTitles

|||

William:

Here is yet another example. I mocked this up with 2.1 million rows. I was able to get this to run in about 3 minutes and 20 seconds. This problem is definitely easier to solve with 2005 than 2000 and there has to be a better way of doing this, but this example might also help you see some direction; I hope it is helpful.

Dave

I used these two tables and faked the data:

create table dbo.final
( [key] char (10) not null,
[data] varchar (6000) not null
)

create table workingTable
( ID_Num varchar (10) not null,
[data] varchar (100) not null,
importance smallint not null,

constraint pk_mockInput primary key (ID_Num, [data])
)

insert into workingTable
select convert (varchar (5), a.iter),
'{ ' + convert (varchar (5), b.iter) + ' }'
+ replicate ('-', 1 + b.iter % 29 ) + '-->',
b.iter
from small_iterator a (nolock)
inner join small_iterator b (nolock)
on a.iter <= 21300
and b.iter <= 40 + a.iter % 119
and b.iter <= 200
order by a.iter,
b.iter

update statistics workingTable
exec sp_recompile workingTable

I would prefer not to use the function because there is a fair amount of overhead associated with functions; nonetheless, I implemented the method using the following function:

alter function dbo.assembleData
( @.arg_key varchar (10),
@.arg_minData varchar (100),
@.arg_maxData varchar (100)
)
returns varchar (6000)
as

begin

declare @.retValue varchar (6000)
set @.retValue = ''

select @.retValue = @.retValue + [data]
from workingTable
where [id_num] = @.arg_key
and [data] >= @.arg_minData
and [data] <= @.arg_maxData

return ( @.retValue )

end

I then tested out the overall procedure with this query:

--truncate table dbo.final

-- The whole thing takes about 3 minutes and 20 seconds
create table #sequencer
( [key] varchar (10) not null,
[data] varchar (100) not null,
importance smallint not null,
seq integer not null,
siz integer not null,

constraint pk_#sequencer primary key ([key], [data])
)

insert into #sequencer
select a.id_num,
a.[data],
a.importance,
count(*) as seq,
sum (datalength (b.[data])) as siz
from workingTable a
inner join workingTable b
on a.id_num = b.id_num
and b.[data] <= a.[data]
group by a.id_num, a.[data], a.importance
order by a.id_num, a.[data]

insert into dbo.final
select [key],
dbo.assembleData ( [key], min_data, max_data )
from ( select [key],
min ([data]) as min_data,
max ([data]) as max_data,
min (seq) as min_seq,
max (seq) as max_seq,
case when siz = 0 then 0
else siz - 1
end / 6000 as segment
from #sequencer
group by [key],
case when siz = 0 then 0
else siz - 1
end / 6000
) x

drop table #sequencer

select top 20 *
from dbo.final

|||

I will give that method a try...

I have been trying all kinds or things including DTS with multiple connections

all have been unsuccessful in speedy results... my last attempt took about 10 hours

|||

THAT WORKED GREAT!!!!!

My job now completes in 1 min 43 sec.... HUGE improvement...

Thanks.

|||

Great you solved it.

Would you tell me which method you used (I know that not the one I said:). I'm asking because I'm not SQL server oriented and for the last few days seriously diving into SQL server. Before I was using it "superficially". Though I was impressed with T-SQL in 2000, 2005 made me an addict:)

|||I used the solution posted by Jared Ko

Execute Multiple SQL statements in Stored Proc

Hi, I have a table containing SQl statements. I need to extract the statements and execute them through stored procedure(have any better ideas?)

Table Test

Id Description

1 Insert into test(Id,Name) Values (1,'Ron')
2 Update Test Set Name = 'Robert' where Id = 1
3 Delete from Test where Id = 1

In my stored procedure, i want to execute the above statements in the order they were inserted into the table. Can Someone shed some light on how to execute multiple sql statements in a stored procedure. Thanks

ReoI hope this will help u.
--Insert statement
--insert into Test values(1, 'Insert into test(Id,Name) Values (1,''Ron'')')

declare @.sql varchar(8000)
select @.sql=Description from Test where Id=1
--print @.sql
exec (@.sql)|||Sounds like homework. What have you tried?

Execute multiple sql commands with one trip to the database.

Ive got a List<sqlCommand> and I want to execute all the commands in it, but only using one trip to the database. I dont need any return values from any of these commands. Most of the commands are update/insert/delete. Sometimes after a insert command there may be a command "set @.NEWID =identity_insert" to facilitate the next insert to a related table, but thats the only other type of command to insert/update/delete.

I am using microsofts enterprise data application block. I want to execute all the commands in the list with just one trip to the database. Would the following code achieve this :

db = DatabaseFactory.CreateDatabase("wlmAdmin");
foreach (SqlCommand sc in saveCommands)
{
db.ExecuteNonQuery(sc);
}

or would this make multiple trips to the database? Im not 100% on whats going on behind the db object. If I create the db Item in the constructor of my class using "db = DatabaseFactory.CreateDatabase("wlmAdmin");" would this mean that every instance of that class would maintain an open database connection throughout its scope/lifetime?

Ive created the class to retrieve all data using a single stored procedure and then when saving, the class builds a List of SqlCommands which as Ive mentioned I want to execute in one trip, to make the number of trips to the database as small as possible. Because of this I don't need the db object maintain an open connection to the database all the time. However It doesnt seem to have any dispose methods so Im not clear on how it operates.

In the case that the code above opens a connection for each command, how would I got about batch executing the queries in one trip?

Thanks,

C

the code sample you provided would make seperate trips to the database for each Command in your saveCommands list. If you need to batch them all together with only a single trip to the database, you might want to consider using a Stored Procedure.

|||

Try with stored procedure.... It is a best solution for your problem...

|||

You can include multiple sql commands just by separating them with semi-colons -- but then it's very hard to track down problems (where did it fail?). You will be much better off using a stored proc as the other posters suggest. Be sure to include good error handling at all steps by checking @.@.error. You will probably want to wrap the whole thing inside a transaction. Be sure also to do these 2 sets:

set nocount on -- this stops needless traffic between sql server an you telling you how many rows were affected, there are other, better ways to get this info

set xact abort on -- the only way to guarantee that a sql error will cause proc termination and a rollback (assuming you've done a begin tran)

|||

Ok, so how do I go about setting up a stored procedure which takes multiple (variable) sqlcommands as arguments.

One of the main reasons Im using sqlcommands is so I can use sqlParameter objects to avoid sql injection, so If possible I want to use the commands, rather than a lot of sql strings seperated by semi-colons.

Im not too advanced on Stored Procedures, so all details are much appreciated :-)

Thanks,

C

|||

Here's a generic "template" -- This examples here will take care of 95% of what you need to do. Note that I may have some typos so if you get a syntax error that's probably why. You don't send the commands as arguments, you put them inside the proc.

Note especially:
- Error handling -- use of @.@.error and @.@.rowcount to determine whether things went bad
- Wrapping everything in a transaction and rolling back in the event of a bad result -- most important!
- Use of OUTPUT variables -- you can use as many of these as you want to return data to the caller -- just be sure to set the parameter type to Output in the caller
- Use of PRINT statement (during debugging)

If you google "sql server stored procedure" you will get many, many examples.

You can write procs in VS or in Enterprise Manager or for that matter in notepad. Unless sql server debugging is turned on (and it isn't in many shops because of permissioning issues) there is no real debugging available

create proc MyProc (IntVar as int, Char50Var as Char(50), VarCharVar as varcahr(255), FloatVar as float ReturnVar1 as int OUTPUT, ReturnVar2 as char(10) OUTPUT) as
set nocount on
set xactabort on

begin tran
declare @.ErrNum int,
@.NumRows int,
@.ErrMsg varchar(255)

--do something--
/* Check error code -- be sure to save it to a local variable first so it does not get overwritten */
select @.ErrNum = @.error
if @.ErrNum <> 0
begin
RAISERROR ('Error occurred...blah, blah, blah',16,1) -- the 16,1 are just there because they have to be, you can use any value
ROLLBACK TRAN
RETURN
end

/* Use print statements liberally when you are debugging to find out what's happening'
/* print 'blah, blah, blah' */

--do something--
select @.ErrNum = @.error
if @.ErrNum <> 0
begin
RAISERROR ('Error occurred...blah, blah, blah',16,1) -- the 16,1 are just there because they have to be, you can use any value
ROLLBACK TRAN
RETURN
end
select @.NumRows = @.@.rowcount
if @.NumRows <> 1
begin
select @.ErrMsg = 'Error: Expected to update 1 row only but ' + convert(varchar(20),@.NumRows) + ' were updated instead!'
RAISERROR (@.ErrMsg ,16,1) -- the 16,1 are just there because they have to be, you can use any value
ROLLBACK TRAN
RETURN
end

etc.
...
...
...
commit tran
go

grant exec on dbo.MyProc to ....user or windows group......
go

NOTE: To change a proc later you can drop and recreate it or use Alter Proc instead of Create Proc.
Alter Proc retains all the permissions

|||

Hi,

Thanks again for the reply. Im still not 100% here. Lets say I have 15 sqlCommands in my List<sqlCommand>. Now I want to send all these to the stored procedure at once how do I do this? There is no way in .net to called a stored prcedure with sqlCommands as parameters, so thats the first thing I dont understand? Each sqlCommand has different Parameters, a different number of Parameters and each operates on different tables.

You say I dont call the procedures as commands, I put them inside the procedure. Does this mean I write the procedures dynamically in my code, call create on them, and then execute them, all from my .net application? I think I must have this wrong.

If I say for example my class has created 2 commands for me to execute :

delete from Table1 where id = @.id (1 Parameter of type int)

update Table1 set uname = @.uname, lname=@.lname where id = @.id (2 parameters, 2 nvarchar, 1 int)

How do I create one stored procedure that will accept both these in one trip to the database and execute them. Again thanks for the feedback so far.

C

|||

staplebottom:

Lets say I have 15 sqlCommands in my List<sqlCommand>.

Either:

1. Make 15 separate calls, in which case you need to use ado to manage your transaction (you said they should all go together). If no transaction issue then just make 15 calls

2. Create 1 proc with all 15 commands and pass in all the parms you need

3. Send 1 call with 15 different 'exec ...' commands, separated by commas -- but then it's very hard to figure out whether anything went wrong and if so, which one.

|||

With that example, I would say you are trying to re-invent the dataadapter class, the dataset class, or a combination of both.

|||

So Im doing things wrong so. My aim was to just hit the database once to select all the data, one stored procedure, which then saves all the data into a dataset with a number of tables.

Then I manually check which tables are changed and create a command for each insert / update and delete. I understand that the dataset has this functionality, but felt that I might be able to reduce the number of trips to the database by implementing this myself for my own classes. So I created a list of commands for the changes to the dataset and thought I might be able to execute them all with one trip to the database. If this isnt possible I suppose I have one more question before I revert to using a few stored procedures (one for each table).

If I convert the sql commands to one long sql string and send it to the database as one command :

1. If I put a value into a sqlParameter say as Varchar and then read it back into a string, will it be safe from causing Sql Injections?

2. Will one long string of sql text e.g (update table set f =2; insert into table1 (test) values ('x') ; etc etc ..), seperated by by semi-colons execute faster than executing the sqlcommands using a trip each time.

3. If I have a command in the semi-colon delimited string, set @.newid = identity_insert; will this actually work ok, storing the value in newid for the next few commands to use?

Thanks,

C

|||

I think you're worrying too much about making trips to the database. Keep things simple. Trying to do everything in one command will only get you into trouble.

staplebottom:

If I have a command in the semi-colon delimited string, set @.newid = identity_insert; will this actually work ok, storing the value in newid for the next few commands to use?

No, I don't believe it will because you aren't creating any context for @.newid to exist in

|||

Yeah I think your right. I think Ill just try use a few stored procedures. Well it was at least a learning experience. Thanks for the feedback.

|||

1. No

2. Yes

3. Yes, btw it'd be SET @.newid=identity_insert(); -- Notice the parenthesis

BTW, the dataadapter will batch up multiple commands into a batch for sqlserver to execute (Reducing the roundtrips). Unfortunately, I see no way of extending/allowing this functionality outside of the dataadapter as Microsoft has marked everything you'd need as notinheritable, etc. Shame.

Execute Multiple Scripts from one directory

I got tired of having to execute multiple scripts one at a time from
my testing server to my production server. So I decided to create a
batch routine that utilizes the command line routine OSQL. All you
have to do to use it is place the batch file in a directory containing
your SQL scripts and execute the batch file. I have placed in the
command the argument to print a result file for each sql script. If
you need your SQL scripts run in a particular order simply rename them
accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql
enjoy
REM ########################################
#####
REM Author: Joe Ocampo
REM Date: 04/17/2004
REM Version: 1.0.1
REM ########################################
#####
REM You must execute the script from the SQL server itself
REM and you must use the SA login or the DBO login of the
REM target database.
REM ########################################
#####
SET login=sa
SET password=password
SET server=local
SET database=pubs
for %%a in (*.sql) do osql -d %database% -U %login% -P %password% -i
%%a -o %%a_result.txtJoe wrote:
> I got tired of having to execute multiple scripts one at a time from
> my testing server to my production server. So I decided to create a
> batch routine that utilizes the command line routine OSQL. All you
> have to do to use it is place the batch file in a directory containing
> your SQL scripts and execute the batch file. I have placed in the
> command the argument to print a result file for each sql script. If
> you need your SQL scripts run in a particular order simply rename them
> accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql
Exceedingly simple, but incredibly useful, and something that I was going to
have to write myself pretty soon - thanks.
John.|||I knew there had to be an easier way. I was scripting 35 scripts at a
time. Between opening and closing files and the occasional, "Didn't I
run that already?" I knew it was time to find a better way.
Glad to help,
Joe
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Execute Multiple Scripts from one directory

I got tired of having to execute multiple scripts one at a time from
my testing server to my production server. So I decided to create a
batch routine that utilizes the command line routine OSQL. All you
have to do to use it is place the batch file in a directory containing
your SQL scripts and execute the batch file. I have placed in the
command the argument to print a result file for each sql script. If
you need your SQL scripts run in a particular order simply rename them
accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql
enjoy
REM #############################################
REM Author: Joe Ocampo
REM Date: 04/17/2004
REM Version: 1.0.1
REM #############################################
REM You must execute the script from the SQL server itself
REM and you must use the SA login or the DBO login of the
REM target database.
REM #############################################
SET login=sa
SET password=password
SET server=local
SET database=pubs
for %%a in (*.sql) do osql -d %database% -U %login% -P %password% -i
%%a -o %%a_result.txt
Joe wrote:
> I got tired of having to execute multiple scripts one at a time from
> my testing server to my production server. So I decided to create a
> batch routine that utilizes the command line routine OSQL. All you
> have to do to use it is place the batch file in a directory containing
> your SQL scripts and execute the batch file. I have placed in the
> command the argument to print a result file for each sql script. If
> you need your SQL scripts run in a particular order simply rename them
> accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql
Exceedingly simple, but incredibly useful, and something that I was going to
have to write myself pretty soon - thanks.
John.
|||I knew there had to be an easier way. I was scripting 35 scripts at a
time. Between opening and closing files and the occasional, "Didn't I
run that already?" I knew it was time to find a better way.
Glad to help,
Joe
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Execute Multiple Scripts from one directory

I got tired of having to execute multiple scripts one at a time from
my testing server to my production server. So I decided to create a
batch routine that utilizes the command line routine OSQL. All you
have to do to use it is place the batch file in a directory containing
your SQL scripts and execute the batch file. I have placed in the
command the argument to print a result file for each sql script. If
you need your SQL scripts run in a particular order simply rename them
accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql
enjoy
REM #############################################
REM Author: Joe Ocampo
REM Date: 04/17/2004
REM Version: 1.0.1
REM #############################################
REM You must execute the script from the SQL server itself
REM and you must use the SA login or the DBO login of the
REM target database.
REM #############################################
SET login=sa
SET password=password
SET server=local
SET database=pubs
for %%a in (*.sql) do osql -d %database% -U %login% -P %password% -i
%%a -o %%a_result.txtJoe wrote:
> I got tired of having to execute multiple scripts one at a time from
> my testing server to my production server. So I decided to create a
> batch routine that utilizes the command line routine OSQL. All you
> have to do to use it is place the batch file in a directory containing
> your SQL scripts and execute the batch file. I have placed in the
> command the argument to print a result file for each sql script. If
> you need your SQL scripts run in a particular order simply rename them
> accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql
Exceedingly simple, but incredibly useful, and something that I was going to
have to write myself pretty soon - thanks.
John.

execute multiple queries over a single connection

Hi!
Is SQL Server 2000, just a toy?
Accordind validation tests it needs 30.000 connections to move
170 rows from Linked Server.
JackWe need more information. How do you "move 170 rows from Linked Server"? Can
you show us some code?
How did you determine that 30000 connection were needed?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <none@.INVALIDmail.com> wrote in message news:AYa%e.137$xG6.129@.read3.inet.fi...[vbcol
=seagreen]
> Hi!
> Is SQL Server 2000, just a toy?
> Accordind validation tests it needs 30.000 connections to move
> 170 rows from Linked Server.
> Jack
>[/vbcol]|||Then it is a BizTalk issue. Perhaps BizTalk isn't very intelligent in how it
interacts with SQL
Server, or BizTalk isn't used in the most efficient way? I can't tell as I d
on't know anything about
BizTalk.
I suggest you raise the issue in a BizTalk group, as they will understand wh
at you want to achieve,
and can respond to how you try to achieve that goal. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <Jack@.none.com> wrote in message news:Y3T0f.251$865.187@.read3.inet.fi...en">
> Well it is this BizTalk Orchestration
> http://msdn.microsoft.com/biztalk/
> BTW, it is PowerToys in their own words ;)
>
> -- Original Message --
> From: "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> Newsgroups: microsoft.public.sqlserver.server
> Sent: Friday, September 30, 2005 5:51 PM
> Subject: Re: execute multiple queries over a single connection
>
>

execute multiple queries over a single connection

Hi!
Is SQL Server 2000, just a toy?
Accordind validation tests it needs 30.000 connections to move
170 rows from Linked Server.
Jack
We need more information. How do you "move 170 rows from Linked Server"? Can you show us some code?
How did you determine that 30000 connection were needed?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <none@.INVALIDmail.com> wrote in message news:AYa%e.137$xG6.129@.read3.inet.fi...
> Hi!
> Is SQL Server 2000, just a toy?
> Accordind validation tests it needs 30.000 connections to move
> 170 rows from Linked Server.
> Jack
>
|||Then it is a BizTalk issue. Perhaps BizTalk isn't very intelligent in how it interacts with SQL
Server, or BizTalk isn't used in the most efficient way? I can't tell as I don't know anything about
BizTalk.
I suggest you raise the issue in a BizTalk group, as they will understand what you want to achieve,
and can respond to how you try to achieve that goal. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <Jack@.none.com> wrote in message news:Y3T0f.251$865.187@.read3.inet.fi...
> Well it is this BizTalk Orchestration
> http://msdn.microsoft.com/biztalk/
> BTW, it is PowerToys in their own words ;)
>
> -- Original Message --
> From: "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> Newsgroups: microsoft.public.sqlserver.server
> Sent: Friday, September 30, 2005 5:51 PM
> Subject: Re: execute multiple queries over a single connection
>
>

execute multiple queries over a single connection

Hi!
Is SQL Server 2000, just a toy?
Accordind validation tests it needs 30.000 connections to move
170 rows from Linked Server.
JackWe need more information. How do you "move 170 rows from Linked Server"? Can you show us some code?
How did you determine that 30000 connection were needed?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <none@.INVALIDmail.com> wrote in message news:AYa%e.137$xG6.129@.read3.inet.fi...
> Hi!
> Is SQL Server 2000, just a toy?
> Accordind validation tests it needs 30.000 connections to move
> 170 rows from Linked Server.
> Jack
>