I have a urgent problem, and can not google any useful answer. Please help me!
In my CLR store procedure, I have used a context connect, and I want to creat another connect to the same database. I have set the database permission to external, creat asymmetric key in my assembly, and creat login for that key. The dbo is the owner.
When I use this connect string as store procedure input, and I opened this new connect in the CLR sp, it throw an exception --"***.MDF is in used, failed to connect"!!
I'm new to sql server stored procedures - I have 2 tables tasksTbl and staff_TasksTbl - tasksTbl primary key is an identity column - When inserting a row into tasksTbl I want to get the ID for the new inserted row using SCOPE_IDENTITY() and use it into a new insert command that will add a row into staff_TasksTbl
I need to know whats the problem in this procedure
CREATE PROCEDURE insertTask (@TaskName varchar(20), @TaskDescription varchar(20),@Rating int, @Status varchar(20),@StartAt varchar(20), @EntAt varchar(20),@EventID varchar(20), @StaffID varchar(100) ) AS declare @TaskID int SET NOCOUNT ON insert into TasksTbl(TaskName, TaskDescription, Rating, Status, StartAt, EntAt, EventID)values(@TaskName, @TaskDescription, @Rating, @Status, @StartAt, @EntAt, @EventID) select @TaskID = SCOPE_IDENTITY()
insert into staff_TasksTbl(TaskID,StaffID)values (@TaskID, @StaffID) GO
hi, i save some text into my database which looks like this.. <ol> <li><strong>adfadsf as </strong> <li><strong>asf as </strong> <li><strong>dfas </strong> <li><strong>df sad </strong> <li><strong>f as</strong> </li> </ol>
how m i gg to display them as numbered list in my report?
i'm using store procedure to retrieve them out into pdf format..
Actally we have different server and we work on only one server and end of the day i have to copy the list of newly or modified procedure to other databases of other server. i have create the following Sp. but i am hving problem int it. please any one can have a look..
DECLARE ProcedureScripingCursor CURSOR FOR SELECT SQLServer,DatabaseName FROM databaseListName
-- where sqlserver is ip address and databasename is databasename on that server
OPEN ProcedureScripingCursor FETCH NEXT FROM ProcedureScripingCursor INTO @ServerName, @DatabaseName WHILE @@FETCH_STATUS = 0 BEGIN SELECT @sp = REPLACE(@sp,'''','''''')
When I create/alter a store procedure in SQL Server 2005, SQL server always checks for syntax errors first and won't let me save the change if it detects any error. Is there a way we can force the SQL server to save the store procedure that fails the syntax check?
I know SQL server will allow such invalid store procedures if you detach & re-attach the entire database from one SQL server to another server. However, if I try to manually create the same store procedure from one server on a different server with a script, then it won€™t let you save the store procedure if the linked server (or the table) can€™t be accessed from the new sql server.
name age weightaaa 23 50bbb 23 60ccc 22 70ddd 24 20 eee 22 30i need the output that calculate the sum of weight group by name input : age limit ex: 22 - 23 output : age total weight 23 11022 100 this output must stored in a sql declared variable for some other further process .
A DB2 store procedure returns two data sets, when executed from SSMS, using linked server. Do we have any simple way to save the two data sets in two different tables ?
I wanted to know if it is possible to run a stored proc (report) when a new record is inserted / updated into a certain table. Also if it could do so for records with a column that meet a certain criteria ie. sell description like '%test%'
Hi I am trying to run 4 - 5 store procedure in a strict order. I have created a SSIS package whereby I trying to execute store procedure in a orderly fashion. I have created constraints between them. Yet whenever I run the package - the store procedures run out of sequence. Is there something I can do to force the store procedure in orderly manner? Help will be much appreciated.
My store Procedure is not save in Strore Procedure folder at the time of saving it give me option to save in Project folder and file name default is SQLQuery6.sql when i save it after saving when i run my store procedure
exec [dbo].[SP_GetOrdersForCustomer] 'ALFKI'
I am getting below error :
Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'dbo.SP_GetOrdersForCustomer'.
Hi guysi would really appreciate your help here. what am i doing wrong with this stored procedure ALTER PROCEDURE usp_CreateNewCustomer( @LName as varchar(50), @FName as varchar(50), @Dept as varchar(50)=NULL, @PhoneType as varchar(50)=NULL, @Complete as Bit=NULL, @CustomerID as Int OUTPUT, @FaxModel as varchar(50)=NULL, @FaxNumber as varchar(50)=NULL, )ASINSERT INTO [CustomerInfo] ([LName], [FName], [Dept], [PhoneType], [Complete]) VALUES (@LName, @FName, @Dept, @PhoneType, @Complete)SELECT SCOPE_IDENTITY()INSERT INTO Extras (CustomerID, FaxModel, FaxNumber) VALUES (@CustomerID, @FaxModel, @FaxNumber)RETURN It keeps on complaning "'usp_CreateNewCustomer' expects parameter '@CustomerID', which was not supplied."thanks
Hi all, I have a few question regarding SPROC. Firstly, I want to create a sp, that will return a multiple column of data, how do you achieve that. Secondly, I want to create a store procedure that will return a multiple columns in a cursor as an output variable. Any help will be much appreciated. Can you have more then 1 return parameters??? Thanks. Kabir
I have asp.net web application which interface with SQL server, I want to run store procedure query of SQL using my asp.net application. How to declare connectons strings, dataset, adapter etc to run my store procedure resides in sql server. for Instance Dim connections as string, Dim da as dataset, Dim adpt as dataadapter. etc if possible , then show me completely code so I can run store procedure using my button click event in my asp.net application thank you maxmax
I am not sure if it is right place to ask the question. I have a store procedure in which funcitons are called and several temp tables are created. When I use sql analyzer, it runs fast. But when I called it from asp.net app, it runs slow and it waits and waits to get data and insert into temp tables and return. Though I have used with (nolock), it still not imprvoed. Any suggestions? Thanks in advance. NetAdventure
Hello, I am very new to ASP.NET and SQL Server. I am a college student and just working on some project to self teaching myself them both. I am having a issue. I was wondering if anyone could help me. Ok, I am creating a web app that will help a company to help with inventory. The employee's have cretin equipment assign to them (more then one equipment can be assigned). I have a search by equipment and employee, to search by equipment the entire database even then equipment that isn't assigned with the employee's it shows only that employees and if they have any equipment. They can select a recorded to edit or see the details of the record. The problem i am having is that the Info page will not load right. I am redirected the Serial Number and EmployeeID to the info page. I got everything working except one thing. If a person has more then one equipment when i click on the select it redirect and only shows the first record not the one I selected. and when i change it, a employee that doesn't have equipment will not show up totally.
SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID and eq.SerialNo=@SerialNo EmployeeID and SerialNo are primary keys. I was thinking maybe create a store procedures, but I have no idea how to do it. I created this and no syntax error came up but it doesn't work CREATE PROCEDURE dbo.inventoryinfo AS DECLARE @EmployeeID int DECLARE @SerialNo varchar(50) IF( @SerialNo is NULL) SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID ELSE SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID and eq.SerialNo=@SerialNoGO Also, when a user selects the select button in either Employee search or Equipment search it redirects them to info.aspx. Should I create a different aspx page for both? But I don't believe I should do that. I don't know if I gave you enough information or not. But if you could give examples that would be wonderful! Thanks so much Nickie
Hi, All, I need to write a sales report which needs to seperate total from direct sale and agentsale. The report looks like this( in the table of query,we have agentnumber, productname, sales, month
Month salefromagent directsale total Jan 1100 2300 3400 Feb 800 500 1300 .......... Dec --------------------------------- I know we can handle this in the program use two queries. But is there a way to do it use store procedure and then pass the result of store procedure to the ASP program. I am trying to write my first store procedure, thanks for any clue. Betty
Hi all, I need to write a sales store procedure. The sales summary is basically group by agentCode. But the problem is some agents have subagents. i.e., in the sales table. one agent 123456 has many subagents whose agentCode start with 17. And I want to group sales for all subagents who have agentCode 17XXXX to agent 123456's sales. what should I do in the store procedure.
I am trying to create a store procedure that look at a table and only maintain 30 worth of data only forever. The data that fall outside the 30 days need to be deleted. Can anyone help me with this?
I would like to create a employee store procedure to insert a record and mean while i can retrieve the EmployeeID for the record just inserted. Can you show me how to create a store procedure like this. Now i have a insert store procedure in the following:
CREATE PROCEDURE AddEmployee (@efname nvarchar(20), @elname nvarchar(20) ) AS
insert into tblEmployeeName (EmployeeFName, EmployeeLName) values (@efname,@elname) GO
This is an Oracle store procedure. Can Any body help me to convert it into SQL Server stored Procedure
PROCEDURE CALC_PERC (DB_ID IN NUMBER, LAT_TYPE IN CHAR) IS Tot_work_all number(12,2); Bid_tot number(12,2); Ewo number(12,2); Overruns number(12,2); Underruns number(12,2); Contr_tot_all number(12,2); sContractType ae_contract.contr_type%type; BEGIN select sum(nvl(tamt_ret_item,0) + nvl(tamt_paid_item,0)) into Tot_work_all from valid_item Where db_contract = db_id; Select sum(Contq * Contr_Price) into Bid_tot From Valid_item Where nvl(New_Item,'N') <> 'Y' and db_contract = db_id; Select sum(Qtd * Contr_price) into Ewo From Valid_item Where nvl(New_item,'N') = 'Y' and db_contract = db_id; Select Sum((Qtd-Nvl(Projq,0))*Contr_Price) into Overruns From Valid_item Where Qtd > Nvl(Projq,0) and db_contract = db_id and nvl(New_Item,'N') = 'N'; IF LAT_type <> 'R' THEN Select Sum((Nvl(Projq,0)-Contq) * Contr_Price) into Underruns From Valid_item Where Nvl(Projq,0) < Contq and db_contract = db_id and nvl(New_Item,'N') = 'N'; ELSE Select Sum((Nvl(Qtd,0)-Contq) * Contr_Price) into Underruns From Valid_item Where Nvl(Qtd,0) < Contq and db_contract = db_id and nvl(New_Item,0) = 'N'; end if; Contr_tot_all:= NVL(Bid_tot,0) +NVL(ewo,0) +NVL(overruns,0) +NVL(underruns,0);
IF Contr_tot_all = 0 THEN
Select Contr_type into sContractType from ae_contract where db_contract = db_id;
IF sContractType = 'A' OR sContractType = 'T' THEN --If the divisor is zero here, it's not an error. update ae_contract set perc_compu = 0 where db_contract = db_id;
ELSE --If the divisor is zero here, it would be an error update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id; END IF; Else --Here we have a real number to calculate, so go ahead and do your stuff! update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id; END IF; END;
