tsql

The type arguments for method cannot be inferred from the usage. Try specifying the type arguments ex - error. Any guidance?

/// <summary> /// Find all of the Areas that are Parents without children Areas. /// </summary> /// <returns>IQueryable object full of Areas.</returns> public IQueryable FindParentAreas() { return db.Areas.SelectMany(x => x.ParentAreaID == null); } I want to return a collection of Area objects that a...

T-SQL Debugging Logon Failure

Server Info: Windows Server 2008, SQL Server Standard 2008 (64-bit) I recently upgraded to SQL 2008 and was interested in trying out T-SQL Debugging. Initally encountered some firewall issues, but after disabling my workstation's Windows Firewall and having TCP port 135 and UDP 4500/500 opened up by the IT group I got a little further....

Help with t-sql query

Based on the following table id Title Date Metadata ------------------------------------ 1 A 08/01/2010 M1 1 A 10/05/2010 M2 1 A 03/15/2011 M3 2 B 09/20/2010 M1 2 B 01/15/2011 M2 3 C 12/15/2010 M1 Input variables will be start and end date. S...

How do capture the result set from RESTORE FILELISTONLY FROM DISK... so I can query it?

How do I capture the output from the following SQL statement so I can query the resultset?: RESTORE FILELISTONLY FROM DISK = N''D:\Restores\MyBackup.BAK'' WITH NOUNLOAD, FILE = 1 Do I need to create a temporary table and then do something like?: INSERT #tmp EXEC ('RESTORE FILELISTONLY FROM DISK = N''D:\Restores\KevsP...

t-sql select query

Based on the following table Title Jul-10 Aug-10 Sep-10 Oct-10 Nov-10 Dec-10 Jan-11 Feb-11 Mar-11 Apr-11 May-11 Jun-11 -------------------------------------------------------------------------------------------- A Null M1 Null M2 Null Null Null Null M3 Null Null Null B Null M1 Null N...

t-sql string & table column manipulation

DETAILS table has following columns Title First Second Third Fourth Fifth ------------------------------------------ A null null null null null input variable -------------- @columns = 'Title, Third, Fourth' I want to generate ouput as @allcolumns = 'Title, Third, Fourth, First, Second, Fifth' Variable @allco...

transaction question in SQL Server 2008

I am using SQL Server 2008 Enterprise. And using ADO.Net + C# + .Net 3.5 + ASP.Net as client to access database. When I access SQL Server 2008 tables, I always invoke stored procedure from my C# + ADO.Net code. My question is, if I do not have any transaction control (I mean begin/end transaction) from my client C# + ADO.Net code, and I...

Query plan caching and performance

You can read the backstory if you want, after changing my nonclustered index to a clustered index things started running a lot faster. The problem with the initial query plan taking 2-3 seconds still remain though. However, the keep plan query hint improved that quite a bit. BACKSTORY I have an inverted index where I store things I wis...

deadlock on a single SQL Server table

Hello everyone, I am using SQL Server 2008 Enterprise. And using ADO.Net + C# + .Net 3.5 + ASP.Net as client to access database. When I access SQL Server 2008 tables, I always invoke stored procedure from my C# + ADO.Net code. I have 3 operations on table FooTable. And Multiple connections will execute them at the same time in sequence...

Entity Framework: Column must be mapped although it does have a default value?

Hi, I am trying to create an Entity Framework 4 model based on a legacy SQL server 2008 database. Since the model is going to be published in a web service, I need to omit an integer column called NewsletterSubscribed from one of the model's entities. After removing the column in the EF designer I got the following compilation error: ...

In T-SQL, how to insert a new row with all values set by default?

Hi, In Microsoft SQL database, I have a table where every column have default values (either fixed values or stuff like identity or getdate()). I am trying to write an SQL statement which will insert a new row in which every cell will have a default value. Neither insert into MySchema.MyTable nor insert into MySchema.MyTable () va...

T-SQL: Move data from one table to another

Hi Guys, Whats going to be the easiest way to migrate my data in the following situation: Table A: ANIMAL ANIMAL_ID (PK,IDENTITY) CAT_NAME DOG_NAME BIRD_NAME FISH_NAME Table B: ANIMAL_NEW ANIMAL_ID (PK,IDENTITY) ANIMAL_TYPE(INT) ANIMAL_NAME I've changed the design, so instead of having 4 of the same table columns, I introduced an...

T-SQL function for generating slugs?

Hi All, Quick check to see if anyone has or knows of a T-SQL function capable of generating slugs from a given nvarchar input. i.e; "Hello World" > "hello-world" "This is a test" > "this-is-a-test" I have a C# function that I normally use for these purposes, but in this case I have a large amount of data to parse and turn into s...

Update query on millions of rows fills the transaction log.

Hi, I need to update millions of rows as part of my next release, but doing so fills the transaction log and fails. I have a few ideas but I'm not a SQL expert so I'm sure there will be gotchas that I'm not aware of. Pertinent points: I need to hand a script over to the operations team so need a T-SQL method with no manual interventi...

A Query to return Alphanumeric

Does any have any query that returns apha numerice values only Sample Select FirstName,Surname,NationalID From Contacts Results FirstName|Surname|NationalID Tony |Smith |934&#fdsaf$34£51 Mary |Jones |655^!ffdat@389£2 Expected results FirstName|Surname|NationalID Tony |Smith |934fdsaf34£51 Mary |Jones |655ffdat38...

How can I programmatically check (parse) the validity of a TSQL statement?

I'm trying to make my integration tests more idempotent. One idea was to execute rollback after every test, the other idea was to some how programatically parse the text, similar to the green check box in Query Analyzer or SSMS. How do I get SQL Server to parse my command without running it using ADO.NET? UPDATE: This is what finally...

TSQL Howto get count of unique users ?

This is really dumb question but still my head cannot work it out. I have a table with Orders, each order has userID in it. User can have unlimited amount of Orders. How do i make count of unique userIDs ? ...

Title: UPDATE is not allowed because the statement updates view "table_name" which participates in a join and has an INSTEAD OF UPDATE trigger.:

i am getting the following error while executing the following query in an Stored Procedure..Could anyone help in finding the fault? UPDATE is not allowed because the statement updates view "sup_item" which participates in a join and has an INSTEAD OF UPDATE trigger. UPDATE si SET name = mc.name, sup_item_cat_id = mc.res_sup_i...

Is it possible to convert nvarchar to int?

Hello all, I have tried something like this: select PREPRO = case when (isnumeric(PREPRO) = 1 and PREPRO in ('0','1','-1')) or convert(varchar, PREPRO) in ('True','False') then convert(bit, convert(integer, PREPRO)) else 'No' end from dbo.TI10SE The PREPRO contains all "False". I get this error: Conversion failed when converting ...

SQL Server - How to Grant Read Access to ALL databases to a Login?

I need to give a new login read access to all 300 databases on a server. How can I accomplish this without checking 300 checkboxes in the user mapping area? ...