I need an efficient way to get the absolute position of a record in a query matching a specific key value. The Find method is a serial search, too slow for big data sets. I am using both SQL Server Express and Jet 4 via ADO.
One example of why I need this .....
I have a list control with a subset of a table (controlled by where clause in query). I want to save the current state of the list control and later restore it when the app restarts. I want to preserve and restore the current line selection in the list control.
So, I save the key value for the current line, upon restart use the find method to locate the key, and set the list control current record index to the current absolute position.
This is too slow for big data sets since the find does a record by record search.
I cannot just save and restore the list control offset, the table may have changed.
The list control has owner data so the data is not all read into the control, so I can't just search through the controls image.
Any ideas. I did search for this answer and failed. Feel free to flame me as long as an answer is included too :-)
I got a problem. I am working on DTS package. The last step is updating a table field. I wrote a stored procedure as below:
CREATE PROCEDURE [Update_product_manufacturer] AS
Declare @product_id int Declare @supplier_name VarChar (255)
Declare ValueCursor Cursor For
select product.product_id, [P21_SUPPLIER_id_name_ke].[supplier_name] from [VARIANT],[P21_INV_MAST_uid_itenID_weight_ke],[product], [P21_INVENTORY_SUPPLIER_uid_supplierID_price_cost_k e],[P21_SUPPLIER_id_name_ke] where [product].product_id = [VARIANT].[product_id] and [P21_INV_MAST_uid_itenID_weight_ke].[item_id]=[VARIANT].[SKU] AND [P21_INV_MAST_uid_itenID_weight_ke].[inv_mast_uid]=[P21_INVENTORY_SUPPLIER_uid_supplierID_price_cost_k e].[inv_mast_uid] AND [P21_SUPPLIER_id_name_ke].[supplier_id]=[P21_INVENTORY_SUPPLIER_uid_supplierID_price_cost_k e].[supplier_id] order by [product].[product_id] for read only
Open ValueCursor while (0 = 0) begin fetch next from ValueCursor Into @product_id, @supplier_name
update product set manufacturer = @supplier_name where product_id = @product_id
end
close ValueCursor Deallocate ValueCursor
Notes: Table: Product has 28,000 rows, other tables with 28,000 - 56,000 rows
it's been 2 hours, the job is still working.
Who has this kind of experience? How can I make updating quickly?
I've been studying this tutorial over here at: http://quickstarts.asp.net/QuickStartv20/aspnet/doc/data/databases.aspx#updatedelete but I can't seem to get my gridview working. I want to be able to edit the fields and also delete an entire row from the table if needed. I hard coded in the update and delete commands into my objectdatasource but I still get the error message. Also, I can't even refresh the aspx page: <%@ Page Language="VB" MasterPageFile="~/templates/admin.master" AutoEventWireup="false" CodeFile="article_comments.aspx.vb" Inherits="admin_Default2" title="Untitled Page" %><asp:Content ID="Content1" ContentPlaceHolderID="PageHeading" Runat="Server"> </asp:Content> <asp:Content ID="Content2" ContentPlaceHolderID="PageContents" Runat="Server"> `<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="ObjectDataSource1" Width="536px" DataKeyNames="ratingID"> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" /> <asp:BoundField DataField="ratingID" HeaderText="ratingID" InsertVisible="False" ReadOnly="True" SortExpression="ratingID" /> <asp:BoundField DataField="rating" HeaderText="rating" SortExpression="rating" /> <asp:BoundField DataField="ip" HeaderText="ip" SortExpression="ip" /> <asp:BoundField DataField="itemID" HeaderText="itemID" SortExpression="itemID" /> <asp:BoundField DataField="comment" HeaderText="comment" SortExpression="comment" /> <asp:CheckBoxField DataField="active" HeaderText="active" SortExpression="active" /> </Columns> </asp:GridView> <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetRatings" TypeName="articlesTableAdapters.tblArticleRatingTableAdapter" DataObjectTypeName="articles+tblArticleRatingDataTable" DeleteMethod="DELETE FROM [tblArticleRating] WHERE [ratingID] = @ratingID" UpdateMethod="UPDATE [tblArticleRating] SET [ratingID] = @ratingID, [rating] = @rating, [ip] = @ip, [itemID] = @itemID, [comment] = @comment WHERE [ratingID] = @ratingID"> <UpdateParameters> <asp:Parameter Name="dataTable" Type="Object" /> <asp:Parameter Name="itemID" Type="Int32" /> </UpdateParameters></asp:ObjectDataSource> </asp:Content> >
We have a static class that makes an HTTPWebRequest to get XML data from one of our vendors. We use this as input to a stored proc in SQLServer2005. When I compile this class and call it from a console application in visual studio it executes in milliseconds, everytime. When I compile it, create the assembly and clr function and execute it in SQLServer, it takes around 14 seconds to execute the first time, then on subsequent requests it is again really fast, until I wait for 10 seconds and re-execute, once again it is slow the first time and then fast on subsequent requests. We do not see this behavior when executing outside SQLServer. Makes me think that some sort of authentication is perhaps taking place the first time the function is run in SQLServer? I have no idea how to debug this further. Anyone seen this before or have any ideas?
Here is the class:
Code Snippet
using System; using System.Collections.Generic; using System.Text; using System.Net; using System.IO;
namespace Predict.Services { public static class Foo { public static string GetIntradayQuote(string symbol) { string returnQuote = "";
HttpWebRequest request = (HttpWebRequest)(WebRequest.Create("http://data.predict.com/predictws/detailed_quote.html?syms=" + symbol + "&fields=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,28,30"));
StreamReader streamReader = new StreamReader(response.GetResponseStream());
returnQuote = streamReader.ReadToEnd();
streamReader.Close(); response.Close();
return returnQuote; } } }
When I run call it from a console app it is fine.
I compile it into a dll and then create the assembly and function as follows:
Code Snippet
drop function fnTestGetIntradayQuoteXML_SJS
go
drop assembly TestGetIntradayQuoteXML_SJS
go
create ASSEMBLY TestGetIntradayQuoteXML_SJS from 'c:DataBackupsCLRLibrariesTestGetIntradayQuote_SJS.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
go
CREATE FUNCTION fnTestGetIntradayQuoteXML_SJS(@SymbolList nvarchar(max)) RETURNS nvarchar(max) AS EXTERNAL NAME TestGetIntradayQuoteXML_SJS.[Predict.Services.Foo].GetIntraDayQuote
go
declare @testing nvarchar(max)
set @testing = dbo.fnTestGetIntradayQuoteXML_SJS('goog')
print @testing
When I execute the function as above, again, really slow the first time, then fast on subsequent calls. Could there be something wrong with the code, or some headers that need to be set differently to operate from the CLR in SQLServer?
Hi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString) { //call stored procedure } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.
Hi, I just have a Dataset with my tables and thats it I have a grid view with several datas on it no problem to get the data or insert but as soon as I try to delete or update some records the local machine through the same error Unable to find nongeneric method... I've try to create an Update query into my table adapters but still not working with this one Also, try to remove the original_{0} and got the same error... Please help if anyone has a solution
I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you
I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)
is this the best and fastest method to connect to a database for DataGrid databind?
--------- Dim MyConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionStringSQL")) Dim MyCommand As SqlCommand = New SqlCommand("sp_BuddiesPendingSelect1", MyConnection) MyCommand.CommandType = CommandType.StoredProcedure MyCommand.Parameters.Add(New SqlParameter("@UserID", intUserID))
MyConnection.Open() Dim dr As SqlDataReader = MyCommand.ExecuteReader()
DataList1.DataSource = dr DataList1.DataBind()
dr.Close() MyConnection.Close()
----------------------- or should i use a DataAdapter and fill a DataSet and then attach the DataSet to the DataGrid?
I have been using VB6 for a long time now and had no problems using ADODB.Recordset.I had a module to which I would send my recordset (byref) and the SQL command (byval) and use the resultant recordset for adding, modifying or deleting records.How can I do the same with VS2005? This is a major problem as there are dataadapters, grids, datareaders etc.Does someone have a simple method to get the recordset so that I can modify the record using the code (eg: .Addnew/.Delete/.Update) and Close the connection?Please note that I do not need to display anything and have to run 40/50 AddModDel for every click of the program.Any help is greately appreciated. Thanks in Advance.
If you not familiar with it, the <a href="http://www.stephenforte.net/owdasblog/permalink.aspx?guid=2b0532fc-4318-4ac0-a405-15d6d813eeb8">Rozenshtein Method</a> uses SQL to create a crosstab. The concept is a stroke of genius but I'm having trouble getting it to work on one of production databases.
I successfully used the Northwind example explained at Stephen Forte's site (see the link above)...but no luck on my real world problems.
I can get the date statements to resolve to 0 correctly, but when I try to aggregate the data - the statements are turning into 1 multiplying the aggregate data for each cell...which fills in the same data across the entire row.
For example (the columns represent the time period), GROUP T1 T2 T3 T4 group1 9 9 9 9 group2 3 3 3 3 group3 5 5 5 5
My sql code is: SELECT dbo.tblHassBatch.ProdLine, COUNT((dbo.tblHassUUT.UnitID)*(1-ABS(SIGN(datediff(dd,dbo.tblHassBatch.StartTime,ge tdate())-0)))) AS Today, COUNT((dbo.tblHassUUT.UnitID)*(1-ABS(SIGN(datediff(ww,dbo.tblHassBatch.StartTime,ge tdate())-0)))) AS [This Week], COUNT((dbo.tblHassUUT.UnitID)*(1-ABS(SIGN(datediff(mm,dbo.tblHassBatch.StartTime,ge tdate())-0)))) AS [This Month], COUNT((dbo.tblHassUUT.UnitID)*(1-ABS(SIGN(datediff(mm,dbo.tblHassBatch.StartTime,ge tdate())-1)))) AS [Last Month] FROM dbo.tblHassBatch INNER JOIN dbo.tblHassUUT ON dbo.tblHassBatch.BatchID = dbo.tblHassUUT.BatchID GROUP BY dbo.tblHassBatch.ProdLine
Am I overlooking something here? I'm pulling my hair out b/c if this works it's really going to provide a great solution for a project I'm working on...but I can't seem to figure it out.
I'm not really strong in SQL. My goal is to compare the beginning mileage of a vehicle record with it's previous ending mileage reading. I have something that works, but it feels clunky. I wonder if there is a better method, ie a join. Here's what I have:
SELECT A.Trolley_num, A.Date, A.Speedo_start, A.Speedo_end, (SELECT B.Speedo_end FROM Daily_Trolley AS B WHERE B.Trolley_num = A.Trolley_num AND B.Date = (SELECT Max(Date) FROM Daily_Trolley AS C WHERE C.Trolley_num = A.Trolley_num And C.Date < '1/23/2005')) AS PrevSpeedoEnd FROM Daily_Trolley AS A WHERE A.Date='1/23/2005'
ps: I inherited this db; I'm aware that "Date" should not have been used as a field name.
'TOP 1' or 'DISTINCT' or 'MAX' Any sugestions on which is better to use if I need to select a record that has the highest value - could be a INT or sometimes a DATETIME.
Can someone please tell me how to fix this following error in SQL 2005 When trying to create a maintenance plan, Method Not Found:'Void Microsoft.SqlServer.Management.DatabaseMaintenance.TaskUIYtils..ctro()' (Mircosoft.SqlServer.MaintenancePlanTasksUI)
Is there an efficient scripting method to update the connection string for ALL reports that reside on a reporting/web server? "(automating the process, rather than having to change the data source for each individual report that resides on that server)".
I have never used replication before and I have been asked to considerit in a project I am currently working on.I have created an application for a sales team which is loaded on theirmachines, it uses ms sql as its data source and connects via theinternet back to the central server in the office.Problem is this has shown to be too slow causing time out errormessages and so on. I have been told to research the possibility ofreplication, but am unclear what type of replication to use or where tostart.Any assistance would be appreciated.Regards,Ben
Hi,Given 2 tables:Table1:id Auto,int,PrimKeytable2id inttxt nvarchar50Table2:id Auto,int,PrimKeyorder intThe scenario: Table2.order defines how the table1.txt is should be ordered.Table1.table2id contains the id of the order. This cannot be changed :(How do I select all Table1.txt values but ordered by their correspondingvalues of the table2.order field?--Thx,PipHans---Outgoing mail is certified Virus Free.Checked by AVG anti-virus system (http://www.grisoft.com).Version: 6.0.516 / Virus Database: 313 - Release Date: 01-09-2003
HiSQL Server 7.0 using stored procedures.I need to do a SELECT Top but I always need to find the record countfor the entire SELECT statement.ID Name-- ----1 Abraham20 Barrington32 Callaway54 Dennis58 EmmettIf I do a SELECT TOP 3, I'll get the required records but get arecordcount of 3.I cant use the ROWCOUNT method either becuase the recordcount willalso be 3.I can see two different options for getting back the information Ineed:1 - Run the select statement twice, first to retrieve the recordcountand then with top to get the recordset2 - Use a temp table to get the entire recordset and recordcount, andthen extract the Top N recordsI'd like to know if anyone has any other suggestions. Currently, I'mtempted to go for the second option....ThanksSam
I want to implement the following query using SSIS Data flow Source component :
SELECT * FROM someTable WHERE someColumn = 'H'
How do I restrict the data coming from my data source? By that I mean how do I apply the WHERE clause in SSIS?! Should I use a conditional Split component?! but that would mean retrieving all records first then adding the split component (not the most efficient method surely).
I need to add a value to a column that I know to be DT_CY. However, there is no PipelineBuffer.SetCurrency() method or anything similar.
I checked out the docs for PipelineBuffer.SetDecimal() and it states "If the DataType of the buffer column is not DT_NUMERIC, an UnsupportedBufferDataTypeException occurs"
Similarly for PipelineBuffer.SetDouble() it says "If the DataType of the buffer column is not DT_R8, an UnsupportedBufferDataTypeException occurs"
So it seems I can't use either of those two methods. How do I push a valueto an output column of DT_CY when building an async component?
By the way, I could ask the same about
DT_DBDATE
DT_DBTIME
DT_FILETIME
DT_IMAGE
DT_TEXT
DT_NTEXT
DT_R4
DT_UI1 as well because according to the documentation there doesn't seem to be a method that supports those either. So, please could you answer the same question for those as well!
I have 1 project in VC++ and 1 in Vb.net I have added the VC++ project in VB.Net project using 'Add Existing item option' Now i want to use a method from VC++ project in VB.net How can i do so?????
Hi, I want to learn what is going on Example: Production Scheduling at http://msdn.microsoft.com/sql/learning/prog/clr/default.aspx?pull=/library/en-us/dnsql90/html/sqlclrguidance.asp
First, please interview Production Scheduling example. I'm curioing about SendResultsEnd method.
foreach (Item item in items) { // Compute the schedule and report it item.ComputeSchedule(); item.OutputSchedule(pipe); }
pipe.SendResultsStart(record); for (int i = 0; i < size; i++) { // Pipe each day of production. Omit zero production // days. if (schedule != 0) { record.SetDateTime(1, (DateTime)(dates)); record.SetInt32(2, schedule); pipe.SendResultsRow(record); } } pipe.SendResultsEnd();
Second code block is a part of OutputSchedule method. When pipe.SendResultsEnd method executed. First code block moves to next item. If SendResultsEnd method sends a resultset to client. This code will send many of resultset to client how many items array has in. If it is sending many resultsets. How can client can catch them? I was confused about this situation. And I don't have SQL Server 2005 in my computer. If you test it and explain what is going on I'll be appreciate to you. Please review example in the link.
I am developing an application in which i have to send data from local Sql Server compact edition database[Which is in a Windows Mobile Device,] to central server[SQL Server 2005]. I am using RDA method for communication
Can i use push method to send data from local DB to Central DB?
Is it must to use PULL method before using PUSH method?
I have three options in my mind to synchronize my data of sql server compact edition with sql server 2000/sql server 2005.
1)Merge Replication
2)Remote Data Access
3)Web Service
All the data would be residing on Sql server 2000/2005 and user will get their data after being authenticated and will change the data and add new data and then again will synchronize with server with new changes.
Please suggest me solution, my opinion is going towards Web service.
I have a column of values in one table that needs to be updated based on an adjustment factor in another table. The table which contains the adjustment factors is built with the following column names.
RangeBeg RangeEnd Factor
The factor is determined depending on where the value falls in the range. I have been trying to do an sql script with no luck. Any Help would be appreciated.
I have spent the afternoon researching this, and to no avail so here's my question. I have a user input form with upwards of 60 fields that are to be filled in by the user (intranet environment). I want to post those values as a new record in an SQL database. I have setup an SQLDataAdapter tied to a parameterized stored procedure on the SQL server. I can successfully post data from the controls on the form when I set the parameter type to Control based. Here's the problem, though. I need to do some pre-processing on most of the values in the controls on the form. I have setup VB code (that executes when the user clicks the "Submit" button), that pulls in each control value, performs any necessary processing, and sets a variable to the value I actually want stored in the DB. How can I assign THAT value (from the variable) to the parameter for the stored procedure? Thanks, Dave
I'm creating an application that will allow users to contribute "content". The content can be tagged, saved as "my content", etc... very web 2.0'ish. The site will rely very heavily on SQL Server 2005. By default, there is a Content table that simply stores the content with an identity column PK. There is also a Tag table and User table. What is the most effective schema for speed and reliable scalability? Eventually there could be hundreds to thousands of people contributing and tagging content. Idea 1: Lookup TablesSimply make a new table that holds the TagID and UserID. The table will be very important as it will be queried very regularly to show the users which tags they have stored.Pros: This will effectively allow me to store & query what tags the user has selected. It's simple to setup and the application won't have to work much with the data returned from the query.Cons: What happens when there are thousands of users tagging content? At what point does it become very inefficient to query a table that has a huge number of rows? Idea 2: Comma Delimited ListSimply has a field in the user table that has a comma delimited list of TagID's the user has selectedPros: Keeps table size low, fairly easy to implement.Cons: Application has to perform more work. It has to separate the TagID's by comma, and requery the database to get each tags data, based on TagID. Those are basically the only two methods I've really got experience using. The reason for this post is to see if there are methods that I'm not aware of that are better suited for what I'm trying to do. Any assistance is greatly appreciated, thank you in advance!
Dear all, i am using asp.net ,C# (VS 2005) and sql server 2005. i have written sp for inserting the the data which written last inserted idendity no. i would like to which method should i use(reader , nonexecutequery or executescalar ) so that i get that value and display the value in the form. As executenonquery return only affected rows.
Hello, I was wondering if there is any method that I can use to determine if a field (defined as text) has any character fields or is really a number. I want to figure out if a field is truly all numeric, and out of curiosity, was wondering if there was a way in SQL or T-SQL. Thanks.
Hi, I'm wondering about the following: I have come across an InfoPath Forms application who's code is scripted in javascript and who's data seems to be in XML files.An analyst at that company told me they suspect the data is ALSO in SQL Server... somewhere. They can't seem to find it though. I havereviewed the .js code and some methods are called for which I can find no source. I believe those methods execute OK because they're foundinside some DLL. I'm thinking I would enter a new record using the form in InfoPath using some datavalue that I can expect will be unique.. like a lastname who's first three chars is ZZZ or something like that. Subsequently, I'd search each column in each table in each DB on the server to see if I can locate it somewhere. So, my question is what is the best approach for this? I have access to the db, table and column names. I know I can write a small vb.net piece of code to execute my search. But, is there some better way using some sql procedure (or using the full text catalog) instead or any other tool(s)? Thanks in advance for your advise. Stewart
