Increase performance of SQL Server by using RAM

Hi,
I have MSSQL-Server 2000 installed on a PIII 1.2 GHz server running with 256 MB of RAM.
The server is starting to run slower and slower at the peak times.
When ever I check the task manager performance, the processor is always bussy with red indicator, while the RAM is calm and running under 50%.
Is there any configuration in MSSQL-Server, I can do, so I can use the RAM to take some of the load on the processor?

I will really appreciate your help.
ThanksSQL server does all its actions in RAM. So if you insert more RAM the performance should (technically) increase. But first check how much RAM is available for SQL server and monitor the harddisk activity.|||Could you please help me, how to check for the amount of RAM reserved for MS SQL server?

Thanks|||Start SQL Enterprise manager
1
Connect to the appropiate SQL server

2
Right click on de name of the server in the right panel of the screen and select properties.

3
Choose the Tab memory

The memory must be configured as Dynamically with a minimum of 0 and a maximum of the total amount of RAM availleble.

You did determine that the process sqlservr.exe is consuming the bulk of the RAM and of the CPU time? If not than another proces is responsible for the slow response time!|||If you've sufficient memory allocated to SQL Server (Microsoft recommends don't restrict sql to a particular amount of memory, let it acquire memory on its own, hence it is preferred to allocated all memory dedicated to sql server.) If this is setted properly go to 'Performance Monitor', and under System object see '%Processor Time' which should be well below 80% . At the same time also see 'Processor Queue Length' which should be below 2 or equal to 2.

If above conditions are not satisfied you have processor bottleneck and must upgrade your processor

Incorrect value returned from Stored Procedure

I have an asp.net 1.1 website that uses sql server 2000 and vb.

I have a bit of a dilema, when I run a stored procedure in a webpage it returns the wrong value, but if I run it

in the query analyzer the correct value is returned.

 Dim orderHistory As nlb.OrdersDB = New nlb.OrdersDB ' Obtain Order ID from QueryString Dim OrderID As Integer = CInt(Request.Params("ID")) ' Get the customer ID too Dim myNewCustomerId As Integer = 0 myNewCustomerId = orderHistory.GetOrderCustomer(OrderID) Public Function GetOrderCustomer(ByVal orderID As Integer) As Integer ' Create Instance of Connection and Command Object Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim myCommand As SqlCommand = New SqlCommand("nlbsp_OrdersCustomerID", myConnection) ' Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure ' Add Parameters to SPROC Dim parameterOrderID As New SqlParameter("@.order_id", SqlDbType.Int, 4) parameterOrderID.Value = orderID myCommand.Parameters.Add(parameterOrderID) Dim parameterOrderCustID As New SqlParameter("@.customer_id", SqlDbType.Int, 4) parameterOrderCustID.Value = ParameterDirection.Output myCommand.Parameters.Add(parameterOrderCustID) 'Open the connection and execute the Command myConnection.Open() myCommand.ExecuteNonQuery() myConnection.Close() ' Return the customer_id (obtained as out paramter of SPROC) If parameterOrderCustID.Value <> 0 Then Return CInt(parameterOrderCustID.Value) Else Return 0 End If End Functionthe stored procdure isCREATE PROCEDURE [dbo].[nlbsp_OrdersCustomerID]( @.order_id int, @.customer_id int OUTPUT)AS/* Return the customer_id from the Orders. */SELECT @.customer_id = customer_id FROM nlb_Orders WHERE order_id = @.order_idGO

I know a particular order_id returns a value of 1. But when I run it in the webpage it always comes back as 2.

Any ideas would be appreciated

Thanks

Pete

Seems you made a small mistake:

parameterOrderCustID.Value = ParameterDirection.Output

It should be:

Dim CustomerID As Integer
parameterOrderCustID.Value = CustomerID
parameterOrderCustID.Direction=ParameterDirection.Output

Incorrect syntax near 'use '

I am trying to run the following three line statement and I get an error
stating: "Line 3: Incorrect syntax near 'use '."
The database name is correct, "cms user messaging"
declare @.dbname sysname
set @.dbname = 'cms user messaging'
'use ' + @.dbname + ' DBCC SHOWFILESTATS with no_infomsgs'
Message posted via http://www.webservertalk.comTry,
declare @.dbname sysname
set @.dbname = 'cms user messaging'
exec('use [' + @.dbname + '] DBCC SHOWFILESTATS with no_infomsgs')
go
AMB
"Robert Richards via webservertalk.com" wrote:

> I am trying to run the following three line statement and I get an error
> stating: "Line 3: Incorrect syntax near 'use '."
> The database name is correct, "cms user messaging"
> declare @.dbname sysname
> set @.dbname = 'cms user messaging'
> 'use ' + @.dbname + ' DBCC SHOWFILESTATS with no_infomsgs'
> --
> Message posted via http://www.webservertalk.com
>|||The first message was incorrect. This is the correct statement I am trying
to run:
declare @.dbname sysname
set @.dbname = 'cms user messaging'
create table #datafilestats
( dbname varchar(25),
flag bit default 0,
Fileid tinyint,
[FileGroup] tinyint,
TotalExtents dec (7, 1),
UsedExtents dec (7, 1),
[Name] varchar(50),
[FileName] sysname )
declare @.string sysname
set @.string = 'use ' + @.dbname + ' DBCC SHOWFILESTATS with no_infomsgs'
insert into #datafilestats (Fileid, [FileGroup] , TotalExtents ,
UsedExtents , [Name] , [FileName]) exec (@.string)
In running this statement I get the following error message:
Server: Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'cms'. No entry found
with that name. Make sure that the name is entered correctly.
Message posted via http://www.webservertalk.com|||Try,
declare @.dbname sysname
set @.dbname = 'cms user messaging'
create table #datafilestats
( dbname varchar(25),
flag bit default 0,
Fileid tinyint,
[FileGroup] tinyint,
TotalExtents dec (7, 1),
UsedExtents dec (7, 1),
[Name] varchar(50),
[FileName] sysname )
declare @.string sysname
set @.string = 'use [' + @.dbname + '] DBCC SHOWFILESTATS with no_infomsgs'
insert into #datafilestats (Fileid, [FileGroup] , TotalExtents , UsedExtents
, [Name] , [FileName])
exec (@.string)
select * from #datafilestats
...
AMB
"Robert Richards via webservertalk.com" wrote:

> The first message was incorrect. This is the correct statement I am trying
> to run:
> declare @.dbname sysname
> set @.dbname = 'cms user messaging'
> create table #datafilestats
> ( dbname varchar(25),
> flag bit default 0,
> Fileid tinyint,
> [FileGroup] tinyint,
> TotalExtents dec (7, 1),
> UsedExtents dec (7, 1),
> [Name] varchar(50),
> [FileName] sysname )
> declare @.string sysname
> set @.string = 'use ' + @.dbname + ' DBCC SHOWFILESTATS with no_infomsgs'
> insert into #datafilestats (Fileid, [FileGroup] , TotalExtents ,
> UsedExtents , [Name] , [FileName]) exec (@.string)
> In running this statement I get the following error message:
> Server: Msg 911, Level 16, State 1, Line 1
> Could not locate entry in sysdatabases for database 'cms'. No entry found
> with that name. Make sure that the name is entered correctly.
> --
> Message posted via http://www.webservertalk.com
>

Incorrect syntax near the keyword SELECT

Hello, I have the following query. When I run the query I get the following error message: "Incorrect syntax near keyword SELECT"

----------------
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY DateAdded) AS rownum, * FROM
SELECT TOP (100) PERCENT Videos.VideoId, Videos.UserId, Videos.UserName, Videos.Title, Videos.Description, Videos.Tags, Videos.VideoLength, Videos.TimesHeard,
Videos.ImageURL, Videos.RecType, Videos.Language, Videos.Category, Videos.DateAdded, Videos.RewardProgram, Videos.EditorChoice, TB2.hits
FROM Videos LEFT OUTER JOIN
(SELECT TOP (100) PERCENT VideoId, COUNT(*) AS hits
FROM (SELECT TOP (100) PERCENT UserId, VideoId, COUNT(*) AS cnt1
FROM Hits
GROUP BY VideoId, UserId) AS TB1
GROUP BY VideoId) AS TB2 ON Videos.VideoId = TB2.VideoId
ORDER BY TB2.hits DESC
) AS T1
WHERE rownum <= 5
-------------

If I run the query that is in BOLD as:

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY DateAdded) AS rownum, * FROM Videos) AS T1 WHERE rownum <=5

the query runs just fine. Also if I run the query that is NOT bold (above), it also runs fine. What can I do to run them both together as seen above?

Thank in advance,

Louis

Try this:

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY DateAdded) AS rownum, * FROM(SELECT TOP (100) PERCENT Videos.VideoId, Videos.UserId, Videos.UserName, Videos.Title, Videos.Description, Videos.Tags, Videos.VideoLength, Videos.TimesHeard,
Videos.ImageURL, Videos.RecType, Videos.Language, Videos.Category, Videos.DateAdded, Videos.RewardProgram, Videos.EditorChoice, TB2.hits
FROM Videos LEFT OUTER JOIN
(SELECT TOP (100) PERCENT VideoId, COUNT(*) AS hits
FROM (SELECT TOP (100) PERCENT UserId, VideoId, COUNT(*) AS cnt1
FROM Hits
GROUP BY VideoId, UserId) AS TB1
GROUP BY VideoId) AS TB2 ON Videos.VideoId = TB2.VideoId
ORDER BY TB2.hits DESC

)
) AS T1
WHERE rownum <= 5

|||

Thanks Limno, but I had tried that before and I get this error:

Incorrect syntax near ')'

I don't understand why

Louis

|||

Here is a working one:

SELECT*FROM(SELECT ROW_NUMBER()OVER(ORDERBY DateAdded)AS rownum,*FROM(SELECTTOP(100)PERCENT Videos.VideoId, Videos.UserId, Videos.UserName, Videos.Title, Videos.Description, Videos.Tags, Videos.VideoLength, Videos.TimesHeard,

Videos.ImageURL, Videos.RecType, Videos.Language, Videos.Category, Videos.DateAdded, Videos.RewardProgram, Videos.EditorChoice, TB2.hits

FROM VideosLEFTOUTERJOIN

(SELECTTOP(100)PERCENT VideoId,COUNT(*)AS hits

FROM(SELECTTOP(100)PERCENT UserId, VideoId,COUNT(*)AS cnt1

FROM Hits

GROUPBY VideoId, UserId)AS TB1

GROUPBY VideoId)AS TB2ON Videos.VideoId= TB2.VideoId

ORDERBY TB2.hitsDESC

)AS T1) AS T2

WHERE rownum<= 5

|||

Thanks Limno, that worked

Louis

Incorrect Syntax near the keyword 'LEFT' in MSSQL6.5

I have an MSSQL6.5 server and when i run the following SQL statement, it
promt me an error message: Incorrect Syntax near the keyword 'LEFT'
SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
what happen to my SQL Server? Because i am sure my statement is correct.
if i run this sql on SQL 2000 or 7, no problem.Hi
Chekc that your column and table name is exaclty the same case as on the SQL
Server 6.5 DB. You might be running a case sensitive SQL 6.5 installation.
Regards
Mike
"yichun" wrote:
> I have an MSSQL6.5 server and when i run the following SQL statement, it
> promt me an error message: Incorrect Syntax near the keyword 'LEFT'
> SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
> what happen to my SQL Server? Because i am sure my statement is correct.
> if i run this sql on SQL 2000 or 7, no problem.
>|||Perhaps the LEFT function was introduced in 7.0? I don't have a Books Online to check against, but I
bet that you do. Check it out. If it isn't available in 6.5, use SUBSTRING instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"yichun" <yichun@.discussions.microsoft.com> wrote in message
news:349D4286-8C05-4E37-BDB3-00A4F4EEB077@.microsoft.com...
>I have an MSSQL6.5 server and when i run the following SQL statement, it
> promt me an error message: Incorrect Syntax near the keyword 'LEFT'
> SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
> what happen to my SQL Server? Because i am sure my statement is correct.
> if i run this sql on SQL 2000 or 7, no problem.
>

Incorrect Syntax near the keyword 'LEFT' in MSSQL6.5

I have an MSSQL6.5 server and when i run the following SQL statement, it
promt me an error message: Incorrect Syntax near the keyword 'LEFT'
SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
what happen to my SQL Server? Because i am sure my statement is correct.
if i run this sql on SQL 2000 or 7, no problem.
Hi
Chekc that your column and table name is exaclty the same case as on the SQL
Server 6.5 DB. You might be running a case sensitive SQL 6.5 installation.
Regards
Mike
"yichun" wrote:

> I have an MSSQL6.5 server and when i run the following SQL statement, it
> promt me an error message: Incorrect Syntax near the keyword 'LEFT'
> SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
> what happen to my SQL Server? Because i am sure my statement is correct.
> if i run this sql on SQL 2000 or 7, no problem.
>
|||Perhaps the LEFT function was introduced in 7.0? I don't have a Books Online to check against, but I
bet that you do. Check it out. If it isn't available in 6.5, use SUBSTRING instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"yichun" <yichun@.discussions.microsoft.com> wrote in message
news:349D4286-8C05-4E37-BDB3-00A4F4EEB077@.microsoft.com...
>I have an MSSQL6.5 server and when i run the following SQL statement, it
> promt me an error message: Incorrect Syntax near the keyword 'LEFT'
> SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
> what happen to my SQL Server? Because i am sure my statement is correct.
> if i run this sql on SQL 2000 or 7, no problem.
>

Incorrect Syntax near the keyword 'LEFT' in MSSQL6.5

I have an MSSQL6.5 server and when i run the following SQL statement, it
promt me an error message: Incorrect Syntax near the keyword 'LEFT'
SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
what happen to my SQL Server? Because i am sure my statement is correct.
if i run this sql on SQL 2000 or 7, no problem.Hi
Chekc that your column and table name is exaclty the same case as on the SQL
Server 6.5 DB. You might be running a case sensitive SQL 6.5 installation.
Regards
Mike
"yichun" wrote:

> I have an MSSQL6.5 server and when i run the following SQL statement, it
> promt me an error message: Incorrect Syntax near the keyword 'LEFT'
> SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
> what happen to my SQL Server? Because i am sure my statement is correct.
> if i run this sql on SQL 2000 or 7, no problem.
>|||Perhaps the LEFT function was introduced in 7.0? I don't have a Books Online
to check against, but I
bet that you do. Check it out. If it isn't available in 6.5, use SUBSTRING i
nstead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"yichun" <yichun@.discussions.microsoft.com> wrote in message
news:349D4286-8C05-4E37-BDB3-00A4F4EEB077@.microsoft.com...
>I have an MSSQL6.5 server and when i run the following SQL statement, it
> promt me an error message: Incorrect Syntax near the keyword 'LEFT'
> SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
> what happen to my SQL Server? Because i am sure my statement is correct.
> if i run this sql on SQL 2000 or 7, no problem.
>sql

Incorrect syntax near 'sp_cursorclose'

I get this error message when using JDBC and JSP to access a sqlserver 2000 database. Trying to insert a record. Insert works perfectly when run from query analyzer. Fails with above error message when inserting from web app.
Any ideas?
Amy Thropp wrote:

> I get this error message when using JDBC and JSP to access a sqlserver 2000 database. Trying to insert a record. Insert works perfectly when run from query analyzer. Fails with above error message when inserting from web app.
> Any ideas?
Show the actual jdbc code you're running and also the whole stacktrace of the
exception.
thanks
Joe
|||inserting lock with {INSERT INTO record_locks (type, record_id, session_id, user_id, timestamp) VALUES( 'epss', 1006, 'B77384E6BF824A351B8434967F99C7BF', 1, getdate())}
connection: jdbc:JSQLConnect://tsps5.bha.biancohopkins.com:1433/database=conversion_db/sa barfed on update {INSERT INTO record_locks (type, record_id, session_id, user_id, timestamp) VALUES( 'epss', 1006, 'B77384E6BF824A351B8434967F99C7BF', 1, getdate())}
, error: com.jnetdirect.jsql.u: sp_cursoropen/sp_cursorprepare: The statement parameter can only be a single select or a single stored procedure.
com.jnetdirect.jsql.u: sp_cursoropen/sp_cursorprepare: The statement parameter can only be a single select or a single stored procedure.
at com.jnetdirect.jsql.at.a(Unknown Source)
at com.jnetdirect.jsql.ae.f(Unknown Source)
at com.jnetdirect.jsql.ae.new(Unknown Source)
at com.jnetdirect.jsql.ae.for(Unknown Source)
at com.jnetdirect.jsql.l.execute(Unknown Source)
at com.jnetdirect.jsql.ae.else(Unknown Source)
at com.jnetdirect.jsql.ae.executeQuery(Unknown Source)
at TestLock.main(TestLock.java:28)
"Joe Weinstein" wrote:

>
> Amy Thropp wrote:
>
> Show the actual jdbc code you're running and also the whole stacktrace of the
> exception.
> thanks
> Joe
>
|||here's the code. The other posting had the stacktrace messages
public static void main( String[] args)
{
String query =
"INSERT INTO record_locks (type, record_id, session_id, " +
"user_id) " +
"VALUES( 'epss', 1006, 'B77384E6BF824A351B8434967F99C7BF', 1)";
try {
Class.forName( "com.jnetdirect.jsql.JSQLDriver");
Connection conn = DriverManager.getConnection( DB, USER, PASSWD);
Statement stmt =
conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
print( "inserting lock with {" + query + "}");
ResultSet rs = stmt.executeQuery( query);
print( "and got back from update");
} catch (Exception e) {
print( "connection: " + DB + "/" + USER + " barfed on update {" +
query + "}, error: " + e.toString());
e.printStackTrace();
}
return;
}
"Joe Weinstein" wrote:

>
> Amy Thropp wrote:
>
> Show the actual jdbc code you're running and also the whole stacktrace of the
> exception.
> thanks
> Joe
>
|||Ok.
The problem is that you're doing an insert (not a query), and then calling
executeQuery() instead of executeUpdate().
Try this:
String insert =
"INSERT INTO record_locks (type, record_id, session_id, " +
"user_id) " +
"VALUES( 'epss', 1006, 'B77384E6BF824A351B8434967F99C7BF', 1)";
Class.forName( "com.jnetdirect.jsql.JSQLDriver");
Connection conn = DriverManager.getConnection( DB, USER, PASSWD);
Statement stmt = conn.createStatement();
print( "inserting lock with {" + insert + "}");
stmt.executeUpdate(insert);
Joe Weinstein at BEA
Amy Thropp wrote:
[vbcol=seagreen]
> here's the code. The other posting had the stacktrace messages
> public static void main( String[] args)
> {
> String query =
> "INSERT INTO record_locks (type, record_id, session_id, " +
> "user_id) " +
> "VALUES( 'epss', 1006, 'B77384E6BF824A351B8434967F99C7BF', 1)";
> try {
> Class.forName( "com.jnetdirect.jsql.JSQLDriver");
> Connection conn = DriverManager.getConnection( DB, USER, PASSWD);
> Statement stmt =
> conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
> ResultSet.CONCUR_READ_ONLY);
> print( "inserting lock with {" + query + "}");
> ResultSet rs = stmt.executeQuery( query);
> print( "and got back from update");
> } catch (Exception e) {
> print( "connection: " + DB + "/" + USER + " barfed on update {" +
> query + "}, error: " + e.toString());
> e.printStackTrace();
> }
> return;
> }
>
> "Joe Weinstein" wrote:
>

Incorrect syntax near LEFT

Hi guys;

I am trying to create a stored procedure in my database with dynamic filter, how ever i cant even let my query work.

When i run my stored procedure i get this errorIncorrect syntax near 'LEFT'

I dnt knw wats wrong with my code.

Please help me...
below is my stored procedure..

Thanks is advance.

ALTER PROCEDURE dbo.GetClassByCustomFilter
@.pcCustomFilterNVARCHAR(500)=''
AS
DECLARE @.sSqlString nvarchar(1024)
SET @.sSqlString = 'SELECT R.cRoomNo, P.cFirstName, P.cLastName, P.cMiddleName, U.cCode AS cSubjectCode, U.cName AS cSubjectName, '
SET @.sSqlString = @.sSqlString + 'U.cDescription AS cSubjectDescription, B.cCode AS cSection, H.cName AS cRecurenceName, C.iClassID, C.iInstructorID, C.iSubjectid, C.iEnrollmentID, '
SET @.sSqlString = @.sSqlString + 'C.iRecordTypeID, C.iBlockSectionID, C.cCode, C.cType, C.iRoomID, C.cRecurrence, CAST(CONVERT(nvarchar, GETDATE(), 1) + '' '' + CONVERT(nvarchar, '
SET @.sSqlString = @.sSqlString + 'C.tStartTime,8) AS datetime) AS tStartTime, CAST(CONVERT(nvarchar, GETDATE(), 1) + '' '' + CONVERT(nvarchar, C.tEndTime,8) AS datetime) '
SET @.sSqlString = @.sSqlString + 'AS tEndTime, C.fUnits, C.nAllowed, C.nMaxAllowed, C.mNotes, C.tCreated, C.tEdited, C.iEditedBy, C.iCreatedby, C.adGUID '
SET @.sSqlString = @.sSqlString + 'FROM dbo.PERSONALINFO P INNER JOIN dbo.INSTRUCTORS I ON P.iPersonalInfoId = I.iPersonalInfoID RIGHT OUTER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.CLASSES C INNER JOIN dbo.BLOCKSECTIONS B ON C.iBlockSectionID = B.iBlockSectionId INNER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.SUBJECTS U ON C.iSubjectid = U.iSubjectID LEFT OUTER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.SCHEDULERECURRENCE H ON C.cRecurrence = H.cRecurrence ON I.iInstructorID = C.iInstructorID LEFT OUTER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.ROOMS R ON C.iRoomID = R.iRoomId WHERE (C.cType <> ''0'')'
IF LEN(@.pcCustomFilter) > 0
BEGIN
SET@.sSqlString = @.sSqlString + ' AND ' + @.pcCustomFilter
END
EXEC sp_executesql @.sSqlString

Well, thanks for reminding me why I hate complex dynamic SQL strings.

Thisa line is causing you problems:

SET @.sSqlString = @.sSqlString + 'dbo.SCHEDULERECURRENCE H ON C.cRecurrence = H.cRecurrence ON I.iInstructorID = C.iInstructorID LEFT OUTER JOIN '

Note the:ON C.cRecurrence = H.cRecurrenceON I.iInstructorID = C.iInstructorID (two ON clauses for a single JOIN clause).|||Thanks for the reply .

The Query is correct.

I get this error because the lenght of the variable@.sSqlString is only 1024 but my query string is longer than that.

Regards|||Well, be that as it may, having to ON statements for a single JOIN clause will give you a syntax error...|||Of course it will. But the SQL statement below has 6JOIN and 6ON.

enewe thanks 4 the time.

Regards.|||The following, cut and pasted from you original post, is what I'm referencing:

SET @.sSqlString = @.sSqlString + 'dbo.SUBJECTS U ON C.iSubjectid = U.iSubjectID LEFT OUTER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.SCHEDULERECURRENCE H ON C.cRecurrence = H.cRecurrence ON I.iInstructorID = C.iInstructorID LEFT OUTER JOIN '

Note the 'ON C.cRecurrence = H.cRecurrence ON I.iInstructorID = C.iInstructorID' of the second line. That will cause issues.

However, I'm glad you got it working...|||Of course it wont work bcoz its just a part of the statement, not the whole statement. :) jst Kid'N. But I am 101% sure that the SQL Statement below will work because I just copied it from Visual Studio .NET View Designer.

8)

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 near ',' using a Multi-value parameter

I created a report in Reporting Services 2005 where I added multi-value parameters. When I run my report, and try to select more than one parameter, I get an error: Incorrect syntax near ','I fixed it by changing my '=' signs to 'IN', so that it would search for the multi-value parameter within the values selected.

Incorrect Parameter in Desing Mode

WHERE (Cono = @.Company) AND (DATEPART(month, PaymentDate) = @.Month) AND (DATEPART(year, PaymentDate) = @.Year)

If I run the job in preview mode I enter the parameter data as requested and it runs correctly. When I go back to design mode and run the query using the ! (the parameter box pops up with the data I entered in preview mode) I get an error message - The Parameter is incorrect.

I've tried setting the parameters to every combination of string/interger I can think of.

What is happening here?

Try to eliminate parameters one by one (replace with a literal value), so you'll know which parameter causes the error message.|||It has to do with the cono (Company) parameter. Cono is defined as integer. I get the message no matter if I set the parameter value to string or integer.

Incorrect Login associated with dbo

If I run the following statement and do not get any returns, does that mean I
have an incorrect login associated with the dbo?
use MY_DATABASE
go
select d.name, dp.name, d.owner_sid, dp.sid from sys.databases d,
sys.database_principals dp where d.owner_sid = dp.sid and dp.name = 'dbo'
and d.name = 'MY_DATABASE'
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
> If I run the following statement and do not get any returns, does that
> mean I
> have an incorrect login associated with the dbo?
Yes. The dbo user sid should normally match the owner_sid in sys.databases.
The query below will should the names, if valid.
SELECT
d.name AS database_name,
SUSER_SNAME(d.owner_sid) AS databases_owner,
SUSER_SNAME(dp.sid) AS dbo_login
FROM sys.databases d
CROSS JOIN sys.database_principals dp
WHERE
d.name = 'MY_DATABASE'
AND dp.name = 'dbo'
You can correct the mismatch with ALTER AUTHORIZATION. For example:
ALTER AUTHORIZATION ON DATABASE::MY_DATABASE TO [sa];
Hope this helps.
Dan Guzman
SQL Server MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6f875a7486c91@.uwe...
> If I run the following statement and do not get any returns, does that
> mean I
> have an incorrect login associated with the dbo?
> use MY_DATABASE
> go
> select d.name, dp.name, d.owner_sid, dp.sid from sys.databases d,
> sys.database_principals dp where d.owner_sid = dp.sid and dp.name = 'dbo'
> and d.name = 'MY_DATABASE'
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
>

Incorrect Login associated with dbo

If I run the following statement and do not get any returns, does that mean
I
have an incorrect login associated with the dbo?
use MY_DATABASE
go
select d.name, dp.name, d.owner_sid, dp.sid from sys.databases d,
sys.database_principals dp where d.owner_sid = dp.sid and dp.name = 'dbo'
and d.name = 'MY_DATABASE'
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1> If I run the following statement and do not get any returns, does that
> mean I
> have an incorrect login associated with the dbo?
Yes. The dbo user sid should normally match the owner_sid in sys.databases.
The query below will should the names, if valid.
SELECT
d.name AS database_name,
SUSER_SNAME(d.owner_sid) AS databases_owner,
SUSER_SNAME(dp.sid) AS dbo_login
FROM sys.databases d
CROSS JOIN sys.database_principals dp
WHERE
d.name = 'MY_DATABASE'
AND dp.name = 'dbo'
You can correct the mismatch with ALTER AUTHORIZATION. For example:
ALTER AUTHORIZATION ON DATABASE::MY_DATABASE TO [sa];
Hope this helps.
Dan Guzman
SQL Server MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6f875a7486c91@.uwe...
> If I run the following statement and do not get any returns, does that
> mean I
> have an incorrect login associated with the dbo?
> use MY_DATABASE
> go
> select d.name, dp.name, d.owner_sid, dp.sid from sys.databases d,
> sys.database_principals dp where d.owner_sid = dp.sid and dp.name = 'dbo'
> and d.name = 'MY_DATABASE'
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200703/1
>

Incorrect Login associated with dbo

If I run the following statement and do not get any returns, does that mean I
have an incorrect login associated with the dbo?
use MY_DATABASE
go
select d.name, dp.name, d.owner_sid, dp.sid from sys.databases d,
sys.database_principals dp where d.owner_sid = dp.sid and dp.name = 'dbo'
and d.name = 'MY_DATABASE'
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1> If I run the following statement and do not get any returns, does that
> mean I
> have an incorrect login associated with the dbo?
Yes. The dbo user sid should normally match the owner_sid in sys.databases.
The query below will should the names, if valid.
SELECT
d.name AS database_name,
SUSER_SNAME(d.owner_sid) AS databases_owner,
SUSER_SNAME(dp.sid) AS dbo_login
FROM sys.databases d
CROSS JOIN sys.database_principals dp
WHERE
d.name = 'MY_DATABASE'
AND dp.name = 'dbo'
You can correct the mismatch with ALTER AUTHORIZATION. For example:
ALTER AUTHORIZATION ON DATABASE::MY_DATABASE TO [sa];
Hope this helps.
Dan Guzman
SQL Server MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6f875a7486c91@.uwe...
> If I run the following statement and do not get any returns, does that
> mean I
> have an incorrect login associated with the dbo?
> use MY_DATABASE
> go
> select d.name, dp.name, d.owner_sid, dp.sid from sys.databases d,
> sys.database_principals dp where d.owner_sid = dp.sid and dp.name = 'dbo'
> and d.name = 'MY_DATABASE'
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1
>

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 SQL results

Hi

I have an oddity. If I run a piece of SQL:

SELECT EmployeeNo, MailTo
FROM ST_PPS.dbo.Employee
where AddedOn BETWEEN '01-jan-2006' and '01-feb-2006'
AND MailTo NOT IN ( '3', 'x')
order by MailTo

I get the results

EmployeeNo MailTo
---- --
608384 1
606135 1
608689 1
609095 1
607163 1
606165 1
606472 1
608758 1
....

for 2594 rows

If I create a stored procedure with the same SQL:-

CREATE PROCEDURE dbo.PPS_test
AS
SELECT EmployeeNo, MailTo
FROM ST_PPS.dbo.Employee
where AddedOn BETWEEN '01-jan-2006' and '01-feb-2006'
AND MailTo NOT IN ( '3', 'x')
order by MailTo
GO

and run it:-

EXEC PPS_test

I get three extra rows

EmployeeNo MailTo
---- --
607922 NULL
606481 NULL
605599 NULL
606316 1
608871 1
607427 1
608795 1
....
for 2597

Does anyone know what is happening here? It appears that the clause:-

MailTo NOT IN ( '3', 'x')

excludes NULL in raw SQL, but includes NULL (correctly I think) in a
stored procedure.

Chloe Crowder
The British Libraryverify your ansi_nulls settings:

create table #t(i int)
insert into #t
select null
union all
select 1
union all
select 2
union all
select 3
go
select i from #t where i not in(1)

i
----
2
3

(2 row(s) affected)
go
set ansi_nulls off
go
select i from #t where i not in(1)

i
----
NULL
2
3

(3 row(s) affected)

go
drop table #t|||chloe.crowder@.bl.uk wrote:
> Hi
> I have an oddity.

Well, I'm not able to duplicate your problem with the sql below. Can
you possibly provide create table and insert statements that show the
strange behavior?

create table Employee (
EmployeeNo int,
MailTo char(1) NULL
)
insert Employee values (608384,'1')
insert Employee values (606135,'1')
insert Employee values (608689,'x')
insert Employee values (609095,'3')
insert Employee values (607922,'2')
insert Employee (EmployeeNo) values (606481)
insert Employee (EmployeeNo) values (605599)

select EmployeeNo, MailTo
from Employee
where MailTo NOT IN ( '3', 'x')
order by MailTo
go

create procedure PPS_test as
select EmployeeNo, MailTo
from Employee
where MailTo NOT IN ( '3', 'x')
order by MailTo
go

exec PPS_test

drop table Employee
drop procedure PPS_test|||(chloe.crowder@.bl.uk) writes:
> If I create a stored procedure with the same SQL:-
> CREATE PROCEDURE dbo.PPS_test
> AS
> SELECT EmployeeNo, MailTo
> FROM ST_PPS.dbo.Employee
> where AddedOn BETWEEN '01-jan-2006' and '01-feb-2006'
> AND MailTo NOT IN ( '3', 'x')
> order by MailTo
> GO
> and run it:-
> EXEC PPS_test
> I get three extra rows
> EmployeeNo MailTo
> ---- --
> 607922 NULL
> 606481 NULL
> 605599 NULL

Let me guess: you are creating your stored procedures in Enterprise
Manager, aren't you? That's a crappy tool to edit stored procedures
in. You are better off using Query Analyzer.

One reason it's crappy is because, it defaults the settings
ANSI_NULLS and QUOTED_IDENTIFIER to be OFF. These settings are
saved with the procedure, so when you run the procedure ANSI_NULLS
is off, and you get three extra rows. Normally, when ANSI_NULLS is
ON (which is the default in most environments), NULL is never
equal to anything, and never is it unequal to anything. But when the
setting is OFF NULLs are equal to other NULLS and unequal to other
values. This is a legacy setting that should be avoided.

There are also features in SQL Server that requires ANSI_NULLS to
be ON, so there is all reason to run with ANSI_NULLS on.

In Query Analyzer, ANSI_NULLS is ON by default.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland

Ah, that would certainly explain it. Don't you just love Microsoft at
times!

Does the 'feature' carry forward into SQL 2005?

Thanks to Eric and Alexander for their time

Chloe|||(chloe.crowder@.bl.uk) writes:
> Ah, that would certainly explain it. Don't you just love Microsoft at
> times!
> Does the 'feature' carry forward into SQL 2005?

The legacy settings SET ANSI_NULLS OFF and SET QUOTED_IDENTIFIER OFF
remains. But in the new Mgmt Studio it is not equally easy to run with
these settings off. (There are alas some cases where they still turn them
off, despite that I have hammered them with bug reports about this.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Inconsistent sp_spaceused

When I run sp_spaceused 'table', @.updateusage = 'TRUE', here is what I get:
name rows reserved data index_size unused
-- -- -- -- -- --
table 0 7253 KB 5020 KB 32 KB 2201 KB
How come that empty table still occupies lots of space?It seems that in some moment before such table contained rows (and had an
index).
Try a DBCC UPDATEUSAGE on the whole database or DBCC CHECKTABLE on the
interested table
"Leon Shargorodsky" wrote:
> When I run sp_spaceused 'table', @.updateusage = 'TRUE', here is what I get:
> name rows reserved data index_size unused
> -- -- -- -- -- --
> table 0 7253 KB 5020 KB 32 KB 2201 KB
> How come that empty table still occupies lots of space?

Inconsistent sp_spaceused

When I run sp_spaceused 'table', @.updateusage = 'TRUE', here is what I get:
name rows reserved data index_size unused
-- -- -- -- -- --
table 0 7253 KB 5020 KB 32 KB 2201 KB
How come that empty table still occupies lots of space?It seems that in some moment before such table contained rows (and had an
index).
Try a DBCC UPDATEUSAGE on the whole database or DBCC CHECKTABLE on the
interested table
"Leon Shargorodsky" wrote:

> When I run sp_spaceused 'table', @.updateusage = 'TRUE', here is what I get
:
> name rows reserved data index_size unused
> -- -- -- -- -- --
> table 0 7253 KB 5020 KB 32 KB 2201 KB
> How come that empty table still occupies lots of space?