This is the code for calculating a formula field in Crystal Reports. I want to implement the same in Sql Server Reporting Services.. But it doesn't have the feature of For Loop.... The strings started with @ are formula fields.... Can anyone tell me, how can the below code be implemented in Sql Server Reporting Services
numbervar YR; for YR := 1 to {@CalcFiscalAge} step 1 do ( IF YR = {@CalcLifeCode} +1 then locFactor1 := 1; exit for; IF (YR = 1 OR YR = {@CalcFiscalAge}) THEN HALF_YEAR := 2 ELSE HALF_YEAR := 1 ; LINEAR := ROUND(REM/ ({@CalcLifeCode}-YR+1.5)/HALF_YEAR,4); MACR := ROUND(REM / {@CalcLifeCode}*{@CalcSRate}/HALF_YEAR,4); IF MACR >= LINEAR then DEP := MACR ELSE DEP := LINEAR; locFactor1 := locFactor1 + DEP ; REM := 1 - locFactor1; locFactor := locFactor1; );
What is the way we could implement a business logic from a Table bystoring it statemnnets in a colums and defining an execute sql toexecute it.Some legal requirements make it diffcult for us to createmodify stored procdures so Iwant to have a process where we create newrows in a table and execute it to execute business logic.All views are welcome.Havin g one table two tables different approaches to store ststementsand execute them....Case logic how to implement it?Flags in the tble colums in the tablesetcThanksAjay Garg
I am currently working with C and SQL Server 2012. My requirement is to Bulk fetch the records and Insert/Update the same in the other table with some business logic? How do i do this?
Hi! I wanted to execute an update command in T-SQL using some special characters like single quote ('). when i try with this it fails to accept the command. For example the text which i wanted to update is something like this "<Choice index="3" value="Don't know"/>", here i have a single quote('), less/greater than signs (<>), double quotes and forward slash (/). when i try with this type of text it fails to update, the field data type is NChar. I need help on how to successfully execute with this type of data.
In a very busy SQL2000 enterprise edition server with 8GB memory and 6 cpus sp3, I could not install a update trigger, unless all the appl connections are dropped. For this 24 HR running svr, could do it.
then I try to run a query as follows:
if exists (select rfABC.* A from rfABC inner join remoteSvr.XYZDB.dbo.vwIP L on A.Address = L.address and A.metro <> L.metro begin print ' ---- Yes metroID <> LAMetro, start job exec.... -----'
insert into tempCatchMetroIDGPRS select rfABC.*, metro, getdate() from rfABC inner join remoteSvr.XYZDB.dbo.vwIP L on rfABC.Address = L.address and rfABC.metro <> L.metro
update rfABC A set A.metro = L.metro from rfABC A inner join remoteSvr.XYZDB.dbo.vwIP L on A.Address = L.address and A.metro <> L.metro end else begin print ' ---- no metroID <> LAMetro, skip job exec.... -----' end
------------------------------ this query hang there could not execute. When I took off the if ... else condition, it run with like 0 second. Wondered if a 'busy' (which updates the IP address continueously) could cause above issues...
it is taken from SQL2K5 SP2 readme.txt. Anyone have idea what to do to implement this ? Our sp2 is failing. we suspect the above problem and researching it.we are running on default instance of SQL2K5 on win2003 ent sp2
"When you apply SP2, Setup upgrades system databases. If you have implemented restrictions on the ALTER DATABASE syntax, this upgrade may fail. Restrictions to ALTER DATABASE may include the following:
Explicitly denying the ALTER DATABASE statement.
A data definition language (DDL) trigger on ALTER DATABASE that rolls back the transaction containing the ALTER DATABASE statement.
If you have restrictions on ALTER DATABASE, and Setup fails to upgrade system databases to SP2, you must disable these restrictions and then re-run Setup."
Posts: 20 Joined: Aug 2003 Mon November 08, 2004 9:42 AM
I have a table where from 1 to 3 fields will be update (lastname,firstname,birthdate). Any combination of the 3 can be updated. I need a trigger to handle the updates which update some other db tables. I can handle the update trigger part, the ? is how to best approach the logic. I can do thee separate statements to handle an update for each of the columns, but that could cause 3 separate updates to fire. I was looking at doing something like
If UPDATE(last_name) and UPDATE(first_name) and update(date_of_birth) BEGIN ... end else if If UPDATE(last_name) and UPDATE(first_name) begin ... end
I am dealing with two tables and I am trying to take one column from a table and match the records with another table and append the data of that column.
I used an update query that looks like this:
UPDATE Acct_table Set Acct_table.Score = (Select Score_tbl.Score from Score_tbl Where Acct_table.Acctnb = Score_tbl.Acctnb
This process has been running for over an hour and a half and is building a large log file. I am curious to know if there is a better command that I can use in order to join the tables and then just drop the column from one to the other. Both tables are indexed on Acctnb.
I am facing a small problem while updating the warehouse using SSIS package.
Here is an sample scenario. I have a staging table T1 with some important columns as stateid,ename etc .. and target table as Trg with only two columns stateid and statecnt. The Rule is, each state will be sending an excel file which basically contains state and employees belonging to the state information. For the first time when i execute the SSIS package, i need to load statetid and count_of_employees for that particular state. say for example.
SELECT stateid, count(ename) from T1 where stateid = ?
For the next time if the same state information is received then we need to update the count to the existing record in the Dataware house rather than creating a brand new record once again.
I am little bit confused of implementig this logic. Can anyone help me out in accomplishing this task. What can done or what steps is involved in achiving the task.
We have a table X with a gender column taking values M(male) or F(Female).There are 52 rows in the table X.We want to update all the rows with Gender M as F and update all remaining F as males (M) in a single Update Query.Im looking for the exact logic we would write in the single update statement. Please help me out.
hi.I am having probelms with an update statement. every timei run it, "every" row updates, not just the one(s) intended.so, here is what i have. i have tried this with both AND and ORand neither seem to work.i dont know why this is elluding me, but i'd appreciate help with thesolution.thanks.UPDATE addSET add_s = 1WHERE add.add_status = 0 and add.add_email = 'mags23@rice.edu'or add_s in(SELECT a.add_sFROM add a, edit eWHERE a.email_address = e.email_addressand e.public_name = 'professor')
i need to update for example the eployee 1111 with employee 3333 but with swap ( take the value of employee 1111 in field- fld2,fld22,fld222 and swap value between employee 3333 in field- fld2,fld22,fld222 )
Code Snippet ---update eployee 1111 with employee 3333 -so if i put the value 2 than ------------------ swap value between 2 employee set empid1= 1111 set empid2=3333 value_swap=2 if value_swap=2 than update fld2,fld22,fld222 with fld2,fld22,fld222 ------------------- take the value of employee 1111 in field- fld2,fld22,fld222 and swap value between employee 3333 --------------------in field- fld2,fld22,fld222
Code Snippet ---update eployee 2222 with employee 1111 -so if i put the value 5 than ------------------ swap value between 2 employees set empid1= 1111 set empid2=2222 value_swap=5 if value_swap=5 than update fld5,fld55,fld555 with fld5,fld55,fld555 ------------------- take the value of employee 1111 in field- fld5,fld55,fld555 and swap value between employee 3333 --------------------in field- fld5,fld55,fld555
I have a perl program that is looping through a hash of a hash. I need to Update any existing records but also insert any new records in the table using collected data in the hash.
Life would be very simple if it was possible to use a Where Clause in an Insert statement but not does not work.
Here is some example code from my program: sub Test{ foreach my $table(keys %$HoH){ foreach my $field(keys %{$HoH->{$table}}){ if($table eq "CPU"){ my $CPUstatement = "INSERT INTO CPU(CPUNumber, Name, MaxClockSpeed, SystemNetName) Values ('$field', '$HoH->{CPU}{$field}{Name}', '$HoH->{CPU}{$field}{MaxClockSpeed}' , '$HoH->{Host}{SystemNetName}')"; print "$CPUstatement"; if ($db->Sql($CPUstatement)) { print "Error on SQL Statement"; Win32::ODBC::DumpError(); } else { print "successful"; } } }
I have got a business logic update conflict handler working, but I have had to work round what appears to be a bug.
Please can someone confirm if this is indeed a bug €“ and if it is a known bug?
My conflict handler needs to take some columns from the publisher row and some from the subscriber row in the event of conflict.
I can quite happily generate a custom dataset which contains the winning row that I want €“ I can see that because I can step through the conflict handler with debug when a conflict occurs.
However, just returning ActionOnUpdateConflict.AcceptCustomConflictData from the UpdateConflictsHandler method does not set the publisher and subscriber columns correctly. I end up with different values on the two databases.
I have found that the only way to get the correct rows on both publisher and subscriber is to create a new ADO connection to the publisher and actually perform an update €“ updating all the modified columns. This now works reliably in my testing.
Fortunately, due to business rules the frequency of update conflicts are likely to be very infrequent, but I would very much like to avoid having to do the €˜unnecessary€™ update.
Notes:
I am using column level tracking €“ but I have seen the problem with row level tracking too I have mainly been using SP1 but have repeated the test on a configuration using the SP2 CTP €“ and the problem occurs there too The problem is not due to complex logic in my code. If the method just sets customDataSet = publisherDataSet.Copy and then returns ActionOnUpdateConflict.AcceptCustomConflictData, the changed and winning publisher values are not sent to the subscriber
I have a stored proc that contains an update which utilizes a case statement to populate values in a particular column in a table, based on values found in other columns within the same table. The existing update looks like this (object names and values have been changed to protect the innocent):
UPDATE dbo.target_table set target_column = case when source_column_1= 'ABC'then 'XYZ' when source_column_2= '123'then 'PDQ'
[Code] ....
The powers that be would like to replace this case statement with some sort of table-driven structure, so that the mapping rules defined above can be maintained in the database by the business owner, rather than having it embedded in code and thus requiring developer intervention to perform changes/additions to the rules.
The rules defined in the case statement are in a pre-defined sequence which reflects the order of precedence in which the rules are to be applied (in other words, if a matching value in source_column_1 is found, this trumps a conflicting matching value in source_column_2, etc). A case statement handles this nicely, of course, because the case statement will stop when it finds the first "hit" amongst the WHEN clauses, testing each in the order in which they are coded in the proc logic.
What I'm struggling with is how to replicate this using a lookup table of some sort and joins from the target table to the lookup to replace the above case statement. I'm thinking that I would need a lookup table that has column name/value pairings, with a sequence number on each row that designates the row's placement in the precedence hierarchy. I'd then join to the lookup table somehow based on column names and values and return the match with the lowest sequence number, or something to that effect.
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE( ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL, FirstName VARCHAR(50) NULL, LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded) VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName) VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded) SELECT FirstName, LastName, NULL FROM MYOTHERTABLE
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
I'm working on inserting data into a table in a database. The table has two separate triggers, one for insert and one for update (I don't like it this way, but that's how it's been for years). When there is a normal insert, done via a program, it looks like the triggers work fine. When I run an insert manually via a script, the first insert trigger will run, but the update trigger will fail. I narrowed down the issue to a root cause.
This root issue is due to both triggers using the same temporary table name. When the second trigger runs, there's an error stating that a few columns don't exist. I went to my test server and test db and changed the update trigger so that the temporary table is different than the insert trigger temporary table, the triggers work fine. The weird thing is that if the temporary table already exists, when the second trigger tries to create the temporary table, I would expect it to fail and say that it already exists.I'm probably just going to update the trigger tonight and change the temporary table name.
Hello,I am writing a stored procedure that will take data from severaldifferent tables and will combine the data into a single table for ourdata warehouse. It is mostly pretty straightforward stuff, but there isone issue that I am not sure how to handle.The resulting table has a column that is an ugly concatenation fromseveral columns in the source. I didn't design this and I can't huntdown and kill the person who did, so that option is out. Here is asimplified version of what I'm trying to do:CREATE TABLE Source (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,ssn CHAR(9) NOT NULL )GOALTER TABLE SourceADD CONSTRAINT PK_SourcePRIMARY KEY CLUSTERED (grp_id, mbr_id)GOCREATE TABLE Destination (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,member_ssn CHAR(9) NOT NULL,subscriber_ssn CHAR(9) NOT NULL )GOALTER TABLE DestinationADD CONSTRAINT PK_DestinationPRIMARY KEY CLUSTERED (grp_id, mbr_id)GOThe member_ssn is the ssn for the row being imported. Each member alsohas a subscriber (think of it as a parent-child kind of relationship)where the first 9 characters of the mbr_id (as a zero-padded string)match and the last two are "00". For example, given the followingmbr_id values:1234567890012345678901123456789021111111110022222222200They would have the following subscribers:mbr_id subscriber mbr_id12345678900 1234567890012345678901 1234567890012345678902 1234567890011111111100 1111111110022222222200 22222222200So, for the subscriber_ssn I need to find the subscriber using theabove rule and fill in that ssn.I have a couple of ideas on how I might do this, but I'm wondering ifanyone has tackled a similar situation and how you solved it.The current system does an insert with an additional column for thesubscriber mbr_id then it updates the table using that column to joinback to the source. I could also join the source to itself in the firstplace to fill it in without the extra update, but I'm not sure if theextra complexity of the insert statement would offset any gains fromputting it all into one statement. I plan to test that on Monday.Thanks for any ideas that you might have.-Tom.
Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end) scenario In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table. I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
Hi, Can anyone explain what the difference is and the advantages or disadvantages of using the below statements in my SQL paramaters please. Is there a performance hit if I use the second option? If myCustomer.Phone2 IsNot Nothing Then versus If myCustomer.Phone2.Length > 0 Then
Is there an easy way with DTS to pump data from one table to another so that it will update the row if it exists (the source and destination have the same value for the ID colum) or insert it if it doesn't. I know this can be done with stored procedures/sql by doing IF EXISTS UPDATE ELSE INSERT but there are many tables and columns and this will be very tiime consuming.
I have a page where the user can update stock records. it has 5 x 5 text boxes. If the user has already entered stock before that stock will show up and they can change it and clicking the button it will update, however if they have just entered new data i would assume they would need to insert it, so how do i go about doing this? do i need to use both insert and update in the same sql string?
I am developing an application for the marketing dept at my company. Basically users can build the content of an email to be sent to our subscriber database.
I am wanting the application to initailly save the content into a database, the update the most recently inserted row.
The save button uses the following SQL command: Dim SqlMethod As String = "INSERT INTO CZC_email (Offer, SendDate, Destinations, Copy, BannerURL) VALUES ('" & txtCampaignName.Text & "','" & calCampaignDate.SelectedDate.ToString("yy/dd/MM") & "','" & DestinationsSelected & "','" & FreeTextBox2.Text & "', '" & txtBannerPath.Text & "')SELECT @@IDENTITY AS 'CZ_ID'"
And my update button has this SQL command:
Dim SqlMethod As String = "UPDATE CZC_email SET SendDate = '" & calCampaignDate.SelectedDate.ToString("yy/dd/MM") & "', Offer = '" & txtCampaignName.Text & "',Destinations = '" & DestinationsSelected & "', BannerURL = '" & txtBannerPath.Text & "' WHERE CZ_ID = @@IDENTITY "
but it doesnt seem to be updating. anyone know what I'm doing wrong?
I have a GridView on a page, It contains data from 2 joined tables and a command column to Edit/Update. I want to update the data in only one of the tables. If the data exists in the table to be updated there seems to be no problem (obviously). But I get an error when trying to update a record that does not exist (I should think so). Is there a way of Inserting the record before the update is fired? I have tried to do an insert contained in a 'Try' in the GridView1_RowUpdating, but this does not seem to work. as I still get the same error. Object cannot be cast from DBNull to other types. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidCastException: Object cannot be cast from DBNull to other types. Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [InvalidCastException: Object cannot be cast from DBNull to other types.] System.DBNull.System.IConvertible.ToInt32(IFormatProvider provider) +54 System.Convert.ChangeType(Object value, TypeCode typeCode, IFormatProvider provider) +293 System.Web.UI.WebControls.SqlDataSourceView.AddParameters(DbCommand command, ParameterCollection reference, IDictionary parameters, IDictionary exclusionList, String oldValuesParameterFormatString) +577 System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +400 System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +78 System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1173 System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +1084 System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +88 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +117 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +83 System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +136 System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +172 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4839
Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating Try Dim eventid As Integer = CInt(GridView1.Rows(GridView1.EditIndex).Cells(7).ToString) Dim userid As Integer = CInt(hfID.Value) Dim guests As Integer = CInt(GridView1.Rows(GridView1.EditIndex).Cells(4).ToString) Dim attending As Boolean = CBool(GridView1.Rows(GridView1.EditIndex).Cells(5).ToString) Dim extra As String = Server.HtmlEncode(GridView1.Rows(GridView1.EditIndex).Cells(6).ToString) InsertRecord(eventid, userid, guests, attending, extra) Catch ex As Exception End Try End Sub
Function InsertRecord(ByVal eventID As Integer, ByVal userID As Integer, ByVal guests As Integer, ByVal attending As Boolean, ByVal extra As String) As Integer Dim connectionString As String = "server='localhost'; trusted_connection=true; Database='AFRA'" Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "INSERT INTO [AFRAAttendance] ([EventID], [UserID], [Guests], [Attending], [Extra]) VALUES (@EventID, @UserID, @Guests, @Attending, @Extra)" Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection) sqlCommand.Parameters.Add("@EventID", System.Data.SqlDbType.Int).Value = eventID sqlCommand.Parameters.Add("@UserID", System.Data.SqlDbType.Int).Value = userID sqlCommand.Parameters.Add("@Guests", System.Data.SqlDbType.Int).Value = guests sqlCommand.Parameters.Add("@Attending", System.Data.SqlDbType.Bit).Value = attending sqlCommand.Parameters.Add("@Extra", System.Data.SqlDbType.VarChar).Value = extra Dim rowsAffected As Integer = 0 sqlConnection.Open() Try rowsAffected = sqlCommand.ExecuteNonQuery Finally sqlConnection.Close() End Try Return rowsAffected End FunctionThank you
Is it possible to run a DTS package so that it just updates existing rows rather than a full on insert?
I have an HR db as a source and a health and safety db as destination. records of peoples H+S acheivments and assessments are maintained in the H+S db but i need to keep this topped up with the newly recruited staff details and the existing staff change of details. The DTS need to know to insert where its a new record and to update where its just a change of name or work location etc.
I have a form that pulls existing information from a database and allows users to edit it. Every field can be left blank if the user wishes. I am having an issue with determining which SQL statement to run. There has to be an easier way to do this then what I am doing. For each filed I process the data this way:
SQL2="select * from 1985ClassList where MailingListID =" & Session("EID") set aData = oConn.execute(SQL2)
SQL = "Update 1985ClassList SET "
if aData.Fields("FirstName") > " " and Request.QueryString("FirstName") > " " then SQL = SQL & "'FirstName' = '" & Request.QueryString("FirstName") & "', " end if
if aData.Fields("FirstName") = " " and Request.QueryString("FirstName") > " " then strFSQL = strFSQL & "FirstName, " strVSQL = strVSQL & Request.QueryString("FirstName") & ", " end if
Hi, Is it possible to Read data From a Table while Insertion/Update is happening on the table?? In case if this is not possible,Is there anyway to do that??
Tables: DETAIL --Here are all the detailed transactions Customer Item Amount ...MoreFields
TOTAL --Here are the accumulated values (1 record by Customer-Item Customer Item Accumulated ...MoreFields
What I want to know is the general logic to -Read all records from table DETAIL -If relationship Customer-Item exists in TOTAL table then UPDATE the TOTAL.Accumulated field adding the DETAIL.Amount field -If relationship Customer-Item doesn't exists in TOTAL table then INSERT a new ecord with the values from DETAIL table
Because of my experience in VB and ADO my first try is to use a Cursor and loop record by record
But I know that is not the only and better solution
May You post some hint or some sample querie on how could be done