DTS - Dataimport On A Daily Basis

Apr 3, 2000

I have to import data into 2 tables on a daily basis.
The data is provided as a flatfile.
In order to fullfill this task the tables have to be truncated first.
Are there any possibilities to do this job automatically with dts, or do I
have to write an Interface
in VB or something like that?

Thanx

Michael F.

info@sunguard-explorers.de

View 2 Replies


ADVERTISEMENT

How Xml Could Help Us With Our ETL Processes On Daily-basis?

May 26, 2006

Hi all of you,

I'm just a newbie with XML. Now we're moving all our DTS to SSIS packages. Most of them are simply processes that takes a plain file and loading these data into Sql table or in inverse way. So that I wonder what role will play XML here.

I mean, are there big differences between store data as XML format and nvarchar?

That sort of stuff.

Thanks in advance for any input,

View 5 Replies View Related

SQL 2005 Sps Need To Be Recompiled On A Daily Basis Why?

Aug 16, 2006

Hi All,

I have recently upgraded one of our servers using the in place upgrade method to SQL Server 2005 from SQL Server 2000.

We are currently having an issue with stored procedures as they appear to be running slowly and the only way to correct this issue is to recompile them on a daily basis.

Has any one else had a similar issue, any ideas what could be causing this to occur.

Thanks.

View 1 Replies View Related

Scheduling DB Restore On Daily Basis

Sep 27, 2013

I need to be able to restore a test db from a production db on a daily basis..

View 3 Replies View Related

Compound Return On A Rolling Daily Basis

Nov 2, 2014

Looking to create a query, as simple as possible, that allows me to compound returns on a rolling daily basis. So far this this have I have:

DECLARE @stock_returns TABLE
(
stock_code VARCHAR(10) NOT NULL,
date1 DATE NOT NULL,
daily_return NUMERIC(10, 2) NOT NULL
);

[Code] ....

But I´m not getting what I need. If you run the above select, the output should be:

stock_codedate1daily_returnLAGCompound_return
stock12014-07-080.00510 0.00000 0.0051000000
stock12014-07-090.00300 0.00510 0.0081153000
stock12014-07-100.00500 0.00300 0.0080150000
stock12014-07-110.00600 0.00500 0.0110300000
stock12014-07-120.00200 0.00600 0.0080120000
stock12014-07-130.00700 0.00200 0.0090140000
stock12014-07-140.00240 0.00700 0.0094168000
stock12014-07-150.00240 0.00240 0.0048057600
stock12014-07-160.00250 0.00240 0.0049060000

The problem is with this column:

(lag(daily_return, 1, 0) over (order by date1) + 1) * (daily_return + 1) - 1 as Compound_return

The (daily_return + 1) portion should be the accumulated compound return. So it should be something like

(lag(ACCUMULATED_COMPOUND RETURN, 1, 0) over (order by date1) + 1) * (daily_return + 1) - 1 as Compound_return

And the output should be:

Date1Daily returnLAGCompound Return
08/07/20140,00510,00000,0051
09/07/20140,00300,00510,0081
10/07/20140,00500,00300,0132
11/07/20140,00600,00500,0192
12/07/20140,00200,00600,0213
13/07/20140,00700,00200,0284
14/07/20140,00240,00700,0309
15/07/20140,00240,00240,0334
16/07/20140,00250,00240,0359

View 10 Replies View Related

Executing Reports On Daily And Monthly Basis

Sep 21, 2007



Hi All,

I'm creating 15 reports based on one data source.
Once I created, I'm supposed to run this on daily and monthly basis. I'm going to use snapshot option in Report Manager. Is it fine?
Do I need to create a data base too?

Also when I change the target URL address in the report property box, can I deploy it to any other server? Do I have to change the settings of report configuration manager too, inorder to publish reports on another server(not in my local machine)

I really appriciate answers for these questions

Thanks

View 5 Replies View Related

Transact SQL :: Email Notification Of Count Of Pending And Suggested Name On Daily Basis

Oct 23, 2015

I have a query which sends email notification of count of pending name and suggested name.. on a daily basis. So this works fine but now I want like if the pending and suggested are ' o count' I don't want the email notification. It should not send an email, if it is o count, but if we have the number for pending and suggested this email should be sent.

View 8 Replies View Related

Automated Dataimport

Oct 28, 2005

Hi everyone,i'm new to ado.net and need some help. My problem:got one DB with real bad tablestructureand one new DB (structured!)i need do do an automated import from the old to the new DB, with check if the data row is new or updated.I try to do it this way-> 2 datasets merged together  ->Problem: new entries in destination dataset have row state unchangedother way  i tryed  add rows from source to destination with add funktion -> problem no check if data exist and maybe is changedSo how can i get the merge working or how can i implemt an effective funktion to solve this problem. Thanks in advance!

View 5 Replies View Related

Basis SQL Question

Mar 21, 2008

How do I format a date value in SQL 2000 sp?  Tired the following none are returning the quivilent rates.  What is the syntax please?
SELECT *FROM tblCompanyWHERE ( C_LastBuyDate = '03/21/2008')
SELECT *FROM tblCompanyWHERE ( C_LastBuyDate = '2008/03/2008')
SELECT *FROM tblCompanyWHERE ( C_LastBuyDate = '03-21-2008')

View 3 Replies View Related

Update On Basis Of Another Table

Apr 12, 2007

hello guys !
i need a little help in an update statement.

i have two tables as

table1(id1,name)
table2(id2,date,val2)

what i want is to set val2=45 in table2 where date=4/4/2007 (say) and id2, now id is to be obtained from table1 based on the name;

like
table1
---------
(1,"akmal")
(2,"peter")
(3,"kamal")

I know the name as peter, but i want to used its id1 (2) in the update statement. You might have got the idea that id2 is a foreign key referring to id1.

How to do that ?

View 2 Replies View Related

Getting Count On Weekly Basis

Apr 11, 2008

Hi All
I am using following code for my application
SELECT
convert(varchar(10),acr._eventTime,120) [Date],
sum(case r.Severity WHEN 'Warning' THEN 1 end)[Warning],
isnull(sum(case r.Severity WHEN 'OK' THEN 1 end),0)[OK],
FROM ActiveCheckIssueResult r
GROUP BY convert(varchar(10),acr._eventTime,120)


it gives me Result as,

Date warning OK
--------- --------- -----
1/1/1008 1 1
2/1/2008 0 2

i want count on weekly basis
Could any one help me?

View 8 Replies View Related

Job Agent To Schedule For Second Basis ????

Dec 4, 2006

Hello friends!

I want to set my job agent to schedule for second basis but in EM i found by right clicking job in EDIT Recuring Job Schedule i m getting option in Hr and MM but not in SS so where i can set this in seconds (SS) or through QA????


T.I.A

View 2 Replies View Related

Synchronization Performed On Day Basis

Nov 5, 2007

Currently database1 and database2 are planned to be synchronized with the interval of 5 seconds!! ( dont know exact, but i select run continoulsly on subscriber screen )
This could cause serious performance issues as both applications will be live on difference servers.

how can i Synchronization should be performed on day basis or it should be modification basis to avoid any performance issue on live application

i am using merge replication
Local distributor
and subscriber on another server .


plz help me
its urgent

View 3 Replies View Related

Breaking Query On Basis Of Conditions

Mar 20, 2008

 On a webpage, there are filters to choose from. Like date, amount, SSN (multiple filters can be choosen).I have a single query so far.   SqlCommand cmd = new SqlCommand("SELECT [column1], [column2], [column3],  [column4], [column5] FROM [table] WHERE [column4] = 'condition4'  AND [column5] = @total_bill AND [last_change] >= @txtStartDate AND [last_change] <= @txtEndDate  ", Conn) ;          cmd.Parameters.Add(new SqlParameter("@total_bill", total_bill1.Text));         cmd.Parameters.Add(new SqlParameter("@txtStartDate", txtStartDate.Text));           cmd.Parameters.Add(new SqlParameter("@txtEndDate", txtEndDate.Text));  I want to break the query so that it executes on the basis of different sets of conditions (filters). If I dont select date filter, then the above query will not execute properly.Please help. 

View 2 Replies View Related

Select On The Basis On Month And Year

May 18, 2004

hi guys.

I have a datetime column in my SQL server database.. I need to select the value from the table by passing month and year only..

any suggestions..??

Thanks in advance..

:)

View 1 Replies View Related

Capturing A Warning - Ongoing Basis

May 17, 2004

Hi all,

I currently have a series of stored procedures that capture stock prices on a daily basis, then summarize the results into a daily, and further, a weekly summary of the "index" of a group of stocks. The data is accumulated from a (to use a highly technical unit of measurement...) bunch-O-individual rows of data using aggregate functions such as AVG and SUM.

The problem is that I occasionally get a warning on such aggregate statements which is the common one complaining thusly: "Warning: Null value is eliminated by an aggregate or other SET operation"

I know where it comes from, and I know how to code to protect the aggregate from complaining (i.e., AVG(ISNULL(yadayada,0)) ) but I am interested in figuring out a way to REPORT the statement that contains null values. I can, of course, capture ERRORS in selects, but is the same mechanism used to capture these NULL warnings on my aggregate statements? I don't necessarily want to know which individual row is causing it, just want to "tag" somehow the statement that results in the warning so I can go back after the run and check into it (after capturing local "pointer" info at the time the offending aggregate is invoked).

The code I use to capture errors and trace information follows:
UPDATE PortfolioPerformance
SET PrevDate = @PrevDate,
DailyPerChg = GPP.DailyPerChg,
DailySumPriceChg = GPP.DailySumPriceChg,
SumCurrPrice = GPP.SumCurrPrice,
SumPrevPrice = GPP.SumPrevPrice,
StockCount = GPP.StockCnt,
AvgHighPriceRatio = GPP.AvgHighPriceRatio,
AvgLowPriceRatio = GPP.AvgLowPriceRatio,
Volume = GPP.Volume
FROMPortfolioPerformance PP (nolock), VIEW_Get_PortfolioPerformance GPP
WHERE PP.PortfolioID = GPP.PortfolioID AND
(PP.CreateDate = GPP.CreateDate AND
PP.CreateDate = @CreateDate) AND
PP.PrevDate IS NULL

SELECT @RowCount = LTRIM(STR(@@ROWCOUNT)) /* capture rowcount so @m_error select doesn't clobber it*/

SELECT @m_error = @@Error IF @m_error <> 0 GOTO ErrorHandler
SET @TraceMsg = 'Completed Daily Portfolio Performance calculations (updated ' + @RowCount + ' rows)'
EXECUTE [dbo].[tracelog] 1, 'Index', 'sp_Set_PortfolioPerformance', @TraceMsg

NOTE: the aggregation in the above code is performed in the view referenced as "GPP", but that's outside the realm of the question, I think, so I won't bore you with the details of that just yet.

So I think if I can capture the warning like I do the errors, I can accomplish what I want to accomplish. I haven't yet been able to find any guidance in the Books Online, so do any of you have any pointers?

Thanks!
Paul

View 2 Replies View Related

Merging 50 Databases Into One On A Nightly Basis

Sep 13, 2007

How would you solve this problem?
Would you use replication? SSIS? Schedule stored procs? A third party data tool?

We have about 50 databases located at offices around the world. There are several key tables which we need a central copy of at our Head Office.

We need only the new INSERTS or UPDATES for the day to be copied to head office.
The copying process should happen after business hours for that office.

Some cleaning of the data needs to happen before updating head office.

Our database is being installed at new offices every other month, so it's important to us that this be easy to add a new database into the schedule, ideally without needing a SQL DBA or Developer.

We want something robust, performant and easy to troubleshoot.


Kev

View 1 Replies View Related

On What Basis Sql Server Sorts The Rows

May 16, 2007

Hi,



When I execute the following statement:



Select * from table1;



On what basis, SQL server decides the sequence of these rows?



I need to fetch data from a table with very large number of rows. Because of the datasize I need to do this in chunks. I am thinking of passing row counter and fetch N rows at a time. I want to know if there is a need for sorting a table before I apply the above logic or I can rely on default sorting.



Thanks veyr much.



Regards,

Tim

View 5 Replies View Related

Transfer Data In Monthly Or Adhoc Basis

May 7, 2012

I have 10 oracle o/p tables. I have to transfer data in monthly or adhoc basis. Each table will have millions of records. How to transfer Oracle to SQL Server 2005. Which is the best way to transfer the data.

View 2 Replies View Related

SQL Server 2012 :: How To Get Data On Quarterly Basis

May 12, 2014

I need to get results on quarterly basis, matching 2 quarters AUTOMATICALLY.

- As the new quarter starts, it needs to match the last quarter results.

SELECT DATEADD(mm, (QUARTER - 1) * 3, year_date) StartDate,
DATEADD(dd, -1, DATEADD(mm, QUARTER * 3, year_date)) EndDate,
QUARTER QuarterNo

[Code] ....

Here is my Query, I don't know whether I'm getting it right?

--Quarter 1
SELECTD.MerchantName, A.MID, A.TID, ISNULL(SUM(A.SumTrxnMon), 0) AS SumTrxnMon, E.FullName, E.DxBEmail
INTO#Quarter1
FROMdbo.tblRPT_Spend AS A INNER JOIN
dbo.tblMer_DeployORetrieveTerm AS B ON A.MID = B.MID AND A.TID = B.TID INNER JOIN

[Code] ....

View 4 Replies View Related

T-SQL (SS2K8) :: Ranking Rows On Basis Of SUM Of Records

Jul 17, 2014

Following is my table structure

IDRowCount PagID
1448
2267
3297
4216
5405
6254

[Code] ....

PageId is currently set to 0

I have a user input, @IntNoOfRowsPerPage = 800 Means 800 rows per page. So following is the output I require.

AIDRowCountPageId
1448 1
2267 1
3297 2
4216 2
5405 3
6254 3

[Code] ....

The values of PageID are such that summation of RowCount for PageID is <= @IntNoOfRowsPerPage (i.e, 800)

If NTILE function can be used in such scenarios.

View 5 Replies View Related

SQL Server 2012 :: Call SP On The Basis Of Input?

Feb 15, 2015

I have create Store procedure for all the Source and destination table for loading new and updated record.

For example I have below Store procedure

1-SP_Archive_using_merge_Fdoor
2-SP_Archive_using_merge_Fdoop

now I want to call all the sp on the basis of input like If filename is Fdoor then it shold fire the SP_Archive_using_merge_Fdoor , if file name is Fdoop then it shoilud fire the SP_Archive_using_merge_Fdoop like that .

below is the 2 sp .

--First SP
ALTER PROCEDURE [dbo].[SP_Archive_using_merge_Fdoor]
AS
BEGIN
SET NOCOUNT ON
DECLARE @Source_RowCount int
DECLARE @New_RowCount int

[code].....

View 1 Replies View Related

Use SSIS To Pull Data From A BASIS Database.

Oct 17, 2007

Hi All,

Anyone have any experience in connecting to a Basis database?
I want to build an SSIS package to ETL data out of a Basis database and into a sql server 2005 db.
I have downloaded the ODBC driver, but am not usre how to plug it in to SSIS to do teh extraction.

Also I am totally new to Basis...it is not an RDBMS at all but the literature says it supports SQL queries, so I am not sure about where to start.

Thanks,

Stephen

View 7 Replies View Related

Torrid Time Installing SQL EE 2K5 SP2 On Standalone Basis

Feb 21, 2007

I've just spent the most frustrating 4 hours trying to install SP2.

I kept getting "unable to start SQL Server service....see BOL ...re starting the service manually" messages; with the only options being to Retry or Cancel. Retry resulted in the same message. Cancel resulted in all components except SQL (sqlexpress) being installed - therefore totally useless

No mater what I did i could start the SQLservice as it did'nt show in the list of Services; nor did the SQL EE programme show in the Add/Remove Programs...evenn though the other components did.

I deleted/removed everything that was istalled; cleaned up the Registry; and tried again...

..and again

..and again

FIVE install / re-install failures; numerous registry cleanups!!!!

I was at the point where it looked like i'd have to reformat my hard disk, and completely re-install XP, all aplications i.e. Office 2003 etc in order for the thing to work.

I gave it one more shot, but this time, during install

- selected all components except SDK

- I unchecked the Start SQL / BROWSER at startup

- Checked the Enable User Instances and Add to Administrators boxes

Lo and behold....it installed!!!

I'm still very worried as the registry is full of "duplicate" entries i.e. Blah blah blah and blah blah blah.1

But at least the thing is installed.

Would interested to hear if anyone has had similar or other problems with the SP2 install/upgrade

----------------------

For info:

I ran the install as Administrator

IIS not installed

Win XP Sp2 fully updated

Dell Inspiron XPS M1710 laptop with 2gb Ram; 2ghz Duo core processors

View 1 Replies View Related

How To Force Encryption On A Per-database Or Per-user Basis

Oct 30, 2007

I would like to force only certain users on certain databases to use encrypted connections for accessing our 2005 SQL Server. I've found a clunky way to do it, via registering a login event notification and using the kill statement. However, that's ugly, and I don't think there is any guarantee that the event will always fire before an evil client could get off a few evil commands. Is this supported, or is the only supported feature the forceencryption option, which is server-wide?

View 2 Replies View Related

How To Get Data From A Excel File To A Sql Database On A Regular Basis

May 12, 2008

Hello All,i have 2 excel files which i want to upload to sql database. i know how to upload to the folder. my question how to i use these excel files and convert them into  tables in sql database.i have to do this on a regular basis. is there anyway i can do this. the 2 files when converted to tables, i need to create relationships etc. any suggestions or references please. i appreciate it. Thanks 

View 2 Replies View Related

SQL Server 2014 :: Count Order On Weekly Basis

Mar 10, 2014

I want the count of orders of a particular table on weekly basis i.e if date given to me is 10/3/2014 then my output should be count of orders from date 10/3/2014 to 09/3/2014(one week) then count of orders from 2/3/2014 to 08/3/2014(another week) and then from 24/2/2014 to 01/3/2014(another week).....

View 5 Replies View Related

T-SQL (SS2K8) :: Split Function On The Basis Of Contact Number

Jan 9, 2015

Split function. I have records of multiple users, the last value of every record is a contact number (10 Digits- Numeric), I want a split function which can take the whole text and split the records on the basis of contact number.

In order words i want SQL to locate the contact number and move to the next record after that and so on till the end of the text.

create table
tbl_1
(txt varchar (max))

insert into tbl_1 values ('john asfasdf 535 summit ave franklin lks nj 15521 510_644_1079 na na 5,8/12 executive,
finance finance and planning far 5537 21133 8.25 126 ronald d hensor jr. 5575621596

[Code] .....

Output
john jimenez 535 summit ave franklin lks nj 15521 510_644_1079 na na 5,8/12 executive,finance finance and planning far 5537 21133 8.25 126 ronald d hensor jr. 5575621596
jeffrey galione 57 allen dr wayne nj 15810 562_434_0710 na na 5,8/12 executive, technical sales and support good 8137 91630 8.25 126 eileen oneal 8258364083

[Code] ....

View 6 Replies View Related

Transact SQL :: Break Down Content Based Of Hourly Basis

Oct 12, 2015

I am trying to break down the content based of hourly basis. It works fine when there are values for that specific hour but if there are entries or values for a specific hour then it returns null instead of 0. How not to get null instead get zero.

Here is the code below: 

With temp_exp As
(Select pl.state,Cast(signeddate As date) As signatureDate, signeddate As DoneTime From contract c with(nolock) where c.signeddate>DateAdd(Day, Datediff(Day,0, GetDate()), 0)
)
Select
Sum(Case When CONVERT(varchar(8),DoneTime,108) Between '07:00:00' And '07:59:59' Then 1 Else 0 End) '8AM',

[Code] ....

View 3 Replies View Related

DB Design :: How To Identify Tables That Have Been Made On Temporary Basis Not Used

Sep 9, 2015

In the Operating environment databases, may be made tables in the database on a temporary basis but they are still yet and they are not removed, how to identify tables that have been made on a temporary basis are not used (don’t have any read & write records)? 

View 2 Replies View Related

Transact SQL :: How To Export Data To CSV File On Scheduled Basis

Apr 17, 2011

I have an existing MS SQL database (2008 R2). I have a very simple SQL script. I need to run this script on a daily basis and save the results as a .csv file.

I've tried creating a job in SQL Manager - that didn't work. Then I found a tutorial that had me use MS Visual Studio... couldn't get that to function either.

what program I should be using, and how to go about creating this as a task / job that can run on a scheduled basis?

View 13 Replies View Related

Update Data Of LocalDB From Remote DB Weekly Basis

Sep 23, 2007

Hi All

Can any one solve my problem?

Requirements:
We have Our Local DataBase(SQL Server) Guess eg. DBLocal
We have a requirements to update this DBLocal Table Data with Other Database DBRemote(SQL Server) table data.
This Task is Schedule in a Week. Means We needs to update this DBLocal Table Every Week from DBRemote Data.

Both Side we have only one table. Means Source is One Table and Destination is Table One.

Right Now I have a connection string for DBRemote(SQL Server) and SQL Statement for getting Data from DBRemote(SQL Server).

can any one tell me what I need to do for achive this requirement?

please provide me link also; from there i can get enough information for my requirements.

Thanks in Advance

Regards

View 6 Replies View Related

SQL 2012 :: Write Query Which Runs In Background On Cyclic Basis

Jul 9, 2014

I want to write sql query which runs in a background on cyclic basis. Basically i want to count the row entries of 1 table and store the data and the count in two distinct columns.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved