I'm trying to write a simple, thin and efficient database abstraction layer on top of the PHP MySQLi (procedural) and MSSQL libraries, to be used like this... while ($a = db::go('db_name')->query('select * from foo')->row()) { print_r($a); } I have the class written, but am struggling somewhat with how best to loop through the resul...
Based on information I have read about the "exec sp_reset_connection" which appears in SQL Server Profiler trace data when connection pooling exists, it seems safe--or logical, rather--to remove/exclude it from trace data to be considered by the Database Tuning Advisor. Any thoughts or objections? ...
Below is a simplified version of SQL script I have. print @RowNum always shows 0, rather than the real record number of the first result set. What's wrong? Thank you. declare @i int, @RowNum int set @i=0 while @i<2 begin execute StoredProcedure @i --containing a big select if @i=0 set @RowNum=@@rowcount set @i=@i+1 end print...
What's the easiest way to import a CSV into a database via web application? UPDATE: Small example of CSV: stop_id, stop_name, stop_desc, stop_lat, stop_lon, zone_id, stop_url TR, Trenton, , 40.2177778, -74.7550000, 6, LVTN, Levittown-Tullytown, , 40.1402778, -74.8169444, 5, BRST, Bristol, , 40.1047222, -74.8547222, 4, CROY, Croydon...
I tried rebuilding but it seems that the edmx file doesn't update itself with the changes I made. Any suggestions besides removing the edmx and remaking it? ...
Hi In a recent interview I was asked the difference between the two. I replied the general answere that all we know... The interviewer then asked if truncate can be rollbacked ? I replied no... The interviewer said that it can be rollbacked and asked me to go through the details of the behind the scene operation of both delete and t...
I have a sql stored procedure that runs for about 3 minutes, I am looking to execute this stored procedure from asp.net, but I know that if I do, that asp.net will most likely time out. I'm thinking of just creating a job within sql to have it execute that stored procedure, and have asp.net call a stored procedure to call that job. I ha...
I've got a large query where a simple subquery optimization dropped it from 8 minutes down to 20 seconds. I'm not sure I understand why the optimization had such a drastic effect. In essence, here's the problem part: SELECT (bunch of stuff) FROM a LEFT OUTER JOIN b ON a.ID = b.a LEFT OUTER JOIN c ON b.ID = c.b ... ... I...
I have a database consisting of the following tables: SourcesA: source, country, language SourcesB: id, url, source I first need to create a new table (Table1)and insert the join of the two tables where source matches. Second table (Table2) needs to contain the records that do not match. Third operation (Table3) is to take the fi...
DECLARE @T INT EXEC('SELECT @T = count(user_id) FROM ( .. .. .. )') PRINT @T I guess the example is sufficient to explain what i am trying to do, i create dynamic query and want to access its count outside the exec statement. I am using SQL 2000. How can this be achieved ? ...
We use stored procedures exclusively here, and that raises a little problem. We cross reference two different databases like dbdev..table1 in dev, dbqa..table1 in qa, and dbprod..table1 in production. So every time we deploy to a different environment, we have to search and replace from dbdev to dbqa or dbprod. Is there a way to use ...
Let's say I have the following data in a decimal (18,9) identity column in a database table: ID ======== 1000.00 1001.23 1002.00 1003.23 I want to write a SQL query that returns only those records that have .230000000 for the right side of the decimal place, such that only the following are returned: 1001.23 1003.23 I know I could ...
I have the following query: DECLARE @sync_table_name nvarchar(500) SET @sync_table_name = 'ContactTypes' DECLARE @sync_batch_size bigint SET @sync_batch_size = 2 DECLARE @sync_last_received_anchor timestamp SET @sync_last_received_anchor = 1 DECLARE @sync_max_received_anchor timestamp SET @sync_max_received_anchor = 18732866 DECLARE...
I have the following string áéíóú which I need to convert it to aeiou How can I achieve it? (I don't need to compare, I need the new string to save) ...
We have a database that has grown to about 50GB and we want to pull out a certain set of tables (about 20 of them) from within that database and move them into a new database. All of this would be on the same SQL Server. The tables that we want to pull out are about 12GB of space (6GB data, 6GB indexes). How can we move the tables fro...
I want to read rss from different sources and populate them in the database (sqlserver 2008). I'm planning to store the content of rss.xml in one table and the master information like the version and deep link of the rss in another table. I haven't worked in xml before and couldn't find a better solution till now... help anybody... ...
I'm thinking about how to represent a complex structure in a SQL Server database. Consider an application that needs to store details of a family of objects, which share some attributes, but have many others not common. For example, a commercial insurance package may include liability, motor, property and indemnity cover within the same...
I have a table that looks like this: Id PageId Key Content LastUpdated --------------------------------------------- 1 12 key1 content1 02-21-2010 2 12 key1 content2 02-25-2010 3 12 key2 content1 02-21-2010 4 12 key2 content2 02-25-2010 5 12 key3 ...
Hello All, I have a stored procedure that works fine on its own. A recent requirement has made me think a Union query will accomplish what I need. Here is the working version. It's uses the ROW_NUMBER() to accomplish paging and sorting correctly SELECT x.TicketID, x.TicketNumber, x.AccountID, x.SkillID FROM ( SELECT ROW_NUMBER() O...
i have a varchar reporttime that has the date stored. dont ask me why it is a varchar i am doing this to get data between two dates: select rowid from batchinfo where CONVERT(DATE, reporttime, 103) between '2010-07-01' and '2010-07-31' and it is not working what am i doing wrong? btw this is what the data looks like: rowid da...