|
Ok, finally I made it work - here's the simplified procedure's code that's working fine:
<br />
ALTER PROCEDURE [dbo].[GetData](<br />
@Column varchar(100),<br />
@Begin datetime,<br />
@End datetime<br />
)<br />
AS<br />
BEGIN<br />
DECLARE @query nvarchar(1000)<br />
SET @query = N'SELECT ' + quotename(@Column) + ' FROM MyTable'<br />
<br />
EXEC sp_executesql @query <br />
END<br />
Thanks for your hint vimal_yet!
|
|
|
|
|
CREATE PROCEDURE [dbo].[GetValueFromMyTable]
( @ColID INT)
AS
BEGIN
SELECT ColName = CASE @ColID
WHEN @ColID=1 THEN Col1
WHEN @ColID=2 THEN Col2
ELSE col3
END,
FROM tableName
Regards,
Arun Kumar.A
|
|
|
|
|
hi,
i need a solution for a scenario.When server goes down i need to continue my work using temporary values available.how dis can be acheived does dataset hold values even wen the server goes down? if so how it works?
thnx in advance,
zari
|
|
|
|
|
You can put whatever data you like into a dataset, whether its from a database or any other source. It holds the data in memory.
This means it can be used as a cache so that if the connection to the server is lost you can carry on working. But if you have a large database it's not practical to hold the entire thing in memory. You'll also come across concurrency problems if there are multiple people using datasets and then they try to put the data back into the database when its been down.
Why would the server go down? The database should have redundancy so that it doesn't go down because its a vital part of the system.
|
|
|
|
|
After getting much help from Michael and Andy I managed to tweak this thing to meet the requirements....However, I need to group by an addition column..since I have never done it this way I have floundered for a few days before asking for help with this.
I can make it work in a different query just not this one.
I need to group by an addditonal field named:
problem_type_name in the Problem_type table which is tied to the job_ticket.problem_type_id
This worked in the other query I used and then added in the "group by"
INNER JOIN
problem_type p
ON (p.problem_type_id = j.problem_type_id)
Here is the working query that I cant add p.problem_type_name to "group by"
I have the inner join there and it will execute fine....I just need to know how to get it into the group by with location_name...I have been trying with no success...
Thanks to all who have helped thus far; I am learning and hopefully I will be able to contribute to the forum @ some point.
SELECT
Total ,
[Total Remain Open],
(SELECT
COUNT(*)
FROM
dbo.job_ticket
WHERE
location_id = Source.location_id
AND
report_date between @startdate and @enddate) AS [Total Opened],
(SELECT
COUNT(*)
FROM
dbo.job_ticket
WHERE
location_id = Source.location_id
AND status_type_id like '[3,4,5]'
AND
report_date between @startdate and @enddate) AS [Total Closed],
Client,
[Average days open]
FROM
(SELECT
l.location_id,
l.location_name AS 'Client',
COUNT(*) AS 'Total',
SUM(CASE
WHEN status_type_id LIKE '[1,2,6,7]' AND
report_date between @startdate and @enddate THEN 1
ELSE 0
END) AS 'Total Remain Open',
AVG(datediff(d, report_date, close_date)) AS [Average Days Open]
FROM
job_ticket j
INNER JOIN
location l
ON (l.location_id = j.location_id)
INNER JOIN
problem_type p
ON (p.problem_type_id = j.problem_type_id)
WHERE
report_date BETWEEN @startdate AND @enddate
GROUP BY
l.location_id,
l.location_name) AS Source
Regards,
Hulicat
|
|
|
|
|
I would appreciate it very much if anyone can provide me with links to websites and/or articles that can explain to me if it is better to use MySQL or SQL Server 2005 to develop my database application in. I know that SQL Server 2005 is closely integrated with the Visual Studio environment. Is MySQL closely integrated to any particular IDE? I prefer to use to SQL Server 2005, but our customers prefers MySQL and I don't know enough about MySQL to conclude which one is better and easier to develop with.
|
|
|
|
|
No, MySQL is not integrated with anything, unless the database explorer in VS2005 can find/use it.
You customers are probably victims of FUD. here are the facts:
MySQL is well behind the curve, it didn't even have stored procs 18 months or so ago
MySQL is NOT free for commercial use. Sql Server Express, is.
MySQL apparently has better clustering behaviour, so I am told.
http://www.databasejournal.com/features/mssql/article.php/3087841[^]
This looks out of date, I'm sure they support procs now. Not sure about views, triggers, etc.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
My two cents worth, I have used MySQL since SQL 2005 Express came into the limelight, and I haven't really looked back at MySQL since.
"I've seen more information on a frickin' sticky note!" - Dave Kreskowiak
|
|
|
|
|
Sql Server has a huge number of tools and features that goes with it. It brings huge ammounts of value beyond simple database functionality.
With 2005 it now also supports .Net stored procedures, triggers and datatypes, although these are for use where the traditional TSQL falls short rthaer than as a replacement.
TSQL is very nice to use and also has a mass of powerful features.
I've never personally use MySQL but I've never really heard anything good about it, all I've ever heard is developers complaining about it being broken or lacking. But then again I don't hang around in very pro-MySQL places 
I think most people use it because it'll run on Linux and isn't made by the evil empire. Personally I think the evil empire is so big because it makes some great products, especially Sql Server :P
|
|
|
|
|
Hi,
I have a column of datatype DateTime. I need to retrieve data from last 14
days old.
I am doing like this but it doesn't work.
Select reveiveddate from table1
where receiveddate = GetDate()-day(14)
seema
|
|
|
|
|
Use DATEADD
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
Is there a way to configure\create a datatable (strongly typed or untyped)
that can have a row accessed in constant time if you are searching on the
primary key?
If there isn't a way, then what is the quickest way to access a row when
using datatables?
Thanks,
Ryan
"I'm a mushroom cloud layin motherf*cker, motherf*cker!"
- Marsellus Wallace from Pulp Fiction
|
|
|
|
|
Ryan Andrus wrote: have a row accessed in constant time
What is "constant time"?
Additional: To the person that voted this a 1. Why is this not a valid question? When I read the OP's request I wondered if he meant "real time" or something else. So I asked for clarification. What is so wrong with that?!
-- modified at 19:49 Tuesday 26th June, 2007
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
O(1) in Big O notation which basically means that the amount of time to find one row in the datatable is independent of the number of rows. In other words, it will always take the same amount of time to access a row in the datatable. The same way that getting an element in an array in C++ is constant time because it is done through pointer arithmetic and not by iterating through the elements.
"I'm a mushroom cloud layin motherf*cker, motherf*cker!"
- Marsellus Wallace from Pulp Fiction
|
|
|
|
|
Please have a look at Indexes and Clusters.
I think it will reduce the time from O(n) to O(log n).
Regards,
Arun Kumar.A
|
|
|
|
|
Are you refering to index's on the Database table? That is not what I am asking about. I am talking about System.Data.DataTable. If there is a way to index a System.Data.DataTable can someone please post up a how to?
"I'm a mushroom cloud layin motherf*cker, motherf*cker!"
- Marsellus Wallace from Pulp Fiction
|
|
|
|
|
I am asking about System.Data.DataTable not a table in the database. Do System.Data.DataTable's support index's?
"I'm a mushroom cloud layin motherf*cker, motherf*cker!"
- Marsellus Wallace from Pulp Fiction
|
|
|
|
|
Hi
i have 2 questions in SQL 2005
First:
How to upload a picture in the database as i want each user to put his pic
second:
How to make the search CaseSentive
so it can diffrentiate bet
bahaa & BaHaa
Thanks a lot
|
|
|
|
|
Bahaa Hany wrote: How to upload a picture in the database as i want each user to put his pic
Use a varbinary(max) column to store this. When communicating between the database and your application use parameters. The data must be sent as a byte array (byte[] in C#)
Bahaa Hany wrote: How to make the search CaseSentive
Change the collation sequence to a case sensitive (CS) one. This is one of the options at install and is the best time to make that desision because it is a PITA to change it afterwards. Also, you will have great difficulty making comparisons between columns in different collation sequences so it is best pick one and apply it across the whole server.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
Have a little patience. Reposting the same question, "spamming", within 24 hours will likely get you ignored in the future.
|
|
|
|
|
I am building an application similar to a help desk application. This application allows the management to assign and track all assigned tasks. The users will be a create a trouble ticket and track and update assigned tasks.
Below is the current ERD:
DEPT(DEPT_SID, DEPT_NAME)
TASK(TASK_SID, TASK_DESC)
PRIORITY(PRIRITY_SID, PRIORITY_LEVEL)
USERS(USERS_SID, USERNAME, LAST_NAME, FIRST_NAME)
STAFF(STAFF_SID, STAFF_USERNAME)
ASSIGNMENT(A_SID, STAFF_SID, PRIORITY_SID, TASK_SID, DEPT_SID, USERS_SID, A_TITLE, A_DESC, A_DATE)
ASSIGN_LOG(A_LOG_SID, STATUS_SID, A_SID, A_LOG_DATE, A_LOG_NOTES, RESOLVE_DATE)
STATUS(STATUS_SID, STATUS_DESC)
Question:
1) How do I change the design to I only use one table for "Create By:" and "Assign To:"?
Comments and design suggestions are welcome.
Thanks,
Steve C.
kstv@netzero.net
|
|
|
|
|
astv wrote: Below is the current ERD:
That isn't an ERD. It is a bunch of text. I can only assume (because you haven't said) that the first bit of text represents a table name and everything in the brackets represents the columns. Perhaps the bolded text are primary keys (but who, other than you, can really know). Also a completely wilde guess suggests that the underlined columns might be foreign keys).
If you are going to describe the table structure don't assume people will understand your structure unless you tell them what it means.
astv wrote: 1) How do I change the design to I only use one table for "Create By:" and "Assign To:"?
No where in your structure is "Created By:" or "Assign To:". What does this mean?
astv wrote: Comments and design suggestions are welcome.
I can't. I don't understand what you have, so I can't offer suggestions on how to get where you are going.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
My apologies. What I have posted are the tables and fields. I followed how my book on how to describe the tables and fields. I tried to paste an image of my ERD but I could not figure it out.
[Table Name]([PK], [FK], [Other Fields])
The fields "Created By" and "Assigned To" are a part of the ASSIGNMENT table. Again I apologize, I missed this two fields. Since, "Assigned To" can have multiple values, this may have to be normalized. This is the piece that is unclear to me.
Another is, if a task is reassigned to a different user, I may want to know who reassigned this specific task.
Thanks for your response.
Steve C.
kstv@netzero.net
|
|
|
|
|
|
So your assignment table actually looks more like this:
A_SID [PK]
STAFF_SID [FK]
PRIORITY_SID [FK]
TASK_SID [FK]
DEPT_SID [FK]
USERS_SID [FK]
A_TITLE
A_DESC
A_DATE
CreatedBy
AssignedTo
astv wrote: How do I change the design to I only use one table for "Create By:" and "Assign To:"?
You also mention that you want to record if a person reassigns a task. I would guess that should go in the ASSIGN_LOG table. You may want to add a couple of nullable columns for that. You might also want to keep the current assignee in the ASSIGNMENT table for performance reasons.
Also, I don't know what you mean by "only use one table for "Create By:" and "Assign To:". They already are in one table.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
