|
DECLARE @sProcName SYSNAME
DECLARE @iRowCnt INT, @i INT = 1, @sSQL VARCHAR(255)
DECLARE @tblProc TABLE (Id INT IDENTITY(1,1), Name SYSNAME)
INSERT INTO @tblProc (Name) SELECT name FROM sys.procedures
SET @iRowCnt = @@ROWCOUNT
WHILE @i <= @iRowCnt
BEGIN
SET @sProcName = (SELECT Name FROM @tblProc WHERE Id = @i)
SET @sSQL = 'DROP PROC '+@sProcName
PRINT 'Procedure '+@sProcName+' deleted.'
EXEC (@sSQL)
SET @i = @i + 1
END
|
|
|
|
|
Hi!
I'm updating a Table. Each time I update, a new record is created. But I want only one record to be created(I've to over write the existing record). My query is:
char query[512];
sprintf_s(query, 512, "UPDATE Current SET TeamID=(%d),Theme=(%d)", TeamSlectLst->getSelected(),ThemeSelectLst->getSelected()+1);
SQLdb.Query(query);
I'm using SQLite. I'm calling SQLite Query from C++. What modifications has to be done in the above Query? This may be a silly question. But don't neglect this, why because I mainly working with C++.
|
|
|
|
|
There seem to be several issues.
Current is a reserved key word for SQL Server (see http://msdn.microsoft.com/en-us/library/aa238507(SQL.80).aspx[^]).
Furthermore, a WHERE clause is appropriate as otherwise all rows would receive the new values.
UPDATE [MyTable] SET TeamID=21, Theme=59 WHERE Identifier=95
By the way, parameterized queries are to be preferred to plain text queries.
|
|
|
|
|
I am pretty sure SQLite is not a SQL server product. Which probably accounts for the lack of response!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I asked this question on StackOverflow, and learnt that Skype uses SQLite, but can anyone give me any info on the actual semantics of the database tables used?
Skype seems to use .db, .db-journal, and .lock files, as well as subdirectories full of .dat files for storing conversation histories.ces
Are the formats of these files available anywhere, or is there an open source library for accessing them that I can examine and possibly modify? I think several libraries allow the retrieval of conversation history, but I would like to also achieve insertion of conversation history records.
|
|
|
|
|
I have 55000 of member records in the database, I have a procedure in Sql Server 2005 to calculate all members commission. I Execute this procedure on first date of every month. This is a MLM Project, Commission of every Member depends on Member Hierarchi, So in the way to calculate commission consider all members in the Hierarchi, it takes 24 hrs to generation commission. In this process I has to execture procedure for 24 hrs in my local machine.
So, I need to assign task to server from client and disconnect from server.
Is it posible ? pls suggest me a solution....
|
|
|
|
|
Manish_84 wrote: Is it posible ?
Yes
Manish_84 wrote: pls suggest me a solution....
I would set it up as a job in SQL Server, scheduled to run every month. I would also look at the procedure, 55k members and runs for 24 hrs seems a lot excessive.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
This should not take 24 hours.
Without seeing your code I would guess you need to rethink the steps to get the result. By the description it sounds like you are trying to do it all at once. First find commission rate for all to a temp table then apply commission rate for the output.
Also as stated by others if not using SQL 2005 Express a job can be scheduled for the first of every month.
|
|
|
|
|
Manish_84 wrote: 24 hrs to generation commission
For that few of rows, there is no way it should take that long.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
Lately I've been working an C# app that stores values in an Access db. One of the fields stores a time value. (7:30 PM)
Is there a more effecient way to store a time value than using the DateTime column? I will need to convert it to a DateTime value in the SELECT query.
Serious answers please. 
|
|
|
|
|
I'm a bit confused. 
You say that want a better way to store time in Access, but you will need to convert it to DateTime in your select statement. Then why not store it in a DateTime column to begin with ?
I worked for a Time and Attendace company for a while and what I've learned is that you always seem to need the date component along with the time because there is always the overnight shift worker. (11pm to 7am)
When you store the data in a DateTime column, the calculation of time difference is already built into those functions, you don't have to worry about crossing midnight etc.
My vote is to store the time in a DateTime column. 
I'm getting off my soapbox now. 
Best of luck with your project.
|
|
|
|
|
The key word here is "store". Access has a max capacity of 2gb.
DateTime types require 8 bytes for each field. Some Access developers have advocated using an int type for Date only values. This would be accomplished by storing the number of days since 1900 and using the DateAdd() function to convert the value to DateTime in the SELECT query.
I ask for serious responses! 
|
|
|
|
|
Richard Blythe wrote: I ask for serious responses!
Use a DateTime-field - that's what most people do, and that's where you can expect the most support. You can fit a whole lotta dates in those 2 Gb, and optimizing for space is a bit of a timewaster IMO.
I are Troll
|
|
|
|
|
Wow that's pretty serious! 
I certainly don't want to create a second curse by trying to eliminate the first one. Thanks for the reply.
|
|
|
|
|
I ask for serious design. Using Access for something when you are afraid of running into the 2gb limit is terrible. Use SQL Compact Edition or something else. I think the OLE automation date format is only 4 bytes.
|
|
|
|
|
I agree with Mr Gray, if you have to optimise for space using spit Access then you are using the wrong tool. Seriously change tools, there are many better options out there than spit Access
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I agree with the others, except when the application would fit the following description:
- a lot of fields are time values, so storing them in a compact format will be significant to DB size;
- the time resolution and range required are such that a small integer could do, say 1-minute resolution and a 2-day range (to cope with midnight shifts), would fit in a 16-bit int;
- your DB queries are known in advance and don't require complex datetime calculations.
If there is start time and end time, I would consider storing begin time and duration instead.
If the above matches pretty well, then you're set for a smaller DB, and no installation, as the JET engine is always present AFAIK.
OTOH if the above feel like restrictions, if there is too much uncertainty about how the requirements will evolve, if you want the comfort of full T-SQL, ... then by all means use a real DB and pay the price.
|
|
|
|
|
Luc Pattyn wrote: then by all means use a real DB and pay the price.
I would love to use SQL Server Express but the software is being deployed with CD-ROM using InstallShield 2010 express edition. This edition cannot install SQL Server silently. (Which would be a must)
At this point I can't justify the $900 upgrade cost on InstallShield so Access seems to be my best bet. 
|
|
|
|
|
I guess the problem here is not the field type in Access - in almost all cases it must be DateTime - but the user interface.
Make sure you can convert a user input like "7:30 PM" correctly into a DateTime value. And that you format values returned from the database in a way the user wants to see them.
For the communication with the database, you must use a parameterized query when you want to send DateTime values from your application to the database - be certain to add the DateTime values as DateTime values, and do not convert them to strings when adding them!
|
|
|
|
|
We have saved the 24 hr time in a text field as HH:MM
That being said you need to look at what overhead is needed to convert it to a true time.
By the way Microsoft Access and serious do not go together.
|
|
|
|
|
I have created sql stored procedure to calculate work hours of employees in a week.EmployeeID,FromDate,ToDate,Department,Designation are the parameters passed.My output is:
Day EmpID EmpName FirstTimeIn LastTimeOut WorkHours Break
-------------------------------------------------------------------------
Wednesday 2855747 AlexanderMathew 9:01:09 AM 6:15:14PM 8:59:05
Thursday 2855747 AlexanderMathew 9:45:09 AM 6:45:09 PM 8:00:00 Friday 2855747 AlexanderMathew 9:08:09 AM 7:20:09 PM 9:00:00
Saturday 2855747 AlexanderMathew 9:30:09 AM 6:30:09 PM 8:00:00
Sunday 2855747 AlexanderMathew 9:32:09 AM 6:28:09 PM 8:00:00
Monday 2855747 AlexanderMathew 9:20:09 AM 6:25:09 PM 8:05:00
Tuesday 2855747 AlexanderMathew 9:02:09 AM 6:02:09 PM 8:30:00
But the problem is that,in my output 'day' is shown as row.But is there any way to display day as columns.ie:
eg:
EmpName Friday Saturday Sunday Monday Tuesday Wednesday WorkHours
-------------------------------------------------------------------------
Alexander 07:11:18 Saturday Sunday 06:20:41 08:06:28 03:23:27 25:01:54
This stored procedure is for reporting.Hope you will help me to overcome this problem..
Thanks in advance
|
|
|
|
|
Just have a look through and see whether it solves your need,
Approach - 1 (using subquery)
sample table,
create table #tm (days varchar(10), empid varchar(10), empname varchar(100), intime varchar(20), outtime varchar(20), workhours varchar(20))
sample records,
insert into #tm
select 'Wednesday', '2855747', 'AlexanderMathew', '9:01:09 AM', '6:15:14PM', '8:59:05'
union
select 'Thursday','2855747','AlexanderMathew','9:45:09 AM','6:45:09 PM','8:00:00'
union
select 'Friday','2855747','AlexanderMathew','9:08:09 AM','7:20:09 PM','9:00:00'
union
select 'Saturday','2855747','AlexanderMathew','9:30:09 AM','6:30:09 PM','8:00:00'
union
select 'Sunday','2855747','AlexanderMathew','9:32:09 AM','6:28:09 PM','8:00:00'
union
select 'Monday','2855747','AlexanderMathew','9:20:09 AM','6:25:09 PM','8:05:00'
union
select 'Tuesday','2855747','AlexanderMathew','9:02:09 AM','6:02:09 PM','8:30:00'
select distinct empid, empname,
(select workhours from #tm b where a.empid = b.empid and days = 'Sunday') sunday,
(select workhours from #tm b where a.empid = b.empid and days = 'Monday') Monday,
(select workhours from #tm b where a.empid = b.empid and days = 'Tuesday') Tuesday,
(select workhours from #tm b where a.empid = b.empid and days = 'Wednesday') Wednesday,
(select workhours from #tm b where a.empid = b.empid and days = 'Thursday') Thursday,
(select workhours from #tm b where a.empid = b.empid and days = 'Friday') Friday,
(select workhours from #tm b where a.empid = b.empid and days = 'Saturday') Saturday
from #tm a
disadvantage of approach-1:
---------------------------
1) if the table has huge records then the above will take time for execution
Approach - 2
-------------
create table #tm1 (empid varchar(10), empname varchar(100),
sunday varchar(20), monday varchar(20), tuesday varchar(20), wednesday varchar(20), thursday varchar(20),
friday varchar(20), saturday varchar(10))
insert into #tm1 (empid, empname)
select distinct empid, empname from #tm
update a set a.sunday = b.workhours
from #tm1 a
join #tm b on a.empid = b.empid and b.days = 'Sunday'
update a set a.monday = b.workhours
from #tm1 a
join #tm b on a.empid = b.empid and b.days = 'Monday'
update a set a.tuesday = b.workhours
from #tm1 a
join #tm b on a.empid = b.empid and b.days = 'Tuesday'
update a set a.wednesday = b.workhours
from #tm1 a
join #tm b on a.empid = b.empid and b.days = 'Wednesday'
update a set a.thursday = b.workhours
from #tm1 a
join #tm b on a.empid = b.empid and b.days = 'Thursday'
update a set a.friday = b.workhours
from #tm1 a
join #tm b on a.empid = b.empid and b.days = 'Friday'
update a set a.saturday = b.workhours
from #tm1 a
join #tm b on a.empid = b.empid and b.days = 'Saturday'
select * from #tm1
i didn't do calculating total work hour calculation.
|
|
|
|
|
You need to use the pivot function[^], assuming you are using sql server of course.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi all
Does PRINT work in sql server function???
One person's data is another person's program.
--J.Walia
|
|
|
|
|
Yes
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
