Re: sqlserver apostrophe problem. - Database Discussion Boards

gerrybrennan7-Dec-09 20:54

7-Dec-09 20:54

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

Re: sqlserver apostrophe problem.

Ashfield7-Dec-09 20:56

Ashfield

7-Dec-09 20:56

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

Re: sqlserver apostrophe problem.

gerrybrennan7-Dec-09 21:14

gerrybrennan

7-Dec-09 21:14

Hi Bob,

Thank you for your help.

When I use single quotes i get an error

Line 16: Incorrect syntax near 'SI'.

G

Re: sqlserver apostrophe problem.

Niladri_Biswas7-Dec-09 21:42

Niladri_Biswas

7-Dec-09 21:42

Try

and TransType =

''SI''

Niladri Biswas

Re: sqlserver apostrophe problem.

Nilesh.Kuvar7-Dec-09 23:28

Nilesh.Kuvar

7-Dec-09 23:28

Hello,

Generally here, apostrophe is considered as escape sequence character
You may execute as

TransType = ''SI''

This may solve you problem
Thumbs Up | :thumbsup:

Re: sqlserver apostrophe problem.

Blue_Boy8-Dec-09 0:22

Blue_Boy

8-Dec-09 0:22

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

Re: sqlserver apostrophe problem.

gerrybrennan8-Dec-09 5:41

gerrybrennan

8-Dec-09 5:41

Eventually got it

''''SI'''' Solved the problem.

Many Thanks

G.

Re: sqlserver apostrophe problem.

Blue_Boy8-Dec-09 6:09

Blue_Boy

8-Dec-09 6:09

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

How to remove the duplicate records with the old timestamp.

chakran7-Dec-09 20:00

chakran

7-Dec-09 20:00

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.

Re: How to remove the duplicate records with the old timestamp.

dan!sh 7-Dec-09 20:58

dan!sh

7-Dec-09 20:58

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...!!

Re: How to remove the duplicate records with the old timestamp.

chakran7-Dec-09 21:16

chakran

7-Dec-09 21:16

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'

Re: How to remove the duplicate records with the old timestamp.

Niladri_Biswas7-Dec-09 21:35

Niladri_Biswas

7-Dec-09 21:35

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
Smile | :)

Niladri Biswas

Re: How to remove the duplicate records with the old timestamp.

chakran7-Dec-09 22:12

chakran

7-Dec-09 22:12

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.

Re: How to remove the duplicate records with the old timestamp.

Niladri_Biswas7-Dec-09 22:34

Niladri_Biswas

7-Dec-09 22:34

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
Smile | :)

Niladri Biswas

Re: How to remove the duplicate records with the old timestamp.

chakran7-Dec-09 22:41

chakran

7-Dec-09 22:41

it will not work out. but the example and query you sent in the previous reply is working fine even we executed query many times.

delete from @t where timestamps in(select MIN(timestamps) from @tgroup by bugdate having (COUNT(timestamps)>1))..

As you have used having clause I think we need to put the same thing in my query. I tired but getting syntax errors... Can you think once again...

Re: How to remove the duplicate records with the old timestamp.

chakran7-Dec-09 23:22

chakran

7-Dec-09 23:22

Got the solution.. below query will be workout

            DELETE T1
     FROM BugsDB_DefectVolume T1, BugsDB_DefectVolume T2
     WHERE T1.[Product ID] = T2.[Product ID]
     and T1.BugDate = T2.BugDate and T1.Timestamps < T2.Timestamps

Trigger in a certain period of time of day

Member 47041437-Dec-09 19:07

Member 4704143

7-Dec-09 19:07

Hi all,

I have two tables A(a1,a2,a3), B(b1,b2,b3) and a period of time Delta t [t1,t2].

When it is between t1 - t2 of time, if there is a row inserted in table A, the field b1 of table B will be updated such as b1=b1+1.

Moreover, I also have a web application which can assign value for t1, t2 through an aspx page.

Could you tell me how to do that?

Thank kiu so much.

Re: Trigger in a certain period of time of day

_Damian S_7-Dec-09 19:25

_Damian S_

7-Dec-09 19:25

If you are using SQL Server, you could set up a trigger on table a, so when a row is inserted it checks if the time is between t1 and t2, and if so update table b.

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!!

Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!!
Do something wonderful - make a donation to Camp Quality today!!

Re: Trigger in a certain period of time of day

Member 47041438-Dec-09 4:09

Member 4704143

8-Dec-09 4:09

Could you give me an example because I do not know how to assign value for t1 & t2?

Thank kiu!

SQLServer Default Database Changed

Tim Carmichael7-Dec-09 5:46

Tim Carmichael

7-Dec-09 5:46

On a SQLServer 2000 server, the default database for the user accounts was changed. This appears to have occurred for ALL users accounts, both network login names and SQLServer named accounts.

Any idea how this can happen?

Our DBA resigned Thanksgiving week, so there is no help onsite.

Thanks in advance,

Tim

Re: SQLServer Default Database Changed

loyal ginger7-Dec-09 6:03

loyal ginger

7-Dec-09 6:03

There are multiple ways to change the default database for the user accounts. You will need to retrieve a backup to restore the old default databases for all users. You may need to talk to the previous DBA about this issue. It is time consuming to find the cause by yourself. Was the previous DBA happy or not?

Re: SQLServer Default Database Changed

David Mujica7-Dec-09 6:10

David Mujica

7-Dec-09 6:10

Use this statement to verify what you think happened ...

select name,default_database_name,modify_date from sys.sql_logins

If, indeed the changes were made, you will see when they happened and someone here might be able to work you through fixing it.

You may want to start by changing your administrative passwords since your DBA left. Make sure to tighten up any remote access accounts, etc.

Re: SQLServer Default Database Changed

Tim Carmichael7-Dec-09 7:08

Tim Carmichael

7-Dec-09 7:08

David Mujica wrote:
Use this statement to verify what you think happened ...

Users could access the system last Thursday; on Friday, they couldn't. The default database was changed to a database that is used for sending e-mail messages, so, they neither need nor have access to it.
The accounts (there are only a handful) have been manually updated.

David Mujica wrote:
select name,default_database_name,modify_date from sys.sql_logins

Since this is a SQLServer 2000 node, the listed table didn't work, but it gave me enough information to move forward. I used the following:

select l.name, xdate1 as creation_Date, xdate2 as modify_date, l.dbid,
d.name as defaultdb
from sysxlogins l inner join
sysdatabases d on l.dbid = d.dbid

And that just opened up more questions...

One of the accounts was created in 2007, updated a minute later and not touched after that, but, they have a default database set to a database that didn't exist at the time.

As far as access rights, when someone leaves the company, their password is changed and remote access removed.

Tim

How i can write query return the douplicate record with deffrint IDs ?! [modified]

AJ Hoge6-Dec-09 22:06

AJ Hoge

6-Dec-09 22:06

Hi ,ALL

If i have this Customer table :

ID | CustomerName | PhoneNum | City |Street
<code>19       jon     555     NK    st.5</code>
21          Mark       236      TK      KA.st
<code>37       jon     555     NK    st.5</code> 
45          AJ         800      LA      WS.17

We note that Customer Name "Jon" added in this table tow time with deffrint IDs .
In my real database , i have Thousands of this case. Cry | :((

How i can write query return the douplicate record with there IDs ? Confused | :confused:

, like :

ID | CustomerName | PhoneNum | City |Street
19         jon        555     NK     st.5
37         jon        555     NK     st.5

modified on Monday, December 7, 2009 4:18 AM

Re: How i can write query return the douplicate record with feffrint IDs ?!

Mycroft Holmes6-Dec-09 22:20

Mycroft Holmes

6-Dec-09 22:20

Do some basic research[^]

This is a common problem that has been solved over and over again. The first 2 items look interesting.

Never underestimate the power of human stupidity
RAH

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.