Database

__yash__20-Nov-07 21:29

__yash__

20-Nov-07 21:29

Yeah true. My last question was in addition to the prevoius one.
You clarified my doubt on SQL 2000. Now, again, I wanted to know the same for for SQL 2005 Express as well as Enterprise editions.

Thanks.

BugWacker21-Nov-07 3:29

BugWacker

21-Nov-07 3:29

SQL Express works fine on XP. So does SQL 2005 standard. The only problem with SQL Express is the number of connections (5 If I remember correctly.) Also Express does not have fun things like SSIS or SSAS. It does have the reporting service. SQL Express is free to download.

WPWood

Marek Grzenkowicz21-Nov-07 0:08

Marek Grzenkowicz

21-Nov-07 0:08

What do you mean by local database? Is it a database that you can access only from the computer where the server is installed?

INSERT INTO and when is table updated

__yash__21-Nov-07 0:20

__yash__

21-Nov-07 0:20

There are two kinds of database installation that we can have, right?
1. Server database
2. Desktop database
(I am not well versed with the exact terminologies but the above i got after a little googling)
so what i meant by local was 'desktop database' (like MSDE)

Anyway, I found a good link:
http://download.microsoft.com/download/8/1/7/817bb6e6-9d97-4a5d-be8d-508256ae4045/ChoosEd.doc[^]

john john mackey20-Nov-07 11:55

john john mackey

20-Nov-07 11:55

Hi,

I'm appending data to a table with the INSERT INTO command. I am also operating on the same table with recordset commands .MoveFirst and .Find

What I am noticing is that my INSERT commands are not immediately recognized until I close the table...

Is there a way to make my INSERT commands stick right away?

I'm working in VBA/Access through a user form and two tables that support it. I'm probably talking above the specifics, but this is what appears to be happening in my problem.

Thanks,
Johnny

Re: INSERT INTO and when is table updated

john john mackey20-Nov-07 13:07

john john mackey

20-Nov-07 13:07

addendum:

I am doing the INSERT INTO tbl_Defects followed by the .MoveFirst and .Find commands (in that order.)

What seems to be failing is the .Find (the INSERT INTO didn't refresh the table???)

Thanks.

Re: INSERT INTO and when is table updated

ChandraRam21-Nov-07 1:09

ChandraRam

21-Nov-07 1:09

john john mackey wrote:
What seems to be failing is the .Find (the INSERT INTO didn't refresh the table???)

What is probably not refreshed is the recordset...

Comparing only the TIME part of DATETIME

Chris Meech20-Nov-07 5:51

Chris Meech

20-Nov-07 5:51

I have a column called AUDIT_EVENT_DATE that is a DATETIME stamp for when an event occurred. I'm trying to put a query together that lets me see only the records for events that occurred between 18:30 and 18:45 for the last two months.

select to_char(audit_event_date,'DD-MON-YYYY HH24:MI:SS') from transaction_audit
 where trunc(audit_event_date) > to_date('01-OCT-2007','DD-MON-YYYY')
   and audit_event_date between to_date('18:30:00','HH24:MI:SS') and to_date('18:45:00','HH24:MI:SS')
 order by audit_event_date

The above only supplies me with audit records dated on November the 1st. I think that's because of a truncation thing that is happening. Wouldn't it be nice if there was a handy function called TIME_only, and then I could do this instead

select to_char(audit_event_date,'DD-MON-YYYY HH24:MI:SS') from transaction_audit
 where trunc(audit_event_date) > to_date('01-OCT-2007','DD-MON-YYYY')
   and time_only(audit_event_date) between time_only(to_date('18:29:59','HH24:MI:SS')) and time_only(to_date('19:00:00','HH24:MI:SS'))
 order by audit_event_date

Anybody have any ideas of how to do this? Thanks.

Chris Meech
I am Canadian. [heard in a local bar]
Donate to help Conquer Cancer[^]

Re: Comparing only the TIME part of DATETIME

andyharman20-Nov-07 6:36

andyharman

20-Nov-07 6:36

Hi Chris. How about:

to_char(audit_event_date, 'HH24:MI:SS') between '18:29:59'
      and '19:00:00'

Also (if I remember my Oracle SQL correctly) you can use "trunc(date) to remove the time component from datetime fields.

Regards
Andy

Re: Comparing only the TIME part of DATETIME

Chris Meech20-Nov-07 7:13

Chris Meech

20-Nov-07 7:13

Thanks Andy. I couldn't see the forest for the trees. Smile | :)

That worked perfect for me.

Chris Meech
I am Canadian. [heard in a local bar]
Donate to help Conquer Cancer[^]

T-SQL Question

ffowler20-Nov-07 5:42

20-Nov-07 5:42

Hello All,

I am looking for a little assistance with a problem I am having. We have been adding new users to our database as needed without issue; the problem begins when I have to assign permissions to all of the stored procedures in a db for that user. I know the command to give the user Execute permissions but I have to do it procedure by procedure.
Isn't there a simpler way of scripting this to grant execute permissions to all of the procedures?

Any help would be greatly appreciated.

F.Fowler

Big Grin | :-D

andyharman20-Nov-07 6:41

andyharman

20-Nov-07 6:41

Try:

select 'grant execute on ' + name + ' to username' from sysobjects where xtype = 'P'
order by name

Then run the resulting script (substituting the names of your users).

ffowler20-Nov-07 7:21

20-Nov-07 7:21

Hello Andy,

I have no idea what I am doing wrong but I keep getting the error

"Msg 207, Level 16, State 1, Line 1
Invalid column name 'CTTest'."

I just entered the command you have listed as such:

select 'grant execute on ' + CTTest + ' to ACM\ttomaselli' from sysobjects where xtype = 'P' order by name"

and the error pops up. I am certain this is what I need but why does it think that CTTest is a column when it is actually the database?

F

astanton197820-Nov-07 9:43

astanton1978

20-Nov-07 9:43

you have to use name

select 'GRANT EXECUTE ON ' + name + ' TO Bob' from sysobjects where xtype = 'P' order by name

ffowler20-Nov-07 9:51

20-Nov-07 9:51

Got it now.

ffowler20-Nov-07 9:54

Dirrerence Between these Two Queries

20-Nov-07 9:54

Thank you all for your help!
Made it easy.

F

Vimalsoft(Pty) Ltd20-Nov-07 3:38

20-Nov-07 3:38

hi i all i have the following two Queries, i want to know the Difference Count Between these two Queries,by combining the query and display a difference.

SELECT	COUNT(DISTINCT PROPERTY_KEY) PROPERTY_KEY 
FROM	  sde.VW_Toi_Deeds_Vuyiswa
WHERE	FUNCTION_KEY like '%GEOSS%'
GO

SELECT	COUNT(*) AS [UNITS IN PRO-V]
FROM	SDE.PROPERTY
WHERE	FUNC_KEY LIKE 'GEOSS%'

.

THANKS

Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://www.Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

Jason Lepack (LeppyR64)20-Nov-07 3:49

Jason Lepack (LeppyR64)

20-Nov-07 3:49

This should illustrate the difference.

CREATE TABLE #foo (<br />
	foo_id TINYINT )<br />
	<br />
INSERT INTO [#foo] VALUES (1) <br />
INSERT INTO [#foo] VALUES (1) <br />
INSERT INTO [#foo] VALUES (1) <br />
INSERT INTO [#foo] VALUES (1) <br />
INSERT INTO [#foo] VALUES (2) <br />
INSERT INTO [#foo] VALUES (2) <br />
INSERT INTO [#foo] VALUES (2) <br />
INSERT INTO [#foo] VALUES (2) <br />
<br />
SELECT<br />
	COUNT(*)<br />
FROM<br />
	[#foo]<br />
	<br />
SELECT<br />
	COUNT(DISTINCT [#foo].[foo_id])<br />
FROM<br />
	[#foo]<br />
	<br />
DROP TABLE [#foo]

Vimalsoft(Pty) Ltd20-Nov-07 4:02

20-Nov-07 4:02

Thanks man,

But now this two Queries, is from Two tables,

thanks

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

Jason Lepack (LeppyR64)20-Nov-07 4:27

Jason Lepack (LeppyR64)

20-Nov-07 4:27

Oh, sorry. I misunderstood your question.

This should do what you want then:

SELECT<br />
	A.PROPERTY_KEY - B.[UNITS IN PRO-V]<br />
FROM<br />
	(SELECT<br />
		COUNT(DISTINCT PROPERTY_KEY) PROPERTY_KEY<br />
	FROM<br />
		sde.VW_Toi_Deeds_Vuyiswa<br />
	WHERE<br />
		FUNCTION_KEY like '%GEOSS%') A,<br />
	(SELECT<br />
		COUNT(*) AS [UNITS IN PRO-V]<br />
	FROM<br />
		SDE.PROPERTY<br />
	WHERE<br />
		FUNC_KEY LIKE 'GEOSS%') B

Vimalsoft(Pty) Ltd20-Nov-07 20:27

20-Nov-07 20:27

Thanks man it worked Perfectly

Thanks

Vimalsoft(Pty) Ltd20-Nov-07 21:06

20-Nov-07 21:06

Hi Leppyr64

i have to Display records not Count, how do i archive this. in the Above Query that you gave me.

SELECT
A.PROPERTY_KEY - B.[UNITS IN PRO-V]
FROM
(SELECT
COUNT(DISTINCT PROPERTY_KEY) PROPERTY_KEY
FROM
sde.VW_Toi_Deeds_Vuyiswa
WHERE
FUNCTION_KEY like '%GEOSS%') A,
(SELECT
COUNT(*) AS [UNITS IN PRO-V]
FROM
SDE.PROPERTY
WHERE
FUNC_KEY LIKE 'GEOSS%') B

Thanks

Jason Lepack (LeppyR64)21-Nov-07 2:33

Jason Lepack (LeppyR64)

21-Nov-07 2:33

I don't understand what you want.

You asked for the difference between two queries that returned a number.

A - B = C

If what I gave you is not what you want then I need an example. I'm done guessing.

Please post DDL, Example Data, and Expected results.

Vimalsoft(Pty) Ltd21-Nov-07 3:46