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.
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
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)
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?
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
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.
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?
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....
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
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
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
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 )
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
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.
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
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
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
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?
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?
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#.
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.
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.
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.
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.
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.
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.
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
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!
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.
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.
