I have the following table with data: tag dt value 1 2009/03/01 10.2 2 2009/03/01 12 3 2009/03/01 120 .... 1 2009/03/02 13.2 2 2009/03/02 9 3 2009/03/02 23 .... Basically, for a group of tags, there are values along date (March 1, 2, ...). I would like to have data in a different view li...
I have a User table: Name varchar(20) Picture image I want to store a image into the Picture column -- how can I achieve this using SQL Scripts? ...
Is it faster to use 1 type of provider over the other? We are using SSIS (SQL Server 2005) to pull some data from Oracle and import it into SQL Server. It was my understanding that OLEDB is faster, because the connection is native, and the data isn't being run through any .NET code? Is this correct? ...
I've got a log file on my database whose size is 303 MB and %used is 3.19. I'm pretty sure the transaction volume is limited to a few tables. The database and log are backed up nightly. How do I assess my log to determine: whether I can shrink the allocated space, and thus the log how big my log grows daily/weekly? Do I check the l...
I am trying to create an SSIS package that queries data from a table, and calls a stored procedure in another database with each row. In my old DTS package, I was doing this: EXEC myStoredProcedure ?, ?, ? ...and then I mapped the parameters. However, in SSIS, I can't figure out how to make this work. I have a Data Flow task, which...
I have a query returning a column of floating point numbers but I am only interested in the number before the decimal place. I don't want to round the number so I am looking for something like: 1.95 = 1 1.45678 = 1 12.00 = 12 12.9999 = 12 Is there an easy way to achieve this in SqlServer other than doing a substring? ...
I have a case where I wanna choose any database entry that have an invalid Country, Region, or Area ID, by invalid, I mean an ID for a country or region or area that no longer exists in my tables, I have four tables: Properties, Countries, Regions, Areas. I was thinking to do it like this: SELECT * FROM Properties WHERE Country_ID NOT ...
How would you DBA's handle this? I have taken ownership of an existing app (VB6) and database that was written in 1999. The database design is fairly 'flat', meaning the main tables are fairly wide (100+ columns) and developers have continued to tack on additional columns to the end of the tables. This has resulted in columns that have...
Application is being developed in VS 2008. Report viewer is used and calls a report from SSRS on a SQL Server 2005 server. Error: Server Error in '/AppName' Application. Configuration Error Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error d...
I have a sql server 2005 table which has xml store in a text field. I am wondering if there is an easy way (using TSQL) of retrieving the a value from the xml data without doing a line by line parsing of the text? Doing a line-by-line parsing is a possibility because the amount of xml is pretty small. However, if possible I would like ...
I have a table with columns id, a, and b. a+b should be unique, but this is a legacy database that is not constrained correctly. How can I get a set of ids for records in which a+b is not unique? If I have ID A B 1 2 3 2 2 3 3 1 3 4 1 4 Then I want to get records 1 and 2 back from the query. ...
I have SMS based survey application which takes in a survey domain, and a answer. I've gotten requests for detailed DDL, so.... The database looks like this SurveyAnswer.Answer must be unique within all active Surveys for that SurveyDomain. In SQL terms, this should always return 0..1 rows: select * from survey s, surveyanswer sa wh...
When I try to add a new Sql Server Database file to my Visual Studio project, it tells me that I need to have Sql Server 2005 Express installed even though I have Sql Server 2005 Developer already? Is this by design and if so, why did Microsoft do it? and if it is not by design, what is the solution to fix it? ...
Hi there, I need some advice on SQL Server 2005 maintenance plan, okay here some question: What task(s) is/are suitable for daily maintenance and what for weekly/monthly maintenance Do database need to be offline while in some task, example : reorganize/rebuild index, Shrinking database, etc... (since we need to keep 90% uptime) How l...
hi,I am trying to rename the column datatype from text to ntext but getting error Msg 4927, Level 16, State 1, Line 1 Cannot alter column 'ColumnName' to be data type ntext. query that i m using is as follows:- alter table tablename alter column columnname ntext null ...
I'm trying to write a query to see if an engineer visited his job in a agreed time slot. This is my query so far: SELECT v.[VISITDATE], CONVERT(VARCHAR, v.[STARTTIME], 105) AS 'Startdate', CONVERT(VARCHAR, v.[STARTTIME], 108) AS 'StartTime', CONVERT(VARCHAR, v.[bookeddate], 105) AS 'BookedDate', CONVERT(VARCHAR, t.[star...
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER Procedure [dbo].[rptGGM] ( @FromDate datetime, @ToDate datetime, @PartyName varchar(50), @Type int ) As DECLARE @WhrStr VarChar(500) SET @WhrStr = CASE @Type WHEN 1 THEN ' And E.ATAAir between '+ @FromDate +' And '+@ToDate WHEN 2 THEN ' And D.AT...
I have a MS SQL 2005 database with a table Test with column ID. ID is a identity column. I have rows in this table and all of them have their corresponding ID autoincremented value. Now I would like to change every ID in this table like this: ID = ID + 1 But when I do this I get error: Cannot update identity column 'ID'. I've trie...
I'm trying to investigate when & why certain rows are getting deleted in a SQL 2005 database. I've started building a trigger to log some information when a row is deleted. My trigger is activated when row(s) are deleted from a certain table. I have it set up to log a timestamp in another logging table when the delete occurs. I'd also l...
Is there an alternative for master..xp_remote_execute system stored proc inside sql 2005. We are currently in the middle of a migration from 2000 to 2005 and this is one of those litele glitches. Thanks, ...