Hi, we a phonenumber field in our database and I would like to do a simple lookup query like: SELECT * FROM TABLE WHERE Phonenumber = '555123456' But since the phonenumbers are entered by users and are not normalized, we don't really know what they look like. Could be: +555-123456 or (555) 123 456 or 555-12-34-56 or som...
MS Access has a button to generate sql code for finding duplicated rows. I don't know if SQL Server 2005/2008 Managment Studio has this. If it has, please point where If it has not, please tell me how can I have a T-SQL helper for creating code like this. ...
I need my query to pull all DirName's what have the following prefix 'site/test/test/' is the correct syntax a '*' SELECT DirName, count(*) AS FileCount, SUM(Size)/1024 as 'SizeKB' FROM alldocs Where DirName = 'site/test/test/*' GROUP BY dirName ORDER BY DirName ...
I am representing a tree in a table, and I need to be able to get the root node (TOP ID) of a specific element. The root node always has a ParentID of null. For example, if the table looks like this: ID ParentID 1 null 2 null 3 null 4 2 5 1 6 2 7 6 8 4 9 8 10 6 When ID=10, the TOP ID = 2; when ID=9, the TOP ID =...
Say I have a Server named "MyServerABC", on which I have Sql Server 2005 installed with a Default Instance. Thus I can always connect to my sql server just by specifying "MyServerABC". Now, I change my server's name to "MyServerDEF". Will I now be able to connect to the sql server by just specifying "MyServerDEF"? Are there any hol...
Background: Progress based ERP system called QAD 2008.1 (formerly MFGPRO. SQL Server 2005 Enterprise SP2. Reporting Services 2005 SP2. Progress OpenEdge 10.2A Driver. ODBC System DSN created using the Progress OpenEdge 10.2A Driver. Problem: I am creating a report with Reporting Services and will need to use the Progress OpenEdge 10.2A ...
I have SQL Server 2005 Database where the transaction log has not updated in a week. The transaction log has recently been truncated due to a situation that used up all available disk space. I have a test database on the same server which is growing daily as expected. What might be the cause of this? ...
I have a remote database I need to create a linked server to. The problem is it's been set up with my group's functional account an not my own. The only way I can think of doing this is logging out of my personal work account and then back in with out functional account and then set up the link that way, but that is impractical. So my ...
Does anyone see a performance issue with my logon Trigger? I'm trying to reduce the overhead and prevent any performance issues before I push this trigger to my production SQL Server. I currently have the logon trigger working on my Development sql server. I let it run over the past weekend and it put 50,000+ rows into my audit log ta...
I'm working on a DB upgrade script that needs to drop a column (that part's easy). Before I can drop the column, I want to take the value and move it into another column that already exists. I'm trying to acheive idempotence with my script, which is where I seem to be failing. I have code like this... IF EXISTS (SELECT * FROM...
I am using SQL Server 2008 express edition but I want to make my databases still in 2005. How can I set it up to only show features that a 2005 database can use? Like for instance I don't remember there being a "date" type. I only remember "dateTime". I don't want to be using features that 2005 can't support and I later on upload my db...
Hi I want to select values from tables by passing two parameters like start and end ranges How can i get it? Ex: sid sname s001 name1 s002 name2 s003 name3 s004 name4 s005 name5 s006 name6 s007 name7 s008 name8 s009 name9 s0010 name10 here i want to pass two values like 3 and 5 so that my quer...
Is there any way to get a table's last modification datetime on Sql Server 2005? Preferably without creating triggers.. And for databases? EDIT The solution of this question contemplated only CREATE and ALTER modifications. For INSERT, UPDATE and DELETE please look my next question ...
not a duplicate of my previous question Is there any way to get the latest datetime when a table/database had an insert/update/delete on Sql Server 2005? Preferably without creating triggers.. I know that when you need the last update per row, you need triggers. But I am not sure if they are needed when you just want to get the last up...
Duplicate: this exact question was asked by the same author in http://stackoverflow.com/questions/1221583/how-do-i-update-a-xml-string-in-an-ntext-column-in-sql-server. Please close this one and answer in the original question. I have a SQL table with 2 columns. ID(int) and Value(ntext) The value rows have all sorts of xml strings in...
I have the following DataTable (counting hours of work): start | end | time -----------------+------------------+------------------ 2009-05-01 08:00 | 2009-05-01 10:00 | 2009-05-01 02:00 2009-05-02 07:30 | 2009-05-02 11:00 | 2009-05-02 03:30 2009-05-03 23:00 | 2009-05-04 02:00 | 2009-05-03 03:00 There's a ...
Although I have experience with SQL and generating HTML reports with PHP, I'm a relative beginner with Microsoft Access. I'm currently using Microsoft Access 2007 to connect to MSSQL Server 2005. I have a table of Reports that looks something like this: ReportID DateCreated Author ... I'd like to create a form that allows the user t...
Hi All, using a C# 3.5 Windows form, I am calling a stored procedure that can take 30 minutes to execute (please no knocks or debate on that...it’s doing a lot to a lot of data and is fully optimized). I don't want to make my user leave the app open for that entire time period, so I would like to call the sproc, let it fly and let them ...
We need to backup 40 databases inside a SQL Server instance. We backup each database with the following script: BACKUP DATABASE [dbname1] TO DISK = N'J:\SQLBACKUPS\dbname1.bak' WITH NOFORMAT, INIT, NAME = N'dbname1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO declare @backupSetId as int select @backupSetId = positi...
I wanted to reduce the size of my log file in SQL SERVER 2005, which grown to 16 gigs, so I created a backup and used the dbcc shrinkfile command to shrink it. All that is set. Now what should I do with the backup file that is created - should I delete it? What impact will deletion have? ...