|
ok thenks let me check
Syed Shahid Hussain
|
|
|
|
|
Its worked fine
thanks boss
Syed Shahid Hussain
|
|
|
|
|
neilarnold wrote: Well known problem, simply solved.
Oops, I meant to vote this a 5, but it ended up as a 1 because I goofed while not paying attention. Sorry, but just thought you'd wanna know.
|
|
|
|
|
Hi All,
After searching the web for a solution I just seem to be more confused - If someone could throw some light on this I would really appreciate it - as my sql isn't brill as you will see..
I have a sql SP which accepts 2 params:
@SalesOrderID int = 0,
@AllocationEndDate datetime
The select statement contain various joins from orders & allocations tables.
Its the main where clause that I am having issues with - If I pass a SalesOrderID into this SP I want the where to match this ID along with some date checking & a trans type. If I dont pass a SalesOrderID I want the where to just match on the date checking & a trans type.
See below the code which I have & that doesnt work:-
IF @SalesOrderID = 0
Begin
Where
(M.DateTransactionDue >= '01/01/1900' AND M.DateTransactionDue <= @AllocationEndDate) AND M.TransactionTypeID = 1
END
ELSE
Begin
Where
(M.SalesOrderID = @SalesOrderID) AND (M.DateTransactionDue >= '01/01/1900') AND (M.DateTransactionDue <= @AllocationEndDate) AND
(M.TransactionTypeID = 1)
END
I did try a Case statement I couldn't get that to work either:-
--WHERE
--(case @SalesOrderID
--when 0 M.DateTransactionDue >= '01/01/1900' AND M.DateTransactionDue <= @AllocationEndDate AND
-- M.TransactionTypeID = 1
--when > 0 (M.SalesOrderID = @SalesOrderID) AND (M.DateTransactionDue >= '01/01/1900') AND (M.DateTransactionDue <= @AllocationEndDate) AND
-- (M.TransactionTypeID = 1)
-- else -1
-- end)
Many thanks
Janet
Lady Programmers are a rare breed!
|
|
|
|
|
I've come across this situation as well and here is the equivalent of what I've had to do:
WHERE @SalesOrderID = case when @SalesOrderID > 0 then M.SalesOrderID else @SalesOrderID end
AND M.DateTransactionDue BETWEEN '01/01/1900' AND @AllocationEndDate
AND M.TransactionTypeID = 1
In the above, you kind of have to think of the SalesOrderID filter very differently of how we normally handle it. Essentially, we'll use the CASE statement to compare @SalesOrderID either to the Field M.SalesOrderID or to itself. Comparing it to itself is the situation where the value is not provided and we don't want to filter on it, but we have to put something there and that accomplishes it. Also, notice that the CASE statement is resolved to provide the right side of the equals, so this gives you the flexibility to determine what you want to compare against; it's a pretty handy technique. If you prefer not to have the CASE statement and are free to modify the SP, you could have the @SalesOrderID default to NULL and then use ISNULL() in the WHERE, like so:
WHERE ISNULL(@SalesOrderID, M.SalesOrderID) = M.SalesOrderID
AND M.DateTransactionDue BETWEEN '01/01/1900' AND @AllocationEndDate
AND M.TransactionTypeID = 1
In this situation, when @SalesOrderID is NULL, the ISNULL function will use the Field M.SalesOrderID's value for the left side of the equals, essentially toggling whether to compare M.SalesOrderID against @SalesOrderID or itself and mimicking the desired behavior that the condition be optional; this too is also a handy technique. It's more compact and accomplishes the same thing as the above WHERE clause as long as @SalesOrderID defaults to NULL.
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Since C# is my expertise & really appreciate your help with SQL. I now have 2000+ records returned (its only taken most of the aft!). 
I used your example for the 'Case' as I couldn't  get the ISNULL to work - even though I prefer the look of that syntax as it is easier to read - It kept telling me that '@SalesOrderID: this input parameter cannot be converted. This does have to be defined as an int - so Im assumming the ISNULL cannot be used in this instance, but I will remember for the future.
Thanks again...
Janet
Lady Programmers are a rare breed!
|
|
|
|
|
Hi,
I have a problem using synonyms in stored procedure.
In my procedure I create a synonym based on a specific business logic.
For example:
create procedure test_sp_create_synonyms
as
if object_id('synTest')is not null
drop synonym synTest
exec('create synonym synTest for Counters')
select top 10 * from synTest
go
The problem appears when before executing this procedure, the synonym synTest
points to table T1 that does not exists, and the table T1 is placed on Linked Server.
For example:
synTest -> [test_ls].[DB1].[dbo].[T1]
When I execute the procedure an error is raised:
The OLE DB provider "SQLNCLI" for linked server "test_ls" does not contain the table ""DB1"."dbo"."T1"". The table either does not exist or the current user does not have permissions on that table.
The problem is only when T1 is situated on linked server. If is on a local server(example: synTest -> [dbo].[T1]) here is no problem and the execution of test_sp_create_synonyms passes OK.
I will be grateful if someone can help me.
|
|
|
|
|
This doesn't quite make sense.
The synonym creation should be ok even if the table doesn't exist, but your select statement (select top 10...) should fail if T1 does not exist in target database. But then again, this is supposed to happen if the table does not exist.
Synonyms can be created against non-existing objects and that shouldn't be any problem as long as when using the synonym the object exists.
Or did I miss something?
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hello all,
I'm wondering how to get the schema of the result set returned by a stored procedure without calling it. I know that some code generation tools do this, but I've been unable to figure out how to go about it. Is there some INFORMATION_SCHEMA view that does this that I don't know about?
Thanks,
Will
|
|
|
|
|
I have the impression they discovery tool actually executes the procedure to get the resultset. The only other way I can think of would be to parse the sysobjects data but that would be a nightmare.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I ran a SQL trace. Looks like this kind of thing is what they are doing. I tested it and it is non-destructive.
SET FMTONLY ON
EXEC Test_Proc
SET FMTONLY OFF
I've never seen that keyword before. Ever day is a learning experience.
|
|
|
|
|
Hi,
I have a problem with attaching databases to Microsoft SQL Server Express 2005, databases are attached as Read-Only even I'm using Management Studio or sp_attach_db stored procedure.
Can you help me??
|
|
|
|
|
One possibility is that the database is marked read-only before detach. If the database isn't in restoring state (everything is fine after attach), could you simply set it to read-write state:
ALTER DATABASE DatabaseName SET READ_WRITE;
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi Mika, thanx for reply
I cannot change database to be read write when I tried your code I received the following errors:
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "D:\>> DatabaseName.mdf". Operating system error 5: "5(error not found)".
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "D:\>> DatabaseName.ldf". Operating system error 5: "5(error not found)".
File activation failure. The physical file name ">> DatabaseName.ldf" may be incorrect.
Msg 945, Level 14, State 2, Line 1
Database 'DatabaseName' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
My database is a valid database copied from another machine on witch it works fine.
|
|
|
|
|
Ahmad Safwat wrote: Database 'DatabaseName' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
In the ALTER command, replace the DatabaseName with the actual database name and try again. Now you tried to set options for database named DatabaseName. For example if your database is named Test then the command would be:
ALTER DATABASE <code>Test </code>SET READ_WRITE;
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
I'm sorry it was the weak end, and I was unable to follow up the thread
Mika Wendelius wrote: replace the DatabaseName with the actual database name
I understood you first time and this is what I exactly did, and received the previous errors.
And my database is a valid database copied from another machine on witch it works fine.
|
|
|
|
|
Ok,
The error message you got when trying to set read_write is quite odd. It clearly states that your physical files are not in good condition or not found.
- are there any suspicious messages in the error log when you attach the database?
- also check that the file attributes are correct on both physical files (not read-only and not system)
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
!!! S U R P R I S E !!!
The problem has been solved by changing "Log On" type of SQL Server Service from "Network Service" to "Local System".
Now, I changed "Log On" type back to "Network Service" and still no problem !!!
Can anybody explain that to me??
|
|
|
|
|
Local system has all the privileges for the computer whereas privileges for Network Service are limited.
There are several reasons to this. Your datafiles in the db you attached may have had restrictions at file level which are now corrected, the directory where the files reside may have had a privilege problem etc.
Didn't the errorlog say anything when you attached the database for the first time?
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Is the database file's attribute set to readonly?
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
i would like to insert a file into a table corresponding to a varbinary column. is there a way to specify the complete filepath in the local directory in the sql script? all within sql not c#.
thanks in advance.
----------------------------------------------------------
"unzip; strip; touch; finger; mount; fsck; more; yes; unmount; sleep" - my daily unix command list
|
|
|
|
|
Perhaps somehthing like:
UPDATE SomeTableName
SET SomeColumnName = (SELECT *
FROM OPENROWSET(BULK
'C:\AnyPath\AnyFile.Extension',
SINGLE_BLOB) TableAlias )
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi,
I have created a table and in that i have inserted some records. then i want to display the records between specified dates then how to do that.
create table rough(checkeddate nvarchar(100))
These are the inserted values..
insert into rough values('1/01/2002')
insert into rough values('3/01/2002')
insert into rough values('12/01/2003')
insert into rough values('11/09/2006')
insert into rough values('8/01/2008')
insert into rough values('1/01/2004')
insert into rough values('1/01/2006')
insert into rough values('1/21/2003')
insert into rough values('1/31/2005')
insert into rough values('1/11/2006')
insert into rough values('1/01/2006')
insert into rough values('1/01/2002')
insert into rough values('1/01/2004')
insert into rough values('1/01/2006')
The output as follows
mm/dd/yyyy
----------
12/01/2003
11/09/2006
1/01/2006
1/21/2003
1/31/2005
1/11/2006
1/01/2006
1/01/2006
so my requirement is now i want to diplay particular records between two dates like as follows
select * from rough where checkeddate between '1/01/2006' and '12/31/2006'
Can anyone help me..
|
|
|
|
|
Samiullah wrote: create table rough(checkeddate nvarchar(100))
Why are you storing date as an nvarchar? Try using a datetime type instead and you can easily filter records using the query you mentioned. But if you still want to achieve it using an nvarchar, you can simply cast the value to a datetime. Here's a small change to your query:
select * from rough where cast(checkeddate as datetime) between '1/01/2006' and '12/31/2006'
Regards,
Syed Mehroz Alam
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
modified on Thursday, November 13, 2008 8:22 AM
|
|
|
|
|
Thanks for your immediate response. As iam new to sql server and its also my requirement to use the nvarchar thats why!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
