Is there a straightforward way to do this? Basically, there's a large set of sql servers that some people need access to. They only permissions they need is to read all data and all code. ...
I have a SQL problem I am trying to digest. I am using SQL Server 2005. In a table I have data as such: ID Type 1 A 2 A 3 A 3 B 4 B I need to find all of the IDs that have a Type of both A and B. This is not a homework problem. It's a real work issue I'm trying to resolve. Thanks ...
I am developing a web portal in Asp.Net. Primary target users will be of India Only. But in future I may target overseas users also. I want to know if I should use Sql Server Replication or not. Should I concern about "Replication" at initial stage or can I use it at later stages. Thanks in advance. ...
I have installed SQL 2005 and now have instance name as the default one: SERVERNAME I'd like to change to: SERVERNAME\MyDB Is this possible? If yes, how? ...
I have a DATETIME column "EntryDate" and I am storing values as dd/MM/yyyy format. But when I fetch records through SQL I don't get the expected result. For ex: I have 90 records from date 23/10/2009 to 23/11/2009. I get only 5-10 records. Why is this ? Here is my query : SET DATEFORMAT DMY; SELECT * FROM salesorderlist so LEFT JOIN...
I'm creating an update trigger that goes like this (SQL Server 2005): Is the state column of the row is 23 or 25 don't update it. Else update it. It's very simple. I'm trying OldState = (Select State from Deleted) If OldState in (25,23) Update it --how to do it easily? else dont do nothing for this row The problem is that the ...
I have a relation between two tables with 600K rows and my first question is, is that a lot of data? It doesn't seem like a lot (in terms of rows, not bytes) I can write a query like this SELECT EntityID, COUNT(*) FROM QueryMembership GROUP BY EntityID And it completes in now time at all, but when I do this. SELECT EntityID, COUNT(*...
Is there a way to write SQL Server Stored Procedure which to be strongly typed ( i.e. returning known result set of columns ) and having its group statement to be dynamic. Something like: SELECT SUM( Column0 ) FROM Table1 GROUP BY @MyVar I tried the following also: SELECT SUM( Column0 ) FROM Table1 GROUP BY CASE @MyVar WHEN 'Column...
It's my understanding that SQL Server 2005 does some sort of result or index caching. I'm currently profiling complex select statements which take several seconds to several minutes to complete. My problem is that a second run of a query never takes more than a second to run even if I don't alter it. I'm currently using SQL Server Manage...
I have a question related to database design. The database that I'm working with requires data to treated in some way that it is never physically deleted. We started going down a path of adding a "DeleteDateTime" column to some tables, that is NULL by default but once stamped would mark a record as deleted. This gives us the ability ar...
I am having a really difficult time getting the result I need out of the following query. SELECT SUM(HEALTH_MTTR) AS MTTR, SUM(HEALTH_OTR) AS OTR, SUM(HEALTH_REPEAT) AS REPEAT, SUM(HEALTH_CHRONIC) AS CHRONIC, SUM(HEALTH_TOTAL) AS TOTAL, (CAST(CAST(YEAR([DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(4)) + '-' + Right('00' + CAST(MONTH([DATE_RES...
I'm trying to write a query that returns the most recent GPS positions from a GPSReport table for each unique device. There are 50 devices in the table, so I only want 50 rows returned. Here is what I have so far (not working) SELECT TOP(SELECT COUNT(DISTINCT device_serial) FROM GPSReport) * FROM GPSReport AS G1 RIGHT JOIN (SELECT DIST...
i want to check for a particular set of values. eg check columnname should be between 1 to 5 check columnname should be either 1 or 2 or 4 ...
I have a table with INT id and DATETIME date, amongst other fields. Rows are inserted into this table each weekday (not guaranteed), and several other tables use this id as a foreign key. My question is, how can I get the id for the max date of each month, which I can then use to join to other data tables? For example, if the process ...
It is well documented here that when you update an assembly (that is not on the approved list) in the GAC that happens to be inside SQL 2005/2008, the SQLCLR will complain that could not load file or assembly. This is also a problem when moving a database to a new machine (that could have different versions of the assembies). Is there a...
I'm trying to profile the experience of multiple users of a web application, all trying to generate reports at the same time. The reports are displayed on a web page using the report viewer control. The execution log on the report server seems to indicate that the reports are executed sequentially (one at a time). Is this the expected b...
Hi all, I am using output inserted.PKfielName to get the varchar type primary key value of last inserted record. Now i want to get the primary key value of last updated record. Geetha ...
Hello Friends... I am working on a project, which includes a searching task by entering the string as a key for search. I have made a table which stores all the words which are entered by the user. I have written a function for separating the words from a string. Up until it works perfect. I want to search the candidate which has the e...
Hi guys, I am thinking this is impossible but I wanted to make sure. Is there a way for me to know when a table was locked and maybe for how long? I know that I can see whether a table is currently locked, but I would like to have a "history" of locks. ...
Is it possible to limit CPU usage at SqlBulkCopy? When I execute my code (see below), CPU usage on SQL server jumps to 100%. I'd like to limit this to, let's say, 50%. Is this possible? Code: string connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ToString(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(...