arabic characters are not saved (was "A serious problem ...- plz. help"

Dear all ..
i have a serious problem & all ur comments will be appreciated..

i have bought an ASP .NET publishing tool which i receieved an sql script with it to execute on either Ms sql server or MSDE .

i executed it on MSDE as i don't have Ms sql server on my windows dedicated server .

I wanted the tool for publishing (Arabic Language)content for a highly traffic soccer website..

After executing the sql script i tested the tool but i found arabic characters are not saved when i add articles .. they were saved as question marks (??).

so i re-executed the sql script on a new db after modifying every code containg (varchar) to (nvarchar) to support unicode & thus arabic.

it worked & i succeeded in saving arabic articles

BUT >>>>>>>>>>>>>

i found that only short arabic articles r saved fine while any article that reaches around (1 microsoft word page) is not saved well with arabic characters but saved as question marks ( ? ) .. !!

=======

so i checked the db tables using ASP.NET Enterprise manager & i found that

the field of article has (ntext) & infront of it number 16 ..

it seems that the (ntext) has a limit to wt it can save ..

so i believe there's a way which i don't know to make the (ntext) accepts long articles entry .

========

Here's the code in the original sql script i received with the tool & i hope u can guide me in details to any modification to do so that the ntext limit is raise to save any long arabic article.

========

code :

CREATE TABLE [dbo].[xlaANMarticles] (
[articleid] [int] IDENTITY (1, 1) NOT NULL ,
[posted] [nvarchar] (50) NOT NULL ,
[lastupdate] [nvarchar] (50) NOT NULL ,
[headline] [nvarchar] (350) NOT NULL ,
[headlinedate] [nvarchar] (255) NOT NULL ,
[startdate] [nvarchar] (50) NOT NULL ,
[enddate] [nvarchar] (50) NOT NULL ,
[source] [nvarchar] (255) NOT NULL ,
[summary] [nvarchar] (3000) NOT NULL ,
[articleurl] [nvarchar] (1000) NOT NULL ,
[article] [ntext] NOT NULL ,
[status] [tinyint] NOT NULL ,
[autoformat] [nvarchar] (50) NOT NULL ,
[publisherid] [int] NOT NULL ,
[clicks] [int] NOT NULL ,
[editor] [int] NOT NULL ,
[relatedid] [nvarchar] (2000) NOT NULL ,
[isfeatured] [nvarchar] (10) NULL ,
[keywords] [nvarchar] (255) NULL ,
[description] [nvarchar] (255) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

i highlighted the specific column for the article field with red color ..

i tried making it (nvarchar) but the sql manager i use saud it can't be done coz. there has to be a field for TEXTIMAGE coz. it's set to be (on) in the code.

waiting for ur help plz. ... i am desperate .. :confused:Masry-

I maximum length of NTEXT is 1,073,741,823 characters. I can't imagine a page of MS word document holding more than that limit. In addition, BOL says prefix the unicode character strings with N. You might want to try with N. Not sure what 16 means.|||The problem is that saving Arabic characters (like any unicode or 16 bit characters) requires a full 16 bit data path from begining to end. If any part of the path reverts back to 8 bit characters, then any character that isn't supported by the 16 bit to 8 bit translation will appear as a question mark.

Apparently something in the data path that handles the translation from Word documents larger than a given (roughly one page) threshold to a database column causes the data to pass through as 8 bit characters. The problem lies in isolating whatever that weak point is!

-PatP|||Masry,
BLOB values are not saved in row, but elsewhere in data file, unless you use sp_tableoption. The 16 in front of ntext column is only a pointer to where the actual ntext value is located.
But about your problem, I suggest to check the path that your data is traversing to be saved to DB. There might some variables or ... be used that cause the Unicode information be lost!
Regards,
Leila

Applying the updates to SQL Mobile Server?!

Dear MSDN Support,

Here i am back with an inquiry about the last process of Merge Replication between SQL Mobile 2005 and SQL Server 2005.

Well i have performed all the steps found in the tutorial of the SQL Mobile Edition Books Online, and everything went on so fine and smooth. Now when i deploy my application on the Pocket PC emulator all possible SQL queries work and modify the data only in the emulator, i had installed ActiveSync 4.0 and it synchronizes the SQL Mobile data with the SQL Mobile server only when i copy the database from the emulator to my desktop, and this way is not efficient to my project, i need a better way to synchronize the data between the database on the emulator and that on the SQL Mobile Server.

I'll be looking forward to getting your help as soon as possible.

Thank you for your attention.

Best Regards;

Just FYI: there's no “MSDN support” on these forums, all answers are provided by

volunteers including your fellow developers, MVPs and Microsoft's employees.

|||

Thank you for your notice, and thank you for spotting at my problem!

|||

Mr Ahmad,

Just checking - did you figure out how to perform replication from your emulator to SQL Server? I couldn't tell from yor last post if you had solved your issue and are doing ok now. Let us know.

Thanks,

Darren

|||

Dear Darren,

I appreciate your reply alot, regarding the replication from the emulator to SQL Server, i had written the following code to my project and i am calling the function synchronize from a simple button.Here is the code i wrote for the Sync.

Dim repl As New SqlCeReplication()

repl.InternetUrl = "http://TOSHIBAMAN/Clinic/sqlcesa30.dll"

repl.Publisher = "TOSHIBAMAN"

repl.PublisherDatabase = "Clinic"

repl.PublisherSecurityMode = SecurityType.NTAuthentication

repl.Publication = "Clinic"

repl.Subscriber = "Clinic"

repl.SubscriberConnectionString = "Data Source=\My Documents\Clinic.sdf; Max Database Size=128;Default Lock Escalation =100;"

Try

repl.AddSubscription(AddOption.ExistingDatabase)

repl.Synchronize()

Catch err As SqlCeException

MessageBox.Show(err.ToString)

End Try

When clicking the button Synchronize the emulator fires the following message:

The SQL Mobile subscription already exists. Publisher, PublisherDatabase,and Publication for
this subscription should be different from any existing subscription

So what i do is to sync the database in the emulator and that in the SQL Mobile through the ActiveSync 4.0 then i connect to my database in the SQL Mobile and expand Replication right click on the subscription and choose to Synchronize the subscription.

But after i read some threads here, and the books online i do believe that there is a way which synchronizes the subscription from the emulator to the SQL Mobile to the original database engine!!

I'll be looking forward to getting your comments and advices.

Thank you alot for your attention!

Best Regards;

|||

Mr Ahmed,

Your code is very close - here are some corrections:

1. if you choose to use NT Authentication, you will have to provide the InternetLogin and InternetPassword properties as well. I suggest you start out by just using anonymous authentication until you have the replication working and then add more security afterwards.

2. in the connection string, it is not necessary to say max database size = 128 and Lock Escalation = 100 because these are the default values for SQL Mobile

3. if you are trying to dynamically create the Clinic.sdf SQL Mobile database upon the first merge replication synchronize, then you want to use AddOption.CreateDatabase.

So some sample code would look like this:

Public Function GetReplication() As SqlCeReplication

Dim repl As SqlCeReplication = Nothing

Try
repl = New SqlCeReplication()

repl.InternetUrl = Configuration.GetAppSetting("REPLICATION_URL")
repl.Publisher = Configuration.GetAppSetting("REPLICATION_PUBLISHER")
repl.PublisherDatabase = Configuration.GetAppSetting("REPLICATION_PUBLISHER_DB")
repl.PublisherSecurityMode = SecurityType.NTAuthentication
repl.Publication = Configuration.GetAppSetting("REPLICATION_PUBLICATION")
repl.Subscriber = Configuration.GetAppSetting("REPLICATION_SUBSCRIBER")
repl.SubscriberConnectionString = Configuration.GetAppSetting("REPLICATION_SUBSCRIBER_CONNECTION_STRING")

If Not System.IO.File.Exists(_dbmsFile) Then
repl.AddSubscription(AddOption.CreateDatabase)
End If

Catch ex As Exception
DisplaySQLCEErrors(ex)
Return Nothing
End Try

Return repl

End Function

Where the value of _dbmsFile (in your case) is "\My Documents\Clinic.sdf"

The point of the code sample above is to show you the minumum set of properties you need to set on the SqlCeReplication object in order for it to work, as well as how to handle dynamically creating the SQL Mobile database the first time (if it doesn't already exist)

Since you are using an emulator, you need to ensure that the emulator's network settings are configured to communicate with IIS and SQL Server on the server (which I can see is the same development machine you are running the emulator on). So open Pocket Internet Explorer on the emulator and try the URL http://TOSHIBAMAN/Clinic/sqlcesa30.dll to verify that you can get to the SQL Mobile server tools directory on IIS.

Darren

sql

application with critical database

Dear all

I am a pretty new in the development world fresh from uni. I am doing development on a system that has a security database. Access to the data in that database is pretty important. So in case the main server where the database is stored for soem reason fails or gets compromised i need to have a second copy with the most recent data in that database and keep the application up and running. The data i have is stored in a SQL 2005 database. What are the recomended aproaches for acheiving this needed reliability?

Would running the SQL Agent every 2 minutes do the trick? And replicate the database to another server and then have asecondary deployment on that server running as a backup? Or are there any other means?

Any advice is apreciated.

Sincerely

See if below links can be useful.

http://msdn2.microsoft.com/en-us/library/ms151247.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

Application Role And SSRS

Hi dear reader

I made an application that uses a Sql Server 2005 Express DataBase.

In the database I made a application role.

When the user logs into my application I run this procedure:

If Not sqlConnectionCR Is Nothing Then

If Not sqlConnectionCR.State = ConnectionState.Open Then

sqlConnectionCR.Open()

SqlConnection.ClearAllPools()

ConsultasSqlCommand = New SqlCommand

ConsultasSqlCommand.CommandType = CommandType.Text

ConsultasSqlCommand.CommandText = "sp_setapprole 'appRole', 'drowssap"

ConsultasSqlCommand.Connection = sqlConnectionCR

ConsultasSqlCommand.ExecuteNonQuery()

End If

Else....

I understand that this procedure connects to my sqlserver database as my application role

Ok, so far no problems in reading and manipulating data.

The problem comes with the reports in my application. For example: I have a reportviewer with a serverreport but when I try to show the report gives an error about permissions and grant access....

I think that is because the Server Report uses the user account (domain/user) to read the database. No user (besides admin) has access permissions in the database (only admin and application role).

So, my cuestion is: How can I tell Report Server to use the application role to display reports?

Thank you for your time and help.

Giber

Your report application should also set the application role, which should have access to the database.

Thanks
Laurentiu

|||

I develop my Server Reports in SQL Server Business Intelligence (VS2005)

I can't seem to find where or how to tell BI how to connect to the DB using the application role.

To connect im using just: "Data Source=localhost\SQLEXPRESS;Initial Catalog=ConsultasSql" in the data view seccion of the report (rdl) designer.

Greetings

|||

I am not familiar with Server Reports or SQL Server Business Intelligence, but hopefully explaining how application roles work may be useful.

Application roles or approles are database-scoped principals that require no permissions (other than being able to access the database where they are defined) to be set as the execution context at run time, but require the knowledge of a password. Approles can only be set at ad-hoc level, this means that the call to sp_setapprole cannot be part of a stored procedure body or within a user defined transaction. More detailed information is available on BOL: Sp_setapprole http://msdn2.microsoft.com/en-us/library/ms188908.aspx

If the Server Reports definition allow you to define ad-hoc statements, it may be possible to call sp_setapprole as the first statement. I would recommend asking for information on how to customize these reports in the SQL Server Integration Services forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1).

An alternative could be to use the new impersonated mechanism (EXECUTE AS) or digital signatures in a stored procedure or user defined function to retrieve the data from the database without granting permissions beyond executing the SP. You can find more information on this topic in BOL, a good starting point is Context switching: http://msdn2.microsoft.com/en-us/library/ms188268.aspx

-Raul Garcia

SDE/T

SQL Server Engine

|||

Im putting this thread under SQL Reporting Services Forum.

Also Im going to check that Execute As clause.

Tnx a lot

Giber

Application Role And SSRS

Hi dear reader

I made an application that uses a Sql Server 2005 Express DataBase.

In the database I made a application role.

When the user logs into my application I run this procedure:

If Not sqlConnectionCR Is Nothing Then

If Not sqlConnectionCR.State = ConnectionState.Open Then

sqlConnectionCR.Open()

SqlConnection.ClearAllPools()

ConsultasSqlCommand = New SqlCommand

ConsultasSqlCommand.CommandType = CommandType.Text

ConsultasSqlCommand.CommandText = "sp_setapprole 'appRole', 'drowssap"

ConsultasSqlCommand.Connection = sqlConnectionCR

ConsultasSqlCommand.ExecuteNonQuery()

End If

Else....

I understand that this procedure connects to my sqlserver database as my application role

Ok, so far no problems in reading and manipulating data.

The problem comes with the reports in my application. For example: I have a reportviewer with a serverreport but when I try to show the report gives an error about permissions and grant access....

I think that is because the Server Report uses the user computer account (domain/user) to read the database. No user (besides admin and application role) has access permissions in the database.

So, my cuestion is: How can I tell Report Server to connect to the DB using the application role in the DB?

Thank you for your time and help.

Giber

I don't see how you could do this. You could try experimenting by placing the call to sp_setapprole within the query window just before the select to see if it would execute the query in the correct context. I'm not sure whether RS reuses connections to execute multiple dataset queries so you may need to put this in every query. Like I said, never tried it, so you'll need to experiment.|||

All my reports use stored procedures to populate the DataSet.

I put Execute As in the stored procedure to impersonate the user:

ALTER PROCEDURE [dbo].[usp_Cheque]

@.CRS nvarchar(50)

EXECUTE AS 'dbo'

AS

BEGIN

DECLARE @.Temp AS nvarchar(50)

SET NOCOUNT ON;

CREATE TABLE #Temp(CRS BigInt)

WHILE LEN(@.CRS) > 0

IF CHARINDEX(',',@.CRS) = 0

BEGIN

SET @.Temp = @.CRS

INSERT INTO #Temp VALUES(@.Temp)

SET @.CRS = ''

END

ELSE

BEGIN

SET @.Temp = LEFT(@.CRS,CHARINDEX(',',@.CRS)-1)

INSERT INTO #Temp VALUES(@.Temp)

SET @.CRS = RIGHT(@.CRS,LEN(@.CRS)-LEN(@.Temp)-1)

END

END

SELECT

Nombre,

NomCheque,

NumCR,

FechaCR,

Importe

FROM

CatProveedores INNER JOIN ContraRecibos

ON CatProveedores.CveProveedor = ContraRecibos.CveProveedor

WHERE

NumCR IN (SELECT CRS FROM #Temp)

But Im still getting that EXECUTE PERMISSION denied on usp_Cheques .....dbo' Error when trying to display the report

I have to give users Domain\Administrator Windows Role so they view the reports and then change their users back to Domain\Users when reporting is done...

|||

Let me try to explain it again (my english is not so good)

When a user try to log in the database (throught my application), the application run a setapprole. So the user is not the one connecting directly to the database, if fact, no user can connect to the database (only domain\administrators). Is the application role (that as all privileges) the one that give access to tables, sp, view, etc...

How can I do so in Reporting Services? RS tries to enter the database as the computer user, but as I said before, no computer user can enter the database, I need to tell RS that when trying to connect to the database, run the set approle command.

How do I do that?

Thank's for your attention.

Giber

|||

Hi Giber,

What are the credentials you are using for your datasource for stored procedures?

If you use sa or some admin role for data source credentials, the report should be able to run the stored procedure in the database. You can try running the report(preview) in local mode.

Also, if you are trying to control access for your reports, you can create different roles and groups and assign groups to these roles in SQL Server 2005 Reporting services. Or you can control the access for each report by giving appropriate permissions directly to that particular user or group. You will have to manuaaly ensure that the groups you make for Reporting Services match with the ones in SQl Server database.

Hope this will solve your problem.

Regards

Application respond slowly

Dear All
My SQL Server is contains 2 databases. Both of them are used by
different client/server application and each application uses several
stored procedure to query and update its database. The problem is my
users reports that those two application respond slowly.
I used SQL Profile, and here the result.
EventClass______________________TestData
SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted Update TableA Set Col1 =
SQL:BatchCompleted Update TableB Set Col2 = '23456..
SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted Update TableA Set Col1=
What should I do to improve the performance?
- by using views to query data or modify the stored procedure so that
they select data into temporary tables.
Pls give me your advice
Thanks
Robert Lie
There's a boundless number of potential issues and bottlenecks. Far more
information is needed to help with this. First try to gather some
perfromance stats. Is the server busy? Is there blocking? What
perfromance do you get when running in query analyzer?
"robert lie" <robert.lie24@.gmail.com> wrote in message
news:ebnyU$RZFHA.2884@.tk2msftngp13.phx.gbl...
> Dear All
> My SQL Server is contains 2 databases. Both of them are used by different
> client/server application and each application uses several stored
> procedure to query and update its database. The problem is my users
> reports that those two application respond slowly.
> I used SQL Profile, and here the result.
> EventClass______________________TestData
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1 =
> SQL:BatchCompleted Update TableB Set Col2 = '23456..
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1=
>
> What should I do to improve the performance?
> - by using views to query data or modify the stored procedure so that they
> select data into temporary tables.
> Pls give me your advice
> Thanks
> Robert Lie
|||As mentioned by Danny, there can be a lot of reason. Before you just jump to
Profiler, I'd investigate a bit more what the users means that the
applications respond slowly. It might help to know if it's something that
has happended all of a sudden or if performance has degraded slowly over
time. It will also be different things to look for if it's a new application
that just never has performed decent.
If performance has degraded slowly over time, I'd look at things like
missing statistic update, Index defragmentation, memory consumption,
Disksystem defragmention, logfile size (i.e. does it autogrow too often?),
disk space etc.
If performance has degraded all of a sudden, I'd more look for diskproblems,
disk space issues, server HW problems, CPU utilization etc. I'd then also
check what have been done to the server in terms of updates (Windows, SQL,
AntiVirus etc) to see if it could something that are consuming a lof of CPU.
If performance never has been decent with those applications I'd look at
more basic things like server configuration, RAID, disksystem, memory etc.
It could also be poorly designed applications, but if it's two applications
independent of each other, it's not very likely that they both suffer from
poor design.
I hope that some of the above can help you to troubleshoot the issues?
Regards
Steen
robert lie wrote:
> Dear All
> My SQL Server is contains 2 databases. Both of them are used by
> different client/server application and each application uses several
> stored procedure to query and update its database. The problem is my
> users reports that those two application respond slowly.
> I used SQL Profile, and here the result.
> EventClass______________________TestData
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1 =
> SQL:BatchCompleted Update TableB Set Col2 = '23456..
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1=
>
> What should I do to improve the performance?
> - by using views to query data or modify the stored procedure so that
> they select data into temporary tables.
> Pls give me your advice
> Thanks
> Robert Lie

Application respond slowly

Dear All
My SQL Server is contains 2 databases. Both of them are used by
different client/server application and each application uses several
stored procedure to query and update its database. The problem is my
users reports that those two application respond slowly.
I used SQL Profile, and here the result.
EventClass______________________TestData
SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted Update TableA Set Col1 = SQL:BatchCompleted Update TableB Set Col2 = '23456..
SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted Update TableA Set Col1=
What should I do to improve the performance?
- by using views to query data or modify the stored procedure so that
they select data into temporary tables.
Pls give me your advice
Thanks
Robert LieThere's a boundless number of potential issues and bottlenecks. Far more
information is needed to help with this. First try to gather some
perfromance stats. Is the server busy? Is there blocking? What
perfromance do you get when running in query analyzer?
"robert lie" <robert.lie24@.gmail.com> wrote in message
news:ebnyU$RZFHA.2884@.tk2msftngp13.phx.gbl...
> Dear All
> My SQL Server is contains 2 databases. Both of them are used by different
> client/server application and each application uses several stored
> procedure to query and update its database. The problem is my users
> reports that those two application respond slowly.
> I used SQL Profile, and here the result.
> EventClass______________________TestData
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1 => SQL:BatchCompleted Update TableB Set Col2 = '23456..
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1=>
> What should I do to improve the performance?
> - by using views to query data or modify the stored procedure so that they
> select data into temporary tables.
> Pls give me your advice
> Thanks
> Robert Lie|||As mentioned by Danny, there can be a lot of reason. Before you just jump to
Profiler, I'd investigate a bit more what the users means that the
applications respond slowly. It might help to know if it's something that
has happended all of a sudden or if performance has degraded slowly over
time. It will also be different things to look for if it's a new application
that just never has performed decent.
If performance has degraded slowly over time, I'd look at things like
missing statistic update, Index defragmentation, memory consumption,
Disksystem defragmention, logfile size (i.e. does it autogrow too often?),
disk space etc.
If performance has degraded all of a sudden, I'd more look for diskproblems,
disk space issues, server HW problems, CPU utilization etc. I'd then also
check what have been done to the server in terms of updates (Windows, SQL,
AntiVirus etc) to see if it could something that are consuming a lof of CPU.
If performance never has been decent with those applications I'd look at
more basic things like server configuration, RAID, disksystem, memory etc.
It could also be poorly designed applications, but if it's two applications
independent of each other, it's not very likely that they both suffer from
poor design.
I hope that some of the above can help you to troubleshoot the issues?
Regards
Steen
robert lie wrote:
> Dear All
> My SQL Server is contains 2 databases. Both of them are used by
> different client/server application and each application uses several
> stored procedure to query and update its database. The problem is my
> users reports that those two application respond slowly.
> I used SQL Profile, and here the result.
> EventClass______________________TestData
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1 => SQL:BatchCompleted Update TableB Set Col2 = '23456..
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1=>
> What should I do to improve the performance?
> - by using views to query data or modify the stored procedure so that
> they select data into temporary tables.
> Pls give me your advice
> Thanks
> Robert Lie

Application respond slowly

Dear All
My SQL Server is contains 2 databases. Both of them are used by
different client/server application and each application uses several
stored procedure to query and update its database. The problem is my
users reports that those two application respond slowly.
I used SQL Profile, and here the result.
EventClass______________________TestData
SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted Update TableA Set Col1 =
SQL:BatchCompleted Update TableB Set Col2 = '23456..
SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted Update TableA Set Col1=
What should I do to improve the performance?
- by using views to query data or modify the stored procedure so that
they select data into temporary tables.
Pls give me your advice
Thanks
Robert LieThere's a boundless number of potential issues and bottlenecks. Far more
information is needed to help with this. First try to gather some
perfromance stats. Is the server busy? Is there blocking? What
perfromance do you get when running in query analyzer?
"robert lie" <robert.lie24@.gmail.com> wrote in message
news:ebnyU$RZFHA.2884@.tk2msftngp13.phx.gbl...
> Dear All
> My SQL Server is contains 2 databases. Both of them are used by different
> client/server application and each application uses several stored
> procedure to query and update its database. The problem is my users
> reports that those two application respond slowly.
> I used SQL Profile, and here the result.
> EventClass______________________TestData
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1 =
> SQL:BatchCompleted Update TableB Set Col2 = '23456..
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1=
>
> What should I do to improve the performance?
> - by using views to query data or modify the stored procedure so that they
> select data into temporary tables.
> Pls give me your advice
> Thanks
> Robert Lie|||As mentioned by Danny, there can be a lot of reason. Before you just jump to
Profiler, I'd investigate a bit more what the users means that the
applications respond slowly. It might help to know if it's something that
has happended all of a sudden or if performance has degraded slowly over
time. It will also be different things to look for if it's a new application
that just never has performed decent.
If performance has degraded slowly over time, I'd look at things like
missing statistic update, Index defragmentation, memory consumption,
Disksystem defragmention, logfile size (i.e. does it autogrow too often?),
disk space etc.
If performance has degraded all of a sudden, I'd more look for diskproblems,
disk space issues, server HW problems, CPU utilization etc. I'd then also
check what have been done to the server in terms of updates (Windows, SQL,
AntiVirus etc) to see if it could something that are consuming a lof of CPU.
If performance never has been decent with those applications I'd look at
more basic things like server configuration, RAID, disksystem, memory etc.
It could also be poorly designed applications, but if it's two applications
independent of each other, it's not very likely that they both suffer from
poor design.
I hope that some of the above can help you to troubleshoot the issues?
Regards
Steen
robert lie wrote:
> Dear All
> My SQL Server is contains 2 databases. Both of them are used by
> different client/server application and each application uses several
> stored procedure to query and update its database. The problem is my
> users reports that those two application respond slowly.
> I used SQL Profile, and here the result.
> EventClass______________________TestData
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1 =
> SQL:BatchCompleted Update TableB Set Col2 = '23456..
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1=
>
> What should I do to improve the performance?
> - by using views to query data or modify the stored procedure so that
> they select data into temporary tables.
> Pls give me your advice
> Thanks
> Robert Lie

Application is very Slow

Dear All,

Finally I completed my project. Thanks all you helped me to do it.

Now I have the biggest problem.

In my application the Data grid is filled with Data from SQL server table which has large number of records. When I run my queries in SS Management Studio it runs very fast. To fill data to datagrid it takes lots of time. How can I reduce this time. How can I increase the performance of my Application.

Thanks,

Janaka

Try to disable debugging and trace in your web.config after deployment.

Also for large amount of data you can use a custom paging solution to paging through data to prevent getting all the data which for sure affect the performance.

|||

Thanks I did custom paging its amazing. Now ok.

Again another problem.

Users edit some records in Gridview and click update button. Data will be updated in SQL table. But I want to track what are the records actually edited.

Do you have any idea?

thanks,

Janaka

|||

You may do this in many ways:

- You can handle the GridView RowUpdated event and log the information to say Database table ( Data may be : current login user id , Update time stamp,record id)

|||

Hi,

You understood what I have to do clearly. But in multi editing grid view, row updating event is not firing. Because when page loading the grid is in editable mood and there is no edit button or update button inside the grid for each and every row.

Thanks,

Janaka

Application goes slow or not responding after few transaction

Dear all viewer ,

Thanks to everyone.

We are using -
MSSQL Server 2000 on windows 2000 advanced server.
PB 7.0 for client server front end tools.

Prior to few days our application works fine, rightnow it get slow or not responding after few (4/5) transaction. We don't know wht it is, If anyone has same experience so that is helpfull for us.

Thanks

R.MallMonitor your activity with SQL Profiler.|||Thanks for reply

How I can do it? Can you give me step by step methods?

R.Mall|||SELECT Profiler
FROM BooksOnline

or

SELECT Consultant
FROM YellowPages|||Thanks for right suggession, I will do it.

Thanks|||I wonder if he actually did that? :)

You can also find information about running Profiler from:

www.sql-server-performance.com
www.sqlteam.com
SQL Server 2000 Performance Tuning Technical Manual (Microsoft Press)

Appending Records to the Existing MS SQL EXPRESS SERVER Table.

Dear All,

I am Using MS SQL EXPRESS SERVER .I have installed all tools available to Express Edition site.

Now I have created my database on this .I have imported a table from my MS ACCESS database (Using ODBC Datasource).This table contains 10,000 records ,

Now I want to append 1 more access Table(5500 records) to the existing table having same fields.

How to do this.Can any body tell me?

Thanks and Regards

mukesh

Hi mukesh,

You can append the imported records into the existing table using SQL Server import & Export Wizard.

1.Select Your database from SQL Server Management Studio

2.Right Click on Database and go to the Task->Import Data menu Item

3. SQL Server Import & Export Wizard will be open., choose ur data source. as MicrosoftAccess, and select the MDB file

4.Press Next to Move onChoose a Destination Page, and select your Database Name from dropdown

5.Press Next to Move onSpecity Table Copy or QueryPage, and selectcopy data from one or more tables or viewradio button.

6. Press Next to Move onSelect Source Table and View , Select your Source Table , and Destination Table and then pressEdit button,Column Mappings dialog box will be open, chooseAppend rows to the destination tabelradion button option ( it will append the new records with existing records, in your case ur new 5500 records will be apended with existing 10,000 records )

7Press Next, and then Press Finish.Import Process will be started.

Thanks

Best Regards,

Muhammad AKhtar Shiekh

SQL Server Import & Export Wizard

|||

Hi mukesh,

The way you imported the first table just load the second table but with a different name i.e Table2 to the same database and then you can use the query

Lets Table_1 is having 10000

Table_2 is having 5500

----------------

Insert into Table_1

Select * from Table_2

----------------

the simplest way to do the stuff...

Satya

|||

Thanks Mr.Akhhttar

But sir in management studio I can't find "Import and Export option" there r these option,"detach","shrink","backup","restore"& generate scripts.

thanks and regards

mukesh

|||

Thanks a lot,Mr.Satya

Appending Backedup data During Restore Process

Dear All,

Can i append new database backup to the existing data, while restoring the backedup database? If so give me the solution. I had backups for 30 days backup and trying to restore all these bckups..

During the restore process, i had observed that the previsous data is getting deleted and new data is replaced on it (in the database). But i want to append the new data to the existing data.

Note : The backup format that i had taken for all these 30 days is of Full Backup (not differential backup)

Any solution(s) for the above stated...

Regards,Not possible using FULL BACKUP, you can use TLOG Backups to restore them on this server.

Anything wrong with the connection?

Dear all,
I have a program (written in VB6) running in a Win2000 server with SQL
server 2000 in it. However, the program runs very slow or even "Time Out
Expired" many times. My connection string is:
strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
Id=mySQLUser;Password=myPassword;"
On the other hand I tried to run the same program in my own PC (Win2000
professional) with SQL desktop version. The program runs very fast. Can
anyone advise what is the problem when the program is running in SQL server
2000?
Thanks a million!!
Ivan
On Mon, 7 Nov 2005 01:26:50 -0800, Ivan wrote:

> Dear all,
> I have a program (written in VB6) running in a Win2000 server with SQL
> server 2000 in it. However, the program runs very slow or even "Time Out
> Expired" many times. My connection string is:
> strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
> Id=mySQLUser;Password=myPassword;"
> On the other hand I tried to run the same program in my own PC (Win2000
> professional) with SQL desktop version. The program runs very fast. Can
> anyone advise what is the problem when the program is running in SQL server
> 2000?
> Thanks a million!!
> Ivan
Hello,
I'm afraid that you need to investigate more in depth. Probably nothing to
do with your connection. Try to use the SQL Server profiler to trace
queries sent to the server, and watch things like reads, CPU, duration on
this trace. Then isolate the queires that could cause problems and watch
their query plan. Search for indexes that could lack.
Look in Enterprise Manager (or doing a sp_who2, or sp_lock) the number of
connections, and if there are blocking locks. Have also a look at some
performance counters.
You'll need to search for hints. Info you gave is not sufficient to give a
clue of what could be the problem.
Good luck
Rudi Bruchez
MCDBA

Anything wrong with the connection?

Dear all,
I have a program (written in VB6) running in a Win2000 server with SQL
server 2000 in it. However, the program runs very slow or even "Time Out
Expired" many times. My connection string is:
strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
Id=mySQLUser;Password=myPassword;"
On the other hand I tried to run the same program in my own PC (Win2000
professional) with SQL desktop version. The program runs very fast. Can
anyone advise what is the problem when the program is running in SQL server
2000?
Thanks a million!!
IvanOn Mon, 7 Nov 2005 01:26:50 -0800, Ivan wrote:

> Dear all,
> I have a program (written in VB6) running in a Win2000 server with SQL
> server 2000 in it. However, the program runs very slow or even "Time Out
> Expired" many times. My connection string is:
> strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;Use
r
> Id=mySQLUser;Password=myPassword;"
> On the other hand I tried to run the same program in my own PC (Win2000
> professional) with SQL desktop version. The program runs very fast. Can
> anyone advise what is the problem when the program is running in SQL serve
r
> 2000?
> Thanks a million!!
> Ivan
Hello,
I'm afraid that you need to investigate more in depth. Probably nothing to
do with your connection. Try to use the SQL Server profiler to trace
queries sent to the server, and watch things like reads, CPU, duration on
this trace. Then isolate the queires that could cause problems and watch
their query plan. Search for indexes that could lack.
Look in Enterprise Manager (or doing a sp_who2, or sp_lock) the number of
connections, and if there are blocking locks. Have also a look at some
performance counters.
You'll need to search for hints. Info you gave is not sufficient to give a
clue of what could be the problem.
Good luck
Rudi Bruchez
MCDBA

Anything wrong with the connection?

Dear all,
I have a program (written in VB6) running in a Win2000 server with SQL
server 2000 in it. However, the program runs very slow or even "Time Out
Expired" many times. My connection string is:
strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
Id=mySQLUser;Password=myPassword;"
On the other hand I tried to run the same program in my own PC (Win2000
professional) with SQL desktop version. The program runs very fast. Can
anyone advise what is the problem when the program is running in SQL server
2000?
Thanks a million!!
IvanOn Mon, 7 Nov 2005 01:26:50 -0800, Ivan wrote:
> Dear all,
> I have a program (written in VB6) running in a Win2000 server with SQL
> server 2000 in it. However, the program runs very slow or even "Time Out
> Expired" many times. My connection string is:
> strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
> Id=mySQLUser;Password=myPassword;"
> On the other hand I tried to run the same program in my own PC (Win2000
> professional) with SQL desktop version. The program runs very fast. Can
> anyone advise what is the problem when the program is running in SQL server
> 2000?
> Thanks a million!!
> Ivan
Hello,
I'm afraid that you need to investigate more in depth. Probably nothing to
do with your connection. Try to use the SQL Server profiler to trace
queries sent to the server, and watch things like reads, CPU, duration on
this trace. Then isolate the queires that could cause problems and watch
their query plan. Search for indexes that could lack.
Look in Enterprise Manager (or doing a sp_who2, or sp_lock) the number of
connections, and if there are blocking locks. Have also a look at some
performance counters.
You'll need to search for hints. Info you gave is not sufficient to give a
clue of what could be the problem.
Good luck
--
Rudi Bruchez
MCDBA