Hide One column while displaying in Excel sheet

I have a query whose result is fetched as an attachment in the Excel sheet. Now what I want is,

I want to hide RAName column while displaying in the excel sheet.

So what I want is, Can I hide that in sql query.

Below is my query.

SELECT                  
       row_number() over (order by (UserName)) as SR_No,        
       UserName,
       Doc_No Inward_No,  
       Document_Type Doc_Type,
       Party_Name,           
       Ref_No,      
       doc_date Inward_Date,
       Ref_date Dept_Received_Doc_Date,       
       Last_Action_date Last_status_update_date,
       Remarks,

       RAName                   
       FROM #MainTempTable                  
       order by UserName,RAName

NOTE: Please ignore the temp table part.

Sql group by yesterday's 9am to today's 9pm

50  2016-07-29 04:40:44.000
70  2016-07-29 04:40:33.000
30  2016-07-29 03:50:56.000
15  2016-07-28 00:03:16.000
74  2016-07-28 23:38:18.000
15  2016-07-28 23:30:11.000
7   2016-07-28 23:22:37.000
10  2016-07-28 21:02:19.000

How can i achieve to group yesterday and today?

I must see only one row,

271  2016-07-28

Thanks for any idea.

How to toggle between two values of concurrent table rows in sql

I have a table name [NavBar] with these columns:

Id [int]
Name [nvarchar]
DisplayOrder [int]

Sample data:

Id   Name   DisplayOrder
---------------------------
 100   Home        1
 101   Products    2
 103   Contact     3
 104   Career      4

How do I perform update operation to toggle between two rows DisplayOrder of two concurrent rows (order by Displayorder) ?

For example Contact = 4, Career = 3

Expected output:

100  Home      1
101  Products  2
102  Contact   4
103  Career    3

Input parameter: Id of one row only

How to Toggle between two values from table rows in Sql

I have a table name [NavBar] with these columns:

Id [int]
Name [nvarchar]
DisplayOrder [int]

Sample data:

Id   Name   DisplayOrder
---------------------------
 1   Home        1
 2   Products    2
 3   Contact     3
 4   Career      4

I want to write a query to update DisplayOrder between two rows.

For example Contact = 4, Career = 3

Expected output:

1  Home      1
2  Products  2
3  Contact   4
4  Career    3

How do I perform update operation to toggle between two rows display order ?

Input parameter: Id of one row only

Group parents with same children

I have a table like this

ID  Type                    ParentID
1   ChildTypeA              1
2   ChildTypeB              1
3   ChildTypeC              1
4   ChildTypeD              1

5   ChildTypeA              2 
6   ChildTypeB              2
7   ChildTypeC              2

8   ChildTypeA              3 
9   ChildTypeB              3
10  ChildTypeC              3
11  ChildTypeD              3

12  ChildTypeA              4
13  ChildTypeB              4
14  ChildTypeC              4

and I want to group parents that have same children - meaning same number of children of same type.

Example of my output would be:

Config Group    ParentID
ConfigA         1   
ConfigA         3   
ConfigB         2
ConfigB         4

I have no idea where to even begin.

How to sum dynamic columns in sql?

Assume I have a table with 3 columns. Is there possible sum of each column without specifying name of the column? And is there possible create a table with dynamic name of the column, and then sum of each column?

How to get count of availible guid from a comma separated string of guid

Declare @i int;
declare @stringOfGuids nvarchar(max)='''70173C2D-0B8E-4043-BD14-665D5DCCF112'',''B0B7445C-DF50-4D49-BD4E-B74958FB0618''
,''70173C2D-0B8E-4043-BD14-665D5DCCF112''';

as i have above string and here i need to count number of availible guid which is three. how can i get it in a Integer varible

Something like this ??

select @i= count(@stringOfGuids)

but it gives me 1 , need three as available guids are 3

Serial NO starting from 1

I have a query in which, I want one columns as SR_No which should start from 1

Below is my query

SELECT         
top 2      
 userName,   
 Party_name, 
 Ref_No,
 Ref_date,   
 doc_date,
 Last_Action_date,
 RAName,          
 COUNT(Doc_No) AS CountofDocNo,                  
 Document_Type,           
 RA1_Email                  
FROM #MainTempTable         
 GROUP BY RAName,          
 userName,           
 Document_Type,          
 RA1_Email ,Party_name,Ref_No,
 Ref_date, doc_date,Last_Action_date

order by RAName

How to start it's serial no from 1.

I tried with Row_NUM() but it was not starting from 1.

SQL script to rename all the table names with new name if it containes required string in mySQL and MsSQL

I have one database with 100 tables in it. out of 100 some tables have name like House, House1, House2 , HouseXYZ and so on. Now I want to write a script in MySQL and MsSQL to replace the House with Home. So my database should be having the table name Home, Home1, Home2, HomeXYZ and so on.

Unable to apply a constraint when data in a column for multiple rows need to be kept unique for a particular foreign key's reference

Let me explain this scenario, with my table structure-

RoomId  RoomMemberId
R1       RM1
R1       RM2
R1       RM3    
R2       Rm1
R2       RM2
R3       RM1
R3       RM4
R3       RM3

Here in the above table RM1,RM2 and RM3 are the member of R1 room , now I have to apply a constraint that there should not be any other room where only these three are members i.e. there should not be any other room with same room members.

How can i do it at database end, by any unique constraint or any other way to do so ?? Pls help...

Case into union all select 0

I want to create a two cases first one i want union select 0 and second if its not 0 UNION ALL SELECT cast(0 as varchar(50))

I need something like that

UNION ALL SELECT case when union select >0  then cast(0 as varchar(50)) else union select 0 end  from inventory

Why my code doesn't work?(using multi expressions+cast+case)

My column's   example 
          (panelname) as varchar(50)
              o01
              o02
              o16

I want to Select first missing panelname above 0 also with using case and cast(in my example desired select will be 'o03')

WITH cte AS(SELECT panelname FROM inventorypanelcaptions UNION ALL SELECT 0) SELECT CASE WHEN  cast(min(right(panelname, 2) + 1) as varchar(50)) < 10 THEN 'o0' ELSE 'o' END + cast(min(right(panelname, 2) + 1) as varchar(50)) FROM cte WHERE NOT EXISTS ( SELECT panelname FROM inventorypanelcaptions WHERE cast(right(inventorypanelcaptions.panelname, 2) as varchar(50))= cast(right(cte.panelname, 2) as varchar(50))+1) ", con);

This code works but only in case when my table is null or table contains only numbers.

example my columns in panelname -> Result will be o03
             1     or   101    
             2          102
             16         116

But when it contain letter

 example my columns in panelname 
             o01         
             o02
             o016

It doesnt work. I get an error : Conversion failed when converting the varchar value 'o01' to data type int. Should i put somewhere else cast to varchar?

One PC cannot connect SQL Server 2005 Express but other do

I've an extrange issue with that. I'm working on a local network with 3 PCS running windows 7 pro x64. The fact is they need to be interconnected to use a program that is running over SQL Server Express 2005.

Ok, so i've the program in a shared folder and SQL express 2005 installed in one of them, then its supposed that the others only have to keep installed sql native client, and then open shared folder, double click on .exe and work.

it's running well in one of both "clients" and in a "server" (and we can work on both) but the third, is no cappable to connect sql database.

i tried to uninstall and install sql services, unable firewall and defender, enabling port on firewall etc...

When i try to connect to database from PC3 to PC1/SQLEXPRESS i get an error 26 (i tried almost everything i found on msdn) But when i connect from PC2 to PC1/SQLEXPRESS everything is ok. (same network, supposedly same installed referred to sql)

To make it more extrange, other day it was running, then we add PC2 to network and only PC1 and PC2 are working.

Any suggestions?

Thanks

is there any key word like "Only IN" keyword in sql server

something like - Select * from RoomMemberTable where RoomMemberId "ONLY IN" ('RM1','RM3') My table structure -

RoomId RoomMemberId R1 RM1 R1 RM2 R1 RM3 R2 Rm1 R2 RM2 R3 RM1 R3 RM3

I need to get rows which contains only these room members. Here i don't need such entries where any other member except RM1 and RM3 presents.

Cursor not runing thru whole table

Okay, i got this cursor and it needs to update column test with the result from @sql query. but it doesnt update each column with their own result from query, like he doesnt execute that query for each column, if u undestand me.

    declare @promenjiva nvarchar(max)
    declare @docidgodina nvarchar(max)
    declare @sql nvarchar(max)
    declare @var nvarchar(max)

    declare c1 cursor for

    select documentid,a4 from damdocumenttype1

    begin
    open c1;

    fetch next from c1 into @var,@promenjiva
    while @@fetch_status = 0
    begin
    set @promenjiva=REPLACE(@promenjiva,'C','')
    SET @SQL = --some sql query that uses @promenjiva and give the result like '3/2014, 4/2014, 5/2014, 8/2014, 16/2014, 308/2015
3/2014, 4/2014, 5/2014, 8/2014, 16/2014, 308/2015'--
    update someTable set test=@sql

    fetch next from c1 into @var,@promenjiva

    end

    end
    close c1
    deallocate c1

I can join someTable with damdocumenttype1 on documentid, just so you know

Combining conditions in an IF condition

Can't we have this in a single SQL without using IF

IF (@PPRODUCT_TYPE_ID != '0')   
    SELECT * FROM PRODUCT 
    WHERE 
        PRODNAME = @pName 
    AND 
        PRODUCT_TYPE_ID IN (SELECT DISTINCT ID FROM PPRODUCT_TYPE)
ELSE
    SELECT * FROM PRODUCT 
    WHERE PRODNAME = @pName 

Couple columns from result into one separated with comma

I got this query

declare var1 nvarchar(100)
    set @var1='84,85,86'
    SET @sql = N'select a1 from DamDocumentType1 where documentid in (' + @var1 + ')' ;
    EXEC (@SQL);

The result i get comes in one column and 4 rows. For example:

3/2014

4/2014

5/2014

8/2014

How can i put this couple of rows in one row and one column and separate them with comma.For example:

3/2014,4/2014,5/2014,8/2014

I use that query for something so now i need it fixed to do this thing i just explained, i cant figure it out. You can say that I'm new in this :)

Contradictory condition checks on Where with a Case Statement

I want to check two Contradictory conditions on a WHERE clause for a same field using CASE;

If I extract the thing I want, shows below

WHERE FULFILMENT_STATUS_CODE != 'CNL'
WHERE FULFILMENT_STATUS_CODE = 'FUL'

They way I tried (and want) as follows, But I wonder how to have != in her

WHERE
    FULFILMENT_STATUS_CODE =
    CASE @pFILTER_TYPE
        WHEN 1 THEN 'CNL' 
        WHEN 2 THEN 'FUL' 
    END

Converting couple of characters from nvarchar to int

declare @var1 nvarchar(100)
set @var1='C85,c84,c88'
set @var1=REPLACE(@var1,'C','')
select column24 from someTable where column23 in (@var1)

Hello guys and gurls, I am new around here and I need help with this. This query gives me nothing I know its beacouse var1 is nvarchar but i dont know how to make it work. Dont ask me why I used that REPLACE, I am testing something, in this particular query it looks stupid. So close your eyes on that one :) And yes, there are rows 84,85,88 in someTable at column23. I am doing this only thru mssql management studio.

How to Create index for cte

I have a query:

With Cte as (some code)

Select * 
from Cte
order by id 

Can I create a index for this cte?

I'm using ms SQL Server 2005

Thanks.

Check why record is returned twice in SQL

I have an inward_doc_tracking_hdr table with a total of 79 records.

But when I run the same thing in a stored procedure, I get a total of 80 records.

I checked the count and saw that one record count is 2.

I want to know why the record is being returned twice.

Here is the query:

Select  
    UserName, Document_Type, 
    count(Doc_No) docno,
    Doc_No, No_Of_Days_Doc_Pending, UserEmail,
    RA1_Email  -- User table  
from 
    MainTempTable
group by 
    UserName, Document_Type, Doc_No, No_Of_Days_Doc_Pending, 
    UserEmail, RA1_Email, Doc_No

SQL Column + DataType concatenation in select statement

So I think I know the answer to this but I would like to check with some other professionals. We have a statement that is part of an insert statement that looks like this:

INSERT INTO <Table_Name> 
SELECT   CHAR(34) + RTRIM(Col_1) + CHAR(34)
        ,CHAR(34) + RTRIM(Col_2) + CHAR(34)
        ,CHAR(34) + RTRIM(Col_3) + CHAR(34)
        ,CHAR(34) + RTRIM(Col_4) + CHAR(34)

To me this is adding character padding to the selected columns. Am I correct in this way of thinking or is there something more that I missed? Thank you for your consideration.

Regards, Z

sql query to insert values to auto increament column

Hi I want to populate value for Auto increament column when insert value for a table from another table. below is query im using and it throws error

create table test12
(
Id int,
name varchar(255),
dept varchar(255)
)

insert into test12 values(1,'f','cs'),(2,'b','cse'),(3,'c','cs'),(4,'d','cse'),(5,'e','cs'),(6,'f',null)

select * from test12                                                            

create table test34
(
seq int identity(1,1) not null,
name varchar(255) not null,
dept varchar(255) default('cs')
)
insert into test34(seq,name,dept) values 
(1,(select name from test12),
(select case when dept='cse' then 'Y' else 'N' end as dept from test12))          

Please let me what is the mistake

Are there any side-effects of converting a VARBINARY to a VARCHAR?

I was writing a FUNCTION to wrap around a call to HASHBYTES, and had two parameters, a VARCHAR(MAX) one and a VARBINARY(MAX) one. The intention being that the user will call this to hash a VARCHAR value or a VARBINARY value.

However, in testing I noticed that I could pass a VARBINARY value to the VARCHAR parameter and have it get quietly and implicity converted to VARCHAR.

CREATE FUNCTION fnHashBigField( @varcharInput   AS VARCHAR(MAX)     = NULL,
                                @varbinaryInput AS VARBINARY(MAX)   = NULL) RETURNS VARCHAR(900)
BEGIN

    DECLARE @initialValue AS VARCHAR(MAX)
    SET @initialValue = ''  

    IF @varbinaryInput IS NOT NULL
    BEGIN
        SET @initialValue = '0x' + cast('' AS XML).value('xs:hexBinary(sql:variable("@varbinaryInput") )', 'VARCHAR(MAX)');
    END
    ELSE
    BEGIN
        SET @initialValue = @varcharInput
    END

    --Code to split up the VARCHAR into chunks small enough for HASHBYTES to work with

    RETURN ''

END

From research, I've noticed that the conversion will implicitly work from VARBINARY to VARCHAR, but not the other way around. I've also noted on SQL Server 2005 that if you try to CONVERT a VARBINARY to VARCHAR, it will give you what appears to be a text version of the raw data, rather than a hex string like in later versions of SQL Server. Thus I suspect that I would still need to keep the XQuery conversion code.

My question is this: Are there any side-effects to this implicit conversion from VARBINARY to VARCHAR? If there's no issues I can simply have one VARCHAR(MAX) parameter.

Insert SP data into two temporary tables

I have a SP whose data looks like below

Now what I want is, I want to insert its data into two different temp table

First temp table will consist

`Doc_type`, `Doc_No`, and `No_of_days`

Second temp table will consist

Username, DocType, and No_of_days.

I tried like below

CREATE TABLE #table1 
            (
                Doc_type varchar(55),
                Doc_No varchar(55),
                No_of_days varchar(55),
            )
        INSERT INTO #table1 

But it is giving error as

Incorrect syntax near the keyword 'END'.

Here is my FULL SP

Alter procedure GET_INWARD_REMINDER_REPORT 
                AS 
        BEGIN 
                    Select
                    U.first_name + ' ' + U.last_name UserName, 
                    TH.User_ID, 
                    TY.Type_desc Document_Type, 
                    RA.mkey Reporting_To,
                    U.Email AS UserEmail, 
                    RAU.Email AS RA1_Email, 
                    RAU.first_name + ' ' + RAU.last_name RAName, 
                    TH.Doc_No, 
                    DATEDIFF(DAY,TH.LastAction_DateTime,GETDATE()) - DATEDIFF(WK,TH.LastAction_DateTime, GETDATE()) 
                    AS No_Of_Days_Doc_Pending 
                    from inward_doc_tracking_hdr TH 
                    inner join  
                    user_mst U ON TH.User_Id = U.mkey 
                    inner join 
                    emp_mst M ON M.mkey = U.employee_mkey 
                    inner join 
                    type_mst_a TY ON TY.master_mkey = TH.doc_type 
                    inner join 
                    emp_mst RA ON RA.mkey = M.Reporting_To 
                    inner join  
                    user_mst RAU ON RAU.employee_mkey = RA.mkey 
                        where 
                    TH.Status_flag NOT IN (5,14)  --- 5 for close, 14 for return
                    and TH.To_user IS NOT NULL 

                        CREATE TABLE #table1 
                        (   
                            Doc_type varchar(55),
                            Doc_No varchar(55),
                            No_of_days varchar(55),
                        )
                    INSERT INTO #table1 

END

Convert SmallInt to time

I am trying to convert a smallnt to a time format.

I am using SQL Server 2005 which doesn't support the time datatype.

I am storing the time as 1400 which is a smallint. When retrieving i want it to be converted to a time format. Such as 14:00

Any Ideas or guidance on the matter. If there is an easier way to do it or if the way i am trying is possible?

Thanks

Why local\instancename works and ip\instancename doesn't?

I'm using SQL Server 2005. When I use (local)\instance name in my connection-string it inserts; But when I use ip\instancename(ex 192.168.1.100\instancename) it doesn't. How can I enable it?

Split a column into 3 columns?

I have table 1 like this:

----------------
|  id  |  col  |
----------------
|   1  |   7   |
|   2  |   6   |
|   3  |   1   |
|   4  |   8   |
|   5  |   9   |
|   6  |   5   |
|   7  |   4   |
|   8  |   3   |
|   9  |   2   |
----------------

I want to create new table get data from column col of table 1 and get the result like this:

-------------------
|  A  |  B  |  C  |
-------------------
|  7  |  6  |  1  |
|  8  |  9  |  5  |
|  4  |  3  |  2  |
-------------------

Any suggestions? Thanks a lot.

Max of two Columns, within found set

I have a large activity pull where I have to get the last activity of selected ca.matter_key's in a table, determined by ca.activity_key (tables primary key). I would like to know the most efficient way of getting this as the end result will be in the ballpark of 20K.

    select distinct

    ca.matter_key,
    ca.activity_key,
    ca.actrow1,
    ca.actrow2,
    ca.actrow3,
    ca.actrow4

    FROM CaseAct ca

    where
    ca.matter_key in (
    4945046,
    5040953,
    5025296,
    5078360
    )

The result from above will give the below result:

Matter_key  activity_key actrow1 actrow2 actrow3 actrow4
4945046     12           Data    Data    Data    Data    
4945046     13           Data    Data    Data    Data    
4945046     14           Data    Data    Data    Data    
5040953     35           Data    Data    Data    Data    
5040953     36           Data    Data    Data    Data    
5025296     77           Data    Data    Data    Data    
5025296     78           Data    Data    Data    Data    
5025296     79           Data    Data    Data    Data    
5078360     NULL         Data    Data    Data    Data     
4887024     93           Data    Data    Data    Data    
4887024     94           Data    Data    Data    Data    
4887024     95           Data    Data    Data    Data    
5061591     NULL         Data    Data    Data    Data    

And I need:

Matter_key  activity_key actrow1 actrow2 actrow3 actrow4 
4945046     14           Data    Data    Data    Data    
5040953     36           Data    Data    Data    Data      
5025296     79           Data    Data    Data    Data    
5078360     NULL         Data    Data    Data    Data    
4887024     95           Data    Data    Data    Data    
5061591     NULL         Data    Data    Data    Data 

I've done this on previous sets using a combination of group by ca.matter_key, and Max(ca.activity_key) using criteria, however I need the pre-selected matter_keys, which is a large list. Also, is Where in ca.activity_key() the most efficient way of pulling this list?

Thanks for any help!

Remove one table and its alias from the stored procedure

I have a stored procedure in which I don't want to use table which is inward_doc_tracking_trl and its related aliases.

This is the code:

ALTER PROCEDURE [dbo].[GET_INWARD_REMINDER_REPORT]     
AS     
BEGIN     
    Select distinct     
        U.first_name + ' ' + U.last_name UserName,     
        TH.User_ID, TH.doc_no, TH.U_datetime,     
        CASE     
            WHEN TL.U_datetime < DATEADD(d, -5, GETDATE())     
                THEN M.Reporting_To     
           ELSE NULL     
        END Reporting_1 ,     
        --U.Email AS UserEmail,     
        --senior.Email AS RA1_Email,     
        10000 AS UserEmail,    
        100001 AS RA1_Email,                                
         DATEDIFF(DAY,TH.LastAction_DateTime,GETDATE()) - DATEDIFF(WK,TH.LastAction_DateTime,GETDATE())AS No_Of_Days_Opening     
    from 
        inward_doc_tracking_trl TL     
    inner join 
        inward_doc_tracking_hdr TH ON TH.mkey = TL.ref_mkey     
    inner join
        user_mst U ON TH.User_Id = U.mkey     
    inner join
        emp_mst M ON M.mkey = U.employee_mkey     
    outer apply
        (select 
             uss.Email     
         from 
             emp_mst MS     
         inner join
             user_mst uss ON uss.employee_mkey = MS.mkey     
         where
             MS.mkey = M.Reporting_To) senior     
    where 
        TH.Status_flag NOT IN (5,14)     
        and TH.To_user IS NOT NULL  
END

Select rows by applying whatever condition(if or case)

I have a set of records suppose below:

Id  Name  status  date

1    xx     1      2016-06-27 14:05:17.447
2    yy     2      2016-06-27 14:05:17.447
3    zz     1      2016-06-27 14:05:17.447
4    aa     2      2016-06-27 14:05:17.447
5    bb     2      2016-06-27 14:05:17.447

I want to select all the rows from above but for the rows who have status=1, i want to apply a condition that

select if status=1 and date<=getdate

How can I do that?

Insert to multiple column from single column of another table

I have already table 1 with 3 columns like this:

-------------------
|  A  |  B  |  C  |
-------------------

and table 2:

---------
|  col  |
---------
|   1   |
|   2   |
|   3   |
|   4   |
|   5   |
|   6   |
|   7   |
|   8   |
|   9   |
---------

I want to insert to table 1 from data of table 2 and get the result like this:

-------------------
|  A  |  B  |  C  |
-------------------
|  1  |  4  |  7  |
|  2  |  5  |  8  |
|  3  |  6  |  9  |
-------------------

Any suggestions? Thanks a lot.

Create table with column names from another tables column data

I have table with a single column like this:

---------
|  col  |
---------
|   A   |
|   B   |
|   C   |
---------

I want to create a new table with the following column names like this:

-------------------
|  A  |  B  |  C  |
-------------------

Any suggestions? Thanks a lot.

In Trigger getting error as error converting data type varchar to numeric

I have a trigger which was working earlier perfectly. But later on, as required I added some more lines into that which is below

declare @imkey numeric(10,0);declare @xmkey numeric(10,0); declare @xsrno numeric(10,0)
            select  @xmkey=max([Mkey])+1, @xsrno=max([Entry_Sr_No])+1 
            from erp190516.[dbo].[Inward_Doc_Tracking_Trl] where Ref_Mkey=@imkey;
            select @imkey=i.Inward_ref_key from inserted i 

            insert into erp190516.[dbo].[Inward_Doc_Tracking_Trl]
            SELECT  @xmkey,@xsrno,[N_UserMkey],'1' [N_Department], 'F' [CStatus_Flag]
            ,'Requester' [Remarks],'1' [CUser_ID],getdate() [U_Datetime],
            ,'N' [NStatus_Flag], 'N' [Delete_Flag]
            ,'1'[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],[Receipt_No],[Po_No],[Bill_No]
            ,[Disp_through],[Disp_Through_Name],[Site_Id]
        FROM erp190516.[dbo].[Inward_Doc_Tracking_Trl] where Ref_Mkey=@imkey

It eexcuted perfectly, but while inserting the data into the table I got error as

Error converting data type varchar to numeric

I am trying to backup a database and am getting MSG 3013

I have tried to backup in Microsoft SSMS with the GUI backup task, and it fails after a few seconds, so then I tried running this command:

BACKUP DATABASE databasename TO DISK = 'd:\databasename_full.Bak' WITH FORMAT, MEDIANAME = 'd_datbasenamebackup', NAME = 'Full Backup of databasename'; 

And get a very generic error of the following

Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally.

I am wondering if anyone has come across this error before. Everything I have read is saying there is a media fault, which I know isn't the case.

Excel 2013 VBA to query an SQL Server to lookup items based on cell values

I have an Excel file that has a list of SN's in column A (about 100k). I would like to run a query from within Excel (2013) to an SQL server (2005) to lookup each SN and return the part number and mfg date and place the results in column B and C. Is this possible?

Example worksheet:

   A        B        C
1  I0001
2  I0025
3  J0128
.
. 

Query would lookup SN (column A) from SQL Sever 2005 and fill in column B and C if SN is found in database... results would look like this:

   A        B        C
1  I0001    PN9214   10/20/2012
2  I0025    PN9214   10/31/2012
3  J0128    PN7771   06/06/2015
.
.

The SQL server: db.dbo.table
Fields: SN, PN, mfg_date....

MSSQL Job Alerts, find matches from user saved searches

I'm trying to create a script that checks user's 'saved searches' against live jobs.

The desired output would be a table/list of; JobID + JobSearchID.

To approach this I've created a temp table, #TempJobSavedSearches.

SELECT
    JS.JobSearchID, 
    JS.JobSortByOptionID, 
    Radius, 
    JobTypeID, 
    JobSearchKeywordID, 
    TownCityLookupID, 
    CountryID, 
    WorkingHoursID, 
    SectorID, 
    SalaryFrom, 
    SalaryTo 
INTO #TempJobSavedSearches
FROM UserJobSearches AS UJS
JOIN JobSearches AS JS ON UJS.JobSearchID = JS.JobSearchID
LEFT JOIN JobSearchJobTypes ON JS.JobSearchID = JobSearchJobTypes.JobSearchID
LEFT JOIN JobSearchStrings ON JS.JobSearchID = JobSearchStrings.JobSearchID
LEFT JOIN JobSearchTowns ON JS.JobSearchID = JobSearchTowns.JobSearchID
LEFT JOIN JobSearchWorkingHours ON JS.JobSearchID = JobSearchWorkingHours.JobSearchID
LEFT JOIN JobSearchCountries ON JS.JobSearchID = JobSearchCountries.JobSearchID
LEFT JOIN JobSearchSectors ON JS.JobSearchID = JobSearchSectors.JobSearchID
LEFT JOIN JobSearchSalaries ON JS.JobSearchID = JobSearchSalaries.JobSearchID
LEFT JOIN JobSortByOptions ON JS.JobSortByOptionID = JobSortByOptions.JobSortByOptionID
WHERE Alert = 1

This creates;

JobSearchID JobSortByOptionID Radius      JobTypeID   JobSearchKeywordID TownCityLookupID CountryID   WorkingHoursID SectorID    SalaryFrom  SalaryTo
----------- ----------------- ----------- ----------- ------------------ ---------------- ----------- -------------- ----------- ----------- -----------
901         8                 200         1           NULL               1                4           2              31          30001       40000
901         8                 200         1           NULL               1                4           2              34          30001       40000
904         8                 10          1           114                1                4           2              23          30001       40000
904         8                 10          1           114                1                4           2              24          30001       40000
904         8                 10          1           114                1                4           2              39          30001       40000

I then thought I could loop through each JobSearchID, and compare the respective data to my Jobs table.

Jobs:

JobID TownCityLookupID WorkingHoursID JobTypeID JobTitle JobDescription SalaryFrom SalaryTo 
----- ---------------- -------------- --------- -------- -------------- ---------- --------
1     1                2              5         foo      foo            10000      60000
2     1                2              1         foo      foo            30000      60000
3     11               3              3         bar      bar            70000      100000

Other link tables (some tables/columns excluded for brevity);

JobSectors

SectorID JobID
-------- -----
18       1
19       1
20       1
21       1
23       2
24       2
25       2
26       2
44       3


TownCities

TownCityLookupID CountryID
---------------- ---------
1                4
11               8

Heres my loop/attempt and comparing the results;

DECLARE @JobSearchID int = 0
DECLARE @CountryID int = 0
DECLARE @TownCityLookupID int = 0
DECLARE @WorkingHoursID int = 0
DECLARE @SalaryFrom int = 0
DECLARE @SalaryTo int = 0

WHILE(1 = 1)
BEGIN

  SELECT @JobSearchID = MIN(JobSearchID)
  FROM #TempJobSavedSearches WHERE JobSearchID > @JobSearchID

  IF @JobSearchID IS NULL BREAK

  -- store single vars
  SELECT @CountryID = (SELECT TOP(1) CountryID FROM #TempJobSavedSearches WHERE JobSearchID = @JobSearchID)
  SELECT @TownCityLookupID = (SELECT TOP(1) TownCityLookupID FROM #TempJobSavedSearches WHERE JobSearchID = @JobSearchID)
  SELECT @WorkingHoursID = (SELECT TOP(1) WorkingHoursID FROM #TempJobSavedSearches WHERE JobSearchID = @JobSearchID)
  SELECT @SalaryFrom = (SELECT TOP(1) SalaryFrom FROM #TempJobSavedSearches WHERE JobSearchID = @JobSearchID)
  SELECT @SalaryTo = (SELECT TOP(1) SalaryTo FROM #TempJobSavedSearches WHERE JobSearchID = @JobSearchID)


  -- should I be joining on jobs not on temp table?
  SELECT @JobSearchID, JobID FROM #TempJobSavedSearches
  INNER JOIN JobSectors ON #TempJobSavedSearches.SectorID = JobSectors.SectorID 
  INNER JOIN JobSortByOptions ON #TempJobSavedSearches.JobSortByOptionID = JobSortByOptions.JobSortByOptionID 
  INNER JOIN JobTypes ON #TempJobSavedSearches.JobTypeID = JobTypes.JobTypeID
  INNER JOIN JobSearchKeywords ON #TempJobSavedSearches.JobSearchKeywordID = JobSearchKeywords.JobSearchKeywordID

  WHERE 
    JobSearchID = @JobSearchID 
    AND CountryID = @CountryID 
    TownCityLookupID = @TownCityLookupID 
    AND WorkingHoursID = @WorkingHoursID
    AND SalaryFrom < @SalaryTo 
    AND SalaryTo > @SalaryFrom

END

However its returning both IDs, where I expected only JobID 2 to be shown

JobSearchID JobID
----------- -----
901         1
901         2
901         1
901         2
901         1
901         2
901         1
901         2
901         1
901         2
901         1
901         2
901         1
901         2
901         1
901         2

Could anyone explain what im doing wrong. Or provide a link to some reading that could help?

SQL Query to filter atleast the combination

Below my query,

SELECT 
  COUNT(*), PERSON_ID
FROM 
  SGT_PERSON_ACCOUNT SPAT(NOLOCK)
WHERE
  ACCOUNT_STATUS_VALUE IN ('ACTV','RFND')

GROUP BY PERSON_ID HAVING COUNT(PERSON_ID) > 1  

Here I just want to make sure it should return the records count greater than one whose account status should be combination of any one 'ACTV' and 'RFND' status.

Get One column in join from same table but different database

I have two database.

a. DB_1 b. DB_2

let say, I am currently using DB_1 for my below query

Select top 1 a.mkey,convert(varchar(255), a.ref_date,103) as REF_DATE,  cd.type_desc as DOC_TYPE,  a.doc_no as INWARD_NO, 
                       cr.type_desc as dept_received, e.emp_name as EMP_RECEIVED,    
                       convert(varchar(255), a.doc_date,103) as date,   
                       a.to_user, a.No_of_pages, Ref_No,    
                       e.emp_name as NAME,    
                       coalesce(e.Email_Id_Official, 'test@test.com') EMAILID, a.Party_Name                           
                from inward_doc_tracking_hdr a left join   
                     type_mst_a cd   
                     on a.doc_type = cd.master_mkey left join  
                   type_mst_a cr   
                     on a.dept_received = cr.master_mkey 
                     and cr.type_code='D1'  
                     left join  emp_mst e  
                     on a.emp_received = e.mkey   
                     where a.emp_received is not null and
                        a.mkey = 146

Now what I want is. I want to join the same table from another database(DB_2) how to do that ??

UPDATE

My another db column name is Inward_ref_key and its table name is inward_doc_tracking_hdr

How to combine results from 2 tables

I have a table 1 like this:

and a table 2:

I want to combine results from 2 tables above. How can I do it? Thanks for any help.

Create a table from another table in sql server 2005

I have already a table like this:

I want to create another table, get data from above table and the result like this:

How can I do it? Thanks for any help.

SQL Server 2005 Insert with WHILE LOOP

I want to get the result like this

Here is my code

declare @current int
declare @Temp Table(c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int, c10 int)
select @current = 1
while @current <= 10
    begin
        --I want to insert here
        select @current = @current + 1
    end
select * from @Temp

How can I insert? Thanks for any help.

SQL: Convert multiple columns to rows

I have a table in below format I need to convert it to this format

SO basically i am looking to convert multiple columns to rows. Can some one help me with this?

Thanks

UNION ALL - Prevent Combining of Colimns

I tried the following but i ended up with a single column Amount2 containing SUM's from Amount1 too.

SELECT 
    YEAR(createdDate) as Year, 
    MONTH(createdDate) AS Month, 
    Sum(GrandTotal) AS Amount1
FROM 
    Quotes
WHERE createdDate BETWEEN @DateFrom AND @DateTo
GROUP BY YEAR(createdDate), MONTH(createdDate)
--ORDER BY YEAR(createdDate), MONTH(createdDate)

UNION ALL

SELECT 
    YEAR(createdDate) as Year, 
    MONTH(createdDate) AS Month, 
    Sum(GrandTotal) AS Amount2
FROM 
    Quotes
WHERE createdDate BETWEEN @DateFrom AND @DateTo
AND orderDate IS NOT NULL
GROUP BY YEAR(createdDate), MONTH(createdDate)
ORDER BY YEAR(createdDate), MONTH(createdDate);

However i want to keep both Amount1 and Amount2 columns/amounts.