I am running two very similar update queries but for a reason unknown to me they are using completely different execution plans. Normally this wouldn't be a problem but they are both updating exactly the same amount of rows but one is using an execution plan that is far inferior to the other, 4 secs vs 2 mins, when scaled up this is cau...
I am relatively new to using SQL Server (we're on 2005) but not to database design, creation, and programming. I have decided to use TSQLUnit for the testing we want to do, and have been learning it as I go. But, there are some things that have not become clear over time, and it gets pretty frustrating to want to do a particular type o...
I have just installed SP3 for SSMS 2005 so that I can connect to 2008 databases. I can now connect fine and even create databases, but I get "Unspecified error" when I try to create a new table. ...
;with Ranked(Empid,Mngrid,Empnm,RN,level) As (select Empid,Mngrid ,Empnm ,row_number() over (order by Empid)AS RN , 0 as level from dbo.EmpMngr), AnchorRanked(Empid,Mngrid,Empnm,RN,level) AS(select Empid,Mngrid,Empnm,RN ,level from Ranked ), RecurRanked(Empid,Mngrid,Empnm,RN,level) AS(select Empid,Mn...
I have a SQl Server 2005 database backup that I want to transfer to SQL Server 2008 on my server. I spent 3 days transferring the .bak file from my own machine to my server. I then tried to restore the bak file and I got an error. I then read online a completely different method for adding a SQL server 2005 Database to SQL server 2008 wh...
Hi, I need date string using sql statement like.. select getDate() this will return 2010-06-08 16:31:47.667 but I need in this format 201006081631 = yyyymmddhoursmin How can I get this? Thanks ...
I know that snapshot isolation would fix this problem, but I'm wondering if NOLOCK is safe in this specific case so that I can avoid the overhead. I have a table that looks something like this: drop table Data create table Data ( Id BIGINT NOT NULL, Date BIGINT NOT NULL, Value BIGINT, constraint Cx primary key (Date, I...
I have an column declarated as int (called HourMil) wich store the time in military format. i need convert this values to an formated string (HH:MM) example HourMil = 710 -> must be 07:10 HourMil = 1305 -> must be 13:05 Actually i am using this code (and works ok) for convert the column HourMil to the string representation. SELEC...
In SQL Server, how can I insert data into a table that has just one column which is of identity type? Such as insert into the following table t. How can I write the insert statement? CREATE TABLE t ( id INT IDENTITY(1, 1) PRIMARY KEY ) Great thanks. ...
I have been working on a query that will return a suggested start date for a manufacturing line based on due date and the number of minutes needed to complete the task. There is a calendar table(LINE_ID, CALENDAR_DATE, SCHEDULED_MINUTES) that displays per manufacturing line, the number of minutes scheduled for that day. Example: (Usu...
I'm trying to create a persistent SQL (SQL Server 2005) lock on a table level. I'm not updating/querying the specified table, but I need to prevent a third party application from updating the locked table as a means to prevent transactions from being posted (the table I wish to lock is the key on their transaction that interferes with my...
I am trying to set up a package template in SSIS, following the Wrox Programmer to Programmer book, SQL Server 2008 Integration Services: Problem - Design - Solution. I'm really liking this book even though it is 2008 and we're using SQL Server 2005. I've got a working package template that uses an Indirect XML package configuration to i...
I am developing a C# VS 2008 / SQL Server 2005 Express website application. I have tried some of the fixes for this problem but my call stack differs from others. And these fixes did not fix my problem. What steps can I take to troubleshoot this? Here is my error: System.Data.SqlClient.SqlException was caught Message="Conversion f...
I've got an application running on Rails 2.3.5, on a Linux box, and I need to connect to Sql Server 2005 for one of my models. I've been reading up on this, and it's getting frustrating, can't find a solution that works thus far. I've done this before using JRuby and JDBC, but I can't use Java in this scenario. Any pointers to a failsa...
I'm trying to create an index on a view and it keeps failing, I'm pretty sure its b/c I'm using an alias for the column. Not sure how or if I can do it this way. Below is a simplified scenario. CREATE VIEW v_contracts WITH SCHEMABINDING AS SELECT t1.contractid as 'Contract.ContractID' t2.name as 'Customer.Name' FROM contr...
This is my Very first Post! Bear with me. I have an Update Statement that I am trying to understand how SQL Server handles it. UPDATE a SET a.vField3 = b.vField3 FROM tableName a INNER JOIN tableName b on a.vField1 = b.vField1 AND b.nField2 = a.nField2 – 1 This is my query in its simplest form. vField1 is a Varchar ...
Hi guys, I have a problem of listing duplicated rows that include NULL columns. Lemme show my problem first. USE [tempdb]; GO IF OBJECT_ID(N'dbo.t') IS NOT NULL BEGIN DROP TABLE dbo.t END GO CREATE TABLE dbo.t ( a NVARCHAR(8), b NVARCHAR(8) ); GO INSERT t VALUES ('a', 'b'); INSERT t VALUES ('a', 'b'); INSERT t VALUES ('a...
Where does temporary tables gets stored in database. I want to drop a temporary table if it already exists. I can do this for securable table by querying at information schema but i done know where temporary tables are stored ...
Hi guys, Can I install a second SQL Server instance on the same location as the first instance? Can that cause some problem? By the way, I'm using SQL Server 2005. Great thanks. ...
Possible Duplicate: Can I install a second SQL Server instance on the same location as the first instance? Hi guys, Can I install a different edition of a SQL Server instance? I mean, let's say I already installed standard edition of SQL Server 2005 on my Windows XP SP3 machine, then I want to install another instance and use...