I am trying to find how many days are between two dates. I have been attempting to use this: SET Days_Outstanding = DATEDIFF(day, CONVERT(datetime, LS_CHG_DTE_EQP, 1), CONVERT(datetime, Report_Date, 1)) However, this is not returning the number in days. It's returning a datetime in years that have nothing to do with my data set. ...
Background We have a Windows .NET application used by our field employees who travel all over the country and live out of hotels most of the time. Recently we added some features that connect directly to our SQL Server 2005 for data access (i.e. SqlConnection.Open()). Since then we have run into some issues with this generating errors...
Is there a way in sql server 2005 to query for enabled and disabled accounts? basically I wanted to see the current state of user accounts. I mean the accounts affected by command below. ALTER LOGIN someuser ENABLED|DISABLE ...
Hey Guys, I am new to managing servers but I have just been given full access to set up an MS SQL Database on win server 2003. I have created a Database successfully and can connect locally (via remote desktop server) Problem is I can't connect to the database from my personal computer (remotely, via ms sql server management studio). I...
Hi Folks, How do I get the Physical Database and Log File location (file system path) of a DB in SQL Server 2005? I used this to get the mdf file: {SELECT [Name], FileName FROM sysdatabases} but need to get the log file as well... Cheers, Conor ...
I'm upgrading from SQL Server 2005 to 2008. I've detached the database I need, but can't find it on the file system. Where does a default installation of SQL Server 2005 store these? ...
Through SINGLE T-sql query can we find : 1. Database Backup mode - [full,simple,bulk] 2. IS database is scheduled for backup [yes, no] 3. If backup is enabled the size of the DB backup and Daily transaction log backup size ? any help ...
Hi, I have the following in a stored procedure: DECLARE @new_column_name varchar(9) DECLARE @table_name varchar(16) DECLARE @SQLString nvarchar(2000) SET @new_column_name = N'name' SET @table_name = N'tbl_test_table' SET @SQLString = N'SELECT @CountOUT = COUNT(*) FROM [' + @table_name + '] WHERE [' + @new_column_name + '] = ''' + @de...
Following is the table and script of this table. DECLARE @temp TABLE (PPId INT, SVPId INT, Minimum INT, Maximum INT) INSERT INTO @temp VALUES(1,1,8,20) INSERT INTO @temp VALUES(2,1,21,100) Minimum & Maximum are passed in as parameter. I want to find all rows that fall in the given range. E.g.; If @minimum = 9 and @maximum = 15 t...
I have a table containing the working hours for the company. We define the range of the dates and the number of hours for the range. The number of working hours which is not in the defined range is 9.5 hours. I want to have the non-defined range with the value of 9.5 added to the result record set. how should I make the query to get th...
I want a query to insert records from one table to another table in different database if the destination table already exists, it should append the records at the end of the table. Please help me. Thanks in advance. ...
Trying to track down a bug in an application, but need to confirm whether a column value was changed on a particular record in SQL 2005. How can I query the transaction logs for the record's previous column values? ...
I am attempting to execute a stored proc in asp.net in the code behind. The parameter I am trying to pass is strErrorMessage that contains a value of "The transport failed to connect to the server.; ". The error message when the query gets executed is: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is...
What I'm trying to do is create a table that has 2 fields, one being the status and one being a type of equipment. I then need a list of how long these particular types of equipment have been being repaired. There can be multiples of each equipment, so I would like something like this: Equipment Type | Status | 0-7 days | 8-15 days ...
I have one big query for performance related in SQL Server 2005. I have data like this id parentId 1 null 2 1 3 1 4 2 5 4 6 3 I want the order for the records by downline with parentId and id wise like id Order 1 1 2 2 4 3 5 4 3 5 4 6 I don't want to use loop, because loop is creating the problem if high ...
I am using Hibernate entity manager 3.5.1-Final with MS SQL Server 2005 and trying to persist multiple new entities. My entity is annotationally configured thus: @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private int id; After calling entityManager.persist(newEntity) I do not see the generatedId set, it remains as 0. ...
I have a db columns with values like 100.23.24.1, 100.23.24.2, 100.23.24.3 etc. I have to find the last number after the last dot. In this case I want 3. Then I want to increment the last number and generate new entry as 100.23.24.4. How can I do this. Can someone please help? I cannot look for the 100.23.24. pattern because this may ch...
hi, i want to know what are the various ways to ESCAPE single quotes(') in SQL LIKE command . one way is to put two single quotes whenever you have to escape a single quote. Can you people suggest something?? Thanks. Database -- SQL2005, oracle 10g ...
Hi We have a site in development that when we deployed it to the client's production server, we started getting query timeouts after a couple of hours. This was with a single user testing it and on our server (which is identical in terms of Sql Server version number - 2005 SP3) we have never had the same problem. One of our senior dev...
I have a table where I have 6 columns. 5 of these columns I have to make sure that I don't have any duplicates. So I used the statement: SELECT SUB_ACCT_NO_PAJ, CustomerType, POST_DTE_PAJ, IA_DateYear, ADJ_RSN_PAJ, count(*) AS [aCount] INTO TempTable1 FROM All_Adjustments GROUP BY SUB_ACCT_NO_PAJ, CustomerType, POST_DTE_PAJ, IA_DateYe...