Error "provided statistics stream is corrupt" on SQL Server 2005

I've got an error "provided statistics stream is corrupt" on SQL Server 2005. Is there any root cause for this error? Really appreciate for any comment in advance.

isnumeric function

I have table with data a mixture of alpha and numeric, I'm trying to find the way to extract value only if it's numeric

PSDcode2:
========
631302
631308
631309
631301
 STATE
 STATE
ON TWP

My select statement will work just fine, but I just is there better way of doing this? thanks

case when PS.PSDcode2 in (' STATE','ON TWP') then 'NULL' else PS.PSDcode2  end as PSDcode2,

XML column compare in SQl server 2005

I want to compare two XML columns with multiple rows in SQL Server 2005.

Table structure is as below

CREATE TABLE [dbo].[UpdationLog](
    [LogID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [CustID] [int] NOT NULL,
    [OldValue] [xml] NOT NULL,
    [NewValue] [xml] NOT NULL,
 CONSTRAINT [PK_UpdationLog] PRIMARY KEY CLUSTERED 
(
    [LogID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO [dbo].[UpdationLog] VALUES
(1526,'<ccm CustID="1526" CustName="Teja" Address="Bangalore"/>','<ccm CustID="1526" CustName="Tejas" Address="Bengaluru"/>'),
(1245,'<ccm CustID="1245" CustName="Abhi" Address="Andhra"/>','<ccm CustID="1245" CustName="Abhilash" Address="Andra Pradesh"/>'),
(1145,'<ccm CustID="1145" CustName="Abhi" Address="Assam"/>','<ccm CustID="1145" CustName="Abhinandan" Address="Assam"/>')

I want to compare XML column OldValue and NewValue and display updated records.

Desired Output

|-------|-------------|---------------|------------
|CustID |   Attribute |     OldValue  | NewValue
|-------|-------------|---------------|---------
|1526   | CustName    |  Teja         | Tejas
|1526   | Address     | Bangalore     | Bengaluru
|1245   | CustName    |  Abhi         | Abhilash
|1245   | Address     |  Andhra       | Andra Pradesh
|1145   | CustName    |  Abhi         | Abhinandan

http://ift.tt/2e3Glvw

VB.net how to create table using xml and displaying the table on the webpage

Can somebody please help me to amend this code because i tried running but the table didnt appear on the page. I'm new to XML and somebody gave me this snippet saying that XML coding is more reliable for tables.

The code probably works but i dont know how to display it to the page. I tried searching but i saw that many also did nearly the same thing but why didnt it work for me? While, some others made another xml file to keep the data, but in my case, i'm actually planning to retrieve data from a MS SQL 2005 database and display it in a table. What am i missing here?

Dim dt As DataTable = Me.GetData()
    For Each row As DataRow In dt.Rows
        Dim tr As XElement = <tr></tr>

        For Each column In dt.Columns
            Dim td As XElement = <td></td>
            td.Value = row(column.Ordinal).ToString()

            tr.Add(td)
        Next

        table.Add(tr)
    Next

Need help concat multiple fields into one based off of other fields

I asked a similar question recently but, unfortunately, the nature of the request changed. Been looking at this code for a bit but my brain is friend and I can't quite think of a way to get this working as needed.

Working in SQL 2005 and this will encompass hundreds of values like this.

Thanks in advance!

Sample Table

+----+-------------+---------------------+---------------------+
| ID |    CLASS    |  PARENT_ATTRIBUTE   |      ATTRIBUTE      |
+----+-------------+---------------------+---------------------+
|  1 | Genre       | A                   | Hip Hop             |
|  1 | Genre       | B                   | Pop                 |
|  1 | Instruments | Keyboards           | Synth               |
|  1 | Instruments | Keyboards           | Grand Piano         |
|  1 | Instruments | Drums               | Kit                 |
|  1 | Moods       | Positive/Optimistic | Uplifting/Inspiring |
|  1 | Moods       | Positive/Optimistic | Happy/Feel Good     |
|  1 | Moods       | Musical Feel        | Pulsing             |
+----+-------------+---------------------+---------------------+

Desired Output

+----+----------------------------------------------------------------------------------------------------------+---------------------------------------------------------+
| ID | MOODS                                                                                                    | INSTRUMENTS                                             |
| 1  |Positive/Optimistic - Uplifting/Inspiring, Positive/Optimistic -  Happy/Feel Good, Musical Feel - Pulsing | Keyboards - Synth, Keyboards - Grand Piano, Drums - Kit |
+----+----------------------------------------------------------------------------------------------------------+---------------------------------------------------------+

Sum Sql column with join

I'm trying this code:

Select  C.CustomerNum
      , C.Coupon
      , C.name
      , C.Surname
      , Sum(P.Points)
From    customers C
Join    Points P
        On P.CustomerNum = C.CustomerNum
Where   C.Coupon = 'xxx-xxx-xxx-x'; 

I'm getting error:

Msg 8118, Level 16, State 1, Line 1 Column 'C.CustomerNum' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Convert into column using delimited PIPE in MS SQL

Present value

All (one column)

--

43 | 00043 | 22/09/2016 | Ooredoo - AuU TX Projects | Raj singh

I want it should be in separate column like given below

ID Key Date Company Owner

43 00043 22/09/2016 Ooredoo - AuU TX Projects Raj singh

Pull up the most recent record including joining 2 tables and filters

I have seen a lot of posts on pulling up the most recent record. I haven't been able to find one that includes joining another table and filters.

What I need is information regarding the most recent document (record) created, but only if it meets certain criteria. PLUS I need to pull in some data from another table.

s504Plans Table

    Student ID   |   Firstname   |   Startdate   |   Status
    ----------       ---------       ---------       ------
    111111            Johnny         1/5/2015          F
    222222            Sue            4/7/2016          I
    333333            Barb           2/5/2016          F
    111111            Johnny         2/1/2016          F

Cases Table

    Student ID   |   School   |   
    ----------       ------
    111111           Franklin
    222222           Eisenhower
    333333           Franklin

And the results I'd like to see are only the most recent document where the status of the document is F...

    Student ID  |  Firstname  |  Startdate  |  Status  |   School
    ----------     ---------     ---------     ------      ------
    111111          Johnny       2/1/2016        F         Franklin
    333333          Barb         2/5/2016        F         Franklin

Thanks!

Showing "Invalid object name " in sqlServer?

While Executing the Following query it showing the Invalid object name '#temp1'. can any body knows the error occurred due to which reason

SELECT @sql = N'--
                    SELECT * into #temp1
                    FROM (
                            SELECT [BranchID],
                            [SubInventory],
                            [Product],
                            [Stock] 

                    FROM #MyTempTable
                    WHERE [BranchName] = ''' +@BranchName +'''
                    ) as t
                PIVOT (
                MAX([Stock]) FOR [SubInventory] IN ('+STUFF(@columns,1,1,'')+')

        ) as pvt'

    EXEC sp_executesql @sql

    select * from #tmepeee

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