Need Help In Writing A Stored Procedure In MSSQL Server 2000
Mar 29, 2007
Hi Everybody,
I am trying to update a column Percentage in a table named Critical Doctors with a column named
PercentTime from tblPercent table, Where the column Doctor matches with any DoctorId from
tblPercent.
I am getting an error message for the following query.
Have Two tables
1.CriticalDoctors
2.tblPercent
update CriticalDoctors set Percentage =
(select PercentTime from tblPercent)
where CriticalDoctors.Doctor = (select DoctorId from tblPercent)
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=
, >, >= or when the subquery is used as an expression.
The statement has been terminated.
Pls give me reply on how to write a stored procedure so that I can equate the percentage column
with one value and also check the condition with one value.
Thanking you guys in advance.
madhav
View 4 Replies
ADVERTISEMENT
Dec 26, 2006
Hi,
Has anyone worked on a system that needs per day around 1 Million writes, almost that many reads and deletes once every 1-2h. File sizes range from 2KB-1MB only that 85% of the files are in the 1-8KB range.
This is all done in a single table with a GUID key.
If yes could you tell me what problems did you encounter, any suggestions etc.
Thank you in advance
View 14 Replies
View Related
Mar 4, 2004
Hy all.
My main goal would be to create some kind of map of the database, wich contains information of all connecting fields, including wich fields has key references to the other one and wich table. I'd like to have a table wich shows all the database connections tablename, field, field/table_key (from or to the key points), field_key_type (prymary or foreign)
So I thaugh to get sp_fkeys and sp_pkeys from master table and inner joining their results, but I simplay cannot "catch" their result sets. The script must have been written in SQL.
Obviously I'd like to do this:
SELECT * FROM EXEC sp_fkeys @table_name = 'xy'
of course it is not working this way, but I'd like something like this.
Please help! Thanx!
View 3 Replies
View Related
Jul 7, 2006
Hi,
I am working on an application in ASP.NET 1.1 and SQL Server 2005 as database.I wanted to use SQLCLR feature of SQL Server 2005. Is it possible that i write Stored Procedures in C# 1.1 and deploy on SQL Server 2005? as it is in case of C# 2.0.
Please refer some good tutorial for it.
Regards,Imran Ghani
View 1 Replies
View Related
Feb 26, 2015
Any way to have a process run that will not write its changes to the transaction log? I have a process that runs every three hours and has a huge impact on the transaction log (it becomes larger than the database itself). We do hourly backups of the transaction log and normally it is reasonably sized but when this process runs, it gets HUGE.
The process takes source data, massages it and writes it to summary tables. It is not something we need to track as we can recreate the summary tables if needed and it has no impact on the source tables.
Everything is driven through a stored procedure. Is there a way to run a stored procedure and tell it that nothing it does should be written to the transaction log?
View 6 Replies
View Related
Dec 20, 2007
I'm developing a library and want to display the alphabets across the screen. When a user clicks on one of the alphabets I want all titles beginning with that letter to appear on the screen.
How would I write this stored procedure?
My table is called Titles
Fields: Title ID Titles
Thanks!
View 2 Replies
View Related
Feb 2, 2008
How would I write a stored procedure to get * where duedate is lessthan and not equal to today's date. Is this right?select * from librarywhere duedate < != getdate()Thanks
View 2 Replies
View Related
Oct 30, 2007
hi,
i am new to this.
in my aspx page, i am having a dropdown which has two value
Name
EmpID
if user clicks on name from drop down, user has enter the name of employee in a textbox. And if user clicks on EmpID, he has to enter the employee ID in textbox.
Based on selection the data will be displayed in grid view.
In my database, i have a table called EmpInfo, which has three fields - EmpName, EmpID and State.
can anybody help me in writing a stored procedure for this??
Thanks
Jaimin
View 3 Replies
View Related
Aug 13, 2007
Hi Guys,
I need some help and suggestion to rewrite one of my screens (using ASP.NET) which is using stored procedure. The processing on this screen is taking more than 3 minutes (which i know is totaly
unacceptable). I am making use of cursors within the stored procedure (SQL Server 2005). I really intend to get rid of cursors as they have their performance hit. I have been told to rewrite this screen
(or the stored procedure) so i need some help for SQL Gurus. Following are the details:
1. This is a Monthly Employee Attendance Report on a day by day basis for any given month (maximum 31 days in a month)
2. The values (for each day) have to be computed at runtime and not stored. e.g. Since an employee may have signed in/out several times in a day
3. There are around 500 employees data im dealing with
4. The user will select any given department and employee's data for the respective department has to be displayed for any given month
5. If the user selects [All Department], the entire 500 employees have to be displayed on the screen
6. This report will look like an excel report on the screen i.e. Employee's basic info and record of 31 days (maximum days in a month) are displayed in one row for each employee
7. This report involves are 7-8 tables. 7 tables are for employees basic info whereas one table has the attendance record
Kindly give me your suggestion on writing the SQL stored procedure. I cannot use any other option such as a real Excel Sheet or anything. I need suggestion on how to write this monthly report. By the
way, we dont intend to Cache the data since the report can be viewed at anytime of the day, so fresh data is required everytime. Also the data for 500 employees may be too much to be cached. Also in
the attendance table, we are dealing with approximately half a million attendance records.
Thanks and waiting for your suggestions...
View 7 Replies
View Related
Mar 7, 2005
I am trying to write a stored procedure which would execute following logic:
- The stored procedure takes 2 optional parameters @StartDate and @EndDate
@StartDate Datetime = null
@EndDate Datetime = null
- Since the parameters are optional user can enter either one or can leave both blank.
- If user doesnot enter any values for SD (start date) and ED (end date), stored procedure should run select query replacing those values with wildcard character '%' or NULL
- If user enters SD, query should use @StartDate as the SD and GetDate() as the ED
- If user enters ED, query should use @EndDate as the ED and MIN() of the Date field as SD
I was able to write query which did almost everything as is stated above expect for incorporating NULLs
The query is as below
CREATE PROCEDURE SearchDocumentTable
@FName varchar(100) = null,
@LName varchar(25) = null,
@ID varchar(9) = null,
@StartDate Datetime = null,
@EndDate Datetime = null
AS
IF ( @StartDate IS NULL)
Select @StartDate = MIN(DateInputted) from Document
Select
FName as 'First Name',
LName as 'Last Name',
ID as 'Student ID',
Orphan as 'Orphan',
DocumentType as 'Document Type',
DocDesc as 'Description of the Document',
DateInputted as 'Date Entered',
InputtedBy as 'Entered by'
From Document,DocumentTypeCodes
Where FName LIKE ISNULL(@FName,'%')
AND LName LIKE ISNULL(@LName,'%' + NULL)
AND ID LIKE ISNULL(@ID,'%' + NULL)
AND (DateInputted BETWEEN @StartDate AND ISNULL(@EndDate,GETDATE()) OR DateInputted IS NULL)
AND Document.DocTypeCode = DocumentTypeCodes.DocTypeCode
GO
Any help would be appreciated
Thanks in advance :)
View 7 Replies
View Related
Sep 26, 2013
Want to write from a table variable to a text file from a stored procedure.Read about xp_cmdshell bcp etc. but worried because it's supposed to be a security problem and needs to be from a permanent database.Also am getting error "The EXECUTE permission was denied on the object 'xp_cmdshell'..."
1. Is xp_cmdshell a bad idea to use even if I get permissions ?
2. Can a "permanent" table be used in a stored procedure starting out fresh each time with 0 rows rather than use a table variable ?
View 2 Replies
View Related
Mar 29, 2006
hi
Writing to text file from table/view is done using osql,bcp etc. How do we write output of stored procedure into text file??
Thank you
View 4 Replies
View Related
Aug 7, 2007
Was wondering which is better, writing the SQL code within the execute SQL task or calling a SP from within it with respect to performance? and what is the best practice? Also is there any way to incoporate the logic of a cursur within SSIS?
Thanks
View 3 Replies
View Related
Oct 14, 2007
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
View 3 Replies
View Related
Sep 6, 2006
Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache
View 9 Replies
View Related
May 15, 2007
hi,
i am a nubie, and struggling with the where clause in my stored procedure. here is what i need to do:
i have a gridview that displays records of monthly view of data. i want the user to be able to page through any selected month to view its corresponding data. the way i wanted to do this was to set up three link buttons above my gridview:
[<<Prev] [Selected Month] [Next>>]
the text for 'selected month' would change to correspond to which month of data was currently being displayed in the gridview (and default to the current month when the application first loads).
i am having trouble writing the 'where' clause in my stored procedure to retrieve the selected month and year.
i am using sql server 2000. i read this article (http://forums.asp.net/thread/1538777.aspx), but was not able to adapt it to what i am doing.
i am open to other suggestions of how to do this if you know of a cleaner or more efficient way. thanks!
View 2 Replies
View Related
Apr 28, 2008
Hi.
Now I am learning mssql stored procedure and I faced on some trouble.
I want to make some stored procedure following as...
CREATE PROCEDURE PeopleSearch
(
@name nvarchar(50),
@country nvarchar(50),
@city nvarchar(5),
@condition integer
)
My question from here...But I don't know how to build the code with IF conditions.
IF @condition = 0 THEN
"SELECT * FROM tbl_User WHERE fname='@name'"
IF @condition = 1 THEN
....SELECT Statement..
IF @condition = 2 THEN
....SELECT Statement..
I wish to make some condition in stored procedure like above style.
@condition is not field and it come from outside(vb behind-code).
Is it possible ? Please let me know....how do i have to do...
View 5 Replies
View Related
Jun 22, 2008
please help for MsSQL stored procedure for ASP.net/C#. Inside stored procedure i used 4 different statements in 'where' clause . first one is working but last 3's are not . Statements are given below-FILENAME LIKE ''%'+ @FILENAMETEXT +'%'' -----workingFILENAME LIKE '+ @FILENAMETEXT +'%'' -----not workingSUBSTRING(FILENAME,1,4)='+@FILENAMETEXT+' ----not workingLEFT(FILENAME,4)='+@FILENAMETEXT+' -----not working bunch of code for 'where' clause are given below- DECLARE @FILENAMETEXT CHAR(4) DECLARE @CONDITION VARCHAR(2000) SET @FILENAMETEXT='http' SET @CONDITION = ' WHERE (' SET @CONDITION = @CONDITION + 'TITLE LIKE ''%' +@SearchText+ '%'' OR DESCRIPTION LIKE ''%'+@SearchText+ '%'' OR TAGS LIKE ''%'+@SearchText+ '%'') AND FILENAME LIKE ''%'+ @FILENAMETEXT +'%'' AND ISAPPROVED=1 AND ENCODESTATUS=1 AND ISARCHIVED=0 AND ISDELETED=0 ' SET @CONDITION = ' WHERE (' SET @CONDITION = @CONDITION + 'TITLE LIKE ''%' +@SearchText+ '%'' OR DESCRIPTION LIKE ''%'+@SearchText+ '%'' OR TAGS LIKE ''%'+@SearchText+ '%'') AND FILENAME LIKE '+ @FILENAMETEXT +'%'' AND ISAPPROVED=1 AND ENCODESTATUS=1 AND ISARCHIVED=0 AND ISDELETED=0 ' SET @CONDITION = ' WHERE (' SET @CONDITION = @CONDITION + 'TITLE LIKE ''%' +@SearchText+ '%'' OR DESCRIPTION LIKE ''%'+@SearchText+ '%'' OR TAGS LIKE ''%'+@SearchText+ '%'') AND SUBSTRING(FILENAME,1,4)='+@FILENAMETEXT+' AND ISAPPROVED=1 AND ENCODESTATUS=1 AND ISARCHIVED=0 AND ISDELETED=0 ' SET @CONDITION = ' WHERE (' SET @CONDITION = @CONDITION + 'TITLE LIKE ''%' +@SearchText+ '%'' OR DESCRIPTION LIKE ''%'+@SearchText+ '%'' OR TAGS LIKE ''%'+@SearchText+ '%'') AND LEFT(FILENAME,4)='+@FILENAMETEXT+' AND ISAPPROVED=1 AND ENCODESTATUS=1 AND ISARCHIVED=0 AND ISDELETED=0 ' please reply me as soon as possible
View 1 Replies
View Related
Jul 20, 2005
I am trying to get the DTS Execute SQL task to run a simple sql server 2000no-parameter stored procedure.The procedure runs without error when using SQL Analyzer but DTS Execute SQLreports "could not find stored procedure "[dbo].[test]"The stored procedure is as follows:create procedure test asdeclare tnames_cursor CURSORforselect database_name, tmp_table_name from[DW_Dimensions].[dbo].[vw_dimension_temporary_tables_active]open tnames_cursordeclare @tablename sysnamedeclare @databasename sysnamewhile (@@FETCH_STATUS <> -1)beginselect @databasename = rtrim(@databasename)select @tablename = rtrim(@tablename)exec ("delete from " + @databasename + ".dbo." + @tablename)fetch next from tnames_cursor into @databasename, @tablenameendclose tnames_cursordeallocate tnames_cursorGOAny ideas?
View 1 Replies
View Related
Feb 1, 2008
I have a stored procedure that builds a sql statement and executes it. When run in Query Analyzer it runs successfully and displays records as it should. When I open a recordset with the stored procedure from VBA it executes the codes but doesn't open the recordset.
The store procedure is:
ALTER PROCEDURE dbo.spTest
AS
DECLARE @sColumns varchar(2000)
DECLARE @sFrom varchar(2000)
DECLARE @sWhere varchar(2000)
DECLARE @sSqlString AS nvarchar(2000)
SET @sColumns = 'dbo.tblDoctor.lastname '
SET @sFrom = 'FROM dbo.tbldoctor '
SET @sWhere = 'WHERE dbo.tbldoctor.doctorcode is not null '
SET @sSqlString = 'SELECT ' + @scolumns + ' ' + @sFrom + ' ' + @swhere PRINT '@scolumns ' + @scolumns
PRINT '@sfrom ' + @sfrom PRINT '@swhere ' + @swhere PRINT 'sqlstring ' + @sSqlstring
EXEC Sp_executesql @sSqlString
The VBA code is:
Private Sub Command0_Click()
Dim cn As Connection
Dim rs As New ADODB.Recordset
Dim strsql As String
Set cn = CurrentProject.Connection
strsql = "exec spTest"
With rs
.Source = strsql
.ActiveConnection = cn
.CursorType = adOpenKeyset
.LockType = adLockReadOnly
.Open
End With
Debug.Print rs.RecordCount & " records found"
End Sub
When it hits the line with rs.recordcount it displays the following error: "Operation is not allowed when object is closed" referring to the rs recordset never being opened.
Any suggestions?
View 4 Replies
View Related
May 30, 2008
suppose,the type of the stored procedure's paramters is varchar .I hate to add parameterNames and types.If i can read the string of the stored procedure the get the paramterNames by operating text?
public void storeOperate(string stringParameter,string name) { string[] strs=stringParameter.Split('&'); SqlConnection conn = new SqlConnection(getConnectionString.getconnectionString()); SqlCommand cmd=new SqlCommand(name,conn); cmd.CommandText=name; cmd.CommandType=CommandType.StoredProcedure; foreach(string str in strs) { cmd.Parameters.Add(".....",SqlDbType........).Value=str; //my trouble } conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); cmd.Dispose(); }
View 2 Replies
View Related
Jul 30, 2007
Hi
This is seshu.
Is there any way to find out the last used stored procedure.
Let me know about this one.
View 1 Replies
View Related
Apr 1, 2005
I'm using Java to connect to a SQL Server 2000 database. I connect using the Driver Manager with Sun's odbc driver ( sun.jdbc.odbc.JdbcOdbcDriver ) or I can use the jdbc driver provided by Microsoft (com.microsoft.jdbc.sqlserver.SQLServerDriver)
The Java application makes 1 Connection.
Within the database there exists a stored procedure that updates 2 Tables. The tables have a fixed number of rows that get updated continuously by calls to this stored procedure.
The Java application has a thread pool of 15 threads that create 15 CallableStatements (1 per thread) using the same instance of the Connection object.
According the the Microsoft JDBC driver docs, 1 Connection with multiple calls to the Callable statements is how it's supposed to be done. The following is an excerpt from Microsoft's "SQL Server 2000 Driver for JDBC User’s Guide and Reference" (page 86) regarding Connection Managment:
Managing Connections
Connection management is important to application performance. Optimize your application by connecting once and using multiple statement objects, instead of performing multiple connections. Avoid connecting to a data source after establishing an initial connection.
This is precisely what I'm doing, but I do not know if the the stored procedures are being run concurrently, the documentation does not tell me.
So my question: What is happening inside SQL Server 2000?
View 2 Replies
View Related
May 19, 2004
Hi everyone,
I am new to the forum and I have a problem with a stored procedure in SQL Server 2000. I use a stored procedure that returns a string but the problem is that my variable is empty at least I think it's empty.
With the code below the variable is not empty:
set @Ssql = 'select Cust_Company_id, fix_price from GEC_FixPrice where priority > 0 and cust_company_id like ' + char(39) + @InvoiceLine_CustCompanyid + char(39) + ''
But when I use the code below it's empty
- set @sSql = 'select Cust_Company_id, fix_price from GEC_FixPrice where priority > 0 '
- set @sSql = @sSql + ' and cust_company_id like ' + char(39) + @InvoiceLine_CustCompanyid + char(39) + ''
I also tried this :
- set @Start = 'select Cust_Company_id, fix_price from GEC_FixPrice where priority > 0 '
- set @End = ' and cust_company_id like ' + char(39) + @InvoiceLine_CustCompanyid + char(39) + ''
- set @sSql = @Start + @End
This works also, so I don't get why the second example won't work and the second example is the one I want to use.
So does anyone has a clue :confused:
thx in advance,
grtz
Loki2600
View 2 Replies
View Related
Jul 20, 2005
Hi All,I have a table that holds pregenerated member IDs.This table is used to assign an available member id to web sitevisitors who choose to register with the siteSo, conceptually the process has been, from the site (in ASP), to:- select the top record from the members table where the assigned flag= 0- update the row with details about the new member and change theassigned flag to 1- return the selected member id to the web pageNow I'm dealing with the idea that there may be brief, high trafficperiods of registration, so I'm trying to build a method (storedprocedure?) that will ensure the same member id isn't returned by theselect statement if more than 1 request to register happens at thesame instant.So, my question is, is there a way, once a record has been selected,to exclude that record from other select requests, within the boundsof a stored procedure?ie:- select statement is executed and row is instantly locked; any otherselect statement running at that exact moment will receive a differentrow returned and sill similarly lock it, ad nauseum for as manysimultaneous select statements as take place- row is updated with details and flag is updated to indicate themember id is no longer unassigned- row is released for general purposes etcIf what I'm suggesting above isn't practical, can anyone help meidentify a different way of achieving the same result?Any help immensely, immensely appreciated!Much warmth,Murray
View 12 Replies
View Related
Aug 15, 2007
I have no idea about how can I use xp_sendmail
when I try to use it this error displayed on the screen.
There is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail ...
View 3 Replies
View Related
Mar 12, 2007
Can anyone of you help me in finding online - resources to write extended stored procedure for Sql Server 2000 using C#.
Thank you,
Andy Rogers
View 1 Replies
View Related
Jul 23, 2007
Hi,
I m wanting a stored procedure to pass an XML string from a stored procedure to my application. Whats the best way to achieve this?
Hope you can help
Thanks
Paul
View 10 Replies
View Related
Dec 14, 2006
Is there a way to do a SELECT TOP # using a variable for the #?
In other words I'm doing a SELECT TOP 50* FROM DATATABLE
If I pass an @value for the number
SELECT TOP @value* FROM DATATABLE doesn't work
I am generating a random sampling of data and I want to allow the user to select the number of results they choose to have.
Thanks in advance.
View 2 Replies
View Related
May 21, 2008
Hi i have a stored procedure which deletes a row from a table.
Sometimes the proc will break the constraints for the table.
How can i check if the constraint is going to be broken before I try to delete so that I can avoid a nasty error message coming onto my asp.net page?
Thanks in advance.
Gary
View 2 Replies
View Related
May 19, 2007
I am developing a windows application in VB.Net 2005 and Database is SQl Server 2000.I want to insert, update and delete records from a master table which has 8 columns.So should I write a stored procedure for this or write three queries and execute them in code.I haven't used stored procedure before. What will be advantages of using stored procedures?And tell me how to write stored procedure to insert,update and delete. Then how to call it in VB.Net code.
View 7 Replies
View Related
Mar 3, 2008
can I send a mail by stored procedure?
View 5 Replies
View Related
Jul 15, 2007
Hi allI am looking for the best method to automate a website's database management. Lets say I have a user registration database and the users register. This sends an automated email to the user with a link to activate the users registration. If the user does not register within 24 hours, his registration must be automatically deleted from the database using a stored procedure.I know how to do this using the global.aspx file, however there must be an alternative way of doing this, especially if the database is an SQL database. I do not know how much MSSQL server access is given to a developer by an as ISP who hosts the website.Can anyone tell me what would be the best method to use.ThxWarren
View 1 Replies
View Related
