Default charset in sqlserver 2000? / jtds

Hi,

I am writing to a text column in my SQL Server 2000 database. The text
comes from a web form in my java web application, where the character
encoding is ISO-8859-1. (I have no control over the charset, my app is
a plugin inside another app.)
Characters such as (ascii 128) and '(ascii 146) are inserted into
the db as '?'.

I'm connecting using the free jtds driver, and I'm not specifying any
details about charsets in my usage of the driver.

Can anyone tell me what the default charset in sqlserver 2000 is?
Should I be specifying this charset when using my driver?
Thanks.downlode@.gmail.com wrote:
> Hi,
> I am writing to a text column in my SQL Server 2000 database. The text
> comes from a web form in my java web application, where the character
> encoding is ISO-8859-1. (I have no control over the charset, my app is
> a plugin inside another app.)
> Characters such as (ascii 128) and '(ascii 146) are inserted into
> the db as '?'.
> I'm connecting using the free jtds driver, and I'm not specifying any
> details about charsets in my usage of the driver.
> Can anyone tell me what the default charset in sqlserver 2000 is?
> Should I be specifying this charset when using my driver?
> Thanks.

You probably want to use ntext instead of text, nvarchar instead of
varchar, etc.|||(downlode@.gmail.com) writes:
> I am writing to a text column in my SQL Server 2000 database. The text
> comes from a web form in my java web application, where the character
> encoding is ISO-8859-1. (I have no control over the charset, my app is
> a plugin inside another app.)
> Characters such as ?(ascii 128) and '(ascii 146) are inserted into
> the db as '?'.

Hm, in iso-8859-1, the slots 128-159 not graphic characters. In Windows-
1252, Microsoft's extension of 8859-1, some of them are indeed graphic.

> I'm connecting using the free jtds driver, and I'm not specifying any
> details about charsets in my usage of the driver.
> Can anyone tell me what the default charset in sqlserver 2000 is?

No, because this depends on the regional settings of the machine. For
instance, if I install SQL Server on my machine, and do not make any
selection, I will get Finnish_Swedish_CI_AS, which implies code page
1252. People in Poland are likely to get Polish_CI_AS, which implies
code page 1250. And that's only the default. This can be overridden
at installation. And then the collation can be set independently by
column.

So start doing

SELECT serverproperty('Collation') -- Server default collation.
SELECT databasepropertyex('db', 'Collation') -- Database default

And then use sp_help to determine the coilations of the columns you
are working with. If you don't know which code page a certain collation
has, there is a function Collationproperty() for this.

If the columns are of different code pages, you will have to use
Unicode somewhere on the way, and as Trevor said, ntext nvarchar are
probably better options.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi,
sorry for the late follow up to this.
My database has the same collation throughout -
SQL_Latin1_General_CP1_CI_AS
The columns share this collation.
Even when I use a preparedStatement, ensuring that the outgoing text is
treated as Unicode by the free jtds driver, I get the same problems.

I am stumped by this one.

If I change my text column to an ntext column, will this affect the
existing entries?
Thanks,
Mike|||(downlode@.gmail.com) writes:
> sorry for the late follow up to this.
> My database has the same collation throughout -
> SQL_Latin1_General_CP1_CI_AS
> The columns share this collation.
> Even when I use a preparedStatement, ensuring that the outgoing text is
> treated as Unicode by the free jtds driver, I get the same problems.
> I am stumped by this one.

Since SQL_Latin1_General_CP1_CI_AS is share code page with iso-8859-1,
it's indeed a little funny. But as I noted in my previous post, the
characters you have problem with are not in iso-8859-1 - these code
points are control characters to 8859-1. In Windows Latin-1 they are
indeed printable characters.

My guess is that the free jtds takes a strict definiton of what is
8859-1. But I don't it, so you should inquire in a forum for that driver.

> If I change my text column to an ntext column, will this affect the
> existing entries?

No.

--
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.mspxsql

Default ASPNETDB database

Hi

I want to set up my web app to use a web parts personalization, I notice that a default database named aspnetdb is made, I also made one in my sql server using the aspnet_regsql.exe to make it. Now what I need to find out is how to make my app point to the database in the sql server instead of making that default one.

Thanks

You need to modify your connectionstring in the web.config to point to the instance of the DB you wish to connect to.

Marco

Please mark as answered if this helps!

|||

Hi

thanks for the response, how exactly should I use the connection string and where in the file?

thanks

|||

Hello - are you using VWD? Your web.config file will be stored where all your .aspx files are stored. Open the file and make the below changes (in bold)

<connectionStrings>

<addname="DatabaseNameHere"connectionString="Data Source=ComputerNameHere\SQLInstanceNameHere;Initial Catalog=DatabaseNameHere;Integrated Security=True"providerName="System.Data.SqlClient"/>

</connectionStrings>

Marco

|||

Hi, thanks for the string, the connection works but the app still uses that local aspnetbd as the default database, any ideas as to why.

thanks

|||

Hello - try adding a customized membership provider in your web.config, which if implemented as below is just a copy of the default provider but with a different name, Then point the customized membership provider to your connection string. Read this article which fully explains what your are trying to do -http://aspnet.4guysfromrolla.com/articles/120705-1.aspx

<membershipdefaultProvider="CustomizedMembershipProvider">

<providers>

<addname="CustomizedMembershipProvider"type="System.Web.Security.SqlMembershipProvider"connectionStringName="XXXXXXXX"applicationName="XXXXXXXXXX"/>

</providers>

</membership>

|||

Thanks that put me in the right path now im getting a error on the connection string

ERROR :The specified connectionStringName, 'xxxx', was not registered.

Any ideas,

Thanks

|||

Hello - have you replaced the 'xxxx' with the name of your connection string? the name of your connection string is in your web.config

<connectionStrings>

<addname="XXXXX" - this is where the name of your connection string will be.

Marco

|||

Hi, thanks for all your help again, that set me on the right path and I found the solution.

What I found out is that the machine.config file was looking for a connection named LocalSqlServer so in order to override that I had to clear that connection, I was using the <remove> element to remove the LocalSqlServer but didn't seem to work, so I just used the <clear/>, also I named the new connection LocalSqlServer because without it it was giving me a "connectionstring not register" error, so as soon as I named it LocalSqlServer it went through. I'm not sure if it can go in with a different name as every time I tried to name it differently it gave me errors, it was looking for LocalSqlServer regardless. I also had to give the Aspnet user access to that database.

Here is a sample of what I used

<connectionStrings
<clear/>
<add name="LocalSqlServer" connectionString="Data Source=path/;database=aspnetdb;Integrated Security=True" providerName="System.Data.SqlClient" /
</connectionStrings
<system.web
<roleManager enabled="true" />
<compilation debug="true" strict="false" explicit="true"/
<authentication mode="Forms" /
<webParts>
<personalization defaultProvider="AspNetSqlPersonalizationProvider">
<providers
<clear/>
<add connectionStringName="LocalSqlServer"
name="AspNetSqlPersonalizationProvider" type="System.Web.UI.WebControls.WebParts.SqlPersonalizationProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" applicationName="/appname" /
</providers>
</personalization>
</webParts
</system.web
</configuration>

Maybe this might help someone else.

Thanks agian for your help

Decreasing the maximum space allocated to the Databse

Hello All,
I have My databse on SQL Server at the Web with an space allocation of 800
MB (500 MB for Datafile and 300 MB for the LOG file ). I keep On Uploading t
he Data in the Database. This has caused the maximum size allocated
to my Database exceed, which disallows me for further Uploading of data. To
Come out of the Problem I opted for taking the regular backup of the databas
e which would truncarte the Log fle, But still I cant decrease the
maximum size of the Log file since almost 300 MB is used wile doing transact
ion for the 500 MB datafiles. I also tried deleting the old/unused data fro
m the datafile , but this did not resulted in the decrease of size of
the datafiles. is there any way I can decrease the maximum space allocated t
o the database. I can afford to deleate certain History data which is of no
use to me.
Regards
JaiHi,
1. Perform the Transaction log backup ( BACKUP LOG DBNAME to
disk='c:\backup\dbname.TRN')
2. Shrink the database (Refer Books online for DBCC SHRINKDATABASE and DBCC
SHRINKFILE)
3. Before doing the step 2, Better make the database to restricted mode
use master
go
alter database dbname set RESTRICTED_USER with rollback immediate
go
DBCC SHRINKDATABASE oR DBCC SHRINKFILE
Have a look into the below article on shrinking the Transaction log file,
http://support.microsoft.com/defaul...kb;en-us;272318
Thanks
Hari
MCDBA
"Jai" <jai.s@.sagainfotech.com> wrote in message
news:4805D811-29D2-4973-ABD8-51CD36F9A1EB@.microsoft.com...
> Hello All,
> I have My databse on SQL Server at the Web with an space allocation of
800 MB (500 MB for Datafile and 300 MB for the LOG file ). I keep On
Uploading the Data in the Database. This has caused the maximum size
allocated
> to my Database exceed, which disallows me for further Uploading of data.
To Come out of the Problem I opted for taking the regular backup of the
database which would truncarte the Log fle, But still I cant decrease the
> maximum size of the Log file since almost 300 MB is used wile doing
transaction for the 500 MB datafiles. I also tried deleting the old/unused
data from the datafile , but this did not resulted in the decrease of size
of
> the datafiles. is there any way I can decrease the maximum space allocated
to the database. I can afford to deleate certain History data which is of no
use to me.
>
> Regards
> Jai|||Thanks for the reply..
Will you explain me that even if I delete substantial records from the data
base..why I did not get any decrease in the size of the mdf file ?
Regards,
Jai
-- Hari Prasad wrote: --
Hi,
1. Perform the Transaction log backup ( BACKUP LOG DBNAME to
disk='c:\backup\dbname.TRN')
2. Shrink the database (Refer Books online for DBCC SHRINKDATABASE and DBCC
SHRINKFILE)
3. Before doing the step 2, Better make the database to restricted mode
use master
go
alter database dbname set RESTRICTED_USER with rollback immediate
go
DBCC SHRINKDATABASE oR DBCC SHRINKFILE
Have a look into the below article on shrinking the Transaction log file,
http://support.microsoft.com/defaul...kb;en-us;272318
Thanks
Hari
MCDBA
"Jai" <jai.s@.sagainfotech.com> wrote in message
news:4805D811-29D2-4973-ABD8-51CD36F9A1EB@.microsoft.com...
> Hello All,
800 MB (500 MB for Datafile and 300 MB for the LOG file ). I keep On
Uploading the Data in the Database. This has caused the maximum size
allocated
> to my Database exceed, which disallows me for further Uploading of data.
To Come out of the Problem I opted for taking the regular backup of the
database which would truncarte the Log fle, But still I cant decrease the
> maximum size of the Log file since almost 300 MB is used wile doing
transaction for the 500 MB datafiles. I also tried deleting the old/unused
data from the datafile , but this did not resulted in the decrease of size
of
> the datafiles. is there any way I can decrease the maximum space allocated
to the database. I can afford to deleate certain History data which is of no
use to me.
> Jai

Debugging database errors with SQL Server Express

I am trying to debug a classic ASP application using Visual Web Developer Express and SQL Server Express editions. When my application tries to execute a stored procedure, I get the following error:

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available.

Is there a way for me to trace queries that are getting executed in SQL Server Express so I can see which stored procedure is failing? I seem to remember being able to do this with SQL Server 2000. Any other ideas on how to debug SQL Server errors like these using SQL Server Express?

TIA for the help.

Michael

Hi Michael,

There are a number of KB articles that mention this error, you should go to http://support.microsoft.com and search on your error to see if any of the existing articles address the issue. I found the following, fairly generic problem that produces this error, but without more informaiton it's just a shot in the dark:

269495 PRB: "Multiple-step OLE DB operation generated errors" when opening ADO connection
http://support.microsoft.com/default.aspx?scid=kb;EN-US;269495

You may also find more pople who can help with this type of problem in the ASP.net forums. You can find all the forums at http://forums.asp.net/ and the forum that is specifically about SQL Express in VWD at http://forums.asp.net/54/ShowForum.aspx.

For the record, SQL Profiler is not included in SQL Express. This was probably the tool you were using in SQL 2000.

Regards,

Mike Wachal
SQL Express team

-
Please mark your thread as Answered when you get your solution.

debug stored procedure

I created a stored procedure that is referenced by a SQLDataSource& DetailView on a web page. I tested the stored procedure in QueryAnalyzer & everyting looks good.
When I get to the page to display the detailview, nothing is displayed.I ran SQL Profiler, to see what was happening & the storedprocedure isn't being called.
I checked & rechecked the references to the SQLDataSource & even tested them in the configuration wizard.
I replaced the stored procedure with the query & it worked, except I had to 'hardcode' the variable values.

The stored procedure does return error results when they occur - how can I intercept them?

How can I determine what is happening with this stored procedure? Is there a way to trace it other than with SQL Profiler?

Thanks in advance.

OldSamIf your stored proc works fine in Query Analyzer and not from your app unless the parameters are hardcoded => your values from the app are not being properly communicated with the stored proc. You could do a response.write of the values you are passing to see whats being passed.|||You are correct. I found it just before your post arrived - one of my nvarchar fields was NULL.
I still wish there were a better way to see these values in debug mode. I suspect there is; I just don't know how to get to it.

Thanks

OldSam|||

If you have VS 2005 you can create a Database Project and supposedly you can debug stored procs. I havent yet done that though.

Dear Insert in to database from textbox in asp.net

I have 5 textboxs and 1 botton in web form. I want to click botton and insert all data in textbox to database that using sql server 2005. Please help meYou can use SqlCommand with parameters, as shown in this documentation:
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx