Sorting Cursor Output WITHOUT Using Order By
Hi,
I have a situation where I need to sort the output of a cursor. But since the sort criteria are rather complex, I am NOT able to use the Order By clause directly with the cursor definition statement.
Hence, I need to have a solution where I will use a dummy (calculated) field within the CURSOR and I want the output of this cursor sorted by the dummy field that I calculated within the cursor itself.
Please let me know the different possibilities in this scenario.
Thank you in advance
Raj
View Complete Forum Thread with Replies
Related Forum Messages:
Dynamic Cursor - Sorting In Declaration
Hello everybody!I have a small table "ABC" like this:id_position | value---------------------------1 | 112 | 223 | 33I try to use a dynamic cursor as below.When the statement "order by id_position" in declare part of the cursor_abcis omitted - cursor work as it should.But when the statement "order by id_position" is used, cursor behave asstatic one.What's the matter, does anybody know?Code:declare @id_position as int, @value as intDECLARE cursor_abc CURSORFORselect id_position, value from abcorder by id_positionset nocount onopen cursor_abcFETCH NEXT FROM cursor_abcINTO @id_position, @valueWHILE @@FETCH_STATUS = 0BEGINprint @id_positionprint @valueprint '----------------------------'update abc set value=666 --next reading should give value=666FETCH NEXT FROM cursor_abcINTO @id_position, @valueENDCLOSE cursor_abcDEALLOCATE cursor_abcGORegardsLucas
View Replies !
Field Not Sorting In Ascending Order
Hi, I've created a website usiing asp.net and all the data are stored in sql front. All the item are sorted in ascending order except one record. The correct order should be MP61, MP100, MP200, but this record is retrieved as MP100, MP200, MP61. If the coding is wrong, all the order displayed is not in ascending order. We have hundreds of items, but why it happens to this particular record? Can anyone help? Thanks in advance
View Replies !
Sorting Date/timestamp Is Out Of Order
I have a problem with an Order By sort on a SubmissionDate column in my SQLSERVER DB. I am inputing a timestamp in this format into the column above: 1/18/2005 11:03:19 AM Problem is, once I sort this column in DESC order to return the results to a datalist dates with a time like this: 1/18/2005 1:03:19 AM get placed out of place (lower on the return in DESC/higher on the return in ASC). I am assuming this is happening because it reads 1 as coming before 11 instead of after like it is with time. If this was in 24 hour format this wouldn't be a problem I guess because 1PM would be 13, so that is after 11. Anyone know what I can do to get this sorted correctly?
View Replies !
Sorting The Data In Ascending Order .
Hi , Hopw can i sort the data in the ascending order so that i can see the date of my log files imported at the top of my table . Right now my log files get imported and appended in the tables but they get appended in the descending order at the bottom of the last data . Is there any way to sort the data so that i can see it in the reverse way when i open my tables ? Many Thanks . Anita.
View Replies !
Column Sorting In Ascending Order
Hi I have Constructed a table Table Name : MasterEntry Column Name: MasterEntryNumber ServiceName ServiceDepartment EmployeeName MasterEntryNumber is GenerationNumber where ever any entry is happen in table if put a queries [select * From masterentry Order by MasterentryNumber] is give output in ascending order e.g MasterEntryNumber has 1 2 3 it give correct ordering but if i exceed more 10 if i put same queries Output of queries is 1 10 11 2 3 this is the out of the queries if it exceed more 10 rows i want queries should be look like this 1 2 3 ...... 10 did any have experience on this issue please let me know where i can fix kinds regards
View Replies !
Sorting Matrix Column In Random Order
Hi, I have a matrix report...the column results are as follows Con Std , Con Access, SF Std, SF Acc, Broadband, Pay TV how would i make the columns appear in the above order when displaying as it is default alphetically sorted...I have tried putting numbers at the front which work till I get to the number 10 which alphetically sorted is next to 1 not 9? is there a better way off sorting matrix column which have no specific criteria to sort from? thanks
View Replies !
Cursor And Order By
I noticed that I cannot use ORDER BY in cursor, but in my case I need to order data in cursor. I'm thinking about selecting data for cursor from temp table where data is ordered but may be there are other decisions to get data in cursor ordered?
View Replies !
ORDER BY Have Problem In SP With Cursor
Hi, when I create a stored procedure with cursor, I got this problem: if I have ORDER BY in the code, I got below error message. It change to OK once I remove the ORDER BY.I tried to run the SELECT with ORDER BY in seperated statement, works just fine.I cannot understand what the error message mean, either google found nothing. What's the problem, or how I debug out the reason. Thank you very much. here is the code: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[SP_Lookup_A] ( @projId varchar(10) ) AS BEGIN -- Lot Number DECLARE rsLotnos CURSOR FOR (SELECT LOT_NUMBER FROM ASG_LOT WHERE PROJECT_ID = @projId ORDER BY LOT_NUMBER ) END here is error message: Msg 156, Level 15, State 1, Procedure SP_Lookup, Line 17 Incorrect syntax near the keyword 'ORDER'. Wes
View Replies !
Cursor Issue - Order Not Correct
I'm using a cursor in SQL Server 2000 to assist me in calculating for each store, the Sales Rank of a zip code. There are about 1500 stores, and 125,000 store/sales/zip code records. I am finding that this works for about 95% of the stores, but about 5% are getting fouled up, where the store's records are getting split in the sequencing, and so the store ends up with two zips ranked 1, two ranked 2, two ranked 3, etc. In the DB structure, there is a constraint restricting one record per store (org_id) per zip code (postalcode). Here's my code. Basically what I'm trying to have the cursor do is go through the table, ordered by org_id (store) asc, org_criteria_value (sales) desc, and rank the zip codes. When a new store is encountered, reset the counter to 1 and start ranking again. Do this until all the records are processed. /*CREATE Sales_Table table */ CREATE TABLE [dbo].[Sales_Table] ( [count_id] [int] NULL , [org_id] [int] NULL , [postalcode] [varchar] (20) NULL, [sales] [numeric](18,6) NULL , [sales_rank] [integer] NULL, [org_criteria_input_date] [datetime] NULL ) ON [PRIMARY] insert into Sales_Table select 0 as count_id, omd.org_id, omd.postalcode, omd.org_criteria_value, 0 as cum_rank, org_criteria_input_date fromorg_model_data omd join org o on o.org_id = omd.org_id where o.client_id = @ClientID and model_Criteria_id = 27 and org_criteria_value <> 0 order by omd.org_id asc, omd.org_criteria_value desc -- DECLARE CURSOR for Sales_Table declare SalesRankCursor CURSOR SCROLL dynamic FOR select org_id, sales, sales_rank from Sales_Table for update of sales_rank -- CREATE LOOP TO UPDATE SALES RANK in Sales_Table with valid values OPEN SalesRankCursor while exists (Select * from Sales_Table where sales_rank = 0) Begin FETCH NEXT FROM SalesRankCursor set @StoreNext = @StoreCurrent set @SalesRank = (@SalesRank + 1) update Sales_Table set@StoreCurrent = org_id where current of SalesRankCursor if @StoreCurrent <> @StoreNext begin set @SalesRank = 1 end update Sales_Table setsales_rank = @SalesRank where current of SalesRankCursor End CLOSE SalesRankCursor DEALLOCATE SalesRankCursor Any ideas?
View Replies !
Select Output With Cursor
In my previous post I asked how to do the bottom question. I got a response to use a cursor, now I made an attempt to use a cursor but I still get the same response. Any help will be greatly appreciated. --CURRENT OUTPUT-- empID Rank Skills ------- ---- -------------------------------------------------- 2924 1 Create Documents 2924 2 Mail Merge 2924 3 Create Header and footer 2924 3 Create Spreadsheet 2924 3 Joining Tables in a Query --DESIRED OUTPUT-- empID Rank Skills ------ ---- ------------ 2924 1 Create Documents 2924 2 Mail Merge 2924 3 Create Header and footer, Joining Tables in a Query, Create Spreadsheet --Here is the cursor script.-- Declare @skills varchar(255),@skills2 varchar(255),@message varchar(255),@empID varchar(255), @Rank varchar(255) DECLARE emp_skills CURSOR For select C.empID, B.Rank,B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and (B.Rank ='1'or B.Rank ='2' or B.Rank ='3') or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' DECLARE emp_skills2 CURSOR For select B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and (B.Rank ='1'or B.Rank ='2' or B.Rank ='3') or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' OPEN emp_skills OPEN emp_skills2 FETCH NEXT FROM emp_skills into @empID, @Rank, @skills FETCH NEXT FROM emp_skills2 into @skills2 WHILE @@FETCH_STATUS = 0 BEGIN SELECT @message = @skills2 FETCH NEXT FROM emp_skills2 into @skills2 Print @empID + ' '+ @Rank + ' ' + @message FETCH NEXT FROM emp_skills into @empID, @Rank, @skills End CLOSE emp_skills DEALLOCATE emp_skills CLOSE emp_skills2 DEALLOCATE emp_skills2 --Previous Post-- Another question for all you SQL experts, I have a lot of them. I am trying to select from a table wher some conditions need to be met based on an employee ID. What I am doing is when the rank is a 1,2, or 3 I pick up the text description of that rank. Can I make it so that I get the ID only once and all the text descriptions are on the same line. Here is the sql script along with my current output and my desired output. --SQL SCRIPT__ select C.empID, B.Rank,B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and (B.Rank ='1'or B.Rank ='2' or B.Rank ='3') --CURRENT OUTPUT-- empID Rank Skills ------- ---- -------------------------------------------------- 2924 1 Create Documents 2924 2 Mail Merge 2924 3 Create Header and footer 2924 3 Create Spreadsheet 2924 3 Joining Tables in a Query --DESIRED OUTPUT-- empID Rank Skills ------ ---- ------------ 2924 1 Create Documents 2924 2 Mail Merge 2924 3 Create Header and footer, Joining Tables in a Query, Create Spreadsheet
View Replies !
Using A Cursor Output From Stored Proc
Has anyone ever tried to use a cursor as an output variable to a stored proc ? I have the following stored proc - CREATE PROCEDURE dbo.myStoredProc @parentId integer, @outputCursor CURSOR VARYING OUTPUT AS BEGIN TRAN T1 DECLARE parent_cursor CURSOR STATIC FOR SELECT parentTable.childId, parentTable. parentValue FROM parentTable WHERE parentTable.parentId = @parentId OPEN parent_cursor SET @outputCursor = parent_cursor DECLARE @childId int DECLARE @parentValue varchar(50) FETCH NEXT FROM parent_cursor INTO @childId, @parentValue WHILE @@FETCH_STATUS = 0 BEGIN SELECT childTable.childValue FROM childTable WHERE childTable.childId = @childId FETCH NEXT FROM parent_cursor INTO @childId, @parentValue END CLOSE parent_cursor DEALLOCATE parent_cursor COMMIT TRAN T1 GOAnd, I found that I had to use a cursor as an output variable because, although the stored proc returns a separate result set for each returned row in the first SQL statement, it did not return the result set for the first SQL statement itself. My real problem at the moment though is that I can't figure a way to get at this output variable with VB.NET.Dim da as New SqlDataAdapter() da.SelectCommand = New SqlCommand("myStoredProc", conn) da.SelectCommand.CommandType = CommandType.StoredProcedure Dim paramParentId as SqlParameter = da.SelectCommand.Parameters.Add("@parentId", SqlDbType.Int) paramParentId.Value = 1 Dim paramCursor as SqlParameter = daThread.SelectCommand.Parameters.Add("@outputCursor") paramCursor.Direction = ParameterDirection.OutputThere is no SqlDataType for cursor. I tried without specifying a data type but it didn't work. Any ideas? Thanks Martin
View Replies !
How To Order The Output.
Hi!I have a database (Sql) which has a table in it named news_tbl, within this table I have a column called newsDate which is filled with a timestamp in the format dd/mm/yyyy 00:00:00. The date shows when the news item was created but the time is not used hence the reason its always 00:00:00. I have an archive page which allows the user to view the news articles by the month they were posted. I have an SQL statement just which looks like this: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:news2007aConnectionString %>" SelectCommand="Select Distinct LEFT(DATENAME(month, newsDate), 3) As TheMonth, DateName(month,newsDate) As TheFullMonth, DateName(year,newsDate) As TheYear, DatePart(month, newsDate) As TheOrder From news_tbl Order By TheMonth ASC, TheOrder DESC"> </asp:SqlDataSource> and the output just now is April 2008 December 2007 February 2008 January 2008 March 2008 November 2007 October 2007 September 2007 I would like the output to be the latest month first i.e. April 2008 then March 2008 then February 2008 and so on. I have tried a couple of different SQL statements but can't get the output to appear in the order I want. Can anyone suggest where I am going wrong. Thanks, O.
View Replies !
Output Of Select Stament Into Variable Within Cursor
Within a cursor that I am building I would like to execute a select statement built from a varchar variable as such: SELECT @BuildDBPageUsed = 'SELECT sum(reserved) FROM ' + @DatabaseName + '.dbo.sysindexes WHERE segment <> 2' Next I execute the statement, which is now in the variable @BuildDBPageUsed. Such as: EXEC (@BuildDBPageUsed) The resulting output I need to set to another variable @DBPageUsed which is integer. I have been unsuccessful in finding the correct set of commands to do this. How should I build the command to input the results of the EXEC (@BuildDBPageUsed) into the integer variable @DBPageUsed? *Thanks* for any help in this matter. Brad
View Replies !
Exporting Cursor's Output To Excel Spreadsheet
Hi, I have a this table.... CREATE TABLE RCSAdvantage (Resident_No int , FirstName varchar(50), LastName Varchar(50), DOB datetime, Tel char(10), source varchar(10)) Records.... INSERT INTO RCSAdvantage VALUES('123','Mike','Bhatt','12/12/2003','123','RCSA') INSERT INTO RCSAdvantage VALUES('TM123','Mike','Bhatt','12/12/2003','456','TRIMICRO') INSERT INTO RCSAdvantage VALUES('INR234','Mike','Bhatt','12/12/2003','890','INSIGHT') INSERT INTO RCSAdvantage VALUES('INR234','John','Bhatt','12/12/2003','890','INSIGHT') I needed to run following cursor and get the result exported to excel file. But Cursor retrives two resultset and while exporting to excel spreadsheet , it is the only first resultset without second resultset. How can it be exported to excel as a single resultset combined of first and second one. /* Suppress counts from being displayed*/ SET NOCOUNT ON /*Declare the variables */ DECLARE @cnt int,@FirstName varchar(15),@LastName varchar(15),@DOB datetime /*Declare Cursor */ DECLARE RCSA_C CURSOR LOCAL FOR SELECT COUNT(*), FirstName, LastName, DOB FROM RCSAdvantage GROUP BY FirstName,LastName,DOB HAVING (COUNT(*)>1) ORDER BY FirstName /*Open the cursor */ OPEN RCSA_C /*Get the resultset from the first row of the cursor*/ FETCH NEXT FROM RCSA_C INTO @cnt,@FirstName,@LastName,@DOB WHILE @@fetch_status=0 BEGIN SELECT * FROM RCSAdvantage WHERE FirstName=@FirstName AND LastName=@LastName AND DOB=@DOB /*Get the next row */ FETCH NEXT FROM RCSA_C INTO @cnt,@FirstName,@LastName,@DOB END /*Close the cursor */ DEALLOCATE RCSA_C --SELECT * FROM RCSAdvantage Please help
View Replies !
Order By Clause In DECLARE CURSOR Select Statement Won't Compile
The stored procedure, below, results in this error when I try to compile... Msg 156, Level 15, State 1, Procedure InsertImportedReportData, Line 69 Incorrect syntax near the keyword 'ORDER'. However the select statement itself runs perfectly well as a query, no errors. The T-SQL manual says you can't use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in a cursor select statement, but nothing about plain old ORDER BYs. What gives with this? Thanks in advance R. The code: Code Snippet -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF object_id('InsertImportedReportData ') IS NOT NULL DROP PROCEDURE InsertImportedReportData GO -- ============================================= -- Author: ----- -- Create date: -- Description: inserts imported records, marking as duplicates if possible -- ============================================= CREATE PROCEDURE InsertImportedReportData -- Add the parameters for the stored procedure here @importedReportID int, @authCode varchar(12) AS BEGIN DECLARE @errmsg VARCHAR(80); -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; --IF (@authCode <> 'TX-TEC') --BEGIN -- SET @errmsg = 'Unsupported reporting format:' + @authCode -- RAISERROR(@errmsg, 11, 1); --END DECLARE srcRecsCursor CURSOR LOCAL FOR (SELECT ImportedRecordID ,ImportedReportID ,AuthorityCode ,[ID] ,[Field1] AS RecordType ,[Field2] AS FormType ,[Field3] AS ItemID ,[Field4] AS EntityCode ,[Field5] AS LastName ,[Field6] AS FirstMiddleNames ,[Field7] AS Title ,[Field8] AS Suffix ,[Field9] AS AddressLine1 ,[Field10] AS AddressLine2 ,[Field11] AS City ,[Field12] AS [State] ,[Field13] AS ZipFull ,[Field14] AS OutOfStatePAC ,[Field15] AS FecID ,[Field16] AS Date ,[Field17] AS Amount ,[Field18] AS [Description] ,[Field19] AS Employer ,[Field20] AS Occupation ,[Field21] AS AttorneyJob ,[Field22] AS SpouseEmployer ,[Field23] As ChildParentEmployer1 ,[Field24] AS ChildParentEmployer2 ,[Field25] AS InKindTravel ,[Field26] AS TravellerLastName ,[Field27] AS TravellerFirstMiddleNames ,[Field28] AS TravellerTitle ,[Field29] AS TravellerSuffix ,[Field30] AS TravelMode ,[Field31] As DptCity ,[Field32] AS DptDate ,[Field33] AS ArvCity ,[Field34] AS ArvDate ,[Field35] AS TravelPurpose ,[Field36] AS TravelRecordBackReference FROM ImportedNativeRecords WHERE ImportedReportID IS NOT NULL AND ReportType IN ('RCPT','PLDG') ORDER BY ImportedRecordID -- this should work but gives syntax error! ); END
View Replies !
Indicating Sort Order In Printed Output
I can see the built-in interactive sort arrows on my report columns when I view the report on a monitor but the sort arrows do not appear in the printed output. Is there a way to get the built-in interactive sort arrows to appear in the printed output? Thanks for your help.
View Replies !
Stored Procedure With CURSOR OUTPUT Parameter, Using JDBC And A Callable Statement
My server is MS Sql Server 2005. I'm using com.microsoft.sqlserver.jdbc.SQLServerDriver as the driver class. I've established a connection to the database. I'm trying to invoke a stored procedure using JDBC and a callable statement. The stored procedure has a parameter @CurOut CURSOR VARYING OUTPUT. How do I setup the callable statement so the output parameter is accepted by the driver? I'm not really trying to pass a cursor up to the database Server but I'm wanting a cursor back from the stored procedure that is other than the result set or other value the stored procedure returns. First problem: What java.sql.Types (or SQL Server specific) value do I specify for the out parameter I'm registering on the CallableStatement? Second problem: What do I set the value of the parameter to? The code looks like: CallableStatement cstmt = myConnection.prepareCall(sQuery); cstmt.registerOutParameter(1, Types.OTHER); // What is the right type? cstmt.setNull(1, Types.OTHER); // What is the right type? if (cstmt.execute()) { ResultSet rs = cstmt.getResultSet(); } Execution results in a NullPointerException from the driver. What am I doing wrong? Thanks for your assistance. Jon Weaver
View Replies !
Records From Internal Table Not Output In Order To Flat File
Hi - I have a SSIS package that dumps data from an internal table to a flat file output using standard data flow tasks. The entire table is output - no special SQL. Most of the time the records are placed in the output file in the same order as the internal DB table, but occasionally the order appears to be more random. When that happens, the record order in the internal table is correct - it is just the output. I can find no properties that seem to affect this. I would appreciate any hints and advice that anyone can give me. Has anyone else encountered this same problem? Thanks in advance, Jim Sweeney
View Replies !
Sorting And Grouping Question By Allowing Users To Select The Sorting Field
I have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info. So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not. Any suggestions would help. Thanks
View Replies !
Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin
View Replies !
Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker I get the following error can you please explain this problem to me so I can over come the issue. Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31 Could not complete cursor operation because the set options have changed since the cursor was declared. Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32 Could not complete cursor operation because the set options have changed since the cursor was declared. Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153 Cursor is not open. here is the stored procedure: Alter PROCEDURE [dbo].[sp_MSforeachsp] @command1 nvarchar(2000) , @replacechar nchar(1) = N'?' , @command2 nvarchar(2000) = null , @command3 nvarchar(2000) = null , @whereand nvarchar(2000) = null , @precommand nvarchar(2000) = null , @postcommand nvarchar(2000) = null AS /* This procedure belongs in the "master" database so it is acessible to all databases */ /* This proc returns one or more rows for each stored procedure */ /* @precommand and @postcommand may be used to force a single result set via a temp table. */ declare @retval int if (@precommand is not null) EXECUTE(@precommand) /* Create the select */ EXECUTE(N'declare hCForEachTable cursor global for SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ''PROCEDURE'' AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 ' + @whereand) select @retval = @@error if (@retval = 0) EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0 if (@retval = 0 and @postcommand is not null) EXECUTE(@postcommand) RETURN @retval GO example useage: EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]" GO
View Replies !
Express Will Not Load. Insurmountable Difficulties With Order Of Uninstalls/order Of Installs/ Suggestions Plz
Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following... SQL Server Express edition Visual Studios 2005 Jet 4.0 newest upgrade .Net Framework 2.0 (or should I use 3.0) VS2005 Security upgrade Anything else I need for just creating a database for my VS2005 Visual Basic project? I was trying to use MS Access as my backend db but would like to try SQL Express Thank you, Mark
View Replies !
Default Sort Order - Open Table - Select Without Order By
Hi! I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order. The table returns the data in the same order in SQL Manager "Open Table" So I started to wonder what deterimins the sort order when there is no order by clause ? I researched this for a bit but found no straight answers. My table has no PK, but an identiy column. Peace. /P
View Replies !
How To Add Order Item Into A Purchase Order Using A Stored Procedure/Trigger?
Hey guys, i need to find out how can i add order items under a Purchase Order number. My table relationship is PurchaseOrder ->PurchaseOrderItem. below is a Stored Procedure that i have wrote in creating a PO: CREATE PROC spCreatePO (@SupplierID SmallInt, @date datetime, @POno SmallInt OUTPUT) AS BEGIN INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID) END SET @POno = @@IDENTITY RETURN However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem? CREATE TRIGGER trgInsertPOItem ON PurchaseOrderItem FOR INSERT AS BEGIN 'What do i entered???' END RETURN help is needed asap! thanks!
View Replies !
Join Cursor With Table Outside Of Cursor
part 1 Declare @SQLCMD varchar(5000) DECLARE @DBNAME VARCHAR (5000) DECLARE DBCur CURSOR FOR SELECT U_OB_DB FROM [@OB_TB04_COMPDATA] OPEN DBCur FETCH NEXT FROM DBCur INTO @DBNAME WHILE @@FETCH_STATUS = 0 BEGIN SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' + + 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' + + 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL' EXEC(@SQLCMD) PRINT @SQLCMD FETCH NEXT FROM DBCur INTO @DBNAME END CLOSE DBCur DEALLOCATE DBCur Part 2 SELECT T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE, SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE, SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT INTO #MAIN FROM ORDR T0 JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE WHERE ISNULL(T0.U_OB_TID,0) <> 0 GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS my question is, how to join the part 1 n part 2? is there posibility?
View Replies !
Output And Error Output Write The Same Table At The Same Time, Stall The Process.
Hi I have Lookup task to determine if source data should be updated to or insert to the customer table. After Lookup task, the Error Output pipeline will redirect to insert new data to the table and the Output pipeline will update customer table. But these two tasks will be processing at the same time which causes stall on the process. Never end..... The job is similiart to what Slow Changing Dimention does but it won't update the table at the same time. What can I do to avoid such situation? Thanks in advance, JD
View Replies !
Find Order By Date Range Or Order Id
hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @Date_ordered OR @Date_ordered IS NULL) AND ([Date_ordered] <= @Date_ordered2 OR @Date_ordered2 IS NULL OR (Order_ID=ISNULL(@OrderID_ID,Order_ID) OR @Order_ID IS NULL))"> but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please! Thanks Jez
View Replies !
Recordset's Order And Database's Physical Order?
Hi,guys!I have a table below:CREATE TABLE rsccategory(categoryid NUMERIC(2) IDENTITY(1,1),categoryname VARCHAR(20) NOT NULL,PRIMARY KEY(categoryid))Then I do:INSERT rsccategory(categoryname) VALUES('url')INSERT rsccategory(categoryname) VALUES('document')INSERT rsccategory(categoryname) VALUES('book')INSERT rsccategory(categoryname) VALUES('software')INSERT rsccategory(categoryname) VALUES('casus')INSERT rsccategory(categoryname) VALUES('project')INSERT rsccategory(categoryname) VALUES('disert')Then SELECT * FROM rsccategory in ,I can get a recordeset with the'categoryid' in order(1,2,3,4,5,6,7)But If I change the table definition this way:categoryname VARCHAR(20) NOT NULL UNIQUE,The select result is in this order (3,5,7,2,6,4,1),and 'categoryname 'in alphabetic.Q:why the recordset's order is not the same as the first time since'categoryid' is clustered indexed.If I change the table definition again:categoryname VARCHAR(20) NOT NULL UNIQUE CLUSTEREDthe result is the same as the first time.Q:'categoryname' is clustered indexed this time,why isn't in alphabeticorder?I am a newbie in ms-sqlserver,or actually in database,and I do havesought for the answer for some time,but more confused,Thanks for yourkind help in advance!
View Replies !
Default Sort Order When Order By Column Value Are All The Same
Hi, We got a problem. supposing we have a table like this: CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times) select top 5 * from a order by aName Result is: 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde select top 10 * from a order by aName Result is: 11Bank of abcde 10Bank of abcde 9Bank of abcde 8Bank of abcde 7Bank of abcde 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek: Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot. So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
View Replies !
Default Sort Order When The Order By Column Value Are All The Same
Hi, We got a problem. supposing we have a table like this: CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times) select top 5 * from a order by aName Result is: 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde select top 10 * from a order by aName Result is: 11 Bank of abcde 10 Bank of abcde 9 Bank of abcde 8 Bank of abcde 7 Bank of abcde 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot. So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
View Replies !
Inconsistent Sort Order Using ORDER BY Clause
I am getting the resultset sorted differently if I use a column number in the ORDER BY clause instead of a column name. Product: Microsoft SQL Server Express Edition Version: 9.00.1399.06 Server Collation: SQL_Latin1_General_CP1_CI_AS for example, create table test_sort ( description varchar(75) ); insert into test_sort values('Non-A'); insert into test_sort values('Non-O'); insert into test_sort values('Noni'); insert into test_sort values('Nons'); then execute the following selects: select * from test_sort order by cast( 1 as nvarchar(75)); select * from test_sort order by cast( description as nvarchar(75)); Resultset1 ---------- Non-A Non-O Noni Nons Resultset2 ---------- Non-A Noni Non-O Nons Any ideas?
View Replies !
Order By Clause In View Doesn't Order.
I have created view by jaoining two table and have order by clause. The sql generated is as follows SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.* FROM dbo.UWYearDetail INNER JOIN dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag, dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth If I run sql the results are displayed in proper order but the view only order by first item in order by clause. Has somebody experience same thing? How to fix this issue? Thanks,
View Replies !
Using Output From A Stored Procedure As An Output Column In The OLE DB Command Transformation
I am working on an OLAP modeled database. I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff). I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails. The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output. What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column? I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it. Thanks in advance for any assistance you can provide.
View Replies !
Query Produces Jumbled Output / Output Not In Sequence
Hi!Server info -Win2K3 Server +SP1 with 1 GB Memory and 1.5 GB Virtual MemorySQL Server 2000 Enterprise Edition + SP3 running on this.Required result -Create a SQL Script that will contain a set of create, update, insert& delete statements (about 17500 lines including blank lines) thatcan be run on different databases seperatelyHow we do this -We have a SP - that creates a temporary table and then calls anotherSP that actually populates the temporary table created by the first SP*Samples of both SPs are below -PROBLEMThe result is directed to a file -However when the query is run it runs through the entire script but'Jumbles' the outputRunning the same scripts on a copy of the database on other machineswork fine and the size of the outfiles is exactly the sameI have increased the page size to 2.5 GB and restarted the server.Running the sp now generated the correct output a few times but gotjumbled as before after a few more users logged in and activity on theserver increased.Another interesting point is that the output is jumbled exactly thesame way each time. It seems the sql executes correctly and writesthe output in chunks only writting the chunks out of sequence - butin the same sequence each time.e.g. of expected resultInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table3Values ................Update RefGenSet Last = 1234Where RefGenRef = 1JUMBLED OUTPUTInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table2Values ...Values ...Update RefGenSet Last = 1234Where RefGenRef = 1Insert into Table3Values ................Insert into Table1Values c, d, e, 21, 12....Insert into Table2----------------------------------------Sample of First Script - STATDATA_RSLT**************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOSET NOCOUNT ONGOCREATE PROCEDURE StatData_rsltASCREATE TABLE #tbl_Script(ScriptText varchar(4000))EXEC TestStatData_intSELECT t.ScriptTextFROM #tbl_Script tGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************************Sample of CALLED SP - TestStatData_int*******************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOCREATE PROCEDURE TestStatData_intASDECLARE @AttrRef int,@TestID int,@PrtTestRef int,@AttrType tinyint,@EdtblSw tinyint,@NmValRef int,@SrtTypeRef int,@AttrStr varchar(20),@TestStr varchar(20),@PrtTestStr varchar(20),@AttrTypeStr varchar(20),@EdtblStr varchar(20),@NmValStr varchar(20),@SrtTypeStr varchar(20),@TestRef int,@Seq int,@PrtRef int,@Value varchar(255),@TermDate datetime,@AttrID int,@DefSw tinyint,@WantSw tinyint,@TestRefStr varchar(20),@SeqStr varchar(20),@PrtStr varchar(20),@TermDateStr varchar(255),@AttrIDStr varchar(20),@DefStr varchar(20),@WantStr varchar(20),@LanRef int,@LanStr varchar(20),@Code varchar(20),@Desc varchar(255),@MultiCode varchar(20),@MultiDesc varchar(255),@InhSw tinyint,@InhStr varchar(20),@InhFrom int,@InhFromStr varchar(20),@Lan_TestRef int,@ActSw tinyint,@ActSwStr varchar(20)SELECT @Lan_TestRef = dbo.fn_GetTestRef('Lan')INSERT INTO #tbl_ScriptVALUES('')-- Create tablesINSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_Test (AttrRef int, TestID int , PrtTestRefint, AttrType tinyint, EdtblSw tinyint, NmValRef int, SrtTypeRefint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES('CREATE TABLE #tbl_TestAttr(AttrRef int, TestRef int, Seq int,PrtRef int, AttrType tinyint, Value varchar(255), TermDate datetime,AttrID int, DefSw tinyint, WantSw tinyint, ActSw tinyint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_AttrName(AttrRef int, LanRef int, Codevarchar(20), [Desc] varchar(255), MultiCode varchar(20), MultiDescvarchar(255), InhSw tinyint, InhFrom int)')INSERT INTO #tbl_ScriptVALUES ('')-- insert Test valuesDECLARE Test_cursor CURSOR FORSELECT l.AttrRef, l.TestID, l.PrtTestRef, l.AttrType, l.EdtblSw,l.NmValRef, l.SrtTypeRefFROM Test lOPEN Test_cursorFETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestStr = ISNULL(CAST(@TestID as varchar), 'NULL'),@PrtTestStr = ISNULL(CAST(@PrtTestRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@EdtblStr = ISNULL(CAST(@EdtblSw as varchar), 'NULL'),@NmValStr = ISNULL(CAST(@NmValRef as varchar), 'NULL'),@SrtTypeStr = ISNULL(CAST(@SrtTypeRef as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_Test(AttrRef, TestID, PrtTestRef,AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('VALUES ( ' + @AttrStr + ', ' + @TestStr + ', ' +@PrtTestStr+ ', ' + @AttrTypeStr + ', ' + @EdtblStr + ', ' + @NmValStr + ', ' +@SrtTypeStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefENDCLOSE Test_cursorDEALLOCATE Test_cursorDECLARE TestAttr_cursor CURSOR FORSELECT le.AttrRef, le.TestRef, le.Seq, le.PrtRef, le.AttrType,le.Value,le.TermDate, le.AttrID, le.DefSw, le.WantSw, le.ActSwFROM TestAttr leWHERE le.WantSw = 1AND le.ActSw = 1OPEN TestAttr_cursorFETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestRefStr = ISNULL(CAST(@TestRef as varchar), 'NULL'),@SeqStr = ISNULL(CAST(@Seq as varchar), 'NULL'),@PrtStr = ISNULL(CAST(@PrtRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@Value = ISNULL(@Value, 'NULL'),@TermDateStr = ISNULL(CAST(@TermDate as varchar), 'NULL'),@AttrIDStr = ISNULL(CAST(@AttrID as varchar), 'NULL'),@DefStr = ISNULL(CAST(@DefSw as varchar), 'NULL'),@WantStr = ISNULL(CAST(@WantSw as varchar), 'NULL'),@ActSwStr = ISNULL(CAST(@ActSw as varchar), '1')SELECT @Value = '''' + @Value + ''''WHERE @Value <> 'NULL'INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_TestAttr(AttrRef, TestRef, Seq, PrtRef,AttrType, Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @TestRefStr + ', ' +@SeqStr+ ', ' + @PrtStr + ', ' + @AttrTypeStr + ', ' + @Value + ', ' +@TermDateStr + ', ' + @AttrIDStr + ', ' + @DefStr + ', ' + @WantStr+', '+ @ActSwStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwENDCLOSE TestAttr_cursorDEALLOCATE TestAttr_cursorDECLARE AttrName_cursor CURSOR FORSELECT e.AttrRef, e.LanRef, e.Code, e.[Desc], e.MultiCode,e.MultiDesc, e.InhSw, e.InhFromFROM AttrName e, TestAttr leWHERE e.LanRef = 0AND e.AttrRef = le.AttrRefAND le.WantSw = 1AND le.ActSw = 1OPEN AttrName_cursorFETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@LanStr = ISNULL(CAST(@LanRef as varchar), 'NULL'),@Code = ISNULL(@Code, 'NULL'),@Desc = ISNULL(@Desc, 'NULL'),@MultiCode = ISNULL(@MultiCode, 'NULL'),@MultiDesc = ISNULL(@MultiDesc, 'NULL'),@InhStr = ISNULL(CAST(@InhSw as varchar), 'NULL'),@InhFromStr = ISNULL(CAST(@InhFrom as varchar), 'NULL')SELECT @Code = REPLACE(@Code, '''',''''''),@Desc = REPLACE(@Desc, '''','''''') ,@MultiCode = REPLACE(@MultiCode, '''','''''') ,@MultiDesc = REPLACE(@MultiDesc, '''','''''')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_AttrName(AttrRef, LanRef, Code, [Desc],MultiCode, MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @LanStr + ', ''' + @Code +''', ''' + @Desc + ''', ''' + @MultiCode + ''', ''' + @MultiDesc +''',' + @InhStr + ', ' + @InhFromStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromENDCLOSE AttrName_cursorDEALLOCATE AttrName_cursor-- Do update TestAttr dataINSERT INTO #tbl_ScriptVALUES ('UPDATE le')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' le.TestRef = t.TestRef,')INSERT INTO #tbl_ScriptVALUES (' le.PrtRef = t.PrtRef,')INSERT INTO #tbl_ScriptVALUES (' le.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' le.Value = t.Value,')INSERT INTO #tbl_ScriptVALUES (' le.TermDate = t.TermDate,')INSERT INTO #tbl_ScriptVALUES (' le.AttrID = t.AttrID,')INSERT INTO #tbl_ScriptVALUES (' le.DefSw = t.DefSw,')INSERT INTO #tbl_ScriptVALUES (' le.WantSw = t.WantSw,')INSERT INTO #tbl_ScriptVALUES (' le.ActSw = t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM TestAttr le, #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('WHERE le.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Update AttrNameINSERT INTO #tbl_ScriptVALUES ('UPDATE en')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' en.Code = te.Code,')INSERT INTO #tbl_ScriptVALUES (' en.[Desc] = te.[Desc],')INSERT INTO #tbl_ScriptVALUES (' en.MultiCode = te.MultiCode,')INSERT INTO #tbl_ScriptVALUES (' en.MultiDesc = te.MultiDesc,')INSERT INTO #tbl_ScriptVALUES (' en.InhSw = te.InhSw,')INSERT INTO #tbl_ScriptVALUES (' en.InhFrom = te.InhFrom')INSERT INTO #tbl_ScriptVALUES ('FROM AttrName en, #tbl_AttrName te')INSERT INTO #tbl_ScriptVALUES ('WHERE en.AttrRef = te.AttrRef')INSERT INTO #tbl_ScriptVALUES (' AND en.LanRef = te.LanRef')INSERT INTO #tbl_ScriptVALUES (' AND te.LanRef = 0')-- Do update Test the dataINSERT INTO #tbl_ScriptVALUES ('UPDATE l')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' l.TestID = t.TestID,')INSERT INTO #tbl_ScriptVALUES (' l.PrtTestRef = t.PrtTestRef,')INSERT INTO #tbl_ScriptVALUES (' l.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' l.EdtblSw = t.EdtblSw,')INSERT INTO #tbl_ScriptVALUES (' l.NmValRef = t.NmValRef')INSERT INTO #tbl_ScriptVALUES ('FROM Test l, #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('WHERE l.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')--DELETE where just updatedINSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t, Test l')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = l.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM te')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE te.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Insert TestAttrINSERT INTO #tbl_ScriptVALUES ('INSERT INTO TestAttr (AttrRef, TestRef, Seq, PrtRef,AttrType,Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestRef, t.Seq, t.PrtRef, t.AttrType,t.Value, t.TermDate, t.AttrID, t.DefSw, t.WantSw, t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('')-- AttrNameINSERT INTO #tbl_ScriptVALUES ('INSERT INTO AttrName(AttrRef, LanRef, Code, [Desc],MultiCode,MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('SELECT te.AttrRef, le.AttrRef, te.Code, te.[Desc],te.MultiCode, te.MultiDesc, ')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN 0')INSERT INTO #tbl_ScriptVALUES (' ELSE 1 END,')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN NULL')INSERT INTO #tbl_ScriptVALUES (' ELSE 0 END')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE le.TestRef = ' + CAST(@Lan_TestRef as varchar))INSERT INTO #tbl_ScriptVALUES ('')-- Insert new rowsINSERT INTO #tbl_ScriptVALUES ('INSERT INTO Test(AttrRef, TestID, PrtTestRef, AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestID, t.PrtTestRef, t.AttrType,t.EdtblSw, t.NmValRef, t.SrtTypeRef')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_Test')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_TestAttr')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_AttrName')-- Update RefGenDECLARE @RefGenReflast int,@RefGenRefStr varchar(10)SELECT @RefGenReflast = lastFROM RefGenWHERE RefGenRef = 1SELECT @RefGenRefStr = ISNULL(CAST(@RefGenReflast as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES('')INSERT INTO #tbl_ScriptVALUES('UPDATE RefGen')INSERT INTO #tbl_ScriptVALUES ('SET Last = ' + @RefGenRefStr)INSERT INTO #tbl_ScriptVALUES ('WHERE RefGenRef = 1')INSERT INTO #tbl_ScriptVALUES ('')GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************RegardsGlenn
View Replies !
Specify Order For Select Results, Order By: Help!
Lets say I have a table named [Leadership] and I want to select the field 'leadershipName' from the [Leadership] Table. My query would look something like this: Select leadershipName From Leadership Now, I would like to order the results of this query... but I don't want to simply order them by ASC or DESC. Instead, I need to order them as follows: Executive Board Members, Delegates, Grievance Chairs, and Negotiators My question: Can this be done through MS SQL or do I need to add a field to my [Leadership] table named 'leadershipImportance' or something as an integer to denote the level of importance of the position so that I can order on that value ASC or DESC? Thanks, Zoop
View Replies !
Order ID For Latest Order For Every Customer
Hi! For the Orders table (let's assume for the Northwind database), I'm trying to get the order id of the latest order for every customer. That means that the result should be one record per customer and that would display CustomerID and OrderID. Any ideas? Thanks, Assaf
View Replies !
In-Order/Level Order Etc. Traversal Using CTE
Hi, I have some hierarchical data in a table. Say for example: Parent Child ------------------------ NULL 1 1 2 1 3 2 4 2 5 3 6 3 7 5 8 5 9 7 10 7 11 11 12 11 13 Now I want to be able to use CTE's to be able to traverse this tree in 1) level by level order 1,2,3,4,5,6,7,8,9,10.... 2) in order 1,2,4,5,8,9,3,6,7,10,11,12,13... What would be the aueries for this. Using the following i get: 1,2,3,6,7,10,11,12,13,4,5,8,9 (interesting and potentially useful) but I would like to be able to experiment with the aforementioned orders as well. with Tree (id) as ( select id from WithTest where parent is null union all select a.id from Tree b join WithTest a on b.id = a.parent ) select * from Tree Any ideas? Thanks.
View Replies !
Cursor Inside A Cursor
I'm new to cursors, and I'm not sure what's wrong with this code, it run for ever and when I stop it I get cursor open errors declare Q cursor for select systudentid from satrans declare @id int open Q fetch next from Q into @id while @@fetch_status = 0 begin declare c cursor for Select b.ssn, SaTrans.SyStudentID, satrans.date, satrans.type, SaTrans.SyCampusID, Amount = Case SaTrans.Type When 'P' Then SaTrans.Amount * -1 When 'C' Then SaTrans.Amount * -1 Else SaTrans.Amount END From SaTrans , systudent b where satrans.systudentid = b.systudentid and satrans.systudentid = @id declare @arbalance money, @type varchar, @ssn varchar, @amount money, @systudentid int, @transdate datetime, @sycampusid int, @before money set @arbalance = 0 open c fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount while @@fetch_status = 0 begin set @arbalance = @arbalance + @amount set @before = @arbalance -@amount insert c2000_utility1..tempbalhistory1 select @systudentid systudentid, @sycampusid sycampusid, @transdate transdate, @amount amount, @type type, @arbalance Arbalance, @before BeforeBalance where( convert (int,@amount) <= -50 or @amount * -1 > @before * .02) and @type = 'P' fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount end close c deallocate c fetch next from Q into @id end close Q deallocate Q select * from c2000_utility1..tempbalhistory1 truncate table c2000_utility1..tempbalhistory1
View Replies !
Number Of ROWS Of &&"Output Of Aggregate Transformation&&" Sometimes Doesn't Match The Output From T-SQL Query
While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS. For example,the output of the Aggregate Transformation may be 960216 ,but the 'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*. I'm sure the Group By of the Aggregate Transformation is right! But ,when I set the "keyscale" property of the transformation,the results match! In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation. Thanks for your advice.
View Replies !
Screen Output Vs Print Output
I would like the screen output for my reports to have the same page breaks as the print output, is this possible?? For example, I have a long detail report that our managers like to print out, however they only want to print a section of the report. So they sort on a certain column (ie. current balance) and then they find the range that they want and hit the print button, but they specify a certain page range based on page that they see on the screen. The problem is that the printed pages have different page breaks so they get the wrong information. Has anyone been able to match up the page breaks on the screen with what is printed?? I have suggested sorting then exporting to PDF or Excel but it seems that is not a viable solution for the allmighty managers. Hopefully someone can help me out, i can provide more detail as needed.
View Replies !
Client Side Cursor Vs Sever Side Cursor?
I having a difficult time here trying to figure out what to do here.I need a way to scroll through a recordset and display the resultswith both forward and backward movement on a web page(PHP usingADO/COM)..I know that if I use a client side cursor all the records get shovedto the client everytime that stored procedure is executed..if thisdatabase grows big wont that be an issue?..I know that I can set up a server side cursor that will only send therecord I need to the front end but..Ive been reading around and a lot of people have been saying never touse a server side cursor because of peformance issues.So i guess im weighing network performance needs with the client sidecursor vs server performance with the server side cursor..I am reallyconfused..which one should I use?-Jim
View Replies !
How To Load A Unicode File Into The Database In The Same Order As The File Order
The data file is a simple Unicode file with lines of text. BCPapparently doesn't guarantee this ordering, and neither does theimport tool. I want to be able to load the data either sequentially oradd line numbering to large Unicode file (1 million lines). I don'twant to deal with another programming language if possible and Iwonder if there's a trick in SQL Server to get this accomplished.Thanks for any help.Mark Leary----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups---= East/West-Coast Server Farms - Total Privacy via Encryption =---
View Replies !
Sorting Twice?!
here's a good one for you...I want to return the last 20 records I have modified. I have adatemodified field - excellent.So I run a query to select the top 20 when ordered by datemodifieddesc.But now I have these results I want them sorted by companyname.Is this possible?Yes I could use my GUI to do the second sort, but can it be done justin a query?ThanksTim
View Replies !
|
TRACKER
