I'm trying to select more than 80,000 record in SQL Server in a table that has millions of records. The issue is that I've the correct Index, but it takes more than 15 minutes to return the recordset. I'm using MS SQL Server 2000, I found a pagination method using stored procedures but it uses a temporal table that I have to insert the ...
I have a table that represents a series of matches between ids from another table as follows: CREATE TABLE #matches ( asid1 int, asid2 int ) insert into #matches values (1,2) insert into #matches values (1,3) insert into #matches values (3,1) insert into #matches values (3,4) insert into #matches values (7,6) insert into #matches v...
Do T-SQL queries in SQL Server support short-circuiting? For instance, I have a situation where I have two database and I'm comparing data between the two tables to match and copy some info across. In one table, the "ID" field will always have leading zeros (such as "000000001234"), and in the other table, the ID field may or may not h...
I have a column that has fields such as C5, C6, C3, CC, CA, CD, etc. I want to be able to find all the columns that have a letter and then a number such as C5 and C6. I know if I do: SELECT * FROM TABLE WHERE FIELD LIKE 'C%' It will return CC,CA,CD as well, but I don't want that, I want to just return the ones numbers. I also wan...
I'm at square one on a project that will need to repeatedly import data from MySQL *.dmp files into an existing Sql Server database. I am looking for guidance in the form of an existing tool, framework, or, barring pre-existing solutions, suggestions for how to proceed. My first thought is to read the dmp file in as text and perform so...
Here is my SQLCommand object: oCommand.CommandText = "INSERT INTO hits (id,client_id,client_ip,page,vars) VALUES _ (@@IDENTITY,@client_id,@ip,@page,@vars)" oCommand.Parameters.Count = 4 >> oCommand.Parameters.Item(0).ParameterName = "@client_id" >> oCommand.Parameters.Item(0).Value = "123456" >> oCommand.P...
Using SQL Server, how do I script a constraint on a field in a table, so that the acceptable range of values is between 0 and 100? ...
Can someone explain the use of DBCC DROPCLEANBUFFERS together with the CHECKPOINT operator and provide an example? I understand it is useful to test performances running before your queries but I don't fully grasp it. Also I have no clue about the use in combination with the CHECKPOINT operator. If I execute it before my queries they t...
For many years we have been using xp_pcre for regular expressions in sql server: xp_pcre - Regular Expressions in T-SQL I've just migrated to a new 64 bit vista development machine and have installed xp pcre. Unfortunately when i try to use the sprocs I get the following error: Could not load the DLL xp_pcre.dll, or one of the DLLs it...
Hi, I need to transfer data from one table to the same table in another server which has been truncated. Can somebody please, let me know the easiest way to do it. Please, help me. Thanks, Chris ...
How can I get a DataSet with all the data from a SQL Express server using C#? Thanks edit: To clarify, I do want all the data from every table. The reason for this, is that it is a relatively small database. Previously I'd been storing all three tables in an XML file using DataSet's abilities. However, I want to migrate it to a databas...
I have the below query, which basically it retrieves the 5 top most books sold: select top 5 count(id_book_orddetails) 'books_sold', bk.* from orderdetails_orddetails ord inner join books_book bk on ord.id_book_orddetails = bk.id_book group by id_book, name_book,author_book,desc_book,id_ctg_book,qty_book,image_book,isdel...
I'm a MySQL guy working on a SQL Server project, trying to get a datetime field to show the current time. In MySQL I'd use NOW() but it isn't accepting that. INSERT INTO timelog (datetime_filed) VALUES (NOW()) ...
What is the best way to code the following generic data access functions (ADO.NET, C# or VB, SQLServer or OLEDB) Execute SQL on a connection Open a DataReader Open a DataSet (any ideas on this one?) Such that I can call these functions from anywhere in my program. I'm not interested in Data Access patterns or Data Access layers unle...
I have created a one-time subscription in SSRS report manager 2008. However I keep getting duplicate emails sent out by the subscription. The interval between each email is about 30 minutes. I have checked SQL agent job and it looks the SQL agent job is created correctly. It just runs once and after that the next run time is cleared. ...
I would like to use an SQL Server table as an action queue. So whenever the table is non-empty, I'd like some sort of notification to my VDF application, in order to avoid constantly polling the database. VDF relies on using stdcall, the standard calling convention for the Win 32 API, or COM/ActiveX. Another solution that could work for ...
If i SELECT a row for updating in MS SQL Server, and want to have it locked till i either update or cancel, which option is better :- 1) Use a query hint like UPDLOCK 2) Use REPEATABLE READ isolation level for the transaction 3) any other option. Thanks, Chak. ...
Hi all, So my goal here is to have a single search field in an application that will be able to search multiple tables and return results. For example, two of these tables are "performers" and "venues" and there are the following performers: "John Andrews","Andrew Smith","John Doe" and the following venues: "St. Andrew's Church","City ...
I've a table with a lot of registers (more than 2 million). It's a transaction table but I need a report with a lot of joins. Whats the best practice to index that table because it's consuming too much time. I'm paging the table using the storedprocedure paging method but I need an index because when I want to export the report I need t...
I've been working on some data transformation tasks in SSIS. Visual Studio has gotten better in 2008 in it's usability, but I find there are some things that annoy me (i.e. When I delete something in the Package Explorer it refreshs the whole screen bringing me back to the top of the tree. Also, lack of some keyboard shortcuts.) Is there...