Ok, this should be an easy question to answer. I need the month+year from the datetime in sql server like 'Jan 2008'. I'm grouping the query by month,year. I've searched and found functions like datepart, convert etc. but none of them seem useful for this. Am I missing something here? Is there a function for this? ...
I am working in Visual Basic 6 and need to sort by multiple columns in a ListView. For example, sorting a list of music tracks by artist, then album, then track number. As far as I know, VB6 does not support this out of the box. Here are the suggestions I have already heard: Sort the data in a SQL table first and display the data in...
I'm trying to perform a SQL query through a linked SSAS server. The initial query works fine: SELECT "Ugly OLAP name" as "Value" FROM OpenQuery( OLAP, 'OLAP Query') But if I try to add: WHERE "Value" > 0 I get an error Invalid column name 'Value' Any ideas what I might be doing wrong? So the problem was that the order in...
We have a SQL Server 2005 SP2 machine running a large number of databases, all of which contain full-text catalogs. Whenever we try to drop one of these databases or rebuild a full-text index, the drop or rebuild process hangs indefinitely with a MSSEARCH wait type. The process can’t be killed, and a server reboot is required to get thin...
I have ms sql databases that grow very large. Upon examination I find that there is a bunch of unused space in certain tables. I don't do many physical deletes, so I don't think that its just deleted records. DBCC SHRINK doesn't make the file smaller. But, if I dump the table to a new, empty database, the size goes down about 80%. Instea...
Why can't you do this and is there are work around? You get this error. Msg 2714, Level 16, State 1, Line 13 There is already an object named '#temptable' in the database. declare @x int set @x = 1 if (@x = 0) begin select 1 as Value into #temptable end else begin select 2 as Value into #temptable end select * from #temptabl...
What is the best way to determine duplicate records in a SQL Server table? For instance, I want to find the last duplicate email received in a table (table has primary key, receiveddate and email fields). Sample data: 1 01/01/2008 [email protected] 2 02/01/2008 [email protected] 3 01/12/2008 [email protected] ...
Can anyone explain this behavior or how to get around it? If you execute this query: select * from TblA left join freetexttable ( TblB, *, 'query' ) on TblA.ID = [Key] inner join DifferentDbCatalog.dbo.TblC on TblA.ID = TblC.TblAID It will be very very very slow. If you change that query to use two inner joins instead of a left joi...
When should I choose one or the other? What are the implications regarding space and (full-text) indexing? BTW: I'm currently using SQL Server 2005 planing to upgrade to 2008 in the following months. Thanks ...
We have literally 100's of Access databases floating around the network. Some with light usage and some with quite heavy usage, and some no usage whatsoever. What we would like to do is centralise these databases onto a managed database and retain as much as possible of the reports and forms within them. The benefits of doing this would...
In a project of mine the SQL statements that are executed against a ms SQL server are failing for some unknown reason. Some of the code is already used in production so debugging it is not an easy task. Therefore I need a way to see in the database itself what SQL statements are used, as the statements are generated at runtime by the pro...
Consider the following 2 queries: select tblA.a,tblA.b,tblA.c,tblA.d from tblA where tblA.a not in (select tblB.a from tblB) select tblA.a,tblA.b,tblA.c,tblA.d from tblA left outer join tblB on tblA.a = tblB.a where tblB.a is null Which will perform better? My assumption is that in general the join will be better except in cases whe...
I'm loading a SQL Server 2000 database into my new SQL Server 2005 instance. As expected, the full-text catalogs don't come with it. How can I rebuild them? Right-clicking my full text catalogs and hitting "rebuild indexes" just hangs for hours and hours without doing anything, so it doesn't appear to be that simple... ...
I have never "hand coded" creation code for SQL Server and foreign key deceleration is seemingly different from SQL Server and Postgres...here is my sql so far: drop table exams; drop table question_bank; drop table anwser_bank; create table exams ( exam_id uniqueidentifier primary key, exam_name varchar(50), ); create table qu...
I need to be able to retrieve a CREATE TABLE script to recreate a specific table in a SQL Server (2000) database, programmatically (I'm using C#). Obviously you can do this with Enterprise Manager but I would like to automate it. I've been playing around with SQLDMO which offers a Backup command, but I'm not sure if this will give a SQ...
In Sql Server would a varbinary(16) be the most efficient way of storing an MD5 hash? Won't be doing anything with it except returning it in a linq query. ...
I have a table containing prices for a lot of different "things" in a MS SQL 2005 table. There are hundreds of records per thing per day and the different things gets price updates at different times. ID uniqueidentifier not null, ThingID int NOT NULL, PriceDateTime datetime NOT NULL, Price decimal(18,4) NOT NULL I need to get today's...
Take a .Net Winforms App.. mix in a flakey wireless network connection, stir with a few users who like to simply pull the blue plug out occasionally and for good measure, add a Systems Admin that decides to reboot the SQL server box without warning now and again just to keep everyone on their toes. What are the suggestions and strategie...
In our current database development evironment we have automated build procceses check all the sql code out of svn create database scripts and apply them to the various development/qa databases. This is all well and good, and is a tremdous improvement over what we did in the past, but we have a problem with rerunning scripts. Obviously...
I have written a log shipping program a number of times. It is a simple program that is used to maintain a warm fail over box for SQL Server. It has two pieces. On the live dB server it: Does full and transaction backups and removes old files On the backup server it: Copies the backups from the live box Restores the backups or t...