Re: database creation - Database Discussion Boards

benjamin yap13-Jun-09 7:16

13-Jun-09 7:16

Re: 2 tables?

Blue_Boy13-Jun-09 7:28

Blue_Boy

13-Jun-09 7:28

select accounts.*,
(select count(event_joined.eventid) from event_joined where event_joined.admin_num=accounts.admin_num) as [Number of event joined]
from accounts

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.

Re: 2 tables?

benjamin yap13-Jun-09 7:36

benjamin yap

13-Jun-09 7:36

THANKS!!

Re: 2 tables?

Blue_Boy13-Jun-09 7:39

Blue_Boy

13-Jun-09 7:39

No problem.

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 creation

malika mouzmine13-Jun-09 5:48

malika mouzmine

13-Jun-09 5:48

can you send me how to create a database to create an online trading and shopping site

Re: database creation

Henry Minute13-Jun-09 6:07

Henry Minute

13-Jun-09 6:07

Without knowing what you wish to trade, it is almost impossible to advise you, with any detail.

It would also take too much time and space. However, if you go to the top of the page and enter shopping cart into the search box and click go!, you will find lots of articles that will get you started.

When you have had a look and tried a few, I'm sure that you will find help for any specific problems that you might have.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Re: database creation

Eddy Vluggen13-Jun-09 7:58

Eddy Vluggen

13-Jun-09 7:58

Starter kits can be found here[^]. Check out the "BeerHouse" example Smile | :)

I are troll Smile | :)

Re: database creation

Jerry Hammond14-Jun-09 4:12

Jerry Hammond

14-Jun-09 4:12

Use master;
go
Create Database [Insert name here];
go

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

Which SQL server to install on Novel network server

supportks13-Jun-09 1:51

supportks

13-Jun-09 1:51

Kindly suggest SQL version to be loaded on Novel Network server

I have tried SQL Express edition but it;s not working

Re: Which SQL server to install on Novel network server

Henry Minute13-Jun-09 6:12

Henry Minute

13-Jun-09 6:12

You need to be more specific!

What do you mean it's not working?

It won't install?
It installs, but you cannot connect?
What?

You might find some useful information here[^].

how to get data from Server A to Server B

Satish_S12-Jun-09 18:30

Satish_S

12-Jun-09 18:30

hi
I have to get data's from sqlserver 2000 Db to my Sql server 2005 Db, how to do this? is there any way other than linked server..

Re: how to get data from Server A to Server B

Mycroft Holmes13-Jun-09 2:33

Mycroft Holmes

13-Jun-09 2:33

BCP, SSIS/DTS or use data compare from red-gate.

Never underestimate the power of human stupidity
RAH

problem in sql server connection

pampam11012-Jun-09 6:40

pampam110

12-Jun-09 6:40

I installed SQL Server 2005 on my computer. I am using Vista Ultimate. After installation, I can't login to SQL Server.
It gives the error:

Login Failed for User'DOMAINNAME\ACCOUNTNAME'. (Microsoft SQL Server Error: 18456)

I also have SQL Server Express installed on my computer and I can login to that without any problem.

Any ideas?

[]D @ []v[] []D @ []v[]

Re: problem in sql server connection

Jerry Hammond12-Jun-09 10:13

Jerry Hammond

12-Jun-09 10:13

Are you sure you're using the correct account to login with?

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

Re: problem in sql server connection

pampam11015-Jun-09 22:24

pampam110

15-Jun-09 22:24

Yeah , I'm Sure.

[]D @ []v[] []D @ []v[]

Stored Procedure with complex(ish) IF.....ELSE

munklefish12-Jun-09 4:02

munklefish

12-Jun-09 4:02

Hi,

Thanks to a massively helpful ROb ive manged to learn a little about conditional statements in Stored Procedures. However i have now created the following SP and it gives an error when compiling.

Incorrect syntax near the keyword 'ELSE'

CREATE PROCEDURE web.createDefaultAddressBook
       (
       @tmp_custID bigint,
       @tmp_Description varchar(500),
       @tmp_NoRecipients bigint,
       @tmp_Email varhar(100),
       @tmp_Forename varchar(100),
       @tmp_Surname varchar(100)
       )
AS
BEGIN
       declare @key bigint
       declare @newSignUpID bigint
       --determine id of record if exists
       SELECT @key = addMaster_Key FROM tbl_AddressBookMaster WHERE addMaster_UserId = @tmp_custID AND addMaster_Title = 'Default'

       IF @key IS null
       --address book doesnt exist just so create default
       
       INSERT INTO tbl_AddressBookMaster ([addMaster_UserId], [addMaster_Title], [addMaster_Description], [addMAster_NoRecipients])
       VALUES (@tmp_custID, 'Default', @tmp_Description, 0)
       SELECT @newSignUpID = SCOPE_IDENTITY()
       
      --add addresses to second table
       INSERT INTO tbl_AddressBookAddresses ([adds_ABMId], [adds_Email], [adds_RecipientForename], [adds_RecipientSurname])
       VALUES (@newSignUpID, @tmp_Email, @tmp_Forename, @tmp_Surname)

       ELSE
       --default already exists
       
       --add addresses to second table
       INSERT INTO tbl_AddressBookAddresses ([adds_ABMId], [adds_Email], [adds_RecipientForename], [adds_RecipientSurname])
       VALUES (@key, @tmp_Email, @tmp_Forename, @tmp_Surname)
END

What am i doing wrong?

Is there a more efficient way to do this considering i will be using it in code which will loop through a huge array and pass new records into the SP?

Effective but simple is probably best at this stage though.

Thanks in advance.

Re: Stored Procedure with complex(ish) IF.....ELSE

Blue_Boy12-Jun-09 4:12

Blue_Boy

12-Jun-09 4:12

munklefish wrote:
SELECT @key = addMaster_Key FROM tbl_AddressBookMaster WHERE addMaster_UserId = @tmp_custID AND addMaster_Title = 'Default' IF @key IS null

I guess column addMaster_Key return integer value and your query should be:
set @key = (select addMaster_Key FROM tbl_AddressBookMaster WHERE addMaster_UserId = @tmp_custID AND addMaster_Title = 'Default')

IF @key is null then
begin

end

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.

Re: Stored Procedure with complex(ish) IF.....ELSE

munklefish12-Jun-09 4:18

munklefish

12-Jun-09 4:18

Thats how it was suggested to me by someone else on here. That part of it works.

Is it more efficient to use SET rather than the SELECT method i have used?

Thanks.

Re: Stored Procedure with complex(ish) IF.....ELSE

Rob Philpott12-Jun-09 4:22

Rob Philpott

12-Jun-09 4:22

People argue about that a lot. There's really not much in it. I tend to use SET outside of a select statement (so would use it in your example).

set @now = getdate()

But either is fine.

Regards,
Rob Philpott.

Re: Stored Procedure with complex(ish) IF.....ELSE

munklefish12-Jun-09 4:16

munklefish

12-Jun-09 4:16

SO i figured out that i need to place the 'BEGIN' & 'END' blocks within the IF statement.

Any suggestions on optimising the procedure?

Re: Stored Procedure with complex(ish) IF.....ELSE

Rob Philpott12-Jun-09 4:20

Rob Philpott

12-Jun-09 4:20

Yeah, as posted above if you have more than one statement in your conditional code you need to place them in a begin-end block.

As a general point, the last two inserts you have there are almost identical, the first parameter being the only difference.

I think you could shorten it to this (or similiar):

IF @key IS null
begin
    INSERT INTO tbl_AddressBookMaster ([addMaster_UserId], [addMaster_Title], [addMaster_Description], [addMAster_NoRecipients])
    VALUES (@tmp_custID, 'Default', @tmp_Description, 0)
    SELECT @key = SCOPE_IDENTITY()
end

INSERT INTO tbl_AddressBookAddresses ([adds_ABMId], [adds_Email], [adds_RecipientForename], [adds_RecipientSurname])
VALUES (@key, @tmp_Email, @tmp_Forename, @tmp_Surname)

Make sense?

Regards,
Rob Philpott.

Re: Stored Procedure with complex(ish) IF.....ELSE

munklefish12-Jun-09 4:35

munklefish

12-Jun-09 4:35

THANKS ROB!!!!!!!

Its actually quite easy when you know how, isnt it. Laugh | :laugh:

Re: Stored Procedure with complex(ish) IF.....ELSE

Niladri_Biswas17-Jun-09 20:55

Niladri_Biswas

17-Jun-09 20:55

The block of code inside the IF & ELSE statement should be within BEGIN and END block.

Like this:

IF @key IS null
       --address book doesnt exist just so create default
       BEGIN
       INSERT INTO tbl_AddressBookMaster ([addMaster_UserId], [addMaster_Title], [addMaster_Description], [addMAster_NoRecipients])
       VALUES (@tmp_custID, 'Default', @tmp_Description, 0)
       SELECT @newSignUpID = SCOPE_IDENTITY()
       
      --add addresses to second table
       INSERT INTO tbl_AddressBookAddresses ([adds_ABMId], [adds_Email], [adds_RecipientForename], [adds_RecipientSurname])
       VALUES (@newSignUpID, @tmp_Email, @tmp_Forename, @tmp_Surname)

      END

       ELSE
       --default already exists
       BEGIN
       --add addresses to second table
       INSERT INTO tbl_AddressBookAddresses ([adds_ABMId], [adds_Email], [adds_RecipientForename], [adds_RecipientSurname])
       VALUES (@key, @tmp_Email, @tmp_Forename, @tmp_Surname)

        END

Niladri Biswas

Stored procedure across multiple servers?

enipla12-Jun-09 3:13

enipla

12-Jun-09 3:13

I have two servers with SQL 2005 on them. Is it possible to write a stored procedure that moves data from a table in a database on server A to a table on server B?

Or must both tables be in the same Database on the same server where the stored procedure resides?

I’ve never written a SP before. I am also looking into SP written with .NET in managed code. Perhaps that is the way to go?

Re: Stored procedure across multiple servers?

saanj12-Jun-09 3:16

saanj

12-Jun-09 3:16

You have to create a linked server first.

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

Last Visit: 31-Dec-99 18:00 Last Update: 6-May-24 10:26

Refresh

ᐊ Prev 1...831 832 833 834835836 837 838 839 840 Next ᐅ

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