|
If your using SQL Server 2005 you could use the Day() / Month() / Year() - Methods to get the specified part of the date. I don't know exactly whether these functions are available in SQL Server 2000, too.
|
|
|
|
|
thanks
You get the best out of others when you give the best of yourself.
|
|
|
|
|
Don't delete your original message. It is rude!
|
|
|
|
|
Is there a clear cut guide, tool, etc... for doing a migration from MySQL to SQL Server 2000 or 2005?
I have to migrate a small DB and am looking for an easy way to do it if such a thing exists.
|
|
|
|
|
Hi,
Our website provide users with a forum where they can enter messages. So, typically, a user would enter a message, click submit, and their message would be displayed on the forum.
The db that stores these msgs uses a varchar field. Now, we're adding a facility to convert any urls that are entered to links by adding 'a href' tags, so that when users see messages with urls, those urls appear as links, and they can click on them. The problem is that if a user enters a message of 8K chars, then we add 'a href' tags to any urls they have, that makes the message length > 8K, preventing us from storing it in our db. (Please note that we do need to add HTML tags for other reasons as well, not just to convert urls to links.)
We thought about adding 'a href' tags during pre-render, thus avoiding storing html in the db, but found that doing such string manipulations, while a user is waiting to see their post displayed on the forum, was expensive. Timing tests revealed that the time to render such pages more than doubled.
I have solved such problems previously by adding an extra field in the db to store any overflow data, that is, data that appears after the first 8K chars.
However, I learnt of sql server 2005's varchar(max) datatype, and 'am wondering if the best way to solve this problem would be to replace the varchar field with varchar(max).
Thanks in advance for any advice.
Shefali
|
|
|
|
|
shefali_sinha wrote: We thought about adding 'a href' tags during pre-render, thus avoiding storing html in the db, but found that doing such string manipulations, while a user is waiting to see their post displayed on the forum, was expensive. Timing tests revealed that the time to render such pages more than doubled.
To be honest, that sounds odd. If you were using a regular expression to detect URIs then you might want to switch to a cheaper option, or consider using a precompiled regex.
shefali_sinha wrote: However, I learnt of sql server 2005's varchar(max) datatype, and 'am wondering if the best way to solve this problem would be to replace the varchar field with varchar(max).
Yes, that would be a solution.
Be aware that memo-style fields are stored differently (physically) to standard fields. I doubt this will make a difference to a typical forum database though.
|
|
|
|
|
Thanks a lot.
Haven't had a chance to look into pre-compiles regex, but will look into it.
I simply converted a varchar(8000) field to varchar(max) and another ntext field to varchar(max), the latter conversion was for the sake of better perfromance of a varchar(max) vs. ntext, and my world stayed perfect. Testing reveled no problems. To do the conversion in sql server 2005, I used the statement:
alter table alter column <column name=""> varchar(max).
Just thought I should document this here in case it helps someone else.
Shefali
|
|
|
|
|
ntext -> nvarchar(max). Otherwise you'll blow away any unicode data....
|
|
|
|
|
Hi,
I have a sql script that returns the value of one query to a table variable (Table A). I then compare this table variable to a physical table (Table B) in my database to retrieve all values in B that are not in A. I am able to see the results via the query analyzer but would like to be able to store these results in a view (for reporting purposes). Unfortunately, I am unable to Create the View (as it must be the first statement in the batch). Secondly, I am using variables that can be only used in the scope of the sql statement that it was created (which means that I cannot reference a declared table variable within scope of a newly created view statement nor use views w/table variables or temp tables). Can anyone recommend a way to store the returned data? Thanks in advance to all responses.
|
|
|
|
|
It sounds like you'll need another table in your database to store the results. Include an INSERT statement with your results to the new table.
|
|
|
|
|
SQL 2000
System.Data.SqlClient.SqlException: Warning Fatal error 3624
when executing fill dataadapter from sql data table
Please help me to solve the problem
Thanks
|
|
|
|
|
alexrad wrote: SQL 2000
System.Data.SqlClient.SqlException: Warning Fatal error 3624
when executing fill dataadapter from sql data table
Please help me to solve the problem
How can anyone help you if you don't provide your code? The people on this forum aren't psychic.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Ofcourse,
but it is every time when i call storedprocedure on that same table,
no metter what query. Eny of them on that table doesnt work.
|
|
|
|
|
Since you didn't get the point of my previous reply, let me make it simple for you.
No information = no help
If you don't provide enough information to let someone understand what the problem is, your question will remain unanswered. It's up to you.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Error 3624 is a SQL Server internal, unexpected error. Usually there's a note of the SQL Server source file and line number that the assertion occurred at.
I would start by making sure you're running the latest service pack on the server (SP4) and the newest version of MDAC on the client (2.8 SP1, included in Windows XP SP2 and Windows Server 2003 SP1, if I recall correctly). If the problem doesn't go away you'll have to search for the solution at support.microsoft.com[^], and if no solution exists you'll have to call Microsoft support.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Thanks, i was thinking the same, but enother view of the problem is very usefull. I don't have sp4 on that server, sp2 is ok. I will try your way !
Thanks again !
|
|
|
|
|
Is there any standard or as per need?
|
|
|
|
|
As far as possible you want to design your database so that once columns have been created they do not need amending in the future.
The nice people at Microsoft provided us with the VarChar field which allows our text based database field sizes to grow over time.
With regards to integer fields you need a fairly good idea of how big the numbers are going to get as there are four different types of integer.
With regards to:
Master Mind'z wrote: Is there any standard or as per need?
The analysis phase should specify what column definitions are.
My advice is to create a database where the columns are as future proofed as possible - this means avoid setting columns such as 'sales quantity' columns to tinyint.
Also google your question as there will be pages dedicated to this area.
You always pass failure on the way to success.
|
|
|
|
|
Hi
I am using SQL Server 2005 in our InstallShield Project.In that It needs to take the login inforamtion at run time for checking the password.Where will we get all those kind of Informations.Where is it resided in our machine.
Please .... respond to my question.
Thanks In Advance.
Jeeva
|
|
|
|
|
hi all
i have a Query that return results and i have written it like this
<br />
select * <br />
from sde.property p1<br />
where p1.func_key = 'PV000000'<br />
and exists (select 1 <br />
from sde.property p2<br />
where p2.lis_key = p1.lis_key<br />
and substring(p2.func_key,1,5)='GEOSS'<br />
and P1.aCTIVE =1<br />
And substring(p1.Attrib_code,5,2) != '16')<br />
order by Actual_extent Asc<br />
<br />
And i want to Update the records returned by the above query like this
<br />
<br />
update Property<br />
set Attrib_code = substring(Attrib_code,1,4)+ '16'+ substring(Attrib_code,7,6)<br />
<br />
Now i was to combine this Queries , how can i achieve this
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Give this a try
update p1
set Attrib_code = substring(Attrib_code,1,4)+ '16'+ substring(Attrib_code,7,6)
from sde.property p1
where p1.func_key = 'PV000000'
and exists (select 1
from sde.property p2
where p2.lis_key = p1.lis_key
and substring(p2.func_key,1,5)='GEOSS'
and P1.aCTIVE =1
And substring(p1.Attrib_code,5,2) != '16')
order by Actual_extent Asc
Regards
Guy
You always pass failure on the way to success.
|
|
|
|
|
Thank you very much its working
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Thank you very much its working
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Recently i came across an error updating my DB. had checked the validations and field requirements of each individual field in the DB and when submit, it prompt me this.
--------------------------------------------------------------------------------------------------------------------
Error updating the database. Please make sure all values are correctly input
INSERT INTO tblUsers (userid, email, password, Cpassword, Fname, Lname, birthDate, street, Pcode, city, state) VALUES ('asdasd','adasda@asdas.asa','123123123','123123123','dasda','asdada','01/14/2008','asdasd','1233','asda','asda')
System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at ASP.rego4_aspx.ExecuteStatement(Object strSQL) in c:\inetpub\wwwroot\test\insertDB.aspx:line 81
--------------------------------------------------------------------------------------------------------------------
its stated Syntex error in the command but however it seems workingly fine updating another table. can anyone help point out my error here?
Thanx in advance!
|
|
|
|
|
Try running the query through Management Studio. You should get more detailed error information.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|