Cursor Issue - Order Not Correct

Expressions Not Always Evaluated In Correct Order
Hello!
 
I have an SSIS package, run by the DTExec utility, each night. When I run my package, I assign a value to a variable (package scope) (/SET "Package.Variables[User:: psRunNo]";0154). (Note: all the variables here are strings).
 

Value for variable psRunNo = 0154
 
In the package, I have another package variable called S_SOURCE_FILE_NAME, which is an expression and that uses the variable psRunNo.
 

Expression for S_SOURCE_FILE_NAME = @[User:: psRunNo] + "_{TABLENAME}.txt"
 

So, at the beginning, the value of variable S_SOURCE_FILE_NAME is 0154_{TABLENAME}.txt with that example.
 
Then, I use the variable S_SOURCE_FILE_NAME in the expression used to assign the ConnectionString property of a Connection (a source file), like this:
 

REPLACE( @[User:: S_REJECTED_ROWS_FILE_NAME] ,"{TABLENAME}", "STADDRES")
 
So here for example, the final value for ConnectionString would be 0154_STADDRES.txt
 
Everything works nice, most of the time. Sometimes (intermittent problem), the value of the ConnectionString for one of the many Connections I have in the package is not assigned with the right value of psRunNo. The ConnectionString gets the value of psRunNo which is saved into the package (when it was deployed) instead of the value of psRunNo passed with the DTExec.
 
It is like if the ConnectionString value was computed before the variable psRunNo (and S_SOURCE_FILE_NAME expression) was assigned to the new value, but only for one of the connections (all my connections use the same kind of expression for their ConnectionString property).
 
Does somebody had similar precedence problem? Is there some settings I could use to indicate a precedence in assignation of variables ? Where does the "natural" precedence in assignation comes from in SSIS ??
 
Thanks!

View Replies !

---

Reporting Services Parameters Not In Correct Order
Hi,

SQL Server Reporting Services 2005.

I have 13 parameters, ordered correctly within the Report Parameter screen.

When displayed in the Preview tab they are all ordered correctly, but when viewed in the application the first 4 are at the top but ordered incorrectly.  The remainder are ordered correctly.

I have tried reordering, saving, deploying, viewing and then doing the same but in the correct order without any success.

Please can someone suggest how I can get the parameters to appear in the correct order within the application?

Thanking you,

dwemh

View Replies !

---

Sysdepends And Compiling Stored Procedures In The Correct Order
Hello:

We are presently testing various upgrade scripts to our current application which is scheduled to shortly be upgraded to mssql 7.0. We are testing under mssql 7.0,sp 2.

As it works now, I receive some existing scripts that have been modified and some stored proceures that are new. For the existing stored procedures, I usually take my best guess as to what the order of creation will be, test it in my script for recompile(actually all are dropped first and then the guesswork on the creates). This is usually trial and error as I run the script, see any sysdepends errors such as:

"CREATE PROCEDURE: ep_invoiceheaderformat_spv0101
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'ep_assumepay"

And then move the order of the create procedures around in the script and try again until I get a clean run in a test database I use just to syntactically test the scripts.

I looked at the sysdepends table for the database and pretty much decided that the object numbers and stuff was pretty much incomprehensible to me.


Alternatively I could compile each one separately but I would have the same problem subsequently trying to generate a script of the al of the create procedures... in the right order which would not

My question is:

1) Is there a way I can read and understand what the data means in sysdepends?

2) Figure out a way to utilize the data there to create or generate the create stored procedure text in the correct clean compile order?

3) Any other suggestions?

Any information which can be proveded will be greatly appreciated. Thanks.


David Spaisman

View Replies !

---

Report Builder - Filter Drop Down Order Not Always Correct
My Problem
=========
I have a problem with a parameter drop down that is on my report. On one report server (i.e. a SQL Server Reporting Server instance), the drop down data is ordered correctly. On another, it appears to be random.
 
The report is created using Report Builder v9.0.2047.0.
 
I have used SQL Server Profiler and I have found that an "order by" clause is appended on one of the report servers, while it is excluded on another.
 
About my Set-up
============
I have configured multiple report servers (i.e. SQL Server Reporting Server instances) on a single SQL Server 2005 instance. The SQL Server 2005 details are:
productversion = 9.00.3054.00
productlevel = SP2
edition = Standard Edition (64-bit)
 
 
Another post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1379591&SiteID=1) puts this problem down to SP2. However, both my report servers are on the same SQL server instance.
 
I also see that Bob has a blog on a possible solution for this: http://blogs.msdn.com/bobmeyers/archive/2007/10/11/sorting-the-values-in-parameter-dropdowns-in-report-builder.aspx.
However, I would like to understand why the report is behaving differently.

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 !

---

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 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 !

---

Issue With Cursor
Hi

I have created a cursor with the following syntax: "DECLARE costs_cursor CURSOR SCROLL DYNAMIC FOR SELECT RegNumber, FirstName, LastName, Assessment, Catering, Travel, Accommodation, Other from dbo.T_Course_Data ORDER BY LastName OPEN costs_cursor" which works.

What I don't understand is why, when I attempt to update a value in the cursor, irrespective of whether I use the 'FOR UPDATE' option or not, I get the error message to the effect that the cursor cannot be updated because it is READ ONLY.  Clearly (to my mind anyway) the cursor wasn't created as read only.  My update statement is "Update dbo.T_Course_Data set Assessment='222' WHERE CURRENT OF costs_cursor"

The odd thing is I have another cursor in my app using the exact same statements and it doesn't give this error.

Please help if you can.

Neil

View Replies !

---

Cursor Issue
Can someone tell me what's wrong with this cursor?

if @senddisputed=1
declare x cursor for select email from dbo.Users where UserType='A' and email is not null
open x
fetch next from x into @email
while @@fetch_status = 0
BEGIN
insert into emails (transno,reqid,efrom,eto,subject,body,notified,emailtype)
(select 0,0,@fromaddress,@email,'Disputed Transaction Notification',
'This Email is to alert you that you currently have '+ ltrim(str(count(distinct th.transno)))+ ' transactions in
resolve that are under the status of declined. Please log into the system and review these records.' + char(13),
null,8
from
transhdr th join cards c on c.cardid = th.cardid join users u on c.mgrid = u.userkey
join transdtl td on th.transno = td.transno
join recon r on th.transno = r.transno
where
u.email is not null
and th.reqid=1
group by
u.email)
fetch next from x into @email
END
close x
deallocate x

Results:
Msg 16916, Level 16, State 1, Procedure email_generate_general_is2, Line 212
A cursor with the name 'x' does not exist.
Msg 16916, Level 16, State 1, Procedure email_generate_general_is2, Line 215
A cursor with the name 'x' does not exist.
Msg 16916, Level 16, State 1, Procedure email_generate_general_is2, Line 234
A cursor with the name 'x' does not exist.
Msg 16916, Level 16, State 1, Procedure email_generate_general_is2, Line 235
A cursor with the name 'x' does not exist.


---
http://www.ssisdude.blogspot.com/

View Replies !

---

Order By Issue
I am having a small issue with the order by command.

Everytime I run the code below, everything displays in the correct order which was assigned by the order by command:

select *
from [table1]
order by column_a, column_b, column_c

My problem is that I want it to be stored in another table in the order that I specify, so I run the code below which has the same order:

select *
into [new table]
from [table1]
order by column_a, column_b, column_c


My problem is that when I do a select * form the new table that I inserted all the information into in order, it will not be returned in order. Why would that be? My understanding is that it would store the info in the new table by the way I specified on the order by line.

Any help would be appreciated. It is driving me crazy!

View Replies !

---

ORDER BY Issue.
Hi All,

If I use ORDER BY in Union query then it take lot of time .

My query looks like.

Select x,y,z
FROM(

SELECT x,y,z
FROM tt,yy,zz

UNION
SELECT x,y,z
FROM tt1,yy1,zz1
) A
WHERE a.x > '03/03/2004'
order by x

Union query return morethan 200000 records.

It's take lot of time around 20 sec if I removerd it then takes 2 sec.
I can put middle(union) part of query in view but I can put ORDER BY in query but I have to use TOP n.


Can I put any index on column in view or else.

Please suggest me asap.

View Replies !

---

Mdac 2.8 And Sql 2005 Cursor Performace Issue
HelloI have a VB6 application using classic ado (MDAC 2.8) for connectingms sql 2000 server. Application uses a lot of server side cursors. NowI want to switch to ms sql 2005 server but I have noticed very seriousperformance problem. Sql profiler results of execution of followingcommands:declare @p1 intset @p1=180150131declare @p3 intset @p3=1declare @p4 intset @p4=16388declare @p5 intset @p5=22221exec sp_cursoropen @p1 output,N' Select ... from ... where .... orderby ...',@p3 output,@p4 output,@p5 outputselect @p1, @p3, @p4, @p5on sql server 2000:CPU: 234Reads:82515Writes:136Duration:296and on sql server 2005:CPU: 4703Reads:678751Writes:1Duration:4867Both databases are identical, the servers runs on the same machine(Pentium 2,8 Ghz, 2 GB RAM) with only one client connected. On forumsI've read that Microsoft doesn't recommend using server side cursorson sql 2005 but is there any way to increase performance to someacceptable level?thanks in advanceszymon strus

View Replies !

---

Pass Linked ServerName To A Cursor Issue...
Need to loop through a Cursor to linked server:
-----------------------------------------------
Declare Cursor_Loop_serverName Cursor for
select cast(name as varchar(30)) name, cast(dbID as varchar(5)) dbID,
cast(crdate as varchar(25)) crdate
from ServerName_A.master.dbo.sysdatabases

***How could I pass @serverName to change the from to
from @RemoteServer.master.dbo.sysdatabases?
I have tried dynamic sql, it did not work after the Declare Cursor for...

thanks for the help
David

View Replies !

---

Solve Order Issue
 

Good Day
 
Is it possible to set the Solve Order of a calculated member ceated in ProClarity to default to 99 ?
 
 

Currently,  If I have a member created and the measures I am wanting to return are : sales and Sales % growth, then the Sales add up correctly for the member, but the % growth is incorrect. That is because the solve order is defaulting to 0, If I edit and change the solve order on the member to 99, then both measures work correctly.
 
Therefore, I am looking for  a way to set the solve order to automatically default to 99
 
Thanks for your help

View Replies !

---

Conducting A Radius Search Via Postcodes - Cursor Issue?
Hi, I’ve developed the following stored procedure to query a table of services following a user search. The user can specify a radius from a certain location(UK postcode) in which to search, and its with this Im having a few problems.

Here’s my method:

STEP1 - I first check the table of services and insert all services matching the specified criteria (regardless of location) into a temporary table (#tMatches).

STEP 2 - I get the postcode entered by the user and retrieve its co-ordinates.

STEP 3 – I open a cursor and loop through the records in #tMatches. For each record, I first retrieve their co-ordinates and then use a bit of Pythagoras to work out whether its location falls within the radius specified by the user.

STEP 4 – If the record falls outside the range specified by the user, I deleted from #tMatches.

STEP 5 – I bind the remaining (matching) records from the #tMatches to a dataset.

Now, I think that seems pretty simple, and the easiest way to achieve what I’m trying to do. How then when querying just 600 records am I finding it times out? I think I’ve pinned it down to the STEP 3 (cursor), but not being the SQL expert I’d like to be, I’ve no further ideas where I’m going wrong? Any ideas?

The following is an excerpt from the script - don't worry about the fact several parameters may not be declared.

Many thanks

======================================

--STEP 1
CREATE TABLE #tMatches
(
record_uid uniqueidentifier,
service_name varchar(500),
service_address varchar(500),
service_description varchar(500),
GRE integer,
GRN integer
)
BEGIN
INSERT INTO #tMatches
(record_uid,service_name,service_address,service_description,GRE,GRN)
SELECT service_loc_uid,title_return,service_address,service_overview,GRE,GRN FROM v_RecordsLive_short WHERE (title_return LIKE '%' + @lookingfor + '%' OR service_category LIKE '%' + @lookingfor + '%' OR service_overview LIKE '%' + @lookingfor + '%')
END

-STEP 2

DECLARE @UserCoordX integer
DECLARE @UserCoordY integer
--1) USE POSTCODE DATA FOR REGION ONLY
SELECT @UserCoordX = GRE,@UserCoordY = GRN FROM tPCD WHERE tPCD.PCD = @postcode

--STEP3

--open a cursor containing record ids with co-ordinates
DECLARE @record_uid uniqueidentifier
DECLARE @CoordX integer
DECLARE @CoordY integer
DECLARE @XLen integer
DECLARE @YLen integer
DECLARE @range real

DECLARE locs_cursor CURSOR FOR
SELECT record_uid,GRE,GRN FROM #tMatches

OPEN locs_cursor

FETCH NEXT FROM locs_cursor
INTO @record_uid,@CoordX,@CoordY

WHILE @@FETCH_STATUS = 0
BEGIN
--calculate range from entered postcode using pythagorus
SET @XLen = Abs(@UserCoordX - @CoordX)
SET @YLen = Abs(@UserCoordY - @CoordY)
SET @range = SQRT((@XLen * @XLen) + (@YLen * @YLen))

END

--convert range to miles
SET @range = COALESCE(@range,0)
IF (@range > 0)
SET @range = @range/160.9

--IF OUT OF RANGE, DELETE RECORD FROM TABLE STRAIGHTAWAY
IF @range > CAST(@miles as float)
DELETE FROM #tMatches WHERE record_uid = @record_uid

--get next record
FETCH NEXT FROM locs_cursor
INTO @record_uid,@CoordX,@CoordY

CLOSE locs_cursor
DEALLOCATE locs_cursor

--------------------RETURN ALL RESULTS-----------------------------------------------
SELECT record_uid,service_name,service_address,service_description FROM #tMatches

DROP Table #tMatches

View Replies !

---

Migartion Issue With ORDER BY Clause
Please help me how to write the following query in SQL Server 2005

SELECT 3,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
        CONVERT(char(10), fulldate, 126),
        CONVERT(char(8), fulldate, 108),
        flag = CASE Invalid WHEN 'Y' THEN 2 ELSE 1 END,
        srk
 FROM #raw
 ORDER BY tag, parent, 'Value!3!date' asc

View Replies !

---

Moving From 2000 To 2005: Issue Accessing Cursor Returned By Sp
I have a number of triggers that call a stored procedure that returns a cursor.  The triggers then use the results of this cursor to do other actions.

My problem is that this works fine in SQL2000 but just won't work in SQL2005.  When I try to access the results of the returned cursor, I get an error -2147217900 could not complete cursor operation because the set options have changed since the cursor was declared.

If I port the code contained in the sp into the trigger, it runs fine.   But having to port over the sp's code defeats the whole concept of being able to re-use the sp.

Does anybody have any ideas of what could be going on?

I look forward to a quick response.

Dennis

View Replies !

---

Mdac 2.8 And Sql 2005 Server Side Cursor Performace Issue
Hello

I have a VB6 application using classic ado (MDAC 2.8) for connecting ms sql 2000 server. Application uses a lot of server side cursors. Now I want to switch to ms sql 2005 server but I have noticed very serious performance problem. Sql profiler results of execution of following commands:

declare @p1 int
set @p1=180150131
declare @p3 int
set @p3=1
declare @p4 int
set @p4=16388
declare @p5 int
set @p5=22221
exec sp_cursoropen @p1 output,N' Select ... from ... where .... order by ...',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

on sql server 2000:

CPU:     234
Reads:    82515
Writes:    136
Duration:    296

and on sql server 2005:

CPU:     4703
Reads:    678751
Writes:    1
Duration:    4867

Both databases are identical, the servers runs on the same machine (Pentium 2,8 Ghz, 2 GB RAM) with only one client connected. On forums I've read that Microsoft doesn't recommend using server side cursors on sql 2005 but is there any way to increase performance to some acceptable level?

thanks in advance

szymon strus

View Replies !

---

2000 To 2005 SQL Statement Order By Issue
 

Hi,

  I have a simple sql statement that used to work in SQL 2000 that isn't working in SQL 2005. The order by clause doesn't seem to have any effect on the result set. The sql statement is:

 

ALTER  VIEW dbo.SELECT_PP_END
AS
SELECT     TOP 100 PERCENT

PP_PERIOD_ID,

CONVERT(VARCHAR, PP_END_DATE, 101) AS PP
FROM         dbo.PP_PERIODS
ORDER BY PP_END_DATE DESC

 

The period end date is appearing in ascinding order on sql server 2005 and in the correct order in sql 2000. Any idea? Thank you for your help

 

- T.A.

View Replies !

---

Sort Order Via Parameter-now Security Issue
we are firing this from my asp page, what i can't work out how to do is pass a sort order via parameter to my stored procedure.
Any Help appreciated

Nigel

set conn = CreateObject("ADODB.Connection")

conn.open MM_fc_conn_STRING

sql = "EXEC Sproc_01020_Select_WebUser" & " @UserLoginName='" & Request.Form("Username") & "'"

set Recordset1 = conn.execute(sql)

View Replies !

---

SQL Server 2005 Issue - The Cursor Type/concurrency Combination Is Not Supported.
Hi

 

I have recently upgraded from SQL 2000 to SQL 2005 and I'm getting the following problem, can you suggest me if this is a issue with SQL 2005 or suggest me an asnwer for this.

 Below is the exception from my log file

 The cursor type/concurrency combination is not supported.
com.microsoft.sqlserver.jdbc.SQLServerException: The cursor type/concurrency combination is not supported.
 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.<init>(Unknown Source)
 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.<init>(Unknown Source)
 at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(Unknown Source)

 

The following is the piece of code where the problem I'm assuming is happening, how can I correct it.

varStmt1 = varConnection.prepareStatement(varCitationSQL.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                                                ResultSet.CONCUR_UPDATABLE);

Have tried using both JDC v1.1 and 1.2 but of no use.

 

View Replies !

---

Query Issue Please Try To Correct The Query
Hi all,

I have written a query which is not producing the correct ouput.
Please help me to solve the query issue.

my query goes like this:-
 
SELECT     ResourceInformation.PreferredFirstName, ResourceInformation.PreferredLastName, ResourceInformation.CellPhone,
                      ResourceInformation.HomePhone, ResourceInformation.HomeAddressLine1, ResourceInformation.HomeAddressLine2,
                      ResourceInformation.HomeAddressState,ResourceInformation.HomeAddressCity,ResourceInformation.HomeAddressZIP,
                      ResourceInformation.HomeAddressCountry, EmploymentInformation.ArrangementType, EmploymentInformation.PracticeGroup, Projects.Recruiter,
                      Projects.AccountManager, Projects.TEPOApproved, Projects.BillRate, Projects.Expenses, Projects.ClientProjMgr,
                      Projects.ClientAddressLine1,Projects.ClientAddressLine2,Projects.ClientAddressCity,Projects.ClientAddressZIP,Projects.ClientAddressState,
                      Projects.ClientAddressCountry,Projects.LabourCategoryNotes, Projects.Client, Projects.EndClient, Projects.ProjectStartDate,
                      Projects.DrugScreenRequired, Projects.SkillSet, Projects.AribaProposal, Projects.Sourcer, Projects.ProjectEndDate, Subtier.SubtierCompany,
                      Subtier.SubtierContactName, Subtier.SubtierAddressLine1,Subtier.SubtierAddressLine2,Subtier.SubtierAddressCity,Subtier.SubtierAddressState,
                      Subtier.SubtierAddressZIP,Subtier.SubtierAddressCountry, Subtier.SubtierEmail, Subtier.SubtierPhone, Subtier.TaxID,
                      Subtier.SubtierVendorPassthrough, Subtier.ClientPassthrough, Compensation.PaymentTerms
FROM         ResourceInformation INNER JOIN
                      EmploymentInformation ON ResourceInformation.ResourceID = EmploymentInformation.ResourceID CROSS JOIN
                      Projects CROSS JOIN
                      Subtier CROSS JOIN
                      Compensation
WHERE     (Projects.EmploymentID = 23) AND (ResourceInformation.ResourceID = 23)

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 !

---

Variable Type For Fetcing The Cursor Record In Tsql Cursor
what is the equivalent of the %rowtype in oracle for the tsql

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 !

---

&"ORDER BY&" Issue
ASP .NET
SQL 2005

Trying to sort a search on products by GroupID in the MPDB_ItemTypes Table.


Public Shared Function GetFurniture(ByVal iCollectionID As Integer, ByVal iRoomGroupID As Integer, ByVal iItemTypeID As Integer, ByVal iStyleGroupID As Integer, ByVal iPhotoTypeID As Integer, ByVal sStyleNumber As String, ByVal sSearchText As String, ByVal iStartIndex As Integer, ByVal iPageSize As Integer, ByRef iVirtualItemCount As Integer) As HCDataset.SearchResultsDataTable
Dim cn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection
cn.ConnectionString = System.Configuration.ConfigurationManager.AppSetti ngs("ConnectionString")
cn.Open()

Dim iEndIndex As Integer = iStartIndex + iPageSize - 1 'we may override the iEndIndex below
If iPageSize > 0 Then
Dim cmdGetFurnitureCount As New System.Data.SqlClient.SqlCommand
cmdGetFurnitureCount.Connection = cn
cmdGetFurnitureCount.CommandType = Data.CommandType.Text
cmdGetFurnitureCount.CommandText = "SELECT COUNT(*) " & _
"FROM MPDB_Items I " & _
"INNER JOIN MPDB_Photos P ON I.PhotoID = P.PhotoID " & _
"INNER JOIN MPDB_Collections C ON I.CollectionID = C.CollectionID " & _
"LEFT OUTER JOIN MPDB_PhotoTypes T ON P.PhotoTypeID = T.PhotoTypeID " & _
"LEFT OUTER JOIN MPDB_StyleGroups S ON C.StyleGroupID = S.StyleGroupID " & _
"WHERE I.ShowOnWeb = 1 AND NOT I.FriendlyDescription IS NULL AND " & _
"((@CollectionID = 0) OR (C.CollectionID = @CollectionID)) AND " & _
"((@RoomGroupID = 0) OR (I.RoomGroupID = @RoomGroupID)) AND " & _
"((@StyleGroupID = 0) OR (C.StyleGroupID = @StyleGroupID)) AND " & _
"((@PhotoTypeID = 0) OR (P.PhotoTypeID = @PhotoTypeID)) AND " & _
"((@StyleNumber = '') OR (I.StyleNumber LIKE '%' + @StyleNumber + '%')) AND " & _
"(" & _
"(@SearchText = '') OR " & _
"((NOT EXISTS (SELECT TOP 1 * FROM Keywords WHERE UPPER(Keyword) = UPPER(@SearchText))) AND ((I.StyleNumber LIKE '%' + REPLACE(@SearchText, '-', '') + '%') OR (UPPER(I.FriendlyDescription) LIKE '%' + UPPER(@SearchText) + '%') OR (UPPER(I.Comments) LIKE '%' + UPPER(@SearchText) + '%'))) OR " & _
"( (EXISTS (SELECT TOP 1 * FROM Keywords WHERE UPPER(Keyword) = UPPER(@SearchText))) AND (I.ItemTypeID IN (SELECT ItemTypeID FROM Keywords_Relate_ItemTypes INNER JOIN Keywords ON Keywords.ID = Keywords_Relate_ItemTypes.KeywordID AND UPPER (Keywords.Keyword) = UPPER (@SearchText)))) " & _
") "
cmdGetFurnitureCount.Parameters.Add("@CollectionID", Data.SqlDbType.Int).Value = iCollectionID
cmdGetFurnitureCount.Parameters.Add("@RoomGroupID", Data.SqlDbType.Int).Value = iRoomGroupID
cmdGetFurnitureCount.Parameters.Add("@StyleGroupID", Data.SqlDbType.Int).Value = iStyleGroupID
cmdGetFurnitureCount.Parameters.Add("@PhotoTypeID", Data.SqlDbType.Int).Value = iPhotoTypeID
cmdGetFurnitureCount.Parameters.Add("@StyleNumber", Data.SqlDbType.VarChar).Value = IIf(Not sStyleNumber Is Nothing, sStyleNumber, "")
cmdGetFurnitureCount.Parameters.Add("@SearchText", Data.SqlDbType.VarChar).Value = IIf(Not sSearchText Is Nothing, sSearchText, "")
iVirtualItemCount = cmdGetFurnitureCount.ExecuteScalar
If iEndIndex > iVirtualItemCount Then
iPageSize = iPageSize - (iEndIndex - iVirtualItemCount)
iEndIndex = iVirtualItemCount
End If
End If

'Note that the "ORDER BY" uses a tricky way of determing if there are any alphabetical characters in the sSearchText by comparing the .ToUpper with the .ToLower
Dim cmdGetFurniture As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand
cmdGetFurniture.Connection = cn
cmdGetFurniture.CommandType = Data.CommandType.Text
cmdGetFurniture.CommandText = IIf(iPageSize > 0, "SELECT * FROM (SELECT TOP " & iPageSize & " * FROM (SELECT TOP " & iEndIndex, "SELECT ") & _
"I.ItemID AS ID, I.FriendlyDescription AS Description, I.StyleNumber, P.FileName, 1 AS Type " & _
"FROM MPDB_Items I " & _
"INNER JOIN MPDB_Photos P ON I.PhotoID = P.PhotoID " & _
"INNER JOIN MPDB_Collections C ON I.CollectionID = C.CollectionID " & _
"LEFT OUTER JOIN MPDB_PhotoTypes T ON P.PhotoTypeID = T.PhotoTypeID " & _
"LEFT OUTER JOIN MPDB_StyleGroups S ON C.StyleGroupID = S.StyleGroupID " & _
"WHERE I.ShowOnWeb = 1 AND NOT I.FriendlyDescription IS NULL AND " & _
"((@CollectionID = 0) OR (C.CollectionID = @CollectionID)) AND " & _
"((@RoomGroupID = 0) OR (I.RoomGroupID = @RoomGroupID)) AND " & _
"((@StyleGroupID = 0) OR (C.StyleGroupID = @StyleGroupID)) AND " & _
"((@PhotoTypeID = 0) OR (P.PhotoTypeID = @PhotoTypeID)) AND " & _
"((@StyleNumber = '') OR (I.StyleNumber LIKE '%' + @StyleNumber + '%')) AND " & _
"(" & _
"(@SearchText = '') OR " & _
"((NOT EXISTS (SELECT TOP 1 * FROM Keywords WHERE UPPER(Keyword) = UPPER(@SearchText))) AND ((I.StyleNumber LIKE '%' + REPLACE(@SearchText, '-', '') + '%') OR (UPPER(I.FriendlyDescription) LIKE '%' + UPPER(@SearchText) + '%') OR (UPPER(I.Comments) LIKE '%' + UPPER(@SearchText) + '%'))) OR " & _
"( (EXISTS (SELECT TOP 1 * FROM Keywords WHERE UPPER(Keyword) = UPPER(@SearchText))) AND (I.ItemTypeID IN (SELECT ItemTypeID FROM Keywords_Relate_ItemTypes INNER JOIN Keywords ON Keywords.ID = Keywords_Relate_ItemTypes.KeywordID AND UPPER (Keywords.Keyword) = UPPER (@SearchText)))) " & _
") " & _
"ORDER BY " & IIf(Not [String].IsNullOrEmpty(sSearchText) AndAlso sSearchText.ToUpper = sSearchText.ToLower, "I.StyleNumber", "I.FriendlyDescription") & _
IIf(iPageSize > 0, ") NoBottom ORDER BY " & IIf(Not [String].IsNullOrEmpty(sSearchText) AndAlso sSearchText.ToUpper = sSearchText.ToLower, "StyleNumber", "Description") & " DESC) NoTop ORDER BY " & IIf(Not [String].IsNullOrEmpty(sSearchText) AndAlso sSearchText.ToUpper = sSearchText.ToLower, "StyleNumber", "Description"), "")
cmdGetFurniture.Parameters.Add("@CollectionID", Data.SqlDbType.Int).Value = iCollectionID
cmdGetFurniture.Parameters.Add("@RoomGroupID", Data.SqlDbType.Int).Value = iRoomGroupID
cmdGetFurniture.Parameters.Add("@ItemTypeID", Data.SqlDbType.Int).Value = iItemTypeID
cmdGetFurniture.Parameters.Add("@StyleGroupID", Data.SqlDbType.Int).Value = iStyleGroupID
cmdGetFurniture.Parameters.Add("@PhotoTypeID", Data.SqlDbType.Int).Value = iPhotoTypeID
cmdGetFurniture.Parameters.Add("@StyleNumber", Data.SqlDbType.VarChar).Value = IIf(Not sStyleNumber Is Nothing, sStyleNumber, "")
cmdGetFurniture.Parameters.Add("@SearchText", Data.SqlDbType.VarChar).Value = IIf(Not sSearchText Is Nothing, sSearchText, "")

Dim daGetFurniture As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
daGetFurniture.SelectCommand = cmdGetFurniture

Dim dtSearchResults As HCDataset.SearchResultsDataTable = New HCDataset.SearchResultsDataTable
daGetFurniture.Fill(dtSearchResults)

If iPageSize <= 0 Then
iVirtualItemCount = dtSearchResults.Rows.Count
End If

cn.Close()

Return dtSearchResults
End Function


I want to sort the results by the GroupID field located in the MPDB_ItemTypes Table.

Example: Lets says that there are 4 items in the MPDB_Items table

Plate (ItemTypeID = 1 & GroupID = 1)
Glass (ItemTypeID = 3 & GroupID = 2)
Bowl (ItemTypeID = 4 & GroupID = 3)
Silverware (ItemTypeID = 2 & GroupID = 4)

Both Tables MPDB_Item & MPDB_ItemTypes have the field ItemTypeID (int). So, that is how they will be joined together.

What I need to do is if I search for a product # of 44 it will show all products that have 44 in the # (this is done and works fine). When I go to view the results it shows them in the ORDER BY GroupID.

So, I would see all Plates together, Glass together, Bowls together, and Silverware together. At the present time it ORDER BY is done via determing if there are any alphabetical characters in the sSearchText by comparing the .ToUpper with the .ToLower

Right now whatever I try I fail on and the site throws back compile errors and such. I am new to .NET and SQL but need this to work soon as possible. Any ideas would be appreciated.

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 !

---

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 !

---

SQL To Order Results In Predefined Order
I have a DB with items which can have lengths from 0 to 400 meter.In my resultset I want to show the items with length 1-400 meter and then the results with length 0 meterHow to build my SQL?

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 !

---

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 !

---

The Order Of Insertion Of Rows Into Destination Is Not Same As The Order Of Incoming Rows
Hi ,

i am dealing with around 14000 rows which need to be put into the sql destination.,But what i see is that the order of the rows in the desination is not the same as in the source,

However it is same for smaller number of rows.

Please help ...i want the order to be same.

 

View Replies !

---

Correct Me????
I've created C#.net program (behind code style). 
when I run it in Internet explorer, the following error occurs in IE window.
pls instruct me how to handle and correct this error.
And how to initialize the connectionstring...  Great thank!
 
 
Server Error in '/' Application.
--------------------------------------------------------------------------------
The ConnectionString property has not been initialized. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: The ConnectionString property has not been initialized.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  
Stack Trace:
 [InvalidOperationException: The ConnectionString property has not been initialized.]   System.Data.SqlClient.SqlConnection.Open() +809   CodeBox.BehindCode.getSubject() +80   CodeBox.BehindCode.Page_Load(Object sender, EventArgs e) +31   System.Web.UI.Control.OnLoad(EventArgs e) +67   System.Web.UI.Control.LoadRecursive() +29   System.Web.UI.Page.ProcessRequestMain() +724 
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.0.3705.0; ASP.NET Version:1.0.3705.0

View Replies !

---

Correct Use Of While
I have a simple while process to use with a trigger to insert values
into another table. IN VB this was simple but the while in TSQL seems
a little different. If anyone can point out my flaw greatly appreciated.

while @cnter < @nodays
--insert values
insert into table values (value1, value2)
--then increment counters and repeat
set @sdate = @sdate + 1
Set @cnter = @cnter + 1
How or what is the best way to loop back?

View Replies !

---

What Is Correct ?
If you start receiving continuous error messages by e-mail indicating that the transaction log is full. After 2 days, the messages suddenly stopped. What could be the reason?

Windows NT App log is full or SQL Server Agent stopped

I think SQL Server Agent stopped
How do you think..and why ?

thanks

View Replies !

---

What Is Correct ?!!!!^_^
If you start receiving continuous error messages by e-mail indicating that the transaction log is full. After 2 days, the messages suddenly stopped. What could be the reason?

Windows NT App log is full or SQL Server Agent stopped

I think SQL Server Agent stopped
How do you think..and why ?

thanks

View Replies !

---