Haunted Database (reverting Procedures)
Dec 20, 2007
For the second time in two days, a completely rewritten procedure (using "alter procedure" statement) has reverted to an earlier version after hours of testing and confirmation. In both cases, there was 4-6 hours from successful final testing to discovery of the "reversion."
Since sql server has no memory of earlier procedure versions, I can only conclude:
1) someone restored an earlier version of the db (log says no)
2) someone acidentally ran a script for the old procedure (no evidence for that)
3) the database crashed and was rolled back as part of restore (but alter procedure is a ddl and, as such, is auto-commited so it cannot be rolled back)
None of these 3 possibilities seems to have occurred. Is there another possibility I'm missing?
View 5 Replies
ADVERTISEMENT
Dec 28, 2006
The following list of actions leads to a corrupt database on SQL Server 2005.
Create a database snapshot
Drop a table in the database
Backup the database
Restore from backup
Revert to the snapshot
I'm not entirely surprised that it results in a corruption, what is surprising is that I can revert from a snapshot after restoring. There needs to be some kind of check to prevent reverting to a snapshot in a case like this. Until SQL Server prevents you from doing it, I'd recommend a best practice is to delete all snapshots before you restore a database so that you cannot do this by accident.
View 3 Replies
View Related
Aug 9, 2005
Our client's database is hosted by a third party in a clustered SQL Server 2000 environment. Things were running swimmingly for 18 months when, in January, some inconsistencies started to appear. The first symptoms were three records in one of the tables that look corrupted; i.e. they contained dates for the years 2079 and 6619! We simply removed the data that hd been bulk loaded that day and re-bcpd it and were able to continue without issue.
We never quite got to the bottom of the exact cause of this corruption and were happy to leave it be until, on 30 June, a DBCC after a failure gave the following (trimmed) results.
DBCC results for 'DealPeriodParmsFlexShadow'.
There are 0 rows in 1 pages for object 'DealPeriodParmsFlexShadow'.
DBCC results for 'DealPeriodParmsShadow'.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1383012008, index ID 0: Page (1:128399) could not be processed. See other errors for details.
There are 127575 rows in 1057 pages for object 'DealPeriodParmsShadow'.
DBCC results for 'recvspre17'.
There are 97593 rows in 3367 pages for object 'recvspre17'.
DBCC results for 'DealPeriodParmsSubShadow'.
There are 0 rows in 1 pages for object 'DealPeriodParmsSubShadow'.
DBCC results for 'System'.
There are 22 rows in 1 pages for object 'System'.
DBCC results for 'Recvs'.
There are 4980591 rows in 185222 pages for object 'Recvs'.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'Recvs' (object ID 1383012008).
DBCC results for 'DealPeriodsShadow'.
There are 1182 rows in 32 pages for object 'DealPeriodsShadow'.
DBCC results for 'DealsShadow'.
There are 0 rows in 1 pages for object 'DealsShadow'.
Running DBCC CHECKDB a second time seemed to imply that the issue was fixed. The application crashed within 45 minutes again. Manually running the SP that was executing at the time of the crash resulted in the following message:
Server: Msg 21, Level 21, State 1, Procedure sp_FlagGalpIneligibleOverdueObligors,
Line 109 Warning: Fatal error 644 occurred at Jun 30 2005 4:23PM
Running the same SP immediately again resulted in a new message:
Server: Msg 21, Level 20, State 1, Procedure sp_FlagGalpIneligibleOverdueObligors, Line 109
Warning: Fatal error 3624 occurred at Jun 30 2005 4:31PM
Location: scanrid.cpp:321
Expression: m_len != 0
SPID: 52
Process ID: 3756
A DBCC CHECKDB resulted in "CHECKDB found 0 allocation errors and 68 consistency errors in database" with messages such as:
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'dbCresta', index 'Recvs.XIEDealPeriodTypeStatus' (ID 1383012008) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:245:193) with values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'I') points to the data row identified by (RID = (1:663407:10)).
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'dbCresta', index 'Recvs.XIEDealPeriodTypeStatus' (ID 1383012008) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:245:194) with values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'I') points to the data row identified by (RID = (1:663407:11)).
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'dbCresta', index 'Recvs.XIEDealPeriodTypeStatus' (ID 1383012008) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:245:195) with values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'I') points to the data row identified by (RID = (1:663407:12)).
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'dbCresta', index 'Recvs.XIEDealPeriodTypeStatus' (ID 1383012008) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:245:196) with values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'I') points to the data row identified by (RID = (1:663407:16)).
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'dbCresta', index 'Recvs.XIEDealPeriodTypeStatus' (ID 1383012008) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:245:197) with values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'I') points to the data row identified by (RID = (1:663407:17)).
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'dbCresta', index 'Recvs.XIEDealPeriodTypeStatus' (ID 1383012008) (index ID 2). Extra or invalid key for the keys:
{These two messages appeared 42 consecutive times, then there were some standard DBCC result messages and then...}
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'dbCresta', index 'Recvs.XIEDealPeriodTypeStatus' (ID 1383012008) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:571866:69) with values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'I') points to the data row identified by (RID = (1:663445:25)).
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'dbCresta', index 'Recvs.XIEDealPeriodTypeStatus' (ID 1383012008) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:571867:244) with values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'I') points to the data row identified by (RID = (1:663382:1)).
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'dbCresta', index 'Recvs.XIEDealPeriodTypeStatus' (ID 1383012008) (index ID 2). Extra or invalid key for the keys:
{a further 26 consecutive times.}
Running DBCC a second time resulted in 0 inconsistencies. This seemed to be a growing pattern: i.e. DBCC would show problems on the first attempt and come back clean on immediate subsequent attempts. The third party hosting the database insist that there are no hard drive issues (we pointed them to a number of forums implying this might be the case). They are using a RAID 5 configuration and also insist that no config changes have taken place in the last year.
That night a job was run to rebuild all of the indexes in the database. The following morning the very first thing I did was run a DBCC checkdb, with the following (trimmed) results:
...
Table error: Table 'Recvs' (ID 1383012008). Missing or invalid key in index 'XIEDealPeriodTypeStatus' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:663382:1) identified by (RID = (1:663382:1) ) has index values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'E').
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Recvs' (ID 1383012008). Missing or invalid key in index 'XIEDealPeriodTypeStatus' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:663382:2) identified by (RID = (1:663382:2) ) has index values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'E').
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Recvs' (ID 1383012008). Missing or invalid key in index 'XIEDealPeriodTypeStatus' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:663382:3) identified by (RID = (1:663382:3) ) has index values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'E').
...
CHECKDB found 0 allocation errors and 3 consistency errors in database
{Running DBCC immediately again resulted in:}
...
DBCC results for 'DealPeriodParmsShadow'.
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Recvs' (ID 1383012008). Missing or invalid key in index 'XIEDealPeriodTypeStatus' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:663382:1) identified by (RID = (1:663382:1) ) has index values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'E').
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Recvs' (ID 1383012008). Missing or invalid key in index 'XIEDealPeriodTypeStatus' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:663382:2) identified by (RID = (1:663382:2) ) has index values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'E').
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Recvs' (ID 1383012008). Missing or invalid key in index 'XIEDealPeriodTypeStatus' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:663382:3) identified by (RID = (1:663382:3) ) has index values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'E').
There are 127803 rows in 1059 pages for object 'DealPeriodParmsShadow'.
...
CHECKDB found 0 allocation errors and 3 consistency errors in database
It should be noted that between the overnight job running and me executing DBCC in the morning no other external database activity took place. I.e. our application had been closed and no other databases on the server are currently in use.
The third party host subsequently executed dbcc checkdb with repair_rebuild and after this a standard DBCC CHECKDB came back clean. We later found that some of the data we had deleted in an attempt to rollback to a state before the errors had actually been resurrected by some of this activity. We then had to run many manual checks on the affected tables to verify the data was as expected.
Over the weekend of the 2nd and 3rd July diagnostic tests were run against the hardware in question in order to rule out h/w issues. The system was given a clean bill of health.
On Monday 4th the DBCC inconsistencies were back... They come and go, appearing on average once a week (sometimes two days in a row, sometimes not for two weeks). We have tried archiving data to make the tables smaller, removing the statistics (which we discovered were severely affecting performance anyway), running nightly DBCC CHECKDBs, all to no avail. The Recvs table seems to be the one that is the main problem. The bizarre thing is that the indexes on this table are dropped and recreated daily, since we bcp our daily data in. So the indexes should always be 'fresh'. There are no clustered indexes on this table, simply four non-clustered ones.
The support work resulting from the application failing (each time these DBCC inconsistencies occur) is time we'd very much like to spend more productively. We would very much appreciate any assistance anyone can give in getting to the root cause of the problem so that it can be addressed and the problems made to go away.
View 13 Replies
View Related
Nov 1, 2015
We have an issue with a 3 node SQL 2012 Always on availability group. Normal operation is node 1 (primary replica) with node 2 and node 3 as secondary replicas.After some patching, SQL wasn't running on node 1 hence the AG flipped over to node 2. This went unnoticed for some time and the transaction log for one of the AG databases became full on node 2 and node 3. (I think this is because it couldn't commit the transactions on node 1 so couldn't truncate it's t-log?) The DB is using synchronous replication btw.So I started SQL on node 1 and flipped the AG back to node 1 (with a data loss warning but I accepted this).Now the issue is that on node 2 and 3, the DB in question is stuck in a "Reverting / In Recovery" State. I've tried various commands such as ALTER DATABASE SET ONLINE, RESTORE DATABASE WITH RECOVERY etc but these fail stating unable to obtain a lock on the DB.
The weird thing is that on node 1 the state of the DB is "synchronised".how to resolve this issue on node 2 and 3? I've left them overnight (in case they were rolling back transactions, the DB is fairly large) but nothing seems to have happened. remove the DB from the AG in node 2 and 3 and add it back in again, ie recreate the replication?
View 2 Replies
View Related
May 10, 2001
Question:: I know there is a switch to take a database that was upgraded to 7.0 back to 6.5. How does it work? If I upgrade a database and for 2 weeks input
data and then decide I need to go back to 6.5 -- do I lose the data from those 2 weeks or does it revert all information back to 6.5
View 1 Replies
View Related
May 12, 2007
I have installed MSSQL 2005 on my D: drive of my windows 2003 server, but now need to install MSSQL version 2000 b/c I realized I have one less license than I thought I had. I have build no databases, only the default information. Can I install another instance (being the 2000 instance) on the same drive? If I can't, can I install it on the Crive without a lot of issues???? Or would it be easier to remove 2005 and just start from scratch? The sql version is 9.0. Any help or comments would be greatly appreciated.!!!!!
View 3 Replies
View Related
Jul 23, 2005
I'm trying to write a procedure that having created a new database,will then create within that new database all the tables andprocedures that go with it.In doing this I'm hitting the problem that you can't issue a USEcommand within a procedure.So my question is either- how do I get around this?- if I can't, how can I create procedures etc in a *different*(i.e. the newly created) databaseor- is there a better way to do all this (*)I have SQL files that do this currently, but I need to edit in thename of the database each time before execution, so I thought aprocedure would be better. Also I'd like eventually to expose someof this functionality via a web interface.Although I'm a newbie, I feel I'm diving in the deep end. Any goodpointers to all the issues involved in this aspect of databasemanagement would be appreciated.(*) One thought that occurs to me is to have a "template" database,and to then somehow copy all procedures, tables, view etc from that.--HTML-to-text and markup removal with Detaggerhttp://www.jafsoft.com/detagger/
View 10 Replies
View Related
Jul 15, 2001
We need to perform some maintainence on our database server which may take quite some time. We have a standby server which we constantly apply logs to.
We are planning to recover the standby server and use it while the production server is being maintained.
My question is how to I get back to using the original production machine.
The only way I can think of is to do a full backup on the standby machine,
do some transaction log backup and then restore these on the original production machine.
Is there any way to use the full backup from the original production machine and well as logs from the standby machine. When I try this I get the message that the database has not been rolled forward far enough.
In summary:
1. Backup full database on Prod Server
2. Restore full database on Standby Server
3. Backup transaction log (1) from Prod Server
4. Restore transaction log (1) on Standby Server
5. Recover database on Standby Server
6. Make modifications on Standby Server
7. Backup transaction log (2) on Standby Server
8. Restore full database on Prod Server
9. Restore transaction log (1) on Prod Server
10. Attempt to restore transaction log (2) fails.
View 3 Replies
View Related
Aug 3, 2015
I have user called DBA_USER he has db_owner access on (123DB) database ....
But when ever my machine restarted the user was getting access error (i.e.. you don't have access on this database) and i checked his access on that particular database his access got reverted back ....
Here my question is why this particular user getting this type of error ....
View 7 Replies
View Related
Feb 28, 2015
I have a SQL Server 2008 R2 database.
I have modified the autogrowth configuration several times. The strangest thing... they keep reverting back to the original configuration.
View 7 Replies
View Related
Nov 9, 2007
Hi I've got a sql server 2000 database that when running is runnign fine. About 9 months ago I altered one of the stored procedures and ever since then when the machine is rebooted the stored procedure is "reverted" back to the old sproc... ???
is there any way I can recrete a sproc in a job that runs every day?? why would it be doing this?
View 1 Replies
View Related
May 20, 2015
I have two inline selects against a table with a nonclustered columnstore on SQL 2014 (12.0.2000). Both execute in batch mode and when I inner-join the two, they continue to execute in batch mode. When I cross join them, one executes in row mode. Below is some SQL to simulate the issue.
-- The purpose of this script is to demonstrate that
-- two queries against a columnstore index that each execute in batch mode
-- will continue to execute in batch mode when inner joined.
-- However, one of the queries will execute in row mode when cross-joined.
-- Create function to return 0 to n rows
IF OBJECT_ID('dbo.IntCount') IS NOT NULL
DROP FUNCTION dbo.IntCount;
[Code] .....
View 6 Replies
View Related
May 30, 2007
Since as soon as you extend your mdx datasets manually you can no longer switch back into design mode without losing your changes, right?
If that's the case, is there some way to disable design mode completely? i'm finding that the GUI has the tendency to SILENTLY revert the dataset editor back to design mode while I'm busy editing a layout, thereby losing my carefully crafted MDX.
View 4 Replies
View Related
Feb 13, 2008
Hi all,
I try to learn "How to Access Stored Procedures with ADO.NET 2.0 - VB 2005 Express: (1) Handling the Input and Output Parameters and (2) Reporting their Values in VB Forms". I found a good article "Calling Stored Procedures from ADO.NET" by John Paul Cook in http://www.dbzine.com/sql/sql-artices/cook6. I downloaded the source code into my VB 2005 Express:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form_Cook
Inherits System.Windows.Form.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox
Friend WithEvents labelPAF As System.Windows.Forms.Label
Friend WithEvents labelNbrPrices As System.Windows.Forms.Label
Friend WithEvents UpdatePrices As System.Windows.Forms.Button
Friend WithEvents textBoxPAF As System.Windows.Forms.TextBox
Friend WithEvents TenMostExpensive As System.Windows.Forms.Button
Friend WithEvents grdNorthwind As System.Windows.Forms.DataGrid
Friend WithEvents groupBox2 As System.Windows.Forms.GroupBox
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.GroupBox1 = New System.Windows.Forms.GroupBox()
Me.labelPAF = New System.Windows.Forms.Label()
Me.labelNbrPrices = New System.Windows.Forms.Label()
Me.textBoxPAF = New System.Windows.Forms.TextBox()
Me.UpdatePrices = New System.Windows.Forms.Button()
Me.groupBox2 = New System.Windows.Forms.GroupBox()
Me.TenMostExpensive = New System.Windows.Forms.Button()
Me.grdNorthwind = New System.Windows.Forms.DataGrid()
Me.GroupBox1.SuspendLayout()
Me.groupBox2.SuspendLayout()
CType(Me.grdNorthwind, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'GroupBox1
'
Me.GroupBox1.Controls.AddRange(New System.Windows.Forms.Control() {Me.labelPAF, Me.labelNbrPrices, Me.textBoxPAF, Me.UpdatePrices})
Me.GroupBox1.Location = New System.Drawing.Point(8, 8)
Me.GroupBox1.Name = "GroupBox1"
Me.GroupBox1.Size = New System.Drawing.Size(240, 112)
Me.GroupBox1.TabIndex = 9
Me.GroupBox1.TabStop = False
'
'labelPAF
'
Me.labelPAF.Location = New System.Drawing.Point(8, 16)
Me.labelPAF.Name = "labelPAF"
Me.labelPAF.Size = New System.Drawing.Size(112, 32)
Me.labelPAF.TabIndex = 2
Me.labelPAF.Text = "Enter Price Adjustment Factor"
'
'labelNbrPrices
'
Me.labelNbrPrices.Location = New System.Drawing.Point(8, 80)
Me.labelNbrPrices.Name = "labelNbrPrices"
Me.labelNbrPrices.Size = New System.Drawing.Size(216, 16)
Me.labelNbrPrices.TabIndex = 5
'
'textBoxPAF
'
Me.textBoxPAF.Location = New System.Drawing.Point(120, 16)
Me.textBoxPAF.Name = "textBoxPAF"
Me.textBoxPAF.TabIndex = 0
Me.textBoxPAF.Text = ""
'
'UpdatePrices
'
Me.UpdatePrices.Location = New System.Drawing.Point(8, 48)
Me.UpdatePrices.Name = "UpdatePrices"
Me.UpdatePrices.Size = New System.Drawing.Size(88, 23)
Me.UpdatePrices.TabIndex = 6
Me.UpdatePrices.Text = "Update Prices"
'
'groupBox2
'
Me.groupBox2.Controls.AddRange(New System.Windows.Forms.Control() {Me.TenMostExpensive, Me.grdNorthwind})
<Part 1----To be continued due to the length of this post>
View 1 Replies
View Related
Jan 14, 2008
Hi all,
I put "Northwind" Database in the Database Explorer of my VB 2005 Express and I have created the following stored procedure in the Database Exploror:
--User-defined stored procedure 'InsertCustomer'--
ALTER PROCEDURE dbo.InsertCustomer
(
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24)
)
AS
INSERT INTO Customers
(
CustomerID,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
Region,
PostalCode,
Country,
Phone,
Fax
)
VALUES
(
@CustomerID,
@CompanyName,
@ContactName,
@ContactTitle,
@Address,
@City,
@Region,
@PostalCode,
@Country,
@Phone,
@Fax
)
=================================================
In my VB 2005 Express, I created a project "KimmelCallNWspWithAdoNet" that had the following code:
--Form_Kimmel.vb--
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form_Kimmel
Public Sub InsertCustomer()
Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _
"Initial Catalog=northwind;Data Source=NAB-WK-EN12345"
Dim connection As SqlConnection = New SqlConnection(connectionString)
connection.Open()
Try
Dim command As SqlCommand = New SqlCommand("InsertCustomer", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@CustomerID", "PAULK")
command.Parameters.Add("@CompanyName", "Pauly's Bar")
command.Parameters.Add("@ContactName", "Paul Kimmel")
command.Parameters.Add("@ContactTitle", "The Fat Man")
command.Parameters.Add("@Address", "31025 La Jolla")
command.Parameters.Add("@City", "Inglewoog")
command.Parameters.Add("@Region", "CA")
command.Parameters.Add("@Counrty", "USA")
command.Parameters.Add("@PostalCode", "90425")
command.Parameters.Add("@Phone", "(415) 555-1234")
command.Parameters.Add("@Fax", "(415 555-1235")
Console.WriteLine("Row inserted: " + _
command.ExecuteNonQuery().ToString)
Catch ex As Exception
Console.WriteLine(ex.Message)
Throw
Finally
connection.Close()
End Try
End Sub
End Class
==============================================
I executed the Form_Kimmel.vb and I got no errors. But I did not get the new values insterted in the table "Custermers" of Northwind database. Please help and tell me what I did wrong and how to correct this problem.
Thanks in advance,
Scott Chang
View 10 Replies
View Related
Jan 31, 2008
Hi I have an application that I have started to develop. I have successfully set the connection to open the SQL Server database that I created. When I first started on the program, I was able to create Table Adapters by dragging the tables or stored procedures onto the DataSet work surface and going through the configuration process. At one point, however, I stopped being able to see any of my stored procedures in the database view although they are there because when I go to create a new table adapter, I am able to right click and add the new adapter and find the stored procedure in the wizard.Is there any way I can reset this so that my stored procedures are visible again. I have tried refreshing to no avail - and I think this is creating problems in other parts of the application.Any help appreciate.Roger
View 1 Replies
View Related
Apr 24, 2007
Hello everyone,I face currently a problem where I could need some input for searchingthe source of the ProblemSystem: SQL Server 9.0I fill from Database A with triggers Database B, everything worksfine.On Database B there is a Stored Procedures that checks the records andadd additional information accordingly, this Stored Procedures isnormally called by the application on "update and insert" in theaccording table.When I try to call this Stored Procedures from the Database A, thetrigger does not work anymore, even if I do a try catch over the wholetrigger, he never reach the Catch and the insert I try to do there toget the error message.On both Databases the user, that is taken to execute the trigger isexistent and DB-Owner of both Databases.If I go and execute the Stored Procedures manually after an insert orupdate to Database B everything works fine.I also already tried to check on Database B if there is an insert orupdate from Database A and if, to execute the Stored Procedures, withthe same result, nothing and all happens anymore, neither update onDatabase A and also not on Database B.And also I cant catch the error as the Try/Catch is not working.Hope I could explain it understandable and maybe someone remembersalready having the same problem.Thanks & Best regardsPascal
View 2 Replies
View Related
Apr 14, 2008
I've never dealt with stored procedures much.. but i have a new database
created.. imported the tables, but the stored procedures didnt get copied
over..
Is there an easy way to export them.. perhaps to a .sql file ... then import
them or run a script on the other database..
I have never done much with the query window before, so i'm not sure how to
handle this.. as there are around 20 stored procedures that need imported..
Thanks for any tips...
View 5 Replies
View Related
Mar 1, 2008
is there a way to copy whole stored procedures to another database?
I have aproximately 80 SPs in a database, I want to copy whole SPs into another DB. Is there a practical way to copy them?
Thanks
View 2 Replies
View Related
Jun 16, 2008
Hi,
Is there a practical way to transfer Stored Procedures to another database?
Thanks.
View 1 Replies
View Related
Jun 4, 2008
Hello.
Is there a way, through t-sql or through SQL Server Management Studio, to retrieve (or export to file) all procedure code for all my stored procedures?
I would like this for backup reasons. Possible?
View 3 Replies
View Related
Apr 24, 2007
I am attempting to compile a list of questions that will enable meassess 'at risk' stored procedures that need to be remediated in orderto minimize unplanned downtime and enhance database performance. Isthere a subset of criteria (parameters) from the MS SQL Server BestPractices Analyzer that can be used for this purpose? Any pointers tothe appropriate documentation is most welcome.Thanks in advance.
View 1 Replies
View Related
Jul 20, 2005
I have 2 databases, one that we use called MyShop and one that I develop oncalled TestShop.After I have a stored procedure working the way I want in TestShop , isthere a way to just copy the SP to the other database without the copy andpaste method?. Same if I have a new table. Any way to add it in withoutrecreating it in the MyShop database?I am using Sql Server 2000ThanksAndy
View 1 Replies
View Related
Apr 19, 2007
How do i copy Stored Procedures from one SQL Express database to another?
View 8 Replies
View Related
Aug 31, 2006
I have read that it is possible to configure sql server express so that the database can only be accessed through stored procedures. Can anyone tell me how to do this. Many thanks.martin
View 2 Replies
View Related
Dec 16, 2005
Hello,
I'm building an ecommerce website which requires customers to create an account before they go ahead with a purchase. I have a createaccount.aspx page in Visual Web Developer 2005 with text boxes where users can enter their details (email, password, name and address). I'm trying to insert the information which users type into the text boxes into an SQL database table called Customers.
I've dragged and dropped an SQL data source onto my page and have set it to operate on my AddCustomer stored procedure. I've confirgured my data source such that the parameter for each field in the database is set to the appropriate control on the webpage (for example the Email parameter source is "textboxEmail").
I've also placed a button onto my page so that the button click event can act as the trigger for sending the information in the text boxes to the database. I wasn't totally sure how to write code for the button click event such that when the button is clicked, the INSERT stored procedure runs. At the moment I'm using:
Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
SqlDataSource1.Insert()
End Sub
When I try to run my application I'm getting an error which reads:
Cannot insert the value NULL into column 'Email', table 'C:DOCUMENTS AND SETTINGSLUKE JACKSONMY DOCUMENTSVISUAL STUDIO 2005WEBSITESJACKSONSNURSERIESAPP_DATADATABASE.MDF.dbo.Customers'; column does not allow nulls. INSERT fails.The statement has been terminated.
The error message implies that I haven't set the necessary parameters correctly but I really don't know where I'm going wrong!
The code I'm using for my stored procedure is as follows:
ALTER PROCEDURE AddCustomer
(
@CustomerID int,
@Email nvarchar(50),
@Password nvarchar(MAX),
@Name nvarchar(50),
@Address1 nvarchar(50),
@Address2 nvarchar(50),
@Address3 nvarchar(50),
@City nvarchar(50),
@County nvarchar(50),
@PostCode nvarchar(50)
)
AS
INSERT INTO Customers
(Email, Password, Name, Address1, Address2, Address3, City, County, PostCode)
VALUES
(@Email, @Password, @Name, @Address1, @Address2, @Address3, @City, @County, @PostCode)
I'd be really grateful if anyone could help me out with this.
Thanks in advance,
Luke
p.s. just incase it helps, here's my createaccount.aspx page:
<%@ Page Language="VB" MasterPageFile="~/Master.master" AutoEventWireup="false" CodeFile="createaccount.aspx.vb" Inherits="createaccount" title="Untitled Page" %>
<%-- Add content controls here --%>
<asp:Content ID="Content1" runat="server" ContentPlaceHolderID="ContentPlaceHolder1">
<span style="text-decoration: underline"><strong>Create Account<br />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="AddCustomer" SelectCommandType="StoredProcedure" InsertCommand="INSERT INTO Customers 	(Email, Password, Name, Address1, Address2, Address3, City, County, PostCode) 	VALUES 	(@Email, @Password, @Name, @Address1, @Address2, @Address3, @City, @County, @PostCode)">
<SelectParameters>
<asp:Parameter Name="CustomerID" Type="Int32" />
<asp:ControlParameter ControlID="textboxEmail" Name="Email" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="textboxPassword" Name="Password" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="textboxName" Name="Name" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="textboxAddress" Name="Address1" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="textboxAddress2" Name="Address2" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="textboxAddress3" Name="Address3" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="textboxCity" Name="City" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="textboxCounty" Name="County" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="textboxPostCode" Name="PostCode" PropertyName="Text"
Type="String" />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="Email" Type="String" />
<asp:Parameter Name="Password" Type="String" />
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="Address1" Type="String" />
<asp:Parameter Name="Address2" Type="String" />
<asp:Parameter Name="Address3" Type="String" />
<asp:Parameter Name="City" Type="String" />
<asp:Parameter Name="County" Type="String" />
<asp:Parameter Name="PostCode" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
<br />
</strong></span>
<table style="font-weight: bold; width: 394px; text-decoration: underline">
<tr>
<td style="width: 111px; height: 21px; text-align: left">
Email:</td>
<td style="height: 21px">
<asp:TextBox ID="textboxEmail" runat="server" Width="147px"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 111px; height: 21px; text-align: left">
Password:</td>
<td style="height: 21px">
<asp:TextBox ID="textboxPassword" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 111px; height: 21px; text-align: left">
Name:</td>
<td style="height: 21px">
<asp:TextBox ID="textboxName" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 111px; text-align: left">
Address 1:</td>
<td>
<asp:TextBox ID="textboxAddress" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 111px; height: 21px; text-align: left">
Address 2:</td>
<td style="height: 21px">
<asp:TextBox ID="textboxAddress2" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 111px; text-align: left">
Address 3:</td>
<td>
<asp:TextBox ID="textboxAddress3" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 111px; text-align: left">
City:</td>
<td>
<asp:TextBox ID="textboxCity" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 111px; height: 21px; text-align: left">
County:</td>
<td style="height: 21px">
<asp:TextBox ID="textboxCounty" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 111px; text-align: left">
Post Code:</td>
<td>
<asp:TextBox ID="textboxPostCode" runat="server"></asp:TextBox></td>
</tr>
</table>
<asp:Button ID="btnSubmit" runat="server" Text="Submit" />
</asp:Content>
Thanks again
View 1 Replies
View Related
Feb 12, 2015
I'm looking for an easy way to run all stored procedures in a database that match a specified criteria.
Under normal curcumstances, I'd create a "master" procedure that would call each one in turn using the "EXECUTE" syntax.
As there will be around 140 procedures (there are a few more but they are used for different purposes), I'd like to try and execute them dynamically.
Is there any way of finding out which procedures match the pattern of "usp_merge_*" for the name and executing it?
View 4 Replies
View Related
May 13, 2015
create all the maintenance scripts (SProcs) to my maintenance databases in an automated way?I have around 12 procedures which I don't want to consolidate in a single Proc and execute.Another problem which is coming to my mind is, I can';t use the below method to execute the proc, as I dont have RDP access to the servers.
sqlcmd -i C:MyFolderMyScript.sql -o C:MyFolderMyOutput.txt
I am thinking to ask for a central location where I can create a folder , dumping all my scripts and build the maintenance DB and creating all my procs, table, etc.
View 1 Replies
View Related
Jul 20, 2005
Hello all, this is my second post to this newsgroup. It's a questionabout stored procedures and permissions and how these behave betweendatabases.Here's the scenario. I have a database that stores information for asystem "A", and I have a different database on the same SQL serverthat stores the login and other info "LOGIN". I write a storedprocedure in the "A" database that checks some tables in the "LOGIN"database, let's call this "SP_A".Additionally I have a user account that accesses all appropriatestored procedures in "A" called "USER_A", and the same for the "LOGIN"database, "USER_LOGIN".Here's the part that raised my curiosity. I log into the server viaQuery Analyzer using the "USER_A" account. I run "SP_A" which does ajoin between some table in "A" and another table in "LOGIN". I give"USER_A" execute permission on "SP_A", then I try to run "SP_A" andget an error:SELECT permission denied on object '(table in "LOGIN" database)',database '(real name of "LOGIN")', owner 'dbo'Huh? how come I need to assign additional select permissions in thisdatabase if I'm not doing an actual select statement? I'm not evendynamically running a select statement through an exec function. Thisjust struck me as odd, seeing as how I never explicitly set SELECTpermission on any table in "A" for "USER_A", yet my stored procedureworks, but between databases I have to assign extra permissions for astored procedure "SP_A" access to the tables in "LOGIN".Anyone able to explain this behavior? Because I'm at a loss and I'veonly been doing this DB thing for about 2 years.Thanks in advance, all.-TJ
View 4 Replies
View Related
Aug 14, 2007
I have database in the database there are a few users that no one is used. When I try to drop thpse users I got next error message:
"The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped." (Msg 15136)
Indeed, I think that those users have execute rights on store procedures.
How do I find for wich procedures or other database objects those users have grants?
How do I delete them from database (and maybe from logins of the server)?
How can I see what grants a user has?
How can I see what grants does STP has?
View 10 Replies
View Related
Oct 9, 2006
Hello,
Is there a way to copy some selected or all stored procedures and functions from one database to another?
Thanks in advance.
Best regards,
View 3 Replies
View Related
Mar 21, 2008
I have my database: "RequestTrack"
My table (with its columns): "Request"RequestKey (automatically generated)..and the Primary KeyEntryDate (datetime)Summary (nvarchar)RequestStatusCodeKey (bigint)EntryUserID (nvarchar)EntryUserEmail (nvarchar)I am wanting to create a basic web form where my user interface has 3 text boxes and a Submit button:
txtUserID.TexttxtEmailAddress.TexttxtRequestSummary.Text
**After I hit the submit button the information will then be inserted into the database. Also the RequestStatusCodeKey will be MANUALLY typed in so that will not require the user to add that. Please please please help ! I've been searching online for days and looking at various websites and still havent found anything. I've found somethings but they went into too much depth with too much information. I am just wanting to stay basic but w/o using SQLDataSource Controls. I would like to be able to store a lot of data. Thanks for your help!!!
View 4 Replies
View Related
Jul 7, 2005
Hi there,I am trying to create a Sql Server 2005 Express database by running Sql Scripts. I have a script that has CREATE statements for tables and procedures. What I'd like it to do is create a new database with a name and a file location I specify.I searched high and low, tried lots of options with the SqlCmd tool but I keep getting authentication or other errors.Is this even possible? If so, does someone know of a good reference? I know I can create a new database and tables from within VWD but I need to create this database based on a .sql file....Thanks in advance....Imar
View 22 Replies
View Related
