DB Engine :: Stored Procedure Change History Information?

Jun 5, 2015

one of my SQL Developer member had one observation that, size of the parameter 'Parameter_XYZ' in certain stored procedure had changed from 25 to 255 during some production fixes, however suddenly its looks like that, someone has changed it back to 25 instead of 255.

DECLARE @Parameter_XYZ
varchar(25);

Can we figure out in which sprint/drop the stored procedure was changed and the Parameter_XYZ back to 25. Can any log recovery mechanism will get such details.

Can we get stored procedure text between different alteration.

View 4 Replies


ADVERTISEMENT

Complex Stored Procedure On History Table

Jun 7, 2006

Hi All,

I have a table that hold status history records for cases. In this table is a status field with values, opened, assigned, or complete. Each case can be assigned a number of times before it is complete, and can be reassigned. I have the need to run a query that will get each case that is still assigned, and not yet complete. I wrote a stored procedure that contains a cursor containing each case, and get the last status history record for each case and puts it into a temp table to return to the user, but is hurting performance as there are .5 million records here. Does anyone know of a better way of doing this?



Thanks in advance : )

View 1 Replies View Related

Stored Procedure Information

Aug 8, 2005

Hi all,I'm trying to get information about a given Stored Procedure (SP) but am able (until now) to get to extract only the name and columns either using T-SQL or SQLDMO. I want to extract information like which Tables the SP accesses, and also which Fields. This however doesn't seem to be available through T-SQL nor SQLDMO.I haven't been able to find any information yet, and am thinking that my only way to work this out is to design my own parser, which I've done to some extent - it extracts the tables and fields of every query inside the SP, but it gets a bit messy when the SP gets complicated, even when using regular expressions.I'm sure SQL must know a way to find out about these objects, since it manages to compile SP - so where does it maintain or (even better) expose information about the tables and fields each SP uses?Any help or pointers would be very very helpful!Regards,Pieter

View 1 Replies View Related

Incorrect Information In SQL AgentOperator History

Jun 21, 2007

Apologies if this is in the wrong forum.



I've set up an email alert to an Operator who can only be alerted via email. When i view the history of this operator, it has a "most recent notification attempt" value against by net send and not against by e-mail. The value is the correct date of the latest email alert, but is just set against the wrong notification type.


Not a major bug for me, but does anyone else have this problem?



--------------------------------------------------------------------------

DDL statement for operator.



EXEC msdb.dbo.sp_add_operator @name=N'Rich',

@enabled=1,

@weekday_pager_start_time=90000,

@weekday_pager_end_time=180000,

@saturday_pager_start_time=90000,

@saturday_pager_end_time=180000,

@sunday_pager_start_time=90000,

@sunday_pager_end_time=180000,

@pager_days=0,

@email_address=N'rich@rich.net,

@category_name=N'[Uncategorized]'

View 3 Replies View Related

Get Table Information With Stored Procedure?

Apr 6, 2008

Hi,

I have a database with two handred tables.I want to get a list that have table name and numbers of record in each table.

Would you please give me a idea if you know how to do this with query or build-in stored procedure.

Thanks a lot

Mark

View 1 Replies View Related

How To Get Extended Error Information From Stored Procedure

Oct 3, 2007

Hi
I have a stored procedure in SQL server 2005. It works fine when I execute it from the Management Studio.But when executing it from ASP.NET code like this:
..... Of course more code is executed before this call .....int retVal = this.odbcCreateDataBaseCommand.ExecuteNonQuery();
retVal is -1. But -1 doesn't really tell me what the problem is?
Is there anyway to get extended error information so I can figure out whats going wrong?
(The stored procedure was working fine in SQL server 2000 before I upgraded to SQL server 2005. I use .NET Framework 1.1 and ODBC Sql Native Client to access the 2005 server.)
 Regards
Tomas

View 1 Replies View Related

Updating Daily Information In A History Table (was Help-Brainstorming)

Feb 25, 2005

Hi everyone,
I have a big table which contains approx. 31,524,044 rows. The structure of the table look like this:
date ID A B C D
1/1/65 X Null Null Null Null
1/4/65 X 1 2 3 4
...
2/25/05 X 2 3 4 5
1/1/65 Y Null Null Null Null
1/4/65 Y Null Null Null Null
...
2/25/05 Y 2 3 4 5
...

The number of distinct(ID) is approx 3200 and each one has daily historical A, B, C, and D back for 40 years. For going forward I need to update daily information for 3200 ids. Currently, I am runing query against to this table is ok. I am thinking by the time pass by the table will be hudge since "stored historical information". It probably takes "long long long" time to run the query against this table. Any suggestion or comments... what is the best/better solution? Or it is not problem at all?

Thank you everyone for the help.
shiparsons

View 8 Replies View Related

DB Engine :: Unable To See Server Job History

Nov 18, 2015

I am unable to check old history in sql server jobs. I have set limit size of job history. but it will show the old history.

View 2 Replies View Related

DB Engine :: Table Transaction History

Oct 22, 2015

i have a table and i would like to know what are the queries that were executed referring to that table in the past 5 days. is it possible?

View 4 Replies View Related

DB Engine :: Determine Database Access History?

Jul 31, 2015

I was asked to determine the last time 2 databases were accessed.

Are the .trc files an accurate way to determine the database access history?

View 5 Replies View Related

DB Engine :: How To Optimize Stored Procedure Without Modify

Nov 25, 2015

How to optimize stored procedure without modify?Is there is any way to forcing index without using FORCESEEK hint?

View 3 Replies View Related

DB Engine :: Profiling Statements Within A Stored Procedure?

Apr 22, 2015

I'm profiling a specific procedure on a database, I have the start and end times captured but within the same trace I would like to capture all the individual sql statements.

So, given this simple procedure 

ALTER PROCEDURE [dbo].[usp_b]
AS
BEGIN
SET NOCOUNT ON;
SELECT @@VERSION;
SELECT GETDATE();
END

In the trace I would like to see the two SELECTs, however all I get is this 

I have the following events selected in the trace.

View 13 Replies View Related

DB Engine :: Log File Error While Running A Stored Procedure?

Nov 2, 2015

We try to run a stored procedure in management studio - and we see the following error - 
"
Executed as user: "Some User". Unspecified error occurred on SQL Server. Connection may have been terminated by the server. 

[SQLSTATE HY000] (Error 0)  The log for database 'Some-database' is not available. 

Check the event log for related error messages. 

Resolve any errors and restart the database. 

[SQLSTATE HY000] (Error 9001)  During undoing of a logged operation in database 'Some-Database', an error occurred at log record ID (2343114:16096:197). 

Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database. 

[SQLSTATE HY000] (Error 3314)  During undoing of a logged operation in database 'Some-Database', an error occurred at log record ID (2342777:81708:1). 

Typically, the specific failure is logged previously as an error in the Windows Event Log service.

Restore the database or file from a backup, or repair the database. [SQLSTATE HY000] (Error 3314).  The step failed. 

View 2 Replies View Related

DB Engine :: Can Find A Record Of Stored Procedure Being Called?

Jul 15, 2015

I seem to be able to see where a procedure is being recompiled, but not the actual statement that was executing the procedure.

Note, with 2008 there is a DMV called dm_exec_procedure_stats , which is not present in 2005

USE YourDb;

SELECT qt.[text] AS [SP Name],
qs.last_execution_time,
qs.execution_count AS [Execution Count]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = DB_ID()
AND objectid = OBJECT_ID('YourProc')

The above shows results that include the CREATE PROCEDURE statements for the procedure in question, but this only indicates that the procedure was being recompiled, not necessarily that it was being executed?

View 3 Replies View Related

DB Engine :: Shutdown Or Disable All Connections From Stored Procedure?

May 12, 2015

I have an auto exec stored procedure that needs to complete successfully or:

- the server should shutdown, or

- disable remote connections

Officially I cannot issue a Shutdown from a Stored Procedure. In addition, I can't see how to programatically disable remote connections.

View 6 Replies View Related

DB Engine :: TLog Does Not Get Truncated During Long Running Stored Procedure

Nov 8, 2015

I have a vendor database that has a stored procedure that runs a long time.Eventually, the database runs out of log space.

Setting the database to FULL and doing frequent log backups does not work.

The log does not get truncated during this log backups.

The stored procedure in question has SET XACT_ABORT ON statement at the beginning.

View 4 Replies View Related

DB Engine :: Error Trying To Execute BULK INSERT In Stored Procedure

May 16, 2015

At my customer's site they get this error trying to run a stored procedure I wrote that does BULK INSERT.

-2147217900
[Microsoft ODBC SQL Server Driver][SQL Server] You do not have permission to use the bulk load statement.
upImportFromICPMSRaw 'GSADC1CompanyInstrumentOutputFilesICPMSNew185367.csv', tblFromICPMSRaw

The customer has SQL Server 2008 R2 Express installed

The connection string to the database works on everything else and it is the sa account with password

On my own development system with SQL Server 2008 R2 Standard, it works perfectly OK.

View 5 Replies View Related

DB Engine :: How To Save Dataset In Server 2014 Stored Procedure

Aug 17, 2015

We are collecting values in a string format with delimeteres and sending to DB .We would like to insert the data in Bulk insert format rather than splitting the same and then inserting..

In sql 2014 can we  archive the same..sample format currently we are getting the client is like this is

Saleid$ salename$month$year$totalsale#Saleid$salename$month$year$totalsale# has a dataset.

View 6 Replies View Related

DB Engine :: Collation Change And DDL Change In Same Script

Nov 18, 2015

We ran into weird/interesting issue with below details.

Version: Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200):

We are using SQLCMD to run DDL script on our product database in below order. That script has below content.

step # 1 - database collation change (case -sensitive) statement as very first statement of the script
step # 2 - Actual DDL SQL statements
step # 3 - database collation change back to original (case insensitive)

When we execute all above 3 steps in single script using SQLCMD on our test_server#1 , it is successful but when same is being implemented on test_ server#2 , it is failing.We ensured that there is no other user accessing the db and setting on both the server are all default/basic. Separating out all 3 steps in 3 different script working fine. This is only problem when we combine them into single script and fire it using SQLCMD. If it is something related to session/transaction then we should hit same issue on our test_server#1 server as well but that is not the case.test_server#1 and test_server#2 has exact same database/data, just two different physical machine & SQL Server instance.

View 7 Replies View Related

Tracking Change History, By Column...

Jun 17, 2004

This one is giving me quite a bit more difficulty then I ever imagined it would...

Essentially I would like to use one table to store the change history for multiple tables. I would like to use an update trigger to check which fields have changed in each record, and write a single record for each field that changed containing the table name, field name, previous value and new value to a history table.

I can't seem to find a good way to do this.

View 9 Replies View Related

Schema Change History Report

Feb 1, 2008

There is a report when you click servername, report and run SCHEMA CHANGE HISTORY
I had my SQL 2005 running for a few weeks and this is many listed from day started is there a way to recycle this and clean it up on a weekly basic

View 5 Replies View Related

SQL Login Password Change History

Oct 4, 2007

Hello.

Is it possible to find out a complete history of when the passwords for any SQL Server logins were changed and by what/whom in 2005 standard edition?

Thanks.

View 1 Replies View Related

How To Use Stored Procedure To Change Permission??

Apr 24, 2001

Hi,
I have a schedule task which call one of my stored procedure,
In this stored procedure, I need to change db owner of one of
database, but I find sp_changedbowner do not allow me to specify
db name,it only change current db,so I have to open a db before
call sp_changedbowner,but it is invalid..


CREATE PROC demo
as
begin
...

use demo_db //it is invalid
exec sp_changedbowner 'scott'

end

Can anyone give me ideas?

Thanks

View 1 Replies View Related

I Need To Change The Owner Of A Stored Procedure To Dbo! Help!

May 18, 2001

Hello. I am trying to change the owner of a stored procedure from a user ID to dbo. How do i do this. I have tried sp_changeobjectowner but I get an error message. Can I do this from Enterprise Manager? btw, I am using SQL 7.0. Any help would be appreciated. Thanx

View 2 Replies View Related

Stored Procedure To Change Default

Dec 14, 2005

Can someone explain to me why the following doesn't work?declare @oname sysnameselect @oname=name from sysobjects where name like"df__mytable__mycol%"alter table mytable drop constraint @oname

View 4 Replies View Related

History/Data Change File Approach

Mar 4, 2005

I need to record in a table:
Who, When, What Field and New Value of Fields
When changes occur to an existing record.

The purpose is for users to occassionally view the changes. They'll want to be able to see the history of the record - who changed what and when.

I figured I'd add the needed code to the stored procedure that's doing the update for the record.

When the stored procedure is called to do the update, the PK and parameters are sent.

The SP could first retain the current state of the record from the disk,
then do the update, then "spin" thru the fields comparing the record state prior to the update and after. Differences could be parsed to a "Changes string" and in the end, this string is saved in a history record along with a few other fields:

Name, DateTime, Changes

FK to Changed Record: some int value
Name: Joe Blow
Date: 1/1/05 12:02pm
Changes: Severity: 23 Project: Everest Assigned Lab: 204

How does the above approach sound?

Is there a better way you'd suggest?

Any sample code for a system that spins thru the fields comparing 1 temporary record with another looking for changes?

Thanks,

Peter

View 3 Replies View Related

System Stored Procedure Name That Allows One To Change One's Password ?

Jan 2, 2008

I am using a web application using asp.net 1.1 vs 2003.
i am using sql server authentication where users are using their sql server user id and sql server saved passwords.
how can i change their passwords inside the sql server?
is there a system sp that allows one to pass one's user id and then change password.
 I will need to call that sp from a user defined store dprocedure and pass the parameters and that will change the password on the sql server.
thanks
  
 

View 3 Replies View Related

How To Change Date Formats In Stored Procedure

Oct 4, 2005

I need help on how to change the date format in a stored procedure. I am using the GetDate() function but need to convert it to short date format.

thanks
mike

View 14 Replies View Related

Change Data Format In Stored Procedure

Jul 31, 2006

Hi All,

My scenario is that I want to change the default SQL server format in my stored procedure more preferably only during the course of stored procedure execution (not permanent changes does any one have idea that how will I able to achieve this simple task...

regards,

Anas

View 1 Replies View Related

How To Create Stored Procedure Change Value Once A Month From 1,2,3,4 TO 2,3,4,1

May 8, 2008

help
i need to create stored procedures that run once month
change value (UPDATE) once a month in one table the values from 1,2,3,4 next month 2,3,4,1 ..... 3,4,2,1 .......4,1,2,3 ..... 1,2,3,4
i have this code



Code Snippet
SELECT empID, location, ISNULL(NULLIF (( location + DATEDIFF(mm, location_date, GETDATE())) % 4, 0), 4) AS new_location
FROM dbo.empList

like this the employee go from one location once amonth to new location






table on this month (5)
empid location location_date
--------------------------------------------------------------

1111 1
222 2
333 3
444 4

5555 3
666 2
777 3
888 4

table on next month (6) (after a month)
empid location location_date
-------------------------------------------------------------------

1111 2
222 3
333 4
444 1

5555 4
666 3
777 4
888 1


TNX

View 16 Replies View Related

How Do I Change The Type Of A Stored Procedure From User To System?

Aug 11, 2005

I built a database by using a generated script from the originaldatabase. It built the System Store Procedures as User type. How do Ichange them back to System type?

View 4 Replies View Related

How To Change View Code Or Table Form Stored Procedure + -

Jun 1, 2008

need help with my problem
i have this view
this code change the value field "new_unit" evry month from 1 > 2 > 3 > 4
like this evry 4 month it return to 1 >2..........
------------------------------------------ for example

if i put
unit_date = 01/05/2008
and unit=1
than new_unit=1




Code Snippet

SELECT empID, ShiftType, unit_date, unit, ISNULL(NULLIF ((unit - DATEDIFF(mm, unit_date, '01/01/' + CONVERT(varchar, YEAR(GETDATE())))) % 4, 0), 4)

AS new_unit

FROM dbo.empList




my question is how to create a stored procedure that move forward (all the employee) the "new_unit" field
in +1 OR "unit_date" value MONTH +1


like create stored procedure name "plus" + so if i run this name stored procedure name "plus"

the stored procedure go to the viewor table and change the code view or table value

so i forward all the the "new_unit" or "unit_date" value IN one (change the cycle)+1

it doesn't matter if it change the "unit" value in the table "dbo.empList" or "unit_date" value

the important thing is that i can forward +1 or backward -1

evry time i run the stored procedure i get +1 (in the "new_unit")
and olso
create stored procedure name "minus" + so if i run this name stored procedure name "minus"
this stored procedure that move backward the the "unit" value in the table "dbo.empList" or "unit_date" value in -1

TABLE dbo.empList

empid ShiftType unit_date unit

----------------------------------------------------------------------------
11111 6 01/01/2008 1
2222 8 01/03/2008 4
3333 9 01/04/2008 3

TNX for the help

View 7 Replies View Related

How Can I Change The Data Type Of The Parameter For The Deployed Stored Procedure ??

Jan 11, 2006

Hi
 
I have Try to Create Stored Procedure in C# with the following structure
 
[Microsoft.SqlServer.Server.SqlProcedure]
public static void sp_AddImage(Guid ImageID, string ImageFileName, byte[] Image)
{
       
}
 
But  when I try to deploy that SP to SQL Server Express  , The SP Parameters become in the following Stature
 
@ImageID uniqueidentifier
@ImageFileName nvarchar(4000)
@Image varbinary(8000)
 
But I don€™t want that Data types .. I want it to be in the following format
 
@ImageID uniqueidentifier
@ImageFileName nText
@Image Image
 
 
How Can I Control the data type for each parameter ??
Or
How Can I Change the data type of the parameter for the Deployed Stored Procedure ??
Or
How Can I defined the new Data type ??
 
Or
 
What's the solution to this problem ??
 
Note : I get Error when I try to use Alert Statement to change the parameter Data type for the SP
 
ALTER PROCEDURE [dbo].[sp_AddImage]
      @ImageID [uniqueidentifier],
      @ImageFileName nText,
      @Image Image
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [DatabaseAndImages].[StoredProcedures].[sp_AddImage]
GO
 
And thanks with my best regarding
Fraas

View 7 Replies View Related







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