Transact SQL :: Create A Report Which Would Give Latest Transaction On Database

Jul 17, 2015

I'm trying to create a report which would give the latest transaction on a database, which all sit on different servers. I wanted to know if there is an a simple way tracking the latest transactions instead of getting the information from the database tables.

View 3 Replies


ADVERTISEMENT

Transact SQL :: Create A Pivot Query That Will Give Result That Is Horizontal

Jul 8, 2015

I have some data which is vertical...I want to create a pivot query in SQL that will give me a result that is horizontal like this. I cannot find a way of doing it without lots of IF or CASE statements?

View 10 Replies View Related

Is There A Way To Give Someone The Right To Create Only Views And No Other Objects In A Database

Feb 25, 2008

Is there a way to give someone the right to create only views and no other objects in a database? Currently I have given the individual ddladmin database rose, but would rather be more restrictive?

View 1 Replies View Related

SQL Security :: Create Database User And Give Grants To Few System Tables

Aug 12, 2015

Need to create a user in sql server provide grants to few system tables to the above user.

View 10 Replies View Related

Transact SQL :: Transaction On Alter Database Statement

Apr 20, 2015

Im working on Partition purge process, where I need to specify following statement:

SET @cmd = 'ALTER PARTITION FUNCTION ' + @function_name + '() MERGE RANGE (@range)'
EXEC (@cmd);
SET @cmd1 = 'ALTER DATABASE '+ db_name()+ ' REMOVE FILE ' + @partition_file
EXEC (cmd1);

I want to put this statement in Begin Tran /Commit statement but getting error that it is not allowed.  "ALTER DATABASE statement not allowed within multi-statement transaction"..what are my options to rollback in case there is a failure. 

View 4 Replies View Related

Transact SQL :: HierarchyID - How To Give A Column Name For Node Path

Aug 19, 2015

I just started learning HierarchyID and copied the the following code from [URL] .... and Chapter 7 Hierarchical Data and the Relational Database of the book "Programming Microsoft SQL Server 2012", written by L. G. Lobel and A. J. Brust (Published by Microsoft Press):

-- shcHierarchyid_1.sql
-- 19 August 2015 13:15 PM

USE master
GO
IF EXISTS(SELECT name FROM sys.databases WHERE name = 'shcDB')
DROP DATABASE shcDB
GO
CREATE DATABASE shcDB
GO

[Code] ....

I executed the code in my Microsoft SQL Server 2012 Management Studio (SSMS2012).  It worked nicely. But, I don't understand the following 2 things:

#1: In the Results, I got:          

(No column name)     Position    Title
                1       /          CEO
                2 /1/         0x58      Purchase Manager
                3      /1/1/         0x5AC0 Purchase Executive
                4      /2/             0x68       Sales Manager
                5 /2/1/         0x6AC0  Sales Executive

I don't know how to give a column name for the /, /1/, /1/1/, /2/, /2/1/ (NodePath (?) I guessed). How I can give a column name to that column.

#2:   The book lists "USE master  GO .....".  I wonder whether I can do the "hierarchyid" thing in the private database I created!!??  Whether I can do the "hierarchyid" thing in the private database or not.

View 8 Replies View Related

Transact SQL :: How To Give Rankings Based On GUID Column

May 22, 2015

I have the table like below I want to gave Rankings to GUID Column. GUID col has Duplicate rows I am not getting unique ranking.

View 6 Replies View Related

Transact SQL :: Count Only Latest Entry

May 25, 2015

I have a table with below structure, with a sample entry for 1 country:

ID Date              Event CountryCode TxnID
1   22/05/2015   ABC   123   111
2   23/05/2015   XYZ   123   111
3   20/05/2015   XYZ   123   222
4   21/05/2015   ABC   123   222

So, I need to get a count like:

FOR COUNTRYCODE=123
ABC count= 1 
XYZ count= 1

Which means, I want to count for each transaction, the latest entry based on date. And there can be many countries like this. And I need to count both entries for ABC & XYZ.Normal count will give me duplicate entries.

View 10 Replies View Related

Transact SQL :: Tagging The Latest Entry?

Oct 1, 2015

Got a view with a number of monthly tables joined with union all. The fields' structure is as follows:

   Report_Date
   Staff_ID
   Staff_Name
   ...
   Current_Department

In such a case any query to reflect all the columns with an additional column reflecting the said Staff's latest Department?

In other words, ones still working in the organization reflecting in the latest Department field, the Current Department and those already resigned/fired reflecting the last department they worked at with respect to the Report_Date.

View 6 Replies View Related

Transact SQL :: How To Fetch Only Latest Address

May 10, 2015

Got a Table_A in the form of:
Cust_ID
Cust_Name
and another Table_B as:
Recording_Date
Invoice_ID
Cust_ID
Cust_Address

In such a case let's say I need a View to represent the columns:

Cust_ID (From Table_A)
Cust_Name (From Table_A)
Cust_Address (From Table_B)

However, with the Customers changing their addresses and having multiple records of different dates in Table_B how to reflect the Latest address of the same in the requisite View?In other words Cust_Id (AB), Cust_Name (CD), who as per Table_B have 100 records with three different addresses (EF, GH & IJ) spread over two years. How to fetch the only latest address in this case? i.e. a record in View_C as:

Cust_ID
Cust_Name
Cast_Address

AB
CD
IJ

View 7 Replies View Related

Transact SQL :: Create A User For Only One Database

Jul 17, 2015

I have a Java application (APP) that use SQL Server 2008 - 20014. In the SQL server there can be more than my database. During setup of the database the first user (ex:ADM) is created using MS SQL Server Management Studio (SMS). ADM can then launch the APP with proper credentials and connect to the database.ADM can then, in the APP, create new users for the APP and database. After some testing for proper CREATE and GRANTS I use the following code to create a new Create, Read, Update, Delete (CRUD) user for the APP and database: CREATE LOGIN testuser WITH PASSWORD='password', DEFAULT_DATABASE = testdb

USE testdb
CREATE USER testuser FOR LOGIN testuser
GRANT AUTHENTICATE TO testuser WITH GRANT OPTION
GRANT CONNECT TO testuser WITH GRANT OPTION
GRANT ALTER ANY USER TO testuser WITH GRANT OPTION
GRANT CONNECT SQL TO testuser WITH GRANT OPTION
GRANT INSERT,SELECT,UPDATE, EXECUTE, DELETE TO testuser WITH GRANT OPTION

USE master
GRANT CONTROL SERVER TO testuser

USE testdb..When I the use the SMS and look at security the "testuser" is only added to testdb. To test I create a second database "testdb2" --> launch the APP to connect to testdb2 and can login with "testuser".I don't know if I give to much grants or missing one...proper TSQL so new users only have CRUD access to "testdb" and no other database on the SQL server.

View 5 Replies View Related

Transact SQL :: Querying Data With Latest Date

Jun 24, 2015

Below is the table information. I want the Code information with latest EDate only.

CREATE TABLE Test
(
EDate Datetime,
Code varchar(255),
Cdate int,
Price int
);

[Code] ....

Expected output :
 
2015-06-23 00:00:00.000 CL 20150701 73
2011-04-08 00:00:00.000 XP 20110501 37
2015-06-23 00:00:00.000 HO 20150701 22

View 13 Replies View Related

Mirroring :: Store Transaction Log File / When Create Mirror Database

Nov 27, 2008

When Create Mirror Database Server, Where need to store the Transaction Log backup file?I took FULL Backup of my Primary Database, and I restored at my Mirror Server also. When I try to create a Mirror Database."The remote copy of database "<db_name>" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error:1412)".I am misplacing the Transaction Log backup file. Where I need to store that file?

View 10 Replies View Related

Transact SQL :: Query To Give Results Of All Configured Alerts Occurring History

May 14, 2015

We created sql alerts on all our sql servers environments. Now, i want to see each sql server which sql alerts so far got fired and which one never occurs. is there any way, we can get this information from any system database?

View 9 Replies View Related

Transact SQL :: Create Database Trigger Automatically Whenever New DB Is Creating?

Aug 5, 2015

I need to deploy the trigger in database whenever new DB is creating on the server.

View 6 Replies View Related

Create A Query That Will Give Result Set Containing Primary Order On Type

May 14, 2012

I have a table with plant types and plant names. Certain plants are grouped on a custom field, currently called Field. I am trying to create a query that will give me a result set containing the primary order on Type, but need items with the same 'Field' value grouped by each other.For example, the following shows a standard query result with "order by Type", ie select * from plants order by Type

Code:
ID Type Name Field
1 Type1Name1(group1)
2 Type2Name2(group2) -group2
3 Type3Name3(group3)
4 Type4Name4(group4)
5 Type5Name5(group2) -group2
6 Type6Name6(group6)

But I want it to look like this, with fields of the same value located next to each other in the result set (but still initially ordered by Type)

Code:
1 Type1Name1(group1)
2 Type2Name2(group2) -group2
5 Type5Name5(group2) -group2
3 Type3Name3(group3)
4 Type4Name4(group4)
6 Type6Name6(group6)

View 7 Replies View Related

Create A View To Get Latest Status For Each Application

Apr 24, 2007

I would like some help creating a view that will display the latest status for each application. The lastest status should be based on CreateDt. 
For example:
Table Structure:
============
Application: ApplicationID, Name, Address, City, State, Zip, etc..
ApplicationAction: ApplicationActionID, ApplicationID, Status (Ex:new, reviewed, approved, closed), CreateDt 
  
View should display:
==============
ApplicantID, ApplicantActionID, Status, CreateDt  
 
Example:
==========
ApplicantID=4, Name=Bob Smith, etc....
ApplicantActionID=1,  ApplicantID=4, Status=New,  CreatDt=1/3/20071:00
ApplicantActionID=2,  ApplicantID=4, Status=Reviewed,  CreatDt=1/3/2007 2:00
ApplicantActionID=3,  ApplicantID=4, Status=Approved,  CreatDt=1/4/2007 1:00
 .... etc....
View should return:
Applicant=4, ApplicantActionID=3, Status=Approved,  CreatDt=1/4/2007 1:00
etc....
 
 
 
 

View 4 Replies View Related

Create A View That Will Give Most Current Status (by Statusdatetime) Of Each Order Number

Jan 20, 2015

We are having trouble figuring out how to create a view for this scenario:

We have a status log table that holds an order number, statusdatetime, and statuscode. This table will have multiple status' for the same order number. I want to create a view that will give me the most current status (by statusdatetime) of each order number. This view would show: order number, statusdatetime, and statuscode.

Here is a sample of the data:

Order numberStatusDateTimeStatusCode
1234512/15/2014 15:00CREATE
1234512/15/2014 16:30CONFIRMED
4567812/16/2014 08:00CREATE
9876412/18/2014 12:00CREATE
9876412/19/2014 08:00CONFIRMED
4567812/17/2014 09:30CONFIRMED
4567812/19/2014 15:30IN-TRANSIT

So my view should result in :

Order numberStatusDateTimeStatusCode
1234512/15/2014 16:30CONFIRMED
9876412/19/2014 08:00CONFIRMED
4567812/19/2014 15:30IN-TRANSIT

View 4 Replies View Related

SQL Security :: Give Access To Create Alter And Execute SPs In Server 2008R2

May 12, 2015

Is there a way i can grant a windows authenticated user say abcadam privilege:

-> to create, execute and alter the stored procedures that will be created by him only:

Point is we do not want the user to execute alter or modify other stored procedures out there for that DB

View 13 Replies View Related

How To Create Merge Replication With Latest Update Always From Subscribers

May 13, 2006

Hi ,

I am trying to create Replication Topology (Merge Replication) like below.

Subscriber1 --> Publisher <-- Subscriber2.

I have created both subscribers with Subscription Type as Server with Priority as 75. I am updating the Column A of Row_10 in Subscriber1 on time say 11 am. After i am running the Starting synchronizing agent from Subscriber1. The value propagated to Publisher now publisher contains the latest value in Column A. Uptonow the Subscriber2 is not synchronized with Publisher.

Now in Subscriber2 also Column A of Row_10 is updated say at 11.10 am. Actually now Publisher contains the value from Subscriber1 for that Column and in Subscriber2 we have the same column updated.

Now i am running the Synchronization in Subscriber2, i am getting the result which is not expected. Here Publisher's value is propagated to Subscriber2. But as per real scenario Subscriber2 has the Latest value which is updated on 11.10 AM.

I don't know what am i missing here. Actually merge replication should see the time stamp and it has to decide winner. But here it always considers publisher as a winner and puts the data to Subscriber.

Can anyone help ?

Thanks in advance

View 4 Replies View Related

Transact SQL :: Retrieve Latest Record Of Serial Number With Multiple Entries

Sep 30, 2015

I work for an organization that repairs serialized devices. Each time a device is repaired it's serial number is recorded in a database table along with the date it was repaired along with other information about the device. There are multiple cases where a unit has been repaired more than once.

I am trying to write a query that will return the serial only once and that record will be the record of the latest repair date. To sum it up,

Return a list of serials where if a serial exists more than once in the table, return only the instance of the serial record(s) with the max(created_dt). The end result will be a list of distinct serial numbers.

Here is my Query. The problem I believe is in my sub-query but I am not sure how to structure it.

SELECT
S.Id
, RMA
, PinSerial
, L4Serial
, L4Model

[Code] ....

View 3 Replies View Related

Please Give Me Your Suggestions On Report Query - Very Urgent

Oct 25, 2006

I need to disaplay number of Active Agencies on monthwise in one of my report. I have tbl_Agency table with ActiveDate and ActiveFlag. ActiveDate column contains always first Activation Date. If any chances in the agencies(update/delete) the same record will move to tbl_AgencyHistory table.

"If an agency is inactivated in September 10th, inactivated all of October, and then reactivated November 10th - the agency would be counted in September, not in October and counted in November"

ActiveDate column has always first activation date, I could not meet this requirement. This is very urgent issue, Could you please help me on this.

Thanks,

Malar











View 6 Replies View Related

DB Design :: Create Views From 2 Tables One Of Which Is Lookup Table That Will Give Column Names

Aug 5, 2015

I am trying a create views that would join 2 tables:

Table 1: Has all the columns need by a view (
Name: Product
Structure: ID, Attribute 1, Attribute 2, Attribute 3, Attribute 4, Attribute 5 etc
Table 2: Is a lookup table that provides the names of columns
Name: lookupTable
Structure: tableName, ColumnName, columnValue
Values: Product, Attribute1, Color
Product, Attribute2, Size
Product, Attribute3, Flavor
Product, Attribute4, Shape

I want to create a view that looks like

ID, Color, Size, Flavor, Shape

View 4 Replies View Related

Transact SQL :: Create Email Report Which Gives Result Of Multiple Results From Multiple Databases In Table Format

Jul 24, 2015

I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:

EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary', 
@profile_name  =
'SQL SMTP',
   
[code]....

View 3 Replies View Related

Create Output Which Just Reflects Latest Conditions When Joining Back To Contract

Oct 10, 2013

col 1 is the pk for this table called Conditions - this table is related to contract.

Col 1 - PKContract TypeType ID
973300 711917 C30
973301 711917 C32
973302 711917 C31
1152323 711917 C30
1152324 711917 C31
1152325 711917 C32

A contract can many conditions so 1:M...Col 2 is the contract reference and the linking join to contract

Now - a condition related to an contract can have many re-trys and the causes the pk to increment. As you can see there are three conditions related to first attempt and then another three conditions.I want to create an ouput which just reflects the latest conditions when joining back to contract - Is this possible?I have requested to application providers to provide flag, but this will take some time...

View 8 Replies View Related

Transact SQL :: Find Missing Months In A Table For The Earliest And Latest Start Dates Per ID Number?

Aug 27, 2015

I need to find the missing months in a table for the earliest and latest start dates per ID_No.  As an example:

create table #InputTable (ID_No int ,OccurMonth datetime)
insert into #InputTable (ID_No,OccurMonth) 
select 10, '2007-11-01' Union all
select 10, '2007-12-01' Union all
select 10, '2008-01-01' Union all
select 20, '2009-01-01' Union all
select 20, '2009-02-01' Union all
select 20, '2009-04-01' Union all
select 30, '2010-05-01' Union all
select 30, '2010-08-01' Union all
select 30, '2010-09-01' Union all
select 40, '2008-03-01'

For the above table, the answer should be:

ID_No OccurMonth
----- ----------
20 2009-02-01
30 2010-06-01
30 2010-07-01

1) don't include an ID column,

2) don't use the start date/end dates in the data or

3) use cursors, which are forbidden in my environment.

View 9 Replies View Related

Why Did MS Give Access To Reports Thru Both Reporting Manager And Report Server?

Dec 20, 2007

what is MS's strategy for having two off the shelf ways of getting at reports? In a typical company, is the average non administrator type user getting at his reports via one, the other, both?

View 1 Replies View Related

Transact SQL :: Report Table Sizes In Each Database On Server

Aug 4, 2015

The below query works perfectly fine, except that it  produces many outputs instead of one continuous table that can be easaly converted to xml / csv by copying it from the "Results" window. 

What I need is a query that will produce a single result for all the tables in all the databases on the server.

The results:

The query:

DECLARE @begin INT = 1, @end INT, @sql NVARCHAR(MAX)
DECLARE @CREATE_TEMPLATE VARCHAR(MAX);
DECLARE @DBNAME VARCHAR(255);
DECLARE @SQL_SCRIPT VARCHAR(MAX);
SELECT @end = COUNT(name) FROM sys.databases
SET @CREATE_TEMPLATE = '

[Code] ....

View 11 Replies View Related

Query With Transpose - Create A Report Out Of Database

Nov 3, 2014

I need to create a report out of our database.

Simplified example:

Table 1:

LINK_ID Item
123456 Item1
123457 Item2
123458 Item3
123459 Item4

Table 2

Link_IDPROP_IDProperty
1234561Property_1
1234562Property_2
1234563Property_3
1234564Property_4

[Code] ....

When I Join this 2 tables i get:

LINK_IDItemPROP_IDProperty
123456Item11Property_1
123456Item12Property_2
123456Item13Property_3
123456Item14Property_4

[Code] ....

But I need:

LINK_IDItemPROP_ID1PROP_ID2PROP_ID3PROP_ID4
123456Item1Property_1Property_2Property_3Property_4
123457Item2Property_1Property_2Property_3Property_4
123458Item3Property_1Property_2Property_3Property_4
123459Item4Property_1Property_2Property_3Property_4

View 3 Replies View Related

Subtotals In Report Give Infinity Value In Case Of Empty Cells In Column.

Mar 13, 2008

The title, I think, accurately illustrates the situation in my report.
Naturally, this result is unwanted.

How do I get the avg-function to ignore empty cells in the column?

Thanks in advance,

Pluggie

View 5 Replies View Related

Can We Generate Reports Without Using Report Viewer And Give A Popup For Saving And Opening It?

Apr 24, 2008

Hi


I am generating a Report by using SSRS. I am making using use of SSRS Report Viewer control for displaying the report. Which has an option to export the generated report in PDF format. Instead of displaying the report in by using report viewer I want to generate it directly in PDF format whenever that report is selected from the list of reports. So is there is any way to generate the report directly in PDF format which prompt a pop up for opening or saving the PDF?


Thanks,
Shailesh

View 3 Replies View Related

Reporting Services :: How Subscription ID Will Create In Report Server Database

Oct 19, 2015

We migrated ssrs from 2008 to ssrs 2014.after that we need to move only subscriptions to  New 2014 report server DB.

How to proceed that.

I am trying to insert record in DB. But subscription ID is not identity column. How to do this.

Can I place old Subscription IDS in  New 2014 reporting DB?

View 2 Replies View Related

Transact SQL :: Powershell Script To Fetch Database Usage Details For Multiple Servers (report)

Oct 28, 2015

Is there a way to fetch database usage details for multiple SQL servers (report) usirng powershell script.

Details: servername, databasename, datafile usage, logfile usage, free % age...etc.

View 3 Replies View Related







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