We run a weekly update on one of our databases from a download (FTP) from our mainframe. What I want to do is start the update job automatically after all of the files are downloaded.
I have a similar process in another database where the users set a flag in an application after they've made certain changes. The job fires daily at 18:00 and checks the value of the flag in the table. It then processes or goes back to sleep. This works great but there is no flag that the users can update in this application.
Is possibile configure as expression the properties XMLData and XMLdataDefinition of a "XML Source"? looking the editors it seems it is not possibile... I think it is a great limit of the component because I must hardcode the path!
Regards,
M.rkino
-- Marco Barzaghi - [MVP - MCP] http://mvp.support.microsoft.com - http://italy.mvps.org UGIDotNet - User Group Italiano .NET, http://www.ugidotnet.org Read my WebLog: http://blogs.ugidotnet.org/markino
Hi all,I am having real problems allowing a client to connect to sql 2000through enterprise manager.. I have tried all sorts but to no avail:From our local network all works fine.. Using the adsl line.. Even ifi try and connect to the external IP and use login details..(Asopposed to connecting local and using windows accounts) I can ping andtelnet the server and port.The server is behind a firewall so i opened up port 1433 and port1434(? is it needed).. To replicate the client i took the same laptophome that i use to connect in the office.. However using the exactsame config on my dial up at home i can ping the server but cannottelnet onto port 1433.. I think that this is the vital missing stepbut do not know hpw to resolve it..Any ideas anyoneCheersAnt
I have an odd one. I have a SQL job that doesn't have a schedule and is being run each morning. It is a legacy system and I am trying to document the data flow process and I am having a hard time tracking down where/what is starting the job. I see which user executed the job:
SELECT message FROM sysjobhistory WHERE job_id = 'jobid' AND run_date > 'yesterday'
Which is useful, but I want to know what is starting the job.
How can I move data from an external source to SQL-server 6.5.
If you for example has an oracle database on a UNIX or VMS-system and want to move table data from a table in the oracle database to the SQL-server database.
There must be plenty of tools to do that via ODBC.
I'm looking to create a table in SQL using data from the as/400. I need some code that extracts it from as/400 using odbc and I need it to loop and create that table every minute. The file its retrieving is a live file but not very large.. I need to basically poll the data. Does anyone have code for something like this.. thanks
I have a knowledge of SISS and CRM 3.0 but don't get any proper steps to create such Integration Service in SQL Server 2005 using CRM 3.0 as external data source.
I don't get any option of external data source for CRM.
So if anybody know about this then let me know steps to do that.
I have a report which lists out the employees' details. I need A-Z links above the report, On clicking on an alphabet, say "C", should display all the employee details whose name starts with the selected alphabet. In the stored procedure we can accept the character and return back those results. But it is not a drill report and we need the result in the same report. Is there any way so that on clicking each link, the output will be shown in the same report. Any help is appreciated.
I am trying to create a CLR based stored procedure in C#. When i tried printing simple "Hello" from it, it works fine. Now requirement is to run an exe file from it. For that i use process.start. But when i try to execute the procedure i get all the security execptions. Can someone please help. Following is the code snippet. -------------------------------------------------------------------------------------------------------------------------------------------------------------
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void RunProc(string arg)
{
SqlPipe pipe = SqlContext.Pipe;
pipe.Send("Hello");
Process.Start("E: est.exe");
}
}
CREATE ASSEMBLY [RunProcess]
FROM 'RunProcess.dll'
CREATE PROCEDURE dbo.sqlclr_RunProc
(
@arg nvarchar(1024)
)
AS EXTERNAL NAME [RunProcess].[StoredProcedures].[RunProc]
I have an XML file that my XML Source component is accessing. I have noticed that is possible to set a column in the external metadata collection to a certain datatype and the matching output column to a different datatype and this doesn't not generate a warning like it does with other source components (e.g. Flat File Source Adapter).
Try it. Set a column in your external metadata to have a datatype of DT_WSTR. Set the matching output column to DT_UI8. You will NOT get a validation error. I think you should.
This behaviour was noticed on RTM (i.e. no service pack installed) by the way.
Hopefully someone can help me with what I'm sure is a very simple question (new to the XML thing). I receive an XML file from "someplace" that I need to parse out using the XML Source in SSIS. I have SSIS generate me an XSD document, as one isn't provided for me. However, after I do this, SSIS does not show any available external columns to pull data from- the "Columns" section of the source is just blank. I'm pretty sure this has to do with a syntax error in either the XML file that is being provided to me, or the XSD doc that SSIS is generating. Below are both (obviously with data dummied up). Can someone take a look and let me what needs to be changed in either file to get this up and running? I'm looking to grab the AccountNumber, RecordNumber, ProcessedDate, Status, and StatusMessage elements.
I have an application that is set up using Service Broker to pass messages between services asynchronously. I am using event-based external activation and have successfully set up my event notification in SQL Server so if a message appears on any of my Service Broker queues, I'm getting the activation event from SQL Server sent to my activation service.
The problem that I am seeing is that every time I am posting a message onto a Service Broker queue, I am losing the event notification entry in the sys.event_notifications view and I'm not receiving my activation event notifications. When I execute the CREATE EVENT NOTIFICATION T-SQL statement to recreate the event notification, I'm getting the event notification immediately (since there are messages on the queues being monitored). The event notification appears to be registered until the next message is posted on the queue.
I have a SSRS 2012 report which references a custom c# assembly. This report exists in multiple environments (alpha, beta and production ) which are each associated with different data sources.
Is there a way for the assembly to determine the datasource used by the calling report so it can also connect to it?
I have a SSIS package with a Data Flow task. This task transfers the data from SQL Server 2000 to a table in SQL Server 2005.
I deployed and tested this package on the Test Server. Then put this package in a job and executed it - Works fine.
On the production server- If I execute the package through DTEXECUI, it works fine. But when I try executing it through a job- the job fails and it gives me following error:
Description: The external metadata column collection is out of synchronization with the data source columns. The "external metadata column "T_FieldName" (82)" needs to be removed from the external metadata column collection....
What I don't understand is, why are there no errors displayed when I execute the package through DTEXECUI.
Hi, Is it possible to do an event based scheduling of SSIS packages from SQL Server Agent? Like schedule a package to run when a file is available in a particular folder. Does anyone have any similar experiences to share?
I have tried my best and not able to locate the source of this error.
The error logged in Event Viewer shows the source is from MSSQLServerOLAPService, but it is impacting the Reporting Services reports that use the Analysis Services as a data source to hang frequently.
Have anyone else encounter this and know what the Category 256 Event ID 22 means?
I would like to generate a working schedule for employees for x-days ahead based on a starting date that the user can enter.
I have got 3 relevant tables:
1. Table X with (1) resourcenumber, (2) starting date working schedule and (3) the daynumber representing the starting date (this is ISO so 1 for Monday, 2 for Tuesday etc.)
2. Table Y has the schedule itself and can hold a 7-days schedule or a 14-days schedule. In case of 7 days schedule there a 14 (!) records with (1) resourcenumber, (2) daynumber, (3) starting hour a.m. (4) ending hour a.m (5) starting hour p.m and (6) ending hour p.m. In case of a 14-days schedule there are 28 records (a.m. and p.m. records)
3. Table Z with resource data.
An example to clarify (for fake employee 100):
Table X: Resource: 100 Starting date: 2012-03-01 (from this date the schedule will be effective) Daynumber: 4 (2012-03-01 was a Thursday)
Table Y (Resource has a 14 days schedule because per 2 weeks Monday is an off-day):
Record 1 shows: Resource: 100, Daynumber: 1 (= Monday, working day), AM-Starting hour: 09:00, AM-Ending hour: 13:00, PM-starting hour: 13:30, PM-ending hour: 17:30 Record 2: same but daynumber is 2 Record 3: same but daynumber is 3 etc. ... Record 8 shows: Resource: 100, Daynumber: 8 (= Monday, off-day), AM-Starting hour: 00:00, AM-Ending hour: 00:00, PM-starting hour: 00:00, PM-ending hour: 00:00 Record 9: same as record 2 but daynumber is 9. etc. ... Record 14: same as record 7 but day is 14 (= last day)
The weekend days show as 00:00 for the hours (same as day 8 in example)
I generated the working schedule with a CROSS APPLY function based on the starting date and the x-number of days ahead.
I then evaluate the actual daynumber corresponding with that date with the daynumber in table Y. That works fine with a 7-days schedule but I can't get it fixed with a 14-days schedule. Day 8 in that schedule represents an actual day 1 but how do I know what actual date day 8 is ... I think I have to start with the starting date in table X ...
I think ideally I would like to have the generated days as follows (as an example in case of a 14-days schedule starting 2014-05-01 for 30 days ahead):
2014-05-01 = day 4 (= actual daynumber) 2014-05-02 = day 5 2014-05-03 = day 6 ... 2014-05-10 = day 13 2014-05-11 = day 14 2014-05-12 = day 1 2014-05-13 = day 2 2014-05-14 = day 3 ... 2014-05-24 = day 13 2014-05-25 = day 14 2014-05-26 = day 1 2014-05-27 = day 2 ... 2014-05-31 = day 6
With this done I can compare the actual daynumber with the daynumber in Table Y.
The rownumber that the CROSS APPLY function generates has to be reset to 1 after day 14. I tried PARTITION BY in THE ROW_NUMBER function but to no avail ... The only field I can partition by is the maximum value of the daynumber (14 is the example) but that is not allowed in the rownumber function.
I am new to SSIS programming, so bear with me if my question seems naive to you gurus. I have a situation that needs to set the data source for a data flow from external .NET application ('external' means that the application will run on different process than the SSIS). I am trying to set the data source on which the data flow works from my C# application in a DataSet format. Ideal solution is not to save the DataSet to any file on harddisk (I know that will work, but has the overhead of writing, reading and managing the temp file). What I want to achive is that the business logic of picking data for SSIS Data Flow to process is controlled inside my C# application, the Data Flow just does what it does best - Transformation. Have any of you successfully done this before?. Thanks!
Set up reports to use expression-based data sources and everything works fine as far as executing and viewing the reports. The ConnectionString expression includes the credentials, and the "No credentials required" option is selected.
Unfortunately, am getting this when trying to create a subscription for same report. WTF?
eportingServicesService!processing!3!12/20/2006-12:20:07:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'XXXData'., ; Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'XXXData'. ---> System.InvalidOperationException: The ConnectionString property has not been initialized. at System.Data.SqlClient.SqlConnection.PermissionDemand() at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper.Open() at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ReportRuntimeDataSourceNode.OpenConnection(DataSource dataSourceObj, ReportProcessingContext pc) --- End of inner exception stack trace ---
When our rep distribution services time-out (several times a day) some dba's just restart the service while others restart the rep job which starts the service. Are there any differences between these two methods? Does restarting the service inherit any changes made to the job's attributes?
I'm trying to load data from old SQL server 2000 to new SQL server 2014. I need to do a checksum to check if all the source data is loaded in the target database(SQL server 2014). I've created the insert statement for the same which works. I need to use checksum to make sure all the source rows are loaded in the target table. I haven't done checksum before.
Here is my insert statement:
INSERT INTO [Test].[dbo].[Order_tab] ([rec_id] ,[date_loaded] ,[Name1] ,[Name2] ,[Address1] ,[Address2]
I am running dts in Sql Server 2005 management studio from Management, Legacy and data Transformation Services.
Once the dts has run, I get this error message "Error Source : Microsoft Data Transformation Services (DTS) Package Error Description : Error accessing Windows Event Log."
We have SQL 2000 (SP2) , Our SQL server agent showing green light as started from EM , It is succussfully restarting from NT Services too . But When I open the properties of the jobs from EM its gives error message SQL DMO error " SQl Agent is starting , try later " . And in EM Next run Date & Time not available .
MDDB database is accessable & in Log file its recovered fully
Is it possible to include a currency symbol in an amount-field in PowerPivot/Pivottable based on a Currency column in a table? Something as the same as with SSAS MD. And I don't want fixed values in my code.
I have implemented a package to load multiple files to a destination. Since the source was a txt file, i have created as flat file source. However now we are getting files in excel format as well.
Is there anyway the source gets changed dynamically based on the file extension, output of the foreach file enumerator? I can think one solution to have 2 dataflow tasks based on precedence constraining and expression one is for .txt and other one is for .xls.
My SQL Server 2005 SP4 on Windows 2008 R2 is flooded with the below errors:-
Date 10/25/2011 10:55:46 AM Log SQL Server (Current - 10/25/2011 10:55:00 AM) Source spid Message Event Tracing for Windows failed to send an event. Send failures with the same error code may not be reported in the future. Error ID: 0, Event class ID: 54, Cause: (null).
Is there a way I can trace it how it is coming? When I check input buffer for these ids, it looks like it is tracing everything. All the general application DMLs are coming in these spids.
I have been testing with the WMI Event Watcher Task, so that I can identify a change to a file. The WQL is thus:
SELECT * FROM __InstanceModificationEvent within 30 WHERE targetinstance isa 'CIM_DataFile' AND targetinstance.name = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak'
This polls every 30 secs and in the SSIS Event (ActionAtEvent in the WMI Task is set to fire the SSIS Event) I have a simple script task that runs a message box).
My understanding is that the event polls every 30 s and if there is a change on the AdventureWorks.bak file then the event is triggered and the script task will run producing the message. However, when I run the package the message is occurring every 30s, meaning the event is continually firing even though there has been NO change to the AdventureWorks.bak file.
Am I correct in my understanding of how this should work and if so why is the event firing when it should not ?
Server 2003 SE SP1 5.2.3790 Sql Server 2000, SP 4, 8.00.2187 (latest hotfix rollup) We fixed one issue, but it brought up another. the fix we applied stopped the ServicesActive access failure, but now we have a failure on MSSEARCH. The users this is affecting do NOT have admin rights on the machine, they are SQL developers. We were having
Event Type: Failure Audit Event Source: Security Event Category: Object AccessEvent ID: 560 Date: 5/23/2007 Time: 6:27:15 AM User: domainuser Computer: MACHINENAME Description: Object Open: Object Server: SC Manager Object Type: SC_MANAGER OBJECT Object Name: ServicesActive Handle ID: - Operation ID: {0,1623975729} Process ID: 840 Image File Name: C:WINDOWSsystem32services.exe Primary User Name: MACHINE$ Primary Domain: Domain Primary Logon ID: (0x0,0x3E7) Client User Name: User Client Domain: Domain Client Logon ID: (0x0,0x6097C608) Accesses: READ_CONTROL Connect to service controller Enumerate services Query service database lock state
