Inserting to SQL DB from a form using parameters

I have a web form for new members to fill out and save their info in a SQL database to be retrieved later. There will be like 20 elements in the form mostly textboxes. Is there a good example of how to read all the elements and have them INSERTed in a SQL Database? I imagine parameters and a SP would be the way to go. So if someone could provide me with some sample code or a good link.

Thanks in advance,Here's a sample calling a stored procedure with parameters and with a variety of data types.

SqlCommand cm= new SqlCommand();
cm.Connection= Connection;
cm.CommandType= CommandType.StoredProcedure;
cm.CommandText= "StoredProcedureName";
SqlParameter sp1 = cm.Parameters.Add( new SqlParameter("@.Field1",SqlDbType.Int,4)); sp1.Value = salesID;
SqlParameter sp2 = cm.Parameters.Add( new SqlParameter("@.Field2",SqlDbType.Int, 4)); sp2.Value = someIntValue;
SqlParameter sp3 = cm.Parameters.Add( new SqlParameter("@.Field3",SqlDbType.Money, 8));sp3.Value = someDoubleValue;
SqlParameter sp4 = cm.Parameters.Add( new SqlParameter("@.Field4",SqlDbType.Float, 8 )); sp4.Value = anotherDoubleValue;
SqlParameter sp5 = cm.Parameters.Add( new SqlParameter("@.Field5",SqlDbType.DateTime, 8)); sp5.Value = someDateTimeValue
Connection.Open();
cm.ExecuteNonQuery();
Connection.Close();

Inserting to multiple tables in SQL Server 2005 that use identity specification

Hi, I am having a bit of hassle with trying to enter details to multiple tables in SQL Server 2005.
I have four tables, an

Attendance Table (AttendanceID(PK Identity specific), MembershipNo, Date)
Resistance Table (ResistId(PK Identity specific), Weight , Reps, Sets)
Aerobics Tables(AerobicsID(PK Identity specific), MachineID, Intensity, Time)
and a linking table for all of them... ExerciseMaster(AttendanceID,ResistanceID,AerobicsI D)

My problem is that I can insert data to each specific table by itself using seperate insert statements....eg...

//insert an attendance record to the attendance table
string userID;

userID = Session["User"].ToString();

SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();

pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Attendance] ([MembershipNo], [Date]) VALUES (@.MembershipNo, @.Date)";
pgpDataSource.InsertParameters.Add("MembershipNo", userID);
pgpDataSource.InsertParameters.Add("Date", txtVisitDate.Text);

int RowsAffected = 0;

try
{
RowsAffected = pgpDataSource.Insert();
}

catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}

finally
{
pgpDataSource = null;
}

//insert an aerobics record into the aerocibs table

SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();

pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Aerobics] ([MachineID], [Intensity], [ExerciseTime]) VALUES (@.MachineID, @.Intensity, @.ExerciseTime)";

pgpDataSource.InsertParameters.Add("MachineID", rower.ToString());
pgpDataSource.InsertParameters.Add("Intensity", txtRowerLevel.Text);
pgpDataSource.InsertParameters.Add("ExerciseTime", txtRowerTime.Text);

int RowsAffected = 0;

try
{
RowsAffected = pgpDataSource.Insert();
}

catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}

finally
{
pgpDataSource = null;
}
//same code as above for the resistance table

However, i am facing the problem where this does not populate the link table(ExerciseMaster) with any information as i am unable to write the relevant IDs into the table that have been auto generated by SQL Server for each of the subTables.
I have read several forums where they recommend using something called @.@.IDENTITY but i have no idea how or where to use this in order to fill my exercise table...
Any help would be so much appreciated... Also, hopefully what i have said all makes sense and someone will be able to help me...oh and one more thing...this is an ASP.NET page coding in C#
Cheers
ScottyI think the problem is in your design. I'm thinking you should dump the ExerciseMaster table.

If you have a one-to-one relationship between attendance, Resistance, and Aerobics, then a single table will do. If you have a one-to-many relationship (for a given attendance, there may be zero or more resistance sessions and zero or more aerobics sessions) then this schema should work for you:

1. Attendance Table (AttendanceID(PK Identity specific), MembershipNo, Date)
2. Resistance Table (ResistId(PK Identity specific), AttendanceID(FK), Weight , Reps, Sets)
3. Aerobics Table(AerobicsID(PK Identity specific), AttendanceID(FK), MachineID, Intensity, Time)

You can insert the attendance record and then either recover the ID created using @.@.Identity or switch to GUID identifiers and let your interface create the ID. Then use that ID when you insert the Resistance and Aerobics records.|||Thanks Blindman, I see where you are coming from about the tables and I agree with what you have suggested. However, i am still a bit lost as to what the actual code would be to use the @.@.IDENTITY...
eg.
Suppose i have changed my database design to reflect your suggestions and want to enter code to the Aerobics table, how do i use the attendanceID that is used in the attendance table and enter it into the attendanceID in the Aerobics table..I have no idea how or where to use the @.@.IDENTITY in my code...

//insert an attendance record to the attendance table
string userID;

userID = Session["User"].ToString();

SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();

pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Attendance] ([MembershipNo], [Date]) VALUES (@.MembershipNo, @.Date)";
pgpDataSource.InsertParameters.Add("MembershipNo", userID);
pgpDataSource.InsertParameters.Add("Date", txtVisitDate.Text);

int RowsAffected = 0;

try
{
RowsAffected = pgpDataSource.Insert();
}

catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}

finally
{
pgpDataSource = null;
}

//insert an aerobics record into the aerocibs table

SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();

pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Aerobics] ([AttendanceID], [/COLOR][MachineID], [Intensity], [ExerciseTime]) VALUES (@.MachineID, @.Intensity, @.ExerciseTime)";

pgpDataSource.InsertParameters.Add("MachineID", rower.ToString());
pgpDataSource.InsertParameters.Add("Intensity", txtRowerLevel.Text);
pgpDataSource.InsertParameters.Add("ExerciseTime", txtRowerTime.Text);

int RowsAffected = 0;

try
{
RowsAffected = pgpDataSource.Insert();
}

catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}

finally
{
pgpDataSource = null;
}|||Well, the other thing you need to do is to stop issuing dynamic SQL code from your application. It is more complicated, less efficient, and insecure.

Write a store procedure called "InsertAttendance" that takes MembershipNo and Date as parameters. The stored procedure will insert the new record (after checking to make sure it does not already exist), and then call @.@.Identity (better yet, use @.@.SCOPEIDENTITY...) to get the ID that was just created. The sproc can pass the new ID back as an output parameter.

Or, as I said, you can switch to using GUIDs rather than identity integers as your primary key. Then you can actually create the new GUID within your application code, and you never have to query the database to find out what ID was assigned.|||is there any chance you could provide some sample code as to how i would call the @.@.IDENTITY, where i would call it from and how i would use it to actually write the information to another table ? im a bit lost as im very new to all this...
Thanks|||set nocount on
--Create a dummy table
create table SampleTable (IdentityColumn int identity, DummyData varchar(50))
go

--Create a dummy sproc
create procedure InsertSample (@.DummyValue varchar(50), @.NewIdentity int output)
as
begin
insert into SampleTable (DummyData) values(@.DummyValue)
set @.NewIdentity = Scope_Identity()
end
go

--Run some insert statements and check the ID values retrieved
declare @.NewID int
exec InsertSample 'Fubar1', @.NewIdentity = @.NewID output
select @.NewID
exec InsertSample 'Fubar2', @.NewIdentity = @.NewID output
select @.NewID
exec InsertSample 'Fubar3', @.NewIdentity = @.NewID output
select @.NewID

--Cleanup
drop procedure InsertSample
drop table SampleTable
go|||Thanks for that, Have that working but only when i execute the stored procedure from the database. I am still unable to actually use it unfortunatley within the application...

string userID;
const int macId = 3;

userID = Session["User"].ToString();

SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();
pgpDataSource.SelectCommandType = SqlDataSourceCommandType.Text;

pgpDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;
pgpDataSource.InsertCommand = "InsertAttendance";

pgpDataSource.InsertParameters.Add("MembershipNo", userID);
pgpDataSource.InsertParameters.Add("Date", txtVisitDate.Text);
//I think this may be the problem, both here and again below where
// i have the line of code commented out again...
//pgpDataSource.InsertParameters.Add("AttendanceID", newID.ToString());
pgpDataSource.InsertParameters.Add("MachineID", macId.ToString());
pgpDataSource.InsertParameters.Add("Intensity", txtRowerLevel.Text );
pgpDataSource.InsertParameters.Add("ExerciseTime", txtRowerTime.Text);
//pgpDataSource.InsertParameters.Add("AttendanceID", newID.ToString());
pgpDataSource.InsertParameters.Add("Reps", txtReps.Text);
pgpDataSource.InsertParameters.Add("Sets", txtSets.Text);

int RowsAffected = 0;

try
{
RowsAffected = pgpDataSource.Insert();
}

catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}

finally
{
pgpDataSource = null;
}

if (RowsAffected != 1)
{
Response.Redirect("~/Problem.aspx");
}
else
{
Response.Redirect("~/Log.aspx");
}
}

This is the storedProcedure i created to fill all three tables which works but not when i try to use it in the application...

ALTER PROCEDURE InsertAttendance

@.NewIdentity int output,
@.MembershipNo smallint,
@.Date datetime,
@.Reps smallint,
@.Sets smallint,
@.Weight smallint,
@.MachineID smallint,
@.Intensity smallint,
@.ExerciseTime int

AS

BEGIN

INSERT Attendance (MembershipNo, Date)
Values (@.MembershipNo, @.Date)

INSERT Aerobics (AttendanceID, MachineID, Intensity, ExerciseTime)
Values (@.NewIdentity, @.MachineID, @.Intensity, @.ExerciseTime)

INSERT Resistance ( AttendanceID, Reps, Sets, Weight)
VALUES (@.NewIdentity, @.Reps, @.Sets, @.Weight)
Set @.NewIdentity = Scope_Identity()

END|||How you call this stored procedure from your application code depends upon your programming platform. I don't code interfaces, so I can't help you with that. But I'd have a hard time believing that any respectable programming language would not have a method of calling a stored procedure and retrieving an output parameter.
I suggest you ask about this on a forum specific to your development platform.|||Are you sure this works properly? I would have thought that you would have to set the value of @.NewIdentity before you used it in the Insert statements for the Aerobics and Resistance tables.

[/QUOTE]

INSERT Attendance (MembershipNo, Date)
Values (@.MembershipNo, @.Date)

INSERT Aerobics (AttendanceID, MachineID, Intensity, ExerciseTime)
Values (@.NewIdentity, @.MachineID, @.Intensity, @.ExerciseTime)

INSERT Resistance ( AttendanceID, Reps, Sets, Weight)
VALUES (@.NewIdentity, @.Reps, @.Sets, @.Weight)
Set @.NewIdentity = Scope_Identity()

END[/QUOTE]

inserting to multiple fields using a button

I have multiple textboxes in a page. How do i make them insert their values to multiple fields on multiple tables using a button.

You can just execute some SqlCommands to insert data to different tables in the Click event of a button, for example:

protected void Button2_Click(object sender, EventArgs e)
{
using (SqlConnection conn = new SqlConnection(@."Data Source=labsh96223\iori2000;Integrated Security=SSPI;Database=tempdb"))
{
conn.Open();
string insertSql = @."Insert into myTbl_1 select @.id, @.name";
SqlCommand myCommand = new SqlCommand(insertSql, conn);
myCommand.Parameters.Add("@.id", SqlDbType.Int);
myCommand.Parameters.Add("@.name", SqlDbType.VarChar, 100);
myCommand.Parameters["@.id"].Value = Int32.Parse(TextBox1.Text);
myCommand.Parameters["@.name"].Value = TextBox2.Text;
int i = myCommand.ExecuteNonQuery();
myCommand.CommandText = @."Insert into myTbl_2 select @.id, @.Description";
myCommand.Parameters.Clear();
myCommand.Parameters.Add("@.id", SqlDbType.Int);
myCommand.Parameters.Add("@.Description", SqlDbType.VarChar, 1000);
myCommand.Parameters["@.id"].Value = Int32.Parse(TextBox3.Text);
myCommand.Parameters["@.name"].Value = TextBox4.Text;
i= myCommand.ExecuteNonQuery();
//execute other commands
}
}

Inserting to Dimension on Failed Lookup

First, I'm very new to SSIS, so forgive any stupid questions.

I'm trying to normalize some data by creating rows in a dimension table as I find new values in the original table.

My first thought is to use a Lookup to replace string values with id's to the Dimension table, and when the Lookup fails, insert the value into the Dimension table with a new key.

Does that make sense? This would be relatively easy to do with TSQL, but surely there is a way to do this in SSIS.

Thoughts?

Greg,

Why you do not, first hand, create a default(error, unknown, etc) member in every dimension and then if the lookup fails assign the id of the default. Anyway, I think that has been disscussed in previous threads; here is one.

http://forums.microsoft.com/msdn/showpost.aspx?postid=75211&siteid=1

Rafael Salas

|||Thanks! I will read that thread...

Responding to your suggestion, however, I can't use a default value because if the lookup doesn't work then a new record needs to be inserted into the Dimension table.

For example, if my row has "XXX" as a value and that value isn't in the Dimension table, I want to insert the row with the KEY rather than the VALUE("XXX") and insert the KEY/VALUE pair into the dimension table

Basically, I want to do an insert into dimension (select distinct value from facts) before I do a lookup to ensure that all lookups will be successful.|||

I read that thread, and the example that Thomas Pagel blogs on is what I'm looking for...

however, this really seems like an unnecessarily complicated approach.

Since I'm new to this, I'm inclined to think that I'm making it more difficult that it needs to be, but can't think of another approach.

If I was scripting this in TSQL, or SQL, I'd do a select of the distinct values from the FACTs table, insert those into the dimension, and then for each row in the FACTS table replace the value with the key from the new Dimension table.

Is this approach inconsistent with how SSIS is intended to be used?

|||

Greg Akins wrote:

however, this really seems like an unnecessarily complicated approach.

Since I'm new to this, I'm inclined to think that I'm making it more difficult that it needs to be, but can't think of another approach.

I really think this approach over complicates things. Not matches between a fact and its dimensions should be an exceptional case rather than the driver of your dimension load process.

Greg Akins wrote:

If I was scripting this in TSQL, or SQL, I'd do a select of the distinct values from the FACTs table, insert those into the dimension, and then for each row in the FACTS table replace the value with the key from the new Dimension table.

I would to exactly the same in SSIS; in general dimensions should be processed before the facts, even if like in your case, they come from the same source.

Greg Akins wrote:

Is this approach inconsistent with how SSIS is intended to be used?

Not sure if there is an answer for that...

Rafael Salas

|||OK. Thanks alot. I appreciate your advice.

Inserting the diff rows into a table

Hi,

I have two tables as follows

Table TempTab

{

CatId varchar(20),

lastupdate Datetime

}

Table MainTab

{

CatId varchar(20),

lastupdate Datetime

}

and the data in those tables are as follows

Table TempTab

{

CatId LastUpdate

Cat1 D1

Cat2 D2

Cat3 D3

Cat4 D4

}

Table MainTab

{

CatId LastUpdate

Cat1 D1

Cat3 D3

Cat5 D5

}

I need a query to insert the differences into the MinTab, i mean to say the fincal MainTab should look like as follows

Table MainTab

{

CatId LastUpdate

Cat1 D1

Cat2 D2

Cat3 D3

Cat4 D4

Cat5 D5

}

can any one please let me know the query

Thanks alot

~Mohan

INSERT INTO MainTab(CatID, LastUpdate)
SELECT CatID, LastUpdate FROM TempTab
WHERE CatID NOT IN(SELECT CatID FROM MainTab WHERE atID IS NOT NULL)
|||

Here it is,

Code Snippet

Create Table #temptab (

[CatId] Varchar(100) ,

[LastUpdate] Varchar(100)

);

Insert Into #temptab Values('Cat1','D1');

Insert Into #temptab Values('Cat2','D2');

Insert Into #temptab Values('Cat3','D3');

Insert Into #temptab Values('Cat4','D4');

Create Table #maintab (

[CatId] Varchar(100) ,

[LastUpdate] Varchar(100)

);

Insert Into #maintab Values('Cat1','D1');

Insert Into #maintab Values('Cat3','D3');

Insert Into #maintab Values('Cat5','D5');

Code Snippet

Insert Into #maintab

Select * from #temptab t

Where not exists

(

select 1 from #maintab m

where m.catid=t.catid

)

inserting the date into SQL Server problem

Im having problems inserting the current date into SQL server from my ASP.net application.

The error that asp.net throws is:

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated. "

In my page, im setting the date as follows:

Dim strDate as Date

strDate = now()

Then in my page I call a function called "do_store"

This works fin putting my variables into my database until I tried adding the date to the statement. Here is my function. Can anyone see my problem?

Public Function do_store(ByVal strTestID As String, ByVal strUserName As String, ByVal strUserID As String, ByVal strWrong As String, ByVal strRight As String, ByVal strAnswers As String, ByVal strDate As Date)
Dim strSQL As String
Dim sConn As String
Dim oConn As SqlConnection
'do the insert into the database
strSQL = "insert into test_results (test_plan_id, user_name, user_id, questions_wrong, questions_right, questions_answered, date_time) values (" & strTestID & ", '" & strUserName & "', " & strUserID & ", " & strWrong & ", " & strRight & ", " & strAnswers & ", '" & strDate & "')"
sConn = ConfigurationSettings.AppSettings("ConnectionString")
oConn = New SqlConnection(sConn)
Dim myCommand As SqlCommand

myCommand = New SqlCommand(strSQL, oConn)
myCommand.Connection.Open()
myCommand.ExecuteNonQuery()
End Function::Im having problems inserting the current date into SQL server from my ASP.net
::application.
::
::The error that asp.net throws is:
::
::"The conversion of a char data type to a datetime data type resulted in an out-of-range
::datetime value. The statement has been terminated. "

* Your datetime formates between the server and your process dont match.

Means: your server expects (for example) YMD, and you deliver YDM. Result: the date gets interpreted as month, and this can result in you "entering" month values from 13-31, which ARE out of range.

You need to make sure that the string you create actually is what the SQL Server expects.

BTW - if this is for a website, your code is insecure and very sucesseptable to a SQL INJECTION ATTACK.

Please read up the fundamental basics of SQL security - do it now.|||How do I check what format SQL Server wants?
how can I format the date so it fits into SQL server?
I got it all running locally (SQL Server and VS.NET) so Presumed their locale setting would be the same.
Im only building this as an intranet so its not open to the big bad world.|||This is explained in the SQL Server oducmentaiton. Actually you should not care - you should ALAWYS insert datetimes as ISO form, country independant. The correct way to insert this is:

return String.Format ("'{0:yyyy-MM-dd HH:mm:ss}'", Value);

At least this is what our EntityBroker O/R mapping framework uses, and it works so far.

The documentation of SQL Server has more information on this.

Inserting the date as defaultvalue in a sqldatasource Parameter

Hi Guys.

I am trying to insert the date as the default value into the DatePosted parameter in the sqldatasource object. I have put have the following below but it doesn't work. I have also tried <asp:Parameter Name="DatePosted" Type="DateTime" DefaultValue="<%= Date.Now %>" />

<asp:Parameter Name="DatePosted" Type="DateTime" DefaultValue="<%= now() %>" />

I know the solution is probably simple and I look like an idiot, but excuse me because I am very knew and fragile at this lol...

any help would be great :).

Mike.

Change your code to this,and it is OK.

<asp:Parameter Name="DatePosted" Type="DateTime" DefaultValue='<%# DateTime.Now %>' />

|||

Hi Jason,This does not work either. It says you can't use databind in a parameter I tried it with <%= %> as well but still nothing?

Any other ideas?

|||

If you can't override the parameter, it would be easiest to change the sql statement to reference getdate or getutcdate instead like:

INSERT INTO MyTable(col1,DatePosted) VALUES (@.col1,getutcdate())

If you have to allow for the parameter to be overridden, try putting this code into page_init:

SqlDatasource1.InsertParameters("DatePosted").DefaultValue=now

|||just do it at the database level via enterprise manager by modifying the table in question and set the default for the column by specifying the getdate() function so when a recorded is inserted via code with dateposted not supplied the sql server will insert that column with the current date.|||I have the same problem and this solution doesnt work. The column in the table has a default of getdate(), but I still get an error that NULL cannot be insert into the column. The problem is there is no way to tell it to not supply a value--if a value is not set it will supply NULL. Is there another way to default a DateTime parameter to DateTime.Now or getdate()? TIA|||

The solution I posted should work. The solution posted after mine should also work.

Please post your code.

|||

Doing it at the database level only works if you can remove it as a parameter entirely. In my case I needed to allow the user to enter datetime and only default it to getdate()/DateTime.Now when they choose not to enter specific date.

I knew of the page init method you suggested, but I was trying to avoid this as I was experimenting with how much you can do with only declarative code--I guess I just hit one of the limits of declarative coding.

(For the record, I much prefer DDD with NHibernate, NUnit, etc., but in some cases, especially for small/trivial/non-critical projects, being able to do things quickly outweighs other concerns.)

inserting the current date and time into SQL Server database

I need an SQL string that inserts the current date into a database.

So far I have tried:

SQL = "INSERT INTO X (START_DATE) VALUES('" & Date.Now &"')"

mycomm =

New SqlCommand(sql, myconn)

mycomm.ExecuteNonQuery()

However, there is a problem with the SQL command. The problem is related to the date. Is there a way of programatically inserting the current date/time into the SQL database? Language used is VB.

GetDate() is a function in SQL Server that returns the current timestamp. So you can directly use that.

SQL = "INSERT INTO X (START_DATE) VALUES( GetDate())"

Inserting TextBox values to database using SqlDataSource.Insert method

Hi, it is few days I posted here my question, but received no answer. Maybe the problem is just my problem, maybe I put my question some strange way. OK, I try to put it again, more simply.

I have few textboxes, their values I need to transport to database. I set SqlDataSource, parameters... and used SqlDataSource.Insert() method. I got NULL values in the database's record. So I tried find problem by using Microsoft's sample code from addresshttp://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.insert.aspx. After some changes I tried that code and everything went well, data were put into database. Next step was to separate code beside and structure of page to two separate files followed by new test. Good again, data were delivered to database. Next step: to use MasterPage, very simple, just with one ContentPlaceHolder. After this step the program stoped to deliver data to database and delivers only NULLs to new record. It is exactly the same problem which I have found in my application. The functionless code is here:

http://forums.asp.net/thread/1437716.aspx

I cannot find any answer this problem on forums worldwide. I cannot believe it is only my problem. I compared html code of two generated pages - with maserPage and without. There are differentions in code in ids' of input fields generated by NET.Framework:

Without masterpage:

<input name="NazevBox" type="text" id="NazevBox" />
<span id="RequiredFieldValidator1" style='color:Red;visibility:hidden;'>Please enter a company name.</span>
<p>
<input name="CodeBox" type="text" id="CodeBox" />
<span id="RequiredFieldValidator2" style='color:Red;visibility:hidden;'>Please enter a phone number.</span>
<p>
<input type="submit" name="Button1" value="Insert New Shipper" onclick="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("Button1", "", true, "", "", false, false))" id="Button1" />

With masterpage:

<input name="ctl00$Obsah$NazevBox" type="text" id="ctl00_Obsah_NazevBox" />
<span id="ctl00_Obsah_RequiredFieldValidator1" style='color:Red;visibility:hidden;'>Please enter a company name.</span>
<p>
<input name="ctl00$Obsah$CodeBox" type="text" id="ctl00_Obsah_CodeBox" />
<span id="ctl00_Obsah_RequiredFieldValidator2" style='color:Red;visibility:hidden;'>Please enter a phone number.</span>
<p>
<input type="submit" name="ctl00$Obsah$Button1" value="Insert New Shipper" onclick="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("ctl00$Obsah$Button1", "", true, "", "", false, false))" id="ctl00_Obsah_Button1" />

In second case ids' of input fields have different names, but I hope it is inner business of NET.Framework.

There must be something I haven't noticed, maybe NET's bug, maybe my own. Thanks for any suggestion.

Had a look at both of your posts, mostly the first.

By using the Inserting event for the SqlDataSource control, you can programmatically supply the parameters and their values.

http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.inserting.aspx

|||Thank you for your reply. Your solution works fine (and resolves this bug of NET.Framework 2.0 - I think it is little bug.)

Inserting Text into table replaces apostrophes with ?

When we insert text into field in a table, SQL SERVER apparently
replaces apostrophes with question marks -- is there a way to not have
this occur? We don't have this happen with the mySQL databases that
we also support.

Much help appreciated.I've encountered users coying and pasting text from MS Word and this exact
issue occurred. My problem was the MS 'smart quotes' so I had to replace
them prior to inserting.

Rob

"TNR" <stage7@.tgs.fr> wrote in message
news:bg3efc$jv5$1@.news-reader3.wanadoo.fr...
> Try to double the apostrophes.
> "kalbee" <kalbee@.einsof.com> wrote in message
> news:45082167.0307280703.29b9d428@.posting.google.c om...
> > When we insert text into field in a table, SQL SERVER apparently
> > replaces apostrophes with question marks -- is there a way to not have
> > this occur? We don't have this happen with the mySQL databases that
> > we also support.
> > Much help appreciated.

Inserting Text Into SQL 2005 Database Padded With Spaces

I have a Detailsview with Insert and Update options connected to a SQL 2005 table with templated textboxes for input. The textboxes have maxlength set to the number of characters in each respective field in the SQL 2005 table. When text is inserted it gets padded with spaces if all the field length is not used. When you try to edit the field the cursor does not move because of the padded spaces. The End key must be pressed to find the end of the string and the padded space removed before adding edited text. I am working in VB.net. If I check the field in SQL Studio Express is shows the text I typed plus blank space for the remainder of the field.

My question is how can I add text to the textboxes without the padded spaces being added when the maxlength of the field is not used?

if you look at the datatype of the data columns in the sql database, you will most likely find that it it set tochar.
the char datatype automatically pads with spaces to completely fill the allocated space.

If you prefer not to get these spaces, then you would want to use thevarchar datatype when setting up your table in sql server.
the varchar datatype will not add the padding.

Note: if you change the datatype of a column in an existing database, you will likely need to trim the existing data as it will still contain the padding. however, new data added to a varchar column will not get the padding.

|||

Thank you that worked like a charm. Three questions before I close this out:

1) How does the type nchar work? It comes up always as the default? Which type is the best?

2. Should I expect any suprises in my code where in some places I have embedded Rtrim statements?

3. When binding a variable to the table is there any format { } string that can be used to achieve the no padding result? I saw in a forum some place about the F parameter to reduce spaces but I did not know how to implement it?

|||

1) nchar is for storing fixed length unicode character data.
char will pad the data if created with ansi_padding set on. nchar always pads. http://msdn2.microsoft.com/en-us/library/ms175055.aspx
Which is best depends on your needs. As you've noticed, char/nchar might require to you to trim off whitespace.
For most text columns, I prefer varchar (or nvarchar for data i need to store as unicode).

2) Trimming the data with RTrim is unaffected by your use of char/varchar - other than it becomes unnecessary if your data is not padded. Note that the String type contains a TrimEnd method (among others) which i prefer over the trim methods from the visualbasic library.

3) I'm not aware of a standard format code that would trim the padding.

Here's an interesting thread:http://www.sqljunkies.com/Forums/ShowPost.aspx?PostID=7978

inserting text after a certain record of a recordset

What I'm trying to accomplish is to add some text after the 5th
dispalyed record of a recrod set, I tried using absolute position and
the following:
<%
if rs.absoluteposition = 5 then
Response.Write "Testing"
end if
%>
I'm probably missing something or there is some better way to do this,
thanks for any assistance in advance.counter = 0
do while not rs.eof
counter = counter + 1
if counter = 5 then
response.write "testing"
end if
rs.movenext
loop
<dabootleg@.gmail.com> wrote in message
news:1134328540.390884.113180@.g47g2000cwa.googlegroups.com...
> What I'm trying to accomplish is to add some text after the 5th
> dispalyed record of a recrod set, I tried using absolute position and
> the following:
> <%
> if rs.absoluteposition = 5 then
> Response.Write "Testing"
> end if
> %>
> I'm probably missing something or there is some better way to do this,
> thanks for any assistance in advance.
>

inserting text > 65535

hi,
i want to insert text of size > 65535 in a text column of sql-server. I'm presently using mcp command to do this, but this utility doesn't allow these long files, and work well upto 65 kb files.
is there a way out.
thanks in advanceThis error had been noted back in Version 4.2 of SQL, see technet article
BUG: BCP Cannot Copy Text/Image Data > 64K from SQL Server (Q111921) (http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q111921)

Try setting the packet size on BCP (flag -a) to the max which is 65535|||That doesn't work... I set the size 100000. When I inserted small files that were loaded smoothly. But for bigger files it says max packaet size is 65k.

I rephrase my problem again:::::
=====================
I'm working with sql server.
I need to load large xml files in the databse, fragment it into the
tables.
(1) I don't know a way to load file with the help of script that can load
text value of > 65k. I'm presently doing with the help of "mcp" command.
But this has got size limitation. So how do u load big file in some of the
"text" field of a table.
(2) I need to do fragmentation of this file. But I cannot declare text
type variable in the procedure. I am able to parse files of upto 8000
characters, but how do I do it if I have a document > 8000 char stored in
a "text" field. I just don't know how to call sp_xml_preparedocument with
a text attribute. I think u cannot say something like
sp_xml_preparedocument @.idoc out,(select textfield from doctable)
where idoc and textfield are integer and text respectively.

INSERTING TEXT

Hello,
Can someone tell me how to insert text into a table containing a TEXT field?
How to update?
I would like to create a stored procedure that take a text parameter and
inserts it into a new column in my table. Then do the same stored procedure
but this time to update the TEXT field.
HELP!
YamaInsert is simple.
INSERT table_name(int_column, text_column) VALUES(1, ' ... really long text
... ')
Update is a little trickier. In the apps I write, I pull out the whole
value, let the user edit it as a whole, and write the whole value back. As
you might know, you can't append to a TEXT column (so you can't do UPDATE
table_name SET text_column += @.varcharParam), and you can't even manipulate
it locally because you can't have a local variable of type TEXT.
If you are adding things to the end of a text column, you might consider
having a separate related table with comments. That way you can track them
separately, and you don't have to worry about concatenation.
The only time I would ever update is if I have to a batch search and replace
on all rows, e.g. see http://www.aspfaq.com/2445
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Yama" <ykamyar@.grandpacificresorts.com> wrote in message
news:#4zAO0w2DHA.1532@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Can someone tell me how to insert text into a table containing a TEXT
field?
> How to update?
> I would like to create a stored procedure that take a text parameter and
> inserts it into a new column in my table. Then do the same stored
procedure
> but this time to update the TEXT field.
> HELP!
> Yama
>|||Hi Aaron,
Are you sure the INSERT will work even with more than 8000 characters?
I thought you had to use the WRITETEXT command?
Yama
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23MPf2fy2DHA.2680@.tk2msftngp13.phx.gbl...
> Insert is simple.
> INSERT table_name(int_column, text_column) VALUES(1, ' ... really long
text
> ... ')
> Update is a little trickier. In the apps I write, I pull out the whole
> value, let the user edit it as a whole, and write the whole value back.
As
> you might know, you can't append to a TEXT column (so you can't do UPDATE
> table_name SET text_column += @.varcharParam), and you can't even
manipulate
> it locally because you can't have a local variable of type TEXT.
> If you are adding things to the end of a text column, you might consider
> having a separate related table with comments. That way you can track
them
> separately, and you don't have to worry about concatenation.
> The only time I would ever update is if I have to a batch search and
replace
> on all rows, e.g. see http://www.aspfaq.com/2445
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Yama" <ykamyar@.grandpacificresorts.com> wrote in message
> news:#4zAO0w2DHA.1532@.TK2MSFTNGP10.phx.gbl...
> > Hello,
> >
> > Can someone tell me how to insert text into a table containing a TEXT
> field?
> > How to update?
> >
> > I would like to create a stored procedure that take a text parameter and
> > inserts it into a new column in my table. Then do the same stored
> procedure
> > but this time to update the TEXT field.
> >
> > HELP!
> >
> > Yama
> >
> >
>|||> Are you sure the INSERT will work even with more than 8000 characters?
Yes, though it depends from where it came. Some providers / clients will
truncate because they don't know how to deal with >255 or >8000.
> I thought you had to use the WRITETEXT command?
I've never used the WRITETEXT command in production systems. www.aspfaq.com
stores articles in a TEXT column and I have no problems using INSERT /
UPDATE through a web interface. All I can suggest is that you keep your
drivers up to date (e.g. MDAC 2.8) and test your environment before taking
my word for it.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||My Dear Friend,
Here is how I settled doing it.
In my ASPX web page I have four fields. A dropdown with a list of all the
letters, a checkbox for activating a letter or setting it to innactive, a
text box for the description of a letter, and another text box for the
letter with multiple line enabled. Hope you like this... :-)
--The table:
CREATE TABLE [dbo].[tblLetter] (
[LetterID] [numeric](9, 0) IDENTITY (1, 1) NOT NULL ,
[Letter] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Active] [bit] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
--The stored procedure:
CREATE PROCEDURE SaveLetter
@.Description VARCHAR(100) = '',
@.Active BIT = 1,
@.BlobLetter TEXT = '',
@.blnInsert BIT = 0,
@.LetterNumber INT = NULL,
@.blnDelete BIT = 0
AS
DECLARE @.s BINARY(16)
IF @.blnInsert = 1 AND @.blnDelete = 0
BEGIN
BEGIN TRAN
DECLARE @.ID INT
INSERT INTO tblLetter
(Description, Active, Letter) VALUES (@.Description, @.Active, @.BlobLetter)
SET @.ID = @.@.IDENTITY
SELECT @.s = TEXTPTR( Letter )
FROM tblLetter
WHERE LetterID = @.@.IDENTITY
WRITETEXT tblLetter.Letter @.s @.BlobLetter
COMMIT TRAN
END
IF @.blnInsert = 0 AND @.blnDelete = 0
BEGIN
BEGIN TRAN
UPDATE tblLetter
SET Description = @.Description , Active = @.Active
WHERE LetterID = @.LetterNumber
SELECT @.s = TEXTPTR(Letter)
FROM tblLetter
WHERE LetterID = @.LetterNumber
WRITETEXT tblLetter.Letter @.s @.BlobLetter
COMMIT TRAN
END
IF @.blnDelete = 1
BEGIN
DELETE FROM tblLetter
WHERE LetterID = @.LetterNumber
END
GO
Yama Kamyar
Senior Microsoft .NET Consultant
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23SAJJZ62DHA.1740@.TK2MSFTNGP09.phx.gbl...
> > Are you sure the INSERT will work even with more than 8000 characters?
> Yes, though it depends from where it came. Some providers / clients will
> truncate because they don't know how to deal with >255 or >8000.
> > I thought you had to use the WRITETEXT command?
> I've never used the WRITETEXT command in production systems.
www.aspfaq.com
> stores articles in a TEXT column and I have no problems using INSERT /
> UPDATE through a web interface. All I can suggest is that you keep your
> drivers up to date (e.g. MDAC 2.8) and test your environment before taking
> my word for it.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>

INSERTING TEXT

Hello,
Can someone tell me how to insert text into a table containing a TEXT field?
How to update?
I would like to create a stored procedure that take a text parameter and
inserts it into a new column in my table. Then do the same stored procedure
but this time to update the TEXT field.
HELP!
YamaInsert is simple.
INSERT table_name(int_column, text_column) VALUES(1, ' ... really long text
... ')
Update is a little trickier. In the apps I write, I pull out the whole
value, let the user edit it as a whole, and write the whole value back. As
you might know, you can't append to a TEXT column (so you can't do UPDATE
table_name SET text_column += @.varcharParam), and you can't even manipulate
it locally because you can't have a local variable of type TEXT.
If you are adding things to the end of a text column, you might consider
having a separate related table with comments. That way you can track them
separately, and you don't have to worry about concatenation.
The only time I would ever update is if I have to a batch search and replace
on all rows, e.g. see http://www.aspfaq.com/2445
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Yama" <ykamyar@.grandpacificresorts.com> wrote in message
news:#4zAO0w2DHA.1532@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hello,
> Can someone tell me how to insert text into a table containing a TEXT

---

field?

quote:

---

> How to update?
> I would like to create a stored procedure that take a text parameter and
> inserts it into a new column in my table. Then do the same stored

---

procedure

quote:

---

> but this time to update the TEXT field.
> HELP!
> Yama
>

---

|||Hi Aaron,
Are you sure the INSERT will work even with more than 8000 characters?
I thought you had to use the WRITETEXT command?
Yama
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23MPf2fy2DHA.2680@.tk2msftngp13.phx.gbl...

quote:

---

> Insert is simple.
> INSERT table_name(int_column, text_column) VALUES(1, ' ... really long

---

text

quote:

---

> ... ')
> Update is a little trickier. In the apps I write, I pull out the whole
> value, let the user edit it as a whole, and write the whole value back.

---

As

quote:

---

> you might know, you can't append to a TEXT column (so you can't do UPDATE
> table_name SET text_column += @.varcharParam), and you can't even

---

manipulate

quote:

---

> it locally because you can't have a local variable of type TEXT.
> If you are adding things to the end of a text column, you might consider
> having a separate related table with comments. That way you can track

---

them

quote:

---

> separately, and you don't have to worry about concatenation.
> The only time I would ever update is if I have to a batch search and

---

replace

quote:

---

> on all rows, e.g. see http://www.aspfaq.com/2445
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Yama" <ykamyar@.grandpacificresorts.com> wrote in message
> news:#4zAO0w2DHA.1532@.TK2MSFTNGP10.phx.gbl...
> field?
> procedure
>

---

|||> Are you sure the INSERT will work even with more than 8000 characters?
Yes, though it depends from where it came. Some providers / clients will
truncate because they don't know how to deal with >255 or >8000.

quote:

---

> I thought you had to use the WRITETEXT command?

---

I've never used the WRITETEXT command in production systems. www.aspfaq.com
stores articles in a TEXT column and I have no problems using INSERT /
UPDATE through a web interface. All I can suggest is that you keep your
drivers up to date (e.g. MDAC 2.8) and test your environment before taking
my word for it.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||My Dear Friend,
Here is how I settled doing it.
In my ASPX web page I have four fields. A dropdown with a list of all the
letters, a checkbox for activating a letter or setting it to innactive, a
text box for the description of a letter, and another text box for the
letter with multiple line enabled. Hope you like this... :-)
--The table:
CREATE TABLE [dbo].[tblLetter] (
[LetterID] [numeric](9, 0) IDENTITY (1, 1) NOT NULL ,
[Letter] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Active] [bit] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
--The stored procedure:
CREATE PROCEDURE SaveLetter
@.Description VARCHAR(100) = '',
@.Active BIT = 1,
@.BlobLetter TEXT = '',
@.blnInsert BIT = 0,
@.LetterNumber INT = NULL,
@.blnDelete BIT = 0
AS
DECLARE @.s BINARY(16)
IF @.blnInsert = 1 AND @.blnDelete = 0
BEGIN
BEGIN TRAN
DECLARE @.ID INT
INSERT INTO tblLetter
(Description, Active, Letter) VALUES (@.Description, @.Active, @.BlobLetter)
SET @.ID = @.@.IDENTITY
SELECT @.s = TEXTPTR( Letter )
FROM tblLetter
WHERE LetterID = @.@.IDENTITY
WRITETEXT tblLetter.Letter @.s @.BlobLetter
COMMIT TRAN
END
IF @.blnInsert = 0 AND @.blnDelete = 0
BEGIN
BEGIN TRAN
UPDATE tblLetter
SET Description = @.Description , Active = @.Active
WHERE LetterID = @.LetterNumber
SELECT @.s = TEXTPTR(Letter)
FROM tblLetter
WHERE LetterID = @.LetterNumber
WRITETEXT tblLetter.Letter @.s @.BlobLetter
COMMIT TRAN
END
IF @.blnDelete = 1
BEGIN
DELETE FROM tblLetter
WHERE LetterID = @.LetterNumber
END
GO
Yama Kamyar
Senior Microsoft .NET Consultant
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23SAJJZ62DHA.1740@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Yes, though it depends from where it came. Some providers / clients will
> truncate because they don't know how to deal with >255 or >8000.
>
> I've never used the WRITETEXT command in production systems.

---

www.aspfaq.com

quote:

---

> stores articles in a TEXT column and I have no problems using INSERT /
> UPDATE through a web interface. All I can suggest is that you keep your
> drivers up to date (e.g. MDAC 2.8) and test your environment before taking
> my word for it.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>

---

Inserting Text

Could someone help me, please?

How do I insert text, something like a header, before the result of the query presents itself?

I want the text to appear before the result. Not like an alias, that I know how to do.

Thanks.

SELECT 'This is my Header Message'

SELECT colList FROM MyTable

OR

SELECT
'This is My Header Message',
Col1,
Col2,
etc
FROM MyTable

Inserting Symbols into Fields (like ?)

I am attempting to insert a trademark symbol? into a database field but it is not working. Does anyone have any ideas on how to do this properly?

Any help would be very appreciated.

Since the Symbol ? is Ascii charater it should insert on Both Varchar & Nvarchar fields properly. The equalent to the symbol ? is char(153)

|||

insert mytable
(col1)
values
(char(153))

Inserting symbols in field

Hi All,
I am trying to insert symbols in a field of a table.

I am using the datatype nvarchar for the field.
However when i tried to insert a statement with the symbol pi, it comes out as following:
(A) 100? cm2 (B) 140? cm2 (C) 200? cm2

All the pi symbol are converted to ?.

Can ne 1 tell me how i can store such strings in the field.

Thanks

Both the client tool that inserts the symbol AND the client tool that displays the symbol MUST be capable of displaying the symbols. And the symbol for p is not included in the ascii set used by SSMS.

You can store unicode, and you can display unicode -you just can't do it with the SQL Server client tools.

How are you displaying the fields (what application?)

inserting symbols in field

Hi All,
I am trying to insert symbols in a field of a table.

I am using the datatype nvarchar for the field.
However when i tried to insert a statement with the symbol pi, it comes out as following:
(A) 100? cm2 (B) 140? cm2 (C) 200? cm2

All the pi symbol are converted to ?.

Can ne 1 tell me how i can store such strings in the field.

Thanks

You're not stating how you're inserting the strings. If you're inserting using a plain INSERT statement you must N-prefix the string literals when using nvarchar.

INSERT INTO thetable (thecolumn,anothercolumn) VALUES(N'whatever',N'however')

inserting string with quotes

Hi Guys,
i want to insert a string for example 'abcd'edfg'gg'into a
table in sql server 2000.
but it is not working but giving the error
"not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are
not permitted."
will this require any sp_configue or db_option changes?
pls advice me.Use two single quotes instead of one like: 'abcd''edfg''gg'
Anith

Inserting SSAS cube data into an SQL table

I'm trying to find a way to automatedly insert some data from my cube into a table in my SQL database.

Within the "browser" the data I'm interested in is a single dimension on the Y-axis and a couple measures. Also, I have a couple filters set up. The columns will always be the same, so the data should always fit into the same table.

I have tried to set up an Integration Services data transformation package for this. I have set up my source to be 'Ole DB', which lets you pick SSAS as a source. The problem is that the source editor seems to only allow adding a dimension, without any of the rich detail allowed by the "browser" interface. What the heck am I missing here?

Thanks, Joe

I'm not sure if this works or not. I did have a play with it at one point, but did not have much luck. It might be worth asking in the SSIS forum too.

What I have done in the past is to set up a linked server to SSAS and then used SELECT ... FROM OPENQUERY(LinkedSSAS, 'MDX Query') However this does mean that you need a bit of working knowledge of MDX. There are a couple of options there. If you set up a Reporting Services report you could use the MDX query builder there to design your query, it works "ok" if you only need measures on the columns, but it will not let you put other dimensions in the column axis. You could also run a profiler trace on SSAS while you refresh your browser, but some of the browsers break their queries into pieces, creating session level sets which make it harder to capture the entire query.

|||

Thanks for the reply Darren as it got me on the right track. I ended up writing my query in MDX and then the problem became, getting an MDX query into SSIS, which has been written about in the SSIS forums. Strangely, there isn't an obvious MDX data source in SSIS. What I did was use OLE DB "SQL Command" for the data access mode. Also, I needed to add "Format=Tabular" to the connection string... to get things running "smoothly". I wonder why this isn't more straightforward.

Here is the thread that talks about all this:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=408058&SiteID=1

-Joe

inserting SQL statement in a table

Hi,
I want to insert an SQL statement in an sql table, i think i need an escape
character for the single quotes in statement?
declare @.statement varchar(255)
select @.statement = "select name from employees where fname = 'john' and
lname like 'chr%'"
insert into sqltable (str_text) values (@.statement)
If i use double quotes or braces in the statement, then i get an error.
Thanks in advance.
MAQMAQ
It's a good idea to use stored procedure that accepts parameters
Also , I hope you are aware of SQL injections, so with stored procedure you
will be more safely.
CREATE PROC spMyProc
@.fname VARCHAR(20),
@.lname VARCHAR(20)
AS
SELECT <column lists> FROM Table WHERE fname =@.fname AND lname LIKE @.lname
+'%'
GO
EXEC spMyProc 'John','Braun'
"MAQ" <maq@.nos.pam> wrote in message
news:%23RpXXGmRFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to insert an SQL statement in an sql table, i think i need an
escape
> character for the single quotes in statement?
> declare @.statement varchar(255)
> select @.statement = "select name from employees where fname = 'john' and
> lname like 'chr%'"
> insert into sqltable (str_text) values (@.statement)
> If i use double quotes or braces in the statement, then i get an error.
> Thanks in advance.
>
> MAQ
>|||Hi,
Thanks for the answer. I know i can do it very easily through stored
procedure. But the problem is that i am customising an existing very large
database. and they have all the SQL statements in a table. I only need to
modify some of those statements. Though i can do it through enterprise
manager directly, but I have to create an sql script which updates those
statements in the table.
/MAQ
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OJ86wKmRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> MAQ
> It's a good idea to use stored procedure that accepts parameters
> Also , I hope you are aware of SQL injections, so with stored procedure
> you
> will be more safely.
> CREATE PROC spMyProc
> @.fname VARCHAR(20),
> @.lname VARCHAR(20)
> AS
> SELECT <column lists> FROM Table WHERE fname =@.fname AND lname LIKE @.lname
> +'%'
> GO
> EXEC spMyProc 'John','Braun'
>
>
> "MAQ" <maq@.nos.pam> wrote in message
> news:%23RpXXGmRFHA.1564@.TK2MSFTNGP09.phx.gbl...
> escape
>|||use double single quotes inside the string and single quotes for statement
itself:
select @.statement = 'select name from employees where fname = ''john'' and
lname like ''chr%'''
that will do the trick
Hope it helps
"Uri Dimant" wrote:

> MAQ
> It's a good idea to use stored procedure that accepts parameters
> Also , I hope you are aware of SQL injections, so with stored procedure yo
u
> will be more safely.
> CREATE PROC spMyProc
> @.fname VARCHAR(20),
> @.lname VARCHAR(20)
> AS
> SELECT <column lists> FROM Table WHERE fname =@.fname AND lname LIKE @.lname
> +'%'
> GO
> EXEC spMyProc 'John','Braun'
>
>
> "MAQ" <maq@.nos.pam> wrote in message
> news:%23RpXXGmRFHA.1564@.TK2MSFTNGP09.phx.gbl...
> escape
>
>

Inserting sql script

Hi, I was wondering if anyone knows how to import an sql script to the MSSQL server using the SQL Server enterprise manager? I have some scripts that I want to use and I don't want to manually create the database if I have the scripts.You can use Query Analyzer for these types of activities

Inserting Specific Value in Query Result

I have a table where I need to insert a unique USERID based on the
USERNAME. Seems pretty simple to me, however I am a novice writing SQL
statements.
My knowledge is very limited, so when responding please do not assume I
know basic code.
I have taken on this task in an emergency situation.
Thanks in advance for your help and please let me know if further
information is needed.DK13 (DericK@.sklarcorp.com) writes:
> I have a table where I need to insert a unique USERID based on the
> USERNAME. Seems pretty simple to me, however I am a novice writing SQL
> statements.
> My knowledge is very limited, so when responding please do not assume I
> know basic code.
> I have taken on this task in an emergency situation.
If it's an emergencty I am puzzled by the fact that you did not provide
more information. The standard recommendation for assistance is that
you post:
o CREATE TABLE statement(s) for the tables involved.
o INSERT statements with sample data.
o The desired result given the sample.
This permits anyone who wants to answer your question to easily
copy-and-paste into a query and developer a tested solution.
I realise that you if don't know basic code, this may go over your
head. But you should at least be able to produce some sample input,
and the output from it. "A unique USERID based on the USERNAME" is
quite ambiguous. And supposedly there is a requirement that these
user id follows some pattern. It would also be interesting to know
how these usernames look like. Furthermore, it is also unclear whether
you want the userid added to the query result, or a column added
to the table. (Your subject lines says the former, but the latter
makes more sense.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||OK, let's see if this helps.
The query I ran:
select* from sysdba.HISTORY where USERNAME = ('Cruz, Jane')
The result set looks like this(I've left out several columns):
USERID USERNAME ORIGINALDATE
<NULL> Cruz, Jane 2/6/2006 12:00:05 AM
<NULL> Cruz, Jane 2/6/2006 11:25:00 PM
Now from this result I would like to Insert a userid, such as
'U6UJ9A00003J' where the USERID above = "<NULL>". I have several users
that I need to do this for.
Let me know if this is more useful info|||DK13 (DericK@.sklarcorp.com) writes:
> OK, let's see if this helps.
> The query I ran:
> select* from sysdba.HISTORY where USERNAME = ('Cruz, Jane')
> The result set looks like this(I've left out several columns):
> USERID USERNAME ORIGINALDATE
><NULL> Cruz, Jane 2/6/2006 12:00:05 AM
><NULL> Cruz, Jane 2/6/2006 11:25:00 PM
> Now from this result I would like to Insert a userid, such as
> 'U6UJ9A00003J' where the USERID above = "<NULL>". I have several users
> that I need to do this for.
> Let me know if this is more useful info
You could do:
UPDATE tbl
SET USERID = dbo.createuseridfromname(USERNAME)
WHERE USERID IS NULL
All that remains is to write the user-defined function. Unfortunately,
I cannot do that, because I don't know the rules.
Or are the user ids in fact already defined in a table somewhere?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you, I did some other research and found the same function to be
helpful, much appreciated!!

Inserting special characters

Hi
I have a web page where users insert some comments into a text area. users
are able to insert any character into this area. When they submit the
character ' and " causes problems. How can i allow these characters to be
inserted into the database( sql server 2000)?
I am using Java server page to process and insert the comments to the
database.
thanksHi,
Not quite sure what you are trying exactly. Just try using NVARCHAR data
type and see if it helps.
Thanks
Hari
SQL Server MVP
"panda" <panda@.discussions.microsoft.com> wrote in message
news:CF0A3B21-D97F-44C6-800B-88B3B54E501C@.microsoft.com...
> Hi
> I have a web page where users insert some comments into a text area. users
> are able to insert any character into this area. When they submit the
> character ' and " causes problems. How can i allow these characters to be
> inserted into the database( sql server 2000)?
> I am using Java server page to process and insert the comments to the
> database.
> thanks|||This would be less of a problem if you were using stored procedures. Since
you are probably sending query strings from the web page to the SQL Server,
you may be very vulnerable to SQL Injection attacks. (Write to me off line
and I will give you more information about your vulnerability.)
For the single quote, if you must store them, have the application double
(two single quotes) them before sending to SQL Server. The double quote
shouldn't be a problem -please confirm how you are experiencing the problem.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"panda" <panda@.discussions.microsoft.com> wrote in message
news:CF0A3B21-D97F-44C6-800B-88B3B54E501C@.microsoft.com...
> Hi
> I have a web page where users insert some comments into a text area. users
> are able to insert any character into this area. When they submit the
> character ' and " causes problems. How can i allow these characters to be
> inserted into the database( sql server 2000)?
> I am using Java server page to process and insert the comments to the
> database.
> thanks|||HI,
I am trying to insert a ' charcerter. However the SQL syntax for inserting
nvarchar or strings use the ' character to determine the begining and the
end.
How do i go about inserting a ' character into the database?
"Hari Prasad" wrote:
> Hi,
> Not quite sure what you are trying exactly. Just try using NVARCHAR data
> type and see if it helps.
> Thanks
> Hari
> SQL Server MVP
> "panda" <panda@.discussions.microsoft.com> wrote in message
> news:CF0A3B21-D97F-44C6-800B-88B3B54E501C@.microsoft.com...
> > Hi
> >
> > I have a web page where users insert some comments into a text area. users
> > are able to insert any character into this area. When they submit the
> > character ' and " causes problems. How can i allow these characters to be
> > inserted into the database( sql server 2000)?
> >
> > I am using Java server page to process and insert the comments to the
> > database.
> >
> > thanks
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_05F8_01C6C63E.8066A380
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
Use two of them, for example:
CREATE TABLE #MyTable
( RowID int IDENTITY
, MyStringValue varchar(100)
)
INSERT INTO #MyTable VALUES ('"This isn''t so obvious, is it?", said =Bill O''Shea to Terrance O''Donald.')
SELECT MyStringValue FROM #MyTable
DROP TABLE #MyTable
Hope that this helps...
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
"panda" <panda@.discussions.microsoft.com> wrote in message =news:26E49E21-4F45-4F5E-ABD2-BD5E5D8771C4@.microsoft.com...
> HI,
> > I am trying to insert a ' charcerter. However the SQL syntax for =inserting > nvarchar or strings use the ' character to determine the begining and =the > end. > > How do i go about inserting a ' character into the database?
> > "Hari Prasad" wrote:
> >> Hi,
>> >> Not quite sure what you are trying exactly. Just try using NVARCHAR =data >> type and see if it helps.
>> >> Thanks
>> Hari
>> SQL Server MVP
>> >> "panda" <panda@.discussions.microsoft.com> wrote in message >> news:CF0A3B21-D97F-44C6-800B-88B3B54E501C@.microsoft.com...
>> > Hi
>> >
>> > I have a web page where users insert some comments into a text =area. users
>> > are able to insert any character into this area. When they submit =the
>> > character ' and " causes problems. How can i allow these characters =to be
>> > inserted into the database( sql server 2000)?
>> >
>> > I am using Java server page to process and insert the comments to =the
>> > database.
>> >
>> > thanks >> >> --=_NextPart_000_05F8_01C6C63E.8066A380
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Use two of them, for =example:
CREATE TABLE =#MyTable ( =RowID int IDENTITY , MyStringValue varchar(100) )
INSERT INTO #MyTable VALUES ('"This isn''t so obvious, is it?", =said Bill O''Shea to Terrance O''Donald.')
SELECT MyStringValue FROM #MyTable
DROP TABLE #MyTable
Hope that this helps...
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
"panda" wrote in message news:26E49E21-4F45-4F5E-ABD2-BD5E5D8771C4@.microsoft.com...> =HI,> > I am trying to insert a ' charcerter. However the SQL syntax =for inserting > nvarchar or strings use the ' character to determine =the begining and the > end. > > How do i go about =inserting a ' character into the database?> > "Hari Prasad" =wrote:> > Hi,> > Not quite sure what you are =trying exactly. Just try using NVARCHAR data > type and see if it helps.> > Thanks> Hari> SQL =Server MVP> > "panda" wrote in message > news:CF0A3B21-D97F-44C6-800B-88B3B54E501C@.microsoft.com...> > Hi> =>> > I have a web page where users insert some comments into a text area. users> > are able to insert any character into this area. =When they submit the> > character ' and " causes problems. How =can i allow these characters to be> > inserted into the =database( sql server 2000)?> >> > I am using Java server =page to process and insert the comments to the> > =database.> >> > thanks > > >

---

--=_NextPart_000_05F8_01C6C63E.8066A380--

Inserting special characters

Hi
I have a web page where users insert some comments into a text area. users
are able to insert any character into this area. When they submit the
character ' and " causes problems. How can i allow these characters to be
inserted into the database( sql server 2000)?
I am using Java server page to process and insert the comments to the
database.
thanksHi,
Not quite sure what you are trying exactly. Just try using NVARCHAR data
type and see if it helps.
Thanks
Hari
SQL Server MVP
"panda" <panda@.discussions.microsoft.com> wrote in message
news:CF0A3B21-D97F-44C6-800B-88B3B54E501C@.microsoft.com...
> Hi
> I have a web page where users insert some comments into a text area. users
> are able to insert any character into this area. When they submit the
> character ' and " causes problems. How can i allow these characters to be
> inserted into the database( sql server 2000)?
> I am using Java server page to process and insert the comments to the
> database.
> thanks|||This would be less of a problem if you were using stored procedures. Since
you are probably sending query strings from the web page to the SQL Server,
you may be very vulnerable to SQL Injection attacks. (Write to me off line
and I will give you more information about your vulnerability.)
For the single quote, if you must store them, have the application double
(two single quotes) them before sending to SQL Server. The double quote
shouldn't be a problem -please confirm how you are experiencing the problem.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"panda" <panda@.discussions.microsoft.com> wrote in message
news:CF0A3B21-D97F-44C6-800B-88B3B54E501C@.microsoft.com...
> Hi
> I have a web page where users insert some comments into a text area. users
> are able to insert any character into this area. When they submit the
> character ' and " causes problems. How can i allow these characters to be
> inserted into the database( sql server 2000)?
> I am using Java server page to process and insert the comments to the
> database.
> thanks|||HI,
I am trying to insert a ' charcerter. However the SQL syntax for inserting
nvarchar or strings use the ' character to determine the begining and the
end.
How do i go about inserting a ' character into the database?
"Hari Prasad" wrote:

> Hi,
> Not quite sure what you are trying exactly. Just try using NVARCHAR data
> type and see if it helps.
> Thanks
> Hari
> SQL Server MVP
> "panda" <panda@.discussions.microsoft.com> wrote in message
> news:CF0A3B21-D97F-44C6-800B-88B3B54E501C@.microsoft.com...
>
>|||Use two of them, for example:
CREATE TABLE #MyTable
( RowID int IDENTITY
, MyStringValue varchar(100)
)
INSERT INTO #MyTable VALUES ('"This isn''t so obvious, is it?", said Bill O'
'Shea to Terrance O''Donald.')
SELECT MyStringValue
FROM #MyTable
DROP TABLE #MyTable
Hope that this helps...
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"panda" <panda@.discussions.microsoft.com> wrote in message news:26E49E21-4F45-4F5E-ABD2-BD5E
5D8771C4@.microsoft.com...[vbcol=seagreen]
> HI,
>
> I am trying to insert a ' charcerter. However the SQL syntax for inserting
> nvarchar or strings use the ' character to determine the begining and the
> end.
>
> How do i go about inserting a ' character into the database?
>
> "Hari Prasad" wrote:
>

Inserting SOAP formatted message data into a SQL table -- OPENXML

I have a SOAP formatted message. I am passing this message as a string from
a
C# client to a SQL Stored Procedure.
I would like to read this SOAP formatted message and store the data in the
SOAP message in appropriate columns in a SQL table. How can I achieve this?
Can OPENXML be used for this purpose?
--
ggCould you just store in a nvarchar(max) ?
William Stacey [MVP]
"gudia" <gudia@.discussions.microsoft.com> wrote in message
news:B85E4E76-A4FB-49A7-881A-A1795CE80728@.microsoft.com...
> Is configuring IIS to be used in conjunction with SQLXML a requirement for
> the question I am asking?
> My eventual goal is to insert the data present in the SOAP message into a
> SQL table.
> Please let me know how I can achieve this goal.
> Thanks|||There are a couple of approaches. You could pass the entire SOAP XML doc to
a stored procedure and use OPENXML to shred it into tables, or you could
create an annotated XSD schema that maps the elements/attributes in your
SOAP message to the tables/column in the database and use the SQLXML Bulk
Load component.
Neither of these approaches requires a SQLXML IIS site.
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"gudia" <gudia@.discussions.microsoft.com> wrote in message
news:B85E4E76-A4FB-49A7-881A-A1795CE80728@.microsoft.com...
Is configuring IIS to be used in conjunction with SQLXML a requirement for
the question I am asking?
My eventual goal is to insert the data present in the SOAP message into a
SQL table.
Please let me know how I can achieve this goal.
Thanks

Inserting SOAP formatted message data into a SQL table -- OPEN

I have displaying my SOAP message below. Could you show me what T-SQL
statement using OPENXML or otherwise I can use to import the data in this
SOAP message into various columns in a SQL table.
My SQL table has the following columns:
LogEntry varchar(255)
Message varchar(255)
Title varchar(255)
Category varchar(50)
Priority int
EventID int
Severity varchar(255)
MachineName varchar(50)
TimeStampVal datetime
ErrorMessages varchar(255)
ExtendedProperties varchar(255)
AppDomainName varchar(50)
ProcessID int
ProcessName varchar(255)
ThreadName varchar(50)
<SOAP-ENV:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:clr="http://schemas.microsoft.com/soap/encoding/clr/1.0"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<a1:LogEntry id="ref-1"
xmlns:a1="http://schemas.microsoft.com/clr/nsassem/Microsoft.Practices.Enter
priseLibrary.Logging/Microsoft.Practices.EnterpriseLibrary.Logging%2C%20Vers
ion%3D1.0.0. 0%2C%20Culture%3Dneutral%2C%20PublicKeyT
oken%3Dnull">
<message id="ref-3">Msg successfully populated</message>
<title id="ref-4">Title1</title>
<category id="ref-5">Cat1</category>
<priority>100</priority>
<eventId>16</eventId>
<severity>Information</severity>
<machineName id="ref-6">MACH1</machineName>
<timeStamp>2005-05-20T15:24:18.4850496-05:00</timeStamp>
<errorMessages xsi:null="1"/>
<extendedProperties xsi:null="1"/>
<appDomainName id="ref-7">B1.exe</appDomainName>
<processId id="ref-8">3932</processId>
<processName id="ref-9">C:\B1\bin\Debug\B1.exe</processName>
<threadName xsi:null="1"/>
<win32ThreadId id="ref-10">244</win32ThreadId>
</a1:LogEntry>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
--
gg
"Graeme Malcolm" wrote:

> There are a couple of approaches. You could pass the entire SOAP XML doc t
o
> a stored procedure and use OPENXML to shred it into tables, or you could
> create an annotated XSD schema that maps the elements/attributes in your
> SOAP message to the tables/column in the database and use the SQLXML Bulk
> Load component.
> Neither of these approaches requires a SQLXML IIS site.
> Cheers,
> Graeme
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "gudia" <gudia@.discussions.microsoft.com> wrote in message
> news:B85E4E76-A4FB-49A7-881A-A1795CE80728@.microsoft.com...
> Is configuring IIS to be used in conjunction with SQLXML a requirement for
> the question I am asking?
> My eventual goal is to insert the data present in the SOAP message into a
> SQL table.
> Please let me know how I can achieve this goal.
> Thanks
>
>Here's one way (I wasn't sure what you want in the LogEntry column, since
that's an XML element that contains all the others - so I used the id
attribute). This example uses a temporary table with the columns you
specified and I've hardcoded the SOAP message as a variable - in reality
you'd pass it to a stored procedure as a parameter. I suggest you take some
time to examine the documentation on OPENXML in Books Online to tweak this
to do exactly what you want it to.
USE Tempdb
CREATE TABLE #TestTable
(
LogEntry varchar(255),
Message varchar(255),
Title varchar(255),
Category varchar(50),
Priority int,
EventID int,
Severity varchar(255),
MachineName varchar(50),
TimeStampVal datetime,
ErrorMessages varchar(255),
ExtendedProperties varchar(255),
AppDomainName varchar(50),
ProcessID int,
ProcessName varchar(255),
ThreadName varchar(50)
)
DECLARE @.doc nvarchar(2000)
SET @.doc = '<SOAP-ENV:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:clr="http://schemas.microsoft.com/soap/encoding/clr/1.0"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<a1:LogEntry id="ref-1"
xmlns:a1="http://schemas.microsoft.com/clr/nsassem/Microsoft.Practices.Enter
priseLibrary.Logging/Microsoft.Practices.EnterpriseLibrary.Logging%2C%20Vers
ion%3D1.0.0. 0%2C%20Culture%3Dneutral%2C%20PublicKeyT
oken%3Dnull">
<message id="ref-3">Msg successfully populated</message>
<title id="ref-4">Title1</title>
<category id="ref-5">Cat1</category>
<priority>100</priority>
<eventId>16</eventId>
<severity>Information</severity>
<machineName id="ref-6">MACH1</machineName>
<timeStamp>2005-05-20T15:24:18.4850496-05:00</timeStamp>
<errorMessages xsi:null="1"/>
<extendedProperties xsi:null="1"/>
<appDomainName id="ref-7">B1.exe</appDomainName>
<processId id="ref-8">3932</processId>
<processName id="ref-9">C:\B1\bin\Debug\B1.exe</processName>
<threadName xsi:null="1"/>
<win32ThreadId id="ref-10">244</win32ThreadId>
</a1:LogEntry>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
'
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.doc, '<ns
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:a1="http://schemas.microsoft.com/clr/nsassem/Microsoft.Practices.Enter
priseLibrary.Logging/Microsoft.Practices.EnterpriseLibrary.Logging%2C%20Vers
ion%3D1.0.0. 0%2C%20Culture%3Dneutral%2C%20PublicKeyT
oken%3Dnull"/>'
INSERT #TestTable
SELECT * FROM OPENXML(@.hDoc, 'SOAP-ENV:Envelope/SOAP-ENV:Body/a1:LogEntry',
2)
WITH
(
LogEntry varchar(255) '@.id',
message varchar(255),
title varchar(255),
category varchar(50),
priority int,
eventId int,
severity varchar(255),
machineName varchar(50),
timeStampVal datetime,
errorMessages varchar(255),
extendedProperties varchar(255),
appDomainName varchar(50),
processId int,
processName varchar(255),
threadName varchar(50)
)
SELECT * FROM #TestTable
DROP TABLE #TestTable
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"gudia" <gudia@.discussions.microsoft.com> wrote in message
news:B85C3731-29C4-4503-9A4A-237B7F83A5B7@.microsoft.com...
I have displaying my SOAP message below. Could you show me what T-SQL
statement using OPENXML or otherwise I can use to import the data in this
SOAP message into various columns in a SQL table.
My SQL table has the following columns:
LogEntry varchar(255)
Message varchar(255)
Title varchar(255)
Category varchar(50)
Priority int
EventID int
Severity varchar(255)
MachineName varchar(50)
TimeStampVal datetime
ErrorMessages varchar(255)
ExtendedProperties varchar(255)
AppDomainName varchar(50)
ProcessID int
ProcessName varchar(255)
ThreadName varchar(50)
<SOAP-ENV:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:clr="http://schemas.microsoft.com/soap/encoding/clr/1.0"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<a1:LogEntry id="ref-1"
xmlns:a1="http://schemas.microsoft.com/clr/nsassem/Microsoft.Practices.Enter
priseLibrary.Logging/Microsoft.Practices.EnterpriseLibrary.Logging%2C%20Vers
ion%3D1.0.0. 0%2C%20Culture%3Dneutral%2C%20PublicKeyT
oken%3Dnull">
<message id="ref-3">Msg successfully populated</message>
<title id="ref-4">Title1</title>
<category id="ref-5">Cat1</category>
<priority>100</priority>
<eventId>16</eventId>
<severity>Information</severity>
<machineName id="ref-6">MACH1</machineName>
<timeStamp>2005-05-20T15:24:18.4850496-05:00</timeStamp>
<errorMessages xsi:null="1"/>
<extendedProperties xsi:null="1"/>
<appDomainName id="ref-7">B1.exe</appDomainName>
<processId id="ref-8">3932</processId>
<processName id="ref-9">C:\B1\bin\Debug\B1.exe</processName>
<threadName xsi:null="1"/>
<win32ThreadId id="ref-10">244</win32ThreadId>
</a1:LogEntry>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
--
gg
"Graeme Malcolm" wrote:

> There are a couple of approaches. You could pass the entire SOAP XML doc
> to
> a stored procedure and use OPENXML to shred it into tables, or you could
> create an annotated XSD schema that maps the elements/attributes in your
> SOAP message to the tables/column in the database and use the SQLXML Bulk
> Load component.
> Neither of these approaches requires a SQLXML IIS site.
> Cheers,
> Graeme
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "gudia" <gudia@.discussions.microsoft.com> wrote in message
> news:B85E4E76-A4FB-49A7-881A-A1795CE80728@.microsoft.com...
> Is configuring IIS to be used in conjunction with SQLXML a requirement for
> the question I am asking?
> My eventual goal is to insert the data present in the SOAP message into a
> SQL table.
> Please let me know how I can achieve this goal.
> Thanks
>
>|||Thanks so much Graeme. That worked.
Appreciate your help.
"Graeme Malcolm" wrote:

> Here's one way (I wasn't sure what you want in the LogEntry column, since
> that's an XML element that contains all the others - so I used the id
> attribute). This example uses a temporary table with the columns you
> specified and I've hardcoded the SOAP message as a variable - in reality
> you'd pass it to a stored procedure as a parameter. I suggest you take so
me
> time to examine the documentation on OPENXML in Books Online to tweak this
> to do exactly what you want it to.
> USE Tempdb
> CREATE TABLE #TestTable
> (
> LogEntry varchar(255),
> Message varchar(255),
> Title varchar(255),
> Category varchar(50),
> Priority int,
> EventID int,
> Severity varchar(255),
> MachineName varchar(50),
> TimeStampVal datetime,
> ErrorMessages varchar(255),
> ExtendedProperties varchar(255),
> AppDomainName varchar(50),
> ProcessID int,
> ProcessName varchar(255),
> ThreadName varchar(50)
> )
> DECLARE @.doc nvarchar(2000)
> SET @.doc = '<SOAP-ENV:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
> xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:clr="http://schemas.microsoft.com/soap/encoding/clr/1.0"
> SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
> <SOAP-ENV:Body>
> <a1:LogEntry id="ref-1"
> xmlns:a1="http://schemas.microsoft.com/clr/nsassem/Microsoft.Practices.Ent
erpriseLibrary.Logging/Microsoft.Practices.EnterpriseLibrary.Logging%2C%20Ve
rsion%3D1.0.0. 0%2C%20Culture%3Dneutral%2C%20PublicKeyT
oken%3Dnull">
> <message id="ref-3">Msg successfully populated</message>
> <title id="ref-4">Title1</title>
> <category id="ref-5">Cat1</category>
> <priority>100</priority>
> <eventId>16</eventId>
> <severity>Information</severity>
> <machineName id="ref-6">MACH1</machineName>
> <timeStamp>2005-05-20T15:24:18.4850496-05:00</timeStamp>
> <errorMessages xsi:null="1"/>
> <extendedProperties xsi:null="1"/>
> <appDomainName id="ref-7">B1.exe</appDomainName>
> <processId id="ref-8">3932</processId>
> <processName id="ref-9">C:\B1\bin\Debug\B1.exe</processName>
> <threadName xsi:null="1"/>
> <win32ThreadId id="ref-10">244</win32ThreadId>
> </a1:LogEntry>
> </SOAP-ENV:Body>
> </SOAP-ENV:Envelope>
> '
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.doc, '<ns
> xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:a1="http://schemas.microsoft.com/clr/nsassem/Microsoft.Practices.Ent
erpriseLibrary.Logging/Microsoft.Practices.EnterpriseLibrary.Logging%2C%20Ve
rsion%3D1.0.0. 0%2C%20Culture%3Dneutral%2C%20PublicKeyT
oken%3Dnull"/>'
> INSERT #TestTable
> SELECT * FROM OPENXML(@.hDoc, 'SOAP-ENV:Envelope/SOAP-ENV:Body/a1:LogEntry'
,
> 2)
> WITH
> (
> LogEntry varchar(255) '@.id',
> message varchar(255),
> title varchar(255),
> category varchar(50),
> priority int,
> eventId int,
> severity varchar(255),
> machineName varchar(50),
> timeStampVal datetime,
> errorMessages varchar(255),
> extendedProperties varchar(255),
> appDomainName varchar(50),
> processId int,
> processName varchar(255),
> threadName varchar(50)
> )
> SELECT * FROM #TestTable
> DROP TABLE #TestTable
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "gudia" <gudia@.discussions.microsoft.com> wrote in message
> news:B85C3731-29C4-4503-9A4A-237B7F83A5B7@.microsoft.com...
> I have displaying my SOAP message below. Could you show me what T-SQL
> statement using OPENXML or otherwise I can use to import the data in this
> SOAP message into various columns in a SQL table.
> My SQL table has the following columns:
> LogEntry varchar(255)
> Message varchar(255)
> Title varchar(255)
> Category varchar(50)
> Priority int
> EventID int
> Severity varchar(255)
> MachineName varchar(50)
> TimeStampVal datetime
> ErrorMessages varchar(255)
> ExtendedProperties varchar(255)
> AppDomainName varchar(50)
> ProcessID int
> ProcessName varchar(255)
> ThreadName varchar(50)
> <SOAP-ENV:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
> xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:clr="http://schemas.microsoft.com/soap/encoding/clr/1.0"
> SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
> <SOAP-ENV:Body>
> <a1:LogEntry id="ref-1"
> xmlns:a1="http://schemas.microsoft.com/clr/nsassem/Microsoft.Practices.Ent
erpriseLibrary.Logging/Microsoft.Practices.EnterpriseLibrary.Logging%2C%20Ve
rsion%3D1.0.0. 0%2C%20Culture%3Dneutral%2C%20PublicKeyT
oken%3Dnull">
> <message id="ref-3">Msg successfully populated</message>
> <title id="ref-4">Title1</title>
> <category id="ref-5">Cat1</category>
> <priority>100</priority>
> <eventId>16</eventId>
> <severity>Information</severity>
> <machineName id="ref-6">MACH1</machineName>
> <timeStamp>2005-05-20T15:24:18.4850496-05:00</timeStamp>
> <errorMessages xsi:null="1"/>
> <extendedProperties xsi:null="1"/>
> <appDomainName id="ref-7">B1.exe</appDomainName>
> <processId id="ref-8">3932</processId>
> <processName id="ref-9">C:\B1\bin\Debug\B1.exe</processName>
> <threadName xsi:null="1"/>
> <win32ThreadId id="ref-10">244</win32ThreadId>
> </a1:LogEntry>
> </SOAP-ENV:Body>
> </SOAP-ENV:Envelope>
> --
> gg
>
> "Graeme Malcolm" wrote:
>
>
>

inserting single precision data into sql server float column

Hi,
iam using the bcp api to load data into sql server. The data to be loaded
is single precision and hence my bcp_bind type is SQLFLT4. The column in my
sql server table is a FLOAT(which is of course double precision).
If i try to insert say a value 73.22 it gets inserted as 73.22000122070313.
I mean the documentation says that implicit conversion for these types are
allowed. So iam not sure why this happens.
Appreciate any inputs.
VivekHi Vivek,
Thats the way float works:
"Approximate-number data types for use with floating point numeric
data. Floating point data is approximate; therefore, not all values in
the data type range can be represented exactly. "
DECLARE @.SOMEValue Float(2)
SEt @.SomeValue = 1.100001
SELECT @.SOMEValue
For more precicion you have to use another database like decimal.
HTH, Jens Suessmeyer.|||Because a float in SQL Server is an *approximate* floating point
representation, essentially meaning if you round it to the appropriate
number of significant digits then you'll get the number you're after but
it's only stored as accurately as the binary numbering system can manage
(defined by IEEE 754). The same would happen if you used real rather
than float. I think what you're after is an *exact* floating point
representation, which corresponds to the numeric (or decimal) data types
in SQL Server (i.e. fixed precision & scale).
See Using decimal, float and real data
<http://msdn.microsoft.com/library/e...con_03_6mht.asp> in
SQL Books Online.
*mike hodgson*
http://sqlnerd.blogspot.com
Vivek wrote:

>Hi,
> iam using the bcp api to load data into sql server. The data to be loaded
>is single precision and hence my bcp_bind type is SQLFLT4. The column in my
>sql server table is a FLOAT(which is of course double precision).
>If i try to insert say a value 73.22 it gets inserted as 73.22000122070313.
>I mean the documentation says that implicit conversion for these types are
>allowed. So iam not sure why this happens.
>Appreciate any inputs.
>Vivek
>|||I think my query was not stated clearly. If i load the same value into a
REAL column it shows exactly what i inserted. (73.22)
Similarily if i store that value in a double precision program variable and
load into a FLOAT column it shows exactly what i stored.
The problem is when the value is in a single precision program variable and
i load into a FLOAT
"Mike Hodgson" wrote:

> Because a float in SQL Server is an *approximate* floating point
> representation, essentially meaning if you round it to the appropriate
> number of significant digits then you'll get the number you're after but
> it's only stored as accurately as the binary numbering system can manage
> (defined by IEEE 754). The same would happen if you used real rather
> than float. I think what you're after is an *exact* floating point
> representation, which corresponds to the numeric (or decimal) data types
> in SQL Server (i.e. fixed precision & scale).
> See Using decimal, float and real data
> <http://msdn.microsoft.com/library/e...con_03_6mht.asp> in
> SQL Books Online.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Vivek wrote:
>
>|||> I think my query was not stated clearly. If i load the same value into a
> REAL column it shows exactly what i inserted. (73.22)
We understood the question. You do not understand the issue. Did you read
BOL regarding their definition and usage (Accessing and Changing Relational
Data / Using decimal, float, and real Data)? If so, then contine with
http://docs.sun.com/source/806-3568/ncg_goldberg.html. Do not confuse
representation of a value with the actual value. What you claim to see can
also be an artifact of whatever technique you use to "see" the value after
storage in the database. Below is a script that demonstrates the problem
more clearly.
set nocount on
declare @.test1 real, @.test2 float, @.test3 float(2)
set @.test1 = 73.22
set @.test2 = 73.22
set @.test3 = 73.22
select @.test1, @.test2, @.test3
select cast(@.test1 as varbinary(8)), cast(@.test2 as varbinary(8)),
cast(@.test3 as varbinary(8))
print @.test1
print @.test2
print @.test3|||That's what i thought initially. (I use Query Analyzer btw) After inserting
the same value (from a double precision and single precision variable
respectively) into a FLOAT column, if i do a 'select * from tfloat' i get
this output:
73.22
73.22000122070313
Would you say that the actual values of both rows are the same regardless of
what i see above?
The binary values are:
0x40524E147AE147AE
0x40524E1480000000
"Scott Morris" wrote:

> We understood the question. You do not understand the issue. Did you re
ad
> BOL regarding their definition and usage (Accessing and Changing Relationa
l
> Data / Using decimal, float, and real Data)? If so, then contine with
> http://docs.sun.com/source/806-3568/ncg_goldberg.html. Do not confuse
> representation of a value with the actual value. What you claim to see ca
n
> also be an artifact of whatever technique you use to "see" the value after
> storage in the database. Below is a script that demonstrates the problem
> more clearly.
> set nocount on
> declare @.test1 real, @.test2 float, @.test3 float(2)
> set @.test1 = 73.22
> set @.test2 = 73.22
> set @.test3 = 73.22
> select @.test1, @.test2, @.test3
> select cast(@.test1 as varbinary(8)), cast(@.test2 as varbinary(8)),
> cast(@.test3 as varbinary(8))
> print @.test1
> print @.test2
> print @.test3
>
>|||On Mon, 23 Jan 2006 03:39:03 -0800, Vivek wrote:

>That's what i thought initially. (I use Query Analyzer btw) After inserting
>the same value (from a double precision and single precision variable
>respectively) into a FLOAT column, if i do a 'select * from tfloat' i get
>this output:
>73.22
>73.22000122070313
>Would you say that the actual values of both rows are the same regardless o
f
>what i see above?
>The binary values are:
>0x40524E147AE147AE
>0x40524E1480000000
Hi Vivek,
These binary values explainexactly what's going on.
The closest representation in a double precision representation is,
obviosuly, 0x40524E147AE147AE. When you store that in a single precision
variable or column, it has to be rounded to the closest that can be
represented in the 24 bits set aside for single precision, which is
apparently 0x40524E148. If you then store this in a double precision
column, the extra bits are added again - but of course as 0 bits, since
SQL Server has no memory of the bits that were prreviously lost. And so
it ends up as 0x40524E1480000000.
Hugo Kornelis, SQL Server MVP|||Thanks Hugo. That sounds reasonable. So do i just avoid these kind of
insertions and stick to single to single and double to double precision
insertions?
"Hugo Kornelis" wrote:

> On Mon, 23 Jan 2006 03:39:03 -0800, Vivek wrote:
>
> Hi Vivek,
> These binary values explainexactly what's going on.
> The closest representation in a double precision representation is,
> obviosuly, 0x40524E147AE147AE. When you store that in a single precision
> variable or column, it has to be rounded to the closest that can be
> represented in the 24 bits set aside for single precision, which is
> apparently 0x40524E148. If you then store this in a double precision
> column, the extra bits are added again - but of course as 0 bits, since
> SQL Server has no memory of the bits that were prreviously lost. And so
> it ends up as 0x40524E1480000000.
> --
> Hugo Kornelis, SQL Server MVP
>|||On Mon, 23 Jan 2006 20:35:02 -0800, Vivek wrote:

>Thanks Hugo. That sounds reasonable. So do i just avoid these kind of
>insertions and stick to single to single and double to double precision
>insertions?
Hi Vivek,
I don't know what the requirements of your applications are. But as a
rule of thumb, I'd recommend to avoid conversions as much as possible,
stick to the same precision. Once you've lost precision, there's no way
to get it back. But OTOH, storing data at more than required precision
is just a waste of space.
Find the precision you need, then design your DB and application around
that.
Hugo Kornelis, SQL Server MVP|||Thank you guys.
"Hugo Kornelis" wrote:

> On Mon, 23 Jan 2006 20:35:02 -0800, Vivek wrote:
>
> Hi Vivek,
> I don't know what the requirements of your applications are. But as a
> rule of thumb, I'd recommend to avoid conversions as much as possible,
> stick to the same precision. Once you've lost precision, there's no way
> to get it back. But OTOH, storing data at more than required precision
> is just a waste of space.
> Find the precision you need, then design your DB and application around
> that.
> --
> Hugo Kornelis, SQL Server MVP
>