I would like to run several checklists against each sql server 2005 instance.
I can get a list of the sql server 2005 machines (Which are on the network) from a sql server table.
There is a another table which holds the list of the checks that need to be carried out against each sql server 2005 machine called tblCheckList.
The list of tasks in the table is briefly as follows:
1) get the path where the .mdf file is placed.
2) get the path where the .ldf file is placed
3) get the path where .BAK or .TRN are placed
4) get the path where the sql 2005 is installed
5) get the servicepack version
6) get the authentication
7) remove sample databases i.e. northwind or pubs, etc...
8) get the recovery model on UserDB
9) get the size of each database
1) Is there a way to call a second task 'upon completion' of the first one. Or would I need to place the code of the second task in one task? (This is v6.5) I guess the main purpose is to avoid conflict & see if one fails, the other may succeed, etc. I generally like to keep diff tasks separate, but one is dependent upon the completion of the other.
2) I want to restore a db, upon completion of the backup of another db. They are on separate servers, but i know i can use remote sp's.
Any advice, help, code, scripts, etc, would be appreciated. Thank you.
I want to schedule a task that checks if a file(s) exists on d:Acc and then run the sql script shown below i.e. with the file(s) to load the content of the file(s) every day new files will be loaded. after the file(s) are loaded the file(s) are deleted.
I currently can achieve the above using Java and sql server and running the program as a scheduled task... (only reason for change would be that i wouldnt require java, JDBC etc to run the software it would be easy manage, migrate etc)
I wanted to find out if it can be done with sql server directly? (i.e. check if file exist and ased on that carryout the task or other wise just ignore) What do i need to look for? does sql server have a procedural language in it to be used for such thing? does sql server have an inbuilt task scheduler?
I am currently using the following script in a java program please note that i have used fixed values in this script for readabliity (of what i am doing)..
@bulk_cmd varchar(1000) SET @bulk_cmd = 'BULK INSERT AccInv..VW_AccInvoice FROM ''d:Invbatch2001.txt'' WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = '''+CHAR(10)+''')' EXEC(@bulk_cmd)
I have an issue that hopefully someone can help me with. We have SQL server running in a 64 bit environment. We are running SQL Server 2005 SP2. The problem I am experiencing is that my SSIS script components produce the following error when the SSIS package is executed from the server.
Code: 0x00000003 Source: Set Perspective Connection Description: Precompiled script failed to load. The computer that is running the Script Task does not have a compiler capable of recompiling the script. To recompile the script, see the Microsoft Knowledge Base article, KB931846 (http://go.microsoft.com/fwlink/?LinkId=81885).
I have reviewed this article and everything points to SP2 as being the fix. Being that I am running SP2 there is something else going on. I was curious to see if anyone else has run into this issue with SP2.
I've had problems trying to get a new maintenance plan on a new server to work.
I'm running a plan that does an integrity check, reindex, shrink, backup, the a maint cleanup to get rid of backups older than 2 weeks.
I'm getting failures that say:
Error Number -1073548784
Error Message: Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'BAK',N'2007-10-18T18:43:41',1 " failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Yes, this is a post SP2 server. Current version, with the patches I've applied looking to fix this is: 9.0.3054
Additionally, if you go in to edit a maintenance cleanup task, you cannot click on OK, or the T-SQL. Changing values within it does not help.
I have two servers, A for dev and B for production.
On server A I developed a project containing a SSIS package using SQL Server business intelligence development studio. The package runs fine from the BIDS and also when I save it to SQLServer itself and run it as a scheduled job using the SQLServer Agent.
All ready to roll out to Server B I thought, so I then saved the .dtsx file to a shared network drive.
On Server B, I created a empty project with the same name as it had on Server A. I then imported the .dtsx file into the project using project > add existing item.
The package appeared to import ok but I now cannot execute any of the data flow tasks in isolation. If I right click on them, there is no option to 'execute task' as there should be, it is not greyed out, it's not there at all.
Also, if I attempt to debug the whole package I get a message saying 'This document is opened by another project'.
Can anyone help with this as my deployment to live isn't going very well to say the least!
Both server A + B are 32-bit 2005 std edition SP1 on W2003 Server std edition SP1.
I have this job that download 4 files once a month from the same server. The files are sizable and I need to download them in less than 5 hours total. In 2000 I use an active x script to generate the ftp script then execute the script. all four files download at the same time in 4 different tasks with no issues.
I am rewrote the process in 2005 so that it uses the IS FTP function but when all 4 ftp tasks kick off they all fail... instantly. the initially shared the same FTP connection manager so I created different ones for each and still the same result
the error is one that relates to changing directories.... Now if I just run one of the tasks it runs fine it is just when more than one try to run at once. I ended up putting in 10 second delays between each ftp task kicking off and it works just fine...
Does this sound like a bug??
Also... I am on SQL 2005 Enterprise SP1 on Windows 2003 enterprise SP1.
I have some "Execute T-SQL Statement Tasks" in a package. I would like to run this same package on another SQL Server without having to change it on the other server. Since the server name can be given when setting up the connection, I think if I leave the server name out then the package could run on any server? Is my assumption correct?
are there restrictions on what a DBA can do on a developer's edition?
i'm asking coz i get really bad connection problems when changing service accounts, client aliases, port numbers, connecting to different components like ssis, reporting services, database engine, etc...
in sql2000, changing these things are a breeze, is there some guidelines when making these changes in 2005?
Just want to know whether you guys do the following as a dba:
1. Setup a logging for tracking database as well as table size that containing size, indexing size. You can measure the growth. 2. Record for indexing for each database. I think this is over kill task. 3. Record database setup such as create statistic, update statistic etc.
These tasks can be automate to record every month for example and record this into Administration database for instance.
If I'm in the Data Flow tab in VS 2005, how can I select only certain components to run to test? I tried highlighting the ones that I want to run but it's running all of them in the tab...some of the components I want to take out for testing then maybe put back in later. If I delete the tasks I don't want to run, then I end up having to recreate them
Where to look for the web assitant created jobs on the server?I created a web page using the web assistant which is suppossed to get updated each time a value changes in a particular table,but I can't find the task which I created ,where do i look for that?I didn't use this wizard previously.thanks for any help! Sheila.
Would anyone be aware of anyplace I could find some good information on creating DTS custom tasks? I've come across a couple of articles from SQL Server Magazine, but nothing too substantive... Better yet, if anyone has any success (great or small), I'd like to hear from you and hear some of the things you did, what your custom task does, difficulty. I'm just trying to get an idea of how much work I have ahead of me....
If I have 2 scheduled tasks set for the same time (perhaps accidentally), will the SQL Executive start 1 and queue the other one until the first is complete and then run the 2nd task? Or will they both be started simultaneously?
I have been running the following production job successfully for a long time. It now fails, and the Task History Last Error Message displays 'No Message'. The log file ( C:MSSQLLOGMaint_TombV50.txt) shows it ran successfully, with a Return Code 0.
To all, If I have a scheduled tasks that is owned by 'sa', how can I assign permissions to allow another user, even the database dbo, to register the SQL server and view the scheduled tasks?
Hello , I need create tasks, which will be wake up daily to backup 4 databases. My questions are: 1. Can I create 1 task to backup all databases using TSQL command? Ex: DUMP DATABASE test1 to test1_backup DUMP TRANSACTIONS test1 to test1_backup with truncate_only DUMP DATABASE test2 to test2_backup DUMP TRANSACTIONS test2 to test2_backup with truncate_only Etc. If it’s possible can I just print this command to ‘Command:’ text box in the ‘New task’ window without writing the TSQL command into a text file and execute it with the ISQL program through CmdExec?????? 2.If first doesn’t work: can I do the same job, but create for every backup own task and run the same TSQL command for particular database. And if I can is it possible to schedule run all 4 tasks the same time or I should put time’s interval? 3. If 1. And 2. Are false. Give me your smart advice.
Is anybody knows how to transfer all the tasks in one server to another? Our development database will be transferred/copied to a new production box and that includes all tasks that we've created. We have almost a hundred tasks defined and we don't want it create manually. If someone had done this before, please give me a hint, i appreciate it very much!
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..
I just volunteered to be the database administrator of our new sql server. I am normally the programmer so I have little experience with database administration. There are some basics that I need some assistance with mainly when to perform various maintanence checks. For example, how often do I need to perform some of the dbcc commands that I have read about? How often do I check for data integrity, index fragmentation, rebuilding indexes, or defrag indexes?
I have to come up with a plan by tomorrow and I don't know if I have anything solid enough since I don't exactly know when to perform some of these tasks. :confused:
insert into trial_sales values(111,'11/01/2005',10000,'GAS') insert into trial_sales values(111,'11/02/2005',1000,'GAS') insert into trial_sales values(111,'11/02/2005',20000,'DSL') insert into trial_sales values(222,'11/01/2005',10000,'GAS') insert into trial_sales values(222,'11/02/2005',10000,'DSL') insert into trial_sales values(111,'11/15/2005',1000,'GAS') insert into trial_sales values(222,'11/15/2005',10000,'DSL') insert into trial_sales values(111,'11/25/2005',1000,'GAS') insert into trial_sales values(222,'11/25/2005',10000,'DSL')
Task 3 Display unique values
Display unique values in
create table t (colA varchar(25))
insert into t values('apple') insert into t values('bat') insert into t values('car')
ouput required:
a b c e l p r t
Task : 4
I have a table in the database I am working with with the following columns: (I have simplified this down for the sake of clarity) int p1 int p2 int p3 int s1 date d1 string s1
There is no primary key and the table more resembles a log than anything. e.g.
What I want to do is exttract the rows from the table where the combination of p1 p2 and p3 is distinct/unique and the date is the most recent one for all combinations of p1, p2, p3. So from the table above I would end up with the resultset:
say i have the following data... uId(int PK), productcode(int), quantity(int) 1,12,5 2,12,3 3,12,5 4,11,9
....and i have an order for productcode 12 with quantity of 6 products. i need to select only enough rows to reach a quantity of 6. in this case, i would want the first two rows which would give me a sum of 8. how can i do this without looping through each record, getting the quantity and exiting the loop when i have a sum >= 6?
The ID of TALBE1 is the primary key and the ID of TABLE2 is the foreign key. What's the right T-SQL JOIN statement when I pass the date of 2005/8/7, it will return the result as below:
create table sample_judge( judge_namevarchar(50)) go
insert into sample_judge values ('J Smith') insert into sample_judge values ('M Samuel') insert into sample_judge values ('S Shan') insert into sample_judge values ('K Anand') insert into sample_judge values ('K K K K Anand') insert into sample_judge values ('K R Shanth Anand') insert into sample_judge values ('K R Shanth Anand Kumara Velu')
go
Expected Output :
Smith J Samuel M Shan S Anand K Anand K K K K Shanth Anand K R Shanth Anand Kumara Velu K R
Hi, I am using Windows 2000 and created around 15 scheduled tasks. My scheduled tasks supposed to run every morning. I have to set password to first scheduled tasks every morning; otherwise none of the scheduled tasks works. As soon as i put password in the scheduled tasks, other tasks work fine.
I will be entering a job as a Junior Sql Server DBA with no senior above me. I would like to know what a DBA does everyday and all day.
If anyone can give me a thorough answer I would very much apreciate it, what are the day to day tasks.
Receiving projects is one thing, that is understood, but sometimes there are no projects and u r just supposed to do ur job.
What do you do as soon as you start your shift, then half an hour later at 9am what do you do then? then at 10am what do you do, then 11am, noon, 1pm, 2pm, 3pm, 4pm, etc.....
Do do you do all day and everyday? Someone please give me a thourough answer.
Folks, I am sort of new to SSIS.. I am doing the following
- Have a package that does some data transformation - The transformed data is written to a csv file - The csv file is copied to multiple destinations by a script task
All the above actions are performed in one package.
The SSiS log is not very helpful it just mentions a generic error code that is of no use.
Q1) If I need to separate the above actions in the Sql Server job scheduler as separate tasks do I have to split the above tasks into separate dtsx files ? If so how can we have the first dtsx kickoff the second one and can the second dtsx file share the variables ?
Q2) does anyone have any suggestions or examples on how log each step of your dts package to a log file
After SQL Server Express 2012 is installed, is there a way to automate setting up a few options? I need to enable the option for both windows and sql server authentication. Can this be done with Powershell or other tool? This is a situation where the user has absolutely no computer skills and there is no one available in their office that can set the options manually. I thought some sort of script would be useful for this.
no results during my search of the web, maybe one of yours can help.
Is there a possibility (during design time is OK) to find out, which TaskHost is before and after a CustomTask (by wiring in ssis-designer) and which type of wire it is (Error, Finish, Success).
Maybe the order of executables is providing this information, when there are no props or methods (and i found none), but then you don't know anything about the type.
I want to transfer a few access mdb kept in a folder to sql server. I am using foreach file enumerator. I want to perform the following actions :
1. At time of loading the file, check if the file name starts with "ED". If not, move the file to error folder. 2. Count no. of records that were there in the access db and that were transferred to the sql table. 3. I also want to run the package from command line. Even though i figured out that, i want to display only limited messages on the console. For eg: File processing begins, File currently being processed, TOtal rows transferred, File processing completed etc.
