I am experiencing a situation where I issue a lengthy SQL command to MS SQL Server 7.0 through MTS and it "disappears" - no errors or recordsets are returned. The command is "SELECT * FROM CUSTOMERS WHERE LASTNAME LIKE 'SMITH%'". When I issue this command from SQL Query Analyzer it takes 27 seconds to return 87 rows. When I issue this exact same command through MTS it does not return at all.
I've used the SQL Profiler to analyze the requests. It shows the commands from MTS starting but they never stop (or at least the profiler never reports them as stopping). The same commands coming from Query Analyzer are reported as starting and stopping without fail.
Here's a twist: I can issue less demanding commands (ie, one that doesn't take so long to process) through MTS and they come back fine. For example, when a user logs into my application, I use an SQL statement to verify the user name and password and status the user as logged in. This is routed through MTS and it comes back fine in less than a second. Same application, same PC, same MTS and SQL server, same SQL database. The only difference is that the CUSTOMERS table has over 800,000 records and the USERS table has only 5 records.
I'm building a simple webform, except Visual Studio and my service provider have combined to drive me nutty.
First, I MUST use an ODBC connection to my remote SQL Server do to some unknown configuartion problem. I've been playing with Visual Studio for only a month, so normally when something goes wrong I can go look in the mirror and find the culprit. This is different. I've got a totally functional web form with a SQL Connection, but when I try to change it to an ODBC Connection, I get the following error.
An OdbcParameter with ParameterName '@CertHolder' is not contained by this OdbcParameterCollection
My coding is fine because I stole it straight from the walkthrough and it works. But the specifications that Visual Studio provide are quite suspect. Please note the failure to include some key "@" signs.
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer. <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent() Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection Me.cmdUpdate = New System.Data.SqlClient.SqlCommand Me.cmdGetAll = New System.Data.SqlClient.SqlCommand Me.cmdSelect = New System.Data.SqlClient.SqlCommand Me.OdbcConnection1 = New System.Data.Odbc.OdbcConnection Me.OdbcGetAll = New System.Data.Odbc.OdbcCommand Me.OdbcSelect = New System.Data.Odbc.OdbcCommand Me.OdbcUpdate = New System.Data.Odbc.OdbcCommand ' 'SqlConnection1 ' Me.SqlConnection1.ConnectionString = "this works fine" ' 'cmdUpdate ' Me.cmdUpdate.CommandText = "UPDATE InsuranceData SET Name = @Name, Address = @Address, Address2 = @Address2, " & _ "City = @City, State = @State, Zip = @Zip, CertHolder = WHERE (CertHolder = @Cert" & _ "Holder)" Me.cmdUpdate.Connection = Me.SqlConnection1 Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Name", System.Data.SqlDbType.NVarChar, 50, "Name")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Address", System.Data.SqlDbType.NVarChar, 50, "Address")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Address2", System.Data.SqlDbType.NVarChar, 50, "Address2")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@City", System.Data.SqlDbType.NVarChar, 50, "City")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@State", System.Data.SqlDbType.NVarChar, 50, "State")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Zip", System.Data.SqlDbType.NVarChar, 50, "Zip")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CertHolder", System.Data.SqlDbType.NVarChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CertHolder", System.Data.DataRowVersion.Original, Nothing)) ' 'cmdGetAll ' Me.cmdGetAll.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData" Me.cmdGetAll.Connection = Me.SqlConnection1 ' 'cmdSelect ' Me.cmdSelect.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData W" & _ "HERE (CertHolder = @CertHolder)" Me.cmdSelect.Connection = Me.SqlConnection1 Me.cmdSelect.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CertHolder", System.Data.SqlDbType.NVarChar, 50, "CertHolder")) ' 'OdbcConnection1 ' Me.OdbcConnection1.ConnectionString = "This works fine" ' 'OdbcGetAll ' Me.OdbcGetAll.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData" Me.OdbcGetAll.Connection = Me.OdbcConnection1 ' 'OdbcSelect ' Me.OdbcSelect.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData W" & _ "HERE CertHolder = @CertHolder" Me.OdbcSelect.Connection = Me.OdbcConnection1 Me.OdbcSelect.Parameters.Add(New System.Data.Odbc.OdbcParameter("CertHolder", System.Data.Odbc.OdbcType.NVarChar, 50, "CertHolder")) ' 'OdbcUpdate ' Me.OdbcUpdate.CommandText = "UPDATE InsuranceData SET Name = @Name, Address = @Address, Address2 = @Address2, " & _ "City = @City, State = @State, Zip = @Zip WHERE CertHolder = @CertHolder" Me.OdbcUpdate.Connection = Me.OdbcConnection1 Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Name", System.Data.Odbc.OdbcType.NVarChar, 50, "Name")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Address", System.Data.Odbc.OdbcType.NVarChar, 50, "Address")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Address2", System.Data.Odbc.OdbcType.NVarChar, 50, "Address2")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("City", System.Data.Odbc.OdbcType.NVarChar, 50, "City")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("State", System.Data.Odbc.OdbcType.NVarChar, 50, "State")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Zip", System.Data.Odbc.OdbcType.NVarChar, 50, "Zip")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Original_CertHolder", System.Data.Odbc.OdbcType.NVarChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CertHolder", System.Data.DataRowVersion.Original, Nothing))
I NEVER EVER TYPED ORIGINAL_CERTHOLDER IN THE SQL PREPARATION
End Sub Protected WithEvents btnSave As System.Web.UI.WebControls.Button Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection Protected WithEvents ddlCertHolder As System.Web.UI.WebControls.DropDownList Protected WithEvents txtName As System.Web.UI.WebControls.TextBox Protected WithEvents txtAddress As System.Web.UI.WebControls.TextBox Protected WithEvents ddlCH As System.Web.UI.WebControls.DropDownList Protected WithEvents cmdUpdate As System.Data.SqlClient.SqlCommand Protected WithEvents cmdGetAll As System.Data.SqlClient.SqlCommand Protected WithEvents cmdSelect As System.Data.SqlClient.SqlCommand Protected WithEvents txtAddress2 As System.Web.UI.WebControls.TextBox Protected WithEvents txtCity As System.Web.UI.WebControls.TextBox Protected WithEvents txtState As System.Web.UI.WebControls.TextBox Protected WithEvents txtZip As System.Web.UI.WebControls.TextBox Protected WithEvents OdbcConnection1 As System.Data.Odbc.OdbcConnection Protected WithEvents OdbcGetAll As System.Data.Odbc.OdbcCommand Protected WithEvents OdbcSelect As System.Data.Odbc.OdbcCommand Protected WithEvents OdbcUpdate As System.Data.Odbc.OdbcCommand
'NOTE: The following placeholder declaration is required by the Web Form Designer. 'Do not delete or move it. Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init 'CODEGEN: This method call is required by the Web Form Designer 'Do not modify it using the code editor. InitializeComponent() End Sub
#End Region
Comments? Suggestions, I am not positive about how to fix this.
I have a similar problem to the one indicated by David Van Diest. I could not find any answers to his query. Could anyone help!
Thanks
Mon, 8 Feb 1999 16:24:55 -0600 From: David Van Diest [sql7] DTS and disappearing indexes
I have noticed that if I use the Microsoft ODBC driver for SQL Server when I transfer data from one database to another my indexes disappear. If I pick other odbc source and then fill in the data source my indexs do not go away.
Anyone else see this behavior? I don`t think it should be working this way.
I have three servers replicating. One is a publisher and two subscribers. If I put data in the publisher, some of it will disappear after replicating. When data is entered in one of the subscribers, the same thing happens.
Anyone have any ideas??? I am doing a merge replication and all servers are running SQL Server 2000.
Ok, I'm new here and I REALLY need some help here.
In my organisation, we have a Server with MSSQL 7 running with some databases. Some of the databases are published for replication.
Now, since Monday, we've been losing databases that are in use! :confused:
The files are disappearing and being marked as suspect in SQL. The physical file has been deleted from the machine even when SQL is running and on some databases are replicating. In one instance, we were making permission changes on a user and it just said that the file is no longer there. And when we checked, the msdb system database had disappeared as well. So now the server is showing up as having no databases. We were able to recover some of them through backups and a handy recovery tool. But now we are helpless as to WHY this is happening. We suspect Virus but we have Norton CorporateEdition running and Liveupdate updating every day.
I've searched the web for any one that had the same problem, but nothing came up. Only some stuff saying that space was an issue, but not on a drive with 21GB remaining and the biggest database is 3GB...
Any help, insight or resolution will be greatly appreciated.
I am running an Access 2000 MDB against a SQL 7 back end, using ODBC linkedtables over a LAN and a WAN. The system has been operational for years withrelatively few problems.Recently, WAN users have been reporting several hundred records disappearingat a time. The records are all sequential, and they're in a table thatcontains about 60,000 records. This all started last week at about the sametime (not sure if before or after) that the WAN went down for a couple ofhours for an unknown reason. Since then, every once in a while, a WAN userwill report that several hundred records in a block are just "missing."Then, an hour or two later, they reappear.Any ideas as to what's going on or what can be done to rectify this?Thanks!Neil
I am not sure where to put this but correct me if i'm in the wrong forum.
I've been working on this database for some time now. It's an Access 2003 database with a SQL backend. Everything was going well until one of the users noticed her data was disappearing after she send an email. The database has a memo field that gets pasted in the body of an email when it is created. Sometimes, when the user sends an email and goes back into the record, the memo field is blank. I know the field has data in it at the time of the email because it will give you an error if the field is null and you won't be able to send it. And, it goes into a history table with everything the user does with each record. I've checked this history table and it shows that all records in question, an email was sent. I have no idea why the data would disappear in this field. It does not happen all the time. I've performed the same steps and I can't duplicate the problem. There's no code associated with the memo field that deletes it after sending the email. Can anyone think of anything that would cause this?
The problem i am facing here is disappearing data in sql server 2000. I not sure what is the problem, but it is getting worse and worse. Anyone can point me to a right direction whether this is a security vulnerability which i need to download the patch, or is it my computer is infected with virus., or is it a bug in sql server?
I remember having read somewhere about this problem also, but not sure in which website already. Anyone, please help!!! Because we are going to deploy to production server soon, and this is a problem that need to be solve ASAP..
I am supporting a system that needs to allow users to have access to Temp DB. I set these users up using the GUI, but whenever the server is restarted, these users are wiped out. Is there anyway to keep these users when the server is rebooted?
1. The table statistic shows for example 67 rows in a table, select count(*) only returns 63 rows. 2. Table statistic shows 50 rows, select count(*) returns 55 rows.
In both cases if you do an insert the newly inserted row sometimes can be retrieved by a select statement sometimes not. Row statistics sometimes is updated correctly, sometimes not.
Integrity check for these databases says everything is fine. DBCC CHECKDB, DBREINDEX, UPDATESTATISTICS, ... does not help or says everything is fine.
Already opened a case at HP's Microsoft support and they involved Microsoft itself but all are a little bit clueless at the moment.
As this is our main DB cluster and several databases are affected we had to stop most of our applications since last Thursday and now we are getting a little bit in trouble so any hint is very welcome.
Thanks in advance
PS: Restore is not an option - as all tools say everything is fine all of our backups from the last months include the error. Don't ask why nobody recognized the lost data earlier, seems they were stored but not required for some time.
I am using a hosted MSSQL 2000 database that powers the backend of mywebsite. Website visitors interact with it via ASP pages I havedeveloped. I also have an internal FileMaker 7 database thatperiodically synchs with it via Filemaker's ODBC functionality.Several times now, I have come in one day to discover that my ASP pagesdon't work. When I look into it, one of my MSSQL tables will be missinga few fields. There is nothing in any of my code that sends an ALTERTABLE command or any other command that affects table structure. Thesedeletions of fields is totally random.The most recent example was with a table called FreeTeacherSubs. I justdiscovered today that three fields went missing:HowDidYouHearAboutYES TEXTFollowUpCallOrEmail TEXTHowMaterialsFit TEXTSuffice to say I am baffled! Has anyone ever experienced fieldsdisappearing from their tables?Kevin
Hi,We have a SQL server db backend for our ERP system. I have written aCrystal Report to extract data from one particular table calleditemspecbomw (c.30000 records) which contains amongst other thingsBill of Materials costings. When I run the report I know that somerows are missing as when I look at values through the ERP systemitself, the values are different.What I have found is that when I run the equivalent ERP system report,the Crystal Report I have written shows extra rows. SQL Query Analyserbehaves exactly the same as the report I have written even when usinga "select * from". I have tested opening up a bigger tables (c. 700000records) which appears without a problem. If anyone knows why thismystery is happening, I woould be grateful for your help.Many thanks,Tony.
I'm using an Access front end to a SQL 2000 database.I have a form from which I enter data. Everything seems fine, I check thetables and my records are being inserted.When I close Access and reopen it, data from three of my tables is gone.SQL Profiler turns up nothing. If I try to access a problem table fromEnterprise manager while Access is open I get an ODBC error. If I try toaccess it after I close Access, the records are gone.Where do I start looking to figure out where the problem is? I'm at a lost.TIA for help.--Jake
Has anyone seen precedence constraints disappear in a package after closing and opening again? In this case, it's not package-wide. Only constraints inside one Foreach Loop container disappeared. Any idea what causes this to happen?
p.s: Yes, I did Save All. Most of these contraints were saved in the package for more than a week anyway.
I created a simple stored procedure shown below. It works wonderfully. The only problem is that it likes to disappear on me. I am accessing it through a VB.Net program. The system will work fine throughout the day and suddenly it will be gone. I can exit Sql Server Management Studio and come back in and it will still be there so that is not causing it. The database stays connected all the time.
Any suggestions?
Code Snippet IF EXISTS( SELECT * FROM sys.procedures WHERE name = N'VerifyPart' ) DROP procedure VerifyPart; --create a procedure for the part code tab verify part function go create procedure VerifyPart ( @PartNumber varchar(50) = null, @LastUpdated varchar(30) = null ) as --Update Part Number update Parts set LastUpdated = @LastUpdated where PartNumber = @PartNumber
I'm not even sure where to post this, but has anyone had a problem with maintenance plans disappearing?
I mean the SSIS package and the job just disappearing....?
I have four servers. Two dev and two prod. I created a pretty simple maintenance plan to backup databases. There are four tasks in the plan. One for each of four databases. Backing each up to a separate file, all in the same folder.
The plan gets associated with a job that has it run daily, every six hours, with no end date.
The plan runs just fine. Then this morning, on one of the servers, the plan and its job are just gone.
This is the second time this has happened. And it's not the same server that it happened on the first time.
When I enter data into a SQL database and save it, the data that I have just added disapears, but when I close the applicaiton and re-open it, it's still there - is there any reason for this and any way to get it to stay?
I have set up 80 connections to the SQL Server - mostly through code. People have been successfully using the database, but then when they come in to use their computer and the database the next day, their connection is completely gone! What is that all about? So far this has happened to 2 or 3 different users. Any idea why? Thanks.
We have a number of databases on our SQL Server 2005. One of them, AddressPoint, keeps disappearing completely every few days. I should qualify that - the database name still exists and is visible in Management Studio, but there is no + icon to expand to see the object types.
I can manually restore the database. But we are unable to identify any process that might be deleting the database.
So where do I even start in trying to track down the process that is deleting it? I have not been able to pinpoint a specific time when the deletion might have occurred, as I am not using the database regularly, and neither are our users, yet. The latest deletion definitely occurred within the last 24 hours, but that's as specific as I can be.
I'm trying to modify an existing report. But if I make any change at all to the query (anything at all, like add a space), when I switch to the layout tab, the list of fields in the field list disappears. Un-doing the change does not restore the field list. The query runs fine from the data tab and returns a valid resultset.
Any suggestions?
This is with RS 2000 and Visual Studio 2003. Specifically, I'm trying to modify the Sales Pipeline Detail report from CRM.
Relationships between tables disappear without explanation in thedatabase diagram using SQL Server 2005.Many thanks for any thoughts/questions on the topic!
At one of our SQL2005 servers we've got a really strange problem. The transaction logfile of one of our databases is suddenly disappearing without a reason. From that moment on the event log (application) is being filled up with SQLserver event 9001
Event Type: Error Event Source: MSSQL$SQLEXPRESS Event Category: (2) Event ID: 9001 Date: 16/04/2007 Time: 12:31:40 User: N/A Computer: PBVSRV Description: The log for database 'Putboringen' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
I have created a number of reports where I need to display database elements in the Header record. I have been using the reportitems feature to get them to display there. When I preview the report the header looks fine; however, when the report is exported to pdf or printed out the header information completly disappears.
I'm executing a stored procedure from an Execute SQL Task in my SSIS package. The procedure takes two input variables and creates and executes an SQL statement based on those variables. I don't have it set to return anything.
When I run the package in debug mode on my desktop, it runs just fine.
However, when I save it to the RDBMS and execute it as a step in a job, the stored procedure errors out because it doesn't receive the input parameters. If I replace the ? in the statement with the value I know the parm should have, it executes just fine. The parms are mapped to the 0 (and, where applicable, 1) input parms.
As an aside, the package variable supplying the value is a byte value. However, in the parm mapping for the step, I define it as varchar because that's what the stored procedure is expecting. Could this be goofing me up?
When I create a stored procedure like the one below and save it,everything seems fine. But when I close Enterprise Manager andre-open it, the stored procedure is gone.Any ideas?SET ANSI_DEFAULTS ONGOCREATE PROCEDURE [dbo].[sp_Test] ASSELECT * FROM OPENQUERY(LINKEDSERVER, 'SELECT V.VENDOR_ID,V.VENDOR_NAME FROM VENDOR V')GOTed
As a CRM person, I sometimes have to update the database to cope with custom reports that I have written. Usually, I will
Go into SQL Server Management Studio 2008Open the database Locate either the table or the view Right click on permissions. Add in the role or user that's needed Grant them Select permissions
This allows the report in CRM to access the database and this works.
The problem I have is that after a certain amount of time, the user or role will disappear from the permissions meaning that the report will not run. Sometimes, this can be as soon as a couple of days or it can be months. It happens on views more than tables and, whilst it's not too difficult to put the permissions back, it is annoying for the users.
I don't use SQL Server Management Studio very regularly, only to write a few queries and set up these permissions so I'm not sure if there's anything else in there that might be doing this.
I have an Alwayson group with 2 Nodes. The SQL Performance counters are always showing on the secondary, but the primary is inconsistent.
I loaded the counters on the primary (unlodctr and lodctr ), restarted the SQL service, and confirmed that the counters were there. 2 days later the counters were gone again. I loaded them again. A week later we had maintenance, failed over to the secondary, and when we failed back to the primary - the counters were gone again….
We have an issue with our replication configuration when viewed through replication monitor. Parameterised Filters and joined filters don't appear in the gui. However, when we script the publication all the filters are present.
This issue only seems to occur when we have a remote distributor.
I should also point out that we have a merge push topology that uses a custom RMO synchronisation component on a separate server to either the publisher or the distributor. Also all the databases in the topology are called the same name. This has caused us other issues relating to this topology in particular so I raise it here as well although I don't expect it to be the case in this instance.
Any help would be greatly appreciated in clarifying this matter.
I have these two commands that I execute at the end of my stored procedure. I get an email every time I execute this stored procedure whether the select statement returns a value or not. But I only want to get an email if select statement returns an Error value. How can I accomplish this?
set @cmd = 'osql -S server -U user -P psswd -q "set nocount on; select distinct(rtrim(col1)) from ##table where datediff(dd,col2,getdate()) = 1 and (col1 like ''%Error: %'')" -h-1 -w 1025 -o J:MyFolderErrorLogMsg.txt'
I have these two commands that I execute at the end of my stored procedure. I get an email every time I execute this stored procedure whether the select statement returns a value or not. But I only want to get an email if select statement returns an Error value. How can I accomplish this?
set @cmd = 'osql -S server -U user -P psswd -q "set nocount on; select distinct(rtrim(col1)) from ##table where datediff(dd,col2,getdate()) = 1 and (col1 like ''%Error: %'')" -h-1 -w 1025 -o J:MyFolderErrorLogMsg.txt'
