Database Discussion Boards

Re: Matching two tables in SQL on certain Fields

andyharman18-Oct-07 4:55

andyharman

18-Oct-07 4:55

For SQL-Server:

select E1.Primary_Key, E2.Primary_Key,
   IsNull(E1.EmployeeName, E2.EmployeeName) AS EmployeeName,
   IsNull(E1.LastName, E2.LastName) AS LastName
from Employee E1
full outer join Employees E2
on E1.EmployeeName = E2.EmployeeName
and E1.LastName = E2.LastName
where (E1.Primary_Key is null or E2.Primary_Key is null)

Regards
Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Re: Matching two tables in SQL on certain Fields

Vimalsoft(Pty) Ltd18-Oct-07 5:40

Vimalsoft(Pty) Ltd

18-Oct-07 5:40

Thanks and Rate for your Reply

I will try it

Rated

Vuyiswa Maseko,

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

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

put a default value

Sarfaraj Ahmed18-Oct-07 4:36

Sarfaraj Ahmed

18-Oct-07 4:36

Hello Everybody !!

How Can I put a default value in dataColumn

Thanks

Sarfarj Ahmed

select statement to another database on another server

Support12318-Oct-07 2:11

Support123

18-Oct-07 2:11

Hi all,

I have this problem:

i want to write a select statement. But i am connected to one server and i want to select on another database on another server????

How do i go about doing so?

if i run this script:
SELECT

a.*

FROM
OPENROWSET('SQLOLEDB.1','Data Source=<OurServerName>;Initial Catalog=<OurDataBaseOnThatServer>;User ID=<OurUserName>;password=<OurPassword>;','SELECT TOP 1 FROM <OurServerName>.<OurDataBaseOnThatServer>.dbo.<TableNameInThatDataBase>') a

Am i doing something wrong??? i get an error: "Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB.1' reported an error."

Please advise Cry | :((

"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison

P.S.

Support12318-Oct-07 2:16

Support123

18-Oct-07 2:16

I found this code on the internet BUT i haven't run it yet.

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE<br />
go<br />
<br />
EXEC sys.sp_configure N'Ad Hoc Distributed Queries', N'1'<br />
go <br />
<br />
RECONFIGURE WITH OVERRIDE<br />
go<br />
<br />
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE<br />
go

Do you think this could solve my problem? Confused | :confused:

"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison

Re: P.S.

astanton197819-Oct-07 5:22

astanton1978

19-Oct-07 5:22

Probably.... Its better practice to add the other server as linked server if you are going to use the connection often enough. It will simplify your query writing (no openrowset crap needed).

Redneckin with www.wantedband.com

Re: select statement to another database on another server

neeraj_indianic22-Oct-07 20:10

neeraj_indianic

22-Oct-07 20:10

I have seen that to use OPENROWSET command the first parameter should be MSDASQL
the SQLOLEDB is used for OPENDATASOURCE. i have tried this query and working perfectly. Also i hope that you have already enabled the OPENROWSET and OPENDATASOURCE command from the SQL server surface area configuration wizard.

SELECT *
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=yourremoteserver_name;UID=your_remote_user_id;PWD=your_remote_password',
your_remote_database_name.dbo.remote_table_name)

Neeraj Gupta

how to capture count of records from stored procedrure

attalurisubbu18-Oct-07 2:01

attalurisubbu

18-Oct-07 2:01

Hi,

I want only count of a records returning by a particular stored procedure..

Im trying like this..but not coming..

DECLARE @COUNT INT

SET @COUNT = EXEC 'SP NAME'

SELECT @COUNT

please help me..

bala

Re: how to capture count of records from stored procedrure

Illegal Operation18-Oct-07 2:03

Illegal Operation

18-Oct-07 2:03

SELECT COUNT(*) FROM TABLE WHERE Variable = @Variable.

Hope it helps.

Illegal Operation

Making Computer Software Talk

Re: how to capture count of records from stored procedrure

attalurisubbu18-Oct-07 2:06

attalurisubbu

18-Oct-07 2:06

hi,
thanx for ur reply..
but i want count of (number only) records returing by a particular stored procedure..not table records count..

bye

Re: how to capture count of records from stored procedrure

pmarfleet18-Oct-07 2:05

pmarfleet

18-Oct-07 2:05

@@ROWCOUNT will return the no. of rows affected by the last operation in the current session.

Paul Marfleet

Re: how to capture count of records from stored procedrure

attalurisubbu18-Oct-07 2:15

attalurisubbu

18-Oct-07 2:15

hi paul, thanx for ur reply..

but in this scenario rowcount will not work.. im trying like this

DECLARE @COUNT INT

SET @COUNT = EXEC 'SP NAME'

SELECT @COUNT

can we capture only count of records returning by stored procedures like this??

Re: how to capture count of records from stored procedrure

pmarfleet18-Oct-07 2:23

pmarfleet

18-Oct-07 2:23

Works OK when I try it...

Paul Marfleet

Re: how to capture count of records from stored procedrure

neeraj_indianic19-Oct-07 21:12

neeraj_indianic

19-Oct-07 21:12

You can use @@ROWCOUNT in your stored procedure and take the value returned by @@ROWCOUNT as output parameter of stored procedure.

Automatic Add and Update

Sarfaraj Ahmed18-Oct-07 1:36

Sarfaraj Ahmed

18-Oct-07 1:36

Hello Everybody

I am new in SQL SERVER 2005 and ASP.NET 2.0 , Im doing a Online Library Management Project, I know you are expert and please help me.

On Borrower Table
MemberID, ItemID, DateTaken, Duedate, SubmitDate, Delay

On Penalty Table
MemberID, ItemID, Fine, Paid, DatePaid

(B as Borrower and P as Penalty) and a Member borrow a Item and Borrower below

B.MemberID = SCOM1001
B.ItemID=EBEG100302
B.DateTaken = 14/10/2007
B.DueDate= 16/10/2007
B.Submitdate= NULL
B.Delay=NULL

right now CurrentDate is 18/10/2007 and M.MemberID = SCOM1001 did not return ItemID = EBEG100302 .

Per day penalty is 1.500 and Member already delayed 2 days and Tomorrow it will be 3 days then 4 days then 5 days............

Just think Penalty Table is Empty, If you delay 1 day then Penalty Table will automatic add MemberID, ItemID, Fine

P.MemberID=SCOM1001
P.ItemID = EBEG100302
P.Fine=1.500
P.Paid=NO
P.PaidDate=NULL

Dealy day 2
Penalty Table will automati update P.Fine, Value will be 3.000

Delay day 3, day4....................... UpdateP.Fine

My Question is it possible to write some code inside my SQL SERVER datbase, Which will automatic check the B.DateDue column and (add or update Penaly Table where necessary)
I can write some function inside ASP.NET which will create a report for my needs but It will be taken more time.

I want my DataBase will check column status and put value to other table or same table where necessary.

Please tell me how can I do it? Any instruction will be really helpful.

Thanks
Sarfaraj Ahmed

Sarfarj Ahmed

Re: Automatic Add and Update

pmarfleet18-Oct-07 2:04

pmarfleet

18-Oct-07 2:04

Didn't you ask the same question last week? I gave you some advice then.

Use an INSERT...SELECT to select the records from the Borrower table where SubmitDate is greater than DueDate and insert corresponding entries into the Penalty table. Calculate the fine based on the logic I explained in my previous reply.

Paul Marfleet

Re: Automatic Add and Update

Sarfaraj Ahmed18-Oct-07 2:26

Sarfaraj Ahmed

18-Oct-07 2:26

Thanks For Your Reply

I followed your instruction last time.

I put that query inside ASP.NET and I have to call every single time for updating information.

I want this code inside my database which will be executed automatically and put that value on the correct column.

Could Please Help me about this.

Thanks

Sarfarj Ahmed

Re: Automatic Add and Update

pmarfleet18-Oct-07 2:42

pmarfleet

18-Oct-07 2:42

Post what you have written so far. I'm not going to write your code for you, but I'll look at what you've currently done try and help you improve it.

Paul Marfleet

problem in javascript

Arihant rathi18-Oct-07 0:49

Arihant rathi

18-Oct-07 0:49

hi i m ajay
can any one of u tell me that for javascript problem where i should write my problem...
actually i want to make a fuction that can compare two dates....the date are in mm/dd/yyyy formate..

i want a alert that comes in that condition when second date will less thn first...
and i want this onclick of button

thxxxx

Ajay Rathi
software engineer
NOIDA(UP),INDIA

Re: problem in javascript

pmarfleet18-Oct-07 1:57

pmarfleet

18-Oct-07 1:57

Your problem is that you posted your question in the wrong forum.

Paul Marfleet

query

venky45617-Oct-07 23:51

venky456

17-Oct-07 23:51

hi how to display files of week in a month based on date.plz send query for displaying the files for a week in a month using sqlserver 2000
send query asap.
regards

venkat

Re: query

Imran Khan Pathan18-Oct-07 0:19

Imran Khan Pathan

18-Oct-07 0:19

venky456 wrote:
files of week in a month based on dat

Please be clear your query first

best regard
pathan

please don't forget to vote on the post that helped you.

Data Encryption and Decryption in sql server/2005

nhss17-Oct-07 21:51

nhss

17-Oct-07 21:51

Hi Guys,

I need your help.

How to use Encryption and Decryption Logic in Sql server or sql 2005.

Function in SQL

Please let me know suggestions

Subash

Importing Data From Excel

Syed Mujtaba Hassan17-Oct-07 18:45

Syed Mujtaba Hassan

17-Oct-07 18:45

Hi All,

Can somebody tell me the easiest way to import data from and Excel sheet to a table in Sql Server 2005 database.

An immediate response would be appreciable.

Thanx in advance.

Mujtaba

"If both of us are having one apple each and we exchange it, at the end we both will have one apple each. BUT if both of us are having one idea each and we exchange it, at the end both of us will be having two ideas each."

Re: Importing Data From Excel

Sathesh Sakthivel17-Oct-07 20:01

Sathesh Sakthivel

17-Oct-07 20:01

Get it

SSK.

Anyone who says sunshine brings happiness has never danced in the rain.

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