I have a data structure inside a table in SQL Server 2005 representing a chain of related objects. Each object can have replacements in many steps. I want to perform a query that returns all objects and each object's leaf in the replacement chain. The data: id replacement 1 null 2 3 3 null 4 5 5 6 6 null The result shoul...
I have a raw file that contains 2m rows ; an ID and a text column What I want to do is use this raw file and update a table on our live database. The problem I have is I want this to happen in batches/slowly as I dont want to impact live too much whilst it is doing this. The process will need to open the raw file using a Dataflow task...
Is there any particular reason (performance or otherwise) to use AS ahead of = when aliasing a column? My personal preference (for readability) is to use this: select alias1 = somecolumn alias2 = anothercolumn from tables etc... instead of this: select somecolumn as alias1 anothercolumn as alias2 from tables etc... Am I mi...
I am looking for a good SQL Statement to select all rows from the previous day from one table. The table holds one datetime column. I am using SQL Server 2005. ...
How to convert the datetime value to nvarchar and want to format it "Month, Year" e-g October 1st 2009 value should get converted to "October, 2009" ...
In SQL Server 2000 I have a query like SELECT DISTINCT A.COLUMN1, B.COLUMN2 FROM TABLEA A, TABLEB B WHERE A.KEY_ID = B.FK_ID ORDER BY CASE @ORDER_NAME WHEN 'COL1' THEN COLUMN1 WHEN 'COL2' THEN COLUMN2 ELSE COLUMN2 END ASC Here A.COLUMN1 is varchar(50) and B.COLUMN2 is datetime. This quer...
Is it possible to add multiple queries to the SqlCommand object in .net? And if one of the queries fails and the rest succeeds, does the command report this as a failure or succes? last question: can the queries share the same parameters? So, is this possible: UPDATE mytable SET column1 = @param1 WHERE recid = @recid; UPDA...
I have a number of reports deployed to a SQL Server 2005 Reporting Services server. They were all developed using the same Report Model (SDML) that references the same Data Source View (DSV) that points to a test database filled with mostly dummy data. Now, I would like to make those reports pull data from the live database with our real...
I'm trying to understand how to combine queries when one of them returns more than one record. This is an invoicing report where I want to pull in the Serial Numbers of products invoiced. I'll abbreviate the script as much as possible to clarify. Here is my script before adding the serials: SELECT ARM.fcustno AS [Cust No] , ...
Hi there, is there a recommended amount of connections fro each application i have for use with connection pooling.., my apps are using asp.net and c# against sql express on the "same" server. I have 5 applications running, they are not used intensively, all connections are opened and closed.. So i was thinking of setting each app to ...
I have a bunch of users making ad-hoc queries into a database running SQL Server. Occasionally someone will run a query that locks up the system for a long period of time as they retrieve 10MM rows. Is it possible to set some options when a specific login connects? e.g.: transaction isolation level max rowcount query timeout If this...
Hi, I have an SQL script than runs a couple of DBBC commands. Is there a way to send the messages generated from executing these commands to a simple log file e.g "C:\log.txt"? The messages I mean are the ones that appear in the messages box at the bottom of SQL Server 2005 Management Studio after a query has executed. The reason for ...
I have been told that SQL Profiler makes changes to the MSDB when it is run. Is this true and if so what changes does it make? MORE INFO The reason I ask is that we have a DBA who wants us to range a change request when we run the profiler on a live server. Her argument is that it makes changes to the DB's which should be change contro...
Given a table Event containing a field called EventTime of type DateTime and that the value will contain both date and time elements, I need create a summary query which counts the number of events in each month. The resulting type of the Group By field must also be Date Time with a 0 time element and set to 1st day of the month. This ...
I am using a timetabling application called CELCAT and trying to pull out some data about when students should have been marked for reporting... This seems to be extremely difficult because of the way the adding and removing of students on registers is structured see below: studentid eventid fromdatetime additio...
Consider searching a table that contains Apartment Rental Information: A client using the interface selects a number of criteria that are represented as bit fields in the DB, for instance: AllowsPets HasParking HasDeck ModernKitchen etc.. We are facing a situation where each new client of our software has additional fields they w...
I want to join to tables and get the following output Table1 TestId1 ---------- one two three four five six seven eight Table2 TestId2 ---------- fiftythree fiftyfour fiftytwo fiftyfive fiftyone I want Table3 as my output with all rows from table1 and the first rows from table2 until there are no more rows left and then they shoul...
I have a particularly difficult business constraint that I would like to enforce at the database level. The data is financial in nature, and so must be protected from inconsistencies to the nth degree – no trusting the business layer with this stuff. I use the word "temporal" somewhat loosely, meaning that I intend to control how an enti...
I have a table in a SQL Server 2005 database that logs purchases like so: ID (PK, int, not null) Request (text, null) Response (text, null) MerchantId (varchar(14), null) Amount (money, null) The Request and Response fields are really storing XML. I can't change the data type to XML. I need to draw a query that will get data out of ...
Is there a way in SQL Server 2008 to find the table with the most rows in the database? ...