Hi all, I have some databases that have four files each; one for PRIMARY, IDX, IMAGE, LOG their initial sizes were set to 200MB each but they are consuming far less space, about 100MB total. 1) is it possible to resize the database files while they are in use down to more reasonable sizes, say "current consumption" + 10MB? 2) is it p...
I have a table with four columns: PartNumber, ValvePartNumber, ActuatorPartNumber, Price I want to find the number of distinct prices for each combination of ValvePartNumber and ActuatorPartNumber. This is using SQL Server 2005 ...
Is it possible to create an sql statement that selects the week number (NOT the day of week - or the day number in a week). I'm creating a view to select this extra information along with a couple of other fields and thus can not use a stored procedure. I'm aware that it's possible to create a UDF to do the trick, but if at all possible ...
We have a stored procedure that runs nightly that in turn kicks off a number of other procedures. Some of those procedures could logically be run in parallel with some of the others. How can I indicate to SQL Server whether a procedure should be run in parallel or serial ie: kicked off of asynchronously or blocking? What would be...
3 fields: FirstName, MiddleName, LastName Any field can be null, but I don't want extra spaces. Format should be "First Middle Last", "First Last", "Last", etc. ...
I'm trying to debug a rather complicated formula evaluator written in T-SQL UDFs (don't ask) that recursively (but indirectly through an intermediate function) calls itself, blah, blah. And, of course, we have a bug. Now, using PRINT statements (that can then be read from ADO.NET by implementing a handler for the InfoMessage event), I ...
SELECT pe.prodtree_element_id prodID, pe.prodtree_element_name_s, li.line_name, av2.value FROM prodtree_element pe LEFT JOIN prodtree_link pl ON pe.prodtree_element_id = pl.to_prodtree_node_id LEFT JOIN line li ON pe.line_code = li.line_code INNER JOIN attribute_values av ON av.attribute_definition_id = #st...
I'm implementing some rudimentary SQL Server monitoring to watch for excessive blocking. I have a stored procedure that looks like this: CREATE PROCEDURE [dbo].[bsp_GetBlockedProcessCount] AS BEGIN SET NOCOUNT ON; DECLARE @count INT SELECT @count = COUNT(*) FROM master.dbo.sysprocesses WITH (NOLOCK) WHERE blocked ...
I have a dynamic query that returns around 590,000 records. It runs successfully the first time, but if I run it again, I keep getting a System.OutOfMemoryException. What are some reasons this could be happening? The error is happening here: public static DataSet GetDataSet(string databaseName,string ...
I want to limit the number of records returned by calling ROW_NUMBER(), but I am curious as to if you can call it in dynamic sql? Also, Can you use CTE's in dynamic sql? ...
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY hrl.Frn) as Row, hrl.unq, hrl.LcnsId, hc.Business,hc.Name,hc.Phone, hrl.Frn,hrl.CallSign, hrl.gsamarkettypeid, gmt.[Market Type Code] + ' - ' + gmt.gsamarkettype, hrl.gsalatitude,hrl.gsalongitude, rsc.RadioServiceCode + ' - ' + rsc.RadioService, GrantDt, Ex...
Is T-SQL's CASE lazy evaluation? ...
Hi, Can anybody let me know the query to find all the tables that has a date columns on it. Thanks ...
I have a few huge tables on a production SQL 2005 DB that need a schema update. This is mostly an addition of columns with default values, and some column type change that require some simple transformation. The whole thing can be done with a simple "SELECT INTO" where the target is a table with the new schema. Our tests so far show tha...
SQL Server 2005 Express Edition SP2 supports scripting of objects into separate files. By default, the filenames include the name of the object's owner, the name of the SQL object followed by the object type. For example, dbo.Employee.Table.sql or dbo.GSP_EmployeeUpdate.StoredProcedure.sql. Though this naming convention may help one to...
I'm having the trouble finding the wording, but is it possible to provide a SQL query to a MS SQL server and retrieve the results asynchronously? I'd like to submit the query from a web request, but I'd like the web process to terminate while the SQL server continues processing the query and dumps the results into a temp table that I ca...
I have a sql statement that consists of multiple SELECT statements. I want to limit the total number of rows coming back to let's say 1000 rows. I thought that using the SET ROWCOUNT 1000 directive would do this...but it does not. For example: SET ROWCOUNT 1000 select orderId from TableA select name from TableB My initial thought wa...
Due to the packaged nature of the release, a SQL Server script (well more of a statement) needs to be created that can execute correctly on SQL Server 7.0 thru 2008 which can essentially achieve this: if exists(select * from sys.databases where name = 'Blah') Reasons this is difficult. SQL 7 'sys.databases' is not valid SQL 2008 'sys...
I've got some data files that are stored compressed on our company's server with a .Z extension (UNIX compress utility used to zip them down). Can SQL Server's BULK IMPORT operation read these files in that format? Or must I uncompress them before getting at the data? ...
I've got a couple of rogue spid's in my database that I can see are sleeping when I log in as sa and use sp_who, but attempts to use kill <spid> to eliminate them have failed and I don't actually have access to the server itself to bounce it. Is there any other way of killing these things off? ...