Transact SQL :: How To Get Info Which Stored Procedure Updated A Column For Particular Timestamp

Jul 31, 2015

How to get the details of a stored proc or sql query which updated a particular table for specified time stamp or interval. Is there any query to get this?

View 3 Replies


ADVERTISEMENT

Is There A System Table With Timestamp Info Or DTS Job Info?

May 7, 2007

I want to be able to see when records have been added to a table. The issue is we have a DTS job scheduled to run every night. The developer who wrote it password protected it and doesn't work here anymore. I want to add a step to this series of DTS jobs and want to run it just prior to his job. Is there a way to see when the records are being added or when this job is being run? Thanks again, you guys are the best.

ddave

View 3 Replies View Related

Transact SQL :: Can Invoke Stored Procedure Stored Inside From User Defined Table Column?

Nov 5, 2015

Can I invoke stored procedure stored inside from a user defined table column?

View 5 Replies View Related

Transact SQL :: Create Table With Timestamp Column

Jul 2, 2015

Im trying to insert the values from this query into a table, so I can later check the history of memory usage:

SELECT
[total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB]
,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Page_File_MB]
,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB]
,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB]
,[system_memory_state_desc] AS [System_Memory_State_Desc]
FROM [master].[sys].[dm_os_sys_memory]

What I'm missing is a way to insert the current timestamp every time I insert to the table.My plan is to use the insert into command.

View 3 Replies View Related

Transact SQL :: Invalid Column Not Detected In Stored Procedure

Aug 17, 2015

When I create/alter a stored procedure I am expecting to get an error because one of the columns was renamed.

However, the stored procedure compiles without error.  Is this correct behavior?

Example:  Create a Test table.  Create stored procedure that reference table along with temp table.  The columns are not verified.  The stored procedure still compiles, even with the join containing bad column names.  Why is an error
not thrown?

Example:
create table Test (col1 int)
GO

create procedure TestSP
as
create table #Temp (col1 int)
select 1
from Test a
join Temp b
on a.bad = b.bad
-- Bad column names
GO

View 2 Replies View Related

Transact SQL :: Query To Display Avg Values For Each Timestamp And Count Of Timestamp

Jun 23, 2015

date        time         s-sitename TimeTaken(Seconds)
6/8/2015 10:56:26 TestSite 100
6/8/2015 10:56:26 TestSite 500
6/8/2015 10:56:26 TestSite 800
6/9/2015 11:56:26 TestSite 700
6/9/2015 11:56:26 TestSite 200
6/12/2015 12:56:26 TestSite 700

I have a table with above values, I am looking for a sql query to find AvgTimeTaken at different time stamps and total count of each time stamp

Output
date        time         s-sitename TimeTaken(Seconds) Count_of_Request
6/8/2015 10:56:26 TestSite 1400                  3
6/9/2015 11:56:26 TestSite 900                   2
6/12/2015 12:56:26 TestSite 700                   1

View 5 Replies View Related

Finding Last Updated With A Timestamp

Jul 20, 2005

Hi AllI know an SQL Server timestamp seems to be as useful as rocking horse forshow jumping, but I'm hoping you know a 'fudge' to get me round a problem orat least confirm that it isn't possible.I have 2 tables, one called ACCOUNTS and one called STOCK.These tables have the usual ints, varchars, etc and have a timestamp fieldas well.My end user wants to see a simple list of the details in these tables(individually - no joins present here), but sorted from most recentlyupdated to never touched.As the timestamp seems to update each time a transaction hits it I thoughthis would be perfect, but I've had the following mixed results:1) I did a simple ORDER BY TIMESTAMP DESC, but because the order isalphanumeric I don't get a true 'recent to old' list.2) So I did ORDER BY CAST(TIMESTAMP AS INT) DESC. There is a numeric sortnow, but the timestamp values seem to be hit and miss so that an accountthat I know should be near the top is around about the middle.Do you know how I can achieve the results I want?Is the timestamp a waste of time?ThanksRobbie

View 2 Replies View Related

Field Not Being Updated Within Stored Procedure

Jul 23, 2005

I have this stored procedure that loops through a table and updates acouple of fields. For some reason one of the fields is not beingupdated. If I run the same code from query analyzer, it works fine.Let me know if anyone can figure out why @lastscandate would ever beNULL. If it is null it should be equal to @maildate. The senerio thatseems to fail is when no records are returned from the select statementto fill in @lastscandate. This should then active the next ifstatement and set the @lastscandate equal to the @maildate. MailDateis always filled in in the database and LastScanDate will be NULL.Thanks for your help.DECLARE c1 CURSOR LOCAL FORSELECT m.id, m.acctno, m.ordid, m.cycle FROM master m WITH (nolock)WHERE m.printstatus IN ('ST', 'ML') AND (m.batchid IS NULL OR m.batchid= 0) AND (m.maildate ='' OR m.maildate IS NULL)AND NOT EXISTS(SELECT * FROM packagemaster p WITH (nolock)WHERE m.acctno = p.acctno AND m.ordid = p.ordid AND m.cycle = p.cycleAND p.status NOT IN ('BM', 'PM'))OPEN c1FETCH FROM c1 INTO @mid, @acctno, @ordid, @cycleWHILE @@fetch_status = 0BEGIN--Get MailDate from Manifest - if NULL then use GetDateset @maildate = NULLSELECT @maildate = MAX(whenmailed) FROM manifest WITH (nolock)WHERE acctno = @acctno AND ordid = @ordid AND cycle = @cycleif @maildate is NULLset @maildate = getdate()--Get Last Scan Date from Transactions - if NULL then use MailDateset @lastscandate = NULLselect @lastscandate=max(actiondate) from transactions whereacctno=@acctno and ordid=@ordid and cycle=@cycle and actionid=303if @lastscandate is NULLset @lastscandate = @maildateBEGIN TRANSACTIONUPDATE master SET printstatus = 'ML', maildate = @maildate,lastscandate=@lastscandateWHERE id = @midINSERT INTO transactions (initials, actionid, machinelogin, acctno,ordid, cycle, program) VALUES ('RLT', 55, 'Mars', @acctno, @ordid,@cycle, 'Update Mail Dates')COMMIT TRANSACTIONFETCH NEXT FROM c1 INTO @mid, @acctno, @ordid, @cycleENDCLOSE c1

View 1 Replies View Related

Update Row Using Stored Procedure With Timestamp

Oct 10, 2005

Hi,I'm attempting to update one row using strored procedure which check timestamp field.
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'tbCharakterystyki_Update')BEGINDROP Procedure tbCharakterystyki_UpdateENDGO
CREATE Procedure dbo.tbCharakterystyki_Update@CH_ID int ,@CH_CHARAKTERYSTYKA VARCHAR(30),@CH_OPIS VARCHAR(300),@CH_TIMESTAMP timestampAS
UPDATE tbCharakterystyki SET ch_charakterystyka = @CH_CHARAKTERYSTYKA,
ch_opis = @CH_OPIS
WHERE ch_id = @CH_ID AND ch_timestamp = @CH_TIMESTAMP
GO
Function which call stored procedure:public int CallSP(DataRow dr)    {      try       {             SqlConnection conn = new SqlConnection(this.strConnection);        SqlCommand cmd = new SqlCommand();                cmd.Connection = conn;         cmd.CommandType = CommandType.StoredProcedure;         cmd.CommandText = "dbo.tbCharakterystyki_Update";
        SqlParameter p1 = new SqlParameter();        p1.ParameterName = "@CH_ID";        p1.SqlDbType = SqlDbType.Int;        p1.Value = dr[0];
        SqlParameter p2 = new SqlParameter();        p2.ParameterName = "@CH_CHARAKTERYSTYKA";        p2.SqlDbType = SqlDbType.VarChar;        p2.Value = dr[1];
        SqlParameter p3 = new SqlParameter();        p3.ParameterName = "@CH_OPIS";        p3.SqlDbType = SqlDbType.VarChar;        p3.Value = dr[2];
        SqlParameter p4 = new SqlParameter();        p4.ParameterName = "@CH_TIMESTAMP";        p4.SqlDbType = SqlDbType.VarChar;        p4.Value = dr[3];
        cmd.Parameters.Add(p1);        cmd.Parameters.Add(p2);        cmd.Parameters.Add(p3);        cmd.Parameters.Add(p4);                cmd.Connection.Open();        cmd.ExecuteNonQuery();        cmd.Connection.Close();        return 0;          }      catch ( Exception err )       {         ShowError(err.Message)                return null;      }     }
And function which call "CallSP()":private void Update(){DataSet ds = helper.GiveDataSetCharakterystyki()DataRow dr = ds.Tables[0].Select("ch_id=51")[0];
dr[1] = "new value";CallSP(dr);}When I fire Update(), stored procedure (dbo.tbCharakterystyki_Update) is not started, Sql Profiler doesn't show it.Whilst when I modify stored procedure, removing WHERE ch_id = @CH_ID AND ch_timestamp = @CH_TIMESTAMP and put only WHERE ch_id = @CH_ID , it works perfectly.What I did wrong?
 

View 1 Replies View Related

How Can I Include A Timestamp On A File Name In A Stored Procedure???

Oct 24, 2007

ok...i give up.....
can this be done? (sql server 2000)
all i want to do is have my stored procedure output some data to a file and i want the filename to include a time stamp.
here's my current working file output code:
EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:employees.txt" -c -Usa -P'
i'd like it to be something like this:
EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:employees" + datetime + ".txt" -c -Usa -P'
but nothing seems to work.
 

View 16 Replies View Related

Trying To Add Info To Database Using A Stored Procedure

Jul 16, 2004

Hi there :)

I was hoping someone could help me figure out why this doesn't work....

I have a class called ProductsDB with a method called AddProduct that looks like this:


public String AddProduct(int categoryID, int makeID, string name, double price, double saleprice, double offerprice, int homepage, string thumbpath, string imagepath, string description)
{
// Create Instance of Connection and Command Object
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand myCommand = new SqlCommand("ProductsAdd", myConnection);

// Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure;

// Add Parameters to SPROC
SqlParameter parameterCategoryID = new SqlParameter("@CategoryID", SqlDbType.Int, 4);
parameterCategoryID.Value = categoryID;
myCommand.Parameters.Add(parameterCategoryID);

SqlParameter parameterMakeID = new SqlParameter("@MakeID", SqlDbType.Int, 4);
parameterMakeID.Value = makeID;
myCommand.Parameters.Add(parameterMakeID);

SqlParameter parameterName = new SqlParameter("@Name", SqlDbType.NVarChar, 50);
parameterName.Value = name;
myCommand.Parameters.Add(parameterName);

SqlParameter parameterPrice = new SqlParameter("@Price", SqlDbType.Money, 8);
parameterPrice.Value = price;
myCommand.Parameters.Add(parameterPrice);

SqlParameter parameterSalePrice = new SqlParameter("@SalePrice", SqlDbType.Money, 8);
parameterSalePrice.Value = saleprice;
myCommand.Parameters.Add(parameterSalePrice);

SqlParameter parameterOfferPrice = new SqlParameter("@OfferPrice", SqlDbType.Money, 8);
parameterOfferPrice.Value = offerprice;
myCommand.Parameters.Add(parameterOfferPrice);

SqlParameter parameterHomepage = new SqlParameter("@Homepage", SqlDbType.Bit, 1);
parameterHomepage.Value = homepage;
myCommand.Parameters.Add(parameterHomepage);

SqlParameter parameterThumbnail = new SqlParameter("@Thumbnail", SqlDbType.NVarChar, 50);
parameterThumbnail.Value = thumbpath;
myCommand.Parameters.Add(parameterThumbnail);

SqlParameter parameterImage = new SqlParameter("@Image", SqlDbType.NVarChar, 50);
parameterImage.Value = imagepath;
myCommand.Parameters.Add(parameterImage);

SqlParameter parameterProductID = new SqlParameter("@ProductID", SqlDbType.Int, 4);
parameterProductID.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterProductID);

try
{
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();

// Calculate the ProductID using Output Param from SPROC
int productId = (int)parameterProductID.Value;

return productId.ToString();
}
catch
{
return String.Empty;
}
}



And then in another cs file for my aspx page I have an event handler for an add button in a webform, which looks like this:


// Add New Product to ProductDB database
bikescene.Components.ProductsDB accountSystem = new bikescene.Components.ProductsDB();
String productId = accountSystem.AddProduct(Int32.Parse(DDLproductcategory.SelectedValue), Int32.Parse(DDLproductmanufacturer.SelectedValue), TBproductname.Text, Double.Parse(TBproductprice.Text), Double.Parse(TBproductsaleprice.Text), Double.Parse(TBproductofferprice.Text), Int32.Parse(DDLproducthomepage.SelectedValue), thumbnailpath, imagepath, TBproductdescription.Text);


And finally, here is my stored procedure:


CREATE Procedure ProductsAdd
(
@ProductID int,
@CategoryID int,
@MakeID int,
@Name nvarchar(50),
@Price money,
@SalePrice money,
@OfferPrice money,
@Homepage int,
@Thumbnail nvarchar(50),
@Image nvarchar(50),
@Description nvarchar(500)
)
AS

INSERT INTO Products
(
productID,
categoryID,
makeID,
name,
price,
saleprice,
offerprice,
homepage,
thumbpath,
imagepath,
description
)
VALUES
(
@ProductID,
@CategoryID,
@MakeID,
@Name,
@Price,
@SalePrice,
@OfferPrice,
@Homepage,
@Thumbnail,
@Image,
@Description
)

SELECT
@ProductID = @@Identity
GO


Basically, my images are uploaded to the server just fine but nothing is being added to the database + I don't get any error messages.

Any ideas?

Many thanks indeed :)

View 1 Replies View Related

SQL Stored Procedure Parameters Info C++ Clr 2.0

Feb 16, 2006

How does one get the names and data types of the parameters of a SQL 2005 stored procedure? There does not seem to be a refresh for a command parameters object, and the syntax for the get() in I don't seem to have right in c++.

View 1 Replies View Related

A SQL Procedure To STOP Repeated Info Being Stored

Feb 4, 2007

In the web site that I am building ( in C# language ), a hypothetic customer who would buy something would then be redirected to a Secure payments company where he would make the payment and then the company would send back to my web site, information about this transaction.
 
My program would then save this info in a Microsoft SQL database. The problem is that this company uses to send the same info several times repeatedly and I do not want to store the same info more than once.
 
So I want a SQL procedure where it takes the invoice number of the customer ( contained in its string of info ) and looks inside a table to see if it was already stored there. If it is there ( or not ), it would return a value, which could be false/true or 0/1 so my program could use this value to save a new info or not and then activate ( or not ) some related tasks.
 
I am still learning SQL and I tried the below procedure but it is not working. Which alternative procedure could solve the problem ?
 
~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE PROCEDURE VerifyIfInvoiceExists
(@Invoice VARCHAR(50))
AS
SELECT COUNT(*) FROM IPN_received
WHERE Invoice = @Invoice
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

View 5 Replies View Related

Stored Procedure Returning Info About A Table

Sep 28, 2007

i want to have a user defined function or stored procedure(i don't know which one should be used, anyway!) which returns false if the table has no records, with the table name comes with a parameter.

could you give a little code sample?

thanx

View 4 Replies View Related

More Info: Stored Procedure Security Question

Jul 20, 2005

Dear GroupI have found that table A had SELECT permissions for 'Public' but not tableB.Giving 'Public' SELECT permissions on table B did the trick.HOWEVER, I don't want anyone to be able to do a direct SELECT on table A orB but only give them access to the data by using the stored procedures. Isthere any way this can be set up?Thanks for your efforts!Have a nice day!Martin"Martin Feuersteiner" <theintrepidfox@hotmail.com> wrote in message news:...[color=blue]> Dear Group>> I'm having two stored procedures, sp_a and sp_b>> Content of stored procedure A:> CREATE PROCEDURE dbo.sp_a> SELECT * FROM a> GO>> Content of stored procedure B:> CREATE PROCEDURE dbo.sp_b> SELECT * FROM b> GO>> I have created a user that has execute permissions for both procedures.> When I run procedure A, all works fine but when running procedure B I'm> getting an error saying that the user must have SELECT permissions on> table B.>> Both tables are owned by dbo, and the security role for the user doesn't> has any SELECT permission on table a and b.> I'd be grateful if anyone could point me in a direction why this error> might come up for procedure B but not for A,> with a possible solution without giving the user SELECT permissions.>> Thanks very much for your help!>> Martin>[/color]

View 6 Replies View Related

Integration Services :: Log Inserted And Updated Count Done By Stored Procedure

Sep 8, 2015

I am run a stored procedure using Execute SQL task in, I want to log information of number of record inserted update in my table. I want to enable SSIS logging after from where I get information number of record inserted update.

I am using SQL server 2008R2 standard edition.

View 4 Replies View Related

Timestamp - Automatically Update Date And Time When Rows Updated?

Apr 7, 2014

I know 2008 MS SQL Server has a timestamp data type that adds date and time when the rows are inserted. Is there a way to automatically update the date and time when the rows are updated?

View 5 Replies View Related

SQL Server 2008 :: Trigger To Show Which Stored Procedure Has Updated A Table

Jul 9, 2015

I am looking to created a trigger that inserts records into a log table to show the stored porcedure that has updated a specific column to a specific value in a specific table

At present I have:

CREATE TRIGGER [dbo].[trUpdaterTable]
ON [dbo].[t_account]-- A DB level trigger
FOR UPDATE
--Event we want to capture
AS
IF update (document_status)

[Code] ...

However this doesn't appear to bring back the procedure that triggered the update...

The value the trigger should update on is document_status = 0

DDLProcExecutedByEventDate
NULLNULLLOMBDAadministrator2015-06-25 07:42:01.677
NULLNULLLOMBDA im64602015-06-25 07:51:34.503
NULLNULLLOMBDAadministrator2015-06-25 07:52:01.610
NULLNULLLOMBDAadministrator2015-06-25 08:02:01.417
CREATE TRIGGER [dbo].[trTableupdateaccount] ON [DoesMore].[dbo].[t_account]

[Code] ....

View 9 Replies View Related

Using Triggers To Get Recently Updated Info In SQL Server 2000

Sep 30, 2004

Hello all,

I am trying to change values of two tables in my sql server 2000 database. When one of the tables is modified in some way like adding/updating a record, I need a trigger procedure to copy this new data from the first table to the second. Problem is, how do I get the newly inserted or updated data from the first table? How do I specify that I only want the data which caused the trigger to execute? Anyone know?

Thanks,
Bob

View 1 Replies View Related

Get Row Timestamp With No Timestamp Column

Jul 10, 2007

For starters, please feel free to move this if it is in the wrong forum.



The issue I have is this. I have been asked to delete all information from a table that was inserted before May 12 this year. The issue is that when the DB was created, whoever designedd it neglected to add a timestamp column for the user data table (the one I need to purge). Does SQL, by default, happen to store insert times? Would it be something that might hide ina log file somewhere?

View 4 Replies View Related

Transact SQL :: DML Within Stored Procedure

May 7, 2015

How we can get to know what DML action(INSERT,UPDATE,DELETE) is present inside a Stored procedure, and i want to know if we have any specific columns are present in system tables or system related dynamic views which gives this info. I am aware of the SQL profiler, but in my scenario i want to use it through system tables or system related dynamic views.

View 7 Replies View Related

Transact SQL :: DMV For Stored Procedure

Jun 26, 2015

Why i cannot see my stored procedure in the result of 

select * from sys.dm_exec_procedure_stats

SQL Server DBA

View 12 Replies View Related

Transact SQL :: How To Sum Up Overlapping Timestamp Without Cursors

Oct 20, 2015

[URL] ...

So What I am trying to accomplish is sum up overlapping time ranges while also keeping Unique data rows within the table. So If a data row is unique meaning it is NOT within a overlapping group of data rows then I want to just insert it into a "final table", but if the data rows are overlapping then I want to grab the min(timestart) and the max(timestop) and the PKID of the data row with the max(timestop) within the overlapping group. 

I accomplish this task using nested cursors, however When I place my method into a trigger which runs on INSERT, then my trigger never runs and nothing happens. Is there a way to accomplish this without using cursors.  I have placed my cursor method into the sql fiddle to be inspected.

create table temp1
( pkid int,
line int,
dateentry date,
timestart time,
timestop time
)

[Code] ....

DESIRED RESULTS:

1060
1
2015-10-01
16:30:00
17:00:00
30
NULL

[Code] .....

View 11 Replies View Related

Transact SQL :: Round Off Timestamp In DateTime To 11:00 Or 23:00

Oct 5, 2015

I have a datetime stamp and I want to round off the the time to 11:00 if the timestamp is between 5AM and 5PM. If it is beyond then round off to 23:00. I don't use date I only use timestamp in my query so I just want to round off the timestamp. Is there is function to do that.

View 3 Replies View Related

Transact SQL :: Decrypt Timestamp Datatype

Jun 23, 2015

I have column which has timestamp datatype.in this column inserting date in encrypt format.

I want insert date format into that column.If it is not possible to insert date format while fetching (through select statement) want to decrypt format ( I mean date format).

View 6 Replies View Related

Transact SQL :: Stored Procedure To Return Results

May 27, 2015

How can I return results from this SP?

Alter
Procedure  sp_Blocking
as
      SET NOCOUNT
ON
truncate
table blocked

[Code] ....

View 4 Replies View Related

Transact SQL :: Stored Procedure Executed At The Server?

Nov 5, 2015

I could identify the last or all stored procedure, which was performed on a database, +/- something similar to what the profile of sql server identifies as below ?

View 2 Replies View Related

Transact SQL :: Call Cobol From Stored Procedure?

Jun 17, 2015

In DB2 stored procedures are calling external cobol code/program, I want convert DB2 stored procedure to MS SQL stored procedure, on that case, How to call call cobol from MS SQL stored procedure.

View 7 Replies View Related

Transact SQL :: How To Create Stored Procedure Using Agent Job

May 8, 2015

I have 3rd party application implementation I am doing, if I have stored procedure in one database, can it be copied to other database (newly created) on the same server? I will have the name of old and new database when this will be copied. I do need to automate this copy.

View 10 Replies View Related

Transact SQL :: Stored Procedure Return Values

Sep 23, 2015

If I create a stored procedure and do not specify a return value or type, why does SSMS show that the stored procedure returns an int in the object explorer?  Is that simply the success flag?

View 5 Replies View Related

Transact SQL :: Delta Process In Stored Procedure

Apr 30, 2015

Currently through my stored procedure, I am pulling the data from my source systems in the way Daily Truncate and loading the data in to my database table. Currently my query looks as follows,

SELECT
     a.col1,a.col2,b.col3,b.col4,c.col5
FROM
a
left outer join b ON a.col1 = b.col1
left outer join c ON b.col1 = c.col1.

Now, I want to change the above one to delta process(I have updateddate field in all the tables). The requirement is any of these 3 tables has updateddate = getdate() then i need to take "a.col1,a.col2,b.col3,b.col4,c.col5" columns and push to destination
table.

I think usually delta process means we will use to consider the key table, in that table if there is an updated record will take that record and join with the other required tables to pull the updated record. But these logic seems different.how to build the query for the above delta process? Since it is an urgent requirement need to implement asap.

View 9 Replies View Related

Transact SQL :: Difference Between Batch And Stored Procedure?

Jun 19, 2015

What is the difference between Batch and Stored Procedure?

View 5 Replies View Related

Transact SQL :: How To Run Stored Procedure In Another Database On Different Server

Jul 13, 2015

I linked MS SQL and MySQL and i would like to have insert trigger on MySQL to import certain fields every time when there's new entry.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved