Where do I start? My son and I wrote a small ASP.net 2.0 website on XP IIS 5. Works ok with the standard login controls.
I FTP'ed the files up to a Windows server2003. I installed the SQL publishing wizard. I created a .sql file of the data base.
I ran the file in SQL2005 manager to build the data base on SQL express running on the Windows 2003 server. It installed, no errors. great!
The website would not run with Data base connection errors , like files or data base was read only, duplicate database , can not run mydatabase.create. something like that.
I created a new app pool and made sure network service was the account. Gave network server permissions to the folders, it started working great.
Now sit down. I created a few accounts using the website ok, no errors, Great! Then check the database to see if the passwords were encrypted. The new accounts were not there.
But the accounts went somewhere???? Must be two databases???? One hidden?
I detached the database I created with the .sql script file.... to see if I would get an error on the browser. nope still worked. Hmmmm other hidden database still working.... I stopped database server
in the management tool, It still worked....Ok, I'll get the house check for poltergiests.
I went to SERVICES. The service was stopped(SQLEXPRESS).. I changed auto start to manual. Browsed back to the site again, finally an error.
I restarted the service, website worked again, but the database, the only user database, is still detached. Created more user accounts with no error....
Seached the hard drives for .MDF files. Found the file I detached and also the ASPNETDB.MDF that I orginally FTP'ed to the server earlier in the APP_DATA folder of the website. I renamed the
MDF file to see if then I would get an error, yep I did. It then automatcly re-created the MDF file exactly the same size. Two files in the directory now and now a new error in the browser
about a database mismatch. Ok that makes sense. But it will not allow me to rename the new file so I can rename the original.
Question is how does SQLEXPRESS run the ASPNETDB.MDF file in the web folder which is not attached to the database engine?
Why use a SQL pub wizard if ASP.NET creates the database automaticly? Or just "Runs" it? AM I HIGH?
Thanks in advance, before I take drugs and sit in a dark closet.
I need to restore a DB but it was prevented by a background process of "Ghost Cleanup". server is SQL2000 ENT. sp4.
It could not be killed, neither it was stoped after restart the server. Is there a way to change its running schedule and/or to kill it when I need to restore the db?
I have a test environment that we rebuild servers on a regular basis.To streamline the process we use ghost. We will be installing SQL onthe servers and want to build a ghost image with that build. We havetested it by doing the build, loading the data, and then stopping allthe services and setting them to manual. After we Ghost the machine,we start up the services and reset them to Automatic. Seems to work.My question is:Are there any risks? Should I expect any adverse affects?Thanks for all your help!
I just now had the strangest error, for some reason a table in my DB was locked by two processes, there is no use with BEGIN TRANSACTION in the queries that execute on the DB and while it suddnely happaned no heavy process was running... I was just browsing the table with the enterprise manager.
After viewing the processes with sp_lock I found the two processes that caused the locking and killed them, this fixed the problem, but I would like to know what caused it... When viewing the process info with sp_who the status was "sleeping" and in sp_lock in one process the type was "key" and the status was "wait" (mode "s").
I have problem with 'Ghost cleanup' system process. It is locking up my tables and user transactions are keep getting wait status. So is there any way to disable or change the schedule of ghost cleaner? Thanks in advance..
Hello,I have a table 'customers', with 2 records:SELECT * FROM customers;customerID | customerName------------------+-------------------------myFriend | myFriend's Nametest | testing user(2 rows)but when I'm asking about customerID column, I get the answer:SELECT customerID FROM customers;ERROR: column "customerid" does not existWhat happens? I'm using PostgreSQL 8.1.3
I have problem with 'Ghost cleanup' system process. It is locking up my tables and user transactions are keep getting wait status. So is there any way to disable or change the schedule of ghost cleaner? Thanks in advance..
I have a VB program which uses ADODB.Connection to make connection to SQL Server DB. However, under some circumstances, orphan / ghost connections remain in DB even after the VB program closed.
Here are the facts...
(1) Windows Server 2000 + SQL Server 2000 - run the VB program and made connection to the DB, leave it for 3 minutes, the connection closes when the program closes. - run the VB program and made connection to the DB, leave it for 3 hours, the connection closes when the program closes.
(2) Windows Server 2003 + SQL Server 2000 - run the VB program and made connection to the DB, leave it for 3 minutes, the connection closes when the program closes. - run the VB program and made connection to the DB, leave it for 3 hours, the connection does not close even after the program is closed.
Does anyone know if there is any problem with Windows Server 2003 + SQL Server 2000 using ADODB.Connection? Or is there some kind of "timeout" parameter for which I can set to resolve such issue?
I noticed that several connections were blocked by something called Ghost Cleanup (or something like that). I know what the cleanup does, but it often causes blocking for quite a while . . .
why do the following return the same datasets? select * from myTable where myData = '' select * from myTable where myData = ' ' in the first I'm specifically searching empty strings, in the second a sequence of five spaces. Yet both return any and all white character matches? Is this a "feature" of SQL... P.S. I'm using T-SQL
Until a few days ago we didn't even know SPID 1 existed. These are different guys, using old version of Crystal passing crappy queries.
Here's the scenario SPID 1 is running (inefficient select) SPID 2 is waiting 30 minutes pass and SPID 2 is still waiting PAGEIOLATCH_SH SPID 3 fires off
Now SPID 2 is blocking SPID 3, but SPID 2 is still waiting for SPID 1
Finally when SPID 1 finishes, everything catches up. But it appears that there is a period of at least 45 minutes when all SPID's are just sleeping.
My questions: 1. If SPID 1 is just running an inefficient select query, why is it holding up SPID 2 (Which reads and inserts). SPID 1 should have a shared lock on table, this would prevent SPID 2 from insert/update? 2. Is there any steps I can take to give SPID 2 precendence? or is it first come first serve?
Hello,I run the following query on a database (SQL 2005):delete from person where pers_companyid in (select pers_companyid fromcompany where comp_expiry is not null)I wanted to delete all people associated with a company which had anexpiry date. The mistake I made was that the column pers_companyiddoes not exist. It should have been comp_companyid.However, the query ran anyway and deleted all records from my persontable? If I run the subquery on its own then it doesn't run as thecolumn is missing.Shouldn't I have got an error running this query?Thanks,David
I found this on http://msdn2.microsoft.com/en-us/library/86773566.aspx which i am hoping may be able to resolve my problem but I am not sure I actually understand it. Where it shows // Start a local transaction. transaction = connection.BeginTransaction("SampleTransaction"); is the "SampleTransaction" nothing more then just a name of the transaction that is being created through this code or is it actually pointing to something?I believe that I understand the rest. Oh and in case you have a better way of doing what I am needing here is the SQL Query that is pulling info for me to display on a pageSELECT p.product_ID, p.class_ID, p.category_ID, p.product_name, p.product_desc, p.product_image, p.product_dimension, p.product_o1, p.product_o2, p.product_o3, p.product_ac, p.product_ph, p.product_photo, pcl.class_name, pca.category_nameFROM products AS p INNER JOIN productClass AS pcl ON p.class_ID = pcl.class_ID INNER JOIN productCategories AS pca ON p.category_ID = pca.category_IDBasically I am using the transaction to perform multiple updates seeing as how I will need to update multiple tables. private static void ExecuteSqlTransaction(string connectionString){ using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlCommand command = connection.CreateCommand(); SqlTransaction transaction; // Start a local transaction. transaction = connection.BeginTransaction("SampleTransaction"); // Must assign both transaction object and connection // to Command object for a pending local transaction command.Connection = connection; command.Transaction = transaction; try { command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"; command.ExecuteNonQuery(); command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')"; command.ExecuteNonQuery(); // Attempt to commit the transaction. transaction.Commit(); Console.WriteLine("Both records are written to database."); } catch (Exception ex) { Console.WriteLine("Commit Exception Type: {0}", ex.GetType()); Console.WriteLine(" Message: {0}", ex.Message); // Attempt to roll back the transaction. try { transaction.Rollback(); } catch (Exception ex2) { // This catch block will handle any errors that may have occurred // on the server that would cause the rollback to fail, such as // a closed connection. Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType()); Console.WriteLine(" Message: {0}", ex2.Message); } } }}
I am new to triggers.I am unsure why on the SELECT statement the ROLLBACK is enforced. I thought this would be only for when an attempt is made to insert the data. Also what is the pupose of IF @counter = 1 When does this counter change value? USE Northwind GO CREATE TRIGGER PriceCheck ON [Order Details] FOR INSERT AS DECLARE @counter int SET @counter= @@ROWCOUNT DECLARE @unitprice money DECLARE order_details_insert_cursor CURSOR FOR SELECT Unitprice FROM inserted IF @counter = 1 BEGIN IF(SELECT UnitPrice FROM inserted) > 300 -- If the price entered is greater than 300 BEGIN --print a warning PRINT 'Cannot enter price greater than 300' --Take back the command ROLLBACK TRANSACTION END END ELSE BEGIN OPEN order_details_insert_cursor FETCH NEXT FROM order_details_insert_cursor INTO @unitprice WHILE @@FETCH_STATUS = 0 BEGIN if @unitprice > 300 -- If the price entered is greater than 300 BEGIN --print a warning PRINT 'Cannot enter price greater than 300' --Take back the command ROLLBACK TRANSACTION RETURN --Exit trigger immediately END FETCH NEXT FROM order_details_insert_cursor INTO @unitprice END CLOSE order_details_insert_cursor END DEALLOCATE order_details_insert_cursor
I've got some questions about xp_cmdshell and was hoping someone could explain some things to me. I've written a trigger for a table that uses 'xp_cmdshell' to launch a VB.Net application. The syntax is correct and it "seems" to work but in the way I intended. When the app launches there is no GUI present but when I check the task list I see that the exe has been launched. This intrigued me so I started playing with it in Query Analyzer. When I run the commandexec master.dbo.xp_cmdshell 'C:WINDOWSsystem32otepad.exe'I seen a process for notepad show up on my processes list started by 'system' as opposed to myself and notepad still doesn't open up for me to see. I also noticed that the query executes until I kill that particular process. I'm guessing cause it's waiting on the tranaction to complete. What I want to know though is why can't I see the program when I open like this? Is it because 'system' is the user behind it or is there some extra parameter to throw in the xp_cmdshell?
Is there some equivalent to MySQL's EXPLAIN function? (or some utility that does the same thing?)
Basically the function takes a query and will return a list of the tables in the query and the number of rows that would need to be checked. It's used to check how well the indexes are set. (If a table returns a very high number, that could be a bottleneck for the query).
Hello All,The following script is reproducing the problem assuming you haveNorthwind database on the server.Please note it gives you the error message on line 12.USE tempdbGOsp_addlinkedserver 'Test17'GOsp_setnetname 'Test17', @@SERVERNAMEGOIF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =object_id(N'[dbo].[This_works]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)DROP PROCEDURE [dbo].[This_works]GOCREATE PROCEDURE This_works@UseLinkedServer bit = 0-- WITH RECOMPILE -- Does not helpASSET NOCOUNT ONIF @UseLinkedServer = 1 -- Linked ServerBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.OrdersENDELSE -- LocalBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.OrdersSELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?ENDBEGIN TRANSACTIONSelect 'Line 25'SELECT COUNT(*) FROM dbo.Orders_TMPCOMMITgoIF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =object_id(N'[dbo].[This_does_not]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)DROP PROCEDURE [dbo].[This_does_not]GOCREATE PROCEDURE This_does_not@UseLinkedServer bit = 0-- WITH RECOMPILE -- Does not helpASSET NOCOUNT ONIF @UseLinkedServer = 1 -- Linked ServerBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.OrdersENDELSE -- LocalBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders--SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?ENDBEGIN TRANSACTIONSelect 'Line 25'SELECT COUNT(*) FROM dbo.Orders_TMPCOMMITGOPRINT 'This_works'EXECUTE This_works 0PRINT ' 'PRINT 'This_does_not'EXECUTE This_does_not 0Thanks for any help or hint,Igor Raytsin
Hello.Does someone of you know if is there a 'explain plan' like function in SQLServer (similar to explain plan in Oracle or DB2)If so then how it works, where stores data and how it can be retrieved?Best regardsBagieta=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=dbDeveloper - Multiple databases editorhttp://prominentus.com=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Dear experts, Recently i got an error msg looks like this: you cannot use subqueries within a sqldatadpter except the subquery is introduced with EXISTS. Well, actually i was using IN. I know I can revise my query sting to use INNER JOIN or such stuff just to remove the nested queries. But i'm realllllly curious why it's not allowed?? Really appreciate it if some expert can tell me. Thanks in advance
I have a complex select statement that is used in several stored procedures. I decided that instead of having x number of T-SQL scripts with the same exact select statement that I would to put this query into a view and then do a select * from View. Recently an instructor told me that this was a bad idea and that anyone who uses a select * from anything should be fired. When I asked for his reasoning his response was to say the least abnoxious. I can understand why a Select * from Table might be a bad idea as the table definition can change, but the chances of a view changing seems much less likely. Is a view a good idea in this case? Is the Select * from View really a bad idea? Thanks
Hi I am in the process of creating a new db in sql. In my users table I wish to set the UserIds as Integer datatype. It defualts on precision 4. Does this mean that when the column auto increments as its my primary key with a seed of one, my highest number allowed in the table would be row 9999. ???
Also if you where to store a phone number in your db, what column type would you give it. I have used varChar but its all numbers i want to store. Would this suffice.
If you have a table named Users then using mysql you can run EXPLAIN Users; to get all the fields & types, etc. How would you do this same thing in SQL 2000?
