Executing SP In Loop

Sep 21, 2000

What is way for executing SP in loop? I have to delete some records in table X depends on records from table Y. Simple way is:

DELETE FROM X
WHERE X.ID IN
(
SELECT ID FROM Y WHERE ...
)

But if deleting from X is not so simply (after deleting from X I have to delte it from 5 others tables) ? I've created SP 'SP_DELETE_X' which has input parameter 'ID'. My question is how execute this SP in above loop???

Fly

View 6 Replies


ADVERTISEMENT

Data Flow Task Within For Each Loop Not Executing

Aug 22, 2007

All:
I am sure I am missing something really silly but I am not able to figure out what. The For Each Loop uses an ADO Enumerator and passes variable values to a data flow. In executing the package the loop runs fine but nothing is happening to the data flow. When I move the data flow out of the loop it runs fine. What is going on?

Thanks!

desibull

View 14 Replies View Related

Executing SSIS Package Using Jobs In SQL Server Agent Infinite Loop

Jan 4, 2008

I'm trying to execute my package using schedule in SQL Server Agent, I've already tested my package by run it manually in Integration services and it works. The table created, the data from my flat file also inserted into the table correctly and the result return with success.

The question is why when I execute my package using SQL Server Agent, the SQL Server Agent keep executing my package like infinite loop until I stop the job. after I stop the job there is no error generated by sql server. Could you figured why this happen?? I've already tried to upgrade into SP2 and set the package protection level and still not get good result from it. thank you.

View 11 Replies View Related

Passing Parameters To Batch File And Executing Batch File Loop

Aug 7, 2007

HELP,

I need to take a variable from a tabel in SQL Server pass to a Batch file and execute the batch file. Right now I can exec the batch file with XP_CMDSHELL but how can I pass the variable to the batch file and loop through all the variables.

Please help

Phil

View 4 Replies View Related

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Qu

Jan 24, 2008



Hi,

I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window

Please see the image through this url http://kyxao.net/127/ExecutionProblem.png


Any ideas for this issue?

Thanks a lot

View 1 Replies View Related

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Query Window

Jan 23, 2008

Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack 

View 2 Replies View Related

Different Results When Executing From .NET Component Compare To Executing From SQL Management Studio

Oct 10, 2006

Hi all,I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.This started to really annoying me, any thoughts or solution? Thanks very much guys

View 2 Replies View Related

SQL Server 2008 :: Difference Between FOR LOOP And FOREACH LOOP?

May 28, 2010

difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.

View 4 Replies View Related

Loop Though Table Using RowID, Not Cursor (was Loop)

Feb 22, 2006

I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks

View 6 Replies View Related

Foreach Loop Doesn't Loop

Mar 3, 2006

I have a foreach loop that is supposed to loop through a recordset, however it doesn't loop. It just repeats the same row, row after row.

I would like to look into the recordset variable but I can't because it is a COM object and the ADODB namespace is not available in the script task.

Any solution to this? anyone experienced anything similar

View 1 Replies View Related

Fishing For A Clue. To Loop Or Not To Loop

Jul 8, 2006

I have a table called Tbltimes in an access database that consists of the following fields:

empnum, empname, Tin, Tout, Thrs

what I would like to do is populate a grid view the a select statement that does the following.

display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I€™m trying to accomplish.

Tbltimes

|empnum | empname | Tin | Tout | Thrs |

| 1 | john | 2:00PM | 3:00PM |1hr |

| 1 | john | 2:00PM | 3:00PM | 1hr |

| 2 | joe | 1:00PM | 6:00PM | 5hr |

GridView1

| 1 | John | 2hrs |

| 2 | Joe | 5hrs |

im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.

View 18 Replies View Related

ForEach Loop Or For Loop??

Feb 23, 2006

I have source and destination table names in the database(one table) and I need to read the source and destination tables one by one...

My Lookp table is like the following...

Srn srctable desttable

1 SRC1 DEST1

2 SRC2 DEST2

3 SRC3 DEST3

Now I want one package to load from source to destination.. how do I do it.. I dont know how to use....

How do I run the pacakge for each of the rows... ..............................

View 1 Replies View Related

Pb Executing An SP

Sep 25, 2006

I have an SP that has this part of code located in the line:30
select convert(datetime,CONFIGURATION1) from dbdw..configuration
where CLECONFIG = 'DATE-AJOUT-VENTES'

I get teh error:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Do u know what the problem is pls

View 3 Replies View Related

Executing A Job Using VB

Sep 17, 2007

I want to execute a backend SQL job using VB on an MSACCESS frontend.
I know how to run a DTS using VB script. Can anyone give me the syntax for a SQL job? Thanks in advance.

View 1 Replies View Related

Executing A DTS From Another DTS...

Jul 20, 2005

Hi there,I am trying to execute a DTS package from another DTSpackage and change some variables in the original DTS. Ihave used the info on the following page but it doesn'twork: http://www.sqldts.com/default.aspx?215. Would youhave any input?Thanks,Andreas*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 2 Replies View Related

Executing More Than One Query

Jun 16, 2007

Hi,
How to execute more than one query consecutively in SqlDataSource like the picture shown below.
 

View 3 Replies View Related

Executing DTS (on Sql Server) From ASP.NET??

Feb 5, 2004

Does Any one know how to execute DTS (on sql server) from ASP.NET??

View 2 Replies View Related

Executing DTS Package From VB.NET

Nov 16, 2004

Hello,

I have a web application that I kick off a DTS package. I tested the package and it works fine. However, when running the package through ASP.NET, I get the following error:

Type mismatch. (mscorlib (80004005): Missing parameter does not have a default value. Parameter name: parameters)

I've traced it to a dynamic task step. I have this as the first step in the workflow, but for some reason, it is executing last. Which is definitely not the place where I want it. Is there anyway I can force it, or why is it doing that?

Any ideas?

Brian

View 2 Replies View Related

Executing DDL In A Trigger

Nov 15, 2000

Dear All,
I would like to execute a DDL statement(create table) in a trigger. The DDL statement is stored as a field in another table. I have read that sql statement into a variable of type varchar. How do I execute that sql statement.
Thanks in advance

View 3 Replies View Related

Job Sticks On "executing"

Jan 18, 2001

Hi, I have a DTS package that runs correctly when executed it manually, but when I schedule a job via the package, the job begins executing, but does not stop. I have to cancel it. The Package runs in less that 1 minute, but the job reports that it's being executed but does not stop until I cancel it.

The job was created by using the DTS package schedule command, so how can I check what's wrong?

Thanks in advance.

View 1 Replies View Related

Job Stuck While Executing

Nov 29, 2001

Hi All

I have a job that extracts data from 10 different tables to 10 diffrenet tables of MS-Access (.mdb file).
The job ran okay since last one month but yesterday it ran and did not stop at all .Status is shown as "Executing" ..
I tried to stop it but it is not stopping . I tried to kill associated process (spid) but it allows me to kill spid but it shows there running.

Any idea why it is happening ?

If any body ahs any solution you can call me at 1 860 520 7454.

Sujit

View 1 Replies View Related

Error 266 After Executing SP

Apr 24, 2003

I am facing this error when using a SP called by a DTS package (using ADO connection).
The SP gets three parameter and looks like the following (just major part of it):

SET @STR = 'SET ROWCOUNT 10000' + ' WHILE 1 = 1 ' + 'BEGIN' + ' BEGIN TRAN'
SET @STR = @STR + ' DELETE FROM ' + @TableToBeCleaned + ' WHERE ' + @SELECTEDCOLUMN + ' <

DATEADD (m,' + ' -' + @KeepMonthsInDatabase + ', + GETDATE())'
SET @STR = @STR + 'IF @@ROWCOUNT = 0 ' + 'BREAK ' + 'COMMIT ' + 'END ' + 'SET ROWCOUNT 0'
EXEC (@str)

Following error appears:

Server: Msg 266, Level 16, State 2, Line 1
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.

Previous count = 4, current count = 5.
Server: Msg 266, Level 16, State 2, Procedure p_DBCleanerDeleteRows, Line 56
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.

Previous count = 4, current count = 5.

I use the SET ROWCOUNT to execute the delete in batches. When checking with SELECT @@TRANCOUNT it

returns me 5. Somehow it is clear to me that @@TRANCOUNT exited the SP with another value than when

entering it but there is commit in the dynamic SQL statement and I do not know what is wrong.

Is it neccessary to indicate BEGIN TRAN and COMMIT in the statement at all or can I ommit it?

Thanks

mipo

View 1 Replies View Related

Executing Cursors With ASP

Jan 19, 2006

Hi,
I have written a Stored Procedure which opens a Cursor , carries out some functionality and the I De-Allocate and Close it.When I execute the Stored Procedure from Query Analyzer it works fine.However, When I execute it through ASP it does not.

Conn.Execute "Execute Proc_Name"

The Connection Object (Conn) works fine for other pages as it is in an include file.

If I remove the Cursor from the Stored_Proc the other Statements get executed , however the Execution stops when Cursor Code is there.(Though works fine with Query Anaylzer)

No error is thrown, I have checked with Query Profiler and it shows that though Stored Procedure is called but the statements in it are not executed.

There are no Parameters to it, as all it is doing is comparing values of two tables and inserting it into 1st table and deleting from the second.


Pl. Help, As Stored_Proc works fine when executed through
Query analyzer but not thru ASP.

Thanx
Dkashyap

View 4 Replies View Related

Executing Xp_cmdshell

Oct 14, 2004

Hi ,

I want to execute xp_cmdshell from normal sql server account not sysadmin member in SQL Server 2000. I had give execute privileges on xp_cmdshell to normal account... but its not working...

Is it possible to execute xp_cmdshell from non sysadmin member account in SQL Server2000...

Thanks in advance...

-Mohit.

View 1 Replies View Related

Executing Set Of SPs As JoB Is Hanging..

May 27, 2004

I have a job which is set of few Stored procedures,Usually it taked around 3-5 mins to complete the job.But somehow today the job was still executing even after 3:45:24 (yes 3 hrs,45 mins 25 secs)
WHen i tried to run the each procedure indivdually even its taking more time in the query analyzer.But when i try to execute those SPS as individual sql statements(it's step by step) they were working in reasonable time.What should be the reason for these SPs taking that much time?

Thanks.

View 5 Replies View Related

Executing .sql File

Oct 4, 2006

hi

i have a .sql file, which i can run using query analyzer. is there any other way to execute this file, like using a .bat file or .exe file. user should be able to execute this file without using query analyzer or any other database tools. i cannot call this from my main programe.
this should be a self executable file.

thanks

suji

View 2 Replies View Related

Executing DTS Package

Dec 27, 2006

Hi Friends

I am trying to execute the DTS package from the T-Sql
Its basically Exporting the table from Sql Server to tab delimited text file




DECLARE @Command varchar(1000)
declare @partcode varchar(100)

set @command = 'DTSRun /S "servername" /U "username" /P "password" /N "TabFormat" /G "{459784D8-0170-400C-8919-AC25423D7F34}" /W "0" '


EXEC master..xp_cmdshell @Command


It Comes up with the Following Error



output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DTSRun: Loading...

DTSRun: Executing...

DTSRun OnStart: DTSStep_DTSDataPumpTask_1

DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)

Error string: Error opening datafile: The system cannot find the path specified.



Error source: Microsoft Data Transformation Services Flat File Rowset Provider

Help file: DTSFFile.hlp

Help context: 0



Error Detail Records:



Error: 3 (3); Provider Error: 3 (3)

Error string: Error opening datafile: The system cannot find the path specified.



Error source: Microsoft Data Transformation Services Flat File Rowset Provider

Help file: DTSFFile.hlp

Help context: 0



DTSRun OnFinish: DTSStep_DTSDataPumpTask_1

DTSRun: Package execution complete.

NULL

(22 row(s) affected)



Vic

View 5 Replies View Related

Executing DTS - DSN Error...

Jul 23, 2005

Perhaps this has already been answered. If so, I apologize. I'm anewbie at this stuff. I have a DTS package to import some VisualFoxpro data. Yes, I said Visual Foxpro. :) The DTS package worksbeautifully when I rightclick on the package and choose "execute." Andit works beautifully when I run dtsrun.... at the DOS prompt. But itdoesn't work when I try to execute it with code in Query Analyzer.I've tried exec master..xp_cmdshell 'dtsrun ...' And I've triedscheduling the package as a Job, disabling the Job and then usingmsdb..sp_start_job to run it. Neither of those work.I get a "DSN not found and no default driver specified."My DSN stuff looks OK to me, but perhaps some of that is wrong. Whatdoes the DSN have to look like in order for the code in Query Analyzerto find it? If the DSN is screwed up, why would it work OK whenexecuted from some places, but not in others? What am I missing?Any help would be much appreciated. :) thanks...-emily

View 3 Replies View Related

Executing Sql Statements

Jul 23, 2005

When i execute the following in Sql query anlyzerDeclare @dbname varchar(30),@str varchar(500),@emailID varchar(50)set @EmailID='santosh@yahoo.com'set @dbname='DB_kms_prv'set @str='SELECT empid, NTName, officialEmail, PreferredName FROM ' +@dbname + '.dbo.tblEmployee where officialEmail=' + @emailIDexec (@str)I get error messageThe column prefix 'santosh@yahoo' does not match with a table name oralias name used in the query.How to get rid of it..?

View 1 Replies View Related

Executing An String On MS SQL From .Net

Jan 26, 2006

Hi there,

I have this:

Dim strSQL As String = "SELECT * FROM GrupoRegistro WHERE Fecha BETWEEN '" & fini & "' AND '" & ffin & "' AND (g.IDTipoCliente = 5) OR g.Fecha BETWEEN '" & fini & "' AND '" & ffin & "' AND (g.IDTipoCliente = 3)"

How do I execute on .Net? I am using VWD2005 Express Edition and MS SQL 2000 server on a WinXp pro with MS Framework 2.0

I am using o trying and some variants :
SelectCommand="@strsqlvar">
<SelectParameters>
<asp:QueryStringParameter Name="strsqlvar" DefaultValue="strsql.string" />
</SelectParameters>


And it doesn´t work... any help will e well apreciated. thank u in advance.

View 1 Replies View Related

Executing Package As Job

May 8, 2006

Hi,

I am having trouble executing a package as a job in SQL Server 2005. I can run the package fine manually from sql server but when I create a job for that package and run the job I get an error that says

The package execution failed. The step failed.

I am completely lost at this point. Any help would be greatly appreciated.

Thanks
Brian

View 1 Replies View Related

Executing Serial Dts

Feb 1, 2007

hi,

i am new here and interested in how to :

- execute serial dts in one single action or using vb script.

- change the status (enabled to disabled) and schedule using vb script.

Many dts make me tired when i have to change the status and schedule one by one.



regards.

View 1 Replies View Related

Executing Packages From ASP.Net

Oct 10, 2005

I just want to confirm my thinking here.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved