I need help in converting this sql query to linq

I need to convert the following sql query to linq.

I am having trouble in using left outer join in LINQ. Any help will be appreciated

select 
LTRIM(RTRIM(SKU.SELLER_SKU)) AS "SKU Number",
LTRIM(RTRIM(SKU.DESCRIPTION)) as "Description",
LTRIM(RTRIM(SOURCING.SOURCING_CATEGORY)) as "Product Category",
LTRIM(RTRIM(SKU.UOM_CODE)) as "Seller UOM",
LTRIM(RTRIM(SKU.LEAD_TIME)) as "Lead Time",
CASE
 WHEN CONRT.CONVERSION_RATE is null  THEN 
'1'
else
LTRIM(RTRIM(conrt.CONVERSION_RATE)) end as "Converstion Rate",
LTRIM(RTRIM(SKU.QTY_MEASURE)) as "Qty UOM",
LTRIM(RTRIM(SKU.START_DATE)) as "Seller Start Date",
LTRIM(RTRIM(SKU.END_DATE)) as "Seller End Date",
CASE
 WHEN SKU.END_DATE >= getdate() or SKU.END_DATE is null THEN 
 'Published'
 else
    'Unpublished' end as "Web Status",
     LTRIM(RTRIM(SUPP.SUPPLIER_NAME)) as "Supplier",
     LTRIM(RTRIM(SUPPSKU.SUPPLIER_SKU)) as "Supplier SKU",
     LTRIM(RTRIM(SUPP.BLANKET_PO)) as "Blanket Number",
     LTRIM(RTRIM(SUPPSKU.UOM_CODE)) as "Supplier UOM",
     LTRIM(RTRIM(SUPPCOST.UNIT_COST)) as "Supplier Unit Cost",
     LTRIM(RTRIM(SUPPSKU.START_DATE)) as "Supplier Start Date",
     LTRIM(RTRIM(SUPPSKU.END_DATE)) as "Supplier End Date",
     LTRIM(RTRIM(SKU.MANUFACTURER_NAME)) as "Manufacturer Name",
     LTRIM(RTRIM(SKU.MANUFACTURER_SKU)) as "Manufacturer Part Number"


from [dbo].[SELLER_ITEMS] as SKU
LEFT OUTER JOIN  [dbo].[SUPPLIER_ITEMS] as SUPPSKU
ON SKU.ITEM_ID = SUPPSKU.SELLER_ITEM_ID
LEFT OUTER JOIN SOURCING_CATEGORY AS SOURCING
ON SKU.SOURCING_CATEGORY_ID = SOURCING.SOURCING_CATEGORY_ID 
LEFT OUTER JOIN [dbo].[SUPPLIERS] as     SUPP
ON SUPPSKU.SUPPLIER_ID = SUPP.SUPPLIER_ID
LEFT OUTER JOIN [dbo].[CONVERSION_RATES] AS CONRT
ON SUPPSKU.SUPPLIER_ITEM_ID = CONRT.SUPPLIER_ITEM_ID
LEFT OUTER JOIN DBO.SUPPLIER_ITEM_COSTS SUPPCOST
ON SUPPSKU.SUPPLIER_ITEM_ID = SUPPCOST.SUPPLIER_ITEM_ID

Need conversion of above query to linq...

Query optimization with variables

First, I apologize if this question is a common one... I can't seem to find the correct terms to search...

I have a somewhat large table that has an incremental ID and an inserted datetime value. There are a couple indexes on it, including a primary key on the ID and an non-clustered index on the inserted time and ID. So, when I write a query like the following, it's very quick:

select min(ID), max(ID)
from tbl
where inserted between '2015-10-07' and '2015-10-08'

However, if I variablize the where clause conditions (as below), it's quite a bit slower:

declare @sTime datetime, @eTime datetime
select @sTime = '2015-10-07', @eTime = '2015-10-08'

select min(ID), max(ID)
from tbl
where inserted between @sTime and @eTime

When I look at the two query plans I see the obvious problem. The first query utilizes a single seek on the non-clustered index of the inserted time and ID. However, the second query instead performs 2 scans on the primary key (which is just the ID).

So, my question is two-fold: 1) Why is sql server performing these optimizations, and 2) how can I fix it?

How to migrate from SQL Server 2005 to SQL Server 2012

I have this scenario:

Old Server -> SQL Server 2005 with the current Database

New Server -> SQL Server 2012 no database created.

I want to migrate the old database to the new one.

I've tried to create a backup from the Management Studio 2005 and restore in the 2012. Failed.

I've connected from the 2012 server using Management Studio 2012 to the old server and create the backup. Then restored in the new one. Failed.

Then, I tried to use the Copy database Wizard. But a error message shows up: The destination server can not be an instance of SQL Server 2005 Express or higher .

So, how it is supposed to do the backup?

I'm getting the scripts in sql files as last resort...

PS: I've check this thread, I need to migrate to 2008 and then 2012? How to migrate a database from SQL Server 2005 to 2008 as is?

Counting and grouping challenge in a pivot table with T-SQL

I have a pivot table that converts a vertical database design to a horizontal one:

The source table:

Id  ParentId    Property    Value
---------------------------------
1   1           Date        01-09-2015
2   1           CountValue  2
3   1           TypeA       Value1
4   1           TypeB       Value2
5   1           TypeC       Value2
6   2           Date        15-10-2015
7   2           CountValue  3
8   2           TypeA       Value3
9   2           TypeB       Value22
10  2           TypeC       Value99

After pivoting this looks like:

ParentId    Date        CountValue  TypeA   TypeB   TypeC
----------------------------------------------------------
1           01-09-2015  2           Value1  Value2  Value2
2           15-10-2015  3           Value3  Value22 Value99

Then, there's a look-up table for valid values in columns TypeA, TypeB and TypeC:

Id  Name    Value
-----------------
1   TypeA   Value1
2   TypeA   Value2
3   TypeA   Value3
4   TypeB   Value20
5   TypeB   Value21
6   TypeB   Value22
7   TypeC   Value1
8   TypeC   Value2

So, given the above structure I'm looking for a way to query the pivot table in a way that I'll get a count of all invalid values in TypeA, TypeB and TypeC where Date is a valid date and CountValue is not empty and greater than 0.

How can I achieve a result that is expected and outputted like below:

Count   Column
--------------
0       TypeA
1       TypeB
1       TypeC

I've accomplished the result by creating three several queries and glue the results using UNION, but I think it should also be possible using the pivot table, but I'm unsure how. Can the desired result be realized using the pivot table?

Note: the database used is a SQL Server 2005 database.

Create a Linked Server for a MySQL Database in SQL Server 2005

I'm currently trying something I've never done before - I'm trying to access MySQL data in an SQL Server environment. Where I am, we have two servers (the MySQL and MSSQL servers), and each stores different types of data. Recently I've found a need to write queries that include data for both servers. One of the ways I read to do this is adding a "Linked Server" in SQL Server. But try as I might I cannot seem to get a Linked Server added.

I am working on a Windows 7 system, our SQL Server instance is 2005, and the MySQL version is 5.5.31.

What I've done so far is this:

 - Downloaded and installed the MySQL ODBC drivers (http://ift.tt/1hr1Vdl)
 - Created a System DSN using that driver for the MySQL database, called "Production". (I also tested the connection from the Data Sources application and it connects just fine).
 - Checked for the Provider in SQL Server (MSDASQL) to configure options such as "allow nested queries" and "allow 'LIKE' operator"
 - right click on Linked Servers in Server Objects in SQL Server > "New Linked Server"

From here, I fill out the linked server info like this:

---

 Checked "Other data source" (not SQL Server)


 Chose Provider "Microsoft OLE DB Provider for ODBC Drivers"


 Product Name: "Production"


 Data Source: (the name of the System DSN) Production

---

Then in the security tab, I chose the "Connections will be made using this security context" and provided my username and login for the MySQL server.

Then this is where I run into an error every time. I always get the following error message:

"The linked server has been created but failed a connection test. Do you want to keep the linked server?"

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

------------------------------

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "FLUX".
OLE DB provider "MSDASQL" for linked server "FLUX" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)

For help, click: http://ift.tt/1LitqSF

If you click that link for help, by the way, nothing shows up. I saw other people suggested trying an older MySQL ODBC driver, adding a System Data Source for the 32bit driver as well as the 64bit driver, and restarting my machine (not the DB server) -- none of these fixed my error.

What I do not understand is why my System DSN seems to work and connect fine, but SQL Server cannot see the Data Source. Does anyone see what I'm missing, or what I might be configuring incorrectly?

Thanks for your time.

EDIT Clarified that I restarted my local machine, but not the DB server since trying to add the Linked Server.

Batch process to delete table from SQL Server backup files

I have a set of full backups from a same SQL Server database saved in different files. For example:

• 01.my-database.bak
• 02.my-database.bak
• 03.my-database.bak

I want to delete a specific table from those files to save disk space. There is a batch process to do that?

MsSql Merge not working

I'm trying to preform a merge but for some reason i get a error.

What I'm doing is: I am insert all the records where the productname is not empty and it is not marked as double in a temp table.

After I loop through temp table to insert/update every record in the real table [Products]

MERGE INTO [Products] AS T
USING SingleProducts AS S
ON (T.SoftwareID = S.[@ProductCode] AND T.PriceType = S.[@PriceType])
WHEN NOT MATCHED BY TARGET
    THEN INSERT (SoftwareID, IsActive, [Image], ProductNameNL, ProductNameFR, DescriptionNL, DescriptionFR, UnitPrice, PriceType, CreationDate, LastUpdatedDate)
        VALUES (S.[@ProductCode], 0, 'no-image.png', S.[@ProductNameNL], S.[@ProductNameFR], S.[@DescriptionNL], S.[@DescriptionFR], 
                    S.[@UnitPrice], S.[@PriceType], S.[@CreationDate], GETDATE())
WHEN MATCHED
    THEN UPDATE SET
        T.[SoftwareID] = S.[@ProductCode],
        T.[ProductNameNL] = S.[@ProductNameNL],
        T.[ProductNameFR] = S.[@ProductNameFR],
        T.[DescriptionNL] = S.[@DescriptionNL],
        T.[DescriptionFR] = S.[@DescriptionFR],
        T.[UnitPrice] = S.[@UnitPrice],
        T.[PriceType] = S.[@PriceType]
WHEN NOT MATCHED BY SOURCE
    THEN DELETE
OUTPUT $action, inserted.*, deleted.*;