hi,
i need to create two instances of db and transfer incremental uploads from one db to another without having to transfer the entire table of data again and again. how should i go about it? what commands should i use?
thanks in advance
ramya.
I have tried to upload about 1 million records from a foxpro table to SQL Server. It only loaded about 700,000 records.
I found out that the drive where the SQL Server transaction log file were located had increased to 15 gigs and I run out of disk space.
I guess that is the reason it could not load the rest of the records.
What should be the option setting in my SQL Server database when doing "bulk uploads"?.
Should I delete table indexes when uploading?.
Should I set the Recovery Model to simple?.
Thanks
Hi,I created a small application to upload small video files on my
local machine and tested it to work perfectly on my local sql server
database. I then change the connection string to connect to the remote
database.When I do this and try to upload, the application hangs and
the video file is not uploaded. The file is small file only about 10MB
or less.When I upload the same file locally,it works perfectly fine and
uploads quickly without hanging.Here's a link to sample of my web.config filehttp://www.bleusolutions.com/sampleconfig.txt Here's a link to the pagehttp://www.bleusolutions.com/foto/uploadvideo.aspxI believe that this may be a database issue. I am running my
application locally
(http://localhost:4796/BleuS/foto/uploadvideo.aspx)and all I do is
change the web.config connection string to point to my local database
or to the remote database. When I point to my local database the application works fine as you can see: http://www.bleusolutions.com/images/localupload.jpgWhen
I run the application locally and change the connection string to point
to the remote database this is when I have problems with the same video
file. The application hangs for a minute or two and then it finally
finishes the upload. When I go to the database to view the table the
table takes a minute or two to open and then I get this message:http://www.bleusolutions.com/images/remoteupload.jpgAnd this is what the table looks like after I click Okhttp://www.bleusolutions.com/images/remoteupload2.jpg Any help on why this is happening or how I can fix this issue will be greatly appreciated. Thanks
I am using transfer manager in SQL 6.5 to copy a database and
all objects with data to another server. Transfer manager is not recreating all stored procedures. This even happens when I used it to another database on the same server. Any ideas??
Is it possible/advisable when transfering very large amounts of data from server to server to:
trasnfer the data to a new table first
second alter new table adding indexes, defaults, ets based on original table
if it is what flow item would be used to transfer/alter the indexes and defaults?
I'm very new to ssis so the more detail you can give the better.
Thanks
Hi guys,
I am trying to get a result like below without using the cursor.
col1 col2 col3 col4
1111 date uniquenumber 6
1111 date uniquenumber 5
1111 date uniquenumber 4
1111 date uniquenumber 3
1111 date uniquenumber 2
1111 date uniquenumber 1
2222 date uniquenumber 4
2222 date uniquenumber 3
2222 date uniquenumber 2
2222 date uniquenumber 1
3333 date uniquenumber 2
3333 date uniquenumber 1
the column that say unique number is unique and is not duplicated and date column might have duplicates
Please advise whether it is possible to write a query without cursor.
Thanks,
Anu
Hi!
I have a request of having a select statement displaying a list of values in descending order and another column that tells what record it is.
something like this:
id | value | no
4 345 1
7 234 2
2 143 3
9 32 4
3 4 5
...
is this possible?
can it be something like this:
select id, value, increment(1,1) as no from tblTable
order by value desc
thanks.
I am looking to create a incremental value based on the resulting insert that I am using. There already is another field being used as an identity field. I have a beginning value that I just want to add the row number to for the insert.
insert into lineitem select substring(group_id,4,len(ltrim(rtrim(group_id)))-3) as co_code,
0,0,(case when enddate < cast(month(getdate()) as varchar(10))+cast(day(getdate()) as varchar(10))
then 'Prior' else 'Current' end ),
left(acct_type,2) as bene_type, convert(smalldatetime,left(ltrim(rtrim(eff_date)), 8)),
0,trans_amt,0,0,convert(smalldatetime,left(ltrim(r trim(sett_date)),8)),
ltrim(rtrim(b.fname)) + ' ' + ltrim(rtrim(b.lname)) as payee,0,0,a.ssn,'Y',999+count(*),
(case when isnull(b.location,'') = '' then '' else b.location end) as location
from mbi_tran_temp a
left join enrollees b on a.ssn = b.ssn and
ltrim(rtrim(a.group_id)) = ltrim(rtrim(b.mbicode))
The '999+count(*)' is where I would like to have the incremental value.
My Question is of understanding Incremental Backup. How does this work,
Does it go by new files added or activate on file size.
Hi,
Is there any way to maintain history of data while incremental load without using the Slowly changing dimension(SCD) concept?
Hi Friends please let me know how can we incrementally load a destination table with source table. bearing in mind that we need to track that there are no duplicates in the destination table. I need to load only changed or new data in the final load. Please give me some examples also. I am tryin this from last 2 days as I am totally new to SSIS.
View 11 Replies View Relatedhi guys,
I'm a newbie in sql.
anybody know how,
if I want to update incrementally using integration service.
say I have 2 server. we want to took the data from server A and put the data to server B.
but we want just the changes in server A that send to server B.
regards,
-dedys
I have done an bulk upload and I would like to start doing incremental uploads. I just want to upload only the new records that have been added to my data source ( free foxpro tables ). Can anybody point me to an example or info to accomplish this.
Thanks,
Sergio
Hello:
I have been trying to run transfer manager to transfer all of the data
from the production database on one server to a test database on another
server(to refresh it). In order to make sure it runs on the server, I have
been scheduling it under EM to do so and I am pointing to the log on the
destination server on the EM Transfer panel.
For some reason, I am getting the following message in the destination
server error log:
99/02/16 10:24:41.42 ods Error : 17824, Severity: 10, State: 0
99/02/16 10:24:41.42 ods Unable to write to ListenOn connection
'.pipesqlquery', loginname 'sa', hostname 'TEMP09'.
99/02/16 10:24:41.42 ods OS Error : 232, The pipe is being closed.
99/02/16 10:24:41.42 spid17 Error : 1608, Severity: 21, State: 2
99/02/16 10:24:41.42 spid17 A network error was encountered while sending
results to the front end. Check the SQL Server errorlog for more
information.
I checked the event viewer error log and see no messages for today.
Can any one advise me what I need to do for this to run successfully?
Thanks. Any information furnished will be greatly appreciated.
David Spaisman
Dear all,I have an SQL table where I am doing insert from an asp.net web project.I have a primary Key, set to be incremented by 1on each insert.I have another column, Col1 that should be incremented on each insert, How can I do this?I need to have 2 incremental columns? do u have any idea on how to do this?I am doing this right now by calling a select statement in the Add stored procedure on this table, I read the last value of col1 and then I increment it by 1, this work fine on development environement, but when many users are accessing this website, I will have wrong values for col1. Any idea??Thanks.
View 6 Replies View RelatedHi Everybody,
Right now i have a DTS package thats populating a fact table,I'm trying to do an incrmental update of the fact table (which has a sequential#id).
any ideas ,please
Thanks,
Paris
i have a catalog and add directory which has 10,000 documents and all are index but if i add 1000 documents to that directory and i don't want those 1000 documents to be indexed. i want only previous 10,000 index document and don't want to new document to be indexed. is there any way can stop the new document to be indexed, please let me it's bit urgent.
Thanking you in anticipation
Hi All
I have an existing table of students, the school now has decided to give then a General Record No.The table structure is as follows
Students
++++++++++++++
Name
Address
TelNo.
++++++++++++++
I have modified the table as
Students
++++++++++++++
GRN.
Name
Address
TelNo.
++++++++++++++
The data already exists in the table . I just want to add the GRN No. that would be incremental
E.g.
it should be 20060001 for the 1st record
20060002 for the second
The rest data should be as it is
Plz help
Thanks
Hi All
I've created an SSIS package that loads data from source to destination, using Lookup and conditional Split to check New rows and changed rows for one table.
Now I want to take this father by loading data for multible table more that 100. I did it in T-SQL using dynamic sql and cursor.
How can I achive this using SSIS.
I was wondering if there is a way to schedule a tast that will dump afixed width text file of all the new entries in a table. So if I hada table with likeusername - varchar(20)created - smalldatetimeI could get a weekly feed each week of all the new users in a textfile. I know I could write a script that would go through and do thisby looking at the time stamp, and the last time that the filepreviously ran and get the new dates but I was hoping there was abuilt way to do this. Or perhaps a more elegant solution.Thanks,Charlie
View 2 Replies View RelatedHi allNeed your help to do this; I got a table with these records:Supplier RegNo Status PoNumberABC sbh1309m 1DCD sbt99x 1FGJ sbg3939m 1FGJ sbg3939m 1OEE ey3939d 1Need to have a sql command to transform to :Supplier RegNo Status PoNumberABC sbh1309m 1 50001DCD sbt99x 1 50002FGJ sbg3939m 1 50003FGJ sbg3939m 1 50003OEE ey3939d 1 50004Any ideas?Thanks in advance.Rashid.
View 1 Replies View Related
I am attempting to perform an incremental load, inserting new rows and updating existing rows. I am using a lookup and everything works fine, except when it is the first load of the destination table. As there are no records in the table at all, the lookup fails. I thought of using a rowcount - if the count variable is zero load everything from temporary table to load table, otherwise perform lookup and incremental load.
Can anyone help?
We are in the process of converting our existing incremental loads from DTS to SSIS.
Currently we get all the data for the past month into temp tables in the warehouse, compare with key fields add the new rows and update changed rows. All this is done using Execute SQL task.
Is there a better way to implement the incremental logic using SSIS any new objects that be used to avoid too much SQL codes? Performance is very important and we do a lot of aggregation after the load for the reports to run faster so that we can meet customer SLA's.
We have around 20 tables that needs to be loaded 4 have large amount of data between 20 and 40 million rows out of which we will be brining over around 100 thousand during each incremental run. The other tables have less than 100,000 rows so does not hurt truncating and reloading the entire table.
Any assistance is appreciated.
Thanks!
Hi everyone. I'm trying to figure out how to run an incremental load into a Staging table.
At this point I'm not trying to Conditional Split it between "New" and "Changed" records... just the load.
The logic in my head says that after each load, you can take the most recent "modified" date/time and store that in an incremental load table. That way, next time you run an incremental load, you just have to look up that "modified" date/time, and only load the source records with a "modified" date/time later than the record in your incremental load table. Does that plan sound feasible?
I think so far my problem is that my source is on an ADO.NET connection, and my incremental load table is on my SQL Server. So when I do my load from the ADO.NET database, I cannot read the data from the incremental load table.
Is my logic flawed?
Any help would be appreciated.
Thank you very much!
i have to import a csv file into a database via ssis and so far everything works fine. but now i have to add a column where a incremental int should be inserted, so every row should have a unique number ... i tried to realize this by using derived col transofrmation, but without success ... has someone an idea how to do this?
View 10 Replies View RelatedDoes an Differential Backup contain all the changes since the last full backup, including Inserts, Updates, and Deletions. Our DB has "Truncate on Checkpoint" = True, so Log backups are non-sensical. I want to apply the Differentials to an archive, offline DB.
"one pebble does not fill the void, but it is a start."
"Dedicated to only creating original mistakes."
I'm trying to update every record with a incremental number. I wrote the following query but it updates the records with the same number. Could someone please tell me what I'm doing wrong? Thanks.
declare @NextKey int
SELECT @NextKey = NEXT_KEY FROM TABLE_KEYS
WHERE TABLE_NAME = "tblEmp"
set @NextKey = @NextKey + 1
DECLARE tblNewEmp_cursor CURSOR FOR
select emp_pk from tblNewEmp
open tblNewEmp_cursor
FETCH NEXT FROM tblNewEmp_cursor
WHILE @@FETCH_STATUS = 0
begin
update tblNewEmp
set emp_pk = @NextKey
set @NextKey = @NextKey + 1
FETCH NEXT FROM tblNewEmp_cursor
end
close tblNewEmp_cursor
Hi,
I am working on one application, which retrieves data from multiple tables in the database and all the fields retrieved, are exported as an excel sheet. All the export functionality is done through DTS. And the data is retrieved using an SP.
I am supposed to use an "incremental backup" approach here. Means, for the 1st scheduling of the package, all the database dump will be there in excel file. But, after that only fields that are updated/inserted are to be filled in the excel file. Each time, when the dump of the database is taken, the excel file is stored in an archive folder with Date and tiemstamp(e.g. TEST_11_04_2007_15_00_00.xls)
e.g. For the first dump, I get 100 records from the database. Before next execution of the package, 10 rows get inserted and 1 row gets updated. So, on the second time execution of the package, I should populate the excel sheet with those 11 rows only, and not 110 records.
I am not authorised to change the database schema.
So, is there any approach to try out this?
Hi, all.
I want to Select query with incremental column on the fly.
For example
Use pubs
GO
select * from jobs where job_desc like '%e%' Order by max_lvl
returns
job_idjob_descmin_lvlmax_lvl
1New Hire - Job not specified1010
12Editor25100
13Sales Representative25100
...
I want to add here Rank Column numbering in order
select RankOnTheFly, * from jobs where job_desc like '%e%' Order by max_lvl
Then result will be..
Rankjob_idjob_descmin_lvlmax_lvl
11New Hire - Job not specified1010
212Editor25100
313Sales Representative25100
..
I can get the result using cursor and looping throught and inserting or Using Identity function.
But, I saw before there is just one simple Select query doing that.
Does anyone know this?
Thank you..
Hi All
I have this table which has about 30 thousand records
I want to send all the records everyday at night to the other interface(TIBCO) in form of XML.
I can send all the records at once or maybe 10 records at a time
1. If I make one XML with all the records and send it, its risky as XML would be quite big, also it would be time consuming and would generate a big load on the program(.NET program will create XML). Also, any abnormal error would kill the whole process and we would have to start again
2. Processing 10 or 20 records at a time and then sending the XML(with these 10 or 20 records) and doing this till all the records are sent. If there is any error in between in the program(.NET), the program can start from that step again. Now the problem is how I can keep track of the record number already sent to the other interface
Can someone help?
Thanks
I required query for Incremental Load of CDC using Query
View 1 Replies View RelatedHi;Before I start let me say I know this is a silly way to go aboutthings, but it is one of those "my company made me do it" things.My company is in the process of ( finishing ) migrating from foxpro tosqlserver.They have a foxpro program that does a lot of various updates. Iwould like to write a tsql script to replace it.The trick is that the tsql script would have to have some proceduralprogramming artifacts that I don't think it has.I/my company would like the script to generate output messages thatthe user can see while the script is running. Everytime I have ran ascript in query analyzer I usually don't see the output ("print"statemetns, etc ) until all of the work isdone. Is there a way to run thing so I can see the output statementsas the work is being done?Can print statements work from within a tsql loop?The other thing is that I/my company wants the tsql script to writeoutput messages to a log file while it is operating ( for errorchecking and other logging).Is this possible with tsql?Thanks in advance for the infoSteve
View 1 Replies View RelatedIs there a way in SSIS to go through a certain column in a table and to collect incrementally the values for each row in this column and to finally insert the final value to a second field or parameter?
Is it possible to do it with no external code? only within SSIS tasks?