SSMS 2014 - Cannot insert multiple values into table variable

This is probably something really trivial I'm missing, but I can't seem to figure out why it's not working:

Basically, this works:

DECLARE @names TABLE (name NVARCHAR(100))
;
INSERT INTO @names
VALUES
    ('John')
;

but this does not:

DECLARE @names TABLE (name NVARCHAR(100))
;
INSERT INTO @names
VALUES
    ('John'),
    ('Jane')
;

I'm getting this error:

Msg 102, Level 15, State 1, Line 5

Incorrect syntax near ','.

Why wouldn't this work? I've done this thousands of times with SSMS 2008.

Sql query with join to return a part number that exists in one table and does not exist in another table with other field data

I'm trying to return data from one table A that does not exist in table B with certain data. For instance:

Table A     No_         Inventory Posting Group
             1           PART
             2           NEW
             3           PART

Table B     No_         Table ID       Dimension Code
             1           27             Branch
             2           27             Manufacturer
             3           27             Department

Every part No_ exists in table A. Table B needs to have both "Branch" and "Department" on a separate row. Some parts have one or the other or both in Table B. I'm trying to return results that show for every part listed in table A which ones are not present for "Branch" and "Department" in table B. It may be two separate queries. Here is what I have now, but I'm not getting what I want.

select dd.[No_], dd.[Table ID], dd.[Dimension Code], dd.[Dimension Value Code], dd.[Value Posting], dd.[Multi Selection Action]
from [Item] i left outer join [Default Dimension] dd

on  i.[No_] = dd.[No_]

where dd.[Dimension Code] not in ('BRANCH', 'DEPARTMENT')
and i.[Inventory Posting Group] = 'PAR'
and dd.[Table ID] = 27

How to get Sql Record with out any reference on given example

I have two table as table1 and table 2 as

I want one table3 structure as

I dont have any of the reference between upper two tables.

1.Is it possible in single select statement?

Or

2.Required looping for that.

What I have tried is

    select E.EmpID,E.FName,E.LName,C.CityName,E.Salary,DOJ,
    case
    when E.Salary > 0 and E.Salary < 30001 then 'Trainee'
    when E.Salary > 30001 and E.Salary < 60001 then 'Jr. Developer'
    when E.Salary > 60001 and E.Salary < 150001 then 'Sr. Developer'
    when E.Salary > 150001 and E.Salary < 180001 then 'Project Lead'
    when E.Salary > 180001 and E.Salary < 250001 then 'Project Manager'
    else '' end  as Designation 
    from Emp_Master as E inner join City_Master as C
    on E.CityID = C.CityId 

which I think is totally wrong

Please answer me Providing example

Can we drop and recreate primary key in SQL Server table @ Production environment? Do we have to down the server for it or we can do it live?

Can we drop and recreate a composite primary key in a SQL Server table in production environment? Do we have take down the server for it, or can we do it live?

Because we have to add more columns to the primary key. If we done it in live what are the problems we have to face?

ignore bcp right truncation

I have a file with the stock information, such as ticker and stock price. The file was loaded to database table using freebcp. The stock price format in the file is like: 23.125. The stock price data type in database table is [decimal](28, 2). freebcp loaded the data to the table without any problem by ignoring the last digit: 23.12 was loaded to the table column of the record. We are now using Microsoft SQL Server's bcp utility (Version: 11.0 ) to load the data. However we now encounter an issue: bcp considers loading 23.125 to decimal(28.2) is an error (#@ Row 783, Column 23: String data, right truncation @#). It rejected the record.

I didn't want to modify the input file, because there are a lot of columns in the file need to be fixed by removing the last digit of columns.

Are there any ways to construct the BCP or the Microsoft SQL Server to ingore the right truncation error?

Thanks.

Charlie

Trying to create a trigger in SQL server that converts and inserts an xml string

Have created this trigger

It completes successfully and enters into the right tables, however when it all processes through on the table that it inserts into the table the Long Message section of the trigger has "NULL" inside in there.

It is not often that I look at SQL really. The actual part of the script that converts the data to XML string on its own works and makes a string. However when it is in the trigger it doesn't populate anything.

///////////////////////////////////////////

USE [SysproCompanyP]
GO
/****** Object:  Trigger [dbo].[trig_DNIP]    Script Date: 28/09/2015 11:25:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--=======================================
-- Author       : Thomas Mullins
-- Create date  : 02/09/2015
-- Description  : Email event on a dispatch note being printed
-- Update
-- Update
--=======================================

ALTER TRIGGER [dbo].[trig_DNIP]
      ON [dbo].[MdnMaster]
      AFTER INSERT
AS 
BEGIN 

DECLARE @ConstCompany AS CHAR(1)
DECLARE @SourceCompanyFull AS CHAR(40)

DECLARE @DispatchNote AS CHAR(15)
DECLARE @SalesOrder AS CHAR(6)
DECLARE @CustomerName AS CHAR(30)
DECLARE @Customer AS CHAR(7)
DECLARE @var_LongMessage AS CHAR (255)



SET @SourceCompanyFull = DB_NAME()
SET @ConstCompany = UPPER(RIGHT(RTRIM(@SourceCompanyFull),1))

IF @ConstCompany = 'S'
BEGIN 
   SET @ConstCompany = '0'
END


SELECT 
    @DispatchNote = RTRIM(DispatchNote), 
    @SalesOrder = RTRIM(SalesOrder),
    @CustomerName = RTRIM(CustomerName), 
    @Customer = RTRIM(Customer)
FROM INSERTED 


SET NOCOUNT ON; 

IF @Customer in ('CN01PIO', 'CN01WUH') --AND datepart(yyyy, @CreationDate) = datepart(yyyy, getdate() and datepart(mm, @CreationDate = datepart(mm, @CreationDate) = datepart (mm,getdate()) and datepart(dd, @CreationDate) = datepart(dd, getdate()
BEGIN 

    SET @var_LongMessage = (SELECT (STUFF((
               SELECT ',  ' + RTRIM(WRL.StockCode) + ' ' + RTRIM(WRL.Job) + '     '
               FROM SysproCompanyP.dbo.MdnDetailLot (nolock) AS MDL 
               LEFT OUTER JOIN SysproCompanyP.dbo.WipReservedLots (nolock) AS WRL on WRL.Lot = MDL.Lot 
               WHERE MDL.DispatchNote = RTRIM(@DispatchNote)
               GROUP BY WRL.StockCode, WRL.Job 
               FOR XML PATH('')
               ), 1, 2, '')
            ))

INSERT INTO [SysproCompanyC].[dbo].[EmailEvents]
       ([SourceCompany]         ,[SourceCompanyFull]    ,[ParentValue]          ,[CFFName]          ,[CFFData]  ,[TxtFileExtension]  ,[DateEntered]    ,[LongMessage])
VALUES 
       (@ConstCompany          ,@SourceCompanyFull    ,@SalesOrder            ,@Customer      ,@DispatchNote    ,'DNIP'                ,GETDATE()       ,@var_LongMessage)

INSERT INTO [Piolax_Logging].[dbo].[EventLogging]   ([Company],[EventType],[Source],[Annotation],[NumberOfRecords],[DateTime])
        VALUES (@ConstCompany           ,'INFO'           ,'[trig_DNIP]'           ,'Dispatch Note Printed  '  + @DispatchNote + @ConstCompany + @SourceCompanyFull + @SalesOrder  + @Customer + 'DNIP' + HOST_NAME(),1           ,GETDATE())
END 




END

If anybody could give my any info that would be great, thanks.

unable to connect SQL server 2005 VC++ using ATL and window auth

I can connect to DB without using winAuth, but not ok if I using winAuth.

Here is the permission setting, I am using EFTG\eftg_app_svc

I cannot post image, but I believe I have correct permission setting.

And configured the SQL server can use window auth.

And here is my code:

HRESULT OpenDataSource()
    {
        HRESULT     hr;
        CDataSource db;
        CDBPropSet  dbinit(DBPROPSET_DBINIT);
    if (m_DBCfg.GetWindowsAuthFlag())
    {
        dbinit.AddProperty(DBPROP_AUTH_INTEGRATED, OLESTR("SSPI"));
    }
    else
    {
        dbinit.AddProperty(DBPROP_AUTH_PASSWORD, OLESTR(""));
        dbinit.AddProperty(DBPROP_AUTH_USERID, m_DBCfg.GetDatabaseUsername());
    }
    dbinit.AddProperty(DBPROP_INIT_CATALOG, m_DBCfg.GetDatabaseName());
    dbinit.AddProperty(DBPROP_INIT_DATASOURCE, m_DBCfg.GetDatabaseServer());
    dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
    dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
    dbinit.AddProperty(DBPROP_INIT_OLEDBSERVICES, DBPROPVAL_OS_RESOURCEPOOLING);
    hr = db.OpenWithServiceComponents(_T("SQLOLEDB.1"), &dbinit);
    if (FAILED(hr))
        return hr;

But DB eventlog: -> Login failed for user 'EFTG\eftg_app_svc'. [CLIENT: ] What's wrong???

Thanks very much!!

Update master/child rows

I have tables [Cases] and [CDegrees] with relacion [Cases].[CID] - [CDegrees].[CID]. [CID] is PK of [Cases] with AutoIncrement. Both tables are used in one form and it's assumed that user can add new records to both tables same time and than save whole master/child data in one GUI action. So in Dataset where I created FK I set "Both Relation and Foreign Key Constraint" to ensure during update of new record in Cases table, retrieved IDENTITY value will cause child records to update from -1 to retrieved [CID] value. When I update [Cases] adapter, it causes to retrieve an new IDENTITY value and cascade update in [CDegrees] child record works ok too. But [CDegrees]'s update causes Insert script with [CID]=-1 (original value). I changed Insert parameter [@CID] of [CDegrees] to "Proposed" version but same happens (seen in SQL Profiler). Actually my task is mach more complicated, I just simplified task to localize my problem. Any ideas?

How Can I Use Count Function for the different values of the same column in MS-SQL?

I have created a Database StudentInfo and a table named Student as follows:

    CREATE TABLE Student(
ID INT PRIMARY KEY IDENTITY(1,1),
Name nvarchar(255)
)

and inserting the values: Insert Into Student Values ('Ashok') Executing it 3 times and Insert Into Student Values ('Achyut')Executing it 2 times and total 5 data are inserted into table. I want to display a result counting the result with the Name Having 'Ashok' & 'Achyut'.

Generally for Single Values Count in a column i use: SELECT Count(Name) as NoOfStudentHavingNameAshok From Student WHERE Name='Ashok'

but to dispaly the NoOfStudentHavingNameAshok & NoOfStudentHavingNameAchyut what query should i run?

Order By Used with Case Statement

i use Order By Used with Case Statement and may be this is the reason for which i am getting error Windowed functions can only appear in the SELECT or ORDER BY clauses.

here is my complete sql where i use Row_Number function for pagination with sorting. please guide me where i made the mistake.

DECLARE @StartIndex INT
DECLARE @EndIndex   INT

DECLARE @SortColumn VARCHAR(MAX)
DECLARE @SortDirection VARCHAR(MAX)

SET @StartIndex = 1
SET @EndIndex = 20
SET @SortColumn = 'OrderID'
SET @SortColumn = 'A'

SELECT * FROM Orders
WHERE
  ROW_NUMBER() OVER 
  (
    ORDER BY
        CASE (@SortColumn + ':' + @SortDirection)
            WHEN 'OrderID:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderID ASC)
            WHEN 'OrderID:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderID DESC)
            WHEN 'CustomerID:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.CustomerID ASC)
            WHEN 'CustomerID:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.CustomerID DESC)
            WHEN 'EmployeeID:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.EmployeeID ASC)
            WHEN 'EmployeeID:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.EmployeeID DESC)
            WHEN 'OrderDate:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderDate ASC)
            WHEN 'OrderDate:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderDate DESC)
            WHEN 'RequiredDate:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.RequiredDate ASC)
            WHEN 'RequiredDate:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.RequiredDate DESC)
            WHEN 'ShippedDate:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderID ASC)
            WHEN 'ShippedDate:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderID DESC)
            WHEN 'ShipVia:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipVia ASC)
            WHEN 'ShipVia:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipVia DESC)
            WHEN 'Freight:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.Freight ASC)
            WHEN 'Freight:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.Freight DESC)
            WHEN 'ShipName:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipName ASC)
            WHEN 'ShipName:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipName DESC)
            WHEN 'ShipAddress:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipAddress ASC)
            WHEN 'ShipAddress:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipAddress DESC)
            WHEN 'ShipCity:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipCity ASC)
            WHEN 'ShipCity:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipCity DESC)
            WHEN 'ShipRegion:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipRegion ASC)
            WHEN 'ShipRegion:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipRegion DESC)
            WHEN 'ShipPostalCode:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipPostalCode ASC)
            WHEN 'ShipPostalCode:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipPostalCode DESC)
            WHEN 'ShipCountry:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipCountry ASC)
            WHEN 'ShipCountry:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipCountry DESC)

        END 
  ) BETWEEN (@StartIndex - 1) * @EndIndex + 1 AND @StartIndex * @EndIndex 
  /* AND more conditions ... */
ORDER BY
  CASE WHEN @SortDirection = 'A' THEN
    CASE @SortColumn 
        WHEN 'OrderID'    THEN OrderID
        WHEN 'CustomerID' THEN CustomerID
        WHEN 'EmployeeID' THEN CustomerID      
        WHEN 'OrderDate' THEN CustomerID
        WHEN 'RequiredDate' THEN CustomerID
        WHEN 'ShippedDate' THEN CustomerID
        WHEN 'ShipVia' THEN CustomerID
        WHEN 'Freight' THEN CustomerID
        WHEN 'ShipName' THEN CustomerID
        WHEN 'ShipAddress' THEN CustomerID
        WHEN 'ShipCity' THEN CustomerID
        WHEN 'ShipRegion' THEN CustomerID
        WHEN 'ShipPostalCode' THEN CustomerID
        WHEN 'ShipCountry' THEN CustomerID
    END
  END,
  CASE WHEN @SortDirection = 'D' THEN
    CASE @SortColumn 
        WHEN 'OrderID'    THEN OrderID
        WHEN 'CustomerID' THEN CustomerID
        WHEN 'EmployeeID' THEN CustomerID      
        WHEN 'OrderDate' THEN CustomerID
        WHEN 'RequiredDate' THEN CustomerID
        WHEN 'ShippedDate' THEN CustomerID
        WHEN 'ShipVia' THEN CustomerID
        WHEN 'Freight' THEN CustomerID
        WHEN 'ShipName' THEN CustomerID
        WHEN 'ShipAddress' THEN CustomerID
        WHEN 'ShipCity' THEN CustomerID
        WHEN 'ShipRegion' THEN CustomerID
        WHEN 'ShipPostalCode' THEN CustomerID
        WHEN 'ShipCountry' THEN CustomerID
    END 
  END DESC

SQLServer connection timeout

I've been working for a long time with a SQLServer 2015 project, logging in with win authentication. (SQLServer 2015).

Today I have a Timeout on my connection.

Why? Today I've changed my Password on windows, forgetting the SQLServer connection. UPS!!! The connection fails and I've restored the old psswd... and the connection fails... mmmm?

Then I've checked my connections on ODBC (I have a couple of apps running), and connection success.... lol?

Then I've returned to SQLServer (I'm trying to logg in with SQLServer Management Studio) and the connection fails. .... OMG!

I am trying to logg to my SERVERDB from my SERVERAPP with Management Studio 2015. ODBC loggs in, but management Studio fails. Both with windows autenthication.

I am not an expert with connections (nor on english ¬¬), but this is so strange.

Someone may help me, I'm getting crazy about that.

Thank you very much. I've been looking on web but no cases like that.

ConCat Fields in Different Rows with Same ID SQL 2005

I am Looking to Concat a field in each row that that holds text input....

I am using SQL Server 2005

The rows look like this

Number     Date           Update Time     description
------    -----           -----------    -------------
0123      01/01/2015      01/07/2015     Hello, i am wanting to
0123      01/01/2015      01/01/2015     Concat these fields

Hopefully this is easy and i am just being a simpleton

Query to retain tuples which were not subtracted

I have two tables as follows

table1

name number
a    100
b    150
c    200

table 2

name number
a    10
b    20

How can I write a query to subtract both tables and retain values such as c

Output should be:

name number
a     90
b     130
c     200

I know how to get a and b just couldn't figure out how to keep c as well.

How to compatible datagridview on demand data loading with store procedure instead of in-line sql

i like to refer a code which i have taken from this url

http://ift.tt/1NMgULN

the above url shown how we can load data by datagridview just on demand means when user scroll and reach a limit then data will load from db. my code is working but now i am in situation that i have to use store procedure from sql server which is very big and it return data after so many calculation. so i have to customize my code as a result it should be working with store procedure instead of in-line sql. from the above link anyone can see full working sample code. i just here highlight 3 routine from where in-line sql fired to fetch data from db and this routine i need to change and want to call store proc from there instead of in-line sql.

from this one route use in-line sql

public int RowCount
        {
            get
            {
                // Return the existing value if it has already been determined. 
                if (rowCountValue != -1)
                {
                    return rowCountValue;
                }

                if (filters.Trim().ToUpper().IndexOf("WHERE") > -1)
                {
                    filters = filters.ToUpper().Replace("WHERE", string.Empty);
                }

                // Retrieve the row count from the database.
                command.CommandText = "SELECT COUNT(*) FROM " + tableName + " WHERE 1=1 " + (filters.Trim().Length > 0 ? " AND " : string.Empty) + filters;
                rowCountValue = (int)command.ExecuteScalar();
                return rowCountValue;
            }
        }

this routine too use in-line sql

public DataColumnCollection Columns
        {
            get
            {
                // Return the existing value if it has already been determined. 
                if (columnsValue != null)
                {
                    return columnsValue;
                }

                // Retrieve the column information from the database.
                command.CommandText = "SELECT * FROM " + tableName;
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = command;
                DataTable table = new DataTable();
                table.Locale = System.Globalization.CultureInfo.InvariantCulture;
                adapter.FillSchema(table, SchemaType.Source);
                columnsValue = table.Columns;
                return columnsValue;
            }
        }

this routine too

public DataTable SupplyPageOfData(int lowerPageBoundary, int rowsPerPage)
        {
            // Store the name of the ID column. This column must contain unique  
            // values so the SQL below will work properly. 
            if (columnToSortBy == null)
            {
                columnToSortBy = this.Columns[0].ColumnName;
            }

            if (!this.Columns[columnToSortBy].Unique)
            {
                throw new InvalidOperationException(String.Format(
                    "Column {0} must contain unique values.", columnToSortBy));
            }

            // Retrieve the specified number of rows from the database, starting 
            // with the row specified by the lowerPageBoundary parameter.
            if (filters.Trim().ToUpper().IndexOf("WHERE") > -1)
            {
                filters = filters.ToUpper().Replace("WHERE", string.Empty);
            }

            command.CommandText = "Select Top " + rowsPerPage + " " +
                CommaSeparatedListOfColumnNames + " From " + tableName +
                " WHERE 1=1 AND " + filters + " " + (filters.Trim().Length > 0 ? " AND " : string.Empty) + columnToSortBy + " NOT IN (SELECT TOP " +
                lowerPageBoundary + " " + columnToSortBy + " From " +
                tableName + "  WHERE 1=1 " + (filters.Trim().Length > 0 ? " AND " : string.Empty) + filters + " Order By " + sortColumn +
                ") Order By " + sortColumn;
            adapter.SelectCommand = command;

            DataTable table = new DataTable();
            table.Locale = System.Globalization.CultureInfo.InvariantCulture;
            adapter.Fill(table);
            return table;
        }

now i want to use store proc in one routine which replace other two routine.

i will develop store proc whose 1st result will return no of rows and second result will return actual data.

this way i am paging in sp

WHERE [rn] BETWEEN ((@StartIndex-1) * @EndIndex ) + 1 AND (@StartIndex * @EndIndex)

now see the above routine called SupplyPageOfData how it is doing paging and tell me how to use lowerPageBoundary and rowsPerPage as a result i could send those two value in my sp and sp can do the successfully paging.

rowsPerPage is fixed that is 16 but lowerPageBoundary jump by 16. so calculation is not coming to my mind like how to change code as a result i can send lowerPageBoundary & rowsPerPage to store proc and with in store proc i can form paging line like WHERE [rn] BETWEEN ((@StartIndex-1) * @EndIndex ) + 1 AND (@StartIndex * @EndIndex)

please help me with code and sample. thanks

Default schema for DROP and SELECT INTO in stored procedures

I am a bit confused as to how the default database schema is determined in MS SQL server.

I have the following stored procedure as a minimal working example:

CREATE PROCEDURE [dbo].[SampleSP]
AS

SELECT 'HI' as [SampleColumn]
INTO [SampleTable]

DROP TABLE [SampleTable]

All tests are executed using a user User on MS SQL server using Windows Auth with a default database schema of the same name.

• When I execute this SP on MS SQL Server 2005 installation (running in compatibility mode 80, i.e. Server 2000) the table is created as [User].[SampleTable] and DROP TABLE fails with Invalid object name 'SampleTable' (I assume because it looks for [dbo].[SampleTable])
• When I DROP TABLE [SampleTable] in a separate query it does work
• When I execute the SP on MS SQL Server 2008 R2 (also running in compat. 80) the table is created as [dbo].[SampleTable] and dropped without error

I have found this answer describing the lookup in stored procedures, but it doesn't mention the user default in that context, even though it is used on 2005. Maybe someone knows how this changed and whether new versions can be configured to behave the same way.

Hirarchical sorting in sql server when child id contains '.'

i have a parent child relation like below

childid
------------

1.1
1.2
2.8
2.7
6.5
6.5.1
6.5.15
7.1
8

sort order will be

childid 
--------
1.1
1.2
2.7
2.8
6.5
6.51
6.5.15
7.1

converted to intiger like below

declare @str nvarchar(max)='1.23.2';

set @str=(select replace(@str,'.',''))
select @str

but it fails when

7.1
8

comes it gives order like

8
7.1 

but i need order like below

7.1
8

also if number like

7.1.1
7.1.8
6.7.7.7

then order should be

6.7.7.7
7.1.1
7.1.8

i hope somebody can help me to solve this

Can't makes BCP to export data from view to csv

I need to export data from view to csv file. The easiest way, as it seems to me is BCP. I tried to read MSDN http://ift.tt/1wffzae for syntax help, but I can't makes bcp to create file cause of error (running bcp from cmd). Thanks in advance for any help with syntacsis.

bcp [base_name].[dbo].[all_users_for_ad] out 1.csv -t -c -u mylogin -p mypassword -s mysever


bcp:  unknown option u
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]

How to do the pagination in sql server 2005 when multiple table used by joining

i know people use ROW_NUMBER() function to do the pagination but my below two query is bit complex. so i need suggestion like how to use pagination there ?

i used ROW_NUMBER() OVER(ORDER BY IsNull(A.OEReference, B.OEReference) ASC) as Line in one but not sure am i right or wrong.

 IF IsNull(@GroupID,'') = ''      
  SELECT  IsNull(PartGroupName, 'UnMapped') AS PartGroupName,      
     CASE IsNull(PartGroupName, '')      
      WHEN '' THEN ''       
      ELSE IsNull(IsNull(K.GroupID, IsNull(C.PartGroupID,'')),'')      
     END AS PartGroupID,      
     IsNull(A.OEReference, B.OEReference) AS OEReference,   
     IsNull(SaleDone,0) AS [SALE/OUR],        
     IsNull(WarrantyDone, 0) AS [WARRANTY/WARRANTYOUR]      
  FROM  @Sale A FULL OUTER JOIN @Warranty B ON A.OEReference = B.OEReference      
     LEFT OUTER JOIN BBAPart K ON IsNull(A.OEReference,B.OEReference) = K.Stock_code      
     LEFT OUTER JOIN BBAPartGroup C ON K.GroupID=C.PartGroupID AND C.IsDeleted = 0  
  ORDER BY IsNull(PartGroupName, 'UnMapped')   
 ELSE      
  SELECT  IsNull(PartGroupName, 'UnMapped') AS PartGroupName,      
     CASE IsNull(PartGroupName, '')      
      WHEN '' THEN ''       
      ELSE IsNull(IsNull(K.GroupID, IsNull(C.PartGroupID,'')),'')      
     END AS PartGroupID,      
     IsNull(A.OEReference, B.OEReference) AS OEReference,   
     IsNull(SaleDone,0) AS [SALE/OUR],        
     IsNull(WarrantyDone, 0) AS [WARRANTY/WARRANTYOUR]

  FROM  @Sale A FULL OUTER JOIN @Warranty B ON A.OEReference = B.OEReference      
     LEFT OUTER JOIN BBAPart K ON IsNull(A.OEReference,B.OEReference) = K.Stock_code      
     LEFT OUTER JOIN BBAPartGroup C ON K.GroupID=C.PartGroupID AND C.IsDeleted = 0      
  WHERE  PartGroupID IN (SELECT Data FROM dbo.stringtotable(@GroupID, ',') )      
  ORDER BY IsNull(PartGroupName, 'UnMapped')   
END   

how to convert xml string to datetime and float in sql server 2005?

I refer following article to send datatable in c# to sql server. it works but I want to send datetime and float value to my database. how can I do it as referred article.please help me.

http://ift.tt/1Wf0wX2

cast(colx.query('data(dob) ') as datetime) as dob,
cast(colx.query('data(salery) ') as float)     as salery

Return newest row when several columns are the same

I have a very complex Union query that returns up to two rows. If the second through the 15th columns are identical in both records, I want to return the results of the second record. Otherwise, return the results of the first record which is the newest record. The goal is to return the newest name and address with a pending flag only if the data is not the same.

SELECT  TOP 1 
                        1 AS updatePending,
                        a.entity_number,
                        a.name_title,
                        a.name_first,
                        a.name_middle,
                        a.name_last,
                        a.name_suffix,
                        LTRIM(RTRIM(REPLACE(
                        LTRIM(RTRIM(ISNULL(a.name_first, ''))) +
                        CASE WHEN LEN(LTRIM(RTRIM(ISNULL(a.name_first, '')))) = 1 THEN '. ' ELSE ' ' END +
                        LTRIM(RTRIM(ISNULL(a.name_middle, ''))) +
                        CASE WHEN LEN(LTRIM(RTRIM(ISNULL(a.name_middle, '')))) = 1 THEN '. ' ELSE ' ' END +
                        LTRIM(RTRIM(ISNULL(a.name_last, ''))) + ' ' +
                        LTRIM(RTRIM(ISNULL(a.name_suffix, '')))
                        ,'  ',' '))) AS name_full,
                        NULLIF(LTRIM(RTRIM(
                        LTRIM(RTRIM(ISNULL(a.company, ''))) +
                        LTRIM(RTRIM(ISNULL(a.firm_name, ''))))),'') AS company,
                        a.address1,
                        a.mailing_address,
                        a.city,
                        a.state,
                        a.zip_code AS zipcode,
                        a.internet_address AS email_address,
                        a.time_stamp
        FROM        statebar.dbo.STAGING_Address_Change_Request a
            INNER JOIN Member m ON m.entity_number = a.entity_number
        WHERE       a.entity_number = (
SELECT m.entity_number
FROM Member m
    INNER JOIN  Named_Entity ne ON  (ne.entity_number = m.entity_number)
WHERE   ne.name_last = 'Park'
    AND m.birth_year = '1958'
    AND m.barno = '12345'
    )
            AND a.time_stamp > m.time_stamp

        UNION ALL

        SELECT TOP 1 
                0 AS updatePending,
                ne.entity_number,
                ne.name_title,
                ne.name_first,
                ne.name_middle,
                ne.name_last,
                ISNULL(ne.name_suffix, ''),
                LTRIM(RTRIM(REPLACE(
                LTRIM(RTRIM(ISNULL(ne.name_first, ''))) +
                CASE WHEN LEN(LTRIM(RTRIM(ISNULL(ne.name_first, '')))) = 1 THEN '. ' ELSE ' ' END +
                LTRIM(RTRIM(ISNULL(ne.name_middle, ''))) +
                CASE WHEN LEN(LTRIM(RTRIM(ISNULL(ne.name_middle, '')))) = 1 THEN '. ' ELSE ' ' END +
                LTRIM(RTRIM(ISNULL(ne.name_last, ''))) + ' ' +
                LTRIM(RTRIM(ISNULL(ne.name_suffix, '')))
                ,'  ',' '))) AS name_full,
                NULLIF(LTRIM(RTRIM(
                LTRIM(RTRIM(ISNULL(ne.company, ''))) +
                LTRIM(RTRIM(ISNULL(ne.firm_name, ''))))),'') AS company,
                ISNULL(ne.address1, ''),
                ne.mailing_address,
                ne.city,
                ne.state,
                ne.zip_code,
                ne.internet_address AS email_address,
                m.time_stamp
        FROM    Member m
            INNER JOIN  Named_Entity ne ON  (ne.entity_number = m.entity_number)
            LEFT JOIN   statebar.dbo.STAGING_Address_Change_Request a ON a.entity_number = m.entity_number
        WHERE   ne.entity_number = (
                                    SELECT m.entity_number
                                    FROM Member m
                                        INNER JOIN  Named_Entity ne ON  (ne.entity_number = m.entity_number)
                                    WHERE   ne.name_last = 'Park'
                                        AND m.birth_year = '1958'
                                        AND m.barno = '12345'
                                        )
            AND m.time_stamp > a.time_stamp
        ORDER BY updatePending DESC, a.time_stamp DESC

Query to return values from table B when newer than record in Table A

I have a table with a member's name, address, etc. and a time stamp of the last time the record was updated. I have a second table that holds updates to the member record, a holding table, until changes are approved by staff.

I have a query that returns data from the member table. I now need to check the updates table, and if the member's record in the updates table has a more recent time stamp, return that record instead of the record in the member table.

I tried a few things such as a UNION with Top 1 but it's not quite right. I could make a complex CASE statement but is that going to perform well?

It sounds simple, get the most recent record from table A, and the most recent from table B and return the one record that is the newest.

SELECT name, address, city, state, zipcode, time_stamp
FROM Member
WHERE ID = 123

SELECT name, address, city, state, zipcode, time_stamp
FROM MemberUpdates
WHERE ID = 123

How to declare variables within dynamic columns in SQL Server 2005 using PIVOT

I've checked out this and this, but I am lost.

For some reason I cannot declare the @wsDateFrom & @wsDateTo variables to be used inside the following dynamic column SQL code.

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

DECLARE @wsDateFrom AS smalldatetime
DECLARE @wsDateTo   AS smalldatetime

SET @wsDateFrom = '01-JAN-2015'
SET @wsDateTo   = '30-JUN-2015'

select @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(a.AbsenceDescription) 
                      FROM dbo.tblAbsentCodes AS a
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

set @query = N'SELECT StudentID, ' + @cols + 
             'FROM  (SELECT a.StudentID, ab.AbsenceDescription, a.AttendanceID
                     FROM   dbo.tblAttendance AS a
                        INNER JOIN tblCalendar c 
                            ON a.DateID = c.DateID
                        INNER JOIN tblAbsentCodes as ab
                            ON ab.AbsenceID = a.AbsenceID
                     WHERE c.DayDate BETWEEN @wsDateFrom AND @wsDateTo) AS p
              PIVOT (COUNT(AttendanceID) FOR AbsenceDescription IN (' + @cols + ')) AS pvt '

execute(@query)

The error I am getting is

Must declare the scalar variable "@wsDateFrom".

But it's there! Or should I be placing the DECLARE somehow inside the @query?

If that's the case then how would I pass those two date variables in a function or stored-procedure? It would open it up for sql-injection wouldn't it?

Filter latitude and longitude records based on for given latitude and longitude for given n kilometre [duplicate]

This question already has an answer here:

• SQlite Getting nearest locations (with latitude and longitude) 7 answers

I have latitude and longitude stored in a table and I want to filter the records that are in n kilo meter radius from the given latitude and longitude.

I searched sql-server management studio system functions But it is not having any.

any valuable suggestions on how do I go about solving this issue would be helpful..

Note:

I am looking for a solution that uses just plain-sql coupled with forumlas so that it can be used in other db such as sqlite too.

Msg 102, Level 15, State 1, Procedure insertRetailer, Line 24 Incorrect syntax near ','

Msg 102, Level 15, State 1, Procedure insertRetailer, Line 24 Incorrect syntax near ','.

>SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[insertRetailer]
    -- Add the parameters for the stored procedure here
    @RetailerId bigint output,
    @Name varchar(255),
    @Pin char (4),
    @EmailAdress varchar(255),
    @MonthlyOrderLimit numeric(10,2),
    @DiscountPercentage numeric(2,2),
    @URL varchar(255)

AS
BEGIN

Does anybody know how to solve this error?

Passing query parameters in Pandas

Trying to write a simple pandas script which executes a query from SQL Server with WHERE clause. However, the query doesnt return any values. Possibly because the parameter is not passed? I thought we could pass the key-value pairs as below. Can you please point out what i am doing wrong here?

Posting just the query and relevant pieces. All the libraries have been imported as needed.

  curr_sales_month = '2015-08-01'
  sql_query = """SELECT sale_month,region,oem,nameplate,Model,Segment,Sales  FROM [MONTHLY_SALES] WHERE Sale_Month = %(salesmonth)s"""
    print ("Executed SQL Extract", sql_query)

    df = pd.read_sql_query(sql_query,conn,params={"salesmonth":curr_sales_month}) 

The program returned with: Closed Connection - Fetched 0 rows for Report

    Process finished with exit code 0

Removing everything after ',' or '.' in a string - SQL Server

Although I checked the answers on StackOverflow, I am not sure hot to apply it on my specific case:

Select s.saleno, s.comments,
case 
    when Charindex('held by', s.comments) > 0 
    then substring(s.comments, Charindex('held by', s.comments)+8,40)
    else null end as TrimmedText,
FROMsales s
JOIN push p 
ON p.saleno = s.saleno
WHERE Charindex('held by', s.comments) > 0;

The issue is that I get result like:

For Company1, due ti
Company1.</p>
Long Company1, is a scan 
Order Company1, originally dated 01/01/2000
Super Company1.</p>
My Company1, due 02/01/2010.</p>

I always want to keep "[...] Company1" and always a dot or a comma appears after this string.

Thank you for the help!

Cannot insert duplicate key row in object with unique index The duplicate key value SQL Server

I'm getting this error:

Cannot insert duplicate key row in object 'dbo.CLIENT' with unique index 'CLIENT_idx_A'. The duplicate key value is (338393005, 1).

When I checked, the indexes, it's created like this:

CREATE UNIQUE NONCLUSTERED INDEX [CLIENT_idx_A] ON [dbo].[CLIENT] 
(
     [SOURCE_SYSTEM_CLIENT_ID] ASC,
     [SOURCE_SYSTEM_ID] ASC
)
     WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE  = OFF,
           SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
           DROP_EXISTING = OFF, ONLINE = OFF,
           ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]

Actually, I want to update one value in SOURCE_SYSTEM_CLIENT_ID and my SOURCE_SYSTEM_ID is 1 for all the SOURCE_SYSTEM_CLIENT_ID that I want to update. So I think, its encountering duplicate. How can I solve this?

SQL Server: is it to drop a SQL Server 2005 by sqlcmd or set an instance to SQL Server 2012?

I have a problem I have an instance called SQLEXPRESS previously created by SQL Server 2005 Express.

Now it is uninstalled but it didn't remove the instance.

How can I remove it and after to create it with an instance of SQL Server 2012?

The instance is: AC5099\SQLEXPRESS

Thanks

Not in and Exists not working as i expect

all. My question is easy, but I can't solve it by myselft. I have 2 tables: et_pics.ob_no where ob_no is user id and et_thanks_2014 where thnk.e_to is a link to et_pics.ob_no. I need to find out ob_no in et_pics who absent in e_to in et_thanks_2014.

SELECT pics.ob_no, thnk.e_to FROM et_pics pics 
left join et_thanks_2014 thnk on thnk.e_to = pics.ob_no
WHERE e_to is null

This code works, but I thinks it's not the best way to solve my task. I've tried to solve it with IN predict:

SELECT pics.ob_no FROM et_pics pics 
WHERE pics.ob_no  in ((SELECT e_to FROM et_thanks_2014))

and not exists

SELECT ob_no from et_pics
WHERE not exists (SELECT DISTINCT (e_to) FROM et_thanks_2014 thnk)

but both returns nothing. Why?

Database data encryption in MSSQL for web application

I am trying to saved the data in database table in encrypted format to hide the data store in the database table to be read by the user

Any advice how can i do this with out much effort on application level with less overhead on my application side

Aggregate value by any of two columns

Suppose I have a Customers Table:

Cutsomers
-----------------------------------------------
Id INTEGER
SSN NCHAR(11)
FullName NVARCHAR(100)
LastPurchaseDate DATETIME

There are many stores around the city, and the customer can register in any of them, each one giving him a different Id. Whenever he buys, the corresponding Id gets it's LastPurchaseDate updated.

Now I need to get the Id corresponding to the 'latest' LastPurchaseDate by person. Problem is, due to X different reasons, there can be typos on either the SSN or the FullName. Let's say I have the next data:

Id          SSN            FullName      LastPurchaseDate
----------- -----------    ------------- -----------------
200123      123-45-6789    John Doe      10-09-2015 
201978      456-78-9012    Mary Jane     15-08-2015 
380789      789-01-2345    Pete Zahut    01-08-2015 
389236      123-45-6789    Jhon Doe      23-07-2015 
215875      456-87-9012    Mary Jane     30-08-2015 
974186      123456789      John Doe      28-04-2015 
123758      789-01-2345    Pete Zaut     18-08-2015 

A customer is considered to be the same person if it has either the same SSN or the same FullName. So in this sample, customers 200123, 389236 and 974186 are the same person. Therefore, the resulting Ids should be

200123
215875
123758

How could I achieve this?

update/insert/select query performance issue

Hi I have a question in sql server

I have 3 tables : 1) Targettable 2) sourcetable 3) sourcetable2 Target table and source table have columns 35 columns and all are varchar datatype Here I want Implement cocncept Incremental concept(scd) type 2 concept in sql server using merge statment.

Source table have records : 3 and half core records few off condition to implement scdtype 2 concept 1) I need fetch sourcetable1 table which are records not avilable sourcetable2 that records only 2)col4,col7,col8,col9,col10,col11 columns frequently change data we need to maintain history and latest

I done like this steps: 1)Handled null/empty values for all columns 2)I Created nonclustered index for col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11 Reason for nonclustered index to fetch records fastly 3)I apply merge statment to implement scdtype2 concept.

query look like below:

INSERT INTO Test.[dbo].[targettable]
([col1],[col2],[col3],[col4],col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,
col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col31,col32,col33
,col34,col35)
    SELECT 
       [col1],[col2],[col3],[col4],col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,
col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col31,col32,col33
,col34,col35
      ,[Flag],[RecordValidFromDate],[RecordExpiredDate]
  FROM (
  merge   Test.[dbo].[targettable]  target
  using( 
select * from 
 (select [col1],[col2],[col3],[col4],col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,
col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col31,col32,col33
,col34,col35
,
ROW_NUMBER()over(partition by  col1 order by col5 desc,col6 desc)as rn 
from    Test1.[dbo].[sourcetable] a
where   col2 !='820' and isnull(col3,'') !='')a

where a.rn=1 and not exists 
(select 1 from  Test1.[dbo].[sourcetable2] b where a.col1=b.pcol1)
 ) stag 
on target.[col1]=stag.[col1]
when not matched then
insert ([col1],[col2],[col3],[col4],col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,
         col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col31,col32,col33
         ,col34,col35,[Flag],[RecordValidFromDate],[RecordExpiredDate]
    )values 
        ( CASE WHEN coalesce(ltrim(rtrim(stag.[col1])),'') = '' THEN 'NA' ELSE ltrim(rtrim(stag.[col1])) END
        ,CASE WHEN coalesce(ltrim(rtrim(stag.col2)),'') = '' THEN '1800-01-01' ELSE ltrim(rtrim(stag.col2)) END
        , CASE WHEN coalesce(ltrim(rtrim(stag.col3)),'') = '' THEN '1800-01-01' ELSE ltrim(rtrim(stag.col3)) END
        ,  CASE WHEN coalesce(ltrim(rtrim(stag.col4)),'')= '' THEN 'NA' ELSE ltrim(rtrim(stag.col4)) END

         ------similary way I done upto 35 columns to hanndel empty and null value for all columns-----------------------------------------
        , CASE WHEN coalesce(ltrim(rtrim(stag.col35)),'') = '' THEN 'NA' ELSE ltrim(rtrim(stag.col35)) END
        ,'1',getdate(),'1800-01-01'
                  )
when matched and target.flag=1 and 
                 (CASE WHEN coalesce(ltrim(rtrim(target.col4)),'') = '' THEN 'NA' ELSE ltrim(rtrim(target.col4)) END
        <>CASE WHEN coalesce(ltrim(rtrim(stag.col4)),'') = '' THEN 'NA' ELSE ltrim(rtrim(stag.col4)) END
        or CASE WHEN coalesce(ltrim(rtrim(target.col7)),'') = '' THEN 'NA' ELSE ltrim(rtrim(target.col7)) END
        <>CASE WHEN coalesce(ltrim(rtrim(stag.col7)),'') = '' THEN 'NA' ELSE ltrim(rtrim(stag.col7)) END
        orCASE WHEN coalesce(ltrim(rtrim(target.col8)),'') = '' THEN 'NA' ELSE ltrim(rtrim(target.col8)) END
        <>CASE WHEN coalesce(ltrim(rtrim(stag.col8)),'') = '' THEN 'NA' ELSE ltrim(rtrim(stag.col8)) END
        or CASE WHEN coalesce(ltrim(rtrim(target.col9)),'') = '' THEN 'NA' ELSE ltrim(rtrim(target.col9)) END
        <>CASE WHEN coalesce(ltrim(rtrim(stag.col9)),'') = '' THEN 'NA' ELSE ltrim(rtrim(stag.col9)) END
        or CASE WHEN coalesce(ltrim(rtrim(target.col10)),'') = '' THEN 'NA' ELSE ltrim(rtrim(target.col10)) END
                <>CASE WHEN coalesce(ltrim(rtrim(stag.col10)),'') = '' THEN 'NA' ELSE ltrim(rtrim(stag.col10)) END
        or  CASE WHEN coalesce(ltrim(rtrim(target.col11)),'') = '' THEN 'NA' ELSE ltrim(rtrim(target.col11)) END
        <> CASE WHEN coalesce(ltrim(rtrim(stag.col11)),'') = '' THEN 'NA' ELSE ltrim(rtrim(stag.col11)) END
                 )
       then update set target.flag='0',target.[RecordExpiredDate]=getdate()
       output $ACTION ACTIONOUT,
       stag.[col1],stag.[col2],stag.[col3],stag.[col4],stag.col5,stag.col6,stag.col7,stag.col8,stag.col9,stag.col10,stag.col11,stag.col12,stag.col13,stag.col14,
stag.col15,stag.col16,
stag.col17,stag.col18,stag.col19,stag.col20,stag.col21,stag.col22,stag.col23,stag.col24,stag.col25,stag.col26,stag.col27,stag.col28,stag.col29,
stag.col30,stag.col31,stag.col32,stag.col33,stag.col34,stag.col35,'1' as flag,getdate() as RecordValidFromDate,'1800-01-01' as [RecordExpiredDate]
       )AS MERGE_OUT
       WHERE MERGE_OUT.ACTIONOUT='UPDATE';

when I ran above query For first time it is executed with in 3 minutes after that I Try to ran 2nd time its taking more than 2 hours and some tim its take 1hour 50 minuts.

coluld please tell me where I need to change query or what step I need to implement above query to give best performance and reduce exectuion time in sql server.

Move From Access To SQL Server: Best Calculation options?

I am in the process of moving my Access database tables and update process onto SQL Server. I have been able to transfer the historical data and create tasks via SSIS to import the data regularly the issue is replicating the saved queries that were one in Access

For example I had a query that calculated multiple KPIs using SQl such as

SUM(Case when ThisField = 5 then 1 else 0))

However short of creating a query in SQL Server and saving it somewhere I cant seem to find a better answer.

I had considered using SSAS, that way all of these functions could be predefined however as the majority of this is simply on 1 table that proved difficult

Other users will need to access these queries, before they were stored within Access and could modify as they need but I am not so sure with SQL Server, what option would be best?

Thanks

SQL Server transfer 6,000,000 records from one server to another server - fast

What is the fastest way to transfer 6,000,000 records between two servers in different locations, using SQL Server 2005?

Sql Query to find count of 0 and 1

I have a column name Allowed I want MSSQL query to count of both values 0 and 1

Allowed

0
0
0
1
1
1

How to get the total sales of immediate subordinate based on sales of all subordinate in charge in SQL Server?

the structure is as follows:

ID_EMPLOYEE   ID_EMPLOYEE_BOSS     LEVEL
        -------------------------------------------
            100           NULL                1
            201           100                 2
            202           100                 2
            301           201                 3
            302           201                 3
            303           202                 3
            304           202                 3
            401           302                 4
            402           302                 4
            403           302                 4
            N             N-1                 N

And structure for sales

ID_EMPLOYEE           SALE        DATE
    401               1100.00     2015-06-07
    402               1500.00     2015-06-05
    403               1400.00     2015-06-25
    303               5000.00     2015-05-25
    304               8250.00     2015-05-25

I tried this

WITH Sales_CTE (ID_EMPLOYEE, SALE, MONTHS)
AS
(
    SELECT ID_EMPLOYEE, sum(SALE), DATEPART(mm,DATE)
    FROM SALES
    GROUP BY ID_EMPLOYEE, DATEPART(mm,DATE)
)
SELECT JE.ID_EMPLOYEE, OS.SALE, OS.MONTHS,
    JE.ID_EMPLOYEE_BOSS, OM.SALE, OM.MONTHS
FROM EMPLOYEES AS JE
    JOIN Sales_CTE AS OS
    ON JE.ID_EMPLOYEE= OS.ID_EMPLOYEE
    LEFT OUTER JOIN Sales_CTE AS OM
    ON JE.ID_EMPLOYEE_BOSS= OM.ID_EMPLOYEE
    WHERE ID_EMPLOYEE_BOSS= 302
ORDER BY JE.ID_EMPLOYEE_BOSS;

Currently only I can see sales employee level n-1 immediate, but I want to show as follows. For example employee 100 can see

ID_EMPLOYEE  SALES    MONTH
201           4000    6
202          13250    5

This n levels

Convert Date/Time to Date

I have a query which runs and I would like to return records where the date is greater than or equal to a particular date. The column I'm querying against is datetime format.

SELECT COUNT(RSO_ParentID), AssignedDateTime
FROM Task
WHERE OwnerTeam='2nd Line Support' AND AssignedDateTime>='2015-09-01 00:00:00.000'
GROUP BY AssignedDateTime

Is there a way of having filtering on the date part of AssignedDateTime, and searching relative to the current date (i.e. search for the previous 7 days)?

Thanks, Matt

SQL Server 2005 - updating many to one issue

I am trying to update a table from another staging table. The data is store employees. the problem is the unique identifier for the destination table (there is no PK) is a RSN# that does not match an RSN number in any other table. In the destination the combination of store id# and employee id# is unique but not in the staging table where there are people listed more than once with different titles, phone numbers, etc. for that store.

The problem I have is the file that is being used for the staging table is going to have many of the same records every time the import runs. So if there are 2 records for the employee each time it runs it will just toggle back and forth between the two records. I am not sure how to proceed.

Thanks in advance.

What script would concatenate strings and filter by column value?

I need a script that will concatenate the Reason values by PLAN_ID in a colon delimited string from the following table and place them in a further table, staying true to the Order value. How can this be achieved?

PLAN_ID Order   Reason
6281    1       Declined
6281    4       Unfit
6281    8       Other
6281    9       Monitoring
6286    1       Declined
6286    5       Unknown Site
6286    10      Not Known

PLAN_ID Reason
6281    Declined;Unfit;Other;Monitoring
6286    Declined;Unknown Site;Not Known

How to connect to a MSSQL database to render a multi line google chart?

I am a beginner trying to create a dashboard using JS. I'm trying to present some live data graphically (using Google Charts), and need to fetch the data points for my chart from an MSSQL server (which I need to connect to using remote desktop), and I'm not sure how! Help!

Another issue I've been facing is I cannotcanimate/customize my chart - I've tried using the following :

var options = {
       chart: {
                        animation: 
                        {
                        duration: 1500,
                        easing: 'out',
                        startup: true
                        },
                        title: 'Pick Monitor',
                        subtitle: 'Updated every 3 Hours',
                        colors: ['#0099CC', '#339966', '#99CC00'],
                        is3D : true
                        },

                width: 1000,
                height: 500

                };

The color hasn't changed from the default (red,blue, yellow) to the one's I've specified, and also the animation doesn't happen when the page loads.

optimization for create index on large table

I have one doubt in sql server . Table : emp

 col1  |   Col2   | COl3  | col4 | col5 | col6| col7| col8|col9| col10

in the above table have data approach 3 crore records.all columns datatype is varchar only. for retrieve select query optimization purpose I create cluster index on emp table. create cluster index cl_emp on emp( col1 desc,col2 desc ,col3 desc,col8 desc,col10 desc)

In this process to run above query its taking more than 1 hour. here create indexs and select query both take more time I want reduce time and even when I try

select col1,col2,col3,col8,col10 from emp

that time also its taking more than 50 minutes. please tell what steps I need to processed to give good performance for large tables in sql server .

How to restore sql 2000 database backup in sql 2005?

I am trying to restore backup of a database which was created in SQL 2000 in SQL 2005 and getting this error

Index was outside the bounds of the array.(Microsoft.SqlServer.Express.Smo)

How can I restore ?

How to verify that whether a column is an encryption key/value or plain text in sql server 2008

I stuck up in a situation which is demonstrated below: How to verify that whether a column is an encryption key/value or plain text in sql server 2008.

Thanks

Can't find the apache application

I have an app that is connected via an apache server and a windows 2005 server. It runs well on a windows xp pc, but I want to transfer the database from that pc to another where I have wamp installed. The problem is I can't find the apache app so I can do the export. All I have is a www folder, from where the app runs. Any ideas?(I wasn't the genius that configured the app in the first place:) )

Deadlock on timer controlled db operation

I have 3 identical programs (made using topshelf) that run as 3 separate services for 3 different departments. When they run, they keep accessing database using timers - mainly to keep sending Alive signal or to purge the old logs. Recently I've been asked to add additional timer only for one of the departments

 Random r = new Random();   //to avoid possible deadlock we save the timestamp on random time
        int hourly =  1000 * 60 * 60;     //this is an hour



       Timer TimerItem = new Timer(TimerCallBack, null, 0, r.Next(20000, 25000));  //alive timer
       Timer purgeLogs = new Timer(TimerPurgeLogs, null,0,hourly);    //purge logs timer
       if (selDep == "planners")
       {
           Timer UpdatePlannes = new Timer(TimerUpdatePlanners, null, 0, r.Next(50000,60000));    //planners update
       }



    private static void TimerUpdatePlanners (Object o)
    {
        string conn = System.Configuration.ConfigurationManager.ConnectionStrings["Microsoft.Rtc.Collaboration.Sample.Properties.Settings.lynccallrosterConnectionString"].ToString();
        if (selDep == "planners") //Only for planners - auto turn the forwading on for everyone except the current person
        {
            string updateCommand = "update employees set on_call = 1 where Department ='Planners' and gkey <> (Select gkey from currently_on_call where Department ='Planners')";
            using (SqlConnection updatePlanners = new SqlConnection(conn))
            {
                SqlCommand executeUpdate = new SqlCommand(updateCommand, updatePlanners);
                try
                {
                    updatePlanners.Open();
                    executeUpdate.ExecuteNonQuery();
                    updatePlanners.Close();
                }
                catch { }
            }
        }

Unfortunately after I do this, it causes a hell of a problems with other program that is trying to read the same database, because it somehow deadlocks the database (SQL 2005). I can t see the reason for deadlock here: timer is executed only every 50-60s and the command execution last few millisecond, yet it seems that the connection is kept open all the time - in the activity monitor I can see dozen or so Update commands that keep clogging the data traffic. What is also interesting, this issue only happens when the program is run as a service - topsehlf allows to run service as a console app and when its run in this mode, I can't see any deadlocks or unnecessary Update commands wiating in the queue.

What have I done wrong?

Translating this formula to SQL

I have this formula in one of the report's fields:

Sum ({@MarketValue}, {IA.AS_AT_DATE}, "daily")

But I'm not sure what is the intent here or what "daily" does in this case.

Any ideas ?

Check based on columns condition in sql server

How to solve this issue in SQL Server

Table: emp 

Pid  | Address           |  City | datetime                   |  Edate       | level
1    | Homeless          |  Chen | 2014-11-13  09:32:14.000   |2013-02-10    |3
1    | 3913 W. Strong    |  Chen | 2011-03-044 19:04:10.000   |2014-02-04    |7
1    | 1100 W MALLON     |  Chen | 2014-11-13  09:32:14.000   |2013-02-10    |5
2    | 610 W GARLAND #3  |  Hyd  | 2013-11-13  09:32:14.000   |2014-04-02    |4
3    | banvanu           |  chen | 2015-03-044 06:04:10.000   |2015-05-06    |6
3    | naneku            |  chen | 2015-03-044 06:04:10.000   |2015-06-09    |4

based on above table I want output like below

Pid  | Address           |  City | datetime                   |  Edate       | level
1    | 1100 W MALLON     |  Chen | 2014-11-13  09:32:14.000   |2013-02-10    |5
2    | 610 W GARLAND #3  |  Hyd  | 2013-11-13  09:32:14.000   |2014-04-02    |4
3    | naneku            |  chen | 2015-03-044 06:04:10.000   |2015-06-09    |4

we need to get address,city from same table based on below conditions

We have few condition to get output : first level check the max(datetime) based on pid if max(datetime) values same for same pid then same pid need to check max(edate) if we get again same value then we need to check max(level) particilar patient the retrieve address,city for that pid

I tried like below

select * from (select *,row_number()over(partition by id ,order by datetime,edate,level)as rno
               from emp)
where rno=1

but above query not given expect result please tell me how to write query to achive this task in sql server