|
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[ ^]
|
|
|
|
|
I have a table that looks like this:
11
28
18
.
.
.
i'm trying to roll up the values so it would take the value and add it to the previous sum of all the rows above it
11
39
57
.
.
.
I am having the hardest time, any ideas?
Einstein argued that there must be simplified explanations of nature, because God is not capricious or arbitrary. No such faith comforts the software engineer.
-Fred Brooks
|
|
|
|
|
ahhh stupid me, forgot about fetch
DECLARE SalesYearCursor CURSOR FOR
SELECT * FROM #tmpSalesYear
OPEN SalesYearCursor
FETCH NEXT FROM SalesYearCursor
INTO @Percent,@xxxxx,@xxxxx,@xxx
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Current = @percent + @previous
SET @previous = @Current
UPDATE #tmpSales SET Roll = @Current
WHERE Percentage = @percent
AND SalesYear = @year
FETCH NEXT FROM SalesYearCursor
INTO @Percent,@xxxxx,@xxxxxx,@xxxxxx
END
CLOSE SalesYearCursor
DEALLOCATE SalesYearCursor
EDIT: removed names that identify business 
Einstein argued that there must be simplified explanations of nature, because God is not capricious or arbitrary. No such faith comforts the software engineer.
-Fred Brooks
|
|
|
|
|
Not sure, but sounds like ROLLUP / GROUPING operations. Have a look at this, it might help you: GROUPING (Transact-SQL)[^]
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
What I need to do seems really easy, but I don't know how to do it and haven't been able to find the answer.  I have a SQL reporting services report that takes a date parameter. I want to create a subscription to the report that will run early each morning and show the data for the day prior. Nothing fancy, just a Now - 1. Only Now - 1 doesn't work. Does anyone know if/how this can be done?
Mike Devenney
|
|
|
|
|
Not sure about SSRS, but in SQL you would say GETDATE()-1. If both of those don't work, what's the error message (or any other problem description)?
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
I'm familiar with the SQL command to do this, I wanted to know if there is a way to specify a dynamic parameter in an SSRS report. GETDATE()-1 would work perfectly, but the question is how to set that as the parameter?
Mike Devenney
|
|
|
|
|
Where are you launching the report from? C#? Could you post the code that's causing the problem.
I must admit that I haven't used SSRS very much, only client side invocation and from IIS so this may be beyond my knowledge...
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
SSRS reports can be scheduled for delivery (called a subscription) through the web interface. When setting up a subscription you can specify the value you want passed to the report when it runs. I want to pass GETDATE()-1 but don't know how.
Mike Devenney
|
|
|
|
|
|
Please don't apologize! I've been banging my head against the wall trying to find a way to do this that didn't involved modifying the underlying stored procedure, which I now think I'll end up doing. I've asked DBA's, developers, project managers and anyone else who would continue to listen to the question past the words SQL Reporting Services but no one seems to know. Well, to be more accurate most of them knew, but they didn't have the answer I was looking for. 
Thanks for your time, I'll hit the links you posted and see if someone out there is going to save me from some relatively minor stored proc updates. Now that I think about it I've expended more effort trying to find the easy answer than I would have if I'd just made the update and pushed it to production. *sigh* I guess the adventure is the journey, right?
Mike Devenney
|
|
|
|
|
Mike Devenney wrote: I guess the adventure is the journey, right?
You're absolutely right about that! But then again, don't we all know the answer already (check the link if not familiar): 42[^]. However, the ultimate question seems kind of... forgotten
Anyhow, I really hope you get your original question solved!
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
You can use the following expression: =DateAdd("d",-1,Today())
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
