how do you pivot sql data without aggregating a column
Hello, I have the following output in a query. SKILL LEVEL SCORERANGE ----------------------------------------------------------------------------- Stunts LOW 0.0 - 4.0 Stunts MED ...
sql-server-2005
Reporting Services 2005 Online Training Resources
I have a bit of reporting services know how but I'm looking to expand my knowledge. Does anyone have any recommendations on (free) online training materials for this (2005 unfortunatley) ...
CHARINDEX always 0 when looking for substring
I have the following in a field ABCD, EFG, HIJ and can confirm this by selecting it. I need to be able to search this string for ABCD so I used the following: case when CHARINDEX(g.letters ,'ABCD') <> 0 then (- 2) else (- 1) end However its always returning -1 ...
Null vs empty field in database
I have database in which data is imported from the other table. if data is empty there. so while importing to here it became null. When i query the columns like, name doesnot starts with 'a', it should return all records whose name doesn't start with 'a'. including NULL/empty column. its returning epty records but not null. But i need nu...
Can i just STOP SqlServer replication?
Hi, i'm running 12 sql server replications, 11 of which have 1 subscription, and 1 has 2 subscriptions. Now i have a problem with a very large transaction log which i want to shrink, but i get the messages that the transaction log is in use. I suspect that the replication (publication, maybe the subscription too) is causing this problem...
php SQL Server mssql_select_db error
Up until yesterday I have been happily connecting to SQL Server Express 2005 using PHP 5 on IIS 7. Yesterday I started getting errors when selecting a database. <?php $link = mssql_connect('localhost,1433', 'login', 'password'); if(!$link) { die('could not connect to MSSQL'); } if(!mssql_select_db('database', $link)) { echo ...
Violation of UNIQUE KEY Contstraints - Cannot insert duplicate key
I am pulling out my hair over here. I have a table that has a UNIQUE Key (IX_tblTable) and the unique key is on a column Number. I am parsing some data from the web and storing it in the table. My latest collection of data from the web contains number THAT ARE NOT CONTAINED IN THE DATABASE. so I am getting data from the site and all ...
Bypass last INNER JOIN in query
I have the following stored procedure which takes a user ID, a starting date, an end date, and a list of codes in a comma-delimited list, and it returns all activity records between those two dates which match one of the codes in the list. ALTER PROCEDURE [dbo].[ActivitiesSummary] @UserID varchar(30), @StartDate datetime, @EndDate dat...
How to select items from table by using a @local_variable table
Currently in a simple form i have the following declared table in code: declare @FileIDs as table ( ID int not null ) and i can fill it up e.g. manually like this: insert into @FileIDs values (1) insert into @FileIDs values (2) insert into @FileIDs values (3) Also i have another table called Files and ...
Whats Select '1' for in the following stored proceedure
BEGIN IF EXISTS(SELECT * FROM Table1 WHERE ID=@ID) BEGIN UPDATE Table1 SET Name=@Name WHERE ID=@ID SELECT '1' END ELSE SELECT '0' END Is this the row no. of the table or what ? Also "IF EXISTS" is checking what ? the table or if the ID exists or not ?? ...
Why does a database query only go slow in the application?
I have a webpage that takes 10 minutes to run one query against a database, but the same query returns in less than a second when run from SQL Server Management Studio. The webpage is just firing SQL at the database that is executing a stored procedure, which in turn is performing a pretty simple select over four tables. Again the code...
Simplify this code
I hate code that looks like its been hacked together. I have just written this: update table1.dbo.totals set @FEE = case when isnull(g.SGROUPS,0) > 1 then @GROUPPRICE * case when CHARINDEX('JMCG', g.GROUPS) > 0 then (g.SGROUPS - 2) else (g.SGROUPS - 1) ...
Debug stored procedure from Visual Studio 2005
Hello, i'm missing the "Step Into Stored Procedure" option when i rightclick a Stored Procedure in Server Explorer/Data Connections. This is neither a problem under Windows XP(development pc) nor with Windows Server 2003/64 bit(old productive Server). But under Windows Server 2008/64bit there is not an option to debug a stored procedure...
Find the minimum flow in a day and the time it occurs
using MSQL 2005 I have a continuous set of flow measurements (averaged for each 15 minute time slice) I am trying to write a query to find the minimum flow for each day and the time it occurs Finding the minimum flow is easy but getting the time is harder. Currently I do this: select d1.data_point_groupid , min(d1.timeID) [timeI...
Query by Date/Time in SQL Server 2005
Hello, I have such a simple question, I feel stupid for asking it. I can't believe I'm hitting my head on this. I have a table called "Orders". "Orders" has a smalldatetime field called "DateOrdered". I need to get all of the "Orders" on a specific date. I have tried the following without any success: SELECT * FROM Orders WHERE [DateO...
declare variable for query string
Hey all, i was wondering if there was a way to do this in MS SQL Server 2005: DECLARE @theDate varchar(60) SET @theDate = '''2010-01-01'' AND ''2010-08-31 23:59:59''' SELECT AdministratorCode, SUM(Total) as theTotal, SUM(WOD.Quantity) as theQty, AVG(Total) as avgTotal, (SELEC...
SQL Server 2005 Losing Permissions
We're using SQL Server 2005 as a DB for our website. The user account that is being used to access the DB from the website is losing certain permissions once a day or once every 2 days. The role is still in SQL, but it is losing it's "data reader" permission on it's own. What could be causing this? How can I prevent it? ...
tsql : outputting each record to their own text file
is there a simple way to just output each record in a select statement to write to its own file? for example, if you have the tsql query in sql server 2005, select top 10 items, names + ':' + address from book and you ended up with 10 text files with the individual name and addresses in each file. is there a way to do this witho...
Change string order - Sql server
Hi All, I am moving data from an old table to a SQL server new version and am struggling with something that hopefully someone can help with. I have a column in the old table with 'EmployeeName' in the order firstname.lastname. In the new table we will be using a guid to identify the employees. The table with the guid's has the names ...
Find total number of hours between two dates
I know there are so many overlapping query questions already been answered but none of them were able to resolve the issue I'm having: We need to find out the total number of hours between the lowest start_date and highest end_date, considering the overlapping range as well. Start_Date End_Date 3/5/2010 11:27 3/5/2010 13:04 - Ne...