Need Help ASAP... Stored Procedures And SQLDataSource
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?
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)
I was advised to use stored procedures instead of directly calling the tables in the database. I figured out how to do that in the aspx.vb file, but within the aspx page using SqlDataSource, I'm not sure how to do it, if it can be done. Basically I need to change the following so that instead of using the SQL to call the table, it calls a stored procedure: <asp:SqlDataSource ID="srcDescLoc" runat="server" ConnectionString="<%$ ConnectionStrings:Connection_DB%>" selectcommand="SELECT [MATERIAL_NUMBER], [DESCRIPTION], [ISSUE_LOC] FROM [tbl_MATERIAL] WHERE [MATERIAL_NUMBER] = @MATERIAL_NUMBER"> </asp:SqlDataSource> Is this possible, and if so, how? Thanks in advance!
Can I use a sqldatasource control with a stored procedure that has several statements within it. The stored procedure inserts records into two separate tables but i want to use the sqldatasource bound to a formview control to get the values to insert into one of the tables. Is this possible, if not how do I pass the values to the stored procedure. This will be my first time using stored procedures so I am a little confused as far as how to get the data in from my application... Any Ideas are appreciated Thanks, Laura
Hi all, I've been trying to use the stored procedures created by asp (i.e. aspnet_membership_createuser)... but they don't show up when trying to use them in a sqldatasource. Are they blocked or am I doing something wrong?Thanks in advance.
Can a SqlDataSource or a TableAdapter be attached to a stored procedure that returns a temporary table? I am using Sql Server 2000. The SqlDataSource is created with the wizard and tests okay but 2.0 controls will not bind to it. The TableAdapter wizard says 'Invalid object name' and displayes the name of the temporary table.ALTER PROCEDURE dbo.QualityControl_Audit_Item_InfractionPercentageReport @AuditTypeName varchar(50), @PlantId int = NULL, @BuildingId int = NULL, @AreaId int = NULL, @WorkCellId int = NULL, @WorkShift int = NULL, @StartDate datetime = NULL, @EndDate datetime = NULL, @debug bit = 0 AS CREATE TABLE #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable (ItemHeader varchar(100), Item varchar(250), HeaderSequence int, ItemSequence int, MajorInfractionPercent money, MinorInfractionPercent money) DECLARE @sql nvarchar(4000), @paramlist nvarchar(4000) SELECT @sql = ' INSERT INTO #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable (ItemHeader, Item, HeaderSequence, ItemSequence, MajorInfractionPercent, MinorInfractionPercent) SELECT DISTINCT QualityControl_Audit_Item.Header, QualityControl_Audit_Item.AuditItem, QualityControl_Audit_Item.HeaderSequence, QualityControl_Audit_Item.AuditItemSequence, NULL, NULL FROM QualityControl_Audit INNER JOIN QualityControl_Audit_Item ON QualityControl_Audit.QualityControl_Audit_Id = QualityControl_Audit_Item.QualityControl_Audit_Id WHERE (QualityControl_Audit.AuditType = @xAuditTypeName)' IF @PlantId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.PlantId = @xPlantId' IF @BuildingId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.BuildingId = @xBuildingId' IF @AreaId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.AreaId = @xAreaId' IF @WorkCellId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.WorkCellId = @xWorkCellId' IF @WorkShift IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.WorkShift = @xWorkShift' IF @StartDate IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.DateAuditPerformed >= @xStartDate' IF @EndDate IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.DateAuditPerformed <= @xEndDate'SELECT @sql = @sql + ' ORDER BY QualityControl_Audit_Item.HeaderSequence, QualityControl_Audit_Item.AuditItemSequence' IF @debug = 1 PRINT @sqlSELECT @paramlist = '@xAuditTypeName varchar(50), @xPlantId int, @xBuildingId int, @xAreaId int, @xWorkCellId int, @xWorkShift int, @xStartDate datetime, @xEndDate datetime' EXEC sp_executesql @sql, @paramlist, @AuditTypeName, @PlantId, @BuildingId, @AreaId, @WorkCellId, @WorkShift, @StartDate, @EndDateDECLARE my_cursor CURSOR FOR SELECT ItemHeader, Item, MajorInfractionPercent, MinorInfractionPercent FROM #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable OPEN my_cursor --Perform the first fetchDECLARE @ItemHeader varchar(100), @Item varchar(250), @MajorInfractionPercent money, @MinorInfractionPercent money FETCH NEXT FROM my_cursor INTO @ItemHeader, @Item, @MajorInfractionPercent, @MinorInfractionPercent --Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN --Major EXEC dbo.QualityControl_Audit_Item_InfractionPercentageReport_CalculateForMajorOrMinor @AuditTypeName, @PlantId, @BuildingId, @AreaId, @WorkCellId, @WorkShift, @StartDate, @EndDate, @ItemHeader, @Item, 'Major', @debug, @InfractionPercent = @MajorInfractionPercent OUTPUTUPDATE #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable SET MajorInfractionPercent = @MajorInfractionPercentWHERE (((ItemHeader IS NULL) AND (@ItemHeader IS NULL) OR (ItemHeader = @ItemHeader)) AND (Item = @Item)) --Minor EXEC dbo.QualityControl_Audit_Item_InfractionPercentageReport_CalculateForMajorOrMinor @AuditTypeName, @PlantId, @BuildingId, @AreaId, @WorkCellId, @WorkShift, @StartDate, @EndDate, @ItemHeader, @Item, 'Minor', @debug, @InfractionPercent = @MinorInfractionPercent OUTPUTUPDATE #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable SET MinorInfractionPercent = @MinorInfractionPercentWHERE (((ItemHeader IS NULL) AND (@ItemHeader IS NULL) OR (ItemHeader = @ItemHeader)) AND (Item = @Item)) FETCH NEXT FROM my_cursor INTO @ItemHeader, @Item, @MajorInfractionPercent, @MinorInfractionPercent END CLOSE my_cursor DEALLOCATE my_cursor SELECT * FROM #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable
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
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out? SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
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?
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
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#.
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
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.
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
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.
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.
