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
ADVERTISEMENT
Feb 7, 2008
I have been struggling with the Adapter Fill issue for days.
I got an eeror "Invalid object name 'Weather'." Why? How to modify it?
Thanks
Code Snippet
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
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 My.Name.Space;
public partial class GetStrings : System.Web.UI.Page
{
getStrings g = new getStrings();
protected void Page_Load(object sender, EventArgs e)
{
string strConnectionString = ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString;
SqlConnection myConnection = new SqlConnection(strConnectionString);
DataSet myDataSet = new DataSet();
try
{
myConnection.Open();
// create a new DataSet
// create the data
GenerateDataSet(myDataSet, myConnection);
}
finally
{
myConnection.Close();
// bind each to table to a grid
GridView1.DataSource = myDataSet.Tables["Weather"];
}
// databind the page
GridView1.DataBind();}
void GenerateDataSet(DataSet dset, SqlConnection conn)
{
AddTable(dset);
FillTable(dset, conn);
}
void AddTable(DataSet dset)
{
// create the table
DataTable WeatherTable = new DataTable("Weather");
DataColumn WeatherID = WeatherTable.Columns.Add("State", typeof(string));
WeatherTable.Columns.Add("Division", typeof(String));
WeatherTable.Columns.Add("TEMP", typeof(string));
WeatherTable.Columns.Add("PCPN", typeof(string));
WeatherTable.Columns.Add("UL", typeof(string));
WeatherTable.Columns.Add("LL", typeof(string));
WeatherTable.Columns.Add("PCT", typeof(string));
WeatherTable.Columns.Add("POT", typeof(string));
WeatherTable.Columns.Add("RO", typeof(string));
WeatherTable.Columns.Add("MOIST_INDEX", typeof(string));
WeatherTable.Columns.Add("CF", typeof(string));
WeatherTable.Columns.Add("MNA", typeof(string));
WeatherTable.Columns.Add("DROUGHT_INDEX", typeof(string));
WeatherTable.Columns.Add("DROUGHT", typeof(string));
// set the column properties
WeatherTable.Columns["State"].MaxLength = 50;
WeatherTable.Columns["Division"].MaxLength = 50;
WeatherTable.Columns["TEMP"].MaxLength = 50;
WeatherTable.Columns["PCPN"].MaxLength = 50;
WeatherTable.Columns["UL"].MaxLength = 50;
WeatherTable.Columns["LL"].MaxLength = 50;
WeatherTable.Columns["PCT"].MaxLength = 50;
WeatherTable.Columns["POT"].MaxLength = 50;
WeatherTable.Columns["RO"].MaxLength = 50;
WeatherTable.Columns["MOIST_INDEX"].MaxLength = 50;
WeatherTable.Columns["CF"].MaxLength = 50;
WeatherTable.Columns["MNA"].MaxLength = 50;
WeatherTable.Columns["DROUGHT_INDEX"].MaxLength = 50;
WeatherTable.Columns["DROUGHT"].MaxLength = 50;
GetDataRow(WeatherTable, g.gets());
// add the table
dset.Tables.Add(WeatherTable);
}
void FillTable(DataSet dset, SqlConnection conn)
{
// create the Command and DataAdapter
SqlDataAdapter WeatherAdapter = new SqlDataAdapter();
SqlCommand WeatherCommand = new SqlCommand("SELECT State, Division,MOIST_INDEX,DROUGHT_INDEX FROM Weather", conn);
WeatherAdapter.SelectCommand = WeatherCommand;
// fill the DataTable
WeatherAdapter.Fill(dset,2,0,"Weather");//wrong here,
//WeatherAdapter.Fill(dset.Tables["Weather"]);
}
//assuming the first dimention is collumn and the second is row
public void GetDataRow(DataTable datatable, string[][] source)
{
DataRow dr;
int rows = source.GetLength(0);
int collumns = source[0].Length;
for (int i = 0; i < rows; i++)
{
dr = datatable.NewRow();
if (source[i] != null)
{
dr.ItemArray = (object[])source[i];
datatable.Rows.Add(dr);
}
}
datatable.AcceptChanges();
}
}
View 1 Replies
View Related
Sep 9, 2015
I have a table that is returning rows from a table query. It seems I have done it before but I cannot seem to get the right procedure to obtain the values. I will paste in the code below in which you will see my bad attempts at accomplishing what I need.
Dim uid As String
Dim pw As String
Dim em As String, fn, ln, mi As String
Dim par As String
Dim Field, n, j As Integer
Dim JJ As Integer
[code]...
View 3 Replies
View Related
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
Mar 7, 2007
I thought I'd post this quick problem and answer, as I couldn't find the answer when searching for it.
I tried to call a stored procedure on SQL Server 2000 using the System.Data.SqlClient objects, and was not expecting any unusual issues. However when I came to call the Fill method I received the error "Arithmetic overflow error converting expression to data type int."
My first checks were the obvious ones of verifying that I'd provided all the correct datatypes and had no unexpected null values, but I found nothing out of order. The problem turns out to be a difference on the maximum values for integers between C# and SQL Server 2000. Previously having hit issues with SQL Server integers requiring Long Integer types in VB6, I was aware that these are 32-bit integers, so I was passing in Int32 variables. The problem was that Int32.MaxValue is not a valid integer for SQL Server. Seeing as I was providing an abitrary upper value for records-per-page (to fetch all in one page), I was simply able to change this to Int16.MaxValue and will hit no further problems as this is also well beyond any expected range for this parameter.
If anyone can name off the top of their heads what value should be provided as a maximum integer for SQL Server 2000, this might be a useful addition, but otherwise I hope this spares others some hunting if they also experience this problem.
James Burton
View 1 Replies
View Related
Sep 1, 2015
I have several versions of SQL Server and have been using SQL 2008 on a regular basis due to this issue. Our SQL 2014 when I do the Import Data process, it opens up the dialog window, hit next, and the data source is currently defaulting to ".NET Framework Data Provider for IBM i" - when it does this it immediately errors out with:
"An error occurred which the SQL Server Integration Services Wizard was not prepared to handle.
Additional Information:
> Exception has been thrown by the target of an invocation (mscrolib)
>> Failed to find or load the registered .NET Framework Data Provider (System.Data)"
It immediately crashes/closes the Import/Export wizard with me unable to change the data source to what I need it to be.
My 2008 defaults to SQL Server Native Client 10.0 and does allow me to change to that same option (at which point it errors) but it does not close the wizard.
I need a way to either:
> Default the starting Data Source to be something else
> Fix whatever error is causing it to crash - I am at a loss as to what the error is looking for
> Not have the wizard crash whenever it defaults to this source.
Any of the above solutions would work fine - but at the moment I am unable to use the Import/Export wizard at all in SQL 2014.
View 3 Replies
View Related
Aug 2, 2000
I seem to be having a problem on all of my SQL servers. WHen I or a developer attmept to do a delete on a table i get a Log file for database is full. I truncate the log try again and get the same error. IT doesnt seem to matter how much is being deleted or how big the table is. THis is very strange and very frustrating.
Thanks
David
View 5 Replies
View Related
Jul 23, 2005
I'm trying to simply change a column definition from Null to Not Null. It'sa multi million row table. I've already checked to make sure there are nonulls for any rows and a default has been created for the column. My log isset to autogrow and as the alter column colname char(6) Not Null runs thelog begins to grow. If I use no check BOL say the optimizer won't considerthe change. How can I change the nullability of a column that currentlycontains no nulls without using up extreme amounts of log space?Danny
View 1 Replies
View Related
Aug 14, 2014
I'm moving data from one database to another (INSERT INTO ... SELECT ... FROM ....) and am encountering this error:
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.
My problem is that Line 6 is:
set @brn_pk = '0D4BDE66347C440F'
so that is obviously not the problem and my query has almost 200 columns. I can go through one by one and compare what column is int in my destination table and what is varchar in my source tables, but that could take quite a while. How I can work out what column is causing the problem?
View 3 Replies
View Related
May 2, 2007
Hi ! I have a textbox and a Search button. When the user inputs a value and press the button, i want a datagrid to be filled.
The following code runs:
Dim connect As New Data.SqlClient.SqlConnection( _
"Server=SrvnameSQLEXPRESS;Integrated Security=True; UID= ;password= ; database=dtbsname")
connect.Open()
Dim cmd As New SqlCommand
Dim valor As New SqlParameter("@valor", SqlDbType.VarChar, 50)
cmd.CommandText = "Ver_Contactos_Reducido"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = connect
cmd.Parameters.Add(valor)
cmd.Parameters("@valor").Value = texto
Dim adapter As New SqlDataAdapter(cmd)
Dim ds As New System.Data.DataSet()
adapter.Fill(ds)
GridViewContactos.DataSource = ds
GridViewContactos.DataBind()
connect.Close()
I drag a datagrid on the page and only changed its Id.
Into the SQL database the stored procedure is the following:
ALTER PROCEDURE [dbo].[Ver_Contactos_Reducido]
(@Valor VARCHAR(100))
AS
BEGIN
SET NOCOUNT ON;
SELECT NombreRazonSocial, Nombre, Apellido,TelefonoLaboral,
Interno, TelefonoCelular, Email1, Organizacion
FROM CONTACTOS
WHERE NombreRazonSocial = @Valor OR Nombre = @Valor OR Apellido = @Valor OR TelefonoLaboral = @Valor OR Interno = @Valor OR
TelefonoCelular =@Valor OR Email1 = @Valor OR Organizacion= @Valor
END
When i run the page i can't see the datagrid, and after i enter a text and press the button, nothing happens. What am i doing wrong??
Thks!!
View 2 Replies
View Related
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
Jul 8, 2013
I am working on a school project and have come up against a bit of a sticking point. I am supposed to be creating a very basic OMS, the teacher themselves have said they do not know how to do this (in previous years it has all be done via Access) but apparently I am a lucky one to be doing it in SQL this year.
So I have 2 tables for products in the system
products
+-----------+------------+
|productid |productname |
|Int |varchar(50) |
+-----------+------------+
productdetail
+---------+----------+------------+------+------+
|detailId |productid |description |price |stock |
|Int |Int |Text |Money |Int |
| |FK_From_ | | | |
| |productid_| | | |
| |products | | | |
+---------+----------+------------+------+------+
One of the user requirements of the OMS is to fill a data grid with product name and the product details which I have the query for or rather I have created a view for, which is then queried from a stored procedure.
CREATE VIEW [dbo].[v_stock]
AS SELECT tab_products.productname, tab_productdetails.description, tab_productdetails.image, tab_productdetails.price, tab_productdetails.stock
FROM tab_productdetails INNER JOIN
tab_products ON tab_productdetails.productid = tab_products.productid
The problem I am having is then returning the data from this query into a data grid, I think the reason is because when I attached the stored procedure to a table and then call that procedure via the table adapter there is a mismatch of the schema - specifically the table it is attached to does not contain the column "productName".
I am thinking I need to create a temporary table to fill the data grid with - however, I am not sure how I would create a temporary table.
Is there something I am missing or not done correctly. As far as I can tell the queries work as when I preview them they produce the expected results.
View 1 Replies
View Related
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
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
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
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
Oct 28, 2006
Hello guys! I am relavtively new to ASP.NET programming ang was just starting out on my first project. I am using ASP.NET2.0 technology by using Visual Web Developer 2005 Express Edition and of course with SQL 2005 Express Edition. I would like to develop a database for our IP addresses, so one field of my table in a SQL data is the field for IP addresses.I would like to write a program wherein after clicking the button, that field will be automatically filled up with IP addresses (e.g, from 192.168.0.0 to 192.168.0.255).How do I accomplish this kind of dynamic filling up of fields? Thanks a lot!
View 1 Replies
View Related
Jul 14, 2014
I am working with some data that is not so clean. I have some rows that are Very close to being a duplicate row. I need to take all the rows with the same SKU number and fill in the missing data for the Price, Cost or the SalePrice, what ever may be missing. The data is set like @Diamonds MissingData and I need the row to look like @DiamondsWithData table. I can remove the duplicate values once I get the missing data filled in.
DECLARE @DiamondsMissingData TABLE
(
SKU varchar(20)
,VendorNumber varchar(20)
,VendorSKU varchar(20)
,GradingReportType varchar(20)
,GradingReportNumber bigint
[code]....
View 3 Replies
View Related
Jan 30, 2008
I want to fill text box using sqldatasource ...
how can i do that ??
thanx in advace ..
View 10 Replies
View Related
Aug 3, 2002
Hi All,
By mistake i delete database from sql server. Due to which the the mdf and ldf file of the datbase are removed from my system. Then i used file recover utility and retrived my mdf and ldf files.But now i am not able to attach those files into databse. These mdf and ldf files has been corrupted. Now how can i recover my data from those mdf and ldf files.Please answer me if you have any solution on this problem.
Regards,
Satish Rotella
View 2 Replies
View Related
Jun 11, 2007
Hi,
I was wondering if one could manage the color of the data series in line and bar charts much like what you could do in Excel?
Even changing the default order of the colors would be nice, just so different charts actually look...well, different.
Thanks!
View 2 Replies
View Related
May 8, 2015
I have a small question here is below how my data look
K_IDÂ Â Â Â Â ColAÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â ColBÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
ColCÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â ColD
1 Â Â Â Â Â Â Â Â Â Â X12134Â Â Â Â Â Â Â Â Â Â Â Â Â ABCDÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
'Â Â Â Â Â 'Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 1/1/2015
2 Â Â Â Â Â Â Â Â Â Â Â X12134Â Â Â Â Â Â Â Â Â Â Â Â Â ABCDÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
'Â Â Â Â Â 'Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 2/1/2015
3 Â Â Â Â Â Â Â Â Â Â X12134Â Â Â Â Â Â Â Â Â Â Â Â Â ABCDÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
DE02635 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 3/1/2015
I want the output like this
K_IDÂ Â Â Â Â Â ColAÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â ColBÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
ColCÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â ColD
1 Â Â Â Â Â Â Â X12134Â Â Â Â Â Â Â Â Â Â Â Â Â ABCDÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
DE02635 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 1/1/2015
2 Â Â Â Â Â Â Â Â X12134Â Â Â Â Â Â Â Â Â Â Â Â Â ABCDÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
DE02635 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 2/1/2015
3 Â Â Â Â Â Â Â Â Â X12134Â Â Â Â Â Â Â Â Â Â Â Â Â ABCDÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
DE02635 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 3/1/2015
I tried using Over by, Lag functions.. But some where i am missing the logic in achieving the Output...
View 28 Replies
View Related
Aug 19, 2005
Hi,
Some of the data files on our database got corrupted. Is there any tool to get just the data out of the data files and dump somewhere. We can manually rebuild the database using the schema scripts and loading the data. Appreciate any help. Any suggestions are welcome too.
Thanks.
View 4 Replies
View Related
Feb 19, 2008
Ok, so I can connect to the database without any errors, however im not sure about the syntax for filling a table. Heres what i have so far in the pageload. Like i said this all works with out any errors. Thanks in advance for the help.testDS = New DataSet()
testDataTable = New DataTable("Tbl")testDataTable.Columns.Add("username")
testDataTable.Columns.Add("datecompleted")testDataTable.Columns.Add("lastfive")
testDS.Tables.Add(testDataTable)Me.dgrdSearch0.DataSource = testDS.Tables("Tbl")
Dim Conn As Object = Server.CreateObject("ADODB.Connection")
Dim strConn = "DRIVER={SQL Server};SERVER=serverName;UID=userID;PWD=password;DATABASE=net"Dim DSNtest As String = strConn
Dim sql As String = "SELECT * FROM Tbl"
Conn.open(DSNtest)
Conn.close()
View 1 Replies
View Related
Aug 16, 2006
Matt writes "Greetings! Warning, I'm a rookie. I wrote a stored procedure to pull data in order to do a nightly export/import from one system to another. I have a batch file that looks like this:
bcp "exec WinSNAP_retrieveStudents '0607'" queryout c:WinSNAPData06.txt -c -U user -P password
Sometimes, the file works and I get perfectly formed data, with everything just as I've requested (mostly basic demographic information: names, addresses, etc.).
But, other times the output file contains nothing but garbage characters, like this:
剒乏†††††††††ठ䅍啎䱅†††䴉㐉㐷‰䕓䄠䅐䡃⁅剄
The file size looks right, but it contains nothing but characters like this from beginning to end. I can find no pattern as to why/when good data gets pulled versus the corrupt data. I can run the batch file one minute and get good data, and run it the next minute and it's all corrupt. We have the batch file scheduled late at night when no users are online, and I get the same results -- one day it works, the next it doesn't.
Forgive me if this is a well-documented issue -- my searches so far haven't turned up a thing!
Thanks much for any advice you can provide!!
Matt Smith
DeSoto County School District
Arcadia, FL"
View 1 Replies
View Related
Jul 20, 2005
Anyone knows what would cause this?When Enterprise Manager is used to open a tableRuntime error data provider or other service provided an efail status
View 1 Replies
View Related
Jan 9, 2015
I'm trying to move a log file of a database that is part of an availability group. I have been following steps from the article: [URL]
At first this worked fine for me in a test environment. When I tried it in a production environment the database on the secondary went into "Recovery Pending" state and I can't get it out.
I checked to ensure that the dB is looking in the right place for the log file, and it is. It just doesn't seem to actually use the new file. If I start and stop SQL service, the dB comes back up and is fine.
Here are the steps I'm going through and what is happening at each step:
--------------------------------------
:Connect DEVSQL --This is currently PRIMARY
USE[master]
GO
ALTER AVAILABILITY GROUP [DP-AG-DEV] MODIFY REPLICA ON N'DEVSQL' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = NO))
[Code] ....
All is good so far. Both the Primary and the Secondard have had their logical files changed, which has not taken affect yet because there has been no failover.
--Make SQL10 the PRIMARY
:Connect SQL10
ALTER AVAILABILITY GROUP [DP-AG-DEV] FAILOVER;
GO
SQL10 is now the Primary for this AG. And, as expected, the database [AG-Test] is in "Recovery Pending" because it is now looking for the log file in the new location. I need to move the file to the new location.
:Connect DEVSQL
--Enable XP_CMDSHELL
sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'xp_cmdshell',1
[code].....
This is where the script is failing, returning the error:
Msg 1468, Level 16, State 5, Line 5
The operation cannot be performed on database "AG-Test" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
Msg 5069, Level 16, State 1, Line 5
ALTER DATABASE statement failed.
I can not get the dB to recognize the log file at it's new location.
If I restart the SQL Service, it comes back fine, which seems to indicate to me that it is not a permission problem and confirms that the file is in the right place.
How do I force SQL to look for the log file again without restarting the service?
View 2 Replies
View Related
Apr 9, 2008
HI
I need help
how can i fill data in textboxes from sql databases but two different tables when i select a name that is inside a dropdownlist
my controls are as follows
<asp:DropDownList ID="ddl" runat="server" DataSourceID="SqlDataSource13" DataTextField="fullname" DataValueField="fullname">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource13" runat="server" ConnectionString="<%$ ConnectionStrings:NPI Employee MasterConnectionString2 %>"
SelectCommand="SELECT [FirstName]+' '+ [Surname] as fullname FROM [Employee] where CurrentEmployee_YN=1 order by FirstName "></asp:SqlDataSource><table bordercolor="#111111" cellpadding="0" cellspacing="0" style="width: 100%;
border-collapse: collapse; height: 32px; visibility: hidden;" id="table0">
<tr>
<td style="width: 159px; visibility: hidden;">
</td>
<td style="width: 170px">
</td>
<td bgcolor="#eeeddb" style="width: 20%; height: 25px">
<strong>
Order No:</strong></td>
<td bgcolor="#eeeddb" style="width: 26%; height: 25px">
<asp:Label ID="OrderNo" runat="server" Width="104px"></asp:Label></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 159px; height: 25px">
<strong>
Account No:</strong></td>
<td bgcolor="#eeeddb" style="width: 170px">
<asp:TextBox ID="AccountNo" runat="Server" MaxLength="10" Width="130px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ControlToValidate="AccountNo"
Display="Static" ErrorMessage="Enter Acc No." Text="*"></asp:RequiredFieldValidator></td>
<td bgcolor="#eeeddb" style="width: 20%; height: 25px">
<strong>
Today's Date:</strong></td>
<td>
<asp:Label ID="Label1" runat="server" Text="Label" Width="200px"></asp:Label></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 159px; height: 25px">
<strong>
Travel Consultant:</strong></td>
<td bgcolor="#eeeddb" style="width: 170px">
<asp:TextBox ID="Consultant" runat="Server" MaxLength="30" Width="128px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ControlToValidate="Consultant"
Display="Static" ErrorMessage="Enter Travel Consultant." Text="*"></asp:RequiredFieldValidator></td>
</tr>
</table>
<center>
</center>
<center>
</center><table bordercolor="#111111" cellpadding="0" cellspacing="0" style="width: 80%;
border-collapse: collapse; height: 32px; display: block; visibility: hidden;" id="table2">
<tr>
<td align="center" bgcolor="#ffcc33" colspan="3" style="width: 90%; height: 29px">
<font color="#000000" size="5">Enter Passenger(s) Details</font></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Surname:</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="Surname" runat="Server" MaxLength="30" Width="148px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="Surname" Display="Static" ErrorMessage="Enter Surname." Text="*"></asp:RequiredFieldValidator></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 20px">
<strong>
Name:</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 20px">
<asp:TextBox ID="Name" runat="Server" MaxLength="30" Width="148px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="Name"
Display="Static" ErrorMessage="Enter Name." Text="*"></asp:RequiredFieldValidator></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Initials:</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="Initials" runat="Server" MaxLength="5" Width="148px"></asp:TextBox>
</td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Title:</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:DropDownList ID="DropDownList1" runat="server" Width="156px">
<asp:ListItem></asp:ListItem>
<asp:ListItem Value="Mr"></asp:ListItem>
<asp:ListItem Value="Mrs"></asp:ListItem>
<asp:ListItem Value="Ms"></asp:ListItem>
<asp:ListItem Value="Dr"></asp:ListItem>
<asp:ListItem Value="Prof"></asp:ListItem>
<asp:ListItem Value="Min"></asp:ListItem>
<asp:ListItem Value="Other"></asp:ListItem>
</asp:DropDownList>
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="Dropdownlist1"
Display="Static" ErrorMessage="Select Title." Text="*" Width="20px"></asp:RequiredFieldValidator>
</td>
</tr>
<tr><td bgcolor="#eeeddb">
<strong>
Department</strong>
</td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="Department" runat="server"></asp:TextBox></td>
</tr>
<tr><td bgcolor="#eeeddb">
<strong>
Cost Centre</strong>
</td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="CostCentre" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Tel:</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<input id="Tel" runat="SERVER" maxlength="15" name="Tel" onkeypress="if((event.keyCode<48)|| (event.keyCode>57))event.returnValue=false"
style="width: 143px" type="text" />
</td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Fax:</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<input id="Fax" runat="SERVER" maxlength="15" name="Fax" onkeypress="if((event.keyCode<48)|| (event.keyCode>57))event.returnValue=false"
style="width: 143px" type="text" />
</td>
</tr>
</table>
cost centre and department are from cost table
and the rest are from employee table
View 10 Replies
View Related
Jul 25, 2006
W2k3 server, SQL 2005.
@@version = Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Standard Edition on Windows NT 5.2
(Build 3790: Service Pack 1)
I have my first SSIS package almost working, but I'm having an odd problem and can't find any information to help resolve it.
I'm importing from a flat file (csv) to an existing table (append). I've got a Derived Column transformation in the middle to do some data cleanup. It's all working except for one little problem...
One of the transformations is 'REPLACE([Column 3],"^","; ")', output to a new column. (The input file has a field that uses carets as delimiters between an unknown number of items; I'm changing that to semicolons for easier reading.) Not all rows have data in this column, some will have one item, some will have multiple items.
The REPLACE works except that it fills in repeated data for all the blank rows.
Example:
Incoming data is:
1 Smith,Jane^Jones,Jane
2 Brown,John
3
4 Adams,James^Adams,Jim
5
6 White,Debra
Data inserted into the table is:
1 Smith,Jane; Jones,Jane
2 Brown,John
3 Brown,John
4 Adams,James; Adams,Jim
5 Adams,James; Adams,Jim
6 White,Debra
I've tried to use a Conditional to skip the empty rows, but I can't get that working at all (get syntax errors no matter what I put in).
Any suggestions on how to fix this would be most appreciated!
Thank you.
View 5 Replies
View Related
May 25, 2006
hi
I am new at MSSQL 2000 DBA thing. and trying to learn more about analysis service/data warehouse/data mining. so is any expert out there can Recommend some good books or web link article to read? Thanks
View 1 Replies
View Related
Feb 17, 2014
Background: In my current company the business users maintain a huge quantity of master data using excel. Then a series of SSIS jobs are edited and manually executed.
Goal: the challenge is to replace this process using MDS. One of the requested features is the possibility for the users to edit or insert new master data using the Web UI or the Excel Add-in and when they are done perform a merge of the master data in the target, in this case in the reporting DB.
The perfect solution for me is something like trigger the execution of a SSIS package to export the data from the subscription views to the reporting DB after the business rules are apply to a specific entity.
View 1 Replies
View Related
Jan 19, 2007
It's well known issue, that one can't use any dataset fields in a
report header/footer directly. One of the approach is to create
query-based parameter that basically equals
=First(Fields!@FieldName@.Value, "@DataSetName@") and use that
parameter value instead. But it doesn't work in my case!
My report displays some entity description and is parametrized with
EntityID param. Its header contains entity name that, according to the
approach, is queried from the data source through the EntityName
report parameter. There's important issue: the report is displayed in
ReportViewer control, that is embedded into my application and entity
ID parameter isn't ser by user in ReportViewer parameters area. Its
default value is changed by the application with SetReportParameters()
web method every time a user wants to view the report according to the
entity the user is exploring in the application. But after the report
has been rendered, its header always contains not actual (outdated)
entity name. Nevertheless, the report body contains actual data
(including entity name). If I alter entity ID parameter in ReportViewer
or in web-based Report Manager and refresh report, header displays
correct entity name.
What's wrong in the workflow described?
View 3 Replies
View Related
Sep 29, 2015
I cannot find the problem with this function.
ALTER function [Event].[DetermineTrackTime](@TrialID varchar(max)) returns int as
begin
Declare @ret int;
Declare @EnterVolumeTime int;
Declare @ExitVolumeTime int;
Declare @StartTrackTime int;
[code]....
I am getting the following error on line 75:
Select statements included within a function cannot return data to a client.
This is happening when declaring TrackUpdateCursor
The compiler has no problem with the VolumeTimesCursor. What is causing this and what can I do about it?
View 20 Replies
View Related