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

Error Converting NVARCHAR to DATETIME

I create a temp table

CREATE TABLE #Test(theDate DATETIME)

I use this query to insert data into the temp table

INSERT INTO #Test VALUES(CONVERT(NVARCHAR, '2016-09-30' 21))

It works on one database (SQL Server 2005), but gives error on another (SQL Server 2008). I can't remember the exact error, but it has something to do with 'Error converting NVARCHAR TO DATETIME'

Why is working on one database, but not another? Is there a special property to enforce error on mismatched type? I can't find information anywhere.

Distribution Transaction Completed - Error - SQL Server 2005

Trying to execute a stored procedure to update a certain data warehouse, however when I execute the stored procedure I get an error message stating:

error Msg 8525, Level 16, State 1, Line 1 Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction

After some Googling, it suggested to configure DTC settings, but I have already checked my MSDTC and it is already checked for allowing inbound and outbound for Transaction Manager Communication.

Any ideas to fix it?

Select last value from within multiple groupings (SQL Server)

I'm unsure how to both group and select the last value from within that group and could use some expertise.

This simple table (snippet of table below) holds the values of various timestamps for when a series of URLs I'm checking don't respond correctly. The yellow highlighted lines represent the data that I'm attempting to capture.

Essentially I'm trying to capture the last downtime timestamp for each grouping. failure_id will auto-increment with each entry.

I've got a partial success with the following:

WITH FAILURES AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY url_id ORDER BY failure_id DESC) AS lastFail
    FROM URL_FAILURES
)

and then building my actual SELECT statement with that data and the condition

WHERE
  lastFail = 1

...but it disregards the groupings that I need for last_check_success.

---

Microsoft SQL Server 2008 R2 (SP3)
Database Compatibility: SQL Server 2005

Not able to Append @FIPSName value on the selected Query using Procedure

ALTER PROCEDURE [dbo].[usp_RmsExecuteValidationRule]        

-- Parameters           
 @nRuleId INT     

AS BEGIN        
-- Local variables        
DECLARE @sqlstat AS NVARCHAR(MAX)        
DECLARE @params  AS NVARCHAR(MAX)        
DECLARE @RULE_QUERY NVARCHAR(MAX)     
DECLARE @FIPS  varchar(5) 


SET @sqlstat = N'SELECT @RULE_QUERY=RULE_QUERY from GdmValidationRuleMaster where RULE_ID = @nRuleId'        
--SET @sqlstat =  N'SELECT ' + @RULE_QUERY + '=RULE_QUERY from GdmValidationRuleMaster where RULE_ID = ' + @nRuleId 

SET @params = N'@nRuleId INT,   @RULE_QUERY NVARCHAR(MAX) OUTPUT'        
EXEC sp_executesql         
    @sqlstat,        
    @params,        
    @nRuleId = @nRuleId,     
    @RULE_QUERY= @RULE_QUERY OUTPUT 

-- Output   

SELECT @RULE_QUERY            

END

When we Exec usp_RmsExecuteValidationRule 1 It Returns @RULE_QUERY i.e:

SELECT GDMID AS GDM_ID from @FIPSName+CADMIN1 WHERE INTPRIORITY IS NULL

Here I have intentionally added @FIPSName+ As prefix because

I have another table LCountry table which FIPS coulumn

When we It Returns

FIPS
----
GM
FR
UK
AN
BE
BU
BL
BR
BH
CA
AU
VT
CI
CH
CO
CS
EZ
AS
DA
EC
SP
AR
BD
BB
BF
AV
AA
AC
DO
CU
CJ
DR
GP
GJ
HA
JM
ST
RQ
SC
MB
MH
XN
XM
XJ
VC
VI
TK
TD
TB
RN
XL
XK
VQ
GR
GT
GQ
HK
HO
HU
ID
EI
IS
GZ
XC
WE
IN
IT
SM
JA
LS
LU
MX
NU
NL
NO
NZ
PM
PE
RP
PL
PO
RO
SW
SI
LO
ES
TU
TW
SZ
VE
SN
SB
PF
PG
PC
NE
NF
MQ
US
SX
TE
TT
WQ
BQ
DQ
BS
CK
CQ
CR
AY
AT
BV
LQ
KQ
JQ
JU
KT
IO
IP

Using Loop I am able fetch data using ObjectID of Lcountry.

DECLARE @LoopCounter INT , @MaxBcountryObjId INT, 
        @FIPSName NVARCHAR(100)
SELECT @LoopCounter = min(OBJECTID) , @MaxBcountryObjId = max(OBJECTID) 
FROM dbo.LCOUNTRY

WHILE(@LoopCounter IS NOT NULL
      AND @LoopCounter <= @MaxBcountryObjId)
BEGIN
   SELECT @FIPSName = FIPS
   FROM dbo.LCOUNTRY WHERE OBJECTID = @LoopCounter

   PRINT @FIPSName  
   SET @LoopCounter  = @LoopCounter  + 1 

This query Return @FIPSName.

QUESTION

I want to This return Value @FIPName Should Appends on Rule_Query return Value. and the same value can be executed too.

For Example :

SELECT GDMID AS GDM_ID from @FIPSName+CADMIN1 WHERE INTPRIORITY IS NULL
Changes Into

SELECT GDMID AS GDM_ID from GM+CADMIN1 WHERE INTPRIORITY IS NULL
Changes Into 

GDMID
------         (The above query must be executed and Return GDMID.)
1198

Why date is not taking 13/09/2016

I am checking condition for 12/09/2016 to 13/09/2016 but it is not showing me data for 13/09/2016 and giving error

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Here is my query

SELECT DISTINCT  
   b.mkey ,  a.N_UserMkey, cuser_id,isnull(a.N_UserMkey,cuser_id) aa,
   ISNULL(b.first_name + ' ', '')  
   + ISNULL(b.last_name, '') NAME, convert(varchar,a.U_datetime,103) Action_Date
  FROM      inward_doc_tracking_trl a  
   INNER JOIN user_mst b ON isnull(a.N_UserMkey,cuser_id) = b.mkey  
  WHERE 
  convert(datetime,a.U_datetime,103) 
    BETWEEN convert(varchar,'12/09/2016',103)
  AND convert(varchar,'13/09/2016',103)
   and b.mkey=2357

Check if two different values appear successively in a table in SQL?

There's a table that looks like this (there are more columns, but not relevant to the query):

DocumentId | DocumentStateId | TransitionMoment
111222       -2                2016-04-21
111222       -1                2016-04-22
111222       -7                2016-04-23
111222       -5                2016-04-24
111222       -6                2016-04-25
111222       -1                2016-04-26
333141       -2                2016-05-01
333141       -7                2016-05-09
333141       -6                2016-05-10
333141       -3                2016-05-15
777525       -1                2016-02-10
777525       -6                2016-02-10
777525       -7                2016-02-10
777525       -5                2016-02-10
777525       -2                2016-02-10

What options do I have to check whether a document has went from state "-7" to state "-6" consecutively (without transitioning through other states in between)? In the example Document no. 33141.

Thanks in advance!

How to SUM Only One Time Per UniqueId in SQL?

I have two tables that look roughly like this:

Table A

DocumentId (*is unique) DocumentDate
1                       2016-01-01
2                       2016-01-01
3                       2016-02-01
4                       2016-03-01

and Table B

ContractId SnapshotTimeId NetFinanced
1          20160231       300
1          20160331       300
1          20160431       300
2          20160231       450
2          20160331       450
2          20160431       450
3          20160331       500
3          20160431       500
4          20160431       150

I would like the final table to look something like this:

DocumentDate NetFinanced
2016-01-01   750
2016-02-01   500
2016-03-01   150

I have tried the following and it doesn't work:

SELECT A.DocumentDate, SUM(B.NetFinanced)
 FROM A
 JOIN B on B.ContractId=A.DocumentId
GROUP BY A.DocumentDate

Any ideas? Thanks in advance

Count how many rows are for a value in SQL?

I have a table that looks like this:

[ContractId]     [ContractDate] [SnapshotTimeId]    [DaysPastDue] [Exposure]

Int(not unique)  Datetime       Int(format20160431) Int           Int

The table is sorted by ContractId, ContractDate.

Now, I would like to add a 6th column, let's call it Unique, which has value 1 for the first ContractId value then adds 1 until it bumps across the next ContractId. Basically, I want to know how many rows I have for each ContractId and put the values, incrementally, in a column.

Edit: I want the output to look like this

>DocumentId ContractDate    SnapshottimeId  DPD Exposure Unique
>1          31-Aug-15       31-Aug-15       0   500      1
>1          31-Aug-15       30-Sep-15       5   450      2
>1          31-Aug-15       31-Oct-15       35  450      3
>1          31-Aug-15       30-Nov-15       7   350      4
>1          31-Aug-15       31-Dec-15       37  350      5
>1          31-Aug-15       31-Jan-16       67  340      6
>2          31-Aug-15       30-Jun-14       3   800      1
>2          31-Aug-15       31-Jul-14       15  760      2
>2          31-Aug-15       31-Aug-14       45  750      3
>2          31-Aug-15       30-Sep-14       75  750      4
>2          31-Aug-15       31-Oct-14       0   630      5
>2          31-Aug-15       30-Nov-14       15  590      6
>2          31-Aug-15       31-Dec-14       45  580      7

How to rollup columns

We are running SQL Server 2005. I am having problems rolling up columns.

create table group_roll 
(
    id_name int,
    full_name varchar(50),
    qty int
)
go

insert into group_roll (id_name, full_name, qty) 
values (1, 'john smith', 10)

insert into group_roll (id_name, full_name, qty) 
values (1, 'john smith', 40)

insert into group_roll (id_name, full_name, qty) 
values (1, 'jane smith', 50)

insert into group_roll (id_name, full_name, qty) 
values (1, 'dean smith', 10)

insert into group_roll (id_name, full_name, qty) 
values (2, 'frank white', 5)

insert into group_roll (id_name, full_name, qty) 
values (2, 'Ann white', 12)

insert into group_roll (id_name, full_name, qty) 
values (1, 'john smith', 8)

insert into group_roll (id_name, full_name, qty) 
values (2, 'frank white', 10)

insert into group_roll (id_name, full_name, qty) 
values (3, 'perry mason', 10)
go

select * 
from group_roll
order by id_name

Output:

   id_name       full_name          qty
   --------------------------------------
    1            john smith          10
    1            john smith          40
    1            jane smith          50
    1            dean smith          10
    1            john smith           8
    2            frank white         10
    2            frank white          5
    2            Ann white           12
    3            perry mason         10

I want the result to be rolled up into something like this

    id_name  name                                   qty
    ----------------------------------------------------
     1       john smith, jane smith, dean smith     118
     2       frank white, ann white                  27
     3       perry mason                             10

How do you code to rollup names and qty as shown?

Thank you,

Seyed

SQL Server: How to write query to bring data in particular format?

I have below sample data: Here Events are repeated twice.

Sample data

Now I want to write query to display data in the below format. Output data

I am trying to use RANK function. I am running out of ideas.... Anyone, Please help me with query. Thanks

The parameter "@parameter" is not the same type as the type it was created with. Drop and recreate the proc using a two-part name for the type

I have a error in SQL server 2005. Help me! Error:"The parameter "@parameter" is not the same type as the type it was created with. Drop and recreate the proc using a two-part name for the type." Thank you very much!

Add CDept_Id In first query for result

In my first query, I want to get CDept_Id. But CDept_Id column does not exist in inward_doc_tracking_hdr table.

It comes from inward_doc_tracking_trl table. like below

select CDept_id from inward_doc_tracking_trl where ref_mkey in
(Select mkey from inward_doc_tracking_hdr where doc_no = 'IW/HU/16/42')

So, From this. I get CDept_Id. Now I want to add this in my below query.

Select mkey,Delivered_By,Department_Id, 
 (Select mkey from erp190516.dbo.emp_mst where mkey  in 
 (select employee_mkey from erp190516.dbo.user_mst where mkey =   To_User))User_Id, 
Doc_Type,Email_Id,Ref_No,convert(varchar(25),Ref_date,103) Ref_date,
Inward_Amt, Remarks, party_name, disp_through
from erp190516.dbo.inward_doc_tracking_hdr, CDept_id  -- add CDept_id here
 where doc_no = 'IW/HU/16/42'  
 and Status_Flag = '13'

How to add this

UPDATE

inward_doc_tracking_hdr mkey is equal to inward_doc_tracking_trl ref_mkey

SQL Replaced Stored Procedure

Is there a way to recover replaced Stored Procedure after restoration. I forgot to create a backup before replacing the database.

SQL Server JOIN returns Cartesian result

I am trying to combine two views and it returns a Cartesian result.

View 1 192 (this view has inner join with the order id for the expired keys)

View 2 70 (this view has inner join with order id and sale date to get the renewed keys)

When I join these views (any of the joins), it results in 13340 rows returned.

Thanks

ETL from SQL Server 2005 to SQL Server Management Studio 2016

I am trying to copy a database from sql server 2005 server to another server in sql server managment studio 2016. (server1.database1) to (server2.database1) I tried detach and attached method and it did not work. I also tried to copy the data base and it did not work either! I need all of tables and views and pr! finally I could copy all of tables and pr's but problem is I can not have the views as a view and they just saved as a table. to solve this issue I thought if I copy the query from server1.views and execute that query in server2.database1.views I will have the view since I have all objects copied in server2.database1. but I get some errors: one was "format" function that they used in sql server 2005 which should be "convert" now. I changed all of that "format" functions and executed the query and now I left with this errors: Msg 15135, Level 16, State 8, Procedure sp_addextendedproperty, Line 37 [Batch Start Line 35] Object is invalid. Extended properties are not permitted on 'dbo.v_RASAM', or the object does not exist.

So view that I want to copy calls : dbo.v_RASAM And I am sure (because I checked the procedure name sp_addextendedproperty) sp_addextendedproperty exist! So question that I have is possible that procedure follows some statements that needs to be change since "view" had functions that are not available in sql server management studio 2016?(this maybe sounds stupid but I think of any possible reason that may cause this issue) how can I have those views copy to server2.database1 any other way that I have not tried?

any help or thought will be much appreciate! Thank you so much for your time!

Increase Varchar in a SQL2005 field and connected erp

I don't know if you can help me in this problem. I've an ERP connected to SQL 2005 DB. I need to increase the characters permitted in the description of my articles... If I increare the varchar(80) that I've in the description column it's enough? Increasing it I can damage my DB or my software?

Find a constraint column name using INFORMATION_SCHEMA.TABLE_CONSTRAINTS in SQL

I have a primary key constraint set up in a table - "tCustomerSessions" with the name "PK_tCustomerSessions".

Below is my query -

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'tCustomerSessions'
    AND Constraint_Type = 'PRIMARY KEY'

Here how do i get the name of the column on which the primary key constraint has been set up ?

check column ISNULL and replace another column

I have column as UserMail like below

U.Email AS UserEmail

I want to replace with RAU.Email if U.Email is isNULL

Error ASP.NET Core EF6 and SQL Server 2005: The version of SQL Server in use does not support datatype 'datetime2'

I receive the following exception when attempting to update a database record even though I was passing in the correct datetime values to the controller.

System.ArgumentException: The version of SQL Server in use does not support datatype 'datetime2'

Other stackoverflow topics suggest either the datetime has not been set or to edit the edmx file, which in this case, doesn't exist. I have tested the application with a localdb context in Visual Studio 2015 but I only receive an error when trying to connect to the SQL Server 2005 DbContext.

Is this exception due to an incompatibility with SQL Server 2005 or is there a workaround which I'm missing?

stored procedure within stored procedure

create  procedure  spGoti

@WeekNumber nvarchar(255)
as
begin


execute spPointsUpdate @WeekNumber

execute spGivebadges @WeekNumber

execute spLevelField @WeekNumber
execute spAddNewWeekDataToTotalOfEmployeeTable @WeekNumber

execute spTop15Overall
execute spWeeklytop15 @WeekNumber

end

in the above code i have written stored procedures within stored procedures. I have a peculiar problem here.the second stored procedure here "spGivebadges @WeekNumber" uses the output of previous stored procedure "spPointsUpdate @WeekNumber" here.

The problem is if i execute the parent stored procedure i.e "spGoti" the second stored procedure "spGivebadges @WeekNumber" doesnot get executed .But when i RUN THE PARENT STORED PROCEDURE i.e "spGoti" THE SECOND TIME THE SECOND STORED PROC GETS EXECUTED WITHOUT ANY PROBLEM.

All the other stored procedures within the parent gets executed the first time itself,though some are using output of another.

wHY IS THIS HAPPENING ? ANYONE FACED SOMETHING SIMILAR BEFORE OR ANY SOLUTION YOU CAN THINK OF ? THANKS

Create View "The name is not a valid identifier" [on hold]

I have a dynamic crosstab which I need to build every month. I thought I could use a view to do this, and have cobbled the following together

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(YrMth) 
                from tbl_CRO_AverageAge where YrMth>=DATEADD(M,-11, DATEADD(DD,day(getdate())+1,GETDATE())) and 
YrMth<=DATEADD(DD,day(getdate())+1,GETDATE()) ORDER BY YrMth
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')
    --print @cols

set @query = 'create view [vw_CRO_AverageAge] AS SELECT ' + @cols + ' from 
         (
            select YrMth, sum(AverageAge) over(partition by YrMth) Age FROM
              tbl_CRO_AverageAge
              where YrMth>=DATEADD(M,-11, DATEADD(DD,day(getdate())+1,GETDATE())) and 
YrMth<=DATEADD(DD,day(getdate())+1,GETDATE())
        ) x
        pivot 
        (
            sum(Age)
            for YrMth in (' + @cols + ')
        ) p '

print @query
execute @query

The output of the print can be run fine, but the execute returns the error. I've clearly made some simple error but I can't see it, as I'm not familiar at all with this sort of dynamic code. Is what I'm attempting possible in this manner, or have I gone about it all wrong?

Reward points and create new fields in original table

I have a table here :

I want to reward a gold and a silver(i.e a value of 1 wherever applicable,else 0 )to top 2 persons by looking at pointsRewarded field.

I already have the first table created.In the first table, the two new fields should be created i.e the gold and silver fields.

i want the output to be something like this:

Please help me with the query or give me some suggestions on how to proceed.

I have some query suggested to be:

select t.*,
   (case when rnk = 1 then 1 else 0 end) as gold,
   (case when rnk = 2 then 1 else 0 end) as silver
from (select t.*,
     dense_rank() over (partition by week order by pointsrewarded) as rnk
from t
 ) t;

But I want the new fields i.e rank,gold and silver to be added in the first table.Don't want it as a view.I want the new fields to be hardcoded in the original table.

Please help me with the query or give me some suggestions on how to proceed.

Thanks a lot.

Return duplicates by comparing each records of the same table in the sql server

I have table like below.I wanted to get the duplicate records.Here the condition

I need find duplicate on subscriber whoes status = 1 i.e. active and for current year it has the multiple records by compairing start_date and end_date. I have around more than 5000 records in the DB.Showing here few sample example.

id      pkg_id  start_date  end_date    status  subscriber_id
2857206 9128    8/31/2014   8/31/2015   2       3031103
2857207 9128    12/22/2015  12/22/2016  1       3031103
3066285 10308   8/5/2016    8/4/2018    1       3031103
2857206 9128    8/31/2013   8/31/2015   2       3031104
2857207 9128    10/20/2015  11/22/2016  1       3031104
3066285 10308   7/5/2016    7/4/2018    1       3031104
3066285 10308   8/5/2016    8/4/2018    2       3031105

I tried below's query but not worked for all records:

select * from dbo.consumer_subsc 
where subscriber_id in (3031103) and status=1 and year(getdate()) >= year(start_date) and
year(getdate()) <= year(end_date) and subscriber_id in (select T.subscriber_id from (select subscriber_id,count(subscriber_id) as cnt from dbo.consumer_subsc where
status=1 group by subscriber_id having count(subscriber_id) > 1)T
) order by subscriber_id desc

The problem is I'm not able to find a way, where each row can be compared with each other with above date condition.I should get the result like below as duplicate:

id      pkg_id  start_date  end_date    status  subscriber_id
2857207 9128    12/22/2015  12/22/2016  1       3031103
3066285 10308   8/5/2016    8/4/2018    1       3031103
2857207 9128    10/20/2015  11/22/2016  1       3031104
3066285 10308   7/5/2016    7/4/2018    1       3031104

How to connect remote sql server in my ssms with windows authentication?

I'm new in sql server,i install the sql server in my local network pc and in my pc want to connect to that sql server with windows authentication,for that purpose i click browse for more option and click network server and wait to show that sql server,show that and try to connect but show me this error message:

what happen?i try to find that problem solution in google but i can't understand any solution,thanks.

Retrieving the timezone of a recorded datetime in SQL Server (2005)

Let testdate be a table with a datetime column d. I executed this sql request a few times and changed the OS timezone between each execution.

INSERT INTO [testdate] ([d])
     VALUES (CAST(GETDATE() AS DATETIME))

I got this result :

     ________________________
    | d (datetime)           |
.---|------------------------|
| 1 | 2016-09-08 15:15:28.847|
| 2 | 2016-09-08 18:15:45.407|
| 3 | 2016-09-08 11:17:23.317|
°----------------------------°

The first was played at GMT+6, second at GMT+9, last at GMT+1 (summer time). I believed I'll get all these rows at current timezone.

Are these datetime values stored as a "snapshot" ? Or is there a way to get the timezone for each value ?

DBLink performance issue after upgrading to 11.2.0.4 writing to SQL Server

We have been writing information to a MS Sql Server database (v2005) for a few years now without much difficulty. After upgrading our Oracle db to 11.2.0.4 (from 11.1.0.7) this past weekend we are finding the process extremely slow. For example, to write 100 records used to take 5 seconds and now takes 50. With almost 30,000 records, it's going to be a problem if we can't figure out how to do it better. The destination table is truncated before beginning. Here is the code we used for the 100 record test. Any tips you have to speed this up are appreciated.

BEGIN
  FOR rec IN
  (SELECT * FROM my_orders_v WHERE  item_number like  '52548%'
  )
  LOOP
    INSERT
INTO bill_test_so@sqldatabase
  (
    ORDER_NUMBER ,
    item_number ,
    ITEM_DESCRIPTION ,
    QUANTITY ,
    uom_code,
    ORDER_TYPE_TEXT ,
    NEW_DUE_DATE ,
    ACTION ,
    PLANNER_CODE ,
    ORGANIZATION_ID ,
    INVENTORY_ITEM_ID ,
    LAST_UPDATE_DATE ,
    LAST_UPDATED_BY ,
    CREATED_BY ,
    CREATION_DATE ,
    TRANSACTION_ID
  )
  VALUES
  (
    rec.ORDER_NUMBER ,
    rec.item_number ,
    rec.DESCRIPTION ,
    rec.QUANTITY ,
    rec.uom_code,
    rec.ORDER_TYPE_TEXT ,
    rec.NEW_DUE_DATE ,
    rec.ACTION ,
    rec.PLANNER_CODE ,
    rec.ORGANIZATION_ID ,
    rec.INVENTORY_ITEM_ID ,
    rec.LAST_UPDATE_DATE ,
    rec.LAST_UPDATED_BY ,
    rec.CREATED_BY ,
    rec.CREATION_DATE ,
    rec.TRANSACTION_ID
  );
  END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
  retcode := 2;
  errbuf  := SQLERRM;
  ROLLBACK;
END ;

Thanks for your help! Bill

How to copy stored procedure from one database to another database on same server dynamically

I tried like this:

SELECT @def = [definition] 
FROM [@from_db].sys.sql_modules 
WHERE object_id = OBJECT_ID('MyProcedure') 

EXEC(@def); 

or any other way same like this.

sqlcmd utility not showing greek letters

First of all I am using SQL Server 9.00.4035.00 which is SQL Server 2005 SP3.

I am using the below command to fetch the results from a table which includes columns with either english or greek characters.

sqlcmd -W -S SERVER_NAME -d DATABASE_NAME -U USER_NAME -P PASSWORD -s "|" -Q "set nocount on; set ansi_warnings off;SELECT * from DATABASE.TABLE;" 

This command returns rows with english characters ok, but when it comes to greek characters I only get ??????

For example instead of

1110|20160907|ΓΙΩΡΓΟΣ

I am getting

1110|20160907|????????

I already tried the -f and -u options but none of them seems to work. Here is how I used them:

650001 is for UTF-8 according with this link

sqlcmd -W -S SERVER_NAME -d DATABASE_NAME -U USER_NAME -P PASSWORD -f 65001 -s "|" -Q "set nocount on; set ansi_warnings off;SELECT * from DATABASE.TABLE;" 

1253 is for greek accoring to this book

sqlcmd -W -S SERVER_NAME -d DATABASE_NAME -U USER_NAME -P PASSWORD -f 1253 -s "|" -Q "set nocount on; set ansi_warnings off;SELECT * from DATABASE.TABLE;" 

-u option

sqlcmd -W -S SERVER_NAME -d DATABASE_NAME -U USER_NAME -P PASSWORD -u -s "|" -Q "set nocount on; set ansi_warnings off;SELECT * from DATABASE.TABLE;" 

Add values of a column and display added result in new table

The table with the data that I have

In the above table I have the columns : weekNumber , weeklyHours , points_Rewarded.

There are four employees : a,b,c,d

I have the values for week1,week2,week3, and so on ( I can have data for many more weeks also such as week4,week5, etc)

I want to write a query such that after passing the query I get the total of the weeklyHours and points_Rewarded for each employee in a new table. The kind of table that the query should give me is here the desired table that I want after passing the query

Please help me with the query.

Thanks in advance.

find consecutive info and reward points accordingly

In the table,I have the columns

: WeekNumber,WeeklyHoursLogged,points_Rewarded

I want to write a query to find out if any employee (see EmployeeName column) has less than 40 (<40) in WeeklyHoursLogged column for week32 and week33 (both i.e consecutively for both week32 and week33).

If the condition is true i.e the employee has less than 40 (<40) in WeeklyHoursLogged column for week32 and week33 (both) then subtract 25 points from the Points_Rewarded column.

Please help me with the query. Refer the below link for table schema.

Thanks in advance.

Table for the above question

sql query to find two consecutive value and reward points accordingly

Table for below question

In the table,I have the columns : WeekNumber,WeeklyHoursLogged,points_Rewarded .I want to write a query to find out if any employee ( see EmployeeName column) has less than 40 (<40) in WeeklyHoursLogged column for week32 and week33 (both i.e consecutively for both week32 and week33).If the condition is true i.e the employee has less than 40 (<40) in WeeklyHoursLogged column for week32 and week33 (both) then subtract 25 points from the Points_Rewarded column. Please help me with the query.table in above link.Thanks in advance.

SQL Server Database Transaction Log File size increased dramatically

After a backup, I noticed size of SQL Server Database Transaction Log File increased dramatically. We were having 10 GB free space, now I could see only 9 MB.

Where should I check to find the root cause of this issue ?

MS SQL Union all & Group by clause not working

I have a table as per below detail:-

CREATE TABLE ITEM_DIM
(DOCKET_NO VARCHAR(9),DOC_DT DATETIME,GL_CODE VARCHAR(10),ITEM_CD VARCHAR(15),
ITEM_DIMENSION VARCHAR(100),LOT_NOS VARCHAR(15),
QTY1 DECIMAL(14,6),QTY2 DECIMAL(14,6),QTY3 DECIMAL(14,6),QTY4 DECIMAL(14,6),QTY5 DECIMAL(14,6),
QTY6 DECIMAL(14,6),QTY7 DECIMAL(14,6),QTY8 DECIMAL(14,6),QTY9 DECIMAL(14,6),QTY10 DECIMAL(14,6)
,QTY11 DECIMAL(14,6),QTY12 DECIMAL(14,6),QTY14 DECIMAL(14,6),QTY15 DECIMAL(14,6),QTY16 DECIMAL(14,6))

inserted statement to insert data into table :-

INSERT INTO ITEM_DIM (DOCKET_NO,DOC_DT,GL_CODE ,ITEM_CD ,ITEM_DIMENSION ,LOT_NOS ,QTY1,QTY2,QTY3,QTY4,QTY5,QTY6,QTY7,QTY8) values
('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','INNER DIA', 'AA8/ZZ16', 40.1,40,39.91,39.92,39.93,39.94,39.95,39.96),
('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','INNER DIA 1', 'AA8/ZZ16', 37.8,37.71,37.72,37.73,37.74,37.75,37.76,37.77),
('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','THICKNESS ', 'AA8/ZZ16', 0.88,0.72,0.73,0.74,0.75,0.76,0.77,0.77),
('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','THICKNESS 1', 'AA8/ZZ16', 0.71,0.72,0.73,0.74,0.75,0.76,0.77,0.78)

Select statement to display data :-
SELECT DOCKET_NO,DOC_DT,GL_CODE PARTY_CD,ITEM_CD ,ITEM_DIMENSION ,LOT_NOS ,QTY1,QTY2,QTY3,QTY4,QTY5,QTY6,QTY7,QTY8 FROM ITEM_DIM

i m trying to get result as per attached image "Desire_Result":-

so i write below query to get desire result:-

SELECT DOC_DT,PARTY_CD,ITEM_CD,LOT_NOS,INNER_DIA,THK FROM
(
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,QTY1 INNER_DIA,0 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,QTY2 INNER_DIA,0 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,QTY3 INNER_DIA,0 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,QTY4 INNER_DIA,0 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,QTY5 INNER_DIA,0 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,QTY6 INNER_DIA,0 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,QTY7 INNER_DIA,0 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,QTY8 INNER_DIA,0 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY1 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY2 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY3 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY4 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY5 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY6 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY7 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY8 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')) Z 

but my query displaying result as per attached image "My_Qry_Result" instead of desire result.

i also tried joins but not getting desire data So pls help me to get desire result thanxxxx

sql server 2005 srvice pack 3 and 4

I am working in a company with 3000 windows server 2003 with sql server 2005 installed; we have some thing like atm devices that read record (information about buy) from sql server and people purchase mony from that device; idea was that we must install sql server service packs to better performance to read records from sql server; but after install packages all off servers become slower than past and now we can not unistall packages; what we should do? Please help us; thanks:-(

Coldfusion cfthread MSSQL returning multiple records

we are sending almost million email. our process runs in cfthread. MSSQL gives recordset, when we loop through emails, we are updating records as sent.

when we run email sending process every one minute it works fine, when we run every 30 seconds. it start feeding records multiple times.

although we flag records when we pull as pulled but still facing issues. It look like when 1st request is in process, 2nd request comes before updating as pulled flag it serves again same record.

i don't want to lock the table because while, im pulling records, im sending emails and updating records as sent in same table.

please advice me.

Script to run SQL upgrade to 2012 unattended

I need a script to run silent and unattended to upgrade from 2005 to 2012. Backup the SQL databases, remove SQL 2005, Upgrade to 2012 and attach preexisting databases to the newly upgraded 2012. I've noticed that when upgrading to 2012, there are two instances of SqL ( both 2005 and 2012) sharing the same databases, hence creating a lag time in production. This is needed for an upgrade of around 1000 sites. Thank you

Dealing with out of range value on varchar date conversion

I'm attempting to convert dates input in our system as text in the format YYYYMMDD into dates. Unfortunately our system allows the use of the 31st of any month to signify that it's the last day of the month that's important, for some functions like interest accrual etc.

I have a date showing as 20160931 which obviously fails to convert via CONVERT(Datetime, CONVERT(Char(8), [FIELD])) and throws the out-of-range value error.

How can I overcome this, so that I can convert it to the correct value, in this case 30/09/2016.

Merge with delete nested query not giving delete count

I am trying merge with delete in that I want to take count of records getting deleted I have following snippet

MERGE  @DuplicateCurrencyData  DD USING 
        (SELECT PP.PAYMENTB_ID,PP.PPID,PP.BID FROM ProfileTable PP) tempTable
            ON DD.PPID = tempTable.PPID
            WHEN MATCHED THEN
                    UPDATE SET DD.PBID=tempTable.PAYMENTB_ID 


                    OUTPUT
                    $action, Updated.PBID 'Updated' INTO #MergeCountP;

It gives me error

Msg 4104, Level 16, State 1, Line 122
The multi-part identifier "Updated.PBID" could not be bound.

If I not take count Merge statement works fine , I am unable to understand how take rid of it

Giving alias name with space in sql

I have a column in my query for which I want to use its alias name.

Currently it is looking like this:

SELECT U.first_name + ' ' + U.last_name UserName,

But I want to use it as like below

SELECT U.first_name + ' ' + U.last_name as User Name,

I tried but I got error as:

Incorrect syntax near the keyword 'User'.

sql server - check to see if cast is possible then use in order clauses

I have a varchar(20) column, need to convert to int and use in order clauses. But things is okay when it is possible to convert. My code:

            SELECT    DISTINCT people.ParYear,CAST(people.ParID As int), people.Pfamily, people.Pname
            FROM               people 
                WHERE                        people.osID    = @osID 
                         AND people.shID    = @shID
                         AND people.ParYear = @ParYear
               ORDER BY        people.ParYear, CAST(people.ParID As int)

So how can I avoid error Message like Conversion failed!

How to Backup Database on Database Creation Date?

I have a scenario, On every start of the month new database is dynamically created and we have to take backup of newly created database. Full database backup is not required only backup is required for newly created databases, so how how can I take backup of these newly created databases?

Add a security layer to our SQL Servers (currently accesible from remote sql management studio)

We have a big system running with thousands of users (some from android apps, other from the web app, etc.).

The system is distributed, with databases in two locations (within the same country). In one location there are 5 servers in the same network, and each one has a copy of the database (via replication).

Among the software developers, a few have direct access to the production databases. Sometimes due to technical support requested by users to modify some operations not possible from the system itself, the developers/support team have to access the database directly and modify some records.

We know this is not the ideal manner of working. But it's been like this since years.

Recently we have found a few problems. One day one person updated hundreds of records from a table by mistake.

Since then we are analyzing how to improve this access.

We are looking for some way of improving the security. We would like to have a two-phase authentication system in place. Something that asks the user for two passwords when accessing from Sql Server Management Studio...

Is that possible? Or is there any other approach we can use to improve the security but still allow devs/support team to access the production database when necessary?

Users also (currenty) have access via remote desktop to all servers.

At least we would like to KNOW when this access is being done.

SQL 2005 : Email Profile that send me email even if removed

I'm having a problem with email service for SQL 2005. When i set my first backup maintenance plan, I've activated a mail that notify me if the backup was complete successfull. Then I've removed the mail alert but, I'm still receveing the email from the agent... How can I solve that?

Combining column name is giving error

I have two columns which I want to combine and show the data, I tried like below

select case when status='R' then 'Resign'
 when status='A' then 'Active' end as status1, 
  Program_name + ' ' + emp_card_no as program_details,
   * from GetEmployeeDetails
  Where emp_name ='ABHAY ASHOK MANE'and STATUS= 'A' ORDER BY EMP_NAME

but I am getting error as

Error converting data type varchar to numeric.

here is the sample data available

MS SQL error selecting count on a subquery

I am trying to select the count of the number of rows returned on a query. The query is

Select a.itm_cd, max(b.doc_num) ,max(c.text) 
from ist b,itm_trn a, ist_cmnt c  
where a.ist_seq_num = b.ist_seq_num 
and a.ist_seq_num = c.ist_seq_num  
and a.ist_wr_dt = b.ist_wr_dt 
and a.new_loc_cd  like 'BOX115' 
and a.ITT_CD = 'XFR' and a.create_dt >'21-AUG-16' 
group by a.itm_cd;

For this particular query I return 3 rows, I need to write a query that returns how many rows are returned.

I have tried this :

Select count(*) from 
(Select a.itm_cd, max(b.doc_num) ,max(c.text) 
from ist b,itm_trn a, ist_cmnt c  
where a.ist_seq_num = b.ist_seq_num 
and a.ist_seq_num = c.ist_seq_num  
and a.ist_wr_dt = b.ist_wr_dt 
and a.new_loc_cd  like 'BOX115' 
and a.ITT_CD = 'XFR' and a.create_dt >'21-AUG-16' 
group by a.itm_cd);

This results in a syntax error

msg 102, level 15, state 1 line 1  incorrect syntax near ')'.

I am not sure what I am doing wrong, I have a similar sql statement that works this way in oracle, but have not found where I am messing up in MSSQL

Enabling AES_128_CBC and RC4_128 for JDBC connections to MS SQL Server 2005

To ensure backward compatibility of my application, I'm testing JDBC over TLS behaviour when an MS SQL Server version vulnerable to CVE-2011-3389 is used (any 2005, or 2008/2008R2 w/o service packs fit). In theory, two options are available:

• either disable CBC protection via -Djsse.enableCBCProtection=false and continue to use a block cipher such as AES_128_CBC or 3DES_EDE_CBC,
• or fall back to a stream cipher such as RC4 (yes I'm aware this is insecure, too, due to CVE-2015-2808).

In practice, while I have no trouble establishing a connection using 3DES_EDE_CBC with CBC protection off, I'm still unable to use RC4_128 using an JDK newer than 1.8.0_51 (which happened to address CVE-2015-2808) or AES_{128,256}_CBC (using any 1.6+ JDK).

Here's the results broken down by Java version:

• 1.6.0_45 (jTDS)
  • SSL_RSA_WITH_RC4_128_MD5 is used
• 1.7.0_76 (jTDS) and 1.8.0_40 (MS SQL JDBC):
  • SSL_RSA_WITH_RC4_128_MD5 (default) or SSL_RSA_WITH_3DES_EDE_CBC_SHA can be used
  • won't use AES_128_CBC even if 3DES is disabled (3DES_EDE_CBC will be forced anyway)
• 1.8.0_45 (IBM J9 8.0 SR1) (MS SQL JDBC)
  • SSL_RSA_WITH_3DES_EDE_CBC_SHA is used (successful only if CBC protection is off), also if either AES or RC4 is requested
• 1.8.0_92 (Oracle) (MS SQL JDBC)
  • SSL_RSA_WITH_3DES_EDE_CBC_SHA is used (successful only if CBC protection is off),
  • won't use AES_128_CBC or AES_256_CBC even if requested (unlike previous Java versions, 3DES is no longer forced, instead I get an IOException after ClientHello, which does list *_WITH_AES_128_CBC_SHA as compatible ciphersuites)
  • won't use RC4 even if both with AES and 3DES are disabled: "no negotiable cipher suite" (both jTDS and MS SQL JDBC).

Here's the java.security I use to request AES:

jdk.certpath.disabledAlgorithms=MD2
jdk.tls.disabledAlgorithms=SSLv3, RC4, TLSv1.1, TLSv1.2, 3DES_EDE_CBC
jdk.tls.legacyAlgorithms= \
        K_NULL, C_NULL, M_NULL, \
        RC4_128, RC4_40

and here's the version to request RC4:

jdk.certpath.disabledAlgorithms=MD2
jdk.tls.disabledAlgorithms=SSLv3, AES_128_CBC, TLSv1.1, TLSv1.2, AES_256_CBC, AES_128_GCM, AES_256_GCM, 3DES_EDE_CBC
jdk.tls.legacyAlgorithms= \
        K_NULL, C_NULL, M_NULL

Questions:

• Apparently, AES_{128,256}_CBC is supported by my Java clients, as I can use TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA when connecting to MS SQL Server 2014. Can anyone confirm it is not supported by MS SQL Server 2005? Since disabling AES effectively leads to "no negotiable cipher suite", I assume it is supported, but something happens server-side, even though CBC protection is off.
• How can I still use RC4 in Java 1.8.0_51+? This solution no longer works, nor has any effect https.cipherSuites system property (described here). There's a magical jdk.tls.enableRC4CipherSuites system property in 6u115 and 7u101, but it seems to have no effect in Java 1.8.
• What the heck is wrong with jTDS? It works fine with Java 1.6 and 1.7 (driver versions 1.2.8 and 1.3.1), but using Java 1.8 I'm constantly receiving "Connection reset by peer" whenever MS SQL JDBC would just use 3DES to encrypt connection data.

How to pass stored procedure's parameter values to temp table using MS SQL 2005?

I need to store the procedures acutal parameter values to a temp table. I tried to create a script which can be applied for different stored procedures, therefore the script cannot contain the name of the parameters. This is the code I made, however when I try to store the value of parameters one by one in a temp table, I get error message saying the parameter have to be declared. Please help how to solve this one.

ALTER PROCEDURE [dbo].[EQSP_Parameters] (@MIC varchar(4), @ISIN varchar(12), @Client varchar(12), @TradeDate datetime, @Email int )

Make mssql query

help me please with query:
select * from sc84 as nom join sc319 as p on p.PARENTEXT = nom.id join sc219 as pt on p.sp327 = pt.id join _1SCONST as c on c.objid=p.id
As a result approximately such table
Car / price_base / 08-08-2016:13-40 / 100 /
Car / price_base / 08-08-2016:14-40 / 150 /
Car / price_base / 08-09-2016:13-40 / 190 /
Car / price_super / 08-09-2016:18-40 / 210 /
Car / price_super / 08-10-2016:13-40 / 290 /
That is goods, types of the price of date and their value. Prompt please how to receive the last (the actual price for each type of the price and each goods) tried options with group but there is obviously not enough skill.

nom.id - PK SKU     
pt.id - PK price type   
p.id -PK price    
p.parentext - parent price (sku)    
p.sp327 - FK to price type   
date = date column   

SQL Server 2005 : Create Procedure for Google Directions

Can you help me to convert this procedure that give me the distance beetween the start address and the end address

CREATE PROCEDURE [dbo].[CalculateDistanceGoogle]
    (@ToAddress NVARCHAR(100), 
     @FromAddress NVARCHAR(100), 
     @DistanceistanceInKm FLOAT OUTPUT) 
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Object INT 
    DECLARE @ResponseonseText NVARCHAR(MAX) 
    DECLARE @StatuserviceUrl NVARCHAR(500)

    SET @StatuserviceUrl = 'http://ift.tt/2bfUgvP' + @ToAddress + '&destinations=' + @FromAddress +'&mode=driving&language=en-EN&units=metric&key=APIKEY;'

    EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT; EXEC sp_OAMethod @Object, 'open', NULL, 'get', @StatuserviceUrl, 'false' EXEC sp_OAMethod @Object, 'send' EXEC sp_OAMethod @Object, 'responseText', @ResponseonseText OUTPUT

    DECLARE @Response XML

    SET @ResponseonseText = REPLACE(@ResponseonseText, N'encoding="UTF-8"', N'');
    SET @Response = CAST(CAST(@ResponseonseText AS NVARCHAR(MAX)) AS XML)

    DECLARE @Status NVARCHAR(20) 
    DECLARE @Distance NVARCHAR(20)

    SET @Status = @Response.value('(DistanceMatrixResponse/row/element/status)[1]', 'NVARCHAR(20)')

    IF(@Status = 'ZERO_RESULTS') 
        SET @Distance = NULL 
    ELSE 
        SET @Distance = @Response.value('(DistanceMatrixResponse/row/element/distance/value)[1]', 'NVARCHAR(20)')

    SET @DistanceistanceInKm = ROUND(CAST(@Distance AS FLOAT) / 1000, 1)

    PRINT @DistanceistanceInKm

    EXECUTE sp_OADestroy @Object
END

To a procedure that use the direction api instead of the distance with this link

http://ift.tt/2beA0xX KEY

and print all XML end_address in the order that google give me back?

I'm using SQL Server 2005

I'm developing a system that will be save on two different database

Hi Guys i just want to ask an alternative solution for my saving problem i'm saving data in two different database mssql and mysql . Mysql is my backups so every time the user will create an entry it they will be save in two database the user encounter a problem. the user added new entry then when they check the data it doesn't save so every time the user will create the same data it will result to duplicate entry because the data was saved firt in mssql then after that it will be saved on mysql any solutions may help thank you so much

SQL and Shipping Optimization

I need to optimize my shipping process. I need to take datas from sql2005 server and then I think to use Google Directions Api to optimize the locations order. But I don't know if it is the best solution to use those api or if there's another service to directly show the result of a query on google maps and then calculate the best road. My query must look to destinations in the next 3 weeks. So I need also to crop this order, selecting for example 3 or 4 location for each day in the next 3 weeks. Can you help me to find the best solution? Thank you!

Procedure result with XML parsing error

Hi guys I'm trying to use my first procedure. I need it for Google Matrix. This is my procedure, found there

CREATE PROCEDURE [dbo].[CalculateDistanceGoogle] ( @ToAddress NVARCHAR(100) , @FromAddress NVARCHAR(100), @DistanceistanceInKm FLOAT OUTPUT ) AS

BEGIN

DECLARE @Object INT DECLARE @ResponseonseText NVARCHAR(4000) DECLARE @StatuserviceUrl NVARCHAR(500)

SET @StatuserviceUrl = 'http://ift.tt/19WUt3A' + @ToAddress + '&destinations=' + @FromAddress +'&mode=driving&language=it-IT&units=metric;'

EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT; EXEC sp_OAMethod @Object, 'open', NULL, 'get', @StatuserviceUrl, 'false' EXEC sp_OAMethod @Object, 'send' EXEC sp_OAMethod @Object, 'responseText', @ResponseonseText OUTPUT

DECLARE @Response XML

SET @Response = CAST(CAST(@ResponseonseText AS NVARCHAR(MAX)) AS XML)

DECLARE @Status NVARCHAR(20) DECLARE @Distance NVARCHAR(20)

SET @Status = @Response.value('(DistanceMatrixResponse/row/element/status)[1]', 'NVARCHAR(20)')

IF(@Status = 'ZERO_RESULTS') SET @Distance = NULL ELSE SET @Distance = @Response.value('(DistanceMatrixResponse/row/element/distance/value)[1]', 'NVARCHAR(20)')

SET @DistanceistanceInKm = ROUND(CAST(@Distance AS FLOAT) / 1000, 1)

PRINT @DistanceistanceInKm

END

And this is the execution to store result of distance in the variable:

DECLARE @Distanza NVARCHAR 
EXEC @Distanza = dbo.CalculateDistanceGoogle @ToAddress = 'CITY', @FromAddress='CITY',  @DistanceistanceInKm = @Distanza
PRINT @Distanza

I've this error:

Msg 9402, Level 16, State 1, Procedure CalculateDistanceGoogle, Line 13 XML parsing: line 1, character 38, unable to switch the encoding

Can you help me to understand why?

Thank you!

How to named CSV file as yesterday' date?

I need a SQL job to run every day.

This CSV file needs to be named with yesterday’s date. So, if this job was to run today, it would generate a file called 20160820_invoices.csv.

I use following code but it's not work. "C:\Invoices_" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEADD("dd", -1, GetDate()),2) + ".csv"`

Is there any other way it can get work?

Advice to create a little script to associate near shipping address in the next 3 weeks

Hi guys I'm searching an advice on how to proceed to create a script that can calculate the distance between all shipping address in the next 3 weeks and if the distance between some of these locations is under 50 km it must send an email to make that thing notice to the operator. I'm starting from sql 2005 to take location and make all combination. Then I was thinking if there is some online service that, with a link structure where I can put the two location name, can retrieve the distance between those two point, then store it in different variable to use to make comparisons. I'll wait for some advice.thank you guys

Sql 2005: Help to concatenate Nvarchar and Int

I've this block of code with an error because i'm using an Int variable inside a Nvarchar.

 DECLARE @FattAnnoCorrente INT;
DECLARE @Tabscontianno1 NVARCHAR(MAX);

 SET @Tabscontianno1 = 
N'<p align="left"><b>ANNO ' + @Anno1 + ' - </b><b>' + @FattAnnoCorrente + '<br>
  </b></p>
<table height="62" border="1" cellpadding="2" cellspacing="2"
  width="501">
  <tbody>
    <tr>
      <td valign="top">FATTURATO<br>
      </td>
      <td valign="top">SCONTO<br>
      </td>
    </tr>' + CAST ((
                    SELECT  
                    td = SUM(TOTNETTORIGA),    '',
                    td = SCONTIESTESI
                    FROM .dbo.TESTEDOCUMENTI 
                    INNER JOIN .dbo.RIGHEDOCUMENTI
                    ON PROGRESSIVO=IDTESTA AND TOTNETTORIGA <>'0'
                    WHERE  CODCLIFOR = @CodiceCliente AND .dbo.TESTEDOCUMENTI.DOCCHIUSO = '0' AND .dbo.TESTEDOCUMENTI.BLOCCATO = '0' AND .dbo.TESTEDOCUMENTI.TIPODOC = 'FVC'  AND .dbo.TESTEDOCUMENTI.ESERCIZIO = YEAR(GETDATE())
                    GROUP BY TESTEDOCUMENTI.ESERCIZIO,SCONTIESTESI
    FOR XML PATH('tr'), TYPE )
    AS NVARCHAR(MAX) ) +
    N'  </tbody>
</table>'+
    N'<BR/>' ;

I've this error:

Conversion failed when converting the nvarchar value 'ANNO 2016 - ' to data type int.

If I use

CAST(CAST(COALESCE(@FattAnnoCorrente) as int) as varchar(255))

I've instead those errors:

Msg 102, Level 15, State 1, Line 154 Incorrect syntax near ')'. Msg 156, Level 15, State 1, Line 173 Incorrect syntax near the keyword 'FOR'.

Can you help me to solve this problem. After insert it in the nvarchar variable I need to format it as money like that:

'€ ' + REPLACE(CONVERT(varchar, CAST(@FattAnnoCorrente AS money), 105),',','.')

Thank you guys!

Using two cursors in a stored procedure

I have a SP in which there is a cursor which loops and gives me the desired result.

Now what I want is, I want to use the same cursor twice so that the same record should get inserted into the inward_doc_tracking_trl twice

Here is my SP:-

ALTER procedure set_auto_action_1    
                    as       
                   begin 
                declare @inv_hdr_mkey int    
                declare @inv_trl_mkey nvarchar(200)    
                declare @entry_sr_no nvarchar(200)    
                declare @Nuser_mkey varchar(10)    
                declare @n_department varchar(10)    
                declare @cstatus_flag int    
                declare @remarks varchar(500)    
                declare @cuserid int    
                declare @u_datetime DATETIME    
                declare @nstatus_flag int    
                declare @delete_flag varchar(1)    
                declare @cdept_id int    
                declare @ref_mkey int    
                declare @No_Of_Days varchar(10)    
                declare @Approved_Amount numeric(18, 2)    
                declare @Chq_No varchar(10)    
                declare @Chq_dated DATETIME    
                declare @Chq_Bank varchar(10)    
                declare @Chq_Amount numeric(18, 2)    
                declare @Vendor_MKey varchar(10)    
                declare @Vendor_Comp_Mkey varchar(10)    
                declare @Project_Mkey varchar(10)    
                declare @Program_mkey varchar(10)    
                declare @Payment_MKey varchar(10)    
                declare @Due_Date datetime    
                declare @Updated_Remarks varchar(100)    
                declare @Updated_Bill_no varchar(10)    
                declare @Updated_Bill_Date datetime    
                declare @Updated_Bill_Amt numeric(18, 2)    
                declare @Party_Name varchar(100)    
                declare @Acc_mkey varchar(10)    
                declare @TotalDeductions numeric(18, 2)    
                declare @Broker_Mkey varchar(10)    
                declare @Customer_Mkey varchar(10)    
                declare @Payable_Amt numeric(18, 2)    
                declare @Balance_Amt numeric(18, 2)    
                set @Nuser_mkey = NULL    
                set @n_department = NULL    
                set @remarks = 'Document received'    
                set @u_datetime = getdate()    
                set @nstatus_flag=1
                set @cstatus_flag = 2    
                set @delete_flag = 'N'    
                set @No_Of_Days = NULL    
                set @Acc_mkey = NULL          

                declare acCur cursor for select 

                    mkey,status_flag,to_department,to_user,approved_amount,chq_no,chq_dated,chq_bank,chq_amount,vendor_mkey,
                    Vendor_Comp_Mkey,project_mkey,program_mkey,payment_mkey,due_date,updated_remarks,updated_bill_no,updated_bill_date,
                    updated_bill_amt,party_name,totaldeductions,Broker_Mkey,Customer_Mkey,Payable_Amt,balance_amt 
                    from inward_doc_tracking_hdr where status_flag not in (5,13,14)
                    and doc_date <  CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 120) + ' 15:00:00')   

                open acCur    

                FETCH NEXT FROM acCur into  
                                    @inv_hdr_mkey,@cstatus_flag,@cdept_id,@cuserid,@Approved_Amount,
                                    @Chq_No,@Chq_dated,@Chq_Bank,@Chq_Amount,@Vendor_MKey,@Vendor_Comp_Mkey,
                                    @Project_Mkey,@Program_mkey,@Payment_MKey,@Due_Date,@Updated_Remarks,@Updated_Bill_no,
                                    @Updated_Bill_Date,@Updated_Bill_Amt,@Party_Name,@TotalDeductions,@Broker_Mkey,
                                    @Customer_Mkey,@Payable_Amt,@Balance_Amt        

                WHILE @@FETCH_STATUS = 0    
                BEGIN    

                                    select @inv_trl_mkey= max(mkey)+1 from inward_doc_tracking_trl     
                                    select @entry_sr_no = max(entry_sr_no)+1 from inward_doc_tracking_trl where ref_mkey=@inv_hdr_mkey



                                    insert into inward_doc_tracking_trl (Mkey,Entry_Sr_No,N_UserMkey,N_Department,
                                    CStatus_Flag,Remarks,
                                    CUser_ID,                           
                                    U_Datetime,NStatus_Flag,Delete_Flag,CDept_Id,Ref_Mkey,
                                    No_Of_Days,Approved_Amount,Chq_No,Chq_dated,Chq_Bank,Chq_Amount,Vendor_MKey,Vendor_Comp_Mkey,
                                    Project_Mkey,Program_mkey,Payment_MKey,Due_Date,Updated_Remarks,Updated_Bill_no,
                                    Updated_Bill_Date,Updated_Bill_Amt,Party_Name,Acc_mkey,TotalDeductions,Broker_Mkey,Customer_Mkey,
                                    Payable_Amt,Balance_Amt) 

                                    values 
                                    (@inv_trl_mkey,@entry_sr_no,@Nuser_mkey,@n_department,@cstatus_flag,
                                    @remarks,                           
                                    CASE WHEN (@cuserid IS NULL) THEN 0 ELSE @cuserid END,
                                    @u_datetime,@nstatus_flag,@delete_flag,
                                    CASE WHEN (@cdept_id IS NULL) THEN 0 ELSE @cdept_id END,
                                    @inv_hdr_mkey,
                                    @No_Of_Days,@Approved_Amount,@Chq_No,@Chq_dated,@Chq_Bank,@Chq_Amount,@Vendor_MKey,
                                    @Vendor_Comp_Mkey,@Project_Mkey,@Program_mkey,@Payment_MKey,@Due_Date,@Updated_Remarks,
                                    @Updated_Bill_no,@Updated_Bill_Date,@Updated_Bill_Amt,@Party_Name,@Acc_mkey,@TotalDeductions,
                                    @Broker_Mkey,@Customer_Mkey,@Payable_Amt,@Balance_Amt)    


                                    FETCH NEXT FROM acCur into @inv_hdr_mkey,@cstatus_flag,@cdept_id,@cuserid,@Approved_Amount,@Chq_No,
                                    @Chq_dated,@Chq_Bank,@Chq_Amount,@Vendor_MKey,@Vendor_Comp_Mkey,@Project_Mkey,@Program_mkey,@Payment_MKey,
                                    @Due_Date,@Updated_Remarks,@Updated_Bill_no,@Updated_Bill_Date,@Updated_Bill_Amt,@Party_Name,
                                    @TotalDeductions,@Broker_Mkey,@Customer_Mkey,@Payable_Amt,@Balance_Amt

                end    
                            close acCur    
                            deallocate acCur    
                end

kindly help me with how to use the same cursor twice in SP

CASE with IS NULL not working

I want to give column some value if the value is NULL I tried like below

insert into inward_doc_tracking_trl 
    (Mkey,Entry_Sr_No,N_UserMkey,N_Department,CStatus_Flag,Remarks,     
    CUser_Id,
    U_Datetime,NStatus_Flag,Delete_Flag, 
    CDept_Id,
    Ref_Mkey,No_Of_Days,Approved_Amount,Chq_No,Chq_dated,Chq_Bank,Chq_Amount,
    Vendor_MKey,Vendor_Comp_Mkey,Project_Mkey,Program_mkey,Payment_MKey,Due_Date,Updated_Remarks,Updated_Bill_no,
    Updated_Bill_Date,Updated_Bill_Amt,Party_Name,Acc_mkey,
    TotalDeductions,Broker_Mkey,Customer_Mkey,Payable_Amt,Balance_Amt)

     values (@inv_trl_mkey,@entry_sr_no,@Nuser_mkey,@n_department,@nstatus_flag2,@remarks,               
        CASE WHEN (@cuserid IS NULL) THEN 'YES' ELSE 'NO' END AS @cuserid,
    @u_datetime,@nstatus_flag,@delete_flag,     
        CASE WHEN (@cdept_id IS NULL) THEN 'YES' ELSE 'NO' END AS @cdept_id,
    @inv_hdr_mkey,@No_Of_Days,
     @Approved_Amount,@Chq_No,@Chq_dated,
     @Chq_Bank,@Chq_Amount,@Vendor_MKey,@Vendor_Comp_Mkey,@Project_Mkey,@Program_mkey,@Payment_MKey,@Due_Date  
    ,@Updated_Remarks,@Updated_Bill_no,@Updated_Bill_Date,@Updated_Bill_Amt,@Party_Name,
    @Acc_mkey,@TotalDeductions,@Broker_Mkey,@Customer_Mkey,@Payable_Amt,@Balance_Amt)  

but getting error as

Incorrect syntax near the keyword 'AS'.

Insert duplicate record with max + 1 entry_sr_no

I have a SP which returns me the Maximum entry no for respective document no.

Now what I want is, I want to insert the same record in the inward_doc_tracking_trl table with max + 1 entry_sr_no

Let say,

Max_Entry_Srno of doc_no = IW/16/5694 is 3, then while inserting the same in trl table the Max_Entry_Srno should be 4.

Here is my SP

 ALTER procedure set_auto_action  
       as   
        begin         
             select hdr.mkey, hdr.doc_no, MAX(trl.entry_sr_no) as Max_Entry_Srno  
              from inward_doc_tracking_hdr hdr  
               JOIN inward_doc_tracking_trl trl  
             ON(hdr.mkey = trl.ref_mkey)  
              where hdr.status_flag     not in (5,13)   
             and trl.U_Datetime < DATEADD(DAY, -1, GETDATE())       
             GROUP BY hdr.mkey, hdr.doc_no  
       end

Also see the screenshot for the result returned by the SP

The table script of inward_doc_tracking_trl is also below:-

CREATE TABLE [dbo].[Inward_Doc_Tracking_Trl](
[Mkey] [numeric](18, 0) NOT NULL,
[Entry_Sr_No] [numeric](4, 0) NOT NULL,
[N_UserMkey] [numeric](10, 0) NULL,
[N_Department] [numeric](10, 0) NULL,
[CStatus_Flag] [numeric](8, 0) NOT NULL,
[Remarks] [varchar](500) NULL,
[CUser_ID] [numeric](10, 0) NOT NULL,
[U_Datetime] [datetime] NOT NULL,
[NStatus_Flag] [numeric](10, 0) NOT NULL, 
[Delete_Flag] [char](1) NULL,
[CDept_Id] [numeric](10, 0) NOT NULL,
[Ref_Mkey] [numeric](18, 0) NULL,
[No_Of_Days] [int] NULL,
[Approved_Amount] [float] NULL,
[Chq_No] [varchar](50) NULL,
[Chq_dated] [datetime] NULL,
[Chq_Bank] [varchar](40) NULL,
[Chq_Amount] [float] NULL,
[Vendor_MKey] [int] NULL,
[Vendor_Comp_Mkey] [int] NULL,
[Project_Mkey] [numeric](10, 0) NULL,
[Program_mkey] [numeric](10, 0) NULL,
[Payment_MKey] [int] NULL,
[Due_Date] [datetime] NULL,
[Updated_Remarks] [varchar](500) NULL,
[Updated_Bill_no] [varchar](27) NULL,
[Updated_Bill_Date] [datetime] NULL,
[Updated_Bill_Amt] [float] NULL,
[Party_Name] [varchar](80) NULL,
[Acc_mkey] [numeric](10, 0) NULL,
[TotalDeductions] [float] NULL,
[Broker_Mkey] [numeric](10, 0) NULL,
[Customer_Mkey] [numeric](10, 0) NULL,
[Payable_Amt] [float] NULL,
[Balance_Amt] [float] NULL,
[Receipt_No] [varchar](50) NULL,
[Po_No] [varchar](50) NULL,
[Bill_No] [varchar](50) NULL,
[Disp_through] [varchar](50) NULL,
[Disp_Through_Name] [varchar](100) NULL,
[Site_Id] [int] NULL,
   CONSTRAINT [PK_Inward_Doc_Tracking_Trl_1] PRIMARY KEY CLUSTERED 
  (
   [Mkey] ASC,
   [Entry_Sr_No] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =       OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

NOTE: The reason behind adding the duplicate row with max+ 1 sr_no is to let user know the document is received by that user

SQL 2005 IF not working fine

I've created an if statement to control if a customer discount is right. I take the sales volume of its previous year, control if it is in the exact range, then if it is not I need to write the right discount to apply. My problem is that this suggested discount is not the right one. I'll show you the code. Before I give a sample customer data.

Discount Applied is 60 % Sales Volume 2016--->€ 13.895.90 Sales Volume 2015 ---> € 25.686.92

This is my query:

DECLARE all variables that i need......
SET @Anno1 = YEAR(GETDATE());
SET @Anno2 = YEAR(DATEADD(year,-1,GETDATE()));
SET @Anno3 = YEAR(DATEADD(year,-2,GETDATE()));
SET @datada = DATEADD(DAY, -15, GETDATE());
SET @dataa = GETDATE();

----- set discount sales volume --- 
SET @40 = '€ '+ REPLACE(CONVERT(varchar, CAST('1500.0000' AS money), 105),',','.');
SET @50 = '€ '+ REPLACE(CONVERT(varchar, CAST('15000.0000' AS money), 105),',','.');
SET @60 = '€ '+ REPLACE(CONVERT(varchar, CAST('150000.0000' AS money), 105),',','.');
SET @70 = '€ '+ REPLACE(CONVERT(varchar, CAST('200000.0000' AS money), 105),',','.');
SET @80 = '€ '+ REPLACE(CONVERT(varchar, CAST('500000.0000' AS money), 105),',','.');

---create cursor---
DECLARE c CURSOR FOR
SELECT DISTINCT
   CODCONTO,
   DSCCONTO1
FROM .dbo.TESTEDOCUMENTI 

WHERE D (.dbo.TESTEDOCUMENTI.TIPODOC = 'PCL' OR .dbo.TESTEDOCUMENTI.TIPODOC = 'ORC' ) AND .dbo.TESTEDOCUMENTI.DATADOC BETWEEN @datada AND @dataa



----take each customer----
OPEN c
FETCH NEXT FROM c INTO @CodiceCliente,@Cliente
--IF @CodiceCliente IS NULL goto finescript;
WHILE @@FETCH_STATUS = 0
    BEGIN

-------------------------------------------------------------------
----------------------set sales volumes to variables---
-------------------------------Current year -----
SET @FattAnnoCorrente = 
(SELECT  '€ '+ REPLACE(CONVERT(varchar, CAST(SUM(TOTIMPONIBILE) AS money), 105),',','.') FROM .dbo.TESTEDOCUMENTI 
WHERE  CODCLIFOR = @CodiceCliente ANDAND .dbo.TESTEDOCUMENTI.TIPODOC = 'FVC'  AND .dbo.TESTEDOCUMENTI.ESERCIZIO = YEAR(GETDATE()));

-------------------------------Previous Year -----
SET @FattAnnoPrecedente = 
(SELECT  '€ '+ REPLACE(CONVERT(varchar, CAST(SUM(TOTIMPONIBILE) AS money), 105),',','.') FROM .dbo.TESTEDOCUMENTI 
WHERE  CODCLIFOR = @CodiceCliente  AND .dbo.TESTEDOCUMENTI.TIPODOC = 'FVC'  AND .dbo.TESTEDOCUMENTI.ESERCIZIO = YEAR(DATEADD(year,-1,GETDATE())));
------------------------------2 Previous years -----
SET @Fatt2AnniPrecedenti = 
(SELECT  '€ '+ REPLACE(CONVERT(varchar, CAST(SUM(TOTIMPONIBILE) AS money), 105),',','.') FROM .dbo.TESTEDOCUMENTI 
WHERE  CODCLIFOR = @CodiceCliente AND .dbo.TESTEDOCUMENTI.TIPODOC = 'FVC'  AND .dbo.TESTEDOCUMENTI.ESERCIZIO = YEAR(DATEADD(year,-2,GETDATE())));

----------- Take the last document discount and set to variable ----- 
SET @Sconto = 
(SELECT DISTINCT MAX(SCONTORIGA)                
FROM .dbo.TESTEDOCUMENTI 
WHERE  SCONTORIGA IS NOT NULL AND CODCLIFOR = @CodiceCliente AND (.dbo.TESTEDOCUMENTI.TIPODOC = 'PCL' OR .dbo.TESTEDOCUMENTI.TIPODOC = 'ORC' ) AND .dbo.TESTEDOCUMENTI.DATADOC BETWEEN @datada AND @dataa);

--------------------------verify condition---THERE IS THE TRUOBLES----
---------------------------PREVIOUS YEAR SALES VOLUME----
IF @FattAnnoCorrente IS NULL SET @FattAnnoCorrente = '0'
IF @FattAnnoPrecedente IS NULL SET @FattAnnoPrecedente = '0'
IF @Fatt2AnniPrecedenti IS NULL SET @Fatt2AnniPrecedenti = '0'
IF @FattAnnoPrecedente = '0' goto fatturatocorrente;

IF (@FattAnnoPrecedente > '0' and @FattAnnoPrecedente < @40) and @Sconto < '40' goto finescript;
IF (@FattAnnoPrecedente > @40 and @FattAnnoPrecedente < @50 ) and (@Sconto > '40' and @Sconto < '50') goto finescript;
IF (@FattAnnoPrecedente > @50 and @FattAnnoPrecedente < @60 ) and (@Sconto > '50' and @Sconto < '60') goto finescript;
IF (@FattAnnoPrecedente > @60 and @FattAnnoPrecedente < @70 ) and (@Sconto > '60' and @Sconto < '70') goto finescript;
IF (@FattAnnoPrecedente > @70 and @FattAnnoPrecedente < @80 ) and (@Sconto > '70' and @Sconto < '80') goto finescript;
IF (@FattAnnoPrecedente > @80 and @FattAnnoPrecedente < '999999999999999999' ) and @Sconto > '80' goto finescript;


------------------------------------FIND THE SUGESTED DISCOUNT ------ THIS IS WRONG

IF ((@FattAnnoPrecedente > '0' and @FattAnnoPrecedente < @40 ))
    SET @ScontoPrevisto = 'inferiore al 40%';
IF ((@FattAnnoPrecedente > @40 and @FattAnnoPrecedente < @50 )  )
    SET @ScontoPrevisto = 'compreso tra 40% e 50%';
IF ((@FattAnnoPrecedente > @50 and @FattAnnoPrecedente < @60 )  )
    SET @ScontoPrevisto = 'compreso tra 50% e 60%';
IF ((@FattAnnoPrecedente > @60 and @FattAnnoPrecedente < @70 ) )
    SET @ScontoPrevisto = 'compreso tra 60% e 70%';
IF ((@FattAnnoPrecedente > @70 and @FattAnnoPrecedente < @80 )  )
    SET @ScontoPrevisto = 'compreso tra 70% e 80%';
IF ((@FattAnnoPrecedente > @80 and @FattAnnoPrecedente < '999999999999999999' ) )
     SET @ScontoPrevisto = 'superiore all"80%';
SET @AnnoConsiderato = 'ANNO PRECEDENTE';
fatturatocorrente:

------------USE CURRENT YEAR IF PREVIOUS SALES VOLUME IS 0---------
IF @FattAnnoPrecedente NOT LIKE '0' goto fatturatoesistente;


IF (@FattAnnoCorrente > '0' and @FattAnnoCorrente < @40 ) and @Sconto < '40' goto finescript;
IF (@FattAnnoCorrente > @40 and @FattAnnoCorrente < @50 ) and (@Sconto > '40' and @Sconto < '50') goto finescript;
IF (@FattAnnoCorrente > @50 and @FattAnnoCorrente < @60 ) and (@Sconto > '50' and @Sconto < '60') goto finescript;
IF (@FattAnnoCorrente > @60 and @FattAnnoCorrente < @70 ) and (@Sconto > '60' and @Sconto < '70') goto finescript;
IF (@FattAnnoCorrente > @70 and @FattAnnoCorrente < @80 ) and (@Sconto > '70' and @Sconto < '80') goto finescript;
IF (@FattAnnoCorrente > @80 and @FattAnnoCorrente < '999999999999999999' ) and @Sconto > '80' goto finescript;


------------------------------------FIND SUGGESTED DISCOUNT ------
--SET @FattAnnoCorrente = '1';
IF ((@FattAnnoCorrente > '0' and @FattAnnoCorrente < @40 ))
     SET @ScontoPrevisto = 'inferiore al 40%';
IF ((@FattAnnoCorrente > @40 and @FattAnnoCorrente < @50 ))
     SET @ScontoPrevisto = 'compreso tra 40% e 50%';
IF ((@FattAnnoCorrente > @50 and @FattAnnoCorrente < @60 ))
     SET @ScontoPrevisto = 'compreso tra 50% e 60%';
IF ((@FattAnnoCorrente > @60 and @FattAnnoCorrente < @70 ))
     SET @ScontoPrevisto = 'compreso tra 60% e 70%';
IF ((@FattAnnoCorrente > @70 and @FattAnnoCorrente < @80 ))
     SET @ScontoPrevisto = 'compreso tra 70% e 80%';
IF ((@FattAnnoCorrente > @80 and @FattAnnoCorrente < '999999999999999999'))
      SET @ScontoPrevisto = 'superiore all"80%';
SET @AnnoConsiderato = 'ANNO CORRRENTE';
IF @Sconto LIKE '0.0%' SET @ScontoPrevisto = 'da stabilire in base alla merce ordinata'

fatturatoesistente:

-----------
--- HERE THERE WAS SOME TABLES CALLED BELOW BUT THEY WORK FINE, SO I REMOVED THEM ---
---------------------------------
---HTML EMAIL BODY SET WITH ALL VARIABLES, ALL WORKING FINE BUT THE @SCONTOPREVISTO is the wrong one----
SET @Email = 
    N'......HTML CODE....' + @ScontoPrevisto + '..HTML CODE...';

SET @oggettomail = 'ERRATA SCONTISTICA PER ' + @Cliente;
IF @Emailis null goto finescript;
EXEC msdb.dbo.sp_send_dbmail 
    @recipients = 'email@gmail.com',
    @subject = @oggettomail,
    @body = @Email,
    @body_format = 'HTML' ; 

finescript:
--take the next customerE---
FETCH NEXT FROM c INTO @CodiceCliente,@Cliente
END
--clean---
CLOSE c
DEALLOCATE c

The result of this query for @ScontoPrevisto, suggested discount is wrong, it is between 70% and 80 % but as you see the previous year sales volume is about 25000 so the right discount must be 50-60%. I dont' understand why. Instead for some customer, reslt is good. Another customer has

Sales Volume 2016--->0 Sales Volume 2015 ---> 0 Discount 60%

Result is greater then 80 % instead to be smaller than 40 %. I wait for your answer. Thank you guys!

Select from two tables without CommonElement

I want making select from two tables without any common element I'm trying this code, but i get incorrect syntax near ','.

select top 1 inventory.name,customer.name from inventory where inventor.name='test' ,  customer where customer.name='test2'

Get Max Entry_Sr_no from another table in SP

I have a SP, which gives me mkey and doc_no from one table.

Now I want to get the max of entry_sr_no of that respective mkey from the another table.

Here is my SP

alter procedure set_auto_action
    as 
        begin

            select mkey, doc_no from inward_doc_tracking_hdr 
           where status_flag not in (5,13)      
    end

I want to get max from inward_doc_tracking_trl table

The relation of both the table is something like below

inward_doc_tracking_hdr table mkey is equal to inward_doc_tracking_trl table ref_mkey

kindly suggest how to do this in SP

How to check Current SQL Statement inside Stored Procedure in SQL Server

Please tell me how to check Currently executing SQL Statement called inside a Stored Procedure in SQL Server and its rows affected [ if DML, so far count like ROWS_PROCESSED in Oracle].

Select all results but Order by condition on single column

I've some records in a table like below:

ID  Name Group JoinDate    ActiveDate
1   A    12    11-01-2015  25-05-2015
2   A    19    12-06-2014  27-08-2015
3   B    16    03-09-2015  12-04-2016
4   B    35    05-05-2013  21-07-2015
5   B    39    06-09-2015  14-08-2016

From the data i need a result set like below: PS: Result set is based on the ActivityDate order by desc

Result:

ID  Name Group JoinDate    ActiveDate
2   A     19   12-06-2014  27-08-2015
1   A     12   11-01-2015  25-05-2015
5   B     39   06-09-2015  14-08-2016
3   B     16   03-09-2015  12-04-2016
4   B     35   05-05-2013  21-07-2015

Any Suggestions?

How to install sql2005 on sql2008r2?

I am poorly experienced in SQL Server installations. I have already installed 2008r2 on Microsoft Windows 8.

Now I want to install MS SQL Server 2005, but I need both MS SQL Server 2008r2 and MS SQL Server 2005.

How can I install this?

total server memory - what is the most accurate way to get it through sql server?

How is it that the values of these 2 queries disagree between each other:

SELECT [server memory] = physical_memory_in_bytes /1024.00/1024.00/1024.00
FROM sys.dm_os_sys_info;



SELECT object_name, cntr_value 
  FROM sys.dm_os_performance_counters
  WHERE counter_name = 'Total Server Memory (KB)';

they should both be showing the total server memory.

why do they disagree?

Using SQL Profiler to get the name of stored procedures

I have a database with almost a thousand of stored procedures and I want to know which stored procedure is called after a button is clicked. I try to use SQL Profiler (version 8.0) to capture the name of the stored procedure, but it seems it's not possible to get the name directly from SQL Profiler.

A tutorial says I can get the stored procedure name by running:

Select name from sysobjects where id = <ObjectID>

I tried by the id matched nothing. How can I get the name of the stored procedure?

Thanks

Adding Row in existing table (SQL Server 2005)

I want to add another row in my existing table and I'm a bit hesitant if I'm doing the right thing because it might skew the database. I have my script below and would like to hear your thoughts about it.

I want to add another row for 'Jane' in the table, which will be 'SKATING" in the ACT column.

Table: [Emp_table].[ACT].[LIST_EMP]

My script is:

INSERT INTO [Emp_table].[ACT].[LIST_EMP]
([ENTITY],[TYPE],[EMP_COD],[DATE],[LINE_NO],[ACT],[NAME])
VALUES
('REG','EMP','45233','2016-06-20 00:00:00:00','2','SKATING','JANE')

Will this do the trick?

How to construct query to change the output of an sql table

I need some little help here...So, let me introduce you my problem. I have the following SQL table:

| RankCode | SeaPortInd | WatchKeepingInd |      EffectiveDate      | VesselCode |        FromDate         |         ToDate          |        LastDate         | LastUser |
+----------+------------+-----------------+-------------------------+------------+-------------------------+-------------------------+-------------------------+----------+
| C/E      |          0 |               0 | 1900-01-01 00:00:00.000 |        031 | 1900-01-01 05:00:00.000 | 1900-01-01 07:00:00.000 | 2016-08-11 12:40:00.000 | d.baltas |
| C/E      |          0 |               0 | 2016-06-02 00:00:00.000 |        031 | 1900-01-01 00:00:00.000 | 1900-01-01 00:00:00.000 | 1900-01-01 00:00:00.000 | d.baltas |
| C/E      |          0 |               1 | 2016-06-01 00:00:00.000 |        031 | 1900-01-01 01:00:00.000 | 1900-01-01 02:00:00.000 | 2016-08-11 17:58:00.000 | d.baltas |
| C/E      |          0 |               1 | 2016-06-02 00:00:00.000 |        031 | 1900-01-01 01:00:00.000 | 1900-01-01 02:00:00.000 | 2016-08-10 17:58:00.000 | d.baltas |
| C/E      |          1 |               1 | 2016-06-01 00:00:00.000 |        031 | 1900-01-01 03:00:00.000 | 1900-01-01 04:00:00.000 | 2016-08-10 17:58:00.000 | d.baltas |
| MSTR     |          0 |               0 | 2016-06-02 00:00:00.000 |        031 | 1900-01-01 16:00:00.000 | 1900-01-01 22:00:00.000 | 2016-08-10 17:58:00.000 | d.baltas |
| MSTR     |          0 |               1 | 2016-06-01 00:00:00.000 |        031 | 1900-01-01 08:00:00.000 | 1900-01-01 12:00:00.000 | 2016-08-10 17:58:00.000 | d.baltas |
| MSTR     |          1 |               0 | 2016-06-03 00:00:00.000 |        031 | 1900-01-01 08:00:00.000 | 1900-01-01 14:00:00.000 | 2016-08-11 15:00:00.000 | d.baltas |
+----------+------------+-----------------+-------------------------+------------+-------------------------+-------------------------+-------------------------+----------+

I want to take an output like this table:

Some more explanation of table:

Scheduled daily work hours at sea means SeaPortInd = 1

Scheduled daily work hours at port means SeaPortInd = 0

Watchkeeping means WatchkeepingInd = 1

NonWatchkeeping means WatchkeepingInd = 0

I managed to take to the following table:

+----------+--------------------+
| RankCode | SeaNonWatchkeeping |
| C/E      |  00:00 - 00:00     |
|          |  05:00 - 07:00     |
| MSTR     |  16:00 - 22:00     |
+----------+--------------------+

with the query:

SELECT CASE 
        WHEN row_number() OVER (
                PARTITION BY RankCode ORDER BY FromDate asc
    ) = 1
            THEN RankCode
        ELSE ''
        END AS RankCode

    ,substring(convert(VARCHAR(255), FromDate, 120), 11, 6) + ' -' + substring(convert(VARCHAR(255), ToDate, 120), 11, 6) AS SeaNonWatchkeeping

FROM WorkingHoursSchedule WHERE SeaPortInd = 0 AND watchkeepingind = 0

Can you please help me how to get the cases SeaportInd = 0 and Watchkeeping= 1 etc?

I use SQL SERVER 2008 but the query will also run at some previous versions sith minimum SQL SERVER 2005

Thanks in advance!!

case statement in where clause,should not check only for specific values

i need a CASE statement in where clause:

lets say we have two tables X and Y X have 3 columns namely EmpId,Name,JoinDate,DeptID Y have 4 columns namely EmpId,JoinDate,DeptID,DeptRefCd

I've to get the results so that except for X.deptId in (1,2,3) it should check for X.JoinDate=ISNULL(Y.JoinDate,'').i.e., it have to ignore the JoinDate condition deptId= 1 ,2 ,3

I've written as below,but its showing error: CASE WHEN X.deptId NOT IN ('4','5','6') THEN X.JoinDate=ISNULL(Y.JoinDate,'') END

Please suggest.

Query optimization. Duplicate subqueries

We found a slow query in our legacy system. What I see in the query is a duplicate fragment. Here's the full query:

DECLARE @SellerId INT;
DECLARE @DateFrom DATETIME;
DECLARE @DateTo DATETIME;

SET @SellerId = 5396884;
SET @DateFrom = '2016-01-05';
SET @DateTo = '2016-10-08';

DECLARE @CurrentDate DATETIME;
SET @CurrentDate = GETDATE();



CREATE TABLE #ReportDate (codes INT, dates DATETIME);
DECLARE @dif as INT;
DECLARE @cont as INT;
DECLARE @currdate as DATETIME;
SET @dif = DATEDIFF(day, @DateFrom, @DateTo);
SET @cont = 1;
SET @currdate = @DateFrom - 1;
WHILE (@cont <= @dif + 1)
BEGIN
    SET @currdate = DATEADD(DAY, 1, @currdate);
    INSERT INTO #ReportDate VALUES (@cont, @currdate);
    SET @cont = @cont + 1;
END


/* HOW TO OPTIMIZE THIS ONE? */
SELECT
        #ReportDate.dates as valid_date,
        (
          SELECT 

          COUNT(DISTINCT(nonCancelledSales.num_remito)) as actives

          FROM      
                (

                    SELECT *

                    FROM salesView

                    WHERE

                        salesView.sell_id NOT IN 
                            (
                              SELECT sell_id

                              FROM salesStates

                              WHERE
                                  salesStates.aborted = 1
                            ) 

                  ) nonCancelledSales

          WHERE
                nonCancelledSales.seller_id = @SellerId AND
                nonCancelledSales.cancelled = 0 AND
                nonCancelledSales.void = 0 AND
                nonCancelledSales.hasDiscount = 0 AND
                nonCancelledSales.dateOfSale <=  #ReportDate.dates AND
                nonCancelledSales.currentState =  (SELECT   MAX(hveest.date)

                                              FROM  salesStates hveest

                                              WHERE 
                                                    hveest.sell_id = nonCancelledSales.sell_id AND
                                                    hveest.date <= #ReportDate.dates) AND
                nonCancelledSales.lastProductDate = (SELECT     MAX(hvepro.date)

                                              FROM  productHistory hvepro

                                              WHERE 
                                                    hvepro.sell_id = nonCancelledSales.sell_id AND
                                                    hvepro.date <= #ReportDate.dates) 

        ) total_actives,

        (
          SELECT 

          ISNULL(SUM(nonCancelledSales.paymentValue),0) as active

          FROM      
                (

                    SELECT *

                    FROM salesView

                    WHERE

                        salesView.sell_id NOT IN 
                            (
                              SELECT sell_id

                              FROM salesStates

                              WHERE
                                  salesStates.aborted = 1
                            ) 

                  ) nonCancelledSales

          WHERE
                nonCancelledSales.seller_id = @SellerId AND
                nonCancelledSales.cancelled = 0 AND
                nonCancelledSales.void = 0 AND
                nonCancelledSales.hasDiscount = 0 AND
                nonCancelledSales.dateOfSale <=  #ReportDate.dates AND
                nonCancelledSales.currentState =  (SELECT   MAX(hveest.date)

                                              FROM  salesStates hveest

                                              WHERE 
                                                    hveest.sell_id = nonCancelledSales.sell_id AND
                                                    hveest.date <= #ReportDate.dates) AND
                nonCancelledSales.lastProductDate = (SELECT     MAX(hvepro.date)

                                              FROM  productHistory hvepro

                                              WHERE 
                                                    hvepro.sell_id = nonCancelledSales.sell_id AND
                                                    hvepro.date <= #ReportDate.dates)             
        ) active
FROM 
        #ReportDate
GROUP BY
        #ReportDate.dates



DROP TABLE #ReportDate

Here are the two duplicated fragments I see:

(
          SELECT 

          COUNT(DISTINCT(nonCancelledSales.num_remito)) as actives

          FROM      
                (

                    SELECT *

                    FROM salesView

                    WHERE

                        salesView.sell_id NOT IN 
                            (
                              SELECT sell_id

                              FROM salesStates

                              WHERE
                                  salesStates.aborted = 1
                            ) 

                  ) nonCancelledSales

          WHERE
                nonCancelledSales.seller_id = @SellerId AND
                nonCancelledSales.cancelled = 0 AND
                nonCancelledSales.void = 0 AND
                nonCancelledSales.hasDiscount = 0 AND
                nonCancelledSales.dateOfSale <=  #ReportDate.dates AND
                nonCancelledSales.currentState =  (SELECT   MAX(hveest.date)

                                              FROM  salesStates hveest

                                              WHERE 
                                                    hveest.sell_id = nonCancelledSales.sell_id AND
                                                    hveest.date <= #ReportDate.dates) AND
                nonCancelledSales.lastProductDate = (SELECT     MAX(hvepro.date)

                                              FROM  productHistory hvepro

                                              WHERE 
                                                    hvepro.sell_id = nonCancelledSales.sell_id AND
                                                    hvepro.date <= #ReportDate.dates) 

        ) total_actives,

        (
          SELECT 

          ISNULL(SUM(nonCancelledSales.paymentValue),0) as active

          FROM      
                (

                    SELECT *

                    FROM salesView

                    WHERE

                        salesView.sell_id NOT IN 
                            (
                              SELECT sell_id

                              FROM salesStates

                              WHERE
                                  salesStates.aborted = 1
                            ) 

                  ) nonCancelledSales

          WHERE
                nonCancelledSales.seller_id = @SellerId AND
                nonCancelledSales.cancelled = 0 AND
                nonCancelledSales.void = 0 AND
                nonCancelledSales.hasDiscount = 0 AND
                nonCancelledSales.dateOfSale <=  #ReportDate.dates AND
                nonCancelledSales.currentState =  (SELECT   MAX(hveest.date)

                                              FROM  salesStates hveest

                                              WHERE 
                                                    hveest.sell_id = nonCancelledSales.sell_id AND
                                                    hveest.date <= #ReportDate.dates) AND
                nonCancelledSales.lastProductDate = (SELECT     MAX(hvepro.date)

                                              FROM  productHistory hvepro

                                              WHERE 
                                                    hvepro.sell_id = nonCancelledSales.sell_id AND
                                                    hvepro.date <= #ReportDate.dates)             
        ) active

Is it fully necessary to duplicate the query ? In the first one he's getting:

 COUNT(DISTINCT(nonCancelledSales.num_remito)) as actives

on the second one:

  ISNULL(SUM(nonCancelledSales.paymentValue),0) as active

I suppose there has to be some way to rewrite the query but I'm not sure how.