current updated value in table

hello all..

i want to update my table information with the value which is currently inserted/updated in another table dynamically..how to get the value of a currently inserted single cell in that table..?

table1 contains Refno,....Refno is primarykey,identity

table 2 contains uploadID,...uploadID is primary key,identity

table3 contains RefNo,UploadID(both r foriegn keys corresponding to table1 & table2)

how to fill table3 with values(not manually) when i am inserting records into table1 n table2 ..( refno and uploadId are identity columns )..Any Idea..?

thanks in Advance

Anne

hello anne..,

are u use sqldatasource to insert table1 and 2? if yes, in sqldatasource ,u can add query insert like this:

insert into [table3] ([refno],[uploadid]) values (@.refno,@.uploadid)

ok.. let's try it..

:)

|||

Hello hardy,

tranks for ur responce..i think i didn`t explained my requirement well...Actually i am doing some workflow application...in that app i hav one report Incident/Problem page...users has to enter the details regarding their incidents/problems....after submit click i hav to store the form information in Table....The form contains user,date occured,Application,Module,Description and 3 file uploads.....so now to store form info in database i created two tables...Submit (Refno,date,Application,Module,Description), Upload (UploadID,MIMEType,ImageData)......

In the above two tables RefNo ,UploadID are primary keys and identity columns....so no need to insert values in thses two columns explictly as these are identity columns..I used sqldatasource to insert the remaining info into the that tables...

while retriving i need information from the two tables..so i need one more table to relate these two tables...then i created one more table UploadRelate(Refno,uploadID)...both these columns are foriegn keys corresponding to two tables simultaneously....

my plan is to insert values into third table whenever values r inserted into other two tables...so that while retriving i can get the correct uploaded files for the corresponding user.....mySubmit, ,Upload,UploadRelate tables has to look like this after inserting values........

RefNo : user : date : application : Module: Description UploadID:MIMEType:ImageData RefNo : UploadID

1 a 1/1/07 sas sdas dsdd 1 jpg Binary 1 : 1

2 b -- -- -- -- 2 bmp -- 1 : 2

3 c -- -- -- -- 3 txt -- 1 : 3

4 d -- -- -- -- 4 jpg -- 2 : 4

5 doc -- 2 : 5

......user may upload 1 file or 2 files or 3 files..So now the problem is to insert the values in to 3rd table....whenever the other two tables r inserted with values my 3rd one has to be inserted...Is it the correct way to store in 3 tables like this or can i achive my requirement in any other way..?if my oproach is ok...then how to get the value of currently inserted column value from table...?...

insert into [table3] ([refno],[uploadid]) values (@.refno,@.uploadid) can be used ..but the thing is how to get the values of @.refno,@.uploadid...from the other two tables?

Thanks in Advance

Anne

|||

hello...

i am not undestand about ur third table like:

refno : uploadid

1 : 1

1 : 2

1 : 3

2 : 4

2 : 5

why the 1 relation with 1,1 with 2, 1 with 3..

u can not do like that..

emmm... i know..

u must use normalize if u want create table... like this:

ur 3rd table can be delete, so u have 2 table,but add uploadid in table one like this:

mysubmit table:

RefNo uploadid user date application Module Description

1 1 a 1/1/07 sas sdas dsdd

1 2 a 1/1/07 sas sdas dsdd

upload table:

UploadID MIMEType imageData

1 jpg Binary

2 bmp --

so u don't need create 3rd table, but u can call that data from two table...

ok..., let's try it...

the first important thing if u want to make table, u must understand about normalize..

|||

You can. However since there is a one-to-many relationship here instead of a many-to-many relationship, you can place the RefNo directly in the Upload Table (Since each upload must have one and only one RefNo).

That should greatly simplify your application logic.

|||

Thank u for the replies Hardy and Motley...

|||

Hello..

i am getting some problem regarding the above requirement...i am able to enter the data in my first table ,but when i am trying to enter data in second (upload) table i am not able to get the currently inserted refno from first table..my storedprocedure for second table is

ALTER PROCEDUREdbo.UploadInsert

(

@.TitleVarchar(50),

@.MMIETypevarchar(50),

@.ImageDataimage

)

AS

BEGIN

set nocount on

declare@.Ref_Noint

select@.Ref_No=@.@.IDENTITYfromdbo.Submit

INSERT INTO[upload]

([Title],[MMIEType], [ImageData],Ref_No)

VALUES(@.Title,@.MMIEType, @.ImageData,@.Ref_No)

end

RETURN

--Ref_No value is becoming null while inserting into upload table..is it the correct way to get the identity value from submit table ...? in submit table records r entering corrrectly...

Pls i need help..

anne

|||

yes i got the solution...i am doing mistake in my submit storedprocedure...without returning identity value i am retuning 0..so i am not able to get the refno into second table...

AnyHow Thanks to all

anne

sql

cube has been updated by the server, data is now obsolete

Client issues query which sends out individual requests to the 2 OLAP servers that are load balanced. The client evaluates the versions of the returned record sets to ensure the consist data being returned for one single query. Otherwise, this error will be seen:

The cube has been updated by the server the data is now obsolete.

These servers sit behind a cisco 11506 CSS with load balancing based on balance type: least busy server, also persistence based on cookies.

My developer says this worked fine for a long time then just 'started happening'.

any suggestions are appreciated.Any infomational messsage or error on SQL error log?|||I'll see if there are any other associated messages or logs and post them.

Cube corruption

I have an AS 2005 cube (MOLAP) that is updated incrementally on a daily basis. Twice in the last few weeks, the cube has suddenly become corrupt. The By-Day aggregations for previous days show incredibly large positive and negative numbers. These numbers at the time they entered the cube were accurate.

As background, the incremental update is done via a view and the view is calculating fields. At this point the cube is processed manually during the AM, and the processing has not reported any errors.

Any insight would be appreciated.

Doug

First try to install SP1 for SQL Server 2005 and see if the same is happening. (http://www.microsoft.com/downloads/details.aspx?familyid=cb6c71ea-d649-47ff-9176-e7cac58fd4bc&displaylang=en)

If your problem persists, please contact customer support and report the problem.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

hi Doug,

I am investigating this issue and trying to reproduce it. Could you give me more information on this issue such as:

1. Are the results correct after a process clear followed by a process full on the cube (and everything else it depends on)?

2. What is the sequence of operations that consistently lead to this situation (like start with no databases, deploy the project, process full, then continue with daily process update)?

3. how by day aggs are setup?

thank,

Gang

|||

Gang,

Thanks for looking into this.

1. Yes, the results are fine if we do a Full Process. The couple of times this has happened, that has been the solution.

2. The sequence of events here is pretty straightforward. We just do a Process Incremental every morning (after updating the dimensions). We've been in production about a month now. I'm doing it manually so far just to watch it execute. It never reports failure thru the output windows. Occasionally, our data migration fails, and I'm unable to refresh the cube. In this case, since each nightly load ends up in a table, I just add that table to the view and do the incremental update to catch us up.

The cube is loaded thru a view that has calced fields. That may be a problem for the cube. The view correctly unions the tables, and the data are fine if you query the view. I tested this. The cube, on the other hand, may not be able to digest the union of two recordsets that contain calculated fields. That might be something to check on. This is the only thing I can think of that might me causing the problem. Most of the time, the incremental process works just fine, though we did notice a real performance hit when we added the calculated fields to the view loading the cube.

3. I'm not sure what you mean with this question. We're in the telcom business, and the data in question are call records, a couple of million a day. In the datamart that's underneath the cube, each call gets assigned a "day" key. "Day" is the lowest level of granularity in the DataMart. In the cube, we group by Year/Month/Day and sum the call minutes. What we occasionally see is that certain days become corrupt, with super large numbers appearing, both positive and negative. The data around them are fine. We process full and the problem goes away.

Hope this helps

Doug

|||

Doug,

thank you for taking the time to report the problem in such great detail. We are going to simulate your project. Is that possible to share your project with us? All we need are data source, DSV, dimensions plus cube. Not the data. It will be great If you can trim your project to exactly what's necessary to repro the problem (i.e. exclude “experimental” cubes/dimensions).

Gang

|||

Gang,

Any help is appreciated. I have created a Winzip archive with the VS objects (.Cube, .dim ,etc.) How do I get that to you? I'm not seeing a way on the forum to attach this file.

Doug

|||

Doug,

could you send it to me via email(get my email address by clicking my name)?

thanks,

Gang

CSV Exporting SSMSE (Text Identifier)

I just updated to SQL Express SP2 per this Feedback item:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=132806

But I don't see an option to use quoted identifiers. Is it not included in the Express version?

I thinnk that is set on a database level. (And the 'default' condition is [ ON ].)

To change:

ALTER DATABASE MyDatabase

SET QUOTED_IDENTIFIER ON

CSV --> SQL and CSV <-- SQL

I have two things that I need to do. I have a csv file that comes from our distributor. It sits and is updated everyday on their website. I would like to automate a table to be dropped and updated with this information everyday. Then I can use some procs to do with it what I need to do with it.

Second I would like to do what ever he is doing and create my own csv file that is outputted from a proc's output everyday. I have the proc setup so when it is run it will pull the information I need and then I copy it to excel. I would like to take the idiot user who is going to mess it up (me) out of the equation. Is there anyone that can help me with how to do this? Thanks, Ben.

Quote:

Originally Posted by ryushinyama

I have two things that I need to do. I have a csv file that comes from our distributor. It sits and is updated everyday on their website. I would like to automate a table to be dropped and updated with this information everyday. Then I can use some procs to do with it what I need to do with it.

Second I would like to do what ever he is doing and create my own csv file that is outputted from a proc's output everyday. I have the proc setup so when it is run it will pull the information I need and then I copy it to excel. I would like to take the idiot user who is going to mess it up (me) out of the equation. Is there anyone that can help me with how to do this? Thanks, Ben.

you can use DTS(Data Transformation) In SQL Server .you can also import/export wizards in enterprise manger of sql server to do this schedually.if you want to customize the control in your application ,you must use MSSQL SDK.