|
in sql server any version up to 2000(havnt played with anything past that..yet) stored procedures, typically prefixed with usp_(user stored procedure), can only return INT values so that you can check if the work was completed or had problems. however if you need something else you can get it the same way you SELECT what you want from any other place in the database. it works very well in sql server, however it wont work in oracle to my knowledge.. if you dont like my first idea you can always try an output parameter.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
hi,
thanx dude..the issue is even i use output parameter the sql server is not returning its returning 1ly int values.i think its an issue with sql server .. i want u ppl 2 try this and let me knw..if u also face the same issue we will inform microsoft about this and ask them the solution.
T.Balaji
|
|
|
|
|
well what the output parameter is defined as determines what kind of values it gets out...or if you do a simple
SELECT @varName
at the end of your sp you will be able to get any value from an sp, just remmeber that each SELECT generates a different result set so if you use a reader in c# to read them you'll have to read a value then move to the next result set. I know its not a problem with Sql server i've done this several dozens of times for my applications including a few yesterday(testing things, usually not production code) Sql Server has several problems, rollup doesnt work correctly sometimes, count(*) returns one when it should be 0, a few others, but returning values from a sp is not one of them.
<edit>
i was re reading your posts a second ago, and i've never used this method of retrieving values from anything..
balaji.t wrote: DC1DataContext obj = new DC1DataContext();
string b = null;
string c = obj.StoredProcedure1(1, ref b).ToString();
i always used SqlCommands and SqlReaders, and opened and closed my own SqlConnections. To be honest i'm not sure how your method actually works..i can guess at the obvious parts, but its entirely possible that your problem is with how you're running the sp and getting your data back, and not with SqlServer. also if you initialize strings with null, you can get null reference exceptions, however if you initialize them with "" you wont get the exceptions, and you'll just have to check b == "" or string.Empty. which works unless you're treating "" as a valid value for something.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
Please - just pick one and stick with it. Stop cross posting.
|
|
|
|
|
hi ,
sorry dude ..i wont repeat
T.Balaji
|
|
|
|
|
here i want discip with maximu manhours
but i cant get it , i get here multiple rows , i just want the maximum record here .
thanks in advance
select discip, max (manhour)
from
(
select discip,sum(forecast_mh) MANHOUR from espcs.mh_proj_planning
where projn = '6293' and to_char(month,'Mon-yyyy') = 'Aug-2008'
group by projn, discip,to_char(month,'Mon-yyyy'))
group by discip
hello
|
|
|
|
|
Does your inner query return any data? I suspect it is the to_char of the date that is the culprit.
What database are you using?
You are filtering for a single month AND grouping by the month. One of those is redundant.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi ,
i have application form which is filled by the applicant , i want to query that how much application is submitted daily weekly quarterly and yearly.
i have multiple fileds but primary key is Caseno and Date is in format dd/mm/yyyy and stored as datetime data type in sql server 2000.
please give me hint that how i do that .
thanks in advance
Rameez
|
|
|
|
|
User DatePart to define your grouping points and Count(*) and Group By to get the numbers you need.
All this is covered by BOL very nicely.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
rameez Raja wrote: Date is in format dd/mm/yyyy and stored as datetime
If it's in a DateTime, it's not in any particular "format".
I have a DateTruncate function that I use for that type of operation.
GROUP BY DateTruncate ( somefield , 'YY' )
I think such a thing is/was built into Oracle, I wish there were one in SQL Server.
I just took a quick look at the code and discovered it doesn't do quarterly, I'll have to work on it tomorrow.
|
|
|
|
|
please write the few lines of query with simple example for understanding
how i use the datepart function for counting number of cases sumbitted daily weekly monthly and quarterly
Regards
Rameez
Thanks in advance
|
|
|
|
|
rameez Raja wrote: please write the few lines of query with simple example for understanding
Do you have book on line (BOL), it comes free with SQL SErver.
Can you read and understand english.
Mate, there are examples in BOL which do exactly what you want, are you too lazy or stupid to do some reasearch. Bone bloody idle it seems!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
When attempting to update a recordset, I get an error saying:
Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type varchar to binary is not allowed. Use the CONVERT function to run this query.
I've Googled it but can't find a concrete answer how to use the convert function to do this.
My statement is something like:
UPDATE USERS
SET
PASSWORD ='0x98CA89CFB23D213D3A12C54B',
HASH ='0x134523450'
WHERE
ID='1';
|
|
|
|
|
You don't need to enclose binary data within single quotes.
UPDATE USERS
SET
PASSWORD =0x98CA89CFB23D213D3A12C54B,
HASH =0x134523450
WHERE
ID='1';
|
|
|
|
|
|
How can I view the contents of a record if the data is stored as "<binary data>"? Stored procedure? Some sort of cast? Help!
|
|
|
|
|
Presentation of the data is not the function of the database engine. When you query it in a .NET application you get a byte array (byte[]) back. How you interpret that is entirely up to you.
If you view the data in the SQL Server Management Studio it will show up as HEX - However that is a function of the Management Studio presenting the data to you. If you attempt to cast it to a VARCHAR, for example, you will get a variety of characters that will not mean much unless the binary was text to begin with.
|
|
|
|
|
|
Hi, thanks for looking
I made a web app, some records in db have a datetime field, like lastmodified, datecreated...the time is important for me
This is working as expected on my pc, but this site will be hosted on remote shared server
There is a one hour difference between my country and the time of the server (1 hour less)
When records are shown, obviously they are shown with the time of the server since usually im using getdate() in sqlserver or now in asp.net
Ive been modifing views and queries to substract 1 hour to any datetime field, but this doesnt seem the best approach
My question is...are there any other ways to either save records in DB taking my locale time?
Or is there any setting i could set in the DB so it works with in my locale time?
I repeat, i have no control over the sql server or web server, its a shared server
Thanks for any advices
Alexei Rodriguez
|
|
|
|
|
SELECT DATEADD(hh,-1,GETDATE())
SELECT GETDATE()
This seems too obvious, I think what you are asking for is how to discover the servers timezone and then deal with that! SQL Server (I don't think) does not access the servers culture into, you could create an assembly VB/C# to supply the info. Caveat, you are assuming the servers is set up with the correct timezone information which is reasonable but not 100%
Why not set up a settings table in SQL which will supply the number of hours to add/delete from getdate. This will give you complete control of the calc.
SELECT DATEADD(hh,@HourToAdd,GETDATE()))
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for your response
Im currently using dateadd(...) to read datetime fields
I could use any of this 2 approaches:
Save the data with my local time using dateadd(...) and read it normally
or
Save the data with the servers time and use dateadd(...) to read it
But this doesnt look quite right
I was reading that in oracle there something to set the timezone for the server, database, or even at field level http://www.lazydba.com/oracle/0__49654.html[^]
I was wondering if there were something like that in sqlserver
Alexei Rodriguez
|
|
|
|
|
Seems to be plenty of stuff around the TimeZone in BOL but none of it in TSQL.
What happens if you change hosting providor in the future? Is it valid that the app/data be oriented on the 1 office (will there never be another office). What happens if your host has a DR in another timezone and switches host? Oh I can think of lots of nasty ones (that DR one is bitter).
All of the above may be completely irrelevant but they come to mind.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yes, it could be a problem if i change provider or if the provider changes the server time or the timezone
I couldnt rely on adding n hours with dateadd
Thats why i was looking for someway of setting the timezone to the DB and no matter where the server were i would always have the correct time
By the way, whats DR?
Alexei Rodriguez
|
|
|
|
|
AlexeiXX3 wrote: By the way, whats DR?
Disaster Recovery - the really big providors will have a DR zone usually across the city from the main datacentre. Your site will be mirrored there and the database will failover if the primary server dies.
If you are building mission critical systems this will become a factor. I work for a bank, it is legislated that DR is part of the infrastructure. I hate it when they decide to test it out, 70% of my devs dissapear for 2 days!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Have you considered using UTC time? Its zone independant.
getutcdate()
Most programming langauges also support it, so any 'local' dates can be converted to utc and back easily.
Bob
Ashfield Consultants Ltd
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
