Database corrupted: update

Showing posts with label update. Show all posts

Thursday, March 29, 2012

Database Compare / Update

Ok, I have 2 Tables,
table1
ID, Count
table2
ID, Count,....
I need to go through table1, and make a change to the table2.count, if the table1.count is != table2.count
Are Cursors the only way to accomplish this?
Thanks alot guys/gals...Q1 Are Cursors the only way to accomplish this?

A1 No.

If the requirement is simply to have a similar count record maintained in a second table for a given ID; a common approach would probably involve an Update trigger (and possibly an Insert trigger and a Delete trigger as well), depending on the exact business rules.|||table 2 is actually a live table with data maintained thru regular channels. Once a day (at night) we get data from an external source with changes. One of these changes is an updated count (count of pictures on file) for each ID. (not all records have changes).

So I need to scan the live table (table 2) for matching records in the changes table (which is imported from a text file into a table for other processing), and update matching IDs with count updates.

I am probably lousy at describing what I am after.. but thanks for the help...|||RE: So I need to scan the live table (table 2) for matching records in the changes table (which is imported from a text file into a table for other processing), and update matching IDs with count updates.

One or more appropriate trigger(s) on the 'changes' table (or alternatively, on some subsequent processing work table) may well be able to accomplish the required updates. (Even using bcp or Bulk Insert to import the data would not perclude trigger based implementations, as both support 'FIRE TRIGGERS' type options.)|||You can use stamp.
put a stamp field that always be updated when the data updated.|||RE:
One or more appropriate trigger(s) on the 'changes' table (or alternatively, on some subsequent processing work table) may well be able to accomplish the required updates. (Even using bcp or Bulk Insert to import the data would not perclude trigger based implementations, as both support 'FIRE TRIGGERS' type options.)

See the dba_tr_i_Changes_Source for a trigger stored procedure example, with supporting Live_Target and Changes_Source table ddl statements.

Execute the: DEMO EXAMPLES separately to most clearly see the statement results.

The purpose is to Illustrate a trigger implementation on a Changes_Source table (which is populated from an external source).

As the triggered Changes_Source is populated over time with additional records, the related Live_Target table rows will be updated as appropriate. Additional code could handle 'new' changes_Source..PictureGroupID values (those not already present in table Live_Target)|||For bulk loads, one may use something like the following in the body of the trigger:

UPDATE Target
SET
Target.PictureCount = Source.PictureCount,
Target.LastUpDated = Source.ChangeDateTime
FROM
tempdb.dbo.Live_Target Target
INNER JOIN
tempdb.dbo.Changes_Source Source
ON
Target.PictureGroupID = Source.PictureGroupID
Where
Source.ChangeDateTime >= Target.LastUpDated

Then, as the triggered Changes_Source is populated by batch loads (using bcp in or bulk insert with the fire triggers options) the related Live_Target table rows will be updated as appropriate. Here too, additional code could handle 'new' Changes_Source..PictureGroupID values (those not already present in table Live_Target).

Wednesday, March 7, 2012

Database "staging" area

I currently have the capability to serve up my Product Knowledge form to my clients so they can update their information; but I do not want them to be committing that information to my production db until it has been confirmed for release by my content management team. Would the following idea I have present any problems?

I want to create two pages (duplicates of the page I currently use):

1) Client. Information is posted to a "staging" database.
2) Internal. Content team opens this page and it loads the data from the staging database with two buttons on the page. One to update the staging database, and the other to push the data to my production db.

Any problems with this, or is there a better way to do it? I feel that I should probably create a second app to achieve all this instead of putting it in my production app.

Please advise.It might be worth considering createing a "Pending" column in your existing database rather than creating a shadow database. If you need to handle updates of exisiting information, you can create some sort of sequence field (perhaps just a datestamp) and then use views to get the current record, or the current APPROVED record from the production database.|||One of the reasons I considered a shadow database was because of the size of the table and data entry page; plus it would be easier for me to replicate the db and app. It may seem like the lazy approach, but I have some serious time constraints. I was thinking I could just open the shadow db for editing by the client in one page, and the content management team could open another replicated page that has an extra button, and that button would push the data to the production db.

The aspx and code behind pages total 11,000 lines of code and the db has a couple hundred columns in it. And before I get hammered on normalization... "My boss made me do it!!!"|||::One of the reasons I considered a shadow database was because of the size of the table
::and data entry page

Both are ntot relevant, sadly. Thesize of the tabele should not be an isue. Using a staging databaese creates all kinds of problems though.

::plus it would be easier for me to replicate the db and app

Hm. So the app would not know anything about whether staging or life? And you would open this to all kinds of errors by the end user? Hardly better.

::The aspx and code behind pages total 11,000 lines of code and the db has a couple
::hundred columns in it. And before I get hammered on normalization... "My boss made me
::do it!!!"

What an idiotic approach. Sorry. As you said :-)

11.000 lines of code - I just hope that most is in the HTML part. Tell your boss now it is time top pay for a broken design.|||So whatis the best way to do this? I don't want our clients accessing or updating our production database until the data has been reviewed and approved.

As for the db size and line code, Ihave proposed a normalization plan. This is the project that I inherited a few months back that's been through three sets of external development teams before coming in house. I have had to just add to what they put together because internally it was not viewed as expedient to do a major rewrite. I explained the potential problems and it was taken note of.

I guess the fortunate part of this (if there could possibly be a bright side) is that the form in question will only be used a few times a day by 1 or 2 users, and I am doubtful that most of our clients are disciplined enough to update their information consistently (which might be once every 2-3 months.)

When it was recommended that I use a pending column, I assumed that I would have to duplicate each colum in the table. I felt that taking the time to do that would be worth the extra time investment of normalizing the whole thing, that does not address the current problem I know; but it got me to thinking that if I am going to go back and normalize the db, then I am digging myself into a deeper hole by creating more dependencies.

When I got my hands on this thing and saw it, I was not a happy camper. But I think the mind-set here was, "it works right?" And i'm thinking, "yeah, for the moment."

I appreciate the responses and guidance.

Thanks.|||VERY good question. I would propably go with another table in the same database - unless you want to add versioning to the main table echanism, which may be an overhead.

But another database just looks a little too much over the top.

Definitly a great candidate for a lot of future functionality and normalisation, though.

Dataadpater does not update database

I hope someone can help me.

My dataadapter will not update my database. Everything seems to work, and I get no errors of any kind. But my database is not being touched. I ran a sql profiler on it and there is no trace of my update. I can retrieve data just fine, but I cannot get it to update with my onclick event. Please just point me in the right direction, or any direction would also be cool.

Thank you.

Here is my code:

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

publicpartialclassWebAdmin_EditNews2 : System.Web.UI.Page

{

publicSqlConnection conn =newSqlConnection("Data Source=vnadbms01;initial catalog=vnawebdev; " +

"integrated security=true;persist security info=True;");

publicSqlDataAdapter da =newSqlDataAdapter();

publicDataSet ds =newDataSet();

publicDataTable dt =newDataTable();

protectedvoid Page_Load(object sender,EventArgs e)

{

if (!IsPostBack)

{

string MsgIDString = Session["MsgID"].ToString();

//Select Parameters:

SqlParameter SQLPMsgID =newSqlParameter("@.Msg_ID",SqlDbType.Int);

SQLPMsgID.Direction =ParameterDirection.Input;

SQLPMsgID.Value = MsgIDString;

da.SelectCommand =newSqlCommand();

//Set Select Commands

da.SelectCommand.Connection = conn;

da.SelectCommand.CommandText ="Messages_select";

da.SelectCommand.CommandType =CommandType.StoredProcedure;

da.SelectCommand.Parameters.Add(SQLPMsgID);

da.Fill(ds,"VNA_News");

FormView1.DataSource = ds;

FormView1.DataBind();

}

protectedvoid EditButton_Click(object sender,EventArgs e)

{

//Update parameters:

Label sqlprmMsgID = (Label)FormView1.FindControl("Msglbl");

SqlParameter SQLPMsgID2 =newSqlParameter("@.Msg_ID",SqlDbType.Int);

SQLPMsgID2.Direction =ParameterDirection.Input;

SQLPMsgID2.Value = sqlprmMsgID.Text;

TextBox sqlprmUsrID = (TextBox)FormView1.FindControl("User_IDTextBox");

SqlParameter SQLPDeptID =newSqlParameter("@.User_ID",SqlDbType.Int);

SQLPDeptID.Direction =ParameterDirection.Input;

SQLPDeptID.Value = sqlprmUsrID.Text;

TextBox sqlprmDept= (TextBox)FormView1.FindControl("Dept_IDTextBox");

SqlParameter SQLPUsrID =newSqlParameter("@.Dept_ID",SqlDbType.Int);

SQLPUsrID.Direction =ParameterDirection.Input;

SQLPUsrID.Value = sqlprmDept.Text;

TextBox sqlprmBody = (TextBox)FormView1.FindControl("Msg_bodyTextBox");

SqlParameter SQLPMsgBody =newSqlParameter("@.Msg_body",SqlDbType.Text);

SQLPMsgBody.Direction =ParameterDirection.Input;

SQLPMsgBody.Value = sqlprmBody.Text;

TextBox sqlprmMsgTitle = (TextBox)FormView1.FindControl("Msg_TitleTextBox");

SqlParameter SQLPMsgTitle =newSqlParameter("@.Msg_Title",SqlDbType.VarChar);

SQLPMsgTitle.Direction =ParameterDirection.Input;

SQLPMsgTitle.Value = sqlprmMsgTitle.Text;

TextBox sqlprmsts = (TextBox)FormView1.FindControl("stsTextBox");

SqlParameter SQLPMsgsts =newSqlParameter("@.sts",SqlDbType.Int);

SQLPMsgsts.Direction =ParameterDirection.Input;

SQLPMsgsts.Value = sqlprmsts.Text;

TextBox sqlprmexpdat = (TextBox)FormView1.FindControl("Exp_DatTextBox");

SqlParameter SQLPExpDat =newSqlParameter("@.Exp_Dat",SqlDbType.DateTime);

SQLPExpDat.Direction =ParameterDirection.Input;

SQLPExpDat.Value = sqlprmexpdat.Text;

//Set Update Commands

SqlConnection conn2 =newSqlConnection("Data Source=vnadbms01;initial catalog=vnawebdev; " +

"integrated security=true;persist security info=True;");

SqlDataAdapter dau =newSqlDataAdapter();

dau.UpdateCommand =newSqlCommand();

dau.UpdateCommand.Connection = conn2;

dau.UpdateCommand.CommandText ="Messages_Update";

dau.UpdateCommand.CommandType =CommandType.StoredProcedure;

dau.UpdateCommand.Parameters.Add(SQLPMsgID2);

dau.UpdateCommand.Parameters.Add(SQLPDeptID);

dau.UpdateCommand.Parameters.Add(SQLPMsgTitle);

dau.UpdateCommand.Parameters.Add(SQLPMsgBody);

dau.UpdateCommand.Parameters.Add(SQLPMsgsts);

dau.UpdateCommand.Parameters.Add(SQLPExpDat);

dau.UpdateCommand.Parameters.Add(SQLPUsrID);

dau.TableMappings.Add("VNA_News","VNA_News");

dau.Update(ds,"VNA_News");

I recommend you not to use SqlDataAdapter.Update to do data update to database.It is better to use SqlCommand.ExecuteNonQuery or SqlCommand.ExecuteScaler.If you want to do this,try to see this.

http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/UpdateDataFromDB.aspx

http://p2p.wrox.com/topic.asp?TOPIC_ID=7537

Wish this help you.

|||

Thank you for the info. After doing some reading I see why the dataadapter in a webform can be problematic. It sounds like it works great for windows forms. I am moving ahead with the sqlcommand.Executenonquery. That should hopefully do the trick. If not, I will try another method.

I spent way too much time on this. I should have been open to other methods for getting the job done. Reading also helps.

dataadapter.Update() OR ExecuteNonQuery()

Hy, again! I am at the begining of an application. I have some modules that insert, delete and update only one row at one table. My question is should I use dataadapter.Update() or ExecuteNonQuery(). I prefer ExecuteNonQuery because I want to build a class : DataLayer to implement my own InsertProcedure(), UpdateProcedure(),DeleteProcedure(). I want speed in my application, so which is the best: dataadapter.Update() OR ExecuteNonQuery(). Thank you!

If you are only intending on working with one row at a time (presumably using the primary key for the data you are working with) then it would be more efficient to use the ExecuteNonQuery method of the command object. The Update method of the DataAdapter object will attempt to update all changes to rows within a DataSet/DataTable.

HTH

|||You said: "Update method of the DataAdapter object will attempt to update ALL changes to rows within a DataSet/DataTable", even if I write: dataadapter.UpdateCommand="update TableName set field1 with ? for .... "?|||

If you specify the DataAdapter's UpdateCommand to update only one row then that will be similar to the ExecuteNonQuery method. I assumed by DataAdapter.Update that you were going to pass it a DataSet/DataTable so that all changed rows would be persisted to the Database.

To be honest though, the DataAdapter's UpdateCommand is a command object so therefore, to update a row you would have configured a DataAdapter and then configured a Command object when you could simply have configured a Command object only. However, if you already had the DataAdapter available to you then this wouldn't be so much of an issue. It really depends on the type of aproach you are taking, especially when considering a Data Access Layer.

HTH

|||Yes, you are right. With dataadapter the work is double. Thank you.

DataAdapter.Update updates zero records

I am having trouble getting an Update call to actually update records. The select statement is a stored procedure which is uses inner joins to link to property tables. The update, insert, and delete commands were generated by Visual Studio and only affect the primary table. So, to provide a simple example, I have a customer table with UID, Name, and LanguageID and a seperate table with LanguageID and LanguageDescription. The stored procedure can be used to populate a datagrid with all results (this works). The stored procedure also populates an edit page with one UID (this works). After the edit is completed, I attempt to update the dataset, which only has one row at this time, which shows that it has been modified. The Update modifies 0 rows and raises no exceptions. Is this because the update, insert, and delete statements do not match up one-to-one with the dataset? If so, what are my choices?I found the source of the problem. It was a result of a conflict elsewhere. So, if anyone else is trying to use a combination of stored procedures and normal sql queries, it will work fine, even if the statements do not match up perfectly with the dataset. The queries simply need to meet any requirements of the database.

DataAdapter.Update Method Question.

Hi,

I am trying to use DataAdapter.Update to save a file stream into SQl Express.

I have a dialog box that lets user select the file:

openFileDialog1.ShowDialog();

I want to put

openFileDialog1.OpenFile();

Into

this.documentTableAdapter.Update(this.docControllerAlphaDBDataSet.Document.DocumentColumn);

I am thinking that it might just be some syntax issue, but I looked online, and didn't find much answers.

Thanks,
Ke

Take a look on the links I posted here:

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

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Saturday, February 25, 2012

DataAdapter Update Problems

Hi, I am populating a datagrid with data from one sql table, I add additional columns and then wish to write it back out to another table. I have written the following code to acheive this, however it keeps throwing the follwing exception - "Update requites a valid UpdateComman when passed a DataRow collection with modified rows"

I've looked and I can see what the problem is, can anybody help please?

Private Sub FillData()'SQL connectionMe.sqlconnection =New System.Data.SqlClient.SqlConnectionMe.sqlconnection.ConnectionString = ConfigurationSettings.AppSettings("CuroConnection")Dim sqlAs String If Request("action") ="gen"Then sql ="select item from tblPickList where GroupName ='xxx'AND Category = '" & Category2 & "' ORDER BY item ASC" ElseIf Request("action") = "fav" Then sql = "select identifiertext as item from tblfavourites where HRID =" & Session("hrid") & " and type = 3 ORDER BY identifiertext ASC" ElseIf Request("action") = "spec" Then sql = "select item from tblPickList where GroupName ='" & GroupID & "' AND Category = '" & Category2 & "' ORDER BY item ASC"End If ds =New DataSet da =New SqlClient.SqlDataAdapter(sql, sqlconnection)Try sqlconnection.Open() da.Fill(ds,"tblPickListTemp") sqlconnection.Close()Catch exAs Exception sqlconnection.Close()End Try ds.Tables(0).Columns.Add("HRID") ds.Tables(0).Columns.Add("Flag")'may possibly add a favourites column here at a later date to ensure no duplicationDim drAs DataRowFor Each drIn ds.Tables("tblPickListTemp").Rows dr("HRID") = Session("hrid") dr("Flag") = 0Next DgrdFavourites.DataSource = ds DgrdFavourites.DataBind()End Sub Public Sub CreateTempRecords()'SQL connectionMe.sqlconnection =New System.Data.SqlClient.SqlConnectionMe.sqlcommand =New System.Data.SqlClient.SqlCommandMe.da =New SqlClient.SqlDataAdapterMe.sqlconnection.ConnectionString = ConfigurationSettings.AppSettings("CuroConnection")Dim sqlAs String sql ="Insert into tblPickListTemp (Item, HRID, Flag) values (@.Item, @.HRID, @.Flag)" sqlcommand.CommandText = sql sqlcommand.Connection = sqlconnection sqlcommand.Parameters.Add("@.Item","Item") sqlcommand.Parameters.Add("@.HRID","HRID") sqlcommand.Parameters.Add("@.Flag","Flag") da.InsertCommand = sqlcommand da.Update(ds.Tables(0))End Sub

Hello Naimh,

Your program retrieves records from the database, you modify them and try to update the records in the database.

In that case you need to add an UpdateCommand to your SqlDataAdapter.

The Update statement will than handle new rows and modified rows.

Jeroen Molenaar.

|||I thought that was what I was doing with the da.InsertCommand on the 2nd to last line.I'm not sure I've really got to grips with the dataadapter syntax, could you show me please?|||

The InsertCommand you use is correct. But you are not adding new rows to the table, so you need also the UpdateCommand.

sql = "Update tblPickList Set HRID = @.HRID, Flag = @.Flag where Item = @.Item"

// Add the parameters here ...

sqlcommand.CommandText = sql

da.UpdateCommand = sqlcommand

// etc.

Hope you get it working now.

Jeroen Molenaar.

|||

jeroenm:
The InsertCommand you use is correct. But you are not adding new rows to the table, so you need also the UpdateCommand.

Okay really confused now, as that is exactly what I want to do, I populate the dataset from one table, manipulate it in the dataset and then Insert entirely new row's into a completely different table, erk! I shall give your suggestion a try but I have to fess up to being none the wiser.|||

jeroenm:
sql?= "Update tblPickListTemp Set HRID = @.HRID, Flag = @.Flag where Item = @.Item"

See now this is never going to work for me, tblPickListTemp ( the table I want to insert rows into) at the start of this process is empty, I cannot update and set fields to new values when no records currently exist.Ah well back to the drawing board, thanks anyway.|||

Do you have an AcceptChanges statement somewhere before the data is saved to the database?

When you modify the dataset after AcceptChanges, the tableadapter thinks it needs an Update statement in stead of an Insert statement.

Jeroen Molenaar.

|||No I am afraid I don't, all the code is in the snippet above. It's most confusing. I have also ensured that the primary key is set in the recipient table, which is another common problem which throws this error. I am at a real loss and almost at the point of scrapping the whole thing and taking a different approach, it's just that if I could have got this to work it would have been less trips to the Db and a lot less spaghetti code.|||

You can add some ASP.NET code. Perhaps this gives another clue.

|||

<form id="Form1" method="post" runat="server"><TABLE id="Table2" cellSpacing="0" cellPadding="0" width="280" align="center" border="0"><TR height="24"><TD class="fntmed" style="WIDTH: 280px" background="../images/header.jpg">         PickList</TD></TR><TR><TD class="fntnorm" style="WIDTH: 280px; HEIGHT: 36px"><IMG height="6" src="../images/6pixspacer.gif"><br><asp:image id="imgLine" runat="server" Width="260px" ImageUrl="../Images/line.jpg"></asp:image></TD></TR><TR><TD><div style="OVERFLOW: auto; WIDTH: 270px; POSITION: static; HEIGHT: 190px"><asp:DataGrid id="DgrdFavourites" autogeneratecolumns="False" BackColor="#ffffff" showheader="False"OnItemCommand="DgrdFavourites_ItemCommand" runat="server" Width="256px" Height="25px" BorderWidth="0"CssClass="fntsmall" AlternatingItemStyle-CssClass="fntsmall"><AlternatingItemStyle BackColor="#e2eaee" /><Columns><asp:BoundColumn DataField="Item" /><asp:templatecolumn ItemStyle-HorizontalAlign="Right"><ItemTemplate><asp:button CssClass="buttonlike" Text="Select" commandname="Select" runat="server" ID="btnSelect" /></ItemTemplate></asp:templatecolumn><asp:templatecolumn ItemStyle-HorizontalAlign="Right"><ItemTemplate><asp:button CssClass="buttonlike" Text="Favourite" runat="server" commandname="Add" ID="btnAdd" /></ItemTemplate></asp:templatecolumn></Columns></asp:DataGrid></div></TD></TR></TABLE><asp:Label ID="oops" Runat="server"></asp:Label></form></body>

That the entirety of the front end code, basically at page load inside a "If not Is Postback" the Filldata() method is invoked followed by the CreateTempData() method. Not a lot to it really.|||

Hello Naimh,

Sorry, but I've been moving you into the wrong direction. My first idea was correct, but I didn't read your code properly. Embarrassed

When you call FillData the dataset ds is filled from the table tblPickList. In FillData you add 2 columns and modify all rows with the session HRID. At this stage your rows have the status Modified in the dataset ds. (This generates the error message when calling da.Update())

Before "DgrdFavourites.DataSource = ds" you can enter the command ds.AcceptChanges(). All modifications are accepted and no update statement will be generated by the dataadapter.

The second thing is make some changes to CreateTempRecords. Create a new dataset dstemp as a copy of ds. After that insert the rows from ds into the new dataset dstemp, for example with a foreach construction. Now the new dataset is ready for the insert statement that you have written in CreateTempRecords(). Change da.Update(ds.Tables(0)) into da.Update(dstemp.Tables(0)).

Now your code should do the job!

Jeroen Molenaar.

data with apostrophe in it

How do I add data that has an apostrophe in it?
For example,
update logon set question='Mother's maiden name'
won't work as there is an apostrophe in it. I need to be able to allow
apostrophes in some of my fields.
Thanks,
TomDelimit with a second apostrophe:
update logon set question='Mother''s maiden name'
If you're passing this in through a client, then use a parameter, which you
assign the string to. That will handle the apostrophes for you.|||Hi tshad
for this u need to use 2 single apostrophies
update logon set question='Mother''s maiden name'
best Regards,
Chandra
---
"tshad" wrote:

> How do I add data that has an apostrophe in it?
> For example,
> update logon set question='Mother's maiden name'
> won't work as there is an apostrophe in it. I need to be able to allow
> apostrophes in some of my fields.
> Thanks,
> Tom
>
>|||Hi tshad,
If you are passing eg. via a client VB, for those parameters, parse with a r
eplace string function 1st
Example:
Dim sSQL as string
Dim sParam as string
sParam = replace(Mother's maiden name, '"'", "''") -- note: the
bold are double quotes while unbold are single quote
sSQL = "update logon set question='" & sParam & "'" -- note: the bold ar
e double quotes while unbold are single quote
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message news:23556560-7C78-481D-A84F
-C59DC9C0F8CB@.microsoft.com...
> Hi tshad
> for this u need to use 2 single apostrophies
>
> update logon set question='Mother''s maiden name'
>
>
> --
> best Regards,
> Chandra
> ---
>
>
>
> "tshad" wrote:
>|||... or use a command object with parameter objects. That will take care of
it for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kriste L" <whiteegg@.hotmail.com> wrote in message news:e24XPHFVFHA.628@.tk2m
sftngp13.phx.gbl...
Hi tshad,
If you are passing eg. via a client VB, for those parameters, parse with a r
eplace string function
1st
Example:
Dim sSQL as string
Dim sParam as string
sParam = replace(Mother's maiden name, '"'", "''") -- note: the
bold are double quotes
while unbold are single quote
sSQL = "update logon set question='" & sParam & "'" -- note: the bold ar
e double quotes while
unbold are single quote
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:23556560-7C78-481D-A84F-C59DC9C0F8CB@.microsoft.com...
> Hi tshad
> for this u need to use 2 single apostrophies
> update logon set question='Mother''s maiden name'
>
> --
> best Regards,
> Chandra
> ---
>
> "tshad" wrote:
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e4GFjKHVFHA.3544@.TK2MSFTNGP12.phx.gbl...
> ... or use a command object with parameter objects. That will take care of
> it for you.
It did.
Thanks,
Tom

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kriste L" <whiteegg@.hotmail.com> wrote in message
> news:e24XPHFVFHA.628@.tk2msftngp13.phx.gbl...
> Hi tshad,
> If you are passing eg. via a client VB, for those parameters, parse with a
> replace string function 1st
> Example:
> Dim sSQL as string
> Dim sParam as string
> sParam = replace(Mother's maiden name, '"'", "''") -- note:
> the bold are double quotes while unbold are single quote
> sSQL = "update logon set question='" & sParam & "'" -- note: the bold
> are double quotes while unbold are single quote
>
>
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:23556560-7C78-481D-A84F-C59DC9C0F8CB@.microsoft.com...
>|||"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:82BCBE73-3EF2-4A48-AF62-0D296EAB0C9D@.microsoft.com...
> Delimit with a second apostrophe:
> update logon set question='Mother''s maiden name'
>
I assume I would do a replace on all my strings that a user would put
apostrophes, first.
Tom

> If you're passing this in through a client, then use a parameter, which
> you
> assign the string to. That will handle the apostrophes for you.
I would use both.
Thanks,
Tom

Data warehouse data refresh/update

Hello,
Im a member of a team planning a data warehousing project. We have multiple data sources which are aggregated in a staging area. This is then denormalised and imported into the datawarehouse database.
I am looking at ideas for incremental data refresh, rather than a drop and re-import of all data. This would allow us to have historic data.
Does anyone have any tips that might be helpful for detecting changes in the source data for import? We have had some bad experiences with triggers on our source database in the past, so would rather not use these. I have considered replication and log sh
ipping, but these just give a replica of the source data and does not flag the updated/new data.
Any help would be greatly appreciated.
Thanks.
Ben.
Look in SQL Books Online for CHECKSUM_AGG to identify changes in a table. The difficulty then is in trying to identify what has changed. Another route is to try and identify fields within the production data that will identify when it was last changed. Ty
pically these tend to be datetime, timestamp or rowversion data types.
If you keep production keys in your fact table as additional attributes then this will give you another option for identifying new data.
"Ben" wrote:

> Hello,
> Im a member of a team planning a data warehousing project. We have multiple data sources which are aggregated in a staging area. This is then denormalised and imported into the datawarehouse database.
> I am looking at ideas for incremental data refresh, rather than a drop and re-import of all data. This would allow us to have historic data.
> Does anyone have any tips that might be helpful for detecting changes in the source data for import? We have had some bad experiences with triggers on our source database in the past, so would rather not use these. I have considered replication and log
shipping, but these just give a replica of the source data and does not flag the updated/new data.
> Any help would be greatly appreciated.
> Thanks.
> Ben.
|||Timestamp your source data when it gets changed. That is definately the best way.
Rgards
Jamie
"Ben" wrote:

> Hello,
> Im a member of a team planning a data warehousing project. We have multiple data sources which are aggregated in a staging area. This is then denormalised and imported into the datawarehouse database.
> I am looking at ideas for incremental data refresh, rather than a drop and re-import of all data. This would allow us to have historic data.
> Does anyone have any tips that might be helpful for detecting changes in the source data for import? We have had some bad experiences with triggers on our source database in the past, so would rather not use these. I have considered replication and log
shipping, but these just give a replica of the source data and does not flag the updated/new data.
> Any help would be greatly appreciated.
> Thanks.
> Ben.

Data warehouse data refresh/update

Hello,
Im a member of a team planning a data warehousing project. We have multiple
data sources which are aggregated in a staging area. This is then denormalis
ed and imported into the datawarehouse database.
I am looking at ideas for incremental data refresh, rather than a drop and r
e-import of all data. This would allow us to have historic data.
Does anyone have any tips that might be helpful for detecting changes in the
source data for import? We have had some bad experiences with triggers on o
ur source database in the past, so would rather not use these. I have consid
ered replication and log sh
ipping, but these just give a replica of the source data and does not flag t
he updated/new data.
Any help would be greatly appreciated.
Thanks.
Ben.Look in SQL Books Online for CHECKSUM_AGG to identify changes in a table. Th
e difficulty then is in trying to identify what has changed. Another route i
s to try and identify fields within the production data that will identify w
hen it was last changed. Ty
pically these tend to be datetime, timestamp or rowversion data types.
If you keep production keys in your fact table as additional attributes then
this will give you another option for identifying new data.
"Ben" wrote:

> Hello,
> Im a member of a team planning a data warehousing project. We have multipl
e data sources which are aggregated in a staging area. This is then denormal
ised and imported into the datawarehouse database.
> I am looking at ideas for incremental data refresh, rather than a drop and
re-import of all data. This would allow us to have historic data.
> Does anyone have any tips that might be helpful for detecting changes in the sourc
e data for import? We have had some bad experiences with triggers on our source data
base in the past, so would rather not use these. I have considered replication and l
og
shipping, but these just give a replica of the source data and does not flag the updated/new
data.
> Any help would be greatly appreciated.
> Thanks.
> Ben.|||Timestamp your source data when it gets changed. That is definately the best
way.
Rgards
Jamie
"Ben" wrote:

> Hello,
> Im a member of a team planning a data warehousing project. We have multipl
e data sources which are aggregated in a staging area. This is then denormal
ised and imported into the datawarehouse database.
> I am looking at ideas for incremental data refresh, rather than a drop and
re-import of all data. This would allow us to have historic data.
> Does anyone have any tips that might be helpful for detecting changes in the sourc
e data for import? We have had some bad experiences with triggers on our source data
base in the past, so would rather not use these. I have considered replication and l
og
shipping, but these just give a replica of the source data and does not flag the updated/new
data.
> Any help would be greatly appreciated.
> Thanks.
> Ben.

Friday, February 24, 2012

data update suggestions

We have production database (1TB, sql server 2005) and we will receive daily
updates from a vendor. All the updates will be imported in another database
first (source db). What would be the best way to apply the updated
information to the from the source DB to the production DB (example of
updates are: new products or just price changes on existing products
etc...). I was thinking about unidirectional merge database (src db to prod
db) but if someone can give some suggestions it would be greatly appreciated!
Thank you all
--
System Engineer
MSc in CS, MCSE4, IBM Certified MQ 5.3 Administrator
"I saw it work in a cartoon once so I am pretty sure I can do it."
I guess you should start with estimating amount of data being transfered, it
frequency and concurrency requirement.
the more data and more frequent and how fast you have to be "in sync"-
replication is most likely scenario you should go with. Less data and less
frequent - a custom app or SSIS package might do the trick...
"Sas" <Sas@.discussions.microsoft.com> wrote in message
news:0F14BBEA-A720-40FF-88A3-8C7B126DBC46@.microsoft.com...
> We have production database (1TB, sql server 2005) and we will receive
> daily
> updates from a vendor. All the updates will be imported in another
> database
> first (source db). What would be the best way to apply the updated
> information to the from the source DB to the production DB (example of
> updates are: new products or just price changes on existing products
> etc...). I was thinking about unidirectional merge database (src db to
> prod
> db) but if someone can give some suggestions it would be greatly
> appreciated!
> Thank you all
> --
> --
> System Engineer
> MSc in CS, MCSE4, IBM Certified MQ 5.3 Administrator
> "I saw it work in a cartoon once so I am pretty sure I can do it."
|||Why are you using merge when the data flow is in one direction. You should
be using transactional for this. Expect at least twice the amount of data
flow that hits the publisher to be applied on the subscriber. However this
can vary widely depending on how many rows are affected by each DML on the
publisher and whether text or binary data is involved.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rafael Lenartowicz" <rafaell@.rcl-consulting.com> wrote in message
news:OrX0acKfHHA.1252@.TK2MSFTNGP04.phx.gbl...
>I guess you should start with estimating amount of data being transfered,
>it frequency and concurrency requirement.
> the more data and more frequent and how fast you have to be "in sync"-
> replication is most likely scenario you should go with. Less data and less
> frequent - a custom app or SSIS package might do the trick...
> "Sas" <Sas@.discussions.microsoft.com> wrote in message
> news:0F14BBEA-A720-40FF-88A3-8C7B126DBC46@.microsoft.com...
>
|||How are the updates imported into the source db? If they are indeed applied
as TSQL updates (as opposed to tables being overwritten) then look at
transactional replication. If the schema of the source db and production db
are the same and you have PKs on all the involved tables then things will be
straightforward.
Paul Ibison

Data Update Problem

Dear All,
I am facing a problem from last one month. I have around 100 of client user of my database. I am using VB application for the client users. When they submit the information most of the time the information submits properly. But twice or thrice a day no one can submit the information properly. It remains for more then 15 mins and it will sort out automatically.
What might be the problem. Please suggestDatabase locking?

Without more information, we can't really help you.

When this happens again, check to see if any blocking is occuring.|||I am trying to trace from the Profiler. All the Insert statement to that particular table is taking a lot of time. At that point of time the cpu usases, memory everything is ok.|||did you sp_who2 or sp_lock?

data update notification

Please Help!!I have been trying to think of a solution to a problem i have and still not sure how to go about it.

I have a database filled with message data that is sent by one user to another.

What i am trying to do is have it so (if users are online) they are notified - by a popup or something - that a new message has arrived forthem.

My idea was to create a trigger on the message table to update a change table with sender and recipient ids and date of change in it.

Not a problem so far but what is troubling me is that i want the recipient to automatically query this change table every say 30 seconds to check if new message has arrived with their id.

Tryed using ajax update panel and timer but naturally that kept posting back whole page - looks really terriable :( - tryed sqlnotification but as far as i can see that is only changing cached data for current data - not the same issue i think.

Is there a way to query db table regualry without the client side knowing? - thinking along lines of how outlook requeries server for new messages and displays popup to say new message but doesnt interupt you if doing something else.

Any help would begreatly appreaciated.

Thanks

I am using the following in one of my apps. In case you need help understanding some part, get back to me. What is happening is that I have a webservice that I call on a fixed interval from my page using javascript. Based on the result returned by the webservice, I am using javascript to inform the user by setting certain UI elements.

In aspx page

<asp:ScriptManager ID="ScriptManager1" runat="server" ScriptMode="Release" LoadScriptsBeforeUI="true">
<Services>
<asp:ServiceReference Path="CargoListDB.asmx" />
</Services>
</asp:ScriptManager>

function GetFleetFlagStatus() {
var userContext;
CargoListDB.GetFleetFlagStatus(lastChecked, OnSucceededGetFleetFlagStatus, null, userContext, null);
}
function OnSucceededGetFleetFlagStatus(result, userContext) {

//result is the result returned by webservice

//your code
}

</script>

Webservice

<%@. WebService Language="VB" Class="CargoListDB" %
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Data.OracleClient

<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<System.Web.Script.Services.ScriptService()> _
Public Class CargoListDB
Inherits System.Web.Services.WebService

<WebMethod()> _
Public Function GetFleetFlagStatus(ByVal lastChecked As String) As String
'your code
Return result
End Function

End Class|||

Hey Prashant,

Thanks for replying - been trying to play around with this webservice idea but not really getting anywhere unfortunatly - doesnt even seem to be calliing it.

Will keep playing with it tho.

Cheers

|||

Post your code (both webservice and the calling code). I might be able to help you fix it.

|||

// in head of aspx page

// scriptmanager

<asp:ScriptManager ID="ScriptManager1" runat="server" ScriptMode=Release LoadScriptsBeforeUI=true>
<Services >
<asp:ServiceReference Path="MessageService.asmx" />
</Services>
</asp:ScriptManager>

// webservice page

using System;
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

/// <summary>
/// Summary description for MessageService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class MessageService : System.Web.Services.WebService
{

public MessageService ()
{

//Uncomment the following line if using designed components
//InitializeComponent();
}

[WebMethod]
public string getMessage()
{

string connString = ConfigurationManager.ConnectionStrings["EMS_App_DBConnectionString"].ConnectionString;

SqlConnection conn = new SqlConnection(connString);

SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = "SELECT * FROM EMS_ChangeEvent_Msg WHERE EMS_ChangeEvent_Msg_ID = @.EMS_ChangeEvent_Msg_ID";
cmd.Parameters.Add("@.EMS_ChangeEvent_Msg_ID", SqlDbType.Int, Int32.MaxValue).Value = 8;

conn.Open();

SqlDataReader reader = cmd.ExecuteReader();

// get returned data and place in data table -
DataTable taskTable = new DataTable();
taskTable.Load(reader);

string info = taskTable.Rows[0]["EMS_ChangeEvent_Msg_Date"].ToString();
conn.Close();

return info;
}

}

if i just run the webservice in debug it works - gets value, hits breakpoints etc so am guessing my complete lack of
javascript understanding is to blame or do i need to initialise webservice anywhere?

Am running on localhost during this test - call is in master page

WebServices are another new thing to me so not up to play yet

Thanks for looking at

|||

In order for your webservice to be callable by clientscript, you need to have the following attribute defined for the webservice

System.Web.Script.Services.ScriptService()

/// <summary>
/// Summary description for MessageService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]
public class MessageService : System.Web.Services.WebService
{

//your code

In web.config, you need the following in the configuration section (it might already be there, just confirm)

Refer to the following link on the configuration requirements

http://ajax.asp.net/files/Migration_Guide_Beta2_to_RC.aspx

|||

Hey,

Still no go unfortunatly.

If run app and have breakpoints in webservice .cs they never get hit so never being called in first place.

Can it be run in local host? - shld be i'm sure but never know.

|||

Just to make sure, as per your code, the page and webservice should be in the same folder.

Can you email your complete test project (page, webservice and web.config)? Easier to fix that way.

|||

Hey, Thanks for taking the time here are the pages - the service is in the same folder - as in the asmx - but the .cs file was put in the app code folder which from what i've read is just where it goes automatically.

As this is an add on to my project that has all sorts of other stuff going on :) waswondering does authentication make a diff?

Anyway the pages-

web-config ( have had system.webserver with the http handlers in there also at end just took back out before post)

<?xml version="1.0"?>

<configuration>
<appSettings>
<add key="webReference.MessageService" value="http://localhost:1035/EMS_Application_01/MessageService.asmx"/>
</appSettings>
<connectionStrings>
<add name="EMS_App_DBConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\EMS_App_DB.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<caching>
<sqlCacheDependency enabled="true">
<databases>
<add connectionStringName="EMS_App_DBConnectionString" name="EMS_App_DB" pollTime="1000"/>
</databases>
</sqlCacheDependency>
</caching>


<compilation debug="true" defaultLanguage="c#">

<compilers>
<compiler language="c#" type="Microsoft.CSharp.CSharpCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089" extension=".cs" compilerOptions="/d:DEBUG;TRACE"/>
</compilers>

<assemblies>
<add assembly="System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
<add assembly="System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>

<add assembly="CrystalDecisions.ReportSource, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
<add assembly="CrystalDecisions.Enterprise.Framework, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
<add assembly="CrystalDecisions.Enterprise.InfoStore, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>

<add assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
<add assembly="System.Web.Extensions.Design, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
<add assembly="System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
<add assembly="System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
<add assembly="System.Configuration, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
<add assembly="System.Drawing, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
<add assembly="System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
<add assembly="System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
<add assembly="System.Web.Services, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
<add assembly="System.DirectoryServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
<add assembly="System.DirectoryServices.Protocols, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
<add assembly="System.EnterpriseServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
<add assembly="System.ServiceProcess, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
<add assembly="System.Web.RegularExpressions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/></assemblies>
</compilation>

<authentication mode="Forms">
<forms loginUrl="SiteLogon.aspx" name="sqlAuthCookie" timeout="10" path="/" protection="All">
</forms>
</authentication>
<authorization>
<deny users="?"/>
<allow users="*"/>
</authorization>

<sessionState mode="InProc" cookieless="false" timeout="10">
</sessionState>

<xhtmlConformance mode="Legacy"/>
<httpHandlers>

<remove verb="*" path="*.asmx"/>
<add verb="*" path="*.asmx" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
<add verb="*" path="*_AppService.axd" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
<add verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" validate="false"/>
<add verb="GET" path="CrystalImageHandler.aspx" type="CrystalDecisions.Web.CrystalImageHandler, CrystalDecisions.Web, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
</httpHandlers>

</system.web
</configuration>

.master page

<%@. Master Language="C#" AutoEventWireup="true" EnableTheming="true" CodeFile="MasterPage.master.cs" Inherits="MasterPage" %>
<%@. Register Assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
Namespace="System.Web.UI" TagPrefix="asp" %
<%@. Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<BASE target="_self"></BASE
<title></title>
<meta http-equiv="Page-Exit" content="progid:DXImageTransform.Microsoft.Fade(duration=.3)" />

<script language=javascript>

function testWin()
{

window.open('SetMsgOptions.aspx','_blank','height=200,width=500,status=yes,toolbar=no,menubar=no, chromeless=true,titlebar=no,location=no,left=window.event.clientY,top=window.event.clientX')

}
function winClose()
{
window.close();
}
</script>



<script language=javascript type="text/javascript">
window.setInterval("getMessage()", 10000);

function getMessage()
{
var userContext;
userContext = document.getElementById("lbltime").value
MessageService.getMessage(lastChecked, OnSuccededGetMessage, null, userContext, null);

}

function OnSuccededGetMessage(result, userContext)
{

window.alert("new message");

}

</SCRIPT>

</head>
<body >
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server" ScriptMode=Release LoadScriptsBeforeUI=true>
<Services >
<asp:ServiceReference Path="MessageService.asmx" />
</Services>
</asp:ScriptManager>





<div id=title>

<asp:Label ID="lblPageTitle" runat="server" ></asp:Label>



<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:HiddenField ID="lbltime" Value="24/05/2007 10:42:20 p.m." runat="server" />

</div>

<div id=headerPos >
<div id=toprightheadcorner></div>
<div id=bottomleftheadcorner></div>

<div id=user>

<table width=100% align=right class=normalFont>
<tr><td valign=middle align=right style="height: 65px">

<asp:Label ID="lblEntity" runat="server" Text=""></asp:Label>

</td>
<td width=20px style="height: 55px">
<asp:Image ID="imgEntity" runat="server" />
</td></tr>
</table>

</div>

<div >
<table width=100% align=right class=normalFont>
<tr><td valign=bottom align=right style="height: 55px">

<asp:LoginName ID="LoginName1" runat="server" Font-Bold="True" Font-Names="Verdana" Font-Size="Smaller" FormatString="Signed into: {0} " />
||
<asp:LoginStatus ID="LoginStatus1" CssClass=normalLinkFont runat="server" LogoutAction="RedirectToLoginPage" LogoutPageUrl="~/SiteLogon.aspx" OnLoggingOut="LoginStatus1_LoggingOut" Font-Bold="True" Font-Names="Verdana" Font-Size="Smaller" />
</td>
<td width=20px style="height: 55px"></td></tr>
</table>
</div>

</div>





<div id=navTag>

<div id=infolabel>
<asp:Label ID="lblInfo" runat="server" CssClass=infoFont Text="Open Events:"></asp:Label>
</div>

</div>

MessageService page

using System;
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

using System.Configuration;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

/// <summary>
/// Summary description for MessageService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]
public class MessageService : System.Web.Services.WebService
{

public MessageService ()
{

//Uncomment the following line if using designed components
//InitializeComponent();
}

[WebMethod]
public string getMessage()
{

string connString = ConfigurationManager.ConnectionStrings["EMS_App_DBConnectionString"].ConnectionString;

SqlConnection conn = new SqlConnection(connString);

SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = "SELECT * FROM EMS_ChangeEvent_Msg WHERE EMS_ChangeEvent_Msg_ID = @.EMS_ChangeEvent_Msg_ID";
cmd.Parameters.Add("@.EMS_ChangeEvent_Msg_ID", SqlDbType.Int, Int32.MaxValue).Value = 8;

//24/05/2007 10:42:20 p.m.

conn.Open();

SqlDataReader reader = cmd.ExecuteReader();

// get returned data and place in data table -
DataTable taskTable = new DataTable();
taskTable.Load(reader);

string info = taskTable.Rows[0]["EMS_ChangeEvent_Msg_Date"].ToString();
conn.Close();

return info;
}

}

Hope this makes sense in its context

Cheers

|||

Firstly, the security does affect the webservice

To make the webservice accessible to all, add the following to the configuration section of your web.config

Secondly, userContext expects a json object e.g.
var userContext = [cgoId, elemToUpdate];

It can later be used in the onSucceeded function as
var cgoId = userContext[0];
var elemToUpdate = userContext[1];

My guess is the security issue is causing the problem. Allowing anonymous access to the webservice should solve the problem. Try and let me know.

|||

To help me understand a little better could you please tell me whats happening here -

MessageService.getMessage(lastChecked, OnSuccededGetMessage, null, userContext, null);

what are being passed, how, why?

Reality is i want the webservice to look up db with two values that i would have to pass from .aspx page so they are the objects need to send.

This is driving me a bit insane at moment - last try the error console in firefox was saying that the problem was in the ScriptResource.asxd - dont even know where/what this is!

I appreciate the help but if this looks like going on forever i understand help must stop at some point ;)

cheers

|||

All the parameters before the function reference of the onsucceeded function are those that are expected by the webservice.

userContext is a JSON object which is used to associate relevant data with a particular call. (google JSON to learn how they work)
Since a call here is asynchronous, you need a means to identify which specific call is returning the current result. The userContext object helps you with that.

Sorry, I think I forgot to post the relevant links. Refer to the following links to understand how this works

http://msdn.microsoft.com/msdnmag/issues/07/01/ExtremeASPNET/default.aspx

http://ajax.asp.net/docs/tutorials/ConsumingWebServicesWithAJAXTutorial.aspx

This is a simple webservice call using XMLHttp (not using the ScriptManager)

http://forums.asp.net/t/1112930.aspx

|||

Hey Prashant,

Thanks for the effort - finally got there :). This problem has been vexing me for a while and this solution does just what i need so cheers.

this link - http://ajax.asp.net/docs/tutorials/ConsumingWebServicesWithAJAXTutorial.aspx - was most helpful as gave the essential grounding on what i was actually doing - somtimes better to read properly rather than hack eh?

Cheers much

Data update

Hello all,

I'm new in this newsgroup and I apologise if my question has already done.
Let me knwo if FAQ is avaible somewhere.

I use msSQL server to manage data for a B2B portal.
Orginal data comes from as400 db2.

I need to update msSQL data from db2 source.

I used MetaDataService executed by SQL server Agent, but dosn't work as
excepted 'cause I need to delete all row in a table and then copy all new
rows, this works fine but what happen if an user is quering data while sql
agent starts?
I need to make data update very often so I need other solution.

The update procedure must follow these rules:
1- check diffrents between a table in DB2(source) and the same table on
msSQL server
2- update my msSQL table if some diffrents was found.

I'm a beginner with msSQL server, any advices are welcomed, I need a startin
point.
please help.

thank you all
kindly regards
AlbertoIt all depends on your timing. If it's only for a few seconds, the
risk may be worthwhile in comparison to the development time. If it's
longer, I'd suggest adding in a 'work' table to do the import into (if
this is what takes the time) and then moving the date 'en-mass' from
there. This may give you the improvement you need.

I have a similar situation where I have to generate a lot of data from
various sources and replace it in a live system after truncating the
existing data. I do the generation work in a seperate table and then
move it over afterwards. The final part takes a couple of seconds to
do. You could always add in something for these few seconds to
introduce a deliberate delay to the user if the table isn't populated
(maybe hold a flag somewhere in a 1 row table ?) and then release it
when you have finished.

Ryan

"Alberto" <alberto@.nomail.it> wrote in message news:<cof0ge$9eq$1@.newsreader.mailgate.org>...
> Hello all,
> I'm new in this newsgroup and I apologise if my question has already done.
> Let me knwo if FAQ is avaible somewhere.
> I use msSQL server to manage data for a B2B portal.
> Orginal data comes from as400 db2.
> I need to update msSQL data from db2 source.
> I used MetaDataService executed by SQL server Agent, but dosn't work as
> excepted 'cause I need to delete all row in a table and then copy all new
> rows, this works fine but what happen if an user is quering data while sql
> agent starts?
> I need to make data update very often so I need other solution.
> The update procedure must follow these rules:
> 1- check diffrents between a table in DB2(source) and the same table on
> msSQL server
> 2- update my msSQL table if some diffrents was found.
> I'm a beginner with msSQL server, any advices are welcomed, I need a startin
> point.
> please help.
> thank you all
> kindly regards
> Alberto

Sunday, February 19, 2012

Data type in audit record

Hi,

TABLE: audit_record
*audit_record_id (uniqueidentifier, auto-assign, PK) - unique
idenfiier of the audit record
table_name (varchar) - name of the table where the action (insert/
update/delete) was made
pk_value (varchar) - primary key of the changed record. If the PK
itself has changed, this will store the old value.
user_id (varchar) - user who changed the record
date (datetime) - date/time at which the change was made
action (int) - 0, 1 or 2 (insert, update, delete)

TABLE: audit_column
*audit_record_id (uniqueidentifier, composite PK) - FK to
cdb_audit_record table
*column_name (varchar, composite PK) - name of the column with changed
data
new_value (text?) - value after the change

So every column which changes has its new value logged individually in
the audit_column table. However, I'm not sure what data type the
new_value column should have. The obvious answer (to me) is text, as
that can handle any necessary data type with the appropriate
conversion (we don't store any binary data). However, this table is
going to grow to millions of records and I'm not sure what the
performance implications of a text column will be, particularly given
that the actual data stored in it will almost always be tiny.

Any thoughts/recommendations/criticism would be greatly appreciated.

Thanks
AlexSorry for replying to myself - I forgot to state that I'm using SQL
Server 2000 standard edition.|||WombatDeath@.gmail.com wrote:

Quote:

Originally Posted by

I want my application to audit any data changes (update, insert,
delete) made by the users. Rather than have an audit table mirroring
each user table, I'd prefer to have a generic structure which can log
anything. This is what I've come up with:
>
TABLE: audit_record
*audit_record_id (uniqueidentifier, auto-assign, PK) - unique
idenfiier of the audit record
table_name (varchar) - name of the table where the action (insert/
update/delete) was made
pk_value (varchar) - primary key of the changed record. If the PK
itself has changed, this will store the old value.
user_id (varchar) - user who changed the record
date (datetime) - date/time at which the change was made
action (int) - 0, 1 or 2 (insert, update, delete)
>
TABLE: audit_column
*audit_record_id (uniqueidentifier, composite PK) - FK to
cdb_audit_record table
*column_name (varchar, composite PK) - name of the column with changed
data
new_value (text?) - value after the change
>
So every column which changes has its new value logged individually in
the audit_column table. However, I'm not sure what data type the
new_value column should have. The obvious answer (to me) is text, as
that can handle any necessary data type with the appropriate
conversion (we don't store any binary data). However, this table is
going to grow to millions of records and I'm not sure what the
performance implications of a text column will be, particularly given
that the actual data stored in it will almost always be tiny.
>
Any thoughts/recommendations/criticism would be greatly appreciated.

Do you actually have anything (or any reasonable prospect of having
anything in future) for which NVARCHAR(4000) wouldn't be good enough?

Whatever you do, I strongly recommend keeping tabs on how quickly it
grows, showing that trend information to the client, and (1) narrow it
down to the tables that really need an audit trail and/or (2) come up
with a sane archive-and-purge schedule.|||On Mar 30, 3:42 pm, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

WombatDe...@.gmail.com wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Any thoughts/recommendations/criticism would be greatly appreciated.

>
Do you actually have anything (or any reasonable prospect of having
anything in future) for which NVARCHAR(4000) wouldn't be good enough?
>
Whatever you do, I strongly recommend keeping tabs on how quickly it
grows, showing that trend information to the client, and (1) narrow it
down to the tables that really need an audit trail and/or (2) come up
with a sane archive-and-purge schedule.

Yeah, unfortunately we do have several tables with a column of type
text. These generally don't hold anything close to 4000 chars but
there's nothing actually preventing them from doing so. But...if
there's no tidier option I think I may just truncate to 4000 and be
done with it. We're not auditing to fulfil legal obligations or
anything nasty like that so I don't think it will be a problem.

Your point about maintenance is well taken. I've specified that the
application's auditing must be configurable on an entity-by-entity
basis, and every so often we'll archive away any old data for fast-
changing entities.

Thanks very much for your input!|||(WombatDeath@.gmail.com) writes:

Quote:

Originally Posted by

That is not going to be fun in SQL 2000. In SQL 2005 you could build a
generic audit solution on the xml data type.

I would recommend that you research the market for audit products. I
know for instance that ApexSQL has a something they call SQLAudit
if memory serves.

--
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|||>I want my application to audit any data changes (update, insert, delete) made by the users. Rather than have an audit table mirroring each user table, I'd prefer to have a generic structure which can log anything. <<

Any chance you might post DDL instead of your personal pseudo-code?
And I hope you know that auto-numbering is not a relational key.
Finally, Google "EAV design flaw" for tens of thousands of words on
why this approach stinks. There is no such magical shape shifting
table in RDBMS. Data Versus metadata, etc.? Freshman database
course, 3rd week of the quarter?

While you might like this kludge your accountants and auditors will
not. NEVER keep audit trails on the same database or even the same
hardware as the database.

Quote:

Originally Posted by

Quote:

Originally Posted by

>Any thoughts/recommendations/criticism would be greatly appreciated. <<

Look at third party tools that follow the law and get a basic dat
modeling book.

Friday, February 17, 2012

Data type Conversion during update ...

I am trying to update a row in a table. To my SP, I send an XML string.
One of the columns I am trying to update is of type "Bit" and the value I am
getting from my app is either "true" or "false". This is the general
structure of sp:
UPDATE x
SET x.isEmployee = xmlEmp.isEmployee
WHERE ...
I tried the following to convert my xmlEmp.isEmployee to "BIT" ...
SET x.isEmployee = CASE WHEN xmlEmp.isEmployee='false' THEN 0 ELSE 1 END
SET x.isEmployee = CASE WHEN xmlEmp.isEmployee='false' THEN CAST(0 AS BIT)
ELSE CAST(1 AS BIT) END
both of them don't convert the strings 'false' and 'true' to 0 and 1 ... not
sure what I am missing. Appreciate any tips. TIA.
"exBK" <exBK@.discussions.microsoft.com> wrote in message
news:09146AA0-F234-45F6-9B71-D4F90FDED3B2@.microsoft.com...
>I am trying to update a row in a table. To my SP, I send an XML string.
> One of the columns I am trying to update is of type "Bit" and the value I
> am
> getting from my app is either "true" or "false".
That should work...
declare @.x varchar(10), @.y bit
set @.x = 'true'
set @.y = case when @.x = 'false' then 0 else 1 end
select @.y
The above results in 1. Are you getting an error?
Bryant

Thursday, March 29, 2012

Database Compare / Update

Wednesday, March 7, 2012

Database "staging" area

Dataadpater does not update database

dataadapter.Update() OR ExecuteNonQuery()

DataAdapter.Update updates zero records

DataAdapter.Update Method Question.

Saturday, February 25, 2012

DataAdapter Update Problems

data with apostrophe in it

Data warehouse data refresh/update

Data warehouse data refresh/update

Friday, February 24, 2012

data update suggestions

Data Update Problem

data update notification

Data update

Sunday, February 19, 2012

Data type in audit record

Friday, February 17, 2012

Data type Conversion during update ...

Database corrupted

Blog Archive

About Me