Generally speaking when you want to optimise an application that relies on a database which is the order of the following optimization techniques
a) optimizing the spread of the pysichal elements of the database on different disks of the server
b) optimizing the use ot the RAM
c) optimizing the SQL
d) opimizing the OS
Thank you,
Ronnyy
Hello Everybody,
I have a small tricky problem here...need help of all you experts.
Let me explain in detail. I have three tables
1. Emp Table: Columns-> EMPID and DeptID
2. Dept Table: Columns-> DeptName and DeptID
3. Team table : Columns -> Date, EmpID1, EmpID2, DeptNo.
There is a stored procedure which runs every day, and for "EVERY" deptID that exists in the dept table, selects two employee from emp table and puts them in the team table. Now assuming that there are several thousands of departments in the dept table, the amount of data entered in Team table is tremendous every day.
If I continue to run the stored proc for 1 month, the team table will have lots of rows in it and I have to retain all the records.
The real problem is when I want to retrive data for a employee(empid1 or empid2) from Team table and view the related details like date, deptno and empid1 or empid2 from emp table.
HOw do we optimise the data retrieval and storage for the table Team. I cannot use partitions as I have SQL server 2005 standard edition.
Please help me to optimize the query and data retrieval time from Team table.
Thanks,
Ganesh
Hi,
I have two job J1 and J2, each one has 10 steps. Now I want J2 to be the 11th step of J1 and I did not want manually type all the steps of J2 to be 11-20 step of J2. Is there an easy way through TSQL to do this ?
sp_add_jobstep only works when the step is OS command or a script but not a job
Hi,
I'm using SQL Server 7.0. I have a job which runs DTS packages (1 package per step). When a task fails within my DTS package, I'd like an error returned for that step in the job thus stopping the job and not starting up the next step (DTS package) in the job. As it stands right now, if a task fails within the DTS package, that step in the job still returns a successful completion. Has anyone seen this before and is there something I can do to get the DTS to send a failure for that step in the job?
Thanks in advance,
Darrin
I am going to be moving multiple databases to a new server. Everything should go smooth, but I need to change a lot of the DTS packages that reference the old servername and replace it with the databases DNS record.
Is there an easy way to get a list of which dts reference the old server explicitly (not using database DNS)?
Thanks.
hi !!!i try to connect to my sql server local instance but it is always failed ..... can you please tell me the step by steps and options to use to install sql server on my machine and i think i need to use he personal copy rather than the standard as it will be on my machne not in the server??? please help
View 6 Replies View RelatedHello,
Can anybody tell me how many steps it's possible to put in one job.
The reason I ask is that we have a job that has over 500 steps (import data from Excel file into SQl table) and every time it runs we have different steps failures.
Does fact, that excel file was dropped and recreated, change DTS Id ?
Thank you in advance
Hi,
I am new to replication.
I have to replicate a db on SQL7.0 sp5 .
It's going to be transactionol.
Is there any article which explains everything - where to start from and where to end?
I mean everything step by step.....
TIA.
hello!
A job as a whole can be scheduled.But can a individual job step be scheduled?
thankfully in advance,
Rams.
Here is an interesting problem I can't figure out. I have a job with 6 steps as follows:
Step 1 - Import text file 1
Step 2 - Import text file 2
Step 3 - Delete all data from address tables 1 and 2
Step 4 - Copy data from imported table 1 to address table 1
Step 5 - Copy data from imported table 2 to address table 2
Step 6 - Delete imported taxt file table 1 and 2
Now when I run each of these steps individually, like running the dts packages and stored procedures my self it all works fine and the data in my tables appears to update. Then, when I set the job to run automatically, it says completed and no errors but my data hasn't updated. The job must be doing what it is meant to as it took about 40 seconds which is normal.
Ever seen this problem before?
Hello,
SQL Server 2005 Enterprise and new hardware have been ordered for our department. We currently run SQL Server 2000 (sp4). We have almost 500 DTS packages, 293 Jobs, and 14 user databases with hundreds of objects within.
Is there any documentation out there on how to scrutenize a current system? I have searched, and most of what I can find addresses migration planning with the assumption that the databases, packages, jobs, security, etc are ready to move over. We have a lot to think about before we can do that. We know we have redundancy problems (like View proliferation), table schema issues, obsolete DTS packages and Jobs, and otherwise a host of opportunities to 'clean house' and/or improve. We would really like to get a handle on what we are migrating before we migrate.
If you have any ideas or resources to you feel would be worth looking at, please share.
Thank you for your help!
cdun2
I've created SQL Server Agent jobs through management studio on SQL Server 2005. I can view and edit these jobs when I am logged into the server via remote desktop, but when trying to administer these jobs through Management Studio on a different machine, the steps do not appear in the job properties window.
Anybody else see this behavior? Know why it occurs? Is it a bug, or another wonderful "feature" of Manglement Studio?
i have setup a sql job with 4 steps
1-checkdb
2-indexdefrag
3-sp_updatestats
4-email notification.
if 1,2 success go next step
if 1,2,3 fails go step 4
if 3, 4 success quit with success
if 4 fails quit with failure
i created a script and pasted it in production server and i get this msg:
Warning: Non-existent step referenced by @on_fail_step_id.
Warning: Non-existent step referenced by @on_fail_step_id.
Warning: Non-existent step referenced by @on_fail_step_id.
how can i fix it? I think the error is due to step 4 not executing before calling it from stepe 1,2,3
welcome everybody
i want to publish my sql2005 server through my isa2004
so i do the following steps and i want to know if there is wrong in it or if
there is another step is missing or not?
1-i make editing in router configuration file to natting requests on my real
ip to the external interface of my isa
2-at isa i make sql publishing rule to forword requests to the ip of sql
server
(from:anywhere to: ip of sql server listner:external protocol:microsoft
sql server requests:appear from isa not original client ports:default
ports1433)
3-at sql server i enable allow remote and local connection over tcp only
4-at sql server i enable allow remote desktop
5-at sql server i enable firewall and in exception tab i add remotedesktop
and 1433 port
but still when i try to connect from internet using the studio managment
express tool using the real ip address(tcp:{my real ip address}) and login information of sql still
error occure and no connection opened....
note:scw was installed and i uninstall it
so what is the problem
why sql can't published
also i make at isa another rule to allow remote desktop to my sql server using rdp protocol but when i try to connect using remote connection to sql server it failed but when connect to any other internal server it work succesfully
I am migrating the SQL Server 2K instance from Windows 2000 server to Windows 2003 Server on a new box.
After restoring the database is there any tasks to be done to make sure the performance is not compromised.
For example... updating the statistics.....
Likewise are there any tasks that I need to run after the successful migration.
Thank you,
Gish
What in SSIS replaces DTS Task Steps? In DTS you could build tasks and assign them an order in which to execute. How is this replaced in the SSIS Control Flow. Thanks.
JGL
I am trying to create a SQL Agent job with 3 steps.
I want to delete three tables.
Step 1 ...delete table "X"
Step 2 ...delete table "X"
Step 3 ...delete table "X"
problem is that afer i create the three steps and start the job it never seems to finish the first steps and non of the other steps run, the job looks like is executing and never finishes. I break the job into three jobs each completes fine. I need this to runs one job, any ideas?
perplexed for sure.....
I want to update a char column and remove all the periods (.) - middle initial data. What are the basic steps in SSIS?
Source Table/Column --> Transform of some kind --> ? --> ?
I've never had to massage data and replace in the same table in my brief career so far but I see a lot more ahead.
There isn't a simple sql statement to do this, is there?
TIA
Hello,
Thanks for reviewing my question. I am trying to install SQL Server 2005 but I keep running into the same error:
SQL Server Setup failed to obtain system account information for the ASPNET account. To proceed, reinstall the .NET Framework, and then run SQL Server Setup again.
The only documentation I find on this is configuring the issolation mode in IIS 6.0.
Any help on is will be appreciated.
Peter
Hi All,
In creating 'steps' in JOBS, is it possible to execute many DOS CmdExec in
one step, instead of creating several steps with a single DOS-cmd in each.
For example:
Step1: bcp sourcedb..sourcetbl out source.dat -n -T -Ssourceserver
Step2: isql -T -Stargetserver -Q "truncate table targettbl"
Step3: bcp targetdb..targettbl in source.dat -n -T -Stargetserver
If I created a job executing those 3 functions in 3 separate steps then it works fine. But if I put all those 3 DOS command in one step, it won't work. Somehow,
SQL doesn't 'understand' it should execute after the end of each command OR
I missed something here (apparently so!).
I know if I put all those 3 DOS commands into a DOIT.BAT and execute it, it will work. But I want to use SQL Job to schedule it to run on a regular basis.
Anyone has run into this same problem? Thanks in advance.
David Nguyen.
My end game is to automate some of my monthly queries in a Job in SQL Server Agent. Right now I have two metric tables. One table is the name and comment with the PK. The secondary table is attributes/detail, such as reporting month, target and actuals.
I am currently running all different types of queries to get the aggregates. I'd like to get these into a job so it would run automatically and update the reporting table.
Would you recommend making one step or multiple steps for each query? I am trying to use an intelligent approach to begin to load the tables.
Hi all,
Can any one help me in this issue?I am new for this SQL server.By creating scripts from server system.I got the tables without data in my system.But i want to get the full database as usual there in server.
I have to convert my existing MS Access frontend and SQL backend app into ASP.NET web app with sql server backend.The version is 2000.Pls it's urgent?
Kranthi
Hi. I have an 'Attendance' table like this:PIN Year Category Days1 2006 Authorized 11 2006 Available 21 2006 Personal 32 2006 Authorized 42 2006 Available 52 2006 Personal 63 2006 Authorized 73 2006 Available 83 2006 Personal 94 2006 Authorized 104 2006 Available 114 2006 Personal 121 2007 Authorized 131 2007 Available 141 2007 Personal 152 2007 Authorized 162 2007 Available 172 2007 Personal 183 2007 Authorized 193 2007 Available 203 2007 Personal 214 2007 Authorized 224 2007 Available 234 2007 Personal 24I need to sum the days by PIN, Year and Category (that's easy...) ANDobtain a layout like this:PIN Auth 2006 Avail 2006 Pers 2006 Auth2007 Avail 2007 Pers 20071 1 23 13 14 152 4 56 16 17 183 7 89 19 20 214 10 1112 22 23 24How can I do this by queries without writing too many intermediatesteps ?What I have done is this (5 queries, 2, 3, and 4 building on top of1,and 5 building on 2, 3, 4).1 = Table1_Crosstab:TRANSFORM Sum(Table1.Days) AS SumOfDaysSELECT Table1.PIN, Table1.YearFROM Table1GROUP BY Table1.PIN, Table1.YearPIVOT Table1.Category;Then, based on that,2 = Authorized:TRANSFORM First([1 = Table1_Crosstab].Authorized) ASFirstOfAuthorizedSELECT [1 = Table1_Crosstab].PINFROM [1 = Table1_Crosstab]GROUP BY [1 = Table1_Crosstab].PINPIVOT [1 = Table1_Crosstab].Year;3 = Available:TRANSFORM First([1 = Table1_Crosstab].Available) AS FirstOfAvailableSELECT [1 = Table1_Crosstab].PINFROM [1 = Table1_Crosstab]GROUP BY [1 = Table1_Crosstab].PINPIVOT [1 = Table1_Crosstab].Year;and4 = Personal:TRANSFORM First([1 = Table1_Crosstab].Personal) AS FirstOfPersonalSELECT [1 = Table1_Crosstab].PINFROM [1 = Table1_Crosstab]GROUP BY [1 = Table1_Crosstab].PINPIVOT [1 = Table1_Crosstab].Year;and finally5 = AllSELECT [2 = Authorized].PIN, [2 = Authorized].[2006] AS [Auth 2006],[3 = Available].[2006] AS [Avail 2006], [4 = Personal].[2006] AS[Pers2006], [2 = Authorized].[2007] AS [Auth 2007], [3 = Available].[2007]AS [Avail 2007], [4 = Personal].[2007] AS [Pers 2007]FROM ([2 = Authorized] INNER JOIN [3 = Available] ON [2 =Authorized].PIN = [3 = Available].PIN) INNER JOIN [4 = Personal] ON[3= Available].PIN = [4 = Personal].PIN;It works, but... I am sure that this is an awkward way of doing it.Isthere any other, more elegant, way, please ? Besides, what if I hadnot 3, but 15 categories, for example ????Thanks a lot for your time reading this, Alex
View 4 Replies View RelatedHi all
on the last few days i build SSIS packages for WH db and now i need to write doc. for packages steps defination i need to write the doc with Prof. ways if anyone help me on this , for sample doc. or concept or the way
thanks in advance
I have a few reports. I want to run each report as one step of sql agent job. If the report succeeds only then the job should move to the next step ? is it possible
I am able to make separate jobs for separate reports but not as steps of one job.
Thanks in advance
Hello,
I am putting my first steps into the new Service Broker.
I (think i) undestand what it can do, so i execute some example sript i found on the Internet.
But with none om them i receive any message into a queue.
Can somebody help me.
The undeneath example i use.
----------------------------------------
USE AdventureWorksDW
GO
CREATE MESSAGE TYPE HelloMessage
VALIDATION = NONE
GO
CREATE CONTRACT HelloContract
(HelloMessage SENT BY INITIATOR)
GO
CREATE QUEUE SenderQueue
CREATE QUEUE ReceiverQueue
GO
CREATE SERVICE Sender
ON QUEUE SenderQueue
CREATE SERVICE Receiver
ON QUEUE ReceiverQueue (HelloContract)
GO
DECLARE @conversationHandle UNIQUEIDENTIFIER
DECLARE @message NVARCHAR(100)
BEGIN
BEGIN TRANSACTION;
BEGIN DIALOG @conversationHandle
FROM SERVICE Sender
TO SERVICE 'Receiver'
ON CONTRACT HelloContract
SET @message = N'Hello, World';
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE HelloMessage (@message)
COMMIT TRANSACTION
END
GO
-----------
From here i do a select * from queue. with 0 rows selected
select * from ReceiverQueue or
RECEIVE CONVERT(NVARCHAR(max), message_body) AS message
FROM ReceiverQueue
Any help is welcome
Hi, I have one data flow with 10 sources and destinations in the flow. For the sources I'm using the datareader for an odbc and for the destination I'm using the ole db destination source. By default these run parallel when executed. Is there a way in the dataflow to run them step by step instead of creating 10 different data flow tasks in the control flow? Or is it better to have 10 different data flow tasks?
Good Evening!
First and foremost any help would be greatly appreciated!My question is how to use a temp table within two (2) seperate called stored procedures?
I am using MS ReEporting Services and I call a stored proc that creates a temp table and executes some summarization code - so the scenario is as follows:
1. I select the first MS REporting services from a menu item - which creates the Temp Table (within a stored procedure) and the report is displayed
2. Within option (1) (MSRE - I have defined a navigation link to call another report - which uses a stored procedure) which I would like to access the temp table created in step 1.I know that #Temp_Table will only be available for step 1 and I have tried to use##Temp_Table which should be available for my session - which maybe I am not understanding correctly in that I thought a ##Temp_Table would be available for my session until no other T-Sql scripts are trying to access or I sign off!I don't really want to create a table and have it "hang around" until someone selects the menu option again to select the program again which then the T-Sql will then Drop the table.Any insight or help is sincerely appreciated!Best regards
Hi,
Previously I was performing the process of populating my DataMart and then processing my cubes via 2 separate job scheduled one after the other and with about 30 minutes idle time in-between (just in case).
I now have tried to make a single job with both of the above as 2 steps (one after the other) of a single job.
However when I was done, I did a right click and tried to run the newly created job but instead of what I was hoping (that the job would start, would perform the 1st step, followed by the 2nd), it instead popped up a dialog box asking (I think) which step needs to be executed :(
Is there a solution so that when the job is scheduled to run, it should start performing the steps in the order rather than waiting for someone to select which step to perform.
Will really appreciate urgent reply.
Hi.
I have set up my confirmation system to work as thus:
1) user registers
2) registration goes into a temp table with a code
3) user gets a MD5 code in his inbox
4) user clicks back
5) click-back page moves registration data from TEMP table to USER table and sends the user to the signin page.
6) ...
Now, my question is - how do I handle this final step in account
confirmation? Should the first signin act as a final confirmation of
the users account? This makes sense. Should the data in the USER table self-delete after a day if there has been no first sign-in? Should I have a column in the USER table to show if the first login has happened? How should I do this?
I am sure I could mess around with this but it would be great to
get feedback from somebody that has done this multiple times and has a sense of what the best practice is (based on large volume examples).
Your feedback is much apprecaited.
Thanks.
Hi,
Previously I was performing the process of populating my DataMart and then processing my cubes via 2 separate job scheduled one after the other and with about 30 minutes idle time in-between (just in case).
I now have tried to make a single job with both of the above as 2 steps (one after the other) of a single job.
However when I was done, I did a right click and tried to run the newly created job but instead of what I was hoping (that the job would start, would perform the 1st step, followed by the 2nd), it instead popped up a dialog box asking (I think) which step needs to be executed :(
Is there a solution so that when the job is scheduled to run, it should start performing the steps in the order rather than waiting for someone to select which step to perform.
Will really appreciate urgent reply.
I am going nuts trying to get this to work. Maybe someone can help me. I am running sql server 2000 and am using a dts package. The package runs fine on sql server. When I access it using asp.net I get the following error:
----------------
The execution of the following DTS Package succeeded:
Package Name: MapsImport
Package Description: Import Excel to Maps table
Package ID: {C56FF415-CD35-461E-98E4-BB2430163413}
Package Version: {30415D05-B121-4C4B-991C-43496EA47090}
Package Execution Lineage: {EAF14EB3-F6C1-4D9B-A4B9-46E31AF4B608}
Executed On: NS23
Executed By: ASPNET
Execution Started: 6/7/2006 3:33:12 PM
Execution Completed: 6/7/2006 3:33:17 PM
Total Execution Time: 5.11 seconds
Package Steps execution information:
Step 'Copy Data from Sheet1$' to [dbname].[dbo].[Maps] Step' failed
Step Error Source: Microsoft JET Database Engine
Step Error Description:Failure creating file.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:5003436
Step Execution Started: 6/7/2006 3:33:12 PM
Step Execution Completed: 6/7/2006 3:33:17 PM
Total Step Execution Time: 5.015 seconds
Progress count in Step: 0
----------
I searched through several forums and found that this seems to be a permissions problem. I set the IIS process to Low, I also added the <identity impersonate="true" /> tag to my web.config file. After adding the tag I get a new error message:
----------
No Steps have been defined for the transformation Package.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Runtime.InteropServices.COMException: No Steps have been defined for the transformation Package.
------------------
I am at a stand-still trying to get any further. Can anyone teel me what else I might be able to try to resolve this problem?
Here is my code for executing the package:
-----------------
Sub Page_Load(Src As Object, E As EventArgs)
Dim cnnstring as String="Data Source=NS32;Initial Catalog=dbname;Pooling=False;Min Pool Size=100;Max Pool Size=200;User ID=userid;Password=password"
Dim cnn as SqlConnection
Dim cmd as SqlCommand
Dim rs as SqlDataReader
Dim sql as String="Truncate Table Maps"
'Empty Equipment Contract Pricing table
cnn=New SqlConnection(cnnstring)
cnn.Open()
cmd=New SqlCommand(sql, cnn)
sql="DELETE FROM Maps WHERE Name IS NULL"
cmd=New SqlCommand(sql, cnn)
rs=cmd.ExecuteReader()
'check to see if table is empty
If rs.HasRows Then
Response.Write("<p><b>Failed to empty table.</b></p>")
Else
Response.Write("<p><b>Table successfully emptied.</b><br><br>Importing Data...<br>Please wait...</p>")
End If
'declare variables for DTS
Dim objDTSPackage, objDTSStep, strResult, blnSuccess
Const DTSSQLStgFlag_Default = 0
Const DTSStepExecResult_Failure = 1
'Use stored procedure on sql server to import data
objDTSPackage = Server.CreateObject("DTS.Package")
blnSuccess = True
'Load package from sql server
objDTSPackage.LoadFromSQLServer ("NS32", "user", "pass", DTSSQLStgFlag_Default, "pass", "", "", "MapsImport")
'Explanation: LoadFromSQLServer ("ServerName", "Username", "Password", "Flags", "PackagePassword", "PackageGUID", "PackageVersionGUID", "Package Name", "PersistsHost")
objDTSPackage.Execute
'walk through steps and check for errors
For Each objDTSStep in objDTSPackage.Steps
If objDTSStep.ExecutionResult = DTSStepExecResult_Failure Then
strResult = strResult & "Package " & objDTSStep.Name & " failed.<br><br>"
blnSuccess = False
Else
strResult = strResult & "Package " & objDTSStep.Name & " succeeded.<br><br>"
End If
Next
'display success or failure message
If blnSuccess Then
Response.Write ("<p><b>Package Succeeded.</b></p>")
Else
Response.Write ("<p><b>Package Failed.</b></p>")
End If
Response.Write ("<p>"& strResult &"</p>")
rs.Close
cnn.Close
'Response.Redirect("list.aspx")
End Sub
----------------------------
The code fails on the line where it says:
objDTSPackage.Execute
Thanks in advance!
-Mike
I have a problem with a SQL Server (2000) Agent Job that has 3 steps that don't produce the desired outcome when invoked in total but will produce the desired outcome if the first step is broken up from the last two steps.
1) The first step uses third party software to generate PDFs to a folder.
2) The second step executes the following command to ftp the PDFs to a folder and move these PDFs to a backup folder:
EXEC master..xp_cmdshell 'm: & cd C:Program Filesfoldername1 AutoGeneral_Ftp & dir & FTP -n -s:C:Progra~1foldername1AutoGeneral_FTPupload_G eneral.ibc www.someorgname.org & move /Y
\networkdriveArchiveGeneral*.pdf \networkdriveArchiveackupsGeneral'
3) The third step determines if any files remain in the folder that contained the PDFs. If files are still in the folder then the FTP and move step (step 2) did not work and a stored procedure is invoked to send an email to the appropriate administrator
DECLARE @result int
EXEC @result = master..xp_cmdshell 'Dir "\networkdriveArchiveGeneral*.pdf" | find /i "file"'
IF @result = 0
exec ibc_sp_Email_Report_Failure 'FTP General Report'
When I invoke this job from Enterprise Manager and view the job history, it says that all steps executed successfully (which I understand may be the case even if the files were not FTPed or moved since the command can still return a code indicating success even though it didn't do what I expected.) In fact, the PDF is generated and written to \networkdriveArchiveGeneral
eportname.pdf. Steps 2 and 3 do not do what I expect. The PDFs still remain in the folder.
But when I start the job from step 2, the files are then moved. So, invoking the entire job does not move the files; invoking the job from step 2 moves the files.
Simple, you think, it is obvious that you didn't set up your job steps correctly in that step 1 does not go to the next step upon success. But I already checked that. Step 1 goes to the next step upon success.
Anybody ever come upon this problem? Any suggestions as to what else I can look at?
Thanks in advance,
Stedan