|
One way to do this would be:
DECLARE @FID INT
DECLARE @LID INT
SET NOCOUNT ON
SELECT @FID = [ID] FROM FirstNames WHERE FirstName = 'Fritz'
IF @FID IS NULL
BEGIN
INSERT INTO FirstNames(FirstName) VALUES ('Fritz')
SET @FID = Scope_Identity()
END
SELECT @LID = [ID] FROM LastNames WHERE LastName = 'Blignaut'
IF @LID IS NULL
BEGIN
INSERT INTO LastNames(LastName) VALUES ('Blignaut')
SET @LID = Scope_Identity()
END
INSERT INTO Users VALUES (@FID, @LID) Are you sure that you really want to normalize down to this level? You are going to be doing an awful lot of looking up on information which is going to be wasteful in system processing terms.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi all,
I have two ASP.NET modules that have been built at different times that I want to use together. there are only 4 tables that need to be merged, but both modules use stored procedures and Crystal reports extensively - so I am looking as to whether it is possible in MSSQL 2005 to map or alias the fields in one table to another, and so avoid a rather painful refactoring task. I have listed the tables and the corresponding fields below for greater clarity - the order and orderItems tables are the one's I need to keep.
orders(table) ==> cart(table)
OrderID(int PK identity) ==> CartID(int PK identity)
EntryDate(DateTime) ==> SaleDate(DateTime)
userID(uniqueidentifier) ==> CustomerName(varchar)
PartnerID(int) ==> PartnerID(int)
TransactionID(varchar) ==> ReceiptNo(varchar)
(no corresponding field) ==> CustomerEmail(varchar)
orderItems(table) ==> CartItem(table)
ItemID(int PK identity) ==> CartItemID(int PK identity)
OrderID(int) ==> CartID(int)
ProductID(int) ==> ProductID(int)
SellPrice(money) ==> SalePrice(money)
Many thanks in advance,
Nigel
|
|
|
|
|
Would VIEWs solve your problem?
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
Thanks for the reply Colin,
Must admit a limited understanding of table views - so I am not sure.
If I used a view, wouldn't I still have to refactor all the stored procedures and reports that use the cart and cartitems tables to use the view instead??
If I have to do that I am still in the same predicament.
Or is it possible to create a table view with the exact name of the original table (deleting the tables of course) and the SP's and reports will still pick up the data?
Cheers,
Nigel
|
|
|
|
|
big_nige wrote: Or is it possible to create a table view with the exact name of the original table (deleting the tables of course) and the SP's and reports will still pick up the data?
If you are renaming the original tables and create views with the original table names then it should work. You may have to recompile the stored procedures to get it to pick up the changes.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
Many thanks Colin,
Will give the views a go and let you know how I get on.
Regards,
Nigel
|
|
|
|
|
thanks Colin - the views worked a charm!!
Still had some tidy up work as one module was built in the US and the other here in Australia - so the date formats were different, but otherwise a simple solution to what was potentially a nightmare.
Oh the joys of contract programming...
Cheers mate.
Nigel
|
|
|
|
|
Hi.
Which version(s) of Microsoft SQL Server 2005 can be installed on windows vista?
Best wishes
|
|
|
|
|
Express and Developer. All other editions (as far as I'm aware) require a server class OS.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
I believe that SP2 is recommended for running SQL 2005 on vista
|
|
|
|
|
When I give random Search of 1000 records in particular table, the systems hangs after some 30 to 40 searches. Is there a Memory Problem. I use the Process Rowset function in teh demo1 code and retreived.
|
|
|
|
|
i have problem i can't attachment for database (the file you've specified is not a valid sql server database file )
how can solve this problem
Rami Abd alhalim
|
|
|
|
|
What is the file extension?
Rami Said Abd Alhalim wrote: (the file you've specified is not a valid sql server database file )
What part of that do you not understand?
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
u must attach the .mdf file.
|
|
|
|
|
Hi
each time I try to add/Update something I receive the error below:
"The transaction log for database 'DatabaseName' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases"
any idea how to solve this
thanks alot
Shaimmaa
|
|
|
|
|
Have you tried the advice given in the error message? What was the result?
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
I have a winApplication which has a form.
In that form I have a sql data adapter which contains 4 command for select,update,delete,insert.
When I execute insert command, with passing 9 parameters values, by use of executenonguery function I receive "procedure or function "procedure name" has too many argument specified" error massage at line of executing.
What should I do? I don't know what's happend there cause I check every parameters by breakpoint and saw that every parameters were passed!
|
|
|
|
|
isiran wrote: When I execute insert command, with passing 9 parameters values, by use of executenonguery function I receive "procedure or function "procedure name" has too many argument specified" error massage at line of executing.
What should I do?
Post the code - because we can only guess at the cause of the problem.
isiran wrote: I don't know what's happend there cause I check every parameters by breakpoint and saw that every parameters were passed
Yes, but how many times? The error says "too many" which suggests that the problem isn't that the values are being passed as parameters but "too many" are being passed.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
how can i schedule a job in
SSQL 2000
job is a stored procedure..
i want to excute it in daily basis
All I ever wanted is what others have.... CrazySanker
|
|
|
|
|
this may not be the best way but a thought
write an app - which would just have this one command of stored proc execution.
Then schedule this app using windows scheduler.
|
|
|
|
|
Hey ppl, can anyone explain me this code.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER FUNCTION dbo.JoinBase(@JID INT) RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @JEX VARCHAR(200),
@Qualification VARCHAR(20)
DECLARE c1 CURSOR LOCAL FAST_FORWARD FOR
select enb from I_EM inner join Ew on I_EM.e_id=Ew.e_id and empid=@JID
ORDER BY e_name
OPEN c1
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH c1 INTO @Qual
IF (@@FETCH_STATUS = 0)
BEGIN
SET @JEX = IsNull(@JEX + ',', '') + @Qual
END
END
CLOSE c1
RETURN @JEX
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
and what does this ISNULL do????
pls help somebuddy..
|
|
|
|
|
John Mecas wrote: can anyone explain me this code
It produces a comma separated list of all the data in the enb returned by the query.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
hey thanks for response..but can u explain it in some more details..... I knew it concats the values.
|
|
|
|
|
-- Alter an existing function called JoinBase
-- It takes an integer as a parameter called @JID
-- It will return a VARCHAR with a maximum size of 200 characters.
ALTER FUNCTION dbo.JoinBase(@JID INT) RETURNS VARCHAR(200)
AS
BEGIN
-- Two local variables are declared
DECLARE @JEX VARCHAR(200),
@Qualification VARCHAR(20)
-- A cursor is declared.
-- It will return the results of the SELECT statement one row for each loop
DECLARE c1 CURSOR LOCAL FAST_FORWARD FOR
select enb from I_EM inner join Ew on I_EM.e_id=Ew.e_id and empid=@JID
ORDER BY e_name
OPEN c1
-- Loops until the cursor has exhausted all the rows from the SELECT
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- This line won't even compile because @Qual doesn't exist
-- but it is supposed to get the value of enb from the SELECT
-- statement into the local variable @Qual (@Qualification)
FETCH c1 INTO @Qual
-- If the fetch was successful
IF (@@FETCH_STATUS = 0)
BEGIN
-- Append @Qual into the existing list.
-- If the list doesn't exist create one as a blank string
-- The end result is a comma separated list mad up of each
-- @Qualification/@Qual returned from the query.
SET @JEX = IsNull(@JEX + ',', '') + @Qual
END
END
-- Close the cursor
CLOSE c1
-- Returns the result to the caller
RETURN @JEX
END
To me it looks like the cursor is badly used. (Well, using a cursor is bad enough, but there are bits that just don't quite look right). If you want more information on cursors then I expect you can look it up in the documentation - Just search MSDN for it. I don't use cursors if I can help it. They are, occasionally, a necessary evil, but for the most part the should be avoided like the plague.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
Maybe this SQL can do the same thing:
select @JEX = isnull(@JEX + ',', '') + enb from I_EM inner join Ew on I_EM.e_id=Ew.e_id and empid=@JID
ORDER BY e_name
If not necessary,do not use CURSOR;P
|
|
|
|