People I am not a SQL Server guy so bear with me please. This should be very simple. I am looking for a procedure; given a TABLE_NAME as input; will run the following three SQL statements for each COLUMN in that table:
select max(COLUMN_NAME) from TABLE_NAME
select count(0) from TABLE_NAME where COLUMN_NAME is null
select count (distinct COLUMN_NAME) from TABLE_NAME
It will then write the output in a readable fashion to a local file.
Hello Everyone,I am sure, for the knowing, that this is a simple question.I have a table called tblTickets and a table called tblProjects. I would like to make a stored procedure that does the followingCopy entire contents of tblTickets record to tblProjects record and also changes tblTickets.sDetails to contain some text like "This ticket has been escalated into a project click here to view the project"tblProjects.sDetails would remain the same as the original.tblProjects will have additional fields but they can remain blank for the time being.ThanksMatt
What I am doing:I am in the process of creating a "log" that records when I update a record. I would like a new row to be inserted into BookLog each time a book's information is updated. Below I have the "update" code and below that is the code that is suppose to be inserting the newely updated record in the BookLog table. **What the problem is**: I've tested the insert code, everything works EXCEPT BookEntryDate. I do not want to update the BookEntryDate when I am inserting it into BookLog. I just want to keep the original entry date from the Books table thats already there. In the BooksLog table I have "BookEntryDate" to not allow nulls...when i go to test i get an error that says "Procedure 'BookUpdate' expects parameter '@BookEntryDate', which was not supplied." I've tried several things and I am stuck :-/ I need some ideas. Thanks for your help!Alter Procedure dbo.BookUpdate@BookEntryDate datetime,@BookSummary nvarchar(1000),@BookComment nvarchar(50),@UserID nvarchar(50),@BookID bigint AsDeclare @Now datetimeSet @Now = GetDate()Update BooksSet Books.BookSummary = @BookSummary,Books.UserID = @UserID,Books.BookComment = @BookCommentWhere Books.BookID = @BookIDINSERT INTO BookLog(BookID,BookEntryDate,BookSummary, UserID,BookCommentLogDate)VALUES (@BookID,@BookEntryDate, @BookSummary,@UserID, @BookComment,@Now)RETURN GO
hi, i have a sqlserver2005 table 'member' with the fields 'id','name','datecreated','datemodified' and 'memberlevel' (which the default value is 1). Now i want to create a stored procedure that will run automatically once a day, this procedure will reset to 0 the 'memberlevel' that are 1 if the 'datemodified' is greater than 3 months. can anyone help me on this, thanks!
When I verify syntax, I get this error: Msg 1038, Level 15, State 4, Procedure webservices_BENEFICIAL_USES_DM_SELECT, Line 8 An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
Here's the proc: CREATE PROCEDURE webservices_BENEFICIAL_USES_DM_SELECT -- Add the parameters for the stored procedure here @DISPOSAL_AREA_NAME varchar(40) = "" AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here SELECT* FROMBENEFICIAL_USES_DM END GO
Hey all, Can anyone tell me why this stored procedure doesn't work?
ALTER PROCEDURE [dbo].[RemoveLocation] @Id int, @Name varchar AS DELETE FROM Classifieds_Terminals WHERE [Id] = @Id and [Name] = @Name
I try exeuting it like this: USE [CLASSIFIEDSDB2.MDF] GO DECLARE @return_value int EXEC @return_value = [dbo].[RemoveLocation] @Id = 18, @Name = N'Terminal A' SELECT 'Return Value' = @return_value GO
It returns 0 and nothing happens....???
Here is the table: id | Name 18 Terminal A18 Terminal B18 Terminal C
Hi, I got a problem of a sp. Did the sp can use over one GO script? It is because after I create the below scripts in the sp and checked no syntax error and then apply changed. I re-open this sp, it just save my scripts upto the first GO statement . The scripts from "IF EXISTS..." has not saved. But I try to run the script in analyzer is successful. How can I fix it in Enterprise manager? Thanks. Here is my test code: CREATE PROCEDURE sp_Test AS--set nocount onSELECT id, name from MainGO--insert the record in SimpleReports tableIF EXISTS (select ID from Main where id=5)delete from Main where id=5GOINSERT INTO Main ( ID, Name, Quantity)VALUES ( '6', 'hij', '60')GO
I have what I hope is a simple question about a stored procedure. If this is not the right spot for this, I offer my apologies. I am planning on calling a stored procedure from my ASP.Net site using C#. What I want to do is pass a variable to the procedure and using that varible, put together another variable that I will pass back. I am very new to all but the most basic stored procedures so I was not shocked when my code didnt work. Can anyone toss me a hint? I am actively working on this and if I find anything on a website to assist and have a breakthru, I will be sure to update you all. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON goCREATE PROCEDURE [dbo].[GetAttySectyData] (@ID as int) AS declare @First as varchar(200) declare @Middle as varchar(50) declare @Last as varchar(200) declare @Name as varchar(255)@First = SELECT value FROM dbo.text WHERE efield_id = 10741 AND employee_id = @ID @Middle = SELECT value FROM dbo.text WHERE efield_id = 10906 AND employee_id = @ID @Last = SELECT value FROM dbo.text WHERE efield_id =10740 AND employee_id = @ID Select @First+' '+@Middle+' '+@Last from text AS @Name RETURN
I am using SQL Server Express within visual studio and am needing to create a stored procedure. The proble is that every time I finish and close it then it asks me if I want to save the changes which of course I want to. But then it always comes up with a message box that says "invalid object name" followed with the name of the stored procedure. ??? why? I try creating a stored procedure from scratch or copying the code of another stored procedure. It alway gives me that message and does not let me save the stored procedure. I tried changing the default name of "dbo.storedprocedure1" or even leaving it as is and always I get the message that says "invalid object name: dbo.storedprocedure1" and it does not let me save the procedure. What am I doing wrong?
I am trying to figure out the syntax for an SQL statement I have in a stored procedure.CREATE PROCEDURE dbo.forum_post_add @forumId integer, @fpTopic char(100), @fpBody text, @fpPoster integer AS Insert into forumPost (forumId, fpTopic, fpBody, fpPoster) values (@forumId, @fpTopic, @fpBody, @fpPoster) UPDATE forum SET forumThreads = forumThreads + 1 where forumId = @forumId UPDATE forumGroup SET fgThreads = fgThreads + 1 where fgID = select forumGroup from forum where forumId = @forumId GOThe statement in question is the second UPDATE. How do I create an UPDATE statement that selects that correct record based on the single result of a SELECT statement?
I am also not too sure about the 'fgThreads = fgThreads + 1' thing, is this the best way to increment a value?
I know I should also be using transactions and probably a few other bells and whistles but I'll worry about that later.
I have a SP below that authenticates users, the problem I have is that activate is of type BIT and I can set it to 1 or 0. If I set it to 0 which is disabled, the user can still login. Therefore I want users that have activate as 1 to be able to login and users with activate as 0 not to login
If I put 5 or 6 stored procedures within another stored procedure will they be executed sequentially or all at once? Is there any method. I can manage this process with some code I was just wondering how query analyzer or an sp would handle this by default.
Dear experts,Again, sorry to bother you again with such a seemingly dumb question,but I'm having some really mysterious results here.ie.Create procedure the_testAsBeginSelect CustomerID
Hi All,I am pretty new to stored procedure and I have a quick question. Iappreciate any help offered.I am writing a stored procedure as follow. This is just an example:select field1, field2 from table1 where field1 = 1I need to change this stored procedure so that if field2=0 then it willbe displayed on the web application as 'Yes', if field2=1 then I needto display the text 'No' on the web application.Field2's type is bit, ie: its' value is either 1 or 0.Thanks for any suggestion.Sincerely,Teresa
room table looks like roomID room name 1 prikey 100 2 101 3 102
Color table looks like
colorID Color name 1prikey red 2 blue 3 grey 4 white
I will need to pull the data from the system table based building name, room name and color. If the parameter is zero I will not filter on that parameter so will only be filtering on one parameter for each time the procedure is called. Any ideas on a simple query, thanks!
create procedure [dbo].[usp_procedure]@var1 int=null output,@var2 varchar(25)=null output,@var3 varchar(25)=null output,@var4 varchar(25)=null output,@var5 varchar(25)=null output,@action char(1)=null outputasbegin set nocoutn on;if @action = 'S'begin select * from t_table order by @var1end
in my script I call to this stored procedure but, this send me a error. my script is $action="S";// Set up T-SQL query.$tsql = "call dbo.usp_T_Usuarios (@action=?)";// Assign parameter values.$params = array($action);// Specify types for parameters.$stmt = sqlsrv_query( $conn, $tsql, $params);echo "<br><br>Executed.....<br><br>";// Create and execute the statement. Display any errors that occur.if(!$stmt){ echo "Error executing the sp...<br>"; die( print_r( sqlsrv_errors(), true));}else{ echo "<br>Execute sp....<br><br>"; echo $stmt;} and the error is:
conected...The conection was succefully.Executed.....Error executing the sp...Array ( [0] => Array ( [0] => 07009 [SQLSTATE] => 07009 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Native Client]Ă?ndice descriptor no vĂ¡lido [message] => [Microsoft][SQL Native Client]Ă?ndice descriptor no vĂ¡lido ) [1] => Array ( [0] => 07009 [SQLSTATE] => 07009 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Native Client]NĂºmero de parĂ¡metros no vĂ¡lido [message] => [Microsoft][SQL Native Client]NĂºmero de parĂ¡metros no vĂ¡lido ) )I need help me. some body tell me what is my error. please.
I need to get a better understanding of alter procedure. If I Create a Procedure in SQL Server 2005, and want to modify that to Alter Procedure statement instead of create procedure... How do I do that?? I'm not an SQL Server person.. I dont understand why my work place needs Alter proceudre all the time but all our procedures have Alter procedure statement. Once they are created they have to be changed to Alter procedure... Do I just need to change the keyword CREATE to ALTER?? because when I try to save it ,,, it saves it as a .sql file... Not the script. Immediate reply would be appreciated
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.
I suspect I have a but in a stored procedure that is used to delete records in a table. Here's the code for the gridview and its SQLDataSource 1 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 2 DataKeyNames="table2_id" DataSourceID="SqlDataSource1"> 3 <Columns> 4 <asp:CommandField ShowDeleteButton="True" ShowSelectButton="True" /> 5 <asp:BoundField DataField="table2_id" HeaderText="table2_id" 6 InsertVisible="False" ReadOnly="True" SortExpression="table2_id" /> 7 <asp:BoundField DataField="table1_id" HeaderText="table1_id" 8 SortExpression="table1_id" /> 9 <asp:BoundField DataField="name_2" HeaderText="name_2" 10 SortExpression="name_2" /> 11 <asp:CheckBoxField DataField="yesno" HeaderText="yesno" 12 SortExpression="yesno" /> 13 <asp:BoundField DataField="MyArtificialColumn" HeaderText="MyArtificialColumn" 14 ReadOnly="True" SortExpression="MyArtificialColumn" /> 15 </Columns> 16 </asp:GridView> 17 <asp:SqlDataSource ID="SqlDataSource1" runat="server" 18 ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString %>" 19 DeleteCommand="EraseIt" DeleteCommandType="StoredProcedure" 20 SelectCommand="StoredProcedure5" SelectCommandType="StoredProcedure"> 21 </asp:SqlDataSource> Recently, "Wreck of u" kindly provided me with the stored procedure "StoredProcedure5" (line 20) which populates the gridview as shown. What I would like to now is write a stored procedure that DELETES a row. As like 19 shows, I attempted to write such an SP called EraseIt. However, when I click on Delete in the browser for a row, I get the error message "Procedure EraseIt has no parameters and arguments were supplied. The following is the code for EraseIt. Do I in fact have an error with specifying the input parameter "@table2_id", and or am I doing something else wrong? 1 ALTER PROCEDURE [dbo].[EraseIt] 2 AS 3 DECLARE @TABLE2_ID int 4 BEGIN 5 DELETE FROM DBO.TABLE2 WHERE TABLE2_ID = @TABLE2_ID 6 END 7 Thanks!-Kurt
I got some xml that is essentially an html table that I need to turn into a standard table resultset from a stored proc. If you take this xml and save it as html that is the desired resultset I am looking for. I realize the <td> tags repeat so I would just prefer 'col' + positional index for the col name. Keep in mind that <td> could be is 1 to n.
I ma using sql server 2005.I have a bunch of statements of sql and i have created a stored procedure for those. When i execute i found that there is lot's of difference between execution time of stored procedure and direct sql in query windows.
can anyone help me to optimize the execution time for stored prcedure even stored prcedure is very simple. I have used sql server 2000 and i am new in sql server 2005.
I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.
result set
colA colB colC colD colE colF colG colG (from yesterday) 1 1 a b c d e m 1 1 a b c d m e
So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.
I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)
foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)
however not all hotels have thumbnail image
hence i have hotel inner join AddressXReff inner join Address ..... however i must have left outer join mediaXref left outer join media
the problem is that if there is no entry in Media or mediaXref, I don't get any results
i tried to get over it by using where (media.mediaTyple like 'thumbnail' or media.mediaType is null) but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned
...when I started this endeavor. I have a previously developed Lotus Notes App. The idea was simple; as I am sefl taught on Lotus Script, I figured I'd be able to stumble my way through VB.Well, it started OK. I used VB Express to get familiar with the stuff, but decided to go with a full version of VS 2005 and try and get this thing properly developed as a web app. I purchased several reference books etc., and have become relatively familiar with the forums here.First issue I have is that I simply want to use code to update or add records to an SQL DB. I know about datagridview etc., but I want to update the DB using forms, not the tabular view those controls provide. I thought it would be relatively straight forward, but found my ignorance runs deeper than I thought. When I tried to do so I am finding I am not really clear on where to make declarations etc. in the web app. If anyone could point me in the right direction that would be great. The issue with searching these forums is most posts deal with datagridview or something similar.I have spent a ton of time trying to find relevant posts or articles, but have had no luck yet.Again, all Ireally need is a nudge in the right direction. I am more than willing to plod through reference materials or articles/posts to find what I need to know, I just can't find where to even start on the info I need. Regards, Joe
Hi I have a problem with my sql WHERE query, if i manually type ([Area] = 'The First Area') then it is okay but if i try and pass the variable 'The First Area' using the
([Area] = @Area) it doesnt work. ALTER PROCEDURE dbo.StoredProcedure1(@oby nvarchar,@Area char,@Startrow INT,@Maxrow INT, @Minp INT,@Maxp INT,@Bed INT)ASSELECT * FROM(SELECT row_number() OVER (ORDER BY @oby DESC) AS rownum,Ref,Price,Area,Town,BedFROM [Houses] WHERE ([Price] >= @Minp) AND ([Price] <= @Maxp) AND ([Bed] >= @Bed) AND ([Area] = @Area)) AS AWHERE A.rownum BETWEEN (@Startrow) AND (@Startrow + @Maxrow) Please Help I know it must be something simple as the sql works but not when i pass the variable.... Thanks In Advance
Output: First row: initial values of the fields Second row: average of the same fields
Please help me...
select * from ( select HEM_LOKOSIT, HEM_NNS from LPMS.HEMOGRAMS where HEM_PATIENT_ID = 33 union select AVG(HEM_LOKOSIT), AVG(HEM_NNS) from LPMS.HEMOGRAMS where HEM_PATIENT_ID = 33) order by HEM_LOKOSIT desc nulls last;
I'm relatively new to SQL7 but I did use 6.5 a fair bit. I'm trying to test the restore of the Transaction log backup and having a bit of difficulty. The idea is that I make a complete database backup at 1am backup the transaction log every 30 minutes between 7am and 7pm. I need to be able to restore the database to a known state between 7am and 7pm with a max data loss of 30 minutes.
What I am trying to achieve is (as a test):
1)Create a small test database with a test table 2)Add some data to the test table 3)Back up the transaction log 4)Restore the transaction log to 'undo' the data added in step 2
Should be simple I think !!! The problem I am encountering is that in step 4 it won't let me restore only the transaction log (a tick automatically appears in the database backup as well). Bah !
Can someone please tell me what simple steps are required to get this to work. I need specifically on what options to chose during the backup and restore processes.
