|
Hi all,
I made one procedure in which i use sp_cursoropen procedure and it has one parameter for rowcount. When i execute the procedure, it does not return rowcount, i mean it returns -1 even there is 20 records in my table.
Can anybody help me...
Thanks
Krishnraj
|
|
|
|
|
Hi
Put return at the end of you're stored procedure
declare @counter int
select @counter = count(*) from mytable
return @counter
or
set rowcount as output parameter and print it.
or
set nocount off
greetz
|
|
|
|
|
Hi topcatalpha,
Thanks for reply,
but sp_cursoropen has one parameter which returns the total count of table. so no need to fire extra query as u said.
sp_cursoropen is sql server 2000's in built system procedure
i think there may be some other settings.
any way thanks buddy...
Krishnraj
|
|
|
|
|
With what parameters are you callin this sp. It would depend on the cursor type if the rowcount can be returned. For example if the cursor is forward only, AFAIK the actual rowcount cannot be determined.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi Mika,
Below is my query where u find variable @mainQuery. which is my table fetch query with Inner join with 3 tables.
Declare @cursor int, @rowcount int
Exec sp_cursoropen @cursor output,@mainQuery,16,8196,@rowcount output
Select @rowcount
Exec sp_cursorfetch @cursor,32,@RowNumber,@PageSize
Exec sp_cursorclose @cursor
Do u think that there may be a problem with inner join query?
Thanks for replying...
Krishnraj
|
|
|
|
|
No I don't believe that the inner join is the problem. Now you're using fast forward-only cursor.
What happens if you change to keyset-driven cursor:
Exec sp_cursoropen @cursor output, @mainQuery, <code>1,</code> 8196, @rowcount output
or dynamic cursor
Exec sp_cursoropen @cursor output, @mainQuery, <code>2,</code> 8196, @rowcount output
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi Mika,
Yes your answer is correct, inner join is not problem here.
And as per u said, i used keyset-driven cursor then it gives me count.. 
So its briliant suggesion...
Thank you boss....![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
Krishnraj
|
|
|
|
|
You're welcome 
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi All,
I accidently updated some data in Staff table on a production database, and i can't just simply install the back-up database as there are quite a few real time transaction occuring, is there a way that i can get the original staff data back? My database is M/S Sql Server 2000.
Many thanks.
|
|
|
|
|
You could restore the backup to another server and update the data back from the Staff-table on that restored database. This way you don't destroy other data in the production database.
For updating the data back from restored database, you can use for example SQL statements using linked server.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Oops it happens all the time, even to people who are very carefull,Everyone has work with SQL, has done something like that and you know what, If your Staff table was updated after the Back.There is no way you can recover those lost Updates. It happens to everyone remember that
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
I am having trouble with the Between condition and CASE expression in a stored procedure. I have set it up such that if a beginning date and end date are specified then return all records between the specified date range. If neither date is specified then I just return records that are between the actual date for records (basically Between 11/15/2008 and 11/15/2008). When I try to save the sp it gives me an "Incorrect Syntax near the keyword AND' error. I'm a newbie to using the CASE Statement so any help would be appreciated. Thanks in advance to all that respond.
--Case statements to determine which parameters to pass in
u.userCreationDate BETWEEN CASE WHEN @beginDate IS NULL
THEN u.userCreationDate AND u.userCreationDate
WHEN @endDate IS NULL THEN u.userCreationDate AND u.userCreationDate
ELSE @beginDate AND @endDate
END
|
|
|
|
|
Perhaps the easiest way to create the logic you're after would be using COALESCE funtion. Something like:
u.userCreationDate BETWEEN COALESCE(@beginDate, u.userCreationDate)
AND COALESCE(@endDate, u.userCreationDate)
If you want to use larger expression in BETWEEN operations you should use parenthesis like:
... BETWEEN (...) AND (...)
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
I used the COALESCE Function and it worked. Thanks Mike. Before closing, if you do not mind can you tell me exactly how that function works.
|
|
|
|
|
|
I need to solve the problem to execute sp_addextendedproperty with a new description for a column but that does in all the databases (more than 20) for typical the record created_id, deleted_id, updated_id….
I have proven it with sp_MSForeachDB and with a cursor for the tables nested within a cursor for databases but always it gives some problem with "USE @database".
This he would be the one that I believe that it would have to work but there is some detail that forgets to me.
Thank you very much, I have always worked with Oracle and I am new with Transact
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_AAA_INSERT_DESCRIPTION_BY_SELECT_COLUMN]
@pCOLUMN VARCHAR(100),
@pDESCRIPTION VARCHAR(255),
@pForced smallint = 0,
@pBD VARCHAR(100) = NULL,
@pExceptBD VARCHAR(100) = NULL
/*
EXEC [SP_AAA_INSERT_DESCRIPTION_BY_SELECT_COLUMN] @pCOLUMN = 'CREATED_ID', @pDESCRIPTION = 'Record creation user', @pForced = 1
, @pBD = 'DBNAME'
, @pExceptBD = 'DBNAME'
*/
AS
BEGIN
SET NOCOUNT ON
DECLARE @cError INT
DECLARE @cErrorMsg VARCHAR(8000)
DECLARE @cSPName SYSNAME
DECLARE @cTIENE_TRAN INT
DECLARE @DB_Name varchar(100)
DECLARE @Command nvarchar(200)
DECLARE @strTable nvarchar(200)
DECLARE @strDescription nvarchar(2000)
SET @cError = 0
SET @cTIENE_TRAN = @@TRANCOUNT
SET @cSPname = OBJECT_NAME(@@PROCID)
BEGIN TRY
IF @cTIENE_TRAN = 0
BEGIN
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION @cSPname
END
/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
DECLARE crsBD CURSOR FOR
SELECT [Name]
FROM MASTER.sys.sysdatabases
WHERE [Name] NOT IN ( 'Master', 'tempdb', 'model', 'msdb' )
AND ( @pBD IS NULL OR [Name] = @pBD )
AND ( @pExceptBD IS NULL OR [Name] <> @pExceptBD )
ORDER BY [Name]
OPEN crsBD
FETCH NEXT FROM crsBD INTO @DB_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Command = 'USE [' + @DB_Name + ']'--+ CHAR(13) + CHAR(10) +' GO '+ CHAR(13) + CHAR(10)
print @Command
EXEC ( @Command )
BEGIN TRY
DECLARE crsTable CURSOR FOR
SELECT OBJECT_NAME(c.object_id) AS [TableName], Cast( ex.value AS nVarchar ) AS [Description]
FROM sys.columns c
LEFT OUTER JOIN sys.extended_properties ex
ON ex.major_id = c.object_id
AND ex.minor_id = c.column_id
WHERE OBJECTPROPERTY(c.object_id, 'IsMsShipped') = 0
AND ( ex.value is null OR ( @pForced = 1 AND ex.value is not null))
AND OBJECT_NAME(c.object_id) NOT LIKE 'VIW_%'
AND c.name = @pCOLUMN
OPEN crsTable
FETCH NEXT FROM crsTable INTO @strTable, @strDescription
WHILE @@FETCH_STATUS = 0
BEGIN
--print @strTable
IF ISNULL( @strDescription, '' ) = ''
BEGIN
SET @Command = 'sp_addextendedproperty ''MS_Description'' ,''' + @pDESCRIPTION + ''', ''user'', ''dbo'', ''table'', ''' + @strTable + ''', ''column'', '''+@pCOLUMN + ''''
END
ELSE
BEGIN
SET @Command = 'sp_updateextendedproperty ''MS_Description'' ,''' + @pDESCRIPTION + ''', ''user'', ''dbo'', ''table'', ''' + @strTable + ''', ''column'', '''+@pCOLUMN+''''
END
print @Command
-- EXEC sp_executesql @Command
EXEC ( @Command )
FETCH NEXT FROM crsTable INTO @strTable, @strDescription
END
CLOSE crsTable
DEALLOCATE crsTable
print '... ' + @DB_Name + ' (final)'
print ' '
END TRY
BEGIN CATCH
SELECT @cError = @@ERROR
DEALLOCATE crsTable
SET @cErrorMsg = dbo.fGET_ERROR()
RAISERROR(@cErrorMsg,16,1) WITH LOG
END CATCH
FETCH NEXT FROM crsBD INTO @DB_Name
END
CLOSE crsBD
DEALLOCATE crsBD
/*-------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
IF @cTIENE_TRAN = 0 COMMIT TRANSACTION @cSPname
END TRY
BEGIN CATCH
SELECT @cError = @@ERROR
DEALLOCATE crsBD
IF @cTIENE_TRAN = 0 ROLLBACK TRANSACTION @cSPname
SET @cErrorMsg = dbo.fGET_ERROR()
RAISERROR(@cErrorMsg,16,1) WITH LOG
END CATCH
RETURN(@cERROR)
END
|
|
|
|
|
Didn't go through all the code, but at least it seems that you are using exec in parts. First when you change the database using exec, the change is valid only in the context where it's executed. Later on when you execute a procedure, the context is back in the original (master).
So you should build the execution string as a whole and then execute it. Something like:
@sqltext = 'USE CorrectDataBase; EXEC sp_...'
EXEC (@sqltext)
Hope this helps you forward,
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi, thanks for looking
I have a numeric column that i would like to reorder
I mean, there are gaps between the numbers, for example: 1,3,4,5,8,9
I would like it to be: 1,2,3,4,5,6
This column is the primarty key, i obiously have many rows
In other words, i want this column to have the value corresponsing to the row number
How could i accomplish this?
Thanks in advance
Alexei Rodriguez
|
|
|
|
|
Well, after thinking or a little bit, i found a solution using row_number() and a temp table
But i remember once i saw another way, it was a single query, but i dont remember where i saw it
If you know any other way, let meknow
Thanks
Alexei Rodriguez
|
|
|
|
|
I hope this query is which you saw:
select <br />
(select count(t1.columnname)+1 from mytable as t1 where t1.columname < mytable.columnname) as RowNumber<br />
from mytable
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Thanks for the reply
I was talking about a single query to update the numeric column according to a sort
Im trying to eliminate all the gaps on my primary key, You know about the little problem with autoincremented columns
Alexei Rodriguez
|
|
|
|
|
Alexei, auto incrementented numbers should NEVER be "filled". Read the link from Mika. You are heading down a very nasty path....
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
When using autoincrement for primary key (surrogate key), the values should have no meaning. For this reason the gaps shouldn't be any problem in a real world situation. See: http://en.wikipedia.org/wiki/Surrogate_key[^].
The operation you're thinking of, should include:
- copying the actual data to for example temp table
- re-seeding the identity
- inserting the data back
However, if you have foreign keys referencing to this table, this sequence is much more complicated.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi all,
I am using SQL Server 2000 in my organization. We have several databases. I want to restrict a set of users from accessing other databases using Enterprise Manager. In other words, these users should be able to access only their database where they have been created as users through Enterprise Manager.
Now I have read that creating Database specific roles help to restrict access. But unfortunately the role permissions cannot be spanned across databases. Therefore, are any SQL MVPs or intelligent readers out there who can provide me an answer. Many thanx in advance.
Regards
vinodi
|
|
|
|
|
Creating a role in a database helps you to restrict access for objects in database. If you don't want that a user has access to a database at all, you'll manage database users.
First create a login and then add that login as a user to those databases you want. See:
- sp_addlogin[^]
- sp_adduser[^]
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
