This program gets the values of A and B passed in. They are for table columns DXID and CODE. The textbox GET1 is initialized to B when the page is loaded. When I type another value in GET1 and try to save it, the original initialized value gets saved and not the new value I just typed in. A literal value, like "222" saves but the new GET1.TEXT doesn't.
View 1 RepliesHow can i pass my .net application's Userid to the trigger? I have a audit trail trigger on myTable. I dont know how to pass the userid (not the sql server user) to the trigger when a user delete a record from the application(.NET Application).
The trigger saves the modifications on the table including the userid of one who does the changes.
I want to convert the data from Original Table to Reporting View like below, I have tried but not get success yet.
Original Table:
================================================================
Id || Id1 || Id2 || MasterId || Obs ||Dec || Act || Status || InstanceId
================================================================
1 || 138 || 60 || 1 || Obs1 ||Dec1 || Act1 || 0|| 14
2 || 138 || 60 || 2 || Obs2 ||Dec2 || Act2 || 1|| 14
3 || 138 || 60 || 3 || Obs3 ||Dec3 || Act3 || 1|| 14
4 || 138 || 60 || 4 || Obs4 ||Dec4 || Act4 || 0|| 14
5 || 138 || 60 || 5 || Obs5 ||Dec5 || Act5 || 1|| 14
View For Reporting:
Row Header:
Id1 || Id2 || MasterId1 || Obs1 ||Desc1 ||Act1 ||StatusId1||MasterId ||Obs2 ||Desc2 ||Act2 ||StatusId2 ||MasterId3||Obs3 ||Desc3 ||Act3 ||StatusId3||MasterId4||Obs4||Desc4 ||Act4 ||StatusId4 ||MasterId5||Obs5 ||Desc5 ||Act5 ||StatusId5||InstanceId
Row Values:
138 || 60 || 1 || Obs1 ||Desc1 ||Act1 ||0 ||2 ||Obs2 ||Desc2||Act2 ||1 ||3 ||Obs3||Desc3 ||Act3 ||2 ||4||Obs4||Desc4 ||Act4 ||0 ||5 ||Obs5 ||Desc5 ||Act5 ||1 ||14
Hi..
I am getting a xml stream of data and putting it to a object and then calling a big sproc to insert or update data in many tables across my database... But there is one Table that i am having trouble inserting it.. But if i run an update it works fine... This my code for that part of the sproc..
IF Exists(
SELECT
*
FROM
PlanEligibility
WHERE
PlanId = @PlanId
) BEGIN
UPDATE
PlanEligibility
SET
LengthOfService = Case When @PD_EmployeeContribution = 0 Then @rsLengthOfServicePS
ELSE @rsLengthOfService END,
EligibilityAge = CASE When @PD_EmployeeContribution = 0 Then @EligibilityAgePS Else @EligibilityAge End,
EntryDates = @EntryDates,
EligiDifferentRequirementsMatch = Case When @PD_EmployeeContribution = 0 Then 0
When @PD_EmployeeContribution = 1 and @PD_EmployerContribution = 0 then 0 Else 1 END, --@CompMatchM,
LengthOfServiceMatch = CASE When @MCompanyMatch = 0 Then @rsLengthOfServicePs ELSE @rsLengthOfServiceMatch END,
EligibilityAgeMatch = CASE When @MCompanyMatch = 0 Then @EligibilityAgePS ELSE @EligibilityAgeMatch END,
OtherEmployeeExclusions = @OtherEmployeeExclusions
WHERE
PlanId = @PlanId
END
ELSE BEGIN
INSERT INTO PlanEligibility
(
PlanId,
LengthOfService,
EligibilityAge,
EntryDates,
EligiDifferentRequirementsMatch,
LengthOfServiceMatch,
EligibilityAgeMatch,
OtherEmployeeExclusions
)
VALUES
(
@PlanId,
Case When @PD_EmployeeContribution = 0 Then @rsLengthOfServicePS
ELSE @rsLengthOfService END,--@rsLengthOfService,
CASE When @PD_EmployeeContribution = 0 Then @EligibilityAgePS Else @EligibilityAge End, --@EligibilityAge,
@EntryDates,
Case When @PD_EmployeeContribution = 0 Then 0
When @PD_EmployeeContribution = 1 and @PD_EmployerContribution = 0 then 0 Else 1 END, --having trouble here
CASE When @MCompanyMatch = 0 Then @rsLengthOfServicePs ELSE @rsLengthOfServiceMatch END,
CASE When @MCompanyMatch = 0 Then @EligibilityAgePS ELSE @EligibilityAgeMatch END, --EligibilityAgeMatch,@EligibilityAgeMatch,
@OtherEmployeeExclusions
)
END
Any help will be appreciated..
Regards,
Karen
How do I save my query results into new table.... The ORIGINAL COLUMN Of course before parsing--- But the only data I want is in the three no name columns---(NO Column Name),(NO Column Name),(NO Column Name)I don’t want the original column saved back but I think it existing in the final query is blocking my Insert Into---
View 2 Replies View RelatedI am trying to do one scheduling website for my company. Its contains tasks thats we scheduling for ourself each one and the assigning task by the boss to everyone. I want to do this with two tables. I need to save the task and assigned task in one column named as "Tasks" and to gave after the task name assigned or myself scheduled. Also after deletion of each assigned and scheduled it must save in the table with some name like deleted or any symbol. How can I do this?please help me to solve this issue.
View 1 Replies View RelatedI have a table with two fields Part_num and Pic in SQL server 2000 Pic is of Image type. Is there a way I can save images for each part_num using Query analyzer?
View 6 Replies View RelatedCan anyone show me how to run a prediction query and save the results to a sql table without using the T-SQL OPENQUERY tip here http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx? I am looking for an example in vb.net that I can use in a SSIS script task.
Thanks
Hi,
My scenario:
I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to
1) separate the master (static) data from the flat files,
2) check whether that data is present in the master table, if not then insert that data into the master table
3) If data present then move that existing record to an history table and then update the main master table.
All the 7 fields need to be checked to uniquely identify a single record in the master table.
How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.
Thanks in advance for your help.
Regards,
$wapnil
I have cubes that hold quite a few calculations and so creating Excel pivot table views from it take a long time. This is even true for Excel 2007.
Now I wonder if it would be possible to write back all the calculation results to a relational table - maybe one that exactly matches the report format - so creating another report would be much faster?
SSRS seems to be a way to go but it does not speed up my Excel case.
I read about write-back in ROLAP and MOLAP but I don't think any of these concepts help me to really speed up my reports.
The closest thing I was able to find so far, which besides seems to do exactly what I want is Microsoft's new PerformancePoint 2007. It's just it seems overkill for my projects and the price is at $20K.
Any suggestions are appreciated.
Dirk
Here with the below query iam binding my gridview with industry name,company name,Plant Name,Group Name related to the IDs in Audit table.Select Aud.Ad_ID_PK,Aud.Audit_Name,Ind.Industry_Name,Cmp.Company_Name,Pla.Plant_Name,Gr.Groups_Name,Aud.Audit_Started_On,Aud.Audit_Scheduledto,Aud.Audit_Created_On from
Industry Ind,
Company Cmp,
Plant Pla,
Groups Gr,
Audits Audwhere Ind.Ind_Id_PK =Aud.Audit_Industry and
Cmp.Cmp_ID_PK =Aud.Audit_Company and
Pla.Pl_ID_PK =Aud.Audit_Plant and
Gr.G_ID_PK =Aud.Audit_Group and
Ad_ID_PK in (select Ad_ID_PK from Audits)
Now i want to edit these names.
when i click on edit in gridview these names will be filled into textboxes and when i change the names it should compare the name with particular tables and should get the Id of that and store in Audits table.
For example:
i have this data in my audits table:
Commercial83312
2
2
2
1
Here Commercial83312 is ID of that Audit and 2,2,2,1 are the Industry,Company,Plant and group Ids for that particular audit.In the front end i can see the names of this particular IDs.
when i edit the industry name in the UI it must check the name with industry table and get the ID of the changed name and store it in audit table.
so the data may be changed in audits table as :
Commercial83312
4
2
2
1
so here the industry ID is changed
I need the stored procedure for this.
please help me,its very urgent...
Hello All,
I am working on constructing a software layer around some features of the RDL language that would allow me to programatically generate reports.
I am reading the RDL specification language, and I do not understand three things:
1) How the DataSet element is populated by the query or more precisely how do the <Field> elements capture all the rows inside the table that is being queries?
To my understanding I wilkl have to define several fields that correspond to all columns of interest in the query.
But that is only for one row (?!) How do the rest of the rows get populated? Does the server recursively create new rows based on my definitions until it matches row for row all the data in the table?
2) Once the elements are inside a DataSet how do make use of that data to render it in a table.
I understand how the DataSource, DataSet, and Table work individually, yet I do not understand how to establish a flow of data between DataSet and Table.
3) Do I even need to use a <Table> as an RDL element in order to organize the data in an excel table?
I would appreciate any help. Thank you!
I believe saving prediction query results to relational tables is possible (the BI studio does it!). I am not clear on how to do this w/o the BI studio, which means if I write a DMX query and want to store its output to a relational table, how do I do it?
Tips, anyone?
Thanks!
To avoid locking/blocking, or in transaction scope, we are trying make a common practice of writing coide for update commands in our all SPs based on primary key columns in where clause. I have a following scenario...
UPDATE [dbo].[TL_CST_Locker_Issuance] SET
[isActive] = 0
WHERE
LockerIssuanceId IN (SELECT LockerIssuanceId
[Code] ...
What is the better approach and should be followed to avoid locks and gain performance or best approach.
So i am trying to make a simple database and with one table and use a gui to make rows.
When i run the program in debug mode it creates a new copy of my DB in the debug folder and saves only their, i cannot find a way to save back to the original DB no matter what i do including changing the Copy to Output Directory.
Now the more i try to fix this the more new DB it creates one in the Release Folder, one in the bin/Debug/bin/Debug
one in bin/Release/bin/Release.
I am going crazy trying to scoure the entire project looking for someplace i can fix this.
All i want is to create a DB and be able to make changes to it and only it.
Does anyone have any solutions to this?
Thank you for your time
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
View 1 Replies View RelatedHello, everyone:
I have a table like:
ColName
b
b
b
d
d
d
a
a
c
c
c
I use DISTINCT to filter duplicated row. I want to get the return by original order like:
b
d
a
c
However, SQL Server re-order it if using DISTINCT and return like:
a
b
c
d
Can any one have the idea to handle that? Thanks
ZYT
We have the following two tables :
Link ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
this is my first table
CID-----BID---EID-----Ename-------------Phone---------CELL NO
1--------1----7--------A---------------5047183-----03138236415
1--------1----15-------B---------------5045107-----03212634704
this is my second table
CID-----BID---EID-----Ename-------------Phone---------CELL NO
1--------1----7---------A------------------------------------
1--------1----15--------B------------------------------------
i want a query to update my second table from first table. like this after execute query my second table look like this
CID-----BID---EID-----Ename-------------Phone---------CELL NO
1--------1----7--------A---------------5047183-----03138236415
1--------1----15-------B---------------5045107-----03212634704
Hello,
I have a message that is validated using a schema.
If I send the message that is not compliant with the schema, the message is not enqueued on the target queue but it is send back to the Initiator queue and the message_body contains the description of the Error.
On the Initiator queue I have activated a SP that is I used to end the conversation and log the error.
One of the information that I need to log is the original XML message that causes the validation error so I can check it.
It seems that in the moment I'm on this SP, activated when there is something on the Initiator queue I have no memory of the original message or at least I'm not able to find it.
Any hints/helps??
Thank you very much!
M.B.
I'm a horrible noob everyone, so my apologies come up front.
I know Oracle allows me to just do an
INSERT INTO table
SELECT column1, column2
FROM table2
But I cannot get this to work in MS SQL 2000. (See question #3)
Here's my script that I'm trying to execute from Enterprise Manager:
From this table:
CREATE TABLE ISIS_DATA
(ISIS_STATUS_ID VARCHAR(15) CONSTRAINT ISIS_DATA_ISIS_ID_PK PRIMARY KEY,
ISIS_NAME VARCHAR(50),
ISIS_CLASS_EXPIR_DAT VARCHAR(20),
ISIS_SEX_BD_CAT_SCHL VARCHAR(20))
Where this is a sample row of data:
A123456789 THOMAS, CHARLES B. 009/11/01 M05/04/511G
I want to run that data through some substrings etc. and dump it into another table like this:
//THIS TABLE WILL BE WHERE WE PUT THE RESULTING DATA FROM
//EXECUTING THE SUBSTRs AND OTHER FUNCTIONS TO PREP THE DATA FOR BASIS.
CREATE TABLE ISIS_DATA_PREP
(ISIS_STATUS_ID VARCHAR(15),
STATUS VARCHAR(5),
STUDENT_ID NUMERIC(15) CONSTRAINT ISIS_DATA_PREP_STUDENT_ID_PK PRIMARY KEY,
LAST_NAME VARCHAR(25),
FIRST_NAME VARCHAR(20),
MID_INIT CHAR(1),
CLASS NUMERIC(2),
EXPIR_DATE VARCHAR(10),
SEX CHAR(1),
BIRTHDAY VARCHAR(10),
CAT NUMERIC(3),
SCHOOL VARCHAR(5))
Using this script so far:
INSERT INTO ISIS_DATA_PREP
SELECT B.STATUS, B.STUDENT_ID, B.LAST_NAME, B.FIRST_NAME, B.MID_INIT,
B.CLASS, B.EXPIR_DATE,
B.SEX, B.BIRTHDAY, B.CAT, B.SCHOOL, A.ISIS_STATUS_ID
FROM ISIS_DATA A,
(SELECT SUBSTRING(ISIS_STATUS_ID, 1, 1) STATUS, SUBSTRING(ISIS_STATUS_ID, 2, 9) STUDENT_ID,
ISIS_NAME LAST_NAME, SUBSTRING(ISIS_NAME, 5, 1) FIRST_NAME, SUBSTRING(ISIS_NAME, 5, 1) MID_INIT,
SUBSTRING(ISIS_CLASS_EXPIR_DAT, 1, 1) CLASS, SUBSTRING(ISIS_CLASS_EXPIR_DAT, 2, 8) EXPIR_DATE,
SUBSTRING(ISIS_SEX_BD_CAT_SCHL, 1,1) SEX, SUBSTRING(ISIS_SEX_BD_CAT_SCHL, 2, 8) BIRTHDAY,
SUBSTRING(ISIS_SEX_BD_CAT_SCHL, 10, 1) CAT, SUBSTRING(ISIS_SEX_BD_CAT_SCHL, 11, 2) SCHOOL,
ISIS_STATUS_ID
FROM ISIS_DATA) B
WHERE A.ISIS_STATUS_ID = B.ISIS_STATUS_ID
I keep getting this error:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
QUESTION #1 - Is there an equivalent in MS SQL 2000 to Oracle's TONUM function?
So, to get around this I change all the fields in the receiving table (ISIS_DATA_PREP) to a varchar so there is no conversion necessary at this time.
Now I get this message:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
I look up this error on MS's knowledge base and get something along these lines:
...Because the truncated string is shorter than the maximum length, the char column that does not allow a null value and the char variable is padded with trailing blanks while the varchar column will not store trailing blanks....
QUESTION #2 -
What am I doing wrong on the field structure on the receiving table of the input command? I experimented by setting all fields to VARCHAR(25) so it would accept more data and not truncate, but still get the same message.
QUESTION #3 -
My main question was for the syntax for INSERTing into a table from another table the rows in MS SQL. Basically copying the data. I have it for Oracle, but MS SQL doesn't seem to take it in that format.
Sorry for the absolutely massive post. : ) And thanks for any feedback.
Lance
I want to fill in a field whose name is stored in a variable. This code runs, but the field is not filled in afterward. I think I'm doing something wrong:
SET @field = N'bindery'
SET @ordernum = N'SM38948M08'
UPDATE Orders
SET @field = GETDATE() + 5
WHERE ordernum = @ordernum
My problem is related to using the @field variable in the UPDATE query.
How do I fix this?
Thanks!
Brian
Hello, everyone:
I have an Excel table that has columns like,
Vendoe_Name
Level
Address
Email
Phone
I use the statemen
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C: est.xls;Extended Properties=Excel 8.0')...[Sheet1$]
and got output by column order,
Address
Email
Level
Phone
Vendoe_Name
How to make output by original column order like,
Vendoe_Name
Level
Address
Email
Phone
Any help will appreciated. Thanks
ZYT
I am trying to create a trigger on a table. Let's call it table ABC. Table looks like this:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ABC](
[id] [uniqueidentifier] NOT NULL,
[Code] ....
When someone updates a row on table ABC, I want to insert the original values along with the current date and time getdate() into table ABCD with the current date and time into the updateDate field as defined below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ABCD](
[id] [uniqueidentifier] NOT NULL,
[Code] .....
The trigger I've currently written looks like this:
/****** Object: Trigger [dbo].[ABC_trigger] Script Date: 4/10/2015 1:32:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[ABC_trigger] ON [dbo].[ABC]
[Code] ...
This trigger works, but it inserts all of the rows every time. My question is how can I get the trigger to just insert the last row updated?
I can't sort by uniqueidentifier in descending as those can be random.
I built a query that brings in 'Discounts' (bolded) to the Order detail by using the bolded syntax below. I started off by running the query without the bolded lines and got exactly what I was looking for but without the ‘Discount’ column. When I tried to add the ‘Discount’ into the query, it duplicated several order lines. Although total ‘Discount’ column ties out to the total amount expected in that column, ‘Total Charges’ are now several times higher than before.
For example, I get 75 records when I run without the bolded syntax and I get several hundred results back when adding back in the bolded syntax when i should still be getting 75 records, just with an additional column ‘PTL Discount’ subtotaled.My question is, how to I introduce a new select or join from another table without duplicating the original data?
select
first_stop.actual_departure ‘Start'
, last_stop.actual_departure 'End'
, last_stop.city_name 'End city'
, last_stop.state 'End state'
, last_stop.zip_code 'End zip'
[code]....
(VB 2005 Express) I have a textbox linked to a DataTable (database). I want to save its text into the database as a new row. What is the VB code? The following is not working.
Me.Validate()
Me.myDataTableBindingSource.EndEdit()
Me.myDataTableTableAdapter.Update(Me.myDatabaseDataSet.myDataTable)
Thanks for any help.
Dear All,
I have a database table which has all the inputs, key and the result. In visual studio, I created a decision tree model which has exactly the same fields as in the table. However the visual studio automatically add space preceding the capital letters. As the field name in the Datamining model and those in the database table are slightly different. I cannot use NATURAL prediction join. Is there anyway to told the visual studio not to add the spaces in the variable names?
Thanks and regards,
Tony Chun Tung Siu
I have a sql table in which field ExchangeRate is defined as float. When i tries to enter data into ExchangeRate field using LINQ i get follwoing error.
"String or binary data would be truncated.The statement has been terminated."
My code is as follows
ExchangeRate = float.Parse(txtRate.Text)
I tried with
ExchangeRate = double.Parse(txtRate.Text)
I would appreciate any help.
Hi experts,
I have a textbox and a upload file function in my asp.net page.User can either copy/paste their resume in text or upload their resume file and submit the application.The uploaded file will be saved into a BLOB column, but do you know if text in textbox can be saved into BLOB column?
I received error message on the code:'save Applicant resume to a BLOB-image datatype column objComd.Parameters.Add(New SqlParameter("@AppResume", SqlDbType.NText))
error:Exception has been thrown by the target of an invocation.Operand type clash:ntext is incompatible with image
I have a query that simply slelcts the min value of a specified fieldfrom one table, I want to take that value to update a field inannother table, just can not figure it out.
View 1 Replies View RelatedI'm looking for a query that can "batch" update one table from another. For example, say there are fields on both tables like this:
KeyField
Value1
Value2
Value3
The two tables will match on "KeyField". I would like to write one SQL query that will update the "Value" fields in Table1 with the data from Table2 when there is a match.
Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani
View 3 Replies View RelatedHow do I update a table of 600 records with varying amounts of the same branch names say, there are 60 records that are Atlanta etc.Based on the following query that retrieves the Branch Id. Select t1.Branch, t2.SCName, SCID from WWFE07BoothLeads As t1JOIN SMC_New_Products.dbo.SupportCenters As t2ON t2.SCName Like t1.Branch+'%'Update WWFE07BoothLeads Set BoothId = ?(1) Where Branch = ?(Atlanta)
View 2 Replies View Related