How can we get the Sum of the Pivot Column sum for each Row?

The Following Code is used to get the pivoted column from row i need the sum of the each row from the following code Please help me to get the sum of the each row pivoted columns .

PIVOT (MAX([Stock]) FOR [SubInventory] IN ('+STUFF(@columns,1,1,'')+')

bu using this code i got the pivoted columns and i need to get the sum of these column from this code

    DECLARE @t TABLE (
                        id int IDENTITY(1,1),
                        BranchName nvarchar(max)
                     )

    DECLARE @n int = 0,
            @i int = 1,
            @BranchName nvarchar(max),
            @sql nvarchar(max),
            @columns nvarchar(max)

    INSERT INTO @t
        SELECT DISTINCT BranchName
        FROM ALX_Branches

        SELECT @n = @@ROWCOUNT

    WHILE @n >= @i
    BEGIN
        SELECT @BranchName = BranchName
        FROM @t
        WHERE id = @i
        SELECT @columns = (
                            SELECT DISTINCT ','+QUOTENAME([SubInventory])
                            FROM #MyTempTable
                            WHERE [BranchName] = @BranchName
                            FOR XML PATH('')
                          )

        SELECT @sql = N'--
                    SELECT *
                    FROM (
                            SELECT [BranchID],
                                   [SubInventory],
                                   [Product],
                                   [Stock],
                                   [Weighted Average Rate] 
                            FROM #MyTempTable
                            WHERE [BranchName] = ''' +@BranchName +'''
                          ) as t
                    PIVOT (
                            MAX([Stock]) FOR [SubInventory] IN ('+STUFF(@columns,1,1,'')+')

                          ) as pvt'

        EXEC sp_executesql @sql
        SET @i = @i+1
    END

SSIS 2005 - Get previous day in an Expression

Just need help on this as I am new at SSIS. I got an expression but i want yesterday. Not today

"Daily "+ (RIGHT("0" + (DT_STR,4,1252) DatePart("yyyy",getdate()),4))+(RIGHT("0" + (DT_STR,4,1252) DatePart("mm",getdate()),2))+(RIGHT("0" + (DT_STR,4,1252) DatePart("dd",getdate()),2))+".CSV"

Currently it looks like this

Daily 20161006.CSV

I want is

Daily 20161005.CSV

Need help grouping multiple rows into single string based on GROUP BY?

This question may have been asked before but I've been doing some digging and can't find anything quite like it. Thanks in advance

EDIT: Totally forgot. We're using SQL 2005 (otherwise I'd likely just use GROUP_CONCAT)

Sample Table:

ID  CATEGORY    ATTRIBUTE
1   A   Piano
1   A   Saxophone
1   B   Jazz
1   C   Mellow
1   C   Pensive
2   A   Drums
2   A   Guitar
2   B   Rock
2   B   Classics
2   C   Upbeat
2   C   High Energy

Desired Output:

ID  CATEGORY    ATTRIBUTE
1   A   Piano, Saxophone
1   B   Jazz
1   C   Mellow, Pensive
2   A   Drums, Guitar
2   B   Rock, Classics
2   C   Upbeat, High Energy

Latin1_General_100_CI_AS Collation on SQL Server 2005

We currently have a SQL 2005 Server which we use as a report server.

This now needs to link to a Navision database on a SQL 2014 server using collation Latin1_General_100_CI_AS.

I have happily set up a linked server on SQL 2005 but any queries using it fail.

Can anyone advise on how to add Latin1_General_100_CI_AS collation on SQL Server 2005?

Is this even possible or will have to upgrade the SQL Server?

Kind regards

Rob

SQL server 2005 - Joining two tables and two columns

In SQL server 2005, by joining two table's two columns, how do we get the value by matching first table two columns to the second table two columns and value zero for non-matching columns?

Below are the sample tables:

Table 1:
City    Vehicle      Count
London  Two wheeler  834
NewYork Four wheeler 60
NewYork Two wheeler  3
Sydney  Four wheeler 514
Sydney  Two wheeler  4551

Table 2:
City    Vehicle     Count
London  Two wheeler 5
NewYork Two wheeler 2
Sydney  Two wheeler 16

The expected output:
City    Vehicle      Count
London  Two wheeler  5
NewYork Four wheeler 0
NewYork Two wheeler  2
Sydney  Four wheeler 0
Sydney  Two wheeler  16

I did this successful on MS Excel using Pivot Table formula:

{=INDEX($L$6:$L$550,MATCH(F6,IF($K$6:$K$550=G6,$J$6:$J$550),0))}

Restoring database from sql2014 to sql2005

I want restore the database from sqlserver 2014 to sqlserver 2005. So I have tried it by generating script from sqlserver 2014 and executing in sqlserver 2005 version. This database contains around 4 GB size. We got an error during the execution of the query

“Could not allocate space for object 'sys.syscolpars'.'clst' in database 'xy' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.”

We are getting error an error on following code

CREATE TABLE [dbo].[rff_aud](
        [rff_id] [int] NOT NULL,
        [rff_ud] [varchar](35) NOT NULL,
        [auth_number] [varchar](35) NULL,
        [rff_status_id] [int] NOT NULL,
        [rff_urgency_id] [int] NOT NULL,
        [elig_id] [int] NOT NULL,
        [rff_provider_id] [int] NOT NULL,
        [rff_category_id] [int] NULL
) 
SET ANSI_PADDING ON
ALTER TABLE [dbo].[rff_aud] ADD [pri_diagnosiscode_ud] [varchar](35) NULL

  

We are using the sql server version Microsoft SQL Server 2005 - 9.00.1399.06.

Also my database configuration is

Can any one help us why we are getting the error on my database. But I could execute sucessfully the above code on new database on the same server.

VB.net attaching mdf file error

I have .mdf file stored in a pendrive and I used to connect that to sql server.

But suddenly it has stopped working and throws an ERROR:

"An attempt to attach and auto-named DB failed. The file doesn't exists or it is located on UNC or share.."

Visual studio 2010 and SQL server 2005