helo all..., this is my store procedure. but it can not display message. my friend said it must use output code. can someone add output code to my store procedure, so it can display message?ALTER PROCEDURE [bank].[dbo].[pay]( @no_bill INT, @no_order int, @totalcost money, @message varchar(100) -- make it output parameter in your stored procedure)ASBEGIN TRANSACTION DECLARE @balance AS moneyselect @balance = balancefrom bank.dbo.billwhere no_bill = @no_billselect @totalcost = totalcostfrom games.dbo.totalcostwhere no_order = @no_orderif (@balance > @totalcost)beginset @balance = @balance - @totalcostUPDATE bank.dbo.bill SET [balance] = @balance WHERE [no_bill] = @no_bill-- set @message = 'your have enough balance'endelsebeginset @message = 'sorry, your balance not enough'endCOMMIT TRANSACTIONset nocount off pls.., thx
I have a bundling package that runs about 20 other packages. It has been working fine for a while but a couple of days ago it fail with the following message,
Error 0x800706BE while loading package file "D:PackagesToradSales.dtsx". The remote procedure call failed.
I´m running the SSIS packages in an 64-bit environment.
I am trying to insert a record using stored procedure thr' ASP.NET page. I am getting following error. How to find out what exactly is the problem Line 4: Incorrect syntax near 'l'. The label 'PD' has already been declared. Label names must be unique within a query batch or stored procedure. The label 'BA' has already been declared. Label names must be unique within a query batch or stored procedure. Unclosed quotation mark before the character string ''.
HiI've the following SP:-----------CREATE PROCEDURE spServicios@numero int,@maxdif int,@resultado int OUTPUTASBEGINSET ROWCOUNT 1UPDATE control SET registro = getdate()WHERE (fecha >= getdate()AND fecha < dateadd(mi, @maxdif, getdate())AND clientes_codigo IN (SELECTclientes_codigo FROM telefonos WHERE numero = @numero)SELECT @resultado = @@rowcountSET ROWCOUNT 0ENDSELECT @resultado------------But when I try to compile I get the message: Incorrect syntax near thekeyword 'SELECT'Any idea ?Thanks in advanceJ
I have trouble to get this stored procedure running and I tried to figure out where I did a mistake but I'm just lost, so if somebody could help me... Thanks!
I have read many error message articles on the web but still cannot get this to work. I need to return the description of the error that is produced to a output variable (@ErrMsg).
In my stored procedure, I assign @SQLCode to @@Error. @SQLCode is also an output variable. I got the @SQLCode to return no problem, just the description is wrong.
Code SnippetIF @SQLCode <> 0 BEGIN SELECT description = @ErrMsg FROM master.dbo.sysmessages WHERE error = @SQLCode END
Can anyone shed some light on this? I have heard I have to assign the error information to another table and then pull the info from that table, but I don't know how to do that either. Help is greatly appreciated.
Hi all,Here is my error: Server: Msg 245, Level 16, State 1, Procedure NewAcctTypeSP, Line 10Syntax error converting the varchar value 'The account type is already exist' to a column of data type int.Here is my procedure:ALTER PROC NewAcctTypeSP(@acctType VARCHAR(20), @message VARCHAR (40) OUT)ASBEGIN --checks if the new account type is already exist IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @acctType) BEGIN SET @message = 'The account type is already exist' RETURN @message END BEGIN TRANSACTION INSERT INTO AcctTypeCatalog (acctType) VALUES (@acctType) --if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction IF @@error <> 0 OR @@rowcount <> 1 BEGIN ROLLBACK TRANSACTION SET @message = 'Insertion failure on AcctTypeCatalog table.' RETURN @message END ELSE BEGIN COMMIT TRANSACTION END RETURN @@ROWCOUNTENDGO --execute the procedureDECLARE @message VARCHAR (40);EXEC NewAcctTypeSP 'CDs', @message;I am not quite sure where I got a type converting error in my code and anyone can help me solve it???(p.s. I want to return the @message value to my .aspx page)Thanks.
I am creating a package that has many SQL tasks. Each task executes a stored procedure. I need to capture any error messages returned by the stored procedures. Eventually, the error messages will be logged so that we can audit the package and know if individual tasks succeeded or failed.
I'm not sure where or how I can access a stored procedure message. What is the best way?
Hi All,I am having an error message when running the program below, the message says " Could not find stored procedure 'dbo.U_Login ". I have tried a inline Sql statement to match if user exists and then redirects to another page but when using a Stored Procedure what I get is the above mentioned error. I am logged onto my PC as Administrator but I don't know why this error appaears. Can anyone help me with this ? I am using SQL Server Express with Visual Studio. here the application I am trying to run:1 using System; 2 using System.Data.SqlClient; 3 using System.Data; 4 using System.Configuration; 5 using System.Web; 6 using System.Web.Security; 7 using System.Web.UI; 8 using System.Web.UI.WebControls; 9 using System.Web.UI.WebControls.WebParts; 10 using System.Web.UI.HtmlControls; 11 12 13 public partial class _Default : System.Web.UI.Page 14 { 15 protected void Page_Load(object sender, EventArgs e) 16 { 17 18 } 19 protected void btnSubmit_Click(object sender, EventArgs e) 20 { 21 22 //SqlDataSource LoginDataSource = new SqlDataSource(); 23 //LoginDataSource.ConnectionString = 24 // ConfigurationManager.ConnectionStrings["LoginConnectionString"].ConnectionString; 25 ////ConfigurationManager.ConnectionStrings["LoginConnectionString"].ToString(); 26 ////// if (LoginDataSource != null) 27 //// // Response.Redirect("DebugPage.aspx"); 28 29 //Sql connection = LoginDataSource.ConnectionString; 30 31 string UserName = txtUserName.Text; 32 string Password = txtPassword.Text; 33 34 // string query = "SELECT * FROM Users WHERE userName = @UserName " + "AND Password = @Password"; 35 //SqlCommand Command = new SqlCommand(query, new SqlConnection(GetConnectionString())); 36 37 SqlCommand Command = new SqlCommand("dbo.U_Login", new SqlConnection(GetConnectionString())); 38 39 40 //SqlConnection SqlConnection1 = new SqlConnection(GetConnectionString()); //added 41 //SqlCommand Command = new SqlCommand(); //added 42 //Command.Connection = SqlConnection1; //added 43 // Command.Connection = new SqlConnection(GetConnectionString()); 44 //SqlConnection_1.Open();//added 45 46 Command.CommandType = CommandType.StoredProcedure; //added 47 //Command.CommandText = "dbo.U_Login"; //added 48 49 50 51 52 Command.Parameters.AddWithValue("@UserName", UserName); 53 54 Command.Parameters.AddWithValue("@Password", Password); 55 56 Command.Connection.Open(); 57 58 SqlDataReader reader; 59 60 61 //reader = Command.ExecuteReader(CommandBehavior.CloseConnection);11 62 63 reader = Command.ExecuteReader(); 64 65 66 if (reader.Read()) 67 68 Response.Redirect("DebugPage.aspx"); 69 else 70 Response.Write("user doesn't exist"); 71 72 73 //SqlConnection_1.Close();//added 74 75 76 } 77 private static string GetConnectionString() 78 { 79 80 return ConfigurationManager.ConnectionStrings["LoginConnectionString"].ConnectionString; 81 82 } 83 84 85 } 86 87 88 89
My store procedure get the QuestionID (PK) from the page and then it's to return a few varchars but gives me the error that string can't be converted to int. ALTER PROCEDURE [dbo].[usp_getQuestionsforEditPopulateText]@QuestionID int,@QuestionDescription varchar(MAX) OUTPUT,@Option1 varchar(50) OUTPUT,@Option2 varchar(50) OUTPUT,@Option3 varchar(50) OUTPUT,@Option4 varchar(50) OUTPUT,@Option5 varchar(50) OUTPUT,@reference varchar(50) OUTPUT,@chb1 int OUTPUT,@chb2 int OUTPUT,@chb3 int OUTPUT,@chb4 int OUTPUT,@chb5 int OUTPUTAsSet @QuestionDescription =(Select questionDescription from QuestionsBank Where questionID = @QuestionID)Set @Option1 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 1)Set @Option2 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 2)Set @Option3 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 3)Set @Option4 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 4)Set @Option5 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 5)Set @reference = (Select referencedescription from reference where questionID = @QuestionID)Set @chb1 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 1)Set @chb2 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 2)Set @chb3 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 3)Set @chb4 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 4)Set @chb5 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 5) RETURN This is what the page callsDim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection("myconnectionstring ") Dim cmdUpdate As New Data.SqlClient.SqlCommand("usp_getQuestionsforEditPopulateText", dbConnection) cmdUpdate.CommandType = Data.CommandType.StoredProcedure cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionID", Data.SqlDbType.Int)) cmdUpdate.Parameters("@QuestionID").Value = QuestionID cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionDescription", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@QuestionDescription").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option1", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option1").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option2", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option2").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option3", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option3").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option4", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option4").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option5", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option5").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@reference", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@reference").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb1", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb1").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb2", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb2").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb3", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb3").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb4", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb4").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb5", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb5").Direction = Data.ParameterDirection.Output 'open connection dbConnection.Open() 'Execute non query cmdUpdate.ExecuteNonQuery() 'close connection dbConnection.Close()
Client/Server machine: Windows Xp Pro (SP2) (latest patches) Office Software: Access 2003 (latest patches) Database S/W: SQL Server 2005 (latest patches)
The following error message is displayed when trying to modify a stored procedure.
This version of Microsoft Access doesn't support design changes to the version of Microsoft SQL Server your project is connected to. See the Microsoft Office Update Web site for the latest information and downloads (on the Help menu, click Office on the Web). Your design changes will not be saved.
However, if you save, close and re-open the stored procedure having made the required changes, the changes have been saved.
Is there any way to suppress the error message / hotfix available from microsoft since the error message appears to be completely erroneous ?
Have I provided enough detail as this is my first post ?
Hi, I want to create a text file and write to text it by calling its assembly from Stored Procedure. Full Detail is given below
I write a code in class to create a text file and write text in it. 1) I creat a class in Visual Basic.Net 2005, whose code is given below: Imports System Imports System.IO Imports Microsoft.VisualBasic Imports System.Diagnostics Public Class WLog Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String) Dim w As StreamWriter = File.AppendText(LogName) LogIt(newMessage, w) w.Close() End Sub Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter) wr.Write(ControlChars.CrLf & "Log Entry:") wr.WriteLine("(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString()) wr.WriteLine(" :") wr.WriteLine(" :{0}", logMessage) wr.WriteLine("---------------------------") wr.Flush() End Sub Public Shared Sub LotToEventLog(ByVal errorMessage As String) Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog log.Source = "My Application" log.WriteEntry(errorMessage) End Sub End Class
2) Make & register its assembly, in SQL Server 2005. 3)Create Stored Procedure as given below:
CREATE PROCEDURE dbo.SP_LogTextFile ( @LogName nvarchar(255), @NewMessage nvarchar(255) ) AS EXTERNAL NAME [asmLog].[WriteLog.WLog].[LogToTextFile]
4) When i execute this stored procedure as Execute SP_LogTextFile 'C:Test.txt','Message1'
5) Then i got the following error Msg 6522, Level 16, State 1, Procedure SP_LogTextFile, Line 0 A .NET Framework error occurred during execution of user defined routine or aggregate 'SP_LogTextFile': System.UnauthorizedAccessException: Access to the path 'C:Test.txt' is denied. System.UnauthorizedAccessException: at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, ileOptions options) at System.IO.StreamWriter.CreateFile(String path, Boolean append) at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize) at System.IO.StreamWriter..ctor(String path, Boolean append) at System.IO.File.AppendText(String path) at WriteLog.WLog.LogToTextFile(String LogName, String newMessage)
I have an File System Task that copies a file from one directory ot another. When I hard code the target directory (c:dirfile.txt) it works fine. When I change it to a virtual directory (\serverdirfile.txt) I get a security error:
[File System Task] Error: An error occurred with the following error message: "Access to the path '\gracehbtest oS2TMM_Live_Title_000002.xml' is denied.".
I'm trying to use an XML Task to do a simple XSLT operation, but it fails with this error message:
[XML Task] Error: An error occurred with the following error message: "There are multiple root elements. Line 5, position 2.".
The source XML file validates fine and I've successfully used it as the XML Source in a data flow task to load some SQL Server tables. It has very few line breaks, so the first 5 lines are pretty long: almost 4000 characters, including 34 start-tags, 19 end-tags, and 2 empty element tags. Here's the very beginning of it:
<?xml version="1.0" encoding="UTF-8"?> <ESDU releaselevel="2006-02" createdate="26 May 2006"><package id="1" title="_standard" shorttitle="_standard" filename="pk_stan" supplementdate="01/05/2005" supplementlevel="1"><abstract><![CDATA[This package contains the standard ESDU Series.]]></abstract>
There is only 1 ESDU root element and only 1 package element.
Of course, the XSLT stylesheet is also an XML document in its own right. I specify it directly in the XML Task:
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'.
My apologies...I wasn't for sure where to post an error like this...
Over the last 2 months I have gotten this SQL Server error (twice). All existing processes will continue to work, however no new processes can be created and users cannot connect to the server. This is the exact text of the message in the SQL Server error log.
Operating system error 10038: An operation was attempted on something that is not a socket...
Error: 17059, Severity: 18, State: 0
Error accepting connection request via Net-Library 'SSNETLIB'. Execution continuing.
Error: 17882, Severity: 18, State:
While we can typically just stop SQL Server Service and restart the services...I have found it is best to restart the machine during non-production times to take care of any 'residual' effects of this error.
The SQL Server 2000 SP4 box with Windows 2003 Standard SP1 is well maintained by our I.T. team and it typically will run 4 or 5 months without a reboot.
Hi, I have developed a website in asp.net 2. I have tester it and it is working fine on my computer but when I have uploaded it to my server I'm getting an error message when the user signup. The error occurs when I'm setting the user role to 'members'.
Error line > Roles.AddUserToRole(user.UserName, "members")
The strage thig is that it is working on my computer but not on the server. My home computer and the server are running the same software versions and the website database is the same as well.
To double check that my code is not generating the error I have lonched 'SQL Query Analizer' and executed the folowing code on my database: NOTE: In my database I have create the user “teeluk12� and a role “members�
Trying to connect to remote server croaktoad.simpli.biz I have SQL 2005 Developer on XP SP2 , I have disabled my windows firewall. I can ping to my server (croaktoad.simpli.biz) and i get no error message. My remote connection using both TCP/IP and named pipes are checkeed. My SQL Server Browser is running as well.
However when I try to connect using Managment Studio or running SQLCMD /Scroaktoad. simpli.biz /E I get the following error message
C:sqlcmd /Scroaktoad.simpli.biz /E HResult 0x52E, Level 16, State 1 Named Pipes Provider: Could not open a connection to SQL Server [1326]. Sqlcmd: Error: Microsoft SQL Native Client : 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.. Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired
So I've read all the forums for past 2 days and tried everything, nothing changed Any ideas?
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