After SP4, Proc W. Cursor Doesn't Release Keylocks
Aug 15, 2006
Existing Stored Procedure, has been running well on SQL since 7.0.
(but needed some tweaking to migrate to 2000).
Now all of a sudden after installing SP4 of SQL 2000,
this process slows down, and SQL Spotlight shows the number of locks
just climbing throughout the processing run.
According to the MS Knowledge Base Articles on KeyLocks .. this was a
problem that was *fixed* in the service pack ... where as for me it is
now broken.
Article ID: 260652
PRB: Nested Loop Join That Uses A "BOOKMARK LOOKUP ...WITH PREFETCH"
May Hold Locks Longer http://support.microsoft.com/kb/260652/
Article ID: 828096
FIX: Key Locks Are Held Until the End of the Statement for Rows That
Do Not Pass Filter Criteria http://support.microsoft.com/kb/828096/
Anybody else have this issue, or have any "eazy" solutions?
The proc cursors thru a list and runs a proc on each item in the "work
list".
This is an existing system
with no plans to turn the process into a set oriented one,
as is going away shortly.
View 4 Replies
ADVERTISEMENT
Sep 6, 2000
Hello.
I'm having a perfectly(!) normal stored procedure that returns a Resultset with one row (containing an ID I want).
Not I need that ID in another stored procedure and I can't get it out from the stored procedure.
exec @blabla = MyProc -- works well if I use return
exec MyProc @blabla -- works using OUTPUT keyword
But neither of these examples works with a CURSOR as the @blabla.
Do I need to specificly pass a cursor as a return value, wich would give me bellyache, or can I do something like this:
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FOR exec MyProc
Thanks for any help!
Daniel Ronnqvist, Stockholm
View 2 Replies
View Related
Apr 25, 2002
Can I do something like
CREATE PROCEDURE ProcA
AS
DECLARE @temp CURSOR
BEGIN
EXECUTE ProcB @temp OUT
END
GO
I am sure that this code is not right. But, if someone can tell me a better way to solve this, I would apprciate it.
AB
View 1 Replies
View Related
Aug 28, 2007
---Master query (Assuming this will display 20 rows) we are dealing with one single table that we need to pivot.
select id,fname,lname,sponsor from masterfile where id='TARZAN'
---from those 20 rows there is id that sponsored some one else
---explain: assuming ID=SHAGGY FNAME=Shaggy LNAME=Scooby (was sponsored by Tarzan)
---but Shaggy has sponsored 2 others
select id,fname,lname,sponsor from masterfile where id='SHAGGY'
---will display 3 rows and if from one of those 3 others that belongs to shaggy
---I also want to get their information ID,fname,lname
---This can go up to 10 per saying is like building a Tree with branches and leaves under those branches
---Explain:
---Let's assume that we have an OAK Tree that has 4 main branches
---and out of those 4 main branches 2 of them have other branches with leaves under it
--I would like to do this process in a cursor (Store Proc) is possible
--the way I have it now taking way too long
--because in within so many (do while loop)
TIA
Please pardon me, I could not find better layout to explain this.
View 4 Replies
View Related
Sep 20, 2007
Hi all,
I have a parent-child table, and i want to copy subtrees of it, so for instance this would be the starting point:
(id, parentId, label)
0, null, World
1, 0, US
2, 1, NY
3, 0, UK
4, 3, London
now i want to copy object 3 (UK) and it's children, so i would get
0, null, World
1, 0, US
2, 1, NY
3, 0, UK
4, 3, London
5, 0, UK_copy
6, 5, London_copy
I have this sproc:
Code Snippet
alter proc CopyObject
(@ObjectId int,
@NewParentId int)
as
declare @NewId int,
@NewName varchar
select @NewId = max(Id) + 1 from Object
select @NewName = [Name] + 'copy' from [Object] where Id = @ObjectId
-- copy object
INSERT INTO [Object]
([Id]
,[Name]
,[ParentId]
select @NewId,
@NewName,
@NewParentId
from [Object]
where Id = @ObjectId
-- copy children and set their parent to the newly created object
declare c cursor fast_forward for
select Id
from [Object]
where ParentId = @ObjectId
declare @ChildId int
open c
fetch next from c into @ChildId
while @@fetch_status = 0
begin
exec CopyObject
@ObjectID = @ChildId,
@NewParentId = @NewId
fetch next from c into @ChildId
end
close c
deallocate c
But htis throws an error that the cursor already exists:
Msg 16915, Level 16, State 1, Procedure CopyObject, Line 66
A cursor with the name 'c' already exists.
Msg 16905, Level 16, State 1, Procedure CopyObject, Line 72
The cursor is already open.
I've tried to think of an approach without cursors, but i can't figure it out. Because on the first pass, the new parentId will be the same as the parentId of the object to be copied. But the copies of the children of this first original object should have the parentid set to id of the copied object, and so all the way down the tree.
Any ideas?
Thanks in advance,
Gert-Jan
View 3 Replies
View Related
Feb 19, 2005
Has anyone ever tried to use a cursor as an output variable to a stored proc ?
I have the following stored proc - CREATE PROCEDURE dbo.myStoredProc
@parentId integer,
@outputCursor CURSOR VARYING OUTPUT
AS
BEGIN TRAN T1
DECLARE parent_cursor CURSOR STATIC
FOR
SELECT parentTable.childId, parentTable. parentValue
FROM parentTable
WHERE parentTable.parentId = @parentId
OPEN parent_cursor
SET @outputCursor = parent_cursor
DECLARE @childId int
DECLARE @parentValue varchar(50)
FETCH NEXT FROM parent_cursor INTO @childId, @parentValue
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT childTable.childValue
FROM childTable
WHERE childTable.childId = @childId
FETCH NEXT FROM parent_cursor INTO @childId, @parentValue
END
CLOSE parent_cursor
DEALLOCATE parent_cursor
COMMIT TRAN T1
GOAnd, I found that I had to use a cursor as an output variable because, although the stored proc returns a separate result set for each returned row in the first SQL statement, it did not return the result set for the first SQL statement itself.
My real problem at the moment though is that I can't figure a way to get at this output variable with VB.NET.Dim da as New SqlDataAdapter()
da.SelectCommand = New SqlCommand("myStoredProc", conn)
da.SelectCommand.CommandType = CommandType.StoredProcedure
Dim paramParentId as SqlParameter = da.SelectCommand.Parameters.Add("@parentId", SqlDbType.Int)
paramParentId.Value = 1
Dim paramCursor as SqlParameter = daThread.SelectCommand.Parameters.Add("@outputCursor")
paramCursor.Direction = ParameterDirection.OutputThere is no SqlDataType for cursor. I tried without specifying a data type but it didn't work. Any ideas?
Thanks
Martin
View 6 Replies
View Related
Sep 10, 2001
I have a stored Procedure that is looping through multiple cursors.
It is never finding any records in any curosr that is using a local variable in the where clause...Help
Alter Procedure ProjectedIncome
As
SET ROWCOUNT 0
Declare
-- Date types
@startdate smalldatetime
,@enddate smalldatetime
,@ProjectedDate smalldatetime
,@termination smalldatetime
,@effectivedate smalldatetime
-- Integer
,@Nums int
,@nums2 int
,@ClientId int
,@AssetId int
,@ProductID int
,@Policies int
,@product int
,@Per int
,@Projected int
-- String
,@debugtext varchar(150)
,@productid2 varchar(15)
-- float
,@rate float
,@Cap float
--bit
,@Override bit
--Money
,@AnnualPremium Money
,@Value Money
,@Premium Money
,@PaymentAmount Money
--Doubles
,@PremCalc int
,@HoldPrem int
,@HoldCom int
,@CumBal int
,@CumPrem int
,@MonthlyPrem int
,@XBal int
,@CapPrev int
,@PremTier int
,@Incriment int
--Declare cursor for System Variables
DECLARE SystemVar_cur cursor for
SELECT ProjectionStartDate,ProjectionEndDate from SystemVariables
--Declare the Cursor for Asset Definitions
declare AssetDef_cur cursor for
SELECT termination,effectivedate,ClientID,AnnualPremium,A ssetID,ProductID,Policies from AssetDefinitions
--Declare cursor for CommisionDefinitions
declare CommisionDef_cur cursor for
Select a.product,a.per,a.cap,a.rate,a.value from CommisionDefinitions a where a.product = @ProductId2;
--Declare cursor for projections
declare projections_cur cursor for
Select a.override,a.premium,a.paymentamount from projections a where a.date = @ProjectedDate and assetid = @AssetId;
-- Select from the SystemVariables Table
OPEN SystemVar_cur
FETCH SystemVar_cur INTO @startdate,@enddate
CLOSE SystemVar_cur
DEALLOCATE SystemVar_cur
-- Open the AssetDefinition File and loop through
--
INSERT INTO debug_table VALUES('Open the Asset Cursor')
Open AssetDef_cur
Fetch AssetDef_cur INTO
@termination
,@effectivedate
,@clientId
,@AnnualPremium
,@assetId
,@ProductId
,@Policies
While @@fetch_status = 0
Begin-- begin AssetDefinitions Loop
--If Asset is not Terminated
If @termination IS NULL
BEGIN-- begin @termination IS NULL
SET @MonthlyPrem = (@AnnualPremium/12)
SET @debugtext = 'MonthlyPrem = AnnualPrem' + CAST(@AnnualPremium as Char) + '/12'
INSERT INTO debug_table VALUES(@debugtext)
If @effectivedate > @startdate
SET @ProjectedDate = @effectivedate
Else
SET @ProjectedDate = @startdate
-- end if
SET @PremCalc = 0
SET @CumBal = 0
SET @XBal = 0
SET @HoldCom = 0
-- Fetch the Projection Record
open projections_cur
fetch projections_cur INTO
@override,@premium,@paymentamount
If @@fetch_status = 0
BEGIN
IF @override = 1
BEGIN-- begin @override = 1
SET @CumPrem = @premium
SET @CumBal = @paymentamount
SET @HoldPrem = @CumPrem
SET @HoldCom = @CumBal
END-- end @override = 1
Else
SET @HoldPrem = @MonthlyPrem
END
CLOSE projections_cur
While @ProjectedDate <= @enddate
BEGIN-- begin While @ProjectedDate <= @enddate
SET @CapPrev = 0 --reset cap balance
SET @XBal = 0
SET @debugtext = 'Begin Get Commision Record For Product' + CAST(@productID as CHAR)
INSERT INTO debug_table VALUES(@debugtext)
SET @productid2 = @productid
SET @PremTier = @HoldPrem
---NOW Open the CommisionDef table
OPEN CommisionDef_cur
FETCH CommisionDef_cur INTO
@product,@per,@cap,@rate,@value
IF @@fetch_status <> 0
BEGIN
SET @debugtext = 'ERROR? ' + CAST(@@error as Char)
INSERT INTO debug_table VALUES(@debugtext)
END
WHILE @@fetch_status = 0
BEGIN-- begin While CommisionDef Fetch = 0
SET @debugtext = 'Found Commision Record' + CAST(@product as Char)
INSERT INTO debug_table VALUES(@debugtext)
If @Per = 0
BEGIN-- begin If @Per = 0
SET @Incriment = @Cap - @CapPrev
If @PremTier > @Incriment
SET @XBal = @XBal + (@Incriment * @Rate)
Else
BEGIN-- begin @PremTier > @Incriment
If @PremTier >= 0
SET @XBal = @XBal + (@PremTier * @Rate)
END-- end @PremTier > @Incriment
SET @debugtext = 'XBal ' + CAST(@XBal as CHAR(10))
INSERT INTO debug_table VALUES(@debugtext)
SET @CapPrev = @Cap
SET @PremTier = @PremTier - @Incriment
END-- end If @Per = 0
Else
BEGIN-- begin If @Per <> 0
SET @XBal = @value * @Policies / 12
SET @HoldCom = 0
SET @PremCalc = 0
SET @CumBal = @XBal
SET @debugtext = 'CumBal' + CAST(@CumBal as Char)
INSERT INTO debug_table VALUES(@debugtext)
SET @HoldPrem = @Policies
END-- end If @Per <> 0
FETCH CommisionDef_cur INTO
@product,@per,@cap,@rate,@value
END-- end While CommisionDef Fetch = 0
CLOSE commisionDef_cur
-- Fetch the Projection Record
open projections_cur
fetch projections_cur INTO
@override,@premium,@paymentamount
IF @@fetch_status = 0
BEGIN -- begin Projection Fetch = 0
IF @override = 1
SET @HoldCom = @CumBal
ELSE
-- If not overridden, set the fields to Update the projection File
BEGIN-- begin @override <> 1
SET @Projected = ((@XBal - @HoldCom) * 100 + 0.5) / 100
SET @Premium = @HoldPrem - @PremCalc
UPDATE projections SET projected = @projected, premium = @Premium where assetid=@AssetID and date = @ProjectedDate
SET @HoldCom = @XBal
END-- end @override <> 1
END-- end Projection Fetch = 0
ELSE
BEGIN -- Begin Projection Fetch else
IF @@fetch_status = -1
BEGIN-- begin Projection Fetch = -1
SET @Projected = ((@XBal - @HoldCom) * 100 + 0.5) / 100
SET @Premium = @HoldPrem - @PremCalc
SET @debugtext = '((xbal - holdcom)*100 + 0.5)/100 ' + CAST(@Xbal as char) + ' , ' + CAST(@holdcom as CHAR)
INSERT INTO debug_table VALUES(@debugtext)
SET @debugtext = 'Projection Record Not Found so Write it'
INSERT INTO debug_table VALUES(@debugtext)
--Projection record was not found so write it
SET @override = 0
INSERT INTO Projections
(AssetId,Date,Premium,Projected,Override,Payment,P aymentAmount)
VALUES(@AssetId,@ProjectedDate,@Premium,@Projected ,@override,0,0)
SET @HoldCom = @XBal
END-- end Projection Fetch = -1
END -- end Projection Fetch else
CLOSE projections_cur
SET @ProjectedDate = DateAdd("m", 1, @ProjectedDate)
SET @PremCalc = @HoldPrem
-- Fetch the Projection Record
OPEN projections_cur
FETCH projections_cur INTO
@override,@premium,@paymentamount
IF @override = 1
BEGIN-- begin @override = 1
SET @CumBal = @paymentamount
SET @HoldPrem = @HoldPrem + @CumPrem
END -- end @override = 1
ELSE
SET @HoldPrem = @HoldPrem + @MonthlyPrem
CLOSE projections_cur
END-- End the While ProjectedDate <=@enddate
END --End the If Termination is NULL
Fetch AssetDef_cur INTO
@termination
,@effectivedate
,@clientId
,@AnnualPremium
,@assetId
,@ProductId
,@Policies
END
CLOSE AssetDef_cur
DEALLOCATE AssetDef_cur
DEALLOCATE projections_cur
DEALLOCATE CommisionDef_cur
return
View 1 Replies
View Related
May 26, 2015
I have to modify a stored procedure that is written by someone else.Basically the stored prcoedure uses a cursor to fetch the data from the table and then insert that data in another table. While fetching the code form another table, it also gets some distinct columns from another table Below is my code:
Declare data_cursor cursor for
Select emp_no, emp_name, event_date, Test_no, Code, Test_result
From test_table1
order by emp_no
[code]...
The reason, I have to modify the above stored proc because now because of application changes, I am getting around 50 distinct userID from test_table1 so the above subquery(SELECT @ProcessName = (select distinct userID from test_table1) won't work. How can I loop through the above stored proc so that each @ProcessName can get inserted in table TESTTable2 so in other words
I want to pass each userId one at a time and insert it in table test_table1 and other subsequent tables. I can declare another cursor to accomplish this, but I was wondering if there is any better way to rewrite this stored proc and not use the cursor at all.because of my application changes all these three statements above are throwing the error:
SELECT @ProcessName = (select distinct userID from test_table1)
SELECT @FileProcess = 'EW' + @ProcessName
Select @TestProcess = (Select distinct userID from testTable1) + 'TXT'
View 8 Replies
View Related
Apr 14, 2015
I have a table that has the following data
ID
---
101
102
105
108
124
189
I need to call a stored proc for each of the IDs above. Our existing code which has a cursor to loop through the table and call the proc for each value is proving to be a performance nightmare. Is there an alternate method that I can use to avoid cursor and make it more efficient?
View 2 Replies
View Related
Jul 20, 2005
In SQL how can a cursor be opened to iterate the result set returnedfrom a stored proc-Rahul SoodJoin Bytes!
View 1 Replies
View Related
Jul 10, 2014
I have a situation where I need to call a stored procedure once per each row of table (with some of the columns of each row was its parameters). I was wondering how I can do this without having to use cursors.
Here are my simulated procs...
Main Stored Procedure: This will be called once per each row of some table.
-- All this proc does is, prints out the list of parameters that are passed to it.
CREATE PROCEDURE dbo.MyMainStoredProc (
@IDINT,
@NameVARCHAR (200),
@SessionIDINT
)
AS
BEGIN
[Code] ....
Here is a sample call to the out proc...
EXEC dbo.MyOuterStoredProc @SessionID = 123
In my code above for "MyOuterStoredProc", I managed to avoid using cursors and was able to frame a string that contains myltiple EXEC statements. At the end of the proc, I am using sp_executesql to run this string (of multipl sp calls). However, it has a limitation in terms of string length for NVARCHAR. Besides, I am not very sure if this is an efficient way...just managed to hack something to make it work.
View 9 Replies
View Related
Aug 12, 2015
In MSDN file I read about static cursor
STATIC
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in
tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications
It say's that modifications is not allowed in the static cursor. I have a questions regarding that
Static Cursor
declare ll cursor global static
for select name, salary from ag
open ll
fetch from ll
while @@FETCH_STATUS=0
fetch from ll
update ag set salary=200 where 1=1
close ll
deallocate ll
In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says modifications is not allowed in the static cursor.But I am able to update the data using static cursor.
View 3 Replies
View Related
Jul 20, 2005
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin
View 1 Replies
View Related
Sep 20, 2007
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.
Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31
Could not complete cursor operation because the set options have changed since the cursor was declared.
Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32
Could not complete cursor operation because the set options have changed since the cursor was declared.
Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153
Cursor is not open.
here is the stored procedure:
Alter PROCEDURE [dbo].[sp_MSforeachsp]
@command1 nvarchar(2000)
, @replacechar nchar(1) = N'?'
, @command2 nvarchar(2000) = null
, @command3 nvarchar(2000) = null
, @whereand nvarchar(2000) = null
, @precommand nvarchar(2000) = null
, @postcommand nvarchar(2000) = null
AS
/* This procedure belongs in the "master" database so it is acessible to all databases */
/* This proc returns one or more rows for each stored procedure */
/* @precommand and @postcommand may be used to force a single result set via a temp table. */
declare @retval int
if (@precommand is not null) EXECUTE(@precommand)
/* Create the select */
EXECUTE(N'declare hCForEachTable cursor global for
SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = ''PROCEDURE''
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '
+ @whereand)
select @retval = @@error
if (@retval = 0)
EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0
if (@retval = 0 and @postcommand is not null)
EXECUTE(@postcommand)
RETURN @retval
GO
example useage:
EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"
GO
View 7 Replies
View Related
Jun 20, 2007
Hello,
I've tried searching for details on the next release date of SSRS, but can't find any. Has anyone heard anything regarding the next release? There are at least a dozen features that I really would like to have, and when searching for each, the MSFT developer typically responds with "that feature is targeted for a future release". So I'd like to know if anyone has any idea, even heard a rumor, regarding the next release timeline. Or, is there a better place to search for this information?
Thanks!
Michael
View 1 Replies
View Related
Jun 15, 2006
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View 3 Replies
View Related
Jun 26, 1998
anyone have any ideas on when beta 3 will be available?
View 3 Replies
View Related
Jul 8, 2004
Check this out
this is the replacement for MSDE 2000
SQL Server 2005 Express Edition (http://lab.msdn.microsoft.com/express/sql/default.aspx)
Top 10 cool things about SQL Server Express Edition (http://lab.msdn.microsoft.com/express/sql/top10/default.aspx)
SQL Server Express Books Online (http://www.microsoft.com/downloads/details.aspx?FamilyId=2ADBC1A8-AE5C-497D-B584-EAB6719300CD&displaylang=en)
by the way if you follow the first link, you will find a pretty accurate photo of rdjabarov...
View 14 Replies
View Related
Nov 10, 2006
If we upgrade an instance to SP2 (CTP), can we simply apply SP2 (Release) on top of the SP2 (CTP)? Or will we need to uninstall the whole instance, re-install SQL 2005 and only then apply SP2 (Release)?
I notice in section "1.1 Overview of SQL Server 2005 SP2 Installation" of the documentation the statement:
"During installation, SQL Server 2005 SP2 Setup will list all installed components of SQL Server 2005 and allow you to select the components to upgrade, including components that have already been upgraded to SP2. For more information, see the SP2 Setup documentation."
I'm hoping that this means you can apply the release SP2 on top of a ctp SP2 with the result being full release SP2 versions. I've read the "SP2 Setup documentaiton" and it did not clarify for me.
Thanks!
Bob Hodgman
P.S. - sorry for the duplicate posting... I think posting this as a reply to the announcement (as I originally did) didn't get it much attention.
View 1 Replies
View Related
Oct 27, 2006
I can't find any timeframe for the 1.0 release of SQL Server Everywhere. Is this information available?
View 6 Replies
View Related
Mar 24, 2008
Dear all,
We are working on C++ in eVC++ 3.0 environment (CE 3.0) with SQL CE 2.0.
While executing the SQL CE commands like GetData(),GetNextRows() and Seek(), 8 KB keeps increasing each time in the memory. We are releasing the accessors,columnsinfo,rowsetchange,rowset handles properly.But still the memory increases without being released.
Have we missed out anything?
Kindly help us in this regard.
Regards,
Sasi.
View 3 Replies
View Related
Sep 7, 2006
I am using SQL Server 2000 SE. When I used my application, the SQL server memory go on increasing & it will never come down. Application runs very slowly. Can anybody suggest how to release memory from sql server.
Thanks in advance.
View 3 Replies
View Related
Jan 11, 2007
I see that a CTP for SP2 came out in December. When will the service pack itsself be released?
View 3 Replies
View Related
Sep 8, 2005
We alter the database schema that our production application uses with almost every release. Is there a way to basically remove the replication and re-do it for all tables / views at one time? Then we could just "rebuild" the replication with every release. We have about 200 tables and 200 views that have many dependencies. I'm sure it could be done with scripting and the stored procedures, but I'm new at this and not sure where to start. Any ideas? Thanks!
View 3 Replies
View Related
Feb 23, 2007
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View 1 Replies
View Related
Sep 9, 1998
I`ve been following the newsgroups, and the consensus had seemed to be
that 7.0 would be released around November. However, I spoke to a Microsoft
partner last week who told me that the release date would be sometime in
the second quarter of 1999. Does anyone know whether if this is true/untrue?
View 1 Replies
View Related
Nov 26, 2001
Hello,
I am trying to remember the release date for SQL7 SP3 to resolve a problem. Please could somebody let me know?
Thanks in advance,
Chris.
View 1 Replies
View Related
Dec 19, 2007
The former programmer wrote this stored procedure. It haven't been run for a while, so I was given the assignment to get it working. When I ran the stored procedure, it took almost 9 hours. Then I found that I can't access a few tables, so my guess it there is some issues with table locking. The stored procedure use this...
Code:
BEGIN TRAN
--blah blah
COMMIT TRAN
ERROR_HANDLER:
ROLLBACK TRAN
Obviously there seem to be a logic error in the middle of the script while running the stored procedure. So, how do I cancel the transaction and unlock the table? I'm unable to access the few tables.
Also, does rebooting the computer helped to release the transaction or table locking?
Thanks...
View 3 Replies
View Related
Jul 20, 2005
Hi,I am having an issue with SQL server that slows everything to a crawl,and makes almost any query impossible to complete.Here are the symptoms:I stop SQL, I start SQL. The task manager shows SQL starting up andquickly allocates 50 MB of memory. I then open SQL Manager and I canopen up the instance of SQL Server and I expand to see all of thedatabases, memory rises to about 70MB allocated. I then expand adatabase and view a list of tables. The memory allocation quicklyrockets to 860+ MB and any attempt to query they database results inhuge hang times.I have MacAfee anti-virus in place and it is constantly scanning forviruses, so I don't think it is a virus. The log files do not appearto be large. I have done everything I know to do to resolve this. Anyhelp would be much appreciated.Does anyone have any ideas?Thank you for taking the time to read this.Tod
View 2 Replies
View Related
Jul 20, 2005
Hi,Our DBA group is debating production release methodology. We alwaysperfect our deployment package (typically a script) against thedatabase in a Staging environment, before executing the packageagainst Production. One side argues that the safest approach is tocreate a script containing all schema changes, data changes, storedprocs, functions, etc. Run that script against Staging until it iserror-free. Then, when you run it against Production on Release Nightyou know it will also be error-free, since Staging is a copy ofProduction. However, any changes to the deployment, such as updatesto procs (and there are always a bunch in the pre-deployment period)must be manually implemented on the script.The other side wants to take advantage of the .NET environment. Wekeep all procs, views, and functions in a Release folder on VSS,execute a Get Latest Version (Recursive) from VisualStudio, and runagainst Staging/Production. Any changes to the procs areautomatically propagated from VSS to VS, and there is no manualediting of the script. You still need a script for table and datachanges. The "script everything" side of the debate feels this methodis not as reliable, especially since when views are nested, you willget compile errors and must execute the run-on several times until allthe views compile. The "script" side feels that any errors areunacceptable in a Production environment.What is the opinion out there?Thanks!
View 5 Replies
View Related
Dec 11, 2006
We have a product that uses SQL Express that is ready and waiting to go to market. It absolutely requires Vista compatibility on day one. When will SP2 be released? The UAC problem has been known for months.
We cannot distribute the November CTP, of course. Manually correcting SQL Express installs on Vista using the recommended workaround is impractical to say the least.
View 1 Replies
View Related
Apr 24, 2007
Is there a way to tell what release of SQL Server 2005 is installed without the management studio? I was working on a server with SQL 2005 installed, but I wasn't sure if it was full or express. The person who installed SQL didn't include the management studio. Thanks in advance.
View 5 Replies
View Related
