Incorrect syntax near '?'

Hello. When I run my application (it's used to place orders) in VB.net I get the following message: "Failed to complete order! Reason: Incorrect syntax near 'champnr'. Incorrect syntax near '?'. At System.Data.Sqlclient.Sqlcommand.ExecuteReader(CommandBehaviour cmdBehaviour, Runbehavior runbehavior, Boolean returnStream)
Here is a part of the code:
Me.NewOrder.CommandText = "INSERT INTO TTOrder (beskrivning, bestallarnr, bestdatum, bolagsnr, champnr, " & _
"costcenter, doknr, doktypnr, ordernr, projekt, timenr, ttkommentar, volvokomment" & _
"ar) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
Me.NewOrder.Connection = Me.sqlOrderDb
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("beskrivning", System.Data.SqlDbType.NVarChar, 0, "beskrivning"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("bestallarnr", System.Data.SqlDbType.Int, 0, "bestallarnr"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("bestdatum", System.Data.SqlDbType.DateTime, 0, "bestdatum"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("bolagsnr", System.Data.SqlDbType.Int, 0, "bolagsnr"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("champnr", System.Data.SqlDbType.NVarChar, 255, "champnr"))
Any ideas? I'm not using any stored procedures. Maybe I should?
Hi,

Me.NewOrder.CommandText = "INSERT INTO TTOrder (beskrivning, bestallarnr, bestdatum, bolagsnr, champnr, " & _
"costcenter, doknr, doktypnr, ordernr, projekt, timenr, ttkommentar, volvokomment" & _
"ar) VALUES (@.beskrivning, @.bestallarnr, @.bestdatum, @.bolagsnr, @.champnr, @.costcenter, @.doknr, @.doktypnr, @.ordernr, @.projekt, @.timenr, @.ttkommentar, @.volvokomment)

Me.NewOrder.Connection = Me.sqlOrderDb
Me.NewOrder.Parameters.Add("@.beskrivning", System.Data.SqlDbType.NVarChar)

and so on...

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Incorrect syntax near ''?''

Hello. When I run my application (it's used to place orders) in VB.net I get the following message: "Failed to complete order! Reason: Incorrect syntax near 'champnr'. Incorrect syntax near '?'. At System.Data.Sqlclient.Sqlcommand.ExecuteReader(CommandBehaviour cmdBehaviour, Runbehavior runbehavior, Boolean returnStream)
Here is a part of the code:
Me.NewOrder.CommandText = "INSERT INTO TTOrder (beskrivning, bestallarnr, bestdatum, bolagsnr, champnr, " & _
"costcenter, doknr, doktypnr, ordernr, projekt, timenr, ttkommentar, volvokomment" & _
"ar) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
Me.NewOrder.Connection = Me.sqlOrderDb
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("beskrivning", System.Data.SqlDbType.NVarChar, 0, "beskrivning"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("bestallarnr", System.Data.SqlDbType.Int, 0, "bestallarnr"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("bestdatum", System.Data.SqlDbType.DateTime, 0, "bestdatum"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("bolagsnr", System.Data.SqlDbType.Int, 0, "bolagsnr"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("champnr", System.Data.SqlDbType.NVarChar, 255, "champnr"))
Any ideas? I'm not using any stored procedures. Maybe I should?
Hi,

Me.NewOrder.CommandText = "INSERT INTO TTOrder (beskrivning, bestallarnr, bestdatum, bolagsnr, champnr, " & _
"costcenter, doknr, doktypnr, ordernr, projekt, timenr, ttkommentar, volvokomment" & _
"ar) VALUES (@.beskrivning, @.bestallarnr, @.bestdatum, @.bolagsnr, @.champnr, @.costcenter, @.doknr, @.doktypnr, @.ordernr, @.projekt, @.timenr, @.ttkommentar, @.volvokomment)

Me.NewOrder.Connection = Me.sqlOrderDb
Me.NewOrder.Parameters.Add("@.beskrivning", System.Data.SqlDbType.NVarChar)

and so on...

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

This post was very helpful to me, thanks

Incorrect syntax

I'm getting a pop up error "Incorrect syntax near the keyword 'ORDER'.

Can someone please tell me what is wrong with my code? It worked before I added the underlined part.

If FoundChecked =TrueThen

Dim SQLStringAsString

SQLString ="SELECT ID, Bedrooms, Bathrooms, Location, Rent FROM ListingsTable WHERE "

ForEach ItemIn LocationList.Items

If Item.SelectedThen

SQLString &=" Location = '" & Item.Value &"' OR " &""&" ORDER BY Location" -------This is the problem

EndIf

The sql is going to look something like this (which is wrong):

SELECT ID, Bedrooms, Bathrooms, Location, RentFROM ListingsTableWHERE Location ='Item1'ORORDER BY LocationLocation ='Item2'ORORDER BY Location

|||I believe the problem is with the OR before ORDER BY. OR should be used if you want to say something like Location = "5" OR Location = "10" ORDER BY Location.|||

SGWellens:

The sql is going to look something like this (which is wrong):

SELECT ID, Bedrooms, Bathrooms, Location, RentFROM ListingsTableWHERE Location ='Item1'ORORDER BY LocationLocation ='Item2'ORORDER BY Location

What do you suggest?

I know this works:

SQLString &=" Location = '" & Item.Value &"' OR " &""
 
but I would like to have the ORDER added in somehow. Thanks.

|||

Either of these two patterns will work:

SELECT *FROM CustomerswhereCity ='London'OR City ='Bern'OR City ='Paris'ORDER BY CitySELECT *FROM CustomerswhereCityin ('London','Bern','Paris')ORDER BY City

|||

SGWellens:

Either of these two patterns will work:

SELECT *FROM CustomerswhereCity ='London'OR City ='Bern'OR City ='Paris'ORDER BY CitySELECT *FROM CustomerswhereCityin ('London','Bern','Paris')ORDER BY City

I'm sorry, but that isn't helping me. I need to know how to changemy code which is:

SQLString &=" Location = '" & Item.Value &"' OR " &""

to incorporate ORDER BY Location.

|||

You said it works before you added the ORDER BY Location to the end of the string.

Go ahead and run the For Loop that works and just after the For Loop, after NEXT, rebuild the string.

I use parameterized statements so I haven't concatenated statements for a while. Any way, tack the "ORDER BY Location" on the end of SQLString after the Loop is finished.

You'll have to clean up the syntax (comments). Because I've probaly got it wrong.

For blah
IF blah Then
SQLString = "blah"
End If
Next

SQLString ="SQLString" &"ORDER BY Location"

|||

Hi prk72,

You cannot use ORDER BY that way in your query clause. The solution has been given bySGWellens

Either of these two patterns will work:

SELECT *FROM CustomerswhereCity ='London'OR City ='Bern'OR City ='Paris'ORDER BY CitySELECT *FROM CustomerswhereCityin ('London','Bern','Paris')ORDER BY City

You can modify your code based on the solutionSGWellens has suggested you (Actually our community memberhypercode has already told you how to modify you code)

CODE EXAMPLE:

 
If FoundChecked = True Then Dim SQLString As StringSQLString ="SELECT ID, Bedrooms, Bathrooms, Location, Rent FROM ListingsTable WHERE " For Each Item In LocationList.Items If Item.Selected ThenSQLString &=" Location = '" & Item.Value &"'OR" End If
 
SQLString & =" 1=2 ORDER BY Location"
 

|||

You can use,

Dim SQLStringAs StringSQLString ="SELECT ID, Bedrooms, Bathrooms, Location, Rent FROM ListingsTable WHERE "For Each ItemIn LocationList.ItemsIf Item.SelectedThen SQLString &=" Location ='" & Item.Value & "' OR "End IfSqlString &= " 1=2 ORDER BY Location"

or

Dim SQLStringAs StringSQLString ="SELECT ID, Bedrooms, Bathrooms, Location, Rent FROM ListingsTable WHERE Location in ("For Each ItemIn LocationList.ItemsIf Item.SelectedThen SQLString &="'" & Item.Value & "', "End IfSqlString &= "'') ORDER BY Location"

Incorrect processing order for views

I am using SQL 2005 merge replication with SP1 hotfix build 9.00.2227.00. This build is in use rather than SP2 because a fix I need is not yet available for SP2

Essentially the problem is as follows:

1) Initial state is that merge replication of table and views is working fine

2) I then alter one view which references a new view in the same publication

3) Synchronization processes the view scripts in the wrong order regardless of the processing order

4) An 'invalid object name' error results as the new view has not arrived at the subscriber when alteration of the first view is attempted

The number suffixes on the script filenames in the snapshot folder do, however,appear to be numbered correctly so as to process in the correct order

Note that I have tried using the default processing order and have also set the processing order explicitly using sp_changemergearticle - but the problem still occurs

I have tried to recreate this problem on a small database with a minimum of articles, but attempts at repro have failed to date with a simple configuration - ie the processing order applied is correct

Is there an known problem in this area?

Any suggestions would be much appreciated

aero1

Note

1) Dependency information is up to date and accurate on the publisher

2) Creating new view manually on the subscriber allows sync to complete successfully - but this isn't an option for ongoing system updates with large numbers of subscribers

3) I am not clear whether the SQL for creating the new view is ever reaching the subscriber. I will test for this with profiler

|||

While this is not a fix for your problem it might help you out for a while.

You can schedule a script to run before the snapshot is extracted onto the subscriber. In the script you could create your troublesome view. That way it will automatically create the view everytime someone is added. Not the solution but maybe a workaround.

Martin

|||

Hi Martin

Thanks for your suggestion. Similarly to what you suggest - I am looking at ways of introducing a generic mechanism in our system to provide a means of running scripts that replication should have run. As some subscribers are SQL Express this can't be an agent job

I did look at using the pre-snapshot script for this purpose - but this script is only run when initializing

The case I have cited is just one instance of the type of ordering problems that may be encountered - so I can't just code for this specific case

Note that I created the new view manually on a test system and profiled the subsequent merge (which was successful). As anticipated all the 'alters' were run first and then the 'creates' came along afterwards. The processing order applied was correct within the 'alters' and correct within the 'creates'. However, the processing order needs to be applied across the whole set of 'alters' and 'creates' together

aero1

|||I don't konw that merge replication has great dependency checks like tran replication does, but for this scenario, it's not uncommon to separate out the user procs/views/functions/etc into a separate snapshot or tran publication and refresh it periodically (or on a daily schedule) when you know you've made changes.|||

Hi Greg

I have tested as per your suggestion - i.e. I created a separate merge publication for all the non schema articles (stored procs, view and functions) - and recreated the other publications without those articles.

All appeared to be going well until I attempted to alter a replicated stored proc (on the publisher). I attempted altering a number of stored procs, but each time the alter hung - and I could see that the alter SPID had clocked up up to 20 minutes of CPU each time. I checked this behaviour on my original configuration and the alters took about 4 seconds.

I like the simplicity of a separate publication, but need to get over this issue to make it workable.

I don't know if it is relevant, but the database had 3 merge publications previously and now has four with the new publication for the non schema articles.

The 3 publications were setup for the following reasons

- To get over the 256 article limit problem

- To use separate publications for subscribers needing filtered and unfiltered data

Any thoughts would be much appreciated

Thanks

aero1

|||

I have found that the problem altering procs is a known issue covered in this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=417921&SiteID=1

I added a dummy table to the publication and am now able to alter published procs successfully

aero1

Incorrect processing order for views

I am using SQL 2005 merge replication with SP1 hotfix build 9.00.2227.00. This build is in use rather than SP2 because a fix I need is not yet available for SP2

Essentially the problem is as follows:

1) Initial state is that merge replication of table and views is working fine

2) I then alter one view which references a new view in the same publication

3) Synchronization processes the view scripts in the wrong order regardless of the processing order

4) An 'invalid object name' error results as the new view has not arrived at the subscriber when alteration of the first view is attempted

The number suffixes on the script filenames in the snapshot folder do, however,appear to be numbered correctly so as to process in the correct order

Note that I have tried using the default processing order and have also set the processing order explicitly using sp_changemergearticle - but the problem still occurs

I have tried to recreate this problem on a small database with a minimum of articles, but attempts at repro have failed to date with a simple configuration - ie the processing order applied is correct

Is there an known problem in this area?

Any suggestions would be much appreciated

aero1

Note

1) Dependency information is up to date and accurate on the publisher

2) Creating new view manually on the subscriber allows sync to complete successfully - but this isn't an option for ongoing system updates with large numbers of subscribers

3) I am not clear whether the SQL for creating the new view is ever reaching the subscriber. I will test for this with profiler

|||

While this is not a fix for your problem it might help you out for a while.

You can schedule a script to run before the snapshot is extracted onto the subscriber. In the script you could create your troublesome view. That way it will automatically create the view everytime someone is added. Not the solution but maybe a workaround.

Martin

|||

Hi Martin

Thanks for your suggestion. Similarly to what you suggest - I am looking at ways of introducing a generic mechanism in our system to provide a means of running scripts that replication should have run. As some subscribers are SQL Express this can't be an agent job

I did look at using the pre-snapshot script for this purpose - but this script is only run when initializing

The case I have cited is just one instance of the type of ordering problems that may be encountered - so I can't just code for this specific case

Note that I created the new view manually on a test system and profiled the subsequent merge (which was successful). As anticipated all the 'alters' were run first and then the 'creates' came along afterwards. The processing order applied was correct within the 'alters' and correct within the 'creates'. However, the processing order needs to be applied across the whole set of 'alters' and 'creates' together

aero1

|||I don't konw that merge replication has great dependency checks like tran replication does, but for this scenario, it's not uncommon to separate out the user procs/views/functions/etc into a separate snapshot or tran publication and refresh it periodically (or on a daily schedule) when you know you've made changes.|||

Hi Greg

I have tested as per your suggestion - i.e. I created a separate merge publication for all the non schema articles (stored procs, view and functions) - and recreated the other publications without those articles.

All appeared to be going well until I attempted to alter a replicated stored proc (on the publisher). I attempted altering a number of stored procs, but each time the alter hung - and I could see that the alter SPID had clocked up up to 20 minutes of CPU each time. I checked this behaviour on my original configuration and the alters took about 4 seconds.

I like the simplicity of a separate publication, but need to get over this issue to make it workable.

I don't know if it is relevant, but the database had 3 merge publications previously and now has four with the new publication for the non schema articles.

The 3 publications were setup for the following reasons

- To get over the 256 article limit problem

- To use separate publications for subscribers needing filtered and unfiltered data

Any thoughts would be much appreciated

Thanks

aero1

|||

I have found that the problem altering procs is a known issue covered in this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=417921&SiteID=1

I added a dummy table to the publication and am now able to alter published procs successfully

aero1

Incorrect processing order for views

I am using SQL 2005 merge replication with SP1 hotfix build 9.00.2227.00. This build is in use rather than SP2 because a fix I need is not yet available for SP2

Essentially the problem is as follows:

1) Initial state is that merge replication of table and views is working fine

2) I then alter one view which references a new view in the same publication

3) Synchronization processes the view scripts in the wrong order regardless of the processing order

4) An 'invalid object name' error results as the new view has not arrived at the subscriber when alteration of the first view is attempted

The number suffixes on the script filenames in the snapshot folder do, however,appear to be numbered correctly so as to process in the correct order

Note that I have tried using the default processing order and have also set the processing order explicitly using sp_changemergearticle - but the problem still occurs

I have tried to recreate this problem on a small database with a minimum of articles, but attempts at repro have failed to date with a simple configuration - ie the processing order applied is correct

Is there an known problem in this area?

Any suggestions would be much appreciated

aero1

Note

1) Dependency information is up to date and accurate on the publisher

2) Creating new view manually on the subscriber allows sync to complete successfully - but this isn't an option for ongoing system updates with large numbers of subscribers

3) I am not clear whether the SQL for creating the new view is ever reaching the subscriber. I will test for this with profiler

|||

While this is not a fix for your problem it might help you out for a while.

You can schedule a script to run before the snapshot is extracted onto the subscriber. In the script you could create your troublesome view. That way it will automatically create the view everytime someone is added. Not the solution but maybe a workaround.

Martin

|||

Hi Martin

Thanks for your suggestion. Similarly to what you suggest - I am looking at ways of introducing a generic mechanism in our system to provide a means of running scripts that replication should have run. As some subscribers are SQL Express this can't be an agent job

I did look at using the pre-snapshot script for this purpose - but this script is only run when initializing

The case I have cited is just one instance of the type of ordering problems that may be encountered - so I can't just code for this specific case

Note that I created the new view manually on a test system and profiled the subsequent merge (which was successful). As anticipated all the 'alters' were run first and then the 'creates' came along afterwards. The processing order applied was correct within the 'alters' and correct within the 'creates'. However, the processing order needs to be applied across the whole set of 'alters' and 'creates' together

aero1

|||I don't konw that merge replication has great dependency checks like tran replication does, but for this scenario, it's not uncommon to separate out the user procs/views/functions/etc into a separate snapshot or tran publication and refresh it periodically (or on a daily schedule) when you know you've made changes.|||

Hi Greg

I have tested as per your suggestion - i.e. I created a separate merge publication for all the non schema articles (stored procs, view and functions) - and recreated the other publications without those articles.

All appeared to be going well until I attempted to alter a replicated stored proc (on the publisher). I attempted altering a number of stored procs, but each time the alter hung - and I could see that the alter SPID had clocked up up to 20 minutes of CPU each time. I checked this behaviour on my original configuration and the alters took about 4 seconds.

I like the simplicity of a separate publication, but need to get over this issue to make it workable.

I don't know if it is relevant, but the database had 3 merge publications previously and now has four with the new publication for the non schema articles.

The 3 publications were setup for the following reasons

- To get over the 256 article limit problem

- To use separate publications for subscribers needing filtered and unfiltered data

Any thoughts would be much appreciated

Thanks

aero1

|||

I have found that the problem altering procs is a known issue covered in this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=417921&SiteID=1

I added a dummy table to the publication and am now able to alter published procs successfully

aero1

Incorrect parameter order - Months

Hi to everyone,
Sorry if this has been asked before but I couldn't see anything similar from a search of the forums.
I'm trying to filter reports using two parameters set up in the data tab of report builder in VS 2005. I want to filter on month and year, and when I set these filters up in the data tab VS creates the two report parameters for me. When the report is previewed or deployed, the year parameter drop-down list is in the correct order, but the month parameter drop-down list is in alphabetical order (April, August, December, etc).
I have set the time dimension up, with the hierarchy year>month>date. Is there any way to force the months into their correct order? I have created parameters of my own to do this, but the ability to select more than one item with the VS-generated parameters would really enhance the reports I am building.
If anyone can help or post links to resources that'd be great - Many thanks.

If you don't mind having all months appear all the time, you can open the report in VS Report Designer and change the parameter's ValidValues property from a query to a static list of values in the correct order. The report may not re-open in Report Builder afterwards, or if it does, the ValidValues property will be reset to a query, so be aware of that.

Incorrect parameter order - Months

Hi to everyone,
Sorry if this has been asked before but I couldn't see anything similar from a search of the forums.
I'm trying to filter reports using two parameters set up in the data tab of report builder in VS 2005. I want to filter on month and year, and when I set these filters up in the data tab VS creates the two report parameters for me. When the report is previewed or deployed, the year parameter drop-down list is in the correct order, but the month parameter drop-down list is in alphabetical order (April, August, December, etc).
I have set the time dimension up, with the hierarchy year>month>date. Is there any way to force the months into their correct order? I have created parameters of my own to do this, but the ability to select more than one item with the VS-generated parameters would really enhance the reports I am building.
If anyone can help or post links to resources that'd be great - Many thanks.

If you don't mind having all months appear all the time, you can open the report in VS Report Designer and change the parameter's ValidValues property from a query to a static list of values in the correct order. The report may not re-open in Report Builder afterwards, or if it does, the ValidValues property will be reset to a query, so be aware of that.

Incorrect Order in rendering report

Hi,

I have this problem on Reporting Services 2005 SP2:

There is a stored procedure that is the source of a dataset in report, this procedure return a recordset ordered by some fileds (es. order by fields1, fields2, ecc...). This procedure also have some parameters, but this isn't important.

If I launch the stored procedure in sql server management studio the data are returned in the correct order, instead, when I run the report, the data are showed in wrong order.

Some one have informations about this issue?

Kind Regards,

Elia.

Did you try sorting in the report? If so doesn't it still sort in the order that you selected.

If not try sorting in the report, within table properties you will see sorting within which you can specify the sort order

|||

Thanks,

I have resolved the problem.

Regards,

Elia.

Incorrect column order in syscolumns / Information_schema.columns

When dropping a column, syscolumns keeps the old ordinal
for the following columns. Is this correct / expected
behavior?
This also affects Information_schema.Columns. See example
below.
Is there any SUPPORTED way to refresh or update this info?
Regards
Hans Schyldt
Drop Table HS_Test
go
CREATE TABLE [dbo].[HS_Test] (
[C1] [int] IDENTITY (1, 1) NOT NULL ,
[C2] [int] NOT NULL ,
[C3] [varchar] (10) NOT NULL ,
[C4] [varchar] (10) NOT NULL) ON [PRIMARY]
go
select
T1.Table_Name, T1.Column_Name, T1.Ordinal_Position
from Information_Schema.Columns as T1
where T1.Table_Name = 'HS_Test'
order by T1.Ordinal_Position
go
alter table HS_Test
Drop Column C2
go
select
T1.Table_Name, T1.Column_Name, T1.Ordinal_Position
from Information_Schema.Columns as T1
where T1.Table_Name = 'HS_Test'
order by T1.Ordinal_Position
go
Drop Table HS_Test
goThis is correct behavior, but can be annoying.
Lot of people will argue that order and ordinal numbers do not matter, but
they matter to me.
What I did to correct this, was making a change with the Enterprise Manager
and reversing that change. Using such a change that the enterprise manager
rebuilds the whole table. In this way my ordinals where again as they were
in other databases. (So I could compare the databases again). This worked
fast enough so I didn't look for 'simpler' commands which only changed
metadata.
ben brugman
"Hans Schyldt" <hans.reply_to_forum.schyldt@.intentia.se> wrote in message
news:0e4701c367e7$70a16ec0$a101280a@.phx.gbl...
> When dropping a column, syscolumns keeps the old ordinal
> for the following columns. Is this correct / expected
> behavior?
> This also affects Information_schema.Columns. See example
> below.
> Is there any SUPPORTED way to refresh or update this info?
> Regards
> Hans Schyldt
>
> Drop Table HS_Test
> go
> CREATE TABLE [dbo].[HS_Test] (
> [C1] [int] IDENTITY (1, 1) NOT NULL ,
> [C2] [int] NOT NULL ,
> [C3] [varchar] (10) NOT NULL ,
> [C4] [varchar] (10) NOT NULL) ON [PRIMARY]
> go
> select
> T1.Table_Name, T1.Column_Name, T1.Ordinal_Position
> from Information_Schema.Columns as T1
> where T1.Table_Name = 'HS_Test'
> order by T1.Ordinal_Position
> go
> alter table HS_Test
> Drop Column C2
> go
> select
> T1.Table_Name, T1.Column_Name, T1.Ordinal_Position
> from Information_Schema.Columns as T1
> where T1.Table_Name = 'HS_Test'
> order by T1.Ordinal_Position
> go
> Drop Table HS_Test
> go

Inconsistent UDF column order

I have a UDF that when I run by two different users, gives two different
orders of data columns. One follows the syntax of the function and the othe
r
is mis-ordered but returns this way consistently. Has anyone else
encountered this?Can you post the ddl?
AMB
"ZachB" wrote:

> I have a UDF that when I run by two different users, gives two different
> orders of data columns. One follows the syntax of the function and the ot
her
> is mis-ordered but returns this way consistently. Has anyone else
> encountered this?|||Not sure what you mean by posting the ddl but here's the syntax of the UDF:
CREATE FUNCTION dbo. MISMOqryMINRegistrationCldDateNE(@.Enter_
Begin_Date
datetime,
@.Enter_End_Date datetime)
RETURNS TABLE
AS
RETURN ( SELECT TOP 100 PERCENT dbo.dbo_Tracking_File_Ext.f755#MERS,
dbo.MERS.MERS_MINNumber AS [MISMO MERS], dbo.tblLoanDetails.FirstSecond,
dbo.dbo_Tracking_File_Ext.f422#ClosingDate AS [MV Note
Date], dbo.tblLoanInfo.ClsdTDDate AS NoteDate,
dbo.LOAN_DETAILS.ClosingDate AS [MISMO ClosingDate],
dbo.tblLoanDetails.[Loan Amount],
dbo.LOAN_DETAILS.DisbursementDate AS [Funding Date],
dbo.dbo_Tracking_File_Ext.F251#CompanyName1,
dbo.dbo_Tracking_File_Ext.f252#CompanyName2,
dbo.GENERIC_ENTITY_LenderName._UnparsedName AS [MISMO CompanyName1],
' ' AS [MISMO CompanyName2],
dbo.qryBorrJoin.firstname, dbo.qryBorrJoin.B1MI, dbo.qryBorrJoin.Name AS
B1LastName, dbo.qryBorrJoin.BSSN,
dbo.qryBorrJoin.CoFirstName, dbo.qryBorrJoin.B2MI,
dbo.qryBorrJoin.coLastName, dbo.qryBorrJoin.CBSSN, dbo.tblLoanInfo.[Security
Address Street],
dbo.tblLoanInfo.City, dbo.tblLoanInfo.State,
dbo.tblLoanInfo.ZIP, dbo.dbo_Tracking_File.f555#property_county,
dbo.PROPERTY._County AS [MISMO property_county],
dbo.dbo_Tracking_File_Ext.f519#TrusteeName,
dbo.GENERIC_ENTITY_Trustee._UnparsedName AS [MISMO
TrusteeName], dbo.tblStateLookup.StateTrustVMort,
dbo.tblClosedLoan.LoanNumber,
dbo.[tblPurpose Lookup].PurpComerica,
dbo.tblClosedLoan.CommitID, dbo.tblClosedLoan.SandDYN,
dbo.tblClosedLoan.PSStatus
FROM dbo.tblStateLookup RIGHT OUTER JOIN
dbo.dbo_Tracking_File RIGHT OUTER JOIN
dbo.qryBorrJoin INNER JOIN
dbo.tblLoanInfo INNER JOIN
dbo.tblLoanDetails INNER JOIN
dbo.tblClosedLoan ON dbo.tblLoanDetails.LoanDetailID =
dbo.tblClosedLoan.NCLoanNumber ON
dbo.tblLoanInfo.[Acct Number] =
dbo.tblLoanDetails.[Loan Number] ON
dbo.qryBorrJoin.[Loan Number] = dbo.tblLoanInfo.[Acct
Number] LEFT OUTER JOIN
dbo.[tblPurpose Lookup] ON dbo.tblLoanInfo.Purpose =
dbo.[tblPurpose Lookup].[Purpose Lookup] LEFT OUTER JOIN
dbo.dbo_Tracking_File_Ext ON
dbo.tblClosedLoan.LoanNumber = dbo.dbo_Tracking_File_Ext.Loan_ID ON
dbo.dbo_Tracking_File.Loan_ID =
dbo.tblClosedLoan.LoanNumber ON dbo.tblStateLookup.StateID =
dbo.tblLoanInfo.State LEFT OUTER JOIN
dbo._CLOSING_DOCUMENTS LEFT OUTER JOIN
dbo.GENERIC_ENTITY_LenderName ON
dbo._CLOSING_DOCUMENTS.CLDC_ID = dbo.GENERIC_ENTITY_LenderName.CLDC_ID LEFT
OUTER JOIN
dbo.GENERIC_ENTITY_Trustee RIGHT OUTER JOIN
dbo.RECORDABLE_DOCUMENT ON
dbo.GENERIC_ENTITY_Trustee.RCDO_ID = dbo.RECORDABLE_DOCUMENT.RCDO_ID ON
dbo._CLOSING_DOCUMENTS.CLDC_ID =
dbo.RECORDABLE_DOCUMENT.CLDC_ID RIGHT OUTER JOIN
dbo.MORTGAGE_TERMS_MaxAppl LEFT OUTER JOIN
dbo.LOAN_APPLICATION ON
dbo.MORTGAGE_TERMS_MaxAppl.APPL_ID = dbo.LOAN_APPLICATION.APPL_ID LEFT OUTER
JOIN
dbo.PROPERTY ON dbo.MORTGAGE_TERMS_MaxAppl.APPL_ID =
dbo.PROPERTY.APPL_ID ON
dbo._CLOSING_DOCUMENTS.LOAN_ID =
dbo.LOAN_APPLICATION.LOAN_ID LEFT OUTER JOIN
dbo.LOAN_DETAILS ON dbo._CLOSING_DOCUMENTS.CLDC_ID =
dbo.LOAN_DETAILS.CLDC_ID LEFT OUTER JOIN
dbo.MERS ON dbo.MORTGAGE_TERMS_MaxAppl.APPL_ID =
dbo.MERS.APPL_ID ON
dbo.tblClosedLoan.LoanNumber =
dbo.MORTGAGE_TERMS_MaxAppl.LenderLoanIdentifier
WHERE (dbo.tblLoanInfo.ClsdTDDate BETWEEN @.Enter_Begin_Date AND
@.Enter_End_Date) AND (dbo.tblClosedLoan.SandDYN = 0 OR
dbo.tblClosedLoan.SandDYN IS NULL) AND
(dbo.tblClosedLoan.PSStatus <> N'rescinded')
ORDER BY dbo.qryBorrJoin.Name, dbo.qryBorrJoin.firstname )|||ZachB,
When you say "gives two different orders of data columns", Do you mean
different sort of the result or that the column list is different?. How are
you querying this table function?
The "order by" clause used inside the function does not guarantee any order
of the result when you use:
declare @.sd datetime
declare @.ed datetime
set @.sd = '20050101'
set @.ed = '20050321'
select col1, col2, ..., coln
from dbo.MISMOqryMINRegistrationCldDateNE(@.sd, @.ed) as t
you have to use an "order by" clause again if you want the order of the rows
to be consistent.
select col1, col2, ..., coln
from dbo.MISMOqryMINRegistrationCldDateNE(@.sd, @.ed) as t
order by col1, ...
AMB
"ZachB" wrote:

> Not sure what you mean by posting the ddl but here's the syntax of the UDF
:
> CREATE FUNCTION dbo. MISMOqryMINRegistrationCldDateNE(@.Enter_
Begin_Date
> datetime,
> @.Enter_End_Date datetime)
> RETURNS TABLE
> AS
> RETURN ( SELECT TOP 100 PERCENT dbo.dbo_Tracking_File_Ext.f755#MERS,
> dbo.MERS.MERS_MINNumber AS [MISMO MERS], dbo.tblLoanDetails.FirstSecond,
> dbo.dbo_Tracking_File_Ext.f422#ClosingDate AS [MV No
te
> Date], dbo.tblLoanInfo.ClsdTDDate AS NoteDate,
> dbo.LOAN_DETAILS.ClosingDate AS [MISMO ClosingDate],
> dbo.tblLoanDetails.[Loan Amount],
> dbo.LOAN_DETAILS.DisbursementDate AS [Funding Date],
> dbo.dbo_Tracking_File_Ext.F251#CompanyName1,
> dbo.dbo_Tracking_File_Ext.f252#CompanyName2,
> dbo.GENERIC_ENTITY_LenderName._UnparsedName AS [MISMO CompanyName1],
> ' ' AS [MISMO CompanyName2],
> dbo.qryBorrJoin.firstname, dbo.qryBorrJoin.B1MI, dbo.qryBorrJoin.Name AS
> B1LastName, dbo.qryBorrJoin.BSSN,
> dbo.qryBorrJoin.CoFirstName, dbo.qryBorrJoin.B2MI,
> dbo.qryBorrJoin.coLastName, dbo.qryBorrJoin.CBSSN, dbo.tblLoanInfo.[Securi
ty
> Address Street],
> dbo.tblLoanInfo.City, dbo.tblLoanInfo.State,
> dbo.tblLoanInfo.ZIP, dbo.dbo_Tracking_File.f555#property_county,
> dbo.PROPERTY._County AS [MISMO property_county],
> dbo.dbo_Tracking_File_Ext.f519#TrusteeName,
> dbo.GENERIC_ENTITY_Trustee._UnparsedName AS [MISMO
> TrusteeName], dbo.tblStateLookup.StateTrustVMort,
> dbo.tblClosedLoan.LoanNumber,
> dbo.[tblPurpose Lookup].PurpComerica,
> dbo.tblClosedLoan.CommitID, dbo.tblClosedLoan.SandDYN,
> dbo.tblClosedLoan.PSStatus
> FROM dbo.tblStateLookup RIGHT OUTER JOIN
> dbo.dbo_Tracking_File RIGHT OUTER JOIN
> dbo.qryBorrJoin INNER JOIN
> dbo.tblLoanInfo INNER JOIN
> dbo.tblLoanDetails INNER JOIN
> dbo.tblClosedLoan ON dbo.tblLoanDetails.LoanDetailID
=
> dbo.tblClosedLoan.NCLoanNumber ON
> dbo.tblLoanInfo.[Acct Number] =
> dbo.tblLoanDetails.[Loan Number] ON
> dbo.qryBorrJoin.[Loan Number] = dbo.tblLoanInfo.[Acc
t
> Number] LEFT OUTER JOIN
> dbo.[tblPurpose Lookup] ON dbo.tblLoanInfo.Purpose =
> dbo.[tblPurpose Lookup].[Purpose Lookup] LEFT OUTER JOIN
> dbo.dbo_Tracking_File_Ext ON
> dbo.tblClosedLoan.LoanNumber = dbo.dbo_Tracking_File_Ext.Loan_ID ON
> dbo.dbo_Tracking_File.Loan_ID =
> dbo.tblClosedLoan.LoanNumber ON dbo.tblStateLookup.StateID =
> dbo.tblLoanInfo.State LEFT OUTER JOIN
> dbo._CLOSING_DOCUMENTS LEFT OUTER JOIN
> dbo.GENERIC_ENTITY_LenderName ON
> dbo._CLOSING_DOCUMENTS.CLDC_ID = dbo.GENERIC_ENTITY_LenderName.CLDC_ID LEF
T
> OUTER JOIN
> dbo.GENERIC_ENTITY_Trustee RIGHT OUTER JOIN
> dbo.RECORDABLE_DOCUMENT ON
> dbo.GENERIC_ENTITY_Trustee.RCDO_ID = dbo.RECORDABLE_DOCUMENT.RCDO_ID ON
> dbo._CLOSING_DOCUMENTS.CLDC_ID =
> dbo.RECORDABLE_DOCUMENT.CLDC_ID RIGHT OUTER JOIN
> dbo.MORTGAGE_TERMS_MaxAppl LEFT OUTER JOIN
> dbo.LOAN_APPLICATION ON
> dbo.MORTGAGE_TERMS_MaxAppl.APPL_ID = dbo.LOAN_APPLICATION.APPL_ID LEFT OUT
ER
> JOIN
> dbo.PROPERTY ON dbo.MORTGAGE_TERMS_MaxAppl.APPL_ID =
> dbo.PROPERTY.APPL_ID ON
> dbo._CLOSING_DOCUMENTS.LOAN_ID =
> dbo.LOAN_APPLICATION.LOAN_ID LEFT OUTER JOIN
> dbo.LOAN_DETAILS ON dbo._CLOSING_DOCUMENTS.CLDC_ID =
> dbo.LOAN_DETAILS.CLDC_ID LEFT OUTER JOIN
> dbo.MERS ON dbo.MORTGAGE_TERMS_MaxAppl.APPL_ID =
> dbo.MERS.APPL_ID ON
> dbo.tblClosedLoan.LoanNumber =
> dbo.MORTGAGE_TERMS_MaxAppl.LenderLoanIdentifier
> WHERE (dbo.tblLoanInfo.ClsdTDDate BETWEEN @.Enter_Begin_Date AND
> @.Enter_End_Date) AND (dbo.tblClosedLoan.SandDYN = 0 OR
> dbo.tblClosedLoan.SandDYN IS NULL) AND
> (dbo.tblClosedLoan.PSStatus <> N'rescinded')
> ORDER BY dbo.qryBorrJoin.Name, dbo.qryBorrJoin.firstname )|||The UDF is being called from an MS Access .adp project. The "order" of the
data columns is different meaning in one case it shows Column A, Column B,
Column C, Column D but in the other it shows Column B, Column C, Column A,
Column D (Even if the syntax says SELECT Column A, Column B, Column C, Colum
n
D.
row 1 test1 test2 test3 test4
vs.
row 1 test2 test3 test1 test4
"Alejandro Mesa" wrote:
> ZachB,
> When you say "gives two different orders of data columns", Do you mean
> different sort of the result or that the column list is different?. How ar
e
> you querying this table function?
> The "order by" clause used inside the function does not guarantee any orde
r
> of the result when you use:
> declare @.sd datetime
> declare @.ed datetime
> set @.sd = '20050101'
> set @.ed = '20050321'
> select col1, col2, ..., coln
> from dbo.MISMOqryMINRegistrationCldDateNE(@.sd, @.ed) as t
> you have to use an "order by" clause again if you want the order of the ro
ws
> to be consistent.
> select col1, col2, ..., coln
> from dbo.MISMOqryMINRegistrationCldDateNE(@.sd, @.ed) as t
> order by col1, ...
>
> AMB
> "ZachB" wrote:
>|||ZachB,
Can you trace the statements sent to sql server by the project?
AMB
"ZachB" wrote:
> The UDF is being called from an MS Access .adp project. The "order" of th
e
> data columns is different meaning in one case it shows Column A, Column B,
> Column C, Column D but in the other it shows Column B, Column C, Column A,
> Column D (Even if the syntax says SELECT Column A, Column B, Column C, Col
umn
> D.
> row 1 test1 test2 test3 test4
> vs.
> row 1 test2 test3 test1 test4
> "Alejandro Mesa" wrote:
>|||Not that I'm aware of. I know you can check the properties of a particular
spid under Current Activity and see what syntax is or has just been run. Bu
t
I would assume that since the two users are hitting the same .adp they
shouldn't be passing different statements.
The MS Access .adp menu option states:
Open table 'dbo.MISMOqryMINRegistrationCldDateNE'
and this is just one example. This mis-ordering happens consisently across
several UDFs that I'm working with.
"Alejandro Mesa" wrote:

> ZachB,
> Can you trace the statements sent to sql server by the project?
>
> AMB|||Use Profiler to trace activities in the server. You can read about it in BOL
.
AMB
"ZachB" wrote:
> Not that I'm aware of. I know you can check the properties of a particula
r
> spid under Current Activity and see what syntax is or has just been run.
But
> I would assume that since the two users are hitting the same .adp they
> shouldn't be passing different statements.
> The MS Access .adp menu option states:
> Open table 'dbo.MISMOqryMINRegistrationCldDateNE'
> and this is just one example. This mis-ordering happens consisently acros
s
> several UDFs that I'm working with.
> "Alejandro Mesa" wrote:
>|||Anyone else? While I'm learning to trace, has anyone ever had a UDF return
data in different COLUMN order for different users? Let me know. Thanks i
n
advance.
"ZachB" wrote:

> I have a UDF that when I run by two different users, gives two different
> orders of data columns. One follows the syntax of the function and the ot
her
> is mis-ordered but returns this way consistently. Has anyone else
> encountered this?|||"ZachB" <ZachB@.discussions.microsoft.com> wrote in message
news:5966BAE6-5CC4-4055-A8D9-454B2E5A2E9C@.microsoft.com...
> Anyone else? While I'm learning to trace, has anyone ever had a UDF
return
> data in different COLUMN order for different users? Let me know. Thanks
in
> advance.
>
Wild Guesses:
1. The clients are executing different code. One client has been updated
and the other hasn't.
2. The clients are attached to different databases, one on test and one on
production.
3. It isn't really happening - The client preferences are different so that
on one client things appear differently but aren't actually different.
(e.g. hidden display controls, etc)
4. You are using Select * and there is a weird caching thing going on.
Good Luck.
Jim

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?As far as i figured your query out, i am just wondering why this works for you as the 1 will be casted to a constant string which should not be allowed in the order by clause. Are you sure this works for you ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de|||First, you are running the release version of 2005. You should install at least SP1.

Second, if you install SP1, you would see an error:

Msg 408, Level 16, State 1, Line 9
A constant expression was encountered in the ORDER BY list, position 1.

Because you are sorting by the NUMBER 1, not column 1 by using the cast. So basically you have no sort.

inconsistent order by using insert into in a stored procedure

hi there,

i am using sql server 7. below is the stored procedure that is giving
me grief. its purpose it two-fold, depending on how it is called:
either to return a pageset (based on page number and page size), or to
return IDs of previous and next records (based on current record id).
the problem is, that the order in which records are inserted into the
temp table is inconsistent, even though the calling statement and the
order by is always the same: sometimes records are ordered correctly,
by project_number, and sometimes the order is broken starting at some
record (which is always the same).

i have no idea what is wrong here, i would appreciate any help!
thanks so much.

here is the calling statement:
EXECUTE spProjects 2,null,'project_number','asc','',6,50

here is the proc:
CREATE PROCEDURE spProjects

@.action int,
@.currID int,
@.sortBy varchar(50),
@.sortDir varchar(4),
@.searchBy varchar(255),
@.Page int,
@.RecsPerPage int

AS

SET NOCOUNT ON

DECLARE @.nextID int
DECLARE @.prevID int
DECLARE @.currRow int
DECLARE @.rowCount int
DECLARE @.firstRec int
DECLARE @.lastRec int
DECLARE @.total int
DECLARE @.more int
DECLARE @.sortBy2 varchar(50)

-- setup temp table
SELECT r.id as row, r.*,
a.name agr_type,
pu.name purpose,
sp.name sponsor,
pr.name prime,
p.lname p_lname, p.fname p_fname, p.mname p_mi, p.email
p_email,
o.name org,
convert(varchar(10), r.created_date, 101) adddate_c,
convert(varchar(10), r.updated_date, 101) upddate_c
INTO #project_temp_table
FROM spm_projects r, spm_agreement_types a, spm_purpose_types
pu, spm_sponsors sp, spm_sponsors pr, spm_pis p, spm_orgs o
WHERE 1 = 0

IF @.sortBy IS NULL SELECT @.sortBy = 'project_number'
IF @.sortBy = '' SELECT @.sortBy = 'project_number'
SELECT @.sortBy2 = @.sortBy + ' ' + @.sortDir
IF @.sortBy NOT LIKE '%project_number%' SELECT @.sortBy2 = @.sortBy2 +
', project_number'

-- get projects
EXEC ('INSERT INTO #project_temp_table
SELECT r.id as row, r.*,
a.name agr_type,
pu.name purpose,
sp.name sponsor,
pr.name prime,
p.lname p_lname, p.fname p_fname, p.mname p_mi, p.email
p_email,
o.name org,
convert(varchar(10), r.created_date, 101) adddate_c,
convert(varchar(10), r.updated_date, 101) upddate_c
FROM spm_projects r, spm_agreement_types a, spm_purpose_types
pu, spm_sponsors sp, spm_sponsors pr, spm_pis p, spm_orgs o
WHERE r.agreement_type_id = a.id
AND r.purpose_type_id = pu.id
AND r.sponsor_id = sp.id
AND r.prime_id *= pr.id
AND r.pi_id = p.id
AND r.org_id = o.id
' + @.searchBy + '
ORDER BY ' + @.sortBy2)

SET @.rowCount = 0

-- number records
UPDATE #project_temp_table SET @.rowCount = row = @.rowCount + 1

-- prev/next
SELECT @.currRow = row FROM #project_temp_table WHERE id = @.currID
SELECT @.prevID = id FROM #project_temp_table WHERE row = @.currRow -
1
SELECT @.nextID = id FROM #project_temp_table WHERE row = @.currRow +
1

-- paging
SELECT @.firstRec = (@.Page - 1) * @.RecsPerPage
SELECT @.lastRec = (@.Page * @.RecsPerPage + 1)
SELECT @.more = COUNT(*) FROM #project_temp_table WHERE row >=
@.LastRec
SELECT @.total = COUNT(*) FROM #project_temp_table

SET NOCOUNT OFF

-- prev/next
IF @.action = 1 SELECT @.prevID as prevID, @.nextID as nextID

--paging
IF @.action = 2
SELECT *, @.more as more, @.total as total
FROM #project_temp_table
WHERE row > @.firstRec AND row < @.lastRec

DROP TABLE #project_temp_table> the problem is, that the order in which records are inserted into the
> temp table is inconsistent

INSERT INTO has no defined order. The engine will choose an arbitrary
order, and you can't control it. Sometimes it just happens to work the way
you want, but the behavior is not well-defined... and certainly not
guaranteed.

You are thinking about the inserted table as a physical structure; add an
index, use an order by on *that* table to determine first, next, previous,
last, ranges, etc. Don't rely on the *physical* structure being in the
*logical* order you expect.|||FYI

Actually INSERT INTO does have a defined order.

The order says what sequence
the new info is added to the table.

Meanwhile, the organization of the rows
in the table may or may not have anything
at all to do with what sequence the rows
where created.

Bye,
Delbert Glass

"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uetddJ8nDHA.1676@.TK2MSFTNGP09.phx.gbl...
> > the problem is, that the order in which records are inserted into the
> > temp table is inconsistent
> INSERT INTO has no defined order. The engine will choose an arbitrary
> order, and you can't control it. Sometimes it just happens to work the
way
> you want, but the behavior is not well-defined... and certainly not
> guaranteed.
> You are thinking about the inserted table as a physical structure; add an
> index, use an order by on *that* table to determine first, next, previous,
> last, ranges, etc. Don't rely on the *physical* structure being in the
> *logical* order you expect.|||I think we're saying the same thing.

A SELECT from an INSERT INTO ... SELECT statement certainly may have an
ORDER BY clause. But how these rows end up in the target table once the
operation has completed is not guaranteed to be in the same order as the
original ORDER BY clause. Again, usually this *is* the case (barring a
clustered index), but like I said before, it is not guaranteed.

"Delbert Glass" <delbert@.noincoming.com> wrote in message
news:OqeWMI$nDHA.1764@.tk2msftngp13.phx.gbl...
> FYI
> Actually INSERT INTO does have a defined order.
> The order says what sequence
> the new info is added to the table.
> Meanwhile, the organization of the rows
> in the table may or may not have anything
> at all to do with what sequence the rows
> where created.
> Bye,
> Delbert Glass
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:uetddJ8nDHA.1676@.TK2MSFTNGP09.phx.gbl...
> > > the problem is, that the order in which records are inserted into the
> > > temp table is inconsistent
> > INSERT INTO has no defined order. The engine will choose an arbitrary
> > order, and you can't control it. Sometimes it just happens to work the
> way
> > you want, but the behavior is not well-defined... and certainly not
> > guaranteed.
> > You are thinking about the inserted table as a physical structure; add
an
> > index, use an order by on *that* table to determine first, next,
previous,
> > last, ranges, etc. Don't rely on the *physical* structure being in the
> > *logical* order you expect.|||aaron,

i can add an alter column and add a clustered index to the table
before i do insert into select from...

however, the stored procedure returns "... index being rebuilt"
message even though NOCOUNT is set to ON. obviously, this screws up
the ado recordset.

is there any way to disable these messages?

thanks,

--sasha

"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message news:<uetddJ8nDHA.1676@.TK2MSFTNGP09.phx.gbl>...
> > the problem is, that the order in which records are inserted into the
> > temp table is inconsistent
> INSERT INTO has no defined order. The engine will choose an arbitrary
> order, and you can't control it. Sometimes it just happens to work the way
> you want, but the behavior is not well-defined... and certainly not
> guaranteed.
> You are thinking about the inserted table as a physical structure; add an
> index, use an order by on *that* table to determine first, next, previous,
> last, ranges, etc. Don't rely on the *physical* structure being in the
> *logical* order you expect.|||If SET ANSI_WARNINGS OFF doesn't disable them, you can use ado's errors
collection to ignore error numbers in your range. We actually had to do
that for an ASP app that called a stored procedure which, among other
things, swapped out names for a "current" and "archive" metadata table,
using sp_rename. The ASP code to avoid the error was something like this:

on error resume next
set rs = conn.execute(SQL)
if conn.errors.count > 0 THEN
for each e in conn.errors
if e.NativeError <> 15477 then
Response.Write "Oops"
else
' it's okay, just warnings
end if
next
end if

' you may have to experiment with nextrecordset():

set rs = rs.nextrecordset()

"Alex Vorobiev" <sasha@.mathforum.com> wrote in message
news:e9b2000a.0310311837.7dc75200@.posting.google.c om...
> aaron,
> i can add an alter column and add a clustered index to the table
> before i do insert into select from...
> however, the stored procedure returns "... index being rebuilt"
> message even though NOCOUNT is set to ON. obviously, this screws up
> the ado recordset.
> is there any way to disable these messages?
> thanks,
> --sasha|||aaron, et.al.,

thank you guys for your help...

i just realized, however, that a clustered index won't do it for me.
if i have a dynamic where clause, i would know the name of the column
to index only at runtime.

what i am trying to do has most certainly been done, but i cannot find
a good comprehensive solution. i would like to have dynamic search by
and sort by arguments in a stored procedure, and end up with a
recordset / temp table where records are numbered consecutively based
on the dynamic sort by.

1. if i don't want to spell out my sort_by and search_by vars using
"case when", i need to use dynamic sql via EXEC. i was hoping to do
this using "SELECT INTO" local temp table, but if the SELECT INTO is
wrapped in the EXEC, other calls within my stored proc can't find the
table - i assume, this is a compile time vs runtime scoping issue.

2. i can first define the temp table within the scope of the proc
using CREATE TABLE or a fake SELECT INTO ... WHERE 1 = 0, however i
found if i do subsequent EXEC("SELECT INTO..."), there is nothing in
the previously defined temp table. this may be obvious to most of
you, i am guessing another scoping issue. i guess you can't overwrite
an existing table like that.

furthermore, if i define the table first, and then use INSERT INTO, as
we have already discussed in this thread, records in the table won't
be ordered according to the ORDER BY.

3. adding a clustered index won't help me because i have dynamic
order_by and order_direction

i've thought about doing other things to work around this. i can't
use a regular table because of a possible race condition when multiple
users are using the application. i've thought about using a global
table with a unique name via SPID, but then the table name will also
be a variable, and every statement in my stored proc would have to be
wrapped in EXEC.

from what i can see, i have only two options:

1) write a SELECT INTO, spell out all possible cases of search_by and
sort_by using CASE WHEN, with something like row = identity(int,1,1)
to generate consecutive numbers for my ordered rows. this way i avoid
dynamic sql, but i have to spell out all search and sort cases, and i
may have other existing identity columns in my SELECT already.

2) leave everything as is, but loop over the entire temp table after
the INSERT INTO, and number the rows within the loop. with
potentially thousands of rows in the table, this may be a significant
performance hit.

hoping for your collective wizdom to come to the rescue, i wonder if
there is another, smarter way to do this.

thanks in advance for your help,

--sasha|||Hi Sasha,

If you want dynamic order by, you can do:
ORDER BY
[case statement] ascending,
[case statement] descending,
[case statement] ascending,
[case statement] descending ...

You should have two case statements, for every column you may want to
order by. If your case statement returns [void], sql will ignore
that part of the statement. I've written 3 articles that may interest
you.
http://www.sql-server-performance.c...icles_other.asp. Look for
"Sql techniques for web reporting".

In What Order Index Sorts the Data?

Hi,

I want to ask a basic question, that is

IN WHAT ORDER A CLUSTERED INDEX SORT THE DATA IN THE COLUMN?

Somewhere in the MSDN library I read the following line:

"A clustered index physically sorts the table's contents in the order of the specified index columns"

But Sorting means it will be in ASCENDING ORDER (ASC) or It will be in DESCENDING ORDER (DESC)

So my question is lets suppose a column on which the cluistered index is defined and it contains character data liek abcd so in wht order it will sort the data alphabetically ASC or DESC

or

If the same above case with integer type of values, if column having integer values then in wht order the data in the table will be sorted.

?

Thanks..!!!

You are the one that has to speciy it. By default SS will use ASC.

Code Snippet

use tempdb

go

create table dbo.t1(

c1 int identity not null,

constraint pk_t1 primary key clustered (c1 ASC))

go

drop table dbo.t1

go

create table dbo.t1(

c1 int identity not null,

constraint pk_t1 primary key clustered (c1 DESC))

go

drop table dbo.t1

go

create table dbo.t1(

c1 int identity not null

)

go

create unique clustered index t1_c1_u_c_ix

on dbo.t1(c1 ASC)

go

drop index t1_c1_u_c_ix on dbo.t1

go

create unique clustered index t1_c1_u_c_ix

on dbo.t1(c1 DESC)

go

drop table dbo.t1

go

AMB|||

Ascending by default, but you can specify descinding if you'd like

|||

Hi,

I want to ask a basic question, that is

IN WHAT ORDER A CLUSTERED INDEX SORT THE DATA IN THE COLUMN?

Somewhere in the MSDN library I read the following line:

"A clustered index physically sorts the table's contents in the order of the specified index columns"

But Sorting means it will be in ASCENDING ORDER (ASC) or It will be in DESCENDING ORDER (DESC)

So my question is lets suppose a column on which the cluistered index is defined and it contains character data liek abcd so in wht order it will sort the data alphabetically ASC or DESC

or

If the same above case with integer type of values, if column having integer values then in wht order the data in the table will be sorted.

?

Thanks..!!!

--
PRASHANT PANDEY

Aargh!

Prashant,

Unfortunately, this information is simply wrong. There is absolutely no way to do anything at all in SQL Server to guarantee the physical order of the data. This is a good thing. If the data could be kept this way, you might have to move many gigabytes of data just to insert one row in the middle of a huge table. This would be terrible. "Ok, everyone move over to make room for the new person."

A clustered index does optimize the data storage for retrieval in the order of the clustered key columns, but if you want to be certain that data from a SELECT query comes back in a particular order, you absolutely must include an ORDER BY clause for that SELECT.

You may discover that without the order by, it works for years, and for millions of repetitions, but nevertheless, the order is not guaranteed, and suddenly when your table gets large, or you move to a different storage or processor configuration, or you apply a service pack, you may find you no longer see data in the order you expect.

Just in case all you were asking is in what logical order the data is organized when a column is specified as a key column, it is ascending order unless you specify otherwise with DESC.

Steve Kass

Drew University

http://www.stevekass.com

In what order does a clustered index store data?

Hi

I was going through the book by Kalen Delaney where she has mentioned the following paragpraph in Chapter 7 (Index Internals):

Many documents describing SQL Server indexes will tell you that the clustered index physically stores the data in sorted order. This can be misleading if you think of physical storage as the disk itself. If a clustered index had to keep the data on the actual disk in a particular order, it could be prohibitively expensive to make changes. If a page got too full and had to be split in two, all the data on all the succeeding pages would have to be moved down. Sorted order in a clustered index simply means that the data page chain is logically in order.

Then I read the book on SQL Server 2000 (on Perf Tuning) by Ken England. He says the clustered index stores data in physical order and any insert means moving the data physically. Also the same statement is echoed on the net by many articles.

What is the truth? How are really clustered index stored? What does physical order in the above statement really mean?

Regards

SanjaySi

It is an 'Apples and Oranges' difference.

Kalen is referrring to the actual physical disk sectors, where Ken is referring to the pages where SQL Server stores indexes.

The disk is NOT kept in perfect sequential order. Files can become fragmented from daily ordinary use. For indexes, SQL Server keeps a 'chain' of logical order, so that even where there is a page split, the chain is unbroken.

The 'Logical' order of clustered indexes is indeed kept in sequetial order. (Simplistic view coming...)

The index may be located on pages 1, 2, 3, 4, 5, 6, 27, 55, etc. The logical order 'chain', after page splits may be 1, 2, 55, 3, 27, 4, 5, 6, etc. And due to file fragmentation, the file may be stored in disk sectors 1,2, 454, 455, 1036, 1037, etc.

In what order are these methods on the PipelineComponent class called

I am interested in what order these three methods are called because they are all needed early in the component's life.

RegisterEvents
RegisterLogEntries
ProvideComponentProperties

I would guess ProvideComponentProperties, RegisterEvents,RegisterLogEntries
Thanks

Allan

Hi Allan,

Since they are unrelated it really should not matter. However, so you know, the order is RegisterEvents, RegisterLogEntries, and then sometime later ProvideComponentProperties. However, this only is true the first time a component is added to the design surface. Afterwards you will still get RegisterEvents and RegisterLogEntries but you will not get PCP. Also, if you create a component programatically then it is your responsibility to call PCP, whereas the other 2 will be called regardless.

Thanks,|||Matt

I'm not sure that I would say they are unrelated as they all provide details AFAIK about your component in some way

RegisterEvents - Add a custom event to the pipeline
RegisterLogEntries - Show the user any custom pipeline events in the log (if any) for the package
ProvideComponentProperties - Describe and setup the component.

Thanks for the order info