Adressing memory above 1,7 Gb in SQL

Hi everyone,
We are using W2k3 standard x 64 edition in combination with SQL2000 standard
edition.
W2k with SQL2000 systems are also in use.
These machines have 4Gb of RAM, but i can't seem to get SQL to use more than
1,7Gb.
Tried the /3Gb switch in boot.ini en AWE enabled in sp_configure.
Nothing works, am i doing something strange or wrong ?
Rob
How do you check the memory used ? Performance monitor or Task Manager?
Task Manager will show you a wrong value if you use more than 2 Gb of
RAM.
Also how much memory is used by other Apps and/or OS.
Markus
|||Hi Rob,
Unfortunately SQL 2000 Standard Edition has a limit of 2GB worth of memory.
You can check the Maximum Capacity Specifications here:
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
Let me know if I can help any further.
Adam J Warne, MCDBA
"Rob Vos" wrote:

> Hi everyone,
> We are using W2k3 standard x 64 edition in combination with SQL2000 standard
> edition.
> W2k with SQL2000 systems are also in use.
> These machines have 4Gb of RAM, but i can't seem to get SQL to use more than
> 1,7Gb.
> Tried the /3Gb switch in boot.ini en AWE enabled in sp_configure.
> Nothing works, am i doing something strange or wrong ?
> --
> Rob
|||Thanks Adam. Now i need to get back to my supplier, he sold me 4 Gb system
with SQL standard.
Rob
"Adam Warne" wrote:
[vbcol=seagreen]
> Hi Rob,
> Unfortunately SQL 2000 Standard Edition has a limit of 2GB worth of memory.
> You can check the Maximum Capacity Specifications here:
> http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
> Let me know if I can help any further.
> --
> Adam J Warne, MCDBA
>
> "Rob Vos" wrote:

Adressing memory above 1,7 Gb in SQL

Hi everyone,
We are using W2k3 standard x 64 edition in combination with SQL2000 standard
edition.
W2k with SQL2000 systems are also in use.
These machines have 4Gb of RAM, but i can't seem to get SQL to use more than
1,7Gb.
Tried the /3Gb switch in boot.ini en AWE enabled in sp_configure.
Nothing works, am i doing something strange or wrong ?
--
RobHow do you check the memory used ? Performance monitor or Task Manager?
Task Manager will show you a wrong value if you use more than 2 Gb of
RAM.
Also how much memory is used by other Apps and/or OS.
Markus|||Hi Rob,
Unfortunately SQL 2000 Standard Edition has a limit of 2GB worth of memory.
You can check the Maximum Capacity Specifications here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
Let me know if I can help any further.
--
Adam J Warne, MCDBA
"Rob Vos" wrote:
> Hi everyone,
> We are using W2k3 standard x 64 edition in combination with SQL2000 standard
> edition.
> W2k with SQL2000 systems are also in use.
> These machines have 4Gb of RAM, but i can't seem to get SQL to use more than
> 1,7Gb.
> Tried the /3Gb switch in boot.ini en AWE enabled in sp_configure.
> Nothing works, am i doing something strange or wrong ?
> --
> Rob|||Thanks Adam. Now i need to get back to my supplier, he sold me 4 Gb system
with SQL standard.
--
Rob
"Adam Warne" wrote:
> Hi Rob,
> Unfortunately SQL 2000 Standard Edition has a limit of 2GB worth of memory.
> You can check the Maximum Capacity Specifications here:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
> Let me know if I can help any further.
> --
> Adam J Warne, MCDBA
>
> "Rob Vos" wrote:
> > Hi everyone,
> >
> > We are using W2k3 standard x 64 edition in combination with SQL2000 standard
> > edition.
> > W2k with SQL2000 systems are also in use.
> >
> > These machines have 4Gb of RAM, but i can't seem to get SQL to use more than
> > 1,7Gb.
> >
> > Tried the /3Gb switch in boot.ini en AWE enabled in sp_configure.
> >
> > Nothing works, am i doing something strange or wrong ?
> >
> > --
> > Rob

Adressing memory above 1,7 Gb in SQL

Hi everyone,
We are using W2k3 standard x 64 edition in combination with SQL2000 standard
edition.
W2k with SQL2000 systems are also in use.
These machines have 4Gb of RAM, but i can't seem to get SQL to use more than
1,7Gb.
Tried the /3Gb switch in boot.ini en AWE enabled in sp_configure.
Nothing works, am i doing something strange or wrong ?
RobHow do you check the memory used ? Performance monitor or Task Manager?
Task Manager will show you a wrong value if you use more than 2 Gb of
RAM.
Also how much memory is used by other Apps and/or OS.
Markus|||Hi Rob,
Unfortunately SQL 2000 Standard Edition has a limit of 2GB worth of memory.
You can check the Maximum Capacity Specifications here:
http://msdn.microsoft.com/library/d...br />
8dbn.asp
Let me know if I can help any further.
Adam J Warne, MCDBA
"Rob Vos" wrote:

> Hi everyone,
> We are using W2k3 standard x 64 edition in combination with SQL2000 standa
rd
> edition.
> W2k with SQL2000 systems are also in use.
> These machines have 4Gb of RAM, but i can't seem to get SQL to use more th
an
> 1,7Gb.
> Tried the /3Gb switch in boot.ini en AWE enabled in sp_configure.
> Nothing works, am i doing something strange or wrong ?
> --
> Rob|||Thanks Adam. Now i need to get back to my supplier, he sold me 4 Gb system
with SQL standard.
Rob
"Adam Warne" wrote:
[vbcol=seagreen]
> Hi Rob,
> Unfortunately SQL 2000 Standard Edition has a limit of 2GB worth of memory
.
> You can check the Maximum Capacity Specifications here:
> http://msdn.microsoft.com/library/d... />
s_8dbn.asp
> Let me know if I can help any further.
> --
> Adam J Warne, MCDBA
>
> "Rob Vos" wrote:
>

ADO.NET SP throws ArgumentException after migrating to SQL 2005 Standard - UPDATE 2 - FIXED

I just migrated a database from SQL 2000 Developer Edition on W2k Server to a production machine running SQL 2005 Standard on W2k3 Server Standard. A Web Service was also migrated from .NET 1.1 on the development machine to .NET 2.0 on the production machine.

Since the migration, a stored procedure that worked on SQL 2000/.NET 1.1 now throws a System.ArgumentException: "No mapping exists from object type System.RuntimeType to a known managed proider native type."

This occurs with a date value - the stored procedure is filled from XML data the Web Service receives from the client program by a generic subroutine that recognizes the applicable table names and primary key column names, selecting the appropriate stored procedure to use, but may or may not have data to update for each field. I have tried explicitly setting the SQLDBType for the parameter as the parametercollection is filled, but still get the ArgumentException. I also tried changing the parameter type to varchar(20), and having the stored procedure convert it to datetime, but still got the exception.

I know the ultimate solution will probably be to build the data Web Service within SQL 2005, but what can I do to get this working until I have time for that conversion?

Thanks for any help.

Darrell Escola

UPDATE - I was able to use the stored procedures to manually update the database using data that should have passed through the web service, so the problem is within the web service on .NET 2.0 - I think 2.0 is much stricter on data types than was 1.1. The SPs create dynamic SQL that is then executed - I know this is not the best security, hence my eventual interest in creating an end-point within SQL 2005 to handle the data in a more secure manner.

Darrell

|||

I finally got the web service to update through the stored procedure. I was able to access the stored procedure by specifically casting the data types for each parameter using a subroutine similar to that shown below, but the web service was still throwing an exception on the output value type, which I use to get the actual primary key value for the new record, so the off-line client can properly map its dependent records to the proper key value before inserting those dependent records:

Dim newID As SQLTypes.SQLInt32

I added a subroutine to determine the data type for each column used in the update:

Private Sub AddSPParameter(ByRef cmd as SQLClient.SQLCommand, ByVal paramName as String, ByVal fieldValue as Object)

If paramName = "<stringColName1>" Or paramName = "<stringColName...>" Then

cmd.Parameters.AddWithValue(Ctype(Cstr(fieldValue), SQLTypes.SQLString).DbType = DbType.String

ElseIf paramName = "<otherTypeColName1>" Or paramName.IndexOf("...") > 0 Or paramName.EndsWith("...") Then

cmd.Parameters.AddWithValue(Ctype(Cstr(fieldValue), SQLTypes.SQL<otherType>).DbType = DbType.<otherType>

ElseIf ...

End If

This subroutine determines the column data type by matches on the column name, and then explicitly casting the parameter value to the applicable database data type.

I also had to explicitly cast the Return Value:

cmd.Parameters.AddWithValue("@.ReturnValue", Ctype(0, SQLTypes.SQLInt32)).DbType = SqlDbType.Int

cmd.Parameters("@.ReturnValue").Direction = ParameterDirection.ReturnValue

Lesson learned: Explicitly type all data values used.

Darrell

ADO.NET SP throws ArgumentException after migrating to SQL 2005 Standard

I just migrated a database from SQL 2000 Developer Edition on W2k Server to a production machine running SQL 2005 Standard on W2k3 Server Standard. A Web Service was also migrated from .NET 1.1 on the development machine to .NET 2.0 on the production machine.

Since the migration, a stored procedure that worked on SQL 2000/.NET 1.1 now throws a System.ArgumentException: "No mapping exists from object type System.RuntimeType to a known managed proider native type."

This occurs with a date value - the stored procedure is filled from XML data the Web Service receives from the client program by a generic subroutine that recognizes the applicable table names and primary key column names, selecting the appropriate stored procedure to use, but may or may not have data to update for each field. I have tried explicitly setting the SQLDBType for the parameter as the parametercollection is filled, but still get the ArgumentException. I also tried changing the parameter type to varchar(20), and having the stored procedure convert it to datetime, but still got the exception.

I know the ultimate solution will probably be to build the data Web Service within SQL 2005, but what can I do to get this working until I have time for that conversion?

Thanks for any help.

Darrell Escola

UPDATE - I was able to use the stored procedures to manually update the database using data that should have passed through the web service, so the problem is within the web service on .NET 2.0 - I think 2.0 is much stricter on data types than was 1.1. The SPs create dynamic SQL that is then executed - I know this is not the best security, hence my eventual interest in creating an end-point within SQL 2005 to handle the data in a more secure manner.

Darrell

|||

I finally got the web service to update through the stored procedure. I was able to access the stored procedure by specifically casting the data types for each parameter using a subroutine similar to that shown below, but the web service was still throwing an exception on the output value type, which I use to get the actual primary key value for the new record, so the off-line client can properly map its dependent records to the proper key value before inserting those dependent records:

Dim newID As SQLTypes.SQLInt32

I added a subroutine to determine the data type for each column used in the update:

Private Sub AddSPParameter(ByRef cmd as SQLClient.SQLCommand, ByVal paramName as String, ByVal fieldValue as Object)

If paramName = "<stringColName1>" Or paramName = "<stringColName...>" Then

cmd.Parameters.AddWithValue(Ctype(Cstr(fieldValue), SQLTypes.SQLString).DbType = DbType.String

ElseIf paramName = "<otherTypeColName1>" Or paramName.IndexOf("...") > 0 Or paramName.EndsWith("...") Then

cmd.Parameters.AddWithValue(Ctype(Cstr(fieldValue), SQLTypes.SQL<otherType>).DbType = DbType.<otherType>

ElseIf ...

End If

This subroutine determines the column data type by matches on the column name, and then explicitly casting the parameter value to the applicable database data type.

I also had to explicitly cast the Return Value:

cmd.Parameters.AddWithValue("@.ReturnValue", Ctype(0, SQLTypes.SQLInt32)).DbType = SqlDbType.Int

cmd.Parameters("@.ReturnValue").Direction = ParameterDirection.ReturnValue

Lesson learned: Explicitly type all data values used.

Darrell