|
Somebody has been kind enough to foresee such question; have a look here[^].
FYI: I found the link by Googling SQL update
EDIT
unless count isn't the name of a field, and you intended to count some rows and use that as a selection criterium... Then look at the other reply.
/EDIT
|
|
|
|
|
try something like this ...
Update myItems<br />
set myField = 'more than 1 count'<br />
where item_class in <br />
(<br />
select item_class<br />
from myItems<br />
group by item_class<br />
having count(*) > 1<br />
)
|
|
|
|
|
Thanks guys. Just what I was looking for.
|
|
|
|
|
Then mark as answer. 
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Hi,
I have a large varbinary field in one of my tables, and I would like to download in parts for show a download progress indicator in my application.
How can I split the data sent in a SELECT query?
Thanks
|
|
|
|
|
What do you mean with
solomon85 wrote: split the data sent in a SELECT query?
?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
I think he means to retrieve it in chunks so as to be able to display a percentage meter.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
hi all
i want sql statement to delete and drop all my procedures in a data base in sql server 2005
how i can do that?
thanks for all
|
|
|
|
|
Does that include the stored proc you want that deletes all of the stored procs?
You need to do this drop procedure [proc_name] to drop a stored proc.
Create one line for each procedure unless you want to get all of the stored procs without knowing in advance what they are called. I'll leave you to work that one out. Hint: sysobjects
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
to delete all the procedure in your specified database, just execute the following line.
1. select 'drop proc '+name from sys.objects where type='p'
you will get list of procedure like
drop table proc1
drop table proc2
select all and execute
thats all
RAVIKUMAR S
BANGALORE
|
|
|
|
|
Create a cursor that loops through the system view INFORMATION_SCHEMA.ROUTINES or sysobjects
Get the name of the proc in the cursor
construct a drop procedure string DROP PROCEDURE [ProcName]
execute the string
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I would suggest backing up the database before you start.
In object explorer go to the database go to the Programmability then Stored Procedures. Open the Object Explorer Details.
You can now script all the stored procedures so that when you delete them you have a copy.
Highlight the stored procedures to be deleted
Right click
Select Script Stored Procedure As/Create To/File
Give file name.
Now you can delete the files that are highlighted by right clicking and selecting delete.
|
|
|
|
|
DECLARE TblCursor CURSOR FOR
SELECT
[name]
FROM
sys.objects
WHERE
[name] like '%' + @fnd + '%'
and is_ms_shipped = 0
and [type] = 'P'
OPEN TblCursor
FETCH next FROM TblCursor INTO @proc
WHILE @@fetch_status = 0
BEGIN
SET @sql = 'DROP PROCEDURE ' + @proc
EXEC @sql
FETCH next FROM TblCursor INTO @proc
END
CLOSE TblCursor
DEALLOCATE TblCursor
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
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! 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
