Simple Question; Is There A Way To Geneteate A List Of Databases, Jobs, DTS Packages
Jul 5, 2006I need to generate a list of databases, jobs, DTS packages, Linked servers, and users.
Is there a simple script or procedure for this?
Thanks
ADVERTISEMENT
Items In List A That Don't Appear In List B (was Simple Query...I Think)
Jan 20, 2005Ok, I want to write a stored procedure / query that says the following:
Code:
If any of the items in list 'A' also appear in list 'B' --return false
If none of the items in list 'A' appear in list 'B' --return true
In pseudo-SQL, I want to write a clause like this
Code:
IF
(SELECT values FROM tableA) IN(SELECT values FROM tableB)
Return False
ELSE
Return True
Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.
I may have to achieve this with some kind of logical loop but I don't know how to do that.
Can anyone help?
BACK UP DTS PACKAGES / JOBS
Oct 22, 2001Perhaps an ignorant question, but is there a way to back up the jobs and packages in SQL server 7..????
View 2 Replies View RelatedPackages Related With Its Jobs...
Oct 26, 2005Hi,
I have diferent jobs scheduled in the system but I can´t find which Package is launched by the job. I only have this information: (double click over the job, steb tab, modify button for any job step, in the command text window)
DTSRun /~Z0x9D852D31537078274085C85BE05756CCE0CA78671EC12A 4BDFFEC4E5E6017E4841EE5F41C492CCAA7F5746CA894011BB 376479B6E679EC3C6045C328D1EF1CDA7CF28B6EEFE9DFE923 7DF5662AE09BD6215C35AA4121BD2DE4433C7BABEE42EC87E7 0F47EA7C01FB44CB28
I would like to know the Package name related to.
Any help would be very appreciated.
Regards
Uh Oh,,,both My Jobs And My Packages Fail-any Ideas?
Jan 8, 2001Today when I came into work, I noticed all of my jobs failed on a particular server. I tried to manually kick off the packages, but to no avail. Both the job history and the error message from the packages state a timeout error.
Any idea on this? Also, I have no problem running a query from query analyzer...even on a remote machine.
Any help would be appreciated
Trevor
Moving Local Packages And Jobs From One DB To Another
Feb 3, 2005Hi boys and girls, I am a total newbie to ms sql. So please be gentle.
I have a ms sql database on one server but I need a duplicate of the ms sql database on another server with all packages and jobs duplicated on the new server.
I was able to export the database over to the new database on the new server no problems there.
The problem is under DTS I have "local packages" that all need to be moved to the new server. I am able to export them into my computer. But on the new server there is no import for the local packages. How do I get these packages onto the new server into my server's ms sql???
Also under the management and sql agent manager, there are jobs that are on the old db, that I need to go to the new server in ms sql.
Any help at all would be appreciated!!!
Thanks!!!
SQL 7 To 2000...how To Transfer User Id's, Jobs, And DTS Packages
Feb 11, 2003Hello,
I want to move a db from a SQL server 7 to SQL Server 2K. I'll do a backup and restore function to move the database, but how do I handle the objects mentioned in the header ?
Bothe servers (old and new) will have the same name, as well as the DB
All help is appreciated !
Thanks
SQL Agent Jobs Hung - SSIS Packages
Jun 26, 2007
I'm having an issue in SQL Server 2005 with jobs that execute SSIS packages. The jobs run fine for a week or so, then I'll come to find that four or five (of the ten or so jobs) are hung in "executing" status. They seem to hang indefinitely (as some have been "executing" for hours with no end. The schedules of the hung jobs are all different, varying from every 10 minutes to nightly. The packages perform completely diffent tasks, as well. I can't seem to find any common thread with the jobs that get hung, other than they are all executing SSIS packages.
I've tried manually stopping the jobs and restarting the agent and SQL Server but the jobs hang again on there next scheduled run. The only thing that fixes the issue is rebooting the box, and then the jobs hang again in a week or so. Could some sort of memory leak be consuming resources throughout the week and be causing the jobs to eventually hang? I just rebooted the box and the sqlagent90.exe process is currently using about 7mb of memory. I'll keep an eye on it. Any other suggestions?
I've thought of creating another job that stops jobs that are hung, but what's to say that this job won't get hung as well? Plus this seems like a band-aid fix...
I don't recall having these problems until installing SQL Server 2005 SP2. Could this be related? I've searched like crazy and still can't find a resolution to this. It's becoming a big PITA...
Anyway, any suggestions would be very much appreciated!
Using ASPX Page To Trigger SQL DTS Packages / SQL Schedule Jobs
Aug 19, 2007
Hi,
any1 tried using a button in aspx page to trigger SQL DTS packages or SQL schedule jobs??
(*using SQL2000)
Generate List Of The Schedules Of All Jobs
Jun 12, 2015I wanted to check with you all if it is possible to create a script which generates the list of the schedules of all the jobs and then analyses it to rearrange the job starttime to ensure that we have equal gap between two jobs starting time. Just to ensure we donot have many jobs running at the same time.
View 1 Replies View RelatedA Simple TSQL To Find The Last N RUN Status Of The SQL Jobs / Job History
Feb 22, 2008Replace the <<job name>> with the actual job name
---Last n RUN status of the job------
SELECT TOP 20
SJ.name 'JOB Name'
,'Run date : ' +
REPLACE(CONVERT(varchar,convert(datetime,convert(varchar,run_date)),102),'.','-')+' '+
SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),5,2) 'Start Date Time'
,SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_duration),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_duration),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_duration),6),5,2) 'Duration'
,CASE run_status WHEN 1 THEN '1-SUCCESS' WHEN 0 THEN '0-FAILED' ELSE CONVERT(varchar,run_status) END AS 'Status'
,Step_id
,[Message]
,[Server]
FROM MSDB..SysJobHistory SJH
RIGHT JOIN MSDB..SysJobs SJ
ON SJ.Job_Id = SJH.job_id
WHERE SJ.name LIKE '%<<job name>>%'
AND Step_ID = 0 --Comments this line if you want to see the status of each step of the job
ORDER BY run_date DESC, run_time DESC, step_ID DESC
---Last n RUN status of the job---------
Query Find A List Of All The Jobs In A SQL 2005 Database
Apr 22, 2008
Hi,
I was wondering if there was a SQL query I could run that would list all the current jobs in a SQL 2005 database? I would like to check to ensure a job exists before I call it, but was having trouble finding a way to determine if it existed.
Thanks for any advice!
-Flea#
DB Engine :: Query To List Out Late Running Jobs
Nov 11, 2015Any sql script or powershell script which outputs late running jobs? Currently I am using the below script to find out currently running jobs along with duration. But my requirement is to add few more columns to the query which indicates whether jobs is running fine or running behind expected time.
-currently using query to pull running jobs
SELECT
   ja.job_id,
   j.name AS job_name,
   ja.start_execution_date,    Â
   ISNULL(last_executed_step_id,0)+1 AS currently_executing_step_id,
  Â
[code]...
SQL Jobs Calling SSIS Packages Within MSDB Not Referencing Connection Info In Config Files
Nov 13, 2007I have deployed to production a number of nested packages (parent packages that call child packages) to the SQL msdb via the Save As option rather than building a deployment utility. These packages reference configuration files in a static location off of the c: drive on the production server. In the development environment, when connection changes are made and I run the Reload with Upgrade option the connection manager takes on the new server and user id settings. However, out on the production side I get the following error from the SQL job log:
Cannot load the XML configuration file. The XML configuration file may be malformed or not valid.
As a result the SQL job uses the default connection information which references the development database rather than the production database. I did research the error but found no good solutions. Is there a way to ensure the configuration files are formed correctly and that the packages are correctly referencing the configuration files? We are trying to run the ETL updates via a SQL job.
Any suggestions? Thanks.
T
Scripting Overnight Backup Jobs For SQL Server Databases
Mar 21, 2008Hi, I've just been given the task of finding out how to implement a backup procedure for our SQL server databases. Most are running 2000, some 2005.
I'm a programmer, and I'm used to having a DBA to help me! I've seen a few methods on the web involving a stored procedure and running the task from task manager.
I need to backup and restore all the databases in SQL Server 2000 and work out a way of displaying whether or not it was successful.
Can anybody please point me in the right direction as I've no idea how to do any of this really. I guess if I could setup a sproc to loop through the databases that would help, but I'm not sure where to start.
Thanks in advance.
Error: Could Not Get A List Of SSIS Packages From The Project.
Apr 26, 2007Hello,
I have a SSIS package and I am trying to build it using devenv.com from command line. It gives me this error:
Error: Could not get a list of SSIS packages from the project.
However if I open the project in Visual Studio it builds properly. In project property I have set CreateDeploymentUtility=true.
This is what I do from command line:
devenv.com MySSIS.sln /Build "Development|Default"
Any idea what I should do ?
Thanks
Deval
List Of All SSIS Packages Stored In MSDB
Mar 14, 2006Hi,
I am writing one program in vb.Net where I need to bind a treeview with all the SSIS packages stored in SQL server MSDB database.
Is it possible to get all the SSIS packages by using T-SQL query or Object model ?
Thanks
How To Copy Databases And Dts Packages?
May 16, 2007Hi All,
I am new to sql server and the database concepts and just started learning.
I want to copy a database and local package from a old sql server to a new server.
can anybody guide me with the steps? I am very new to this field so if you could give me detailed steps
i would very much appreciate it.
thanks in advance,
To List Ssis Packages Stored In File Folder
Nov 13, 2007Hi,
newbie to sql2005. I am trying to write a VB.NET program to list all ssis packages we have stored in a file folder (not in MSDB) where both SqlServer and ssis servers are located. These packages currently running on daily basis by automated jobs with no problem.
I have found the following link on the web that includes VB.Net program. If you scroll down under Example (SSIS Package Store) , you'll see a VB.NET program that I am trying to get it to work for me but keep getting error message like "Cannot find folder......"
http://msdn2.microsoft.com/en-us/library/ms403343.aspx
My goal is to:
1. List all ssis packages stored in the file systems (.dstx)
2. List DB source and distination providers(tables in/out) in a ssis package
3. List properties for DB providers
4. List FlatFile & Excel file provider properties in a ssis package.
My assumption, but not sure, is that I may need to register this file folder (where packages are located) to the SqlServer if that is not done already. Any command or sys table in Sql2005 to figure this out?
I would appreciate any help on this in advance.
Thank You.
Backup All Databases(SQL And AS) And SSIS Packages
Jun 11, 2007Hi All,
I have a requirement of migrating all databases from one server to another. I need to backup all SQL Server 2005 databases (both SQL and AS) with the exact replica copy. Also I need to migrate all the SSIS packages. Unfortunately I am not a Database/System Admin.
Is there any Script/Tool/Wizard Functionality that would help me doing this? I would be happy, even if you can help me reducing the parts of manual work in anyway you can think of, so please pour you thoughts in.
Thanking in Anticipation,
Jasraja
For Each Across List Of Databases
Oct 20, 2006I need to get records from multiple databases.
In my main database, I have a list of databases related to seperate business units.
For each of those databases, I need to get a list of values from a table (the table exists in each database).
Basically
foreach database in a list
Do a Lookup
end
Possible?
List Of Databases
Aug 21, 2006hello anybody now
i need to find how many Databases in my local SQL server (programatically).i know i have 8 databases is there but i need to print each and every database.
can anybody help me.
thanx
kiran.
Query A List Of Databases
Oct 23, 2006anyone know how to query a list of databases?
current the sp_helpdb seems cant help me, because it query all the details, i want to do it like select * from database where database id=2
List Databases With Their Size
Feb 12, 2007I want to list my databases, and physical related files, and their size but the following commands do not provide the size. Any idea?
SELECT * FROM sysdatabases
SELECT * FROM sys.sysfiles
I know it's silly question
Canada DBA
List Attached Databases?
Mar 12, 2007Is there a stored procedure to list attached databases?
You can do this:
use Master
exec sp_helpfile
go
Which shows you the config files for the current DB.
What if you want to do a multiple sp_detach_db? If you could get a list of the attached DB's you could recursilvely or conditioinaly detach.
List Of Attached Databases
Feb 7, 2006How can I, programatically, get a list of attached databases? I'm using VB.
View 4 Replies View RelatedGetting List Of Databases From Server
Sep 25, 2006
Is there a way I can query the database server (SQL 2000) to get a list of all the available databases? As a threshold problem I am not clear how I would connect to the server. I am using VB.Net and presently connect to one database at a time. The connection string is in an XML file, such as:
<?xml version="1.0" encoding="utf-8" ?>
<Connection>workstation id=ASUS;packet size=4096;user id=sa;data source="5.15.183.222KWMSDE2"; persist security info=True;initial catalog=tsLocal;password=pass
</Connection>
Is there some special way that you connect to the server without specifying the database?
Is there a certain query I must use once I am connected? Can the information be returned in the form of a data table in a data set?
Thanks.
Is There A Simple Way To Upload Databases To SQLServer2005?
Mar 4, 2006I host more than 40 websites on my server, and I am starting a migration from another hosting company where I used SQLServer2000. I have brought all the databases onto my Dev server, which last night I upgraded to SQLServer2005. Now I'm wanting to upload all those databases (which total about 350 tables in 35 databases) to the new server
In the old SQL2000 I just made a DTS package which was a pretty simple task once I'd figured out what to do. I'm guessing the equivalent process is pretty simple too in SQL2005 but I'm stumped as to how I go about it. Uploading databases to the server is a pretty regular thing for me and I'm going to have hosting clients who wont want to spend a heap of dosh on getting the full blown SQLServer2005 just to upload their data to the server.
What's the trick I'm missing? So far I've spent more than 9 hours and haven't been able to upload a single table.
I see that SSIS is the place to look, (i think). but there are no examples for uploading a table from one SQLServer to another, overwriting the data that's there, which is telling me that perhaps SSIS isnt the place I should be looking for how to do this. And in any case it's a pretty laborious process writing that whole project every time I want to upload a new copy of the database. (or conversely download the onsite database to my local dev machine)
Can anyone tell me (or direct me to a resource somewhere) that tells me how I should be doing this common task?
The task I'm trying to work is to upload a database from a dev server to a production server where the database is already created and registered with the web server as a datasource. So I need to create the tables, indexes, dependencies, foreign keys, storedprocs etc on the production server, dropping any existing ones that might already be there, then populating them with the latest data from the dev machine.
In the future I'm going to need to do the reverse, bring the whole shebang down from the production server to the dev machine to be worked on and tested etc then reloaded back to the production machine as modified.
How Do I Grab A Simple List Of SQL Server Views, Tables Or Sprocs?
Jul 20, 2005I need just the names of tables, views and sprocs within a SQL Serverdatabase. What's the easiest way to do this?
View 3 Replies View RelatedWRITELOG Lock On Databases Set To SIMPLE Recovery
Jul 23, 2005Hi All,I have a 2MB database set to SIMPLE recovery. This database is usedonly to generate new keys to web users. It has two columns - UID andLASTDATE. The UID column is only updated when users log in or log out(INSERT & DELETE). The LASTDATE column is updated every time theyswitch between pages.Please don't ask for the reason why we do it this way, it just IS.The maximum amount of rows in this table at any given time is about2000. There is a non-clustered index on the UID column.My problem is that all throughout the day I have constant blocks by theWRITELOG waittype. I know my disk I/O is very slow (will be upgradedsoon) but this is the only database on which these blocks occur. Whatis writing to the transaction log if I have the DB set for SIMPLErecovery?Thanks,Josh
View 4 Replies View RelatedNew Databases Defaulting To The Simple Recovery Model
Feb 8, 2008When I create a database, is there a way to make sure that the default recovery model is Full instead of Simple?
View 1 Replies View RelatedList Of All Table Sizes In A Databases
Mar 6, 2000Hi,
Is there a query I can run to retrieve a list of all tables and their sizes in a database? I want something that is like the feature in Enterprise Manager when you click on a database and then the 'Tables & Index' link. It lists the tables and their respective size. I want to push this into a spread sheet.
The reason why I am doing this is the compare data between 2 different databases. Since I cannot find a tool that will compare the data, the closest I can get (without bcp-ing out all data and comparing) is to look at the sizes of each table.
Thanks!
Joyce
Browse Through A List Of Databases On A Given Server
Nov 11, 2004Hi,
I would like to allow the user of my ADO.NET application to browse through a list of the available databases on a given server. INFORMATION_SCHEMA in SQL Server Books Online doesn't describe how to do this. How can I do this?
TIA,
Royce