|
I need to store a time such as 5:00 PM in a table column in SQL SERVER Express 2005. I do not need to store any data about the date. I know that I could store this as text, but I know I should store it in a datetime data type. Should I create a user defined type just for time?
When I first was writing the database I found an article in a forum where users were saying that when they needed to store a time with no date they were using a text field. Then I posted a message in the VB.NET forum and I was told that it would be a big mistake to store a time in a text field.
What should I do?
|
|
|
|
|
AAGTHosting wrote: Should I create a user defined type just for time?
Probably the best route to go. If you have any comparing/manipulation of the data, it will be easier to work with a UDT, rather than a text field.
OT: One of my favorite books is Pro SQL Server 2005 Database Design and Optimization[^] by Louis Davidson. He does a pretty good job with UDTs. Examples in both C# and VB.NET. Though it is for SQL Server 2005, most of the material still works with SQL Server 2005 Express.
"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon
|
|
|
|
|
Hello,
I am looking for a reference book that covers T-SQL for MS-SQL 2005. I have found several that cover SQL Server, but almost nothing about stored procedures. I want a book that covers advanced programming along with some of the performance topics, and advance query and calculations.
Thanks,
Leo T. Smith
|
|
|
|
|
A quick search on Amazon yielded several relevent titles[^].
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
|
|
Thanks, I'll check it out.
Leo T. Smith
Program/Analyst Supervisor
|
|
|
|
|
Thanks, a couple of these looks like what I need. I went and checked them out locally and actually found one.
Leo T. Smith
Program/Analyst Supervisor
|
|
|
|
|
This is a very subjective beginner question... Suppose I have two different applications that access the same database, and I want them both to use the most recent data... how should I do it? Should I clear and refill my whole dataset every time I access data? Should I use remoting between the two applications in order to notify each other of what row to reload (this seems complicated!)? By the way I work in disconnected mode with Typed datasets.
I'd appreciate if you gave me some pointers.
Thank you!
|
|
|
|
|
Applications informing each other about changes to data doesn't sound like a good idea. You haven't stated what database system or programming languages you are using. If you are using, SQL Server 2005 and .NET, Query Notification[^] may be of interest to you.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
goldoche wrote: I want them both to use the most recent data
You can never have the most recent data. As soon as you have retrieved it, it is potentially out of date. That said, you then have to decide how out-of-date you are willing to allow the data to be. You'll have to have some way of dealing with concurrency violation, that is, where another user has modified the data since it was read. One approach, that used by the DataSet's update feature by default (IIRC), is to compare all the field values against the values that were retrieved and if different, to fail the update.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
i have written a trigger as followed:
<br />
-- ================================================<br />
-- Template generated from Template Explorer using:<br />
-- Create Trigger (New Menu).SQL<br />
--<br />
-- Use the Specify Values for Template Parameters <br />
-- command (Ctrl-Shift-M) to fill in the parameter <br />
-- values below.<br />
--<br />
-- See additional Create Trigger templates for more<br />
-- examples of different Trigger statements.<br />
--<br />
-- This block of comments will not be included in<br />
-- the definition of the function.<br />
-- ================================================<br />
SET ANSI_NULLS ON<br />
GO<br />
SET QUOTED_IDENTIFIER ON<br />
GO<br />
-- =============================================<br />
-- Author: <Author,,Name><br />
-- Create date: <Create Date,,><br />
-- Description: <Description,,><br />
-- =============================================<br />
CREATE TRIGGER tr_stagingEmployer_Update<br />
ON Employer<br />
AFTER UPDATE<br />
AS<br />
-- SET NOCOUNT ON added to prevent extra result sets from<br />
-- interfering with SELECT statements.<br />
SET NOCOUNT ON;<br />
<br />
INSERT INTO stagingEmployer<br />
SELECT<br />
*, 'U' as [Action]<br />
FROM<br />
Inserted<br />
GO
BUT i get error stating:
<br />
Msg 311, Level 16, State 1, Procedure tr_stagingEmployer_Update, Line 14<br />
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.<br />
what is wrong with this query?
"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
|
|
|
|
|
From a Microsoft article on the subject:
SQL Server 2005 does not allow for text, ntext, or image column references in the inserted and deleted tables for AFTER triggers. However, these data types are included for backward compatibility purposes only. The preferred storage for large data is to use the varchar(max), nvarchar(max), and varbinary(max) data types. Both AFTER and INSTEAD OF triggers support varchar(max), nvarchar(max), and varbinary(max) data in the inserted and deleted tables
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Okay,
so am i wrong when i say that the query is fine the way it is... it is due to the fact that one of the columns is of image type in my table? If it wasn't for that the query would have worked???
"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
|
|
|
|
|
This article[^] provides more information on the subject.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
 I created one View in my SQL Database on which i set an filter using following way's to fill Dataset at runtime. In which first way take less time then second way but using first way i can not filter the data using Between SQL Keyword (i.e. Between 03/01/2008 and 04/01/2008), is there any other way to filter the data for datatime column?
First way:
SELECT * FROM View WHERE (CONVERT(nvarchar, [Date], 103) = N'04/01/2008')
its take Execution Time = 0:00:01
Second way:
SELECT * FROM View WHERE ([Date] = CONVERT(DATETIME, '2008-01-04 00:00:00', 102))
its take Execution Time = 0:00:16
please help me thank's in advanced.
thanks and regards
Sasmi
|
|
|
|
|
I don't really see why the times could be significantly different for these two queries. Is the time difference repeatably consistent? (i.e. have you run this through several iterations to ensure that it isn't something like data being cached in memory, etc.)
What happens if you put the conversion of the string outside the SELECT in the second example:
DECLARE @date DATETIME
SELECT @date = CONVERT(DATETIME, '2008-01-04 00:00:00', 102)
SELECT * FROM View WHERE ([Date] = @date)
|
|
|
|
|
The first query is converting a DATETIME value to a string. The date is already valid and needs no validation.
I suspect the second version is first converting the string to a DATETIME with validation. I have never used convert in this way since the 3rd parameter '102' is useless when not converting to a string. I wouldn't be surprised if the orignal string is converted back and forth a couple of times during the process. Try it using CAST('2008-01-04 00:00:00' AS DATETIME) instead of CONVERT.
|
|
|
|
|
Hi all.
I have this SSIS package. It is running on my computer without any problems... not even warnings. When i try to run it on the server... i get this:
Message<br />
Executed as user: TEST\TESTadmin. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:45:04 PM Progress: 2008-01-03 15:45:16.77 Source: Create File and insert header Validating: 0% complete End Progress Progress: 2008-01-03 15:45:16.78 Source: Create File and insert header Validating: 50% complete End Progress Progress: 2008-01-03 15:45:16.78 Source: Create File and insert header Validating: 100% complete End Progress Progress: 2008-01-03 15:45:16.80 Source: DataInfo Validating: 0% complete End Progress Progress: 2008-01-03 15:45:16.83 Source: DataInfo Validating: 50% complete End Progress Progress: 2008-01-03 15:45:16.88 Source: DataInfo Validating: 100% complete End Progress Progress: 2008-01-03 15:45:16.88 Source: HeaderInfo Validating: 0% complete End Progress Progress: 2008-01-03 15:45:16.89 Source: HeaderInfo Va... The package execution fa... The step failed.
i dont know why... but it is failing and i cant determine why ??? any help would be greatly appreciated 
"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
|
|
|
|
|
You haven't provided enough information to determine what the problem is. Look at the contents of the output window. The details of the error should be listed there.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
i dont know where you see output window??? what i have posted to you is what i can see in "Right Click on a job in sql > View History" then you see the log file viewer...
But thank you any way... it was a security issue... i managed to get past the problem by changing the path in a file from \\servername\a$\...
to a:\..... and now it is working fine... (the servername was the name of the server that the job was executing on.) thank you any way.
"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
|
|
|
|
|
Support123 wrote: i dont know where you see output window???
Sorry, I assumed that you were using Visual Studio to run the package. If you were, the output window would have provided details of the error.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
you must debug your SSIS package on Server exactly with Business Intelligence Development Studio
This is because there are some problems and error on SSIS packages that you don't aware of them before debugging your package on server, same as pinging an IP from Server that you can't see IP from server but when you try this from your PC this may be correct.
Human knowlege belongs to the world
|
|
|
|
|
It would make more sense if you posted this reply to the OP instead of myself.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Dear all i have a querry,
i want to know how report is generated in mssql 2005,there is something called as report/summry
when we click on report it is generating report like memory uses ,processor uses ,querry execution time.. and all...
can anybody tell me which technology is used there to generate such reports...
thanks in advance
T@SU
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
