Re: Scalar / Navigation properties? - Database Discussion Boards

Mycroft Holmes11-Jun-09 22:33

11-Jun-09 22:33

One area where you are paying dearly is the cursor (they are EVIL) in the function, I use the following to concat string lists.

SET @List = ''

SELECT @List = @List + CASE WHEN @List = '' THEN '[' + AttrType + ']' ELSE ', [' + AttrType  + ']' end
FROM @TblAttr

You need to convert the numerics and remove the [] brackets. You can see it in use in this article[^].

Never underestimate the power of human stupidity
RAH

How to schedule a job in SQL Server 2005?

gunjan.mits11-Jun-09 0:11

gunjan.mits

11-Jun-09 0:11

Hi.. to all
I want to schedule a job(want to execute a query to delete a table)which is execute after a sometime like a repeated task.
Can any one give Step by Step solution how can i do it with Server Agent in SQL Server 2005.

Thanks

Re: How to schedule a job in SQL Server 2005?

Robin_Roy11-Jun-09 1:01

Robin_Roy

11-Jun-09 1:01

Open your SQL Server Management Studio
Expand SQL Server Agent, Right click on Job and click new job and follow the on screen instructions

Re: How to schedule a job in SQL Server 2005?

Jerry Hammond11-Jun-09 4:26

Jerry Hammond

11-Jun-09 4:26

And we don't want to let Mister T-SQL feel left out...

Execute sp_add_job to create a job.

Execute sp_add_jobstep to create one or more job steps.

Execute sp_add_schedule to create a schedule.

Execute sp_attach_schedule to attach a schedule to the job.

Execute sp_add_jobserver to specify the target servers on which the job is to run.

"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering

Scalar / Navigation properties?

pankazmittal10-Jun-09 7:48

pankazmittal

10-Jun-09 7:48

Hi
Simple question, I assume. In SQL tables, what is Scalar and Navigation properties? And what is the use?

Thanks

Re: Scalar / Navigation properties?

saanj10-Jun-09 23:34

saanj

10-Jun-09 23:34

MSDN has very good articles on this:

http://msdn.microsoft.com/en-us/library/bb738485.aspx[^]

http://msdn.microsoft.com/en-us/library/bb738520.aspx[^]

Regards
Saanj

There is no foolish question, there is no final answer...

Re: Scalar / Navigation properties?

Niladri_Biswas17-Jun-09 20:58

Niladri_Biswas

17-Jun-09 20:58

Why you are asking straightforward question which can be available in the net.

Are you testing others?

Niladri Biswas

Re: Scalar / Navigation properties?

pankazmittal18-Jun-09 3:30

pankazmittal

18-Jun-09 3:30

Oh well! what made you think that I didn't look around. Maybe what I read elsewhere was not easy to grasp.

It would be suffice to say that your response is entirely against the essence of this forum. Not only your's and my time got wasted but of others too who will read all this. Please refrain from such thoughts and instead provide the relevant response if you know.

Thanks anyway!

Automatic Database Source Control System?

Yuval Naveh10-Jun-09 4:08

Yuval Naveh

10-Jun-09 4:08

Hi,

I'm looking for an Open Source Automatic Database Control System - that is a system that does automatic check-ins of database objects and its data (e.g. once a day) into CVS/SVN, and thus allows keeping track of all the changes done to the database.

Do you know of such a system?

Thanks
Yuval

"The true sign of intelligence is not knowledge but imagination." - Albert Einstein

Re: Automatic Database Source Control System?

Colin Angus Mackay10-Jun-09 5:07

Colin Angus Mackay

10-Jun-09 5:07

What you are looking for is a version managed relational database. I used to use a system called Smallworld that allowed version management of the database.

For everyone else, you create audit tables. i.e. tables that look like the ones you already have but with additional auditing columns such as change timestamp, username, etc.

Man who stand on hill with mouth open wait long time for roast duck to drop in

JOIN on two tables where with a MAX value [modified]

Gareth H10-Jun-09 2:59

Gareth H

10-Jun-09 2:59

I want to do a JOIN on two tables, and return all the values from that JOIN where the SS.TIME_RECCEIVED is the MAX value. I have got the below SQL so far, but this only returns the two columns, i need them all. I tried doing SS.* but i a get

ORA-00979: not a GROUP BY expression<br />

SELECT SS.SERVICE, MAX(SS.TIME_RECEIVED)
FROM ISS_BSM_SVCSTATES_Table SS
INNER JOIN ISS_BSM_GeoLocation_Table GEO
ON SS.SERVICE = GEO.SERVICE_NAME 
GROUP BY SS.SERVICE
ORDER BY SS.SERVICE ASC

Any ideas?

On a side note, i've tried the below, but keep getting ORA-01722: invalid number, but if a just do a SELECT MAX() on its own, it does not complain about invalid number.

SELECT *
FROM ISS_BSM_SVCSTATES_Table SS
INNER JOIN ISS_BSM_GeoLocation_Table GEO
ON SS.SERVICE = GEO.SERVICE_NAME
WHERE SS.SERVICE = (SELECT MAX(SS.TIME_RECEIVED) FROM ISS_BSM_SVCSTATES_Table WHERE SS.SERVICE = GEO.SERVICE_NAME)

Regards,
Gareth.

(FKA gareth111)

modified on Wednesday, June 10, 2009 9:12 AM

Re: JOIN on two tables where with a MAX value

Gareth H10-Jun-09 5:55

Gareth H

10-Jun-09 5:55

Problem solved:

select * from iss_bsm_svcstates_table ss, iss_bsm_geolocation_table geo
where ss.service = geo.service_name and ss.time_received in 
(select max(ss1.time_received) from iss_bsm_svcstates_table ss1 group by ss1.service)

Regards,
Gareth.

(FKA gareth111)

Re: JOIN on two tables where with a MAX value

Blue_Boy10-Jun-09 21:22

Blue_Boy

10-Jun-09 21:22

Still another way.

select * from iss_bsm_svcstates_table ss, iss_bsm_geolocation_table geowhere ss.service = geo.service_name and ss.time_received in 
(select top 1 ss1.time_received from iss_bsm_svcstates_table ss1 where ss1.time_received  = ss.time_received  order by ss1.service desc)

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.

Database Certificate...................

Isaac Gordon10-Jun-09 1:34

Isaac Gordon

10-Jun-09 1:34

Hi Experts
I am also ask this Question many time but not getting Proper Answer.
What is the Use Of database Certificate(in case of Security).
how to create and use it to in Database Security.
pls Help Me
Thank u
Dinesh Sharma

execute procedure

Abdul Rahman Hamidy9-Jun-09 20:59

Abdul Rahman Hamidy

9-Jun-09 20:59

Dear All,
I have sql query which is

create proc sp_detail
(
@p varchar(max)
)
as
select * from tblName where code in (@p)
go

and i am calling it like following

declare @codes varchar(max)
select @codes=cast(quotename('28-2801','''')+','+quotename('28-2802','''') as varchar(max))
print @codes
exec [sp_getCommunityDetail] @codes

this doesent work, while i have records in table.
i want to do like this
select * from tblName where code in ('28-2801','28-2802',....)
how can i acheieve it in procedure and how do i pass such thing to procedure?

Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan

Re: execute procedure

Vimalsoft(Pty) Ltd9-Jun-09 21:25

Vimalsoft(Pty) Ltd

9-Jun-09 21:25

Good Morning Abdul

Stop Stop Stop Stop Stop

You created a SP called sp_detail

and you said you calling it in the Seconds statements, but there is no place in the second statement you are using the created sp.

i don't see the created Sp has anything to do with

declare @codes varchar(max)
select @codes=cast(quotename('28-2801','''')+','+quotename('28-2802','''') as varchar(max))
print @codes
exec [sp_getCommunityDetail] @codes

please before you post make sure you are posting the right things, it seems you posted the wrong Sp

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

Re: execute procedure

Abdul Rahman Hamidy9-Jun-09 21:44

Abdul Rahman Hamidy

9-Jun-09 21:44

oh i am really sorry, u r right, but the name of procedure was written wrong in here, that is infact sp_detail, i am really sorry again, would u please help me?

Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan

Re: execute procedure

Vimalsoft(Pty) Ltd9-Jun-09 22:04

Vimalsoft(Pty) Ltd

9-Jun-09 22:04

Good Morning Abdul

If you have create Your Sp like this

create proc sp_detail
(
@p varchar(max)
)
as
select * from tblName where code in (@p)
go

if you want to execute it , you have to do the Following

declare @codes varchar(max)
select @codes=cast(quotename('28-2801','''')+','+quotename('28-2802','''') as varchar(max))
exec [sp_detail] @codes

Now to understand the Second problem you are Having, if you say it doesnt work , do you get any sort of Error or you get a logical error , please give more info.

Vuyiswa Maseko

Re: execute procedure

Abdul Rahman Hamidy9-Jun-09 22:18

Abdul Rahman Hamidy

9-Jun-09 22:18

i am getting no record, while there is record in table
if i use the select statement instead of procedure i can get the result

select * from tblName where code in ('28-2801','28-2802',....)
return 2 rows, while procedure returns zero (0) rows.

Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan

Re: execute procedure

Ashfield10-Jun-09 1:16

Ashfield

10-Jun-09 1:16

It won't work. You need to use dynamic sql to do this, sql server does not evaluate the variable. ALternatively, split the variable into a temp table of some sort.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: execute procedure

Abdul Rahman Hamidy10-Jun-09 1:33

Abdul Rahman Hamidy

10-Jun-09 1:33

yeah, i wrote a function which it was splitting the string based on comma, and then inserting those into the temp table. the temp table had my code in records, but still the procedure didnt worked that way too.
i think there is something tricky in such cases, i am searching to find that case. i need your help too.

Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan

Re: execute procedure

Ashfield10-Jun-09 2:58

Ashfield

10-Jun-09 2:58

Well, its a fairly simple join.

declare @Split TABLE (.....)
insert into @Split
	select .. from your_split_function

select * from table t1
join @split s on s.code = t1.code

if it still fails to return records make sure you are trimming spaces from the values in your split function

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: execute procedure

Abdul Rahman Hamidy10-Jun-09 23:00

Abdul Rahman Hamidy

10-Jun-09 23:00

thank dude, this method is very cool, trimming mostly solved my issue

Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan

Re: execute procedure

Ashfield11-Jun-09 1:29

Ashfield

11-Jun-09 1:29

Please to have helped

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Setting password to my own SQL database.

deepseeindeepsy9-Jun-09 20:23

deepseeindeepsy

9-Jun-09 20:23

Hi,

I want to protect my database with ID & Password.
How it will be done. Smile | :)

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