How to insert the value from the text box (ASP.NET 2.0) to the microsoft sql server 2000 d

Hello Friends,

I have a problem with ASP.net with dynamic data transfer from asp page to microsoft sql server 2000. For example , I have asp web page with one text field and a buttion.When I click the buttion, the value entered in the text field should be transfered from the text field to database.

Kindly See the following section c# code ....

SqlConnection objconnect = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand();

cmd.Connection = objconnect;

cmd.CommandText = " select * from Table_Age where Age = @.Age";

cmd.CommandType = CommandType.Text;

SqlParameter parameter = new SqlParameter();

parameter.ParameterName = "@.Age";

parameter.SqlDbType = SqlDbType.VarChar;

parameter.Direction = ParameterDirection.Output;

parameter.Value = TextBox1.Text;

objconnect.Open();

// Add the parameter to the Parameters collection.

cmd.Parameters.Add(parameter);

SqlDataReader reader = cmd.ExecuteReader();

********************this section c# code is entered under the button control************************************

connection string is mentioned in the web.config file.

In the above c# code , I have a text field , where I entered the age , the value entered in the text field should be sent to database. I have used SqlParameter for selecting the type of data should be sent from ASP.NET 2.0 to the microsoft sql server 2000. I am facing a problem where I am unable to sent the random datas from a text field to the database server.I have a created a database file in the microsoft server 2000.

after the excution of the fIeld value is assinged to NULL in the main database i.e microsoft the sql server 2000.

Can anyone help with this issue.

My mail id phijop@.hotmail.com

- PHIJO MATHEW PHILIP.

Hi

Remove :

parameter.Direction = ParameterDirection.Output;

since your direction should be input.

How to INSERT text that contains single quotes?

When users enter text into a textbox, to be INSERTed into my table, SQL Server throws an error if their text contains a single quote.

For example, if they enter "It's great!" then it causes this error:
Error: Incorrect syntax near 's'. Unclosed quotation mark after the character string ''.

How can I allow text with single quotes to be inserted into the table?

Here's my code:

string strInsert = "INSERT INTO [Comments] ([GameID], [UserID], [Comment]) VALUES (@.GameID, @.UserID, @.Comment)";

SqlConnection myConnection = new SqlConnection(<<myconnectionstuff>>);
SqlCommand myCommand = new SqlCommand(strInsert, myConnection);

myCommand.Parameters.Add( "@.GameID", Request.QueryString["GameID"] );
myCommand.Parameters.Add( "@.UserID", (string)Session["UserID"] );
myCommand.Parameters.Add( "@.Comment", ThisUserCommentTextBox.Text );

try {
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
}

catch (SqlException ex) {
ErrorLabel.Text = "Error: " + ex.Message;
}

finally {
myCommand.Connection.Close();
}

You write C# so they are called string literals in C# but in ANSI SQL they are Delimiters per ANSI SQL 92 and SQL Server is compliant so run a search for Qouted Identifier in SQL Server BOL(books online) and try the link below for SQL Server Delimiters. Hope this helps.

http://msdn2.microsoft.com/en-gb/library/ms176027.aspx

|||That code looks fine. Are you sure that is where the problem is?|||

(How can I allow text with single quotes to be inserted into the table?)

The person have not posted the code with the error but I replied the above and the error generated.

|||

Motley:

That code looks fine. Are you sure that is where the problem is?

Well it works fine unless I include a single quote in the text box somewhere.

Caddre, I've researched some more based on your reply, but I don't have easy access to the SQL Server settings - I'm on a shared hosting account. Is there some other workaround, maybe using a regular expression to replace a single quote with something else, at least temprarily while I'm processing the text? I tried entering /' instead, to see if an escape character would work, but SQL Server still won't take it.

|||

Yes there is SET QUOTED_IDENTIFIER ON/OFF will help you deal with it. Try the link below for details.

http://msdn2.microsoft.com/en-US/library/ms174393.aspx

|||

Caddre:

Yes there is SET QUOTED_IDENTIFIER ON/OFF will help you deal with it. Try the link below for details.

http://msdn2.microsoft.com/en-US/library/ms174393.aspx

I'm wondering how to send the "SET QUOTED_IDENTIFIER ON" command within my ASP.NET page.

I'm using a command called ExecuteNonQuery(); -- so does that mean I can just append it to the beginning of my INSERT string, like this?

string strInsert = "SET QUOTED_IDENTIFIER ON GO INSERT INTO [Comments] ([GameID], [UserID], [Comment]) VALUES ..."

Does it last only during this query or will it stay on somehow until I turn it off?

Sorry, I'm a newbie with SQL Server and ASP.NET.

|||You have to use it in your stored proc and ExecuteNonQuery takes stored proc. Hope this helps|||

Yes, I understand. It should work fine for any value you place within the textbox control. That includes quotes. I do this quite often with no problems.

As for can you put the SET statement before your query, the answer is yes, but do not separate the commands with GO. You may place a semi-colon between them if you want to however, like:

SET {stuff}; SELECT {stuff}

|||

OK, I am trying to make sense of this, and it's just not making any sense to me. (I have limited experience with SQL and SQL Server and VBScript; I paid a programmer to write a script and after delivering a partly-working script, he promised to fix it and then disappeared. I think he couldn't solve this problem himself.)

I am getting the error message "Unclosed quotation mark after the character string" when I execute this SQL statement from VBScript, butno error when I execute the same SQL statement from within SQL Management Studio. Any suggestions would be extremely welcome.

INSERT INTO Raw_Datafeeds (ProductID,Name,MerchantID,Merchant,Link,Thumbnail,BigImage,Price,RetailPrice,Category,SubCategory,Description,Custom1,Custom2,Custom3,Custom4,Custom5,LastUpdated,Status,Last_Loaded)
VALUES
('454848129','Healthy Ears Ear Cleanser - Buy 2 Get 1 Free','10052',
'Pet Nutrition Products','http://www.shareasale.com/m-pr.cfm?merchantID=10052&userID=YOURUSERID&productID=454848129',
'http://www.petnutritionproducts.com/images/products/100/cat_ears.jpg',
'http://www.petnutritionproducts.com/images/products/400/cat_ears.jpg',15.98,0,'Home/Family','Pets',
'Healthy Ears Ear Cleanser is a veterinarian formulated combination of gentle ingredients that aid in the removal of dirt, wax and other unwanted matter from your cat''s ears.',
'Dog','Ear Care',' ',' ',' ','6/6/2006 1:35:01 AM','instock','10/25/2006 4:06:00 PM')

|||

The original problem in this thread could probably be solved by specifying the type of each parameter rather than letting the system guess.

As for mark's problem, it's obviously related to "cat''s" in your insert string, however since you haven't posted the code you are using in VBScript, it'd be difficult to say where the problem is. As a quick hack, you can replace all quotes in any parameter with '+CHAR(39)+' so that cat's would then become cat'+CHAR(39)+'s.

How to insert right justifies leading zeros

I have a business rule in my environment where I need to insert right justified leading zeros in the column. For example if the value to be inserted is 12 than it should be inserted as 0000012. How can I do this

Chintan.

Here it is:

SELECT RIGHT('000000' + CAST(12 as SYSNAME), 7)

Thanks,
Zuomin
|||

The "leading zeroes" issue might be more of a display issue than a storage issue. Is your column a character field, integer field, or what exactly?

If your field is character (or varchar) then you can use concatenation and the RIGHT function to pre-pend your string with zero characters; however, if your field is integer understand that it is stored as a binary encoded integer and the zeroes are understood.

You can do something like zuomin has suggested for display purposes. Also, if you MUST carry this as a zero-filled key, you can store it in the table as a computed column -- again as a CHAR field similar to zuomin's suggestion.

|||

the rule applies to about 9600 rows so....what can be the best solution

Chintan

|||

as of now the columns are in Varchar, but this whole table is getting converted to Flatfile of fixed width and than it is submitted to client. In this case wot shud I allocate the column as Varchar or Integer taking flatfile into consideration.

|||

OK, but what is the datatype of your column?

|||

Varchar.

|||

I would suggest using something like zuomin's RIGHT solution.

|||

A bit of editorial: I have never really liked doing this. This makes it possible to have many different '1' records. Now it is possible to have record '1', '01', '001', etc as valid primary keys to this table. And I have seen this problem manifest several times. For me, I would rather store the integer field and have the application do the formatting.

|||

the problem here is 1. there is no primary keys

2. there is no application which will insert leading zeros

3. it is goin as a simple text files

so now if i use

SELECT RIGHT('000000' + CAST(trtyc as SYSNAME), 7)

this is just a select statement, but i need to insert leading zeros into the db so tht whenever the data goes in to flat files instead of showing 12 it will show 0000012

it might some kinda update statment

Chintan

|||

UPDATE yourTable SET yourVarCharColumn= RIGHT('000000'+yourVarCharColumn,7)

Without WHERE condtion, you will update all your records.

How to insert numbers from a text box to a Sql database table column’s type numeric?

Hi,

I am getting an error when I try to insert a number typed in a text box control into a Sql database table column's type numeric(6,2). For example: If I type 35,22 into the text box, or 35, and then I submit the form to insert the data into database, I get the follow error:

System.FormatException: Input string was not in a correct format.
Line 428: CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.decimal, "Measure"))

A piece of the SP inside Sql server:

USE market26
GO
ALTER PROC new_offer
@.Offer_id bigint, @.Measure numeric(6,2) = null, …

What is wrong? Why it doesn' t accept the number typed in my text box?

Thank you,
CesarThis is not correct:

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal, "Measure"))

This would probably work (not tested):

SqlParameter mParm = new SqlParameter("@.measure", SqlDbType.Decimal);
mParm.Size = 13; // Max size in Bytes of the Decimal number
mParm.Precision = 8; // Total number of digits allowed (right + left of decimal point
mParm.Scale = 2; // Set the number of decimal places the Parameter value is resolved
mParm.set_IsNullable(true);
mParm.Value = decimal.Parse(myTextBox.Text.Trim());
CmdInsert.Parameters.Add(mParm)

|||Hi,

I always write my ASP.NET commands to talk with Sql Stored Procedures thus:

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal, "Measure"))

I think that it is correct, and it works fine.

I have never used a text field to insert numeric data into Sql database, but, I have to define all these parameter properties in order to pass a numeric data type to the database?

Thanks|||That's interesting. There is no Constructor attributed to the SqlParameter Class which accepts a Constructor looking like this:

New SqlParameter(<string>,<SqlDbType Enum>, <string>)

as far as I know...But hey, go for it!|||Take a look at this piece of an article and let me know what do you think about it:

Dim sConnectionString As String = _
 "server=localhost;uid=sa;pwd=;database=Northwind"
Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim cmdOrders As New SqlCommand("CustOrderHist", cnNorthwind)
cmdOrders.CommandType = CommandType.StoredProcedure
' Set up parameter for stored procedure
Dim prmCustomerID As New SqlParameter()
prmCustomerID.ParameterName = "@.CustomerID"
prmCustomerID.SqlDbType = SqlDbType.VarChar
prmCustomerID.Size = 5
prmCustomerID.Value = "ALFKI"
cmdOrders.Parameters.Add(prmCustomerID)
Dim daGetOrders As New SqlDataAdapter(cmdOrders)
Dim dsOrders As New DataSet()
daGetOrders.Fill(dsOrders, "Orders")
DataGrid1.DataSource = dsOrders.Tables("Orders")

This example makes all the parameter settings explicit. Some developers like this style, and it's good for instructional purposes. However, some developers prefer an equivalent alternative that has fewer lines of code:

Dim sConnectionString As String = _
 "server=localhost;uid=sa;pwd=;database=Northwind"
Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim cmdOrders As New SqlCommand("CustOrderHist", cnNorthwind)
cmdOrders.CommandType = CommandType.StoredProcedure
cmdOrders.Parameters.Add(New _
 SqlParameter("@.CustomerID", SqlDbType.VarChar, 5))
cmdOrders.Parameters("@.CustomerID").Value = "ALFKI"
Dim daGetOrders As New SqlDataAdapter(cmdOrders)
Dim dsOrders As New DataSet()
daGetOrders.Fill(dsOrders, "Orders")
DataGrid1.DataSource = dsOrders.Tables("Orders")

This code behaves exactly the same as the previous example. However, it only needs two lines of code for each parameter rather than six. When a stored procedure has a lot of parameters (as some of our later examples do), this can be quite a difference on lines of code required, so we'll use that form from this point onward.

The complete article is here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadvnet/html/vbnet09102002.asp|||Hey! Perhaps you have misunderstood me. I meant that I always use this structure:
(Which I knew that something was wrong)

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal, "Measure"))

Instead of this:

SqlParameter mParm = new SqlParameter("@.measure", SqlDbType.Decimal)
mParm.SourceColumn = "Measure"
CmdInsert.Parameters.Add(mParm)

I knew that something was wrong, I suspected that something it lacked in my SqlParameter, and you helped me with your example!, it lacked only the size property which in my case is 5. I am sorry, the only thing that I saw strange is the structure you wrote, and the amount of parameters which I don' t need to use (in this case). I always use the shorter alternative ;-).

So, the correct SqlParameter in my case is:

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal,5, "Measure"))

And now works fine.
Thank you very much!
Cesar

How to insert all info from a table of database to onther table of other databas

I have two database and both of them has the same table, i want to copy all info from this first table to the secornd table

For Example:
Database : DB_1, table is table_1
Database : DB_2, table is table_2

both table_1 and table_2 have the same struct

how can i insert all records from table_1 to table_2

thanksYou can use DTS by using wizard from enterprise manager, if its one-off option.
Otherwise schedule that package as a job oto execute in periodic basis.|||if it is just one table and one time affair, then u can just import the data from EM using the import wizard.|||can you show me in programming|||Use a SELECT subquery to specify the data values for one or more rows.
INSERT INTO MyTable (PriKey, Description)
SELECT ForeignKey, Description
FROM SomeView

And review information from SQL books online.

How to insert a parenthesis into a field

I'd like to know how to insert a parenthesis into a field:
Example:
insert into MyTable(mydescription) values ('4.ó%ø)&.ö')
I tried SET QUOTED_IDENTIFIER ON without success. The above is a scrambled
password. It must go into the database exactly as it appears.
--
Regards,
JamieYour code actually worked for me. The parenthesis shouldn't cause a problem
but some non-printable, control characters might. I suggest you insert data
like this as VARBINARY rather than strings so that you can safely insert any
byte values you may require.
Passwords? Don't store them in the database. Store a secure hash of the
password in the database instead. Maybe you meant that this was a password
hash but your use of the word "scrambled" implied to me that this is an
*encrypted* password. Storing encrypted passwords is not really a good idea
from a security point-of-view.
--
David Portas
SQL Server MVP
--|||Please ignore this post. I was having a problem with syntax. It is solved.
Regards,
Jamie
"thejamie" wrote:
> I'd like to know how to insert a parenthesis into a field:
> Example:
> insert into MyTable(mydescription) values ('4.ó%ø)&.ö')
> I tried SET QUOTED_IDENTIFIER ON without success. The above is a scrambled
> password. It must go into the database exactly as it appears.
> --
> Regards,
> Jamie|||Thanks David,
Ah... you're dead right and as it is now, I'm storing both. I figure that
scrambling the password is adequate to keep people from knowing that they
are passwords stored in a database provided I don't name the field something
conspicuous like 'password'. I'm not doing rocket science here, just
creating a record to read. Each scrambled password is also hashed. If the
scrambled password is altered, the hash won't work. I have enough checks
and balances to satisfy management and that satisfies me. I hash dates,
cpuids, networklogins, userid's, aliases... anything I can think of that
someone might play with. Probably slows the database down a bit, but since
it all gets done at startup, I can live with that too. I probably overdo
the hash thing and one of these days, I'll trim it down. For now, too much
is probably enough. Something like that.
Thanks for the advice though. Never thought of using the varbinary to
store the string. I do use it for the hash. The special characters should
store in the varchar though, shoudn't they?
Giac
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:0tqdndBnn_mcJPjcRVn-jg@.giganews.com...
> Your code actually worked for me. The parenthesis shouldn't cause a
> problem but some non-printable, control characters might. I suggest you
> insert data like this as VARBINARY rather than strings so that you can
> safely insert any byte values you may require.
> Passwords? Don't store them in the database. Store a secure hash of the
> password in the database instead. Maybe you meant that this was a password
> hash but your use of the word "scrambled" implied to me that this is an
> *encrypted* password. Storing encrypted passwords is not really a good
> idea from a security point-of-view.
> --
> David Portas
> SQL Server MVP
> --
>

How to insert a datetime to sql sever

Hi everyone,

I need help in inserting a date time string to sql sever. For example, how do you insert textbox1.text="2006-08-30 09:00:00" into a datatable column names starttime (type datetime) in sql sever? How do I covert the format of this string before I do the insert?

Thanks.

a123.

You don't have to pass that value. In the colum starttime set the data type to 'starttime' then for the default value enter getdate()

That should fix your problem.

|||

That would be an acceptable format (YYYY-MM-DD HH:mm:ss), of course you lose the sub-second precision though. If you always want to insert the current datetime, then as the previous poster said, change your insert statement to use getdate() rather than take the value from a passed parameter.

how to include an apostrophy inside a string

I am doing a row update in a table and the text contains a ' character, just
wondering how to do this?
for example
UPDATE table
SET field2 = 'text to enter to table's but does not work'
WHERE field1 = 133
the second apostrophy I want to not end the text section with.
Thanks.
--
Paul G
Software engineer.YOu have to double quote that:
UPDATE table
SET field2 = 'text to enter to table''s but does not work'
WHERE field1 = 133
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Paul" wrote:
> I am doing a row update in a table and the text contains a ' character, just
> wondering how to do this?
> for example
> UPDATE table
> SET field2 = 'text to enter to table's but does not work'
> WHERE field1 = 133
> the second apostrophy I want to not end the text section with.
> Thanks.
> --
> Paul G
> Software engineer.|||ok thanks. One other question, when I use select from query analizer to
retreive a long text field and then copy and paste it to a word doc it looks
like it only returns the first portion of the large text string. Also is
there anyway to view a large text field from query analyzer when you open the
table? thanks again.
--
Paul G
Software engineer.
"Jens Sü�meyer" wrote:
> YOu have to double quote that:
> UPDATE table
> SET field2 = 'text to enter to table''s but does not work'
> WHERE field1 = 133
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Paul" wrote:
> > I am doing a row update in a table and the text contains a ' character, just
> > wondering how to do this?
> >
> > for example
> > UPDATE table
> > SET field2 = 'text to enter to table's but does not work'
> > WHERE field1 = 133
> > the second apostrophy I want to not end the text section with.
> > Thanks.
> >
> > --
> > Paul G
> > Software engineer.|||QA is limited to a maximum outpur of 8000 characters, if you don´t have even
that you should look in Tools--> Options --> Results --> Maximum charcters
per column
to increase it to 8000
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Paul" wrote:
> ok thanks. One other question, when I use select from query analizer to
> retreive a long text field and then copy and paste it to a word doc it looks
> like it only returns the first portion of the large text string. Also is
> there anyway to view a large text field from query analyzer when you open the
> table? thanks again.
> --
> Paul G
> Software engineer.
>
> "Jens Sü�meyer" wrote:
> > YOu have to double quote that:
> >
> > UPDATE table
> > SET field2 = 'text to enter to table''s but does not work'
> > WHERE field1 = 133
> >
> >
> > --
> > HTH, Jens Suessmeyer.
> >
> > --
> > http://www.sqlserver2005.de
> > --
> >
> >
> > "Paul" wrote:
> >
> > > I am doing a row update in a table and the text contains a ' character, just
> > > wondering how to do this?
> > >
> > > for example
> > > UPDATE table
> > > SET field2 = 'text to enter to table's but does not work'
> > > WHERE field1 = 133
> > > the second apostrophy I want to not end the text section with.
> > > Thanks.
> > >
> > > --
> > > Paul G
> > > Software engineer.|||ok thanks that should do it as it is way less than 8000 characters.
--
Paul G
Software engineer.
"Jens Sü�meyer" wrote:
> QA is limited to a maximum outpur of 8000 characters, if you don´t have even
> that you should look in Tools--> Options --> Results --> Maximum charcters
> per column
> to increase it to 8000
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Paul" wrote:
> > ok thanks. One other question, when I use select from query analizer to
> > retreive a long text field and then copy and paste it to a word doc it looks
> > like it only returns the first portion of the large text string. Also is
> > there anyway to view a large text field from query analyzer when you open the
> > table? thanks again.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Jens Sü�meyer" wrote:
> >
> > > YOu have to double quote that:
> > >
> > > UPDATE table
> > > SET field2 = 'text to enter to table''s but does not work'
> > > WHERE field1 = 133
> > >
> > >
> > > --
> > > HTH, Jens Suessmeyer.
> > >
> > > --
> > > http://www.sqlserver2005.de
> > > --
> > >
> > >
> > > "Paul" wrote:
> > >
> > > > I am doing a row update in a table and the text contains a ' character, just
> > > > wondering how to do this?
> > > >
> > > > for example
> > > > UPDATE table
> > > > SET field2 = 'text to enter to table's but does not work'
> > > > WHERE field1 = 133
> > > > the second apostrophy I want to not end the text section with.
> > > > Thanks.
> > > >
> > > > --
> > > > Paul G
> > > > Software engineer.|||Just wondering if you know how to direct the results to a file?
--
Paul G
Software engineer.
"Jens Sü�meyer" wrote:
> QA is limited to a maximum outpur of 8000 characters, if you don´t have even
> that you should look in Tools--> Options --> Results --> Maximum charcters
> per column
> to increase it to 8000
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Paul" wrote:
> > ok thanks. One other question, when I use select from query analizer to
> > retreive a long text field and then copy and paste it to a word doc it looks
> > like it only returns the first portion of the large text string. Also is
> > there anyway to view a large text field from query analyzer when you open the
> > table? thanks again.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Jens Sü�meyer" wrote:
> >
> > > YOu have to double quote that:
> > >
> > > UPDATE table
> > > SET field2 = 'text to enter to table''s but does not work'
> > > WHERE field1 = 133
> > >
> > >
> > > --
> > > HTH, Jens Suessmeyer.
> > >
> > > --
> > > http://www.sqlserver2005.de
> > > --
> > >
> > >
> > > "Paul" wrote:
> > >
> > > > I am doing a row update in a table and the text contains a ' character, just
> > > > wondering how to do this?
> > > >
> > > > for example
> > > > UPDATE table
> > > > SET field2 = 'text to enter to table's but does not work'
> > > > WHERE field1 = 133
> > > > the second apostrophy I want to not end the text section with.
> > > > Thanks.
> > > >
> > > > --
> > > > Paul G
> > > > Software engineer.|||Press CTRL-Shift+F in WA then Execute the query or select the menu Query -->
Results in File
or you use OSQL on the commandline with the -o <outputfile >switch
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Paul" wrote:
> Just wondering if you know how to direct the results to a file?
> --
> Paul G
> Software engineer.
>
> "Jens Sü�meyer" wrote:
> > QA is limited to a maximum outpur of 8000 characters, if you don´t have even
> > that you should look in Tools--> Options --> Results --> Maximum charcters
> > per column
> >
> > to increase it to 8000
> > --
> > HTH, Jens Suessmeyer.
> >
> > --
> > http://www.sqlserver2005.de
> > --
> >
> >
> > "Paul" wrote:
> >
> > > ok thanks. One other question, when I use select from query analizer to
> > > retreive a long text field and then copy and paste it to a word doc it looks
> > > like it only returns the first portion of the large text string. Also is
> > > there anyway to view a large text field from query analyzer when you open the
> > > table? thanks again.
> > > --
> > > Paul G
> > > Software engineer.
> > >
> > >
> > > "Jens Sü�meyer" wrote:
> > >
> > > > YOu have to double quote that:
> > > >
> > > > UPDATE table
> > > > SET field2 = 'text to enter to table''s but does not work'
> > > > WHERE field1 = 133
> > > >
> > > >
> > > > --
> > > > HTH, Jens Suessmeyer.
> > > >
> > > > --
> > > > http://www.sqlserver2005.de
> > > > --
> > > >
> > > >
> > > > "Paul" wrote:
> > > >
> > > > > I am doing a row update in a table and the text contains a ' character, just
> > > > > wondering how to do this?
> > > > >
> > > > > for example
> > > > > UPDATE table
> > > > > SET field2 = 'text to enter to table's but does not work'
> > > > > WHERE field1 = 133
> > > > > the second apostrophy I want to not end the text section with.
> > > > > Thanks.
> > > > >
> > > > > --
> > > > > Paul G
> > > > > Software engineer.|||ok thanks this seems very useful.
--
Paul G
Software engineer.
"Jens Sü�meyer" wrote:
> Press CTRL-Shift+F in WA then Execute the query or select the menu Query -->
> Results in File
> or you use OSQL on the commandline with the -o <outputfile >switch
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Paul" wrote:
> > Just wondering if you know how to direct the results to a file?
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Jens Sü�meyer" wrote:
> >
> > > QA is limited to a maximum outpur of 8000 characters, if you don´t have even
> > > that you should look in Tools--> Options --> Results --> Maximum charcters
> > > per column
> > >
> > > to increase it to 8000
> > > --
> > > HTH, Jens Suessmeyer.
> > >
> > > --
> > > http://www.sqlserver2005.de
> > > --
> > >
> > >
> > > "Paul" wrote:
> > >
> > > > ok thanks. One other question, when I use select from query analizer to
> > > > retreive a long text field and then copy and paste it to a word doc it looks
> > > > like it only returns the first portion of the large text string. Also is
> > > > there anyway to view a large text field from query analyzer when you open the
> > > > table? thanks again.
> > > > --
> > > > Paul G
> > > > Software engineer.
> > > >
> > > >
> > > > "Jens Sü�meyer" wrote:
> > > >
> > > > > YOu have to double quote that:
> > > > >
> > > > > UPDATE table
> > > > > SET field2 = 'text to enter to table''s but does not work'
> > > > > WHERE field1 = 133
> > > > >
> > > > >
> > > > > --
> > > > > HTH, Jens Suessmeyer.
> > > > >
> > > > > --
> > > > > http://www.sqlserver2005.de
> > > > > --
> > > > >
> > > > >
> > > > > "Paul" wrote:
> > > > >
> > > > > > I am doing a row update in a table and the text contains a ' character, just
> > > > > > wondering how to do this?
> > > > > >
> > > > > > for example
> > > > > > UPDATE table
> > > > > > SET field2 = 'text to enter to table's but does not work'
> > > > > > WHERE field1 = 133
> > > > > > the second apostrophy I want to not end the text section with.
> > > > > > Thanks.
> > > > > >
> > > > > > --
> > > > > > Paul G
> > > > > > Software engineer.sql

How to improve the efficience of Sql query ?

now i want to get results from server tables, but i found it is very slow, for example :

select Coalesce(T1.Name, T2.Name, T3.Name), T1.M1, T2.M2, T3.M3

from T1

full outer join T2

on Coalesce(T1.Name, NULL) = T2.Name

full outer join T3

on Coalesce(T1.Name, T2.Name) = T3.Name

in Tables i have builded index for name, but when every table have 20000 records, the sql above is very slow, is there other method to improve the query speed ?

Thks

You're trying to full outer join three tables and on the join condition is based on computed columns... well that's a query not build for performance...
If you want to improve performance you need to rewrite the query in a way that doesn't uses coalesce on the join condition, you should use tables columns directly and then filter on the where or on the having

Just a final thought: The
Coalesce(T1.Name, NULL) = T2.Name
Coalesce(T1.Name, T2.Name) = T3.Name
conditions may be rewritten as
T2.Name = Coalesce(T1.Name, NULL)
T3.Name = Coalesce(T1.Name, T2.Name)
take a look if this changes the execution plan.
|||

Liu:

Do you filter based on name or are you wanting to process all rows of all tables?

Dave

|||

every one:

i have solved this problem with hash join such as belows:

select Coalesce(T1.Name, T2.Name, T3.Name), T1.M1, T2.M2, T3.M3

from T1

full outer hash join T2

on Coalesce(T1.Name, NULL) = T2.Name

full outer hash join T3

on Coalesce(T1.Name, T2.Name) = T3.Name

in Tables i have builded index for name, but when every table have 20000 records, the sql above is very slow, is there other method to improve the query speed ?

the time to query changes from 33m to 4s

thks

How to import in special character delimited text file by using SSIS ?

Hi,

I would like to know how to import in the custom delimited text file by using SSIS.

For example, instead by using tab or comma delimited, I use this character : '?'

The reason is the delimited format that SSIS provided is too common such as colon, semi colon, tab, comma and pipeline.

I have the data that the user also key in the pipeline there. So I am thinking to separate the field by using this special character, but cannot see if there is anyway to import in by using SSIS.

Please help to share the solution on this :

A?B?C
1?2?3

thanks

best regards,

Tanipar

You can import this as a single column and then use a script transform to cycle through your row and break it down into the appropriate columns. There are various examples of doing this relating to uneven / unbalanced / dynamic number of columns...

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/07/handling-flat-files-with-varying-numbers-of-columns.aspx

|||I am a fan of the approach above , but you can also just use a flat file connection manager. Go to the Columns page in the editor, and put the symbol into the Column Delimiter field. That works fine for me. The approach above is usually only necessary when dealing with flat files with missing columns or delimiters.|||Learn something new every day... I figured since it was a drop down that you could only use the values present, I didn't realize you could type there... Let's just say this approach is MUCH easier :-)

How to import in special character delimited text file by using SSIS ?

Hi,

I would like to know how to import in the custom delimited text file by using SSIS.

For example, instead by using tab or comma delimited, I use this character : '?'

The reason is the delimited format that SSIS provided is too common such as colon, semi colon, tab, comma and pipeline.

I have the data that the user also key in the pipeline there. So I am thinking to separate the field by using this special character, but cannot see if there is anyway to import in by using SSIS.

Please help to share the solution on this :

A?B?C
1?2?3

thanks

best regards,

Tanipar

You can import this as a single column and then use a script transform to cycle through your row and break it down into the appropriate columns. There are various examples of doing this relating to uneven / unbalanced / dynamic number of columns...

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/07/handling-flat-files-with-varying-numbers-of-columns.aspx

|||I am a fan of the approach above , but you can also just use a flat file connection manager. Go to the Columns page in the editor, and put the symbol into the Column Delimiter field. That works fine for me. The approach above is usually only necessary when dealing with flat files with missing columns or delimiters.|||Learn something new every day... I figured since it was a drop down that you could only use the values present, I didn't realize you could type there... Let's just say this approach is MUCH easier :-)

how to implement locking

Take for example ticketmaster where they lock a certain section, row and
even seat numbers for a duration..
I am assuming all this info may be in one table . How can one ensure row
level locking without SQL Server escalating it to some high level lock.
And even if a particular row is locked, does that mean that one can update
rows that are not locked
Trying to find a solution to do locking at a row level while still leaving
other rows for DML i.e select, updates,inserts,deletes
Thanks
On Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
wrote:
>Take for example ticketmaster where they lock a certain section, row and
>even seat numbers for a duration..
>I am assuming all this info may be in one table . How can one ensure row
>level locking without SQL Server escalating it to some high level lock.
>And even if a particular row is locked, does that mean that one can update
>rows that are not locked
>Trying to find a solution to do locking at a row level while still leaving
>other rows for DML i.e select, updates,inserts,deletes
You can always add an IsLocked field to the table, SQLServer will
never escalate that, and it won't block other operations, and will
even still be there if you reboot the server!
J.
|||I think we may get into design here. How will I model the data ? Take for
example Ticketmaster.
And consider high concurrency needed and not being able to block one
another.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:me34h1lcijtg0h301o85u0cj84ds2dbrfa@.4ax.com...
> On Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
> wrote:
> You can always add an IsLocked field to the table, SQLServer will
> never escalate that, and it won't block other operations, and will
> even still be there if you reboot the server!
> J.
>
|||If they have the proper indexes and WHERE clauses they will have to lock
lots of rows in the single transaction before it will escalate to table.
But if you want to ensure it never escalates you can add a dummy row and
have a connection lock it all the time. As long as there is another lock in
the table at any level you can not escalate to a table lock. Of coarse that
means if you do scans you might have an issue but if you have the right
indexes and such it shouldn't be an issue.
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Take for example ticketmaster where they lock a certain section, row and
> even seat numbers for a duration..
> I am assuming all this info may be in one table . How can one ensure row
> level locking without SQL Server escalating it to some high level lock.
> And even if a particular row is locked, does that mean that one can update
> rows that are not locked
> Trying to find a solution to do locking at a row level while still leaving
> other rows for DML i.e select, updates,inserts,deletes
> Thanks
>
|||Locking a dummy row is a clever way of avoiding lock escalation on a
particular table. However, please do not overuse this. Having long running
open transaction is not desirable. It should only be used as a workaround
for heavy contention issues as a result of lock escalation. Disabling lock
escalation in general may result in slower performance.
By default, SQL Server does not escalate unless more than 5000 locks are
obtained in the current statement for a particular table. If the query plan
does not contain a range scan, then most likely you will not encounter
escalation. If it does have a range scan, then you could analyze if that
range scan has a chance of encountering a lot of rows.
If you do need to implement the dummy row locking idea, be sure not to do
anything else in the transaction which holds the long term lock. So it
should be something like this:
-- if you do not want escalated X table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table where PrimyarKeyColumn = dummy_row_key_value
wait for delay ...
commit
-- if you do not want escalated S table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table with (UPDLOCK) where PrimyarKeyColumn =
dummy_row_key_value
wait for delay ...
commit
If you are planning to use SQL Server 2005, then the new
Read-Committed-Snapshot-Isolation feature guarantees that there is no S
table lock for reads.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ON%23waYCrFHA.3640@.tk2msftngp13.phx.gbl...
> If they have the proper indexes and WHERE clauses they will have to lock
> lots of rows in the single transaction before it will escalate to table.
> But if you want to ensure it never escalates you can add a dummy row and
> have a connection lock it all the time. As long as there is another lock
> in the table at any level you can not escalate to a table lock. Of coarse
> that means if you do scans you might have an issue but if you have the
> right indexes and such it shouldn't be an issue.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <hassanboy@.hotmail.com> wrote in message
> news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
>

how to implement locking

Take for example ticketmaster where they lock a certain section, row and
even seat numbers for a duration..
I am assuming all this info may be in one table . How can one ensure row
level locking without SQL Server escalating it to some high level lock.
And even if a particular row is locked, does that mean that one can update
rows that are not locked
Trying to find a solution to do locking at a row level while still leaving
other rows for DML i.e select, updates,inserts,deletes
ThanksOn Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
wrote:
>Take for example ticketmaster where they lock a certain section, row and
>even seat numbers for a duration..
>I am assuming all this info may be in one table . How can one ensure row
>level locking without SQL Server escalating it to some high level lock.
>And even if a particular row is locked, does that mean that one can update
>rows that are not locked
>Trying to find a solution to do locking at a row level while still leaving
>other rows for DML i.e select, updates,inserts,deletes
You can always add an IsLocked field to the table, SQLServer will
never escalate that, and it won't block other operations, and will
even still be there if you reboot the server!
J.|||I think we may get into design here. How will I model the data ? Take for
example Ticketmaster.
And consider high concurrency needed and not being able to block one
another.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:me34h1lcijtg0h301o85u0cj84ds2dbrfa@.
4ax.com...
> On Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
> wrote:
> You can always add an IsLocked field to the table, SQLServer will
> never escalate that, and it won't block other operations, and will
> even still be there if you reboot the server!
> J.
>|||If they have the proper indexes and WHERE clauses they will have to lock
lots of rows in the single transaction before it will escalate to table.
But if you want to ensure it never escalates you can add a dummy row and
have a connection lock it all the time. As long as there is another lock in
the table at any level you can not escalate to a table lock. Of coarse that
means if you do scans you might have an issue but if you have the right
indexes and such it shouldn't be an issue.
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Take for example ticketmaster where they lock a certain section, row and
> even seat numbers for a duration..
> I am assuming all this info may be in one table . How can one ensure row
> level locking without SQL Server escalating it to some high level lock.
> And even if a particular row is locked, does that mean that one can update
> rows that are not locked
> Trying to find a solution to do locking at a row level while still leaving
> other rows for DML i.e select, updates,inserts,deletes
> Thanks
>|||Locking a dummy row is a clever way of avoiding lock escalation on a
particular table. However, please do not overuse this. Having long running
open transaction is not desirable. It should only be used as a workaround
for heavy contention issues as a result of lock escalation. Disabling lock
escalation in general may result in slower performance.
By default, SQL Server does not escalate unless more than 5000 locks are
obtained in the current statement for a particular table. If the query plan
does not contain a range scan, then most likely you will not encounter
escalation. If it does have a range scan, then you could analyze if that
range scan has a chance of encountering a lot of rows.
If you do need to implement the dummy row locking idea, be sure not to do
anything else in the transaction which holds the long term lock. So it
should be something like this:
-- if you do not want escalated X table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table where PrimyarKeyColumn = dummy_row_key_value
wait for delay ...
commit
-- if you do not want escalated S table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table with (UPDLOCK) where PrimyarKeyColumn =
dummy_row_key_value
wait for delay ...
commit
If you are planning to use SQL Server 2005, then the new
Read-Committed-Snapshot-Isolation feature guarantees that there is no S
table lock for reads.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ON%23waYCrFHA.3640@.tk2msftngp13.phx.gbl...
> If they have the proper indexes and WHERE clauses they will have to lock
> lots of rows in the single transaction before it will escalate to table.
> But if you want to ensure it never escalates you can add a dummy row and
> have a connection lock it all the time. As long as there is another lock
> in the table at any level you can not escalate to a table lock. Of coarse
> that means if you do scans you might have an issue but if you have the
> right indexes and such it shouldn't be an issue.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <hassanboy@.hotmail.com> wrote in message
> news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
>

how to implement locking

Take for example ticketmaster where they lock a certain section, row and
even seat numbers for a duration..
I am assuming all this info may be in one table . How can one ensure row
level locking without SQL Server escalating it to some high level lock.
And even if a particular row is locked, does that mean that one can update
rows that are not locked
Trying to find a solution to do locking at a row level while still leaving
other rows for DML i.e select, updates,inserts,deletes
ThanksOn Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
wrote:
>Take for example ticketmaster where they lock a certain section, row and
>even seat numbers for a duration..
>I am assuming all this info may be in one table . How can one ensure row
>level locking without SQL Server escalating it to some high level lock.
>And even if a particular row is locked, does that mean that one can update
>rows that are not locked
>Trying to find a solution to do locking at a row level while still leaving
>other rows for DML i.e select, updates,inserts,deletes
You can always add an IsLocked field to the table, SQLServer will
never escalate that, and it won't block other operations, and will
even still be there if you reboot the server!
J.|||I think we may get into design here. How will I model the data ? Take for
example Ticketmaster.
And consider high concurrency needed and not being able to block one
another.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:me34h1lcijtg0h301o85u0cj84ds2dbrfa@.4ax.com...
> On Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
> wrote:
>>Take for example ticketmaster where they lock a certain section, row and
>>even seat numbers for a duration..
>>I am assuming all this info may be in one table . How can one ensure row
>>level locking without SQL Server escalating it to some high level lock.
>>And even if a particular row is locked, does that mean that one can update
>>rows that are not locked
>>Trying to find a solution to do locking at a row level while still leaving
>>other rows for DML i.e select, updates,inserts,deletes
> You can always add an IsLocked field to the table, SQLServer will
> never escalate that, and it won't block other operations, and will
> even still be there if you reboot the server!
> J.
>|||If they have the proper indexes and WHERE clauses they will have to lock
lots of rows in the single transaction before it will escalate to table.
But if you want to ensure it never escalates you can add a dummy row and
have a connection lock it all the time. As long as there is another lock in
the table at any level you can not escalate to a table lock. Of coarse that
means if you do scans you might have an issue but if you have the right
indexes and such it shouldn't be an issue.
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Take for example ticketmaster where they lock a certain section, row and
> even seat numbers for a duration..
> I am assuming all this info may be in one table . How can one ensure row
> level locking without SQL Server escalating it to some high level lock.
> And even if a particular row is locked, does that mean that one can update
> rows that are not locked
> Trying to find a solution to do locking at a row level while still leaving
> other rows for DML i.e select, updates,inserts,deletes
> Thanks
>|||Locking a dummy row is a clever way of avoiding lock escalation on a
particular table. However, please do not overuse this. Having long running
open transaction is not desirable. It should only be used as a workaround
for heavy contention issues as a result of lock escalation. Disabling lock
escalation in general may result in slower performance.
By default, SQL Server does not escalate unless more than 5000 locks are
obtained in the current statement for a particular table. If the query plan
does not contain a range scan, then most likely you will not encounter
escalation. If it does have a range scan, then you could analyze if that
range scan has a chance of encountering a lot of rows.
If you do need to implement the dummy row locking idea, be sure not to do
anything else in the transaction which holds the long term lock. So it
should be something like this:
-- if you do not want escalated X table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table where PrimyarKeyColumn = dummy_row_key_value
wait for delay ...
commit
-- if you do not want escalated S table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table with (UPDLOCK) where PrimyarKeyColumn =dummy_row_key_value
wait for delay ...
commit
If you are planning to use SQL Server 2005, then the new
Read-Committed-Snapshot-Isolation feature guarantees that there is no S
table lock for reads.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ON%23waYCrFHA.3640@.tk2msftngp13.phx.gbl...
> If they have the proper indexes and WHERE clauses they will have to lock
> lots of rows in the single transaction before it will escalate to table.
> But if you want to ensure it never escalates you can add a dummy row and
> have a connection lock it all the time. As long as there is another lock
> in the table at any level you can not escalate to a table lock. Of coarse
> that means if you do scans you might have an issue but if you have the
> right indexes and such it shouldn't be an issue.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <hassanboy@.hotmail.com> wrote in message
> news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
>> Take for example ticketmaster where they lock a certain section, row and
>> even seat numbers for a duration..
>> I am assuming all this info may be in one table . How can one ensure row
>> level locking without SQL Server escalating it to some high level lock.
>> And even if a particular row is locked, does that mean that one can
>> update rows that are not locked
>> Trying to find a solution to do locking at a row level while still
>> leaving other rows for DML i.e select, updates,inserts,deletes
>> Thanks
>