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.
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)) {
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.
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!!
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.
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.
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!
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"
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
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.
I'm using Script Component to load data into Oracle DB due to the poor performance issue. Now, I found it will missing some data during the transmission. Please see the screenshot below:
I need to see inside a SSIS 2012 project a new SSIS installed component, but in the SSDT 2010 I cannot see the SSIS Data Flow Items tab for adding data source/data destination respect to the choose toolbox items pane.
I've got a report that is using a cube as a data source and I can't get the report to show all the data. Only data at the lowest level of the cube is displayed. The problem is that most of the data I'm concerned with is at higher levels. There's no problem with the MDX. I get the correct results when I run the query.
I'm using a table to show the results. I've also tried a matrix, but I get the same results. I'm using SSRS 2005 and SSAS 2000.
Anyone have experience with this? Am I missing something simple?
The project is a C/S data analysis system built with .Net 2.0 in windows environment, OS: Microsoft Windows 2003 R2 standard Edition Service Pack2, Database used in this project is: Sql server 2005. As a data analysis system, we need to load large amount of data from file to database, we do it by create a dts package and then do data loading by execute "m_Package.Execute(null, variables, m_PackageEvents, null, null)".
The problem is, we fount that DTS miss some data randomly sometimes, we can't find the rule till now. for example we've data as follows in data file, all data field splited by '|' 11234|26341|2007-09-03 00:00|0|0|0.0|0|0.011470833793282509|1|0.045497223734855652|0|0|1|0|3|2929|13130|43|0|2|0|0|40|1|0|0|0|0|0|1||0|0|3|0|0|0|0|0||0|3|0|0|43|43|0|41270|0|3|0|0|10|3|0|0|0|0|0||0|1912|0|0|3|0|0|0|0|0|0|0|3|0|0|5|0|40|0|9|0|0|0|0|0|0|0|0|29|1|1|24|24.0|16|16.0|0|0|0.0|0|0|24|23.980693817138672|0|0.0|0|0.0|0|0.0|0|0.0|11|2.0764460563659668|43|2|0|0|30|11|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|3|3|0|0|0|0|0|0|0|0|0|6|0|0|0|0|0|6|0|0|45|1|0|0|0|2|42|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|2|0|0|0|2|0|0|0|0|0|0|51|47|85|0|0|||||||||||||||||||||||||||||||||||||||||||||||||||0|0|0|0|0|0|0|0|0|0|0|0|0|||||||||||||0|0|0|0|0|97.117401123046875|0|0|83|57|||0.011738888919353485|0|1|0.065955556929111481|0|4|||0.00026658669230528176|1|0.00014440112863667309|1|68|53|12|2|1|2.0562667846679688|10|94|2|0|0|30|11|47|4|13902|7024|6878|18|85|4.9072666168212891|5|0.0|0|0.0|0|0.0|0|0.0|0|358|6448|6324|0|0|0|0||0||462|967|0|41|39|2|0|0|0|1|0|0|0|0|0|0|0|0|3|0|0|3|0|0|0|0|0|0|0|0|0|3|0|3|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0.0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|46|0|1|0|1|37|0|0|46|0|1|0|1|37|0|0|0|0|0|0|0|0|0.0|0|0|6|4|2|0|0|2|1|0|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0.0|0|1|0.012290795333683491|0|44|44.0|0|0.0|0|0|0|30|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|2|0|2|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|2|1|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|1|1|0|0|0|0|0|0|0|0|0|0|0|0|27|0|0|2112|411|411|45|437|2|0|2|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|4|0|4|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|1|0|1|0|0|0|0|0|0|0|0|0|0|0|6|6|0|3|2|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|5|5.0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|600|600|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|6|0|0|0|0|0|0|6|0|9|1|2|2|3|0|1|0|0|0|0|0|0|0|0|0|0|0|13|3|2|5|1|1|1|0|0|0|102|0|1|1|0|0|0|3|3|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0||||||0|0|0|0|0|0|0|0|0|0|0|0|0|0|0||||||0|0|0|0|0|0|0|0|0|0|0||0|0|0|0|0|0|0|0|0||||||||||0|0|0|0|0|0|0||||||||||0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0||0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0.0|46.0|46|0.0|0|0.0|0|0.011469460092484951|1|0.0|0|0.0|0|3|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0.0|0|0.0|0|0|0|0|0|0|0|0|0|0|0|0|0|||0|100.0|100.0|0|1|0|1|0|0|0.02481505274772644|1|0.014951236546039581|1|0|0|0|0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||||||||||||||||||||||||||||||||||||0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|||0|||||||||||||||||||||||||||||||||||||||||||||||||||0|0|0|0|0|0|0|0|0|4695.5556640625|42260|7126.66650390625|62040|||||||||||||||||||||||||||||||||||||||||||||||||||||||0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0||||||||||0|0||||||||||
We found that some of the data field become 'null' after the load action finished, if we load the same data again, problem disappeared, we can't 100% reproduce this issue each time, we don't know why, Anybody here can help us to solve this issue or give us some clue?
Hello all, I am using the Import Wizard to pull in data from an Excel spreadsheet. One column in particular SQL Server sees as a float data type but it contains varchar data. So I change this in the wizard but some of these values are missing when I select * from Sheet1$ in SQL Server 2005. Any ideas why this would happen? I have formatted the particular column as text in Excel.
Using Business Inteligence I have created a report that runs off a stored procedure. This report has three parameters. For this example we will say the parameters are A, B and C. parameters B and C can be passed a NULL. The Stored procedure when run on the SQL Server 2005 runs fine and gives me the expected data. I then created a report based off this Stored procedure. If I run the report and pass it parameter A and either Parameter B or C it works fine. If I run the report and only pass it parameter A then I do not get any data. I know the Stored procedure works because as I saind when I run it on the Server it runs fine. So the problem must be in the Report somewher, but I can't figure out where. I have other reports that use stored procedure and some with parameters that I pass a null value to and they work fine. But this one report is not pulling the data.
Has anybody heard of this before or have any ideas?
I found a big problem. When we do a report it seems to be missing records. I am sure they should not be getting filtered out, given the filter in the report. I ran profiler on the SQL box it was pulling the data from, and it seems that they are all there. If I sort by the ID number of the records, then find the place it should be several are missing. But If I change the filter so that it filters everything but those records they show up. How could adding a filter cause more records to show up?
I faced a problem. I found all data/records of tables in a few databases were missing yesterday. I don't know what had happened. Can anyone tell me why? Can anyone tell me how to trace the root of the problem. Any log files I can trace? I fear it will happen again. My server is sql2000 and run on win server 2000.
I have a sql 2000 database in which reports are generated on a monthly basis from the data inside on of my tables. The reports have been working fine, until some of the rows seemed to have disappered!
I know the data use to be in the table, since it is showing on the old reports, however, when I try to pull that same data, it is not in the database at all.
Does anyone have any ideas on what could have caused this or how I can resolve??
I have a billing database with patient names in it. I received a tab delimited file from insurance plan of our roster of assigned patients.
I now want to compare the insurance roster to our database to see who is missing.
The roster is layed out like this (info jumbled to protect privacy): Eligibility List Sample Last Name First Name Date of Birth Gender Insured ID VW Acct # ALLEN CARRIE A4/16/1939FDH36664A572576-02 BAKER AMBER S11/24/1956FFXI2824C596439-02 BARKLOWLOREN R12/15/1956MKVF0092A588878-01 BRENNANPATRICIA A 1/14/1959FFXI8763A549675-02 BROWN MARTHA E8/14/1967FBD65508A366963-02 CALDWELL MICHAEL V 12/19/1969MLR500N2J595087-01 CLARK CYNTHIA A4/24/1971FVO600M8O596011-02 DEMPSTER SCOTT A 2/21/1976MCC85242A573371-01 DUNNE ANNETTE M10/26/1976FAE88375D598423-02 DUNNE CHRISTOPHER M 8/1/2021MBV81536A598423-01
I have loaded the text into an Excel Spreadsheet to work with it.
I was able to query our patient profile data base to get people with this insurance plan...but of course the data is never an ideal match.
For instance, some of the roster patients above have Middle Initials Concatenated to the First Name. In my database it is a mixed bag of missing initials, initials concatenated to first name or initials in separate Middle field. Thus a strict match on name is not going to work.
Date of Birth should hopefully be valid between both data sources.
Probably the best source of data to validate on would be the VW Acct# as I trust this to be the same in both sets of data. However in my patient data base it is buried in a note field preceded by a "Vital Works ID: " and then the number 602659-02. Generally it is the first part of the note field, but there could be additional notes preceding or trailing this Vital Works ID info.
An example of the query I was able to pull from the patient data base is as follows:
LastFirstMiddleDate of BirthGenderNotes ClarkLawrence J9/7/1955MVital Works ID: 7575-01 ClarkKayleeann NULL1/3/1955FVital Works ID: 7575-02 ColeCodyNULL8/19/1948FVital Works ID: 8771-02 snt ref req to ohms for impact appt tbs Sent ref req back to ohms for Impact-DX. CreaseyWadeL7/9/1988FVital Works ID: 602659-02 KennyRoyJ2/27/1953FVital Works ID: 602679-02 UttJannieC4/11/1984MVital Works ID: 602715-01 WestAliciaG9/9/1992MVital Works ID: 602736-02 WrightMinnieO2/17/1991MVital Works ID: 602736-03 YankeeDonald E10/27/1996MVital Works ID: 602762-03 YankeeStephana A4/4/2001FVital Works ID: 602762-04
How could I now construct a query that would tell me what patients were in the eligibility roster that didnt have a match in the patient database?
I would like to then save that to Excel or somewhere that I could print it out from so I can have someone up date the database.
Hi all, I found problem with my database and was wondering if anyone here could shed some light on the issue.
I have two tables, Absences and AbsenceDates. The first one records the absence of an employee and the second one records a record for each day of the occurance. I do a full select on the second table and I see primary keys that do NOT exist in the select of the second table. so I dug further and here is what I found.
Select * from Absences (rowcount in Query Analyser is: 20883) Select * from Absences Order By AbsenceID Desc (rowcount is 443)
The second select contains the data that I am missing in the first select. So, I called a friend and they said to run DBCC CHECKDB and I did. The data came back as follows...
DBCC results for 'Absences '. There are 21337 rows in 243 pages for object 'Absences'. CHECKDB found 0 allocation errors and 0 consistency errors in database 'EmployeeAbsenteeism'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
now if you add up the rows that the other two selects return it comes to 21326, not 21337. I am assuming that the value that DBCC gets is from sysobjects and that some sort of update would need to be run for it be accurate. This I don't care about too much, what I really need is for my main select statement to return ALL of the data, not just what it feels like returning.
My experience is with programming mainly (6 years in .net) and not DBA, so any help would be greatly appreciated.
Cheers, Brent
Sorry, this was supposed to go to data corruption forum... reposting..
Hi all, I found problem with my database and was wondering if anyone here could shed some light on the issue.
I have two tables, Absences and AbsenceDates. The first one records the absence of an employee and the second one records a record for each day of the occurance. I do a full select on the second table and I see primary keys that do NOT exist in the select of the second table. so I dug further and here is what I found.
Select * from Absences (rowcount in Query Analyser is: 20883) Select * from Absences Order By AbsenceID Desc (rowcount is 443)
The second select contains the data that I am missing in the first select. So, I called a friend and they said to run DBCC CHECKDB and I did. The data came back as follows...
DBCC results for 'Absences'. There are 21337 rows in 243 pages for object 'Absences'. CHECKDB found 0 allocation errors and 0 consistency errors in database 'EmployeeAbsenteeism'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
now if you add up the rows that the other two selects return it comes to 21326, not 21337. I am assuming that the value that DBCC gets is from sysobjects and that some sort of update would need to be run for it be accurate. This I don't care about too much, what I really need is for my main select statement to return ALL of the data, not just what it feels like returning.
My experience is with programming mainly (6 years in .net) and not DBA, so any help would be greatly appreciated.
Cheers, Brent
**** UPDATE ***** I tried running the following sql select * Into Absences1 From Absences the results were: (21337 row(s) affected)
now, I did a select * from Absences1 returned 20883 rows.. :(
****** UPDATE ******* I ended up fixing the following way. Select * Into AbsencesFix WHERE PrimaryKey < 110577 GO INSERT INTO AbsencesFix ({allfields}) (Select {allfields} FROM Absences WHERE PrimaryKey > 110577) Go sp_rename 'Absences', 'Absences_oldCorrupt' GO sp_rename 'AbsencesFix', 'Absences' Go
This little script did the trick, I can now select all the data in the table.
Hope the fix that was given to me by another DBA friend of mine can help someone out who has a similar problem.
After loading the BCP files that are created during the trigger/reporting events I've noticed that the data in the table is missingrecords. I've also noticed that the missing records (records in thetable but not in the BCP out files) seem to occur in contiguousblocks. Since the complete set of records exists in the table, Iassume this points to an issue in the way the TableUpdate script/Triggers interact with the system. But i tried to take out the bcpprocedure, do test on trigger, then no data missing, So, I think theproblem is still on bcp part. Could you help me with that?CREATE TABLE [EventUpdate] ([id] [int] NOT NULL ,[eventid] [int] NOT NULL ,[sequenceid] [int] NOT NULL ,[UpdatePass] [int] NULL) ON [PRIMARY]GOcreate trigger trgEventUpdate on EventLog For Insert,Update asinsert into EventUpdate (id,eventid,sequenceid) select ins.id,ins.eventid,ins.sequenceid from inserted insbcp script:bcp "select a.* from w..eventlog a, w..eventupdate b wherea.eventid=b.eventid and a.sequenceid=b.sequenceid and b.eventid<>-1and b.sequenceid<>-1 and b.updatepass=1" queryout 30sec-%TFN_NOW%.wrk -U <userwithaccess-P <password-S doserver -f EventLog.fmtThanks in advance for your reply!
I wanted to demonstrate how SSIS can easily read an Excel file into a database. I started up the wizard (dtswizard) looked and looked by couldn't find "Excel" in the list of datasources. Next I started up the SSIS IDE, found an Excel Destination, and created a flow that errored out on the copy to Excel step.
>>> SSIS package "Package.dtsx" starting. Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning. Error: 0xC0202009 at Package, Connection manager "Excel Connection Manager": An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". Error: 0xC020801C at Data Flow Task, Excel Destination [666]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component "Excel Destination" (666) failed validation and returned error code 0xC020801C. Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation. Error: 0xC0024107 at Data Flow Task: There were errors during task validation. SSIS package "Package.dtsx" finished: Failure.
>>>
I don't understand that message. I suspect the problem is that Excel is NOT installed on the box where I'm running the package. Does that seem right to you? Also, if Excel is needed on the box, how can I develop such a package on a laptop (in the airport) that doesn't have Excel?
Confused, but good!
Barkingdog
P.S. As the saying goes, "I never had this much trrouble importingexporting an Excel spreadsheet in sql 2000 DTS."
I am working with a database containing time series data. In many, cases there is missing data. For example, while there might be a value for 2001-01-01T23:00:00, there is none for 2001-01-01T23:0100 (one minute later). I would like to replace the missing data with data from the previous record (if the previous record is the same date). Is that possible with T-SQL?
I am using Transactional Replication as a backup for our main database. The subscriber database however is smaller than the original database. Is there something that I'm missing? Is the subscriber missing data?
Here is an issue that has me stumped for the past few days. I have atable called MerchTran. Among various columns, the relevant columns forthis issue are:FileDate datetime, SourceTable varchar(25)SQL statement:SELECT DISTINCTFileDate, SourceTableFROMMerchTranORDER BYFileDate, SourceTableData looks like this:FileDate DataSource-----------------------------------2005-02-13 00:00:00.000S12005-02-13 00:00:00.000S22005-02-13 00:00:00.000S32005-02-14 00:00:00.000S12005-02-14 00:00:00.000S22005-02-14 00:00:00.000S32005-02-15 00:00:00.000S22005-02-15 00:00:00.000S32005-02-16 00:00:00.000S12005-02-16 00:00:00.000S22005-02-16 00:00:00.000S32005-02-17 00:00:00.000S12005-02-17 00:00:00.000S22005-02-18 00:00:00.000S12005-02-18 00:00:00.000S22005-02-18 00:00:00.000S32005-02-19 00:00:00.000S12005-02-19 00:00:00.000S3We run a daily process that inserts data in to this table everyday forall 3 sources S1, S2, S3Notice how some data is missing indicating the import process for thatparticular source failed.Example: Missing record2005-02-15 00:00:00.000S12005-02-17 00:00:00.000S32005-02-19 00:00:00.000S2Can someone please help me with a SQL Statement that will return me the3 missing records as above.Thanks in advance for all your help!DBA in distress!Vishal
I have made trigger on table 'FER' that would be fired if data isinserted, updated to the table. And also, I made batch file using bcpto extract the newly updated / inserted records.But I got missing data in bcp out file like this:Missing 1200 records, blocked at:/*777946 296188 2007-01-29 21:25:45.063778145 296494 2007-01-29 21:25:47.063*/1. trigger.sqlCREATE TABLE [FERUpdate] ([id] [int] NOT NULL ,[fid] [int] NOT NULL ,[sid] [int] NOT NULL ,[UpdatePass] [int] NULL) ON [PRIMARY]GOcreate trigger trgFERUpdate on FER For Insert,Update asinsert into FERUpdate(id,fid,sid) select ins.id, ins.fid,ins.sid frominserted ins2. bcp.bat----isql -U <user-P <pw-S server -Q "update AA..FERUpdate setUpdatePass=1 where UpdatePass is null"bcp "select a.* from AA..FER a, AA..FERUpdate b where a.fid=b.fid anda.sid=b.sid and b.fid<>-1 and b.sid<>-1 and b.updatepass=1" queryout%TFN_NOW%.wrk -U <user-P <pw-S server -f FER.fmtisql -U <user-P <pw-S server -Q "delete from AA..FERUpdate whereUpdatePass=1"-----I have been struggling with this for these two days. Your any helpsare appreciated, Please help me out!! Thanks!!!
If I understand correctly, SSIS is kinda the "new DTS." And to use it, if I get the gist, I am supposed to open "SQL Server Business Intelligence Development Studio," select a new project, highlight the "Business Intelligence Projects" heading, and then select the "Data Transformation Project" template. But there is just one problem.
