Hello Can I refactor the below SQL CASE statements into single for each case ? SELECT CASE RDV.DOMAIN_CODE WHEN 'L' THEN CN.FAMILY_NAME ELSE NULL END AS [LEGAL_FAMILY_NAME], CASE RDV.DOMAIN_CODE WHEN 'L' THEN CN.GIVEN_NAME ELSE NULL END AS [LEGAL_GIVEN_NAME], CASE RDV.DOMAIN_CODE WHEN 'L' THEN CN.MIDDLE_NAMES ELSE NULL END...
I am trying to restore the database in SQL Server 2008. The backup file is SQL Server 2008 from another PC . When i want to restore show me the error TITLE: Microsoft SQL Server Management Studio ------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInf...
I have table a and table b. (SQL Server 2008) Both tables have the exact same schema. For the purposes of this question, consider table a = my local dev table, table b = the live table. I need to create a SQL script (containing UPDATE/DELETE/INSERT statements) that will update table b to be the same as table a. This script will then b...
I'm using LinqToSQL in a 3.5 framework project in VS2K8, running against MS SQL 2k8. When I create the table mappings using the design surface within Visual Studio everything works fine. When I create the table mappings using SQLMetal (from the Windows SDK v6.1), SQLMetal complains about about the SQL Server 2k8 datatypes (e.g. date an...
Hi, I'm using SQL Server 2008 on Windows Server 2008 R2, all sp'd up. I'm getting occasional issues with SQL Server hanging with the CPU usage on 100% on our live server. It seems all the wait time on SQL Sever when this happens is given to SOS_SCHEDULER_YIELD. Here is the Stored Proc that causes the hang. I've added the "WITH (NOLOCK...
I'm need to write a stored procedure for SQL Server 2008 for performing a large select query and I need it to filter results with specifying filtering type via procedure's parameters. I found some solutions like this: create table Foo( id bigint, code char, name nvarchar(max)) go insert into Foo values (1,'a','aaa'), (2,'b','bbb')...
I have an interface that loads a list of documents depending on the tab clicked by a user. I've been retrieving the data through XML from a SQL 2008 database with: SELECT col1, col2 col3 FROM documents WHERE typeId = 4 FOR XML PATH('doc'), ROOT('documents') Then tranforming the XML with an XSLT stylesheet. It all works fine. To imp...
Here's my configuration: I have a re-runnable batch script that I use to update my database. Inside of that batch script, I have code that says the following: If Table 'A' doesn't exist, then create Table 'A' and insert rows into it. Later on in that batch script, I create an schemabound indexed view on that table. And if you didn't...
The snapshot says is it all - I used to be able to attach a database, but after i detached today then tried to attach, i kept getting this error: http://img197.imageshack.us/img197/2574/attacht.jpg ...
I would like to make a query for database user roles for all databases in my sql server instance. I modified a query from sp_helpuser: select u.name ,case when (r.principal_id is null) then 'public' else r.name end ,l.default_database_name ,u.default_schema_name ,u.principal_id from sys.database_principa...
Even if I have made a certification exam on SQL Server Design and implementation, I have no clue about how to trace/debug/optimise performance in SQL Server. Now the database I built is really business critical, and getting big, so it is time for me to dig into optimisation, specially regarding when/where to add indexes. Can you re...
I'm using asp.net mvc, linq2sql, iis7 and sqlserver express 2008. I get these intermittent server errors, primary key conflicts on insertion. I'm using a different setup on my development computer so I can't debug. After a while they go away. Restarting iis helps. I'm getting the feeling there is cache somewhere that I'm not aware of. C...
I see that I can "Create a deployment Script and deploy the database" as a deployment action, howerver it's not clear where I script the actual creation of the database? I see under the "Storage" folder a sub folder for Filegroups, Files, and I'm guessing I could drop in a create database script into there, but how do I pick up the data...
I have update NewLeaderBoards set MonthlyRank=(Select RowNumber() (order by TotalPoints desc) from LeaderBoards) I tried it this way - (Select RowNumber() (order by TotalPoints desc) from LeaderBoards) as NewRanks update NewLeaderBoards set MonthlyRank = NewRanks But it doesnt work for me..Can anyone suggest me how can i perfo...
I have 3 tables Table1 (with 1020690 records), Table2(with 289425 records), Table 3(with 83692 records).I have something like this SELECT * FROM Table1 T1 /* OK fine select * is bad when not all columns are needed, this is just an example*/ LEFT JOIN Table2 T2 ON T1.id=T2.id LEFT JOIN Table3 T3 ON T1.id=T3.id and a query like this S...
I am using CONTEXT_INFO to pass a username to a delete trigger for the purposes of an audit/history table. I'm trying to understand the scope of CONTEXT_INFO and if I am creating a potential race condition. Each of my database tables has a stored proc to handle deletes. The delete stored proc takes userId as an parameter, and sets CON...
Based on the following table ID Date State ----------------------------- 1 06/10/2010 Complete 1 06/04/2010 Pending 2 06/06/2010 Active 2 06/05/2010 Pending I want the following ouptut ID Date State --------------------------- 1 06/04/2010 Complete 2 06/05/2010 Active So date is the ea...
I am using the following SQL code for inserting multiple rows of data in a table. The data is passed to the stored procedure using an XML variable : INSERT INTO MyTable SELECT SampleTime = T.Item.value('SampleTime[1]', 'datetime'), Volume1 = T.Item.value('Volume1[1]', 'float'), Volume2 = T.Item.value('Volume2[1]',...
I have a hard time finding a good question title - let me just show you what I have and what the desired outcome is. I hope this can be done in SQL (I have SQL Server 2008). 1) I have a table called Contacts and in that table I have fields like these: FirstName, LastName, CompanyName 2) Some demo data: FirstName LastName CompanyName...
Hello! I can't seem to crack this - I have two tables (Persons and Companies), and I'm trying to create a view that: 1) shows all persons 2) also returns companies by themselves once, regardless of how many persons are related to it 3) orders by name across both tables To clarify, some sample data: (Table: Companies) Id Name 1 Ba...