Question is pretty much in the thread title.
I'm still plunking away on a history trigger that requires the evaluation of whether or not each column has been updated. If it has,t hen I'll be tossing a record into a history table.
Basically I need something to function along the same logic as this:
IF UPDATED(@s)
BEGIN
--some code
END
Any thoughts you folks may have are greatly appreciated.
is there a way to do this in create/alter table without the overhead of a trigger? I want to ensure that one of the 'key' values (even though it may be duplicated) cannot change after it's been inserted.
View 4 Replies View RelatedHey folks, the question is fairly simple, unfortunately the answer has proven rather elusive.
Is it possible to declare a variable which would then be used to identify either a column or table in an SQL statement?
Here's a basic idea of what I'd like to do:
DECLARE @myVar AS NVARCHAR(50)
SELECT *
FROM @myVar
or
DECLARE @myVar AS NVARCHAR(50)
SELECT @myVar
FROM MyTable
I'm probably looking for some sort of built in function that will accept an argument here... like COLUMN(@myVar) or something of the like. I just don't know where to look...
I have a stored procedure that contains a paramteter of type nvarchar(max). What is the syntax to pass this parameter to the sp from a VB.Net application. Specifically, I need to know what to put in the 3rd parameter below:
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, , Name)
Problem about pass a big string (over 8000 characters) to a variable nvarchar(max) in stored procedure in SQL 2005!
I know that SQL 2005 define a new field nvarchar(max) which can stored 2G size string.
I have made a stored procedure Hellocw_ImportBookmark, but when I pass a big string to @Insertcontent , the stored procedure can't be launch! why?
create procedure Hellocw_ImportBookmark @userId varchar(80), @FolderId varchar(80), @Insertcontent nvarchar(max)
as declare @contentsql nvarchar(max); set @contentsql=N'update cw_bookmark set Bookmark.modify(''declare namespace x="http://www.hellocw.com/onlinebookmark"; insert '+ @Insertcontent+' as last into (//x:Folder[@Id="'+@FolderId+'"])[1]'') where userId='''+@userID+''''; exec sp_executesql @contentsql;
Problem about pass a big string (over 8000 characters) to a variable nvarchar(max) in stored procedure in SQL 2005!
I know that SQL 2005 define a new field nvarchar(max) which can stored 2G size string.
I have made a stored procedure Hellocw_ImportBookmark, but when I pass a big string to @Insertcontent , the stored procedure can't be launch! why?
----------------------13-------------------------------------
create procedure Hellocw_ImportBookmark
@userId varchar(80),
@FolderId varchar(80),
@Insertcontent nvarchar(max)
as
declare @contentsql nvarchar(max);
set @contentsql=N'update cw_bookmark set Bookmark.modify(''declare namespace x="http://www.hellocw.com/onlinebookmark"; insert '+
@Insertcontent+' as last into (//x:Folder[@Id="'+@FolderId+'"])[1]'') where userId='''+@userID+'''';
exec sp_executesql @contentsql;
I want to update a field with a trigger only if a specific field is updated.
When I try the code below, it updates the field when any field in the record is updated. Is there a way to only make look at picked_dt?
ALTER TRIGGER [dbo].[UpdatePickedDate]
on [dbo].[oeordlin_sql]
after update
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[Code] .....
Hi,I want to save the last modification date when the row is updated. I have a column called "LastModification" in the table, every time the row is update I want to set the value of this column to the current date. So far all I know is that I need to use a trigger and the GetDate() function, but could any body help me with how to set the value of the column to getdate()? thanks for your help.
View 3 Replies View RelatedHI, I am running the below method which returns this error: The parameterized query '(@contactdate nvarchar(4000),@dnbnumber nvarchar(4000),@prospect' expects the parameter '@futureopportunity', which was not supplied" Please help.Private Shared Sub InsertData(ByVal sourceTable As System.Data.DataTable, ByVal destConnection As SqlConnection)
' old method: Lots of INSERT statements Dim rowscopied As Integer = 0
' first, create the insert command that we will call over and over:
destConnection.Open()Using ins As New SqlCommand("INSERT INTO [tblAppointmentDisposition] ([contactdate], [dnbnumber], [prospectname], [businessofficer], [phonemeeting], [followupcalldate2], [phonemeetingappt], [followupcalldate3], [appointmentdate], [appointmentlocation], [appointmentkept], [applicationgenerated], [applicationgenerated2], [applicationgenerated3], [comments], [newaccount], [futureopportunity]) VALUES (@contactdate, @dnbnumber, @prospectname, @businessofficer, @phonemeeting, @followupcalldate2, @phonemeetingappt, @followupcalldate3, @appointmentdate, @appointmentlocation, @appointmentkept, @applicationgenerated, @applicationgenerated2, @applicationgenerated3, @comments, @newaccount, @futureopportunity)", destConnection)
ins.CommandType = CommandType.Textins.Parameters.Add("@contactdate", SqlDbType.NVarChar)
ins.Parameters.Add("@dnbnumber", SqlDbType.NVarChar)ins.Parameters.Add("@prospectname", SqlDbType.Text)
ins.Parameters.Add("@businessofficer", SqlDbType.NChar)ins.Parameters.Add("@phonemeeting", SqlDbType.NVarChar)
ins.Parameters.Add("@followupcalldate2", SqlDbType.NVarChar)ins.Parameters.Add("@phonemeetingappt", SqlDbType.NVarChar)
ins.Parameters.Add("@followupcalldate3", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentdate", SqlDbType.NVarChar)
ins.Parameters.Add("@appointmentlocation", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentkept", SqlDbType.NVarChar)
ins.Parameters.Add("@applicationgenerated", SqlDbType.NVarChar)ins.Parameters.Add("@applicationgenerated2", SqlDbType.NVarChar)
ins.Parameters.Add("@applicationgenerated3", SqlDbType.NVarChar)ins.Parameters.Add("@comments", SqlDbType.Text)
ins.Parameters.Add("@newaccount", SqlDbType.NVarChar)ins.Parameters.Add("@futureopportunity", SqlDbType.NVarChar)
' and now, do the work: For Each r As DataRow In sourceTable.RowsFor i As Integer = 0 To 15
ins.Parameters(i).Value = r(i)
Next
ins.ExecuteNonQuery()
'If System.Threading.Interlocked.Increment(rowscopied) Mod 10000 = 0 Then
'Console.WriteLine("-- copied {0} rows.", rowscopied)
'End If
Next
End Using
destConnection.Close()
End Sub
I had this question for quite a long time.
It seems the latter one don't take any extra storage space than the previous one.
As long as the real string length is less than 10.
Is that mean the latter one not cost anything?
I once heard the different is when they are in memory. But not sure of it.
Can anyone explain it and provide some official reference on it?
Thank.
I need to select specific values from all rows where the value of a specific column is "Active"
This part works: SELECT LastName, FirstName, MiddleInit, ClientId FROM dbo.Client
But I want to add: WHERE StatusType = (Active) and how to do this.
We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?
I want to ship 500,000 aged transactions each night to an archive table and delete them from their source table in one or more logical units of work (LUW). Each row is approx 60 bytes and there is only one non clustered index on the source table presently.
I'm trying to weigh the pros and cons of 3 alternatives. One of them would basically insert the non-aged rows into tempdb, ship the aged records, truncate the table and then insert the tempdb records back into their source all in the same LUW.
For this alternative, I'd at least like to turn off logging when the records get inserted into tempdb as I dont see any value in logging that part of the activity. Is this possible?
Hi All,
Could you guys please help me with printing reports invoked thru command line/ URL access to print automatically to specific printers and specific trays and also is it possible to set the specific printer and tray as parameters.
Any suggestions is appreciated
Thanks A lot in advance
e,g :
http://localhost/reportserver?/testreports/employee sales&UserID='ABC'&LName=Lastname='victor'&rs:Command=Render
Hi
We already used Oracle Datasatage Server the following Query statement for Source and Lookup.here there is parameter maping in the SQl Statement . How can achive in SSIS the Folowing Querystatment?
Query 1: (source View Query)
SELECT
V_RDP_GOLD_PRICE.GDR_PRODUCT_ID, V_RDP_GOLD_PRICE.ASSET_TYPE, V_RDP_GOLD_PRICE.PREFERENCE_SEQ, V_RDP_GOLD_PRICE.RDP_PRICE_SOURCE, TO_CHAR(V_RDP_GOLD_PRICE.PRICE_DATE_TIME,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(V_RDP_GOLD_PRICE.REPORT_DATE,'YYYY-MM-DD HH24:MI:SS'), V_RDP_GOLD_PRICE.SOURCE_SYSTEM_ID
FROM
V_RDP_GOLD_PRICE V_RDP_GOLD_PRICE
WHERE
REPORT_DATE = (select max(report_date) from V_RDP_GOLD_PRICE where source_system_id = 'RM' )
Query 2: (look up )
SELECT
GDR_PRODUCT_ID,
TO_CHAR(MAX(PRICE_DATE_TIME),'YYYY-MM-DD HH24:MI:SS') ,
TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')
FROM
V_RDP_GOLD_PRICE
where
GDR_PRODUCT_ID = :1 and
report_date = TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') AND
PRICE_DATE_TIME BETWEEN TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') - 7) AND TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS')
GROUP BY GDR_PRODUCT_ID, TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')
please anyone give the sample control flow and how to pass the parameter?
Thanks & regards
Jeyakumar.M
Hi there !
Thanks for taking the time to read this thread.
I don't know whether anyone has this problem, but I am definitely not using the right keywords to search for a thread.
My situation is this...
I have a dataset that has values to fill cells to multiple tables in a report.
However, I only want to select specific data from the dataset to fill textboxes and others.
I cannot change the stored procedure, but the sample of the data is shown below:-
Row Stat Val
0 dtRpt1 02/01/2005
1 Value1 1
2 Value2 2000
3 dtMailSent 02/28/2005
4 Value3 0
5 Value4 5
6 Value5 658
I know it looks weird, but the row really represents which "row" or textbox is it to fill with the Val. The Stat Column is just a way to make sure that I am filling the right values.
so my new report would have multiple tables to denote different categories.
In my first table, I tried putting the cells as follows:-
(expressions are highlighted in italics and bold)
TextBox1 =IIF(Fields!Row.Value =0, Fields!Val.Value,"")
Table1
Column1
DetailRow1 =IIF(Fields!Row.Value =1, Fields!Val.Value,"")
DetailRow2 =IIF(Fields!Row.Value =2, Fields!Val.Value,"")
Table2
Column1
DetailRow1 =IIF(Fields!Row.Value =3, Fields!Val.Value,"")
DetailRow2 =IIF(Fields!Row.Value =4, Fields!Val.Value,"")
DetailRow3 =IIF(Fields!Row.Value =5, Fields!Val.Value,"")
DetailRow4 =IIF(Fields!Row.Value =6, Fields!Val.Value,"")
I only expect this report to print out one page holding the previous values.
However, it ended up printing like this
----------------------------------------------------------
Table1
Column1
DetailRow1 1
DetailRow2
Column1
DetailRow1
DetailRow2 2000
Table2
Column1
DetailRow1 02/28/2005
DetailRow2
DetailRow3
DetailRow4
Table2
Column1
DetailRow1
DetailRow2 0
DetailRow3
DetailRow4
Table2
Column1
DetailRow1
DetailRow2
DetailRow3 5
DetailRow4
Table2
Column1
DetailRow1
DetailRow2
DetailRow3
DetailRow4 658
------------------------------------------------------
I tried putting it into the headerrows instead of DetailRows, and it ended up printing the last value.
Is there anyway to do this ? print all the values out in one table ? I tried using textboxes, but I think I got my expression wrong.
Is this the correct expression ?
=IIF((Fields!Row.Value,"Dataset") =1, (Fields!Val.value, "Dataset"), "")
and it give me an error
The value expression for the textbox €˜textbox5€™ contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.
Appreciate any advice or suggestion for this scenario !
Thanks!
Bernard
I am very early on in developing a website to track issues with projects which is tied to a SQL database. I have my Projects Table, my Users Table, and am creating a third table to track issues. I'm wondering what is the best way to assign specific users to specific data/projects. The user should only be able to view & update the projects assigned to him. He should not be able to see other projects. What is the best way to assign projects/data to the users to make sure they are only viewing their data?
View 1 Replies View RelatedHi Im doing a simple update in my SP: update users set Name = @Name where id=@userID and password=@password i want to know if a row gets updated. for example if the userID and password dont match then the row will not get updated.so i want some way to tell if a row has been updated. how do i do this?thanks
View 3 Replies View RelatedHi everybody,
I am a total noob conserning ASP, but I am willing to learn
We have a sql2005 SRV(hosted by our ISP, so limited access) and a ASP based forum (WEB WIZ)
When I try to login I get this error: Support Error Code:- err_SQLServer_loginUser()_update_USR_CodeFile Name:- functions_login.aspError details:-Microsoft OLE DB Provider for ODBC DriversQuery cannot be updated because the FROM clause is not a single simple table name.Can somebody tell me whats wrong?
Thanx in advance.
Gerry de Bruijn!
Hi -
apologies if this is not the right forum for this - I've searched a bit and this seems to be the best fit.
I have the following problem: I want to update records in a table that fit certain criteria. The way the insert logic works make sure that there will always be only one record that fits the criteria and I'd like to get the ID value of that record once the update went through. So here is what I tried:
1 UPDATE Timeslot
2 SET StartTime = @StartTime, EndTime = @EndTime
3 WHERE (ProfessionalID = @ProfessionalID) AND (ProviderLocationID = @ProviderLocationID) AND (RequestID IS NULL) AND (StartTime > @StartTime) AND
4 (EndTime < @EndTime);
5 SELECT SCOPE_IDENTITY()
My hope was that the select scope_identity would return the Timeslot ID of the row that was affected, but it doesn't. How do I get that row?
Thanks!!!
Oliver
is there a such thing as a "UPDATED table".. as in a trigger's INSERTED table or DELETED table ....
View 1 Replies View RelatedHow can I determine who and when a table in a db was updated, I dont need this per record, I only need to know the last time an update was made to a table. Thanks.
View 1 Replies View RelatedIs there a way to tell when a file was last updated?
View 1 Replies View RelatedIf I update a recordset a group of records using dynamic SQL where I update the TOP n records, is it possible to get the set of records that was updated?
CREATE PROCEDURE usp_Structural_ScheduleComponent
@cProject char(7),
@cComponentID char(10),
@iPour int,
@iQuantity int,
@iAvailable int OUTPUT,
@dtCast datetime OUTPUT
AS
SET @dtCast = convert(char(10), getdate(), 120)
DECLARE @cSql varchar(500)
SET @cSql = 'UPDATE tbStructuralComponentSchedule SET PourNumber = ' + CAST (@iPour AS VARCHAR) + ', ScheduledDate = ' + '''' + CAST(@dtCast AS VARCHAR) + '''' +
' WHERE EntryID IN ( SELECT TOP ' + CAST(@iQuantity AS VARCHAR) +
' FROM tbStructuralComponentSchedule ' +
' WHERE fkProjectNumber = ' + '''' + @cProject + '''' +
' AND fkComponentID = ' + '''' + @cComponentID + '''' +
' AND IssueDate IS NOT NULL' +
' AND ScheduledDate IS NULL' +
' ORDER BY EntryID DESC)'
EXEC(@cSql)
IF(@@ERROR <> 0 OR @@ROWCOUNT < = 0)
RAISERROR('Failed to add components to pour!',16,1)
SELECT @iAvailable = SUM(CASE WHEN IssueDate IS NOT NULL AND ScheduledDate IS NULL THEN 1 ELSE 0 END)
FROM tbStructuralComponentSchedule WHERE fkProjectNumber = @cProject AND fkComponentID = @cComponentID
GO
-- Is there a way to return the recordset that were modified in the update?
Mike B
Hi,
I am looking for a function (or something else) that shows me, when a row in MS SQL Server was updated the last time. is it possible?
thanks
deviant69
Hi,
Can anybody tell me how to find the column name(s) for recently updated table.
Thanks,
Kishore
Hi,
We run SQL SERVER 2000 on win2000
I've a question regarding Index on a table.
I've a table (1 milj rows) with a extra index ZINDEX1.
Now lets say I insert 1000 rows in that table.
Now what will happend if I search on these NEW rows in my table.
The Index is not updated.
//Martin
We have a third party process that runs and updated several SQL tables.
Is there any way to find out what tables are being updated and store it in another table?
Guys,
Is there a way to find out when a certain DB object (e.g. Stored procedure) was last modified?
SYSOBJECTS table contains crdate and refdate fields. None of these, however, appear to tell me when when the object was last updated.
Any suggestions?
Thanks a lot
Is there a command that will tell me the number of rows that are updated in a statement. I would like to put this in an Stored Procedure and pass the #rows updated back out.
View 1 Replies View RelatedI have a table written in to sql by the program Wincc flex wich is currently constantly saving data in to it. I want to reed this data back in to Labview and update it’s content on Labview constantly to plot a graph that keeps updating over time.
I have achieved this after some research but my problem is that I need a way of just reading the last row (meaning most recent row, not last row as the bottom one) inserted in to the table written by wincc and saved in sql WITHOUT having to read all the data and selecting for example the higest value of a timestamp column (which is added by wincc) because this would cause too much lag since the table has a lot of elements and I need to constantly read it. One of the main problems is that the data written by wincc is written in a circular buffer, which does delete 20% of the data when it fill’s up and start writing new data in to the free space, so that the temporal order of the data does nothing to do (at least nothing simple) with the index order in the table. Is there anything like a method to save the index of the last row modified in the table so that labview could read it and directly access the most recent recordset in the table just by index specification on a query?
I am writing a generic trigger in VS 2005 that selects records from the inserted table, and updates an audit table. I am, however, unable to retrieve the name of the table that the insert occurred on. I am using the following code to select the records, and obtain the name.. Can anyone offer any alternatives to accomplishing this task? Thanks in advnace for any help you can provide.
Craig
SqlDataAdapter tableLoader = new SqlDataAdapter("SELECT * FROM inserted", connection);
DataTable insertedTable = new DataTable();
tableLoader.Fill(insertedTable);
string insertedTableName = insertedTable.TableName;
I am trying to insert data to a simple table using SQL express
However , I can see the data saved in the DataSet but not in the actual database.
If I try to insert the same user again , I got primary key violation ???
Here is the insert function :
string SqlConnection = @"Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|QJ.mdf;Integrated Security=True;User Instance=True";
SqlConnection Conn = new SqlConnection(SqlConnection);
SqlDataAdapter DataAdapter = new SqlDataAdapter("SELECT * FROM USERS", Conn);
SqlCommandBuilder ComandBuilder = new SqlCommandBuilder(DataAdapter);
DataAdapter.InsertCommand = ComandBuilder.GetInsertCommand();
DataAdapter.UpdateCommand = ComandBuilder.GetUpdateCommand();
DataSet DS = new DataSet();
DataAdapter.Fill(DS, "USERS");
DataTable DT = DS.Tables["USERS"];
DataRow DR = DT.NewRow();
DR["NAME"] = txtUserName.Text;
DR["Password"] = txtPass.Text;
DR["Type"] = 1;
DT.Rows.Add(DR);
DataAdapter.Update(DS,"USERS");
DS.AcceptChanges();
Conn.Close();
I dont know what I have done wrong
Please help
Thanks very much