|
Don't know if it's pre-installed. I would believe that not. I think that MDAC installs also dBase drivers, but I'm not sure.
The installation of a single driver is basically copying the dll files and then registering the dll's. After that a DSN can be created programmatically or using Control Panel.
The need to optimize rises from a bad design
|
|
|
|
|
Recently I installed MDAC to have a look to an old VB6 application. It causes me some troubles while installing.
So, I think maybe would be a good idea to use my own DBF class, so I won't have deployment problems...
Best regards,
Jaime.
|
|
|
|
|
Hi , i need all dates of the current month . m using sql server 2000 .
Thanx in Advance .
|
|
|
|
|
Thanx i got it . And the sol is :
declare @current varchar(100)
set @current =(select convert(varchar,getdate(),101))
declare @firstdate varchar(100)
declare @lastdate varchar(100)
set @firstdate=(SELECT convert( varchar,DATEADD(dd,-(DAY(DATEADD(mm,1,@current))-1),DATEADD(mm,0,@current)),101))
set @lastdate=(SELECT convert(varchar, DATEADD(dd, -DAY(DATEADD(m,1,@current)), DATEADD(m,1,@current)),101))
print @firstdate
print @lastdate
declare @i int
declare @k varchar(100)
select @i = datediff(dd,@firstdate,@lastdate)
print @i
declare @j int
set @j=0
while(@j<@i)
begin
select @k = dateadd( dd,1,@firstdate)
print @k
set @firstdate=@k
set @j =@j + 1
end
|
|
|
|
|
I have a table named test in SQL 2005 database. It has three columns (ourKey, ourData1, ourData2). Its first column (ourKey) is primary key column and it updates itself. It has one entry (1,45,56).
I would like to have another entry based on any other entry. I would like to insert entry which should produce (2,46,56)
I would like to read first entry from ourData1 then add one and rest of the table should be same.
Any help will be very much appreciated.
in previous thread
Re: delete duplicate rows by Ashfield
#table is used. What is #table?
Agha Khan
modified on Friday, October 3, 2008 11:42 PM
|
|
|
|
|
Do yo mean something like:
INSERT INTO test (ourData1, ourData2) SELECT MAX(ourData1) + 1, MAX(ourData2) FROM test;
Tables prefixed with # are local temporary tables.
The need to optimize rises from a bad design
|
|
|
|
|
Thank you. but I was looking to copy of Nth row not the Max of colum. i.e. I want to copy 3rd row or 2nd row.
My table has three entries.
1,45,30
2,3,40
3,9,80
the 4th should be if 2 row (2,3,40) is selected
4,3,40
if 1st row (1,45,30) is selected
4,46,30
This means answer depends on selection.
But your answer helped me to understand.
this works
INSERT INTO test (ourData1, ourData2) SELECT (select ourData1 from test where ourkey = 3 ) + 1 , (select ourData2 from test where ourkey = 3 );
but is there short syntex? 
I tried something like this
Select * into #table from test where ourkey = 3
select * from #table
update #table set ourData1 = ourData1 + 1
insert into test select * from #table
But I cant not insert identity column, it is my primey key and it is default. I not have to supply it.
Otherwise it looks it works.
Kind regards
Agha
modified on Saturday, October 4, 2008 11:05 AM
|
|
|
|
|
If I understood you correctly, you could modify the insert statement to following:
INSERT INTO test (ourData1, ourData2)
SELECT ourData1 + 1 , ourData2
FROM test
WHERE ourkey = 3 ;
The need to optimize rises from a bad design
|
|
|
|
|
This works fine.
But that was just an example. In real application I have many columns, so I tried with #table, which works fine, but unable to update primey key column. Please check previous reply.
Kind regards
Agha
I tried something like this
Select * into #table from test where ourkey = 3
select * from #table
update #table set ourData1 = ourData1 + 1
insert into test select * from #table
unable to update primey key column
modified on Saturday, October 4, 2008 11:28 AM
|
|
|
|
|
Yes, since the primary key is autogenerated, you cannot insert into it or update it. Can't you list all of the columns instead of * and leave the primary key out? Like:
INSERT INTO Test (NotKeyColumn1, NotKeyColumn2, ...)
SELECT NotKeyColumn1, NotKeyColumn2, ... FROM #table
An alternative could be that you drop the key column from temp table before insert. Something like:
...
UPDATE #table SET ourData1 = ourData1 + 1
ALTER TABLE #table DROP COLUMN KeyColumn
INSERT INTO test SELECT NULL, * FROM #table
However, I'm not sure if it works. I added NULL to the beginning, because if you do not list the target columns in insert, you must supply a value for all columns (in the order they appear in the target table).
The need to optimize rises from a bad design
|
|
|
|
|
Thank you.
There is no need for adding null,
but it works very well.
Thank you again.
Kind regards
Agha
|
|
|
|
|
Thank you.
It works.
Best regards
Agha
|
|
|
|
|
You're welcome.
The need to optimize rises from a bad design
|
|
|
|
|
Can any one tell the query to delete only duplicate rows from any table ?
|
|
|
|
|
You need to do it in steps:
Select * into #table from mytable where 1 = 2
insert into #table select distinct * from mytable
truncate table mytable
insert into mytable select * from #table
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Example Table :
Cl1 Cl2
ID SecndClmnNAme
Delete From Tab Where ID NOt In Select Distinct ID From Tab)
|
|
|
|
|
Are you sure this works? Surely, if there are duplicate rows there are duplicate ids?
I mean, if you have this
id name
1 bob
2 fred
1 bob
your query will not delete anything as all ids occur in the distinct.
Or have I missed something?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
delete from tablename where id not in (select distinct id from tablename )
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.
|
|
|
|
|
Are you sure this works? Surely, if there are duplicate rows there are duplicate ids?
I mean, if you have this
id name
1 bob
2 fred
1 bob
your query will not delete anything as all ids occur in the distinct.
Or have I missed something?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
you are not suppose to have that problem you know?!
my advice for you is to start all from the beginning!!!
Question yourself why you need that? can´t you achieve what you want to do without creating duplicate rows?
can you see that you are creating unnecessary procedures!
trying to help you there![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
how to restore database of sqlserver2005 into sqlserver2000
|
|
|
|
|
I am pretty sure you can't do this.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Cannot be done using restore. You can use SSIS, bcp or other tools to transfer data from 2005 to 2000.
The need to optimize rises from a bad design
|
|
|
|
|
hi all,
i am new to SQL Server
i want to Create backup of my database and restore it programmatically.
i search many example on Google and also on Code project(USING SMO object) but no link fulfill my requirement.Each example give error "Backup failed for Server 'MachineName\InstanceName'.
Then i use following stored procedure to create backup
Create PROCEDURE CreateBackupFile<br />
@BackUpPath varchar(200)<br />
AS<br />
BEGIN<br />
<br />
Backup Database Test to disk = @BackupPath <br />
END
above procedure works well when i run via VB.net
but now the problem with Restore.I use following statement
Create PROCEDURE RestoreBackupFile<br />
@RestoreFilePath varchar(200)<br />
AS<br />
BEGIN<br />
<br />
RESTORE DATABASE Vehicle<br />
FROM DISK = @RestoreFilePath<br />
END
but when i run this procedure then it give error "Database is use for this session. Use Master'
after that i modify above procedure as following
Create PROCEDURE RestoreBackupFile<br />
@RestoreFilePath varchar(200)<br />
AS<br />
BEGIN<br />
Use master<br />
Go<br />
RESTORE DATABASE Vehicle<br />
FROM DISK = @RestoreFilePath<br />
END
but now one more limitation appear that we can not use the 'Use database' statement within Stored procedure,function and trigger
Pleast suggest how can i resolve this problem.
|
|
|
|
|
RestoreBackupFile-procedure cannot be in the database you are restoring (restore command restores the database completely including stored procedures tables etc).
You must either create the procedure into another database or call the restore from the program.
The need to optimize rises from a bad design
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
