MS SQL Server :: Data Access :: What Is Correct Usage For Processing Data Adapter Rows

Data Adapter FILL Causing Data Corrupt Error In C# Web Service

Apr 2, 2008

I hope someone has seen this one...I've got a very simple web method that returns a DataTable from my database. It worked fine until I changed this line of code:

string SqlString = @"SELECT Timestamp, Confession FROM Confessions WHERE ConfessionID = ? ORDER BY Timestamp desc";

To this line of code:

string SqlString = @"SELECT Timestamp, Confession FROM Confessions WHERE ConfessionID = ? AND CategoryID = ? ORDER BY Timestamp desc";

Here is the complete webmethod:

[WebMethod]
public DataTable GetConfession (int ConfessionID, int CategoryID) {

DataTable dt = new DataTable();
dt.TableName = "XMLConfession";

string SqlString = @"SELECT Timestamp, Confession FROM Confessions WHERE ConfessionID = ? AND CategoryID = ? ORDER BY Timestamp desc";

using (OleDbConnection cn = new OleDbConnection (ConnectionString)) {
using (OleDbCommand cmd = new OleDbCommand (SqlString, cn)) {

cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue ("@ConfessionID", ConfessionID);
cmd.Parameters.AddWithValue ("@CategoryID", CategoryID);
cn.Open();

OleDbDataAdapter da = new OleDbDataAdapter (cmd);

da.Fill (dt);
}
}

return dt;
}
To cause the error, all I did was add the AND statement into the query. Now I get this .NET error message every time I try to run this program:

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt. at System.Data.Common.UnsafeNativeMethods.ICommandWithParameters.SetParameterInfo(IntPtr cParams, IntPtr[] rgParamOrdinals, tagDBPARAMBINDINFO[] rgParamBindInfo) at System.Data.OleDb.OleDbCommand.ApplyParameterBindings(ICommandWithParameters commandWithParameters, tagDBPARAMBINDINFO[] bindInfo) at System.Data.OleDb.OleDbCommand.CreateAccessor() at System.Data.OleDb.OleDbCommand.InitializeCommand(CommandBehavior behavior, Boolean throwifnotsupported) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

I am using SQL Server Compact Edition 3.5 and C# .NET 2.0. It's worth noting that the web service does this outside of the program as well, so I don't think the problem is in the code that calls it. Also worth noting is that the query itself works fine when I substitute values in and run it in .NET Server Explorer.

Thanks in advance for any assistance.

View 5 Replies View Related

Asp.net Page Is Unable To Retrieve The Right Data Calling The Store Procedure From The Dataset/data Adapter

Apr 11, 2007

I'm trying to figure this out
I have a store procedure that return the userId if a user exists in my table, return 0 otherwise
------------------------------------------------------------------------
Create Procedure spUpdatePasswordByUserId
@userName varchar(20),
@password varchar(20)
AS
Begin
Declare @userId int
Select @userId = (Select userId from userInfo Where userName = @userName and password = @password)
if (@userId > 0)
return @userId
else
return 0
------------------------------------------------------------------
I create a function called UpdatePasswordByUserId in my dataset with the above stored procedure that returns a scalar value. When I preview the data from the table adapter in my dataset, it spits out the right value.
But when I call this UpdatepasswordByUserId from an asp.net page, it returns null/blank/0
passport.UserInfoTableAdapters oUserInfo = new UserInfoTableAdapters();
Response.Write("userId: " + oUserInfo.UpdatePasswordByUserId(txtUserName.text, txtPassword.text) );
Do you guys have any idea why?

View 6 Replies View Related

Data Access :: Insert Rows To MS Access 2013 Without Listing Column Names

Nov 12, 2015

We need to insert data/rows from a SQL Server 2014 database into MS Access database. The problem is, there are so many columns (100+) in the table and there are so many insert transactions of this kind (from different tables) that it is not very easy to write the code in VB.NET that lists all column names.

Both the Access and SQL Server tables have the same number of columns and the equivalent data types, so inserting is not really the problem. It's just that is there a way to do an insert statement in T-SQL that does not name all the columns?

View 3 Replies View Related

Data Reader Or Data Adapter With Data Set?

Dec 4, 2007

I have used both data readers and data adapters(with datasets) in the projects that I have worked on. I am trying to get some clarification on when I should be using which one. I think I am doing this correctly but I want to be sure I am developing good habits.

As the name might suggest, it seems like a datareader is for only reading data. I have read that the data adapter and dataset are for a disconnected architecture. Or, that they can be used for this type of set up. I have been using the data adapter and datasets when writing to a database and the datareader when reading from a database.

Is this how these should be used? Is the data reader the best choice for reading data? Am I doing this the optimal way from a performance stand point?

......................................................thanks in advance

View 1 Replies View Related

Converting A Data Type Double To A Type Float Using A Data Adapter && SSCE 3.5

Feb 13, 2008

Hi,

I can populate a dataTable with type double (C#) of say '1055.01' however when I save these to the CE3.5 database using a float(CE3.5) I lose the decimal portion. The 'offending' code is:

this.court0TableAdapter1.Update(this.mycourtsDataSet1.Court0);

this.mycourtsDataSet1.AcceptChanges();

this.court0TableAdapter1.Fill(this.mycourtsDataSet1.Court0);

This did not happen with VS2005/CE3.01.

I have tried changing all references to decimal (or money in CE3.5) without luck.

I'm beginning to think that string may be the way to go!!!!!!!

Can someone shed some light on my problem.

Thanks,

Later:

It's necessary to update the datatable adapter as the 3.01 and 3.5 CE are not compatible.

View 4 Replies View Related

Data Access :: Merging Two Rows In Two Columns

Jul 8, 2015

I have a table data as shown below.

IDFNLN
1x
1y
2a
2b
3g
4t

I want output as shown below.

IDFNLN
1xy
2ab
3g
4t

I want the two duplicate rows to be merged into one. How to achieve it.

View 10 Replies View Related

Clue - Who Dun It? The Data Adapter, DataSet, SqlSelect Etc...

Feb 23, 2007

Ok, I know my connection string and config file are good. Once the rest of the code is added I receive this error:
"SQL Server does not exist or access denied. "
I'm using the 1.1 framework and my hosting company provides MSSql 2005.
Thanks for your time,
Charlie
Here's the code in my cond behind file.
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

namespace blog
{
            /// <summary>
            /// Summary description for WebForm1.
            /// </summary>
            public class WebForm1 : System.Web.UI.Page
            {
                        protected System.Data.SqlClient.SqlConnection sqlConnect;
                        protected System.Web.UI.WebControls.DataList dList;
                        protected System.Data.SqlClient.SqlDataAdapter DA;
                        protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;
                        protected System.Web.UI.WebControls.LinkButton linkAdmin;
                        protected System.Web.UI.WebControls.Image header;
                        protected blog.DS ds1;

                        private void Page_Load(object sender, System.EventArgs e)
                        {
                                    DA.Fill(ds1, "blogEntry");
                                    dList.DataBind();
                        }

                        #region Web Form Designer generated code
                        override protected void OnInit(EventArgs e)
                        {
                                    //
                                    // CODEGEN: This call is required by the ASP.NET Web Form Designer.
                                    //
                                    InitializeComponent();
                                    base.OnInit(e);
                        }

                        /// <summary>
                        /// Required method for Designer support - do not modify
                        /// the contents of this method with the code editor.
                        /// </summary>
                        private void InitializeComponent()
                        {
                                    this.sqlConnect = new System.Data.SqlClient.SqlConnection();
                                    this.DA = new System.Data.SqlClient.SqlDataAdapter();
                                    this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
                                    this.ds1 = new blog.DS();
                                    ((System.ComponentModel.ISupportInitialize)(this.ds1)).BeginInit();
                                    this.linkAdmin.Click += new System.EventHandler(this.linkAdmin_Click);
                                    //
                                    // sqlConnection String
                                    //////////////////webserver////////////////
                                    this.sqlConnection1.ConnectionString = "Server=xxx.xxx.xxx.x;Database=myDataBase;User ID=myID;Password=myPass";

                                    //////////////////local///////////////////
                                    //this.sqlConnect.ConnectionString = "workstation id=HAL;packet size=4096;integrated security=SSPI;data source=HAL;pers" +
                                                //"ist security info=False;initial catalog=blog";
                                    //
                                    // DA
                                    //
                                    this.DA.SelectCommand = this.sqlSelectCommand1;
                                    this.DA.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
                                                                                                                                                                                                                                                                        new System.Data.Common.DataTableMapping("Table", "blogEntry", new System.Data.Common.DataColumnMapping[] {

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        new System.Data.Common.DataColumnMapping("bodyID", "bodyID"),
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            new System.Data.Common.DataColumnMapping("linkID", "linkID")})});
                                    //
                                    // sqlSelectCommand1
                                    //
                                    this.sqlSelectCommand1.CommandText = "SELECT id, wxID, dateID, titleID, bodyID, linkID FROM blogEntry ORDER BY id DESC";
                                    this.sqlSelectCommand1.Connection = this.sqlConnect;
                                    //
                                    // ds1
                                    //
                                    this.ds1.DataSetName = "DS";
                                    this.ds1.Locale = new System.Globalization.CultureInfo("en-US");
                                    this.Load += new System.EventHandler(this.Page_Load);
                                    ((System.ComponentModel.ISupportInitialize)(this.ds1)).EndInit();

                        }
                        #endregion

                        private void linkAdmin_Click(object sender, System.EventArgs e)
                        {
                                    Server.Transfer("logOn.aspx",true);
                        }
            }
}

View 5 Replies View Related

Dynamic Queries With Data Reader Source Adapter

Apr 16, 2008

I am a business user trying to build an incremental ETL package with SSIS. I have a working prototype on SQL Server 2005 where I select the max(ID) from the last successful run and pass that value into a variable. Then, in my Data Flow step, I select an OLE DB source adapter and use this variable in a custom select statement.

Here's my challenge....the live data is actually in a Postgres DB so I have to use a Data Reader Source adapter. When I try to pass my variable to this adapter the job bombs out. Does anyone know how to dynamically update the query text inside a Data Reader source adapter using variables or otherwise?

View 3 Replies View Related

@@DBTS Correct Usage

Apr 11, 2006

Hi,

Suppose my Test table has a timestamp (rowversion) column, and I have the following code:

INSERT INTO Test ...

SELECT @@DBTS as 'TS'

Can I be sure the TS return column value contains the value of the timestamp column of the Test table? I suspect no in general (although it will work "most of the time"), but I would like a confirmation.

If I am correct, and there may be a mismatch, is there any clever way of getting the exact value, except from doing a SELECT WHERE ...?

Thanks.

View 3 Replies View Related

Query Results SSIS Data Flow Source Adapter

Jun 1, 2006

Hi,
Quick question on how SSIS handles queries from Data Source in a Data Flow. I noticed that when I run a particular query from Query Analyzer it takes forever. But, when I run the same query in SSIS data source in a data flow. The query results are immediate.

The query plan is already cached in SQL.

Is this just something which I am seeing incorrect or is there some bit of optimization in there in SSIS. As per my understanding SSIS does not optimize the source query.

Thanks,
Gaurav

View 3 Replies View Related

Data Access :: MS Access ADODB Connection To Stored Procedure - Cannot Retrieve Data

Sep 22, 2015

I'm trying to re-write my database to de-couple the interface (MS Access) from the SQL Backend. As a result, I'm going to write a number of Stored Procedures to replace the MS Access code. My first attempt worked on a small sample, however, trying to move this on to a real table hasn't worked (I've amended the SP and code to try and get it to work on 2 fields, rather than the full 20 plus).It works in SQL Management console (supply a Client ID, it returns all the client details), but does not return anything (recordset closed) when trying to access via VBA code.The Stored procedure is:-

USE [VMSProd]
GO
/****** Object: StoredProcedure [Clients].[vms_Get_Specified_Client] Script Date: 22/09/2015 16:29:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[code]....

View 4 Replies View Related

An Error Has Occurred During Report Processing. A Data Source Instance Has Not Been Supplied For The Data Source DetailDS_get_o

Mar 13, 2008

hi ,

i am trying for a drill through report (rdlc)

ihave written the following code in drill through event of reportviewer, whenever i click on the first report iam getting the error like

An error has occurred during report processing.

A data source instance has no
t been supplied for the data source "DetailDS_get_orderdetail".

the code is

using System;

using System.Data;

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;

using System.Web.UI.HtmlControls;

//using Microsoft.ApplicationBlocks.Data;

using Microsoft.Reporting.WebForms;

using DAC;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

ReportViewer1.Visible = false;

}

protected void Button1_Click(object sender, EventArgs e)

{

DAC.clsReportsWoman obj = new clsReportsWoman();

DataSet ds = new DataSet();

ds = obj.get_order();

ReportViewer1.LocalReport.DataSources.Clear();

ReportDataSource reds = new ReportDataSource("DataSet1_get_order", ds.Tables[0]);

ReportViewer1.LocalReport.DataSources.Add(reds);

ReportViewer1.LocalReport.ReportPath = "C:/Documents and Settings/km63096/My Documents/Visual Studio 2005/WebSites/drillthrurep/Report.rdlc";

ReportViewer1.LocalReport.Refresh();

ReportViewer1.Visible = true;

}

protected void ReportViewer1_Drillthrough(object sender, DrillthroughEventArgs e)

{

DAC.clsReportsWoman obj = new clsReportsWoman();

ReportParameterInfoCollection DrillThroughValues =

e.Report.GetParameters();

foreach (ReportParameterInfo d in DrillThroughValues)

{

Label1.Text = d.Values[0].ToString().Trim();

}

LocalReport localreport = (LocalReport)e.Report;

string order_id = Label1.Text;

DataSet ds = new DataSet();

ds = obj.get_orderdetail(order_id);

ReportViewer1.LocalReport.DataSources.Clear();

ReportDataSource reds = new ReportDataSource("DetailDS_get_orderdetail", ds.Tables[0]);

ReportViewer1.LocalReport.DataSources.Add(reds);

ReportViewer1.LocalReport.ReportPath = Server.MapPath(@"Reportlevel1.rdlc");

ReportViewer1.LocalReport.Refresh();

}

}

the code in method get_orderdetail(order_id) is

public DataSet get_orderdetail(string order_id)
{
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
cmd.Parameters.Add("@order_id", SqlDbType.VarChar, 50);
cmd.Parameters["@order_id"].Value = order_id;
ds = SQLHelper.ExecuteAdapter(cmd, CommandType.StoredProcedure, "dbo.get_orderdetail");
return (ds);
}pls help me.

View 1 Replies View Related

Transform To Remove Rows From Data Set A That Match Rows In Data Set B On A Given Key?

Jun 28, 2006

Hi,

I have a common requirement in numerous SSIS processes to take my main input data set and to remove all rows from it that match a second input data set on a given key and output this as the main output. I also want to output (as a second output) all the rows from the main input data set that did match on the given key. However, I don't want to merge in data from the second input, nor am I interested in rows from the second input data set that have no match in the main input.

E.g. If I have the following data:

Main input:
Key Name
--- ----
1 Steve
2 Jamie
3 Donald

Second Input
Key DontCareAboutThisField1
--- -----------------------
1 ...
3 ...
4 ...

Then I would like the following output:

Main Output
Key Name
--- ----
2 Jamie

Second Output
Key Name
--- ----
1 Steve
3 Donald

Can I do this with a standard transform, or will I have to write my own? Any help on this would be greatly appreciated!

Thanks in advance,

Lawrie

View 1 Replies View Related

Data Access :: Management Studio To Access Data On Laptop?

Jun 30, 2015

I have a client who has SSMS installed on her laptop. She is able to connect to the SQL server via SSMS in the office and query data on the server.

She needs to be out of site often and doesn't have internet access. She asks if the data tables can be "backed up" or saved on her laptop, so she can look at them without worrying connecting to the server. I am not sure if this can be achieved, as SSMS is built for accessing a server, not a desktop. Myself never have this need. If I really need it, I would go to Microsoft Access and create an ODBC connection to the datatables. But this client thinks that Microsoft Access is beneath her.

View 4 Replies View Related

Data Access :: Data Import From Password Protected Access MDB

Jul 20, 2015

HowTo: Import data to MS SQL 2008 from password protected Access DB ?

View 2 Replies View Related

Getting Correct Data From A Table

Dec 20, 2004

Hey,

I have a temp table with a column of all the same number. When I run my select statement I need it to bring back all the other data and just the single instance of the column with all the same numbers. How would I do this in my select statement?

For example, here is my table:

Here1One
There1Two
Up1Three
Down1Four
Back1Five

I need to bring back everything from column 1 and 3, but just the single instance of 1 from column 2 since it contains all the same number. All the data is collected in a single select statement.

Any suggestions?

Thanks

View 11 Replies View Related

Not Fething Correct Data ?

May 9, 2007

when i retreive the data from the client machine, it is not fething the latest data.

(The server is enterprise edition and the client machine we have sql server express)

The results seems to be flextuating. like some time (01.00 -12.00) it show correct data and some time (say 12.01 - 24.00 ) it does not

this issue happens for other objects (sp and fns )also created in database. appears and disappears alternatively

View 5 Replies View Related

Looping To Get Correct Data

May 30, 2007

I'm a newbie so I'll explain what I'm trying to achieve the best I can ...

I'd like to essentially loop through a SQL table to display the correct results. The workflow is the user query's the database and returns records (by property ID). In the return there are duplicate records being returned - in this case, two property owners returned with the same property ID.

How would I loop through the SQL statement in the application (code) to identify when the property id's are the same and display only one owner for that property?

Thanks!

View 3 Replies View Related

How To Retrive Correct Data

Nov 26, 2007

Code Block
SELECT
tce.TimeCardID,
tce.TimeCardExpenseID,
tc.DateCreated,
e.LoginID,
e.FirstName + ' ' + e.LastName AS FullName,
tce.ExpenseAmount,
tce.ExpenseDescription,
op.ProjectName,
op.ProjectDescription,
ec.ExpenseCode
FROM OPS_TimeCards tc
JOIN OPS_Employees e
ON e.EmployeeID = tc.EmployeeID
JOIN OPS_TimeCardExpenses tce
ON tc.TimeCardID = tce.TimeCardID
Join OPS_Projects op
ON op.ProjectID = tce.ProjectID
Join OPS_ExpenseCodes ec
ON ec.ExpenseCodeID = tce.ExpenseCodeID
WHERE e.LoginID = 'jross'
ORDER BY tc.DateCreated DESC

this query returns me the correct data....but i need to tweak the query so it does not duplicate rows....My tce.TimeCardID is a PK in its table and so is TimeCardExpenseID...but the problem is U can have many TimeCardExpenseID's for one timecard so my results look like

TimeCardID TimeCardExpenseID
1 2
1 3
1 4

I want my query to return the "TimeCardID" but i just want that one ID to represent all the TimeCardExpenseID's...but i can not get it to work and have no clue....

so if i do Select * From TimeCardID = '1'

it should return

TimeCardExpenseID
2
3
4

Any help on how to get this done....

View 4 Replies View Related

XML Data Processing

Dec 30, 2005

Hello everyone. I need help regarding the following:Given the following table:CREATE TABLE T1 (C1 nvarchar(10), C2 money)INSERT INTO T1 VALUES ('A',1)INSERT INTO T1 VALUES ('B',2)INSERT INTO T1 VALUES ('C',3)let's say that i have this table in a local server and i want to uploadit to a remote server and in the remote server upload it to a databasethat contains the same table.the uploading part can be done by another application in the remoteserver, but i want i need is a way to transfer the data at the fastestpossible way.what steps do i need to follow?tia,Rey Guerrero

View 10 Replies View Related

Data Mining :: How To Reduce Data Mining Processing Time

Aug 4, 2015

With SASS Database i have created Data mining Structure Using Time series algorithm, while processing the SSAS db, Data mining taking long time to process, so how we can reduce processing time ???

View 2 Replies View Related

Return Correct Data For A Given Date?

Nov 15, 2012

I have two table one call Employee and the other table call Target Ratio. They are related via FK EmployeeID

tblEmployee
EmployeeID FirstName LastName
1 John Doe

tblTargetRatio
TargetRatioID EmployeeID EffectiveDate Ratio
1 1 1/1/2012 8
2 1 6/1/2012 5
3 1 9/1/2012 7

My question is how can I query tblTargetRatio table to return correct record for the following cases:

1 EmployeeID = 1 and Date = 03/12/2012 (Expecting Ratio = 8)
2 EmployeeID = 1 and Date = 10/10/2012 (Expecting Ratio = 7)
3 EmployeeID = 1 and Date = 08/12/2012 (Expecting Ratio = 5)

View 2 Replies View Related

SQL Server For Data Processing

Jul 23, 2005

I'm working on a system right now where I have a database (two,actually, but one is discarded halfway through), but it's createdand used as part of a process (reporting), rather than as theactual production data repository. I may be keeping the databasepermanantly, but it would be completely read-only; once theprocess is complete, the database will not change again. This hasme wanting to do a few things that are rather foreign to my usualexperience, and I don't know how many of them are supported.In several cases, I'm summarizing one table into another by severalfields, and then updating the original table with an ID for thesummary row each source row was summarized into (e.g., I summarizePlaceAndProductSummary into PlaceSummary, and then populatePlaceSummaryID in PlaceAndProductSummary). The update of thesource table is much faster if the summary table has a clusteredindex on the summarized fields, but all later access will be fasterif the clustered index is on the identity column. I've beenincluding an ORDER BY the summarized fields in the original insert,so the identity column is in the same order as the summarized fields,but I don't know of any way to take advantage of that in theindexing declarations.As another approach to the above situation, if I change theclustered index on a table, and the rows happen to be in thesame order by both indexes, will the table still get rebuilt?I will never do a roll-back in the process; if an action fails, Iwant to raise an error and halt (and I haven't lost any data).Is there any way to completely turn off logging?Will I gain anything by marking the database as single-user?Any indexes that I am not using while I populate the tables, I'madding at the end with FillFactor 100, to keep any slack out.Is there a way to remove all the slack from everything else, atthe end of the process? During a backup operation would be fine.Thanks,Bill

View 2 Replies View Related

Processing XML Data From A Table

Sep 8, 2006

We have an XML column in a SQL Server 2005 table. Each row of this table contains one XML document.

I want to shred values from the XML documents and process these within a Data Flow. I want the Data Flow to execute once across a record set comprised of all of the XML documents.

I can shred the XML using a For-Each loop and XML Task. I'm kinda stuck on how I then get the data from variables into a Recordset or similar so that I can process this within single iteration of a Data Flow.

Or - is my approach incorrect? I seem to be building a verbose and clunky solution to this problem. I know I could accomplish the same in a pretty simple SQL statement using .value on the XML column... am I missing something? Is a SQL query just better suited to this problem?

Any help much appreciated.

James

View 1 Replies View Related

Import Data From Excel With Correct Format

Nov 30, 2005

Hi, I have one column of data which is 15.678 but in the excel, i format it to 15.68 ( two decimal place, so in excel i should see 15.68), when i am trying to import the data from excel to sql server by using odbc connection, it still getting 15.678, how can i get the data from 15.678 to 15.68 ( what i see is wat i get). Thanks for help.

View 7 Replies View Related

Problem Grabbing Correct Data On A Join

Apr 21, 2008

Hi folks,

I'm a little new to SQL programming but I'm learning. :)

I need to do a join on a table where I exclude records that have more than one urn (unique record number).

In the example table below I want to exclude both entries in the cr_urn column of 49074 & 49075.

cr_urn proc_urn crs_seq crp_seq crp_site

49073233311NULL
49074205111NULL
49074261512NULL
49075128011NULL
490752185121945
49076233311NULL
49077233311NULL
490781145121875

To get to this ->

cr_urn proc_urn crs_seq crp_seq crp_site

49073233311NULL
49076233311NULL
49077233311NULL
490781145121875

View 3 Replies View Related

Delete Rows With Duplicate Column Data But Unique Row Data

May 25, 2000

Hello,

This probably has been addressed before but I was unable to get the search to work properly on this site.
I am needing a script/way of deleting all rows from a DB with the exception of one record left for each row that has duplicate column data. Example :
Row 1
Field1 = 12345 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Row 2
Field1 = 12345 Field2 =zzzzzz Field 3=xxxxxx Field4=yyyyyy etc.
Row3
Field1 = 12345 Field2 =20202 Field 3=11111 Field4=zzzzz etc.
Row 4
Field1 = 54321 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Etc. Etc.

I want to be able to find the duplicates for Field1 and then delete all but 1 of those rows.( I don't care which one I keep just so only one is left.) The data in the other fields may or may not be unique.

I know how to find the duplicates it's just the deleting part I am having problems with. Any help would be much appreciated. Thanks,

Kerry

View 3 Replies View Related

Processing Data From Stored Procedure

Aug 22, 2001

I want to capture and process data inside of a stored procedure by executing another stored procedure. If proc1 calls proc2 and proc2 returns 1 or more rows, consisting of 2 or more columns, what is the best way to do this?

Currently, I know I'm returning 1 row of 3 columns, so I concatenate the data and either return it with an OUTPUT parameter or using the RETURN stmt.

TIA,
mike

View 1 Replies View Related

Log Errors, But Continue Processing Data

Jun 26, 2007

Do you have to set the Error Output on DF components to "Fail Component" in order to get the errors?

What I would LIKE to do is a combination of "Ignore Failure" and "Fail Component". You see, I am using the Logging feature in my package that creates the sysdtslog90 table in the SQL database. The errors that I am logging make sense and have enough information for my purposes.

The problem is that I would like to continue processing the data and not have it stop when a data error occurs. I REALLY do not want to Redirect Rows unless it is necessary for me to do what I am asking.

Using Ignore Failure on both the source text file and destination SQL table allows the "good" data to be inserted, but I cannot get any info on the columns in error. Conversely, if I choose to Fail component, I get the info on the columns in error, but only the data that was inserted before the error was encountered is inserted into the table.

Suggestions?

View 14 Replies View Related

Errors Processing Data From Oracle To SQL

Aug 7, 2006

hi here�s a new one.....
i�ve created a dts that is using an odbc source to connect to an oracle server, the conexion works just fine and i have no problems with it, then, i run this package from mi computer and the data transfer ends succesfully, when i upload it to my server in SQL 2005 and set it into a job.. i get errors like these:

Event Name: OnError
Message: Thread "WorkThread0" has exited with error code 0xC0047039.

Event Name: OnError
Message: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

Event Name: OnError
Message: The PrimeOutput method on component "table" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Event Name: OnError
Message: The component "faccomitecedidos" (1) was unable to process the data.

Event Name: OnError
Message: The "component "table" (1)" failed because error code 0x80131541 occurred, and the error row disposition on "output column "diasperm" (1707)" specifies failure on error. An error occurred on the specified object of the specified component.

why is this happening , any solutions or ideas? the data i want to extract comes from an sql command not a table
this is the query:

SELECT
FCNSS ,
FITIPOSOLICITUD ,
FIFOLIO ,
RTRIM(FCNOMBRE)||' '||RTRIM(FCAPPATERNO)||' '||RTRIM(FCAPMATERNO) AS NOMBREAFILIADO,
fdfinicta as FAFIL ,
fdFecCedido as FCED ,
(fdFecCedido-fdfinicta) AS diasperm ,
FNSALARIOACTUAL AS SALAFIL ,
(SELECT DISTINCT FISDI FROM gentec_own.AFILCEDIDOS WHERE
FIFOLIO = gentec_own.faccomite.FIFOLIO AND FITIPOSOLICITUD = gentec_own.faccomite.FITIPOSOLICITUD AND FCNSS = gentec_own.faccomite.FCNSS AND ROWNUM = 1) AS salCED ,
(SELECT DISTINCT (FISDI/48.60) FROM gentec_own.AFILCEDIDOS WHERE
FIFOLIO = gentec_own.faccomite.FIFOLIO AND FITIPOSOLICITUD = gentec_own.faccomite.FITIPOSOLICITUD AND FCNSS = gentec_own.faccomite.FCNSS AND ROWNUM = 1) AS CalSalCED ,
FCNUMPROMOTOR AS cod_promotor,
(SELECT RTRIM(FCNOMBRES)||' '||RTRIM(FCAPEPATERNO)||' '||RTRIM(FCAPEMATERNO)
FROM gentec_own.prommaestro where FCNUMPROMOTOR = gentec_own.faccomite.FCNUMPROMOTOR AND ROWNUM = 1) AS NOMPROMOTOR,
FICVEENTCED as aforeorig ,
FCAFORECEDIDO as aforeced ,
FNINGCOMT ,
FNCTOPROMOCION ,
FNCTOADMON ,
FNCONTRIBUCION ,
fcCanal as Canal ,
FCDIVISION as Division ,
FCREGION as Gerencia
FROM gentec_own.FACCOMITE
WHERE FCCEDIDO = 1
and (to_char(fdFecCedido,'yyyymmdd')>=to_char(sysdate-8,'yyyymmdd') and to_char(fdFecCedido,'yyyymmdd')< to_char(sysdate,'yyyymmdd'))
order by fdFecCedido;

please!!!1 somebody

View 4 Replies View Related

Data Processing Extension Not Visible

May 3, 2007

hello,

I have written a Custom Data Processing Extension for SSRS 2005.

The Report Server is in SharePoint integrated mode, this works perfect except for the extension.

When I want to set a DataSource in SharePoint (WSS 3.0), the extension is not in the ComboBox.

This works well on another server with the same extension.

The DLL file of that extension is copied in the reportserver bin folder.

I have added the references in the rsreportserver.config and rssrvpolicy.config (see below).

Code Snippetrsreportserver.config

<Data>

<Extension Name="SPSLISTS" Type="ICom.ReportingServices.SharepointListsExtension.Connection,

ICom.ReportingServices.SharepointListsExtension" />

<Data>

Code Snippet
rssrvpolicy.config
This code is located inside the CodeGroup with Name="SharePoint_Server_Strong_Name".

<CodeGroup class="UnionCodeGroup"

version="1"

PermissionSetName="FullTrust"

Name="SPSLISTS_CodeGroup"

Description="Code group for my SPS LISTS data processing extension">

<IMembershipCondition class="UrlMembershipCondition"

version="1"

Url="D:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServerinICom.ReportingServices.SharepointListsExtension.dll"

/>

</CodeGroup>

I already tried re-installing Reporting Services and the add-in for SharePoint but the problem remains.

Does anyone has an idea of what's wrong here?

Thanks in advance,

Tom

View 3 Replies View Related

WSS 3.0 Lists Data Processing Extension

Mar 12, 2007

Hello,

I'm writing a Data Processing Extension for SSRS 2005 that allows you to use a WSS 3.0 List as a dataset.
This works for one list per dataset. Now I am trying to extend the extension to allow multiple lists in a dataset because I want to join lists on a common field (INNER JOIN). The problem is that I can only return one DataTable to the Report Server with the DataReader. Does anyone know if it is possible to return multiple DataTables (without the join)?

I'm a student, working on this project. I know Visual Basic.net but only started last month with Data.

Thanks in advance

Tom

View 2 Replies View Related