HI,
We are currently trying to import the Data from one DB to Multiple DBs.And these multiple DBs connection strings must be configurable. This I can do it by reading the connection strings from dtsconfig file and execute it for each DB.Is it possible to execute for all the DBs in parallel(Multithreaded).
Thanks,
Sabari.
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 = new SqlCommand();
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 and 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
Hello All,I am am having a strange problem. I am trying to execute a DTS packagefrom VB .NET (.aspx) page. The DTS package takes 21 minutes to execute.When the aspx page sends a command to MS SQL server to execute this DTSpackage it starts executing. After 8 minutes the .aspx page shows error"Cannot Find Server" and errors out with a white screen. Theinetresting thing is the DTS package is still being executed in backendand completes execution successfully.I have no clue why this is happening because there are other DTSpackages that I am executing through the same .aspx page that takesabout 3 minutes to complete execution and are working absolutely fine.By the way I have a stored procedure wrapper over every DTS package. Sowhen i have to execute a DTS package I execute a stored which inturnexecutes a DTS package.Thanks for reading my query. If you have any suggestions please let meknow.Thank you in advance.
View 1 Replies View RelatedHi,
I have a very peculiar problem. I have few CommandExec jobs on SQL Server 7.0 which has been scheduled to execute at a specific time. The jobs are executing properly but, the problem is they stop after the completion. i.e after finishing the jobs they still flash a message "Executing the job" they don't flash the message "Not Running" this is happening only to commandExec jobs, I have also few TSQL jobs which are working properly. I tried even giving the duration (start and stop time) but no use. Has anyone encountered this problem? If you have a solution PLS pass it on to me. I am in a very desparate situation.
Thanks in advance.
Robert
I am generating hundreds of lines of sql with a tool and wish to execute is in runtime. The sql consists of table creation, procedure creation, aswell as inserts and updates. When i try with SqlDataAdapter I get an error. Does anyone have a solution on what I can do?
View 2 Replies View RelatedHey 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,
jonathan
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
Thanks
I am trying to execute around 3 SSIS packages using Execute package task by having all the 3 in one SSIS package,
I am getting the below error:
Error: Error 0xC001000A while preparing to load the package.
The specified package could not be loaded from the SQL Server database. .
Can I use Execute package task for this purpose??
Thanks!!
I am probably missing something simple here but...
I have a CLR Stored Procedure where I am attempting to execute multiple SQL commands. Here is what happens.
Case One
1. Stored Procedure is invoked.
2. Create SQL Connection.
3. Open the connection.
4. Begin Transaction.
5. Create Command from Connection.
6. Set Command objects Transaction property to transaction created above.
7. Set Command Text Property to SQL Query.
8. Execute Query via ExecuteReader and get SQLReader.
9. Read query result set.
10. Close QueryReader.
11. Set Command Text Property to another SQL Query.
12. Execute Query
This is where I get one of two exceptions.
1. An Exception stating that I cannot have parallel transactions.
or
2. An Exception that I have an open SqlReader that must be closed.
Case Two
1. Stored Procedure is invoked.
2. Create SQL Connection.
3. Open the connection.
4. Begin Transaction.
5. Create Command from Connection.
6. Set Command objects Transaction property to transaction created above.
7. Set Command Text Property to SQL INSERT command.
8. Execute Insert command.
9. Set Command Text Property to SQL Query command.
10. Execute Query Command via ExecuteReader.
At this point the stored procedure appears to hang.
Could someone please enlighten me if I have missed something obvious while reading the manual, regarding what you can and cannot do?
Thanks in advance.
Is there a way to get the Report Manager to allow a single user to
execute more than 1 report at a time? Currently, each user can only
have a single report being executed at a time. It looks like any
other reports are being queued and executed serially.
Hi,
I am executing following script which return more than 1 query/row as there are more than 1 log backup :
select @Query = 'RESTORE LOG ' + @dbName + ' FROM DISK=' + '''' +@path+''''+ ' WITH' + ' NORECOVERY, FILE= ' + convert(varchar(10),Position) from #temp where BackupName = 'Log_Backup' and BackupType = 2 and Position> @Diff_Position EXEC(@QUERY)
[Above script restores database with latest Differential backup file and latest log files. ]
Now I want to execute this, but @Query don't work for more than one row returned . Can you please suggest me how to execute such query?
Thanks,
Dipak.
I am trying to execute around 3 SSIS packages using Execute package task by having all the 3 in one SSIS package,
I am getting the below error:
Error: Error 0xC001000A while preparing to load the package.
The specified package could not be loaded from the SQL Server database. .
Then I did the following:
1. Right click on MSDB
2. Import package
Inside Import package window:
Package location: SQL Server
Server: servername
authentication: windows authen
Package path: \serverC$folder1SSIS Packagesfolder2
Import package as:
Package name: package.dtsx
Below is the error I am getting:
TITLE: Import Package
------------------------------
Cannot find folder "\serverC$folder1SSIS Packagesfolder2".
Can I use Execute package task for this purpose??
Thanks!!
Hi all,
Is there any way so that we can run/ execute multiple jobs or local packeges at once. Can we create any sql script which will execute multiple jobs/packeges at once. please advise
Hello!We have a set of individual .SQL scripts which we would like toexecute against a MS SQL Server 2000. Is there any way to have ISQLutility (or any other means) to execute all of them without having toestablish a separate database connection for each script:isql -Ux -Py -Ss -i script1.sqlisql -Ux -Py -Ss -i script2.sqlisql -Ux -Py -Ss -i script3.sql.....isql -Ux -Py -Ss -i scriptN.sqlAll scripts are in the same location, which could be made visible toby the SQL Server itself (so either a client-based or a server-basedinvokation is ok)TIA
View 6 Replies View RelatedHi All
I have been asked by developers if there is any advantage in processing multiple clustering models simultaneously by using AMO and multiple threads as against processing one after another.
I have limited experience with Analysis Services but based on my reading I don't see this method providing any advantage.
Does anyone have any recommendations or advice? The system Enterprise Edition running on an x86 Server with 2 dual core processors and 4GB of RAM. Would the answer alter if the server running x64 version of SQL Server and Windows.
Thanks
Nadreck
Happy new year everyone!!!
Ok, now for my not so happy new year problem ... here's the scenario:
I have a simple task of exporting data retrieved by a parameterized stored procedure to a delimited text file ... task done in 30 mins max using dataflow task from OLE DB Source with SQL Command to Text File Destination ... thank you very much SSIS!
But the problem is the OLE DB Source executed the SP MULTIPLE TIMES (3 to be exact)
Actually configuration is:
1. OLE DB Source
2. SQL Command access mode
3. The following command written in the command text box:
SET FMTONLY OFF;
exec sp_SampleSP @param1=?, @param2=?
4. Configured parameter mapping.
The stored procedure logs to a database table everytime it is executed to report how many rows were selected (please don't crucify me with this method, i'm re-using procs from old system). The log shows that during execution of this package the SP was executed on 3 times.
Help anyone? Please ...
Hi,
Can we execute multiple instances of the same SSIS package simultaneously??
If yes, how?
If no, what is the work-around to simulate such a functionality?
Thanks in advance.
Regards,
B@ns
Hello all -
Based on advice from this board, I am setting up our newly-converted DTSX packages (sql server 2000 (DTS) to 2005 (SSIS) package conversion) to utilize package configurations to make it easier to run them in multiple environments (dev, testing, production, etc...). This seems to be working just fine, but I now have another question.
We have approximately 12 DTSX packages which need to be executed in a specific sequential order. I was wondering what the best / most widely accepted method is to allow for running them as an automated process. If you double click on each DTSX file individually, the "Execute Package Utility" pops up, and you can run the package. I'd like to know the best way for us to be able to run each package, one after the other (each one waiting until the previous one completes before it begins), until they have all processed. It would be nice if it did not rely on SQL Server being installed on the machine on which it is being run...
thanks for all the fantastic support on this site
Does anyone know of a way to execute sql code from a dynamically builttext field?Before beginning, let me state that I know this db architecture isbuilt solely for frustration and I hope to make it better soon.Unfortunately, there is never a non-crucial time in which we can do anupgrade, so we are stuck for now.Point 1:There are multiple tables: students, courses, cross-referencestudent/courses, teachers, cross-reference teacher/courses, andothers.Point 2:Yearly archiving is done by appending a 2 digit year suffix to thetable and rebuilding the table structure for the current year. Thatis, each of the tables named above has an archive table for 1999,2000, 2001, etc. This leads to many tables and a big mess whenunioning them.Point 3:A history report is run by building a big query that unions each unionof tables (students, courses, etc) by year. This query has grown toobig for a varchar(8000) field. Actually, it's too big for 2 of them.Point 4:I don't want to write code to maintain any more varchar(8000) fieldsfor this query. It should be much more easily handled with atemporary table holding each bit of yearly archive data. I have builtthis and it works fine. I have also figured out how to pull the rowsfrom that table, concatenate them, and insert the resulting lump intoa text field in another table.Point 5:I haven't figured out how to grab the 5 or so records from that tableand execute them on their own. I figured I could grab them, put theminto a text field that's big enough to hold the whole query and thenselect and execute that text field. I've had no luck with that and Ihaven't had any luck finding any references that might help me withthis problem. I keep thinking of nesting execute() calls, but thatdoesn't work.I'm open to questions, potential solutions, hints about different wayto approach the problem, anything.Many thanks in advance,Rick Caborn
View 7 Replies View RelatedI am trying to create a logon trigger. As I am testing this, I discovered that each time I do a connection, I get 19 rows, inserted into my audit table. I ran profiler, and I see it is going through the logon trigger multiple times, for a single connection. So, what am I doing wrong? The code is fairly simplistic, and the profiler doesn't give a clue, as to what is going on. When I look at the output, I see the spid for the first couple of connections are different, then a spid, that is different from those 2 is in the next 17 rows. But, when I do an sp_who2, that spid does not exist.
This issue was noticed on a 2012 version, that I was first testing on, then had the same issue on a 2008 R2. I am currently testing on a 2014 version, that is doing the same thing. Is the logon trigger itself, firing, and causing this?
I also tried using the After Logon option, and got the same issue.
Here is the code:
CREATE TRIGGER LogonAuditTrigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
DECLARE @Body NVARCHAR(2000),
[code]....
Hi all,
I've got a DTS job which has lots of tasks in it. I've also got quite a few flows of workflow and i've noticed that a task won't execute if it has both Failure and Success workflow pointing to it... It can have multiple failures pointing to it and it will execute but it can't have multiple successes or a combination of workflows...
Does anyone know a way to get around this or to change the 'AND' ing that seems to be on the workflow going into a task..
Thanks.
Howdy,
Now this one I don't think will be fixed by changing the length of a variable.
Anyway, In this nifty DTS package I've created I have it set so that on the failure of an SQL task DTS should send me an e-mail letting me know it failed. The SQL statement in the DTS task is "EXEC WEB_Check_Files". In the Stored Proc I then call RAISERROR w/ a user defined error message w/ severity 10. When I call RAISERROR the DTS packages logs everything just kosher but skips past the "on fail" e-mail message. It refuses to execute it and don't know why. Spent the last hour or two going through docs looking at RAISERROR. Tried RETURN 1 to see if that would do anything and nadda. I also don't have "Fail package on first error" checked.
Don't know if it matters but I'm executing the package through the "play" button in Enterprise Manager.
So... anyone got an idea? I'm fresh out.
Yall rock!
Thanks,
Me
I hope I've done due dilligence before posting for help. I've combed a lot of Google and dBforums search results with no luck.
Anywho, this *seems* like it should be simple: I have a Transform Data Task, into which I've introduced deliberate errors, followed by an ActiveX Script Task that is supposed to fire based on the failure of the Data Transform Task. The second task is joined to the first via an "On Failure" workflow step.
The problem: The second task never fires. The first task fails as expected, but the second one just shows the "Not Run" indicator in the package results after executing.
Here is a graphical illustration of the package and results: http://www.bountifulware.com/blogs/rex/dtsproblems.html
I've experimented with the transaction settings in the package properties, as well as the transaction settings in the workflow properties of each step. I don't particularly want the package as a whole to be couched in a transaction, but if that is part of the equation for making the "On Failure" step fire, I'll happily go along. Also, in the Data Transform task with the deliberate errors, I have the max error count set high, as I want the task to continue logging errors for each record that chokes. I've tried various settings there as well, however.
Thank you in advance -- I'll try to contribute more and leech less after this.
I would like to build a workflow system where 100 processes are requesting an item from a ~1.000.000 items large DB, process that item and move it to the next state. The problem with the current implementation I tried is that I get deadlocks....
The DB table looks like:
CREATE TABLE Transactions(
itemid CHAR(32),
status TINYINT not null default 0,
result INT not null default 0,
lockby TINYINT not null default 0,
.... (etc.)
PRIMARY KEY(refno)
);
CREATE INDEX IxStatus on transactions (status)
Each process (with his own ID) is doing 4 step:
1) update transactions set status=1, lockby=<ID> from
(select top 1 itemid from transactions where status=0) as t1
where t1.itemid=transactions.itemid
2) select itemid from transactions where status=1 and lockby=<ID>
3) process item
4) update transactions set status=2,result=<RESULT> where itemid=<ITEMID>
Does anyone has a suggestion on how to do this?
I know the idea was to seperate workflow and dataflow, but I have come across a scenario where it would be useful for a branch of a dataflow to wait until another branch has finished.
I have some transactional data which records events for the start and end of a session. I want to build a list of unique sessions with the start and end date. I currently have the list of events sorted by time, followed by a conditional split for the start and end events. I can then insert all of the start events and would like to wait until all of the starts are inserted before updating them with their relevant end times.
Is this achievable?
Does anyone else think it would be a good idea to be able to set precendence across multiple branches of a data flow?
Does anyone have a better solution?
I know this is the wrong forum, but is there a way to model this against the transactional data in SSAS, I will move this question to the SSAS forum if anyone can think this would work!
Philip Coupar
Hi
Can any one provide sample for work flow in SSIS . and how to excute package conditionaly like if package1 is sucess then excute package2 else excute package3 .( more like writeing batch file in Data Stage ETL Tool ).
Thanks & Regards
Jegan.T
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
Hello,
I have a SQL Server 2000 DTS package in which the first step executes a batch file. The batch file contains FTP commands that log into an FTP server, and pull down whatever file is there.
I set up a failure workflow to send an email if the step fails. When I have a SQL Server job run this package, and there is no file to dowload, the whole package fails without the failure workflow result firing.
For the step (DTSStep_DTSCreateProcessTask_1), I have the 'FailPackageOnError' property set to -1. In the package properties, I have the check box for 'Fail Package on First Error' cleared.
What do I need to do so that the failure workflow occurs when the step fails?
Thank you for your help!
cdun2
Trouble with Workflow
Hello. I have a DTS package that executes some tasks of the type "Execute Package Task".
Every Task has a Condition of been executed just in case of success. I understand that only if the precedent task ends successfully the next one would be processed. My problem is that the DTS continues even if one of the previous steps fails. Would you have any idea?____________________________________________________________________
My escenario is something like this:
EP: Execute Package Task
ES: Execute Sql Task
______ _____
EP# 1 (on success) ----> EP# 2 (on success) ----> ....
______ _____
So, EP#1 fails but EP2# is also executed.
Hi,
I have 3 data flows connected sequentially on my workflow before processing dimensions and facts which checks data from different databases and if conditions does not meet then writes to log table. So if it writes to log table , I have to quit and finish the workflow.
How can I do that?
thanks,
J
i created a DTS package in SQL 2000 using the enterprise manager.
I have defined a SQL task to drop some temporary tables on failure of another SQL task. Also the same temporary tables need to be dropped on success of another task. the on success workflow is working fine. but when i add the on failure workflow to the temporary table dropping task, the temp table droppin is not getting excuted at all either for success nor for the failure. please help me out.
Hi,
I just recognized a strange displaying problem in one of my SSIS jobs.
I created job which contains a sequence container. Within the sequence container there is one "Execute SQL Task" and one "Foreach Loop Container". Within the "Foreach Loop Container" there are 4 task which are connected with precedence constraints.
Now my problem is that if I load the SSIS job and open the "sequence container" while the "Foreach Loop Container" is already open then the precedence constraint won't be displayed. If I close and open the "Foreach loop container" again then the precedence constraints will be displayed again.
My first assumption was that it might be a problem with the display drivers of the computer ....however the problem appears also on any other computer.
Does anyone know how to solve this display problem without closeing/reopening the container?
Thanks,
StSt
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