|
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
|
|
|
|
|
<br />
getutcdate()<br />
Returns the datetime value that represents the current UTC time (Coordinated Universal Time or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which the instance of Microsoft SQL Server is running.<br />
Unless im missing something, i would still have to make converisons when reading the values
Alexei Rodriguez
|
|
|
|
|
True, but both C# and VB.net have built in functions to dispay utc date as local date (based on pc settings) so you have no conversion to do - or inform your users that all times are GMT (most non-technical people don't know what utc is)
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ok I created a application that keeps track of our "tickets" at work.
Now they were wanting to add something where it auto assigned the tickets when the user submitted them.
How would I do this in SQL?
I have a "Admin" table:
ID
Name - Name of person
Available - Are they here? (This will be either TRUE or FALSE)
Area - Area that they work (This will be either Telecommunications or System Administrators)
Now I have my other table that when a user submits a ticket it adds it to this table:
"User_Submit" table:
ID
Users_Name
Users_Email
...
...
...
Assigned - (Name of user assigned to (from the Telecommunications or System Administrators branch)
Type - (What it is... will either be Telecommunications or System Administrators)
So when a user submits a ticket I need it to do the following:
Get a count from the User_Submit Table of all names in the ASSIGNED column WHERE the Type = System Administrator.
So lets say:
John Doe - Has 10 tickets
Jane Doe - Has 9 tickets
John Smith - Has 11 Tickets
So how do I search that table and get that Jane Doe has the least amount of tickets. SO then if Jane Doe is available (from the Admin Table) assign it to her.
Otherwise if she is not available assign it to the next person with the least amount of tickets (if they are here)??
???
Ive racked my brain on this... cant figure it out...
|
|
|
|
|
hi Jacob
Well you have explain a lot of things and you are starting to confume me. ok ,let me help you interactively. i will ask you questions where am not sure what you mean.
Jabob Write:Now they were wanting to add something where it auto assigned the tickets when the user submitted them
Yes we have that in SQL but we call it Boolean datatype. but i dont recomend it i just use bit datatype, if its 0 False and if its 1 then true. but still its your choice.
Jabob Write: Type - (What it is... will either be Telecommunications or System Administrators)
Is this a question?
In this case in your Id , you need to make it an identity column. something like this
Create Table Mytable
(
U_ID int indentity(100,01) not null,
--you can continue with other fields.
)
Jabob Write:Available - Are they here? (This will be either TRUE or FALSE)
Get a count from the User_Submit Table of all names in the ASSIGNED column WHERE the Type = System Administrator.
SELECT count(*) as [count] FROM User_Submit
WHERE Type like 'System%'
Hope this Helps
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
|
|
|
|
|
First find the lowest ticket number and name of user in table Admin which are (username) avilable
select top 1 ticketes,name from Admin where avilable = true order by tickets asc
Second do insert in your table User_Submit, if you need to insert usrname which is avilable and with the lowest tickets numbers then use query:
declare @username as varchar(255)<br />
set @username = (select top 1 name from Admin where avilable = true order by tickets asc)<br />
<br />
Insert into User_submit values (val1,val2,val3,...@username,nextval,...)
Hope this helps, if not then post your data how is stored in table and post result which you want to get.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Well SQL didnt have a boolean value but I just chose a string and made it TRUE or FALSE (string values).
Anyways, I'm not seeing how that does what I need? I think its just wayy over my head.
So from the USer_Submit table it counts how many each ADMIN has. ADMIN are the people that work to solve the tickets.
So this way I can find out which ADMIN has the least amount of tickets (or least amount of work, however you want to put it).
BUT
In another table (Admin_Table) it list all of the ADMINs and if they are here or not (column Available, which is TRUE or FALSE, string values).
So I need which ADMIN is here and has the least amount of tickets.
|
|
|
|
|
SELECT COUNT(*),
UserName
FROM TicketTable
INNER JOIN UserTable ON UserTable.UserID = TicketTable.UserID
AND UserTable.OnLine <> 0
GROUP BY UserName
ORDER BY COUNT(*)
SQL Server has a BIT field = boolean (UserDable.OnLine <> 0)
The above code will give you the "online" user with the least number of tickets in the ticket table.
By playing with the filters on the usertable join you can manage the users.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I thought you'd eventually ask that.
The code I gave you to query with a JOIN can be converted to an UPDATE.
UPDATE TableX
SET ...
FROM TableX
INNER JOIN TableY
ON ...
A very powerful technique.
|
|
|
|
|
Thank you all for your answers. I will be sure to try it first thing in the morning. I recently just bought an iMac and having trouble installing Windows in Parallel mode. So I cant get to my project I made in Visual Studio. Not to mention Boot Camp doesnt support Windows XP (only Windows XP SP2+). So I cant install XP and update it to SP2 lol.
Just having computer problems 
Anyways will try it at work and let you know if I get it working! Thanks for all of your help!
|
|
|
|
|
Hey guys, I'm working on a website, I ve got a problem connecting to the database, I get the following message:
Unable to open the physical file "F:\Working on ASP.NET\MywebsiteProject\App_Data\EnglishWebSIteDB.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
An attempt to attach an auto-named database for file F:\Working on ASP.NET\MywebsiteProject\App_Data\EnglishWebSIteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
I don't understand, I ve only got one database by that name and its used by SQL server !!!
|
|
|
|
|
Read some of this google results[^].
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
