We have a really annoying job here that relies on a particular file to be created before several imports run. An old file may already exist, but if it isn't recent, we don't want the import to run. This job can't delete it, since other jobs use that file. What we'd like to do is to be able to check the creation date of the file, and if it is after a certain time of day, run the import, else, delete the file.
I know of xp_fileexists. Is there anything similar in SQL that can return file information or am I stuck parsing the output from xp_cmdshell 'dir F:ftpcoreinputready.txt'?
Any help or hints are appreciated. Let me know if you need more info. Thanks.
Hi all in my project, I need to access SQL job to finish something. But sometimes, the status of SQL Agent is not running, which needs me to check the status first. I am wonder are there some functions or some ways to check the status. If you know, please response me. I appreciate your response !
Is it possible to create an SSIS package that checks for a running Query on my SQL db? I need to some how check my SQL server and see if there is a query running, if its running I need to set an indicator in my table for my app. This job needs to be scheduled and run nightly (which I can do). But how can I query SQL and see if the query is still running?
So far, I only deal with Import & Export data from one server to another.
Earlier today, I initiated a table import, from SQL Server AAA to SQL Server BBB.
I can access Windows Server BBB (and SQL Server BBB) from Computer CCC through Remote Desktop.
My question: Is there any way to check if the import was successful or not, from another computer? Is there a log that I can see? I open the Management>>SQL Server Log, but the import/export is not recorded.
I can see the new table that I imported on Server BBB, but not sure if the import was successful or stopped in the middle since I initiated the import from Computer/Server AAA (I don't have access to Server AAA at this moment). I guess, I need to see the time when it's all done/completely imported. I checked the table properties, but it listed the time of creation. I don't see the time of transfer completed.
I have a full text index created on a table with PK, text column and timestamp column. The table has 10 million rows. I tried one time full population and CPU spiked so after couple of hours i stopped full population.
Now since i have a timestamp column in the table I want to do a incremental population.
But when I run a select
SELECT * FROM sys.fulltext_indexes
The incremental_timestamp column is showing value 0x0000000000000000
How do I find how long will it take for incremental population to complete?
i have 70 SQL database servers and i setup DB Mail on the 70 Servers, i want to know is there a way to find the status of all the jobs which i assigned the DB Mail and if its working/failing... is there a script i can run on powershell or SQL to find out that information
I have a field 'Rowguid' of type uniqueidentifier in a table. This field is the last field in the table. In this case if I update a record through the application I don't get any error. Suppose if there are additional fields after the field Rowguid I get the error "Multiple-Step operation cannot be generated Check each status value"
For your reference I have used the following statement to add the RowGuid field
Alter table <tablename> Add RowGuid uniqueidentifier ROWGUIDCOL NOT NULL Default (newid())
How do I programmatically check a row group's Visibility or Expand/Collapse flag in a matrix table? For example, I have a matrix table contains the following groups:
Row groups: Facility --> Category Type --> Category Column groups --> year, quarter, month
I want to be able to programmatically update the table content if Category rows are not visible (Category Type row group is collapsed).
Hi all, We have Windows 2003 64 sp2 Xeon, 2005 EE SP2 64 bit... Trying to do conversion from DTS sql 2000..One package use load from excel to sql..So I tried to create same thing by myself.. Hell, so many issues.. So I used wizard, package created, I changed Run64bit to False, tried to run package, once - completed in debug mode.. Now it's time to create deployment utility and deploy package..During execution of manifest file got error:
Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
In BIDS, open up solution and tried to rerun package again - no way,".. cannot acquire connection from connection manager blah blah blah.." Even tried to fire package without debugging, it fires 32 bid execution utility, so no question about 64 bit mode.. package failed.. Execution GUID: {CE11CF95-A25E-4285-A8B0-9E28E51A6785} Message: ExternalRequest_post: 'IDataInitialize::GetDataSource failed'. The external request has completed. Start Time: 2007-11-09 09:41:25 End Time: 2007-11-09 09:41:25 End Log Error: 2007-11-09 09:41:25.95 Code: 0xC0202009 Source: Package_name loader Connection manager "SourceConnectionExcel" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" H result: 0x80040E21 Description: "Multiple-step OLE DB operation generated error s. Check each OLE DB status value, if available. No work was done.". End Error Log: Name: OnError
Source Name: Data Flow Task Source GUID: {2A373E56-8AAF-40E9-B9EF-4B2BB40175F0} Execution GUID: {CE11CF95-A25E-4285-A8B0-9E28E51A6785} Message: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER . The AcquireConnection method call to the connection manager "SourceConnection Excel" failed with error code 0xC0202009. There may be error messages posted be fore this with more information on why the AcquireConnection method call failed.
I am using ATL COM library application. It is using sql data base for fetching the records. Some times, i get the following error. could you please let me know, why this happens? This is not reproduceble every time.
(Error! hr=80040e21, hrDesc=Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work
Here is the code to connect to database which i am using.
Our database is running in full recovery mode and we backup the logfile every hour and the database file every night. I notice thetimestamp of database file *.mdf remains unchanged a few days. Forexample one database has its timestamp at Apr 15 while it is Apr 20already.I am wondering if this is acceptable. How do I force the system toflush the data and dirty blocks to the disk more frequently? What isthe downside to this?Thanks for any idea.
now this bat file only start the job, does not wait for it completion or send us the success or failure message.Now i want to check the value of LastRunStatus for this job using BAT file?? Is is possible to fetch the LastRunStatus of sql job using bat file.However i have achieved the same using vbscript that starts a job using vbscript start method and then looping through al the jobs at server and displaying the LastRunStatus. However i wanted to achieve this uisng bat file only.
I looked in what appeared to be pertinent threads, but found no satisfaction (I realize you can't always get what you want, but if I try this time, I've found that I can't even seem to get what I need ??? ).
OK, seriously folks...
I have a batch job on windows NT that fires off a series of bcp operations to export data from tables to flat files as a part of daily processing.
If, for some reason, the bcp fails, is there a way to catch the failure in the batch file? Presently we are seeing the batch file attempt a bcp, which fails (due to a general network error, or something fun like that) and then go skipping happily along to the next bcp without telling us of the failure.
i'm trying to write this script that check my database file and log size(in MB) and insert them into a table.i need the following columns dbid,dbname,compatability_level,recovery_model,db_size_in_MB,log_size_in_MB. i try to write this a got stuck. select sysdb.database_id,sysdb.name,sysdb.compatibility_level, sysdb.recovery_model_desc,sysmaster.size from sys.databases sysdb,sys.master_files sysmaster where sysdb.database_id = sysmaster.database_id
I need to set up create a package so that I could check the date of the files posted in a folder, e.g. H:source. If there is no file created later than one day exists, then continue to check again one hour later. If files do exists, then copy then to c:dest and then upzip the files. Once this is done, sent an notification email to user@mydomain.com.
I need to set up a package to copy a file from a network share to a server. Before the copy job, I need to check whether the file, e.g. test.txt, is created within one day, if not, then check again an hour later.
1)My source files[.txt] come from an FTP server.I need to find a way to include within my SSIS package,a task that would watch a certain folder of the FTP Server and send a mail notification as soon as the file arrives.Tried using filewatcher task http://www.sqlis.com/23.aspx, but the task stays "yellow" for a long time while package is being excuted.I dont know if this is what I need to use.Are they any other task/ways?
Another Problem I'm facing is;
2)If the source file coming in the FTP Server is zipped.How do I unzip the file and move it back to the FTP Server folder for the task in 1) to watch on it .Any ideas ?
In SSIS, I need an easy way to see if a file exists, and if not wait for it until a timeout period expires. Here are the options I've discovered, along with the issues I've had:
a) The File Watcher task from www.sqlis.com
This was my first attempt. The task works great, BUT only detects when there is a change on the file. If the file already exists, it keeps waiting which is not the behavior I need. b) The WMI Event Task
There is very sparce documentation on this event and how to write a WQL query. There are numerous examples of monitoring a folder and if any files appear, cause an event to happen. I need to detect for a specific file. I found maybe one example of this using "PartComponent" but wasn't able to get the sytax right to make it work for me. I also need to access a remote file share using a UNC path (e.g. \servernamepathfile.txt) which I could not get to work. c) Script Task using the File.Exists() method
I imported the System.IO namespace, and used a File.Exists(\servernamepathfile.txt) with actual success, but am not sure of the best way to continue to wait if the file is not found immediately. I also want to modularize this approach so I can wait for several files simultaneously so was thinking of implementing this script task as a package by itself to accept variables (filepath & timeout period) but need to know if anyone has had success with this approach. I'm open to suggestions or ways to get options a) and b) to work for my needs. Thanks! Kory
Hi, I wrote a VB code to generate a xls file. Users are able to run it fine but if they have another file with same name already open, then it just crashes excel. So I want to include a code that checks if file "file.xls" is open on user's machine. If file is open, then message "file "File.xls" is already open. Generating File_1.xls" Run the code but create the file with file name "file_1.xls" If file doesn't exist, then run code and create file with file name "File.xls"
So basically I want the code to run and generate the file. Only difference is that if file with same name is already open, then just rename the newly created file.
Here's the code I've created for generating the file:
Public Function getrmpricing() Dim queryoption As String Dim ans, Msg As String Dim fs As Object Dim sTemplateFile As String Dim e_TemplateFile As String On Error Resume Next
First I want to thank everyone that has given help to me and everyone else with the issues involving migrating to 2005... Thanks alot..
Now for the problem. I am looking for (an not finding anything of help) to check the date of a file on an ftp server. A file always exists but once a month the day changes. I would just download the file and check it locally but the files are several hundred megs in size so that would be inefficient.
So is there anyway to do that??
On another note, can anyone point me to a good resource for learning the scripting language that SSIS uses??
yes,I have an error, like 'The database file may be corrupted. Run the repair utility to check the database file. [ Database name = SDMMC Storage Cardwinpos_2005WINPOS2005.sdf ]' .I develope a program for Pocket Pcs and this program's database sometimes corrupt.what can i do?please help me
Everyday they are sending to our servers a file named like CC_20080501 . it is CC_YYYYMMDD So I have to check it and move into the database table. I am thinking to create standard database connection and name like CC_std And When the file arrives to create something like CC_std (actually copying my daily file CC_YYYYMMDD to CC_std ? BUT how can I do it? any other way to do it?
