I have a asp.net page that with a button which is for user to run a calculation by calling a stored proc in SQL Server 2005. The calculation may take up to an hour or more. When user click on the button to run the stored proc...the browser will wait for the response from the server until the calculation is complete. Because of the calcul...
Hi, I have simple "insert into.." query which is taking around 40 seconds to execute. It simply takes records from one table and inserts into another. I have index on F1 and BatchID on the tbl_TempCatalogue table 146624 records are effected. select itself is not slow insert into is slow. The complete query is insert into tbl_ItemPr...
I just installed SQL Server 2005 and it installed Visual Studio 2005 too. What's the point of this? Is there an option in the SQL Server installation that can prevent this. If I uninstall Visual Studio now will it mess anything up? What if I now install Visual Studio 2008 too? ...
I want get the maximum value for this record. Please help me: SELECT rest.field1 FROM mastertable AS m INNER JOIN ( SELECT t1.field1 field1, t2.field2 FROM table1 AS T1 INNER JOIN table2 AS t2 ON t2.field = t1.field WHERE t1.field3=MAX(t1.field3) -- ...
I'm trying to debug a fairly complex stored procedure that joins across many tabls (10-11). I'm seeing that for a part of the tree the estimated number of rows drasticly differs from the actual number of rows - at its worst SQL server estimates that 1 row will be returned, when in actuality 55,000 rows are returned! I'm trying to work o...
I'm trying to diagnose a slow stored procedure (see this question) and I've noticed that for my auto-generated stats (the ones named things like _WA_Sys_0000000A_0D0FEE32) I cant view the detailed histogram. If I click on the "Details" tab I just get the message: No statistics information available. If I click on the details tab for a...
I have a stored procedure which filters based on the result of the DATEADD function - My understanding is that this is similar to using user defined functions in that because SQL server cannot store statistics based on the output of that function it has trouble evaluating the cost of an execution plan. The query looks a little like this...
One of user name tried login many times and failed. Sql server 2005 locked account automatically. How can I unlock user account in SQL Server 2005 Management Studio? ...
I need to store binary files in a varbinary(max) column on SQL Server 2005 like this: FileInfo FileInfoId int, PK, identity FileText varchar(max) (can be null) FileCreatedDate datetime etc. FileContent FileInfoId int, PK, FK FileContent varbinary(max) FileInfo has a one to one relationship with FileContent. The FileText is meant...
Hi I am looking for A solution to update values in table1 only if the values change. I mean compare to tableb and update only the changed values ...
I have a loginAudit table and I am trying to get a count for all logins for each day. What I'd like to do is have days where there are no logins return their day and a login count of 0. Currently no row is returned for days with no logins. Could be this isn't possible and have to fill in empty groups in the app after query results re...
Hi , I have got an "A" DATABASE and "B" DATABASE.When i inserted new record to "X TABLE" in "A" DATABASE i wanna insert another record to "X2 Table" in "B" DATABASE with Trigger Function of SQL SERVER 2005.How can i do this ? Can someone explain me this. How can i connect another Database and insert record specific table. Thanx everyon...
Guys, The topic of how to audit tables has recently sprung up in our discussions... so I like your opinion on whats the best way to approach this. We have a mix of both the approaches (which is not good) in our database, as each previous DBA did what he/she believed was the right way. So we need to change them to follow any one model. ...
I started a transaction using BEGIN TRANSACTION in Management Studio but I forgot to ROLLBACK or COMMIT it for about 10 minutes. I freaked out and went back to ROLLBACK my changes. Did this rollback all queries that went through the server during that time or just through my user/connection? ...
I have an application which has two database servers: one is the development database server and the other one is a production database server. Accidentaly, a couple of months back I published the application for use and it was pointing towards the development database. Since it was pointing to the development database, all the informa...
Consider the need to create a resultset of dates. We've got a start and end dates, and we'd like to generate a list of dates in between. DECLARE @Start datetime ,@End datetime DECLARE @AllDates table (@Date datetime) SELECT @Start = 'Mar 1 2009', @End = 'Aug 1 2009' --need to fill @AllDates. Trying to avoid loop her...
Hi, How do I Select records between two dates in two columns? Select * From MyTable Where 2009-09-25 is between ColumnDateFrom to ColumnDateTo I have a date (2009-09-25) and I like to select the records that is in the timeframe ColumnDateFrom to ColumnDateTo. Sample Record 1 ColumnDateFrom = 2009-08-01 AND ColumnDateTo = 2009-...
Say I write the query: INSERT INTO DestinationTable (ColumnA, ColumnB, ColumnC, etc.) SELECT FROM SourceTable (ColumnA, ColumnB, ColumnC, etc.) And my source table has 22 million rows. SQL server fills up my hard drive, and errors out. Why can't SQL server handle my query? Should I use a cursor and insert a row at a time? PS - it ...
I want to clone multiple tables' rows that have a single primary key (PrimKey). What's the quickest way to clone a row in SQL Server 2005? Here's an example, Clone PrimKey1 to get PrimKey2. So I try the following : INSERT INTO PrimKeys SELECT 'PrimKey2' AS PrimKey,* FROM PrimKeys WHERE PrimKey='PrimKey1' But of course the is...
I've got a stored procedure which runs this to create a new ticket number: INSERT INTO [Test_DB42].[dbo].[TicketNumber] ([On], [By]) VALUES (CURRENT_TIMESTAMP, CURRENT_USER) When I run with a user with db_datareader and execute permissions, I get the Active Directory samAccountName value which is what I want - this user gets a...