|
Another way:
delete from mytable where block_id not in <br />
(select top 1 t1.block_id from mytable as t1 where t1.block_name=mytable.block_name order by t1.block_id)<br />
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.
|
|
|
|
|
listen, don´t delete prevent it from happening
do this in your procedure
table_countries
|id_country|name|...|
you ask for this right?
@country varchar(50)
if exists(select * from table_countries where name = @country)
begin
select 'ERROR: that country exists'
end
else
begin
insert into table_countries values (@country)
select 'OK: country added'
end
-----------------
it would help if you read and write them in upper() or lower()
upper(hello) = HELLO
lower (HELLO) = hello
use it like this: ...upper(@country)...
that way you won´t get Dubai,DUBAI,dubai etc
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
I'm trying to figure out how to include google maps in my application. so i when i get the cords i can render a google map. ideas on how to do this?
|
|
|
|
|
Don't know, except you are in the wrong forum
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hi. I'm trying to locate the date/time of a certain data. I've tried the typical "SELECT * FROM this_table WHERE column_name = '<some date>'. This does not return anything. Can someone help me with this? I want to search quickly for the specified date and time. Thanks! </some>
|
|
|
|
|
Hi,
If the data type of column_name is datetime, 'some date' should be convertible to datetime implicitely or you should convert it explicitely. For conversion use CONVERT function. For example:
column_name = CONVERT(datetime, '2008-09-07 12:02:00', 120)
Also notice that this is exact match so minutes, seconds and so on must match.
Mika
|
|
|
|
|
Wow thanks. However, What if I would want to search all records under 2008-09-07?
|
|
|
|
|
If you know for sure that the time is always at midnight, then you can use equality like:
column_name = CONVERT(datetime, '2008-09-07', 105)
If time portion can vary, use ranges like:
... column_name >= CONVERT(datetime, '2008-09-07', 105)
AND column_name < CONVERT(datetime, '2008-09-08', 105)
Hope this helps,
Mika
|
|
|
|
|
I tried to use "select @@identity" in my ADO/ACCESS/C++ application to retrieved
the primary key of the last inserted record but failed
the table only contain two column
1.cNo ---> is a primary key (autonumber)
2.cText ----> just dummy field (char 255)
this is the code
CString strConnect = _T("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\\test\\test.mdb;");
ADODB::_ConnectionPtr connectionPtr;
hr = connectionPtr.CreateInstance( __uuidof( ADODB::Connection ) );
connectionPtr->Open((const TCHAR*)strConnect,_T(""),_T(""),NULL);
CString queryString = _T("insert into test_table (cText) values ('dummy')");
connectionPtr->Execute((LPCTSTR)queryString,NULL,ADODB::adCmdText);
CString getIDQueryString = _T("select @@identity");
ADODB::_RecordSetPtr rPtr = connectionPtr->Execute((LPCTSTR)getIDQueryString,NULL,ADODB::adCmdText);
int lastInserted = rPtr->Fields->GetItem(_T("cNo"))->GetValue().lVal;
The execution of getIDQueryString is fine.
but I got an error when try to get the value of 'cNo' , saying that it cannot be found (cNo is the table primary key with autonumber).
Hope someone can give me some solution.
Thank in advance.
|
|
|
|
|
I could be wrong (it's been so long since i used Access) but I don't think it supports this kind of operation - all you can do is runa "select max(id)" or "select top 1 id from.. order by id desc" type query... but note that these are NOT specific to your connection. If someone else happens to insert a record between your insert and you calling this, you will get their last insert id, not yours... one reason why Access should not be used in multi-user environments.
|
|
|
|
|
I get it working now(after losing few of my hair) by modified the query to "SELECT @@IDENTITY AS temp"
and then calling GetData on "temp" field which is work fine now.
but as you said since ACCESS doesn't even support multi query i guess this is still as dangerous as calling MAX(id).
but my application is Single user I guess it ok for now.
Thank for your answer.
|
|
|
|
|
Hi
I am not sure of the right terminology but here goes. (I am fairly new to this any help will be appreciated)
I am trying to take data and assign it a new string name each time a new row has been through the loop. This is in asp classic.
rs1.Open "Select * from email where deleted = 0;", connStr1, 3 , 4
startbody = rs1("body")
rs1.MoveNext
rs1.Close
Set rs1 = Nothing
for each record that comes out I need a new sting name
example
startbody1 = rs1("body") (first record returned)
startbody2 = rs1("body") (second record returned) etc.
modified on Sunday, September 7, 2008 4:53 PM
|
|
|
|
|
First, try Web Development forum.
Second, arrays could be useful for you
Mika
|
|
|
|
|
Thanks will repost it in the web dev area.
|
|
|
|
|
Hello friends I want to run a store procedure in a job schedule each time the server starts. How can I do this?
|
|
|
|
|
|
Thank u Mika. I hope that will work but as I am using Sql Server 2005, And the Sql Server Agent is disabled here by default so I could not do u told me to do. Can u plz tell me how can I activate Sql Server Agent of SQL Server 2005 ?
Thanks in Advance
Johnny
|
|
|
|
|
SQL Server 2005 Standard and Enterprise editions ship with an agent, but if you are using Express edition, there is no way you can use SQL Server's own agent (Express doesn't include that).
However, there are several alternatives, for example SQL Agent: A Job Scheduler Framework[^]
I don't know if that's capable of running a job when the agent starts (that was your original question), but you can take a look.
Mika
|
|
|
|
|
Hi,
I am using datetime variable in one table.I want to convert the date in the following format "yyyy-mm-dd hh:mm:ss". Now I am using conevrt(datetime,getdate(),120).But its returns "yyyy-mm-dd hh:mm:ss.mmm".
Regards,
Subbu
|
|
|
|
|
try this
select convert(varchar(10), getdate(), 23)+' '+convert(varchar(12),getdate(),14)<br />
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.
|
|
|
|
|
A DateTime value is stored in no particular format, a format is only applied when the value is displayed.
|
|
|
|
|
Im returning an XML from a stored procedure using "FOR XML, XMLDATA". Is there any way to specify the name space to be returned? I want to return eg (xmlns="http://App.myapp") and not the default(xmlns="x-schema:#Schema1")
Thanks
|
|
|
|
|
|
Hello!
The uniqueidentifier produces an automated value and it is not acting likely PK in Access database. Therefore I decided to create a uniqueidentifier function which produces a unique value. I defined the first column as an uniqueidentifier column in my table and assigned char(30) instead of uniqueidentifier newID() where I assign produced value.
My question is: is it still mandatory to define this column as uniqueidentifier NEWID() column and then later on assign the produced value to this column as shown example below or can I simply define a column which will be used as an uniqueidentifier column with a definition of Char(30) and then I assign the produces value to this column ?
What would be the prons and cons?
Regards,
<br />
<br />
DECLARE @GUID uniqueidentifier<br />
SET @GUID=NEWID()<br />
INSERT Item VALUES(@GUID,ProducedIdentifier)<br />
<br />
<br />
What a curious mind needs to discover knowledge is noting else than a pin-hole.
|
|
|
|
|
I'm not sure I follow you.
To use a Guid, use the uniqueidentifier datatype.
To have it created automatically, set RowGuid to Yes.
To create the Guid separately, set RowGuid to No.
If you don't want to use Guid, don't use the uniqueidentifier datatype.
Don't use character data as a primary key (or at least very rarely).
What problem are you having?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
