Archive data suggestion

I have a table contains huge rows of data. Performance issue raised. I am
thinking archive some data so that the table will not be that big. The most
convience way is move it to another table. The problem is: will this solve
my performance problem? or I need to move it to another database to reduce
the database size?

Regards,
TrueNoTaking a chunk of your data out of a table will certainly improve
performance on queries against that table, if users have to access data
that contains data from both the old and new tables performance will
suffer. THink of it this way, if users are looking for a needle in a
haystack, decreasing the size of the haystack will decrease the length
of time to find the needle.

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||EK9 (a@.a.com) writes:
> I have a table contains huge rows of data. Performance issue raised. I
> am thinking archive some data so that the table will not be that big.
> The most convience way is move it to another table. The problem is: will
> this solve my performance problem? or I need to move it to another
> database to reduce the database size?

Whether you put the archive table in the same or another database
does not affect performance for queries. It could reduce time for
backup and restore though.

However, it is far from certain than archiving data is the best way.
Maybe you need to review which indexes you have on the table.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

arabic and hebrew search

am having a problem in searching the database which contains arabic and hebrew text, while the english search works just fine, the arabic and hebrew always return no match. the data is stored and utf-8 the website is atwww.allforpeace.org .

is their a way to encode the text or what ever ...!! i need help

can u provide some code plz

Regards

|||

Hi,

If you're searching with parameters, make sure that the parameter type is NVarChar or NChar which accepts unicode. Or the search will not return good results.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

Applying SQL 2005 SP1 to a local copy of Workstation Components

I have installed the Workstation components on my local PC, which also contains VS 2005 and SQL Express. I installed the WS components so that I could run the full blown version of SQL Management Studio from my PC, to remove SQL 2005 servers.

I tried to update the Workstation components to SP1, but the SP1 package stops at the Setup Support Files, step and terminates claiming the package does not support SQL Express. I realize the package is not intended for Express, I simply need to update Management studio.

I tried running the Post SP1 Tools hotfix and it complains that it will not update SP0 of the tools.

I have successfully updated Express to SP1 and applied the Post hotfixes as well. Is there any way to patch just Management studio on a system with Express installed?

Thank,
Jim

Jim, were you able to get this installed? Your scenario sounds valid for sure and I'd like to hear if you got this resolved.

Thanks,

Sam

|||Sam,

No I never got this installed.... I ended up removing the Workstation components and installing Management Studio Express. I wanted to keep the build of mgt. studio inline with the build of SQL Server 2005. I'm hurting now as the management tools are missing from the express version.

I'm sorry about the delay in responding to this. I stopped watching the post when I didn't get any answers. I would really like to get this problem fixed....

Jim

Applying SQL 2005 SP1 to a local copy of Workstation Components

I have installed the Workstation components on my local PC, which also contains VS 2005 and SQL Express. I installed the WS components so that I could run the full blown version of SQL Management Studio from my PC, to remove SQL 2005 servers.

I tried to update the Workstation components to SP1, but the SP1 package stops at the Setup Support Files, step and terminates claiming the package does not support SQL Express. I realize the package is not intended for Express, I simply need to update Management studio.

I tried running the Post SP1 Tools hotfix and it complains that it will not update SP0 of the tools.

I have successfully updated Express to SP1 and applied the Post hotfixes as well. Is there any way to patch just Management studio on a system with Express installed?

Thank,
Jim

Jim, were you able to get this installed? Your scenario sounds valid for sure and I'd like to hear if you got this resolved.

Thanks,

Sam

|||Sam,

No I never got this installed.... I ended up removing the Workstation components and installing Management Studio Express. I wanted to keep the build of mgt. studio inline with the build of SQL Server 2005. I'm hurting now as the management tools are missing from the express version.

I'm sorry about the delay in responding to this. I stopped watching the post when I didn't get any answers. I would really like to get this problem fixed....

Jim

Applying SQL 2005 SP1 to a local copy of Workstation Components

I have installed the Workstation components on my local PC, which also contains VS 2005 and SQL Express. I installed the WS components so that I could run the full blown version of SQL Management Studio from my PC, to remove SQL 2005 servers.

I tried to update the Workstation components to SP1, but the SP1 package stops at the Setup Support Files, step and terminates claiming the package does not support SQL Express. I realize the package is not intended for Express, I simply need to update Management studio.

I tried running the Post SP1 Tools hotfix and it complains that it will not update SP0 of the tools.

I have successfully updated Express to SP1 and applied the Post hotfixes as well. Is there any way to patch just Management studio on a system with Express installed?

Thank,
Jim

Jim, were you able to get this installed? Your scenario sounds valid for sure and I'd like to hear if you got this resolved.

Thanks,

Sam

|||Sam,

No I never got this installed.... I ended up removing the Workstation components and installing Management Studio Express. I wanted to keep the build of mgt. studio inline with the build of SQL Server 2005. I'm hurting now as the management tools are missing from the express version.

I'm sorry about the delay in responding to this. I stopped watching the post when I didn't get any answers. I would really like to get this problem fixed....

Jim

Apply a long list of SQL Statements

I just received a long text file that contains all statements required to
create all necessary tables as well as inserting all the data into this
table
Using the Enterprise Manager how can I apply all these statements?
Thank you,
SamuelSamuel
> Using the Enterprise Manager how can I apply all these statements?
Why ? open a query analyzer and run this statement (modify it for your
needs)
EXEC master..xp_cmdshell
'osql -S<yourServer> -U<user> -P<password> -iC:\File.sql -n '
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:O1$a3wzfGHA.2032@.TK2MSFTNGP02.phx.gbl...
>I just received a long text file that contains all statements required to
>create all necessary tables as well as inserting all the data into this
>table
> Using the Enterprise Manager how can I apply all these statements?
> Thank you,
> Samuel
>|||copy the info into query analyzer and execute it there.
"Samuel Shulman" wrote:
> I just received a long text file that contains all statements required to
> create all necessary tables as well as inserting all the data into this
> table
> Using the Enterprise Manager how can I apply all these statements?
> Thank you,
> Samuel
>
>|||On Wed, 24 May 2006 17:22:51 +0300, Uri Dimant wrote:
>Samuel
>> Using the Enterprise Manager how can I apply all these statements?
>Why ? open a query analyzer and run this statement (modify it for your
>needs)
>EXEC master..xp_cmdshell
>'osql -S<yourServer> -U<user> -P<password> -iC:\File.sql -n '
Hi Uri,
Ir you're going to use osql.exe, why use Query Analyzer at all? Just
open a DOS prompt (or command window, or whatever it is called now).
For better ability to view the output, I'd recommend either adding an
output file to the osql command (using the -o parameter), or switching
to Quary Analyzer, copying in the text of the script, then hitting the
Execute button.
--
Hugo Kornelis, SQL Server MVP|||Hugo
> Ir you're going to use osql.exe, why use Query Analyzer at all? Just
> open a DOS prompt (or command window, or whatever it is called now).
Don't you see people who was wrapping this command into a stored procedure
and run in within a job or whatever?
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:a0h972p5rpockl0n8v38sabjlbkg57ejls@.4ax.com...
> On Wed, 24 May 2006 17:22:51 +0300, Uri Dimant wrote:
>>Samuel
>> Using the Enterprise Manager how can I apply all these statements?
>>Why ? open a query analyzer and run this statement (modify it for your
>>needs)
>>EXEC master..xp_cmdshell
>>'osql -S<yourServer> -U<user> -P<password> -iC:\File.sql -n '
> Hi Uri,
> Ir you're going to use osql.exe, why use Query Analyzer at all? Just
> open a DOS prompt (or command window, or whatever it is called now).
> For better ability to view the output, I'd recommend either adding an
> output file to the osql command (using the -o parameter), or switching
> to Quary Analyzer, copying in the text of the script, then hitting the
> Execute button.
> --
> Hugo Kornelis, SQL Server MVP|||On Thu, 25 May 2006 09:43:06 +0300, Uri Dimant wrote:
>Hugo
>> Ir you're going to use osql.exe, why use Query Analyzer at all? Just
>> open a DOS prompt (or command window, or whatever it is called now).
>Don't you see people who was wrapping this command into a stored procedure
>and run in within a job or whatever?
Hi Uri,
Since Samuel asked "how to apply these statements (...) using Enterprise
Manager", I figured he wanted to do it manually, not to schedule it.
Also, scripts that contain CREATE TABLE statements should normally run
just once.
--
Hugo Kornelis, SQL Server MVP

Apply a long list of SQL Statements

I just received a long text file that contains all statements required to
create all necessary tables as well as inserting all the data into this
table
Using the Enterprise Manager how can I apply all these statements?
Thank you,
SamuelSamuel
> Using the Enterprise Manager how can I apply all these statements?
Why ? open a query analyzer and run this statement (modify it for your
needs)
EXEC master..xp_cmdshell
'osql -S<yourServer> -U<user> -P<password> -iC:\File.sql -n '
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:O1$a3wzfGHA.2032@.TK2MSFTNGP02.phx.gbl...
>I just received a long text file that contains all statements required to
>create all necessary tables as well as inserting all the data into this
>table
> Using the Enterprise Manager how can I apply all these statements?
> Thank you,
> Samuel
>|||copy the info into query analyzer and execute it there.
"Samuel Shulman" wrote:

> I just received a long text file that contains all statements required to
> create all necessary tables as well as inserting all the data into this
> table
> Using the Enterprise Manager how can I apply all these statements?
> Thank you,
> Samuel
>
>|||On Wed, 24 May 2006 17:22:51 +0300, Uri Dimant wrote:

>Samuel
>Why ? open a query analyzer and run this statement (modify it for your
>needs)
>EXEC master..xp_cmdshell
>'osql -S<yourServer> -U<user> -P<password> -iC:\File.sql -n '
Hi Uri,
Ir you're going to use osql.exe, why use Query Analyzer at all? Just
open a DOS prompt (or command window, or whatever it is called now).
For better ability to view the output, I'd recommend either adding an
output file to the osql command (using the -o parameter), or switching
to Quary Analyzer, copying in the text of the script, then hitting the
Execute button.
Hugo Kornelis, SQL Server MVP|||Hugo
> Ir you're going to use osql.exe, why use Query Analyzer at all? Just
> open a DOS prompt (or command window, or whatever it is called now).
Don't you see people who was wrapping this command into a stored procedure
and run in within a job or whatever?
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:a0h972p5rpockl0n8v38sabjlbkg57ejls@.
4ax.com...
> On Wed, 24 May 2006 17:22:51 +0300, Uri Dimant wrote:
>
> Hi Uri,
> Ir you're going to use osql.exe, why use Query Analyzer at all? Just
> open a DOS prompt (or command window, or whatever it is called now).
> For better ability to view the output, I'd recommend either adding an
> output file to the osql command (using the -o parameter), or switching
> to Quary Analyzer, copying in the text of the script, then hitting the
> Execute button.
> --
> Hugo Kornelis, SQL Server MVP|||On Thu, 25 May 2006 09:43:06 +0300, Uri Dimant wrote:

>Hugo
>Don't you see people who was wrapping this command into a stored procedure
>and run in within a job or whatever?
Hi Uri,
Since Samuel asked "how to apply these statements (...) using Enterprise
Manager", I figured he wanted to do it manually, not to schedule it.
Also, scripts that contain CREATE TABLE statements should normally run
just once.
Hugo Kornelis, SQL Server MVP

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

Appending to a record

I have created a Table that contains a WorksheetID field and a SalesNotes field. I can successfully populate that table but I am uncertain as to how I can append to the SalesNotes record.

I want to keep the orginal notes but populate addtional notes.

Would I do this by using the UPDATE call?

Many Thanks

T

Hi,

you explanation is a bit unclear, but if you want to fill the table with additional data which can′t be filled during the initial load, you can sure use an update statement to modify the exisiting records.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Appending to a record

I have created a Table that contains a WorksheetID field and a SalesNotes field. I can successfully populate that table but I am uncertain as to how I can append to the SalesNotes record.

I want to keep the orginal notes but populate addtional notes.

Would I do this by using the UPDATE call?

Many Thanks

T

Hi,

you explanation is a bit unclear, but if you want to fill the table with additional data which can′t be filled during the initial load, you can sure use an update statement to modify the exisiting records.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

append data from a text file

How can I append data to an existing SQL Express table from a text file? The file contains over 50MBs of data. This will be a recurring task so naturally I would like to automate this. I am familiar with programming VC++ and VB but am not sure where to begin.

Since it's a larger file, you could use the BULK INSERT command:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_4fec.asp

Or you could use bcp from a command line:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_bcp_61et.asp

Hope this helps,
Josh Lindenmuth

Apostrphe Usage

How do you allow clients to enter text that contains an apostrophe?
Thanks!since the udfunctions were invented, it's not more a problem.|||...my clients are having trouble entering data if it contains apostrophes. Here is the code that they are using:

sql = "INSERT INTO TrainingData (PartID, TrngDate, TimeDay, Effort, UnitMeas, Partners, Comments, DateSubmt) "

sql = sql & "VALUES (" & Session("partid") & ", '" & CDate(strDateRun) & "', '" & Request.Form("ampm") & "', " & CInt(strEffort)

sql = sql & ", '" & strUnitMeas & "', '" & Request.Form("partners") & "', '" & Request.Form("comments") & "', '" & Date & "')"

Set rs = conn.Execute(sql)
Set rs =Nothing

If the comments or partners field contains an apostrophe, we seem to have a problem. How should I modify my code?

Thanks for your time!!!!|||If you need to insert apostrophies in SQL, make it a double apostrophie.
Like...

Insert into Table (Field1) Values ('That''s That')

In VB, just use:
Replace(sSQL,"'","''")

Works like a charm

Hope this helps.

Apostrophe in char and varchar columns

HI all,
When trying to insert text into a char or varchar column that contains an
apostrophe ' I get an error message
"the value you entered is not consistant with the datatype or length of the
colum"
I know the length is not a problem, and I'm sure that ' is a char value.
How do I deal with this.
I need to import text type date, not long less thatn 100 char, from anothe
database into an sql table
Thanks
RobertOk figured it out. Makes perfect sense
Thanks
"Robert Bravery" <me@.u.com> wrote in message
news:eBAeJKrLGHA.536@.TK2MSFTNGP09.phx.gbl...
> HI all,
> When trying to insert text into a char or varchar column that contains an
> apostrophe ' I get an error message
> "the value you entered is not consistant with the datatype or length of
the
> colum"
> I know the length is not a problem, and I'm sure that ' is a char value.
> How do I deal with this.
> I need to import text type date, not long less thatn 100 char, from anothe
> database into an sql table
> Thanks
> Robert
>