Recently Indexed Messages:-



Transact SQL :: Add A Grand Total To Pivot Output?

Oct 9, 2015

I have a SP that will generate a pivot output. I want to add a grand total at the end row to sum up the counts for each column.

the SP is as below :

/* COLUMN HEADERS*/
DECLARE
@columnHeaders NVARCHAR (MAX)
SELECT
@columnHeaders  =  COALESCE ( (@columnHeaders)  + ',[' + [Date] + ']', '[' + [Date] + ']')

[code]....

I am getting the below error:

Invalid column name 'Grand Total'.

Msg 205, Level 16, State 1, Line 16

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

View 10 Replies

SQL 2012 :: Increment Primary Key

Mar 9, 2014

I am developing asp.net application with sql server database

I have table FinancialYear

caseno(unique) finyear
1 2013-2014
2 2013-2014
3 2013-2014
1 2012-2013
2 2012-2013
3 2012-2013

For financial year-2013 to 2014. if i insert record ( select max(caseno) +1).

insert into finanancialYear values(max(caseno) +1,2013-2014)
if i delete record caseno:3 from the table,
select max(caseno)+1 will be 3

Which is already deleted and again that record with caseno 3 will be inserted

For every financial year in 'FinancialYear' table,.caseno will be reset to 1

And if I add new record in finyear 2012-1013

then max(caseno) will be last value of caseno of that financial year 2012-2013

Again insert becomes

insert into finanancialYear values(max(caseno) +1,2012-2013)

I have given the data.

View 5 Replies

Transact SQL :: Difference Between Inner Join And Left Outer Join In Multi-table Joins?

Oct 8, 2015

I was writing a query using both left outer join and inner join.  And the query was ....

SELECT
        S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
        Production.Suppliers AS S LEFT OUTER JOIN
        (Production.Products AS P
         INNER JOIN Production.Categories AS C

[code]....

However ,the result that i got was correct.But when i did  the same query using the left outer join in both the cases

i.e..

SELECT
        S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
        Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
LEFT OUTER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid)
ON
S.supplierid = P.supplierid
WHERE
S.country = N'Japan';

The result i got was same,i.e

supplier     country    productid    productname     unitprice    categorynameSupplier QOVFD     Japan     9     Product AOZBW    97.00     Meat/PoultrySupplier QOVFD    Japan   10     Product YHXGE     31.00     SeafoodSupplier QOVFD     Japan   74     Product BKAZJ    10.00     ProduceSupplier QWUSF     Japan    13     Product POXFU     6.00     SeafoodSupplier QWUSF     Japan     14     Product PWCJB     23.25     ProduceSupplier QWUSF    Japan     15    Product KSZOI     15.50    CondimentsSupplier XYZ     Japan     NULL     NULL     NULL     NULLSupplier XYZ     Japan     NULL     NULL     NULL     NULL

and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.

View 5 Replies

SQL 2012 :: Incorporate Several Queries In 1

Dec 18, 2014

I am wondering if it is possible to make 1 query that gives several, different resultsets.

I am working on a database for speedskating times, in particular I am working on seasonlists

The query I use for this i (in simplified form)

SELECT LastName, FirstName, Country, Time
FROM TbExample
WHERE Distance = '500' and Gender = 'Man' My question is about the "WHERE Distance = '500' and Gender = 'Man' " part.

I need the combinations 500-Man, 1000-Man, 1500-Man, 500-Women, 1000-Women, 1500-Women.

In the current setup I need to make 6 queries.

Is it possible to make some kind of (as they call it in Excel) Array (or 2: 1 for distances, 1 for gender) that runs through all possible combinations?

View 9 Replies

Query Performance Hard Coded Value Versus Table Driven

Nov 6, 2014

I want to be able to return the rows from a table that have been updated since a specific time. My query returns results in less than 1 minute if I hard code the reference timestamp, but it keeps spinning if I load the reference timestamp in a table. See examples below (the "Reference" table has only one row with a value 2014-09-30 00:00:00.000)

select * from A where ReceiptTS > '2014-09-30 00:00:00.000'

select * from A where ReceiptTS > (select ReferenceTS from Reference)

View 5 Replies

SQL 2012 :: SSIS FTP - Invalid Local Path / Trying To Send Files

Oct 27, 2015

I have created a FTP Task which is returning the value, "Error: 0x0 at FTP Task, SFTP Task: Invalid local path."

In my DB Table, the table contains a field for the location of files to be Ftp'd which is: C:/temp/ftp/OB/*

I believe my settings are correct and am curious to know if the above string should work or if I need a different syntax for the local variable.

View 0 Replies

SQL Server Admin 2014 :: How To Enforce Changes In Dependent Procedures

Feb 27, 2015

I have two procedures Sp1 and Sp2. The Sp1 is calling from Sp2. If there come new parameters or removed from the first procedure Sp1 then it will effect the Sp2 and its execution will break if required changes not done. So I want that when any changes making in SP1 then i need alert about the Sp2 changes.

View 3 Replies

Reporting Services :: IE 11 And SSRS Print Button

Mar 19, 2014

1. We have been using Sql Server 2008 R2 and its reporting services engine.

In the application we used Report Viewer (version 9).

When the report is taken for the first time, couple of messages pops up.

a. Unable to Load print control
b. Install Sql Server Reporting Services 2008 R2.

These messages are fine, but we noticed that the second message is not consistent. When the application is deployed in staging server which is as same as the production server except for the power (CPU, RAM), the second message is "Install Sql Server 2008". Why is this happening?

2. To support IE 11, we upgraded the Report viewer to version 11. Now the messages are different.

a. Printing is not available. Verify that ActiveX is enabled or try using IE from the desktop
b. Install Sql Server Reporting Services 2008 R2. (Here again when deployed in staging server it says "Install Sql Server 2008").

View 6 Replies

Reporting Services :: Expression To Take A Result And Change Name

Jun 3, 2015

Is there a way of taking a result from a query and pulling out a certain account number and displaying those account numbers - as fred?  Eg.  I have a drop down of account number (s) however they pull from a table that only displays them as account numbers.  I would like to give more a description to certain account numbers that are pulled.  Eg.  account number 234 will be fred and account number 555 will be sam etc.  Trying to add it to the query on the dataset is complicated, so I was wondering if I created an additional blank field with an expression to be able to do this? 

View 7 Replies

Can Get Server To Release A Lock Without Using Kill Command

Aug 26, 2015

Anyway I want to know if there is a way to get SQL Server to release a lock without using the kill command. My reason for asking is because this morning I was investigating some deadlocks and thought it would be nice if SQL Server upon identifying a deadlock instead of killing the victim SPID would simply force the victim SPID to release the lock causing the deadlock so that the other SPID could continue and the victim would simply have to wait longer.That lead me to wonder if there was a T-SQL command or DBCC to force SQL Server to release a lock that I specify.

So, any way to get SQL Server to release a lock without using the kill command?

View 8 Replies

WHERE Clause With Multiple ANDs

Apr 2, 2015

I have a table (tblAttributes) that looks like this:

ID|ReferenceNumber|AttrID|AttrVal
1|1|90|7
1|1|91|8
1|1|92|9
2|1|90|10
2|1|91|11
2|1|92|12

I need to return all the distinct IDs where the combination of AttrID and AttrVal matches multiple criteria for that specific ID.

I have the following:

SELECT DISTINCT(ID)
FROM
((SELECT a.ID
FROM tblAttributes AS a
WHERE a.AttrID = 90 AND a.AttrVal = 7) AS x
INNER JOIN
(SELECT a.ID
FROM tblAttributes AS a
WHERE a.AttrID = 91 AND a.AttrVal = 8) AS y
ON x.ID = y.ID)

View 5 Replies

SQL Server Admin 2014 :: Monitor AG Listener Remotely?

Jul 28, 2015

I need to check connectivity to AG listener remotely and log that to a table/file. What is the best way to do that? Powershell?

View 4 Replies

Transact SQL :: How To Find If Email Is Of Correct Format

Aug 13, 2015

I am getting email from the end client and i need to validate in sql query.

View 3 Replies

How To Describe Table Schema

Mar 15, 2014

I am more familiar with writing SQL for Oracle than MS SQL Server. Within Oracle there is a simple command, 'Describe', which actually shows the data types, and whether or not an attribute accepts NULLS.

MS SQL Server does appear to support such a command, however I am looking for a way to describe the attributes of tables nonetheless.

View 6 Replies

Link 2 Store Procedures In 1 Crystal Report

May 20, 2015

In a report called ICD_PrivateHospital, I have designed to show output to two table from two store procedures. 1st store proc: usp_RPT_Private and 2nd store proc: usp_RPT_Private2.

I have created both the store procs and it is executing successfully. I also have designed the Crystal Report in Visual Studio 2008.

I have added both the store procs in the crystal report with no error. I only wants to show top 20 records. It is running successfully when I add the fields and parameters from the first store procedure. But when I add the 2nd store procedures fields into the report, duplication occurs for both the results in store proc 1 and store proc 2. How to solve this issue?

View 5 Replies



Copyrights 2005-15 www.BigResource.com, All rights reserved