HELP!!! ASAP!! Procedures And Triggers
Oct 16, 2005
I need to create a procedure to output mean and deviation AND then I also need to create a trigger. Below is the table 'Account' I made.. but the procedure and trigger I made below that is not correct. Can someone revise this for me? I just can't figure it out.
Here is the 'Account' table I made earlier:
create table Account(Account_Number varchar(6)NOT NULL PRIMARY KEY, Branch_Name varchar(12)NOT NULL REFERENCES Branch (Branch_Name), Balance money)
insert into Account values(10101, 'First Bank', 5500)
insert into Account values(20202, 'US Bank', 3550)
insert into Account values(30303, 'Commerce', 7550)
-------------------------------------------------------------------------
Now I need to output the Mean and Standard Deviation of the above Account. So I tried this code but I'm not sure if its right since when I Analyze it, some values come out as 'null'.
CREATE PROCEDURE project2question2 AS
DECLARE @Mean MONEY
DECLARE @Deviation MONEY
SET @Mean = (SELECT avg(cast(Balance as float)) as mean from Account)
SET @Deviation = (SELECT Balance, STDEV(Balance) st_deviation
FROM Account
GROUP BY Balance)
-------------------------------------------------------------------------
The second question was to create a Trigger that would output the name "Account" and the time of change/update to it. So I had this so far, but I KNOW its not right, so can anyone revise this for me? I basically need to create a trigger named ActionHistory that would have two columns: TableName and ActionTime. They should tie to my Account table so that when it was changed/updated then the name 'Account' would appear under TableName in my trigger, and the datetime of update would appear under ActionTime. But I just can't figure this one out.
Create table ActionHistory(TableName varchar(12) NOT NULL, ActionTime datetime NOT NULL)
CREATE TRIGGER trigger_ex2
ON ActionHistory
AFTER INSERT, UPDATE
AS
BEGIN
UPDATE ActionHistory
SET Account = UPPER(LName) WHERE TableName in (SELECT TableName FROM ActionHistory)
SET Loan = UPPER(LName) WHERE IDN in (SELECT TableName FROM ActionHistory)
-------------------------------------------------------
If someone could give me these codes I would be very grateful. I know you guys are the SQL wizzards. Thanks. (by the way, I use SQL 2000 edition)
- SOnia
View 1 Replies
ADVERTISEMENT
Apr 16, 2008
Hello all, I am working on a tight deadline with a massive project. This project uses stored procedures for the database work. All help is insanely appreciated!
There are 5 different SP's for writing to the database, all separate sections.
The problem is when I try to write with 5 sets of Insert parameters in my SQL data source it throws back "Procedure or function has too many arguments specified."
When I remove them all and just leave the ones that procedure will use, it works fine. Examples below. There is a stored procedure for all number sets such as (SP1 - @Num1, @MaxBS1, @MinBS1) (SP2 - @Num2, @MinBS2, @MaxBS2)
This works fine because its the "1's" that are being written (Num1, MinBS1, MaxBS1, Etc.):1 <InsertParameters>
2
3 <asp:SessionParameter SessionField="Jackalope" Name="Jackalope" />
4
5 <asp:ControlParameter ControlID="Textbox1" PropertyName="Text" Name="Num1" />
6
7 <asp:ControlParameter ControlID="MaskedTextbox1" PropertyName="Text" Name="MinBS1" />
8
9 <asp:ControlParameter ControlID="MaskedTextbox2" PropertyName="Text" Name="MaxBS1" />
10
11 <asp:ControlParameter ControlID="RadioButtonList1" PropertyName="SelectedValue" Name="Bonus1" />
12
13 <asp:ControlParameter ControlID="MaskedTextbox151" PropertyName="Text" Name="MinBonus1" />
14
15 <asp:ControlParameter ControlID="MaskedTextbox152" PropertyName="Text" Name="MaxBonus1" />
16
17 <asp:SessionParameter SessionField="UserId" Name="UserId" />
18
19 </InsertParameters>
20
This on the other hand will not work.. im guessing even though the stored procedure only contains the parameters it needs, it still try's to force them all in?
1 <InsertParameters>2 <asp:SessionParameter SessionField="Jackalope" Name="Jackalope" />3 <asp:ControlParameter ControlID="Textbox1" PropertyName="Text" Name="Num1" />4 <asp:ControlParameter ControlID="Textbox2" PropertyName="Text" Name="Num2" />5 <asp:ControlParameter ControlID="Textbox3" PropertyName="Text" Name="Num3" />6 <asp:ControlParameter ControlID="Textbox4" PropertyName="Text" Name="Num4" />7 <asp:ControlParameter ControlID="Textbox5" PropertyName="Text" Name="Num5" />8 <asp:ControlParameter ControlID="MaskedTextbox1" PropertyName="Text" Name="MinBS1" />9 <asp:ControlParameter ControlID="MaskedTextbox3" PropertyName="Text" Name="MinBS2" />10 <asp:ControlParameter ControlID="MaskedTextbox5" PropertyName="Text" Name="MinBS3" />11 <asp:ControlParameter ControlID="MaskedTextbox7" PropertyName="Text" Name="MinBS4" />12 <asp:ControlParameter ControlID="MaskedTextbox9" PropertyName="Text" Name="MinBS5" />13 <asp:ControlParameter ControlID="MaskedTextbox2" PropertyName="Text" Name="MaxBS1" />14 <asp:ControlParameter ControlID="MaskedTextbox4" PropertyName="Text" Name="MaxBS2" />15 <asp:ControlParameter ControlID="MaskedTextbox6" PropertyName="Text" Name="MaxBS3" />16 <asp:ControlParameter ControlID="MaskedTextbox8" PropertyName="Text" Name="MaxBS4" />17 <asp:ControlParameter ControlID="MaskedTextbox10" PropertyName="Text" Name="MaxBS5" />18 <asp:ControlParameter ControlID="RadioButtonList1" PropertyName="SelectedValue" Name="Bonus1" />19 <asp:ControlParameter ControlID="RadioButtonList2" PropertyName="SelectedValue" Name="Bonus2" />20 <asp:ControlParameter ControlID="RadioButtonList3" PropertyName="SelectedValue" Name="Bonus3" />21 <asp:ControlParameter ControlID="RadioButtonList4" PropertyName="SelectedValue" Name="Bonus4" />22 <asp:ControlParameter ControlID="RadioButtonList5" PropertyName="SelectedValue" Name="Bonus5" />23 <asp:ControlParameter ControlID="MaskedTextbox151" PropertyName="Text" Name="MinBonus1" />24 <asp:ControlParameter ControlID="MaskedTextbox153" PropertyName="Text" Name="MinBonus2" />25 <asp:ControlParameter ControlID="MaskedTextbox155" PropertyName="Text" Name="MinBonus3" />26 <asp:ControlParameter ControlID="MaskedTextbox157" PropertyName="Text" Name="MinBonus4" />27 <asp:ControlParameter ControlID="MaskedTextbox159" PropertyName="Text" Name="MinBonus5" />28 <asp:ControlParameter ControlID="MaskedTextbox152" PropertyName="Text" Name="MaxBonus1" />29 <asp:ControlParameter ControlID="MaskedTextbox154" PropertyName="Text" Name="MaxBonus2" />30 <asp:ControlParameter ControlID="MaskedTextbox156" PropertyName="Text" Name="MaxBonus3" />31 <asp:ControlParameter ControlID="MaskedTextbox158" PropertyName="Text" Name="MaxBonus4" />32 <asp:ControlParameter ControlID="MaskedTextbox160" PropertyName="Text" Name="MaxBonus5" />33 <asp:SessionParameter SessionField="UserId" Name="UserId" />34 </InsertParameters>
Which is being called by:
1 If Val(TextBox1.Text) >= 1 Then2 Session("Jackalope") = "Environmental Engineer"3 SqlDataSource1.InsertCommand = "ScreenD1"4 SqlDataSource1.InsertCommandType = SqlDataSourceCommandType.StoredProcedure5 SqlDataSource1.Insert()6 End If
Is there a way to keep all of my controls parameters centralized in one SQLDataSource and still call my Stored Procedures?
View 1 Replies
View Related
Oct 23, 2003
Hi guys,
I am really new to the Database triggers and procedures
and I just need you to recommend some links or books
that will explain them to me
coz I'm gonna have to use them soon.
Any help will be appreciated.
Thank you in advance.
View 2 Replies
View Related
Nov 29, 2006
is there a trigger that will be activated whena procedure is carried out?
View 13 Replies
View Related
Jul 21, 2007
want to check, triggers are executed After the command is done right? not b4 isit? so if i want to check if the entry is to be Inserted, i should use SP instead? either that or i can use ROLLBACK right? how do i use ROLLBACK? finding Google also at the moment
View 6 Replies
View Related
Dec 5, 2001
Does anyone know of or have a method for tracking stored procedures that get called by a trigger. I have inherited a project that when a user updates a row in a particular table, triggers get fired and result in a nesting error. Thanks for your help in advance.
View 1 Replies
View Related
Dec 14, 2000
Hello,
Can I have a trigger call a stroed procedure and then in turn have the stored procedure call a COM object? If so how? Or where can I get the info to do it?
Thanks
Tony
tfeuz@sorcerysolutions.com
View 1 Replies
View Related
Mar 13, 2003
IS trigger also precompiled and stored in memory on server like SP?
Is there any advantage of converting a trigger into SP?
Thanks in advance
jfkuser
View 1 Replies
View Related
Jun 19, 2006
I have to write a script that would make prefixes for a word
for ex: Ragini Venkataraman ( is the string) is the record from one table and i need to store prefix "rag" and "ven" as two records in another table....
I need to know if this can be done using just SQL stored procedures or triggers...or if there needs to be some code(java code..we are using language java) written..
any help on how it can be done would be great....iam completely new to sql and it all seems confusing....
thanks
View 4 Replies
View Related
Nov 16, 2001
Hi folks,
Is there is any way to run a executable from triggers or stored procedures..
Please let me know, whether it is possible in SQl Server 7.0.
Let me know asap...
rgds,
VJ
View 1 Replies
View Related
Sep 30, 1998
I would like to know if the the DLL`s one can build with VB5/6 can be used to construct Extended Stored Procedures in MS SQLServer 6.5? If so, how does one do this. All the reference material I`ve come across is for C
Thanks in Advance - Ralph
View 1 Replies
View Related
Sep 30, 1998
I would like to know if the the DLL`s one can build with VB5/6 can be used to construct Extended Stored Procedures in MS SQLServer 6.5? If so, how does one do this. All the reference material I`ve come across is for C
Thanks in Advance - Ralph
View 1 Replies
View Related
Mar 8, 2004
Hi,
Is it possible to disable a trigger from a Stored Procedure? If it is, how do you do it?
Thanks,
Federico
View 4 Replies
View Related
Jan 7, 2008
For every trigger and stored procedure I have a try-catch that writes to an error_log table.
The problem is the inner error is not preserved, always get:
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
As seen below - though commented out:
I tried commiting any transactions - though I didn't create one.
I played with the
XACT_STATE though that was 0
My test case was last procedure has 1/0
Thanks,
Russ
-----------------------------------------------------------
Below is what I have
Step 1)
ALTER Trigger [trg_ActivityLogEntryReportsError] ON [dbo].[ActivityLog]
FOR INSERT AS
DECLARE @ActivityLogID int
,@AlertMessageTypeID int
,@comment nvarchar(max)
,@Error_Source nvarchar(max)
--- etc.
SELECT
@ActivityLogID = ActivityLogID
,@AlertMessageTypeID = AlertMessageTypeID
,@Comment = Comment
FROM INSERTED
BEGIN TRY
if @AlertMessageTypeID = 2 -- activity reported an error
begin
exec proc_CreateAlertLogEntry_forError
@ActivityLogID
,@Comment
update ActivityLog
set flgActivityChecked = 1
where @activityLogId = activityLogID
end
END TRY
BEGIN CATCH
select
@Error_Source = 'trg_ActivityLogEntryReportsError '
,@Error_Procedure = ERROR_Procedure()
--- etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
,Error_Message
--- etc.
)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
---etc.
,@Error_Comment )
-- if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH
Step 2)
/*
This will be called by a Trigger
*/
ALTER Procedure [dbo].[proc_CreateAlertLogEntry_forError]
(@ActivityLogID int
,@Comment nvarchar(max))
AS
Declare
@ProcessScheduleID int
,@ProcessID int
--,@comment nvarchar(max)
,@Error_Source nvarchar(max)
---etc
BEGIN TRY
insert into AlertLog
(
AlertMessageTypeID
,comment
,ActivityLogID
)
values
(
2 -- error
,@comment
,@ActivityLogID
)
end
END TRY
BEGIN CATCH
PRINT 'ERROR OCCURED'
PRINT ERROR_Procedure() + ' ' + ERROR_MESSAGE()
select
@Error_Source = 'proc_CreateAlertLogEntry_forError '
---etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
,Error_Message
---etc.
)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
--- etc.)
-- if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH
update ActivityLog
set
flgActivityChecked = 1
,UpdatedDate = getdate()
,UpdatedBy = suser_sname()
where
ActivityLogID = @ActivityLogID
STEP 3
ALTER Trigger [trg_AlertLogEntry_SendsOnInsert] ON [dbo].[AlertLog]
FOR INSERT AS
declare
@AlertLogID Int
,@AlertMessageTypeID int
,@Comment nvarchar(max)
,@Error_Source nvarchar(max)
,@Error_Procedure nvarchar(max)
,@Error_Message nvarchar(max)
--- etc.
SELECT
@AlertLogID = AlertLogID
,@AlertMessageTypeID = AlertMessageTypeID
,@Comment = isnull(Comment,'')
,@ActivityLogID = isnull(ActivityLogID,-1)
FROM INSERTED
BEGIN TRY
PRINT 'trg_AlertLogEntry_SendsOnInsert'
PRINT @COMMENT
exec proc_SendEmail
@AlertLogID
,@AlertMessageTypeID
,@comment
,@ActivityLogID
END TRY
BEGIN CATCH
select
@Error_Source = 'trg_AlertLogEntry_SendsOnInsert '
,@Error_Procedure = ERROR_Procedure()
,@Error_Message = ERROR_MESSAGE()
--- etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
-- etc.)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
---etc.)
-- if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH
STEP 4
ALTER Procedure [dbo].[proc_SendEmail]
(
@AlertLogID Int
,@AlertMessageTypeID int
,@Comment nvarchar(max) = ''
,@ActivityLogID int = -1
)
AS
declare @AlertSubject nvarchar(512)
,@AlertBody nvarchar(max)
,@myQuery nvarchar(512)
,@profile_name1 nvarchar(128)
,@return_value int
,@mymailitem int
,@Error_Source nvarchar(max)
---etc.
,@Error_Comment nvarchar(max)
,@Test int
/*
@return_value int -- not using at this point but 0 is OK 1 is failure
@mymailitem int -- not using now could store mailitem_id which is on msdb.dbo.sysmail_mailitems
sysmail_mailitems.sent_status could be either 0 new, not sent, 1 sent, 2 failure or 3 retry.
*/
select top 1 @profile_name1 = [name] from msdb.dbo.sysmail_profile
order by profile_id
set @profile_name1 = rtrim(ltrim(@profile_name1))
print '@profile_name1: ' + @profile_name1
print '@comment: ' + @comment
Declare @CrsrRecipient Cursor
BEGIN TRY
PRINT 'proc_SendEmail'
--set @test = 1/0 'test crashing
select
@AlertSubject = 'AlertSubject'
,@AlertBody = 'AlertBody'
,@recipients = 'russ@test.com'
print 'sending email ' + CAST(getdate() as nvarchar(100))
EXEC @return_value = msdb.dbo.sp_send_dbmail
@profile_name = @profile_name1
,@recipients = @EMAILID
,@body = @AlertBody
,@subject = @AlertSubject
,@mailitem_id = @mymailitem OUTPUT
print 'Done ' + CAST(getdate() as nvarchar(100))
print cast(@return_value as nvarchar(100))
update alertlog
set AlertSendStatusID = 1 --sent
where
@AlertLogID = AlertLogID
END TRY
BEGIN CATCH
PRINT 'ERROR OCCURED'
PRINT ERROR_Procedure() + ' ' + ERROR_MESSAGE()
select
@Error_Source = ' proc_SendEmail '
,@Error_Procedure = ERROR_Procedure()
--- etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
---etc.)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
--- etc.)
update alertlog
set AlertSendStatusID = 2 --error
where
@AlertLogID = AlertLogID
--if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH
View 5 Replies
View Related
May 9, 2006
Hi,
I would like to use the view of the company data in the following stored procedures
without needing to pass a value into the CompanyID identity column. Is there a way to do this? The following code contains the view, the insert trigger on the view, and the desired stored procedures.
Also, this code is given me problem as well the error message says it cannot convert varchar to int. This code is located in the insert trigger.
INSERT INTO State(StateName)
VALUES(@StateName)
Here is the code of the company view, notice the C.CompanyID it is used in the GetCompany stored procedure.
SELECT C.CompanyID, C.CompanyName, A.Street, A.City, S.StateName, A.ZipCode, A.Country, P.PhoneNumber
FROM Company AS C INNER JOIN
Address AS A ON C.AddressID = A.AddressID INNER JOIN
State AS S ON A.StateID = S.StateID INNER JOIN
Phone AS P ON C.PhoneID = P.PhoneID
Here is code for the insert trigger on the view
CREATE TRIGGER InsertTriggerOnCustomerView
ON ViewOfCompany
INSTEAD OF INSERT
AS
BEGIN
DECLARE @CompanyName VARCHAR(128)
DECLARE @AddressID INT
DECLARE @Street VARCHAR(256)
DECLARE @City VARCHAR(128)
DECLARE @StateID INT
DECLARE @StateName VARCHAR(128)
DECLARE @ZipCode INT
DECLARE @Country VARCHAR(128)
DECLARE @PhoneID INT
DECLARE @PhoneNumber VARCHAR(32)
--DECLARE @CompanyID INT
SELECT
--@CompanyID = CompanyID,
@CompanyName = CompanyName,
@Street = Street,
@City = City,
@StateName = StateName,
@ZipCode = ZipCode,
@Country = Country,
@PhoneNumber = PhoneNumber
FROM INSERTED
INSERT INTO State(StateName)
VALUES(@StateName)
SET @StateID = @@IDENTITY
INSERT INTO Address(Street, City, StateID, Country, ZipCode)
VALUES(@Street, @City, @StateID, @ZipCode, @Country)
SET @AddressID = SCOPE_IDENTITY()
INSERT INTO Phone(PhoneNumber)
VALUES(@PhoneNumber)
SET @PhoneID = SCOPE_IDENTITY()
INSERT INTO Company(CompanyName, AddressID, PhoneID)
VALUES(@CompanyName, @AddressID, @PhoneID)
END
The stored procedures are here.
CREATE PROCEDURE GetCompany
(
@CompanyID INT
)
AS
BEGIN
SELECT CompanyName,Street, City, StateName,
ZipCode, Country, PhoneNumber
FROM
ViewOfCompany
WHERE
CompanyID = @CompanyID
END
GO
CREATE PROCEDURE AddCompany
(
@CompanyName VARCHAR(128),
@Street VARCHAR(256),
@City VARCHAR(128),
@StateName VARCHAR(128),
@ZipCode VARCHAR(128),
@Country VARCHAR(128),
@PhoneNumber VARCHAR(32)
)
AS
BEGIN
INSERT INTO ViewOfCompany
VALUES(@CompanyName, @Street, @City, @StateName,
@ZipCode, @Country, @PhoneNumber)
END
GO
View 3 Replies
View Related
Aug 15, 2006
For those intersted here is our TOC and the book's link. You can preorder at this point. We are sticking to the Nov. timeframe, but we may get it done sooner.
Chapter 1 Introducing SQLCLR
Chapter 2 Building a Procedure
Chapter 3 SQLCLR Strucutre & Common Tasks
Chapter 4 Creating Objects
Chapter 5 Compare & Contrast
Chapter 6 Replacing TSQL Objects
Chapter 7 Using the Base Library
Chapter 8 Using Procedures in Apps
Chapter 9 Error Handling
Chapter 10 Administration
Chapter 11 Case Study
Here is the link:
http://www.wrox.com/WileyCDA/WroxTitle/productCd-0470054034.html
Enjoy,
Derek
View 2 Replies
View Related
Jul 20, 2005
How can i delete all user stored procedures and all table triggers very fastina single database?Thank you
View 17 Replies
View Related
Jun 30, 2004
Hi Folks,
I'm having a bit of trouble updating my script. I have a
script used for MS Access and I need to transfer it to be
compatible with MS SQL Server. When I run the script it
gives the error
''ODBC driver does not support the requested
properties.''
This is the line of the script
that I have to change to work with sql:
''oRS.Open SqlTxt , oConn'' and the sql text is:
''Sqltxt = "SELECT MasterServer, Max(JobId) AS MaxJobId
FROM Jobs GROUP BY MasterServer ORDER BY MasterServer;".
Could anybody please help me out in solving this problem?
Cheers!
View 1 Replies
View Related
Apr 4, 2001
Hello All,
I've created access project using upsizing wizard. Project is created ok on slq server 7.0(ie tables, stored proc, views etc.) but when i open tables or forms in A2k it doesn't allow me to enter or modify data. Though all permissions are Ok.
Could anyone please let me know what could be wrong?
Thanks in advance!!
View 1 Replies
View Related
Aug 1, 2007
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I am using SQL server Express 2005 on my local machine. And ASP.net 2.0 with C#.
View 13 Replies
View Related
Jul 21, 2007
Hello Fellow Members,
Background:
Hopefully someone out there can help me. Here is the situation. I have a documents table which stores "documents". A document can either be a folder or a actually document(word, excel ...). This is determined by a field in the table called 'Type' which is either a '0' or '1'The table stores the following data for each 'document'--------------------------------------------------------------------------------DocID - intDocument - Actual document in bytesLeafName - Actual document nameType - Determines type of document (0 - document, 1- folder)DirName - Which folder the document resides in (can be null)
Problem:I want to create a query which navigates through the entire table and deletes all documents. For example, if a user has a folder called (Temp) and that folder has 30 documents and another folder called (Temp2) that also has documents. Now the user wants to delete (Temp). So what I want the query to do is go through the database and delete then entire folder (Temp) and all of its contents associated with it.
Question:How can I traverse through the table to delete the documents?All of your help would be greatfull.
View 7 Replies
View Related
May 21, 2001
Hi,
How do I find out on which path is the SQl server lisytening to on named pipes when I look at the registry?
Thanks,
Ganesh
View 1 Replies
View Related
Mar 31, 2000
Hi all,
Does anyone knows a trick to change the licensing of a SQL server 7 from a Per server to a Per seat Licensing without re-installing SQL.
Thanks,
Joanna
View 2 Replies
View Related
Mar 30, 2007
SQL Server 2000
Ran sp_removedbreplication @dbname = 'FooDatabase'
Did NOT want to do that!
Have the replication scripted out
but getting errors when trying to run the script to recreate.
All Push
I tried
sp_droppublication 'Foopubname'
sp_MSload_replication_status
Error 21776: [SQL-DMO] the 'publication name' was not found in the
TransPublications collection. I cannot create a new publication of the same
name.
View 1 Replies
View Related
Nov 27, 2007
Hello Everyone. Im sorry for this urgent post, but have critical issue that needs a solution quick. So for my issue. I am adjusting our sales order tables to handle a couple different scenarios. Currently we have 2 tables for sales orders
SALESORDERS
------------
SORDERNBR int PK,
{ Addtl Header Columns... }
SALESORDERDETAILS
-------------------
SODETAILID int,
SORDERNBR int FK,
PN varchar,
SN varchar(25),
{ Addtl Detail Columns ... }
Currently the sales order line item is serial number specific. I need to change the tables to be able to handle different requests like :
Line Item Request ( PN, QTY )
Line Item Request ( SN )
Line Item Request ( PN, GRADE, QTY )
ETC.
I am thinking i need to create a new table to hold the specifics for a particular line item. Maybe like this :
SALESORDERSPECS
----------------
SOSPECID int,
SODETAILID int FK,
SPECTYPE varchar, IE : SN, PN, GRADE. { one value per row }
SPECVALUE varchar IE : GRADE A
Im thinking i would need to rename the SALESORDERDETAILS table to SALESORDERITEMS. SALESORDERITEMS would just contain header info like
SalePrice,
Warranty,
Etc...
Then rename SALESORDERSPECS to SALESORDERDETAILS...
Anyone understand what im trying to do? If you need more info please ask. You can also get a hold of me through IM.
Thanks!
JayDial
JP Boodhoo is my idol.
View 3 Replies
View Related
Apr 11, 2007
My computer install SQL server express, when i try to develop my project, I found out there are some tools missing from this edition. So i try to upgrade to enterprise edition. I setup the enterprise edition, it saying i already have exist copy of SQL server. It does not allow to upgrade. So i try to uninstall the SQL server express and install the enterprise edition. After i install the enterprise edition, it missing all the server service. That's mean the program installed, but the service part is missing. Is there any way to get back those service?. Please help.
View 1 Replies
View Related
Jun 25, 2007
I am having problem which is getting worst by the moment. I had a power failure which my battery backup fail while running a large report in my SSRS all of a sudden I started getting reportservertempdb.dbo.persistedstream error. I could not get my reports to run through the iis webservice. I could get them to run from within my reportbuilder. I was told to reload my sql and restore it but I can not get my sql to successfully reinstall I am using the sql2005 dev ed. It either gives me a name instance issue or fails the database, report server and notifaction portion of the reload. I am not sure why it will not reload. any assistance would be great.
View 1 Replies
View Related
Nov 8, 2007
hello, i have a problem in my sql. im using a stored procedure and i want to get the newly created id number to be used to insert in the other table.it works like:insert into table() values()select @id = (newly created id number) from tableinsert into table1() values(@id) something like that.
View 2 Replies
View Related
Oct 19, 2001
Hello,
I want to write a query that returns the following
sum(cola) sum(colb) pct_change = A/B
How do I write the query such that the %_change handles the case
where B is zero?
Thanks
zack
View 1 Replies
View Related
Oct 12, 2004
Hello,
I'm new to SQL. I wrote this Stored Procedure but it does not work. When I'm executing it I get these errors:
Server: Msg 170, Level 15, State 1, Procedure Test, Line 12
Line 12: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Procedure Test, Line 15
Line 15: Incorrect syntax near ')'.
Server: Msg 170, Level 15, State 1, Procedure Test, Line 19
Line 19: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Procedure Test, Line 24
Line 24: Incorrect syntax near '='
Can anyone please help me with that?
Thank you in advance.
CREATE PROCEDURE Test As
BEGIN TRANSACTION
Select PVDM_DOCS_1_5.DOCINDEX1, TableTest.DOCINDEX2, TableTest.DOCINDEX3, TableTest.DOCINDEX4
From PVDM_DOCS_1_5, TableTest
WHERE TableTest.DOCINDEX1 = PVDM_DOCS_1_5.DOCINDEX1
AND (TableTest.DOCINDEX2 Is NULL or TableTest.DOCINDEX2 ='' OR TableTEst.DOCINDEX3 Is NULL or TableTest.DOCINDEX3 ='' or TableTest.DOCINDEX4 is NULL or TableTEst.DOCINDEX4 = '');
IF TableTest.DOCINDEX2 is NULL or TableTest.DOCINDEX2 = ''
UPDATE TableTest.DOCINDEX2 = DOCINDEX2
WHERE (DOCINDEX1 IN
(SELECT DOCINDEX1
FROM PVDM_DOCS_1_5))
END IF
If TableTest.DOCINDEX3 is NULL or TableTest.DOCINDEX3 = ''
UPDATE TableTest.DOCINDEX3 = PVDM_DOCS_1_5.DOCINDEX3
WHERE (DOCINDEX1 IN
(SELECT DOCINDEX1
FROM PVDM_DOCS_1_5))
END IF
If TableTest.DOCINDEX4 is NULL or TableTest.DOCINDEX4 = ''
UPDATE TableTest.DOCINDEX4 = PVDM_DOCS_1_5.DOCINDEX4
WHERE (DOCINDEX1 IN
(SELECT DOCINDEX1
FROM PVDM_DOCS_1_5))
END IF;
DELETE PVDM_DOCS_1_5 WHERE DOCINDEX1 = DOCINDEX1
IF (@@ERROR <> 0) GOTO on_error
COMMIT TRANSACTION
-- return 0 to signal success
RETURN (0)
on_error:
ROLLBACK TRANSACTION
-- return 1 to signal failure
RETURN (1)
GO
View 2 Replies
View Related
Dec 17, 2004
We seem to have a problem with permission levels and connecting to an MSDE (MSSQL) server. If the user is under the Domain Admins group, the the access projet (front end) will open correctly and connect to the data server. If they are not part of that group then the front end can ever establish a file to the database server. We do not want to make all the users Domain Admins, so is there a way to make MSDE let them trough even though they are on a lower level.
I've done many tests, and also tried many things. I've even went to the extent to give Full Control to the whole MSSQL folder in program files for Everyone. I have made sure that the database file itself inherieted it's parents security settings, which were what I had just described.
Any ideas how how to make MSDE let anyone connect? Thanks in advance!
View 10 Replies
View Related
Apr 13, 2007
My computer install SQL server express, when i try to develop my project, I found out there are some tools missing from this edition. So i try to upgrade to enterprise edition. I setup the enterprise edition, it saying i already have exist copy of SQL server. It does not allow to upgrade. So i try to uninstall the SQL server express and install the enterprise edition. After i install the enterprise edition, it missing all the server service. In Surface Area Conforuration missing all the service. That's mean the program installed, but the service part is missing. Is there any way to get back those service?. I already try to install those package, no matter how i try to install those service package, it keep saying i didn't install any of them. Please help.
View 3 Replies
View Related
Aug 11, 2000
I'm trying to import data into an SQL Server (7.0) and I'm wondering which Source (Microsoft Data Link, Microsoft ODBC Driver for Oracle, Microsoft ODBC Driver for SQL Server, etc.) -- I THINK we would use the SQL Server driver but I'm not sure... to use AND WHERE TO GO FROM THERE? So far, I get seem to get things to work in my favor. I appreciate any help :) The data I'm trying to import is from Microsoft Excell. If there is anything else you need to know, please email me at iami@iami.org Please provide email/forum-based technical support.
View 4 Replies
View Related
