|
If the table tblSelectedItems is quite large, you would be better off linking it with an inner join, like this:
replace this bit:
Member 4648370 wrote: WHERE ipkClientsID IN (SELECT ifkSelectedItemsID from tblSelectedItems where sID = @iParam)
With this:
inner join tblSelectedItems on ((c.ipkClientsID = ifkSelectedItemsID) and (sID = @iParam))
|
|
|
|
|
I try to access the DSN settings for an Oracle database and I get the following errors:
The setup routines for the Oracle in OraClient11g_home1 ODBC driver could not be loaded due to system error code 126.
Driver's ConfigDSN, ConfigDriver, or ConfigTranslator failed!
Could not load the setup or translator library
Is this fixable? What is the problem?
The following microsoft support article did not help unfortunately...
http://support.microsoft.com/kb/260558[^]
|
|
|
|
|
|
If you can see it in object explorer try right click and that will give you an option to script it. Other than that, you may be able to pull the info out of syscomments by direct sql (id joins to id from sysobjects)
If this fails, then you have learnt the lesson - always save your sql to a flat file and put in in a vserion control system.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
I have a C# program that has a connection to 2 different databases across 2 different SQL servers.
How can I easily copy a table and all its contents from one database to another using SQL statements in C#
I do not want to use the management studio to perform this and want to do it in my C# program.
Any help would be appreciated.
Thanks
|
|
|
|
|
|
Solution 1:
Take the help of
SSIS for the task. It is handy and simple.
Solution 2:
Using
SMO class , you can create a backup of your database and restore it also.
There are plenty of articles in the net explaining how to do the same using SMO.
Please have a look
Backup and restore SQL databases[^]
Hope this helps
Niladri Biswas
|
|
|
|
|
|
Hi all,
I want to create a trigger relating to table Demo and table UpdateInfo. UpdateInfo is created as following:
CREATE TABLE UpdateInfo
(ID int primary key,
new int,
click int)
The task for this trigger is whenever there is a row inserted to Demo, there is an update in field [new] of UpdateInfo ( new = new +1) , but it wll just work, for example, from 1:00 am to 6:00 am.
If this has been done, I then want to customize the time from an aspx page.
I think I should start with INSTEAD OF trigger but I am lacking in experiences about it.
Could you give me any suggestion?
Thank you so much!
|
|
|
|
|
How is this different from your question yesterday about triggers in a certain time period?
|
|
|
|
|
yep,
But the answer I got yesterday is still dubious. I am still having no ideas about how to check time & check by which syntax.
The question today is one in my effort to find out solution for the question of yesterday, but in a different form
I think INSTEAD OF trigger can help me to sovle prolem. That is the reason for the question today ^^
However, if you think just a standard trigger is quite enough to solve my problem, could you give me a more specific example?
Thanks for your help!
modified on Wednesday, December 9, 2009 8:36 AM
|
|
|
|
|
Hi
I have a stored procedure schemacreate.sql and below is a snippet.
I am having problems with creating the below procedure. More specifically
and TransType = "SI"
Can any one help me with this.
Thank you in advance.
G
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER OFF
IF NOT EXISTS (select * from sysobjects where id = object_id(N'[dbo].[TransCheckifCurrentExists]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[TransCheckifCurrentExists]
as
declare @SQLstr nvarchar(1000)
declare @value int;
set @SQLstr = ''select @value=count(*) from Trans where TransState = 1 and TransType = "SI" ''
exec sp_executesql @SQLstr, N''@value int out'', @value out
return
'
END
|
|
|
|
|
Double quotes are not valid to enclose literals, you need single quotes. I would suugest before executing your sql string you print it (at least for debug) as it soon shows any syntax errors.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Hi Bob,
Thank you for your help.
When I use single quotes i get an error
Line 16: Incorrect syntax near 'SI'.
G
|
|
|
|
|
Try
and TransType =
''SI''
Niladri Biswas
|
|
|
|
|
Hello,
Generally here, apostrophe is considered as escape sequence character
You may execute as
TransType = ''SI''
This may solve you problem
|
|
|
|
|
Use CHAR(39) for single apostrophe.
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
|
|
|
|
|
Eventually got it
''''SI'''' Solved the problem.
Many Thanks
G.
|
|
|
|
|
Good,but when you have complex query and in which you use multiple times single apostrophe then you can become confused by writing much '''' in query.
Regards.
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,
How to remove the duplicate records with the old timestamp. Below is the table, contains 2 columns Bugdate and timestamp. I wanted to remove the duplicate records with the old time stamp. ie first three records. It would be pleasure if any one send me the query.
BugDate---- Timestamp
4/14/2009 12/8/2009 12:54.611
4/19/2009 12/8/2009 12:54.611
4/26/2009 12/8/2009 12:54.611
4/14/2009 12/8/2009 12:58.623
4/19/2009 12/8/2009 12:58.623
4/26/2009 12/8/2009 12:58.623
Thanks.
|
|
|
|
|
Some threads below, people have told how t osearch duplicate records from a table. Check that out. Once you have the records, just find the ones with older timestamp and delete them.
I believe it should be a mere customization of the same query.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
I know the logic for how to get duplicate rows but I need to delete them. Below is the table and my logic for getting duplicate records with old timestamp. But I need to delete these records. Can you help by doing little modification in my code.
ProductID BugDate Bug Timestamp
111 2009-04-12 1 2009-12-08 12:18:07.453
111 2009-04-14 2 2009-12-08 12:18:07.453
111 2009-04-19 1 2009-12-08 12:18:07.453
111 2009-04-12 1 2009-12-08 12:29:07.453
111 2009-04-14 3 2009-12-08 12:29:07.453
111 2009-04-19 2 2009-12-08 12:29:07.453
SELECT [Product ID],BugDate,Bug,[Timestamp]
FROM BugsDB_DefectVolume, (
SELECT [Product ID] AS B, MIN([Timestamp]) AS S
FROM BugsDB_DefectVolume
GROUP BY [Product ID]) X
WHERE [Timestamp] = X.S and [Product ID]='111'
|
|
|
|
|
Try this.
First I am giving a sample demo and then I will modify your query
Sample demo
declare @t table(bugdate varchar(10),timestamps datetime)
insert into @t
select '4/14/2009','12/8/2009 12:54.611' union all
select '4/19/2009', '12/8/2009 12:54.611' union all
select '4/26/2009', '12/8/2009 12:54.611' union all
select '4/14/2009','12/8/2009 12:58.623' union all
select '4/19/2009','12/8/2009 12:58.623' union all
select '4/26/2009','12/8/2009 12:58.623'
Query:
delete from @t where timestamps in(
select MIN(timestamps) from @t
group by bugdate
having (COUNT(timestamps)>1))
select * from @t
Output:
bugdate timestamps
4/14/2009 2009-12-08 12:58:00.623
4/19/2009 2009-12-08 12:58:00.623
4/26/2009 2009-12-08 12:58:00.623
Coming to your code:
Try this
delete from BugsDB_DefectVolume where [Timestamp] in(
select [Timestamp] from (
SELECT [Product ID],BugDate,Bug,[Timestamp]
FROM BugsDB_DefectVolume, (
SELECT [Product ID] AS B, MIN([Timestamp]) AS S
FROM BugsDB_DefectVolume
GROUP BY [Product ID]) X
WHERE [Timestamp] = X.S and [Product ID]='111' )X)Y
Hope this helps
Niladri Biswas
|
|
|
|
|
but have one problem. if there are duplicate records that first time its working fine but when we execute second time all records were deleted from the table. I think we need to put a check some where in the logic.
|
|
|
|
|
I guess, you can do 1 thing.
Put a check like if the timestamp field is not equal to getdate()
like
select
case when (timestamp = getdate())
then call the delete operation
end as checking
Hope this might help you
Niladri Biswas
|
|
|
|